Query to Concurrent Program running and status checking with Parameters

--Concurrent Program running and status checking with Parameters

SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) "Time took MIN",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",
R.ARGUMENT_TEXT "Parameters",
substr(u.description,1,25) "Who submitted"
FROM
APPS.FND_CONCURRENT_REQUESTS R ,
--apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
--R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
R.ACTUAL_START_DATE >= (SYSDATE-48/24)--2 Days --24/24 (1day)
--and R.REQUEST_ID=23418828
--AND r.requested_by=22378
--AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
and t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
and V.REQUEST_ID=R.REQUEST_ID
--and substr(u.description,1,25) like 'Common UC4 user'
-- and R.ARGUMENT_TEXT like'%3061%'
--AND r.request_id ='24663348' --in ('13829387','13850423')
and t.user_concurrent_program_name like '%XXXX Auto Create Purchase Orders%'
order by TO_CHAR(R.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') DESC;

===============================================================================================================================

--Just to check the parameters


Select * from APPS.FND_CONC_REQ_SUMMARY_V Where Program like 'Interface Trip Stop' and argument_text like '%3919919%'

===============================================================================================================================

--Just to check the pending Count

SELECT COUNT(*) FROM APPS.FND_CONC_REQ_SUMMARY_V
WHERE PROGRAM='Interface Trip Stop'
AND PHASE_CODE='P'
AND STATUS_CODE='Q';

================================================================================================================================

SELECT Requestor,COUNT(*) FROM APPS.FND_CONC_REQ_SUMMARY_V
WHERE PROGRAM='Interface Trip Stop'
AND PHASE_CODE='P'
--and Requestor<>'XXXXX'
AND STATUS_CODE='Q'
group by requestor;