In the world of E Business Application, often there is a need to export the data from Applications and re-import them to some other external application.  This import/export business requires a file to be exported in format (say CSV) so that it can be imported easily to other system.  OA Framework provides a nice feature, Export Button,  which allows to export the rows displayed on the OAF Page to a CSV format.  However this functionality is limited in its own way and it can export only those View object attributes which are currently rendered or used on the OAF page. Consider the following scenario where Export button does not help much:

The solution for this problem can be summarized as below:

1)  Create a data template (or may be reuse if one already exists) which can produce XML data for the complete Purchase Order export

2)  Create an EText Delimiter based template to generate CSV (or any delimiter based output as desired)

3)  Register Data Definition and Template (as E-Text) under XML Publisher Administrator responsibility

4)  Invoke the above XML Publisher EText report from OA Framework - using BIPublisherUtility (This is a custom utility developed by me to easily execute XML Publisher reports from OAF)

 

The real challenge in the above solution approach is that how to pass list of all the purchase orders that user has searched in the OAF Page to XML Publisher data template so that data template query can generate XML output for only the searched records?  Because user can search using n-different combinations and we want to export only those purchase orders which are fetched by user query.  ( Does data template support array type of parameters? ...  a million dollar question!!).

 

Now lets look at the complete solution:

 

Step 1:  Create data template

 

I will not go into details of how to code a data template.  We will discuss few important things which really matters for this solution.

 

Following is the sample data template.  The real world data template will have many other queries to fetch lines, shipments, distributions etc.

The idea here is to use a pl/sql table (highlighted in blue)  as normal table to filter the numbers of records to be fetched.  The PL/SQL table will be populated by passing an array of po_header_id from OA Framework controller discussed later in OA Framework section below.  

 

NOTE:  The below mentioned approach of passing a set of IDs as an array and accessing them in data template query works only when OAF and XML Publisher are in same session.  The approach will not work if OAF populates the pl/sql collection and submits the XML Publisher concurrent request as concurrent requests are executed in their own session.  In other way, the OAF should execute the XML Publisher based report online (using APIs) and should not rely on concurrent program/request framework

 

Data template

 

<?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="PO_EXPORT" description="List of Purchase Orders" defaultPackage="PO_EXP_PKG"  version="1.0">

    <parameters> 

       <parameter name="p_po_header_id" dataType="character" 

        defaultValue="1010"/>

    </parameters>

    <dataQuery>

       <sqlStatement name="Q1">

          <![CDATA[SELECT po.PO_HEADER_ID,po.SEGMENT1, po.COMMENTS, po.APPROVED_FLAG, po.VENDOR_ID, po.VENDOR_SITE_ID /* other PO related columns */

   from PO_HEADERS_ALL  PO

                         (select COLUMN_VALUE as po_header_id

from TABLE(CAST(PO_EXP_PKG.GET_HEADER_IDS AS VARCHAR2_TABLE_100))

)                  PARAMETERS

))

            ]]>

       </sqlStatement>

    </dataQuery>

<dataStructure>

         <group name="G_PURCHASE_ORDER" source="Q1">

<element name="PO_HEADER_ID" value="PO_HEADER_ID"/>

<element name="SEGMENT1"   value="SEGMENT1"/>

<element name="COMMENTS"   value="COMMENTS"/>

<element name="VENDOR_ID"   value="VENDOR_ID"/>

<element name="VENDOR_SITE_ID"   value="VENDOR_SITE_ID"/>

</group>        

</dataStructure>

</dataTemplate>

 

 

Here is the code for default PL/SQL Package used in data template.  

 

Package Specification

 

create or replace package PO_EXP_PKG as 

  p_po_header_id  varchar2 (15);

  

  /*  This is a package level global variable which will store PO_HEADER_IDs */

  g_po_hdr_ids varchar2_table_100 := varchar2_table_100();

  

  /*  This is the setter procedure to set the value of global variable 

      This procedure will be invoked from OA Framework controller with 

      array of PO_HEADERS_ID passed as an argument

  */

  procedure set_po_header_ids (p_po_header_ids  IN     varchar2_table_100 );

  

  /*  This function will return global variable

      It is invoked from data template query 

   */

  function get_header_ids return varchar2_table_100;

  

end PO_EXP_PKG;

Package Body

create or replace PACKAGE BODY po_exp_pkg AS

 

  procedure set_po_header_ids (p_po_header_ids  IN     varchar2_table_100 ) AS

  BEGIN

     g_po_hdr_ids := p_po_header_ids;     

    

  END set_po_header_ids;

  

  function get_header_ids return varchar2_table_100 AS

  BEGIN

      RETURN g_po_hdr_ids;

  END get_header_ids;

 

END po_exp_pkg;

 

Step 2:  Create ETEXT template

 

Here is the sample ETEXT template for generating the Delimited output.  The delimiter can also be a data template parameter instead of hard code comma(,) symbol to make it user defined.

 

Step 3:  Register Data Definition and Template (as E-Text) under XML Publisher Administrator responsibility

 

 

image03

Step 4: Invoke the above XML Publisher EText report from OA Framework - using BIPublisherUtility 

Following is the controller code to invoke the XML Publisher report.  BiPublisherUtility is a custom utility which can be used for executing XML Publisher reports from Oracle Applications.  

Source code for BiPublisherHelper is available here

/*===========================================================================+

 |   Controller to invoke XML Publisher report                               |

 |                                                                           |

 +===========================================================================+

 |  HISTORY                                                                  |

 +===========================================================================*/

package xxcomp.oracle.apps.pos.orders.webui;

 

import com.sun.java.util.collections.Hashtable;

 

import java.io.IOException;

 

import java.sql.SQLException;

 

import javax.servlet.http.HttpServletResponse;

 

 

import oracle.apps.fnd.framework.OAApplicationModule;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.OAViewObject;

import oracle.apps.fnd.framework.webui.OAPageContext;

import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.beans.nav.OAButtonBean;

import oracle.apps.fnd.framework.webui.beans.nav.OAPageButtonBarBean;

import oracle.apps.pos.orders.webui.PosVpoMainCO;

 

import oracle.apps.xdo.XDOException;

 

import oracle.jdbc.OracleCallableStatement;

 

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

 

// this is a custom utility class

import xxcomp.oracle.apps.xxprj.common.utils.BiPublisherHelper;

 

 

/**

 * Controller for Exporting Purchase Orders on iSupplier View Purchase Order page

 */

public class XXExportPoDetailsCO extends PosVpoMainCO // this extension of page level controller

 

{

    public static final String EXPORT_DATA_DEFINITION =  "POEXPTEST"; //Data Definition code

    public static final String EXPORT_CSV_TEMPLATE = "POEXPDELIM"; //Template Short Code

 

 

  public void processRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Begin processRequest", 2);

 

    // Find the application module

    OAApplicationModule am =pageContext.getApplicationModule(webBean);

 

    // Find the view object which is the base for displaying the resutls

    // and which contains the PO_HEADER_ID that we want to pass to XMLP

    // data template

    OAViewObject vo = (OAViewObject)am.findViewObject("PosPOSummaryVO");

 

    int resetMaxFetchSize = vo.getMaxFetchSize();

      // We are interested to export all the  rows from VO. -1 will ensure

      // that we fetch every single row from ViewObject and by pass the VO_MAX_FETCH_SIZE

      // profile value

 

    vo.setMaxFetchSize(-1);

    super.processRequest(pageContext, webBean);

 

    /* Find the button XXExportDetailsBtn created using personalization */

    OAPageButtonBarBean btnBar = (OAPageButtonBarBean)webBean.findChildRecursive("PageBtnRN");

    OAButtonBean expDtlBtn = (OAButtonBean)btnBar.findChildRecursive("XXExportDetailsBtn");

 

    if (expDtlBtn == null) {

        pageContext.putDialogMessage

                (new OAException ("Unable to find personalizatoin ID: XXExportDetailsBtn .  Export Details button cannot be rendered", OAException.WARNING));

    }

    else {

       // Set the fireAction so that when user click on the button we get an event in processFormRequest

          expDtlBtn.setFireActionForSubmit("XXExportPoDetailsEvent",null,null,false);

    }

