Rem PLEASE DO NOT PUT ANY HEADER TO THIS FILE AS THIS IS CALLED FROM
Rem license_data_upgrade.sql BASED ON SOME CONDITIONS
Rem $Header: licensing_script_post_beta.sql 23-sep-2005.02:10:25 paachary Exp $
Rem
Rem licensing_script_post_beta.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      licensing_script_post_beta.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem     In beta version, host(s) would have derived license from pecks belonging to DB and iAS.
Rem     In the prod version, the host(s) will now be de-linked from these packs and they (hosts)
Rem     would now have license on following packs:
Rem     1.  nos_config
Rem     2.  host_smp
Rem     3.  provisioning
Rem     First Step: to delink host from the AS and DB packs
Rem     Second Step: Get all the new packs introduced post beta. (admin_licenses_beta table is already created in license_schema_upgrade.sql)
Rem     Third Step:  to enable license on all new packs on the other target types being introduced post-beta.
Rem                  This includes host and standalone iAS dependent target_types along with others.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    paachary    09/23/05 - Host is no longer associated to storage smp
Rem    paachary    09/22/05 - paachary_bug-4610525
Rem    paachary    09/20/05 - grabtrans
Rem    sdantkal    09/19/05 - Created
Rem

SET ECHO ON

DECLARE

    TYPE p_targettype_array is TABLE OF mgmt_license_definitions.target_type%TYPE;
    p_target_type_arr   p_targettype_array;
    TYPE p_packlabel_array is TABLE OF mgmt_license_definitions.pack_label%TYPE;
    p_pack_label_arr    p_packlabel_array;
    TYPE p_targetguid_array is TABLE OF mgmt_targets.target_guid%TYPE;
    p_target_guid_arr   p_targetguid_array;
    l_count             NUMBER DEFAULT 0;

BEGIN
    -- This block de-links the host from the packs derived from iAS and DB.

    SELECT  target_guid
    BULK COLLECT INTO
            p_target_guid_arr
    FROM    mgmt_targets
    WHERE   target_type='host';

    IF (p_target_guid_arr.COUNT > 0 AND p_target_guid_arr IS NOT NULL) THEN
        FOR ihostcnt IN 1..p_target_guid_arr.COUNT
        LOOP
            DELETE mgmt_licensed_targets
            WHERE  target_guid = p_target_guid_arr(ihostcnt);
        END LOOP;
    END IF;

   -- This block grants license on packs introduced post beta to all the parent licensable target types.
   p_target_guid_arr.DELETE;

   SELECT   mld.target_type,
            mld.pack_label,
            mt.target_guid
    BULK COLLECT INTO
            p_target_type_arr,
            p_pack_label_arr,
            p_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_licenses_beta );
  
    IF (p_target_guid_arr.COUNT > 0 AND p_target_guid_arr IS NOT NULL) THEN
        FOR itgtguidcnt IN 1..p_target_guid_arr.COUNT
        LOOP
            em_license.grant_license
            (
                p_target_type=>p_target_type_arr(itgtguidcnt),
                p_target_guid=>p_target_guid_arr(itgtguidcnt),
                p_pack_label=>p_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.
    p_pack_label_arr.DELETE;

    SELECT distinct pack_label  pack_name
    BULK COLLECT INTO
         p_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_licenses_beta );

    IF (p_pack_label_arr.COUNT > 0 AND p_pack_label_arr IS NOT NULL) THEN
        FOR inewiaspackscnt IN 1..p_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,p_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;

    -- This block sets the "Agree Access" to Yes to all the targets which are licensed 
    p_target_guid_arr.DELETE;

    SELECT DISTINCT target_guid
    BULK COLLECT INTO  p_target_guid_arr
    FROM   mgmt_licensed_targets
    MINUS
    SELECT  target_guid
    FROM    mgmt_license_confirmation
    WHERE   confirmation='Y' 
        AND confirmed_by = mgmt_user.get_repository_owner;

    IF ((p_target_guid_arr IS NOT NULL) AND (p_target_guid_arr.COUNT>0)) THEN

        FOR k IN 1..p_target_guid_arr.COUNT
            LOOP
               INSERT INTO mgmt_license_confirmation
                    (target_guid,confirmation,confirmed_by,confirmed_time)
                 VALUES
                     (p_target_guid_arr(k),'Y',mgmt_user.get_repository_owner,SYSDATE);
            END LOOP;
    END IF;
    COMMIT;

    -- This blocks drops the temporary table being used
    EXECUTE IMMEDIATE 'DROP TABLE admin_licenses_beta';
END;
/