Oracle EBS: Viewing General Ledger (GL) Daily Currency Conversion Rates

NOTE: This SQL works in version 11.5.10.2, your version might be different!

Many companies like to have "one version of the truth" (I say "like to" as we all know how hard this is to actually achieve!). One of the ways this can be achieved is a standardised set of currency conversion rates across an organisation.

The SQL in this blog post gives you a quick and easy report showing the currency conversion rates currently being used in the GL.


If your company is using Oracle Finance software (i.e. General Ledger) and the currency conversion rates are being loaded into the system (either automatically or manually) then it makes sense to publish this information out so that other parts of the company can use it.

The SQL below will show currency conversion rates (or a specified type) between two currencies and between two dates with the latest date conversion rate entered at the top.

SELECT FROM_CURRENCY,
       TO_CURRENCY,
       TO_CHAR(CONVERSION_DATE, 'DD-MON-YYYY') COVERSION_DATE,
       SHOW_CONVERSION_RATE,
       SHOW_INVERSE_CON_RATE
  FROM GL_DAILY_RATES_V
 WHERE status_code != 'D'
   and (FROM_CURRENCY = :FROM_CURRENCY)
   and (TO_CURRENCY = :TO_CURRENCY)
   and (CONVERSION_DATE >=
       to_date(:START_DATE, 'DD-MON-YYYY') AND
       CONVERSION_DATE < to_date(:END_DATE, 'DD-MON-YYYY')+1)
   and (USER_CONVERSION_TYPE = :USER_CONVERSION_TYPE)
 order by from_currency,
          to_currency,
          conversion_date desc,
          user_conversion_type

In order to run this SQL you need to specify five parameters;

FROM_CURRENCY: The currency you wish to convert from (i.e. EUR)
TO_CURRENCY: The currency code you wish to convert to (i.e. GBP)
START_DATE: This is the first date you want to see in the report in the format DD-MON-YYYY
END_DATE: This is the last date you want to see in the report in the format DD-MON-YYYY
USER_CONVERSION_TYPE: This will be dependant on your system, I'd recommend you look in the GL_DAILY_RATES_V view and find out the values used at your site and then plug one of those in.

Hopefully this will prove useful to people!

Oracle EBS: Updating Vendor and Vendor Site (Supplier) Information in Oracle e-Business Suite (11i)

This blog post covers how you update Vendor and Vendor Site records in Apps 11i. The included example shows how you clear the Tax Code associated with both types of record (this is particularly useful in the UK where our Sales Tax has changed).


The Problem
Changes quite often need to be made to the records in the PO_VENDORS and PO_VENDOR_SITES tables that involve changes to a large number of records . For example when the Sales Tax (VAT) rate changed from 17.5 to 15% in the UK each Vendor and Vendor Site had the old rate stored against it (this is the default rate that populates the dialog when you create an invoice for a vendor).


The problem with making these changes directly into the tables themselves is that Oracle a) does not support this, and b) doesn't publish the necessary information for us to work out exactly what doing something as simple as this actually entails.

The Solution
Summary/ Description
Oracle has provided two packages which contain routines that enable developers to make these changes themselves;

AP_VENDORS_PKG

 AP_VENDOR_SITES_PKG

The first thing to note is that there is no "delete" routine; if you want to delete a record you need to update it and set the "INACTIVE_DATE" field to the current date.

The two routines we are interested in are called INSERT_ROW and UPDATE_ROW. There are lots of other routines but they are basically checking/validation or display routines and the Lock routine which we don't want to touch.

Inserting a New Record using INSERT_ROW
Basically you need to pass in ALL the parameters listed (for Vendor Sites there is around forty) these are the direct values you want to end up in the columns in the table. The only field you don't need to worry about is the VENDOR_SITE_ID which is populated from the PO_VENDOR_SITES_S sequence (NEXTVAL).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Other than that the insert relies on Oracles validation (i.e. you won't be able to squeeze 12 characters into 10 character field, put a floating point number into an integer, etc) to stop things if you are inserting invalid data, but there really isn't anything to stop you doing something stupid like creating a new vendor who has only been active in the past or who has an inactive date before their active date.

If you pass in a Shipping Location then a new record will be created in the PO_LOCATION_ASSOCIATIONS table. This appears to be optional.

