IF OBJECT_ID('UFD_EXP_CTB_MOV_VENDA_CONV_DEV') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_VENDA_CONV_DEV
	END
	
GO	
 
CREATE FUNCTION UFD_EXP_CTB_MOV_VENDA_CONV_DEV(@CD_EMP		INT,
											   @CD_FILIAL	INT,
											   @DT_INI		DATETIME,
											   @DT_FIM		DATETIME)		

RETURNS @RS_RETURN TABLE(
		CD_EMP				INT, 
		CD_FILIAL			INT, 		
		DT_VD				DATETIME, 
		CD_CLI				INT,
		RZ_CLI				VARCHAR(100),
		VLR_LIQ_VD			MONEY,
		CD_CONV				INT)
				
AS
BEGIN
	
	-- Nos fontes do layout esta consulta é executada por dia e força as devoluções realizada no mesmo dia da venda por isto foi mantido o relacionamento
	-- da data da devolução com a data da venda.
	
	INSERT INTO @RS_RETURN 
    SELECT 
		PDV_VD.CD_EMP, 
		PDV_VD.CD_FILIAL, 
		PDV_VD.DT_VD, 
		RC_CLI.CD_CLI, 
		RC_CLI.RZ_CLI, 
		SUM(PDV_VD.VLR_CONV) AS VLR_LIQ_VD, 
		RC_CLI_CONV_RC_CLI.CD_CONV    
    FROM PDV_VD 
		INNER JOIN RC_CLI_CONV_RC_CLI ON 
			PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP AND 
			PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI 
		INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON 
			RC_CLI_CONV_RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
			RC_CLI_CONV_RC_CLI.CD_CONV = RC_CLI_CONV_VINC_RC_CLI.CD_CONV 
		INNER JOIN RC_CLI ON 
			RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI.CD_EMP AND 
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI = RC_CLI.CD_CLI 
		INNER JOIN PDV_VD_EST_NF_ENT ON 
			PDV_VD.CD_VD = PDV_VD_EST_NF_ENT.CD_VD AND 
			PDV_VD.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL AND
			PDV_VD.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP 
		INNER JOIN EST_NF_ENT ON 
			PDV_VD_EST_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP AND 
			PDV_VD_EST_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
			PDV_VD_EST_NF_ENT.CD_NF = EST_NF_ENT.CD_NF
		INNER JOIN PDV_VD_CANCEL_CONV ON
			PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP AND
			PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
			PDV_VD_EST_NF_ENT.CD_VD = PDV_VD_CANCEL_CONV.CD_VD

    WHERE 
		PDV_VD.CD_EMP			= @CD_EMP
		AND PDV_VD.CD_FILIAL	= @CD_FILIAL
		AND PDV_VD.ST_VD		= 0
		AND PDV_VD.VLR_CONV		> 0 
		AND PDV_VD.DT_VD		BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_ENT.DT_ENT   = PDV_VD.DT_VD 
		AND EST_NF_ENT.STS_NF   = 0
    
    GROUP BY PDV_VD.CD_EMP, PDV_VD.CD_FILIAL, PDV_VD.DT_VD, RC_CLI.CD_CLI, RC_CLI.RZ_CLI, RC_CLI_CONV_RC_CLI.CD_CONV 
    
    UNION ALL
    
    SELECT 
		PDV_VD.CD_EMP, 
		PDV_VD.CD_FILIAL, 
		PDV_VD.DT_VD,
		RC_CLI.CD_CLI, 
		RC_CLI.RZ_CLI, 
		SUM(PDV_VD.VLR_CONV) AS VLR_LIQ_VD, 
		RC_CLI_CONV_RC_CLI.CD_CONV    
    FROM PDV_VD 
		INNER JOIN RC_CLI_CONV_RC_CLI ON 
			PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP AND 
			PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI     
		INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON 
			RC_CLI_CONV_RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
			RC_CLI_CONV_RC_CLI.CD_CONV = RC_CLI_CONV_VINC_RC_CLI.CD_CONV     
		INNER JOIN RC_CLI ON 
			RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI.CD_EMP AND 
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI = RC_CLI.CD_CLI    
		INNER JOIN PDV_VD_CANCEL_CONV ON
			PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP AND
			PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
			PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD

    WHERE PDV_VD.CD_EMP			= @CD_EMP
		AND PDV_VD.CD_FILIAL	= @CD_FILIAL
		AND PDV_VD.ST_VD		= 0
		AND PDV_VD.VLR_CONV		> 0 
		AND PDV_VD.DT_VD		BETWEEN @DT_INI AND @DT_FIM     
		AND EXISTS( SELECT 
						* 
					FROM 
						PDV_VD_DEV_EST_PROD_IMP 
					WHERE 
						PDV_VD.CD_VD	= PDV_VD_DEV_EST_PROD_IMP.CD_VD 
						AND PDV_VD.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL 
						AND PDV_VD.CD_EMP	= PDV_VD_DEV_EST_PROD_IMP.CD_EMP 
						AND PDV_VD_DEV_EST_PROD_IMP.DT_DEVO = PDV_VD.DT_VD)
        
    GROUP BY PDV_VD.CD_EMP, PDV_VD.CD_FILIAL, PDV_VD.DT_VD, RC_CLI.CD_CLI, RC_CLI.RZ_CLI, RC_CLI_CONV_RC_CLI.CD_CONV 
   
    RETURN
    
END        