Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="system/system_data_upgrade.sql+"/> 
Rem
Rem $Header: txnmgmt_data_upgrade.sql 07-aug-2007.11:07:35 mfidanbo Exp $
Rem
Rem txnmgmt_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      txnmgmt_data_upgrade.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    MODIFIED   (MM/DD/YY)
Rem    mfidanbo    08/07/07 - fix drop cascade
Rem    bmallipe    08/13/06 - fixing the dbcontrol error
Rem    rmarripa    09/08/05 - fix bug 4597632 
Rem    rmarripa    08/17/05 - handle errors 
Rem    mfidanbo    08/04/05 - update beacon specific collection interval 
Rem    mfidanbo    08/04/05 - fix properties 
Rem    chyu        07/26/05 - modifying the migrate header position 
Rem    chyu        07/18/05 - updating the new rep manager header. version
Rem                           number should be 10.2.0.0 
Rem    mfidanbo    04/09/05 - update target-props into test-props 
Rem    rmarripa    03/30/05 - add parent to the step 
Rem    afontana    02/22/05 - fix request mode 
Rem    mfidanbo    02/10/05 - add granularity to http props 
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    vjraghav    11/02/04 - vjraghav_migration_1
Rem    vjraghav    10/26/04 - Created
Rem

--------------------------------------------------------------------------------
-- WARNING!!! THE CODE IN THIS FILE HAS NOT BEEN TESTED AT ALL.
-- I AM WAITING FOR A TEST ENVIRONMENT TO TEST THIS IN - VIJAY RAGHAVAN.
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE EMD_BCNTXN_MIGRATE AS
    FUNCTION GET_APM_TARGET_IDS
            RETURN SMP_EMD_STRING_ARRAY;
    FUNCTION GET_TXN_IDS(target_ids IN SMP_EMD_STRING_ARRAY)
            RETURN SMP_EMD_STRING_ARRAY;
    PROCEDURE MIGRATE_TXN_DEFNS(txn_guids IN SMP_EMD_STRING_ARRAY);
    PROCEDURE MIGRATE_HTTP_STEPS(txn_guids IN SMP_EMD_STRING_ARRAY);
    PROCEDURE MIGRATE_HTTP_STEPS_1(txn_guids IN SMP_EMD_STRING_ARRAY);
    PROCEDURE MIGRATE_PING_PROPS(txn_guids IN SMP_EMD_STRING_ARRAY);
    PROCEDURE MIGRATE_TARGET_TXN_PROPS(tgt_guids IN SMP_EMD_STRING_ARRAY);

    MODULE_NAME constant VARCHAR2(32) := 'WEBAPP_TXN_DATA_UPGRADE';

END EMD_BCNTXN_MIGRATE;
/
SHOW ERRORS;


CREATE OR REPLACE PACKAGE BODY EMD_BCNTXN_MIGRATE AS

--------------------------------------------------------------------------------
-- The following function returns a list of webapp and beacon target ids.
--------------------------------------------------------------------------------

FUNCTION GET_APM_TARGET_IDS
        RETURN SMP_EMD_STRING_ARRAY
IS
    CURSOR targets_cur IS
        SELECT target_guid
        FROM MGMT_TARGETS
        WHERE target_type = 'website' OR target_type = 'oracle_beacon';

    v_more          BOOLEAN;
    v_target        targets_cur%ROWTYPE;
    
    v_count         PLS_INTEGER;
    v_ret           SMP_EMD_STRING_ARRAY;
BEGIN
    v_more := TRUE;
    v_count := 0;
    v_ret := SMP_EMD_STRING_ARRAY();
    OPEN targets_cur;
    WHILE v_more LOOP
        FETCH targets_cur INTO v_target;
        v_more := targets_cur%FOUND;
        IF v_more THEN
            v_ret.EXTEND;
            v_count := v_count + 1;
            v_ret(v_count) := RAWTOHEX(v_target.target_guid);
        END IF;
    END LOOP;
    IF targets_cur%ISOPEN THEN
        CLOSE targets_cur;
    END IF;
    RETURN v_ret;
END GET_APM_TARGET_IDS;

--------------------------------------------------------------------------------
-- The following function returns a list of transaction ids given a list of
-- target ids.
--------------------------------------------------------------------------------

