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.math.BigDecimal;
019 import java.util.ArrayList;
020
021 import org.kuali.kfs.module.bc.BCConstants;
022 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
023 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSalarySummaryReportDao;
024 import org.kuali.rice.kns.service.PersistenceService;
025 import org.kuali.rice.kns.util.Guid;
026 import org.kuali.rice.kns.util.KualiDecimal;
027
028 /**
029 * builds the underlying data table for the salary summary report in budget construction
030 */
031
032 public class BudgetConstructionSalarySummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionSalarySummaryReportDao {
033 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionSalarySummaryReportDaoJdbc.class);
034
035 private static ArrayList<SQLForStep> updateReportsSalarySummaryThreshold = new ArrayList<SQLForStep>(7);
036 private static ArrayList<SQLForStep> salarySummaryAboveThreshold = new ArrayList<SQLForStep>(1);
037 private static ArrayList<SQLForStep> salarySummaryBelowThreshold = new ArrayList<SQLForStep>(1);
038 private static ArrayList<SQLForStep> updateReportsSalarySummaryNoThresholdReason = new ArrayList<SQLForStep>(1);
039 private static ArrayList<SQLForStep> updateReportsSalarySummaryNoThresholdNoReason = new ArrayList<SQLForStep>(1);
040 private static ArrayList<SQLForStep> updateReportsSalarySummaryCommon = new ArrayList<SQLForStep>(2);
041
042 private PersistenceService persistenceService;
043
044 public BudgetConstructionSalarySummaryReportDaoJdbc() {
045
046 // builds and updates SalarySummaryReports
047
048 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
049 StringBuilder sqlText = new StringBuilder(1500);
050
051 /* get no leave bcaf, bcsf and posn info first */
052 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM01_MT \n");
053 sqlText.append(" (SESID, EMPLID, POSITION_NBR, SAL_AMT, SAL_PCT, SAL_MTHS, POS_CSF_AMT, POS_CSF_TM_PCT, SAL_PMTHS) \n");
054 sqlText.append("SELECT ?, bcaf.emplid, bcaf.position_nbr, bcaf.appt_rqst_amt, bcaf.appt_rqst_tm_pct, bcaf.appt_fnd_mo, bcsf.pos_csf_amt, bcsf.pos_csf_tm_pct, posn.iu_pay_months \n");
055 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf \n");
056 sqlText.append(" ON ((bcaf.fin_coa_cd = bcsf.fin_coa_cd) AND (bcaf.account_nbr = bcsf.account_nbr) \n");
057 sqlText.append(" AND(bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) AND (bcaf.fin_object_cd = bcsf.fin_object_cd) \n");
058 sqlText.append(" AND(bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) AND (bcaf.position_nbr = bcsf.position_nbr)\n");
059 sqlText.append(" AND(bcaf.emplid = bcsf.emplid) AND (bcaf.univ_fiscal_yr= bcsf.univ_fiscal_yr))),\n");
060 sqlText.append(" LD_BCN_POS_T posn, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick\n");
061 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
062 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
063 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
064 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
065 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
066 sqlText.append(" AND bcaf.emplid <> '");
067 // empolyee ID for a vacant line in budget construction CSF and appointment funding
068 insertionPoints.add(sqlText.length());
069 sqlText.append("' \n");
070 sqlText.append(" AND bcaf.appt_fnd_dur_cd = '");
071 // default appointment funding duration code
072 insertionPoints.add(sqlText.length());
073 sqlText.append("' \n");
074 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
075 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
076 sqlText.append(" AND pick.select_flag > 0 \n");
077 sqlText.append(" AND bcaf.univ_fiscal_yr = posn.univ_fiscal_yr \n");
078 sqlText.append(" AND bcaf.position_nbr = posn.position_nbr \n");
079
080 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText,insertionPoints));
081 sqlText.delete(0, sqlText.length());
082 insertionPoints.clear();
083
084 /* get leave flagged bcaf, bcsf and posn info first */
085 /* uses leave related info from bcaf, etc */
086 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM01_MT \n");
087 sqlText.append("(SESID, EMPLID, POSITION_NBR, SAL_AMT, SAL_PCT, SAL_MTHS, \n");
088 sqlText.append(" POS_CSF_AMT, POS_CSF_TM_PCT, SAL_PMTHS) \n");
089 sqlText.append("SELECT ?, bcaf.emplid, bcaf.position_nbr, bcaf.appt_rqst_csf_amt, bcaf.appt_rqcsf_tm_pct, posn.iu_norm_work_months, \n");
090 sqlText.append(" bcsf.pos_csf_amt, bcsf.pos_csf_tm_pct, posn.iu_pay_months \n");
091 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf \n");
092 sqlText.append(" ON ((bcaf.fin_coa_cd = bcsf.fin_coa_cd) AND (bcaf.account_nbr = bcsf.account_nbr) \n");
093 sqlText.append("AND (bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) AND (bcaf.fin_object_cd = bcsf.fin_object_cd) \n");
094 sqlText.append("AND (bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) AND (bcaf.position_nbr = bcsf.position_nbr) \n");
095 sqlText.append("AND (bcaf.emplid = bcsf.emplid) AND (bcaf.univ_fiscal_yr= bcsf.univ_fiscal_yr))), LD_BCN_POS_T posn, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick \n");
096 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
097 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
098 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
099 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
100 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
101 sqlText.append(" AND bcaf.emplid <> '\n");
102 // empolyee ID for a vacant line in budget construction CSF and appointment funding
103 insertionPoints.add(sqlText.length());
104 sqlText.append("' \n");
105 sqlText.append(" AND bcaf.appt_fnd_dur_cd <> '");
106 // defualt appointment funding duration code
107 insertionPoints.add(sqlText.length());
108 sqlText.append("' \n");
109 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
110 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
111 sqlText.append(" AND pick.select_flag > 0 \n");
112 sqlText.append(" AND bcaf.univ_fiscal_yr = posn.univ_fiscal_yr \n");
113 sqlText.append(" AND bcaf.position_nbr = posn.position_nbr \n");
114
115 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText,insertionPoints));
116 sqlText.delete(0, sqlText.length());
117 insertionPoints.clear();
118
119 /* for each emplid, find the record with the largest salary (break ties by taking the row with the smallest position number) */
120 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM02_MT \n");
121 sqlText.append("(SESID, EMPLID, SAL_MTHS, SAL_PMTHS) \n");
122 sqlText.append("SELECT DISTINCT ?, sd.emplid, sd.sal_mths, sd.sal_pmths \n");
123 sqlText.append("FROM LD_BCN_BUILD_SALSUMM01_MT sd \n");
124 sqlText.append("WHERE sesid = ? \n");
125 sqlText.append("AND sd.sal_amt = (SELECT max(sd2.sal_amt) \n");
126 sqlText.append(" FROM LD_BCN_BUILD_SALSUMM01_MT sd2\n");
127 sqlText.append(" WHERE sd2.sesid = sd.sesid AND sd2.emplid = sd.emplid)\n");
128 sqlText.append("AND sd.position_nbr = (SELECT min(sd3.position_nbr) \n");
129 sqlText.append(" FROM LD_BCN_BUILD_SALSUMM01_MT sd3\n");
130 sqlText.append(" WHERE sd3.sesid = sd.sesid \n");
131 sqlText.append(" AND sd3.emplid = sd.emplid AND sd3.sal_amt = sd.sal_amt) \n");
132
133 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText));
134 sqlText.delete(0, sqlText.length());
135
136 /* for each emplid, find the CSF from the previous year with the largest salary (break ties by taking the row with the smallest position number */
137 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM03_MT\n");
138 sqlText.append("(SESID, EMPLID, CSF_MTHS, CSF_PMTHS) \n");
139 sqlText.append("SELECT DISTINCT ?, sd.emplid, p.iu_norm_work_months, p.iu_pay_months \n");
140 sqlText.append("FROM LD_BCN_BUILD_SALSUMM01_MT sd, LD_BCN_POS_T p \n");
141 sqlText.append("WHERE sesid = ? \n");
142 sqlText.append(" AND sd.pos_csf_amt = (SELECT max(sd2.pos_csf_amt) \n");
143 sqlText.append(" FROM LD_BCN_BUILD_SALSUMM01_MT sd2\n");
144 sqlText.append(" WHERE sd2.sesid = sd.sesid AND sd2.emplid = sd.emplid) \n");
145 sqlText.append(" AND sd.position_nbr = (SELECT min(sd3.position_nbr) \n");
146 sqlText.append(" FROM LD_BCN_BUILD_SALSUMM01_MT sd3\n");
147 sqlText.append(" WHERE sd3.sesid = sd.sesid AND sd3.emplid = sd.emplid AND sd3.pos_csf_amt = sd.pos_csf_amt) \n");
148 sqlText.append(" AND p.univ_fiscal_yr = ? AND p.position_nbr = sd.position_nbr \n");
149
150 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText));
151 sqlText.delete(0, sqlText.length());
152
153 /* merge the sal max,csf max info and sums to one table */
154 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM04_MT \n");
155 sqlText.append("(SESID, EMPLID, POS_CSF_AMT, RES_CSF_AMT, POS_CSF_TM_PCT, \n");
156 sqlText.append(" SAL_AMT, SAL_PCT, SAL_MTHS, SAL_PMTHS, CSF_MTHS, CSF_PMTHS) \n");
157 sqlText.append("SELECT ?, sm.emplid, SUM(COALESCE(sd.pos_csf_amt,0)), 0, SUM(COALESCE(sd.pos_csf_tm_pct,0)), \n");
158 sqlText.append(" SUM(COALESCE(sd.sal_amt,0)), SUM(COALESCE(sd.sal_pct,0)), sm.sal_mths, sm.sal_pmths, COALESCE(cm.csf_mths,0), COALESCE(cm.csf_pmths,0) \n");
159 sqlText.append("FROM (LD_BCN_BUILD_SALSUMM02_MT sm LEFT OUTER JOIN LD_BCN_BUILD_SALSUMM03_MT cm \n");
160 sqlText.append(" ON ((sm.sesid = cm.sesid) AND (sm.emplid = cm.emplid))),\n");
161 sqlText.append(" LD_BCN_BUILD_SALSUMM01_MT sd \n");
162 sqlText.append("WHERE sm.sesid = ? \n");
163 sqlText.append(" AND sd.sesid = sm.sesid \n");
164 sqlText.append(" AND sd.emplid = sm.emplid \n");
165 sqlText.append("GROUP BY sm.emplid, sm.sal_mths, sm.sal_pmths, cm.csf_mths, cm.csf_pmths \n");
166
167 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText));
168 sqlText.delete(0, sqlText.length());
169
170 /* restate the csf for all records, adjusting it so that it reflects changes in months appointment and percent time. */
171 /* the adjustment factor is (req pct time/base pct time)(req mnths appt/req position mnths appt)/(base mnths appt)/(base position mnths appt)*/
172 sqlText.append("UPDATE LD_BCN_BUILD_SALSUMM04_MT \n");
173 sqlText.append("SET res_csf_amt = ROUND(COALESCE(((pos_csf_amt * sal_pct * sal_mths * csf_pmths) \n");
174 sqlText.append(" / (pos_csf_tm_pct * csf_mths * sal_pmths)), 0.00),0) \n");
175 sqlText.append("WHERE sesid = ? AND pos_csf_tm_pct <> 0 AND csf_mths <> 0 AND sal_pmths <> 0 \n");
176
177 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText));
178 sqlText.delete(0, sqlText.length());
179
180 /* restate the csf amt for change in fte scale */
181 sqlText.append("UPDATE LD_BCN_BUILD_SALSUMM04_MT \n");
182 sqlText.append("SET res_csf_amt = ROUND(COALESCE(((res_csf_amt * sal_pmths) / csf_pmths), 0.00),0) \n");
183 sqlText.append("WHERE sesid = ? AND sal_pmths <> csf_pmths AND csf_pmths <> 0 \n");
184
185 updateReportsSalarySummaryThreshold.add(new SQLForStep(sqlText));
186 sqlText.delete(0, sqlText.length());
187
188 /* produce emplid set for recs >= threshold */
189 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM05_MT \n");
190 sqlText.append("(SESID, EMPLID) \n");
191 sqlText.append("SELECT ?, emplid \n");
192 sqlText.append("FROM LD_BCN_BUILD_SALSUMM04_MT \n");
193 sqlText.append("WHERE sesid = ? \n");
194 sqlText.append(" AND ROUND((((sal_amt - res_csf_amt) / res_csf_amt) * 100),1) >= ? \n");
195 sqlText.append(" AND res_csf_amt <> 0 \n");
196 sqlText.append(" AND sal_amt <> 0 \n");
197
198 salarySummaryAboveThreshold.add(new SQLForStep(sqlText));
199 sqlText.delete(0, sqlText.length());
200
201 /* produce emplid set for recs <= threshold */
202 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM05_MT \n");
203 sqlText.append("(SESID, EMPLID) \n");
204 sqlText.append("SELECT ?, emplid \n");
205 sqlText.append("FROM LD_BCN_BUILD_SALSUMM04_MT \n");
206 sqlText.append("WHERE sesid = ? \n");
207 sqlText.append(" AND ROUND((((sal_amt - res_csf_amt) / res_csf_amt) * 100),1) <= ? \n");
208 sqlText.append(" AND res_csf_amt <> 0 \n");
209 sqlText.append(" AND sal_amt <> 0 \n");
210
211 salarySummaryBelowThreshold.add(new SQLForStep(sqlText));
212 sqlText.delete(0, sqlText.length());
213
214
215 /* get EMPLIDs with at least one reason rec from the list of select reasons */
216 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM05_MT\n");
217 sqlText.append("(SESID, EMPLID) \n");
218 sqlText.append("SELECT DISTINCT ?, bcaf.emplid \n");
219 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick, LD_BCN_AF_REASON_T reas, LD_BCN_RSN_CD_PK_T rpk \n");
220 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
221 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
222 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
223 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
224 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
225 sqlText.append(" AND bcaf.emplid <> '");
226 // empolyee ID for a vacant line in budget construction CSF and appointment funding
227 insertionPoints.add(sqlText.length());
228 sqlText.append("' \n");
229 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
230 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
231 sqlText.append(" AND pick.select_flag > 0 \n");
232 sqlText.append(" AND bcaf.univ_fiscal_yr = reas.univ_fiscal_yr \n");
233 sqlText.append(" AND bcaf.fin_coa_cd = reas.fin_coa_cd \n");
234 sqlText.append(" AND bcaf.account_nbr = reas.account_nbr \n");
235 sqlText.append(" AND bcaf.sub_acct_nbr = reas.sub_acct_nbr \n");
236 sqlText.append(" AND bcaf.fin_object_cd = reas.fin_object_cd \n");
237 sqlText.append(" AND bcaf.fin_sub_obj_cd = reas.fin_sub_obj_cd \n");
238 sqlText.append(" AND bcaf.position_nbr = reas.position_nbr \n");
239 sqlText.append(" AND bcaf.emplid = reas.emplid \n");
240 sqlText.append(" AND reas.appt_fnd_reason_cd = rpk.appt_fnd_reason_cd \n");
241 sqlText.append(" AND rpk.person_unvl_id = ctrl.person_unvl_id \n");
242 sqlText.append(" AND rpk.select_flag <> 0 \n");
243
244 updateReportsSalarySummaryNoThresholdReason.add(new SQLForStep(sqlText,insertionPoints));
245 sqlText.delete(0, sqlText.length());
246 insertionPoints.clear();
247
248 /* get all EMPLIDs for the selection */
249 sqlText.append("INSERT INTO LD_BCN_BUILD_SALSUMM05_MT \n");
250 sqlText.append("(SESID, EMPLID) \n");
251 sqlText.append("SELECT DISTINCT ?, bcaf.emplid \n");
252 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick \n");
253 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
254 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
255 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
256 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
257 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
258 sqlText.append(" AND bcaf.emplid <> '");
259 // empolyee ID for a vacant line in budget construction CSF and appointment funding
260 insertionPoints.add(sqlText.length());
261 sqlText.append("' \n");
262 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
263 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
264 sqlText.append(" AND pick.select_flag > 0 \n");
265
266 updateReportsSalarySummaryNoThresholdNoReason.add(new SQLForStep(sqlText,insertionPoints));
267 sqlText.delete(0, sqlText.length());
268 insertionPoints.clear();
269
270 /* these are the two common driving SQL statements for all the reports */
271
272 /* get the name recs for the set of EMPLIDs */
273 sqlText.append("INSERT INTO LD_BCN_SAL_SSN_T \n");
274 sqlText.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, PERSON_NM, EMPLID)");
275 sqlText.append("SELECT DISTINCT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, iinc.person_nm, bcaf.emplid \n");
276 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_BUILD_SALSUMM05_MT tssn, LD_BCN_OBJ_PICK_T pick, LD_BCN_INTINCBNT_T iinc \n");
277 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
278 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
279 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
280 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
281 sqlText.append(" AND bcaf.emplid = iinc.emplid \n");
282 sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
283 sqlText.append(" AND tssn.sesid = ? \n");
284 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
285 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
286 sqlText.append(" AND pick.select_flag > 0 \n");
287
288 updateReportsSalarySummaryCommon.add(new SQLForStep(sqlText));
289 sqlText.delete(0, sqlText.length());
290
291 /* get the detail recs for the set of EMPLIDs */
292 sqlText.append("INSERT INTO LD_BCN_SAL_FND_T \n");
293 sqlText.append("(PERSON_UNVL_ID, EMPLID, POSITION_NBR, UNIV_FISCAL_YR, FIN_COA_CD, \n");
294 sqlText.append(" ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
295 sqlText.append("SELECT DISTINCT ?, bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr, bcaf.fin_coa_cd, \n");
296 sqlText.append(" bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
297 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_BUILD_SALSUMM05_MT tssn, LD_BCN_OBJ_PICK_T pick \n");
298 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
299 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
300 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
301 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
302 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
303 sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
304 sqlText.append(" AND tssn.sesid = ? \n");
305 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
306 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
307 sqlText.append(" AND pick.select_flag > 0 \n");
308
309 updateReportsSalarySummaryCommon.add(new SQLForStep(sqlText));
310 sqlText.delete(0, sqlText.length());
311
312 }
313
314 /**
315 *
316 * clean out all rows in the report tables associated with this user
317 * @param principalName--the user requesting the report
318 */
319 protected void clearUserPreviouSalarySummaryReports(String principalName) {
320 this.clearTempTableByUnvlId("LD_BCN_SAL_SSN_T", "PERSON_UNVL_ID", principalName);
321 this.clearTempTableByUnvlId("LD_BCN_SAL_FND_T", "PERSON_UNVL_ID", principalName);
322 }
323
324 /**
325 *
326 * clean out the work table used by all reports
327 * @param idForSession--the session which requested the report
328 */
329 protected void clearCommonWorkTable(String idForSession) {
330 this.clearTempTableBySesId("LD_BCN_BUILD_SALSUMM05_MT", "SESID", idForSession);
331 }
332
333 /**
334 *
335 * clean out the work tables for reporting by threshold
336 * @param idForSession--the session which requested the report
337 */
338 protected void clearThresholdWorkTables(String idForSession) {
339 this.clearTempTableBySesId("LD_BCN_BUILD_SALSUMM01_MT", "SESID", idForSession);
340 this.clearTempTableBySesId("LD_BCN_BUILD_SALSUMM02_MT", "SESID", idForSession);
341 this.clearTempTableBySesId("LD_BCN_BUILD_SALSUMM03_MT", "SESID", idForSession);
342 this.clearTempTableBySesId("LD_BCN_BUILD_SALSUMM04_MT", "SESID", idForSession);
343 }
344
345 /**
346 *
347 * runs SQL used by every report
348 * @param principalName--the user requesting the report
349 * @param idForSession--the session of the user
350 */
351 protected void runCommonSQLForSalaryReports(String principalName, String idForSession) {
352 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(0).getSQL(), principalName, principalName, idForSession);
353 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(1).getSQL(), principalName, principalName, idForSession);
354 clearCommonWorkTable(idForSession);
355 }
356
357 /**
358 *
359 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSalarySummaryReportDao#salarySummaryReports(java.lang.String, java.lang.Integer, boolean, org.kuali.rice.kns.util.KualiDecimal)
360 */
361 public void updateSalaryAndReasonSummaryReportsWithThreshold(String principalName, Integer previousFiscalYear, boolean reportGreaterThanOrEqualToThreshold, KualiDecimal threshold) {
362 // get the session ID
363 Guid guid = new Guid();
364 String idForSession = guid.toString();
365
366 // clean out anything left from a previous report requested by this user
367 clearUserPreviouSalarySummaryReports(principalName);
368
369 // default duration code is inserted into a couple of the SQL queries--get it now
370 ArrayList<String> durationCodeDefault = new ArrayList<String>(2);
371 durationCodeDefault.add(BCConstants.VACANT_EMPLID);
372 durationCodeDefault.add(BCConstants.AppointmentFundingDurationCodes.NONE.durationCode);
373
374 // fetch the base and request salary parameters for people who are marked as not going on leave
375 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(0).getSQL(durationCodeDefault), idForSession, principalName);
376 // fetch the base and request salary parameters for people who are marked as going on leave
377 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(1).getSQL(durationCodeDefault), idForSession, principalName);
378 // take request percent time, months appointment, and position months from the row with the largest request salary
379 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(2).getSQL(), idForSession, idForSession);
380 // take base percent time, months appointment, and position months from the row with the largest base salary
381 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(3).getSQL(), idForSession, idForSession, previousFiscalYear);
382 // combine the base and request months/percent time/position months into a single table
383 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(4).getSQL(), idForSession, idForSession);
384 // adjust the base salary so that it reflects the same appointment parameters as the request salary (increase it if the person will work 12 months this year, but worked only 10 last year, for example)
385 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(5).getSQL(), idForSession);
386 // adjust the base salary for changes in the position months versus last year
387 getSimpleJdbcTemplate().update(updateReportsSalarySummaryThreshold.get(6).getSQL(), idForSession);
388 // the salaries taken will either be above or below the threshold
389 // April 09, 2008: Jdbc (at least Oracle's implementation) chokes on a KualiDecimal, with a message that says "illegal column type"
390 // a simple cast to Number has the same result
391 // using the code below works
392 BigDecimal thresholdValue = threshold.bigDecimalValue();
393 if (reportGreaterThanOrEqualToThreshold)
394 {
395 getSimpleJdbcTemplate().update(salarySummaryAboveThreshold.get(0).getSQL(), idForSession, idForSession, thresholdValue);
396 }
397 else
398 {
399 getSimpleJdbcTemplate().update(salarySummaryBelowThreshold.get(0).getSQL(), idForSession, idForSession, thresholdValue);
400 }
401 // populate the holding table with the rows to be reported
402 // (only request is reported--the base was manipulated above to identify people above and below the threshold)
403 // name records for the rows to be reported
404 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(0).getSQL(), principalName, principalName, idForSession);
405 // salary data for the rows to be reported
406 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(1).getSQL(), principalName, principalName, idForSession);
407
408 // clear out the threshold work tables for this session
409 clearThresholdWorkTables(idForSession);
410 // clear out the common work table for this session
411 clearCommonWorkTable(idForSession);
412 /**
413 * 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.
414 */
415 persistenceService.clearCache();
416 }
417
418
419 /**
420 *
421 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSalarySummaryReportDao#reasonSummaryReports(java.lang.String, boolean)
422 */
423 public void updateSalaryAndReasonSummaryReportsWithoutThreshold(String principalName, boolean listSalariesWithReasonCodes) {
424
425 // get the session ID
426 Guid guid = new Guid();
427 String idForSession = guid.toString();
428
429 // get the insertion String for the vacant EMPLID
430 ArrayList<String> vacantEmplid = new ArrayList<String>(1);
431 vacantEmplid.add(BCConstants.VACANT_EMPLID);
432
433 // clean out anything left from a previous report requested by this user
434 clearUserPreviouSalarySummaryReports(principalName);
435
436 // the option exists to report only those people with a salary increase reason code, or to report everyone
437 if (listSalariesWithReasonCodes)
438 {
439 getSimpleJdbcTemplate().update(updateReportsSalarySummaryNoThresholdReason.get(0).getSQL(vacantEmplid), idForSession, principalName);
440 }
441 else
442 {
443 getSimpleJdbcTemplate().update(updateReportsSalarySummaryNoThresholdNoReason.get(0).getSQL(vacantEmplid), idForSession, principalName);
444 }
445 // populate the holding table with the rows to be reported
446 // name records for the rows to be reported
447 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(0).getSQL(), principalName, principalName, idForSession);
448 // salary data for the rows to be reported
449 getSimpleJdbcTemplate().update(updateReportsSalarySummaryCommon.get(1).getSQL(), principalName, principalName, idForSession);
450
451 // clear out the common work table for this session
452 clearCommonWorkTable(idForSession);
453 /**
454 * 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.
455 */
456 persistenceService.clearCache();
457 }
458
459 public void setPersistenceService(PersistenceService persistenceService)
460 {
461 this.persistenceService = persistenceService;
462 }
463
464 }
465
466
467