Many users have noticed the existence of the IMLOCHST (I/M Location History) table and would like to know what information is stored there and how to access it. This document is an attempt to explain what information is stored in this table, how you can access it, and how it gets updated. 

The primary key index of the IMLOCHST table consists of the following columns:

• Year – IMLCOCHST_YEAR
  
• Item-Number – IMLOCHS_ITEM_NO
  
• Location – IMLOCHST_LOCATION
  

It also has an alternate key index consisting of the following columns:

• Item-Number – IMLOCHST_ALT_ITM_NO
  
• Location – IMLOCHST_ALT_LOCATN
  
• Year - IMLOCHST_ALT_YEAR
  

Because this table tends to be large, when you try to access it table through ODBC, ADO.NET or Crystal Report, consider whether you should use the primary key or the alternate index for best performance. For example, if you want to get all records in 2017, then you should use IMOCHST_YEAR to filter. See the following sample SQL statement:

      SELECT * FROM IMLOCHST where IMLOCHST_YEAR = 2017

This will cause the relational engine to use the primary key index and only return the 2017 records. The other example is if you try to get the history records of an item "ABC" for multiple years, in which case you should use IMLOCHST_ALT_ITM_NO to filter. See the following sample SQL statement:

      SELECT * FROM IMLOCHST where IMLOCHST_ALT_ITM_NO = ‘ABC’

In our database, we also provide IMLOCHST_VIEW, which joins IMLOCHST with IMITMFIL (Item Master) table for your convenience. So you can also use "SELECT * FROM IMLOCHST_VIEW where..." in the above SELECT statement.

The records in IMLOCHST are mostly created from COP Sales Journal posting. But they can also be created from Sales Desk, Purchase Order, or sometimes from utility programs to perform re-calculation. Take COP Sales Journal Posting as an example. When an invoice line item is posted, the system will check whether the matching record for the year, item number and location exists. If the corresponding record does not exist, then a new record is created. If the record does exist, then it adds to the existing record the proper month based on invoice date. Compared to the implementation of item accumulators like YTD, PTD, Last Year quantity, sales and cost amount, IMLOCHST has many benefits, such as:

• It is self-maintained and there’s no month-end or year-end procedure to clear the accumulator.
  
• It updates to the right bucket based on document date (i.e., invoice date). Therefore, if you have back-dated posting (over the month-end or year-end), it will not cause your location history to mismatch with your sub module or general ledger.
  
• It provides complete 12-month history breakdown.
  
• You can store as many years worth of history as you want.
  
• It provides the history breakdown by location. 
  

IMLOCHST stores the following type of history information.  The "IMLOCHST...1-12" are the corresponding column names in the IMLOCHST table:

• Quantity Sold – IMLOCHST_QTY_SOLD1 - 12
  
• Usage – IMLOCHST_USAGE_1 - 12
  
• Sales Amount – IMLOCHST_SALE_AMT1 - 12
  
• Cost Amount – IMLOCHST_COST_AMT1 - 12
  
• Demand Quantity Sold – IMLOCHST_DMD_QTY1 - 12
  
• Demand Sales Amount – IMLOCHST_DMD_SALE1- 12 
  
• Demand Cost Amount – IMLOCHST_DMD_COST1 - 12
  
• Sales Desk Time Out of Stock – IMLOCHST_SDOUTSTK1 - 12
  
• Sales Desk Time Quoted – IMLOCHST_SDTIMEQU1 - 12
  
• Sales Desk Quantity Quoted – IMLOCHST_SDQTYQU1 - 12
  
• Sales Desk Number of Wish List – IMLOCHST_TIMEWISH1 - 12
  
• Sales Desk Quantity of Wish List – IMLOCHST_QTYWISH1 - 12
  
• Number of Stock Watch – IMLOCHST_STKWATCH1 - 12
  
• Days Out of Stock – IMLOCHST_DAYS_OS1 - 12
  
• Qty Received – IMLOCHST_QTY_RECV1 - 12
  

All this information is further broken down to 12 columns that represent months 1 – 12.

Quantity Sold - This is the number of units sold based on stocking UOM. It is updated through the COP Sales Journal Posting.

