Scheduled Concurrent Program list :
SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;
Total Scheduled Program Count :
select ‘Scheduled requests:’ schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR
status_code = ‘P’)
AND phase_code = ‘P’;
select ‘Non-scheduled requests:’ schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE requested_start_date <= sysdate
AND status_code != ‘P’
AND phase_code = ‘P’;
SELECT fcr.request_id ,
fcpt.user_concurrent_program_name
|| NVL2(fcr.description, ‘ (‘
|| fcr.description
|| ‘)’, NULL) conc_prog ,
fu.user_name requestor ,
fu.description requested_by ,
fu.email_address ,
frt.responsibility_name requested_by_resp ,
TRIM(fl.meaning) STATUS ,
fcr.phase_code ,
fcr.status_code ,
fcr.argument_text “PARAMETERS” ,
TO_CHAR(fcr.request_date, ‘DD-MON-YYYY HH24:MI:SS’) requested ,
TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’) requested_start ,
TO_CHAR((fcr.requested_start_date), ‘HH24:MI:SS’) start_time ,
DECODE(fcr.hold_flag, ‘Y’, ‘Yes’, ‘N’, ‘No’) on_hold ,
CASE
WHEN fcr.hold_flag = ‘Y’
THEN SUBSTR( fu.description , 0 , 40 )
END last_update_by ,
CASE
WHEN fcr.hold_flag = ‘Y’
THEN fcr.last_update_date
END last_update_date ,
fcr.increment_dates ,
CASE
WHEN fcrc.CLASS_INFO IS NULL
THEN ‘Yes: ‘
|| TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’)
ELSE ‘n/a’
END run_once ,
CASE
WHEN fcrc.class_type = ‘P’
THEN ‘Repeat every ‘
|| SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ‘:’) – 1)
|| DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 1) + 1, 1), ‘N’, ‘ minutes’, ‘M’, ‘ months’, ‘H’, ‘ hours’, ‘D’, ‘ days’)
|| DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 2) + 1, 1), ‘S’, ‘ from the start of the prior run’, ‘C’, ‘ from the completion of the prior run’)
ELSE ‘n/a’
END set_days_of_week ,
CASE
WHEN fcrc.class_type = ‘S’
AND instr(SUBSTR(fcrc.class_info, 33),’1′,1) > 0
THEN ‘Days of week: ‘
|| DECODE(SUBSTR(fcrc.class_info, 33, 1), ‘1’, ‘Sun, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 34, 1), ‘1’, ‘Mon, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 35, 1), ‘1’, ‘Tue, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 36, 1), ‘1’, ‘Wed, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 37, 1), ‘1’, ‘Thu, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 38, 1), ‘1’, ‘Fri, ‘)
|| DECODE(SUBSTR(fcrc.class_info, 39, 1), ‘1’, ‘Sat ‘)
ELSE ‘n/a’
END days_of_week
FROM apps.fnd_concurrent_requests fcr ,
apps.fnd_user fu ,
apps.fnd_concurrent_programs fcp ,
apps.fnd_concurrent_programs_tl fcpt ,
apps.fnd_printer_styles_tl fpst ,
apps.fnd_conc_release_classes fcrc ,
apps.fnd_responsibility_tl frt ,
apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = ‘CP_STATUS_CODE’
AND fcr.phase_code = ‘P’
AND frt.language = ‘US’
AND fpst.language = ‘US’
AND fcpt.language = ‘US’
ORDER BY Fu.Description,
Fcr.Requested_Start_Date ASC
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;
