Rem drv: <migrate type="data_upgrade" version="10.2.0.4.0"/>
Rem
Rem $Header: license_data_upgrade.sql 30-apr-2007.09:28:46 dgiaimo Exp $
Rem
Rem license_data_upgrade.sql
Rem
Rem Copyright (c) 2007, 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      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    dgiaimo     04/26/07 - Changing 10.2.4.0 to 10.2.0.4
Rem    dgiaimo     04/30/07 - Backport dgiaimo_bug-6014523 from main
Rem    smudumba    03/06/07 - license data upgrade for 10.2.4.0.0 upgrade
Rem    smudumba    03/06/07 - Created
Rem

CREATE TABLE admin_license_pre_10240 
     AS SELECT * FROM mgmt_admin_licenses;

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

-- The following block has been added to grant license on the targets for which new packs have been 
-- added post 10.2.0.3
-- 1. First take the new packs introduced during 10.2.0.4
-- 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.4
    -- 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_10240 );

    -- 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_10240 );

    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_10240;