Friday, May 1, 2009

How to Extend a View Object in OA Framework R12

View Object extensions in R12 are possible but inherently buggy. I've performed several but was not able to post a "bug-free" entry until now. This thread will show how to get through all the wizards and successfully deploy a VO extension in R12. Some of the steps are redundant and designed to avoid errors that the JDev IDE might throw. Hence, the product is not free of bugs but it can be used to complete an extension. For information on how to perform VO extensions in prior releases of Oracle Applications, see this article:

How to Extend a View Object in OA Framework

The View Object selected for this exercise is from the Employee Directory functionality in Oracle Applications. Log in to an R12 instance and click the Employee Directory link in the upper-right hand corner.

Search for the Oracle Hack:



Once the detail appears, click the About this Page link in the lower left-hand corner. The page definition will appear...click Expand All and note the View Object (VO) name next to the detail attributes:


EmpDetailVO is the VO that supplies the data seen on the page. View the details of this VO by clicking the link and reviewing the available attributes:


Scrolling down through the available attributes you will see there are many available but none involve the Address DFF. Any of these attributes here can be added to the page using Personalizations. However, the delivered VO oracle.apps.per.selfservice.empdir.server.EmpDetailVO must be extended to show the address DFF.

To do this, the following steps must be performed:
  1. Import the delivered View Object into JDeveloper
  2. Importing dependent classes into JDeveloper
  3. Extending the View Object
  4. Deploying the Extended VO and associated Java files to the application server
  5. Add extended VO columns to the page using personalizations
Before we move on to JDeveloper, enable the DFF segment by following the next steps.

(N) System Administrator -> Application -> Flexfield -> Descriptive -> Segments
  • Search for Additional Address Details
  • Uncheck Freeze Flexfield Definition checkbox
  • With the Global Data Elements Context selected, click the Segments button
  • Configure as follows:


  • Click the Open button
  • Uncheck the Required checkbox
  • Click Save
  • Close the Segments Summary window
  • Check Freeze Flexfield Definition checkbox
  • Click Yes at the Warning
  • Click Save
  • Click OK button to Compile Flexfield Definition
  • Click OK button when it says Flexfield has been compiled

Verify the DFF has a value:


To find the version of JDeveloper that's right for you, see Note:416708.1 or 787209.1 (newer) on Metalink titled "How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12". I've never verified which version of R12 is "correct". If the instance is R12, I download the latest JDev patch and proceed to wreck shop. Do the following to get JDeveloper running on your 'puter:
  1. Download patch
  2. Extract to an empty directory dedicated to JDeveloper. I use "JDEVOAFR12"
  3. Right-click My Computer -> Propoerties -> Advanced -> Environment Variables
  • If you already have one called JDEV_USER_HOME, update the value to C:\JDEVOAFR12\jdevhome\jdev
  • If you don't use the old version, create the Variable
You are now ready to launch JDeveloper by double-clicking the binary at C:\JDEVOAFR12\jdevbin\jdev\bin\jdevW.exe.

Before you can use JDeveloper to create a custom View Object that extends oracle.apps.per.selfservice.empdir.server.EmpDetailVO, you must copy the Java files from $JAVA_TOP on the middle tier to the C:\JDEVOAFR12\jdevhome\jdev\myprojects\ directory on your computer.

As in 11.5.10, these files are stored in the $JAVA_TOP directory on the application (middle) tier and must be downloaded. Since we are extending the View Object (VO) oracle.apps.per.selfservice.empdir.server.EmpDetailVO, we must import the oracle.apps.per.selfservice.empdir.server pacakge and all dependent packages. In order to do this efficiently, tar/zip at least the "per" top and some of the related modules. Zipping the entire $JAVA_TOP can take substantial time due to its size. Here are the steps to move individual modules from $JAVA_TOP to your client:
  1. telnet to middle tier
  2. cd $JAVA_TOP/oracle/apps
  3. tar -cvf per_top.tar per
  4. FTP the .tar file to C:\JDEVOAF\jdevhome\jdev\myprojects
  5. extract the .tar
