Advanced Pricing - Not for the faint of heart

[BBritain]

Ladies and Gentlemen,

Our users are Customer Sales/Service Personnel who are on the phone with their customers constantly. On a 30 second call, they can review the prices of 12-20 different products on their current system. As they go to OneWorld they need the ability to have the advanced prices pop-up for any of their customers. Because advanced pricing can be down to the customer level, each and every item (20,000) must be priced for each and every customer (650) requiring 13 million calculations. I have cut my calculation process down to where I can process 4 calculations per second (most of that time is processing the advanced calculation).

I am looking for suggestions to speed up this process. Since we are running on an AS400 we are looking into RPG running the World Advanced Price Calculation function (We are NOT coexistent but do have a world license), or rewriting the OneWorld Advanced Price Calculation function trying to trim down the complexity but still meet our needs.

Does anybody out there have any experience they can share or any suggestions to try. Every time a Sale occurs they may change the price of a whole product line (~2-5000 items)which needs to be calculated before the calls begin bright and early in the morning. One process we are considering is when pricing changes are known in advance, that we calculate the prices in a backup table and copy the table during the night.

Any advice will be considered,

Ben again,

 

[Frosty the Coder]

Ben,

I have a client dealing w/the same issue.

The only suggestion we came up with is to build a PRICING TAG FILE,
using Item#, AN8, and Price Group as the key.
This tag file would be updated NIGHTLY.
The users would have to be aware that prices are "as of last night".
The idea did NOT fly well.

Should we have any other brainstorms I will let you know.
If anybody else has suggestions, _PLEASE_ post them.

TIA

Gene

 

[JMR]

Ben,

Excuse my initial ignorance, but I am trying to picture what your users are trying to do...

A user on a call with a customer. Is the customer just requesting prices about certain items? If so, why can't you use the Advanced Check Price & Availability program (P4074)?

It sounds as though you are building some kind of pricing file based on your Advanced Price setup...is this correct? If so, why are you doing this? I am just trying to get a feel for your process. I have done something similar with extracting Adv Pricing prices.

I don't understand what you mean about a sale possibly changing prices for an entire product line.

 

[peterban]

Ben,
I also am a little confused.... but in part I agree with Jeremy.

If I can give a little background....
Advanced pricing is based around schedules.
Each of these schedules is a series of adjustments, which in turn can be either against an item, customer or order group and depend on a heirachy.
This is as well as many many other things.
Advanced pricing DOES NOT require that ALL priced be set up for ALL combinations. Indeed you may not want to sell certain products to particular channels!
Also the way that it determines the price uses the groupings and hierachies in such a way that only one (I think !) access to the pricing file (F4074) for each adjustment in the schedule.

If they are giving a discount based on order volume then adjustments can do this and you dont need to change anything. If you want to give a channel discount then adjustments can do this too.

For my money this sounds like a setup/understanding issue.

If you can give some concrete examples I feel sure that we can give more detailed help.

Warm regards

Peter

 

[Pon]

Ben,

I'm think I'm a bit confused as well but, I'll share my experiences with you. I've done/seen some work with this issue two ways:

1) One of my customers wanted to allow their customers to view item availability and the customers price via the web (portal). Of course, they wanted some custom "tweaks" to boot. So, I created a portal program with logic that mimicked the Advanced Check Price & Availability program (P4074 - as Jeremy mentioned). The customers typed in the Item Number and Quantity and, the program calculated the customer and "base" price of the item on the fly. It works great and, the customers love it!

2) For mass changes (like you said, recalculating the prices of ALL items for a specific customer) another developer used the same logic from P4074 and created a batch program that could be run after hours. It is a bit slow and, does have a glitch or two now and again but, I think it's very minor and my customer continues to use it to this day.

 

[BBritain]

OK all,

Sorry for the delay, I was off chasing another fire.

JMR,

The items have many catagory codes associated with them and lets say the first three are length, width, and height, uh ok and a fourth - quality. So the customer calls and says "I need four items that are of this specific length, width, and height." The user will populate the filters and perform a Find which needs to display the item, quality, price and quantity available. The user needs to also be able to order the records by any of those four fields. This fact alone means that the price needs to be precalculated, but besides that, I would just take too long to populate a grid of 10 items that the users would be very unhappy. (The users have a system which now displays these prices immediately - but their current advance pricing is much more limited.)

Occasionally, the company will decide to put all items of a certain brand name (also a cat code) on sale and setup the advance pricing for 10% off. This could result in 2-5000 items changing price between 11:59 pm and 12:01 am although the customer calls really stop between 8:00pm and 5:00am.

So I think I've made my argument for needing to precalculate the prices, because 1) they need to be sorted by price, 2) 'on the fly calculating' is too slow, 3) something concerning my job.......

Peter,

