CREATE OR REPLACE PROCEDURE GERA_RETIRADA_MERENDA(
   P_CDFILIAL     IN CARDIAPME.CDFILIAL%TYPE,
   P_CDFILCENTPME IN CARDIAPME.CDFILCENTPME%TYPE,
   P_DTINICIAL    IN CARDADIA.DTCARDAPIO%TYPE,
   P_DTFINAL      IN CARDADIA.DTCARDAPIO%TYPE,
   P_NRCONSPLAN   IN CONSPLAN.NRCONSPLAN%TYPE,
   P_CDOPERADOR   IN CARDADIA.CDOPERADOR%TYPE,
   P_NRINVENTRETI IN INVENTARIORETI.NRINVENTRETI%TYPE,
   P_CDFILIPROC   IN CARDIAPME.CDFILIAL%TYPE,
   P_DSLANCAMENTO IN LANCTOESTOQ.DSLANCESTQ%TYPE
) AS
   V_CDTIPORETI   VARCHAR2(2);
   V_NRSEQUITRETI NUMBER;
   V_QTCONSPLAN   NUMBER;
   V_QTDEESTO     NUMBER;
   V_VALRESTO     NUMBER;
   V_VALRBRUT     NUMBER;
   V_PERCENT      NUMBER;
   V_NRCARDAPIO   VARCHAR2(20);
   V_COUNT        NUMBER;
   V_CDFILIAL     VARCHAR2(4);
   V_NRLANCTOEST  VARCHAR2(20);
   V_NRRETIRADA   VARCHAR2(20);
   V_QTCONSUMIDA  NUMBER;
