Work In Process

Epicor ERP 10
1

I am trying to understand the Work In Process Report that I run at the end of each month. There are some jobs listed that have a balance total listed under the To Inventory/To Job category, some that have COS/MFG-VAR category, and then some are listed within the W.I.P. category. I am wanting to better understand what causes each of these scenarios. I would also like to figure out if there are different reports or filters that would be more beneficial or relevant to receiving the details for this information. I have some older information that is stuck in here and if I close the job I am afraid it will make my current numbers inaccurate.

2

You should check out the Inventory Transaction Hierarchy document. It explains what each part transaction type (e.g. MFG-STK, MFG-VAR, PUR-MTL,…) means. It’ll also tell you what kind of journal accounts get transacted against when the capture wip process is run.

I also recommend looking at the Part Transaction (PartTran) table. Either through SQL or a BAQ. This is the underlying data that drives the capture WIP process. The GLTrans column indicates if each row will be used during Capture WIP. And the PostedToGL column indicates if it’s already been captured. The LaborDtl table is also used too and has the same two columns. You might try creating a new job and charging time and materials to it. And rerunning your BAQs to see what data was created along the way.

Epicor10_Hierarchy.InventoryTrans_102700.pdf (160.7 KB)

1 Like
3

Hi @SchuylerQHorky ,

Do you happen to have the logic for creating the same numbers as WIP report?
We need to create a dashboard for it and it seems almost impossible to make one.

4

It’s complicated. The reason it’s complicated is that the capture WIP and the WIP reconciliation report depend on the posting rules and the GL controls that you have. It will choose different accounts for you than for me. So it’s difficult to give you an exact recipe.

However, here’s some things I’ve discovered. Anyone else please pipe in if you know more.

Initial Data
Capture WIP looks at 2 data sources in the database. Erp.PartTran and Erp.LaborDtl.
Here are the rows in your database that Epicor processes in those 2 tables when it runs capture WIP.

SELECT * FROM Erp.partTran
WHERE GLTrans = 1 AND PostedToGL = 0
SELECT * FROM Erp.LaborDtl
WHERE GLTrans = 1 AND PostedToGL = 0 AND TimeStatus='A' /*approved time only*/

When it captures successfully, per-record, it changes PostedToGL = 1. This is how it tracks what’s been posted and what’s not. Of course you can also run the WIP report for old stuff, but that’s less relevant I think.

Amounts
For LaborDtl, Capture WIP seems to post two sets of journal lines, one for burden and one for labor.

PostedLabor = LaborHrs * LaborRate
PostedBurden = BurdenHrs * BurdenRate

For project and job labor, the GL controls on Inventory WIP/COS, Part, and Product Code will tell you where those will go.
For indirect labor, the GL controls for the expense code will tell you what accounts will be used for everything else.

For PartTran, Capture WIP seems to post the value in ExtCost. This one is particulary tricky since there’s so many different TranTypes that are supported. To dive in, look up the “Inventory Transaction Hierarchy” document on Epicor for your version. This will tell you what GL controls Epicor will lookup and in what order, based on the type of part transaction.

It also should be mentioned the Erp.TranGLC table. This table holds metadata behind Epicor’s journals. It can be used to link all business documents (invoices, bank adjustments, part transactions, labor details, …) to journals.

Here’s a way of showing the data that ties journals to part transactions.
The query here uses the Key columns on the TranGLC table to look up the part transaction that’s associated with it. You can lookup other document types too, but they use the Key1,Key2,etc fields differently.

SELECT
	GLC.FiscalYear,
	GLC.JournalNum,
	GLC.JournalLine,
	GLC.JournalCode, 
	GLC.RelatedToFile,
	GLC.GLAccount,
	Account=GLC.SegValue1,
	Dept=GLC.SegValue2,
	NetAmount =GLC.BookDebitAmount - GLC.BookCreditAmount,
	[Type]='Part Transaction',
	[Document] = 'Tran ' + GLC.Key3,
	[Desc] = LEFT(PT.PartDescription,100) 
FROM Erp.TranGLC GLC
LEFT JOIN Erp.PartTran PT ON GLC.RelatedToFile = 'PartTran' AND CONVERT(VARCHAR(10),PT.TranNum) = GLC.Key3 AND PT.GLTrans = 1  
WHERE GLC.RelatedToFile = 'PartTran'  AND GLC.FiscalYear = 2022

Dates
Both PartTran and LaborDtl have 2 dates on each row, the sys date and tran date. Tran Date should be something like the actual calendar date that the part movement or labor occured. SysDate may often be TranDate, but do not assume that. SysDate is reported on the journal side more often (I think).

Learning more
You can gain additional insight into Epicor’s behavior if you look at the data Epicor uses to generate the WIP reconciliation report pdf.
Generate a WIP reconcilation report and archive it. Then go into the database and you can get the data behind it. This will be a treasure trove of data you can use to check your code against.

/*Change this to whatever your reports db is */
USE EpicorERPReports

/*Print out archived WIP reconciliation report tables*/
SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME LIKE 'Recon%'

/*Choose table name from previous statement and paste below*/
SELECT * FROM Recon_27A27CBE38214F6587918E4B4FDC0E7B

Now you know what I know! What do you think? Questions?

4 Likes
5

This is very comprehensive
I will spend a day on all of this to digest it completely
Thank you very much :slight_smile: