dok:dbtables

Datastore tables

Status: 2.24.3

  • Constraints not strictly required for correct database operation are omitted in the physical database, for example NOT NULL's. Such constraints are enforced by the application code.
  • The BPS datastores are not designed for safe write by any non-BPS application or tool. While reading the BPS datastores is safe, writing is only allowed through the BPS applications and scripts. Any other write access voids the warranty and support conditions.
  • Tables starting with t_geko_ and t_tako_ are interface tables for the GEKO and TAKO robots do not belong to the BPS core.
  • Tables starting with ct_ are custom tables and do not belong to the BPS core. Ask your system integrator for documentation of such tables.

List of currently active (checked in) users.

Column Type Description
c_key number(15) not null Primary key.
c_sessionid number not null Database session ID.
c_user number(15) not null References t_users.
c_terminal varchar(35) not null Name of the terminal.
c_application varchar(4000) Name of executable or script respectively.
c_timestamp timestamp not null Time stamp of last action.

Allergen names to be emphasized on packing labels.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the allergen.

Available article attributes.

Column Type Description
c_key number(15) not null Primary key of the table.
c_name varchar(35) not null Name of the attribute.

List of defined article fields.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null ID of the article field (used as labeler placeholder ID for example).
c_name varchar(35) not null Descriptive name of the field.

Attributes assigned to distinct articles.

Column Type Description
c_article number(15) not null References t_articles.
c_attribute number(15) not null References t_artattributes.

Bar codes belonging to the articles.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null Article key, references t_articles.
c_prio number Priority: Lower number are higher priorities. Barcodes with same priority are sorted by unit, type and code ascending. The topmost CU barcode is used for packing jobs.
c_unit varchar(1) not null C = for consumer units
T = for traded units
L = for logistic units
c_type varchar(10) not null See Barcode scanning.
c_code varchar(4000) Barcode data.

Article classification tree structure.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Field data of articles.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null References t_articles.
c_artfield number(15) not null References t_artfields.
c_data varchar(4000) Field data.

Labeler settings assigned to distinct articles for regular price.

Column Type Description
c_article number(15) not null References t_articles.
c_label number(15) not null References t_labels.

Labeler settings assigned to distinct articles for introduction price.

Column Type Description
c_article number(15) not null References t_articles.
c_label number(15) not null References t_labels.

Article parts of a compound article.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null Compound article, references t_articles.
c_part number(15) not null Part article, references t_articles.
c_quantity number Quantity of this part.
c_unit varchar(1) not null L = LU
T = TU
C = CU
P = PU
k = kilogram
p = piece
l = liter
m = meter
s = square meter
c = cubic meter
c_per varchar(1) not null L = LU
T = TU
C = CU
P = PU
k = kilogram
p = piece
l = liter
m = meter
s = square meter
c = cubic meter

Pictures of the articles.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null Article key, references t_articles.
c_prio number Priority: Lower number are higher priorities. The topmost picture is shown in certain picking applications.
c_type varchar(10) Data file type. Any types can be read and written by Qt, for example JPG, PNG, TIF, GIF, BMP. The used type can be limited in the validator c_type.formats, by default pictures are stored as PNG if the picture has alpha transparency, or JPG otherwise.
c_size number not null Size of the data file contents in bytes.
c_data blob Data file contents.

Labeler settings assigned to distinct articles for promotions.

Column Type Description
c_article number(15) not null References t_articles.
c_label number(15) not null References t_labels.

Price history of an article.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null Article key, references t_articles.
c_start timestamp not null Starting date/time when price gets in effect.
c_dpr number not null Dealer price.
c_cpr number not null Consumer price.
c_code varchar(1) not null Price code:
n = normal
p = promotion
i = introduction
s = sellout

Resources assigned to articles.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null References t_articles.
c_resource number(15) not null References t_resources.
c_msecs number Machine seconds.
c_msecsper varchar(1) not null L = LU
T = TU
C = CU
P = PU
k = kilogram
p = piece
l = liter
m = meter
s = square meter
c = cubic meter
c_psecs number Person seconds.
c_psecsper varchar(1) not null L = LU
T = TU
C = CU
P = PU
k = kilogram
p = piece
l = liter
m = meter
s = square meter
c = cubic meter

Articles list. Articles are organized in a tree structure, where columns with NULL value inherit the parents value.

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_collection number(15) not null References t_collections.
c_department number(15) not null References t_departments.
c_entryzone number(15) References t_zones.
c_lotzone number(15) References t_zones.
c_packzone number(15) References t_zones.
c_id varchar(35) not null Article identifier (article number).
c_nid varchar(35) Needs identifier (for example Migros BoSS ID)
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_origin number(15) References t_origins.
c_producer number(15) References t_producers.
c_method number(15) References t_methods.
c_trip number(15) Main delivery trip. References t_trips.
c_stock_unit varchar(1) not null Main stock unit:
L = logistic unit
T = traded unit
C = consumer unit
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
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_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_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_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 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_fps_lus number Free pickable stock expressed in logistic units.
(Shadow column, read-only!).
c_fps_tus number Free pickable stock expressed in traded units.
(Shadow column, read-only!).
c_fps_cus number Free pickable stock expressed in consumer units.
(Shadow column, read-only!).

Labeler settings assigned to distinct articles for sellout price.

Column Type Description
c_article number(15) not null References t_articles.
c_label number(15) not null References t_labels.

Texts belonging to the articles, for consumer and buyer receipts or for goods labeling.

Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null Article key, references t_articles.
c_language number(15) nut null Language key, references t_languages.
c_name varchar(35) not null Name of the article.
c_description varchar(4000) Description text.
c_title varchar(4000) Title for labels.
c_subtitle varchar(4000) Subtitle for labels.
c_ingredients varchar(4000) Ingredient texts for labels.
c_nutrition varchar(4000) Nutrition facts text for labels.

Master audit table, holding audit entries for all tables serialized in sequence through the audit key.

