IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_SINTEGRA_REG_50]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_SINTEGRA_REG_50]
GO 
--SELECT * FROM UFD_SINTEGRA_REG_50 (1,27,'2015-12-01','2015-12-31') 
CREATE FUNCTION [dbo].[UFD_SINTEGRA_REG_50] (  
            @CD_EMP INT,  
            @CD_FILIAL VARCHAR(MAX),  
            @DT_INI DATETIME,  
            @DT_FIM DATETIME)  

-- VERSAO  4270          
RETURNS @RS_NF_SINTEGRA TABLE  (  
       CD_EMP    INT,  
       CD_FILIAL   INT,  
       CD_NF     INT,  
       CGC_CPF     VARCHAR (100),  
       INSC_EST      VARCHAR (100),              
       DT_EMI_NF   DATETIME,  
       UF      VARCHAR(100),  
       SERIE_NF    VARCHAR(100),  
       NR_NF         INT,  
       CD_NT_OP_FC      VARCHAR(100),                       
       VLR_TOT_NF   MONEY,  
       VLR_TOT_IT      MONEY,  
       VLR_BASE_ICMS  MONEY,  
       VLR_ICMS    MONEY,  
       ALIQ_ICMS   MONEY,  
       VLR_IPI    MONEY,  
       VLR_OUTROS   MONEY,         
       STS_NF    INT,  
       TP_TRANSA   INT,  
		TP_SR_NFE           INT,  
		CD_ORIG_DEST        MONEY,
	  MODELO VARCHAR(10),
	  TIPO_EMISSAO  VARCHAR(1),
	  VLR_ICMS_DESONERADO MONEY,
	  VLR_ISENTO MONEY)  
AS  
BEGIN  
DECLARE @RS_NF TABLE (  
       CD_EMP     INT,  
       CD_FILIAL   INT,  
       CD_NF     INT,  
       CGC_CPF     VARCHAR (100),  
       INSC_EST      VARCHAR (100),              
       DT_EMI_NF   DATETIME,  
       UF      VARCHAR(100),  
       SERIE_NF    VARCHAR(100),  
       NR_NF         INT,  
       CD_NT_OP_FC     VARCHAR(100),                       
       VLR_TOT_NF   MONEY,  
       VLR_TOT_IT      MONEY,  
       VLR_BASE_ICMS  MONEY,  
       VLR_ICMS    MONEY,  
       ALIQ_ICMS   MONEY,  
       VLR_IPI    MONEY,   
       VLR_OUTROS   MONEY,  
       STS_NF    INT,  
       TP_TRANSA   INT,  
		TP_SR_NFE           INT,  
		CD_ORIG_DEST        MONEY,
       MODELO VARCHAR(10),
	   TIPO_EMISSAO  VARCHAR(1),
	   VLR_ICMS_DESONERADO MONEY,
	   VLR_ISENTO MONEY)  
 
