Query to Find Concurrent Program Responsibilities

Query to get Responsibilities for which Concurrent program is assigned.

SELECT
frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE
frt.responsibility_id = fr.responsibility_id
AND frt.language = userenv('LANG')
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND fcpt.language = userenv('LANG')
AND upper(fcpt.user_concurrent_program_name) = upper(:EnterConcurrentProgramName)
ORDER BY
1,
2,
3,
4

Customers Query

Below is the Query to get the list of Customers, Accounts, Locations and Sites in Oracle application.

SELECT DISTINCT
hp.party_name "Customer Name",
hca.account_number,
hca.status,
hou.name "Operating Unit",
hps.party_site_number,
hps.party_site_name,
hcsu.location,
hcsu.site_use_code,
hcsu.status loc_stat,
hcsu.site_use_id,
hcpc.name profile_name,
hl.address1,
hl.address2,
hl.address3,
hl.city,
hl.state,
hl.postal_code,
hps.identifying_address_flag
FROM
apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles hcp,
apps.hz_cust_profile_classes hcpc,
apps.hr_operating_units hou
WHERE hp.party_id = hca.party_id
AND hp.party_id = hcp.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hps.location_id = hl.location_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcp.cust_account_id
AND hcp.profile_class_id = hcpc.profile_class_id
AND hcsu.org_id = hou.organization_id;

Query to get Scheduled Concurrent Requests

Below is the Query to get the list of scheduled concurrent requests in Oracle application.

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' || cr.description, cp.user_concurrent_program_name) NAME,
argument_text,
cr.resubmit_interval,
NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) schedule_type,
DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')), 'PERIODICALLY', 'EVERY ' || cr.resubmit_interval || ' ' || cr.resubmit_interval_unit_code || ' FROM ' || cr.resubmit_interval_type_code || ' OF PREV RUN', 'ONCE', 'AT :' || TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 'EVERY: ' || fcr.class_info ) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND fu.user_name = NVL (:EnterUserName, fu.user_name);

Query to get list of responsibilities assigned to users

Below is the Query to get the list of responsibilities attached to a particualr user in Oracle application.

SELECT fu.user_name, frt.responsibility_name, furg.start_date, furg.end_date, fr.responsibility_key, fa.application_short_name
FROM fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.LANGUAGE = USERENV ('LANG')
AND fat.LANGUAGE = USERENV ('LANG')
AND UPPER (fu.user_name) = upper(nvl(:EnterUserName,fu.user_name))
ORDER BY frt.responsibility_name;