EmpFinObjectivesPageCO

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

public class EmpFinObjectivesPageCOEx extends EmpFinObjectivesPageCO {
    public EmpFinObjectivesPageCOEx() {
    }

    public void processFormRequest(OAPageContext pc, OAWebBean wb) {
        //super.initTransactionDefaults(pc);

        String scorecardId = pc.getDecryptedParameter("pScorecardId");
        String event = pc.getParameter("event");
        Integer totalwt = 0;
        Integer objcount = 0;

        if ("Finish".equals(event)) {
            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 (totalwt > 100) {
                throw new OAException(" Total Weighting Scale is Greater than 100, must be equal to 100", OAException.ERROR);
            }

            if (totalwt < 100) {
                throw new OAException(" Total Weighting Scale is Less than 100, must be equal to 100", OAException.ERROR);
            }
        }
        super.processFormRequest(pc, wb);
    }
}