<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color

Related Items

Home arrow Technical Articles arrow Oracle Application Framework arrow PLSQL Entity based OA Framework - OAPlsqlEntityImpl
This website has now moved to http://apps2fusion.com





PLSQL Entity based OA Framework - OAPlsqlEntityImpl | Print |  E-mail
Written by Anil Passi   
Sunday, 04 March 2007
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



Comments (10)add
...
written by Ramakrishna , March 04, 2007
Hi
Anil,

Thanks a lot for Example...
...
written by Ramkumar , March 08, 2007
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

Regards
Ram
...
written by Anil Passi , March 12, 2007
Hi Ram,

I will surely do that

thanks
anil
...
written by gattu , May 17, 2007
Hi Anil,

Suppose 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.

Thankx,
Gattu
...
written by Santy , August 23, 2007
Gattu,
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 "FNDsmilies/cheesy.gifiagnostics" 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.
...
written by Anil Passi , October 07, 2007
Thanks Santy
Problem using PL/SQL from J2EE
written by Michael , October 11, 2007
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_acceptance 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.

Example:
BEGIN
DBMS_OUTPUT.PUT_LINE ('---START---');
DBMS_OUTPUT.PUT_LINE ( myPackage.myFunction('7770000118',2,2,1000) );
DBMS_OUTPUT.PUT_LINE ('END');
END;

The myPackage.myFunction simulates the using the fnd_global.initialize and set the org_id using dbms_application_info.set_client_info(' xxOrg_id ');

However when I call the myPackage.myFunction from the java code (using CallableStatement) giving the myPackage.myFunction 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?

Best regards
Michael C.
...
written by Anil Passi , October 11, 2007
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.

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

Please can you put debug message for the parameters being set in java code.
You can use oadbtransaction.writeDiagnostics for this purpose.

Following this, setup FND%Log% profiles and then examine the debug messages from fnd_log_messages

You may also file the link below of some help http://oracle.anilpassi.com/de...iques.html


Thanks,
Anil Passi
Problem using PL/SQL from J2EE
written by Michael Callisen , October 23, 2007
Hi Anil Passi

I already tested that the myPackage.myFunction 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.myFunction simulates the using the fnd_global.initialize and set the org_id using dbms_application_info.set_client_info(' xxOrg_id '), otherwise the API would never work.

BR. Michael

Get rows from API using a View Object
written by Celin , March 10, 2008
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?

You must be logged in to a comment. Please register if you do not have an account yet.

busy