Package/Procedure Using In OAF PAGE

Package/Procedure Using In OAF PAGE

Hi Friends we are going to discuss about the Package/Procedure Using In OAF Page. We will share the detail steps to execute the plsql Package or Procedure or function in OAF Page. To execute the plsql Package or Procedure or function we need to have OAF Page controller. In controller we can write the OAF Code to execute and call the plsql Package or Procedure or function  in OAF Page. In this post , we are trying to share the detail step by step with screenshots to use the plsql Package or Procedure or function in OAF Page. Please find below the complete details about plsql Package or Procedure or function.

Step by Step to execute the plsql Package or Procedure or function in OAF Page

Step 1:-

There is General Requirement in Programming to use Package/Procedure.So In this post i am trying to Package/Procedure in OA Framework.

Step 1:- .Create a new workspace name "RohitPackageCalling".

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

Package/Procedure Using In OAF PAGE

Step 2:-
Enter the name of Workspace="RohitProcedureCalling".Click Ok.

Package/Procedure Using In OAF PAGE

Step 3:- Project wizard will open as below click next.

Package/Procedure Using In OAF PAGE

Step 4:- 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.

Package/Procedure Using In OAF PAGE

Step 5:-

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

Package/Procedure Using In OAF PAGE

Step 6:-
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.

Package/Procedure Using In OAF PAGE

Step 7:-
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.

Package/Procedure Using In OAF PAGE

Step 8:-
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.

Package/Procedure Using In OAF PAGE

Step 9:-

Enter the Page Proeprties.

Page Name= RohitPackageCallingPG.

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

Package/Procedure Using In OAF PAGE

Step 10:-
Page has Created as Below :-

Package/Procedure Using In OAF PAGE

Step 11:-

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

Package/Procedure Using In OAF PAGE

Package/Procedure Using In OAF PAGE

Step 12:-
Now we have to set the Application Module for the PageLayoutRN.

Set AM Property for “PageLayoutRN”.

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

Package/Procedure Using In OAF PAGE

Step 13:-

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

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

Package/Procedure Using In OAF PAGE

Step 14:-

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

ID=MainRN

Region Style=tableLayout.

Package/Procedure Using In OAF PAGE

 Step 15:- 
Right Click on "MainRN".Click New=>Click rowLayout.

Package/Procedure Using In OAF PAGE

Then "Region1" has created under "MainRN".

Package/Procedure Using In OAF PAGE

Step 16:- 
Click on "region1".Click "New"=>Click "cellFormat".

Package/Procedure Using In OAF PAGE

Step 17:-
 Click on "region2".Click "New"=>Click "Item".

Package/Procedure Using In OAF PAGE

 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.

plsql Package or Procedure or function in OAF Page

Click Apply and then "Ok".

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

plsql Package or Procedure or function in OAF Page

Under Property Window.Change Some Proeprties.

Action Type=firePartialAction.

Event=update

plsql Package or Procedure or function in OAF Page

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.

plsql Package or Procedure or function in OAF Page

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

If you Want to Learn Oracle Fusion , Please Follow the Oracle Fusion Tutorial