Download BLOB File from Database

  Below code used to download BLOB File from database.

 Write Below Code Under Event ..


    String MimeType=null,FileName=null;
    String Rowref=pageContext.getParameter(EVENT_SOURCE_ROW_REFERENCE);
    OtherReportRequestVORowImpl row=(OtherReportRequestVORowImpl)am.findRowByRef(Rowref);
    try {
       String Query=" SELECT 'text/plain' MIME_TYPE, xxebex.xxebex_public.CLOB_TO_BLOB(REQUEST_LOG), 'InfoRequest_'||REQUEST_ID||'_ProcessLog.html' FILE_NAME"+
    " FROM xxebex.ebex_info_requests  WHERE request_id ='"+row.getRequestId()+"'";
    PreparedStatement cs=(PreparedStatement)am.getOADBTransaction().getJdbcConnection().prepareStatement(Query);
    ResultSet rs=cs.executeQuery();
    while(rs.next())
    {
        java.sql.Blob blob;
        blob = rs.getBlob(2);
        MimeType=rs.getString(1);
        FileName=rs.getString(3);
        int iLength = (int)(blob.length());
        System.out.println("Length:"+iLength);
        response.setHeader("Content-Disposition", "attachment; filename=\""+FileName+"\"");
        response.setContentType(MimeType);
        response.setContentLength(iLength);
        ServletOutputStream op = response.getOutputStream();
        op.write(blob.getBytes(1, iLength));
        op.flush();
        op.close();

    }
   
       
    }
    catch(Exception e) {
        System.out.println(e.getMessage());
    }

Export Data from Excel file to DataBase tables

Step 1: Create FileUpload Item  in Page

            Id:FileUploadItem

            Prompt:FileUpload

            DataType:Blob

Step 2:Create  a Submit Button

           Id:Go

          Prompt:Go

Step 3:Create a submit Button

           Id:Update

           Prompt:Update

           Event:update

Step 4:Write Following Code in controller i.e, attached to a page.

 import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStreamReader;

import oracle.apps.fnd.common.VersionInfo;

import oracle.apps.fnd.framework.OAApplicationModule;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.server.OAViewObjectImpl;

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

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

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

import oracle.cabo.ui.data.DataObject;

import oracle.jbo.Row;

import oracle.jbo.domain.BlobDomain;

/**

 * Controller for ...

 */

