/****** Object:  View [dbo].[V_EST_NF_ENT_CPL]    Script Date: 03/26/2014 19:09:11 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_CPL]'))
DROP VIEW [dbo].[V_EST_NF_ENT_CPL]
GO
GO
GO
--vERSAO 2981
CREATE VIEW [dbo].[V_EST_NF_ENT_CPL]
AS
SELECT     
	dbo.EST_NF_ENT.CD_EMP, 
	dbo.EST_NF_ENT.CD_FILIAL, 
	dbo.EST_NF_ENT.CD_NF, 
	dbo.EST_NF_ENT.CD_NF_SERIE, 
	ISNULL(dbo.EST_NF_SERIE.ESPC, '')  + ' - ' + ISNULL(dbo.EST_NF_SERIE.SERIE,'') SERIE, 
	dbo.EST_NF_ENT.NR_NF, 
    dbo.EST_NF_ENT.CD_FORN AS CD_ORIG, 
	V_GLB_PESSOA.RAZAO_SOCIAL_PESSOA AS NM_ORIG, 
	V_GLB_PESSOA.NOME_FANTASIA_PESSOA AS NM_ORIG_FANT, 
	V_GLB_PESSOA.CGC_CPF,     
	V_GLB_PESSOA.INSC_EST,     
	V_GLB_PESSOA.TP_SUBS_TRIB,     
	V_GLB_PESSOA.SUBS_TRIB AS SUBS_TRIB_FORN,     
	V_GLB_PESSOA.ENDERECO_PESSOA AS ENDE,     

	V_GLB_PESSOA.NUMERO_ENDERECO_PESSOA AS NUMERO,                           
	V_GLB_PESSOA.BAIRRO_ENDERECO_PESSOA AS BAIRRO,     
	V_GLB_PESSOA.CEP_ENDERECO_PESSOA AS CEP,     
	V_GLB_PESSOA.TELEFONE_PESSOA AS TEL,     
	V_GLB_PESSOA.CD_CID,
	V_GLB_PESSOA.COD_IBGE_MUNICIPIO AS COD_IBGE, 
	V_GLB_PESSOA.DS_CID,    
	V_GLB_PESSOA.FLAG_ICMS_SUBST_PAGO_ANTERIOR,                           
	V_GLB_PESSOA.UF, 
    V_GLB_PESSOA.FLAG_VALIDA_NFE, 
	V_GLB_PESSOA.DS_TIPO_PESSOA AS TIPO_FORNECEDOR, 
	V_GLB_PESSOA.FLAG_SIMPLES_NACIONAL AS FLAG_MICRO_EMPRESA,
	V_GLB_PESSOA.CD_TIPO_PESSOA AS CD_TP_FORN,     
	V_GLB_PESSOA.QTDE_CASAS_DECIMAIS_NOTA,     
	EXPORT_NF_ESCRITA, 
    dbo.EST_NF_ENT.DT_EMI_NF, dbo.EST_NF_ENT.HR_EMI_NF, dbo.EST_NF_ENT.CD_NT_MOV, 
    dbo.EST_NF_ENT.CD_NT_OP, dbo.EST_NF_ENT.VLR_NF, dbo.EST_NF_ENT.VLR_TOT_PROD, dbo.EST_NF_ENT.VLR_BASE_ICMS, 
    dbo.EST_NF_ENT.VLR_ICMS, dbo.EST_NF_ENT.VLR_BASE_ICMS_SUBS, dbo.EST_NF_ENT.VLR_ICMS_SUBS, dbo.EST_NF_ENT.VLR_IPI, 
    dbo.EST_NF_ENT.VLR_FRETE, dbo.EST_NF_ENT.STS_NF, dbo.EST_NF_ENT.CD_USU, dbo.EST_NF_ENT.DT_CAD, dbo.EST_NF_ENT.DT_ULT_ALT, 
    dbo.GLB_MOV_OP.TP_MOV_OP_TRANSA, dbo.GLB_MOV_OP.TP_MOV, dbo.GLB_NT_OP.CD_NT_OP_FC AS CFOP, dbo.GLB_MOV_OP.DS_MOV_OP, 
    dbo.EST_NF_ENT.HIST AS OBS1, dbo.EST_NF_ENT.HIST2 AS OBS2, '' AS OBS3, dbo.EST_NF_ENT.OBS4, dbo.EST_NF_ENT.OBS5, '' AS OBS6, 
    '' AS OBS7, dbo.EST_NF_ENT.DT_ENT, dbo.EST_NF_ENT.VLR_OUTROS, dbo.EST_NF_ENT.VRL_ISENTAS, dbo.GLB_MOV_OP.DEST_ORIG, 
    dbo.EST_NF_ENT.ALIQ_ICMS, 
	DBO.EST_NF_ENT.CD_FORN,	
	ISNULL(FL.CD_EMP,0) CD_FORN_FILIAL,
	NFE.NR_AUTORIZADOR,NFE.NR_PROTOCOLO,NFE.VS_PROTOCOLO,NFE.DT_RECBTO,NFE.DG_VALIDADOR,NFE.TP_AMB,NFE.VS_APLIC,
	NFE.REG_CONTG,NFE_CANCEL.NR_PROTOCOLO AS NR_PROTOCOLO_CANCEL,NFE_CANCEL.JUSTIFICATIVA AS JUSTIFICA_CANCEL,
    ISNULL(DBO.EST_NF_SERIE.MODELO, '')   AS MODELO
    ,ISNULL(EST_NF_ENT.VLR_FECOP_ST,0)VLR_FECOP_ST
    ,ISNULL(EST_NF_ENT.VLR_FECOP_ST_RET,0)VLR_FECOP_RET
    , ISNULL(EST_NF_ENT.UF_DESTINATARIO, '') UF_DESTINATARIO              
FROM        
	dbo.EST_NF_ENT 

	INNER JOIN dbo.GLB_MOV_OP ON 
	dbo.EST_NF_ENT.CD_NT_MOV = dbo.GLB_MOV_OP.CD_MOV_OP 

	INNER JOIN dbo.EST_NF_SERIE ON 
	dbo.EST_NF_ENT.CD_EMP = dbo.EST_NF_SERIE.CD_EMP AND 
	dbo.EST_NF_ENT.CD_NF_SERIE = dbo.EST_NF_SERIE.CD_NF_SERIE 

	INNER JOIN dbo.GLB_NT_OP ON 
	dbo.EST_NF_ENT.CD_NT_OP = dbo.GLB_NT_OP.CD_NT_OP
	
	CROSS APPLY UFD_GLB_PESSOA (  EST_NF_ENT.CD_FORN  ,GLB_MOV_OP.DEST_ORIG) AS V_GLB_PESSOA

	LEFT OUTER JOIN PRC_FILIAL_PG_FORN FL ON
	dbo.EST_NF_ENT.CD_EMP  = FL.CD_EMP AND
	dbo.EST_NF_ENT.CD_FORN = FL.CD_FORN
	
	LEFT OUTER JOIN 	dbo.EST_NF_ENT_NFE NFE ON	
	dbo.EST_NF_ENT.CD_EMP = NFE.CD_EMP AND 
	dbo.EST_NF_ENT.CD_FILIAL = NFE.CD_FILIAL AND 
	dbo.EST_NF_ENT.CD_NF = NFE.CD_NF

	LEFT OUTER JOIN 	dbo.EST_NF_ENT_NFE_CANCEL NFE_CANCEL ON
	dbo.EST_NF_ENT.CD_EMP = NFE_CANCEL.CD_EMP AND 
	dbo.EST_NF_ENT.CD_FILIAL = NFE_CANCEL.CD_FILIAL AND 
	dbo.EST_NF_ENT.CD_NF = NFE_CANCEL.CD_NF



