Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/mos/12.2.0.0/mos/mos_target_progress.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem
Rem mos_target_progress.sql
Rem
Rem Copyright (c) 2014, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/mos/12.2.0.0/mos/mos_target_progress.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/mos/12.2.0.0/mos/mos_target_progress.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      mos_target_progress.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/mos/12.2.0.0/mos/mos_target_progress.sql 
Rem    SQL_SHIPPED_FILE: 
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE 
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/21/25 - make synonym non editionable
Rem    zhxiong     08/09/15 - Bug 21545614 - EBR : ps3 to 13.1 upgrade objects
Rem                           MGMT$ARU_PATCH* and EM_PC* donot have EVs
Rem    amikhare    07/10/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    amikhare    06/19/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    amikhare    06/05/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    amikhare    04/14/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    amikhare    02/05/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    amikhare    01/29/15 - Dropping triggers on table before recreating them
Rem                           on editioning view.
Rem    akmaurya    05/26/14 - Target Progress Status
Rem    akmaurya    05/26/14 - Created
Rem

rem Target Progress Status
rem
rem  PURPOSE
rem
rem     The EM_PC_TARGET_PROGRESS_E table contains target progress status.
rem
rem  COLUMNS
rem
rem     PLAN_GUID                   - Plan Guid
rem     TARGET_GUID                 - Target Guid
rem     STATUS                      - Target Status
rem     PROGRESS                    - Target Progress
rem
CREATE TABLE EM_PC_TARGET_PROGRESS_E
(
     PLAN_GUID    RAW(16) NOT NULL,
     TARGET_GUID  RAW(16) NOT NULL,
     STATUS       NUMBER(1) DEFAULT 1 NOT NULL,
     PROGRESS     NUMBER(3) DEFAULT 0 NOT NULL,
     CONSTRAINT EM_PC_TARGET_PROGRESS_PK PRIMARY KEY(PLAN_GUID,TARGET_GUID),
     CONSTRAINT EM_PC_TARGET_PROGRESS_FK FOREIGN KEY(PLAN_GUID) REFERENCES EM_PC_PLANS_E(PLAN_GUID) ON DELETE CASCADE,
     CONSTRAINT EM_PC_TP_CHECK_STATUS CHECK(STATUS BETWEEN 1 AND 4),
     CONSTRAINT EM_PC_TP_CHECK_PROGRESS CHECK(PROGRESS BETWEEN 0 AND 100)
);

rem Step Progress Status
rem
rem  PURPOSE
rem
rem     The EM_PC_STEP_PROGRESS_E table contains dp step progress status.
rem
rem  COLUMNS
rem
rem     PLAN_GUID                   - Plan Guid
rem     STEP_NAME                   - Step Name
rem     PROGRESS                    - Target Progress
rem
CREATE TABLE EM_PC_STEP_PROGRESS_E
(
     PLAN_GUID    RAW(16) NOT NULL,
     STEP_NAME    VARCHAR2(64) NOT NULL,
     PROGRESS     NUMBER(3) DEFAULT 0 NOT NULL,
     CONSTRAINT EM_PC_STEP_PROGRESS_PK PRIMARY KEY(PLAN_GUID,STEP_NAME),
     CONSTRAINT EM_PC_STEP_PROGRESS_FK FOREIGN KEY(PLAN_GUID) REFERENCES EM_PC_PLANS_E(PLAN_GUID) ON DELETE CASCADE,
     CONSTRAINT EM_PC_SP_CHECK_PROGRESS CHECK(PROGRESS BETWEEN 0 AND 100)
);

alter session set current_schema=SYSMAN_TYPES;

CREATE OR REPLACE TYPE em_pc_tp_payload_type AS OBJECT
(
    INSTANCE_GUID RAW(16),
    STEP_NAME VARCHAR2(64),
    JOB_ID RAW(16)
);
/

grant execute on em_pc_tp_payload_type to sysman with grant option;

alter session set current_schema=SYSMAN;

BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
        queue_table        => '&&EM_REPOS_USER' || '.em_pc_tp_table',
        queue_payload_type => 'SYSMAN_TYPES' || '.em_pc_tp_payload_type',
        multiple_consumers => TRUE,
        sort_list          => 'PRIORITY,ENQ_TIME'
        );
END;
/


BEGIN
DBMS_AQADM.CREATE_QUEUE (
        queue_name  => '&&EM_REPOS_USER' || '.em_pc_tp',
        queue_table => '&&EM_REPOS_USER' || '.em_pc_tp_table'
        );
DBMS_AQADM.START_QUEUE (
        queue_name => '&&EM_REPOS_USER' || '.em_pc_tp'
        );
END;
/

BEGIN

DBMS_AQADM.ADD_SUBSCRIBER (
        queue_name => '&&EM_REPOS_USER' || '.em_pc_tp',
        subscriber => SYS.AQ$_AGENT(
            'em_pc_tp_subscriber',
            NULL,
            NULL )
        );

