Parent part, Sub Parts/Assemblies, material parts

Epicor ERP 10
,
1

Hey everyone,

So I have been tasked with getting a part tree created in Epicor BAQ from Parent Part, all the way to the bones.
I am struggling to find a relationship that I can use to keep the data accurate. I’ve tried using jobhead.jobnum, but with no luck, and I have tried using part.classid FG and go down from there, but no matter what it seems I can only get 2 rows of part numbers. I am looking for 3. raw material into sub component, and sub into final part number. The only link I can find in Epicor 10 Is “Where Used”. I have no clue how this is pulling its data, but if anyone knows of a table of some identifier to link these numbers together, that would be a huge help.

Thank you very much.

2

in E10 in the Help for BAQ’s there is a CTE example of an Epicor Multi-Level BAQ BOM (assuming this is what you want)

3

I followed this example, and its very hard to follow what im actually doing, but I got it done and It seems I can only look up one part number at a time. My bosses would like this for every part number. any ideas?

4

You should be able to remove any filters and then it should work for any part number… I’d have to see what you have to give you specific help.

5

No Filters, just an Action. I removed the action and now it doesn’t have any parts to search. ill mess with it and see if I can get anything.

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],
	[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
	[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
	[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
	[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
	(0) as [Calculated_Hierarchy],
	(cast ( substring('........',1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum
 as nvarchar(25))) as [Calculated_Ind1]
from Erp.PartMtl as PartMtl
 where PartMtl.PartNum = @PartNum

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],
	[SubQuery1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
	[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
	[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
	[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
 as nvarchar(25))) as [Calculated_Ind2]
from Erp.PartMtl as PartMtl1
inner join  SubQuery1  as SubQuery1 on 
	SubQuery1.PartMtl_MtlPartNum = PartMtl1.PartNum
And
	SubQuery1.PartMtl_Company = PartMtl1.Company)

select 
	[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
	[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
	[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
	[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
	[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
	[SubQuery11].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
	[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1]
from  SubQuery1  as SubQuery11
6

Here is what I use directly in SQL.

WITH PartsExplosion (PartNum, MtlPartNum, lv, QtyPer, rowid, Name, SORT)
AS (
-- Anchor This is the parent part selection
  SELECT Erp.Part.PartNum, 
         Erp.Part.PartNum, 
         0,
         cast(Erp.Part.PurchasingFactor as int) as QtyPer,
         Erp.Part.SysRowID,
         CAST(Erp.Part.PartDescription as nvarchar(100)) as Name, 
         CAST('\' + Erp.Part.PartDescription as nvarchar(254)) as Sort
FROM Erp.Part
WHere ClassID in ('FGD', 'FGA')
-- Recursive Call This is the BOM for each parent
UNION ALL
SELECT BOM.PartNum, BOM.MtlPartNum, lv + 1, cast(BOM.QtyPer as int) as QtyPer, BOM.SysRowID,
       CAST(REPLICATE ('|    ' , lv + 1 ) + BOM.PartDescription as nvarchar(100)),
       CAST(cte.Sort + '\' +  BOM.PartDescription as nvarchar(254)) 
FROM PartsExplosion CTE
JOIN (SELECT Erp.PartMtl.PartNum , Erp.PartMtl.MtlPartNum, cast(Erp.PartMtl.QtyPer as int) as QtyPer, Erp.PartMtl.SysRowID, b.PartDescription
FROM Erp.PartMtl Join Erp.Part AS b on Erp.PartMtl.MtlPartNum = b.PartNum
inner join dbo.PartRev on Erp.PartMtl.RevisionNum = dbo.PartRev.RevisionNum and Erp.PartMtl.PartNum = dbo.PartRev.PartNum and Erp.PartMtl.Company = dbo.PartRev.Company 
WHERE dbo.PartRev.Approved = 1
) AS BOM
ON CTE.MtlPartNum = BOM.PartNum
)
select Name,pe.QtyPer, PartNum,PE.MtlPartNum,  lv--,sort
FROM PartsExplosion AS PE
ORDER BY sort
7

This is only showing one part, remove this line.
where PartMtl.PartNum = @PartNum

8

Works perfectly now. Thank you all very much!

9

Is there any way to reverse this which would allow for an end item, where used?