Rem drv: <create type="pkgbodys" pos="util/util_pkgbodys.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/license/license_pkgbodys.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/18 02:29:02 bram Exp $
Rem
Rem license_pkgbodys.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      license_pkgbodys.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       bram     10/03/09 - Perf Fixes
Rem       sbhagwat 07/02/08 - Facilitate testing of license page in shiphome
Rem       ararora  09/14/07 - Increase pack description length
Rem       denath   06/24/07 - Backport denath_bug-6122114 from main
Rem       denath   06/11/07 - Bug fix 6122114.added HEXTORAW conversion to
Rem                           target_guids for performance.
Rem       paachary 05/18/07 - Restricting the query of
Rem                           get_pack_definition_details to display license
Rem                           info for only those target types present in the
Rem                           repository
Rem       bram     08/29/06 - Backport bram_bug-5490219 from main
Rem       yxie     07/20/06 - Backport yxie_forms_license_fix from main
Rem       acgopala 07/19/06 - Backport acgopala_bug5099434 from main 
Rem       bram     08/25/06 - Bug-5490219
Rem       bram     06/01/06 - asm licensing
Rem       bram     07/12/06 - Backport bram_bug-5147069 from main 
Rem       bram     05/25/06 - formsapp licensing
Rem       yxie     07/17/06 - XbranchMerge yxie_formsapp_license from main 
Rem       acgopala 07/11/06 - Backport acgopala_bug-5118309 from main 
Rem       acgopala 04/01/06 - Added procedure get_license_setup_data which will be used
Rem                           to featch target licensing information for Licensable targets as well as 
Rem                           All Targets(Licensable targets and all dependent targets) options
Rem       acgopala 04/05/06 - bug-5118309, modified grant_license procedure to support licensing of grantchildren targets
Rem       paachary 09/23/05 - Making Website licensable target
Rem       paachary 09/20/05 - grabtrans
Rem       paachary 09/16/05 - Code cleanup for get_pack_definition_details API
Rem       gsbhatia 07/01/05 - New repmgr header impl 
Rem       gsbhatia 06/26/05 - New repmgr header impl 
Rem       bram     05/20/05 - Bug 4359248 SQL Violation
Rem    schoudha    04/04/05 -modified query to fetch only licensable target types in procedure get_my_target_licenses 
Rem    sdantkal    03/03/05 - modified query to show only rac database records in popup page 
Rem    paachary    03/03/05 - Added 3 APIs (add_pack_definitions, add_licensable_target_types, add_packs) 
Rem                           to insert new records into mgmt_license_definitions, mgmt_licensable_target_types
Rem                           and mgmt_admin_license respectively. The API add_pack_definitions updates the mgmt_license_definitions table
Rem                           if a corresponding entry already exists.
Rem    bram        02/27/05 - To handle WildCard Search
Rem    pratagar    02/28/05 - 
Rem    sdantkal    02/16/05 - order by clause put for get_my_target_licenses 
Rem    pratagar    02/10/05 - Apply packs to target only when license state has
Rem                           changed
Rem    paachary    02/08/05 - Changed the package name from mgmt_license to em_license
Rem    gsbhatia    02/07/05 - updating repmgr header
Rem    bram        02/03/05 -
Rem    pratagar    01/30/05 - LicensingALL
Rem    bram        01/28/05 - DataDriven Funtionality
Rem    sdantkal    01/26/05 - DataDriven Funtionality
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    pratagar    01/17/05 - Bug id 4116482
Rem    pratagar    01/17/05 - Bug id 4116482 - Display both Databse abd Rac
Rem                           database in one screen
Rem    ktlaw       01/11/05 - add repmgr header
Rem    bram        12/30/04 - Rac Licensing Functionality
Rem    sdantkal    12/28/04 - DISTINCT packs selected in get_target_packs
Rem    vmotamar    12/23/04 -
Rem    schoudha    12/23/04 - Data Drive Auto License Functionality
Rem    paachary    12/20/04 - Optimization of code
Rem    paachary    12/16/04 - Modified for disabling host based licensing
Rem    paachary    12/13/04 - Disabling host based licensing
Rem    paachary    12/06/04 - WebApps Licensing (creation and deletion)
Rem    sdantkal    07/29/04 - standalone targets.
Rem    yaofeng     04/16/04 - yaofeng_bug-3548336
Rem    jpyang      03/19/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY em_license AS
-- Procedure
-- Purpose : To check whether a Target-Pack Combination is Licensed or Not
-- Input Parameters
-- p_target_guid : (optional) GUID of the Target to which the Pack License is granted
-- p_pack_label : Name of the Pack on which License is granted
-- OUTPUT Parameter
-- p_is_licensed : 'TRUE' if Licensed and 'FALSE' in case NOT
FUNCTION is_target_pack_licensed
(
    p_target_guid      IN VARCHAR2,
    p_pack_label       IN VARCHAR2,
    p_from_target_guid IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
IS
    l_result_string VARCHAR2(10);
    l_count         NUMBER;
    l_target_type   MGMT_TARGETS.TARGET_TYPE%TYPE DEFAULT NULL;
BEGIN
    l_result_string:='FALSE';

    FOR c1 IN
    (
        SELECT target_type
        FROM mgmt_targets
        WHERE target_guid=HEXTORAW(p_target_guid)
    )
    LOOP
        l_target_type:=c1.target_type;
    END LOOP;

    IF (l_target_type IS NULL) THEN
        l_result_string:='TRUE';
        RETURN l_result_string;
    END IF;

    -- Now validate the input target type and pack label combination
    SELECT  count(*)
    INTO    l_count
    FROM    mgmt_license_definitions
    WHERE   pack_label=p_pack_label   
        AND target_type IN 
            (
                SELECT distinct(pack_target_type)
                FROM mgmt_licensable_target_types
                WHERE target_type = l_target_type
            );
    IF (l_count = 0) THEN
        l_result_string:='TRUE';
        RETURN l_result_string;
    END IF;

    IF (p_from_target_guid IS NULL) THEN
        SELECT COUNT(*)
        INTO l_count
        FROM mgmt_licensed_targets
        WHERE
            target_guid=HEXTORAW(p_target_guid) AND
            pack_name=p_pack_label;
    ELSE
        SELECT COUNT(*)
        INTO l_count
        FROM mgmt_licensed_targets
        WHERE
            target_guid=HEXTORAW(p_target_guid) AND
            pack_name=p_pack_label AND
            from_target_guid = HEXTORAW(p_from_target_guid);
    END IF;
    IF (l_count>0) THEN
        l_result_string:='TRUE';
    END IF;
    RETURN(l_result_string);
END is_target_pack_licensed;
-- Private Procedure
-- Purpose : To propogate Licenses from a Target to WebApp and formsapp that uses them
--           Formsapp behave similar hence update_webapp_license_info() is modified to license formsapp
PROCEDURE update_webapp_license_info
(
    p_target_guid  IN VARCHAR2,
    p_pack_label   IN VARCHAR2
)
IS
    l_current_license_status VARCHAR2(10);
BEGIN
    -- First Check whether the input Target has a direct "depends-on" relationship with any Web App or formsapp
    FOR c1 IN
    (
        SELECT
            source_target_guid webapp_guid
        FROM
            mgmt_target_assocs, mgmt_targets
        WHERE
            mgmt_target_assocs.assoc_target_guid = HEXTORAW (p_target_guid) AND
            mgmt_target_assocs.assoc_guid=MGMT_ASSOC.G_DEPENDS_ON_GUID AND
            mgmt_target_assocs.source_target_guid=mgmt_targets.target_guid AND
            mgmt_targets.target_type IN(
                                          MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                                          MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE
                                        )
    )
    LOOP
        -- First Check whether the WebApp OR formsapp has License on this package
        l_current_license_status:= is_target_pack_licensed
                                      (
                                          p_target_guid=>c1.webapp_guid,
                                          p_pack_label=>p_pack_label,
                                          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
            (c1.webapp_guid, p_pack_label, p_target_guid);
        END IF;
    END LOOP;
    -- Next Check whether the input target is associated to a WebApp (INDIRECTLY) through a Redundancy Group (can be nested)
    FOR c1 IN
    (
        SELECT
            mgmt_flat_target_assoc.source_target_guid webapp_guid
        FROM
            mgmt_flat_target_assoc ,
            mgmt_targets
        WHERE
            mgmt_flat_target_assoc.assoc_target_guid = HEXTORAW(p_target_guid) AND
            mgmt_flat_target_assoc.is_membership=1 AND
            mgmt_flat_target_assoc.source_target_guid = mgmt_targets.target_guid AND
            mgmt_targets.target_type IN(
                                          MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                                          MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE
                                       )
    )
    LOOP
        -- First Check whether the WebApp has License on this package
        l_current_license_status:= is_target_pack_licensed
                                      (
                                          p_target_guid=>c1.webapp_guid,
                                          p_pack_label=>p_pack_label,
                                          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
            (c1.webapp_guid, p_pack_label, p_target_guid);
        END IF;
    END LOOP;
END update_webapp_license_info;
-- Procedure
-- Purpose : To grant License on a Target(and its associates)-package Combination
-- Input Parameters
-- p_target_type : Target Type to which the Current Target Belongs To (Possible Values : oracle_ias, oracle_database)
-- p_target_name : (optional) Name of the Target to which the Pack License is granted
-- p_target_guid : (optional) GUID of the Target to which the Pack License is granted
-- p_pack_label : Name of the Pack on which License is granted
PROCEDURE grant_license
(
    p_target_type IN VARCHAR2,
    p_target_name IN VARCHAR2 DEFAULT NULL,
    p_target_guid IN VARCHAR2 DEFAULT NULL,
    p_pack_label  IN VARCHAR2
)
IS
    l_count                   NUMBER DEFAULT 0;
    l_parent_target_guid      mgmt_targets.target_guid%type DEFAULT NULL;
    l_current_license_status  VARCHAR2(10);
    asm_cur                   cursorType;
    l_asm_guid                mgmt_targets.target_guid%type DEFAULT NULL;
    p_continue    VARCHAR2(10) := 'TRUE';
    l_webapp_pack_label       varchar2(30):='service_level_mgmt';

BEGIN
    IF ((p_target_name IS NULL) AND (p_target_guid is NULL)) THEN
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
                                'You need to specify either the Target Name or its GUID');
    END IF;
    -- Now validate the input target type and pack label combination
    SELECT
        count(*)
    INTO
        l_count
    FROM
        mgmt_license_definitions
    WHERE
        target_type=p_target_type AND
        pack_label=p_pack_label   AND
    target_type IN (
                        SELECT distinct(pack_target_type)
                    FROM mgmt_licensable_target_types
                    WHERE target_type = p_target_type
            );

    IF(l_count = 0) THEN
        raise_application_error(em_license.INVALID_TARGET_PACK_COMBI,
                                'Invalid Target Type, Pack Label Combination');
    END IF;
    IF (p_target_guid IS NULL) THEN -- Get the Target GUID, based on its Type and Name
        l_parent_target_guid:=mgmt_target.get_target_guid
                                  (
                                      target_name_in=>p_target_name,
                                      target_type_in=>p_target_type
                                  );
    ELSE
        -- Validate the input Target GUID
        -- Change to count('a') for performance as interested in existence only.
        SELECT count('a')
        INTO l_count
        FROM mgmt_targets
        WHERE
            target_type=p_target_type AND
            target_guid=HEXTORAW(p_target_guid);
        IF (l_count=0) THEN
           raise_application_error(mgmt_global.INVALID_TARGET_ERR, mgmt_global.INVALID_TARGET_ERR_M);
        ELSE
            l_parent_target_guid:=p_target_guid;
        END IF;
    END IF; -- if (p_target_guid IS NULL)

    IF (p_target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE) THEN
                     em_rac_license.grant_racdb_licenseinfo
                                                 (
                                                     p_target_guid=>l_parent_target_guid,
                                                     p_pack_name =>p_pack_label
                                                 ) ;

    ELSE
        -- Everything IS fine, grant license
        -- Get list of associate license types
        -- for IAS, it IS IAS, oc4j, oracle_apache, oracle_ias, oracle_jserv, oracle_ldap, host etc..
        -- For DB, it IS DB and Host
        FOR  c2 IN
        (
            SELECT target_type
            FROM mgmt_licensable_target_types
            WHERE
                pack_target_type=p_target_type 
        )
        LOOP
            -- Check if the licensable target type = website or formsapp and
            -- if pack_label = SLM, then grant it license if its already
            -- not licensed.
            IF ((c2.target_type = mgmt_global.g_website_target_type OR
                 c2.target_type = mgmt_global.g_formsapp_target_type)
                AND  p_pack_label =l_webapp_pack_label) THEN
                l_current_license_status:= EM_LICENSE.is_target_pack_licensed
                (
                    p_target_guid      =>l_parent_target_guid,
                    p_pack_label       =>p_pack_label,
                    p_from_target_guid =>l_parent_target_guid
                );
                IF (l_current_license_status ='FALSE') THEN
                    INSERT INTO mgmt_licensed_targets
                        (target_guid, pack_name, from_target_guid)
                    VALUES
                        (l_parent_target_guid,p_pack_label,l_parent_target_guid);
                    EXIT;
                END IF;
            END IF;
            IF (c2.target_type=p_target_type) THEN  -- We are licensing the parent, DB or IAS
                l_current_license_status:= is_target_pack_licensed
                                           (
                                                p_target_guid=>l_parent_target_guid,
                                                p_pack_label=>p_pack_label,
                                                p_from_target_guid => l_parent_target_guid
                                           );
                 IF (l_current_license_status='FALSE') THEN
                      -- When parent and association target types are the same, Target Guid and From_Target_GUID are same
                       INSERT INTO mgmt_licensed_targets
                       (target_guid, pack_name, from_target_guid)
                       VALUES
                       (l_parent_target_guid, p_pack_label, l_parent_target_guid);
                       -- In case of IAS, all Webapps or formsapp using it should get this license
                       -- IAS Dependent Types do not meet "(c2.target_type=p_target_type)" check and are handled later
                       IF (p_target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE) THEN
                           update_webapp_license_info
                               (
                                   p_target_guid => l_parent_target_guid,
                                   p_pack_label  => p_pack_label
                               );
                       END IF;
                 END IF;
        ELSE  -- all other associate target types exception self
            -- Get Target GUIDS of host, oc4j, oracle_jserv etc, which reside on the same host
            -- on which current IAS/DB resides
            FOR  c4 IN
            (
	     SELECT mgmt_target_assocs.assoc_target_guid member_target_guid, mgmt_targets.target_type member_target_type 
                FROM
                    mgmt_target_assocs, mgmt_targets
                WHERE
                    mgmt_target_assocs.assoc_target_guid=mgmt_targets.target_guid AND
                    mgmt_targets.target_type=c2.target_type AND
                    mgmt_target_assocs.source_target_guid=l_parent_target_guid
            )
            LOOP
                -- First Check whether the IAS Member (OC4J say) is licensed as a Standalone Target
                -- At Callback@Target Addition Stage, we do not know whether the OC4J being added is a Standalone
                -- or an IAS Member
                p_continue:='TRUE';
                IF (p_continue='TRUE') THEN
                    l_current_license_status:= is_target_pack_licensed
                                                  (
                                                      p_target_guid=>c4.member_target_guid,
                                                      p_pack_label=>p_pack_label,
                                                      p_from_target_guid =>c4.member_target_guid
                                                  );
                    IF (l_current_license_status='TRUE') THEN
                        BEGIN
                            UPDATE
                                mgmt_licensed_targets
                            SET
                                from_target_guid=l_parent_target_guid
                            WHERE
                                target_guid=c4.member_target_guid AND
                                pack_name=p_pack_label AND
                                from_target_guid=c4.member_target_guid;
                        EXCEPTION
                             WHEN DUP_VAL_ON_INDEX THEN
                             NULL;
                         END;
                    ELSE

                        -- Check whether the IAS Member is already licensed, by virtue of this IAS
                        l_current_license_status:= is_target_pack_licensed
                                                      (
                                                          p_target_guid=>c4.member_target_guid,
                                                          p_pack_label=>p_pack_label,
                                                          p_from_target_guid =>l_parent_target_guid
                                                      );
                        IF (l_current_license_status='FALSE') THEN
                            INSERT INTO mgmt_licensed_targets
                            (target_guid, pack_name, from_target_guid)
                            VALUES
                            (c4.member_target_guid, p_pack_label, l_parent_target_guid);
                            -- Incase the current target is a IAS Member Target Type (like OC4J, JSERV, No Host)
                            -- All WebApps and formsapp which use this, should get this information
                            SELECT
                                COUNT(*)
                            INTO
                                l_count
                            FROM
                                mgmt_licensable_target_types
                            WHERE
                                 pack_target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE AND
                                target_type NOT IN (
                                                      MGMT_GLOBAL.G_HOST_TARGET_TYPE,
                                                      MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                                                      MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE
                                                    ) AND
                                target_type=c2.target_type;
                            IF (l_count>0) THEN
                                update_webapp_license_info
                                (
                                    p_target_guid => c4.member_target_guid,--could be either webapps or formsapp
                                    p_pack_label  => p_pack_label --pack_label for webapps or formsapp is the same
                                );
                            END IF; -- IF (l_count>0)
                        END IF; -- IF (l_current_license_status='FALSE')
                    END IF;
                END IF;
		-- The 'CONNECT BY NOCYCLE' clause works in DB 10.2 onwards
		-- Bug 5490219 is about upgrade from 10.2.0.2 OMS (10202EM with  9206 RAC database) TO 10.2.0.3
		-- The NoCycle clause was not recognised
		-- Hence the following fix to get member targets of child licensable member targets
		-- IAS is the parent, OC4J is its child and OC4JVM is the member target of child licensable member target OC4J
		-- IAS->OC4J->OC4JJVM  (we need to data drive the logic)
		FOR C5 IN
		(
                     SELECT assoc_target_guid grand_child_target_guid -- returns grand children of parent(IAS) which is OC4JJVM for example
		     FROM 
		         mgmt_target_assocs, mgmt_targets
		     WHERE 
                         mgmt_target_assocs.source_target_guid=c4.member_target_guid AND 
			 mgmt_targets.target_guid=mgmt_target_assocs.assoc_target_guid AND
			 mgmt_targets.target_type IN 
			    (
                                SELECT target_type
				FROM mgmt_licensable_target_types
			    )                         
		)
		LOOP
                        l_current_license_status:= is_target_pack_licensed
                                                      (
                                                          p_target_guid=>c5.grand_child_target_guid,
                                                          p_pack_label=>p_pack_label
                                                      );
                        IF (l_current_license_status='FALSE') THEN
                            INSERT INTO mgmt_licensed_targets
                            (target_guid, pack_name, from_target_guid)
                            VALUES
                            (c5.grand_child_target_guid, p_pack_label, l_parent_target_guid);
			END IF;
		END LOOP; -- end C5
            END LOOP; --c4
        END IF;  --if (c2.target_type=p_target_type)
    END LOOP; --c2
  END IF;
END grant_license;
-- Procedure
-- Purpose : To revoke License on a Target(and its associates)-package Combination
-- Input Parameters
-- p_target_type : Target Type to which the Current Target Belongs To
-- p_target_name : (optional) Name of the Target from which the Pack License is to be revoked
-- p_target_guid : (optional) GUID of the Target from which the Pack License is to be revoked
-- p_pack_label : Name of the Pack on which License is to be revoked
PROCEDURE revoke_license
(
    p_target_type   IN VARCHAR2,
    p_target_name   IN VARCHAR2 DEFAULT NULL,
    p_target_guid   IN VARCHAR2 DEFAULT NULL,
    p_pack_label    IN VARCHAR2
)
IS
    l_parent_target_guid mgmt_targets.target_guid%type DEFAULT NULL;
    l_count number DEFAULT 0;
BEGIN
    IF ((p_target_name IS NULL) AND (p_target_guid IS NULL)) THEN
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
                                'You need to specify either the Target Name or its GUID');
    END IF;
    -- Now validate the Target Type and Pack label combination
    SELECT count(*)
    INTO l_count
    FROM mgmt_license_definitions
    WHERE target_type=p_target_type
    AND pack_label=p_pack_label
    AND target_type IN (
                        SELECT distinct(pack_target_type)
                    FROM mgmt_licensable_target_types
                    WHERE target_type = p_target_type
            );

    IF (l_count=0) THEN
        raise_application_error(em_license.INVALID_TARGET_PACK_COMBI,
                                'Invalid Target Type, Pack Label Combination');
    END IF;
    IF (p_target_guid IS NULL) THEN -- Get the Target GUID, based on its Type and Name
        l_parent_target_guid:=mgmt_target.get_target_guid
                                  (
                                      target_name_in=>p_target_name,
                                      target_type_in=>p_target_type
                                  );
        IF (l_parent_target_guid IS NULL) THEN
            raise_application_error(mgmt_global.INVALID_TARGETS_ERR,
                                'Target Does Not Exist');
        END IF;
    ELSE
        -- Validate the input Target GUID
        -- Change to count('a') for performance as interested in existence only.
        SELECT count('a')
        INTO l_count
        FROM mgmt_targets
        WHERE target_type=p_target_type
        AND target_guid=HEXTORAW(p_target_guid);
        IF (l_count=0) THEN
            raise_application_error(mgmt_global.INVALID_TARGETS_ERR,
                                'Target Does Not Exist');
        ELSE
            l_parent_target_guid:=p_target_guid;
        END IF;
    END IF; -- if (p_target_guid IS NULL)
    -- Everything IS fine, Revoke Access
    -- Delete Dependent Targets on Current Target being deleted, who use this pack
    IF (p_target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE) THEN
        --if rac_database type cluster entries need to be also removed hence the OR condition is added
        DELETE
            mgmt_licensed_targets
        WHERE
            pack_name=p_pack_label AND
            (
                from_target_guid=l_parent_target_guid
                OR
                from_target_guid IN
                    (
                        SELECT
                            assoc_target_guid
                        FROM
                            mgmt_flat_target_assoc mfta, mgmt_targets mt
                        WHERE
                            mfta.source_target_guid=l_parent_target_guid AND
                            mfta.is_membership=1 AND
                            mfta.assoc_target_guid = mt.target_guid AND
                            mt.target_type IN
                                (
                                   SELECT target_type
                                   FROM mgmt_licensable_target_types
                                )
                    )
                 OR
                target_guid IN
                   (
                        SELECT
                             mta1.source_target_guid
                        FROM
                             mgmt_flat_target_assoc rac,
                             mgmt_targets mt,
                             mgmt_target_assocs mta1,
                             mgmt_targets mt1
                       WHERE
                             rac.source_target_guid=l_parent_target_guid AND
                             rac.assoc_target_guid = mt.target_guid      AND
                             mt.target_type        =mgmt_global.G_HOST_TARGET_TYPE AND
                             mta1.assoc_target_guid=rac.ASSOC_TARGET_GUID  AND
                             mta1.assoc_guid=MGMT_ASSOC.G_CONTAINS_GUID      AND
                             mta1.source_target_guid=mt1.target_guid       AND
                             mt1.target_type=mgmt_global.G_cluster_target_TYPE
                  )
            );
    ELSE
        DELETE
             mgmt_licensed_targets
        WHERE
            pack_name=p_pack_label AND
            (
                from_target_guid=l_parent_target_guid
                OR
                from_target_guid IN
                    (
                        SELECT
                            assoc_target_guid
                        FROM
                            mgmt_flat_target_assoc mfta, mgmt_targets mt
                        WHERE
                            mfta.source_target_guid=l_parent_target_guid AND
                            mfta.is_membership=1 AND
                            mfta.assoc_target_guid = mt.target_guid AND
                            mt.target_type IN
                                (
                                   SELECT target_type
                                   FROM mgmt_licensable_target_types
                                )
                    )
            );
    END IF;
END revoke_license;

PROCEDURE get_packs_data
(
    pack_defs_cur_out        OUT cursorType
)
IS
BEGIN
-- cursor for packs definitions
  OPEN pack_defs_cur_out FOR
    SELECT distinct  pack_label pack_label,
           target_type,
           pack_display_label,
           pack_label_nlsid,
           rtrim(ltrim(pack_description)) pack_description,
           pack_description_nlsid,
           pack_abbr
    FROM mgmt_license_definitions;
END get_packs_data;

PROCEDURE get_licenses_data
(
    setup_details_cur_out    OUT cursorType,
    pack_defs_cur_out        OUT cursorType,
    licensable_types_cur_out OUT cursorType
)
IS
BEGIN
--  cursor for administrator setup details
  OPEN setup_details_cur_out FOR
    SELECT pack_name FROM mgmt_admin_licenses;
--  cursor for packs definitions
  OPEN pack_defs_cur_out FOR
    SELECT pack_label,
           target_type,
           pack_display_label,
           pack_label_nlsid,
           pack_description,
           pack_description_nlsid
      FROM mgmt_license_definitions;
--  cursor for licensable types
  OPEN licensable_types_cur_out FOR
    SELECT target_type, pack_target_type
      FROM mgmt_licensable_target_types;
END get_licenses_data;
PROCEDURE save_setup_data
(
    setup_details IN SMP_EMD_STRING_ARRAY
)
IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE mgmt_admin_licenses';
  IF setup_details IS NOT NULL THEN
    FOR i IN 1..setup_details.COUNT LOOP
        INSERT INTO mgmt_admin_licenses(pack_name) VALUES (setup_details(i));
    END LOOP;
  END IF;
END save_setup_data;
PROCEDURE get_target_packs
(
    licensable_types_cur_out OUT cursorType
)
IS
BEGIN
--  cursor for licensable types
--distinct was added since rac-database type has the same pack as that of
   -- oracle_database and this was resulting in duplicate rows in the UI hence distinct  was appended
    OPEN licensable_types_cur_out FOR
    SELECT distinct a.target_type, b.pack_label,b.pack_abbr
       FROM mgmt_licensable_target_types a, mgmt_license_definitions b
        WHERE a.pack_target_type = b.target_type
    ORDER BY a.target_type;
END get_target_packs;

PROCEDURE get_parent_target_packs
(
    licensable_types_cur_out OUT cursorType
)
IS
BEGIN
--  cursor for licensable types
--distinct was added since rac-database type has the same pack as that of
   -- oracle_database and this was resulting in duplicate rows in the UI hence distinct  was appended
    OPEN licensable_types_cur_out FOR
    SELECT distinct a.target_type, b.pack_label,b.pack_abbr
       FROM mgmt_target_types a, mgmt_license_definitions b
        WHERE a.target_type = b.target_type
    ORDER BY a.target_type;
END get_parent_target_packs;

PROCEDURE get_my_target_licenses
(
    p_license_info OUT cursorType
)
IS
    l_current_user VARCHAR2(30) := MGMT_USER.get_current_em_user();
BEGIN
--Counting on VPD to return all targets the Current User has atleast view upon
    OPEN p_license_info FOR
        SELECT
            distinct  a.target_name,
            c.target_type,
            b.pack_name
        FROM
            mgmt_targets a, 
            mgmt_licensed_targets b,
            mgmt_licensable_target_types c
        WHERE
            a.target_guid=b.target_guid(+) and
            a.target_type = c.target_type 
            order by c.target_type,a.target_name;
                        
end get_my_target_licenses;
PROCEDURE get_setup_data
(
      p_target_type  IN VARCHAR2,
      p_target_name  IN VARCHAR2,
      p_error_message     OUT VARCHAR2,
      p_setup_details_cur_out OUT cursorType
)
IS
    l_target_name  MGMT_TARGETS.target_name%TYPE;
BEGIN
    --Added to handle wildcard search
    SELECT 
           decode(instr(p_target_name,'*'),0,
           decode(instr(p_target_name,'%'),0,'%'||p_target_name||'%',p_target_name),
           REPLACE(p_target_name,'*','%'))
    INTO   l_target_name 
    FROM   dual;
    --handle escape characters 
    l_target_name:=replace(l_target_name,'_','^_');
       
--  cursor for administrator setup details for database target type
    IF((l_target_name IS NULL ) AND (p_target_type=mgmt_global.G_DATABASE_TARGET_TYPE)) THEN
    --changed to remove database target type who are rac members from
    --being displayed in the 'database'screen in the UI
    -- Following sql will All oracke_database insatnces and filter out
    -- rac_instances. All rac_database will also be selected.
        OPEN p_setup_details_cur_out FOR
            SELECT c.target_guid,
                   c.target_name,
                   c.host_name,
                   a.pack_name,
                   b.confirmation,
                   c.target_type
            FROM
                   mgmt_licensed_targets a,
                   mgmt_license_confirmation b,
                   mgmt_targets c
            WHERE
                   a.target_guid(+)=c.target_guid AND
                   b.target_guid(+)=c.target_guid AND
                   c.target_type=p_target_type    AND
                   c.target_guid IN (
                                     (SELECT
                                            mgmt_targets.target_guid
                                       FROM
                                            mgmt_targets
                                      WHERE
                                            (target_type = mgmt_global.G_DATABASE_TARGET_TYPE) AND
                                             target_name NOT IN (
                                                                    SELECT
                                                                         mt.target_name
                                                                     FROM
                                                                         mgmt_target_assocs, mgmt_targets mt,mgmt_targets mt1
                                                                    WHERE
                                                                          assoc_guid = MGMT_ASSOC.g_contains_guid AND
                                                                          mt.target_guid = assoc_target_guid    AND
                                                                          mt.target_type = mgmt_global.G_DATABASE_TARGET_TYPE AND
                                                                          mt1.target_guid=source_target_guid       AND
                                                                          mt1.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE
                                                                    )
                                            )
                                     )
            UNION ALL
        SELECT c.target_guid,
               c.target_name,
               c.host_name,
               a.pack_name,
               b.confirmation,
               c.target_type
           FROM
               mgmt_licensed_targets a,
               mgmt_license_confirmation b,
               mgmt_targets c
           WHERE
               a.target_guid(+)=c.target_guid AND
               b.target_guid(+)=c.target_guid AND
               c.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE;
    ELSIF(l_target_name IS NULL ) THEN
        OPEN p_setup_details_cur_out FOR
            SELECT c.target_guid, c.target_name,
                   c.host_name,
                    a.pack_name,
                    b.confirmation, c.target_type
            FROM
                   mgmt_licensed_targets a,
                   mgmt_license_confirmation b,
                   mgmt_targets c
            WHERE
                    a.target_guid(+)=c.target_guid AND
                   b.target_guid(+)=c.target_guid AND
                   c.target_type=p_target_type;
    END IF;
    -- Following sql will All oracke_database insatnces and filter out
    -- rac_instances. All rac_database will also be selected.
    IF(l_target_name IS NOT NULL AND (p_target_type=mgmt_global.G_DATABASE_TARGET_TYPE))
    THEN
        OPEN p_setup_details_cur_out FOR
        SELECT c.target_guid, c.target_name,
               c.host_name,a.pack_name,
               b.confirmation, c.target_type
        FROM
              mgmt_licensed_targets a,
              mgmt_license_confirmation b,
               mgmt_targets c
        WHERE
            a.target_guid(+)=c.target_guid  AND
            b.target_guid(+)=c.target_guid AND
            c.target_type=p_target_type AND
            upper(c.target_name) like upper(trim(l_target_name)) escape '^' and
            c.target_guid NOT IN (
            SELECT
                 mt.target_guid
            FROM
                 mgmt_target_assocs, mgmt_targets mt,mgmt_targets mt1
            WHERE
                 assoc_guid = MGMT_ASSOC.g_contains_guid AND
                 mt.target_guid = assoc_target_guid    AND
                 mt.target_type = mgmt_global.G_DATABASE_TARGET_TYPE AND
                 mt1.target_guid=source_target_guid       AND
                 mt1.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE)
        UNION
        SELECT c.target_guid,
               c.target_name,
               c.host_name,
               a.pack_name,
               b.confirmation,
               c.target_type
            FROM
               mgmt_licensed_targets a,
               mgmt_license_confirmation b,
               mgmt_targets c
            WHERE
               a.target_guid(+)=c.target_guid AND
               b.target_guid(+)=c.target_guid AND
           upper(c.target_name) like upper(trim(l_target_name)) escape '^' AND
               c.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE ;
        ELSIF(l_target_name IS NOT NULL) THEN
        OPEN p_setup_details_cur_out FOR
        SELECT c.target_guid,c.target_name,
               c.host_name,a.pack_name,
               b.confirmation,
           c.target_type
        FROM
              mgmt_licensed_targets a,
              mgmt_license_confirmation b,
              mgmt_targets c
        WHERE
            a.target_guid(+)=c.target_guid  AND
            b.target_guid(+)=c.target_guid AND
            c.target_type=p_target_type AND
            upper(c.target_name) like upper(trim(l_target_name)) escape '^' and
            c.target_guid NOT IN (
            SELECT
                 mt.target_guid
            FROM
                 mgmt_target_assocs, mgmt_targets mt,mgmt_targets mt1
            WHERE
                 assoc_guid = MGMT_ASSOC.g_contains_guid AND
                 mt.target_guid = assoc_target_guid    AND
                 mt.target_type = mgmt_global.G_DATABASE_TARGET_TYPE AND
                 mt1.target_guid=source_target_guid       AND
                 mt1.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE);
    END IF;
END get_setup_data;
PROCEDURE apply_packs_to_target
(
    p_target_array       IN  MGMT_TARGET_LICENSE_ARRAY,
    p_error_message      OUT VARCHAR2_TABLE,
    p_commit_indicator   OUT VARCHAR2
)
IS
    l_target_guid      mgmt_targets.target_guid%TYPE;
    l_target_name      mgmt_targets.target_name%TYPE;
    l_target_type      mgmt_targets.target_type%TYPE;
    l_target_host_name mgmt_targets.host_name%TYPE;
    l_pack_name        mgmt_licensed_targets.pack_name%type;
    l_current_user     mgmt_priv_grants.grantee%TYPE := MGMT_USER.get_current_em_user();
    l_current_license_status VARCHAR2(10);
    l_error_count   NUMBER DEFAULT 0;
    l_count         NUMBER;
BEGIN
    p_error_message:=varchar2_table();
    p_commit_indicator := 'N';
    -- As per the new model (Disabling Host Based Licensing), if one target T1 on a host H1 is granted package P1
    -- then, the license propogates to the host on which is it resides and
    -- does not propogate to the other targets (siblings) hosted on the host H1.
    -- For example, if databases D1 and D2 reside on host H1 and package P1 IS granted license on D1
    -- In such a case, license on package P1 IS granted to only D1 and not to D2
    -- So check whether the current target IS already licensed for the current package
    -- In case license on package P1 is revoked from D1, licensed is revoked
    -- from the orresponding host H1.
    -- This has no bearing on the other sibling targets which reside on the same host.
    -- The current set of Targets Received may be a subset of Targets of this Type on Host
    -- License Admin Screen, Use Search, Modify License Info for Search Results, Apply
    -- Hence need to process target by target,
    -- without disturbing license information for targets, not included in input array
    IF (p_target_array IS NOT NULL) AND (p_target_array.count>0) THEN
        FOR i in 1..p_target_array.count
        LOOP
            l_target_name := p_target_array(i).target_name;
            l_target_type := p_target_array(i).target_type;
            l_target_guid:=mgmt_target.get_target_guid
                                      (
                                          target_name_in=>l_target_name,
                                          target_type_in=>l_target_type
                                      );
            -- Proceed if valid target
            IF (l_target_guid IS NOT NULL) THEN
                -- First Process Agree Access (always goes from NO  to YES, never in the opposite direction)
                IF (p_target_array(i).agree_acccess=1) THEN
                    SELECT COUNT(*)
                    INTO l_count
                    FROM mgmt_license_confirmation
                    WHERE
                        target_guid=l_target_guid AND
                        confirmation='Y';
                    IF (l_count=0) THEN
                        INSERT INTO mgmt_license_confirmation
                        (target_guid, confirmation, confirmed_by, confirmed_time)
                        VALUES
                        (l_target_guid, 'Y', l_current_user, SYSDATE);
			p_commit_indicator := 'Y';
                    END IF;
                END IF;
                -- Finish processing Agree Access for the Target
                -- Now Process Pack License Info
                IF (p_target_array(i).pack_license_info IS NOT NULL) AND (p_target_array(i).pack_license_info.count>0) THEN
                    FOR j IN 1..p_target_array(i).pack_license_info.count
                    LOOP
                        l_pack_name := p_target_array(i).pack_license_info(j).pack_name;
                        IF (p_target_array(i).pack_license_info(j).is_licensed=1) THEN
                            -- This check is added to ensure that grant_license is called only if
                            -- Target and Pack combination is not licensed.
                            l_current_license_status:= is_target_pack_licensed
                                                      (
                                                          p_target_guid=>l_target_guid,
                                                          p_pack_label=>l_pack_name,
                                                          p_from_target_guid =>l_target_guid
                                                      );
                            IF (l_current_license_status='FALSE') THEN
                                BEGIN
                                    grant_license
                                        (
                                            p_target_type=>l_target_type,
                                            p_target_name=>l_target_name,
                                            p_pack_label=>l_pack_name
                                        );
                                    p_commit_indicator := 'Y';
                                    EXCEPTION -- HANDLE EXCEPTIONS "grant_license" RAISES AND MOVE TO NEXT TARGET-PACKAGE COMBINATION
                                       WHEN OTHERS THEN
                                           l_error_count:=l_error_count+1;
                                           p_error_message(l_error_count):='Error while Granting License to Target "'||p_target_array(i).target_name||'" on pack '||l_pack_name;
                                END;
                            END IF;
                        ELSIF (p_target_array(i).pack_license_info(j).is_licensed=0) THEN
                            -- Else would have worked as well.  Using ELSIF to be doubly sure
                            -- No License on Target-Pack Combination
                            -- Check if Target-package Combination is already licensed,
                            -- If Yes, Revoke
                            l_current_license_status:= is_target_pack_licensed
                                                          (
                                                              p_target_guid=>l_target_guid,
                                                              p_pack_label=>l_pack_name,
                                                              p_from_target_guid =>l_target_guid
                                                          );
                            IF (l_current_license_status='TRUE') THEN
                                BEGIN
                                    revoke_license
                                        (
                                            p_target_type=>l_target_type,
                                            p_target_name=>l_target_name,
                                            p_pack_label=>l_pack_name
                                        );
                                    p_commit_indicator := 'Y';
                                    EXCEPTION -- HANDLE EXCEPTIONS "revoke_license" RAISES AND MOVE TO NEXT TARGET-PACKAGE COMBINATION
                                       WHEN OTHERS THEN
                                           l_error_count:=l_error_count+1;
                                           p_error_message(l_error_count):='Error while Revoking License from Target "'||p_target_array(i).target_name||'" on pack '||l_pack_name;
                                END;
                            END IF; -- (l_current_license_status='TRUE')
                        END IF; -- IF (p_target_array(i).pack_license_info(j).is_licensed=1)
                    END LOOP; -- j (p_target_array(i).pack_license_info.count)
                END IF;
                -- END Process Pack License Info
            END IF; -- IF (l_target_guid IS NOT NULL)
        END LOOP; -- i (p_target_array.count)
    END IF; -- (p_target_array.count>0),
END apply_packs_to_target;
-- Fetch licenses for the given target
PROCEDURE get_licenses_for_target
(
    p_target_type IN VARCHAR,
    p_target_name IN VARCHAR,
    p_license_info OUT cursorType
)
IS
BEGIN
    OPEN p_license_info FOR
        SELECT
            a.target_name,
            a.target_type,
            b.pack_name
        FROM
            mgmt_targets a, mgmt_licensed_targets b
        WHERE
            a.target_guid(+)=b.target_guid AND
            a.target_type=p_target_type  AND
            a.target_name=p_target_name;
end get_licenses_for_target;
PROCEDURE auto_license_target
(
   p_target_name VARCHAR2,
   p_target_type VARCHAR2,
   p_target_guid RAW
)
IS
    l_count                  NUMBER;
    l_current_user           mgmt_priv_grants.grantee%type := MGMT_USER.get_current_em_user();
    l_host_name              mgmt_targets.host_name%type DEFAULT NULL;
    l_current_license_status VARCHAR2(10);
    l_assoc_count            NUMBER DEFAULT 0;
BEGIN
    SELECT
        COUNT(*)
    INTO
        l_count
    FROM
     mgmt_licensable_target_types
    WHERE
        p_target_type IN
            (
            SELECT
                DISTINCT(pack_target_type)
        FROM
            mgmt_licensable_target_types
        );
    IF (l_count>0) THEN  -- Parent Licensable Target Types (DB, IAS, RAC etc..)  Auto License on all applicable packs
        FOR  c1 IN
        (
            SELECT
                pack_label
            FROM
                mgmt_license_definitions
            WHERE
                target_type=p_target_type
        )
        LOOP
        -- Grant License for Current Target and its Associates for each applicable pack
            grant_license
            (
                p_target_type => p_target_type,
                p_target_guid => p_target_guid,
                p_pack_label  => c1.pack_label
            );
        END LOOP;
    END IF;
                -- Standalone targets, belonging to IAS Dependent Target Types, should be licensed for all IAS packs by default
                -- First Check whether the newly added target belongs to a IAS Dependent Target Type
                -- Targets of type "Web Application (Website)and formsapp(Forms Applications) are handled indepedently
                -- by writing Callbacks om Service-System and System-Component associations
                -- Hosts are not licensed independently. They will get licensed either due to DB or iAS
                SELECT
                    COUNT(*)
                INTO
                    l_count
                FROM
                    mgmt_licensable_target_types
               WHERE
                    pack_target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE AND
                    target_type not in
                    (MGMT_GLOBAL.G_HOST_TARGET_TYPE,
                     MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                     MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE) AND
                    target_type=p_target_type;
             IF (l_count>0) THEN  -- the current target belongs to IAS Dependent Target Type
                 FOR c1 IN
                    (
                       SELECT
                             pack_label
                         FROM
                             mgmt_license_definitions
                        WHERE
                             target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                    )
                  LOOP
                        l_current_license_status:= is_target_pack_licensed
                                                 (
                                                     p_target_guid=>p_target_guid,
                                                     p_pack_label=>c1.pack_label,
                                                     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, c1.pack_label, p_target_guid);
                        END IF;
                  END LOOP;  -- end C1
              END IF; -- IF (l_count>0) THEN  -- the current target belongs to IAS Dependent Target Type
END auto_license_target;
PROCEDURE auto_revoke_license_info
(
   p_target_name VARCHAR2,
   p_target_type VARCHAR2,
   p_target_guid RAW
)
IS
    l_count NUMBER;
    l_rac_member          BOOLEAN:=FALSE;
    l_cluster_node_guid   mgmt_targets.target_guid%TYPE DEFAULT NULL;
    l_cluster_guid        mgmt_targets.target_guid%TYPE DEFAULT NULL;
BEGIN
    l_rac_member:=em_rac_license.is_racmember
                  (
                      p_target_type =>p_target_type,
                      p_target_guid =>p_target_guid
                  );
    IF (l_rac_member=TRUE) THEN

        IF (p_target_type=mgmt_global.G_DATABASE_TARGET_TYPE) THEN

            -- delete info about asm and cluster nodes associated with that instance
            DELETE
                  mgmt_licensed_targets
            WHERE
                 from_target_guid=p_target_guid;
            -- delete info about self
            DELETE
                 mgmt_licensed_targets
            WHERE
                 target_guid=p_target_guid;
                    --get cluster node
                  SELECT
                        target_guid
                    INTO
                       l_cluster_node_guid
                    FROM
                        mgmt_targets
                   WHERE
                        target_name IN
                        (
                          SELECT
                                host_name
                            FROM
                                mgmt_targets
                           WHERE
                                target_guid = p_target_guid
                       );
                         -- There may be multiple instances on the same cluster node.
                         -- So, deleting one rac instance should not remove the license from the
                         -- cluster due to cluster node.
                         -- So, first check if there are other instances which are on the same node.
                       SELECT
                            count(*)
                         INTO
                            l_count
                         FROM
                            mgmt_licensed_targets
                        WHERE
                            target_guid = l_cluster_node_guid;

                        IF (l_count = 0 ) THEN
                            --get cluster guid
                           l_cluster_guid:=em_rac_license.get_cluster_guid
                                  (
                                      p_cluster_node_guid=>l_cluster_node_guid,
                                      p_target_type       =>mgmt_global.G_CLUSTER_TARGET_TYPE
                                  );

                            -- delete clusterNode and cluster lincense combination
                            DELETE
                                mgmt_licensed_targets
                            WHERE
                                from_target_guid=l_cluster_node_guid AND
                                target_guid=l_cluster_guid;
                        END IF;
        ELSIF (p_target_type=mgmt_global.G_HOST_TARGET_TYPE) THEN

            -- When the cluster_node(host) is deleted ,then the following rows will be
                -- delete for cluster
                DELETE
                     mgmt_licensed_targets
                WHERE
                     from_target_guid=p_target_guid;

                -- delete for self and rac instance
                DELETE
                     mgmt_licensed_targets
                WHERE
                    target_guid=p_target_guid;

        ELSIF (p_target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE) THEN
            DELETE
                mgmt_licensed_targets
            WHERE
                from_target_guid=p_target_guid
                OR
                from_target_guid IN
                   (
                        SELECT
                            assoc_target_guid
                        FROM
                            mgmt_flat_target_assoc mfta, mgmt_targets mt
                        WHERE
                            mfta.source_target_guid=p_target_guid AND
                            mfta.is_membership=1                  AND
                            mfta.assoc_target_guid = mt.target_guid AND
                            mt.target_type IN
                               (
                                   SELECT target_type
                                   FROM mgmt_licensable_target_types
                               )
                    )
                OR
                from_target_guid IN
                    (
                        SELECT
                            assoc_target_guid
                        FROM
                            mgmt_flat_target_assoc mfta, mgmt_targets mt
                        WHERE
                            mfta.source_target_guid=p_target_guid   AND
                            mfta.assoc_target_guid = mt.target_guid AND
                            mt.target_type     =mgmt_global.G_HOST_TARGET_TYPE
                    );
        END IF;--IF (p_target_type=mgmt_global.G_DATABASE_TARGET_TYPE)

    ELSIF (l_rac_member=FALSE) THEN

       DELETE
            mgmt_licensed_targets
        WHERE
            target_guid=p_target_guid OR
            from_target_guid=p_target_guid OR
            from_target_guid IN
                (
                    SELECT
                        assoc_target_guid
                    FROM
                        mgmt_flat_target_assoc mfta, mgmt_targets mt
                    WHERE
                        mfta.source_target_guid=p_target_guid AND
                        mfta.is_membership=1 AND
                        mfta.assoc_target_guid = mt.target_guid AND
                        mt.target_type IN
                            (
                               SELECT target_type
                               FROM mgmt_licensable_target_types
                            )
                );
    END IF;--(l_rac_member=FALSE)
END auto_revoke_license_info;

PROCEDURE check_packs_for_target
(
    p_target_type       IN  VARCHAR2,
    p_target_name       IN  VARCHAR2,
    p_pack_array        IN  VARCHAR2_TABLE,
    p_is_licensed       OUT VARCHAR2
)
IS
    l_count        NUMBER;
    l_target_guid  mgmt_targets.target_guid%TYPE := NULL;
BEGIN
    p_is_licensed:='FALSE';
    FOR c1 IN
    (
        SELECT target_guid
        FROM mgmt_targets
        WHERE
            target_type=p_target_type AND
            target_name=p_target_name
    )
    LOOP
        l_target_guid:=c1.target_guid;
    END LOOP;
    IF (l_target_guid IS NULL) THEN -- Invalid Target, Return FALSE (initial value)
        RETURN;
    ELSE  -- Continue with License Check
        p_is_licensed:='TRUE';
    END IF;
    -- If p_pack_array is NULL, 'FALSE' will be returned
    IF (p_pack_array IS NOT NULL) AND (p_pack_array.count>0) THEN
        FOR i IN 1..p_pack_array.count
        LOOP
            SELECT COUNT(*)
            INTO l_count
            FROM MGMT_LICENSED_TARGETS
            WHERE
                TARGET_GUID = l_target_guid AND
                PACK_NAME = p_pack_array(i);
            IF (l_count=0) THEN
                p_is_licensed :='FALSE';
                RETURN;
            END IF;
        END LOOP;
    END IF;
END check_packs_for_target;
-- PROCEDURE:formapp_license_assco_create
-- Purpose :Callback when formapp association is created
PROCEDURE formapp_license_assoc_create
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS 
BEGIN
--Presently formapp_license_assoc_delete is a wrapper to webapp_license_assoc_create
--If any business logic changes with respect to formapp it needs to be inccluded here
webapp_license_assoc_create
(
    p_assoc_def_name,
    p_source_target_name,
    p_source_target_type,
    p_assoc_target_name,
    p_assoc_target_type,
    p_scope_target_name,
    p_scope_target_type
);
END formapp_license_assoc_create;
-- PROCEDURE webapp_license_assoc_create
-- Purpose : When a association is created between a WebApp/formsapp and IAS Dependent Type Standalone Targets or Redundancy Group
PROCEDURE webapp_license_assoc_create
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,--webapps or formsapp target name
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS
    l_webapp_guid             mgmt_targets.target_guid%type;
    l_component_target_guid   mgmt_targets.target_guid%type;
    l_from_target_guid        mgmt_targets.target_guid%type;
    l_current_license_status  VARCHAR2(10);
    l_count                   NUMBER;
    l_continue                BOOLEAN;
    l_is_standalone_target    BOOLEAN;
BEGIN
    -- WebApp Derives License Info from IAS/AS Dependent Type Standalone Targets or Redundancy Group
    -- So in case, the target type of the new key component target does not meet the above criterion, return
    l_continue:=FALSE;
    IF (p_assoc_target_type = MGMT_GLOBAL.G_REDUNDANCY_GROUP_TARGET_TYPE) THEN
       l_continue:=TRUE;
    ELSE
        SELECT
            COUNT(*)
        INTO
            l_count
        FROM
            DUAL
        WHERE
            p_assoc_target_type IN
                (
                    SELECT target_type
                    FROM mgmt_licensable_target_types
                    WHERE PACK_TARGET_TYPE=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                );
        IF (l_count>0) THEN
            l_continue:=TRUE;
        END IF;
    END IF;
    IF (l_continue=FALSE) THEN
        RETURN;
    END IF;
    --  Now get the GUID of WebApp/formsapp and new key component target to aid further processing
    IF (p_source_target_type IN (
                                     MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                                     MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE
                                ))    THEN
                                
        l_webapp_guid := mgmt_target.get_target_guid
                               (
                                   target_type_in =>p_source_target_type,
                                   target_name_in=> p_source_target_name
                               );
    END IF;
       --l_webapp_guid can contain the GUID of either Webapps or Formsapp
       --The subsequent licensing logic is the Same for Webapps and Formsapp
       --Since the behaviour of both are same
       l_component_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_assoc_target_type,
                                         target_name_in=> p_assoc_target_name
                                     );
    IF ((l_webapp_guid IS NULL) OR (l_component_target_guid IS NULL)) THEN
        RETURN;
    END IF;
    -- Callback suppresses all exceptions.  So do not raise them
    -- If Stand Alone Target, the above "ELSE" block confirms that it is a IAS/IAS Dependent Type
    -- If Redundancy Group, check whether it contains flat target(s) of type IAS/IAS Dependent Type
    -- In case not, return
    IF (p_assoc_target_type = MGMT_GLOBAL.G_REDUNDANCY_GROUP_TARGET_TYPE) THEN
        SELECT
            count(assoc_target_guid)
        INTO
            l_count
        FROM
            mgmt_flat_target_assoc ,
            mgmt_targets
        WHERE
            mgmt_flat_target_assoc.source_target_guid = l_component_target_guid AND
            mgmt_flat_target_assoc.assoc_target_guid = mgmt_targets.target_guid AND
            mgmt_flat_target_assoc.is_membership=1 AND
            mgmt_targets.target_type IN
                            (
                                SELECT target_type
                                FROM mgmt_licensable_target_types
                                WHERE PACK_TARGET_TYPE=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                            );
           IF (l_count=0) THEN  -- The Red_Group does not contain member targets of type IAS/IAS Dependent Type
               RETURN;
           END IF;
    END IF;
    -- Do not do anything if WebApp is already for all IAS Related packs
    -- Adding a New Component will not alter the licensing info for WebApp in such a case
    l_continue:=FALSE;
    FOR c1 IN
    (
        SELECT
            pack_label
        FROM
            mgmt_license_definitions
        WHERE
            target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE
    )
    LOOP
        l_current_license_status:= is_target_pack_licensed
                                  (
                                      p_target_guid=>l_webapp_guid,
                                      p_pack_label=>c1.pack_label,
                                      p_from_target_guid=>l_component_target_guid
                                  );
        IF (l_current_license_status='FALSE') THEN
            l_continue:=TRUE;
            EXIT;
        END IF;
    END LOOP;  --END C1
    IF (l_continue=FALSE) THEN
        RETURN;
    END IF;
    -- Now Focus on Packs the WebApp does not have License ON
    -- Check whether adding the new target, gives WebApp License on these missing packs
    FOR c1 IN
    (
        SELECT
            pack_label
        FROM
            mgmt_license_definitions
        WHERE
            target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE AND
            pack_label NOT IN
                (
                    SELECT pack_name
                    FROM mgmt_licensed_targets
                    WHERE target_guid=l_webapp_guid
                )
    )
    LOOP
        -- If Standalone Target, check whether this has the current package (WebApp not licensed for), Licensed
        -- If Redundancy Group, check whether it contains amember this has the current pack Licensed
        -- In either case, if Yes, propogate the License to WebApp
        l_current_license_status:='FALSE';
        IF (p_assoc_target_type !=MGMT_GLOBAL.G_REDUNDANCY_GROUP_TARGET_TYPE) THEN -- STANDALONE TARGET
            l_current_license_status:= is_target_pack_licensed
                                          (
                                              p_target_guid=>l_component_target_guid,
                                              p_pack_label=>c1.pack_label
                                          );
            l_from_target_guid := l_component_target_guid;
        ELSE  -- REDUNDNACY GROUP
            FOR c2 IN
            (
                SELECT
                    assoc_target_guid  -- Get Flat Target List of Type IAS/IAS DEPENDENT TYPE
                FROM
                    mgmt_flat_target_assoc ,
                    mgmt_targets
                WHERE
                    mgmt_flat_target_assoc.source_target_guid = l_component_target_guid AND
                    mgmt_flat_target_assoc.assoc_target_guid = mgmt_targets.target_guid AND
                    mgmt_flat_target_assoc.is_membership=1 AND
                    mgmt_targets.target_type IN
                                    (
                                        SELECT target_type
                                        FROM mgmt_licensable_target_types
                                        WHERE PACK_TARGET_TYPE=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                                    )
            )
            LOOP
                l_current_license_status:= is_target_pack_licensed
                                              (
                                                  p_target_guid=>c2.assoc_target_guid,
                                                  p_pack_label=>c1.pack_label
                                              );
                IF (l_current_license_status='TRUE') THEN -- No need to check further target
                   l_from_target_guid := c2.assoc_target_guid;
                   EXIT;
                END IF;
            END LOOP;  -- END C2
        END IF;
        IF (l_current_license_status='TRUE') THEN
            -- Component Target has License on this pack, the WebApp is not Licensed on
            -- Propogate the License from the Component Target to WebApp
            INSERT INTO mgmt_licensed_targets
            (target_guid, pack_name, from_target_guid)
            VALUES
            (l_webapp_guid, c1.pack_label, l_from_target_guid);
        END IF;
    END LOOP;-- end c1
