Hi,
We use Excel SQL queries for WIP and Stock level reporting.
This works fine, but at year end we get into trouble since creating a query for a dat other than today is difficult.
I wonder is someone knows a solution.
I know Stock reports via Epicor can be created for a date in the past so I suppose there is “something” available to would make it possible to create SQL queries for a past date as well…
Kind regards
Mark
To calculate the QOH for a given date, you would take the current QOH and subtract the sum all transactions after the desired date.
This is what the Stock Status Report does.
Note that the SSR will give you a value for the part, but that is not correct for the date in question. The SSR uses the current cost to display the value. So if a cost changed between your desired date and the current date, the value would be incorrect - but he QOH would still be right.
You’ll have to use the PartTran table to get all the transactions between today and the desired date. But note that the sign of the TranQty depends on the Tran Type. Both PUR-STK and STK-MTL will typically have a positive TranQty, but the STK-MTL actually decrements the QOH. There are many posts on her that will give you the appropriate sign to apply based on the TranType
Thanks. Sorry to hear it’s quite complicated…
The Stock status report does work with the correct prices for the entered date?
NO!
(well maybe … if your costing method is STD, and there’s been no updates between the desired date and today)
And it’s not really that complicated.
QOH of the desired date is something like
SUM (PartBin.OnhandQty) + SUM (PartTran.TrayQty * (case when PartTran.TranType IN (PUR-STK, MFG-STK) THEN 1 ELSE (case when PartTran.TranType IN (STK-MTL, STK-CUS) THEN -1 ELSE 0)))
There are many more Tran Types to be added, to the IN() lists, but you get the picture.