Wednesday, November 26, 2008

Oracle PL/SQL: Pivoting a SQL Query within SQL

This blog post includes a rather simple script that will allow a developer to quickly pivot a single-table query so that rather than returning a single row will the data it returns multiple rows representing Column Name/Value combinations.

Let's assume we have a fairly simple table called PIVOTTEST this table, apart from displaying a distinct lack of imagination as far as naming goes, contains four columns ID (number), NAME (varchar2), DATE_CREATED (date) and DATE_LAST_UPDATED (date). It's created using the SQL:

create table PIVOTTEST
  ID                number,
  NAME              varchar2(60),
  DATE_CREATED      date,

In order to do our test let's put a few records into the table;

insert into pivottest(id, name, date_created, date_last_updated) values (1, 'ANDY', sysdate-200, sysdate - 5);
insert into pivottest(id, name, date_created, date_last_updated) values (2, 'BRETT', sysdate-190, sysdate - 4);
insert into pivottest(id, name, date_created, date_last_updated) values (3, 'COLIN', sysdate-180, sysdate - 3);
insert into pivottest(id, name, date_created, date_last_updated) values (4, 'IAN', sysdate-170, sysdate - 2);
insert into pivottest(id, name, date_created, date_last_updated) values (5, 'ADAM', sysdate-160, sysdate - 1);

If we do a SELECT * FROM PIVOTTEST WHERE ID = 1 we get a single record back:

 1 ANDY 10-MAY-2008 21-NOV-2008 

Yours dates will be different, and the format will be determined by your system settings but you get the point.

Assuming we'd prefer to have the results as multiple columns we would be aiming at something looking like:

 Column Name Column Value 

The easiest way to do this is to use multiple UNIONS and select each field we're interested in in turn:

select 1 ID, 'ID' Name, to_char(ID) Value from APPS.PIVOTTEST where ID = 1
select 2 ID, 'NAME' Name, NAME Value from APPS.PIVOTTEST where ID = 1
select 3 ID, 'DATE_CREATED' Name,to_char(DATE_CREATED) Value from APPS.PIVOTTEST where ID = 1
select 4 ID, 'DATE_LAST_UPDATED' Name, to_char(DATE_LAST_UPDATED) Value from APPS.PIVOTTEST where ID = 1

First thing; in order to allow the UNION to work the columns have to be of the same type. I've go for "character" just because it's the one practically every type has in common. In theory it will depend on the data you're working with but in practice you'll almost certainly want to use characters!

You'll notice that I've included the "WHERE ID =1" clause at the end to just give me the record I'm interested in and I've numbered the select statements so that when they are all joined together with the UNION the columns still come out in the order I'm expecting (if you remove the 1, 2, 3, 4 from the SELECT ... ID then the records come back in alphabetical column name order ... do you want that?).

Because the table details are held in Oracle you can actually do the same thing using a script:

-- Created on 25-Nov-2008 by APE06 
  -- Local variables here
  cursor c_Columns is
    select atc.COLUMN_ID,
      from all_tab_columns atc
     where atc.owner = 'APPS'
       and atc.TABLE_NAME = upper('PIVOTTEST')
     order by atc.COLUMN_ID;

  v_Where varchar2(255) := 'ID = 1';
  for v_Column in c_Columns loop
    dbms_output.put_line('select ' || v_Column.column_id ||
                         ' ID, ''' || v_Column.column_name ||
                         ''' Name, nvl(' || 
                         when v_Column.Data_Type = 'DATE' then 'to_char(' || v_Column.column_name || ',''DD-MON-YYYY'')' 
                         when v_Column.Data_Type = 'NUMBER' then 'to_char(' || v_Column.column_name || ')' 
                         else v_Column.column_name 
                         end ||
                         ', '''') Value from ' || v_Column.Owner || '.' ||
                         v_Column.Table_name || ' where ' || v_Where);
  end loop;

You need to change the OWNER (from APPS), the TABLE_NAME (from PIVOTTEST), and your where clause condition to return a single row (from ID = 1) and then you're ready to go.

