Has anyone found success creating a BAQ that mimics the Stock Status report while using FIFO/Lot FIFO costing methods? I need some insight into the logic behind the unit/ext costs used on the report so that I can replicate them. Thanks!
I’ve not looked deep into it but did briefly look into what appears to be happening in order to produce the Stock Status report. It seems like it’s PartBin combined with PartTran tables and then some logic to group/order the transactions in such a way that entering a cutoff date on Stock Status will exclude any transactions after that date.
Now, throw in the FIFO and lot tracking… This is the part I’m the fuzziest about. I don’t know what cost Stock Status would show (the next cost or the average cost if you sold all of the pieces at once?). We don’t do FIFO costing so I’m not real sure.
I’m hopeful someone else chimes in and can help provide more insight. I will open up a BAQ and attempt to recreate. I’m guessing it starts with PartTran, excluding non-inventory transactions, and do a running total on the TranQty field. Then you’d only care about the last record for each Company/Part/Warehouse/Bin combination. Not exactly sure how that’s expressed but I’ll play around with it.
Thanks for weighing in! I can get a pretty close end total value if I use the FIFO Average from PartCost, but it doesn’t tie out to the bin/lot, just the overall part value in inventory. A couple odd-ball instances were using just the straight average cost on the Stock Status (even though their cost method was FIFO or Lot FIFO) which has me wondering about the PartTran and costing method history on those parts.
I’ll dig into the PartTran records and see if I can make heads or tails of it. Any other insight you find is appreciated!
I would imagine changing the cost method at some point would create havoc for something like this. Not sure if that’s what you meant, but I don’t know if you can get that info from PartTran.
That may be as good as you get.
We are FIFO/LOT FIFO and I did this a couple of years ago and got to within $1500 on 4.5M, but even knowing the parts that were wrong, fixing them made others wrong with a larger delta, so I left it as it was.
Thanks, Greg. Did you end up calculating your values with the FIFO Average?
For Average and FIFO I used the PartCost.Avg Material, Labor, Burden, SubContract fields
For Standard I used the PartCost.Std Material, Labor, Burden, SubContract fields
For LotFIFO I used a sub query summing PartFIFOCost Material, Labor, Burden, SubContract fields to calculate an average cost per unit.
Thanks!