Tuesday, March 13, 2012

Query to see whether cost worker is running or not

Select Fcpl.user_concurrent_program_Name Program ,
Fcr.Request_Id Req_id ,
Resubmit_Interval Submit_Interval ,
To_Char (Trunc (Sysdate) + Numtodsinterval ( (Nvl(Actual_completion_date, Sysdate) - Actual_start_date)* (24 * 60 * 60), 'second'), 'hh24:mi:ss') Time_Taken,
Actual_Start_Date ,
Actual_Completion_Date ,
Sysdate System_Time ,
Phase_Code ,
( Select Meaning
from Apps.Fnd_Lookups fl
where fl.lookup_code = Phase_Code
and Lookup_Type = 'CP_PHASE_CODE'
) Phase_Code_Meaning ,
Status_Code ,
( Select Meaning
from Apps.Fnd_Lookups fl
where fl.lookup_code = Status_Code
and Lookup_Type = 'CP_STATUS_CODE'
) Status_Code_Meaning,
Fcr.*
From Apps.Fnd_Concurrent_Programs_Tl Fcpl,
Apps.Fnd_Concurrent_Requests Fcr
Where Fcpl.concurrent_program_id = Fcr.concurrent_program_id
And user_concurrent_program_Name
In ('Update Standard Costs', 'Cost Manager', 'Material cost transaction worker', 'Resource Cost Worker' )
And (Status_Code Not In ('X', 'C', 'E', 'D', 'G') Or Phase_Code <> 'C')
And Actual_Start_Date > Sysdate - 3 ;