/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_FORN]    Script Date: 12/10/2013 15:17:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_FORN]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_FORN]
GO

--SELECT * FROM UFD_EST_PROD_FORN(1,99,878) where ds_prod like '%leite n%' 
CREATE     FUNCTION [dbo].[UFD_EST_PROD_FORN](
--CREATE FUNCTION UFD_EST_PROD_FORN(
												@CD_EMP		INT,
												@CD_FILIAL	INT,	
												@CD_FORN		INT)
RETURNS @RS_EST_PROD_FORN TABLE(
										CD_PROD INT, 
										CD_PROD_FORN VARCHAR(300),
										DS_PROD VARCHAR(300), 
										QT_EST MONEY, 
										QT_PEND MONEY)
AS
BEGIN
DECLARE @RS_PROD TABLE (CD_PROD INT, CD_PROD_FORN VARCHAR(300), DS_PROD VARCHAR(300), QT_EST MONEY, QT_PEND MONEY)
DECLARE @RS_PROD_PED_ENT TABLE (CD_PROD INT, CD_PROD_FORN VARCHAR(100), QT_PEND MONEY)

/*INSERT INTO @RS_PROD_PED_ENT
	SELECT 
		CD_PROD, 
		CD_PROD_FORN,
--		DBO.UFD_BUSCA_QT_EST_PROD(EST_PROD_FORN.CD_EMP , @CD_FILIAL, EST_PROD_FORN.CD_PROD, 2),
		0
	FROM 
		EST_PROD_FORN
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FORN = @CD_FORN*/

INSERT INTO @RS_PROD
	SELECT     
		EST_PROD.CD_PROD, 
		EST_PROD_FORN.CD_PROD_FORN, 
		EST_PROD.DS_PROD, 
		EST_PROD_CPL.QT_EST,
		0
	FROM         
		EST_PROD,
		EST_PROD_CPL,
		EST_PROD_FORN
	WHERE
		EST_PROD.CD_EMP = EST_PROD_FORN.CD_EMP AND
		EST_PROD.CD_PROD = EST_PROD_FORN.CD_PROD AND
		EST_PROD.CD_EMP = EST_PROD_CPL.CD_EMP AND
		EST_PROD.CD_PROD = EST_PROD_CPL.CD_PROD AND
		EST_PROD_CPL.CD_EMP = @CD_EMP
		AND EST_PROD_CPL.CD_FILIAL = @CD_FILIAL
		AND EST_PROD_CPL.TP_PROD <> 6
		AND EST_PROD.STS_PROD = 0
		AND EST_PROD_FORN.CD_FORN = @CD_FORN
INSERT INTO @RS_EST_PROD_FORN SELECT * FROM @RS_PROD
RETURN
END
GO


