CREATE OR REPLACE PROCEDURE DASHBOARD_FIN_FLUXO_CAIXA (
    P_DTSALDO IN VARCHAR2 DEFAULT SYSDATE
) AS
    V_DTINICIAL   DATE;
    V_DTFINAL     DATE;
BEGIN
    DELETE FROM PDCFLUXO;

    BEGIN
        SELECT
            ADD_MONTHS(TRUNC(SYSDATE),-12) AS DTINICIAL,
            SYSDATE AS  DTFINAL
            INTO   V_DTINICIAL,
            V_DTFINAL
        FROM
            DUAL;
    END;

    FOR C_DATA IN (
        SELECT 
            E.CDEMPRESA,
            E.NRORG,
            DT.DTDATA
        FROM 
            DATA DT, 
            EMPRESA E 
        WHERE 
            DTDATA BETWEEN V_DTINICIAL AND V_DTFINAL
    ) LOOP
        INSERT INTO TMP_PDCFLUXO (
            NRORG,
            CDEMPRESA,
            DATAFLUXO
        ) VALUES (
            C_DATA.NRORG,
            C_DATA.CDEMPRESA,
            C_DATA.DTDATA
        );

    END LOOP;

    FOR C_VRPAGAR IN (
        SELECT
             CDEMPRESA,
             DTSAIDA,
             SUM(VRPAGAR) AS VRPAGAR,
             NRORG
         FROM
             (
                 SELECT
                     BO.CDEMPRESA,
                     BO.DTVENBOLPAG   AS DTSAIDA,
                     SUM(BO.VRFINALBPAG) AS VRPAGAR,
                     BO.NRORG
                 FROM
                     BOLETPAGAR BO
                 WHERE
                     BO.DTVENBOLPAG BETWEEN V_DTINICIAL AND V_DTFINAL
                     AND BO.IDSTATBPAG IN (
                         'A',
                         'P'
                     )
                     AND EXISTS (
                         SELECT
                             1
                         FROM
                             NOTAFISCAL NF,
                             CONTAPAGAR CP
                         WHERE
                             NF.CDFILIAL = CP.CDFILIAL
                             AND NF.NRLANCTONF = CP.NRLANCTONF
                             AND NOT ( NF.IDSTATUS IN (
                                 'C',
                                 'G',
                                 'P',
                                 'R',
                                 'V'
                             ) )
                             AND CP.CDFILIBPAG = BO.CDFILIAL
                             AND CP.NRLANBOLPAG = BO.NRLANBOLPAG
                             AND CP.NRORG = BO.NRORG
                     )
                 GROUP BY
                     BO.CDEMPRESA,
                     BO.DTVENBOLPAG,
                     BO.NRORG
                 UNION ALL
                 SELECT
                     BO.CDEMPRESA,
                     BO.DTVENBOLPAG   AS DTSAIDA,
                     SUM(BO.VRFINALBPAG) AS VRPAGAR,
                     BO.NRORG
                 FROM
                     BOLETPAGAR BO
                 WHERE
                     BO.DTVENBOLPAG BETWEEN V_DTINICIAL AND V_DTFINAL
                     AND BO.IDSTATBPAG IN (
                         'A',
                         'P'
                     )
                     AND BO.CDREDUCONT IS NULL
                     AND EXISTS (
                         SELECT
                             1
                         FROM
                             NOTAFISCAL NF,
                             CONTAPAGAR CP
                         WHERE
                             NF.CDFILIAL = CP.CDFILIAL
                             AND NF.NRLANCTONF = CP.NRLANCTONF
                             AND NOT ( NF.IDSTATUS IN (
                                 'C',
                                 'G',
                                 'P',
                                 'R',
                                 'V'
                             ) )
                             AND CP.CDFILIBPAG = BO.CDFILIAL
                             AND CP.NRLANBOLPAG = BO.NRLANBOLPAG
                             AND CP.NRORG = BO.NRORG
                     )
                 GROUP BY
                     BO.CDEMPRESA,
                     BO.DTVENBOLPAG,
                     BO.NRORG
             )
         GROUP BY
             CDEMPRESA,
             DTSAIDA,
             NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRPAGAR = C_VRPAGAR.VRPAGAR
            WHERE
            CDEMPRESA = C_VRPAGAR.CDEMPRESA
            AND DATAFLUXO = C_VRPAGAR.DTSAIDA;
    END LOOP;

    FOR C_VRPAGPREV IN (
        SELECT
             NF.CDEMPRESA,
             CP.DTATUAVENPAG   AS DTSAIDAPREV,
             SUM(CP.VRATUAPAG) AS VRPAGPREV,
             CP.NRORG
         FROM
             NOTAFISCAL NF,
             CONTAPAGAR CP
         WHERE
             NF.IDSTATUS = 'V'
             AND NF.CDFILIAL = CP.CDFILIAL
             AND NF.NRLANCTONF = CP.NRLANCTONF
             AND NF.NRORG = CP.NRORG
             AND CP.DTATUAVENPAG BETWEEN V_DTINICIAL AND V_DTFINAL
         GROUP BY
             NF.CDEMPRESA,
             CP.DTATUAVENPAG,
             CP.NRORG    
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRPAGPREV = C_VRPAGPREV.VRPAGPREV
            WHERE
            CDEMPRESA = C_VRPAGPREV.CDEMPRESA
            AND DATAFLUXO = C_VRPAGPREV.DTSAIDAPREV;
    END LOOP;

    FOR C_VRPAGEFET IN (
        SELECT
             BO.CDEMPRESA,
             BP.DTBAIXAPAG   AS DTSAIDAEFET,
             SUM(BO.VRFINALBPAG) AS VRPAGEFET,
             BP.NRORG
         FROM
             BOLETPAGAR BO,
             BAIXAPAGA BP
         WHERE
             BP.DTBAIXAPAG BETWEEN V_DTINICIAL AND V_DTFINAL
             AND BO.NRBAIXAPAG = BP.NRBAIXAPAG
             AND BO.CDFILIBAIPAG = BP.CDFILIAL
             AND BO.NRORG = BP.NRORG
             AND BP.IDSTATUSBPAG = 'E'
             AND EXISTS (
                 SELECT
                     1
                 FROM
                     NOTAFISCAL NF,
                     CONTAPAGAR CP
                 WHERE
                     NF.CDFILIAL = CP.CDFILIAL
                     AND NF.NRLANCTONF = CP.NRLANCTONF
                     AND NOT ( NF.IDSTATUS IN (
                         'C',
                         'G',
                         'P',
                         'R',
                         'V'
                     ) )
                     AND CP.CDFILIBPAG = BO.CDFILIAL
                     AND CP.NRLANBOLPAG = BO.NRLANBOLPAG
                     AND CP.NRORG = BO.NRORG
             )
         GROUP BY
             BO.CDEMPRESA,
             BP.DTBAIXAPAG,
             BP.NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRPAGEFET = C_VRPAGEFET.VRPAGEFET
            WHERE
            CDEMPRESA = C_VRPAGEFET.CDEMPRESA
            AND DATAFLUXO = C_VRPAGEFET.DTSAIDAEFET;
    END LOOP;

    FOR C_VRRECEBER IN (
        SELECT
             CDEMPRESA,
             DTENTRADA,
             SUM(VRRECEBER) VRRECEBER,
             NRORG
         FROM
             (
                 SELECT
                     BO.CDEMPRESA,
                     BO.DTVENBOLREC   AS DTENTRADA,
                     SUM(BO.VRFINALBREC) AS VRRECEBER,
                     BO.NRORG
                 FROM
                     BOLETRECEBER BO
                 WHERE
                     BO.DTVENBOLREC BETWEEN V_DTINICIAL AND V_DTFINAL
                     AND BO.IDSTATBREC IN (
                         'A',
                         'P',
                         'E'
                     )
                     AND EXISTS (
                         SELECT
                             1
                         FROM
                             CONTARECEBER CR,
                             NOTAFISCAL NF
                         WHERE
                             CR.CDFILIAL = NF.CDFILIAL
                             AND CR.NRLANCTONF = NF.NRLANCTONF
                             AND NOT ( NF.IDSTATUS IN (
                                 'C',
                                 'G',
                                 'P',
                                 'R',
                                 'V'
                             ) )
                             AND BO.CDFILIAL = CR.CDFILIBOLREC
                             AND BO.NRLANBOLREC = CR.NRLANBOLREC
                             AND BO.NRORG = CR.NRORG
                     )
                 GROUP BY
                     BO.CDEMPRESA,
                     BO.DTVENBOLREC,
                     BO.NRORG
                 UNION ALL
                 SELECT
                     BO.CDEMPRESA,
                     BO.DTVENBOLREC   AS DTENTRADA,
                     SUM(BO.VRFINALBREC) AS VRRECEBER,
                     BO.NRORG
                 FROM
                     BOLETRECEBER BO
                 WHERE
                     BO.DTVENBOLREC BETWEEN V_DTINICIAL AND V_DTFINAL
                     AND BO.IDSTATBREC IN (
                         'A',
                         'P',
                         'E'
                     )
                     AND BO.CDREDUCONT IS NULL
                     AND EXISTS (
                         SELECT
                             1
                         FROM
                             CONTARECEBER CR,
                             NOTAFISCAL NF
                         WHERE
                             CR.CDFILIAL = NF.CDFILIAL
                             AND CR.NRLANCTONF = NF.NRLANCTONF
                             AND NOT ( NF.IDSTATUS IN (
                                 'C',
                                 'G',
                                 'P',
                                 'R',
                                 'V'
                             ) )
                             AND BO.CDFILIAL = CR.CDFILIBOLREC
                             AND BO.NRLANBOLREC = CR.NRLANBOLREC
                             AND BO.NRORG = CR.NRORG
                     )
                 GROUP BY
                     BO.CDEMPRESA,
                     BO.DTVENBOLREC,
                     BO.NRORG
             )
         GROUP BY
             CDEMPRESA,
             DTENTRADA,
             NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRRECEBER = C_VRRECEBER.VRRECEBER
            WHERE
            CDEMPRESA = C_VRRECEBER.CDEMPRESA
            AND DATAFLUXO = C_VRRECEBER.DTENTRADA;
    END LOOP;

    FOR C_VRRECPREV IN (
        SELECT
             NF.CDEMPRESA,
             CR.DTATUAVENREC   AS DTENTRPREV,
             SUM(CR.VRATUAREC) AS VRRECPREV,
             CR.NRORG
         FROM
             NOTAFISCAL NF,
             CONTARECEBER CR
         WHERE
             NF.IDSTATUS = 'V'
             AND NF.CDFILIAL = CR.CDFILIAL
             AND NF.NRLANCTONF = CR.NRLANCTONF
             AND NF.NRORG = CR.NRORG
             AND CR.DTATUAVENREC BETWEEN V_DTINICIAL AND V_DTFINAL
         GROUP BY
             NF.CDEMPRESA,
             CR.DTATUAVENREC,
             CR.NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRRECPREV = C_VRRECPREV.VRRECPREV
            WHERE
            CDEMPRESA = C_VRRECPREV.CDEMPRESA
            AND DATAFLUXO = C_VRRECPREV.DTENTRPREV;
    END LOOP;

    FOR C_VRRECEFET IN (
        SELECT
             BO.CDEMPRESA,
             BR.DTBAIXAREC   AS DTENTREFET,
             SUM(BO.VRFINALBREC) AS VRRECEFET,
             BO.NRORG
         FROM
             BOLETRECEBER BO,
             BAIXARECEBIDA BR
         WHERE
             TRUNC(BR.DTBAIXAREC) BETWEEN V_DTINICIAL AND V_DTFINAL
             AND BO.NRBAIXAREC = BR.NRBAIXAREC
             AND BO.CDFILIBAIREC = BR.CDFILIAL
             AND BO.NRORG = BR.NRORG
             AND BR.IDSTATUSBREC = 'E'
             AND EXISTS (
                 SELECT
                     1
                 FROM
                     NOTAFISCAL NF,
                     CONTARECEBER CR
                 WHERE
                     NF.CDFILIAL = CR.CDFILIAL
                     AND NF.NRLANCTONF = CR.NRLANCTONF
                     AND NOT ( NF.IDSTATUS IN (
                         'C',
                         'G',
                         'P',
                         'R',
                         'V'
                     ) )
                     AND CR.CDFILIBOLREC = BO.CDFILIAL
                     AND CR.NRLANBOLREC = BO.NRLANBOLREC
                     AND CR.NRORG = BO.NRORG
             )
         GROUP BY
             BO.CDEMPRESA,
             BR.DTBAIXAREC,
             BO.NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRRECEFET = C_VRRECEFET.VRRECEFET
            WHERE
            CDEMPRESA = C_VRRECEFET.CDEMPRESA
            AND DATAFLUXO = C_VRRECEFET.DTENTREFET;
    END LOOP;

    FOR C_VRCREDITO IN (
        SELECT
             C.CDEMPRESA,
             DTMOVBANC,
             ROUND(SUM(VRMOVBANC),2) VRCREDITO,
             M.NRORG
         FROM
             MOVBANCARIO M,
             CONTCORR C
         WHERE
             M.IDORIGMBANC IN (
                 'C',
                 'T'
             )
             AND M.IDMOVBANC = 'C'
             AND M.DTMOVBANC BETWEEN V_DTINICIAL AND V_DTFINAL
             AND M.CDREDUCONT = C.CDREDUCONT
         GROUP BY
             C.CDEMPRESA,
             DTMOVBANC,
             M.NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRCREDITO = C_VRCREDITO.VRCREDITO
            WHERE
            CDEMPRESA = C_VRCREDITO.CDEMPRESA
            AND DATAFLUXO = C_VRCREDITO.DTMOVBANC;
    END LOOP;

    FOR C_VRDEBITO IN (
        SELECT
             C.CDEMPRESA,
             DTMOVBANC,
             ROUND(SUM(VRMOVBANC),2) VRDEBITO,
             M.NRORG
         FROM
             MOVBANCARIO M,
             CONTCORR C
         WHERE
             M.IDORIGMBANC IN (
                 'D',
                 'T'
             )
             AND M.IDMOVBANC = 'D'
             AND M.DTMOVBANC BETWEEN V_DTINICIAL AND V_DTFINAL
             AND M.CDREDUCONT = C.CDREDUCONT
         GROUP BY
             C.CDEMPRESA,
             DTMOVBANC,
             M.NRORG
    ) LOOP
        UPDATE TMP_PDCFLUXO SET VRDEBITO = C_VRDEBITO.VRDEBITO
            WHERE
            CDEMPRESA = C_VRDEBITO.CDEMPRESA
            AND DATAFLUXO = C_VRDEBITO.DTMOVBANC;
    END LOOP;

    FOR C_SALDO_CONTACORR IN (
        SELECT
             CC.CDEMPRESA,
             SUM(SALDO_CONTACORR(CC.CDREDUCONT,V_DTINICIAL) ) SALDO_CONTACORR
         FROM
             CONTCORR CC
         GROUP BY
            CC.CDEMPRESA
    ) LOOP
        UPDATE TMP_PDCFLUXO SET SALDO_CONTACORR = C_SALDO_CONTACORR.SALDO_CONTACORR
            WHERE
            CDEMPRESA = C_SALDO_CONTACORR.CDEMPRESA;
    END LOOP;

    FOR C_FLUXO IN (
        SELECT
             NRORG,
             CDEMPRESA,
             TO_CHAR(DATAFLUXO,'MONTH yyyy') AS MENSAL,
             TO_CHAR(DATAFLUXO,'YYYY-MM') AS MENSAL_ORDER,
             SUM(VRPAGAR) AS TVRPAGAR,
             SUM(VRPAGPREV) AS TVRPAGPREV,
             SUM(VRPAGEFET) AS TVRPAGEFET,
             SUM(VRRECEBER) AS TVRRECEBER,
             SUM(VRRECPREV) AS TVRRECPREV,
             SUM(VRRECEFET) AS TVRRECEFET,
             SUM(VRCREDITO) AS TVRCREDITO,
             SUM(VRDEBITO) AS TVRDEBITO,
             SUM(SALDO_CONTACORR) AS TSALDO_CONTACORR,
             SUM(SALDODIA) AS TSALDODIA,
             SUM(SALDODIAREAL) AS TSALDODIAREAL,
             SUM(ACUMULATIVO) AS TACUMULATIVO,
             SUM(ACUMULATIVOREAL) AS TACUMULATIVOREAL,
             SUM(CAPITALGIRO) AS TCAPITALGIRO
         FROM
             (
                 SELECT
                     CDEMPRESA,
                     DATAFLUXO,
                     VRPAGAR,
                     VRPAGPREV,
                     VRPAGEFET,
                     VRRECEBER,
                     VRRECPREV,
                     VRRECEFET,
                     VRCREDITO,
                     VRDEBITO,
                     SALDO_CONTACORR,
                     SALDODIA,
                     SALDODIAREAL,
                     SUM(DECODE(TO_CHAR(DATAFLUXO,'DD/MM/YYYY'),V_DTINICIAL,SALDO_CONTACORR + SALDODIA,SALDODIA) ) OVER(
                     ORDER BY
                         DATAFLUXO
                     ) ACUMULATIVO,
                     SUM(DECODE(TO_CHAR(DATAFLUXO,'DD/MM/YYYY'),V_DTINICIAL,SALDO_CONTACORR + SALDODIAREAL,SALDODIAREAL) 
) OVER(
                     ORDER BY
                         DATAFLUXO
                     ) ACUMULATIVOREAL,
                     CAPITALGIRO,
                     NRORG
                 FROM
                     (
                         SELECT
                             CDEMPRESA,
                             DATAFLUXO,
                             VRPAGAR,
                             VRPAGPREV,
                             VRPAGEFET,
                             VRRECEBER,
                             VRRECPREV,
                             VRRECEFET,
                             VRCREDITO,
                             VRDEBITO,
                             SALDO_CONTACORR,
                             ( ( VRRECEBER + VRRECPREV + VRRECEFET + VRCREDITO ) - ( VRPAGAR + VRPAGPREV + VRPAGEFET + 
VRDEBITO ) ) SALDODIA,
                             ( ( VRRECEFET + VRCREDITO ) - ( VRPAGEFET + VRDEBITO ) ) SALDODIAREAL,
                             ACUMULATIVO,
                             ACUMULATIVOREAL,
                             CAPITALGIRO,
                             NRORG
                         FROM
                             (
                                 SELECT
                                     CDEMPRESA,
                                     DATAFLUXO,
                                     SUM(ROUND(NVL(VRPAGAR,0),2) ) AS VRPAGAR,
                                     SUM(ROUND(NVL(VRPAGPREV,0),2) ) AS VRPAGPREV,
                                     SUM(ROUND(NVL(VRPAGEFET,0),2) ) AS VRPAGEFET,
                                     SUM(ROUND(NVL(VRRECEBER,0),2) ) AS VRRECEBER,
                                     SUM(ROUND(NVL(VRRECPREV,0),2) ) AS VRRECPREV,
                                     SUM(ROUND(NVL(VRRECEFET,0),2) ) AS VRRECEFET,
                                     SUM(ROUND(NVL(VRCREDITO,0),2) ) AS VRCREDITO,
                                     SUM(ROUND(NVL(VRDEBITO,0),2) ) AS VRDEBITO,
                                     SUM(ROUND(NVL(SALDO_CONTACORR,0),2) ) AS SALDO_CONTACORR,
                                     0 AS SALDODIA,
                                     0 AS SALDODIAREAL,
                                     0 AS ACUMULATIVO,
                                     0 AS ACUMULATIVOREAL,
                                     0 AS CAPITALGIRO,
                                     NRORG
                                 FROM
                                     TMP_PDCFLUXO
                                 GROUP BY
                                     CDEMPRESA,
                                     DATAFLUXO,
                                     NRORG
                                 ORDER BY
                                     DATAFLUXO
                             ) A
                     ) B
             )
         GROUP BY
             NRORG,
             CDEMPRESA,
             TO_CHAR(DATAFLUXO,'MONTH yyyy'),
             TO_CHAR(DATAFLUXO,'YYYY-MM')
         ORDER BY
             CDEMPRESA, 
             MENSAL_ORDER
    ) LOOP
        INSERT INTO PDCFLUXO (
            NRORG,
            CDEMPRESA,
            MENSAL,
            MENSAL_ORDER,
            TVRPAGAR,
            TVRPAGPREV,
            TVRPAGEFET,
            TVRRECEBER,
            TVRRECPREV,
            TVRRECEFET,
            TVRCREDITO,
            TVRDEBITO,
            TSALDO_CONTACORR,
            TSALDODIA,
            TSALDODIAREAL,
            TACUMULATIVO,
            TACUMULATIVOREAL,
            TCAPITALGIRO
        ) VALUES (
            C_FLUXO.NRORG,
            C_FLUXO.CDEMPRESA,
            C_FLUXO.MENSAL,
            C_FLUXO.MENSAL_ORDER,
            C_FLUXO.TVRPAGAR,
            C_FLUXO.TVRPAGPREV,
            C_FLUXO.TVRPAGEFET,
            C_FLUXO.TVRRECEBER,
            C_FLUXO.TVRRECPREV,
            C_FLUXO.TVRRECEFET,
            C_FLUXO.TVRCREDITO,
            C_FLUXO.TVRDEBITO,
            C_FLUXO.TSALDO_CONTACORR,
            C_FLUXO.TSALDODIA,
            C_FLUXO.TSALDODIAREAL,
            C_FLUXO.TACUMULATIVO,
            C_FLUXO.TACUMULATIVOREAL,
            C_FLUXO.TCAPITALGIRO
        );

    END LOOP;

    DELETE FROM TMP_PDCFLUXO;

END;
/