We’re running into a costing issue in Epicor Kinetic related to scrap on a subcontracted part. (Admins: I didn’t know the best title for this post, so feel free to change it if needed.)
Process Overview
- Operation 20 is a subcontract operation.
- We send 120 pieces out to a vendor.
- The vendor processes them and returns all 120 pieces.
- The cost for this operation is roughly $30 per part.
- Total cost for this operation = about $3600.
- Operation 40 is an internal operation where we take each of these long bars and cut them into 210 smaller pieces per bar.
- This operation regularly produces some amount of scrap.
- This operation takes the 120 parts from op 20, and generates a total of about 25200 parts.
- At the op scrap was generated we have only completed about 4400 parts.
- Our scrap cost is being calculated at $3600 / 120pc = $30/pc
- Instead of $3600 / 25200pc = $0.15/pc
- I’m doing all this in a custom scrap costing BAQ (attached).
- I calculate scrap cost using the Total Actual Labor Cost for the operation divided by the number of completed pieces at that operation. Then multiply that cost by the number of scrapped parts.
- The goal of this BAQ is to see the costs that went into parts that get scrapped. We want the total cost of all operations and subassemblies that went into creating the scrapped part.
What We Need Help Understanding
- Is Epicor capable of allocating costs when one high‑value part is transformed into many lower‑value parts at a later operation?
- How should scrap costing work in this type of routing scenario?
- Are there specific job configuration settings, scrap reporting options, or costing methods we should be using to get Epicor to calculate this correctly?
- Is the problem entirely in my BAQ, or are we misusing the system somehow?
Here is a snip of my BAQ showing the relevant data for the part in question. Here you can see that the 248 pieces of scrap happend at op 40, and you can see the costs that went into these parts for each operation up through #40. I think I need to change my SCTot calculation to look at the required qty, instead of the completed qty. But I have to do that break out at op 20. Im so confused!
Notes: Although GPT helped generate the SQL that started my BAQ, I had to make a lot of edits to make it run. The BAQ seems to generate correct scrap values in most other jobs. I suspect anytime we run lengths of barstock, this issue could come up.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [AssemblyHierarchy] as
(select
[ja].[Company] as [Company],
[ja].[JobNum] as [JobNum],
[ja].[AssemblySeq] as [AssemblySeq],
[ja].[Parent] as [Parent],
[JobHead].[PartNum] as [JobHead_PartNum],
[ja].[PartNum] as [ja_PartNum],
(0) as [LevelDepth],
(ja.AssemblySeq) as [RootAssembly]
from Erp.JobAsmbl as [ja]
inner join Erp.JobHead as [JobHead] on
ja.Company = JobHead.Company
and ja.JobNum = JobHead.JobNum
where ( ja.JobNum = @JobNum )
union all
select
[child].[Company] as [child_Company],
[child].[JobNum] as [child_JobNum],
[child].[AssemblySeq] as [child_AssemblySeq],
[child].[Parent] as [child_Parent],
[parent].[JobHead_PartNum] as [JobHead_PartNum],
[parent].[ja_PartNum] as [ja_PartNum],
((parent.LevelDepth + 1)) as [Calculated_LevelDepth],
[parent].[RootAssembly] as [RootAssembly]
from Erp.JobAsmbl as [child]
inner join AssemblyHierarchy as [parent] on
child.Company = parent.Company
and child.JobNum = parent.JobNum
and child.Parent = parent.AssemblySeq
and child.AssemblySeq <> parent.AssemblySeq)
,[ConsumedAsm] as
(select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq]
from Erp.JobMtl as [JobMtl]
inner join Erp.JobAsmbl as [JobAsmbl1] on
JobMtl.Company = JobAsmbl1.Company
and JobMtl.JobNum = JobAsmbl1.JobNum
and JobMtl.PartNum = JobAsmbl1.PartNum
where (JobMtl.JobNum = @JobNum))
,[Scrap] as
(select
[ld].[Company] as [ld_Company],
[ld].[JobNum] as [ld_JobNum],
[ld].[AssemblySeq] as [ld_AssemblySeq],
[ld].[OprSeq] as [ld_OprSeq],
(SUM(ld.ScrapQty)) as [ScrapQty]
from Erp.LaborDtl as [ld]
where ( ld.JobNum = @JobNum
and ld.AssemblySeq = @AssemblySeq
and ld.ScrapQty > 0
and ld.OprSeq <= @ThruOp )
group by
[ld].[Company],
[ld].[JobNum],
[ld].[AssemblySeq],
[ld].[OprSeq])
,[AccumulatedCost] as
(select
[s].[ld_Company] as [ld_Company],
[s].[ld_JobNum] as [ld_JobNum],
[s].[ld_AssemblySeq] as [ld_AssemblySeq],
[s].[ld_OprSeq] as [ld_OprSeq],
[s].[ScrapQty] as [ScrapQty],
[h].[JobHead_PartNum] as [JobHead_PartNum],
[h].[ja_PartNum] as [ja_PartNum],
[jo].[AssemblySeq] as [jo_AssemblySeq],
[jo].[OprSeq] as [jo_OprSeq],
(ISNULL(jo.ActLabCost, 0) + ISNULL(jo.ActBurCost, 0) + ISNULL(jm.TotalCost, 0)) as [TotalAccumulatedCost],
(ISNULL(jo.QtyCompleted, 0)) as [QtyCompleted],
[jo].[SubContract] as [jo_SubContract],
[jo].[ActLabCost] as [jo_ActLabCost],
[jo].[ActBurCost] as [jo_ActBurCost],
[jm].[TotalCost] as [jm_TotalCost]
from Scrap as [s]
inner join AssemblyHierarchy as [h] on
s.ld_Company = h.Company
and s.ld_JobNum = h.JobNum
and s.ld_AssemblySeq = h.AssemblySeq
left outer join ConsumedAsm as [ConsumedAsm] on
h.Company = ConsumedAsm.JobMtl_Company
and h.JobNum = ConsumedAsm.JobMtl_JobNum
and h.AssemblySeq = ConsumedAsm.JobAsmbl1_AssemblySeq
left outer join Erp.JobOper as [jo] on
h.Company = jo.Company
and h.JobNum = jo.JobNum
and (h.AssemblySeq = jo.AssemblySeq
or ConsumedAsm.JobMtl_AssemblySeq = jo.AssemblySeq)
and ( jo.OprSeq <= s.ld_OprSeq )
left outer join Erp.JobMtl as [jm] on
jo.Company = jm.Company
and jo.JobNum = jm.JobNum
and jo.AssemblySeq = jm.AssemblySeq
and jo.OprSeq = jm.RelatedOperation
left outer join Erp.JobAsmbl as [JobAsmbl] on
jm.Company = JobAsmbl.Company
and jm.JobNum = JobAsmbl.JobNum
and jm.AssemblySeq = JobAsmbl.AssemblySeq)
select distinct
[AccumulatedCost].[ld_Company] as [ld_Company],
[AccumulatedCost].[ld_JobNum] as [ld_JobNum],
[AccumulatedCost].[ld_AssemblySeq] as [ld_AssemblySeq],
[AccumulatedCost].[ld_OprSeq] as [ld_OprSeq],
[AccumulatedCost].[ScrapQty] as [ScrapQty],
[AccumulatedCost].[TotalAccumulatedCost] as [TotalAccumulatedCost],
(iif(AccumulatedCost.QtyCompleted=0,0, iif(AccumulatedCost.TotalAccumulatedCost=0,0,AccumulatedCost.TotalAccumulatedCost / AccumulatedCost.QtyCompleted))) as [UnitCostAtScrap],
(iif(AccumulatedCost.QtyCompleted=0,0,iif(AccumulatedCost.TotalAccumulatedCost=0,0,iif(AccumulatedCost.ScrapQty=0,0,(((AccumulatedCost.TotalAccumulatedCost / AccumulatedCost.QtyCompleted)) * AccumulatedCost.ScrapQty) )))) as [ScrapCost],
[AccumulatedCost].[jo_AssemblySeq] as [jo_AssemblySeq],
[AccumulatedCost].[jo_OprSeq] as [jo_OprSeq],
[AccumulatedCost].[QtyCompleted] as [QtyCompleted],
(iif(iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted)=0,0,iif(AccumulatedCost.jo_SubContract=0, AccumulatedCost.jo_ActLabCost/ iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted), 0))) as [Calculated_LabTot],
(iif(iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted)=0,0,AccumulatedCost.jm_TotalCost / iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted))) as [Calculated_MatTot],
(iif(iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted)=0,0,iif(AccumulatedCost.jo_SubContract=1, AccumulatedCost.jo_ActLabCost/ iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted), 0))) as [Calculated_SCTot],
(iif(iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted)=0,0,AccumulatedCost.jo_ActBurCost / iif(AccumulatedCost.QtyCompleted=0,0,AccumulatedCost.QtyCompleted))) as [Calculated_BurTot]
from AccumulatedCost as [AccumulatedCost]
ScrapCostingKinetic (1).baq (22.0 KB)
The observant among you may notice that op 10 is in the same boat. Lets stay focused on op 20, as that should resolve the issue for both.
Thank you for your time!
Nate
