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





invoices to payment

4 05 2017

SELECT aca.check_number “Document Number”,
asa.segment1 “Supplier Number”,
asa.vendor_name “Supplier Name”,
aca.STATUS_LOOKUP_CODE “Payment Reconcilation Status”,
aca.CLEARED_AMOUNT “Payment Amount”,
aca.CURRENCY_CODE “Currency”,
aca.CLEARED_DATE “Payment Date”,
aia.invoice_num,
aia.invoice_amount,
AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE)
“Invoice Acct Status”,batch_id
FROM ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_suppliers asa
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aipa.ACCRUAL_POSTED_FLAG = ‘N’
AND POSTED_FLAG = ‘N’
— and aca.check_number =’7221646′
AND TRUNC (aca.CLEARED_DATE) BETWEEN ’01-JAN-2017′ AND ’30-APR-2017′
ORDER BY check_number;





:$PROFILES$.XLA_APPL_ID:NULL,:$PROFILES$.RESP_APPL_ID

5 04 2017

You can see that the “Application” is a parameter this is obtained via this SQL statement (from the Default Value field) “SELECT APPLICATION_NAME FROM FND_APPLICATION_VL WHERE APPLICATION_ID= nvl(:$PROFILES$.XLA_APPL_ID:NULL,:$PROFILES$.RESP_APPL_ID)”. The application is derived from the Application defined on the Responsibility.

 

Oracle introduced a new profile option called “SLA: Alternate Application Id”

you will need to set the profile option “SLA: Alternate Application Id” for each of your responsibilities at the Responsibility level . The patch number that you may need to apply in order to load the “SLA: Alternate Application Id” is Patch 8332032. Find out more at My Oracle Support using notes 833986.1 or 1233453.1.





FND_PROFILE and FND_GLOBAL values

5 04 2017

Following are the FND_PROFILE values that can be used in the PL/SQL code:

fnd_profile.value(‘PROFILEOPTION’);

fnd_profile.value(‘MFG_ORGANIZATION_ID’);

fnd_profile.value(‘ORG_ID’);

fnd_profile.value(‘LOGIN_ID’);

fnd_profile.value(‘USER_ID’);

fnd_profile.value(‘USERNAME’);

fnd_profile.value(‘CONCURRENT_REQUEST_ID’);

fnd_profile.value(‘GL_SET_OF_BKS_ID’);

fnd_profile.value(‘SO_ORGANIZATION_ID’);

fnd_profile.value(‘APPL_SHRT_NAME’);

fnd_profile.value(‘RESP_NAME’);

fnd_profile.value(‘RESP_ID’);
Following are the FND_GLOBAL values that can be used in the PL/SQL code:

FND_GLOBAL.USER_ID;

FND_GLOBAL.APPS_INTIALIZE;

FND_GLOBAL.LOGIN_ID;

FND_GLOBAL.CONC_LOGIN_ID;

FND_GLOBAL.PROG_APPL_ID;

FND_GLOBAL.CONC_PROGRAM_ID;

FND_GLOBAL.CONC_REQUEST_ID;
For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

g_org_id       PLS_INTEGER  :=  fnd_profile.value(‘ORG_ID’);

g_sob_id       PLS_INTEGER  :=  fnd_profile.value(‘GL_SET_OF_BKS_ID’);
And initialize the application environment as follows:

v_resp_appl_id  := fnd_global.resp_appl_id;

v_resp_id       := fnd_global.resp_id;

v_user_id       := fnd_global.user_id;

 

FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);





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=<>;