Work in Process Cost BAQ

Epicor ERP 10
,
1

I am trying to create a baq that will mimic the Work In Process report but show all of the data in a format that is usable in Excel. Has anyone ever done that? I have been able to get the JobAsml costs but am having issues trying to sum the PartTran costs when there are multiple entries for jobs. Thanks in advance.

2

Here’s an old query I had from E9. I had a helper table in a seperate database that had all the tran types and what they did. You will see it here as SuperEpi.dbo.PartTranSign. If you get all the signs correct in part tran then a union between PartTran and LaborDtl will match the Work in Process report exactly.

Select JobNum, PartNum, TranDate
,TranQty * MtlUnitCost * WIPSign as WipMateral
,TranQty * LbrUnitCost * WIPSign as WipLabor
,TranQty * BurUnitCost * WIPSign as WipBur
,TranQty * SubUnitCost * WIPSign as WipSub
,TranQty * MtlBurUnitCost * WIPSign as WipMtlBur
,case when WIPSign = -1 then TranQty * MtlUnitCost * StockSign else 0 end as StkMtl
,case when WIPSign = -1 then TranQty * LbrUnitCost * StockSign else 0 end as StkLabor
,case when WIPSign = -1 then TranQty * BurUnitCost * StockSign else 0 end as StkBur
,case when WIPSign = -1 then TranQty * SubUnitCost * StockSign else 0 end as StkSub
,case when WIPSign = -1 then TranQty * MtlBurUnitCost * StockSign else 0 end as StkMtlBur

,case when WIPSign = -1 then TranQty * MtlUnitCost * COSSign else 0 end as COSMtl
,case when WIPSign = -1 then TranQty * LbrUnitCost * COSSign else 0 end as COSLabor
,case when WIPSign = -1 then TranQty * BurUnitCost * COSSign else 0 end as COSBur
,case when WIPSign = -1 then TranQty * SubUnitCost * COSSign else 0 end as COSSub
,case when WIPSign = -1 then TranQty * MtlBurUnitCost * COSSign else 0 end as COSMtlBur

from PartTran 
inner join SuperEpi.dbo.PartTranSign on PartTran.TranType = PartTranSign.TranType

where PartTran.JobNum = @JobNum and PartTran.Company = @Company and PartTran.TranDate <= @CutOffDate

union all

Select JobNum, 'Labor' as PartNum, PayrollDate as TranDate
,0 as WipMateral
,LaborHrs * LaborRate as WipLabor
,BurdenHrs * BurdenRate as WipBur
,0 as WipSub
,0 as WipMtlBur
,0 as StkMtl
,0 as StkLabor
,0 as StkBur
,0 as StkSub
,0 as StkMtlBur

,0 as COSMtl
,0 as COSLabor
,0 as COSBur
,0 as COSSub
,0 as COSMtlBur

From LaborDtl
3 Likes
3

Hi Carson,

This looks great!
Appreciate this is an older post - but would you be able to share the PartSign Table?

Many thanks

Mark