FUNCTION GET_TXN_IDS(target_ids IN SMP_EMD_STRING_ARRAY)
        RETURN SMP_EMD_STRING_ARRAY
IS
    CURSOR txnids_cur IS
        SELECT txn_guid
        FROM MGMT_BCN_TXN_DEFN
        WHERE target_ids IS NULL 
            OR RAWTOHEX(target_guid) IN
                (SELECT *
                    FROM TABLE (CAST(target_ids AS SMP_EMD_STRING_ARRAY))
                );

    v_more          BOOLEAN;
    v_txn           txnids_cur%ROWTYPE;
    
    v_count         PLS_INTEGER;
    v_ret           SMP_EMD_STRING_ARRAY;
BEGIN
    v_more := TRUE;
    v_count := 0;
    v_ret := SMP_EMD_STRING_ARRAY();
    OPEN txnids_cur;
    WHILE v_more LOOP
        FETCH txnids_cur INTO v_txn;
        v_more := txnids_cur%FOUND;
        IF v_more THEN
            v_ret.EXTEND;
            v_count := v_count + 1;
            v_ret(v_count) := RAWTOHEX(v_txn.txn_guid);
        END IF;
    END LOOP;
    IF txnids_cur%ISOPEN THEN
        CLOSE txnids_cur;
    END IF;
    RETURN v_ret;
END GET_TXN_IDS;

--------------------------------------------------------------------------------
-- The following procedure performs these operations
-- 1. Migrate data from two columns in MGMT_BCN_TXN_DEFN.
-- 2. Create an audit entry for each transaction migrated.
--------------------------------------------------------------------------------

PROCEDURE MIGRATE_TXN_DEFNS(txn_guids IN SMP_EMD_STRING_ARRAY)
IS
    CURSOR txn_defn_cur(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT target_guid, txn_guid, version, failure_regexp, default_schedule
        FROM MGMT_BCN_TXN_DEFN
        WHERE txn_ids IS NULL 
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                );
    
    CURSOR txn_cons_cur(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT DISTINCT txn_guid, auth_realm, auth_user, auth_passwd
        FROM MGMT_BCN_TXN_HTTP
        WHERE txn_ids IS NULL 
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                );

    CURSOR txn_per_bcn_props(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT DISTINCT target_guid, beacon_target_guid, txn_guid, schedule
        FROM MGMT_BCN_TARGET_TXN
        WHERE txn_ids IS NULL
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                );

    v_more          BOOLEAN;
    v_txn_defn      txn_defn_cur%ROWTYPE;
    v_target_guid   MGMT_TARGETS.TARGET_GUID%TYPE;
    v_timestamp     DATE;
    new_bcn_name    MGMT_TARGETS.TARGET_NAME%TYPE;
    v_bcn_guid      MGMT_TARGETS.TARGET_GUID%TYPE;