For most cases it is more convenient to use the va_* views, where the audit records are joined to this table.

Column Type Description
c_key number(15) not null Primary key.
c_user varchar(128) not null Database user name.
c_time timestamp not null Time stamp of the operation.
c_table varchar(128) not null Audited table. Find the field contents in the corresponding ta_* table.
c_op varchar(1) not null Operation performed:
i = insert
u = update
d = delete
E = auditing was enabled
D = auditing was disabled

Master table for collections of articles.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the collection.

Countries used by the application.

Column Type Description
c_key number(15) not null Primary key.
c_code varchar(2) not null Two character country code as used in locales, for example:
CH = Switzerland
DE = Germany
FR = France
IT = Italy.
c_name varchar(35) not null Name of the country.

Assignment of departments to groups.

Column Type Description
c_department number(15) not null References t_departments.
c_group number(15) not null References t_groups.

Departments in the current datastore.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Unique identifier
c_name varchar(35) not null Name of the department

List of distribution keys, used in proration application.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(80) not null Name/description of the distribution key
c_data clob List of partners and quantities separated by semicolon and comma.
For example 106,1;107,2;108,3 are the three partners with key 106, 107 and 108 with the quantities 1, 2 and 3.

EPC events queued for transmission to the parent EPCIS system.

Child records are created for DELETE actions only. If there are no child records for a DELETE action, the whole parent EPC is to be deleted instead of only some child EPS's.

Column Type Description Parent Child
c_key number(15) not null Primary key. x x
c_parent number(15) Parent item. x
c_epc number(15) not null References t_epcs. x x
c_queued timestamp not null Timestamp of job queueing. x
c_action varchar(1) not null a = ADD
d = DELETE
o = OBSERVE
x
c_bizstep varchar(35) not null Business step, for example packing, picking x
c_quantity number not null Quantity to delete. x
c_uom varchar(3) Unit of measure for the quantity:
NULL for count
KGM, LTR, MTR etc for physical units.
x

The EPCIS repository.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_article number(15) References t_articles.
c_ordertrip number(15) References t_ordertrips.
c_batchkey varchar(4000) Combined data key defining the selection for the packing batch.
c_epc varchar(50) not null The EPC of this object. EPC's are stored with an abbreviated type prefix, for example:
grai:7613264.00307.100005001119
sgtin:7610200.001257.0
sscc:7617007.0123456789
c_eventtime timestamp not null Date and time of EPC creation.
c_readpoint varchar(35) Reading point ID.
c_location varchar(35) Business location ID.
c_transactions varchar(4000) Semicolon separated list of order ID's (from t_orderrows.c_orderid).
c_quantity number Quantity of the object.
c_uom varchar(3) Unit of measure for the quantity:
NULL for count
KGM, LTR, MTR etc for physical units.
c_partneriln varchar(35) International/global location number (ILN/GLN) of partner. (M-EPCIS 1.0 only)
c_partnerid varchar(35) Partner ID. (M-EPCIS 1.0 only)
c_partnername varchar(35) Partner name. (M-EPCIS 1.0 only)
c_packline number Packing line number.
c_destzone number Number from assigned partner attribute starting with zone….
c_orderid varchar(35) In case of packing this is the key of the first ordercol involved in the packing batch.
c_batchsize number # of units to be processed in current packing batch.
c_batchpos number Current progress position related to c_batchsize. Starts with 1.
c_deliverydate number Date/time of the order trip (planned delivery).

Global and partner exception days.

The exception days superseed the trip schedule dates in several applications. Top level partners inherit the global exception days, and child partners inherit the exception days from parent.

Column Type Description
c_key number(15) not null Primary key.
c_partner number(15) NULL for global exception days. References t_partners for partner exception days.
c_date timestamp not null Date of the exception day.
c_type varchar(1) not null Day type:
r = rest day
w = work day
s = as scheduled (partner days only)

Assignment of groups to application function permissions.

Column Type Description
c_group number(15) not null References t_groups.
c_permission number(15) not null References t_permissions.

User groups, used for access control assignments.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the group

Used for temporary key sets.

Column Type Description
c_key number(15) not null Key in a key set. Part of primary key.
c_id number(15) not null ID of the key set. Part of primary key.
c_actor number(15) not null References t_actors.

Supported labeler types.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Labeler ID, for example generic or lm2000.
c_data clob Global data of the labaler type. For example global print objects and labels. Usually XML content.

Labelerer settings, for example for a group of similar PLU's.

Column Type Description
c_key number(15) not null Primary key.
c_labeler number(15) not null References t_labelers.
c_name varchar(35) not null Name of the settings set.
c_type varchar(35) Subtype of generic labelers.
c_data clob The settings data. For example PLU print objects and labels. Usually XML content.

Languages used by the application.

Column Type Description
c_key number(15) not null Primary key.
c_code varchar(2) not null Two character language code as used in locales, for example:
de = German
en = English
fr = French
it = Italian.
c_name varchar(35) not null Name of the language.

Utility table caching number of available license units.

Column Type Description
c_licenser varchar(35) not null Licenser name. Primary
c_licgroup varchar(35) not null Licens group name. Primary key.
c_limit number not null Number of licensed units. -1 = unlimited.

Currently in use license tokens.

Column Type Description
c_key number(15) not null Primary key.
c_actor number(15) not null References t_actors.
c_licenser varchar(35) not null Licenser name. Primary key.
c_licgroup varchar(35) not null Licens group name. Primary key.
c_program varchar(35) not null Application using the token.
c_timestamp timestamp not null Time stamp of last action

Entities that may be mixed in a single logistic unit.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.
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.

Production methods used for article packing.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) ID of the production method, for example C.
c_name varchar(35) not null Name of the production method, for example Conventional.

Translation texts for the production method names.

Column Type Description
c_key number(15) not null Primary key.
c_method number(15) not null References t_methods.
c_language number(15) not null References t_languages.
c_name varchar(35) not null Translated name of the production method, for example Konventionell.

