How can we bulk-update Unit Prices of items in all open Sales Orders?

[Former Member]

We are going to update our prices in our primary price list. How can we update the unit prices on all the Open Sales Orders in bulk? We have over 50,000 open line items at any point in time and price changes happen regularly (once a quarter). I will really appreciate an early response for the same.

Regards,

Aakash

[Abdul · Accepted solution]

Export Open Order in Excel and Change Prices and Upload but you will be able to update price for open iteems only if an item is partially delivered then you cannot update its price.

I suggest change price at the time of Invoice by deploying a FMS from relevant price list

For How to use DTW to transactions Data Got Through the below link

DTW For Transactions

[Former Member · Accepted solution]

Yes, the second part of the answer seems like a good idea. Changing prices at the time of the Invoice. Thank you. I will contact my partner about it.

[kothandaraman_nagarajan]

Hi,

In general, sales order will be created after receiving order confirmation from customer. Review your sales process with your customer and explain the impact o price in SAP B1.

Only possible way is to use DTW to update price in open sales order. Take backup of DB before updating sales orders.

Regards,

Nagarajan

[Former Member]

In our industry, prices are usually stable (and well-known). Customers are regulars who place daily orders and only with quantities. Anyway, thanks for the answer. I have seen many helpful answers from you on the forum. Can you please help me out with this as well: https://answers.sap.com/questions/510464/how-to-disable-auto-filling-of-to-release-quantiti.html

[Johan_H]

Hi Aakash,

I would hate to be your customer. Order an item for 10$, to be greeted with an invoice for 100$, because you raised the price afterward.

Anyway, you have two options:

1. Export all orders to Excel, change the prices in excel, and upload them back into B1 with the Data Transfer Workbench.
2. Create an addon to loop through all open sales orders and update the prices.

Regards,

Johan

[Former Member]

It's an industry-specific behavior and the customers are well-aware of an upcoming price increase/decrease so they order accordingly. Goods are dispatched as per availability and the price changes are usually 3-8% (and definitely not 900%). Anyway, thanks, creating an addon might be the only option.

[Johan_H]

Hi Aakash,

The good news is, that such an addon could be a very simple console application, run on the server on a schedule:

1. A query to find all open order lines where the price differs from the price in the primary price list.
2. Loop through the relevant orders and lines, and update the prices
3. Optionally: collect the changes into a courtesy email message, and send it to the relevant customer.

Step one actually brings to mind a potential third option. Considering that price changes only happen once a quarter, and maybe not every time for all your items, with that same query perhaps the actual number of order lines that need updating is not all that large. You need that query anyway, so I would suggest that you develop it first, and see about what sort of workload we really are talking. Based on that, you can decide to use the query in an addon, or to just process it manually.

Regards,

Johan

P.S. If you feel your question has been answered, please close this thread by setting kothandaraman.nagarajan's or my answer as the best answer (link below each answer).