SQL> SELECT DISTINCT far.request_id, SUBSTR (program, 1, 30), far.user_name,
far.phase_code, far.status_code, phase, status,
(CASE
WHEN far.phase_code = 'P' AND far.hold_flag = 'Y'
THEN 'Job is on Hold by user'
WHEN far.phase_code = 'P'
AND (far.status_code = 'I' OR far.status_code = 'Q')
AND far.requested_start_date > SYSDATE
THEN 'Job is scheduled to run at '
|| TO_CHAR (far.requested_start_date,
'DD-MON-RR HH24:MI:SS'
)
WHEN far.phase_code = 'P'
AND (far.status_code = 'I' OR far.status_code = 'Q')
AND fcp.queue_control_flag = 'Y'
THEN 'ICM will run ths request on its next sleep cycle'
WHEN far.phase_code = 'P' AND far.status_code = 'P'
THEN 'Scheduled to be run by the Advanced Scheduler'
WHEN far.queue_method_code NOT IN ('I', 'B')
THEN 'Bad queue_method_code of: '
|| far.queue_method_code
WHEN far.run_alone_flag = 'Y'
THEN 'Waiting on a run alone request'
WHEN far.queue_method_code = 'B'
AND far.status_code = 'Q'
AND EXISTS (
SELECT 1
FROM fnd_amp_requests_v farv
WHERE phase_code = 'P'
AND program_application_id =
fcps.to_run_application_id
AND concurrent_program_id =
fcps.to_run_concurrent_program_id)
THEN 'Incompatible request '
|| (SELECT DISTINCT farv.request_id
|| ': '
|| farv.program
|| ' is Ruuning by : '
|| farv.user_name
FROM fnd_amp_requests_v farv,fnd_concurrent_program_serial fcps1
WHERE phase_code = 'R'
AND program_application_id =fcps1.running_application_id
AND concurrent_program_id =fcps1.running_concurrent_program_id
AND fcps.to_run_application_id=fcps1.to_run_application_id
AND fcps.to_run_concurrent_program_id=fcps1.to_run_concurrent_program_id)
WHEN fcp.enabled_flag = 'N'
THEN 'Concurrent program is disabled'
WHEN far.queue_method_code = 'I' AND far.status_code = 'Q'
THEN 'This Standby request might not run'
WHEN far.queue_method_code = 'I' AND far.status_code = 'I'
THEN 'Waiting for next available '
|| fcqt.user_concurrent_queue_name
|| ' process to run the job. Estimate Wait time '
|| fcq.sleep_seconds
|| ' Seconds'
WHEN far.queue_method_code = 'I'
AND far.status_code IN ('A', 'Z')
THEN 'Waiting for Parent Request: '
|| NVL (far.parent_request_id,
'Could not locate Parent Request ID'
)
WHEN far.queue_method_code = 'B' AND far.status_code = 'Q'
THEN 'Waiting on the Conflict Resolution Manager'
WHEN far.queue_method_code = 'B' AND far.status_code = 'I'
THEN 'Waiting for next available '
|| fcqt.user_concurrent_queue_name
|| ' process to run the job. Estimate Wait time '
|| fcq.sleep_seconds
|| ' Seconds'
WHEN far.phase_code = 'P' AND far.single_thread_flag = 'Y'
THEN 'Single-threaded request. Waiting on other requests for this user.'
WHEN far.phase_code = 'P' AND far.request_limit = 'Y'
THEN 'Concurrent: Active Request Limit is set. Waiting on other requests for this user.'
END
) reason
FROM fnd_amp_requests_v far,
fnd_concurrent_programs fcp,
fnd_conflicts_domain fcd,
fnd_concurrent_program_serial fcps,
fnd_concurrent_queues fcq,
fnd_concurrent_queue_content fcqc,
fnd_concurrent_queues_tl fcqt
WHERE far.phase_code = 'P'
AND far.concurrent_program_id = fcp.concurrent_program_id
AND fcd.cd_id = far.cd_id
AND fcps.running_application_id(+) = far.program_application_id
AND fcps.running_concurrent_program_id(+) = far.concurrent_program_id
AND far.program_application_id = fcps.to_run_application_id(+)
AND far.concurrent_program_id = fcps.to_run_concurrent_program_id(+)
AND far.concurrent_program_id = fcqc.type_id(+)
AND far.program_application_id = fcqc.type_application_id(+)
AND fcq.concurrent_queue_id(+) = fcqc.concurrent_queue_id
AND fcq.application_id(+) = fcqc.queue_application_id
AND fcqt.concurrent_queue_id(+) = fcq.concurrent_queue_id
AND fcqt.application_id(+) = fcq.application_id
ORDER BY far.request_id DESC
/
+++++++++++++++++++++++++
select
fcr.request_id,
fcr.parent_request_id,
fu.user_name requestor,
to_char(fcr.requested_start_date, 'MON-DD-YYYY HH24:MM:SS') START_DATE,
fr.responsibility_key responsibility,
fcp.concurrent_program_name,
fcpt.user_concurrent_program_name,
decode(fcr.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,
decode(fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') phase,
fcr.completion_text
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu,
fnd_responsibility fr
where
fcr.status_code in ('Q', 'I') and
fcr.hold_flag = 'N' and
fcr.requested_start_date > sysdate and
fu.user_id = fcr.requested_by and
fcr.concurrent_program_id = fcp.concurrent_program_id and
fcr.concurrent_program_id = fcpt.concurrent_program_id and
fcr.responsibility_id = fr.responsibility_id
order by
fcr.requested_start_date, fcr.request_id;
-- Query to find concurrent request status related information
-------------------------------------------------------------------------------
SELECT fu.user_name "User ID",
frt.responsibility_name "Responsibility Used",
fcr.request_id "Request ID",
fcpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(fcr.phase_code,
'C', 'Completed',
'P', 'Pending',
'R', 'Running',
'I', 'Inactive',
fcr.phase_code) "Phase",
DECODE(fcr.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',
fcr.status_code) "Status",
fcr.request_date "Request Date",
fcr.requested_start_date "Request Start Date",
fcr.hold_flag "Hold Flag",
fcr.printer "Printer Name",
fcr.parent_request_id "Parent Request ID"
-- fcr.number_of_arguments,
-- fcr.argument_text,
-- fcr.logfile_name,
-- fcr.outfile_name
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt
WHERE fu.user_id = fcr.requested_by
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
-- AND fcr.request_id = 30302146 -- <change it>
AND fcpt.user_concurrent_program_name = 'INV Item Copy' -- <change it>
ORDER BY fcr.request_date DESC;
++++++++++++To know more details on Specific request+++++++++++
select /*+ CHOOSE*/
'Node Name..............................: ' || q.node_name || chr(10) ||
'Req id.................................: ' || Request_Id || chr(10) ||
'Requestor..............................: ' || User_Name || chr(10) ||
'Manager................................: ' || Q.User_Concurrent_Queue_Name || chr(10) ||
'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
'W', 'Paused', 'X','Terminated', status_code) || chr(10) ||
'Phase code.............................: ' || decode(phase_code, 'C', 'Completed',
'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code) || chr(10) ||
'Priority...............................: ' || Fcr.priority || chr(10) ||
'Program................................: ' || Fcp.User_Concurrent_Program_Name || chr(10) ||
'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
'Avg execution time in 30 days.........: ' || trunc(AVG_TIME,2) ||' min' || chr(10) ||
'Max execution time in 30 days..........: ' || trunc(MAX_TIME,2) ||' min' || chr(10) ||
'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min' || chr(10) ||
'Number of executions in last 30 days...: ' ||occurance || chr(10) ||
'ClientPID..............................: ' || Fcr.OS_PROCESS_ID || chr(10) ||
'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID || chr(10) ||
'Arguments passed to the program .......: ' ||Fcr.argument_text
from apps.Fnd_Concurrent_Requests Fcr,
apps.Fnd_Concurrent_Programs_vl Fcp,
apps.Fnd_Oracle_Userid O,
apps.Fnd_Concurrent_Processes P,
apps.Fnd_Concurrent_Queues_vl Q,
apps.Fnd_User,(select
concurrent_program_id
,count(concurrent_program_id) occurance
,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
, avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
from apps.fnd_concurrent_requests
where status_code='C' and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id
having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
where Controlling_Manager = Concurrent_Process_ID
and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
and( Fcr.Program_Application_Id=Fcp.Application_Id
and Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
and Requested_By = User_Id
and Phase_Code = 'R' and status_code in ('R','T')
--adding joins with new
and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
Order By Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;
+++++++++++++++++++++
Thanks alot Admin.. Looking for these scripts from long time...
ReplyDelete