public class FileUploadCO 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);

  }

  /**

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

    OAApplicationModule am=(OAApplicationModule)pageContext.getApplicationModule(webBean);

    OAViewObjectImpl vo = (OAViewObjectImpl) am.findViewObject("DemoVO1");

    if (pageContext.getParameter("Go")!=null)

    {

    DataObject fileUploadData =(DataObject)pageContext.getNamedDataObject("FileUploadItem");

    String fileName = null;

    String contentType = null;

    Long fileSize = null;

    BlobDomain uploadedByteStream = null;

    BufferedReader in = null;

     

    try

    {

    fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");

    contentType =(String)fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

    uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);

    in = new BufferedReader(new InputStreamReader(uploadedByteStream.getBinaryStream()));   

     fileSize = new Long(uploadedByteStream.getLength()); 

    System.out.println("fileSize"+fileSize);

    }

    catch(NullPointerException ex)

    {

    throw new OAException("Please Select a File to Upload", OAException.ERROR);

    }

    try{     

    //Open the CSV file for reading  

    String lineReader="";

    long t =0;

     

     

    String[] linetext;

     

     

    while (((lineReader = in.readLine()) !=null) ){

     

     

    //Split the deliminated data and

    if (lineReader.trim().length()>0)

    {

    System.out.println("lineReader"+lineReader.length());

    linetext = lineReader.split(",");

    t++;  

    if(t>1)
{

    if (!vo.isPreparedForExecution()) {

    vo.setMaxFetchSize(0);

    vo.executeQuery();

    }

    Row row = vo.createRow();

    System.out.println("Column1->"+linetext[0]);

    row.setAttribute("VendorName", linetext[0]);

    row.setAttribute("Segment1",linetext[1]);

    row.setAttribute("VendorId",linetext[2]);

    

    vo.last();

    vo.next();

    vo.insertRow(row);
}

    }

     }

    }

     

     

    catch (IOException e)

    {

     e.printStackTrace();

    }

    }

    if ("update".equals(pageContext.getParameter(EVENT_PARAM))) {

    am.getOADBTransaction().commit();

    throw new OAException("Uploaded SuccessFully",OAException.CONFIRMATION);

     

     

    }

    }

    }

    

            

Export Selected Rows into Excel File

1)Create ViewObject
 eg: EmpVO
Query:Select empno,ename,sal from scott.emp

2)Create Table by Region Using Wizard or manually with multiple Selection Option.
3)Create Transient attribute in EmpVO for Multiple Selection Option.i.e,xxselect type:String.
4)Create button  or Submit Button any where depend on The Requirement .
   Id:ExportBtn
  Prompt:Export
  Event:Export
5)Create a Controller under PageLayoutRN.

Write Below Code

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


  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
       EmpVOImpl vo=(EmpVOImpl )pageContext.getApplicationModule(webBean).findViewObject("EmpVO1");
    vo.executeQuery();
  }

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    if("Export".equals(pageContext.getParameter(EVENT_PARAM)))
    {
    EmpVOImpl vo = (EmpVOImpl)pageContext.getApplicationModule(webBean).findViewObject("EmpVO1");
      EmpVORowImpl row=(EmpVORowImpl)vo.first();
      Row[] row1=vo.getFilteredRows("xxselect","Y");
    try
    {

    HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
    response.setContentType("application/txt");
    response.setHeader("Content-Disposition","attachment; filename=" + "Employee Trouble Call Report" + ".csv");
    PrintWriter pw = null;
    pw = response.getWriter();

    //Writing the headers
    pw.write("Emploee Number");
    pw.write(",");
    pw.write("Employee Name");
    pw.write(",");
    pw.write("Salary");  
    pw.write("\n");
    // getting table data
    Row row2=null;
    System.out.println(row1.length);
       for(int i=0;i<row1.length;i++)//Read Selected Rows
       {
       row2=row1[i];
       String dept_name= row2.getAttribute("Empno").toString();
        String emp_name=row2.getAttribute("Ename").toString();
        String Salary=row2.getAttribute("Sal").toString();
         if(dept_name !=null) // for null handling in the code
         {
         pw.write(dept_name);
         }
         else
         {
         pw.write(" ");
         }
//      emp_name = emp_name.replaceAll(",",".");
           pw.write(",");
           if(emp_name!=null)
           {
          pw.write(emp_name);
           }
           else
           {
             pw.write("");
           }
         pw.write(",");//next cell
         if(Salary!=null)
         {
         pw.write(Salary);
         }
         else
         {
           pw.write("");
         }
           pw.write("\n");//next line
       }  
    pw.write(" ");
    pageContext.setDocumentRendered(false); //mandatory
    pw.flush(); //exporting data
    pw.close();

    }
    catch(Exception e)
    {
    e.printStackTrace();
    }
    }
  }
}

Create Items Programetically

If You want to Create Any Item Or Region Dynamically Code Has to be written In Only Process request.These Items Doesn't Appear in the Structure Pane.

    OAStackLayoutBean oastacklayoutbean=(OAStackLayoutBean)webBean.findIndexedChildRecursive("region2"); // Initialization of StackLayout

    OAMessageTextInputBean oamessagetextinputbean=(OAMessageTextInputBean)createWebBean(pageContext,OAWebBeanConstants.MESSAGE_TEXT_INPUT_BEAN,null,"XX");
  
/*Creation MessagetextInputBean And Setting Prompt */

oamessagetextinputbean.setPrompt("YY");
    oastacklayoutbean.addIndexedChild(oamessagetextinputbean);// adding item to the StackLayout
    OAMessageCheckBoxBean oamessagecheckboxbean=(OAMessageCheckBoxBean)createWebBean(pageContext,OAWebBeanConstants.MESSAGE_CHECKBOX_BEAN,null,"YY");
   
    oastacklayoutbean.addIndexedChild(oamessagecheckboxbean);

