Skip to content

Instantly share code, notes, and snippets.

@dorcilio

dorcilio/Script_Produtos_Calculo_Pauta.sql

Created December 7, 2017 12:30
Show Gist options
  • Save dorcilio/ece69a896f0bc9527fc2c8286704e8a8 to your computer and use it in GitHub Desktop.
Save dorcilio/ece69a896f0bc9527fc2c8286704e8a8 to your computer and use it in GitHub Desktop.
Download ZIP
Gist criado pelo Formatador-Planos para compartilhamento de Query SQL.
Raw
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 = ?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment