| SQL data model - INVOIC description |  |
| SQL data model - INVOIC description | |
This paragraph describes the INVOIC (invoices) 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.
Total (MOA), Taxes (TAX) and Allowances and Charges (ALC) at item level
This "basic" package includes 1 "Allowance or Charge", 3
"amounts" (Gross line item, Discount amount, Line item amount)
and 2 taxes (VAT and recupel) fields at item level. The Allowance or
Charge code (type) is left free, that is, you can process any Allowance or
Charge type, 1 per line. You may however include as many lines as needed !
Total (MOA), Taxes (TAX) and Allowances and Charges (ALC) at document(invoice) level
Full relational model, with all possibilities. Mandatory values are reported beside the table field description.
Totals and Taxes calculation
When creating invoices, totals and tax totals are
mandatory. The EdiPax engine will calculate / create those values if
not present.
That is if you want to use this feature, you must leave the
INVOIC_taxes and INVOIC_totals table empty. The tool will not add records to
those tables, it will just add calculated values to the EDIFACT output.
Woudl
the calculated value not be correct or detailed enough, you can force
values - just fill in the fields. Be sure to use the correct EDIFACT values for
mandatory EDIFACT fields.
remark : "Comment" fields must be 255 character long, minimum to accomodate EDIFACT FTX segment.
SQL validation rules
1. Incoming messages
Some SQL fields are defined as "not null" which means an SQL exception will occur wold the field be missing in the EDIFACT document.
2. Outgoing messages
Some SQL fields are defined as "not null" which means an error will occur would your application not supply the required fields.
Some fields are option or must be present in some given case (business rules). EdiPax will report those errors in the status field.
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 | invoice, credit note |
| Invoice number | required, mandatory document reference inside the messages, unique key per sender |
| Reference type | CD = Credit, DL = debit or IV = invoice |
| Reference | Credit, debit or invoice number |
| Message function | Original (code=9) or duplicate |
| Document date | required |
| Invoice date | required |
| Delivery date | |
| Service completed date | |
| Accounting value date | |
| Account number | bank account number to pay |
| Order number | reference to the order unique key |
| Order date | option |
| Vendor order number | option |
| Vendor order date | option |
| Despatch advice number | option |
| Despatch advice date | option |
| Currency reference | option if not EURO |
| Currency target | option |
| Exchange rate | option |
| Exchange rate date | option |
| Payment periods | option, number of period |
| Payment period | option, coded, period type (week, months,..) |
| Payment code | option, coded |
| Payment date | option, coded |
| Payment relation | option, coded |
| Payment type | option, coded |
| Payment means | option, coded |
| Reference payment | option, payment / bank transfer reference (RFF) |
| Discount rate | option |
| Number line items | Control total - number of LIN elements |
| Number invoice lines | Control total - number of invoice lines, some LIN could be comments or sublines |
| Comment | option |
| Seller name, code, street, zip,city , country, registry, vat, bank account, bank country | Seller information - refer to party table |
| Supplier name, code, street, zip,city , country, registry, vat, bank account, bank country | Supplier information - refer to party table |
| Buyer name, code, street, zip,city , country, vat | Buyer information - refer to party table |
| Delivery party name, code, street, zip,city , country | Delivery information - refer to party table |
| Contact name, code, street, zip,city , country, person | Contact information - refer to party table |
| Status | record flow status |
| FK_UNH | foreign key, pointer to UNH.PK_UNH |
| Database item name | Description |
|---|---|
| PK_LIN | primary key (autonumber) |
| Invoice line | Mandatory. Contains information as received. Generated by EdiPax on new documents |
| Item number | EAN or product code - mandatory |
| GTIN | general trade identification number (EAN code) |
| Item identification | EAN code if not in GTIN |
| Item code list | EAN (code 9) or private |
| Item description | Item description - mandatory |
| Item description2 | Item description - option |
| Item agency | Mandatory if not UN or EAN |
| Comment | |
| Product identification | |
| Order number | reference to orders unique key, if different from Order number in header |
| Order line | reference to orders item unique key - to use if several invoices to match 1 order |
| Delivery date | |
| Order quantity | |
| Invoice quantity | required |
| Delivery quantity | recommanded |
| Despatch quantity | |
| CU in TU | Number of customer units in one trade unit |
| Line item amount | required, amount charge, including ALC |
| Discount rate | |
| Discount amount | can be equal to amount * discount or any fix amount |
| Gross line item | groos amount, with ALC or discount |
| Price qualifier | required, AAB - gross prices - UNTED 5125 |
| Price | required |
| Price type | UNTED 5375 |
| Price specification | Price type qualifier - UNTED 5387 |
| Price measure | required, PCE = piece - UNTED 6411 |
| Unit price basis | number of units in the price ... |
| Tax exemption | required if not tax rate is specified BMF100 codes - UNTED 5279 |
| Tax rate | alphanumeric - can contain numeric values like 21.0 or 21 |
| Tax basis | |
| Tax amount | option - required if "Tax rate" is not numeric, or no tax rate specified |
| ALC qualifier | A (Allowance) or C (Charge) - UNTED 5463 |
| ALC code | any special service UN code (discount,..) - UNTED 7161 |
| ALC amount | required if ALC segement is used |
| Recupel | environmental tax (special charge) |
| Status | record flow status |
| FK_BGM | foreign key to INVOIC_header.PK_BGM |
| Database item name | Description |
|---|---|
| PK_LIN05 | primary key (autonumber) |
| subLine | subLine indicator. Value should be 1 |
| Add info5 | allocation key |
| Add info6 | allocation key |
| Add info7 | allocation key |
| Add info8 | allocation key |
| Add info9 | allocation key |
| Add info10 | allocation key |
| Add info11 | allocation key |
| Add info12 | allocation key |
| ac_agency | allocation key agency |
| Line | invoice line number, alphanumeric, in sequence |
| Tax rate | required, alphanumeric |
| Amount net | required |
| From | from location |
| To | to location |
| Period start | period service performed |
| Period end | |
| Service date | date and time service performed |
| Service duration | duration of a service |
| Service duration unit | 803=week, 804=day, 805=hour, 806=min, 807=sec |
| Comment | |
| Quantity items | required, number of items / number of units |
| Quantity items unit | required, unit (PCE = piece,..) - UNTED 6411 |
| Quantity charged | number of units charged |
| Quantity charged unit | charge quantity unit (PCE = piece, SEC = second, ..) - UNTED 6411 |
| ALC code | |
| Reference | |
| parent | internal code, pointer toward INVOIC_items.Invoice Line parent |
| Status | record flow status |
| FK_LIN | foreign key to INVOIC_items.PK_LIN |
Contains allowance and charges (transport, discount and other services)
| Database item name | Description |
|---|---|
| PK_ALC | primary key (autonumber) |
| Alc Qualifier | required, must be A (allowance) or C (charge) |
| Alc special service |
required, special services, any UN or BMF100 values from UNTED 7162 table Discount amount - discount rate * total- code EDIFACT 52, MOA at the end of the message, not mandatory Cash discount - invoice footer, additional discount - code EDIFACT 21 Quantity discount allowance - discount on quantity, EURO - code EDIFACT QD Promotional discount allowance - discount on quantity, EURO - code EDIFACT PAE Freight charge - transport or freight on goods, EURO - code EDIFACT FC Discount allowance - = discount rate *Line item amount -should match Payment term / discount, EURO - code EDIFACT DI |
| Alc amount | required |
| Tax qualifier | required, 7 or 3 - other code accepted |
| Tax type | required, VAT or ENV |
| Tax id | option, exemption code - mandatory if [Tax category] = E(Exempt) |
| Tax rate | required - can be 0 or null if Tax id is specified |
| Tax category | S = standard rate, E = Exempt |
| Tax basis qualifier | required, 23 = Charge amount, 109 = Payment discount amount, 204 = Allowance amount - must match Alc qualifier ( C = 23, A = 204 or 109 |
| Tax Basis | Vat Basis, Allowance or Charge amount |
| Tax amount | option, mandatory if rate is not numeric - positive or negative |
| Status | record flow status |
| FK_BGM | foreign key to INVOIC_header.PK_BGM |
All total items, at document (invoice) level. We recommend to use 6 codes. You may set values to 0 if amount (zero) is considered as significant.
| Database item name | Description |
|---|---|
| PK_MOA | primary key (autonumber) |
| Amount qualifier |
Total line items amount - Gross value, before document level discount, transport and any other charge, code EDIFACT 79 Invoice amount - including VAT, code EDIFACT 77 or 86 VAT basis - code EDIFACT 79 VAT amount - code EDIFACT 150 Total duty/tax/fee 176 Tax amount - code EDIFACT 124 Total charges, allowance - option, code EDIFACT 131 Amount due - option, code EDIFACT 9, deduct prepaid amount(s) |
| Amount | required |
| C51603 | option |
| C51604 | option |
| C51605 | option |
| Status | record flow status |
| FK_BGM | foreign key to INVOIC_header.PK_BGM |
Tax detail, 1 record per tax class or tax rate. The tax basis and tax amount must be specified.
| Database item name | Description |
|---|---|
| PK_TAX | primary key (autonumber) |
| Tax Qualifier | value = 7, required |
| Tax type | required, VAT (value added taxe) or ENV (environmental) are common, any valid UN code accepted |
| Tax rate | required,alphanumeric value. When VAT tax, we recommend to put the numeric value (ex : 21) |
| Tax exemption | When exempt, tax rate must be null or equal to 0 |
| Tax basis | required. Can be 0 |
| Tax amount | option, must be not null if Tax Basis is null (fix amount tax) |
| Status | record flow status |
| FK_BGM | foreign key to INVOIC_header.PK_BGM |
One record per party (buyer, supplier, invoicee, delivery party, bank,...)
| Database item name | Description |
|---|---|
| PK_PARTY | primary key, autonumber |
| Qualifier | required |
| Code | required Belgian tax authorities : an invoice is always sent by the Supplier tothe Buyer (code EDIFACT SU -> BY) EAN code, part of the new record selection process - review the query "INVOICselectNew" query to check relations We may mention DP (delivery party) |
| Name | option for EAN00n, required legal obligation for BMF100 |
| Address 1 | option for EAN00n, required legal obligation for BMF100 |
| Address 2 | |
| City | option for EAN00n, required legal obligation for BMF100 |
| Zip | option for EAN00n, required legal obligation for BMF100 |
| Country | |
| Registry | |
| Vat | required for supplier, buyer and invoicee |
| Bank account | |
| Bank country | |
| Factor ID | |
| Contact | |
| Telephone | |
| Fax | |
| Status | record flow status |
| FK_BGM | foreign key to INVOIC_header.PK_BGM |