BAQ indented BoM returning 100 repeating levels

Epicor ERP 10
, ,
1

I have attempted to follow the Epicor ICE Tools User Guide 101600 to create an indented level BoM. They have an example I followed step by step to create this report. It seems that I was partially successful, but it looks to show the single level BoM over and over again. When testing the BAQ, it says there are 100 BoM levels, but it is just the single level BoM on each level. It gives the error when testing the BAQ “Severity: Error, Table: , Field: , RowID: , Text: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

I don’t enter any SQL manually and I’m not sure how I would do that if I wanted to. This is all built using the query builder. This is definitely not pulling all of the parts, as the same parts from the single level are just being duplicated. I know for a fact that some of these parts that are showing in the BoM are assemblies and their components are not listed. I need to fix this report to accurately report all levels of the indented BoM.

Here is what the my report looks like:

with [SubQuery1] as
(select
[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],
[PartMtl].[Company] as [PartMtl_Company]
from Erp.PartMtl as PartMtl
where PartMtl.PartNum = @PartNum

union all
select
[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],
[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],
[PartMtl1].[Company] as [PartMtl1_Company]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.PartNum = SubQuery1.PartMtl_PartNum
and PartMtl1.Company = SubQuery1.PartMtl_Company)

select
[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_PullAsAsm] as [PartMtl_PullAsAsm],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[PartMtl_Company] as [PartMtl_Company]
from SubQuery1 as SubQuery11
where (SubQuery11.PartMtl_RevisionNum = ‘B’)

2

I built one a while back… based off the answerbook for an indented menu from Epicor.
Just screwing around one afternoon so I haven’t spend a lot of time verifying.
But… it did seem to be working with my structures at least.
Note that keeping track of material revisions was the trickiest part… for me.

