IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
                    WHERE
	        O.NAME = 'ATUALIZA_IDEVOLESTOQ' AND
	        TYPE = 'P' 			        AND
	        U.UID = O.UID              AND
	        U.NAME = USER)

DROP PROCEDURE ATUALIZA_IDEVOLESTOQ
GO

CREATE PROCEDURE ATUALIZA_IDEVOLESTOQ
    @P_CDFILIPROC VARCHAR(4),
    @P_NRLANCESTQ VARCHAR(10)
AS
    DECLARE @C_CDFILIAL     VARCHAR(4);
    DECLARE @C_NRLANCESTQ   VARCHAR(10);
    DECLARE @C_NRSEQUITEM   VARCHAR(4);
    DECLARE @C_CDPRODMOVI   VARCHAR(10);
    DECLARE @C_CDFILIMOVI   VARCHAR(4);
    DECLARE @C_DTLANCMOVI   DATETIME;
    DECLARE @C_IDPERMESTNEG VARCHAR(1);
    DECLARE @V_DATE         DATETIME;

BEGIN
   SET @V_DATE = GETDATE();
   DECLARE C_PRODIRREG CURSOR FOR
      SELECT DISTINCT I.CDFILIAL, I.NRLANCESTQ, I.NRSEQUITEM,
             I.CDPRODMOVI, I.CDFILIMOVI, I.DTLANCMOVI, ISNULL(PF.IDPERMESTNEG,'N') AS IDPERMESTNEG
      FROM  POSESTDIA P, ITLANCTOEST I LEFT JOIN PARAMFILIAL PF
                         ON (I.CDFILIMOVI   = PF.CDFILIAL)
      WHERE I.CDFILIAL     = @P_CDFILIPROC   AND
            I.NRLANCESTQ   = @P_NRLANCESTQ   AND
            I.CDFILIMOVI   = P.CDFILIAL     AND
            I.CDPRODMOVI   = P.CDPRODUTO    AND
            I.CDALMOXARIFE = P.CDALMOXARIFE AND
            I.CDLOCALESTOQ = P.CDLOCALESTOQ AND
            I.NRLOTEESTQ   = P.NRLOTEESTQ   AND
            I.NRSUBLOTE    = P.NRSUBLOTE    AND
            P.DTPOSIESTQ  >= I.DTLANCMOVI   AND
            P.QTESTOQDIA  < 0;

   OPEN C_PRODIRREG;

   FETCH NEXT FROM C_PRODIRREG INTO @C_CDFILIAL, @C_NRLANCESTQ, @C_NRSEQUITEM, @C_CDPRODMOVI, @C_CDFILIMOVI,
   @C_DTLANCMOVI, @C_IDPERMESTNEG;

   WHILE (@@FETCH_STATUS = 0) BEGIN
      IF @C_IDPERMESTNEG = 'N' BEGIN
         UPDATE ITLANCTOEST
         SET IDEVOLESTOQ = 'N'
         WHERE CDFILIAL   = @C_CDFILIAL   AND
               NRLANCESTQ = @C_NRLANCESTQ AND
               NRSEQUITEM = @C_NRSEQUITEM;

         EXEC ATUALIZA_ESTOQUE @C_CDFILIMOVI, @C_DTLANCMOVI, @V_DATE, @C_CDFILIAL, @C_NRLANCESTQ;
      END;
      FETCH NEXT FROM C_PRODIRREG INTO @C_CDFILIAL, @C_NRLANCESTQ, @C_NRSEQUITEM, @C_CDPRODMOVI, @C_CDFILIMOVI,
      @C_DTLANCMOVI, @C_IDPERMESTNEG;
   END;
   CLOSE C_PRODIRREG;
   DEALLOCATE C_PRODIRREG;
END;
GO