Updating a Record using UPDATE_ROW
This is the best example of how not to write an API you will ever come across. Basically you call it an pass in the ROWID and then new values for all the existing fields (fantastic potential to delete other peoples work here!).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Obviously if the ROWID you specify doesn't exist you will get an error, equally if you try and insert invalid data you will get an error as well.

If you pass in a Shipping Location then a call will be made to the ap_po_locn_association_pkg.update_row package to update the location ID you've specified. This appears to be optional.

Example;
There is a "sample" script here which performs an update on the VAT Code attached to suppliers.

Installing Systems Centre Operation Manager (SCOM) 2007 with SP1

This guide is intended as a simple checklist of things you need to do in order to get a "working" SCOM test environment up and running under some form of "virtualising" technology (we use Microsoft's Hyper-V, but exactly the same instructions should work in VMware Workstation, VirtualBox etc).

In order to put this guide together I've made the following assumptions (well, OK, there weren't really any assumptions - this is how the environment I had to use was configured);

• Your server operating system is Windows 2003R2 SP2 32-bit. The links below will install the required software onto this version of Windows. If you are running a 64-bit version , or a different version of the server operating system (i.e. Windows 2008) then follow the links and see if you can find a x64 version - once again Google is your friend.
• Your server, users, etc are all part of an Active Directory domain. There is a group in Active Directory called "Domain Admins" that includes all the users who are Domain Administrators (i.e. your IT Department). The person running this installation has a "normal" (i.e. Standard User) domain account in addition to their Domain Admin account.
• The software is being installed by a Domain Administrator.
• You have either the installation CD's provided by your company or a MSDN account from which you can download the software.

The entire installation process (including the OS) will take around 3-4 hours - that's on a 2Gb Quad-Core Hyper-V host. You're mileage will almost certainly vary.

Installing the Prerequisites
When you try and install SCOM it will perform a series of checks against your system. If you're using a vanilla 2003 server than a great many of them will fail. Each piece of software listed below needs to be installed in order for SCOM to even begin to install.

• Install the base operating systems (i.e. Windows Server 2003R2) and add it to the Active Directory domain.
• Install the "Application Server" role (you need to go into "Manage your server" under the start menu). When asked make sure you include the FrontPage Server Extensions and ASP.NET.
• Run the SCOM Installer and it will tell you that MSXML6.0 is missing and will offer to install it for you. This is the only useful thing the installer seems to offer, if you try it again it will just start telling you things are missing and you have to fix them all yourself!
• Download and Install Microsoft .NET Framework 2.0 Service Pack 1 (x86).
• Bring up a command prompt (you need this to turn on ASP.NET 2.0).The instructions for doing this are also available if you get the details for the pre-requisite check that fails.
• Type "cd %WINDIR%\Microsoft.NET\Framework\v2.0.50727". As you can see the version of the framework is included in the directory path (so if you're using a different version you'll need to modify the path accordingly).
• Type "aspnet_regiis.exe –i –enable" (this performs the switch-on).
• Download and Install Microsoft .NET Framework 3.0 Service Pack 1 
• Install SQL Server 2005 Standard Edition (use your Domain Admin account as the Service Account, choose "Windows Authentication", otherwise accept the defaults).
• Download and install Microsoft SQL Server 2005 Service Pack 2 (you can do this before you reboot from the previous step if you want to). You should definitely reboot the machine before continuing.
• Download and install Windows PowerShell 1.0 English-Language Installation Package for Windows Server 2003 (KB926139)

Shutdown the VM and take a snapshot.

Installing Systems Centre Operation Manager (SCOM)After installing the prerequisites this is actually quite easy. The steps below are "guidance" and aren't a screen-by-screen description of what the installer is doing. Just accept the defaults until you are asked one of the questions below;

• Enter the group name as "DEVELOPERS" - technically you can pick anything but it's always good to make sure that your group is a good description of what you are using the system for and who set it up!
• Give the AD Group "Domain Admins" permission to administer the box.
• Select the locally installed copy of SQL Server (should be the only one available)
• When prompted again for a User/Password enter your STANDARD user account, not your Domain Administrator (if you enter the Domain Administrator account you will get a "warning" that it's not good practice - it's unlikely your production box will be setup this way so you should avoid doing it for testing).
• When prompted to enter account details for the SDK account enter your Domain Administrator details.
• Choose to use "Windows Authentication" for the Console.
• I would select "Don't use Windows Update" just because it's usually the simplest option, but I'd go with whatever your production system uses.
• Click "Next"/ accept the defaults until you click "Install" to install the software.

