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(
        SEQ_CONSULTA        INT,
		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,
		VLR_FRETE           MONEY )
AS
BEGIN
	INSERT INTO @RS_RETURN 	
		--Devoluo sem classificados sera retirada do dinheiro, cheque, cartao
	    SELECT 
			1 AS SEQ_CONSULTA,
			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) + ISNULL(VLR_OUTRAS_DESPESAS,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,
			0  AS VLR_FRETE
		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 EST_NF_ENT_IT ON
				EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
				EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
				EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
		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 
			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 classificacao sera retirada do dinheiro, cheque, cartao por requisicao
		SELECT 
			2 AS SEQ_CONSULTA,
			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 + ISNULL(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS,0)),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,
			SUM(ISNULL(EST_NF_ENT_IT.VLR_FRETE_IT,0)) AS VLR_FRETE
		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 EST_NF_ENT_IT ON
		       EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
		       EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
		       EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
			   AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD =  EST_NF_ENT_IT.CD_PROD 
			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

		--Devolucao classificada como Cartao
		SELECT
		    3 AS SEQ_CONSULTA,
			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,
			0 AS VLR_FRETE
		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

		--Devolucao sem classificacao sera retirada do cartao por requisicao
		SELECT 
		    4 AS SEQ_CONSULTA,
			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,
			0 AS VLR_FRETE
		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

		--Devolucao classificada como Convenio
		SELECT
		    5 AS SEQ_CONSULTA,
			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,
			0 AS VLR_FRETE
		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

		--Devolucao sem classificacao sera retirada do convenio por requisicao
		SELECT
		    6 AS SEQ_CONSULTA,
			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,
			0 AS VLR_FRETE
		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 

	   --Devolucao sem classificacao sera retirada do convenio PBM por requisicao 
	   SELECT 
			7 AS SEQ_CONSULTA,
			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,
			0 AS VLR_FRETE 
		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

			UNION ALL

		-- Devolucao de venda NFE CONVENIO
		SELECT
		    8 AS SEQ_CONSULTA,
			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,
			EST_NF_SAI.CD_NF AS CD_VD, 
			EST_NF_SAI.DT_EMI_NF AS DT_VD, 
		    SUM(EST_NF_SAI.VLR_NF - EST_NF_SAI.VLR_FRETE) AS VLR_LIQ_VD, 
			0 AS VLR_DINH,
			0 AS  VLR_CHQS,
			0 AS VL_CHQS_A_VISTA, 
			0 AS VL_CHQS_A_PRZ, 
			0 AS VLR_TROCO_CHQ,
			SUM(EST_NF_SAI.VLR_NF - EST_NF_SAI.VLR_FRETE) AS VLR_CONV, 
			0 AS VLR_CARTAO, 
			0 AS CD_ADM_CARTAO, 
			EST_NF_SAI.CD_CLI,
			CONV.CD_CONV AS CD_CONV,
			'CONVENIO' AS TP_DEVO,
			0 AS VLR_CONV_PBM,
			SUM(ISNULL(EST_NF_ENT.VLR_FRETE,0)) AS VLR_FRETE
		FROM
			EST_NF_ENT 
             INNER JOIN EST_NF_SAI_NF_ENT ON 
			     EST_NF_ENT.CD_EMP      = EST_NF_SAI_NF_ENT.CD_EMP AND
			     EST_NF_ENT.CD_FILIAL   = EST_NF_SAI_NF_ENT.CD_FILIAL AND
			     EST_NF_ENT.CD_NF       = EST_NF_SAI_NF_ENT.CD_NF_ENT
              
			  INNER JOIN EST_NF_SAI ON

				EST_NF_SAI.CD_EMP      = EST_NF_SAI_NF_ENT.CD_EMP AND
				EST_NF_SAI.CD_FILIAL   = EST_NF_SAI_NF_ENT.CD_FILIAL AND
				EST_NF_SAI.CD_NF       = EST_NF_SAI_NF_ENT.CD_NF_SAI
				
			   INNER JOIN EST_NF_SAI_RC_VD_CONV ON 
			   
			   EST_NF_SAI.CD_EMP      = EST_NF_SAI_RC_VD_CONV.CD_EMP AND
				EST_NF_SAI.CD_FILIAL   = EST_NF_SAI_RC_VD_CONV.CD_FILIAL AND
				EST_NF_SAI.CD_NF       = EST_NF_SAI_RC_VD_CONV.CD_NF
				INNER JOIN rc_vd_conv CONV ON CONV.CD_CTR = EST_NF_SAI_RC_VD_CONV.CD_CTR
															AND CONV.CD_FILIAL = EST_NF_SAI_RC_VD_CONV.CD_FILIAL
				INNER JOIN GLB_MOV_OP   ON  EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP

				INNER JOIN EST_NF_SAI_IT ON
				
			   EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND
		       EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND
		       EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF

  
		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
			AND GLB_MOV_OP.TP_MOV = 0  -- nota de saída venda
			
		GROUP BY
			EST_NF_ENT.CD_EMP,
			EST_NF_ENT.CD_FILIAL,
			EST_NF_ENT.CD_NF,
			EST_NF_ENT.DT_CONTABILIZAR,
			EST_NF_ENT.DT_CONTABILIZAR ,
			EST_NF_SAI.CD_NF  , 
			EST_NF_SAI.DT_EMI_NF  , 
			EST_NF_SAI.VLR_NF , 
			EST_NF_SAI.VLR_NF , 
			EST_NF_SAI.CD_CLI ,
			CONV.CD_CONV

	 UNION ALL

		 -- Devolucao de venda NFE DUPLICATA
		 SELECT    
			 9 AS SEQ_CONSULTA,
			 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,
			 EST_NF_SAI.CD_NF AS CD_VD, 
			 EST_NF_SAI.DT_EMI_NF AS DT_VD, 
		     SUM(EST_NF_SAI.VLR_NF - EST_NF_SAI.VLR_FRETE) AS VLR_LIQ_VD, 
			 0 AS VLR_DINH,
			 0 AS  VLR_CHQS,
			 0 AS VL_CHQS_A_VISTA, 
			 0 AS VL_CHQS_A_PRZ, 
			 0 AS VLR_TROCO_CHQ,
			 0 AS VLR_CONV, 
			 0 AS VLR_CARTAO, 
			 0 AS CD_ADM_CARTAO, 
			 EST_NF_SAI.CD_CLI,
			 0 AS CD_CONV,
			 'DUPLICATA' AS TP_DEVO,
			 0 AS VLR_CONV_PBM,
			 SUM(ISNULL(EST_NF_ENT.VLR_FRETE,0)) AS VLR_FRETE
		 FROM
			 EST_NF_ENT 
              INNER JOIN EST_NF_SAI_NF_ENT ON 
			      EST_NF_ENT.CD_EMP      = EST_NF_SAI_NF_ENT.CD_EMP AND
			      EST_NF_ENT.CD_FILIAL   = EST_NF_SAI_NF_ENT.CD_FILIAL AND
			      EST_NF_ENT.CD_NF       = EST_NF_SAI_NF_ENT.CD_NF_ENT
              
			   INNER JOIN EST_NF_SAI ON

				 EST_NF_SAI.CD_EMP      = EST_NF_SAI_NF_ENT.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL   = EST_NF_SAI_NF_ENT.CD_FILIAL AND
				 EST_NF_SAI.CD_NF       = EST_NF_SAI_NF_ENT.CD_NF_SAI
				 INNER JOIN GLB_MOV_OP   ON   EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP

				 INNER JOIN EST_NF_SAI_IT ON
				
			     EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND
		         EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND
		         EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF

				 AND NOT EXISTS(SELECT CD_EMP FROM EST_NF_SAI_RC_VD_CONV WHERE 
				     EST_NF_SAI.CD_EMP = EST_NF_SAI_RC_VD_CONV.CD_EMP
				     AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_RC_VD_CONV.CD_FILIAL
				     AND EST_NF_SAI.CD_NF = EST_NF_SAI_RC_VD_CONV.CD_NF  )
  
		 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
			 AND GLB_MOV_OP.TP_MOV IN  (0, 65)
		 GROUP BY
			 EST_NF_ENT.CD_EMP,
			 EST_NF_ENT.CD_FILIAL,
			 EST_NF_ENT.CD_NF,
			 EST_NF_ENT.DT_CONTABILIZAR,
			 EST_NF_ENT.DT_CONTABILIZAR ,
			 EST_NF_SAI.CD_NF  , 
			 EST_NF_SAI.DT_EMI_NF  , 
			 EST_NF_SAI.VLR_NF , 
			 EST_NF_SAI.VLR_NF , 
			 EST_NF_SAI.CD_CLI 

    RETURN
END