IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'P_CTB_CNT_DRE_RESULTADO')
          AND type IN(N'P', N'PC')
)
    BEGIN
        DROP PROCEDURE [P_CTB_CNT_DRE_RESULTADO]
    END;
GO

CREATE PROCEDURE [P_CTB_CNT_DRE_RESULTADO](@CD_EMP		INT, 
									@CD_FILIAL			VARCHAR(max),
									@ANO				INT,
									@MES				INT,
									@TP_DRE				INT,
									@TP_RAT_CENTO_CUSTO	INT,
									@CD_USU INT=1,
									@PROC_APENAS INT=0)
AS 
BEGIN 

SET NOCOUNT ON

	--DECLARE @CD_EMP INT
	--DECLARE @CD_FILIAL INT 
	--DECLARE @ANO INT
	--DECLARE @MES INT
	--DECLARE @TP_DRE INT
								  

	--SET @CD_EMP = 1
	--SET @CD_FILIAL = '1,2,3,4,5,6,7,8'
	--SET @ANO = 2012
	--SET @MES = 7
	--SET @TP_DRE = 1
	
	CREATE TABLE #RsFilial (CD_EMP INT,
							CD_FILIAL INT)

	CREATE TABLE #TBL_DRE_VD  (CD_EMP			INT,
								CD_FILIAL		INT, 
								ANO				INT, 
								MES				INT,
								CD_CNT_DRE		INT,
								VALOR			MONEY,
								TP_OP_DRE		INT,
								IS_FORMULA		INT)


										 
	CREATE TABLE #TBL_DRE_RES (ID						INT,
							    NIVEL_CNT_DRE			VARCHAR(40),
								CD_OP_CNT_DRE			INT, 
								IS_FORMULA				INT, 
								DS_CNT_DRE				VARCHAR(100), 
								VLR_MES_ANO_PAS			MONEY,	
								VLR_ACUM_ANO_PAS		MONEY,	
								VLR_MES_ANT				MONEY,
								VLR_MES_ATU				MONEY,	
								VLR_ACUM_ATU			MONEY,
								NIVEL_BASE_PERC_VERT	VARCHAR(40),
								PERC_HORZ_ACUM_ANO_PAS	MONEY,
								PERC_HORZ_MES_ANO_PAS	MONEY,
								PERC_HORZ_MES_ANT		MONEY,
								PERC_VERT_MES_ANT		MONEY,
								PERC_VERT_MES_ATU		MONEY,
								DT_BASE					DATETIME,
								DT_MES_ANT				DATETIME,
								ANO_PAS					INT,
								FLAG_SINAL				INT)					 

	DECLARE @VLR_MES_ANO_PAS	 MONEY
	DECLARE @VLR_ACUM_ANO_PAS	 MONEY
	DECLARE @VLR_MES_ANT		 MONEY
	DECLARE @VLR_MES_ATU		 MONEY
	DECLARE @VLR_ACUM_ATU		 MONEY
	DECLARE @DT_MES_ATU 		 DATETIME
	DECLARE @DT_MES_ANT 		 DATETIME
	DECLARE @DT_ANO_ANT 		 DATETIME

	DECLARE @SQL				 VARCHAR(MAX)
	DECLARE @SQLUNION			 VARCHAR(MAX)
	DECLARE @RS_CD_CNT_DRE		 INT
	DECLARE @RS_FORMULA			 VARCHAR(200)
	DECLARE @RS_CD_OP_CNT_DRE	 INT 
	DECLARE @RS_NIVEL_CNT_DRE	 VARCHAR(10)
	DECLARE @TP_DATA_RELA	     TINYINT 
	DECLARE @QTDEFILIALPARAMETRO TINYINT
	DECLARE @QTDEFILIALCONFDRE   TINYINT

	SET @TP_DATA_RELA			=@TP_DRE
								   
	SET @SQLUNION = ''
	SET @SQL = ''

	INSERT INTO #RsFilial
	SELECT 
		@CD_EMP, 
		ID 
	FROM 
		UFD_PRM_IN_STRING(@CD_FILIAL)
	

