ALTER TABLE TIPORECE ADD IDBAIXAAUTCNC VARCHAR2(01) DEFAULT 'N' NOT NULL
/
ALTER TABLE TIPORECE ADD CONSTRAINT CKC_TIPORECE_IDBAIXAAUTCNC CHECK (IDBAIXAAUTCNC IN ('S', 'N'))
/
ALTER TABLE TIPORECEFILIAL ADD IDBAIXAAUTCNC VARCHAR2(01) DEFAULT 'N' NOT NULL
/
ALTER TABLE TIPORECEFILIAL ADD CONSTRAINT CKC_TPRECEFILIAL_IDBAIXAAUTCNC CHECK (IDBAIXAAUTCNC IN ('S', 'N'))
/
CREATE OR REPLACE VIEW BOLETOPAGOCNC AS
SELECT BOL.CDFILIBOLREC, BOL.NRLANBOLREC, BOL.CDCLIENTE, BOL.CDEMPRESA, BOL.CDTIPORECE,
       BOL.DTCREDITO, BOL.NMTIPORECE, NVL(TF.CDTPBAIXAREC, BOL.CDTPBAIXAREC) CDTPBAIXAREC,
       BOL.VRFINALBREC, BOL.NRESTABELEC, NVL(TF.CDREDUCONT, BOL.CDREDUCONT) CDREDUCONT
  FROM (SELECT DISTINCT C.CDFILIBOLREC, C.NRLANBOLREC, NF.CDCLIENTE, NF.CDEMPRESA, CM.CDTIPORECE,
               RF.DTCREDITO, T.NMTIPORECE, T.CDTPBAIXAREC, B.VRFINALBREC,
               T.IDBAIXAAUTCNC, RF.NRESTABELEC, T.CDREDUCONT
         FROM RETFINANCADQ RF, RETVENDAADQ RV, CONTARECEBER C, NOTAFISCAL NF,
              CONSMOVCAIXA CM, BOLETRECEBER B, TIPORECE T
        WHERE RF.NRCONCLANC = RV.NRCONCLANCPGTO
          AND RF.DTCREDITO = RV.DTCREDITO
          AND RF.NRADQUIRENTE = RV.NRADQUIRENTE
          AND RF.NRPRODUTOADQ = RV.NRPRODUTOADQ
          AND RF.CDNSU = RV.CDNSU
          AND RV.CDFILIAL = C.CDFILIAL
          AND RV.NRLANCTONF = C.NRLANCTONF
          AND C.CDFILIAL = NF.CDFILIAL
          AND C.NRLANCTONF = NF.NRLANCTONF
          AND C.CDFILIAL = CM.CDFILIALNF
          AND C.NRLANCTONF = CM.NRLANCTONF
          AND CM.CDTIPORECE = T.CDTIPORECE
          AND C.CDFILIBOLREC = B.CDFILIAL
          AND C.NRLANBOLREC = B.NRLANBOLREC
          AND RF.IDSTATUSVENDA = 'L'
          AND B.IDSTATBREC <> 'B'
          AND B.NRBAIXAREC IS NULL) BOL LEFT JOIN TIPORECEFILIAL TF
                                               ON TF.CDFILIAL = BOL.CDFILIBOLREC
                                              AND TF.CDTIPORECE = BOL.CDTIPORECE
 WHERE NVL(TF.CDTPBAIXAREC, BOL.CDTPBAIXAREC) IS NOT NULL
   AND NVL(TF.CDREDUCONT, BOL.CDREDUCONT) IS NOT NULL
   AND NVL(TF.IDBAIXAAUTCNC, BOL.IDBAIXAAUTCNC) = 'S'
 ORDER BY BOL.CDFILIBOLREC, BOL.DTCREDITO, NVL(TF.CDREDUCONT, BOL.CDREDUCONT), BOL.CDTIPORECE
/
CREATE INDEX BOLETRECEBER_10 ON BOLETRECEBER (CDNOSNUMREC, CDCLIENTE, CDFILIBAIREC, NRBAIXAREC)
/
CREATE INDEX CONTARECEBER_10 ON CONTARECEBER (CDFILIAL, NRLANCTONF, CDFILIBOLREC, NRLANBOLREC, DTATUAVENREC, CDTPCTRECEB, VRORIGREC, VRATUAREC)
/
CREATE INDEX CONTARECEBER_11 ON CONTARECEBER (CDFILIBOLREC, NRLANBOLREC, CDTPCTRECEB, CDFILIALDUP, NRLANCTODUP)
/
CREATE INDEX CONTARECEBER_12 ON CONTARECEBER (CDFILIBOLREC, NRLANBOLREC, CDTPCTRECEB)
/
CREATE INDEX TPRECBOLETO_10 ON TPRECBOLETO (CDFILIAL, NRLANBOLREC, CDTIPORECE)
/
CREATE OR REPLACE VIEW CONTANAOPAGACNC 
AS
SELECT DISTINCT C.CDFILIAL,C.NRLANCTONF, C.VRATUAREC, C.CDFILIBOLREC, C.NRLANBOLREC
              FROM CONTARECEBER C, RETVENDAADQ RV, RETFINANCADQ RF
             WHERE C.CDFILIAL = RV.CDFILIAL
               AND C.NRLANCTONF = RV.NRLANCTONF
               AND RF.NRCONCLANC = RV.NRCONCLANCPGTO
               AND RF.DTCREDITO = RV.DTCREDITO
               AND RF.NRADQUIRENTE = RV.NRADQUIRENTE
               AND RF.NRPRODUTOADQ = RV.NRPRODUTOADQ
               AND RF.CDNSU = RV.CDNSU
               AND RF.IDSTATUSVENDA <> 'L'
               AND RF.IDCONCILIADO = 'C'