CREATE OR REPLACE PROCEDURE GRAVA_ENVIOAFFORN(P_CDFILDEST     IN VARCHAR2,
                                              P_CDFILLANC     IN VARCHAR2,
                                              P_NRAUTOFORN    IN VARCHAR2,
                                              P_CDOPERADOR    IN VARCHAR2,
                                              P_CDFORNECED    IN VARCHAR2  DEFAULT NULL,
                                              P_CDCOMPRADOR   IN VARCHAR2  DEFAULT NULL,
                                              P_NRSEQAPURAF   IN VARCHAR2  DEFAULT NULL,
                                              P_DTENTINI      IN DATE  DEFAULT NULL,
                                              P_DTENTFIN      IN DATE  DEFAULT NULL,
                                              P_DTAFINI       IN DATE  DEFAULT NULL,
                                              P_DTAFFIN       IN DATE  DEFAULT NULL)
AS
  V_COUNTADOR                 NUMERIC := 0;
  V_NOVO_CODIGO               VARCHAR2(20);
  V_IDRESTRFILIOPER           PARAMGERAL.IDRESTRFILIOPER%TYPE;
BEGIN
   SELECT NVL(MAX(IDRESTRFILIOPER), 'N')
     INTO V_IDRESTRFILIOPER
     FROM PARAMGERAL;
   NOVO_CODIGO_NT('ENVIOAFFORN', V_NOVO_CODIGO, 1);
   V_NOVO_CODIGO := SUBSTR(V_NOVO_CODIGO, 11, 10);
   FOR C_AUTOFORNE IN (
      SELECT AUTOFORNE.CDFORNECED,
             COUNT(AUTOFORNE.NRAUTOFORN) CONTADOR,
             LISTAGG(AUTOFORNE.NRAUTOFORN,'_') WITHIN GROUP (ORDER BY AUTOFORNE.NRAUTOFORN) NRAUTOFORN,
             LISTAGG(AUTOFORNE.NRSEQAPURAF,'_') WITHIN GROUP (ORDER BY AUTOFORNE.NRSEQAPURAF) NRSEQAPURAF,
             MAX((SELECT MAX(ITEMAUTF.DTPROGENTAF) MAX_DTPROGENTAF
                    FROM ITEMAUTF
                   WHERE AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST
                     AND AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC
                     AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
                     AND (TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')) BETWEEN NVL(P_DTENTINI, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY'))) AND NVL(P_DTENTFIN, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')))))) MAX_DTPROGENTAF,
             MIN((SELECT MIN(ITEMAUTF.DTPROGENTAF) MAX_DTPROGENTAF
                    FROM ITEMAUTF
                   WHERE AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST
                     AND AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC
                     AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
                     AND (TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')) BETWEEN NVL(P_DTENTINI, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY'))) AND NVL(P_DTENTFIN, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')))))) MIN_DTPROGENTAF

        FROM AUTOFORNE, FILIAL
       WHERE AUTOFORNE.CDFILDEST  = FILIAL.CDFILIAL
         AND INSTR('_'||NVL(P_CDFILDEST, FILIAL.CDFILIAL)||'_', FILIAL.CDFILIAL) > 0
         AND INSTR('_'||NVL(P_CDFILLANC, AUTOFORNE.CDFILLANC)||'_', AUTOFORNE.CDFILLANC) > 0
         AND INSTR('_'||NVL(P_NRAUTOFORN, AUTOFORNE.NRAUTOFORN)||'_', AUTOFORNE.NRAUTOFORN) > 0
         AND INSTR('_'||NVL(P_CDFORNECED, AUTOFORNE.CDFORNECED)||'_', AUTOFORNE.CDFORNECED) > 0
         AND INSTR('_'||NVL(P_CDCOMPRADOR, '_'||AUTOFORNE.CDOPERADOR)||'_', AUTOFORNE.CDOPERADOR) > 0
         AND NVL(AUTOFORNE.NRSEQAPURAF, 'T') = NVL(P_NRSEQAPURAF, NVL(AUTOFORNE.NRSEQAPURAF, 'T'))
         AND (TO_DATE(TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY')) BETWEEN NVL(P_DTAFINI, TO_DATE(TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY'))) AND NVL(P_DTAFFIN, TO_DATE(TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY'))))
         AND NVL(AUTOFORNE.IDSITUATUAAF, '0') = '0'
         AND NVL(AUTOFORNE.IDAFFORALIM, 'N')  = 'N'
         AND ((V_IDRESTRFILIOPER = 'N') OR EXISTS (SELECT 1
                                                     FROM FILIOPER
                                                    WHERE FILIOPER.CDFILIAL   = FILIAL.CDFILIAL
                                                      AND FILIOPER.CDOPERADOR = P_CDOPERADOR))
         AND EXISTS (SELECT 1
                       FROM ITEMAUTF
                      WHERE AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST
                        AND AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC
                        AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
                        AND (TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')) BETWEEN NVL(P_DTENTINI, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY'))) AND NVL(P_DTENTFIN, TO_DATE(TO_CHAR(ITEMAUTF.DTPROGENTAF, 'DD/MM/YYYY')))))
       GROUP BY AUTOFORNE.CDFORNECED)
   LOOP
      INSERT
        INTO ENVIOAFFORN
             (NRSEQENVIOAFORN, CDFORNECED, CDPAIS, SGESTADO, DTINIAPURAFFORN,
             DTFINAPURAFFORN, DTENVIOAFFORN, QTAFSENVIOFOR, QTAFSNENVFOR, IDTIPENVLIDO,
             IDTIPENVAFFORN, DSARQENVIOAF, CDOPERINCLUSAO, DTENTREGAFFORN,
             LISTNRSEQAPUR, IDUTILSMTP, DSEXTENSAO, IDTCLASSENVAFOR)
      SELECT V_NOVO_CODIGO, F.CDFORNECED, E.CDPAIS, E.SGESTADO, C_AUTOFORNE.MIN_DTPROGENTAF,
             C_AUTOFORNE.MAX_DTPROGENTAF, TO_DATE(TRUNC(SYSDATE)), C_AUTOFORNE.CONTADOR, 0, 'N',
             NVL(F.IDTPENVAFNORMAL, 'E'), '', P_CDOPERADOR, C_AUTOFORNE.MAX_DTPROGENTAF,
             C_AUTOFORNE.NRSEQAPURAF, 'S', 'PDF', 'N'
        FROM FORNECEDOR F, ENDEFORN E
       WHERE F.CDFORNECED   = C_AUTOFORNE.CDFORNECED
         AND F.CDFORNECED   = E.CDFORNECED
         AND E.IDTPENDEFORN = 'P';

      UPDATE AUTOFORNE
         SET NRSEQENVIOAFORN = V_NOVO_CODIGO
       WHERE INSTR('_'||C_AUTOFORNE.NRAUTOFORN||'_', NRAUTOFORN) > 0
         AND INSTR('_'||(NVL(P_CDFILDEST,AUTOFORNE.CDFILDEST))||'_', AUTOFORNE.CDFILDEST) > 0
         AND NVL(IDSITUATUAAF, '0') = '0'
         AND NVL(IDAFFORALIM, 'N')  = 'N';
   END LOOP;
END;
/