Datastore views

Status: 2.21.1

  • Views starting with v_geko_ and v_tako_are interface views for the GEKO and TAKO robots do not belong to the BPS core.
  • Views starting with cv_ are custom views and do not belong to the BPS core. Ask your system integrator for documentation of such views.

Reference

v_collarticles

Conveniennce article view to facilate selection of articles in a certain collection.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_propagates varchar(4000) Comma separated list of columns and attributes table names propagated to children on changes.
c_department number(15) not null References t_departments.
c_entryzone number(15) not null References t_zones.
c_lotzone number(15) References t_zones.
c_id varchar(35) not null Unique identifier (article number).
c_name varchar(35) not null Name of the article.
c_spokenname varchar(35) Spoken name for pick-by-voice applications. When left empty/null, lower(c_name) gets used for pick-by-voice instead.
c_status varchar(1) not null a = active
l = locked
d = marked for deletion.
c_collective varchar(1) not null y = collective article (separate ordercols when picking)
n = individual article (combine ordercols when picking)
c_dispolist varchar(1) not null n = Not included in disposition lists
y = Included in disposition lists
c_articleclass number(15) References t_articleclasses.
c_shipclass number(15) References t_shipclasses.
c_saleclass number(15) References t_saleclasses.
c_vat number(15) References t_vats.
c_trip number(15) Main delivery trip. References t_trips.
c_stock_unit varchar(1) not null Main stock unit:
C = consumer unit
T = traded unit
L = logistic unit
k = kilogram
l = liter
m = meter.
c_stock_min number Reorder when available stock falls below this value.
c_stock_nom number Nominal available stock.
c_stock_mode varchar(1) not null Stock strategy mode:
r = regular
f = FIFO
q = quick
c_lu varchar(10) Name/acronym of the logistic unit.
c_tu varchar(10) Name/acronym of the traded unit.
c_cu varchar(10) Name/acronym of the consumer unit.
c_pu varchar(1) not null Price base unit:
p = piece
k = kilogram
l = liter
m = meter
c_tu_lu number not null Factor traded units in one logistic unit.
c_cu_tu number not null Factor consumer units in one traded unit.
c_pu_cu number not null Factor price units in one consumer unit.
c_pcs_cu number not null Pieces in one consumer unit.
c_pcs_cu_min number Minimum pieces in one consumer unit.
c_pcs_cu_max number Maximum pieces in one consumer unit.
c_kg_cu number not null Weight of one consumer unit.
c_kg_cu_min number Minimum weight of one consumer unit.
c_kg_cu_max number Maximum weight of one consumer unit.
c_lu_package number(15) Logistic unit package. References t_packages.
c_tu_package number(15) Traded unit package. References t_packages.
c_cu_package number(15) Consumer unit package. References t_packages.
c_plu varchar(35) Price Look-Up code, short article number for packing/labeling lines or check-out at vending points.
c_pickprio number Picking priority. Lower number are higher priorities. Articles with same priority are sorted by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_packline number Main packing line number.
c_packline2 number Alternate packing line number.
c_packprio number Packing priority. Lower number are higher priorities. Articles with same priority are sorted by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_selldays number Number of selling days, counted from production/labeling date.
c_expiredays number Expires this number days after selling days.
c_storagedays number # of storage days.
c_overdelivery number Number of remaining units in robot stocks, which can be overdelivered in case to empty down the stock. When more units are left at end of an order trip, no overdelivery happens at all and the units are kept for the next order trip.
c_hdlspeed number not null Allowed handling speed.
-2 = very slow
-1 = slow
0 = medium
1 = fast
2 = very fast
c_intakecode varchar(1) not null Intake registration code:
c = count only
w = count and weigh.
c_delivcode varchar(1) not null Delivery registration code:
c = count only
w = count and weigh.
c_pickremarks varchar(4000) Remarks shown when picking.
c_collection number(15) not null References t_collections.
c_collectionname varchar(35) not null Name of the collection.
c_order varchar(4000) Order by column.

v_goodsentryitemjobs

Goods receipt view for delivery selection in classic mode.

In contrast to v_goodsentryjobs, this view also lists the sums of completed items.

Column Type Description
c_zone number(15) not null References t_zones.
c_purchaseorder number(15) not null References t_purchaseorders.
c_purchaseitem number(15) not null References t_purchaseitems.
c_purchasedelivery number(15) References t_purchasedeliveries. When null, the missing quantity not yet in deliveries.
c_status varchar(1) not null o = open delivery
p = partial/rest delivery
c = completed item sums
c_date timestamp not null Supposed entry date/time, depending on status:
ordered, confirmed: t_purchaseitems.c_deliverydate
shipped: t_purchasedeliveries.c_deliverydate
arrived: t_purchasedeliveries.c_arrivaldate.
c_orderid varchar(35) Own order ID.
c_selleroid varchar(35) The sellers order ID.
c_distributoroid varchar(35) The distributors order ID.
c_itemid varchar(35) Own item ID.
c_selleriid varchar(35) The sellers item ID.
c_distributoriid varchar(35) The distributors item ID.
c_article number(15) not null References t_article.
c_artid varchar(35) not null Article ID.
c_artname varchar(35) not null Article name.
c_lu varchar(10) Name/acronym of the logistic unit.
c_tu varchar(10) Name/acronym of the traded unit.
c_cu varchar(10) Name/acronym of the consumer unit.
c_intakecode varchar(1) not null Intake registration code:
c = count only
w = count and weigh.
c_sellerid varchar(35) not null Seller ID.
c_sellername varchar(35) not null Seller name.
c_distributorid varchar(35) Distributor ID.
c_distributorname varchar(35) Distributor name.
c_carrier varchar(100) Final carrier and/or vehicle bringing the delivery.
c_deliveryid varchar(35) Delivery ID (to be found on the receipt.)
c_sscc varchar(4000) Comma separated list of SSCC's involved (usually one per hierarchical level).
c_lotid varchar(4000) Comma separated list of lot ID's.
c_ordered number not null Ordered quantity in OU's.
c_entered number not null Entered quantity in OU's.
c_open number not null Open quantity in OU's.
c_ou varchar(1) not null Order unit:
L = LU
T = TU
C = CU
P = PU
c_pu varchar(1) not null Price unit:
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_remarks varchar(4000) Item remarks text.

v_goodsentryjobs

Goods receipt view for purchase delivery selection in advanced mode.

Column Type Description
c_zone number(15) not null References t_zones.
c_purchaseorder number(15) not null References t_purchaseorders.
c_purchaseitem number(15) not null References t_purchaseitems.
c_purchasedelivery number(15) References t_purchasedeliveries. When null, the missing quantity not yet in deliveries.
c_status varchar(1) not null o = open
p = partial/rest delivery
c_date timestamp not null Supposed entry date/time, depending on status:
ordered, confirmed: t_purchaseitems.c_deliverydate
shipped: t_purchasedeliveries.c_deliverydate
arrived: t_purchasedeliveries.c_arrivaldate.
c_orderid varchar(35) Own order ID.
c_selleroid varchar(35) The sellers order ID.
c_distributoroid varchar(35) The distributors order ID.
c_itemid varchar(35) Own item ID.
c_selleriid varchar(35) The sellers item ID.
c_distributoriid varchar(35) The distributors item ID.
c_article number(15) not null References t_article.
c_artid varchar(35) not null Article ID.
c_artname varchar(35) not null Article name.
c_lu varchar(10) Name/acronym of the logistic unit.
c_tu varchar(10) Name/acronym of the traded unit.
c_cu varchar(10) Name/acronym of the consumer unit.
c_intakecode varchar(1) not null Intake registration code:
c = count only
w = count and weigh.
c_sellerid varchar(35) not null Seller ID.
c_sellername varchar(35) not null Seller name.
c_distributorid varchar(35) Distributor ID.
c_distributorname varchar(35) Distributor name.
c_carrier varchar(100) Final carrier and/or vehicle bringing the delivery.
c_deliveryid varchar(35) Delivery ID (to be found on the receipt.)
c_sscc varchar(4000) Comma separated list of SSCC's involved (usually one per hierarchical level).
c_lotid varchar(4000) Comma separated list of lot ID's.
c_ordered number not null Ordered quantity in OU's.
c_entered number not null Entered quantity in OU's.
c_open number not null Open quantity in OU's.
c_ou varchar(1) not null Order unit:
L = LU
T = TU
C = CU
P = PU
c_pu varchar(1) not null Price unit:
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_orderremarks varchar(4000) Order remarks text.
c_itemremarks varchar(4000) Item remarks text.
c_deliveryremarks varchar(4000) Delivery remarks text.

v_myreports

All reports where the current actor has at least read access.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_owner number(15) Report owner (designer). References t_users.
c_name varchar(35) not null Report or folder name, may not contain any slashes /
c_type varchar(1) not null 'r' = report
c_data clob The report as XML.

