Need BAQ Part Explosion

Kinetic ERP
1

I’m trying to create a BAQ that will show every top-level part and any parts/assemblies within any subassemblies within the top-level part. I think I’ve seen it referred to as a BOM explosion in an indented tree list. I will add a filter for a specific price list.

Does anyone have any ideas on how to go about this?

Someone created a BAQ that kind of works but it doesn’t give a complete indented tree. See below.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[Part].[PartNum] as [Part_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartPlant].[MfgLotSize] as [PartPlant_MfgLotSize],
	[PartPlant].[MinMfgLotSize] as [PartPlant_MinMfgLotSize],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[PartRev1].[RevisionNum] as [PartRev1_RevisionNum],
	[PartPlant1].[MfgLotSize] as [PartPlant1_MfgLotSize],
	[PartPlant1].[MinMfgLotSize] as [PartPlant1_MinMfgLotSize],
	[PartMtl2].[MtlPartNum] as [PartMtl2_MtlPartNum],
	[PartMtl2].[QtyPer] as [PartMtl2_QtyPer],
	[PartRev2].[RevisionNum] as [PartRev2_RevisionNum],
	[PartPlant2].[MfgLotSize] as [PartPlant2_MfgLotSize],
	[PartPlant2].[MinMfgLotSize] as [PartPlant2_MinMfgLotSize],
	[PartMtl3].[MtlPartNum] as [PartMtl3_MtlPartNum],
	[PartMtl3].[QtyPer] as [PartMtl3_QtyPer],
	[PartRev3].[RevisionNum] as [PartRev3_RevisionNum],
	[PartPlant3].[MfgLotSize] as [PartPlant3_MfgLotSize],
	[PartPlant3].[MinMfgLotSize] as [PartPlant3_MinMfgLotSize],
	[PartMtl4].[MtlPartNum] as [PartMtl4_MtlPartNum],
	[PartMtl4].[QtyPer] as [PartMtl4_QtyPer],
	[PartRev4].[RevisionNum] as [PartRev4_RevisionNum],
	[PartPlant4].[MfgLotSize] as [PartPlant4_MfgLotSize],
	[PartPlant4].[MinMfgLotSize] as [PartPlant4_MinMfgLotSize],
	[PartMtl5].[MtlPartNum] as [PartMtl5_MtlPartNum],
	[PartMtl5].[QtyPer] as [PartMtl5_QtyPer],
	[PartRev5].[RevisionNum] as [PartRev5_RevisionNum],
	[PartPlant5].[MfgLotSize] as [PartPlant5_MfgLotSize],
	[PartPlant5].[MinMfgLotSize] as [PartPlant5_MinMfgLotSize]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
	and Part.PartNum = PartRev.PartNum
	and ( PartRev.Approved = 1  )

inner join Erp.PartMtl as PartMtl on 
	PartRev.Company = PartMtl.Company
	and PartRev.PartNum = PartMtl.PartNum
	and PartRev.RevisionNum = PartMtl.RevisionNum
	and PartRev.AltMethod = PartMtl.AltMethod
	and ( PartMtl.FixedQty = 0  )

left outer join Erp.PartRev as PartRev1 on 
	PartMtl.Company = PartRev1.Company
	and PartMtl.MtlPartNum = PartRev1.PartNum
	and ( PartRev1.Approved = 1  )

left outer join Erp.PartPlant as PartPlant1 on 
	PartRev1.Company = PartPlant1.Company
	and PartRev1.PartNum = PartPlant1.PartNum
left outer join Erp.PartMtl as PartMtl2 on 
	PartRev1.Company = PartMtl2.Company
	and PartRev1.PartNum = PartMtl2.PartNum
	and PartRev1.RevisionNum = PartMtl2.RevisionNum
	and PartRev1.AltMethod = PartMtl2.AltMethod
	and ( PartMtl2.FixedQty = 0  )

left outer join Erp.PartRev as PartRev2 on 
	PartMtl2.Company = PartRev2.Company
	and PartMtl2.MtlPartNum = PartRev2.PartNum
	and ( PartRev2.Approved = 1  )

left outer join Erp.PartPlant as PartPlant2 on 
	PartRev2.Company = PartPlant2.Company
	and PartRev2.PartNum = PartPlant2.PartNum
left outer join Erp.PartMtl as PartMtl3 on 
	PartRev2.Company = PartMtl3.Company
	and PartRev2.PartNum = PartMtl3.PartNum
	and PartRev2.RevisionNum = PartMtl3.RevisionNum
	and PartRev2.AltMethod = PartMtl3.AltMethod
	and ( PartMtl3.FixedQty = 0  )

left outer join Erp.PartRev as PartRev3 on 
	PartMtl3.Company = PartRev3.Company
	and PartMtl3.MtlPartNum = PartRev3.PartNum
	and ( PartRev3.Approved = 1  )

