IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_INFORMACAO_DIVERSAS_MANUTENCAO]'))

DROP VIEW [dbo].[V_INFORMACAO_DIVERSAS_MANUTENCAO]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW 
	[dbo].[V_INFORMACAO_DIVERSAS_MANUTENCAO]
AS             
---- Buscando o numero do pedido do fornecedor
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,	
	'Número do pedido no fornecedor.: ' + cast(NR_PED_FORN as varchar) AS INFORMACAO
FROM V_EST_NF_ENT 
where NR_PED_FORN > 0
	
UNION ALL
------ MOTIVO DE DEVOLUÇÃO
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,	
	'Motivo de devolução ' + CAST(V_EST_NF_ENT.CD_MT_DEV_VD AS VARCHAR) +' - ' + CAST(DS_MT_DEV_VD AS VARCHAR) AS INFORMACAO
FROM V_EST_NF_ENT INNER JOIN GLB_MT_DEV_VD ON
V_EST_NF_ENT.CD_MT_DEV_VD = GLB_MT_DEV_VD.CD_MT_DEV_VD

UNION ALL
----- Nota fiscal cancelada
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,		
	CASE WHEN CD_NF  > 0 THEN
		'Nota fiscal cancelada'
	ELSE
		'Nota fiscal em Processo de cancelamento'
	END AS INFORMACAO

FROM EST_NF_ENT_NFE_CANCEL

UNION ALL
----- INFORMAÇÕES DE DUPLICATA
Select 
	PG_CRED_DUP_NR_CTR.CD_EMP,
	PG_CRED_DUP_NR_CTR.CD_FILIAL,
	PG_CRED_EST_NF_ENT.CD_NF,
	'Filial: ' + cast(PG_CRED_DUP_NR_CTR.CD_FILIAL as varchar) + 
	+ ' Duplicata: ' + cast(PG_CRED_DUP_NR_CTR.NR_DP as varchar) + ' Parcela: ' + cast(PG_CRED_EST_NF_ENT.PARC AS varchar)
	+ ' Valor R$ ' + cast(PG_CRED.VLR_DP as varchar)
	+ ' Data Cadastro: ' + CONVERT (VARCHAR (10), PG_CRED.DT_CAD,103)
	+ ' Data Vecimento: ' + CONVERT (VARCHAR (10), PG_CRED.DT_VENCTO,103)
	as INFORMACAO

From PG_CRED_EST_NF_ENT INNER JOIN PG_CRED_DUP_NR_CTR ON
	PG_CRED_EST_NF_ENT.CD_EMP = PG_CRED_DUP_NR_CTR.CD_EMP AND
	PG_CRED_EST_NF_ENT.CD_FILIAL = PG_CRED_DUP_NR_CTR.CD_FILIAL AND
	PG_CRED_EST_NF_ENT.NR_DP = PG_CRED_DUP_NR_CTR.NR_DP AND  
	PG_CRED_EST_NF_ENT.ANO = PG_CRED_DUP_NR_CTR.ANO
		
	INNER JOIN PG_CRED  ON
    PG_CRED_DUP_NR_CTR.CD_EMP = PG_CRED.CD_EMP AND  
    PG_CRED_DUP_NR_CTR.CD_FILIAL = PG_CRED.CD_FILIAL 
    AND PG_CRED_DUP_NR_CTR.NR_DP = PG_CRED.NR_DP AND 
    PG_CRED_DUP_NR_CTR.ANO = PG_CRED.ANO

UNION ALL

------ Mercadorias não entregues ao destinatário remetidas com a nota fiscal nº " & Format(RsTemp!NF_NF, "000000") & " de " & Format(RsTemp!DT_EMI_NF, "dd/mm/yyyy")
SELECT
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_NF,
	'Mercadorias não entregues ao destinatário remetidas com a nota fiscal nº ' + CAST(EST_NF_SAI.NF_NF AS VARCHAR)
	+ ' de ' + CONVERT (VARCHAR (10), EST_NF_SAI.DT_EMI_NF,103) as INFORMACAO

FROM EST_NF_SAI INNER JOIN EST_NF_SAI_NF_ENT ON 
	EST_NF_SAI.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP AND 
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_SAI 

