Gross Margin Labor Costs on Partial Shipments

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.

1 Like