IF OBJECT_ID('UFD_EXP_CTB_MOV_CHQ_PRAZO') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_CHQ_PRAZO
	END
	
GO
 
 -- select * from UFD_EXP_CTB_MOV_CHQ_PRAZO (1,10,'20210917','20210917')

CREATE FUNCTION UFD_EXP_CTB_MOV_CHQ_PRAZO( @CD_EMP		INT,
										   @CD_FILIAL	INT,
										   @DT_INI		DATETIME,
										   @DT_FIM		DATETIME)		

RETURNS @RS_RETURN TABLE(
		CD_EMP				INT, 
		CD_FILIAL			INT, 		
		DT_EMI				DATETIME, 
		FLAG_ORIG_VENDA		VARCHAR(10), 
        CD_CUSTODIA			INT, 
        VLR_CUSTODIA		MONEY,
        VLR_ACERTO			MONEY, 
        CD_BC				INT,
        CD_CNT				VARCHAR(50),
        DS_BC				VARCHAR(50),
		FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA INT)
AS
BEGIN       

	DECLARE @FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA INT 
	
	SET @FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA = ISNULL((SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP,'NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA',0,0)),0)
	
	IF @FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA = 0		
		BEGIN
			INSERT INTO @RS_RETURN  
		        SELECT 
				CD_EMP,
				CD_FILIAL,
				DT_EMI,
		        FLAG_ORIG_VENDA, 
		        VALORES.CD_CUSTODIA, 
		        SUM(VLR_CUSTODIA) + VLR_ESTORNO AS VLR_CUSTODIA,
		        SUM(VLR_ACERTO) AS VLR_ACERTO, 
		        ISNULL(CD_BC,0) AS CD_BC,
		        ISNULL(CD_CNT,0) AS CD_CNT,
		        ISNULL(DS_BC,'') AS DS_BC,
				@FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA AS FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA
		        
		        FROM (SELECT 
							CHQ_LANC_CUSTODIA.CD_EMP,
							CHQ_LANC_CUSTODIA.CD_FILIAL,
							CHQ_LANC_CPL.DT_EMI,
							CASE WHEN ISNULL(PDV_VD_CHQS_CHQ_LANC.CD_CHQ,0) > 0 THEN 'SIM' ELSE 'NÃO' END FLAG_ORIG_VENDA,
							CHQ_LANC_CUSTODIA.CD_CUSTODIA,
							YEAR(CHQ_LANC_CUSTODIA.DT_CUSTODIA) AS ANO,
							CHQ_LANC_CPL.VLR_CHQ AS VLR_CUSTODIA,
										   
							ISNULL(( SELECT
									   SUM (VLR_ACERTO)
									 FROM
									   CHQ_LANC_CPL_ACERTO_VALORES
									 WHERE
									   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_ACERTO_VALORES.CD_EMP AND
									   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_ACERTO_VALORES.CD_CHQ),0) AS VLR_ACERTO,         
									   
						   ISNULL((SELECT SUM (VLR_CHQ)
								   FROM
									   CHQ_LANC_CUSTODIA_CPL_ESTORNO
								   WHERE
									   CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_EMP AND
									   CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA_CPL_ESTORNO.ANO AND
									   CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_CUSTODIA),0) AS VLR_ESTORNO
		        
						FROM CHQ_LANC_CUSTODIA 
							 INNER JOIN CHQ_LANC_CUSTODIA_CPL ON 
								 CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA_CPL.CD_EMP AND 
								 CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA_CPL.ANO AND 
								 CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA_CPL.CD_CUSTODIA 
							 RIGHT JOIN CHQ_LANC_CPL ON 
								 CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CUSTODIA_CPL.CD_EMP AND 
								 CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CUSTODIA_CPL.CD_CHQ 
							 LEFT OUTER JOIN PDV_VD_CHQS_CHQ_LANC 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 
		        
						WHERE 
							 CHQ_LANC_CUSTODIA.CD_EMP			= @CD_EMP
							 AND CHQ_LANC_CUSTODIA.CD_FILIAL	= @CD_FILIAL
							 AND CHQ_LANC_CUSTODIA.CD_CUSTODIA IN 
											(SELECT 
											   CD_CUSTODIA 
											FROM 
											   CHQ_LANC_CUSTODIA_DT_MOV 
											WHERE 
											   CD_EMP = @CD_EMP 
											   AND DT_MOV = CHQ_LANC_CPL.DT_EMI) AND 
					    
							 CHQ_LANC_CPL.DT_EMI <> DT_VENCTO AND 
							 CHQ_LANC_CPL.DT_EMI BETWEEN @DT_INI AND @DT_FIM
					    
						GROUP BY 
						    CHQ_LANC_CPL.DT_EMI,
							CHQ_LANC_CUSTODIA.CD_CUSTODIA, 
							YEAR(CHQ_LANC_CUSTODIA.DT_CUSTODIA), 
							CHQ_LANC_CPL.VLR_CHQ, CHQ_LANC_CPL.CD_EMP, 
							CHQ_LANC_CPL.CD_CHQ, CHQ_LANC_CUSTODIA.CD_EMP, CHQ_LANC_CUSTODIA.CD_FILIAL,
							CHQ_LANC_CUSTODIA.ANO,
							PDV_VD_CHQS_CHQ_LANC.CD_CHQ) VALORES 
		        
		        INNER JOIN 
		        
		           (SELECT 
		               BC_CONTAS.CD_BC, 
		               BC_CONTAS.CD_CNT, 
		               YEAR(CHQ_LANC_CUSTODIA.DT_CUSTODIA) AS ANO,
		               CHQ_LANC_CUSTODIA.CD_CUSTODIA, 
		               DS_BC 
		           FROM 
		               CHQ_LANC_CUSTODIA 
		               INNER JOIN BC_LANC_CHQ_LANC_CUSTODIA 
		               INNER JOIN BC_LANC ON 
		               BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP = BC_LANC.CD_EMP AND
		               BC_LANC_CHQ_LANC_CUSTODIA.CD_BC_LANC = BC_LANC.CD_BC_LANC 
		               INNER JOIN BC_CONTAS ON 
		               BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND 
		               BC_LANC.CD_CNT = BC_CONTAS.CD_CNT 
		               INNER JOIN BC_BANCO ON 
		               BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP AND 
		               BC_CONTAS.CD_BC = BC_BANCO.CD_BC ON
		               CHQ_LANC_CUSTODIA.CD_EMP = BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP AND
		               CHQ_LANC_CUSTODIA.ANO = BC_LANC_CHQ_LANC_CUSTODIA.ANO AND
		               CHQ_LANC_CUSTODIA.CD_CUSTODIA = BC_LANC_CHQ_LANC_CUSTODIA.CD_CUSTODIA
		        
		           WHERE 
		        
		           BC_CONTAS.TP_CNT = 3 
		           AND CHQ_LANC_CUSTODIA.CD_FILIAL = @CD_FILIAL
		           ) BANCO ON 
		        
		         BANCO.CD_CUSTODIA = VALORES.CD_CUSTODIA AND
		         BANCO.ANO = VALORES.ANO
		         GROUP BY CD_EMP, CD_FILIAL, DT_EMI,VALORES.CD_CUSTODIA, CD_BC,CD_CNT,DS_BC, VALORES.VLR_ESTORNO,FLAG_ORIG_VENDA 
		        
		         UNION ALL
		        
		         SELECT 
		           PDV_VD_CHQS.CD_EMP,
		           PDV_VD_CHQS.CD_FILIAL,
		           CHQ_LANC_CPL.DT_EMI,
		           'SIM' AS FLAG_ORIG_VENDA,
		           0 AS CD_CUSTODIA,
		           SUM(PDV_VD_CHQS.VL_CHQ)VL_CHQ,
		           0 AS VLR_ACERTO,
		           BC_CONTAS.CD_BC,
		           BC_LANC.CD_CNT,
		           BC_BANCO.DS_BC,
				   @FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA AS FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA
		         FROM
		           CHQ_LANC_CPL 
		           INNER JOIN PDV_VD_CHQS_CHQ_LANC 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_CHQS ON
					   PDV_VD_CHQS_CHQ_LANC.CD_EMP = PDV_VD_CHQS.CD_EMP AND
					   PDV_VD_CHQS_CHQ_LANC.CD_FILIAL = PDV_VD_CHQS.CD_FILIAL AND
					   PDV_VD_CHQS_CHQ_LANC.CD_VD = PDV_VD_CHQS.CD_VD AND
					   PDV_VD_CHQS_CHQ_LANC.NR_ORD = PDV_VD_CHQS.NR_ORD
		           INNER JOIN CHQ_DEPOS_CPL ON
					   CHQ_LANC_CPL.CD_EMP = CHQ_DEPOS_CPL.CD_EMP AND
					   CHQ_LANC_CPL.CD_CHQ = CHQ_DEPOS_CPL.CD_CHQ
		           INNER JOIN BC_LANC_CHQ_DEPOS ON
					   CHQ_DEPOS_CPL.CD_EMP = BC_LANC_CHQ_DEPOS.CD_EMP AND
					   CHQ_DEPOS_CPL.ANO = BC_LANC_CHQ_DEPOS.ANO AND
					   CHQ_DEPOS_CPL.CD_DEPOS = BC_LANC_CHQ_DEPOS.CD_DEPOS
		           INNER JOIN BC_LANC ON
					   BC_LANC_CHQ_DEPOS.CD_EMP = BC_LANC.CD_EMP AND
					   BC_LANC_CHQ_DEPOS.CD_BC_LANC = BC_LANC.CD_BC_LANC
		           INNER JOIN BC_CONTAS ON
					   BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND
					   BC_LANC.CD_CNT = BC_CONTAS.CD_CNT
		           INNER JOIN BC_BANCO ON
					   BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP AND
					   BC_CONTAS.CD_BC = BC_BANCO.CD_BC
		           LEFT OUTER JOIN CHQ_LANC_CUSTODIA_CPL ON
					   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CUSTODIA_CPL.CD_EMP AND
					   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CUSTODIA_CPL.CD_CHQ
		        
		         WHERE
		           PDV_VD_CHQS.CD_EMP			= @CD_EMP
		           AND PDV_VD_CHQS.CD_FILIAL	= @CD_FILIAL 
		           AND CHQ_LANC_CPL.DT_EMI		<> CHQ_LANC_CPL.DT_VENCTO
		           AND CHQ_LANC_CPL.DT_EMI		BETWEEN @DT_INI AND @DT_FIM 
		           AND BC_LANC.TP_LANC			= 1
		           AND CHQ_LANC_CUSTODIA_CPL.CD_CHQ IS NULL
				   
				   GROUP BY
				   
				   PDV_VD_CHQS.CD_EMP,
		           PDV_VD_CHQS.CD_FILIAL,
		           PDV_VD_CHQS.DT_EMI,
		           BC_CONTAS.CD_BC,
		           BC_LANC.CD_CNT,
		           BC_BANCO.DS_BC,
				   CHQ_LANC_CPL.DT_EMI
				   

				   UNION ALL

				   -----

				   	SELECT
						CHQ_LANC_CPL.CD_EMP,
						CHQ_LANC.CD_FILIAL,
						CHQ_LANC_CPL.DT_EMI,
						(CASE WHEN ISNULL(PDV_VD_CHQS_CHQ_LANC.CD_CHQ,0) > 0 THEN 'SIM' ELSE 'NÃO' END) AS FLAG_ORIG_VENDA,
						0 AS CD_CUSTODIA, 
						CHQ_LANC_CPL.VLR_CHQ AS VLR_CUSTODIA,			        
						ISNULL(( SELECT
								   SUM (VLR_ACERTO)
								FROM
								   CHQ_LANC_CPL_ACERTO_VALORES
							   WHERE
								   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_ACERTO_VALORES.CD_EMP AND
								   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_ACERTO_VALORES.CD_CHQ),0) AS VLR_ACERTO,
						0 AS CD_BC,
						0 AS CD_CNT,
						'' AS DS_BC,
						1  AS FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA
				 FROM
					   CHQ_LANC 
					   INNER JOIN CHQ_LANC_CHQ_LANC_CPL ON
						   CHQ_LANC.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP AND
						   CHQ_LANC.ANO = CHQ_LANC_CHQ_LANC_CPL.ANO AND
						   CHQ_LANC.CD_CHQ_LANC = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC
					   INNER JOIN CHQ_LANC_CPL ON
						   CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND
						   CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ
					   LEFT OUTER JOIN PDV_VD_CHQS_CHQ_LANC 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
				 WHERE
					   CHQ_LANC.CD_EMP 		 = @CD_EMP
					   AND CHQ_LANC.CD_FILIAL = @CD_FILIAL
					   AND CHQ_LANC_CPL.DT_EMI <> DT_VENCTO
					   AND CHQ_LANC_CPL.DT_EMI BETWEEN @DT_INI AND @DT_FIM 

					   AND NOT EXISTS( SELECT CD_EMP FROM CHQ_LANC_CUSTODIA_CPL 
										WHERE CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CUSTODIA_CPL.CD_EMP AND 
											  CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CUSTODIA_CPL.CD_CHQ)
											  
					  AND NOT EXISTS( SELECT CD_EMP FROM CHQ_DEPOS_CPL 
									  WHERE  CHQ_LANC_CPL.CD_EMP = CHQ_DEPOS_CPL.CD_EMP AND
					                         CHQ_LANC_CPL.CD_CHQ = CHQ_DEPOS_CPL.CD_CHQ)

				 GROUP BY
					   CHQ_LANC_CPL.VLR_CHQ, CHQ_LANC_CPL.CD_EMP, CHQ_LANC.CD_FILIAL, CHQ_LANC_CPL.DT_EMI,
					   CHQ_LANC_CPL.CD_CHQ , PDV_VD_CHQS_CHQ_LANC.CD_CHQ

			     ---------

		END
		
	ELSE
	
		BEGIN
		
			INSERT INTO @RS_RETURN  
				SELECT
						CHQ_LANC_CPL.CD_EMP,
						CHQ_LANC.CD_FILIAL,
						CHQ_LANC_CPL.DT_EMI,
						(CASE WHEN ISNULL(PDV_VD_CHQS_CHQ_LANC.CD_CHQ,0) > 0 THEN 'SIM' ELSE 'NÃO' END) AS FLAG_ORIG_VENDA,
						0 AS CD_CUSTODIA, 
						CHQ_LANC_CPL.VLR_CHQ AS VLR_CUSTODIA,			        
						ISNULL(( SELECT
								   SUM (VLR_ACERTO)
								FROM
								   CHQ_LANC_CPL_ACERTO_VALORES
							   WHERE
								   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_ACERTO_VALORES.CD_EMP AND
								   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_ACERTO_VALORES.CD_CHQ),0) AS VLR_ACERTO,
						0 AS CD_BC,
						0 AS CD_CNT,
						'' AS DS_BC,
						@FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA AS FLAG_NAO_EXISTE_LANCTO_CHQS_EM_CUSTODIA
				 FROM
					   CHQ_LANC 
					   INNER JOIN CHQ_LANC_CHQ_LANC_CPL ON
						   CHQ_LANC.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP AND
						   CHQ_LANC.ANO = CHQ_LANC_CHQ_LANC_CPL.ANO AND
						   CHQ_LANC.CD_CHQ_LANC = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC
					   INNER JOIN CHQ_LANC_CPL ON
						   CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND
						   CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ
					   LEFT OUTER JOIN PDV_VD_CHQS_CHQ_LANC 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
				 WHERE

						CHQ_LANC.CD_EMP 		 = @CD_EMP
						AND CHQ_LANC.CD_FILIAL = @CD_FILIAL
						AND  CONVERT(CHAR(10), CHQ_LANC_CPL.DT_EMI, 101)  <> CONVERT(CHAR(10),  DT_VENCTO,101)
						AND CHQ_LANC_CPL.DT_EMI BETWEEN @DT_INI AND @DT_FIM
						AND  CONVERT(CHAR(10),  DT_VENCTO,101) > CONVERT(CHAR(10), CHQ_LANC_CPL.DT_EMI, 101)

				 GROUP BY
					   CHQ_LANC_CPL.VLR_CHQ, CHQ_LANC_CPL.CD_EMP, CHQ_LANC.CD_FILIAL, CHQ_LANC_CPL.DT_EMI,
					   CHQ_LANC_CPL.CD_CHQ , PDV_VD_CHQS_CHQ_LANC.CD_CHQ
					
		
		END
				   
	RETURN 
           
END           