oracle r12 Personalisation links

31 05 2017

SQL Query

select COMP_ID ,COMP_ELEMENT,(SELECT (SELECT SYS_CONNECT_BY_PATH(p.path_name, ‘/’) PATH
FROM (SELECT jp.path_name
, jp.path_seq
, jp.path_docid
, jp.path_owner_docid
FROM applsys.jdr_paths jp) p
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY path_docid = PRIOR path_owner_docid
START WITH p.path_docid = jp1.path_docid) full_customisation_path
FROM applsys.jdr_paths jp1
WHERE 1=1–UPPER(jp1.path_name) = ‘CUSTOMIZATIONS’
and PATH_DOCID=c.PATH_DOCID) path_name ,ATT_VALUE
from JDR_COMPONENTS a
,JDR_ATTRIBUTES b
,JDR_PATHS c
where a.COMP_DOCID = b.ATT_COMP_DOCID
and a.COMP_SEQ = b.ATT_COMP_SEQ
and (ATT_VALUE like ‘%.com%’)
and att_name in (‘text’,’destination’)
and c.PATH_DOCID = a.COMP_DOCID

Identify if there are Personalization’s made to a page and what those Personalizations are:

1. The first and easiest way is to go to the page you want to check for Personalization’s, then click on the ‘About this page’ link at the bottom left of the page.
Click on the Personalization tab. Then you should see a list of ‘Effective Personalization’s’, what elements have been customized and when they were customized/personalized.

2. Another way is to use the Functional Administrator.
From the Functional Administrator responsibility Home page, click on the Personalization tab in the upper left hand corner. Insert the document path for the document you want to check – for example: Document Path = /oracle/apps/fnd/wf/worklist/webui/NotificationsPG and then click on the ‘Go’ button to get a list of Personalization’s for this page.

3. 3. A third way to check to see if there are personalizations on a page is to use sqlplus and run the JDR_UTILS program.
You will need the Document path to the page in question – again using the Document path for the Notifications page:
/oracle/apps/fnd/wf/worklist/webui/NotificationsPG

Run the listDocuments function to drill down and find all documents that have been personalizations under a certain Meta Data region:
Run from sqlplus:
set serveroutput on
exec JDR_UTILS.listDocuments(‘/oracle/apps/fnd/wf/worklist/webui/’);
/oracle/apps/fnd/wf/worklist/webui/customizations/

Note, you do not include the Page ID (NotificationsPG) with this query. In my test case, notice that there are ‘customizations’ in this MDS region webui. The ‘customizations’ indicates that personalizations have been made to the ‘webui’ region.

Run the query again with the new path and notice that there are ‘site’ level customizations, although there could be other levels such as user, responsibility, …:
exec JDR_UTILS.listDocuments(‘/oracle/apps/fnd/wf/worklist/webui/customizations/’);
/oracle/apps/fnd/wf/worklist/webui/customizations/site/

Run the query again with the new path and notice that the customizations are at site level ‘0’:
exec JDR_UTILS.listDocuments(‘/oracle/apps/fnd/wf/worklist/webui/customizations/site/’);
/oracle/apps/fnd/wf/worklist/webui/customizations/site/0/

Run the query one more time with the new path and notice that the customizations are made to the ‘NotificationsPG’:
exec JDR_UTILS.listDocuments(‘/oracle/apps/fnd/wf/worklist/webui/customizations/site/0/’);
/oracle/apps/fnd/wf/worklist/webui/customizations/site/0/NotificationsPG

Once we have identified a particular page or pages that have customizations/personalization’s, then we can use the printDocument to see what personalizations have been made to the page:

exec
JDR_UTILS.printDocument(‘/oracle/apps/fnd/wf/worklist/webui/customizations/site/0/NotificationsPG’);
————-
< ?xml version=’1.0′ encoding=’UTF-8′?>
< customization xmlns=”http://xmlns.oracle.com/jrad&#8221; version=”9.0.5.4.89_554″
xml:lang=”en-US”
customizes=”/oracle/apps/fnd/wf/worklist/webui/NotificationsPG”>
< modifications>
< modify element=”MainRegion.NtfView” rendered=”false”/>
< /modifications>
< /customization>
————-

As you can see, rendered has been set to “false” for “MainRegion.NtfView”.

You can then use the deletedocument function to remove this personalization exec
jdr_utils.deletedocument(‘/oracle/apps/fnd/wf/worklist/webui/customizations/site/0/NotificationsPG’);
< !–[if !supportLineBreakNewLine]–>
< !–[endif]–>

 Query to check the customizations:

select att.att_value base_path
, jdr_mds_internal.getDocumentName( pat.path_docid ) custom_path
, regexp_replace( jdr_mds_internal.getDocumentName( pat.path_docid ), ‘.*/customizations/([[:alpha:]]*)/.*’, ‘\1’ ) custom_level
, decode(
regexp_replace( jdr_mds_internal.getDocumentName( pat.path_docid ), ‘.*/customizations/([[:alpha:]]*)/.*’, ‘\1’ )
, ‘site’, ‘SITE’
, ( regexp_replace( jdr_mds_internal.getDocumentName( pat.path_docid ), ‘.*/customizations/([[:alpha:]]*)/([0-9A-Za-z_]*)/.*’, ‘\2’ ))) custom_level_value
, nvl( ( select ‘N’
from jdr_attributes cat
where cat.att_comp_docid = pat.path_docid
and cat.att_name = ‘MDSActiveDoc’
and cat.att_value = ‘false’ ), ‘Y’ ) is_active
, pat.created_by
, pat.creation_date
, pat.last_update_login
from jdr_paths      pat join
jdr_attributes att on ( pat.path_docid   = att.att_comp_docid )
where att_comp_seq = 0
and att_name     = ‘customizes’
and regexp_like( jdr_mds_internal.getDocumentName(path_docid), ‘apps/(ota)/’ )
and not exists( select ‘x’
from jdr_attributes bat
where bat.att_comp_docid = pat.path_docid
and att_name = ‘developerMode’ )

Advertisements