Use POSugg.Generate to update a field in POHeader?

hkeric.wci · July 7, 2022, 11:34pm

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();
		}
	}
}

Enjoy @hmwillett

Topic		Replies	Views
Updating PO Header based on PO Details Epicor ERP 10 bpm , customization	8	2915	February 29, 2020
SalesOrder.MasterUpdate Post Processing Epicor ERP 10 e10	25	3445	June 16, 2020
BPM for Generate PO from Suggestions Epicor ERP 9	4	1313	September 23, 2021
BO.POSugg.Generate Method Epicor ERP 10 bpm , customization	3	1710	August 30, 2021
Adding Attachments to PO Lines during PO Generation/Creation within New PO Suggestions Epicor ERP 10	43	6549	January 14, 2021

Use POSugg.Generate to update a field in POHeader?

Erp.BO.POSugg.Generate POST Directive

Related topics