create or alter procedure SPC_FAMILIAS_CTE (
    CTE_CONTROLE integer)
returns (
    FAMILIA_ID integer,
    DESC_FAMILIA varchar(200),
    VALOR numeric(15,2))
as
declare variable NFE_ID integer;
declare variable NFS_ID integer;
BEGIN
    
    FOR SELECT
            T.NFE_ID,
            T.NFS_ID
        FROM TBNF_REFERENCIADA T
        WHERE T.ID = :CTE_CONTROLE
        INTO :NFE_ID, :NFS_ID
    DO BEGIN
        IF(:NFE_ID > 0) THEN
        BEGIN
            FOR SELECT
                    X.*
            FROM (SELECT
                F.CODIGO AS FAMILIA_ID,
                FN_LPAD(F.CODIGO, 4, '0')||' - '||F.DESCRICAO AS DESC_FAMILIA,
                SUM(T.VALOR_TOTAL) AS VALOR
            FROM TBNFE_ITEM T
            INNER JOIN TBPRODUTO P ON P.CODIGO = T.PRODUTO_CODIGO
            INNER JOIN TBFAMILIA F ON F.CODIGO = P.FAMILIA_ALTERNATIVA
            WHERE T.NFE_CONTROLE = :NFE_ID
            GROUP BY FAMILIA_ID, DESC_FAMILIA) X
            INTO :FAMILIA_ID, :DESC_FAMILIA, :VALOR
            DO BEGIN

                SUSPEND;
            END
        END

        IF(:NFS_ID > 0) THEN
        BEGIN
            FOR SELECT
                    X.*
            FROM (SELECT
                F.CODIGO AS FAMILIA_ID,
                FN_LPAD(F.CODIGO, 4, '0')||' - '||F.DESCRICAO AS DESC_FAMILIA,
                SUM(T.VALOR_TOTAL) AS VALOR
            FROM TBNFS_ITEM T
            INNER JOIN TBPRODUTO P ON P.CODIGO = T.PRODUTO_CODIGO
            INNER JOIN TBFAMILIA F ON F.CODIGO = P.FAMILIA_ALTERNATIVA
            WHERE T.NFS_CONTROLE = :NFS_ID
            GROUP BY FAMILIA_ID, DESC_FAMILIA) X
            INTO :FAMILIA_ID, :DESC_FAMILIA, :VALOR
            DO BEGIN

                SUSPEND;
            END
        END


    END
    



END;