--  @(#)sp_lic_pack_info_by_family.sql	1.6 01/02/08
--
--  Copyright (c) 02/08/01 Sun Microsystems, Inc. All Rights Reserved.
--

CREATE OR REPLACE PROCEDURE sp_lic_pack_info_by_family
-- File:        @(#)sp_lic_pack_info_by_family.sql	1.6
-- Version:     01/02/08
-- Description:
(   csr     IN OUT  topology_package.csr_pack_info_by_family
)
AS
BEGIN
    trace.l( 'sp_lic_pack_info_by_family.sql 1.6', 14, 'START sp_lic_pack_info_by_family.sql 1.6' );

    OPEN    csr
    FOR SELECT  grp
        ,       MAX( DECODE( pack_id, 1, cnt ) )        AS  pack_a
        ,       MAX( DECODE( pack_id, 2, cnt ) )        AS  pack_b
        FROM    (   SELECT  lw.type                     AS  grp
                    ,       pack_id
                    ,       COUNT( DISTINCT l.leaf_id)  AS  cnt
                    FROM    leaf                        l
                    ,       entity_moduleinstance       emi
                    ,       moduleinstance              mi
                    ,       pack_module                 pm
                    ,       family_type                 ft
                    ,       licensing_weights           lw
                    WHERE   l.family_type_id            =   ft.family_type_id
                    AND     lw.licensing_weights_id     =   ft.licensing_weights_id
                    AND     lw.type                     !=  'NA'
                    AND     l.leaf_id                   =   emi.leaf_id
                    AND     l.licensable 		        =   1
                    AND     mi.module_instance_id       =   emi.module_instance_id
                    AND     mi.module_id                =   pm.module_id
                    GROUP BY lw.type
                    ,        pack_id
                )
        GROUP BY grp
    ;

    trace.l( 'sp_lic_pack_info_by_family.sql 1.6', 42, 'FINISH sp_lic_pack_info_by_family.sql 1.6' );

END;
/
SHOW ERRORS;
