MAAppraisePageCO

package xxcus.oracle.apps.per.selfservice.appraisals.webui;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.per.selfservice.appraisals.webui.MAAppraisePageCO;
//import oracle.apps.per.selfservice.objectives.server.ObjectivesAMImpl;
//import oracle.apps.per.selfservice.objectives.server.PerObjPerfRatingsVOImpl;

import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;


public class MAAppraisePageCOEx extends MAAppraisePageCO {
    public MAAppraisePageCOEx() {
    }

    public void processFormRequest(OAPageContext pc, OAWebBean wb) {

        String appraisalId = pc.getDecryptedParameter("AppraisalId");
        //String empnum = "3453"; //pc.getParameter("EmployeeNumber");
        String person_id = pc.getParameter("ObjPersonId");
        Integer totalwt = 0;
    Integer objcount = 0;
    String l_objname = null;
    String wt_5_30_flag = "N";
    Integer l_wt = 0;
    Integer l_min = 0;
    Integer l_max = 0;       
    Integer biz_group_id = pc.getBusinessGroupId();   
        String v_comments = null;
        String overall_rating = null;
        String overall_sql = null;       
        String display_rating = null;
       
      String rating = null; //pc.getParameter("ObjPerfLevel");
      String sql = null; //"Select step_value from PER_RATING_LEVELS_VL where rating_level_id =" + rating;
      String display_val = null;
      Integer intVal = 0;

      OAApplicationModule rootAM = pc.getRootApplicationModule();
      OAApplicationModule am = (OAApplicationModule)rootAM.findApplicationModule("ObjectivesAM");
      OAViewObject vo = (OAViewObject)am.findViewObject("PerObjPerfRatingsVO");
       
        if (pc.getParameter("Apply") != null)
        {
            try {
                Connection conn = pc.getApplicationModule(wb).getOADBTransaction().getJdbcConnection();

                String Query =
                    "SELECT   NVL(poe.weighting_percent, 0) weight, poe.name objname " +
                    "    FROM per_objectives poe," +
                    "         per_performance_ratings ppr," +
                    "         per_performance_ratings ppr1," +
                    "         per_performance_ratings ppr2," +
                    "         per_appraisals pap " +
                    "   WHERE poe.objective_id = ppr.objective_id(+) " +
                    "     AND poe.objective_id = ppr1.objective_id(+) " +
                    "     AND poe.objective_id = ppr2.objective_id(+) " +
                    "     AND poe.appraisal_id = :1 " +
                    "     AND ppr.person_id(+) = :2 " +
                    "     AND ppr1.person_id(+) = :3 " +
                    "     AND ppr2.person_id(+) = :4 " +
                    "     AND pap.appraisal_id = poe.appraisal_id " +
                    "ORDER BY UPPER (NAME)";

                PreparedStatement stmt = conn.prepareStatement(Query);
                stmt.setString(1, appraisalId); 
                stmt.setString(2,person_id);
                stmt.setString(3,person_id);
                stmt.setString(4,person_id);
               
                for (ResultSet resultset = stmt.executeQuery(); resultset.next(); )
                {
                    l_wt = resultset.getInt("weight");
                    if (l_wt < 5 || l_wt > 30)
                    {
                      wt_5_30_flag = "Y";
                      l_objname = resultset.getString("objname");
                      break;
                    }
                    totalwt = resultset.getInt("weight") + totalwt;
                    objcount = objcount + 1;
                }

            } catch (Exception exception) {
                throw new OAException("Error in Weight Extension " + exception,
                                      OAException.ERROR);
            }
           
            try {
                Connection conn1 =
                    pc.getApplicationModule(wb).getOADBTransaction().getJdbcConnection();
                String Query1 =
                    "select rate_id, maximum, minimum" + "  from per_people_f papf" +
                    "     ,per_assignments_f paaf" +
                    "     ,pay_grade_rules_f pgrf" +
                    " where papf.person_id = paaf.person_id" +
                    "  and paaf.grade_id = pgrf.grade_or_spinal_point_id" +
                    "  and sysdate between papf.effective_start_date and papf.effective_end_Date" +
                    "  and sysdate between paaf.effective_start_date and paaf.effective_end_Date" +
                    "  and sysdate between pgrf.effective_start_date and pgrf.effective_end_Date" +
                    "  and papf.person_id = :1     " + "  and rate_id =  " +
                    "    (select rate_id from PAY_RATES" +
                    "     where upper(name) like '%OBJECTIVES'" +
                    "     and business_group_id = :2) ";

                PreparedStatement stmt1 = conn1.prepareStatement(Query1);
                stmt1.setString(1, person_id);
                stmt1.setInt(2, biz_group_id);

                for (ResultSet resultset1 = stmt1.executeQuery();
                     resultset1.next(); ) {
                    //l_min = resultset1.getInt("minimum");
                    //l_max = resultset1.getInt("maximum");
                    l_min = Integer.parseInt(resultset1.getString("minimum"));
                    l_max = Integer.parseInt(resultset1.getString("maximum"));
                }

                if (l_min == 0 || l_min == null || l_max == 0 ||
                    l_max == null) {
                    l_min = 5;
                    l_max = 8;
                }

            } catch (Exception exception) {
                throw new OAException("Error in Weight Extension " + exception, OAException.ERROR);
            }


//validation for Objectives
    for(Row row = vo.first(); row!=null; row=vo.next()) {
        if (row.getAttribute("PerfLevelId")!=null) {
            rating = row.getAttribute("PerfLevelId").toString();
            sql = "Select step_value from PER_RATING_LEVELS_VL where rating_level_id =" + rating;   
            try {
                Connection conn = pc.getApplicationModule(wb).getOADBTransaction().getJdbcConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                rs.next();
                display_val = rs.getString(1).trim();
                }
            catch (Exception e) {
                e.printStackTrace();
            }

            if (display_val.equals("1") || display_val.equals("2")|| display_val.equals("4") || display_val.equals("5"))
            {
                if (row.getAttribute("PerfComments")==null) {
                    throw new OAException("Enter Comments for Objective: '"+row.getAttribute("Name")+"'", OAException.ERROR);
                }           
            }   
        }
    }

     
//validation for Overall Rating and Comments   
    if (pc.getParameter("OverallRating") != null || pc.getParameter("OverallRating") != "")
    {
     overall_rating = pc.getParameter("OverallRating");
     overall_sql = "Select step_value from PER_RATING_LEVELS_VL where rating_level_id =" + overall_rating;

     try {
             Connection conn = pc.getApplicationModule(wb).getOADBTransaction().getJdbcConnection();
             PreparedStatement ps = conn.prepareStatement(overall_sql);
             ResultSet rs = ps.executeQuery();
             rs.next();
             display_rating = rs.getString(1).trim();

         }
         catch (Exception e) {
             e.printStackTrace();
         }

        if (display_rating != null)
        {
         if (display_rating.equals("1") || display_rating.equals("2") || display_rating.equals("4") || display_rating.equals("5"))
         {
             if (pc.getParameter("OverallComments").trim().equals(null) || pc.getParameter("OverallComments") == "")
             {
                 throw new OAException("Overall Comments is mandatory !", OAException.ERROR);
             }
         }
        }
    }


    if (objcount < l_min || objcount > l_max)
    {
        throw new OAException("Objective Count is Incorrect, cannot be lessthan " +
                                l_min + " and cannot be morethan " + l_max,OAException.ERROR);
    }   
   
    if ("Y".equals(wt_5_30_flag))
    {
        throw new OAException("Weighting Scale for Objective: " + l_objname +
                             " cannot be greater than 30 and lessthan 5", OAException.ERROR);
    }   
   
    if (totalwt > 100)
    {
        throw new OAException(" Total Weighting Scale must be lessthan 100", OAException.ERROR);
    }
   }
        super.processFormRequest(pc, wb);
  }
}