I’m curious to hear if anyone else has implemented or thought about implementing a solution to apply pricing to the sales order based on the ship by date (aka req date) rather than the order date. In other words, instead of applying the prices from the price list that is active “now” (when I am entering the order), apply prices from the price list that will be active on the ship by date.
One approach is to override the various methods in order entry, like getlist, on changepartnum, on change qty, etc etc to repopulate the price list drop down (and force selection) of price list that will be active on the future ship date throughout the order entry process. I do not like this approach for many reasons.
User may end up fighting with the functionality without understanding what is going on, or may simply lock price and type in what they want subverting the whole thing anyway.
When entering an order, the ship by is not required at any level (head, dtl, or release) and is often entered later on. So now you are in the position of adding yet more bpms to trigger the pricing change when the date is updated.
The price is on the line BUT the real ship by date is on the release not the line, and there can be multiple releases for one line. So what happens when you change the date on one release and that forces a pricing change. But then you change the date on another release for the same line and it changes the pricing again? Or what happens when you ship out one release and then pricing changes on the line before you ship out the next? Now sales order pricing doesn’t match invoice pricing which will almost certainly cause somebody to try to figure out what is wrong down the road.
The ship by date on the release may not represent the date that the order is actually shipped, e.g. what if it ships early or late and ought to receive different pricing based on the actual ship date vs. the expected ship by date?
Another approach is to have a process or function that updates the pricing on all open sales orders every night (or whatever) based on the earliest ship date on the open releases. A lot of the same issues as noted above with this approach.
Also how do you notify each customer of the updated pricing on their orders? How to avoid sending multiple notifications for each individual change?
Would it be better to have it as kind of an updatable dashboard that can be manually run in “what if” mode before changes are applied to view order impact and potentially pick and choose which orders get updated?
Or what about overriding at the “get shipments” step and applying the pricing directly to the invoice at that moment, ignoring the sales order entirely? Again leaves us in a situation where the customer PO may not match the invoice. Even if this is conveyed ahead of time via T&Cs it will still cause an issue for their AP department. What if the customer has already paid a deposit, is the percentage of the deposit essentially revalued at the time of shipment?
If you’ve read my ramblings up to this point I would be thrilled to hear any of your thoughts on other ways to approach this. I feel like I can think of a billion reasons not to do this and not really any elegant solutions that don’t have a ton of downside, but at the same time I understand the business desire to do it (volatile cost/protect margin).
I am pretty sure that Epicor always uses the req date vs price list effective date, or at least always did in the past so I had to use lock price to make sure it did not change on every date change because we did use the price the date the order was taken.
We (unfortunately, in my opinion) do this with our sales orders.
We have a pre-processing BPM on SalesOrder.MasterUpdate which uses the Customer, Part #, and Ship By Date of each line on the order to override the selected price list. This fires any time the customer, part number, or ship by date was changed on a line.
We have a couple of things going for us that make this easier to manage:
Each customer will only have one or two price lists active at a time - each part sold to that customer will be on a single price list assigned to the customer, so there’s no need for the person entering the order to select a price list from the dropdown.
We primarily use separate lines when splitting ship dates - all of our pricing and discounts are based on long-term contracts rather than calculating price breaks on an order. We don’t have to worry about multiple releases on the line falling in different price lists.
Anyone entering a sales order has been trained on the entry process which includes notes about the price list changing when the ship date changes. We also have notifications that go to our sales managers when a price is locked since that should only happen for a handful of customers. We also require that lines have a ship date entered based on the customer’s request date, which can be updated later.
Our sales team has an updatable dashboard they use whenever price lists are going to change to update open orders that won’t ship before the change date. This usually happens each quarter, so starting ~1 week before the end of the quarter, they’ll pull open orders, figure out which ones definitely won’t ship by the 1st of the next month, and update those to the new ship dates - which then automatically updates the pricing. As they get closer to the end of the month, they repeat that to get everything moved to the new ship dates.
a. Sales is communicating this to customers themselves, using an Open Orders report we have that shows the old vs new pricing.
Sales drives any early shipments so they will have already updated the ship by date before we ship and invoice the order, so it will have the correct pricing.
I would be thrilled if we decided tomorrow to change back to locking pricing at order entry, but I don’t see that happening any time soon for the reasons you stated.
Wow it is so amazing to hear that somebody else has actually implemented this.
I have questions:
Does your update trigger whenever they update the req date on the release? the line? or both?
How are you identifying old vs. new pricing on your open orders report? Are you capturing each individual price change on a sales order?
How do you update the price on a sales order when prices are loaded AFTER the req date is set. For example, an order is entered today (August 25) and set to ship September 1 but you don’t load pricing for September 1 until tomorrow. Does loading the price list trigger the pricing to update or?
It only updates when changing the date on the line. Our sales team has traditionally ignored the Releases tab in Order Entry and do almost everything at the Line level.
The report is run before the update is made and pulls current order price and the most recent price list price. So if we run the report today, it will show the price currently on the order and then the price on the price list that starts 9/1. If there’s no “future” price list, the line just doesn’t show in the report.
The sales team will usually use their updatable dashboard to change the date +/- one day to trigger the BPM after they’ve loaded new pricing. So they would change that order to ship 9/2, then update again to ship 9/1.
What would you say are the biggest problems you run into when doing this? Is there anything that continually comes up as an issue or complaint or something you have to fix for them?
It took a couple of months of tweaking and adjustments to make sure everything lined up correctly, but we have been pretty stable on this for a few years now. We’re lucky that our head of sales is really familiar with Epicor and understands how this customization works, but that also means any small issues they have don’t usually make it to me.
My biggest complaint with it is that in order to make this a manageable update we had to lock in some less-than-ideal process as standard work, like never manually updating releases and only using 1 release per line. I think in order to stay flexible with your order entry process, you would need to make this more complex which will mean weird edge cases like you asked about above.
This is the primary BPM that does the override - the fields we have to update were all found by logging and trial and error, and don’t do anything with multiple currencies or taxes. The code could also use a refactor - it’s one of our older BPMs we’ve had in place since when we upgraded to E10. This code runs after a couple of condition blocks checking if part, customer, or ship date changed on the line.
SalesOrder.MasterUpdate.PreProcessing
// Go over each detail that is still open
foreach (var dtl in (from dtl_row in ttOrderDtl where dtl_row.OpenLine == true && (dtl_row.RowMod == IceRow.ROWSTATE_ADDED || dtl_row.RowMod == IceRow.ROWSTATE_UPDATED) select dtl_row))
{
dtl.PriceListCode = "";
string partNum = dtl.PartNum;
DateTime? shipDate = dtl.RequestDate;
var company = dtl.Company.ToUpper();
var cust = Db.OrderHed.FirstOrDefault(hed => hed.OrderNum == iOrderNum && hed.Company.ToUpper() == company)?.CustNum ?? 0;
// Go through the Customer-PriceList links. First start with non-ShipTo specific price lists.
// start bottom to top so that we will prioritize the top-most price list in customer entry (list with lowest sequence #)
foreach (var custPList in (from cPrice in Db.CustomerPriceLst where cPrice.CustNum == cust && cPrice.ShipToNum == "" orderby cPrice.SeqNum descending select cPrice))
{
// Now get the price list and make sure it's valid for the ship date.
foreach (var pList in (from priceList in Db.PriceLst where custPList.ListCode == priceList.ListCode && priceList.StartDate.Value <= shipDate.Value && (priceList.EndDate == null || priceList.EndDate.Value >= shipDate.Value) select priceList))
{
// Now look for an entry for the line's part number.
foreach (var pListPart in (from listParts in Db.PriceLstParts where listParts.ListCode == pList.ListCode && listParts.PartNum == partNum select listParts))
{
// Finally we can save the list code
dtl.PriceListCode = pListPart.ListCode;
dtl.BreakListCode = pListPart.ListCode;
dtl.UnitPrice = pListPart.BasePrice;
dtl.DocUnitPrice = pListPart.BasePrice;
dtl.OrdBasedPrice = pListPart.BasePrice;
dtl.DocOrdBasedPrice = pListPart.BasePrice;
dtl.ExtPriceDtl = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DocExtPriceDtl = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DocDspUnitPrice = pListPart.BasePrice;
dtl.DocTotalPrice = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DspUnitPrice = pListPart.BasePrice;
dtl.TotalPrice = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.ListPrice = pListPart.BasePrice;
dtl.DocListPrice = pListPart.BasePrice;
}
}
}
// Now get the ShipTo Number on the order and make sure we don't need to take that into account also.
string shipTo = (from hed in Db.OrderHed where hed.OrderNum == iOrderNum select hed.ShipToNum).FirstOrDefault();
foreach (var custPList in (from cPrice in Db.CustomerPriceLst where cPrice.CustNum == cust && cPrice.ShipToNum == shipTo orderby cPrice.SeqNum descending select cPrice))
{
// Now get the price list and make sure it's valid for the ship date.
foreach (var pList in (from priceList in Db.PriceLst where custPList.ListCode == priceList.ListCode && priceList.StartDate.Value <= shipDate.Value && (priceList.EndDate == null || priceList.EndDate.Value >= shipDate.Value) select priceList))
{
// Now look for an entry for the line's part number.
foreach (var pListPart in (from listParts in Db.PriceLstParts where listParts.ListCode == pList.ListCode && listParts.PartNum == partNum select listParts))
{
// Finally we can save the list code
dtl.PriceListCode = pListPart.ListCode;
dtl.BreakListCode = pListPart.ListCode;
dtl.UnitPrice = pListPart.BasePrice;
dtl.DocUnitPrice = pListPart.BasePrice;
dtl.OrdBasedPrice = pListPart.BasePrice;
dtl.DocOrdBasedPrice = pListPart.BasePrice;
dtl.ExtPriceDtl = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DocExtPriceDtl = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DocDspUnitPrice = pListPart.BasePrice;
dtl.DocTotalPrice = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.DspUnitPrice = pListPart.BasePrice;
dtl.TotalPrice = Math.Round(pListPart.BasePrice * dtl.OrderQty, 2);
dtl.ListPrice = pListPart.BasePrice;
dtl.DocListPrice = pListPart.BasePrice;
}
}
}
}
Resurrecting this thread because we finally did implement a solution for this and its actually working really well. Maybe the idea can help somebody else.
The solution is to add UD fields to represent the future price and future price effective date on price list part. Then run a function overnight that, when the future price effective date is <= today, update the price on the price list based on the future price in the UD field. Then iterate through all open sales orders and force them to pull in the updated pricing.
It’s not a perfect solution because you still have the issue that the price on the sales order acknowledgement is potentially different than the price on the invoice, even when the future ship date/pending price change is known at the time of order entry, however this is mitigated with a custom report that generates the customer’s current prices with their future prices (which are loaded in the UD field) side by side, that can be sent out via email to notify them of the upcoming change.
