How To Find All Scheduled Requests Or List Of Requests in a Particular Status?

-- chkreqs.sql

set timing off 

column id format 9999999 heading 'Request' 
column phase heading 'Phase' 
column status format A9 heading 'Status' 
column pname format A40 heading 'Program name' 
column rpname format A32 heading 'Program name' 
column sd format A18 heading 'Start date' 
column submitd format A18 heading 'Submitted on' 
column requestd format A18 heading 'Requested start' 
column numreqs format 99999999 heading 'Number' 
column ra format A9 heading 'Run alone' 
column phase format A18 heading 'Phase' 
column pphase format A7 heading 'Phase' 
column schedt format A25 
column schedcnt format 99999 


prompt 
prompt ============================ 
prompt FND_CONCURRENT_REQUESTS totals 
prompt ============================ 

SELECT decode(phase_code, 'P', 'Pending requests', 
'R', 'Running requests', 
'C', 'Completed requests', 
phase_code) phase, count(*) numreqs 
FROM fnd_concurrent_requests 
GROUP BY phase_code; 


prompt 
prompt ============================
prompt Running requests: 
prompt ============================

SELECT request_id id, 
nvl(meaning, 'UNKNOWN') status, 
user_concurrent_program_name rpname, 
to_char(actual_start_date, 'DD-MON-RR HH24:MI:SS') sd, 
decode(run_alone_flag, 'Y', 'Yes', 'No') ra 
FROM fnd_concurrent_requests fcr, 
fnd_lookups fl, 
fnd_concurrent_programs_vl fcpv 
WHERE phase_code = 'R' 
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 actual_start_date, request_id; 


prompt 
prompt ============================ 
prompt Pending request totals 
prompt ============================ 

prompt 
prompt Total Pending requests (by status code): 
prompt ----------------------------------------------- 

SELECT 'Pending' pphase, 
meaning status, 
count(*) numreqs 
FROM fnd_concurrent_requests, 
fnd_lookups 
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' 
AND lookup_code = status_code 
AND phase_code = 'P' 
GROUP BY meaning; 

prompt 
prompt Total scheduled requests: 
prompt ----------------------------------------------- 


set heading off 
set feedback off 

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'; 


prompt 
prompt 
prompt Total requests on hold: 
prompt ----------------------------------------------- 

select 'Requests on hold:' schedt, count(*) schedcnt 
from fnd_concurrent_requests 
WHERE hold_flag = 'Y' 
AND phase_code = 'P'; 

select 'Not on hold:' schedt, count(*) schedcnt 
from fnd_concurrent_requests 
WHERE hold_flag != 'Y' 
AND phase_code = 'P'; 


set heading on 
set feedback on 


prompt 
prompt 
prompt 
prompt ============================ 
prompt Listing scheduled requests: 
prompt ============================

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; 

prompt 
prompt ============================ 
prompt Listing pending requests (on hold): 
prompt ============================

SELECT request_id id, 
nvl(meaning, 'UNKNOWN') status, 
user_concurrent_program_name pname, 
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd 
FROM fnd_concurrent_requests fcr, 
fnd_lookups fl, 
fnd_concurrent_programs_vl fcpv 
WHERE phase_code = 'P' 
AND hold_flag = 'Y' 
AND fcr.requested_start_date <= sysdate 
AND 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; 


prompt 
prompt ============================ 
prompt Listing pending requests (Not on hold): 
prompt ============================ 

SELECT request_id id, 
nvl(meaning, 'UNKNOWN') status, 
user_concurrent_program_name pname, 
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd 
FROM fnd_concurrent_requests fcr, 
fnd_lookups fl, 
fnd_concurrent_programs_vl fcpv 
WHERE phase_code = 'P' 
AND hold_flag = 'N' 
AND fcr.requested_start_date <= sysdate 
AND 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; 

++++++++++++++++++++++++++++++++++++++

(Doc ID 554380.1)