Oracle Application Framework

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
updateRow()
insertRow()
lockRow()
deleteRow()

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
BEGIN
INSERT INTO xx_oaf_demo_simple_01
(person_id
,first_name
,last_name
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_person_id
,'plsql ' || p_first_name
,'plsql ' || p_last_name
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
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()
{
try
{
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());
oraclecallablestatement.execute();
}
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

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel