
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_RC_DEB_VLR_PGTO_POS_FIN]    Script Date: 06/15/2012 17:57:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UFD_RC_DEB_VLR_PGTO_POS_FIN] (@CD_EMP INT, @DT_REF DATETIME)    
RETURNS @RS_RET TABLE (	CD_EMP		INT,						
						CD_FILIAL	INT,
						CD_RC_DEB	INT,
						VLR_DP		MONEY,						
						SLD_DP		MONEY,
						STS_DP		INT)

AS    
	BEGIN  

		DECLARE @RS_TEMP TABLE (CD_EMP		INT,						
								CD_FILIAL	INT,
								CD_RC_DEB	INT,
								VLR_DP		MONEY,								
								SLD_DP		MONEY,
								STS_DP		INT)
		
		INSERT INTO @RS_TEMP
		SELECT
			RC_DEB.CD_EMP,
			RC_DEB.CD_FILIAL,
			RC_DEB.CD_RC_DEB,
			RC_DEB.VLR_DP,			
			RC_DEB.VLR_DP - ISNULL(SUM(
			
			CASE WHEN  DBO.RC_DEB.VLR_DP <> ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_PGTO,0) AND ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_PGTO,0) > 0 THEN
				   (ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_PGTO, 0) + ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_GLOSA, 0) + ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_IMP, 0))
			ELSE
					ISNULL(V_RC_DEB_VLR_PGTO_PARCIAL.VLR_PGTO, 0)
			END),0) AS VLR_SLD,
			RC_DEB.STS_DP
			
		FROM RC_DEB 
			LEFT OUTER JOIN RC_CRED ON 
				RC_DEB.CD_EMP = RC_CRED.CD_EMP AND
				RC_DEB.CD_FILIAL = RC_CRED.CD_FILIAL AND
				RC_DEB.CD_RC_DEB = RC_CRED.CD_RC_DEB AND				
				RC_CRED.DT_PAG <= @DT_REF
			LEFT OUTER JOIN V_RC_DEB_VLR_PGTO_PARCIAL ON
				RC_CRED.CD_EMP = V_RC_DEB_VLR_PGTO_PARCIAL.CD_EMP AND
				RC_CRED.CD_FILIAL = V_RC_DEB_VLR_PGTO_PARCIAL.CD_FILIAL AND
				RC_CRED.CD_RC_DEB = V_RC_DEB_VLR_PGTO_PARCIAL.CD_RC_DEB AND 
				RC_CRED.CD_DP_LANC = V_RC_DEB_VLR_PGTO_PARCIAL.CD_DP_LANC  
		WHERE RC_DEB.CD_EMP = @CD_EMP					
			
		GROUP BY 		
			RC_DEB.CD_EMP,
			RC_DEB.CD_FILIAL,
			RC_DEB.CD_RC_DEB,
			RC_DEB.VLR_DP,
			RC_DEB.STS_DP
		
		
		INSERT INTO @RS_RET 
		SELECT 
			T.CD_EMP,
			T.CD_FILIAL,
			T.CD_RC_DEB,
			T.VLR_DP,			
			T.SLD_DP,
			(CASE WHEN NOT T.STS_DP IN (0,1,2) THEN T.STS_DP 
				  WHEN (T.VLR_DP = T.SLD_DP) THEN 0
				  WHEN T.SLD_DP	 = 0 THEN 1
				  WHEN T.SLD_DP	 > 0 THEN 2
				  ELSE T.STS_DP END) AS STS_DP			
		FROM @RS_TEMP T
			
		RETURN
	END	
	
 
GO


