Sales Commission

Just some questions how does the epicor user community pay out commissions to sales reps we currently use an access database and generate payment entry manually. I would like to move away from that practice as we have had this database since vantage six and would like to get rid of this and use our epicor 10 environment to do that like it should.

but not sure what it should look like either so open to suggestions.

We go outside Epicor as well… Our commission rate structure gets complicated based on annual sales target tiers. If there are best practices that would allow us to bring it back inside Epicor, that would be a solid 10 hours a week time savings for us.

you can attach a commission rate to a sales person and then put that salesperson on the quote/order. this only works if its a flat rate.
if you get into percentages that are based on some other factor, you could build a BPM that determines that factor at the time of sales order entry and puts it in the Rate area of sales order header.
another option is just use an ssrs report and run it when ever you run your commissions.

2 Likes

@Craig - Interesting idea on the BPM to determine the commission rate. I’ll have to think more about that!

We configure the Commission via a UD05 Table, it is connected to the Customer XPartNum (Cross-Reference) UI for managing it.

Then there are 2 BPMs that ultimately calculate, the payout.

  • Method Directives/CashGrp.PrePostGroup.PRE.SetCommissionValues
  • Method Directives/InvcGrp.PrePostInvoices.PRE.GetCommissionAmount

These BPMs were implemented before my time by @Jason_Woods and it looks like he knows alot more about Sales Rep Commissions =)

using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{

	var ihRows = (from ih in Db.InvcHead.With(LockHint.UpdLock)
			where ih.Company == Session.CompanyID && (decimal)ih["Number03"] == 0 && ih.DocInvoiceBal == 0
		select ih);

	foreach (var ihRow in ihRows)
	{
		comTotal = 0;

		var idRows = (from id in Db.InvcDtl.With(LockHint.NoLock)
						join pg in Db.ProdGrup.With(LockHint.NoLock)
							on new { id.Company, id.ProdCode, CheckBox01 = false } equals new { pg.Company, pg.ProdCode, CheckBox01 = pg.CheckBox01 }
						where id.Company == ihRow.Company && id.InvoiceNum == ihRow.InvoiceNum
					select id);

		foreach (var idRow in idRows) {
			comTotal += idRow.DocExtPrice;
		}

		ihRow["Number03"] = comTotal;
		//Db.InvcHead.Update(InvcHead);
	}

}


//
//  CODE 2
//
/* Set Values for Commissions
Jason Woods (jawoods@epicor.com)
6/4/13
InvcHead.Number01 = Adjustments
InvcHead.Number02 =
InvcHead.Number03 = Commissionable Amount
InvcHead.Number04 = Cash Receipts
*/


decimal adjustment = decimal.Zero;

// Transaction Scope
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{

	var cashRows =
		(from cd in Db.CashDtl.With(LockHint.NoLock)

			join ih in Db.InvcHead.With(LockHint.UpdLock)
				on new { cd.Company, cd.InvoiceNum, CheckBox20 = false } equals new { ih.Company, ih.InvoiceNum, CheckBox20 = ih.CheckBox20 }

			where cd.Company == Session.CompanyID && cd.InvoiceNum == ih.InvoiceNum && cd.GroupID == groupID

			orderby cd.TranDate
		select new { cd, ih }
		).ToList();

	foreach (var cashRow in cashRows)
	{

		var ihRow = cashRow.ih;


		// Set SalesRep and Commission for Programs

		var customerRow =
				(from c in Db.Customer.With(LockHint.NoLock)
				where (c.Company == Session.CompanyID && c.CustNum == ihRow.CustNum) && (decimal)c["Number01"] > 0 && c.GroupCode == "DS"
				select new {
					c.SalesRepCode,
					Number01 = c["Number01"]
				}).FirstOrDefault();

		if (customerRow != null)
		{
			ihRow.SalesRepList = customerRow.SalesRepCode;
			ihRow["Number02"] = customerRow.Number01;
		}


		// Set Adjustment Amount
		adjustment = 0;
		ihRow["Number04"] = 0; // Cash Rcpt
		ihRow["Number05"] = 0; // Misc Chrg

		var imRows =
				(from im in Db.InvcMisc.With(LockHint.NoLock)
				where im.Company == Session.CompanyID && im.InvoiceNum == ihRow.InvoiceNum
				select new { DocMiscAmt = (decimal)im.DocMiscAmt});

		foreach (var imRow in imRows)
		{
			ihRow["Number05"] = (decimal)ihRow["Number05"] - imRow.DocMiscAmt;
		}


		//
		// Get All CashDtl's regardless of group
		//
		var cashDetails =
			(from cd in Db.CashDtl.With(LockHint.NoLock)
				where cd.Company == Session.CompanyID && cd.InvoiceNum == ihRow.InvoiceNum
			select new {
				cd.TranType,
				cd.DocTranAmt
			}).ToList();

		foreach (var cdRow in cashDetails)
		{
			if (cdRow.TranType == "ADJUST") {
				adjustment += cdRow.DocTranAmt;
			}
			else if (cdRow.TranType == "CMemo") {
				ihRow["Number04"] = (decimal)ihRow["Number04"] + cdRow.DocTranAmt;
			}
		}

		if ((decimal)ihRow["Number04"] > (decimal)ihRow["Number03"]) {
			ihRow["Number04"] = ihRow["Number03"]; /* Remove Misc Charges from Cash Rcpt */
		}


		var idRows = (from id in Db.InvcDtl.With(LockHint.NoLock)

						join pg in Db.ProdGrup.With(LockHint.NoLock)
							on new { id.Company, id.ProdCode, CheckBox01 = true } equals new { pg.Company, ProdCode = pg.PriceGroupCode, CheckBox01 = pg.CheckBox01 }

						where id.Company == ihRow.Company && id.InvoiceNum == ihRow.InvoiceNum /*&& ihRow["Number02"] == 0*/
					select new { DocExtPrice = id.DocExtPrice});

		foreach (var idRow in idRows) {
			adjustment += (decimal)idRow.DocExtPrice;
		}

		ihRow["Number01"] = adjustment;

		if ( (ihRow.DocInvoiceAmt - ihRow.DocInvoiceBal) >= (decimal)ihRow["Number03"] && (decimal)ihRow["Number03"] != 0 )
		{
			ihRow["CheckBox20"] = true;
		}
		else {
			ihRow["CheckBox20"] = false;
		}


		// Set Last Cash Receipt
		if (cashRow.cd.TranType != "ADJUST" && cashRow.cd.TranType != "CMemo" && (bool)ihRow["CheckBox20"] == false)
		{
			if ((DateTime?)ihRow["Date01"] == null) {
				ihRow["Date01"] = cashRow.cd.TranDate;
			}

			if ((DateTime?)ihRow["Date01"] < cashRow.cd.TranDate) {
				ihRow["Date01"] = cashRow.cd.TranDate;
			}
		}

	}

	Db.Validate();
	txScope.Complete();

}
1 Like

Wow. There is some older code. I remember reading ABL code I wrote when I first started compared to when E10 was entering the scene. It was a little embarrassing at first until I realized that simply means I got better!
Glad to know my code is still useful!

2 Likes