Finding Concurrent Program Past History Execution Details

If we need to find the Concurrent Program previous\past\history execution details then the below query will be useful.

Script:

select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start date",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started date",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed date",
substr(ARGUMENT_TEXT,1,100) Parameter_Passed ,
TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
                     24
                    )
               ) "Hr",
         TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60,
                     60
                    )
               ) "Mins",
         TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60
                     * 60,
                     60
                    )
               ) "Sec"
from fnd_concurrent_requests cr where concurrent_program_id in
( select concurrent_program_id
from fnd_concurrent_programs
where concurrent_program_name = '&prog_short_name' )
and actual_start_date>sysdate - &noofdaysago
order by phase_code,actual_start_date asc

select distinct * from (select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed"
,TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
24
)
) "Hr",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60,
60
)
) "Mi",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60
* 60,
60
)
) "Sec"
,trim(cr.argument_text) arguments,
r.responsibility_name
from apps.fnd_concurrent_requests cr,apps.fnd_responsibility_tl r where cr.concurrent_program_id =
( select concurrent_program_id
from apps.fnd_concurrent_programs_tl
where user_concurrent_program_name like 'Launch Supply Chain Planning Process'
and language='US')
--and actual_start_date >sysdate -7
--and cr.argument_text like '%MPS-ATP%'
--and phase_code='P'
--and status_code='X'
and cr.responsibility_application_id = r.application_id
and cr.responsibility_id = r.responsibility_id
--and request_id=210692360
and language='US'
order by actual_start_date desc)
/

set lines 180
set pages 100
col Parameters for a20 WORD_WRAPPED
set pages 100
col "Conc Program Name" for a30 WORD_WRAPPED
col "Started at" for a20
col "Completed at" for a20
col "Username" for a10 WORD_WRAPPED
SELECT distinct t.user_concurrent_program_name "Conc Program Name",
r.REQUEST_ID "Request ID",
to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
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",
u.user_name "Username",
--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
-- 2
-- ) "Runtime (in Minutes)"
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
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
AND r.actual_start_date >= (sysdate - &NO_DAYS)
--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 r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

With Concurrent request set Details

SET LINES 180;
SET PAGES 100;
COLUMN "Conc Program Name" FOR A50 WORD_WRAPPED;
COLUMN "Started at" FOR A20;
COLUMN "Completed at" FOR A20;
COLUMN "Username" FOR A10 WORD_WRAPPED;
COLUMN "Parameters" FOR A20 WORD_WRAPPED;

SELECT DISTINCT
t.user_concurrent_program_name ||
CASE
WHEN t.user_concurrent_program_name = 'Report Set' THEN
(SELECT ' - ' || s.user_request_set_name
FROM apps.fnd_request_sets_tl s
WHERE s.application_id = r.argument1
AND s.request_set_id = r.argument2
AND s.language = 'US')
WHEN t.user_concurrent_program_name = 'Check Periodic Alert' THEN
(SELECT ' - ' || a.alert_name
FROM apps.alr_alerts a
WHERE a.application_id = r.argument1
AND a.alert_id = r.argument2
)
ELSE ''
END AS "Conc Program Name",
r.REQUEST_ID AS "Request ID",
TO_CHAR(r.ACTUAL_START_DATE, 'dd-MON-yy hh24:mi:ss') AS "Started at",
TO_CHAR(r.ACTUAL_COMPLETION_DATE, 'dd-MON-yy hh24:mi:ss') AS "Completed at",
DECODE(r.PHASE_CODE,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'NA') AS "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') AS "Status",
r.argument_text AS "Parameters",
u.user_name AS "Username",
ROUND(((NVL(v.actual_completion_date, SYSDATE) - v.actual_start_date) * 24 * 60), 2) AS "ElapsedTime(Mins)"
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
AND r.actual_start_date >= (SYSDATE - &NO_DAYS)
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
ORDER BY
TO_CHAR(r.ACTUAL_COMPLETION_DATE, 'dd-MON-yy hh24:mi:ss') DESC;