-- Nota Fiscal de Saída Eletrônica - DANFE
-- Dados
SELECT  nfs.id_nota_fiscal_saida,
    CAST(nfs.numero AS TEXT) AS numero,
    nfs.id_empresa,
    mdf.serie AS serie_modelo_doc_fiscal,
    mdf.tipo AS tipo_modelo,
    CAST('1' AS TEXT) AS entrada_saida,
        REGEXP_REPLACE(SUBSTRING(REGEXP_REPLACE(nfs.chave_acesso_nfe, '[^0-9]', '', 'g'), 0, 37), '(....)(....)(....)(....)(....)(....)(....)(....)(....)', '\1 \2 \3 \4 \5 \6 \7 \8 \9 ') || 
        REGEXP_REPLACE(SUBSTRING(REGEXP_REPLACE(nfs.chave_acesso_nfe, '[^0-9]', '', 'g'), 37, 8), '(....)(....)', '\1 \2') AS chave_acesso_nfe,
    ufe.codigo_ibge AS codigo_uf_empresa,
    nfs.codigo_numerico_nfe,
    nop.denominacao AS natureza_op,
    CASE WHEN (SELECT COUNT(*) FROM parcela_nfs WHERE id_nota_fiscal_saida = nfs.id_nota_fiscal_saida) > 0 THEN 1 ELSE 2 END AS indicador_forma_pagamento,
    mdf.codigo_modelo_documento AS codigo_modelo_doc_fiscal,
    nfs.emissao,
    nfs.hora_saida,
    nfs.saida AS saida,
    me.codigo_ibge AS codigo_ibge_empresa,
    mdf.formato_impressao_danfe,
        CASE WHEN (pe.natureza = 1) THEN REGEXP_REPLACE(pe.cnpj_cpf, '^([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{2})$', '\1.\2.\3-\4')
        WHEN (pe.natureza = 2) THEN REGEXP_REPLACE(pe.cnpj_cpf, '^([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{2})$', '\1.\2.\3/\4-\5') END AS cnpj_cpf_empresa,
    nfs.processo_emissao_nfe,
    e.logotipo,
    pe.nome AS nome_empresa,
    pe.fantasia_codinome AS fantasia_empresa,
    COALESCE(tlepe.denominacao, '') || ' ' || COALESCE(epe.logradouro, '') AS logradouro_empresa,
    COALESCE(epe.numero, '') AS nro_empresa,
    COALESCE(epe.complemento, '') AS complemento_empresa,
    COALESCE(epe.bairro, '') AS bairro_empresa,
    COALESCE(mee.codigo_ibge, 0) AS codigo_ibge_ender_empresa,
    COALESCE(mee.nome, '') AS municipio_ender_empresa,
    COALESCE(ufee.sigla, '') AS uf_ender_empresa,
    COALESCE(epe.cep, '') AS cep_empresa,
    COALESCE(epe.telefone, '') AS telefone_empresa,
    COALESCE(epe.fax, '') AS fax_empresa,
    pe.email_nfe AS email_empresa,
    pe.inscricao_municipal AS inscricao_municipal_empresa,
    COALESCE(cf.codigo, '') AS cnae_empresa,
    COALESCE(iee.inscricao_estadual, pe.inscricao_estadual)AS inscricao_estadual_empresa,
    pe.regime_tributario AS regime_tributario_empresa,
        CASE WHEN (pc.natureza = 1) THEN REGEXP_REPLACE(pc.cnpj_cpf, '^([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{2})$', '\1.\2.\3-\4')
        WHEN (pc.natureza = 2) THEN REGEXP_REPLACE(pc.cnpj_cpf, '^([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{2})$', '\1.\2.\3/\4-\5') END AS cnpj_cpf_cliente,
    pc.nome AS nome_cliente,
    COALESCE(epc.numero, '') AS nro_cliente,
    COALESCE(epc.complemento, '') AS complemento_cliente,
    COALESCE(epc.bairro, '') AS bairro_cliente,
    COALESCE(pc.email_nfe, '') AS email_cliente,
    COALESCE(tlepc.denominacao, '') || ' ' || COALESCE(epc.logradouro, '') AS logradouro_cliente,
    COALESCE(mec.codigo_ibge, 0) AS codigo_ibge_cliente,
    COALESCE(mec.nome, '') AS municipio_cliente,
    COALESCE(ufec.sigla, '') AS uf_cliente,
    COALESCE(epc.cep, '') AS cep_cliente,
    COALESCE(epc.telefone, '') AS telefone_cliente,
    pc.inscricao_estadual AS inscricao_estadual_cliente,
    nfs.informacao_adicional_fiscal,
    REGEXP_REPLACE(SUBSTRING((CASE WHEN nfs.informacao_complementar_contrib != '' OR nfs.informacao_complementar_contrib IS NOT NULL THEN  nfs.informacao_complementar_contrib ELSE '' END) || 
   (CASE WHEN (nfs.observacao != '' OR nfs.observacao IS NOT NULL) THEN ((CASE WHEN nfs.informacao_complementar_contrib != '' THEN E'\n' ELSE '' END) || nfs.observacao) ELSE '' END) || (CASE WHEN (EXISTS (SELECT 1 FROM mensagem_nfs WHERE id_nota_fiscal_saida = nfs.id_nota_fiscal_saida)) THEN (CASE WHEN (nfs.informacao_complementar_contrib || (CASE WHEN (nfs.observacao != '') THEN ((CASE WHEN nfs.informacao_complementar_contrib != '' THEN E'\n' ELSE '' END) || nfs.observacao) ELSE '' END)) != '' THEN E'\n' ELSE '' END ||(SELECT ARRAY_TO_STRING(ARRAY_AGG(mf.mensagem), ' ') AS mensagem FROM mensagem_nfs AS mnfs INNER JOIN mensagem_fiscal AS mf ON (mf.id_mensagem_fiscal = mnfs.id_mensagem_fiscal) WHERE mnfs.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida) ) ELSE '' END), 0, 5000), E'[\\n\\r]+', ' / ', 'g' ) AS observacao,
    nfs.base_icms_substituicao,
    nfs.icms_substituicao,
    (nfs.total_produto +    COALESCE((SELECT SUM(infs.desconto) FROM item_nfs AS infs
                                INNER JOIN item AS i ON (i.id_item = infs.id_item)
                            WHERE infs.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida
                            AND i.tipo_item = 1), 0)
                        -   COALESCE((SELECT SUM(infs.acrescimo) FROM item_nfs AS infs
                                INNER JOIN item AS i ON (i.id_item = infs.id_item)
                            WHERE infs.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida
                            AND i.tipo_item = 1), 0)) AS total_produto,
    nfs.total_servico,
    nfs.frete,
    nfs.seguro,
    nfs.despesa_acessoria,
    nfs.ipi,
    nfs.total_nota,
    COALESCE((SELECT SUM(i.base_icms_reduzida) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS bc_icms,
    COALESCE((SELECT SUM(i.icms) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS icms,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS desconto,
    COALESCE((SELECT SUM(i.acrescimo) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS acrescimo,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfs AS i INNER JOIN item AS it ON it.id_item = i.id_item WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND it.tipo_item = 2), 0) AS desconto_issqn,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfs AS i INNER JOIN item AS it ON it.id_item = i.id_item WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND it.tipo_item = 1), 0) AS desconto_icms,
    COALESCE((SELECT SUM(i.pis) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS pis,
    COALESCE((SELECT SUM(i.cofins) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 1)), 0) AS cofins,
    COALESCE((SELECT SUM(i.base_iss) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS base_iss,
    COALESCE((SELECT SUM(i.iss) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 2)), 0) AS iss,
    COALESCE((SELECT SUM(i.pis) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 2)), 0) AS pis_iss,
    COALESCE((SELECT SUM(i.cofins) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 2)), 0) AS cofins_iss,
    COALESCE((SELECT SUM(i.fcp) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS fcp,
    COALESCE((SELECT SUM(i.base_fcp) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS base_fcp,
    COALESCE((SELECT SUM(i.aliquota_fcp) FROM item_nfs AS i WHERE i.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida), 0) AS aliquota_fcp,
    0 AS fcp_st_retido,
    0 AS aliquota_fcp_st_retido,
    0 AS base_fcp_st_retido,
    nfs.situacao,
    nfs.numero_lote_nfe,
    nfs.numero_protocolo_nfe,
    nfs.data_autorizacao_nfe,
    solv.titulo AS tipo_modalidade_frete,
    pvt.nome AS trasnportadora,
    pvt.cnpj_cpf AS cnpj_cpf_transportadora,
    COALESCE(pvt.inscricao_estadual, '') AS inscricao_estadual_transportadora,
    COALESCE(tlepvt.denominacao, '') || ' ' || COALESCE(epvt.logradouro, '') || ', ' || COALESCE(epvt.numero, '') || ' - ' || COALESCE(epvt.bairro, '') AS endereco_transportadora,
    mvt.nome AS municipio_transportadora,
    ufvt.sigla AS uf_transportadora,
    vt.placa AS placa_transportadora,
    ufvtt.sigla AS uf_veiculo_transportadora,
    pvt.rntrc,
    nfs.volumes_transportados,
    nfs.peso_liquido,
    nfs.peso_bruto
FROM    nota_fiscal_saida AS nfs
    INNER JOIN modelo_documento_fiscal AS mdf ON (mdf.id_modelo_documento_fiscal = nfs.id_modelo_documento_fiscal)
    INNER JOIN natureza_operacao AS nop ON (nop.id_natureza_operacao = nfs.id_natureza_operacao)
    INNER JOIN sis_empresa AS e ON (e.id_empresa = nfs.id_empresa)
    INNER JOIN pessoa AS pe ON (pe.id_pessoa = (SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', e.id_empresa, 0, 'S')))
    INNER JOIN municipio AS me ON (me.id_municipio = pe.id_municipio)
    INNER JOIN uf AS ufe ON (ufe.id_uf = me.id_uf)
    LEFT OUTER JOIN endereco_pessoa AS epe ON (epe.id_pessoa = pe.id_pessoa AND epe.id_tipo_endereco = 1 AND epe.registro_ativo = 'S')
    LEFT OUTER JOIN tipo_logradouro AS tlepe ON (tlepe.id_tipo_logradouro = epe.id_tipo_logradouro)
    LEFT OUTER JOIN municipio AS mee ON (mee.id_municipio = epe.id_municipio)
    LEFT OUTER JOIN uf AS ufee ON (ufee.id_uf = mee.id_uf)
    LEFT JOIN codigo_fiscal_pessoa cfp ON (cfp.id_pessoa = pe.id_pessoa AND cfp.principal = 'S' AND CURRENT_DATE BETWEEN cfp.inicio_validade AND COALESCE(cfp.fim_validade, CURRENT_DATE))
    LEFT JOIN codigo_fiscal cf ON (cf.id_codigo_fiscal = cfp.id_codigo_fiscal)    
    INNER JOIN pessoa AS pc ON (pc.id_pessoa = nfs.id_cliente)
    LEFT OUTER JOIN endereco_pessoa AS epc ON (epc.id_pessoa = pc.id_pessoa AND epc.id_tipo_endereco = 1 AND epc.registro_ativo = 'S')
    LEFT OUTER JOIN tipo_logradouro AS tlepc ON (tlepc.id_tipo_logradouro = epc.id_tipo_logradouro)
    LEFT OUTER JOIN municipio AS mec ON (mec.id_municipio = epc.id_municipio)
    LEFT OUTER JOIN uf AS ufec ON (ufec.id_uf = mec.id_uf)
    LEFT OUTER JOIN veiculo_transportadora AS vc ON (vc.id_veiculo_transportadora = nfs.id_veiculo_transportadora)
    LEFT OUTER JOIN veiculo_transportadora AS vt ON (vt.id_veiculo_transportadora = nfs.id_veiculo_transportadora)
    LEFT OUTER JOIN pessoa AS pvt ON (pvt.id_pessoa = vt.id_proprietario_veiculo)
    LEFT OUTER JOIN endereco_pessoa AS epvt ON (epvt.id_pessoa = pvt.id_pessoa AND epvt.id_tipo_endereco = 1 AND epvt.registro_ativo = 'S')
    LEFT OUTER JOIN tipo_logradouro AS tlepvt ON (tlepvt.id_tipo_logradouro = epvt.id_tipo_logradouro)
    LEFT OUTER JOIN municipio AS mvt ON (mvt.id_municipio = epvt.id_municipio)
    LEFT OUTER JOIN uf AS ufvt ON (ufvt.id_uf = mvt.id_uf)
    LEFT OUTER JOIN uf AS ufvtt ON (ufvtt.id_uf = vt.id_uf)
    LEFT JOIN inscricao_estadual_empresa iee ON (iee.id_empresa = nfs.id_empresa AND iee.id_uf = ufe.id_uf AND nfs.emissao BETWEEN iee.inicio_validade AND COALESCE(iee.fim_validade, CURRENT_DATE))
    INNER JOIN sis_opcao_lista_valor AS solv ON (solv.id = nfs.tipo_modalidade_frete AND solv.id_lista_valor = 'F19473A6C3DB9CE41C990EB9C21521FB')
WHERE   nfs.id_nota_fiscal_saida = :Id
AND EXISTS(SELECT 1 FROM item_nfs AS i1 INNER JOIN cfop AS c1 ON (c1.id_cfop = i1.id_cfop)
        WHERE id_nota_fiscal_saida = nfs.id_nota_fiscal_saida AND c1.entrada_saida = 2)

UNION ALL

SELECT  nfe.id_nota_fiscal_entrada,
    CAST(nfe.numero AS TEXT) AS numero,
    nfe.id_empresa,
    nfe.serie AS serie_modelo_doc_fiscal,
    mdf.tipo AS tipo_modelo,
    CAST('0' AS TEXT) AS entrada_saida,
        REGEXP_REPLACE(SUBSTRING(REGEXP_REPLACE(nfe.chave_acesso_nfe, '[^0-9]', '', 'g'), 0, 37), '(....)(....)(....)(....)(....)(....)(....)(....)(....)', '\1 \2 \3 \4 \5 \6 \7 \8 \9 ') || 
        REGEXP_REPLACE(SUBSTRING(REGEXP_REPLACE(nfe.chave_acesso_nfe, '[^0-9]', '', 'g'), 37, 8), '(....)(....)', '\1 \2') AS chave_acesso_nfe,
    ufe.codigo_ibge AS codigo_uf_empresa,
    nfe.codigo_numerico_nfe,
    nop.denominacao AS natureza_op,
    CASE WHEN (SELECT COUNT(*) FROM parcela_nfe WHERE id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada) > 0 THEN 1 ELSE 2 END AS indicador_forma_pagamento,
    mdf.codigo_modelo_documento AS codigo_modelo_doc_fiscal,
    nfe.emissao,
    nfe.hora_saida,
    nfe.entrada AS saida,
    me.codigo_ibge AS codigo_ibge_empresa,
    mdf.formato_impressao_danfe,
        CASE WHEN (pe.natureza = 1) THEN REGEXP_REPLACE(pe.cnpj_cpf, '^([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{2})$', '\1.\2.\3-\4')
        WHEN (pe.natureza = 2) THEN REGEXP_REPLACE(pe.cnpj_cpf, '^([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{2})$', '\1.\2.\3/\4-\5') END AS cnpj_cpf_empresa,
    nfe.processo_emissao_nfe,
    e.logotipo,
    pe.nome AS nome_empresa,
    pe.fantasia_codinome AS fantasia_empresa,
    COALESCE(tlepe.denominacao, '') || ' ' || COALESCE(epe.logradouro, '') AS logradouro_empresa,
    COALESCE(epe.numero, '') AS nro_empresa,
    COALESCE(epe.complemento, '') AS complemento_empresa,
    COALESCE(epe.bairro, '') AS bairro_empresa,
    COALESCE(mee.codigo_ibge, 0) AS codigo_ibge_ender_empresa,
    COALESCE(mee.nome, '') AS municipio_ender_empresa,
    COALESCE(ufee.sigla, '') AS uf_ender_empresa,
    COALESCE(epe.cep, '') AS cep_empresa,
    COALESCE(epe.telefone, '') AS telefone_empresa,
    COALESCE(epe.fax, '') AS fax_empresa,
    pe.email_nfe AS email_empresa,
    pe.inscricao_municipal AS inscricao_municipal_empresa,
    COALESCE(cf.codigo, '') AS cnae_empresa,
    COALESCE(iee.inscricao_estadual, pe.inscricao_estadual)AS inscricao_estadual_empresa,
    pe.regime_tributario AS regime_tributario_empresa,
        CASE WHEN (pf.natureza = 1) THEN REGEXP_REPLACE(pf.cnpj_cpf, '^([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{2})$', '\1.\2.\3-\4')
        WHEN (pf.natureza = 2) THEN REGEXP_REPLACE(pf.cnpj_cpf, '^([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{2})$', '\1.\2.\3/\4-\5') END AS cnpj_cpf_cliente,
    pf.nome AS nome_cliente,
    COALESCE(epf.numero, '') AS nro_cliente,
    COALESCE(epf.complemento, '') AS complemento_cliente,
    COALESCE(epf.bairro, '') AS bairro_cliente,
    COALESCE(pf.email_nfe, '') AS email_cliente,
    COALESCE(tlepf.denominacao, '') || ' ' || COALESCE(epf.logradouro, '') AS logradouro_cliente,
    COALESCE(mec.codigo_ibge, 0) AS codigo_ibge_cliente,
    COALESCE(mec.nome, '') AS municipio_cliente,
    COALESCE(ufec.sigla, '') AS uf_cliente,
    COALESCE(epf.cep, '') AS cep_cliente,
    COALESCE(epf.telefone, '') AS telefone_cliente,
    pf.inscricao_estadual AS inscricao_estadual_cliente,
    nfe.informacao_adicional_fiscal,
    REGEXP_REPLACE(SUBSTRING((CASE WHEN nfe.informacao_complementar_contrib != '' OR nfe.informacao_complementar_contrib IS NOT NULL THEN  nfe.informacao_complementar_contrib ELSE '' END) || 
   (CASE WHEN (nfe.observacao != '' OR nfe.observacao IS NOT NULL)THEN ((CASE WHEN nfe.informacao_complementar_contrib != '' THEN E'\n' ELSE '' END) || nfe.observacao) ELSE '' END) || (CASE WHEN (EXISTS (SELECT 1 FROM mensagem_nfs WHERE id_nota_fiscal_saida = nfe.id_nota_fiscal_entrada)) THEN (CASE WHEN (nfe.informacao_complementar_contrib || (CASE WHEN (nfe.observacao != '') THEN ((CASE WHEN nfe.informacao_complementar_contrib != '' THEN E'\n' ELSE '' END) || nfe.observacao) ELSE '' END)) != '' THEN E'\n' ELSE '' END ||(SELECT ARRAY_TO_STRING(ARRAY_AGG(mf.mensagem), ' ') AS mensagem FROM mensagem_nfs AS mnfs INNER JOIN mensagem_fiscal AS mf ON (mf.id_mensagem_fiscal = mnfs.id_mensagem_fiscal) WHERE mnfs.id_nota_fiscal_saida = nfe.id_nota_fiscal_entrada) ) ELSE '' END), 0, 5000), E'[\\n\\r]+', ' / ', 'g' ) AS observacao,
    nfe.base_icms_substituicao,
    nfe.icms_substituicao,
    (nfe.total_produto + COALESCE((SELECT SUM(infe.desconto) FROM item_nfe AS infe
            INNER JOIN item AS i ON (i.id_item = infe.id_item)
        WHERE infe.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada
        AND i.tipo_item = 1), 0)) AS total_produto,
    CAST(0 AS DECIMAL(12,2)) AS total_servico,
    nfe.frete,
    nfe.seguro,
    nfe.despesa_acessoria,
    nfe.ipi,
    nfe.total_nota,
    COALESCE((SELECT SUM(i.base_icms_reduzida) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS bc_icms,
    COALESCE((SELECT SUM(i.icms) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS icms,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS desconto,
    CAST(0 AS DECIMAL(12,2)) AS acrescimo,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfe AS i INNER JOIN item AS it ON it.id_item = i.id_item WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND it.tipo_item = 2), 0) AS desconto_issqn,
    COALESCE((SELECT SUM(i.desconto) FROM item_nfe AS i INNER JOIN item AS it ON it.id_item = i.id_item WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND it.tipo_item = 1), 0) AS desconto_icms,
    COALESCE((SELECT SUM(i.pis) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS pis,
    COALESCE((SELECT SUM(i.cofins) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 1)), 0) AS cofins,
    CAST(0 AS DECIMAL(12,2)) AS base_iss,
    COALESCE((SELECT SUM(i.pis) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 1)), 0) AS iss,
    COALESCE((SELECT SUM(i.pis) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 2)), 0) AS pis_iss,
    COALESCE((SELECT SUM(i.cofins) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND EXISTS (SELECT 1 FROM item WHERE id_item = i.id_item AND tipo_item = 2)), 0) AS cofins_iss,
    COALESCE((SELECT SUM(i.fcp) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS fcp,
    COALESCE((SELECT SUM(i.base_fcp) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS base_fcp,
    COALESCE((SELECT SUM(i.aliquota_fcp) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS aliquota_fcp,
    COALESCE((SELECT SUM(i.fcp_st_retido) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS fcp_st_retido,
    COALESCE((SELECT SUM(i.aliquota_fcp_st_retido) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS aliquota_fcp_st_retido,
    COALESCE((SELECT SUM(i.base_fcp_st_retido) FROM item_nfe AS i WHERE i.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada), 0) AS base_fcp_st_retido,
    nfe.situacao,
    nfe.numero_lote_nfe,
    nfe.numero_protocolo_nfe,
    nfe.data_autorizacao_nfe,
    NULL AS tipo_modalidade_frete,
    NULL AS trasnportadora,
    NULL AS cnpj_cpf_transportadora,
    NULL AS inscricao_estadual_transportadora,
    NULL AS endereco_transportadora,
    NULL AS municipio_transportadora,
    NULL AS uf_transportadora,
    NULL AS placa_transportadora,
    NULL AS uf_veiculo_transportadora,
    NULL AS rntc,
    NULL AS volumes_transportados,
    NULL AS peso_liquido,
    NULL AS peso_bruto
FROM    nota_fiscal_entrada AS nfe
    INNER JOIN modelo_documento_fiscal AS mdf ON (mdf.id_modelo_documento_fiscal = nfe.id_modelo_documento_fiscal)
    INNER JOIN natureza_operacao AS nop ON (nop.id_natureza_operacao = nfe.id_natureza_operacao)
    INNER JOIN sis_empresa AS e ON (e.id_empresa = nfe.id_empresa)
    INNER JOIN pessoa AS pe ON (pe.id_pessoa = (SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', e.id_empresa, 0, 'S')))
    INNER JOIN municipio AS me ON (me.id_municipio = pe.id_municipio)
    INNER JOIN uf AS ufe ON (ufe.id_uf = me.id_uf)
    LEFT OUTER JOIN endereco_pessoa AS epe ON (epe.id_pessoa = pe.id_pessoa AND epe.id_tipo_endereco = 1 AND epe.registro_ativo = 'S')
    LEFT OUTER JOIN tipo_logradouro AS tlepe ON (tlepe.id_tipo_logradouro = epe.id_tipo_logradouro)
    LEFT OUTER JOIN municipio AS mee ON (mee.id_municipio = epe.id_municipio)
    LEFT OUTER JOIN uf AS ufee ON (ufee.id_uf = mee.id_uf)
    LEFT JOIN codigo_fiscal_pessoa cfp ON (cfp.id_pessoa = pe.id_pessoa AND cfp.principal = 'S' AND CURRENT_DATE BETWEEN cfp.inicio_validade AND COALESCE(cfp.fim_validade, CURRENT_DATE))
    LEFT JOIN codigo_fiscal cf ON (cf.id_codigo_fiscal = cfp.id_codigo_fiscal)    
    INNER JOIN pessoa AS pf ON (pf.id_pessoa = nfe.id_fornecedor)
    LEFT OUTER JOIN endereco_pessoa AS epf ON (epf.id_pessoa = pf.id_pessoa AND epf.id_tipo_endereco = 1 AND epf.registro_ativo = 'S')
    LEFT OUTER JOIN tipo_logradouro AS tlepf ON (tlepf.id_tipo_logradouro = epf.id_tipo_logradouro)
    LEFT OUTER JOIN municipio AS mec ON (mec.id_municipio = epf.id_municipio)
    LEFT OUTER JOIN uf AS ufec ON (ufec.id_uf = mec.id_uf)
    LEFT JOIN inscricao_estadual_empresa iee ON (iee.id_empresa = nfe.id_empresa AND iee.id_uf = ufec.id_uf AND nfe.emissao BETWEEN iee.inicio_validade AND COALESCE(iee.fim_validade, CURRENT_DATE))
WHERE   nfe.id_nota_fiscal_entrada = :Id
AND EXISTS(SELECT 1 FROM item_nfe AS i1 INNER JOIN cfop AS c1 ON (c1.id_cfop = i1.id_cfop)
        WHERE id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada AND c1.entrada_saida = 1)