Fortunately, the pricing will NOT be based upon volume of sales. And when I spoke about calculating the price for every Item/Customer combination - I was just shor-cutting my explanation - as it is really every Item(Branch)/Customer combination and we are using branch for channeling? Since every item within a branch is available for purchase to every Customer within that branch.

Pon,

I have created a table which holds these prices as well as a table (really some tables) which carries a flag indicating the prices may have changed (due to changes in Base Price table, Advanced Price table, or settings in the Advanced Price setup, and changes in Item Master/Item Branch). This report works very well at processing um re-processing only those prices that need it, but just like your mass changes batch program - it is a bit slow (although mine never has a glitch! ). I have calculated the time and the best I can seem to get is four calculations per second. Assuming a worst-case scenario of changing 5,000 items for a sale of say 10% off, this would require 3 million calcs or 833 hours to run.

We are currently looking into using an optimizer to improve the general performance of the process. Most of the time, when they change one or two items, this will be a non-issue but on those sales (and even for the first time) watch out!

Keep the questions coming (suggestions too)!
Ben again

 

[Pon]

You are in a pinch. OneWorld and speed. What's that saying? If I had a dime for everytime I heard............

Not sure what your solution should be but, I'll try and give it some thought.

By the way, It's not MY batch program that has the glitch smarty pants

 

[JMR]

Ben,

OK, you make your point about needing it pre-calced.

Tell us a little about your Pricing setup:
1. How many schedules
2. How many adjustments per schedule

3. Does your current build run nightly and if so, when? How much processing is going on during the run?

Suggestions:
#2. If you have a lot of adjustments attached to your schedules, this is the greatest performance hit for Adv Pricing. Try to put the adjustment detail into as few adjustments as possible...by adding hierarchy levels if need be. While this makes for a total pain in the *** to maintain the detail, it will SIGNIFICANTLY increase performance.

#4. If you have spare processor at night (or whenever you do your build), I suggest running mulitple concurrent instances of your build (each with distinct sets of data of course). You may be able to run 2,3,4, or more instances without any processor degredation (depending on your hardware). You could play with that number to see what the threshold is before you start losing performance.

Good luck and let us know what, if anything, you are able to do.

 

[Craig_Potter]

Ben,

I will try and give you some mod-free suggestions, although more often than not I see this handled via database extracts with Access Inquiries or RPG 'hard coded' programs that drill directly into the required F407* files (with all the flexibility (aka I/O) removed).

If you are trying to provide the ability to handle every Customer/Item permutation and combination and your pricing deals are often struck at specific Customer level - stop reading and go back to design mode.

If you have Prices structured on logical Business Groupings (Retailers Class A, B, C Class - Wholesalers A, B, C Class) and if the bulk of your Customers who ring and make these requests fall into these types of customer "pricing" categories - read on.

You can set-up dummy Sales Orders representing the Products bought by each of the main Customer categories. These Sales Orders exist on the Sales Order Detail file under a completely different order type (say 'PB' - Price Book Orders).

- Standard Customer Service Inquiries will allow you to filter these Orders based on Item and/or the first 5 Sales Cat Codes.
- Standard Order Re-pricing functionality will allow you to reclaculate these Prices when Price changes occur.
- Standard Reports exists that would allow you to print/extract these and send them out as Price Lists.

1. Create/Select a Customer representing each of the Customer 'categories' that require pricing.
2. Create a 'PB' order for the first Customer with all of the relevant Products.
3. Copy Order to all Other 'pricing' Customers

You will now also have some maintenance issues...

a. Adding new products - probably repeat the above or use Excel to populate F47011/012 and Batch create orders?
b. Removing Products / Pricing for Products - use a report to change the Status of these 'PB' Order lines to 999 and filter these out of your inquiries/reports using either standard Data Selection and/or Processing Options.

None of this is ideal and this is an area that developers are trying to address as part of the B9 (E9?) Pricing developments so you may want to get some more info on this new 'Price List' functionality to see what it has to offer you.

As a side note, try and get your users to step back from this as well....
a. There are direct costs to your business to have lots of different pricing deals for all of your Customers - communicating deals, maintaining prices, raising credits, training staff, getting IT to develop software, etc...
b. Your Customers do not want lot's of Price Changes - every time you make a change they need to make one to their records, they will get Credits or Adjustments, it they use JDE they will have to find a way to reprice their open purchase orders!.

Hope this helps

Regards,

 

[peterban]

Ben,
can I jsut ask for some additional qualification on the process...

1) the customer phones up and says hi - so we know which of the 650 cutomers they are ?
2) they ask for particular quality, hight etc - so there will be significantly less than 20000 items to look at ?
3) why are you using b/pl and not a customer category code for channel ?
4) how many adjustments per schedule - on average ?
5) are you dealing with a "timber" client ? ( I thought this was sold by the cube/grade )
6) Why arent adjustments used for the 10% off promotions ?

I think that a moded version of the price/availability enquiry/select may be the way forward, but I do not think this would not have the performance issues you are mentioning.

