Querying values in foreign currencies

Here is the API you need to use to query the conversion rate between 2 currencies for a specific date.

gl_currency_api.get_rate(from_currency_code,to_currency_code, conversion_date, Conversion_type)

In a query it would look like this:

select

gl_currency_api.get_rate('USD','EUR', sysdate, 'Corporate')

from dual

Now we can combine that with the cost queries from the previous post to get the value of on-hand quantities in any warehouse in the EUR currency:

select msi.segment1 item,

sob.currency_code warehouse_currency,

cst_cost_api.get_item_cost(1,moq.inventory_item_id, moq.organization_id,NULL,NULL) cost,

gl_currency_api.get_rate(sob.currency_code,'EUR', sysdate, 'Corporate') conversion_rate,

cst_cost_api.get_item_cost(1,moq.inventory_item_id, moq.organization_id,NULL,NULL)*

gl_currency_api.get_rate(sob.currency_code,'EUR', sysdate, 'Corporate') cost_in_eur

from

gl_sets_of_books sob,

mtl_organizations mo,

mtl_onhand_quantities moq,

mtl_system_items_b msi

where

mo.set_of_books_name=sob.name

and moq.organization_id=mo.organization_id

and moq.organization_id=msi.organization_id

and moq.inventory_item_id=msi.inventory_item_id

and mo.organization_code='M1'

The gl_currency_api.get_rate will return exception if the exchange rate is not defined for the day.

How to find item cost

Another common issue when writing queries is getting an item cost.

Here are the APIs to use in queries:

For discrete manufacturing:

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)

here is a sample query:

select segment1 item,

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL) cost

from mtl_system_items_b

If you are in an OPM organization the above won't work and instead you need to call

select msi.segment1 item,

GMF_CMCOMMON.get_cmpt_cost( inventory_item_id, organization_id, sysdate, cmm.cost_type_id ,0) cost

from mtl_system_items_b msi, CM_MTHD_MST cmm

where cmm.cost_mthd_desc='Standard Cost'

How to query key flexfields

It is quite common that in Oracle Applications you need to write queries listing specific account combinations. The problem with that is that the account combinations are stored as their ids and you have to go to the GL_CODE_COMBINATIONS table to find the specific segments and concatenate them one by one.

Easy to do but always waste of time. Moreover, each EBS install has a different number of segments and query written for one client needs to be rewritten for another.

Well, not any more. There are Oracle APIs that allow you to get the complete combination as a string by passing the CCID

Here is the syntax:

fnd_flex_ext.get_segs('SQLGL', 'GL#', chart_of_accounts_id, code_combination_id)

Now, if you are on release 12 and want to have the concatenated combination descriptions you can also run this:

xla_oa_functions_pkg.get_ccid_description (chart_of_accounts_id, code_combination_id)

Here is a sample query:

select

fnd_flex_ext.get_segs( 'SQLGL',

'GL#',

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

code_combination_id) account,

xla_oa_functions_pkg.get_ccid_description(

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

Code_combination_id) description

from GL_JE_LINES

The fnd_flex_ext.get_segs call is universal and will work for any key flexfield. Here is the syntax for HR position hierarchies:

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id)

and here is a sample query:

select

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id) position_definition

from per_position_definitions

OM Defaulting rules Part 2

I worked on a project once where we had different lines of products maintained by different product managers across several sales territories and different currencies.

When an order was placed the price list was not specified on the order header but instead it was selected automatically on lines based on the sales channel, territory and the line of products the item belonged to.

This worked fine with one exception; the currency on the order header defaulted from the order type instead of the customer site. Prices were tailored to a specific market and specific currency and we could not simply create a multicurrency price list. Instead we have created a mandatory flexfield on the customer site and used the PL/SQL defaulting rules to dynamically default it on the order.

In the defaulting order screen you can select PL/SQL as the source type and specify the Package and Function name

The above shows the seeded values where the currency is taken from the Set of Books.

Note that there is no way to pass a specific order number or ID, the values will be taken from the current ONT_HEADER_DEF_HDLR.g_record which is automatically populated by the application for any order.

Here is the code I used to default currency from Attribute11 of site use flexfield:

FUNCTION Get_DEFAULT_CURRENCY

( p_database_object_name IN VARCHAR2

,p_attribute_code IN VARCHAR2)

