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

CREATE PROCEDURE PREVISAO_LOCACAO
   (@P_CDFILIAL     VARCHAR(4),
    @P_CDPRODINI    VARCHAR(20),
    @P_CDPRODFIN    VARCHAR(20),
    @P_DTINICIAL    DATETIME,
    @P_DTFINAL      DATETIME,
    @P_NROREPREVLOC VARCHAR(10),
    @P_IDCONSORCPE  VARCHAR(01))

AS

   DECLARE @V_CDPRODUTO     VARCHAR(10);
   DECLARE @V_DTOREPREVLOC  DATETIME;
   DECLARE @V_QTOREPREVLOC  NUMERIC(15,5);
   DECLARE @V_NRTEMPREPEST  NUMERIC;
   DECLARE @V_IDSITMOV      VARCHAR(2);

   DECLARE @V_QTSALDDIA     NUMERIC(15,5);

BEGIN
   DELETE FROM TRAB_OREPREVLOC
   WHERE CDFILPREVLOC = @P_CDFILIAL
     AND NROREPREVLOC = @P_NROREPREVLOC;

   DELETE FROM TRAB_ORESALDLOC
   WHERE CDFILSALDLOC = @P_CDFILIAL
     AND NRORESALDLOC = @P_NROREPREVLOC;

   DECLARE C_POSESTCONS CURSOR FOR
   SELECT PC.CDPRODUTO, QTESTDIACON
   FROM POSESTCONS PC, PRODUTO PR
   WHERE PC.CDFILIAL = @P_CDFILIAL
     AND PC.CDPRODUTO = PR.CDPRODUTO
     AND PR.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND PC.DTPOSESTCON = (SELECT MAX(P.DTPOSESTCON)
                           FROM POSESTCONS P
                           WHERE P.CDFILIAL = @P_CDFILIAL
                             AND P.CDPRODUTO = PR.CDPRODUTO
                             AND DTPOSESTCON <= @P_DTINICIAL);

   OPEN C_POSESTCONS;
   FETCH NEXT FROM C_POSESTCONS INTO @V_CDPRODUTO, @V_QTOREPREVLOC;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO TRAB_OREPREVLOC
      (CDFILPREVLOC, NROREPREVLOC, DTOREPREVLOC, CDPRODPREVLOC, QTOREPREVLOC, IDOREPREVLOC)
      VALUES
      (@P_CDFILIAL, @P_NROREPREVLOC, @P_DTINICIAL, @V_CDPRODUTO, @V_QTOREPREVLOC, 'PI');

      FETCH NEXT FROM C_POSESTCONS INTO @V_CDPRODUTO, @V_QTOREPREVLOC;
   END;
   CLOSE C_POSESTCONS;
   DEALLOCATE C_POSESTCONS;

   DECLARE C_ORCAEVENTO CURSOR FOR
   SELECT I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED, 103),103) AS DTEVENTO, SUM(QTITEMPED - ISNULL(QTEXPITEMPED,0)) AS QTITEMORCEVE,
          ISNULL(NRTEMPREPEST,1) AS NRTEMPREPEST, 'SE' AS IDSITMOVI
   FROM ITEMPEDIDO I, PRODUTO P, PRODFILI PF
   WHERE I.CDFILIAL = @P_CDFILIAL
     AND I.IDSITITEMPED IN ('1', '2', '6')
     AND I.CDPRODUTO = P.CDPRODUTO
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED, 103),103) BETWEEN @P_DTINICIAL AND @P_DTFINAL
     AND PF.CDFILIAL = @P_CDFILIAL
     AND PF.CDPRODUTO = I.CDPRODUTO
   GROUP BY I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED, 103),103), NRTEMPREPEST
   UNION ALL
   SELECT I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED, 103),103) AS DTEVENTO, SUM(QTITEMPED - ISNULL(QTEXPITEMPED,0)) AS QTITEMORCEVE,
          ISNULL(NRTEMPREPEST,1) AS NRTEMPREPEST, 'RE' AS IDSITMOVI
   FROM ITEMPEDIDO I, PRODUTO P, PRODFILI PF
   WHERE I.CDFILIAL = @P_CDFILIAL
     AND I.IDSITITEMPED IN ('1', '2', '6')
     AND I.CDPRODUTO = P.CDPRODUTO
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED + ISNULL(NRTEMPREPEST,1), 103),103) BETWEEN @P_DTINICIAL AND @P_DTFINAL
     AND PF.CDFILIAL = @P_CDFILIAL
     AND PF.CDPRODUTO = I.CDPRODUTO
   GROUP BY I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, I.DTENTITPED, 103),103), NRTEMPREPEST
   UNION ALL
   SELECT I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO, 103),103) AS DTEVENTO, SUM(QTITEMORCEVE) AS QTITEMORCEVE,
          ISNULL(NRTEMPREPEST,1) AS NRTEMPREPEST, 'SE' AS IDSITMOVI
   FROM ORCAEVENTO O, ITEMORCAEVEN I, PRODUTO P, PRODFILI PF
   WHERE O.CDFILIAL = I.CDFILIAL
     AND O.NRORCAEVENTO = I.NRORCAEVENTO
     AND O.NRSEQORCAEVE = I.NRSEQORCAEVE
     AND O.IDSITUORCEVE = '1'
     AND @P_IDCONSORCPE = 'S'
     AND I.CDPRODUTO = P.CDPRODUTO
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO, 103),103) BETWEEN @P_DTINICIAL AND @P_DTFINAL
     AND PF.CDFILIAL = O.CDFILIAL
     AND PF.CDPRODUTO = I.CDPRODUTO
   GROUP BY I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO, 103),103), NRTEMPREPEST
   UNION ALL
   SELECT I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO, 103),103) AS DTEVENTO, SUM(QTITEMORCEVE) AS QTITEMORCEVE,
          ISNULL(NRTEMPREPEST,1) AS NRTEMPREPEST, 'RE' AS IDSITMOVI
   FROM ORCAEVENTO O, ITEMORCAEVEN I, PRODUTO P, PRODFILI PF
   WHERE O.CDFILIAL = I.CDFILIAL
     AND O.NRORCAEVENTO = I.NRORCAEVENTO
     AND O.NRSEQORCAEVE = I.NRSEQORCAEVE
     AND O.IDSITUORCEVE = '1'
     AND @P_IDCONSORCPE = 'S'
     AND I.CDPRODUTO = P.CDPRODUTO
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO + ISNULL(NRTEMPREPEST,1), 103),103) BETWEEN @P_DTINICIAL AND @P_DTFINAL
     AND PF.CDFILIAL = O.CDFILIAL
     AND PF.CDPRODUTO = I.CDPRODUTO
   GROUP BY I.CDPRODUTO, CONVERT(DATETIME, CONVERT(VARCHAR, O.DTEVENTO, 103),103), NRTEMPREPEST;

   OPEN C_ORCAEVENTO;
   FETCH NEXT FROM C_ORCAEVENTO INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC, @V_NRTEMPREPEST, @V_IDSITMOV;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO TRAB_OREPREVLOC
      (CDFILPREVLOC, NROREPREVLOC, DTOREPREVLOC, CDPRODPREVLOC, QTOREPREVLOC, IDOREPREVLOC)
      VALUES
      (@P_CDFILIAL, @P_NROREPREVLOC, @V_DTOREPREVLOC, @V_CDPRODUTO, @V_QTOREPREVLOC, @V_IDSITMOV);

      FETCH NEXT FROM C_ORCAEVENTO INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC, @V_NRTEMPREPEST, @V_IDSITMOV;
   END;
   CLOSE C_ORCAEVENTO;
   DEALLOCATE C_ORCAEVENTO;

   DECLARE C_ITEMSOLI CURSOR FOR
   SELECT P.CDPRODESTO, IT.DTPROGENTR, ISNULL(SUM(IT.QTSOLIPROD * P.VRFATOCONV),0) AS QTSOLIPROD
   FROM PRODUTO P, ITEMSOLI IT
   WHERE IT.CDFILSOLI = @P_CDFILIAL
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND IT.CDPRODUTO = P.CDPRODUTO
     AND IT.DTPROGENTR BETWEEN @P_DTINICIAL AND @P_DTFINAL
   GROUP BY P.CDPRODESTO, IT.DTPROGENTR

   OPEN C_ITEMSOLI;
   FETCH NEXT FROM C_ITEMSOLI INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO TRAB_OREPREVLOC
      (CDFILPREVLOC, NROREPREVLOC, DTOREPREVLOC, CDPRODPREVLOC, QTOREPREVLOC, IDOREPREVLOC)
      VALUES
      (@P_CDFILIAL, @P_NROREPREVLOC, @V_DTOREPREVLOC, @V_CDPRODUTO, @V_QTOREPREVLOC, 'SC');

      FETCH NEXT FROM C_ITEMSOLI INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC;
   END;
   CLOSE C_ITEMSOLI;
   DEALLOCATE C_ITEMSOLI;

   DECLARE C_ITEMAUTF CURSOR FOR
   SELECT P.CDPRODESTO, IT.DTPROGENTAF, ISNULL(SUM(IT.QTPROGENTAF * P.VRFATOCONV),0) AS QTPROGENTAF
   FROM PRODUTO P, ITEMAUTF IT
   WHERE IT.CDFILDEST   = @P_CDFILIAL
     AND IT.DTPROGENTAF BETWEEN @P_DTINICIAL AND @P_DTFINAL
     AND P.CDARVPROD BETWEEN @P_CDPRODINI AND @P_CDPRODFIN
     AND IT.CDPRODUTO = P.CDPRODUTO
     AND NOT EXISTS(SELECT A.CDFILLANC
                    FROM AUTOFORNE A
                    WHERE A.CDFILLANC  = IT.CDFILLANC
                      AND A.CDFILDEST  = IT.CDFILDEST
                      AND A.NRAUTOFORN = IT.NRAUTOFORN
                      AND A.IDBAIXAAF  IN ('4','5','6'))
   GROUP BY P.CDPRODESTO, IT.DTPROGENTAF;

   OPEN C_ITEMAUTF;
   FETCH NEXT FROM C_ITEMAUTF INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      INSERT INTO TRAB_OREPREVLOC
      (CDFILPREVLOC, NROREPREVLOC, DTOREPREVLOC, CDPRODPREVLOC, QTOREPREVLOC, IDOREPREVLOC)
      VALUES
      (@P_CDFILIAL, @P_NROREPREVLOC, @V_DTOREPREVLOC, @V_CDPRODUTO, @V_QTOREPREVLOC, 'AF');

      FETCH NEXT FROM C_ITEMAUTF INTO @V_CDPRODUTO, @V_DTOREPREVLOC, @V_QTOREPREVLOC;
   END;
   CLOSE C_ITEMAUTF;
   DEALLOCATE C_ITEMAUTF;

   DECLARE C_PRODUTO CURSOR FOR
   SELECT DISTINCT CDPRODPREVLOC
   FROM TRAB_OREPREVLOC
   WHERE CDFILPREVLOC = @P_CDFILIAL
     AND NROREPREVLOC = @P_NROREPREVLOC;
   OPEN C_PRODUTO;
   FETCH NEXT FROM C_PRODUTO INTO @V_CDPRODUTO;

   WHILE (@@FETCH_STATUS = 0) BEGIN
      DECLARE C_TRAB_OREPREVLOC CURSOR FOR
      SELECT D.DTDATA AS DTOREPREVLOC, ISNULL(T.QTOREPREVLOC,0) AS QTOREPREVLOC
      FROM DATA D LEFT JOIN (SELECT DTOREPREVLOC,
                                    SUM(QTOREPREVLOC * (CASE IDOREPREVLOC WHEN 'SE' THEN -1 ELSE 1 END)) AS QTOREPREVLOC
                             FROM TRAB_OREPREVLOC
                             WHERE CDFILPREVLOC = @P_CDFILIAL
                               AND NROREPREVLOC = @P_NROREPREVLOC
                               AND CDPRODPREVLOC = @V_CDPRODUTO
                             GROUP BY DTOREPREVLOC) T
                             ON (D.DTDATA = T.DTOREPREVLOC)
      WHERE D.DTDATA BETWEEN @P_DTINICIAL AND @P_DTFINAL
      ORDER BY D.DTDATA;
      OPEN C_TRAB_OREPREVLOC;
      FETCH NEXT FROM C_TRAB_OREPREVLOC INTO @V_DTOREPREVLOC, @V_QTOREPREVLOC;

      SET @V_QTSALDDIA = 0;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         SET @V_QTSALDDIA = @V_QTSALDDIA + @V_QTOREPREVLOC;

         INSERT INTO TRAB_ORESALDLOC
         (CDFILSALDLOC, NRORESALDLOC, DTORESALDLOC, CDPRODSALDLOC, QTORESALDLOC)
         VALUES
         (@P_CDFILIAL, @P_NROREPREVLOC, @V_DTOREPREVLOC, @V_CDPRODUTO, @V_QTSALDDIA);

         FETCH NEXT FROM C_TRAB_OREPREVLOC INTO @V_DTOREPREVLOC, @V_QTOREPREVLOC;
      END;
      CLOSE C_TRAB_OREPREVLOC;
      DEALLOCATE C_TRAB_OREPREVLOC;

      FETCH NEXT FROM C_PRODUTO INTO @V_CDPRODUTO;
   END;
   CLOSE C_PRODUTO;
   DEALLOCATE C_PRODUTO;
END;
GO




-- VERSAO --
EXEC ATUVEROBJ 'PREVISAO_LOCACAO','5.07.004';
GO


