Oracle EBS: Tidying Up Partially Paid Expenses (in Internet Expenses)

As I may have mentioned previously I'm nearing the end of an implementation of Oracle Internet Expenses for a medium-sized pharmaceutical company in the UK. One of the issues that came up is the interesting way expenses are handled in the current (pre-OIE) Accounts Payable world.

At the moment, once a month, the company receives a statement from Barclaycard which lists the total amount outstanding on each of the company credit cards for all staff in Head Office and across the UK in the Sales Force. Because the credit cards are "company pay" and the payment itself is handled outside of AP (i.e. Barclaycard isn't setup as a vendor/supplier in Oracle) one of the AP staff sits down and enters each card total as a "prepayment" in AP against that individuals supplier record. This takes a couple of days.

The individual then accounts for their expenses in the usual way (it's an Excel spreadsheet in case you're wondering), returns all the details to Finance and then the AP staff enter the expenses on the system in the usual way. Because the prepayments have been entered the employee will only actually be paid for any expenses they incurred that haven't been paid for on their company Barclaycard.

There are plenty of loop holes (not to mention lots of reports checking to make sure that loop-holes we know about aren't being abused) and the entire process is very time consuming.

The reason I mention the way the system is being used is that it causes a problem with Internet Expenses (although it's pretty unique ... who else does it this way?). The problem is that in AP the expense is recorded as being "partially paid". This means that when the user logs into Internet Expenses the expense will still appear in their "active" list at the top (and they have no way of clearing it).

After working with Oracle they suggested a fairly simple piece of SQL that would be "fix" the open expenses;

update ap_invoices_all i
set payment_status_flag = 'Y'
where i.payment_status_flag <> 'Y'
   and i.source = 'XpenseXpress'
   and i.invoice_type_lookup_code = 'EXPENSE REPORT'
   and payment_status_flag = 'P';

This marks the expense as "paid" from the AP side. It's almost certainly best to make sure that you have a list of the affected invoices before you make any changes (just in case);

select i.invoice_id, i.invoice_num

  from ap_invoices_all i

 where i.payment_status_flag <> 'Y'

   and i.source = 'XpenseXpress'

   and i.invoice_type_lookup_code = 'EXPENSE REPORT'

   and payment_status_flag = 'P';

It's a very specific circumstance, maybe it will be useful to others!