I have written a dashboard that lists all parts with an assortment of details on each part. One of the requests is “Total Demand Qty”, “Qty On Hand” and “Qty On Order”. I have found the first two, but where do I find the “Qty On Order”? Is there a database field somewhere or is it calculated from purchase orders? If it is calculated from purchase orders, how do I find them. If I limit my search to open purchase orders, what about lines that have been received. When does an item move to quantity on hand, as soon as it is received or after it is received and the PO closed?
QOH is incremented upon receipt (actually marking the pack slip “received”. Marking it just “arrived”, won’t show as QOH).
If the received complete setting on a PO receipt entry is what drives the PO’s release and line to be marked closed.
Take a look at the PartDtl table. It’s basically timephase without the onhand qty. You will need a group by and some calculated fields, but all the demand and supply will be there.
as @Carson mentioned, if it is not on PartDtl, then create a subquery on PO and sum all Open PO lines/releases not yet received and link it to your top level query, also this information and much more already exist in the Purchase Advisor view screen
1 Like
Thanks for the input, I am finally able to get back to work on this.
I was able to get the info I needed in a separate SQL query, but when I add it to my BAQ, I get garbage numbers. |i used an inner join, but should be doing something different?
Working Query
SELECT [podetail].[partnum] AS [PODetail_PartNum],
( Sum(porel.relqty - porel.receivedqty) ) AS [Calculated_OrderQty]
FROM erp.poheader AS POHeader
LEFT OUTER JOIN erp.podetail AS PODetail
ON poheader.company = podetail.company
AND poheader.ponum = podetail.ponum
AND ( podetail.openline = 1
AND podetail.voidline = 0 )
INNER JOIN erp.porel AS PORel
ON podetail.company = porel.company
AND podetail.ponum = porel.ponum
AND podetail.poline = porel.poline
AND ( porel.openrelease = 1
AND porel.voidrelease = 0 )
WHERE ( poheader.openorder = 1
AND poheader.voidorder = 0 )
GROUP BY podetail.partnum
BAQ Query with above query added as Subquery5
SELECT [part].[typecode] AS [Part_TypeCode],
(( CASE
WHEN part.typecode = 'M' THEN 'Manufactured'
ELSE ( CASE
WHEN part.typecode = 'P' THEN 'Purchased'
ELSE ''
END )
END )) AS [Calculated_PartType],
[part].[inactive] AS [Part_InActive],
(( CASE
WHEN part.inactive = 1 THEN 'Inactive'
ELSE 'Active'
END )) AS [Calculated_PartStatus],
[part].[partnum] AS [Part_PartNum],
[part].[partdescription] AS [Part_PartDescription],
[part].[searchword] AS [Part_SearchWord],
[puragent].[name] AS [PurAgent_Name],
[part].[nonstock] AS [Part_NonStock],
[part].[qtybearing] AS [Part_QtyBearing],
[part].[costmethod] AS [Part_CostMethod],
( CASE
WHEN part.costmethod = 'A' THEN 'Average'
WHEN part.costmethod = 'L' THEN 'Last'
WHEN part.costmethod = 'S' THEN 'Standard'
WHEN part.costmethod = 'T' THEN 'Avg by Lot'
ELSE ''
END ) AS [Calculated_CostMethod],
[partplant].[backflush] AS [PartPlant_BackFlush],
[partplant].[generatesugg] AS [PartPlant_GenerateSugg],
[part].[uomclassid] AS [Part_UOMClassID],
[part].[ium] AS [Part_IUM],
[part].[salesum] AS [Part_SalesUM],
[part].[pum] AS [Part_PUM],
[part].[prodcode] AS [Part_ProdCode],
[prodgrup].[description] AS [ProdGrup_Description],
[part].[classid] AS [Part_ClassID],
[partclass].[description] AS [PartClass_Description],
[part].[mtlanalysiscode] AS [Part_MtlAnalysisCode],
[part].[netweight] AS [Part_NetWeight],
[part].[netweightuom] AS [Part_NetWeightUOM],
(( CASE
WHEN part.netweight = 0 THEN ''
ELSE part.netweightuom
END )) AS [Calculated_NetWeightUOM],
[part].[partlength] AS [Part_PartLength],
[part].[partwidth] AS [Part_PartWidth],
[part].[partlengthwidthheightum] AS
[Part_PartLengthWidthHeightUM],
[part].[thickness] AS [Part_Thickness],
[part].[thicknessum] AS [Part_ThicknessUM],
[partplant].[reorderlevel] AS [PartPlant_ReOrderLevel],
[partplant].[minimumqty] AS [PartPlant_MinimumQty],
[partplant].[maximumqty] AS [PartPlant_MaximumQty],
[partplant].[minorderqty] AS [PartPlant_MinOrderQty],
[partplant].[leadtime] AS [PartPlant_LeadTime],
[partplant].[mfglotmultiple] AS [PartPlant_MfgLotMultiple],
[partplant].[daysofsupply] AS [PartPlant_DaysOfSupply],
( partcost.stdlaborcost
+ partcost.stdburdencost
+ partcost.stdmaterialcost
+ partcost.stdsubcontcost
+ partcost.stdmtlburcost ) AS [Calculated_StdTotalCost],
( partcost.avglaborcost
+ partcost.avgburdencost
+ partcost.avgmaterialcost
+ partcost.avgsubcontcost
+ partcost.avgmtlburcost ) AS [Calculated_AvgTotalCost],
( partcost.lastlaborcost
+ partcost.lastburdencost
+ partcost.lastmaterialcost
+ partcost.lastsubcontcost
+ partcost.lastmtlburcost ) AS [Calculated_LastTotalCost],
[SubQuery2].[calculated_lastpurchasedate] AS
[Calculated_LastPurchaseDate],
(( CASE
WHEN ( prodgrup.description ) IS NULL THEN ''
ELSE prodgrup.description
END )) AS
[Calculated_ProdGrup_Description],
[SubQuery3].[parttran1_ponum] AS [PartTran1_PONum],
[partwhse].[demandqty] AS [PartWhse_DemandQty],
[partwhse].[onhandqty] AS [PartWhse_OnHandQty],
[SubQuery5].[calculated_orderqty] AS [Calculated_OrderQty]
FROM erp.part AS Part
LEFT OUTER JOIN erp.partclass AS PartClass
ON part.company = partclass.company
AND part.classid = partclass.classid
LEFT OUTER JOIN erp.partplant AS PartPlant
ON part.company = partplant.company
AND part.partnum = partplant.partnum
LEFT OUTER JOIN erp.puragent AS PurAgent
ON partplant.company = puragent.company
AND partplant.buyerid = puragent.buyerid
LEFT OUTER JOIN erp.prodgrup AS ProdGrup
ON part.company = prodgrup.company
AND part.prodcode = prodgrup.prodcode
INNER JOIN erp.partcost AS PartCost
ON part.company = partcost.company
AND part.partnum = partcost.partnum
LEFT OUTER JOIN (SELECT [Part1].[partnum] AS [Part1_PartNum],
( Max(parttran.trandate) ) AS
[Calculated_LastPurchaseDate]
FROM erp.part AS Part1
INNER JOIN erp.parttran AS PartTran
ON Part1.company = parttran.company
AND Part1.partnum = parttran.partnum
AND ( parttran.tranclass = 'R'
AND parttran.trantype =
'PUR-STK' )
GROUP BY Part1.partnum) AS SubQuery2
ON part.partnum = SubQuery2.part1_partnum
LEFT OUTER JOIN (SELECT [PartTran1].[partnum] AS
[PartTran1_PartNum],
[PartTran1].[tranclass] AS
[PartTran1_TranClass],
[PartTran1].[trantype] AS
[PartTran1_TranType],
[PartTran1].[trandate] AS
[PartTran1_TranDate],
[PartTran1].[mtlunitcost] AS
[PartTran1_MtlUnitCost],
[PartTran1].[lbrunitcost] AS
[PartTran1_LbrUnitCost],
[PartTran1].[burunitcost] AS
[PartTran1_BurUnitCost],
[PartTran1].[subunitcost] AS
[PartTran1_SubUnitCost],
[PartTran1].[mtlburunitcost] AS
[PartTran1_MtlBurUnitCost],
[PartTran1].[extcost] AS
[PartTran1_ExtCost],
[PartTran1].[vendornum] AS
[PartTran1_VendorNum],
[PartTran1].[jobnum] AS
[PartTran1_JobNum],
[PartTran1].[assemblyseq] AS
[PartTran1_AssemblySeq],
[PartTran1].[jobseqtype] AS
[PartTran1_JobSeqType],
[PartTran1].[jobseq] AS
[PartTran1_JobSeq],
[PartTran1].[ponum] AS [PartTran1_PONum]
,
[PartTran1].[poline] AS [PartTran1_POLine],
[PartTran1].[porelnum] AS [PartTran1_PORelNum],
[PartTran1].[ordernum] AS [PartTran1_OrderNum],
[PartTran1].[orderline] AS [PartTran1_OrderLine],
[PartTran1].[orderrelnum] AS [PartTran1_OrderRelNum]
FROM erp.parttran AS PartTran1
WHERE ( PartTran1.tranclass = 'R'
AND PartTran1.trantype = 'PUR-STK'
AND PartTran1.extcost >= 0 )) AS SubQuery3
ON SubQuery2.part1_partnum = SubQuery3.parttran1_partnum
AND SubQuery2.calculated_lastpurchasedate =
SubQuery3.parttran1_trandate
LEFT OUTER JOIN erp.partwhse AS PartWhse
ON part.company = partwhse.company
AND part.partnum = partwhse.partnum
INNER JOIN (SELECT [podetail].[partnum] AS
[PODetail_PartNum],
( Sum(porel.relqty - porel.receivedqty) ) AS
[Calculated_OrderQty]
FROM erp.poheader AS POHeader
LEFT OUTER JOIN erp.podetail AS PODetail
ON poheader.company = podetail.company
AND poheader.ponum = podetail.ponum
AND ( podetail.openline = 1
AND podetail.voidline = 0 )
INNER JOIN erp.porel AS PORel
ON podetail.company = porel.company
AND podetail.ponum = porel.ponum
AND podetail.poline = porel.poline
AND ( porel.openrelease = 1
AND porel.voidrelease = 0 )
WHERE ( poheader.openorder = 1
AND poheader.voidorder = 0 )
GROUP BY podetail.partnum) AS SubQuery5
ON part.partnum = SubQuery5.podetail_partnumit easier if you screen shot your BAQ, and highlight the unwanted records or the errors instead of pasting the generated SQL statement
Disregard last comments, I have this working. I was just being stupid and running SQL quuery against LIVE and BAQ query against TEST. Numbers matched once I looked at same database for both.