--Somente nota fiscal com vinculo com cupom  
INSERT INTO @RS_NF  
 SELECT     
	V_EST_NF_SAI_SINTEGRA.CD_EMP,  
	V_EST_NF_SAI_SINTEGRA.CD_FILIAL,  
	V_EST_NF_SAI_SINTEGRA.CD_NF,  
	V_EST_NF_SAI_SINTEGRA.CGC_CPF,   
	V_EST_NF_SAI_SINTEGRA.INSC_EST,   
	V_EST_NF_SAI_SINTEGRA.DT_EMI_NF,   
	V_EST_NF_SAI_SINTEGRA.UF,   
	LEFT(V_EST_NF_SAI_SINTEGRA.SERIE_NF,3) AS SERIE_NF,   
	V_EST_NF_SAI_SINTEGRA.NR_NF,   
	V_EST_NF_SAI_SINTEGRA.CFOP AS CD_NT_OP_FC,  
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
	 (SUM(VLR_TOT_IT) + SUM(VLR_ICMS_SUBS) + SUM(VLR_IPI) + SUM(VLR_OUTROS_DESPESAS ) ) 
	ELSE
		0
	END AS VLR_TOT_NF,  
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
	 SUM(VLR_TOT_IT) 
	ELSE
		0
	END AS VLR_TOT_IT, 
	 
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
		SUM(VLR_ICMS_BASE)
	ELSE
		0
	END AS VLR_BASE_ICMS,   
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
		SUM(VLR_ICMS)
	ELSE
		0
	END AS VLR_ICMS,   
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
		ALIQ_ICMS
	ELSE
		0
	END AS ALIQ_ICMS,
	CASE WHEN  PDV_VD_EST_NF_SAI.CD_NF  IS NULL THEN
		SUM(VLR_IPI)
	ELSE
		0
	END AS VLR_IPI,  
	CASE WHEN  SUM(VLR_ICMS) = 0 AND  SUM(VLR_ICMS_SUBS) = 0 AND SUM(VLR_ISENTO)= 0 THEN
		SUM(VLR_TOT_IT)
	ELSE
		0
	END +
	CASE WHEN  SUM(VLR_ICMS_SUBS) > 0 THEN
		SUM(VLR_ICMS_SUBS+VLR_TOT_IT_SUBS)
	ELSE
		0
	END + SUM(VLR_OUTROS_DESPESAS )  AS VLR_OUTROS,  
	STS_NF,  
	0,  
	TP_SR_NFE,  
	0,
	COD_MODELO,
	'P' AS TIPO_EMISSAO,
	sum(VLR_ICMS_DESONERADO) as VLR_ICMS_DESONERADO,
	SUM(VLR_ISENTO) AS VLR_ISENTO
 FROM           
  V_EST_NF_SAI_SINTEGRA LEFT OUTER JOIN PDV_VD_EST_NF_SAI ON  
  V_EST_NF_SAI_SINTEGRA.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND  
  V_EST_NF_SAI_SINTEGRA.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND  
  V_EST_NF_SAI_SINTEGRA.CD_NF = PDV_VD_EST_NF_SAI.CD_NF   
 WHERE       
  V_EST_NF_SAI_SINTEGRA.CD_EMP = @CD_EMP   
  AND V_EST_NF_SAI_SINTEGRA.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
  AND V_EST_NF_SAI_SINTEGRA.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM  
 GROUP BY   
  V_EST_NF_SAI_SINTEGRA.CD_EMP,  
  V_EST_NF_SAI_SINTEGRA.CD_FILIAL,  
  V_EST_NF_SAI_SINTEGRA.CD_NF,  
  V_EST_NF_SAI_SINTEGRA.CGC_CPF,   
  V_EST_NF_SAI_SINTEGRA.INSC_EST,   
  V_EST_NF_SAI_SINTEGRA.UF,   
  V_EST_NF_SAI_SINTEGRA.SERIE_NF,   
  V_EST_NF_SAI_SINTEGRA.NR_NF,   
  V_EST_NF_SAI_SINTEGRA.DT_EMI_NF,   
  V_EST_NF_SAI_SINTEGRA.CFOP,  
  V_EST_NF_SAI_SINTEGRA.VLR_DESC,  
  V_EST_NF_SAI_SINTEGRA.STS_NF,
  V_EST_NF_SAI_SINTEGRA.TP_SR_NFE,
  COD_MODELO,
   PDV_VD_EST_NF_SAI.CD_NF,
   V_EST_NF_SAI_SINTEGRA.ALIQ_ICMS,
   V_EST_NF_SAI_SINTEGRA.VLR_ICMS_DESONERADO
UNION ALL
 SELECT       
  CD_EMP,  
  CD_FILIAL,  
  CD_NF,  
  CGC_CPF,   
  INSC_EST,   
  DT_ENT,   
  UF,   
  LEFT(SERIE_NF,3) AS SERIE_NF,   
  NR_NF,   
  CFOP,  
  SUM(VLR_TOT_IT) + SUM(VLR_ICMS_SUBS_CONTABIL) + SUM(VLR_IPI) + SUM(VLR_OUTROS_CABE_RATEIO) + SUM(VLR_FRETE_CABE_RATEIO),  
  SUM(VLR_TOT_IT), 

  CASE WHEN FLAG_MICRO_EMPRESA= 0 THEN  
	SUM(VLR_ICMS_BASE)
  ELSE
	0 
  END,
  
  CASE WHEN FLAG_MICRO_EMPRESA= 0 THEN    
	SUM(VLR_ICMS)
  ELSE
	0
  END ,   

  CASE WHEN FLAG_MICRO_EMPRESA= 0 THEN 
    ALIQ_ICMS
  ELSE
	0
  END,   
  SUM(VLR_IPI), 
  CASE WHEN  SUM(VLR_ICMS) = 0 AND  SUM(VLR_ICMS_SUBS) = 0 AND SUM(VLR_ISENTO)=0 THEN
		SUM(VLR_TOT_IT)
	ELSE
		0
	END +
	CASE WHEN  SUM(VLR_ICMS_SUBS) > 0  THEN
		SUM(VLR_ICMS_SUBS+VLR_TOT_IT_SUBS)
	ELSE
		0
	END + SUM(VLR_OUTROS_CABE_RATEIO) + SUM(VLR_ICMS_SUBS_CONTABIL) AS VLR_OUTROS,  
  STS_NF,  
  1,  
  TP_SR_NFE,  
  CD_ORIG_DEST,
  COD_MODELO,
  CASE WHEN FLAG_EMISSAO_PROPRIA=1 THEN
	'P'
  ELSE
	'T'
  END ,
  sum(VLR_ICMS_DESONERADO) as VLR_ICMS_DESONERADO,
  CASE WHEN FLAG_MICRO_EMPRESA= 0 THEN 
	SUM(VLR_ISENTO)
  ELSE
	 0
  END AS VLR_ISENTO

 FROM           
  V_EST_NF_ENT_SINTEGRA   
 WHERE       
  CD_EMP = @CD_EMP   
  AND CD_FILIAL IN  (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
  AND DT_ENT BETWEEN @DT_INI AND @DT_FIM  
 GROUP BY   
  CD_EMP,  
  CD_FILIAL,  
  CD_NF,  
  CGC_CPF,   
  INSC_EST,   
  UF,   
  SERIE_NF,   
  NR_NF,   
  DT_ENT,   
  CFOP,  
  STS_NF,  
  ALIQ_ICMS,  
  TP_SR_NFE,  
  CD_ORIG_DEST,
  COD_MODELO,
  FLAG_EMISSAO_PROPRIA,
  FLAG_MICRO_EMPRESA

INSERT INTO @RS_NF
 SELECT   
		V_EST_NF_ENT_CAB.CD_EMP,  
		V_EST_NF_ENT_CAB.CD_FILIAL,  
		V_EST_NF_ENT_CAB.CD_NF,  
		V_EST_NF_ENT_CAB.CGC_CPF,   
		V_EST_NF_ENT_CAB.INSC_EST,   
		V_EST_NF_ENT_CAB.DT_ENT,   
		V_EST_NF_ENT_CAB.UF,   
		LEFT(V_EST_NF_ENT_CAB.SERIE,3) AS SERIE,   
		V_EST_NF_ENT_CAB.NR_NF,  
		'' AS CD_NT_OP_FC,   
		V_EST_NF_ENT_CAB.VLR_NF,   
		V_EST_NF_ENT_CAB.VLR_NF,
		CASE WHEN FLAG_MICRO_EMPRESA = 0 THEN   
			V_EST_NF_ENT_CAB.VLR_BASE_ICMS
		ELSE
			0
		END ,

		CASE WHEN FLAG_MICRO_EMPRESA = 0 THEN   
			V_EST_NF_ENT_CAB.VLR_ICMS
		ELSE
			0
	    END, 
		  
		0 AS ALIQ_ICMS,  
		 
		V_EST_NF_ENT_CAB.VLR_IPI,   
		V_EST_NF_ENT_CAB.VLR_ICMS_SUBS,   
		V_EST_NF_ENT_CAB.STS_NF,  
		1,  
		V_EST_NF_ENT_CAB.TP_SR_NFE,  
		V_EST_NF_ENT_CAB.CD_FORN AS CD_ORIG_DEST,
		COD_MODELO,
		CASE WHEN ISNULL(EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT,0) > 0 THEN
			'P'
		ELSE
		    'T'
		END FLAG_EMISSAO_PROPRIA ,
		VLR_ICMS_DESONERADO_CAB AS VLR_ICMS_DESONERADO,
		0 AS VLR_ISENTO
 FROM   
		V_EST_NF_ENT_CAB 
		LEFT OUTER JOIN EST_NF_ENT_EF_NF_CTR_NF ON
		V_EST_NF_ENT_CAB.CD_EMP = EST_NF_ENT_EF_NF_CTR_NF.CD_EMP AND
		V_EST_NF_ENT_CAB.CD_FILIAL = EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL AND
		V_EST_NF_ENT_CAB.CD_NF = EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT	  
		LEFT OUTER JOIN @RS_NF RS_NF ON
		V_EST_NF_ENT_CAB.CD_EMP = RS_NF.CD_EMP  AND  
		V_EST_NF_ENT_CAB.CD_FILIAL = RS_NF.CD_FILIAL  AND  
		V_EST_NF_ENT_CAB.CD_NF = RS_NF.CD_NF AND
		TP_TRANSA=1
 WHERE   
		V_EST_NF_ENT_CAB.CD_EMP =@CD_EMP  
		AND V_EST_NF_ENT_CAB.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND V_EST_NF_ENT_CAB.DT_ENT BETWEEN @DT_INI AND @DT_FIM  
		AND V_EST_NF_ENT_CAB.TP_MOV not in ( 29 ,32)
		--29 Frete 
		--32 serviço
		AND V_EST_NF_ENT_CAB.STS_NF = 0   
		AND RS_NF.CD_NF IS NULL
        
INSERT INTO @RS_NF_SINTEGRA SELECT DISTINCT * FROM @RS_NF ORDER BY CD_NF  
RETURN  
END