Thursday, March 27, 2008

How to Extend a View Object in OA Framework

View Objects are the queries that provide the data seen on a Self Service web page. Oracle offers many attributes in each View Object, but often an additional attribute is needed. Oracle allows developers to extend the delivered View Objects to meet this need. The following iRecruitment Vacancy Search Page will be used for demonstration, but any page developed using the OAF architecture is a candidate for this exercise.



In our example, the Posting's Job Title and Organization Name that were entered during step 4 of the Vacancy Create process are not offered in the delivered VO. These values are shown during data entry in the below screen print. We will write PL/SQL functions to retrieve them; our extended VO will then call these functions to return the values so they can be rendered on the page:



In order to find which VO to extend, the page definition must be analyzed. There is a distinct VO behind the fields shown in the results table. Only a portion of the available fields are shown by default. Click the "About this Page" link in the lower left corner to see the page definition.


Click Expand All and find the Results Table:


Click the IrcVacancyVO link to see the View Object Details:


Any attribute listed on this page can be added "declaratively", or without programming required by personalizing the results table. You're are not limited to what Oracle provides in the delivered View Objects, however, as you will soon come to know.

For our example, we will be extending the oracle.apps.per.irc.vacancy.server.IrcVacancyVO View Object. Extending a View Object involves the following steps:

1. Importing the delivered View Object into JDeveloper
2. Importing dependent classes into JDeveloper
3. Extending the View Object
4. Deploying the Extended VO to the application server
5. Add extended VO columns to the page using personalizations

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

Open JDeveloper and Create a new OA Workspace and project following these steps. Naturally, my workspace and project is called 'hack':


It is best practice to place extended objects in a custom package. If you are extending an object in the delivered package oracle.apps.per.irc.vacancy.server, the custom package would be hack.oracle.apps.per.irc.vacancy.server


Continue to step 3 and specify a DBC file. This file is found on the application tier at $FND_TOP/secure. It must be copied to C:\JDEVOAF\jdevbin\jdev\multi\system\oa\dbc_files\secure on your local machine, where "JDEVOAF" is the directory where you installed JDeveloper. Enter an Oracle Applications user and valid short name and responsibility key before finishing:


When you have finished marveling over your newly created project, right click it and choose "New Business Components":




If you don't have a connection to use already defined, follow these steps below to create a new one. The connection name is arbitrary:


Enter a Valid Oracle Applications user name/password:


Enter the appropriate box information:


Test and Finish:


Click Finish and return to the Navigator. The custom package where the extended VO will be created is complete, but we must import the delivered VO before continuing. 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.irc.vacancy.server.IrcVacancyVO, we must import the oracle.apps.per.irc.vacancy.server pacakge and all dependent packages. In order to do this efficiently, I perform the following steps:

1. telnet to middle tier
2. cd $JAVA_TOP/oracle/apps
3. tar -cvf ota_top.tar ota
4. tar -cvf per_top.tar per
5. tar -cvf pay_top.tar pay
6. tar -cvf ben_top.tar ben ... repeat for as many modules as you might need
7. FTP each .tar to C:\JDEVOAF\jdevhome\jdev\myclasses
8. extract each .tar in the C:\JDEVOAF\jdevhome\jdev\myclasses directory

Once this task is complete, right click your project and "Import Business Components..."


and answer "Yes" when presented with the dialog "The following XML file is on the classpath.
Would you like to add these objects to your project?"


Upon importing the first package, you may see an information message telling you that additional packages are needed. In this example, import the oracle.apps.per.schema.server (server.xml) package next. Repeat this process until no additional messages appear.

If you see an Information message like the following, it means that the server.xml has defined a file not contained in the classpath directory. It can be ignored; if it really bothers you, open an SR and request that Oracle fix the server.xml file.


Be sure to close JDeveloper and re-open after importing all packages. You may see an additional warning to import more. Your project will look like the following:


We must now create a new View Object in our custom package that extends the delivered VO. A common practice is to use a PL/SQL function for the new column so that any custom logic can be maintained easily. Rather than re-implementing the VO extension, the PL/SQL can be updated anytime the business rules change.