BEGIN
    -- First migrate the data from the table
    v_more := TRUE;
    OPEN txn_defn_cur(txn_guids);
    WHILE v_more LOOP
        FETCH txn_defn_cur INTO v_txn_defn;
        v_more := txn_defn_cur%FOUND;
        IF v_more THEN
            -- Set the failure string as a property
            IF (v_txn_defn.failure_regexp IS NOT NULL) THEN
                INSERT INTO MGMT_BCN_TXN_PROPS
                    (target_guid, txn_guid, name, string_part, string_value, 
                            prop_type, encrypted)
                VALUES
                    (v_txn_defn.target_guid, v_txn_defn.txn_guid, 
                    'FailureString', 0, v_txn_defn.failure_regexp, 1, 'N');
            END IF;
            -- Set the collection interval as a property
            IF (v_txn_defn.default_schedule) IS NOT NULL THEN
                INSERT INTO MGMT_BCN_TXN_PROPS
                    (target_guid, txn_guid, name, num_value, prop_type)
                VALUES
                    (v_txn_defn.target_guid, v_txn_defn.txn_guid, 
                    'Collection Interval', v_txn_defn.default_schedule, 2);   
            END IF;
            -- Set granularity to transaction
                INSERT INTO MGMT_BCN_TXN_PROPS
                    (target_guid, txn_guid, name, string_part, string_value, prop_type, encrypted)
                VALUES
                    (v_txn_defn.target_guid, v_txn_defn.txn_guid, 
                    'granularity', 0, 'transaction', 1, 'N');   

            v_timestamp := tgt_sysdate_wguid(v_txn_defn.target_guid);
            INSERT INTO MGMT_BCN_TXN_AUDIT
                (target_guid, txn_guid, audit_timestamp, change_type,
                is_version_change, version, details)
            VALUES
                (v_txn_defn.target_guid, v_txn_defn.txn_guid,
                v_timestamp, 8, 
                'N', v_txn_defn.version, 'Migrated');
        END IF;
    END LOOP;
    IF txn_defn_cur%ISOPEN THEN
        CLOSE txn_defn_cur;
    END IF;

    FOR subrec IN txn_per_bcn_props(txn_guids)
    LOOP
      
      v_bcn_guid := NULL;

      IF(subrec.target_guid = subrec.beacon_target_guid) THEN
        -- this is local beacon
        -- so we need to get the beacon_guid for the new beacon that will
        -- replace the local beacon
        BEGIN
          SELECT property_value
            INTO new_bcn_name
            FROM mgmt_target_properties prop 
           WHERE prop.target_guid = subrec.target_guid
             AND prop.property_name = 'Webapp10gR2UpgradeBeacon';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                new_bcn_name := NULL;
        END;
        IF(new_bcn_name IS NOT NULL) THEN
          -- we will change local beacon to this beacon name.
          -- create beacon_guid
          v_bcn_guid := MGMT_TARGET.GENERATE_TARGET_GUID(new_bcn_name, MGMT_GLOBAL.G_BEACON_TARGET_TYPE);
        END IF;
     ELSE
       v_bcn_guid := subrec.beacon_target_guid;
     END IF;

     IF (v_bcn_guid IS NOT NULL) THEN

      INSERT INTO MGMT_BCN_BCNTXN_PROPS 
        (target_guid, 
         txn_guid, 
         bcn_guid, 
         name, string_part, prop_type, num_value)
      VALUES 
        (subrec.target_guid, 
         subrec.txn_guid, 
         v_bcn_guid,
         'Collection Interval', 0, 2, subrec.schedule);

     END IF;

    END LOOP;

    FOR subrec IN txn_cons_cur(txn_guids) 
    LOOP
       -- find target_guid for txn
       SELECT target_guid
         INTO v_target_guid
         FROM MGMT_BCN_TXN_DEFN
        WHERE txn_guid = subrec.txn_guid;

       -- Auth Realm
       IF (subrec.auth_realm IS NOT NULL) THEN
         INSERT INTO MGMT_BCN_TXN_PROPS
           (target_guid, txn_guid, name, 
            string_part, string_value, prop_type, encrypted)
         VALUES
           (v_target_guid, subrec.txn_guid, 'authrealm', 
            0, decrypt(subrec.auth_realm), 1, 'N');
       END IF;
                    
       -- Auth User
       IF (subrec.auth_user IS NOT NULL) THEN
         INSERT INTO MGMT_BCN_TXN_PROPS
           (target_guid, txn_guid, name, 
            string_part, string_value, prop_type, encrypted)
         VALUES
           (v_target_guid, subrec.txn_guid, 'authuser', 
            0, decrypt(subrec.auth_user), 1, 'N');
       END IF;
                   
       -- Auth Password
       IF (subrec.auth_passwd IS NOT NULL) THEN
         INSERT INTO MGMT_BCN_TXN_PROPS
           (target_guid, txn_guid, name, 
            string_part, string_value, prop_type, encrypted)
         VALUES
           (v_target_guid, subrec.txn_guid, 'authpwd', 
            0, subrec.auth_passwd, 1, 'Y');
       END IF;
     END LOOP;
    
EXCEPTION
    WHEN OTHERS THEN
        IF EMDW_LOG.P_IS_ERROR_SET THEN
           EMDW_LOG.ERROR('Fatal Error Encountered in MIGRATE_TXN_DEFNS: ' || SUBSTR(SQLERRM, 1, 1000), MODULE_NAME) ;
        END IF;
        IF txn_defn_cur%ISOPEN THEN
            CLOSE txn_defn_cur;
        END IF;
        IF txn_cons_cur%ISOPEN THEN
            CLOSE txn_cons_cur;
        END IF;
        IF txn_per_bcn_props%ISOPEN THEN
            CLOSE txn_per_bcn_props;
        END IF;
        
END MIGRATE_TXN_DEFNS;