END webapp_license_assoc_create;
PROCEDURE formapp_license_assoc_delete
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS
BEGIN
--Presently formapp_license_assoc_delete is a wrapper to webapp_license_assoc_delete
--If any business logic changes with respect to formapp it needs to be inccluded here
webapp_license_assoc_delete
(
    p_assoc_def_name,
    p_source_target_name,
    p_source_target_type,
    p_assoc_target_name,
    p_assoc_target_type,
    p_scope_target_name,
    p_scope_target_type
);
END formapp_license_assoc_delete;
PROCEDURE webapp_license_assoc_delete
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS
    l_webapp_guid             mgmt_targets.target_guid%type;
    l_component_target_guid   mgmt_targets.target_guid%type;
    l_from_target_guid        mgmt_targets.target_guid%type;
    l_count                   NUMBER;
    l_current_license_status VARCHAR2(10);
BEGIN
    -- First Get the GUID of the WebApp/formsapp being processed
    IF (p_source_target_type IN (
                                     MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE,
                                     MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE
                                ))    THEN
        l_webapp_guid := mgmt_target.get_target_guid
                               (
                                   target_type_in =>p_source_target_type,
                                   target_name_in=> p_source_target_name
                               );
    END IF;
       --l_webapp_guid could contain GUID of either Webapps or Formsapp
       l_component_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_assoc_target_type,
                                         target_name_in=> p_assoc_target_name
                                     );
    IF ((l_webapp_guid IS NULL) OR (l_component_target_guid IS NULL)) THEN
        RETURN;
    END IF;
    -- Callback suppresses all exceptions.  So do not raise them
    -- Check whether the key component target being added is licensed for any pack at all
    -- (This also implies check whether WebApp has a License at all)
    --  In case not, the license info associated with WebApp will not change
    -- In case yes, in case the current target is the only key component having a package licensed
    -- the WebApp will lose the pack license
    -- The above approach sounds right, but is more expensive to implement as the sibling component targets can
    -- be Standalone/Redundancy Group and you will have to drill down across them to check whether a sibling has \
    -- the package (the deleted target takes away) licensed or not
    -- Instead Recalculate WebApp package Info, using an efficient algorithm
    
    -- Now webapp is also a licensable target type.
    -- So, we cannot directly delete entries for webapp from 
    -- the licensed table.
    -- So, delete only those entries for packs 
    -- which belong to iAS.
    DELETE FROM mgmt_licensed_targets
    WHERE target_guid=l_webapp_guid
      AND pack_name IN
        ( SELECT pack_label
          FROM   mgmt_license_definitions
          WHERE  target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE);
    
    -- Go by packs that can be licensed
    -- Exit from loop each time a license for the current pack is obtained
    FOR c1 IN
    (
        SELECT
            pack_label
        FROM
            mgmt_license_definitions
        WHERE
            target_type=MGMT_GLOBAL.G_IAS_TARGET_TYPE
    )
    LOOP
        l_current_license_status:='FALSE';
        -- Navigate across sibling key component list
        -- Fccus on Tragets of Type IAS/IAS Dependent Type of Redundancy Group
        FOR c2 IN
        (
            SELECT
                child.target_guid,
                child.target_name,
                child.target_type
            FROM
                mgmt_targets child,  mgmt_target_assocs
            WHERE
                mgmt_target_assocs.source_target_guid=l_webapp_guid AND
                mgmt_target_assocs.assoc_target_guid!=l_component_target_guid AND -- leave out current among children to get siblings
                child.target_guid=mgmt_target_assocs.assoc_target_guid AND
                mgmt_target_assocs.assoc_guid=MGMT_ASSOC.G_DEPENDS_ON_GUID AND
                (
                    child.target_type = MGMT_GLOBAL.G_REDUNDANCY_GROUP_TARGET_TYPE
                    OR
                    child.target_type IN
                        (
                            SELECT target_type
                            FROM mgmt_licensable_target_types
                            WHERE PACK_TARGET_TYPE=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                        )
                )
        )
        LOOP
            IF (c2.target_type !=MGMT_GLOBAL.G_REDUNDANCY_GROUP_TARGET_TYPE) THEN -- STANDALONE TARGET
                l_current_license_status:= is_target_pack_licensed
                                              (
                                                  p_target_guid=>c2.target_guid,
                                                  p_pack_label=>c1.pack_label
                                              );
                l_from_target_guid:= c2.target_guid;
                -- Will be used to insert into "mgmt_licensed_targets" if "l_current_license_status"=TRUE
            ELSE  -- REDUNDNACY GROUP
                FOR c3 IN
                (
                    SELECT
                        assoc_target_guid  -- Get Flat Target List of Type IAS/IAS DEPENDENT TYPE
                    FROM
                        mgmt_flat_target_assoc ,
                        mgmt_targets
                    WHERE
                        mgmt_flat_target_assoc.source_target_guid = c2.target_guid AND
                        mgmt_flat_target_assoc.assoc_target_guid = mgmt_targets.target_guid AND
                        mgmt_flat_target_assoc.is_membership=1 AND
                        mgmt_targets.target_type IN
                                        (
                                            SELECT target_type
                                            FROM mgmt_licensable_target_types
                                            WHERE PACK_TARGET_TYPE=MGMT_GLOBAL.G_IAS_TARGET_TYPE
                                        )
                )
                LOOP
                    l_current_license_status:= is_target_pack_licensed
                                                  (
                                                      p_target_guid=>c3.assoc_target_guid,
                                                      p_pack_label=>c1.pack_label
                                                  );
                    IF (l_current_license_status='TRUE') THEN  -- No need to check further target
                       l_from_target_guid:=c3.assoc_target_guid;
                       EXIT;
                    END IF;
                END LOOP;  -- END C3
            END IF;
            IF (l_current_license_status='TRUE') THEN
                -- Component Target has License on this pack, the WebApp is not Licensed on
                -- Propogate the License from the Component Target to WebApp
                INSERT INTO mgmt_licensed_targets
                (target_guid, pack_name, from_target_guid)
                VALUES
                (l_webapp_guid, c1.pack_label, l_from_target_guid);
            END IF;
        END LOOP; -- END C2
    END LOOP;  --END C1
