/****** Object:  View [dbo].[V_PAINEL_NFE_NF_ENTRADA_PENDENTE_SOLUCAO_ANALITICO]    Script Date: 03/10/2018 11:00:00 ******/ 
IF EXISTS (SELECT * 
           FROM   SYS.VIEWS 
           WHERE  OBJECT_ID = 
      Object_id(N'[dbo].[V_PAINEL_NFE_NF_ENTRADA_PENDENTE_SOLUCAO_ANALITICO]')) 
  DROP VIEW [dbo].[V_PAINEL_NFE_NF_ENTRADA_PENDENTE_SOLUCAO_ANALITICO] 

GO 

--SELECT * FROM V_PAINEL_NFE_NF_ENTRADA_PENDENTE_SOLUCAO_ANALITICO 
CREATE VIEW V_PAINEL_NFE_NF_ENTRADA_PENDENTE_SOLUCAO_ANALITICO  
AS
SELECT DISTINCT 'Entrada' AS tipoMotivacao ,
                nf.nr_nf AS numeroNfe ,
                nf.cd_nf AS codigoControle ,
                nf.dt_emi_nf AS data ,
                nf.vlr_nf AS valor ,
                nf.cd_filial AS filialOrigemId ,
                nf.nm_orig AS filialDestinoNome ,
                nf.cgc_cpf AS cgcCpf ,
                nf.insc_est AS inscricaoEstadual ,
                coalesce(lote_erro.ds_mot_err, 'Motivo erro não informado !') COLLATE Latin1_General_CI_AI AS motivoRejeicao ,
                coalesce(lote_erro.ds_err, 'Descrição do erro não informado !') COLLATE Latin1_General_CI_AI AS descricaoRejeicao ,
                0 AS cancelamento
FROM v_est_nf_ent_cab nf
INNER JOIN V_EST_NF_NFE_PENDENTE_EMISSAO nfe ON nf.cd_emp = nfe.cd_emp
AND nf.cd_filial = nfe.cd_filial
AND nf.cd_nf = nfe.cd_nf
LEFT JOIN est_nfe_lote_ent lote_nf ON nfe.cd_emp = lote_nf.cd_emp
AND nfe.cd_filial = lote_nf.cd_filial
AND nfe.cd_nf = lote_nf.cd_nf
LEFT JOIN est_nfe_lote_ent_err lote_erro ON lote_erro.cd_nfe_lote = lote_nf.cd_nfe_lote
AND lote_erro.cd_nfe_lote_ent = lote_nf.cd_nfe_lote_ent
WHERE NFE.TIPO_NF IN (0, 3)
  AND NFE.PEND_SOLUC = 1
  AND NF.CD_EMP=1
UNION
SELECT DISTINCT 'Entrada' AS movimentacao ,
                nf.nr_nf AS numeroDocumento ,
                nf.cd_nf AS codigoDocumento ,
                nf.dt_emi_nf AS dataDocumento ,
                nf.vlr_nf AS valorDocumento ,
                nf.cd_filial AS codigoFilial ,
                nf.nm_orig AS nomeDestinatario ,
                nf.cgc_cpf AS codigoPessoa ,
                nf.insc_est AS codigoContribuinte ,
                coalesce(lote_erro.ds_retorno, 'Motivo erro não informado !') COLLATE Latin1_General_CI_AI AS motivoRejeicao ,
                coalesce(lote_erro.ds_retorno, 'Descrição do erro não informado !') COLLATE Latin1_General_CI_AI AS descricaoRejeicao ,
                1 AS cancelamento
FROM EST_NF_ENT_NFE_CANCEL_ERR LOTE_ERRO
LEFT JOIN EST_NF_ENT_NFE_CANCEL CANC ON CANC.CD_EMP = LOTE_ERRO.CD_EMP
AND CANC.CD_FILIAL = LOTE_ERRO.CD_FILIAL
AND CANC.CD_NF = LOTE_ERRO.CD_NF
INNER JOIN V_EST_NF_ENT_CAB NF ON NF.CD_EMP = LOTE_ERRO.CD_EMP
AND NF.CD_FILIAL = LOTE_ERRO.CD_FILIAL
AND NF.CD_NF = LOTE_ERRO.CD_NF
INNER JOIN V_EST_NF_NFE_PENDENTE_EMISSAO NFE ON NF.CD_EMP = NFE.CD_EMP
AND NF.CD_FILIAL = NFE.CD_FILIAL
AND NF.CD_NF = NFE.CD_NF
WHERE canc.CD_NF IS NULL
  AND NFE.TIPO_NF IN (0, 3)
  AND NFE.PEND_SOLUC = 1
  AND NF.CD_EMP=1