Rem drv: <create type="pkgbodys" pos="license/license_rac_pkgbodys.sql+"/>
Rem
Rem $Header: mgmt_license_pkgbody.sql 01-jul-2005.21:26:29 gsbhatia Exp $
Rem
Rem mgmt_license_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      mgmt_license_pkgbody.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    gsbhatia    07/01/05 - New repmgr header impl 
Rem    bram        05/29/05 - Changed Standalone(licensing) query
Rem    bram        02/15/05 - Modified the no validation routines 
Rem                           to contain only pack name and target_guid in
Rem                           license_check api call.
Rem
Rem    paachary    02/13/05 - paachary_license_api
Rem    paachary    02/10/05 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_license
AS
-- Procedure         : validate_target_type
-- Private Routine
-- Purpose           : The input target type is validated and checked if it belongs to a valid licensable target type
--                     In case of target type being HOST, ASM etc, then appropriate msg is returned.
--                     It only allows parent licensable targt types or iAS member targets.
-- Input Parameters  : p_target_type   -> input target type
-- Output Parameters : p_txn_msg_array -> This array contains sucess and failure msgs.
--                     p_return_status -> Returns a Number: 0 -> Any failure occured during processing.
--                                                          1 -> If the entire processing is a success.
PROCEDURE validate_target_type
          (
             p_target_type   IN VARCHAR2,
             p_txn_msg_array OUT SMP_EMD_STRING_ARRAY,
             p_return_status OUT NUMBER
          )
IS
    l_count              NUMBER DEFAULT 0;
    l_member_count       NUMBER DEFAULT 0;
    p_txn_msg_counter    NUMBER DEFAULT 0;

BEGIN
    p_return_status := 1;
-- this if for parent licensable target type
    p_txn_msg_array := SMP_EMD_STRING_ARRAY();

    SELECT
        count(*)
    INTO
        l_count
    FROM
        mgmt_licensable_target_types
    WHERE
        target_type = p_target_type;
    IF (l_count = 0) THEN
           p_txn_msg_array.extend;
           p_txn_msg_counter := p_txn_msg_counter + 1;
           p_txn_msg_array(p_txn_msg_counter) := 'Failure -'||p_target_type||' is a not valid target type';
           p_return_status := 0;
           RETURN;
    END IF;

    SELECT
       count(*)
    INTO
        l_count
    FROM
        mgmt_licensable_target_types
    WHERE
        pack_target_type = p_target_type;

    IF (l_count > 0) THEN
        -- this is a valid parent licenable target type
        p_txn_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_target_type||' is a valid licensable target type';
        p_return_status := 1;
    ELSE
    -- this is for iAS member target
        SELECT
            count(*)
        INTO
            l_member_count
        FROM
            mgmt_licensable_target_types
        WHERE
            target_type=p_target_type AND
            pack_target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE and
            target_type not in (MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, MGMT_GLOBAL.G_HOST_TARGET_TYPE);
        IF (l_member_count > 0) THEN
            -- this is a valid iAS member target type
           p_txn_msg_array.extend;
           p_txn_msg_counter := p_txn_msg_counter + 1;
           p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_target_type||' is a valid licensable target type';
           p_return_status := 1;
        ELSE
            -- this is a child target type
            -- cannot directly grant / revoke license on packs directly to a child
           p_txn_msg_array.extend;
           p_txn_msg_counter := p_txn_msg_counter + 1;
           p_txn_msg_array(p_txn_msg_counter) := 'Failure -cannot grant / revoke license on packs directly to a child target type '||p_target_type;
           p_return_status := 0;
        END IF;
    END IF;
END validate_target_type;

