It has been requested that I add the Qty On Hand of a part for the “From Bin” on the Material Request Queue. I have created a BAQ to try and figure out the best way to go about this, but what I’m finding is that I’m able to find an On Hand Qty for only 20% or so of the rows in the material request queue. I’m attaching my BAQ in case anyone would like to look it over. Does anyone know why the results are so few?
MtlQueueOnHandBins.baq (61.0 KB)
This will only lead to heartache and pain. I believe the person processing the request is meant to use the From Location search feature in the transaction screen to determine where quantity exists when they are fulfilling the request.
Jeren, I had a similar request. I decided to add custom fields in Mtl-Queue table and made a BPM to get On-Hand Qty of From Bin whenever there is a row added/updated. Would be interested in that?
I would love to help you but I am not able to open your BAQ for obvious reason: version.
Yeah, I’d be interested in how you went about that if you are able to share! Thank you Rhythem.
You should create a custom field to show the value. Create a data-directive BPM on Mtl-Queue table using custom code widget.
This should help.
When you do this are the results any better than what I reported in my BAQ? I’m confused why so many of our parts do not have an OnHandQty available. For example I am currently showing 14 results in my BAQ but the material request queue has 76 rows.
I do think your method will work better regardless since it is always a pain point trying to add columns to grids, so thanks a lot for sharing.
Could you share Query Phrase from your BAQ, if you don’t mind? I am not able to load because of version.
It should be doing the same thing as the code you shared.
select
[MtlQueue].[PartNum] as [MtlQueue_PartNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[MtlQueue].[FromWhse] as [MtlQueue_FromWhse],
[MtlQueue].[FromBinNum] as [MtlQueue_FromBinNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[PartBin].[BinNum] as [PartBin_BinNum]
from Erp.PartBin as PartBin
inner join Erp.MtlQueue as MtlQueue on
MtlQueue.PartNum = PartBin.PartNum
and MtlQueue.FromWhse = PartBin.WarehouseCode
and MtlQueue.FromBinNum = PartBin.BinNum
This query will only return results when you actually have quantity in the from warehouse and bin.
PartBIN does not contain records for every wh/bin, only those with quantity.
For the items in the queue where you are expecting results from your query and not seeing them, do you actually have quantity of that part in the from wh/bin?
well that is what I was trying to figure out. Where would you pull onHandQty from?