Worksheets
Overview
Sequoia Worksheets are essentially a collection of data that together constitutes a commercial invoice or purchase order. The worksheet consists of a header and one or more lines (rows) of information that describe goods being imported or exported.
Each line describes goods in commercial terms, commonly using product codes to identify specific items. There is no direct (one to one) correlation between a worksheet row/product code and a declaration item. Rather, several product codes could be classified under the same commodity code and be imported/exported to the same customs regime and therefore be included in the same declaration item.
Worksheets therefore function as a transition between commercially generated shipment data and the customs declarations used in the import/export process.
To add to the complexity of the function, a single worksheet is likely to consist of a number of rows. That worksheet could also potentially be consumed by more than one declaration - in the case of CFSP for example.
It is conceivable that one declaration consumes only some of the rows of a worksheet and in some cases only a reduced quantity of an individual row whilst another declaration consumes other (and some of the same) rows, or parts of rows. The structure of the data linking worksheets to declarations reflects this complexity.
The Worksheet Entity
The data structure for storing the source (commercial) data associated to a worksheet will require the following data elements. A worksheet entity consists of a header, giving summary information on the worksheet, and (zero or more) lines of worksheet detail.
Worksheet Data
Whilst a certain level of information is required to create declaration items from a worksheet row, Sequoia can upload worksheets with very little information included. Further information can be added as it becomes available, prior to consumption of the information in a declaration.
Worksheets can also be uploaded even if data within them is incorrect or invalid. On opening an uploaded worksheet in Sequoia any such errors will be highlighted and can be corrected as necessary.
Worksheet Header Data
The worksheet header consists of:
Col | Data Element | SAD box | Data | Imports | Exports | Comments |
---|---|---|---|---|---|---|
A | Invoice Number | 40 | C..20 | Y | Y | Restricted to EDIFACT Character Set B |
B | Purchase Order Number | C..20 | Y | Y | Restricted to EDIFACT Character Set B | |
C | Shipment date | D | Y | Y | ||
D | Shipment Master | C..25 | Y | Y | Master AWB or BoL number | |
E | Shipment House | C..25 | Y | Y | House AWB or BoL number | |
F | Transport ID | 21 | C..27 | Y | Y | Flight/vessel voyage number. Restricted to EDIFACT Character Set B |
G | Customer | C..20 | Y | Shortcode | ||
H | Incoterm | C3 | Y | Y | ||
I | Incoterm Location | C..25 | Y | Required if Incoterm present; otherwise not allowed. | ||
J | Warehouse ID | 49 | C..17 | Y | Y | For SAD box 49 if required |
K | Supplier Shortcode | C..20 | Y | Y | ||
L | Ignore Product Catalogue | L | Y | |||
M | Warehouse Site | C..20 | Y | Y | ||
N | Net Mass | N..14,3 | Y | Y |
Imports Worksheet Line Data
Each worksheet line can consist of the following data elements (in this order):
Col | Data Element | SAD box | Data | Comments |
---|---|---|---|---|
A | Line Number | I | Required - To maintain the order of the rows as input/uploaded. The line number should be sequential, starting with 1. | |
B | Order Number | C..20 | ||
C | Product Code | C..30 | ||
D | SKU | C..30 | ||
E | Origin Country | 34a | A2 | |
F | Goods Description | 31 | C..280 | |
G | Commodity Code | 33 | C8..10 | |
H | Item Gross Mass | 35 | N..11,3 | |
I | Preference | 36 | N3 | |
J | CPC | 37 | C7 | Customs Procedure Code |
K | Item Net Mass | 38 | N..11,3 | |
L | Quota | 39 | N6 | |
M | Quantity | 41 | N..11.3 | We want/need quantities even if the declaration doesn't need them. Otherwise there's no way to calculate or report on landed cost values (duty/VAT at line level) etc |
N | Currency | A3 | ||
O | Unit Price | N..10,2 | ||
P | Total Value | 42 | N..10,2 | |
Q | Valuation Method Code | 43 | N1 | |
R | Item Third Quantity | 44 | N..11,3 | |
S | Valuation Adjustment Code | 45 | C1 | |
T | Valuation Adjustment Percentage | 45 | N..3,3 | |
U | Item Stat Val | 46 | N..10,2 | |
V | Duty Type | 47 | AN3 | E.g. 'A00' for customs duty. From which the duty taxline type can be derived. |
W | Duty Rate | 47 | A1 | E.g. 'F' for full rate. From which the duty taxline rate can be derived. |
X | VAT Rate | 47 | A1 | E.g. 'S' for standard rate. From which the VAT taxline rate can be derived. |
Y | Packages Number | 31 | N..5 | |
Z | Packages Kind | 31 | C2 | |
AA | Packages Marks and Numbers | 31 | C..42 | |
AB | Document Code | 44 | C4 | |
AC | Document Reference | 44 | C..35 | |
AD | Document Status | 44 | C2 | |
AE | Consignor ID | 02 | AN..17 | If required at item level (EORI) |
AF | Consignor Name | 02 | C..35 | If required at item level |
AG | Consignor Street | 02 | C..35 | If required at item level |
AH | Consignor City | 02 | C..35 | If required at item level |
AI | Consignor Postcode | 02 | C..9 | If required at item level |
AJ | Consignor Country | 02 | A2 | If required at item level |
AK | Consignee ID | 02 | AN..17 | If required at item level (EORI) |
AL | Consignee Name | 02 | C..35 | If required at item level |
AM | Consignee Street | 02 | C..35 | If required at item level |
AN | Consignee City | 02 | C..35 | If required at item level |
AO | Consignee Postcode | 02 | C..9 | If required at item level |
AP | Consignee Country | 02 | A2 | If required at item level |
AQ | Ai Statement Code | 44 | C5 | Completion of any set of AI statement code and text results in the creation, where required, of an AI statement in a declaration at item level. Subsequent sets of information are not dependent on a previous set - i.e. completion of set 4 is not an error if set 3 isn't completed. |
AR | Ai Statement Text | 44 | C..70 | |
AS | Ai Statement Code 2 | 44 | C5 | |
AT | Ai Statement Text 2 | 44 | C..70 | |
AU | Ai Statement Code 3 | 44 | C5 | |
AV | Ai Statement Text 3 | 44 | C..70 | |
AW | Ai Statement Code 4 | 44 | C5 | |
AX | Ai Statement Text 4 | 44 | C..70 | |
AY | Ai Statement Code 5 | 44 | C5 | |
AZ | Ai Statement Text 5 | 44 | C..70 | |
BA | Prev Doc Class 1 | 40 | A1 | |
BB | Prev Doc Type 1 | 40 | C6 | |
BC | Prev Doc Ref 1 | 40 | C..35 | |
BD | Prev Doc Class 2 | 40 | A1 | |
BE | Prev Doc Type 2 | 40 | C6 | |
BF | Prev Doc Ref 2 | 40 | C..35 | |
BG | Prev Doc Class 3 | 40 | A1 | |
BH | Prev Doc Type 3 | 40 | C6 | |
BI | Prev Doc Ref 3 | 40 | C..35 | |
BJ | Commodity Add Code | 33 | C4 | |
BK | Serial No. | C..20 | ||
BL | Purchase Order | C..20 | ||
BM | Invoice Number | 40 | C..20 | |
BN | Customer Defined 1 | C..35 | Free format elements, the use of which can be defined by the user. These are not included in any declaration created as a result of this worksheet. They are however included in any subsequent worksheet report output by Sequoia. | |
BO | Customer Defined 2 | C..35 | ||
BP | Tax Rate Override Code | 47 | C..3 | |
BQ | Tax Amount | 47 | N..10,2 | |
BR | VAT Rate Override Code | 47 | C..3 | |
BS | VAT Amount | 47 | N..10,2 | |
BT | Document Code 2 | 44 | C4 | |
BU | Document Reference 2 | 44 | C..35 | |
BV | Document Status 2 | 44 | C2 | |
BW | Document Code 3 | 44 | C4 | |
BX | Document Reference 3 | 44 | C..35 | |
BY | Document Status 3 | 44 | C2 | |
BZ | Document Code 4 | 44 | C4 | |
CA | Document Reference 4 | 44 | C..35 | |
CB | Document Status 4 | 44 | C2 | |
CC | Document Code 5 | 44 | C4 | |
CD | Document Reference 5 | 44 | C..35 | |
CE | Document Status 5 | 44 | C2 |
The following columns were added:
Col | Data Element | SAD box | Data | Comments |
---|---|---|---|---|
CF | Duty MOP | 47 | C..3 | Duty Method of Payment - can be specified, when auto-generating tax lines, to override the default setting of 'D' or 'F' based on whether there is a deferment specified in the declaration. Sequoia groups worksheet rows by matching certain column values. In order to differentiate between a row where no Duty Method of Payment value is included and where a positive indication that Duty Method of Payment should not be declared, the value NPD should be used. |
CG | VAT MOP | 47 | C..3 | VAT Method of Payment - can be specified, when auto-generating tax lines, to override the default setting of 'D' or 'F' based on whether there is a deferment specified in the declaration. Sequoia groups worksheet rows by matching certain column values. In order to differentiate between a row where no VAT Method of Payment value is included and where a positive indication that VAT Method of Payment should not be declared, the value NPD should be used. Please note, NPD does not need to be specified when VAT is zero rated. |
Exports Worksheet Line Data
Each worksheet line can consist of the following data elements (in this order):
Col | Data Element | SAD box | Data | Comments |
---|---|---|---|---|
A | Line Number | I | Required - To maintain the order of the rows as input/uploaded. The line number should be sequential, starting with 1. | |
B | Order Number | C..20 | ||
C | Product Code | C..30 | ||
D | SKU | C..30 | ||
E | Destination Country | 34a | A2 | |
F | Dispatch Country | 34a | A2 | |
G | Origin Country | 34a | A2 | |
H | Goods Description | 31 | C..280 | |
I | Commodity Code | 33 | C8..10 | |
J | Item Gross Mass | 35 | N..11,3 | |
K | CPC | 37 | C7 | Customs Procedure Code |
L | Item Net Mass | 38 | N..11,3 | |
M | Quantity | 41 | N..11.3 | |
N | Currency | A3 | ||
O | Unit Price | N..10,2 | ||
P | Third Quantity | 44 | N..11,3 | |
Q | Statistical Value | N..10,2 | ||
R | UN Dangerous Goods Code | 44 | N4 | |
S | Packages Number | 31 | N..5 | |
T | Packages Kind | 31 | C2 | |
U | Packages Marks and Numbers | 31 | C..42 | |
V | Document Code | 44 | C4 | |
W | Document Reference | 44 | C..35 | |
X | Document Status | 44 | C2 | |
Y | Consignor ID | 02 | AN..17 | If required at item level (EORI) |
Z | Consignor Name | 02 | C..35 | If required at item level |
AA | Consignor Street | 02 | C..35 | If required at item level |
AB | Consignor City | 02 | C..35 | If required at item level |
AC | Consignor Postcode | 02 | C..9 | If required at item level |
AD | Consignor Country | 02 | A2 | If required at item level |
AE | Consignee ID | 02 | AN..17 | If required at item level (EORI) |
AF | Consignee Name | 02 | C..35 | If required at item level |
AG | Consignee Street | 02 | C..35 | If required at item level |
AH | Consignee City | 02 | C..35 | If required at item level |
AI | Consignee Postcode | 02 | C..9 | If required at item level |
AJ | Consignee Country | 02 | A2 | If required at item level |
AK | Ai Statement Code | 44 | C5 | Completion of any set of AI statement code and text results in the creation, where required, of an AI statement in a declaration at item level. Subsequent sets of information are not dependent on a previous set - i.e. completion of set 4 is not an error if set 3 isn't completed. |
AL | Ai Statement Text | 44 | C..70 | |
AM | Ai Statement Code 2 | 44 | C5 | |
AN | Ai Statement Text 2 | 44 | C..70 | |
AO | Ai Statement Code 3 | 44 | C5 | |
AP | Ai Statement Text 3 | 44 | C..70 | |
AQ | Ai Statement Code 4 | 44 | C5 | |
AR | Ai Statement Text 4 | 44 | C..70 | |
AS | Ai Statement Code 5 | 44 | C5 | |
AT | Ai Statement Text 5 | 44 | C..70 | |
AU | Prev Doc Class 1 | 40 | A1 | |
AV | Prev Doc Type 1 | 40 | C6 | |
AW | Prev Doc Ref 1 | 40 | C..35 | |
AX | Prev Doc Class 2 | 40 | A1 | |
AY | Prev Doc Type 2 | 40 | C6 | |
AZ | Prev Doc Ref 2 | 40 | C..35 | |
BA | Prev Doc Class 3 | 40 | A1 | |
BB | Prev Doc Type 3 | 40 | C6 | |
BC | Prev Doc Ref 3 | 40 | C..35 | |
BD | Commodity Add Code | 33 | C4 | |
BE | Serial No. | C..20 | ||
BF | Purchase Order | C..20 | ||
BG | Invoice Number | 40 | C..20 | |
BH | Customer Defined 1 | C..35 | Free format elements, the use of which can be defined by the user. These are not included in any declaration created as a result of this worksheet. They are however included in any subsequent worksheet report output by Sequoia. | |
BI | Customer Defined 2 | C..35 | ||
BJ | Document Code 2 | 44 | C4 | |
BK | Document Reference 2 | 44 | C..35 | |
BL | Document Status 2 | 44 | C2 | |
BM | Document Code 3 | 44 | C4 | |
BN | Document Reference 3 | 44 | C..35 | |
BO | Document Status 3 | 44 | C2 | |
BP | Document Code 4 | 44 | C4 | |
BQ | Document Reference 4 | 44 | C..35 | |
BR | Document Status 4 | 44 | C2 | |
BS | Document Code 5 | 44 | C4 | |
BT | Document Reference 5 | 44 | C..35 | |
BU | Document Status 5 | 44 | C2 |
Character Sets
Whilst it is possible to restrict the inclusion of characters in schema definitions by specifying pattern constraints against data element definitions, this is not enforced on all data elements in all schema definitions published as part of the Sequoia API.
It should be noted that much of this API is concerned with exchanging declaration data with customs. As such there is an implied constraint in that such exchanges are performed using EDIFACT messages and that these are restricted to EDIFACT character set B.
There are methods available on the API - for example 'CreateCrmAccount' - that do not directly involve data exchange with customs. Therefore there are no pattern constraints on those data elements to restrict them to the EDIFACT B character set.
However, methods that create declarations can reference a CRM account that has been created using the above method. Therefore, great care should be taken when passing data to Sequoia, to avoid using characters that are not in the list below, even where no such pattern constraint is applied at data element level in the schema.
EDIFACT Character Set B - Allowable characters
The table below lists the characters allowed by EDIFACT Character Set B.
Description | Example |
---|---|
Letters, upper case | A to Z |
Letters, lower case | a to z |
Numerals | 0 to 9 |
Space character | |
Full stop | . |
Comma | , |
Hyphen/minus sign | - |
Opening parentheses | ( |
Closing parentheses | ) |
Oblique stroke (slash) | / |
Apostrophe | ' |
Plus sign | + |
Colon | : |
Equals sign | = |
Question mark | ? |
Exclamation mark | ! |
Quotation mark | " |
Percentage sign | % |
Ampersand | & |
Asterisk | * |
Semi-colon | ; |
Less-than sign | < |
Greater-than sign | > |