Worksheets

Sequoia v1.0

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

Sequoia v4.19

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	>

---