/* PROCEDURE QUE IRÁ GRAVAR OS PRODUTOS PREVISTOS */

CREATE OR REPLACE PROCEDURE BLOCOK_PROD_PLANEJ(
   P_CDEMPRESA  IN VARCHAR2,
   P_CDINSCESTA IN VARCHAR2,
   P_DTINICIAL  IN DATE,
   P_DTFINAL    IN DATE,
   P_CDOPERADOR IN VARCHAR2) 
IS
   V_NRCONSPLAN VARCHAR2(20);
   V_NRSEQPROD NUMBER;
BEGIN
   NOVO_CODIGO('CONSPLANBLOCOK', V_NRCONSPLAN);
   V_NRCONSPLAN := 'K'||SUBSTR(V_NRCONSPLAN,12,9);
   V_NRSEQPROD := 0;

   DELETE FROM PLAREALBLOCOK
    WHERE DTINIPLAREAL = P_DTINICIAL
      AND CDINSCESTA   = P_CDINSCESTA;
   COMMIT;

   FOR C_SERVICO IN (SELECT SE.CDFILIAL, SE.CDSERVFILI
                       FROM SERVICO SE, FILIAL FI
                      WHERE FI.CDEMPRESA  = P_CDEMPRESA
                        AND FI.CDINSCESTA = P_CDINSCESTA
                        AND FI.CDFILIAL   = SE.CDFILIAL
                        AND SE.IDSERVATIV = 'S') LOOP

      CONSUMO_PLANEJADO_PLA(C_SERVICO.CDFILIAL,
                            C_SERVICO.CDSERVFILI,
                            P_DTINICIAL,
                            P_DTFINAL,
                            'C',
                            'CFP',
                            'N',
                            0,
                            'N',
                            V_NRCONSPLAN,
                            ' ',
                            'WWWWWWWWWW',
                            P_CDOPERADOR,
                            NULL,
                            NULL);
      COMMIT;
   END LOOP;

   INSERT INTO PLAREALBLOCOK
      (NRPLAREAL, DTINIPLAREAL, DTFIMPLAREAL, CDEMPRESA, CDINSCESTA )
   VALUES
      (V_NRCONSPLAN, P_DTINICIAL, P_DTFINAL, P_CDEMPRESA, P_CDINSCESTA );

   FOR C_PROD IN(SELECT CDPRODACABADO, CDPRODCPLA,
                        SUM(QTPRODBRUTA)AS QTPRODBRUTA, SUM(QTPRODLIQ) AS QTPRODLIQ,
                        SUM(QTSERVPRODUZIDO) AS QTSERVPRODUZIDO
                   FROM (SELECT SE.CDPRODUTO AS CDPRODACABADO, CP.CDPRODCPLA,
                                CP.QTCONSPLAN AS QTPRODBRUTA, ((CP.QTCONSPLAN * (100 - CP.VRPEFATCORCONS)) /100) AS QTPRODLIQ,
                                (SELECT SUM(CA.QTCOMESTICAR)
                                   FROM CARDADIA CA
                                  WHERE CDFILIAL = SE.CDFILIAL
                                    AND CDSERVFILI = SE.CDSERVFILI
                                    AND DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AS QTSERVPRODUZIDO
                           FROM CONSPLAN CP, SERVICO SE
                          WHERE CP.NRCONSPLAN = V_NRCONSPLAN
                            AND CP.CDFILICPLA = SE.CDFILIAL
                            AND CP.CDSERVCPLA = SE.CDSERVFILI) PR
                  GROUP BY CDPRODACABADO, CDPRODCPLA) LOOP

      INSERT INTO PRODPLANEJBLOCOK
         (NRPLAREAL, NRSEQPRODPLA, CDPRODACABADO, CDPRODCPLA, QTPRODBRUTA, QTPRODLIQ, QTSERVPRODUZIDO)
      VALUES
         (V_NRCONSPLAN, V_NRSEQPROD, C_PROD.CDPRODACABADO, C_PROD.CDPRODCPLA, C_PROD.QTPRODBRUTA, C_PROD.QTPRODLIQ, C_PROD.QTSERVPRODUZIDO);

      V_NRSEQPROD := V_NRSEQPROD+1;
      IF MOD(V_NRSEQPROD, 500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   COMMIT;

   DELETE FROM CONSPLAN
   WHERE NRCONSPLAN = V_NRCONSPLAN;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('BLOCOK_PROD_PLANEJ','6.05.000'); END;
/