DBMS_AQ.REGISTER (
        SYS.AQ$_REG_INFO_LIST(
            SYS.AQ$_REG_INFO(
                '&&EM_REPOS_USER' || '.EM_PC_TP:EM_PC_TP_SUBSCRIBER',
                DBMS_AQ.NAMESPACE_AQ,
                'plsql://' || '&&EM_REPOS_USER' || '.EM_PC_TP_CALLBACK_PROCEDURE',
                HEXTORAW('FF')
                )
            ),
        1
        );
END;
/

CREATE OR REPLACE PROCEDURE step_success_enqueue_msg(o_payload sysman_types.em_pc_tp_payload_type)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
r_enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle     RAW(16);
BEGIN

DBMS_AQ.ENQUEUE(
        queue_name         => '&&EM_REPOS_USER' || '.em_pc_tp',
        enqueue_options    => r_enqueue_options,
        message_properties => r_message_properties,
        payload            => o_payload,
        msgid              => v_message_handle
        );
COMMIT;
EXCEPTION WHEN OTHERS
THEN
    EMDW_LOG.error('Exception calling step_success_enqueue_msg - ' || SQLCODE || ' error ' || SQLERRM, 'EM.JOB');
END;
/

CREATE OR REPLACE PROCEDURE em_pc_tp_callback_procedure(
        context  RAW,
        reginfo  SYS.AQ$_REG_INFO,
        descr    SYS.AQ$_DESCRIPTOR,
        payload  RAW,
        payloadl NUMBER) 
AS
r_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle     RAW(16);
o_payload            sysman_types.em_pc_tp_payload_type;
BEGIN
r_dequeue_options.msgid := descr.msg_id;
r_dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.DEQUEUE(
        queue_name         => descr.queue_name,
        dequeue_options    => r_dequeue_options,
        message_properties => r_message_properties,
        payload            => o_payload,
        msgid              => v_message_handle
        );
COMMIT;
MERGE INTO EM_PC_TARGET_PROGRESS_E target
USING
(
     SELECT tp.PLAN_GUID, tp.TARGET_GUID, sp.PROGRESS
     FROM EM_PC_STEP_PROGRESS_E sp, EM_PC_EXECUTION_INSTANCES_E e, MGMT_JOB_FLAT_TARGETS_E f, EM_PC_TARGET_PROGRESS_E tp
     WHERE e.execution_guid = o_payload.instance_guid
     AND e.plan_guid = sp.plan_guid
     AND sp.step_name = o_payload.step_name
     AND f.job_id = o_payload.job_id
     AND sp.plan_guid = tp.plan_guid
     AND sp.progress > tp.progress
     AND ((f.target_guid = tp.target_guid)
         OR  EXISTS ( SELECT 1 FROM EM_PC_HOMES_E ph, MGMT_TARGETS oh, MGMT_TARGET_PROPERTIES_E ohp
             WHERE tp.plan_guid = ph.plan_guid
             AND tp.target_guid = ph.target_guid
             AND f.target_guid = oh.target_guid
             AND oh.target_type = 'oracle_home'
             AND oh.target_guid = ohp.target_guid
             AND ohp.property_name = 'INSTALL_LOCATION'
             AND ohp.property_value = ph.home_path
             AND oh.host_name = ph.host_name)
         OR EXISTS ( SELECT 1 FROM MGMT_TARGETS t1, MGMT_TARGETS t2
             WHERE t1.target_guid = tp.target_guid
             AND t2.target_guid = f.target_guid
             AND  t2.target_type = 'host'
             AND t1.host_name = t2.host_name))
) source
ON ( target.plan_guid = source.plan_guid AND target.target_guid = source.target_guid)
WHEN MATCHED THEN UPDATE SET target.progress = source.progress;

COMMIT;
EXCEPTION WHEN OTHERS
THEN
    EMDW_LOG.error('Exception calling em_pc_tp_callback_procedure - ' || SQLCODE || ' error ' || SQLERRM, 'EM.JOB');    
END;
/

show errors;


 declare 
 l_trig_count number; 
 begin 
   select count(1) into l_trig_count from dba_triggers where trigger_name='EM_PC_PLAN_STATUS_UPD' and owner='&EM_REPOS_USER'; 
   if l_trig_count > 0  then 
     execute immediate 'DROP TRIGGER "&EM_REPOS_USER".EM_PC_PLAN_STATUS_UPD'; 
   end if; 
 exception 
 when others then 
   NULL; 
 end; 
/ 

create or replace editioning view EM_PC_PLANS as select * from EM_PC_PLANS_E;

CREATE OR REPLACE TRIGGER EM_PC_PLAN_STATUS_UPD
    AFTER UPDATE OF PLAN_STATUS
    ON    EM_PC_PLANS_E
    FOR   EACH ROW
    WHEN  (new.member_of IS NOT NULL AND (new.plan_status = 'DEPLOY_SCHEDULED' OR new.plan_status = 'DEPLOY_INPROGRESS'
        OR new.plan_status = 'DEPLOY_FAILED' OR new.plan_status = 'DEPLOY_ERRORS' OR new.plan_status = 'DEPLOY_SUCCESS'))