You should also watch out for the "union" that gets tacked on the end ... you'll need to delete that (I could have added a "select '','' from dual where 3=1" to get rid of it but ... well you can do that yourselves now can't you? (I'm also using PL/SQL Developer a a test window which makes copy/pasting very easy so I don't really need 100% accuracy).

This script will generate the SQL to query the table as a Column Name/ Value combination - it also does a few other "nice" things like specifying the format for the date and displaying when the column has a null value.

I hope this helps!

Building an MSI to Deploy Fonts using wItem Installer (formerly Installer2Go)

This blog post gives you detailed instructions on how to build a simple MSI installer that will deploy fonts to Windows users. It is envisaged that this will be used in conjunction with Active Directory (Group Policy) to control the deployment.

Why Use an MSI?

The first thing to understand is that there are many ways of doing the deployment of fonts in Windows. Each machine you are seeking to deploy to has a "Fonts" directory under it's Windows installation folder. In a default Windows installation (XP, Vista, 7, or even 2003 Server) this installation folder is called C:\WINDOWS and the directory used for Fonts is C:\WINDOWS\Fonts.

It would be possible to write a script to copy the files into this directory, you could even run that script from a GPO, but using an MSI is much neater, creates a nice error trail if things go wrong (and they will in any large environment), and is much more controllable using Active Directory and Group Policy Objects (GPOs) for deployment. Unfortunately every company will have a few machines where the installation hasbeen done into a different directory (C:\WINNT for instance!), maybe even a couple where the installation is actually on the D: drive. Putting scripts in place to test all of this is a pain. Not just the writing of the script; it's the testing and proving that it works that consumes all the time.

In short; use an MSI. It really will save you time in the long run. And if you're going to use an MSI why not use one that's free? Hence my recommendation for wItem Installer (which was formerly called Installer2Go).

Getting The Software
Of course the obvious prerequisite is going to be obtaining the MSI-building software. Fairly simple; just visit this URL; (wItem Software)

The software is also available from other sources such as CNET if that link doesn't work.

Make sure you read the software licensing information when prompted by the installation!

The software is provided by wItem Software as "Freeware".

Using The Software
Once you've got the software installed start it up;

Installer2Go Version 4.2 (Freeware Version)
For size reasons I've colour-compressed the images, but hopefully they will still be good enough to give you some idea of the process.

As you can see I'm using version 4.2.5 of the software, as the MSI standard has changed very little (especially when all you need to do is install fonts!) I would expect future versions to be pretty much the same.

Step 1: Click on the "New" icon at the top left. You'll now see a whole heap of tabs;
New Project Multi-Tabbed Dialog
You should now fill in all the fields on the General Info tab. You don't have to, but it's tidy and in IT we like tidy. Just show anyone in IT your windows desktop with it's gazillion icons and watch them flinch ... As the installer is going to be for internal use only you don't have to spend quite as much time on this bit ... Oh. You didn't. You've already scrolled past this bit to;

Step 2: Click on the "Files" tab and expand the "Windows" folder;
"File" Tab
Select "Fonts" and then drag-and-drop the fonts you wish to install into this Window (they will usually have the extension .TTF). Once you've done that you're ready for the next step.

Step 3: Click the "Create Setup" (right-most) tab enter an Output Folder and a Filename and make sure that "Create Self-Extracting Executable that will contain your MSI file" is not checked.

Next click "Build" and your MSI will magically appear in your installation directory.

NOTE: At the end of an attended installation a dialog will be displayed promoting SDS Software. When you're doing an unattended installation no dialog is displayed and if you're deploying via Group Policy then it's the unattended installation that you'll be interested in!

Thursday, October 30, 2008

IBM Maximo: Re-Opening a Work Order in Maximo 4.1.1

This article gives you the instructions and Oracle PL/SQL Source code in order to change an existing Maximo 4 Work Order (WO) from a CLOSE state (where no changes are possible) back to HANDBACK state (where the record can be updated).

