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

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 Framework’s 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:

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_ACTIVE, TRUNC(SYSDATE+1)))
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_active, TRUNC(sysdate  +1)))

Now the modified query is:

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
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_ACTIVE, TRUNC(SYSDATE+1)))
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_active, 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:

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

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)) (CONNECT_DATA= (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.

Reference Article: oaf-modifying-sql-behind-framework-pages (keithturley.wordpress.com)