Neu logistic units under construction (not yet completed logistic units).

Column Type Description
c_key number(15) not null Primary key.
c_ordertrip number(15) not null References t_ordertrips.
c_orderrow number(15) References t_orderrows. When null, the LU is for a non-collective partner and may hold items of multiple orderrows.
c_partner number(15) not null References t_partners.
c_zone number(15) not null References t_zones.
c_mergeclass number(15) not null References t_mergeclasses.
c_actor number(15) References t_actors. Null when currently orphaned.
c_lastuser number(15) not null References t_users.
c_timestamp timestamp not null Time stamp of last action

For collective partners the LU is addressed by c_orderrow. For individual partners the LU is addressed by c_ordertrip and c_partner.

Locks held on order columns.

Column Type Description
c_ordercol number(15) not null References t_ordercols.
c_actor number(15) not null References t_actors.

Article orders within the order trips.

Column Type Description
c_key number(15) not null Primary key.
c_ordertrip number(15) not null References t_ordertrips.
c_article number(15) not null References t_articles.
c_dpr number Dealer price.
c_cpr number Consumer price.
c_prcd varchar(1) not null Price code:
n = normal
p = promotion
i = introduction
s = sellout
c_ou varchar(1) not null Ordered unit:
L = LU
T = TU
C = CU
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_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) Order column remarks.
c_ordered number not null Total original order quantity in OU's.
(Shadow sum from t_orderitems, read-only!)
c_planned number not null Total planned delivery quantitiy in OU's.
(Shadow sum from t_orderitems, read-only!)
c_picked number not null Total picked order units.
(Shadow sum from t_orderitems, read-only!)
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.
(Shadow sum from t_orderitems, read-only!)
c_picks number not null Total number of picks.
(Shadow sum from t_orderpicks, read-only!)
c_lus number not null Total picked expressed as logistic units.
(Shadow sum from t_orderpicks, read-only!)
c_tus number not null Total picked expressed as traded units.
(Shadow sum from t_orderpicks, read-only!)
c_cus number not null Total picked expressed as consumer units.
(Shadow sum from t_orderpicks, read-only!)
c_pus number not null Total picked expressed as price units.
(Shadow sum from t_orderpicks, read-only!)

Locks held on order items.

Column Type Description
c_orderitem number(15) not null References t_orderitems.
c_actor number(15) not null References t_actors.

Order item robot status record.

Column Type Description
c_key number(15) not null Primary key.
c_ordertriprobot number(15) not null References t_ordertriprobots.
c_orderitem number(15) not null References t_orderitems.
c_mergeclass number(15) not null References t_mergeclasses.
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.

The sum of c_base + c_planned equates to t_orderitems.c_planned

Order items within the rows and columns.

Column Type Description
c_key number(15) not null Primary key.
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.
c_planned number not null Planned delivery quantitiy in OU's.
c_remarks varchar(4000) Order item remarks.
c_originfo varchar(4000) Interface data of origin system.
c_picks number not null Total number of picks.
(Shadow sum from t_orderpicks, read-only!)
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 as logistic units.
(Shadow sum from t_orderpicks, read-only!)
c_tus number not null Total picked expressed as traded units.
(Shadow sum from t_orderpicks, read-only!)
c_cus number not null Total picked expressed as consumer units.
(Shadow sum from t_orderpicks, read-only!)
c_pus number not null Total picked expressed as price units.
(Shadow sum from t_orderpicks, read-only!)
c_ou varchar(1) not null Ordered unit:
L = LU
T = TU
C = CU
(Shadow from t_ordercols, read-only!)

Packages registered at order weighing.

Column Type Description
c_key number(15) not null Primary key
c_orderweighing number(15) not null References t_orderweighings
c_package number(15) not null References t_packages
c_quantity number # of packages

Order picks within the items. Usually 1 record = 1 LU.

Column Type Description
c_key number(15) not null Primary key.
c_orderitem number(15) not null References t_orderitems.
c_stock number(15) References t_stock.
c_luid number Logistic unit ID of pick (null = unknown).
c_luid6 number Lowest 6 digits of c_luid.
c_packline number # of packing line (if applicable).
c_lus number # of whole logistic units.
c_tus number # of whole traded units not included in lus.
c_cus number # of whole consumer units not included in lus or tus.
c_pus number not null Total quantity of price units in all lus, tus and cus.
c_tu_lu number not null # of traded units in one logistic unit.
c_cu_tu number not null # of consumer units in one traded unit.
c_lotid varchar(4000) A comma separated list of lot ID's.
c_sscc varchar(4000) Reference ID of delivery container (SSCC).
c_grai varchar(4000) GRAI code of the pick container.
c_pickby number(15) not null Picking user. References t_users.
c_pickdate timestamp not null Date/time of picking.
c_delivery number(15) Delivery ID (null = not yet delivered).
c_delivby number(15) Delivering user. References t_users.
c_delivdate timestamp not null Date/time of delivery.
c_invoice number(15) Invoice ID (null = not yet invoiced).
c_invby number(15) Invoicing user. References t_users.
c_invdate timestamp not null Date/time of invoicing.

Locks held on order rows.

Column Type Description
c_orderrow number(15) not null References t_orderrows.
c_actor number(15) not null References t_actors.

Partner orders within the order trips.

