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!

Oracle EBS: Hiding Import Spreadsheet/Export Spreadsheet in Oracle Internet Expenses

So your company wants to implement a web-only version of Oracle Internet Expenses (i.e. no off-line completing expenses in Excel). The problem is that whenever a user logs into OIE they are presented with three buttons; Create Expense Report, Import Spreadsheet, and Export Spreadsheet (see below)

Figure 1: Expense Report Buttons (default view)

You'll notice that there are a lot of "personalize" links in Figure 1. In order to turn on personalisation you need to following the instructions in my "Changing Justification to Description" blog to update the system profile setting (click here).

In order to start the Personalization process click "Personalize Page" (top right):

Figure 2: Personalize Page

This screen shows you all the options you have when personalizing the screen. There are a lot of them. In order to find the one of we want to alter press Ctrl-F (Internet Explorer/ Firefox "Find") and enter "Import" as shown below:

Figure 3: Internet Explorer Find Dialog

After you click "Next" it will jump to the bottom of the screen and display:

Figure 4: Button Settings

As you can probably just make out from the screen shot (isn't the resizing in Blogger terrible?!) there are options for two buttons; "Button: Import Spreadsheet", and "Button: Export Spreadsheet". Click on the little pencil (edit) in the first line:

Figure 5: Available Personalization Options

Whilst this screen looks complicated it really isn't. What you basically have is options down the left side and across the top you have permission levels. You can change the settings just for you, for all users of your company, for all users of the site, for all users of the system, etc. Wherever you see "Inherit" the option is "use the default".

The bit we need to change is "Rendered". At the moment this is set to "true", we need to change this to "false". However, when you make the change a "warning" will appear that is usefully titled "Error" (just to really worry you):

Figure 6: Error when Changing Rendered Property (really a warning)

What this telling you is that if there are any children for the object you are making invisible they will be made invisible to. For buttons there aren't any children so this error is really just to put the wind up you and doesn't really have any effect.

Now you've made the change for this button, make the change for the other button and then go back to the Expenses page and as if by magic both buttons will be gone.

Oracle EBS: Changing "Justification" to "Description" in Internet Expenses

Now this is a pretty obscure one, I'd be amazed if there were too many people out there who had this problem!

Basically the issue is that I work for a company that rather than having the traditional pyramid structure of management with the people at the top never talking to anyone we have a much flatter structure and if you are brand new in through the door and fancy talking to the MD about something ... then that's fine (even encouraged).

In light of this different culture people who tested our i-Expenses system commented that the word "Justification" wasn't really in keeping with the companies culture. We also didn't require a "justification" to make an expense claim, but a Description would sure help us authorise it. It's a fairly simple change so why not?!

First of all you need to login and select the "System Administrator" responsibility, then go into "Profiles > System" and query for "Personalize Self-Service Defn%". By default this is set to "No", you need to set it to "Yes" (and remember to switch it off after you're done!):

Figure 1: Setting System Profile Options

Next you need to log out and choose the "Internet Expenses" responsibility and then go into the website. You'll notice that there is a "Personalize" link at the the top right of the page and various other links through the website. You can use these to change the way the page displays. You can also use these to make the site completely unusable - so be careful!

The "Description" we are trying to change is on the "Receipt-based Expenses" page. So you need to create a new expense claim and click through to that page (we won't be submitting, so it doesn't matter what you enter). Your screen will look something like this:

Figure 2: Cash and Other Expenses Page

A new link has appeared above the table beginning "Personalize" ... Clicking this link gets you to the field you need to edit quicker, but clicking any link and drilling down will work just as well (well, I'm assuming here ...!).

Now you will need to move down the list of options until you find "Message Text Input: Justification", then click the pencil (edit) to the right:

Figure 3: Editing "Justification"

Clicking on the pencil will display the following screen:

Figure 4: Changing Text

I have highlighted the entry boxes to change "Justification" for your entire organisation. If you just want to change it for your site (or even just for you) then use the boxes immediately to their left.

Clicking on the circular arrow next to the entry boxes restores them to "Inherit" which in effect deletes your change.

NOTE: When you look for these items again (say you wanted to change back to "Justification") then in Figure 3 where it shows "Justification" it would now show "Description".