MAS 500 Sales Orders – Kit Structure

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:

select   
    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
from
    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
where
    d.itemkey is not null
order by
    a.sokey

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!