Not sure if this works for you but working with Epicor and helping them fix their Bug I came up with this Temporary BPM that was passed to them.
Erp.ARInvoice.GetShipments.POST.TempJCInvcDtlBugFix
//
// Populate Job Costing Fields on InvcDtl when the Job is closed
//
// NOTES:
// Usually Job Closing Costs are populated when the Job is closed
// however if the Job has been closed before Invoicing the JC Costs
// are not populated. The manual is to open the job and close it again.
// Hence why this BPM was created to populate the JC Costs without
// having to open and close the job.
//
//
// Variable used for Roundint
// One should use Erp.Internal.Lib.GetDecimalsNumber.LibGetDecimalsNumber if they
// have different costing across companies, the default is 5 and most used is 5
int nCostDecimals = 5;
foreach (string invoiceNum in Invoices.Split(new string[] { Ice.Constants.LIST_DELIM }, StringSplitOptions.RemoveEmptyEntries))
{
// Get Invoices that are Make Direct, associated to a Job
// Where the Job is closed
// There could be multiple Invoice Lines
var invDetailRows =
(from ih in Db.InvcHead.With(LockHint.NoLock)
join id in Db.InvcDtl.With(LockHint.NoLock) on new { ih.Company, ih.InvoiceNum } equals new { id.Company, id.InvoiceNum }
join sd in Db.ShipDtl.With(LockHint.NoLock) on new { id.Company, id.PackNum, id.PackLine } equals new { sd.Company, sd.PackNum, sd.PackLine }
join jh in Db.JobHead.With(LockHint.NoLock) on new { sd.Company, sd.JobNum } equals new { jh.Company, jh.JobNum }
where ih.Company == Session.CompanyID
&& ih.GroupID == GroupID
&& ih.InvoiceType == "SHP"
&& ih.InvoiceNum == Erp.ErpEFFunctions.ConvertToInt(invoiceNum)
&& id.OurShipQty > 0
&& id.ConsolidateLines == false
&& jh.JobClosed == true
select new {
id.Company,
id.InvoiceNum,
id.InvoiceLine,
id.PackNum,
id.PackLine,
jh.JobNum,
jh.PartNum,
jh.JobClosed,
jh.ProdQty,
JobProdQty = (jh.ProdQty <= 0) ? 1 : jh.ProdQty,
PartTranShippedQty = Db.PartTran.Where(w => w.Company == id.Company && w.JobNum == jh.JobNum && w.PartNum == jh.PartNum && w.TranType == "MFG-CUS")
.Select(s => s.TranQty).DefaultIfEmpty(0).Sum()
}
).ToList();
// This is the exact same algorithm Epicor uses when you close a Job
foreach (var idRow in invDetailRows)
{
Ice.Diagnostics.Log.WriteEntry($"[ JC Fix ] InvoiceNum: {idRow.InvoiceNum} - InvoiceLine: {idRow.InvoiceLine} - PackNum: {idRow.PackNum} - PackLine: {idRow.PackLine} - JobNum: {idRow.JobNum} - JobClosed: {idRow.JobClosed}" +
$" - Part: {idRow.PartNum} - ProdQty: {idRow.ProdQty} - JobProdQty: {idRow.JobProdQty} - PartTranShippedQty: {idRow.PartTranShippedQty}");
var JobAsmblRow = Db.JobAsmbl.Where(w => w.Company == idRow.Company && w.JobNum == idRow.JobNum && w.AssemblySeq == 0).FirstOrDefault();
if (JobAsmblRow != null)
{
var JobPartJobProd =
(from jp in Db.JobPart
join jp2 in Db.JobProd on new { jp.Company, jp.JobNum, jp.PartNum } equals new { jp2.Company, jp2.JobNum, jp2.PartNum }
where jp.Company == idRow.Company
&& jp.JobNum == idRow.JobNum
&& jp.ShippedQty > 0
&& jp2.ShippedQty > 0
select new {
JobPart = jp,
JobProd = jp2
}
).ToList();
foreach (var JobProdRow in JobPartJobProd)
{
var JoinResult = JobProdRow;
decimal varShippedQty = (idRow.PartTranShippedQty <= 0) ? idRow.JobProdQty : idRow.PartTranShippedQty;
// Get InvcDtl Rows to update including historical partial shipments
// this is the exact same algorithm Epicor uses when you close a Job
var InvcDtlRows =
(from id in Db.InvcDtl
where id.Company == JoinResult.JobProd.Company
//&& id.InvoiceNum == idRow.InvoiceNum
//&& id.InvoiceLine == idRow.InvoiceLine
&& id.OrderNum == JoinResult.JobProd.OrderNum
&& id.OrderLine == JoinResult.JobProd.OrderLine
&& id.OrderRelNum == JoinResult.JobProd.OrderRelNum
&& id.OurShipQty > 0
&& id.ConsolidateLines == false
select id
).ToList();
//
// Update Costs
//
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var InvcDtl in InvcDtlRows)
{
InvcDtl.JCBurUnitCost = Math.Round((JobAsmblRow.TLABurdenCost + JobAsmblRow.LLABurdenCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCLbrUnitCost = Math.Round((JobAsmblRow.TLALaborCost + JobAsmblRow.LLALaborCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCMtlBurUnitCost = Math.Round((JobAsmblRow.TLAMtlBurCost + JobAsmblRow.LLAMtlBurCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCMtlUnitCost = Math.Round((JobAsmblRow.TLAMaterialCost + JobAsmblRow.LLAMaterialCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCSubUnitCost = Math.Round((JobAsmblRow.TLASubcontractCost + JobAsmblRow.LLASubcontractCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
Ice.Diagnostics.Log.WriteEntry($"[ Updated ] InvoiceNum: {InvcDtl.InvoiceNum} - InvoiceLine: {InvcDtl.InvoiceLine} - JCBurUnitCost: {InvcDtl.JCBurUnitCost} - JCLbrUnitCost: {InvcDtl.JCLbrUnitCost} - JCMtlBurUnitCost: {InvcDtl.JCMtlBurUnitCost} - JCMtlUnitCost: {InvcDtl.JCMtlUnitCost} - JCSubUnitCost: {InvcDtl.JCSubUnitCost}");
}
Db.Validate();
txScope.Complete();
}
// Epicor is also doing something with InvcDtlPack
// we have not tested this scenario but it may be in the future
// leaving this in for reference
//
// using (libCreateInvcDtlPack = new AR.CreateInvcDtlPack(Db))
// {
// libCreateInvcDtlPack.UpdateInvcDtlPackJobClosing(JoinResult.JobProd, JobAsmbl, varShippedQty, nCostDecimals);
// }
}
} /* JobAsmbl */
}
}
I spent so much time on SGM I kinda know what you are talking about but dont recall. I know the report had shortcomings. SGM wasnt calculated on InvcDtl until the post happened, etc…
Try Reporting Qty before Shipping.