4 Replies Latest reply: Feb 12, 2013 11:01 AM by Marcin RSS

    How to add SQL validation in controller

    Marcin
      Hi,

      I extend creditApplicationCO controller.
      I need to make custom validation in it.

      Let me present the logic in PL SQL embeded in my controller.
      public class xxcreditApplicationCO extends creditApplicationCO 
      {
              
          public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
              {        
              super.processFormRequest(oapagecontext, oawebbean);
      
                       String s = (String)oapagecontext.getParameter("_FORM_SUBMIT_BUTTON"); 
      
              OAMessageLovInputBean CurrencyHandle = (OAMessageLovInputBean)oawebbean.findChildRecursive("Arcmcurrency");                    
                
                   if (s.equals("ArCmSubmit") && CurrencyHandle.getValue(oapagecontext) !=null)   // form submit
                   {
                     String Currency = CurrencyHandle.getValue(oapagecontext).toString();
      
                      OAViewObject xoaviewobject = (OAViewObject)oapagecontext.getApplicationModule(oawebbean).findViewObject("creditRequestsVO");
                      oracle.jbo.Row row = xoaviewobject.first();
                      String SiteUseId = row.getAttribute("SiteUseId").toString();
                                 
      /* HERE MY VALIDATION LOGIC
      // PL SQL
                
              DECLARE 
      
              V_VALIDATION VARCHAR2(1) :='N' ;
      
              BEGIN 
      
                  SELECT 'Y'
                  INTO v_validation
                  FROM HZ_CUSTOMER_PROFILES P, HZ_CUST_PROFILE_AMTS PA
                  WHERE 1=1
                  AND P.CUST_ACCOUNT_PROFILE_ID=PA.CUST_ACCOUNT_PROFILE_ID
                  AND P.SITE_USE_ID= 69885        --  SiteUseId -- OAF VARIABLE
                  and PA.CURRENCY_CODE= 'CAD' ;   --  Currency -- OAF VARIABLE;
          
              EXCEPTION when no_data_found then  
          
                    DBMS_OUTPUT.PUT_LINE('Not validated');    
          
      --  throw new OAException(Currency+" is wrong currecy for this customer. Please choose other currency or setup customer for " +Currency+". "+SiteUseId, OAException.INFORMATION);
              END;
      
      */
                    
                     }
             }
      }
      Please guide me how to implement this kind of validation in OAF.

      Regards,
      Marcin
        • 1. Re: How to add SQL validation in controller
          shreevat
          Create a PLSQL procedure/function and call that procedure in your CO/AM using CallableStatements. Search OAF forum for examples. Additionally you can also execute the SQL query directly using CallableStatement.executeQuery and get the results into a resultset. Compare the result as needed. Check OAF Guide/OAF Forum for examples.

          Thanks
          Shree
          • 2. Re: How to add SQL validation in controller
            Kalimuthu V-Oracle
            Hi Mercin,
            The easy way is to call the sql alone to ge tthe value Y or N and handle it in your controller.,
            Please find the below sample code to run the sql from your controller,

            Btw, I saw ur extended controller code has
            super.processFormRequest(oapagecontext, oawebbean);
            in the starting even before your validation,
            if you want to stop the control immediately after your validation, better you put your logic first and then call
            "super.processFormRequest(oapagecontext, oawebbean); "


            Tweak the below method according to your requirement, you call this method and pass the pSqlStmt and lParam (if you have a parameter...etc).

            Execute sql directly in CO
            ----------------------------
            import oracle.apps.fnd.framework.server.OADBTransaction;
            import oracle.jdbc.driver.OraclePreparedStatement;
            import java.sql.ResultSet;
            import java.sql.CallableStatement;
            import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
            /**
            *
            * Method to execute SQL with one param
            */
            public Object executeSql(OAPageContext pageContext, OAWebBean webBean , String pSqlStmt, String lParam)
            {
            OADBTransaction tx = (OADBTransaction)pageContext.getApplicationModule(webBean).getOADBTransaction();
            Object lObject = null;

            // Create the callable statement
            OraclePreparedStatement lCstmt = (OraclePreparedStatement)tx.createPreparedStatement(pSqlStmt, 1);
            ResultSet rs = null;
            try
            {
            lCstmt.setObject(1,lParam);
            rs = lCstmt.executeQuery();
            while(rs.next())
            {
            lObject = rs.getObject(1);
            }
            }
            catch (Exception e)
            {
            throw OAException.wrapperException(e);
            }
            finally
            {
            try {
            rs.close();
            lCstmt.close();
            }
            catch(Exception e) {
            throw OAException.wrapperException(e);
            }
            }
            return lObject;
            }
            • 3. Re: How to add SQL validation in controller
              DilipG
              Hi Marcin,

              Please see how to call PLSQL procedure in OAF.

              1)Create PLSQL Procedure as per your need (in my case i have created to insert data in temp table)

              PROCEDURE roll_proc (
              p_roll_line_id IN VARCHAR2,
              p_po_number IN roll_details_temp.po_number%TYPE,
              AS
              p_roll NUMBER := NULL;
              l_count NUMBER;
              BEGIN

              SELECT COUNT (roll_line_id)
              INTO l_count
              FROM roll_details_temp
              WHERE roll_line_id = p_roll_line_id;

              IF l_count = 0
              THEN
              INSERT INTO roll_details_temp
              (roll_line_id, po_number,

              )
              VALUES (list_line_id.NEXTVAL, p_po_number);

              COMMIT;
              END IF;
              COMMIT;
              EXCEPTION
              WHEN OTHERS
              THEN
              DBMS_OUTPUT.put_line
              ('Error in Inserting/Updating Records in temporary');
              END roll_proc;

              2)I have write code in controller on save button event
              (Here we consider Po number as messageTextinput and Roll Line Id is Sequence)
              First enter data in field and click on save Button

              if (pageContext.getParameter("SaveBtn") != null)
              {
              am.invokeMethod("SaveData");
              }


              3)After click on save Button, SaveData method invoked from AMImpl.

              public void SaveData()
              {
              try
              {
              OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getDBTransaction();
              String s = "begin roll_proc(p_roll_line_id=>:1,p_po_number=>:2); commit;end; ";
              xx_tempvoImpl studVo = getxx_tempvo1();

              for(Row row=studVo.first();row!=null;row=studVo.next())
              {
              xx_tempvoRowImpl rowi = (xx_tempvoRowImpl)row;
              OracleCallableStatement cstmt = null;
              cstmt = (OracleCallableStatement)getOADBTransaction().createCallableStatement(s, -1);
              cstmt.setString(1, rowi.getRollLineId());
              cstmt.setString(2, rowi.getPoNumber());
              cstmt.execute();
              cstmt.close();
              }
              }
              catch (SQLException sqlexception)
              {
              throw OAException.wrapperException(sqlexception);
              }
              }


              4)Finally You can check data in roll_datail Temp Table


              Thanks,
              DilipG
              • 4. Re: How to add SQL validation in controller
                Marcin
                My first extended controller works :-)
                package xx.oracle.apps.ar.creditmgt.application.webui;
                
                import oracle.apps.ar.creditmgt.application.webui.creditApplicationCO;
                import oracle.apps.fnd.framework.OAViewObject;
                import oracle.apps.fnd.framework.webui.OAPageContext;
                import oracle.apps.fnd.framework.webui.beans.OAWebBean;
                import oracle.apps.fnd.framework.OAException;
                import oracle.apps.fnd.framework.webui.beans.message.OAMessageLovInputBean;
                import oracle.apps.fnd.framework.server.OADBTransaction;
                
                import java.sql.CallableStatement;
                import java.sql.SQLException;
                import java.sql.Types;
                
                public class xxcreditApplicationCO extends creditApplicationCO 
                {
                        
                    public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
                        {        
                
                
                                 String s = (String)oapagecontext.getParameter("_FORM_SUBMIT_BUTTON"); 
                
                        OAMessageLovInputBean CurrencyHandle = (OAMessageLovInputBean)oawebbean.findChildRecursive("Arcmcurrency");                    
                          
                             if (s.equals("ArCmSubmit") && CurrencyHandle.getValue(oapagecontext) !=null)   // form submit
                             {
                                String Currency = CurrencyHandle.getValue(oapagecontext).toString();
                
                                OAViewObject xoaviewobject = (OAViewObject)oapagecontext.getApplicationModule(oawebbean).findViewObject("creditRequestsVO");
                                oracle.jbo.Row row = xoaviewobject.first();
                                String SiteUseId = row.getAttribute("SiteUseId").toString();
                
                                String outParamValue; 
                                OADBTransaction txn  = oapagecontext.getApplicationModule(oawebbean).getOADBTransaction();
                                CallableStatement cs = txn.createCallableStatement("begin :1 := xx_ar_cmgt_util.VALIDATECURRENCY(:2,:3); end;",1);
                                  
                                  try
                                  {
                                    cs.registerOutParameter(1, Types.VARCHAR);
                                    cs.setString(2,SiteUseId);
                                    cs.setString(3,Currency);
                                    cs.execute();
                                    outParamValue = cs.getString(1);
                                    cs.close();
                                  }
                                  catch (SQLException sqle)
                                  {
                                      throw OAException.wrapperException(sqle);
                                  }
                
                                if (outParamValue.equals("N"))
                                {
                                throw new OAException(Currency+" is wrong currecy for this customer. Please choose other currency or setup customer for " +Currency+". "+SiteUseId+". "+outParamValue, OAException.INFORMATION);                 
                                }
                
                               }
                        super.processFormRequest(oapagecontext, oawebbean);
                       }
                }
                Thank all of you.
                Marcin