how to call pl sql package from oaf

How to call pl sql package from oaf

In this post , We will discuss how to call pl sql package from oaf. When we need to perform complex logics in the Code and to do multiple validations then we uses pl sql packages. OAF classes not suitable to table large and complex code so to make it more easy and quick , we do all our logics and calculations outside the oaf classes in the plsql package and then call that packages from OAF controller or from Application Module and this gives the output based on the Input parameter we have passed in the runtime. Here below I am sharing the step by steps for how to call pl sql package from oaf.

 

Details steps for how to call pl sql package from oaf

 

 

1.Create a new workspace name "RohitPackageCalling".

Right Click on "Applications".Click on "NewOAWorkspace".

 Enter the name of Workspace="RohitProcedureCalling".Click Ok.

Project wizard will open as below click next.

Enter the name of the Project="RohitPackageCalling".
Default Package= "rohit.oracle.apps.fnd.RohitPackageCalling"

Package determines the directory where the java class files and other files related to this project strored.
In this directory oracle.apps.fnd must be required."fnd" is the oracle application shortname you can use other applications like "ak" in this.

Enter the RunTime connection details as below.Enter the E-business Suit Application User Name and Password.(already explained in the JDev installation)

Click Next and then Finish.Then Workspace and project has been created as below.

Now our next step is to create a "Application Module" for the OAF Page

Right Click on the project "RohitPackageCalling".Click New

Select "Application Module" and then Ok.

Enter the Application Module name="RohitPackageCallingAM"

Defualt Package="rohit.oracle.apps.fnd.RohitPackageCalling.server"

we create "Application module" under the "Server" directory of the project as OAF Standard.

Click next and then finish.

Now we create a OAF Page as below

Right Click on the Project "RohitPackageCalling".Click New.
Under the OA Components.Select “Page” and then ok.

 

Enter the Page Proeprties.

Page Name= RohitPackageCallingPG.

Defualt Package=rohit.oracle.apps.fnd.RohitPackageCalling.webui

 Page has Created as Below :-

Select the “RohitPackageCallingPG”.Under the Structure Pane.Select the “region1” and then in the property window for this region change the Region ID=”PageLayoutRN”.

ID=”PageLayoutRN”. 

Now we have to set the Application Module for the PageLayoutRN.

Set AM Property for “PageLayoutRN”.

AM=rohit.oracle.apps.fnd.RohitPackageCalling.server.RohitPackageCallingAM.

Now we create a new Region under the “PageLayoutRN”.

Right Click on “PageLayoutRN”.Click New an then Click Region.

Then Region1 has created.Change the Name(ID) of Region1 in Property window with “MainRN”.

ID=MainRN

Region Style=tableLayout.

 Right Click on "MainRN".Click New=>Click rowLayout.

Then "Region1" has created under "MainRN".

Click on "region1".Click "New"=>Click "cellFormat".

 Click on "region2".Click "New"=>Click "Item".

 Then Item1 has created under the "Region2".

Set Properties for item1

Prompt=Supplier.

Again Click on "region1".Click "New"=>Click "cellFormat".

Then "Region3" has created as below.

 

 Click on "region3".Click "New"=>Click "Item".

Then Item2 has created under the "Region2".

Set Properties for item2

Prompt=Total Standard.

Same Like Above."Create Region4" and create Item3 under "Region4".

Set Properties for item3

Prompt=Total Prepayment.

  

 To Show Supplier Lov We need to create the Supplier View Object(VO).

Right Click on the Project "RohitPackageCalling".Click New.

 

View Object Name=SupplierVO

Defualt Package="rohit.oracle.apps.RohitPackageCalling.server".

Click Next and go to step 5 as below.

 

Click Next And then Finish.

Our View Object has been created.Now we will attach View Object "SupplierVO" with "Application Module".

Right Click on "Application Module".Select "EditRohitPackageCallingAM".

Select View Object "SupplierVO".And shuttle them to right.

Click Apply and then "Ok".

Select "Item1" under Region1.and then go the Property Window.

Under Property Window.Change Some Proeprties.

Action Type=firePartialAction.

Event=update

Now we will start our coding Part. So we need to create a controller.

Right Click on "PageLayoutRN".Click "SetNewController".

Enter the Controller Properties.

Controller Name=RohitPackageCallingCO.

Default Package=rohit.oracle.apps.fnd.RohitPackageCalling.webui

After This create package in the Data Base:-

CREATE OR REPLACE package APPS.xx_package_test is
procedure XX_STANDARD(VENDOR_CODE IN VARCHAR,RESULT OUT VARCHAR) ;
procedure XX_PREPAY(VENDOR_CODE IN VARCHAR,RESULT_2  OUT VARCHAR) ;
end;
/

CREATE OR REPLACE package BODY APPS.xx_package_test is
procedure XX_STANDARD(VENDOR_CODE VARCHAR,RESULT OUT VARCHAR)
IS
A number;
BEGIN

SELECT SUM(INVOICE_AMOUNT) INTO A FROM AP_INVOICES_ALL A1,PO_VENDORS PV
WHERE INVOICE_TYPE_LOOKUP_CODE='STANDARD'
AND A1.VENDOR_ID=PV.VENDOR_ID
AND PV.SEGMENT1=VENDOR_CODE;

RESULT:=A;

EXCEPTION WHEN OTHERS THEN
A:='ERROR';
RESULT:=A;
END;

procedure XX_PREPAY(VENDOR_CODE VARCHAR,RESULT_2 OUT VARCHAR)
IS
A number;
BEGIN

SELECT SUM(INVOICE_AMOUNT) INTO A FROM AP_INVOICES_ALL A1,PO_VENDORS PV
WHERE INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND A1.VENDOR_ID=PV.VENDOR_ID
AND PV.SEGMENT1=VENDOR_CODE;

RESULT_2:=A;

EXCEPTION WHEN OTHERS THEN
A:='ERROR';
RESULT_2:=A;
END;

end;
/

Now We I am going to create a method in the "Application Module Java Class".

As below double click on "RohitPackageCallingAMIMPL.java" class file.

Then the Class file will open.

Write Code in RohitPackageCallingAMIMPL.java

  public String XXSTANDARD(String item1)

  {
  OADBTransaction oadbtransaction = (OADBTransaction)getTransaction();
  OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getTransaction();
  String retValues;
  StringBuffer str = new StringBuffer();

  str.append( " BEGIN ");
   str.append( " xx_package_test.XX_STANDARD( ");
   str.append( "       VENDOR_CODE           => :1, ");
   str.append( "       RESULT    => :2  ");
   str.append( "    ); ");
   str.append( " END; ");
  
  OracleCallableStatement oraclecallablestatement =
    (OracleCallableStatement)oadbtransaction.createCallableStatement(str.toString(), 1);
   
  try{
    oraclecallablestatement.setInt(1,  Integer.parseInt(item1) );

    oraclecallablestatement.registerOutParameter(2, Types.VARCHAR);

    oraclecallablestatement.execute();
                     
    retValues = oraclecallablestatement.getString(2);
   }
   catch(Exception e)
   {
    throw OAException.wrapperException(e);
   }
   return retValues;
  }

Now Write Code for how to call pl sql package from oaf Under The Controller Class File

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

  {
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule am = pageContext.getApplicationModule(webBean);
   
    if ("update".equals(pageContext.getParameter(EVENT_PARAM)))
    {
   
    Serializable[] parameters1 = { pageContext.getParameter("item1")};
   
    String retVals1 = (String)am.invokeMethod("XXSTANDARD", parameters1);
   
    OAMessageTextInputBean textBean=(OAMessageTextInputBean)webBean.findIndexedChildRecursive("item2");
   
   
    textBean.setValue(pageContext, retVals1);
 

   
    System.out.println("Default File Path---->"+textBean);
   
    throw new OAException(retVals1, OAException.INFORMATION);
   
  

  }
  }

After this We have to compile all Project.So Right Click on Project "RohitPackageCalling"=>Click Rebuild.

After This Run the Page.

OutPut is Below.

When you select the Supplier Name in the Lov Automatically its Total Amount of Standard Invoices will be Shown under the field "Total Standard Invoice".In this post i have uses only a method of "Standard Invoices". My Total Prepayment Part will be pending.