BASIC QUESTION: How get cost of manufactured part?

Epicor ERP 10
,
21

Here you go. Added some notes, removed some strings specific to my company and replaced them with an explanation.

It is not the most efficient way to do this, but I didn’t feel like wrapping my head around recursion that day and it runs quickly enough. It will spit out a table that, if sorted by FullMtlSeq, will give you a neat indented layout. There isn’t anything beyond sums in the report layer.

/*
Variables
@Part: your part number
@ProdQty: Run size. Will amortize production hours, but not setup, on designated machining ops.
@Efficiency: Default is 1.0, which is current BOM. Higher number represents more efficient than current. Again, only affects production hours on machining ops.
@IncludeFixtures include the cost of reusable fixtures, which are designated with a specific part class.
@Lvl internal variable. Starts as zero. Used to track how deep in the BOM the loop is.
*/

SELECT 
	PartRev.PartNum
	,FullMtlSeq = CAST('0' as varchar(max))
	,Lvl = @Lvl
	,MtlSeq = 0
	,ParentPartNum = CAST('' as char(50))
	,ProdQty = CAST(@ProdQty as decimal(12,4))
	,QtyPer = CAST(1.000 as decimal(12,4))
	,FixedQty = 0
INTO #Temp --Runs much quicker with a temp table than CTE/subquery
FROM erp.PartRev
	INNER JOIN LastPartRev() AS LastPartRev --StoredProc that returns the most recent approved rev.
			ON PartRev.PartNum = LastPartRev.PartNum
			AND PartRev.RevisionNum = LastPartRev.RevisionNum
WHERE PartRev.PartNum = @Part 

WHILE @@ROWCOUNT > 0
BEGIN
	SET @Lvl += 1;
	
	INSERT INTO #Temp
	SELECT
		PartNum = PartMtl.MtlPartNum
		,FullMtlSeq = #Temp.FullMtlSeq + '/' + CAST(PartMtl.MtlSeq as varchar)
		,Lvl = @Lvl
		,PartMtl.MtlSeq
		,PartMtl.PartNum
		,ProdQty = CASE WHEN PartMtl.FixedQty = 1 
						THEN PartMtl.QtyPer
						ELSE PartMtl.QtyPer * #Temp.ProdQty END
		,PartMtl.QtyPer
		,PartMtl.FixedQty
	FROM #Temp
		INNER JOIN LastPartRev() AS LastPartRev
			ON #Temp.PartNum = LastPartRev.PartNum
		INNER JOIN erp.PartMtl
			ON LastPartRev.PartNum = PartMtl.PartNum
			AND LastPartRev.RevisionNum = PartMtl.RevisionNum
			AND PartMtl.AltMethod = ''
		INNER JOIN erp.Part ON PartMtl.MtlPartNum = Part.PartNum
	WHERE #Temp.Lvl = @Lvl - 1
		AND (@IncludeFixtures = 1 OR Part.ClassID <> 'FixturePartClass')
END

SELECT
	#Temp.PartNum
	,Part.PartDescription
	,Part.TypeCode
	,Part.ClassID
	,Rev = ISNULL(LastPartRev.RevisionNum,'')
	,#Temp.FullMtlSeq
	,#Temp.Lvl
	,#Temp.MtlSeq
	,#Temp.ParentPartNum
	,#Temp.ProdQty
	,#Temp.QtyPer
	,#Temp.FixedQty
	,MaterialCost = CASE Part.TypeCode WHEN 'P' THEN PartCost.AvgMaterialCost ELSE 0 END * #Temp.ProdQty
	,OprSeq = ISNULL(PartOpr.OprSeq,0)
	,OpCode = ISNULL(PartOpr.OpCode,'')
	,ResourceGrp = ISNULL(PartOpDtl.ResourceGrpID,'')
	,BurdenRate = ISNULL(ResourceGroup.ProdBurRate,0)
	,LaborRate = ISNULL(ResourceGroup.ProdLabRate,0)
	,PartOpr.EstSetHours
	,ProdBurHrs = ISNULL(
		PartOpr.EstProdHours
			 * #Temp.ProdQty
			 ,0)
	,ProdLaborHrs = ISNULL(
		PartOpr.EstProdHours 
			* #Temp.ProdQty 
			/ CASE WHEN OpMaster.AnalysisCode = 'Machining' THEN @Efficiency ELSE 1 END
			,0)
	,SetupCost = ISNULL(
		PartOpr.EstSetHours
		* (ResourceGroup.ProdBurRate 
		+ ResourceGroup.ProdLabRate)
		,0)
	,ProdCost = ISNULL(
		PartOpr.EstProdHours
			 * #Temp.ProdQty
			 * ResourceGroup.ProdBurRate
		+ PartOpr.EstProdHours 
			* #Temp.ProdQty 
			/ CASE WHEN OpMaster.AnalysisCode = 'Machining' THEN @Efficiency ELSE 1 END
			* ResourceGroup.ProdLabRate
		,0)
