This discussion is archived
4 Replies Latest reply: Feb 12, 2013 9:01 AM by Marcin RSS

How to add SQL validation in controller

Marcin Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points