PL/SQL Sample Procedure To Check Status Of Internal Manager, Conflict Resolution Manager and Standard Manager in Oracle Apps R12

The below is a sample procedure which check the status of Internal Manager, Conflict Resolution Manager and Standard Manager in Oracle Apps R12. If any one of them is down then it returns ERROR status.

Create the below types

• create or replace type CM_STATUS_TYPE  as object

            ( 
               cm_name varchar2(100),
       target_processes  number,                                   
       actual_processes number
    );

• create or replace type CM_STATUS_TBL  is table of CM_STATUS_TYPE;

Create the following Procedure :

PROCEDURE cm_status_check(   p_status out VARCHAR2,

                             p_status_msg  out VARCHAR2)

AS

v_cm_detail cm_status_tbl;

v_count_correct_cm NUMBER;

BEGIN

       BEGIN

        -- Select the detail of Internal Manager, Conflict Resolution Manager and Standard Manager.

        SELECT cm_status_type(concurrent_queue_name,

          max_processes,

          running_processes)  

          BULK COLLECT INTO v_cm_detail

        FROM apps.fnd_concurrent_queues

        WHERE concurrent_queue_name IN ('FNDICM','FNDCRM','STANDARD');

        exception

        WHEN others THEN

        p_status:='ERROR';

        p_status_msg := 'Error in selecting the CM details'||sqlerrm;

        END;

        

        v_count_correct_cm := 0;

        -- The above query will give the detail of Internal Manager, Conflict Resolution Manager and Standard Manager.

        

        FOR i IN v_cm_detail.FIRST .. v_cm_detail.LAST

        loop

          -- if this condition checks the status of all three CMs 

          IF((v_cm_detail(i).target_processes != 0) AND (v_cm_detail(i).actual_processes!=0) AND         (v_cm_detail(i).target_processes = v_cm_detail(i).actual_processes)) THEN

           v_count_correct_cm:=v_count_correct_cm+1;

          END IF;

        END loop;

        IF v_count_correct_cm = 3 THEN

          p_status :='SUCCESS';

          p_status_msg :='Internal Manager, Conflict Resolution Manager and Standard Manager Is Working Fine.';

        ELSE

          p_status :='ERROR';

          p_status_msg :='Internal Manager, Conflict Resolution Manager and Standard Manager Needs To Be Restarted.';

        END IF;

END  cm_status_check; 

Happy Coding :)