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

RETURNS @RS_RETURN TABLE(
		CD_EMP				INT, 
		CD_FILIAL			INT, 		
		VLR_DEV_SECAO		MONEY,
		VLR_CUSTO_MD		MONEY,
		DT_CONTABILIZAR		DATETIME)
				
AS
BEGIN
	
	INSERT INTO @RS_RETURN 	
		SELECT 
			EST_NF_ENT.CD_EMP, 
			EST_NF_ENT.CD_FILIAL, 
			SUM((EST_NF_ENT_IT.VLR_TOT_IT + ISNULL(VLR_OUTRAS_DESPESAS,0)) - EST_NF_ENT_IT.VLR_DESC) AS VLR_DEV_SECAO, 
			SUM(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_CUSTO_MD) AS VLR_CUSTO_MD,
			EST_NF_ENT.DT_CONTABILIZAR

	    FROM 
			EST_NF_ENT 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

			INNER JOIN GLB_MOV_OP ON
			EST_NF_ENT.Cd_Nt_Mov = GLB_MOV_OP.Cd_Mov_Op
	       
	    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_OP_TRANSA = 0 
			AND GLB_MOV_OP.TP_MOV 			= 11 
			--AND NOT EXISTS ( SELECT 
		 --                        CD_EMP 
		 --                    FROM 
		 --                        EST_NF_SAI_NF_ENT 
		 --                    WHERE 
		 --                        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)

		 -- Devolução de vendas permitido ( a Saída o  TP_MOV = 0 )
		 -- Barrar Entrada devolução de fornecedor ( a Saída o TP_MOV = 11)
		 AND NOT EXISTS (SELECT 
		                         EST_NF_SAI_NF_ENT.CD_EMP 
		                     FROM 
		                         EST_NF_SAI_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
		                     WHERE 
		                         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
								 AND GLB_MOV_OP.TP_MOV = 11
								 )
	    
	     GROUP BY 
			EST_NF_ENT.CD_EMP, EST_NF_ENT.CD_FILIAL, EST_NF_ENT.DT_CONTABILIZAR
			
		 ORDER BY 
			EST_NF_ENT.DT_CONTABILIZAR, EST_NF_ENT.CD_EMP, EST_NF_ENT.CD_FILIAL
   
    RETURN
    
END        