/****** Object:  StoredProcedure [dbo].[P_BC_QT_QD_QI]    Script Date: 08/08/2012 11:27:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_BC_QT_QD_QI]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_BC_QT_QD_QI]
GO
GO
--EXEC P_BC_QT_QD_QI 1, 1
CREATE PROCEDURE [dbo].[P_BC_QT_QD_QI] (@CD_EMP INT,
								@CD_GRP_ECON VARCHAR(50))
AS
BEGIN
	
	SET NOCOUNT ON

	DECLARE @DT_BASE DATETIME
	DECLARE @DS_GRP_REC_VENCER   VARCHAR(50)
	DECLARE @DS_GRP_REC_VENCIDA  VARCHAR(50)
	DECLARE @DS_GRP_PAGAR		 VARCHAR(50)
	DECLARE @CD_GRP				 INT
	
	SET @DS_GRP_REC_VENCER  = 'CONTAS A RECEBER (VENCER)'
	SET @DS_GRP_REC_VENCIDA = 'CONTAS A RECEBER (VENCIDOS)'
	SET @DS_GRP_PAGAR		= 'CONTAS A PAGAR'


	SET @DT_BASE = CONVERT(VARCHAR(10), GETDATE(), 101)


	CREATE TABLE  #RS_RET ( DS_TP		VARCHAR(50),							
							VLR_TP		MONEY,
							NIVEL   	VARCHAR(5),
							TP			INT)

	CREATE TABLE #RS_CART ( VLR_TP		MONEY,
							 TP_VLR		INT)	

	CREATE TABLE #PRC_FILIAL_GRP (CD_EMP INT, CD_FILIAL INT)


	----------------------------------------------------------------------------------------------------------------
	--FILTRANDO OS GRUPOS ECONOMICOS
	----------------------------------------------------------------------------------------------------------------
	INSERT INTO #PRC_FILIAL_GRP (CD_EMP, CD_FILIAL)
		SELECT GF.CD_EMP, GF.CD_FILIAL  
		FROM PRC_GRP_ECON_PRC_FILIAL GF
		WHERE GF.CD_EMP = @CD_EMP		
			AND GF.CD_GRP_ECON IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_GRP_ECON))

	----------------------------------------------------------------------------------------------------------------
	--CURSOR PARA CAPTURA DOS VALORES DE CARTÕES DE TODOS GRUPOS ECONOMICOS
	----------------------------------------------------------------------------------------------------------------
	DECLARE RS_CARTOES CURSOR FAST_FORWARD FOR 
		SELECT ID AS CD_GRP_ECON 
		FROM UFD_PRM_IN_STRING(@CD_GRP_ECON)
					
		OPEN RS_CARTOES;
		FETCH NEXT FROM RS_CARTOES INTO @CD_GRP;
			
		WHILE @@FETCH_STATUS = 0
			BEGIN		
				INSERT INTO #RS_CART
					 SELECT  ISNULL(SUM(VLR_SLD_VENC),0) AS VLR_TP, 0 AS TP_VLR
					 FROM UFD_RC_ADM_CARTAO_MOV_BAIXA(@CD_EMP, @CD_GRP, '01/01/2000', @DT_BASE - 1) 			
					 
				INSERT INTO #RS_CART
					 SELECT  ISNULL(SUM(VLR_SLD_VENC),0) AS VLR_TP, 1 AS TP_VLR
					 FROM UFD_RC_ADM_CARTAO_MOV_BAIXA(@CD_EMP, @CD_GRP, @DT_BASE, DATEADD(YEAR, 10, @DT_BASE)) 
					 			
				FETCH NEXT FROM RS_CARTOES INTO @CD_GRP;
			END  
			
	CLOSE RS_CARTOES;
	DEALLOCATE RS_CARTOES;


	----------------------------------------------------------------------------------------------------------------
	--MONTANDO A TABELA DE RETORNO
	----------------------------------------------------------------------------------------------------------------


	INSERT INTO #RS_RET

	--################################################################################################################
	--QUANTO TENHO
	--################################################################################################################
	SELECT	'QT' AS DS_TP,			
			0 AS VLR_TP,
			'1' AS NIVEL,
			1 AS TOTAL

	UNION 
		
	----------------------------------------------------------------------------------------------------------------
	--ESTOQUE NO PREÇO MEDIO
	----------------------------------------------------------------------------------------------------------------
	SELECT  'ESTOQUE (CUSTO MEDIO)' AS DS_TP, 			
			SUM(C.QT_EST * C.VLR_CUSTO_MD) AS VLR_TP, 
			'1.1' AS NIVEL,
			0 AS TOTAL
	FROM EST_PROD_CPL C INNER JOIN #PRC_FILIAL_GRP FL ON
			C.CD_EMP = FL.CD_EMP 
			AND C.CD_FILIAL = FL.CD_FILIAL 
	WHERE 
		C.CD_EMP = @CD_EMP
		AND C.STS_PROD = 0	
		AND C.TP_PROD = 0
		
	UNION

	----------------------------------------------------------------------------------------------------------------
	--SALDO BANCARIO
	----------------------------------------------------------------------------------------------------------------
	SELECT  'SALDO BANCARIO' AS DS_TP, 			
			ISNULL(SUM(DBO.UFD_BC_LANC_EXTRATO_SLD(1,CD_CNT, @DT_BASE,0)),0) AS VLR_TP, 
			'1.2' AS NIVEL,
			0 AS TOTAL
	FROM BC_CONTAS C
	WHERE STS_CNT = 0
		AND (TP_CNT IN (1,2)  OR (FLAG_POSICAO = 1 AND TP_CNT = 0))
		AND EXISTS (SELECT GF.CD_EMP 
					FROM BC_CONTAS_PRC_GRP_ECON GF
					WHERE GF.CD_EMP = C.CD_EMP 
						  AND GF.CD_CNT = C.CD_CNT
						  AND GF.CD_GRP_ECON IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_GRP_ECON)))

		
	UNION

	--################################################################################################################
	--CONTAS A RECEBER (VENCIDOS)
	--################################################################################################################
	SELECT	@DS_GRP_REC_VENCIDA AS DS_TP,			
			0 AS VLR_TP,
			'1.3' AS NIVEL,
			1 AS TOTAL

	UNION 
			
	----------------------------------------------------------------------------------------------------------------
	--CONVÊNIOS VENCIDAS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CONVENIOS' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.3.1' AS NIVEL,
			0 AS TOTAL	
	FROM  V_RC_DEB_VLR_SLD R INNER JOIN RC_DEB_RC_CLI_CONV C ON 
				R.CD_EMP = C.CD_EMP 
				AND R.CD_FILIAL = C.CD_FILIAL 
				AND R.CD_RC_DEB = C.CD_RC_DEB 
			INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 
	WHERE  R.CD_EMP = @CD_EMP            
		AND R.DT_VENCTO < @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5) 		  
	
	UNION

	----------------------------------------------------------------------------------------------------------------
	--CARTOES VENCIDOS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CARTOES VENCIDOS' AS DS_TP,
			ISNULL(SUM(VLR_TP),0) AS VLR_TP,
			'1.3.2' AS NIVEL,
			0 AS TOTAL 
	FROM #RS_CART 
	WHERE TP_VLR = 0
	
	UNION
	
	----------------------------------------------------------------------------------------------------------------
	--VERBAS VENCIDAS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'VERBAS FORNECEDORES' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.3.3' AS NIVEL,
			0 AS TOTAL
	FROM  V_RC_DEB_VLR_SLD R INNER JOIN CG_FND_VERBA_RC_DEB_DUP_NR_CTR C ON 
				R.CD_EMP = C.CD_EMP 
				AND R.CD_FILIAL = C.CD_FILIAL 
				AND R.NR_DP = C.NR_DP 
				AND R.ANO  = C.ANO 
			INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 		
	WHERE  R.CD_EMP = @CD_EMP    
		AND R.DT_VENCTO < @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5)
	    
	UNION    

	----------------------------------------------------------------------------------------------------------------
	--CREDIARIO VENCIDOS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CREDIARIO' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.3.4' AS NIVEL,
			0 AS TOTAL
	FROM  V_RC_DEB_VLR_SLD R INNER JOIN RC_CREDIARIO_FECHA_RC_DEB C ON 
				R.CD_EMP = C.CD_EMP 
				AND R.CD_FILIAL = C.CD_FILIAL 
				AND R.CD_RC_DEB = C.CD_RC_DEB 
			INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 		
	WHERE  R.CD_EMP = @CD_EMP            
		AND R.DT_VENCTO < @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5)    
	    
	UNION

	----------------------------------------------------------------------------------------------------------------
	--CHEQUES VENCIDOS
	---------------------------------------------------------------------------------------------------------------- 
	SELECT  'CHEQUES' AS DS_TP, 
			ISNULL(SUM(C.VLR_CHQ),0) AS VLR_TP, 
			'1.3.5' AS NIVEL,
			0 AS TOTAL
	FROM V_CHQ_LANC_CPL_DEPOSITO C INNER JOIN #PRC_FILIAL_GRP FL ON
				C.CD_EMP = FL.CD_EMP 
				AND C.CD_FILIAL = FL.CD_FILIAL 
			LEFT JOIN CHQ_DEST_ENV_CPL ON 
				C.CD_EMP = CHQ_DEST_ENV_CPL.CD_EMP
			    AND C.CD_CHQ = CHQ_DEST_ENV_CPL.CD_CHQ					
	WHERE 
		C.CD_EMP = @CD_EMP 
		AND C.DT_VENCTO < @DT_BASE 
		AND ISNULL(CHQ_DEST_ENV_CPL.VLR_DESC, 0) + ISNULL(CHQ_DEST_ENV_CPL.VLR_RECB, 0) + ISNULL(CHQ_DEST_ENV_CPL.VLR_DESP, 0) <> C.VLR_CHQ
	 
	UNION     

	----------------------------------------------------------------------------------------------------------------
	--OUTOS CONTAS A RECEBER VENCIDO
	----------------------------------------------------------------------------------------------------------------    
	SELECT  'OUTROS CONTAS A RECEBER' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.3.6' AS NIVEL,
			0 AS TOTAL
	FROM  V_RC_DEB_VLR_SLD R  
		INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 	
	WHERE  R.CD_EMP = @CD_EMP            
		AND R.DT_VENCTO < @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5)     
	    
		AND NOT EXISTS (SELECT CD_EMP 
						FROM CG_FND_VERBA_RC_DEB_DUP_NR_CTR C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.NR_DP = C.NR_DP 
							AND R.ANO  = C.ANO )  
							
		AND NOT EXISTS (SELECT CD_EMP 
						FROM RC_DEB_RC_CLI_CONV C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.CD_RC_DEB = C.CD_RC_DEB)
			
		AND NOT EXISTS (SELECT CD_EMP 
						FROM RC_CREDIARIO_FECHA_RC_DEB C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.CD_RC_DEB = C.CD_RC_DEB )
	  
	UNION    

	--################################################################################################################
	--CONTAS A RECEBER (VENCER)
	--################################################################################################################
	SELECT	@DS_GRP_REC_VENCER AS DS_TP,			
			0 AS VLR_TP,
			'1.4' AS NIVEL,
			1 AS TOTAL

	UNION 
	    
	----------------------------------------------------------------------------------------------------------------
	--CONVÊNIOS A VENCER
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CONVENIOS' AS DS_TP, 
			ISNULL(SUM(R.VLR_TP),0) AS VLR_TP, 
			'1.4.1' AS NIVEL,
			0 AS TOTAL
	FROM (
			SELECT ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP
			FROM  V_RC_DEB_VLR_SLD R INNER JOIN RC_DEB_RC_CLI_CONV C ON 
						R.CD_EMP = C.CD_EMP 
						AND R.CD_FILIAL = C.CD_FILIAL 
						AND R.CD_RC_DEB = C.CD_RC_DEB 
					INNER JOIN #PRC_FILIAL_GRP FL ON
						R.CD_EMP = FL.CD_EMP 
						AND R.CD_FILIAL = FL.CD_FILIAL 
			WHERE  R.CD_EMP = @CD_EMP            
				AND R.DT_VENCTO >= @DT_BASE 
				AND R.VLR_SLD > 0
				AND R.STS_DP IN(0,2,5) 
				
			UNION
			
			SELECT ISNULL(SUM(VLR_CONV),0) AS VLR_TP
			FROM RC_VD_CONV 
			WHERE ST_FECHVD_CONV IN (0,5)) R
			
	UNION
	
	----------------------------------------------------------------------------------------------------------------
	--CARTOES A VENCER
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CARTOES A VENCER' AS DS_TP,
			ISNULL(SUM(VLR_TP),0) AS VLR_TP,
			'1.4.2' AS NIVEL,
			0 AS TOTAL 
	FROM #RS_CART 
	WHERE TP_VLR = 1	
	
	UNION
	
	----------------------------------------------------------------------------------------------------------------
	--VERBAS FORNECEDORES A VENCER
	----------------------------------------------------------------------------------------------------------------
	SELECT  'VERBAS FORNECEDORES' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.4.3' AS NIVEL,
			0 AS TOTAL
	FROM  V_RC_DEB_VLR_SLD R INNER JOIN CG_FND_VERBA_RC_DEB_DUP_NR_CTR C ON 
				R.CD_EMP = C.CD_EMP 
				AND R.CD_FILIAL = C.CD_FILIAL 
				AND R.NR_DP = C.NR_DP 
				AND R.ANO  = C.ANO 
			INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 			
	WHERE  R.CD_EMP = @CD_EMP             
		AND R.DT_VENCTO >= @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5)
	    
	UNION    

	----------------------------------------------------------------------------------------------------------------
	--CREDIARIO A VENCER
	----------------------------------------------------------------------------------------------------------------
	SELECT  'CREDIARIO' AS DS_TP, 
			ISNULL(SUM(R.VLR_TP),0) AS VLR_TP, 
			'1.4.4' AS NIVEL,
			0 AS TOTAL
	FROM (
			SELECT ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP
			FROM  V_RC_DEB_VLR_SLD R INNER JOIN RC_CREDIARIO_FECHA_RC_DEB C ON 
						R.CD_EMP = C.CD_EMP 
						AND R.CD_FILIAL = C.CD_FILIAL 
						AND R.CD_RC_DEB = C.CD_RC_DEB 
					INNER JOIN #PRC_FILIAL_GRP FL ON
						R.CD_EMP = FL.CD_EMP 
						AND R.CD_FILIAL = FL.CD_FILIAL 			
			WHERE  R.CD_EMP = @CD_EMP             
				AND R.DT_VENCTO >= @DT_BASE 
				AND R.VLR_SLD > 0
				AND R.STS_DP IN(0,2,5) 
				
			UNION 
			
			SELECT SUM(V.SLD_PARC_CRED) AS VLR_TP
			FROM RC_CREDIARIO_PARC V
					INNER JOIN RC_CREDIARIO_PARC_PDV_VD ON 
						V.CD_CTR = RC_CREDIARIO_PARC_PDV_VD.CD_CTR
			WHERE STS_LANC_CRED IN (0)) R
	    
	UNION

	----------------------------------------------------------------------------------------------------------------
	--CHEQUES A VENCER
	---------------------------------------------------------------------------------------------------------------- 
	SELECT  'CHEQUES' AS DS_TP, 
			ISNULL(SUM(C.VLR_CHQ),0) AS VLR_TP, 
			'1.4.5' AS NIVEL,
			0 AS TOTAL
	FROM V_CHQ_LANC_CPL_DEPOSITO C
			INNER JOIN #PRC_FILIAL_GRP FL ON
				C.CD_EMP = FL.CD_EMP 
				AND C.CD_FILIAL = FL.CD_FILIAL 	
			LEFT JOIN CHQ_DEST_ENV_CPL ON 
				C.CD_EMP = CHQ_DEST_ENV_CPL.CD_EMP
			    AND C.CD_CHQ = CHQ_DEST_ENV_CPL.CD_CHQ				
	WHERE 
		C.CD_EMP = @CD_EMP 
		AND C.DT_VENCTO >= @DT_BASE 
		AND ISNULL(CHQ_DEST_ENV_CPL.VLR_DESC, 0) + ISNULL(CHQ_DEST_ENV_CPL.VLR_RECB, 0) + ISNULL(CHQ_DEST_ENV_CPL.VLR_DESP, 0) <> C.VLR_CHQ
	 
	UNION     

	----------------------------------------------------------------------------------------------------------------
	--OUTROS CONTAS A RECEBER A VENCER
	----------------------------------------------------------------------------------------------------------------    
	SELECT  'OUTROS CONTAS A RECEBER' AS DS_TP, 
			ISNULL(SUM(R.VLR_SLD),0) AS VLR_TP, 
			'1.4.6' AS NIVEL,
			0 AS TOTAL
	FROM  V_RC_DEB_VLR_SLD R  
			INNER JOIN #PRC_FILIAL_GRP FL ON
				R.CD_EMP = FL.CD_EMP 
				AND R.CD_FILIAL = FL.CD_FILIAL 	
	WHERE  R.CD_EMP = @CD_EMP             
		AND R.DT_VENCTO >= @DT_BASE 
		AND R.VLR_SLD > 0
		AND R.STS_DP IN(0,2,5)     
	    
		AND NOT EXISTS (SELECT CD_EMP 
						FROM CG_FND_VERBA_RC_DEB_DUP_NR_CTR C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.NR_DP = C.NR_DP 
							AND R.ANO  = C.ANO )  
							
		AND NOT EXISTS (SELECT CD_EMP 
						FROM RC_DEB_RC_CLI_CONV C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.CD_RC_DEB = C.CD_RC_DEB)
			
		AND NOT EXISTS (SELECT CD_EMP 
						FROM RC_CREDIARIO_FECHA_RC_DEB C 
						WHERE 
							R.CD_EMP = C.CD_EMP 
							AND R.CD_FILIAL = C.CD_FILIAL 
							AND R.CD_RC_DEB = C.CD_RC_DEB )		

	UNION 


	--################################################################################################################
	--QUANTO DEVO
	--################################################################################################################
	SELECT	'QD' AS DS_TP,			
			0 AS VLR_TP,
			'2' AS NIVEL,
			1 AS TOTAL

	UNION 
		
	--################################################################################################################
	--CONTAS A PAGAR
	--################################################################################################################
	SELECT	@DS_GRP_PAGAR AS DS_TP,			
			0 AS VLR_TP,
			'2.1' AS NIVEL,
			1 AS TOTAL

	UNION 

	----------------------------------------------------------------------------------------------------------------
	--CONTAS A PAGAR - TITULOS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'TITULOS EM ABERTO' AS DS_TP, 
			SUM(( isnull(PG_CRED.SLD_DP,0) + CASE WHEN PG_CRED.STS_DP=0 THEN Isnull(PG_CRED.VLR_JUROS, 0) ELSE 0 END + 
			CASE WHEN PG_CRED.STS_DP=0 THEN Isnull(PG_CRED.VLR_DESP_OUTR, 0) ELSE 0 END ) - ( 
			CASE WHEN PG_CRED.STS_DP=0 THEN Isnull(PG_CRED.VLR_DESC, 0) ELSE 0 END + 
			CASE WHEN PG_CRED.STS_DP=0 THEN Isnull(V_PG_CRED_IMPOSTO.VLR_IMP, 0) ELSE 0 END)) AS VLR_TP, 
			'2.1.1' AS NIVEL,
			0 AS TOTAL
	FROM   PG_CRED         
		   INNER JOIN #PRC_FILIAL_GRP FL ON
				PG_CRED.CD_EMP = FL.CD_EMP 
				AND PG_CRED.CD_FILIAL = FL.CD_FILIAL 			
		   LEFT OUTER JOIN V_PG_CRED_IMPOSTO 
			 ON PG_CRED.CD_EMP = V_PG_CRED_IMPOSTO.CD_EMP 
				AND PG_CRED.CD_FILIAL = V_PG_CRED_IMPOSTO.CD_FILIAL 
				AND PG_CRED.CD_PG_CRED = V_PG_CRED_IMPOSTO.CD_PG_CRED        
	WHERE  PG_CRED.CD_EMP = @CD_EMP   
		   AND PG_CRED.STS_DP IN (0, 2 )  
		   AND PG_CRED.TP_DP <> 5      
		   AND NOT EXISTS (SELECT * 
						   FROM   PRC_FILIAL_PG_FORN 
						   WHERE  PG_CRED.CD_EMP = PRC_FILIAL_PG_FORN.CD_EMP 
								  AND PG_CRED.CD_FORN = PRC_FILIAL_PG_FORN.CD_FORN) 			

	UNION

	----------------------------------------------------------------------------------------------------------------
	--CONTAS A PAGAR - NOTA FISCAL SEM TITULO
	----------------------------------------------------------------------------------------------------------------
	SELECT  'NOTA FISCAL SEM CONTAS A PAGAR'  AS DS_TP,        
			ISNULL(SUM(NF.VLR_NF),0)      AS VLR_LIQ,  
			'2.1.2' AS NIVEL,
			0 AS TOTAL
	FROM   
		EST_NF_ENT NF INNER JOIN  GLB_MOV_OP  ON
		NF.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 	
		INNER JOIN #PRC_FILIAL_GRP FL ON
				NF.CD_EMP = FL.CD_EMP AND
				NF.CD_FILIAL = FL.CD_FILIAL   
	WHERE
		NF.CD_EMP  =@CD_EMP 
		AND ISNULL(NF.IGNORAR_GERACAO_FINAN,0) = 0 
		AND NOT EXISTS     (SELECT 
							CD_NF 
							FROM 
								EST_NF_ENT_FINANCEIRO_CANCEL CANCEL         
							WHERE 
								CANCEL.CD_EMP=NF.CD_EMP AND         
								CANCEL.CD_FILIAL=NF.CD_FILIAL AND         
								CANCEL.CD_NF=NF.CD_NF) 
		AND GLB_MOV_OP.GERA_DP_PG = 1  
		AND NOT EXISTS (SELECT      
							PG_CRED_EST_NF_ENT.CD_NF 
						FROM      
							PG_CRED_EST_NF_ENT     INNER JOIN PG_CRED ON      
							PG_CRED_EST_NF_ENT.CD_EMP = PG_CRED.CD_EMP AND     
							PG_CRED_EST_NF_ENT.CD_FILIAL = PG_CRED.CD_FILIAL  AND     
							PG_CRED_EST_NF_ENT.NR_DP = PG_CRED.NR_DP AND     
							PG_CRED_EST_NF_ENT.ANO = PG_CRED.ANO 
						WHERE     
							PG_CRED_EST_NF_ENT.CD_EMP = NF.CD_EMP AND     
							PG_CRED_EST_NF_ENT.CD_FILIAL = NF.CD_FILIAL  AND     
							PG_CRED_EST_NF_ENT.CD_NF = NF.CD_NF     
							AND PG_CRED.STS_DP <> 3) 

	UNION

	--################################################################################################################
	--QUANTO DEVO
	--################################################################################################################
	SELECT	'QI' AS DS_TP,			
			0 AS VLR_TP,
			'3' AS NIVEL,
			1 AS TOTAL

	UNION 
	
	----------------------------------------------------------------------------------------------------------------
	--INVESTIMENTOS
	----------------------------------------------------------------------------------------------------------------
	SELECT  'INVESTIMENTO' AS DS_TP,			
			ISNULL(SUM(DBO.UFD_BC_LANC_EXTRATO_SLD(1,C.CD_CNT, @DT_BASE,0)),0) AS VLR_TP, 
			'3.1' AS NIVEL,
			0 AS TOTAL
	FROM BC_CONTAS C
	WHERE C.STS_CNT = 0
		AND C.TP_CNT IN (6,10)	
		AND EXISTS (SELECT GF.CD_EMP 
					FROM BC_CONTAS_PRC_GRP_ECON GF
					WHERE GF.CD_EMP = C.CD_EMP 
						  AND GF.CD_CNT = C.CD_CNT
						  AND GF.CD_GRP_ECON IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_GRP_ECON)))			
	
	
	
	
	UPDATE RET SET 
		VLR_TP = (SELECT SUM(R.VLR_TP) FROM #RS_RET R WHERE R.NIVEL LIKE RET.NIVEL + '%' AND TP = 0)
	
	FROM #RS_RET RET
	WHERE TP = 1	
	
	INSERT INTO #RS_RET 
		SELECT  'RESULTADO QT/QD',
				SUM((CASE WHEN NIVEL = '1' THEN VLR_TP ELSE 0 END)) / SUM((CASE WHEN NIVEL = '2' THEN VLR_TP ELSE 0 END)) AS VLR_TP,
				'6' AS NIVEL,
				2 AS TOTAL
		FROM #RS_RET 
		WHERE TP = 1

	INSERT INTO #RS_RET 
		SELECT  'RESULTADO (QT + QI)/QD',
				SUM((CASE WHEN NIVEL IN ('1','3') THEN VLR_TP ELSE 0 END)) / SUM((CASE WHEN NIVEL = '2' THEN VLR_TP ELSE 0 END)) AS VLR_TP,
				'7' AS NIVEL,
				2 AS TOTAL
		FROM #RS_RET 
		WHERE TP = 1

	INSERT INTO #RS_RET 
		SELECT  'VALOR QT - QD',
				SUM((CASE WHEN NIVEL = '1' THEN VLR_TP ELSE 0 END)) - SUM((CASE WHEN NIVEL = '2' THEN VLR_TP ELSE 0 END)) AS VLR_TP,
				'8' AS NIVEL,
				2 AS TOTAL
		FROM #RS_RET 
		WHERE TP = 1

	INSERT INTO #RS_RET 
		SELECT  'VALOR (QT + QI) - QD',
				SUM((CASE WHEN NIVEL IN ('1','3') THEN VLR_TP ELSE 0 END)) - SUM((CASE WHEN NIVEL = '2' THEN VLR_TP ELSE 0 END)) AS VLR_TP,
				'9' AS NIVEL,
				2 AS TOTAL
		FROM #RS_RET 
		WHERE TP = 1
						
	SELECT * FROM #RS_RET 
	ORDER BY NIVEL	
END
DROP TABLE  #RS_RET; 
DROP TABLE  #RS_CART;
DROP TABLE  #PRC_FILIAL_GRP;
