****HISTORY OF CONCURRENT REQUEST - SCRIPT (PROGRAM WISE) *****
set pagesize 200
set linesize 200
col "Who submitted" for a25
col "Status" for a10
col "Parameters" for a20
col USER_CONCURRENT_PROGRAM_NAME for a42
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",
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",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
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-30)
--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 t.user_concurrent_program_name like '%%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');
To find child requests for Parent request id.
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name,
DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code)
status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where
req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
--Query to verify the user details to whom a specific Responsibility has been assigned.
SELECT * FROM FND_USER_RESP_GROUPS_DIRECT
WHERE responsibility_ID IN (SELECT responsibility_id FROM apps.FND_RESPONSIBILITY_TL
WHERE responsibility_name LIKE '%responsibility_name')
--Query to Identify the Concurrent Request Submission details + Responsibility Name for a given Concurrent Request_Name
SELECT b.responsibility_name, a.* FROM apps.fnd_concurrent_requests a, apps.FND_RESPONSIBILITY_TL b
WHERE a.responsibility_id = b.responsibility_id
AND request_id = '&request_id'
set pagesize 200
set linesize 200
col "Who submitted" for a25
col "Status" for a10
col "Parameters" for a20
col USER_CONCURRENT_PROGRAM_NAME for a42
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",
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",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
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-30)
--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 t.user_concurrent_program_name like '%%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');
To find child requests for Parent request id.
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name,
DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code)
status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where
req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
--Query to verify the user details to whom a specific Responsibility has been assigned.
SELECT * FROM FND_USER_RESP_GROUPS_DIRECT
WHERE responsibility_ID IN (SELECT responsibility_id FROM apps.FND_RESPONSIBILITY_TL
WHERE responsibility_name LIKE '%responsibility_name')
--Query to Identify the Concurrent Request Submission details + Responsibility Name for a given Concurrent Request_Name
SELECT b.responsibility_name, a.* FROM apps.fnd_concurrent_requests a, apps.FND_RESPONSIBILITY_TL b
WHERE a.responsibility_id = b.responsibility_id
AND request_id = '&request_id'
--Query to fetch Request_group Name(s), Responsibility Name(s) to which a particular Concurrent Request is Registered
SELECT DISTINCT a.concurrent_program_id,a.USer_concurrent_program_name Request_name, c.Request_group_name,
e.responsibility_name FROM apps.FND_CONCURRENT_PROGRAMS_TL a,
apps.FND_REQUEST_GROUP_UNITS b, apps.FND_REQUEST_GROUPS c,apps.FND_RESPONSIBILITY d,
apps.FND_RESPONSIBILITY_TL e
WHERE a.concurrent_program_id = b.request_unit_id
AND b.request_group_id = c.request_group_id
AND b.request_group_id = d.request_group_id
AND d.responsibility_id = e.responsibility_id
AND a.USer_concurrent_program_name LIKE '&a.USer_concurrent_program_name'
No comments:
Post a Comment