28b. OAF VO Extension: How to Add New Column in the Expense Report Summary Home Page – Part 2

 

Part 2: OAF VO Extension — How to Add a New Column in the Expense Report Summary Home Page

Introduction

This is the second and final part of the guide on View Object (VO) Extension in Oracle Application Framework (OAF). In Part 1, we identified the standard View Object behind the Expense Report Summary page, downloaded it, and created the OA Workspace and Project required for the extension.

In this part, we will complete the VO Extension by:

1. Creating the Extended View Object that inherits from the standard VO

2. Modifying the SQL query to include the new column (DFF Attribute5)

3. Performing Substitution to replace the standard VO with the custom VO

4. Uploading the extended VO to the Oracle Java_Top location

5. Registering the VO using the XML Importer script

6. Personalizing the page to display the new column

7. Verifying the new field appears on the standard page

Part 1 Reference: https://docodi.blogspot.com/p/28a-oaf-vo-extension-how-to-add-new.html

---

Prerequisites

Before proceeding, ensure the following are complete from Part 1:

• The standard View Object (TrackExpenseReportsVO) has been identified

• The VO has been downloaded from the Oracle server to your JDev environment

• The OA Workspace and Project have been created with the xx prefix (e.g., xxap)

• The folder structure matches the standard VO's package path

---

Step 1: Create the Extended View Object

Now we create a new View Object that extends the standard VO. This extended VO will inherit all attributes from the standard VO and allow us to add new ones.

1. Open the New Gallery:

   • Right-click on the project XXTrackExpenseReportsVO

   • Select New

2. Select View Object:

   • Navigate to Business Tier → ADF Business Components

   • Select View Object

   • Click OK

3. Configure the Extended View Object:

   • View Object Name: TrackExpenseReportsVOEx (the Ex suffix indicates "Extended")

   • Default Package: Select the custom package (e.g., xxap.oracle.apps.ap.oie.server)

4. Specify the Standard VO to Extend:

   • Under the Extends option, click the Browse button

   • Navigate to and select the standard View Object TrackExpenseReportsVO

   • This is the VO we downloaded in Part 1

Important: The Extends property is what makes this a VO Extension. The new VO inherits all attributes, queries, and behavior from the standard VO, and we can add or override attributes as needed.

5. Select the Standard Controller (if prompted):

   • If prompted, select the standard page controller TrackExpenseReportsCO

   • This ensures the extended VO is associated with the correct page context

6. Review the Extended VO Screen:

   • The final screen of the wizard shows the extended VO configuration

   • Click Next to proceed to the query definition

---

Step 2: Modify the SQL Query

Now we need to modify the SQL query to include the new column (DFF Attribute5).

1. Copy the Standard Query:

   • The wizard displays the SQL query used by the standard VO

   • Copy this query to a SQL tool (e.g., Toad, SQL Developer) for editing

2. Add the New Column:

   • Add the custom column Attribute5 to the SELECT clause

   • Always add new columns at the end of the SELECT list to maintain compatibility with the standard VO

   For example, if the original query is:

   sql

   SELECT report_id, report_name, submit_date, status
   FROM ap_expense_reports

   The modified query becomes:

   sql

   SELECT report_id, report_name, submit_date, status, attribute5
   FROM ap_expense_reports

Important: Always add new columns at the end of the SELECT list. Inserting columns in the middle can break the attribute mapping inherited from the standard VO.

3. Paste the Modified Query:

   • Return to the View Object wizard

   • Paste the modified SQL query into the query definition

   • Click Next

4. Select Required Checkboxes:

   • Select the required checkboxes as needed (e.g., "Generate View Row Class," "Generate View Object Class")

   • Click Finish

Result: The extended View Object (TrackExpenseReportsVOEx) is now created with the new Attribute5 column.

---

Step 3: Substitute the Custom View Object

Substitution tells the OAF framework to use your custom VO instead of the standard VO at runtime. This is the critical step that makes the extension take effect.

1. Open Project Properties:

   • Right-click on the project XXTrackExpenseReportsVO

   • Select Project Properties

2. Navigate to Substitutions:

   • In the Project Properties dialog, select Substitutions

3. Add the Substitution:

   • Click Add to create a new substitution entry

   • Source (Standard VO): oracle.apps.ap.oie.server.TrackExpenseReportsVO

   • Target (Custom VO): xxap.oracle.apps.ap.oie.server.TrackExpenseReportsVOEx

