Skip to content


Worksheets

Sequoia v5.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 number of rows. That worksheet could 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 existing worksheet functionality within Sequoia, based around CHIEF declarations, does not comfortably fit the requirements for populating CDS Declaration data. It is felt the idea of having MS Excel templates and an XML schema with specific set columns together with a set number of subordinate records (Documents, AI Statements, etc) is too restrictive.

Therefore, the worksheets to support CDS Declarations are more flexible.

There is no restriction on the number of repeating ‘collections’ of data that can be included (such as documents, certificates and authorisations, additional information statements, previous documents etc.).

For example, a user can decide how many Documents they wish to add for a row and whether to provide the type, status and reference for each Document or just one or two of those property values. In addition, a user can add as many “Customer Defined” values as required.

The mechanisms for generating a worksheet to use in a CDS Declaration within Sequoia will remain the same as that for CHIEF declarations. There is a new Sequoia API method to import XML data, or data can be imported in MS Excel/XML format through the Sequoia client in the same way CHIEF worksheets were imported.

In addition, worksheets can still be created “manually” through an editor in the Sequoia user interface. The procedure for importing worksheet data into a CDS Declaration is the same as that for CHIEF Declarations.

Spreadsheet Construction

The spreadsheet must have two sheets. Sheet one should be labelled "Header" and sheet two labelled "Rows".

Rather than having 'fixed' columns, Sequoia will import the data so long as the column caption (row 1) matches one of the captions listed below. Any columns included in the spreadsheet that do not match an expected column caption will be ignored. The columns in the spreadsheet can be in any order, however for subordinate records (Documents, AI Statements, etc) refer to the 'Notes' section at the end of Row Data below.

Data Mapping

The following is a list of supported column captions and, where applicable, an explanation of where in a CDS Declaration the cell data would ultimately be mapped.

Header Data

The worksheet header consists of:

Column Caption Data Type / Restriction Comments
Invoice Number C..20
Purchase Order Number C..20
Shipment Date DateTime
Shipment Master C..25
Shipment House C..25
Transport ID C..27
Supplier C..20
Delivery Terms A3 Delivery Terms [20]
Delivery Terms UN/LOCODE * A5 UN/LOCODE describing the Delivery Terms location
Delivery Terms Location* A2 + AN..35 Country Code and description for the Delivery Terms location
Warehouse ID A1 + AN..35 Warehouse [49]
Warehouse Site AN..20
Net Mass N..16,6
Ignore Product Catalogue True or False
  • Either the UN/LOCODE or the Location should be specified when using Delivery Terms, not both.

Row Data

The worksheet rows consist of:

