====== Datastore tables ======
===== General Notes =====
* 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. They are documented in the [[de:bps2:gekotakospec|interface description]].
* Tables starting with ''ct_'' are custom tables and do not belong to the BPS core. Ask your system integrator for documentation of such tables.
===== Reference =====
==== t_actors ====
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. |
==== t_artattributes ====
Available article attributes.
^ Column ^ Type ^ Description ^
| c_key | number(15) not null | Primary key. |
| c_name | varchar(35) not null | Name of the attribute. |
==== t_artfields ====
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. |
==== t_articleattributes ====
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]]. |
==== t_articlebarcodes ====
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 [[bps2:scanning|Barcode scanning]]. |
| c_code | varchar(4000) | Barcode data. |
==== t_articleclasses ====
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. |
==== t_articlecollections ====
Collections assigned to distinct articles.
^ Column ^ Type ^ Description ^
| c_article | number(15) not null | References [[#t_articles]]. |
| c_collection | number(15) not null | References [[#t_collections]]. |
==== t_articlefields ====
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. |
==== t_articlelabels ====
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]]. |
==== t_articleilabels ====
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]]. |
==== t_articleparts ====
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 |
==== t_articlepictures ====
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. |
==== t_articleplabels ====
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]]. |
==== t_articleprices ====
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 |
==== t_articleresources ====
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 |
==== t_articles ====
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_department | number(15) not null | References [[#t_departments]]. |
| c_entryzone | number(15) | 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:\\ ''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!). |
==== t_articleslabels ====
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]]. |
==== t_articletexts ====
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. |
==== t_audits ====
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 [[dbviews#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 |
==== t_collections ====
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. |
==== t_countries ====
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. |
==== t_departmentgroups ====
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]]. |
==== t_departments ====
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 |
==== t_grouppermissions ====
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]]. |
==== t_groups ====
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 |
==== t_keys ====
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]]. |
==== t_labelers ====
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. |
==== t_labels ====
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_data | clob | The settings data. For example PLU print objects and labels. Usually XML content. |
==== t_languages ====
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. |
==== t_licenses ====
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. |
==== t_licensetokens ====
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 |
==== t_mergeclasses ====
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. |
==== t_newlogisticunits ====
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.
==== t_ordercollocks ====
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]]. |
==== t_ordercols ====
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_topack | number | Total tus to pack |
| c_packed | number | Total yet packed tus |
| 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!) |
==== t_orderitemlocks ====
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]]. |
==== t_orderitemrobots ====
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. |
The sum of c_base + c_planned equates to [[#t_orderitems]].c_planned
==== t_orderitems ====
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_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!) |
==== t_orderpackages ====
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 |
==== t_orderpicks ====
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. (null = unknow) |
| 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_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. |
==== t_orderrowlocks ====
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]]. |
==== t_orderrows ====
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!) |
==== t_ordertriprobots ====
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 |
==== t_ordertrips ====
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. |
==== t_ordertriplocks ====
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]]. |
==== t_orderweighings ====
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 |
==== t_packageattributes ====
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]]. |
==== t_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.
^ 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 |
==== t_packagezones ====
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]]. |
==== t_packstats ====
Packing statistics.
^ Column ^ Type ^ Description ^
| c_key | number(15) not null | Primary key. |
| 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_packline | number | Packing line number. |
| c_cus | number | # of consumer units packed. |
| c_pus | number not null | Quantity of price units packed. |
| c_packedby | number(15) not null | Packing user. References [[#t_users]]. |
| c_packdate | timestamp not null | Date/time of packing. |
| c_sentby | number(15) not null | User sending to master. References [[#t_users]]. |
| c_sentdate | timestamp not null | Date/time of sending to master system. |
==== t_partnerattributes ====
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]]. |
==== t_partnerclasses ====
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. |
==== t_partnerdepartments ====
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]]. |
==== t_partners ====
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 [[bps2:scanning|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. |
==== t_permissions ====
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 |
==== t_pickmatrix ====
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]]. |
==== t_pkgattributes ====
Available package attributes.
^ Column ^ Type ^ Description ^
| c_key | number(15) not null | Primary key. |
| c_name | varchar(35) not null | Name of the attribute. |
==== t_pnrattributes ====
Available partner attributes.
^ Column ^ Type ^ Description ^
| c_key | number(15) not null | Primary key. |
| c_name | varchar(35) not null | Name of the attribute. |
==== t_purchasedeliveries ====
Purchase order item delivery records.
^ 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_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_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 not null | Deduction in price units (delivered, but not accepted PU's). |
| c_retention | number not null | Retention in percent. |
| 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_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_remarks | varchar(4000) | Remarks text. |
| c_stock | number(15) | Stock the entry was made to. References [[#t_stock]]. |
==== t_purchaseitemlocks ====
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]]. |
==== t_purchaseitems ====
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_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_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. |
==== t_purchaseorderlocks ====
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]]. |
==== t_purchaseorders ====
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. |
==== t_purchasepackages ====
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 |
==== t_purchaseweighings ====
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 mesured. |
| c_ph | varchar(4000) | Comma separated list of pH values mesured. |
==== t_remarks ====
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. |
==== t_reportaccess ====
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 |
==== t_reportconfigs ====
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 |
==== t_reportqueue ====
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,...". |
| c_data | clob | ECMAScript compliant report data. See examples below. |
| 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.
==== t_reportzones ====
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]]. |
==== t_reports ====
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. |
==== t_reportsettings ====
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. |
==== t_resources ====
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 |
==== t_robotlocks ====
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]]. |
==== t_salechannels ====
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. |
==== t_saleclasses ====
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. |
==== t_settings ====
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. |
==== t_shipclasses ====
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. |
==== t_stkattributes ====
Available stock attributes.
^ Column ^ Type ^ Description ^
| c_key | number(15) not null | Primary key. |
| c_name | varchar(35) not null | Name of the attribute. |
==== t_stock ====
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 | 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 not null | Date and time of entry. |
| c_expdate | timestamp not null | 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!) |
==== t_stockattributes ====
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]]. |
==== t_stocklocations ====
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]]. |
==== t_stockreasons ====
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
==== t_stockreservations ====
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. |
==== t_tripclasses ====
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. |
==== t_tripgroups ====
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]]. |
==== t_trippartners ====
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. |
==== t_trips ====
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 |
==== t_usergroups ====
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]]. |
==== t_users ====
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 [[dbviews#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 |
==== t_vats ====
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 |
==== t_zonegroups ====
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]]. |
==== t_zonepartners ====
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_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. |
==== t_zones ====
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_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_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_storagedays | number | Default # of storage days, if c_storagedays in t_articles is empty |
==== ta_* ====
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 [[dbviews#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. |
==== tl_* ====
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_*. |