Get Program Name, Schedule date, Responsibility name, Requestor, Status, Phase, Start Time, End Time, Arguments from request Id in oracle apps

SELECT DECODE (fcrs.description,

               NULL, fcrs.user_concurrent_program_name,

               fcrs.description)

          Program_Name,

       TO_CHAR (fcrs.requested_start_date, 'Dy HH24:MI') Schedule_Date,

       fcrs.request_id,

       fresp.responsibility_name,

       fcrs.requestor,

       (SELECT meaning

          FROM apps.fnd_lookups

         WHERE     lookup_type = 'CP_STATUS_CODE'

               AND lookup_code = fcrs.status_code)

          Status,

       (SELECT meaning

          FROM apps.fnd_lookups

         WHERE     lookup_type = 'CP_PHASE_CODE'

               AND lookup_code = fcrs.status_code)

          Phase_Status,

       fcrs.actual_start_date Start_Time,

       fcrs.actual_completion_date End_Time,

       fcr.argument_text

  FROM apps.fnd_conc_req_summary_v fcrs,

       apps.fnd_responsibility_vl fresp,

       fnd_concurrent_requests fcr

 WHERE     fcrs.responsibility_id = fresp.responsibility_id

       AND fcrs.request_id = fcr.request_id

       AND fcrs.request_id IN ('Enter request Id');

Get Child Request of Concurrent Request and other details in Oracle Apps

SELECT

  /*+ USE_CONCAT */

  CONCURRENT_PROGRAM_ID,

  (SELECT fcpt.user_concurrent_program_name

  FROM fnd_concurrent_programs_tl fcpt

  WHERE fcpt.concurrent_program_id=fcr.concurrent_program_id

  ) name,

  (SELECT meaning

  FROM apps.fnd_lookups

  WHERE lookup_type = 'CP_STATUS_CODE'

  AND lookup_code   = fcr.status_code

  )status,

  (SELECT meaning

  FROM apps.fnd_lookups

  WHERE lookup_type = 'CP_PHASE_CODE'

  AND lookup_code   = fcr.phase_code

  )phase,

  logfile_name log_file,

  PROGRAM_APPLICATION_ID,

  PROGRAM_SHORT_NAME,

  ARGUMENT_TEXT,

  ACTUAL_COMPLETION_DATE,

  COMPLETION_TEXT,

  PARENT_REQUEST_ID,

  FCP_REQUIRED_STYLE,

  LAST_UPDATE_DATE,

  LAST_UPDATED_BY,

  REQUESTED_BY,

  RESPONSIBILITY_APPLICATION_ID,

  RESPONSIBILITY_ID,

  ENABLED,

  REQUESTED_START_DATE,

  PHASE_CODE,

  HOLD_FLAG,

  STATUS_CODE,

  REQUEST_ID,

  PROGRAM,

  REQUESTOR

FROM apps.FND_CONC_REQ_SUMMARY_V fcr

WHERE (DECODE(IMPLICIT_CODE, 'N', STATUS_CODE,'E', 'E', 'W', 'G') = STATUS_CODE

OR DECODE(IMPLICIT_CODE, 'W', 'E')                                = STATUS_CODE)

AND (((parent_request_id                                          = :req_id

AND request_type                                                  = 'S')

OR (request_id                                                    = :req_id)

OR ((priority_request_id                                          = :req_id

AND request_type                                                  = 'P')

OR (parent_request_id                                             = :req_id

AND request_type                                                  = 'P'))

OR (priority_request_id                                           = :req_id

AND (has_sub_request                                              = 'Y'

OR is_sub_request                                                 = 'Y'))) )

ORDER BY REQUEST_ID DESC