v_ordercolrobots

Order column robot status.

Column Type Description
c_key number(15) not null Primary key.
c_ordercol number(15) not null References t_ordercols.
c_zone number(15) not null References t_zones.
c_zonename varchar(35) not null Picking zone name
c_status varchar(1) not null Trip robot status in t_ordertriprobots.
c_items number not null Total number of items.
c_unsynced number not null Number of items not in sync between BPS and robot.

v_orderitemrobots

Order item robot status.

Column Type Description
c_key number(15) not null Primary key.
c_orderitem number(15) not null References t_orderitems.
c_zone number(15) not null References t_zones.
c_zonename varchar(35) not null Picking zone name
c_status varchar(1) not null Trip robot status in t_ordertriprobots.
c_mergeclass number(15) not null References t_mergeclasses.
c_mergeclassname varchar(35) not null Merge class name
c_base number not null Previously already picked quantity in OU, not to be picked by the robot.
c_planned number not null Quantitiy in OU the robot shall pick.
c_picked number not null Quantitiy in OU yet picked by robot.
c_insync varchar(1) not null n = not in sync between BPS and robot
y = in sync between BPS and robot

The sum of c_base + c_planned equates to c_planned in t_orderitems.

v_orderrowrobots

Order row robot status.

Column Type Description
c_key number(15) not null Primary key.
c_orderrow number(15) not null References t_orderrows.
c_zone number(15) not null References t_zones.
c_zonename varchar(35) not null Picking zone name
c_status varchar(1) not null Trip robot status in t_ordertriprobots.
c_items number not null Total number of items.
c_unsynced number not null Number of items not in sync between BPS and robot.

v_ordertriprobots

Order trip robot status.

Column Type Description
c_key number(15) not null Primary key.
c_ordertrip number(15) not null References t_ordertrips.
c_zone number(15) not null References t_zones.
c_zonename varchar(35) not null Picking zone name
c_status varchar(1) not null u = unsent
s = sent
a = active
f = finished
c_items number not null Total number of items.
c_unsynced number not null Number of items not in sync between BPS and robot.

v_prorationpartners

Utility partners view for the proration application.

Column Type Description
c_key number(15) not null Primary key in t_partners.
c_status varchar(1) not null a = active
l = locked
d = marked for deletion.
c_trip number(15) References t_trips.
c_article number(15) References t_articles.
c_id varchar(35) not null Unique identifier (partner number).
c_name varchar(35) not null Name of the partner.
c_partnerclass number(15) References t_partnerclasses.
c_pricecode varchar(1) not null Price code for billing:
d = dealer price
c = consumer price
c_ordered number Ordered quantity for t_orderitems.
c_remarks varchar(4000) Order remarks for t_orderitems.

v_sumordercols

Orders columns with added up values from the items and picks.

Column Type Description
c_key number(15) not null Primary key in t_ordercols.
c_ordertrip number(15) not null References t_ordertrips.
c_article number(15) not null References t_articles.
c_id varchar(35) not null Unique identifier in t_articles.
c_name varchar(35) not null Descriptive name in t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_stock_unit varchar(1) not null Main stock unit:
L = logistic unit
T = traded unit
C = consumer unit
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_ordered number not null Total original order quantity in OU's in t_orderitems.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units. The value is approximately c_planned-c_picked, but my be greater in case items were overdelivered.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.
c_remarks varchar(4000) Order column remarks in t_ordercols.

v_sumordercols1

Orders columns with added up values from the items and picks, including stock quantities.

Column Type Description
c_key number(15) not null Primary key in t_ordercols.
c_ordertrip number(15) not null References t_ordertrips.
c_article number(15) not null References t_articles.
c_id varchar(35) not null Unique identifier in t_articles.
c_name varchar(35) not null Descriptive name in t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_stock_unit varchar(1) not null Main stock unit:
L = logistic unit
T = traded unit
C = consumer unit
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_currstock number not null Current stock in order units, see f_currentstock.
c_availstock number not null Stock available for picking in order units, see f_availabletripstock.
c_ordered number not null Total original order quantity in OU's in t_orderitems.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units. The value is approximately c_planned-c_picked, but my be greater in case items were overdelivered.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.
c_remarks varchar(4000) Order column remarks in t_ordercols.

v_sumorderitems

Orders items with added up values from the picks, for display as partner order item list.

