In my last blog I discussed how MAS 500 has a concept of Kits and where these are stored in relation to the item tables. Similarly, the Kits can be added to Sales Orders. This post discusses the structure of the Sales Order tables as it relates to Kits.
Sales orders are stored in tsoSalesOrder. The line item details for the Sales Order are contained in tsoSOLine. Similar to the structure found in the Item area, Sales Order lines also have a relationship to the tsoSOLineCompItem. This table contains the components that may exists for a Kit that exists in the tsoSOLine table. The tsoSOLineCompItem table is a many to one relationship to the tsoSOLine table via a join between tsoSOLine.SOLineKey and tsoSOLineCompItem.SOLineKey.
In the tsoSOLineCompItem table there are only four columns:
- SOLineCompItemKey – Unique key for the table
- CompItemKey – The Item Key for the component. This relates back to the timItem table via tsoSOLineCompItem.CompItemKey = timItem.ItemKey
- CompItemQty – The quantity of the component within the Kit.
- SOLineKey – The foreign key back to the tsoSOLine table.
Notice that there is no pricing in the tsoSOLineCompItem as Kit pricing is at the Kit level (tsoSOLine) and not at the component level.
A sample query shows Kit data from the Sales Order:
a.sokey as SOkey, –Foreign key back to tsoSalesOrder
a.itemkey as kit_itemkey, –primary kit item from SO
b.shortdesc as kit_shortdesc, –primary kit description
a.unitprice as kit_unitprice, –price of kit on SO
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
dbo.tsoSOLine a with(NOLOCK) left join –SO line item table
dbo.timitemdescription b with(NOLOCK) –Product description
on a.itemkey = b.itemkey left join
dbo.tsoSOLineCompItem c with(NOLOCK) –SO Line list of sub components
on a.SOlinekey = c.SOlinekey 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
d.itemkey is not null