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

CREATE PROCEDURE GERA_RETIRADA_MERENDA
   @P_CDFILIAL     VARCHAR(4),
   @P_CDFILCENTPME  VARCHAR(4),
   @P_DTINICIAL     DATETIME,
   @P_DTFINAL       DATETIME,
   @P_NRCONSPLAN    VARCHAR(10),
   @P_CDOPERADOR    VARCHAR(12),
   @P_NRINVENTRETI  VARCHAR(10),
   @P_CDFILIPROC    VARCHAR(4),
   @P_DSLANCAMENTO  VARCHAR(60)
AS
   DECLARE @V_CDTIPORETI   VARCHAR(2);
   DECLARE @V_NRSEQUITRETI VARCHAR(4);
   DECLARE @V_QTCONSPLAN   FLOAT;
   DECLARE @V_QTDEESTO     FLOAT;
   DECLARE @V_VALRESTO     FLOAT;
   DECLARE @V_VALRBRUT     FLOAT;
   DECLARE @V_PERCENT      FLOAT;
   DECLARE @V_NRCARDAPIO   VARCHAR(20);
   DECLARE @V_COUNT        FLOAT;
   DECLARE @V_CDFILIAL     VARCHAR(4);
   DECLARE @V_NRLANCTOEST  VARCHAR(20);
   DECLARE @V_NRRETIRADA   VARCHAR(20);
   DECLARE @V_QTCONSUMIDA  FLOAT;
   DECLARE @V_DTINVENTRETIAUX DATETIME;
   DECLARE @V_NOVO_CODIGOAUX  VARCHAR(20);
/*CURSOR*/
   DECLARE @C_CDFILIAL     VARCHAR(4);
   DECLARE @C_NRINVENTRETI VARCHAR(10);
   DECLARE @C_DTINVENTRETI DATETIME;
   DECLARE @C_DSINVENTRETI VARCHAR(60);
   DECLARE @C_IDRETIEFET   VARCHAR(1);
   DECLARE @C_NRITINVENTRETI VARCHAR(4);
   DECLARE @C_QTITINVENTRETI FLOAT;
   DECLARE @C_CDPRODUTO    VARCHAR(10);
   DECLARE @C_CDPRODESTO   VARCHAR(10);
   DECLARE @C_VRFATOCONV   FLOAT;
   DECLARE @C_ITPREV_CDFILIAL VARCHAR(4);
   DECLARE @C_ITPREV_CDSERVCPLA VARCHAR(5);
   DECLARE @C_ITPREV_DTCONSPLAN DATETIME;
   DECLARE @C_ITPREV_CDPRODCPLA VARCHAR(10);
   DECLARE @C_ITPREV_VRCUSTOPROD FLOAT;
   DECLARE @C_ITPREV_QTCONSPLAN FLOAT;