Shutdown the VM and take a snapshot.

Optional Extras: The Oracle ClientIf you're going to be using SCOM to access an Oracle database you will also need to install the Oracle client. For the purposes of this example I'm going to suggest the 10g client but that won't work if you need to monitor 8i and earlier systems. I'm not sure what subset of companies are running older versions of Oracle AND want to use something as "new" as SCOM to monitor them!

• Go to the URL Oracle 10.2 Client Download (this link is for Windows)
• Download "Oracle Database 10g Client Release 2 (10.2.0.1.0)", this is around 470MB.
• Uncompress the zip file into a local directory
• Run the setup.exe and select the "Administrator Client"
• Accept all the defaults and then click though to the "Install" button and then click that.
• Setup the client to work with your network (i.e. if you have a TNS Names Server then configure the client to use that, if you have a "shared TNS" on a server somewhere then set it up to use that, if you have a copy of TNS under Source Control then get that and copy it into the NETWORK/ADMIN directory, whatever works for you).
• Test the client.

Shutdown the VM and take a snapshot.

Oracle PL/SQL: How to Track Which Users Are Running a Report

As anyone managing a large system will tell you it's often not getting the data in that's a problem; it's getting it back out. A Report that is suitable for Finance is probably not exactly what they're looking for in the Warehouse. Pretty soon, especially if you have an end-user Reporting tool (like Business Objects), you're going to have hundreds of reports all solving individual problems out in the business.

Over time peoples needs change, some reports become obsolete and some new ones appear. Of course your users will not actually delete anything "just in case" it is needed in the future.

Now it's upgrade time and you've got 600+ reports and no idea who users what.

The point of the blog post is to suggest a way of writing reports that will build logging into the report so that whenever the report is run a record is kept (and these can then be archived off monthly, or moved into the Data Warehouse, or whatever).


Setting Up The Recording Package
Before we can make any changes to the reports to track their usage we need to define somewhere to store this data. For the purposes of this Knol I'm going to be using the Oracle e-Business Suite (11.5.10.2 to be exact) as the source for my reporting data AND as the storage location for the usage data.

To this end I'm going to create three tables in the APPS schema (yes, I know, it's far from ideal but this is only intended as an example giving the objects their own schema is a lot easier BUT then you have to sort out permissions and things start to get complicated - too complicated for this demo!).

The three tables I'm going to create are called; REPORTLOG, REPORTPARAMLOG, and REPORTRUNLOG. To reduce the volume of data produced I've split the necessary information up into three tables so, for example, if the user runs the same report 20 times with the same parameters I only need to store one copy of them. I hope this makes sense.

Anyway, that table structure is;

Figure 1: Log Information Storage Structure

The scripts to create the objects are;

REPORTRUNLOG
  CREATE TABLE "APPS"."REPORTRUNLOG" 
   ( "ID" NUMBER, 
 "REPORTLOG_ID" NUMBER, 
 "REPORTPARAMLOG_ID" NUMBER, 
 "RUNDATE" DATE, 
 "USERNAME" VARCHAR2(40 BYTE)
   );
REPORTPARAMLOG
  CREATE TABLE "APPS"."REPORTPARAMLOG" 
   ( "ID" NUMBER, 
 "PARAM01" VARCHAR2(80 BYTE), 
 "PARAM02" VARCHAR2(80 BYTE), 
 "PARAM03" VARCHAR2(80 BYTE), 
 "PARAM04" VARCHAR2(80 BYTE), 
 "PARAM05" VARCHAR2(80 BYTE), 
 "PARAM06" VARCHAR2(80 BYTE), 
 "PARAM07" VARCHAR2(80 BYTE), 
 "PARAM08" VARCHAR2(80 BYTE), 
 "PARAM09" VARCHAR2(80 BYTE), 
 "PARAM10" VARCHAR2(80 BYTE)
   );

REPORTLOG
  CREATE TABLE "APPS"."REPORTLOG" 
   ( "REPORTNAME" VARCHAR2(40 BYTE), 
 "ID" NUMBER, 
 "VERSION" VARCHAR2(20 BYTE)
   );