This code has been tested in an Oracle 8i database environment with Maximo 4.1.1 (Service pack 3) as the front end. When you run this code and look at the audit trace of a record a new entry will show that the Work Order has changed state. You will lose the records in the Equipment Hierarchy that show the Work Order has been previously closed. If you are in a tightly regulated (i.e. Pharmaceutical) environment you should carefully study this code, run it on a test system, and study the impact it has on your audit records.

NOTE: This code was written and tested using a Product called PL/SQL Developer (by AllRoundAutomations). This allows you to have output variables in blocks of PL/SQL code. If you are not running PL/SQL Developer then you will almost certainly need to modify this block of code. It will definitely not work in SQL * Plus.

This code is in two parts, the first is a simple Oracle PL/SQL script that takes two parameters; p_WorkOrder (the Work Order number) and :p_User (the user who the change should be audited against). When executed the procedure will return a message in the :p_Result variable. This will usually be "OK" meaning everything worked or an error message if it didn't.

It should be noted for validation purposes the User specified must exist as a record in the LABOR tables.

 -- Purpose: This procedure rolls back a Work Order from CLOSED state back into
 --  HANDBACK (this allows a normal maximo user to edit it).
 cursor c_getWOStatus is
   select wo.wonum,
   from   workorder wo
   where  wo.wonum  = :p_WorkOrder
   and    wo.status = 'CLOSE';

 v_ChangeDate  date;
 v_User        labor.laborcode%Type; -- lib_labor routines require a writable string
 v_RecordCount number;
 v_ChangeDate := SYSDATE; -- This ensures all changes have the same date/time stamp
 v_User       := :p_User;
 if not lib_labor.validateLaborCode(v_User) then
   :p_Result := 'ERROR: Labour code "' || v_User || '" does not exist';
   v_RecordCount := 0; -- keep track of the number of records updated
   for v_WorkOrder in c_getWOStatus loop
     v_RecordCount := v_RecordCount + 1;
     -- Insert an audit record to make sure that this change is "historied"
     values (:p_WorkOrder, 'HANDBACK', v_ChangeDate, v_User, v_WorkOrder.Glaccount);
     -- Remove the existing records in EQHIERARCHY
     delete from eqhierarchy
     where wonum = :p_WorkOrder;
     -- Update the Work Order itself
     update workorder wo
     set wo.status   = 'HANDBACK',
       wo.statusdate = v_ChangeDate,
       wo.changedate = v_ChangeDate
     where wo.wonum = :p_WorkOrder
     and wo.status  = 'CLOSE';
   end loop;

   -- Ensure that we return something as the result.
   if v_RecordCount = 1 then
     :p_Result := 'OK';
   elsif v_RecordCount > 1 then
     :p_Result := 'ERROR: Multiple workorders found for WO ' || :p_WorkOrder; -- *should* be impossible
     rollback; -- do not commit any changes!
     :p_Result := 'ERROR: Work Order ' || :p_WorkOrder || ' does not exist/ is not in CLOSED state';
   end if;
 end if;

-- Commit changes (if any) to the database
when others then
 :p_Result := 'ERROR: PL/SQL error' || SQLERRM || ' (' || SQLERRM || ')';

The second part of the code is the LIB_LABOR package. I created this simply to save myself some time validating labor records. There is no reason why the routines below couldn't just be copied and pasted into the script above and executed from within that (except, of course, the it's a terribly way to do ongoing development - but sometimes the terrible way to do something long term is also the way to get soemthing done quickly).

This package should be installed as your MAXIMO user (and should be accessible to the script running above):

create or replace package lib_labor is

function getEMail(
  p_LaborCode in varchar2) return varchar2;

function getFormattedContactDetails(
  p_LaborCode in varchar2,
  p_Format in varchar2) return varchar2;

function validateLaborCode(
  p_LaborCode in out varchar2) return Boolean;

end lib_labor;

create or replace package body lib_labor is

function getEMail(
  p_LaborCode in varchar2) return varchar2 as
  return getFormattedContactDetails(
    p_LaborCode => p_LaborCode,
    p_Format => '%EMAIL%');
