- Contents
- Index
- Search
Display results with all search words
File Specifications
Learn about the detail about how the supplier's file must be formatted for the import process to work.
The file must be separated into three sections:
- Header section - Contains information of the invoice header.
- Transaction section - Contains information of the invoice line.
- Detail section - Contains information of the lot and dimension detail linked to the invoice line.
Each line in the file must have a Record ID, which identifies the section it relates to. The file is comma delimited, and must contain:
Header Section:
|
Field |
Type |
Size |
Comment |
|
Record ID |
Character |
1 |
Must equal 1 |
|
Transaction Type |
Character |
5 |
Must equal APINV |
|
Invoice Reference |
Character |
20 |
Maps to an AP invoice reference |
|
Date |
Character |
10 |
Maps to an AP invoice document date (must be dd/mm/yyyy) |
|
Supplier No. |
Character |
6 |
Maps to the supplier code |
|
Warehouse |
Character |
2 |
Maps to a valid stocking location (this is the receiving location for all invoice lines). |
|
Narration |
Character |
20 |
[Optional] Maps to an AP Invoice header narration |
|
Net Invoice Value |
Numeric |
12,2 |
Represents total net invoice value. See Transaction Line Adjustment section below which outlines how this field is used during the process. |
|
Total Tax Value |
Numeric |
12,2 |
Represents the total invoice tax value. |
|
Gross Invoice Value |
Numeric |
12,2 |
Represents the total invoice gross value. |
Header Line Example:
1,'APINV','3782457640','08/11/2005','CHHW','01','ROTOITI 810',26391.70,2639.17,29030.87
Transaction Section:
|
Field |
Type |
Size |
Comment |
|
Record ID |
Character |
1 |
Must equal 2 |
|
Inventory Item |
Character |
20 |
Maps to Supplier Inventory Item Code for an active Greentree Desktop Item Code - the invoice line is populated with the inventory item code. |
|
Quantity |
Numeric |
8,4 |
Total quantity for invoice line. This is not actually referenced during the import as the invoice line quantity always be derived from the sum of quantities for lots/dimension line details: (Record ID = 3) linked to this transaction line. A transaction line is repeated within a file if the are multiple detail lines for it multiple lots and/or dimensions). |
|
Unit Cost |
Numeric |
8.4 |
Maps to invoice line unit cost, exclusive of tax. Invoice lines generated always have a tax type E. |
|
Pricing Unit |
Character |
4 |
Maps to invoice line unit of measure. This must be a valid unit of measure defined for the inventory item. It can be either the stocking unit of measure or the purchasing unit of measure - Greentree Desktop handles any unit of measure conversions if they exist. |
|
Tax Rate |
Numeric |
2,2 |
Maps to invoice line tax percentage. |
|
Net Value |
Numeric |
12,2 |
Maps to net value of the line (tax exclusive). |
|
Narration |
Character |
20 |
[Optional] Maps to AP invoice line narration. |
Transaction Line Example 1:
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
Transaction Line Example 2:
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
3,'RM00102138',16,64.8,'B',,,,,
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
3,'RM00102139',16,64.8,'B',,,,,
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
3,'RM00102137',16,64.8,'B',,,,,
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
3,'RM00102140',16,64.8,'B',,,,,
2,'2026696',1036.803,1.32,'MTR',10,1368.58,'3532565458',,
The second example demonstrates how lot or dimension detail lines are for transaction lines in the file. Where quantities associated with multiple lots and/or dimensions make up the line quantity, the transaction line (Record ID = 2) repeats in the file - this is identifiable because the same item number is referenced in each repeating line.
Following each repeating line is the detail line (Record ID = 3), which contains lot/dimension detail of the line. The example above indicates that there is one transaction line for item 2026696, and this consists of 4 lots: RM00102138, RM00102139, RM00102137, RM00102140. See the next section for information about what is contained in the detail line (Record ID = 3).
Note: The total line quantity for the line is calculated using the sum of quantities for each detail line attached to it.
Detail (Lot/Dimension) Section:
|
Field |
Type |
Size |
Comment |
|
Record ID |
Character |
1 |
Must equal 3 |
|
Lot Number |
Character |
12 |
Maps to the lot number associated with the transaction line. |
|
Quantity |
Character |
4 |
Maps to the integer piece) quantity for lot and dimension. The process applies dimension conversions and UOM conversions (if the transaction line Pricing Unit is not equal to item's stocking unit of measure) to convert this value into the stocking unit of measure, so that the total transaction line quantity can be derived. If there are multiple dimensions within a lot, there is repeating detail lines, which are identifiable because the same lot number is referenced in each repeating line, and only the dimension differs. The transaction line quantity is calculated using the sum of the stocking unit of measure quantities of the detail lines. |
|
Dimension |
Numeric |
2,2 |
Maps to the dimension associated with the lot and quantity in the line. |
|
Unit Type |
Character |
1 |
Must equal P (indicates line quantity is a piece count). |
Detail Line Example 1:
3,'436951',10,2.4,'P'
Detail Line Example 2:
2,'1234',380.4,4.8,'MTR',0,1825.92,'J323',,
3,'436951',10,2.4,'P',,,,,
3,'436951',40,3.9,'P',,,,,
3,'436951',15,4.2,'P',,,,,
3,'436951',23,4.8,'P',,,,,
3,'436951',5,5.4,'P',,,,,
The second example demonstrates a repeating lot detail line, indicating that there are multiple dimensions of the lot. In this example, there are 5 dimensions for lot 436951. They are: 2.4, 3.9, 4.2, 4.8, and 5.4.
When the file is processed, Greentree Desktop calculates transaction line quantities using the sum of dimension quantities, derived from detailed lines if the piece count for each dimension is converted into a stocking unit of measure quantity for each dimension). This quantity is then multiplied by the unit cost in the transaction line (Record ID = 2).
The sum of all calculated transaction line quantities is then compared to the total net invoice value in the header line (Record ID = 1). If this values are equal, no adjustment is required. If they are not equal, Greentree Desktop creates a general ledger adjustment line and adds it to the AP invoice using the general ledger account specified on the import form.
Adjustments may be required because of roundings of the conversions of dimension piece quantities to stocking unit of measure quantities.