END webapp_license_assoc_delete;
-- PROCEDURE asm_license_assoc_create
-- Purpose : This is a Callback invoked when an existing relationship between an ASM and an
--           rac instance or a standalone database is created

-- IN PARAMETERS: see sdk_assoc_pkgdef
--                     p_assoc_def_name VARCHAR2,
--                     p_source_target_name VARCHAR2,
--                     p_source_target_type VARCHAR2,
--                     p_assoc_target_name  VARCHAR2,
--                     p_assoc_target_type  VARCHAR2,
--                     p_scope_target_name  VARCHAR2,
--                     p_scope_target_type  VARCHAR2;
-- OUT PARAMETER: none
--
PROCEDURE asm_license_assoc_create
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS
    l_source_target_guid      mgmt_targets.target_guid%type;
    l_component_target_guid   mgmt_targets.target_guid%type;
    l_current_license_status  VARCHAR2(20);
    l_count    NUMBER;

BEGIN

    l_source_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_source_target_type,
                                         target_name_in=> p_source_target_name
                                     );
    l_component_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_assoc_target_type,
                                         target_name_in=> p_assoc_target_name
                                     );

    IF (p_source_target_type=MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND
        p_assoc_target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE)     THEN

        SELECT
            count(*)
        INTO
            l_count
        FROM
            mgmt_target_properties
        WHERE
            property_name =MGMT_GLOBAL.G_OSM_PROPERTY_NAME  AND
            property_value != ' ' AND
            target_guid = l_source_target_guid AND
            UPPER(property_value) =
                                     (
                                         SELECT
                                             upper(p.property_value)
                                         FROM
                                             mgmt_targets t,
                                             mgmt_target_properties p
                                         WHERE
                                             t.target_type    = MGMT_GLOBAL.G_ASM_TARGET_TYPE   AND
                                             p.property_name  = MGMT_GLOBAL.G_SID_PROPERTY_NAME AND
                                             t.target_guid    = p.target_guid AND
                                             t.target_guid    = l_component_target_guid
                                     );
            IF (l_count > 0) THEN
                FOR c1 in
                       (
                        SELECT
                          distinct pack_name pack_name
                        FROM
                              mgmt_licensed_targets
                        WHERE
                              from_target_guid=l_source_target_guid
                    )
                LOOP
                    --Insert the asm association IN mgmt_licensed_targets as
                    -- Target_guid     Pack_id     From_Target_guid
                    --======================================================================
                    -- <GUID of ASM>  <Pack ID>  <GUID of racinstance OR standalone database>

                    l_current_license_status:= em_license.is_target_pack_licensed
                                           (
                                               p_target_guid=>l_component_target_guid,
                                               p_pack_label=>c1.pack_name,
                                               p_from_target_guid=>l_source_target_guid
                                           );

                        --if FALSE then insert into  mgmt_licensed_targets
                        IF  (l_current_license_status='FALSE') THEN
                           INSERT INTO mgmt_licensed_targets
                           (target_guid, pack_name, from_target_guid)
                           VALUES
                           (l_component_target_guid,c1.pack_name,l_source_target_guid);
                        END IF;

                END LOOP;
            END IF;
    END IF;
END asm_license_assoc_create;
-- PROCEDURE: asm_license_assoc_delete
-- Purpose : This is a Callback invoked when an existing relationship between an ASM and an
--           rac instance or a standalone database is deleted

-- IN PARAMETERS: see sdk_assoc_pkgdef
--                     p_assoc_def_name VARCHAR2,
--                     p_source_target_name VARCHAR2,
--                     p_source_target_type VARCHAR2,
--                     p_assoc_target_name  VARCHAR2,
--                     p_assoc_target_type  VARCHAR2,
--                     p_scope_target_name  VARCHAR2,
--                     p_scope_target_type  VARCHAR2;
-- OUT PARAMETER: none
--
PROCEDURE asm_license_assoc_delete
(
    p_assoc_def_name     VARCHAR2,
    p_source_target_name VARCHAR2,
    p_source_target_type VARCHAR2,
    p_assoc_target_name  VARCHAR2,
    p_assoc_target_type  VARCHAR2,
    p_scope_target_name  VARCHAR2,
    p_scope_target_type  VARCHAR2
)
IS

    l_source_target_guid      mgmt_targets.target_guid%type;
    l_component_target_guid   mgmt_targets.target_guid%type;
    l_current_license_status  VARCHAR2(10);

BEGIN

    l_source_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_source_target_type,
                                         target_name_in=> p_source_target_name
                                     );
    -- Get the GUID of the component to be deleted based on its target_type and name
    l_component_target_guid := mgmt_target.get_target_guid
                                     (
                                         target_type_in =>p_assoc_target_type,
                                         target_name_in=> p_assoc_target_name
                                     );

    IF (p_source_target_type=MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND
         p_assoc_target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE)     THEN
         --delete relation with the standalone database or the rac instance
        DELETE
           mgmt_licensed_targets
        WHERE
           target_guid=l_component_target_guid;
    END IF;
