  
/****** Object:  View [dbo].[V_EST_NF_ENT_NFE_SPED]    Script Date: 01/22/2013 09:45:54 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_NFE_SPED]'))
DROP VIEW [dbo].[V_EST_NF_ENT_NFE_SPED]
GO
 

CREATE  VIEW [dbo].[V_EST_NF_ENT_NFE_SPED] AS
 
SELECT 
       EST_NF_ENT.CD_EMP,
       EST_NF_ENT.CD_FILIAL ,
       EST_NF_ENT.CD_NF,
       EST_NF_ENT.NR_NF,
       EST_NF_ENT.DT_EMI_NF AS DT_EMI_NF, 
       EST_NF_ENT_DANFE_PROVISORIO.NR_AUTORIZADOR  
FROM 
        EST_NF_ENT INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
        EST_NF_ENT.CD_EMP  = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP AND
        EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL AND
        EST_NF_ENT.CD_NF = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF
        INNER JOIN EST_NF_ENT_DANFE_PROVISORIO ON
        EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO.CD_EMP
        AND EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR  
        AND EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL= EST_NF_ENT_DANFE_PROVISORIO.CD_FILIAL 
        
        AND NOT EXISTS (SELECT CD_NF FROM EST_NF_ENT_NFE WHERE
        EST_NF_ENT_NFE.CD_EMP =  EST_NF_ENT.CD_EMP 
        AND EST_NF_ENT_NFE.CD_FILIAL =  EST_NF_ENT.CD_FILIAL
        AND EST_NF_ENT_NFE.CD_nf =  EST_NF_ENT.CD_nf) 
        
        AND NOT EXISTS (SELECT CD_NF FROM EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID WHERE
        EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP =  EST_NF_ENT.CD_EMP 
        AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL =  EST_NF_ENT.CD_FILIAL
        AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_nf =  EST_NF_ENT.CD_nf)
        
        
UNION


SELECT 
    EST_NF_ENT.CD_EMP,
    EST_NF_ENT.CD_FILIAL ,
    EST_NF_ENT.CD_NF,
    EST_NF_ENT.NR_NF,
    EST_NF_ENT.DT_EMI_NF AS DT_EMI_NF, 
    EST_NF_ENT_NFE.NR_AUTORIZADOR

FROM 
    EST_NF_ENT 
    INNER JOIN EST_NF_ENT_NFE ON
    EST_NF_ENT.CD_EMP = EST_NF_ENT_NFE.CD_EMP AND
    EST_NF_ENT.CD_FILIAL = EST_NF_ENT_NFE.CD_FILIAL AND
    EST_NF_ENT.CD_NF  = EST_NF_ENT_NFE.CD_NF 

UNION


SELECT 
    EST_NF_ENT.CD_EMP,
    EST_NF_ENT.CD_FILIAL ,
    EST_NF_ENT.CD_NF,
    EST_NF_ENT.NR_NF,
    EST_NF_ENT.DT_EMI_NF AS DT_EMI_NF,  
    EST_NF_SAI_NFE.NR_AUTORIZADOR
FROM   
	EST_NF_ENT 
	INNER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON
    EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND
    EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST AND
    EST_NF_ENT.CD_NF  = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT
    
    INNER JOIN EST_NF_SAI_NFE ON 
    EST_NF_SAI_NFE.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND
    EST_NF_SAI_NFE.CD_FILIAL  = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG AND 
    EST_NF_SAI_NFE.CD_NF  = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI 
    
	AND NOT EXISTS (SELECT CD_NF FROM EST_NF_ENT_NFE WHERE
	EST_NF_ENT_NFE.CD_EMP =  EST_NF_ENT.CD_EMP 
	AND EST_NF_ENT_NFE.CD_FILIAL =  EST_NF_ENT.CD_FILIAL
	AND EST_NF_ENT_NFE.CD_nf =  EST_NF_ENT.CD_nf)
	
	AND NOT EXISTS (SELECT CD_NF FROM EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID WHERE
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP =  EST_NF_ENT.CD_EMP 
	AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL =  EST_NF_ENT.CD_FILIAL
	AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_nf =  EST_NF_ENT.CD_nf) 
	
	 
UNION
 
SELECT 
    EST_NF_ENT.CD_EMP,
    EST_NF_ENT.CD_FILIAL ,
    EST_NF_ENT.CD_NF,
    EST_NF_ENT.NR_NF,
    EST_NF_ENT.DT_EMI_NF AS DT_EMI_NF,  
    NR_AUTORIZADOR  

FROM   
	EST_NF_ENT 
	INNER JOIN  EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID on 
	EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP AND 
	EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL AND 
	EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF
	INNER JOIN EST_NF_ENT_NFE_FORN_VALID ON  
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP = EST_NF_ENT_NFE_FORN_VALID.CD_EMP  AND 
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL = EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL AND 
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR = EST_NF_ENT_NFE_FORN_VALID.CD_CTR

	AND NOT EXISTS (SELECT CD_NF FROM EST_NF_ENT_NFE WHERE
					EST_NF_ENT_NFE.CD_EMP =  EST_NF_ENT.CD_EMP 
					AND EST_NF_ENT_NFE.CD_FILIAL =  EST_NF_ENT.CD_FILIAL
					AND EST_NF_ENT_NFE.CD_nf =  EST_NF_ENT.CD_nf) 



GO