UNION ALL
--- Verificando se a nota de entrada e de devolução de cupom fiscal
SELECT
	PDV_VD_EST_NF_ENT.CD_EMP,
	PDV_VD_EST_NF_ENT.CD_FILIAL,
	PDV_VD_EST_NF_ENT.CD_NF,
	'Devolução referente cupom fiscal nr.: ' + cast(PDV_VD.NR_ECF as varchar) + ' caixa: ' + cast(PDV_VD.CD_CX as varchar)
	+ ' Emissão: ' + CONVERT (VARCHAR (10), PDV_VD.DT_VD,103) as INFORMACAO
     
from PDV_VD_EST_NF_ENT INNER JOIN PDV_VD ON 
	PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP AND 
	PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD


UNION ALL
----- 'Verificando se há DARE gerado
select 
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CASE WHEN CD_NF > 0 THEN
		'Nota fiscal de entrada com DARE já gerado'
	ELSE
		'Nota fiscal de entrada sem DARE gerado'
	END AS INFORMACAO

from EST_NF_ENT_DAR

UNION ALL

-----'Buscando protocolo da Nfe
SELECT
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP,
	EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL,
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF,
	'Protocolo NFe.: ' + CAST(NR_PROTOCOLO AS VARCHAR) + ' Num.Autorização: ' + cast(NR_AUTORIZADOR as varchar) as INFORMACAO

FROM EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID 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 

UNION ALL
---------Testa se a nota é proveniente de devolução por requisição
SELECT DISTINCT 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF,
	'Nota fiscal proveniente de devoluçao de cupom fiscal nr: ' + CAST(PDV_VD.NR_ECF AS VARCHAR) 
	+ ' Caixa: ' + cast(PDV_VD.NR_ECF as varchar)
	+ ' Data: ' + CONVERT (VARCHAR (10), PDV_VD.DT_VD,103) as INFORMACAO

    
FROM PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD 
	INNER JOIN PDV_VD ON 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP


UNION ALL
-------- Requisição de devolução
SELECT DISTINCT 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF,
	'Requisição de devolução nr: ' + cast(PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP as varchar)
	+ ' Data de emissão: ' +  + CONVERT (VARCHAR (10), PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,103) as INFORMACAO
	    
FROM PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD 
	INNER JOIN PDV_VD ON 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP


UNION ALL
-------- Nota fiscal de devolução so pode ser cancelada na data de emissão da mesma.
SELECT DISTINCT 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL,
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF,
	
	CASE WHEN CONVERT (VARCHAR (10), PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,103) <> CONVERT(VARCHAR (10),GETDATE(),103) THEN
		'Nota fiscal de devolução so pode ser cancelada na data de emissão da mesma'
	END AS INFORMACAO
	    
FROM PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON 
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD = PDV_VD_DEV_EST_PROD_IMP.CD_VD AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP = PDV_VD_DEV_EST_PROD_IMP.CD_IMP AND
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD = PDV_VD_DEV_EST_PROD_IMP.CD_PROD 
	INNER JOIN PDV_VD ON 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP

UNION ALL
----- Controle de pré-faturas
SELECT 
	EST_NF_ENT_EST_PED_VD.CD_EMP,
	EST_NF_ENT_EST_PED_VD.CD_FILIAL,
	EST_NF_ENT_EST_PED_VD.CD_NF,
	'Nota fiscal com de pré-fatura de devolucao nr.: ' + CAST(EST_NF_ENT_EST_PED_VD.CD_PED AS VARCHAR) 
	+ 'data de emissão: ' + CONVERT (VARCHAR (10), EST_PED_VD.DT_EMI_PED,103) AS INFORMACAO

FROM EST_NF_ENT_EST_PED_VD INNER JOIN EST_PED_VD ON 
	EST_NF_ENT_EST_PED_VD.CD_EMP = EST_PED_VD.CD_EMP AND
	EST_NF_ENT_EST_PED_VD.CD_FILIAL = EST_PED_VD.CD_FILIAL AND 
	EST_NF_ENT_EST_PED_VD.CD_PED = EST_PED_VD.CD_PED

UNION ALL
------ Nota fiscal proveniente de pré-fatura nr.: 
SELECT 
	EST_NF_ENT_PED_VD.CD_EMP,
	EST_NF_ENT_PED_VD.CD_FILIAL,
	EST_NF_ENT_PED_VD.CD_NF,
	'Nota fiscal proveniente de pré-fatura nr.: ' + CAST(EST_NF_ENT_PED_VD.CD_PED AS VARCHAR)
	+ ' Data de emissão: ' + CONVERT (VARCHAR (10), EST_PED_VD.DT_EMI_PED,103) AS INFORMACAO

