PO cost greater than STD Cost - BPM

[TrevorLenten]

I’ve been asked to create a BPM that will show a message to contact the buyer if the cost of a PO received is >= or <= $1000 + STD cost.

I can do this in SQL (not sure if RcvDtl.OurUnitCost is the correct column to compare) but I’m not sure how to use this in a BPM. Anyone have an idea of how to implement this?

SELECT 
    rd.ponum, 
    rd.poline, 
    rd.partnum, 
    SUM(pc.StdBurdenCost + pc.StdLaborCost + pc.StdMaterialCost + pc.StdMtlBurCost + pc.StdSubContCost) as 'STD Cost', 
    rd.OurUnitCost,
    SUM(pc.StdBurdenCost + pc.StdLaborCost + pc.StdMaterialCost + pc.StdMtlBurCost + pc.StdSubContCost) - rd.OurUnitCost AS 'Cost Difference'
FROM 
    RcvDtl rd
JOIN 
    partcost pc ON pc.company = rd.company AND pc.partnum = rd.partnum

GROUP BY 
    rd.ponum, rd.poline, pc.StdBurdenCost, pc.StdLaborCost, pc.StdMaterialCost, pc.StdMtlBurCost, pc.StdSubContCost, rd.partnum, rd.OurUnitCost 

HAVING 
    SUM(pc.StdBurdenCost + pc.StdLaborCost + pc.StdMaterialCost + pc.StdMtlBurCost + pc.StdSubContCost) >= rd.OurUnitCost + 1000
    OR 
    SUM(pc.StdBurdenCost + pc.StdLaborCost + pc.StdMaterialCost + pc.StdMtlBurCost + pc.StdSubContCost) <= rd.OurUnitCost - 1000

[JasonMcD (Jason McDermott)]

Yes… So much to say here.

I do this on PartTran, which is… kind of evil. Not great for performance and all that. Also I made the BPM in the dark ages before Epicor Functions. Setting aside my poor implementation, though…

1. Are you wanting a Data Directive BPM (on the table) or a Method Directive (on an action)?
   a. If Method, be advised that mass receipt is different from single receipt.
2. I do recommend using an Epicor Function to get the hang of the math, etc. Then wire that into your BPM.
3. “The” Standard Cost is not a thing. You have to ping the Plant table to get the active CostID for that site. I get it, today you only have one. But you won’t always…
4. Are you intending this for expense (and MRO) items, too? If so, that’s fine. But they don’t really obey standard cost, so you may or may not care in those cases.

Also, it seems that my BPM could only handle 2-decimal precision, which gets annoying sometimes. But you’re not worried about that level of detail. Just wanted to mention that. (I don’t remember the exact point that it does that. One day I’ll fix it. Sure.)

[TrevorLenten]

1. I believe a method would be best, no? I’ve read not to touch data directives unless you really have to.

2. I haven’t worked with Epicor functions at all but I will look into this approach.

3. I was kind of wondering if this is something that should be done in a BPM or maybe we should look at the process instead. Just thinking back to what happened with raw material prices in 2020, if something similar were to happen that may generate some irritation with purchasers.
   We are already multi-plant so I am glad I took the time to ask rather than try and
   hack something together as I would not have thought to check the plant table.

4. Nothing was mentioned to me about this.

[JasonMcD (Jason McDermott)]

Right, method directives are proper. Just know that you’ll have to make 2 BPMs at least - one for Mass Receipt and one for a single line (whatever that’s called). And the mass receipt, of course, is a dataset with many rows, so somehow you have to get the cost of each row and compare it to the PO price on each row.

I was lazy/scared and just did a Data Directive.

@TrevorLenten

maybe we should look at the process instead

Meh. Normally, that’s the answer. But ordinary users just don’t care about some arbitrary number defined by accounting. (I do care; I’m just saying.)

[JasonMcD (Jason McDermott)]

Ah, you know, I’m thinking about the BPM I made to catch zero standard cost.

Well, it is the same principle. Something like this.

[JasonMcD (Jason McDermott)]

@TrevorLenten

I haven’t worked with Epicor functions at all but I will look into this approach

An EFx is essentially the middle part of a BPM (or some other proportion). You’ll see it looks immensely familiar.

But you can test it in Postman by giving an input and output.

Once you see it’s working, reference it in your BPM. The BPM just becomes a trigger. All the code can live in the EFx.

Once made, it’s a widget in the BPM:

[TrevorLenten]

Maybe I should compare the cost of the PO to STD cost when purchasers are creating the PO instead of waiting for the receipt to happen since it will get kicked back to purchasing anyway.

Thanks for the help Jason.

[gpayne (Greg Payne)]

I do this in two steps on the PO. I compare to standard ± 1% and require an exception code from a user code drop down PO.Update for the line.

Then in a DD on PODetail if an update happens with a reason code I email the details to the supply chain manager and the buyer.

But if you don’t want a reason you could just do the compare and email in the DD.

[TrevorLenten]

How do you perform the comparison ± 1%?

I think I know what I want to do I just don’t know how to best accomplish it. I was thinking to use code that retrieves STD cost, stores that as a variable then do the same for the PO and then perform some math, but this requires me to learn more about C#, which my knowledge is rudimentary at best.

[gpayne (Greg Payne)]

I am out today, but i will send you my routines later.

[gpayne (Greg Payne)]

This is two bpms that you may combine into the data directive that does the check for over 1000 variance and sends the email. This is an inhouse email so it is not fancy.

On PO Update require reason.

/* reason required.*/
object MESSAGE_ERR = null;
Erp.Tables.PartCost PartCost;
foreach (var ttPODetailRow in (from ttPODetail_Row in ds.PODetail
                                     where ttPODetail_Row.Updated() select ttPODetail_Row))
{
 
    var reason = ttPODetailRow["ShortChar01"].ToString();
    Ice.Diagnostics.Log.WriteEntry(" PO Reason >>" + reason + "<<");
    if(String.IsNullOrEmpty(reason))
    {
    var stdmtlCost = Db.PartCost.Where(PartCost_Row=> PartCost_Row.Company == ttPODetailRow.Company && PartCost_Row.PartNum == ttPODetailRow.PartNum).Select(PartCost_Row=> PartCost_Row.StdMaterialCost).FirstOrDefault();
    if (stdmtlCost != null)
    {
        
        if ((ttPODetailRow.UnitCost > (stdmtlCost * 1.01m)) && String.IsNullOrEmpty(reason))
        {
          CallContext.Current.ExceptionManager.AddBLException("PO Reason is required for this line over std");
        }
        if (ttPODetailRow.UnitCost < (stdmtlCost - (stdmtlCost * .01m)) && String.IsNullOrEmpty(reason))
        {
            string Msg = string.Empty;
            if (this.callContextClient.CurrentUserId == "me")
            {
                Msg = " cost " + System.Convert.ToString(ttPODetailRow.UnitCost) + " less than " + System.Convert.ToString(stdmtlCost - (stdmtlCost * .01m)) + "PO Reason is required for this line under std";
            }
            else
            {
                Msg = "PO Reason is required for this line under std";
            }
            CallContext.Current.ExceptionManager.AddBLException(Msg);
        }
     }  
    }
    
}

Data Directive on PODetail
Condition so it does not run from Receipt Entry or Inspection Processing.

custom Code in In transaction, but running Async, so you can check the exiting data versus the new data.

/* email Po reason v2  */

Ice.Diagnostics.Log.WriteEntry($"Starting Email PO Reason Called From {callContextClient.AssemblyName}");
double TotQty = 0;
decimal TotStd = decimal.Zero;
decimal CostAtStd = decimal.Zero;
decimal CostAtPO = decimal.Zero;
decimal POQty = decimal.Zero;
decimal STDVariance = decimal.Zero;
decimal STDMaterialCost = decimal.Zero;
decimal UnitDiff = decimal.Zero;

