Script Notas Fornecedor Referenciação

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)

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)