FROM EST_NF_ENT_PED_VD INNER JOIN EST_PED_VD ON 
	EST_NF_ENT_PED_VD.CD_EMP = EST_PED_VD.CD_EMP AND
	EST_NF_ENT_PED_VD.CD_FILIAL = EST_PED_VD.CD_FILIAL AND 
	EST_NF_ENT_PED_VD.CD_PED = EST_PED_VD.CD_PED


UNION ALL
-------- Nota fiscal com vínculo com conhecimento de frete nr.: " & Format(RsDevReq!NR_NF, "000000") & " data de emissão: " & Format(RsDevReq!DT_EMI_NF, "dd/mm/yyyy") & " valor " & Format(RsDevReq!VLR_NF, "standard")
SELECT
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.CD_NF,
	'Nota fiscal com vínculo com conhecimento de frete nr.: ' + CAST(EST_NF_ENT.NR_NF AS VARCHAR) 
	+ ' Data emissão: ' + CONVERT (VARCHAR (10), EST_NF_ENT.DT_EMI_NF,103) +
	' Valor R$ ' + CAST(EST_NF_ENT.VLR_NF AS VARCHAR) AS INFORMACAO

FROM EST_NF_ENT_CONH_FRETE INNER JOIN EST_NF_ENT ON 
	EST_NF_ENT_CONH_FRETE.CD_EMP = EST_NF_ENT.CD_EMP AND
	EST_NF_ENT_CONH_FRETE.CD_EMP = EST_NF_ENT.CD_EMP AND 
	EST_NF_ENT_CONH_FRETE.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
	EST_NF_ENT_CONH_FRETE.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND 
	EST_NF_ENT_CONH_FRETE.CD_NF_FRETE = EST_NF_ENT.CD_NF

UNION ALL
-------------Nota fiscal com cross docking gerado nr.: 

SELECT TOP 1 
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	'Nota fiscal com cross docking gerado nr.: ' + CAST(CD_CTR_CROSS_DOCK AS VARCHAR) + ' em: ' + CONVERT (VARCHAR (10), DT_CAD,103) AS INFORMACAO
    
FROM EST_NF_ENT_CROSS_DOCKING_EST_PROD_TRANSF

UNION ALL
    -------------Notas de devolução de compras
SELECT 
	EST_NF_ENT_NF_SAI.CD_EMP,
	EST_NF_ENT_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_NF,
	'Nota fiscal devolução nr.: ' + CAST(EST_NF_SAI.NF_NF AS VARCHAR) + ' Em ' 
	+ CONVERT (VARCHAR (10), EST_NF_SAI.DT_EMI_NF,103) + ' - ' + CONVERT (VARCHAR (10),  EST_NF_SAI.HR_EMI_NF,108) AS INFORMACAO
    
FROM EST_NF_ENT_NF_SAI INNER JOIN EST_NF_SAI ON 
	EST_NF_ENT_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND 
	EST_NF_ENT_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EST_NF_ENT_NF_SAI.CD_NF_SAI = EST_NF_SAI.CD_NF 

--ORDER BY EST_NF_SAI.DT_EMI_NF, EST_NF_SAI.NF_NF

UNION ALL
------------ Buscando nota de entrado no caso de devolução de compras
SELECT 
	V_EST_NF_SAI_CAB.CD_EMP,
	V_EST_NF_SAI_CAB.CD_FILIAL,
	V_EST_NF_SAI_CAB.CD_NF,
	'Nota fiscal de entrada relacionada com a nota fiscal de saida: ' + CAST(V_EST_NF_SAI_CAB.NF_NF AS VARCHAR)
	+ ' em ' + CONVERT (VARCHAR (10), V_EST_NF_SAI_CAB.DT_EMI_NF,103) + ' movimento.: ' + cast(V_EST_NF_SAI_CAB.DS_MOV_OP as varchar) AS INFORMACAO

