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.

Read More

Oracle EBS: Linking GL Lines to AP Invoice Distributions in R12

This blog post gives some guidance as to how to link a GL Journal line in Oracle R12 back to its associated AP Invoice Distribution.

NOTE: That this applies ONLY to R12 data - you will not be able to link old (pre-migration) data using this method you will need to have one linking rule for old records and one for new. Just another reason to do a reimplementation!

As always the easiest way to look at this is with a picture;

The image is click-able for a larger view.

The linking goes as follows;

GL_JE_Lines > XLA_AE_Lines > XLA_Distribution_Links > AP_Invoice_Distributions_All

In our Oracle implementation this didn't end up with a 1:1 relationship (which we had in R11) mainly due to the changes in the way AP invoices are stored (R11: Invoice + Distributions, R12: Invoice + Invoice Lines + Distributions).

Looking at a specific example from our R12 test system for journal “4673291 Purchase Invoices GBP”. This journal contains 5 GL records;

Looking at records in XLA_AE_Lines we also have 5 records;

But when we move on to the next table (XLA_DISTRIBTUION_LINKS);

We now have 6 lines (line 2 having been split into two).

We solved this problem but automatically populating a field in the GL with direct 1:1 linking information (hopefully I can add this as another Knoll).

Read More