CREATE OR REPLACE PROCEDURE RECALCULO_ORCAFIN
AS
	 
	V_IDTPORCAFINA VARCHAR2(1);
	V_IDCONSORCFIN VARCHAR2(1);
	V_SOMACONTASMES FLOAT;
	V_SOMAAJUSTEACRES FLOAT;
	V_SOMAAJUSTEDESC FLOAT;
	V_SOMATOTAL FLOAT;
	V_COUNTORCAFIN FLOAT;
	V_FILIALPADRAO FILIAL.CDFILIAL%TYPE; 
    
BEGIN
   BEGIN
	  SELECT IDTPORCAFINA,
				IDCONSORCFIN
		 INTO V_IDTPORCAFINA, 
				V_IDCONSORCFIN		
	  FROM
		  PARAMGERAL;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		   V_IDTPORCAFINA := NULL; 
			V_IDCONSORCFIN	:= NULL;	
	END; 
	
	--Se utiliza consitencia do orçamento Financeiro
	IF (V_IDCONSORCFIN = 'S') THEN 
	
		FOR C_DATAS IN -- Recálculo é rodado considerando mes atual + doze meses pra frente
			(SELECT DISTINCT DTMES
				FROM DATA
			  WHERE (DTDATA BETWEEN SYSDATE AND SYSDATE + 365)
				ORDER BY DTMES) LOOP

			-- Consiste orçamento por Empresa
			IF(V_IDTPORCAFINA = 'E') THEN 
				
				FOR C_EMPRESAS IN 
					(SELECT CDEMPRESA
						FROM EMPRESA 
					  ORDER BY CDEMPRESA ) LOOP
					  
					FOR C_CLASSFINS IN
					
						(SELECT CDCLASSFINA
					  	   FROM CLASSFINANC
						  ORDER BY CDCLASSFINA) LOOP
						  
						   V_SOMATOTAL := 0;
						  
					 	   BEGIN
								SELECT NVL(SUM(VRATUAPAG),0) 
								  INTO V_SOMACONTASMES
								  FROM
									  CONTAPAGAR CT,
									  NOTAFISCAL NF
								 WHERE
									(CT.CDFILIAL = NF.CDFILIAL)     AND
									(CT.NRLANCTONF = NF.NRLANCTONF) AND
									(NF.IDSTATUS IN ('A', 'E', 'V'))   AND
									(CT.CDEMPRESA    = C_EMPRESAS.CDEMPRESA)   AND
									(CT.CDCLASSFINA  = C_CLASSFINS.CDCLASSFINA) AND
									(CT.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)); 
                     EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_SOMACONTASMES := 0; 
							END;
							
							BEGIN
								SELECT NVL(SUM(MF.VRFXAJBOLPAG),0) 									 
								  INTO V_SOMAAJUSTEACRES
								  FROM MOVFXAJBOLP MF,
										 TIPOAJPAGAR TP
								 WHERE (MF.CDEMPRESA   = C_EMPRESAS.CDEMPRESA) AND
										 (MF.CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (MF.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)) AND
										 (TP.IDAJUCTPAG = 'A') AND
										 (TP.CDAJUCTPAG  = MF.CDAJUCTPAG);
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_SOMAAJUSTEACRES := 0; 
							END;
							
							BEGIN
								SELECT NVL(SUM(MF.VRFXAJBOLPAG),0) 									 
								  INTO V_SOMAAJUSTEDESC
								  FROM MOVFXAJBOLP MF,
										 TIPOAJPAGAR TP
								 WHERE (MF.CDEMPRESA   = C_EMPRESAS.CDEMPRESA) AND
										 (MF.CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (MF.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)) AND
										 (TP.IDAJUCTPAG = 'D') AND
										 (TP.CDAJUCTPAG  = MF.CDAJUCTPAG);
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_SOMAAJUSTEDESC := 0; 
							END;
							
							V_SOMATOTAL := V_SOMACONTASMES + V_SOMAAJUSTEACRES - V_SOMAAJUSTEDESC;		

						   -- Busca filial padrão, caso não exista assume-se '9999'
						   BEGIN
								SELECT NVL(MAX(CDFILIAL),'9999') 
								  INTO V_FILIALPADRAO
								  FROM ORCAFINA
								 WHERE CDEMPRESA = C_EMPRESAS.CDEMPRESA;
						   EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_FILIALPADRAO := '9999'; 							
							END;
							 
							BEGIN
								SELECT COUNT(CDEMPRESA)
								  INTO V_COUNTORCAFIN
								  FROM ORCAFINA
							    WHERE(CDEMPRESA   = C_EMPRESAS.CDEMPRESA)   AND
										(CDFILIAL    = V_FILIALPADRAO)    AND
										(CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										(DTORCAFINA  = C_DATAS.DTMES);														
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_COUNTORCAFIN := 0; 							
							END;
							
							IF (V_COUNTORCAFIN = 0) THEN	
								IF (V_SOMATOTAL <> 0) THEN
									INSERT INTO ORCAFINA
										(CDEMPRESA, CDCLASSFINA, DTORCAFINA, VRREALORCA, CDFILIAL)
									VALUES
										(C_EMPRESAS.CDEMPRESA, C_CLASSFINS.CDCLASSFINA,C_DATAS.DTMES, V_SOMATOTAL, V_FILIALPADRAO);
								END IF;
							ELSE
								UPDATE ORCAFINA
									SET VRREALORCA   = V_SOMATOTAL
								 WHERE (CDEMPRESA   = C_EMPRESAS.CDEMPRESA)   AND
										 (CDFILIAL    = V_FILIALPADRAO)    AND
										 (CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (DTORCAFINA  = C_DATAS.DTMES);							
							END IF;
							COMMIT;
					END LOOP;								  
				END LOOP;
			ELSE -- Consiste Orçamento por Filial
			
				FOR C_EMPRESAS IN 
					(SELECT CDEMPRESA
						FROM EMPRESA 
					  ORDER BY CDEMPRESA ) LOOP
			
					FOR C_FILIAIS IN
						(SELECT CDFILIAL
							FROM FILIAL
						  WHERE CDEMPRESA = C_EMPRESAS.CDEMPRESA
						  ORDER BY CDFILIAL) LOOP
						  
						FOR C_CLASSFINS IN					
						(SELECT CDCLASSFINA
					  	   FROM CLASSFINANC
						  ORDER BY CDCLASSFINA) LOOP
						  
						   V_SOMATOTAL := 0;						  
						  
							BEGIN
								SELECT NVL(SUM(VRATUAPAG),0) 
								  INTO V_SOMACONTASMES
								FROM
									CONTAPAGAR CT,NOTAFISCAL NF
								WHERE
									(CT.CDFILIAL = NF.CDFILIAL)     AND
									(CT.NRLANCTONF = NF.NRLANCTONF) AND
									(NF.IDSTATUS IN ('A','V'))         AND
									(CT.CDEMPRESA    = C_EMPRESAS.CDEMPRESA)   AND
									(CDFIMOVFLPAG    = C_FILIAIS.CDFILIAL)    AND
									(CT.CDCLASSFINA  = C_CLASSFINS.CDCLASSFINA) AND
									(CT.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)); 						  
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
									V_SOMACONTASMES := 0;
							END; 
							
							BEGIN
								SELECT NVL(SUM(MF.VRFXAJBOLPAG),0) 									 
								  INTO V_SOMAAJUSTEACRES
								  FROM MOVFXAJBOLP MF,
										 TIPOAJPAGAR TP
								 WHERE (MF.CDEMPRESA   = C_EMPRESAS.CDEMPRESA) AND
										 (MF.CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (MF.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)) AND
										 (TP.IDAJUCTPAG = 'A') AND
										 (TP.CDAJUCTPAG  = MF.CDAJUCTPAG);
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_SOMAAJUSTEACRES := 0; 
							END;
							
							BEGIN
								SELECT NVL(SUM(MF.VRFXAJBOLPAG),0) 									 
								  INTO V_SOMAAJUSTEDESC
								  FROM MOVFXAJBOLP MF,
										 TIPOAJPAGAR TP
								 WHERE (MF.CDEMPRESA   = C_EMPRESAS.CDEMPRESA) AND
										 (MF.CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (MF.CDFILIAL    = C_FILIAIS.CDFILIAL) AND
										 (MF.DTMOVFLUXO BETWEEN FIRST_DAY(C_DATAS.DTMES) AND LAST_DAY(C_DATAS.DTMES)) AND
										 (TP.IDAJUCTPAG = 'D') AND
										 (TP.CDAJUCTPAG  = MF.CDAJUCTPAG);
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_SOMAAJUSTEDESC := 0; 
							END;
							
							V_SOMATOTAL := V_SOMACONTASMES + V_SOMAAJUSTEACRES - V_SOMAAJUSTEDESC;		
							
							BEGIN
								SELECT COUNT(CDEMPRESA)
								  INTO V_COUNTORCAFIN
								  FROM ORCAFINA
							    WHERE(CDEMPRESA   = C_EMPRESAS.CDEMPRESA)   AND
										(CDFILIAL    = C_FILIAIS.CDFILIAL)    AND
										(CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										(DTORCAFINA  = C_DATAS.DTMES);														
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
								V_COUNTORCAFIN := 0; 							
							END;
							
							IF (V_COUNTORCAFIN = 0) THEN	
								IF (V_SOMATOTAL <> 0) THEN
									INSERT INTO ORCAFINA
										(CDEMPRESA, CDCLASSFINA, DTORCAFINA, VRREALORCA, CDFILIAL)
									VALUES
										(C_EMPRESAS.CDEMPRESA, C_CLASSFINS.CDCLASSFINA,C_DATAS.DTMES, V_SOMATOTAL, C_FILIAIS.CDFILIAL);
                        END IF;
							ELSE
								UPDATE ORCAFINA
									SET VRREALORCA   = V_SOMATOTAL
								 WHERE (CDEMPRESA   = C_EMPRESAS.CDEMPRESA)   AND
										 (CDFILIAL    = C_FILIAIS.CDFILIAL)    AND
										 (CDCLASSFINA = C_CLASSFINS.CDCLASSFINA) AND
										 (DTORCAFINA  = C_DATAS.DTMES);						
							END IF;
							COMMIT;
                  END LOOP;							
					END LOOP;		
				END LOOP;
			END IF;	
		END LOOP;
	END IF;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('RECALCULO_ORCAFIN','6.25.000'); END;
/