CTE help

I’m trying to make a CTE query that can add up all of the labor time needed to make an assembly. I used the link below to make the recursive query to get a BOM. That works fine. Then I pull in the PartOpr table on the top level to see all of the operations and prod standards in the BOM (from which I can create some calculated fields and sum and whatever)


The problem I have is getting the initializing part number into the list to be added in. There are operations on that part number too that have to be included. You can see that 205-2-12390 is my parameter for the first part to look at, and you can see all of the materials. I need one more row per top level operation that would essentially have the parent part information blank (because there won’t be one), and put the 205-2-12390 part and it’s related operations in the column with the material parts.

Any ideas on how I can get that last bit of information into the query?

Below is the copied out SQL for you matrix types that read code like a book.

With [CTE] AS 
	[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 = @Part)
	[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
	PartMtl1.Company = CTE.PartMtl_Company)

	[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)
1 Like

Why are you not using the Manufacturing Lead Time Calculation functionality?

1 Like

?? I didn’t know it existed. After look through Epicor’s awesome help files… I still don’t know how to use it. It created a log file somewhere it looks like, but I don’t know where it’s going, and if I specify the location, it still doesn’t create it there. And there isn’t any other information on the screen. I ran it in pilot and went to the part entry screens and it didn’t change anything. Also, since we don’t have any capacities set in our resource groups, there is no way for the system to know how long all of the operations takes. The system is assuming that I have infinite workers and machines, so the lead time would be a matter of hours for a $600K conveyor.

Is there a report that it spits out somewhere that would be more helpful??

Well, it looks like it calculated something. because there is a date there, but it says 0. I’m assuming that we don’t have all of the pieces in place to make this work correctly (that’s usually how it works here, Garbage in Garbage out)