Wednesday, July 8, 2015

SQL Query for Interface Managers

--Admin Interface Managers
SELECT   x.process_type "Name",
         DECODE ((SELECT '1'
                    FROM apps.fnd_concurrent_requests cr,
                         apps.fnd_concurrent_programs_vl cp,
                         apps.fnd_application a
                   WHERE cp.concurrent_program_id = cr.concurrent_program_id
                     AND cp.concurrent_program_name = x.process_name
                     AND cp.application_id = a.application_id
                     AND a.application_short_name = x.process_app_short_name
                     AND phase_code != 'C'),
                 '1', 'Active',
                 'Inactive'
                ) "Status"
    FROM (SELECT mipc.process_code, mipc.process_status,
                 mipc.process_interval, mipc.manager_priority,
                 mipc.worker_priority, mipc.worker_rows,
                 mipc.processing_timeout, mipc.process_name,
                 mipc.process_app_short_name, a.meaning process_type,
                 FLOOR (mipc.process_interval / 3600) process_hours,
                 FLOOR (  (  mipc.process_interval
                           - (FLOOR (mipc.process_interval / 3600) * 3600)
                          )
                        / 60
                       ) process_minutes,
                 (  mipc.process_interval
                  - (FLOOR (mipc.process_interval / 3600) * 3600)
                  - (  FLOOR (  (  mipc.process_interval
                                 - (FLOOR (mipc.process_interval / 3600)
                                    * 3600
                                   )
                                )
                              / 60
                             )
                     * 60
                    )
                 ) process_seconds,
                 FLOOR (mipc.processing_timeout / 3600) timeout_hours,
                 FLOOR (  (  mipc.processing_timeout
                           - FLOOR (mipc.processing_timeout / 3600) * 3600
                          )
                        / 60
                       ) timeout_minutes
            FROM apps.mtl_interface_proc_controls mipc, apps.mfg_lookups a
           WHERE a.lookup_type = 'PROCESS_TYPE'
             AND a.lookup_code = mipc.process_code) x
   WHERE DECODE ((SELECT '1'
                    FROM apps.fnd_concurrent_requests cr,
                         apps.fnd_concurrent_programs_vl cp,
                         apps.fnd_application a
                   WHERE cp.concurrent_program_id = cr.concurrent_program_id
                     AND cp.concurrent_program_name = x.process_name
                     AND cp.application_id = a.application_id
                     AND a.application_short_name = x.process_app_short_name
                     AND phase_code != 'C'),
                 '1', 'Active',
                 'Inactive'
                ) = 'Inactive'
ORDER BY 1;

No comments: