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.BCConstants;
021 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
022 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionLevelSummaryReportDao;
023 import org.kuali.rice.kns.service.PersistenceService;
024 import org.kuali.rice.kns.util.Guid;
025
026 /**
027 * report general ledger amounts and FTE from the pending budget by object level
028 */
029
030 public class BudgetConstructionLevelSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionLevelSummaryReportDao {
031 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionLevelSummaryReportDaoJdbc.class);
032
033 private static ArrayList<SQLForStep> updateReportsLevelSummaryTable = new ArrayList<SQLForStep>(7);
034 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
035
036 private PersistenceService persistenceService;
037
038
039 public BudgetConstructionLevelSummaryReportDaoJdbc() {
040
041 // builds and updates LevelSummaryReports
042 StringBuilder sqlText = new StringBuilder(1500);
043
044 /* insert the income records */
045 sqlText.append("INSERT INTO LD_BCN_LEVL_SUMM_T \n");
046 sqlText.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_SORT_CD, \n");
047 sqlText.append(" FIN_LEV_SORT_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, \n");
048 sqlText.append(" APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
049 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'A', objc.fin_report_sort_cd, \n");
050 sqlText.append(" objl.fin_report_sort_cd, sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), objl.fin_cons_obj_cd, objt.fin_obj_level_cd, \n");
051 sqlText.append(" 0, 0, 0, 0 \n");
052 sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc \n");
053 sqlText.append("WHERE pick.person_unvl_id = ? \n");
054 sqlText.append(" AND pick.report_flag > 0 \n");
055 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
056 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
057 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
058 sqlText.append(" AND pbgl.fin_obj_typ_cd in ");
059 // IN list of revenue object types
060 insertionPoints.add(sqlText.length());
061 sqlText.append("\n");
062 sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
063 sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
064 sqlText.append(" AND objt.fin_object_cd = pbgl.fin_object_cd \n");
065 sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
066 sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
067 sqlText.append(" AND objc.fin_coa_cd = objl.fin_coa_cd \n");
068 sqlText.append(" AND objc.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
069 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objc.fin_report_sort_cd, \n");
070 sqlText.append(" objl.fin_report_sort_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
071
072 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
073 sqlText.delete(0, sqlText.length());
074 insertionPoints.clear();
075
076 /* insert expenditure records with FTE place holders */
077 sqlText.append("INSERT INTO LD_BCN_LEVL_SUMM_T \n");
078 sqlText.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_SORT_CD, \n");
079 sqlText.append(" FIN_LEV_SORT_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, \n");
080 sqlText.append(" APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
081 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objc.fin_report_sort_cd, \n");
082 sqlText.append(" objl.fin_report_sort_cd, sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), objl.fin_cons_obj_cd, objt.fin_obj_level_cd, 0, \n");
083 sqlText.append(" 0, 0, 0 \n");
084 sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc \n");
085 sqlText.append("WHERE pick.person_unvl_id = ? \n");
086 sqlText.append(" AND pick.report_flag > 0 \n");
087 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
088 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
089 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
090 sqlText.append(" AND pbgl.fin_obj_typ_cd in ");
091 // IN list of expenditure object types
092 insertionPoints.add(sqlText.length());
093 sqlText.append("\n");
094 sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
095 sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
096 sqlText.append(" AND objt.fin_object_cd = pbgl.fin_object_cd \n");
097 sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
098 sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
099 sqlText.append(" AND objc.fin_coa_cd = objl.fin_coa_cd \n");
100 sqlText.append(" AND objc.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
101 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objc.fin_report_sort_cd, objl.fin_report_sort_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
102
103 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
104 sqlText.delete(0, sqlText.length());
105 insertionPoints.clear();
106
107 /* get the BCAF FTE values */
108 sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM03_MT \n");
109 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
110 sqlText.append(" FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, APPT_RQST_FTE_QTY) \n");
111 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
112 sqlText.append(" objt.fin_obj_level_cd, SUM(bcaf.APPT_RQCSF_FTE_QTY), SUM(bcaf.APPT_RQST_FTE_QTY) \n");
113 sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \n");
114 sqlText.append("WHERE pick.person_unvl_id = ? \n");
115 sqlText.append(" AND pick.report_flag > 0 \n");
116 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
117 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
118 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
119 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
120 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
121 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
122 sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
123 sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
124 sqlText.append(" AND objt.fin_object_cd = bcaf.fin_object_cd \n");
125 sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
126 sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
127 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
128
129 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
130 sqlText.delete(0, sqlText.length());
131
132 /* SQL-92 does not allow the target table of an UPDATE to be aliased. Supposedly, PostgreSQL enforces this (Gennick, p.156) */
133
134 /* copy the fte values to the report tables */
135 sqlText.append("UPDATE LD_BCN_LEVL_SUMM_T \n");
136 sqlText.append("SET appt_rqcsf_fte_qty =\n");
137 sqlText.append("(SELECT SUM(fq.appt_rqcsf_fte_qty) \n");
138 sqlText.append("FROM LD_BCN_BUILD_LEVLSUMM03_MT fq \n");
139 sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
140 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
141 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
142 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
143 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
144 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
145 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
146 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
147 sqlText.append(" AND fq.sesid = ?), \n");
148 sqlText.append(" appt_rqst_fte_qty = \n");
149 sqlText.append("(SELECT SUM(fq.appt_rqst_fte_qty) \n");
150 sqlText.append("FROM LD_BCN_BUILD_LEVLSUMM03_MT fq \n");
151 sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
152 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
153 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
154 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
155 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
156 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
157 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
158 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
159 sqlText.append(" AND fq.sesid = ?) \n");
160 sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
161 sqlText.append(" AND EXISTS (SELECT * FROM LD_BCN_BUILD_LEVLSUMM03_MT fq2 \n");
162 sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
163 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq2.org_fin_coa_cd \n");
164 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq2.org_cd \n");
165 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq2.sub_fund_grp_cd \n");
166 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq2.fin_coa_cd \n");
167 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq2.inc_exp_cd \n");
168 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq2.fin_cons_obj_cd \n");
169 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq2.fin_obj_level_cd \n");
170 sqlText.append(" AND fq2.sesid = ? ) \n");
171
172 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
173 sqlText.delete(0, sqlText.length());
174
175 /* get the CSF regular FTE */
176 sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM02_MT \n");
177 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
178 sqlText.append(" FIN_OBJ_LEVEL_CD, POS_CSF_FNDSTAT_CD, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
179 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
180 sqlText.append(" objt.fin_obj_level_cd, NULL, SUM(pos_csf_fte_qty), 0 \n");
181 sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_CSF_TRCKR_T bcsf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \n");
182 sqlText.append("WHERE pick.person_unvl_id = ? \n");
183 sqlText.append(" AND pick.report_flag > 0 \n");
184 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
185 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
186 sqlText.append(" AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
187 sqlText.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd \n");
188 sqlText.append(" AND bcsf.account_nbr = ctrl.account_nbr \n");
189 sqlText.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
190 sqlText.append(" AND (bcsf.pos_csf_fndstat_cd <> '");
191 // CSF funding status code for leave
192 insertionPoints.add(sqlText.length());
193 sqlText.append("' OR bcsf.pos_csf_fndstat_cd IS NULL) \n");
194 sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
195 sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
196 sqlText.append(" AND objt.fin_object_cd = bcsf.fin_object_cd \n");
197 sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
198 sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
199 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
200
201 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
202 sqlText.delete(0, sqlText.length());
203 insertionPoints.clear();
204
205 /* get the CSF leave FTE */
206 sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM02_MT \n");
207 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
208 sqlText.append(" FIN_OBJ_LEVEL_CD, POS_CSF_FNDSTAT_CD, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
209 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
210 sqlText.append(" objt.fin_obj_level_cd, '");
211 // CSF funding status code for leave
212 insertionPoints.add(sqlText.length());
213 sqlText.append("', 0, SUM(pos_csf_fte_qty) \n");
214 sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_CSF_TRCKR_T bcsf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \n");
215 sqlText.append("WHERE pick.person_unvl_id = ? \n");
216 sqlText.append(" AND pick.report_flag > 0 \n");
217 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
218 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
219 sqlText.append(" AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
220 sqlText.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd \n");
221 sqlText.append(" AND bcsf.account_nbr = ctrl.account_nbr \n");
222 sqlText.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
223 sqlText.append(" AND bcsf.pos_csf_fndstat_cd = '");
224 // CSF funding status code for leave
225 insertionPoints.add(sqlText.length());
226 sqlText.append("' \n");
227 sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
228 sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
229 sqlText.append(" AND objt.fin_object_cd = bcsf.fin_object_cd \n");
230 sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
231 sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
232 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
233
234 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
235 sqlText.delete(0, sqlText.length());
236 insertionPoints.clear();
237
238 /* copy the fte values to the report table */
239 sqlText.append("UPDATE LD_BCN_LEVL_SUMM_T \n");
240 sqlText.append("SET pos_csf_fte_qty = ");
241 sqlText.append("(SELECT SUM(pos_csf_fte_qty) \n");
242 sqlText.append(" FROM LD_BCN_BUILD_LEVLSUMM02_MT fq \n");
243 sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
244 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
245 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
246 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
247 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
248 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
249 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
250 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
251 sqlText.append(" AND fq.sesid = ?), \n");
252 sqlText.append(" pos_csf_lv_fte_qty =\n");
253 sqlText.append("(SELECT SUM(pos_csf_lv_fte_qty)\n");
254 sqlText.append(" FROM LD_BCN_BUILD_LEVLSUMM02_MT fq \n");
255 sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
256 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
257 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
258 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
259 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
260 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
261 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
262 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
263 sqlText.append(" AND fq.sesid = ?) \n");
264 sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
265 sqlText.append(" AND EXISTS (SELECT 1 FROM LD_BCN_BUILD_LEVLSUMM02_MT fq2 \n");
266 sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
267 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq2.org_fin_coa_cd \n");
268 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq2.org_cd \n");
269 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq2.sub_fund_grp_cd \n");
270 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq2.fin_coa_cd \n");
271 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq2.inc_exp_cd \n");
272 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq2.fin_cons_obj_cd \n");
273 sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq2.fin_obj_level_cd \n");
274 sqlText.append(" AND fq2.sesid = ?) \n");
275
276 updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
277 sqlText.delete(0, sqlText.length());
278 }
279
280 public void cleanReportsLevelSummaryTable(String principalName) {
281 clearTempTableByUnvlId("LD_BCN_LEVL_SUMM_T", "PERSON_UNVL_ID", principalName);
282 /**
283 * 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.
284 */
285 persistenceService.clearCache();
286 }
287
288 public void updateReportsLevelSummaryTable(String principalName) {
289
290 Guid guid = new Guid();
291 String idForSession = guid.toString();
292
293 ArrayList<String> stringsToInsert = new ArrayList<String>(10);
294
295 cleanReportsLevelSummaryTable(principalName);
296
297 // insert revenue by object level from pending budget construction general ledger into the report-by-level table
298 stringsToInsert.add(this.getRevenueINList());
299 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(0).getSQL(stringsToInsert), principalName, principalName);
300 // insert expenditure by object level from pending budget construction general ledger into the report-by-level table
301 stringsToInsert.clear();
302 stringsToInsert.add(this.getExpenditureINList());
303 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(1).getSQL(stringsToInsert), principalName, principalName);
304 // sum the FTE from appointment funding
305 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(2).getSQL(), idForSession, principalName);
306 // update the appointment FTE in the report-by-level table
307 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(3).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
308 // sum the non-leave FTE from the CSF
309 stringsToInsert.clear();
310 stringsToInsert.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
311 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(4).getSQL(stringsToInsert), idForSession, principalName);
312 // sum the FTE for leaves from the CSF (the leave flag is used twice in this SQL, so just add it again to stringsToInsert)
313 stringsToInsert.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
314 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(5).getSQL(stringsToInsert), idForSession, principalName);
315 // update all the CSF FTE fields in the report-by-level table
316 getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(6).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
317 clearTempTableBySesId("LD_BCN_BUILD_LEVLSUMM02_MT", "SESID", idForSession);
318 clearTempTableBySesId("LD_BCN_BUILD_LEVLSUMM03_MT", "SESID", idForSession);
319 /**
320 * 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.
321 */
322 persistenceService.clearCache();
323
324 }
325
326 public void setPersistenceService(PersistenceService persistenceService)
327 {
328 this.persistenceService = persistenceService;
329 }
330
331 }
332