Sage 500 ERP (formerly MAS 500) User-Defined Item Category

Written by Joe Noll | Mar 6, 2014 8:36:00 AM

I am sure that you have used the following functionality within Sage 500 ERP already: Vendor Items and Customer Items. This allows you to create a link between your items and your customer and vendor items. This information is stored in the following table timItemAliasCat and timItemAlias.

If you look at the information in the Schema Browser for table timItemAliasCat under the field AliasType you will see that you have a User-Defined option for alias category.

If you run the following SELECT you will see they actually have some 0 defined AliasType records already.

<span id="lnum1" style="color: #606060;">   1:</span> <span style="color: #0000ff;">SELECT</span> * <span style="color: #0000ff;">from</span> timItemAliasCat

Sage 500 ERP does not have a User Interface for you to perform maintenance for this AliasType but it does not mean that you are not able to use it. You would have to import this information yourself or build your own UI to perform maintenance of the base table but then you could use this information to create views for BI or use it on labels, reports, forms, etc. The timItemAliasCat table is a Parent table of the following tables: (see the Schema Browser for more definition on these tables)

timCustItemAliasCat – Contains a list of Item Alias Categories that can be used for Customers. Use this for items with an AliasType of 1.
timVendItemAliasCat – Contains a list of Item Alias Categories that can be used for Vendors. Use this for items with an AliasType of 2.
timItemAlias – Contains alternative Item IDs and Item Numbers. Each item in the Item table can have an unlimited number of alternate IDs in this table. Each of these records belong to a category of alias numbers, meaning 0, 1, 2. So records in this table that are attached to a 0 AliasType do not need any records in the first 2 tables but you can use them. You could also create multiple ItemAliasCat records with a 0 AliasType. You can give each of them their own unique ID and Description plus they will have their own key.

You create a record in the timItemAliasCat table for example:

<span id="lnum1" style="color: #606060;">   1:</span> <span style="color: #008000;">/*</span>

<span id="lnum2" style="color: #606060;">   2:</span>

<span id="lnum3" style="color: #606060;">   3:</span> <span style="color: #008000;">DISCLAIMER: This code is for demo purposes only! It contains no error checking or validation. It contains no warranties. Use at your own risk!</span>

<span id="lnum4" style="color: #606060;">   4:</span>

<span id="lnum5" style="color: #606060;">   5:</span> <span style="color: #008000;">*/</span>

<span id="lnum6" style="color: #606060;">   6:</span>

<span id="lnum7" style="color: #606060;">   7:</span> <span style="color: #008000;">-- Create a record in the table for UDF types (ItemAliasCatKey is an IDENTITY column so it increments on its own)</span>

<span id="lnum8" style="color: #606060;">   8:</span> INSERT <span style="color: #0000ff;">INTO</span> timItemAliasCat (AliasType, CompanyID, Description, ItemAliasCatID)

<span id="lnum9" style="color: #606060;">   9:</span>     <span style="color: #0000ff;">VALUES</span> (0, <span style="color: #006080;">'SOA'</span>, <span style="color: #006080;">'My UDF Item Alias'</span>, <span style="color: #006080;">'MyUDFAlias'</span>);

<span id="lnum10" style="color: #606060;">  10:</span>

<span id="lnum11" style="color: #606060;">  11:</span> <span style="color: #008000;">-- Declare variable for next key value</span>

<span id="lnum12" style="color: #606060;">  12:</span> <span style="color: #0000ff;">DECLARE</span> @_iAliasCatKey <span style="color: #0000ff;">int</span>;

<span id="lnum13" style="color: #606060;">  13:</span>

<span id="lnum14" style="color: #606060;">  14:</span> <span style="color: #008000;">-- Get the ItemAliasCatKey that was assigned in the above INSERT</span>

<span id="lnum15" style="color: #606060;">  15:</span> <span style="color: #0000ff;">SELECT</span> @_iAliasCatKey = ItemAliasCatKey

<span id="lnum16" style="color: #606060;">  16:</span> <span style="color: #0000ff;">FROM</span> timItemAliasCat

<span id="lnum17" style="color: #606060;">  17:</span> <span style="color: #0000ff;">WHERE</span> CompanyID = <span style="color: #006080;">'SOA'</span>

<span id="lnum18" style="color: #606060;">  18:</span>   <span style="color: #0000ff;">AND</span> ItemAliasCatID = <span style="color: #006080;">'MyUDFAlias'</span>

<span id="lnum19" style="color: #606060;">  19:</span>

