A day in the life of an Oracle Applications Consultant

Wednesday, January 26, 2005

The ITIL (IT Infrastructure Library) framework

The other day I had someone phone me up and ask me about ITIL. Sadly, I wasn’t able to tell them very much. I soon discovered that ITIL seems to be quite a buzz word in the IT industry right now. This post contains details of an upcoming Webinar on ITIL which I will be attending and which others might be interested in knowing about.

The ITIL (IT Infrastructure Library) framework is quickly becoming the most recognized service management approach by businesses looking to shore up their IT operations. As IT continually strives for improved effectiveness, measured accountability, and automated processes for ensuring best practices and policy compliance — the need for continuous IT controls is finally resonating throughout the organization.

For those interested here is a free upcoming webinar on ITIL (IT Infrastructure Library): -

“Getting the Right Data for ITIL Success”

Date - Tues., February 15th, 2005
Time - 10 AM PST (1 PM EST)
Presenter - Dr. Sheldon Borkin,
CISSP & Cendura VP, Strategic Accounts

» To register, go to:

Friday, January 21, 2005

FSG Transfer Program

The other day I came across this feature in Financial Statement Generator which I never knew existed and found to be quite useful.

FSG Transfer is a programme that you can run in General Ledger which gives you the ability to copy Financial Statement Generator (FSG) components from one environment to another. For example, you might want to transfer your FSG’s from a TEST environment to your PRODUCTION environment.

According to the Oracle General Ledger User Guide, before this report can be run, the following prerequisites should have been implemented: -

  • You or your System Administrator must define database links.
  • The Chart of Accounts in your source database must be identical to the Chart of Accounts in your target database.
  • Any currencies and sets of books referred to by the row sets and column sets being copied must exist in the target database.
  • Report details, such as budgets and encumbrance types, referred to by copied reports must exist in the target database.
  • You must be logged in to General Ledger and connected to the target database.

Programme Parameters

To run the report, from the file menu in General Ledger Responsibility select View > Requests and choose to run a single request entitled Program - FSG Transfer. Examples of how the parameters for this programme can be filled are as follows: -

An example of how you might fill out the FSG programme transfer parameters

Component Type

The following elements of an FSG can be copied between environments: -

  • Column Sets
  • Content Sets
  • Display Groups
  • Display Sets
  • Reports
  • Report Sets
  • Row Orders
  • Row Sets
  • All of the above

Component Name

The name of the component selected above should be entered if you are copying a single component mentioned above. If you are copying all components you do not need to specify a name.

Source DB Chart of Accounts

The exact name of the Chart of Accounts from which you want to copy report objects.

Target DB Chart of Accounts

The exact name of the Chart of Accounts to which you want to copy report objects.


The name of the source database from which you are copying. This field will not have a List of Values (LOV) unless you have defined database links. To define database links within the General Ledger Responsibility go to Setup > System > Database Links and create a New Database Link (if you have any uncertainty about what parameters to enter here contact your DBA, typically you should be able to extract the connect string from the TNSNAMES.ORA file).

Wednesday, January 19, 2005

Oracle Apps Blog Improvements

Some improvements have recently been introduced to this site which should hopefully ensure a richer blogging experience for 2005 - I’ve just moved my site to a new IPP and also upgraded to ExpressionEngine v1.2.1. Here’s the details…..

pMachine Hosting - my new IPP

Towards the end of last year I encountered a problem with my website which was causing it to crash every couple of days. Basically, my existing Internet Presence Provider (IPP) couldn’t handle the amount of traffic I was generating. I decided, therefore, to move my site to a new host, namely pMachine Hosting (who incidentally are the providers of the ExpressionEngine blogging software I use for this site). It seems to have been the right move because I haven’t experienced any crashes to date and the site seems to be running a bit faster.

ExpressionEngine v1.2.1

This was released on the 15th of January 2005 and I’ve just upgraded this site to it. According to the ExpressionEngine site some of the new features are: -

  • A stand-alone version of the “publish” form, so weblog entries can be submitted from any page
  • An improved plugin manager. Plugins can now be browsed and installed directly from your control panel
  • URL mapping for people who migrated from pMachine Pro, allowing pM style links to point to their new EE location
  • Redesigned template installer which allows new template designs to be incorporated more easily
Check out the change log for a detailed list of the changes.

Thursday, January 06, 2005

GET_CUSTOM_PRICE - Do’s and Don’ts

Suv Biswas has posted a very good article written on how to use GET_CUSTOM_PRICE (it is listed under Advanced Pricing section). It gives you a step by step tutorial on how to use GET_CUSTOM_PRICE. 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
  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;

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’.

Wednesday, January 05, 2005

Oracle OpenWorld Amsterdam Presentations available for download

OpenWorld Amsterdam took place some time ago (September 2004) but I never pointed out on this blog where you can download the presentations from so here goes.

You can obtain a listing of the presentations and associated abstracts here or go straight to the downloads page. There are some really good ppt’s here for anyone with a functional or technical interest in Oracle Products and Software.

Since I’m more functionally oriented a selection of the ones I found quite interesting are: -