In contrast to v_sumordercolitems and v_sumorderrowitems, this view does not join t_articles or t_partners respectively. It may therefore perform better when that particular information is not required.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_article number(15) not null References t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_orderid varchar(35) not null Customers item ID in t_orderitems.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_ordered number not null Original order quantity in OU's in t_orderitems.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.
c_remarks varchar(4000) Order item remarks in t_orderitems.

v_sumorderitems0

Orders items with summs of the picks.

This is a convenience variant of v_sumorderitems joining in the table t_orderrows to provide more information.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_orderid varchar(35) not null Customers item ID.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_ordered number not null Original order quantity in OU's in t_orderitems.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_remarks varchar(4000) Order item remarks in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_roworderid varchar(35) not null Customers row ID.
c_partner number(15) not null References t_partners.
c_rowremarks varchar(4000) Order row remarks in t_orderrows.
c_article number(15) not null References t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_colremarks varchar(4000) Order column remarks in t_ordercols.
c_picked number not null Total picked order units.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.

v_sumorderitems1

Orders items with summs of the picks.

This is a convenience variant of v_sumorderitems joining in the tables t_orderrows and t_articles to provide more information.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_orderid varchar(35) not null Customers item ID.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_ordered number not null Original order quantity in OU's in t_orderitems.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_remarks varchar(4000) Order item remarks in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_roworderid varchar(35) not null Customers row ID.
c_partner number(15) not null References t_partners.
c_rowremarks varchar(4000) Order row remarks in t_orderrows.
c_article number(15) not null References t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_colremarks varchar(4000) Order column remarks in t_ordercols.
c_artid varchar(35) not null Unique identifier in t_articles.
c_artname varchar(35) not null Descriptive name in t_articles.
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_kg_cu number not null Weight of a single CU (used for packing only, weight charged is in PU). From t_articles.
c_stock_mode varchar(1) not null Stock strategy mode:
r = regular
f = FIFO
q = quick. From t_articles.
c_artpickremarks varchar(4000) Article pick remarks in t_articles.
c_artcollective varchar(1) not null y = collective article (separate ordercols when picking)
n = individual article (combine ordercols when picking)
c_cu varchar(10) Name/acronym of the consumer unit.
c_tu varchar(10) Name/acronym of the traded unit.
c_lu varchar(10) Name/acronym of the logistic unit.
c_cu_package number(15) Consumer unit package. References t_packages.
c_tu_package number(15) Traded unit package. References t_packages.
c_lu_package number(15) Logistic unit package. References t_packages.
c_picked number not null Total picked order units.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.

v_sumorderitems2

Orders items with summs of the picks.

This is a convenience variant of v_sumorderitems joining in the tables t_orderrows, t_ordercols and t_partners to provide more information.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_orderid varchar(35) not null Customers item ID.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_ordered number not null Original order quantity in OU's in t_orderitems.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_plannedpus number not null Planned delivery quantitiy in PU's in t_orderitems.
c_remarks varchar(4000) Order item remarks in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_roworderid varchar(35) not null Customers row ID.
c_partner number(15) not null References t_partners.
c_rowremarks varchar(4000) Order row remarks in t_orderrows.
c_pnrid varchar(35) not null Unique identifier in t_partners.
c_pnrname varchar(35) not null Descriptive name in t_partners.
c_pnrpickremarks varchar(4000) Partner pick remarks in t_partners.
c_pnrcollective varchar(1) not null y = collective partner (separate orderrows when picking)
n = individual partner (combine orderrows when picking)
c_pnrcollection number(15) Article collection assigned to the partner. References t_collections.
c_pnrembarkpoint varchar(35) Embark point in t_partners.
c_article number(15) not null References t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_selldays number Number of selling days in t_ordercols.
c_expiredays number number of expire days in t_ordercols.
c_colremarks varchar(4000) Order column remarks in t_ordercols.
c_picked number not null Total picked order units.
c_picks number not null Total pick records in t_orderpicks.
c_delivered number not null Number of picks where c_delivery is not null.
(Shadow sum from t_orderpicks, read-only!)
c_invoiced number not null Number of picks where c_invoice is not null.
(Shadow sum from t_orderpicks, read-only!)
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.

v_sumorderitems3

Orders items with summs of the picks.

This is a convenience variant of v_sumorderitems joining in the tables t_orderrows, t_articles and t_partners to provide more information.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_orderid varchar(35) not null Customers item ID.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_ordered number not null Original order quantity in OU's in t_orderitems.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_remarks varchar(4000) Order item remarks in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_roworderid varchar(35) not null Customers row ID.
c_partner number(15) not null References t_partners.
c_rowremarks varchar(4000) Order row remarks in t_orderrows.
c_pnrid varchar(35) not null Unique identifier in t_partners.
c_pnrname varchar(35) not null Descriptive name in t_partners.
c_pnrpickremarks varchar(4000) Partner pick remarks in t_partners.
c_pnrcollective varchar(1) not null y = collective partner (separate orderrows when picking)
n = individual partner (combine orderrows when picking)
c_article number(15) not null References t_articles.
c_dpr number not null Dealer price in t_ordercols.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_colremarks varchar(4000) Order column remarks in t_ordercols.
c_artid varchar(35) not null Unique identifier in t_articles.
c_artname varchar(35) not null Descriptive name in t_articles.
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_kg_cu number not null Weight of a single CU (used for packing only, weight charged is in PU). From t_articles.
c_stock_mode varchar(1) not null Stock strategy mode:
r = regular
f = FIFO
q = quick. From t_articles.
c_artpickremarks varchar(4000) Article pick remarks in t_articles.
c_picked number not null Total picked order units.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.

v_sumorderrows

Orders rows with added up values from the items and picks.

Column Type Description
c_key number(15) not null Primary key in t_ordercols.
c_ordertrip number(15) not null References t_ordertrips.
c_partner number(15) not null References t_partners.
c_id varchar(35) not null Unique identifier in t_partners.
c_name varchar(35) not null Descriptive name in t_partners.
c_orderby number(15) not null References t_users.
c_ordercd varchar(1) not null Order code in in t_orderrows
n = normal
r = reorder
a = assignment
c_orderid varchar(35) not null Customers order ID in t_orderrows.
c_orderdate timestamp not null Date of ordering in t_orderrows.
c_ordered number not null Total original order quantity in OU's in t_orderitems.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units. The value is approximately c_planned-c_picked, but my be greater in case items were overdelivered.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.
c_remarks varchar(4000) Order row remarks in t_orderrows.

v_sumordertrips

The trips for order delivery to buyers along with summary columns.

Column Type Description
c_key number(15) not null Primary key in t_ordertrips.
c_trip number(15) not null References c_key in t_trips.
c_id varchar(35) not null Unique identifier in t_trips.
c_name varchar(35) not null Descriptive name in t_trips.
c_date timestamp not null Date of trip departure in t_ordertrips.
c_status varchar(1) not null Status in in t_ordertrips
n = new, being created
r = ready for working
a = actively picking
c = completed
h= History, archived
c_ordered number not null Total original order quantity in OU's added up from t_orderitems.
c_planned number not null Total planned delivery quantitiy in OU's added up from t_orderitems.
c_picked number not null Quantitiy in OU allready picked.
c_missing number not null Total yet missing order units. The value is approximately c_planned-c_picked, but my be greater in case items were overdelivered.
c_picks number not null Total pick records in t_orderpicks.
c_lus number not null Total picked expressed in logistic units added up from t_orderpicks.
c_tus number not null Total picked expressed in traded units added up from t_orderpicks.
c_cus number not null Total picked expressed in consumer units added up from t_orderpicks.
c_pus number not null Total picked expressed in price units added up from t_orderpicks.

v_sumpackjobs

Packing jobs within the packing production.

Column Type Description
c_key number(15) not null Primary key.
c_packprod number(15) not null References t_packprods.
c_packline number Main packing line number.
c_packline2 number Alternate packing line number.
c_packzone number(15) References t_zones.
c_packprio number Packing priority. Lower number are higher priorities.
c_article number(15) not null References t_articles.
c_plu varchar(35) Price Look-Up code, short article number for packing/labeling lines or check-out at vending points.
c_id varchar(35) not null Unique identifier (article number).
c_name varchar(35) not null Name of the article.
c_packunit varchar(1) not null Packed unit:
L = LU
T = TU
C = CU
c_packunitname varchar(35) not null Name of the packing unit: taken fron t_articles.c_lu, c_tu or c_cu respectively.
c_quantity number Total units to pack.
c_packed number Total units yet packed.
c_missing number Total units yet to pack.
c_quantity_kg number Total kilograms to pack.
c_packed_kg number Total kilograms yet packed.
c_missing_kg number Total kilograms yet to pack.
c_tu_lu number not null Factor traded units in one logistic unit.
c_cu_tu number not null Factor consumer units in one traded unit.
c_pu_cu number not null Factor price units in one consumer unit.
c_cpr number Consumer price.
c_prcd varchar(1) not null Price code:
n = normal
p = promotion
i = introduction
s = sellout
c_pu varchar(1) not null Price base unit:
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_selldays number Number of selling days, counted from production/labeling date.
c_expiredays number Expires this number days after selling days.
c_remarks varchar(4000) Packing job remarks.

