Concurrent Program Monitoring Scripts

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