Column Type Description
c_key number(15) not null Primary key.
c_ordertrip number(15) not null References t_ordertrips.
c_partner number(15) not null References t_partners.
c_orderby number(15) not null References t_users.
c_ordercd varchar(1) not null n = normal
r = reorder
a = assignment
c_orderid varchar(35) not null Customers order ID.
c_orderdate timestamp not null Date of (first) ordering.
c_remarks varchar(4000) Order row remarks.
c_origin varchar(35) Name of the origin system. NULL = BPS itself.
c_originfo varchar(4000) Interface data of origin system.
c_ordered number not null Total original order quantity in OU's.
(Shadow sum from t_orderitems, read-only!)
c_planned number not null Total planned delivery quantitiy in OU's.
(Shadow sum from t_orderitems, read-only!)
c_picked number not null Total picked order units.
(Shadow sum from t_orderitems, read-only!)
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.
(Shadow sum from t_orderitems, read-only!)
c_picks number not null Total number of picks.
(Shadow sum from t_orderpicks, read-only!)
c_lus number not null Total picked expressed as logistic units.
(Shadow sum from t_orderpicks, read-only!)
c_tus number not null Total picked expressed as traded units.
(Shadow sum from t_orderpicks, read-only!)
c_cus number not null Total picked expressed as consumer units.
(Shadow sum from t_orderpicks, read-only!)
c_pus number not null Total picked expressed as price units.
(Shadow sum from t_orderpicks, read-only!)

Locks set on order taking list partners.

When the status of the order taking list partner is open, there is no referencing record in this table.

Column Type Description
c_actor varchar(35) NULL when status is completed.
References t_actors when status is in work.
c_ordertakinglistpnr varchar(35) not null References t_ordertakinglistpnrs.

Master data: partners belonging to a order taking list.

Column Type Description
c_key number(15) not null Primary key.
c_ordertakinglist varchar(35) not null References t_ordertakinglists.
c_partner varchar(35) not null References t_partners.
c_calltime number not null Calling time as hours.minutes.

Master data: order taking list definitions.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Descriptive name of the list.
c_trip1 number(15) NULL if trip 1 not used.
References t_ordertrips if trip 1 used.
c_mindays1 number not null Minimum delivery time in days for trip 1.
0 = today, 1 = tomorrow etc.
c_fromstock1 varchar(1) not null Use selling stock for trip 1: n = no y = yes
c_trip2 number(15) NULL if trip 2 not used.
References t_ordertrips if trip 2 used.
c_mindays2 number not null Minimum delivery time in days for trip 2.
0 = today, 1 = tomorrow etc.
c_fromstock2 varchar(1) not null Use selling stock for trip 2: n = no y = yes

Order trip robot status record.

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_status varchar(1) not null u = unsent
s = sent
a = active
f = finished

The trips for order delivery to buyers.

Column Type Description
c_key number(15) not null Primary key.
c_status varchar(1) not null n = new, being created
r = ready for working
a = actively picking
c = completed
h= History, archived
c_date timestamp not null Date of trip departure.
c_trip number(15) not nul References t_trips.
c_remarks varchar(4000) Open trip remarks.

Locks held on open trips.

Column Type Description
c_ordertrip number(15) not null References t_ordertrips.
c_actor number(15) not null References t_actors.

Order weighing records.

Column Type Description
c_key number(15) not null Primary key.
c_orderpick number(15) not null References t_orderpicks.
c_timestamp timestamp not null Date/time of weighing.
c_id varchar(35) ID info such as animal number, ear mark etc.
c_lus number # of whole logistic units.
c_tus number # of whole traded units not included in lus.
c_cus number # of whole consumer units not included in lus or tus.
c_net number not null Net weight in kg.
c_tare number Total tare in kg.
c_weighed varchar(1) n = Manual entry y = Weight from scales

Origins used for article packing.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) ID of the origin, for example CH.
c_name varchar(35) not null Name of the origin, for example Switzerland.
c_import varchar(1) Import flag for reporting:
n = Native product from home country
y = Imported from foreign country

Translation texts for the origin names.

Column Type Description
c_key number(15) not null Primary key.
c_origin number(15) not null References t_origins.
c_language number(15) not null References t_languages.
c_name varchar(35) not null Translated name of the origin, for example Svizzera.

Attributes assigned to distinct packages.

Column Type Description
c_package number(15) not null References t_packages.
c_attribute number(15) not null References t_pkgattributes.

Package records (bins, barrels, boxes, pallets etc.).

The factors packages per layer, and layers per container refer to the regular container of the package, which is a palette in most applicationa.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Unique identifier.
c_name varchar(35) not null Name of the package.
c_weight number not null Weight in kilogram.
c_width number not null Width (shorter side) in meters.
c_length number not null Length (longer side) in meters.
c_height number not null Total height in meters.
c_socket number not null Socket height in meters. When stacking, the socket of the upper bin sinks into the lower bin, so the total stack height is n * height - (n-1) * socket.
c_pkg_lyr_in number not null Packages per layer inbound e.g. when receiving from suppliers or manufacturers.
c_lyr_ctr_in number not null Layers per container inbound e.g. when receiving from suppliers or manufacturers.
c_pkg_lyr_out number not null Packages per layer outbound e.g. when delivering to customers or branches.
c_lyr_ctr_out number not null Layers per container inbound e.g. when receiving to customers or branches.
c_press number not null Allowed pressing force.
-2 = very low
-1 = low
0 = medium
1 = high
2 = very high
c_labeltype varchar(1) not null null, = do not print any label
A, B, C = label type to print

Picking zone assignments to report configurations.

Column Type Description
c_package number(15) not null References t_packages.
c_zone number(15) not null References t_zones.

Locks held on packing jobs.

Column Type Description
c_packjob number(15) not null References t_packjobs.
c_actor number(15) not null References t_actors.
c_exclusive varchar(1) not null Lock is exclusive: n = no, y = yes

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_article number(15) not null References t_articles.
c_cpr number Consumer price.
c_prcd varchar(1) not null Price code:
n = normal
p = promotion
i = introduction
s = sellout
c_packunit varchar(1) not null Packed unit:
L = LU
T = TU
C = CU
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_quantity number Total units to pack
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.
c_recs number not null Total number of packrecs.
(Shadow sum from t_packrecs, read-only!)
c_lus number not null Total packed expressed as logistic units.
(Shadow sum from t_packrecs, read-only!)
c_tus number not null Total packed expressed as traded units.
(Shadow sum from t_packrecs, read-only!)
c_cus number not null Total packed expressed as consumer units.
(Shadow sum from t_packrecs, read-only!)
c_pus number not null Total packed expressed as price units.
(Shadow sum from t_packrecs, read-only!)

