IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_TRILHO_EXCESSO_FORN]') AND type in (N'P', N'TF'))
DROP FUNCTION [dbo].[UFD_TRILHO_EXCESSO_FORN]  
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_TRILHO_EXCESSO_FORN]    Script Date: 06/26/2012 15:18:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UFD_TRILHO_EXCESSO_FORN] (@CD_EMP INT, @FILIAL_ORIG INT, @CD_DEST INT, @CD_EXCESSO_MAQ INT)	
 
RETURNS @RS_RETORNO TABLE (	CD_EMP				INT,
							CD_CTR				INT,
							CD_CTR_DEPO			INT,
							CD_EXCESSO_MAQ		INT,
							CD_FILIAL			INT,
							CD_DEST				INT,
							CD_NF				INT,
							CD_PROD				INT, 
							QTDE_ATEND			MONEY,
							VLR_ATEND			MONEY )

AS
BEGIN
		declare @nf int;


		INSERT INTO @RS_RETORNO 
		SELECT				 
			@CD_EMP CD_EMP,
			n.CD_CTR,
			E.CD_CTR_DEPO,
			E.CD_EXCESSO_MAQ,
			@FILIAL_ORIG CD_FILIAL,
			@CD_DEST CD_DEST,
			n.CD_NF,
			e.CD_PROD,  
			QTDE_ATEND = ROUND( CASE WHEN e.EXCESSO > n.NECESSIDADE THEN 
								n.NECESSIDADE ELSE e.EXCESSO END,0) ,
			VLR_ATEND = ROUND( CASE WHEN e.EXCESSO > n.NECESSIDADE THEN 
								n.NECESSIDADE ELSE e.EXCESSO END,0) * E.VLR_CUSTO
		FROM
			TRILHO_MAQ_EXCESSO  e 
			INNER JOIN dbo.V_TRILHO_PROD_FORN_EST_NF_ENT n ON 
			e.CD_EMP		= n.CD_EMP	AND 
			e.CD_CTR_DEPO	= n.CD_CTR_DEPO AND 
			e.CD_FILIAL 	= n.CD_FILIAL  AND 
			e.CD_PROD		= n.CD_PROD AND 
			e.DT_MOV		= n.DT_MOV AND
			e.CD_EXCESSO_MAQ  = n.CD_EXCESSO_MAQ
		WHERE 
			n.CD_EMP=@CD_EMP 
			AND e.CD_FILIAL=@FILIAL_ORIG 
			AND n.CD_FORN  =@CD_DEST
			AND E.DT_MOV   =dbo.F_HOJE()
			AND E.CD_EXCESSO_MAQ = @CD_EXCESSO_MAQ
			AND e.EXCESSO > 0 
			AND n.NECESSIDADE > 0 
			AND (ROUND( CASE WHEN e.EXCESSO > n.NECESSIDADE THEN 
								n.NECESSIDADE ELSE e.EXCESSO END,0)) > 0
		ORDER BY VLR_ATEND DESC; 
		  
		SELECT
			@NF = CD_NF
		FROM
		  (SELECT TOP 1  CD_NF, SUM( VLR_ATEND)  VLR_ATEND FROM @RS_RETORNO
							GROUP BY CD_NF
							ORDER BY  VLR_ATEND DESC) B ; 

		delete from @RS_RETORNO where cd_nf <> @nf;

	RETURN;
END
GO



