I have been tasked with creating a BAQ which will return all parts for which there is a demand and that part contains a memo of a specific category.
The memo part is easy, but how does one query whether there is demand for a part? There exists a DemandHed table but it doesn’t have any part numbers in it. I am not sure what I’m actually looking for. Do I have to pull all scheduled jobs and get the part numbers from there or is there an actual table somewhere that stores demand?
The PartWhse table will have your demand.
There it is. Thank you! I knew this would be an easy one for someone who knew.
You can also get it from PartQty
PartDtl contains detailed demand (and supply) information. It’s the source table for Time Phase.
I don’t see a Demand field in PartDtl. Does it go by some other name?
It will be the Quantity field, but only for the SourceFile you want.
SourceFile D: Indicates the record type that created this record. JH-JobHead, JA-JobAsmbl, JM-JobMtl, JO-JobOper, PO-PoRels, OR-OrderRels, FC-ForCast
This is the safest option and usually the most accurate. PartWhse and PartQty tend to be hit or miss on accuracy…
I would (as @Chad_Smith suggested) start by looking at the PartDtl table… if you dont need the actual detail, you can summarize the table, but then when you DO want to know the detail it will all be there, with every demand, demand type, etc.
So just to clarify, there is a demand when:
PartDtl.Qty > 0
Correct?
And PartDtl.SourceFile just tells me where the demand is coming from?
In the PartDtl table, when the RequirementfFlag = 1 then it’s demand. Otherwise, it’s supply.
I think the flag you are looking for is RequirementFlag.
PO will have Qty but that is a supply.
So if I do a BAQ for
PartDtl.Qty > 0 AND PartDtl.RequirementFlag = 1
then I get 2641 results.
But if I do a similar BAQ for
PartWhse.DemandQty > 0
then I only get 687 results.
Should there really be that much discrepancy? If so, why? Am I just missing some filter criteria?
Yes, PartWhse is a summary, but getting back to your need you just need to know if is has any demand, so I would make a subquery grouped by Company and PartNum for a partdtl with a RequirementFlag = true and do an inner join to that so it filters the main query.
Well, maybe, but you can’t necessarily compare row counts. As mentioned earlier, PartWhse isn’t always correct. You can run the utility to synch it up if you want to see if they match then (Refresh Part Quantities and Allocations).
On the other hand, PartDtl is Time Phased (row per date/part combination), so you’ll want to summarize the results most likely. You’ll need to do a bit of Group By to get the totals you want.
You shouldn’t need the PartDtl.Qty > 0 because if there is demand, it’s already more than 0 but I also don’t think it’s hurting anything.
Edit: Group By Company, Plant, Part for example
