Noetix: Reporting AP Credit Card Transactions

If you are, like the company I work at, a user of both Oracle Payables and Oracle Internet Expenses you'll realise that the line between then is incredibly blurred. Especially if you have the situation where some departments/ areas use i-Expenses but other areas have an Excel-based expenses system (that is then sent to Finance who enter the data directly into Payables).

Oracle have made the situation a little worse by licensing Internet Expenses separately to Payables and it looks very much like Noetix have continued the trend.

What makes the Noetix decision even stranger is that the majority of the data for Internet Expenses is stored in the same tables as Payables in Oracle.

After looking at what was offered by the Noetix "Internet Expenses" module and our own reporting requirements the only "gap" that could be identified was for reporting on Credit Card Transactions (primarily around AP.AP_CREDIT_CARD_TRXNS_ALL).

Our reporting requirements are pretty simple, here is a simple list (with properties) of the columns we'd like to report on related to Credit Card Transactions;

CARD_NUMBER    VARCHAR2(30)
DISPUTE_DATE    DATE
EMAIL_ADDRESS    VARCHAR2(240)
EMPLOYEE_FULL_NAME    VARCHAR2(240)
EXPENSED_AMOUNT    NUMBER
LAST_UPDATE_DATE    DATE
MERCHANT_NAME    VARCHAR2(80)
REFERENCE_NUMBER    VARCHAR2(240)
TRANSACTION_AMOUNT    NUMBER
TRANSACTION_DATE    DATE
TRANSACTION_ID    NUMBER(15)

Converting these requirements into a Noetix View took a fair bit of trial and error but the script is available here. As it's quite a large one I won't be copy/pasting it below (as I normally would).

Any questions/ suggestions feel free to post a comment.

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.