-- Procedure         : validate_pack_list
-- Private Routine
-- Purpose           : To populate the pack array list with packs applicable to the input target type.
--                     If input target type is not a licensable parent target type, then the input target type
--                     is checked if there are any parent target types corresponding to the input target type.
--                     In case the input pack list is empty, the routine populates the another array of pack lists
--                     with all teh valid packs corresponding to the input target type.
--                     In case the input pack list contains some packs, then the input target type
--                     is checked if there are any parent target types corresponding to the input target type.
--                     Only the valid pack list array is returned back.
-- Input Parameters  : p_target_type     -> input target type
--                     p_pack_list_array -> input pack list (NULL signifies "ALL").
-- Output Parameters : p_valid_pack_list_array -> output pack list containing only valid packs applicable to target type.
--                     p_txn_msg_array -> This array contains sucess and failure msgs.
--                     p_return_status -> Returns a Number: 0 -> Any failure occured during processing.
--                                                          1 -> If the entire processing is a success.
PROCEDURE validate_pack_list
          (
              p_target_type            IN VARCHAR2,
              p_pack_list_array        IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_valid_pack_list_array  OUT SMP_EMD_STRING_ARRAY,
              p_txn_msg_array          OUT SMP_EMD_STRING_ARRAY,
              p_return_status          OUT NUMBER
          )
IS
    p_valid_pack_counter   NUMBER DEFAULT 0;
    l_count                NUMBER DEFAULT 0;
    l_pack_count           NUMBER DEFAULT 0;
    p_txn_msg_counter      NUMBER DEFAULT 0;
BEGIN
    p_return_status := 1;
    p_txn_msg_array:= SMP_EMD_STRING_ARRAY();
    p_valid_pack_list_array := SMP_EMD_STRING_ARRAY();
    IF (p_pack_list_array IS NULL OR p_pack_list_array.COUNT= 0) THEN
        SELECT
            count(*)
        INTO
            l_count
        FROM
            mgmt_licensable_target_types
        WHERE
            pack_target_type= p_target_type;

    IF (l_count >0) then
        FOR c_pack_list IN
        (
            SELECT
                pack_label
            FROM
                mgmt_license_definitions
             WHERE
                target_type = p_target_type
        )
         LOOP
             p_valid_pack_list_array.extend;
             p_valid_pack_counter := p_valid_pack_counter + 1;
             p_valid_pack_list_array(p_valid_pack_counter) := c_pack_list.pack_label;
             p_txn_msg_array.extend;
             p_txn_msg_counter := p_txn_msg_counter + 1;
             p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_valid_pack_list_array(p_valid_pack_counter)||' is a valid pack for '||p_target_type;
             p_return_status := 1;
         END LOOP;
    ELSIF (l_count = 0) THEN
        FOR c_parent_target_type IN
            (
            SELECT
                pack_target_type
            FROM
                mgmt_licensable_target_types
            WHERE
                target_type = p_target_type
           )
        LOOP
            FOR c_pack_list IN
            (
                SELECT
                    pack_label
                FROM
                    mgmt_license_definitions
                WHERE
                    target_type = c_parent_target_type.pack_target_type
            )
            LOOP
                 p_valid_pack_list_array.extend;
                 p_valid_pack_counter := p_valid_pack_counter + 1;
                 p_valid_pack_list_array(p_valid_pack_counter) := c_pack_list.pack_label;
                 p_txn_msg_array.extend;
                 p_txn_msg_counter := p_txn_msg_counter + 1;
                 p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_valid_pack_list_array(p_valid_pack_counter)||' is a valid pack for '||p_target_type;
            END LOOP;
        END LOOP;
    END IF;
    ELSE
        FOR p_pack_list_counter in 1..p_pack_list_array.COUNT
        LOOP
            SELECT
                count(*)
            INTO
                l_count
            FROM
                mgmt_licensable_target_types
            WHERE
                pack_target_type= p_target_type;

            IF (l_count >0) THEN
                SELECT
                    COUNT(*)
                INTO
                    l_pack_count
                FROM
                    mgmt_license_definitions
                where
                    pack_label = p_pack_list_array(p_pack_list_counter) and
                    target_type = p_target_type;

                IF (l_pack_count = 0) THEN
                    p_txn_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_txn_msg_array(p_txn_msg_counter) := 'Failure - Not a valid combination: '||p_pack_list_array(p_pack_list_counter)||' and '||p_target_type;
                    p_return_status := 0;
                ELSE
                    p_valid_pack_list_array.extend;
                    p_valid_pack_counter := p_valid_pack_counter + 1;
                    p_valid_pack_list_array(p_valid_pack_counter) := p_pack_list_array(p_pack_list_counter);
                    p_txn_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_valid_pack_list_array(p_valid_pack_counter)||' is a valid pack for '||p_target_type;
                END IF;
            ELSIF (l_count = 0) THEN
                FOR c_parent_target_type IN
                (
                    SELECT
                        pack_target_type
                    FROM
                        mgmt_licensable_target_types
                    WHERE
                        target_type = p_target_type
                )
                LOOP
                    SELECT
                        count(*)
                    INTO
                        l_pack_count
                    FROM
                        mgmt_license_definitions
                    WHERE
                        pack_label = p_pack_list_array(p_pack_list_counter) and
                        target_type = c_parent_target_type.pack_target_type;

                    IF (l_pack_count = 0) THEN
                        p_txn_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_txn_msg_array(p_txn_msg_counter) := 'Failure - Not a valid combination: '||p_pack_list_array(p_pack_list_counter)||' and '||p_target_type;
                        p_return_status := 0;
                    ELSE
                        p_valid_pack_list_array.extend;
                        p_valid_pack_counter := p_valid_pack_counter + 1;
                        p_valid_pack_list_array(p_valid_pack_counter) := p_pack_list_array(p_pack_list_counter);
                        p_txn_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_txn_msg_array(p_txn_msg_counter) := 'Success -'||p_valid_pack_list_array(p_valid_pack_counter)||' is a valid pack for '||p_target_type;
                    END IF;
                END LOOP;
            END IF;
        END LOOP;
    END IF;
