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.