REM Use this select statement to display status, run duration,
REM output file(s), log file(s) for a particular concurrent
REM program or all the concurrent programs created so far.
REM This program has one parameter concurrent request id, the
REM possible values are a valid concurrent request id or enter
REM key to list the details of all the programs.

SET pagesize 50000
Accept request_id Prompt "Enter Request Id(or Press Enter to get all): "
Select
'Program Name:  '||decode(CP.user_concurrent_program_name,'Report Set', substr(R.description,1,40),
                        substr(CP.user_concurrent_program_name,1,40)) Program_Name,
'Program Id:    '||R.request_id,
'Queue Name:    '||Concurrent_Queue_Name Queue_Name,
'Duration:      '||to_char(Actual_Start_date, 'MON-DD-YY HH:MI:SS AM') || ' - to - ' ||
                to_char(Actual_COMPLETION_date, 'MON-DD-YY HH:MI:SS AM') Duration,
'Mgr. Log File: '||P.Logfile_Name Manager_Log_File,
'Log File:      '||R.Logfile_Name Concurrent_Log_File,
'Out File:      '||R.Outfile_name Concurrent_Output_File,
'Program Status:'||F.meaning Status,
'Printer:       '||r.printer,
'User Name:     '||substr((R.requested_by||'-'||FU.user_name),1,20) UserName
From  Fnd_Concurrent_Queues Q,
      Fnd_Concurrent_requests R,
      Fnd_Concurrent_Processes P,
      Fnd_Concurrent_Programs_tl CP,
      Fnd_lookups F,
      Fnd_user fu
Where  (P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID
  And  P.Queue_Application_ID  = Q.Application_ID)
  And  R.Controlling_Manager = P.Concurrent_Process_ID
  And  R.Phase_Code = 'C'
  And  Request_ID like '%&&Request_ID%'
  And  R.status_code  = F.lookup_code
  AND  F.lookup_type = 'CP_STATUS_CODE'
  AND  R.program_application_id = CP.application_id
  AND  R.concurrent_program_id = CP.concurrent_program_id
  AND  R.requested_by=FU.user_id
order by 2
/

Return to : Oracle Database, SQL, Application, Programming Tips