These scripts are taken from Oracle SQL Developer tool (selecting the objects and choosing "Export DDL" from the right-click menu).

I've not chosen to enforce the relationship between the tables in Oracle itself (foreign keys). I'm going to use a package to write the information into the tables and I'm happy that the validation I put into the package will enforce the links. This is really a design decision; you could go either way you probably should use foreign keys but it will depend on your environment.

Next comes the creation of the Oracle package;

CREATE OR REPLACE
PACKAGE REPORTLOGGER
AS
FUNCTION LogReport
  (
    p_ReportName IN VARCHAR2,
    p_Version    IN VARCHAR2,
    p_User       IN VARCHAR2,
    p_Param01    IN VARCHAR2 DEFAULT NULL,
    p_Param02    IN VARCHAR2 DEFAULT NULL,
    p_Param03    IN VARCHAR2 DEFAULT NULL,
    p_Param04    IN VARCHAR2 DEFAULT NULL,
    p_Param05    IN VARCHAR2 DEFAULT NULL,
    p_Param06    IN VARCHAR2 DEFAULT NULL,
    p_Param07    IN VARCHAR2 DEFAULT NULL,
    p_Param08    IN VARCHAR2 DEFAULT NULL,
    p_Param09    IN VARCHAR2 DEFAULT NULL,
    p_Param10    IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2;
END REPORTLOGGER;

This created the package header, the defaults for each parameter will mean that if (say) we only have 3 parameters we don't have to pass in 10 and give them null values in order to make the call. Also if this ever needs to be increased to 20 parameters all the existing modified reports should carry on working!

The next (and larger) part is the package body;

CREATE OR REPLACE
PACKAGE BODY REPORTLOGGER
AS
FUNCTION LogReport
  (
    p_ReportName IN VARCHAR2,
    p_Version    IN VARCHAR2,
    p_User       IN VARCHAR2,
    p_Param01    IN VARCHAR2 DEFAULT NULL,
    p_Param02    IN VARCHAR2 DEFAULT NULL,
    p_Param03    IN VARCHAR2 DEFAULT NULL,
    p_Param04    IN VARCHAR2 DEFAULT NULL,
    p_Param05    IN VARCHAR2 DEFAULT NULL,
    p_Param06    IN VARCHAR2 DEFAULT NULL,
    p_Param07    IN VARCHAR2 DEFAULT NULL,
    p_Param08    IN VARCHAR2 DEFAULT NULL,
    p_Param09    IN VARCHAR2 DEFAULT NULL,
    p_Param10    IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
AS
  PRAGMA autonomous_transaction;
  v_ExistsCount      NUMBER;
  v_ReportName       VARCHAR2(40);
  v_Version          VARCHAR2(20);
  v_User             VARCHAR2(40);
  v_Param01          VARCHAR2(80);
  v_Param02          VARCHAR2(80);
  v_Param03          VARCHAR2(80);
  v_Param04          VARCHAR2(80);
  v_Param05          VARCHAR2(80);
  v_Param06          VARCHAR2(80);
  v_Param07          VARCHAR2(80);
  v_Param08          VARCHAR2(80);
  v_Param09          VARCHAR2(80);
  v_Param10          VARCHAR2(80);
  v_RowCount         NUMBER;
  v_ReportRunLogId   NUMBER;
  v_ReportLogId      NUMBER;
  v_ReportParamlogId NUMBER;
BEGIN
  -- This section converts the passed in paramters (which could be of any length) to values that can
  -- be safely stored in hte table without worrying about "too big"-type errors!
  v_ReportName := upper(SUBSTR(p_reportname, 1, 40));
  v_Version    := upper(SUBSTR(p_Version, 1, 20));
  v_User       := upper(SUBSTR(p_User, 1, 40));
  v_Param01    := upper(SUBSTR(NVL(p_Param01, ''), 1, 80));
  v_Param02    := upper(SUBSTR(NVL(p_Param02, ''), 1, 80));
  v_Param03    := upper(SUBSTR(NVL(p_Param03, ''), 1, 80));
  v_Param04    := upper(SUBSTR(NVL(p_Param04, ''), 1, 80));
  v_Param05    := upper(SUBSTR(NVL(p_Param05, ''), 1, 80));
  v_Param06    := upper(SUBSTR(NVL(p_Param06, ''), 1, 80));
  v_Param07    := upper(SUBSTR(NVL(p_Param07, ''), 1, 80));
  v_Param08    := upper(SUBSTR(NVL(p_Param08, ''), 1, 80));
  v_Param09    := upper(SUBSTR(NVL(p_Param09, ''), 1, 80));
  v_Param10    := upper(SUBSTR(NVL(p_Param10, ''), 1, 80));
  -- Get the ID for the Report (with Version)
   SELECT COUNT(*)
     INTO v_RowCount
     FROM reportlog nrl
    WHERE nrl.reportname = v_ReportName
  AND nrl.version        = v_Version;
  IF v_RowCount          = 0 THEN
     SELECT NVL(MAX(id), 0)+1 INTO v_ReportLogId FROM reportlog;
     INSERT
       INTO reportlog
      (
        id        ,
        reportname,
        version
      )
      VALUES
      (
        v_ReportLogId,
        v_ReportName ,
        v_Version
      );
  ELSE
     SELECT id
       INTO v_ReportLogId
       FROM reportlog nrl
      WHERE nrl.reportname = v_ReportName
    AND nrl.version        = v_Version;
  END IF;
  -- Get the ID for the Report parameters
   SELECT COUNT(*)
     INTO v_RowCount
     FROM reportparamlog
    WHERE param01 = v_Param01
  AND param02     = v_Param02
  AND param03     = v_Param03
  AND param04     = v_Param04
  AND param05     = v_Param05
  AND param06     = v_Param06
  AND param07     = v_Param07
  AND param08     = v_Param08
  AND param09     = v_Param09
  AND param10     = v_Param10;
  IF v_RowCount   = 0 THEN
     SELECT NVL(MAX(id), 0)+1 INTO v_ReportParamlogId FROM reportparamlog;
     INSERT
       INTO reportparamlog
      (
        id     ,
        param01,
        param02,
        param03,
        param04,
        param05,
        param06,
        param07,
        param08,
        param09,
        param10
      )
      VALUES
      (
        v_ReportParamlogId,
        v_Param01         ,
        v_Param02         ,
        v_Param03         ,
        v_Param04         ,
        v_Param05         ,
        v_Param06         ,
        v_Param07         ,
        v_Param08         ,
        v_Param09         ,
        v_Param10
      );
  ELSE
     SELECT id
       INTO v_ReportParamlogId
       FROM reportparamlog
      WHERE param01 = v_Param01
    AND param02     = v_Param02
    AND param03     = v_Param03
    AND param04     = v_Param04
    AND param05     = v_Param05
    AND param06     = v_Param06
    AND param07     = v_Param07
    AND param08     = v_Param08
    AND param09     = v_Param09
    AND param10     = v_Param10;
  END IF;
  -- Insert a record into the REPORTRUNLOG table
   SELECT NVL(MAX(id), 0)+1
     INTO v_ReportRunLogId
     FROM reportrunlog;
   INSERT
     INTO reportrunlog
    (
      id               ,
      reportlog_id     ,
      reportparamlog_id,
      rundate          ,
      username
    )
    VALUES
    (
      v_ReportRunLogId  ,
      v_ReportLogId     ,
      v_ReportParamLogId,
      SYSDATE           ,
      v_User
    );
  COMMIT;
  RETURN 'OK';
EXCEPTION
WHEN OTHERS THEN
  RETURN SUBSTR
  (
    'ERROR:' || SQLERRM || '(' || SQLCODE || ')', 1, 255
  )
  ;
END LogReport;
END REPORTLOGGER;

Now a quick test will show if the package has been setup correctly;

SELECT reportlogger.LOGREPORT('test', '1', user)
FROM DUAL;

This should return "OK" (anything else and we need to look into the error).

The following sections look at the changes necessary to reports for the individual platforms. Because of the nature of the business I work in I'm only going to be listing reporting tools we actually use!

Adding Logging to a Microsoft SQL Reporting Services (SRS) Report 
The first thing I should probably mention is that this reporting tool comes with some pretty nice "off the shelf" reports when you're tracking usage. It's very new though and if your company is anything like mine only a tiny fraction of reporting is currently done with it. As things stand at the moment I'm trying to get the reporting data in one place and so I'm going to add logging. If you want, when you sit down to look at your data, multiple reports to look at and reconcile and that's your choice.

Open the Reporting Services Project (in Visual Studio).
Open the Individual Report to be logged.
Click on the "Data" tab and then click on the Dataset drop down and select "";

Figure 2: Creating a New Dataset

A dialog will appear, you only need the first tab ("Query"). You need to give the query a name, select your data source and then enter the query string as shown below. If you have multiple parameters you can add them on the end (up to 10 obviously!). You should enter parameters here in exactly the same way as you would in your reporting query. In Figure 3 below I've got no parameters for the report so I'm just adding the Name, Version and UserID);

Figure 3: Editing a dataset

Once this is done go to the "Report" menu item and select "Report Parameters".

Figure 4: Editing the Report Parameters

The first thing to do is to mark all the parameters you are passing to the logging routine as "Hidden". You don't want users to be able to change or to even see them. Then you need to set the "Default" value for the parameter, this will be the value passed to your routine. Reporting Services give you a list of "Globals" you can use, as you can see in Figure 4 there is one called "ReportName" which I'm going to use.

Version can't be populated automatically so I've decided to give this report a version of "1.0" as a static value. I'll need to remember to change that each time I do an update (but that's what pre-Go-Live review processes are there to check!).

