EDI Export file
The Sales Invoice/Credit EDI export file is made up of:
One STRUCTURE record per file that tells EDI about the format of the file.
One HEADER record per invoice/credit
One BRANCH record per invoice/credit (if HSBC format)
Repeated DETAIL record as required
Repeat VAT record as necessary (one per VAT code used)
One TRAILER record per invoice/credit
Structure Record Mappings
No. | EDI Field | Access Dimensions Mapping |
1 | File format type | Use value of CSV |
2 | Date Format | Use value of DDMMYY |
3 | Time Format | Use value of HHMM |
4 | Header Record ID | Use value of HEAD |
5 | Detail Record ID | Use value of DETAI |
6 | VAT Record ID | Use value of VAT |
7 | Detail Record ID | Use value of TRAIL |
8 | Schedule Record ID | Use value of SCHED |
9 | Reconcile Record ID | Use value of RECON |
10 | End of File Record ID | Use 5 Spaces |
11 | Right Justified Numerics | Use value of N |
12 | Zero Fill Numerics | Use value of N |
Header Record
No. | EDI Field | Access Dimensions Mapping |
1 | Record ID | HEAD |
2 | Company Code | COMP_BRANCHID |
3 | Customer Code | ST_COPYCUST |
4 | Location Code | BLANK |
5 | Invoice Number | ST_HEADER_REF |
6 | Invoice Date | ST_DATE formatted as DDMMYY |
7 | Customer Order Number | OH_ORDER_REF, or if invoice is consolidated then the OH_ORDER_REF from the first order |
8 | Customer Order Date | OH_DATE formatted as DDMMYY. If invoice is consolidated then OH_DATE from the first order |
9 | Proof of Delivery Number | BLANK |
10 | Proof of Delivery Date | BLANK |
11 | Delivery Note Number | ST_HEADER_REF |
12 | Delivery Date | ST_DATE formatted DDMMYY |
13 | Delivery Time | BLANK |
14 | Payment Terms | SL_ACCOUNTS.CU_TERMS |
15 | Payment Date | If ST_TRANTYPE='INV' then SL_TRANSACTIONS.ST_DUEDATE as DDMMYY, else ST_DATE as DDMMYY |
16 | Settlement Discount 1 | ST_SETT_DISC_1 |
17 | Delivery Address 1 | If HSBC Format then AD_ADDRESS line 1 else BLANK |
18 | Delivery Address 2 | If HSBC Format then AD_ADDRESS line 2 else BLANK |
19 | Delivery Address 3 | If HSBC Format then AD_ADDRESS_USER1 else BLANK |
20 | Delivery Address 4 | If HSBC Format then AD_ADDRESS_USER2 else BLANK |
21 | Delivery Postcode | If HSBC Format then AD_ADPOSTCODE else BLANK |
22 | Credit Note Indicator | If ST_TRANTYPE='CRN' then Y if not then N |
23 | Location | ANA BLANK |
24 | Customer Location Code | OH_DESCRIPTION |
25 | File Generation No | BLANK |
26 | File Generation Version | BLANK |
27 | File Generation Date | BLANK |
28 | Original Invoice Number | If ST_TRANTYPE='CRN' then ORD_HEADER.OH_USER3 else BLANK |
29 | Original Invoice Date | If ST_TRANTYPE='CRN then OH_DATE as DDMMYY else BLANK. If invoice is consolidated then OH_DATE from first order |
30 | Debit Note Number | BLANK |
31 | Debit Note Date | BLANK |
32 | Collection Note Number | BLANK |
33 | Collection Date | BLANK |
34 | Payment Ref | BLANK |
35 | Reference Currency | CURREC_ISO_CODE for HOME_CURR_SYMBL |
36 | Invoice Currency | CURREC_ISO_CODE for ST_CURRENCYCODE |
37 | Exchange Rate | If ST_CURRENCYRATE=0 then 1 else ST_CURRENCYRATE |
38 | Supplier Order Number | ST_ORDER_NUMBER, unless invoices is consolidated then BLANK |
39 | Supplier Order Date | OH_DATE as DDMMYY. If invoice is consolidated then OH_DATE from first order |
40 | Contract ID | If EDI format is HSBC then CU_USRCHAR1 and used as HSBC Customer Number if not then BLANK |
41 | Supplier ID | If EDI format is HSBC then CU_USRCHAR2 and used as HSBC Account if not then BLANK |
Branch Record
Only required when exporting in HSBC format.
No. | EDI Field | Access Dimensions Mapping |
1 | Record ID | BRNCH |
2 | Branch Name | CUNAME (limited to first 35 characters) |
3 | Branch Address (Line 1) | CUADDRESS line 1 |
4 | Branch Address (Line 2) | CUADDRESS line 2 |
5 | Postal Town | CU_ADDRESS_USER1 |
6 | County | CU_ADDRESS_USER2 |
7 | Postal Code | CUPOSTCODE (limited to first 8 characters) |
Detail Records
No. | EDI Field | Access Dimensions Mapping |
1 | Record ID | Use Value of 'DETAI' |
2 | Product Code | If Stock then DET_STOCK_CODE else if Price record then DET_PRICE_CODE else if Text line use value of 'TEXT' |
3 | Product Group | If Stock then STK_SORT_KEY else if Price record then PR_SORT_KEY else if Text line BLANK |
4 | Pack Size | If DET_UNIT_QTY<>0 then DET_UNIT_QTY else value of 1 |
5 | Unit of Measure | BLANK |
6 | Measure Indicator | BLANK |
7 | Quantity | If DET_QUANTITY<>0 then DET_QUANTITY else value of 1 |
8 | Total Measure | BLANK |
9 | Measure Indicator (Total) | BLANK |
10 | Gross Price | If DET_CURR_CODE<>HOME_CURR_SYMBL then DET_UNT_PRICE_C else DET_UNIT_PRICE |
11 | Line Discount Percentage | OD_LINEDISC |
12 | Line Discount Value | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_CURR_L_DISC else DET_L_DISCOUNT |
13 | Net Price | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_UNT_PRICE_C else DET_UNIT_PRICE |
14 | VAT Code | DET_VATCODE |
15 | Total Value (Gross) | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_CURR_NETT+DET_CURR_L_DISC+DET_CURR_T_DISC else DET_NETT+DET_L_DISCOUNT+DET_T_DISCOUNT |
16 | Total Value (Nett) | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_CURR_NETT+DET_CURR_T_DISC else DET_NETT+DET_T_DISCOUNT |
17 | Subject to Settlement Flag | Use Value of 'Y' |
18 | Traded Unit EAN | BLANK |
19 | Partner Product Code | BLANK |
20 | Display Until Date | BLANK |
21 | Best Before Date | BLANK |
22 | Traded Unit EAN | BLANK |
23 | Traded Unit DUN | BLANK |
24 | Customers Product Code | BLANK |
25 | Description 1 | First 40 characters of OD_DETAIL |
26 | Description 2 | Next 40 characters of OD_DETAIL |
27 | Credit Reason (Code) | BLANK |
28 | Credit Reason (Text) | If ST_TRANTYPE='CRN' then 'CREDIT' else BLANK |
29 | Mixed VAT Rate Code | BLANK |
30 | Credit Note Indicator | If ST_TRANTYPE='CRN' then 'Y' else 'N |
31 | Customer Order Number | OH_ORDER_REF |
32 | Customer Order Date | BLANK |
33 | Proof of Delivery No | BLANK |
34 | Proof of Delivery Date | BLANK |
35 | Delivery Note No | BLANK |
36 | Delivery Note Date | BLANK |
37 | Delivery Time | BLANK |
38 | Line VAT Amount | If DET_CURR_CODE<>HOME_CURR_SYMBL then DET_CURR_TAX else DET_VAT |
39 | Order Line Number | OD_LINE_NUMBER |
Repeated VAT record (one per VAT code used)
No. | EDI Field | Access Dimensions Mapping |
1 | Record ID | Use Value of 'VAT' |
2 | VAT Code | VAT Code |
3 | Line Count | Number of lines relating to that VAT Code |
4 | Total Gross | If DET_CURR_CODE<>HOME_CURR_SYMBL then total of DET_CURR_NETT+DET_CURR_L_DISC+DET_CURR_T_DISC else total of DET_NETT+DET_L_DISCOUNT+DET_T_DISCOUNT |
5 | Total Nett | If DET_CURR_CODE<> HOME_CURR_SYMBL then total of DET_CURR_NETT+DET_CURR_T_DISC else total of DET_NETT+DET_T_DISCOUNT |
6 | Total Settlement Discount | If DET_CURR_CODE<>HOME_CURR_SYMBL then ST_SETT_DISC1*DET_CURR_NETT else ST_SETT_DISC1*DET_NETT |
7 | VAT Amount Payable | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_CURR_TAX else DET_VAT |
8 | Invoice Discount Amount | If DET_CURR_CODE<> HOME_CURR_SYMBL then DET_CURR_T_DISC else DT_T_DISCOUNT |
9 | Invoice Discount Percentage | OH_DISC_TOTAL_P |
TRAILER record (one per invoice/credit)
No. | EDI Field | Access Dimensions Mapping |
1 | Record ID | Use value of TRAIL |
2 | Total Nett | If ST_CURRENCYCODE<>HOME_CURR_SYMBL then sum of DET_CURR_NETT+DET_CURR_T_DISC else DET_NETT+DET_T_DISCOUNT |
3 | Charge 1 | BLANK |
4 | Charge 2 | BLANK |
5 | Charge 3 | BLANK |
6 | Total Settlement Discount | If ST_CURRENCYCODE<> HOME_CURR_SYMBL then ST_SETT_DISC_1*ST_CURR_NETT else ST_SETT_DISC_1*ST_NETT |
7 | Total VAT Payable | If ST_CURRENCYCODE<>HOME_CURR_SYMBL then total of DET_CURR_TAX else total of DET_VAT |
8 | Total Invoice Discount | If ST_CURRENCYCODE<> HOME_CURR_SYMBL then total of DET-CURR_T_DISC else total of DET_T_DISCOUNT |
9 | Payment Amount | (Field 2 + Field 7) - (Field 6 + Field 8) |
10 | Invoice Discount Percentage | OH_DISC_TOTAL_P |