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’ )





Simple SQL loader

25 08 2011

Say we have records in this format and need to load into database.

Party_number,scheme_year,Status

918888379,2005,Pass

199966199,2008,Fail

999162249,2005,Pass

999909093,2005, Pass

999991399,2005, Pass

Steps to follow:

  1. Create a csv file with above details.

a. create from notepad . copy the contents and save as “test.csv” – select file type as “All”

b. create from excel – save as csv option

2. Create a table if not existing

CREATE TABLE xx_load_test

(party_number varchar2(15),

scheme_year number,

status varchar2(15)

)

3. Create control file. Open notepad and type these and save as “mycontrol.ctl “

– select file type as “All”

LOAD DATA

INFILE ‘C:\test\test.csv’

append INTO TABLE xx_load_test

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

TRAILING NULLCOLS (

PARTY_NUMBER,SCHEME_YEAR,STATUS

)

4. Execute the below command from command prompt.

sqlldr apps<password>@<database> control=C:\test\ mycontrol.ctl

Check the table by quering

SELECT * FROM xx_load_test

Enjoy loadering 🙂 x





Find the trace file path for oracle CP

16 06 2010

select
  u_dump.value 
  “Trace File Path”
from
             v$parameter u_dump
  cross join v$parameter db_name
  cross join v$process
        join v$session
          on v$process.addr = v$session.paddr
where
 u_dump.name   = ‘user_dump_dest’ and
 db_name.name  = ‘db_name’        AND
 sid= <sid>;

sid is the session id





Who locked the table ?

26 10 2009

Anytime curious your query locked any table? Who else is using the same table?

Wanna find the session and kill :P 
SELECT ao.object_name,
decode( vlo.locked_mode,  
0, 'None', /* Mon Lock equivalent */   
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */   
3, 'Row-X (SX)', /* R */  
4, 'Share', /* S */   
5, 'S/Row-X (SSX)', /* C */  
6, 'Exclusive',
vlo.locked_mode) Locked_Mode, /* X */ 
session_id , oracle_username, os_user_name, process 
FROM v$LOCKED_OBJECT vlo, ALL_OBJECTS ao 
WHERE vlo.object_id = ao.object_id 
AND vlo.locked_mode in (3, 5, 6) and ao.object_name like '%<tablename>%'

 Get the session id and kill the session
SELECT* FROM v$session WHERE sid=<>;




Tips on Toad

26 10 2009

 

Tips & Tricks

Any time toad got frozen when attempting to create a new connection ?

Please press Esc and you can save your time 🙂

Tired of wrting SELECT * FROM , why not create a shortcut 😀

Go to Edit –> Editor Options –> Auto Replace

Add definition : saf   write description as SELETE * FROM

Press OK.

Next time just write saf+space   {Time Saved}

Store the difficult table names as alias names.

 

If you want to store more complicated sql’s use

Edit — > Editor Options — > Code Templates

Activate these by Control+space