With [MaterialCTE] AS
(select
[BOM].[PartMtl_PartNum] as [PartMtl_PartNum],
[BOM].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[BOM].[Part_PartDescription] as [Part_PartDescription],
[BOM].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Parts].[Calculated_Rev] as [Calculated_Rev],
[BOM].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[BOM].[Calculated_Level] as [Calculated_Level],
[BOM].[Calculated_Path] as [Calculated_Path],
[BOM].[Calculated_LSeq] as [Calculated_LSeq],
[BOM].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[BOM].[Calculated_TLQty] as [Calculated_TLQty]
from (select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
(1) as [Calculated_Level],
(cast(‘’ as nvarchar(255))) as [Calculated_Path],
(cast( convert(varchar,PartMtl.PartNum) + '' + convert(varchar,(RIGHT(‘00000’ + CAST(PartMtl.MtlSeq AS NVARCHAR(5)), 5 ))) as nvarchar(255))) as [Calculated_LSeq],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
(cast(1 as decimal(10,1))) as [Calculated_TLQty]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on
PartMtl.Company = Part.Company
And
PartMtl.PartNum = Part.PartNum

where (PartMtl.PartNum = @BOM and PartMtl.RevisionNum = @Revision)) as BOM
inner join (select
[Part2].[Company] as [Part2_Company],
[Part2].[PartNum] as [Part2_PartNum],
(case when MtlRevision1.PartRev_RevisionNum > ‘’ then MtlRevision1.PartRev_RevisionNum else ‘N/A’ end) as [Calculated_Rev]
from Erp.Part as Part2
left outer join (select
[PartRev].[PartNum] as [PartRev_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
(max(PartRev.EffectiveDate)) as [Calculated_LastEffDate]
from Erp.PartRev as PartRev
inner join (select
[PartRevLAST].[PartNum] as [PartRevLAST_PartNum],
(max(PartRevLAST.EffectiveDate)) as [Calculated_MaxEffDate]
from Erp.PartRev as PartRevLAST
where (PartRevLAST.Approved = True and PartRevLAST.EffectiveDate <= GETDATE())
group by [PartRevLAST].[PartNum]) as LastEffDate on
PartRev.PartNum = LastEffDate.PartRevLAST_PartNum
And
PartRev.EffectiveDate = LastEffDate.Calculated_MaxEffDate

group by [PartRev].[PartNum],
[PartRev].[RevisionNum]) as MtlRevision1 on
Part2.PartNum = MtlRevision1.PartRev_PartNum) as Parts on
BOM.PartMtl_MtlPartNum = Parts.Part2_PartNum

UNION ALL
select
[ChildPartMtl].[PartNum] as [ChildPartMtl_PartNum],
[ChildPartMtl].[RevisionNum] as [ChildPartMtl_RevisionNum],
[MaterialCTE1].[Part_PartDescription] as [Part_PartDescription],
[ChildPartMtl].[MtlPartNum] as [ChildPartMtl_MtlPartNum],
[MaterialCTE1].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[ChildPartMtl].[MtlSeq] as [ChildPartMtl_MtlSeq],
(MaterialCTE1.Calculated_Level + 1) as [Calculated_Level2],
(Cast(MaterialCTE1.Calculated_Path + ‘.’ as nvarchar(255))) as [Calculated_Path2],
(cast(MaterialCTE1.Calculated_LSeq + '' + convert(varchar,ChildPartMtl.PartNum) + '' + (RIGHT(‘00000’ + CAST(ChildPartMtl.MtlSeq AS NVARCHAR(5)), 5 )) as nvarchar(255))) as [Calculated_LevSeq2],
[MaterialCTE1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(cast(ChildPartMtl.QtyPer as decimal(10,1))) as [Calculated_MLQty]
from Erp.PartMtl as ChildPartMtl
inner join MaterialCTE as MaterialCTE1 on
ChildPartMtl.PartNum = MaterialCTE1.PartMtl_MtlPartNum
And
ChildPartMtl.RevisionNum = MaterialCTE1.Calculated_Rev

where (ChildPartMtl.PartNum is not null))

select
[MaterialCTE].[PartMtl_PartNum] as [PartMtl_PartNum],
[MaterialCTE].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[MaterialCTE].[Part_PartDescription] as [Part_PartDescription],
[MaterialCTE].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[PartMain].[PartDescription] as [PartMain_PartDescription],
[MaterialCTE].[Calculated_Rev] as [Calculated_Rev],
[MaterialCTE].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[MaterialCTE].[Calculated_Level] as [Calculated_Level],
[MaterialCTE].[Calculated_Path] as [Calculated_Path],
[MaterialCTE].[Calculated_LSeq] as [Calculated_LSeq],
[MaterialCTE].[Calculated_TLQty] as [Calculated_TLQty],
[MaterialCTE].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(MaterialCTE.Calculated_TLQty * MaterialCTE.PartMtl_QtyPer) as [Calculated_ReqQty]
from MaterialCTE as MaterialCTE
inner join Erp.Part as PartMain on
MaterialCTE.PartMtl_MtlPartNum = PartMain.PartNum
order by MaterialCTE.Calculated_LSeq

3

Hello Bruce,

I only have one revision that I was looking for, so I set a table criteria for rev B for the part number. This seemed to work. I will see if I can duplicate your report, but it might take me a while to interpret everything and translate it to the query builder.

4

If you have access to EpicWeb or EpicCare.
You can get the Answerbook - “BAQ Example of CTE using Menu Hierarchy”

That is what I based the indented BOM on.
Keeping in mind that material revisions are different from the BOM revision.
In my case I had to get the latest, approved rev. for each material on the BOM.
(since mtl rev is not specified in the PartMtl table).

5

Thanks for that suggestion. I was able to duplicate the menu CTE query. I then attempted to translate this into an indented BoM query, but it times out before spitting out any report.

with [CTE] as
(select
[Inner].[PartMtl_Company] as [PartMtl_Company],
[Inner].[PartMtl_PartNum] as [PartMtl_PartNum],
[Inner].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[Inner].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[Inner].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Inner].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[Inner].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[Inner].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[Inner].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[Inner].[PartPlant_MinimumQty] as [PartPlant_MinimumQty],
[Inner].[PartPlant_MaximumQty] as [PartPlant_MaximumQty],
[Inner].[PartPlant_SafetyQty] as [PartPlant_SafetyQty],
[Inner].[PartPlant_LeadTime] as [PartPlant_LeadTime],
[Inner].[Calculated_IndLevel] as [Calculated_IndLevel],
[Inner].[Calculated_PartPath] as [Calculated_PartPath]
from (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],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
[PartPlant].[SafetyQty] as [PartPlant_SafetyQty],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
(1) as [Calculated_IndLevel],
(cast(’’ as nvarchar(255))) as [Calculated_PartPath]
from Erp.PartMtl as PartMtl
inner join Erp.PartPlant as PartPlant on
PartMtl.Company = PartPlant.Company
and PartMtl.MtlPartNum = PartPlant.PartNum
where PartMtl.PartNum = @PartNumber and PartMtl.RevisionNum = @Revision) as Inner
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],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartPlant1].[MinimumQty] as [PartPlant1_MinimumQty],
[PartPlant1].[MaximumQty] as [PartPlant1_MaximumQty],
[PartPlant1].[SafetyQty] as [PartPlant1_SafetyQty],
[PartPlant1].[LeadTime] as [PartPlant1_LeadTime],
(CTE1.Calculated_IndLevel + 1) as [Calculated_IndLevel2],
(cast( CTE1.Calculated_PartPath + CTE1.PartMtl_MtlPartNum + ’ > ’ as nvarchar(255) )) as [Calculated_PartPath2]
from Erp.PartMtl as PartMtl1
inner join CTE as CTE1 on
PartMtl1.Company = CTE1.PartMtl_Company
inner join Erp.PartPlant as PartPlant1 on
PartMtl1.Company = PartPlant1.Company
and PartMtl1.MtlPartNum = PartPlant1.PartNum
where (PartMtl1.MtlPartNum is not null))

