What Information Is Stored in the Location History (IMLOCHST) Table?

Release Date: 11/27/2017

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 Index of IMLOCHST and Best Practice for Performance


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.

How Are Records Created in IMLOCHST?
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. 

Type of History Information Stored in IMLOCHST

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.

Access Location History Data from Elliott

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:
  • Information in Item Master or Inventory Location
  • IMLOCHST (I/M Loatioon History) Information
  • ATP (Available to Promise) Information
  • Item Attribute Information
  • Certain receiving information of the item
  • Linkage
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 -> Inventory Location -> InvLoc History
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:


Recalculation of IMLOCHST

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.


EMK

Inventory Management

  1. Long-Term vs. Short-Term Inventory Quantity Available and Inventory with Time Line
  2. Introduction to Available to Promise (ATP)
  3. Inventory Aging Report by Location Sequence Produces Different Result Than by Other Sequence
  4. Inventory Aging Report Shows Different Age Between Single and All Locations
  5. Multiple Users Entering Physical Count Tag at the Same Time Issue
  6. Expand Item Description 1 and 2 Character Limit
  7. How Different is an IM Kit from a BOMP Kit?
  8. Explanation on Component Availability Inquiry
  9. How Is Shortcut Item Different from Kit Item?
  10. No Privilege to View Location History
  11. How to Calculate EOQ (Economic Order Quantity)
  12. Item Quantity Allocation Is Incorrect
  13. Reorder Advice Custom CSV Export Shows Zero Items
  14. Elliott Physical Count Processing
  15. Feature - Add-On Item Drill Down From Item Search
  16. Feature - Utility to Reset Item PTD Fields from Location History
  17. Feature - Inventory Snapshot
  18. Feature - ATP Regen to Consider Order Multiple and Economic Order Qty
  19. Feature - Item Master Activity Sub-Code
  20. Feature - Add Support for the Update of “Mostly Mfg Flag” in Change Existing Item Import
  21. Feature - Auto Delete BOMP Product Structure When Item Obsolete
  22. Feature - Item Linkage Report/Processing
  23. Feature - Added the Ability to Organize Product Categories by a New Product Category Group
  24. Procedure to Correct Bad Serial Number with Special Symbol
  25. Feature - Additional SY12MONS Updates
  26. Feature - Additional Activity Codes for Item Linkage CSV Report and Item Linkage Processing
  27. Feature - Item Number Wild Card Support for Starting Item Number
  28. Feature - Obsolete Kit Parent When First Component is Obsolete
  29. Feature - Show Items with Qty Variance on Tag Variance Reports
  30. IM01S3 Inventory Management Iten Audit Trail Report
  31. IM04S2 Inventory Management Inventory Transaction Audit Report
  32. IM02S2 Inventory Management Inventory Location Audit Trail Report
  33. IM03S1 Inventory Management Usage Exception Reports
  34. IM06SCN Inventory Management Stock Status Report
  35. IM08S1 Inventory Management Print Cycle Count Worksheet
  36. IM14S1 Inventory Management Item History Report
  37. IM19S1 Inventory Management Serial/Lot Stock Status Report
  38. IM19S2 Inventory Management Serial/Lot Issue History Report
  39. IM20S1 Inventory Management I/M Distribution to G/L Report
  40. IM26SCN Inventory Management Frozen Stock Status Report
  41. IM30S1 Inventory Management Kit Where-Used Report
  42. IM31S1 Create Unreleased POs by Vendor
  43. IM32S1 Inventory Management Kit Gross Requirements Report
  44. IM0201 Inventory Management Location Control File Maintenance
  45. IM0405 Inventory Management Available To Promise Inquiry
  46. IM0500 Inventory Management Stock Status Inquiry
  47. IM0500Q Inventory Management Stock Status Inquiry
  48. IM0700 Inventory Management ABC Analysis Reports
  49. IM0900 Inventory Management Reordering Advice Reports
  50. IM1000 Inventory Management Physical Count Processing
  51. Feature - Utility to Reset Item PTD/YTD Fields from Location History
  52. IM1100 Inventory Management Utilities Setup
  53. IM1201 Inventory Management Location File Maintenance
  54. IM1300 Inventory Management Recalculate Reorder Fields
  55. IM1400 Inventory Management Clear Item Accumulators
  56. IM1501 Inventory Management Product Category File Maintenance
  57. IM1601 Inventory Management Material Cost Type File Maintenance
  58. IM1701 Inventory Management Material Cost Type/Loc File Maintenance
  59. IM1801 Inventory Management Buyer/Analyst Code File Maintenance
  60. IM1901 Inventory Management Initialize Lifo/Fifo File
  61. IM1902 Inventory Management Adjust Item File to Lifo/Fifo
  62. IM1903 Inventory Management Serial/Lot Processing/Multi-bin Utilities
  63. IM1904 Inventory Management Serial/Lot Stock Status Inquiry
  64. Feature - Add Additional Record Types to Reorder Advise User-Defined CSV Export
  65. IM2500 Inventory Management Reset Allocated Quantities
  66. IM2700 Inventory Management Set Trx Audit File Beginning Balances
  67. IM2800 Inventory Management Freeze Inventory
  68. IM2900 Inventory Management Kit File Maintenance
  69. IM3301 Inventory Management Job Code File Maintenance
  70. IM3400 Inventory Management Job Analysis Report
  71. IMACTMNT Inventory Management I/M Account File Maintenance
  72. IMAGESCN Inventory Management Inventory Aging Report
  73. IMATPGSN Inventory Management Generate ATP File
  74. IMATPRSN Inventory Management Available to Promise Report
  75. IMBININV Inventory Management Bin Inventory File Maintenance
  76. IMBINMNT Inventory Management Bin File Maintenance
  77. IMBINTRN Inventory Management Inventory Transfer Processing
  78. IMCHGBIN Inventory Management Change Bin No/Pick Seq
  79. IMEXPTSN Inventory Management Export Item for Take an Order
  80. IMFRMMNT Inventory Management Item Label Form Setup
  81. IMGENMNU Inventory Management Generate Location History
  82. IMIMSITM Inventory Management Item Import Utility
  83. IMITMUDS Inventory Management Item User-Defined Fields Import Utility
  84. IMSUBMNT Inventory Management Substitute Item Class File Maintenance
  85. IMUSRMNT Inventory Management User-Defined Code File Maintenance
  86. IMVESMNT Inventory Management Vessel File Maintenance
  87. IMVLSMNT Inventory Management I/M Serial/Lot History File Maintenance
  88. Feature - Expanded Selection Parameters for Reorder Advise User Defined CSV Export
  89. Change - Update Both Kit Parent and Components Usage
  90. Feature - Create Price Code 1 from Item Minimum Price Utility
  91. Change - Available to Promise by Item Report - Add Item Description 2
  92. Feature - Support AR (Attribute) Type in Reorder Advise User Defined CSV Export
  93. Can System Default Item User-Defined Code?
  94. Feature - Item Label Printing Enhancements
  95. Feature - Ability to Initialize Character Fields During Change Existing Item Import
  96. Understanding Status Code at Inventory Transfer Dispatch Screen
  97. Feature - Support GTIN Based on Two Different UPC Codes
  98. Feature - Update Estimated Date/Time When Printing Transfer Tickets
  99. Feature - Allow Physical Count Tag Import Without Serial Number
  100. Feature - Delete Kit Components When Item Activity Code is Set to 'O'
  101. Feature - New QTYAVAILBYCOMP Attribute Fields
  102. Feature - Allow GTIN Maintenance in I/M Change Bin No/Pick Seq Processing
  103. Feature - Improvements to Reorder Advise Reports
  104. What Is the Difference Between INV_TRX_UN_COST and INV_TRX_NEW_AVG_COST in IMINVTRX Table?
  105. Feature - Added a Function "Create Inventory Trx CSV Import"
  106. Feature - Added Support to “Create I/M Trx CSV Import” for Kit Items
  107. Feature - Improvements to QTYAVAILBYCOMP and QTYAVAILBYCOMP2 Attributes
  108. Feature - Add Desc2 Column to Item Linkage CSV Report
  109. Feature - Numeric Item List Changes
  110. Feature - Added 1C and LD Column Types to Reordering Advise User Defined CSV Export
  111. What's the Difference Between Lead Time & Planning Lead Time?
  112. Explanation of ATP LP Type - Negative Sign (-) vs (*) Symbols
  113. IM0000 Inventory Management Package Overview 7
  114. IM0000 Inventory Management Package Overview 1
  115. IM0000 Inventory Management Package Overview 2
  116. IM0000 Inventory Management Package Overview 3
  117. IM0000 Inventory Management Package Overview 4
  118. IM0000 Inventory Management Package Overview 6
  119. IM0000 Inventory Management Package Overview 5
  120. IM0000 Inventory Management Package Overview 8
  121. IM0000 Inventory Management Package Overview: Index
  122. IM0101 Inventory Management Item File Maintenance 1
  123. IM0101 Inventory Management Item File Maintenance 2
  124. IM0101 Inventory Management Item File Maintenance 3
  125. IM0101 Inventory Management Item File Maintenance 4
  126. IM0101 Inventory Management Item File Maintenance 5
  127. IM0101 Inventory Management Item File Maintenance 6
  128. IM0101 Inventory Management Item File Maintenance 7
  129. IM0101 Inventory Management Item File Maintenance 8
  130. IM0101 Inventory Management Item File Maintenance: Index
  131. IM2400 Inventory Management Inventory Transaction Processing 1
  132. IM2400 Inventory Management Inventory Transaction Processing 2
  133. IM2400 Inventory Management Inventory Transaction Processing 3
  134. IM2400 Inventory Management Inventory Transaction Processing 4
  135. IM2400 Inventory Management Inventory Transaction Processing 5
  136. IM2400 Inventory Management Inventory Transaction Processing 6
  137. IM2400 Inventory Management Inventory Transaction Processing 7
  138. IM2400 Inventory Management Inventory Transaction Processing: Index
  139. How to Run Inventory Stock Status Report for a Certain Date
  140. Feature - Transition to Obsolete
  141. Item Last Received Date Not Updated When Received from I/M Transaction Processing
  142. Feature - Zero Item Weight When Using F3 to Copy Item
  143. Feature - Initialize User-Defined Note, Date and Amount Fields When Using F3 to Copy Item
  144. What Information Is Stored in the Location History (IMLOCHST) Table?
  145. Feature - Prevent Excel from Dropping Leading Zeroes in CSV Item Number Field
  146. Feature - Add User ID to User Defined CSV Export
  147. Feature - User Defined CSV Export Item Batch Support
  148. Feature - Export Inventory TRX Audit Trail to CSV
  149. Feature - Add User-Defined Code Table Support in IM User-Defined CSV Export
  150. Feature - Added “Item 1st Received Date Range” Criteria to Item User Defined CSV Export
  151. How to Get a List of Items That Does Not Have GTIN Code Defined
  152. Feature - User Defined CSV Export Quantity Sold by Customer#, Customer Type or Customer Group
  153. Feature - Added inKit (IK) and inBOMP (IB) Column Types to the User-Defined CSV Report
  154. Feature - Added Phantom Locking to Item File Maintenance
  155. Feature - Added CSV Support to the Physical Count Posting Update Edit List and the Variance Report By Item/Warehouse

Feedback and Knowledge Base