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