RETURN VARCHAR2 IS

l_invoice_to_org_id NUMBER := APPS.ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id;

l_currency VARCHAR2(4);

l_debug_level CONSTANT NUMBER := apps.oe_debug_pub.g_debug_level;

BEGIN

IF l_debug_level > 0 THEN

oe_debug_pub.add('ENTER Get_sales_channel');

oe_debug_pub.add('Sold To Org ID :'||l_invoice_to_org_id );

END IF;

IF l_invoice_to_org_id is not null

AND l_invoice_to_org_id <> apps.fnd_api.g_miss_num

THEN

BEGIN

--Here is the actual code

select attribute11

into l_currency

from HZ_cust_SITE_USES_ALL situs

where situs.site_use_id=l_invoice_to_org_id;

RETURN l_currency;

EXCEPTION

-- Return null if there is noinvoice to site

WHEN NO_DATA_FOUND THEN

RETURN NULL';

END;

-- Return null if there is no customer

ELSE

RETURN NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

IF l_debug_level > 0 THEN

apps.oe_debug_pub.add('Error in Get_Currency') ;

apps.oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;

END IF;

RETURN NULL;

END Get_DEFAULT_CURRENCY;

OM Defaulting rules Part 1

Recently I came across a very annoying issue. Every time we changed the bill-to or ship-to address on the order header the 'Date Ordered' field would re-default to SYSDATE, messing up all the sales reporting.

A short research on metalink revealed note 748269.1 which recommends updating package OE_Dependencies_Extn and changing some seeded dependencies. To make a long story short we did that ending up with a 3 weeks long P1 issue that did not seem related in any way..

There is, however, a trivially simple way of fixing this problem using the OM defaulting rules:

(N) OM -> Setup -> Rules -> Defaulting

Query OM header, scroll down to Ordered Date and change the seeded defaulting rule to:

This will make the date default to itself first and to sysdate only if it was NULL in the first place.

Choosing the right instance

Working in  a multi instance environment with the main PROD instance accompanied by TEST DEV and others can be tricky. While in my previoust post I covered on how to tell the difference if one is looking for it, it is too easy to confuse them and create a test record on the Production instance, or enter a valid customer order into Test, especially if both are opened at the same time. 

The best way to avoid it is to change the application color and apperance for each instance. 

FORMS

Most of the time, when DBAs make a clone they set the value of the

Site Name 

profile by putting the name of the instance there. This changes the name of the application displayed in the bar on top of the window:

This is very useful but often can be overlooked.  A good way to make the difference apparent is changing the colors of forms which can be done with the 

Java Color Scheme

profile.  As long as every instance has a different color, the risk of confusing them is minimized

Another, a bit more radical approach is to remove the oralce graphical interface and display pure Java forms by changing the 

Java Look and Feel

profile. This will make your 11i or R12 applications look vary much  like the old 11.0.3 version.

OA Framework

The Java profile options will not change the way your Oracle Framework pages look like. 

To change them you need to se the 

Oracle Applications Look and Feel

profile. 

Here is an example of how the pages look after the profile change

If you want to go further than that, you can use the 'Customizing Look and Feel Administrator' responsibility to design your own graphical theme.

There is also a http://oracleskins.com/ company that designs customized skins.

How to find which instance I am using?

Forms

When in Oracle Applications forms the simplest way to identify whether we are on the right instance is  go to:

Help -> Diagnostics -> Examine

 from the application toolbar menu and find the field:

TWO_TASK

 in the

 $ENVIRONMENT$ block.

 This will display the database identifier as on the picture below.

Database

In TOAD or plain SQL level the database can be found by running

select name from v$database

There is only one row in that table and it can give you some addtional info, like when the instance was refreshed from production.

OA Framework

In Oracle Framework enable the 'FND:Diagnostics' profile for your user and re-log into the application. In the lower left corner of the page you will see a link to About Page same as on the picture below

This will take you to pages containing detailed information about your installation.

Switch to the 'Page Context' tab and it will not only identify your database but also provide all the information needed to log in via TOAD or SQL,  other than the user name and password.

This info can be either used to create TNSNAMES entry or used directly to connect to the database. In SQL*PLUS the syntax would be:

The same syntax can be used in other tools like i.e. Workflow