BASIC QUESTION: How get cost of manufactured part?

jgiese.wci · April 20, 2022, 6:50pm

92 hours seems a bit stiff but if you can do it for less with a BAQ you for sure should! Shop around reach out to VARS as well.

Jeff_Owens · April 20, 2022, 6:57pm

They said “there is specific code in the process that makes it impossible to perform with a BAQ”, and they’d have to write some BPM code and hybrid reverse-engineer the BOM report to accomplish this.

I’m not even going to present this to leadership – for fear of getting laughed out of the room.

bsiller · April 20, 2022, 6:59pm

For the ugly BOM cost report, you could change the output format to CSV, then at least you get one row per part to work with in Excel. It’s still ugly in that you need to delete unnecessary and redundant columns, but at least it’s closer to a useable format than the PDF.

Or, if this is something you run and refresh regularly, you could create a new Report Style and delete all of the fields and labels that you don’t need, then export it as a CSV. Should give you the same numbers as the full BOM cost report, but without all of the clutter.

Jeff_Owens · April 20, 2022, 7:07pm

Yeah, this attempt at having them write a BAQ was a seemingly “easier” pay-vs-build way rather than modifying/editing the SSRS output.

TomAlexander · April 20, 2022, 7:07pm

Agree with @bsiller … if the BOMCost dataset works for your needs and your only qualm is that it’s ugly, then spend the $2-3k on building something off of that…

That’s a really high hourly rate you’re implying there… I hope that’s not what they charge you lol.

jgiese.wci · April 20, 2022, 7:08pm

250 is pretty standard these days. No idea their hourly but I went with the mean of what i’ve been seeing. CSG is not a cheap endeavor.

ldenton · April 20, 2022, 7:53pm

a bit late here, but i am new to Epicor v.10 and any idea where can i find the training video? my background is in cost (QAD system)

Craig · April 20, 2022, 8:21pm

@Jeff_Owens is the goal to get a detailed Itemized list of what makes up the cost of a manufactured part or just a list of parts and the BOM cost summarized
Is the goal to get a massive list all at once or would they want to run it as needed as they are selling a part?

Jeff_Owens · April 20, 2022, 8:32pm

Yes @Craig , the idea was to get an entire list, across all parent parts (as a BAQ maybe filtered by group or something) similar to:

Parent PartNum, Description, BOM Material Cost (Sum), BOM Labor Cost (Sum), Base/Unit Selling Price

We’re doing this so we can find “fat fingered” part cost variances where someone may have attributed too much BOM material or whatever (in terms of quantity) that is skewing the cost of the parent part.

Jeff_Owens · April 20, 2022, 8:34pm

Yeah, I concur @jgiese.wci … I think $225-ish rates is what I’ve heard amongst peers for CSG. For this quote, it was ~30 design + ~70 engineering.

jtownsend · April 20, 2022, 8:44pm

LOL @ 100 hours. It took me like…a day…to build a cost-rollup tool in SQL\SSRS and I even included dials to adjust efficiency and economy of scale. I don’t think there was anything in it that couldn’t also be accomplished with a recursive BAQ/dashboard.

utaylor · April 20, 2022, 8:46pm

Would you mind sharing it?

jtownsend · April 20, 2022, 9:03pm

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

PaulMorgan · April 21, 2022, 4:59am

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

Matt_Belshaw · April 21, 2022, 11:20am

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

Jeff_Owens · April 21, 2022, 11:48am

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.

DeanMiller · April 21, 2022, 1:43pm

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

timshuwy · April 21, 2022, 7:51pm

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…

create a NEW COSTID “X” in the SITE COST MAINTENENCE - define it as your playground.
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”.
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:

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.
NOW you can create queries and dashboards all day long that use COSTID X to retrieve the cost of any part.

Matt_Belshaw · April 21, 2022, 8:37pm

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

Randy · April 21, 2022, 8:40pm

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.