Color The Item Based On Employee Position in Advanced table

Step 1:
Create EO Based VO i.e,EMPVO
SELECT EMPEo.EMPNO,
       EMPEo.ENAME,
       EMPEo.JOB,
       EMPEo.MGR,
       EMPEo.HIREDATE,
       EMPEo.SAL,
       EMPEo.COMM,
       EMPEo.DEPTNO,
       EMPEo.CREATION_DATE,
       EMPEo.CREATED_BY,
       EMPEo.LAST_UPDATE_DATE,
       EMPEo.LAST_UPDATE_LOGIN,
       EMPEo.LAST_UPDATED_BY,
       EMPEo.ROWID,DECODE (EMPEo.JOB
,'MANAGER', '1','2'
) color
FROM EMP EMPEo

Step 2:Under Custom.xss

add Follwing code
<style name="For_Bold">
<property name="font-weight">bold</property>
<property name="font-size">10pt</property>
</style>

<style selector=".1">
<includeStyle name="DefaultFamily"/>
<property name="font-size">11pt</property>
<property name="font-weight">Bolder</property>
<property name="color">#FFFF00</property>
<property name="text-indent">3px</property>
<property name="background-color">#FF0000</property>
</style>

<style selector=".2">
<includeStyle name="DefaultFontFamily"/>
<property name="font-size">11pt</property>
<property name="font-weight">Bolder</property>
<property name="color">#FFFF00</property>
<property name="text-indent">3px</property>
<property name="background-color">#003399</property>
</style>


the path of the custom.xss is in $OA_HTML/cabo/Styles/custom.xss


Under Process Request:
    OAApplicationModule am=( OAApplicationModule)       pageContext.getApplicationModule(webBean);
    EMPVOImpl vo=am.getEMPVO1();
    vo.executeQuery();
    OAAdvancedTableBean table = (OAAdvancedTableBean)webBean.
    findIndexedChildRecursive("region2");
    OAColumnBean ejobcol = (OAColumnBean)webBean.
    findIndexedChildRecursive("column1");
    OAMessageTextInputBean job = (OAMessageTextInputBean)ejobcol.
    findIndexedChildRecursive("item1");--for which item you wanted color
    OADataBoundValueViewObject cssjob = new OADataBoundValueViewObject(job,"Color");
    job.setAttributeValue(oracle.cabo.ui.UIConstants.STYLE_CLASS_ATTR, cssjob);


O/P will be like:

Copying Data From One Table to Another Table in OAF and ADF Using RowsetIterator

 
Requirement:Copy FirstName,LastName,EmployeeId  to Sample Table(Custom)

Step1:Create Sample Table  wihch Having Fileds Like FirstName and Last Name and EmployeeId
     Step 2:Create New View Object For this Sample Table.

Add Below Code in AM Impl

public void Rowcount()
    {
        ViewObjectImpl vo = getEmployeesVO1();
        RowSetIterator rs = vo.createRowSetIterator("xxx");
        while (rs.hasNext())
        {
        
           int rowcount=vo.getRowCount();
           Row row=rs.next();          
           ViewObjectImpl newvo=getSampleVO1();
            Row row1=newvo.createRow();           
            newvo.insertRow(row1);
            row1.setAttribute("EmployeeId", row.getAttribute("EmployeeId"));
            row1.setAttribute("FirstName", row.getAttribute("FirstName"));
            row1.setAttribute("LastName", row.getAttribute("LastName"));

          getDBTransaction().commit();  
              }
    }

Important Queries Using in OAF

To get Application Short Name and Responsibility Key



SELECT fr.responsibility_key,fa.application_short_name
  FROM fnd_responsibility fr, fnd_responsibility_tl frt, fnd_application fa
 WHERE fr.responsibility_id = frt.responsibility_id
   AND fa.application_id = fr.application_id
   AND frt.responsibility_name = 'Purchasing, Vision Operations (USA)'

Bundled Exception