BEGIN
   SELECT @V_CDTIPORETI = CDTPRETIPADR
     FROM PARAMPLANEJA
    WHERE CDFILIAL = @P_CDFILIAL;
   IF @V_CDTIPORETI = NULL BEGIN
      SET @V_CDTIPORETI = NULL;
   END;
   SET @V_NRSEQUITRETI = 0;
   DECLARE C_INVENTARIO CURSOR FOR

      (SELECT IR.CDFILIAL,
              IR.NRINVENTRETI,
              IR.DTINVENTRETI,
              IR.DSINVENTRETI,
              IR.IDRETIEFET,
              IT.NRITINVENTRETI,
              IT.QTITINVENTRETI,
              IT.CDPRODUTO,
              PD.CDPRODESTO,
              ISNULL(PD.VRFATOCONV, 1) AS VRFATOCONV
       FROM INVENTARIORETI IR, ITEMINVENTRETI IT, PRODUTO PD
       WHERE IR.CDFILIAL = IT.CDFILIAL
         AND IR.NRINVENTRETI = IT.NRINVENTRETI
         AND IR.CDFILIAL = @P_CDFILIAL
         AND IR.NRINVENTRETI = @P_NRINVENTRETI
         AND PD.CDPRODUTO = IT.CDPRODUTO)
   OPEN C_INVENTARIO;
   FETCH NEXT FROM C_INVENTARIO INTO @C_CDFILIAL, @C_NRINVENTRETI, @C_DTINVENTRETI, @C_DSINVENTRETI,
                                     @C_IDRETIEFET, @C_NRITINVENTRETI, @C_QTITINVENTRETI, @C_CDPRODUTO, @C_CDPRODESTO, @C_VRFATOCONV;


   WHILE (@@FETCH_STATUS = 0) BEGIN

        /* VERIFCAR A POSICAO DE ESTOQUE
            POSICAO - QUANT INVENTARIO = QUANTIDADE A RETIRAR */
      SELECT @V_QTCONSPLAN = SUM(QTCONSPLAN)
      FROM CONSPLAN
      WHERE NRCONSPLAN = @P_NRCONSPLAN
        AND CDFILICPLA = @P_CDFILIAL
        AND CDPRODCPLA = @C_CDPRODUTO;

      SET @V_DTINVENTRETIAUX = @C_DTINVENTRETI;

      EXEC CONSULTA_POSICAO @P_CDFILIAL, @C_CDPRODUTO, @V_DTINVENTRETIAUX, ' ', ' ', ' ', ' ', 'N',
                            @V_QTDEESTO OUTPUT, @V_VALRESTO OUTPUT, @V_VALRBRUT OUTPUT;

      IF @V_QTDEESTO > 0 BEGIN
         SET @V_VALRBRUT = @V_VALRBRUT / @V_QTDEESTO;
      END;

      DECLARE C_ITEMPREVISTO CURSOR FOR
         (SELECT CDFILICPLA, CDSERVCPLA, DTCONSPLAN, CDPRODCPLA, VRCUSTOPROD, SUM(QTCONSPLAN) AS QTCONSPLAN
          FROM(SELECT CDFILICPLA, CDSERVCPLA, DTCONSPLAN, CDPRODCPLA,
                      TEKNISA.CUSTO_PRODUTO(CDFILICPLA, CDPRODCPLA, ISNULL(CDTIPCUSTEST, 3), DTCONSPLAN, GETDATE()) AS VRCUSTOPROD, QTCONSPLAN
               FROM CONSPLAN, PARAMFILIAL
               WHERE NRCONSPLAN = @P_NRCONSPLAN
                 AND CDFILICPLA = @P_CDFILIAL
                 AND CDPRODCPLA = @C_CDPRODUTO
                 AND EXISTS (SELECT CDFILIAL
                             FROM CARDIAPME
                             WHERE CDFILCENTPME = @P_CDFILCENTPME
                               AND CDFILIAL = CDFILICPLA
                               AND DTCARDIAPME = DTCONSPLAN
                               AND CDSERVFILI = CDSERVCPLA
                               AND QTCOMEFECPME > 0)
                AND PARAMFILIAL.CDFILIAL = CONSPLAN.CDFILICPLA) PR
          GROUP BY CDFILICPLA, CDSERVCPLA, DTCONSPLAN, CDPRODCPLA, VRCUSTOPROD)
      OPEN C_ITEMPREVISTO;
      FETCH NEXT FROM C_ITEMPREVISTO INTO @C_ITPREV_CDFILIAL, @C_ITPREV_CDSERVCPLA, @C_ITPREV_DTCONSPLAN,
                                          @C_ITPREV_CDPRODCPLA, @C_ITPREV_VRCUSTOPROD, @C_ITPREV_QTCONSPLAN;
      WHILE (@@FETCH_STATUS = 0) BEGIN
         /* PERCENTUAL DE DIFERENCA = QUANT TOTAL PREVISTA * 100 / QUANT A RETIRAR - ENTAO */
         SET @V_PERCENT =  @C_ITPREV_QTCONSPLAN * 100 / @V_QTCONSPLAN;
            SELECT @V_NRCARDAPIO = NRCARDAPIO
            FROM CARDADIA
            WHERE (CDFILIAL   = @P_CDFILIAL)
              AND (CDSERVFILI = @C_ITPREV_CDSERVCPLA)
              AND (DTCARDAPIO = @C_ITPREV_DTCONSPLAN);
         SET @V_COUNT = 0;
         IF @V_NRCARDAPIO IS NOT NULL BEGIN
            SELECT @V_COUNT = COUNT(1), @V_NRLANCTOEST = MAX(NRLANCESTQ), @V_NRRETIRADA = MAX(NRRETIRADA)
            FROM RETIRADA RE
            WHERE RE.CDFILIAL = @P_CDFILIAL
              AND RE.NRCARDAPIO = @V_NRCARDAPIO
              AND RE.DTRETIRADA = @C_ITPREV_DTCONSPLAN;
         END;
         IF (@V_COUNT = 0) BEGIN
            IF (@V_NRCARDAPIO IS NULL) BEGIN
               SET @V_NOVO_CODIGOAUX = 'CARDAPIO' + @P_CDFILIAL;
               EXEC NOVO_CODIGO @V_NOVO_CODIGOAUX, @P_SEQUENCIAL = @V_NRCARDAPIO OUTPUT;
               SET @V_NRCARDAPIO = SUBSTRING(@V_NRCARDAPIO, 11, 10);

               INSERT INTO CARDADIA
                  (CDFILIAL, NRCARDAPIO, CDSERVFILI, CDOPERADOR, DTCARDAPIO, DTMESCARD)
               VALUES
                  (@P_CDFILIAL, @V_NRCARDAPIO, @C_ITPREV_CDSERVCPLA, @P_CDOPERADOR, @C_ITPREV_DTCONSPLAN, DATEADD(MM, -1, (DATEADD(MM,1, (@C_ITPREV_DTCONSPLAN)) - DAY(@C_ITPREV_DTCONSPLAN))) + 1)
            END;

            SET @V_NOVO_CODIGOAUX = 'LANCTOEST' + @P_CDFILIPROC;
            EXEC NOVO_CODIGO @V_NOVO_CODIGOAUX, @P_SEQUENCIAL = @V_NRLANCTOEST OUTPUT;
            SET @V_NRLANCTOEST = SUBSTRING(@V_NRLANCTOEST, 11, 10);

            INSERT INTO LANCTOESTOQ
               (CDFILIAL, NRLANCESTQ, IDTPLANCTO, DTLANCESTQ, DSLANCESTQ, IDALTCUSMED,
                NRLANCTONF, CDFILIMOVI, CDOPERADOR,  CDALMOXARIFE,  CDLOCALESTOQ)
            VALUES
               (@P_CDFILIPROC, @V_NRLANCTOEST, '3', @C_ITPREV_DTCONSPLAN, @P_DSLANCAMENTO, 'N',
                NULL, @P_CDFILIAL, @P_CDOPERADOR,  ' ',  ' ');

            SET @V_NOVO_CODIGOAUX = 'RETIRADA' + @P_CDFILIAL;
            EXEC NOVO_CODIGO @V_NOVO_CODIGOAUX, @P_SEQUENCIAL = @V_NRRETIRADA OUTPUT;
            SET @V_NRRETIRADA = SUBSTRING(@V_NRRETIRADA, 11, 10);
            INSERT INTO RETIRADA
               (CDFILIAL, NRRETIRADA, NRCARDAPIO, DTRETIRADA, CDFILANCESTQ, NRLANCESTQ, NRINVENTRETI, IDRETIPRAT)
            VALUES
               (@P_CDFILIAL, @V_NRRETIRADA, @V_NRCARDAPIO, @C_ITPREV_DTCONSPLAN, @P_CDFILIPROC, @V_NRLANCTOEST, @P_NRINVENTRETI, 'N');
            SET @V_NRSEQUITRETI = 0;
         END ELSE BEGIN
            SELECT @V_NRSEQUITRETI = MAX(NRSEQUITRETI)
            FROM ITEMRETI
            WHERE CDFILIAL = @P_CDFILIAL
              AND NRRETIRADA = @V_NRRETIRADA
              AND NRCARDAPIO = @V_NRCARDAPIO;
              SET @V_NRSEQUITRETI = @V_NRSEQUITRETI + 1;
         END;
         IF (@V_QTDEESTO < @C_QTITINVENTRETI) BEGIN
            SET @V_QTCONSUMIDA = ROUND(((@C_QTITINVENTRETI * @V_PERCENT / 100) * @C_VRFATOCONV), 3);
         END ELSE BEGIN
            SET @V_QTCONSUMIDA = ROUND((((@V_QTDEESTO - @C_QTITINVENTRETI) * @V_PERCENT / 100) * @C_VRFATOCONV), 3);
         END;

         IF @V_QTCONSUMIDA > 0 BEGIN
            INSERT INTO ITLANCTOEST
               (CDFILIAL, NRLANCESTQ, NRSEQUITEM, CDPRODUTO, QTLANCTOEST, VRUNILANCTO,
                VRTOTLANCTO, IDTIPOMOVI, DTLANCMOVI, IDORDEMCLC, QTTOTLANCTO,
                CDFILIMOVI, CDPRODMOVI, IDEVOLESTOQ, CDALMOXARIFE, CDLOCALESTOQ, NRLOTEESTQ, NRSUBLOTE, CDTIPORETI)
               VALUES
               (@P_CDFILIPROC, @V_NRLANCTOEST, RIGHT(REPLICATE('0', 4) + @V_NRSEQUITRETI, 4), @C_ITPREV_CDPRODCPLA, @V_QTCONSUMIDA, @V_VALRBRUT,
                @C_ITPREV_VRCUSTOPROD * @V_QTCONSUMIDA, '3', @C_ITPREV_DTCONSPLAN, '9', @V_QTCONSUMIDA,
                @P_CDFILIAL, @C_CDPRODESTO, 'S', ' ', ' ', ' ', ' ', @V_CDTIPORETI);

            INSERT INTO ITEMRETI
               (CDFILIAL, NRRETIRADA, NRCARDAPIO, NRSEQUITRETI, CDPRODUTO, QTPREVISRETI, QTEXTRARETI, QTDEVOLVRETI, QTRETIRADA,
                CDFIITLANEST, NRLANCESTQ, NRSEQUITEM, CDTIPORETI)
            VALUES
               (@P_CDFILIAL, @V_NRRETIRADA, @V_NRCARDAPIO, RIGHT(REPLICATE('0', 4) + @V_NRSEQUITRETI, 4), @C_ITPREV_CDPRODCPLA, @V_QTCONSUMIDA, 0, 0, @V_QTCONSUMIDA,
                @P_CDFILIPROC, @V_NRLANCTOEST, RIGHT(REPLICATE('0', 4) + @V_NRSEQUITRETI, 4), @V_CDTIPORETI);
         END ELSE BEGIN
            INSERT INTO ITEMRETI
               (CDFILIAL, NRRETIRADA, NRCARDAPIO, NRSEQUITRETI, CDPRODUTO, QTPREVISRETI, QTEXTRARETI, QTDEVOLVRETI, QTRETIRADA,
                CDFIITLANEST, NRLANCESTQ, NRSEQUITEM, CDTIPORETI)
            VALUES
               (@P_CDFILIAL, @V_NRRETIRADA, @V_NRCARDAPIO, (RIGHT(REPLICATE('0', 4) + @V_NRSEQUITRETI, 4)), @C_ITPREV_CDPRODCPLA, @V_QTCONSUMIDA, 0, 0, @V_QTCONSUMIDA,
                NULL, NULL, NULL, @V_CDTIPORETI);
         END;
         FETCH NEXT FROM C_ITEMPREVISTO INTO @C_ITPREV_CDFILIAL, @C_ITPREV_CDSERVCPLA, @C_ITPREV_DTCONSPLAN,
                        @C_ITPREV_CDPRODCPLA, @C_ITPREV_VRCUSTOPROD, @C_ITPREV_QTCONSPLAN;
      END;
      CLOSE C_ITEMPREVISTO;
      DEALLOCATE C_ITEMPREVISTO;
   FETCH NEXT FROM C_INVENTARIO INTO @C_CDFILIAL, @C_NRINVENTRETI, @C_DTINVENTRETI, @C_DSINVENTRETI,
                   @C_IDRETIEFET, @C_NRITINVENTRETI, @C_QTITINVENTRETI, @C_CDPRODUTO, @C_CDPRODESTO, @C_VRFATOCONV;
   END;
   CLOSE C_INVENTARIO;
   DEALLOCATE C_INVENTARIO;
/*   DELETE FROM CONSPLAN
   WHERE CDFILICPLA = P_CDFILIAL
     AND NRCONSPLAN = P_NRCONSPLAN;*/
END;
GO




 


-- VERSAO --
EXEC ATUVEROBJ 'GERA_RETIRADA_MERENDA','5.07.005';
GO