Click "OK" to save the parameter settings and then click on the Preview tab and see if it works. you should be able to check in the database and see the fact the report has run is being logged.

Adding Logging to a Business Objects 6.5 Report
Unfortunately this isn't quite as "clean" as adding the logging to Reporting Services; it will add a new Variable to the users list which actually executes the logging. It's not necessary to add this to the report for it to work, but it is visible to the users (i.e. if they add it to the report they will see "OK" displayed). To encourage them not to use the report I've called the field "ZZDONOTUSE".

Open the Report in Business Objects.
Under the "Data" menu item select "New Data Provider" to bring up the "New Data Wizard".

Figure 5: Business Objects 6.5 New Data Wizard

Click "Begin".

Figure 6: Specify Data Access

Make sure "Others" is selected and then in the drop down select "Free-hand SQL" and click "Finish".

Figure 7: Free-Hand SQL

Enter the SQL;

SELECT 
  reportlogger.LOGREPORT('Off-Site Storage', '1', @Variable('BOUSER')) as zzDONOTUSE
FROM DUAL

Business Objects 6.5 does not appear to have a variable for the report name (feel free to comment and correct me if that's wrong!) so it's necessary to enter the name and version number for each report and to make sure you update it when the report is changed (again the importance of a rigorous Development > Production process cannot be underestimated).

If you now run the report and check the logging tables you will see a new record for this report execution.

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,
  DATE_LAST_UPDATED 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);
