Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="webapp/txnmgmt_data_upgrade_recompile.sql+"/> 
Rem
Rem $Header: webapp_data_upgrade.sql 02-oct-2005.11:09:24 mfidanbo Exp $
Rem
Rem webapp_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      webapp_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    09/27/05 - pass agent name instead of host name 
Rem    rmarripa    08/24/05 - Remove the parameter emd_url from the job 
Rem                           submission 
Rem    chyu        07/26/05 - modifying the migrate header position 
Rem    chyu        07/25/05 - adding the set echo on to start printing in 
Rem                           verbose mode 
Rem    chyu        07/18/05 - updating the new rep manager header 
Rem    rmarripa    07/06/05 - do not create system for services with host or 
Rem                           emd targets 
Rem    rmarripa    06/21/05 - create only one beacon per agent 
Rem    rmarripa    06/15/05 - add update beacon job required for sync 
Rem    rmarripa   06/06/05 - Remove composite target type property 
Rem    jsardas    05/04/05 - add logging 
Rem    andyao      04/29/05 - Enable test availability upgrade 
Rem    rmarripa    04/18/05 - delete associations with host and agent 
Rem    rmarripa    04/15/05 - fix bcn_tgt_cur to look only for beacon target 
Rem                           types 
Rem    mfidanbo    02/18/05 - load the upgrade job pkg 
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    rmarripa    12/24/04 - rmarripa_webapp_upgrade_changes
Rem    rmarripa    12/22/04 - add member targets upgrade 
Rem    rmarripa    12/16/04 - Created
Rem

--------------------------------------------------------------------------------
-- This package upgrades 10gR1 Web Application data to 10gR2. 
--------------------------------------------------------------------------------


declare
BEGIN
    mgmt_log.register_logging_module ( 'Webapp10gR2Upgrade',
                                   'Web Application 10gR2 Upgrade');
END;
/
show errors;

@&EM_SQL_ROOT/core/latest/jobTypes_sql/WebappUpgrade.sql
SET ECHO ON;
@&EM_SQL_ROOT/core/latest/jobTypes_sql/UpdateBeaconCollections.sql
SET ECHO ON;
@&EM_SQL_ROOT/core/v102010/webapp/webapp_upgrade_pkgdef.sql
SET ECHO ON;
@&EM_SQL_ROOT/core/v102010/webapp/webapp_upgrade_pkgbody.sql
SET ECHO ON;

CREATE OR REPLACE PACKAGE WEBAPP_UPGRADE AS
    WEBAPP_UPGRADE_BEACON CONSTANT VARCHAR2(32) := 'Webapp10gR2UpgradeBeacon';
    DONOT_REPLACE_WITH_BEACON CONSTANT VARCHAR2(32) := 'DoNotReplaceWithBeacon';
  
    MODULE_NAME VARCHAR2(32) := 'Webapp10gR2Upgrade';

    NOOP CONSTANT NUMBER := 0;
    CREATE_BEACON CONSTANT NUMBER := 1;
    USE_EXISTING_BEACON CONSTANT NUMBER := 2;

    JOB_NAME_PREFIX CONSTANT VARCHAR2(32) := 'WBAPP_UPGR_';
    JOB_TYPE        CONSTANT VARCHAR2(32) := 'Upgrade10gR2Webapp';
    JOB_DESCRIPTION CONSTANT VARCHAR2(64) := 'This job upgrades Web Application data from 10gR to 10gR2.';  

    PROCEDURE SUBMIT_UPGRADE_JOB(p_tgt_name IN VARCHAR2, 
                                 p_tgt_type IN VARCHAR2, 
                                 p_bcn_op IN NUMBER, 
                                 p_bcn_tgt_name IN VARCHAR2, 
                                 p_bcn_tgt_type IN VARCHAR2, 
                                 p_host_name IN VARCHAR2,
                                 p_agent_name IN VARCHAR2) ;
    PROCEDURE UPGRADE_WEBAPP;
END WEBAPP_UPGRADE;
/
SHOW ERRORS;


