/*
 * The Kuali Financial System, a comprehensive financial management system for higher education.
 *
 * Copyright 2005-2016 The Kuali Foundation
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.kuali.kfs.module.bc.document.dataaccess.impl;

import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
import org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao;
import org.kuali.kfs.sys.KFSConstants;

import java.util.ArrayList;
import java.util.UUID;

/**
 * implements the SQL procedures to calculate benefits for the personnel object codes in the budget. apply the appropriate
 * percentage to each object type in the general ledger, and split the result out into the monthly budget lines if monthly budgets
 * exist for the accounting key.
 */

public class BenefitsCalculationDaoJdbc extends BudgetConstructionDaoJdbcBase implements BenefitsCalculationDao {

    protected static ArrayList<SQLForStep> sqlAnnualSteps = new ArrayList<SQLForStep>(6);
    protected static ArrayList<SQLForStep> sqlMonthlySteps = new ArrayList<SQLForStep>(4);


    /**
     * these will be set to constant values in the constructor and used throughout SQL for the various steps.
     */

    public BenefitsCalculationDaoJdbc() {

        // this is a bean constructor, so it is dangerous to access static constants defined in other classes here. the other
        // classes may not have been loaded yet.
        // so, we use insertion points to indicate where such constants should be placed in the SQL, and we splice them in a run
        // time. we also use insertion points to splice in run time constants from SH_PARM_T.
        StringBuilder sqlBuilder = new StringBuilder(2500);
        ArrayList<Integer> insertionPoints = new ArrayList<Integer>();
        /**
         * this needs to be done before we can get rid of annual fringe benefits objects with no base. LD_BNCSTR_MNTH_T has an RI
         * child constraint on LD_PND_BCNSTR_GL_T. So, before we eliminate any Budget Construction general ledger rows, we have to
         * get rid of any dependent Budget Construction Monthly rows. If we call this set of queries to rebuild budgeted benefits
         * for the general ledger, the next set of queries will also have to be called if monthly budgets exist. If no monthly
         * budgets exist, the query below will not do anything. In that case, calling the Budget Construction general ledger
         * benefits calculation routine without calling the monthly benefits calculation routine will be acceptable.
         */
        sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
        sqlBuilder.append("WHERE (LD_BCNSTR_MONTH_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = ?)\n");
        sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("AND (EXISTS (SELECT 1\n");
        sqlBuilder.append("       FROM (LD_PND_BCNSTR_GL_T INNER JOIN LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("       ON ((LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = LD_BENEFITS_CALC_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("           AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = LD_BENEFITS_CALC_T.FIN_COA_CD)\n");
        sqlBuilder.append("           AND (LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD = LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD)))\n");
        sqlBuilder.append("       WHERE (LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("         AND (LD_BCNSTR_MONTH_T.FDOC_NBR = LD_PND_BCNSTR_GL_T.FDOC_NBR)\n");
        sqlBuilder.append("         AND (LD_BCNSTR_MONTH_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
        sqlBuilder.append("         AND (LD_BCNSTR_MONTH_T.ACCOUNT_NBR = LD_PND_BCNSTR_GL_T.ACCOUNT_NBR)\n");
        sqlBuilder.append("         AND (LD_BCNSTR_MONTH_T.SUB_ACCT_NBR = LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR)\n");
        sqlBuilder.append("         AND (LD_BCNSTR_MONTH_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
        sqlBuilder.append("         AND (LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT = 0)))\n");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * get rid of fringe benefits objects with no base
         */
        sqlBuilder.append("DELETE FROM LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR =?)\n ");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT = 0)\n");
        sqlBuilder.append("  AND (EXISTS (SELECT 1 FROM LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("  AND (LD_BENEFITS_CALC_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
        sqlBuilder.append("  AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * set the request to 0 for fringe benefits objects with base
         */
        sqlBuilder.append("UPDATE LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("SET ACLN_ANNL_BAL_AMT =0\n");
        sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("  AND (EXISTS (SELECT 1 FROM LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("               WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("                 AND (LD_BENEFITS_CALC_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
        sqlBuilder.append("                 AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * sum the amounts in benefits-eligible objects and attach the appropriate benefits object code
         */
        sqlBuilder.append("INSERT INTO LD_BCN_BENEFITS_RECALC01_MT\n(SESID, POS_FRNGBEN_OBJ_CD, FB_SUM)\n");
        sqlBuilder.append("(SELECT ?,LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD,\n");
        sqlBuilder.append(" ROUND(SUM(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT * (LD_BENEFITS_CALC_T.POS_FRNG_BENE_PCT/100.0)),0)\n ");
        sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T,\n");
        sqlBuilder.append("      LD_LBR_OBJ_BENE_T,\n");
        sqlBuilder.append("      LD_BENEFITS_CALC_T\n");
        sqlBuilder.append(" WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT <> 0)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = LD_LBR_OBJ_BENE_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = LD_LBR_OBJ_BENE_T.fin_coa_cd)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_LBR_OBJ_BENE_T.fin_object_cd)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.actv_ind = ?)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.univ_fiscal_yr = LD_BENEFITS_CALC_T.univ_fiscal_yr)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.fin_coa_cd = LD_BENEFITS_CALC_T.fin_coa_cd)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.finobj_bene_typ_cd = LD_BENEFITS_CALC_T.pos_benefit_typ_cd)\n");
        sqlBuilder.append("   AND (LD_BENEFITS_CALC_T.actv_ind = ?)\n");
        sqlBuilder.append(" GROUP BY LD_BENEFITS_CALC_T.pos_frngben_obj_cd)");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * re-set the request amount for the appropriate benefits code
         */
        sqlBuilder.append("UPDATE LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("SET acln_annl_bal_amt =\n");
        sqlBuilder.append("        (SELECT LD_BCN_BENEFITS_RECALC01_MT.fb_sum\n");
        sqlBuilder.append("         FROM LD_BCN_BENEFITS_RECALC01_MT\n");
        sqlBuilder.append("        WHERE (LD_BCN_BENEFITS_RECALC01_MT.sesid = ?)\n");
        sqlBuilder.append("          AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd))\n");
        sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.fdoc_nbr = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.univ_fiscal_yr = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.account_nbr = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.sub_acct_nbr = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = '");
        // default sub object code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("')\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = '");
        // general ledger budget balance type code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("')\n");
        sqlBuilder.append("  AND EXISTS (SELECT 1\n");
        sqlBuilder.append("              FROM LD_BCN_BENEFITS_RECALC01_MT\n");
        sqlBuilder.append("              WHERE (sesid = ?)\n");
        sqlBuilder.append("                AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd))\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.fin_obj_typ_cd IN ");
        // expenditure object types
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append(")");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder, insertionPoints));
        sqlBuilder.delete(0, sqlBuilder.length());
        insertionPoints.clear();
        /**
         * now re-insert rows with zero base which still have benefits-eligible object codes in pending BC GL. all budget
         * construction GL lines added by the budget construction application have an object type code of
         * FinObjTypeExpenditureexpCd, which we pass at run time as a parameter. we have an IN clause to check for other object
         * types which may have been loaded in the base from the general ledger. the request for such lines will not have this
         * object type.
         */
        sqlBuilder.append("INSERT INTO LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
        sqlBuilder.append(" FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT)\n");
        sqlBuilder.append("(SELECT ?, ?, ?, ?, ?,\n");
        sqlBuilder.append("LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd,\n");
        sqlBuilder.append(" '");
        // default sub object code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', '");
        // general ledger budget balance type code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', ");
        sqlBuilder.append("CA_OBJECT_CODE_T.fin_obj_typ_cd,\n");
        sqlBuilder.append("LD_BCN_BENEFITS_RECALC01_MT.fb_sum, 0\n");
        sqlBuilder.append("FROM LD_BCN_BENEFITS_RECALC01_MT,\n");
        sqlBuilder.append("     CA_OBJECT_CODE_T\n");
        sqlBuilder.append("WHERE (LD_BCN_BENEFITS_RECALC01_MT.sesid = ?)\n");
        sqlBuilder.append("  AND (CA_OBJECT_CODE_T.univ_fiscal_yr = ?)\n");
        sqlBuilder.append("  AND (CA_OBJECT_CODE_T.fin_coa_cd = ?)\n");
        sqlBuilder.append("  AND (CA_OBJECT_CODE_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd)\n");
        sqlBuilder.append("  AND (NOT EXISTS\n");
        sqlBuilder.append("(SELECT 1\n");
        sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append(" WHERE (LD_PND_BCNSTR_GL_T.fdoc_nbr = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.univ_fiscal_yr = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.account_nbr = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.sub_acct_nbr = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = '");
        // default sub object code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("')\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = '");
        // general ledger budget balance type code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("')\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_obj_typ_cd IN ");
        // expenditure object types
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("))))");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder, insertionPoints));
        sqlBuilder.delete(0, sqlBuilder.length());
        insertionPoints.clear();

        /********************************************
         * Added new statements to check labor benefit rate category code. Used only when
         * ENABLE_FRINGE_BENEFIT_CALC_BY_BENEFIT_RATE_CATEGORY_IND system parameter is set to "Y"
         ********************************************/
        /**
         * set the request to 0 for fringe benefits objects with base
         */
        sqlBuilder.append("UPDATE LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("SET ACLN_ANNL_BAL_AMT =0\n");
        sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("  AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("  AND (EXISTS (SELECT 1 FROM LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("               WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("                 AND (LD_BENEFITS_CALC_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
        sqlBuilder.append("                 AND (LD_BENEFITS_CALC_T.LBR_BEN_RT_CAT_CD = ?)\n");
        sqlBuilder.append("                 AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * sum the amounts in benefits-eligible objects and attach the appropriate benefits object code
         */
        sqlBuilder.append("INSERT INTO LD_BCN_BENEFITS_RECALC01_MT\n(SESID, POS_FRNGBEN_OBJ_CD, FB_SUM)\n");
        sqlBuilder.append("(SELECT ?,LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD,\n");
        sqlBuilder.append(" ROUND(SUM(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT * (LD_BENEFITS_CALC_T.POS_FRNG_BENE_PCT/100.0)),0)\n ");
        sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T,\n");
        sqlBuilder.append("      LD_LBR_OBJ_BENE_T,\n");
        sqlBuilder.append("      LD_BENEFITS_CALC_T\n");
        sqlBuilder.append(" WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT <> 0)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = LD_LBR_OBJ_BENE_T.UNIV_FISCAL_YR)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = LD_LBR_OBJ_BENE_T.fin_coa_cd)\n");
        sqlBuilder.append("   AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_LBR_OBJ_BENE_T.fin_object_cd)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.univ_fiscal_yr = LD_BENEFITS_CALC_T.univ_fiscal_yr)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.fin_coa_cd = LD_BENEFITS_CALC_T.fin_coa_cd)\n");
        sqlBuilder.append("   AND (LD_BENEFITS_CALC_T.LBR_BEN_RT_CAT_CD = ?)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.finobj_bene_typ_cd = LD_BENEFITS_CALC_T.pos_benefit_typ_cd)\n");
        sqlBuilder.append("   AND (LD_LBR_OBJ_BENE_T.actv_ind = ?)\n");
        sqlBuilder.append("   AND (LD_BENEFITS_CALC_T.actv_ind = ?)\n");
        sqlBuilder.append(" GROUP BY LD_BENEFITS_CALC_T.pos_frngben_obj_cd)");
        sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());


        /**
         * this is the SQL for the monthly budget benefits. any rounding amount is added to the amount for month 1
         */
        /**
         * Cleanup the rare case where annual request goes to zero with existing monthly buckets. This gives monthly calc benefits
         * problems from constraints since the annual benefit target row might be non-existent when it inserts the new results from
         * the left over monthly buckets This is usually the case since annual benefits are usually calculated first.
         */
        sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
        sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND NOT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt = 0) \n");
        sqlBuilder.append("  AND EXISTS\n");
        sqlBuilder.append("        (SELECT 1\n");
        sqlBuilder.append("         FROM LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("        WHERE LD_PND_BCNSTR_GL_T.fdoc_nbr = LD_BCNSTR_MONTH_T.fdoc_nbr\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.univ_fiscal_yr = LD_BCNSTR_MONTH_T.univ_fiscal_yr\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.fin_coa_cd = LD_BCNSTR_MONTH_T.fin_coa_cd\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.account_nbr = LD_BCNSTR_MONTH_T.account_nbr\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.sub_acct_nbr = LD_BCNSTR_MONTH_T.sub_acct_nbr\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCNSTR_MONTH_T.fin_object_cd\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = LD_BCNSTR_MONTH_T.fin_sub_obj_cd\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = LD_BCNSTR_MONTH_T.fin_balance_typ_cd\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.fin_obj_typ_cd = LD_BCNSTR_MONTH_T.fin_obj_typ_cd\n");
        sqlBuilder.append("          AND LD_PND_BCNSTR_GL_T.acln_annl_bal_amt = 0)");
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());


        /**
         * cleanup by deleting any existing monthly benefit recs
         */
        sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
        sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND EXISTS\n");
        sqlBuilder.append("        (SELECT 1\n");
        sqlBuilder.append("         FROM LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("        WHERE LD_BENEFITS_CALC_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("          AND LD_BENEFITS_CALC_T.fin_coa_cd = ?\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fin_object_cd = LD_BENEFITS_CALC_T.pos_frngben_obj_cd)");
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder));
        sqlBuilder.delete(0, sqlBuilder.length());
        /**
         * calc benefits for source objects and sum to target objects. all budget construction GL lines added by the budget
         * construction application have an object type code of FinObjTypeExpenditureexpCd, which we pass at run time as a
         * parameter. we have an IN clause to check for other object types which may have been loaded in the base from the general
         * ledger. the request for such lines will not have this object type.
         */
        sqlBuilder.append("INSERT INTO LD_BCNSTR_MONTH_T\n");
        sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
        sqlBuilder.append("FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT,\n");
        sqlBuilder.append(" FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT,\n");
        sqlBuilder.append(" FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT)\n");
        sqlBuilder.append("SELECT ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("   LD_BENEFITS_CALC_T.pos_frngben_obj_cd,");
        sqlBuilder.append(" '");
        // default sub object code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', '");
        // general ledger budget balance type code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', ");
        sqlBuilder.append("CA_OBJECT_CODE_T.fin_obj_typ_cd, \n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt * (LD_BENEFITS_CALC_T.pos_frng_bene_pct/100.0),0)),0)\n");
        sqlBuilder.append("FROM LD_BCNSTR_MONTH_T,\n");
        sqlBuilder.append("     LD_BENEFITS_CALC_T,\n");
        sqlBuilder.append("     LD_LBR_OBJ_BENE_T,\n");
        sqlBuilder.append("     CA_OBJECT_CODE_T\n");
        sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND NOT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt = 0\n");
        sqlBuilder.append("          AND LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt = 0) \n");
        sqlBuilder.append("  AND LD_LBR_OBJ_BENE_T.univ_fiscal_yr = LD_BCNSTR_MONTH_T.univ_fiscal_yr\n");
        sqlBuilder.append("  AND LD_LBR_OBJ_BENE_T.fin_coa_cd = LD_BCNSTR_MONTH_T.fin_coa_cd\n");
        sqlBuilder.append("  AND LD_LBR_OBJ_BENE_T.fin_object_cd = LD_BCNSTR_MONTH_T.fin_object_cd\n");
        sqlBuilder.append("  AND LD_LBR_OBJ_BENE_T.actv_ind = ?\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.univ_fiscal_yr = LD_LBR_OBJ_BENE_T.univ_fiscal_yr\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.fin_coa_cd = LD_LBR_OBJ_BENE_T.fin_coa_cd\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.pos_benefit_typ_cd = LD_LBR_OBJ_BENE_T.finobj_bene_typ_cd\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.univ_fiscal_yr = CA_OBJECT_CODE_T.univ_fiscal_yr\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.fin_coa_cd = CA_OBJECT_CODE_T.fin_coa_cd\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.pos_frngben_obj_cd = CA_OBJECT_CODE_T.fin_object_cd\n");
        sqlBuilder.append("  AND LD_BENEFITS_CALC_T.actv_ind = ?\n");
        sqlBuilder.append("GROUP BY LD_BENEFITS_CALC_T.pos_frngben_obj_cd, CA_OBJECT_CODE_T.fin_obj_typ_cd");
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder, insertionPoints));
        sqlBuilder.delete(0, sqlBuilder.length());
        insertionPoints.clear();


        /**
         * adjust the month 1 totals for rounding error
         */
        sqlBuilder.append("UPDATE LD_BCNSTR_MONTH_T\n");
        sqlBuilder.append("SET fdoc_ln_mo1_amt =\n");
        sqlBuilder.append("    (SELECT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt +\n");
        sqlBuilder.append("            (LD_PND_BCNSTR_GL_T.acln_annl_bal_amt -\n");
        sqlBuilder.append("            (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt +\n");
        sqlBuilder.append("             LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt +\n");
        sqlBuilder.append("             LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt +\n");
        sqlBuilder.append("             LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt +\n");
        sqlBuilder.append("             LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt +\n");
        sqlBuilder.append("             LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt)))\n");
        sqlBuilder.append("    FROM LD_PND_BCNSTR_GL_T\n");
        sqlBuilder.append("    WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = LD_PND_BCNSTR_GL_T.fdoc_nbr\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = LD_PND_BCNSTR_GL_T.univ_fiscal_yr\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.fin_coa_cd = LD_PND_BCNSTR_GL_T.fin_coa_cd\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.account_nbr = LD_PND_BCNSTR_GL_T.account_nbr\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.sub_acct_nbr = LD_PND_BCNSTR_GL_T.sub_acct_nbr\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.fin_object_cd = LD_PND_BCNSTR_GL_T.fin_object_cd\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.fin_sub_obj_cd = LD_PND_BCNSTR_GL_T.fin_sub_obj_cd\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.fin_balance_typ_cd = LD_PND_BCNSTR_GL_T.fin_balance_typ_cd\n");
        sqlBuilder.append("      AND LD_BCNSTR_MONTH_T.fin_obj_typ_cd = LD_PND_BCNSTR_GL_T.fin_obj_typ_cd)\n");
        sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
        sqlBuilder.append("  AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND EXISTS \n");
        sqlBuilder.append("        (SELECT 1\n");
        sqlBuilder.append("        FROM LD_BENEFITS_CALC_T\n");
        sqlBuilder.append("        WHERE LD_BENEFITS_CALC_T.univ_fiscal_yr = ?\n");
        sqlBuilder.append("          AND LD_BENEFITS_CALC_T.fin_coa_cd = ?\n");
        sqlBuilder.append("          AND LD_BENEFITS_CALC_T.pos_frngben_obj_cd = LD_BCNSTR_MONTH_T.fin_object_cd\n");
        sqlBuilder.append("          AND LD_BENEFITS_CALC_T.actv_ind = ?)\n");
        ;
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder));

        /********************************************
         * Added new statements to check labor benefit rate category code. Used only when
         * ENABLE_FRINGE_BENEFIT_CALC_BY_BENEFIT_RATE_CATEGORY_IND system parameter is set to "Y"
         ********************************************/
        sqlBuilder.delete(0, sqlBuilder.length());
        insertionPoints.clear();

        /**
         * calc benefits for source objects and sum to target objects. all budget construction GL lines added by the budget
         * construction application have an object type code of FinObjTypeExpenditureexpCd, which we pass at run time as a
         * parameter. we have an IN clause to check for other object types which may have been loaded in the base from the general
         * ledger. the request for such lines will not have this object type.
         */
        sqlBuilder.append("INSERT INTO ld_bcnstr_month_t\n");
        sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
        sqlBuilder.append("FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT,\n");
        sqlBuilder.append(" FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT,\n");
        sqlBuilder.append(" FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT)\n");
        sqlBuilder.append("SELECT ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("    ?,\n");
        sqlBuilder.append("   ld_benefits_calc_t.pos_frngben_obj_cd,");
        sqlBuilder.append(" '");
        // default sub object code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', '");
        // general ledger budget balance type code
        insertionPoints.add(sqlBuilder.length());
        sqlBuilder.append("', ");
        sqlBuilder.append("CA_OBJECT_CODE_T.fin_obj_typ_cd, \n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo1_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo2_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo3_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo4_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo5_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo6_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo7_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo8_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo9_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo10_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo11_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0),\n");
        sqlBuilder.append("   ROUND(SUM(COALESCE(ld_bcnstr_month_t.fdoc_ln_mo12_amt * (ld_benefits_calc_t.pos_frng_bene_pct/100.0),0)),0)\n");
        sqlBuilder.append("FROM ld_bcnstr_month_t,\n");
        sqlBuilder.append("     ld_benefits_calc_t,\n");
        sqlBuilder.append("     ld_lbr_obj_bene_t,\n");
        sqlBuilder.append("     CA_OBJECT_CODE_T\n");
        sqlBuilder.append("WHERE ld_bcnstr_month_t.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.account_nbr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND NOT (ld_bcnstr_month_t.fdoc_ln_mo1_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo2_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo3_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo4_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo5_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo6_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo7_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo8_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo9_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo10_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo11_amt = 0\n");
        sqlBuilder.append("          AND ld_bcnstr_month_t.fdoc_ln_mo12_amt = 0) \n");
        sqlBuilder.append("  AND ld_lbr_obj_bene_t.univ_fiscal_yr = ld_bcnstr_month_t.univ_fiscal_yr\n");
        sqlBuilder.append("  AND ld_lbr_obj_bene_t.fin_coa_cd = ld_bcnstr_month_t.fin_coa_cd\n");
        sqlBuilder.append("  AND ld_lbr_obj_bene_t.fin_object_cd = ld_bcnstr_month_t.fin_object_cd\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.univ_fiscal_yr = ld_lbr_obj_bene_t.univ_fiscal_yr\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.fin_coa_cd = ld_lbr_obj_bene_t.fin_coa_cd\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.pos_benefit_typ_cd = ld_lbr_obj_bene_t.finobj_bene_typ_cd\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.lbr_ben_rt_cat_cd = ?\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.univ_fiscal_yr = CA_OBJECT_CODE_T.univ_fiscal_yr\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.fin_coa_cd = CA_OBJECT_CODE_T.fin_coa_cd\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.pos_frngben_obj_cd = CA_OBJECT_CODE_T.fin_object_cd\n");
        sqlBuilder.append("  AND ld_lbr_obj_bene_t.actv_ind = ?\n");
        sqlBuilder.append("  AND ld_benefits_calc_t.actv_ind = ?\n");
        sqlBuilder.append("GROUP BY ld_benefits_calc_t.pos_frngben_obj_cd, CA_OBJECT_CODE_T.fin_obj_typ_cd");
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder, insertionPoints));
        sqlBuilder.delete(0, sqlBuilder.length());
        insertionPoints.clear();


        /**
         * adjust the month 1 totals for rounding error
         */
        sqlBuilder.append("UPDATE ld_bcnstr_month_t\n");
        sqlBuilder.append("SET fdoc_ln_mo1_amt =\n");
        sqlBuilder.append("    (SELECT (ld_bcnstr_month_t.fdoc_ln_mo1_amt +\n");
        sqlBuilder.append("            (ld_pnd_bcnstr_gl_t.acln_annl_bal_amt -\n");
        sqlBuilder.append("            (ld_bcnstr_month_t.fdoc_ln_mo1_amt + ld_bcnstr_month_t.fdoc_ln_mo2_amt +\n");
        sqlBuilder.append("             ld_bcnstr_month_t.fdoc_ln_mo3_amt + ld_bcnstr_month_t.fdoc_ln_mo4_amt +\n");
        sqlBuilder.append("             ld_bcnstr_month_t.fdoc_ln_mo5_amt + ld_bcnstr_month_t.fdoc_ln_mo6_amt +\n");
        sqlBuilder.append("             ld_bcnstr_month_t.fdoc_ln_mo7_amt + ld_bcnstr_month_t.fdoc_ln_mo8_amt +\n");
        sqlBuilder.append("             ld_bcnstr_month_t.fdoc_ln_mo9_amt + ld_bcnstr_month_t.fdoc_ln_mo10_amt +\n");
        sqlBuilder.append("             ld_bcnstr_month_t.fdoc_ln_mo11_amt + ld_bcnstr_month_t.fdoc_ln_mo12_amt)))\n");
        sqlBuilder.append("    FROM ld_pnd_bcnstr_gl_t\n");
        sqlBuilder.append("    WHERE ld_bcnstr_month_t.fdoc_nbr = ld_pnd_bcnstr_gl_t.fdoc_nbr\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.univ_fiscal_yr = ld_pnd_bcnstr_gl_t.univ_fiscal_yr\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.fin_coa_cd = ld_pnd_bcnstr_gl_t.fin_coa_cd\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.account_nbr = ld_pnd_bcnstr_gl_t.account_nbr\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.sub_acct_nbr = ld_pnd_bcnstr_gl_t.sub_acct_nbr\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.fin_object_cd = ld_pnd_bcnstr_gl_t.fin_object_cd\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.fin_sub_obj_cd = ld_pnd_bcnstr_gl_t.fin_sub_obj_cd\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.fin_balance_typ_cd = ld_pnd_bcnstr_gl_t.fin_balance_typ_cd\n");
        sqlBuilder.append("      AND ld_bcnstr_month_t.fin_obj_typ_cd = ld_pnd_bcnstr_gl_t.fin_obj_typ_cd)\n");
        sqlBuilder.append("WHERE ld_bcnstr_month_t.fdoc_nbr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.univ_fiscal_yr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.fin_coa_cd = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.account_nbr = ?\n");
        sqlBuilder.append("  AND ld_bcnstr_month_t.sub_acct_nbr = ?\n");
        sqlBuilder.append("  AND EXISTS \n");
        sqlBuilder.append("        (SELECT 1\n");
        sqlBuilder.append("        FROM ld_benefits_calc_t\n");
        sqlBuilder.append("        WHERE ld_benefits_calc_t.univ_fiscal_yr = ?\n");
        sqlBuilder.append("          AND ld_benefits_calc_t.fin_coa_cd = ?\n");
        sqlBuilder.append("          AND ld_benefits_calc_t.lbr_ben_rt_cat_cd = ?\n");
        sqlBuilder.append("          AND ld_benefits_calc_t.pos_frngben_obj_cd = ld_bcnstr_month_t.fin_object_cd\n");
        sqlBuilder.append("          AND ld_benefits_calc_t.actv_ind = ?)\n");
        sqlMonthlySteps.add(new SQLForStep(sqlBuilder));

    }


    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateAnnualBudgetConstructionGeneralLedgerBenefits(java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.ArrayList)
     */
    public void calculateAnnualBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd, String expenditureINList) {

        // original non rate category code version

        // the first thing to do is get the SQL IN list of expenditure object code types allowed in budget construction.
        // if this parameter is ill-formed, we can't calculate benefits. we will blow the user out of the water as a consequence.
        // if the benefits portion of budget construction is not in use at a particular site, then doing it this way will have no
        // impact.

        ArrayList<String> stringsToInsert = new ArrayList<String>();
        stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
        stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);
        stringsToInsert.add(expenditureINList);
        String idForSession = UUID.randomUUID().toString();

        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(2).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(3).getSQL(), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, KFSConstants.ACTIVE_INDICATOR, KFSConstants.ACTIVE_INDICATOR);
        // re-set general ledger amount for existing fringe benefits object codes
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(4).getSQL(stringsToInsert), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, idForSession);
        // insert general ledger lines for new fringe benefits object codes.
        stringsToInsert.add(2, stringsToInsert.get(0));
        stringsToInsert.add(3, stringsToInsert.get(1));
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(5).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, idForSession, fiscalYear, chartOfAccounts, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        clearTempTableBySesId("LD_BCN_BENEFITS_RECALC01_MT", "SESID", idForSession);
    }

    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateAnnualBudgetConstructionGeneralLedgerBenefits(java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.ArrayList, java.lang.String)
     */
    public void calculateAnnualBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd, String expenditureINList, String laborBenefitRateCategoryCode) {

        // new rate category code version

        // the first thing to do is get the SQL IN list of expenditure object code types allowed in budget construction.
        // if this parameter is ill-formed, we can't calculate benefits. we will blow the user out of the water as a consequence.
        // if the benefits portion of budget construction is not in use at a particular site, then doing it this way will have no
        // impact.

        ArrayList<String> stringsToInsert = new ArrayList<String>();
        stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
        stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);
        stringsToInsert.add(expenditureINList);
        String idForSession = UUID.randomUUID().toString();

        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(6).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, laborBenefitRateCategoryCode);
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(7).getSQL(), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, laborBenefitRateCategoryCode, KFSConstants.ACTIVE_INDICATOR, KFSConstants.ACTIVE_INDICATOR);
        // re-set general ledger amount for existing fringe benefits object codes
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(4).getSQL(stringsToInsert), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, idForSession);
        // insert general ledger lines for new fringe benefits object codes.
        stringsToInsert.add(2, stringsToInsert.get(0));
        stringsToInsert.add(3, stringsToInsert.get(1));
        getSimpleJdbcTemplate().update(sqlAnnualSteps.get(5).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, idForSession, fiscalYear, chartOfAccounts, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        clearTempTableBySesId("LD_BCN_BENEFITS_RECALC01_MT", "SESID", idForSession);
        /**
         * this is necessary to clear any rows for the tables we have just updated from the OJB cache. otherwise, subsequent calls
         * to OJB will fetch the old, unupdated cached rows.
         */
        // persistenceService.clearCache();
    }

    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateMonthlyBudgetConstructionGeneralLedgerBenefits(java.lang.String,
     * java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
     */
    public void calculateMonthlyBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd) {

        // original non rate category code version

        String idForSession = UUID.randomUUID().toString();

        ArrayList<String> stringsToInsert = new ArrayList<String>();
        stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
        stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);

        // get rid of monthly buckets for any rows with annual zero request
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        // get rid of existing monthly budgets for this key
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts);
        // spread the budgeted general ledger fringe beneftis amounts for this key equally into the twelve months
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(2).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, KFSConstants.ACTIVE_INDICATOR, KFSConstants.ACTIVE_INDICATOR);
        // add any rounding errors to the first month
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(3).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts, KFSConstants.ACTIVE_INDICATOR);
    }


    /**
     * @@ -420,6 +615,31 @@ persistenceService.clearCache(); } /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateMonthlyBudgetConstructionGeneralLedgerBenefits(java.lang.String,
     * java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
     * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateMonthlyBudgetConstructionGeneralLedgerBenefits(java.lang.String,
     * java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
     */
    public void calculateMonthlyBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd, String laborBenefitRateCategoryCode) {

        // new rate category code version

        String idForSession = UUID.randomUUID().toString();

        ArrayList<String> stringsToInsert = new ArrayList<String>();
        stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
        stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);

        // get rid of monthly buckets for any rows with annual zero request
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
        // get rid of existing monthly budgets for this key
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts);
        // spread the budgeted general ledger fringe beneftis amounts for this key equally into the twelve months
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(4).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, laborBenefitRateCategoryCode, KFSConstants.ACTIVE_INDICATOR, KFSConstants.ACTIVE_INDICATOR);
        // add any rounding errors to the first month
        getSimpleJdbcTemplate().update(sqlMonthlySteps.get(5).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts, laborBenefitRateCategoryCode, KFSConstants.ACTIVE_INDICATOR);
        /**
         * this is necessary to clear any rows for the tables we have just updated from the OJB cache. otherwise, subsequent calls
         * to OJB will fetch the old, unupdated cached rows.
         */
        //persistenceService.clearCache();
    }
}
