Rem drv: <migrate type="data_upgrade" version="10.2.0.3"/>
Rem
Rem $Header: license_data_upgrade.sql 14-dec-2006.22:41:59 paachary Exp $
Rem
Rem license_data_upgrade.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      license_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      For Customers who are upgrading to 10.2.0.3 this file will be called
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    paachary    12/14/06 -  Added New Licensing Pack Information For 10.2.0.3
Rem                            Added logic to grant the new license packs introduced during 10.2.0.3
Rem                            to respective targets.
Rem    bram        06/06/06 - bug-5147049
Rem    acgopala    04/05/06 - bug-5118309
Rem    acgopala    04/05/06 - Created
Rem    acgopala    04/05/06 - bug-5118309
Rem    acgopala    04/05/06 - Created
Rem

-- Creating a tempopary table to handle the packs created before 10.2.0.3
CREATE TABLE admin_license_pre_10203 
     AS SELECT * FROM mgmt_admin_licenses;

@&EM_SQL_ROOT/core/10.2.0.3/license/license_post_creation.sql

--Prior to 10.2.0.2 ASM was associated with a  Database
--The Entries with respect to ASM in mgmt_licensed_targets table would be as
--<target-guid><pack_name><from_target_guid>
--<ASM GUID><pack><database_GUID>
--From 10.2.0.3 release onwards ASM will be a independent licensable target
--hence we need to update ASM related entries as mgmt_licensed_targets
----<ASM_GUID><pack><ASM_GUID>
DECLARE
 TYPE l_target_guid_table IS TABLE OF mgmt_targets.TARGET_GUID%TYPE INDEX BY BINARY_INTEGER;
      l_target_guid_arr l_target_guid_table;
BEGIN
SELECT asm.target_guid 
      BULK COLLECT 
      INTO l_target_guid_arr
      FROM mgmt_licensed_targets asm,mgmt_targets tar
      WHERE asm.target_guid=tar.target_guid AND
            tar.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE;
IF (l_target_guid_arr IS NOT NULL) AND (l_target_guid_arr.count>0) THEN
        FOR i IN 1..l_target_guid_arr.count
            LOOP 
                BEGIN
                    UPDATE MGMT_LICENSED_TARGETS
                       SET from_target_guid=l_target_guid_arr(i)
                     WHERE target_guid=l_target_guid_arr(i);
                EXCEPTION 
                     WHEN OTHERS THEN
                    NULL;
                END;
            END LOOP;
    END IF;
  COMMIT;
END;
/

-- The following block has been added to grant license on the targets for which new packs have been 
-- added post 10.2.0.2
-- 1. First take the new packs introduced during 10.2.0.3
-- 2. Pick up all the target types associated with these new packs from mgmt_license_definition
-- 3. Pick up all the corresponding targets to the above targrt types and grant license to these targets on those new packs
-- 4. Have a logic for the above to take care of iAS related packs
DECLARE
    l_target_type_arr   VARCHAR2_TABLE := VARCHAR2_TABLE();
    l_pack_label_arr    VARCHAR2_TABLE := VARCHAR2_TABLE();
    l_target_guid_arr   MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
    l_count             NUMBER DEFAULT 0;
BEGIN
    -- First take the new packs introduced during 10.2.0.3
    -- Pick up all the target types associated with these new packs from mgmt_license_definitions
   SELECT   mld.target_type,
            mld.pack_label,
            mt.target_guid
    BULK COLLECT INTO
            l_target_type_arr,
            l_pack_label_arr,
            l_target_guid_arr
    FROM    mgmt_license_definitions mld,
            mgmt_targets mt
    WHERE   mld.target_type = mt.target_type 
        AND mld.pack_label NOT IN
            ( SELECT pack_name 
              FROM admin_license_pre_10203 );

    -- Pick up all the corresponding targets to the above targrt types and grant license to these targets on those new packs
    IF (l_target_guid_arr.COUNT > 0 AND l_target_guid_arr IS NOT NULL) THEN
        FOR itgtguidcnt IN 1..l_target_guid_arr.COUNT
        LOOP
            em_license.grant_license
            (
                p_target_type   =>l_target_type_arr(itgtguidcnt),
                p_target_guid   =>l_target_guid_arr(itgtguidcnt),
                p_pack_label    =>l_pack_label_arr(itgtguidcnt)
            );
        END LOOP;
    END IF;


    -- This block grants license on iAS related packs introduced post beta to all the standalone iAS dependent target types.
    l_pack_label_arr.DELETE;

    SELECT distinct pack_label  pack_name
    BULK COLLECT INTO
         l_pack_label_arr
    FROM  mgmt_license_definitions
    WHERE   target_type=mgmt_global.G_IAS_TARGET_TYPE 
        AND pack_label NOT IN 
            (  SELECT pack_name
               FROM admin_license_pre_10203 );

    IF (l_pack_label_arr.COUNT > 0 AND l_pack_label_arr IS NOT NULL) THEN
        FOR inewiaspackscnt IN 1..l_pack_label_arr.COUNT
        LOOP
            FOR c1 IN
            (
                SELECT target_guid
                FROM   mgmt_targets
                WHERE  target_type IN
                (
                    SELECT  target_type
                    FROM    mgmt_licensable_target_types
                    WHERE   pack_target_type = mgmt_global.G_IAS_TARGET_TYPE 
                        AND target_type NOT IN (mgmt_global.G_IAS_TARGET_TYPE)
                )
            )
            LOOP
                -- Now check whether this ias dependent target is a standalone                
                SELECT    count(*)
                INTO      l_count
                FROM      mgmt_targets
                WHERE    target_guid IN
                        (
                            SELECT  source_target_guid
                            FROM    mgmt_target_assocs
                            WHERE   assoc_target_guid= c1.target_guid
                        )
                    AND  target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE;
                IF (l_count = 0) THEN -- this is a Stand Alone
                    BEGIN
                        INSERT INTO mgmt_licensed_targets
                        (target_guid, pack_name, from_target_guid)
                        VALUES
                        (c1.target_guid,l_pack_label_arr(inewiaspackscnt),c1.target_guid);
                    EXCEPTION
                        WHEN DUP_VAL_ON_INDEX THEN
                            NULL;
                    END;--exception was added since we are granting license
                        --by invoking 'insert into table' directly and not through grant_license api;
                        --wherein otherwise this exception would be handled in grant_license()
                END IF;
            END LOOP;-- end c1
        END LOOP;
    END IF;
    COMMIT;
END;
/

DROP TABLE admin_license_pre_10203;