CREATE OR REPLACE PACKAGE BODY WEBAPP_UPGRADE AS
    g_upgrade_beacon_prop VARCHAR2(256) := NULL;
    FUNCTION GET_TARGET_PROPS (p_tgt_name IN VARCHAR2, p_tgt_type IN VARCHAR2) RETURN VARCHAR2
    IS
        l_props VARCHAR2(1024) := NULL;
        l_target_props SMP_EMD_NVPAIR_ARRAY := NULL;
    BEGIN
        IF ( p_tgt_name IS NULL ) OR ( p_tgt_type IS NULL ) OR 
           ( p_tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
            return l_props;
        END IF;
        
        BEGIN 
            SELECT SMP_EMD_NVPAIR(props.property_name , props.property_value)
              BULK COLLECT INTO l_target_props
              FROM mgmt_target_properties props, mgmt_targets tgt
             WHERE tgt.target_name = p_tgt_name 
               AND tgt.target_type = p_tgt_type
               AND tgt.target_guid = props.target_guid
               AND props.property_type = 'INSTANCE'
               AND props.property_name in ('proxyHost', 'proxyPort', 'dontProxyFor');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN 
                NULL;
        END;
           
        IF ( l_target_props IS NULL ) OR ( l_target_props.COUNT = 0 ) THEN 
            return l_props;
        END IF;

        l_props := '';
        FOR l_counter IN l_target_props.FIRST..l_target_props.LAST LOOP
            IF ( l_counter = l_target_props.LAST ) THEN 
                l_props := l_props||l_target_props(l_counter).name ||'='||
                              l_target_props(l_counter).value;
            ELSE
                l_props := l_props||l_target_props(l_counter).name ||'='||
                              l_target_props(l_counter).value||'|';
            END IF;
        END LOOP;

        return l_props;

    END GET_TARGET_PROPS;

    FUNCTION GET_NEW_BEACON_NAME (p_tgt_name IN VARCHAR2, 
                                  p_tgt_type IN VARCHAR2,
                                  p_emd_url IN VARCHAR2, 
                                  p_host_name IN VARCHAR2) RETURN VARCHAR2
    IS
        beacon_name VARCHAR2(256) := NULL;
        l_agent_cnt NUMBER := 0;
        l_agent_name mgmt_targets.target_name%TYPE := null;
        bcn_tgt_name mgmt_targets.target_name%TYPE := null;
    BEGIN
        IF ( p_tgt_name IS NULL ) OR ( p_tgt_type IS NULL ) OR 
           ( p_tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
            return beacon_name;
        END IF;
        IF ( p_emd_url IS NULL ) OR ( p_host_name IS NULL ) THEN 
            return beacon_name;
        END IF;
        
        -- Check if the host has more than one agent
        SELECT count(*) 
          INTO l_agent_cnt
          FROM mgmt_targets
         WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
           AND host_name = p_host_name;

        --If the host has more than one agent, use the agent 
        -- target name as beacon target name prefix. Otherwise,
        -- use host name as prefix.
        IF ( l_agent_cnt > 1 ) THEN  
            BEGIN 
                SELECT target_name
                  INTO l_agent_name
                  FROM mgmt_targets
                 WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
                   AND emd_url = p_emd_url;
            EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                    -- if for some reason there is no agent target
                    -- append _2 to the host name
                    l_agent_name := p_host_name ||'_2';
            END;
            beacon_name := l_agent_name || '_beacon';
        ELSE
            beacon_name := p_host_name || '_beacon';
        END IF;

       -- During the upgrade, OMS is down. All the jobs submitted
       -- for creating beacon will be in the job queue. 
       -- Here we check if there is any beacon target with same  
       -- name, prior to upgrade. If yes, then create one beacon 
       -- per target, that means create beacon name with web app
       -- target name as prefix. 
       BEGIN 
            SELECT target_name
              INTO bcn_tgt_name
              FROM mgmt_targets 
             WHERE target_name=beacon_name
               AND target_type = mgmt_global.g_beacon_target_type;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN 
                NULL;
        END;

        IF ( bcn_tgt_name IS NOT NULL ) THEN 
            beacon_name := p_tgt_name ||'_2_beacon';
        END IF;    
        return beacon_name;

    END GET_NEW_BEACON_NAME;

    PROCEDURE ADD_BEACON_ASSOCS
    IS 
        CURSOR tgt_bcn_cur IS
            SELECT tgt.target_name as target_name, 
                   tgt.target_type as target_type, 
                   bcn.target_name as bcn_target_name, 
                   bcn.target_type as bcn_target_type  
              FROM mgmt_bcn_target tgt_bcn, mgmt_targets bcn, mgmt_targets tgt
             WHERE tgt.target_guid = tgt_bcn.target_guid
               AND tgt.target_type=MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE
               AND tgt_bcn.beacon_target_guid = bcn.target_guid
               AND bcn.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE;
    BEGIN
        FOR sub_rec in tgt_bcn_cur LOOP
            mgmt_assoc.create_target_assoc(MGMT_ASSOC.ASSOC_DEF_REMOTELY_MNTRD_BY,
                sub_rec.target_name, sub_rec.target_type, sub_rec.bcn_target_name, 
                sub_rec.bcn_target_type);
        END LOOP;
    END ADD_BEACON_ASSOCS;

    PROCEDURE SUBMIT_UPGRADE_JOB(p_tgt_name IN VARCHAR2, 
                                 p_tgt_type IN VARCHAR2, 
                                 p_bcn_op IN NUMBER, 
                                 p_bcn_tgt_name IN VARCHAR2, 
                                 p_bcn_tgt_type IN VARCHAR2, 
                                 p_host_name IN VARCHAR2,
                                 p_agent_name IN VARCHAR2) 
    IS
        l_job_targets        MGMT_JOB_TARGET_LIST;
        l_job_target_list    MGMT_JOB_TARGET_LIST_ARRAY;
        l_job_params         MGMT_JOB_PARAM_LIST;
        l_job_name           MGMT_JOB.job_name%TYPE;
        l_job_id             MGMT_JOB.job_id%TYPE;
        l_execution_ids      MGMT_JOB_GUID_ARRAY;
        l_queue_names        SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
        l_schedule           MGMT_JOB_SCHEDULE_RECORD;
        l_op_type            VARCHAR2(64) := NULL;
        l_props              VARCHAR2(1024) := NULL;
        l_count              NUMBER;
        l_tgt_id             MGMT_TARGETS.target_guid%TYPE;
        l_index              NUMBER;

    BEGIN
        IF ( p_tgt_name IS NULL ) OR ( p_tgt_type IS NULL ) OR 
           ( p_tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
            return ;
        END IF;
        
        IF ( p_bcn_op != NOOP ) AND ( p_bcn_op != CREATE_BEACON ) AND ( p_bcn_op != USE_EXISTING_BEACON ) THEN 
            return ;
        END IF;
        
        IF ( p_bcn_op = CREATE_BEACON ) THEN 
            l_op_type := 'CreateBeacon';
        ELSE
           l_op_type := 'UseExistingBeacon';
        END IF;

        SELECT RAWTOHEX(target_guid)
          INTO l_tgt_id
          FROM mgmt_targets
         WHERE target_name = p_tgt_name
           AND target_type = p_tgt_type;

        l_job_name := JOB_NAME_PREFIX||l_tgt_id;

        -- Job target list
        l_job_targets := MGMT_JOB_TARGET_LIST();
        l_job_targets.EXTEND(1);
        l_job_targets(1) := MGMT_JOB_TARGET_RECORD(p_tgt_name, p_tgt_type );
          
        l_job_target_list := MGMT_JOB_TARGET_LIST_ARRAY();
        l_job_target_list.EXTEND(1);
        l_job_target_list(1) := l_job_targets;

        -- job params list 
        l_job_params := MGMT_JOB_PARAM_LIST();
        l_job_params.extend(4);

        l_index := 1;
        l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('upgradeType', 1,
                                           l_op_type, null);
        l_index := l_index+1;
        l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('webapp_target_name', 1,
                                           p_tgt_name, null);
        l_index := l_index+1;
        l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('webapp_target_type', 1, 
                                           p_tgt_type, null);
        l_index := l_index+1;

        l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('agent_name', 1,
                                 p_agent_name, null);
        l_index := l_index+1;


        IF ( p_bcn_tgt_name IS NOT NULL ) AND ( p_bcn_tgt_type IS NOT NULL ) THEN 
            l_job_params.extend(2);
            l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('beacon_target_name', 1, 
                                           p_bcn_tgt_name, null);
            l_index := l_index+1;
            l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('beacon_target_type', 1, 
                                           p_bcn_tgt_type, null);
            l_index := l_index+1;
        END IF;
        
        IF ( p_bcn_op = CREATE_BEACON ) THEN 
            l_job_params.EXTEND(1);
            l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('host_name', 1, 
                                       p_host_name, null);
            l_index := l_index+1;
            l_props := get_target_props(p_tgt_name, p_tgt_type);
            IF ( l_props IS NOT NULL )  THEN 
                l_job_params.EXTEND(1);
                l_job_params(l_index) := MGMT_JOB_PARAM_RECORD('properties', 1, 
                                           l_props, null);
                l_index := l_index+1;
            END IF;
        END IF;

        l_schedule := null;

        --Create the queue if not already created 
        l_queue_names.extend(1);
        l_queue_names(1) := JOB_NAME_PREFIX || l_tgt_id;
        SELECT count(1) INTO l_count
          FROM MGMT_JOB_QUEUES
         WHERE queue_name = l_queue_names(1);

        IF l_count=0 THEN
          MGMT_JOB_ENGINE.create_job_queue(l_queue_names(1));
        END IF;

        -- Submit the job
        MGMT_JOBS.submit_job( l_job_name,
                        JOB_DESCRIPTION, 
                        0,
                        JOB_TYPE, 
                        l_job_target_list,
                        l_job_params,
                        l_schedule,
                        -1,
                        l_job_id,
                        l_execution_ids,
                        null,
                        MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY,
                        null,
                        null,
                        l_queue_names);
    dbms_output.put_line('JOB_ID '||l_job_id);
        
    END SUBMIT_UPGRADE_JOB;

    PROCEDURE UPGRADE_TARGET_MEMBERS(p_target_name IN VARCHAR2, 
                                     p_target_type IN VARCHAR2)
    IS
        -- cursor to get the Web Application targets members
        CURSOR webapp_mem_tgt_cur (tgt_name IN VARCHAR2, tgt_type IN VARCHAR2) IS
            SELECT member_target_name, member_target_type, member_target_guid 
            FROM mgmt_target_memberships 
           WHERE composite_target_name = tgt_name 
             AND composite_target_type = tgt_type;
        l_members SMP_EMD_NVPAIR_ARRAY := null;
        i NUMBER := 1;
        l_sys_tgt_name mgmt_targets.target_name%TYPE;
        l_temp_tgt_name mgmt_targets.target_name%TYPE := null;
        l_tz_reg mgmt_targets.timezone_region%TYPE := null;
        l_non_host_emd_tgts NUMBER := 0;
    BEGIN
        IF ( p_target_name IS NULL ) OR ( p_target_type IS NULL ) OR 
           ( p_target_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
            return ;
        END IF;
        
        FOR sub_rec in webapp_mem_tgt_cur (p_target_name, p_target_type) LOOP 
            IF ( l_members IS null ) THEN 
                l_members := SMP_EMD_NVPAIR_ARRAY();
            END IF;
            l_members.extend(1);
            l_members(i) := SMP_EMD_NVPAIR(sub_rec.member_target_name, sub_rec.member_target_type);
            i := i+1;
        END LOOP;
        
        IF ( l_members IS NULL ) OR ( l_members.COUNT = 0 ) THEN 
            return;
        END IF;

        -- Do not create a system if the Web Application has 
        -- host and/or agent as member targets. This is to reduce 
        -- number of system targets created. 
        IF ( l_members.COUNT <= 2 ) THEN
            SELECT count(*) 
              INTO l_non_host_emd_tgts
              FROM mgmt_target_memberships 
             WHERE composite_target_name = p_target_name 
               AND composite_target_type = p_target_type
               AND member_target_type != MGMT_GLOBAL.G_HOST_TARGET_TYPE 
               AND member_target_type != MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
        END IF;

        IF ( l_members.COUNT <= 2 ) AND ( l_non_host_emd_tgts = 0 ) THEN 
            -- delete the "Contains" association between the Web Application 
            -- and all member targets. 
            DELETE
              FROM mgmt_target_assocs assoc
             WHERE assoc.source_target_guid
                IN (SELECT target_guid
                      FROM mgmt_targets
                     WHERE target_name = p_target_name
                       AND target_type = p_target_type)
               AND assoc.assoc_guid = mgmt_assoc.g_contains_guid;

            -- Retun without creating a system 
            return;
        END IF;

        l_sys_tgt_name := p_target_name||'_'||'System';
        BEGIN
            SELECT target_name
              INTO l_temp_tgt_name
              FROM mgmt_targets
             WHERE target_name=l_sys_tgt_name
               AND target_type = MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;

        IF ( l_temp_tgt_name IS NOT NULL ) THEN
            l_sys_tgt_name := p_target_name ||'_'||'System_5';
        END IF;

        --create a generic_system
        SELECT timezone_region
          INTO l_tz_reg
          FROM mgmt_targets
         WHERE target_name = l_members(1).name
           AND target_type = l_members(1).value;
        
        -- Create system
        mgmt_system.create_system(l_sys_tgt_name, 
                                  MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE,
                                  l_members, l_tz_reg );
        -- After creating the system with all the member targets 
        -- delete the "Contains" association between the Web Application 
        -- and all key components. 
        DELETE
          FROM mgmt_target_assocs assoc
         WHERE assoc.source_target_guid
            IN (SELECT target_guid
                  FROM mgmt_targets
                 WHERE target_name = p_target_name
                   AND target_type = p_target_type)
         AND assoc.assoc_guid = mgmt_assoc.g_contains_guid;

        -- Associate system
        -- This will create association between service, system and all 
        -- key components. 
        mgmt_service.set_system( p_target_name, p_target_type, 
                                 l_sys_tgt_name, MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE,
                                 l_members);

    END UPGRADE_TARGET_MEMBERS;




PROCEDURE UPGRADE_GENSVC_AVAIL( p_target_name IN VARCHAR2,
                                                  p_target_type IN VARCHAR2 )
AS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_beacon_name MGMT_TARGETS.target_name%TYPE;
  l_beacon_list MGMT_GENSVC_TGT_NAME_ARRAY;
  l_test_list MGMT_GENSVC_AV_TEST_ARRAY;
  l_test MGMT_GENSVC_AV_TEST;
  l_metric_name MGMT_METRICS.metric_name%TYPE;
  l_metric_column MGMT_METRICS.metric_column%TYPE;
  l_process_test BOOLEAN;
  l_count INTEGER;
  l_more BOOLEAN;

  CURSOR l_beacon_cursor IS
    SELECT mt.target_name 
      FROM MGMT_TARGETS mt, MGMT_BCN_TARGET mbt
     WHERE mbt.target_guid = l_target_guid
       AND mbt.participates_avail = 'Y'
       AND mbt.is_removing = 'N'
       AND mbt.is_local = 'N'
       AND mbt.target_guid <> mbt.beacon_target_guid
       AND mbt.beacon_target_guid = mt.target_guid
       AND mt.target_type = EMD_BCNTXN.p_beacon_type;

  CURSOR l_test_cursor IS
    SELECT tx.name, tx.txn_type, 
           DECODE(tx.is_representative, 'Y', 1, 0) as avail_test,
           DECODE(tx.state, 'NM', 0, 1) as monit_state
      FROM MGMT_BCN_TXN_DEFN tx
     WHERE tx.target_guid = l_target_guid;

  l_cur_test l_test_cursor%ROWTYPE;

BEGIN

  -- get target details
  SELECT target_guid
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- migrate beacons
  l_count := 0;
  l_more := TRUE;
  OPEN l_beacon_cursor;
  WHILE l_more LOOP
    FETCH l_beacon_cursor INTO l_beacon_name;
    l_more := l_beacon_cursor%FOUND;
    IF l_more THEN
      IF l_count = 0 THEN
        l_beacon_list := MGMT_GENSVC_TGT_NAME_ARRAY();
      END IF;
      l_count := l_count + 1;
      l_beacon_list.EXTEND(1);
      l_beacon_list(l_count) := l_beacon_name;
    END IF;
  END LOOP;
  CLOSE l_beacon_cursor;
  IF l_count > 0 THEN
    MGMT_GENSVC_AVAIL.SET_BEACONS(p_target_name, p_target_type, l_beacon_list);
  END IF;

  -- migrate tests
  l_count := 0;
  l_more := TRUE;
  OPEN l_test_cursor;
  WHILE l_more LOOP
    FETCH l_test_cursor INTO l_cur_test;
    l_more := l_test_cursor%FOUND;
    IF l_more THEN
      IF l_cur_test.txn_type = 'HTTP' THEN
        l_metric_name := 'http_response';
        l_metric_column := 'status';
        l_process_test := TRUE;
      ELSIF l_cur_test.txn_type = 'PING' THEN
        l_metric_name := 'ping';
        l_metric_column := 'host_status';
        l_process_test := TRUE;
      ELSE
        l_process_test := FALSE;
      END IF;
      IF l_process_test THEN
        l_test := MGMT_GENSVC_AV_TEST( l_cur_test.name, 
                                       l_cur_test.txn_type,
                                       l_metric_name,
                                       l_metric_column,
                                       l_cur_test.avail_test, 
                                       l_cur_test.monit_state );
        IF l_count = 0 THEN
          l_test_list := MGMT_GENSVC_AV_TEST_ARRAY();
        END IF;  
        l_count := l_count + 1;
        l_test_list.EXTEND(1);
        l_test_list(l_count) := l_test;
      END IF;
    END IF;
  END LOOP;
  CLOSE l_test_cursor;
  IF l_count > 0 THEN
    MGMT_GENSVC_AVAIL.SET_TESTS(p_target_name, p_target_type, l_test_list);
  END IF;

  -- set the eval logic to OR
  MGMT_GENSVC_AVAIL.SET_SVC_EVAL_LOGIC(p_target_name, p_target_type, 
                                       MGMT_GENSVC_AVAIL.k_or_eval);

  -- enable avail for the target
  MGMT_GENSVC_AVAIL.ENABLE_AVAIL(p_target_name, p_target_type);

  UPDATE MGMT_TARGET_PROPERTIES
     SET property_value = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP
   WHERE target_guid = l_target_guid
     AND property_name = MGMT_SERVICE.G_AVAIL_COMPUTATION_MECH
     AND property_type = MGMT_GLOBAL.G_INSTANCE_PROP_TYPE;
  IF SQL%NOTFOUND THEN
    INSERT INTO MGMT_TARGET_PROPERTIES(target_guid, property_name,
            property_type, property_value)
          VALUES ( l_target_guid,
                       MGMT_SERVICE.G_AVAIL_COMPUTATION_MECH, 
                       MGMT_GLOBAL.G_INSTANCE_PROP_TYPE, MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_beacon_cursor%ISOPEN THEN
      CLOSE l_beacon_cursor;
    END IF;
    IF l_test_cursor%ISOPEN THEN
      CLOSE l_test_cursor;
    END IF;
    RAISE;
 
END UPGRADE_GENSVC_AVAIL;


    PROCEDURE REMOVE_LOCAL_INST(p_tgt_name IN VARCHAR2, 
                                p_tgt_type IN VARCHAR2,
                                p_agent_name IN VARCHAR2, 
                                p_emd_url IN VARCHAR2,
                                p_host_name IN VARCHAR2)
    IS
        tgt_upgrade_bcn_prop mgmt_target_properties.property_value%TYPE;
        beacon_name VARCHAR2(256) := null;
        beacon_op number := CREATE_BEACON;
        bcn_tgt_name VARCHAR2(256) := null;
        bcn_tgt_type VARCHAR2(256) := null;
    BEGIN
        IF ( p_tgt_name IS NULL ) OR ( p_tgt_type IS NULL ) OR 
           (p_emd_url IS NULL ) OR 
           ( p_tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
            return;
        END IF;

        BEGIN
            SELECT property_value 
              INTO tgt_upgrade_bcn_prop 
              FROM mgmt_target_properties prop, mgmt_targets tgt
             WHERE prop.target_guid = tgt.target_guid
               AND tgt.target_name=p_tgt_name 
               AND target_type=p_tgt_type
               AND prop.property_name = WEBAPP_UPGRADE_BEACON;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN 
                tgt_upgrade_bcn_prop := NULL;
        END;
        dbms_output.put_line('tgt_upgrade_bcn_prop '||tgt_upgrade_bcn_prop);

        --check for target specific property 
        IF ( tgt_upgrade_bcn_prop IS NULL ) THEN 
            --check for ttarget specific property specific property 
            IF ( g_upgrade_beacon_prop = DONOT_REPLACE_WITH_BEACON ) THEN
                beacon_op := NOOP;
            ELSIF ( g_upgrade_beacon_prop IS NULL ) THEN 
                beacon_op := CREATE_BEACON;
            ELSE
                beacon_op := USE_EXISTING_BEACON;
                beacon_name := g_upgrade_beacon_prop;
            END IF;
        ELSIF ( tgt_upgrade_bcn_prop = DONOT_REPLACE_WITH_BEACON ) THEN 
            beacon_op := NOOP;
        ELSE 
            beacon_op := USE_EXISTING_BEACON;
            beacon_name := tgt_upgrade_bcn_prop;
        END IF;

        dbms_output.put_line('beacon_op '||beacon_op);
        IF ( beacon_op = NOOP ) THEN 
            dbms_output.put_line('NOOP');
            -- submit a job with beacon_target_name, beacon_Target_type as null
            submit_upgrade_job( p_tgt_name, p_tgt_type, beacon_op, null, null, p_host_name, p_agent_name );
        ELSIF ( beacon_op = USE_EXISTING_BEACON )THEN 
            dbms_output.put_line('USE_EXISTING_BEACON');
            -- get the beacon name and type from mgmt_targets to make sure they are valid 
            -- and submit job
            BEGIN 
                SELECT target_name, target_type 
                  INTO bcn_tgt_name, bcn_tgt_type
                  FROM mgmt_targets 
                 WHERE target_name=beacon_name
                   AND target_type = mgmt_global.g_beacon_target_type;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN 
                    bcn_tgt_name := NULL;
                    bcn_tgt_type := NULL;
            END;

            IF ( bcn_tgt_name IS NOT NULL ) AND (bcn_tgt_type IS NOT NULL ) THEN 
                submit_upgrade_job( p_tgt_name, p_tgt_type, beacon_op, bcn_tgt_name, MGMT_GLOBAL.G_BEACON_TARGET_TYPE, p_host_name, p_agent_name);
            ELSE
                dbms_output.put_line('CREATE_BEACON');
                beacon_op := CREATE_BEACON;
                bcn_tgt_name := get_new_beacon_name(p_tgt_name, p_tgt_type, p_emd_url, p_host_name);
                submit_upgrade_job( p_tgt_name, p_tgt_type, beacon_op, bcn_tgt_name, MGMT_GLOBAL.G_BEACON_TARGET_TYPE, p_host_name, p_agent_name);
            END IF;
        ELSIF ( beacon_op = CREATE_BEACON ) THEN 
            dbms_output.put_line('CREATE_BEACON');
            -- submit a job to create a new beacon 
            bcn_tgt_name := get_new_beacon_name(p_tgt_name, p_tgt_type, p_emd_url, p_host_name);
            submit_upgrade_job( p_tgt_name, p_tgt_type, beacon_op, bcn_tgt_name, MGMT_GLOBAL.G_BEACON_TARGET_TYPE, p_host_name, p_agent_name);
         END IF;
    END REMOVE_LOCAL_INST;


    PROCEDURE UPGRADE_WEBAPP
    IS
        -- cursor to get the Web Application targets 
        CURSOR webapp_tgt_cur IS 
            SELECT target_name, target_type, emd_url, host_name
            FROM mgmt_targets 
           WHERE target_type = MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE;
    
        agent_name MGMT_TARGETS.TARGET_NAME%TYPE;

    BEGIN
        
        BEGIN 
            SELECT PARAMETER_VALUE 
              INTO g_upgrade_beacon_prop
              FROM mgmt_parameters 
             WHERE PARAMETER_NAME = WEBAPP_UPGRADE_BEACON;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                g_upgrade_beacon_prop := NULL;
        END;
        dbms_output.put_line('g_upgrade_beacon_prop '||g_upgrade_beacon_prop);
        -- For each Web Application target do the following 

        -- Step 1. Upgrade Transaction Data
      
        
        -- Delete monitored_by association between webapp and agent
        -- Delete hosted_by association between webapp and host
        DELETE
          FROM mgmt_target_assocs assoc
         WHERE assoc.source_target_guid
            IN (SELECT target_guid
                  FROM mgmt_targets
                 WHERE target_type = MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE)
         AND (assoc.assoc_guid = mgmt_assoc.g_hosted_by_guid
                OR assoc.assoc_guid = mgmt_assoc.g_monitored_by_guid 
                OR assoc.assoc_guid = mgmt_assoc.g_remotely_monitored_by_guid );

        --  Step 2. Create associations for existing beacons except the local beacon 
        add_beacon_assocs;

        -- From the following step loop through each target 

        FOR sub_rec IN webapp_tgt_cur LOOP
            
            --  Step 3. Create a generic system and migrate all existing 10gR1 
            --          member targets to key components of the webapp
            upgrade_target_members(sub_rec.target_name, sub_rec.target_type);

            -- Step 4. Remove 10gR1 Transaction availability 
            --         Register transsactions with 10gR2 availability 
            --         Register Web Application target availability
 
             UPGRADE_GENSVC_AVAIL(sub_rec.target_name, sub_rec.target_type);

        
            -- Step 5. Remove Local Website target instance and replace with 
            --         Beacon target if required. 
            BEGIN
             SELECT target_name INTO agent_name
               FROM MGMT_TARGETS
              WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
                AND emd_url = sub_rec.emd_url
                AND host_name = sub_rec.host_name;
            EXCEPTION WHEN NO_DATA_FOUND THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, sub_rec.target_name || ' : ' || sub_rec.target_type || ' does not have an agent. Will not upgrade.');
              agent_name := NULL;
                      WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, sub_rec.target_name || ' : ' || sub_rec.target_type || SQLERRM); 
            END;

            IF ( agent_name IS NOT NULL) THEN
 
              remove_local_inst(sub_rec.target_name, sub_rec.target_type, agent_name,
                                      sub_rec.emd_url, sub_rec.host_name);
            END IF;
            -- Step 6. Upgrade key values part 1
        END LOOP;
        
    END UPGRADE_WEBAPP;

END WEBAPP_UPGRADE;
/
SHOW ERRORS;

---------------------------------------------------------
--Run the main methos to invoke the upgrade
---------------------------------------------------------
DECLARE
  l_wl_item_count NUMBER := 0;
BEGIN
    webapp_upgrade.upgrade_webapp;
    
    -- submit sync jobs for all beacon targets to repush beacon watch list items 
    FOR sub_rec in (SELECT target_name, target_type, target_guid
                      FROM mgmt_targets 
                     WHERE target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE) 
    LOOP
        SELECT count(*) 
          INTO l_wl_item_count 
          FROM mgmt_bcn_txn_defn 
         where target_guid = sub_rec.target_guid;
        
        -- submit jobs only if there is at least one test exists
        IF ( l_wl_item_count > 0 ) THEN 
            webapp_upgrade_job.SYNC_ASSOC_BEACONS( sub_rec.target_name, sub_rec.target_type );
        END IF;
    END LOOP;

    -- Delete the composite target type property
    DELETE 
      FROM mgmt_type_properties 
     WHERE target_type = MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE
       AND (property_name = MGMT_GLOBAL.G_IS_COMPOSITE_PROP OR property_name = MGMT_GLOBAL.G_IS_AGGREGATE_PROP);
      
END;
/
SHOW ERRORS;