FROM EST_NF_SAI_EST_NF_ENT_ADJUDICACAO INNER JOIN V_EST_NF_SAI_CAB ON 
	EST_NF_SAI_EST_NF_ENT_ADJUDICACAO.CD_EMP = V_EST_NF_SAI_CAB.CD_EMP AND 
	EST_NF_SAI_EST_NF_ENT_ADJUDICACAO.CD_FILIAL = V_EST_NF_SAI_CAB.CD_FILIAL AND
	EST_NF_SAI_EST_NF_ENT_ADJUDICACAO.CD_NF = V_EST_NF_SAI_CAB.CD_NF 

UNION ALL
-------- Lotes de envio
SELECT 
	EST_NFE_LOTE_ENT.CD_EMP,
	EST_NFE_LOTE_ENT.CD_FILIAL,
	EST_NFE_LOTE_ENT.CD_NF,
	CASE WHEN ISNULL(EST_NF_ENT_NFE_PEND.PEND_SOLUC,0) = 0 THEN
		'Lote de envio NFE;: ' + CAST(EST_NFE_LOTE.CD_NFE_LOTE AS VARCHAR)
		+ ' Data/Hora Lote.: ' + CONVERT (VARCHAR (10), EST_NFE_LOTE.DT_CAD,103) + ' - ' +  CONVERT (VARCHAR (10),  EST_NFE_LOTE.HR_CAD,108)
		+ ' Usuário: ' + cast(GLB_USU.NM_USU as varchar)
	ELSE
		'Lote de envio NFE;: ' + CAST(EST_NFE_LOTE.CD_NFE_LOTE AS VARCHAR)
		+ ' Data/Hora Lote.: ' + CONVERT (VARCHAR (10), EST_NFE_LOTE.DT_CAD,103) + ' - ' + CONVERT (VARCHAR (10),  EST_NFE_LOTE.HR_CAD,108)
		+ ' Usuário: ' + cast(GLB_USU.NM_USU as varchar) + '.' + ' Aguardando solução.'
	END  AS INFORMACAO
    
FROM EST_NFE_LOTE_ENT INNER JOIN EST_NFE_LOTE ON 
	EST_NFE_LOTE_ENT.CD_NFE_LOTE = EST_NFE_LOTE.CD_NFE_LOTE 
	INNER JOIN GLB_USU ON 
	EST_NFE_LOTE.CD_USU = GLB_USU.CD_USU
	LEFT OUTER JOIN EST_NF_ENT_NFE_PEND ON
	EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT_NFE_PEND.CD_EMP AND
	EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT_NFE_PEND.CD_FILIAL AND
	EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT_NFE_PEND.CD_NF

UNION ALL
----- Lotes de envio arquivamento
----- Buscando arquivo xml armazenado no banco de dados
SELECT 
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CASE WHEN CD_CTR_DANFE_PROV IS NULL THEN
		'Nota fiscal sem arquivo xml armazenado.'
	ELSE
		'Nota fiscal com arquivo xml armazenado.'
	END AS INFORMACAO

FROM EST_NF_ENT_ARQ_DANFE

UNION ALL
--------  Testando se a nota fiscal foi usada em baixa de verba comercial
SELECT 
	CG_FND_VERBA_EST_NF_ENT.CD_EMP,
	CG_FND_VERBA_EST_NF_ENT.CD_FILIAL,
	CG_FND_VERBA_EST_NF_ENT.CD_NF,
	'Nota fiscal usada para baixa de verba comercial contrato nr.: ' + CAST(CG_FND_VERBA_EST_NF_ENT.CD_FND_VERBA AS VARCHAR)
	+ ' Usuário: ' + cast(GLB_USU.NM_USU as varchar) + ' Data Lançamento: ' 
	+ CONVERT (VARCHAR (10), CG_FND_VERBA_EST_NF_ENT.DT_CAD,103) AS INFORMACAO

FROM CG_FND_VERBA_EST_NF_ENT INNER JOIN GLB_USU ON 
	CG_FND_VERBA_EST_NF_ENT.CD_USU = GLB_USU.CD_USU

UNION ALL
------- Buscando usuario quem liberou a divergencia
SELECT 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP,
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF,
	'Usuário Liberação: ' + CAST(EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.CD_USU_LIB AS VARCHAR)
	+ ' - ' + CAST(GLB_USU.NM_USU AS VARCHAR) + ' Data bloqueio: ' + CONVERT (VARCHAR (10), EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.DT_CAD,103)
	+ ' - ' + CONVERT (VARCHAR (8), EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.DT_CAD,108)
	+ ' Data liberação: ' + CONVERT (VARCHAR (10), EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.DT_LIBERACAO,103)
	+ ' - ' + CONVERT (VARCHAR (8), EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.DT_LIBERACAO,108) AS INFORMACAO
	

