Concurrent Program Monitoring Scripts


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_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 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,
      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.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