Does anyone have a sample E10 bpm alert that is like the custom alert from E9? I have 20 to convert and I used the abl to C code converter on the first one and it now compiles, but throws an error on execution, so there is something I am missing in the process.
This one is on PODetail, but brings in UDCodes, PartTran, PartCost and UserFile.
Thanks
Greg
Show us what you got and we can fix it
Thanks, Here is the routine.
/* epicor user */
decimal TotQty = decimal.Zero;
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 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;
Erp.Tables.PODetail OLDPO;
Erp.Tables.PartTran PartTran;
Erp.Tables.PODetail PODetail = null;
Ice.Tables.UD100 UD100;
Erp.Tables.PartCost PartCost;
Erp.Tables.UserFile UserFile;
Erp.Tables.Vendor Vendor;
Ice.Tables.UDCodes UDCodes;
var mailer = this.GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
foreach (var PartTran_iterator in (from PartTran_Row in Db.PartTran
where string.Compare(PartTran_Row.Company, PODetail.Company, true) == 0
&& string.Compare(PartTran_Row.PartNum, PODetail.PartNum, true) == 0
&& PartTran_Row.TranDate >= (DateTime.Now.AddDays(-365))
&& PartTran_Row.TranType.StartsWith("PUR-STK", StringComparison.OrdinalIgnoreCase)
select PartTran_Row))
{
PartTran = PartTran_iterator;
TotQty = TotQty + PartTran.TranQty;
}
Ice.Diagnostics.Log.WriteEntry("DEBUG - In Test Email Tot Qty = " + TotQty.ToString());
PartCost = (from PartCost_Row in Db.PartCost
where string.Compare(PartCost_Row.Company, PODetail.Company, true) == 0 && string.Compare(PartCost_Row.PartNum, PODetail.PartNum, true) == 0
select PartCost_Row).FirstOrDefault();
if (PartCost != null)
{
CostAtStd = TotQty * PartCost.StdMaterialCost;
TotStd = PartCost.StdBurdenCost + PartCost.StdLaborCost + PartCost.StdMaterialCost + PartCost.StdMtlBurCost + PartCost.StdSubContCost;
STDMaterialCost = PartCost.StdMaterialCost;
}
CostAtPO = TotQty * PODetail.UnitCost;
UserFile = (from UserFile_Row in Db.UserFile
where UserFile_Row.DcdUserID == Session.UserID
select UserFile_Row).FirstOrDefault();
if (!String.IsNullOrEmpty(UserFile.EMailAddress.Trim()))
{
EMAIL_From = "<" + UserFile.EMailAddress + ">";
}
else
{
EMAIL_From = "<einfo@compulink.com>";
}
Vendor = (from Vendor_Row in Db.Vendor
where string.Compare(Vendor_Row.Company, PODetail.Company, true) == 0 && Vendor_Row.VendorNum == PODetail.VendorNum
select Vendor_Row).FirstOrDefault();
UDCodes = (from UDCodes_Row in Db.UDCodes
where string.Compare(UDCodes_Row.Company, PODetail.Company, true) == 0 && string.Compare((string)PODetail["ShortChar01"] ,UDCodes_Row.CodeID
,true)==0 select UDCodes_Row).FirstOrDefault();
STDVariance = CostAtPO - CostAtStd;
Ice.Diagnostics.Log.WriteEntry("DEBUG Email - PO Quantity = " + PODetail.XOrderQty.ToString());
vBody = "Cost above Material Standard on " + PODetail.PartNum + " by " + UserFile.Name + "\n\n\tVendor " + Vendor.VendorID + "\n\n\tItem " + PODetail.PartNum + " " + PODetail.LineDesc + "\n\n\tQty " + System.Convert.ToString(PODetail.XOrderQty).Trim() + "\n\n\tMA Cost \t" + PartCost.StdMaterialCost.ToString("C") + "\tExtended Cost " + (PartCost.StdMaterialCost * PODetail.XOrderQty).ToString() + "\n\tPO Cost \t" + PODetail.UnitCost.ToString("C") + "\tExtended Cost " + (PODetail.UnitCost * PODetail.XOrderQty).ToString() + "\n\n\t12 Months Usage " + TotQty.ToString() + "\n\n\tImpact $ " + (CostAtPO - CostAtStd).ToString() + "\n\n\tReason: " + UDCodes.LongDesc + "\n\n\tTotal StandardCost: " + TotStd.ToString() + "\n\n\tPurchase Order Number: " + PODetail.PONUM.ToString() + "\tLine Number: " + PODetail.POLine.ToString();
OLDPO = (from OLDPODetail_Row in Db.PODetail
where string.Compare(OLDPODetail_Row.Company, PODetail.Company, true) == 0 && OLDPODetail_Row.SysRowID == PODetail.SysRowID
select OLDPODetail_Row).FirstOrDefault();
if (string.Compare(Session.UserID ,"gpay01",true)==0)
{
vTo = "<gpayne@compulink.com>;";
vCC = "<einfo@compulink.com>;";
}
else
{
vTo = "<mgtrw@compulink.com>;";
vCC = "<einfo@compulink.com>;";
if ((CostAtPO - CostAtStd) > 50)
{
vTo = vTo + "<mgtss@compulink.com>;";
}
if ((CostAtPO - CostAtStd) > 1000)
{
vCC = vCC + "<mgtbl@compulink.com>;";
}
}
Ice.Diagnostics.Log.WriteEntry("DEBUG Email - vTO = " + vTo);
message.SetTo(vTo);
var cc = vCC + EMAIL_From;
message.SetCC(cc);
var subject = "Epicor Reason: " + UDCodes.CodeDesc;
message.SetSubject(subject);
EMAIL_Text = vBody;
if ((PODetail.UnitCost == OLDPO.UnitCost && string.Compare((string)PODetail["ShortChar01"] ,(string)OLDPO["ShortChar01"],true)==0) || PartCost.StdMaterialCost >= PODetail.UnitCost)
{
message.SetBody(vBody);
mailer.Send(message);
}
Can you also include the exception
Yes, I am currently adding a UD for POEntry and will send as soon as I can test again.
Thanks. I added Entry Framework and used DbFunctions.AddDays and the error went away, but the code is not firing. Not even the first message is writing to the log. I found the source files on the server and noticed the standard data directive which is where the conversion put the routine is post processing and I need to show old and changed values. Can I do an alert in transaction or do I have to fill out the bpmcontext fields and pass them. For this one it is only two fields, but some of them compare a bunch of fields. I am going to try moving to intransaction.
@Aaron_Moreng linq and updating the queries to be EF6 fixed it. I got how to use the old values from @knash and pieces from @rbucek, @jgiese.wci and @ckrusen to get it all to work, so thank to all.
The final working code is below.
Greg
/* epicor e10 bam with old values */
/* add reference for EntityFrameWork and EntityFrameWorkSqlserver */
decimal TotQty = decimal.Zero;
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;
Erp.Tables.PODetail OLDPO;
Erp.Tables.PartTran PartTran;
Erp.Tables.PODetail PODetail;
Ice.Tables.UD100 UD100;
Erp.Tables.PartCost PartCost;
Erp.Tables.UserFile UserFile;
Erp.Tables.Vendor Vendor;
Ice.Tables.UDCodes UDCodes;
Ice.Diagnostics.Log.WriteEntry("DEBUG - In Email PO Reason");
var ttPODetailRow = ttPODetail.FirstOrDefault();
{
foreach (var PartTran_iterator in (from PartTran_Row in Db.PartTran
where PartTran_Row.Company == callContextClient.CurrentCompany
&& string.Compare(PartTran_Row.PartNum, ttPODetailRow.PartNum, true) == 0
&& PartTran_Row.TranDate >= (System.Data.Entity.DbFunctions.AddDays(DateTime.Now,-365))
&& string.Compare(PartTran_Row.TranType,"PUR-STK",true) == 0
select PartTran_Row))
{
PartTran = PartTran_iterator;
TotQty = TotQty + PartTran.TranQty;
}
Ice.Diagnostics.Log.WriteEntry("DEBUG - In Test Email Tot Qty = " + TotQty.ToString());
PartCost = (from PartCost_Row in Db.PartCost
where string.Compare(PartCost_Row.Company, ttPODetailRow.Company, true) == 0 && string.Compare(PartCost_Row.PartNum, ttPODetailRow.PartNum, true) == 0
select PartCost_Row).FirstOrDefault();
if (PartCost != null)
{
CostAtStd = TotQty * PartCost.StdMaterialCost;
TotStd = PartCost.StdBurdenCost + PartCost.StdLaborCost + PartCost.StdMaterialCost + PartCost.StdMtlBurCost + PartCost.StdSubContCost;
STDMaterialCost = PartCost.StdMaterialCost;
}
CostAtPO = TotQty * ttPODetailRow.UnitCost;
UserID = Session.UserID;
UserFile = (from UserFile_Row in Db.UserFile
where UserFile_Row.DcdUserID == UserID //&& UserFile_Row.EMailAddress != string.Empty
select UserFile_Row).FirstOrDefault();
if (UserFile != null)
{
EMAIL_From = "<" + UserFile.EMailAddress + ">";
}
else
{
EMAIL_From = "<" + "einfo@domain.com" + ">";
}
Vendor = (from Vendor_Row in Db.Vendor
where string.Compare(Vendor_Row.Company, ttPODetailRow.Company, true) == 0 && Vendor_Row.VendorNum == ttPODetailRow.VendorNum
select Vendor_Row).FirstOrDefault();
var reason = (string)ttPODetailRow["ShortChar01"];
UDCodes = (from UDCodes_Row in Db.UDCodes
where string.Compare(UDCodes_Row.Company, ttPODetailRow.Company, true) == 0 && string.Compare(reason,UDCodes_Row.CodeID
,true)==0 select UDCodes_Row).FirstOrDefault();
STDVariance = CostAtPO - CostAtStd;
Ice.Diagnostics.Log.WriteEntry("DEBUG Email - PO Quantity = " + ttPODetailRow.XOrderQty.ToString());
vBody = "Cost above Material Standard on " + ttPODetailRow.PartNum + " by " + UserFile.Name
+ "\n\n\tVendor " + Vendor.VendorID + "\n\n\tItem " + ttPODetailRow.PartNum + " " + ttPODetailRow.LineDesc + "\n\n\tQty " + (ttPODetailRow.XOrderQty).ToString("#,##0") + "\n\n\tMA Cost \t" + PartCost.StdMaterialCost.ToString("C") + "\tExtended Cost " + (PartCost.StdMaterialCost * ttPODetailRow.XOrderQty).ToString("C") + "\n\tPO Cost \t" + ttPODetailRow.UnitCost.ToString("C") + "\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: " + UDCodes.LongDesc + "\n\n\tTotal StandardCost: " + TotStd.ToString() + "\n\n\tPurchase Order Number: " + ttPODetailRow.PONUM.ToString() + "\tLine Number: " + ttPODetailRow.POLine.ToString();
OLDPO = (from OLDPODetail_Row in ttPODetail
where OLDPODetail_Row.RowMod == ""
select OLDPODetail_Row).FirstOrDefault();
Ice.Diagnostics.Log.WriteEntry("DEBUG - OLD PO Cost = " + OLDPO.UnitCost.ToString());
if (string.Compare(Session.UserID ,"gpay01",true)==0)
{
vTo = "<gpayne@domain.com>;";
vCC = "<einfo@domain.com>;";
}
else
{
vTo = "<rw@domain.com>;";
vCC = "<einfo@domain.com>;";
if ((CostAtPO - CostAtStd) > 50)
{
vTo = vTo + "<ss@domain.com>;";
}
if ((CostAtPO - CostAtStd) > 1000)
{
vCC = vCC + "<bl@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: " + UDCodes.CodeDesc;
if ((ttPODetailRow.UnitCost == OLDPO.UnitCost && string.Compare((string)ttPODetailRow["ShortChar01"] ,(string)OLDPO["ShortChar01"],true)==0) || PartCost.StdMaterialCost >= ttPODetailRow.UnitCost)
{
}
else
{
message.SetFrom(from);
message.SetTo(vTo);
message.SetCC(cc);
message.SetSubject(subject);
message.SetBody(vBody);
mailer.Send(message);
}
}
Where do I find the log where the above entry is added?
The flight data recorder as @Bart_Elia calls it aka event viewer on the iis server Epicor App Server.
yes, In-Tran will capture the values before Update/change, so you can cast them into callcontex variables then use them at Std
I thought I was going to have to go that route, but @MontyMan posted that you could use RowMod = “” to capture the before values, so I didn’t have to use callcontext. It makes sense that Epicor has the before so they can compare and give you the row has been modified message if the data has changed.