Fusion Blog

EBS Blog


Contact Us

OA Framework - All Articles
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
In our previous OA Framework training article, we saw how to build a simple "base table" screen using OA Framework. In that example, we used default Java Entity Object. In this article we will discuss the steps for a simple OA Framework screen which is based on PLSQL Entity Object. By reading this you will learn the concept of using PL/SQL API as Entity Object, in OA Framework. The PL/SQL APIs can be used when your Update/Delete/Insert operations for a primary table have impact on other related tables too. The best practice is to implement this objective using Java Entity Objects itself. However in case you have an existing & well tested PL/SQL API, then it makes sense to reuse that API in your framework screen.

Give me an example where Oracle uses PL/SQL Entity Object in framework?
In Oracle's user management module [UMX], the class FNDUserEOImpl is a PL/SQL Entity Object.
Lets say if the USER_NAME in FND_USER is modified. For this purpose Oracle have an API named FND_USER_PKG.CHANGE_USER_NAME.
This API does three steps when the username changes
Step 1. Changes the user_name in FND_USER
Step 2. Propagates the change in name to wf_local_roles using API wf_local_synch.propagate_user
Step 3. Initiates change to OID for [Single SignOn], by calling API fnd_ldap_wrapper.change_user_name
As you can see, Oracle already have an API to do all of the above steps, hence there isn't much value in reimplementing these steps in Java. Hence UMX uses a PL/SQL based Entity Object.

What are the minimal steps for implementing PLSQL based Entity Object?
1. Your entity object implementation class will extend class OAPlsqlEntityImpl
2. You will override one or all of the below methods, to implement logic using PL/SQL

What exactly happens in the method, say insertRow()?
The pseudo code, is as below
public void insertRow()

//Write code for JDBC Callable Statement
//Set your in out parameters of PLSQL API, using JDBC callable statement
//Execute JDBC Statement to execute PL/SQL

How will OA Framework know that insertRow() in EOImpl [Entity Object Implementation class] must be executed?
If you have extended your Entity Object from OAPlsqlEntityImpl, OA Framework then know that PL/SQL Implementation in method insertRow() must be called.

What are the steps in brief to try this as an exercise?
They quickest way to try this out is by following below steps
Step 1. Implement the Java Entity Object based simple screen, as shown in article OA Framework Simple Java Entity Screen
Step 2. Create a PL/SQL API that inserts records into the table xx_oaf_demo_simple_01
Step 3. Modify the EO Implementation of Step 1, with below changes
a. Extend the EO from OAPlsqlEntityImpl
b. Write a method insertRow() in EOImpl, that does calls PL/SQL API of Step 2, using JDBC

By implementing the above steps, as soon as User Clicks on button Save Data [to commit], if the record status in EO is INSERT, then Fwk will execute insertRow() method in EO.

What are the steps in detail to try this as an exercise?
Step 1.
Implement the Java Entity Object based simple screen, as shown in article OA Framework Simple Java Entity Screen
Step 2. Create the API as below
CREATE OR REPLACE PROCEDURE xx_oaf_plsql_demo_prc(p_person_id IN INTEGER
,p_first_name IN VARCHAR2
,p_last_name IN VARCHAR2) IS
INSERT INTO xx_oaf_demo_simple_01
,'plsql ' || p_first_name
,'plsql ' || p_last_name
END xx_oaf_plsql_demo_prc;
NOTE: We are appending text "plsql " to first name and last name entered by the user

Step 3. Modify the EO Implementation of Step 1, with below changes
import java.sql.SQLException;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.apps.fnd.framework.server.OAPlsqlEntityImpl;

public class XxOafDemoSimple01Impl extends OAPlsqlEntityImpl
//.Usual EO Fwk generated code
public void insertRow()
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getDBTransaction();
String s = "begin xx_oaf_plsql_demo_prc(p_person_id => :1, p_first_name => :2, p_last_name => :3); end; ";
OracleCallableStatement oraclecallablestatement = (OracleCallableStatement)oadbtransactionimpl.createCallableStatement(s, -1);
oraclecallablestatement.setNUMBER(1, getPersonId());
oraclecallablestatement.setString(2, getFirstName());
oraclecallablestatement.setString(3, getLastName());
catch(SQLException sqlexception)
throw OAException.wrapperException(sqlexception);
catch(Exception exception)
throw OAException.wrapperException(exception);

