----------------------------------------------------------------------------------
--Checking the duplicated schedules of the same program with the same arguments
--The below query can be used to check the duplicated schedule of the same program with the same arguments.
--This can be used to alert the users to cancel these duplicated schedules.
--Note: This query will return even though the request was submitted using a different responsibility.

----------------------------------------------------------------------------------
SELECT request_id, NAME, argument_text, REQUESTED_START_DATE, user_name,RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE
FROM (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.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
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 in ('P','R','I')
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name like '%') t1
WHERE EXISTS (
SELECT 1
FROM (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.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
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 fu.user_name LIKE '%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME;