Commodity coding systems organize data by trade codes. For example, code HS040510 belongs to the Harmonized Commodity Description and Coding System and has the name Dairy produce; derived from milk, butter. Full description of codes are presented in the table UDM_Catalog_Special_International_Trade_Code.
To add this table to your database:
CREATE TABLE [Report].[Catalog.Special.International Trade.Code] ( [CommodityCodingSystemID] [int] NOT NULL, [Code] [nvarchar](20) NOT NULL, [Chapter] [char](2) NULL, [Heading] [char](5) NULL, [Subheading] [char](7) NULL, [Level] [int] NOT NULL, [ParentCode] [nvarchar](20) NULL, [Description] [nvarchar](1000) NOT NULL, [LevelDescription] [nvarchar](1000) NULL, [ShortDescription] [nvarchar](500) NULL, [Notes] [nvarchar](MAX) NULL, [FirstReportPeriod] [date] NOT NULL, [LastReportPeriod] [date] NOT NULL, [LastModified] [datetime] NOT NULL, [Active] [bit] NOT NULL CONSTRAINT [PK_Catalog.Special.International Trade.Code] PRIMARY KEY CLUSTERED ( [CommodityCodingSystemID] ASC, [Code] ASC, [LastReportPeriod] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Description of fields:
- CommodityCodingSystemID: Foreign key to [UDM].[Catalog.Special.International Trade.Commodity Description and Coding Systems]. For example, for Harmonized Commodity Description and Coding System (HS), CommodityCodingSystemID = 1.
- Code: Tariff code, e.g. HS040510.
- Chapter: Most commodity coding systems organize codes in chapters. For example, in the HS system, Chapter 4 contains codes for Dairy produce; birds’ eggs; natural honey; edible products of animal origin, not elsewhere specified or included. Where applicable, we indicate which chapter does the code belong to. For example, code HS040510 would have Chapter = “04”.
- Heading: Where data is organized in chapters, chapters are further subdivided to headings. For example, chapter 4 is subdivided to 10 headings, and code HS040510 belongs to heading “04.05”.
- Subheading: Where data is organized in chapters and headings, data may be further subdivided to 6-digit subheadings. For example, the code HS040510 belongs to subheading “0405.10”. In a system where we find 8- or 10-digit codes, the subheading field remains the 6-digit subheading. For example, the code CN04051011 also has Subheading field equal to “0405.10”.
- Level is the number indicating the number of digits in the HS system or systems that build upon and expand the HS system. For example, for HS040510, the level is 6, and for CN04051011 the level is 8.
- ParentCode is the code of the first immediate higher category. For example, HS040510 is the parent code for CN04051011 and HS0405 is the parent code for HS040510, etc.
- Description, LevelDescription and ShortDescription are three fields that describe the code. The Description field is the longest, most verbose and self-contained description of a field. LevelDescription is a formal only the attributes of the code that differentiate the code from a higher-level category. ShortDescription is a short, self-contained description. For example, for code CN04051011, the Description is “Dairy produce; derived from milk, natural butter, of a fat content, by weight, not exceeding 85 %, in immediate packings of a net content not exceeding 1 kg (excl. dehydrated butter and ghee)“, the LevelDescription: “Butter: Of a fat content, by weight, not exceeding 85 %: Natural butter: In immediate packings of a net content not exceeding 1 kg“, and ShortDescription: “Natural butter of a fat content, by weight, of ≥ 80% but ≤ 85%, in immediate packings of a net content of ≤ 1 kg (excl. dehydrated butter and ghee)“. In practice, we recommend using ShortDescription in displays, and that LevelDescription be used as a formal reference.
- Notes: Where provided, notes clarify which commodities do and which do not belong to a particular code. If a code has been repealed, the notes may indicate which codes were introduced instead.
- FirstReportPeriod and LastReportPeriod demark the period over which the code has been in use. By default, the FirstReportPeriod is set to January 1, 1988 and the LastReportPeriod is set to January 1, 2500.
- LastModified: Timestamp that can be used for incremental pulls.
- Active: Soft-delete flag. Active = false for soft-deleted records.
To pull the data from this feed for the first time:
To pull only data inserted, updated or soft-deleted since 2019-06-30 at 19:00 Central U.S. Time:
