m_inout_createfrom_v

Columns

Related columns Constraint Comments

Search:

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

qty

numeric

√

null

multiplier

numeric

√

null

c_uom_id

numeric

√

null

c_uom.c_uom_id

Implied Constraint

m_locator_id

numeric

√

null

m_product_id

numeric

√

null

c_charge_id

numeric

√

null

vendorproductno

varchar

2147483647

√

null

line

numeric

√

null

c_orderline_id

numeric

√

null

c_invoiceline_id

numeric

√

null

Showing 1 to 10 of 23 entries

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT
        CASE o.issotrx
            WHEN 'Y'::bpchar THEN (l.qtyordered - l.qtydelivered)
            ELSE ((l.qtyordered - sum(COALESCE(m.qty, (0)::numeric))) - COALESCE(( SELECT sum(iol.movementqty) AS sum
               FROM (m_inoutline iol
                 JOIN m_inout io ON ((iol.m_inout_id = io.m_inout_id)))
              WHERE ((l.c_orderline_id = iol.c_orderline_id) AND (io.processed = 'N'::bpchar))), (0)::numeric))
        END AS qty,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END AS multiplier,
    l.c_uom_id,
    p.m_locator_id,
    COALESCE(l.m_product_id, (0)::numeric) AS m_product_id,
    COALESCE(l.c_charge_id, (0)::numeric) AS c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    0 AS c_invoiceline_id,
    0 AS m_rmaline_id,
    l.c_bpartner_id,
    l.c_order_id,
    0 AS c_invoice_id,
    0 AS m_rma_id,
    l.c_orderline_id AS m_inout_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    p.upc,
    o.m_warehouse_id,
    o.issotrx,
    260 AS ad_table_id
   FROM ((((c_orderline l
     JOIN c_order o ON ((o.c_order_id = l.c_order_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (l.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN m_matchpo m ON (((l.c_orderline_id = m.c_orderline_id) AND (m.m_inoutline_id IS NOT NULL))))
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
  GROUP BY l.qtyordered, l.qtydelivered,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END, l.c_uom_id, p.m_locator_id, po.vendorproductno, l.m_product_id, l.c_charge_id, l.line, l.c_orderline_id, p.upc, o.m_warehouse_id, o.issotrx, l.c_bpartner_id, l.c_order_id, l.ad_client_id, l.ad_org_id, l.isactive
UNION ALL
 SELECT (l.qtyinvoiced - sum(COALESCE(mi.qty, (0)::numeric))) AS qty,
    (l.qtyentered / l.qtyinvoiced) AS multiplier,
    l.c_uom_id,
    p.m_locator_id,
    l.m_product_id,
    l.c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    l.c_invoiceline_id,
    0 AS m_rmaline_id,
    inv.c_bpartner_id,
    0 AS c_order_id,
    l.c_invoice_id,
    0 AS m_rma_id,
    l.c_invoiceline_id AS m_inout_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    p.upc,
    0 AS m_warehouse_id,
    inv.issotrx,
    333 AS ad_table_id
   FROM ((((c_invoiceline l
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
     JOIN c_invoice inv ON ((l.c_invoice_id = inv.c_invoice_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (inv.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN m_matchinv mi ON ((l.c_invoiceline_id = mi.c_invoiceline_id)))
  WHERE (l.qtyinvoiced <> (0)::numeric)
  GROUP BY l.qtyinvoiced, (l.qtyentered / l.qtyinvoiced), l.c_uom_id, p.m_locator_id, l.m_product_id, l.c_charge_id, po.vendorproductno, l.c_invoiceline_id, l.line, l.c_orderline_id, inv.c_bpartner_id, l.c_invoice_id, p.upc, inv.issotrx, l.ad_client_id, l.ad_org_id, l.isactive
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_locator_id,
    p.m_product_id,
    c.c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    p.upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM ((((((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN m_inoutline iol ON ((rl.m_inoutline_id = iol.m_inoutline_id)))
     LEFT JOIN m_product p ON ((p.m_product_id = iol.m_product_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = COALESCE(p.c_uom_id, iol.c_uom_id))))
     LEFT JOIN c_charge c ON ((c.c_charge_id = iol.c_charge_id)))
     LEFT JOIN m_product_po po ON (((rl.m_product_id = po.m_product_id) AND (r.c_bpartner_id = po.c_bpartner_id))))
  WHERE (rl.m_inoutline_id IS NOT NULL)
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_locator_id,
    p.m_product_id,
    0 AS c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    p.upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM ((((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN m_product p ON ((p.m_product_id = rl.m_product_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = p.c_uom_id)))
     LEFT JOIN m_product_po po ON (((rl.m_product_id = po.m_product_id) AND (r.c_bpartner_id = po.c_bpartner_id))))
  WHERE ((rl.m_product_id IS NOT NULL) AND (rl.m_inoutline_id IS NULL))
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    0 AS m_locator_id,
    0 AS m_product_id,
    c.c_charge_id,
    NULL::character varying AS vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    NULL::character varying AS upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM (((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN c_charge c ON ((c.c_charge_id = rl.c_charge_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = (100)::numeric)))
  WHERE ((rl.c_charge_id IS NOT NULL) AND (rl.m_inoutline_id IS NULL));

 
SELECT
        CASE o.issotrx
            WHEN 'Y'::bpchar THEN (l.qtyordered - l.qtydelivered)
            ELSE ((l.qtyordered - sum(COALESCE(m.qty, (0)::numeric))) - COALESCE(( SELECT sum(iol.movementqty) AS sum
               FROM (m_inoutline iol
                 JOIN m_inout io ON ((iol.m_inout_id = io.m_inout_id)))
              WHERE ((l.c_orderline_id = iol.c_orderline_id) AND (io.processed = 'N'::bpchar))), (0)::numeric))
        END AS qty,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END AS multiplier,
    l.c_uom_id,
    p.m_locator_id,
    COALESCE(l.m_product_id, (0)::numeric) AS m_product_id,
    COALESCE(l.c_charge_id, (0)::numeric) AS c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    0 AS c_invoiceline_id,
    0 AS m_rmaline_id,
    l.c_bpartner_id,
    l.c_order_id,
    0 AS c_invoice_id,
    0 AS m_rma_id,
    l.c_orderline_id AS m_inout_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    p.upc,
    o.m_warehouse_id,
    o.issotrx,
    260 AS ad_table_id
   FROM ((((c_orderline l
     JOIN c_order o ON ((o.c_order_id = l.c_order_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (l.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN m_matchpo m ON (((l.c_orderline_id = m.c_orderline_id) AND (m.m_inoutline_id IS NOT NULL))))
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
  GROUP BY l.qtyordered, l.qtydelivered,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END, l.c_uom_id, p.m_locator_id, po.vendorproductno, l.m_product_id, l.c_charge_id, l.line, l.c_orderline_id, p.upc, o.m_warehouse_id, o.issotrx, l.c_bpartner_id, l.c_order_id, l.ad_client_id, l.ad_org_id, l.isactive
UNION ALL
 SELECT (l.qtyinvoiced - sum(COALESCE(mi.qty, (0)::numeric))) AS qty,
    (l.qtyentered / l.qtyinvoiced) AS multiplier,
    l.c_uom_id,
    p.m_locator_id,
    l.m_product_id,
    l.c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    l.c_invoiceline_id,
    0 AS m_rmaline_id,
    inv.c_bpartner_id,
    0 AS c_order_id,
    l.c_invoice_id,
    0 AS m_rma_id,
    l.c_invoiceline_id AS m_inout_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    p.upc,
    0 AS m_warehouse_id,
    inv.issotrx,
    333 AS ad_table_id
   FROM ((((c_invoiceline l
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
     JOIN c_invoice inv ON ((l.c_invoice_id = inv.c_invoice_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (inv.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN m_matchinv mi ON ((l.c_invoiceline_id = mi.c_invoiceline_id)))
  WHERE (l.qtyinvoiced <> (0)::numeric)
  GROUP BY l.qtyinvoiced, (l.qtyentered / l.qtyinvoiced), l.c_uom_id, p.m_locator_id, l.m_product_id, l.c_charge_id, po.vendorproductno, l.c_invoiceline_id, l.line, l.c_orderline_id, inv.c_bpartner_id, l.c_invoice_id, p.upc, inv.issotrx, l.ad_client_id, l.ad_org_id, l.isactive
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_locator_id,
    p.m_product_id,
    c.c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    p.upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM ((((((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN m_inoutline iol ON ((rl.m_inoutline_id = iol.m_inoutline_id)))
     LEFT JOIN m_product p ON ((p.m_product_id = iol.m_product_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = COALESCE(p.c_uom_id, iol.c_uom_id))))
     LEFT JOIN c_charge c ON ((c.c_charge_id = iol.c_charge_id)))
     LEFT JOIN m_product_po po ON (((rl.m_product_id = po.m_product_id) AND (r.c_bpartner_id = po.c_bpartner_id))))
  WHERE (rl.m_inoutline_id IS NOT NULL)
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_locator_id,
    p.m_product_id,
    0 AS c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    p.upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM ((((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN m_product p ON ((p.m_product_id = rl.m_product_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = p.c_uom_id)))
     LEFT JOIN m_product_po po ON (((rl.m_product_id = po.m_product_id) AND (r.c_bpartner_id = po.c_bpartner_id))))
  WHERE ((rl.m_product_id IS NOT NULL) AND (rl.m_inoutline_id IS NULL))
UNION ALL
 SELECT (rl.qty - rl.qtydelivered) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    0 AS m_locator_id,
    0 AS m_product_id,
    c.c_charge_id,
    NULL::character varying AS vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS c_invoiceline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS c_invoice_id,
    r.m_rma_id,
    rl.m_rmaline_id AS m_inout_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    NULL::character varying AS upc,
    0 AS m_warehouse_id,
    r.issotrx,
    660 AS ad_table_id
   FROM (((m_rmaline rl
     JOIN m_rma r ON ((r.m_rma_id = rl.m_rma_id)))
     JOIN c_charge c ON ((c.c_charge_id = rl.c_charge_id)))
     LEFT JOIN c_uom uom ON ((uom.c_uom_id = (100)::numeric)))
  WHERE ((rl.c_charge_id IS NOT NULL) AND (rl.m_inoutline_id IS NULL));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views