Before proceeding in JDeveloper, lettuce first create the PL/SQL function:

Package spec:

create or replace package hack_jdev_pkg as
--
function get_vac_posting_org (p_vacancy_id in number) return varchar2;
--
function get_vac_posting_job (p_vacancy_id in number) return varchar2;
--
end hack_jdev_pkg;
/
show errors;


Package body:

create or replace package body hack_jdev_pkg as
--
cursor c_vacancy_posting_data (cp_posting_id number) is
select org_name
,job_title
from irc_posting_contents_vl ipcv
,per_vacancies pv
where pv.primary_posting_id = cp_posting_id
and pv.primary_posting_id = ipcv.posting_content_id;
--
cursor c_posting_id (cp_vacancy_id number) is
select primary_posting_id
from per_all_vacancies
where vacancy_id = cp_vacancy_id;
--
function get_vac_posting_org (p_vacancy_id in number) return varchar2 is
--
ln_posting_id number;
rec_vac_posting_data c_vacancy_posting_data%rowtype;
--
begin
--
open c_posting_id (p_vacancy_id);
fetch c_posting_id into ln_posting_id;
close c_posting_id;
--
open c_vacancy_posting_data (ln_posting_id);
fetch c_vacancy_posting_data into rec_vac_posting_data;
if c_vacancy_posting_data%notfound then
rec_vac_posting_data := null;
end if;
close c_vacancy_posting_data;
--
return rec_vac_posting_data.org_name;
--
end get_vac_posting_org;
--
function get_vac_posting_job (p_vacancy_id in number) return varchar2 is
--
ln_posting_id number;
rec_vac_posting_data c_vacancy_posting_data%rowtype;
--
begin
--
open c_posting_id (p_vacancy_id);
fetch c_posting_id into ln_posting_id;
close c_posting_id;
--
open c_vacancy_posting_data (ln_posting_id);
fetch c_vacancy_posting_data into rec_vac_posting_data;
if c_vacancy_posting_data%notfound then
rec_vac_posting_data := null;
end if;
close c_vacancy_posting_data;
--
return rec_vac_posting_data.job_title;
--
end get_vac_posting_job;
--
end hack_jdev_pkg;
/
show errors;



Right click the project and create a new View Object:


Name your new VO; the best practice is to give it a prefix before the delivered name. Be sure to enter oracle.apps.per.irc.vacancy.server.IrcVacancyVO in the "Extends" field.


Continue to step 3 and click New and configure as shown in the following two screen prints. This is the actual extension to the VO where the values from Step 4 of the Vacancy Create process are added to the VO:


Repeat for the Posting Organization:


Continue to step 6 and configure as shown below and Finish. Check the "Generate Java File" if you need to extend the methods delivered with the VO:

Double click the .jpx file in your project to add a substitution, which tells Oracle to use your View Object instead of the delivered VO. Highlight the delivered VO and the extended VO and click "Add".

Make and Rebuild your project:


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

To upload the substitution, open a command prompt on the local machine and run the following command:


C:\JDEVOAF\jdevbin\jdev\bin\jpximport C:\JDEVOAF\jdevhome\jdev\myclasses\hack.jpx -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.hack.com)(PORT=1521))(CONNECT_DATA=(SID=hack)))"






To deploy the files, zip the compiled project files in C:\\jdevhome\jdev\myclasses\hack and FTP them to $JAVA_TOP. Unzip them:


Bounce Apache and then return to iRecruitment Recruiter -> Search for Vacancies -> About this Page. The delivered VO has been replaced by your extended VO:


Create the necessary personalization fields on the Advanced Table Region of the page to render the new column, beginning with the Job Posting:



Yay-yeah...you are now an Oracle Hack:

Now challenge yourself by repeating the personalizations for the Posting Organization without the assistance of screen prints. Duplicate this effort on another Self Service page to practice your newly minted VO substitution hacking capabilities.