In Process Form Request when Save Button Is Clicked   

 if(pageContext.getParameter("save")!=null)
    {
    Row row=vo.first();
    ArrayList errMsg=new ArrayList();
    try
    {
      int sal=Integer.parseInt(row.getAttribute("Sal").toString());
      System.out.println(sal);
      int comm=Integer.parseInt(row.getAttribute("Comm").toString());
      System.out.println(comm);
      if(sal>100)
             {
             System.out.println(sal);
              errMsg.add(new OAException("Salary Less than 100"));
             }
             if(comm>100)
             {
             System.out.println(comm);
              errMsg.add(new OAException("Comm Less tahn 100"));
             }
           
      System.out.println(errMsg);      
         
           
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
   
       
   
      am.getOADBTransaction().commit();
      errMsg.add( new OAException("Rebate Created Successfully"));
     
      System.out.println(errMsg);
      OAException.raiseBundledOAException(errMsg);
}

Output Like

Entity Level Validation for StartDate and End Date comparison

Entity Level Validation for StartDate and End Date comparison


In EOImpl.java


  protected void validateEntity()
  {
    super.validateEntity();
    Date sdate=getStartdate();
    Date Edate=getEnddate();
    if(sdate.dateValue().getTime()>Edate.dateValue().getTime())
    {
      throw new OAAttrValException(OAException.TYP_ENTITY_OBJECT,
      getEntityDef().getFullName(), // EO name
      getPrimaryKey(), // EO PK
      "Enddate", // Attribute Name
      Edate, // Attribute Value
      "AK", // Message product short name
      "FWK_TBX_T_END_DATE_PAST"); // Message name

    }
  }

Create ViewObject(VO) Dynamically

In ProcessRequest
import oracle.apps.fnd.framework.server.OAViewDef
import oracle.apps.fnd.framework.OAViewObject 

    AdminRegAMImpl am=(AdminRegAMImpl)pageContext.getApplicationModule(webBean);

    if(am!=null)

    {

        OAViewDef viewdef=(OAViewDef)am.getOADBTransaction().createViewDef();

    viewdef.setSql("select ename from scott.emp");

         OAViewObject vo=(OAViewObject)am.createViewObject("SampleVO1",viewdef);

//SampleVO1 is ViewObject Name

            

    }

   

Set Active Subtab in SubTabLayout

In Process Request

    OASubTabLayoutBean subTabLayout = (OASubTabLayoutBean)webBean.findChildRecursive("region2");/*Region2 subtablayoutname*/

    subTabLayout.setSelectedIndex(pageContext,"DeptVO1");/*DeptVO1 is Region Name Under subtablayout*/

    subTabLayout.setUnvalidated(Boolean.TRUE);

  

  

Make Page Readonly

import com.oaframewrok.toolkit.util.webBeanUtil

In Process Request:

After super.processFormRequest(pageContext,webBean)

WebBeanUtil.setViewOnlyRecursive(pageContext,webBean);

Download Above package from below location

http://sourceforge.net/projects/oaftoolkit/files/latest/download?source=files

PopUp Window Using PopupBean Programetically

In Process Request

OAPopupBean popupBean =(OAPopupBean)createWebBean(pageContext,POPUP_BEAN,null,"myPopup");
//Set the following properties on the pop-up:
popupBean.setID("myPopup");
popupBean.setUINodeName("myPopup");
String popupRegion= "/oracle/apps/fnd/framework/toolbox/labsolutions/webui/TestEmpDetailsRN" ;
popupBean.setRegion(popupRegion);
popupBean.setHeight("130");
popupBean.setWidth("320"); 
popupBean.setTitle("Test");
popupBean.setType(EMBEDDED_POPUP); /* Embedded type */

Alert Using JavaScript in OAF

    if(pageContext.getParameter("SubmitButton")!=null)
    {
      StringBuffer l_buffer = new StringBuffer();
      l_buffer.append("javascript:alert('hello')");
      pageContext.putJavaScriptFunction("SomeName",l_buffer.toString());
    }


Here SubmitButton is the id name of submitbutton

l_buffer is the variable name of StringBuffer