Data structures; Additional codes - uktrade/tariff-data-manual GitHub Wiki

Additional codes

What additional codes are

Goods are usually identified by their 10-digit commodity code.

Sometimes additional context is needed, beyond just the type of good identified by the commodity code, to ensure that the apporpriate tariffs and/or restrictions are applied, in these cases additional codes are used. Additional codes identify:

whether a company is subject to punitive measures, such as anti-dumping duties
products which have reduced duties because they are for pharmaceutical use
the route goods take into the UK and how different tariff or regulatory treatments apply
stolen works of art, for example from Syria or Iraq as a result of military conflict

Additional codes can be applied to more than one commodity code.

How additional codes work

Additional codes are 4 digits combining the following 2 elements:

a code type
a 3-character ID (which can also include letters)

The additional code type defines the additional code that is being addressed.

For example, the additional code C233 is made up of type C and specific ID 233.

Numeric IDs from 001 to 999 can be reused across multiple additional code type IDs. Numeric IDs are unique only when they are used with a character code.

The list of addional codes is frequently updated, with tens of new codes being added each year.

Additional Code Types

SELECT additional_code_types.sid AS "Code Type",
	additional_code_types.description AS "Description",
	CASE
		WHEN application_code = 0 THEN "Export refund nomenclature"
		WHEN application_code = 1 THEN "Additional codes"
		WHEN application_code = 3 THEN "Meursing additional codes"
		WHEN application_code = 4 THEN "Export refund for processed agricultural goods"
		END AS "Applicability"

FROM additional_code_types
        INNER JOIN common_version_groups ON additional_code_types.trackedmodel_ptr_id = common_version_groups.current_version_id

The list of additional code types is set by DBT, and rarely changes as it requires extensive updates to downstream systems. The only changes in the last two decades have been the addition of code type C in 2009, and the end of code type F in 2016.

Applicability indicates the data type the additional codes apply to. Only those labelled "Additional Codes" are still in use, however the remainder still appear in the data and are included here for completeness.

Entity-relationship diagram for additional codes

Table "Additional Codes" {
	"Code Id" char(3) [pk]
	"Code Type Id" varchar(4) [pk]
	"Validity Start" date [not null]
	"Validity End" date
	}

Table "Additional Code Descriptions" {
	"Description Id" int [pk]
	"Described Code Id" char(3) [pk]
	"Description" varchar(255)
	"Validity Start" date [not null]
	}

Table "Additional Code Types" {
	"Code Type Id" char(1) [pk]
	"Code Type Description" varchar(255)
	"Application Code" char(1) [not null]
	"Validity Start" date [not null]
	"Validity End" date
	}

Table "Footnote Additional Code Associations" {
  "Footnote Type" char(2) [pk]
  "Footnote Code" char(3) [pk]
  "Additional Code Id" int [pk]
  "Validity Start" date [not null]
  "Validity End" date
}

Table "Measures" {
    "Measure Id" [pk]
    "Additional Code Id" [pk]
    "..." "..."
}

Table "Measure Additional Code Types" {
	"Additional Code Type Id" [pk]
	"Measure Type Id" [pk]
	"Validity Start" date [not null]
    "Validity End" date
	}

Table "Measure Types" {
	"Measure Type Id" [pk]
	"..." "..."
	}

Ref: "Additional Codes"."Code Type Id" > "Additional Code Types"."Code Type Id"
Ref: "Additional Code Descriptions"."Described Code Id" > "Additional Codes"."Code Id"
Ref: "Footnote Additional Code Associations"."Additional Code Id" > "Additional Codes"."Code Id"
Ref: "Measures"."Additional Code Id" > "Additional Codes"."Code Id"
Ref: "Additional Code Types"."Code Type Id" > "Measure Additional Code Types"."Additional Code Type Id"
Ref: "Measure Additional Code Types"."Measure Type Id" > "Measure Types"."Measure Type Id"

Validation rules

Additional Codes

Code	Description
ACN1	Uniqueness for combination of fields additional code type, additional code ID and start date.
ACN13	Validity contained rule requiring that when an additional code is referred to in a measure, the validity period of the code must span the validity period of the measure.
ACN14	Deletion while in use for additional codes that are used in a measure.
ACN17	Validity contained rule for the additional code type the additional code belongs to.
ACN2	The referenced additional code type must exist and have as application code “non-Meursing” or “Export Refund for Processed Agricultural Goods.
ACN4	Validity overlaps with self rule stating the validity period of the additional code must not overlap any other additional code with the same additional code type, additional code ID and start date.
ACN5	Mandatory subrecord rule for additional code descriptions. The start date of the first description period must be equal to the start date of the additional code. No two associated description periods may have the same start date. The start date must be less than or equal to the end date of the additional code.
CT1	Uniqueness rule for additional code types.

Read the validation rules for additional codes in the system documentation.

How additional codes apply

Additional codes can only be applied to particular types of measures. For example, types 8, A, B and C can only be used with trade remedies. The full lists of measure types that can reference additional code types are shown below for each code type.

Prohibition/Restriction/Surveillance (3)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "3"
GROUP BY measure_types.sid

Dual Use (D)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "D"
GROUP BY measure_types.sid

Agricultural Tables (non-Meursing) (6)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "6"
GROUP BY measure_types.sid

Tariff Preference (2)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "2"
GROUP BY measure_types.sid

Restrictions (4)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "4"
GROUP BY measure_types.sid

Anti-dumping/countervailing measures (8,A,B,C)

SELECT measure_types.sid AS "Measure Type",
	measure_types.description AS "Description"
FROM measure_additional_code_types
    INNER JOIN common_version_groups AS cvg0
        ON measure_additional_code_types.trackedmodel_ptr_id = cvg0.current_version_id
    INNER JOIN common_tracked_models AS ctm1
        ON measure_additional_code_types.measure_type_id = ctm1.id
    INNER JOIN common_version_groups AS cvg1
        ON ctm1.version_group_id = cvg1.id
    INNER JOIN measure_types  
        ON cvg1.current_version_id = measure_types  .trackedmodel_ptr_id
    INNER JOIN common_tracked_models AS ctm2
        ON measure_additional_code_types.additional_code_type_id = ctm2.id
    INNER JOIN common_version_groups AS cvg2
        ON ctm2.version_group_id = cvg2.id
    INNER JOIN additional_code_types 
        ON cvg2.current_version_id = additional_code_types.trackedmodel_ptr_id
WHERE additional_code_types.sid = "8" OR
	additional_code_types.sid = "A" OR
	additional_code_types.sid = "B" OR
	additional_code_types.sid = "C"
GROUP BY measure_types.sid