Getting to bottom of WIP report "Include Phantoms"

[TerryR (Terry Raymond)]

Hey all sharing some info after a lot of digging. At year end we ran some GRNI invoices on some closed jobs. This added a bunch of transactions to the PartTran table. And it did NOT uncheck the WIP Cleared box on Job Closing. So when we ran the WIP Report, it was showing tons of closed jobs with WIP.

To fix, we’re having to manually unclose those jobs, uncheck wip cleared, reclose the jobs, and run Capture WIP. This fixes it but its pretty time consuming, and we wanted to know what exactly went wrong.

• Note, when running WIP Report for troubleshooting, you want to have the report “WIP Cleared” Option set to Both. Otherwise the report might not check the job. If all your jobs are good, you can run it with default No, which is faster. But if you’re not sure they are all good, better to use Both.

My understanding:
The WIP Report “Include Phantoms”, if CHECKED, means that it will show Zero WIP for closed jobs with “WIP Cleared” UNCHECKED. It knows that when CaptureCOS/WIP runs, it will create a MFG-VAR transaction to cancel the remaining WIP.

If Include Phantoms is UNCHECKED, then it doesn’t zero out WIP, it just shows the current PartTran balance.

For example:
Here are all the the PartTran transactions on a job, which sum to -$47.76 (this is WIP, or difference from the job costs minus the value of shipped equipment).

For this job, on Job Closing, it is Completed/Closed, but WIP Cleared was UNCHECKED.
So when I run WIP Report with Include Phantoms UNCHECKED, it shows that there is -$47.76 WIP.
And when I run WIP Report with Include Phantoms CHECKED, it shows Zero WIP.

WIP Reconciliation shows it like this:

Indicating that it will balance it out with phantom transactions on the GL when Capture COS/WIP is run. This will also generate a MFG-VAR PartTran transaction that will zero out the balance in the PartTran table. And then next time WIP Report is run, with either Include Phantoms Checked or Unchecked, the job will show zero WIP.

I’d initially started this post as a question but ended up figuring it all out while trying to identify exactly where I was stuck. But man is this confusing.

Some links…

Phantom Purge WIP to Variance

Phantom MFG-VAR transactions are temporary records which are created for Completed Jobs which have remaining costs in WIP and no outstanding uncosted shipment records. These temporary transactions are automatically generated as the Inventory/WIP Reconciliation Report captures simulated COS and WIP information.  The records do not exist as MFG-VAR PartTran records in your database.   When a job is closed, any remaining costs are purged from WIP during the WIP/COS calculation process.  Thes…

Negative WIP Balances

Hi I have now found the problem… it was credits matched to jobs through job adjustments, but the jobs had previously been closed and captured, marking these as WIP Cleared in the Job closing screen. When they were reopened to make the adjustments, WIP Cleared remained ticked in the Job Closing screen when they were closed. I have gone through the jobs with negative balances on Jobs with “Only if WIP Cleared is Yes” Unticked Closed, Unticked Complete. Save. Untick WIP Cleared. Save. Tick Cl…

Work In Process

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 …

From Epicor help…

Quirks… there are probably dozens but here are some I ran into:

1. For make-to-stock jobs, they need to be received into inventory in order to complete/close/clear wip properly.
2. GRNI process adds transactions to closed jobs, and will make negative WIP. To fix, you need to unclose job, uncheck WIP cleared, save. Close job and run capture.
3. If a job refuses to clear WIP, it might be due to a bug. JobMtl or JobAsmbl Costs might be out of sync with PartTrans. Unclose and uncomplete the job and run Conversion 620 in conversion workbench. See KB KB0045081 for this. Here is the SQL I used:

select sum(mtlUnitcost*
case
	when TranQty = 0 then 1
	else TranQty
end)
from erp.parttran 
where company = 'xx'
and jobnum = '000344'
and not trantype like 'MFG%'
and not trantype like 'INS%'

select sum(totalcost) from JobMtl where company = 'xx' and jobnum = '000344'

select sum(TLAMaterialCost) from JobAsmbl where company = 'xx' and jobnum = '000344'

See here how the TLAMaterialCost is out of sync:

Note that sometimes there are transactions with no unitcost, only extcost, that above would miss. For those you’ll need logic to tie the cost to mtl or sub or burden etc by the trantype.

4. We had some old jobs with some Burden WIP that wouldn’t clear. I found the “Repair JobOper Hours and Costs program” and ran it, and then ran CaptureCOS/WIP, and the Burden WIP cleared. The repair fixed 1100 operations. Be warned though - if you use projects, you have to open all the projects before running repair, and then close them afterwards.