IF OBJECT_ID('V_EST_NF_PEND_VALIDACAO') IS NOT NULL
BEGIN
	DROP VIEW V_EST_NF_PEND_VALIDACAO
END
GO

CREATE VIEW [dbo].[V_EST_NF_PEND_VALIDACAO] AS
SELECT CD_EMP               ,
       CD_FILIAL            ,
       CD_NF                ,
       NM_FANT              ,
       DT_EMI_NF            ,
       VLR_NF               ,
       NF_NF                ,
       SERIE                ,
       NR_PROTOCOLO         ,
       CD_CTR_CARTA_CORRECAO,
	   CD_INU               ,
       TP_ENT_SAI           ,
       TIPO_TRANSACAO
FROM   (
SELECT 
	TOP 20 PRC_FILIAL.NM_FANT,
	ENT.NR_NF AS NF_NF,
	EST_NF_SERIE.SERIE,
	ENT.DT_EMI_NF,
	ENT.VLR_NF,
	ENT.CD_NF,
	ENT.CD_FILIAL,
	ENT.CD_EMP,
	1 AS TP_ENT_SAI,
	'' AS NR_PROTOCOLO,
	0 AS CD_CTR_CARTA_CORRECAO,
	0 AS CD_INU,
	'ENTRADA' AS TIPO_TRANSACAO
FROM             
			EST_NF_ENT ENT INNER JOIN PRC_FILIAL ON
			ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
			ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
			INNER JOIN EST_NF_ENT_NFE_PEND lote_ent ON               
			lote_ent.CD_EMP   = ENT.CD_EMP AND              
			lote_ent.CD_FILIAL = ENT.CD_FILIAL AND              
			lote_ent.CD_NF     = ENT.CD_NF
			INNER JOIN EST_NF_SERIE ON
			ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
       WHERE            
			lote_ent.PEND_SOLUC = 0
       
       UNION ALL
        SELECT 
			TOP 20 PRC_FILIAL.NM_FANT,
            SAI.NF_NF,
            EST_NF_SERIE.SERIE,
            SAI.DT_EMI_NF,
            SAI.VLR_NF,
            SAI.CD_NF,
            SAI.CD_FILIAL,
            SAI.CD_EMP,
            0 AS TP_ENT_SAI,
            '' AS NR_PROTOCOLO,
            0 AS CD_CTR_CARTA_CORRECAO,
			0 AS CD_INU,
            'SAIDA' AS TIPO_TRANSACAO
       FROM            
			EST_NF_SAI SAI INNER JOIN PRC_FILIAL ON
			SAI.CD_EMP = PRC_FILIAL.CD_EMP AND
			SAI.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
			INNER JOIN  EST_NF_SAI_NFE_PEND lote_sai ON              
			lote_sai.CD_EMP    = SAI.CD_EMP AND             
			lote_sai.CD_FILIAL = SAI.CD_FILIAL AND             
			lote_sai.CD_NF     = SAI.CD_NF
			INNER JOIN EST_NF_SERIE ON
			SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
       WHERE           
			lote_sai.PEND_SOLUC = 0
       
       UNION ALL
		SELECT 
			TOP 10 PRC_FILIAL.NM_FANT,
			ENT.NR_NF AS NF_NF,
			EST_NF_SERIE.SERIE,
			ENT.DT_EMI_NF,
			ENT.VLR_NF,
			ENT.Cd_Nf,
			ENT.CD_FILIAL,
			ENT.CD_EMP,
			1 AS TP_ENT_SAI,
			P.NR_PROTOCOLO,
			0 AS CD_CTR_CARTA_CORRECAO  ,
			0 AS CD_INU,
			'CANCELAMENTO' AS TIPO_TRANSACAO
		FROM  
			EST_NF_ENT ENT INNER JOIN PRC_FILIAL ON
			ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
			ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
			INNER JOIN EST_NF_ENT_NFE_CANCEL_PEND Cancel_ent ON     
			Cancel_ent.CD_EMP    = ENT.CD_EMP AND    
			Cancel_ent.CD_FILIAL = ENT.CD_FILIAL AND    
			Cancel_ent.CD_NF     = ENT.CD_NF
			INNER JOIN EST_NF_SERIE ON
			ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE	   
			LEFT OUTER JOIN EST_NF_ENT_NFE p ON     
			Cancel_ent.CD_EMP    = p.CD_EMP AND    
			Cancel_ent.CD_FILIAL = p.CD_FILIAL AND    
			Cancel_ent.CD_NF    = p.CD_NF
		WHERE  
			Cancel_ent.PEND_SOLUC       = 0
       
       UNION ALL
       
       SELECT PRC_FILIAL.NM_FANT               ,
              SAI.NF_NF                 ,
              EST_NF_SERIE.SERIE                     ,
              SAI.DT_EMI_NF             ,
              SAI.VLR_NF                ,
              SAI.Cd_Nf                 ,
              SAI.CD_FILIAL             ,
              SAI.CD_EMP                ,
              0 AS TP_ENT_SAI           ,
              p.NR_PROTOCOLO          ,
              0 AS CD_CTR_CARTA_CORRECAO,
			  0 AS CD_INU,
              'CANCELAMENTO' AS TIPO_TRANSACAO
       FROM   
			EST_NF_SAI SAI INNER JOIN PRC_FILIAL ON
			SAI.CD_EMP = PRC_FILIAL.CD_EMP AND
			SAI.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
			INNER JOIN EST_NF_SAI_NFE_CANCEL_PEND Cancel_sai ON     
			Cancel_sai.CD_EMP    = SAI.CD_EMP AND    
			Cancel_sai.CD_FILIAL = SAI.CD_FILIAL AND    
			Cancel_sai.CD_NF    = SAI.CD_NF
			LEFT OUTER JOIN EST_NF_SAI_NFE p ON     
			Cancel_sai.CD_EMP    = p.CD_EMP AND    
			Cancel_sai.CD_FILIAL = p.CD_FILIAL AND    
			Cancel_sai.CD_NF     = p.CD_NF
			INNER JOIN EST_NF_SERIE ON
			SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
       WHERE  
			Cancel_sai.PEND_SOLUC       = 0
	UNION ALL
	SELECT 
		TOP 20 PRC_FILIAL.NM_FANT,
		SAI.NF_NF,
		EST_NF_SERIE.Serie,
		SAI.DT_EMI_NF,
		SAI.VLR_NF,
		SAI.Cd_Nf,
		SAI.CD_FILIAL,
		SAI.Cd_Emp,
		0 AS TP_ENT_SAI,
		NFE.NR_AUTORIZADOR AS NR_PROTOCOLO,
		CC.CD_CTR_CARTA_CORRECAO,
		0 AS CD_INU,
		'CARTA CORRECAO' AS TIPO_TRANSACAO
	FROM      
		EST_NFE_CARTA_CORRECAO_PEND CC INNER JOIN 
		EST_NFE_CARTA_CORRECAO_EST_NF_SAI CC_SAI ON        
		CC_SAI.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO 
		INNER JOIN EST_NF_SAI SAI  ON        
		CC_SAI.CD_EMP    = SAI.CD_EMP AND       
		CC_SAI.CD_FILIAL = SAI.CD_FILIAL AND       
		CC_SAI.CD_NF     = SAI.CD_NF
		INNER JOIN PRC_FILIAL ON
		SAI.CD_EMP = PRC_FILIAL.CD_EMP AND
		SAI.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
		INNER JOIN EST_NF_SAI_NFE NFE ON        
		CC_SAI.CD_EMP    = NFE.CD_EMP AND       
		CC_SAI.CD_FILIAL = NFE.CD_FILIAL AND       
		CC_SAI.CD_NF     = NFE.CD_NF
		INNER JOIN EST_NF_SERIE ON
		SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
	WHERE     
		CC.PEND_SOLUC= 0
	UNION ALL
	SELECT  TOP 20 PRC_FILIAL.NM_FANT,
		ENT.NR_NF AS NF_NF,
		EST_NF_SERIE.SERIE             ,
		ENT.DT_EMI_NF     ,
		ENT.VLR_NF       ,
		ENT.Cd_Nf         ,
		ENT.CD_FILIAL     ,
		ENT.Cd_Emp        ,
		1                  AS TP_ENT_SAI   ,
		NFE.NR_AUTORIZADOR AS NR_PROTOCOLO ,
		CC.CD_CTR_CARTA_CORRECAO           ,
		0 AS CD_INU,
		'CARTA CORRECAO' AS TIPO_TRANSACAO
	FROM            
		EST_NFE_CARTA_CORRECAO_PEND CC INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT CC_ENT ON              
		CC.CD_CTR_CARTA_CORRECAO = CC_ENT.CD_CTR_CARTA_CORRECAO
		INNER JOIN EST_NF_ENT_NFE NFE ON              
		CC_ENT.CD_EMP    = NFE.CD_EMP AND 
		CC_ENT.CD_FILIAL = NFE.CD_FILIAL AND             
		CC_ENT.CD_NF     = NFE.CD_NF
		INNER JOIN EST_NF_ENT ENT ON
		CC_ENT.CD_EMP    = ENT.CD_EMP AND 
		CC_ENT.CD_FILIAL = ENT.CD_FILIAL AND             
		CC_ENT.CD_NF     = ENT.CD_NF
		INNER JOIN PRC_FILIAL ON
		ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
		ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL 			
		INNER JOIN EST_NF_SERIE ON
		ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE	
	WHERE           
		CC.PEND_SOLUC = 0
	UNION ALL
	SELECT  TOP 20 PRC_FILIAL.NM_FANT,
			ENT.NR_NF  AS NF_NF,
			CAST(EST_NF_SERIE.SERIE AS VARCHAR(10)) AS SERIE,
			ENT.DT_EMI_NF     ,
			ENT.VLR_NF       ,
			ENT.Cd_Nf         ,
			ENT.CD_FILIAL     ,
			ENT.Cd_Emp        ,
			1 AS TP_ENT_SAI   ,
			ISNULL(NFE.NR_AUTORIZADOR,'') AS NR_PROTOCOLO ,
			0 AS CD_CTR_CARTA_CORRECAO           ,
			PEND.CD_INU,
			'INUTILIZACAO' AS TIPO_TRANSACAO
		FROM            
			EST_NF_NFE_INU_PEND PEND INNER JOIN EST_NF_NFE_INU INU ON              
			PEND.CD_INU = INU.CD_INU
			INNER JOIN EST_NF_ENT_EST_NF_NFE_INU INU_ENT ON              
			INU.CD_INU = INU_ENT.CD_INU
			LEFT OUTER JOIN EST_NF_ENT ENT ON
			ENT.CD_EMP    = INU_ENT.CD_EMP AND             
			ENT.CD_FILIAL = INU_ENT.CD_FILIAL AND             
			ENT.CD_NF     = INU_ENT.CD_NF
			LEFT JOIN V_EST_NF_ENT_NFE NFE ON
			ENT.CD_EMP = NFE.CD_EMP AND
			ENT.CD_FILIAL = NFE.CD_FILIAL AND
			ENT.CD_NF = NFE.CD_NF 
			INNER JOIN PRC_FILIAL ON
			ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
			ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
			INNER JOIN EST_NF_SERIE ON
			ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE	
		WHERE           
			PEND.PEND_SOLUC                    = 0
	   UNION ALL
SELECT 
	TOP 20 PRC_FILIAL.NM_FANT,
	SAI.NF_NF         ,
	CAST(EST_NF_SERIE.SERIE AS VARCHAR(10)) AS SERIE,
	SAI.DT_EMI_NF     ,
	SAI.VLR_NF       ,
	SAI.Cd_Nf         ,
	SAI.CD_FILIAL     ,
	SAI.Cd_Emp        ,
	0  AS TP_ENT_SAI   ,
	NFE.NR_AUTORIZADOR AS NR_PROTOCOLO ,
	0 AS CD_CTR_CARTA_CORRECAO,
	PEND.CD_INU,
	'INUTILIZACAO' AS TIPO_TRANSACAO
FROM            
	EST_NF_NFE_INU_PEND PEND INNER JOIN EST_NF_NFE_INU INU ON              
	PEND.CD_INU = INU.CD_INU
	INNER JOIN EST_NF_SAI_EST_NF_NFE_INU INU_SAI ON              
	INU.CD_INU = INU_SAI.CD_INU
	INNER JOIN EST_NF_SAI SAI ON
	SAI.CD_EMP    = INU_SAI.CD_EMP AND             
	SAI.CD_FILIAL = INU_SAI.CD_FILIAL AND             
	SAI.CD_NF     = INU_SAI.CD_NF
	INNER JOIN PRC_FILIAL ON
	SAI.CD_EMP = PRC_FILIAL.CD_EMP AND
	SAI.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
	INNER JOIN EST_NF_SERIE ON
	SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
	LEFT JOIN EST_NF_SAI_NFE NFE ON        
	SAI.CD_EMP    = NFE.CD_EMP AND       
	SAI.CD_FILIAL = NFE.CD_FILIAL AND       
	SAI.CD_NF     = NFE.CD_NF
WHERE           
	PEND.PEND_SOLUC = 0
		UNION ALL
	SELECT  TOP 20 PRC_FILIAL.NM_FANT,
		ENT.NR_NF AS NF_NF,
		EST_NF_SERIE.SERIE             ,
		ENT.DT_EMI_NF     ,
		ENT.VLR_NF       ,
		ENT.Cd_Nf         ,
		ENT.CD_FILIAL     ,
		ENT.Cd_Emp        ,
		1                  AS TP_ENT_SAI   ,
		NFE.NR_AUTORIZADOR AS NR_PROTOCOLO ,
		CC.CD_CTR_INF_EFET_PGTO_INTEG           ,
		0 AS CD_INU,
		'INFORMACAO DE EFETIVO PAGAMENTO INTEGRAL' AS TIPO_TRANSACAO
	FROM            
		EST_NFE_INF_EFET_PGTO_INTEG_PEND CC INNER JOIN EST_NFE_INF_EFET_PGTO_INTEG_EST_NF_ENT CC_ENT ON              
		CC.CD_CTR_INF_EFET_PGTO_INTEG = CC_ENT.CD_CTR_INF_EFET_PGTO_INTEG
		INNER JOIN EST_NF_ENT_NFE NFE ON              
		CC_ENT.CD_EMP    = NFE.CD_EMP AND 
		CC_ENT.CD_FILIAL = NFE.CD_FILIAL AND             
		CC_ENT.CD_NF     = NFE.CD_NF
		INNER JOIN EST_NF_ENT ENT ON
		CC_ENT.CD_EMP    = ENT.CD_EMP AND 
		CC_ENT.CD_FILIAL = ENT.CD_FILIAL AND             
		CC_ENT.CD_NF     = ENT.CD_NF
		INNER JOIN PRC_FILIAL ON
		ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
		ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL 			
		INNER JOIN EST_NF_SERIE ON
		ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE	
	WHERE           
		CC.PEND_SOLUC = 0) AS CONSULTA_NOTAS_PENDENTES