BEGIN
   BEGIN
      SELECT CDTPRETIPADR
         INTO V_CDTIPORETI
      FROM PARAMPLANEJA
      WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDTIPORETI := NULL;
   END;
   V_NRSEQUITRETI := 0;
   FOR C_INVENTARIO IN
      (SELECT IR.CDFILIAL,
              IR.NRINVENTRETI,
              IR.DTINVENTRETI,
              IR.DSINVENTRETI,
              IR.IDRETIEFET,
              IT.NRITINVENTRETI,
              IT.QTITINVENTRETI,
              IT.CDPRODUTO,
              PD.CDPRODESTO,
              NVL(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
      ) LOOP
      
        /* VERIFCAR A POSICAO DE ESTOQUE
            POSICAO - QUANT INVENTARIO = QUANTIDADE A RETIRAR */
      SELECT SUM(QTCONSPLAN)
         INTO V_QTCONSPLAN
      FROM CONSPLAN
      WHERE NRCONSPLAN = P_NRCONSPLAN
        AND CDFILICPLA = P_CDFILIAL
        AND CDPRODCPLA = C_INVENTARIO.CDPRODUTO;

      CONSULTA_POSICAO(P_CDFILIAL,
                       C_INVENTARIO.CDPRODUTO,
                       C_INVENTARIO.DTINVENTRETI,
                       ' ',
                       ' ',
                       ' ',
                       ' ',
                       'N',
                       V_QTDEESTO,
                       V_VALRESTO,
                       V_VALRBRUT);

      IF V_QTDEESTO > 0 THEN
         V_VALRBRUT := V_VALRBRUT / V_QTDEESTO;
      END IF; 

      FOR C_ITEMPREVISTO IN
         (SELECT CDFILICPLA, CDSERVCPLA, DTCONSPLAN, CDPRODCPLA, VRCUSTOPROD, SUM(QTCONSPLAN) AS QTCONSPLAN
          FROM(SELECT CDFILICPLA, CDSERVCPLA, DTCONSPLAN, CDPRODCPLA,
                      CUSTO_PRODUTO(CDFILICPLA, CDPRODCPLA, NVL(CDTIPCUSTEST, 3) , DTCONSPLAN, SYSDATE) AS VRCUSTOPROD, QTCONSPLAN
               FROM CONSPLAN, PARAMFILIAL
               WHERE NRCONSPLAN = P_NRCONSPLAN
                 AND CDFILICPLA = P_CDFILIAL
                 AND CDPRODCPLA = C_INVENTARIO.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
         ) LOOP

         /* PERCENTUAL DE DIFERENCA = QUANT TOTAL PREVISTA * 100 / QUANT A RETIRAR - ENTAO */
         V_PERCENT :=  C_ITEMPREVISTO.QTCONSPLAN * 100 / V_QTCONSPLAN;

         BEGIN
            SELECT MAX(NRCARDAPIO)
               INTO V_NRCARDAPIO
            FROM CARDADIA
            WHERE (CDFILIAL   = P_CDFILIAL)
              AND (CDSERVFILI = C_ITEMPREVISTO.CDSERVCPLA)
              AND (DTCARDAPIO = C_ITEMPREVISTO.DTCONSPLAN);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRCARDAPIO := NULL;
         END;
         V_COUNT := 0;
         IF V_NRCARDAPIO IS NOT NULL THEN
            SELECT COUNT(1), MAX(NRLANCESTQ), MAX(NRRETIRADA)
               INTO V_COUNT, V_NRLANCTOEST, V_NRRETIRADA
            FROM RETIRADA RE
            WHERE RE.CDFILIAL = P_CDFILIAL
              AND RE.NRCARDAPIO = V_NRCARDAPIO
              AND RE.DTRETIRADA = C_ITEMPREVISTO.DTCONSPLAN;
         END IF;
         
         IF (V_COUNT = 0) THEN
            IF V_NRCARDAPIO IS NULL THEN
               NOVO_CODIGO('CARDAPIO'||P_CDFILIAL, V_NRCARDAPIO, 1);
               V_NRCARDAPIO := SUBSTR(V_NRCARDAPIO, 11, 10);
               INSERT INTO CARDADIA
                  (CDFILIAL, NRCARDAPIO, CDSERVFILI, CDOPERADOR, DTCARDAPIO, DTMESCARD)
               VALUES
                  (P_CDFILIAL, V_NRCARDAPIO, C_ITEMPREVISTO.CDSERVCPLA, P_CDOPERADOR, C_ITEMPREVISTO.DTCONSPLAN, ADD_MONTHS(LAST_DAY(C_ITEMPREVISTO.DTCONSPLAN), -1) + 1);
            END IF;

            NOVO_CODIGO('LANCTOEST'||P_CDFILIPROC, V_NRLANCTOEST, 1);
            V_NRLANCTOEST := SUBSTR(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_ITEMPREVISTO.DTCONSPLAN, P_DSLANCAMENTO, 'N',
                NULL, P_CDFILIAL, P_CDOPERADOR,  ' ',  ' ');

            NOVO_CODIGO('RETIRADA'||P_CDFILIAL, V_NRRETIRADA, 1);
            V_NRRETIRADA := SUBSTR(V_NRRETIRADA, 11, 10);
            INSERT INTO RETIRADA
               (CDFILIAL, NRRETIRADA, NRCARDAPIO, DTRETIRADA, CDFILANCESTQ, NRLANCESTQ, NRINVENTRETI, IDRETIPRAT)
            VALUES
               (P_CDFILIAL, V_NRRETIRADA, V_NRCARDAPIO, C_ITEMPREVISTO.DTCONSPLAN, P_CDFILIPROC, V_NRLANCTOEST, P_NRINVENTRETI, 'N');
            V_NRSEQUITRETI := 0;
         ELSE
            SELECT MAX(NRSEQUITRETI)
               INTO V_NRSEQUITRETI
            FROM ITEMRETI
            WHERE CDFILIAL = P_CDFILIAL
              AND NRRETIRADA = V_NRRETIRADA
              AND NRCARDAPIO = V_NRCARDAPIO;
            V_NRSEQUITRETI := V_NRSEQUITRETI + 1;
         END IF;
         
         IF (V_QTDEESTO < C_INVENTARIO.QTITINVENTRETI) THEN
            -- V_QTCONSUMIDA := ROUND(((C_INVENTARIO.QTITINVENTRETI * V_PERCENT / 100) * C_INVENTARIO.VRFATOCONV), 3);
            V_QTCONSUMIDA := 0;
         ELSE
            V_QTCONSUMIDA := ROUND((((V_QTDEESTO - C_INVENTARIO.QTITINVENTRETI) * V_PERCENT / 100) * C_INVENTARIO.VRFATOCONV), 3);
         END IF;

         IF V_QTCONSUMIDA > 0 THEN
            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, LPAD(V_NRSEQUITRETI, 4, '0'), C_ITEMPREVISTO.CDPRODCPLA, V_QTCONSUMIDA, V_VALRBRUT,
               C_ITEMPREVISTO.VRCUSTOPROD * V_QTCONSUMIDA, '3', C_ITEMPREVISTO.DTCONSPLAN, '9', V_QTCONSUMIDA,
               P_CDFILIAL, C_INVENTARIO.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, LPAD(V_NRSEQUITRETI, 4, '0'), C_ITEMPREVISTO.CDPRODCPLA, V_QTCONSUMIDA, 0, 0, V_QTCONSUMIDA,
                P_CDFILIPROC, V_NRLANCTOEST, LPAD(V_NRSEQUITRETI, 4, '0'), V_CDTIPORETI);

         ELSE
            INSERT INTO ITEMRETI
               (CDFILIAL, NRRETIRADA, NRCARDAPIO, NRSEQUITRETI, CDPRODUTO, QTPREVISRETI, QTEXTRARETI, QTDEVOLVRETI, QTRETIRADA,
                CDFIITLANEST, NRLANCESTQ, NRSEQUITEM, CDTIPORETI)
            VALUES
               (P_CDFILIAL, V_NRRETIRADA, V_NRCARDAPIO, LPAD(V_NRSEQUITRETI, 4, '0'), C_ITEMPREVISTO.CDPRODCPLA, V_QTCONSUMIDA, 0, 0, V_QTCONSUMIDA,
                NULL, NULL, NULL, V_CDTIPORETI);
         END IF;

      END LOOP;
   END LOOP; 
END;
/

