 
/****** Object:  UserDefinedFunction [dbo].[UFD_SPED_PRODUTOS]    Script Date: 03/27/2013 17:13:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_SPED_PRODUTOS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_SPED_PRODUTOS]
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_SPED_PRODUTOS]    Script Date: 03/27/2013 17:13:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
CREATE FUNCTION [dbo].[UFD_SPED_PRODUTOS] ( @CD_EMP INT, @CD_FILIAL NVARCHAR(500), @DT_INI DATETIME, @DT_FIM DATETIME )

RETURNS @RsProd  TABLE (CD_PROD			INT, 
							DS_PROD			VARCHAR (150), 
							CD_NCM			VARCHAR (20), 
							TP_PROD_CP		VARCHAR(2))   
BEGIN
	
	DECLARE @Prod  TABLE (CD_PROD			INT, 
							DS_PROD			VARCHAR (150), 
							CD_NCM			VARCHAR (20), 
							TP_PROD_CP		VARCHAR(2)) 
	
	INSERT INTO @Prod
	SELECT     
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD, 
		MIN(EST_PROD.NR_NCM), 
		'00' AS TP_PROD_CP 
	FROM 
		EST_NF_SAI_IT  
		INNER JOIN EST_NF_SAI ON  
		EST_NF_SAI_IT.CD_EMP = EST_NF_SAI.CD_EMP AND 
		EST_NF_SAI_IT.CD_FILIAL  = EST_NF_SAI.CD_FILIAL AND 
		EST_NF_SAI_IT.CD_NF = EST_NF_SAI.CD_NF 
		INNER JOIN EST_PROD ON 
		EST_NF_SAI_IT.CD_EMP = EST_PROD.CD_EMP AND
		EST_NF_SAI_IT.CD_PROD = EST_PROD.CD_PROD
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV =  GLB_MOV_OP.CD_MOV_OP 
	WHERE 
		EST_NF_SAI.DT_EMI_NF  BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_FILIAL  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND EST_NF_SAI.CD_EMP = @CD_EMP
		AND GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED = 0
	GROUP BY
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD
	 
	INSERT INTO @Prod
	SELECT 
		EST_PROD.CD_PROD ,  
		EST_PROD.DS_PROD, 
		MIN(EST_PROD.NR_NCM),
		'00' AS TP_PROD_CP 
	FROM EST_NF_ENT_IT INNER JOIN EST_NF_ENT ON  
		EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP AND EST_NF_ENT_IT.CD_FILIAL  = EST_NF_ENT.CD_FILIAL AND 
		EST_NF_ENT_IT.CD_NF = EST_NF_ENT.CD_NF 
		INNER JOIN EST_PROD ON 
		EST_NF_ENT_IT.CD_PROD = EST_PROD.CD_PROD AND
		EST_NF_ENT_IT.CD_PROD = EST_PROD.CD_PROD 
		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.DT_ENT  BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_ENT.CD_FILIAL  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED = 0
	GROUP BY
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD
	 
	INSERT INTO @Prod
	SELECT  
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD, 
		MIN(EST_PROD.NR_NCM), 
		'00' AS TP_PROD_CP 
	FROM 
		PDV_VD_IT 
		INNER JOIN PDV_VD ON  
		PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP AND 
		PDV_VD_IT.CD_FILIAL  = PDV_VD.CD_FILIAL AND 
		PDV_VD_IT.CD_VD = PDV_VD.CD_VD 
		INNER JOIN EST_PROD ON 
		PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD AND
		PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP  		
	WHERE 
		PDV_VD.DT_VD  BETWEEN @DT_INI AND @DT_FIM
		AND PDV_VD.CD_FILIAL    IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND PDV_VD.ST_VD =  0
		AND PDV_VD.CD_EMP =@CD_EMP
	GROUP BY
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD
	INSERT INTO @RsProd	
	SELECT DISTINCT   
		CD_PROD, 
		DS_PROD, 
		ISNULL(CD_NCM,'') CD_NCM, 
		'00' AS TP_PROD_CP  
	FROM  
		@Prod;		
 
	RETURN
END

GO


