Thursday, October 29, 2015

How To Run SQL query in OAF

Prepared statement in oaf


  1. import java.sql.Connection;  
  2. import java.sql.PreparedStatement;  
  3. import java.sql.ResultSet;  
  4.   
  5. Expample 1:-
  6.              
  7.  try
  8.               {
  9.                   Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
  10.               String query = "select REQUEST_NUMBER from mtl_txn_request_headers mtrh where mtrh.header_id="+VHEADER_ID+"";
  11.               PreparedStatement stmt = conn.prepareStatement(query);
  12.              // String resultset = stmt.executeQuery().toString();
  13.                   ResultSet resultset = stmt.executeQuery();
  14.                   resultset.next();
  15.               System.out.println("Move Oredr number Is"+resultset.getInt("REQUEST_NUMBER") );
  16.                   OAHeaderBean headerBean = (OAHeaderBean)webBean.findChildRecursive("region2");
  17.                   headerBean.setText("Move Order Number "+resultset.getInt("REQUEST_NUMBER"));
  18.               }     
  19.              catch(Exception e)
  20.               {
  21.                   throw OAException.wrapperException(e);
  22.               }


  23. Example 2:-

  24. try  
  25.   
  26. {  
  27.   
  28.   
  29. Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();  
  30.   
  31. String Query = "SELECT count(*) count from XX_PA_SCO_V where project_id=:1 and CI_ID is not null";  
  32.   
  33. PreparedStatement stmt = conn.prepareStatement(Query);  
  34. stmt.setString(1, project_id);  
  35. for(ResultSet resultset = stmt.executeQuery(); resultset.next();)  
  36. {  
  37. pageContext.writeDiagnostics(this, "Query Executed", 1);  
  38. result = resultset.getInt("count");;  
  39. pageContext.writeDiagnostics(this, "Query Executed"+ result, 1);  
  40. }  
  41. }  
  42.   
  43. catch(Exception exception)  
  44.   
  45. {   
  46. throw new OAException("Error in Staffing Query"+exception, OAException.ERROR);  
  47. }  

Import Oaf page From Command Prompt

D:\OAF\jdevbin\oaext\bin\import D:\OAF\jdevhome\jdev\myclasses\xx\oracle\apps\ak\ajml\webui\MainPG.xml -rootdir D:\OAF\jdevhome\jdev\myclasses -mmddir "D:\OAF\jdevbin\oaext\config\mmd" -username "apps" -password "apps" -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=180.211.97.37)(PORT=1530)) (CONNECT_DATA= (SID=VISION) ) )"

Sunday, October 25, 2015

Xml report in oaf


Controller Code:-