FROM EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO INNER JOIN GLB_USU ON
	EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.CD_USU_LIB = GLB_USU.CD_USU
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
	EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR


UNION ALL
------- Buscando protocolo da Nfe
SELECT 
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	'Protocolo Nfe.: ' + cast(NR_PROTOCOLO as varchar) + ' Número Autorizador: ' + cast(NR_AUTORIZADOR as varchar) AS INFORMACAO

FROM EST_NF_ENT_NFE

UNION ALL
-------Buscando protocolo da Nfe
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	'Número pedido WMS entrada.: ' + cast(CD_PED_WMS as varchar) AS INFORMACAO

FROM EST_NF_ENT_EST_WMS_ENT

UNION ALL
------- Buscando carta de correcao
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CASE WHEN NR_AUTORIZADOR <> 0 And STS_IMP = 1 THEN
		'Nota fiscal com carta de correção nr.: ' + cast(EST_NFE_CARTA_CORRECAO.CD_CTR_CARTA_CORRECAO as varchar)
		+ ' Autorizador.: ' + cast(NR_AUTORIZADOR as varchar) + ' Protocolo: ' + cast(NR_PROTOCOLO as varchar)
		+ ' Validada em : '  + CONVERT (VARCHAR (10), DT_RECBTO,103) + ' - ' + CONVERT (VARCHAR (8), DT_RECBTO,108)
	ELSE
		'Nota fiscal com carta de correção nr.: ' + cast(EST_NFE_CARTA_CORRECAO.CD_CTR_CARTA_CORRECAO as varchar)
		+ ' Sem validacao na SEFAZ.'

	END AS INFORMACAO

FROM EST_NFE_CARTA_CORRECAO_EST_NF_ENT INNER JOIN EST_NFE_CARTA_CORRECAO ON
	EST_NFE_CARTA_CORRECAO_EST_NF_ENT.CD_CTR_CARTA_CORRECAO = EST_NFE_CARTA_CORRECAO.CD_CTR_CARTA_CORRECAO
	LEFT OUTER JOIN EST_NFE_CARTA_CORRECAO_VALIDADA ON
	EST_NFE_CARTA_CORRECAO.CD_CTR_CARTA_CORRECAO = EST_NFE_CARTA_CORRECAO_VALIDADA.CD_CTR_CARTA_CORRECAO
	
UNION ALL
------- Buscando efeitivo pagamento integral
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CASE WHEN NR_AUTORIZADOR <> 0 And STS_IMP = 1 THEN
		'Nota fiscal com Informação de efetivo pagamento integral nr.: ' + cast(EST_NFE_INF_EFET_PGTO_INTEG.CD_CTR_INF_EFET_PGTO_INTEG as varchar)
		+ ' Autorizador.: ' + cast(NR_AUTORIZADOR as varchar) + ' Protocolo: ' + cast(NR_PROTOCOLO as varchar)
		+ ' Validada em : '  + CONVERT (VARCHAR (10), DT_RECBTO,103) + ' - ' + CONVERT (VARCHAR (8), DT_RECBTO,108)
	ELSE
		'Nota fiscal com Informação de efetivo pagamento integral nr.: ' + cast(EST_NFE_INF_EFET_PGTO_INTEG.CD_CTR_INF_EFET_PGTO_INTEG as varchar)
		+ ' Sem validacao na SEFAZ.'

	END AS INFORMACAO

FROM EST_NFE_INF_EFET_PGTO_INTEG_EST_NF_ENT INNER JOIN EST_NFE_INF_EFET_PGTO_INTEG ON
	EST_NFE_INF_EFET_PGTO_INTEG_EST_NF_ENT.CD_CTR_INF_EFET_PGTO_INTEG = EST_NFE_INF_EFET_PGTO_INTEG.CD_CTR_INF_EFET_PGTO_INTEG
	LEFT OUTER JOIN EST_NFE_INF_EFET_PGTO_INTEG_VALIDADA ON
	EST_NFE_INF_EFET_PGTO_INTEG.CD_CTR_INF_EFET_PGTO_INTEG = EST_NFE_INF_EFET_PGTO_INTEG_VALIDADA.CD_CTR_INF_EFET_PGTO_INTEG

GO

