001 /*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.kfs.module.bc.document.dataaccess.impl;
017
018 import java.util.ArrayList;
019
020 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
021 import org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao;
022 import org.kuali.kfs.sys.KFSConstants;
023 import org.kuali.rice.kns.service.PersistenceService;
024 import org.kuali.rice.kns.util.Guid;
025
026 /**
027 *
028 * implements the SQL procedures to calculate benefits for the personnel object codes in the budget.
029 * apply the appropriate percentage to each object type in the general ledger, and split the result out into the monthly budget lines
030 * if monthly budgets exist for the accounting key.
031 */
032
033 public class BenefitsCalculationDaoJdbc extends BudgetConstructionDaoJdbcBase implements BenefitsCalculationDao {
034
035 private static ArrayList<SQLForStep> sqlAnnualSteps = new ArrayList<SQLForStep>(6);
036 private static ArrayList<SQLForStep> sqlMonthlySteps = new ArrayList<SQLForStep>(4);
037
038 private PersistenceService persistenceService;
039
040
041 /**
042 * these will be set to constant values in the constructor and used throughout SQL for the various steps.
043 */
044
045 public BenefitsCalculationDaoJdbc() {
046
047 //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.
048 //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.
049 StringBuilder sqlBuilder = new StringBuilder(2500);
050 ArrayList<Integer> insertionPoints = new ArrayList<Integer>();
051 /**
052 * this needs to be done before we can get rid of annual fringe benefits objects with no base.
053 * LD_BNCSTR_MNTH_T has an RI child constraint on LD_PND_BCNSTR_GL_T. So, before we eliminate any Budget Construction
054 * general ledger rows, we have to get rid of any dependent Budget Construction Monthly rows. If we call this set of
055 * queries to rebuild budgeted benefits for the general ledger, the next set of queries will also have to be called if
056 * monthly budgets exist. If no monthly budgets exist, the query below will not do anything. In that case, calling the
057 * Budget Construction general ledger benefits calculation routine without calling the monthly benefits calculation
058 * routine will be acceptable.
059 */
060 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
061 sqlBuilder.append("WHERE (LD_BCNSTR_MONTH_T.FDOC_NBR = ?)\n");
062 sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = ?)\n");
063 sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.FIN_COA_CD = ?)\n");
064 sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.ACCOUNT_NBR = ?)\n");
065 sqlBuilder.append("AND (LD_BCNSTR_MONTH_T.SUB_ACCT_NBR = ?)\n");
066 sqlBuilder.append("AND (EXISTS (SELECT 1\n");
067 sqlBuilder.append(" FROM (LD_PND_BCNSTR_GL_T INNER JOIN LD_BENEFITS_CALC_T\n");
068 sqlBuilder.append(" ON ((LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = LD_BENEFITS_CALC_T.UNIV_FISCAL_YR)\n");
069 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = LD_BENEFITS_CALC_T.FIN_COA_CD)\n");
070 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD = LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD)))\n");
071 sqlBuilder.append(" WHERE (LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
072 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.FDOC_NBR = LD_PND_BCNSTR_GL_T.FDOC_NBR)\n");
073 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
074 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.ACCOUNT_NBR = LD_PND_BCNSTR_GL_T.ACCOUNT_NBR)\n");
075 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.SUB_ACCT_NBR = LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR)\n");
076 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
077 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT = 0)))\n");
078 sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
079 sqlBuilder.delete(0,sqlBuilder.length());
080 /**
081 * get rid of fringe benefits objects with no base
082 */
083 sqlBuilder.append("DELETE FROM LD_PND_BCNSTR_GL_T\n");
084 sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
085 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR =?)\n ");
086 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
087 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
088 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
089 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT = 0)\n");
090 sqlBuilder.append(" AND (EXISTS (SELECT 1 FROM LD_BENEFITS_CALC_T\n");
091 sqlBuilder.append("WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
092 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
093 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))");
094 sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
095 sqlBuilder.delete(0, sqlBuilder.length());
096 /**
097 * set the request to 0 for fringe benefits objects with base
098 */
099 sqlBuilder.append("UPDATE LD_PND_BCNSTR_GL_T\n");
100 sqlBuilder.append("SET ACLN_ANNL_BAL_AMT =0\n");
101 sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
102 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
103 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
104 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
105 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
106 sqlBuilder.append(" AND (EXISTS (SELECT 1 FROM LD_BENEFITS_CALC_T\n");
107 sqlBuilder.append(" WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR)\n");
108 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.FIN_COA_CD = LD_PND_BCNSTR_GL_T.FIN_COA_CD)\n");
109 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))");
110 sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
111 sqlBuilder.delete(0, sqlBuilder.length());
112 /**
113 * sum the amounts in benefits-eligible objects and attach the appropriate benefits object code
114 */
115 sqlBuilder.append("INSERT INTO LD_BCN_BENEFITS_RECALC01_MT\n(SESID, POS_FRNGBEN_OBJ_CD, FB_SUM)\n");
116 sqlBuilder.append("(SELECT ?,LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD,\n");
117 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 ");
118 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T,\n");
119 sqlBuilder.append(" LD_LBR_OBJ_BENE_T,\n");
120 sqlBuilder.append(" LD_BENEFITS_CALC_T\n");
121 sqlBuilder.append(" WHERE (LD_PND_BCNSTR_GL_T.FDOC_NBR = ?)\n");
122 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = ?)\n");
123 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_COA_CD = ?)\n");
124 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.ACCOUNT_NBR = ?)\n");
125 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.SUB_ACCT_NBR = ?)\n");
126 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT <> 0)\n");
127 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.UNIV_FISCAL_YR = LD_LBR_OBJ_BENE_T.UNIV_FISCAL_YR)\n");
128 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = LD_LBR_OBJ_BENE_T.fin_coa_cd)\n");
129 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_LBR_OBJ_BENE_T.fin_object_cd)\n");
130 sqlBuilder.append(" AND (LD_LBR_OBJ_BENE_T.univ_fiscal_yr = LD_BENEFITS_CALC_T.univ_fiscal_yr)\n");
131 sqlBuilder.append(" AND (LD_LBR_OBJ_BENE_T.fin_coa_cd = LD_BENEFITS_CALC_T.fin_coa_cd)\n");
132 sqlBuilder.append(" AND (LD_LBR_OBJ_BENE_T.finobj_bene_typ_cd = LD_BENEFITS_CALC_T.pos_benefit_typ_cd)\n");
133 sqlBuilder.append(" GROUP BY LD_BENEFITS_CALC_T.pos_frngben_obj_cd)");
134 sqlAnnualSteps.add(new SQLForStep(sqlBuilder));
135 sqlBuilder.delete(0, sqlBuilder.length());
136 /**
137 * re-set the request amount for the appropriate benefits code
138 */
139 sqlBuilder.append("UPDATE LD_PND_BCNSTR_GL_T\n");
140 sqlBuilder.append("SET acln_annl_bal_amt =\n");
141 sqlBuilder.append(" (SELECT LD_BCN_BENEFITS_RECALC01_MT.fb_sum\n");
142 sqlBuilder.append(" FROM LD_BCN_BENEFITS_RECALC01_MT\n");
143 sqlBuilder.append(" WHERE (LD_BCN_BENEFITS_RECALC01_MT.sesid = ?)\n");
144 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd))\n");
145 sqlBuilder.append("WHERE (LD_PND_BCNSTR_GL_T.fdoc_nbr = ?)\n");
146 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.univ_fiscal_yr = ?)\n");
147 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = ?)\n");
148 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.account_nbr = ?)\n");
149 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.sub_acct_nbr = ?)\n");
150 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = '");
151 // default sub object code
152 insertionPoints.add(sqlBuilder.length());
153 sqlBuilder.append("')\n");
154 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = '");
155 // general ledger budget balance type code
156 insertionPoints.add(sqlBuilder.length());
157 sqlBuilder.append("')\n");
158 sqlBuilder.append(" AND EXISTS (SELECT 1\n");
159 sqlBuilder.append(" FROM LD_BCN_BENEFITS_RECALC01_MT\n");
160 sqlBuilder.append(" WHERE (sesid = ?)\n");
161 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd))\n");
162 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_obj_typ_cd IN ");
163 // expenditure object types
164 insertionPoints.add(sqlBuilder.length());
165 sqlBuilder.append(")");
166 sqlAnnualSteps.add(new SQLForStep(sqlBuilder,insertionPoints));
167 sqlBuilder.delete(0, sqlBuilder.length());
168 insertionPoints.clear();
169 /**
170 * 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.
171 *
172 */
173 sqlBuilder.append("INSERT INTO LD_PND_BCNSTR_GL_T\n");
174 sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
175 sqlBuilder.append(" FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT)\n");
176 sqlBuilder.append("(SELECT ?, ?, ?, ?, ?,\n");
177 sqlBuilder.append("LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd,\n");
178 sqlBuilder.append(" '");
179 // default sub object code
180 insertionPoints.add(sqlBuilder.length());
181 sqlBuilder.append("', '");
182 // general ledger budget balance type code
183 insertionPoints.add(sqlBuilder.length());
184 sqlBuilder.append("', ");
185 sqlBuilder.append("?, \n");
186 sqlBuilder.append("LD_BCN_BENEFITS_RECALC01_MT.fb_sum, 0\n");
187 sqlBuilder.append("FROM LD_BCN_BENEFITS_RECALC01_MT\n");
188 sqlBuilder.append("WHERE (LD_BCN_BENEFITS_RECALC01_MT.sesid = ?)\n");
189 sqlBuilder.append(" AND (NOT EXISTS\n");
190 sqlBuilder.append("(SELECT 1\n");
191 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
192 sqlBuilder.append(" WHERE (LD_PND_BCNSTR_GL_T.fdoc_nbr = ?)\n");
193 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.univ_fiscal_yr = ?)\n");
194 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_coa_cd = ?)\n");
195 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.account_nbr = ?)\n");
196 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.sub_acct_nbr = ?)\n");
197 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCN_BENEFITS_RECALC01_MT.pos_frngben_obj_cd)\n");
198 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = '");
199 // default sub object code
200 insertionPoints.add(sqlBuilder.length());
201 sqlBuilder.append("')\n");
202 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = '");
203 // general ledger budget balance type code
204 insertionPoints.add(sqlBuilder.length());
205 sqlBuilder.append("')\n");
206 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.fin_obj_typ_cd IN ");
207 // expenditure object types
208 insertionPoints.add(sqlBuilder.length());
209 sqlBuilder.append("))))");
210 sqlAnnualSteps.add(new SQLForStep(sqlBuilder,insertionPoints));
211 sqlBuilder.delete(0, sqlBuilder.length());
212 insertionPoints.clear();
213 /**
214 * this is the SQL for the monthly budget benefits. any rounding amount is added to the amount for month 1
215 */
216 /**
217 * Cleanup the rare case where annual request goes to zero with existing monthly buckets.
218 * This gives monthly calc benefits problems from constraints since the annual benefit target row
219 * might be non-existent when it inserts the new results from the left over monthly buckets
220 * This is usually the case since annual benefits are usually calculated first.
221 */
222 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
223 sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
224 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
225 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
226 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
227 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
228 sqlBuilder.append(" AND NOT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt = 0\n");
229 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt = 0\n");
230 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt = 0\n");
231 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt = 0\n");
232 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt = 0\n");
233 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt = 0\n");
234 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt = 0\n");
235 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt = 0\n");
236 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt = 0\n");
237 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt = 0\n");
238 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt = 0\n");
239 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt = 0) \n");
240 sqlBuilder.append(" AND EXISTS\n");
241 sqlBuilder.append(" (SELECT 1\n");
242 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
243 sqlBuilder.append(" WHERE LD_PND_BCNSTR_GL_T.fdoc_nbr = LD_BCNSTR_MONTH_T.fdoc_nbr\n");
244 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.univ_fiscal_yr = LD_BCNSTR_MONTH_T.univ_fiscal_yr\n");
245 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.fin_coa_cd = LD_BCNSTR_MONTH_T.fin_coa_cd\n");
246 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.account_nbr = LD_BCNSTR_MONTH_T.account_nbr\n");
247 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.sub_acct_nbr = LD_BCNSTR_MONTH_T.sub_acct_nbr\n");
248 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.fin_object_cd = LD_BCNSTR_MONTH_T.fin_object_cd\n");
249 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.fin_sub_obj_cd = LD_BCNSTR_MONTH_T.fin_sub_obj_cd\n");
250 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.fin_balance_typ_cd = LD_BCNSTR_MONTH_T.fin_balance_typ_cd\n");
251 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.fin_obj_typ_cd = LD_BCNSTR_MONTH_T.fin_obj_typ_cd\n");
252 sqlBuilder.append(" AND LD_PND_BCNSTR_GL_T.acln_annl_bal_amt = 0)");
253 sqlMonthlySteps.add(new SQLForStep(sqlBuilder));
254 sqlBuilder.delete(0, sqlBuilder.length());
255
256
257 /**
258 * cleanup by deleting any existing monthly benefit recs
259 */
260 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
261 sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
262 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
263 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
264 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
265 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
266 sqlBuilder.append(" AND EXISTS\n");
267 sqlBuilder.append(" (SELECT 1\n");
268 sqlBuilder.append(" FROM LD_BENEFITS_CALC_T\n");
269 sqlBuilder.append(" WHERE LD_BENEFITS_CALC_T.univ_fiscal_yr = ?\n");
270 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.fin_coa_cd = ?\n");
271 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_object_cd = LD_BENEFITS_CALC_T.pos_frngben_obj_cd)");
272 sqlMonthlySteps.add(new SQLForStep(sqlBuilder));
273 sqlBuilder.delete(0, sqlBuilder.length());
274 /**
275 * 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.
276 */
277 sqlBuilder.append("INSERT INTO LD_BCNSTR_MONTH_T\n");
278 sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
279 sqlBuilder.append("FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT,\n");
280 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");
281 sqlBuilder.append(" FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT)\n");
282 sqlBuilder.append("SELECT ?,\n");
283 sqlBuilder.append(" ?,\n");
284 sqlBuilder.append(" ?,\n");
285 sqlBuilder.append(" ?,\n");
286 sqlBuilder.append(" ?,\n");
287 sqlBuilder.append(" LD_BENEFITS_CALC_T.pos_frngben_obj_cd,");
288 sqlBuilder.append(" '");
289 // default sub object code
290 insertionPoints.add(sqlBuilder.length());
291 sqlBuilder.append("', '");
292 // general ledger budget balance type code
293 insertionPoints.add(sqlBuilder.length());
294 sqlBuilder.append("', ");
295 sqlBuilder.append("?, \n");
296 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");
297 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");
298 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");
299 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");
300 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");
301 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");
302 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");
303 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");
304 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");
305 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");
306 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");
307 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");
308 sqlBuilder.append("FROM LD_BCNSTR_MONTH_T,\n");
309 sqlBuilder.append(" LD_BENEFITS_CALC_T,\n");
310 sqlBuilder.append(" LD_LBR_OBJ_BENE_T\n");
311 sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
312 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
313 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
314 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
315 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
316 sqlBuilder.append(" AND NOT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt = 0\n");
317 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt = 0\n");
318 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt = 0\n");
319 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt = 0\n");
320 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt = 0\n");
321 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt = 0\n");
322 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt = 0\n");
323 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt = 0\n");
324 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt = 0\n");
325 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt = 0\n");
326 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt = 0\n");
327 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt = 0) \n");
328 sqlBuilder.append(" AND LD_LBR_OBJ_BENE_T.univ_fiscal_yr = LD_BCNSTR_MONTH_T.univ_fiscal_yr\n");
329 sqlBuilder.append(" AND LD_LBR_OBJ_BENE_T.fin_coa_cd = LD_BCNSTR_MONTH_T.fin_coa_cd\n");
330 sqlBuilder.append(" AND LD_LBR_OBJ_BENE_T.fin_object_cd = LD_BCNSTR_MONTH_T.fin_object_cd\n");
331 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.univ_fiscal_yr = LD_LBR_OBJ_BENE_T.univ_fiscal_yr\n");
332 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.fin_coa_cd = LD_LBR_OBJ_BENE_T.fin_coa_cd\n");
333 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.pos_benefit_typ_cd = LD_LBR_OBJ_BENE_T.finobj_bene_typ_cd\n");
334 sqlBuilder.append("GROUP BY LD_BENEFITS_CALC_T.pos_frngben_obj_cd");
335 sqlMonthlySteps.add(new SQLForStep(sqlBuilder,insertionPoints));
336 sqlBuilder.delete(0, sqlBuilder.length());
337 insertionPoints.clear();
338
339
340 /**
341 * adjust the month 1 totals for rounding error
342 */
343 sqlBuilder.append("UPDATE LD_BCNSTR_MONTH_T\n");
344 sqlBuilder.append("SET fdoc_ln_mo1_amt =\n");
345 sqlBuilder.append(" (SELECT (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt +\n");
346 sqlBuilder.append(" (LD_PND_BCNSTR_GL_T.acln_annl_bal_amt -\n");
347 sqlBuilder.append(" (LD_BCNSTR_MONTH_T.fdoc_ln_mo1_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo2_amt +\n");
348 sqlBuilder.append(" LD_BCNSTR_MONTH_T.fdoc_ln_mo3_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo4_amt +\n");
349 sqlBuilder.append(" LD_BCNSTR_MONTH_T.fdoc_ln_mo5_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo6_amt +\n");
350 sqlBuilder.append(" LD_BCNSTR_MONTH_T.fdoc_ln_mo7_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo8_amt +\n");
351 sqlBuilder.append(" LD_BCNSTR_MONTH_T.fdoc_ln_mo9_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo10_amt +\n");
352 sqlBuilder.append(" LD_BCNSTR_MONTH_T.fdoc_ln_mo11_amt + LD_BCNSTR_MONTH_T.fdoc_ln_mo12_amt)))\n");
353 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
354 sqlBuilder.append(" WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = LD_PND_BCNSTR_GL_T.fdoc_nbr\n");
355 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = LD_PND_BCNSTR_GL_T.univ_fiscal_yr\n");
356 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_coa_cd = LD_PND_BCNSTR_GL_T.fin_coa_cd\n");
357 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.account_nbr = LD_PND_BCNSTR_GL_T.account_nbr\n");
358 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.sub_acct_nbr = LD_PND_BCNSTR_GL_T.sub_acct_nbr\n");
359 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_object_cd = LD_PND_BCNSTR_GL_T.fin_object_cd\n");
360 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_sub_obj_cd = LD_PND_BCNSTR_GL_T.fin_sub_obj_cd\n");
361 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_balance_typ_cd = LD_PND_BCNSTR_GL_T.fin_balance_typ_cd\n");
362 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_obj_typ_cd = LD_PND_BCNSTR_GL_T.fin_obj_typ_cd)\n");
363 sqlBuilder.append("WHERE LD_BCNSTR_MONTH_T.fdoc_nbr = ?\n");
364 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.univ_fiscal_yr = ?\n");
365 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.fin_coa_cd = ?\n");
366 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.account_nbr = ?\n");
367 sqlBuilder.append(" AND LD_BCNSTR_MONTH_T.sub_acct_nbr = ?\n");
368 sqlBuilder.append(" AND EXISTS \n");
369 sqlBuilder.append(" (SELECT 1\n");
370 sqlBuilder.append(" FROM LD_BENEFITS_CALC_T\n");
371 sqlBuilder.append(" WHERE LD_BENEFITS_CALC_T.univ_fiscal_yr = ?\n");
372 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.fin_coa_cd = ?\n");
373 sqlBuilder.append(" AND LD_BENEFITS_CALC_T.pos_frngben_obj_cd = LD_BCNSTR_MONTH_T.fin_object_cd)\n");
374 ;
375 sqlMonthlySteps.add(new SQLForStep(sqlBuilder));
376 }
377
378 /**
379 * @see org.kuali.kfs.module.bc.document.dataaccess.BenefitsCalculationDao#calculateAnnualBudgetConstructionGeneralLedgerBenefits(String, Integer, String, String, String, String)
380 */
381 public void calculateAnnualBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd) {
382
383 // the first thing to do is get the SQL IN list of expenditure object code types allowed in budget construction.
384 // if this parameter is ill-formed, we can't calculate benefits. we will blow the user out of the water as a consequence.
385 // if the benefits portion of budget construction is not in use at a particular site, then doing it this way will have no impact.
386
387 ArrayList<String> stringsToInsert = new ArrayList<String>();
388 stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
389 stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);
390 stringsToInsert.add(getExpenditureINList());
391 String idForSession = (new Guid()).toString();
392
393 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
394 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
395 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(2).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
396 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(3).getSQL(), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
397 // re-set general ledger amount for existing fringe benefits object codes
398 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(4).getSQL(stringsToInsert), idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, idForSession);
399 // insert general ledger lines for new fringe benefits object codes.
400 stringsToInsert.add(2,stringsToInsert.get(0));
401 stringsToInsert.add(3,stringsToInsert.get(1));
402 getSimpleJdbcTemplate().update(sqlAnnualSteps.get(5).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, finObjTypeExpenditureexpCd, idForSession, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
403 clearTempTableBySesId("LD_BCN_BENEFITS_RECALC01_MT", "SESID", idForSession);
404 /**
405 * 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.
406 */
407 persistenceService.clearCache();
408 }
409
410 /**
411 *
412 * @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)
413 */
414 public void calculateMonthlyBudgetConstructionGeneralLedgerBenefits(String documentNumber, Integer fiscalYear, String chartOfAccounts, String accountNumber, String subAccountNumber, String finObjTypeExpenditureexpCd) {
415 String idForSession = (new Guid()).toString();
416
417 ArrayList<String> stringsToInsert = new ArrayList<String>();
418 stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
419 stringsToInsert.add(KFSConstants.BALANCE_TYPE_BASE_BUDGET);
420
421 // get rid of monthly buckets for any rows with annual zero request
422 getSimpleJdbcTemplate().update(sqlMonthlySteps.get(0).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
423 // get rid of existing monthly budgets for this key
424 getSimpleJdbcTemplate().update(sqlMonthlySteps.get(1).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts);
425 // spread the budgeted general ledger fringe beneftis amounts for this key equally into the twelve months
426 getSimpleJdbcTemplate().update(sqlMonthlySteps.get(2).getSQL(stringsToInsert), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, finObjTypeExpenditureexpCd, documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber);
427 // add any rounding errors to the first month
428 getSimpleJdbcTemplate().update(sqlMonthlySteps.get(3).getSQL(), documentNumber, fiscalYear, chartOfAccounts, accountNumber, subAccountNumber, fiscalYear, chartOfAccounts);
429 /**
430 * 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.
431 */
432 persistenceService.clearCache();
433 }
434
435 public void setPersistenceService(PersistenceService persistenceService)
436 {
437 this.persistenceService = persistenceService;
438 }
439 }