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.BudgetConstructionMonthSummaryReportDao;
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 * report general ledger and monthly summaries from the budget by organization, subfund group, and object code
028 */
029
030 public class BudgetConstructionMonthSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionMonthSummaryReportDao {
031 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionMonthSummaryReportDaoJdbc.class);
032
033 private static ArrayList<SQLForStep> updateReportsMonthSummaryTable = new ArrayList<SQLForStep>(9);
034
035 private PersistenceService persistenceService;
036
037 public BudgetConstructionMonthSummaryReportDaoJdbc() {
038
039 //builds and updates MonthSummaryReports
040 StringBuilder sqlText = new StringBuilder(2500);
041 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
042
043 /* sum pending budget income records */
044 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
045 sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
046 sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
047 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
048 sqlText.append(" ctrl.fin_coa_cd, 'A', pbgl.fin_object_cd, '");
049 //default sub object code
050 insertionPoints.add(sqlText.length());
051 sqlText.append("', sum(pbgl.acln_annl_bal_amt) \n");
052 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \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 // list of revenue object types
060 insertionPoints.add(sqlText.length());
061 sqlText.append("\n");
062 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
063 sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd \n");
064
065 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
066 sqlText.delete(0, sqlText.length());
067 insertionPoints.clear();
068
069 /* sum pending budget expenditure records */
070 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
071 sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
072 sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
073 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
074 sqlText.append(" ctrl.fin_coa_cd, 'B', pbgl.fin_object_cd, '");
075 // default sub object code
076 insertionPoints.add(sqlText.length());
077 sqlText.append("', sum(pbgl.acln_annl_bal_amt) \n");
078 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
079 sqlText.append("WHERE pick.person_unvl_id = ? \n");
080 sqlText.append(" AND pick.report_flag > 0 \n");
081 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
082 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
083 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
084 sqlText.append(" AND pbgl.fin_obj_typ_cd in ");
085 // list of expenditure object types
086 insertionPoints.add(sqlText.length());
087 sqlText.append("\n");
088 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, pbgl.fin_object_cd \n");
089
090 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
091 sqlText.delete(0, sqlText.length());
092 insertionPoints.clear();
093
094 /* sum monthly budget income records */
095 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
096 sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
097 sqlText.append(" FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, \n");
098 sqlText.append(" FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT) \n");
099 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, 'A', \n");
100 sqlText.append(" mnth.fin_object_cd, '");
101 // default sub object code
102 insertionPoints.add(sqlText.length());
103 sqlText.append("', sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
104 sqlText.append(" sum(mnth.fdoc_ln_mo4_amt), sum(mnth.fdoc_ln_mo5_amt), sum(mnth.fdoc_ln_mo6_amt), sum(mnth.fdoc_ln_mo7_amt), \n");
105 sqlText.append(" sum(mnth.fdoc_ln_mo8_amt), sum(mnth.fdoc_ln_mo9_amt), sum(mnth.fdoc_ln_mo10_amt), sum(mnth.fdoc_ln_mo11_amt), sum(mnth.fdoc_ln_mo12_amt) \n");
106 sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
107 sqlText.append("WHERE pick.person_unvl_id = ? \n");
108 sqlText.append("AND pick.report_flag > 0 \n");
109 sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
110 sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
111 sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
112 sqlText.append("AND mnth.fin_obj_typ_cd in ");
113 // list of revenue object types
114 insertionPoints.add(sqlText.length());
115 sqlText.append("\n");
116 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, \n");
117 sqlText.append(" ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, mnth.fin_object_cd \n");
118
119 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
120 sqlText.delete(0, sqlText.length());
121 insertionPoints.clear();
122
123 /* sum monthly budget expenditure records */
124 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
125 sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
126 sqlText.append(" FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, \n");
127 sqlText.append(" FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT) \n");
128 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, \n");
129 sqlText.append(" 'B', mnth.fin_object_cd, '");
130 // default sub object code
131 insertionPoints.add(sqlText.length());
132 sqlText.append("', sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
133 sqlText.append(" sum(mnth.fdoc_ln_mo4_amt), sum(mnth.fdoc_ln_mo5_amt), sum(mnth.fdoc_ln_mo6_amt), sum(mnth.fdoc_ln_mo7_amt), \n");
134 sqlText.append(" sum(mnth.fdoc_ln_mo8_amt), sum(mnth.fdoc_ln_mo9_amt), sum(mnth.fdoc_ln_mo10_amt), sum(mnth.fdoc_ln_mo11_amt), sum(mnth.fdoc_ln_mo12_amt) \n");
135 sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
136 sqlText.append("WHERE pick.person_unvl_id = ? \n");
137 sqlText.append(" AND pick.report_flag > 0 \n");
138 sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
139 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
140 sqlText.append(" AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
141 sqlText.append(" AND mnth.fin_obj_typ_cd in ");
142 // list of expenditure object types
143 insertionPoints.add(sqlText.length());
144 sqlText.append("\n");
145 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, mnth.fin_object_cd \n");
146
147 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
148 sqlText.delete(0, sqlText.length());
149 insertionPoints.clear();
150
151 /* sum to the sub-object code */
152 /* sum pending budget income records */
153 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
154 sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
155 sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
156 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
157 sqlText.append(" ctrl.fin_coa_cd, 'A', pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, sum(pbgl.acln_annl_bal_amt) \n");
158 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
159 sqlText.append("WHERE pick.person_unvl_id = ? \n");
160 sqlText.append("AND pick.report_flag > 0 \n");
161 sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
162 sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
163 sqlText.append("AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
164 sqlText.append("AND pbgl.fin_obj_typ_cd in ");
165 // list of revenue object types
166 insertionPoints.add(sqlText.length());
167 sqlText.append("\n");
168 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
169 sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd \n");
170
171 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
172 sqlText.delete(0, sqlText.length());
173 insertionPoints.clear();
174
175 /* sum pending budget expenditure records */
176 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
177 sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
178 sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
179 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
180 sqlText.append(" ctrl.fin_coa_cd, 'B', pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, sum(pbgl.acln_annl_bal_amt) \n");
181 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \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 pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
187 sqlText.append("AND pbgl.fin_obj_typ_cd in ");
188 // list of expenditure object types
189 insertionPoints.add(sqlText.length());
190 sqlText.append("\n");
191 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
192 sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd \n");
193
194 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
195 sqlText.delete(0, sqlText.length());
196 insertionPoints.clear();
197
198 /* sum monthly budget income records */
199 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
200 sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
201 sqlText.append(" FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, \n");
202 sqlText.append(" FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT) \n");
203 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, 'A', \n");
204 sqlText.append(" mnth.fin_object_cd, mnth.fin_sub_obj_cd, sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
205 sqlText.append(" sum(mnth.fdoc_ln_mo4_amt), sum(mnth.fdoc_ln_mo5_amt), sum(mnth.fdoc_ln_mo6_amt), sum(mnth.fdoc_ln_mo7_amt), sum(mnth.fdoc_ln_mo8_amt), \n");
206 sqlText.append(" sum(mnth.fdoc_ln_mo9_amt), sum(mnth.fdoc_ln_mo10_amt), sum(mnth.fdoc_ln_mo11_amt), sum(mnth.fdoc_ln_mo12_amt) \n");
207 sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
208 sqlText.append("WHERE pick.person_unvl_id = ? \n");
209 sqlText.append("AND pick.report_flag > 0 \n");
210 sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
211 sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
212 sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
213 sqlText.append("AND mnth.fin_obj_typ_cd in ");
214 // list of income object types
215 insertionPoints.add(sqlText.length());
216 sqlText.append("\n");
217 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
218 sqlText.append(" ctrl.fin_coa_cd, mnth.fin_object_cd, mnth.fin_sub_obj_cd \n");
219
220 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
221 sqlText.delete(0, sqlText.length());
222 insertionPoints.clear();
223
224 /* sum monthly budget expenditure records */
225 sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
226 sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
227 sqlText.append(" FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, \n");
228 sqlText.append(" FDOC_LN_MO6_AMT, FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT) \n");
229 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, \n");
230 sqlText.append(" 'B', mnth.fin_object_cd, mnth.fin_sub_obj_cd, sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
231 sqlText.append(" sum(mnth.fdoc_ln_mo4_amt), sum(mnth.fdoc_ln_mo5_amt), sum(mnth.fdoc_ln_mo6_amt), sum(mnth.fdoc_ln_mo7_amt), sum(mnth.fdoc_ln_mo8_amt), \n");
232 sqlText.append(" sum(mnth.fdoc_ln_mo9_amt), sum(mnth.fdoc_ln_mo10_amt), sum(mnth.fdoc_ln_mo11_amt), sum(mnth.fdoc_ln_mo12_amt) \n");
233 sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
234 sqlText.append("WHERE pick.person_unvl_id = ? \n");
235 sqlText.append("AND pick.report_flag > 0 \n");
236 sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
237 sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
238 sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
239 sqlText.append("AND mnth.fin_obj_typ_cd in ");
240 // list of expenditure object types
241 insertionPoints.add(sqlText.length());
242 sqlText.append("\n");
243 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, \n");
244 sqlText.append(" mnth.fin_object_cd, mnth.fin_sub_obj_cd \n");
245
246 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
247 sqlText.delete(0, sqlText.length());
248 insertionPoints.clear();
249
250 /* join the summed values and merge level and consolidation info */
251 sqlText.append("INSERT INTO LD_BCN_MNTH_SUMM_T \n");
252 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");
253 sqlText.append(" FIN_LEV_SORT_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT, FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, \n");
254 sqlText.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, FDOC_LN_MO9_AMT, \n");
255 sqlText.append(" FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD) \n");
256 sqlText.append("SELECT ?, LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_fin_coa, LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_cd, \n");
257 sqlText.append(" LD_BCN_BUILD_MNTHSUMM01_MT.sel_sub_fund_grp, LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd, \n");
258 sqlText.append(" LD_BCN_BUILD_MNTHSUMM01_MT.inc_exp_cd, objc.fin_report_sort_cd, objl.fin_report_sort_cd, LD_BCN_BUILD_MNTHSUMM01_MT.fin_object_cd, \n");
259 sqlText.append(" LD_BCN_BUILD_MNTHSUMM01_MT.fin_sub_obj_cd, LD_BCN_BUILD_MNTHSUMM01_MT.acln_annl_bal_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo1_amt, \n");
260 sqlText.append(" LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo2_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo3_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo4_amt, \n");
261 sqlText.append(" LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo5_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo6_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo7_amt, \n");
262 sqlText.append(" LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo8_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo9_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo10_amt, \n");
263 sqlText.append(" LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo11_amt, LD_BCN_BUILD_MNTHSUMM02_MT.fdoc_ln_mo12_amt, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
264 sqlText.append("FROM CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc, (LD_BCN_BUILD_MNTHSUMM01_MT \n");
265 sqlText.append(" LEFT OUTER JOIN LD_BCN_BUILD_MNTHSUMM02_MT ON ((LD_BCN_BUILD_MNTHSUMM01_MT.sesid = LD_BCN_BUILD_MNTHSUMM02_MT.sesid) AND \n");
266 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_fin_coa = LD_BCN_BUILD_MNTHSUMM02_MT.sel_org_fin_coa) AND \n");
267 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_cd = LD_BCN_BUILD_MNTHSUMM02_MT.sel_org_cd) AND \n");
268 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_sub_fund_grp = LD_BCN_BUILD_MNTHSUMM02_MT.sel_sub_fund_grp) AND \n");
269 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.univ_fiscal_yr = LD_BCN_BUILD_MNTHSUMM02_MT.univ_fiscal_yr) AND \n");
270 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_coa_cd) AND \n");
271 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.inc_exp_cd = LD_BCN_BUILD_MNTHSUMM02_MT.inc_exp_cd) AND \n");
272 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_object_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_object_cd) AND \n");
273 sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_sub_obj_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_sub_obj_cd))) \n");
274 sqlText.append("WHERE LD_BCN_BUILD_MNTHSUMM01_MT.sesid = ?\n");
275 sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.univ_fiscal_yr = objt.univ_fiscal_yr \n");
276 sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd = objt.fin_coa_cd \n");
277 sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.fin_object_cd = objt.fin_object_cd \n");
278 sqlText.append("AND objt.fin_coa_cd = objl.fin_coa_cd \n");
279 sqlText.append("AND objt.fin_obj_level_cd = objl.fin_obj_level_cd \n");
280 sqlText.append("AND objl.fin_coa_cd = objc.fin_coa_cd \n");
281 sqlText.append("AND objl.fin_cons_obj_cd = objc.fin_cons_obj_cd \n");
282
283 updateReportsMonthSummaryTable.add(new SQLForStep(sqlText));
284 sqlText.delete(0, sqlText.length());
285
286 }
287
288 public void cleanReportsMonthSummaryTable(String principalName) {
289 clearTempTableByUnvlId("LD_BCN_MNTH_SUMM_T", "PERSON_UNVL_ID", principalName);
290 /**
291 * 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.
292 */
293 persistenceService.clearCache();
294 }
295
296 /**
297 *
298 * sums general ledger and montly budgets by subfund and organization to the object-code level
299 * @param principalName--the user requesting the report
300 * @param idForSession--the session id for the user
301 */
302 protected void consolidateMonthSummaryReportToObjectCodeLevel(String principalName, String idForSession) {
303
304 // set up the things that need to be inserted into the SQL (default sub object code and an object type IN list)
305 ArrayList<String> revenueInsertions = new ArrayList<String>(2);
306 ArrayList<String> expenditureInsertions = new ArrayList<String>(2);
307 revenueInsertions.add(KFSConstants.getDashFinancialSubObjectCode());
308 revenueInsertions.add(this.getRevenueINList());
309 expenditureInsertions.add(KFSConstants.getDashFinancialSubObjectCode());
310 expenditureInsertions.add(this.getExpenditureINList());
311
312 // sum revenue from the pending general ledger to the object code level
313 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(0).getSQL(revenueInsertions), idForSession, principalName);
314 // sum expenditure from the pending general ledger to the object code level
315 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(1).getSQL(expenditureInsertions), idForSession, principalName);
316 // sum revenue from the monthly budgets to the object code level
317 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(2).getSQL(revenueInsertions), idForSession, principalName);
318 // sum expenditure from the monthly budgets to the object code level
319 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(3).getSQL(expenditureInsertions), idForSession, principalName);
320 }
321
322 /**
323 *
324 * sums general ledger and monthly amounts by organization and subfund group to the sub-object level
325 * @param principalName--the user requesting the report
326 * @param idForSession--the ID for the user's session
327 */
328 protected void detailedMonthSummaryTableReport(String principalName, String idForSession) {
329
330 // set up the strings to be inserted into the SQL (revenue and expenditure object types
331 ArrayList<String> revenueInsertions = new ArrayList<String>(2);
332 ArrayList<String> expenditureInsertions = new ArrayList<String>(2);
333 revenueInsertions.add(this.getRevenueINList());
334 expenditureInsertions.add(this.getExpenditureINList());
335
336 // sum revenue from the pending general ledger to the sub-object code level
337 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(4).getSQL(revenueInsertions), idForSession, principalName);
338 // sum expenditure from the pending general ledger to the sub-object code level
339 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(5).getSQL(expenditureInsertions), idForSession, principalName);
340 // sum revenue from the monthly budgets to the sub-object code level
341 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(6).getSQL(revenueInsertions), idForSession, principalName);
342 // sum expenditure from the monthly budgets to the sub-object code level
343 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(7).getSQL(expenditureInsertions), idForSession, principalName);
344 }
345
346 /**
347 *
348 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthSummaryReportDao#updateReportsMonthSummaryTable(java.lang.String, boolean)
349 */
350 public void updateReportsMonthSummaryTable(String principalName, boolean consolidateToObjectCodeLevel) {
351
352 Guid guid = new Guid();
353 String idForSession = guid.toString();
354
355 // remove any previous reporting rows for this user
356 this.cleanReportsMonthSummaryTable(principalName);
357
358 if (consolidateToObjectCodeLevel)
359 {
360 consolidateMonthSummaryReportToObjectCodeLevel(principalName, idForSession);
361 }
362 else
363 {
364 detailedMonthSummaryTableReport(principalName, idForSession);
365 }
366 // join monthly budgets and general ledger to build the final table for the report
367 getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(8).getSQL(), principalName, idForSession);
368
369 // clear out the user's work table rows for this session
370 this.clearTempTableBySesId("LD_BCN_BUILD_MNTHSUMM01_MT","SESID",idForSession);
371 this.clearTempTableBySesId("LD_BCN_BUILD_MNTHSUMM02_MT","SESID",idForSession);
372 /**
373 * 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.
374 */
375 persistenceService.clearCache();
376 }
377
378 public void setPersistenceService(PersistenceService persistenceService)
379 {
380 this.persistenceService = persistenceService;
381 }
382
383 }
384