Uploading data form .csv file in OAF

Create a Page with a message file upload button
Create a button to process upload in my case a button with Id UploadBtn
Then use this code in the Process Form Request

      if (pageContext.getParameter("UploadBtn")!=null)
      {
      DataObject fileUploadData =pageContext.getNamedDataObject("UploadItem");
      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;
          System.out.println("inside the try1===>");
      while (((lineReader = in.readLine()) !=null) )
      {
      //Split the deliminated data and
      System.out.println("inside while==>");
      if(!lineReader.split(",")[0].equals("Dept"))
      {
      if (lineReader.trim().length()>0)
      {
      linetext = lineReader.split(",");
      System.out.println("LineText"+linetext);
      t++;
      if(!ReqIntVO.isPreparedForExecution())
      {
          ReqIntVO.setMaxFetchSize(-1);   
      }
      Row row=ReqIntVO.createRow();
      ReqIntVO.insertRow(row);
      row.setNewRowState(Row.STATUS_INITIALIZED);
     row.setAttribute("ReqIntId",pageContext.getApplicationModule(webBean)
     .getOADBTransaction().getSequenceValue("XXDPC_REQ_INT_ID_S"));
      row.setAttribute("UserName",pageContext.getUserName());
      if(!linetext[0].trim().equals("ENG"))
      {
      //row.setAttribute("DepartmentCode",linetext[0].trim());
      row.setAttribute("DepartmentCode",orgParams[0]);
      row.setAttribute("LineNum",linetext[1].trim());
      row.setAttribute("HdrDesc",linetext[2].trim());
      row.setAttribute("SourceDocNumber",linetext[3].trim());
      row.setAttribute("SourceDocLineNum",linetext[4].trim());
      row.setAttribute("Quantity",linetext[5].trim());
      row.setAttribute("Attribute1","01"); 
      row.setAttribute("Attribute2",linetext[6].trim());
      row.setAttribute("Attribute3",linetext[7].trim());
      row.setAttribute("Attribute4",linetext[8].trim());
      row.setAttribute("Attribute5",linetext[9].trim());
      row.setAttribute("Attribute6","00");
      row.setAttribute("Attribute7","0000");
      row.setAttribute("ProcessStatus","N");
      }
}
In this case am creating a Row in a VO and setting values to it so when later if we commit record will be created in data base

Message File Upload Bean

import oracle.apps.fnd.framework.webui.beans.message.OAMessageFileUploadBean;

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

code in PR for Defaulting the file name to Message File upload bean

PR Code:

OAMessageFileUploadBean attachmentBean = (OAMessageFileUploadBean)webBean.findChildRecursive("UploadItem");

OADataBoundValueViewObject attachmentNameBoundValue = new OADataBoundValueViewObject(attachmentBean, "FileName");

attachmentBean.setAttributeValue(DOWNLOAD_FILE_NAME, attachmentNameBoundValue);

where UploadItem is the Id of the MessaageFileUpload Bean.

PFR Code to get filename when MessageFileUploadBean is used in table Region

    while(vo.hasNext()) 

    {

    Row row=vo.next();

    String StrAttachId="AttachmentRN.UploadItem";

    i=i+1;

    StrAttachId=StrAttachId+i;

    DataObject fileUploadData = pageContext.getNamedDataObject(StrAttachId);

    if(fileUploadData != null)

    {

    strFileName= fileUploadData.selectValue(null,"UPLOAD_FILE_NAME").toString();

    row.setAttribute("FileName",strFileName);

    }

    }

Making a OAF page read only

Some times we may need to make a oaf page read only in situations like when it is pending for approval etc

for this there is an API; using which we can make all the items in the page read only

lets see how to use that

first import the following package from PO_TOP

import oracle.apps.po.common.webui.ClientUtil;

And call the API as shown below in the process request

ClientUtil.setViewOnlyRecursive(pageContext, webBean);

it will make all the items read only irrespective of any item type

Switcher in OAF

sometimes we may need to show different images for different rows based on some conditions

like in below image

let's see how to achieve it 

I want to show different images for a different records based on the status column if it is P one image and if its any thing else then another different Image

There is a region called switcher for this type of requirement in oaf

Right click on any table column select switcher 

Row right click on switcher and add new case

Now right click on case add new item and change the property of it to desired like MessageStyledText, Choice, Image etc. In my case am using a Image 

Add as many cases as many conditions you have

if you have 3 conditions add 3 cases. In my case i have 2 conditions so i am adding two cases

write a Sql query that returns different values for different conditions like below

SELECT emp_id,

       full_name,

       date_of_birth,

       DECODE (gender,  'M', 'Male',  'F', 'Female') Gender,

       email_id,

       status,

       DECODE (status, 'N', 'Enabled', 'Disabled') Update_Enabled,

       marital_status

  FROM emp_master

My Query returns the value Enabled if status is N and Disabled for all other values

I am using the same query in the vo mapped to table

Now change the Id of the Items under case as per the values returned by query.

so give different images for Enabled Item and Disabled Item like in below image

now give the view attribute to Switcher item

here i have give UpdateEnabled as my view attribute because this columns holds the logic to give different values for different row based on the flag

Now write a code to execute query of the vo attached to table on process request then you can get the required output.

Different Image for different rows using this you can handle Update on few rows and disable on few rows.

Calling Pl/Sql Package,Function,sql Statement in Oaf Code either in AM or in CO

First import the following packages

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

Calling a Function:
 public void callConcProg(String StrParamString)

 {

String StrRequestId=null;

String programcall=null;

programcall="begin :1 := FND_REQUEST.SUBMIT_REQUEST("+StrParamString+"); end;";

            CallableStatement cs =  getOADBTransaction().createCallableStatement(programcall,-1); 

            try

            { 

            cs.registerOutParameter(1,Types.VARCHAR); 

           //Need to define what type of output is that valid types are Number, Varchar ,Date etc.

            cs.execute(); 

            getOADBTransaction().commit();

           //After execute we can get the value of output value using the following syntax. 

           StrRequestId = cs.getString(1); 

            cs.close(); 

            } 

            catch (SQLException sqle) 

            { 

            throw new OAException("Error in Calling",sqle.toString());

            }

            if(!StrRequestId.equals("0")) 

             {

              errMsg1.add(new OAException(StrRequestId+" request submitted successfully",

              OAException.CONFIRMATION));

             }

        }

Calling a pl/Sql Procedure:

 public void excuteUpload() 

 throws SQLException {  

 OADBTransaction transaction = getOADBTransaction(); 

CallableStatement cs=transaction.createCallableStatement

("begin PACKAGE.UPLOAD_FILE("+transaction.getUserId()+") ; end;", -1);

                     try 

                     {

                       cs.execute();

                       cs.close();

                     }

                     catch(Exception e) 

                     {

                     throw new OAException("Error in Api PACKAGE.UPLOAD_FILE"+e.toString());    

                     }

                     }

Calling a Sql Query;

public void getDBName ( ) 

{

OADBTransaction transaction =this.getOADBTransaction();  

String Query="SELECT NAME FROM V$DATABASE";

try{

PreparedStatement stmt = transaction.createPreparedStatement(Query,0);

for(ResultSet resultset = stmt.executeQuery(); resultset.next();)  

{  

StrDBName        = resultset.getString(1);

//here getString(1) refers first column if you have many u need to use

//getString(2),getString(3) like that and if you had other data types you can use

// getDate(),getNumber() etc.

}  

}  

catch(Exception e) 

{

throw new OAException("Error in Query:"+Query+e,OAException.ERROR);

}

}

Syntax to get Uploaded File Name from Message File Upload Bean

First import DataObject from this package
import oracle.cabo.ui.data.DataObject;

Then make a reference to the MessageFileUpload bean
like below, where MessageFileUpload is the Id of my MessageFileUpload Item

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

so to get file name of uploaded file with extension.

using the following syntax in processFormRequest

String strFileName=null;      
if(fileUploadData != null)
        {
         strFileName= fileUploadData.selectValue(null,"UPLOAD_FILE_NAME").toString();
        }

Key Flex field kff in oaf pages

write a method like this in the Controller of the page where you have key flex field
and call that in the Process request
where Budget is the id of my kff item use your kff item id in that place
and the value of code combination id will store in ToAccountId
u cant give in ViewAttribute at page level only through coding only you can set
  
protected void prepareChargeAccountFlex(OAPageContext paramOAPageContext, OAWebBean paramOAWebBean)
      {
 OAKeyFlexBean HdrAcc = (OAKeyFlexBean)paramOAWebBean.findIndexedChildRecursive("Budget");
        HdrAcc.setCCIDAttributeName("ToAccountId");
        HdrAcc.setStructureCode("XX_KFF_NAME");
        HdrAcc.setDynamicInsertion(true);
      }