My company’s warehouses are all located far away.(overseas) So even if the same RM is in another warehouse, it cannot be used in another warehouse. And production is also carried out in each warehouse. The same FG is sometimes produced in different warehouses.
So I would like to create a report from BAQ showing the shortage of Job Materials for each warehouse.
Required Qty of material for open job in each warehouse - On-hand of that warehouse = Shortage or excess quantity
Has anyone ever created a BAQ report similar to this one?
Should I create an OnHand for each warehouse using InnerSubQuery?
How do I connect this to TopLevel?
How I would be most grateful if you could share similar query sources, but any help is greatly appreciated.
Thanks in advance!
This isn’t a full answer to your question but it should get you started.
In your case, I suppose I would start with the
JobAsmbl table (which gives you JobNum and top level assy part information).
Link JobMtl…
Then PartBin
Probably still want to make onhand qty a calculated sum field like in the below post. But leave the Warehouse column in there so you can see sum of each material part per warehouse.
This would, I think get you all the data you needed. But how you expand on it and present it is up to you so I’m not sure how else to help at this point.
For example, do you want to see each material broken out? or just a true/false that a warehouse has all required materials?
You mentioned you wanted to see shortages… so you could create a calc field of ReqQty - SumOnHand (your calculated field) = ShortageQty
Again… not a full answer but should get your started. Get the BAQ built so that you’re getting all the data you want. Then you can move on to creating a BAQ report, if that’s the end game. A lot of grouping/formatting can be done on the SSRS side.
Here’s hoping that someday the Production Planner process/workbench function properly. If it worked properly/consistently it would have these details for you quickly and easily. I do freely admit to not having tested it in 2024 before this whine.
I think David has you on the right path for getting your own BAQ going though. Shortage as a true/false should be fairly straightforward. Calculating a running Qty Available might take a little king fu. I haven’t tried that before. Might be a fun rainy day project.
That’s a good point. The user needs to take in mind that this report would be a snap-shot in time… and only evaluates each material, one at a time, against each job, one at a time.
A Material Part could appear multiple times within the same BOM! Assembly 1 may use material part XYZ… Assembly 4 may ALSO use material XYZ. This is not going to COMBINE the full demand of a particular part this way. To do that, you could sum ReqQty and group by job in a subquery…
Similarly, this only looks at each job independently. So MtlPart XYZ is used on job 12345… may also be needed for job 98765! Again, no harm in using the data this way, but the user needs to understand that its not evaluating full company demand… just against one job at a time… and even one material at a time. So again, potentially another subquery to sum ReqQty across all open jobs.
Doable… and agreed… could be a nice rainy day exercise!
You almost need to re-create Time Phase but instead of using a single part, you feed it a list of parts. The list of parts can be generated from your list of job materials grouped by part number/job number with total qty calculated as suggested. Then you would create a running qty available calculated field that is partitioned by part number/warehouse and order the running qty available by date/requirement. Then, you could take that subquery and put it into a new one and group by job, then look for any instances where running qty < 0 and display that as a true/false or qty and if desired, list out the parts using a string_agg or similar aggregate statement. I have something close to this that I would be willing to share and anyone who is willing could modify it or use it as reference for building one based on warehouse qty (mine only looks at site level).
I don’t know where you are but it’s pretty rainy here… lol
So I ran into a problem when updating the BAQ to use WarehouseCode instead of Plant. Time Phase (aka PartDtl) does not have a WarehouseCode field. That is how I was calculating the supply/demand to determine if we were going to have enough. Any ideas how else I might be able to determine this? Less conveniently we might be able to create our own “faux” PartDtl CTE by combining orders, jobs, transfer orders, purchase orders, etc. That sounds like a rainier day project though haha.
Thanks, I know. So I’m going to create two BAQs. BAQ showing the sum of ReqQty of jobs in the warehouse and BAQ showing only ReqQty of individual jobs.
And I’m going to bring these two BAQs into the dashboard, and create 2 tabs.
Thanks for your great advice, I will give this a try.
