How to customize a LOV in OAF (VO Extension)?

OCTOBER 29, 2011 4 COMMENTS

2 Votes

OA Framework provides robust support for personalizing and extending the E-Business Suite user interface and
underlying business logic. The capabilities are largely achieved by leveraging the OA Frameworks declarative
architecture, and the object-oriented features of Java.
This post describes how to customize a LOV in an OAF Page and also make the LOV dependent on a parent LOV. It
can be done through a VO Extension and Personalization in R12. 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 iProcurement Requisition Page will be used for demonstration, but any page developed using the OAF
architecture is a candidate for this exercise.

Here we are about to customize the Expenditure Type LOV so that when creating an iProcurement requisition and
entering project related information, the Expenditure Types list of values will present the user with a subset of
expenditure types, instead of all expenditure types.

Analysis:
1] Check About this page':
To find all dependent objects of the base Oracle Application page you wish to extend, use the About this page link
shown at the bottom-left corner of the base page. The link renders the About page which displays page definition,
current session, and technology stack information for that base page. (If you do not see the About this page link,
verify that the FND_DIAGNOSTICS profile option is enabled.)

2] Find the VO Object Name:

3] Fnd the SQL query behind the VO:

Implementation:
Now that we have identified the VO we need to extend, ExpenditureTypeNoAwardLovVO. we need to setup our
JDeveloper environment so that we can create a new View Object that contains our extended code.
1] Setup Jdeveloper:
You can refer the below articles for this:
Initial Setup in JDeveloper for OAF Development
2] Create a New OA Workspace:
In the Applications Navigator right click the Applications Node and select New OA Workspace.

Give a suitable name. Leave the directory path as your default myprojects folder in your JDev Home, ensure that the
Add a New OA Project check box is selected. Select OK.
3] Create a New Project:

Give a suitable project name. Leave the default directory as your myprojects folder in the JDev Home. For a VO
substitution the default package name will need to represent the VO file path on JAVA_TOP with the exception that it
must be prefixed with your custom application shortname, the prefix can actually be anything you like however it is
recommended that the custom application shortname is used.
Click Next and complete the remaining steps.
4] Copy the required files from JAVA_TOP:
Next we need to copy the existing VO component from the apps server and import it into our local file system. In
order to do this we must setup a file structure on our local machine in our project folder so we can copy the VO into it.
Log onto the applications server and navigate to $JAVA_TOP, change into the oracle/apps/icx/ directory (Or which
ever product top you are working with).
For that do the following:
1.

cd $JAVA_TOP/oracle/apps

2.

tar -cvf icx_top.tar icx .. repeat for as many modules as you might need.

3.

FTP each .tar file to \jdevhome\jdev\myclasses.

4.

Extract each .tar file in the \jdevhome\jdev\myclasses directory

5.

Also extract each .tar file in the \jdevhome\jdev\myprojects directory.

Click the Refresh button on the Applications Navigator tab of JDeveloper, you should notice that a new business
components package under oracle.apps.pa has now appeared.
5] Create a new VO Object:
Now that we have the existing projects business components in our environment we need to create our new VO
object that will be used in place of the existing one.

Please note- We do not customize existing components in OA Framework. We extend the existing ones which
means creating a new object that extends the original and we notify the framework that we want to use the new
object rather than the seeded one.
5.1] Test the seeded VO:
We now need to test that the VO we want to substitute does not contain any java errors following the download.
Navigate to the VO in the applications navigator window right click the VO and click edit.
If the VO opens without error then we are ready to move onto the next step. If you get an error message saying that
java errors exist in either the voNameImpl.java file or the voNameRowImpl.java file then do the following:
1.

Get the .class files.

2.

Download a java deconpiler (ex:CAVAJ) and decompile the files to .java files and put it in myprojects folder.

3.

Remake the project.

5.2] Lunch the create new VO Object wizard:

Right click on the project node and select New. Select View Object under Business Tier > ADF Business
Components and click OK.

5.3] Give the details:

Specify the package as xxscm.oracle.apps.icx.lov.server (This component package will hold our extended object so
we need the custom application prefix). Specify the VO name as the name of the custom application concatenated
with the orginal VO name i.e. XxScmExpenditureTypeNoAwardLovVO. Select the original VO in the extends box
using the browse button and select the original VO i.e. oracle.apps.icx.lov.server.ExpenditureTypeNoAwardLovVO.
5.4] Modify the SQL Query:

On step 2 of the VO wizard you can see the SQL statement that we first saw when we were analysing the page. Here
add your modified query.Please note that you should add your additional columns at the end.
In our example the initial query was:

1
2
3
4
5
6
7
8

SELECT et.expenditure_type,
et.sys_link_start_date_active,
et.sys_link_end_date_active,
1 AS dummy_number
FROM pa_expenditure_types_expend_v et
WHERE et.system_linkage_function = 'VI'
AND (TRUNC(SYSDATE) BETWEEN ET.EXPND_TYP_START_DATE_ACTIVE AND NVL(ET.EXPND_TYP_END_DATE
TRUNC(SYSDATE+1)))
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_a
TRUNC(sysdate +1)))

