Gist criado pelo Formatador-Planos para compartilhamento de Query SQL.

Script_Produtos_Calculo_Pauta.sql

SELECT
    a.id_nfe,
    b.cpf_cnpj,
    d.n_item,
    e.ID AS id_produto,
    e.c_prod,
    e.x_prod,
    e.v_prod,
    e.q_com,
    COALESCE(e.v_frete, 0) :: NUMERIC(18, 2) AS v_frete,
    COALESCE(e.v_seg, 0) :: NUMERIC(18, 2) AS v_seg,
    COALESCE(e.v_desc, 0) :: NUMERIC(18, 2) AS v_desc,
    h.p_icms,
    h.v_icms,
    COALESCE(j.v_ipi, 0) :: NUMERIC(18, 2) AS v_ipi,
    COALESCE((k.v_outro / k.v_prod) * e.v_prod, 0) :: NUMERIC(15, 2) AS v_outro,
    COALESCE(m.valor, 0) :: NUMERIC(18, 2) AS v_pauta,
    CASE WHEN l.id_pauta IS NULL THEN FALSE ELSE TRUE END AS referenciado
FROM
    nfe.b01_ide a
    JOIN nfe.c01_emit b USING(id_nfe)
    JOIN nfe.h01_det d USING(id_nfe)
    JOIN nfe.i01_prod e ON d.ID = e.id_det
    AND a.id_nfe = e.id_nfe
    JOIN nfe.m01_imposto f ON a.id_nfe = f.id_nfe
    AND d.ID = f.id_det
    JOIN nfe.n01_icms g ON a.id_nfe = g.id_nfe
    AND f.ID = g.id_imposto
    JOIN nfe.icms_filhos h ON a.id_nfe = h.id_nfe
    AND g.ID = h.id_icms
    LEFT JOIN nfe.o01_ipi i ON a.id_nfe = e.id_nfe
    AND f.ID = i.id_imposto
    LEFT JOIN nfe.ipi_filhos j ON a.id_nfe = j.id_nfe
    AND i.ID = j.id_ipi
    LEFT JOIN nfe.w02_icms_tot k ON a.id_nfe = k.id_nfe
    LEFT JOIN icms.produtos_pauta_has_xml l ON b.cpf_cnpj = l.cpf_cnpj
    AND e.c_prod = l.c_prod
    LEFT JOIN icms.cad_pautas m USING(id_pauta)
WHERE
    a.id_nfe = ?