
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_EST_CX_FECHA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA]
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_RELA_EST_CX_FECHA]    Script Date: 07/01/2013 10:29:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
  


CREATE       FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA] (
					@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_CHQS.CD_FILIAL, 
			PDV_VD.CD_CX,
			PRC_FILIAL.NM_FANT,
			PDV_VD_CHQS.NR_CHQ, 
			PDV_VD_CHQS.SR_CHQ, 
			PDV_VD_CHQS.CD_BC_CHQ, 
			PDV_VD_CHQS.DT_VENCTO, 
			PDV_VD_CHQS.DT_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,
            PDV_VD_CHQS.COMP_CHQ COMP,
            0 AS TIPO_TRANSA,
			REL.CD_VD_RELACIONADA,
			PDV_VD_CHQS.ANO_CHQ 
		FROM         
			PDV_VD INNER JOIN PDV_VD_CHQS ON 
			PDV_VD.CD_EMP = PDV_VD_CHQS.CD_EMP AND 
			PDV_VD.CD_FILIAL = PDV_VD_CHQS.CD_FILIAL AND 
			PDV_VD.CD_VD = PDV_VD_CHQS.CD_VD AND
			PDV_VD_CHQS.DT_EMI = PDV_VD_CHQS.DT_VENCTO 
			INNER JOIN  PRC_FILIAL ON
			PDV_VD_CHQS.CD_EMP = PRC_FILIAL.CD_EMP AND
			PDV_VD_CHQS.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			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
			 PDV_VD.CD_EMP = @CD_EMP
  			AND PDV_VD.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	
			AND PDV_VD.ST_VD = 0
			AND PDV_VD.DT_VD = @DT_MOV		
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 AS 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
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_CHQS.CD_FILIAL, 
			PDV_VD.CD_CX,
			PRC_FILIAL.NM_FANT,
			PDV_VD_CHQS.NR_CHQ, 
			PDV_VD_CHQS.SR_CHQ, 
			PDV_VD_CHQS.CD_BC_CHQ, 
			PDV_VD_CHQS.DT_VENCTO, 
			PDV_VD_CHQS.DT_EMI,
			PDV_VD_CHQS.CPF_CGC_EMI, 
			PDV_VD_CHQS.NM_EMI,
			PDV_VD.DT_VD,
			PDV_VD_CHQS.VL_CHQ,
			PDV_VD.NR_ECF,
            PDV_VD_CHQS.COMP_CHQ COMP,
            0 AS TIPO_TRANSA,
			REL.CD_VD_RELACIONADA,
			PDV_VD_CHQS.ANO_CHQ 
		FROM         
			PDV_VD INNER JOIN PDV_VD_CHQS ON 
			PDV_VD.CD_EMP = PDV_VD_CHQS.CD_EMP AND 
			PDV_VD.CD_FILIAL = PDV_VD_CHQS.CD_FILIAL AND 
			PDV_VD.CD_VD = PDV_VD_CHQS.CD_VD AND
			PDV_VD_CHQS.DT_EMI <> PDV_VD_CHQS.DT_VENCTO 
			INNER JOIN  PRC_FILIAL ON
			PDV_VD_CHQS.CD_EMP = PRC_FILIAL.CD_EMP AND
			PDV_VD_CHQS.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			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
			PDV_VD.CD_EMP = @CD_EMP
			AND PDV_VD.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
			AND PDV_VD.ST_VD = 0
			AND PDV_VD.DT_VD = @DT_MOV
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
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


