SKU Interface

How to Structure Your Data

If you have large amounts of highly complex, nested inventory information, you should use the XML column in the SKU_DESCRIPTOR table to store that information, rather than using nested SKU_DESCRIPTOR records. However, it will not be possible to search for SKUs using the XML info. You will only be able to search on the non-XML columns in the SKU and SKU_DESCRIPTOR tables.

If you have small or medium amounts of less complex inventory information, you can use nested SKU_DESCRIPTOR records instead. Using this method, it will be possible to find a SKU by a sub-descriptor.

SKU Table

The following sample SKU record shows a part used to make Novelty phones. The Novelty stock code is 2002, which is used to form the XID. This corresponds to the packaged_item novelty.8946. The warehouse is novelty.wh1. The supplier is General Electric, who also currently owns the inventory.

SKU_GID = novelty.2002-wh1
SKU_xid = 2002-wh1
Packaged_item_GID = novelty.8946
Warehouse_location_GID = novelty.wh1
Supplier_corporation_GID = novelty.ge
Owner_corporation_GID = novelty.ge
Quantity_on_hand = 1800
Min_level = 100
Max_level = 2000
Domain_name = novelty

SKU_DESCRIPTOR table - BLOB

Transportation and Global Trade Management Cloud cannot show BLOBs in tree view of the inventory manager.

This section illustrates the relational approach to storing SKU descriptor and sub-descriptor information in using the SKU_DESCRIPTOR table. This method would be used when it is necessary to use standard SQL to search SKU descriptor data.

The example below shows a top-level SKU_DESCRIPTOR record. Notice that the parent_sku_descriptor_seq is null.

SKU_GID = novelty.2002-wh1
SKU_descriptor_seq = 1
SKU_descriptor_type = status
SKU_desriptor_value = held
SKU_descriptor_quantity = 1000
Parent_sku_descriptor_seq = null
Domain_name = novelty

The example below shows a level-2 SKU_DESCRIPTOR record. The parent_sku_descriptor_seq is set to 1, pointing to the previous example.

SKU_GID = novelty.2002-wh1
SKU_descriptor_seq = 2
SKU_descriptor_type = reason
SKU_descriptor_value = damaged
SKU_descriptor_quantity = 600
Parent_sku_descriptor_seq = 1
Domain_name = novelty

The example below shows a level-3 SKU_DESCRIPTOR record. The parent_sku_descriptor_seq is set to 2, pointing to the previous example.

SKU_GID = novelty.2002-wh1
SKU_descriptor_seq = 3
SKU_descriptor_type = batch
SKU_descriptor_value = 001
SKU_descriptor_quantity = 250
Parent_sku_descriptor_seq = 2
Domain_name = novelty
 
SKU_GID = novelty.2002
SKU_descriptor_seq = 4
SKU_descriptor_type = batch
SKU_descriptor_value = 002
SKU_descriptor_quantity = 300
Parent_sku_descriptor_seq = 2
Domain_name = novelty
 
SKU_GID = novelty.2002
SKU_descriptor_seq = 5
SKU_descriptor_type = batch
SKU_descriptor_value = 003
SKU_descriptor_quantity = 50
Parent_sku_descriptor_seq = 2
Domain_name = novelty

SKU_DESCRIPTOR Table - XML

If the SKU descriptor information need not be fully searchable using standard SQL, then the XML column in the SKU_DESCRIPTOR table may be used to represent the information at level 2 and below. In other words, it would be possible to use standard SQL to search for a SKU descriptor, but not for a SKU sub-descriptor.

An example situation where the XML method may not be appropriate would be where the top level SKU is a combination of shoes of different styles. The top level SKU_DESCRIPTOR records would have one row for each style. The level 2 SKU_DESCRIPTOR would have counts of sizes within each style. A query to determine the total inventory of size 9 shoes across all styles would not be possible using the XML method. You can think of similar examples for the auto industry, i.e. find the inventory of all cars with anti-lock brakes, etc.

When using the XML method for representing detailed SKU_DESCRIPTOR information, each client implementation will be responsible for developing their own industry-specific XML schema for that information. By default, the UI will display this information in a nicely formatted manner. The UI provides a mechanism whereby you can install customer-defined XSL for formatting information. However, this XSL file is purely optional.

Below is a snippet of how the information from the previous section might appear in the database if the XML approach is used instead of the nested SKU_DESCRIPTOR method. In this case, the parent_sku_descriptor_seq column is always null, and the XML column is used instead. In this case, the top level status information is available relationally. However, the lower level descriptors within that status are represented inside the XML.

SKU_GID=novelty.2002-wh1
SKU_descriptor_seq = 1
SKU_descriptor_type = status
SKU_descriptor_value = held
SKU_descriptor_quantity = 1000
Domain_name = novelty
Xml = 
<SkuDescriptor>
  <type>damaged</type>
  <value>001</value>
  <quantity>600</quantity>
<SkuDescriptor>
  <type>batch</type>
  <value>001</value>
  <quantity>250</quantity>
</SkuDescriptor>
<SkuDescriptor>
  <type>batch</type>
  <value>002</value>
  <quantity>300</quantity>
</SkuDescriptor> 
… etc …
</SkuDescriptor>