Warm regards

Peter

 

[sam]

Hi Ben,

I have had similar situations with JDE and performance on the AS400 database. Some of the things that you can do which can have varying degrees of improvement are to:
a) Check that all of the index's have been created for the tables used by the application, I have found that they are not always there.
b) Create an SQL performance monitor during peak time, then analyse the results, and create the additional recommended indexes.
c) Check the code for expensive database I/O.

Regards
Sean

 

[BBritain]

Everyone,

First let me express my appreciation for all of these quality responses, I am currently trying to evaluate each and every suggestion for our particular needs and am fairly bogged down, but will be sure to post more as time and progress permit, so know that I am not ignoring this.

These are specific answers to Peters questions - which I hope will help all - in helping me.

1) When the customer phones up, they immediately give us their customer number, so we know it right away.
2) The customer either knows the exact part number or will give us the size criteria and maybe a brand, so this narrows the item list down dramatically.
3) Since I am a developer and not an Applications person, I am not sure about what channel is, but I do know that the customer can order any part that is in the same Branch Plant that he is associated with. If this is done by customer catagory code, then to me it is a wash because I see the same effect either way.
4) There are usually just *one adjustment per schedule which alters the Base Price but there are Add Ons which show up in invoicing as additional line items - I guess this is a new feature with our release B8?
5) No, I am not dealing with a timber client. The examples I gave were not reality - but were close enough to get the idea across. I have not been given permission to discuss specific information about the actual product, but if you look at my email address that should give you a real good clue as to what products we do work with. Or to make it easier a car usually has four of these that can be found ....where the rubber hits the road.....
6) We are using adjustments to include sale prices of 10% off. I'm not sure what I said that indicated differently. If I did, I didn't mean to.

Because this is a price sensitive market, the customer is very acute to the prices for various "interchangeable" products. Our Sale Order Entry has been modified to exit out to a "Price Inquiry Screen" which basically displays Part, Description, Price, and Quantity Available. With the Price being precalculated, this screen is immediate and customer sales can order order up to 10 different parts (80 % of sales fall into this catagory) in 10 seconds. Upon returning to SOE, the ordered items are programmatically added into the sales order seemlessly (regardless of whether lines have previously been entered) and ordering can continue afterwards.

One of the ideas under consideration is to determine which of the customers' prices are only based upon the customer price group and calculate this once and populate all associated customers price field. Since all 650 customers fall into one of 25 customer groups this could potentially reduce our calculations dramatically (if we can correctly and efficiently identify these groups). This is one idea that has come out of your suggestions so far!

Ben again

 

[Its not on sale]

okay, I will assume you have World RPG Code available and also in executable form.

Have you already looked into X4074 (advance pricing price server). This server can be called indiviudally for each item to calculate price of item or customer/Item.

Server values are returned in an user index and you have to run through the index and get the last adjustment schedule to get the latest price.

You can create a seperate file and have a precalculated price available for the users to inquire on items. A seperate inquiry screen can be made on the custom file to just inquire on prices rather calculate them on the fly in price check and availability screen.

I am not sure if thats what you need but if it helps than its good, If need further info on X4074 I have a server written where you only pass Item and date or Customer/Item and date and it returns you the current price.

Regards

 

[WhippingBoy]

Well, I wrote X4074 and I designed B4500050. I think you've got a custom price book on your hands Ben. I suggest you compile known assumptions about the price structure and write a price-book to calculate prices based on that. It'll be much faster than relying on the generic Adv Pricing server. Then put a hook in order entry to look at (and retrieve) your 'pre-calc' prices somehow.

Good Luck!

 

[James_Halligan75]

Welcome back Darren!!! The Whipping Boy Lives on.....

Yes, I am currently confronted by the customer price list scenario at the
moment. The performance impact using SOE edit line is causing a meltdown in
any custom UBE written to generate a customer price list. I see there appears
to be a new layer on the pricing in 8.9 that may serve this purpose - me
wonders has JDE written new BSFNs to generate the pricing workbench files?

Again good to have you back....

James

 

[BBritain]

Re: RE: Advanced Pricing - Not for the faint of heart

James and all,

First of all - I want to reassure everyone that even though I have not been very active on this post, we are still very active in trying to solve it. AND solutions will be posted as soon as we discover what they are.

Now James - are you saying that there may be a layer that serves as a customer price list?

Ben again,

 

[BBritain]

I know that, as developers, we work with them constantly but I am not sure I have ever seen it in writing. "known assumptions" -- isn't that an oxymoron? (He says not knowing whether to laugh or cry because of the truth of it....)

Sorry, I just needed to get a little silly.

Ben again,

 

[James_Halligan75]

RE: RE: Advanced Pricing - Not for the faint of heart

I am just starting to get my head around what JDE have done in 8.9 - a good
starting point would be the KG doc ODS-03-0057 that explains the new Pricing
Workbench

J.