SET TERM ^ ;

CREATE OR ALTER procedure SPC_COTA_ORCAMENTARIA (
    DATA_1 date = null,
    DATA_2 date = null,
    COTA_GGF varchar(10) = null,
    COTA_VALIDA varchar(10) = null,
    COTA_ZERADA varchar(10) = null,
    FATURAMENTO varchar(10) = null,
    USUARIO_ID integer = null,
    ACERTO_INVENTARIO varchar(10) = null)
returns (
    TIPO varchar(10),
    ID bigint,
    CCUSTO varchar(50),
    CCUSTO_MASK varchar(50),
    CCUSTO_DESCRICAO varchar(150),
    MES integer,
    ANO integer,
    PERIODO_DESCRICAO varchar(150),
    CCONTABIL varchar(50),
    CCONTABIL_MASK varchar(50),
    CCONTABIL_DESCRICAO varchar(150),
    DESTAQUE varchar(10),
    TOTALIZA varchar(10),
    NOTIFICA varchar(10),
    BLOQUEIA varchar(10),
    VALOR_REAL numeric(18,4),
    VALOR_TRANSFERENCIA numeric(18,4),
    VALOR numeric(18,4),
    EXTRA numeric(18,4),
    TOTAL numeric(18,4),
    OUTROS numeric(18,4),
    UTIL numeric(18,4),
    PERC_UTIL numeric(18,4),
    SALDO numeric(18,4),
    CUSTO_SETOR numeric(18,4),
    OBSERVACAO_GERAL varchar(2000),
    FAT numeric(18,6),
    GRUPO varchar(150))
as
declare variable COTA_TIPO integer;
declare variable FATURAMENTO_VALOR numeric(18,6);

declare function SFN_FATURAMENTO(ANO integer, MES integer) returns numeric(15,4) as begin return(
    select sum(CF.VALOR) + sum(CF.VALOR_EXTRA)
    from TMPCOTA_FATURAMENTO CF
    where CF.ANO = :ANO and
          CF.MES = :MES);
