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.BudgetConstructionAccountObjectDetailReportDao;
023 import org.kuali.kfs.sys.KFSConstants;
024 import org.kuali.rice.kns.service.PersistenceService;
025 import org.kuali.rice.kns.util.Guid;
026
027 /**
028 * builds the report table that supports the Organization Account Object Detail report. the report is customized by user, so the table rows are labeled with the user id
029 */
030
031 public class BudgetConstructionAccountObjectDetailReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionAccountObjectDetailReportDao {
032 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionAccountObjectDetailReportDaoJdbc.class);
033
034 private static ArrayList<SQLForStep> updateReportsAccountObjectDetailTable = new ArrayList<SQLForStep>(4);
035 private static ArrayList<SQLForStep> insertDetailForReport = new ArrayList<SQLForStep>(1);
036 private static ArrayList<SQLForStep> insertSummaryForReport = new ArrayList<SQLForStep>(1);
037
038 private PersistenceService persistenceService;
039
040 public BudgetConstructionAccountObjectDetailReportDaoJdbc() {
041
042 //builds and updates AccountObjectDetailTable
043 StringBuilder sqlText = new StringBuilder(5000);
044 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
045 //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.
046 //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.
047
048 /* get the set of income and expenditure lines */
049 /* for the selected accounts */
050 sqlText.append("INSERT INTO LD_BCN_BUILD_ACCTBAL01_MT\n");
051 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR,\n");
052 sqlText.append(" SUB_ACCT_NBR, INC_EXP_CD, FIN_CONS_SORT_CD, FIN_LEVEL_SORT_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQST_FTE_QTY,\n");
053 sqlText.append(" APPT_RQCSF_FTE_QTY, POS_CSF_FTE_QTY, FIN_BEG_BAL_LN_AMT, ACLN_ANNL_BAL_AMT, POS_CSF_LV_FTE_QTY) \n");
054 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, pbgl.univ_fiscal_yr, pbgl.fin_coa_cd, pbgl.account_nbr, \n");
055 sqlText.append(" pbgl.sub_acct_nbr, 'A', c.fin_report_sort_cd, objl.fin_report_sort_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, objt.fin_obj_level_cd, 0, \n");
056 sqlText.append(" 0, 0, sum(pbgl.fin_beg_bal_ln_amt), sum(pbgl.acln_annl_bal_amt), 0 \n");
057 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 c \n");
058 sqlText.append("WHERE pick.person_unvl_id = ? \n");
059 sqlText.append(" AND pick.report_flag > 0 \n");
060 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
061 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
062 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
063 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
064 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
065 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
066 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
067 sqlText.append(" AND pbgl.fin_obj_typ_cd in \n");
068 // list of income object types
069 insertionPoints.add(sqlText.length());
070 sqlText.append("\n");
071 sqlText.append(" AND pbgl.univ_fiscal_yr = objt.univ_fiscal_yr \n");
072 sqlText.append(" AND pbgl.fin_coa_cd = objt.fin_coa_cd \n");
073 sqlText.append(" AND pbgl.fin_object_cd = objt.fin_object_cd \n");
074 sqlText.append(" AND objt.fin_coa_cd = objl.fin_coa_cd \n");
075 sqlText.append(" AND objt.fin_obj_level_cd = objl.fin_obj_level_cd \n");
076 sqlText.append(" AND c.fin_coa_cd = objl.fin_coa_cd \n");
077 sqlText.append(" AND c.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
078 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, pbgl.univ_fiscal_yr, pbgl.fin_coa_cd, pbgl.account_nbr, pbgl.sub_acct_nbr, \n");
079 sqlText.append(" c.fin_report_sort_cd, objl.fin_report_sort_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, objt.fin_obj_level_cd \n");
080 sqlText.append("UNION ALL\n");
081 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, pbgl.univ_fiscal_yr, pbgl.fin_coa_cd, pbgl.account_nbr, \n");
082 sqlText.append(" pbgl.sub_acct_nbr, 'B', c.fin_report_sort_cd, objl.fin_report_sort_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, objt.fin_obj_level_cd, 0, \n");
083 sqlText.append(" 0, 0, sum(pbgl.fin_beg_bal_ln_amt), sum(pbgl.acln_annl_bal_amt), 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 c \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.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
091 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
092 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
093 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
094 sqlText.append(" AND pbgl.fin_obj_typ_cd in \n");
095 // list of expense object types
096 insertionPoints.add(sqlText.length());
097 sqlText.append("\n");
098 sqlText.append(" AND pbgl.univ_fiscal_yr = objt.univ_fiscal_yr \n");
099 sqlText.append(" AND pbgl.fin_coa_cd = objt.fin_coa_cd \n");
100 sqlText.append(" AND pbgl.fin_object_cd = objt.fin_object_cd \n");
101 sqlText.append(" AND objt.fin_coa_cd = objl.fin_coa_cd \n");
102 sqlText.append(" AND objt.fin_obj_level_cd = objl.fin_obj_level_cd \n");
103 sqlText.append(" AND c.fin_coa_cd = objl.fin_coa_cd \n");
104 sqlText.append(" AND c.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
105 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, pbgl.univ_fiscal_yr, pbgl.fin_coa_cd, pbgl.account_nbr, pbgl.sub_acct_nbr, \n");
106 sqlText.append(" c.fin_report_sort_cd, objl.fin_report_sort_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, objt.fin_obj_level_cd \n");
107
108 updateReportsAccountObjectDetailTable.add(new SQLForStep(sqlText,insertionPoints));
109 sqlText.delete(0, sqlText.length());
110 insertionPoints.clear();
111
112 /* SQL-92 does not permit us to use an alias for the target table in an UPDATE clause--neither, apparently does PostgreSQL (Gennick, p. 156) */
113
114 /* get the appointment funding fte */
115 sqlText.append("UPDATE LD_BCN_BUILD_ACCTBAL01_MT \n");
116 sqlText.append("SET appt_rqst_fte_qty = (SELECT SUM(af.appt_rqst_fte_qty) \n");
117 sqlText.append("FROM LD_PNDBC_APPTFND_T af \n");
118 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = af.univ_fiscal_yr \n");
119 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = af.fin_coa_cd \n");
120 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = af.account_nbr \n");
121 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = af.sub_acct_nbr \n");
122 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = af.fin_object_cd \n");
123 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = af.fin_sub_obj_cd), \n");
124 sqlText.append(" appt_rqcsf_fte_qty = (SELECT SUM(af.appt_rqcsf_fte_qty) \n");
125 sqlText.append("FROM LD_PNDBC_APPTFND_T af \n");
126 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = af.univ_fiscal_yr \n");
127 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = af.fin_coa_cd \n");
128 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = af.account_nbr \n");
129 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = af.sub_acct_nbr \n");
130 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = af.fin_object_cd \n");
131 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = af.fin_sub_obj_cd) \n");
132 sqlText.append("WHERE sesid = ? \n");
133 sqlText.append("AND EXISTS (SELECT 1 FROM LD_PNDBC_APPTFND_T af2 \n");
134 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = af2.univ_fiscal_yr \n");
135 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = af2.fin_coa_cd \n");
136 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = af2.account_nbr \n");
137 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = af2.sub_acct_nbr \n");
138 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = af2.fin_object_cd \n");
139 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = af2.fin_sub_obj_cd) \n");
140
141 updateReportsAccountObjectDetailTable.add(new SQLForStep(sqlText));
142 sqlText.delete(0, sqlText.length());
143
144 /* SQL-92 does not permit us to use an alias for the target table in an UPDATE clause--neither, apparently does PostgreSQL (Gennick, p. 156) */
145
146 /* get the csf regular fte */
147 sqlText.append("UPDATE LD_BCN_BUILD_ACCTBAL01_MT \n");
148 sqlText.append("SET pos_csf_fte_qty = (SELECT SUM(bcsf.pos_csf_fte_qty) \n");
149 sqlText.append("FROM LD_BCN_CSF_TRCKR_T bcsf \n");
150 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = bcsf.univ_fiscal_yr \n");
151 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = bcsf.fin_coa_cd \n");
152 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = bcsf.account_nbr \n");
153 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = bcsf.sub_acct_nbr \n");
154 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = bcsf.fin_object_cd \n");
155 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = bcsf.fin_sub_obj_cd \n");
156 sqlText.append("AND bcsf.pos_csf_fndstat_cd <> '");
157 // CSF Leave indicator
158 insertionPoints.add(sqlText.length());
159 sqlText.append("')\n");
160 sqlText.append("WHERE sesid = ? \n");
161 sqlText.append("AND EXISTS (SELECT 1 FROM LD_BCN_CSF_TRCKR_T bcsf2 \n");
162 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = bcsf2.univ_fiscal_yr \n");
163 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = bcsf2.fin_coa_cd \n");
164 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = bcsf2.account_nbr \n");
165 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = bcsf2.sub_acct_nbr \n");
166 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = bcsf2.fin_object_cd \n");
167 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = bcsf2.fin_sub_obj_cd \n");
168 sqlText.append("AND bcsf2.pos_csf_fndstat_cd <> '");
169 // CSF leave inndicator
170 insertionPoints.add(sqlText.length());
171 sqlText.append("')\n");
172
173 updateReportsAccountObjectDetailTable.add(new SQLForStep(sqlText,insertionPoints));
174 sqlText.delete(0, sqlText.length());
175 insertionPoints.clear();
176
177 /* SQL-92 does not permit us to use an alias for the target table in an UPDATE clause--neither, apparently does PostgreSQL (Gennick, p. 156) */
178
179 /* get the csf leave fte */
180
181 sqlText.append("UPDATE LD_BCN_BUILD_ACCTBAL01_MT \n");
182 sqlText.append("SET pos_csf_fte_qty = (SELECT SUM(bcsf.pos_csf_fte_qty) \n");
183 sqlText.append("FROM LD_BCN_CSF_TRCKR_T bcsf \n");
184 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = bcsf.univ_fiscal_yr \n");
185 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = bcsf.fin_coa_cd \n");
186 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = bcsf.account_nbr \n");
187 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = bcsf.sub_acct_nbr \n");
188 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = bcsf.fin_object_cd \n");
189 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = bcsf.fin_sub_obj_cd \n");
190 sqlText.append("AND bcsf.pos_csf_fndstat_cd = '");
191 //CSF leave indicator
192 insertionPoints.add(sqlText.length());
193 sqlText.append("')\n");
194 sqlText.append("WHERE sesid = ? \n");
195 sqlText.append("AND EXISTS (SELECT * FROM LD_BCN_CSF_TRCKR_T bcsf2 \n");
196 sqlText.append("WHERE LD_BCN_BUILD_ACCTBAL01_MT.univ_fiscal_yr = bcsf2.univ_fiscal_yr \n");
197 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_coa_cd = bcsf2.fin_coa_cd \n");
198 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.account_nbr = bcsf2.account_nbr \n");
199 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.sub_acct_nbr = bcsf2.sub_acct_nbr \n");
200 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_object_cd = bcsf2.fin_object_cd \n");
201 sqlText.append("AND LD_BCN_BUILD_ACCTBAL01_MT.fin_sub_obj_cd = bcsf2.fin_sub_obj_cd \n");
202 sqlText.append("AND bcsf2.pos_csf_fndstat_cd = '");
203 //CSF leave indicator
204 insertionPoints.add(sqlText.length());
205 sqlText.append("')\n");
206
207 updateReportsAccountObjectDetailTable.add(new SQLForStep(sqlText,insertionPoints));
208 sqlText.delete(0, sqlText.length());
209 insertionPoints.clear();
210
211 /* no rollup */
212 sqlText.append("INSERT INTO LD_BCN_ACCT_BAL_T \n");
213 sqlText.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, \n");
214 sqlText.append(" INC_EXP_CD, FIN_LEVEL_SORT_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_CONS_SORT_CD, FIN_OBJ_LEVEL_CD, APPT_RQST_FTE_QTY, \n");
215 sqlText.append(" APPT_RQCSF_FTE_QTY, POSITION_FTE_QTY, FIN_BEG_BAL_LN_AMT, ACLN_ANNL_BAL_AMT, POS_CSF_LV_FTE_QTY) \n");
216 sqlText.append(" SELECT ?, org_fin_coa_cd, org_cd, sub_fund_grp_cd, univ_fiscal_yr, fin_coa_cd, account_nbr, sub_acct_nbr, \n");
217 sqlText.append(" inc_exp_cd, fin_level_sort_cd, fin_object_cd, fin_sub_obj_cd, fin_cons_sort_cd, fin_obj_level_cd, appt_rqst_fte_qty, \n");
218 sqlText.append(" appt_rqcsf_fte_qty, pos_csf_fte_qty, fin_beg_bal_ln_amt, acln_annl_bal_amt, pos_csf_lv_fte_qty \n");
219 sqlText.append(" FROM LD_BCN_BUILD_ACCTBAL01_MT WHERE sesid = ? \n");
220
221 insertDetailForReport.add(new SQLForStep(sqlText));
222 sqlText.delete(0, sqlText.length());
223
224 /* rollup the sub-accounting and insert */
225 //should change order of select
226 sqlText.append("INSERT INTO LD_BCN_ACCT_BAL_T \n");
227 sqlText.append(" (PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, \n");
228 sqlText.append(" INC_EXP_CD, FIN_LEVEL_SORT_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_CONS_SORT_CD, FIN_OBJ_LEVEL_CD, APPT_RQST_FTE_QTY, \n");
229 sqlText.append(" APPT_RQCSF_FTE_QTY, POSITION_FTE_QTY, FIN_BEG_BAL_LN_AMT, ACLN_ANNL_BAL_AMT, POS_CSF_LV_FTE_QTY) \n");
230 sqlText.append(" SELECT ?, org_fin_coa_cd, org_cd, sub_fund_grp_cd, univ_fiscal_yr, fin_coa_cd, account_nbr, '");
231 // default subaccount number
232 insertionPoints.add(sqlText.length());
233 sqlText.append("', \n");
234 sqlText.append(" inc_exp_cd, fin_level_sort_cd, fin_object_cd, '");
235 // default subobject code
236 insertionPoints.add(sqlText.length());
237 sqlText.append("', fin_cons_sort_cd, fin_obj_level_cd, sum(appt_rqst_fte_qty), sum(appt_rqcsf_fte_qty), \n");
238 sqlText.append(" sum(pos_csf_fte_qty), sum(fin_beg_bal_ln_amt), sum(acln_annl_bal_amt), sum(pos_csf_lv_fte_qty) \n");
239 sqlText.append("FROM LD_BCN_BUILD_ACCTBAL01_MT \n");
240 sqlText.append("WHERE sesid = ? \n");
241 sqlText.append("GROUP BY org_fin_coa_cd, org_cd, sub_fund_grp_cd, univ_fiscal_yr, fin_coa_cd, account_nbr, inc_exp_cd, \n");
242 sqlText.append(" fin_cons_sort_cd, fin_level_sort_cd, fin_object_cd, fin_obj_level_cd \n");
243
244 insertSummaryForReport.add(new SQLForStep(sqlText,insertionPoints));
245 sqlText.delete(0, sqlText.length());
246 insertionPoints.clear();
247 }
248
249 protected void buildInitialAccountBalances(String sessionId, String principalName)
250 {
251 // remove any rows previously processed by this user
252 cleanReportsAccountObjectDetailTable(principalName);
253
254 // build the tables used both for detail and for consolidation
255 // insert the funding with all FTE zeroed out
256 ArrayList<String> stringsToInsert = new ArrayList<String>(2);
257 stringsToInsert.add(this.getRevenueINList());
258 stringsToInsert.add(this.getExpenditureINList());
259 getSimpleJdbcTemplate().update(updateReportsAccountObjectDetailTable.get(0).getSQL(stringsToInsert),sessionId, principalName, sessionId, principalName);
260 // fill in the FTE fields that come from appointment fundinng
261 getSimpleJdbcTemplate().update(updateReportsAccountObjectDetailTable.get(1).getSQL(),sessionId);
262 // fill in the FTE fields that come from CSF for people not on leave
263 stringsToInsert.clear();
264 stringsToInsert.add(new String(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue()));
265 stringsToInsert.add(new String(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue()));
266 getSimpleJdbcTemplate().update(updateReportsAccountObjectDetailTable.get(2).getSQL(stringsToInsert), sessionId);
267 // fill in the FTE fields that come from CSF for people who are on leave
268 getSimpleJdbcTemplate().update(updateReportsAccountObjectDetailTable.get(3).getSQL(stringsToInsert), sessionId);
269 }
270
271 protected void cleanReportsAccountObjectDetailTable(String principalName) {
272 clearTempTableByUnvlId("LD_BCN_ACCT_BAL_T", "PERSON_UNVL_ID", principalName);
273 }
274
275 protected void cleanReportsAccountObjectTemporaryTable(String sessionId)
276 {
277 clearTempTableBySesId("LD_BCN_BUILD_ACCTBAL01_MT","SESID",sessionId);
278 }
279
280
281 /**
282 *
283 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionAccountObjectDetailReportDao#updateReportsAccountObjectDetailTable(java.lang.String)
284 */
285 public void updateReportsAccountObjectDetailTable(String principalName) {
286
287 // get a unique ID to identify this user's session
288 String sessionId = (new Guid()).toString();
289
290 // add the reporting rows to the common base tables
291 this.buildInitialAccountBalances(sessionId, principalName);
292
293 // fill in the detail rows
294 getSimpleJdbcTemplate().update(insertDetailForReport.get(0).getSQL(), principalName, sessionId);
295
296 // clean out the temporary holding table for the reporting rows
297 cleanReportsAccountObjectTemporaryTable(sessionId);
298 /**
299 * 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.
300 */
301 persistenceService.clearCache();
302 }
303
304 /**
305 *
306 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionAccountObjectDetailReportDao#updateReportsAccountObjectConsolidatedTable(java.lang.String)
307 */
308 public void updateReportsAccountObjectConsolidatedTable(String principalName) {
309
310 // get a unique ID to identify this user's session
311 String sessionId = (new Guid()).toString();
312
313 // add the reporting rows to the common base tables
314 this.buildInitialAccountBalances(sessionId, principalName);
315
316 // fill in the consolidated rows with the default subaccount and the default subobject
317 ArrayList<String> stringsToInsert = new ArrayList<String>(2);
318 stringsToInsert.add(KFSConstants.getDashSubAccountNumber());
319 stringsToInsert.add(KFSConstants.getDashFinancialSubObjectCode());
320 getSimpleJdbcTemplate().update(insertSummaryForReport.get(0).getSQL(stringsToInsert), principalName, sessionId);
321
322 // clean out the temporary holding table for the reporting rows
323 cleanReportsAccountObjectTemporaryTable(sessionId);
324 /**
325 * 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.
326 */
327 persistenceService.clearCache();
328 }
329
330 public void setPersistenceService(PersistenceService persistenceService)
331 {
332 this.persistenceService = persistenceService;
333 }
334
335 }
336