View Javadoc
1   /*
2    * The Kuali Financial System, a comprehensive financial management system for higher education.
3    * 
4    * Copyright 2005-2014 The Kuali Foundation
5    * 
6    * This program is free software: you can redistribute it and/or modify
7    * it under the terms of the GNU Affero General Public License as
8    * published by the Free Software Foundation, either version 3 of the
9    * License, or (at your option) any later version.
10   * 
11   * This program is distributed in the hope that it will be useful,
12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14   * GNU Affero General Public License for more details.
15   * 
16   * You should have received a copy of the GNU Affero General Public License
17   * along with this program.  If not, see <http://www.gnu.org/licenses/>.
18   */
19  package org.kuali.kfs.module.bc.document.dataaccess.impl;
20  
21  import java.util.ArrayList;
22  
23  import org.kuali.kfs.module.bc.BCConstants;
24  import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
25  import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionObjectSummaryReportDao;
26  
27  public class BudgetConstructionObjectSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionObjectSummaryReportDao {
28  
29      protected static ArrayList<SQLForStep> objectSummarySql = new ArrayList<SQLForStep>(5);
30  
31      public BudgetConstructionObjectSummaryReportDaoJdbc() {
32  
33          StringBuilder sqlBuilder = new StringBuilder(1500);
34          ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
35  
36  
37         // build the INSERT SQL for the main table
38         sqlBuilder.append("INSERT INTO LD_BCN_OBJT_SUMM_T\n");
39         sqlBuilder.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD,\n");
40         sqlBuilder.append("INC_EXP_CD, FIN_CONS_SORT_CD, FIN_LEV_SORT_CD, FIN_OBJECT_CD, ACLN_ANNL_BAL_AMT,\n");
41         sqlBuilder.append("FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY,\n");
42         sqlBuilder.append("APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY)\n");
43         sqlBuilder.append("SELECT\n");
44         sqlBuilder.append("?,\n");
45         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
46         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
47         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
48         sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,'A',\n");
49         sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
50         sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
51         sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
52         sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT),\n");
53         sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT),\n");
54         sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
55         sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD,\n");
56         sqlBuilder.append("0,0,0,0\n");
57         sqlBuilder.append("   FROM LD_BCN_SUBFUND_PICK_T,\n");
58         sqlBuilder.append("        LD_BCN_CTRL_LIST_T,\n");
59         sqlBuilder.append("        LD_PND_BCNSTR_GL_T,\n");
60         sqlBuilder.append("        CA_OBJECT_CODE_T,\n");
61         sqlBuilder.append("        CA_OBJ_LEVEL_T,\n");
62         sqlBuilder.append("        CA_OBJ_CONSOLDTN_T\n");
63         sqlBuilder.append("  WHERE (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = ?)\n");
64         sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.REPORT_FLAG > 0)\n");
65         sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.SUB_FUND_GRP_CD = LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP)\n");
66         sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = LD_BCN_CTRL_LIST_T.PERSON_UNVL_ID)\n");
67         sqlBuilder.append("    AND (CA_OBJ_CONSOLDTN_T.FIN_COA_CD = CA_OBJ_LEVEL_T.FIN_COA_CD)\n");
68         sqlBuilder.append("    AND (CA_OBJ_CONSOLDTN_T.FIN_CONS_OBJ_CD = CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD)\n");
69         sqlBuilder.append("    AND (LD_PND_BCNSTR_GL_T.FDOC_NBR = LD_BCN_CTRL_LIST_T.FDOC_NBR)\n");
70         sqlBuilder.append("    AND (CA_OBJECT_CODE_T.UNIV_FISCAL_YR = LD_BCN_CTRL_LIST_T.UNIV_FISCAL_YR)\n");
71         sqlBuilder.append("    AND (CA_OBJECT_CODE_T.FIN_COA_CD = LD_BCN_CTRL_LIST_T.FIN_COA_CD)\n");
72         sqlBuilder.append("    AND (CA_OBJECT_CODE_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
73         sqlBuilder.append("    AND (CA_OBJ_LEVEL_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD)\n");
74         sqlBuilder.append("    AND (CA_OBJ_LEVEL_T.FIN_OBJ_LEVEL_CD = CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD)\n");
75         sqlBuilder.append("    AND (LD_PND_BCNSTR_GL_T.FIN_OBJ_TYP_CD IN ");
76         // income object type IN list
77         insertionPoints.add(sqlBuilder.length());
78         sqlBuilder.append(")\n");
79         sqlBuilder.append("GROUP BY LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
80         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
81         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
82         sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,\n");
83         sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
84         sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
85         sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
86         sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
87         sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD\n");
88         sqlBuilder.append("UNION ALL\n");
89         sqlBuilder.append("SELECT\n");
90         sqlBuilder.append("?,\n");
91         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
92         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
93         sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
94         sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,");
95         sqlBuilder.append("'B',\n");
96         sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
97         sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
98         sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
99         sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.ACLN_ANNL_BAL_AMT),\n");
100        sqlBuilder.append("sum(LD_PND_BCNSTR_GL_T.FIN_BEG_BAL_LN_AMT),\n");
101        sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
102        sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD,\n");
103        sqlBuilder.append("0,0,0,0\n");
104        sqlBuilder.append("   FROM LD_BCN_SUBFUND_PICK_T,\n");
105        sqlBuilder.append("        LD_BCN_CTRL_LIST_T,\n");
106        sqlBuilder.append("        LD_PND_BCNSTR_GL_T,\n");
107        sqlBuilder.append("        CA_OBJECT_CODE_T,\n");
108        sqlBuilder.append("        CA_OBJ_LEVEL_T,\n");
109        sqlBuilder.append("        CA_OBJ_CONSOLDTN_T\n");
110        sqlBuilder.append("  WHERE (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = ?)\n");
111        sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.REPORT_FLAG > 0)\n");
112        sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.SUB_FUND_GRP_CD = LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP)\n");
113        sqlBuilder.append("    AND (LD_BCN_SUBFUND_PICK_T.PERSON_UNVL_ID = LD_BCN_CTRL_LIST_T.PERSON_UNVL_ID)\n");
114        sqlBuilder.append("    AND (CA_OBJ_CONSOLDTN_T.FIN_COA_CD = CA_OBJ_LEVEL_T.FIN_COA_CD)\n");
115        sqlBuilder.append("    AND (CA_OBJ_CONSOLDTN_T.FIN_CONS_OBJ_CD = CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD)\n");
116        sqlBuilder.append("    AND (LD_PND_BCNSTR_GL_T.FDOC_NBR = LD_BCN_CTRL_LIST_T.FDOC_NBR)\n");
117        sqlBuilder.append("    AND (CA_OBJECT_CODE_T.UNIV_FISCAL_YR = LD_BCN_CTRL_LIST_T.UNIV_FISCAL_YR)\n");
118        sqlBuilder.append("    AND (CA_OBJECT_CODE_T.FIN_COA_CD = LD_BCN_CTRL_LIST_T.FIN_COA_CD)\n");
119        sqlBuilder.append("    AND (CA_OBJECT_CODE_T.FIN_OBJECT_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)\n");
120        sqlBuilder.append("    AND (CA_OBJ_LEVEL_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD)\n");
121        sqlBuilder.append("    AND (CA_OBJ_LEVEL_T.FIN_OBJ_LEVEL_CD = CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD)\n");
122        sqlBuilder.append("    AND (LD_PND_BCNSTR_GL_T.FIN_OBJ_TYP_CD IN ");
123        // expenditure object type IN list
124        insertionPoints.add(sqlBuilder.length());
125        sqlBuilder.append(")\n");
126        sqlBuilder.append("GROUP BY LD_BCN_CTRL_LIST_T.SEL_ORG_FIN_COA,\n");
127        sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_ORG_CD,\n");
128        sqlBuilder.append("LD_BCN_CTRL_LIST_T.SEL_SUB_FUND_GRP,\n");
129        sqlBuilder.append("LD_BCN_CTRL_LIST_T.FIN_COA_CD,\n");
130        sqlBuilder.append("CA_OBJ_CONSOLDTN_T.FIN_REPORT_SORT_CD,\n");
131        sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_REPORT_SORT_CD,\n");
132        sqlBuilder.append("LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD,\n");
133        sqlBuilder.append("CA_OBJ_LEVEL_T.FIN_CONS_OBJ_CD,\n");
134        sqlBuilder.append("CA_OBJECT_CODE_T.FIN_OBJ_LEVEL_CD\n");
135 
136        objectSummarySql.add(new SQLForStep(sqlBuilder,insertionPoints));
137        sqlBuilder.delete(0,sqlBuilder.length());
138        insertionPoints.clear();
139 
140        // SQL to get the FTE amounts from appointment funding that match with the expenditure
141       sqlBuilder.append("INSERT INTO LD_BCN_BUILD_OBJTSUMM01_MT\n");
142       sqlBuilder.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD,\n");
143       sqlBuilder.append(" INC_EXP_CD, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, FIN_OBJECT_CD,\n");
144       sqlBuilder.append(" APPT_RQCSF_FTE_QTY, APPT_RQST_FTE_QTY)\n");
145       sqlBuilder.append("(SELECT\n");
146       sqlBuilder.append(" ?,\n");
147       sqlBuilder.append(" ctrl.sel_org_fin_coa,\n");
148       sqlBuilder.append(" ctrl.sel_org_cd,\n");
149       sqlBuilder.append(" ctrl.sel_sub_fund_grp,\n");
150       sqlBuilder.append(" ctrl.fin_coa_cd,\n");
151       sqlBuilder.append(" 'B',\n");
152       sqlBuilder.append( "objl.fin_cons_obj_cd,\n");
153       sqlBuilder.append(" objt.fin_obj_level_cd,\n");
154       sqlBuilder.append(" bcaf.fin_object_cd,\n");
155       sqlBuilder.append(" SUM(bcaf.appt_rqcsf_fte_qty),\n");
156       sqlBuilder.append(" SUM(bcaf.appt_rqst_fte_qty)\n");
157       sqlBuilder.append(" FROM LD_BCN_SUBFUND_PICK_T pick,\n");
158       sqlBuilder.append("      LD_BCN_CTRL_LIST_T ctrl,\n");
159       sqlBuilder.append("      LD_PNDBC_APPTFND_T bcaf,\n");
160       sqlBuilder.append("      CA_OBJECT_CODE_T objt,\n");
161       sqlBuilder.append("      CA_OBJ_LEVEL_T objl\n");
162       sqlBuilder.append(" WHERE pick.person_unvl_id = ?\n");
163       sqlBuilder.append("   AND pick.report_flag > 0\n");
164       sqlBuilder.append("   AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
165       sqlBuilder.append("   AND pick.person_unvl_id = ctrl.person_unvl_id\n");
166       sqlBuilder.append("   AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
167       sqlBuilder.append("   AND bcaf.fin_coa_cd = ctrl.fin_coa_cd\n");
168       sqlBuilder.append("   AND bcaf.account_nbr = ctrl.account_nbr\n");
169       sqlBuilder.append("   AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
170       sqlBuilder.append("   AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
171       sqlBuilder.append("   AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
172       sqlBuilder.append("   AND objt.fin_object_cd = bcaf.fin_object_cd\n");
173       sqlBuilder.append("   AND objl.fin_coa_cd = objt.fin_coa_cd\n");
174       sqlBuilder.append("   AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
175       sqlBuilder.append(" GROUP BY ctrl.sel_org_fin_coa,\n");
176       sqlBuilder.append("          ctrl.sel_org_cd,\n");
177       sqlBuilder.append("          ctrl.sel_sub_fund_grp,\n");
178       sqlBuilder.append("          ctrl.fin_coa_cd,\n");
179       sqlBuilder.append("          objl.fin_cons_obj_cd,\n");
180       sqlBuilder.append("          objt.fin_obj_level_cd,\n");
181       sqlBuilder.append("          bcaf.fin_object_cd)");
182       objectSummarySql.add(new SQLForStep(sqlBuilder));
183       sqlBuilder.delete(0,sqlBuilder.length());
184 
185       // 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.)
186       sqlBuilder.append("UPDATE LD_BCN_OBJT_SUMM_T\n");
187       sqlBuilder.append("SET appt_rqcsf_fte_qty =\n");
188       sqlBuilder.append("  (SELECT SUM(fq.appt_rqcsf_fte_qty)\n");
189       sqlBuilder.append("   FROM LD_BCN_BUILD_OBJTSUMM01_MT fq\n");
190       sqlBuilder.append("   WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
191       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
192       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
193       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
194       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
195       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
196       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
197       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
198       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
199       sqlBuilder.append("     AND fq.sesid = ?),\n");
200       sqlBuilder.append("   appt_rqst_fte_qty =\n");
201       sqlBuilder.append("  (SELECT  SUM(fq.appt_rqst_fte_qty)\n");
202       sqlBuilder.append("   FROM LD_BCN_BUILD_OBJTSUMM01_MT fq\n");
203       sqlBuilder.append("   WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
204       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
205       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
206       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
207       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
208       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
209       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
210       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
211       sqlBuilder.append("     AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
212       sqlBuilder.append("     AND fq.sesid = ?)\n");
213       sqlBuilder.append("WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
214       sqlBuilder.append("  AND EXISTS (SELECT 1\n");
215       sqlBuilder.append("              FROM LD_BCN_BUILD_OBJTSUMM01_MT fq2\n");
216       sqlBuilder.append("              WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
217       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq2.org_fin_coa_cd\n");
218       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq2.org_cd\n");
219       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq2.sub_fund_grp_cd\n");
220       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq2.fin_coa_cd\n");
221       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq2.inc_exp_cd\n");
222       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq2.fin_cons_obj_cd\n");
223       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq2.fin_obj_level_cd\n");
224       sqlBuilder.append("                AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq2.fin_object_cd\n");
225       sqlBuilder.append("                AND fq2.sesid = ?)");
226 
227       objectSummarySql.add(new SQLForStep(sqlBuilder));
228       sqlBuilder.delete(0,sqlBuilder.length());
229 
230       // sum the base (CSF for the current year) FTE into a holding table
231       sqlBuilder.append("INSERT INTO LD_BCN_BUILD_OBJTSUMM02_MT\n");
232       sqlBuilder.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD,\n");
233       sqlBuilder.append(" FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, FIN_OBJECT_CD, POS_CSF_FNDSTAT_CD,\n");
234       sqlBuilder.append(" POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY)\n");
235       sqlBuilder.append("SELECT\n");
236       sqlBuilder.append("  ?,\n");
237       sqlBuilder.append("  ctrl.sel_org_fin_coa,\n");
238       sqlBuilder.append("  ctrl.sel_org_cd,\n");
239       sqlBuilder.append("  ctrl.sel_sub_fund_grp,\n");
240       sqlBuilder.append("  ctrl.fin_coa_cd,\n");
241       sqlBuilder.append("  'B',\n");
242       sqlBuilder.append("  objl.fin_cons_obj_cd,\n");
243       sqlBuilder.append("  objt.fin_obj_level_cd,\n");
244       sqlBuilder.append("  bcsf.fin_object_cd,\n");
245       sqlBuilder.append("  NULL,\n");
246       sqlBuilder.append("  SUM(bcsf.pos_csf_fte_qty),\n");
247       sqlBuilder.append("  0\n");
248       sqlBuilder.append("FROM LD_BCN_SUBFUND_PICK_T pick,\n");
249       sqlBuilder.append("  LD_BCN_CTRL_LIST_T ctrl,\n");
250       sqlBuilder.append("  LD_BCN_CSF_TRCKR_T bcsf,\n");
251       sqlBuilder.append("  CA_OBJECT_CODE_T objt,\n");
252       sqlBuilder.append("  CA_OBJ_LEVEL_T objl\n");
253       sqlBuilder.append("WHERE pick.person_unvl_id = ?\n");
254       sqlBuilder.append("  AND pick.report_flag > 0\n");
255       sqlBuilder.append("  AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
256       sqlBuilder.append("  AND pick.person_unvl_id = ctrl.person_unvl_id\n");
257       sqlBuilder.append("  AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
258       sqlBuilder.append("  AND bcsf.fin_coa_cd = ctrl.fin_coa_cd\n");
259       sqlBuilder.append("  AND bcsf.account_nbr = ctrl.account_nbr\n");
260       sqlBuilder.append("  AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
261       sqlBuilder.append("  AND (bcsf.pos_csf_fndstat_cd <> '");
262       // CSF LEAVE funding status
263       insertionPoints.add(sqlBuilder.length());
264       sqlBuilder.append("' OR bcsf.pos_csf_fndstat_cd IS NULL)\n");
265       sqlBuilder.append("  AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
266       sqlBuilder.append("  AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
267       sqlBuilder.append("  AND objt.fin_object_cd = bcsf.fin_object_cd\n");
268       sqlBuilder.append("  AND objl.fin_coa_cd = objt.fin_coa_cd\n");
269       sqlBuilder.append("  AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
270       sqlBuilder.append("GROUP BY ctrl.sel_org_fin_coa,\n");
271       sqlBuilder.append("         ctrl.sel_org_cd,\n");
272       sqlBuilder.append("         ctrl.sel_sub_fund_grp,\n");
273       sqlBuilder.append("         ctrl.fin_coa_cd,\n");
274       sqlBuilder.append("         objl.fin_cons_obj_cd,\n");
275       sqlBuilder.append("         objt.fin_obj_level_cd,\n");
276       sqlBuilder.append("         bcsf.fin_object_cd\n");
277       sqlBuilder.append("UNION ALL\n");
278       sqlBuilder.append("SELECT\n");
279       sqlBuilder.append("?,\n");
280       sqlBuilder.append("ctrl.sel_org_fin_coa,\n");
281       sqlBuilder.append("ctrl.sel_org_cd,\n");
282       sqlBuilder.append("ctrl.sel_sub_fund_grp,\n");
283       sqlBuilder.append("ctrl.fin_coa_cd,\n");
284       sqlBuilder.append("'B',\n");
285       sqlBuilder.append("objl.fin_cons_obj_cd,\n");
286       sqlBuilder.append("objt.fin_obj_level_cd,\n");
287       sqlBuilder.append("bcsf.fin_object_cd,\n");
288       sqlBuilder.append("'");
289       // CSF LEAVE funding status
290       insertionPoints.add(sqlBuilder.length());
291       sqlBuilder.append("',\n");
292       sqlBuilder.append("0,\n");
293       sqlBuilder.append("    SUM(bcsf.pos_csf_fte_qty)\n");
294       sqlBuilder.append("FROM LD_BCN_SUBFUND_PICK_T pick,\n");
295       sqlBuilder.append("    LD_BCN_CTRL_LIST_T ctrl,\n");
296       sqlBuilder.append("    LD_BCN_CSF_TRCKR_T bcsf,\n");
297       sqlBuilder.append("    CA_OBJECT_CODE_T objt,\n");
298       sqlBuilder.append("    CA_OBJ_LEVEL_T objl\n");
299       sqlBuilder.append("WHERE pick.person_unvl_id = ?\n");
300       sqlBuilder.append("  AND pick.report_flag > 0\n");
301       sqlBuilder.append("  AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp\n");
302       sqlBuilder.append("  AND pick.person_unvl_id = ctrl.person_unvl_id\n");
303       sqlBuilder.append("  AND bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
304       sqlBuilder.append("  AND bcsf.fin_coa_cd = ctrl.fin_coa_cd\n");
305       sqlBuilder.append("  AND bcsf.account_nbr = ctrl.account_nbr\n");
306       sqlBuilder.append("  AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr\n");
307       sqlBuilder.append("  AND bcsf.pos_csf_fndstat_cd = '");
308       // CSF LEAVE funding status
309       insertionPoints.add(sqlBuilder.length());
310       sqlBuilder.append("'\n");
311       sqlBuilder.append("  AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr\n");
312       sqlBuilder.append("  AND objt.fin_coa_cd = ctrl.fin_coa_cd\n");
313       sqlBuilder.append("  AND objt.fin_object_cd = bcsf.fin_object_cd\n");
314       sqlBuilder.append("  AND objl.fin_coa_cd = objt.fin_coa_cd\n");
315       sqlBuilder.append("  AND objl.fin_obj_level_cd = objt.fin_obj_level_cd\n");
316       sqlBuilder.append("GROUP BY ctrl.sel_org_fin_coa,\n");
317       sqlBuilder.append("    ctrl.sel_org_cd,\n");
318       sqlBuilder.append("    ctrl.sel_sub_fund_grp,\n");
319       sqlBuilder.append("    ctrl.fin_coa_cd,\n");
320       sqlBuilder.append("    objl.fin_cons_obj_cd,\n");
321       sqlBuilder.append("    objt.fin_obj_level_cd,\n");
322       sqlBuilder.append("    bcsf.fin_object_cd\n");
323 
324       objectSummarySql.add(new SQLForStep(sqlBuilder,insertionPoints));
325       sqlBuilder.delete(0,sqlBuilder.length());
326       insertionPoints.clear();
327 
328       // 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.)
329       sqlBuilder.append("UPDATE LD_BCN_OBJT_SUMM_T\n");
330       sqlBuilder.append("SET LD_BCN_OBJT_SUMM_T.POS_CSF_FTE_QTY =\n");
331       sqlBuilder.append("        (SELECT SUM(fq.pos_csf_fte_qty)\n");
332       sqlBuilder.append("         FROM LD_BCN_BUILD_OBJTSUMM02_MT fq\n");
333       sqlBuilder.append("         WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
334       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
335       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
336       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
337       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
338       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
339       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
340       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
341       sqlBuilder.append("           AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
342       sqlBuilder.append("           AND fq.sesid = ?),\n");
343       sqlBuilder.append("     LD_BCN_OBJT_SUMM_T.POS_CSF_LV_FTE_QTY =\n");
344       sqlBuilder.append("         (SELECT SUM(fq.pos_csf_lv_fte_qty)\n");
345       sqlBuilder.append("          FROM LD_BCN_BUILD_OBJTSUMM02_MT fq\n");
346       sqlBuilder.append("          WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
347       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq.org_fin_coa_cd\n");
348       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq.org_cd\n");
349       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq.sub_fund_grp_cd\n");
350       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq.fin_coa_cd\n");
351       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq.inc_exp_cd\n");
352       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq.fin_cons_obj_cd\n");
353       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq.fin_obj_level_cd\n");
354       sqlBuilder.append("            AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq.fin_object_cd\n");
355       sqlBuilder.append("            AND fq.sesid = ?)\n");
356       sqlBuilder.append("    WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
357       sqlBuilder.append("      AND EXISTS (SELECT 1\n");
358       sqlBuilder.append("                  FROM LD_BCN_BUILD_OBJTSUMM02_MT fq2\n");
359       sqlBuilder.append("                  WHERE LD_BCN_OBJT_SUMM_T.PERSON_UNVL_ID = ?\n");
360       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.ORG_FIN_COA_CD = fq2.org_fin_coa_cd\n");
361       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.ORG_CD = fq2.org_cd\n");
362       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.SUB_FUND_GRP_CD = fq2.sub_fund_grp_cd\n");
363       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.FIN_COA_CD = fq2.fin_coa_cd\n");
364       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.INC_EXP_CD = fq2.inc_exp_cd\n");
365       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.FIN_CONS_OBJ_CD = fq2.fin_cons_obj_cd\n");
366       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.FIN_OBJ_LEVEL_CD = fq2.fin_obj_level_cd\n");
367       sqlBuilder.append("                    AND LD_BCN_OBJT_SUMM_T.FIN_OBJECT_CD = fq2.fin_object_cd\n");
368       sqlBuilder.append("                    AND fq2.sesid = ?)");
369 
370         objectSummarySql.add(new SQLForStep(sqlBuilder));
371         sqlBuilder.delete(0, sqlBuilder.length());
372 
373 
374     }
375 
376     /**
377      * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionObjectSummaryReportDao#cleanGeneralLedgerObjectSummaryTable(java.lang.String)
378      */
379     @Override
380     public void cleanGeneralLedgerObjectSummaryTable(String principalName) {
381         this.clearTempTableByUnvlId("LD_BCN_OBJT_SUMM_T", "PERSON_UNVL_ID", principalName);
382     }
383 
384 
385     /**
386      * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionObjectSummaryReportDao#updateGeneralLedgerObjectSummaryTable(java.lang.String, java.lang.String, java.lang.String)
387      */
388     @Override
389     public void updateGeneralLedgerObjectSummaryTable(String principalName, String revenueINList, String expenditureINList) {
390         String idForSession = java.util.UUID.randomUUID().toString();
391         ArrayList<String> inLists = new ArrayList<String>(2);
392         inLists.add(revenueINList);
393         inLists.add(expenditureINList);
394 
395         // get rid of anything left over from the last time this user ran this report
396         cleanGeneralLedgerObjectSummaryTable(principalName);
397 
398         // insert the general ledger amounts into the report table, with 0 placeholders for the FTE
399         getSimpleJdbcTemplate().update(objectSummarySql.get(0).getSQL(inLists), principalName, principalName, principalName, principalName);
400 
401         // sum up the FTE from the appointment funding and stick it in a holding table
402         getSimpleJdbcTemplate().update(objectSummarySql.get(1).getSQL(), idForSession, principalName);
403 
404         // set the FTE in the report table using the appointment funding FTE from the holding table
405         getSimpleJdbcTemplate().update(objectSummarySql.get(2).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
406 
407         // sum up the FTE from the CSF tracker (base funding) table and stick it in a holding table
408         ArrayList<String> csfLeaveIndicator = new ArrayList<String>(3);
409         csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
410         csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
411         csfLeaveIndicator.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
412         getSimpleJdbcTemplate().update(objectSummarySql.get(3).getSQL(csfLeaveIndicator), idForSession, principalName, idForSession, principalName);
413 
414         // set the CSF FTE in the report table using the FTE from the holding table
415         getSimpleJdbcTemplate().update(objectSummarySql.get(4).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
416 
417         // clean out this session's rows from the holding tables used
418         this.clearTempTableBySesId("LD_BCN_BUILD_OBJTSUMM01_MT", "SESID", idForSession);
419         this.clearTempTableBySesId("LD_BCN_BUILD_OBJTSUMM02_MT", "SESID", idForSession);
420 
421     }
422 
423 }