In MAS 500 there is the concept of a “Kit” which is a group of items that are bundled together and sold as a single entity. Think of something like a computer which consists of various hardware and software components. The Kit itself has a separate price than what the sum of all of the components may be.
MAS 500 uses two tables to store the Kits in the item catalog. First of the Kit item has an ordinary line in the timItem table, just like any other item. These items exist with an ItemType = 7. These items then have a foreign key relationship in the timKitCompList via timItem.ItemKey = timKitCompList.KitItemKey. The timKitCompList is a table which contains the definitions of what items make up the kit. As described, the KitItemKey contains the ItemKey of the actual kit item from the timItem table, while the timKitCompList.ItemKey contains the ItemKey for the various components in the kit. These components can then tie back to the timItem table via timKitCompList.ItemKey = timItem.ItemKey.
We can see a sample query showing the complete query here:
select
a.itemkey as kit_itemkey, –primary kit item
b.shortdesc as kit_shortdesc, –primary kit description
a.stdprice as kit_stdprice, –price of kit
cast(c.compitemqty as int) as comp_quantity, –quantity of component in kit
d.itemkey as comp_itemkey, –component item
e.shortdesc as comp_shortdesc –component description
from
dbo.timitem a with(NOLOCK) left join –Product table
dbo.timitemdescription b with(NOLOCK) –Product description
on a.itemkey = b.itemkey left join
dbo.timkitcomplist c with(NOLOCK) –List sub components
on a.itemkey = c.kititemkey left join
dbo.timitem d with(NOLOCK) –sub product table
on c.compitemkey = d.itemkey left join
dbo.timitemdescription e with(NOLOCK) –Sub Product description
on d.itemkey = e.itemkey
where
a.itemtype = 7
It should be noted that there are two types of Kits: BTO Kits and Assembled Kits. BTO Kits (ItemType=7) stand for Build To Order Kits and are kits where the components are modifiable. One other type of kit is the Assembled Kit which is a pre-defined kit that is not modifiable. This type of kit has a ItemType=8.
