I am trying to get the quantity and value of a part on a specific date. I’ve been trying to do this with PartTran and rebuilding to the day selected, however, if I do it for today, I end up with a value that does not match what is in PartBin.
Is there a good way to do this? Is there something built in that I am missing? What would be the best way to go about this?
The Stock Status report has an As of Date. See if that report gets you what you need.
I don’t imagine those tables it is using for that report are available anywhere? I see StkActivity and StkPart as data sources for that report but can’t find them anywhere. The report has most of the information we need, but my boss is wanting something more like a dashboard to make it easier to read but not seeing how I can get there.
If you use PartTran and calculate a running qty… can you get the quantity correct on a given day?
Calc Field for Running Qty:
SUM(PartTran.TranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)
No. That’s what I was doing and it doesn’t match what’s in PartBin if I run it up through today. I also tried using the starting and ending qty fields on PartBin, but those don’t even match each other (Starting Qty does not match Ending Qty of previous transaction)
Example: Top part is publishing PartNum and LotNum
Bottom is PartTran only filtering on PartNum and LotNum
partBin tells me I have over 800.
Adding up through PartTran I get 0 if I adjust based on Tran Type
No adjustment adds up to 71.5
There aren’t even enough to make it to 200, let alone 800.
I have lots of these, just picked this one as it has few transactions and easier to see.
Are these purchased or Manufactured parts?. I don’t see any PUR-STK or MFG-STK transactions in your summary to increase you inventory counts.
What module are you looking at there? Is it possible the dataview isn’t showing all transactions?
Hi Todd,
have you seen this solution?
Both, that just happened to be a purchased part with that one. Same behavior on manufactured.
Running a query on PartTran wide open with no limitations (yes it takes a bit but need the info to troubleshoot)
Downloaded and ran it. Looked good on the first one but the second one is not.
If I’m reading this right, should have 29 in inventory.
Part Advisor: 13
Have you run the Refresh PartBin QOH from PartTran process in Report Only mode to verify your PartBin records match your PartTran records? We have some parts that will pop up on this once in a while because an MES station’s session is in the wrong plant or (seemingly) random bugs that cause the PartBin to not update. It happens infrequently enough that we haven’t tracked it all down, but if you haven’t ever run this, it can add up.
We also noticed when changing a part from non-quantity bearing to quantity bearing, or vice-versa, this process will pick up all those PartTrans and suggest a change to the PartBin to bring it to what it “should” be if it were always quantity bearing.
Yes, PartBin can be a bit, uhmmm, “unreliable” 
I’m not all that familiar with Part Advisor - does that view include non-nettable bins? If not, maybe that’s your difference?
I would follow Tyler’s suggestion as I have found that over time that things do get out of sync in Epicor and the reported quantities such as what you displayed in Part Advisor aren’t always correct. I have always used the transactions listed in the PartTran table as be correct. The part you show would not seem to have a large number of transactions, what do you see when you manual calculate your current balance?
Honestly, wasn’t even aware of this process. Considering I haven’t ran it ever, that means it hasn’t been run in years if ever… Guessing this is a huge part of my problem. Will update after.
Especially with PCIDs - and needing datafixes to fix. 
Well if you are certain what’s wrong, you can fix it yourself, but when I say certain, I mean it.
I might be dumb but I’m not st00pid.
When we first ran the process 2 years ago (after first implementing Epicor in 2010…), we had a significant list of parts that were off. We had to sit down with Finance and figure out the impact to our inventory value and make sure it all made sense. We ended up running the process right before a physical inventory and rolling the resulting adjustments into that process.
If your inventory is accurate, you will probably need to run the process in update mode and then do quantity adjustments to put everything back to where it was - those adjustments are probably offsetting adjustments that have been made over time.
We have the report running daily now and we check it about once a week. If a part comes up it’s easy to run the process for that one part and have the materials team do a quick cycle count to verify the quantity on hand.
