I am researching the material costs to try to estimate our upcoming material costs based on jobs we plan to work on in the coming months. I found the JobMtl table to have a field called EstUnitCost. I am trying to trace this back to find out where the value came from. I don’t have any unit cost information for this material part number in my Part table.
JobMtl.EstUnitCost Description: Estimated Unit Cost of the material. Defaults from the Part table if valid PartNum.
If it doesn’t default from the part table (because the value there is 0.00), then where does it get a value from? I can use Purchase advisor to look up the history of this material part and I see the last few purchases were nowhere near the EstUnitCost in the JobMtl table.
I was using the PartCost table, looking at the last cost columns, as that is what I am interested in. Do you know where JobMtl.EstUnitCost comes from if Part.UnitPrice is 0. It is not coming from the PartCost table. Not in a way that I can see.
None of the columns in the PartCost table represent the Total Cost for any Costing Method but must be totaled across the five fields. I would use the Part Cost total shown on the Part screen to compare to the EstUnitCost shown on the Job. They should match for the appropriate Costing Method.
Here’s what the help text in Data Dictionary says about JobMtl.EstUnitCost:
Estimated Unit Cost of the material. Defaults from the Part table if valid PartNum.
Part.UnitPrice wouldn’t be the field though…also from Data Dictionary: Base Unit Selling Price for the Item. Maintainable only via Part Master Maintenance program. It is used as a default unit price on Sales Order line detail and on Invoice line details that are not referencing a sales order line.
So I’m not sure what field it’d be pulling from. There is a CostMethod at the Part and PartPlant levels. I’d think that, and/or use of CostID, would point you to the right numbers.
Me too!
In my example I have a material part that is last cost method, but we dont have a part.unitprice. I can see the last few purchases from purchase advisor indicate the material unit cost is about $30.00, per FT. My JobMtl.EstUnitCost is 1.73 per inch. If I multiply this out by 12 inches, I get $20.76 per FT. I really want to be able to use JobMtl.EstUnitCost as it would make my BAQ a lot simpler.
I would guess that the EstUnitCost on the Job is the cost at the time the Job was created. Is there a time gap between the Job and the values you are seeing in the Purchase Advisor?
Here’s the Data Dictionary field level info on PartCost.LastLaborCost - picked the first of the five buckets you need. It looks like PartCost is where it’s being stored (and not in the Part table)…Company, PartNum and/or CostID would probably be the keys to get to it…
Last Labor cost. Directly updated via the Part Master Maintenance program. Indirectly via Purchase Part receipts, Manufactured receipts or inventory cost adjustments if cost method is “last cost” . The last costs are overlaid by the most recent receipt cost. Both LastLaborCost and LastBurdenCost are set to zero when updated by a Purchased Part Receipt transaction.
Part.UnitPrice is ONLY used for populating the Selling Price on a Sales Order. It has nothing to do with cost, regardless of what the Help system says.
If this part number has a Cost Method of “last”, then the value in your JobMtl.EstUnitCost would be the sum of PartCost.LastLaborCost, LastBurdenCost, LastMaterialCost, LastSubContCost, and LastMtlBurCost ON THE DATE THAT THE JOB WAS CREATED. Since the Last Cost fields are updated with every stock receipt, those values may have changed between then and now.
Thanks for the intel. We might be an average/standard costing house but having that “as of” cost stamp on the JobMtl record might come in handy some day.
The other piece of this to keep in mind is that these are ESTIMATES… when the material is eventually issued to the job, the ACTUAL costs at that time will be used.
The Production Detail Report shows all and tells all when the dust finally settles.
By default, Epicor updates both the Average and Last cost fields in PartCost for each receipt-to-stock transaction. FIFO only updates if FIFO costing is selected as the cost method on the part. Standard cost is updated with the Costing Workbench.
Absolutely…and whatever hits PartTran rules all from the accounting side.
Having the LastCost values at job creation and comparing to actual-cost-at-issue might be useful in some reporting down the line…especially if there’s a big price spike or drop that affects a job’s profitability.
There is no functionality within Epicor that does this, but you could try using DMT. If you want it to automagicaly update with any cost changes at the part level, you’d need a pretty significant customization.
EDIT: OR you could delete the material and re-add it to the job (or delete the job and recreate it.)
I think it is close enough. I modified my BAQ to show the JobMtl.EstUnitCost, along with the LastMaterailCost from PartCost. I think together this is enough information to work off of.
Thanks everyone!
