I’m working on a BAQ to see what parts are in stock, where (bin location), and their corresponding costs. FIFO Costing.
The problem is, we could have parts in multiple bins. We could also have multiple FIFO layers. This creates the below scenario when I attempt a simple join of PartBin to PartFIFOCost
The part I’m experimenting with appears in (2) bins (M-204 & M-205).
Because these parts were received to inventory at two different times, there is (2) FIFO layers. This returns incorrect results.
Because I have (2) active FIFO layers, I’m getting two rows per Bin, one row is correct, one is not.
PartBin basically is providing a total qty on hand, in a given bin. But I can’t figure out how to correlate the parts that are IN that bin to their respective FIFO Layer.
Anybody ever play with this? I couldn’t find any previous posts on the matter, but I’ll keep looking.
FIFO doesn’t care about bins. It doesn’t care about Lots or Serial Numbers. If you remove one from inventory, it will use the oldest FIFO bucket. You can transfer inventory inside the site all day long and the costs don’t matter. Do one transaction where it leaves the cost ID and the oldest cost gets used.
I always average the FIFO buckets and then use that for all locations within the Site ID. It won’t match when doing outgoing transaction, but should tie out to the stock status report.
PartFIFOTran to PartTran by TranNum. PartTran will give you the bin. You’ll just have to do all the grouping and subqueries to make it line up.
You’ll probably want to throw PartFIFOCost where the OnHandQty>0 (or something like it) in there so you’re not looking at inactive layers.
Or, if you want to be lazy and “close enough” then PartCost table holds the average fifo cost and you can simply multiply by BinQty.
Yes, I tend to agree with what you’re saying, but…
… this is the part I need to explore.
I can use PartFIFOTran and/or PartFIFOCost to get the initial inventory transaction and easily link that to PartTran, which holds the original Bin (sounds oddly religous)… but that information does not carry forward through STK-STK transactions.
The problem is, PartTran doesn’t account for (carry forward) FIFO details in STK-STK transaction where we simply move the parts to a different bin. Doing this creates two STK-STK transactions… a negative on the original bin, a positive on the new bin. These rows of PartTran do not include FIFO information… so there’s no direct way to track the FIFO details through those movements.
Once a STK-CUS, or a STK-MTL transaction occurs, the FIFO details show up again (the system pings the FIFO tables and chooses the oldest bucket to pull from).
But STK-STK is where I lose things. I’m trying to figure out how to advance the bin, but hold on to my cost details of what’s in that bin.
I could show, in the dashboard I’m working on, the results from above… the users would get used to seeing it and walking through the STK-STK movements. The problem is I’m only trying to show what is currently IN the bin. If I try to filter (using PartFIFOCost’s InActive column) I lose all STK-STK transactions from the results because again, PartFIFOCost is not involved in those transactions.
Its messy. I’m now thinking the easiest approach (for my final dashboard) is to simply break this into two different BAQ grids. One that shows the Bin quantities and another that shows all active FIFO costs. Trying to link them in one query may not be possible (or at least not worth the effort).
I was going to suggest two queries, either a pub/sub or just two separate grids, but I figured you were trying to get it all in one query for a reason.
For a STK-STK trans PartTran will have the cost, could you filter those out by quantity>0?
Yea I agree it gets messy pretty quick.
Well, the initial goal was one query, but, not afraid to pivot. This is actually already an active dashboard using one query joining PartBin and PartFIFOCost. For the majority of parts viewed on this dashboard, the existing query works perfectly fine. But, there has always been this ugly/confusing issue of duplicate lines (if multiple FIFO layers exist) which I have on “ok” grasp on… but trying to explain it, or decipher the query results for other users is a PITA!
So… this whole effort is me trying to find a more appealing solution.
For example, this is my current grid on my dashboard:
Same part, in two different bins, with two different fifo layers, results in (4) rows… none of which provide the actual total quantity on hand (which I previously “fixed” by pushing that value to a numberbox in the grid header).
So, the initial effort was to get this grid to only return (2) rows… one for each bin, with only its corresponding cost.
BUT, these oddball cases within multiple bins/layers get muddy. The initial query (not my query by the way) was only joining PartBin to PartFIFOCost by PartNum, so all FIFO layers get applied to all bins.
Definitely going to head down this path and see if I can shake out something that’s more “presentable” to the end user. Filtering by Qty > 0 may or may not work as it wouldn’t show the negative transaction on the old bin. So, I don’t know if that would look more confusing or not. But I may try it. I could highlight the bin column on the dashboard so the user would see the qty stays constant and only the bin number changed.
