CREATE OR REPLACE FORCE EDITIONABLE VIEW VW_CONSOLIDADO_REC_DESP AS (
SELECT NRORG,
       NMORGANIZACAO,
       CDEMPRESA,
       NMEMPRESA,
       CDFILIAL,
       NMFILIAL,
       CDCENTCUST,
       NMCENTCUST,
       CDFORNECED,
       NMFANTFORN,
       CDCLIENTE,
       NMFANTCLIE,
       CDMUNICIPIO,
       NMMUNICIPIO,
       SGESTADO,
       IDSTATBREC,
       IDSTATREC,
       IDSTATPAG,
       IDSTATBPAG,
       IDSTATUS,
       CDTIPOOPER,
       TIPO_OPERACAO,
       CDCLASSFINA,
       DSCLASSFINA,
       CDTPCTRECEB,
       DSTPCTRECEB,
       CDTPCTPAGAR,
       DSTPCTPAGAR,
       NOTASERIE,
       NRLANCTONF,
       DTEMISSAO,
       DTENTRSAID,
       DTORIGVENREC,
       DTATUAVENREC,
       DTATUAVENPAG,
       DTBAIXAPAG,
       PERIODO,
       TRIMESTRE,
       DTBAIXAREC,
       DATAREFERENCIA,
       SUM(DIAS)          AS DIAS,
       SUM(VRORIGREC)     AS VRORIGREC,
       SUM(VRATUARECBAIX) AS VRATUARECBAIX,
       SUM(VRATUARECABERT)AS VRATUARECABERT,
       SUM(VRATUAREC)     AS VRATUAREC,
       SUM(VRFINALBREC)   AS VRFINALBREC,
       SUM(VRBAIXAREC)    AS VRBAIXAREC,
       SUM(VRATUAPAG)     AS VRATUAPAG,
       SUM(VRATUAPAGBAIX) AS VRATUAPAGBAIX,
       SUM(VRATUAPAGABERT)AS VRATUAPAGABERT,
       SUM(VRFINALBPAG)   AS VRFINALBPAG,
       SUM(VRBAIXAPAG)    AS VRBAIXAPAG,
       SUM(VRRATPAG)      AS VRRATPAG
   FROM
  (SELECT EM.NRORG,
         'ORGANIZACAO' NMORGANIZACAO,
         EM.CDEMPRESA,
         EM.NMEMPRESA,
         NF.CDFILIEMIT AS CDFILIAL,
         FI.NMFILIAL,
         NULL AS CDCENTCUST,
         NULL AS NMCENTCUST,
         NULL AS CDFORNECED,
         NULL AS NMFANTFORN,
         NF.CDCLIENTE,
         CL.NMFANTCLIE,
         CL.CDMUNICIPIO,
         CL.NMMUNICIPIO,
         CL.SGESTADO,
         BO.IDSTATBREC,
         CR.IDSTATREC,
         NULL AS IDSTATPAG,
         NULL AS IDSTATBPAG,
         NF.IDSTATUS,
         TP_OPERNF.CDTIPOOPER,
         TP_OPERNF.CDTIPOOPER
         ||' - '
         ||TP_OPERNF.NMTIPOOPER AS TIPO_OPERACAO,
         CF.CDCLASSFINA,
         CF.DSCLASSFINA,
         CR.CDTPCTRECEB,
         TR.DSTPCTRECEB,
         NULL AS CDTPCTPAGAR,
         NULL AS DSTPCTPAGAR,
         NF.NRNOTAFISC
         ||' / '
         || NF.CDSERIE AS NOTASERIE,
         NF.NRLANCTONF,
         NF.DTEMISSAO,
         NF.DTENTRSAID,
         CR.DTORIGVENREC,
         CR.DTATUAVENREC,
         NULL                              AS DTATUAVENPAG,
         NULL                              AS DTBAIXAPAG,
         TO_CHAR(NVL(BR.DTBAIXAREC, CR.DTATUAVENREC),'YYYY/MM')AS PERIODO,
         TO_CHAR(NVL(BR.DTBAIXAREC, CR.DTATUAVENREC),'Q')
         ||' Trimestre'AS TRIMESTRE,
         BR.DTBAIXAREC,
         CR.DTATUAVENREC                   AS DATAREFERENCIA,
         ROUND(SYSDATE - CR.DTATUAVENREC,0)AS DIAS,
         CR.VRORIGREC,
         DECODE(BO.IDSTATBREC,'B',(ROUND(ROUND(CR.VRATUAREC, 2) + ((BO.VRFINALBREC - BO.VRBOLREC) * (CR.VRATUAREC/(DECODE(BO.VRBOLREC, 0, 1, BO.VRBOLREC)))),2)),0)AS VRATUARECBAIX,
         DECODE(BO.IDSTATBREC,'B',0,(ROUND(ROUND(CR.VRATUAREC, 2) + ((BO.VRFINALBREC - BO.VRBOLREC) * (CR.VRATUAREC/(DECODE(BO.VRBOLREC, 0, 1, BO.VRBOLREC)))),2)))AS VRATUARECABERT,
         CR.VRATUAREC,
         ROUND(ROUND(CR.VRATUAREC, 2) + ((BO.VRFINALBREC - BO.VRBOLREC) * (CR.VRATUAREC/(DECODE(BO.VRBOLREC, 0, 1, BO.VRBOLREC)))),2) AS VRFINALBREC,
         BR.VRBAIXAREC,
         0 AS VRATUAPAG,
         0 AS VRATUAPAGBAIX,
         0 AS VRATUAPAGABERT,
         0 AS VRFINALBPAG,
         0 AS VRBAIXAPAG,
         0 AS VRRATPAG
  FROM BANCO BC,
       AGENCIA AG,
       CLASSFINANC CF,
       CONTCORR CC,
       TIPOCTRECEBER TR,
       FILIAL FI,
       BAIXARECEBIDA BR,
       BOLETRECEBER BO,
       VENDEDOR VE,
       NFRELACORC NR,
      (SELECT * FROM NOTAFISCAL WHERE(NOT(IDSTATUS IN('C','G','P','R','V'))))NF,
      (SELECT DISTINCT CLI.CDCLIENTE,
              CLI.NMFANTCLIE,
              CLI.NMRAZSOCCLIE,
              CLI.IDTPIJURCLIE,
              CLI.IDPRIPUBCLIE,
              EN.CDMUNICIPIO,
              M.NMMUNICIPIO,
              EN.SGESTADO
         FROM ENDECLIE EN
              INNER JOIN CLIENTE CLI
              ON(CLI.CDCLIENTE   =EN.CDCLIENTE
          AND EN.IDTPENDECLIE='P' )
              INNER JOIN PAIS PA
              ON(PA.CDPAIS=EN.CDPAIS)
              INNER JOIN ESTADO ES
              ON(PA.CDPAIS   =ES.CDPAIS
          AND ES.SGESTADO=EN.SGESTADO )
              INNER JOIN MUNICIPIO M
              ON(EN.CDMUNICIPIO=M.CDMUNICIPIO
          AND M.SGESTADO   =ES.SGESTADO ))CL,
      (SELECT * FROM CONTARECEBER)CR,
      (SELECT * FROM EMPRESA)EM,
      (SELECT * FROM TIPOOPERAC) TP_OPERNF
  WHERE (NF.CDFILIAL      = CR.CDFILIAL)
    AND (NF.IDENTRSAIDOP = TP_OPERNF.IDENTRSAIDOP)
    AND (NF.CDTIPOOPER   = TP_OPERNF.CDTIPOOPER)
    AND (NF.NRORG           = CR.NRORG)
    AND (NF.NRLANCTONF      = CR.NRLANCTONF)
    AND (FI.CDFILIAL        = NF.CDFILIEMIT)
    AND (CL.CDCLIENTE       = NF.CDCLIENTE)
    AND (VE.CDVENDEDOR(+)   = NF.CDVENDEDOR)
    AND (CR.CDEMPRESA       = EM.CDEMPRESA)
    AND (CF.CDCLASSFINA     = CR.CDCLASSFINA)
    AND (CF.NRORG           = CR.NRORG)
    AND (BO.CDFILIAL        = CR.CDFILIBOLREC)
    AND (BO.NRLANBOLREC     = CR.NRLANBOLREC)
    AND (BR.CDFILIAL(+)     = BO.CDFILIBAIREC)
    AND (BR.NRBAIXAREC(+)   = BO.NRBAIXAREC)
    AND (CC.CDREDUCONT(+)      = BR.CDREDUCONT)
    AND (AG.CDBANCO(+)         = CC.CDBANCO)
    AND (AG.CDAGENCIA(+)       = CC.CDAGENCIA)
    AND (BC.CDBANCO(+)         = CC.CDBANCO)
    AND (TR.CDTPCTRECEB     = CR.CDTPCTRECEB)
    AND (NF.CDFILIAL        = NR.CDFILIALNF(+))
    AND (NF.NRLANCTONF      = NR.NRLANCTONF(+))
  UNION ALL
 SELECT NRORG,
        NMORGANIZACAO,
        CDEMPRESA,
        NMEMPRESA,
        CDFILIAL,
        NMFILIAL,
        NULL AS CDCENTCUST,
        NULL AS NMCENTCUST,
        CDFORNECED,
        NMFANTFORN,
        NULL AS CDCLIENTE,
        NULL AS NMFANTCLIE,
        CDMUNICIPIO,
        NMMUNICIPIO,
        SGESTADO,
        NULL AS IDSTATBREC,
        NULL AS IDSTATREC,
        IDSTATPAG,
        IDSTATBPAG,
        IDSTATUS,
        CDTIPOOPER,
        TIPO_OPERACAO,
        CDCLASSFINA,
        DSCLASSFINA,
        NULL AS CDTPCTRECEB,
        NULL AS DSTPCTRECEB,
        CDTPCTPAGAR,
        DSTPCTPAGAR,
        NOTASERIE,
        NRLANCTONF,
        DTEMISSAO,
        DTENTRSAID,
        NULL AS DTORIGVENREC,
        NULL AS DTATUAVENREC,
        DTATUAVENPAG,
        DTBAIXAPAG,
        PERIODO,
        TRIMESTRE,
        NULL         AS DTBAIXAREC,
        DTATUAVENPAG AS DATAREFERENCIA,
        DIAS,
        0 AS VRORIGREC,
        0 AS VRATUARECBAIX,
        0 AS VRATUARECABERT,
        0 AS VRATUAREC,
        0 AS VRFINALBREC,
        0 AS VRBAIXAREC,
        VRATUAPAG,
        DECODE(IDSTATBPAG,'B',(ROUND(VRATUAPAG, 2) + ((VRFINALBPAG - VRBOLPAG) * (VRATUAPAG/(DECODE(VRBOLPAG, 0, 1, VRBOLPAG))))),0)AS VRATUAPAGBAIX,
        DECODE(IDSTATBPAG,'B',0,(ROUND(VRATUAPAG, 2) + ((VRFINALBPAG - VRBOLPAG) * (VRATUAPAG/(DECODE(VRBOLPAG, 0, 1, VRBOLPAG))))))AS VRATUAPAGABERT,
        ROUND(VRATUAPAG, 2) + ((VRFINALBPAG - VRBOLPAG) * (VRATUAPAG/(DECODE(VRBOLPAG, 0, 1, VRBOLPAG)))) AS VRFINALBPAG,
        VRBAIXAPAG,
        0 AS VRRATPAG
   FROM
    (SELECT NF.NRORG,
            'ORGANIZACAO' NMORGANIZACAO,
            NF.CDFILIAL,
            FI.NMFILIAL,
            NF.CDFORNECED,
            FO.NMFANTFORN,
            EM.CDEMPRESA,
            EM.NMEMPRESA,
            BO.IDSTATBPAG,
            NF.IDSTATUS,
            TP_OPERNF.CDTIPOOPER,
            TP_OPERNF.CDTIPOOPER
            ||' - '
            ||TP_OPERNF.NMTIPOOPER AS TIPO_OPERACAO,
            BO.CDTPBAIXAPAG,
            BO.VRFINALBPAG,
            BO.VRBOLPAG,
            BP.DTBAIXAPAG,
            BP.NRBAIXAPAG,
            BP.VRBAIXAPAG,
            CF.DSCLASSFINA,
            TP.DSTPCTPAGAR,
            CP.CDTPCTPAGAR,
            CP.DTATUAVENPAG,
            CP.VRATUAPAG,
            CP.DSCONTAPAG,
            CP.IDSTATPAG,
            NF.NRNOTAFISC,
            NF.DTEMISSAO,
            NF.DTENTRSAID,
            FO.CDMUNICIPIO,
            FO.NMMUNICIPIO,
            FO.SGESTADO,
            CP.CDCLASSFINA,
            NF.NRNOTAFISC
            ||' / '
            || NF.CDSERIE AS NOTASERIE,
            NF.NRLANCTONF,
            TO_CHAR(NVL(BP.DTBAIXAPAG, CP.DTATUAVENPAG),'YYYY/MM')AS PERIODO,
            TO_CHAR(NVL(CP.DTATUAVENPAG, BP.DTBAIXAPAG),'Q')
            ||' Trimestre'                   AS TRIMESTRE,
            ROUND(SYSDATE - CP.DTATUAVENPAG,0)AS DIAS
       FROM
          (SELECT * FROM NOTAFISCAL WHERE(NOT(IDSTATUS IN('C','G','P','R','V'))))NF,
           BOLETPAGAR BO,
          (SELECT * FROM BAIXAPAGA)BP,
           CONTCORR CC,
          (SELECT DISTINCT EN.CDFORNECED,
            FO.NMFANTFORN,
            EN.CDMUNICIPIO,
            M.NMMUNICIPIO,
            EN.SGESTADO
          FROM ENDEFORN EN
               INNER JOIN FORNECEDOR FO
               ON(FO.CDFORNECED   =EN.CDFORNECED
           AND EN.IDTPENDEFORN='P' )
               INNER JOIN PAIS PA
               ON(PA.CDPAIS=EN.CDPAIS)
               INNER JOIN ESTADO ES
               ON(PA.CDPAIS   =ES.CDPAIS
           AND ES.SGESTADO=EN.SGESTADO )
               INNER JOIN MUNICIPIO M
               ON(EN.CDMUNICIPIO=M.CDMUNICIPIO
           AND M.SGESTADO   =ES.SGESTADO ))FO,
               BANCO BC,
               AGENCIA AG,
               CLASSFINANC CF,
               TIPOCTPAGAR TP,
               TIPOBAIXAPAG TR,
               FILIAL FI,
               (SELECT * FROM CONTAPAGAR)CP,
               (SELECT * FROM EMPRESA)EM,
               (SELECT * FROM TIPOOPERAC) TP_OPERNF
    WHERE(NF.CDFILIAL      = CP.CDFILIAL)
      AND(NF.IDENTRSAIDOP    = TP_OPERNF.IDENTRSAIDOP)
      AND(NF.CDTIPOOPER      = TP_OPERNF.CDTIPOOPER)
      AND(NF.NRLANCTONF      = CP.NRLANCTONF)
      AND(NF.NRORG           = CP.NRORG)
      AND(FI.CDFILIAL        = NF.CDFILIRECE)
      AND(FO.CDFORNECED      = NF.CDFORNECED)
      AND(CP.CDEMPRESA       = EM.CDEMPRESA)
      AND(CF.CDCLASSFINA     = CP.CDCLASSFINA)
      AND(CF.NRORG(+)        = CP.NRORG)
      AND(TR.CDTPBAIXAPAG(+) = BO.CDTPBAIXAPAG)
      AND(BO.CDFILIAL        = CP.CDFILIBPAG)
      AND(BO.NRLANBOLPAG     = CP.NRLANBOLPAG)
      AND(BP.CDFILIAL(+)     = BO.CDFILIBAIPAG)
      AND(BP.NRBAIXAPAG(+)   = BO.NRBAIXAPAG)
      AND(CC.CDREDUCONT(+)   = BP.CDREDUCONT)
      AND(AG.CDBANCO(+)      = CC.CDBANCO)
      AND(AG.CDAGENCIA(+)    = CC.CDAGENCIA)
      AND(BC.CDBANCO(+)      = CC.CDBANCO)
      AND(TP.CDTPCTPAGAR  = CP.CDTPCTPAGAR))
 UNION ALL
SELECT NRORG,
       NMORGANIZACAO,
       CDEMPRESA,
       NMEMPRESA,
       CDFILIAL,
       NMFILIAL,
       CDCENTCUST,
       NMCENTCUST,
       CDFORNECED,
       NMFANTFORN,
       NULL AS CDCLIENTE,
       NULL AS NMFANTCLIE,
       NULL AS CDMUNICIPIO,
       NULL AS NMMUNICIPIO,
       NULL AS SGESTADO,
       NULL AS IDSTATBREC,
       NULL AS IDSTATREC,
       IDSTATPAG,
       IDSTATBPAG,
       IDSTATUS,
       CDTIPOOPER,
       TIPO_OPERACAO,
       CDCLASSFINA,
       DSCLASSFINA,
       NULL AS CDTPCTRECEB,
       NULL AS DSTPCTRECEB,
       CDTPCTPAGAR,
       DSTPCTPAGAR,
       NOTASERIE,
       NRLANCTONF,
       DTEMISSAO,
       DTENTRSAID,
       NULL AS DTORIGVENREC,
       NULL AS DTATUAVENREC,
       DTATUAVENPAG,
       DTBAIXAPAG,
       PERIODO,
       TRIMESTRE,
       NULL AS DTBAIXAREC,
       DATAREFERENCIA,
       0            AS DIAS,
       0            AS VRORIGREC,
       0            AS VRATUARECBAIX,
       0            AS VRATUARECABERT,
       0            AS VRATUAREC,
       0            AS VRFINALBREC,
       0            AS VRBAIXAREC,
       0            AS VRATUAPAG,
       0            AS VRATUAPAGBAIX,
       0            AS VRATUAPAGABERT,
       0            AS VRFINALBPAG,
       0            AS VRBAIXAPAG,
       VRRATCCCTPAG AS VRRATPAG
  FROM
    (SELECT NF.NRORG,
            'ORGANIZACAO' NMORGANIZACAO,
            NF.CDEMPRESA,
            EM.NMEMPRESA,
            CP.CDFILIAL,
            NF.CDFORNECED,
            FO.NMFANTFORN,
            FO.NMRAZSOCFORN,
            NF.CDFILIRECE,
            FI.NMFILIAL,
            NF.IDSTATUS,
            TP_OPERNF.CDTIPOOPER,
            TP_OPERNF.CDTIPOOPER
            ||' - '
            ||TP_OPERNF.NMTIPOOPER AS TIPO_OPERACAO,
            BO.IDSTATBPAG,
            BO.IDHABIBPAG,
            TP.DSTPCTPAGAR,
            CF.CDCLASSFINA,
            CF.DSCLASSFINA,
            CP.CDTPCTPAGAR,
            CP.DTORIGVENPAG,
            CP.DTATUAVENPAG,
            DTATUAVENPAG AS DATAREFERENCIA,
            BP.DTBAIXAPAG,
            CP.VRATUAPAG,
            CP.DSCONTAPAG,
            CP.IDSTATPAG,
            CP.NRPARCELAPAG,
            NF.NRNOTAFISC
            ||' / '
            || NF.CDSERIE AS NOTASERIE,
            NF.NRLANCTONF,
            NF.DTEMISSAO,
            NF.DTENTRSAID,
            RP.CDCENTCUST,
            CC.NMCENTCUST,
            RP.VRRATCCCTPAG,
            TO_CHAR(NVL(BP.DTBAIXAPAG, CP.DTATUAVENPAG),'YYYY/MM')AS PERIODO,
            TO_CHAR(NVL(BP.DTBAIXAPAG, CP.DTATUAVENPAG),'Q')
            ||' Trimestre'AS TRIMESTRE
    FROM TIPOCTPAGAR TP,
         BOLETPAGAR BO,
         CLASSFINANC CF,
         FILIAL FI,
         BAIXAPAGA BP,
        (SELECT * FROM RATCTPAGAR)RP,
        (SELECT * FROM FORNECEDOR)FO,
        (SELECT * FROM NOTAFISCAL WHERE(NOT(IDSTATUS IN ('C','G','P','R','V'))))NF,
        (SELECT * FROM CENTCUST)CC,
        (SELECT * FROM CONTAPAGAR)CP,
        (SELECT * FROM EMPRESA)EM,
        (SELECT * FROM TIPOOPERAC) TP_OPERNF
   WHERE(NF.CDFILIAL      = CP.CDFILIAL)
     AND(NF.IDENTRSAIDOP    = TP_OPERNF.IDENTRSAIDOP)
     AND(NF.CDTIPOOPER      = TP_OPERNF.CDTIPOOPER)
     AND(NF.NRLANCTONF      = CP.NRLANCTONF)
     AND(FI.CDFILIAL        = NF.CDFILIRECE)
     AND(FO.CDFORNECED      = NF.CDFORNECED)
     AND(EM.CDEMPRESA       = CP.CDEMPRESA)
     AND(CF.CDCLASSFINA     = CP.CDCLASSFINA)
     AND(CF.NRORG(+)        = CP.NRORG)
     AND(BO.CDFILIAL        = CP.CDFILIBPAG)
     AND(BO.NRLANBOLPAG     = CP.NRLANBOLPAG)
     AND(BP.CDFILIAL (+)    = BO.CDFILIBAIPAG)
     AND(BP.NRBAIXAPAG (+)  = BO.NRBAIXAPAG)
     AND(TP.CDTPCTPAGAR     = CP.CDTPCTPAGAR)
     AND(RP.CDFILIAL        = CP.CDFILIAL)
     AND(RP.NRLANCTONF      = CP.NRLANCTONF)
     AND(RP.NRSEQVENPAG     = CP.NRSEQVENPAG)
     AND(RP.NRPARCELAPAG    = CP.NRPARCELAPAG)
     AND(CC.CDCENTCUST      = RP.CDCENTCUST)))
GROUP BY NRORG,
  NMORGANIZACAO,
  CDEMPRESA,
  NMEMPRESA,
  CDFILIAL,
  NMFILIAL,
  CDCENTCUST,
  NMCENTCUST,
  CDFORNECED,
  NMFANTFORN,
  CDCLIENTE,
  NMFANTCLIE,
  CDMUNICIPIO,
  NMMUNICIPIO,
  SGESTADO,
  IDSTATBREC,
  IDSTATREC,
  IDSTATPAG,
  IDSTATBPAG,
  IDSTATUS,
  CDCLASSFINA,
  DSCLASSFINA,
  CDTPCTRECEB,
  DSTPCTRECEB,
  CDTPCTPAGAR,
  DSTPCTPAGAR,
  NOTASERIE,
  NRLANCTONF,
  DTEMISSAO,
  DTENTRSAID,
  DTORIGVENREC,
  DTATUAVENREC,
  DTATUAVENPAG,
  DTBAIXAPAG,
  PERIODO,
  TRIMESTRE,
  DTBAIXAREC,
  DATAREFERENCIA,
  CDTIPOOPER,
  TIPO_OPERACAO)
/