Running a concurrent program with XML output and opening a pdf output file in new window with just click of a button

Had a requirement of opening aging bucket report from OAF page with just click of a button.

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;

All About MO_GLOBAL and FND_GLOBAL

11i -> dbms_application_info.set_client_info(&org_id);
R12 -> mo_global.set_policy_context('S',&org_id);
Ref: from http://www.orafaq.com/node/2243

How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12 [ID 787677.1]
Ref:from http://oracle.anilpassi.com/mo-global-dive-into-r12-multi-org-design.html

How is CLIENT_INFO being replaced in R12?
Lets take an example.
In pre Release 12, you would have had following methodology for PO_HEADERS_ALL
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"

But now in R12, following will happen
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL

d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL

select * from all_policies where object_name='PO_HEADERS'
 
e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

SELECT * FROM PO_HEADERS
WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)

Explanation:



What is MO_GLOBAL.INIT
Purpose of mo_global.init :-
It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

What is the purpose of MO_GLOBAL.ORG_SECURITY?
The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object.
1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled

What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ?
This procedure has two parameters
p_access_mode
Pass a value "S" in case you want your current session to work against Single ORG_ID
Pass a value of "M" in case you want your current session to work against multiple ORG_ID's
p_org_id
Only applicable if p_access_mode is passed value of "S"



FND_PROFILE and FND_GLOBAL values
[For more information, please visit http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462356.htm]

Following are the FND_PROFILE values that can be used in the PL/SQL code:
   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:
   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:
   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:
   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
    
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);