| SQL data model - DESADV description |  |
| SQL data model - DESADV description | |
This paragraph describes the DESADV (despatch advices) 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.
The simpliest (basic) version of the
DESpatch ADVice does not support packaging level (1 CPS only, no
split per package) and all products will be added together at the
shipment level ("PRO" version only).
For compatibility reason, we do ship the DESADV_group table. When creating
despatch advices, you will have to create 1 "dummy" record in the
"DESADV_group" table.
Remark : "Comment" fields must be 255 character 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.
| Database item 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, mandatory document reference inside the messages, unique key per sender |
| Document type | despatch advice |
| Shipment Number | required |
| Document Date | required |
| Delivery Date | required |
| Order Number | required |
| Order Date | required |
| Supplier code | required, EAN code |
| Buyer code | required, EAN code |
| Delivery code | A despacth advice is always sent to the Delivery party required, EAN code of the delivery party, part of the new record selection process - review the query "DESADVselectNew" to check relations |
| Status | record flow status |
| FK_UNH | Foreign key - pointer to UNH.PK_UNH |
The simplified, basic, mono package version do not include multi package
shipment information - included for upgrade/compatibility reason
DESADV Group.
| Database item name | Description |
|---|---|
| PK_CPS | primary key (autonumber) |
| Package ID | required - 1 is default |
| Mark type | option, multi package only |
| OuterPack type | option, multi package only |
| Number of items | option, multi package only |
| Package type | option, multi package only |
| Item type | option, multi package only |
| Serial number | option, multi package only |
| GIN qualifier | option, multi package only |
| Status | record flow status |
| FK_BGM | related to DESADV_header - DESADV_D_96A_UN_EAN008.PK_BGM |
DESADV items
| Database item name | Description |
|---|---|
| PK_LIN | unique key, autonumber |
| DESADV line | required. Contains information as received. Generated by EdiPax on new documents |
| GTIN | required, general trade identification number (EAN code) |
| EAN number | article EAN code, required if not equal to GTIN |
| Supplier article | option, required if not EAN |
| Inventory number | option |
| Promotional variant | option |
| Quantity despatch | required |
| Quantity unit | required, PCE - UNTED 6411 - normally the same as ORDERS |
| Package type | option |
| Serial number | option |
| Serial mark | option |
| GIN qualifier | option |
| Quantity variance | if quantity delivered is not the one ordered |
| Discrepancy reason | reason why Quantity variance <> 0 |
| Backorder delivery date | missing products will be delivered on ... |
| Discrepancy reason qualifier | 21 = quantity ordered ORDERS |
| CU in TU | Number of customer units in 1 trade unit |
| Description | option |
| Comment | option |
| Status | record flow status |
| FK_CPS | related to DESADV_group - DESADV_D_96A_UN_EAN008.PK_CPS |