cof_rv_recursivebom

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

planomestre

numeric

√

null

ad_org_id

numeric

√

null

ad_client_id

numeric

√

null

created

timestamp

29,6

√

null

createdby

numeric

√

null

isactive

bpchar

√

null

updated

timestamp

29,6

√

null

updatedby

numeric

√

null

produtoconsultado

numeric

√

null

nome

varchar

255

√

null

descricao

varchar

255

√

null

produzido

bpchar

√

null

codigo

varchar

510

√

null

previsaouso

numeric

√

null

qtddisponivel

numeric

√

null

material

numeric

√

null

qtd_outros_planos

numeric

√

null

saldo

numeric

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH RECURSIVE bom_recursivo AS (
         SELECT pmp.cof_pp_planomestreprod_id AS planomestre,
            pmp.ad_client_id,
            pmp.ad_org_id,
            pmp.created,
            pmp.createdby,
            pmp.isactive,
            pmp.updated,
            pmp.updatedby,
            bomnivel0.m_product_id AS produtoconsultado,
            bomnivel0.m_product_bom_id AS bomid,
            bomnivel0.m_product_id AS produtopai,
            bomnivel0.m_productbom_id AS material,
            (pmp.cof_pp_quantidadeplanejada - pmp.cof_pp_qtdproduzida) AS qtdrestante,
            bomnivel0.bomqty AS quantidadeldm,
            ((pmp.cof_pp_quantidadeplanejada - pmp.cof_pp_qtdproduzida) * bomnivel0.bomqty) AS previsaouso,
            ( SELECT sum(m_product_stock_v.qtyonhand) AS sum
                   FROM m_product_stock_v
                  WHERE (m_product_stock_v.m_product_id = bomnivel0.m_productbom_id)) AS qtddisponivel,
            1 AS nivel,
            produto.name AS nome,
            produto.description AS descricao,
            produto.ismanufactured AS produzido,
            produto.value AS codigo
           FROM ((m_product_bom_old bomnivel0
             LEFT JOIN m_product produto ON ((produto.m_product_id = bomnivel0.m_productbom_id)))
             RIGHT JOIN cof_pp_planomestreprod pmp ON ((pmp.m_product_id = bomnivel0.m_product_id)))
        UNION
         SELECT bomr.planomestre,
            bomr.ad_client_id,
            bomr.ad_org_id,
            bomr.created,
            bomr.createdby,
            bomr.isactive,
            bomr.updated,
            bomr.updatedby,
            bomr.produtoconsultado,
            bomall.m_product_bom_id AS bomid,
            bomr.material AS produtopai,
            bomall.m_productbom_id AS material,
            bomr.qtdrestante,
            bomall.bomqty AS quantidadeldm,
            (bomall.bomqty * bomr.previsaouso) AS previsaouso,
            ( SELECT sum(m_product_stock_v.qtyonhand) AS sum
                   FROM m_product_stock_v
                  WHERE (m_product_stock_v.m_product_id = bomall.m_productbom_id)) AS qtddisponivel,
            (bomr.nivel + 1),
            produto.name AS nome,
            produto.description AS descricao,
            produto.ismanufactured AS produzido,
            produto.value AS codigo
           FROM ((bom_recursivo bomr
             LEFT JOIN m_product_bom_old bomall ON ((bomall.m_product_id = bomr.material)))
             LEFT JOIN m_product produto ON ((produto.m_product_id = bomall.m_productbom_id)))
          WHERE ((bomr.material IS NOT NULL) AND (bomall.m_product_bom_id IS NOT NULL) AND (bomr.nivel < 5))
        )
 SELECT planomestre,
    ad_org_id,
    ad_client_id,
    created,
    createdby,
    isactive,
    updated,
    updatedby,
    produtoconsultado,
    nome,
    descricao,
    produzido,
    codigo,
        CASE
            WHEN (sum(previsaouso) < (0)::numeric) THEN (0)::numeric
            ELSE sum(previsaouso)
        END AS previsaouso,
    sum(qtddisponivel) AS qtddisponivel,
    material,
    ( SELECT (sum(((pmp.cof_pp_quantidadeplanejada - pmp.cof_pp_qtdproduzida) * bom.bomqty)) - bom_recursivo.previsaouso) AS outros_pmp
           FROM ((cof_pp_planomestreprod pmp
             JOIN m_product product ON ((pmp.m_product_id = product.m_product_id)))
             JOIN m_product_bom_old bom ON ((bom.m_product_id = product.m_product_id)))
          WHERE ((bom.m_productbom_id = bom_recursivo.material) AND (((pmp.docstatus)::text = 'IP'::text) OR (pmp.cof_pp_planomestreprod_id = bom_recursivo.planomestre)))) AS qtd_outros_planos,
    ((COALESCE(qtddisponivel, (0)::numeric) - COALESCE(previsaouso, (0)::numeric)) - COALESCE(( SELECT (sum(((pmp.cof_pp_quantidadeplanejada - pmp.cof_pp_qtdproduzida) * bom.bomqty)) - bom_recursivo.previsaouso) AS outros_pmp
           FROM ((cof_pp_planomestreprod pmp
             JOIN m_product product ON ((pmp.m_product_id = product.m_product_id)))
             JOIN m_product_bom_old bom ON ((bom.m_product_id = product.m_product_id)))
          WHERE ((bom.m_productbom_id = bom_recursivo.material) AND (((pmp.docstatus)::text = 'IP'::text) OR (pmp.cof_pp_planomestreprod_id = bom_recursivo.planomestre)))), (0)::numeric)) AS saldo
   FROM bom_recursivo
  GROUP BY nome, descricao, produzido, codigo, previsaouso, qtddisponivel, produtoconsultado, planomestre, created, createdby, updated, updatedby, isactive, ad_org_id, ad_client_id, material;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views