Locks held on pack productions.

Column Type Description
c_packprod number(15) not null References t_packprods.
c_actor number(15) not null References t_actors.

Packing production master data.

Column Type Description
c_key number(15) not null Primary key.
c_status varchar(1) not null n = new, being created
r = ready for working
a = actively picking
c = completed
h= history, archived
c_date timestamp not null Date of production.
c_prod number(15) not nul References t_prods.
c_remarks varchar(4000) Pack production remarks.
c_settings clob Settings used when generating the pack production.

Packing records within the packing job.

Column Type Description
c_key number(15) not null Primary key.
c_packjob number(15) not null References t_packjobs.
c_stock number(15) References t_stock.
c_origin number(15) References t_origins.
c_producer number(15) References t_producers.
c_method number(15) References t_methods.
c_packline number Packing line number.
c_lotid varchar(4000) Comma separated list of lot ID's.
c_grai varchar(4000) GRAI code of the packed container.
c_lus number # of whole logistic units.
c_tus number # of whole traded units not included in lus.
c_cus number # of whole consumer units not included in lus or tus.
c_pus number not null Total quantity of price units in all lus, tus and cus.
c_tu_lu number not null # of traded units in one logistic unit.
c_cu_tu number not null # of consumer units in one traded unit.
c_packby number(15) not null Packing user. References t_users.
c_packdate timestamp not null Date/time of packing.
c_sentby number(15) User sending the record to host system. References t_users.
c_sentdate timestamp not null Date/time of sending to host system.

Attributes assigned to distinct partners.

Column Type Description
c_partner number(15) not null References t_partners.
c_attribute number(15) not null References t_pnrattributes.

Partner classification tree structure.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Departments assigned to distinct partners.

Column Type Description
c_partner number(15) not null References t_partners.
c_department number(15) not null References t_departments.

Partners list.

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_id varchar(35) not null Unique internal identifier: „partner number“
c_shortid varchar(35) Short partner ID to be used for certain applications (for example pick-by-voice). When left empty/null, c_id gets used instead.
c_checkdigs varchar(35) Checkdigits to be used for pick-by-voice applications for example.
c_iln varchar(35) not null International/global location number (ILN/GLN) for particimation in electronic data interchange (EDI).
c_name varchar(35) not null Name of the partner. (Internal)
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_collective varchar(1) not null y = collective partner (separate orderrows when picking)
n = individual partner (combine orderrows when picking)
c_status varchar(1) not null a = active
l = locked
d = marked for deletion.
c_partnerclass number(15) References t_partnerclasses.
c_salechannel number(15) References t_salechannels.
c_closeprio number Closure priority. Lower number are higher priorities. Partners with same priority are sorted by c_client, c_ident, c_key ascending. A null value is interpreted as lowest possible priority.
c_barcodetype varchar(10) See Barcode scanning.
c_barcode varchar(4000) Barcode data for partner.
c_labeltext varchar(4000) Text to print on labels.
c_icon blob Icon data file contents.
c_address varchar(4000) Address (delivery).
c_zipcode varchar(35) ZIP code.
c_city varchar(35) City
c_country number(15) not null References t_countries.
c_language number(15) not null Billing Language, references t_languages.
c_labellang1 number(15) First article labeling language, references t_languages.
c_labellang2 number(15) Second article labeling language, references t_languages.
c_labellang3 number(15) Third article labeling language, references t_languages.
c_phone varchar(35) Phone number.
c_fax varchar(35) Telefax number.
c_email varchar(35) Email address.
c_billmode varchar(1) not null Billing mode:
l = when logistic unit is complete
t = at trip closure
d = daily
w = weekly
m = monthly
c = custom period.
c_debtorid varchar(35) Debtor ID, referencing billing application.
c_vatregno number Value added tax registration number.
c_pricecode varchar(1) not null Price code for billing:
d = dealer price
c = consumer price
c_detaildisc number Detail position discount, in percent.
c_invoicedisc number Invoice total discount, in percent.
c_refund number Periodic refund in percent.
c_postagecode varchar(1) not null n = none
k = per kg
d = per delivery
c_postage number Postage amount.
c_invoiceto varchar(1) not null t = this partner
p = this partners parent
i = c_invoicepartner
c_invoicecopies number(i) Number of printed invoice copies.
c_invoiceparther number(15) Bill to this partner when c_invoiceto = i. References t_partners.
c_reportto varchar(1) not null t = this partner
p = this partners parent
r = c_reportpartner
c_reportcopies number(i) Number of printed report copies.
c_reportpartner number(15) Report to this partner when c_reportto = i. References t_partners.
c_collection number(15) Article collection available to this partner. References t_collections.
c_embarkpoint varchar(35) Embark point for this partner.
c_conveyance varchar(35) Internal conveyance route.
c_pickremarks varchar(4000) Remarks shown when picking.

List of application function permissions.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the permission

Assignment of picking zone and merge class to trip-, article- and partnerclasses.

Column Type Description
c_tripclass number(15) References t_tripclasses. Valid for any trip when null.
c_articleclass number(15) References t_articleclasses. Valid for any articleclass when null.
c_partnerclass number(15) References t_partnerclasses. Valid for any partnerclass when null.
c_zone number(15) not null Reference to t_zones.
c_mergeclass number(15) not null Reference to t_mergeclasses.

Available package attributes.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the attribute.

Available partner attributes.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the attribute.

Production class tree structure.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Assignment of users to zones.

Column Type Description
c_prod number(15) not null References t_prods.
c_group number(15) not null References t_groups.

Producers used for article packing.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null ID of the producer, for example 125.
c_name varchar(35) not null Name of the producer, for example RODI Fructus.

