Rem PLEASE DO NOT ADD ANY HEADER TO THIS FILE
Rem THIS FILE IS INVOKED FROM license_data_upgrade.sql BASED ON 
Rem CONDITIONS.
Rem $Header: licensing_script.sql 25-aug-2005.23:21:15 paachary Exp $
Rem
Rem licensing_script.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      licensing_script.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    paachary    08/25/05 - paachary_upgrdbgs
Rem    bram        08/24/05 - Created
Rem

DECLARE
--Approach Steps
--Step1 Grant License on Packs which were enabled in the previous version to licensable targets (with propagation). 
--Step2 Grant License on IAS related Packs which were enabled in the previous version to IAS Standalone Target Types.
--Step3 Grant License on Packs which have been made available in 10GR2 version (with propagation). 
--Step4 If such a new pack is IAS related than grant to Standalone IAS Dependent Types as well
--Step5 Set "Agree Access" on all targets to YES

    l_count       INTEGER;
    l_repos_owner mgmt_license_confirmation.confirmed_by%type:=mgmt_user.get_repository_owner;
    l_confirm     VARCHAR2(1):='Y';
    
    TYPE l_parent_target_type_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
         l_parent_target_type_arr     l_parent_target_type_table;
         l_ias_parent_target_type_arr l_parent_target_type_table;
    
    TYPE l_pack_name_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
         l_pack_name_arr     l_pack_name_table;
         l_ias_pack_name_arr l_pack_name_table;
	 l_new_ias_pack_name_arr l_pack_name_table;
    
    TYPE l_guid_table IS TABLE OF mgmt_targets.TARGET_GUID%TYPE INDEX BY BINARY_INTEGER;
         l_guid_arr l_guid_table;
BEGIN
       delete mgmt_licensed_targets;
       delete MGMT_LICENSE_CONFIRMATION;    
       --Step1 
       --Grant License on Packs which were enabled in the previous version to licensable targets (with propagation). 
    
     SELECT 
           DISTINCT a.pack_target_type parent_target_type,
           b.pack_label    pack_name,
           c.target_guid target_guid
     BULK COLLECT             
     INTO
           l_parent_target_type_arr,
           l_pack_name_arr,
           l_guid_arr
     FROM  
           mgmt_licensable_target_types a,
           mgmt_license_definitions b,
           mgmt_targets c,
           licensed_packs_1014 d
     WHERE 
           a.pack_target_type=b.target_type AND   
           c.target_type=b.target_type      AND
           b.pack_label =d.pack_name;
      
    IF (l_parent_target_type_arr IS NOT NULL) AND (l_parent_target_type_arr.count>0) THEN

        FOR i IN 1..l_parent_target_type_arr.count
            LOOP
               --Grant License to the licensable target types
                   em_license.grant_license
                       (
                           p_target_type=>l_parent_target_type_arr(i),
                           p_target_guid=>l_guid_arr(i),
                           p_pack_label=>l_pack_name_arr(i)                        
                       );
            END LOOP;
    END IF;
    --End of Step1--

    -- Step Two Start--Grant License on IAS related Packs which were enabled in the previous version to IAS Standalone Target Types.
    
    --Get IAS Related Packs which was enabled in previous version(10.1.0.4)
    SELECT  
         pack_label  pack_name
    BULK COLLECT             
    INTO  
         l_ias_pack_name_arr
    FROM  
         mgmt_license_definitions a,
	 licensed_packs_1014      b
    WHERE 
         a.target_type=mgmt_global.G_IAS_TARGET_TYPE AND
	 a.pack_label=b.pack_name;   
            
    IF (l_ias_pack_name_arr IS NOT NULL) AND (l_ias_pack_name_arr.count>0) THEN
    
        FOR j IN 1..l_ias_pack_name_arr.count
            LOOP
            -- Get list of targets whose Target Type is a child Target Type of "oracle_ias" (but not hosts) 
            -- and  do not have any packs licensed.  These invariably are standalone OC4Js etc.  
                -- Grant License on all applicable packs to them.        
                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, mgmt_global.G_HOST_TARGET_TYPE)
                            ) 
                )
                LOOP
                    -- Now check whether this ias depedent 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_ias_pack_name_arr(j), c1.target_guid);
                        EXCEPTION 
                            WHEN DUP_VAL_ON_INDEX THEN
                            NULL;
                        END;--exception was added since we are granting license
                            --by 'insert into table' directly and not through grant_license api;
                            --wherein otherwise this exception would be handled in grant_license()
                    END IF;--end if (l_count=0)
                END LOOP;-- end c1-
            END LOOP;
    END IF;
    --End of Step2    

    --Start of Step3--Grant License on Packs which have been made available in present(10.2) version (with propagation). 
    SELECT DISTINCT 
           b.pack_target_type parent_target_type,
           a.pack_label    pack_name,
           c.target_guid target_guid
    BULK COLLECT             
    INTO  
          l_parent_target_type_arr,
          l_pack_name_arr,
          l_guid_arr
    FROM  
          mgmt_license_definitions a,
          mgmt_licensable_target_types b,
          mgmt_targets c
    WHERE 
          a.target_type=b.pack_target_type AND
          c.target_type=b.pack_target_type AND
          a.pack_label NOT IN (
	                       SELECT pack_label 
                               FROM   all_packs_1014
                              );

    IF (l_parent_target_type_arr IS NOT NULL) AND (l_parent_target_type_arr.count>0) THEN

        FOR k IN 1..l_parent_target_type_arr.count
            LOOP
                --Grant License on packs which is present in the previous version
                 --to all applicable parent target type 
                 em_license.grant_license
                     (
                          p_target_type=>l_parent_target_type_arr(k),
                          p_target_guid=>l_guid_arr(k),
                          p_pack_label=>l_pack_name_arr(k)
                     );
            END LOOP;-- i in 1..l_pack_list.COUNT
    END IF;

   --Step4 Start --If the new pack is IAS related grant license to the IAS Standalone
   --Get IAS Related Packs which is enabled in the new version(10.1.0.4)

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

    IF (l_new_ias_pack_name_arr IS NOT NULL) AND (l_new_ias_pack_name_arr.count>0) THEN
        FOR m IN 1..l_new_ias_pack_name_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, mgmt_global.G_HOST_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_new_ias_pack_name_arr(m),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 1..l_ias_pack_name_arr.count
    END IF;--end l_new_ias_pack_name_arr IS NOT NULL
   -- Step4 End

    --Start of Step 5-- Set "Agree Access" to Yes to all the targets which are licensed 
        SELECT DISTINCT 
             target_guid
        BULK COLLECT
        INTO
            l_guid_arr
        FROM
            mgmt_licensed_targets;
 
    IF (l_guid_arr IS NOT NULL) AND (l_guid_arr.count>0) THEN

        FOR k IN 1..l_guid_arr.count
            LOOP
               INSERT INTO MGMT_LICENSE_CONFIRMATION
                    (TARGET_GUID,CONFIRMATION,CONFIRMED_BY,CONFIRMED_TIME)
                 VALUES
                     (l_guid_arr(k),l_confirm,l_repos_owner,SYSDATE);
            END LOOP;
    END IF;
    COMMIT;
    --drop the temporary tables all_packs_1014 and licensed_packs_1014
   execute immediate 'drop table all_packs_1014';
   execute immediate 'drop table licensed_packs_1014';
END;    
/