OAF – Modifying SQL Behind Framework Pages

Posted on December 9, 2009. Filed under: Oracle Applications Framework | Tags: , , , , , , , , , , , |

This article will discuss the process of modifying the seeded SQL that exists behind an Oracle Applications Framework Page, by a Framework page we mean any browser based EBS interface screen including selfservice pages. The article will discuss the analysis process involved in making such a change, the implementation of the extension using JDeveloper 10g and the subsequent uploading and “Substitution” of the new code within the MDS (Meta Data System) Repository.

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

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

Fig 2

We can see in Fig 2 that the “My Projects Result Container” is driven by the “ProjectListVO” View Object. In order to examine the SQL within the VO and to identify more information from the VO Click on the “ProjectListVO” link.
 
From the “About View Objects” screen we can see a large SQL statement, The “Entity Objects” associated with the VO, and a list of the VO’s available attributes. Any of these attributes could be used as additional content on the “Project Search” page simply by personalising the page however we will not be covering that process in this article.
 
At the top of the page we can also see the name of the VO and a file path in the form: oracle.apps.pa.project.server.ProjectListGenVO this gives us the location of the VO on JAVA_TOP. Having identified the VO and its location on JAVA_TOP we can close the “About This Page” diagnostic screen and return to the Projects Search Screen.
 

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

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

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

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

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

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

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

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

Fig 10

Now click “Finish” and the new business components package will be created containing our new extended VO. 

Fig 11

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

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

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

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  

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

8 Responses to “OAF – Modifying SQL Behind Framework Pages”

RSS Feed for Oracle Apps Tech Blog Comments RSS Feed

Hi

very good article, it helps me a lot
I have to extend the same view object (ProjectListGenVO), but I can’t avoid
This “Each row in the query results column msut be mapped to a unique query attribute in the mapped entity columns” error. Could you tell me which attributes aliases did you exactly correct and how. I have problems with transient attributes: ToCharPpoaOpportunityValue1, ToCharPpoaProjfuncOppValue1, ToCharPpoaProjectOppValue1. If I delete them it works fine. I’m working on EBS R12.1.2

Hi Jaja,

In my VO extension example we did not add any new columns we only modified the selection criteria. By the looks of your attribute names I would imagine that these are not already in use within the query. I would recomend that you alias the new SQL columns with something a bit more meaningfull but in particular make sure that you click “Apply” in the SQL window first and make sure that the attributes have updated in the attribute tab, also ensure that your attribute mappings are correct, your transient attributes (i.e. ones not bound to an EO) will have an “SQL” image against the attribute mapping and your EO attributes will have an “xyz” image. Ensure that you are not mapping more than one query column (left hand side of the attribute mapping screen) to the same view attribute (right hand side of the attribute mapping screen).

Many Thanks for this article. By following the mentioned steps, I was able to extend the Issues at Control of Project List.
I’ve learned many wonderful things from this article, thank you.

One of the best explanations i have ever seen….Its really very nice presentation

Hi,
It is really nice blog, I have followed the same sequence of steps to extend and delopy the VO object.
JPXImport completed sucessfully,
\Imported document : /oracle/apps/oks/quote/server/customizations/site/0/SubLinesVO
Import completed successfully

but when I ran the Java concurrent program, I got the below error message
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = oracle.jbo.NoDefException: JBO-25002: Definition xx.oracle.apps.oks.quote.server.xxSubLinesVO of type View Definition not found;
OKSQuoteCP:runProgram: In the finally section

Plz, Let me know where I am missing

Hi Ram,

I am assuming that your concurrent program is java based and is attempting yo use your substituted component. You need to make sure in this instance if you are using different machines for your concurrent processing that the files have been deployed to the appropriate machines. the fact that it is looking got xx. shows that your substitution has worked…it just cant physically locate the file.

Thanks

Keith

Hi Keith,
Thanks you for a great article as it helped me in understanding the OAF extension and I am working on the same now.
I am extending the PoRequisitionLineEO to rebuild the account if the user changes the Requisition Line DFF’s values. Everything is spot on as I completed the task with no issues at all. After deploying the .class, .xml and .java files on to the server I then uploaded the substitution file to the MDS using JPX Importer it finished successfully. Finally bounced the middle tier and verified that the substitution had taken place both in db using jrd_utils as well as using Functional Administrator resp.

when I did the test the charge account build wf never seems to have triggered.

Am I missing any thing? we are going live in 2 weeks time and the users have to test this customization before that. So appreciate if you could guide help in this regards.

Thanks,
Aarsh

Hi Aarsh,

I’m afraid I am not familiar with that process. How is the workflow normally triggered? I assume you are expecting the dff update to trigger it?

As far as the OAF extension is concerned is the dff value u enter getting persisted to the database?

Keith

Keith


Where's The Comment Form?

  • December 2009
    M T W T F S S
    « May   Dec »
     123456
    78910111213
    14151617181920
    21222324252627
    28293031  

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: