if object_id('V_NFE_ERROS_IMPORTACAO') is null
begin
execute sp_executesql N'CREATE VIEW V_NFE_ERROS_IMPORTACAO
AS

WITH 
	lst_notas as ( 
		SELECT 
			CAST( 
				CASE 
					WHEN recb.sts_import = 0 THEN ''NAO PROCESSADO'' 
					WHEN recb.sts_import = 1 THEN ''VALIDADO'' 
					WHEN recb.sts_import = 2 THEN ''REJEITADO'' 
					WHEN recb.sts_import = 3 THEN ''ARMAZENADO'' 
					WHEN recb.sts_import = 4 THEN ''REPROCESSADO'' 
					WHEN recb.sts_import = 5 THEN ''IGNORADA'' 
				ELSE 
					''UNKNOW'' 
				END 
			AS character varying(14)) as STATUS 
			, recb.* 
		FROM NFe..est_nf_nfe_recebimento recb 
		WHERE RECB.STS_IMPORT = 2
	) 
	, manifestos as ( 
		SELECT 
			DISTINCT man.CHAVE_NFE 
			, man.TP_EVENTO 
			, man.STS_MANIFESTO 
		FROM NFe..EST_NF_NFE_MANIFESTO man 
		WHERE 
			man.CHAVE_NFE in (SELECT nt.CHAVE_NFE FROM lst_notas nt) 
	) 
SELECT 
	nt.* 
	, COALESCE(man.STS_MANIFESTO, -1) AS STS_MANIFESTO 
	, COALESCE(man.TP_EVENTO, -1) AS TP_EVENTO 
	, CAST( 
		CASE 
			WHEN man.STS_MANIFESTO = 0 THEN 
				CASE 
					WHEN man.TP_EVENTO = 0 THEN ''PENDENTE - CONFIRMACAO DA OPERACAO''
					WHEN man.TP_EVENTO = 1 THEN ''PENDENTE - CONFIRMACAO DA CIENCIA DA OPERACAO'' 
					WHEN man.TP_EVENTO = 2 THEN ''PENDENTE - CONFIRMACAO DO DESCONHECIMENTO DA OPERACAO'' 
					WHEN man.TP_EVENTO = 3 THEN ''PENDENTE - CONFIRMACAO DO NAO REGISTRO DA OPERACAO'' 
				ELSE 
					''MANIFESTO DESCONHECIDO PENDENTE'' 
				END 
			WHEN man.STS_MANIFESTO = 1 THEN 
				CASE 
					WHEN man.TP_EVENTO = 0 THEN ''OPERACAO CONFIRMADA'' 
					WHEN man.TP_EVENTO = 1 THEN ''CIENTE DA OPERACAO'' 
					WHEN man.TP_EVENTO = 2 THEN ''CIENTE DO DESCONHECIMENTO DA OPERACAO'' 
					WHEN man.TP_EVENTO = 3 THEN ''CIENTE DO NAO REGISTRO DA OPERACAO'' 
				ELSE 
					''MANIFESTO DE OPERACAO DESCONHECIDA'' 
				END 
			WHEN man.STS_MANIFESTO = 2 THEN 
				''MANIFESTO REJEITADO'' 
			ELSE 
				''MANIFESTO NAO REALIZADO'' 
		END 
		AS character varying(80) 
	) AS STATUS_MAN 
FROM lst_notas nt 
LEFT JOIN manifestos man ON man.CHAVE_NFE = nt.CHAVE_NFE'
end