I’ve tried to find another post to help me, but I’m still stuck. Are there any clear paths to get from a POST POSugg.Generate BPM to update a PO.Header field using the Epicor tools?
We have a UD field in POHeader to store the PayAccount number from OrderHed.PayAccount, but when using a POSugg.Generate BPM cannot figure out how to pass it.
The closest solution so far is that when the POSugg.Generate (post) runs, we set callContextBpmNumber01 = ttSugPoDtlRow.OrderNum. Then, a POHeader data directive checks to see if a callContextBpmNumber01 is more than zero and if so passes that value to an “update table by query” to a) query the orderhed table to get the payaccount, then b) update the poheader table ud field with that payaccount number.
This works fine, as long as POSugg.Generate has only ONE buy line checked. The second another buy option is checked, an error comes up that says “the table ttSugPODtl has more than one record”. I get why this error occurs (only one callContextBpmNumber01 for the call), but now I’m really stuck… we were thinking this was the best way to pass the order number onto the POHeader data directive, but now not sure.
I could update the POHeader table directly with a datadirective on add new row, but the PORel entries (where the ordernum is) aren’t populated when add new row runs.
Are there any other BOs that I could try to use? POSugg.Generate seems to be the only one to get me where I need to go.
We’re using an in-transaction data directive on POHeader with a clause in the BPM to validate the callcontextclient.assemblyname. It is only setting a boolean value though so not pulling any data from the suggestion
Yes, that would work if I was only trying to know what was fired, but in my case I need the order number passed so that I can query the OrderHed.PayAccount or the PORel.PayAccount (which isn’t set until after the POHeader is touched)…
Thanks though. This whole project is pushing me into new areas of Epicor. Hopefully I can land on a solution.
@Adam I am sort of doing what you are doing with the callcontext, but also adding a step to stash the suggestion data pre processing in a UDxx table for matching up along with the fields I am passing. I use the guid hack from Jose’s post below to identify all of the newly created POs by buyer.
In the UD table keyed to the buyer and SugNum I use vendornum, partnum, duedate and quantity to find the release added post processing on POSugg.Generate and update its header.
You could also put the OrderHed pay account in the suggestion CommentText in POSugg.GetRowsPlant and then slide to the PO Header or Release from the PODetail.CommentText after the lines and releases are made.
Alright, Alright. I just can’t allow you to do Data Directives and Hacks. Especially for my dear friend @gpayne!!!
I’ll give you a solution that supports all the Generate scenarios. Different Buyers, Multiple PO’s etc… Consider this my 2022 gift to the community, it wasn’t easy. Might even have some tech debt in it.
A Single Method Directive to rule them all…
Basically we added Project ID, LockQty and LockDate columns to PO Suggestion Entry and then pass it down to PORel and ProjectID to POHead.
Erp.BO.POSugg.Generate POST Directive
Example shows changing applying columns to releases and 1 to header, but the code is written in a way to easily add changes to line as well.
Supports all scenarios such as when PO Suggestion generate a Single PO, Multiple PO’s, Multiple Lines, Multiple Release etc…
Supports PartClass Split Setting.
Supports Appending New Lines / Rels to Existing PO
// Pass Values to Newly created POs
//
// NOTES:
// Epicor creates a new PO when any of the following changes:
// Grouping Algorithm:
//
// New POHead GroupBy = BuyerID, VendorNum, PurPoint, ShipViaCode, TermsCode, FOB
// New PODtl GroupBy = PartNum, IUM, PUM
// New PORel GroupBy = SugNum processed
//
// Helpers
Func<string, string> GetProjectContractID = (sProjectID) =>
{
return Db.Project.Where(x => x.Company == Session.CompanyID && x.ProjectID == sProjectID)
.Select(s => s.ContractID).DefaultIfEmpty("").First();
};
Func<string, bool> IsValidProjectID = (sProjectID) =>
{
return Db.Project.Any(x => x.Company == Session.CompanyID && x.ProjectID == sProjectID);
};
Func<string, bool> IsValidContractID = (sContractID) =>
{
return !string.IsNullOrEmpty(sContractID)
&& Db.PlanContractHdr.Any(x => x.Company == Session.CompanyID && x.ContractID == sContractID && x.Active == true);
};
Func<string, bool> IsPartInMaster = (sPartNum) =>
{
return Db.Part.Any(x => x.Company == Session.CompanyID && x.PartNum == sPartNum);
};
//
// Process Material Suggestions that have a pre-defined PONum
//
// In PO Suggestions you can specify a existing PONum and Line
// This will only append new releases
//
var ttSugPoDtlExistingPONum =
ttSugPoDtl.Where(w => w.SugType == "M" && w.PONUM != 0)
.GroupBy(g => new {
g.PONUM,
g.POLine
}).Select(r => r).ToList();
foreach (var sugRowx in ttSugPoDtlExistingPONum)
{
Ice.Diagnostics.Log.WriteEntry("---------------- Append to Existing PO Detail ---------------");
Ice.Diagnostics.Log.WriteEntry($"[ PODtl ] PONum: {sugRowx.Key.PONUM} - Line: {sugRowx.Key.POLine}");
int iMaxRelNum =
(from por in Db.PORel
where por.Company == Session.CompanyID
&& por.PONum == sugRowx.Key.PONUM
&& por.POLine == sugRowx.Key.POLine
select por.PORelNum
).Max();
//Ice.Diagnostics.Log.WriteEntry("iMaxRelNum: " + iMaxRelNum + " - sugRowCount: " + sugRowx.Count());
int relNum = iMaxRelNum - sugRowx.Count();
foreach (var sugRow in sugRowx)
{
relNum++;
var relRow =
(from por in Db.PORel
where por.Company == Session.CompanyID
&& por.PONum == sugRow.PONUM
&& por.POLine == sugRow.POLine
&& por.PORelNum == relNum
select por
).FirstOrDefault();
//Ice.Diagnostics.Log.WriteEntry("---------------- PORel ---------------");
Ice.Diagnostics.Log.WriteEntry($"[ PORel ] Line: {relRow.POLine} - Rel: {relRow.PORelNum} - DueDate: {relRow.DueDate?.ToShortDateString()} - RelQty: {relRow.RelQty}" +
$" - idxProjectID_c: {sugRow.UDField<string>("idxProjectID_c")} - LockDate_c: {sugRow.UDField<bool>("LockDate_c")} - LockQty_c: {sugRow.UDField<bool>("LockQty_c")}");
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
relRow.LockDate = sugRow.UDField<bool>("LockDate_c");
relRow.LockQty = sugRow.UDField<bool>("LockQty_c");
relRow.ProjectID = sugRow.UDField<string>("idxProjectID_c");
// Mimick Epicor Logic when changing Project ID
// Assign the default Planning Contract from Project. Do Not default if PartNum is not a master-Part
if (!string.IsNullOrEmpty(relRow.ProjectID))
{
string sProjectDefaultContractID = GetProjectContractID(relRow.ProjectID);
if (IsValidContractID(sProjectDefaultContractID) && IsPartInMaster(sugRow.POLinePartNum))
{
relRow.ContractID = sProjectDefaultContractID;
}
}
Db.Validate(relRow);
txScope.Complete();
}
}
}
//
// Process Material Suggestions that have a new PONum
//
// Group Suggestions by the Keys
//
var ttSugPoDtlGrouped =
ttSugPoDtl.Where(w => w.SugType == "M" && w.PONUM == 0)
.GroupBy(g => new {
g.BuyerID,
g.VendorNum,
g.PurPoint,
g.ShipViaCode,
g.TermsCode,
g.FOB
}).Select(r => r).ToList();
int iMaxPONum =
(from ph in Db.POHeader
where ph.Company == Session.CompanyID
select ph.PONum
).Max();
int iNextPONum = iMaxPONum - ttSugPoDtlGrouped.Count() - ttSugPoDtl.Where(w => w.SugType == "S" && w.PONUM == 0).Count();
foreach (var grp in ttSugPoDtlGrouped)
{
iNextPONum++;
Ice.Diagnostics.Log.WriteEntry("---------------- New POHead ---------------");
//Ice.Diagnostics.Log.WriteEntry($"iNextPONum: {iNextPONum}");
// Get the Next POHead that has been created
// We use several keys in the where for more accuracy
var headRow =
(from poh in Db.POHeader
where poh.Company == Session.CompanyID
&& poh.PONum >= iNextPONum
&& poh.BuyerID == grp.Key.BuyerID
&& poh.VendorNum == grp.Key.VendorNum
&& poh.PurPoint == grp.Key.PurPoint
&& poh.TermsCode == grp.Key.TermsCode
&& poh.ShipViaCode == grp.Key.ShipViaCode
&& poh.FOB == grp.Key.FOB
orderby poh.PONum descending
select poh
).FirstOrDefault();
Ice.Diagnostics.Log.WriteEntry($"[ POHead ] PONum: {iNextPONum} - BuyerID: {grp.Key.BuyerID} - TermsCode: {grp.Key.TermsCode} - ShipViaCode: {grp.Key.ShipViaCode}" +
$" - FOB: {grp.Key.FOB} - VendorNum: {grp.Key.VendorNum} - PurPoint: {grp.Key.PurPoint}");
if (headRow == null) {
Ice.Diagnostics.Log.WriteEntry($"[ Error ] PO Not Found! iNextPONum: {iNextPONum}");
return;
}
var grpGroupedByLine = grp.GroupBy(g => new { g.PartNum, g.IUM, g.PUM }).Select(r => r);
int lineNum = 0;
foreach (var sugRowx in grpGroupedByLine)
{
lineNum++;
//Ice.Diagnostics.Log.WriteEntry("---------------- PODetail ---------------");
Ice.Diagnostics.Log.WriteEntry($"Processing Keys... PartNum: {sugRowx.Key.PartNum} - IUM/PUM: {sugRowx.Key.IUM}/{sugRowx.Key.PUM}");
Ice.Diagnostics.Log.WriteEntry($"Looking for... PONum: {headRow.PONum}");
// Get the PO Line related to the suggestion PartNum
// that we are currently processing
// We are currently not writing anything to the POLine but we may in the future
var detailRow =
(from pod in Db.PODetail
where pod.Company == Session.CompanyID
&& pod.PONUM == headRow.PONum
&& pod.PartNum == sugRowx.Key.PartNum
&& pod.IUM == sugRowx.Key.IUM
&& pod.PUM == sugRowx.Key.PUM
select pod
).FirstOrDefault();
Ice.Diagnostics.Log.WriteEntry($"[ PODtl ] Line: {detailRow.POLine} - PartNum: {detailRow.PartNum} - IUM/PUM: {detailRow.IUM}/{detailRow.PUM}" +
$" - DueDate: {detailRow.DueDate?.ToShortDateString()}");
string OldCommentText = string.Empty;
int relNum = 0;
foreach (var sugRow in sugRowx)
{
relNum++;
//
// CHECK FOR SPLIT LOGIC
//
bool isSplit = Db.PartClass.Any(x => x.Company == Session.CompanyID && x.ClassID == sugRow.ClassID && x.SplitPOLine == true);
if (isSplit && relNum > 1 && OldCommentText != sugRow.CommentText)
{
Ice.Diagnostics.Log.WriteEntry("!!!! RELEASE SPLIT DETECTED !!!!");
Ice.Diagnostics.Log.WriteEntry($"CommentText: {sugRow.CommentText} - OldCommentText: {OldCommentText}");
lineNum++;
relNum = 1;
Ice.Diagnostics.Log.WriteEntry($"Fetching: {headRow.PONum} - Line: {lineNum}");
// Overwrite current detailRow
detailRow =
(from pod in Db.PODetail
where pod.Company == Session.CompanyID
&& pod.PONUM == headRow.PONum
&& pod.POLine == lineNum
select pod
).FirstOrDefault();
Ice.Diagnostics.Log.WriteEntry($"After Fetch...");
Ice.Diagnostics.Log.WriteEntry($"[ PODtl ] Line: {detailRow.POLine} - PartNum: {detailRow.PartNum} - IUM/PUM: {detailRow.IUM}/{detailRow.PUM}" +
$" - DueDate: {detailRow.DueDate?.ToShortDateString()}");
}
//
// PO Releases
//
var relRow =
(from por in Db.PORel
where por.Company == Session.CompanyID
&& por.PONum == detailRow.PONUM
&& por.POLine == detailRow.POLine
&& por.PORelNum == relNum
select por
).FirstOrDefault();
//Ice.Diagnostics.Log.WriteEntry("---------------- PORel ---------------");
Ice.Diagnostics.Log.WriteEntry($"[ PORel ] Line: {relRow.POLine} - Rel: {relRow.PORelNum} - DueDate: {relRow.DueDate?.ToShortDateString()} - RelQty: {relRow.RelQty}" +
$" - idxProjectID_c: {sugRow.UDField<string>("idxProjectID_c")} - LockDate_c: {sugRow.UDField<bool>("LockDate_c")} - LockQty_c: {sugRow.UDField<bool>("LockQty_c")} - SugNum: {sugRow.SugNum}");
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
relRow.LockDate = sugRow.UDField<bool>("LockDate_c");
relRow.LockQty = sugRow.UDField<bool>("LockQty_c");
relRow.ProjectID = sugRow.UDField<string>("idxProjectID_c");
// Mimick Epicor Logic when changing Project ID
// Assign the default Planning Contract from Project. Do Not default if PartNum is not a master-Part
if (!string.IsNullOrEmpty(relRow.ProjectID))
{
string sProjectDefaultContractID = GetProjectContractID(relRow.ProjectID);
if (IsValidContractID(sProjectDefaultContractID) && IsPartInMaster(detailRow.PartNum))
{
relRow.ContractID = sProjectDefaultContractID;
}
}
Db.Validate(relRow);
txScope.Complete();
} // using
// Store Comment Text for Split Detection
OldCommentText = sugRow.CommentText;
}
}
//
// Update POHead
// By Finding the Last Project used on a Release
//
string sLastRelProjectID =
(from pr in Db.PORel
where pr.Company == headRow.Company
&& pr.PONum == headRow.PONum
orderby
pr.POLine descending,
pr.PORelNum descending
select pr.ProjectID
).Take(1).DefaultIfEmpty("").First();
if (!string.IsNullOrEmpty(sLastRelProjectID))
{
Ice.Diagnostics.Log.WriteEntry($"Assigning ProjectID to POHead... {sLastRelProjectID}");
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
headRow.idxProjectID_c = sLastRelProjectID;
Db.Validate(headRow);
txScope.Complete();
}
}
}
@hkeric.wci This will definitely earn you some drinks at Insights. I did not realize that the PO suggestion still existed post generation. I could use this to remove the write and read from the UD table.
I did not realize that the PO suggestion still existed post generation. I could use this to remove the write and read from the UD table.