Dynamic View Object through Personalization | R12.2 New Feature

               Create View Object through Personalization

This post will help you to understand on how we can create a view object with the help of OAF personalization.

1. Set 'Personalize Self-Service Defn' & 'FND: Personalization Region Link Enabled' Profile options so that you can get the personalization link.

2. In R12.2 we get the personalize link through the Setting icon as seen below.

3. Then navigate to any oaf page and click on 'Personalize Page' and then click on 'Model Personalization'

 4. Click on create button to create a Dynamic View Object. We can only create a dynamic read-only view instance that encapsulates a database query, to the root application module of the current OA Framework-based page.

Once you create a new dynamic view instance, you can also enable, disable, or delete the view instance, or delete the model personalization from the root application module.

5. Now specify a name for the view instance and enter the SQL query for the view instance.

 6. Select Apply to save the changes.

 7.  Then I have created a Advance Table and three columns through personalization only to see the data which I have defined in above view object. Columns are - User Name,

 8.  Now as per below screenshots you can see that a view only table region get added to existing OAF page and also it is showing the data.

Thanks,

--Anil

Integrate XML Publisher Report with OAF

In this example we are going to integrate a simple XML publisher report with OAF. The business scenario is something like that we have a OAF page and on click of "Print" button we want to call a XML publisher report.

Steps
1. Create a OAF page with table region and print button to call a XML publisher report.

2. Designed XML report template. here I have not mentioned steps to register the XML report in Oracle apps.

Controller Code
Here we are handling the print button action and calling the XML report.

    public void processFormRequest(OAPageContext pageContext, 
                                   OAWebBean webBean) {
        super.processFormRequest(pageContext, webBean);
        if (pageContext.getParameter("Print") != null) {

            System.out.println("Print Button clicked");

            printReport(pageContext, webBean, "pdf");

        }

    public void printReport(OAPageContext pageContext, OAWebBean webBean, 
                            String outputType) {

        OAApplicationModule lo_am = pageContext.getApplicationModule(webBean);
        
        String ls_extensionType = "pdf"; //Setting the output extension type
        byte l_outputType = 0;
        l_outputType = TemplateHelper.OUTPUT_TYPE_PDF;


        DataObject lo_sessionDictionary = 
            pageContext.getNamedDataObject("_SessionParameters");
        HttpServletResponse lo_response = 
            (HttpServletResponse)lo_sessionDictionary.selectValue(null, 
                                                                  "HttpServletResponse");
        try {
            ServletOutputStream lo_outputStream = 
                lo_response.getOutputStream();

            // setting the report name & output type
            String lo_contentDisposition = 
                "attachment;filename=OrderDetails." + ls_extensionType;
            lo_response.setHeader("Content-Disposition", 
                                  lo_contentDisposition);
            lo_response.setContentType("application/pdf");

            System.out.println("Calling XML generation method");

            XMLNode xmlNode = 
                (XMLNode)lo_am.invokeMethod("getOrderReportDetailsXML");
            // Calling getOrderReportDetailsXML method to get the XML for the VO query

            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

            xmlNode.print(outputStream);
            System.out.println("After calling XML generation method" + 
                               outputStream.toString());
            //Above sop will return the XML output which will help you in designing the template.
 
            ByteArrayInputStream lo_inputStream = 
                new ByteArrayInputStream(outputStream.toByteArray());
            ByteArrayOutputStream lo_outputFile = new ByteArrayOutputStream();
            
            // Pass the Template code and Application short name
            try {
                TemplateHelper.processTemplate(((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getAppsContext(), 
                                               "PA", "XX_ORDER_DT", 
                                               ((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getLanguage(), 
                                               ((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getCountry(), 
                                               lo_inputStream, l_outputType, 
                                               null, lo_outputFile);

            } catch (XDOException xe) {
                throw new OAException("Exception:" + xe.getMessage());

            }

            byte[] l_bytes = lo_outputFile.toByteArray();

            lo_response.setContentLength(l_bytes.length);
            lo_outputStream.write(l_bytes, 0, l_bytes.length);
            lo_outputStream.flush();
            lo_outputStream.close();
        } catch (Exception e) {
            lo_response.setContentType("text/html");
            throw new OAException(e.getMessage(), OAException.ERROR);
        }
        pageContext.setDocumentRendered(false);

    }

Application Module Code

 This method will return the XML output of the VO query attached to the page.

   public XMLNode getOrderReportDetailsXML() {
        //XMLNode lo_xmlNode;
       System.out.println("Inside the AM Method");
        OrderReportDetailsVOImpl vo = getOrderReportDetailsVO1();
            vo.executeQuery();
          XMLNode  lo_xmlNode = (XMLNode)vo.writeXML(-1, 0L);

//OL Stands for XMLInterface.XML_OPT_ALL_ROWS which means to includes all rows in the view object's row set in the XML.

        return lo_xmlNode;
    }

Report Output

Thanks,
 --Anil

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