To Manage Personalization in Oracle R12

Note: Validate the steps in TEST before doing in PROD. The personalization level will impact the dependency document path

To Disable Personalization if system not allowing to login:

DECLARE

stat boolean;

BEGIN

dbms_output.disable;

dbms_output.enable(100000);

stat := FND_PROFILE.SAVE('FND_DISABLE_OA_CUSTOMIZATIONS', 'Y', 'SITE');

IF stat THEN

dbms_output.put_line( 'Stat = TRUE - profile updated' );

ELSE

dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );

END IF;

commit;

END;

To find personalization from backend:

SELECT

    jp.path_docid,

    jdr_mds_internal.getdocumentname(jp.path_docid) personalization_path,

    jp.path_name,

    jp.path_owner_docid,

    jp.path_seq,

    jp.path_type,

    jp.path_xml_encoding,

    jp.path_xml_version,

    jp.created_by,

    jp.creation_date,

    jp.last_updated_by,

    jp.last_update_date

FROM

    apps.jdr_paths jp

WHERE

    jp.path_docid IN (

        SELECT DISTINCT

            comp_docid

        FROM

            jdr_components

        WHERE

            comp_seq = 0

            AND comp_element = 'customization'

            AND comp_id IS NULL)

--AND   upper(jdr_mds_internal.getdocumentname(jp.path_docid) ) LIKE upper('%HOMEPG%')

AND jp.creation_date > SYSDATE - 7;

List the Personalization:

set serveroutput on;

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

Output:

/oracle/apps/fnd/framework/navigate/webui/customizations/function/OAHOMEPAGE/HomePG

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

Export The Personalization:

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter \

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

-username apps \

-password apps \

-dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=test.orcl1.com)(PORT=1526)))(CONNECT_DATA=(SID=TEST)))" \

-rootdir /usr/tmp

Note: Output file will be saved in xml format in rootdir location

Example: /usr/tmp/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG.xml

Delete the personalization document:

SQL> exec jdr_utils.deletedocument('/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG);

SQL> commit;

Bounce Apache web server & Clear the Cache

Import The Personalization:

adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter \

/usr/tmp/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG.xml \

-username apps \

-password apps \

-dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=test.orcl1.com)(PORT=1526)))(CONNECT_DATA=(SID=TEST)))" \

-rootdir /usr/tmp