| SQL data model - ORDERS description |  |
| SQL data model - ORDERS description | |
This paragraph describes the ORDERS tables hierarchy and
fields stored in the original "EdiPax+net+web-eancom.mdb".
Use Microsoft
Access "tools/relation" menu or
any other compatible tool to browse table relations (primary key, foreign key, integrity) and table
structures.
remark : "Comment" fields must be 255 characters long, minimum to accomodate EDIFACT FTX segment.
Common table for UNB segment.
Common table for UNH segment. FK_UNB is related to UNB.PK_UNB.
Common table for NAD segment.
| Field name | Description |
|---|---|
| PK_BGM | primary key (autonumber) |
| Audit number | unique internal key - refer to UpdateBatch - audit number |
| Interchange reference number | required, EDIFACT mandatory interchange unique reference |
| Message reference number | required, EDIFACT mandatory message unique reference inside interchange |
| Document reference number | required, document reference inside the messages, unique key per sender |
| Document Type | order, ruch order,.. |
| Message Date | required, Message date / Order date and time |
| Order Number | required, order number |
| Order Date | order date (if not present, document date is used) |
| Supplier Code | required, Supplier party EAN code An order is always sent to the Supplier (EDIFACT qualifier SU) EAN code, part of the new record selection process - review the query "ORDERSselectNew" query to check relations |
| Supplier name | recommended |
| Buyer Code | required, Buyer party EAN code (EDIFACT qualifier BY) |
| Delivery code | required, Delivery party EAN code / identification ((EDIFACT qualifier DP) |
| Invoicee code | Invoicee party EAN code / identification ((EDIFACT qualifier IV) |
| Buyer corporate code | Buyer corporate party EAN code (EDIFACT qualifier BCO) |
| Supplier corporate name | Supplier corporate party EAN code (EDIFACT qualifier SCO) |
| Algebraic Total | Summary total - algebraic total |
| Line Total | Summary total - number of orders line |
| Reference currency | Currency - Order currency |
| Reference Currency Type | Currency - reference currency type |
| Target Currency Type | Currency - target currency type |
| Target Currency | Currency - target currency |
| Exchange Rate | Currency - exchange rate |
| Exchange rate date | Currency - quotation date / time |
| Latest Delivery | Delivery date/time, latest |
| Earliest Delivery | Delivery date/time, earliest |
| Payment Term | Payment condition and term |
| Discount Rate | Discount rate (%) |
| Total duty/tax/fee | option |
| Total amount | option |
| Comment | option |
| Status | record flow status |
| FK_UNH | Foreign key - pointer to UNH.PK_UNH |
ORDERS items.
| Field name | Description |
|---|---|
| PK_LIN | primary key, auto number |
| Order line | required, line item number |
| GTIN | required, item number (EAN code) |
| Item type | item type identification code |
| Amount qualifier | monetary amount type code qualifier |
| Amount | monetary amount value |
| Price qualifier | price qualifier |
| Price | required, price per unit |
| Price type | |
| Price specification | price specification, coded |
| Price measure | |
| Price unit | required, unit prices is calculated (per piece,...) |
| Tax function | required, tax function qualifier |
| Tax type | required, tax type name or code |
| Tax rate | required, tax rate, VAT or other - all taxes |
| Supplier article | required, item number (Supplier's article number) |
| Buyer part | item number (Buyer's part number) |
| EAN code | item number (EAN code - GTIN) |
| Promotional variant | item number (Promotional variant number) |
| Number of layers | required, number of layers (Measured attribute code == LAY) |
| Units per layer | required, number of units per layer (Measured attribute code == ULY) |
| Item identification | item Class (TU = Trade Unit, CU = Customer unit) |
| Item description | other coded information (EAN) |
| Item code list | |
| Other description | |
| Order quantity | required, quantity (Ordered quantity) |
| Order unit | required, order unit (Piece, kg,..) |
| CU in TU | required, number of consumer units in the trade unit |
| FK_BGM | foreign key, related to ORDERS.PK_BGM |
| Status | record flow status |