Tables and Columns Information - all_tab_columns & all_objects

all_tab_columns 

select * from all_tab_columns
where table_name = 'PO_REQUISITION_LINES_ALL'
and column_name like '%HAZARD_CLASS%';


all_objects

select * from ALL_OBJECTS
where object_name like 'PS%'
and object_type = 'TABLE'

Here are detail steps to copy personalization from one instance to other instances using the XMLImporter/XMLExporter tool

1. Get Document Name.

Go to the page you want to copy the personalization from and click the "about this Page" link at the bottom of the page. You will see the page name with full path which starts with /oracle/apps/<prod>.
2. Get the Personalization Document info

Run the following command at SQLPlus as APPS user     

SQL> set serveroutput on
SQL> exec jdr_utils.listCustomizations('<full document name from step 1>')

1.  If there are multiple records returned by this command, you will need to use export/import each item individually that you wish to export. 

2.  You may also find there are records returned for seeded personalizations provided by Oracle which do not need to be exported
3. Use XMLExporter to export personalization document

java oracle.jrad.tools.xml.exporter.XMLExporter <personalization document from step #2>  \
-username "<username>" -password "<password>"  \
-dbconnection "(description=(address_list=(address=(protocol=tcp)(host=<host>)(port=<port)))(connect_data=(sid=<sid>)))" \
-rootdir "<output directory>"
4. Use XMLImporter to import personalization document

java oracle.jrad.tools.xml.importer.XMLImporter \
<full path of the file you want to import> \
-username "<username>" -password "<password>"  \
-dbconnection "(description=(address_list=(address=(protocol=tcp)(host=<host>)(port=<port>)))(connect_data=(sid=<sid>)))" -rootdir "<top level directory>" \
-rootPackage "/oracle/apps/<prod>"


If you are importing files exported from step #3, then the rootdir in step #4 will be same as in step #3
Example
Here is a step-by-step guide showing how to use XMLImporter/XMLExporter to transfer a SITE level personalization for the PreferencesPG page ($FND_TOP/mds/preferences/webui/PreferencesPG.xml)

1. List the personalization document to be exported

SQL> set serveroutput on
SQL> exec jdr_utils.listcustomizations('/oracle/apps/fnd/preferences/webui/PreferencesPG');

Returns:
/oracle/apps/fnd/preferences/webui/customizations/site/0/PreferencesPG
2. Export the above personalization file to /tmp/mytest from the DEV instance

Before exporting, check the directory /tmp/mytest is valid and writable.
java oracle.jrad.tools.xml.exporter.XMLExporter \/oracle/apps/fnd/preferences/webui/customizations/site/0/PreferencesPG \-username apps -password appsdevpass \
-dbconnection "(description=(address_list=(address=(protocol=tcp)(host=myhost)(port=1523)))(connect_data=(sid=DEV)))" \
-rootdir "/tmp/mytest"
OR

java oracle.jrad.tools.xml.exporter.XMLExporter -rootdir "/tmp/mytest" /oracle/apps/fnd/preferences/webui/customizations/site/0/PreferencesPG -username apps -password appsdevpass  -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host =myhost)(port = 1523)))(connect_data = (sid = DEV)))";


3. Import the above exported file into the UAT instance:

java oracle.jrad.tools.xml.importer.XMLImporter \ /tmp/mytest/oracle/apps/fnd/preferences/webui/customizations/site/0/PreferencesPG.xml \
-username apps -password appsuatpass \
-dbconnection "(description=(address_list=(address=(protocol=tcp)(host=myhost)(port=1522)))(connect_data=(sid=UAT)))" \
-rootdir "/tmp/mytest" -rootPackage "/oracle/apps/fnd"


NOTE: The use of: -rootPackage reflected above IS AN OPTIONAL parameter.

Personalization for Profile Setting

FND: Personalization Region Link Enabled : Yes

Personalize Self-Service Defn : Yes

Disable Self-Service Personal : No

Method 4 - Removing personalization document in the database using JDR_UTILS package

This method only requires access to the database and must be used with great care to ensure the correct document is deleted. For this method you need to know the document name of the OAF page with the personalization (See Introduction section)

1) Login to SQLPlus as APPS user

2) Run the following commands to show the available personalization documents

SQL> set serverout on;
SQL> exec jdr_utils.listcustomizations('/oracle/apps/<product>/<subcategories>/<pagename>');
e.g.

SQL> exec jdr_utils.listcustomizations('/oracle/apps/fnd/framework/navigate/webui/HomePG');

3) This returns the document names of the personalization document for this OAF page. For example for SITE level on Homepage

/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG

4) Backup the personalization document by exporting it to a file (so it can be reloaded if needed). Make sure you are backing up the correct document. It should have "/customizations/" in the document name.

From the web server, use the OS command:

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter \
/oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/ /<pagename> \
-username apps \
-password <appspwd> \
-dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" \
-rootdir /tmp
 
This create an XML document in the /tmp directory, like

/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml

5) Delete the personalization document. Be VERY careful you are deleting the personalization and not the base document !

SQL> exec jdr_utils.deletedocument('/oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/<pagename>');
SQL> commit;
For the home page example this is:

SQL> exec jdr_utils.deletedocument('/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG');
SQL> commit;
6) Restart Apache to ensure that Java caching is cleared

7) Retest the issue and confirm that OAF page is not accessible again

8) To restore the personalization document deleted, use the following command (optional)

-adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter \
/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml \
-username apps \
-password apps \
-dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" \
-rootdir /tmp

How to Grab Debug Log

a)set the following profile at user level

FND: Debug Log Enabled = Yes
FND: Debug Log Level = Statement
FND: Debug Log Filename = leave it as null
FND: Debug Log Module = %

b) select max(log_sequence)
from fnd_log_messages;

c) Now Reproduce the issue

d) select *
from fnd_log_messages where log_sequence > <retrieved from first query>

How to Check DB Version

SELECT * FROM V$VERSION


SELECT version FROM V$INSTANCE

No Record Response When Access HR table Views

--for HR table views


insert into fnd_sessions values (userenv('SESSIONID'),SYSDATE)

XXX is not a valid responsibility for the current user. Please contact your System Administrator.

Error: " XXX Not a valid responsibility for the current user. Please contact your System Administrator."

1) Login with System Administrator Responsibility
2) Navigate to Profile > System, click on User and enter the user name
3) Search for profile 'Applications Start Page'. Delete the value set at user level and save
4) Go to Functional Administrator -> Clear Cache
5) Try logging into the account and try again