v_sumpackjobs2

Packing jobs within the packing production. This variant of v_sumpackjobs calculates the packed and missing units different for articles with price per kilo and a cu/tu relation of 1 in the packing job. For these jobs the packed units are calculated from the packed kilos divided by the kilos per unit (result rounded down to the next integer).

Column Type Description
c_key number(15) not null Primary key.
c_packprod number(15) not null References t_packprods.
c_packline number Main packing line number.
c_packline2 number Alternate packing line number.
c_packzone number(15) References t_zones.
c_packprio number Packing priority. Lower number are higher priorities.
c_article number(15) not null References t_articles.
c_plu varchar(35) Price Look-Up code, short article number for packing/labeling lines or check-out at vending points.
c_id varchar(35) not null Unique identifier (article number).
c_name varchar(35) not null Name of the article.
c_packunit varchar(1) not null Packed unit:
L = LU
T = TU
C = CU
c_packunitname varchar(35) not null Name of the packing unit: taken fron t_articles.c_lu, c_tu or c_cu respectively.
c_quantity number Total units to pack.
c_packed number Total units yet packed.
c_missing number Total units yet to pack.
c_quantity_kg number Total kilograms to pack.
c_packed_kg number Total kilograms yet packed.
c_missing_kg number Total kilograms yet to pack.
c_tu_lu number not null Factor traded units in one logistic unit.
c_cu_tu number not null Factor consumer units in one traded unit.
c_pu_cu number not null Factor price units in one consumer unit.
c_cpr number Consumer price.
c_prcd varchar(1) not null Price code:
n = normal
p = promotion
i = introduction
s = sellout
c_pu varchar(1) not null Price base unit:
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_selldays number Number of selling days, counted from production/labeling date.
c_expiredays number Expires this number days after selling days.
c_remarks varchar(4000) Packing job remarks.

v_sumpickcols

View for the order columns when distributing break bulk.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_ordercol number(15) Individual articles: NULL
Collective articles: references t_ordercols
c_ordercols varchar Comma separated list the order column keys in t_ordercols
c_prio number not null Article picking priority from t_articles.c_pickprio
c_article number(15) not null References t_articles.
c_id varchar(35) not null Unique identifier in t_articles.
c_name varchar(35) not null Descriptive name in t_articles.
c_zone number(15) not null References t_zones.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units that need to get picked.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = regular
p = promotion
i = introduction
s = sellout
c_tu_lu number not null Factor traded units in one logistic unit. From t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit. From t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit. From t_ordercols.
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
c_ouname varchar(10) Name of the order unit, see c_lu, c_tu or c_cu in t_articles respectively.
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_artpickremarks varchar(4000) Article pick remarks in t_articles.
c_remarks varchar(4000) Order column remarks joined by newlines, see f_ordercolsremarks.
c_users varchar(4000) List of locking users joined by newlines, see f_ordercolsusers.

v_sumpickcols2

View for the order columns for packline picking of article orders.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_ordercol number(15) Individual articles: NULL
Collective articles: references t_ordercols
c_ordercols varchar Comma separated list the order column keys in t_ordercols
c_prio number not null Article picking priority from t_articles.c_pickprio
c_article number(15) not null References t_articles.
c_id varchar(35) not null Unique identifier in t_articles.
c_plu varchar(35) not null PLU in t_articles.
c_packline varchar(35) not null Packing line in t_articles.
c_packline2 varchar(35) not null Alternate packing line in t_articles.
c_name varchar(35) not null Descriptive name in t_articles.
c_zone number(15) not null Picking zone. References t_zones.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units that need to get picked.
c_cpr number not null Consumer price in t_ordercols.
c_prcd varchar(1) not null Price code in t_ordercols:
n = regular
p = promotion
i = introduction
s = sellout
c_tu_lu number not null Factor traded units in one logistic unit. From t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit. From t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit. From t_ordercols.
c_selldays number Number of selling days in t_ordercols.
c_expiredays number number of expire days in t_ordercols.
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_ouname varchar(10) Name of the order unit, see c_lu, c_tu or c_cu in t_articles respectively.
c_artpickremarks varchar(4000) Article pick remarks in t_articles.
c_remarks varchar(4000) Order column remarks joined by newlines, see f_ordercolsremarks.
c_users varchar(4000) List of locking users joined by newlines, see f_ordercolsusers.

