CREATE OR REPLACE PROCEDURE DASHBOARD_FIS_COUNT   
    AS
BEGIN
    DELETE FROM FISPDCCONT;

    FOR COUNT_FIS IN (
        SELECT
            NRORG,
            CDEMPRESA,
            DTENTRVENDA AS DTFISPDCCONT,
            'CUPOM_VENDAS' AS IDFISPDCCONT
        FROM
            (
                SELECT DISTINCT
                    V.NRORG,
                    V.CDFILIAL,
                    V.CDEMPRESA,
                    V.DTENTRVENDA,
                    V.IDAPROVACAONFCE,
                    V.CDCAIXA,
                    '' AS CDINSCESTA,
                    DECODE(IDTPEMISVEND,'N','NFC-E','SAT') AS TIPO,
                    DECODE(TU.DTFECHCAIX,NULL,'Aberto','Fechado') AS STATUS
                FROM
                    VENDA V,
                    FILIAL F,
                    PARAVEND PA,
                    TURCAIXA TU
                WHERE
                    V.IDTPEMISVEND IN (
                        'N',
                        'S'
                    )
                    AND V.CDFILIAL = F.CDFILIAL
                    AND V.IDTPAMBNFCE = '1'
                    AND V.IDSTATUSNFCE IN (
                        'A',
                        'C'
                    )
                    AND V.IDSITUVENDA = 'O'
                    AND F.CDEMPRESA = V.CDEMPRESA
                    AND V.IDAPROVACAONFCE IN (
                        'P',
                        'E'
                    )
                    AND V.CDFILIAL = PA.CDFILIAL
                    AND PA.IDPREVALIDCUPOM = 'S'
                    AND TU.CDFILIAL = V.CDFILIAL
                    AND TU.CDCAIXA = V.CDCAIXA
                    AND F.CDFILIAL = PA.CDFILIAL
                    AND TRUNC(TU.DTMOVTURCAIX) = V.DTENTRVENDA
                UNION ALL
                SELECT DISTINCT
                    V.NRORG,
                    V.CDFILIAL,
                    V.CDEMPRESA,
                    V.DTENTRVENDA,
                    V.IDAPROVACAONFCE,
                    V.CDCAIXA,
                    '' AS CDINSCESTA,
                    'REDUCAOZ' AS TIPO,
                    DECODE(TU.DTFECHCAIX,NULL,'Aberto','Fechado') AS STATUS
                FROM
                    REDUCAOZ R,
                    VENDA V,
                    FILIAL F,
                    PARAVEND PA,
                    TURCAIXA TU
                WHERE
                    R.DTREDUCAOZ = V.DTENTRVENDA
                    AND R.CDFILIAL = V.CDFILIAL
                    AND R.CDCAIXA = V.CDCAIXA
                    AND (
                        V.IDTPEMISVEND NOT IN (
                            'N',
                            'S'
                        )
                        OR V.IDTPEMISVEND IS NULL
                    )
                    AND IDAPROVACAONFCE IN (
                        'P',
                        'E'
                    )
                    AND V.IDSITUVENDA = 'O'
                    AND F.CDEMPRESA = V.CDEMPRESA
                    AND R.IDSTATUS = 'A'
                    AND V.CDFILIAL = PA.CDFILIAL
                    AND TU.CDFILIAL = V.CDFILIAL
                    AND TU.CDCAIXA = V.CDCAIXA
                    AND F.CDFILIAL = PA.CDFILIAL
                    AND TRUNC(TU.DTMOVTURCAIX) = V.DTENTRVENDA
            )
        UNION ALL
        SELECT
            NRORG,
            CDEMPRESA,
            DATA AS DTFISPDCCONT,
            'NOTA_FISCAL' AS IDFISPDCCONT
        FROM
            (
                SELECT DISTINCT
                    'Pendente' AS IDSTATUS,
                    CDEMPRESA,
                    N.NRORG,
                    NRINSJURNF,
                    DTENTRSAID,
                    DTEMISSAO,
                    NRNOTAFISC,
                    VRNOTAFISC,
                    DECODE(IDENTRSAIDOP,'E',DTENTRSAID,DTEMISSAO) AS DATA,
                    CDINSCESTA,
                    IDTPIJURNF,
                    IDENTRSAIDOP,
                    IDSISTEMA,
                    CDTIPOOPER,
                    '' AS CDIMPOSTO,
                    '' AS SGIMPOSTO,
                    1 AS MOVIMENTO
                FROM
                    NOTAFISCAL N,
                    IMPOSTONF I,
                    IMPOSTO IM,
                    PARAMFISCAL P
                WHERE
                    N.IDSTATUS = 'P'
                    AND N.IDSTATUSNFE <> 'D'
                    AND I.CDIMPOSTO = IM.CDIMPOSTO
                    AND I.CDFILIAL = N.CDFILIAL
                    AND I.NRLANCTONF = N.NRLANCTONF
                UNION ALL
                SELECT DISTINCT
                    DECODE(IDSTATUS,'P','Pendente',DECODE(IDSTATUS,'A','Aprovada','Emitida') ) AS IDSTATUS,
                    CDEMPRESA,
                    N.NRORG,
                    NRINSJURNF,
                    DTENTRSAID,
                    DTEMISSAO,
                    NRNOTAFISC,
                    VRNOTAFISC,
                    DECODE(N.IDENTRSAIDOP,'E',DTENTRSAID,DTEMISSAO) AS DATA,
                    CDINSCESTA,
                    IDTPIJURNF,
                    N.IDENTRSAIDOP,
                    IDSISTEMA,
                    N.CDTIPOOPER,
                    IM.CDIMPOSTO,
                    IM.SGIMPOSTO,
                    1 AS MOVIMENTO
                FROM
                    NOTAFISCAL N,
                    IMPOBRIGTPOPER I,
                    IMPOSTO IM,
                    PARAMFISCAL P
                WHERE
                    N.IDSTATUS <> 'C'
                    AND N.IDSTATUSNFE <> 'D'
                    AND I.CDTIPOOPER = N.CDTIPOOPER
                    AND I.IDENTRSAIDOP = N.IDENTRSAIDOP
                    AND I.CDIMPOSTO = IM.CDIMPOSTO
                    AND NVL(N.IDSISTEMA,'DFS') <> 'REC'
                    AND NVL(N.IDSISTEMA,'DFS') <> 'PAG'
                    AND NVL(N.IDSISTEMA,'DFS') <> 'CMP'
                    AND NVL(N.IDSISTEMA,'DFS') <> 'SUP'
                    AND NVL(N.IDSISTEMA,'DFS') <> 'TMP'
                    AND NVL(N.IDSISTEMA,'DFS') <> 'FIS'
                    AND NOT EXISTS (
                        SELECT
                            IMPOSTONF.CDFILIAL,
                            IMPOSTONF.NRLANCTONF
                        FROM
                            IMPOSTONF
                        WHERE
                            IMPOSTONF.CDFILIAL = N.CDFILIAL
                            AND IMPOSTONF.NRLANCTONF = N.NRLANCTONF
                            AND IMPOSTONF.CDIMPOSTO = I.CDIMPOSTO
                    )
            )
        ORDER BY
            CDEMPRESA
    ) LOOP
        INSERT INTO FISPDCCONT (
            IDFISPDCCONT,
            NRORG,
            CDEMPRESA,
            DTFISPDCCONT
        ) VALUES (
            COUNT_FIS.IDFISPDCCONT,
            COUNT_FIS.NRORG,
            COUNT_FIS.CDEMPRESA,
            COUNT_FIS.DTFISPDCCONT
        );

    END LOOP;

END;
/