SET @QTDEFILIALPARAMETRO=ISNULL((SELECT
							COUNT(CD_FILIAL)
						 FROM
							#RsFilial),0)
SET @QTDEFILIALCONFDRE=ISNULL((SELECT
							COUNT(CD_FILIAL)
						 FROM
							PRC_FILIAL
						WHERE
							FLAG_ANALISE_DRE=1),0)

	--Se for regime de caixa, busca pela data do pagamento 
	IF @TP_DRE=1
	   SET @TP_DATA_RELA =4
	IF ISNULL(@TP_DRE,0) = 0 
		BEGIN
			SET @TP_DRE = 3
		END
	ELSE
		BEGIN
			SET @TP_DRE = 4
		END 
IF @QTDEFILIALPARAMETRO = @QTDEFILIALCONFDRE
BEGIN
	PRINT 'VARIAS FILIAIS'
	DELETE 
		CTB_CNT_DRE_RESULTADO
	WHERE 
		CTB_CNT_DRE_RESULTADO.CD_EMP=@CD_EMP 
		AND CTB_CNT_DRE_RESULTADO.CD_FILIAL=0
		AND CTB_CNT_DRE_RESULTADO.ANO=@ANO
		AND CTB_CNT_DRE_RESULTADO.MES=@MES
		AND CTB_CNT_DRE_RESULTADO.TP_DRE=@TP_DRE
		AND CTB_CNT_DRE_RESULTADO.TP_RAT_CENTO_CUSTO=@TP_RAT_CENTO_CUSTO	
END

IF EXISTS (SELECT 
				COUNT(CD_FILIAL)
			FROM
				#RsFilial
			HAVING 
				COUNT(CD_FILIAL) = 1)
BEGIN
	--Apagando historico para analises de comparacao
	DELETE 
		CTB_CNT_DRE_RESULTADO
	FROM 
		CTB_CNT_DRE_RESULTADO INNER JOIN #RsFilial f ON
		CTB_CNT_DRE_RESULTADO.CD_EMP = f.CD_EMP AND
		CTB_CNT_DRE_RESULTADO.CD_FILIAL = f.CD_FILIAL
	WHERE 
		CTB_CNT_DRE_RESULTADO.CD_EMP=@CD_EMP 
		AND CTB_CNT_DRE_RESULTADO.ANO=@ANO
		AND CTB_CNT_DRE_RESULTADO.MES=@MES
		AND CTB_CNT_DRE_RESULTADO.TP_DRE=@TP_DRE
		AND CTB_CNT_DRE_RESULTADO.TP_RAT_CENTO_CUSTO=@TP_RAT_CENTO_CUSTO
END
	----------------------------------------------------------------------------------
	--Capturando o ms anterior
	----------------------------------------------------------------------------------
	SET @DT_MES_ATU = CONVERT(VARCHAR(10), CAST(@ANO AS VARCHAR) + '-' + CAST(@MES AS VARCHAR) + '-01', 101)
	SET @DT_MES_ATU = CONVERT(VARCHAR(10), DBO.F_FINAL_MES(@DT_MES_ATU), 101)

	SET @DT_MES_ANT = DATEADD(MONTH ,-1, @DT_MES_ATU)

	SET @DT_ANO_ANT = CONVERT(VARCHAR(10), CAST(@ANO - 1 AS VARCHAR) + '-01-01', 101)

	------------------------------------------------------------------------------------------------------------------------
	-- MONTANDO O CURSOR COM AS SQL'S QUE EXECUTAR AS FORMULAS CADASTRADAS
	------------------------------------------------------------------------------------------------------------------------
	DECLARE RS_FORMULAS CURSOR FAST_FORWARD FOR 
		SELECT DR.CD_CNT_DRE, VD.FORMULA, DR.CD_OP_CNT_DRE 
		FROM CTB_CNT_DRE DR INNER JOIN CTB_CNT_DRE_CONSLD_VD VD ON 
				DR.CD_EMP = VD.CD_EMP AND 
				DR.CD_CNT_DRE = VD.CD_CNT_DRE
		WHERE DR.STS_CNT = 0
			AND VD.FORMULA <> ''
		 
		OPEN RS_FORMULAS;
	 
		FETCH NEXT FROM RS_FORMULAS INTO @RS_CD_CNT_DRE, @RS_FORMULA, @RS_CD_OP_CNT_DRE;

		WHILE @@FETCH_STATUS = 0
			BEGIN
				IF @RS_FORMULA <> '' AND NOT @RS_FORMULA IS NULL
					BEGIN 
						IF LEN(@SQLUNION) > 0 
							BEGIN
								SET @SQLUNION = @SQLUNION + ' UNION ALL '
							END 
	   
						SET @SQL = 'SELECT CD_EMP, CD_FILIAL,' + 
									'ANO_VD, MES_VD, ' + CAST(@RS_CD_CNT_DRE AS VARCHAR) + ' AS CD_CNT_DRE, ' +
									' ISNULL(' + @RS_FORMULA + ',0) AS VALOR, ' + CAST(@RS_CD_OP_CNT_DRE AS VARCHAR) + ' AS CD_OP_CNT_DRE, ' +
									'1 AS IS_FORMULA ' +
									'FROM CTB_CNT_DRE_CONSLD_VD_MES ' +
									'WHERE CD_EMP = ' + CAST(@CD_EMP AS VARCHAR) 	+ 
																															  
									'		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(''' + convert(varchar(max),@CD_FILIAL) + '''))' 	
	 
						SET @SQLUNION = @SQLUNION + @SQL 
					END
	 
				FETCH NEXT FROM RS_FORMULAS INTO @RS_CD_CNT_DRE, @RS_FORMULA, @RS_CD_OP_CNT_DRE;
   
			END
   
		CLOSE RS_FORMULAS;
		DEALLOCATE RS_FORMULAS;

				  
	------------------------------------------------------------------------------------------------------------------------
	-- EXECUTANDO AS SQL'S E INSERINDO O RESULTADO NA TABELA DE PRE-APURAO DO DRE
	------------------------------------------------------------------------------------------------------------------------
	INSERT INTO #TBL_DRE_VD    
	EXECUTE (@SQLUNION);
	 
  
	------------------------------------------------------------------------------------------------------------------------
	-- OBTENDO VALORES DBITOS DAS DESPESAS VINCULADAS AO DRE DO RELATRIO DE FLUXO ANALITICO
	------------------------------------------------------------------------------------------------------------------------	    
	INSERT INTO #TBL_DRE_VD  
		SELECT	T.CD_EMP, 
				T.CD_FILIAL, 
				YEAR(DT_SEL_REL) AS ANO, 
				MONTH(DT_SEL_REL) AS MES, 
				DR_CNT.CD_CNT_DRE,
				SUM(CASE WHEN @TP_DRE = 3 THEN ROUND(T.VLR_DP,2) ELSE ROUND(T.VLR_DESP,2) END) AS VALOR,
				DR.CD_OP_CNT_DRE,
				0 AS IS_FORMULA
		FROM UFD_REL_FLUXO_ANALIT(@CD_EMP,'0.0.0.00.0000', @DT_ANO_ANT ,@DT_MES_ATU, 0, @TP_DATA_RELA, @TP_RAT_CENTO_CUSTO, 1) T 
				INNER JOIN CTB_CNT_DRE_GLB_DESP_REC  DR_CNT ON
					T.CD_EMP = DR_CNT.CD_EMP AND
					T.CD_DESP_REC = DR_CNT.CD_DESP_REC
				INNER JOIN CTB_CNT_DRE DR ON 
					DR_CNT.CD_EMP = DR.CD_EMP AND
					DR_CNT.CD_CNT_DRE = DR.CD_CNT_DRE
				INNER JOIN #RsFilial f ON
				t.CD_EMP = f.CD_EMP AND
				t.CD_FILIAL = f.CD_FILIAL
		WHERE DR.STS_CNT = 0
																	 
				AND DR.TP_ORIG_DADOS IN (0,1)
												 
		GROUP BY 
				T.CD_EMP, 
				T.CD_FILIAL, 
				YEAR(DT_SEL_REL),
				MONTH(DT_SEL_REL),
				DR_CNT.CD_CNT_DRE,
				DR.CD_OP_CNT_DRE;			

	------------------------------------------------------------------------------------------------------------------------
	-- SOMANDO OS VALORES CREDITOS AGRUPANDO A CONTA DE RESULTADO DO DRE NA TABELA DE APURAO FINAL
	------------------------------------------------------------------------------------------------------------------------
	PRINT '@TP_DATA_RELA = '+CONVERT (VARCHAR ,@TP_DATA_RELA) + ' @CD_EMP = ' +CONVERT (VARCHAR ,@CD_EMP) + ' @DT_ANO_ANT = ' + CONVERT (VARCHAR ,@DT_ANO_ANT) +' @DT_MES_ATU = '+CONVERT (VARCHAR ,@DT_MES_ATU)
	INSERT INTO #TBL_DRE_VD  
		SELECT	T.CD_EMP, 
				T.CD_FILIAL, 
				YEAR(DT_SEL_REL) AS ANO, 
				MONTH(DT_SEL_REL) AS MES, 
				DR_CNT.CD_CNT_DRE,
				SUM(CASE WHEN @TP_DRE = 3 THEN ROUND(T.VLR_DP,2) ELSE ROUND(T.VLR_RECTA,2) END) AS VALOR,
				DR.CD_OP_CNT_DRE,
				0 AS IS_FORMULA
		FROM UFD_REL_FLUXO_ANALIT_RECEITA(@CD_EMP,'0.0.0.00.0000',@DT_ANO_ANT ,@DT_MES_ATU, 0, @TP_DATA_RELA) T
				INNER JOIN CTB_CNT_DRE_GLB_DESP_REC  DR_CNT ON
					T.CD_EMP = DR_CNT.CD_EMP AND
					T.CD_DESP_REC = DR_CNT.CD_DESP_REC
				INNER JOIN CTB_CNT_DRE DR ON 
					DR_CNT.CD_EMP = DR.CD_EMP AND
					DR_CNT.CD_CNT_DRE = DR.CD_CNT_DRE	
				INNER JOIN #RsFilial f ON
				t.CD_EMP = f.CD_EMP AND
				t.CD_FILIAL = f.CD_FILIAL
						
		WHERE 
			DR.STS_CNT = 0
			AND DR.TP_ORIG_DADOS IN (0,2)
		GROUP BY 
				T.CD_EMP, 
				T.CD_FILIAL, 
				YEAR(DT_SEL_REL),
				MONTH(DT_SEL_REL),
				DR_CNT.CD_CNT_DRE,
				DR.CD_OP_CNT_DRE;

	------------------------------------------------------------------------------------------------------------------------
	-- SOMANDO OS VALORES DAS MOVIMENTACOES POR MEIO DE NOTA FISCAL
	------------------------------------------------------------------------------------------------------------------------
	INSERT INTO #TBL_DRE_VD		
		SELECT	SAI.CD_EMP, 
				SAI.CD_FILIAL, 
				YEAR(SAI.DT_EMI_NF) AS ANO, 
				MONTH(SAI.DT_EMI_NF) AS MES, 
				DR_CNT.CD_CNT_DRE,
				SUM(SAI.VLR_NF) AS VALOR,
				DR.CD_OP_CNT_DRE,
				0 AS IS_FORMULA
		FROM EST_NF_SAI AS SAI
				INNER JOIN GLB_MOV_OP AS MOV
					ON SAI.CD_NT_MOV = MOV.CD_MOV_OP
				INNER JOIN CTB_CNT_DRE_GLB_DESP_REC AS DR_CNT ON
					SAI.CD_EMP = DR_CNT.CD_EMP AND
					MOV.CD_DESP_REC = DR_CNT.CD_DESP_REC
				INNER JOIN CTB_CNT_DRE DR ON 
					DR_CNT.CD_EMP = DR.CD_EMP AND
					DR_CNT.CD_CNT_DRE = DR.CD_CNT_DRE
				INNER JOIN EST_NF_SAI_NFE AS NFE
					ON SAI.CD_EMP = NFE.CD_EMP
					AND SAI.CD_FILIAL = NFE.CD_FILIAL
					AND SAI.CD_NF = NFE.CD_NF
		WHERE DR.STS_CNT = 0
				AND SAI.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
				AND YEAR(SAI.DT_EMI_NF) IN (@ANO - 1 , @ANO)		
				AND SAI.STS_NF = 0
		GROUP BY 
				SAI.CD_EMP, 
				SAI.CD_FILIAL, 
				YEAR(SAI.DT_EMI_NF),
				MONTH(SAI.DT_EMI_NF),
				DR_CNT.CD_CNT_DRE,
				DR.CD_OP_CNT_DRE
		UNION ALL
		SELECT	ENT.CD_EMP, 
				ENT.CD_FILIAL, 
				YEAR(ENT.DT_ENT) AS ANO, 
				MONTH(ENT.DT_ENT) AS MES, 
				DR_CNT.CD_CNT_DRE,
				SUM(ENT.VLR_NF) AS VALOR,
				DR.CD_OP_CNT_DRE,
				0 AS IS_FORMULA
		FROM EST_NF_ENT AS ENT
				INNER JOIN GLB_MOV_OP AS MOV
					ON ENT.CD_NT_MOV = MOV.CD_MOV_OP
				INNER JOIN CTB_CNT_DRE_GLB_DESP_REC AS DR_CNT ON
					ENT.CD_EMP = DR_CNT.CD_EMP AND
					MOV.CD_DESP_REC = DR_CNT.CD_DESP_REC
				INNER JOIN CTB_CNT_DRE DR ON 
					DR_CNT.CD_EMP = DR.CD_EMP AND
					DR_CNT.CD_CNT_DRE = DR.CD_CNT_DRE		
				INNER JOIN EST_NF_ENT_NFE AS NFE
					ON ENT.CD_EMP = NFE.CD_EMP
					AND ENT.CD_FILIAL = NFE.CD_FILIAL
					AND ENT.CD_NF = NFE.CD_NF
		WHERE DR.STS_CNT = 0
				AND ENT.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
				AND YEAR(ENT.DT_ENT) IN (@ANO - 1 , @ANO)		
				AND ENT.STS_NF = 0
		GROUP BY 
				ENT.CD_EMP, 
				ENT.CD_FILIAL, 
				YEAR(ENT.DT_ENT),
				MONTH(ENT.DT_ENT),
				DR_CNT.CD_CNT_DRE,
				DR.CD_OP_CNT_DRE;




													
	-----------------------------------------------------------------------------------------------------------------------
	-- SOMANDO OS VALORES AGRUPANDO A CONTA DE RESULTADO DO DRE NA TABELA DE APURAO FINAL
	------------------------------------------------------------------------------------------------------------------------
	INSERT INTO #TBL_DRE_RES    
		SELECT ROW_NUMBER() OVER (ORDER BY DR.NIVEL_CNT_DRE) AS ID,
		DR.NIVEL_CNT_DRE, 
		DR.CD_OP_CNT_DRE, 
		ISNULL(MIN(DR_VD.IS_FORMULA),0) AS IS_FORMULA, 
		DR.DS_CNT_DRE, 
		SUM(CASE WHEN DR_VD.MES = MONTH(@DT_MES_ATU)  AND DR_VD.ANO = YEAR(@DT_MES_ATU) -1 THEN ISNULL(DR_VD.VALOR,0) ELSE 0 END) AS VLR_MES_ANO_PAS,	
		SUM(CASE WHEN DR_VD.MES <= MONTH(@DT_MES_ATU) AND DR_VD.ANO = YEAR(@DT_MES_ATU) -1 THEN ISNULL(DR_VD.VALOR,0) ELSE 0 END) AS VLR_ACUM_ANO_PAS,	

		SUM(CASE WHEN DR_VD.MES = MONTH(@DT_MES_ANT)  AND DR_VD.ANO = YEAR(@DT_MES_ANT) THEN ISNULL(DR_VD.VALOR,0) ELSE 0 END) AS VLR_MES_ANT,
		SUM(CASE WHEN DR_VD.MES = MONTH(@DT_MES_ATU)  AND DR_VD.ANO = YEAR(@DT_MES_ATU) THEN ISNULL(DR_VD.VALOR,0) ELSE 0 END) AS VLR_MES_ATU,	
		SUM(CASE WHEN DR_VD.MES <= MONTH(@DT_MES_ATU) AND DR_VD.ANO = YEAR(@DT_MES_ATU) THEN ISNULL(DR_VD.VALOR,0) ELSE 0 END) AS VLR_ACUM_ATU,
  
		DR.NIVEL_BASE_PERC_VERT, 
  
		0 AS PERC_HORZ_ACUM_ANO_PAS	,
		0 AS PERC_HORZ_MES_ANO_PAS	,
		0 AS PERC_HORZ_MES_ANT		,
		0 AS PERC_VERT_MES_ANT		,
		0 AS PERC_VERT_MES_ATU		,
		@DT_MES_ATU AS DT_BASE		,
		@DT_MES_ANT AS DT_MES_ANT	,
		YEAR(@DT_ANO_ANT) AS ANO_PAS,
		ISNULL (DR.FLAG_SINAL,0)
  
  
	FROM CTB_CNT_DRE DR LEFT JOIN #TBL_DRE_VD DR_VD ON
			DR.CD_EMP = DR_VD.CD_EMP AND
			DR.CD_CNT_DRE = DR_VD.CD_CNT_DRE AND
			DR_VD.ANO BETWEEN YEAR(@DT_MES_ATU) -1 AND YEAR(@DT_MES_ATU)
   
	GROUP BY DR.CD_CNT_DRE, DR.CD_OP_CNT_DRE, DR.NIVEL_CNT_DRE, DR.DS_CNT_DRE, DR.NIVEL_BASE_PERC_VERT,DR.FLAG_SINAL	
	ORDER BY DR.NIVEL_CNT_DRE; 


	------------------------------------------------------------------------------------------------------------------------
	-- SOMANDO OS VALORES PARA AS CONTAS DE RESULTADO DEFINIDA COMO 'AGRUPADORES DE SUB-NIVEIS'
	------------------------------------------------------------------------------------------------------------------------
	UPDATE T SET 		
			VLR_MES_ANO_PAS		= ABS((SELECT SUM(R.VLR_MES_ANO_PAS  * (CASE R.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END))	FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE LIKE T.NIVEL_CNT_DRE + '%' AND R.CD_OP_CNT_DRE IN (0,1))),
			VLR_ACUM_ANO_PAS	= ABS((SELECT SUM(R.VLR_ACUM_ANO_PAS * (CASE R.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END))	FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE LIKE T.NIVEL_CNT_DRE + '%' AND R.CD_OP_CNT_DRE IN (0,1))),
			VLR_MES_ANT			= ABS((SELECT SUM(R.VLR_MES_ANT		 * (CASE R.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END))	FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE LIKE T.NIVEL_CNT_DRE + '%' AND R.CD_OP_CNT_DRE IN (0,1))),
			VLR_MES_ATU			= ABS((SELECT SUM(R.VLR_MES_ATU		 * (CASE R.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END))	FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE LIKE T.NIVEL_CNT_DRE + '%' AND R.CD_OP_CNT_DRE IN (0,1))),
			VLR_ACUM_ATU		= ABS((SELECT SUM(R.VLR_ACUM_ATU     * (CASE R.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END))	FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE LIKE T.NIVEL_CNT_DRE + '%' AND R.CD_OP_CNT_DRE IN (0,1)))
	FROM #TBL_DRE_RES T
	WHERE T.CD_OP_CNT_DRE = 2

	------------------------------------------------------------------------------------------------------------------------
	-- INICIALIZANDO VARIAVEIS PARA O CALCULO DAS CONTAS DE RESULTADO DEFINIDA COMO 'RESULTADO PARCIAL'
	------------------------------------------------------------------------------------------------------------------------
	SELECT 		
			TOP 1
			@VLR_MES_ANO_PAS	= (CASE WHEN T.CD_OP_CNT_DRE IN (2,3) AND T.IS_FORMULA = 0 AND @VLR_MES_ANO_PAS IS NOT NULL THEN @VLR_MES_ANO_PAS	ELSE T.VLR_MES_ANO_PAS 	END),			
			@VLR_ACUM_ANO_PAS	= (CASE WHEN T.CD_OP_CNT_DRE IN (2,3) AND T.IS_FORMULA = 0 AND @VLR_ACUM_ANO_PAS IS NOT NULL THEN @VLR_ACUM_ANO_PAS	ELSE T.VLR_ACUM_ANO_PAS END),			
			@VLR_MES_ANT		= (CASE WHEN T.CD_OP_CNT_DRE IN (2,3) AND T.IS_FORMULA = 0 AND @VLR_MES_ANT IS NOT NULL THEN @VLR_MES_ANT		ELSE T.VLR_MES_ANT 		END),			
			@VLR_MES_ATU		= (CASE WHEN T.CD_OP_CNT_DRE IN (2,3) AND T.IS_FORMULA = 0 AND @VLR_MES_ATU IS NOT NULL THEN @VLR_MES_ATU		ELSE T.VLR_MES_ATU 		END),			
			@VLR_ACUM_ATU		= (CASE WHEN T.CD_OP_CNT_DRE IN (2,3) AND T.IS_FORMULA = 0 AND @VLR_ACUM_ATU IS NOT NULL THEN @VLR_ACUM_ATU		ELSE T.VLR_ACUM_ATU 	END)

	FROM #TBL_DRE_RES T
	WHERE T.NIVEL_CNT_DRE = (SELECT MIN(NIVEL_CNT_DRE) FROM #TBL_DRE_RES R);

	------------------------------------------------------------------------------------------------------------------------
	-- CALCULANDO O VALOR PARA AS CONTAS DE RESULTADO DEFINIDA COMO 'RESULTADO PARCIAL' PELA ORDER DA ESTRUTURA DO DRE
	------------------------------------------------------------------------------------------------------------------------		
	UPDATE T SET 	
			@VLR_MES_ANO_PAS	= @VLR_MES_ANO_PAS + (T.VLR_MES_ANO_PAS  * (CASE T.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END)),
			VLR_MES_ANO_PAS		= (CASE WHEN ID = 1 THEN T.VLR_MES_ANO_PAS WHEN T.CD_OP_CNT_DRE = 3 AND T.IS_FORMULA = 0 THEN @VLR_MES_ANO_PAS ELSE T.VLR_MES_ANO_PAS END),
			
			@VLR_ACUM_ANO_PAS	= @VLR_ACUM_ANO_PAS + (T.VLR_ACUM_ANO_PAS  * (CASE T.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END)),
			VLR_ACUM_ANO_PAS	= (CASE WHEN ID = 1 THEN T.VLR_ACUM_ANO_PAS WHEN T.CD_OP_CNT_DRE = 3 AND T.IS_FORMULA = 0 THEN @VLR_ACUM_ANO_PAS ELSE T.VLR_ACUM_ANO_PAS END),
			
			@VLR_MES_ANT		= @VLR_MES_ANT + (T.VLR_MES_ANT  * (CASE T.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END)),
			VLR_MES_ANT			= (CASE WHEN ID = 1 THEN T.VLR_MES_ANT WHEN T.CD_OP_CNT_DRE = 3 AND T.IS_FORMULA = 0 THEN @VLR_MES_ANT ELSE T.VLR_MES_ANT END),
			
			@VLR_MES_ATU		= @VLR_MES_ATU + (T.VLR_MES_ATU  * (CASE T.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END)),
			VLR_MES_ATU			= (CASE WHEN ID = 1 THEN T.VLR_MES_ATU WHEN T.CD_OP_CNT_DRE = 3 AND T.IS_FORMULA = 0 THEN @VLR_MES_ATU ELSE T.VLR_MES_ATU END),
			
			@VLR_ACUM_ATU		= @VLR_ACUM_ATU + (T.VLR_ACUM_ATU  * (CASE T.CD_OP_CNT_DRE WHEN 0 THEN -1 WHEN 1 THEN 1 ELSE 0 END)),
			VLR_ACUM_ATU		= (CASE WHEN ID = 1 THEN T.VLR_ACUM_ATU WHEN T.CD_OP_CNT_DRE = 3 AND T.IS_FORMULA = 0 THEN @VLR_ACUM_ATU ELSE T.VLR_ACUM_ATU END)
			
	FROM #TBL_DRE_RES T

	------------------------------------------------------------------------------------------------------------------------
	-- CALCULANDO OS PERCENTUAIS HORIZONTAIS E VERTICAIS DAS LINHAS E COLUNHAS
	------------------------------------------------------------------------------------------------------------------------
	UPDATE T SET 		
			PERC_HORZ_ACUM_ANO_PAS  =  (CASE WHEN VLR_ACUM_ANO_PAS > 0	THEN 
												ROUND(((VLR_ACUM_ATU / VLR_ACUM_ANO_PAS) * 100) - 100, 3)		
										WHEN VLR_ACUM_ATU = 0	THEN 
												0 
										ELSE 
												-100 
										END),
										
			PERC_HORZ_MES_ANO_PAS	=  (CASE WHEN VLR_MES_ANO_PAS > 0	THEN 
												ROUND(((VLR_MES_ATU  / VLR_MES_ANO_PAS) * 100) - 100, 3)		
										WHEN VLR_MES_ATU = 0	THEN 
												0 
										ELSE 
												-100 
										END),
										
			PERC_HORZ_MES_ANT		=  (CASE WHEN VLR_MES_ANT > 0		THEN 
												ROUND(((VLR_MES_ATU  / VLR_MES_ANT) * 100) - 100, 3)			
										WHEN VLR_MES_ATU = 0	THEN 
												0 
										ELSE 
												-100 
										END),
			
			PERC_VERT_MES_ANT		=  (CASE WHEN (SELECT SUM(R.VLR_MES_ANT) FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE = T.NIVEL_BASE_PERC_VERT ) > 0 THEN 
												(((T.VLR_MES_ANT / (SELECT SUM(R.VLR_MES_ANT) FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE = T.NIVEL_BASE_PERC_VERT )) * 100))
										ELSE
												0
										END),
										
			PERC_VERT_MES_ATU		=  (CASE WHEN (SELECT SUM(R.VLR_MES_ATU) FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE = T.NIVEL_BASE_PERC_VERT ) > 0 THEN 
												(((T.VLR_MES_ATU / (SELECT SUM(R.VLR_MES_ATU) FROM #TBL_DRE_RES R WHERE R.NIVEL_CNT_DRE = T.NIVEL_BASE_PERC_VERT )) * 100))
										ELSE
												0
										END)																					

	FROM #TBL_DRE_RES T


	------------------------------------------------------------------------------------------------------------------------
	-- ATRIBUINDO O SINAL NAS COLUNAS DE VALORES DAS CONTAS DE RESULTADO COM ESTA DEFINI��O
	------------------------------------------------------------------------------------------------------------------------
	UPDATE T SET 		
			VLR_MES_ANO_PAS		= T.VLR_MES_ANO_PAS		* -1,
			VLR_ACUM_ANO_PAS	= T.VLR_ACUM_ANO_PAS	* -1,
			VLR_MES_ANT			= T.VLR_MES_ANT			* -1,
			VLR_MES_ATU			= T.VLR_MES_ATU			* -1,
			VLR_ACUM_ATU		= T.VLR_ACUM_ATU		* -1																								

	FROM #TBL_DRE_RES T
	WHERE	T.CD_OP_CNT_DRE <> 3
			AND T.FLAG_SINAL = 1

UPDATE
	T
SET 
	VLR_MES_ANO_PAS=0,	
	VLR_ACUM_ANO_PAS=0,	
	VLR_MES_ANT=0,
	VLR_MES_ATU=0,	
	VLR_ACUM_ATU=0,
	PERC_VERT_MES_ANT=0,
	PERC_VERT_MES_ATU=0
FROM
	#TBL_DRE_RES T
WHERE
	(VLR_MES_ANO_PAS IS NULL	
	OR VLR_ACUM_ANO_PAS IS NULL	
	OR VLR_MES_ANT IS NULL
	OR VLR_MES_ATU IS NULL	
	OR VLR_ACUM_ATU IS NULL
	OR PERC_VERT_MES_ANT IS NULL
	OR PERC_VERT_MES_ATU IS NULL)
	

IF @QTDEFILIALPARAMETRO = @QTDEFILIALCONFDRE
BEGIN
INSERT INTO CTB_CNT_DRE_RESULTADO
	(CD_EMP,
	CD_FILIAL,
	ANO,
	MES,
	TP_DRE,
	TP_RAT_CENTO_CUSTO,
	NIVEL_CNT_DRE,
	CD_OP_CNT_DRE,
	IS_FORMULA,
	DS_CNT_DRE,
	VLR_MES_ANO_PAS,
	VLR_ACUM_ANO_PAS,
	VLR_MES_ANT,
	VLR_MES_ATU,
	VLR_ACUM_ATU,
	NIVEL_BASE_PERC_VERT,
	PERC_HORZ_ACUM_ANO_PAS,
	PERC_HORZ_MES_ANO_PAS,
	PERC_HORZ_MES_ANT,
	PERC_VERT_MES_ANT,
	PERC_VERT_MES_ATU,
	DT_BASE,
	DT_MES_ANT,
	ANO_PAS,
	FLAG_SINAL,
	CD_USU,
	DT_CAD)
SELECT
	@CD_EMP, 
	0,
	@ANO,
	@MES,
	@TP_DRE,
	@TP_RAT_CENTO_CUSTO,
	NIVEL_CNT_DRE,
	CD_OP_CNT_DRE, 
	IS_FORMULA, 
	DS_CNT_DRE, 
	ISNULL(VLR_MES_ANO_PAS,0),	
	ISNULL(VLR_ACUM_ANO_PAS,0),	
	ISNULL(VLR_MES_ANT,0),
	ISNULL(VLR_MES_ATU,0),	
	ISNULL(VLR_ACUM_ATU,0),
	NIVEL_BASE_PERC_VERT,
	PERC_HORZ_ACUM_ANO_PAS,
	PERC_HORZ_MES_ANO_PAS,
	PERC_HORZ_MES_ANT,
	ISNULL(PERC_VERT_MES_ANT,0),
	ISNULL(PERC_VERT_MES_ATU,0),
	DT_BASE,
	DT_MES_ANT,
	ANO_PAS,
	FLAG_SINAL,
	@CD_USU,
	GETDATE()
FROM
	#TBL_DRE_RES
END

IF EXISTS (SELECT 
				COUNT(CD_FILIAL)
			FROM
				#RsFilial
			HAVING 
				COUNT(CD_FILIAL) = 1)
BEGIN
DECLARE @CD_FILIAL_GRAVAR INT
SET @CD_FILIAL_GRAVAR = (SELECT
							TOP 1 CD_FILIAL
						 FROM
							#RsFilial)
INSERT INTO CTB_CNT_DRE_RESULTADO
	(CD_EMP,
	CD_FILIAL,
	ANO,
	MES,
	TP_DRE,
	TP_RAT_CENTO_CUSTO,
	NIVEL_CNT_DRE,
	CD_OP_CNT_DRE,
	IS_FORMULA,
	DS_CNT_DRE,
	VLR_MES_ANO_PAS,
	VLR_ACUM_ANO_PAS,
	VLR_MES_ANT,
	VLR_MES_ATU,
	VLR_ACUM_ATU,
	NIVEL_BASE_PERC_VERT,
	PERC_HORZ_ACUM_ANO_PAS,
	PERC_HORZ_MES_ANO_PAS,
	PERC_HORZ_MES_ANT,
	PERC_VERT_MES_ANT,
	PERC_VERT_MES_ATU,
	DT_BASE,
	DT_MES_ANT,
	ANO_PAS,
	FLAG_SINAL,
	CD_USU,
	DT_CAD)
SELECT
	@CD_EMP, 
	@CD_FILIAL_GRAVAR,
	@ANO,
	@MES,
	@TP_DRE,
	@TP_RAT_CENTO_CUSTO,
	NIVEL_CNT_DRE,
	CD_OP_CNT_DRE, 
	IS_FORMULA, 
	DS_CNT_DRE, 
	ISNULL(VLR_MES_ANO_PAS,0),	
	ISNULL(VLR_ACUM_ANO_PAS,0),	
	ISNULL(VLR_MES_ANT,0),
	ISNULL(VLR_MES_ATU,0),	
	ISNULL(VLR_ACUM_ATU,0),
	ISNULL(NIVEL_BASE_PERC_VERT,0),
	ISNULL(PERC_HORZ_ACUM_ANO_PAS,0),
	ISNULL(PERC_HORZ_MES_ANO_PAS,0),
	ISNULL(PERC_HORZ_MES_ANT,0),
	ISNULL(PERC_VERT_MES_ANT,0),
	ISNULL(PERC_VERT_MES_ATU,0),
	DT_BASE,
	DT_MES_ANT,
	ANO_PAS,
	FLAG_SINAL,
	@CD_USU,
	GETDATE()
FROM
	#TBL_DRE_RES
END
IF @PROC_APENAS=0
BEGIN
	------------------------------------------------------------------------------------------------------------------------
	-- DIVULGANDO O RESULTADO DO DRE COMO RETORNO DA PROCEDURE
	------------------------------------------------------------------------------------------------------------------------
	SELECT * 
	FROM #TBL_DRE_RES  
	ORDER BY NIVEL_CNT_DRE
END  
END

DROP TABLE #TBL_DRE_VD; 
DROP TABLE #TBL_DRE_RES;