CREATE OR REPLACE PROCEDURE GERA_CONTAPAGAR_PREVISTA
                     (P_CDFILDEST  AUTOFORNE.CDFILDEST%TYPE,
                      P_CDFILLANC  AUTOFORNE.CDFILLANC%TYPE,
                      P_NRAUTOFORN AUTOFORNE.NRAUTOFORN%TYPE)
IS
   V_RESULTADO    NUMBER;
   V_CDEMPRESA    EMPRESA.CDEMPRESA%TYPE;
   V_CDINSCESTA   INSCRESTAD.CDINSCESTA%TYPE;
   V_CDINSCMUNI   INSCRMUNICIP.CDINSCMUNI%TYPE;
   V_CDTIPOOPER   PARCMPSUP.CDTIPOOPER%TYPE;
   V_NOVOCODIGO   NOVOCODIGO.NRSEQUENCIAL%TYPE;
   V_IDGERCONTPGT FORNECEDOR.IDGERCONTPGT%TYPE;
   V_CDFORNECED   FORNECEDOR.CDFORNECED%TYPE;
   V_CDCLASSFINA  TIPOCTPAGAR.CDCLASSFINA%TYPE;
   V_CDTPCTPAGAR  TIPOCTPAGAR.CDTPCTPAGAR%TYPE;
   V_NRINSJURFORN FORNECEDOR.NRINSJURFORN%TYPE;
   V_VRORIGPAG    CONTAPAGAR.VRORIGPAG%TYPE;
