How to calculate how many of a top level part can be built based on current inventory

Kinetic ERP
,
1

I am trying to build a BAQ that calculates how many of each top level part (finished good) can be built based on a multilevel BOM and current inventory.
Attached is a BAQ that uses recursion to explode the BOM and calculate how much of each material is needed for the top level part. The excel file is sample data showing the current BAQ output and how it needs to collapse each level into the level above because assemblies could be stocked or created.
Where I am stuck is rolling up the on hand quantity of the current level plus the quantity that can be made based on the lower level’s on hand quantity all the way to the parent part, taking the minimum on each level. I tried using window functions to get the minimum at each level but it seems to fail a few levels deep. The deepest BOM is about 6 levels deep. This report needs to calculate the “can be built” quantity for about a thousand top level parts at a time.
Is this solvable using just a BAQ? If not, what is the best method to do this calculation?

with [SubQuery1] as
(select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
(0) as [Calculated_Hierarchy],
(cast ( substring(’ + + + + + + + + +‘,1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum
as nvarchar(25))) as [Calculated_Ind1],
(cast(Part.PartNum +’- ‘+ PartMtl.MtlPartNum as nvarchar(1000))) as [Calculated_SortOrder],
[Part].[PartNum] as [Part_PartNum],
(convert(int, PartMtl.QtyPer)) as [Calculated_ExtQtyPer],
(cast(Part.PartNum +’|‘+ PartMtl.MtlPartNum as nvarchar(1000))) as [Calculated_SplitByDelim]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on
PartMtl.Company = Part.Company
and PartMtl.PartNum = Part.PartNum
inner join Erp.PartRev as PartRev on
PartMtl.Company = PartRev.Company
and PartMtl.PartNum = PartRev.PartNum
and PartMtl.RevisionNum = PartRev.RevisionNum
and PartMtl.AltMethod = PartRev.AltMethod
and ( PartRev.Approved = 1 )
union all
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
(SubQuery1.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring(’+ + + + + + + + + + + ',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
as nvarchar(25))) as [Calculated_Ind2],
(cast(SubQuery1.Calculated_SortOrder + '- ‘+ right(‘0000’ + cast(PartMtl1.MtlSeq as nvarchar(4)),4)as nvarchar(1000))) as [Calculated_SortOrder2],
[SubQuery1].[Part_PartNum] as [Part_PartNum],
(convert(int, SubQuery1.PartMtl_QtyPer* PartMtl1.QtyPer)) as [Calculated_ExtQtyPer],
(cast(SubQuery1.Calculated_SplitByDelim + ‘|’+ PartMtl1.MtlPartNum as nvarchar(1000))) as [Calculated_SplitByDelim2]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
SubQuery1.PartMtl_Company = PartMtl1.Company
and SubQuery1.PartMtl_MtlPartNum = PartMtl1.PartNum
inner join Erp.PartRev as PartRev1 on
PartMtl1.Company = PartRev1.Company
and PartMtl1.PartNum = PartRev1.PartNum
and PartMtl1.RevisionNum = PartRev1.RevisionNum
and PartMtl1.AltMethod = PartRev1.AltMethod
and ( PartRev1.Approved = 1 ))
select
[SubQuery11].[Part_PartNum] as [Part_PartNum],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
(replicate(’ ‘, SubQuery11.Calculated_Hierarchy)+’+ '+ SubQuery11.PartMtl_MtlPartNum) as [Calculated_IndentedPartNum],
[SubQuery11].[Calculated_SortOrder] as [Calculated_SortOrder],
[Part1].[ClassID] as [Part1_ClassID],
[Part1].[NonStock] as [Part1_NonStock],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[Calculated_ExtQtyPer] as [Calculated_ExtQtyPer],
[OHQ].[Calculated_OHQty] as [Calculated_OHQty],
(case when SubQuery11.Calculated_ExtQtyPer>0 then OHQ.Calculated_OHQty/ SubQuery11.Calculated_ExtQtyPer else 0 end) as [Calculated_OHQtyDivExtQtyPer],
[SubQuery11].[Calculated_SplitByDelim] as [Calculated_SplitByDelim]
from SubQuery1 as SubQuery11
left outer join Erp.Part as Part1 on
SubQuery11.PartMtl_MtlPartNum = Part1.PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum(case when Part2.NonStock=1 then null else
PartWhse.OnHandQty end)) as [Calculated_OHQty]
from Erp.PartWhse as PartWhse
inner join Erp.Part as Part2 on
PartWhse.Company = Part2.Company
and PartWhse.PartNum = Part2.PartNum
group by [PartWhse].[PartNum]) as OHQ on
SubQuery11.PartMtl_MtlPartNum = OHQ.PartWhse_PartNum
order by SubQuery11.Calculated_SortOrder

BOMExample.baq (191.0 KB)
BOM Example.xlsx (10.8 KB)

2

I would tell you to review @timshuwy’s bom_explode baq which does this in reverse as in can I build X, maybe it can help you.

3

There is a standard Kinetic report that does this. It’s called Part Availability.

4

Hi Mark, it is available since which version? i can’t find it in my 2022.2.4

5

Vantage 8.

It’s under Production Management | Engineering Management. Search the word Availability.

1 Like
6

The availability report requires a part number and desired quantity to run it. It also does not roll up the quantity that can be assembled. The items that are not in stock but can still be manufactured are displayed as exceptions.
I want to be able to run it for an entire product group at once and have it show how many could be made.
The goal is for external sales reps who do not have epicor access to be able to see how many units we can promise next day delivery on.

7

Running a time phased demand across multiple parts and the same time is what MRP does. The Availability report is not nearly complete as it does not look at demand for other top level parts.

You may want to also look at Available To Promise for the sales team.