To Get Concurrent Request Details

SELECT r.request_id,

       DECODE (r.description,

               NULL, pt.user_concurrent_program_name,

               r.description || ' ( ' || pt.user_concurrent_program_name

               || ' ) '

              ) program,

       r.phase_code,

       r.status_code,

       fnd_amp_private.get_phase (r.phase_code,

                                  r.status_code,

                                  r.hold_flag,

                                  p.enabled_flag,

                                  r.requested_start_date,

                                  r.request_id

                                 ) phase,

       fnd_amp_private.get_status (r.phase_code,

                                   r.status_code,

                                   r.hold_flag,

                                   p.enabled_flag,

                                   r.requested_start_date,

                                   r.request_id

                                  ) status,

       u.user_name,

       rt.responsibility_name,

       AT.application_name,

       r.requested_start_date,

       r.actual_start_date,

       r.actual_completion_date,

       r.request_date,

       r.argument_text,

       r.oracle_session_id,

       r.completion_text,

       r.controlling_manager,

       r.requested_by,

       r.program_application_id,

       r.concurrent_program_id,

       r.is_sub_request,

       r.parent_request_id,

       r.queue_method_code,

       r.cd_id,

       r.hold_flag,       

       p.enabled_flag,

       p.concurrent_program_name,

       p.user_concurrent_program_name,

       r.os_process_id,

       r.nls_language,

       r.nls_territory,

       r.nls_numeric_characters,

       r.description

  FROM fnd_concurrent_programs_tl pt,

       fnd_responsibility_tl rt,

       fnd_application_tl AT,

       fnd_concurrent_programs_vl p,

       fnd_user u,

       fnd_concurrent_requests r

 WHERE r.program_application_id = p.application_id

   AND r.concurrent_program_id  = p.concurrent_program_id

   AND pt.concurrent_program_id = p.concurrent_program_id

   AND pt.application_id        = p.application_id

   AND pt.LANGUAGE              = USERENV ('LANG')

   AND u.user_id                = r.requested_by

   AND rt.application_id        = r.responsibility_application_id

   AND rt.responsibility_id     = r.responsibility_id

   AND rt.LANGUAGE              = USERENV ('LANG')

   AND AT.application_id        = r.program_application_id

   AND AT.LANGUAGE              = USERENV ('LANG')

   AND r.request_id             = :request_id;