end
begin

    IF ( DATA_1 IS NULL OR DATA_2 IS NULL ) THEN
    BEGIN
        DATA_1 = EXTRACT(YEAR FROM DATEADD(-1 MONTH TO CURRENT_DATE)) || '.' || EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) || '.01';
        DATA_2 = (DATEADD(1 MONTH TO CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'.'||EXTRACT(MONTH FROM CURRENT_DATE)||'.01' AS DATE))-1);
    END

    IF ( COALESCE(USUARIO_ID,0) IS NULL ) THEN
    BEGIN
        USUARIO_ID = COALESCE((
            SELECT FIRST 1 CODIGO
              FROM TBUSUARIO U
             WHERE U.USUARIO = CURRENT_USER),5);
    END


    IF ( COALESCE(FATURAMENTO,0) = 1 ) THEN
    BEGIN
        EXECUTE PROCEDURE SPI_COTA_FATURAMENTO(:DATA_1,:DATA_2);
    END



    FOR
        SELECT
            X.ID,
            X.CCUSTO,
            X.CCUSTO_MASK,
            X.CCUSTO_DESCRICAO,
            X.MES,
            X.ANO,
            X.PERIODO_DESCRICAO,
            X.CCONTABIL,    
            X.CCONTABIL_MASK,   
            X.CCONTABIL_DESCRICAO,
            X.DESTAQUE,
            X.TOTALIZA,
            X.NOTIFICA,
            X.BLOQUEIA,
            X.VALOR,
            X.EXTRA,
            X.TOTAL,
            X.OUTROS,
            X.UTIL,
            X.PERC_UTIL,
            X.SALDO,
            COALESCE(((UTIL+OUTROS)/iif(faturamento > 0, faturamento, 1))*100,0) CUSTO_SETOR,
            TRIM('COTA') TIPO,
            X.OBSERVACAO_GERAL,
            x.GRUPO
        FROM
            (SELECT
                A.ID,
                COALESCE(A.DESTAQUE,'0')DESTAQUE,
                COALESCE(A.TOTALIZA,'0')TOTALIZA,   
                COALESCE(A.BLOQUEIO,'0')BLOQUEIA,
                COALESCE(A.NOTIFICACAO,'0')NOTIFICA,
                A.CCUSTO,
                FN_CCUSTO_MASK(A.CCUSTO) CCUSTO_MASK,
                FN_CCUSTO_DESCRICAO(A.CCUSTO) CCUSTO_DESCRICAO,
                A.MES,
                A.ANO,
                SFN_FATURAMENTO(ANO,MES) as faturamento,
                TRIM(FN_MES_DESCRICAO(A.MES) || '/' || A.ANO) PERIODO_DESCRICAO,
                B.CONTA             CCONTABIL,
                UPPER(B.DESCRICAO)  CCONTABIL_DESCRICAO,
                FN_CCONTABIL_MASK(B.CONTA) CCONTABIL_MASK,
                A.VALOR,
                A.EXTRA,
                (A.VALOR+A.EXTRA) TOTAL,
                A.OUTROS,
                (A.VALOR+A.EXTRA-A.OUTROS-A.SALDO)UTIL,
                IIF(A.VALOR+A.EXTRA > 0, ((1-(A.SALDO/NULLIF((A.VALOR+A.EXTRA),0)))*100),
                IIF(A.VALOR+A.EXTRA = 0 AND A.SALDO < 0, 100, 0))  PERC_UTIL,
                A.SALDO,
                A.OBSERVACAO_GERAL,
                coalesce(b.grupo, 'OUTROS') as grupo

            FROM
                TBCCUSTO_COTA A,
                TBCONTACONTABIL B,
                TBUSUARIO_CCUSTO D

            WHERE             
                A.STATUSEXCLUSAO = '0'
            AND B.CONTA = A.CONTACONTABIL
            AND A.CCUSTO = D.CCUSTO
            AND D.USUARIO_ID = COALESCE(:USUARIO_ID,5) /* HAROLDO */
            AND A.DATA BETWEEN :DATA_1 AND :DATA_2
            AND IIF(:COTA_VALIDA = '1' AND :COTA_ZERADA = '1',(A.VALOR > 0 OR A.VALOR = 0 ),
                IIF(:COTA_ZERADA = '1',(A.VALOR = 0),
                IIF(:COTA_VALIDA = '1',(A.VALOR > 0),FALSE)))
            )X
   INTO
    ID                 ,
    CCUSTO             ,
    CCUSTO_MASK        ,
    CCUSTO_DESCRICAO   ,
    MES                ,
    ANO                ,
    PERIODO_DESCRICAO  ,
    CCONTABIL          ,
    CCONTABIL_MASK     ,
    CCONTABIL_DESCRICAO,
    DESTAQUE           ,
    TOTALIZA           ,
    NOTIFICA           ,
    BLOQUEIA           ,
    VALOR              ,
    EXTRA              ,
    TOTAL              ,
    OUTROS             ,
    UTIL               ,
    PERC_UTIL          ,
    SALDO              ,
    CUSTO_SETOR        ,
    TIPO               ,
    OBSERVACAO_GERAL   ,
    GRUPO
   DO
   BEGIN  

        VALOR_TRANSFERENCIA = COALESCE((
            SELECT SUM(IIF(L.NATUREZA='D',L.VALOR * -1,L.VALOR))
              FROM TBCCUSTO_COTA_LANCAMENTO L
             WHERE L.COTA_ORIGEM = 'COTA'
               AND L.COTA_ID = :ID
               AND L.OPERACAO = 'T'),0);
        VALOR_REAL = VALOR - VALOR_TRANSFERENCIA;

      SUSPEND;
   END


    IF ( COALESCE(COTA_GGF,'0') = '1' ) THEN
    BEGIN
        FOR
            SELECT
                99999 || MES || ANO || CCUSTO   ID,
                CCUSTO,
                CCUSTO_MASK,
                CCUSTO_DESCRICAO,
                MES,
                ANO,
                PERIODO_DESCRICAO,
                CCONTABIL,
                CCONTABIL_MASK,
                CCONTABIL_DESCRICAO,
                DESTAQUE,
                TOTALIZA,        
                0 NOTIFICA,
                0 BLOQUEIA,
                VALOR,
                EXTRA,
                VALOR TOTAL,
                0 OUTROS,
                UTIL,

                IIF(VALOR > 0, ((1-(SALDO/NULLIF((VALOR),0)))*100),
                IIF(VALOR = 0 AND SALDO < 0, 100, 0))  PERC_UTIL,

                SALDO,
                COALESCE(((UTIL)/SFN_FATURAMENTO(ANO,MES))*100,0) CUSTO_SETOR,
                TRIM('GGF') TIPO,
                '' OBSERVACAO_GERAL,
                GRUPO
            FROM (

                SELECT
                    GGF,
                    CCUSTO,
                    CCUSTO_MASK,
                    CCUSTO_DESCRICAO,
                    MES,
                    ANO,
                    PERIODO_DESCRICAO,
                    CCONTABIL,
                    CCONTABIL_MASK,
                    CCONTABIL_DESCRICAO,
                    DESTAQUE,
                    COALESCE(CAST(SUBSTRING(GGF FROM   1 FOR 30) AS NUMERIC(15,4)),0) VALOR,
                    COALESCE(CAST(SUBSTRING(GGF FROM  31 FOR 30) AS NUMERIC(15,4)),0) UTIL,
                    COALESCE(CAST(SUBSTRING(GGF FROM  61 FOR 30) AS NUMERIC(15,4)),0) PERC_UTIL,
                    COALESCE(CAST(SUBSTRING(GGF FROM  91 FOR 30) AS NUMERIC(15,4)),0) SALDO,
                    COALESCE(CAST(SUBSTRING(GGF FROM 121 FOR 30) AS NUMERIC(15,4)),0) EXTRA,
                    COALESCE(SUBSTRING(GGF FROM 151 FOR  1),0) TOTALIZA,
                    GRUPO
                FROM (
                    SELECT
                        (SELECT
                            LPAD(SUM(VALOR_COTA),30)||
                            LPAD(SUM(VALOR_UTILIZADO),30)||
                            LPAD(SUM(PERCENTUAL_UTILIZADO),30)||
                            LPAD(SUM(SALDO),30)||
                            LPAD(SUM(VALOR_CREDITO),30)||
                            MAX(TOTALIZA)
                        FROM SPC_COTA_GGF_V2(CCUSTO,CAST(ANO||'.'||MES||'.01' AS DATE),(DATEADD(1 MONTH TO CAST(ANO||'.'||MES||'.01' AS DATE))-1))) GGF,
                        CCUSTO,
                        CCUSTO_MASK,
                        CCUSTO_DESCRICAO,
                        MES,
                        ANO,
                        PERIODO_DESCRICAO,
                        CCONTABIL,
                        CCONTABIL_MASK,
                        CCONTABIL_DESCRICAO,
                        DESTAQUE,
                        GRUPO
                    FROM (

                        SELECT DISTINCT
                            Y.CCUSTO,
                            Y.CCUSTO_MASK,
                            Y.CCUSTO_DESCRICAO,
                            Y.MES,
                            Y.ANO,
                            Y.PERIODO_DESCRICAO,
                            Y.CCONTABIL,  
                            Y.CCONTABIL_MASK,  
                            Y.CCONTABIL_DESCRICAO,
                            Y.DESTAQUE,
                            Y.TOTALIZA,
                            y.GRUPO

                        FROM
                           (SELECT
                                X.ID,
                                X.CCUSTO,
                                X.CCUSTO_MASK,
                                X.CCUSTO_DESCRICAO,
                                X.MES,
                                X.ANO,
                                X.PERIODO_DESCRICAO,
                                X.CCONTABIL,    
                                X.CCONTABIL_MASK,   
                                X.CCONTABIL_DESCRICAO,
                                X.DESTAQUE,
                                X.TOTALIZA,
                                x.GRUPO
                            FROM
                                (SELECT
                                    A.ID,
                                    0 DESTAQUE,
                                    0 TOTALIZA,
                                    A.CCUSTO,
                                    FN_CCUSTO_MASK(A.CCUSTO) CCUSTO_MASK,
                                    FN_CCUSTO_DESCRICAO(A.CCUSTO) CCUSTO_DESCRICAO,
                                    A.MES,
                                    A.ANO,
                                    TRIM(FN_MES_DESCRICAO(A.MES) || '/' || A.ANO) PERIODO_DESCRICAO,
                                    99999999999999 CCONTABIL,
                                    'G.G.F./G.G.A.' CCONTABIL_DESCRICAO,
                                    '' CCONTABIL_MASK,
                                    'OUTROS' as grupo

                                FROM
                                    TBCCUSTO_COTA A,
                                    TBCONTACONTABIL B,
                                    TBCENTRO_DE_CUSTO C,
                                    TBUSUARIO_CCUSTO D

                                WHERE             
                                    A.STATUSEXCLUSAO = '0'
                                AND B.CONTA = A.CONTACONTABIL
                                AND A.CCUSTO = C.CODIGO
                                AND C.CODIGO = D.CCUSTO
                                AND D.USUARIO_ID = COALESCE(:USUARIO_ID,5) /* HAROLDO */
                                AND A.DATA BETWEEN :DATA_1 AND :DATA_2
                                AND IIF(:COTA_VALIDA = '1' AND :COTA_ZERADA = '1',(A.VALOR > 0 OR A.VALOR = 0 ),
                                    IIF(:COTA_ZERADA = '1',(A.VALOR = 0),
                                    IIF(:COTA_VALIDA = '1',(A.VALOR > 0),TRUE)))
                                )X)Y
                        ORDER BY
                            CCUSTO,
                            ANO,
                            MES,
                            CCONTABIL
                        )Z
                    
                    )W
            )K
        INTO
            ID                 ,
            CCUSTO             ,
            CCUSTO_MASK        ,
            CCUSTO_DESCRICAO   ,
            MES                ,
            ANO                ,
            PERIODO_DESCRICAO  ,
            CCONTABIL          ,
            CCONTABIL_MASK     ,
            CCONTABIL_DESCRICAO,
            DESTAQUE           ,
            TOTALIZA           , 
            NOTIFICA           ,
            BLOQUEIA           ,
            VALOR              ,
            EXTRA              ,
            TOTAL              ,
            OUTROS             ,
            UTIL               ,
            PERC_UTIL          ,
            SALDO              ,
            CUSTO_SETOR        ,
            TIPO               ,
            OBSERVACAO_GERAL   ,
            GRUPO
        DO
        BEGIN 

            VALOR_TRANSFERENCIA = COALESCE((
                SELECT SUM(IIF(L.NATUREZA='D',L.VALOR * -1,L.VALOR))
                  FROM TBCCUSTO_COTA_LANCAMENTO L
                 WHERE L.COTA_ORIGEM = 'GGF'
                   AND L.COTA_ID = :ID
                   AND L.OPERACAO = 'T'),0);
            VALOR_REAL = VALOR - VALOR_TRANSFERENCIA;
           SUSPEND;
        END
    END


    IF ( COALESCE(ACERTO_INVENTARIO,'0') = '1' ) THEN
    BEGIN
        FOR
            SELECT
                ID,
                CCUSTO,
                CCUSTO_MASK,
                CCUSTO_DESCRICAO,
                MES,
                ANO,
                PERIODO_DESCRICAO,
                CCONTABIL,
                CCONTABIL_MASK,
                CCONTABIL_DESCRICAO,
                DESTAQUE,
                TOTALIZA,
                NOTIFICA,
                BLOQUEIA,
                VALOR,
                EXTRA,
                TOTAL,
                OUTROS,
                UTIL,
                PERC_UTIL,
                SALDO,
                COALESCE(((UTIL)/SFN_FATURAMENTO(ANO,MES))*100,0) CUSTO_SETOR,
                TIPO,
                OBSERVACAO_GERAL,
                GRUPO
            FROM (
                SELECT
                ID,
                CCUSTO,
                FN_CCUSTO_MASK(Y.CCUSTO) CCUSTO_MASK,
                FN_CCUSTO_DESCRICAO(Y.CCUSTO) CCUSTO_DESCRICAO,
                MES,
                ANO,
                        TRIM(FN_MES_DESCRICAO(Y.MES)||'/'||Y.ANO) PERIODO_DESCRICAO,
                999999999999999 CCONTABIL,
                '' CCONTABIL_MASK,
                'AJUSTES DE INVENTÁRIO' CCONTABIL_DESCRICAO,
                0 DESTAQUE,
                0 TOTALIZA,
                0 NOTIFICA,
                0 BLOQUEIA,
                0 VALOR,
                0 EXTRA,
                0 TOTAL,
                0 OUTROS,
                UTIL,
                UTIL / SUM(UTIL) OVER (PARTITION BY MES,ANO) * 100 PERC_UTIL,
                UTIL SALDO,
                'INV' TIPO,
                '' OBSERVACAO_GERAL,
                GRUPO
                
                
                FROM (
                    SELECT
                        999999999 || MES || ANO || CCUSTO ID,
                        X.CCUSTO,
                        X.ANO,
                        X.MES,
                        CAST(SUM(UTIL) AS NUMERIC(15,2)) UTIL,
                        GRUPO
                    FROM (
                        SELECT
                            CC.CCUSTO_CONTABILIZACAO CCUSTO,
                            EXTRACT (YEAR FROM A.DATA) ANO,
                            EXTRACT (MONTH FROM A.DATA) MES,
                            A.QUANTIDADE *
                                (SELECT FIRST 1 S.CUSTO_MEDIO
                                   FROM TBESTOQUE_SALDO_DIARIO S
                                  WHERE S.ESTABELECIMENTO_CODIGO = A.ESTABELECIMENTO_CODIGO
                                    AND S.LOCALIZACAO_CODIGO     = A.LOCALIZACAO_CODIGO
                                    AND S.PRODUTO_CODIGO         = A.PRODUTO_CODIGO
                                    AND S.DATA = A.DATA) * (IIF(A.TIPO='E',-1.000,1.000)) UTIL,

                            'Ajustes de Inventário' as grupo
                        FROM
                            TBESTOQUE_TRANSACAO_ITEM A,
                            TBPRODUTO P,
                            TBOPERACAO O,
                            TBCENTRO_DE_CUSTO CC,
                            TBUSUARIO_CCUSTO US
                    
                        WHERE
                            A.PRODUTO_CODIGO         = P.CODIGO
                        AND A.OPERACAO_CODIGO        = O.CODIGO
                        AND O.ACERTO                 = '1'
                        AND P.INVENTARIO             = '1'
                        AND A.CENTRO_DE_CUSTO        = CC.CODIGO
                        AND CC.CCUSTO_CONTABILIZACAO = US.CCUSTO
                        AND US.USUARIO_ID = COALESCE(:USUARIO_ID,5) /* HAROLDO */
                        AND A.DATA BETWEEN :DATA_1 AND :DATA_2
                
                        ) X
                    GROUP BY X.CCUSTO, X.ANO, X.MES, x.grupo
                ) Y
            
                ) Z                       