END validate_pack_list;


-- Procedure         : validate_target_list
-- Private Routine
-- Purpose           : To populate the target array list with targets belonging to the input target type.
--                     This routine is used to populate all the targets applicable to the target type, in case
--                     the input target array list is empty.
--                     In case the input target array list contains some targets, then the targets are validated
--                     and appropriate msg is returned. The routine returns only the valid list of target array.
-- Input Parameters  : p_target_type             -> input target type
--                     p_target_list_array       -> input pack list (NULL signifies "ALL").
-- Output Parameters : p_valid_target_list_array -> output target list containing only valid targets applicable to target type.
--                     p_txn_msg_array         -> This array contains sucess and failure msgs.
--                     p_return_status         -> Returns a Number: 0 -> Any failure occured during processing.
--                                                                  1 -> If the entire processing is a success.
PROCEDURE validate_target_list
          (
              p_target_type             IN VARCHAR2,
              p_target_list_array       IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_no_validate_flag        IN  VARCHAR2 DEFAULT NULL,
              p_valid_target_list_array OUT SMP_EMD_STRING_ARRAY,
              p_txn_msg_array           OUT SMP_EMD_STRING_ARRAY,
              p_return_status           OUT NUMBER
          )
IS

    p_is_rac               BOOLEAN DEFAULT FALSE;
    p_valid_target_counter NUMBER DEFAULT 0;
    p_txn_msg_counter      NUMBER DEFAULT 0;
    l_count                NUMBER DEFAULT 0;
    p_target_guid          mgmt_targets.target_guid%type;
