Question

Beginning Inventory Balances Day 1 of Prior Month

  • 9 September 2022
  • 10 replies
  • 173 views

  • Known Participant
  • 59 replies

Does anyone know of a good way to determine the beginning inventory balances at the start of the prior month?  I have not found where this would be stored in the tables, if at all.

I have a request for data showing what the inventory balances were on day 1 of the prior month. (i.e. from today, it would be August 1st), to be run on any given day.  

We are using Epicor 10.2.500.13.


10 replies

Have you looked at the Stock Status Report?

Userlevel 2

I’m not aware of any tool out of the box… it seems like you could create it by making a BAQ. Start with a subquery on PartTran and filter out all transactions before the first of the previous month. Aggregate the sum of the quantity of “inventory” transactions by part number/bin location. Then you could join that subquery to the PartBin table on the Part Number and Bin location. Then you’d essentially take your current on-hand and subtract the value obtained from each Part/Bin combo in the subquery. Maybe there’s an easier way, but that’s how I’d approach it.

Userlevel 1

We schedule a task to save an Excel of the inventory daily. Then we can quickly go back to any date.

The Stock Status Report is ideally the value we need to get.  The catch is users are looking for a report that starts with that number, and then accounts for all MFG-STK and STK-CUS transactions. 

They are trying to determine , of the parts that were manufactured in a month, how much is left today, with the knowledge that anything sold comes out of older inventory first.

Userlevel 2

I agree with previous comments.

If I remember correctly, I was told that there is no table storing that info.  Every time Stock Status runs, it runs through the Part transactions.

 

Therefore, I dump a Stock Status on the first of each month to excel, I have an extraction template that pulls values and use that.  I would presume an expert SSRS would be able to start with Stock Status and create a report/extract you could use.

Good luck

cch

Userlevel 3

I run a BAQ extract on the 1st of each month to grab the inventory quantities and value.  I also have a BAQ that determines the quantity of inventory that was issued to jobs and brought in a receipts.  Using Excel provides the required data we need.

The stock status would give you the right quantity if you back date it but not the correct cost.  If quering and you need quantity only, use the part transaction history tracker running total column.  We also have a  report services report that pulls the ins and outs of inventory using part transaction history tracker and transaction type to see what the quantity and cost is each day.  I don’t know if this helps.

mwood, what are you using as your SSRS dataset to get the ins and outs of inventory using part transaction history tracker ?  Just the PartTran table?

 

I double checked with IT (I am just accounting).  We use PartTran table.

PartTran.ExtCost - This is what we use for costing per transaction type

PartTran.TranQty - This is what we use for quantity on the transaction type

You just need to watch the ins and outs

 

We have a parameter of PartTran.PostedToGL = 1….that is going to depend on how much you post.  We post our transactions Daily

I bullied my IT guy and got the report details for you.  It’s not pretty but it works

Reply