END asm_license_assoc_delete;
-- PROCEDURE: get_pack_definition_details
-- Purpose : This procedure is used to retreive the list of package definitions
--           and their corresponding targets types
-- OUT PARAMETER: em_license_def_array
--
PROCEDURE get_pack_definition_details
(
    l_pack_def_types OUT em_license_def_array
)
IS
    CURSOR c2 (p_target_type VARCHAR2)
        IS
        SELECT
              target_type
          FROM
              mgmt_licensable_target_types
         WHERE
              pack_target_type=p_target_type
          AND target_type IN
             (SELECT target_type 
              FROM mgmt_target_types);

        l_target_counter PLS_INTEGER:=0;
        l_type_counter   PLS_INTEGER:=0;

BEGIN
    l_pack_def_types :=em_license_def_array();

    SELECT em_license_def_obj
           ( pack_abbr,
            pack_display_label,
            pack_label_nlsid,
            null,
            pack_description,
            pack_description_nlsid
            )
     BULK COLLECT INTO l_pack_def_types
     FROM
     ( SELECT DISTINCT pack_abbr,
            pack_display_label,
            pack_label_nlsid,
            pack_description,
            pack_description_nlsid
         FROM   mgmt_license_definitions
         WHERE target_type IN
             (SELECT target_type 
              FROM mgmt_target_types));
    
    IF (l_pack_def_types.COUNT > 0 AND l_pack_def_types IS NOT NULL ) THEN
        FOR l_target_counter IN 1..l_pack_def_types.COUNT
        LOOP
            l_type_counter:=0;
            l_pack_def_types(l_target_counter).target_type_details := em_license_target_type_array();
            FOR c1 IN
            (
                SELECT target_type
                FROM   mgmt_license_definitions
                WHERE  pack_abbr = l_pack_def_types(l_target_counter).pack_abbr
                   AND target_type != MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
            )
            LOOP
                -- To retreive Target_types
                FOR j IN c2(c1.target_type)
                LOOP
                    l_type_counter:=l_type_counter+1;
                    l_pack_def_types(l_target_counter).target_type_details.extend;
                    l_pack_def_types(l_target_counter).target_type_details(l_type_counter):= em_license_target_type_obj(null);
                    l_pack_def_types(l_target_counter).target_type_details(l_type_counter).target_type:=j.target_type;
                END LOOP;
            END LOOP;
        END LOOP;
    END IF;