BEGIN
    p_return_status := 1;
    p_valid_target_list_array := SMP_EMD_STRING_ARRAY();
    p_txn_msg_array := SMP_EMD_STRING_ARRAY();
    IF (p_target_list_array IS NULL OR p_target_list_array.COUNT = 0) THEN
        FOR c_target_list IN
        (
         SELECT
             target_name,
             target_guid
         FROM
             mgmt_targets
         WHERE
             target_type = p_target_type
        )
        LOOP
           p_is_rac := FALSE;
           p_valid_target_counter := p_valid_target_counter + 1;
           p_txn_msg_counter := p_txn_msg_counter + 1;
           IF (p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) THEN
               IF (p_no_validate_flag IS NULL) THEN
                       p_is_rac := em_rac_license.is_racmember
                                   (
                                       p_target_type => p_target_type,
                                       p_target_guid => c_target_list.target_guid
                                   );
               END IF;
           END IF;
           IF (p_is_rac = TRUE) THEN
               p_txn_msg_array.extend;
               p_txn_msg_array(p_txn_msg_counter) := 'Failure - Cannot directly grant / revoke license to RAC database instance:  '||c_target_list.target_name;
               p_return_status := 0;
           ELSE
               p_valid_target_list_array.extend;
               p_valid_target_list_array(p_valid_target_counter) := c_target_list.target_name;
               p_txn_msg_array.extend;
               p_txn_msg_array(p_txn_msg_counter) := 'Success - '||c_target_list.target_name||' is a valid target of '||p_target_type;
           END IF;
        END LOOP;
    ELSE
        FOR p_target_list_counter in 1..p_target_list_array.COUNT
        LOOP
            p_is_rac := FALSE;
            p_valid_target_counter := p_valid_target_counter + 1;
            p_txn_msg_counter := p_txn_msg_counter + 1;

            SELECT
                count(*)
            INTO
                l_count
            FROM
                mgmt_targets
            WHERE
                target_name = p_target_list_array(p_target_list_counter) AND
                target_type = p_target_type;
            IF (l_count = 0) THEN
                p_txn_msg_array.extend;
                p_txn_msg_array(p_txn_msg_counter) := 'Failure - '||p_target_list_array(p_target_list_counter)||' is not a valid target of '||p_target_type;
                p_return_status := 0;
            ELSE
                p_target_guid := mgmt_target.get_target_guid
                                 (
                                      target_name_in=>p_target_list_array(p_target_list_counter),
                                      target_type_in=>p_target_type
                                 );
                IF(p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) THEN
                    IF (p_no_validate_flag IS NULL) THEN
                            p_is_rac := em_rac_license.is_racmember
                                        (
                                            p_target_type => p_target_type,
                                            p_target_guid => p_target_guid
                                        );
                    END IF;
                END IF;
                IF (p_is_rac = TRUE) THEN
                    p_txn_msg_array.extend;
                    p_txn_msg_array(p_txn_msg_counter) := 'Failure - Cannot directly grant / revoke license to RAC database instance for '||p_target_list_array(p_target_list_counter);
                    p_return_status := 0;
                ELSE
                    p_valid_target_list_array.extend;
                    p_valid_target_list_array(p_valid_target_counter) := p_target_list_array(p_target_list_counter);
                    p_txn_msg_array.extend;
                    p_txn_msg_array(p_txn_msg_counter) := 'Success - '||p_target_list_array(p_target_list_counter)||' is a valid target of '||p_target_type;
                END IF;
            END IF;
        END LOOP;
    END IF;
END validate_target_list;


-- Procedure         : is_target_standalone
-- Private Routine
-- Purpose           : This routine is used to validate whether the iAS member target type is a standalone target type
-- Input Parameters  : p_target_guid             -> input target guid
-- Output Parameters : TRUE  -> If target is a standalone target
--                     FALSE -> If target is associated with the iAS in the system.
FUNCTION is_target_standalone
         (
             p_target_guid IN VARCHAR2
         )
RETURN BOOLEAN
IS
    l_count    NUMBER DEFAULT 0;
BEGIN
    SELECT
        count(*)
    INTO
        l_count
    FROM
        mgmt_targets
    WHERE
        target_guid
        IN
          (
          SELECT
              source_target_guid
          FROM
              mgmt_target_assocs
          WHERE
              assoc_target_guid= p_target_guid
          ) AND
    target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE;
    IF (l_count = 0) THEN
        RETURN TRUE; -- the target is a standalone target
    ELSE
       RETURN FALSE;
    END IF;
END is_target_standalone;

PROCEDURE grant_license_with_validation
          (
              p_target_type            IN VARCHAR2,
              p_pack_list_array        IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_target_list_array      IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_transctional_msg_array OUT SMP_EMD_STRING_ARRAY,
              p_return_status          OUT NUMBER
          )
IS
    p_valid_target_list_array   SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_valid_pack_list_array     SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_txn_msg_array             SMP_EMD_STRING_ARRAY;
    p_txn_msg_counter           NUMBER DEFAULT 0;
    p_is_standalone             BOOLEAN DEFAULT FALSE;
    l_current_license_status    VARCHAR2(10);
    l_member_count              NUMBER DEFAULT 0;
    p_target_guid               mgmt_targets.target_guid%type;
	is_failure                  NUMBER DEFAULT 1;
