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_CENTRAIS VARCHAR2(2000);
   V_PRXLISTA VARCHAR2(20);
   V_NRORG NUMBER;
BEGIN
   V_NRORG := NVL(SYS_CONTEXT('nrorg_ctx','nrorg'),1);
   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_CENTRAIS := '_';
    
   NOVO_CODIGO('LISTA', V_PRXLISTA, 1, V_NRORG);  
   V_PRXLISTA := SUBSTR(V_PRXLISTA,11,10);  

	-- TRATA AS CENTRAIS
	FOR C_CENTRAL IN
		(SELECT FILIAL.CDFILIAL, FILIAL.IDFILCENTPME
		  FROM FILIAL
	  	 WHERE (INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND
		       (P_CDGRUPFILI IS NULL) 
		UNION ALL
		SELECT FILGRUPFILI.CDFILIAL, FILIAL.IDFILCENTPME
		  FROM FILGRUPFILI, FILIAL
		 WHERE (INSTR(P_CDFILIAL, FILGRUPFILI.CDFILIAL) > 0 OR P_TCDFILIAL = 'T') AND
			   (FILGRUPFILI.CDGRUPFILI = P_CDGRUPFILI) AND
			   (FILIAL.CDFILIAL = FILGRUPFILI.CDFILIAL)
		 ORDER BY 1) 
	LOOP
		IF C_CENTRAL.IDFILCENTPME = 'S' AND INSTR(V_CENTRAIS, C_CENTRAL.CDFILIAL) = 0 THEN
			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;

			FOR CUR_SERVAGREG IN (
				SELECT DISTINCT F.CDFILIAL, S.CDSERVFILI
				  FROM FILIAL FF
				 INNER JOIN( SELECT CDFILIAGREGA AS CDFILIAL
							   FROM CELULAPROD
							  WHERE INSTR(C_CENTRAL.CDFILIAL, CDCENTRAL)<> 0
							  UNION ALL
							 SELECT F.CDFILIAL
							   FROM FILIAL F
							  WHERE INSTR(C_CENTRAL.CDFILIAL, F.CDFILCENTPME)<> 0
					   ) F ON F.CDFILIAL = FF.CDFILIAL
				 INNER JOIN SERVICO S ON S.CDFILIAL = F.CDFILIAL AND S.IDSERVTRAN = 'S'					
				 WHERE FF.IDCNTRRESC = 'N')
			LOOP			
				INSERT INTO LISTA(NRLISTA, CDLISTA, NRORG, NRORGULTATU, NRORGINCLUSAO)
				VALUES (V_PRXLISTA, CUR_SERVAGREG.CDFILIAL||CUR_SERVAGREG.CDSERVFILI, V_NRORG, V_NRORG, V_NRORG);
			END LOOP;

			V_CENTRAIS := V_CENTRAIS||'_'||C_CENTRAL.CDFILIAL;

			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 P_CDPRODUTOFIN
							   AND PRODUTO.CDPRODUTO = PREVCONS.CDPRODUTO));

			PREVISAO_DE_CONSUMO_PLANPROD(C_CENTRAL.CDFILIAL,
										V_PRXLISTA,
										V_DATAPROC,
										P_DTFINAL,
										P_CDPRODUTOINI,
										P_CDPRODUTOFIN||'Z',
										P_PEACRESCIMO,
										P_IDARREDOND,
										P_CDOPERADOR,
										'N');

			DELETE FROM LISTA WHERE NRLISTA = V_PRXLISTA;
		END IF;
	END LOOP;

	COMMIT;
END; 
/