select
[CTE].[PartMtl_Company] as [PartMtl_Company],
[CTE].[PartMtl_PartNum] as [PartMtl_PartNum],
[CTE].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[CTE].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[CTE].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[CTE].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[CTE].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[CTE].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[CTE].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[CTE].[PartPlant_MinimumQty] as [PartPlant_MinimumQty],
[CTE].[PartPlant_MaximumQty] as [PartPlant_MaximumQty],
[CTE].[PartPlant_SafetyQty] as [PartPlant_SafetyQty],
[CTE].[PartPlant_LeadTime] as [PartPlant_LeadTime],
[CTE].[Calculated_IndLevel] as [Calculated_IndLevel],
[CTE].[Calculated_PartPath] as [Calculated_PartPath]
from CTE as CTE
order by CTE.Calculated_IndLevel, CTE.Calculated_PartPath

6

Here is the query I use. Your looks a bit off with the select statement up top

WITH PartsExplosion (ParentPartNum, ParentSN, ChildPart, ChildSN, lv, rowid, Name, SORT, customer, TopSN, TopPart)
AS (
-- Anchor
  SELECT p.PartNum  as ParentPartNum,
         sn.SerialNumber as ParentSN, 
         p.PartNum as ChildPart ,
         sn.SerialNumber as ChildSN, 
         0 as lv,
         cast('' as nvarchar(MAX))  as rowid,
         CAST(p.PartDescription as nvarchar(100)) as Name, 
         CAST('\' + sn.SerialNumber as nvarchar(254)) as Sort,
         p.shortchar10,
        sn.SerialNumber as TopSN,
        p.PartNum as TopPart
  from part p
    inner join SerialNo sn on sn.PartNum = p.PartNum
       --and p.ClassID in ('FGA', 'FGD')
-- Recursive Call
UNION ALL
SELECT 
BOM.ParentPartNum, 
BOM.ParentSerialNo, 
BOM.ChildPartNum, 
BOM.ChildSerialNo,
lv + 1, 
cast('' as nvarchar(MAX)) As Rowid, 
CAST(REPLICATE ('|    ' , lv + 1) + BOM.PartDescription as nvarchar(100)), 
CAST(cte.Sort + '\' +  BOM.ChildSerialNo as nvarchar(254)),
cte.customer, 
CTE.TopSN,
CTE.TopPart
FROM PartsExplosion CTE
JOIN (SELECT sm.ParentPartNum, sm.ParentSerialNo, sm.ChildPartNum, sm.ChildSerialNo, p.PartDescription 
from Erp.SerialMatch sm
inner join SerialNo sn on sm.ChildPartNum = sn.PartNum and sm.ChildSerialNo = sn.SerialNumber
inner join part p on p.PartNum = sn.PartNum
where sm.ParentSerialNo <> sm.ChildSerialNo
) AS BOM
ON CTE.ChildPart = BOM.ParentPartNum and CTE.ChildSN = BOM.ParentSerialNo
)
select PE.customer, PE.TopSN, PE.TopPart, PE.ParentPartNum, PE.ParentSN, PE.ChildPart as CompPart, PE.ChildSN as ComponentSN, PE.lv,  PE.[Name] as Description,
sn.SNFirstShippedDt_c, sn.SNFirstShippedtoCusDt_c, sn.ShortChar01, oh.ordernum,oh.PONum
FROM PartsExplosion AS PE
inner join SerialNo as sn on sn.SerialNumber = pe.TopSn and sn.PartNum = PE.TopPart
inner join Erp.JobProd jp on jp.JobNum = sn.shortchar01
inner join OrderHed oh on jp.OrderNum = oh.OrderNum     
ORDER BY sort
7

Thinking about it. You need to make sure you anchor is set. I am guesing that your top portion of your query is bringing back multiple parts as parents. that will cause the 200 items in the loop your are seeing.

8

Hello Ken,

I believe that I have an anchor set according to the Epicor ICE user guide. Here is how it specifies how to anchor and then the calculated field on my screen.

Capture.PNG707×608 62.4 KB

Capture1.PNG932×612 23.8 KB

9

The anchor part of the query, which is the top part. What does you select statement bring back on its own?

Think it is:

select
 [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],
 [PartMtl].[Company] as [PartMtl_Company]
 from Erp.PartMtl as PartMtl

That is every parent/child part combination. I am guessing it will bring back duplicates because the parent part is in the record for each child part.

attach your baq. we can get you there.

10

Hello Ken,

I redid this report again and it seems to be working. Now I just need to add the fields I need and make this query update-able.

11

great to hear.

12

Hello Bruce,

Now that I have my report doing (mostly) what I want, I am trying to only pull the most recent revisions for the parents parts after the BoM Level 0. I setup a parameter for the parent part number and revisions, which works perfectly if I only had the level 0 of the BoM pulling. After that, It pulls every revision for any parent parts under the top level. I’m trying to interpret your calculated Rev fields that pull only the latest rev. Could you send a screenshot of how this is setup in your BAQ?

13

Screen shot of revisions sections If this helps… or let me know if you have someplace I can sent a copy to.

Note I had to handle materials without a revision too (since I could not use an open join).
Also… I ran into a few exceptions where users had created multiple approved revisions for a part with the same effective dates - rather than try to sort them out, I just ended up manually un-approving and/or correcting the dates.

image.png1162×1672 268 KB

14

I am having trouble duplicating this in BAQ. I cannot get the revision queries to come out correctly. I am missing something somewhere. I looked at your example screen shots and I cannot get the parts inner sub ssquery to come out right. is there any way possible to get some insight? I got the basic query to function in SSRS and it does work for our structures. When I use BAQ, my joins are coming up cross joins instead of inner. You insight would be appreciated. Thanks!

15

Hi Bruce

I have been trying to replicate this query, any chance you could export the BAQ so I could upload it and learn from how you have done it. A recursive BOM of the latest revision would be so helpful if I could get it working.

Regards

Richard

16

Here is a zip of a dashboard exported from version E10.1.600.18.

Note, that I discovered cases where the mtl rev selection is not 100% match with the standard indented BOM in E10.
This was a while back & I just haven’t gotten around to tracking down the cause yet since the discrepancies, when they did occur, were very minor.
(In my cases at least, your mileage may vary).

.E10 BOMIndentAndRevSelection.zip (86.7 KB)

1 Like
17

Hi Bruce

Thanks for that, it looks really good.

The issue you had is the same we are having and I can’t work the logic of why.

Your list stops at phantom items unfortunately we have a few of these and they are at a higher level.

It would be great if we could get this to work, I’m surprised Epicor haven’t come up with a solution as I would have thought most companies would use this BAQ in some form.

Regards

Richard

18

Post the query phrase of what you have?

I might be able to see what you are trying to do and help out.

19

OK thanks.
When/if I get a chance to go back thru this, I will be sure to verify phantoms too.
Might be a while though…

Epicor gives a few examples of indenting but… they are really simple.
And I think the majority of sites are using simpler BOMs, max of one or two levels deep.
It’s been a while but when I have looked, usually didn’t find a lot that helped with more complicated methods.

20

Hi Ken

See dashboard zip Bruce kindly uploaded. The problem is it stops if the part is a phantom.

Regards

Richard