| SQL data model - RECADV description |  |
| SQL data model - RECADV description | |
This paragraph describes the RECADV (receipt 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 RECeipt ADVice does not support packaging level (1
CPS only, no split per package) and all products will be
added together at the package level.
For compatibility reason, we do ship the RECADV_group table. When creating
despatch advices, you will have to create 1 "dummy" record in the
"RECADV_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.
| 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 | receipt advice |
| Shipment Number | required |
| Document Date | required |
| Delivery date | required |
| Order number | required, coded |
| Order date | optional |
| Supplier code | required, coded |
| Supplier name | optional |
| Buyer code | required, coded |
| Buyer name | optional |
| Delivery code | required, coded |
| Delivery name | optional |
| Status | record flow status |
| FK_UNH | foreign key pointing to higher level primary key |
The simplified, basic, mono package version do not include multi package
shipment information - included for upgrade/compatibility reason
RECADV Group.
| Database item name | Description |
|---|---|
| PK_CPS | primary key (autonumber) |
| Package ID | required |
| Parent ID | parent package ID |
| Packaging level | |
| Mark type | |
| OuterPack type | |
| Number package | |
| Number items | |
| Package type | |
| Marking instructions | |
| Item type | |
| Serial number | |
| GIN qualifier | |
| ID number1 | |
| ID number2 | |
| Status | record flow status |
| FK_BGM | foreign key pointing to higher level primary key |
RECADV items
| Database item name | Description |
|---|---|
| PK_LIN | primary key (autonumber) |
| RECADV Line | |
| GTIN | Global Trade Item Number - coded |
| EAN number | |
| Supplier article | |
| Buyer part | |
| Promotional variant | |
| Despatch quantity | |
| Quantity unit | |
| Item identification | |
| Serial number | batch number also |
| Serial mark | |
| GIN qualifier | |
| Quantity variance | when backorder |
| Discrepancy reason | when backorder |
| Backorder delivery date | when backorder|
| Quantity variance qualifier | when backorder |
| CU in TU | number customer units inside trade units |
| Item description | |
| Comment | |
| ID number1 | |
| ID number2 | |
| Marking instructions | |
| Reference line number | reference to order line |
| Status | record flow status |
| FK_CPS | foreign key pointing to higher level primary key |