Expected Error:-
if TemplateHelper gives error then we need to TemplateHelper class file in "xdo" folder in apps folder in class.
Write Following Code in Controller:-


      if (pageContext.getParameter("Report") != null)
                           {      
                         
                     //  OAApplicationModule am = pageContext.getApplicationModule(webBean) ;
                       DataObject sessionDictionary = (DataObject)pageContext.getNamedDataObject("_SessionParameters");
                       HttpServletResponse response = (HttpServletResponse)sessionDictionary.selectValue(null,"HttpServletResponse");
                       String sqlStatement1 = new String();
                       String pfilename = "JEG_TAX_2015.xls";
           
                     // am.invokeMethod("FilterPPStart");  
                      am.invokeMethod("PrintXML");
                               
                               try {
                                 ServletOutputStream os = response.getOutputStream();
                                 String contentDisposition = "attachment;filename="+pfilename;
                                 response.setHeader("Content-Disposition",contentDisposition);
                                 response.setContentType("application/MSEXCEL");

                             
                                /// Get the Data XML File as the XMLNode
                               ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

                                   //Starting tag for overall XML
                                   String tag = "<Header>";
                                   byte[] tagBytes = tag.getBytes();
                                   //Write starting tag to outputStream
                                   outputStream.write(tagBytes);
                               XMLNode xmlNode2 = (XMLNode) am.invokeMethod("getPAXML");
                               xmlNode2.print(outputStream);
                                 
                               
                               tag = "</Header>";
                               tagBytes = tag.getBytes();
                               outputStream.write(tagBytes);
                                 
                                 
                               ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
                               ByteArrayOutputStream pdfFile = new ByteArrayOutputStream();
           
                             Properties prop = new Properties();
                           
                               //Generate the PDF Report.

                               TemplateHelper.processTemplate(
                               ((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getAppsContext(),
                               "PO",
                               "JEG_LCD_TEST1",
                              ((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getLanguage(),
                              ((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getCountry(),
                               inputStream,
                               TemplateHelper.OUTPUT_TYPE_EXCEL,
                               null,
                               pdfFile);

                               // Write the PDF Report to the HttpServletResponse object and flush.
                               byte[] b = pdfFile.toByteArray();
                               response.setContentLength(b.length);
                               os.write(b, 0, b.length);
                               os.flush();
                               os.close();
                           



                               }

                               catch(Exception e)
                               {
                               response.setContentType("text/html");
                               System.out.println(e.getMessage());
                               throw new OAException(e.getMessage(), OAException.ERROR);
                               }
                               pageContext.setDocumentRendered(true);

                           }  //if (pageContext.getParameter("Report") != null)

AM Code:-
Create Following Methods In AM

    public void  PrintXML()
           {
         
            try{
                 ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                 OAViewObject vo = (OAViewObject)findViewObject("EmpVO");
               XMLNode xmlNode = (XMLNode)vo.writeXML(4, XMLInterface.XML_OPT_ALL_ROWS);      
               xmlNode.print(outputStream);
              System.out.println(outputStream.toString());
         
            }
            catch (Exception e)
            {
         
                System.out.println("Error");
            }
         
         
         
           }

     
     
       //print xml on the pag
     
     
        public XMLNode getPAXML()
            {
            try
            {
              ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
              OAViewObject vo = (OAViewObject)findViewObject("EmpVO");
                XMLNode xmlNode = (XMLNode)vo.writeXML(4, XMLInterface.XML_OPT_ALL_ROWS);

                return xmlNode;

             // System.out.println(outputStream.toString());
            } catch (Exception e)
            {
              throw new OAException(e.getMessage() + "Custom Error");
            }

            }

Saturday, October 24, 2015

Export Button Code to get data in EXCEL

 1. Take One Button whose style is "exportButton"   
Then write following code in controller change the vo according to requirement.

  if(pageContext.getParameter("ExportActive")!=null)
                             {
                             String emp_no = null;
                             String emp_name = null;
                             String site_name = null;
                           
                             System.out.println("Export Active Button Pressed");

                             //OAApplicationModule am = pageContext.getApplicationModule(webBean);
                             OAViewObject trCallVO = (OAViewObject)am.findViewObject("EmpVO");

                             try
                             {

                             HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
                             response.setContentType("application/text");
                             response.setHeader("Content-Disposition","attachment; filename=" + "Full Population Report" + ".csv");
                             PrintWriter pw = null;
                             pw = response.getWriter();
                             //Writing the headers
                             pw.write("Employee Number");
                             pw.write(",");
                             pw.write("Employee Name");
                             pw.write(",");
                             pw.write("Job");
                             pw.write("\n");
                             // getting table data
                             if(trCallVO!=null)
                             {
                             for(EmpVORowImpl row = (EmpVORowImpl)trCallVO.first();row!=null;row =(EmpVORowImpl)trCallVO.next())
                             {
                             if(row.getEmpno()!=null)
                             {
                           //  pw.write(row.getEmployeeFullName().replaceAll((",",".")));
                           
                                 emp_no = row.getEmpno().toString();
                                 pw.write(emp_no);
                             }
                             else
                             {
                             pw.write(" ");
                             }
                             pw.write(",");
                             if(row.getEname()!=null)
                             {
                             pw.write(row.getEname());
                             }
                             else
                             {
                             pw.write(" ");
                             }
                             pw.write(",");
                             if(row.getJob()!=null)
                             {
                             pw.write(row.getJob());
                             }
                             else
                             {
                             pw.write(" ");
                             }

                                     pw.write("\n");
                           

                                 }
                               
                               
                                 pw.write(" ");
                                 pw.close();
                               
                             }
                             }
                                 catch(Exception e)
                                 {
                                 e.printStackTrace();
                                 }

                             }

Thursday, October 15, 2015

Date Overlap Logic And How to Use Array And loop iterations for tabular form rows validation:-

public void Apply()
    {
       
        JegPaBillRatesVOImpl vo=getJegPaBillRatesVO();
        Date StartDate = (Date)vo.getCurrentRow().getAttribute("EffectiveEndDate");
        int flag=0;
    Date EndtDate = (Date)vo.getCurrentRow().getAttribute("EffectiveEndDate");
        System.out.println("StartDate= "+StartDate);

           
       /* if(StartDate.dateValue().getTime() == EndtDate.dateValue().getTime())
        {
        System.out.println("Inside date validation and throw error messages");
        }*/
       
       
        //VO Instance
        JegPaBillRatesDtlVOImpl vo1= getJegPaBillRatesDtlVO();              
     int Total_rows = vo1.getFetchedRowCount();         
        System.out.println("Tatal line rows "+Total_rows);
       
     
 //Row Instance
   JegPaBillRatesDtlVORowImpl row=null;        
        if(Total_rows>0)
        {
            System.out.println("Tatal rows greter than 0 is "+Total_rows);           
            //Array Declaration
            String sequence1[] = new String[Total_rows];
             Date  StartDate3[] = new Date[Total_rows];
             Date  EndDate3[] = new Date[Total_rows];                     
            // Loop Code For Tabular Rows Validations        
//RowSetIterator Instanc
           RowSetIterator Iter = vo1.createRowSetIterator("Iter");
//Set row range to zero, to traverse from beginning of VO rows collection
            Iter.setRangeStart(0); 
            Iter.setRangeSize(Total_rows);
                   
            for (int i = 0; i < Total_rows; i++)
             {
//Insert Data In To Array

                    row = (JegPaBillRatesDtlVORowImpl)Iter.getRowAtRangeIndex(i);                
                          sequence1[i] = row.getId1().toString();
                          StartDate3[i] = (Date)row.getEffectiveStartDate();
                          EndDate3[i] = (Date)row.getEffectiveEndDate();                          
                         
                          System.out.println(" Outside Exception  StartDate: "+StartDate3[i]);
                          System.out.println(" Outside Exception  EndDate: "+EndDate3[i]);
                          System.out.println(" Outside Exception  sequence1: "+sequence1[i]);
                                  
             }
            //Close row set iterator.           
             Iter.closeRowSetIterator();  
                     
            for (int i = 0; i < Total_rows; i++)
              {
                   for (int j = i + 1; j < Total_rows; j++)
                       {
                            java.sql.Date javaSqlDate;                             
                            javaSqlDate  = EndDate3[i].dateValue();
                           
                              if ((!sequence1[i].equals(sequence1[j])) &&
                                     (StartDate3[j].dateValue().getTime() <= EndDate3[i].dateValue().getTime() )     
                                 )
                                 { 
                                     flag ++;
                                     System.out.println("Dates are wrong ");
                                 }
                          }
              }
                      
                     
         

                   
        }
       
        if (flag!= 0 )
           {
         
           throw new OAException("Date Cannot be Overlap. Please reenter.",
                                                (byte)0);
            }
          else
           {
                    getTransaction().commit();
             
           }
       
     

    } // End of Apply Method

How to Compare Two Dates :-

1.     Get dates from field of view object to variable
// Following is View Object object creation
        JegPaBillRatesVOImpl vo=getJegPaBillRatesVO();
        Date StartDate = (Date)vo.getCurrentRow().getAttribute("EffectiveEndDate");
       
    Date EndtDate = (Date)vo.getCurrentRow().getAttribute("EffectiveEndDate");
        System.out.println("StartDate= "+StartDate);
2.     Write Following Code to compare dates
        if(StartDate.dateValue().getTime() == EndtDate.dateValue().getTime())
        {
        System.out.println("Inside date validation and throw error messages");

        }

How to Create View Object Dynamically

1.   Create Sql Statement in variable:-

String sqlStatement1;
                sqlStatement1 = " SELECT JEG_PA_REGREC_BRATE_BILL_TYPE('"+pptype+"','COLUMN1'"+") FROM DUAL " ;

2.   Check View object is exists or not if exist then remove and Create and execute like following code


OAApplicationModule am = pageContext.getApplicationModule(webBean);

                              if(am.findViewObject("getsqlStatement1VO") != null)
                               {
                                 am.findViewObject("getsqlStatement1VO").remove();
                               }
                      ViewObject getsqlStatement1VO = am.createViewObjectFromQueryStmt("getsqlStatement1VO", sqlStatement1);

                      getsqlStatement1VO.executeQuery();

Partial Rendering Required Codes:-

1.     Create PPRVO1 View Object of following query:-

select 'FALSE' A1 FROM DUAL

2.    Create Following True False methods in Amimpl:-

    public void ColumnTrue1()
           {
          
           OAViewObject pVO = (OAViewObject)findViewObject("PPRVO1");
           OARow row = (OARow)pVO.first();
           row.setAttribute("A1", Boolean.TRUE);
           }
    public void ColumnFalse1()
            {
           
            OAViewObject pVO = (OAViewObject)findViewObject("PPRVO1");
            OARow row = (OARow)pVO.first();
            row.setAttribute("A1", Boolean.FALSE);
            }

3.   Call these methods in main controller where we need:-
OAApplicationModule am = pageContext.getApplicationModule(webBean);
am.invokeMethod("ColumnFalse1");
am.invokeMethod("ColumnTrue1") ;
4.   Set Render Property

           ${oa.PPRVO1.A1}

How to solve issues Developer Mode Error: Stale Data Error

In PR method i wrote this :

-----------

OAApplicationModule am = pageContext.getApplicationModule(webBean);
am.invokeMethod("initializeInsertDetails"); 
-------------



in AM 

----


public void initializeInsertDetails(){

OAViewObject insertVO = getInsertDeviceDetailsEOView1();

if (!insertVO.isPreparedForExecution()) 
{ 
insertVO.executeQuery(); 
} 

//Create a blank Row
Row row = insertVO.createRow();
//insertVO.createRow();
//Attach that blank row to the VO. Data will be fed into this row, when the user types into the fields
insertVO.insertRow(row);
//Set the status of the blank row to initialized. This tells OA Framework that record is blank and must not be included in DML 
//Operations until changes are made to its underlying VO [via screen fields]
row.setNewRowState(Row.STATUS_INITIALIZED);



}// end initializeInsertDetails ()

Nevigate From One Page To Another:-

      if (pageContext.getParameter("Go") != null)
                   {
                           String V_VENDOR_NAME = pageContext.getParameter("VendorName");
                           String V_VENDOR_ID =  pageContext.getParameter("VendorIDVariable");
                           System.out.println("Vendor Name = "+V_VENDOR_NAME);
                           System.out.println("Vendor ID = "+V_VENDOR_ID);
                         
                          String V_VendorSite = pageContext.getParameter("VendorSite");
                          String V_VendorSiteIdVariable =  pageContext.getParameter("VendorSiteIdVariable");
                       System.out.println("Vendor Site Code = "+V_VendorSite);
                       System.out.println("Vendor Site ID = "+V_VendorSiteIdVariable);
                      
                      
                        String V_InvoiceNum = pageContext.getParameter("InvoiceNum");
                        String V_InvoiceId =  pageContext.getParameter("InvoiceIdVariable");
                       System.out.println("Invoice Num = "+V_InvoiceNum);
                       System.out.println("Invoice ID = "+V_InvoiceId);
                      
                     
                      
                           String message="Go Button Sumbited Sucessfully !";
                          
                      
                       HashMap xxhashMap = new HashMap(5);
                       xxhashMap.put("P_InvoiceId", V_InvoiceId);
                       xxhashMap.put("P_VENDOR_ID", V_VENDOR_ID);
                       xxhashMap.put("P_VendorSiteIdVariable", V_VendorSiteIdVariable);
                       xxhashMap.put("P_Action", "Go");
                      
                       if(
                       pageContext.getParameter("VendorName").equals("") && pageContext.getParameter("VendorSite").equals("")
                       && pageContext.getParameter("InvoiceNum").equals("")
                       )
                       {
                      
                           String message1= "Please Select Atleast One Parameter ! ";
                           throw new OAException(message1, OAException.ERROR);
                       }
                      
    
                         pageContext.setForwardURL("OA.jsp?page=/Krishna/oracle/apps/ak/FreightVendor/webui/FreightResultPG",
                                                           null,
                                                           OAWebBeanConstants.KEEP_MENU_CONTEXT,                            
                                                           null,                                                   
                                                           xxhashMap,
                                                           true,                            
                                                           OAWebBeanConstants.ADD_BREAD_CRUMB_NO,
                                                           OAWebBeanConstants.IGNORE_MESSAGES);
                      
                          
                           throw new OAException(message, OAException.INFORMATION);
                         
                      
                     

                   }