Generic Purchase Order and Invoice File Formats

Description

This document outlines the specifications of the generic file formats for Purchase Orders and Supplier Invoices used by SwiftPOS Software.

Note : Please ensure that you have ALL of the Suppliers set up correctly with all the relevant information for this import to work correctly.

It is intended for use by both internal and external developers and any other POS Suppliers who are interested in developing an industry standard for integration between Wholesalers and POS Systems. The basis of the design is that the Purchase Order structure matches the Supplier Invoice structure so it is possible to create a Purchase Order and lifecycle it into the software as a Suppliers' Invoice for testing purposes.

To Be Considered

When requesting electronic invoices from LMG, request format 6. This format is described below. Note : Do not request format 5 and attempt to import it as a Metcash invoice as it will not be successful.
Prior to Creating Purchase Orders

Ensure the Supplier Details held in SwiftPOS Back Office are correct.
Ensure the Business/Venue's ABN is recorded in the SwiftPOS Registration screen. This identifies the Business/Venue as the source of the document.

Operation

Save the Invoice files from the emails received from the Suppliers in the default folder.
Merge and append all the Invoice files into one large text (CSV) file.
Once all Invoice files have been merged into one, delete the individual Invoice files from the folder.
Using the Import File button from the Invoice Import menu item, import the text (CSV) file created in step 4 above.

Purchase Order File Format (PO)

File Name Convention

The PO File Name structure is as follows:

Field	Length	Description
YYYY	4 char	Year, e.g.: 2014.
MM	2 char	Month, e.g. 05.
DD	2 char	Day of the month, e.g.: 24.
00000	5 char	Purchase Order ID, e.g.: 22464.
*	Variable	Supplier ID, e.g.: Metcash.
.TXT	N/A	File extension.

Convention: YYYYMMDD00000*.txt

Example: 2014052422464Metcash.txt

File Format Pre-Requisites

The PO File must comply with the following formatting rules:

The file must be CSV (comma separated variable) formatted.
Makes use of the CR-LF character for line termination.
All text fields must be enclosed within double quotes. For example : "1380DIN". Double quotes must NOT be embedded in the text itself.
Numeric values and Currency amounts are limited to 2 decimal places.
Currency Amounts must NOT contain currency symbols. For example the $ symbol must not prefix the amount.
Each record (line) must contain a record identifier at the start of the record to indicate the type of data, ie. the Record Type. The following Record Types are valid:

Record ID	Record Type	No. of Data Fields*
H	PO Header	10
I	PO Item	10
D	PO Delivery Details	10
T	PO Totals	10

*Each Record (line) can contain up to 10 fields for data use. Some of which are reserved for specific data, while others remain available for potential future use.

PO Header (Record Type H)

H, Account ID/Customer ID/Supplier ABN, Location ID, Clerk ID, Site ABN, PO Number, PO Date,,,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	H
2	Account ID	Text	20	Alphanumeric	Supplier's Account ID
3	Location ID	Text	4	Range 1 to 9999	Default to 1 if Location is not known.
4	Clerk ID	Long Integer		Numeric
5	Supplier ABN	Text	20	Alphanumeric
6	PO Number	Text	10	Numeric
7	PO Date	Date	10	Alphanumeric	Specify in mm/dd/yyyy format
8	Supplier ID	Text	10	Alphanumeric
9	Not used			Null	For future use
10	Not used			Null	For future use
11	Not used			Null	For future use
Example	H,"","3366",0,"",00017,03/28/2016,,,,,

PO Item (Record Type I)

