SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPC_FATURAMENTO_CN (
    CENTRO_NEGOCIO_ID INTEGER,
    DATA_INICIAL DATE,
    DATA_FINAL DATE)
RETURNS (
    MODELO_ID INTEGER,
    FAMILIA_ID INTEGER,
    PERFIL_MODELO VARCHAR(100),
    LINHA_ID INTEGER,
    VALOR_TOTAL NUMERIC(15,2))
AS
DECLARE VARIABLE VMODELO_ID INTEGER;
DECLARE VARIABLE VFAMILIA_ID INTEGER;
DECLARE VARIABLE VPERFIL_MODELO VARCHAR(100);
DECLARE VARIABLE VLINHA_ID INTEGER;
DECLARE VARIABLE VVALOR_TOTAL NUMERIC(15,2);
BEGIN
    FOR
        SELECT
            MODELO_ID,
            FAMILIA_ID,
            PERFIL_MODELO,
            LINHA_ID,
            VALOR_TOTAL
    FROM (
        SELECT
            MODELO_ID,
            FAMILIA_ID,
            PERFIL_MODELO,
            LINHA_ID,
            SUM(VALOR_TOTAL) VALOR_TOTAL
        FROM (
            SELECT
                MODELO_ID,
                FAMILIA_ID,
                PERFIL_MODELO,
                LINHA_ID,
                CAST(SUM(VALOR_TOTAL) AS NUMERIC(15,2)) VALOR_TOTAL
            FROM (
                SELECT
                    M.CODIGO AS MODELO_ID,
                    F.FAMILIA_ID,
                    M.CLASSIFICACAO AS PERFIL_MODELO,
                    P.LINHA_CODIGO AS LINHA_ID,
                    CAST((CAST(B.QUANTIDADE AS DOUBLE PRECISION)*(CAST(B.VALOR_UNITARIO AS DOUBLE PRECISION)+CAST(COALESCE(B.MARGEM,1) AS DOUBLE PRECISION))) AS DOUBLE PRECISION) VALOR_TOTAL
                  FROM TBNFS A, TBNFS_ITEM B, TBPRODUTO P, TBMODELO M, TBOPERACAO O, TBCENTRO_NEGOCIO_FAMILIA F
                 WHERE A.CONTROLE = B.NFS_CONTROLE
                   AND A.DATA_EMISSAO BETWEEN :DATA_INICIAL AND :DATA_FINAL
                   AND B.PRODUTO_CODIGO = P.CODIGO
                   AND P.MODELO_CODIGO = M.CODIGO
                   AND B.OPERACAO_CODIGO = O.CODIGO
                   AND O.CONTROLE_FATURAMENTO = '1'
                   AND F.FAMILIA_ID = P.FAMILIA_ALTERNATIVA
                   AND ((A.NATUREZA = 1 AND A.SITUACAO = 2) OR
                        (A.NATUREZA = 2 AND A.SITUACAO = 1))
                   AND F.CENTRO_NEGOCIO_ID = :CENTRO_NEGOCIO_ID
                  UNION
                SELECT
                    M.CODIGO AS MODELO_ID,
                    F.FAMILIA_ID,
                    M.CLASSIFICACAO AS PERFIL_MODELO,
                    P.LINHA_CODIGO AS LINHA_ID,
                    CAST((CAST(B.QUANTIDADE AS DOUBLE PRECISION)*(CAST(B.VALOR_UNITARIO AS DOUBLE PRECISION))) AS DOUBLE PRECISION) VALOR_TOTAL
                  FROM VWMFE A, VWMFE_ITEM B, TBPRODUTO P, TBMODELO M, TBOPERACAO O, TBCENTRO_NEGOCIO_FAMILIA F
                 WHERE A.MFE_ID = B.MFE_ID
                   AND A.DATA_EMISSAO BETWEEN :DATA_INICIAL AND :DATA_FINAL
                   AND B.PRODUTO_CODIGO = P.CODIGO
                   AND P.MODELO_CODIGO = M.CODIGO
                   AND B.OPERACAO_CODIGO = O.CODIGO
                   AND O.CONTROLE_FATURAMENTO = '1'
                   AND F.FAMILIA_ID = P.FAMILIA_ALTERNATIVA
                   AND ((A.NATUREZA = 1 AND A.SITUACAO = 2) OR
                        (A.NATUREZA = 2 AND A.SITUACAO = 1))
                   AND F.CENTRO_NEGOCIO_ID = :CENTRO_NEGOCIO_ID
                ) X
                GROUP BY MODELO_ID, FAMILIA_ID, PERFIL_MODELO, LINHA_ID
            )
            GROUP BY MODELO_ID, FAMILIA_ID, PERFIL_MODELO, LINHA_ID
        ) INTO :VMODELO_ID, :VFAMILIA_ID, :VPERFIL_MODELO, :VLINHA_ID, :VVALOR_TOTAL
        DO BEGIN
            :MODELO_ID              = :VMODELO_ID;
            :FAMILIA_ID             = :VFAMILIA_ID;
            :PERFIL_MODELO          = :VPERFIL_MODELO;
            :LINHA_ID               = :VLINHA_ID;
            :VALOR_TOTAL            = :VVALOR_TOTAL;

            SUSPEND;

        END

END
^

SET TERM ; ^

