OAF page to DOWNLOAD the EXCEL template dynamically (template columns to be extracted from PLSQL procedure)!!
BELOW logic to be written in Page Controller on the press of "Download" button in OAF page.
It will download the .csv template of excel to the local system.
Headings in csv can be changed in plsql procedure written separately and called in OAF page.
Target:
here goes,
STEP 1: (Controller)
/*===============================================================================+ +===============================================================================+
| HISTORY |
| Release Date Author Remarks |
| ======= =========== ============= ================= |
| 1.1 04-Sep-20XX ********* Added Logic for ****** |
+===============================================================================*/
package rhmozf.oracle.apps.ozf.oa.ACDordupld.webui;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Serializable;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
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.domain.BlobDomain;
import oracle.jdbc.OracleTypes;
/**
* Controller for ...
*/
public class OrdUpldCO 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);
pageContext.writeDiagnostics(this,"HAR$ - Inside PFR",1);
String filename = null;
String fname = null;
String ext = null;
if (pageContext.getParameter("UploadBtn")!= null)
{
DataObject upload = (DataObject)pageContext.getNamedDataObject("FileUpload");
try{
filename = (String)upload.selectValue(null,"UPLOAD_FILE_NAME");
int mid = filename.lastIndexOf(".");
fname = filename.substring(0,mid);
ext = filename.substring(mid + 1, filename.length());
}
catch(NullPointerException e)
{
pageContext.writeDiagnostics(this,"HAR$ - Exception @ filename: " + e,1);
}
//checks if it is a csv file
if(!"csv".equals(ext))
{
throw new OAException("Please upload only .csv extension file !!",OAException.ERROR);
}
//
BlobDomain uploadstream = (BlobDomain)upload.selectValue(null, filename);
Serializable param[] = {uploadstream, filename};
Class classparam[] = {BlobDomain.class ,String.class};
pageContext.writeDiagnostics(this,"HAR$ - Before Invoke Method",1);
am.invokeMethod("DataUpload", param, classparam);
}
if(pageContext.getParameter("DownloadBtn")!=null)
{
pageContext.writeDiagnostics(this,"HAR$ - Inside DownloadBtn",1);
String lv_block = "BEGIN xxrhm_acd_ordupld_pkg.get_download_template_asclob(:1,:2); END;";
CallableStatement callstmt = (CallableStatement)am.getOADBTransaction().createCallableStatement(lv_block,1);
Clob clob = null;
pageContext.writeDiagnostics(this,"HAR$ - Before executing callable stmt",1);
//
try{
callstmt.setString(1, "ORDER_UPLOAD");
callstmt.registerOutParameter(2, OracleTypes.CLOB, 0);
callstmt.execute();
clob = callstmt.getClob(2);
callstmt.close();
pageContext.writeDiagnostics(this,"HAR$ - Before getting the path",1);
String uFileName = "ACDOrderUploadProcessFDD.csv";
String OAHTML_PATH = getOAHTML(pageContext, am); //getOAHTML is method below
pageContext.writeDiagnostics(this,"HAR$ - Path: "+OAHTML_PATH ,1);
File file = new File(OAHTML_PATH, uFileName); //getting together path with filename
try {
long wrote = 0;
BufferedWriter fwriter = new BufferedWriter(new FileWriter(file));
wrote = readFromClob(clob, fwriter); //readFromClob is a method below
fwriter.close();
}
catch(IOException e)
{
throw new OAException("HAR$ - In Catch of BufferedWriter statement " +
e.getMessage());
}
HttpServletResponse response =
(HttpServletResponse)pageContext.getRenderingContext().getServletResponse();
File fileToDownload = null;
try{
fileToDownload = new File(OAHTML_PATH + "/" + uFileName);
pageContext.writeDiagnostics(this,"HAR$ - Inside download event after file to download: " +
fileToDownload ,1);
}
catch(Exception e)
{
throw new OAException("HAR$ - Invalid File Path or file does not exist.");
}
response.setContentType("application/octet-stream");
response.setContentLength((int)fileToDownload.length());
response.setHeader("Content-Disposition",
"attachment; filename=" + uFileName + "");
response.resetBuffer();
InputStream in = null;
ServletOutputStream outs = null;
try{
outs = response.getOutputStream();
in = new BufferedInputStream(new FileInputStream(fileToDownload));
int length = 1024 * 32;
byte[] arrayOfByte = new byte[length];
int m = 0;
while ((m = in.read(arrayOfByte, 0, length)) > 0) {
outs.write(arrayOfByte, 0, m);
response.flushBuffer();
}
}
catch(IOException e)
{
throw new OAException("In Catch of outs.write(ch) statement " +
e.getMessage());
}
finally{
try {
outs.flush();
outs.close();
if (in != null)
{
in.close();
}
}
catch (Exception e) {
throw new OAException("In Catch of in.close() statement " +
e.getMessage());
}
}
}
catch(SQLException e) {
throw new OAException("HAR$ - Exception inside Callable Stmt." +
e.getMessage());
}
}
//sometimes Download button doesn't work directly so then go for EVENT option as shown below:
if (pageContext.getParameter("event") != null)
{
if (pageContext.getParameter("event").equalsIgnoreCase("Download"))
{
pageContext.writeDiagnostics(this,
"inside EVENT_PARAM the condition " +
pageContext.getParameter("EVENT_PARAM"),
2);
String sqlUpdatePrimaryKey =
"BEGIN xxrhm_acd_ordupld_pkg.get_download_template_asclob(:1,:2); END;";
CallableStatement callablestatement =
(CallableStatement)am.getOADBTransaction().createCallableStatement(sqlUpdatePrimaryKey,
1);
Clob clob = null;
try
{
callablestatement.setString(1, "ORDER_UPLOAD");
callablestatement.registerOutParameter(2, OracleTypes.CLOB, 0);
callablestatement.execute();
clob = callablestatement.getClob(2);
callablestatement.close();
pageContext.writeDiagnostics(this,
"inside ExportData event value of clob " +
clob, 2);
String uFileName = null;
uFileName = "ACDOrderUploadProcessFDD.csv";
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
"in ExportData action uFileName " +
uFileName, 2);
String OAHTML_PATH = getOAHTML(pageContext, am);
File file = new File(OAHTML_PATH, uFileName);
try
{
long wrote = 0;
BufferedWriter fwriter = new BufferedWriter(new FileWriter(file));
wrote = readFromClob(clob, fwriter);
fwriter.close();
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
"inside ExportData event after fwriter.close() ",
2);
} catch (IOException e)
{
throw new OAException("In Catch of BufferedWriter statement " +
e.getMessage());
}
HttpServletResponse response =
(HttpServletResponse)pageContext.getRenderingContext().getServletResponse();
File fileToDownload = null;
try
{
fileToDownload = new File(OAHTML_PATH + "/" + uFileName);
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
"inside ExportData event after fileToDownload " +
fileToDownload, 2);
} catch (Exception e)
{
throw new OAException("Invalid File Path or file does not exist.");
}
response.setContentType("application/octet-stream");
response.setContentLength((int)fileToDownload.length());
response.setHeader("Content-Disposition",
"attachment; filename=" + uFileName + "");
response.resetBuffer();
InputStream in = null;
ServletOutputStream outs = null;
try
{
outs = response.getOutputStream();
in = new BufferedInputStream(new FileInputStream(fileToDownload));
int length = 1024 * 32;
byte[] arrayOfByte = new byte[length];
int m = 0;
while ((m = in.read(arrayOfByte, 0, length)) > 0)
{
outs.write(arrayOfByte, 0, m);
response.flushBuffer();
}
} catch (IOException e)
{
throw new OAException("In Catch of outs.write(ch) statement " +
e.getMessage());
} finally
{
try
{
outs.flush();
outs.close();
if (in != null)
{
in.close();
}
} catch (Exception e)
{
throw new OAException("In Catch of in.close() statement " +
e.getMessage());
}
}
File fileToDelete = null;
try
{
fileToDelete = new File(OAHTML_PATH + "/" + uFileName);
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
"inside ExportData event to delete " +
fileToDownload, 2);
if (fileToDelete.delete())
{
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
" file deleted successfully ", 2);
} else
{
pageContext.writeDiagnostics(" JUMP PFR XXRHMTMMatrixQuickCalCO ",
" file not deleted successfully ",
2);
}
} catch (Exception e)
{
throw new OAException("Invalid File Path or file does not exist to delete.");
}
} catch (SQLException e)
{
throw new OAException("In Catch of prepared statement " +
e.getMessage());
}
}
}
}
//get path
public static String getOAHTML(OAPageContext pageContext
,OAApplicationModule am)
{
try {
String lv_sql = "SELECT VALUE||'/html'\n" + " FROM apps.fnd_env_context\n" +
" WHERE variable_name ='OAH_TOP'\n" +
" AND concurrent_process_id =\n" +
" (SELECT MAX(concurrent_process_id) \n" +
" FROM apps.fnd_env_context)\n" + " ORDER BY 1";
PreparedStatement ps =
am.getOADBTransaction().getJdbcConnection().prepareStatement(lv_sql);
ResultSet rs = ps.executeQuery();
rs.next();
String PATH = rs.getString(1);
am.getOADBTransaction().putTransientValue("PATH", PATH);
}
catch(Exception e)
{
e.printStackTrace();
}
String PATH = am.getOADBTransaction().getTransientValue("PATH").toString();
return PATH;
}
static final int bBufLen = 4 * 8192;
//to read column structure retrieved from package
public static long readFromClob(Clob clob, Writer out) throws SQLException,
IOException
{
BufferedReader in =
new BufferedReader(new InputStreamReader(clob.getAsciiStream()));
int length = -1;
long read = 0;
char[] buf = new char[bBufLen];
while ((length = in.read(buf, 0, bBufLen)) != -1)
{
out.write(buf, 0, length);
read += length;
}
in.close();
out.flush();
return read;
}
}
STEP 2: (PLSQL package or procedure)
CREATE OR REPLACE PACKAGE xxrhm_acd_ordupld_pkg AUTHID CURRENT_USER
-- +===================================================================================+
+===================================================================================+
-- | Name : XXRHM_ACD_ORDUPLD_PKG
-- |Change Record:
-- |===============
-- |Version Date Author Remarks
-- |======= =========== ============= ======================================
-- |1.0 27-AUG-20XX ************ Initial version ***
-- +====================================================================================+
IS
PROCEDURE get_download_template_asclob (p_template_name_i IN VARCHAR2
,p_file_o OUT CLOB
);
PROCEDURE cancel_filename_existing (p_filename_i IN VARCHAR2);
END xxrhm_acd_ordupld_pkg;
/
Comments
Post a Comment