Oracle EBS: Currency Conversion in Oracle Internet Expenses (OIE.K)

This blog post aims to explain where the conversion rate in Oracle Internet Expenses is derrived from and how setting a policy can have quite a significant effect on the total value returned.


The exchange rate used to convert a non-GBP expense claim back into GBP to be paid is handled in two parts within Oracle.

In the General Ledger (GL) there is a table of "Daily Rates";

This shows the currency conversion rate entered into the system for a specific day. As you can see in the screen shot above rather than entering truly "daily" rates the Finance department where I work have entered these dates on a monthly basis - this isn't really a problem so long as you accept that these are the rates that will be used.

The rate stored in the GL is the "base rate" that Internet Expenses uses for the conversion.

The next part is dependent on the conversion policy as configured in internet expenses;

At the moment for the operating unit show above is configured with an allowance of 5% so when the total is converted back to sterling there is a 5% "bonus" paid to the end user.

The effect of this 5% bonus is that on the 31st January 2009 rather than getting a little less back in GBP that they had spend in Euros they got back a little more;

This is "as expected".

The problem with the 5% is that the more you spend the more you make. So if you spend 500 EUR you will get back 525 EUR at the prevailing exchange rate in the GL.

Read More

Oracle EBS: Viewing General Ledger (GL) Daily Currency Conversion Rates

NOTE: This SQL works in version 11.5.10.2, your version might be different!

Many companies like to have "one version of the truth" (I say "like to" as we all know how hard this is to actually achieve!). One of the ways this can be achieved is a standardised set of currency conversion rates across an organisation.

The SQL in this blog post gives you a quick and easy report showing the currency conversion rates currently being used in the GL.


If your company is using Oracle Finance software (i.e. General Ledger) and the currency conversion rates are being loaded into the system (either automatically or manually) then it makes sense to publish this information out so that other parts of the company can use it.

The SQL below will show currency conversion rates (or a specified type) between two currencies and between two dates with the latest date conversion rate entered at the top.

SELECT FROM_CURRENCY,
       TO_CURRENCY,
       TO_CHAR(CONVERSION_DATE, 'DD-MON-YYYY') COVERSION_DATE,
       SHOW_CONVERSION_RATE,
       SHOW_INVERSE_CON_RATE
  FROM GL_DAILY_RATES_V
 WHERE status_code != 'D'
   and (FROM_CURRENCY = :FROM_CURRENCY)
   and (TO_CURRENCY = :TO_CURRENCY)
   and (CONVERSION_DATE >=
       to_date(:START_DATE, 'DD-MON-YYYY') AND
       CONVERSION_DATE < to_date(:END_DATE, 'DD-MON-YYYY')+1)
   and (USER_CONVERSION_TYPE = :USER_CONVERSION_TYPE)
 order by from_currency,
          to_currency,
          conversion_date desc,
          user_conversion_type

In order to run this SQL you need to specify five parameters;

FROM_CURRENCY: The currency you wish to convert from (i.e. EUR)
TO_CURRENCY: The currency code you wish to convert to (i.e. GBP)
START_DATE: This is the first date you want to see in the report in the format DD-MON-YYYY
END_DATE: This is the last date you want to see in the report in the format DD-MON-YYYY
USER_CONVERSION_TYPE: This will be dependant on your system, I'd recommend you look in the GL_DAILY_RATES_V view and find out the values used at your site and then plug one of those in.

Hopefully this will prove useful to people!

Read More

Oracle EBS: Updating Vendor and Vendor Site (Supplier) Information in Oracle e-Business Suite (11i)

This blog post covers how you update Vendor and Vendor Site records in Apps 11i. The included example shows how you clear the Tax Code associated with both types of record (this is particularly useful in the UK where our Sales Tax has changed).


The Problem
Changes quite often need to be made to the records in the PO_VENDORS and PO_VENDOR_SITES tables that involve changes to a large number of records . For example when the Sales Tax (VAT) rate changed from 17.5 to 15% in the UK each Vendor and Vendor Site had the old rate stored against it (this is the default rate that populates the dialog when you create an invoice for a vendor).


The problem with making these changes directly into the tables themselves is that Oracle a) does not support this, and b) doesn't publish the necessary information for us to work out exactly what doing something as simple as this actually entails.

The Solution
Summary/ Description
Oracle has provided two packages which contain routines that enable developers to make these changes themselves;

AP_VENDORS_PKG

 AP_VENDOR_SITES_PKG

The first thing to note is that there is no "delete" routine; if you want to delete a record you need to update it and set the "INACTIVE_DATE" field to the current date.

The two routines we are interested in are called INSERT_ROW and UPDATE_ROW. There are lots of other routines but they are basically checking/validation or display routines and the Lock routine which we don't want to touch.

Inserting a New Record using INSERT_ROW
Basically you need to pass in ALL the parameters listed (for Vendor Sites there is around forty) these are the direct values you want to end up in the columns in the table. The only field you don't need to worry about is the VENDOR_SITE_ID which is populated from the PO_VENDOR_SITES_S sequence (NEXTVAL).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Other than that the insert relies on Oracles validation (i.e. you won't be able to squeeze 12 characters into 10 character field, put a floating point number into an integer, etc) to stop things if you are inserting invalid data, but there really isn't anything to stop you doing something stupid like creating a new vendor who has only been active in the past or who has an inactive date before their active date.

If you pass in a Shipping Location then a new record will be created in the PO_LOCATION_ASSOCIATIONS table. This appears to be optional.

Updating a Record using UPDATE_ROW
This is the best example of how not to write an API you will ever come across. Basically you call it an pass in the ROWID and then new values for all the existing fields (fantastic potential to delete other peoples work here!).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Obviously if the ROWID you specify doesn't exist you will get an error, equally if you try and insert invalid data you will get an error as well.

If you pass in a Shipping Location then a call will be made to the ap_po_locn_association_pkg.update_row package to update the location ID you've specified. This appears to be optional.

Example;
There is a "sample" script here which performs an update on the VAT Code attached to suppliers.

Read More