Usage -This is the usage quantity.  Usage is not the same as quantity sold.  If you are a manufacturer, the components used in production are updated to the usage, but not quantity sold. On the other hand, if you are a distributor, then it is likely the usage is the same as quantity sold.  If you use kits, depending on Global Setup, it is likely the component of a kit parent will not update to quantity sold, but will update to the usage fields. Usage information is updated through COP Sales Journal, IM Inventory Transaction Processing and BOMP Production posting, 

Sales Amount - Similar to Quantity Sold, this is the sales amount updated through the COP Sales Journal Posting.

Cost Amount - Similar to Quantity Sold, this is the cost amount updated through the COP Sales Journal Posting. It can be average, last or standard cost depending on the cost type you use in I/M setup.

Demand Information - If you have multiple warehouse locations, then it may be desirable for you to track the sales originated from each warehouse location. For example, say you have a warehouse in Los Angeles with location code = “LA.”  You also have a warehouse location in New York with the location code = “NY.” You will designate your customer file field “23. Location” according to the distance to your warehouse locations. When your LA location customer makes a purchase of a certain item, if you don’ t have it on stock in LA but you do have it on stock in NY, you will ship it from NY even though the customer is closer to LA. From the location history point of view, this is a sale from NY. But, as a purchasing manager, you want to know that the sale originated from LA, which is what we called the “Demand” location in this case. This information is important for purchasing managers so they can properly forecast the purchase quantity required for each location.

The way the system determines the demand location is by comparing Customer file field “23. Location,” or Ship-To file field “17. Location” if used, with the order location. The order location is the shipped-from location. The customer or ship-to location is the demand location. If they are the same, or if customer/ship-to location is blank, then the demand and sales locations are the same.

You can further control whether the demand location should be used for all locations in Global Setup -> Dist -> I/M Global Control, Screen 3, 

        “11. Track Demand For Sales Of Certain Locs Only?”

The default value is “N.” If you set this flag to “Y,” then a window will appear and prompt you to enter the demand locations that you wish to keep track of. If the demand location is not one designated in the setup, the system will make the demand location the same as the sales location. This can happen when using certain virtual locations that are not considered as the true warehouse locations. Then you may not want to make those virtual locations track their own demand, which would complicate the purchase manager's planning.

We track demand information for its quantity sold (IMLOCHST_DMD_QTY1 – 12), sales amount (IMLOCHST_DMD_SALE1- 12) and cost amount (IMLOCHST_DMD_COST1- 12). If you only have one location where you sell inventory, then the Qty Sold, Sales and Cost Amount should have the same value as the demand information.

Sales Desk Time/Quantity Quoted  - If you use Sales Desk (COP -> Processing -> Sales Desk), then Elliott can update certain Sales Desk related information in the IMLOCHST table. This, for example, can be very helpful in tracking a customer’s inquiry regarding an out-of-stock item. If you see a lot of quotes for a certain item, but not as many sales, it could be because your price is too high. It could also be because you are out-of-stock. Therefore, analyzing Sales Desk inquiry and quote information can be as important as analyzing your sales information. Pay attention to the following flag in Global Setup -> Cop-Ctl -> Sales Desk, 2nd screen:

        22. Update Timing for Loc History Qty Out of Stock?

The possible values are “1=After Item No Entered,” “2=After Qty Entered,” or “3=Save Line.”  We suggest that you use value “1,” which means when there’s an inquiry about an item, immediately after you enter the item number in Sales Desk, and if the item is out-of-stock at the default location, the system will update the field IMLOCHST_SDOUTSTK1 – 12 immediately. This is valuable because salesmen may not want to enter an amount in the quantity field in Sales Desk knowing the item is out-of-stock.

As for the IMLOCHST_SDTIMEQU1 – 12 or IMLOCHST_SDQTYQU1 – 12 columns, you must enter the line item in Sales Desk and indicate that it is a “Q” (quote) line item in order for it to be updated.

Sales Desk Number/Quantity WishList/Stock Watch - In order to update these fields in the IMLOCHST table, you need to go to Global Setup -> System -> Events Handling:

        7. Enable Stock Watch & Wish List in Sales Desk?