v_sumpickrows

View for the order rows when picking from stock.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_orderrow number(15) Individual partners: NULL
Collective partners: references t_orderrows
c_orderrows varchar Comma separated list the order row keys in t_orderrows
c_zoneprio number Priority from t_zonepartners. Lower number are higher priorities. Partners with same priority are ordered by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_tripprio number Priority from t_trippartners. Lower number are higher priorities. Partners with same priority are ordered by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_closeprio number Priority from t_partners. Lower number are higher priorities. Partners with same priority are ordered by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_partner number(15) not null References t_partners.
c_id varchar(35) not null Unique identifier in t_partners.
c_name varchar(35) not null Descriptive name in t_partners.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_mergeclassname varchar(35) not null Descriptive name in t_mergeclasses.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units that need to get picked.
c_pnrpickremarks varchar(4000) Partner pick remarks in t_partners.
c_remarks varchar(4000) Order row remarks joined by newlines, see f_orderrowsremarks.
c_users varchar(4000) List of locking users joined by newlines, see f_orderrowsusers.

v_sumpurchaseitems

Purchase order item records including delivery sums.

Column Type Description
c_key number(15) not null Primary key.
c_purchaseorder number(15) not null References t_purchaseorders.
c_status varchar(1) not null o = open
c = completed
l = locked
c_itemid varchar(35) Own item ID.
c_selleriid varchar(35) The sellers item ID.
c_distributoriid varchar(35) The distributors item ID.
c_article number(15) not null References t_article.
c_prcd varchar(1) not null Price code for labeling:
n = normal
p = promotion
i = introduction
s = sellout
c_cpr number Consumer price for labeling.
c_bpr number Buying price per PU.
c_ordered number not null Original order quantity in OU's.
c_confirmed number Confirmed quantity in OU's.
c_required number Confirmed quantity if it is not null, otherwise the order quantity.
c_confirmdate timestamp Date/time of confirmation.
c_ou varchar(1) not null Order unit:
L = LU
T = TU
C = CU
P = PU
c_pu varchar(1) not null Price unit:
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_unit varchar(1) not null When c_pu = 'P' the price unit, otherwise the order unit:
L = LU
T = TU
C = CU
p = piece
k = kilogram
l = liter
m = meter
s = square meter
c = cubic meter
c_tu_lu number not null Factor traded units in one logistic unit.
c_cu_tu number not null Factor consumer units in one traded unit.
c_pu_cu number not null Factor price units in one consumer unit.
c_remarks varchar(4000) Remarks text.
c_artid varchar(35) nut null Article ID.
c_artname varchar(35) nut null Article name.
c_lu varchar(10) Name/acronym of the logistic unit.
c_tu varchar(10) Name/acronym of the traded unit.
c_cu varchar(10) Name/acronym of the consumer unit.
c_stock_unit varchar(1) not null Main stock unit:
L = logistic unit
T = traded unit
C = consumer unit
c_intakecode varchar(1) not null Intake registration code:
c = count only
w = count and weigh.
c_deliveries number Number of deliveries.
c_shipped number Total OU shipped in deliveries.
c_arrived number Total OU accepted in deliveries.
c_entered number Total OU entered in deliveries.
c_cleared number Total OU cleared in deliveries.

v_users

Records of all available application users with the db user name.

Useful when querying audit tables for example.

Column Type Description
c_key number(15) not null Primary key.
c_dbuser varchar(35) Database user name: schema|c_id, except for the schema owner with c_id=0, where the db user is identical with the schema name.
c_id number(8) not null Unique user (employee) number.
c_pincode number(8) not null PIN code for login. The actual db user password is schema|c_pincode
c_name varchar(35) Full name of user
c_mnemonic varchar(10) Shorthand logogram of user
c_phone varchar(35) Phone number to contact user
c_email varchar(60) Email address to contact user

v_voicesumpickcolitems

View for the order items when distributing break bulk with voice applications.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_mergeclassname varchar(35) not null Spoken or regular merge class name in t_mergeclasses.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_partner number(15) not null References t_partners.
c_rowremarks varchar(4000) Order row remarks in t_orderrows.
c_partnerid varchar(35) not null Short or regular id in t_partners.
c_partnername varchar(35) not null Spoken or lowercase regular name in t_partners.
c_pnrcollective varchar(1) not null y = collective partner (separate orderrows when picking)
n = individual partner (combine orderrows when picking)
c_pnrcheckdigs varchar(35) Partner checkdigits in t_partners.
c_article number(15) not null References t_articles.
c_cpr number not null Consumer price in t_ordercols.
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_picked number not null Total picked order units.