Step4 Test the changes.

Run the screen and create a new record as below

Verify the results. You will notice that API xx_oaf_plsql_demo_prc prefixed text plsql with the first name and last name.

If you wish to see the source code, then use the link below.
Source Code Link for Project
Direct Link to EO, you will find insertRow() at bottom of the file

Anil Passi


0 #1 Ramakrishna 2007-03-04 00:00

Th anks a lot for Example...
0 #2 Ramkumar 2007-03-08 00:00
hi anil,
for OAFramework what are Technical Documents you are preparing.if u have any technical documets like MD50,MD70 on OAFramework plz put in this website.plzzzz

0 #3 Anil Passi 2007-03-12 00:00
Hi Ram,

I will surely do that

0 #4 gattu 2007-05-17 00:00
Hi Anil,

Suppos e there is OA Framework Page and one would like to find out the tables which this page is based on, the column which it refers...can you please give an example page and the drilldown strtegy.

Tha nkx,
0 #5 Santy 2007-08-23 20:41
If you worked on forms you might know "Examine" under diagnostics ,There is a similr or may be more powerful functionality "About this Page" in OAF , for Enabling the link set
profile "FND:Diagnostic s" to Yes for your user , once you have the link coming in your page ,click on the link , click on Expand all below "Page Definition" header , it will show all fields on the page ,look for the field you are looking for and note down the correpsonding VO Name ,
Once you have th VO Name Expand "Business Component References Details" to find the VO,
Click on VO Link ,and you should be able to see VO query and from there your tables.
0 #6 Anil Passi 2007-10-07 07:23
Thanks Santy
0 #7 Michael 2007-10-11 15:03
Hi Anil

I making a customized extension to EBS 11.5.10 (self-service) where I'm using the
Purchase Order Change APIs (public APIs). To be more exact the PL/SQL package po_change_api1_ s function record_acceptan ce and update_po.

I call the API’s from a PL/SQL package which is called from the middle-tier (J2EE). I tested my PL/SQL package in SQL*PLUS and it works fine.

DBMS_OUTPUT.PUT _LINE ( myPackage.myFun ction('77700001 18',2,2,1000) );

The myPackage.myFun ction simulates the using the fnd_global.init ialize and set the org_id using dbms_applicatio n_info.set_clie nt_info(' xxOrg_id ');

However when I call the myPackage.myFun ction from the java code (using CallableStateme nt) giving the myPackage.myFun ction the same parameters the Purchase Order Change APIs does not read the parameters correctly. As if the parameters were null.

The funny thing it that I used this way of calling others API’s without any problems thus I don’t understand what could be the problem. I even tried granting like this: GRANT EXECUTE ON po_change_api1_ s TO myPackageSchema WITH GRANT OPTION, but nothing helps.

My question is have you experienced a similar problem and it disappear when you installed the program on the middle-tier?

B est regards
Michael C.
0 #8 Anil Passi 2007-10-11 15:11
Hi Michael

I doubt if this has something to do with grant permissions, because OAF connects to APPS schema, and all the business pl/sql packages in EBS are owned by APPS schema itself.

Perhap s this is an issue with JDBC Code, where you set parameters.

Pl ease can you put debug message for the parameters being set in java code.
You can use oadbtransaction .writeDiagnosti cs for this purpose.

Follo wing this, setup FND%Log% profiles and then examine the debug messages from fnd_log_message s

You may also file the link below of some help www.google.com/search?q=site:apps2fusion.com+debugging-oa-framework-7-different-techniques

Anil Passi
0 #9 Michael Callisen 2007-10-23 11:16
Hi Anil Passi

I already tested that the myPackage.myFun ction receives the correct parameters, thus I don't understand what else could be the problem. The thing is the when I executing the java code I'm not logged in as a user (even though added it to the project settings and the web.xml file). This means that I will never be able to get some meaningfull error message within Jdev.

That's why the myPackage.myFun ction simulates the using the fnd_global.init ialize and set the org_id using dbms_applicatio n_info.set_clie nt_info(' xxOrg_id '), otherwise the API would never work.

BR. Michael
0 #10 Celin 2008-03-10 22:29
Hi Michael!
Maybe I'm wrong writting in this blog, however a need your opinion. I have an API that return me a table of rows. I need to show this rows in a APP page like a Table and I don't know exactly how to do it. Can I use a View Object?

Add comment

Security code

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner