CREATE OR REPLACE PROCEDURE STPCARGA_DUPLICATA
AS
  V_QTDEREG NUMBER;
BEGIN
   FOR C_PAGAR IN (SELECT N.CDFILIAL, N.NRLANCTONF, SUBSTR(N.NRNOTAFISC,LENGTH(N.NRNOTAFISC)-6, 7) AS NRNOTAFISC,
                          REPLACE(REPLACE(REPLACE(NRINSJURNF, '-',''), '.', ''), '/', '') AS NRINSJURNF, C.DTATUAVENPAG, C.VRATUAPAG, SUBSTR(LTRIM(F.NMCOMPRZSFIL),1,3) AS BASE,
                          C.NRSEQVENPAG, C.NRPARCELAPAG, N.DTENTRSAID
                   FROM NOTAFISCAL N, CONTAPAGAR C, FILIAL F
                   WHERE N.IDENTRSAIDOP = 'E'      AND
                         N.DTEXPORTACAO IS NULL    AND
                         N.CDFILIRECE = F.CDFILIAL AND
                         N.CDFILIAL = C.CDFILIAL   AND
                         N.NRLANCTONF = C.NRLANCTONF)LOOP
      SELECT COUNT(*)
      INTO V_QTDEREG
      FROM USR_CARGA_DUPLICATA
      WHERE TX_FILIAL  = C_PAGAR.CDFILIAL    AND
            TX_LANCNF  = C_PAGAR.NRLANCTONF  AND
            TX_SEQLANC = C_PAGAR.NRSEQVENPAG AND
            TX_PARCELA = C_PAGAR.NRPARCELAPAG;

      IF V_QTDEREG = 0 THEN
         INSERT INTO USR_CARGA_DUPLICATA (ID_DUPLICATA, TP_DUPLICATA, DT_VENCIMENTO, TX_CNPJ_CPF,
                                          VL_DUPLICATA, BASE_BENLOC, TP_ATUALIZACAO, OP_IMP, TX_FILIAL,
                                          TX_LANCNF, TX_SEQLANC, TX_PARCELA, TX_NOTA_FISCAL, DT_NOTA_FISCAL)
         VALUES(C_PAGAR.NRNOTAFISC, 'P', C_PAGAR.DTATUAVENPAG, C_PAGAR.NRINSJURNF, C_PAGAR.VRATUAPAG, C_PAGAR.BASE, 'I', 'N',
                C_PAGAR.CDFILIAL, C_PAGAR.NRLANCTONF, C_PAGAR.NRSEQVENPAG, C_PAGAR.NRPARCELAPAG, C_PAGAR.NRNOTAFISC, C_PAGAR.DTENTRSAID);
      ELSE
         UPDATE USR_CARGA_DUPLICATA
         SET DT_VENCIMENTO = C_PAGAR.DTATUAVENPAG,
             TX_CNPJ_CPF   = C_PAGAR.NRINSJURNF,
             VL_DUPLICATA  = C_PAGAR.VRATUAPAG,
             TP_ATUALIZACAO = 'A'
         WHERE TX_FILIAL  = C_PAGAR.CDFILIAL    AND
               TX_LANCNF  = C_PAGAR.NRLANCTONF  AND
               TX_SEQLANC = C_PAGAR.NRSEQVENPAG AND
               TX_PARCELA = C_PAGAR.NRPARCELAPAG;
      END IF;


      UPDATE NOTAFISCAL
      SET DTEXPORTACAO = TRUNC(SYSDATE)
      WHERE CDFILIAL   = C_PAGAR.CDFILIAL AND
            NRLANCTONF = C_PAGAR.NRLANCTONF;
   END LOOP;


   FOR C_RECEB IN (SELECT N.CDFILIAL, N.NRLANCTONF, SUBSTR(N.NRNOTAFISC,LENGTH(N.NRNOTAFISC)-6, 7) AS NRNOTAFISC,
                          REPLACE(REPLACE(REPLACE(NRINSJURNF, '-',''), '.', ''), '/', '') AS NRINSJURNF, C.DTATUAVENREC, C.VRATUAREC, SUBSTR(LTRIM(F.NMCOMPRZSFIL),1,3) AS BASE,
                          C.NRSEQVENREC, C.NRPARCELAREC, N.DTENTRSAID
                   FROM NOTAFISCAL N, CONTARECEBER C, FILIAL F
                   WHERE N.IDENTRSAIDOP = 'S'      AND
                         N.IDSTATUS IN ('A','E')   AND
                         N.DTEXPORTACAO IS NULL    AND
                         N.CDFILIEMIT = F.CDFILIAL AND
                         N.CDFILIAL = C.CDFILIAL   AND
                         N.NRLANCTONF = C.NRLANCTONF)LOOP

      SELECT COUNT(*)
      INTO V_QTDEREG
      FROM USR_CARGA_DUPLICATA
      WHERE TX_FILIAL  = C_RECEB.CDFILIAL    AND
            TX_LANCNF  = C_RECEB.NRLANCTONF  AND
            TX_SEQLANC = C_RECEB.NRSEQVENREC AND
            TX_PARCELA = C_RECEB.NRPARCELAREC;

      IF V_QTDEREG = 0 THEN
         INSERT INTO USR_CARGA_DUPLICATA (ID_DUPLICATA, TP_DUPLICATA, DT_VENCIMENTO, TX_CNPJ_CPF,
                                          VL_DUPLICATA, BASE_BENLOC, TP_ATUALIZACAO, OP_IMP, TX_FILIAL,
                                          TX_LANCNF, TX_SEQLANC, TX_PARCELA, TX_NOTA_FISCAL, DT_NOTA_FISCAL)
         VALUES(C_RECEB.NRNOTAFISC, 'R', C_RECEB.DTATUAVENREC, C_RECEB.NRINSJURNF, C_RECEB.VRATUAREC, C_RECEB.BASE, 'I', 'N',
                C_RECEB.CDFILIAL, C_RECEB.NRLANCTONF, C_RECEB.NRSEQVENREC, C_RECEB.NRPARCELAREC, C_RECEB.NRNOTAFISC, C_RECEB.DTENTRSAID);
      ELSE
         UPDATE USR_CARGA_DUPLICATA
         SET DT_VENCIMENTO = C_RECEB.DTATUAVENREC,
             TX_CNPJ_CPF   = C_RECEB.NRINSJURNF,
             VL_DUPLICATA  = C_RECEB.VRATUAREC,
             TP_ATUALIZACAO = 'A'
         WHERE TX_FILIAL  = C_RECEB.CDFILIAL    AND
               TX_LANCNF  = C_RECEB.NRLANCTONF  AND
               TX_SEQLANC = C_RECEB.NRSEQVENREC AND
               TX_PARCELA = C_RECEB.NRPARCELAREC;
      END IF;

      UPDATE NOTAFISCAL
      SET DTEXPORTACAO = TRUNC(SYSDATE)
      WHERE CDFILIAL   = C_RECEB.CDFILIAL AND
            NRLANCTONF = C_RECEB.NRLANCTONF;
   END LOOP;

   COMMIT;
END;
/



-- VERSAO --
BEGIN ATUVEROBJ('STPCARGA_DUPLICATA','4.11.053'); END;
/

