Find out Scheduled Concurrent Programs in Oracle Apps

Run the following script to find Scheduled Concurrent Programs and its frequency

  SELECT *

    FROM apps.FND_CONC_REQ_SUMMARY_V

   WHERE     phase_code = 'P'

         AND status_code IN ('I', 'Q')

         AND (NVL (request_type, 'X') != 'S')

         AND requested_start_date >= SYSDATE

ORDER BY program_short_name DESC;

• PHASE_CODE status P stands for Pending
• STATUS_CODE status I stands for Normal & status Q stands of Standby
• Request_type != 'S' condition is to disallow Request Set Stage programs.

Script to find Scheduled concurrent programs and request sets

SELECT
A.REQUEST_ID,
B.USER_NAME,
C.USER_CONCURRENT_PROGRAM_NAME,
C.ARGUMENT_TEXT
FROM
FND_CONCURRENT_REQUESTS A,
FND_USER B,
FND_CONC_REQ_SUMMARY_V C
WHERE
B.USER_ID = A.REQUESTED_BY
AND A.REQUEST_ID = C.REQUEST_ID
AND A.REQUESTED_START_DATE > SYSDATE
AND A.HOLD_FLAG = 'N'
AND A.STATUS_CODE IN ('Q','I')
AND B.USER_NAME LIKE '%'
AND A.DESCRIPTION LIKE '%'
ORDER BY
A.REQUEST_ID

To find last run date of custom concurrent program in oracle apps

 select fcp.user_concurrent_program_name
,fcr.request_date
,fu.user_name
,fcr.actual_start_date
,fcr.actual_completion_date
,fcr.phase_code
,fcr.status_code
,fcr.argument1
,fcr.argument2
,fcr.argument3
from fnd_concurrent_programs_vl fcp
,fnd_concurrent_requests fcr
,fnd_user fu
where fcp.user_concurrent_program_name like 'Payroll Run'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.requested_by = fu.user_id
order by fcr.actual_completion_date desc

Query To check Scheduled Concurrent Programs and Request Sets details

SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
            'PERIODICALLY',
            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',
                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
              'PERIODICALLY',
              'EVERY ' || fcr.resubmit_interval || ' ' ||
              fcr.resubmit_interval_unit_code || ' FROM ' ||
              fcr.resubmit_interval_type_code || ' OF PREV RUN',
              'ONCE',
              'AT :' ||
              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fu,
       apps.fnd_conc_release_classes   fcrc
 WHERE fcpt.application_id = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND fcr.phase_code = 'P'
   AND fcr.requested_start_date > SYSDATE
   AND fcpt.LANGUAGE = 'US'
   AND fcrc.release_class_id(+) = fcr.release_class_id
   AND fcrc.application_id(+) = fcr.release_class_app_id
   and  DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) like '%XXXXXXXX%'



---------------IST Time ---------------------------


SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
            'PERIODICALLY',
            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',
                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
              'PERIODICALLY',
              'EVERY ' || fcr.resubmit_interval || ' ' ||
              fcr.resubmit_interval_unit_code || ' FROM ' ||
              fcr.resubmit_interval_type_code || ' OF PREV RUN',
              'ONCE',
              'AT :' ||
              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
   ,requested_start_date+5/24+(30/(24*60)) ISO_Start_date --- +5.30
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fu,
       apps.fnd_conc_release_classes   fcrc
 WHERE fcpt.application_id = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND fcr.phase_code = 'P'
   AND fcr.requested_start_date > SYSDATE
   AND fcpt.LANGUAGE = 'US'
   AND fcrc.release_class_id(+) = fcr.release_class_id
   AND fcrc.application_id(+) = fcr.release_class_app_id
   and  DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) like '%XXXX%'

SQL to get list of Scheduled Concurrent Programs

Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.
SELECT FCR.request_id REQUEST_ID
,FCP.concurrent_program_name PROGRAM_SHORT_NAME
,FCP.user_concurrent_program_name PROGRAM_NAME
,FNU.user_name SUBMITTED_BY
,TO_CHAR(FCR.requested_start_date
,'DD-MON-YYYY HH24:MM:SS'
) REQUEST_START_DATE
,'Every '|| DECODE(LENGTH(FCL.class_info)
,39,FCL.class_info
,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)
,'N','Minute(s) '
,'D','Day(s) '
,'H','Hour(s) '
,'M','Month(s) '
)
|| 'after '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)
,'S','Start '
,'C','Completion '
)
|| 'of prior request'
) SCHEDULED_INTERVAL
,NVL(TO_CHAR(FCL.end_date_active
,'DD-MON-YYYY'),'forever'
) ENDING_ON
FROM APPS.fnd_concurrent_requests FCR
,APPS.fnd_concurrent_programs_vl FCP
,APPS.fnd_user FNU
,APPS.fnd_conc_release_classes FCL
WHERE FCR.phase_code = 'P'
AND FCR.status_code IN ('I','Q')
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCR.requested_by = FNU.user_id
AND FCR.release_class_app_id = FCL.application_id
AND FCR.release_class_id = FCL.release_class_id
ORDER BY FCP.concurrent_program_name
, FCR.requested_start_date