Hi there BAQ wizards, I could use some help finishing a BAQ I started.
I am trying to find out OnHandQty per Part at each Site location (Am using PrtWHSE for this) as well as the total cost for each Part (OnHandQty x UnitCost).
I also want to capture the total demand for each Part, with existing open jobs, future orders, etc. All demand for each part. While doing research, I found an article stating to use PartDtl table then do criteria RequirementFlag = 1 to filter Parts that have demand.
So far I am having troubles getting the results from my subquery to populate. PartDtl being the subquery and PartWhse being the top level.
PartWhse.Company = PartDtl.Company
PartWhse.PartNum = PartDtl.PartNum
PartWhse.KBPlant = PartDtl.Plant
I also am not sure if I should use PartCost to capture the $ for inventory on hand?
When it comes to demand, should I make this as a BAQ for each site and filter down? That way it’s not showing the total demand company wide.
My end goal is to show the parts at each site location, their OnHandQty, total $ for OnHQty, then also the total demand for that part. Basically, showing what warehouses have too much Mtl on hand and how much it’s costing us.
That join on PartWhse.KBPlant = PartDtl.Plant is likely your issue. You need to pull in Plant from the Warehse table. You can do this either by making an inner sub query with Partwhse joined to Warehse and then make sure you have Warehse.Plant selected in your fields, and then you can easily join that inner subquery to your other subquery. Another option is to just add Warehse to your top level, join on partwhse (company/warehousecode) and then in your join for partwhse to subquery1, on the left type in Warehse.Plant (or if you’ve already used Warehse elsewhere, the Warehse table you added will have an alias, e.g Warehse1, use that, and I believe the BAQ editor is case sensitive) where KBplant currently exists so you have Warehse.Plant = Partdtl.Plant.
If you use FIFO, the PartCost table will give you an average. If you want accurate cost then you’ll want to hit the PartFIFOCost table for those parts.
Tip for PartFIFOCost table is to filter (Inactive = 0) and (OnHandQty > 0). The OnHandQty from this table is only for that fifo line. Total cost for each line is ((FIFOLabor + FIFOBurden + FIFOMaterial + FIFOSubCont + FIFOMtlBur) * OnHandQty), then group by part and sum all lines.
I only have one site so you might need to play around with joining it to PartTran to break it down by warehouse. Don’t quote me on this part.
When it comes to demand, if your company has a goal for inventory turns keep that in mind, so look at the demand date too.
If OHQ = TotalDemandQty then it looks like you’re good. But if that part has a 5-day lead time and you’re stocking demand that’s 6-months out, then you’re arguably overstocked.
