Adventures of an Oracle EBS Developer

Building a generic upload page in OA Framework

2011-04-08T15:43:00.001+02:00

Let’s built a very simple page in OAF to enable end-users to upload files to the FND_LOBS table.
After they have been inserted in the table you have all kinds of options, use them as attachments or save them on the filesystem of the server, I use them as input for interfaces for example.

First of all we need some standard stuff from Oracle (actually the VO for FND_LOBS), so zip the contents of the $JAVA_TOP/oracle/apps/fnd/server and unzip in your JDEV_USER_HOME/myclasses directory.

Create a new OA Workspace and project…

Set the default package to something like xx.oracle.apps.fnd.upload.webui
I use webui as default package as i plan to create a custom page and controller which reside in the webui package.

Click next twice to get to step 3…

First the DBC file is needed, you can find this file on the server or via a URL, see this article for details.
Enter a EBS username and password and a valid application (short name) and responsibility (key).
Off course the EBS user you just entered should have access to this responsibility.

Create a new business component.

Leave the package name as is…

Press next and press finish on page 2.

Now we can import the files you copied from the server to myclasses.

Find your myclasses directory and goto myclasses/oracle/apps/fnd/server, there should be a server.xml file over there which we will import.

You can get following message, answer with Yes.

Your project should now look like below:

When you open oracle.apps.fnd.server you should see all standard FND objects, we are interested in FndLobsVO, which should be there as well.

Now let’s create the page from which we will upload the files.
Click File – New – Web Tier – OA Components and choose Page, name it something like xxUploadPG.

You will see the default Page Layout Region which i renamed to pageLayoutRegion.
Set the following properties:

AM Definition to oracle.apps.fnd.server.OAAttachmentsAM
Window title to something like: xx Web Upload
Title to something like: Web Upload

Add an item to the pageLayout region with Item Style messageFileUpload.

Enter properties as follows:

View Instance: FndLobsVO
Data Type: BLOB
View Attribute: FileData (This is the BLOB column FILE_DATA in FND_LOBS)
Prompt to something like: File Name

Add a region of type pageButtonBar to hold the buttons and add a Submit and Cancel button (item style submitButton) to this region so we are able to add code later on to submit the upload of the file.
If you run your page, it will look like this now:

To be able to cancel without any validation and return to the homepage we have to change 2 properties of the cancel button first, set Disable Server Side Validation and Disable Client Side Validation to True.

Add a controller to the pageLayout region.

NOTE: For this example we will add all code to the controller, in real situations you should consider moving parts of the code to the AM and/or EO to get cleaner code in the controller.

This it the complete code of the controller xxUploadCO.java, i hope the inline comments will do all the explanation:

/*===========================================================================+
|   Copyright (c) 2001, 2005 Oracle Corporation, Redwood Shores, CA, USA    |
|                         All rights reserved.                              |
+===========================================================================+
| HISTORY                                                                  |
| 1.0 RJUNGERIUS   07-APR-11 Initial creation                             |
+===========================================================================*/
package xx.oracle.apps.fnd.upload.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.OAWebBeanConstants;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.OAException;

import oracle.jbo.Row;

import oracle.apps.fnd.framework.webui.beans.message.OAMessageFileUploadBean;
import oracle.apps.fnd.framework.webui.OADataBoundValueViewObject;

/**
* Controller for ...
*/
public class xxUploadCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
        VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

