IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'UFD_SINTEGRA_REG_60D')
	DROP FUNCTION [dbo].[UFD_SINTEGRA_REG_60D]
GO
--select * from UFD_SINTEGRA_REG_60D(1,1,'2005-07-01','2005-07-31',1) WHERE CD_PROD  = 104    
CREATE FUNCTION [dbo].[UFD_SINTEGRA_REG_60D](  
             @CD_EMP  INT,  
             @CD_FILIAL   VARCHAR(50) ,  
             @DT_INI      DATETIME,  
             @DT_FIM      DATETIME,
			 @CD_CX INT)  
RETURNS @UFD_RETURN TABLE(  
      DT_EMIS      DATETIME,  
      NR_SERIE   VARCHAR(100),  
      CD_PROD   INT,  
      TRIB       VARCHAR(4),  
      ST_TRIB      VARCHAR(4),        
      PERC_ICMS  MONEY,        
      QT_TOTAL  MONEY,  
      VLR_TOTAL  MONEY,  
      VLR_BASE_ICMS  MONEY,  
      VLR_ICMS  MONEY,  
      CFOP   VARCHAR(10),  
      PIS_COFINS  INT,
	  CD_CX INT)  
BEGIN  
DECLARE @RS_VD_IT TABLE(  
      CD_EMP   INT,  
      CD_FILIAL       INT,       
      DT_EMIS      DATETIME,  
      CD_CX   INT,   
      CD_PROD   INT,  
      QT_TOTAL  MONEY,  
      VLR_TOTAL  MONEY,  
      PIS_COFINS  INT,  
      TRIB_FC   VARCHAR(3),  
      TX_ICMS_IT  MONEY,
	  TX_RED_IT  MONEY)  
  
INSERT INTO  
 @RS_VD_IT  
SELECT       
	 PDV_VD.CD_EMP,  
	 PDV_VD.CD_FILIAL,   
	 PDV_VD.DT_VD,   
	 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,  
	 PDV_VD_IT.PIS_COFINS,  
	 PDV_VD_IT.CD_TRIB_FC,  
	 PDV_VD_IT.TX_ICMS_IT,
	 COALESCE(PDV_VD_IT.TX_RED_IT,0) AS TX_RED_IT
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 PDV_VD.CD_CX = @CD_CX
GROUP BY   
	 PDV_VD.CD_EMP,  
	 PDV_VD.CD_FILIAL,  
	 PDV_VD.CD_CX,   
	 PDV_VD.DT_VD,   
	 PDV_VD_IT.CD_PROD,  
	 PDV_VD_IT.PIS_COFINS,  
	 PDV_VD_IT.CD_TRIB_FC,  
	 PDV_VD_IT.TX_ICMS_IT,
	 COALESCE(PDV_VD_IT.TX_RED_IT,0)
  
INSERT INTO @UFD_RETURN  
 SELECT       
  RS_VD_IT.DT_EMIS,  
  PDV_CX.NR_SERIE_IMP,  
  RS_VD_IT.CD_PROD,  
  EST_TRIB.CD_TRIB_FC,  
  ST_TRIB = CASE WHEN EST_TRIB.ICMS = 1 THEN  
         'T'        
       WHEN EST_TRIB.ISENTO = 1 THEN  
         'I'  
       WHEN EST_TRIB.SUBS_TRIB_ANT = 1 OR SUBS_TRIB = 1 THEN  
         'F'         
       END,  
  TX_ICMS_IT,  
  RS_VD_IT.QT_TOTAL,  
  RS_VD_IT.VLR_TOTAL,  
  VLR_BASE_ICMS = CASE WHEN EST_TRIB.ICMS = 1 THEN  
         RS_VD_IT.VLR_TOTAL        
        ELSE  
         0  
        END,  
  VLR_ICMS = CASE WHEN EST_TRIB.ICMS = 1 THEN 
				(RS_VD_IT.VLR_TOTAL * TX_ICMS_IT) / 100  
			ELSE  
				0  
			END,  
  ST_TRIB = CASE WHEN EST_TRIB.ICMS = 1 THEN  
       '5102'        
     WHEN EST_TRIB.ISENTO = 1 THEN  
       '5102'  
     WHEN EST_TRIB.SUBS_TRIB_ANT = 1 OR SUBS_TRIB = 1 THEN  
       '5405'         
     END,  
  PIS_COFINS   ,
  RS_VD_IT.CD_CX
 FROM           
  @RS_VD_IT RS_VD_IT INNER JOIN EST_TRIB ON   
  RS_VD_IT.TRIB_FC COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI  = EST_TRIB.CD_TRIB_FC COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI   
  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       
RETURN  
END
