/****** Object:  UserDefinedFunction [dbo].[UFD_SINTEGRA_REG_60R]    Script Date: 07/23/2012 19:04:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_SINTEGRA_REG_60R]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_SINTEGRA_REG_60R]
GO
--SELECT * FROM UFD_SINTEGRA_REG_60R(1,201,'2015-05-05') WHERE CD_PROD = 7986
CREATE FUNCTION [dbo].[UFD_SINTEGRA_REG_60R](
											  @CD_EMP		INT,
											  @CD_FILIAL   VARCHAR(2000),
											  @DT_INI      DATETIME,
											  @DT_FIM     DATETIME)
	 										   
									
											  
RETURNS @UFD_RETURN TABLE(
					   MES     				INT,
						ANO     			INT,
						NR_SERIE			VARCHAR(100),
						CD_PROD				INT,						
						ST_TRIB     		VARCHAR(4),						
						PERC_ICMS			MONEY,						
						QT_TOTAL			MONEY,
						VLR_TOTAL			MONEY,
						VLR_BASE_ICMS 		MONEY,
						VLR_ICMS			MONEY)
						

BEGIN
DECLARE @RS_VD_IT TABLE(
						CD_EMP			INT,
						CD_FILIAL       INT,					
					    MES     		INT,
						ANO     		INT,
						CD_CX			INT,	
						CD_PROD			INT,
						QT_TOTAL		MONEY,
						VLR_TOTAL		MONEY,
						TX_ICMS			MONEY,
						TRIB			VARCHAR(10))

						INSERT INTO
	@RS_VD_IT
SELECT     
	PDV_VD_IT.CD_EMP,
	PDV_VD_IT.CD_FILIAL,	
	MONTH(PDV_VD_IT.DT_VD) AS MES, 
	YEAR(PDV_VD_IT.DT_VD) AS ANO, 
	PDV_VD.CD_CX, 
	PDV_VD_IT.CD_PROD, 
	SUM(PDV_VD_IT.QT_IT) AS QT_TOTAL, 
	SUM(PDV_VD_IT.TOT_IT) AS VLR_TOTAL,
	ISNULL(PDV_VD_IT.TX_ICMS_IT,0),
	PDV_VD_IT.CD_TRIB_FC
FROM           
	 PDV_VD INNER JOIN PDV_VD_IT ON
	 PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND
	 PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
	 PDV_VD.CD_VD = PDV_VD_IT.CD_VD AND
	 PDV_VD.DT_VD = PDV_VD_IT.DT_VD 
WHERE     
	PDV_VD_IT.CD_EMP = @CD_EMP
	AND PDV_VD_IT.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))    
	AND PDV_VD_IT.DT_VD between @DT_INI and @DT_FIM
	
	AND PDV_VD.TIPO = 0
	AND PDV_VD.ST_VD = 0  
	AND PDV_VD_IT.ST_IT = 1
	AND ISNULL(PDV_VD.FLAG_NFCE, 0) = 0

GROUP BY 
	PDV_VD_IT.CD_EMP,
	PDV_VD_IT.CD_FILIAL,
	PDV_VD.CD_CX, 
	MONTH(PDV_VD_IT.DT_VD), 
	YEAR(PDV_VD_IT.DT_VD), 
	PDV_VD_IT.CD_PROD,
	PDV_VD_IT.TX_ICMS_IT,
	PDV_VD_IT.CD_TRIB_FC	

INSERT INTO @UFD_RETURN
	SELECT     
		MES, 
		ANO, 
		PDV_CX.NR_SERIE_IMP,
		RS_VD_IT.CD_PROD,
		ST_TRIB = CASE WHEN RIGHT(RTRIM(TRIB),2) = '00' OR RIGHT(RTRIM(TRIB),2) = '20' THEN
								 'T'						
				   WHEN RIGHT(RTRIM(TRIB),2) = '41' OR  RIGHT(RTRIM(TRIB),2) = '40' THEN
								 'I'
				  WHEN RIGHT(LTRIM(TRIB),2) = '10' OR RIGHT(LTRIM(TRIB),2) = '60' AND RIGHT(LTRIM(TRIB),2) = '70' OR RIGHT(LTRIM(TRIB),2) = 60 THEN
								 'F'	
				   END,
		PERC_ICMS_EST = TX_ICMS, 
		RS_VD_IT.QT_TOTAL,
		RS_VD_IT.VLR_TOTAL,
		VLR_BASE_ICMS = CASE WHEN TX_ICMS <> 0 THEN
								 RS_VD_IT.VLR_TOTAL						
							 ELSE
								 0
							 END,
		VLR_ICMS = CASE WHEN TX_ICMS <> 0 THEN
								 ROUND((RS_VD_IT.VLR_TOTAL *  TX_ICMS) / 100,2)
							 ELSE
								 0
							 END	
	FROM         
		@RS_VD_IT RS_VD_IT INNER JOIN PDV_CX ON 
		PDV_CX.CD_EMP  	= RS_VD_IT.CD_EMP 	AND
		PDV_CX.CD_FILIAL = RS_VD_IT.CD_FILIAL	AND
		PDV_CX.CD_CX  = RS_VD_IT.CD_CX   
	WHERE
		PDV_CX.EXPORT=0
RETURN
END
