Dos and Don'ts for GET_CUSTOM_PRICE

Suv Biswas has posted a step by step tutorial on how to use GET_CUSTOM_PRICE on www.oracleappsblog. I have used this feature in multiple implementations. At one of the client sites, I had some come up with some Dos and Don'ts for the developers using GET_CUSTOM_PRICE. I have re-written it to fit the format of this blog, I think that it would be a nice extension to Suv's article, I also hope that it might come in handy (and save some time) for those trying to use this functionality.

=> Don't use global memory structures like OE_ORDER_PUB.G_LINE, ASO_PRICING_INT. G_LINE etc in Get Custom Price.
Since these structures are used to source Pricing and Qualifier Attributes (seeded or custom), there is a tendency to use these in GET CUSTOM PRICE as well. However there are two major differences between the way Sourcing rules for Pricing and Qualifier Attributes are used and the way GET_CUSTOM_PRICE is used.

a) When any module calls Pricing for more than one line it calls QP in a batch. The calling application loads all the pricing and qualifier attributes before the call to QP. As a result when the sourcing for these attributes is called it has access to the global structures (like OE_ORDER_PUB.G_LINE etc.). A structure with all these attributes is used as an input to QP Call.

So when GET_CUSTOM_PRICE gets called, OE_ORDER_PUB.G_LINE (in our example) has information for any one line (typically the last line). The reason is simple. Pricing Attributes and Qualifier Attributes are input parameters for QP.

b) In a lot of implementations, more than one application would use the same Price List (or Modifier). for example, Quoting, iStore and OM would often use the same Price List and Modifiers. You will notice, that while there is an option for writing different sourcing rules for different calling application there is no such thing for GET_CUSTOM_PRICE. The reason is simple (as explained in previous point). The pricing and qualifier attributes are loaded by the calling application. QP just concerns itself with Pricing and Qualifier Attributes which in effect are input parameters to QP. It is specifically done so that various modules can call QP and it is not tightly coupled with any one.


=> Make sure that the pricing attributes used in GET_CUSTOM_PRICE is either used somewhere in an active pricing setup in Setups or the profile option "QP: Build Attributes Mapping Options" is set to No (or QP: Check For Active Flag is set to No).

The profile option "QP: Build Attributes Mapping Options" controls whether all or only the used pricing/qualifier attributes are sourced. Setting the profile option to "No" is pretty wasteful, since there are more than 200 seeded pricing and qualifier attributes and it is highly unlikely that you use more than 50. You might end up creating a dummy price list to use the pricing and qualifier attributes that are not used anywhere else but are used in GET_CUSTOM_PRICE.


=> If you are using GET_CUSTOM_PRICE in various Pricing Formulae for different functionality, set a flexfield aside to identify the formula.

One of the inputs to GET_CUSTOM_PRICE is pricing_formula_id, but the value of this is likely to change between development, test and production instances, so it is, obviously, not advisable to hard code the pricing_formula_id in the function. The pricing formula name field is update-able, it is possible for someone to inadverdently change it, for the users to demand a more friendly name. The additional advantage of using the flexfield value is that if you have more than one formula using the same functionality you don't have to code for it.


=> Minimize number of Loops

One has to loop through the structure "p_req_line_attrs_tbl" to get to the required value. Say :

FOR i IN 1..p_req_line_attrs_tbl.COUNT
LOOP
if p_req_line_attrs_tbl(i).attribute_type = 'PRICING' and
p_req_line_attrs_tbl(i).context = 'PRICING ATTRIBUTE' and
p_req_line_attrs_tbl(i).attribute = 'PRICING_ATTRIBUTE31' then
if X_FORMULA_TYPE = 'A'then
x_return_value := xxxx_get_price_details_a ((to_number(p_req_line_attrs_tbl(i).value)));
elsif X_FORMULA_TYPE = 'B' then
x_return_value := xxxx_get_price_details_b ((to_number(p_req_line_attrs_tbl(i).value)));
..
..
end if;
end if;
END LOOP;

in this case PRICING_ATTRIBUTE31 is setup to source line_id, so we find the required value in p_req_line_attrs_tbl(i).value
If get_custom_price is being used in more than one formula, then make sure that (a) the loop runs only once and (b) you exit the loop as soon as you are done. PL/SQL can be pretty slow, and the time consumed is difficult to find as it does not appear in your standard TKPROF output. Another reason why (as covered in the next point) the value of "QP: Pass Qualifiers to Get_Custom_Price API" should be set to No unless you really require it.


=> Set correct value for "QP: Pass Qualifiers to Get_Custom_Price API".

Qualifier Attributes are not automatically available to GET_CUSTOM_PRICE. If you want to use value of a qualifier attribute, make sure that the value of profile option "QP: Pass Qualifiers to Get_Custom_Price API" to 'Yes'. As explained in the previous point, set the value to No unless it is really required to be 'Yes'.

11i Implementations. Does it make sense to Offshore?

I met a few friends for lunch, and we got into this discussion on whether it made sense to offshore work on Oracle Applications Implementation.

From my personal experience at various implementations, I am convinced that it does not.

