CREATE OR REPLACE PROCEDURE EXPIRA_AF
IS
   V_IDUTILDTPROC    VARCHAR2(1);
   V_NRMAXDIASIMPAF  FLOAT;
   V_CONT_COMMIT NUMBER := 0;
   V_QTDE_COMMIT NUMBER := 500;
   V_RESULTADO       VARCHAR2(100);

BEGIN

   SELECT
     NVL(MAX(IDUTILDTPROC), 'N'),
     NVL(MAX(NRMAXDIASIMPAF), 0)
   INTO
      V_IDUTILDTPROC,
      V_NRMAXDIASIMPAF
   FROM
     PARAMGERAL;

   V_CONT_COMMIT := 0;

   IF (V_NRMAXDIASIMPAF > 0) THEN
      FOR C_AUTOFORNE IN
         (SELECT
            A.CDFILDEST,
            A.CDFILLANC,
            A.NRAUTOFORN
          FROM
             AUTOFORNE A,
             PARAMFILIAL P
          WHERE
             (A.CDFILDEST  = P.CDFILIAL)   AND
             (NVL(A.IDBAIXAAF, '1') = '1')         AND
             NVL(A.IDAFEXPIRADA, 'N') <> 'S'       AND
             EXISTS
                 (SELECT
                    '1'
                  FROM
                     ITEMAUTF I
                  WHERE
                     A.CDFILDEST  = I.CDFILDEST  AND
                     A.CDFILLANC  = I.CDFILLANC  AND
                     A.NRAUTOFORN = I.NRAUTOFORN AND
                    (DECODE(V_IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, TRUNC(SYSDATE)), TRUNC(SYSDATE)) - I.DTPROGENTAF) > V_NRMAXDIASIMPAF) AND
             NOT EXISTS
                 (SELECT
                    '1'
                  FROM
                     ITEMAUTF I
                  WHERE
                     A.CDFILDEST  = I.CDFILDEST  AND
                     A.CDFILLANC  = I.CDFILLANC  AND
                     A.NRAUTOFORN = I.NRAUTOFORN AND
                    (DECODE(V_IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, TRUNC(SYSDATE)), TRUNC(SYSDATE)) - I.DTPROGENTAF) <= V_NRMAXDIASIMPAF)
          GROUP BY
             A.CDFILDEST,
             A.CDFILLANC,
             A.NRAUTOFORN
         ) LOOP

             V_CONT_COMMIT := V_CONT_COMMIT + 1;
             UPDATE
                AUTOFORNE
             SET
                IDAFEXPIRADA = 'S'
             WHERE
                CDFILDEST  = C_AUTOFORNE.CDFILDEST AND
                CDFILLANC  = C_AUTOFORNE.CDFILLANC AND
                NRAUTOFORN = C_AUTOFORNE.NRAUTOFORN AND
                NVL(IDAFEXPIRADA, 'N') <> 'S';
             EXCLUI_CONTAPAGAR_PREVISTA(C_AUTOFORNE.CDFILDEST, C_AUTOFORNE.CDFILLANC, C_AUTOFORNE.NRAUTOFORN, V_RESULTADO);
             IF (V_CONT_COMMIT >= V_QTDE_COMMIT) THEN
                V_CONT_COMMIT := 0;
                COMMIT;
             END IF;
      END LOOP;
   END IF;
   COMMIT;
END;
/

BEGIN
   DBMS_SCHEDULER.DROP_JOB (job_name => 'JOB_EXPIRA_AF');
END;
/

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      JOB_NAME   =>  'JOB_EXPIRA_AF',
      JOB_TYPE   =>  'PLSQL_BLOCK',
      JOB_ACTION =>  'DECLARE
                        V_NRMAXDIASIMPAF  FLOAT;
                        V_IDUTLEXPIRAAF VARCHAR2(1);
                        BEGIN
                           SELECT NVL(MAX(NRMAXDIASIMPAF), 0)
                             INTO V_NRMAXDIASIMPAF
                             FROM PARAMGERAL;
                           IF (V_NRMAXDIASIMPAF > 0) THEN
                              FOR C_CENTRAL IN (SELECT MAX(FILIAL.CDFILIAL) CDFILIAL, MAX(PARASUPRI.IDUTLEXPIRAAF) IDUTLEXPIRAAF
                                                  FROM FILIAL, PARASUPRI
                                                 WHERE NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL) = FILIAL.CDFILIAL
                                                   AND FILIAL.CDFILIAL = PARASUPRI.CDFILIAL
                                                   AND NVL(PARASUPRI.IDUTLEXPIRAAF, ''N'') = ''S'')
                              LOOP
                                 IF (C_CENTRAL.IDUTLEXPIRAAF = ''S'') THEN
                                    EXPIRA_AF;
                                    COMMIT;
                                 END IF;
                              END LOOP;
                           END IF;
                        END;',
      START_DATE =>  systimestamp,
      repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;BYHOUR=2;',
      JOB_CLASS  =>  '"DEFAULT_JOB_CLASS"',
      ENABLED    => TRUE,
      AUTO_DROP  => FALSE,
      COMMENTS   =>  '');
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPIRA_AF','6.39.006'); END;
/