Explanation: The substitution entry tells the OAF framework: "Whenever the standard TrackExpenseReportsVO is requested, use the custom TrackExpenseReportsVOEx instead." This is how your extended VO replaces the standard one at runtime.

4. Save the Substitution:

   • Click OK to save the project properties

---

Step 4: Upload the Extended VO to Java_Top

Now we need to deploy the extended VO to the Oracle server's Java_Top location.

1. Locate the Extended VO File:

   • In your JDev project, locate the generated file TrackExpenseReportsVOEx.xml

2. Copy the File to Java_Top:

   • Copy the TrackExpenseReportsVOEx.xml file to the Oracle server

   • Place it in the appropriate directory under Java_Top:

     text

     $JAVA_TOP/oracle/apps/xxap/oie/server/

Note: The xxap prefix indicates this is a custom (extended) object. The rest of the path matches the standard VO's package structure.

---

Step 5: Register the VO Using XML Importer

Now we register the extended VO in the Oracle application using the XML Importer script.

Run the XML Importer Script:

bash

java oracle.jrad.tools.xml.importer.XMLImporter \
  $JAVA_TOP/oracle/apps/xxap/oie/server/TrackExpenseReportsVOEx.xml \
  -rootdir $JAVA_TOP \
  -username apps \
  -password apps \
  -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server_name)(PORT=100001))(CONNECT_DATA=(SERVICE_NAME=SID_NAME)))"

Script Parameters

Parameter	Description
XMLImporter	The Java class that performs the import
$JAVA_TOP/.../TrackExpenseReportsVOEx.xml	The path to the extended VO file
-rootdir $JAVA_TOP	The root directory for the import
-username apps	The APPS database username
-password apps	The APPS database password
-dbconnection "..."	The database connection string

Important: Replace server_name, PORT, and SID_NAME with your actual database connection details. The XML Importer registers the VO in the JDR_ATTRIBUTES and JDR_PATHS tables, making it available to the OAF framework at runtime.

---

Step 6: Personalize the Page to Display the New Column

Now we use Personalization to add the new field to the standard OAF page.

1. Open the Standard OAF Page:

   • Navigate to the Expense Report Summary page in the application

2. Click the Personalize Link:

   • Click the Personalize link at the top of the page

   • This opens the Personalization page for the current page

3. Create a New Item:

   • Click the Create Item icon to add a new field

4. Set the Item Style:

   • Set the Item Style to MessageStyledText

   • This renders the field as read-only text

5. Configure Item Properties:

   • Set the following properties for the new item:

Property	Value
ID	Attribute5 (or any meaningful name)
Item Style	MessageStyledText
View Instance	TrackExpenseReportsVOEx (the extended VO)
View Attribute	Attribute5 (the new column)

Important: The View Instance must reference the extended View Object (TrackExpenseReportsVOEx), not the standard one. The View Attribute must match the new column name you added to the SQL query.

6. Apply the Personalization:

   • Click Apply to save the personalization

---

Step 7: Verify the New Column

1. Return to the Standard Page:

   • Navigate back to the Expense Report Summary page

2. Verify the New Column:

   • The new column (Tracking No. or Attribute5) should now be displayed on the page

   • The column shows the DFF Attribute5 value for each expense report

Result: The new field is now displayed on the standard OAF page without modifying any Oracle seeded code.

---

How VO Extension Works at Runtime

The following illustrates the complete runtime flow:

text

┌─────────────────────────────────────────────────────────────────┐
│                    STANDARD OAF PAGE                            │
│  1. Page requests TrackExpenseReportsVO                         │
│  2. Substitution rule intercepts the request                    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    SUBSTITUTION                                 │
│  1. Standard VO: oracle.apps.ap.oie.server.TrackExpenseReportsVO│
│  2. Custom VO:   xxap.oracle.apps.ap.oie.server.TrackExpense... │
│  3. Framework uses the CUSTOM VO instead of the standard one    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    EXTENDED VO                                  │
│  1. Inherits all attributes from the standard VO                │
│  2. Adds the new Attribute5 column to the query                 │
│  3. Returns data including the new column                       │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    PERSONALIZED PAGE                            │
│  1. New item (Attribute5) added via Personalization             │
│  2. Bound to the extended VO's Attribute5                       │
│  3. Displays the DFF Attribute5 value                           │
└─────────────────────────────────────────────────────────────────┘