DECLARE
    l_clean BOOLEAN := TRUE;
    l_status  NUMBER(1) := 1;
BEGIN    
    CASE :new.plan_status
        WHEN 'DEPLOY_INPROGRESS' THEN
            l_status := 2;
        WHEN 'DEPLOY_FAILED' THEN    
            l_status := 3;
            l_clean := FALSE;
        WHEN 'DEPLOY_ERRORS' THEN       
            l_status := 3;
            l_clean := FALSE;
        WHEN 'DEPLOY_SUCCESS' THEN
            l_status := 4;
            l_clean := FALSE;
    END CASE;        
    IF l_clean THEN
    BEGIN
        DELETE FROM EM_PC_TARGET_PROGRESS_E WHERE PLAN_GUID = :NEW.PLAN_GUID;
        INSERT INTO EM_PC_TARGET_PROGRESS_E(PLAN_GUID,TARGET_GUID)
        SELECT DISTINCT PLAN_GUID, TARGET_GUID
        FROM EM_PC_PLAN_PATCHES_E
        WHERE PLAN_GUID = :NEW.PLAN_GUID;
    END;
    END IF;

    IF l_status <> 1 THEN
        UPDATE EM_PC_TARGET_PROGRESS_E SET STATUS = l_status 
        WHERE PLAN_GUID = :NEW.PLAN_GUID;
    END IF;
                                        
    IF l_status = 4 THEN                                        
        UPDATE EM_PC_TARGET_PROGRESS_E SET PROGRESS = 100
        WHERE PLAN_GUID = :NEW.PLAN_GUID;
    END IF;
END;
/

show errors;


 declare 
 l_trig_count number; 
 begin 
   select count(1) into l_trig_count from dba_triggers where trigger_name='EM_PAF_STATES_AFTER_UPD2' and owner='&EM_REPOS_USER'; 
   if l_trig_count > 0  then 
     execute immediate 'DROP TRIGGER "&EM_REPOS_USER".EM_PAF_STATES_AFTER_UPD2'; 
   end if; 
 exception 
 when others then 
   NULL; 
 end; 
/ 

create or replace editioning view EM_PAF_STATES as select * from EM_PAF_STATES_E;

CREATE OR REPLACE TRIGGER EM_PAF_STATES_AFTER_UPD2
    AFTER UPDATE OF STATUS
    ON EM_PAF_STATES
    FOR EACH ROW
    WHEN (new.state_type = 6 AND old.status != new.status AND new.status IS NOT NULL)
DECLARE
l_count  NUMBER := 0;
BEGIN
    IF :new.status = MGMT_PAF_STATUS.COMPLETED THEN 
    BEGIN
        SELECT COUNT(1) INTO l_count
        FROM EM_PC_STEP_PROGRESS_E sp, EM_PC_EXECUTION_INSTANCES_E e
        WHERE e.execution_guid = :new.execution_guid
        AND e.plan_guid = sp.plan_guid
        AND sp.step_name = :new.step_guid;
      
        IF l_count > 0 THEN
            step_success_enqueue_msg(sysman_types.em_pc_tp_payload_type(:new.execution_guid, :new.step_guid, :new.job_id));
        END IF;    
    EXCEPTION WHEN OTHERS
    THEN
        EMDW_LOG.error('Exception calling EM_PAF_STATES_AFTER_UPD2 - ' || SQLCODE || ' error ' || SQLERRM, 'EM.JOB');
    END;
    END IF;
END;
/

BEGIN
    DECLARE
        synonym_exists NUMBER;
    BEGIN
        SELECT COUNT(*)
        INTO synonym_exists
        FROM DBA_SYNONYMS
        WHERE SYNONYM_NAME = 'EM_PC_TP_PAYLOAD_TYPE'
        AND OWNER = 'SYSMAN';

        IF synonym_exists > 0 THEN
            EXECUTE IMMEDIATE 'DROP SYNONYM SYSMAN.EM_PC_TP_PAYLOAD_TYPE FORCE';
        END IF;    
            EXECUTE IMMEDIATE 'CREATE NONEDITIONABLE SYNONYM SYSMAN.EM_PC_TP_PAYLOAD_TYPE FOR SYSMAN_TYPES.EM_PC_TP_PAYLOAD_TYPE';
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error occurred when dropping and creating synonym EM_PC_TP_PAYLOAD_TYPE: ' || SQLERRM);
    END;
END;
/

create or replace editioning view EM_PC_STEP_PROGRESS as select * from EM_PC_STEP_PROGRESS_E;
create or replace editioning view EM_PC_TARGET_PROGRESS as select * from EM_PC_TARGET_PROGRESS_E;

show errors;