Product development is a completely different ballgame. Most of the folks, for e.g. in Oracle Product Development teams (at least the programmers) are isolated from the industry/end users anyway. So it makes perfect sense for that part to be sent offshore and it has been done (albeit with varying degrees of success).

An implementation, however, is a totally different. The expected time taken to develop something is much lesser in implementations (you don’t have to do something that works for all clients) and the amount and frequency of changes much more. Time after time I have seen projects run into problems because of the offshore components. You invariably end up taking more time in just writing the “detailed” design documents than you would have to code the whole darn thing, test it and document it, and yet, the documentation is never detailed enough. The amount of testing and iterations needed is many folds as off course is the time taken per iteration. The team sizes are always bigger and it always results in more project management effort and resources. I am not even talking about small to mid size companies here. I am talking about fortune 50 companies that spend 10s of million dollars (100 + in some cases) in implementation.

The sole exception to this (maybe, just maybe) is writing reports, that too when you are using Oracle Reports and where you have folks on site who can make small changes and turn things around quickly.

I was expecting at least some folks to support the offshore strategy. Interestingly enough no one did. Everyone had horror stories of how bad the offshore thing was and how their companies/clients were spending far more money (and more importantly taking much, much longer) to implement.

I guess I wasn’t talking to the decision makers. And the decision makers, sure as heck, aren’t talking to us…

OM : Can Processing Constraints be used for Warning?

Another blog for a very-very specific issue.

Clients have always asked me two questions about Processing Constraints.

1. Can the messages have variables in them? (For e.g. in case of Minimum Order Quantities that vary by Item, instead of saying "the quantity is less that Minimum Allowed" could the message be "Minimum Order Quantity for Item X is 500")?
2. I don't want a hard constraint. Can I use processing constraint to throw up a warning?

The answer to both the question is Yes. (well why else would I be writing about such a specific scenario, though it would have been fun if I just said NO and finished this blog?). The solution for both is same as well. It is a little ugly for case (1) as you might see multiple message windows. But hey, it works.

The title of the blog is the link to the pdf.

Extending Flexfields.

Almost every big client runs out of flexfields in a few key tables. The flexfields in these tables are often considered to be prime real-estate. Most clients either settle with sub-optimal solutions or create a custom table for flexfields and create a form to maintain the custom table, which is a fairly expensive customization.

We came with this idea where we could create a fairly simple solution, which would use all the standard flexfield features that Oracle provides and yet make it very simple to extend flexfields on, pretty much any table. The cool thing was that the idea was implemented within 3 days.

There were some concerns about the performance intially, but it worked out pretty well at the end.

The title "Extending Flexfields" is a link to the white paper on the solution.

We added some more cool features to this solution after this white paper was written. (The solutions should be pretty obvious once you have read the White Paper).

• We added an action field to the extended flexfield form, which could be used for any user defined action. Basic concept was the same. We had a table that had all the actions and associated PL/SQL procedures/functions. The forms were linked to actions (so that the actions were available only on appropriate forms). The Primary Keys were used as input to the PL/SQL routine associated to actions. (eg. you want to run certain custom validations on user requests for an order - this would let you do it, if you wanted user to be able to send certain pre-formatted notification - this would let you).
  
• We added multiple rows to the extended flexfield form. Basically it would allow you to have multiple (set of) values of the same flexfield for one parent record.
  

I have the entire code for this, if any one is interested do let me know.

What would be published here?

Anything and everything related about Oracle Applications is welcome here. (an occasional thought about SF Giants is acceptable too).

Anything. I would just like it to be interesting and informative (or at least interesting :-)).

Macro stuff
like Peoplesoft acquisition. Its effect on consulting.
Micro ones
like a cool script I wrote.

Most of Oracle Apps related work that I have done has been on distribution modules (OM, QP, AR, CZ etc.), and I intend to write about that. I plan to invite other friends who have worked on other more exotic modules, to share their thoughts and work. And ofcourse all readers are more than welcome to contribute. I will be happy to let all contributors promote themselves.

Happy Blogging...

Why Oracle Applications Blog?

I have been thinking of starting an Oracle Applications specific blog for the longest time.

Why?

Well thinking of doing anything for the longest time and not starting is, some thing I do quite often...

oh, oh! Why on Oracle Applications?

Many reasons

• I haven't found a good place on the net to discuss "Oracle Applications".
• I wanted an informal medium, where my immense love and talent for documentation did not become a barrier to my posting any thoughts.
  
• I have several friends/acquaitances who, I think , have significant stuff to contribute. I think they would (just like I would) be more likely to do that in an informal setup like this. Where they could share some of the excellent work they have done, or just add their thoughts to what others have posted.
  
• I wanted the discussions to be moderated, and not be inundated by job postings.
• I wanted a place where, I could sneak in and change my past postings, and show the world how prescient I can be. (Wait! I can do that, on blogspot, can I not?). [Edited... Yes, I can!]
  
• and last and (I promise :-)) the least, the shameless commerce department of my company told me that it was about time I indulged in some [er.. shameless] self-promotion.