---

Understanding the Three Key Mechanisms

Mechanism	Purpose	How It Works
VO Extension	Adds new attributes to a standard VO	New VO "extends" the standard VO, inheriting all its attributes
Substitution	Replaces the standard VO with the custom VO	Project property tells the framework to use the custom VO
Personalization	Adds the new field to the page	Page-level configuration references the extended VO

Key Insight: VO Extension is powerful because it doesn't modify Oracle's seeded code. Instead, it creates a new layer that inherits from the standard VO, adds new capabilities, and substitutes itself at runtime. This ensures your customizations survive Oracle patches and upgrades.

---

Best Practices

Practice	Description
Add Columns at the End	Always add new columns at the end of the SELECT list
Use the xx Prefix	Follow the standard customization naming convention
Use the Ex Suffix	Name extended VOs with an Ex suffix for clarity
Test in Development	Always test VO extensions in a development environment
Document Substitutions	Keep a record of all substitutions for future reference
Verify After Patches	Re-verify extensions after applying Oracle patches
Use Version Control	Store extended VO files in a version control system

---

Troubleshooting Common Issues

Issue	Possible Cause	Solution
New column not displayed	Personalization not applied	Verify the item is added and applied
Wrong data displayed	View Instance mismatch	Verify the View Instance references the extended VO
VO not found at runtime	XML Importer not run	Run the XML Importer script to register the VO
Substitution not working	Wrong substitution entry	Verify the source and target VO names
Compilation errors	Missing imports	Rebuild the project and check for errors
Column shows no data	SQL query error	Verify the modified query returns Attribute5
Page breaks after patch	Substitution overwritten	Re-apply the substitution after patches

---

Required Scripts and Commands

XML Importer Script

bash

java oracle.jrad.tools.xml.importer.XMLImporter \
  $JAVA_TOP/oracle/apps/xxap/oie/server/TrackExpenseReportsVOEx.xml \
  -rootdir $JAVA_TOP \
  -username apps \
  -password apps \
  -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server_name)(PORT=100001))(CONNECT_DATA=(SERVICE_NAME=SID_NAME)))"

Verification Query

After importing, verify the VO is registered:

sql

SELECT jdr_path, jdr_mds_internal_name
FROM jdr_paths
WHERE jdr_path LIKE '%TrackExpenseReportsVOEx%';

---

Summary

Completing the VO Extension involves the following key steps:

1. Create the Extended VO: A new VO that extends the standard TrackExpenseReportsVO

2. Modify the Query: Add the new column (Attribute5) at the end of the SELECT list

3. Substitute the VO: Configure the project to replace the standard VO with the custom VO

4. Upload to Java_Top: Copy the extended VO file to the server

5. Register with XML Importer: Run the script to register the VO in the database

6. Personalize the Page: Add the new item and bind it to the extended VO

7. Verify: Confirm the new column appears on the standard page

Why VO Extension Matters

VO Extension is one of the most important customization techniques in Oracle E-Business Suite development because it:

• Preserves Oracle Code: No modifications to seeded Oracle objects

• Survives Upgrades: Customizations remain intact after patches and upgrades

• Follows Standards: Uses Oracle's official extension mechanisms

• Is Maintainable: Clear separation between standard and custom code

• Is Scalable: Can be applied to any standard OAF page

By mastering VO Extension, you gain the ability to add fields, modify queries, and enhance standard Oracle pages to meet business requirements without compromising the integrity of the Oracle E-Business Suite environment.

---

References

• Part 1: OAF VO Extension — https://docodi.blogspot.com/p/28a-oaf-vo-extension-how-to-add-new.html

• Part 2: OAF VO Extension (Continued) — https://docodi.blogspot.com/p/28b-oaf-vo-extension-how-to-add-new.html

• Oracle E-Business Suite Documentation: OA Framework Developer's Guide

• Oracle E-Business Suite Documentation: Personalization Guide

• Oracle JDeveloper Documentation: Available in the jdevdoc folder

• Related Guides: Controller Extension in OAF, Creating a Hello World Page in OAF, Creating an LOV in OAF