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




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