end getEMail;

function getFormattedContactDetails(
  p_LaborCode in varchar2,
  p_Format in varchar2) return varchar2 as
  pragma autonomous_transaction;

  cursor c_Labor is
    select l.Name, l.CallId extension, l.pagepin email
    from labor l
    where l.laborcode = upper(p_LaborCode)
    and rownum = 1;

  v_Result varchar2(255);
  for v_Labor in c_Labor loop
    v_Result := Replace(p_Format, '%NAME%', v_Labor.Name);
    v_Result := Replace(v_Result, '%EXT%', v_Labor.extension);
    v_Result := Replace(v_Result, '%EMAIL%',;
    v_Result := Replace(v_Result, '%CODE%', p_LaborCode);
  end loop;
  return v_Result;
end getFormattedContactDetails;

function validateLaborCode(
  p_LaborCode in out varchar2) return Boolean as
  if p_LaborCode <> Upper(p_LaborCode) then
    p_LaborCode := Upper(p_LaborCode);
  end if;

  return (getFormattedContactDetails(p_LaborCode, '%CODE%') is not null);
end validateLaborCode;

end lib_labor;

Thursday, July 3, 2008

Classic PC: Installing Zool (Gremlin) Into DOS Box

This is a fairly quick and easy guide for running classic PC games, games intended to be run under DOS, under later versions of windows.

As an example I'll be taking the game "Zool" (from Gremlin Interactive, released in 1996) and using the original PC CD-ROM and a piece of software called DOSBox get this game running under Windows XP Home.

DOSBox is an Software Emulator that allows you to play old games on a modern system (such as Windows XP or even Windows Vista) or even play them on an alternate system such as under Ubuntu (or any other flavour of Linux).

The steps to follow are;

1. Download & Install DOSBox
Visit and download the Windows installer (the top link under "DOSBox"). The download link on this page takes you to a different website (SourceForge.Net) which hosts the pre-built installation files.

The size is approximately 1.2MB (should take about 10 seconds, maybe a minute if you're on dial up).

Install the software by double-clicking the DOSBox0.72-win32-installer.exe file (the "0.72" part of the filename will change depending on whatever the current version is).

2. Configure DOXBox
Find the DOSBox-X.XX group;

Figure 1: DOSBOX-0.72 Program Icons

Start the DOSBox application by clicking on it. Two new windows will appear, one called "DOSBox Status Window" and the other beginning "DOSBox 0.72".

Using Windows Explorer (or My Computer) go to your C:\ or D:\ drives and create a directory called "DOSGAMES". On my system I'm going to create and use a directory called "C:\DOSGAMES".

Next create a file in the new directory called TEST.TXT.

Now in the window titled "DOSBox 0.72" type the following lines;



In your window you should now have something looking like this;

Figure 2: Configuring DOSBox

You can see in the Figure above that TEST.TXT file you created earlier. Now you can delete that file.

Next you need to mount your CD ROM drive (so you can install the game). You need to find the letter associated with your CD ROM, on my system this is "F". After you've got the letter you need to enter the following lines (also in the window titled "DOSBox 0.72");

MOUNT D F:\ -t cdrom


You should now see a listing for the CD in the DOSBox window.

Clearly you're not going to want to do this every single time you want to play a game so under the DOSBox program group is an icon called "DOSBox.conf", run this and a file will open in Notepad. Scroll down to the bottom and add the two mount lines in the section titled "[autoexec]" (as shown below):

# Lines in this section will be run at startup.

mount c c:\dosgames

mount D F:\ -t cdrom

Close down DOSBox (click on the "X" at the top right of the window) and re-start it. You should see that the two MOUNT commands are automatically executed when you start the application.

3. Installing Zool
Switch to your CD ROM drive (in DOSBox) by typing;


There INSTALL.EXE file will install the application so type;

1 (for English)
1 (install the game)
C (the drive to install to)
Y (yes, you did mean C)
Y (yes, "C:\ZOOL" is the directory you want it to use)
(ANY KEY) (it's a classic game, let's re-live that classic joke too ... ;-)
ESC (exit)

Now the game is installed.

4. Running Zool!
Let's be honest, this is the important bit. Type the following;


Now you should see something like;

Figure 3: Zool!

Hope this has been useful for you, any questions leave a comment and I'll help where I can.

Wednesday, May 14, 2008

Oracle EBS: Automating Changes of Username

As as typically the case within any organisation that has been around for a while the rules behind generating a user name will change. In our case the change was from initials (ARP01 - Andy Pellew) to a sort of initial/surname combination (APE06 - yes, one day I'll get together with apes 1-5 and we'll take over the world).

Oracle will allow you to update a single users user name as everything is stored against the USER_ID (from the FND_USER table under APPLSYS) in the database.

The package below (XXXX_APPUSERMANAGER) allows you to specify an old user name and a new user name and does the switch for you (after validating that it should let you). The source code for the package is below;

create or replace package XXXX_APPUSERMANAGER is

  procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
                        p_ReturnCode    OUT NUMBER,
                        p_OldUserName   IN VARCHAR2 default '',
                        p_NewUserName   IN VARCHAR2 default '');


And here is the required code for the package body;

create or replace package body XXXX_APPUSERMANAGER is

  c_NOTFOUND constant number := -998;
  c_BLANK    constant number := -999;

  /* ********** ********** ********** ********** ********** --
  -- PRIVATE Routines (available only to this package)      --
  -- ********** ********** ********** ********** ********** */
  function private_GetUser(p_Username in varchar2) return number as
    cursor c_GetUser is
      select u.user_id
        from applsys.fnd_user u
       where upper(u.user_name) = upper(p_UserName);
    v_UserId applsys.fnd_user.user_id%type;
    open c_GetUser;
    fetch c_GetUser
      into v_UserId;
    close c_GetUser;
    if v_UserId is null then
      if length(p_UserName) = 0 then
        v_UserId := c_BLANK;
        v_UserId := c_NOTFOUND;
      end if;
    end if;
    return v_UserId;
  end private_GetUser;

  function private_emailSubjectTag return varchar2 as
    v_Database v$;
    select distinct name into v_Database from v$database;
    return '[User Admin - ' || nvl(v_Database, 'Null') || ']';
  end private_emailSubjectTag;

  function private_passwordGenerator return varchar2 as
    -- This is not massively secure, but it's secure enough (providing you don't have external users!)
    c_ALPHA constant varchar2(255) := '1DFC84XSW3ERTGBZA52KI9JM6LOPYH70N';
    v_Position number := mod(to_number(to_char(sysdate, 'SSSSS')),
    v_Length   number := mod(to_number(to_char(sysdate, 'SSSSS')), 4) + 6;
    return substr(c_ALPHA || c_ALPHA, v_Position, v_Length);

  /* ********** ********** ********** ********** ********** --
  -- PUBLIC Routines (available globally)                   --
  -- ********** ********** ********** ********** ********** */
  procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
                        p_ReturnCode    OUT NUMBER,
                        p_OldUserName   IN VARCHAR2 default '',
                        p_NewUserName   IN VARCHAR2 default '') as
    pragma autonomous_transaction;
    v_OldUserId    number;
    v_NewUserId    number;
    v_RowCount     number;
    v_Password     varchar2(255);
    v_EmailAddress applsys.fnd_user.email_address%TYPE;
    v_URL          varchar2(255) := 'http://%HOST%:8000/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=' ||
    v_Host         v$instance.HOST_NAME%TYPE;
    procedure LogMessage(p_Text in varchar2) as
      apps.fnd_file.put_line(apps.fnd_file.log, p_Text);
    v_OldUserId := private_GetUser(p_OldUserName);
    v_NewUserId := private_GetUser(p_NewUserName);
    LogMessage('Renaming ' || p_OldUserName || '(' || to_char(v_OldUserId) ||
               ') to ' || p_NewUserName || '(' || to_char(v_NewUserId) || ')');
    -- If the user details are blank raise an error
    if (v_OldUserId = c_BLANK) or (Length(trim(p_NewUserName)) = 0) then
                              'ERROR: You cannot rename from/to a blank username!');
    end if;
    -- If the old user is not found then raise an error
    if v_OldUserId = c_NOTFOUND then
                              'ERROR: The user "' || p_OldUsername ||
                              '" does not exist');
    end if;
    -- If the old user is not found then raise an error
    if upper(p_OldUserName) = upper(p_NewUserName) then
                              'ERROR: Old and new usernames are the same!');
    end if;
    -- Get the email address, if there is no email address raise an error
    select email_address
      into v_EmailAddress
      from applsys.fnd_user u
     where u.user_id = v_OldUserId;
    if v_EmailAddress is null then
                              'ERROR: Unable to update user "' ||
                              upper(p_OldUserName) ||
                              '" because they do not have a valid email address');
    end if;
    -- If the old user *is* found then raise an error
    if v_NewUserId <> c_NOTFOUND then
                              'ERROR: The user "' || p_NewUserName ||
                              '" already exists');
    end if;
    -- Perform the rename
    LogMessage('Updating user record (ID=' || to_char(v_OldUserId) || ')');
    update applsys.fnd_user u
       set u.user_name = upper(p_NewUserName)
     where u.user_id = v_OldUserId;
    v_RowCount := SQL%ROWCOUNT;
    -- If anything other than a single record has been updated raise an error
    if v_RowCount <> 1 then
                              'ERROR: Updating the user record has failed (' ||
                              to_char(v_RowCount) || ')');
    end if;
    -- Reset the users password. This is necessary because the encryption on the account includes
    -- the Username in the key (so when we change the username we make it impossible for the user
    -- to login - hence the resetting of the users password to a known value).
    LogMessage('Initialising Oracle Application Suite (required for Password Change)');
    execute immediate 'alter session set NLS_LANGUAGE = AMERICAN'; -- oddly we need these lines
    execute immediate 'alter session set NLS_TERRITORY = AMERICA';
    apps.fnd_global.apps_initialize(0, 20420, 1); -- switch to SYSADMIN under apps.
    v_Password := private_passwordGenerator; -- this is a pretty poor generator, but is almost certainly secure enough for us.
    -- Get the hostname (necessary to customise the URLs).
    select lower(host_name) into v_Host from v$instance where rownum = 1;
    LogMessage('Changing the users password');
    fnd_user_pkg.UpdateUser(x_user_name                  => upper(p_NewUserName),
                            x_owner                      => 'SEED',
                            x_unencrypted_password       => v_Password,
                            x_password_lifespan_days     => '',
                            x_password_lifespan_accesses => '',
                            x_password_accesses_left     => '');
    -- Notify the user their account name and password have changed
    XXXX_emailmanager.addEmail(p_From    => v_EmailAddress,
                               p_To      => v_EmailAddress,
                               p_Subject => private_emailSubjectTag ||
                                            ' User/Password Change Notification',
                               p_Body    => '

Your username (which was "' ||
                                            upper(p_OldUserName) ||
                                            '") has been changed to "' ||
                                            upper(p_NewUserName) ||
". Your password has also been reset to "' ||
                                            v_Password ||
                                            '" (without the quotes).

Please click the Oracle link below to logon and change your password:
' ||
                                            Replace(v_URL, '%HOST%', v_Host) ||

If you have any problems accessing the system please contact the IT Helpdesk.
    p_ReturnMessage := 'OK';
    p_ReturnCode    := 0;
    when others then
        p_ReturnMessage := '(' || TO_CHAR(SQLCODE) || ') ' || SQLERRM;
        p_ReturnCode    := 1;
        logMessage('999 ERROR: (' || TO_CHAR(SQLCODE) || '): ' || SQLERRM);
        XXXX_emailmanager.addEmail(p_From    => '',
                                   p_To      => '',
                                   p_Subject => private_emailSubjectTag ||
                                                ' Error',
                                   p_Body    => '999 ERROR: (' ||
                                                TO_CHAR(SQLCODE) || '): ' ||
                                                SQLERRM ||
Please raise this issue with the helpdesk.');
  end RenameUsers;


Now the bad news; this won't work (yes, I know that's a bit of a biggie). For copyright reasons several routines are missing from the package, the routine that sends the notification email to the user letting them know that their user name and password have been changed. In the packages these routines are referred to as:
XXXX_emailmanager.addEmail, and

The former allows you to add an e-mail to a queue, the latter processes all emails in the queue (rather than waiting for an automated process to pick the new email up).

These are custom packages that sit on top of two packages from oracle; BASE64ENCODER and EMAILER. Unfortunately the links I have for these packages are no longer working but as this is pretty standard functionality that most people would want to do it shouldn't be too hard to find a replacement (or the packages themselves - try google).

One day it's my goal to write an installation script to get everything installed nicely, but that goal is not going to be met today (and as I'm updating this post 3 years later and I still haven't done it it's looking like "never" is the window of time this work will be dropping into!).

Wednesday, April 16, 2008

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!

Friday, April 11, 2008

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).

  -- 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,
      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);
    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;
  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.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 || ')=' ||
          dbms_output.put_line('CHANGED LIVEVALUE ' ||
                               v_Test.Application_Id || ',' ||
                               v_Test.Profile_Option_Id || ',' ||
                               v_Test.Level_Id || ' (' ||
                               v_Option.Profile_Option_Name || ')=' ||
          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 || ')=' ||
          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 || ')=' ||
  end loop;

Friday, February 8, 2008

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.

Thursday, January 24, 2008

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".

Friday, January 4, 2008

Oracle EBS: Editing the FA Account Generator Process (Oracle Financials)

1. Editing Workflows using Oracle Workflow BuilderInstall "Oracle Workflow Builder" from the website. The version you will need depends on the version of Oracle Applications you are currently using. If you are unsure which version to use you can always raise a Service Request (SR) via Metalink and ask Oracle.

To open the workflow select “File > Open” from the menu:

Figure 1: Oracle Workflow Builder, Open Dialog

User and password are for the Oracle APPS account.

If you want a version of the Workflow other than the current version you can enter a date in the “Effective” entry box and it will give you the version of the workflow that was in effect on that date. Unless you’re doing a rollback to a previous version you can leave this blank.

Clicking “OK” will connect to the database and then display the “Show Item Types” dialog:

Figure 2: Oracle Workflow Builder, Show Item Types

The Workflow to edit is called “FA Account Generator”, in order to edit it you need to highlight it in the right-hand list (Hidden) and move it into the left-hand list (Visible) – as shown in Figure 2. Once you’ve selected it click “OK”.

NOW THIS IS IMPORTANT: If you work on a workflow while connected to the database any change you make is immediately committed to the server. As soon as you have opened a Workflow for editing you should save the Workflow locally and work on the local copy rather than working against a database (you can then open the local file and save it back to the database when you’re done).

The Workflow builder uses an MDI interface, a window will open titled “Navigator”:

Figure 3: Oracle Workflow Builder, Navigator

All the items shown in the figure above (beginning "Generate ...") are the workflows provided by Oracle. You should never edit these, if you do there is a risk (well, a practical certainty) of them being overwritten during an upgrade. If you need to make changes to a standard item (i.e. the process “Generate Book Level Account” in Figure 3) then you must create a copy of that item by right-clicking it, selecting “Copy” and then “Paste”. When prompted to enter the details prefix the name with a short-code, for example your company name or initials (there are length limitations, if you go over the length just remove characters from the end until it fits).

Fixing problems with the “Generate Accounts” process requires that you open (double-click) the “XXXX Generate Book Level Account” process (where XXXX is your chosen prefix). The original version (i.e. the “Generate Book Level Account” process) is below:

Figure 4: Generate Book Level Account Process

As you can see the "out of the box" process is fairly straight forward. The most common changes are usually to create "custom" branches coming off the "Get Book Account Name" process (where it shows "" above the arrow). Looking at the company I currently work for our customised process is:

Figure 5: Customised Generate Book Level Account Process

This shows separate flows for Proceeds of Sale (Loss/ Gain/ Clearing), Cost of Removal (Loss/ Gain/ Clearing) and Net Book Value Retired (Gain/ Loss). If you check the first “Assign to Value …” in each of the three flows you’ll see that these route to hard-coded constants:

Figure 6: Process Flow Hard-Coded Values (009) .

If a new branch needs to be created then you will need to copy most of the values from an existing branch (unfortunately there is no “copy/paste” for processes so you need to create new processes by dragging and dropping from the navigator and then set their properties).

2. Configuring Apps to use the New Workflow
After the work flows have been altered and you have uploaded them into Apps you need to configure Oracle E-Business Suite to stop using the Default workflow and switch to the customised version.

To do this you need to connect to Apps and switch to the “Fixed Assets Manager” responsibility. Expand the Financials > Flexfields > Key option in the tree view and select “Accounts” (see below):

Figure 7: Showing the Account Generation Options

Once you have selected this you are presented with an empty grid:

Figure 8: Account Generator Processes

Click on the “Find” button on the toolbar and search for “General Ledger%” and click “Find”. Select the entry with the correct structure (usually the company name)

Click “OK”.

The main Account Generator Process window will now populate with a list of Item Types and Processes. Scroll down and highlight the “FA Account Generator” Item Type:

Figure 9: Selecting the FA Account Generator

Click on the “…” (in the Process Name field) and you are presented with a list of processes from your workflow. Select the correct process (most likely “XXXX Generate Default Account”) and then click the save button on the toolbar.

Thursday, January 3, 2008

Oracle PL/SQL: Searching All VARCHAR2 Fields In A Schema

How frustrating is that? I've entered the data into the Oracle Front-end, the workflow has kicked off, an email has been sent, and *somewhere* in all this mess is a record of an error e-mail being received.

After 4 hours of trying to track down the problem (we actually have 3 emails being received - all "address not found") every 3 minutes ... for the past month and a half. We've got 48,000 emails in the Inbox at the moment. It takes 15 minutes to open in Outlook.

Where do you start?

Now I know the email address that is being used. I also know that the problem persists despite the server being taken down (for backup) every week therefore it *must* be stored somewhere in the database.

The following script runs through every single table in the database that contains a VARCHAR2 column and tries to find a specific string. It takes a while against an Oracle 11i schema (best to leave overnight ... maybe over a weekend if you have that many modules installed!).

  c_SEARCHTEXT constant varchar2(255) := 'SEARCH TEXT GOES IN HERE';

  cursor c_Tables is 
    select distinct atc.owner, atc.table_name
    from all_tab_columns atc
    where data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and not exists (select 'X' from all_views av where av.owner = atc.owner and av.view_name = atc.table_name);
  cursor c_Columns (p_Owner varchar2, p_TableName varchar2) is
    select distinct column_name
    from all_tab_columns
    where owner = p_Owner
    and data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and table_name = p_TableName;
  cv cv_typ;
  record_count integer;
  v_SQL varchar2(8124);
  for v_Table in c_Tables loop
    for v_Columns in c_Columns(v_table.owner, v_table.table_name) loop
      v_SQL := 
        'select count(*) ' || chr(13) ||
        'from ' || v_table.owner || '.' || v_Table.table_name || chr(13) ||
        'where upper(' || v_Columns.column_name || ') like upper(''%' || c_SEARCHTEXT || '%'')' || chr(13);
      open cv for
      fetch cv into record_count;
      if record_count > 0 then
        dbms_output.put_Line(v_Table.table_name || '.' || v_Columns.column_name || '***** FOUND *****');
      end if;
      close cv;
    end loop;
  end loop;

Now this is unoptimised so it will be sloooooooow. You can always change the initial select to prioritise schemas you are interested in, or add in a "length" check to make sure data of the correct length exists, but the biggest saving will be replacing the "dbms_output" call with something that will send you e-mail messages when it finds something (rather than waiting until the end when it's done!).