CREATE OR REPLACE PROCEDURE ETL_TRACKING_AF (P_NRORG          IN VARCHAR2 DEFAULT 1,
                                             P_CDOPERADOR     IN VARCHAR2 DEFAULT 'DBA') IS
   V_CENTRALCOMPRAS VARCHAR2(4);
   V_DATAINIPROC    DATE;
   V_DIASATRASO     FLOAT;
   V_DATAINI        DATE;
   V_DATAFIN        DATE;
   V_DTATUAL        DATE;

BEGIN
   V_DATAINIPROC := SYSDATE;

   SELECT TRUNC(FIRST_DAY(SYSDATE)-10) , TRUNC(LAST_DAY(SYSDATE)+15), TRUNC(SYSDATE)
     INTO V_DATAINI, V_DATAFIN, V_DTATUAL
     FROM DUAL;

   IF ((V_DTATUAL - 10) >= FIRST_DAY(SYSDATE)) THEN
      V_DATAINI := FIRST_DAY(SYSDATE);
   END IF;

   /*DEFINE TODAS AS CENTRAIS DE COMPRAS PARAMETRIZADAS NO SUPRIMENTOS*/
   FOR C_CENTRAL IN (SELECT CDFILIAL AS CDFILCENTCMP
                       FROM PARASUPRI
                      WHERE NRORG = P_NRORG
                      ORDER BY CDFILIAL)
   LOOP
       /* DELETA OS REGISTROS EXISTENSTES CONFORME OS PARAMETROS DE PERIODO E CENTRAL DE COMPRAS E QUE SERAO REPROCESSADOS*/
       DELETE 
         FROM DM_TRACKING_AF 
        WHERE CDFILCENTCMP = C_CENTRAL.CDFILCENTCMP
          AND DTPROGENTAF BETWEEN V_DATAINI AND V_DATAFIN
          AND NRORG = P_NRORG;

       COMMIT; 

       /*SELECIONA AS AF'S CONFORME OS PARAMETROS DE PERIODO E CENTRAL DE COMPRAS*/
       FOR C_AFS IN (SELECT CDFILDEST, NMFILIAL, CDFILLANC, CDFILCENTCMP, NRAUTOFORN, CDOPERADOR, NMOPERADOR, DTAUTOFORN, VRTOTALAF, 
                            DTPROGENTAF, IDBAIXAAF, IDTIPOLANCAF, IDAFEXPIRADA, IDITAFMIGRADA, IDAUTOFORNEX, IDSITUATENDIMENTOAF, 
                            TXJUSTIFICRECAF, DTCONFATENDIMENTOAF, IDCONFRECEBIMENTO, DTCONFRECEBIMENTO, CDOPERCONFAF, NMOPERCONFAF, 
                            NMPAIS, SGESTADO, NMESTADO, NMREGIAOGEO, NMMUNICIPIO, CDFORNECED, NRINSJURFORN, NMRAZSOCFORN, NMFANTFORN, 
                            CDFILLANNF, NRLANCTONF, NRNOTAFISC, DTEMISSAO, DTENTRSAID, VRNOTAFISC, NRACESSONFE, VRPBRUTOVOLU, VRPLIQUVOLU, 
                            IDTPNOTAFISCAL, NMOPERLANCNF, NRORG
    				           FROM TABLE(TRACKING_AF(C_CENTRAL.CDFILCENTCMP, V_DATAINI, V_DATAFIN, P_NRORG))
    				          ORDER BY CDFILDEST)
       LOOP
          V_DIASATRASO := NULL;

          /* DETERMINA O TEMPO DE ATRASO NO LANCAMENTO DE AF*/
          V_DIASATRASO := C_AFS.DTENTRSAID - C_AFS.DTCONFRECEBIMENTO;

          /*INSERTE NA TABELA DO TRACKING*/
          INSERT INTO DM_TRACKING_AF (CDFILDEST, NMFILIAL, CDFILLANC, CDFILCENTCMP, NRAUTOFORN, CDOPERADOR, NMOPERADOR, DTAUTOFORN, VRTOTALAF, 
                                      DTPROGENTAF, IDBAIXAAF, IDTIPOLANCAF, IDAFEXPIRADA, IDITAFMIGRADA, IDAUTOFORNEX, IDSITUATENDIMENTOAF, 
                                      TXJUSTIFICRECAF, DTCONFATENDIMENTOAF, IDCONFRECEBIMENTO, DTCONFRECEBIMENTO, CDOPERCONFAF, NMOPERCONFAF, 
                                      NMPAIS, SGESTADO, NMESTADO, NMREGIAOGEO, NMMUNICIPIO, CDFORNECED, NRINSJURFORN, NMRAZSOCFORN, NMFANTFORN, 
                                      CDFILLANNF, NRLANCTONF, NRNOTAFISC, DTEMISSAO, DTENTRSAID, VRNOTAFISC, NRACESSONFE, VRPBRUTOVOLU, VRPLIQUVOLU, 
                                      IDTPNOTAFISCAL, NMOPERLANCNF, NRATRASOLANCNF, NRORG, DTINCLUSAO, CDOPERINCLUSAO)
          VALUES(C_AFS.CDFILDEST, C_AFS.NMFILIAL, C_AFS.CDFILLANC, C_AFS.CDFILCENTCMP, C_AFS.NRAUTOFORN, C_AFS.CDOPERADOR, C_AFS.NMOPERADOR, 
                 C_AFS.DTAUTOFORN, C_AFS.VRTOTALAF, C_AFS.DTPROGENTAF, C_AFS.IDBAIXAAF, C_AFS.IDTIPOLANCAF, C_AFS.IDAFEXPIRADA, C_AFS.IDITAFMIGRADA, 
                 C_AFS.IDAUTOFORNEX, C_AFS.IDSITUATENDIMENTOAF, C_AFS.TXJUSTIFICRECAF, C_AFS.DTCONFATENDIMENTOAF, C_AFS.IDCONFRECEBIMENTO, 
                 C_AFS.DTCONFRECEBIMENTO, C_AFS.CDOPERCONFAF, C_AFS.NMOPERCONFAF, C_AFS.NMPAIS, C_AFS.SGESTADO, C_AFS.NMESTADO, C_AFS.NMREGIAOGEO, 
                 C_AFS.NMMUNICIPIO, C_AFS.CDFORNECED, C_AFS.NRINSJURFORN, C_AFS.NMRAZSOCFORN, C_AFS.NMFANTFORN, C_AFS.CDFILLANNF, C_AFS.NRLANCTONF, 
                 C_AFS.NRNOTAFISC, C_AFS.DTEMISSAO, C_AFS.DTENTRSAID, C_AFS.VRNOTAFISC, C_AFS.NRACESSONFE, C_AFS.VRPBRUTOVOLU, C_AFS.VRPLIQUVOLU, 
                 C_AFS.IDTPNOTAFISCAL, C_AFS.NMOPERLANCNF, V_DIASATRASO, C_AFS.NRORG, SYSDATE, P_CDOPERADOR);
       END LOOP;
      /*GRAVA LOG DA EXECUCAO*/
      INSERT INTO LOG_ETL_TRACKING (CDOPERADOR, DTSTART, DTEND, DTPARAMINI, DTPARAMFIN, CDFILCENTCMP, NRORG, DTINCLUSAO, CDOPERINCLUSAO)
      VALUES (P_CDOPERADOR, V_DATAINIPROC, SYSDATE, V_DATAINI, V_DATAFIN, C_CENTRAL.CDFILCENTCMP, P_NRORG, SYSDATE, P_CDOPERADOR );
   END LOOP;
   COMMIT;
END;
/