Set this flag to “Y” and the system will prompt you for a series of questions to set up a stock want and wish list. Stock watch is an automated event created from Sales Desk or Web Services. Its purpose is to notify salesman and/or the customer through email when you receive inventory for the particular item.

You can also establish a wish list feature through this setup.  A wish list is designed to work on top of the stock watch. That is to say, a wish list is also a stock watch. But a stock watch is not necessarily a wish list. The purpose of the wish list is to make it easier for the salesman to manage the customer’s wish list. It also provides visibility to the purchasing manager on which items are out of stock and in demand. If the stock watch or wish list feature is turned on in Global Setup, then Sales Desk can update the corresponding column in the IMLOCHST table.

Days Out of Stock - Days out-of-stock in the IMLOCHST table for each month can have a value from 0 – 31. A value of zero means this item is not out-of-stock for the month. A value of 30 or 31 means the item is entirely out-of-stock for the month. The purchase manager can correlate this value with the quantity sold for the month to make more sense of it. Elliott cannot automatically change the value in Days Out of Stock each night for those items that are out-of-stock. Instead, to update this value, you need to run a calculation utility each night, ideally immediately after midnight through Defer Processing. You can find this utility in Global Setup-> Utilities -> IM Utilities -> Generate Statistic Info -> Generate IMLOCHST Days Out of Stock.

Qty Received - Quantity Received is updated through PO legacy receiving or warehouse receiving posting. It can be helpful for the purchase manager to correlate the quantity sold with the receiving information.

In addition to ODBC, ADO.NET or Crystal Report, you can access Location History information from the following areas in Elliott:

I/M -> Inquiry -> Stock Status Inquiry, Drill down to History. You may view IMLOCHST Sales, Demand and Sales Desk related information by using the F7 key to cycle through this information:

I/M -> Reports -> Item Location History Summary. In the parameter screen of this report, the first field  prompts you to “Print What Information.”  See sample screen below:

The following is an explanation for the two-digit codes:

• Q=Quantity Sold
  
• U=Usage
  
• S=Sales Amount
  
• C=Cost Amount
  
• M=Margin Amount (calculated as “Sales – Cost”)
  
• DQ=Demand Quantity
  
• DS=Demand Sales
  
• DC=Demand Cost
  
• DM=Demand Margin (calculated as “Demand Sales – Demand Cost”)
  
• SO=Sales Desk Times Out of Stock
  
• ST=Sales Desk Times Quoted
  
• SQ=Sales Desk Quantity Quoted
  
• WN=Number of Wish List
  
• WQ=Wish List Quantity
  
• SW=Number of Stock Watch
  
• OS= Days Out of Stock
  
• QR=Quantity Received

I/M -> Reports -> Reordering Advice Report -> User Defined CSV Export/Maintenance

The I/M Reordering Advice User Defined CSV Export is a function that we consider to be "Inventory Report Writer."  You can easily select the following information and export it to a CSV format:

This information is stored in various Elliott tables. But you will find it difficult to retrieve it through ODBC, ADO.NET or Crystal Reports. Hence, we are providing this "Inventory Report Writer" function to make it easier. The following is a sample screen:

Export Processor can also export Inventory Location History if you choose the "Inventory Location" category.  Compared to "Inventory Report Writer," the Export Processor is a generic report writer that can retrieve information from many tables in Elliott. But it does not do as well as "Inventory Reporter Writer" when it comes to the inventory-related information. For example, Export Processor can't export the ATP (Available to Promise) information together in the Inventory location category.  The following is a sample screen:

The quantity, sales, cost, usage, and demand information can be recalculated if needed. This can be useful during conversion from another ERP software to Elliott. To do so, you can go to I/M -> Util-Setup -> Generate Location History.

The quantity received information can be regenerated by going to Global Setup-> Utilities -> IM Utilities -> Generate Statistic Info -> Generate IMLOCHST Qty Received.

Due to the special nature of the information, the Days Out of Stock must be re-calculated every night. To do so, you should set up Defer Processing to run after midnight for the following function: Global Setup-> Utilities -> IM Utilities -> Generate Statistic Info -> Generate IMLOCHST Days Out of Stock.