Hi all, I need to create a baq where I can identify all the manufactured parts in a BOM and sub assemblies so I can extract the comments of each. Any ideas on how can I pretty much have the indented BOM in a BAQ? Thanks
There is an example of how to do a recursive CTE query in Epicor’s documentation. Search that up and follow the example to create your own.
Thank you very much John, the example is very good. However it doesn’t drill down on my assemblies so I tested it in Epicor Training database and it works. I wonder why it wouldn’t recognize my lower levels
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],
[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]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.MtlPartNum = SubQuery1.PartMtl_PartNum
and PartMtl1.Company = SubQuery1.PartMtl_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
Hi Ivonne,
Did you ever get this to work? I am looking to build the same (Indented BOM Dashboard).