OAF – Modifying SQL Behind Framework Pages
All OAF Pages use a number of “View Objects” to execute SQL queries against the E-Business Suite Database. These View Objects are actually XML files on the application server that contain an SQL query, this XML file is used in conjunction with a Java Utility called the JPX Importer to load a pointer to the content of the XML file into a number of Database tables that EBS uses to identify which SQL queries it should be executing when a page loads. These tables are collectively known as the MDS (Meta Data System) Repository. The MDS holds a lot more than just SQL query definitions but for the scope of this article we need not cover the other content.
For this article we are going to use the Oracle Projects Search screen as our OAF web page on an R12 instance. We are going to modify the SQL that is used to query back a list of projects so that the SQL only returns projects that are within their current start date and end date as opposed to returning all projects.
Analysis
First of all we need to identify which “View Object” we will be working with, to do this we will need to activate OAF diagnostics. To do so locate the profile option “FND: Diagnostics” and set it to “Yes”. Now take a look at the page we are going to be extending…
Fig 1
By turning on the “FND: Diagnostics” profile option you will now be able to see two new links on the page. The “Diagnostics” link which will appear in the header and the footer of the page and the “About This Page” link which will appear in the footer only. In order to identify the VO that is being used by the page we will first examine the “About This Page Link”.
When you click on the “About This Page” link you will see an array of tabs all of which do various tasks which we can use as OAF developers however for the purposes of this article we will be looking at the default “Page” tab which you see when the page opens.
All OAF pages are made up of a number of BC4J (Business Components for Java) and Web elements known as the MDS content or “Web Beans”. The BC4J content exists as compiled java code and XML under JAVA_TOP on the application server and the web content exists as meta data in the MDS Repository. The web content is organised into a tree structure i.e. you have a page which contains a table which contains fields etc… When the page loads this “Bean Hierarchy” is rendered according to its structure and this structure is what we can see in the “Page Definition” section of the Page tab. Click the “Expand All” Link.
If you are familiar with OAF development this screen will be very apparent to you as representing the page structure layout that you are used to seeing in JDeveloper when building a page and navigating it will be simple. If you are not used to OAF page development and you are simply trying to identify the SQL behind this page then examine the View Object Column until you see populated content, you should then be able to determine that the “Name” column holds content such as “messageStyledText: Project Manager” and that this is describing the “Project Manager” column that we see on the OAF page. We can also see that there is a collection of columns indented below the “Bean” “table: My Projects Result Container” which refers to the result table that we see on the page.
Fig 2
Implementation
Now that we have identified the VO we need to extend, oracle.apps.pa.project.server.ProjectListGenVO, we need to setup our JDeveloper environment so that we can create a new View Object that contains our extended code. If you have not already got your JDeveloper environment setup then do so now, you can read how to at the Apps Tech Blog post “OAF – Downloading and Configuring JDeveloper”.
Start JDeveloper and follow the instructions below. Please note that this article only covers the procedure for JDeveloper 10g. The process although similar is slightly different in 9i.
In the Applications Navigator right click the Applications Node and select “New OA Workspace”
In the file name box enter a suitable file name, if you are often working with different clients then it may be an idea to call this XXCLIENT.jws, if you are working for your own company you may want to call it XXCUST.jws, the workspace name is however completely personal preference and up to you as a developer how you organise your workspaces and projects within JDeveloper. 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.
Fig 3
Select “OK”.
The new project wizard will now be launched, select “Next”.
In the project name enter a meaningfull name, I recommend that the project name be pertinent to the piece of work you are doing i.e. XXCUST_projects_list, 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, in this example we would specify the default package as xxcust.oracle.apps.pa.project.server
Fig 4
Click “Next”
Ensure that the “Use repository for design time” box is not selected and click “Next”.
Select your DBC file, enter your applications username and password, enter ICX as the responsibility application shortname and responsibility key. For more information regarding this setup step including obtaining your DBC file and setting up your user account please refer to the Apps Tech Blog post “OAF – Downloading and Configuring JDeveloper”.
Fig 5
Click “Next”
Click “Finish”
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/pa/ directory (Or which ever product top you are working with)
Fig 6
Zip the project application directory and save it to a directory that you can ftp from. i.e. zip -r $HOME/pa.zip pa
FTP the pa.zip file in binary mode back to your local machine and extract it to your user JDev Home under myprojects/oracle/apps/ (You will have to create the directory structure manually if it does not exist), you should also extract the contents to your myclasses folder in the same way i.e. myclasses/oracle/apps/
You should now have the following file structure in your local file system under the oracle.apps directory in myprojects and myclasses
Fig 7
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.
Fig 8
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. Remember we do not customise 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.
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 i.e. Application Sources > oracle.apps.pa > project > server > ProjectListGenVO, right click the VO and click edit (If you have not already done so you will now need to configure your database connection, for more details on how to do this see the apps tech blog article “OAF – Downloading and Configuring JDeveloper”). 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 you will need to obtain the original source files for the files listed as being in error and add them to your myprojects folder in the relevant directory and recompile the project, unfortunately Oracle do not allow you to obtain the source files and you may need to employee the services of an Oracle consultant who would have access to the files.
Before closing the base VO window take a look at the “Java” settings and make a note of the boxes that are checked/unchecked, we will need this for later.
If you havn’t already done so Close the Edit VO window.
Right click on the project node and select “New”. Select “View Object” under Business Tier > ADF Business Components and click “OK”
The View Object Wizard has been launched, click “Next”.
Specify the package as xxcust.oracle.apps.pa.project.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. XxcustProjectListGenVO. Select the original VO in the “extends” box using the browse button and select the original VO i.e. oracle.apps.pa.project.ProjectListGenVO
Fig 9
Click “Next”
On step 2 of the VO wizard you can see the SQL statement that we first saw when we were analysing the page. For this example we are simply going to wrap the SQL as follows and add an additional where clause statement:
SELECT * FROM (ORIGINAL_QUERY) WHERE trunc(sysdate) between project_start_date and nvl(project_end_date,sysdate)
Click the “Test” button to validate that your modified SQL is valid.
Click “Next” until you reach the end of the wizard, If you encounter the following error at step 4 “Each row in the query results column msut be mapped to a unique query attribute in the mapped entity columns” then their is something wrong with the base VO, in this example I did experience this error and found that the original VO SQL had 3 missing aliases that the view object attributes were referencing, In order to correct the error you must make the change to the base VO by editing the SQL, I successfully added the missing aliases and then re-ran the create new VO instructions above and it worked correctly.
Once we have clicked through to the end of the wizard the final step is to create the relevant java files. When a VO is implemented we always generate a voNameImpl.java file but we dont always have a voNameRowImpl.java file, the best way to tell which files to implement is to look at the java screen for the seeded VO and ensure that our new VO has the same settings.
In this case we need both so our final wizard screen would look like this
Fig 10
Now click “Finish” and the new business components package will be created containing our new extended VO.
Fig 11
Right click on the custom business components package and select “Make”, this will compile the Impl and RowImpl java files.
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 (XXCUST_projects_list) and select “Project Properties”, click “Substitutions” under the “Business Components” menu. In the “Available” pane select the original VO i.e. oracle.apps.pa.project.server.ProjectListGenVO, and in the right hand “Substitute” pane select the new VO i.e. xxcust.oracle.apps.pa.project.server.XxcustProjectListGenVO. Once you have done this click the “Add” button and select “OK”.
Fig 12
In your JDev home in the myprojects folder you will see a file called projectName.jpx in my case it would be called XXCUST_projects_list.jpx, this is your substitution file and we will use this later when we deploy the substitution.
Deployment
In order to deploy our solution to the actual ebusiness suite instance we need to copy the relevant java and xml files to java top and also upload our substituion file to the MDS repository. You can do this just by simply transfering the files using an FTP tool however I find it is best to create an archive file that will retain the folder hierarchy and allow you to issue a single transfer and unpack command. For a large OAF project we may have files that have to be transfered to different locations and because of this I tend to create 3 types of jar files called BC4J, MDS and SRC. These three files will contain the BC4J files like class files and VO’s, the MDS files such as pages and regions and the java source files.
Following the transfer we can issue a java call from the command line on the apps server to perform the MDS upload.
For this example a VO substitution only involves BC4J objects and java source files, the solution does not require that we package up our source code so for the sake of simplicity I will just create one archive file called BC4J.jar
Right click on the project node and select “New”, select “Jar File” under the General > Deployment Profiles menu. Name the profile as something meaningful, in this example I will stick to convention and call it “XXCUST_PROJECTS_LIST_BC4J” . 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 xxcust.oracle.apps.pa.project.server directory, select all the files in this directory and click “OK”
Fig 13
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 successfull. 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.
Fig 14
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.
Now that we have both our BC4J objects for java_top and our substituion file for the MDS we need to transfer them both to the apps server, FTP both the files (remember to send the jar file in binary format and the jpx file in ascii) to a convienient directory on the apps server.
Now extract the jar file to java_top i.e.
cp home/kturley/*.jar $JAVA_TOP
jar -xvf /$JAVA_TOP/XXCUST_PROJECTS_LIST_BC4J.jar
Now upload the substitution file to the MDS using the following command:
java oracle.jrad.tools.xml.importer.JPXImporter $HOME/kturley/XXCUST_projects_list.jpx -username apps -password xxxx -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.oracle.co.uk)(PORT=1551))(CONNECT_DATA=(SID=XXXXX)))”
Make sure you specify the correct values for
1. Path of you jpx file
2. apps password
3. host name
4. port
5. database SID
Finally restart the webserver so that you can see your changes in the application
For R11:
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start
For R12:
$INST_TOP/admin/scripts/adoacorectl.sh stop
$INST_TOP/admin/scripts/adoacorectl.sh start
« Previous Entries
Oracle Apps Tech Blog 