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.Report;
021 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
022 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionAccountSummaryReportDao;
023 import org.kuali.rice.kns.service.PersistenceService;
024
025 /**
026 * builds rows for the general ledger summary report. allows three different levels of aggregation: account/sub-account, account, and subfund
027 */
028
029 public class BudgetConstructionAccountSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionAccountSummaryReportDao {
030 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionAccountSummaryReportDaoJdbc.class);
031
032 private static ArrayList<SQLForStep> updateReportsAccountSummaryTable = new ArrayList<SQLForStep>(1);
033
034 private static ArrayList<SQLForStep> updateReportsAccountSummaryTableWithConsolidation = new ArrayList<SQLForStep>(1);
035
036 private static ArrayList<SQLForStep> updateSubFundSummaryReport = new ArrayList<SQLForStep>(1);
037
038 private PersistenceService persistenceService;
039
040 public BudgetConstructionAccountSummaryReportDaoJdbc() {
041
042 //builds and updates AccountSummaryReports
043
044 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
045
046 //report the detail
047 StringBuilder sqlText = new StringBuilder(10000);
048 sqlText.append("INSERT INTO LD_BCN_ACCT_SUMM_T (PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, FIN_COA_CD, FUND_GRP_CD, SUB_FUND_GRP_CD, \n");
049 sqlText.append(" ACCOUNT_NBR, SUB_ACCT_NBR, INC_EXP_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, SUB_FUND_SORT_CD) \n");
050 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
051 sqlText.append(" ctrl.account_nbr, ctrl.sub_acct_nbr, '");
052 // INCOME_EXP_TYPE_A
053 insertionPoints.add(sqlText.length());
054 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd\n");
055 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
056 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
057 // IN list of object types for revenue
058 insertionPoints.add(sqlText.length());
059 sqlText.append(" \n");
060 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
061 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
062 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
063 sqlText.append(" AND pick.report_flag > 0 \n");
064 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
065 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
066 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
067 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
068 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
069 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
070 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, \n");
071 sqlText.append(" sf.fund_grp_cd, ctrl.sel_sub_fund_grp, ctrl.account_nbr, ctrl.sub_acct_nbr \n");
072 sqlText.append("UNION ALL\n");
073 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
074 sqlText.append(" ctrl.account_nbr, ctrl.sub_acct_nbr, '");
075 // INCOME_EXP_TYPE_E
076 insertionPoints.add(sqlText.length());
077 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
078 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
079 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
080 // IN list of object types for expenditure
081 insertionPoints.add(sqlText.length());
082 sqlText.append("\n");
083 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
084 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
085 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
086 sqlText.append(" AND pick.report_flag > 0 \n");
087 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
088 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
089 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
090 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
091 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
092 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
093 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
094 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
095 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
096 sqlText.append(" AND o.fin_obj_level_cd not in ('CORI','TRIN') \n");
097 sqlText.append(" AND EXISTS (SELECT 1 FROM CA_OBJECT_CODE_T o1, LD_PND_BCNSTR_GL_T pb \n");
098 sqlText.append("WHERE pb.fdoc_nbr = pbgl.fdoc_nbr \n");
099 sqlText.append(" AND pb.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
100 sqlText.append(" AND pb.fin_coa_cd = pbgl.fin_coa_cd \n");
101 sqlText.append(" AND pb.account_nbr = pbgl.account_nbr \n");
102 sqlText.append(" AND pb.sub_acct_nbr = pbgl.sub_acct_nbr \n");
103 sqlText.append(" AND o1.univ_fiscal_yr = pb.univ_fiscal_yr \n");
104 sqlText.append(" AND o1.fin_coa_cd = pb.fin_coa_cd \n");
105 sqlText.append(" AND o1.fin_object_cd = pb.fin_object_cd \n");
106 sqlText.append(" AND o1.fin_obj_level_cd in ('CORI','TRIN')) \n");
107 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd,\n");
108 sqlText.append(" ctrl.sel_sub_fund_grp, ctrl.account_nbr, ctrl.sub_acct_nbr \n");
109 sqlText.append("UNION ALL\n");
110 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
111 sqlText.append(" ctrl.account_nbr, ctrl.sub_acct_nbr, '");
112 // INCOME_EXP_TYPE_T
113 insertionPoints.add(sqlText.length());
114 sqlText.append("', sum(pbgl.acln_annl_bal_amt),sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
115 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
116 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
117 // IN list of expenditure object types
118 insertionPoints.add(sqlText.length());
119 sqlText.append(" \n");
120 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
121 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
122 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
123 sqlText.append(" AND pick.report_flag > 0 \n");
124 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
125 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
126 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
127 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
128 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
129 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
130 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
131 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
132 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
133 sqlText.append(" AND o.fin_obj_level_cd in ('CORI','TRIN') \n");
134 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd, \n");
135 sqlText.append(" ctrl.sel_sub_fund_grp, ctrl.account_nbr, ctrl.sub_acct_nbr \n");
136 sqlText.append("UNION ALL\n");
137 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
138 sqlText.append(" ctrl.account_nbr, ctrl.sub_acct_nbr, '");
139 // INCOME_EXP_TYPE_X
140 insertionPoints.add(sqlText.length());
141 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
142 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
143 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
144 // IN list of expenditure object types
145 insertionPoints.add(sqlText.length());
146 sqlText.append(" \n");
147 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
148 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
149 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
150 sqlText.append(" AND pick.report_flag > 0 \n");
151 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
152 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
153 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
154 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
155 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
156 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
157 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd,\n");
158 sqlText.append(" ctrl.sel_sub_fund_grp, ctrl.account_nbr, ctrl.sub_acct_nbr \n");
159
160 updateReportsAccountSummaryTable.add(new SQLForStep(sqlText,insertionPoints));
161 sqlText.delete(0, sqlText.length());
162 insertionPoints.clear();
163
164 //report at the account level
165 sqlText.append("INSERT INTO LD_BCN_ACCT_SUMM_T (PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, FIN_COA_CD, FUND_GRP_CD, SUB_FUND_GRP_CD, \n");
166 sqlText.append(" ACCOUNT_NBR, SUB_ACCT_NBR, INC_EXP_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, SUB_FUND_SORT_CD) \n");
167 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
168 sqlText.append(" ctrl.account_nbr, '-----', '");
169 // INCOME_EXP_TYPE_A
170 insertionPoints.add(sqlText.length());
171 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
172 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, \n");
173 sqlText.append(" LD_BCN_CTRL_LIST_T ctrl, \n");
174 sqlText.append(" LD_BCN_SUBFUND_PICK_T pick, \n");
175 sqlText.append(" CA_SUB_FUND_GRP_T sf \n");
176 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
177 insertionPoints.add(sqlText.length());
178 // IN list of revenue object types
179 sqlText.append(" \n");
180 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
181 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
182 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
183 sqlText.append(" AND pick.report_flag > 0 \n");
184 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
185 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
186 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
187 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
188 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
189 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
190 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, \n");
191 sqlText.append(" ctrl.sel_org_cd, \n");
192 sqlText.append(" ctrl.fin_coa_cd, \n");
193 sqlText.append(" sf.fin_report_sort_cd, \n");
194 sqlText.append(" sf.fund_grp_cd, \n");
195 sqlText.append(" ctrl.sel_sub_fund_grp, \n");
196 sqlText.append(" ctrl.account_nbr \n");
197 sqlText.append("UNION ALL\n");
198 sqlText.append(" SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, ctrl.account_nbr, '-----', \n");
199 sqlText.append(" '");
200 // INCOME_EXP_TYPE_E
201 insertionPoints.add(sqlText.length());
202 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
203 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
204 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
205 // IN list of expenditure object types
206 insertionPoints.add(sqlText.length());
207 sqlText.append(" \n");
208 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
209 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
210 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
211 sqlText.append(" AND pick.report_flag > 0 \n");
212 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
213 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
214 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
215 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
216 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
217 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
218 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
219 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
220 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
221 sqlText.append(" AND o.fin_obj_level_cd not in ('CORI','TRIN') \n");
222 sqlText.append(" AND EXISTS \n");
223 sqlText.append(" (SELECT 1 \n");
224 sqlText.append(" FROM CA_OBJECT_CODE_T o1, LD_PND_BCNSTR_GL_T pb \n");
225 sqlText.append(" WHERE pb.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
226 sqlText.append(" AND pb.fin_coa_cd = pbgl.fin_coa_cd \n");
227 sqlText.append(" AND pb.account_nbr = pbgl.account_nbr \n");
228 sqlText.append(" AND o1.univ_fiscal_yr = pb.univ_fiscal_yr \n");
229 sqlText.append(" AND o1.fin_coa_cd = pb.fin_coa_cd \n");
230 sqlText.append(" AND o1.fin_object_cd = pb.fin_object_cd \n");
231 sqlText.append(" AND o1.fin_obj_level_cd in ('CORI','TRIN')) \n");
232 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, \n");
233 sqlText.append(" sf.fund_grp_cd, ctrl.sel_sub_fund_grp, ctrl.account_nbr \n");
234 sqlText.append("UNION ALL\n");
235 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
236 sqlText.append(" ctrl.account_nbr, '-----', '");
237 // INCOME_EXP_TYPE_T
238 insertionPoints.add(sqlText.length());
239 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt),sf.fin_report_sort_cd \n");
240 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
241 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
242 // IN list of expenditure object types
243 insertionPoints.add(sqlText.length());
244 sqlText.append(" \n");
245 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
246 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
247 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
248 sqlText.append(" AND pick.report_flag > 0 \n");
249 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
250 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
251 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
252 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
253 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
254 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
255 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
256 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
257 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
258 sqlText.append(" AND o.fin_obj_level_cd in ('CORI','TRIN') \n");
259 sqlText.append(" GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, \n");
260 sqlText.append(" sf.fund_grp_cd, ctrl.sel_sub_fund_grp, ctrl.account_nbr \n");
261 sqlText.append("UNION ALL\n");
262 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
263 sqlText.append(" ctrl.account_nbr, '-----', '");
264 // INCOME_EXP_TYPE_X
265 insertionPoints.add(sqlText.length());
266 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
267 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
268 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
269 // IN list of expenditure object types
270 insertionPoints.add(sqlText.length());
271 sqlText.append(" \n");
272 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
273 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
274 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
275 sqlText.append(" AND pick.report_flag > 0 \n");
276 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
277 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
278 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
279 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
280 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
281 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
282 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, \n");
283 sqlText.append(" sf.fund_grp_cd, ctrl.sel_sub_fund_grp, ctrl.account_nbr \n");
284
285 updateReportsAccountSummaryTableWithConsolidation.add(new SQLForStep(sqlText,insertionPoints));
286 sqlText.delete(0, sqlText.length());
287 insertionPoints.clear();
288
289 //builds and updates SubFundSummaryReports
290 sqlText.append("INSERT INTO LD_BCN_ACCT_SUMM_T(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, FIN_COA_CD, FUND_GRP_CD, SUB_FUND_GRP_CD, \n");
291 sqlText.append(" ACCOUNT_NBR, SUB_ACCT_NBR, INC_EXP_CD, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, SUB_FUND_SORT_CD) \n");
292 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
293 sqlText.append(" '-------', '-----', '");
294 // INCOME_EXP_TYPE_A
295 insertionPoints.add(sqlText.length());
296 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
297 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
298 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
299 // IN list of revenue object types
300 insertionPoints.add(sqlText.length());
301 sqlText.append(" \n");
302 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
303 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
304 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
305 sqlText.append(" AND pick.report_flag > 0 \n");
306 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
307 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
308 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
309 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
310 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
311 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
312 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp \n");
313 sqlText.append("UNION ALL\n");
314 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
315 sqlText.append("'-------', '-----', '");
316 // INCOME_EXP_TYPE_E
317 insertionPoints.add(sqlText.length());
318 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
319 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
320 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
321 // IN list of expenditure object types
322 insertionPoints.add(sqlText.length());
323 sqlText.append(" \n");
324 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
325 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
326 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
327 sqlText.append(" AND pick.report_flag > 0 \n");
328 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
329 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
330 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
331 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
332 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
333 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
334 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
335 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
336 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
337 sqlText.append(" AND o.fin_obj_level_cd not in ('CORI','TRIN') \n");
338 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp \n");
339 sqlText.append("UNION ALL\n");
340 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
341 sqlText.append(" '-------', '-----', '");
342 // INCOME_EXP_TYPE_T
343 insertionPoints.add(sqlText.length());
344 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
345 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T o, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
346 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
347 insertionPoints.add(sqlText.length());
348 sqlText.append(" \n");
349 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
350 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
351 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
352 sqlText.append(" AND pick.report_flag > 0 \n");
353 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
354 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
355 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
356 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
357 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
358 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
359 sqlText.append(" AND o.univ_fiscal_yr = pbgl.univ_fiscal_yr \n");
360 sqlText.append(" AND o.fin_coa_cd = pbgl.fin_coa_cd \n");
361 sqlText.append(" AND o.fin_object_cd = pbgl.fin_object_cd \n");
362 sqlText.append(" AND o.fin_obj_level_cd in ('CORI','TRIN') \n");
363 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp \n");
364 sqlText.append("UNION ALL\n");
365 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp, \n");
366 sqlText.append(" '-------', '-----', '");
367 // INCOME_EXP_TYPE_X
368 insertionPoints.add(sqlText.length());
369 sqlText.append("', sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), sf.fin_report_sort_cd \n");
370 sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick, CA_SUB_FUND_GRP_T sf \n");
371 sqlText.append("WHERE pbgl.fin_obj_typ_cd in ");
372 // IN list for expenditure
373 insertionPoints.add(sqlText.length());
374 sqlText.append(" \n");
375 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
376 sqlText.append(" AND ctrl.person_unvl_id = pick.person_unvl_id \n");
377 sqlText.append(" AND ctrl.sel_sub_fund_grp = pick.sub_fund_grp_cd \n");
378 sqlText.append(" AND pick.report_flag > 0 \n");
379 sqlText.append(" AND pick.sub_fund_grp_cd = sf.sub_fund_grp_cd \n");
380 sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
381 sqlText.append(" AND pbgl.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
382 sqlText.append(" AND pbgl.fin_coa_cd = ctrl.fin_coa_cd \n");
383 sqlText.append(" AND pbgl.account_nbr = ctrl.account_nbr \n");
384 sqlText.append(" AND pbgl.sub_acct_nbr = ctrl.sub_acct_nbr \n");
385 sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.fin_coa_cd, sf.fin_report_sort_cd, sf.fund_grp_cd, ctrl.sel_sub_fund_grp \n");
386
387 updateSubFundSummaryReport.add(new SQLForStep(sqlText,insertionPoints));
388 sqlText.delete(0, sqlText.length());
389 insertionPoints.clear();
390 }
391
392 /**
393 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetReportsControlListDao#cleanReportsAccountSummaryTable(java.lang.String)
394 */
395 public void cleanReportsAccountSummaryTable(String principalName) {
396 clearTempTableByUnvlId("LD_BCN_ACCT_SUMM_T", "PERSON_UNVL_ID", principalName);
397 /**
398 * 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.
399 */
400 persistenceService.clearCache();
401 }
402
403 /**
404 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetReportsControlListDao#updateRepotsAccountSummaryTable(java.lang.String)
405 */
406 public void updateReportsAccountSummaryTable(String principalName) {
407 // build the list of strings to insert
408 String revenueList = getRevenueINList();
409 String expenditureList = getExpenditureINList();
410 ArrayList<String> stringsToInsert = new ArrayList<String>(8);
411 stringsToInsert.add(Report.INCOME_EXP_TYPE_A);
412 stringsToInsert.add(revenueList);
413 stringsToInsert.add(Report.INCOME_EXP_TYPE_E);
414 stringsToInsert.add(expenditureList);
415 stringsToInsert.add(Report.INCOME_EXP_TYPE_T);
416 stringsToInsert.add(expenditureList);
417 stringsToInsert.add(Report.INCOME_EXP_TYPE_X);
418 stringsToInsert.add(expenditureList);
419 // run the SQL after inserting the constant strings
420 getSimpleJdbcTemplate().update(updateReportsAccountSummaryTable.get(0).getSQL(stringsToInsert), principalName, principalName, principalName, principalName, principalName, principalName, principalName, principalName);
421 /**
422 * 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.
423 */
424 persistenceService.clearCache();
425 }
426
427
428 /**
429 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetReportsControlListDao#updateRepotsAccountSummaryTable(java.lang.String)
430 */
431 public void updateReportsAccountSummaryTableWithConsolidation(String principalName) {
432 // build the list of strings to insert
433 String revenueList = getRevenueINList();
434 String expenditureList = getExpenditureINList();
435 ArrayList<String> stringsToInsert = new ArrayList<String>(8);
436 stringsToInsert.add(Report.INCOME_EXP_TYPE_A);
437 stringsToInsert.add(revenueList);
438 stringsToInsert.add(Report.INCOME_EXP_TYPE_E);
439 stringsToInsert.add(expenditureList);
440 stringsToInsert.add(Report.INCOME_EXP_TYPE_T);
441 stringsToInsert.add(expenditureList);
442 stringsToInsert.add(Report.INCOME_EXP_TYPE_X);
443 stringsToInsert.add(expenditureList);
444 // run the SQL after inserting the constant strings
445 getSimpleJdbcTemplate().update(updateReportsAccountSummaryTableWithConsolidation.get(0).getSQL(stringsToInsert), principalName, principalName, principalName, principalName, principalName, principalName, principalName, principalName);
446 /**
447 * 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.
448 */
449 persistenceService.clearCache();
450 }
451
452 /**
453 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetReportsControlListDao#updateSubFundSummaryReport(java.lang.String)
454 */
455 public void updateSubFundSummaryReport(String principalName) {
456 // build the list of strings to insert
457 String revenueList = getRevenueINList();
458 String expenditureList = getExpenditureINList();
459 ArrayList<String> stringsToInsert = new ArrayList<String>(8);
460 stringsToInsert.add(Report.INCOME_EXP_TYPE_A);
461 stringsToInsert.add(revenueList);
462 stringsToInsert.add(Report.INCOME_EXP_TYPE_E);
463 stringsToInsert.add(expenditureList);
464 stringsToInsert.add(Report.INCOME_EXP_TYPE_T);
465 stringsToInsert.add(expenditureList);
466 stringsToInsert.add(Report.INCOME_EXP_TYPE_X);
467 stringsToInsert.add(expenditureList);
468 // run the SQL after inserting the constant strings
469 getSimpleJdbcTemplate().update(updateSubFundSummaryReport.get(0).getSQL(stringsToInsert), principalName, principalName, principalName, principalName, principalName, principalName, principalName, principalName);
470 /**
471 * 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.
472 */
473 persistenceService.clearCache();
474 }
475
476 public void setPersistenceService(PersistenceService persistenceService)
477 {
478 this.persistenceService = persistenceService;
479 }
480 }
481