
IF EXISTS (SELECT
						 1
					 FROM
						 sys.views
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_CAB]'))
	DROP VIEW [dbo].[V_EST_NF_ENT_CAB]

GO


CREATE VIEW [dbo].[V_EST_NF_ENT_CAB]
WITH ENCRYPTION
AS

	SELECT
		DBO.EST_NF_ENT.CD_EMP
		,DBO.EST_NF_ENT.CD_FILIAL
		,PRC_FILIAL.NM_FANT
		,DBO.EST_NF_ENT.CD_NF
		,ISNULL(DBO.EST_NF_ENT_PED_CP.CD_PED, 0)     AS CD_PED
		,DBO.EST_NF_ENT.NR_NF
		,DBO.EST_NF_ENT.CD_NF_SERIE
		,ISNULL(DBO.EST_NF_SERIE.SERIE, '')          AS SERIE
		,ISNULL(DBO.EST_NF_SERIE.ESPC, '')           AS ESPC
		,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                        AS CGC_CPF
		,V_GLB_PESSOA.INSC_EST
		,V_GLB_PESSOA.ENDERECO_PESSOA                AS ENDE
		,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.DS_CID
		,V_GLB_PESSOA.UF
		,V_GLB_PESSOA.FLAG_VALIDA_NFE
		,V_GLB_PESSOA.DS_TIPO_PESSOA                 AS TIPO_FORNECEDOR
		,V_GLB_PESSOA.CD_TIPO_PESSOA                 AS CD_TP_FORN
		,V_GLB_PESSOA.FLAG_SIMPLES_NACIONAL          AS FLAG_FORN_MICRO_EMPRESA
		,V_GLB_PESSOA.FLAG_SIMPLES_NACIONAL          AS FLAG_MICRO_EMPRESA
		--CAMPO A SER RETORNADO DA FUNÇÃO UFD_GLB_PESSOA
		,V_GLB_PESSOA.FLAG_NAO_ABATER_ICMS_DESONERADO AS FLAG_NAO_ABATER_ICMS_DESONERADO 
		,DBO.EST_NF_ENT.DT_ENT
		,DBO.EST_NF_ENT.DT_EMI_NF
		,DBO.EST_NF_ENT.HR_EMI_NF
		,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
		,ISNULL(RSIMPOSTO.VLR_ICMS_ITENS, 0)         AS VLR_ICMS_ITENS
		,ISNULL(EST_NF_ENT_DAR.VLR_DAR_RECOLHER, 0)  AS VLR_ICMS_SUBS_ITENS
		,DBO.EST_NF_ENT.VLR_FRETE
		,DBO.EST_NF_ENT.VLR_DESC_CAB
		,DBO.EST_NF_ENT.VLR_DESC
		,DBO.EST_NF_ENT.VLR_PERC_IPI_FRETE
		,DBO.EST_NF_ENT.PERC_IPI_FRETE
		,2                                           AS TP_FRETE
		,DBO.EST_NF_ENT.VLR_IPI
		,DBO.EST_NF_ENT.VLR_OUTROS
		,DBO.EST_NF_ENT.VRL_ISENTAS
		,DBO.EST_NF_ENT.VLR_ENCG_FIN
		,DBO.EST_NF_ENT.PERC_ICMS_ENCG_FIN
		,DBO.EST_NF_ENT.HIST                         AS OBS1
		,DBO.EST_NF_ENT.HIST2                        AS OBS2
		,DBO.EST_NF_ENT.DT_ENTRADA_EST
		,0                                           AS VLR_OUTRAS_DESP
		,''                                          AS OBS3
		,DBO.EST_NF_ENT.OBS4
		,DBO.EST_NF_ENT.OBS5
		,''                                          AS OBS6
		,''                                          AS OBS7
		,''                                          AS NM_COMPRADOR
		,DBO.EST_NF_ENT.INF_DESCONTO
		,DBO.EST_NF_ENT.DT_CAD
		,DBO.EST_NF_ENT.CD_USU
		,DBO.EST_NF_ENT.STS_NF
		,DBO.EST_NF_ENT.DS_TRANSP
		,DBO.EST_NF_ENT.PLACA_VEIC
		,DBO.EST_NF_ENT.PLACA_UF
		,DBO.EST_NF_ENT.ENCG_TRIB
		,DBO.EST_NF_ENT.CGC_CPF_TRANSP
		,DBO.EST_NF_ENT.ENDE_TRANSP
		,DBO.EST_NF_ENT.DS_CID_TRANSP
		,DBO.EST_NF_ENT.UF_TRANSP
		,DBO.EST_NF_ENT.INSC_EST_TRANSP
		,DBO.EST_NF_ENT.QT_VOL
		,DBO.EST_NF_ENT.ESPCIE_VOL
		,DBO.EST_NF_ENT.MC_VOL
		,DBO.EST_NF_ENT.NR_VOL
		,DBO.EST_NF_ENT.PESO_BRT
		,DBO.EST_NF_ENT.PESO_LIQ
		,DBO.GLB_MOV_OP.CD_MOV_OP
		,DBO.GLB_MOV_OP.TP_MOV_OP_TRANSA
		,DBO.GLB_MOV_OP.DS_MOV_OP
		,DBO.GLB_MOV_OP.DS_MOV_OP_USUAL
		,DBO.GLB_MOV_OP.TP_MOV
		,DBO.GLB_MOV_OP.ATU_EST
		,DBO.GLB_MOV_OP.DEST_ORIG
		,EST_NF_ENT.PGTO_VISTA_PRAZO
		,DBO.GLB_MOV_OP.TP_ESTQ
		,DBO.GLB_MOV_OP.ENVIO_OPERADOR_LOG
		,DBO.GLB_MOV_OP.FLAG_RETORNO_OPERADOR_LOG
		,DBO.GLB_MOV_OP.FLAG_ENTRADA_OPERADOR_LOG
		,EST_NF_ENT.NR_SELO_SAIDA
		,TP_LAYOUT_EXP_ENT
		,ISNULL(EST_NF_ENT.NR_PED_FORN, '')          AS NR_PED_FORN
		,EST_NF_ENT.NR_FORMULARIO
		,EXPORT_NF_ESCRITA
		,GLB_MOV_OP.FLAG_NAO_CROSS_DOCKING
		,ISNULL(FF.CD_EMP, 0)                        CD_FORN_FILIAL
		,EST_NF_ENT.CD_FORN
		,ISNULL(VLR_BASE_PIS_COFINS, 0)              AS VLR_BASE_PIS_COFINS
		,DS_MOV                                      AS TP_MOV_DS
		,ISNULL(PG_CRED_EST_NF_ENT.NR_DP, 0)         AS NR_DP_PAG
		,ISNULL(PG_CRED_EST_NF_ENT.ANO, 0)           AS ANO_DP_PAG
		,CASE WHEN PG_CRED_EST_NF_ENT.CD_NF IS NULL THEN 'NAO'
		 ELSE 'SIM'
		 END                                         AS CONTASPAGAR
		,EST_NF_ENT.DT_ULT_ALT
		,GLB_MOV_OP.FLAG_CONTABILIZAR_COMO_DESPESAS
		,EST_NF_ENT.IGNORAR_GERACAO_FINAN
		,GLB_MOV_OP.GERA_DP_PG
		,ISNULL(EST_NF_SERIE.TP_SR_NFE, 0)           AS TP_SR_NFE
		,HR_ENT COLLATE SQL_Latin1_General_CP1_CI_AS AS HR_ENT
		,ISNULL(EST_NF_ENT.NFE_REJEITADA, 0)         AS NFE_REJEITADA
		,EST_NF_ENT.CD_NT_MOV
		,CASE WHEN ISNULL(EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT, 0) > 0 THEN 1
		 ELSE 0
		 END                                         FLAG_NOTA_EMISSAO_PROPRIA
		,EST_NF_ENT.VERSAO_APLICATIVO
		,EST_NF_ENT.APLICATIVO
		,NFE.NR_AUTORIZADOR
		,NFE.NR_PROTOCOLO
		,NFE.VS_PROTOCOLO
		,NFE.DT_RECBTO
		,NFE.DG_VALIDADOR
		,NFE.TP_AMB
		,NFE.VS_APLIC
		,NFE.REG_CONTG
		,ISNULL(EST_NF_ENT.FLAG_NOTA_INTEGRACAO, 0)  FLAG_NOTA_INTEGRACAO
		,ISNULL(EST_NF_ENT.EMAIL_TRANSP, '')         AS EMAIL_TRANSP
		,EST_NF_SERIE.COD_MODELO
		,CASE WHEN GLB_MOV_OP.GERA_ICMS_SUBS = 0 THEN 'SIM'
		 ELSE 'NAO'
		 END                                         AS GERA_MOV_ICMS_SUBS
		,CASE WHEN GLB_MOV_OP.ALQ_ICMS = 0 THEN 'SIM'
		 ELSE 'NAO'
		 END                                         AS GERA_MOV_ICMS
		,ISNULL(EST_NF_ENT.VLR_ICMS_DESONERADO, 0)   AS VLR_ICMS_DESONERADO_CAB
		,ISNULL(GLB_MOV_OP.FLAG_MOV_OP_NFE_CPL, 0)   AS FLAG_MOV_OP_NFE_CPL
		,GLB_MOV_OP.TP_PRECO
		,GLB_MOV_OP.CALC_CUSTO_MEDIO
		,GLB_MOV_OP.FLAG_CUSTO_ZERO_ENT
		,ISNULL(EST_NF_ENT.FLAG_REPOSICAO_PBM,0) AS FLAG_REPOSICAO_PBM,
		EST_NF_ENT.DT_HR_CAD,
		V_GLB_PESSOA.AR_MERCANTIL
		,ISNULL(EST_NF_ENT.VLR_FECOP_ST,0)             AS VLR_FECOP_ST
		,ISNULL(EST_NF_ENT.VLR_FECOP_ST_RET,0)         AS VLR_FECOP_RET
		,ISNULL(GLB_MOV_OP.FLAG_N_EXPORTAR_CONTABIL,0) AS FLAG_N_EXPORTAR_CONTABIL
		,ISNULL(EST_NF_ENT.VLR_FECOP,0)VLR_FECOP
		,EST_NF_ENT.UUID
		,ISNULL(EST_NF_ENT.FLAG_VLR_ICMS_DESONERADO_DESCONTO,0) AS FLAG_VLR_ICMS_DESONERADO_DESCONTO
		
	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
		LEFT OUTER JOIN DBO.EST_NF_ENT_PED_CP ON
			DBO.EST_NF_ENT.CD_EMP = DBO.EST_NF_ENT_PED_CP.CD_EMP AND
			DBO.EST_NF_ENT.CD_FILIAL = DBO.EST_NF_ENT_PED_CP.CD_FILIAL AND
			DBO.EST_NF_ENT.CD_NF = DBO.EST_NF_ENT_PED_CP.CD_NF
		LEFT OUTER JOIN PRC_FILIAL_PG_FORN FF ON
			EST_NF_ENT.CD_EMP = FF.CD_EMP AND
			EST_NF_ENT.CD_FORN = FF.CD_FORN
		LEFT JOIN GLB_MOV_OP_TP_MOV ON
			GLB_MOV_OP.TP_MOV = GLB_MOV_OP_TP_MOV.TP_MOV
		OUTER apply UFD_GLB_PESSOA (EST_NF_ENT.CD_FORN, GLB_MOV_OP.DEST_ORIG)V_GLB_PESSOA
		LEFT OUTER JOIN(SELECT
								V_EST_NF_ENT_IT_PIS_COFINS.CD_EMP
								,V_EST_NF_ENT_IT_PIS_COFINS.CD_FILIAL
								,V_EST_NF_ENT_IT_PIS_COFINS.CD_NF
								,SUM(VLR_BASE_PIS_COFINS) AS VLR_BASE_PIS_COFINS
								,SUM(VLR_ICMS_ITENS)      AS VLR_ICMS_ITENS
							FROM
								V_EST_NF_ENT_IT_PIS_COFINS
							GROUP  BY
								V_EST_NF_ENT_IT_PIS_COFINS.CD_EMP
								,V_EST_NF_ENT_IT_PIS_COFINS.CD_FILIAL
								,V_EST_NF_ENT_IT_PIS_COFINS.CD_NF) RSIMPOSTO ON
			EST_NF_ENT.CD_EMP = RSIMPOSTO.CD_EMP AND
			EST_NF_ENT.CD_FILIAL = RSIMPOSTO.CD_FILIAL AND
			EST_NF_ENT.CD_NF = RSIMPOSTO.CD_NF
		OUTER APPLY (SELECT TOP 1 * 
						FROM PG_CRED_EST_NF_ENT 
						WHERE EST_NF_ENT.CD_EMP = PG_CRED_EST_NF_ENT.CD_EMP AND
							EST_NF_ENT.CD_FILIAL = PG_CRED_EST_NF_ENT.CD_FILIAL AND
							EST_NF_ENT.CD_NF = PG_CRED_EST_NF_ENT.CD_NF) PG_CRED_EST_NF_ENT
		INNER JOIN PRC_FILIAL ON
			EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP AND
			EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL
		LEFT OUTER JOIN EST_NF_ENT_DAR ON
			EST_NF_ENT_DAR.CD_EMP = EST_NF_ENT.CD_EMP AND
			EST_NF_ENT_DAR.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
			EST_NF_ENT_DAR.CD_NF = EST_NF_ENT.CD_NF
		LEFT OUTER JOIN EST_NF_ENT_EF_NF_CTR_NF ON
			EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = EST_NF_ENT.CD_EMP AND
			EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
			EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT = EST_NF_ENT.CD_NF
		LEFT JOIN V_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 
