Importer based WebADI

This discuss and shows how to create end to end webadi with importer.

Oracle EBS R12.2.4 VM Template for local EBS installation -setting networks

Oracle EBS R12.2.4 VM Template for local EBS installation

https://blogs.oracle.com/stevenChan/entry/ebs_12_2_4_available

However once we install, we have issues in accessing instance once we shut down, as ip keeps on changing on host machine, so the ifconfig will have different entry every time, so instance will not work.

I have crated a video tutorial to explain and fix it. Its my first video post so please excuse..comments are most welcome..

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;