I have a requirement to collect Job Actual Cost data “up to X date” and I’m struggling on how to properly get the material costs. (I’ve got labor worked out fine). As a Note; I won’t be able to use existing project analysis or anything like that because I need to break down the costs by some additional criteria.
I’ve narrowed it down that I need to collect parttrans records up to my “X date”, but the problem is that there are loads of transaction types, and I don’t know which ones to filter by in order to get the right numbers. I should note that I am also FAR from an accountant, so much of the terminology is very fresh to me.
Does anyone have any insight as to which transaction types I’d need to include/exclude? So far, I’ve had my best luck on a sample project by excluding transactions with the class of “A” and “S” and this got me correct results for that sample, but the project only had maybe 3-5 different actual transaction types present, and didn’t have any “D” or “X” class transactions.
First off you can put a Table Criteria on that limits the PartTRan records to on be ones from prior to your desired date. You’ll have to choose whether to us the TranDate or SystemDate as your cutoff.
Next, you’ll need to correct the sign of the cost, based on the transaction type.
In the following, STK-MTL and PUR-MTL adds to the Job Cost, MFG-CUS take away from it
There is a PartTran.InventoryTrans field. You can take the first row <= your cutoff date that has this inventorytrans = true and sum up the mtl,labor,burden,sub,mtlbur unit cost fields in that row.
Yeah, sorry, I was thinking you were looking for the material cost of a specific part at a specific time.
If you want to cover everything on jobs you can start with filtering on * -MTL, * -ASM, * -SUB, for cost going in and MTL- * , ASM- * , SUB- * , MFG- * for cost going out.
KB0038528 has the transactions types for reference.
Thanks for the speedy replies! I’ve looked into your suggestions and it looks like MFG-CUS actually throws my numbers off (Added or subtracted) are you sure those contribute to the actual cost? I’m essentially trying to match against what would show on the “actual” column of project costing analysis if that helps.
Ross’s note about
That makes sense except for maybe the “out” ones… that MFG-CUS one actually gives me the wrong number when included. would there be issues with the other “out” transactions do you think?
Are you only trying to get the total cost accumulation at a date or the net balance (cost in/out)? The * -X transaction examples are going to capture the costs going in to the job and the X- * transaction examples are going to capture the cost going off the job (job receipts to inventory, shipments from the job, inspection entries, etc.). The MFG-CUS is a customer shipment directly from the job. That will carry material, labor, burden, subcontract values and take if off the job.
As an example, the work in process report will show you the total accumulation of the job, what went to inventory or shipped and what is remaining. You can use that as a reference for your query, if you are not already.
If you are only going for cost buildup and not removal (put into stock, shipments, etc.) then you can just remove the transactions going out.
The others might not be needed but I am considering whether you use inspection processing, DMR, assembly issuing, etc. and was throwing a wider net.
A good way to see what you are dealing with is to just do a complete dump of the parttran records for a specific job number in question. You can see all of the transaction types, the unit cost breakouts and the extcost field. That should tie back to your cost add/removal from the job. If you want to post an excel file on this thread I can take a look and add comments.
Sorry, I’m still having a slightly hard time finding the right terms in this area of the system. We are trying to get a collection of accumulated costs (the total job costs up to a point). Same thing that would show up on job analysis at the end of the project for actual costs.
And we will have some purchases going for inspection and DMR, so those may factor in.
What I’ve been using to check my work on my few samples is the “build project analysis” function in project entry. It gives the accurate totals we want to match against for actual material costs, my QRY is just breaking down those costs according to some additional criteria.
I’ve got to dig around a bit to see if we can get a sample with INS and DMR involved.
Attached are a list of transaction types in Epicor. MTL is job material and in the transaction type model it uses a [source - destination] format so, an an example, any transaction type with the destination as MTL is going to be material going on the job and if it is in the source location it is going to going off the job. Of course, there are always job adjustments as well (ADJ).
With that in mind, elements of job transaction types are listed below.
MTL - Job Material
ASM - Job Assemblies
MFG - Generally Final Jobs Assemblies
SUB- Job Subcontract activities
If you only want the build up, you can just take the transaction types with job transaction type elements on the destination side. A complete list of part transactions for the job would be a big help so you can see the whole thing on one sheet and group accordingly.
There is a built-in report that you can use called “Production Detail Report” available as a stand-alone report or from Job Entry/Tracker. It gives you the summary of all costs with the ability to print detailed transactions.
Thanks all for the info, through some of these tips and working w/ our accounting group to validate them, this is the list I’ve went with that seems to work for our company’s setup;
(+) add the extcost to the total
(-) subtract the extcost from the total
INS-MTL (+) inspection to job matl
INS-SUB (+) inspection to subcontract
PUR-MTL (+) purch to job matl
PUR-SUB (+) purch to subcontract
ASM-INS (-) assembly to inspection
INS-ASM (+) inspection to assembly
MTL-INS (-) matl to inspection
STK-ASM (+) stock to assembly
STK-MTL (+) stock to job matl
SUB-DMR (-) subcontract to DMR
SUB-INS (-) subcontract to inspection
DMR-MTL (+) DMR to job matl
DMR-SUB (+) DMR to subcontract
MTL-DMR (-) job matl to DMR
ADJ-MTL (+) adjustment to job matl cost
ADJ-SUB (+) adjustment to subcontract cost
ADJ-PUR (+) adjustment to purch
