1. Register/Copy existing program as new program, keep output as XML.
2. Create a link on page
3.
Upon click of Link, page will redirected to another page, and where report will open
Revised Code:
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
System.out.println("submitRequest Starts****");
getOutput(pageContext,webBean);
}
public void getOutput(OAPageContext pageContext, OAWebBean webBean)
{
CustomerAMImpl am3 = (CustomerAMImpl)pageContext.getApplicationModule(webBean);
//System.out.println(scustName);
//calling submit request
int requestId= submitrequest(pageContext,webBean);
//calling wait for request
System.out.println("Start of Loop"+am3.getOADBTransaction().getCurrentDBDate());
try
{
System.out.println("getRequestStatus Called");
getRequestStatus (pageContext,webBean,requestId);
System.out.println("getRequestStatus Ends");
}
catch( Exception ie)
{
System.out.println(ie);
}
System.out.println("End of Loop"+am3.getOADBTransaction().getCurrentDBDate());
String url= "OA.jsp?akRegionCode=FNDCPREQUESTVIEWPAGE&akRegionApplicationId=0&OUTPUT=Y&REQUESTID="+requestId+"&retainAM=Y&addBreadCrumb=Y"; pageContext.setForwardURL
(url,
null,
OAWebBeanConstants.KEEP_MENU_CONTEXT,
null,
null,
true,
OAWebBeanConstants.ADD_BREAD_CRUMB_YES,
OAWebBeanConstants.IGNORE_MESSAGES
);
}
public int submitrequest(OAPageContext pageContext, OAWebBean webBean)
{
CustomerAMImpl am3 = (CustomerAMImpl)pageContext.getApplicationModule(webBean);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
oracle.jbo.domain.Date convertDate = am3.getOADBTransaction().getCurrentDBDate();
java.util.Date date=null;
try {
date = format.parse(convertDate.toString());
} catch (ParseException e) {
// TODO
}
SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String convStr = formatDate.format(date);
System.out.println(convStr);
//2015/03/12 00:00:00
SimpleDateFormat formatDate2 = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
String currentDate = formatDate.format(date);
System.out.println(currentDate);
//CustomerNameTxt
OAMessageStyledTextBean CustomerNameTxt= (OAMessageStyledTextBean)webBean.findIndexedChildRecursive("CustomerNameTxt");
System.out.println("CustomerNameTxt"+CustomerNameTxt.getValue(pageContext));
try
{
OADBTransaction tx = (OADBTransaction)am3.getOADBTransaction();
java.sql.Connection pConncection = tx.getJdbcConnection();
ConcurrentRequest cr = new ConcurrentRequest(pConncection);
String applnName = "AR"; //Application that contains the concurrent program
String cpName = "XXMJFARXAGSW"; //Concurrent program name
String cpDesc = "Aging- 7 Bucket Report"; // concurrent Program description
// Pass the Arguments using vector
//Vector cpArgs = new Vector();
Vector cpParameters = new Vector(20);
cpParameters.addElement("1000"); //SELECT * FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'FND_MO_REPORTING_LEVEL' AND LOOKUP_CODE = 1000;
cpParameters.addElement(getLedgerId(pageContext,webBean));
cpParameters.addElement("ARXAGS"); //select lookup_code from ar_lookups where lookup_type = 'AR_AGING_TYPE' and LOOKUP_CODE = 'ARXAGS';
cpParameters.addElement(getChartOfAcctId(pageContext,webBean));
cpParameters.addElement("");
cpParameters.addElement("Customer");
cpParameters.addElement("C");
cpParameters.addElement("B");
cpParameters.addElement(currentDate+"");//currentDate
cpParameters.addElement("MJF Aging");
cpParameters.addElement("DETAIL");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement(CustomerNameTxt.getValue(pageContext));
cpParameters.addElement(CustomerNameTxt.getValue(pageContext));
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("");
cpParameters.addElement("NONE");
/*
function add_layout (template_appl_name in varchar2,
template_code in varchar2,
template_language in varchar2,
template_territory in varchar2,
output_format in varchar2,
nls_language in varchar2 default null) return boolean is
*/
cr.addLayout("AR","XXMJFARXAGSW","en","US","PDF", null);
// Calling the Concurrent Program
int requestId = cr.submitRequest(applnName, cpName, cpDesc, "", false, cpParameters);
tx.commit();
System.out.println("Request ID is "+requestId);
return requestId;
}
catch (RequestSubmissionException e)
{
System.out.println("Request ID is "+e);
return -1;
} catch (Exception e) {
// TODO
}
return-3;
}
public void getRequestStatus (OAPageContext pageContext, OAWebBean webBean, int requestId)
{
String v_phase, v_status, v_dev_phase, v_dev_status, v_req_status, v_message_out;
if ( requestId <= 0)
System.out.println("Request Not Submitted");
else
System.out.println("getRequestStatus Starts****");
try{
String runRequestStr = "";
OADBTransactionImpl txn = (OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction();
runRequestStr = "BEGIN " +
" apps.xxmjf_conc_request_status( " +
" pi_request_id => :1 , " +
" pi_interval => :2 , " +
" pi_max_wait => :3 , " +
" po_request_phase => :4 , " +
" po_request_status => :5 , " +
" po_dev_request_phase => :6 , " +
" po_dev_request_status => :7 , " +
" po_request_status_mesg => :8 , " +
" po_message_out => :9 " +
" ); " + " END; ";
try {
CallableStatement cs = txn.createCallableStatement(runRequestStr,OADBTransaction.DEFAULT);
cs.setInt(1,requestId);
cs.setInt(2,0);
cs.setInt(3,0);
cs.registerOutParameter(4, Types.VARCHAR);
cs.registerOutParameter(5, Types.VARCHAR);
cs.registerOutParameter(6, Types.VARCHAR);
cs.registerOutParameter(7, Types.VARCHAR);
cs.registerOutParameter(8, Types.VARCHAR);
cs.registerOutParameter(9, Types.VARCHAR);
cs.execute();
v_phase = cs.getString(4);
v_status = cs.getString(5);
v_dev_phase = cs.getString(6);
v_dev_status = cs.getString(7);
v_req_status = cs.getString(8);
v_message_out = cs.getString(9);
System.out.println("v_phase"+v_phase);
System.out.println("v_status"+v_status);
System.out.println("v_dev_phase"+v_dev_phase);
System.out.println("v_dev_status"+v_dev_status);
System.out.println("v_req_status"+v_req_status);
System.out.println("v_message_out"+v_message_out);
} catch (Exception e) {
throw new OAException("Error:" + e, OAException.ERROR);
}
} catch (Exception e1) {
throw new OAException("Error:" + e1, OAException.ERROR);
}
}
public String getLedgerId(OAPageContext pageContext, OAWebBean webBean)
{
//SELECT LEDGER_ID FROM FND_MO_REPORTING_ENTITIES_V WHERE entity_name='PRIMARY LEDGER';
String query="", ledgerId="";
Connection con =
pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
query = "SELECT LEDGER_ID FROM FND_MO_REPORTING_ENTITIES_V WHERE entity_name='PRIMARY LEDGER' and rownum=1";
try
{
PreparedStatement stmt = con.prepareCall(query);
ResultSet set = stmt.executeQuery();
while (set.next()) {
ledgerId = set.getString("LEDGER_ID");
}
set.close();
stmt.close();
System.out.println("ledgerId"+ledgerId);
}
catch(Exception e)
{
System.out.println(e);
return "2021";
}
return ledgerId;
}
public String getChartOfAcctId(OAPageContext pageContext, OAWebBean webBean)
{
//select CHART_OF_ACCOUNTS_ID from gl_sets_of_books where name='PRIMARY LEDGER';
String query="", chartOfAcctId="";
Connection con =
pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
query = "select CHART_OF_ACCOUNTS_ID from gl_sets_of_books where name='PRIMARY LEDGER' and rownum=1";
try
{
PreparedStatement stmt = con.prepareCall(query);
ResultSet set = stmt.executeQuery();
while (set.next()) {
chartOfAcctId = set.getString("CHART_OF_ACCOUNTS_ID");
}
set.close();
stmt.close();
System.out.println("chartOfAcctId"+chartOfAcctId);
}
catch(Exception e)
{
System.out.println(e);
return "50368";
}
return chartOfAcctId;
}
}
PLSQL:
create or replace PROCEDURE apps.xx_conc_request_status(
pi_request_id in NUMBER,
pi_interval in NUMBER,
pi_max_wait in NUMBER,
po_request_phase out VARCHAR2,
po_request_status out VARCHAR2,
po_dev_request_phase out VARCHAR2,
po_dev_request_status out VARCHAR2,
po_request_status_mesg out VARCHAR2,
po_message_out out varchar2
)
as
call_status BOOLEAN;
BEGIN
IF pi_request_id = 0 THEN
DBMS_OUTPUT.PUT_LINE('STATUS=NONE'||pi_request_id);
ELSE
call_status := fnd_concurrent.wait_for_request
(
request_id => pi_request_id ,
interval => 10,
max_wait => 120,
phase => po_request_phase,
status => po_request_status,
dev_phase => po_dev_request_phase,
dev_status => po_dev_request_status,
MESSAGE => po_request_status_mesg
);
END IF;
IF call_status = TRUE THEN
IF po_dev_request_phase!= 'Completed' OR po_dev_request_status IN ('Cancelled' , 'Error' , 'Terminated') THEN
DBMS_OUTPUT.PUT_LINE('STATUS='||po_dev_request_phase);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('WAIT FOR REQUEST FAILED - STATUS UNKNOWN'||po_dev_request_phase);
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED'||sqlerrm);
po_message_out:=sqlerrm;
END;
Test above code:
set serveroutput on;
declare
v_request_phase varchar2(100);
v_request_status varchar2(100);
v_dev_request_status varchar2(100);
v_request_status_mesg varchar2(100);
v_dev_request_phase varchar2(100);
v_message_out varchar2(1000);
BEGIN
xxmjf_conc_request_status( pi_request_id => 1958107, pi_interval => 0, pi_max_wait => 0, po_request_phase => v_request_phase , po_request_status => v_request_status, po_dev_request_phase => v_dev_request_phase, po_dev_request_status => v_dev_request_status, po_request_status_mesg => v_request_status_mesg, po_message_out => v_message_out );
DBMS_OUTPUT.PUT_LINE('v_request_phase'||v_request_phase);
DBMS_OUTPUT.PUT_LINE('v_request_status'||v_request_status);
DBMS_OUTPUT.PUT_LINE('v_dev_request_status'||v_dev_request_status);
DBMS_OUTPUT.PUT_LINE('v_request_status_mesg'||v_request_status_mesg);
DBMS_OUTPUT.PUT_LINE('v_dev_request_phase'||v_dev_request_phase);
END;
