c_invoice_createfrom_v

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

creditqty

numeric

√

null

qty

numeric

√

null

multiplier

numeric

√

null

c_uom_id

numeric

√

null

c_uom.c_uom_id

Implied Constraint

m_product_id

numeric

√

null

c_charge_id

numeric

√

null

vendorproductno

varchar

2147483647

√

null

line

numeric

√

null

c_orderline_id

numeric

√

null

m_inoutline_id

numeric

√

null

m_rmaline_id

numeric

√

null

c_bpartner_id

numeric

√

null

c_bpartner.c_bpartner_id

Implied Constraint

c_order_id

numeric

√

null

m_inout_id

numeric

√

null

m_rma_id

numeric

√

null

c_invoice_createfrom_v_id

numeric

√

null

ad_client_id

numeric

√

null

ad_org_id

numeric

√

null

isactive

bpchar

√

null

issotrx

bpchar

√

null

ad_table_id

int4

√

null

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT sum(COALESCE(m.qty, (0)::numeric)) AS creditqty,
    (l.qtyordered - sum(COALESCE(m.qty, (0)::numeric))) AS qty,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END AS multiplier,
    l.c_uom_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 m_inoutline_id,
    0 AS m_rmaline_id,
    l.c_bpartner_id,
    l.c_order_id,
    0 AS m_inout_id,
    0 AS m_rma_id,
    l.c_orderline_id AS c_invoice_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    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.c_invoiceline_id IS NOT NULL) AND (COALESCE(m.reversal_id, (0)::numeric) = (0)::numeric))))
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
  GROUP BY l.qtyordered,
        CASE
            WHEN (l.qtyordered = (0)::numeric) THEN (0)::numeric
            ELSE (l.qtyentered / l.qtyordered)
        END, l.c_uom_id, po.vendorproductno, l.m_product_id, l.c_charge_id, l.line, l.c_orderline_id, o.issotrx, l.ad_client_id, l.ad_org_id, l.isactive, l.c_bpartner_id, l.c_order_id
UNION ALL
 SELECT
        CASE
            WHEN (io.issotrx = 'N'::bpchar) THEN (l.movementqty - (sum(COALESCE(mi.qty, (0)::numeric)) * (
            CASE
                WHEN (io.movementtype = 'V-'::bpchar) THEN '-1'::integer
                ELSE 1
            END)::numeric))
            ELSE (l.movementqty - (sum(COALESCE(mi.qty, (0)::numeric)) * (
            CASE
                WHEN (io.movementtype = 'V-'::bpchar) THEN '-1'::integer
                ELSE 1
            END)::numeric))
        END AS creditqty,
    (l.movementqty - (sum(COALESCE(mi.qty, (0)::numeric)) * (
        CASE
            WHEN (io.movementtype = 'V-'::bpchar) THEN '-1'::integer
            ELSE 1
        END)::numeric)) AS qty,
    (l.qtyentered / l.movementqty) AS multiplier,
    l.c_uom_id,
    l.m_product_id,
    l.c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    l.m_inoutline_id,
    0 AS m_rmaline_id,
    io.c_bpartner_id,
    0 AS c_order_id,
    l.m_inout_id,
    0 AS m_rma_id,
    l.m_inoutline_id AS c_invoice_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    io.issotrx,
    320 AS ad_table_id
   FROM ((((m_inoutline l
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
     JOIN m_inout io ON ((l.m_inout_id = io.m_inout_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (io.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN m_matchinv mi ON ((l.m_inoutline_id = mi.m_inoutline_id)))
  WHERE ((l.movementqty <> (0)::numeric) AND (io.issotrx = 'N'::bpchar))
  GROUP BY io.movementtype, l.movementqty, (l.qtyentered / l.movementqty), l.c_uom_id, po.vendorproductno, l.m_product_id, l.c_charge_id, l.line, l.c_orderline_id, l.m_inoutline_id, io.c_bpartner_id, l.m_inout_id, io.issotrx, l.ad_client_id, l.ad_org_id, l.isactive
 HAVING ((l.movementqty - sum(COALESCE(mi.qty, (0)::numeric))) <> (0)::numeric)
UNION ALL
 SELECT (l.movementqty - sum(COALESCE(il.qtyinvoiced, (0)::numeric))) AS creditqty,
    (l.movementqty - sum(COALESCE(il.qtyinvoiced, (0)::numeric))) AS qty,
    (l.qtyentered / l.movementqty) AS multiplier,
    l.c_uom_id,
    l.m_product_id,
    l.c_charge_id,
    po.vendorproductno,
    l.line,
    l.c_orderline_id,
    l.m_inoutline_id,
    0 AS m_rmaline_id,
    io.c_bpartner_id,
    0 AS c_order_id,
    l.m_inout_id,
    0 AS m_rma_id,
    l.m_inoutline_id AS c_invoice_createfrom_v_id,
    l.ad_client_id,
    l.ad_org_id,
    l.isactive,
    io.issotrx,
    320 AS ad_table_id
   FROM ((((m_inoutline l
     LEFT JOIN m_product p ON ((l.m_product_id = p.m_product_id)))
     JOIN m_inout io ON ((l.m_inout_id = io.m_inout_id)))
     LEFT JOIN m_product_po po ON (((l.m_product_id = po.m_product_id) AND (io.c_bpartner_id = po.c_bpartner_id))))
     LEFT JOIN c_invoiceline il ON ((l.m_inoutline_id = il.m_inoutline_id)))
  WHERE ((l.movementqty <> (0)::numeric) AND (io.issotrx = 'Y'::bpchar))
  GROUP BY io.movementtype, l.movementqty, (l.qtyentered / l.movementqty), l.c_uom_id, po.vendorproductno, l.m_product_id, l.c_charge_id, l.line, l.c_orderline_id, l.m_inoutline_id, io.c_bpartner_id, l.m_inout_id, io.issotrx, l.ad_client_id, l.ad_org_id, l.isactive
 HAVING ((l.movementqty - sum(COALESCE(il.qtyinvoiced, (0)::numeric))) <> (0)::numeric)
UNION ALL
 SELECT (rl.qty - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS creditqty,
    (rl.qty - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_product_id,
    c.c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS m_inoutline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS m_inout_id,
    r.m_rma_id,
    rl.m_rmaline_id AS c_invoice_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    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 - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS creditqty,
    (rl.qty - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    p.m_product_id,
    0 AS c_charge_id,
    po.vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS m_inoutline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS m_inout_id,
    r.m_rma_id,
    rl.m_rmaline_id AS c_invoice_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    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 - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS creditqty,
    (rl.qty - COALESCE(rl.qtyinvoiced, (0)::numeric)) AS qty,
    1 AS multiplier,
    uom.c_uom_id,
    0 AS m_product_id,
    c.c_charge_id,
    NULL::character varying AS vendorproductno,
    rl.line,
    0 AS c_orderline_id,
    0 AS m_inoutline_id,
    rl.m_rmaline_id,
    r.c_bpartner_id,
    0 AS c_order_id,
    0 AS m_inout_id,
    r.m_rma_id,
    rl.m_rmaline_id AS c_invoice_createfrom_v_id,
    rl.ad_client_id,
    rl.ad_org_id,
    rl.isactive,
    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