Introduction¶
This document describes the CSV interfaces supported by OrderFlow for the import of order data.
The authoritative version of this document is held in the Realtime Despatch version control system. The document is subject to continual change; please check the current version with Realtime Despatch before relying on the contents.
OrderFlow provides a sophisticated XML interface with third party systems, which is the preferred method of integrating with third party systems. The XML interface is described in the document ‘OrderFlow 3.x API Documentation’.
CSV Imports¶
CSV datafiles can be uploaded to OrderFlow manually from the user’s PC or can be left on the server that is hosting OrderFlow by FTP transfer.
The OrderFlow import tools can support a wide range of different CSV formats, the format described in this document is the one that conforms most closely to the internal OrderFlow data model. As a result it requires minimal changes to the default import mechanism and should be used if no other format is preferred.
When order files are uploaded to OrderFlow manually the user receive immediate feedback, the successful imports will be confirmed and meaningful errors should be returned if an import fails. Order files that are left on the OrderFlow server by FTP will be automatically imported by a scheduled process, there is no mechanism to return import confirmations or errors via FTP, instead a scheduled report can be configured to send an import summary to a defined email address.
File structure¶
The file contents should be encoded using the UTF8 character set.
The first line of the file must contain the column headers that describe the fields in the remaining lines. The first line must contain the column names corresponding to the fields in the rest of the file. The ordering of the columns in the file is not significant.
Each order most be on a separate line, delimited by a line break (CRLF).
The last record in the file should not have an ending line break (CRLF).
Each order should contain the same number of fields.
Multiline orders should be represented by multiple lines in the file, the contents of all the fields should be identical expect those that relate to the different line items within the order.
Lines associated with the same order should always be grouped together in the order file.
All text fields should ideally be surrounded by double quotes. If fields are not enclosed with double quotes, double quotes may not appear inside the fields.
Double quotes should not appear inside the fields.
Optional fields that are not populated with data do not need to be included in the file.
Data fields¶
| Name | Mandatory | Format | Example |
|---|---|---|---|
| externalReference | YES | VARCHAR(80) | ORDREF_123000034 |
| state | Project specific | One of a predefined list of values which may be used to control how an order is processed. Normally, it is best not to set this value, instead leaving this job to the OrderFlow import mapping script | created |
| paymentGatewayIdentifier | NO | VARCHAR(120) Used to pass a payment gateway identifier to OrderFlow, if required. | google_checkout |
| paymentTransactionInfo | NO | VARCHAR(1024) | Environment dependent |
| placed | NO | TIMESTAMP Time the order was placed by the customer | 2014-11-31 13:24:06 |
| authorised | NO | TIMESTAMP Time the payment was approved (typically by a payment gateway) | 2014-11-31 15:24:09 |
| customerComment | NO | VARCHAR(1024) | As discussed, please fix UK plug |
| totalPriceNet | NO | FLOAT | 10.20 |
| totalPriceGross | NO | FLOAT | 11.99 |
| totalPriceTax | NO | FLOAT | 1.79 |
| totalTaxCode | NO | One of a list of values which may be used to control the information shown on customer paperwork | T0T1T2 |
| shippingPriceNet | NO | FLOAT
The net amount for
the shipping charge
for the order
|
2.98 |
| shippingPriceGross | NO | FLOAT | 3.50 |
| shippingTaxTotal | NO | FLOAT | 0.52 |
| shippingTaxCode | NO | One of a list of values which may be used to control the information shown on customer paperwork | T0T1T2 |
| goodsPriceNet | NO | FLOAT
The price for the goods
in the order. Includes
the goods portion of the
shipping price; excluding
the shipping price
|
10.20 |
| goodsPriceGross | NO | FLOAT | 11.20 |
| goodsTax | NO | FLOAT | 0.52 |
| goodTaxCode | NO | One of a list of values which may be used to control the information shown on customer paperwork | T0T1T2 |
| currency | NO | One of a predefined list of values (usually based on ISO 4217) which may be used to control the information shown on customer paperwork | GBPEURUSDCNY |
| currencyUnits | NO | One of a predefined list of values which may be used at the point the order is imported | pounds |
| promotionCode | NO | VARCHAR(80)
The promotion code
for the order if present
|
summer_2013 |
| promotionDescription | NO | VARCHAR(120)
The description of
the promotion
|
|
| source | NO | VARCHAR(255)
A string which
identifies
the source of the order.
Can be used within
OrderFlow to remap order
to a different channel.
|
|
| deliveryAddressLine1 | NO | VARCHAR(255) | |
| deliveryAddressLine2 | NO | VARCHAR(255) | |
| deliveryAddressLine3 | NO | VARCHAR(255) | |
| deliveryAddressLine4 | NO | VARCHAR(255) | |
| deliveryAddressLine5 | NO | VARCHAR(255) | |
| deliveryAddressLine6 | NO | VARCHAR(255) | |
| deliveryCountryCode | NO | VARCHAR(2) | |
| deliveryPostCode | NO | VARCHAR(10) | |
| deliveryContactName | NO | VARCHAR(255) | |
| deliveryEmailAddress | NO | VARCHAR(80) | |
| deliveryDayPhoneNumber | NO | VARCHAR(50) | |
| deliveryEveningPhoneNumber | NO | VARCHAR(50) | |
| deliveryMobilePhoneNumber | NO | VARCHAR(50) | |
| deliveryFaxNumber | NO | VARCHAR(50) | |
| deliveryCompanyName | NO | VARCHAR(120) | |
| invoiceAddressLine1 | NO | VARCHAR(255) | |
| invoiceAddressLine2 | NO | VARCHAR(255) | |
| invoiceAddressLine3 | NO | VARCHAR(255) | |
| invoiceAddressLine4 | NO | VARCHAR(255) | |
| invoiceAddressLine5 | NO | VARCHAR(255) | |
| invoiceAddressLine6 | NO | VARCHAR(255) | |
| invoiceCountryCode | NO | VARCHAR(2) | |
| invoicePostCode | NO | VARCHAR(10) | |
| invoiceContactName | NO | VARCHAR(255) | |
| invoiceEmailAddress | NO | VARCHAR(80) | |
| invoiceDayPhoneNumber | NO | VARCHAR(50) | |
| invoiceEveningPhoneNumber | NO | VARCHAR(50) | |
| invoiceMobilePhoneNumber | NO | VARCHAR(50) | |
| invoiceFaxNumber | NO | VARCHAR(50) | |
| invoiceCompanyName | NO | VARCHAR(120) | |
| userDefined1 | Project specific | VARCHAR(255) | |
| userDefined2 | Project specific | VARCHAR(255) | |
| userDefined3 | Project specific | VARCHAR(255) | |
| userDefined4 | Project specific | VARCHAR(255) | |
| userDefined5 | Project specific | VARCHAR(255) | |
| paidFor | NO | BOOLEAN
Flag indicating
whether shipment
payment
has been taken.
|
true |
| earliestShipDate | NO | DATETIME
The earliest date the
shipment should be
shipped
|
2012-11-31 |
| site | NO | VARCHAR(120)
Optional field
used to explicitly
select site or
warehouseto be used
for fulfilling
shipment.
Only used for
multi-site
environments.
|
WAREHOUSE_1 |
| priority | NO | INTEGER
A positive number
representing
the priority of
the shipment
|
100 |
| priorityName | NO | VARCHAR(30)
A human-readable string
representing
the priority
|
Urgent |
| weight | NO | FLOAT | 100 |
| weightUnits | NO | VARCHAR(10)
The weight unit,
which defaults to
‘gram’
|
gram |
| addressLine1 | NO | VARCHAR(255) | |
| addressLine2 | NO | VARCHAR(255) | |
| addressLine3 | NO | VARCHAR(255) | |
| addressLine4 | NO | VARCHAR(255) | |
| addressLine5 | NO | VARCHAR(255) | |
| addressLine6 | NO | VARCHAR(255) | |
| countryCode | NO | VARCHAR(2) | |
| postCode | NO | VARCHAR(10) | |
| contactName | NO | VARCHAR(255) | |
| contactName | NO | VARCHAR(255) | |
| emailAddress | NO | VARCHAR(80) | |
| dayPhoneNumber | NO | VARCHAR(50) | |
| eveningPhoneNumber | NO | VARCHAR(50) | |
| mobilePhoneNumber | NO | VARCHAR(50) | |
| faxNumber | NO | VARCHAR(50) | |
| companyName | NO | VARCHAR(120) | |
| courier | NO | VARCHAR(120)
Allows the courier
to be set directly.
Not set normally,
rather allow it to
be determined
dynamically via
script.
|
royalmail_ppi |
| deliverySuggestionCode | NO | VARCHAR(120)
A hint or suggestion
to the system which
can be used to
help determine the
actual courier
and service.
combination to be used
|
express |
| deliverySuggestionName | NO | VARCHAR(255)
The name associated
with the delivery
suggestion code.
|
Express |
| companyName | NO | VARCHAR(120) | |
| pickingMode | NO | VARCHAR(120)
Not set normally,
rather allow it to
be determined
dynamically via
script
|
batchindividual |
| userDefined1 | Project specific | VARCHAR(255) | |
| userDefined2 | Project specific | VARCHAR(255) | |
| userDefined3 | Project specific | VARCHAR(255) | |
| userDefined4 | Project specific | VARCHAR(255) | |
| userDefined5 | Project specific | VARCHAR(255) | |
| orderLineExternalReference | YES | VARCHAR(120) | DVD-MATR |
| orderLineQuantity | YES | INTEGER | 2 |
| orderLineDescription | NO | VARCHAR(1024)
Typically used for a
local language order
line description. If
not set, then product
description is used
instead
|
|
| orderLineTotalPriceNet | NO | FLOAT | 10.20 |
| orderLineTotalPriceGross | NO | FLOAT | 11.99 |
| orderLineTotalPriceTax | NO | FLOAT | 1.79 |
| orderLineTotalTaxCode | NO | VARCHAR(10) | T0 |
| orderLineUnitPriceNet | NO | FLOAT | 10.20 |
| orderLineUnitPriceGross | NO | FLOAT | 11.99 |
| orderLineUnitPriceTax | NO | FLOAT | 1.79 |
| orderLineUnitTaxCode | NO | VARCHAR(10) | T0 |
| promotionCode | NO | VARCHAR(80)
The promotion code
for the order if present
|
summer_2013 |
| PromotionPriceDescription | NO | VARCHAR(150)
The description of
the promotion price
|
£9.99 (was £12.99) |
| orderLineUserDefined1 | Project specific | VARCHAR(255) | |
| orderLineUserDefined2 | Project specific | VARCHAR(255) | |
| orderLineUserDefined3 | Project specific | VARCHAR(255) | |
| orderLineUserDefined4 | Project specific | VARCHAR(255) | |
| orderLineUserDefined5 | Project specific | VARCHAR(255) |
Note that at both an order level and a line item level either the total price or the unit price should be specified. OrderFlow will not derive order line prices if none are supplied, but it can, for example, derive a total price value from unit prices.
Prices are typically required for despatch note paperwork, and for international shipment, for customs documentation.
XML Reports¶
[Point out that reports can be transformed using XSLT]
[Output format for the XSLT should be as described in OrderFlow API document].
[Note that an XSLT document will need to be written to transform the XML into the target format].s