IF EXISTS (
		SELECT *
		FROM sys.VIEWS
		WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_NFE]')
		)
	DROP VIEW [dbo].[V_EST_NF_ENT_NFE]
GO

--select * from V_EST_NF_ENT_NFE where 
CREATE VIEW [dbo].[V_EST_NF_ENT_NFE]
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
	,NFE.NR_AUTORIZADOR
	,NFE.NPROT AS NR_PROTOCOLO
	,'' AS VS_PROTOCOLO
	,'' AS DT_RECBTO
	,'' AS DG_VALIDADOR
	,'' AS TP_AMB
	,'' AS VS_APLIC
	,'' AS REG_CONTG
	,'DANFE PROVISORIO (EST_NF_ENT_DANFE_PROVISORIO)' AS DS_ORIGEM_PROTOCOLO
	,0 AS ORIGEM_PROTOCOLO
FROM EST_NF_ENT
CROSS APPLY (
	SELECT TOP 1 NFE.*
	FROM EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO NFE ON EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR = NFE.CD_CTR
	WHERE 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
	ORDER BY substring(NFE.NR_AUTORIZADOR, 35, 1) ASC
	) NFE
LEFT 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
LEFT 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
WHERE EST_NF_ENT.CD_EMP = 1
	AND EST_NF_ENT_NFE.CD_EMP IS NULL
	AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP IS NULL

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
	,NFE.NR_AUTORIZADOR
	,NFE.NR_PROTOCOLO
	,NFE.VS_PROTOCOLO
	,NFE.DT_RECBTO
	,NFE.DG_VALIDADOR
	,NFE.TP_AMB
	,NFE.VS_APLIC
	,NFE.REG_CONTG
	,'DANFE EMITIDO EMPRESA (EST_NF_ENT_NFE)' AS DS_ORIGEM_PROTOCOLO
	,1 AS ORIGEM_PROTOCOLO
FROM EST_NF_ENT
INNER JOIN EST_NF_ENT_NFE NFE ON EST_NF_ENT.CD_EMP = NFE.CD_EMP
	AND EST_NF_ENT.CD_FILIAL = NFE.CD_FILIAL
	AND EST_NF_ENT.CD_NF = NFE.CD_NF
LEFT OUTER 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
WHERE EST_NF_ENT.CD_EMP = 1
	AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT IS NULL

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
	,NFE.NR_AUTORIZADOR
	,NFE.NR_PROTOCOLO
	,NFE.VS_PROTOCOLO
	,NFE.DT_RECBTO
	,NFE.DG_VALIDADOR
	,NFE.TP_AMB
	,NFE.VS_APLIC
	,NFE.REG_CONTG
	,'DANFE PROVISORIO TRANSFERENCIA (EST_NF_SAI_NFE)' AS DS_ORIGEM_PROTOCOLO
	,2 AS ORIGEM_PROTOCOLO
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 NFE ON EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = NFE.CD_EMP
	AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = NFE.CD_FILIAL
	AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = NFE.CD_NF
WHERE EST_NF_ENT.CD_EMP = 1

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_FORN_VALID.NR_AUTORIZADOR
	,ISNULL(EST_NF_ENT_NFE_FORN_VALID.NR_PROTOCOLO, '') AS NR_PROTOCOLO
	,'' AS VS_PROTOCOLO
	,ISNULL(EST_NF_ENT_NFE_FORN_VALID.DT_VALIDACAO, '') AS DT_RECBTO
	,ISNULL(EST_NF_ENT_NFE_FORN_VALID.DIG_VALIDACAO, '') AS DG_VALIDADOR
	,'' AS TP_AMB
	,'' AS VS_APLIC
	,'' AS REG_CONTG
	,'DANFE FORNECEDOR VALIDE (EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID)' AS DS_ORIGEM_PROTOCOLO
	,3 AS ORIGEM_PROTOCOLO
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
LEFT 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
LEFT OUTER 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
WHERE EST_NF_ENT.CD_EMP = 1
	AND EST_NF_ENT_NFE.CD_EMP IS NULL
	AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT IS NULL

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_DANFE_CTE.NR_AUTORIZADOR
	,EST_NF_ENT_DANFE_CTE.NPROT AS NR_PROTOCOLO
	,'' AS VS_PROTOCOLO
	,'' AS DT_RECBTO
	,'' AS DG_VALIDADOR
	,'' AS TP_AMB
	,'' AS VS_APLIC
	,'' AS REG_CONTG
	,'DANFE CTE (EST_NF_ENT_DANFE_CTE)' AS DS_ORIGEM_PROTOCOLO
	,4 AS ORIGEM_PROTOCOLO
FROM EST_NF_ENT
LEFT JOIN EST_NF_ENT_DANFE_CTE ON EST_NF_ENT.CD_EMP = EST_NF_ENT_DANFE_CTE.CD_EMP
	AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DANFE_CTE.CD_FILIAL
	AND EST_NF_ENT.NR_NF = EST_NF_ENT_DANFE_CTE.NCT
	AND EST_NF_ENT_DANFE_CTE.MOD = 57
	AND EST_NF_ENT.CD_NF_SERIE = (
		SELECT DISTINCT EST_NF_SERIE.CD_NF_SERIE
		FROM EST_NF_SERIE
		INNER JOIN EST_NF_ENT_DANFE_CTE ON EST_NF_SERIE.CD_EMP = EST_NF_ENT_DANFE_CTE.CD_EMP
			AND EST_NF_SERIE.SERIE = EST_NF_ENT_DANFE_CTE.SERIE
			AND EST_NF_SERIE.ESPC = 'CTE'
		WHERE EST_NF_SERIE.CD_EMP = EST_NF_ENT.CD_EMP
			AND EST_NF_SERIE.CD_NF_SERIE = EST_NF_ENT.CD_NF_SERIE
		)
WHERE EST_NF_ENT.CD_EMP = 1
