CreateObjectivePageCO

package xxcus.oracle.apps.per.wpm.objectives.webui;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.per.wpm.objectives.webui.CreateObjectivePageCO;

public class CreateObjectivePageCOEx extends CreateObjectivePageCO {
    public CreateObjectivePageCOEx() {
    }
   
    public void processFormRequest(OAPageContext pc, OAWebBean wb) {
    super.initTransactionDefaults(pc);
   
        String scorecardId = pc.getDecryptedParameter("pScorecardId");
        String event = pc.getParameter("event");
        Integer totalwt = 0;
        String person_id = (String)pc.getSessionValue("SELECTED_USER_ID");
        Integer biz_group_id = pc.getBusinessGroupId();
        Integer l_min = 0;
        Integer l_max = 0;
        Integer objcount = 0;
        String str3 = null;
        Integer istr3 = 0;
        Integer l_totalwt = 0;
        Integer l_objcount = 0;

        if (("Apply".equals(event)) ||
            ("CreateAnother".equals(event))
            )
        {

        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");       
                }
               
                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);
                }
           
            try {
                Connection conn =
                    pc.getApplicationModule(wb).getOADBTransaction().getJdbcConnection();

                String Query =
                "select sum(totalwt) totalwt, count(1) objcount\n" +
                "from ( \n" +
                " SELECT  (decode(extractvalue(VALUE(xx_row),  \n" +
                "                           '/ObjectiveEORow/WeightingPercent'),  \n" +
                "              '(null)',                0, \n" +
                "              '',                0,  \n" +
                "              extractvalue(VALUE(xx_row), '/ObjectiveEORow/WeightingPercent'))) AS totalwt  \n" +
                "  FROM HR_API_TRANSACTIONS xx_api,  \n" +
                "       TABLE(xmlsequence(extract(xmlparse(document transaction_document wellformed),\n" +
                "                                 '/Transaction/TransCache/AM/TXN/EO/ObjectiveEORow'))) xx_row  \n" +
                " WHERE xx_api.transaction_ref_id = :1\n" +
                "union all \n" +
                "  SELECT po.weighting_percent AS totalwt\n" +
                "    FROM per_objectives po, per_people_f ppf\n" +
                "   WHERE po.scorecard_id = NVL (:2, po.scorecard_id)\n" +
                "     AND ppf.person_id(+) = po.owning_person_id\n" +
                "     AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+)\n" +
                "     AND appraisal_id IS NULL\n" +
                " )\n" +
                " ";
               
                PreparedStatement stmt = conn.prepareStatement(Query);
                stmt.setString(1, scorecardId);
                stmt.setString(2, scorecardId);

                for (ResultSet resultset = stmt.executeQuery();
                     resultset.next(); ) {
                    totalwt = resultset.getInt("totalwt");
                    objcount = resultset.getInt("objcount");
                }
            }
             catch (Exception exception) {
                throw new OAException("Error in Weight Extension " + exception,
                                      OAException.ERROR);
            }
/*
            if (true) {
            throw new OAException("scorecardId = " +scorecardId+
                                  " totalwt= " + totalwt+
                                  " person_id" +person_id+
                                  " biz_group_id"+biz_group_id+
                                  " l_min="+l_min+
                                  " l_max="+l_max+
                                  " objcount="+objcount, OAException.INFORMATION);
            }
*/

            //get page values
           
            str3 = pc.getParameter("Weighting");
           
            if (str3!="") { istr3 = Integer.parseInt(str3); }
            if (istr3 < 5 || istr3 > 30)
            {
                throw new OAException(" Objective Weighting should be between 5 and 30", OAException.ERROR);
            }           

            l_totalwt = totalwt+istr3;
            l_objcount = objcount+1;
           
            if (l_totalwt > 100)
            {
                throw new OAException("Total Weights exceed 100. Total Weight " +
                    "including current objective is:"+l_totalwt, OAException.ERROR);
            }
           
            if ("CreateAnother".equals(event) && (l_objcount == l_max))
            {
                throw new OAException("Maximum Objectives created, cannot create more. Click Apply instead", OAException.ERROR);
            }

           

        }
       
        super.processFormRequest(pc, wb);
    }   
   
}