Column Caption Import/Export Date Type/Restriction API Element Name Notes/Mapping Description
Line Number Both Integer lineNumber Mandatory. Must be numeric and individual for each row
Order Number Both C..20 orderNumber
Product Code Both C..30 productCode
SKU Both C..30 sku
Serial Number Both C..20 serialNumber
Purchase Order Both C..20 purchaseOrder
Invoice Number Both C..20 invoiceNumber
Commodity Both AN10 (Imports) AN8 (Exports) commodityCode Commodity Code [33] DE 6/14 and 6/15
Add Commodity Code Both AN4 additionalCommodityCode *Commodity Additional Code(s) [33] DE 6/16 and 6/17
Procedure Both AN4 procedureCode Procedure Code [37] DE 1/10
Add Procedure Code Both AN3 additionalProcedureCode *Additional Procedure Code(s) [37] DE 1/11
Goods Description Both C..512 goodsDescription Goods Description [31] DE 6/8
CUS Code Both N8 cusCode CUS Code [31] DE 6/13
Origin Country Import A2 originCountry Country of Origin [34a] DE 5/15
Country of Preferential Origin Import AN..4 countryOfPreferentialOrigin Country of Preferential Origin [34b] DE 5/16
Preference Import AN3 preference Preference [36] DE 4/17
Quota Import AN6 quota Quota [39] DE 8/1
Quantity Both N..16,6 quantity Combined with Unit Price to calculate Item Price [42] DE 4/14
Currency Both A3 currency Item Price Currency [42]
Unit Price Both N..16,2 unitPrice Combined with Quantity to calculate Item Price [42] DE 4/14
Total Value Both N..16,2 totalValue Item Price [42] DE 4/14
Statistical Value Both N..16,2 statisticalValue Statistical Value [46] DE 8/6
Net Mass Both N..16,6 netMass Net Mass [38] DE 6/1
Gross Mass Both N..16,6 grossMass Gross Mass [35] DE 6/5
Valuation Method Import N1 valuationMethod Valuation Method [43] DE 4/16
Valuation Indicators Import N4 valuationIndicators Valuation Indicators [45] DE 4/13
Dispatch Country Both A2 dispatchCountry Dispatch Country [15a] DE 5/14
Destination Country Both A2 destinationCountry Destination Country [17a] DE 5/8
Transaction Nature Both N..2 natureOfTransaction Nature of Transaction [24] DE 8/5
Consignor EORI Export AN..17 consignor/eori Consignor EORI DE 3/8
Consignor Name Export C..70 consignor/name Consignor Name DE 3/7
Consignor Street Export C..70 consignor/street Consignor Street DE 3/7
Consignor City Export C..35 consignor/city Consignor City DE 3/7
Consignor PostCode Export C..9 consignor/postcode Consignor Post Code DE 3/7
Consignor Country Export A2 consignor/countrycode Consignor Country Code DE 3/7
Consignor ShortCode Export C..20 consignor/shortcode Lookup to existing CRM Account
Consignor External ID Export C..20 consignor/externalId Lookup to existing CRM Account
Consignee EORI Export AN..17 consignee/eori Consignee [08] EORI DE 3/10
Consignee Name Export C..70 consignee/name Consignee [08] Name DE 3/9
Consignee Street Export C..70 consignee/street Consignee [08] Street DE 3/9
Consignee City Export C..35 consignee/city Consignee [08] City DE 3/9
Consignee PostCode Export C..9 consignee/postcode Consignee [08] Post Code DE 3/9
Consignee Country Export A2 consignee/countrycode Consignee [08] Country Code DE 3/9
Consignee ShortCode Export C..20 consignee/shortcode Lookup to existing CRM Account
Consignee External ID Export C..20 consignee/externalId Lookup to existing CRM Account
Exporter EORI Import AN..17 exporter/eori Exporter [02] EORI DE 3/2
Exporter Name Import C..70 exporter/name Exporter [02] Name DE 3/1
Exporter Street Import C..70 exporter/street Exporter [02] Street DE 3/1
Exporter City Import C..35 exporter/city Exporter [02] City DE 3/1
Exporter PostCode Import C..9 exporter/postcode Exporter [02] Post Code DE 3/1
Exporter Country Import A2 exporter/countrycode Exporter [02] Country Code DE 3/1
Exporter ShortCode Import C..20 exporter/shortcode Lookup to existing CRM Account
Exporter External ID Import C..20 exporter/externalId Lookup to existing CRM Account
Seller EORI Import AN..17 seller/eori Seller [02] EORI DE 3/25
Seller Name Import C..70 seller/name Seller [02] Name DE 3/24
Seller Street Import C..70 seller/street Seller [02] Street DE 3/24
Seller City Import C..35 seller/city Seller [02] City DE 3/24
Seller PostCode Import C..9 seller/postcode Seller [02] Post Code DE 3/24
Seller Country Import A2 seller/countrycode Seller [02] Country Code DE 3/24
Seller ShortCode Import C..20 seller/shortcode Lookup to existing CRM Account
Seller External ID Import C..20 seller/externalId Lookup to existing CRM Account
Buyer EORI Import AN..17 buyer/eori Buyer [08] EORI DE 3/27
Buyer Name Import C..70 buyer/name Buyer [08] Name DE 3/26
Buyer Street Import C..70 buyer/street Buyer [08] Street DE 3/26
Buyer City Import C..35 buyer/city Buyer [08] City DE 3/26
Buyer PostCode Import C..9 buyer/postcode Buyer [08] Post Code DE 3/26
Buyer Country Import A2 buyer/countrycode Buyer [08] Country Code DE 3/26
Buyer ShortCode Import C..20 buyer/shortcode Lookup to existing CRM Account
Buyer External ID Import C..20 buyer/externalId Lookup to existing CRM Account
Tax Type Import AN3 taxline/taxType *Tax Lines [47] Type DE 4/3
MoP Import A1 taxline/methodOfPayment Tax Lines [47] MoP DE 4/8
Measurement Unit Import AN..4 taxline/measurementUnit Tax Lines [47] Meas Unit DE 4/4
Tax Base Quantity Import N..16,2 taxline/taxBaseQuantity Tax Lines [47] Tax Base Qty DE 4/4
Tax Currency Import A3 taxline/currency Tax Lines [47] Currency DE 4/6 and DE 4/7
Tax Base Amount Import N..16,2 taxline/taxBaseAmount Tax Lines [47] Tax Base Amount DE 4/4
Tax Payable Import N..16,2 taxline/taxPayable Tax Lines [47] Tax Payable DE 4/6
Tax Total Import N..16,2 taxline/total Tax Lines [47] Total DE 4/7
Package Kind Both AN2 package/kind *Packages [31] Type DE 6/9
Package Number Both N..8 package/number Packages [31] Number DE 6/10
Package Marks Both C..512 package/marks Packages [31] Shipping Marks DE 6/11
Prev Doc Category Both A1 previousDocument/category *Simplified Declaration / Previous Documents [40] Category DE 2/1
Prev Doc Type Both AN3 previousDocument/type Simplified Declaration / Previous Documents [40] Type DE 2/1
Prev Doc Reference Both C..35 previousDocument/reference Simplified Declaration / Previous Documents [40] Reference DE 2/1
Prev Doc Identifier Both N..5 previousDocument/identifier Simplified Declaration / Previous Documents [40] Item Identifier DE 2/1
Doc Type Both AN4 documentAndCertificate/type *Documents, certificates, authorisations… [44] Type DE 2/3 and 8/7
Doc Status Both A2 documentAndCertificate/status Documents, certificates, authorisations… [44] Status DE 2/3 and 8/7
Doc Reference Both C..35 documentAndCertificate/reference Documents, certificates, authorisations… [44] Reference DE 2/3 and 8/7
Doc Units Both AN..4 documentAndCertificate/units Documents, certificates, authorisations… [44] Units DE 8/7
Doc Quantity Both N..16,6 documentAndCertificate/quantity Documents, certificates, authorisations… [44] Quantity DE 8/7
Doc Validity Date Both DateTime documentAndCertificate/validityDate Documents, certificates, authorisations… [44] Validity Date DE 8/7
Doc Issuing Auth Both C..70 documentAndCertificate/issuingAuthority Documents, certificates, authorisations… [44] Issuing Authority DE 8/7
Doc Reason Both C..35 documentAndCertificate/reason Documents, certificates, authorisations… [44] Reason DE 2/3 and 8/7
Add Deduct Code Import A2 additionOrDeduction/code *Additions and Deductions [45] Code DE 4/9
Add Deduct Currency Import A3 additionOrDeduction/currency Additions and Deductions [45] Currency DE 4/9
Add Deduct Amount Import N..16,2 additionOrDeduction/amount Additions and Deductions [45] Amount DE 4/9
AI Code Both AN5 additionalInformation/code *Additional Information [44] Code DE 2/2
AI Text Both C..512 additionalInformation/description Additional Information [44] Description DE 2/2
Fiscal Ref Role Both AN..3 fiscalReference/role *Additional Fiscal References [44] Role DE 3/40
Fiscal Ref Identifier Both AN..17 fiscalReference/identifier Additional Fiscal References [44] Identifier DE 3/40
Supply Chain Role Both AN..3 supplyChainActor/role *Additional Supply Chain Actors [44] Role DE 3/37
Supply Chain Identifier Both AN..17 supplyChainActor/identifier Additional Supply Chain Actors [44] Identifier DE 3/37
Container Number Both AN..17 containerNumber *Containers [31] Identification number DE 7/10
Customer Defined Type Both No restriction customerDefinedData/code *Customer defined
Customer Defined Value Both No restriction customerDefinedData/value