BEGIN
   SELECT MAX(CDFORNECED)
     INTO V_CDFORNECED
     FROM AUTOFORNE
    WHERE AUTOFORNE.CDFILDEST = P_CDFILDEST
      AND AUTOFORNE.CDFILLANC = P_CDFILLANC
      AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN;

   SELECT MAX(IDGERCONTPGT), MAX(NRINSJURFORN)
     INTO V_IDGERCONTPGT, V_NRINSJURFORN
     FROM FORNECEDOR
    WHERE CDFORNECED = V_CDFORNECED;

   V_CDCLASSFINA := NULL;
   V_CDTPCTPAGAR := NULL;
   SELECT MAX(TIPOCTPAGAR.CDCLASSFINA), MAX(TIPOCTPAGAR.CDTPCTPAGAR)
     INTO V_CDCLASSFINA, V_CDTPCTPAGAR
     FROM TIPOCTPAGAR, AUTOFORNE
   WHERE AUTOFORNE.CDTPCTPAGAR = TIPOCTPAGAR.CDTPCTPAGAR
     AND AUTOFORNE.CDFILDEST  = P_CDFILDEST
     AND AUTOFORNE.CDFILLANC  = P_CDFILLANC
     AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN;
   IF V_CDCLASSFINA IS NULL THEN
      SELECT MAX(TIPOCTPAGAR.CDCLASSFINA), MAX(TIPOCTPAGAR.CDTPCTPAGAR)
        INTO V_CDCLASSFINA, V_CDTPCTPAGAR
      FROM
         TIPOCTPAGAR, TIPOOPERAC
      WHERE TIPOOPERAC.IDENTRSAIDOP = 'E'
        AND TIPOOPERAC.CDTIPOOPER   = V_CDTIPOOPER
        AND TIPOOPERAC.CDTPCTPAGAR IS NOT NULL
        AND TIPOOPERAC.CDTPCTPAGAR = TIPOCTPAGAR.CDTPCTPAGAR;
   END IF;
   FOR C_AF IN
     (
      SELECT
       TOTITM.DTPROGENTAF,
       TOTITM.CDEMPRESA,
       TOTITM.CDFORNECED,
       TOTITM.VRPROD,

       ROUND(TOTITM.TOT + NVL(TOTIMP.TOT,0) +
             NVL(ENCACRES.TOTACRES,0) - NVL(ENCDESC.TOTDESC,0), 5) AS VRTOTAL,

       ROUND(TOTITM.TOT + NVL(TOTIMP.TOT,0) +
             NVL(ENCACRES.TOTACRES,0) - NVL(ENCDESC.TOTDESC,0) - NVL(IMPRETER.TOTIMPRET, 0), 5) AS VRTOTCTPAGAR,

       ROUND(TOTITM.VRPROD_REST +
             NVL(TOTIMP.TOT_REST,0) +
             DECODE(TOTITM.VRPROD - TOTITM.VRPROD_REST, /* TESTA SE O ITEM ESTA EM ABERTO */
                    0, - NVL(TOTITM.VRDESC,0) + NVL(ENCACRES.TOTACRES,0) - NVL(ENCDESC.TOTDESC,0) - NVL(IMPRETER.TOTIMPRET, 0),
                    0), 5) AS VRTOTAL_REST
      FROM

      (SELECT
       ITEMAUTF.DTPROGENTAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED,
       SUM((ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF) * (NVL(ITEMAUTF.VRPERDESAF,0) / 100)) AS VRDESC,
       SUM(ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF) AS VRPROD,
       SUM((ITEMAUTF.QTPROGENTAF - NVL(ITEMAUTF.QTEFETENTRAF,0)) * ITEMAUTF.VRPREUNIAF) AS VRPROD_REST,
       SUM((ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF) -
          ((ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF) *
              NVL(ITEMAUTF.VRPERDESAF,0) / 100)) AS TOT
      FROM
         ITEMAUTF,
         AUTOFORNE
      WHERE AUTOFORNE.CDFILDEST = P_CDFILDEST
        AND AUTOFORNE.CDFILLANC = P_CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN
        AND AUTOFORNE.CDFILDEST = ITEMAUTF.CDFILDEST
        AND AUTOFORNE.CDFILLANC = ITEMAUTF.CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
      GROUP BY
      ITEMAUTF.DTPROGENTAF,
      AUTOFORNE.CDEMPRESA,
      AUTOFORNE.CDFORNECED) TOTITM,

      (
      SELECT
       IMPITMAF.DTPROGENTAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED,
       SUM (IMPITMAF.VRADIMPAF * (ITEMAUTF.QTPROGENTAF - NVL(ITEMAUTF.QTEFETENTRAF,0))/ITEMAUTF.QTPROGENTAF) AS TOT_REST,
       SUM (IMPITMAF.VRADIMPAF ) AS TOT
      FROM
         IMPITMAF,
         ITEMAUTF,
         AUTOFORNE
      WHERE AUTOFORNE.CDFILDEST  = P_CDFILDEST
        AND AUTOFORNE.CDFILLANC  = P_CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN
        AND AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST
        AND AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
        AND IMPITMAF.CDFILDEST   = ITEMAUTF.CDFILDEST
        AND IMPITMAF.CDFILLANC   = ITEMAUTF.CDFILLANC
        AND IMPITMAF.NRAUTOFORN  = ITEMAUTF.NRAUTOFORN
        AND IMPITMAF.CDPRODUTO   = ITEMAUTF.CDPRODUTO
        AND IMPITMAF.CDMARCA     = ITEMAUTF.CDMARCA
        AND IMPITMAF.DTPROGENTAF = ITEMAUTF.DTPROGENTAF
      GROUP BY
      IMPITMAF.DTPROGENTAF,
      AUTOFORNE.CDEMPRESA,
      AUTOFORNE.CDFORNECED) TOTIMP,

      (
      SELECT
       ENCARGOAF.DTENTRENCAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED,
       SUM(ENCARGOAF.VRENCARGOAF) AS TOTACRES
      FROM
       ENCARGOAF, ENCARGO, AUTOFORNE
      WHERE AUTOFORNE.CDFILDEST  = P_CDFILDEST
        AND AUTOFORNE.CDFILLANC  = P_CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN
        AND AUTOFORNE.CDFILDEST  = ENCARGOAF.CDFILDEST
        AND AUTOFORNE.CDFILLANC  = ENCARGOAF.CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = ENCARGOAF.NRAUTOFORN
        AND ENCARGOAF.CDENCARGO  = ENCARGO.CDENCARGO
        AND ENCARGO.IDENCARGO = 'A'
      GROUP BY
       ENCARGOAF.DTENTRENCAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED) ENCACRES,

      (
      SELECT
       ENCARGOAF.DTENTRENCAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED,
       SUM(ENCARGOAF.VRENCARGOAF) AS TOTDESC
      FROM
       ENCARGOAF, ENCARGO, AUTOFORNE
      WHERE AUTOFORNE.CDFILDEST  = P_CDFILDEST
        AND AUTOFORNE.CDFILLANC  = P_CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN
        AND AUTOFORNE.CDFILDEST  = ENCARGOAF.CDFILDEST
        AND AUTOFORNE.CDFILLANC  = ENCARGOAF.CDFILLANC
        AND AUTOFORNE.NRAUTOFORN = ENCARGOAF.NRAUTOFORN
        AND ENCARGOAF.CDENCARGO  = ENCARGO.CDENCARGO
        AND ENCARGO.IDENCARGO = 'D'
      GROUP BY
       ENCARGOAF.DTENTRENCAF,
       AUTOFORNE.CDEMPRESA,
       AUTOFORNE.CDFORNECED) ENCDESC,

      (
      SELECT
       IMPITMAF.DTPROGENTAF,
       SUM(IMPITMAF.VRIMPITMAF) AS TOTIMPRET
      FROM
       IMPOSTO, IMPITMAF
      WHERE IMPITMAF.CDFILDEST  = P_CDFILDEST
        AND IMPITMAF.CDFILLANC  = P_CDFILLANC
        AND IMPITMAF.NRAUTOFORN = P_NRAUTOFORN
        AND IMPOSTO.CDIMPOSTO   = IMPITMAF.CDIMPOSTO
        AND IMPOSTO.IDTRATIMPO  = 'R'
      GROUP BY
       IMPITMAF.DTPROGENTAF) IMPRETER

      WHERE
       TOTITM.DTPROGENTAF = TOTIMP.DTPROGENTAF(+) AND
       TOTITM.DTPROGENTAF = ENCACRES.DTENTRENCAF (+) AND
       TOTITM.DTPROGENTAF = ENCDESC.DTENTRENCAF (+) AND
       TOTITM.DTPROGENTAF = IMPRETER.DTPROGENTAF (+) AND
       (NOT EXISTS
                 (SELECT
                     CDFILIAL
                  FROM
                     NOTAFISCAL,
                     ITEMAUTF
                  WHERE (P_CDFILDEST        = ITEMAUTF.CDFILDEST   )
                    AND (P_CDFILLANC        = ITEMAUTF.CDFILLANC   )
                    AND (P_NRAUTOFORN       = ITEMAUTF.NRAUTOFORN  )
                    AND (ITEMAUTF.CDFILDEST  = NOTAFISCAL.CDFILIAL  )
                    AND (ITEMAUTF.NRLANCTONF = NOTAFISCAL.NRLANCTONF)
                    AND (TOTITM.DTPROGENTAF  = NOTAFISCAL.DTENTRSAID)
                    AND (NOTAFISCAL.IDSISTEMA <> 'CMP'   )))
      )
   LOOP
      V_CDEMPRESA  := NULL;
      V_CDINSCMUNI := NULL;
      V_CDINSCESTA := NULL;
      SELECT MAX(FILIAL.CDEMPRESA), MAX(FILIAL.CDINSCESTA), MAX(FILIAL.CDINSCMUNI)
        INTO V_CDEMPRESA, V_CDINSCESTA, V_CDINSCMUNI
        FROM FILIAL
       WHERE FILIAL.CDFILIAL  = P_CDFILDEST
         AND FILIAL.CDEMPRESA = C_AF.CDEMPRESA;
      IF V_CDEMPRESA IS NULL THEN
         V_CDEMPRESA := C_AF.CDEMPRESA;
         SELECT MAX(EF.CDEMPRESA), MAX(EF.CDINSCESTA), MAX(EF.CDINSCMUNI)
           INTO V_CDEMPRESA, V_CDINSCESTA, V_CDINSCMUNI
           FROM EMPRCNTRFIL EF
         WHERE EF.CDEMPRESA = C_AF.CDEMPRESA
           AND EF.CDFILIAL  = P_CDFILDEST;
      END IF;
      IF V_CDEMPRESA IS NOT NULL THEN
         V_CDTIPOOPER := NULL;
         SELECT MAX(CDTIPOOPER)
           INTO V_CDTIPOOPER
           FROM PARCMPSUP
          WHERE CDFILIAL = P_CDFILDEST;
         IF V_CDTIPOOPER IS NULL THEN
            V_CDTIPOOPER := '01';
         END IF;
         NOVO_CODIGO('LANCNF/FILIAL'||P_CDFILLANC, V_NOVOCODIGO);
         V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
         INSERT INTO NOTAFISCAL
            (CDFILIAL, NRLANCTONF, CDEMPRESA,
             CDINSCESTA, CDINSCMUNI, CDFORNECED, IDBLOQUEADO, VRPRODUTOS,
             VRNOTAFISC, IDENTRSAIDOP, IDSTATUS, CDTIPOOPER, DTENTRSAID,
             IDSISTEMA, CDFILIRECE)
         VALUES
            (P_CDFILLANC, V_NOVOCODIGO,
             V_CDEMPRESA, V_CDINSCESTA, V_CDINSCMUNI, C_AF.CDFORNECED,
             'N', C_AF.VRPROD, C_AF.VRTOTAL, 'E', 'V', V_CDTIPOOPER,
             C_AF.DTPROGENTAF, 'CMP', P_CDFILDEST);
         UPDATE ITEMAUTF
            SET NRLANCTONF = V_NOVOCODIGO
         WHERE CDFILDEST = P_CDFILDEST
           AND CDFILLANC = P_CDFILLANC
           AND NRAUTOFORN = P_NRAUTOFORN
           AND DTPROGENTAF = C_AF.DTPROGENTAF;
      END IF;
      IF V_IDGERCONTPGT = 'S' THEN
         IF V_CDCLASSFINA IS NOT NULL THEN
            /*Busca NF geradas*/
            FOR C_NOTAFISCAL IN
               (SELECT DISTINCT
                   NOTAFISCAL.CDFILIAL,
                   NOTAFISCAL.NRLANCTONF,
                   NOTAFISCAL.DTENTRSAID,
                   NOTAFISCAL.CDEMPRESA,
                   NOTAFISCAL.VRPRODUTOS,
                   NOTAFISCAL.DTEMISSAO,
                   NOTAFISCAL.VRNOTAFISC - NVL(IMPRETER.TOTIMPRET,0) AS VRNOTAFISC
                 FROM
                   NOTAFISCAL, ITEMAUTF,
                 (
                 SELECT
                  IMPITMAF.DTPROGENTAF,
                  SUM(IMPITMAF.VRIMPITMAF) AS TOTIMPRET
                 FROM
                  IMPOSTO, IMPITMAF
                 WHERE IMPITMAF.CDFILDEST  = P_CDFILDEST
                   AND IMPITMAF.CDFILLANC  = P_CDFILLANC
                   AND IMPITMAF.NRAUTOFORN = P_NRAUTOFORN
                   AND IMPOSTO.CDIMPOSTO   = IMPITMAF.CDIMPOSTO
                   AND IMPOSTO.IDTRATIMPO  = 'R'
                 GROUP BY
                  IMPITMAF.DTPROGENTAF) IMPRETER
                 WHERE ITEMAUTF.CDFILDEST = P_CDFILDEST
                   AND ITEMAUTF.CDFILLANC = P_CDFILLANC
                   AND ITEMAUTF.NRAUTOFORN = P_NRAUTOFORN
                   AND NOTAFISCAL.CDFILIAL = ITEMAUTF.CDFILLANC
                   AND NOTAFISCAL.NRLANCTONF = ITEMAUTF.NRLANCTONF
                   AND NOTAFISCAL.IDSISTEMA = 'CMP'
                   AND NOTAFISCAL.DTENTRSAID = C_AF.DTPROGENTAF
                   AND IMPRETER.DTPROGENTAF (+) = NOTAFISCAL.DTENTRSAID)
            LOOP
               CRIA_VENCIMENTO(C_NOTAFISCAL.CDFILIAL,
                               C_NOTAFISCAL.NRLANCTONF,
                               'A',
                               C_NOTAFISCAL.VRNOTAFISC,
                               C_AF.DTPROGENTAF,
                               C_AF.DTPROGENTAF,
                               V_CDFORNECED,
                               P_CDFILDEST,
                               NULL,
                               P_CDFILLANC,
                               P_CDFILDEST,
                               P_NRAUTOFORN,
                               NULL,
                               NULL);
               /*INSERE A CONTA A PAGAR DE ACORDO COM OS VENCIMENTOS GERADOS*/
               /*TRAB_VENCIMENTO : TABELA TEMPORARIA PREENCHIDA PELA CRIA_VENCIMENTO*/
               FOR C_ITEMTRAB IN (SELECT DTVENCIMENTO, VRVENCIMENTO, QTDIASVENC, NRSEQVENC
                                   FROM TRAB_VENCIMENTO
                                  WHERE CDFILIALVENC = C_NOTAFISCAL.CDFILIAL
                                    AND NRLANCTONFVENC = C_NOTAFISCAL.NRLANCTONF
                                  ORDER BY NRSEQVENC) LOOP

                  /*ATUALIZA FLUXO DE CAIXA DO AJUSTE*/
                  ATUALIZA_FLUXO_DE_CAIXA(V_CDEMPRESA,
                                          P_CDFILDEST,
                                          C_ITEMTRAB.DTVENCIMENTO,
                                          'P',
                                          V_CDCLASSFINA,
                                          0,
                                          C_ITEMTRAB.VRVENCIMENTO);

                  IF C_AF.VRTOTAL_REST > 0 THEN
                     V_VRORIGPAG := (C_NOTAFISCAL.VRNOTAFISC*C_ITEMTRAB.VRVENCIMENTO)/C_AF.VRTOTAL_REST;
                  ELSE
                     V_VRORIGPAG := C_ITEMTRAB.VRVENCIMENTO;
                  END IF;

                  /*INSERE OS VENCIMENTOS GERADOS NA TABELA DE CONTAS A PAGAR*/
                  INSERT INTO CONTAPAGAR
                     (CDFILIAL, NRLANCTONF, CDMOTIPROR, CDMOTIPARC, CDTPCTPAGAR, NRSEQVENPAG,
                     NRPARCELAPAG, QTDIASVENPAG, DTORIGVENPAG, VRORIGPAG, DTATUAVENPAG, VRATUAPAG,
                     IDSTATPAG, DSCONTAPAG, CDFILIBPAG, NRLANBOLPAG, CDFIMOVFLPAG, CDCLASSFINA,
                     CDEMPRESA, IDMOVFLUXO, DTMOVFLUXO)
                  VALUES
                     (C_NOTAFISCAL.CDFILIAL,
                      C_NOTAFISCAL.NRLANCTONF,
                      NULL,
                      NULL,
                      V_CDTPCTPAGAR,
                      SUBSTR(C_ITEMTRAB.NRSEQVENC, LENGTH(C_ITEMTRAB.NRSEQVENC) -1, 2),
                      '00',
                      C_ITEMTRAB.QTDIASVENC,
                      C_ITEMTRAB.DTVENCIMENTO,
                      V_VRORIGPAG,
                      C_ITEMTRAB.DTVENCIMENTO ,
                      C_ITEMTRAB.VRVENCIMENTO,
                      'A',
                      'AF : '||V_NRINSJURFORN||' '||P_NRAUTOFORN,
                      NULL,
                      NULL,
                      P_CDFILDEST,
                      V_CDCLASSFINA,
                      V_CDEMPRESA,
                      'P',
                      C_ITEMTRAB.DTVENCIMENTO);

                  UPDATE MOVFLUXAF
                     SET VRPREVAF = C_ITEMTRAB.VRVENCIMENTO
                   WHERE CDFILDEST = P_CDFILDEST
                     AND CDFILLANC = P_CDFILLANC
                     AND NRAUTOFORN = P_NRAUTOFORN
                     AND DTPREVVENC = C_ITEMTRAB.DTVENCIMENTO
                     AND CDCLASSFINAF = V_CDCLASSFINA
                     AND DTENTMOVAF = C_NOTAFISCAL.DTENTRSAID;

                  IF NOT(SQL%ROWCOUNT > 0) THEN
                     INSERT INTO MOVFLUXAF
                     (CDFILDEST, CDFILLANC, NRAUTOFORN, DTENTMOVAF,
                      DTPREVVENC, CDCLASSFINAF, VRPREVAF)
                     VALUES
                     (P_CDFILDEST, P_CDFILLANC, P_NRAUTOFORN, C_NOTAFISCAL.DTENTRSAID,
                      C_ITEMTRAB.DTVENCIMENTO, V_CDCLASSFINA, C_ITEMTRAB.VRVENCIMENTO);
                  END IF;
               END LOOP;
               UPDATE NOTAFISCAL
               SET IDSTATUS = (SELECT DECODE(DECODE(SUM(CONTAPAGAR.VRATUAPAG),
                                                    ABS(SUM(CONTAPAGAR.VRATUAPAG)), SUM(CONTAPAGAR.VRATUAPAG),
                                                    0),
                                             0, 'C',
                                             'V')
                               FROM CONTAPAGAR
                               WHERE CONTAPAGAR.CDFILIAL = C_NOTAFISCAL.CDFILIAL
                                 AND CONTAPAGAR.NRLANCTONF = C_NOTAFISCAL.NRLANCTONF)
               WHERE CDFILIAL = C_NOTAFISCAL.CDFILIAL
                 AND NRLANCTONF = C_NOTAFISCAL.NRLANCTONF;
            END LOOP;
         END IF;
      END IF;
   END LOOP;
END;
/