END get_pack_definition_details;
PROCEDURE get_ALL_setup_data
(
      p_target_type       IN VARCHAR2,
      p_target_name       IN VARCHAR2,
      p_error_message     OUT VARCHAR2,
      p_setup_details_cur_out OUT cursorType
)
IS
      l_target_name     VARCHAR2(64):='%'||p_target_name||'%'; 
BEGIN
    IF length(trim(p_target_name))>0 then
        SELECT 
           upper(trim
           (
               decode
                   (
                       instr(p_target_name,'*'),0,
                               DECODE(instr(p_target_name,'%'),
                       0,'%'||p_target_name||'%',
                       p_target_name),
                           REPLACE(p_target_name,'*','%'))
                    )
            ) 
        INTO l_target_name
        FROM dual;
    END IF;
    
    --to handle excape character _
    l_target_name:=replace(l_target_name,'_','^_');
    
    IF (p_target_type = 'oracle_database')
    THEN
        OPEN p_setup_details_cur_out FOR
            SELECT distinct c.target_guid, c.target_name,
                   c.host_name,
                   a.pack_name,
                   b.confirmation,
                   c.target_type
            FROM
                   mgmt_licensed_targets a,
                   mgmt_license_confirmation b,
                   mgmt_targets c
            WHERE
                    a.target_guid(+)=c.target_guid AND
                    b.target_guid(+)=c.target_guid AND
                    c.target_type  IN (select distinct(target_type)
    		                   FROM mgmt_licensable_target_types) AND
                    c.target_type in ('oracle_database','rac_database') AND 
                    upper(c.target_name) like l_target_name escape '^'
            ORDER BY c.target_type;
    ELSE
        OPEN p_setup_details_cur_out FOR
            SELECT distinct c.target_guid, c.target_name,
                   c.host_name,
                   a.pack_name,
                   b.confirmation,
                   c.target_type
            FROM
                   mgmt_licensed_targets a,
                   mgmt_license_confirmation b,
                   mgmt_targets c
            WHERE
                    a.target_guid(+)=c.target_guid AND
                    b.target_guid(+)=c.target_guid AND
                    c.target_type  IN (select distinct(target_type)
    		                   FROM mgmt_licensable_target_types) AND
                    c.target_type=nvl(p_target_type,c.target_type) and
                    upper(c.target_name) like l_target_name escape '^'
            ORDER BY c.target_type;
    END IF;
	
END get_ALL_setup_data;
PROCEDURE apply_packs_to_ALL_target
(
    p_target_array       IN  MGMT_TARGET_LICENSE_ARRAY,
    p_error_message      OUT VARCHAR2_TABLE,
    p_commit_indicator   OUT varchar2
)
IS
    l_target_guid      mgmt_targets.target_guid%TYPE;
    l_target_name      mgmt_targets.target_name%TYPE;
    l_target_type      mgmt_targets.target_type%TYPE;
    l_target_host_name mgmt_targets.host_name%TYPE;
    l_pack_name        mgmt_licensed_targets.pack_name%type;
    l_current_user     mgmt_priv_grants.grantee%TYPE := MGMT_USER.get_current_em_user();
    l_current_license_status VARCHAR2(10);
    l_error_count   NUMBER DEFAULT 0;
    l_count         NUMBER;
BEGIN
    p_error_message:=varchar2_table();
    p_commit_indicator := 'N';

    IF (p_target_array IS NOT NULL) AND (p_target_array.count>0) THEN
        FOR i in 1..p_target_array.count
        LOOP
        BEGIN
            l_target_name := p_target_array(i).target_name;
            l_target_type := p_target_array(i).target_type;
            l_target_guid:=mgmt_target.get_target_guid
                                      (
                                          target_name_in=>l_target_name,
                                          target_type_in=>l_target_type
                                      );
            -- Proceed if valid target
            IF (l_target_guid IS NOT NULL) THEN
                -- First Process Agree Access (always goes from NO  to YES, never in the opposite direction)
                IF (p_target_array(i).agree_acccess=1) THEN
        -- Change to count('a') for performance as interested in existence only.
                    SELECT COUNT('a')
                    INTO l_count
                    FROM mgmt_license_confirmation
                    WHERE
                        target_guid=l_target_guid AND
                        confirmation='Y';
                    IF (l_count=0) THEN
                        INSERT INTO mgmt_license_confirmation
                        (target_guid, confirmation, confirmed_by, confirmed_time)
                        VALUES
                        (l_target_guid, 'Y', l_current_user, SYSDATE);
                         p_commit_indicator := 'Y';			
                    END IF;
                END IF;
                -- Finish processing Agree Access for the Target

                -- Now Process Pack License Info
                -- First Handle Inserts in MGMT_LICENSED_TARGETS
                IF (p_target_array(i).pack_license_info IS NOT NULL) AND (p_target_array(i).pack_license_info.count>0) THEN
                    FOR j IN 1..p_target_array(i).pack_license_info.count
                    LOOP
                        l_pack_name := p_target_array(i).pack_license_info(j).pack_name;
                        IF (p_target_array(i).pack_license_info(j).is_licensed=1) THEN
                        -- This will ensure that only valid packs for a target type will be processed
                        -- The packs that are not valid for a target type will not have is_licensed=0
                        BEGIN
                            l_current_license_status:= is_target_pack_licensed
                                                      (
                                                          p_target_guid=>l_target_guid,
                                                          p_pack_label=>l_pack_name
                                                      );
                            IF (l_current_license_status='FALSE') THEN
                                BEGIN
                                    INSERT INTO mgmt_licensed_targets (target_guid, pack_name, from_target_guid)
                                    VALUES (l_target_guid, l_pack_name, l_target_guid);
                                    p_commit_indicator := 'Y';

                                    EXCEPTION -- HANDLE EXCEPTIONS "INSERT" RAISES AND MOVE TO NEXT TARGET-PACKAGE COMBINATION
                                       WHEN OTHERS THEN
                                           l_error_count:=l_error_count+1;
					   p_error_message.extend;
                                           p_error_message(l_error_count):='Error while Granting License to Target "'||p_target_array(i).target_name||'" on pack '||l_pack_name;
                                END;
                            END IF; -- l_current_license_status='FALSE'
                        END;
                        ELSIF (p_target_array(i).pack_license_info(j).is_licensed=0) THEN
                            -- Else would have worked as well.  Using ELSIF to be doubly sure
                            -- No License on Target-Pack Combination
                            -- Check if Target-package Combination is already licensed,
                            -- If Yes, Revoke
                            -- Check on From TargetGuid is required.
                            l_current_license_status:= is_target_pack_licensed
                                                          (
                                                              p_target_guid=>l_target_guid,
                                                              p_pack_label=>l_pack_name
                                                          );
                            IF (l_current_license_status='TRUE') THEN
                            BEGIN
                                DELETE  mgmt_licensed_targets
                                WHERE pack_name = l_pack_name and target_guid = l_target_guid;
                                p_commit_indicator := 'Y';
                                EXCEPTION -- HANDLE EXCEPTIONS "RELETE" RAISES AND MOVE TO NEXT TARGET-PACKAGE COMBINATION
                                   WHEN OTHERS THEN
                                       l_error_count:=l_error_count+1;
                                       p_error_message.extend;
                                       p_error_message(l_error_count):='Error while Revoking License from Target "'||p_target_array(i).target_name||'" on pack '||l_pack_name;
                            END;
                            END IF; -- (l_current_license_status='TRUE')
                        END IF; -- IF (p_target_array(i).pack_license_info(j).is_licensed=1)
                    END LOOP; -- j (p_target_array(i).pack_license_info.count)
                END IF; -- p_target_array(i).pack_license_info IS NOT NULL
                -- END Process Pack License Info
            END IF; -- IF (l_target_guid IS NOT NULL)
            EXCEPTION -- HANDLE EXCEPTIONS "INSERT" RAISES AND MOVE TO NEXT TARGET-PACKAGE COMBINATION
               WHEN OTHERS THEN
                   l_error_count:=l_error_count+1;
                   p_error_message.extend;
                   p_error_message(l_error_count):='Error while checking TargetName and TargetType Combination: "'|| l_target_name || ' and '|| l_target_type;

        END; -- END Begin inside loop.
        END LOOP; -- i (p_target_array.count)
    END IF; -- (p_target_array IS NOT NULL) AND (p_target_array.count>0)
