Sage 300 ERP (formally known as Accpac) “Location Details” UI provides item quantity information by location. This number comes from the ICILOC table and updates in real time when you create or update a PO and SO and when you receive or ship items. In ICILOC, each item and location combination creates a record within the table. The records include columns such as:
- Quantity on Hand (QOH)
- Quantity on Sales Order (QOSO)
- Quantity on Purchase Order (QOPO)
- Quantity Committed, and
- Quantity Available to Ship
Unfortunately, there is not a report or UI in Accpac if you want to know your inventory levels at a specific date. In this blog, I share with you a solution to this problem using PowerPivot.
First, I added the PO and SO header and detail tables into the data model. Then, I built the relationship between the time dimension, the sales order date and the expected shipment date. Now, when a specific date is selected, the quantity is calculated in the filter context of the sales order date or expected shipment date. In the screenshot below, the IC QOH, IC QOSO, IC QOPO pull from ICILOC, so they don’t change with the date selection. The rest columns are calculated columns and are time sensitive and filtered by the date selection.
To get a sense of the look and feel of this report, click here. I will share this report in my next blog where I will wrap up our PowerPivot series on operation management.