*Notes

These columns can repeat to add multiple records. The following rules apply:

  • There can be a maximum of 99 Additional Commodity Codes
  • There can be a maximum of 99 Additional Procedure Codes
  • Tax Type, Tax MOP, Tax Meas Unit, Tax Base Quantity, Tax Currency, Tax Base Amount, Tax Payable and Tax Total are grouped and treated as a single Tax Line record. There can be a maximum of 99 Tax Lines.
  • Package Kind, Number and Marks are grouped and treated as a single Package record. There can be a maximum of 99 Packages.
  • Previous Document, Type and Reference are grouped and treated as a single Previous Document record. There can be a maximum of 99 Previous Documents.
  • Document Type, Status and Reference etc. are grouped and treated as a single Document record. There can be a maximum of 99 Documents.
  • Add Deduct Code, Add Deduct Currency and Add Deduct Amount are grouped and treated as a single Addition or Deduction record. There can be a maximum of 99 Additions or Deductions.
  • Additional Information (AI) Code and Text are grouped and treated as a single AI Statement record. There can be a maximum of 99 AI Statements.
  • Fiscal Reference Role and Identifier are grouped and treated as a single Additional Fiscal Reference. There can be a maximum of 99 Additional Fiscal References.
  • Supply Chain Actor Role and Identifier are grouped and treated as a single Additional Supply Chain Actor. There can be a maximum of 99 Additional Supply Chain Actors.
  • There can be a maximum of 9999 container numbers.
  • Customer Defined Type and Value are grouped and treated as a single Customer Defined record. There can be an unlimited number of Customer Defined records.

Not all values need to be supplied for each of these collections. The order of the columns (reading from left to right) will determine whether a new record is added to a collection or if the data in that column is added to an existing record in the collection.

For example, given the following columns,

Subordinate Record Example

a total of 5 Documents would be created within the worksheet as follows:

Record # Document Type Document Status Document Reference
1 C505 AE 12345
2 C506
3 N920 678
4 JE 888
5 999