Productions master data.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null ID of the production, for example P01.
c_name varchar(35) not null Name of the production, for example Vegetable packing.
c_initstatus varchar(1) not null Initial status:
n = New
r = Ready
a = Active
c_prodclass number(15) not null References t_prodclasses.
c_fgcolor varchar(35) not null Foreground color name.
c_bgcolor varchar(35) not null Background color name.
c_schedule varchar(350) not null Schedule times in CRON syntax.

Purchase order item delivery records.

Depending on the timestamps the status of the delivery is considered as:

  • Shipped when c_shipdate is not null
  • Arrived when c_arrivaldate is not null
  • Entered when c_entrydate is not null
  • Cleared when c_clearingdate is not null
Column Type Description
c_key number(15) not null Primary key.
c_purchaseitem number(15) not null References t_purchaseitems.
c_blocked varchar(1) not null Delivery blocked:
n = no
y = yes
c_shipdate timestamp Date/time of shipping
c_deliverydate timestamp Estimated delivery date
c_deliveryid varchar(35) Delivery ID (to be found on the receipt.)
c_arrivaldate timestamp Arrival date/time
c_arrivalby number(15) User accepting the arrival. References t_users.
c_entrydate timestamp Date/time of entry processing.
c_entryby number(15) User processing the entry. References t_users.
c_clearing number Batch run id of clearing processing/transmission. Created by sequence s_clearings.
c_clearingdate timestamp Date/time of clearing processing.
c_clearingby number(15) User processing the clearing. References t_users.
c_carrier varchar(100) Final carrier and/or vehicle bringing the delivery.
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_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_lus number # of whole logistic units.
c_tus number # of whole traded units not included in lus.
c_cus number # of whole consumer units not included in lus or tus.
c_pus number not null Total quantity of price units in all lus, tus and cus.
c_deduction number Deduction in price units (delivered, but not accepted PU's).
c_retention number Retention in percent.
c_remarks varchar(4000) Remarks text.
c_stock number(15) Stock the entry was made to. References t_stock.

Locks held on purchase items.

Column Type Description
c_purchaseitem number(15) not null References t_purchaseitems.
c_actor number(15) not null References t_actors.

Purchase order item records.

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 Status:
o = open
c = completed
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_articles.
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_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_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_originfo varchar(4000) Interface data of origin system.

Locks held on purchase orders.

Column Type Description
c_purchaseorder number(15) not null References t_purchaseorders.
c_actor number(15) not null References t_actors.

Purchase order records.

Column Type Description
c_key number(15) not null Primary key.
c_zone number(15) not null References t_zones.
c_status varchar(1) not null Status:
n = new, being created
r = ready for working
a = active, can be processed in entry zone
c = entry completed ok
h= history (cleared and archived)
c_orderid varchar(35) Our own order ID.
c_deliverydate timestamp not null Target delivery date/time.
c_seller number(15) not null Selling partner, references t_partners.
c_selleroid varchar(35) Sellers order ID.
c_sellerom varchar(1) not null Seller order mode:
n = none (not yet known)
p = phone call
f = fax
e = e-mail
o= online, EDI
c_selleros varchar(1) not null Seller order transmission status (fax/email/EDI):
u = unsent
t = transmission enabled
s = sent
c_sellerod timestamp Seller ordering date/time.
c_distributor number(15) Distributor partner, references t_partners.
c_distributoroid varchar(35) Distributors order ID.
c_distributorom varchar(1) not null Distributor order mode:
n = none
p = phone call
f = fax
e = e-mail
o= online, EDI
c_distributoros varchar(1) not null Distributor order transmission status (fax/email/EDI):
u = unsent
t = transmission enabled
s = sent
c_distributorod timestamp Distributor ordering date/time.
c_remarks varchar(4000) Remarks text.
c_origin varchar(35) Name of the origin system. NULL = BPS itself.
c_originfo varchar(4000) Interface data of origin system.
c_createby number(15) User ID of the purchaser or record creator. References t_users.
c_createdate timestamp not null Record creation date/time.
c_printby number(15) User ID who printed the job in the goods entry program. References t_users.
c_printdate timestamp Job printing date/time. Null = not yet printed.

Packages registered at purchase order weighing.

Column Type Description
c_key number(15) not null Primary key
c_purchaseweighing number(15) not null References t_purchaseweighings
c_package number(15) not null References t_packages
c_quantity number # of packages

Purchase order weighing records.

Column Type Description
c_key number(15) not null Primary key.
c_purchasedelivery number(15) not null References t_purchasedeliveries.
c_timestamp timestamp not null Date/time of weighing.
c_id varchar(35) ID info such as animal number, ear mark etc.
c_lus number # of whole logistic units.
c_tus number # of whole traded units not included in lus.
c_cus number # of whole consumer units not included in lus or tus.
c_net number not null Net weight in kg.
c_tare number Total tare in kg.
c_deduction number Deduction in percent of net weight
c_weighed varchar(1) n = Manual entry y = Weight from scales
c_quality varchar(35) Quality codes and remarks
c_temp varchar(4000) Comma separated list of temperatures measured.
c_ph varchar(4000) Comma separated list of pH values measured.

Remark lists.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null ID of the remarks.
c_remarks varchar(4000) Remarks text.
c_user number(15) The user ID for private settings, or NULL for public settings. References t_users.
c_application varchar(35) not null Application or list identifier. All records with same c_application belong to one list.

Access rights of groups on reports.

Column Type Description
c_group number(15) not null References t_groups.
c_report number(15) not null References t_reports.
c_access number(1) not null 0 = no access (*)
1 = read only
2 = read and write

(*) Same when no record exists for a certain group/record

Report configurations for spooler.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Printer number or other config ID.
c_name varchar(35) not null Descriptive name.
c_type varchar(35) not null Report type identifier, see table below
c_report number(15) not null References t_reports.
c_title varchar(35) not null Customized report title.

Current report types:

Type Application
LU label Logistic unit label
TU label Traded unit label
CU label Consumer unit label
Stock label Stock label
Entry weight label Single weight label at entry
Entry receipt Purchase delivery receipt at entry

Reports queued to get printed by the spooler.

Column Type Description
c_key number(15) not null Primary key.
c_reportconfig number(15) not null Report configuration, references t_reportconfigs.
c_selections clob Table selections: Each line holds a selection in the form „name:key,key,key,…“.

The selections are accessable within the report script as elements of object report.selections.
Check the availability for example as if (report.selections) ….
c_data clob ECMAScript compliant report data. See examples below.

The data is accessable within the report script as object report.data.
Check the availability for example as if (report.data) ….
c_submitter varchar(50) not null Submitter information: user-mnemonic@terminal.
c_queued timestamp not null Time stamp when the job was queued by the submitter.
c_status varchar(1) not null a = active
s = suspended
c_message varchar(4000) Message when job was suspended.

Examples for data

Single values:

123
'bar'

Array of objects:

[
  { foo: 'text a', bar: 123 },
  { foo: 'text b', bar: 234 }
]

A single object:

({
    foo: 'footext',
    bar: 123,
    tsp: new Date(2011,3,11,10,47,5,325)
})

Note that the single object needs to be enclosed in parentheses, because otherwise javascript will mistake the opening curly bracket as block opening, instead of object literal.

See also Report Reference

Picking zone assignments to report configurations.

Column Type Description
c_reportconfig number(15) not null References t_reportconfigs.
c_zone number(15) not null References t_zones.

Central report inventory.

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 'f' = folder, 'r' = report
c_data clob The report as XML.

User settings on reports.

Column Type Description
c_key number(15) not null Primary key.
c_report number(15) not null References t_reports.
c_title varchar(35) not null Customized report title.
c_user number(15) The user ID for private settings, or NULL for public settings. References t_users.
c_application varchar(35) not null Name of the application this settings are for.

User settings on reports.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Resource ID.
c_name varchar(35) not null Resource descriptive name.
c_packline number Associated packing line number.
c_prodlist varchar(1) not null n = Not included in production lists
y = Included in production lists

Locks held on order items.

Column Type Description
c_zone number(15) not null References t_zones.
c_actor number(15) not null References t_actors.

Sale/distribution channels to be assigned to partners.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Sale classes to be assigned to articles (for example in Migros: BoSS).

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Reservations for selling stock.

The records hold not only true reservations, but is also used as temporary memory of the order quantities and remarks entered while in the working page of the order take by list application. Whether the amounts of c_quantity1 or c_quantity2 are included in c_reserved depends if trip 1 or trip 2 are defined to use the selling stock in t_ordertakinglists.

Column Type Description
c_actor number(15) not null References t_actors.
c_article number(15) not null References t_articles.
c_quantity1 number Quantity for trip 1.
c_quantity2 number Quantity for trip 2.
c_remarks varchar(4000) Item remarks.
c_sellstock number New sell stock immediately after reservation (for display only)
c_reserved number Reserved quantity

Selling stock per article, used by the order taking by list application.

Column Type Description
c_article number(15) not null References t_articles.
c_stock number not null Stock quantity in articles main stock units.
See c_stock_unit in t_articles.

Stores central user and system settings.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_user number(15) User key, or null if a system setting. References t_users.
c_name varchar(35) not null Group or key name, may not contain any slashes /
c_type varchar(1) not null 'g' = group, 'k' = value key
c_value varchar(4000) The value associated to the key.

Shipping class tree structure.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Available stock attributes.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the attribute.

Stock content data.

Note that c_lus, c_tus, c_cus and c_pus represent the quantity in the respective unit. To get the total stock quantity all values must be taken into account, for example to calculate the total stock in PU's:

tot_pus = ((c_lus*c_tu_lu+c_tus)*c_cu_tu+c_cus)*c_pu_cu+c_pus
Column Type Description
c_key number(15) not null Primary key.
c_article number(15) not null References t_articles.
c_stocklocation number(15) not null References t_stocklocations.
c_stockreason number(15) not null References t_stockreasons.
c_prio number not null Priority for picking: Lower number are higher priorities. Stocks with same priority are sorted by key ascending.
c_status varchar(1) not null 'f' = free, 'r' = reserved, 'q' = quality, 'l' = locked
c_type varchar(1) not null 'p' = physical, 'v' = virtual
c_indate timestamp Date and time of entry.
c_expdate timestamp Expire date.
c_cpr number Consumer price for price marked articles.
c_lotid varchar(35) LOT identifier.
c_lus number not null # of logistic units on stock.
c_tus number not null # of traded units on stock.
c_cus number not null # of consumer units on stock.
c_tu_lu number not null # of traded units in one logistic unit.
c_cu_tu number not null # of consumer units in one traded unit.
c_pu_cu number not null # of price units in one consumer unit.
c_zone number(15) not null References t_zones.
(Shadow from t_stocklocations, read-only!)
c_loc_status varchar(1) not null o = open (stockpiling and drawing)
s = stockpiling only
d = drawing only
c = closed (no stockpiling, no drawing).
(Shadow from t_stocklocations, read-only!)

Attributes assigned to distinct stock locations.

Column Type Description
c_stocklocation number(15) not null References t_stocklocations.
c_attribute number(15) not null References t_stkattributes.

Stock locations tree structure.

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_zone number(15) not null References t_zones.
c_id varchar(35) not null Unique identifier.
c_status varchar(1) not null o = open (stockpiling and drawing)
s = stockpiling only
d = drawing only
c = closed (no stockpiling, no drawing).
c_counted timestamp not null Time of last coiunting. Only relevant if c_counter is not null.
c_counter number(15) Who did last counting. References t_users.

Stock change reasons.

The „manual“ change types are presented to the users when they do pure stock operations. There may be any number of change reasons with same manual type.

All other types are bound to specific application functions doing automatic stock operations. Of each of those types, there should be exactly one if that particular automatic operation shall be able to run. In case there are more than one of those types, the application will order them by c_id, c_key and use the first of that order.

plus change = applicable when the total quantity of PU on stock is increased
minus change = applicable when the total quantity of PU on stock is decreased
no change = applicable when the total quantity of PU on stock stays equal

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null ID of the change reason, for example for reporting to upper chain systems.
c_name varchar(35) not null Name/text of the changing reason.
c_type varchar(1) not null Type of operation, see below

Change type codes:

'm' = any manual change plus, minus or no change.
'm+' = manual change plus only
'm-' = manual change minus only
'g+' = general storage
'g-' = general withdraw
'i+' = supply intake
'i-' = supply return
'p+' = pick return
'p-' = pick take
's0' = stocktaking ok confirmation
's+' = stocktaking plus correction
's-' = stocktaking minus correction
'a' = automatic sync

Temporary stock reservations applied during picking.

When the actor terminates the session, his records get automatically deleted.

Column Type Description
c_orderitem number(15) not null References t_orderitems.
c_stock number(15) not null References t_stock.
c_actor number(15) not null References t_actors.
c_lus number not null Number of logical units reserved.
c_tus number not null Number of traded units reserved.
c_cus number not null Number of consumer units reserved.

Trip class tree structure.

Column Type Description
c_key number(15) not null Primary key.
c_parent number(15) Parent item. Root items are null.
c_name varchar(35) not null Name of the class element. May not contain any slash.

Groups enabled for trips.

Column Type Description
c_trip number(15) not null References t_trips.
c_group number(15) not null References t_groups.

Partners settings per trip.

Column Type Description
c_key number(15) not null Primary key.
c_trip number(15) not null References t_trips.
c_partner number(15) not null References t_partners.
c_prio number Picking priority. Lower number are higher priorities. Partners with same priority are ordered by c_partner ascending. A null value is interpreted as lowest possible priority.
c_htime number not null Handover time in hours.minutes. At this time the order picking should be finished.
c_restmode varchar(1) not null How to handle non full LU's for the partner by robots:
n = no further picking.
m = manually continue picking to fill up the LU.

Trips master data.

Column Type Description
c_key number(15) not null Primary key.
c_id varchar(35) not null Unique identifier.
c_name varchar(35) not null Descriptive name.
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_adhocpicking varchar(1) not null n = ad hoc picking disallowed
y = ad hoc picking allowed.
c_initstatus varchar(1) not null Initial status for new orders. Set by order desk application or import scripts when the order creation process is finished.
n = new, being created
r = ready for working
a = actively picking
c_carriertrip varchar(35) not null Carriers trip name.
c_tripclass number(15) not null Class, references t_tripclasses.
c_fgcolor varchar(35) not null Foreground color name.
c_bgcolor varchar(35) not null Background color name.
c_schedule varchar(350) not null Schedule times in CRON syntax.
c_ptime number not null Preload time in hours.minutes. How much the picking should be finished in advance of the trips schedule time.
c_fillgrade number not null Percentage of actual filling of a logistic unit, to handle the unit as „full“.
c_robotmode varchar(1) not null Robot working mode.
o = order waves
l = LU waves
c_robotupdt varchar(1) not null How new created order items are sent to a robot, in case some items of the order trip were already send to the robot before.
m = manual by user action
i = Automatic for items of partner orders where c_origin is empty (internal orders created by BPS itself)
a = All automatic

Assignment of users to groups.

Column Type Description
c_user number(15) not null References t_users.
c_group number(15) not null References t_groups.

Records of all available application users.

The actual database user name is schema|c_id, except for the schema owner with c_id=0, where the database user name is identical with the schema name. See v_users column c_dbuser.

Column Type Description
c_key number(15) not null Primary key.
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

Value added taxes.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name/description of the tax rate.
c_rate number not null Tax rate in percent

Assignment of users to zones.

Column Type Description
c_zone number(15) not null References t_zones.
c_group number(15) not null References t_groups.

Partners list per zone.

Column Type Description
c_key number(15) not null Primary key.
c_zone number(15) not null References t_zones.
c_partner number(15) not null References t_partners.
c_pickplace number Picking place number, for example for MIFA robot.
c_prio number Picking priority. 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.

Picking zones.

Column Type Description
c_key number(15) not null Primary key.
c_name varchar(35) not null Name of the picking zone.
c_epclocation varchar(35) EPCIS bizLocation ID.
c_pickmode varchar(35) Name of the picking mode in this zone.
c_entrymode varchar(35) Name of the goods receipt mode in this zone.
c_lotmode varchar(35) Name of the lot management mode in this zone.
c_packmode varchar(35) Name of the packing mide in this zone.
c_direction varchar(1) not null f = Forward
r = Reverse
b = Bidirectional
c_pictures varchar(1) not null n = Do not use pictures
y = Use pictures if available
c_strictpartners varchar(1) not null Restrict order changes to this zone to partners defined in this zone, omit items of partners not defined in this zone.
n = no, y = yes
c_storagedays number Default # of storage days, if c_storagedays in t_articles is empty

Audit tables of the corresponding t_* tables. These tables hold the new content of the corresponling t_* table for update and insert operations, and the old content for delete operations.

It is more convenient to use the va_* views, where the audit records are joined to the table t_audits.

Column Type Description
c_audit number(15) not null References t_audits which holds the audit informations about user, time and operation performed.
c_* ? Other columns as in corresponding t_* table.

Logging tables are similar to audit tables and track changes. The main differences are the columns which are stored as old and new values. Also, the logging trigges are ment stay active all time.

Column Type Description
c_key number(15) not null Primary key of the log record itself.
c_user number(15) not null References t_users, but without db constraint.
c_time timestamp not null Time stamp of the operation.
c_old_* ? Old values of columns c_* in corresponding table t_*.
c_new_* ? New values of columns c_* in corresponding table t_*.
  • dok/dbtables.txt
  • Zuletzt geändert: 27.03.2021 13:04
  • von ibk