--                WHERE
--                    IIF(:COTA_VALIDA = '1' AND :COTA_ZERADA = '1',(Z.VALOR > 0 OR Z.VALOR = 0 ),
--                    IIF(:COTA_ZERADA = '1',(Z.VALOR = 0),
--                    IIF(:COTA_VALIDA = '1',(Z.VALOR > 0),TRUE)))
        INTO
            ID                 ,
            CCUSTO             ,
            CCUSTO_MASK        ,
            CCUSTO_DESCRICAO   ,
            MES                ,
            ANO                ,
            PERIODO_DESCRICAO  ,
            CCONTABIL          ,
            CCONTABIL_MASK     ,
            CCONTABIL_DESCRICAO,
            DESTAQUE           ,
            TOTALIZA           , 
            NOTIFICA           ,
            BLOQUEIA           ,
            VALOR              ,
            EXTRA              ,
            TOTAL              ,
            OUTROS             ,
            UTIL               ,
            PERC_UTIL          ,
            SALDO              ,
            CUSTO_SETOR        ,
            TIPO               ,
            OBSERVACAO_GERAL   ,
            GRUPO
        DO
        BEGIN          
            VALOR_REAL = VALOR;
            VALOR_TRANSFERENCIA = 0;
           SUSPEND;
        END
    END


    IF ( COALESCE(FATURAMENTO,'0') = '1' ) THEN
    BEGIN
        FOR
            SELECT
                9999999 || MES || ANO   ID,
                ''CCUSTO,
                '' CCUSTO_MASK,
                'FATURAMENTO' CCUSTO_DESCRICAO,
                MES,
                ANO,
                PERIODO_DESCRICAO,
                '' CCONTABIL,
                ''CCONTABIL_MASK,
                '' CCONTABIL_DESCRICAO,
                0 DESTAQUE,
                0 TOTALIZA,
                0 NOTIFICA,
                0 BLOQUEIA,
                VALOR,
                EXTRA,
                EXTRA+VALOR TOTAL,
                OUTROS,
                0 UTIL,

                NULL PERC_UTIL,
                NULL SALDO,
                NULL CUSTO_SETOR,
                TRIM('FAT') TIPO,
                '' OBSERVACAO_GERAL,
                GRUPO
            FROM (SELECT CT.ANO,
                         CT.MES,
                         CT.PERIODO_DESCRICAO,
                         CT.VALOR,
                         CT.VALOR_REDUCAO OUTROS,
                         CT.VALOR_EXTRA EXTRA,
                         'FATURAMENTO' as grupo
                    FROM TMPCOTA_FATURAMENTO CT)K
        INTO
            ID                 ,
            CCUSTO             ,
            CCUSTO_MASK        ,
            CCUSTO_DESCRICAO   ,
            MES                ,
            ANO                ,
            PERIODO_DESCRICAO  ,
            CCONTABIL          ,
            CCONTABIL_MASK     ,
            CCONTABIL_DESCRICAO,
            DESTAQUE           ,
            TOTALIZA           , 
            NOTIFICA           ,
            BLOQUEIA           ,
            VALOR              ,
            EXTRA              ,
            TOTAL              ,
            OUTROS             ,
            UTIL               ,
            PERC_UTIL          ,
            SALDO              ,
            CUSTO_SETOR        ,
            TIPO               ,
            OBSERVACAO_GERAL   ,
            GRUPO
        DO
        BEGIN       
            VALOR_REAL = VALOR;
            VALOR_TRANSFERENCIA = 0;
           SUSPEND;
        END
    END
end
^

SET TERM ; ^

