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: Comparing Profile Options In Two Systems (i.e. Test vs Live)

Probably one of the most useful code snippets. By creating a database link between test and live systems you can directly compare the contents of tables and see which items have changed. This script specifically details the profile options that have changed as well as any new profile options that have appeared.

I find this especially useful for writing documentation, or just for capturing changes.

NOTE: For the purposes of this script the link between live and test is called APPSRO, the live system is called APPLIVE (and runs on ENTERPRISE) and the test system is called APPDEV(and runs on DEFIANT).

declare
  -- These constants have the server names and SIDs, they're necessary so that "normal" changes during the
  -- cloning process aren't reported as profile option changes.
  c_TEST_SERVERNAME constant varchar2(255) := 'APPDEV';
  c_TEST_SERVER     constant varchar2(255) := 'DEFIANT';
  c_LIVE_SERVERNAME constant varchar2(255) := 'APPLIVE';
  c_LIVE_SERVER     constant varchar2(255) := 'ENTERPRISE';

  -- This will produce a MASSIVE report if set to YES
  v_ShowUnchanged varchar2(3) := 'NO';

  cursor c_AllProfileOptions is
    select fpo.profile_option_id, fpo.profile_option_name
      from applsys.fnd_profile_options fpo;

  cursor c_TestOptions(p_OptionId in number) is
    select Application_Id, Profile_Option_Id, Level_Id, Level_Value, Profile_Option_Value
      from applsys.fnd_profile_option_values fpov
     where fpov.profile_option_id = p_OptionId
       and fpov.level_id in (10001, 10002, 10003); -- site/app/resp level

  cursor c_LiveOption(p_Application in number, p_ProfileOption in number, p_Level in number, p_LevelValue in number) is
    select profile_option_value
      from applsys.fnd_profile_option_values@APPSRO fpov
     where fpov.application_id = p_Application
       and fpov.profile_option_id = p_ProfileOption
       and fpov.level_id = p_Level
       and fpov.level_value = p_LevelValue;

  cursor c_NewOptions is
    select fpov1.Profile_Option_Id,
           fpov1.Level_Id,
           fpov1.Profile_Option_Value,
           fpov1.Application_Id,
           fpo.profile_option_name
      from applsys.fnd_profile_option_values fpov1,
           applsys.fnd_profile_options       fpo
     where fpov1.level_id in (10001, 10002, 10003)
       and fpov1.profile_option_id = fpo.profile_option_id
       and not exists
     (select 'x'
              from applsys.fnd_profile_option_values@APPSRO fpov2
             where fpov2.application_id = fpov1.application_id
               and fpov2.profile_option_id = fpov1.profile_option_id
               and fpov2.level_id = fpov1.level_id
               and fpov2.level_value = fpov1.level_value);

  function isDifferent(p_Text1 in varchar2, p_Text2 in varchar2) return boolean is
    v_Text1 varchar2(1024);
    v_Text2 varchar2(1024);
  begin
    v_Text1 := upper(nvl(p_Text1, ''));
    v_Text2 := upper(nvl(p_Text2, ''));
    if instr(v_Text1, c_TEST_SERVERNAME) > 0 then
      v_Text1 := replace(v_Text1, c_TEST_SERVERNAME, c_LIVE_SERVERNAME);
    end if;
    if instr(v_Text2, c_TEST_SERVERNAME) > 0 then
      v_Text2 := replace(v_Text2, c_TEST_SERVERNAME, c_LIVE_SERVERNAME);
    end if;
  
    if instr(v_Text1, c_TEST_SERVER) > 0 then
      v_Text1 := replace(v_Text1, c_TEST_SERVER, c_LIVE_SERVER);
    end if;
    if instr(v_Text2, c_TEST_SERVER) > 0 then
      v_Text2 := replace(v_Text2, c_TEST_SERVER, c_LIVE_SERVER);
    end if;
  
    return not(v_Text1 = v_Text2);
  end isDifferent;
begin
  rollback; -- because of the DB link you can get transaction problems if you run the process twice
  for v_Option in c_AllProfileOptions loop
    for v_Test in c_TestOptions(v_Option.Profile_Option_Id) loop
      for v_LiveValue in c_LiveOption(v_Test.Application_Id,
                                      v_Test.Profile_Option_Id,
                                      v_Test.Level_Id,
                                      v_Test.Level_Value) loop
        if isDifferent(v_LiveValue.profile_option_value,
                   v_Test.Profile_Option_Value) then
          dbms_output.put_line('CHANGED TESTVALUE ' ||
                               v_Test.Application_Id || ',' ||
                               v_Test.Profile_Option_Id || ',' ||
                               v_Test.Level_Id || ' (' ||
                               v_Option.Profile_Option_Name || ')=' ||
                               v_Test.Profile_Option_Value);
          dbms_output.put_line('CHANGED LIVEVALUE ' ||
                               v_Test.Application_Id || ',' ||
                               v_Test.Profile_Option_Id || ',' ||
                               v_Test.Level_Id || ' (' ||
                               v_Option.Profile_Option_Name || ')=' ||
                               v_LiveValue.profile_option_value);
        else
          if v_ShowUnchanged = 'YES' then
            dbms_output.put_line('NO CHANGE ' || v_Test.Application_Id || ',' ||
                                 v_Test.Profile_Option_Id || ',' ||
                                 v_Test.Level_Id || ' (' ||
                                 v_Option.Profile_Option_Name || ')=' ||
                                 v_Test.Profile_Option_Value);
          end if;
        end if;
      end loop;
    end loop;
  end loop;

  for v_NewOption in c_NewOptions loop
    dbms_output.put_line('NEW OPTION ' || v_NewOption.Application_Id || ',' ||
                         v_NewOption.Profile_Option_Id || ',' ||
                         v_NewOption.Level_Id || ' (' ||
                         v_NewOption.Profile_Option_Name || ')=' ||
                         v_NewOption.Profile_Option_Value);
  end loop;
  rollback;
end;