string EmailAddress = string.Empty;
string UserName = string.Empty;
string UserID = string.Empty;
string vFrom = string.Empty;
string vTo = string.Empty;
string vCC = string.Empty;
string vSubject = string.Empty;
string vBody = string.Empty;
string EMAIL_From = string.Empty;
string EMAIL_To = string.Empty;
string EMAIL_CC = string.Empty;
string EMAIL_Subject = string.Empty;
string EMAIL_Text = string.Empty;

string reason = string.Empty;
string reasonDesc = string.Empty;
string oldReason = string.Empty;

decimal oldUnitCost = decimal.Zero;

var OLDPO = ttPODetail.FirstOrDefault(r => r.RowMod == "");

if (OLDPO != null)

{
	oldUnitCost = OLDPO.UnitCost;
	oldReason = OLDPO.UDField<System.String>("ShortChar01", throwIfNull:false); // (string)OLDPO["ShortChar01"];
}
else
{
	oldUnitCost = 0m;
	oldReason = "";
}



var ttPODetailRow = ttPODetail.Where(t => !t.Unchanged()).FirstOrDefault();
{

	reason  = (string)ttPODetailRow["ShortChar01"];

	if (ttPODetailRow.UnitCost != oldUnitCost || (reason != oldReason && !string.IsNullOrEmpty(reason)))
	{
		Ice.Diagnostics.Log.WriteEntry($"DEBUG - PO {ttPODetailRow.PONUM.ToString()}");


		TotQty = (from PartTran_Row in Db.PartTran
		where PartTran_Row.Company == callContextClient.CurrentCompany
		&& PartTran_Row.PartNum == ttPODetailRow.PartNum
		&& PartTran_Row.TranDate >= (System.Data.Entity.DbFunctions.AddDays(DateTime.Now,-365))
		&& PartTran_Row.TranType == "PUR-STK"
		select new {PartTran_Row.TranQty}).Sum(x=> (double?) x.TranQty) ?? 0;


		Ice.Diagnostics.Log.WriteEntry($"DEBUG - PO {ttPODetailRow.PONUM.ToString()} Email  Tot Qty {TotQty.ToString()}");

		var partCost = (from PartCost_Row in Db.PartCost
		where PartCost_Row.Company == ttPODetailRow.Company && PartCost_Row.PartNum == ttPODetailRow.PartNum
		select new {PartCost_Row.StdBurdenCost, PartCost_Row.StdLaborCost, PartCost_Row.StdMaterialCost, PartCost_Row.StdMtlBurCost, PartCost_Row.StdSubContCost}).FirstOrDefault();


		if (partCost != null)
		{
			CostAtStd = (decimal)TotQty * partCost.StdMaterialCost;
			TotStd = partCost.StdBurdenCost + partCost.StdLaborCost + partCost.StdMaterialCost + partCost.StdMtlBurCost + partCost.StdSubContCost;
			STDMaterialCost = partCost.StdMaterialCost;
		}
		CostAtPO = (decimal)TotQty * ttPODetailRow.UnitCost;
		UserID = callContextClient.CurrentUserId;
		var userFile = (from UserFile_Row in Db.UserFile
		where UserFile_Row.DcdUserID == UserID //&& UserFile_Row.EMailAddress != string.Empty
		select new {UserFile_Row.EMailAddress,UserFile_Row.Name}).FirstOrDefault();
		if (userFile != null)
		{
			EMAIL_From = "<" + userFile.EMailAddress + ">";
			UserName = userFile.Name;
		}
		else
		{
			EMAIL_From = "<" + "info@domain.com" + ">";
			UserName = "Not Found";
		}
		var vendorID = (from Vendor_Row in Db.Vendor
		where Vendor_Row.Company == ttPODetailRow.Company && Vendor_Row.VendorNum == ttPODetailRow.VendorNum
		select Vendor_Row.VendorID).FirstOrDefault();

		reason = (string)ttPODetailRow["ShortChar01"];

		var UDCodes = (from UDCodes_Row in Db.UDCodes
		where UDCodes_Row.Company == ttPODetailRow.Company && reason == UDCodes_Row.CodeID
		select UDCodes_Row).FirstOrDefault();

		if(UDCodes != null)
		{
			reasonDesc = UDCodes.LongDesc;
		}




		STDVariance = CostAtPO - CostAtStd;

		Ice.Diagnostics.Log.WriteEntry("DEBUG Email  -  PO Quantity  = " + ttPODetailRow.XOrderQty.ToString());

		vBody = "Cost not Standard on " + ttPODetailRow.PartNum + " by " + UserName
		+ "\n\n\tVendor " + vendorID
		+ "\n\n\tItem " + ttPODetailRow.PartNum + "    " + ttPODetailRow.LineDesc
		+ "\n\n\tQty    " + (ttPODetailRow.XOrderQty).ToString("#,##0")
		+ "\n\n\tMA Cost \t" + STDMaterialCost.ToString("#,##0.00###")
		+ "\tExtended Cost " + (STDMaterialCost * ttPODetailRow.XOrderQty).ToString("C")
		+ "\n\tPO Cost \t" + ttPODetailRow.UnitCost.ToString("#,##0.00###")
		+ "\tExtended Cost " + (ttPODetailRow.UnitCost * ttPODetailRow.XOrderQty).ToString("C")
		+ "\n\n\t12 Months Usage " + TotQty.ToString("#,##0")
		+ "\n\n\tImpact $ " + (CostAtPO - CostAtStd).ToString("C")
		+ "\n\n\tReason: " + reasonDesc
		+ "\n\n\tTotal StandardCost: " + TotStd.ToString()
		+ "\n\n\tPurchase Order Number: " + ttPODetailRow.PONUM.ToString()
		+ "\tLine Number: " + ttPODetailRow.POLine.ToString();

		Ice.Diagnostics.Log.WriteEntry("DEBUG Email  - Cost not Standard on " + ttPODetailRow.PartNum + " by " + UserName);



		Ice.Diagnostics.Log.WriteEntry("DEBUG - OLD PO Cost = " + oldUnitCost.ToString());

		if (callContextClient.CurrentUserId == "me")
		{
			vTo = "<me@domain.com>;";
			vCC = "<info@domain.com>;";
		}
		else
		{
			vTo = "<scmanager@domain.com>;";
			vCC = "<info@domain.com>;";

			//if ((CostAtPO - CostAtStd) > 1000)
			//{
			//    vCC = vCC + "<sales@domain.com>;";
			//}
		}
		Ice.Diagnostics.Log.WriteEntry("DEBUG Email  -  vTO  = " + vTo);

		var mailer = this.GetMailer(async: true);
		var message = new Ice.Mail.SmtpMail();

		var from = EMAIL_From;
		var cc = vCC + EMAIL_From;
		var subject = "Epicor Reason: " + reasonDesc;



		if ((ttPODetailRow.UnitCost == oldUnitCost && reason == oldReason) || STDMaterialCost == ttPODetailRow.UnitCost)
		{
			Ice.Diagnostics.Log.WriteEntry("DEBUG No Email Sent");
		}
		else
		{
			message.SetFrom(from);
			message.SetTo(vTo);
			message.SetCC(cc);
			message.SetSubject(subject);
			message.SetBody(vBody);
			mailer.Send(message);
			Ice.Diagnostics.Log.WriteEntry("DEBUG Email Sent");
		}
	}
}

Ice.Diagnostics.Log.WriteEntry("Exiting Email PO Reason");

[TrevorLenten]

Thank you for sharing Greg.