END apply_packs_to_ALL_target;

-- Procedure : add_pack_definitions
-- Purpose  : To insert pack definitions into mgmt_license_definitions if the pack definition is new
--            or update an existing pack definition if pack definition needs to be changed.
-- Input Parameters
-- pack_label
-- target_type
-- pack_description
-- pack_display_label
-- pack_abbr

-- Output Parameters

PROCEDURE add_pack_definitions
(
    p_pack_label         mgmt_license_definitions.pack_label%TYPE,
    p_target_type        mgmt_license_definitions.target_type%TYPE,
    p_pack_display_label mgmt_license_definitions.pack_display_label%TYPE DEFAULT NULL,
    p_pack_description   mgmt_license_definitions.pack_description%TYPE,
    p_pack_abbr          mgmt_license_definitions.pack_abbr%TYPE
)
IS
    p_count              NUMBER DEFAULT 0;
    p_update_clause      VARCHAR2(200) DEFAULT NULL;
    p_set_clause         VARCHAR2(4000) DEFAULT NULL;
    p_where_clause       VARCHAR2(1000) DEFAULT NULL;
    p_update_statement   VARCHAR2(2000) DEFAULT NULL;
    l_pack_label_nlsid       mgmt_license_definitions.pack_label_nlsid%TYPE;
    l_pack_description_nlsid mgmt_license_definitions.pack_description_nlsid%TYPE;
    
BEGIN
-- Check if a record corresponding to the pack_label and target_type already exists in mgmt_license_definitions.
-- If yes, then update the existing record with new values of pack_description, pack_abbr and pack_display_label 
-- got from the input parameters of the procedure.
-- If no records exists corresponding to the pack_label and target_type, then insert a new record with the values
-- of the input parameters passed to the procedure.

    SELECT count(*)
    INTO   p_count
    FROM   mgmt_license_definitions
    WHERE
        pack_label  = p_pack_label AND
        target_type = p_target_type;
    IF (p_count > 0) THEN
        EM_CHECK.check_not_null(p_pack_label,'p_pack_label');
	
        l_pack_label_nlsid:=p_pack_label||'_nlsid';
        l_pack_description_nlsid:=p_pack_label||'_description_nlsid';
        
	--Note on SQL Violation Fix
	--This procedure is not called by any UI code
	--This is used to only maintain initial values in
	--  MGMT_LICENSE_DEFINITIONS as part of setup script
	--Used Bind Variables in the update clause
	
    -- record corresponding to the pack_label and target_type already exists in mgmt_license_definitions
    -- update the existing record.
        p_update_clause  := ' UPDATE mgmt_license_definitions ';
        -- Check if the pack_display_label is passed as null, then retain the old value of the pack_display_label
        IF(p_pack_display_label IS NULL) THEN
           p_set_clause := ' SET pack_description =:p_pack_description, 
                                 pack_label_nlsid = :l_pack_label_nlsid,
                                 pack_description_nlsid =:l_pack_description_nlsid,
                                 pack_abbr =:p_pack_abbr';
            
        ELSE
        -- if pack_display_label is not null, then update the existing value of the pack_display_label 
        -- with the new value passed as the input parameter to the procedure.
           p_set_clause := ' SET pack_description =:p_pack_description,
                                 pack_label_nlsid = :l_pack_label_nlsid ,
                                 pack_description_nlsid = :l_pack_description_nlsid,
                                 pack_abbr =:p_pack_abbr, 
                                 pack_display_label =:p_pack_display_label';
            
        END IF;
        p_where_clause   := ' WHERE pack_label =:p_pack_label AND target_type =:p_target_type';
        p_update_statement := p_update_clause||p_set_clause||p_where_clause;
        IF (p_pack_display_label IS NULL) THEN
            EXECUTE IMMEDIATE EM_CHECK.NOOP(p_update_statement) using p_pack_description,l_pack_label_nlsid,l_pack_description_nlsid,p_pack_abbr,p_pack_label,p_target_type;    				
        ELSE
           EXECUTE IMMEDIATE EM_CHECK.NOOP(p_update_statement) using p_pack_description,l_pack_label_nlsid,l_pack_description_nlsid,p_pack_abbr,p_pack_display_label,p_pack_label,p_target_type;			
        END IF;
       
    ELSE
    -- new row, insert the record
        INSERT INTO mgmt_license_definitions
        (
            pack_label, 
            target_type, 
            pack_display_label,
            pack_label_nlsid, 
            pack_description, 
            pack_description_nlsid,
            pack_abbr
        )
        VALUES
        (
            p_pack_label,
            p_target_type,
            p_pack_display_label,
            p_pack_label||'_nlsid',
            p_pack_description,
            p_pack_label||'_description_nlsid',
            p_pack_abbr
       );
    END IF;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE;
END add_pack_definitions;

-- Procedure : add_licensable_target_types
-- Purpose  : To insert licensable target types into mgmt_licensable_target_types. Check if already present, else insert.
-- Input Parameters
-- pack_target_type
-- target_type

-- Output Parameters
PROCEDURE add_licensable_target_types
(
    p_pack_target_type    mgmt_licensable_target_types.pack_target_type%TYPE,
    p_target_type         mgmt_licensable_target_types.target_type%TYPE
)
IS
    p_count               NUMBER DEFAULT 0;
BEGIN
    SELECT count(*)
    INTO   p_count
    FROM   mgmt_licensable_target_types
    WHERE
        pack_target_type  = p_pack_target_type AND
        target_type       = p_target_type;
    
    IF (p_count = 0) THEN
        INSERT INTO mgmt_licensable_target_types
        (
            pack_target_type,
            target_type
        )
        VALUES
        (
            p_pack_target_type,
            p_target_type
        );
    END IF;
END add_licensable_target_types;
-- Procedure:del_licensable_target_types
-- Purpose  :To delete licensable target types into mgmt_licensable_target_types. Check if already present, else delete.
-- Input Parameters : pack_target_type
--                    target_type
-- Output Parameters
PROCEDURE del_licensable_target_types
(
    p_pack_target_type    mgmt_licensable_target_types.pack_target_type%TYPE,
    p_target_type         mgmt_licensable_target_types.target_type%TYPE
)
IS
    p_count               NUMBER DEFAULT 0;
BEGIN
    SELECT count(*)
    INTO   p_count
    FROM   mgmt_licensable_target_types
    WHERE
        pack_target_type  = p_pack_target_type AND
        target_type       = p_target_type;
    
    IF (p_count = 1) THEN
        DELETE FROM mgmt_licensable_target_types
        WHERE  pack_target_type=p_pack_target_type AND
               target_type=p_target_type;
    END IF;
END del_licensable_target_types;

-- Procedure : add_packs
-- Purpose  : To insert license packs into mgmt_admin_licenses. Check if already present, else insert.
-- Input Parameters
-- pack_name

-- Output Parameters

PROCEDURE add_packs
(
    p_pack_name    mgmt_admin_licenses.pack_name%TYPE
)
IS
    p_count        NUMBER DEFAULT 0;
BEGIN
    SELECT  count(*)
    INTO    p_count
    FROM    mgmt_admin_licenses
    WHERE
        pack_name  = p_pack_name;

    IF (p_count = 0) THEN
        INSERT INTO mgmt_admin_licenses
        (
            pack_name
        )
        VALUES
        (
            p_pack_name
        );
    END IF;
END add_packs;

-- Procedure : get_license_setup_data
-- Purpose  : To get license packs information for targets.
-- Input Parameters
-- p_target_type       - search criteria for target type 
-- p_target_name       - serach criteria for target name 
-- p_all_targets_packs - flag indicating if user selected All Targets option OR Licensable Targets option
-- p_max_rows          - maximum no. of records to be retrieved
-- Output Parameters
-- p_error_message     - error message is any
-- p_columns_array     - the columns (license pack names) to be rendered on UI
-- p_target_array      - License Pack info for each target selected

PROCEDURE get_license_setup_data
(
    p_target_type  IN VARCHAR2,
    p_target_name  IN VARCHAR2,
    p_all_targets_packs IN VARCHAR2,
    p_max_rows IN NUMBER,
    p_error_message     OUT VARCHAR2,
    p_columns_array   OUT cursorType,
    p_target_array    OUT em_license_pack_array
)

IS

    -- License Pack Constants
    LICENSED_PACK       CONSTANT NUMBER(1) := 0;
    NOT_LICENSED_PACK   CONSTANT NUMBER(1) := 1;
    NOT_APPLICABLE_PACK CONSTANT NUMBER(1) := 2;

    ALL_TARGETS         CONSTANT VARCHAR2(64) := 'ALL';

    l_target_guid_array mgmt_target_guid_array;             --collection of target guid
    l_target_type_array varchar2_table := varchar2_table(); --collection of target type
    l_target_name_array varchar2_table := varchar2_table(); --collection of target name
    l_host_name_array   varchar2_table := varchar2_table(); --collection of host name
    l_license_confirmation_array varchar2_table := varchar2_table(); --collection of license confirmation
    l_license_pack_array varchar2_table := varchar2_table(); --collection of license packs    

    l_target_name       mgmt_targets.target_name%TYPE;
    l_target_guid       mgmt_targets.target_guid%TYPE;
    l_target_type	mgmt_targets.target_type%TYPE;
    l_packname          mgmt_licensed_targets.pack_name%TYPE;
    l_packnames_cursor cursorType;
    l_count          NUMBER;
    l_licensed       NUMBER;
    l_target_counter NUMBER;  
    l_pack_counter   NUMBER;