BEGIN
    p_transctional_msg_array := SMP_EMD_STRING_ARRAY();
    is_failure := 1;
    validate_target_type
    (
        p_target_type,
        p_txn_msg_array,
        p_return_status
    );
	IF (p_return_status = 0) THEN
	    is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i in 1..p_txn_msg_array.COUNT
    LOOP
	    p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_return_status = 0) THEN
        RETURN;
    END IF;

    validate_target_list
    (
        p_target_type => p_target_type,
        p_target_list_array => p_target_list_array,
        p_valid_target_list_array => p_valid_target_list_array,
        p_txn_msg_array => p_txn_msg_array,
        p_return_status => p_return_status
    );
    IF (p_return_status = 0) THEN
	    is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i in 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
    p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Target(s) of target type '||p_target_type;
        is_failure := 0;
    END IF;

    validate_pack_list
    (
        p_target_type,
        p_pack_list_array,
        p_valid_pack_list_array,
        p_txn_msg_array,
        p_return_status
    );
    IF (p_return_status = 0) THEN
	    is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Pack(s) of target type '||p_target_type;
        is_failure := 0;
    END IF;

    IF ((p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) OR
       (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0)) THEN
        p_return_status := is_failure;
        RETURN;
    END IF;

    -- if p_target_type= ias_member_type (no host, website)
    -- check whether he is a standalone target or not
    -- If yes, insert records into mgmt_licensed_targets
    -- If Not, return an error message
    SELECT
        count(*)
    INTO
        l_member_count
    FROM
        mgmt_licensable_target_types
    WHERE
        target_type=p_target_type AND
        pack_target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE and
        target_type not in (MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, MGMT_GLOBAL.G_HOST_TARGET_TYPE, MGMT_GLOBAL.G_IAS_TARGET_TYPE);

    IF (l_member_count > 0) THEN
        FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
        LOOP
            FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
            LOOP
                p_target_guid := mgmt_target.get_target_guid
                                (
                                    target_name_in=>p_valid_target_list_array(p_targets_counter),
                                     target_type_in=>p_target_type
                                 );

                p_is_standalone := is_target_standalone
                                   (
                                       p_target_guid
                                    );
                IF (p_is_standalone = TRUE) THEN
                    l_current_license_status:= em_license.is_target_pack_licensed
                                               (
                                                   p_target_guid=>p_target_guid,
                                                   p_pack_label=>p_valid_pack_list_array(p_packs_counter),
                                                   p_from_target_guid => p_target_guid
                                               );
                    IF (l_current_license_status='FALSE') THEN
                        INSERT INTO
                            mgmt_licensed_targets
                            (
                                target_guid,
                                pack_name,
                                from_target_guid
                            )
                        VALUES
                            (
                                p_target_guid,
                                p_valid_pack_list_array(p_packs_counter),
                                p_target_guid
                            );
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                    ELSE
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Success - target '||p_valid_target_list_array(p_targets_counter)||' already licensed on '||p_valid_pack_list_array(p_packs_counter);
                    END IF;
                ELSE -- standalone false
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Cannot grant license on '||p_valid_pack_list_array(p_packs_counter)||' to '||p_valid_target_list_array(p_targets_counter)||'. It is not a standalone target';
                    is_failure := 0;
                END IF;
            END LOOP;
        END LOOP;
    ELSE -- other than ias member targets
        FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
        LOOP
            FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
            LOOP
                BEGIN
                    em_license.grant_license
                    (
                        p_target_type  => p_target_type,
                        p_target_name  => p_valid_target_list_array(p_targets_counter),
                        p_pack_label   => p_valid_pack_list_array(p_packs_counter)
                    );
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                EXCEPTION
                    WHEN OTHERS THEN
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - processing '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter);
                        p_transctional_msg_array(p_txn_msg_counter) := p_transctional_msg_array(p_txn_msg_counter)||':'||sqlerrm;
                        is_failure := 0;
                END;
            END LOOP;
        END LOOP;
    END IF;
   COMMIT WORK;
   p_return_status := is_failure;
END grant_license_with_validation;

PROCEDURE revoke_license_with_validation
          (
              p_target_type            IN VARCHAR2,
              p_pack_list_array        IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_target_list_array      IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_transctional_msg_array OUT SMP_EMD_STRING_ARRAY,
              p_return_status          OUT NUMBER
          )
IS
    p_valid_target_list_array   SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_valid_pack_list_array     SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_txn_msg_array             SMP_EMD_STRING_ARRAY;
    p_txn_msg_counter           NUMBER DEFAULT 0;
    p_is_standalone             BOOLEAN DEFAULT FALSE;
    l_current_license_status    VARCHAR2(10);
    l_member_count              NUMBER DEFAULT 0;
    p_target_guid               mgmt_targets.target_guid%type;
	is_failure                  NUMBER DEFAULT 1;
BEGIN
    p_transctional_msg_array := SMP_EMD_STRING_ARRAY();
    p_return_status := 1;
    validate_target_type
    (
        p_target_type,
        p_txn_msg_array,
        p_return_status
    );
	IF (p_return_status = 0) THEN
        is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i in 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_return_status = 0) THEN
        RETURN;
    END IF;

    validate_target_list
    (
        p_target_type => p_target_type,
        p_target_list_array => p_target_list_array,
        p_valid_target_list_array => p_valid_target_list_array,
        p_txn_msg_array => p_txn_msg_array,
        p_return_status => p_return_status
    );
    IF (p_return_status = 0) THEN
        is_failure := 0;
    END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i in 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Target(s) of target type '||p_target_type;
        is_failure := 0;
    END IF;

    validate_pack_list
    (
        p_target_type,
        p_pack_list_array,
        p_valid_pack_list_array,
        p_txn_msg_array,
        p_return_status
    );
    IF (p_return_status = 0) THEN
        is_failure := 0;
    END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Pack(s) of target type '||p_target_type;
        is_failure := 0;
    END IF;

    IF ((p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) OR
       (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0)) THEN
	   p_return_status := is_failure;
       RETURN;
    END IF;

    -- if p_target_type= ias_member_type (no host, website)
    -- check whether he is a standalone target or not
    -- If yes, insert records into mgmt_licensed_targets
    -- If Not, return an error message
    SELECT
        count(*)
    INTO
        l_member_count
    FROM
        mgmt_licensable_target_types
    WHERE
        target_type=p_target_type AND
        pack_target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE and
        target_type not in (MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, MGMT_GLOBAL.G_HOST_TARGET_TYPE,MGMT_GLOBAL.G_IAS_TARGET_TYPE);

    IF (l_member_count > 0) THEN
        FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
        LOOP
            FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
            LOOP
                p_target_guid := mgmt_target.get_target_guid
                         (
                             target_name_in=>p_valid_target_list_array(p_targets_counter),
                             target_type_in=>p_target_type
                         );

                p_is_standalone := is_target_standalone
                              (
                                      p_target_guid
                      );
                IF (p_is_standalone = TRUE) THEN
                    l_current_license_status:= em_license.is_target_pack_licensed
                                               (
                                                   p_target_guid=>p_target_guid,
                                                   p_pack_label=>p_valid_pack_list_array(p_packs_counter),
                                                   p_from_target_guid => p_target_guid
                                               );
                    IF (l_current_license_status='TRUE') THEN
                        DELETE
                            mgmt_licensed_targets
                        WHERE
                            from_target_guid = p_target_guid AND
                            pack_name   = p_valid_pack_list_array(p_packs_counter);
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                    ELSE
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Success - target '||p_valid_target_list_array(p_targets_counter)||' not licensed on pack '||p_valid_pack_list_array(p_packs_counter);
                    END IF;
                ELSE -- standalone false
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Cannot revoke license on '||p_valid_pack_list_array(p_packs_counter)||' from '||p_valid_target_list_array(p_targets_counter)||'. It is not a standalone target';
                    is_failure := 0;
                END IF;
            END LOOP;
        END LOOP;
    ELSE -- other than ias member targets
        FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
        LOOP
            FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
            LOOP
                BEGIN
                    em_license.revoke_license
                    (
                        p_target_type  => p_target_type,
                        p_target_name  => p_valid_target_list_array(p_targets_counter),
                        p_pack_label   => p_valid_pack_list_array(p_packs_counter)
                    );
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                EXCEPTION
                    WHEN OTHERS THEN
                        p_transctional_msg_array.extend;
                        p_txn_msg_counter := p_txn_msg_counter + 1;
                        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - processing '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter);
                        p_transctional_msg_array(p_txn_msg_counter) := p_transctional_msg_array(p_txn_msg_counter)||':'||sqlerrm;
                        is_failure := 0;
                END;
            END LOOP;
        END LOOP;
    END IF;
    COMMIT WORK;
    p_return_status := is_failure;