/**
   * Layout and page setup logic for a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
    super.processRequest(pageContext, webBean);
    OAApplicationModule am = pageContext.getApplicationModule(webBean);
    OAViewObject vo = (OAViewObject)am.findViewObject("FndLobsVO");
    // Per the coding standards, this is the proper way to initialize a
    // VO that is used for both inserts and queries. See View Objects
    // in Detail in the Developer's Guide for additional information.
    if (!vo.isPreparedForExecution())
    {
      vo.executeQuery();
    }
    Row row = vo.createRow();
    vo.insertRow(row);
    // Required per OA Framework Model Coding Standard M69
    row.setNewRowState(Row.STATUS_INITIALIZED);

    // Initialize columns
    // Set column FILE_FORMAT to IGNORE, this column is used for the online help index
    row.setAttribute("FileFormat","IGNORE");
    // set upload date to current date and time
    row.setAttribute("UploadDate", am.getOADBTransaction().getCurrentDBDate());
    // set program name to the name of this module, i.e xxUpload
    // This way it is easy to identify that the record was inserted by this customization
    row.setAttribute("ProgramName", "xxUpload");
    // get a handle to the uploadbean
    OAMessageFileUploadBean uploadBean = (OAMessageFileUploadBean)webBean.findChildRecursive("fileName");
    // set file name display
    OADataBoundValueViewObject displayNameBoundValue = new OADataBoundValueViewObject(uploadBean, "FileName");
    uploadBean.setAttributeValue(DOWNLOAD_FILE_NAME,displayNameBoundValue);
    // set content type (MIME)
    OADataBoundValueViewObject contentBoundValue = new OADataBoundValueViewObject(uploadBean, "FileContentType");
    uploadBean.setAttributeValue(FILE_CONTENT_TYPE, contentBoundValue);
}

/**
   * Procedure to handle form submissions for form elements in
   * a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
    super.processFormRequest(pageContext, webBean);
    if (pageContext.getParameter("cancelButton") != null)
    {
      // Perform a rollback on the database
      OAApplicationModule am = pageContext.getApplicationModule(webBean);
      am.getTransaction().rollback();
      // cancel button pressed, return to homepage
      pageContext.forwardImmediately("OA.jsp?OAFunc=OAHOMEPAGE",
                                     null,
                                     OAWebBeanConstants.KEEP_MENU_CONTEXT,
                                     null,
                                     null,
                                     false, // do not retain AM
                                     OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
    }
    else if (pageContext.getParameter("submitButton") != null)
    {
      // Perform a commit on the database
      OAApplicationModule am = pageContext.getApplicationModule(webBean);
      am.getTransaction().commit();
      // Display upload confirmation message
      OAViewObject vo = (OAViewObject)am.findViewObject("FndLobsVO");
      oracle.jbo.domain.Number fileId = (oracle.jbo.domain.Number)vo.getCurrentRow().getAttribute("FileId");
      String fileName = (String)vo.getCurrentRow().getAttribute("FileName");
      OAException confirmMessage = new OAException("File "+fileName+" uploaded succesfully with ID "+fileId+".",OAException.CONFIRMATION);
      pageContext.putDialogMessage(confirmMessage);
      // return to upload page
      pageContext.forwardImmediately("OA.jsp?page=/xx/oracle/apps/fnd/upload/webui/xxUploadPG",
                                     null,
                                     OAWebBeanConstants.KEEP_MENU_CONTEXT,
                                     null,
                                     null,
                                     true, // retain AM
                                     OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
    }

}

}

Make and rebuild the complete project and run the page from within JDeveloper.
If you followed all steps then you should be able to select a file and after pressing Submit it will be inserted in FND_LOBS and the confirmation message is shown as below:

To deploy copy the controller java file and the page xml file to $JAVA_TOP/xx/oracle/apps/fnd/upload/webui.
Compile the java file.
Import the page with the following command:

java oracle.jrad.tools.xml.importer.XMLImporter \
$JAVA_TOP/xx/oracle/apps/fnd/upload/webui/xxUploadPG.xml \
-username apps -password <password> -rootdir $JAVA_TOP/xx/oracle/apps/fnd/upload/webui \
-rootPackage /xx/oracle/apps/fnd/upload/webui \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host_name>)(PORT=<port>))(CONNECT_DATA=(SID=<your_SID>)))"

Define a function via Functional Administrator responsibility like below:

That’s all folks, assign the function to an appropiate menu and users have access to it.
This has been done in 11i but it works exactly the same on R12 environments.

Customize the #HDR header attributes of notifications in a workflow

2011-03-16T08:54:00.001+01:00

Oracle uses a standard way to display header information and as such it is very easy to customize this part.
Let’s take the Purchase Requisition approval notification and replace the header attribute “Estimated Tax” with our own information or any other information already present in the workflow.

Open your workflow and open a message, in this case we use “Requisition Approved JRAD”.

If you click “Description”, “Requisition Total” or “Estimated Tax” then you will see that these are marked as #HDR_1, #HDR2, #HDR3, etc.
These internal names have special meaning and will be used to build up the right part of the notification header.
There are some more special internal names like #ATTACHMENTS, but in this blog we will focus on customizing the #HDR attributes.

You can find the definition of all the attributes, including #HDR, (per language) in the message as follows:

SELECT *
FROM   wf_message_attributes_tl
WHERE message_type = 'REQAPPRV'
AND    LANGUAGE='US'
AND    message_name = 'PO_REQ_APPROVED_JRAD';

Furthermore you can find the values of these attributes per notification with this SQL:

SELECT *
FROM wf_notification_attributes
WHERE notification_id = <ntf_id>
AND NAME = '#HDR_1';

This is the default #HDR_3, next we will replace it with another value.
For this example we will use a standard attribute “Emergency PO Number”, but you can also use a custom attribute off course.

#HDR3 replaced with our own information.

In all new notifications of this type you will now see the Emergency PO Number instead of the Estimated Tax.

That’s all there is to it, this way you can easily customize the header section of any notification.

Extending a VO: ReqLinesNotificationsVO in ReqLinesNotificationsRN

2010-10-07T20:36:00.005+02:00

The standard requisition approval notification is based on OA Framework regions and shows basic information for requisition lines. A common change request is to extend this line with additional information/columns.
So let’s find out how this is done.

First let’s find out which VO is behind the requisition lines information.
Make you sure you have the “About this page” link available and open up a requisition approval notification.

Click “About this page” and Click the Expand All link for the Page Definition.

Find the section where the Requisition Lines are defined.

As you can see the VO to extend will be ReqLinesNotificationsVO.

Click Collapse All on the Page Definition and open the Business Component References section.

Here you can see the location of the ReqLinesNotificationsVO, i.e. $JAVA_TOP/oracle/apps/icx/por/wf/server.
Get all the directories and files from this location and place them on your pc in the myclasses directory.

Define a new OA workspace and OA project to host the extension we will develop.
Set the default package in the OA Project definition to xx.oracle.apps.icx.por.wf.server

Create a new Business Components Package to host the extended VO

Make sure the package mentions xx.oracle.apps.icx.por.wf.server

Click Next and click Finish in Step 2 of 3.

Import the standard Business Components you copied from the server to your myclasses directory.
Go to myclasses and click the server.xml file in myclasses\oracle\apps\icx\por\wf\server.

If all is ok then your project will now like this:

Before we extend the VO we first need the original SQL so we can add additional columns to it.
Double-click the ReqLinesNotificationsVO.xml file and copy the complete SQL to the clipboard.

If all is ok then we can now define the new VO.

Make sure that you populate the Extends field with the original VO.
Proceed to step 5 and paste the SQL in the Query Statement field.
Now we can alter the SQL to include extra columns, for this example i will add the item category.
It is best to define PLSQL functions for the columns so that you can easily change the logic behind it later on without touching the VO again.

The function we will use to get this category is as follows:

CREATE OR REPLACE FUNCTION xx_category_fnc(p_requisition_line_id IN NUMBER)
RETURN VARCHAR2 IS
v_category mtl_categories_kfv.concatenated_segments%TYPE;
BEGIN
SELECT mcat.concatenated_segments
INTO   v_category
FROM   po_requisition_lines_all prl
        ,mtl_categories_kfv       mcat
WHERE prl.requisition_line_id = p_requisition_line_id
AND    prl.category_id = mcat.category_id;
RETURN v_category;
EXCEPTION
WHEN OTHERS THEN
    RETURN NULL;
END;

Add the new column “xx_category_fnd(requisition_line_id) AS XX_CATEGORY” as the last column of the query.
Click Test to check if the query is valid.

Continue to step 7 and make sure you generate java files for the view row class, this is to comply with OA Framework coding guidelines. You can uncheck the view object class as we will not add any custom code.

We are almost there, now we need to register the substitution of the original ReqLinesNotificationsVO with the newly created xxReqLinesNotificationsVO, double-click the xxReqLinesNotifications.jpx file and select the Substitutions.

On the left side select the ReqLinesNotificationsVO and on the right side select xxReqLinesNotificationsVO, click Add to add the substitution and click Ok to close the dialog.

Done (Click Save All to make sure everything is saved)!
Make and build the project and close JDeveloper.

Go to your myclasses directory and move the complete directory “xx\oracle\apps\icx\por\wf\server” to the $JAVA_TOP on the server. Also copy the xxReqLinesNotifications.jpx to the $JAVA_TOP/xx/oracle/apps/icx/por/wf/server directory on the server so we can run jpximport from the server.

Execute following command to import the jpx file (substitute the $variables with your own values):

java oracle.jrad.tools.xml.importer.JPXImporter \
$JAVA_TOP/xx/oracle/apps/icx/por/wf/server/xxReqLinesNotifications.jpx \
-username $APPS_NAME -password $APPS_PASSWORD \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$IP_ADDRESS)(PORT=$PORT_NUMBER))(CONNECT_DATA=(SID=$DATABASE_SID)))"

This should return a message:

Imported document : /oracle/apps/icx/por/wf/server/customizations/site/0/ReqLinesNotificationsVO
Import completed successfully

DONE!

Bounce the Apache server or the complete mid-tier and we can continue to define the new column in the EBS environment so it will be displayed.
To be able to personalize the page make sure the following profiles have been enabled on your account:
FND: Personalization Region Link Enabled
Personalize Self-Service Defn

Login to EBS and select a Requisition notification, click the “About this page” link so we can found out the attribute name of our newly defined column. You should be able to find your xxReqLinesNotificationsVO in the View Objects list now, click this one so we can see all attributes defined.

If all is ok you will see the xx_category column defined as attribute XxCategory:

Return to the notification and now click the Personalize link of the requisition lines table.

Click the Create Item link on the Table:

Select Site as level and choose Message Styled Text as item style, populate the ID with a unique name:

Populate the Prompt and the View Attribute and you are done, press Apply to finish.

Return to the notification and you should now see the newly defined column at the end of the line.

NOTE:
It could well be that your requirements are to only show the columns for specific organizations. You would assume you can do this by setting the Rendered property False on site level and only set it to True on specific organization levels.
Unfortunately this will not work when opening notifications from the homepage worklist as at that point oracle has not set any context or organization (it is actually unaware at that point which org_id needs to be set in the context).
There is quick and easy way to solve this yourself and not rely on context switching anymore.

See “Using SPEL in OA Framework to overcome “context” issue in notifications” for detailed information.

Using SPEL in OA Framework to overcome “context” issue in notifications

2010-10-07T20:32:00.001+02:00

When you extend a VO used in a notification and you want to make sure that the custom columns are only visible for certain operating units then you will quickly notice that this is not possible using personalization's on organization level.
Easiest way to see this in action is to open a notification from the homepage worklist (without opening/selecting any responsibility/function). In that case oracle has not set any context yet, so OA Framework will also not apply personalization's on any level and will default to site level. If any custom columns have been added to the VO and you would expect them to only show up for a certain organization then you will notice that it is not working and other organizations will also see the custom columns.

So how can we make sure that certain personalization's within notifications only show up for certain organizations without relying on oracle to set the context correctly ?

we can achieve this by using SPEL and adding a new attribute to the VO.

Add a new column to the VO query that will return a boolean that returns True/1 when you want the custom columns to render and False/0 when you don’t want them to render.

For example you can add a case statement to the VO query that will do the trick on org_id:
NOTE: I would not use this in a real situation but always use a PLSQL function to return the flag, that way you can easily change the requirements in PLSQL without touching the VO again.

CASE org_id
WHEN 65 THEN 1
WHEN 67 THEN 1
ELSE 0
END AS xx_display_flag

So in above case we want to make sure that the columns will display only for org_id 65 and 67.

We can now use this xx_display_flag column in SPEL to determine the rendered property of the custom columns.
Go to the Personalize Region screen again and select the custom column you want to apply this SPEL to.

In the Rendered property (on site level) set the value to SPEL and enter “${oa.<View Name>.<View Attribute>}”.
Apply the change and you will immediately see that the custom column(s) will now only render when the data returned by the VO is for a org_id that makes the flag return True/1.
There is no need anymore to rely on oracle to set the context correctly.

Get contents of DBC file without access to server

2010-08-26T09:00:00.001+02:00

During OA Framework development you need to get the DBC file of the environment you want to work on.
This file is located in $FND_TOP/secure for 11i and $FND_SECURE for R12.

But if you don’t have access to the server it is also possible to get the DBC file in a convenient way.
Open up a webbrowser and go to the homepage URL and append “/OA_HTML/jsp/fnd/aoljtest.jsp” to it.
(so for example: http://vis11.server.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp)

Fill in the details and click “Test”.
If all your details are ok you will get an overview page with details, click on the link “Enter AOL/J Setup Test”.

From this point find the menu entry “Locate DBC File” and click it…

On the right side you will get the contents of the DBC file.
Copy/paste this to a <sid>.dbc file and place it in your JDeveloper JDEV_USER_HOME/dbc_files/secure.

Use a sequence value when saving a record in OA Framework

2010-08-17T10:55:00.001+02:00

Most tables in EBS have a unique identifier which can be generated using a database sequence.
For custom tables i also use the same approach.
In Oracle Forms this is relatively easy to achieve, how about OA Framework ?

If you use an EO (Entity Object) then it is also very easy to achieve, just put the code in the setter method of the required column:

public void setFileId(Number value) {
   if (value == null) {
        OADBTransaction t = getOADBTransaction();
        value = t.getSequenceValue("<sequence_name>");
    }

    setAttributeInternal(FILEID, value);
}

If you need to do it in the PR of PFR then use:

OAApplicationModule ap = pageContext.getApplicationModule(webBean);
// Assign a new sequence value to a row attribute for example
row.setAttribute("FileId",ap.getSequenceValue("<sequence_name>"));

Extending a controller in OA Framework

2010-08-04T21:14:00.001+02:00

As an example i will take the change request i received to make the Oracle Internet Expense Audit page “security rule aware”.
Currently you will get all the expense reports in there but we would like to create different copies of the responsibility to which we assign different (GL) security rules on the cost centre segment:

XX OIE Internal Audit (CC 0300-0399), will only see expense reports from cost centre 0300 - 0399
XX OIE Internal Audit (CC 0400-0499), will only see expense reports from cost centre 0400 - 0499
etc

To achieve this functionality the where clause of the query behind the results table in this page needs to be modified so it will check if the flex_concatenated column of ap_expense_report_headers_all table is between the assigned flex value security rules ranges.
To extend the OA Framework page you have 2 options, extend the VO or extend/create the controller behind the results table in the page.
Extending a VO is on a global level and you pick this option if you want to add extra columns.
Extending/creating a controller can be assigned on all levels (function/site/organization/responsibility/etc) and you can change the where clause in the ProcessRequest method, so extending the controller is definitely the way to go…

Open up the standard Internet Expenses Internal Audit screen and click “About this page” in the lower left corner.
You can already see that it is the “Search Results” table we are after…

Click “Expand All” below Page Definition

Now find the Search Results Table in the page definition list…

As you see there is already a controller assigned to the Search Results table, i.e. SearchTableCO.
So we need to extend this controller to be able to add a custom where clause to the query.

Extending a controller is merely creating a new java file and as such could be done using any editor if you know all the details. I always rely on JDeveloper.
We only need the source file of SearchTableCO, so that will be $JAVA_TOP/oracle/apps/ap/oie/audit/webui/SearchTableCO.class.
So login on your server and ftp the file over to JDEV_USER_HOME\myclasses\oracle\apps\ap\oie\audit\webui\SearchTableCO.class.

Open up JDeveloper and create a new OA Workspace and project, follow below screenshots:

Extended objects need to be placed in a custom package, usually it will be the original package prefixed with xx, i.e. xx.oracle.apps.ap.oie.audit.webui

Move on to step 3 and specify a valid DBC file for the environment you are testing on (get this one from the server at $FND_TOP/secure and place it in JDEV_USER_HOME\dbc_files\secure).
Furthermore specify a valid applications user/password which has the correct responsibility assigned
and enter the correct application (SQLAP) and responsibility key.

Right-click the newly created project and choose “New Class…”
Enter a name for the new class, i.e. xxSearchTableCO and check that the package defaults to the custom package of the project.
Use the browse button from “Extends” to lookup up the class SearchTableCO under oracle/apps/ap/oie/audit/webui as we want
to extend the original controller class from the Search Table. Click OK to create the new class file.

Now that we have created a new class file that extends the original controller we can override the processRequest method.
In green you can see what needs to be added to the original file JDeveloper created for you:

package xx.oracle.apps.ap.oie.audit.webui;
import oracle.apps.ap.oie.audit.webui.SearchTableCO;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;

public class xxSearchTableCO extends SearchTableCO
{
    public void processRequest(OAPageContext pageContext, OAWebBean webBean)
    {
        super.processRequest(pageContext, webBean);
          // Get a handle to the AuditReportHeadersVO VO
          OAApplicationModule ap = pageContext.getApplicationModule(webBean);
          OAViewObject vo = (OAViewObject)ap.findViewObject("AuditReportHeadersVO");
          // extend where clause
          vo.setWhereClause("((SELECT COUNT(1) FROM   fnd_responsibility fr,fnd_flex_value_rule_usages ffvru,fnd_flex_value_rules ffvr"+
                            " WHERE fr.responsibility_id = ffvru.responsibility_id AND ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id) = 0) OR"+
                            " (((SELECT COUNT(1) FROM fnd_responsibility fr, fnd_flex_value_rule_usages ffvru, fnd_flex_value_rules ffvr,"+
                            " fnd_flex_value_rule_lines ffvrl WHERE fr.responsibility_id = ffvru.responsibility_id AND"+
                            " ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id AND"+
                            " ffvrl.include_exclude_indicator = 'E' AND"+
                            " flex_concatenated BETWEEN ffvrl.flex_value_low AND ffvrl.flex_value_high) = 0) AND"+
                            " ((SELECT COUNT(1) FROM fnd_responsibility fr, fnd_flex_value_rule_usages ffvru, fnd_flex_value_rules ffvr,"+
                            " fnd_flex_value_rule_lines ffvrl WHERE fr.responsibility_id = ffvru.responsibility_id AND"+
                            " ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id AND"+
                            " ffvrl.include_exclude_indicator = 'I' AND"+
                            " flex_concatenated BETWEEN ffvrl.flex_value_low AND ffvrl.flex_value_high) > 0))");
    }
}

NOTE: In above where clause below checks are done:
(maybe it can be done in a smarter way, let me know!)

Check if there are NO security rules assigned to the current responsibility (count = 0)
OR
Check if there are NO EXCLUDE lines in the security rules assigned to the current responsibility (count = 0) for the cost centre of the expense report
AND
Check if there ARE INCLUDE lines in the security rules assigned to the current responsibility (count > 0) for the cost centre report

So the expense report is shown in the search table if check 1 is true OR (check 2 AND check 3 are true).

Try if the class file compiles correctly, right-click on the project and Make and Rebuild the project.

To deploy it is very simple, copy the xxSearchTableCO.java file to your server in directory $JAVA_TOP/xx/oracle/apps/ap/oie/audit/webui.
Open a terminal session on your server, set your environment and compile the java file.

Last step to take is to assign the new controller class to the Search Results table.
Login into Oracle E-business Suite and click the Expenses Internal Audit responsibility. On the main page click “Personalize Search Results” link.

Click the Personalize Pencil on Table Search Results.

We want the extension to be applied on organization level so we enter the new controller class xx.oracle.apps.ap.oie.audit.webui.xxSearchTableCO in the organization entry field. After you applied this change you can return to the personalization and you should see that the Result/Source column now shows your new controller class to be the active one on Organization level.

Bounce the Apache server and you are done!
Create copies of the Internal Audit responsibility and assign different security rules to them to get the desired results.

Making sure (some) workflow notifications do not get sent via email

2010-07-29T08:25:00.001+02:00

When you define a workflow mailer on a test instance and/or on production you usually don’t want the complete history of pending notifications to be emailed.
To avoid this you need to set the mail status of the notifications in WF_NOTIFICATIONS to SENT
and rebuild the WF_NOTIFICATION_OUT queue/table (wfntfqup.sql).
What i found more convenient is to run a script to only dequeue the messages that you don’t want to sent out, so no need to do a complete rebuild.

-- Step 1:

-- Clear mail status for ALL pending HR (item_type is HRSSA) notifications older then 1 day.
-- This will make sure they will not end up in the WF_NOTIFICATION_OUT queue in case of a rebuild.

UPDATE wf_notifications
SET    mail_status = 'SENT'
WHERE message_type = 'HRSSA'
AND    TRUNC(begin_date) < TRUNC(SYSDATE)
AND    mail_status IN ('MAIL', 'FAILED');

COMMIT;

-- Step 2:

-- Run following pl/sql code from SQL*Plus on the server (logged in as APPS).
-- This will dequeue all pending HRSSA notifications older then 1 day from the mailer queue
-- NOTE: Enable serveroutput as it will display the number of notifications it will dequeue.

SET SERVEROUTPUT ON

DECLARE
q_version VARCHAR2(200);
event sys.aq$_jms_text_message;
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;

TYPE msgidtab IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;

msg_id msgidtab;
dq_msg_id RAW(16);
q_name VARCHAR2(60);

CURSOR target_msgids IS
    SELECT wfno.msg_id
    FROM applsys.aq$wf_notification_out wfno
    WHERE wfno.corr_id LIKE 'APPS:HRSSA:%'
    AND TRUNC(wfno.enq_time) < TRUNC(SYSDATE)
    AND wfno.msg_state = 'READY'
    ;

BEGIN
q_name := 'APPLSYS.WF_NOTIFICATION_OUT';
dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
dequeue_options.wait := dbms_aq.no_wait;
dequeue_options.navigation := dbms_aq.first_message;
dequeue_options.consumer_name := 'WF_NOTIFICATION_OUT';

OPEN target_msgids;
FETCH target_msgids BULK COLLECT
INTO msg_id;
CLOSE target_msgids;
--
dbms_output.put_line('Dequeue of '||msg_id.COUNT||' messages.');
--
IF (msg_id.COUNT > 0) THEN
    FOR i IN msg_id.FIRST .. msg_id.LAST LOOP dequeue_options.msgid := msg_id(i);
      dbms_aq.dequeue(queue_name         => q_name
                       ,dequeue_options    => dequeue_options
                       ,message_properties => message_properties
                       ,payload            => event
                       ,msgid              => dq_msg_id);
      dequeue_options.navigation := dbms_aq.next_message;
    END LOOP;
    COMMIT;
END IF;
EXCEPTION
--timeout will fall to here
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
IF (target_msgids%ISOPEN) THEN
    CLOSE target_msgids;
END IF;
END;
/

NOTE: Dequeuing can take a few minutes to be processed so check the wf_notification_out table to make sure no messages are pending before starting the workflow mailer (select count(1) from wf_notification_out where corrid like ‘APPS:HRSSA%’ and state=0;)

SQL to list profile values on all levels

2010-07-26T16:27:00.001+02:00

SELECT fpot.user_profile_option_name "Profile"
       ,fpov.profile_option_value "Value"
       ,decode(fpov.level_id
             ,10001
             ,'Site'
             ,10002
             ,'Application'
             ,10003
             ,'Responsibility'
             ,10004
             ,'User'
             ,'UNKNOWN') "Level"
       ,decode(fpov.level_id
             ,10002
             ,fat.application_name
             ,10003
             ,frt.responsibility_name
             ,10004
             ,fu.user_name
             ,'n/a') "Level value"
FROM   applsys.fnd_application_tl        fat
      ,applsys.fnd_user                  fu
      ,applsys.fnd_responsibility_tl     frt
      ,applsys.fnd_profile_option_values fpov
      ,applsys.fnd_profile_options       fpo
      ,applsys.fnd_profile_options_tl    fpot
WHERE upper(fpot.user_profile_option_name) LIKE upper('%&v_profile%')
AND    fpot.profile_option_name = fpo.profile_option_name
AND    fpot.LANGUAGE = 'US'
AND    fpo.application_id = fpov.application_id(+)
AND    fpo.profile_option_id = fpov.profile_option_id(+)
AND    fpov.level_value = frt.responsibility_id(+)
AND    frt.LANGUAGE(+) = 'US'
AND    fpov.level_value = fu.user_id(+)
AND    fpov.level_value = fat.application_id(+)
AND    fat.LANGUAGE(+) = 'US'
ORDER BY "Profile"
          ,"Level"
          ,"Level value"
          ,"Value";

Flexfield security rule assignments to responsibility

2010-06-24T13:58:00.001+02:00

The following query will list all the security rules assigned to a responsibility

SELECT fr.responsibility_id
      ,fr.responsibility_key
      ,fr.responsibility_name
      ,ffvr.flex_value_rule_name
      ,ffvrl.include_exclude_indicator
      ,ffvrl.flex_value_low
      ,ffvrl.flex_value_high
FROM   fnd_responsibility_vl      fr
      ,fnd_flex_value_rule_usages ffvru
      ,fnd_flex_value_rules_vl    ffvr
      ,fnd_flex_value_rule_lines ffvrl
WHERE fr.responsibility_id = ffvru.responsibility_id
AND    ffvru.flex_value_rule_id = ffvr.flex_value_rule_id
AND    ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id
AND    fr.responsibility_name = '&resp_name’
ORDER BY fr.responsibility_id
         ,ffvr.flex_value_rule_name
         ,ffvrl.include_exclude_indicator DESC
         ,ffvrl.flex_value_low
         ,ffvrl.flex_value_high;

Join the WF_NOTIFICATIONS and WF_NOTIFICATION_OUT table

2010-03-16T12:56:00.002+01:00

SELECT wno.*
FROM   (SELECT o.deq_time deq_time
              ,o.enq_time enq_time
              ,decode(o.state
                     ,0
                     ,'PENDING'
                     ,2
                     ,'EMAIL SENT'
                     ,3
                     ,'EXCEPTION'
                     ,o.state) state
              ,to_number((SELECT str_value
                         FROM   TABLE(o.user_data.header.properties)
                         WHERE NAME = 'NOTIFICATION_ID')) p_notification_id
              ,(SELECT str_value
                FROM   TABLE(o.user_data.header.properties)
                WHERE NAME = 'ROLE') p_Role
              ,o.user_data
        FROM   applsys.wf_notification_out o) wno
      ,applsys.wf_notifications wn
WHERE wn.notification_id = wno.p_notification_id;

Detailed login information via ICX_SESSIONS

2010-03-02T14:19:00.001+01:00

Often it is quite handy to be able to see which user logged in under which responsibility and used which function.
In a large environment it is even more convenient to limit this information per HR business group.

-- Select all logins from a certain BG
-- Select user, responsibility and function used
-- First connect and last connect
SELECT to_char(first_connect, 'dd-mm-yyyy hh24:mi:ss') "First Connect"
       ,to_char(last_connect, 'dd-mm-yyyy hh24:mi:ss') "Last Connect"
       ,user_name
       ,p.email_address
       ,p.employee_number
       ,(SELECT description
        FROM   fnd_responsibility_tl r
        WHERE r.responsibility_id = a.responsibility_id
        AND    r.LANGUAGE = 'US') "Responsibility"
       ,(SELECT user_function_name
        FROM   fnd_form_functions_vl fffv
        WHERE (fffv.function_id = a.function_id)) "Current Function"
FROM   icx_sessions        a
      ,fnd_user            b
      ,per_people_x        p
      ,per_business_groups bg
WHERE a.user_id = b.user_id
AND    b.employee_id = p.person_id
AND    p.business_group_id = bg.business_group_id
AND    bg.NAME = '&business_group'
-- Uncomment to only list today
--AND    TRUNC(last_connect)=TRUNC(SYSDATE)
ORDER BY first_connect DESC;

How to enable auditing on custom tables

2010-01-20T21:38:00.001+01:00

As i am currently working for a company that needs to comply with SOX (Sarbanes Oxley act) we are implementing standard Oracle auditing on almost all setup related tables. Lately also the question was asked to enable auditing on custom tables, as the contents of these tables becomes more important.

To enable auditing on custom tables you first have to register the table, its columns and the primary key.

I am using the following PLSQL block to automatically register the table and all its columns by using the dba_tab_columns view:

DECLARE
CURSOR c1 IS
    SELECT column_name
          ,table_name
          ,column_id
          ,data_type
          ,data_length
          ,nullable
    FROM   dba_tab_columns
    WHERE table_name = 'XX_GL_REVALUATION_ACCOUNTS'
    ORDER BY column_id;
r1 c1%ROWTYPE;
BEGIN
ad_dd.register_table('XXGL', 'XX_GL_REVALUATION_ACCOUNTS', 'T');
FOR r1 IN c1 LOOP
    ad_dd.register_column('XXGEN'
                         ,r1.table_name
                         ,r1.column_name
                         ,r1.column_id
                         ,r1.data_type
                         ,r1.data_length
                         ,r1.nullable
                         ,'N');
    dbms_output.put_line(r1.column_name || ' registered.');
END LOOP;
ad_dd.register_primary_key('XXGL’
                            ,'XX_GL_REV_ACCOUNTS_PK'
                            ,'XX_GL_REVALUATION_ACCOUNTS'
                            ,'XX_GL_REVALUATION_ACCOUNTS'
                            ,'S'
                            ,'Y'
                            ,'Y');
ad_dd.register_primary_key_column('XXGL'
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'SET_OF_BOOKS_ID'
                                   ,1);
ad_dd.register_primary_key_column('XXGL'
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'GAIN_CCID'
                                   ,2);
ad_dd.register_primary_key_column('XXGL’
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'LOSS_CCID'
                                   ,3);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
END;
/

Registration of the primary key and primary key columns is another story.
This is required for auditing but we do have some tables that do not have a real primary key.
So these have to be registered manually by using the following code:

ad_dd.register_primary_key('XXGL’
                            ,'XX_GL_REV_ACCOUNTS_PK'
                            ,'XX_GL_REVALUATION_ACCOUNTS'
                            ,'XX_GL_REVALUATION_ACCOUNTS'
                            ,'S'
                            ,'Y'
                            ,'Y');
ad_dd.register_primary_key_column('XXGL'
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'SET_OF_BOOKS_ID'
                                   ,1);
ad_dd.register_primary_key_column('XXGL'
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'GAIN_CCID'
                                   ,2);
ad_dd.register_primary_key_column('XXGL’
                                   ,'XX_GL_REV_ACCOUNTS_PK'
                                   ,'XX_GL_REVALUATION_ACCOUNTS'
                                   ,'LOSS_CCID'
                                   ,3);

PS: Don’t forget to COMMIT !

The table should now be available for auditing and you can add the table to an audit group.
Navigate to System Administrator responsibility and access the menu “Security –> Audittrail –> Tables”, you should be able to find the table.

Also make sure that Audit installations on the custom schema (in this example XXGL) is enabled, otherwise it will still not work:

Oracle E-Business Suite Development & Extensibility Handbook

2009-10-21T16:04:00.002+02:00

Just got an email that Anil Passi's book (now also available via bol.com) is out.

As i still use his website as a reference and also had the pleasure to attend a 2-day special about best practices on custom EBS development i think i can recommend this book to anybody who wants to learn more about developing customizations in EBS.

Extend and Customize Oracle E-Business Suite

Transform your Oracle E-Business Suite infrastructure to suit enterprise requirements using the comprehensive development methods in this Oracle Press guide. Oracle E-Business Suite Development & Extensibility Handbook lays out each tool alongside reusable code and step-by-step examples. Learn how to create multiorg objects, integrate Java and PL/SQL, design custom user interfaces, and use Oracle Application Framework and Oracle Workflow. This detailed volume also explains how to work with Oracle XML Gateway, publish dynamic business intelligence reports, and tune your configuration.

Manage an efficient development platform with Oracle Application Object Library
Develop concurrent programs, customized forms, and personalized graphical user interfaces
Securely share data between organizations
Distribute business communiqués through Oracle Reports and Oracle Business Intelligence Publisher
Develop Java extensions with Oracle JDeveloper and Oracle Application Framework
Capture and graphically map business processes using Oracle Workflow
Exchange data between applications via Oracle XML Gateway
Change the appearance of applications using Custom Look and Feel
Integrate Oracle E-Business Suite with service-oriented architecture using Oracle Applications Adapter On Demand

Issues after moving from Oracle JInitiator to the Sun JRE

2009-10-20T13:25:00.001+02:00

We finally made the move from Jinitiator to the Sun JRE, but it appears that there are some small but annoying issues related to this move.

Performance issue:
The first one i encountered on my laptop (and not on my desktop) is that it takes ages to drag a window from one position to a new position.
It takes more then 2 seconds to draw the outline of the window and afterwards another 5 or more seconds to draw the contents of the window.

This issue is caused by hardware acceleration (DirectX) used by the JRE and does not occur on all hardware.
The solution to the root cause of this issue is currently expected to be provided by Sun in JRE 1.6.0_14,
in the meanwhile you can workaround it by following these instructions:

1. Open the Java control panel
2. Go to the "Java" tab
3. Under "Java Applet Runtime Settings", click "View".
4. In the "Java Runtime Parameters", enter "-Dsun.java2d.d3d=false"
5. Click OK and OK to close the Java control panel.

Focus issues: Browser window opens behind the Forms window :
Opening a html page/OAF session from the Forms Navigator may cause the html window to open behind the Navigator window when using Internet Explorer. This issue occurs when using next-generation java plug-in technology with JRE 1.6.0_10 or later, however it does not appear to occur on all desktops.
This bug is currently under investigation by Sun.

To workaround this issue perform the following actions:

Open the Java control panel
Go to the “Advanced” tab
Under “Java Plug-in” settings uncheck the “Enable the next-generation Java Plug-in” setting.
Click OK and restart all your browser sessions.

Larry Ellison on cloud computing

2009-10-15T14:06:00.001+02:00

Oracle Workflow Special Notification Mailer Attributes

2009-10-14T20:21:00.001+02:00

You can use special message attributes to control how a notification mailer generates the e-mail message for a notification, if the recipient has a notification preference to receive e-mail notifications. For example, if you want to customize notifications from a particular department, you can define these attributes for those notifications.

#WFM_FROM - Define this attribute for a message to specify the value that appears in the From field of the message header when the e-mail notification message is delivered to a user. If the #WFM_FROM message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_FROM attribute value in the From field for that message, overriding the mailer's From Address parameter value.

#WFM_REPLYTO - Define this attribute for a message to specify the address of the e-mail account that receives incoming messages, to the which e-mail notification response should be sent. If the #WFM_REPLYTO message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_REPLYTO attribute value as the reply address for that message, overriding the mailer's Reply To parameter value.

#WFM_HTMLAGENT - Define this attribute for a message to specify the base URL that identifies the HTML web agent that handles HTML notification responses. This URL is required to support e-mail notifications with HTML attachments. The default URL is derived from the Workflow Web Agent specified in the Global Preferences web page in standalone Oracle Workflow, or from the Applications Web Agent (APPS_WEB_AGENT) profile option in Oracle Applications. However, you can override this default by defining a different value for this attribute. If the #WFM_HTMLAGENT message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_HTMLAGENT attribute value as the HTML web agent for that message, overriding the mailer's HTML Agent parameter value.

NOTE: Text copied from http://download.oracle.com/docs/cd/B14099_19/integrate.1012/b12161/defcom38.htm

There are a few more attributes which can be used then the one mentioned above:

#WFM_CC – Set a CC email address on the notification
#WFM_BCC – set a BCC email address on the notification
#FROM_ROLE – Specify the role that is the source of a notification
#HIDE_REASSIGN – Set to Y to hide the reassign button in the notification

In Workflow builder this looks as follows:

First define the attributes:

On message level define it as follows:

The WFM_FROM attribute can now be programmatically set and will be set on the message level automatically.

For example:

wf_engine.setitemattrtext(itemtype => itemtype
                         ,itemkey => itemkey
                         ,aname    => ‘WFM_FROM'
                         ,avalue   => ‘robert.jungerius@gmail.com’);

Oracle Workflow scripts, removing a complete item type definition

2009-10-14T11:25:00.001+02:00

From within Workflow Builder you can save new workflow item types, but removing a complete workflow definition is not in there.

Oracle provides some scripts to perform this task for you, deleting an item type for example:

Wfrmitt.sql

Use wfrmitt.sql to delete all data in all Oracle Workflow design time and runtime tables for a particular item type. This script prompts you for an item type from a list of valid item types.

Use the script as follows:

sqlplus <apps user/apps pwd> @$FND_TOP/sql/wfrmitt

Warning: This script deletes ALL workflow data for a specified item type.

See this link for all workflow scripts:

http://download.oracle.com/docs/cd/B14099_19/integrate.1012/b12160/admsql.htm

Forms Personalizations: Enabling LOV validation on vendor and vendor location in the Requisitions entry form

2009-10-14T11:14:00.001+02:00

Requirement is to set the (suggested) vendor and (suggested) vendor location as required and to also make them LOV validated so you cannot enter non-existing vendors.

Settings form items to required is an easy task, it can be done by a Forms personalization.
Settings form items LOV validated is unfortunately only possible from the CUSTOM.pll.

As these settings have to be controlled per entity and we don’t want to edit the custom.pll everytime a new entity is added we solved it like this…

In CUSTOM.pll we added a custom event name, so we can call this one from within Forms Personalizations:

procedure event(event_name varchar2) is
….
IF event_name='XX_POXRQERQ_SUPPL_LOV_VAL' THEN
set_item_property('LINES.SUGGESTED_VENDOR_NAME',LOV_VALIDATION,PROPERTY_TRUE);
set_item_property('LINES.SUGGESTED_VENDOR_LOCATION',LOV_VALIDATION,PROPERTY_TRUE);
END IF;
….
end event;

Now in the Forms Personalizations we can easily meet the requirements (per entity, per responsibility or whatever) as follows:

1. First set the lines.suggested_vendor_name and lines.suggested_vendor_location to required as follows

2. Call the custom event name we defined in the CUSTOM.pll

This last step will set the LOV_VALIDATION property to TRUE on vendor and vendor location.
Solving it this way we are flexible as we can call this custom event from any personalization and we don’t have to edit the CUSTOM.pll anymore.

Forms Personalizations: Set vendor LOV in Requisitions form to only display vendors in current OU

2009-10-14T10:53:00.001+02:00

Requirement is to make the LOV on vendor in the Requisitions entry form to only display vendors that have at least 1 vendor site in the current operating unit.

This can be easily solved with Forms Personalizations as follows:

Note: the condition displays some custom code i cannot display because of copyright, but it basically is a custom function that takes the fnd_profile.value(‘ORG_ID’) and returns the operating unit name/country name, so we can control on which operating unit this personalization will fire.

In the actions tab the following actions have to be performed:

1. Create a new custom record group that will replace the current record group behind the vendor LOV

SQL statement is as follows:

select pv.vendor_name,
pv.segment1 ,
decode (hold_status, 'Y', '*', '') on_hold,
pv.num_1099 taxpayer_id,
pv.vat_registration_num tax_registration_num,
pv.vendor_id
from po_suppliers_val_v pv,
po_vendor_sites pvs
where pv.vendor_id = pvs.vendor_id
and (pvs.INACTIVE_DATE is null
or
pvs.INACTIVE_DATE > sysdate)
group by (pv.vendor_name, pv.segment1, hold_status, pv.num_1099,pv.vat_registration_num,pv.vendor_id)
order by upper(vendor_name)

So this SQL will make sure that only vendors with a join to a vendor site in the current operating unit (po_vendor_sites is an org-enabled view) will pop up.

2. Attach the newly created record group as a LOV to the supplier

OAF: Setting initial focus on an input field

2009-10-14T09:03:00.002+02:00

Nothing more annoying in web entry forms if the cursor is not blinking in the first enterable input field upon first load. And yet i see that a lot in web forms.

In OAF it is easy to set the focus to the first input field so the user can start typing and does not need the mouse.

Add the following to processRequest:

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
// Set focus on the first search item segment1
OABodyBean oabean = (OABodyBean)pageContext.getRootWebBean();
oabean.setInitialFocusId("Segment1");
}

Don’t forget to add the import of OABodyBean:

import oracle.apps.fnd.framework.webui.beans.OABodyBean;

Recreating a tablespace, totally forgot… DBMS_METADATA to the rescue

2009-10-13T14:18:00.003+02:00

After many years of only developing in oracle i totally forgot the sql statement to (re)create a tablespace.
This week i was asked to install a new version of APRO on our 11.5.10.2 environment and it basically was as simple as creating two tablespaces and running 2 sql scripts.
But the first part already blocked me, because i did not know the statement by head and i also wanted to create the tablespaces the same way as the previous version of APRO. I don’t use TOAD anymore so also have no easy way to get a DDL script from an existing tablespace.

It actually proved to be quite easy to get a DDL script from any object you want:
TABLESPACES: select dbms_metadata.get_ddl(‘TABLESPACE’, ‘<tablespace_name>’) from dual;
USERS: select dbms_metadata.get_ddl('USER','XXABET') FROM dual;

31-12-4712, end of life as we know it

2009-10-13T14:18:00.001+02:00

When dealing with Oracle HRMS development you are inevitably going to hit the date-track tables/views.
I have built and also seen a lot of views with the to_date(‘31-12-4712’,’DD-MM-RRRR’) coding and in a lot of situations also the to_date(‘31-DEC-4712’,’DD-MON-RRRR’) which will surely get you into trouble in an NLS environment.

But finally found a better and cleaner solution to code your where clauses:
select hr_general.start_of_time, hr_general.end_of_time from dual;

These function calls return the standard start and end of time in HRMS as DATE’s.

BI-expert van Mei 2009 (sorry, only in dutch)

2009-06-01T21:20:00.000+02:00

Scamander was the company that awarded me this precious title: BI Expert of the month.

It is only in dutch, but do take a look.... BI-expert van Mei 2009

ORA-39700 after RDBMS upgrade

2008-09-01T21:26:00.004+02:00

Just upgraded my Oracle 10g EE database 10.2.0.1.0 to 10.2.0.2.0 as this is a prereq if you want to install SOA Suite.
Running the upgrade process went very smooth, but when trying to open the database i ran into the following error:
---------------------------------
Errors in file /app/oracle/product/10.2.0/db/admin/orcl/udump/orcl_ora_8351.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Mon Sep 1 19:53:32 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
----------------------------------

Hmmm, as i am still preparing for my 10g OCA certification i did not encounter this error before.
But the solution is off course very simple and reading the README would have been a great idea too ;-)
1. sqlplus / as sysdba
2. startup upgrade
3. @$ORACLE_HOME/rdbms/admin/catupgrd.sql
4. shutdown immediate
5. startup

This can run for quite some time (at least it is for me, but mine is running inside a Virtualbox VM)

Let's continue with the SOA Suite and see if this one is willing to install...