Pricing Stored Procedure

[SBarnes]

What is the name of the stored procedure that Jiwa uses to calculate the price of an inventory item for a given debtor etc?

[Mike.Sheen]

Hi Stuart,

We have a stored procedure, usp_JIWA_Price_GetPrice which emulates what we do in the PriceScheme object - the price scheme object doesn't use it because we needed to maintain support for script based pricing and as that can't be achieved by a stored proc alone.

I'm not sure if anyone really uses the scripted pricing - there have been protests in the past when we threatened to remove that function so we never were able to make the PriceScheme object use the usp_JIWA_Price_GetPrice stored procedure.

Mike

[SBarnes]

Hi Mike,

Thanks for letting me know I would lodge a protest against it being removed also.

Actually I had always thought the Pricing Scheme Object actually called the stored proc as I had though this allowed the option of customising how Jiwa got to the final price which I had always thought to be a good design feature.

[Mike.Sheen]

I had though this allowed the option of customising how Jiwa got to the final price which I had always thought to be a good design feature.

That would only be possible if we discontinued the scripted pricing - which is one of the reasons why we wanted to retire scripted pricing. If we did remove scripted pricing, the equivalent of scripted pricing could be achieved by plugins - but you'd need to add handlers in many different business logic objects to achieve that (anything getting a price - like sales orders, sales quotes, service jobs and so on). That wouldn't be ideal - as you're now not using a nice centralised point to get the price, and you don't know what 3rd party bits of logic are getting prices from the price scheme object currently.

In reality, the current methodology of defining price elements via the price schemes system simply provides a list of stored procs - and if used, also scripted prices - which are executed in order as defined by the price scheme - and as each price scheme has the ability to define the order of price elements to be examined and if the cheapest price is to always be used, so if you wanted to alter the how the price is determined you do that by either changing the price element in the priority order, or changing the cheapest price option.

If that's not flexible enough (and I don't think we've ever been accused of that when it comes to price determination - it's one of the things our competitors still have not managed to match) - then we can perhaps add some events to the price scheme object to allow plugins to override what price should be determined. There would be a performance penalty for that if the cheapest price option is off for the price scheme, as we'd need to call every stored proc, every pricing script in the price scheme and not trip out once a price is obtained from the ordered list of price elements - and then raise an event so a plugin handler could make it's own price determination based on all the available prices.

If it's important for you to have this, let us know and we'll consider backlogging an improvement to allow plugins to override the pricing logic in a neat, centralised fashion.

Mike

[SBarnes]

Hi Mike,

Thanks for the detailed update to be honest with you I would prefer over all to see things remain in stored procedures as much as possible as I am currently working on a project to upgrade a version of Jiwa where the stored procedures are being called by other stored procedures that have been added to Jiwa to support an external application, namely a web store, that has has added its own tables to the Jiwa database so plugin code wouldn't get applied in this case.

If there was a way of guaranteeing that some "final stored procedure" would calculate the price and also apply any loaded plugin code would be great as it would achieve the "best of both worlds" but I don't see how that would be possible from a technical perspective.

The elegance of the functionality to support pricing through customisation using stored procedures and also the way in which this can be done in other areas to system such as printing filters and now in displaying grids etc. is I believe one of Jiwa's greatest strengths.

In fact one idea that I would suggest that would be of advantage would be if there was a stored procedure that was called every time a business logic object wrote to the database that might take the type of object updated, id etc and gave the option of handing back an error code and error message if any customisation / validation in this procedure was applied in terms of required business logic and roll back the change if say the error code wasn't zero as this would avoid the situation of where you end up with a lot of small plugins to take care of minor business rules and validations.