m_packagelines_avail_v

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ad_client_id

numeric

10

√

null

ad_org_id

numeric

10

√

null

m_inout_id

numeric

10

√

null

m_inoutline_id

numeric

10

√

null

line

numeric

0

√

null

qty

numeric

0

√

null

m_product_id

numeric

10

√

null

productname

varchar

255

√

null

uom

varchar

60

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

SELECT DISTINCT iol.ad_client_id,
    iol.ad_org_id,
    iol.m_inout_id,
    iol.m_inoutline_id,
    iol.line,
    (iol.qtyentered - COALESCE(( SELECT sum(m_packageline.qty) AS sum
           FROM m_packageline
          WHERE (m_packageline.m_inoutline_id = iol.m_inoutline_id)), (0)::numeric)) AS qty,
    iol.m_product_id,
    p.name AS productname,
    uom.name AS uom
   FROM ((((m_inoutline iol
     LEFT JOIN m_packageline mpl ON ((iol.m_inoutline_id = mpl.m_inoutline_id)))
     JOIN m_product p ON ((iol.m_product_id = p.m_product_id)))
     JOIN c_uom uom ON ((iol.c_uom_id = uom.c_uom_id)))
     JOIN ad_clientinfo ci ON ((ci.ad_client_id = iol.ad_client_id)))
  WHERE ((p.ispicklistprintdetails = 'N'::bpchar) AND (iol.m_product_id <> COALESCE(ci.m_productfreight_id, (0)::numeric)) AND ((iol.qtyentered - COALESCE(( SELECT sum(m_packageline.qty) AS sum
           FROM m_packageline
          WHERE (m_packageline.m_inoutline_id = iol.m_inoutline_id)), (0)::numeric)) <> (0)::numeric))
UNION
 SELECT DISTINCT iol.ad_client_id,
    iol.ad_org_id,
    iol.m_inout_id,
    iol.m_inoutline_id,
    (iol.line + (pbom.line / (100)::numeric)) AS line,
    ((iol.qtyentered * pbom.bomqty) - COALESCE(( SELECT sum(m_packageline.qty) AS sum
           FROM m_packageline
          WHERE ((m_packageline.m_product_id = p2.m_product_id) AND (m_packageline.m_package_id IN ( SELECT m_package.m_package_id
                   FROM m_package
                  WHERE (m_package.m_inout_id = iol.m_inout_id))))), (0)::numeric)) AS qty,
    pbom.m_productbom_id AS m_product_id,
    p2.name AS productname,
    uom.name AS uom
   FROM (((((m_inoutline iol
     LEFT JOIN m_packageline mpl ON ((iol.m_inoutline_id = mpl.m_inoutline_id)))
     JOIN m_product p ON ((iol.m_product_id = p.m_product_id)))
     JOIN m_product_bom_old pbom ON ((p.m_product_id = pbom.m_product_id)))
     JOIN m_product p2 ON ((pbom.m_productbom_id = p2.m_product_id)))
     JOIN c_uom uom ON ((iol.c_uom_id = uom.c_uom_id)))
  WHERE ((p.isbom = 'Y'::bpchar) AND (p.isverified = 'Y'::bpchar) AND (p.ispicklistprintdetails = 'Y'::bpchar) AND (((iol.qtyentered * pbom.bomqty) - COALESCE(( SELECT sum(m_packageline.qty) AS sum
           FROM m_packageline
          WHERE ((m_packageline.m_product_id = p2.m_product_id) AND (m_packageline.m_package_id IN ( SELECT m_package.m_package_id
                   FROM m_package
                  WHERE (m_package.m_inout_id = iol.m_inout_id))))), (0)::numeric)) <> (0)::numeric))
  ORDER BY 1, 2, 3, 5;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views