Some times we face requirement to display the concurrent request output on click of Button or Image.
There are many ways to display PDF report output on OAF page such as,
1) Using XML Publisher data template and by passing XML data through View Object.
In this case we face difficulty if the report is complex and required multiple queries and calculations for report. And if large number of data is there.
2) Using submit request from PL/SQL to generate PDF/XLS output on file server and then downloading file using ServletOutputStream byte by byte and displaying it by DesktopApi.open(). As you can see in my previous blogs.
In this also we face access problems because normally user does not has access to the directory on file server where output file is generated. And in some cases Concurrent Request output file is read only etc.
There is one more way to display concurrent output without downloading the file, by generating the session specific URL for Concurrent Output file. This method is used by Oracle itself to show request output.
Below code can be used in Controller, AM and PL/SQL.
Controller Class code ...
/*
* Handles functionality of Print button / image
*/
if("printRpt".equals(pageContext.getParameter(EVENT_PARAM))) {
String P_Seq = pageContext.getParameter("P_Seq");
String P_USER_ID = pageContext.getUserId()+"";
String P_RESP_ID = pageContext.getResponsibilityId()+"";
String P_RESP_APPL_ID = pageContext.getResponsibilityApplicationId()+"";
if (P_Seq != null) {
// generate output URL
Serializable[] param = { P_Seq, P_USER_ID, P_RESP_ID, P_RESP_APPL_ID};
am.invokeMethod("getOutputURL", param);
String OutputURL = (String)pageContext.getTransactionValue("OutputURL");
if ("ERROR".equals(OutputURL)) {
throw new OAException(" Error in report generation ");
} else {
try {
// redirects to URL
pageContext.sendRedirect((String)OutputURL);
return;
} catch (Exception localException1) {
throw new OAException(" Error in report generation ");
}
}
}
}
Application Module code
/** To generate output URL for CP.
*/
public void getOutputURL(String P_Seq, String P_USER_ID, String P_RESP_ID, String P_RESP_APPL_ID)
{
OADBTransactionImpl localOADBTransactionImpl = (OADBTransactionImpl)getDBTransaction();
String P_TWO_TASK = localOADBTransactionImpl.getAppsContext().getEnvStore().getEnv("TWO_TASK");
String P_GWYUID = localOADBTransactionImpl.getAppsContext().getEnvStore().getEnv("GWYUID");
String str3 = "BEGIN XX_SUBMIT_REQUEST_PKG.SUBMIT_HR_ORDERS_RPT (P_SEQ => "+P_Seq+", P_USER_ID => "+P_USER_ID+", P_RESP_ID => "+P_RESP_ID+", P_RESP_APPL_ID => "+P_RESP_APPL_ID+", P_GWYUID => '"+ P_GWYUID +"', P_TWO_TASK => '"+ P_TWO_TASK +"', P_OUTPUT_URL => :1); END;";
CallableStatement localCallableStatement = getOADBTransaction().createCallableStatement(str3, 1);
if (P_Seq != null)
try
{
localCallableStatement.registerOutParameter(1, Types.VARCHAR);
localCallableStatement.execute();
String str4 = localCallableStatement.getString(1);
if ((str4 == null) || (str4.equals(""))) {
throw new OAException(retrivePLSQLError());
}
localOADBTransactionImpl.putValue("OutputURL", str4);
}
catch (Exception localException2)
{
throw OAException.wrapperException(localException2);
}
finally
{
try {
localCallableStatement.close();
}
catch (Exception localException3)
{
throw OAException.wrapperException(localException3);
}
}
}
/** To retrieve PLSQL Error that might occur.
*/
private String retrivePLSQLError()
throws Exception
{
String str1 = "BEGIN :1 := FND_MESSAGE.GET; END;";
String str2 = "";
CallableStatement localCallableStatement = null;
try
{
localCallableStatement = getOADBTransaction().createCallableStatement(str1, 1);
localCallableStatement.registerOutParameter(1, 12);
localCallableStatement.execute();
str2 = localCallableStatement.getString(1);
localCallableStatement.close();
}
catch (Exception localException2) {
str2 = localException2.getMessage();
throw new Exception(str2);
}
finally
{
try
{
if (localCallableStatement != null) localCallableStatement.close();
}
catch (Exception localException3) {
throw new OAException(" Error in report generation ");
}
}
return str2;
}
PL/SQL Code
Here in PL/SQL code create one package XX_SUBMIT_REQUEST_PKG, inside it create one procedure SUBMIT_HR_ORDERS_RPT. In our AM code we are calling this procedure,
XX_SUBMIT_REQUEST_PKG.SUBMIT_HR_ORDERS_RPT
Now in this procedure use,
APPS.FND_REQUEST.ADD_LAYOUT();
-- To define the Concurrent request Layout i.e. PDF/XLS
APPS.FND_REQUEST.SUBMIT_REQUEST();
-- To Submit the Concrrent request
FND_CONCURRENT.WAIT_FOR_REQUEST();
-- To wait until concurrent request finishes generating output file.
And finally you can use fnd_webfile.get_url under same procedure to get the Output URL like,
OutputURL := fnd_webfile.get_url(file_type => fnd_webfile.request_out,
id => P_Request_ID,
gwyuid => P_gwyuid,
two_task => P_two_task,
expire_time => 1);
Thanks,
Abbas Qureshi