END revoke_license_with_validation;

PROCEDURE grant_license_no_validation
          (
              p_target_type            IN VARCHAR2,
              p_pack_list_array        IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_target_list_array      IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_transctional_msg_array OUT SMP_EMD_STRING_ARRAY,
              p_return_status          OUT NUMBER
          )
IS
    p_valid_target_list_array   SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_valid_pack_list_array     SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_target_guid               mgmt_targets.target_guid%type;
    l_count                     NUMBER DEFAULT 0;
    p_txn_msg_counter           NUMBER DEFAULT 0;
    p_txn_msg_array             SMP_EMD_STRING_ARRAY;

    l_current_license_status    VARCHAR2(10) DEFAULT 'FALSE';
	is_failure                  NUMBER DEFAULT 1;
BEGIN
    p_transctional_msg_array := SMP_EMD_STRING_ARRAY();
    p_return_status := 1;

    SELECT
        count(*)
    INTO
        l_count
    FROM
        mgmt_licensable_target_types
    WHERE
        target_type = p_target_type;

    IF (l_count =0 ) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid licensable target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;

    validate_pack_list
    (
        p_target_type,
        p_pack_list_array,
        p_valid_pack_list_array,
        p_txn_msg_array,
        p_return_status
    );
	IF (p_return_status = 0) THEN
	    is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Pack(s) of target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;

    validate_target_list
    (
        p_target_type             => p_target_type,
        p_target_list_array       => p_target_list_array,
        p_no_validate_flag        => 'no_validate',
        p_valid_target_list_array => p_valid_target_list_array,
        p_txn_msg_array           => p_txn_msg_array,
        p_return_status           => p_return_status
    );
    IF (p_return_status = 0) THEN
        is_failure := 0;
    END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Target(s) of target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;
    FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
    LOOP
        FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
        LOOP
            BEGIN
            p_target_guid := mgmt_target.get_target_guid
                         (
                     target_name_in=>p_valid_target_list_array(p_targets_counter),
                                      target_type_in=>p_target_type
                 );
                l_current_license_status:= em_license.is_target_pack_licensed
                                       (
                                           p_target_guid=>p_target_guid,
                                           p_pack_label=>p_valid_pack_list_array(p_packs_counter)
                                       );
                IF (l_current_license_status='FALSE') THEN
                    INSERT INTO
                        mgmt_licensed_targets
                        (
                            target_guid,
                            pack_name,
                            from_target_guid
                        )
                    VALUES
                        (
                            p_target_guid,
                            p_valid_pack_list_array(p_packs_counter),
                            p_target_guid
                        );
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                ELSE
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - target '||p_valid_target_list_array(p_targets_counter)||' already licensed on '||p_valid_pack_list_array(p_packs_counter);
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Failure - processing '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter);
                    p_transctional_msg_array(p_txn_msg_counter) := p_transctional_msg_array(p_txn_msg_counter)||':'||sqlerrm;
                    is_failure := 0;
            END;
        END LOOP;
    END LOOP;
    COMMIT WORK;
    p_return_status := is_failure;
END grant_license_no_validation;

PROCEDURE revoke_license_no_validation
          (
              p_target_type            IN VARCHAR2,
              p_pack_list_array        IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_target_list_array      IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
              p_transctional_msg_array OUT SMP_EMD_STRING_ARRAY,
              p_return_status          OUT NUMBER
          )