BEGIN   
    --validate the taregt type
    IF (p_target_type IS NOT NULL) THEN
        SELECT count(*)
        INTO l_count
        FROM mgmt_licensable_target_types
        WHERE target_type = p_target_type;

        IF (l_count=0) THEN
            raise_application_error(em_license.INVALID_TARGET_PACK_COMBI,
                                'Invalid Target Type');
        END IF;
    END IF;
    
    --Validate max_rows passed
    IF (p_max_rows <= 0) THEN
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
                                'Maximum rows specified should be greater than zero.');
    END IF;    
    --To handle wildcard search
    IF LENGTH(TRIM(p_target_name))>0 THEN
        SELECT
            UPPER(TRIM
            (
                DECODE
                    (
                        INSTR(p_target_name,'*'),0,
                        DECODE(INSTR(p_target_name,'%'),
                        0,'%'||p_target_name||'%',
                        p_target_name),
                        REPLACE(p_target_name,'*','%'))
                    )
            )
        INTO l_target_name
        FROM dual;
    END IF;

    --To handle escape character _
    l_target_name:=REPLACE(l_target_name,'_','^_');

    IF (UPPER(p_all_targets_packs) = ALL_TARGETS) THEN --If All Targets Option is selected    
    
        -- Following sql will select All oracle_database insatnces and 
        -- rac_instances. All rac_database will also be selected.
        IF (p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) THEN
            IF(l_target_name IS NULL ) THEN -- Target Name is not specified in search criteria
                SELECT DISTINCT target_guid, 
                       target_name,
                       host_name, 
                       confirmation,
                       target_type
                BULK COLLECT INTO 
                       l_target_guid_array, 
                       l_target_name_array, 
                       l_host_name_array, 
                       l_license_confirmation_array,
                       l_target_type_array
                FROM
                (
                    SELECT 
                       c.target_guid, 
                       c.target_name,
                       c.host_name, 
                       b.confirmation,
                       c.target_type
                    FROM
                       mgmt_license_confirmation b,
                       mgmt_targets c
                    WHERE
                       b.target_guid(+)=c.target_guid AND
                       c.target_type  IN (
                                          SELECT DISTINCT(target_type)
                                          FROM mgmt_licensable_target_types
                                          ) 
                        AND c.target_type IN (MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
                                              MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) 
                        ORDER BY c.target_name
                ) WHERE ROWNUM <= p_max_rows;
            ELSE -- Target Name is specified in search criteria
                SELECT DISTINCT target_guid, 
                       target_name,
                       host_name, 
                       confirmation,
                       target_type
                 BULK COLLECT INTO 
                       l_target_guid_array, 
                       l_target_name_array, 
                       l_host_name_array, 
                       l_license_confirmation_array,
                       l_target_type_array
                FROM 
                (
                    SELECT  
                       c.target_guid, 
                       c.target_name,
                       c.host_name, 
                       b.confirmation,
                       c.target_type
                    FROM
                       mgmt_license_confirmation b,
                       mgmt_targets c
                    WHERE
                        b.target_guid(+)=c.target_guid AND
                        c.target_type  IN (
                                           SELECT distinct(target_type)
                                           FROM mgmt_licensable_target_types
                                           ) AND
                        c.target_type IN (MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
                                          MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) AND
                        UPPER(c.target_name) LIKE l_target_name ESCAPE '^' 
                    ORDER BY c.target_name
                ) WHERE ROWNUM <= p_max_rows;
            END IF;
        ELSE
            IF(l_target_name IS NULL ) THEN -- Target Name is not specified in search criteria
               SELECT DISTINCT target_guid,
                       target_name,
                       host_name, 
                       confirmation,
                       target_type
               BULK COLLECT INTO 
                     l_target_guid_array, 
                     l_target_name_array, 
                     l_host_name_array, 
                     l_license_confirmation_array,
                     l_target_type_array
               FROM
               (
                   SELECT DISTINCT 
                       c.target_guid,
                       c.target_name,
                       c.host_name, 
                       b.confirmation,
                       c.target_type
                   FROM
                       mgmt_license_confirmation b,
                       mgmt_targets c
                   WHERE
                        b.target_guid(+)=c.target_guid AND
                        c.target_type  IN (
                                           SELECT DISTINCT(target_type)
                                           FROM mgmt_licensable_target_types
                                           ) 
                        AND c.target_type = NVL(p_target_type,c.target_type) 
                   ORDER BY c.target_name, c.target_type
               ) WHERE ROWNUM <= p_max_rows;            
            ELSE -- Target Name is specified in search criteria
               SELECT DISTINCT target_guid, 
                       target_name,
                       host_name, 
                       confirmation,
                       target_type
               BULK COLLECT INTO 
                      l_target_guid_array, 
                      l_target_name_array, 
                      l_host_name_array, 
                      l_license_confirmation_array,
                      l_target_type_array
               FROM
               (
                   SELECT c.target_guid, 
                          c.target_name,
                          c.host_name, 
                          b.confirmation,
                          c.target_type
                   FROM   mgmt_license_confirmation b,
                          mgmt_targets c
                   WHERE
                        b.target_guid(+)=c.target_guid AND
                        c.target_type  IN (
                                           SELECT DISTINCT(target_type)
                                           FROM mgmt_licensable_target_types
                                           ) 
                        AND c.target_type=NVL(p_target_type,c.target_type) and
                        UPPER(c.target_name) LIKE l_target_name escape '^' 
                        ORDER BY c.target_name, c.target_type
                ) WHERE ROWNUM <= p_max_rows;
            END IF;
        END IF; 
    ELSE  --If Licensable Targets option is selected
        IF((l_target_name IS NULL ) AND (p_target_type=MGMT_GLOBAL.G_DATABASE_TARGET_TYPE)) THEN
         -- Following sql will All oracle_database insatnces and filter out
         -- rac_instances. All rac_database will also be selected.
            SELECT DISTINCT target_guid, 
                   target_name,
                   host_name, 
                   confirmation,
                   target_type
            BULK COLLECT INTO 
                  l_target_guid_array, 
                  l_target_name_array, 
                  l_host_name_array, 
                  l_license_confirmation_array,
                  l_target_type_array                   
            FROM (
                   SELECT c.target_guid, 
                       c.target_name,
                       c.host_name, 
                       b.confirmation,
                       c.target_type
                   FROM
                       mgmt_license_confirmation b,
                       mgmt_targets c
                   WHERE
                       b.target_guid(+)=c.target_guid AND
                       c.target_type=p_target_type    AND
                       c.target_guid 
                       IN (
                                SELECT  mgmt_targets.target_guid
                                FROM    mgmt_targets
                                WHERE   (target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) AND
                                         target_name NOT IN 
                                         (
                                             SELECT mt.target_name
                                              FROM  mgmt_target_assocs, 
                                                    mgmt_targets mt,mgmt_targets mt1
                                              WHERE  assoc_guid = MGMT_ASSOC.g_contains_guid AND
                                                     mt.target_guid = assoc_target_guid    AND
                                                     mt.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND
                                                     mt1.target_guid=source_target_guid    AND
                                                     mt1.target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE
                                         )
                          ) 
                   UNION ALL
                   SELECT c.target_guid,
                       c.target_name,
                       c.host_name,
                       b.confirmation,
                       c.target_type
                   FROM
                       mgmt_license_confirmation b,
                       mgmt_targets c
                   WHERE
                       b.target_guid(+)=c.target_guid AND
                       c.target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE 
                   ORDER BY target_name
               ) WHERE ROWNUM <= p_max_rows;
        ELSIF(l_target_name IS NULL ) THEN
            SELECT DISTINCT target_guid, 
                  target_name,
                  host_name, 
                  confirmation, 
                  target_type
            BULK COLLECT INTO 
                  l_target_guid_array, 
                  l_target_name_array, 
                  l_host_name_array, 
                  l_license_confirmation_array,
                  l_target_type_array                      
            FROM (
                    SELECT c.target_guid, 
                        c.target_name,
                        c.host_name, 
                        b.confirmation, 
                        c.target_type
                    FROM
                        mgmt_license_confirmation b,
                        mgmt_targets c
                    WHERE
                        b.target_guid(+)=c.target_guid AND
                        c.target_type=p_target_type 
                    ORDER BY target_name
            ) WHERE ROWNUM <= p_max_rows;
        END IF;
        -- Following sql will All oracle_database insatnces and filter out
        -- rac_instances. All rac_database will also be selected.
        IF(l_target_name IS NOT NULL AND (p_target_type=MGMT_GLOBAL.G_DATABASE_TARGET_TYPE)) THEN
           
           SELECT DISTINCT target_guid, 
                  target_name,
                  host_name,
                  confirmation, 
                  target_type
           BULK COLLECT INTO 
                  l_target_guid_array, 
                  l_target_name_array, 
                  l_host_name_array, 
                  l_license_confirmation_array,
                  l_target_type_array                
          FROM 
          (
              SELECT c.target_guid, 
                  c.target_name,
                  c.host_name,
                  b.confirmation, 
                  c.target_type
              FROM
                  mgmt_license_confirmation b,
                  mgmt_targets c
              WHERE
                  b.target_guid(+)=c.target_guid AND
                  c.target_type=p_target_type AND
                  UPPER(c.target_name) like UPPER(TRIM(l_target_name)) ESCAPE '^' and
                  c.target_guid NOT IN 
                  (
                     SELECT mt.target_guid
                      FROM  mgmt_target_assocs, 
                            mgmt_targets mt,
                            mgmt_targets mt1
                      WHERE assoc_guid = MGMT_ASSOC.g_contains_guid AND
                            mt.target_guid = assoc_target_guid    AND
                            mt.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND
                            mt1.target_guid=source_target_guid       AND
                            mt1.target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE
                  ) 
              UNION
              SELECT c.target_guid,
                     c.target_name,
                     c.host_name, 
                     b.confirmation, 
                     c.target_type
              FROM
                   mgmt_license_confirmation b,
                   mgmt_targets c
              WHERE
                  b.target_guid(+)=c.target_guid AND
                  UPPER(c.target_name) like UPPER(TRIM(l_target_name)) escape '^' AND
                  c.target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE 
              ORDER BY target_name
          ) 
          WHERE ROWNUM <= p_max_rows;
        ELSIF(l_target_name IS NOT NULL) THEN
           SELECT DISTINCT target_guid, 
                   target_name,
                   host_name,
                   confirmation, 
                   target_type
            BULK COLLECT INTO 
                   l_target_guid_array, 
                   l_target_name_array, 
                   l_host_name_array, 
                   l_license_confirmation_array,
                   l_target_type_array                 
            FROM 
            (          
                SELECT c.target_guid,
                   c.target_name,
                   c.host_name,
                   b.confirmation,
                   c.target_type
                FROM
                   mgmt_license_confirmation b,
                   mgmt_targets c
                WHERE
                   b.target_guid(+)=c.target_guid AND
                   c.target_type=p_target_type AND
                   UPPER(c.target_name) like l_target_name escape '^' and
                   c.target_guid NOT IN 
                   (
                        SELECT  mt.target_guid
                         FROM   mgmt_target_assocs, 
                                mgmt_targets mt,
                                mgmt_targets mt1
                         WHERE  assoc_guid = MGMT_ASSOC.g_contains_guid AND
                                mt.target_guid = assoc_target_guid    AND
                                mt.target_type = mgmt_global.G_DATABASE_TARGET_TYPE AND
                                mt1.target_guid=source_target_guid       AND
                                mt1.target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE
                   )
                ORDER BY c.target_name
            ) WHERE ROWNUM <= p_max_rows;
        END IF;
    END IF;  
    
    p_target_array:= em_license_pack_array();
    l_target_counter:=0;

    --For All Targets option when All Targets is selected in search criteria
    -- select all pack names
    IF(UPPER(p_all_targets_packs) = ALL_TARGETS AND p_target_type IS NULL) THEN 
        IF (l_target_type_array IS NOT NULL) THEN
            SELECT distinct b.pack_label BULK COLLECT INTO l_license_pack_array
            FROM mgmt_licensable_target_types a, mgmt_license_definitions b
            WHERE a.pack_target_type = b.target_type
            AND a.target_type in (SELECT * FROM TABLE(CAST(l_target_type_array AS varchar2_table)));
        END IF;
    --For All Targets option when specific target type is selected in search criteria
    -- select all pack names applicable to that target type (including direct AND associated packs)
    ELSIF (UPPER(p_all_targets_packs) = ALL_TARGETS AND p_target_type IS NOT NULL) THEN
       SELECT distinct b.pack_label BULK COLLECT INTO l_license_pack_array
       FROM mgmt_licensable_target_types a, mgmt_license_definitions b
       WHERE a.pack_target_type = b.target_type AND a.target_type=p_target_type;
    --For Licensed Targets option select pack names (direct licensing) applicable to that target type
    ELSE
        SELECT distinct pack_label BULK COLLECT INTO l_license_pack_array
        FROM  mgmt_license_definitions 
        WHERE target_type=p_target_type;
    END IF;

    -- Populate p_target_array object with licensing information for each target       
    IF (l_target_guid_array IS NOT NULL) AND (l_target_guid_array.count>0) THEN
        FOR counter IN 1..l_target_guid_array.count
        LOOP
            l_target_guid:=l_target_guid_array(counter);
            l_target_type:=l_target_type_array(counter);
            l_target_counter:=l_target_counter+1;
            p_target_array.extend(1);
            p_target_array(l_target_counter):=em_license_pack_obj(NULL,NULL, NULL, NULL, NULL, NULL);

            p_target_array(l_target_counter).target_guid:=l_target_guid_array(counter);
            p_target_array(l_target_counter).target_type:=l_target_type_array(counter);
            p_target_array(l_target_counter).target_name:=l_target_name_array(counter);
            p_target_array(l_target_counter).host_name:=l_host_name_array(counter);

            p_target_array(l_target_counter).pack_license_info:=mgmt_pack_license_array();
            l_pack_counter:=0;
            p_target_array(l_target_counter).confirmation :=l_license_confirmation_array(counter);
            --Get pack information for each target
            IF (l_license_pack_array IS NOT NULL) AND (l_license_pack_array.count > 0) THEN
                FOR pack_counter IN 1..l_license_pack_array.count
                LOOP
                   l_packname := l_license_pack_array(pack_counter);

                   p_target_array(l_target_counter).pack_license_info.extend(1);
                   p_target_array(l_target_counter).pack_license_info(pack_counter):=mgmt_pack_license_obj(NULL, NULL);
                   p_target_array(l_target_counter).pack_license_info(pack_counter).pack_name:=l_packname;

                   -- First Check whether the current pack is applicable to the current target or not
                   SELECT count(*)
                   INTO   l_count
                   FROM
                        mgmt_license_definitions
                   WHERE
                        target_type IN
                        (
                            SELECT distinct(pack_target_type)
                            FROM mgmt_licensable_target_types
                            WHERE target_type=l_target_type
                         ) 
                         AND pack_label=l_license_pack_array(pack_counter);

                   IF (l_count=0) THEN-- the pack/target commbi is NA
                       p_target_array(l_target_counter).pack_license_info(pack_counter).is_licensed:=NOT_APPLICABLE_PACK;
                   ELSE
                       -- The pack is applicable to this target.  Check whether it is licensed
                       SELECT COUNT(*)
                       INTO   l_count
                       FROM   mgmt_licensed_targets
                       WHERE
                            target_guid=l_target_guid AND
                            pack_name=l_license_pack_array(pack_counter);

                       IF (l_count>0) THEN -- Licensed
                            p_target_array(l_target_counter).pack_license_info(pack_counter).is_licensed:=LICENSED_PACK;
                       ELSE
                            p_target_array(l_target_counter).pack_license_info(pack_counter).is_licensed:=NOT_LICENSED_PACK;
                       END IF;
                   END IF;           
               END LOOP; --end loop for license pack               
           END IF;
       END LOOP;
    END IF;

    --Get Pack details  
    IF (l_license_pack_array IS NOT NULL) THEN
        OPEN p_columns_array FOR
            SELECT distinct pack_label,
            pack_label_nlsid,
            pack_display_label,
            pack_description,
            pack_description_nlsid
            FROM mgmt_license_definitions 
            WHERE pack_label in (
                                 SELECT /*+ CARDINALITY (a 10)*/ * FROM TABLE(CAST(l_license_pack_array AS varchar2_table)) a
                                )ORDER BY pack_label;
    END IF;
END get_license_setup_data;

end em_license;
/