Indented BOM, BAQ to SQL Help

Epicor ERP 10
,
1

Hi everyone, I have been tasked to pull all related levels for a plan which includes subassemblies and such. I followed the exercise for an indented BOM in IceTools manual and I can analyze and test fine. We will be using PowerBI for this so I need to get this running in sql mgmt studio. I plugged in my part number and I get these errors.

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘hierarchy’.
Msg 207, Level 16, State 1, Line 1
Invalid column name ‘Hierarchy2’.

Here is the query:

With [CTE] 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 = ‘K100’)
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],
(CTE.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 CTE as CTE on
PartMtl1.PartNum = CTE.PartMtl_MtlPartNum
And
PartMtl1.Company = CTE.PartMtl_Company)

select
[CTE1].[PartMtl_Company] as [PartMtl_Company],
[CTE1].[PartMtl_PartNum] as [PartMtl_PartNum],
[CTE1].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[CTE1].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[CTE1].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[CTE1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[CTE1].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[CTE1].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[CTE1].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[CTE1].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[CTE1].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[CTE1].[Calculated_Ind1] as [Calculated_Ind1],
[PartOpr].[OprSeq] as [PartOpr_OprSeq],
[PartOpr].[OpCode] as [PartOpr_OpCode],
[PartOpr].[ProdStandard] as [PartOpr_ProdStandard]
from CTE as CTE1
inner join Erp.PartOpr as PartOpr on
CTE1.PartMtl_MtlPartNum = PartOpr.PartNum

where (CTE1.PartMtl_PullAsAsm = true)

Could someone help me?

2

I think you need to refer to the calculated hierarchy field you created. So this formula would become:

(cast ( substring(’…’,1,([Calculated_Hierarchy] + 1) ) + Partmtl.MtlPartNum as nvarchar(25))) as [Calculated_Ind1]

Same with your hierarchy 2 field.

3

Beware, we did the icetools indented bom exercise also and found that if you have two of the same subassembly it will only count as one subassembly. I would love to be proven wrong.

4

Similarly I always had issues with the lack of revision control in the structure.

5

I usually try to duplicate the logic which Epicor uses for planning jobs, ‘latest approved revision’ which helps in that regard.

1 Like