--------------------------------------------------------------------------------
-- The following procedure performs these operations
-- 1. Migrate data from MGMT_BCN_TXN_HTTP and drop the table
--------------------------------------------------------------------------------

PROCEDURE MIGRATE_HTTP_STEPS(txn_guids IN SMP_EMD_STRING_ARRAY)
IS
    CURSOR steps_defn_cur(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT txn_guid, step, req_mode, url, http_method, target_frame,
                anchor_hint, hidden_input_hint
        FROM MGMT_BCN_TXN_HTTP
        WHERE txn_ids IS NULL 
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                )
        ORDER BY step ASC;
        
    v_more          BOOLEAN;
    v_step_defn     steps_defn_cur%ROWTYPE;

    v_target_guid   MGMT_TARGETS.TARGET_GUID%TYPE;
    v_step_guid     MGMT_BCN_STEP_DEFN.STEP_GUID%TYPE;
    v_parent_step_guid MGMT_BCN_STEP_DEFN.PARENT_STEP_GUID%TYPE;
    v_step_name     VARCHAR2(32);
BEGIN
    -- First migrate the data from the table
    v_more := TRUE;
    OPEN steps_defn_cur(txn_guids);
    WHILE v_more LOOP
        FETCH steps_defn_cur INTO v_step_defn;
        v_more := steps_defn_cur%FOUND;
        IF v_more THEN
            BEGIN
                -- Find the target guid for this transaction
                SELECT target_guid
                INTO v_target_guid
                FROM MGMT_BCN_TXN_DEFN
                WHERE txn_guid = v_step_defn.txn_guid;

                IF (v_step_defn.step < 10) THEN
                    v_step_name := 'STEP 00' || v_step_defn.step;
                ELSIF (v_step_defn.step < 100) THEN
                    v_step_name := 'STEP 0' || v_step_defn.step;
                ELSE
                    v_step_name := 'STEP ' || v_step_defn.step;
                END IF;
            
                IF ( v_step_defn.req_mode = 1 ) THEN 
                    v_parent_step_guid := null;
                ELSE
                    BEGIN
                        SELECT step_guid 
                          INTO v_parent_step_guid
                          FROM (
                                SELECT max(step) AS step_num 
                                  FROM mgmt_bcn_step_defn step, mgmt_bcn_step_props props 
                                 WHERE step.txn_guid = v_step_defn.txn_guid
                                   AND step.step_type = 'HTTP'
                                   AND step.step < v_step_defn.step
                                   AND step.target_guid = props.target_guid 
                                   AND step.step_guid = props.step_guid  
                                   AND props.name = 'req_mode' 
                                   AND props.NUM_VALUE=1) parent_step, mgmt_bcn_step_defn step_defn 
                         WHERE step_defn.txn_guid = v_step_defn.txn_guid
                           AND step_defn.step=parent_step.step_num;
                    EXCEPTION 
                        WHEN NO_DATA_FOUND THEN 
                            IF EMDW_LOG.P_IS_ERROR_SET THEN
                                EMDW_LOG.ERROR('Unable to find the parent step guid for the step. Step Name = '||v_step_name||', Step Order = '||v_step_defn.step, MODULE_NAME) ;
                            END IF;
                    END;
                END IF;

                -- Insert the step definition. This should create the step guid.
                INSERT INTO MGMT_BCN_STEP_DEFN
                    (target_guid, txn_guid, step, name, step_type, parent_step_guid)
                VALUES
                    (v_target_guid, v_step_defn.txn_guid, v_step_defn.step,
                    v_step_name, 'HTTP', v_parent_step_guid); 
                    
                BEGIN
                    -- Read the step guid back
                    SELECT step_guid
                    INTO v_step_guid
                    FROM MGMT_BCN_STEP_DEFN
                    WHERE target_guid = v_target_guid
                        AND txn_guid = v_step_defn.txn_guid
                        AND step = v_step_defn.step
                        AND name = v_step_name
                        AND step_type = 'HTTP';
        
                    -- Request Mode
                    IF (v_step_defn.req_mode IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, num_value, prop_type)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'req_mode', 
                            v_step_defn.req_mode, 2);
                    ELSE
                        -- Else Insert "User Action"
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, num_value, prop_type)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'req_mode', 
                            1, 2);
                    END IF;
                    
                    -- URL
                    IF (v_step_defn.url IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'url', 
                            0, v_step_defn.url, 1, 'N');
                    END IF;
                        
                    -- HTTP Method
                    IF (v_step_defn.http_method IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'http_method', 
                            0, v_step_defn.http_method, 1, 'N');
                    ELSE
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'http_method', 
                            0, 'G', 1, 'N');
                    END IF;
                    
                    -- Target Frame
                    IF (v_step_defn.target_frame IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'target_frame', 
                            0, v_step_defn.target_frame, 1, 'N');
                    END IF;
                    
                    -- Anchor Hint
                    IF (v_step_defn.anchor_hint IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'anchor_hint', 
                            0, v_step_defn.anchor_hint, 1, 'N');
                    END IF;
                    
                    -- Hidden Input Hint
                    IF (v_step_defn.hidden_input_hint IS NOT NULL) THEN
                        INSERT INTO MGMT_BCN_STEP_PROPS
                            (target_guid, step_guid, name, 
                            string_part, string_value, prop_type, encrypted)
                        VALUES
                            (v_target_guid, v_step_guid,
                            'hidden_input_hint', 
                            0, v_step_defn.hidden_input_hint, 1, 'N');
                    END IF;
                    
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        -- Do nothing. Ignore this entry.
                        DBMS_OUTPUT.PUT_LINE('Invalid step: ' || v_step_name);
                    WHEN DUP_VAL_ON_INDEX THEN
                        -- Do nothing. Ignore this entry.
                        DBMS_OUTPUT.PUT_LINE('Invalid step: ' || v_step_name);
                END;
                
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    -- Do nothing. Ignore this entry.
                    DBMS_OUTPUT.PUT_LINE('Invalid transaction guid: ' ||
                            v_step_defn.txn_guid);
            END;
            

        END IF;
    END LOOP;
    