<span id="lnum20" style="color: #606060;">  20:</span> <span style="color: #008000;">-- This creates 2 new UDF Item Aliases for ItemID 'Shovel-P200' in SOA company</span>

<span id="lnum21" style="color: #606060;">  21:</span>

<span id="lnum22" style="color: #606060;">  22:</span> <span style="color: #008000;">-- Declare variable for next key value</span>

<span id="lnum23" style="color: #606060;">  23:</span> <span style="color: #0000ff;">DECLARE</span>    @_iAliasKey <span style="color: #0000ff;">int</span>

<span id="lnum24" style="color: #606060;">  24:</span>

<span id="lnum25" style="color: #606060;">  25:</span> <span style="color: #008000;">-- Get the next key value </span>

<span id="lnum26" style="color: #606060;">  26:</span> <span style="color: #0000ff;">exec</span> spGetNextSurrogateKey <span style="color: #006080;">'timItemAlias'</span>, @_iAliasKey;

<span id="lnum27" style="color: #606060;">  27:</span>

<span id="lnum28" style="color: #606060;">  28:</span> <span style="color: #0000ff;">SELECT</span> @_iAliasKey

<span id="lnum29" style="color: #606060;">  29:</span>

<span id="lnum30" style="color: #606060;">  30:</span> <span style="color: #008000;">-- Create Item Alias records for the new UDF type</span>

<span id="lnum31" style="color: #606060;">  31:</span> INSERT <span style="color: #0000ff;">INTO</span> timItemAlias (ItemAliasCatKey, ItemAliasDesc, ItemAliasID, ItemAliasKey, ItemKey)

<span id="lnum32" style="color: #606060;">  32:</span>     <span style="color: #0000ff;">VALUES</span> (@_iAliasCatKey, <span style="color: #006080;">'My Item Alias 1'</span>, <span style="color: #006080;">'MyUDF1'</span>, @_iAliasKey, 281);

<span id="lnum33" style="color: #606060;">  33:</span>

<span id="lnum34" style="color: #606060;">  34:</span> <span style="color: #008000;">-- Get the next key value</span>

<span id="lnum35" style="color: #606060;">  35:</span> <span style="color: #0000ff;">exec</span> spGetNextSurrogateKey <span style="color: #006080;">'timItemAlias'</span>, @_iAliasKey;

<span id="lnum36" style="color: #606060;">  36:</span>

<span id="lnum37" style="color: #606060;">  37:</span> <span style="color: #008000;">-- Create Item Alias records for the new UDF type</span>

<span id="lnum38" style="color: #606060;">  38:</span> INSERT <span style="color: #0000ff;">INTO</span> timItemAlias (ItemAliasCatKey, ItemAliasDesc, ItemAliasID, ItemAliasKey, ItemKey)

<span id="lnum39" style="color: #606060;">  39:</span>     <span style="color: #0000ff;">VALUES</span> (@_iAliasCatKey, <span style="color: #006080;">'My Item Alias 2'</span>, <span style="color: #006080;">'MyUDF2'</span>, @_iAliasKey, 281);

<span id="lnum40" style="color: #606060;">  40:</span>

<span id="lnum41" style="color: #606060;">  41:</span>

<span id="lnum42" style="color: #606060;">  42:</span> <span style="color: #0000ff;">SELECT</span> i.ItemKey, i.CompanyID, i.ItemID, ia.ItemAliasID, iac.ItemAliasCatID, iac.AliasType

<span id="lnum43" style="color: #606060;">  43:</span> <span style="color: #0000ff;">FROM</span> timItem i

<span id="lnum44" style="color: #606060;">  44:</span>     <span style="color: #0000ff;">INNER</span> <span style="color: #0000ff;">JOIN</span> timItemAlias ia

<span id="lnum45" style="color: #606060;">  45:</span>         <span style="color: #0000ff;">ON</span> ia.ItemKey = i.ItemKey

<span id="lnum46" style="color: #606060;">  46:</span>     <span style="color: #0000ff;">INNER</span> <span style="color: #0000ff;">JOIN</span> timItemAliasCat iac

<span id="lnum47" style="color: #606060;">  47:</span>         <span style="color: #0000ff;">ON</span> iac.ItemAliasCatKey = ia.ItemAliasCatKey

<span id="lnum48" style="color: #606060;">  48:</span> <span style="color: #0000ff;">WHERE</span> i.ItemKey = 281;

I have seen this used in the auto parts industry to create cross-reference items for the same part number but different manufacturers part number.

View full post