commit;

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

 ID NAME DATE_CREATED DATE_LAST_UPDATED
 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 
 ID 1 
 NAME ANDY 
 DATE_CREATED 10-MAY-2008 
 DATE_LAST_UPDATED 21-NOV-2008 

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
union
select 2 ID, 'NAME' Name, NAME Value from APPS.PIVOTTEST where ID = 1
union
select 3 ID, 'DATE_CREATED' Name,to_char(DATE_CREATED) Value from APPS.PIVOTTEST where ID = 1
union
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 
declare
  -- Local variables here
  cursor c_Columns is
    select atc.COLUMN_ID,
           atc.owner,
           atc.table_name,
           atc.COLUMN_NAME,
           atc.DATA_TYPE
      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';
begin
  for v_Column in c_Columns loop
    dbms_output.put_line('select ' || v_Column.column_id ||
                         ' ID, ''' || v_Column.column_name ||
                         ''' Name, nvl(' || 
                         case 
                         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);
    dbms_output.put_line('union');
  end loop;
end;

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;

http://www.witemsoft.com/togo/ (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!

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.

declare
 -- 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,
     wo.glaccount
   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;
begin
 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';
 else
   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"
     insert into WOSTATUS (WONUM, STATUS, CHANGEDATE, CHANGEBY, GLACCOUNT)
     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!
   else
     :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
 commit;
exception
when others then
 :p_Result := 'ERROR: PL/SQL error' || SQLERRM || ' (' || SQLERRM || ')';
 rollback;
end;

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
begin
  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);
begin
  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_Labor.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
begin
  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;