IF OBJECT_ID('UFD_EXP_CTB_MOV_DEVOLUCOES') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_DEVOLUCOES
	END
	
GO	 
--  SELECT * FROM UFD_EXP_CTB_MOV_DEVOLUCOES(1,4,'20180301','20180301')

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

RETURNS @RS_RETURN TABLE(
		CD_EMP				INT, 
		CD_FILIAL			INT,
		CD_NF				INT,
		DT_ENT				DATETIME,
		VLR_NF				MONEY,
		CD_VD				INT, 
		DT_VD				DATETIME, 
		VLR_LIQ_VD			MONEY, 
		VLR_DINH			MONEY, 
		VLR_CHQS			MONEY,
		VL_CHQS_A_VISTA		MONEY,
		VL_CHQS_A_PRZ		MONEY,
		VLR_TROCO_CHQ		MONEY,
		VLR_CONV			MONEY, 
		VLR_CARTAO			MONEY, 
		CD_ADM_CARTAO		MONEY, 
		CD_CLI				INT,
		CD_CONV             INT, 
		TP_DEVO				VARCHAR(50),
		VLR_CONV_PBM  MONEY	)
				
AS
BEGIN
	
	INSERT INTO @RS_RETURN 	
		
		--Devolução sem classificados sera retirada do dinheiro, cheque, cartao
	    SELECT 
			EST_NF_ENT.CD_EMP, 
			EST_NF_ENT.CD_FILIAL, 
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT, 
			ROUND(SUM(ISNULL(EST_NF_ENT.VLR_NF,0)),2) AS VLR_NF,
			PDV_VD.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD.VLR_CARTAO, 
			PDV_VD.CD_ADM_CARTAO, 
			PDV_VD.CD_CLI,
			0 AS CD_CONV,
			'CAIXA' AS TP_DEVO,
			0 AS VLR_CONV_PBM    
		FROM
	       EST_NF_ENT 
		   INNER JOIN PDV_VD_EST_NF_ENT ON
		       EST_NF_ENT.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP AND
		       EST_NF_ENT.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL AND
		       EST_NF_ENT.CD_NF = PDV_VD_EST_NF_ENT.CD_NF
	       INNER JOIN PDV_VD ON
		       PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD AND
		       PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL AND
		       PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP
    
		WHERE
			PDV_VD.CD_EMP 					= @CD_EMP
			AND EST_NF_ENT.CD_FILIAL 		= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN @DT_INI  AND @DT_FIM
			AND EST_NF_ENT.STS_NF 			= 0 
			AND NOT EXISTS (SELECT
								PDV_VD_CANCEL_TEF.CD_EMP
							FROM
								PDV_VD_CANCEL_TEF
							WHERE
								PDV_VD.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND
								PDV_VD.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND
								PDV_VD.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP)
			AND NOT EXISTS (SELECT
								PDV_VD_CANCEL_CONV.CD_EMP
							FROM
								PDV_VD_CANCEL_CONV
							WHERE
								PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD AND
								PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
								PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP)
		GROUP BY
			EST_NF_ENT.CD_EMP,
			EST_NF_ENT.CD_FILIAL,
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR,
			PDV_VD.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD.VLR_CARTAO,
			PDV_VD.CD_ADM_CARTAO,
			PDV_VD.CD_CLI
        
		UNION ALL 
        
		--Devolução sem classificados sera retirada do dinheiro, cheque, cartao por requisição
		SELECT 
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
			0 AS CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT, 
			ROUND(ISNULL(SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT),0),2)  AS VLR_NF,
			PDV_VD.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD.VLR_CARTAO, 
			PDV_VD.CD_ADM_CARTAO, 
			PDV_VD.CD_CLI,
			0 AS CD_CONV,
			'CAIXA' AS TP_DEVO,
			0 AS VLR_CONV_PBM
    
		FROM
			PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT 
			INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD AND 
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT

			INNER JOIN EST_NF_ENT ON 
				EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF 

			INNER JOIN PDV_VD ON 
				PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
				PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP
    
		WHERE
			PDV_VD.CD_EMP 							= @CD_EMP
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL 	= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN @DT_INI AND @DT_FIM
			AND NOT EXISTS (SELECT
								PDV_VD_CANCEL_TEF.CD_EMP
							FROM
								PDV_VD_CANCEL_TEF
							WHERE
								PDV_VD.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND
								PDV_VD.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND
								PDV_VD.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP)
			AND NOT EXISTS (SELECT
								PDV_VD_CANCEL_CONV.CD_EMP
							FROM
								PDV_VD_CANCEL_CONV
							WHERE
								PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD AND
								PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
								PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP)
		GROUP BY
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
			PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,
			PDV_VD.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD.VLR_CARTAO,
			PDV_VD.CD_ADM_CARTAO,
			PDV_VD.CD_CLI,
			EST_NF_ENT.DT_CONTABILIZAR
    
		UNION ALL
    
		--Devolução classificados como Cartao
		SELECT 
			EST_NF_ENT.CD_EMP, 
			EST_NF_ENT.CD_FILIAL, 
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT, 
			ROUND(ISNULL(SUM(EST_NF_ENT.VLR_NF),0),2) AS VLR_NF,
			PDV_VD_TEF.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD_TEF.VL_CARTAO AS  VLR_CARTAO, 
			PDV_VD_TEF.CD_ADM_CARTAO, 
			PDV_VD.CD_CLI,
			0 AS CD_CONV,
			'CARTAO' AS TP_DEVO,
			0 AS VLR_CONV_PBM
    
		FROM
			EST_NF_ENT 
			INNER JOIN PDV_VD_EST_NF_ENT ON
				EST_NF_ENT.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_EST_NF_ENT.CD_NF

			INNER JOIN PDV_VD ON
				PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD AND
				PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP

			INNER JOIN PDV_VD_CANCEL_TEF ON
				PDV_VD.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND
				PDV_VD.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP

			INNER JOIN PDV_VD_TEF ON 
				PDV_VD.CD_VD = PDV_VD_TEF.CD_VD AND 
				PDV_VD.CD_FILIAL = PDV_VD_TEF.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_TEF.CD_EMP
    
		WHERE
			EST_NF_ENT.CD_EMP 				= @CD_EMP
			AND EST_NF_ENT.CD_FILIAL 		= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN @DT_INI AND @DT_FIM
			AND EST_NF_ENT.STS_NF 			= 0 
    
		GROUP BY
			EST_NF_ENT.CD_EMP,
			EST_NF_ENT.CD_FILIAL,
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR,
			PDV_VD_TEF.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD_TEF.VL_CARTAO,
			PDV_VD_TEF.CD_ADM_CARTAO,
			PDV_VD.CD_CLI
    
		UNION ALL
    
		--Devolução sem classificados sera retirada do cartao por requisiçã0
		SELECT 
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
			0 AS CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR  AS DT_ENT, 
			ROUND(ISNULL(SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT),0),2)  AS VLR_NF,
			PDV_VD_TEF.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD_TEF.VL_CARTAO AS  VLR_CARTAO, 
			PDV_VD_TEF.CD_ADM_CARTAO, 
			PDV_VD.CD_CLI,
			0 AS CD_CONV,
			'CARTAO' AS TP_DEVO,
			ISNULL(VLR_CONV_PBM ,0)VLR_CONV_PBM
    
		FROM
			PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT 
			INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT

			INNER JOIN EST_NF_ENT ON 
				EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF 

			INNER JOIN PDV_VD ON 
				PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
				PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP
			INNER JOIN PDV_VD_CANCEL_TEF ON
				PDV_VD.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND
				PDV_VD.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP

			INNER JOIN PDV_VD_TEF ON 
				PDV_VD.CD_VD = PDV_VD_TEF.CD_VD AND 
				PDV_VD.CD_FILIAL = PDV_VD_TEF.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_TEF.CD_EMP
    
		WHERE
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP 			= @CD_EMP
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL 	= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 		BETWEEN  @DT_INI  AND  @DT_FIM
			
		
		GROUP BY
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
			PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,
			PDV_VD_TEF.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD_TEF.VL_CARTAO,
			PDV_VD_TEF.CD_ADM_CARTAO,
			PDV_VD.CD_CLI,
			VLR_CONV_PBM,
			EST_NF_ENT.DT_CONTABILIZAR
    
		UNION ALL
    
		--Devolução classificados como Convenio
		SELECT 
			EST_NF_ENT.CD_EMP, 
			EST_NF_ENT.CD_FILIAL, 
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT, 
			ROUND(ISNULL(SUM(EST_NF_ENT.VLR_NF),0),2) AS VLR_NF,
			PDV_VD.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD.VLR_CARTAO, 
			PDV_VD.CD_ADM_CARTAO, 
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			ISNULL(RC_VD_CONV.CD_CONV,0)CD_CONV,
			'CONVENIO' AS TP_DEVO,
			0 AS VLR_CONV_PBM

		FROM
			EST_NF_ENT 
			INNER JOIN PDV_VD_EST_NF_ENT ON
				EST_NF_ENT.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_EST_NF_ENT.CD_NF
				
			INNER JOIN PDV_VD ON
				PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD AND
				PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP
				
			INNER JOIN PDV_VD_CANCEL_CONV ON
				PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD AND
				PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP
		

			INNER JOIN PDV_VD_RC_VD_CONV ON 
			 PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP  
			 AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
			 AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
			INNER JOIN RC_VD_CONV ON
			 PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP
			 AND PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR
			INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON
			 RC_VD_CONV.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
			 AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_VINC_RC_CLI.CD_CONV


		WHERE
			PDV_VD.CD_EMP 					= @CD_EMP
			AND EST_NF_ENT.CD_FILIAL 		= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN  @DT_INI  AND  @DT_FIM
			AND EST_NF_ENT.STS_NF 			= 0 
			
		GROUP BY
			EST_NF_ENT.CD_EMP,
			EST_NF_ENT.CD_FILIAL,
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR,
			PDV_VD.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD.VLR_CARTAO,
			PDV_VD.CD_ADM_CARTAO,
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			RC_VD_CONV.CD_CONV
    
		UNION ALL
    
		--Devolução sem classificados sera retirada do convenio por requisiçã0
		SELECT 
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
			0 AS CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT, 
			ROUND(ISNULL(SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT),0),2)  AS VLR_NF,
			PDV_VD.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD.VLR_CARTAO, 
			PDV_VD.CD_ADM_CARTAO, 
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			RC_VD_CONV.CD_CONV,
			'CONVENIO' AS TP_DEVO,
			0 AS VLR_CONV_PBM

		FROM
			PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT 
			INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT

			INNER JOIN EST_NF_ENT ON 
				EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF 

			INNER JOIN PDV_VD ON 
				PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
				PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP

			INNER JOIN PDV_VD_CANCEL_CONV ON
				PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD AND
				PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND
				PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP
		   
		   INNER JOIN PDV_VD_RC_VD_CONV ON 
			 PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP  
			 AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
			 AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
			INNER JOIN RC_VD_CONV ON
			 PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP
			 AND PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR
			INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON
			 RC_VD_CONV.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
			 AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_VINC_RC_CLI.CD_CONV


		WHERE
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP 			=@CD_EMP
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL 	= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN  @DT_INI  AND  @DT_FIM

			AND NOT EXISTS ( SELECT CD_EMP FROM PDV_VD_IT_VD_PBM WHERE 

				    PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP
				    AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL
				    AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD
				    AND PDV_VD_IT_VD_PBM.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT )


		GROUP BY
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
			PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,
			PDV_VD.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD.VLR_CARTAO,
			PDV_VD.CD_ADM_CARTAO,
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			RC_VD_CONV.CD_CONV,
			EST_NF_ENT.DT_CONTABILIZAR

	   UNION ALL 

	   --Devolução sem classificados sera retirada do convenio PBM por requisição 
	   SELECT 
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
			0 AS CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR AS DT_ENT , 
			ROUND(ISNULL(SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT),0),2)  AS VLR_NF,
			PDV_VD.CD_VD, 
			PDV_VD.DT_VD, 
			PDV_VD.VLR_LIQ_VD, 
			PDV_VD.VLR_DINH, 
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA, 
			PDV_VD.VL_CHQS_A_PRZ, 
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV, 
			PDV_VD.VLR_CARTAO, 
			PDV_VD.CD_ADM_CARTAO, 
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			ISNULL(RC_CLI_CONV_VINC_RC_CLI.CD_CONV,0) CD_CONV,
			'CONVENIO' AS TP_DEVO,
			PDV_VD.VLR_CONV_PBM AS VLR_CONV_PBM

		FROM
			PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT 
			INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD AND
				PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT

			INNER JOIN EST_NF_ENT ON 
				EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF 

			INNER JOIN PDV_VD ON 
				PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP AND
				PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
				PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD 

			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 
			
			INNER JOIN PDV_VD_IT_VD_PBM ON
				PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP
				AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL
				AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD
				AND PDV_VD_IT_VD_PBM.CD_IT = PDV_VD_DEV_EST_PROD_IMP.CD_IT

		   INNER JOIN RC_CLI ON 
			    PDV_VD.CD_EMP =	 RC_CLI.CD_EMP
			    AND PDV_VD.CD_CLI_PBM =	 RC_CLI.CD_CLI

		   LEFT JOIN RC_CLI_CONV_VINC_RC_CLI ON  
			   RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP  
			   AND RC_CLI.CD_CLI = RC_CLI_CONV_VINC_RC_CLI.CD_CLI  

		   LEFT JOIN RC_CLI_CONV ON  
			 RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI_CONV.CD_EMP  
			 AND RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV 
			 
		WHERE
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP 		= @CD_EMP
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL 	= @CD_FILIAL
			AND EST_NF_ENT.DT_CONTABILIZAR 	BETWEEN  @DT_INI  AND  @DT_FIM

		GROUP BY
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
			PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
			PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,
			PDV_VD.CD_VD,
			PDV_VD.DT_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.VLR_DINH,
			PDV_VD.VLR_CHQS,
			PDV_VD.VL_CHQS_A_VISTA,
			PDV_VD.VL_CHQS_A_PRZ,
			PDV_VD.VLR_TROCO_CHQ,
			PDV_VD.VLR_CONV,
			PDV_VD.VLR_CARTAO,
			PDV_VD.CD_ADM_CARTAO,
			RC_CLI_CONV_VINC_RC_CLI.CD_CLI,
			RC_CLI_CONV_VINC_RC_CLI.CD_CONV,
			PDV_VD.VLR_CONV_PBM,
			EST_NF_ENT.DT_CONTABILIZAR
		
    RETURN
    
END        