IF EXISTS(SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'EXPIRA_AF'	and
            type = 'P'                  and
            u.uid = o.uid               and
            u.name = USER)
  DROP PROCEDURE EXPIRA_AF
  GO

CREATE PROCEDURE EXPIRA_AF
AS
   DECLARE @V_IDUTILDTPROC    VARCHAR(1) = 'N';
   DECLARE @V_NRMAXDIASIMPAF  FLOAT = 0;
   DECLARE @V_CONT_COMMIT     NUMERIC = 0;

   DECLARE @V_AUTOFORNE_CDFILDEST  VARCHAR(4);
   DECLARE @V_AUTOFORNE_CDFILLANC  VARCHAR(4);
   DECLARE @V_AUTOFORNE_NRAUTOFORN VARCHAR(10);

BEGIN
BEGIN TRANSACTION
   SELECT @V_IDUTILDTPROC = ISNULL(IDUTILDTPROC, 'N'), @V_NRMAXDIASIMPAF = ISNULL(NRMAXDIASIMPAF, 0)
     FROM PARAMGERAL;

   IF @V_NRMAXDIASIMPAF > 0 BEGIN
      DECLARE C_AUTOFORNE CURSOR FOR
         SELECT A.CDFILDEST, A.CDFILLANC, A.NRAUTOFORN
           FROM AUTOFORNE A INNER JOIN PARAMFILIAL P ON A.CDFILDEST = P.CDFILIAL
          WHERE ISNULL(A.IDBAIXAAF, '1') = '1'
            AND ISNULL(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 (CASE @V_IDUTILDTPROC WHEN 'S' THEN ISNULL(P.DTPROCESSA, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103), 103)) ELSE CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103), 103) END - 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 (CASE @V_IDUTILDTPROC WHEN 'S' THEN ISNULL(P.DTPROCESSA, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103), 103)) ELSE CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103), 103) END - I.DTPROGENTAF) <= @V_NRMAXDIASIMPAF))
          GROUP BY A.CDFILDEST, A.CDFILLANC, A.NRAUTOFORN
             
      OPEN C_AUTOFORNE;
      FETCH NEXT FROM C_AUTOFORNE INTO @V_AUTOFORNE_CDFILDEST,
                                       @V_AUTOFORNE_CDFILLANC,
                                       @V_AUTOFORNE_NRAUTOFORN;
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
         UPDATE AUTOFORNE
            SET IDAFEXPIRADA = 'S'
          WHERE CDFILDEST  = @V_AUTOFORNE_CDFILDEST
            AND CDFILLANC  = @V_AUTOFORNE_CDFILLANC
            AND NRAUTOFORN = @V_AUTOFORNE_NRAUTOFORN;

         FETCH NEXT FROM C_AUTOFORNE INTO @V_AUTOFORNE_CDFILDEST,
                                          @V_AUTOFORNE_CDFILLANC,
                                          @V_AUTOFORNE_NRAUTOFORN;
      END;
      CLOSE C_AUTOFORNE;
      DEALLOCATE C_AUTOFORNE;
   END;
   COMMIT;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'EXPIRA_AF','5.30.000';
GO