cancel
Showing results for 
Search instead for 
Did you mean: 

Mass Updating the Price in Existing Sales Orders using DTW

0 Kudos

Hello,

We as a distributor, take orders from our network of retailers who take advantage of booking seasons. Manufactures offer large incentives to pre-book product for future deliveries. IE: We accept booking orders in October for delivery to take place in May the following year. Here is the scenario. We as a company face exchange rate fluctuation and price increases handed down to us from the manufactures due to high demand with commodities. It is very common in our industry to see price changes often. I am asking the question. How can I simply, Mass Update existing sales orders to the new price we have re-calculated due to price increases?

I have tried RDR1 and failed due to the row numbers not matching correctly. (This happens when a line is deleted off an order, and is common practice for us to delete lines from sales orders) I am also aware that any lines from a sales order that has partial delivery cannot be updated and my file is prepared accordingly. I also managed to try a line query using script that was written by a member on here which was helpful, but the row numbers were still not aligning correctly which hindered my attempt. Link is here - https://blogs.sap.com/2018/05/03/tips-for-linenum-of-dtw/

Price fluctuations are common in my industry and I need to be able to update sales orders quickly and often throughout the year.

We work on SAP B1 - Hana 9.3 and I am looking forward to solving this with your help. Please let me know if you need any more clarity on my question.

Thank you,

Warren

Accepted Solutions (0)

Answers (4)

Answers (4)

0 Kudos

This blog post was from 2007. Is it still relevant to SAP B1 Hana?

Maybe I should try a different approach. Who on here has ever needed to do a mass update of prices on their sales orders? Am I the only company? If you have had to do a mass upload of sales prices, can you please reach out to me.

Thank you

0 Kudos

Crickets? Nobody in this forum has the answer? Come on! Where are the Guru's?

Johan_H
Active Contributor
0 Kudos

Hi Warren,

Please use the 'Add a comment' link below any given answer, instead of posting an answer yourself. When you post an answer to your own question, two things happen:

  1. The person (other than you) who posted an answer, will not get notified, and will likely never see your comment / follow up question. (you may thank SAP for this design flaw)
  2. The more answers there are to an question (when you look at the list), the less new people will look at it, because they assume your question is being handled.

Now I am trying to help you. Please have a look at my follow up question. If however for some reason you don't want my help, please let me know, so I don't waste my time.

Regards,

Johan

support_19
Active Participant
0 Kudos

sap-131313,

You may look into this tool as well.

https://blogs.sap.com/2007/08/19/get-your-kicks-with-di-commander/

Thank You.

0 Kudos

Hi Johan,

Thank you for the reply. I have build a very strong query file used to upload to DTW, however because we delete rows from Sales orders, the row numbers are out of sync and do not match up correctly which causes DTW to throw an error message. I have run out of real options using DTW due to us deleting lines from orders. I was hoping for a different approach. DI API is a new term for me. I will research further and try and get a better understanding on how that works.

I am taken by surprise that this is not a feature in SAP B1. I surely cannot be the only business on planet earth that needs to change prices on sales orders? My quest shall continue. Hoping to see some more feedback on here from other users as well. Please chime in! 🙂

Thanks,

Warren

Johan_H
Active Contributor
0 Kudos

Hi Warren,

Of course everything depends on the scale of the work this dynamic scenario is causing you. I see the following solutions, in order of how much easier they would possibly make your life vs. investment:

  1. Use the DTW with a well built query, the result of which, when exported to Excel, will give you a ready import file for the DTW, including the correct line numbers.
  2. Create (or have created for you) an addon that uses the DI API to automatically update changes to prices in open Sales Orders, according to any rules, and considerations your scenario requires.

Regards,

Johan

Johan_H
Active Contributor
0 Kudos

Hi Warren,

"...I have build a very strong query file used to upload to DTW..."

What I meant with the first option, is to save a query into B1, that you run whenever a price change takes place, and/or that compares price list prices with those in open sales orders. This query should return a result that you can paste into Excel, to create a ready made file that you can then import with the DTW.

Come to think of it, you could create a stored procedure directly into MS SQL Server, and have that write the data directly to a tab-delimited text file.

"..however because we delete rows from Sales orders, the row numbers are out of sync and do not match up correctly..."

I am not entirely sure what you mean with this. My idea was that you run the query ad hoc, and it should return the current row numbers, and the new prices, so it does not matter whether rows were deleted or not. You should not try to create the file beforehand, and change the prices in that file.

"...I am taken by surprise that this is not a feature in SAP B1..."

You can change prices in an open sales order, so again I am not entirely sure what you mean. Could you perhaps please describe your scenario in a little more detail?

"...DI API is a new term for me. I will research further..."

The DI API is a "driver" of sorts, that can be used by an application to manipulate data in a SAP Business One company database. You can use it to simplify or completely automate (laborious) tasks. It is the "big guns" so to speak, but if someone in your company happens to have coding skills (preferably, but not necessarily .NET), it does not even need to be difficult or expensive. The necessary SDK is included in your installation media (unless you are running a cloud version). The DTW uses the DI API, so you would be able to automate the entire process, and extend it, for example to send a report to your email inbox with the changes made. You could even create a console application, run it in a scheduled task on the server, and cut out manual work entirely.

Regards,

Johan