Before you begin, delete the following tutorial files:

C:\JDEVOAFR12\jdevhome\jdev\myprojects\mycompany
C:\JDEVOAFR12\jdevhome\jdev\myprojects\ExtendLabSolutions.jpr
C:\JDEVOAFR12\jdevhome\jdev\myprojects\ExtendLabSolutions.jpx
C:\JDEVOAFR12\jdevhome\jdev\myprojects\LabSolutions.jpr
C:\JDEVOAFR12\jdevhome\jdev\myprojects\LabSolutions.jpx
C:\JDEVOAFR12\jdevhome\jdev\myprojects\SampleLibrary.jpr
C:\JDEVOAFR12\jdevhome\jdev\myprojects\SampleLibrary.jpx
C:\JDEVOAFR12\jdevhome\jdev\myprojects\toolbox.jws
C:\JDEVOAFR12\jdevhome\jdev\myprojects\Tutorial.jpx
C:\JDEVOAFR12\jdevhome\jdev\myprojects\Tutorial.jpr

Now open JDeveloper and create a new Workspace for Oracle Applications.

File -> New


General -> Projects -> Workspace Configured for Oracle Applications


Give your new Workspace an arbitrary name:


Proceed in the Wizard to create a new project. Since the VO we're extending is in the package "oracle.apps.per.selfservice.empdir.server", create yours with a custom prefix. Naturally, mine will be called "hack.oracle.apps.per.selfservice.empdir.server":


Next!


It's time to specify a DBC file. This file is found somewheres on the application tier ($FND_TOP/secure in previous versions). Rather than bothering to find it, go to the Oracle Applications homepage and append this to the URL:

/OA_HTML/jsp/fnd/aoljtest.jsp

Enter the appropriate database information and click Test button:


Click "Enter AOL/J Setup Test"

Click "Locate DBC file"


Copy and paste this DBC file into C:\JDEVOAFR12\jdevbin\oaext\dbc_files\secure where "C:\JDEVOAFR12" is the directory where you installed R12 JDeveloper client. Now you can browse for your DBC file in Step 3:

Click Finish to see your project:

You will notice under Application Sources that the various "tops" copied from the application server appear. In previous versions of JDeveloper, you had to manually import the server.xml file from each business components package for it to appear in your project. In R12, it automatically imports.

Right-click Application Sources and choose New:


The "New Gallery" window appears. I'm not sure what a gallery is, but proceed to Business Tier -> ADF Business Components -> View Object


If you haven't already done so, JDev will ask you to setup a DB COnnection. Click New button to start the wizardry. Enter an arbitrary name and click Next:



Enter the APPS username/password and click Next:


Enter the values for host/port from the TNS Names file and click Next:


Test Connection on the last step and then Finish:


The VO wizard will now continue...the default package will already be specified. Enter a custom prefix to the delivered VO. Click the Browse button on the Extends field to choose the delivered VO oracle.apps.per.selfservice.empdir.server.EmpDetailVO:


If there are no VOs from which to choose, cancel the wizard...


...and expand the Application Source until you find oracle.apps.per.selfservice.empdir.server.EmpDetailVO. Then retry the wizard:




Keep clicking next...



If you get this error, click OK and then Cancel:


Retry adding a VO, except on step 1, click the "Updatable Access through Entity Objects" radio button and then click next through each screen.



Create a new attribute:


Configure as follows:

Click OK...Next!


Add the function call to the SQL Statement as well.

This step is clutch as you will potentially receive the following error if you deploy your VO to Oracle Applications without modifying the SQL:

JBO-27022: Failed to load value at index 53 with java object of type java.lang.String due to java.sql.SQLException.

Keep going on the train (6 of 8) ...




Click Finish and you'll see JDev work vigorously to update the Java files. The VO has now been created:



The function call used must of course exist in order to call it inside your VO. A slight detour as we write some PLSQL, compiling in it the database as APPS:


create or replace package hack_jdev_utility_pkg as
--
function get_address_region (p_person_id in number
,p_effective_date in date) return varchar2;
--
end hack_jdev_utility_pkg;
/
show errors;



CREATE OR REPLACE package body hack_jdev_utility_pkg as
----------------------------------------------------------------------------------------
-- get_address_region
----------------------------------------------------------------------------------------
function get_address_region (p_person_id in number
,p_effective_date in date) return varchar2 is
--
lv_address_region varchar2(155) := null;
--
cursor c_address (cp_person_id number
,cp_effective_date date) is
select addr_attribute10
from per_addresses
where person_id = cp_person_id
and cp_effective_date between date_from and nvl(date_to,hr_general.end_of_time);
--
begin
--
open c_address (p_person_id, p_effective_date);
fetch c_address into lv_address_region;
if c_address%notfound then
lv_address_region := null;
end if;
close c_address;
--
hr_utility.set_location('lv_address_region: '||lv_address_region,50);
return lv_address_region;
--
exception
when others then
hr_utility.set_location('Error: ',998);
hr_utility.set_location(substr(sqlerrm,40),999);
return 'NA';
end get_address_region;
--
end hack_jdev_utility_pkg;
/
show errors;


Save your works at this point and do the following:
  1. Close JDeveloper (yes, I'm serious)
  2. Copy per_top.tar to C:\JDEVOAFR12\jdevhome\jdev\myclasses\oracle\apps
  3. Extract files
This will make all the delivered files you will be extending available during compilation. Basically the myprojects folder uses the .xml files in $JAVA_TOP/oracle/apps/per and the myclasses folder uses the .class files. Once the files are extracted, open JDev again, right-click your project and perform a "Make" and "Rebuild":


Just a couple of warnings:


What happens if you try to Rebuild without the $JAVA_TOP/oracle/apps/per files extracted into myclasses? Mad errors:


It's time to generate a substitution, which is a file that will tell Oracle to use your View Object instead of the delivered VO. Inside JDeveloper, right-click the Project -> Project Properties -> Business Components -> Substitutions:


Select the delivered VO on the left pane, the custom VO on the right pane and click Add:


Click OK, make/rebuild your project and save your works.

The remaining steps are:
  1. upload substitution to database
  2. deploy Java and .xml files to middle tier
  3. bounce Apache
  4. Personalize page to display new items
Execute this at the command line to import the substitution:

C:\JDEVOAFR12\jdevbin\oaext\bin\jpximport C:\JDEVOAFR12\jdevhome\jdev\myclasses\hack0.jpx -username apps -password hack -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.hack)(PORT=1521))(CONNECT_DATA=(SID=HACK)))"


Take a peek at the .jpx file. The substitution command must exist in this file in order for the import to be successful and thus allow your VO to be used instead of the delivered one. Peek-a-boo:


Now that the substitution has been uploaded, deploy the java files by zipping the compiled project files in C:\JDEVOAFR12\jdevhome\jdev\myclasses\hack and FTP them to $JAVA_TOP. Unzip them:


Bounce the apache and OC4J process:
  1. $ADMIN_SCRIPTS_HOME/adapcctl.sh stop
  2. $ADMIN_SCRIPTS_HOME/adapcctl.sh start
  3. $ADMIN_SCRIPTS_HOME/adoacorectl.sh stop
  4. sleep 10
  5. $ADMIN_SCRIPTS_HOME/adoacorectl.sh start
Log back into Oracle Applications and browse to the Employee Directory, searching for an employee of your choice. Click "Personalize Page" in the upper right-hand corner:


Click the Complete View radio button -> Expand All and Create an Item in this section:


Configure as follows:


Yay-yeah...