IF EXISTS(SELECT * FROM SYS.VIEWS
			 WHERE OBJECT_ID = OBJECT_ID(N'[V_SPED_EST_NF_ENT]'))
    DROP VIEW V_SPED_EST_NF_ENT;
GO
GO
CREATE VIEW V_SPED_EST_NF_ENT
WITH ENCRYPTION
AS
     SELECT V_EST_NF_ENT.CD_EMP,
            V_EST_NF_ENT.CD_FILIAL,
            V_EST_NF_ENT.CD_NF AS CD_DOC,
            0 AS IND_OPER,
            (CASE
                 WHEN EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT > 0
                 THEN 0
                 ELSE 1
             END) AS IND_EMIT,
            V_EST_NF_ENT.CD_ORIG,
            '01' AS COD_MOD,
            V_EST_NF_ENT.TP_SR_NFE,
            V_EST_NF_ENT.SERIE,
            V_EST_NF_ENT.NR_NF AS NR_DOC,
            ISNULL(V_EST_NF_ENT_NFE_SPED.NR_AUTORIZADOR, '') AS CHAVE_NFE,
            V_EST_NF_ENT.DT_EMI_NF,
            V_EST_NF_ENT.DT_ENT AS DT_ENT_SAI,
            V_EST_NF_ENT.VLR_NF,
            PGTO_VISTA_PRAZO AS IND_PAGTO,
            V_EST_NF_ENT.VLR_DESC_CAB AS VLR_DESC,
            0 AS VLR_ABAT,
            CASE
                WHEN ISNULL(V_EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0
                THEN V_EST_NF_ENT.VLR_TOT_PROD
                ELSE V_EST_NF_ENT.VLR_TOT_PROD - V_EST_NF_ENT.VLR_DESC_CAB
            END AS VLR_MERC,
            1 AS TP_FRETE,
            VLR_FRETE,
            0 AS VLR_SEG,
            V_EST_NF_ENT.VLR_OUTROS AS VLR_OUTRAS_DESP_CAB,
            V_APURA_ICMS.VLR_BASE_ICMS_CAB,
            V_APURA_ICMS.VLR_ICMS_CAB,
            V_EST_NF_ENT.VLR_BASE_ICMS_SUBS AS VLR_BASE_ICMS_SUBS_CAB,
            V_EST_NF_ENT.VLR_ICMS_SUBS AS VLR_ICMS_SUBS_CAB,
            V_EST_NF_ENT.VLR_IPI AS VLR_IPI_CAB,
            V_EST_NF_ENT.VRL_ISENTAS AS VLR_ISENTAS_CAB,
            V_EST_NF_ENT.CGC_CPF,
            V_EST_NF_ENT.STS_NF,
            V_EST_NF_ENT.VLR_OUTROS AS VLR_SERV_NT,
            0 AS VLR_BASE_ISSQN_CAB,
            0 VLR_ISSQN_CAB,
            0 AS VLR_BASE_IRRF_CAB,
            0 AS VLR_IRRF_CAB,
            0 AS CD_TRANSP,
            QT_VOL,
            PESO_BRT,
            PESO_LIQ,
            ISNULL(
                  (
                      SELECT TOP 1 CD_EMP
                      FROM PG_CRED_EST_NF_ENT
                      WHERE V_EST_NF_ENT.CD_EMP = PG_CRED_EST_NF_ENT.CD_EMP
                            AND V_EST_NF_ENT.CD_FILIAL = PG_CRED_EST_NF_ENT.CD_FILIAL
                            AND V_EST_NF_ENT.CD_NF = PG_CRED_EST_NF_ENT.CD_NF
                  ), 0) AS POSSUI_DUPL,
            V_EST_NF_ENT.TP_MOV,
            V_EST_NF_ENT.TP_MOV_OP_TRANSA,
            V_EST_NF_ENT.DEST_ORIG,
            CASE
                WHEN DEST_ORIG = 0
                THEN 'CLI'
                WHEN DEST_ORIG = 1
                THEN 'FOR'
                ELSE 'FIL'
            END AS TIPO_PART,
            V_EST_NF_ENT.SEQ_ITEM AS NUM_ITEM,
            V_EST_NF_ENT.CD_PROD AS COD_ITEM,
            EST_PROD_CPL.TP_PROD AS TIPO_ITEM,
            V_EST_NF_ENT.DS_PROD, -- 
            V_EST_NF_ENT.QT_IT,
            V_EST_NF_ENT.VLR_IT,
            V_EST_NF_ENT.VLR_TOT_IT AS VLR_ITEM,
            V_EST_NF_ENT.CD_TRIB_FC AS CST_ICMS,
            V_EST_NF_ENT.TRIB_FC_ELETRONICO,
            V_EST_NF_ENT.CFOP,
            CASE
                WHEN V_EST_NF_ENT.ICMS > 0
                THEN V_EST_NF_ENT.VLR_BASE_ICMS_IT
                ELSE 0
            END VLR_BASE_ICMS,
            CASE
                WHEN V_EST_NF_ENT.ICMS > 0
                THEN V_EST_NF_ENT.ALIQ_ICMS
                ELSE 0
            END ALIQ_ICMS,
            CASE
                WHEN V_EST_NF_ENT.ICMS > 0
                     OR TP_MOV IN(58)
                THEN V_EST_NF_ENT.VLR_ICMS_IT
                ELSE 0
            END VLR_ICMS_IT,
            V_EST_NF_ENT.VLR_BASE_SUBS_IT AS VLR_BASE_SUBS,
            V_EST_NF_ENT.VLR_ICMS_SUBS_IT AS VLR_ICMS_SUBS,
            V_EST_NF_ENT.VLR_IPI_IT,
            V_EST_NF_ENT.PERC_IPI,
            V_EST_NF_ENT.VLR_ISENTO_IT,
            ISNULL(V_EST_NF_ENT.CST_PIS, 0) CST_PIS,
            ISNULL(V_EST_NF_ENT.CST_COFINS, 0) CST_COFINS,
            ISNULL(V_EST_NF_ENT.VLR_BASE_PIS, 0) VLR_BASE_PIS,
            ISNULL(V_EST_NF_ENT.PERC_ALIQ_PIS, 0) PERC_ALIQ_PIS,
            ISNULL(V_EST_NF_ENT.VLR_PIS, 0) VLR_PIS,
            ISNULL(V_EST_NF_ENT.VLR_BASE_COFINS, 0) VLR_BASE_COFINS,
            ISNULL(V_EST_NF_ENT.PERC_ALIQ_COFINS, 0) PERC_ALIQ_COFINS,
            ISNULL(V_EST_NF_ENT.VLR_COFINS, 0) VLR_COFINS,
            V_EST_NF_ENT.VLR_OUTRAS_DESPESAS AS VLR_OUTRAS_DESPESAS_IT,
            0 AS VLR_BASE_ISSQN,
            0 AS VLR_ISSQN,
            0 AS ALIQ_ISSQN,
            V_EST_NF_ENT.CD_ORIG AS COD_PART,
            1 AS COD_IMP_ALMOX,
            V_EST_NF_ENT.LISTA_PROD,
            V_EST_NF_ENT.SUBS_TRIB,
            V_EST_NF_ENT.SUBS_TRIB_ANT,
            V_EST_NF_ENT.CD_NT_OP,
            V_EST_NF_ENT.ICMS,
            V_EST_NF_ENT.ICMS_RD,
            FLAG_OPERADOR_LOG,
            ISNULL(
                  (
                      SELECT SUM(EST_NF_ENT_IT_EST_PROD_CPL_LOTE.QTDE_PROD) QTDE_PROD
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS QTDE_PROD,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.NR_LOTE
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                      ORDER BY EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS NR_LOTE,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.DT_VALID
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS DT_VALID,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.DT_FABR
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS DT_FABR,
            EST_PROD_PRECO.VLR_TABELA,
            CASE
                WHEN CTR_PMC = 1
                THEN 1
                ELSE 0
            END AS PROD_MEDICAM,
            (CASE
                 WHEN DS_SIGLA_LINHA = 'S'
                 THEN 0   --' SIGLAS: E - ETICO,   S - SIMILAR, G - GENERICO |0- SIMILAR; 1- GEN?RICO; 2- ?TICO;
                 WHEN DS_SIGLA_LINHA = 'G'
                 THEN 1
                 WHEN DS_SIGLA_LINHA = 'E'
                 THEN 2
                 ELSE 0
             END) TIPO_MEDIC,
            ISNULL(V_EST_NF_ENT.NM_ORIG, '') AS NOME_PART,
            V_EST_NF_ENT.ENDE AS ENDERECO,
            V_EST_NF_ENT.BAIRRO,
            ISNULL(V_EST_NF_ENT.NUMERO, '') NUMERO,
            V_EST_NF_ENT.INSC_EST,
            V_EST_NF_ENT.UF,
            V_EST_NF_ENT.TEL,
            GLB_CID.CD_PAIS,
            ISNULL(GLB_MUNICIPIO.COD_MUNICIPIO_SINTEGRA, 0) AS COD_IBGE, -- SELECT TOP 2 CEP,* FROM V_EST_NF_ENT
            ISNULL(V_EST_NF_ENT.CEP, '') CEP,
            EST_PROD_CPL.TP_PROD,
            ISNULL(
                  (
                      SELECT TOP (1) CD_BARRA
                      FROM EST_PROD_CD_BARRA
                      WHERE CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND CD_PROD = V_EST_NF_ENT.CD_PROD
                      ORDER BY CD_BARRA
                  ), '') AS COD_BARRA,
            V_EST_NF_ENT.NR_NCM AS COD_NCM,
            V_EST_NF_ENT.VLR_DESC_IT,
            ISNULL(V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.PIS_NF, 0) PIS_NF,
            ISNULL(V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.COFINS_NF, 0) COFINS_NF,
            V_EST_NF_ENT.DS_NT_OP,
            V_EST_NF_ENT.CD_MOV_OP,
            V_EST_NF_ENT.FLAG_NAO_GERAR_ARQ_SPED,
            V_EST_NF_ENT.CD_TRIB,
            ISNULL(EST_NF_ENT_CANCEL.CANCEL_FORA_PRAZO, 0) CANCEL_FORA_PRAZO,
            ISNULL(EST_NF_ENT_NFE_CANCEL.NR_PROTOCOLO, '') AS PROTOCOLO_CANCEL,
            ISNULL(EST_NF_ENT_DAR_IT.CD_EMP, 0) AS POSSUI_DARE,
            '' AS NUM_DARE,
            '' AS VALOR_DARE,
            '' AS DT_VENCTO_DARE,
            '' AS DT_RECOLHIMENTO_DARE,
            V_EST_NF_ENT.FLAG_MOV_OP_NFE_CPL, -- select top 2 vlr_frete_cabe_rateio, * from V_EST_NF_ENT where cd_filial = 1030 and nr_nf = 13341
            ISNULL(EST_NF_ENT_DENEGADA.CD_EMP, 0) AS NOTA_DENEGADA,
            ISNULL(VLR_FRETE_CABE_RATEIO, 0) VLR_FRETE_CABE_RATEIO,
            ISNULL(EST_NF_SAI_NF_ENT.CD_NF_SAI, 0) CD_NF_SAI,
            V_APURA_ICMS.VLR_FECOP,
            ISNULL(NR_CEST, 0) AS NR_CEST,
            V_EST_NF_ENT.COD_IBGE_MUNICIPIO_DESTINATARIO,
            V_EST_NF_ENT.END_DESTINATARIO,
            V_EST_NF_ENT.BAIRRO_DESTINATARIO,
            V_EST_NF_ENT.CEP_DESTINATARIO,
            V_EST_NF_ENT.CIDADE_DESTINATARIO,
            V_EST_NF_ENT.CNPJ_DESTINATARIO,
            V_EST_NF_ENT.INSC_ESTADUAL_DESTINATARIO,
            V_EST_NF_ENT.RZ_DESTINATARIO,
            V_EST_NF_ENT.UF_DESTINATARIO,
            V_EST_NF_ENT.CD_CID_DESTINATARIO,
            V_EST_NF_ENT.ALIQ_ICMS_INTERNO,
            V_APURA_ICMS.VLR_ICMS_DESONERADO,
            V_EST_NF_ENT.FLAG_MICRO_EMPRESA AS PART_FLAG_MICRO_EMPRESA,
            V_APURA_ICMS.BASE_ICMS_SIMPLES,
            V_APURA_ICMS.ALIQ_SIMPLES,
            V_APURA_ICMS.VLR_ICMS_SIMPLES
     FROM V_EST_NF_ENT
          INNER JOIN V_APURA_ICMS ON V_EST_NF_ENT.CD_EMP = V_APURA_ICMS.CD_EMP
                                     AND V_EST_NF_ENT.CD_FILIAL = V_APURA_ICMS.CD_FILIAL
                                     AND V_EST_NF_ENT.CD_NF = V_APURA_ICMS.CD_NF
                                     AND V_APURA_ICMS.TIPO = 0
          INNER JOIN EST_PROD_CPL ON V_EST_NF_ENT.CD_EMP = EST_PROD_CPL.CD_EMP
                                     AND V_EST_NF_ENT.CD_FILIAL = EST_PROD_CPL.CD_FILIAL
                                     AND V_EST_NF_ENT.CD_PROD = EST_PROD_CPL.CD_PROD
          LEFT JOIN EST_NF_SAI_NF_ENT ON V_EST_NF_ENT.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
                                         AND V_EST_NF_ENT.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
                                         AND V_EST_NF_ENT.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_ENT
          LEFT JOIN EST_PROD_PRECO ON EST_PROD_PRECO.CD_EMP = V_EST_NF_ENT.CD_EMP
                                      AND EST_PROD_PRECO.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                      AND EST_PROD_PRECO.CD_PROD = V_EST_NF_ENT.CD_PROD
          LEFT JOIN EST_NF_ENT_EF_NF_CTR_NF ON EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = V_EST_NF_ENT.CD_EMP
                                               AND EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                               AND EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT = V_EST_NF_ENT.CD_NF
          LEFT JOIN GLB_CID ON V_EST_NF_ENT.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_CID_GLB_MUNICIPIO ON GLB_CID_GLB_MUNICIPIO.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_MUNICIPIO ON GLB_MUNICIPIO.CD_MUNICIPIO = GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO
          LEFT JOIN V_EST_NF_ENT_IMPOSTOS_PIS_COFINS ON V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_EMP = V_EST_NF_ENT.CD_EMP
                                                        AND V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                                        AND V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN V_EST_NF_ENT_NFE_SPED ON V_EST_NF_ENT_NFE_SPED.CD_EMP = V_EST_NF_ENT.CD_EMP
                                             AND V_EST_NF_ENT_NFE_SPED.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                             AND V_EST_NF_ENT_NFE_SPED.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN EST_NF_ENT_DAR_IT ON V_EST_NF_ENT.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP
                                         AND V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
                                         AND V_EST_NF_ENT.CD_NF = EST_NF_ENT_DAR_IT.CD_NF
                                         AND V_EST_NF_ENT.CD_PROD = EST_NF_ENT_DAR_IT.CD_PROD
          LEFT JOIN EST_NF_ENT_CANCEL ON EST_NF_ENT_CANCEL.CD_EMP = V_EST_NF_ENT.CD_EMP
                                         AND EST_NF_ENT_CANCEL.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                         AND EST_NF_ENT_CANCEL.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN EST_NF_ENT_NFE_CANCEL ON EST_NF_ENT_CANCEL.CD_EMP = EST_NF_ENT_NFE_CANCEL.CD_EMP
                                             AND EST_NF_ENT_CANCEL.CD_FILIAL = EST_NF_ENT_NFE_CANCEL.CD_FILIAL
                                             AND EST_NF_ENT_CANCEL.CD_NF = EST_NF_ENT_NFE_CANCEL.CD_NF
          LEFT JOIN EST_NF_ENT_DENEGADA ON V_EST_NF_ENT.CD_EMP = EST_NF_ENT_DENEGADA.CD_EMP
                                           AND V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DENEGADA.CD_FILIAL
                                           AND V_EST_NF_ENT.CD_NF = EST_NF_ENT_DENEGADA.CD_NF
     WHERE EST_NF_ENT_DAR_IT.CD_NF IS NULL
 
			--AND  V_EST_NF_ENT.CD_FILIAL =17 AND V_EST_NF_ENT.cD_NF = 1719
			----===========================================================
     UNION ALL
     SELECT V_EST_NF_ENT.CD_EMP,
            V_EST_NF_ENT.CD_FILIAL,
            V_EST_NF_ENT.CD_NF AS CD_DOC,
            0 AS IND_OPER,
            (CASE
                 WHEN EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT > 0
                 THEN 0
                 ELSE 1
             END) AS IND_EMIT,
            V_EST_NF_ENT.CD_ORIG,
            '01' AS COD_MOD,
            V_EST_NF_ENT.TP_SR_NFE,
            V_EST_NF_ENT.SERIE,
            V_EST_NF_ENT.NR_NF AS NR_DOC,
            ISNULL(V_EST_NF_ENT_NFE_SPED.NR_AUTORIZADOR, '') AS CHAVE_NFE,
            V_EST_NF_ENT.DT_EMI_NF,
            V_EST_NF_ENT.DT_ENT AS DT_ENT_SAI,
            V_EST_NF_ENT.VLR_NF,
            PGTO_VISTA_PRAZO AS IND_PAGTO,
            V_EST_NF_ENT.VLR_DESC_CAB AS VLR_DESC,
            0 AS VLR_ABAT,
            CASE
                WHEN ISNULL(V_EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0
                THEN V_EST_NF_ENT.VLR_TOT_PROD
                ELSE V_EST_NF_ENT.VLR_TOT_PROD - V_EST_NF_ENT.VLR_DESC_CAB
            END AS VLR_MERC,
            1 AS TP_FRETE,
            VLR_FRETE,
            0 AS VLR_SEG,
            V_EST_NF_ENT.VLR_OUTROS AS VLR_OUTRAS_DESP_CAB,
            V_EST_NF_ENT_CAB_DAR.VLR_CAB_BASE_ICMS_DARE AS VLR_BASE_ICMS_CAB,
            V_EST_NF_ENT_CAB_DAR.VLR_CAB_ICMS_DARE AS VLR_ICMS_CAB,
            V_EST_NF_ENT_CAB_DAR.VLR_BASE_SUBS_DARE AS VLR_BASE_ICMS_SUBS_CAB,
            V_EST_NF_ENT_CAB_DAR.VLR_ICMS_SUBS_DARE AS VLR_ICMS_SUBS_CAB,
            V_EST_NF_ENT.VLR_IPI AS VLR_IPI_CAB,
            V_EST_NF_ENT.VRL_ISENTAS AS VLR_ISENTAS_CAB,
            V_EST_NF_ENT.CGC_CPF,
            V_EST_NF_ENT.STS_NF,
            V_EST_NF_ENT.VLR_OUTROS AS VLR_SERV_NT,
            0 AS VLR_BASE_ISSQN_CAB,
            0 VLR_ISSQN_CAB,
            0 AS VLR_BASE_IRRF_CAB,
            0 AS VLR_IRRF_CAB,
            0 AS CD_TRANSP,
            QT_VOL,
            PESO_BRT,
            PESO_LIQ,
            ISNULL(
                  (
                      SELECT TOP 1 CD_EMP
                      FROM PG_CRED_EST_NF_ENT
                      WHERE V_EST_NF_ENT.CD_EMP = PG_CRED_EST_NF_ENT.CD_EMP
                            AND V_EST_NF_ENT.CD_FILIAL = PG_CRED_EST_NF_ENT.CD_FILIAL
                            AND V_EST_NF_ENT.CD_NF = PG_CRED_EST_NF_ENT.CD_NF
                  ), 0) AS POSSUI_DUPL,
            V_EST_NF_ENT.TP_MOV,
            V_EST_NF_ENT.TP_MOV_OP_TRANSA,
            V_EST_NF_ENT.DEST_ORIG,
            CASE
                WHEN DEST_ORIG = 0
                THEN 'CLI'
                WHEN DEST_ORIG = 1
                THEN 'FOR'
                ELSE 'FIL'
            END AS TIPO_PART,
            V_EST_NF_ENT.SEQ_ITEM AS NUM_ITEM,
            V_EST_NF_ENT.CD_PROD AS COD_ITEM,
            EST_PROD_CPL.TP_PROD AS TIPO_ITEM,
            V_EST_NF_ENT.DS_PROD, -- 
            V_EST_NF_ENT.QT_IT,
            V_EST_NF_ENT.VLR_IT,
            V_EST_NF_ENT.VLR_TOT_IT AS VLR_ITEM,
            TRIB_DARE.CD_TRIB_FC AS CST_ICMS,
            V_EST_NF_ENT.TRIB_FC_ELETRONICO,
            GLB_NT_OP.CD_NT_OP_FC AS CFOP,
            CASE
                WHEN TRIB_DARE.ICMS > 0
                THEN EST_NF_ENT_DAR_IT.VLR_BASE_ICMS
                ELSE 0
            END AS VLR_BASE_ICMS,
            CASE
                WHEN TRIB_DARE.ICMS > 0
                THEN EST_NF_ENT_DAR_IT.PERC_ICMS
                ELSE 0
            END ALIQ_ICMS,
            CASE
                WHEN TRIB_DARE.ICMS > 0
                THEN EST_NF_ENT_DAR_IT.VLR_ICMS
                ELSE 0
            END AS VLR_ICMS_IT,
            EST_NF_ENT_DAR_IT.VLR_BASE_SUBS AS VLR_BASE_SUBS,
            EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS AS VLR_ICMS_SUBS,
            V_EST_NF_ENT.VLR_IPI_IT,
            V_EST_NF_ENT.PERC_IPI,
            V_EST_NF_ENT.VLR_ISENTO_IT,
            ISNULL(V_EST_NF_ENT.CST_PIS, 0) CST_PIS,
            ISNULL(V_EST_NF_ENT.CST_COFINS, 0) CST_COFINS,
            ISNULL(V_EST_NF_ENT.VLR_BASE_PIS, 0) VLR_BASE_PIS,
            ISNULL(V_EST_NF_ENT.PERC_ALIQ_PIS, 0) PERC_ALIQ_PIS,
            ISNULL(V_EST_NF_ENT.VLR_PIS, 0) VLR_PIS,
            ISNULL(V_EST_NF_ENT.VLR_BASE_COFINS, 0) VLR_BASE_COFINS,
            ISNULL(V_EST_NF_ENT.PERC_ALIQ_COFINS, 0) PERC_ALIQ_COFINS,
            ISNULL(V_EST_NF_ENT.VLR_COFINS, 0) VLR_COFINS,
            V_EST_NF_ENT.VLR_OUTRAS_DESPESAS AS VLR_OUTRAS_DESPESAS_IT,
            0 AS VLR_BASE_ISSQN,
            0 AS VLR_ISSQN,
            0 AS ALIQ_ISSQN,
            V_EST_NF_ENT.CD_ORIG AS COD_PART,
            1 AS COD_IMP_ALMOX,
            V_EST_NF_ENT.LISTA_PROD,
            TRIB_DARE.SUBS_TRIB,
            TRIB_DARE.SUBS_TRIB_ANT,
            EST_NF_ENT_DAR_IT.CD_NT_OP,
            TRIB_DARE.ICMS,
            TRIB_DARE.ICMS_RD,
            FLAG_OPERADOR_LOG,
            ISNULL(
                  (
                      SELECT SUM(EST_NF_ENT_IT_EST_PROD_CPL_LOTE.QTDE_PROD) QTDE_PROD
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS QTDE_PROD,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.NR_LOTE
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                      ORDER BY EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS NR_LOTE,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.DT_VALID
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS DT_VALID,
            ISNULL(
                  (
                      SELECT TOP (1) EST_PROD_CPL_LOTE.DT_FABR
                      FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE,
                           EST_PROD_CPL_LOTE
                      WHERE EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF = V_EST_NF_ENT.CD_NF
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD = V_EST_NF_ENT.CD_PROD
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
                            AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
                  ), 0) AS DT_FABR,
            EST_PROD_PRECO.VLR_TABELA,
            CASE
                WHEN CTR_PMC = 1
                THEN 1
                ELSE 0
            END AS PROD_MEDICAM,
            (CASE
                 WHEN DS_SIGLA_LINHA = 'S'
                 THEN 0   --' SIGLAS: E - ETICO,   S - SIMILAR, G - GENERICO |0- SIMILAR; 1- GEN?RICO; 2- ?TICO;
                 WHEN DS_SIGLA_LINHA = 'G'
                 THEN 1
                 WHEN DS_SIGLA_LINHA = 'E'
                 THEN 2
                 ELSE 0
             END) TIPO_MEDIC,
            ISNULL(V_EST_NF_ENT.NM_ORIG, '') AS NOME_PART,
            V_EST_NF_ENT.ENDE AS ENDERECO,
            V_EST_NF_ENT.BAIRRO,
            ISNULL(V_EST_NF_ENT.NUMERO, '') NUMERO,
            V_EST_NF_ENT.INSC_EST,
            V_EST_NF_ENT.UF,
            V_EST_NF_ENT.TEL,
            GLB_CID.CD_PAIS,
            ISNULL(GLB_MUNICIPIO.COD_MUNICIPIO_SINTEGRA, 0) AS COD_IBGE,
            ISNULL(V_EST_NF_ENT.CEP, '') CEP,
            EST_PROD_CPL.TP_PROD,
            ISNULL(
                  (
                      SELECT TOP (1) CD_BARRA
                      FROM EST_PROD_CD_BARRA
                      WHERE CD_EMP = V_EST_NF_ENT.CD_EMP
                            AND CD_PROD = V_EST_NF_ENT.CD_PROD
                      ORDER BY CD_BARRA
                  ), '') AS COD_BARRA,
            V_EST_NF_ENT.NR_NCM AS COD_NCM,
            V_EST_NF_ENT.VLR_DESC_IT,
            ISNULL(V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.PIS_NF, 0) PIS_NF,
            ISNULL(V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.COFINS_NF, 0) COFINS_NF,
            GLB_NT_OP.DS_NT_OP,
            V_EST_NF_ENT.CD_MOV_OP,
            V_EST_NF_ENT.FLAG_NAO_GERAR_ARQ_SPED,
            EST_NF_ENT_DAR_IT.CD_TRIB,
            ISNULL(EST_NF_ENT_CANCEL.CANCEL_FORA_PRAZO, 0) CANCEL_FORA_PRAZO,
            ISNULL(EST_NF_ENT_NFE_CANCEL.NR_PROTOCOLO, '') AS PROTOCOLO_CANCEL,
            1 AS POSSUI_DARE,
            EST_NF_ENT_DAR.NR_DAR AS NUM_DARE,
            EST_NF_ENT_DAR.VLR_DAR AS VALOR_DARE,
            EST_NF_ENT_DAR.DT_VENCTO AS DT_VENCTO_DARE,
            EST_NF_ENT_DAR.DT_RECOLHIMENTO AS DT_RECOLHIMENTO_DARE,
            V_EST_NF_ENT.FLAG_MOV_OP_NFE_CPL,
            ISNULL(EST_NF_ENT_DENEGADA.CD_EMP, 0) AS NOTA_DENEGADA,
            ISNULL(VLR_FRETE_CABE_RATEIO, 0) VLR_FRETE_CABE_RATEIO,
            ISNULL(EST_NF_SAI_NF_ENT.CD_NF_SAI, 0) CD_NF_SAI,
            V_APURA_ICMS.VLR_FECOP,
            ISNULL(NR_CEST, 0) AS NR_CEST,
            V_EST_NF_ENT.COD_IBGE_MUNICIPIO_DESTINATARIO,
            V_EST_NF_ENT.END_DESTINATARIO,
            V_EST_NF_ENT.BAIRRO_DESTINATARIO,
            V_EST_NF_ENT.CEP_DESTINATARIO,
            V_EST_NF_ENT.CIDADE_DESTINATARIO,
            V_EST_NF_ENT.CNPJ_DESTINATARIO,
            V_EST_NF_ENT.INSC_ESTADUAL_DESTINATARIO,
            V_EST_NF_ENT.RZ_DESTINATARIO,
            V_EST_NF_ENT.UF_DESTINATARIO,
            V_EST_NF_ENT.CD_CID_DESTINATARIO,
            V_EST_NF_ENT.ALIQ_ICMS_INTERNO,
            V_APURA_ICMS.VLR_ICMS_DESONERADO,
            V_EST_NF_ENT.FLAG_MICRO_EMPRESA AS PART_FLAG_MICRO_EMPRESA,
            V_APURA_ICMS.BASE_ICMS_SIMPLES,
            V_APURA_ICMS.ALIQ_SIMPLES,
            V_APURA_ICMS.VLR_ICMS_SIMPLES
     FROM V_EST_NF_ENT V_EST_NF_ENT
          INNER JOIN V_APURA_ICMS ON V_EST_NF_ENT.CD_EMP = V_APURA_ICMS.CD_EMP
                                     AND V_EST_NF_ENT.CD_FILIAL = V_APURA_ICMS.CD_FILIAL
                                     AND V_EST_NF_ENT.CD_NF = V_APURA_ICMS.CD_NF
                                     AND V_APURA_ICMS.TIPO = 0
          INNER JOIN V_EST_NF_ENT_CAB_DAR ON V_EST_NF_ENT.CD_EMP = V_EST_NF_ENT_CAB_DAR.CD_EMP
                                             AND V_EST_NF_ENT.CD_FILIAL = V_EST_NF_ENT_CAB_DAR.CD_FILIAL
                                             AND V_EST_NF_ENT.CD_NF = V_EST_NF_ENT_CAB_DAR.CD_NF
          INNER JOIN EST_NF_ENT_DAR_IT ON V_EST_NF_ENT.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP
                                          AND V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
                                          AND V_EST_NF_ENT.CD_NF = EST_NF_ENT_DAR_IT.CD_NF
                                          AND V_EST_NF_ENT.CD_PROD = EST_NF_ENT_DAR_IT.CD_PROD
          INNER JOIN EST_NF_ENT_DAR ON EST_NF_ENT_DAR.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP
                                       AND EST_NF_ENT_DAR.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
                                       AND EST_NF_ENT_DAR.CD_NF = EST_NF_ENT_DAR_IT.CD_NF
          INNER JOIN EST_TRIB AS TRIB_DARE ON EST_NF_ENT_DAR_IT.CD_TRIB = TRIB_DARE.CD_TRIB
          INNER JOIN GLB_NT_OP ON EST_NF_ENT_DAR_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
          INNER JOIN EST_PROD_CPL ON V_EST_NF_ENT.CD_EMP = EST_PROD_CPL.CD_EMP
                                     AND V_EST_NF_ENT.CD_FILIAL = EST_PROD_CPL.CD_FILIAL
                                     AND V_EST_NF_ENT.CD_PROD = EST_PROD_CPL.CD_PROD
          LEFT JOIN EST_NF_SAI_NF_ENT ON V_EST_NF_ENT.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
                                         AND V_EST_NF_ENT.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
                                         AND V_EST_NF_ENT.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_ENT
          LEFT JOIN EST_PROD_PRECO ON EST_PROD_PRECO.CD_EMP = V_EST_NF_ENT.CD_EMP
                                      AND EST_PROD_PRECO.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                      AND EST_PROD_PRECO.CD_PROD = V_EST_NF_ENT.CD_PROD
          LEFT JOIN EST_NF_ENT_EF_NF_CTR_NF ON EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = V_EST_NF_ENT.CD_EMP
                                               AND EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                               AND EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT = V_EST_NF_ENT.CD_NF
          LEFT JOIN GLB_CID ON V_EST_NF_ENT.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_CID_GLB_MUNICIPIO ON GLB_CID_GLB_MUNICIPIO.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_MUNICIPIO ON GLB_MUNICIPIO.CD_MUNICIPIO = GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO
          LEFT JOIN V_EST_NF_ENT_IMPOSTOS_PIS_COFINS ON V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_EMP = V_EST_NF_ENT.CD_EMP
                                                        AND V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                                        AND V_EST_NF_ENT_IMPOSTOS_PIS_COFINS.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN V_EST_NF_ENT_NFE_SPED ON V_EST_NF_ENT_NFE_SPED.CD_EMP = V_EST_NF_ENT.CD_EMP
                                             AND V_EST_NF_ENT_NFE_SPED.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                             AND V_EST_NF_ENT_NFE_SPED.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN EST_NF_ENT_CANCEL ON EST_NF_ENT_CANCEL.CD_EMP = V_EST_NF_ENT.CD_EMP
                                         AND EST_NF_ENT_CANCEL.CD_FILIAL = V_EST_NF_ENT.CD_FILIAL
                                         AND EST_NF_ENT_CANCEL.CD_NF = V_EST_NF_ENT.CD_NF
          LEFT JOIN EST_NF_ENT_NFE_CANCEL ON EST_NF_ENT_CANCEL.CD_EMP = EST_NF_ENT_NFE_CANCEL.CD_EMP
                                             AND EST_NF_ENT_CANCEL.CD_FILIAL = EST_NF_ENT_NFE_CANCEL.CD_FILIAL
                                             AND EST_NF_ENT_CANCEL.CD_NF = EST_NF_ENT_NFE_CANCEL.CD_NF
          LEFT JOIN EST_NF_ENT_DENEGADA ON V_EST_NF_ENT.CD_EMP = EST_NF_ENT_DENEGADA.CD_EMP
                                           AND V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DENEGADA.CD_FILIAL
                                           AND V_EST_NF_ENT.CD_NF = EST_NF_ENT_DENEGADA.CD_NF;
		
