Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Wednesday, 18 October 2017

Query to Get Concurrent Program details in Oracle Apps

0 comments

--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
Continue reading >>