Now the modified query is:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT DISTINCT
et.expenditure_type,
et.sys_link_start_date_active,
ET.SYS_LINK_END_DATE_ACTIVE,
1 AS dummy_number,
P.PROJECT_ID,
BV.BUDGET_VERSION_ID
FROM
PA_PROJECTS_ALL P,
PA_BUDGET_VERSIONS BV ,
PA_FIN_PLAN_TYPES_B PT,
PA_RESOURCE_ASSIGNMENTS RA,
PA_EXPENDITURE_TYPES_EXPEND_V ET
WHERE BV.PROJECT_ID = P.PROJECT_ID

13
14
15
16
17
18
19
20
21
22

AND BV.FIN_PLAN_TYPE_ID = PT.FIN_PLAN_TYPE_ID

AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND PT.PLAN_CLASS_CODE='FORECAST'
AND BV.CURRENT_FLAG='Y'
and ET.EXPENDITURE_TYPE = RA.EXPENDITURE_TYPE
AND ET.SYSTEM_LINKAGE_FUNCTION = 'VI'
AND (TRUNC(SYSDATE) BETWEEN ET.EXPND_TYP_START_DATE_ACTIVE AND NVL(ET.EXPND_TYP_END_DATE
TRUNC(SYSDATE+1)))
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_a
TRUNC(sysdate +1)))

5.5] Generate the Java files:

Once we have clicked through to the end of the wizard the final step is to create the relevant java files.
Now click Finish and the new business components package will be created containing our new extended VO. Right
click on the custom business components package and select Make, this will compile the Impl and RowImpl java
files.
6] Create a Substitution:
Now that we have our newly extended VO, we need to create a substitution file. This substitution file will be an xml
based file that will contain a mapping from the old VO to the new VO, we will use this file to tell Oracle Application to
use our newly extended VO rather than the old one by uploading it to the MDS repository using the JPX Import tool.

Right click on your project node and select Project Properties, click Substitutions under the Business
Components menu. In the Available pane select the original VO , and in the right hand Substitute pane select the
new VO. Once you have done this click the Add button and select OK.
In your JDev home in the myprojects folder you will see a file called projectName.jpx. This is your substitution file and
we will use this later when we deploy the substitution.

Deployment:
1] Deploy the the relevant java and xml files to java top:
Right click on the project node and select New, select Jar File under the General > Deployment Profiles menu.
Name the profile as something meaningful. Leave the directory set as the default myprojects folder and click OK.

In the deployment profile properties deselect the Include Manifest File option. On the filters menu deselect the root
folder and navigate to the xxscm.oracle.apps.icx.lov.server directory, select all the files in this directory and click OK.

You will see in JDeveloper that you now have a deployment profile listed under you application sources node, right
click the .deploy file and select Deploy to JAR file, You will see a deploy tab appear next to the compile log and this
will confirm that the deployment was successful. If you experience issues with compilation i.e. there are issues with
some of the files in your project that are not connected to your custom files then simply remove them from the project
by selecting the top level component package i.e. oracle.app.pa and click the Exclude Project Content button (Little
file symbol with a red cross on it) and re-try the deployment.
Inspect your myprojects folder and you will see you now have a Deploy directory, in this directory will be your jar file
ready for deployment to the apps server. FTP the jar file in binary format to a convienient directory on the apps
server. Then run the below command:
jar -xvf /$JAVA_TOP/XxScmRestrictExpTypes_Deploy.jar
2] Deploy the Substitution file:
You can run a script something like below to deploy the substitution file:

1
2
3
4
5
6
7
8
9
10

echo "--------------------------------------------"
echo "Please enter the following details :"
echo "--------------------------------------------"
echo Enter APPS Password :
read apps_pw
echo "Enter Host TNS DATABASE Server name:>"
read v_host
echo "Enter Host Database TNS Port Number:"
read v_port

11
12
13
14
15
16
17
18
19
20
21
22
23
24

echo "Enter Host Database SID name:"

read v_sid
########################################################################
echo "Importing VO Extension..."
########################################################################

java oracle.jrad.tools.xml.importer.JPXImporter \
$XXSCM_TOP/install/XxScmRestrictExpTypes.jpx -username apps -password $apps_pw \
-dbconnection "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=$v_host)(PORT=$v_port)) (CO
(SID=$v_sid) ) )"
echo "Importing VO Extension successful!!!!..."

Finally restart the webserver (bounce Apache) so that you can see your changes in the application.

Creating Personalizations:
1] Add a new attribute:
Go to Functional Administrator responsibility and go to the path
/oracle/apps/icx/lov/webui/ExpenditureTypeLovRN
Create a new Item and Give the below details:
Item Style

Message Styled Text

Id

XxScmProjectId

Datatype

Number

Rendered

False

View Attribute

ProjectId

View Instance

ExpenditureTypeNoAwardLovVO

2] Add a LOV Mapping to make the LOV dependent ( Here Expenditure Type LOV is dependent to the Projects
LOV):
Go to Functional Administrator responsibility and go to the
path /oracle/apps/icx/por/req/webui/CheckoutSummaryPG
Create a new Item and Give the below details:
Item Style

Lov Map

Id

XxScmProjectIdLovMap

Criteria Item

ProjectIdExpense

* LOV Region Item

XxScmProjectId

Great!!Its over now. Go to iProcurement Requisition Page and check the new Expenditure Type LOV.