SetObjectivesPageCO

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.SetObjectivesPageCO;

public class SetObjectivesPageCOEx extends SetObjectivesPageCO {
    public SetObjectivesPageCOEx() {
    }

    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;

        if
            //("DuplicateObj".equals(pc.getParameter(EVENT_PARAM)))
            (pc.getParameter("EmpFinish") != null ||
             pc.getParameter("MgrFinish") != null ||
             ("CreateObj".equals(event)) || ("CopyObj".equals(event)) ||
             ("DuplicateObj".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);
            }
*/
            //handle finish button
            if (pc.getParameter("EmpFinish") != null ||
                pc.getParameter("MgrFinish") != null) {
                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 (totalwt < 100)
                {
                    throw new OAException(" Total Weighting Scale is Less than 100, must be equal to 100",
                                        OAException.ERROR);
                }
*/

                if (totalwt > 100) {
                    throw new OAException(" Total Weighting Scale is Greater than 100, must be equal to 100", OAException.ERROR);
                }
            }

            //handle create, copy and duplicate objectives
            if (("CreateObj".equals(event)) || ("CopyObj".equals(event)) ||
                //("DuplicateObj".equals(pc.getParameter(EVENT_PARAM)))
                ("DuplicateObj".equals(event))
                ) {
                if (objcount >= l_max) {
                    throw new OAException("Maximum Objectives created, cannot create more ", OAException.ERROR);
                }
                if (totalwt >= 100) {
                    throw new OAException(" Total Weighting Scale is already :" +
                                          totalwt + " cannot be morethan 100", OAException.ERROR);
                }
            }
        }

        super.processFormRequest(pc, wb);
    }
}