IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_EST_CX_FECHA_CONGELADO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA_CONGELADO]
GO
 
/****** Object:  UserDefinedFunction [dbo].[UFD_RELA_EST_CX_FECHA_CONGELADO]    Script Date: 27/03/2015 10:52:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE  FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA_CONGELADO] (
					@CD_EMP	INT,
					@CD_FILIAL 	NVARCHAR(1000),
					@DT_MOV	DATETIME,
					@TP_RELA	INT
					)
RETURNS @RS_RETORNO TABLE (	
					CD_EMP              INT,
					CD_FILIAL			INT,
					CD_CX					INT,	
					NM_FANT				NVARCHAR(60),
					NR_CHQ				NVARCHAR(50),
					SR_CHQ				NVARCHAR(50),
					CD_BC_CHQ			INT,
					DT_VENCTO			DATETIME,
					DT_EMI				DATETIME,
					CPF_CGC_EMIT		NVARCHAR(50),
					NM_EMI				NVARCHAR(60),
					DT_VD					DATETIME,
					VLR_CHQ				MONEY,
					NR_ECF				INT,
					COMP				INT,
					TIPO_TRANSA			INT,
					CD_VD_RELACIONADA   INT,
					ANO_CHQ             INT)
AS
BEGIN
IF @TP_RELA = 1 -- A VISTA
	INSERT INTO @RS_RETORNO
	SELECT   
		PDV_VD_CHQS.CD_EMP,  
		PDV_VD.CD_FILIAL, 
		PDV_VD.CD_CX, 
		PRC_FILIAL.NM_FANT, 
		CHQ_LANC_CPL.NR_CHQ, 
		CHQ_LANC_CPL.SR_CHQ, 
		CHQ_LANC_CPL.CD_BC_CHQ, 
		PDV_VD_FECHA_MOV_CHQ_LANC_CPL.DT_VENCTO, 
		CHQ_LANC_CPL.DT_EMI, 
		--CHQ_EMI.CGC_CPF,
		--CHQ_EMI.NM_EMI, 
		PDV_VD_CHQS.CPF_CGC_EMI COLLATE SQL_Latin1_General_CP1_CI_AI,
		PDV_VD_CHQS.NM_EMI COLLATE SQL_Latin1_General_CP1_CI_AI,		
		PDV_VD.DT_VD,
		PDV_VD_CHQS.VL_CHQ,
		PDV_VD.NR_ECF,
        CHQ_LANC_CPL.COMP,
        0 TIPO_TRANSA,
		REL.CD_VD_RELACIONADA,
		PDV_VD_CHQS.ANO_CHQ
	FROM         
		CHQ_LANC_CPL INNER JOIN PDV_VD_CHQS 
		INNER JOIN PDV_VD_CHQS_CHQ_LANC ON 
		PDV_VD_CHQS.CD_EMP = PDV_VD_CHQS_CHQ_LANC.CD_EMP AND 
	    PDV_VD_CHQS.CD_FILIAL = PDV_VD_CHQS_CHQ_LANC.CD_FILIAL AND 
		PDV_VD_CHQS.CD_VD = PDV_VD_CHQS_CHQ_LANC.CD_VD AND 
	    PDV_VD_CHQS.NR_ORD = PDV_VD_CHQS_CHQ_LANC.NR_ORD ON 
		CHQ_LANC_CPL.CD_EMP = PDV_VD_CHQS_CHQ_LANC.CD_EMP AND 
	    CHQ_LANC_CPL.CD_CHQ = PDV_VD_CHQS_CHQ_LANC.CD_CHQ 
		INNER JOIN PDV_VD ON 
		PDV_VD_CHQS.CD_EMP = PDV_VD.CD_EMP AND 
		PDV_VD_CHQS.CD_FILIAL = PDV_VD.CD_FILIAL AND 
	    PDV_VD_CHQS.CD_VD = PDV_VD.CD_VD 
		INNER JOIN PRC_FILIAL ON 
		PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP AND 
		PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
		INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON 
		CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND 
	    CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ 
		INNER JOIN CHQ_EMI ON 
		CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND 
		CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
		LEFT JOIN PDV_VD_CHQS_TROCO_RELACIONADA REL ON
		REL.CD_EMP=PDV_VD_CHQS.CD_EMP AND
		REL.CD_FILIAL=PDV_VD_CHQS.CD_FILIAL AND
		REL.CD_VD=PDV_VD_CHQS.CD_VD 
	WHERE     
		CHQ_LANC_CPL.DT_EMI = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.DT_VENCTO AND
		PDV_VD.CD_EMP = @CD_EMP 
		AND PDV_VD.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	
		AND PDV_VD.DT_VD = @DT_MOV
		AND PDV_VD.ST_VD = 0
UNION ALL
SELECT 
	CHQ_LANC_CPL.CD_EMP,    
	CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL, 
	0 AS CD_CX, 
	PRC_FILIAL.NM_FANT, 
	CHQ_LANC_CPL.NR_CHQ COLLATE SQL_Latin1_General_CP1_CI_AI, 
	CHQ_LANC_CPL.SR_CHQ COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_LANC_CPL.CD_BC_CHQ, 
    CHQ_LANC_CPL.DT_VENCTO, 
    CHQ_LANC_CPL.DT_EMI, 
    CHQ_EMI.CGC_CPF COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_EMI.NM_EMI COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_LANC_CPL.DT_EMI,
    CHQ_LANC_CPL.VLR_CHQ, 
    0 AS NR_ECF, 
    CHQ_LANC_CPL.COMP,
    1 TIPO_TRANSA,
	0 AS CD_VD_RELACIONADA,
	CHQ_LANC_CPL.ANO_CHQ
FROM         
	CHQ_LANC_CPL INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO ON 
	CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP AND 
    CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_CHQ 
    INNER JOIN PRC_FILIAL ON 
    CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PRC_FILIAL.CD_EMP AND 
    CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
    INNER JOIN CHQ_EMI ON 
    CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND 
    CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
	INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON 
	CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND 
    CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ     
WHERE
	CHQ_LANC_CPL.DT_EMI = CHQ_LANC_CPL.DT_VENCTO  AND
	CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = @CD_EMP
	AND CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	
	AND CHQ_LANC_CPL.DT_EMI = @DT_MOV				
ELSE
	-- A PRAZO
	INSERT INTO @RS_RETORNO
	SELECT
		PDV_VD_CHQS.CD_EMP,     
		PDV_VD.CD_FILIAL, 
		PDV_VD.CD_CX, 
		PRC_FILIAL.NM_FANT, 
		CHQ_LANC_CPL.NR_CHQ, 
		CHQ_LANC_CPL.SR_CHQ, 
		CHQ_LANC_CPL.CD_BC_CHQ, 
	   PDV_VD_FECHA_MOV_CHQ_LANC_CPL.DT_VENCTO, 
		CHQ_LANC_CPL.DT_EMI, 
		--CHQ_EMI.CGC_CPF,
		--CHQ_EMI.NM_EMI, 
		PDV_VD_CHQS.CPF_CGC_EMI COLLATE SQL_Latin1_General_CP1_CI_AI,
		PDV_VD_CHQS.NM_EMI COLLATE SQL_Latin1_General_CP1_CI_AI,		
		PDV_VD.DT_VD,
		PDV_VD_CHQS.VL_CHQ,
		PDV_VD.NR_ECF,
        CHQ_LANC_CPL.COMP,
        0 TIPO_TRANSA,
		REL.CD_VD_RELACIONADA,
		PDV_VD_CHQS.ANO_CHQ
	FROM         
		CHQ_LANC_CPL INNER JOIN PDV_VD_CHQS 
		INNER JOIN PDV_VD_CHQS_CHQ_LANC ON 
		PDV_VD_CHQS.CD_EMP = PDV_VD_CHQS_CHQ_LANC.CD_EMP AND 
	   PDV_VD_CHQS.CD_FILIAL = PDV_VD_CHQS_CHQ_LANC.CD_FILIAL AND 
		PDV_VD_CHQS.CD_VD = PDV_VD_CHQS_CHQ_LANC.CD_VD AND 
	   PDV_VD_CHQS.NR_ORD = PDV_VD_CHQS_CHQ_LANC.NR_ORD ON 
		CHQ_LANC_CPL.CD_EMP = PDV_VD_CHQS_CHQ_LANC.CD_EMP AND 
	   CHQ_LANC_CPL.CD_CHQ = PDV_VD_CHQS_CHQ_LANC.CD_CHQ 
		INNER JOIN PDV_VD ON 
		PDV_VD_CHQS.CD_EMP = PDV_VD.CD_EMP AND 
		PDV_VD_CHQS.CD_FILIAL = PDV_VD.CD_FILIAL AND 
	   PDV_VD_CHQS.CD_VD = PDV_VD.CD_VD 
		INNER JOIN PRC_FILIAL ON 
		PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP AND 
		PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
		INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON 
		CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND 
	   CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ 
		INNER JOIN CHQ_EMI ON 
		CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND 
		CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
			LEFT JOIN PDV_VD_CHQS_TROCO_RELACIONADA REL ON
			REL.CD_EMP=PDV_VD_CHQS.CD_EMP AND
			REL.CD_FILIAL=PDV_VD_CHQS.CD_FILIAL AND
			REL.CD_VD=PDV_VD_CHQS.CD_VD 
	WHERE     
		CHQ_LANC_CPL.DT_EMI <> PDV_VD_FECHA_MOV_CHQ_LANC_CPL.DT_VENCTO AND
		PDV_VD.CD_EMP = @CD_EMP 
		AND PDV_VD.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	
		AND PDV_VD.DT_VD = @DT_MOV
		AND PDV_VD.ST_VD = 0
UNION ALL
SELECT
	CHQ_LANC_CPL.CD_EMP,     
	CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL, 
	0 AS CD_CX, 
	PRC_FILIAL.NM_FANT, 
	CHQ_LANC_CPL.NR_CHQ COLLATE SQL_Latin1_General_CP1_CI_AI, 
	CHQ_LANC_CPL.SR_CHQ COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_LANC_CPL.CD_BC_CHQ, 
    CHQ_LANC_CPL.DT_VENCTO, 
    CHQ_LANC_CPL.DT_EMI, 
    CHQ_EMI.CGC_CPF COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_EMI.NM_EMI COLLATE SQL_Latin1_General_CP1_CI_AI, 
    CHQ_LANC_CPL.DT_EMI,
    CHQ_LANC_CPL.VLR_CHQ, 
    0 AS NR_ECF, 
    CHQ_LANC_CPL.COMP,
	1 TIPO_TRANSA,
	0 AS CD_VD_RELACIONADA,
	CHQ_LANC_CPL.ANO_CHQ
FROM         
	CHQ_LANC_CPL INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO ON 
	CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP AND 
    CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_CHQ 
    INNER JOIN PRC_FILIAL ON 
    CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PRC_FILIAL.CD_EMP AND 
    CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
    INNER JOIN CHQ_EMI ON 
    CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND 
    CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
	INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON 
	CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND 
    CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ     
WHERE
	CHQ_LANC_CPL.DT_EMI <> CHQ_LANC_CPL.DT_VENCTO  AND
	CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = @CD_EMP
	AND CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	
	AND CHQ_LANC_CPL.DT_EMI = @DT_MOV			
RETURN
END

GO




GO