FROM #Temp
	LEFT JOIN LastPartRev() AS LastPartRev
			ON #Temp.PartNum = LastPartRev.PartNum
	INNER JOIN erp.Part ON #Temp.PartNum = Part.PartNum
	INNER JOIN erp.PartCost 
		ON #Temp.PartNum = PartCost.PartNum
		AND PartCost.CostID = 1
	LEFT JOIN erp.PartOpr
		ON #Temp.PartNum = PartOpr.PartNum
		AND LastPartRev.RevisionNum = PartOpr.RevisionNum
		AND PartOpr.AltMethod = ''
	LEFT JOIN erp.PartOpDtl
		ON PartOpr.PartNum = PartOpDtl.PartNum
		AND PartOpr.RevisionNum = PartOpDtl.RevisionNum
		AND PartOpr.AltMethod = PartOpDtl.AltMethod
		AND PartOpr.OprSeq = PartOpDtl.OprSeq
		AND PartOpr.PrimaryProdOpDtl = PartOpDtl.OpDtlSeq
	LEFT JOIN erp.ResourceGroup
		ON PartOpDtl.ResourceGrpID = ResourceGroup.ResourceGrpID
	LEFT JOIN erp.OpMaster ON PartOpr.OpCode = OpMaster.OpCode

DROP TABLE #Temp
2 Likes
22

try doing a right moue click on the part from method tracker (top left panel part) then click on view costs. To get to method tracker select a part via part tracker, click on the revision tab and right mouse click on the revision you desire then select open with and select method tracker.

Useful info there and grid can be exported to excel

23

There is also this BAQ which I’ve run in test. Returns data but I haven’t validated yet:

24

FWIW, a quick note on this… the SH_BOM BAQ mentioned only lists BOM items for an individual parameter value partnum and doesn’t include any cost data.

1 Like
25

Late to the Party here… In the past I have used a hybrid of the suggestions above.

Load up the Costing workbench with selected Parts, Part Classes, or Product Groups
Run the rollup
Write BAQ on the CostPart table filtered to the CostPart.GroupID of the parts loaded in Costing Workbench
Rack and Stack as needed.

Hope this helps.

Dean

1 Like
26

you dont need to go through all that to do a cost rollup… you could use the COSTING WORKBENCH and play with the costs there…

  1. create a NEW COSTID “X” in the SITE COST MAINTENENCE - define it as your playground.
  2. in the Costing Workbench, you can LOAD the cost from your current average cost table into a costing workbench. When you do this, you can specify that you are costing to CostID “X”.
  3. in the actions menu, do a cost ROLLUP. What this does it it rolls the cost up through all the levels in the BOM, so that you will have a total cost at the top.

OPTIONAL STEP:

  1. IF YOU WANT< at this point you can “post” the cost into CostID “X”… since this cost id is not actually attached to any SITE ID, it will not cause any GL Transactions.

  2. NOW you can create queries and dashboards all day long that use COSTID X to retrieve the cost of any part.

1 Like
27

Hi Tim - If you are average cost you don’t need to do this though?

28

You can make a custom version of the BOM Cost report. I’ve done it before to make it more Excel friendly at my last company. Much better than 23k.