EXCEPTION
    WHEN OTHERS THEN
        IF steps_defn_cur%ISOPEN THEN
            CLOSE steps_defn_cur;
        END IF;
        
END MIGRATE_HTTP_STEPS;

--------------------------------------------------------------------------------
-- The following procedure performs these operations
-- 1. Migrate data from MGMT_BCN_TXN_HTTP_PARAM.
--------------------------------------------------------------------------------

PROCEDURE MIGRATE_HTTP_STEPS_1(txn_guids IN SMP_EMD_STRING_ARRAY)
IS
    CURSOR http_params_cur(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT txn_guid, step, part, headers, postdata
        FROM MGMT_BCN_TXN_HTTP_PARAM
        WHERE txn_ids IS NULL 
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                );
        
    v_more          BOOLEAN;
    v_http_params   http_params_cur%ROWTYPE;
    
    v_target_guid   MGMT_TARGETS.TARGET_GUID%TYPE;
    v_step_guid     MGMT_BCN_STEP_DEFN.STEP_GUID%TYPE;
BEGIN
    -- First migrate the data from the table
    v_more := TRUE;
    OPEN http_params_cur(txn_guids);
    WHILE v_more LOOP
        FETCH http_params_cur INTO v_http_params;
        v_more := http_params_cur%FOUND;
        IF v_more THEN
            BEGIN
                -- Find the target guid for this transaction
                SELECT target_guid
                INTO v_target_guid
                FROM MGMT_BCN_TXN_DEFN
                WHERE txn_guid = v_http_params.txn_guid;
                
                -- Find the step guid for this step
                SELECT step_guid
                INTO v_step_guid
                FROM MGMT_BCN_STEP_DEFN
                WHERE target_guid = v_target_guid
                    AND txn_guid = v_http_params.txn_guid
                    AND step = v_http_params.step
                    AND step_type = 'HTTP';
                
                -- HTTP Headers
                IF (v_http_params.headers IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_STEP_PROPS
                        (target_guid, step_guid, name, 
                        string_part, string_value, prop_type, encrypted)
                    VALUES
                        (v_target_guid, v_step_guid,
                        'headers', 
                        v_http_params.part, v_http_params.headers, 1, 'N');
                END IF;

                -- Post Data
                IF (v_http_params.postdata IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_STEP_PROPS
                        (target_guid, step_guid, name, 
                        string_part, string_value, prop_type, encrypted)
                    VALUES
                        (v_target_guid, v_step_guid,
                        'post_data', 
                        v_http_params.part, v_http_params.postdata, 1, 'Y');
                END IF;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    -- Do nothing. Ignore this entry.
                    DBMS_OUTPUT.PUT_LINE('Invalid entry: Step ' ||
                            v_http_params.step || ' Transaction '
                            || v_http_params.txn_guid);
                WHEN DUP_VAL_ON_INDEX THEN
                    -- Do nothing. Ignore this entry.
                    DBMS_OUTPUT.PUT_LINE('Invalid entry: Step ' ||
                            v_http_params.step || ' Transaction '
                            || v_http_params.txn_guid);
            END;
        
        END IF;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        IF http_params_cur%ISOPEN THEN
            CLOSE http_params_cur;
        END IF;
        
END MIGRATE_HTTP_STEPS_1;

--------------------------------------------------------------------------------
-- The following procedure performs these operations
-- 1. Migrate data from MGMT_BCN_TXN_PING.
--------------------------------------------------------------------------------

PROCEDURE MIGRATE_PING_PROPS(txn_guids IN SMP_EMD_STRING_ARRAY)
IS
    CURSOR ping_params_cur(txn_ids SMP_EMD_STRING_ARRAY) IS
        SELECT txn_guid, hostname, ping_mode, num_packets, max_ttl, timeout
        FROM MGMT_BCN_TXN_PING
        WHERE txn_ids IS NULL 
            OR RAWTOHEX(txn_guid) IN
                (SELECT *
                    FROM TABLE (CAST(txn_ids AS SMP_EMD_STRING_ARRAY))
                );
        
    v_more          BOOLEAN;
    v_ping_params   ping_params_cur%ROWTYPE;
    
    v_target_guid   MGMT_TARGETS.TARGET_GUID%TYPE;
BEGIN
    -- First migrate the data from the table
    v_more := TRUE;
    OPEN ping_params_cur(txn_guids);
    WHILE v_more LOOP
        FETCH ping_params_cur INTO v_ping_params;
        v_more := ping_params_cur%FOUND;
        IF v_more THEN
            BEGIN
                -- Find the target guid for this transaction
                SELECT target_guid
                INTO v_target_guid
                FROM MGMT_BCN_TXN_DEFN
                WHERE txn_guid = v_ping_params.txn_guid;
                
                -- Host Name
                IF (v_ping_params.hostname IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_TXN_PROPS
                        (target_guid, txn_guid, name, 
                        string_part, string_value, prop_type, encrypted)
                    VALUES
                        (v_target_guid, v_ping_params.txn_guid,
                        'targetHost', 
                        0, v_ping_params.hostname, 1, 'N');
                END IF;
    
                -- Ping Mode
                IF (v_ping_params.ping_mode IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_TXN_PROPS
                        (target_guid, txn_guid, name, num_value, prop_type)
                    VALUES
                        (v_target_guid, v_ping_params.txn_guid, 
                        'mode', v_ping_params.ping_mode, 2);   
                END IF;
                
                -- Number of Packets
                IF (v_ping_params.num_packets IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_TXN_PROPS
                        (target_guid, txn_guid, name, num_value, prop_type)
                    VALUES
                        (v_target_guid, v_ping_params.txn_guid, 
                        'numpackets', v_ping_params.num_packets, 2);   
                END IF;
                
                -- Maximum Time To Live
                IF (v_ping_params.max_ttl IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_TXN_PROPS
                        (target_guid, txn_guid, name, num_value, prop_type)
                    VALUES
                        (v_target_guid, v_ping_params.txn_guid, 
                        'maxttl', v_ping_params.max_ttl, 2);   
                END IF;
                
                -- Timeout
                IF (v_ping_params.timeout IS NOT NULL) THEN
                    INSERT INTO MGMT_BCN_TXN_PROPS
                        (target_guid, txn_guid, name, num_value, prop_type)
                    VALUES
                        (v_target_guid, v_ping_params.txn_guid, 
                        'maxtime', v_ping_params.timeout, 2);   
                END IF;
                
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    -- Do nothing. Ignore this entry.
                    DBMS_OUTPUT.PUT_LINE('Invalid transaction guid: ' ||
                            v_ping_params.txn_guid);
                WHEN DUP_VAL_ON_INDEX THEN
                    -- Do nothing. Ignore this entry.
                    DBMS_OUTPUT.PUT_LINE('Invalid transaction guid: ' ||
                            v_ping_params.txn_guid);
            END;
                    
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        IF ping_params_cur%ISOPEN THEN
            CLOSE ping_params_cur;
        END IF;
        
END MIGRATE_PING_PROPS;

PROCEDURE INSERT_INTO_PROP_TABLE(property_name IN VARCHAR2,
                                 property_value IN VARCHAR2,
                                 in_txn_guid IN RAW,
                                 tgt_guid IN RAW)
IS
  prop_type NUMBER;

BEGIN

  BEGIN
    SELECT props.property_type INTO prop_type
      FROM MGMT_TEST_PROP props, MGMT_BCN_TXN_DEFN txn
     WHERE txn.txn_guid = in_txn_guid
       AND txn.target_guid = tgt_guid
       AND props.test_type = txn.txn_type
       AND props.prop_name = property_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      prop_type := 1;
  END;
 
  BEGIN
    IF prop_type = 1 THEN -- String
      INSERT INTO MGMT_BCN_TXN_PROPS (target_guid, txn_guid, name, string_value, prop_type, encrypted)
           VALUES (tgt_guid, in_txn_guid, property_name, property_value, prop_type, 'N');
    ELSIF (prop_type = 2 or prop_type = 3) THEN -- Integer or float
      INSERT INTO MGMT_BCN_TXN_PROPS (target_guid, txn_guid, name, num_value, prop_type)
           VALUES (tgt_guid, in_txn_guid, property_name, to_number(property_value), prop_type);
    END IF;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN 
      NULL;
  END;

END INSERT_INTO_PROP_TABLE;

PROCEDURE MIGRATE_TARGET_TXN_PROPS(tgt_guids IN SMP_EMD_STRING_ARRAY)

IS
  l_count PLS_INTEGER;
  single_guid RAW(16);
  tgt_level_props SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();


  CURSOR txnids_cur(single_guid RAW) IS
  SELECT txn_guid
    FROM MGMT_BCN_TXN_DEFN
   WHERE target_guid = single_guid;

  CURSOR txn_level_props(txn_guid RAW, tgt_guid RAW) IS
  SELECT SUBSTR(property_name, INSTR(property_name, ':') + 1) as property_name, 
         property_value
    FROM mgmt_target_properties
   WHERE target_guid = tgt_guid
     AND property_type = EMD_BCN_ADMIN.REP_ONLY_PROP_TYPE
     AND property_name LIKE (RAWTOHEX(txn_guid) || '%');

BEGIN

  IF(tgt_guids IS NULL OR tgt_guids.COUNT = 0) THEN
    RETURN;
  END IF;

  FOR l_count IN tgt_guids.FIRST..tgt_guids.LAST LOOP 
    single_guid := HEXTORAW(tgt_guids(l_count));

    -- select all tgt level txn properties
    SELECT SMP_EMD_NVPAIR(property_name, property_value) BULK COLLECT INTO tgt_level_props
      FROM mgmt_target_properties 
     WHERE target_guid = single_guid
       AND property_type = EMD_BCN_ADMIN.REP_ONLY_PROP_TYPE
       AND INSTR(property_name, ':') = 0; 

    -- select all txns belonging to this target
    -- for each txn, add all tgt level txn props to this txn
    -- migrate all txn level txn props from mgmt_target_properties to txn_props
    FOR subrec IN txnids_cur(single_guid) LOOP

      IF(tgt_level_props IS NOT NULL and tgt_level_props.COUNT > 0) THEN
        FOR l_prop_count IN tgt_level_props.FIRST..tgt_level_props.LAST LOOP
          BEGIN
            IF( (tgt_level_props(l_prop_count).name = 'OCCURRENCES') OR 
                (tgt_level_props(l_prop_count).name = 'OCCURENCES') )THEN
              -- update mgmt_admin_metric_thresholds,
              -- this applies to all collections, all beacons, all metrics
              UPDATE MGMT_ADMIN_METRIC_THRESHOLDS 
                 SET num_occurences = TO_NUMBER(tgt_level_props(l_prop_count).value)
               WHERE target_guid = single_guid
                 AND coll_name = subrec.txn_guid;
            ELSE
  			    INSERT_INTO_PROP_TABLE(tgt_level_props(l_prop_count).name, tgt_level_props(l_prop_count).value, subrec.txn_guid, single_guid);
            END IF;
          EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN 
              NULL;
          END;
        END LOOP;
      END IF;
      
      FOR subtxnprop IN txn_level_props(subrec.txn_guid , single_guid) LOOP
        BEGIN
          IF( (subtxnprop.property_name = 'OCCURRENCES') OR
              (subtxnprop.property_name = 'OCCURENCES') )THEN
            -- update mgmt_admin_metric_thresholds,
            -- this applies to all beacons, all metrics
            UPDATE MGMT_ADMIN_METRIC_THRESHOLDS 
               SET num_occurences = TO_NUMBER(subtxnprop.property_value)
             WHERE target_guid = single_guid
               AND coll_name = subrec.txn_guid; -- this might change in the future, coll_name = txn_name?
          ELSE
            INSERT_INTO_PROP_TABLE(subtxnprop.property_name, subtxnprop.property_value, subrec.txn_guid, single_guid);
          END IF;
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN 
            NULL;
        END;
      END LOOP;
    END LOOP; -- txn id loop

    IF (tgt_level_props IS NOT NULL) THEN
      tgt_level_props.DELETE;
    END IF;

  END LOOP; -- tgt id loop

END MIGRATE_TARGET_TXN_PROPS;


END EMD_BCNTXN_MIGRATE;
/
SHOW ERRORS;



--------------------------------------------------------------------------------
-- Use the package created above to perform the migration.
--------------------------------------------------------------------------------

DECLARE
    v_target_ids        SMP_EMD_STRING_ARRAY;
    v_txn_ids           SMP_EMD_STRING_ARRAY;
BEGIN
    -- First get the ids of all targets for which we want to migrate transactions.
    -- We then figure out the transaction ids corresponding to this target id list.
    v_target_ids := EMD_BCNTXN_MIGRATE.GET_APM_TARGET_IDS;
    v_txn_ids := EMD_BCNTXN_MIGRATE.GET_TXN_IDS(v_target_ids);
    
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO(v_target_ids.COUNT || ' targets selected', EMD_BCNTXN_MIGRATE.MODULE_NAME) ;
      EMDW_LOG.INFO(v_txn_ids.COUNT || ' transactions selected', EMD_BCNTXN_MIGRATE.MODULE_NAME) ;
    END IF;

    -- Migrate transaction definitions
    EMD_BCNTXN_MIGRATE.MIGRATE_TXN_DEFNS(v_txn_ids);

    -- Migrate HTTP step definitions and properties
    EMD_BCNTXN_MIGRATE.MIGRATE_HTTP_STEPS(v_txn_ids);

    -- Migrate additional HTTP step properites
    EMD_BCNTXN_MIGRATE.MIGRATE_HTTP_STEPS_1(v_txn_ids);

    -- Migrate Ping properties
   EMD_BCNTXN_MIGRATE.MIGRATE_PING_PROPS(v_txn_ids);

    -- Migrate target props into txn props
    EMD_BCNTXN_MIGRATE.MIGRATE_TARGET_TXN_PROPS(v_target_ids);
END;
/
-- Delete two columns from the txn definition table
ALTER TABLE MGMT_BCN_TXN_DEFN
DROP COLUMN failure_regexp;

ALTER TABLE MGMT_BCN_TXN_DEFN
DROP COLUMN default_schedule;

---------------------------------------------------------------------
--  Drop Table Constraints
---------------------------------------------------------------------
DECLARE
    l_count NUMBER := 0;
BEGIN
    SELECT count(*)
    INTO   l_count
    FROM   all_constraints
    WHERE  lower(constraint_name) = 'bcn_tgt_txn_primary_key';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE MGMT_BCN_TARGET_TXN
                           DROP CONSTRAINT bcn_tgt_txn_primary_key CASCADE DROP INDEX';
    END IF;
END;
/
---------------------------------------------------------------------
--  Drop Tables
---------------------------------------------------------------------
DROP TABLE MGMT_BCN_TARGET_TXN;

-- Drop the old HTTP table
DROP TABLE MGMT_BCN_TXN_HTTP;

-- Drop the old HTTP params table
DROP TABLE MGMT_BCN_TXN_HTTP_PARAM;

-- Drop the ping table
DROP TABLE MGMT_BCN_TXN_PING;


-- Drop the migration package
DROP PACKAGE EMD_BCNTXN_MIGRATE;

SHOW ERRORS;