Query to Get Concurrent Program details in Oracle Apps

--Concurrent Programs details

SELECT fcp.concurrent_program_id,

       fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcpt.description,

       fe.executable_name,

       fet.user_executable_name,

       fe.execution_file_name

  FROM apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_executables fe,

       apps.fnd_executables_tl fet

 WHERE     fe.executable_id = fet.executable_id

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fcpt.language = fet.language

       AND fcp.executable_id = fe.executable_id

       AND fcp.executable_application_id = fe.application_id

       AND fcpt.language = 'US'

       AND fcpt.user_concurrent_program_name LIKE

              'Meter Collection Main Program'

--To find Responsibilities having a particular Concurrent Program

  SELECT frt.responsibility_name, fcpt.user_concurrent_program_name

    FROM fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt,

         fnd_request_group_units frgu,

         fnd_responsibility fr,

         fnd_responsibility_tl frt

   WHERE     fcp.concurrent_program_id = fcpt.concurrent_program_id

         AND fcp.application_id = fcpt.application_id

         AND fcp.concurrent_program_id = frgu.request_unit_id(+)

         AND frgu.unit_application_id(+) = fcp.application_id

         AND frgu.request_group_id = fr.request_group_id(+)

         AND fr.responsibility_id = frt.responsibility_id(+)

         AND fr.application_id = frt.application_id(+)

         AND frgu.request_unit_type(+) = 'P'

         AND frt.language(+) = 'US'

         AND fcpt.language = 'US'

         AND fcpt.user_concurrent_program_name LIKE 'I%Materialized%'

ORDER BY frt.responsibility_name

--Concurrent Request Details

SELECT           

        fcr.request_id request_id,

         NVL (fu.description, fu.user_name) requested_by,

         DECODE (fcp.concurrent_program_name,

                 'FNDRSSUB', 'Request Set - ' || fcr.description,

                 fcpt.user_concurrent_program_name)

            concurrent_program,

         DECODE (fcr.phase_code,

                 'I', 'Inactive',

                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'),

                 'R', 'Running',

                 'C', 'Complete',

                 fcr.phase_code)

            phase,

         DECODE (

            fcr.status_code,

            'U', 'Disabled',

            'W', 'Paused',

            'X', 'Terminated',

            'Z', 'Waiting',

            'M', 'No Manager',

            'Q', 'Standby',

            'R', 'Normal',

            'S', 'Suspended',

            'T', 'Terminating',

            'D', 'Cancelled',

            'E', 'Error',

            'F', 'Scheduled',

            'G', 'Warning',

            'H', 'On Hold',

            'I', CASE

                    WHEN fcr.request_date < fcr.requested_start_date

                    THEN

                       'Scheduled'

                    ELSE

                       'Normal'

                 END,

            'A', 'Waiting',

            'B', 'Resuming',

            'C', 'Normal',

            fcr.status_code)

            status,

         fcr.argument_text,

         TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_requested,

         TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')

            requested_start_date,

         TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')

            date_started,

         TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH12:MI:SS AM')

            date_completed,

         fcr.oracle_process_id,

         fcr.os_process_id,

         fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name,

         fcr.outfile_node_name || ': ' || fcr.outfile_name output_name

    FROM fnd_concurrent_requests fcr,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt,

         fnd_user fu

   WHERE     fcr.concurrent_program_id = fcp.concurrent_program_id

         AND fcp.concurrent_program_id = fcpt.concurrent_program_id

         AND fcr.program_application_id = fcp.application_id

         AND fcp.application_id = fcpt.application_id

         AND fcr.requested_by = fu.user_id

         AND fcpt.language = 'US'

ORDER BY fcr.request_date DESC