SQL Query to find out the status of Interface Mangers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

/**************************************************************************
 *PURPOSE: To find out the status of Interface Mangers                    *
 *PARAMETERS: Name of Interface Manger(Optional)                          *
 **************************************************************************/
SELECT x.process_type "Name",
  DECODE(
  (SELECT '1' FROM fnd_concurrent_requests cr,
    fnd_concurrent_programs_vl cp,
    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",
  x.worker_rows "Worker Rows",
  x.timeout_hours "Timeout Hours",
  x.timeout_minutes "Timeout Minutes",
  x.process_hours "Process Interval Hours",
  x.process_minutes "Process Interval Minutes",
  x.process_seconds "Process Interval Seconds"
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 mtl_interface_proc_controls mipc,
    mfg_lookups a
  WHERE a.lookup_type = 'PROCESS_TYPE'
  AND a.lookup_code   = mipc.process_code
  ) x
  -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager
ORDER BY 1;

/************************************************************************** *PURPOSE: To find out the status of Interface Mangers * *PARAMETERS: Name of Interface Manger(Optional) * **************************************************************************/ SELECT x.process_type "Name", DECODE( (SELECT '1' FROM fnd_concurrent_requests cr, fnd_concurrent_programs_vl cp, 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", x.worker_rows "Worker Rows", x.timeout_hours "Timeout Hours", x.timeout_minutes "Timeout Minutes", x.process_hours "Process Interval Hours", x.process_minutes "Process Interval Minutes", x.process_seconds "Process Interval Seconds" 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 mtl_interface_proc_controls mipc, mfg_lookups a WHERE a.lookup_type = 'PROCESS_TYPE' AND a.lookup_code = mipc.process_code ) x -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager ORDER BY 1;