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 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".
The "Header" sheet columns must be present and in the order shown below in Header Data.
For the "Rows" sheet, 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 "Rows" sheet 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 | Arrival Trpt ID [18] |
| 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 | Both | 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,
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 |