I, Supplier Code, Item Description, Cost, Case Qty, Cases, Qty/Units, Total Excl Tax, Total Incl Tax, Total Tax,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	I
2	Supplier Code	Text	13	Alphanumeric
3	Item Description	Text	30	Alphanumeric
4	Cost	Double		Numeric	Last Purchase Price per Unit
5	Case Qty	Double		Numeric	Quantity of Units in a Case. Example : 12 or 24 (Used in conjunction with Field # 5 below)
6	Cases	Double	5	Numeric	No. of Cases. Specify a value of zero (0) if ordering in Units
7	Units	Double	10	Numeric	Quantity or No. of Units. Specify a value of zero (0) if ordering in Cases
8	Total Excl Tax	Double		Numeric	Total Line Cost based on Last Purchase Price multiplied by Qty Ordered. Excluding Tax
9	Total Incl Tax	Double		Numeric	Total Line Cost based on Last Purchase Price multiplied by Qty Ordered. Including Tax
10	Total Tax	Double		Numeric	Total calculated Tax
11	Product Barcode	Text	13	Numeric	First Product Barcode
12	Product Code	Text	13	Alphanumeric	SwiftPOS Back Office Product Code
Examples	I, "500042", "THEAKSTONS OLD PECUL 330ML", 1.88, 24, 2, 3, 95.88, 95.88, 0.00,9415767017320,TOP330 I, "500131", "MCCALLUMS SCOTCH 1125ML", 31.20, 12, 2, 0, 748.80, 748.80, 0.00,9415767017306,MS1125 I, "500173", "RENMANO R/BRZ RIESLING 750ML", 2.85, 12, 0, 6, 17.10, 17.10, 0.00,9415767019720,RR750 To order 2 cases and 6 single bottles where the Case Qty was 12 it would look like this. Field 5 = 12, Field 6 = 2 and Field 7 = 6. The total quantity in units is not shown because it is calculated internally by multiplying Field 5 by Field 6 and adding Field 7. Like this (2 x 12) = 24 + 6 = 30 units.

PO Delivery Details (Record Type D)

D, Delivery Address,,,,,,,,,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	D
2	Delivery Address	Text	245	Alphanumeric
3	Delivery Date	Date	10	Alphanumeric	Specify in mm/dd/yyyy format
4	Not used			Null	For future use
5	Not used			Null	For future use
6	Not used			Null	For future use
7	Not used			Null	For future use
8	Not used			Null	For future use
9	Not used			Null	For future use
10	Not used			Null	For future use
11	Not used			Null	For future use
Example	D, "Ring the bell at the back door Deliveries only between 8am and 2pm Monday to Friday",03/28/2016,,,,,,,,

PO Totals (Record Type T)

T, Item Count, Purchase Order Total Inc Tax,,,,,,,,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	T
2	Item Count	Integer		Numeric	Total Number of Items
3	PO Total Excl Tax	Double		Numeric	Total of PO Items Excluding Tax
4	PO Total Tax	Double		Numeric	Total of PO Items Tax
5	Not used			Null	For future use
6	Not used			Null	For future use
7	Not used			Null	For future use
8	Not used			Null	For future use
9	Not used			Null	For future use
10	Not used			Null	For future use
11	Not used			Null	For future use
Example	T, 3, 312.00, 0.00,,,,,,,

Example of a PO File

H, "12223255", "0001", 1, "123456", "00011", 03/24/2014,,,,

I, "500042", "THEAKSTONS OLD PECUL 330ML", 1.88, 24, 2, 3, 95.88, 95.88, 0.00,

I, "500131", "MCCALLUMS SCOTCH 1125ML", 31.20, 12, 2, 0, 748.80, 748.80, 0.00,

I, "500173", "RENMANO R/BRZ RIESLING 750ML", 2.85, 12, 0, 6, 17.10, 17.10, 0.00,

D, "Ring the bell at the back door Deliveries only between 8am and 2pm Monday to Friday",,,,,,,,,

T, 3, 312.00, 0.00,,,,,,,

Supplier Invoice File Format (INV)

File Name Convention

The INV File Name structure is as follows:

Field	Length	Description
YYYY	4 char	Year, e.g.: 2014.
MM	2 char	Month, e.g. 05.
DD	2 char	Day of the month, e.g.: 24.
*	Variable	Supplier ID, e.g.: Metcash.
.INV		File extension.

Convention: YYYYMMDD*.inv

Example: 20140524Metcash.inv

File Format Pre-Requisites

The INV File must comply with the following formatting rules:

The file must be CSV (comma separated variable) formatted.
Makes use of the CR-LF character for line termination.
All text fields must be enclosed within double quotes. For example : "1380DIN". Double quotes must NOT be embedded in the text itself.
Numeric values and Currency amounts are limited to 2 decimal places.
Currency Amounts must NOT contain currency symbols. For example the $ symbol must not prefix the amount.
Each record (line) must contain a record identifier at the start of the record to indicate the type of data, ie. the Record Type. The following Record Types are valid:

Record ID	Record Type	No. of Data Fields*
H	INV Header	10
I	INV Item	10
D	INV Delivery Details	10
T	INV Totals	10

*Each Record (line) can contain up to 10 fields for data use. Some of which are reserved for specific data, while others remain available for potential future use.

Multiple Invoices from the same Supplier may exist in a single file.
Invoices can be transmitted electronically and stored in a nominated directory on the clients computer ready for import into the software.

INV Header (Record Type H)

H, Account ID, Location ID,, Supplier?s ABN, INV Number, INV Date, Admin Fees, Document Type, PO Number,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	H
2	Account ID*	Text	20	Alphanumeric	Account ID from Supplier. SwiftPOS will attempt to match this field with the SwiftPOS Supplier's Account. If no match is found, then matching will be done using the Supplier?s ABN (see field# 5 below) and the SwiftPOS Supplier's Company No.
3	Location ID	Text	4	Range 1 to 9999	Default to 1 if Location is not known.
4	Supplier Name	Text	50	Alphanumeric
5	Supplier?s ABN	Text	20	Alphanumeric
6	INV Number	Text	15	Alphanumeric
7	INV Date	Date	10	Alphanumeric	Specify in mm/dd/yyyy format
8	Admin Fees	Double		Numeric or Null
9	Document Type	Integer		0/1/2	0 or 1 = Invoice, 2 = Credit Note
10	PO Number	Text	10	Numeric
11	Invoice URL or Filename	TEXT	2048
Example	H,"123456","0004",,"123456","000000000000012",09/30/2014,,2,,

INV Item (Record Type I)

I, Stock Code, Description,, Case Qty, Cases, Qty/Units, Total Excl Tax,, Total Tax,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	I
2	Stock Code	Text	20	Alphanumeric	Stock Code from Supplier. SwiftPOS will attempt to match this field with the SwiftPOS Supplier's Order Code. If no match is found, then matching will be done using this field and the SwiftPOS Product's Product Code. If no match is found, then matching will be done using this field and the SwiftPOS Product's Description. If there is still no match it will record the item without a Stock Code but leave the Supplier's Stock Code and Description intact so that the Customer is able to match it against the Product purchased.
3	Description	Text	30	Alphanumeric	Item Description
4	Not used			Null	For future use
5	Case Qty	Double		Numeric	Quantity of Units in a Case. Example : 12 or 24 (Used in conjunction with Field # 6 below)
6	Cases	Double	5	Numeric	No. of Cases. Specify a value of zero (0) if ordering in Units only
7	Units	Double	10	Numeric	No. of Units. Specify a value of zero (0) if ordering in Cases only
8	Total Excl Tax	Double		Numeric	Total item value excluding Tax
9	For future use			Null
10	Total Tax	Double		Numeric	Total item Tax value
11	Barcode	Text	14
Examples	I,"1111","B. CROWN LAGER STUB T/A",2.09,,,-15,-31.35,-34.49,-3.14, I,"1112","CTN TOOHEYS OLD",10.24,,,-12,-122.88,-122.88,.00, I,"1149","CTN TOOHEYS BLUE",7.97,,,-6,-47.82,-52.60,-4.78, To receive 2 cases and 6 single bottles where the Case Qty was 12 it would look like this. Field 5 = 12, Field 6 = 2 and Field 7 = 6. The total quantity in units is not shown because it is calculated internally by multiplying Field 5 by Field 6 and adding Field 7. Like this (2 x 12) = 24 + 6 = 30 units.

INV Delivery Details (Record Type D)

D, Delivery Address,,,,,,,,,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	D
2	Delivery Address	Text	245	Alphanumeric
3	Not used			Null	For future use
4	Not used			Null	For future use
5	Not used			Null	For future use
6	Not used			Null	For future use
7	Not used			Null	For future use
8	Not used			Null	For future use
9	Not used			Null	For future use
10	Not used			Null	For future use
11	Not used			Null	For future use
Example	D, "Ring the bell at the back door Deliveries only between 8am and 2pm Monday to Friday",,,,,,,,,

INV Totals (Record Type T)

T, Item Count, INV Total Excl Tax, INV Total Tax, Freight Excl Tax, Freight Tax, Discount,,,,

Field#	Field	Field Type	Length	Valid Values	Comments
1	Record Type ID	Text	1	T
2	Item Count	Integer		Numeric	Total Number of Items
3	INV Total Excl Tax	Double		Numeric	Total of INV Items Excluding Tax.
4	INV Total Tax	Double		Numeric	Total of INV Items Tax
5	Freight Excl Tax	Double		Numeric	Freight Excluding Tax
6	Freight Tax	Double		Numeric	Freight Tax
7	Discount	Double		Numeric	Total Discount
8	Not used			Null	For future use
9	Not used			Null	For future use
10	Not used			Null	For future use
11	Not used			Null	For future use
Example	T,3,-202.05,-7.92,,,,,,,

Note : Invoice Total + Tax is the amount the Supplier is to be paid net of freight/discounts & rounding.

Example of a INV File with multiple Invoices

The software will attempt to match the ?A/c ID ? field supplied in the Invoice Header with the field called ?Account No:? in the Supplier Details menu. If there is no match it will try matching the ?Supplier?s ABN? field with the field called ?Supplier ABN? in the Supplier Details menu.

H,"123456","4",0,"123456",00012,09/30/2002,,2,,

I,"1111","B. CROWN LAGER STUB T/A",2.09,,,-15,-31.35,-34.49,-3.14,

I,"1112","CTN TOOHEYS OLD",10.24,,,-12,-122.88,-122.88,.00,

I,"1149","CTN TOOHEYS BLUE",7.97,,,-6,-47.82,-52.60,-4.78,

D," ",,,,,,,,,

T,3,-202.05,-7.92,,,,,,,

H, "12223255", "1", 1, "123456", "00011", 03/24/2002, , , ,

I, "500042", "THEAKSTONS OLD PECUL 330ML", 1.88, 24, 2, 3, 95.88, 95.88, 0.00,

I, "500131", "MCCALLUMS SCOTCH 1125ML", 31.20, 12, 2, 0, 748.80, 748.80, 0.00,

I, "500173", "RENMANO R/BRZ RIESLING 750ML", 2.85, 12, 0, 6, 17.10, 17.10, 0.00,

D, "Ring the bell at the back door Deliveries only between 8am and 2pm Monday to Friday", , , , , , , , ,

T, 3, 312.00, 0.00, , , , , , ,