create or replace PROCEDURE CALCULA_PREVCONS_FILIAIS  
  (P_CDFILIAL     IN VARCHAR2,  
   P_TCDFILIAL    IN VARCHAR2,  
   P_CDGRUPFILI   IN VARCHAR2,  
   P_DTFINAL      IN DATE,  
   P_PEACRESCIMO  IN NUMBER,  
   P_CDPRODUTOINI IN VARCHAR2,  
   P_CDPRODUTOFIN IN VARCHAR2,  
   P_CDOPERADOR   IN VARCHAR2,  
   P_DTHRCALCPC   IN DATE,  
   P_IDARREDOND   IN VARCHAR2,  
   P_STDTINICIAL  IN VARCHAR2 DEFAULT NULL,  
   P_STDTFINAL    IN VARCHAR2 DEFAULT NULL) IS  
   V_DATAPROC  DATE;  
   V_IDUTILDTPROC VARCHAR2(1);  
   V_PRXCONSPLAN VARCHAR2(20);  
   V_CDPRODUTOFIN VARCHAR2(50);  
   V_FILIAL VARCHAR2(4); 
   V_PRXLISTA VARCHAR2(20); 
   V_PRXLISTACENT VARCHAR2(20); 
BEGIN  
   V_IDUTILDTPROC := '';  
   SELECT  
      MAX(IDUTILDTPROC)  
   INTO  
      V_IDUTILDTPROC  
   FROM  
      PARAMGERAL;  
   FOR CUR_FILIAL IN  
      (SELECT  
          FILIAL.CDFILIAL  
       FROM  
          FILIAL  
       WHERE  
          (INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND  
          (P_CDGRUPFILI IS NULL)  
       UNION ALL  
       SELECT  
          FILGRUPFILI.CDFILIAL  
       FROM  
          FILGRUPFILI  
       WHERE  
          (INSTR(P_CDFILIAL, FILGRUPFILI.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND  
          (FILGRUPFILI.CDGRUPFILI = P_CDGRUPFILI))  
   LOOP        
      V_PRXCONSPLAN  := '';  
      V_CDPRODUTOFIN := '';  
      V_CDPRODUTOFIN := P_CDPRODUTOFIN;  
  
      IF V_IDUTILDTPROC <> 'S' THEN  
         V_DATAPROC := TRUNC(SYSDATE);  
      ELSE  
         SELECT  
            MAX(NVL(DTPROCESSA, TRUNC(SYSDATE)))  
         INTO  
            V_DATAPROC  
         FROM  
            PARAMFILIAL  
         WHERE  
            CDFILIAL = CUR_FILIAL.CDFILIAL;  
      END IF;  
      NOVO_CODIGO('CONSPLAN'||CUR_FILIAL.CDFILIAL, V_PRXCONSPLAN);  
      V_PRXCONSPLAN := SUBSTR(V_PRXCONSPLAN,11,10);  
      PREVISAO_DE_CONSUMO_PLA(CUR_FILIAL.CDFILIAL,  
                              'T',   
                              V_DATAPROC,  
                              P_DTFINAL,  
                              P_PEACRESCIMO,  
                              P_CDPRODUTOINI,  
                              V_CDPRODUTOFIN,  
                              P_CDOPERADOR,  
                              P_DTHRCALCPC,  
                              V_PRXCONSPLAN,  
                              P_IDARREDOND,  
                              P_STDTINICIAL,  
                              P_STDTFINAL);  
   END LOOP;  
   COMMIT;  
    
    
 
   V_FILIAL := '';   
   V_CDPRODUTOFIN := P_CDPRODUTOFIN||'Z'; 
    
   NOVO_CODIGO('LISTA', V_PRXLISTA);  
   V_PRXLISTA := SUBSTR(V_PRXLISTA,11,10);  
    
   NOVO_CODIGO('LISTA', V_PRXLISTACENT);  
   V_PRXLISTACENT := SUBSTR(V_PRXLISTACENT,11,10);  
    
   FOR CUR_FILIAL IN  
      (SELECT  
          FILIAL.CDFILIAL, SERVICO.CDSERVFILI, FILIAL.IDFILCENTPME 
       FROM  
          FILIAL, SERVICO   
       WHERE  
          (INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND  
          (P_CDGRUPFILI IS NULL) AND 
          (FILIAL.CDFILIAL = SERVICO.CDFILIAL) AND 
          (SERVICO.IDSERVTRAN = 'S') 
       UNION ALL  
       SELECT  
          FILGRUPFILI.CDFILIAL, SERVICO.CDSERVFILI, FILIAL.IDFILCENTPME  
       FROM  
          FILGRUPFILI, FILIAL, SERVICO  
       WHERE  
          (INSTR(P_CDFILIAL, FILGRUPFILI.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND  
          (FILGRUPFILI.CDGRUPFILI = P_CDGRUPFILI) AND  
          (FILIAL.CDFILIAL = FILGRUPFILI.CDFILIAL) AND 
          (FILIAL.CDFILIAL = SERVICO.CDFILIAL) AND 
          (SERVICO.IDSERVTRAN = 'S') 
        ORDER BY 1  ) 
   LOOP        
      IF V_FILIAL IS NULL OR V_FILIAL <> CUR_FILIAL.CDFILIAL THEN 
         IF CUR_FILIAL.IDFILCENTPME = 'S' THEN  
            INSERT INTO LISTA(NRLISTA, CDLISTA) 
            VALUES (V_PRXLISTACENT, CUR_FILIAL.CDFILIAL); 
         END IF; 
      END IF;    
      V_FILIAL := CUR_FILIAL.CDFILIAL; 
       
      INSERT INTO LISTA(NRLISTA, CDLISTA) 
      VALUES (V_PRXLISTA, CUR_FILIAL.CDFILIAL||CUR_FILIAL.CDSERVFILI); 
   END LOOP; 
    
   FOR C_CENTRAL IN  
      (SELECT CDLISTA AS CDFILIAL 
       FROM LISTA 
       WHERE NRLISTA = V_PRXLISTACENT) 
   LOOP 
 
      IF V_IDUTILDTPROC <> 'S' THEN  
         V_DATAPROC := TRUNC(SYSDATE);  
      ELSE  
         SELECT  
            MAX(NVL(DTPROCESSA, TRUNC(SYSDATE)))  
         INTO  
            V_DATAPROC  
         FROM  
            PARAMFILIAL  
         WHERE  
            CDFILIAL = C_CENTRAL.CDFILIAL;  
      END IF;     
       
      DELETE FROM PREVCONS 
      WHERE EXISTS(SELECT CDLISTA 
                   FROM LISTA 
                   WHERE NRLISTA = V_PRXLISTA 
                     AND SUBSTR(CDLISTA, 1, 4) = CDFILIAL 
                     AND SUBSTR(CDLISTA, 5, 5) = CDSERVFILI) 
            AND (DTPREVCONS BETWEEN V_DATAPROC AND P_DTFINAL)  
            AND (EXISTS (SELECT CDPRODUTO  
                         FROM PRODUTO  
                        WHERE CDARVPROD BETWEEN P_CDPRODUTOINI AND V_CDPRODUTOFIN  
                          AND PRODUTO.CDPRODUTO = PREVCONS.CDPRODUTO));  
      
     PREVISAO_DE_CONSUMO_PLANPROD (C_CENTRAL.CDFILIAL, 
                                   V_PRXLISTA, 
                                   V_DATAPROC,
                                   P_DTFINAL, 
                                   P_CDPRODUTOINI, 
                                   V_CDPRODUTOFIN, 
                                   P_PEACRESCIMO, 
                                   P_IDARREDOND, 
                                   P_CDOPERADOR, 
                                   'N'); 
      
      
       
   END LOOP; 
    
   DELETE FROM LISTA WHERE NRLISTA = V_PRXLISTA; 
   DELETE FROM LISTA WHERE NRLISTA = V_PRXLISTACENT; 
    
   COMMIT;  
END; 
/
