Anil's Blog is Best Viewed on GOOGLE CHROME

Please contact us for Off-shore projects, Online Trainings and any kind of Operational & Support issues

Thursday, December 4, 2014

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

Sunday, August 31, 2014

Find Duplicate Row OR Copy/Clone VO Row

This article will give you idea on how to restrict user to enter duplicate rows. The requirement came when we want to enhance the user experience by providing the functionality to allow multiple Tools issue at one time.

This is applicable to all pages having table region/ advance table region and you want to restrict the user from enetring duplicate row. Here please keep in mind that I am not comparing the VO rows with DB rows. 

Application Module Method

        public void checkDuplicateRow() {

        XxTestToolsVOImpl lo_tools = getXxTestToolsVO(); //Table View Object
        XxTestToolsVOImpl clonevo = 
            (XxTestToolsVOImpl)this.findViewObject("XxTestToolsVO2"); //Dummy VO for check duplicity 
        if (clonevo == null) {
   //Create Dummy VO
            clonevo = 
                    (XxTestToolsVOImpl)this.createViewObject("XxTestToolsVO2", 
                                                             "xxadat.oracle.apps.ahl.tools.server.XxAdatToolsVO");
        }

        clonevo.clearCache();
        Row[] sourceVORows = 
            lo_tools.getFilteredRows("SelectFlag", "Y"); //Picking only those row where the Checkbox is checked
        if (!(clonevo.isPreparedForExecution())) {
            clonevo.executeQuery();
        }
  
  //Create ClonveVO with SourceVO rows
        for (Row row: sourceVORows) {
            Row cloneRow = clonevo.createRow();
            cloneRow = row;
            clonevo.insertRowAtRangeIndex(clonevo.getRangeIndexOf(clonevo.last()) + 
                                          1, cloneRow);//Inserting row one after another
        }
        int i = 0;
        int j;
        {
   for (Row row: sourceVORows) {
                j = 0;
                for (clonevo.first(); j < clonevo.getFetchedRowCount(); 
                     clonevo.next()) {
                    if (i == j) {
                        j++;
                        continue; //Ignore the row where index is same.
                    }
// This loop compare 1st row with all the rows except 1st then second row will   all the rows except 2nd and so on.
        if ((row.getAttribute("SerialNumber") +"").equals((clonevo.getCurrentRow().getAttribute("SerialNumber"))+"") && (row.getAttribute("InventoryItemId") + "").equals((clonevo.getCurrentRow().getAttribute("InventoryItemId"))+"") && (row.getAttribute("LotNumber") + "").equals((clonevo.getCurrentRow().getAttribute("LotNumber")) +  "") && (row.getAttribute("SubinventoryCode") +  "").equals((clonevo.getCurrentRow().getAttribute("SubinventoryCode")) + "") &&  (row.getAttribute("Locator") + "").equals((clonevo.getCurrentRow().getAttribute("Locator")) + ""))
                {
                  clonevo.remove(); //If the row is duplicate remove the dummy VO
                  throw new OAException("Row Number " + (i + 1) + " and " + (j + 1) + " is duplicate." + " Please select only unique Tools to issue");
                    }
                      j++;
                }
                i++;
            }
            lo_tools.first();
            clonevo.remove();

        }
    }
Thanks, --Anil

Sunday, July 20, 2014

Setting WHO columns in OAF & setting date in custom table

import java.text.SimpleDateFormat;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;

Application Module Code

    public void SettingWhoColumns(String CStringDate) {
        java.sql.Date ConvStringDate = null;
        try {
            Connection conn = getOADBTransaction().getJdbcConnection();

            String Query = 
                "insert into XX_INSERT_WHO_COLUMNS  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) values (:1, :2, :3, :4 ,:5)";
            PreparedStatement stmt = conn.prepareStatement(Query);
            //Creation Date
            stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            stmt.setInt(2, getOADBTransaction().getUserId()); // Created by
            //Last update date 
            stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            stmt.setInt(4, getOADBTransaction().getUserId()); // Last updated by
            stmt.setInt(5,getOADBTransaction().getLoginId()); // Last updated login
            //Who columns setting end here.....

            // Getting Date as a String, Converting it to Date format and setting it
            if (CStringDate != null & !("".equalsIgnoreCase(CStringDate))) {
                SimpleDateFormat dtformatter = 
                    new SimpleDateFormat("dd-MMM-yyyy");
                ConvStringDate = 
                        new java.sql.Date(dtformatter.parse(CStringDate).getTime());
                dtformatter.format(ConvStringDate);
            }
            stmt.setDate(6, ConvStringDate);
        } catch (Exception e) {
            throw new OAException("Exception" + e);
        }
    }

Thanks,
--Anil