    if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: End of processRequest", 2);

  }

 

 /**

   * 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)  {

 

      if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Begin of processFormRequest", 2);

 

      super.processFormRequest(pageContext, webBean);

 

      if ("XXExportPoDetailsEvent".equals(pageContext.getParameter(EVENT_PARAM)))

      // User clicked on Export Details button.  This is the same fireAction event

      // that we have set on button above in processRequest method.

      {

          //The e-Text template

          String strLayoutTemplate = EXPORT_CSV_TEMPLATE;

          //Assign the file name for multiple PO Export

          String strFileName = "PO_Details_Export.csv";

          // Set output type to E-Text as we want a plain text output from delimiter based template

          byte outputType = BiPublisherHelper.OUTPUT_TYPE_ETEXT;

 

          String[] lPoHdrIds = null;

          OAApplicationModule am = pageContext.getApplicationModule(webBean);

 

          // Find the view object used for displaying the results

          OAViewObject vo = (OAViewObject)am.findViewObject("PosPOSummaryVO");

 

          if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Populating the array with Ids", 2);

 

          // View object is not yet executed, may be user did not perform a search

          if (!vo.isExecuted()) return;

 

          //View object is executed, Reset the row currency to before the first row

          vo.reset();

 

          // Create a string array to hold all po_header_id

          lPoHdrIds = vo.getRowCount()>0?new String[vo.getRowCount()] : null;

 

          // Populate array with PO_HEADER_ID from instance which is recently searched

          for (int i=0; vo.hasNext(); i++) {

              lPoHdrIds[i] =  vo.next().getAttribute("PO_HEADER_ID").toString();

          }

          String strCall = "BEGIN PO_EXP_PKG.set_po_header_ids  (?); END;";

          OracleCallableStatement oca = (OracleCallableStatement) am.getOADBTransaction().createCallableStatement(strCall, am.getOADBTransaction().DEFAULT);

          ArrayDescriptor ad;

          ARRAY lPoHdrIdsArray=null;

 

          //If we do not have list of header ids to process we can simply return

          if (lPoHdrIds == null ) {

            if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Array to pass is null, lPoHdrIds="+ lPoHdrIds.toString(), 2);

            throw new OAException("Failed to get list of Document Ids to be exported", OAException.INFORMATION);

          }

 

          try {

              /*

               * Create the array descriptor for collection VARCHAR2_TABLE_100

               * Using the array descriptor, we can create an SQL Array object

               * which can be passed to pl/sql

               */

              ad = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE_100", am.getOADBTransaction().getJdbcConnection());

              lPoHdrIdsArray = new ARRAY (ad, am.getOADBTransaction().getJdbcConnection(),lPoHdrIds);

              if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Array descriptor is defined and Array is ready to pass", 2);

          }

          catch (SQLException e) {

            if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Exception:" + e.getMessage() , 2);

            throw new OAException ("Exception: Failed to create array descriptor: "+e.getMessage());

          }

 

         /*

          * Invoke the procedure to set the global pl/sql collection variable

          */

          boolean bInvokeXMLP = false;

          try {

 

            if (lPoHdrIdsArray==null) return; //Array does not contain anything

 

            oca.setARRAY(1, lPoHdrIdsArray);

            oca.execute();

 

            if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Procedure executed:" + strCall , 2);

 

            //We have successfully passed array to procedure

            bInvokeXMLP = true;

 

          }

          catch (SQLException e) {

            if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Exception:" + e.getMessage(), 2);

            throw new OAException ("Exception:Failed to invoke procedure to pass array"+e.getMessage());

          }

 

          //If flag to invoke the XMLP report is not set we can simply return

          if (!bInvokeXMLP) return;

 

          /*

           * Invoke xml publisher data template which will read the data from the collection table

           * initialized above and generate the final output

           */

          if (pageContext.isLoggingEnabled(2)) pageContext.writeDiagnostics(this, "XX: Invoking the Bi Report App=XXPRJ"+"DataDef="+EXPORT_DATA_DEFINITION

                                                                                                            +", Layout="+strLayoutTemplate+", file="+strFileName, 2);

          Hashtable prms = new Hashtable(1); //Create a hashtable for data template parameters

          prms.put("P_ORG_ID", 204); //Set parameter values

          try {

              String strApp = "XXPRJ";     //Application name under which the data template and layout are registered

 

              // Get an instance of the BiPublisherHelper utility to run the report

              BiPublisherHelper biHelper = BiPublisherHelper.createInstance(pageContext,am);

              if (prms != null) {

                  // Set the data template parameters if any

                  biHelper.setDataTemplateParams(prms);

              }

              // Run the xml publisher report

              biHelper.runBiReport(strApp,EXPORT_DATA_DEFINITION,strApp,EXPORT_CSV_TEMPLATE,outputType);

              // Show the report output as file download dialog box

              biHelper.showOutputAsDownload("PoDetialsExport.csv");

 

          }

          catch (SQLException e) {

             System.out.println("::::ERROR:::: Failed to find data template "+EXPORT_DATA_DEFINITION);

             e.printStackTrace();

             pageContext.putDialogMessage(new OAException ("Exception:"+e.getMessage(),OAException.ERROR ));

          }

          catch (XDOException xe) {

             System.out.println("::::ERROR:::: Failed to execute data template "+EXPORT_DATA_DEFINITION);

             xe.printStackTrace();

             pageContext.putDialogMessage(new OAException ("Exception:"+xe.getMessage(),OAException.ERROR ));

          }

          catch (IOException ioe) {

             System.out.println("::::ERROR:::: Failed to execute layout template "+EXPORT_CSV_TEMPLATE);

             ioe.printStackTrace();

             pageContext.putDialogMessage(new OAException ("Exception:"+ioe.getMessage(),OAException.ERROR ));

          }

          catch(Exception e)

          {

             pageContext.putDialogMessage( new OAException(e.getMessage(), OAException.ERROR));

          }

 

        }

 

      }

}

Add comment