/****** Object:  UserDefinedFunction [dbo].[UFD_RELA_EST_CX_FECHA_DEVO]    Script Date: 07/01/2013 10:29:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_EST_CX_FECHA_DEVO_FILIAIS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA_DEVO_FILIAIS]
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_RELA_EST_CX_FECHA_DEVO_FILIAIS]    Script Date: 07/01/2013 10:29:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
										  
CREATE FUNCTION [dbo].[UFD_RELA_EST_CX_FECHA_DEVO_FILIAIS] (  
	 @CD_EMP 	INT
	,@CD_FILIAL VARCHAR(1000)
	,@DT_INI	DATETIME
	,@DT_FIM	DATETIME)  
  
RETURNS @RS_RETORNO TABLE (      
	 CD_EMP   		INT
	,CD_FILIAL  	INT
	,CD_NF    		INT
	,NM_FANT   	VARCHAR(60)
	,CD_VD   		INT
	,CD_CX   		INT
	,NR_NF   		INT
	,DT_EMI_NF     DATETIME
	,VLR_NF   		MONEY
	,NR_ECF   		INT
	,DT_VD   		DATETIME
	,VLR_LIQ_VD  	MONEY
	,TP_DEVO   	VARCHAR(100)
	,CD_CTR_NCC  	INT
	,SITUACAO  	VARCHAR (100)
	,VLR_NCC   	MONEY)  
  
AS  
BEGIN  
	DECLARE @RS_RET TABLE (      
		  CD_EMP   		INT
		 ,CD_FILIAL  	INT
		 ,CD_NF    		INT
		 ,NM_FANT   	VARCHAR(60)
		 ,CD_VD   		INT
		 ,CD_CX   		INT
		 ,NR_NF   		INT
		 ,DT_EMI_NF  	DATETIME
		 ,VLR_NF   		MONEY
		 ,NR_ECF   		INT
		 ,DT_VD   		DATETIME
		 ,VLR_LIQ_VD  	MONEY
		 ,TP_DEVO   	VARCHAR(100)
		 ,CD_CTR_NCC	INT
		 ,SITUACAO   	VARCHAR(100)
		 ,VLR_NCC   	MONEY)  
  
	DECLARE @RS_FILIAL TABLE (CD_EMP INT, CD_FILIAL INT)  
	  
	INSERT INTO @RS_FILIAL  
	SELECT   
		 1 
		,ID   
	FROM   
		UFD_PRM_IN_STRING(@CD_FILIAL)  
	  
	DECLARE @NF_ENTRADA TABLE(  
	    CD_EMP 		INT
	   ,CD_FILIAL 	INT
	   ,CD_NF 		INT
	   ,CD_VD 		INT
	   ,CD_IMP 		INT
	   ,CD_IT 		INT
	   ,CD_PROD 	INT
	   ,NR_NF 		INT
	   ,DT_EMI_NF 	DATETIME)  
	  
	INSERT INTO @NF_ENTRADA  
	SELECT       
		  EST_NF_ENT.CD_EMP
		 ,EST_NF_ENT.CD_FILIAL
		 ,EST_NF_ENT.CD_NF
		 ,PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD
		 ,PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP
		 ,PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT
		 ,PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD
		 ,EST_NF_ENT.NR_NF
		 ,PDV_VD_DEV_EST_PROD_IMP.DT_DEVO  
		 
	FROM 
		DBO.EST_NF_ENT WITH (NOLOCK) 
		
		INNER JOIN DBO.PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT WITH (NOLOCK)
			ON EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP 
			AND EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL 
			AND EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF
		
		INNER JOIN DBO.PDV_VD_DEV_EST_PROD_IMP WITH (NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IT = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_PROD = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD  
		 
	WHERE  
		 PDV_VD_DEV_EST_PROD_IMP.CD_EMP = @CD_EMP  
		 AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		 AND EST_NF_ENT.STS_NF = 0  
		 AND PDV_VD_DEV_EST_PROD_IMP.DT_DEVO BETWEEN @DT_INI AND @DT_FIM
	  
	INSERT INTO @RS_RET  
	SELECT       
		 EST_NF_ENT.CD_EMP
		,EST_NF_ENT.CD_FILIAL
		,EST_NF_ENT.CD_NF
		,PRC_FILIAL.NM_FANT
		,PDV_VD.CD_VD
		,PDV_VD.CD_CX
		,EST_NF_ENT.NR_NF
		,EST_NF_ENT.DT_EMI_NF
		,EST_NF_ENT.VLR_NF
		,PDV_VD.NR_ECF
		,PDV_VD.DT_VD
		,PDV_VD.VLR_LIQ_VD
		,'COM VINCULO COM CUPOM FISCAL'
		,0 AS CD_CTR_NCC
		,'' AS SITUACAO
		,0 AS VLR_NCC    
		
	FROM           
		DBO.PDV_VD_EST_NF_ENT WITH (NOLOCK) 
		
		INNER JOIN DBO.PDV_VD WITH (NOLOCK) 
			ON PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP 
			AND PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL 
			AND PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD   
		
		INNER JOIN DBO.EST_NF_ENT WITH (NOLOCK) 
			ON PDV_VD_EST_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP 
			AND	PDV_VD_EST_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
			AND PDV_VD_EST_NF_ENT.CD_NF = EST_NF_ENT.CD_NF   
		
		INNER JOIN DBO.PRC_FILIAL WITH (NOLOCK) 
			ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP 
			AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
		
	WHERE  
		PDV_VD.CD_EMP = @CD_EMP  
		AND PDV_VD.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		AND PDV_VD.ST_VD = 0   
		AND EST_NF_ENT.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_ENT.STS_NF = 0  

		AND NOT EXISTS
		(SELECT 
			CD_VD 
		 FROM 
			DBO.PDV_VD_DEV_EST_PROD_IMP WITH (NOLOCK)
		 WHERE 
			PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD)
			  
	--Sem vinculo com nota fiscal  
	INSERT INTO @RS_RET  
	SELECT       
		 PDV_VD_DEV_EST_PROD_IMP.CD_EMP
		,PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL
		,ISNULL(EST_NF_ENT.CD_NF,PDV_VD_DEV_EST_PROD_IMP.CD_IMP)
		,PRC_FILIAL.NM_FANT
		,PDV_VD.CD_VD
		,PDV_VD.CD_CX
		,ISNULL(EST_NF_ENT.NR_NF,PDV_VD_DEV_EST_PROD_IMP.CD_IMP)
		,EST_PROD_IMP.DT_CAD
		,SUM(EST_PROD_IMP.QT_IMP * PDV_VD_DEV_EST_PROD_IMP.VLR_IT) AS VLR_NF
		,PDV_VD.NR_ECF
		,PDV_VD.DT_VD
		,SUM(EST_PROD_IMP.QT_IMP * PDV_VD_DEV_EST_PROD_IMP.VLR_IT)
		,CASE WHEN EST_NF_ENT.NR_NF IS NULL THEN 'POR REQUISICAO'  
		 ELSE 'COM VINCULO COM CUPOM FISCAL' END
		,NCC.CD_CTR_NCC
		,V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		,NCC.VLR_NCC    
		
	FROM           
		DBO.PDV_VD_DEV_EST_PROD_IMP WITH (NOLOCK)   
		
		INNER JOIN DBO.EST_PROD_IMP WITH (NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_IMP.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_IMP.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_PROD_IMP.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_IMP.CD_PROD   
	  
		INNER JOIN DBO.PDV_VD WITH (NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD 
			
		INNER JOIN DBO.PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP AS VINC WITH (NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_IMP = VINC.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = VINC.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_EMP = VINC.CD_EMP   
			
		INNER JOIN DBO.PDV_VD_DEV_NCC AS NCC WITH (NOLOCK) 
			ON VINC.CD_CTR_NCC = NCC.CD_CTR_NCC    
			
		INNER JOIN V_PDV_VD_DEV_NCC_STATUS 
			ON NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
		
		--ADICIONADO  
		LEFT OUTER JOIN DBO.PDV_VD_DEV_NCC_PDV_VD WITH (NOLOCK) 
			ON NCC.CD_EMP = PDV_VD_DEV_NCC_PDV_VD.CD_EMP  
			AND NCC.CD_FILIAL = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL  
			AND NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC  
	   
		LEFT JOIN DBO.PDV_VD_DEV_NCC_LANC_CPL WITH (NOLOCK) 
			ON NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_NCC  
	   
		LEFT JOIN DBO.PDV_VD_DEV_NCC_LANC WITH (NOLOCK) 
			ON PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_LANC_NCC = PDV_VD_DEV_NCC_LANC.CD_CTR_LANC_NCC   
		--FIM  
	   
		LEFT JOIN @NF_ENTRADA AS EST_NF_ENT 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_NF_ENT.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_VD = EST_NF_ENT.CD_VD 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_NF_ENT.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IT = EST_NF_ENT.CD_IT 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_NF_ENT.CD_PROD  
			
		INNER JOIN DBO.PRC_FILIAL WITH (NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PRC_FILIAL.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PRC_FILIAL.CD_FILIAL      
			
		INNER JOIN @RS_FILIAL AS f 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = f.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = f.CD_FILIAL   
		
	WHERE  
		EST_PROD_IMP.CD_EMP = @CD_EMP  
		AND EST_PROD_IMP.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_ENT.CD_NF IS NULL  
		
	GROUP BY   
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP,   
		PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,   
		EST_PROD_IMP.DT_CAD,   
		PDV_VD_DEV_EST_PROD_IMP.CD_IMP,   
		PRC_FILIAL.NM_FANT,  
		PDV_VD.CD_VD,   
		PDV_VD.CD_CX,   
		PDV_VD.NR_ECF,  
		EST_NF_ENT.NR_NF,  
		EST_NF_ENT.CD_NF,  
		PDV_VD.DT_VD,  
		NCC.DT_CAD,  
		PDV_VD_DEV_NCC_LANC.DT_CAD,  
		NCC.CD_CTR_NCC,   
		NCC.STS_NCC,  
		NCC.VLR_NCC,
		V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC  
	  
	--Sem v?nculo com requisi??o  
	INSERT INTO @RS_RET  
	SELECT       
		 NCC.CD_EMP
		,NCC.CD_FILIAL
		,0
		,PRC_FILIAL.NM_FANT
		,0
		,0
		,0
		,NCC.DT_CAD
		,NCC.VLR_NCC AS VLR_NF
		,0 AS NR_ECF
		,NULL AS DT_VD
		,0
		,'NCC SEM VINCULO'
		,NCC.CD_CTR_NCC
		,V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		,NCC.VLR_NCC    
		
	FROM           
		DBO.PDV_VD_DEV_NCC AS NCC WITH (NOLOCK)
		
		INNER JOIN DBO.PRC_FILIAL WITH (NOLOCK) 
			ON NCC.CD_EMP = PRC_FILIAL.CD_EMP 
			AND NCC.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
			
		INNER JOIN @RS_FILIAL AS f 
			ON NCC.CD_EMP = f.CD_EMP 
			AND NCC.CD_FILIAL = f.CD_FILIAL 
			
		LEFT OUTER JOIN DBO.PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP WITH (NOLOCK) 
			ON NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC 
			
		INNER JOIN V_PDV_VD_DEV_NCC_STATUS 
			ON NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC  
		--ADICIONADO FARMAGOI-52301 /ELB	
		LEFT OUTER JOIN DBO.EST_NF_ENT_PDV_VD_DEV_NCC WITH (NOLOCK)
			ON EST_NF_ENT_PDV_VD_DEV_NCC.CD_EMP	= NCC.CD_EMP
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_FILIAL = NCC.CD_FILIAL
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_CTR_NCC = NCC.CD_CTR_NCC
		--FIM
	WHERE  
		NCC.CD_EMP = @CD_EMP  
		AND NCC.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		AND NCC.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC IS NULL
		--ADICIONADO FARMAGOI-52301 /ELB		
	    AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_NF IS NULL
		--FIM
		
	--Com Vinculo com nota fiscal  
	INSERT INTO @RS_RET  
	SELECT       
		 PDV_VD_DEV_EST_PROD_IMP.CD_EMP
		,PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL
		,EST_NF_ENT.CD_NF
		,PRC_FILIAL.NM_FANT
		,PDV_VD.CD_VD
		,PDV_VD.CD_CX
		,EST_NF_ENT.NR_NF
		,EST_PROD_IMP.DT_CAD
		,SUM(EST_PROD_IMP.QT_IMP * PDV_VD_DEV_EST_PROD_IMP.VLR_IT) AS VLR_NF
		,PDV_VD.NR_ECF
		,PDV_VD.DT_VD
		,SUM(EST_PROD_IMP.QT_IMP * PDV_VD_DEV_EST_PROD_IMP.VLR_IT)
		,CASE WHEN EST_NF_ENT.NR_NF IS NULL THEN  'POR REQUISICAO'  
		 ELSE  'COM VINCULO COM CUPOM FISCAL' END
		,NCC.CD_CTR_NCC
		,V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		,NCC.VLR_NCC     
		
	FROM           
		DBO.PDV_VD_DEV_EST_PROD_IMP WITH(NOLOCK) 
		
		INNER JOIN DBO.EST_PROD_IMP WITH(NOLOCK)
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_IMP.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_IMP.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_PROD_IMP.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_IMP.CD_PROD   
	  
		INNER JOIN DBO.PDV_VD WITH(NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD  
	  
		INNER JOIN @NF_ENTRADA EST_NF_ENT 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_NF_ENT.CD_EMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_VD = EST_NF_ENT.CD_VD 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_NF_ENT.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_IT = EST_NF_ENT.CD_IT 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_NF_ENT.CD_PROD  
	   
		INNER JOIN DBO.PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP AS VINC WITH(NOLOCK)
			ON PDV_VD_DEV_EST_PROD_IMP.CD_IMP = VINC.CD_IMP 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = VINC.CD_FILIAL 
			AND PDV_VD_DEV_EST_PROD_IMP.CD_EMP = VINC.CD_EMP 
			
		INNER JOIN DBO.PDV_VD_DEV_NCC AS NCC WITH(NOLOCK) 
			ON VINC.CD_CTR_NCC = NCC.CD_CTR_NCC 
			
		INNER JOIN V_PDV_VD_DEV_NCC_STATUS 
			ON NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC     
			
		--ADICIONADO  
		LEFT OUTER JOIN DBO.PDV_VD_DEV_NCC_PDV_VD WITH(NOLOCK) 
			ON NCC.CD_EMP = PDV_VD_DEV_NCC_PDV_VD.CD_EMP  
			AND NCC.CD_FILIAL = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL  
			AND NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC  
	   
		LEFT JOIN DBO.PDV_VD_DEV_NCC_LANC_CPL WITH(NOLOCK) 
			ON NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_NCC  
	   
		LEFT JOIN DBO.PDV_VD_DEV_NCC_LANC WITH(NOLOCK) 
			ON PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_LANC_NCC = PDV_VD_DEV_NCC_LANC.CD_CTR_LANC_NCC   
		--FIM   
	  
		INNER JOIN DBO.PRC_FILIAL WITH(NOLOCK) 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PRC_FILIAL.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PRC_FILIAL.CD_FILIAL    
			
		INNER JOIN  @RS_FILIAL AS f 
			ON PDV_VD_DEV_EST_PROD_IMP.CD_EMP = f.CD_EMP 
			AND	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = f.CD_FILIAL   
		
	WHERE  
		EST_PROD_IMP.CD_EMP = @CD_EMP  
		AND EST_PROD_IMP.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		
	GROUP BY   
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP,   
		PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,   
		EST_PROD_IMP.DT_CAD,   
		PRC_FILIAL.NM_FANT,  
		PDV_VD.CD_VD,   
		PDV_VD.CD_CX,   
		PDV_VD.NR_ECF,  
		EST_NF_ENT.NR_NF,  
		EST_NF_ENT.CD_NF,  
		PDV_VD.DT_VD,  
		NCC.DT_CAD,  
		PDV_VD_DEV_NCC_LANC.DT_CAD,  
		NCC.CD_CTR_NCC,   
		NCC.STS_NCC,  
		NCC.VLR_NCC,
		V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC 
	   
	  
	UPDATE   
	 @RS_RET   
	SET  
		TP_DEVO ='CONTROLE DE CONVENIO'  
	FROM  
		@RS_RET RS_RET INNER JOIN PDV_VD_CANCEL_CONV ON  
		PDV_VD_CANCEL_CONV.CD_EMP = RS_RET.CD_EMP AND  
		PDV_VD_CANCEL_CONV.CD_FILIAL = RS_RET.CD_FILIAL AND  
		PDV_VD_CANCEL_CONV.CD_VD = RS_RET.CD_VD  
	  
	UPDATE   
		@RS_RET   
	SET  
		TP_DEVO ='CONTROLE DE TEF'  
	FROM  
		@RS_RET RS_RET INNER JOIN  PDV_VD_CANCEL_TEF ON  
		PDV_VD_CANCEL_TEF.CD_EMP = RS_RET.CD_EMP AND  
		PDV_VD_CANCEL_TEF.CD_FILIAL = RS_RET.CD_FILIAL AND  
		PDV_VD_CANCEL_TEF.CD_VD = RS_RET.CD_VD  
	  
	UPDATE   
		@RS_RET   
	SET  
		TP_DEVO ='CONTROLE DE DUPLICATA'  
	FROM  
		@RS_RET RS_RET INNER JOIN PDV_VD_CANCEL_DUP ON  
		PDV_VD_CANCEL_DUP.CD_EMP = RS_RET.CD_EMP AND  
		PDV_VD_CANCEL_DUP.CD_FILIAL = RS_RET.CD_FILIAL AND  
		PDV_VD_CANCEL_DUP.CD_VD = RS_RET.CD_VD  
	  
	INSERT INTO @RS_RET  
	SELECT       
		 EST_NF_ENT.CD_EMP
		,EST_NF_ENT.CD_FILIAL
		,EST_NF_ENT.CD_NF
		,PRC_FILIAL.NM_FANT
		,0
		,0
		,EST_NF_ENT.NR_NF
		,EST_NF_ENT.DT_EMI_NF
		,EST_NF_ENT.VLR_NF
		,0
		,EST_NF_ENT.DT_EMI_NF
		,EST_NF_ENT.VLR_NF
		--ALTERADO FARMAGOI-52301 /ELB
		,CASE WHEN ISNULL(EST_NF_ENT_PDV_VD_DEV_NCC.CD_CTR_NCC,0) = 0 THEN 'SEM VINCULO COM CUPOM FISCAL' 
			  WHEN EST_NF_ENT_PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC.CD_CTR_NCC THEN 'VINCULO COM NF' END
		,ISNULL(PDV_VD_DEV_NCC.CD_CTR_NCC,0) AS CD_CTR_NCC
		,V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		,PDV_VD_DEV_NCC.VLR_NCC
		--FIM
	FROM           
		DBO.EST_NF_ENT WITH (NOLOCK)
		
		INNER JOIN DBO.PRC_FILIAL WITH (NOLOCK)
			ON EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP 
			AND EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
		
		INNER JOIN DBO.GLB_MOV_OP WITH (NOLOCK)
			ON EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP  
		
		LEFT OUTER JOIN @RS_RET RS_RET
			ON EST_NF_ENT.CD_EMP = RS_RET.CD_EMP
			AND EST_NF_ENT.CD_FILIAL = RS_RET.CD_FILIAL 
			AND EST_NF_ENT.CD_NF = RS_RET.CD_NF  
		
		LEFT OUTER JOIN DBO.PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT WITH (NOLOCK)
			ON EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP
			AND EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL 
			AND EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF
		
		--ADICIONADO FARMAGOI-52301 /ELB
		LEFT OUTER JOIN DBO.EST_NF_ENT_PDV_VD_DEV_NCC WITH (NOLOCK)
			ON EST_NF_ENT_PDV_VD_DEV_NCC.CD_EMP	= EST_NF_ENT.CD_EMP
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_FILIAL = EST_NF_ENT.CD_FILIAL
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_NF = EST_NF_ENT.CD_NF
		
		LEFT OUTER JOIN DBO.PDV_VD_DEV_NCC WITH(NOLOCK)
			ON EST_NF_ENT_PDV_VD_DEV_NCC.CD_EMP	= PDV_VD_DEV_NCC.CD_EMP
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_FILIAL = PDV_VD_DEV_NCC.CD_FILIAL
			AND EST_NF_ENT_PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC.CD_CTR_NCC
		
		LEFT OUTER JOIN V_PDV_VD_DEV_NCC_STATUS 
			ON PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC
		--FIM
		
	WHERE  
		EST_NF_ENT.STS_NF = 0  
		AND EST_NF_ENT.CD_EMP = @CD_EMP  
		AND EST_NF_ENT.CD_FILIAL IN (SELECT CD_FILIAL FROM @RS_FILIAL)  
		AND EST_NF_ENT.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND GLB_MOV_OP.TP_MOV_OP_TRANSA = 0  
		AND GLB_MOV_OP.TP_MOV = 11    
		AND GLB_MOV_OP.ATU_EST  = 1  
		AND GLB_MOV_OP.DEST_ORIG = 0  
		AND RS_RET.CD_NF IS NULL  
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF IS NULL  
	  
	INSERT @RS_RETORNO SELECT  * FROM @RS_RET ORDER BY NR_NF  
	  
	RETURN       
END  



