create or replace PROCEDURE PREVISAO_DE_CONSUMO_PLANPROD
  (P_CDCENTRAL    IN VARCHAR2,
   P_NRLISTAFILI  IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_CDPRODUTOINI IN VARCHAR2,
   P_CDPRODUTOFIN IN VARCHAR2,
   P_PEACRESCIMO  IN NUMBER,
   P_IDARREDOND   IN VARCHAR2,
   P_CDOPERADOR   IN VARCHAR2,
   P_IDEXCL_PREV  IN VARCHAR2 DEFAULT 'S',
   P_IDPRODUTOS   IN VARCHAR2 DEFAULT 'CFP') IS

   V_NRCONSPLAN VARCHAR2(20);
   V_CDSERVICO   CLOB;
   V_CDSERVRETPME VARCHAR2(5);
   V_DTFINLIMITE DATE;
   V_IDPRODUTOS VARCHAR2(3);
   V_DT DATE;
BEGIN
   /*CALCULA MAIOR DATA DE LIMITE DE SEGURANCA (SOMA DE LIMITE DE SEGURANCA DA RECEITA + PRATO*/
   BEGIN
      SELECT TO_CHAR(TO_DATE(P_DTFINAL) + MAX(A.QTLIMSEGPRAT) + MAX(A.QTLIMSEGPROD), 'DD/MM/YYYY') AS DTFINAL
      INTO V_DTFINLIMITE
      FROM
        (SELECT MAX(TO_NUMBER(NVL(QTLIMSEGSERV,'0'))) QTLIMSEGPRAT, 0 QTLIMSEGPROD FROM PRATSERV
        UNION ALL
        SELECT MAX(TO_NUMBER(NVL(QTLIMSEGFILI,'0'))) QTLIMSEGPRAT, 0 QTLIMSEGPROD FROM PRATFILI
        UNION ALL
        SELECT MAX(TO_NUMBER(NVL(QTLIMSEGPADR,'0'))) QTLIMSEGPRAT, 0 QTLIMSEGPROD FROM PRATPADR
        UNION ALL
        SELECT 0 QTLIMSEGPRAT, MAX(TO_NUMBER(NVL(QTLIMSEGSERV,'0'))) QTLIMSEGPROD FROM RECESERV
        UNION ALL
        SELECT 0 QTLIMSEGPRAT, MAX(TO_NUMBER(NVL(QTLIMSEGFILI,'0'))) QTLIMSEGPROD FROM RECEFILI
        UNION ALL
        SELECT 0 QTLIMSEGPRAT, MAX(TO_NUMBER(NVL(QTLIMSEGPADR,'0'))) QTLIMSEGPROD FROM RECEPADR
        ) A;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_DTFINLIMITE := P_DTFINAL;
   END;
   
   V_NRCONSPLAN := '';
   NOVO_CODIGO('CONSPLAN'||P_CDCENTRAL, V_NRCONSPLAN);
   V_NRCONSPLAN := SUBSTR(V_NRCONSPLAN,11,10);
   
   FOR CUR_FILIAL IN
     (SELECT DISTINCT
         SUBSTR(CDLISTA, 1, 4) AS CDFILIAL
      FROM LISTA
      WHERE
         NRLISTA = P_NRLISTAFILI
     )
   LOOP
      V_CDSERVICO := '_';

      FOR CUR_CDSERVICO IN
        (SELECT
            SERVICO.CDSERVFILI
         FROM SERVICO, LISTA, FILIAL
         WHERE
            NRLISTA = P_NRLISTAFILI AND
            SERVICO.CDFILIAL = CUR_FILIAL.CDFILIAL AND
            SERVICO.CDFILIAL||SERVICO.CDSERVFILI = CDLISTA AND
            SERVICO.IDSERVTRAN = 'S' AND
            SERVICO.CDFILIAL = FILIAL.CDFILIAL AND
            NVL(FILIAL.IDCNTRRESC,'N') = 'N'
        )
      LOOP
         V_CDSERVICO := V_CDSERVICO||CUR_CDSERVICO.CDSERVFILI||'_';
      END LOOP; /* LOOP DOS SERVICOS */
       CONSUMO_PLANEJADO_PLA(CUR_FILIAL.CDFILIAL, 
                             V_CDSERVICO, 
                             P_DTINICIAL, 
                             P_DTFINAL, 
                             'C',    
                             'XFP',  
                             'P',    
                             P_PEACRESCIMO, 
                             P_IDARREDOND, 
                             V_NRCONSPLAN, 
                             P_CDPRODUTOINI, 
                             P_CDPRODUTOFIN, 
                             P_CDOPERADOR, 
                             NULL, 
                             NULL);
       COMMIT;
      /*DELETA A PREVCONS DE TODAS AS AGREGADAS FILTRADAS NO PERIODO*/  
      IF P_IDEXCL_PREV = 'S' THEN
         DELETE FROM PREVCONS 
         WHERE 
            (CDFILIAL   = CUR_FILIAL.CDFILIAL)             AND 
            (INSTR(V_CDSERVICO,CDSERVFILI) <> 0)           AND 
            (DTPREVCONS BETWEEN P_DTINICIAL AND P_DTFINAL) AND
            (NVL(CDFILICALC, P_CDCENTRAL) = P_CDCENTRAL) AND
            (EXISTS (SELECT CDPRODUTO 
                       FROM PRODUTO 
                      WHERE CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN 
                        AND PRODUTO.CDPRODUTO = PREVCONS.CDPRODUTO)); 
      END IF; 


   END LOOP;
   
   SELECT CDSERVRETPME 
     INTO V_CDSERVRETPME 
     FROM PARAMPLANEJA
    WHERE CDFILIAL = P_CDCENTRAL;
    
   DELETE FROM FILTROPREVCONS
   WHERE (CDFILLANC = P_CDCENTRAL) 
     AND (DTPREVCONS BETWEEN P_DTINICIAL AND P_DTFINAL);         

   DELETE FROM PREVCONS
   WHERE (CDFILIAL   = P_CDCENTRAL)                      AND
         (CDSERVFILI = V_CDSERVRETPME)                   AND
         (DTPREVCONS BETWEEN P_DTINICIAL AND P_DTFINAL)  AND
         (NVL(CDFILICALC, P_CDCENTRAL) = P_CDCENTRAL) AND
         (EXISTS (SELECT CDPRODUTO 
                  FROM PRODUTO 
                 WHERE CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN 
                       AND PRODUTO.CDPRODUTO = PREVCONS.CDPRODUTO)); 
                           



       /*CALCULA PREVISAO CONSOLIDADA PARA CENTRAL - PRODUTOS DE PRODUCAO E DISTRIBUICAO*/
    FOR C_PREVCONS_DIST IN (
        SELECT  P_CDCENTRAL AS CDCENTRAL, V_CDSERVRETPME AS CDSERVRETPME, CDPRODCPLA, DTCONSPLAN , SUM(QTCONSPLAN) AS QTCONSPLAN, P_CDOPERADOR AS CDOPERADOR
            FROM(SELECT R.CDPRODUTO AS CDPRODCPLA ,R.QTPRODUTO * ((C.QTCOMESTICAR * C.VRPECONSCARD)/100) AS QTCONSPLAN,
                        C.DTCARDAPIO - (NVL(R.LIMITEACUMULADO,0) + NVL(QTLIMSEG,0)) AS DTCONSPLAN FROM 
                 (SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, C.QTCOMESTICAR, P.VRPECONSCARD, P.CDPRATO, P.CDFILAUXPRAT
                   FROM CARDADIA C INNER JOIN FILIAL F ON F.CDFILIAL = C.CDFILIAL AND NVL(IDCNTRRESC,'N') = 'N'
                                   INNER JOIN COMPCARD P ON ((C.CDFILIAL = P.CDFILIAL) AND (C.NRCARDAPIO = P.NRCARDAPIO) AND
                                                             (C.DTCARDAPIO BETWEEN P_DTINICIAL AND V_DTFINLIMITE) AND
                                                             (E_AGREGADA(P_CDCENTRAL, C.CDFILIAL, C.CDSERVFILI, P.CDTIPOPRAT, C.DTCARDAPIO) > 0))
                                   INNER JOIN (SELECT SUBSTR(CDLISTA,1,4) AS CDFILIAL, SUBSTR(CDLISTA,5,5) AS CDSERVFILI 
                                                 FROM LISTA
                                                WHERE NRLISTA = P_NRLISTAFILI) L ON (C.CDFILIAL = L.CDFILIAL AND C.CDSERVFILI = L.CDSERVFILI)) C
                 INNER JOIN TABLE(RECEITA_UTIL(C.CDFILIAL, C.CDSERVFILI, C.CDPRATO, C.CDFILAUXPRAT, P_CDPRODUTOINI, P_CDPRODUTOFIN, C.CDPRATO,0)) R
                 ON (C.DTCARDAPIO - (NVL(R.LIMITEACUMULADO,0) + NVL(QTLIMSEG,0))BETWEEN  P_DTINICIAL AND P_DTFINAL) AND (NVL(R.IDTPREQPROD,'E') IN ('P','D'))
                 
                 
                 UNION ALL
                 
                 SELECT C.CDPRODCPLA, C.QTCONSPLAN, C.DTCONSPLAN
                   FROM CONSPLAN C INNER JOIN PRODUTO PP ON C.CDPRODCPLA = PP.CDPRODUTO AND PP.CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN
                                   INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTAFILI AND C.CDFILICPLA = SUBSTR(L.CDLISTA,1,4) AND C.CDSERVCPLA = SUBSTR(L.CDLISTA,5,5)
                                   INNER JOIN FILIAL F ON F.CDFILIAL = C.CDFILICPLA AND NVL(IDCNTRRESC,'N') = 'N'
                  WHERE C.NRCONSPLAN = V_NRCONSPLAN
                    AND C.DTCONSPLAN BETWEEN P_DTINICIAL AND P_DTFINAL
                    AND (E_AGREGADA(P_CDCENTRAL, C.CDFILICPLA, C.CDSERVCPLA, C.CDTPPRATCPLA, C.DTCONSPLAN) > 0)
                    AND C.IDCONSPLAN IN ('P','F')
                    AND NVL(C.IDTPREQPROD,'E') = 'D')
                 GROUP BY CDPRODCPLA, DTCONSPLAN
    )LOOP
        BEGIN
           INSERT INTO PREVCONS
             (CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONS, QTPREVCONS, CDOPERADOR, CDFILICALC)
            VALUES
             (C_PREVCONS_DIST.CDCENTRAL, C_PREVCONS_DIST.CDSERVRETPME, C_PREVCONS_DIST.CDPRODCPLA, C_PREVCONS_DIST.DTCONSPLAN, C_PREVCONS_DIST.QTCONSPLAN,
               C_PREVCONS_DIST.CDOPERADOR, C_PREVCONS_DIST.CDCENTRAL);
         EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN BEGIN
            UPDATE PREVCONS
               SET QTPREVCONS = QTPREVCONS + C_PREVCONS_DIST.QTCONSPLAN
            WHERE
               CDFILIAL = C_PREVCONS_DIST.CDCENTRAL AND
               CDSERVFILI = C_PREVCONS_DIST.CDSERVRETPME AND
               CDPRODUTO = C_PREVCONS_DIST.CDPRODCPLA AND
               DTPREVCONS = C_PREVCONS_DIST.DTCONSPLAN;
         END;
      END;

    END LOOP;


   /*CALCULA PREVISAO PARA FILIAL - PRODUTOS DE ENTREGA DIRETA*/
   FOR C_PREVCONS IN (
      SELECT CDFILICPLA, CDSERVCPLA, CDPRODCPLA, DTCONSPLAN, SUM(QTCONSPLAN) AS QTCONSPLAN, P_CDOPERADOR
        FROM(SELECT C.CDFILIAL AS CDFILICPLA, C.CDSERVFILI AS CDSERVCPLA, R.CDPRODUTO AS CDPRODCPLA ,
                    C.DTCARDAPIO - (NVL(R.LIMITEACUMULADO,0)) AS DTCONSPLAN,
                    QTPRODUTO * ((C.QTCOMESTICAR * C.VRPECONSCARD)/100) AS QTCONSPLAN FROM   
             (SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, C.QTCOMESTICAR, P.VRPECONSCARD, P.CDPRATO, P.CDFILAUXPRAT
               FROM CARDADIA C INNER JOIN FILIAL F ON F.CDFILIAL = C.CDFILIAL AND NVL(IDCNTRRESC,'N') = 'N'
                               INNER JOIN COMPCARD P ON ((C.CDFILIAL = P.CDFILIAL) AND (C.NRCARDAPIO = P.NRCARDAPIO) AND
                                                         (C.DTCARDAPIO BETWEEN P_DTINICIAL AND V_DTFINLIMITE) AND
                                                         (E_AGREGADA(P_CDCENTRAL, C.CDFILIAL, C.CDSERVFILI, P.CDTIPOPRAT, C.DTCARDAPIO) > 0))
                               INNER JOIN (SELECT SUBSTR(CDLISTA,1,4) AS CDFILIAL, SUBSTR(CDLISTA,5,5) AS CDSERVFILI 
                                             FROM LISTA
                                            WHERE NRLISTA = P_NRLISTAFILI) L ON (C.CDFILIAL = L.CDFILIAL AND C.CDSERVFILI = L.CDSERVFILI)) C
              INNER JOIN TABLE(RECEITA_UTIL(C.CDFILIAL, C.CDSERVFILI, C.CDPRATO, C.CDFILAUXPRAT, P_CDPRODUTOINI, P_CDPRODUTOFIN, C.CDPRATO,0)) R
                ON (C.DTCARDAPIO - (NVL(R.LIMITEACUMULADO,0) )BETWEEN  P_DTINICIAL AND P_DTFINAL) AND (NVL(R.IDTPREQPROD,'E') = 'E')
                
             UNION ALL
             
             SELECT C.CDFILICPLA, C.CDSERVCPLA,  C.CDPRODCPLA, C.DTCONSPLAN, C.QTCONSPLAN
               FROM CONSPLAN C INNER JOIN PRODUTO PP ON C.CDPRODCPLA = PP.CDPRODUTO AND PP.CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN
                               INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTAFILI AND C.CDFILICPLA = SUBSTR(L.CDLISTA,1,4) AND C.CDSERVCPLA = SUBSTR(L.CDLISTA,5,5)
                               INNER JOIN FILIAL F ON F.CDFILIAL = C.CDFILICPLA AND NVL(IDCNTRRESC,'N') = 'N'
              WHERE C.NRCONSPLAN = V_NRCONSPLAN
                AND C.DTCONSPLAN BETWEEN  P_DTINICIAL AND P_DTFINAL
                AND (E_AGREGADA(P_CDCENTRAL, C.CDFILICPLA, C.CDSERVCPLA, C.CDTPPRATCPLA, C.DTCONSPLAN) > 0)
                AND C.IDCONSPLAN IN ('P','F')
                AND NVL(C.IDTPREQPROD,'E') = 'E') A
      GROUP BY CDFILICPLA, CDSERVCPLA, CDPRODCPLA, DTCONSPLAN
   )LOOP
        BEGIN
           INSERT INTO PREVCONS 
             (CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONS, QTPREVCONS, CDOPERADOR, CDFILICALC) 
            VALUES 
              (C_PREVCONS.CDFILICPLA, C_PREVCONS.CDSERVCPLA, C_PREVCONS.CDPRODCPLA, C_PREVCONS.DTCONSPLAN, C_PREVCONS.QTCONSPLAN, P_CDOPERADOR, P_CDCENTRAL ); 
         EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN BEGIN
            UPDATE PREVCONS
               SET QTPREVCONS = QTPREVCONS + C_PREVCONS.QTCONSPLAN
            WHERE
               CDFILIAL = C_PREVCONS.CDFILICPLA AND
               CDSERVFILI = C_PREVCONS.CDSERVCPLA AND
               CDPRODUTO = C_PREVCONS.CDPRODCPLA AND
               DTPREVCONS = C_PREVCONS.DTCONSPLAN;
         END;
      END;  
   
   
   END LOOP;
   COMMIT;

   DELETE FROM CONSPLAN
   WHERE
      CDFILICPLA = P_CDCENTRAL AND
      NRCONSPLAN = V_NRCONSPLAN;
   
    V_DT := P_DTINICIAL;
    WHILE V_DT <= P_DTFINAL   
    LOOP
        INSERT INTO FILTROPREVCONS
            (CDFILLANC, CDFILIAL, CDSERVFILI, DTPREVCONS, DTINCLUSAO, CDOPERINCLUSAO)
            (SELECT P_CDCENTRAL AS CDFILLANC, SUBSTR(CDLISTA, 1, 4) AS CDFILIAL, SUBSTR(CDLISTA, 5) AS CDSERVFILI, 
                       V_DT AS DTINICIAL, SYSDATE AS DTINCLUSAO, P_CDOPERADOR AS CDOPERINCLUSAO
                  FROM LISTA
                 WHERE NRLISTA = P_NRLISTAFILI);
                 
        V_DT := V_DT + 1;
    END LOOP;

   COMMIT;
END;
/