Finding concurrent request status related information

The following query finds the concurrent process status and its related information (such as, completion phase, responsibility used, which user submitted the program, etc.).

In the following example, "Autoinvoice Import Program" as Concurrent Program Name. This is well tested in R12.1.


 SELECT
       fu.user_name                           "User ID",
       frt.responsibility_name                "Responsibility Used",
       fcr.request_id                         "Request ID",
       fcpt.user_concurrent_program_name      "Concurrent Program Name",
       flv2.meaning                           "Phase",
       flv1.meaning                           "Status",
       fcr.request_date                       "Request Date",
       fcr.requested_start_date               "Request Start Date",
       fcr.parent_request_id                  "Parent Request ID"
  FROM
       fnd_user                    fu,
       fnd_responsibility_tl       frt,
       fnd_concurrent_requests     fcr,
       fnd_concurrent_programs_tl  fcpt,
       fnd_lookup_values           flv1,
       fnd_lookup_values           flv2
 WHERE
       1=1
   AND flv1.lookup_code           =  fcr.status_code
   AND flv1.lookup_type           =  'CP_STATUS_CODE'
   AND flv1.enabled_flag          =  'Y'
   AND flv1.view_application_id   <> 0
   AND flv2.lookup_code           =  fcr.phase_code
   AND flv2.lookup_type           =  'CP_PHASE_CODE'
   AND flv2.enabled_flag          =  'Y'
   AND flv2.view_application_id   <> 0
   AND fu.user_id                 =  fcr.requested_by
   AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id
   AND fcr.responsibility_id      =  frt.responsibility_id
   AND frt.LANGUAGE               =  USERENV ('LANG')
   AND fcpt.LANGUAGE              =  USERENV ('LANG')
   AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program' 
 ORDER BY fcr.request_date DESC;