Skip to content

Instantly share code, notes, and snippets.

@dorcilio

dorcilio/andre.sql

Last active November 8, 2017 14:08
Show Gist options
  • Save dorcilio/20adb6d57b24b5ce63d0f727293f8f02 to your computer and use it in GitHub Desktop.
Save dorcilio/20adb6d57b24b5ce63d0f727293f8f02 to your computer and use it in GitHub Desktop.
Download ZIP
Script Notas Fornecedor Referenciação
Raw
andre.sql
SELECT
a.cnpj AS empresa_cnpj,
e.empresa_cod_item,
e.empresa_descricao,
e.empresa_operacao,
b.cpf_cnpj AS fornecedor_cnpj,
c.c_prod AS fornecedor_cod_item,
c.x_prod AS fornecedor_descricao,
c.v_prod AS fornecedor_vl_item,
c.ncm AS fornecedor_ncm,
c.cfop AS fornecedor_cfop,
d.operacao AS fornecedor_operacao,
COALESCE(e.ID :: BOOLEAN, FALSE) AS referenciado,
CASE WHEN e.convenio IS NULL THEN FALSE ELSE TRUE END AS conveniado,
e.substituido--,
--CASE ? :: INTEGER WHEN 0 THEN COUNT(*) OVER(PARTITION BY a.id_reg_c100) ELSE NULL END AS qtd_rows
FROM
(
SELECT
a.id_reg_c100,
a.num_doc,
a.chv_nfe,
b.cpf_cnpj,
a.cod_mod,
a.ser :: INTEGER,
c.cnpj
FROM
sf.reg_c100 AS a
JOIN sf.reg_0150 AS b USING (cod_part, id_reg_0000)
JOIN sf.reg_0000 AS c USING (id_reg_0000)
WHERE
a.ind_emit = '1'
AND a.ser SIMILAR TO '[0-9]+'
AND a.cod_mod = '55'
AND a.id_reg_c100 = 136069621
) AS a
JOIN nfe.raiz_nfe AS b ON (a.cpf_cnpj, a.num_doc, a.ser, a.cod_mod) = (b.cpf_cnpj, b.n_nf, b.serie, b.MOD)
JOIN nfe.i01_prod AS c USING (id_nfe)
JOIN ncm_helper.cad_cfop AS d USING (cfop)
LEFT JOIN (
SELECT
DISTINCT c.id,
b.empresa_cod_item,
b.empresa_descricao,
b.empresa_operacao,
b.convenio,
b.substituido
FROM
(
SELECT
DISTINCT a.id_nfe,
b.cpf_cnpj,
b.cnpj,
b.cod_item,
b.descr_item,
b.operacao
FROM
nfe.raiz_nfe AS a
JOIN (
SELECT
b.cpf_cnpj,
c.num_doc,
c.chv_nfe,
c.cod_mod,
c.ser :: INTEGER,
a.cnpj,
d.cod_item,
e.descr_item,
f.operacao,
c.id_reg_c100
FROM
sf.reg_0000 AS a
JOIN sf.reg_0150 AS b USING (id_reg_0000)
JOIN sf.reg_c100 AS c USING (cod_part, id_reg_0000)
JOIN sf.reg_c170 AS d ON c.id_reg_c100 = d.id_reg_c100
JOIN sf.reg_0200 AS e ON (d.cod_item, e.id_reg_0000) = (e.cod_item, d.id_reg_0000)
JOIN ncm_helper.cad_cfop AS f USING (cfop)
WHERE
c.ind_emit = '1'
AND c.ser SIMILAR TO '[0-9]+'
AND c.cod_mod = '55'
AND c.id_reg_c100 = 136069621
) AS b ON (a.cpf_cnpj, a.n_nf, a.serie, a.MOD) = (b.cpf_cnpj, b.num_doc, b.ser, b.cod_mod)
) AS a
LEFT JOIN ncm_helper.produtos_referenciados AS b ON (a.cpf_cnpj, a.cnpj, a.cod_item, a.operacao) = (
b.fornecedor_cnpj,
b.empresa_cnpj,
b.empresa_cod_item,
b.empresa_operacao
)
LEFT JOIN nfe.i01_prod AS c ON (
a.id_nfe,
b.fornecedor_cod_item,
b.fornecedor_descricao
) = (c.id_nfe, c.c_prod, c.x_prod)
LEFT JOIN ncm_helper.cad_cfop AS d ON (b.fornecedor_operacao, c.cfop) = (d.operacao, d.cfop)
) AS e USING (ID)
Raw
cidor.sql
SELECT
a.cnpj AS empresa_cnpj,
e.empresa_cod_item,
e.empresa_descricao,
e.empresa_operacao,
b.cpf_cnpj AS fornecedor_cnpj,
c.c_prod AS fornecedor_cod_item,
c.x_prod AS fornecedor_descricao,
c.v_prod AS fornecedor_vl_item,
c.ncm AS fornecedor_ncm,
c.cfop AS fornecedor_cfop,
d.operacao AS fornecedor_operacao,
COALESCE(e.ID :: BOOLEAN, FALSE) AS referenciado,
CASE WHEN e.convenio IS NULL THEN FALSE ELSE TRUE END AS conveniado,
e.substituido--,
--CASE ?::INTEGER WHEN 0 THEN COUNT(*) OVER(PARTITION BY a.id_reg_c100) ELSE NULL END AS qtd_rows
FROM
(
SELECT
a.id_reg_c100,
a.num_doc,
a.chv_nfe,
b.cpf_cnpj,
a.cod_mod,
a.ser :: INTEGER,
c.cnpj
FROM
sf.reg_c100 AS a
JOIN sf.reg_0150 AS b USING (cod_part, id_reg_0000)
JOIN sf.reg_0000 AS c USING (id_reg_0000)
WHERE
a.ind_emit = '1'
AND a.ser SIMILAR TO '[0-9]+'
AND a.cod_mod = '55'
AND a.id_reg_c100 = 136069659
) AS a
JOIN nfe.raiz_nfe AS b ON (a.cpf_cnpj, a.num_doc, a.ser, a.cod_mod) = (b.cpf_cnpj, b.n_nf, b.serie, b.MOD)
JOIN nfe.i01_prod AS c USING (id_nfe)
JOIN ncm_helper.cad_cfop AS d USING (cfop)
LEFT JOIN (
SELECT
DISTINCT c.id,
e.empresa_cod_item,
e.empresa_descricao,
e.empresa_operacao,
e.convenio,
e.substituido
FROM
(
SELECT
DISTINCT a.id_nfe,
b.cpf_cnpj,
b.cnpj,
b.cod_item,
b.descr_item,
b.operacao
FROM
nfe.raiz_nfe AS a
JOIN (
SELECT
b.cpf_cnpj,
c.num_doc,
c.chv_nfe,
c.cod_mod,
c.ser :: INTEGER,
a.cnpj,
d.cod_item,
e.descr_item,
f.operacao,
c.id_reg_c100
FROM
sf.reg_0000 AS a
JOIN sf.reg_0150 AS b USING (id_reg_0000)
JOIN sf.reg_c100 AS c USING (cod_part, id_reg_0000)
JOIN sf.reg_c170 AS d ON c.id_reg_c100 = d.id_reg_c100
JOIN sf.reg_0200 AS e ON (d.cod_item, e.id_reg_0000) = (e.cod_item, d.id_reg_0000)
JOIN ncm_helper.cad_cfop AS f USING (cfop)
WHERE
c.ind_emit = '1'
AND c.ser SIMILAR TO '[0-9]+'
AND c.cod_mod = '55'
AND c.id_reg_c100 = 136069659
) AS b ON (a.cpf_cnpj, a.n_nf, a.serie, a.MOD) = (b.cpf_cnpj, b.num_doc, b.ser, b.cod_mod)
) AS a
LEFT JOIN nfe.i01_prod AS c ON a.id_nfe = c.id_nfe
LEFT JOIN ncm_helper.cad_cfop AS d ON c.cfop = d.cfop
LEFT JOIN ncm_helper.produtos_referenciados AS e ON (a.cpf_cnpj, c.c_prod, d.operacao, c.x_prod, a.cnpj, a.cod_item, a.operacao, a.descr_item) = (
e.fornecedor_cnpj,
e.fornecedor_cod_item,
e.fornecedor_operacao,
e.fornecedor_descricao,
e.empresa_cnpj,
e.empresa_cod_item,
e.empresa_operacao,
e.empresa_descricao
)
) AS e USING (id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment