Most frequently in support projects we need to monitor and track the concurrent programs. Here are some the important queries to track the status of concurrent program. These are also very useful for the oracle apps system administrator
--------- Terminating requests----------
SELECT Request_Id Reqst,
user_concurrent_program_name concurrent_program,
User_Name Requestor,
Oracle_Username Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs_tl Fcp,
Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
Fnd_User
Fnd_User
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 ( 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 = 'T'
ORDER BY Actual_Start_Date, Request_Id
------ Currently running requests ------------
SELECT Request_Id Reqst,
User_Name Requestor,
Oracle_Username Orcl,
Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program,
user_concurrent_program_name conc_program,
user_concurrent_program_name conc_program,
TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Fcr.Logfile_Name LN,
Run_Alone_Flag, Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,
fnd_concurrent_programs_tl fcpt,
Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q, Fnd_User
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 ( Fcr.Program_Application_Id = Fcp.Application_Id
AND Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
AND (Fcpt.concurrent_program_id = Fcp.Concurrent_program_id)
AND Requested_By = User_Id
AND Phase_Code = 'R' AND Status_Code = 'R'
ORDER BY Actual_Start_Date, Request_Id
--- Summary of concurrent request execution since Date ---
SELECT L1.Meaning Request_Type,
L2.Meaning Status,
COUNT(Status_Code) NR
FROM Fnd_Concurrent_Requests R,
Fnd_Concurrent_Programs P,
Fnd_Lookups L1,
Fnd_Lookups L2
WHERE
L1.Lookup_Code = P.Execution_Method_Code
AND L1.Lookup_Type = 'CP_EXECUTION_METHOD_CODE'
AND (R.Program_Application_ID = P.Application_ID
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND (R.Status_Code = L2.Lookup_Code
AND L2.Lookup_Type = 'CP_STATUS_CODE' )
AND R.Phase_Code = 'C'
AND R.Actual_Completion_Date > SYSDATE - 5
GROUP BY
L1.Meaning, L2.Meaning
--------Errored programs on a day ------------
SELECT fcp.user_concurrent_program_name
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp
WHERE status_code = 'E'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND TRUNC(requested_start_date) = TRUNC(SYSDATE)
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
---- Managers that are currently running a request -----
SELECT Concurrent_Queue_Name Manager,
Request_Id Request,
User_name,
Concurrent_Program_Name Program,
Run_Alone_Flag,
TO_CHAR(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
FROM Fnd_Concurrent_Queues Fcq,
Fnd_Concurrent_Requests Fcr,
Fnd_Concurrent_Programs Fcp,
Fnd_User Fu,
Fnd_Concurrent_Processes Fpro
WHERE
Phase_Code = 'R' AND
Fcr.Controlling_Manager = Concurrent_Process_Id AND (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id AND
Fcq.Application_Id = Fpro.Queue_Application_Id ) AND (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id AND Fcr.Program_Application_Id = Fcp.Application_Id ) AND
Fcr.Requested_By = User_Id
