/****** Object:  UserDefinedFunction [dbo].[UFD_APURACAO_ICMS]    Script Date: 07/20/2012 10:20:11 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_APURACAO_ICMS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_APURACAO_ICMS]
 
 
GO

/*
	
SELECT *  FROM UFD_APURACAO_ICMS(1,'1,3,4,5,6','01/01/2012','01/30/2012') 
 

*/

create  FUNCTION [dbo].[UFD_APURACAO_ICMS] (	
											@CD_EMP		INT,
											@FILIAL  	VARCHAR(100),
											@DT_INI		DATETIME,
											@DT_FIM		DATETIME
											)

RETURNS @RS_RETORNO TABLE (CD_EMP 	                 INT,
						   CD_FILIAL	             INT,
						   VLR_TOT_CREDITOS           MONEY, 
						   VLR_TOT_DEBITOS           MONEY) 
 			
AS
BEGIN

DECLARE @RS_ICMS TABLE (
						   CD_EMP 		    INT,
						   CD_FILIAL	    INT,
						   VLR_ICMS_CRED    MONEY,
						   VLR_ICMS_DEB     MONEY)
					   
 
INSERT INTO @RS_ICMS
	SELECT     
		PDV_MAPA_RESUMO.CD_EMP, 
		PDV_MAPA_RESUMO.CD_FILIAL,
		0 AS VLR_ICMS_CRED, 
        ISNULL(VLR_ICMS,0) VLR_ICMS_DEB 
FROM         
		PDV_MAPA_RESUMO 
	WHERE
		PDV_MAPA_RESUMO.CD_EMP = @CD_EMP
		AND PDV_MAPA_RESUMO.CD_FILIAL IN(SELECT ID FROM UFD_PRM_IN_STRING(@FILIAL)) 
		AND PDV_MAPA_RESUMO.DT_ECF  BETWEEN @DT_INI AND @DT_FIM
        
 
INSERT INTO @RS_ICMS
	
	SELECT     
		EST_NF_SAI.CD_EMP, 
		 EST_NF_SAI.CD_FILIAL,
		0 AS VLR_ICMS_CRED,
		
		CASE WHEN EST_TRIB.ICMS = 1 THEN  
			SUM(EST_NF_SAI_IT.VLR_ICMS_IT)
		ELSE
			0
		END AS  VLR_ICMS_DEB
		  -- SELECT TOP 1 * FROM EST_NF_SAI_IT
	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_TRIB ON 
        EST_NF_SAI_IT.CD_TRIB = EST_TRIB.CD_TRIB 
		
	WHERE
		EST_NF_SAI.CD_EMP = @CD_EMP 
		AND EST_NF_SAI.CD_FILIAL IN(SELECT ID FROM UFD_PRM_IN_STRING(@FILIAL)) 
		AND EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.STS_NF = 0
		
		AND NOT EXISTS (SELECT 
							CD_NF 
						FROM 
							PDV_VD_EST_NF_SAI 
						WHERE 
							PDV_VD_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND
							PDV_VD_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
							PDV_VD_EST_NF_SAI.CD_NF = EST_NF_SAI.CD_NF)
     GROUP BY 		
        EST_NF_SAI.CD_EMP, 
		EST_NF_SAI.CD_FILIAL,
		EST_TRIB.ICMS

INSERT INTO @RS_ICMS
	SELECT     
		EST_NF_ENT.CD_EMP, 
		EST_NF_ENT.CD_FILIAL, 
		
		CASE WHEN EST_TRIB.ICMS = 1 THEN 
		SUM(EST_NF_ENT_IT.VLR_ICMS_IT ) 
		ELSE
			0
		END  ASVLR_ICMS_CRED,
		
		0 AS VLR_ICMS_DEB
		
	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 EST_TRIB ON 
			EST_NF_ENT_IT.CD_TRIB = EST_TRIB.CD_TRIB 
			 
	WHERE
		 EST_NF_ENT.CD_EMP = @CD_EMP 
		AND  EST_NF_ENT.CD_FILIAL IN(SELECT ID FROM UFD_PRM_IN_STRING(@FILIAL)) 
		AND  EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND  EST_NF_ENT.STS_NF = 0
		
   GROUP BY 		
        EST_NF_ENT.CD_EMP, 
		EST_NF_ENT.CD_FILIAL,
		EST_TRIB.ICMS

INSERT INTO @RS_RETORNO
	SELECT 
	     RS.CD_EMP ,
	     RS.CD_FILIAL ,
	     SUM(RS.VLR_ICMS_CRED)VLR_ICMS_CRED,
	     SUM(rs.VLR_ICMS_DEB)VLR_ICMS_DEB
	    
    FROM @RS_ICMS RS 
    
    GROUP BY
		 RS.CD_EMP ,
	     RS.CD_FILIAL
  
RETURN
END

