-- To find out the responsibilities the given concurrent program assigned to

SELECT
rt.responsibility_name,
a1.application_name req_grp_app,
rg.request_group_name,
rg.description,
a2.application_name program_app,
cp.concurrent_program_name,
cpt.user_concurrent_program_name
FROM
fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_application_tl a1,
fnd_application_tl a2,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_responsibility r,
fnd_responsibility_tl rt
WHERE rgu.application_id = rg.application_id
AND rgu.request_group_id = rg.request_group_id
AND a2.application_id = cp.application_id
AND cp.concurrent_program_id = rgu.request_unit_id
AND cpt.concurrent_program_id = cp.concurrent_program_id
AND cpt.application_id = cp.application_id
AND cp.concurrent_program_name LIKE 'ONT_DROPSHIP' -- this is the program name
-- and cpt.user_concurrent_program_name like 'Auto%' -- this one is the long concurrent program name
AND rgu.unit_application_id = cp.application_id
AND rg.application_id = a1.application_id
AND r.request_group_id = rg.request_group_id
AND r.responsibility_id = rt.responsibility_id
AND r.application_id = rt.application_id
AND r.group_application_id = rg.application_id


-- concurrent program details
SELECT
cpt.user_concurrent_program_name,
cp.concurrent_program_name,
e.execution_file_name,
cp.*, cpt.*, e.*
FROM
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_executables e
WHERE cp.application_id = cpt.application_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cp.concurrent_program_name LIKE 'ONT%'
--and cp.concurrent_program_name = 'ONTCEWHCP'
AND cp.executable_id = e.executable_id

--To find if the particular concurrent program is running or not form Database
--will be usefull when you don't have front end access ie) in case of production

SELECT a.requestor,a.* FROM FND_CONC_REQ_SUMMARY_V a WHERE request_Date >=TRUNC(SYSDATE)
AND program_short_name=
ORDER BY request_date DESC