Concurrent request queries

9 11 2017

Find request sets attached to concurrent program

SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( ‘&Enter_Prog_name’)));

Find programs attached to request sets

SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper(‘&Request_Set_Name’)));

Find request set details with parameters

SELECT rs.user_request_set_name “Request Set”
, rss.display_sequence “Stage Seq”
, rsp.sequence “Program Seq”
, cp_param.column_seq_num “Param Seq”
, cp.user_concurrent_program_name “Concurrent Program”
, e.execution_file_name “Execution File”
, lv.meaning “File Type”
, form_left_prompt “Parameter”
, cp_param.default_type
, cp_param.default_value “Prog Default”
, NVL((SELECT default_value
FROM apps.FND_REQUEST_SET_PROGRAM_ARGS set_param
WHERE set_param.descriptive_flexfield_name = cp_param.descriptive_flexfield_name
and set_param.application_column_name = cp_param.application_column_name
and set_param.descriptive_flex_appl_id = cp_param.application_id
and set_param.request_set_id = rs.request_set_id
and set_param.request_set_program_id = rsp.request_set_program_id
), cp_param.default_value) “Reqquest Set Default Value”
, rs.REQUEST_SET_ID
FROM apps.fnd_request_sets_vl rs
, apps.fnd_req_set_stages_form_v rss
, applsys.fnd_request_set_programs rsp
, apps.fnd_concurrent_programs_vl cp
, apps.fnd_executables e
, apps.fnd_lookup_values lv
, apps.fnd_descr_flex_col_usage_vl cp_param
WHERE 1=1 /* Above condition should be changed to select desired request sets */
and rs.user_request_set_name = :request_name
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND lv.lookup_code = e.execution_method_code
AND rs.end_date_active IS NULL
AND cp_param.descriptive_flexfield_name (+) = ‘$SRS$.’ || cp.concurrent_program_name
AND cp_param.application_id (+) = cp.application_id
AND cp_param.enabled_flag (+)= ‘Y’
ORDER BY rss.display_sequence
, rsp.sequence
, cp_param.column_seq_num

 

Advertisements




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