v_voicesumpickcols

View for the order columns when distributing break bulk with voice applications.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_ordertripdate timestamp not null Date of trip departure, from t_ordertrips.
c_trip number(15) Delivery trip. References t_trips.
c_tripname varchar(35) not null Spoken trip name if available, or lowercase regular trip name otherwise from t_trips.
c_ordercol number(15) Individual articles: NULL
Collective articles: references t_ordercols
c_colremarks varchar(4000) Individual articles: NULL
Collective articles: remarks from t_ordercols
c_article number(15) not null References t_articles.
c_articleid varchar(35) not null Unique identifier in t_articles.
c_articlename varchar(35) not null Descriptive name in t_articles.
c_articleprio number not null Article picking priority from t_articles.c_pickprio
c_zone number(15) not null References t_zones.
c_cpr number not null Consumer price in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit. From t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit. From t_ordercols.
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_delivcode varchar(1) not null Delivery registration code:
c = count only
w = count and weigh.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units that need to get picked.

v_voicesumpickrowitems

View for the order items when picking from stock with voice applications.

Column Type Description
c_key number(15) not null Primary key in t_orderitems.
c_orderrow number(15) not null References t_orderrows.
c_ordercol number(15) not null References t_ordercols.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_planned number not null Planned delivery quantitiy in OU's in t_orderitems.
c_ordertrip number(15) not null References t_ordertrips.
c_partner number(15) not null References t_partners.
c_article number(15) not null References t_articles.
c_cpr number not null Consumer price in t_ordercols.
c_ou varchar(1) not null Ordered units in t_ordercols:
L = logistic unit
T = traded unit
C = consumer unit
P = price base unit
c_tu_lu number not null Factor traded units in one logistic unit in t_ordercols.
c_cu_tu number not null Factor consumer units in one traded unit in t_ordercols.
c_pu_cu number not null Factor price units in one consumer unit in t_ordercols.
c_colremarks varchar(4000) Order column remarks in t_ordercols.
c_articleid varchar(35) not null Unique identifier in t_articles.
c_articlename varchar(35) not null Descriptive name in t_articles.
c_artcollective varchar(1) not null y = collective article (separate ordercols when picking)
n = individual article (combine ordercols when picking)
c_pu varchar(1) not null Price base unit in t_articles:
p = piece
k = kilogram
l = liter
m = meter
c_delivcode varchar(1) not null Delivery registration code:
c = count only
w = count and weigh.
c_stock_mode varchar(1) not null Stock strategy mode:
r = regular
f = FIFO
q = quick. From t_articles.
c_picked number not null Total picked order units.

v_voicesumpickrows

View for the order rows when picking from stock with voice applications.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_ordertripdate timestamp not null Date of trip departure, from t_ordertrips.
c_trip number(15) Delivery trip. References t_trips.
c_tripname varchar(35) not null Spoken trip name if available, or lowercase regular trip name otherwise from t_trips.
c_orderrow number(15) Individual partners: NULL
Collective partners: references t_orderrows
c_rowremarks varchar(4000) Individual partners: NULL
Collective partners: remarks from t_orderrows
c_partner number(15) not null References t_partners.
c_partnerid varchar(35) not null Unique identifier in t_partners.
c_partnername varchar(35) not null Spoken or regular partner name in t_partners.
c_partnerprio number Picking priority from t_zonepartners. Lower number are higher priorities. Partners with same priority are ordered by c_id, c_key ascending. A null value is interpreted as lowest possible priority.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_mergeclassname varchar(35) not null Spoken or regular merge class name in t_mergeclasses.
c_planned number not null Total planned delivery quantitiy in OU's in t_orderitems.
c_picked number not null Total picked order units.
c_missing number not null Total yet missing order units that need to get picked.

va_*

Views where the master audit table t_audits is joined to the audit tables ta_*.

This views are for convenience when checking the table audits.

Column Type Description
c_audit_key number(15) not null Primary audit key.
c_audit_user varchar(128) not null Database user name.
c_audit_time timestamp not null Time stamp of the operation.
c_audit_op varchar(1) not null Operation performed:
i = insert
u = update
d = delete
c_* ? Other columns as in corresponding t_* table.

Comments and Discussion

Enter your comment. Wiki syntax is allowed: