Another Way : Passing Table Type Object from OAF

This article will describe you another way of passing table type object from OAF. I have already posted same article on it in 2010. Here is the link for your reference

http://oracleanil.blogspot.ae/2010/09/oaf-passing-table-type-object-to-oracle.html


Pre steps

1. We need to have object Type & Table Type for this.
   a) XX_OM_ORDER_TBL_TYPE
   b) XX_OM_ORDER_REC_TYPE

/* Script for your reference */

CREATE OR REPLACE TYPE APPS.XX_OM_ORDER_TBL_TYPE IS TABLE OF XX_OM_ORDER_REC_TYPE;


CREATE OR REPLACE TYPE APPS.XX_OM_ORDER_REC_TYPE AS OBJECT (
  ORDER_ID         NUMBER,
  ORDER_TYPE       VARCHAR2(20),
  ORDER_DATE       DATE,
  DESCRIPTION      VARCHAR2(200) );

*/ End Here.


2. Custom Table to insert data.
   a) xx_order_details
   
   /* Script for your reference */
   
   CREATE TABLE APPS.XX_ORDER_DETAILS
(
  ORDER_NUMBER       VARCHAR2(100 BYTE),
  ORDER_DESCRIPTION  VARCHAR2(240 BYTE),
  ORDER_TYPE         VARCHAR2(100 BYTE),
  ORDER_DATE         DATE
)

*/ End Here.

OAF Stuff starts here

1. Here we have to create components like - Controller, Page, AM, VO. 
2. Then we have to search for Object & Table type in Jdeveloper so that we can generate JAVA for these objects.

   Navigation : Connection Navigator --> Database --> DB SID --> Types
   

   
3. Back in the Application Navigator window you will find that reference java files are created

4. Order page will have table region and a save button.

5. Now we will first create blank rows in VO & then handle the event of Save button in Controller and add the logic in AMImpl file

//Controller code start here

package xx.oracle.apps.ont.order.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

/**
 * Controller for ...
 */
public class OrderCO extends OAControllerImpl
{
  public static final String RCS_ID="$Header$";
  public static final boolean RCS_ID_RECORDED =
        VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

  /**
   * Layout and page setup logic for a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
     //init method to initialize the VO & to create 5 rows in it.
      pageContext.getApplicationModule(webBean).invokeMethod("init");
  }

  /**
   * Procedure to handle form submissions for form elements in
   * a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
        super.processFormRequest(pageContext, webBean);
      // AM method to handle the save button logic

      pageContext.getApplicationModule(webBean).invokeMethod("saveOrderData");

//Controller code end here

//AMImpl code start here

package xx.oracle.apps.ont.order.server;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OAExceptionUtils;

import oracle.jbo.Row;
import oracle.jbo.domain.Number;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import xx.oracle.apps.ont.order.server.OrderVOImpl;

/*
* Method to initialize the VO & to create blank rows in VO */
public void init() {
        OrderVOImpl vo = getOrderVO1();
        if (!vo.isPreparedForExecution()) {
            vo.executeQuery();
        Row row = null;
        for (int i = 0; i <= 5; i++) {
            row = vo.createRow();
            vo.insertRow(row);
        }
        row.setNewRowState(Row.STATUS_INITIALIZED);
    }

/* Wrapper to save the Order Entry Data. getOrderRecord() method will return the table type. We are getting this data from Order VO and then passing it to the object type. 

Further callOmOrderApi is getting called in this method which takes orderRec as a input parameter and calls the Pl/SQL to insert the data into the custom table.  */

  public void saveOrderData() {
        XxOmOrderTblType orderRec = getOrderRecord();
        callOmOrderApi(orderRec);
    }

    public XxOmOrderTblType getOrderRecord() {

        OrderVOImpl vo = getOrderVO1();
        XxOmOrderRecType[] orderRec = null;
        boolean l_error = false;

        if (vo != null) {
            System.out.println("VO is not null");
            Row[] vorow = vo.getAllRowsInRange();
            orderRec = new XxOmOrderRecType[vorow.length];

            for (int i = 0; i < vorow.length; i++)
                try {
                    XxOmOrderRecType recType = new XxOmOrderRecType();
                    OrderVORowImpl row = (OrderVORowImpl)vorow[i];

                    BigDecimal bg = new BigDecimal(row.getOrderNumber());
                    recType.setOrderId(bg);
                    recType.setOrderType(row.getOrderType());
                    recType.setDescription(row.getOrderDescription());

                    oracle.jbo.domain.Date d = row.getOrderDate();
                    java.sql.Date jd = d.dateValue();
                    System.out.println("Conv Date" + jd);
                    java.sql.Timestamp t = new Timestamp(jd.getTime());
                    System.out.println("Conv Date 1 " + t);
                    recType.setOrderDate(t);

                    orderRec[i] = recType;

                } catch (Exception e) {
                    throw new OAException("Error " + e.getMessage());
              }

        }
        XxOmOrderTblType table_row = new XxOmOrderTblType(orderRec);
        return table_row;
    }

/* This method accept table type parameter and calls PL/SQL procedure to write data to DB.

 *

    public void callOmOrderApi(XxOmOrderTblType issueTable) {
        Number lo_count = null;
        String lo_status = null;
        String ls_msgData = null;
        OracleCallableStatement lo_oraclecallablestatement;
        OADBTransaction lo_dbtransaction = getOADBTransaction();

        System.out.println("Above the callable statement");
        lo_oraclecallablestatement = null;
        try {
            lo_oraclecallablestatement = 
                    (OracleCallableStatement)lo_dbtransaction.createCallableStatement("begin xx_create_order_prc(:1, :2); end;", 
                                                                                      1);
            lo_oraclecallablestatement.setObject(1, issueTable, 
                                                 OracleTypes.ARRAY);

            lo_oraclecallablestatement.registerOutParameter(2, Types.VARCHAR);
            lo_oraclecallablestatement.execute();
            lo_status = lo_oraclecallablestatement.getString(2);

            if ("S".equalsIgnoreCase(lo_status)) {
                lo_dbtransaction.commit();
                lo_dbtransaction.putDialogMessage(new OAException("Order Created", 
                                                                  OAException.CONFIRMATION));
            } else {
                lo_dbtransaction.rollback();
                OAExceptionUtils.checkErrors(getOADBTransaction());
            }
            if (lo_oraclecallablestatement != null)
                lo_oraclecallablestatement.close();
        } catch (SQLException sqlexception) {
            throw new OAException(sqlexception.getMessage());
        }


    }

PL/SQL Code

//This code accept table type parameter then we loop through the records and insert those records to custom table and finally it return back the status as 'E' or 'S'

CREATE OR REPLACE PROCEDURE APPS.xx_create_order_prc (
      p_i_order_tbl                    XX_OM_ORDER_TBL_TYPE,
      p_o_return_status   OUT NOCOPY   VARCHAR2
   )
   IS    
   BEGIN
   
         FOR i IN p_i_order_tbl.FIRST .. p_i_order_tbl.LAST
         LOOP
            INSERT into xx_order_details VALUES (
            p_i_order_tbl (i).order_id,
            p_i_order_tbl (i).description,
            p_i_order_tbl (i).order_type,
            p_i_order_tbl (i).order_date);
           
         END LOOP;
   
   p_o_return_status := 'S';
 
   EXCEPTION
      WHEN OTHERS
      THEN
         p_o_return_status := 'E';
   
   END xx_create_order_prc;

/

Thanks,
--Anil