left outer join Erp.PartPlant as PartPlant3 on 
	PartRev3.Company = PartPlant3.Company
	and PartRev3.PartNum = PartPlant3.PartNum
left outer join Erp.PartMtl as PartMtl4 on 
	PartRev3.Company = PartMtl4.Company
	and PartRev3.PartNum = PartMtl4.PartNum
	and PartRev3.RevisionNum = PartMtl4.RevisionNum
	and PartRev3.AltMethod = PartMtl4.AltMethod
	and ( PartMtl4.FixedQty = 0  )

left outer join Erp.PartRev as PartRev4 on 
	PartMtl4.Company = PartRev4.Company
	and PartMtl4.MtlPartNum = PartRev4.PartNum
	and ( PartRev4.Approved = 1  )

left outer join Erp.PartPlant as PartPlant4 on 
	PartRev4.Company = PartPlant4.Company
	and PartRev4.PartNum = PartPlant4.PartNum
left outer join Erp.PartMtl as PartMtl5 on 
	PartRev4.Company = PartMtl5.Company
	and PartRev4.PartNum = PartMtl5.PartNum
	and PartRev4.RevisionNum = PartMtl5.RevisionNum
	and PartRev4.AltMethod = PartMtl5.AltMethod
	and ( PartMtl5.FixedQty = 0  )

left outer join Erp.PartRev as PartRev5 on 
	PartMtl5.Company = PartRev5.Company
	and PartMtl5.MtlPartNum = PartRev5.PartNum
	and ( PartRev5.Approved = 1  )

left outer join Erp.PartPlant as PartPlant5 on 
	PartRev5.Company = PartPlant5.Company
	and PartRev5.PartNum = PartPlant5.PartNum
inner join Erp.PartPlant as PartPlant on 
	PartRev.Company = PartPlant.Company
	and PartRev.PartNum = PartPlant.PartNum
where (Part.Company = 'XXXX'  and Part.PartNum in ('x-xxx-xxx-xx'))

Any help is greatly appreciated!

2

Look for Indented BOM in here. there are a few posts that have examples. If i recall someone posted a BAQ at one time.

2 Likes
3

CTEFTW
CTEFTW1024×1024 134 KB

~If you want to recursively traverse the BOM you may need a CTE query?~ - Kinetic ERP - Epicor User Help Forum

4

Thanks Craig. There are a couple posts that might get close. Playing with one now.

5

Thanks Nate. Someone shared a BAQ with a CTE on another thread that I’m playing with. Gets close to a solution.

6

If your on-prem and can use external BAQ’s, then the following SQL may help.

We created a view using the SQL and then reference that in an external BAQ.

I haven’t tried it in the SQL-BAQ in our 2024.2 environment, but might be worth a go.

WITH PartList AS
(SELECT pm.company, pm.partnum AS toplevelpartnum, pm.revisionnum AS toplevelrevisionnum, pm.partnum, pm.revisionnum, prt.effectivedate, pm.mtlpartnum, pm.qtyper, 1 As BomLevel, pt.typecode
FROM Kinetic.Erp.partmtl as PM
INNER JOIN Kinetic.Erp.part as PT
ON PM.mtlpartnum = PT.partnum
AND pt.company = ‘???’
INNER JOIN Kinetic.Erp.PartRev PRT
ON PM.partnum = PRT.partnum
AND PM.RevisionNum = PRT.RevisionNum
AND PRT.Approved = 1
UNION ALL
SELECT nplus1.Company, pl.toplevelpartnum, pl.toplevelrevisionnum, nplus1.partnum, nplus1.revisionnum, pr.effectivedate, nplus1.mtlpartnum, nplus1.qtyper, PL.BomLevel + 1, pt.typecode
FROM Kinetic.Erp.partmtl as nplus1
INNER JOIN Kinetic.Erp.part as PT
ON nplus1.mtlpartnum = pt.partnum
and pt.company = ‘???’
INNER JOIN Kinetic.Erp.partrev PR
ON nplus1.partnum = pr.partnum
AND nplus1.revisionnum = pr.revisionnum
AND pr.approved = 1
INNER JOIN PartList AS PL
ON nplus1.partnum = PL.mtlpartnum

)
SELECT * From PartList pl2 where
pl2.effectivedate = (select max(r2.effectivedate) from kinetic.erp.partrev r2
where
pl2.partnum = r2.partnum
and r2.Approved = 1
and r2.EffectiveDate <= GETDATE()
)

1 Like
7

They have that access restricted to nearly everyone except the systems team.

I got something close after creating a BAQ using your code, but not quite there.

8

Doug.C’s BAQ in the link below is a working solution.

BAQ Union/Union All - Help Needed - Epicor ERP 10 - Epicor User Help Forum