Rem drv: <create type="pkgbodys" pos="license/mgmt_license_pkgbody.sql+"/>
Rem
Rem $Header: license_report_pkgbody.sql 01-jul-2005.21:26:31 gsbhatia Exp $
Rem
Rem license_report_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      license_report_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    vkgarg      03/17/05 - vkgarg_bug-3996615
Rem    vkgarg      03/14/05 - update to get pack labels
Rem    vkgarg      03/10/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

CREATE OR REPLACE PACKAGE BODY em_licensing_report
AS

PROCEDURE consolidated_report
(
    p_include_targets IN  NUMBER default 0,
    p_agree_access    IN  NUMBER DEFAULT 0,
    p_target_array    OUT em_license_report_array
)
IS
    --p_target_array em_license_report_array;
    
    l_include_targets   NUMBER :=LTRIM(RTRIM(p_include_targets));
    l_agree_access      NUMBER :=LTRIM(RTRIM(p_agree_access));
        
    l_target_counter NUMBER;
    l_pack_counter   NUMBER;
    l_count          NUMBER;
    
    l_select_statement VARCHAR2(2000);
    
    TYPE RefCurType IS REF CURSOR;  -- define weak REF CURSOR type
    l_RefCur RefCurType;            -- declare cursor variable
    
    l_target_guid       mgmt_targets.target_guid%TYPE;
    l_target_guid_array mgmt_target_guid_array;
    l_target_type_array varchar2_table := varchar2_table();
    l_target_name_array varchar2_table := varchar2_table();
    l_host_name_array   varchar2_table := varchar2_table();
        
BEGIN    
    IF (l_include_targets NOT IN (0,1,2)) THEN
        l_include_targets:=0;
    END IF;
    
    IF (l_agree_access NOT IN (0,1,2)) THEN
        l_agree_access:=0;
    END IF;
    
    IF (l_include_targets=0) AND (l_agree_access=0) THEN  -- Parents with Agree Access not Confirmed
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(pack_target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ') AND '||
                                  'target_guid NOT IN '||
                                      '('||
                                           'SELECT target_guid '|| 
                                           'FROM mgmt_license_confirmation'||
                                      ')';
    
    ELSIF (l_include_targets=0) AND (l_agree_access=1) THEN  -- Parents with Agree Access Confirmed 
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(pack_target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ') AND '||
                                  'target_guid IN '||
                                      '('||
                                           'SELECT target_guid '|| 
                                           'FROM mgmt_license_confirmation'||
                                      ')';
    
    ELSIF (l_include_targets=0) AND (l_agree_access=2) THEN -- All parents irrespective of Agree Access status
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(pack_target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ')';
     
    ELSIF  (l_include_targets=1) AND (l_agree_access=0) THEN -- All targets with Agree Access Not Confirmed  
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ') AND '||
                                  'target_guid NOT IN '||
                                      '('||
                                           'SELECT target_guid '|| 
                                           'FROM mgmt_license_confirmation'||
                                      ')';
    
    ELSIF  (l_include_targets=1) AND (l_agree_access=1) THEN -- All targets with Agree Access Confirmed  
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ') AND '||
                                  'target_guid IN '||
                                      '('||
                                           'SELECT target_guid '|| 
                                           'FROM mgmt_license_confirmation'||
                                      ')';
    ELSIF  (l_include_targets=1) AND (l_agree_access=2) THEN -- All targets irrespective of Agree Access Confirmed     
        l_select_statement:= 'SELECT target_guid, target_name, target_type, host_name '||
                             'FROM mgmt_targets '||
                             'WHERE target_type IN '||
                                  '('||
                                        'SELECT DISTINCT(target_type) '||
                                        'FROM mgmt_licensable_target_types '||
                                     ')';

    END IF;

    OPEN l_RefCur FOR l_select_statement;
        FETCH l_RefCur 
        BULK COLLECT INTO 
        l_target_guid_array, l_target_name_array, l_target_type_array, l_host_name_array;
    CLOSE l_RefCur;

    p_target_array:= em_license_report_array();
    l_target_counter:=0;

    IF (l_target_guid_array IS NOT NULL) AND (l_target_guid_array.count>0) THEN
        FOR i IN 1..l_target_guid_array.count
        LOOP
            l_target_guid:=l_target_guid_array(i);
            
            l_target_counter:=l_target_counter+1;
            p_target_array.extend(1);
            p_target_array(l_target_counter):=em_license_report_obj(NULL, NULL, NULL, NULL, NULL);
            
            p_target_array(l_target_counter).target_type:=l_target_type_array(i);
            p_target_array(l_target_counter).target_name:=l_target_name_array(i);
            p_target_array(l_target_counter).host_name:=l_host_name_array(i);
            
            p_target_array(l_target_counter).pack_license_info:=mgmt_pack_license_array();
            l_pack_counter:=0;

            FOR c2 IN
            (
                SELECT 
                    DISTINCT pack_label,
                    pack_display_label
                FROM
                    mgmt_license_definitions 
                ORDER BY pack_label
            )
            LOOP
                l_pack_counter:=l_pack_counter+1;
                p_target_array(l_target_counter).pack_license_info.extend(1);
                p_target_array(l_target_counter).pack_license_info(l_pack_counter):=mgmt_pack_license_obj(NULL, NULL);
                
                p_target_array(l_target_counter).pack_license_info(l_pack_counter).pack_name:=c2.pack_label;
                
                -- 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=p_target_array(l_target_counter).target_type
                        )AND
                    pack_label=c2.pack_label;
                
                IF (l_count=0) THEN-- the pack/target commbi is NA
                    p_target_array(l_target_counter).pack_license_info(l_pack_counter).is_licensed:=2;
                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=c2.pack_label;
                    
                    IF (l_count>0) THEN -- Licensed
                        p_target_array(l_target_counter).pack_license_info(l_pack_counter).is_licensed:=1;                                  
                    ELSE
                        p_target_array(l_target_counter).pack_license_info(l_pack_counter).is_licensed:=0;              
                    END IF;
                END IF;             
            END LOOP; -- END C2
            
            IF (l_agree_access=0) THEN
                p_target_array(l_target_counter).agree_access:=0;
            ELSIF (l_agree_access=1) THEN
                p_target_array(l_target_counter).agree_access:=1;
            ELSE -- AGREE_ACCESS=2 include all
                SELECT count(*)
                INTO l_count
                FROM mgmt_license_confirmation
                WHERE target_guid=l_target_guid;
                
                IF (l_count=0) THEN
                    p_target_array(l_target_counter).agree_access:=0;
                ELSE
                    p_target_array(l_target_counter).agree_access:=1;   
                END IF;
            
            END IF;
        END LOOP;
    END IF;
    
END consolidated_report;


END em_licensing_report;
/