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.BudgetConstructionObjectSummaryReportDao;
023 import org.kuali.rice.kns.service.PersistenceService;
024 import org.kuali.rice.kns.util.Guid;
025
026 public class BudgetConstructionObjectSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionObjectSummaryReportDao {
027
028 private static ArrayList<SQLForStep> objectSummarySql = new ArrayList<SQLForStep>(5);
029
030 private PersistenceService persistenceService;
031
032 public BudgetConstructionObjectSummaryReportDaoJdbc()
033 {
034
035
036 StringBuilder sqlBuilder = new StringBuilder(1500);
037 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
038
039
040 // build the INSERT SQL for the main table
041 sqlBuilder.append("INSERT INTO LD_BCN_OBJT_SUMM_T\n");
042 sqlBuilder.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD,\n");
043 sqlBuilder.append("INC_EXP_CD, FIN_CONS_SORT_CD, FIN_LEV_SORT_CD, FIN_OBJECT_CD, ACLN_ANNL_BAL_AMT,\n");
044 sqlBuilder.append("FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY,\n");
045 sqlBuilder.append("APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY)\n");
046 sqlBuilder.append("SELECT\n");
047 sqlBuilder.append("?,\n");
048 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
049 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
050 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
051 sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,'A',\n");
052 sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
053 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
054 sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
055 sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT),\n");
056 sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT),\n");
057 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
058 sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD,\n");
059 sqlBuilder.append("0,0,0,0\n");
060 sqlBuilder.append(" FROM LD_BCN_SUBFUND_PICK_T,\n");
061 sqlBuilder.append(" LD_BCN_CTRL_LIST_T,\n");
062 sqlBuilder.append(" LD_PND_BCNSTR_GL_T,\n");
063 sqlBuilder.append(" CA_OBJECT_CODE_T,\n");
064 sqlBuilder.append(" CA_OBJ_LEVEL_T,\n");
065 sqlBuilder.append(" CA_OBJ_CONSOLDTN_T\n");
066 sqlBuilder.append(" WHERE (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = ?)\n");
067 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.REPORT_FLAG > 0)\n");
068 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.SUB_FUND_GRP_CD = LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP)\n");
069 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = LD_BCN_CTRL_LIST_T.PERSON_UNVL_ID)\n");
070 sqlBuilder.append(" AND (CA_OBJ_CONSOLDTN_T.FIN_COA_CD = CA_OBJ_LEVEL_T.FIN_COA_CD)\n");
071 sqlBuilder.append(" AND (CA_OBJ_CONSOLDTN_T.FIN_CONS_OBJ_CD = CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD)\n");
072 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FDOC_NBR = LD_BCN_CTRL_LIST_T.FDOC_NBR)\n");
073 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.UNIV_FISCAL_YR = LD_BCN_CTRL_LIST_T.UNIV_FISCAL_YR)\n");
074 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.FIN_COA_CD = LD_BCN_CTRL_LIST_T.FIN_COA_CD)\n");
075 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
076 sqlBuilder.append(" AND (CA_OBJ_LEVEL_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD)\n");
077 sqlBuilder.append(" AND (CA_OBJ_LEVEL_T.FIN_OBJ_LEVEL_CD = CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD)\n");
078 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_OBJ_TYP_CD IN ");
079 // income object type IN list
080 insertionPoints.add(sqlBuilder.length());
081 sqlBuilder.append(")\n");
082 sqlBuilder.append("GROUP BY LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
083 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
084 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
085 sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,\n");
086 sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
087 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
088 sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
089 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
090 sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD\n");
091 sqlBuilder.append("UNION ALL\n");
092 sqlBuilder.append("SELECT\n");
093 sqlBuilder.append("?,\n");
094 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
095 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
096 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
097 sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,");
098 sqlBuilder.append("'B',\n");
099 sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
100 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
101 sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
102 sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT),\n");
103 sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT),\n");
104 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
105 sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD,\n");
106 sqlBuilder.append("0,0,0,0\n");
107 sqlBuilder.append(" FROM LD_BCN_SUBFUND_PICK_T,\n");
108 sqlBuilder.append(" LD_BCN_CTRL_LIST_T,\n");
109 sqlBuilder.append(" LD_PND_BCNSTR_GL_T,\n");
110 sqlBuilder.append(" CA_OBJECT_CODE_T,\n");
111 sqlBuilder.append(" CA_OBJ_LEVEL_T,\n");
112 sqlBuilder.append(" CA_OBJ_CONSOLDTN_T\n");
113 sqlBuilder.append(" WHERE (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = ?)\n");
114 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.REPORT_FLAG > 0)\n");
115 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.SUB_FUND_GRP_CD = LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP)\n");
116 sqlBuilder.append(" AND (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = LD_BCN_CTRL_LIST_T.PERSON_UNVL_ID)\n");
117 sqlBuilder.append(" AND (CA_OBJ_CONSOLDTN_T.FIN_COA_CD = CA_OBJ_LEVEL_T.FIN_COA_CD)\n");
118 sqlBuilder.append(" AND (CA_OBJ_CONSOLDTN_T.FIN_CONS_OBJ_CD = CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD)\n");
119 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FDOC_NBR = LD_BCN_CTRL_LIST_T.FDOC_NBR)\n");
120 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.UNIV_FISCAL_YR = LD_BCN_CTRL_LIST_T.UNIV_FISCAL_YR)\n");
121 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.FIN_COA_CD = LD_BCN_CTRL_LIST_T.FIN_COA_CD)\n");
122 sqlBuilder.append(" AND (CA_OBJECT_CODE_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
123 sqlBuilder.append(" AND (CA_OBJ_LEVEL_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD)\n");
124 sqlBuilder.append(" AND (CA_OBJ_LEVEL_T.FIN_OBJ_LEVEL_CD = CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD)\n");
125 sqlBuilder.append(" AND (LD_PND_BCNSTR_GL_T.FIN_OBJ_TYP_CD IN ");
126 // expenditure object type IN list
127 insertionPoints.add(sqlBuilder.length());
128 sqlBuilder.append(")\n");
129 sqlBuilder.append("GROUP BY LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
130 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
131 sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
132 sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,\n");
133 sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
134 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
135 sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
136 sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
137 sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD\n");
138
139 objectSummarySql.add(new SQLForStep(sqlBuilder,insertionPoints));
140 sqlBuilder.delete(0,sqlBuilder.length());
141 insertionPoints.clear();
142
143 // SQL to get the FTE amounts from appointment funding that match with the expenditure
144 sqlBuilder.append("INSERT INTO LD_BCN_BUILD_OBJTSUMM01_MT\n");
145 sqlBuilder.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD,\n");
146 sqlBuilder.append(" INC_EXP_CD, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, FIN_OBJECT_CD,\n");
147 sqlBuilder.append(" APPT_RQCSF_FTE_QTY, APPT_RQST_FTE_QTY)\n");
148 sqlBuilder.append("(SELECT\n");
149 sqlBuilder.append(" ?,\n");
150 sqlBuilder.append(" ctrl.sel_org_fin_coa,\n");
151 sqlBuilder.append(" ctrl.sel_org_cd,\n");
152 sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
153 sqlBuilder.append(" ctrl.fin_coa_cd,\n");
154 sqlBuilder.append(" 'B',\n");
155 sqlBuilder.append( "objl.fin_cons_obj_cd,\n");
156 sqlBuilder.append(" objt.fin_obj_level_cd,\n");
157 sqlBuilder.append(" bcaf.fin_object_cd,\n");
158 sqlBuilder.append(" SUM(bcaf.appt_rqcsf_fte_qty),\n");
159 sqlBuilder.append(" SUM(bcaf.appt_rqst_fte_qty)\n");
160 sqlBuilder.append(" FROM LD_BCN_SUBFUND_PICK_T pick,\n");
161 sqlBuilder.append(" LD_BCN_CTRL_LIST_T ctrl,\n");
162 sqlBuilder.append(" LD_PNDBC_APPTFND_T bcaf,\n");
163 sqlBuilder.append(" CA_OBJECT_CODE_T objt,\n");
164 sqlBuilder.append(" CA_OBJ_LEVEL_T objl\n");
165 sqlBuilder.append(" WHERE pick.person_unvl_id = ?\n");
166 sqlBuilder.append(" AND pick.report_flag > 0\n");
167 sqlBuilder.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
168 sqlBuilder.append(" AND pick.person_unvl_id = ctrl.person_unvl_id\n");
169 sqlBuilder.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
170 sqlBuilder.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd\n");
171 sqlBuilder.append(" AND bcaf.account_nbr = ctrl.account_nbr\n");
172 sqlBuilder.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
173 sqlBuilder.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
174 sqlBuilder.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
175 sqlBuilder.append(" AND objt.fin_object_cd = bcaf.fin_object_cd\n");
176 sqlBuilder.append(" AND objl.fin_coa_cd = objt.fin_coa_cd\n");
177 sqlBuilder.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
178 sqlBuilder.append(" GROUP BY ctrl.sel_org_fin_coa,\n");
179 sqlBuilder.append(" ctrl.sel_org_cd,\n");
180 sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
181 sqlBuilder.append(" ctrl.fin_coa_cd,\n");
182 sqlBuilder.append(" objl.fin_cons_obj_cd,\n");
183 sqlBuilder.append(" objt.fin_obj_level_cd,\n");
184 sqlBuilder.append(" bcaf.fin_object_cd)");
185 objectSummarySql.add(new SQLForStep(sqlBuilder));
186 sqlBuilder.delete(0,sqlBuilder.length());
187
188 // update the original lines using the FTE generated above. (PostGreSQL supposedly does not allow the target table in an UPDATE to be aliased. Gennick, p.159.)
189 sqlBuilder.append("UPDATE LD_BCN_OBJT_SUMM_T\n");
190 sqlBuilder.append("SET appt_rqcsf_fte_qty =\n");
191 sqlBuilder.append(" (SELECT SUM(fq.appt_rqcsf_fte_qty)\n");
192 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM01_MT fq\n");
193 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
194 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
195 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
196 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
197 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
198 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
199 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
200 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
201 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
202 sqlBuilder.append(" AND fq.sesid = ?),\n");
203 sqlBuilder.append(" appt_rqst_fte_qty =\n");
204 sqlBuilder.append(" (SELECT SUM(fq.appt_rqst_fte_qty)\n");
205 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM01_MT fq\n");
206 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
207 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
208 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
209 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
210 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
211 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
212 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
213 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
214 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
215 sqlBuilder.append(" AND fq.sesid = ?)\n");
216 sqlBuilder.append("WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
217 sqlBuilder.append(" AND EXISTS (SELECT 1\n");
218 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM01_MT fq2\n");
219 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
220 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq2.org_fin_coa_cd\n");
221 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq2.org_cd\n");
222 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq2.sub_fund_grp_cd\n");
223 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq2.fin_coa_cd\n");
224 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq2.inc_exp_cd\n");
225 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq2.fin_cons_obj_cd\n");
226 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq2.fin_obj_level_cd\n");
227 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq2.fin_object_cd\n");
228 sqlBuilder.append(" AND fq2.sesid = ?)");
229
230 objectSummarySql.add(new SQLForStep(sqlBuilder));
231 sqlBuilder.delete(0,sqlBuilder.length());
232
233 // sum the base (CSF for the current year) FTE into a holding table
234 sqlBuilder.append("INSERT INTO LD_BCN_BUILD_OBJTSUMM02_MT\n");
235 sqlBuilder.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD,\n");
236 sqlBuilder.append(" FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, FIN_OBJECT_CD, POS_CSF_FNDSTAT_CD,\n");
237 sqlBuilder.append(" POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY)\n");
238 sqlBuilder.append("SELECT\n");
239 sqlBuilder.append(" ?,\n");
240 sqlBuilder.append(" ctrl.sel_org_fin_coa,\n");
241 sqlBuilder.append(" ctrl.sel_org_cd,\n");
242 sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
243 sqlBuilder.append(" ctrl.fin_coa_cd,\n");
244 sqlBuilder.append(" 'B',\n");
245 sqlBuilder.append(" objl.fin_cons_obj_cd,\n");
246 sqlBuilder.append(" objt.fin_obj_level_cd,\n");
247 sqlBuilder.append(" bcsf.fin_object_cd,\n");
248 sqlBuilder.append(" NULL,\n");
249 sqlBuilder.append(" SUM(bcsf.pos_csf_fte_qty),\n");
250 sqlBuilder.append(" 0\n");
251 sqlBuilder.append("FROM LD_BCN_SUBFUND_PICK_T pick,\n");
252 sqlBuilder.append(" LD_BCN_CTRL_LIST_T ctrl,\n");
253 sqlBuilder.append(" LD_BCN_CSF_TRCKR_T bcsf,\n");
254 sqlBuilder.append(" CA_OBJECT_CODE_T objt,\n");
255 sqlBuilder.append(" CA_OBJ_LEVEL_T objl\n");
256 sqlBuilder.append("WHERE pick.person_unvl_id = ?\n");
257 sqlBuilder.append(" AND pick.report_flag > 0\n");
258 sqlBuilder.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
259 sqlBuilder.append(" AND pick.person_unvl_id = ctrl.person_unvl_id\n");
260 sqlBuilder.append(" AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
261 sqlBuilder.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd\n");
262 sqlBuilder.append(" AND bcsf.account_nbr = ctrl.account_nbr\n");
263 sqlBuilder.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
264 sqlBuilder.append(" AND (bcsf.pos_csf_fndstat_cd <> '");
265 // CSF LEAVE funding status
266 insertionPoints.add(sqlBuilder.length());
267 sqlBuilder.append("' OR bcsf.pos_csf_fndstat_cd IS NULL)\n");
268 sqlBuilder.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
269 sqlBuilder.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
270 sqlBuilder.append(" AND objt.fin_object_cd = bcsf.fin_object_cd\n");
271 sqlBuilder.append(" AND objl.fin_coa_cd = objt.fin_coa_cd\n");
272 sqlBuilder.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
273 sqlBuilder.append("GROUP BY ctrl.sel_org_fin_coa,\n");
274 sqlBuilder.append(" ctrl.sel_org_cd,\n");
275 sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
276 sqlBuilder.append(" ctrl.fin_coa_cd,\n");
277 sqlBuilder.append(" objl.fin_cons_obj_cd,\n");
278 sqlBuilder.append(" objt.fin_obj_level_cd,\n");
279 sqlBuilder.append(" bcsf.fin_object_cd\n");
280 sqlBuilder.append("UNION ALL\n");
281 sqlBuilder.append("SELECT\n");
282 sqlBuilder.append("?,\n");
283 sqlBuilder.append("ctrl.sel_org_fin_coa,\n");
284 sqlBuilder.append("ctrl.sel_org_cd,\n");
285 sqlBuilder.append("ctrl.sel_sub_fund_grp,\n");
286 sqlBuilder.append("ctrl.fin_coa_cd,\n");
287 sqlBuilder.append("'B',\n");
288 sqlBuilder.append("objl.fin_cons_obj_cd,\n");
289 sqlBuilder.append("objt.fin_obj_level_cd,\n");
290 sqlBuilder.append("bcsf.fin_object_cd,\n");
291 sqlBuilder.append("'");
292 // CSF LEAVE funding status
293 insertionPoints.add(sqlBuilder.length());
294 sqlBuilder.append("',\n");
295 sqlBuilder.append("0,\n");
296 sqlBuilder.append(" SUM(bcsf.pos_csf_fte_qty)\n");
297 sqlBuilder.append("FROM LD_BCN_SUBFUND_PICK_T pick,\n");
298 sqlBuilder.append(" LD_BCN_CTRL_LIST_T ctrl,\n");
299 sqlBuilder.append(" LD_BCN_CSF_TRCKR_T bcsf,\n");
300 sqlBuilder.append(" CA_OBJECT_CODE_T objt,\n");
301 sqlBuilder.append(" CA_OBJ_LEVEL_T objl\n");
302 sqlBuilder.append("WHERE pick.person_unvl_id = ?\n");
303 sqlBuilder.append(" AND pick.report_flag > 0\n");
304 sqlBuilder.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
305 sqlBuilder.append(" AND pick.person_unvl_id = ctrl.person_unvl_id\n");
306 sqlBuilder.append(" AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
307 sqlBuilder.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd\n");
308 sqlBuilder.append(" AND bcsf.account_nbr = ctrl.account_nbr\n");
309 sqlBuilder.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
310 sqlBuilder.append(" AND bcsf.pos_csf_fndstat_cd = '");
311 // CSF LEAVE funding status
312 insertionPoints.add(sqlBuilder.length());
313 sqlBuilder.append("'\n");
314 sqlBuilder.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
315 sqlBuilder.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
316 sqlBuilder.append(" AND objt.fin_object_cd = bcsf.fin_object_cd\n");
317 sqlBuilder.append(" AND objl.fin_coa_cd = objt.fin_coa_cd\n");
318 sqlBuilder.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
319 sqlBuilder.append("GROUP BY ctrl.sel_org_fin_coa,\n");
320 sqlBuilder.append(" ctrl.sel_org_cd,\n");
321 sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
322 sqlBuilder.append(" ctrl.fin_coa_cd,\n");
323 sqlBuilder.append(" objl.fin_cons_obj_cd,\n");
324 sqlBuilder.append(" objt.fin_obj_level_cd,\n");
325 sqlBuilder.append(" bcsf.fin_object_cd\n");
326
327 objectSummarySql.add(new SQLForStep(sqlBuilder,insertionPoints));
328 sqlBuilder.delete(0,sqlBuilder.length());
329 insertionPoints.clear();
330
331 // update the base FTE in the reporting table using the holding table values. (PostGreSQL supposedly does not allow the target table in an UPDATE to be aliased. Gennick, p.159.)
332 sqlBuilder.append("UPDATE LD_BCN_OBJT_SUMM_T\n");
333 sqlBuilder.append("SET LD_BCN_OBJT_SUMM_T.POS_CSF_FTE_QTY =\n");
334 sqlBuilder.append(" (SELECT SUM(fq.pos_csf_fte_qty)\n");
335 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM02_MT fq\n");
336 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
337 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
338 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
339 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
340 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
341 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
342 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
343 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
344 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
345 sqlBuilder.append(" AND fq.sesid = ?),\n");
346 sqlBuilder.append(" LD_BCN_OBJT_SUMM_T.POS_CSF_LV_FTE_QTY =\n");
347 sqlBuilder.append(" (SELECT SUM(fq.pos_csf_lv_fte_qty)\n");
348 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM02_MT fq\n");
349 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
350 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
351 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
352 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
353 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
354 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
355 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
356 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
357 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
358 sqlBuilder.append(" AND fq.sesid = ?)\n");
359 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
360 sqlBuilder.append(" AND EXISTS (SELECT 1\n");
361 sqlBuilder.append(" FROM LD_BCN_BUILD_OBJTSUMM02_MT fq2\n");
362 sqlBuilder.append(" WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
363 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq2.org_fin_coa_cd\n");
364 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq2.org_cd\n");
365 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq2.sub_fund_grp_cd\n");
366 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq2.fin_coa_cd\n");
367 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq2.inc_exp_cd\n");
368 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq2.fin_cons_obj_cd\n");
369 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq2.fin_obj_level_cd\n");
370 sqlBuilder.append(" AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq2.fin_object_cd\n");
371 sqlBuilder.append(" AND fq2.sesid = ?)");
372
373 objectSummarySql.add(new SQLForStep(sqlBuilder));
374 sqlBuilder.delete(0,sqlBuilder.length());
375
376
377 }
378
379 /**
380 *
381 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionObjectSummaryReportDao#cleanGeneralLedgerObjectSummaryTable(java.lang.String)
382 */
383 public void cleanGeneralLedgerObjectSummaryTable(String principalName) {
384 this.clearTempTableByUnvlId("LD_BCN_OBJT_SUMM_T","PERSON_UNVL_ID",principalName);
385 /**
386 * 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.
387 */
388 persistenceService.clearCache();
389 }
390
391 /**
392 *
393 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionObjectSummaryReportDao#updateGeneralLedgerObjectSummaryTable(java.lang.String)
394 */
395 public void updateGeneralLedgerObjectSummaryTable(String principalName) {
396 String idForSession = (new Guid()).toString();
397 ArrayList<String> inLists = new ArrayList<String>(2);
398 inLists.add(this.getRevenueINList());
399 inLists.add(this.getExpenditureINList());
400
401 // get rid of anything left over from the last time this user ran this report
402 cleanGeneralLedgerObjectSummaryTable(principalName);
403
404 // insert the general ledger amounts into the report table, with 0 placeholders for the FTE
405 getSimpleJdbcTemplate().update(objectSummarySql.get(0).getSQL(inLists),principalName,principalName,principalName,principalName);
406
407 // sum up the FTE from the appointment funding and stick it in a holding table
408 getSimpleJdbcTemplate().update(objectSummarySql.get(1).getSQL(),idForSession,principalName);
409
410 // set the FTE in the report table using the appointment funding FTE from the holding table
411 getSimpleJdbcTemplate().update(objectSummarySql.get(2).getSQL(),principalName,idForSession,principalName,idForSession,principalName,principalName,idForSession);
412
413 // sum up the FTE from the CSF tracker (base funding) table and stick it in a holding table
414 ArrayList<String> csfLeaveIndicator = new ArrayList<String>(3);
415 csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
416 csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
417 csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
418 getSimpleJdbcTemplate().update(objectSummarySql.get(3).getSQL(csfLeaveIndicator),idForSession,principalName,idForSession,principalName);
419
420 // set the CSF FTE in the report table using the FTE from the holding table
421 getSimpleJdbcTemplate().update(objectSummarySql.get(4).getSQL(),principalName,idForSession,principalName,idForSession,principalName,principalName,idForSession);
422
423 // clean out this session's rows from the holding tables used
424 this.clearTempTableBySesId("LD_BCN_BUILD_OBJTSUMM01_MT","SESID",idForSession);
425 this.clearTempTableBySesId("LD_BCN_BUILD_OBJTSUMM02_MT","SESID",idForSession);
426 /**
427 * 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.
428 */
429 persistenceService.clearCache();
430
431 }
432
433 public void setPersistenceService(PersistenceService persistenceService)
434 {
435 this.persistenceService = persistenceService;
436 }
437
438 }
439