IS
    p_valid_target_list_array   SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_valid_pack_list_array     SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    p_target_guid               mgmt_targets.target_guid%type;
    l_count                     NUMBER DEFAULT 0;
    p_txn_msg_counter           NUMBER DEFAULT 0;
    p_txn_msg_array             SMP_EMD_STRING_ARRAY;
    l_current_license_status    VARCHAR2(10) DEFAULT 'FALSE';
	is_failure                  NUMBER DEFAULT 1;
BEGIN
    p_transctional_msg_array := SMP_EMD_STRING_ARRAY();
    p_return_status := 1;

    SELECT
        count(*)
    INTO
        l_count
    FROM
        mgmt_licensable_target_types
    WHERE
        target_type = p_target_type;

    IF (l_count =0 ) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid licensable target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;

    validate_pack_list
    (
        p_target_type,
        p_pack_list_array,
        p_valid_pack_list_array,
        p_txn_msg_array,
        p_return_status
    );
	IF (p_return_status = 0) THEN
	    is_failure := 0;
	END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_pack_list_array IS NULL OR p_valid_pack_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid Pack(s) of target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;

    validate_target_list
    (
        p_target_type             => p_target_type,
        p_target_list_array       => p_target_list_array,
        p_no_validate_flag        => 'no_validate',
        p_valid_target_list_array => p_valid_target_list_array,
        p_txn_msg_array           => p_txn_msg_array,
        p_return_status           => p_return_status
    );
    IF (p_return_status = 0) THEN
	    is_failure := 0;
    END IF;
    p_txn_msg_counter := p_txn_msg_counter + p_txn_msg_array.COUNT;
    p_transctional_msg_array.extend(p_txn_msg_array.COUNT);
    FOR i IN 1..p_txn_msg_array.COUNT
    LOOP
        p_txn_msg_counter := p_txn_msg_counter +1;
        p_transctional_msg_array.extend;
        p_transctional_msg_array(p_txn_msg_counter) := p_txn_msg_array(i);
    END LOOP;

    IF (p_valid_target_list_array IS NULL OR p_valid_target_list_array.COUNT = 0) THEN
        p_transctional_msg_array.extend;
        p_txn_msg_counter := p_txn_msg_counter + 1;
        p_transctional_msg_array(p_txn_msg_counter) := 'Failure - Invalid target(s) of target type '||p_target_type;
        p_return_status := 0;
        RETURN;
    END IF;
    FOR p_packs_counter IN 1..p_valid_pack_list_array.COUNT
    LOOP
        FOR p_targets_counter IN 1..p_valid_target_list_array.COUNT
        LOOP
            BEGIN
                p_target_guid := mgmt_target.get_target_guid
                         (
                     target_name_in=>p_valid_target_list_array(p_targets_counter),
                                      target_type_in=>p_target_type
                 );
                l_current_license_status:= em_license.is_target_pack_licensed
                                       (
                                           p_target_guid=>p_target_guid,
                                           p_pack_label=>p_valid_pack_list_array(p_packs_counter)
                                       );
                IF (l_current_license_status='TRUE') THEN
                    DELETE
                        mgmt_licensed_targets
                    WHERE
                        target_guid = p_target_guid AND
                        pack_name   = p_valid_pack_list_array(p_packs_counter);
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - Licensing for '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter)||' successfully processed';
                ELSE
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Success - target '||p_valid_target_list_array(p_targets_counter)||' not licensed on pack '||p_valid_pack_list_array(p_packs_counter);
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    p_transctional_msg_array.extend;
                    p_txn_msg_counter := p_txn_msg_counter + 1;
                    p_transctional_msg_array(p_txn_msg_counter) := 'Failure - processing '||p_valid_target_list_array(p_targets_counter)||' and '||p_valid_pack_list_array(p_packs_counter);
                    p_transctional_msg_array(p_txn_msg_counter) := p_transctional_msg_array(p_txn_msg_counter)||':'||sqlerrm;
                    is_failure := 0;
            END;
        END LOOP;
    END LOOP;
    COMMIT WORK;
    p_return_status := is_failure;
END revoke_license_no_validation;

END mgmt_license;
/
  

SHOW ERRORS;