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.

Allergen names to be emphasized on packing labels.

Available article attributes.

List of defined article fields.

Attributes assigned to distinct articles.

Bar codes belonging to the articles.

Article classification tree structure.

Field data of articles.

Labeler settings assigned to distinct articles for regular price.

Labeler settings assigned to distinct articles for introduction price.

Article parts of a compound article.

Pictures of the articles.

Labeler settings assigned to distinct articles for promotions.

Price history of an article.

Resources assigned to articles.

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

Labeler settings assigned to distinct articles for sellout price.

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

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.

Master table for collections of articles.

Countries used by the application.

Assignment of departments to groups.

Departments in the current datastore.

List of distribution keys, used in proration application.

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.

The EPCIS repository.

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.

Assignment of groups to application function permissions.

User groups, used for access control assignments.

Used for temporary key sets.

Supported labeler types.

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

Languages used by the application.

Utility table caching number of available license units.

Currently in use license tokens.

Entities that may be mixed in a single logistic unit.

Production methods used for article packing.

Translation texts for the production method names.

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

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.

Article orders within the order trips.

Locks held on order items.

Order item robot status record.

The sum of c_base + c_planned equates to t_orderitems.c_planned

Order items within the rows and columns.

Packages registered at order weighing.

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

Locks held on order rows.

Partner orders within the order trips.

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.

Master data: partners belonging to a order taking list.

Master data: order taking list definitions.

Order trip robot status record.

The trips for order delivery to buyers.

Locks held on open trips.

Order weighing records.

Origins used for article packing.

Translation texts for the origin names.

Attributes assigned to distinct packages.

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.

Picking zone assignments to report configurations.

Locks held on packing jobs.

Packing jobs within the packing production.

Locks held on pack productions.

Packing production master data.

Packing records within the packing job.

Attributes assigned to distinct partners.

Partner classification tree structure.

Departments assigned to distinct partners.

Partners list.

List of application function permissions.

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

Available package attributes.

Available partner attributes.

Production class tree structure.

Assignment of users to zones.

Producers used for article packing.

Productions master data.

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

Locks held on purchase items.

Purchase order item records.

Locks held on purchase orders.

Purchase order records.

Packages registered at purchase order weighing.

Purchase order weighing records.

Remark lists.

Access rights of groups on reports.

Report configurations for spooler.

Current report types:

Reports queued to get printed by the spooler.

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.

Central report inventory.

User settings on reports.

User settings on reports.

Locks held on order items.

Sale/distribution channels to be assigned to partners.

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

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.

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

Stores central user and system settings.

Shipping class tree structure.

Available stock attributes.

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

Attributes assigned to distinct stock locations.

Stock locations tree structure.

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

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.

Trip class tree structure.

Groups enabled for trips.

Partners settings per trip.

Trips master data.

Assignment of users to 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.

Value added taxes.

Assignment of users to zones.

Partners list per zone.

Picking zones.

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.

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.