/*
* The Kuali Financial System, a comprehensive financial management system for higher education.
*
* Copyright 2005-2014 The Kuali Foundation
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.kuali.kfs.module.bc.document.dataaccess.impl;
import java.util.ArrayList;
import java.util.UUID;
import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthSummaryReportDao;
import org.kuali.kfs.sys.KFSConstants;
/**
* report general ledger and monthly summaries from the budget by organization, subfund group, and object code
*/
public class BudgetConstructionMonthSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionMonthSummaryReportDao {
private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionMonthSummaryReportDaoJdbc.class);
protected static ArrayList<SQLForStep> updateReportsMonthSummaryTable = new ArrayList<SQLForStep>(9);
public BudgetConstructionMonthSummaryReportDaoJdbc() {
// builds and updates MonthSummaryReports
StringBuilder sqlText = new StringBuilder(2500);
ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
/* sum pending budget income records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, 'A', pbgl.fin_object_cd, '");
// default sub object code
insertionPoints.add(sqlText.length());
sqlText.append("', sum(pbgl.acln_annl_bal_amt) \n");
sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append(" AND pick.report_flag > 0 \n");
sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append(" AND pbgl.fin_obj_typ_cd in ");
// list of revenue object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum pending budget expenditure records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, 'B', pbgl.fin_object_cd, '");
// default sub object code
insertionPoints.add(sqlText.length());
sqlText.append("', sum(pbgl.acln_annl_bal_amt) \n");
sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append(" AND pick.report_flag > 0 \n");
sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append(" AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append(" AND pbgl.fin_obj_typ_cd in ");
// list of expenditure object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
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");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum monthly budget income records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
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");
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");
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");
sqlText.append(" mnth.fin_object_cd, '");
// default sub object code
insertionPoints.add(sqlText.length());
sqlText.append("', sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
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");
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");
sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append("AND pick.report_flag > 0 \n");
sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append("AND mnth.fin_obj_typ_cd in ");
// list of revenue object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, \n");
sqlText.append(" ctrl.univ_fiscal_yr, ctrl.fin_coa_cd, mnth.fin_object_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum monthly budget expenditure records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
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");
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");
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");
sqlText.append(" 'B', mnth.fin_object_cd, '");
// default sub object code
insertionPoints.add(sqlText.length());
sqlText.append("', sum(mnth.fdoc_ln_mo1_amt), sum(mnth.fdoc_ln_mo2_amt), sum(mnth.fdoc_ln_mo3_amt), \n");
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");
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");
sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append(" AND pick.report_flag > 0 \n");
sqlText.append(" AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append(" AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append(" AND mnth.fin_obj_typ_cd in ");
// list of expenditure object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
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");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum to the sub-object code */
/* sum pending budget income records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
sqlText.append(" (SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, 'A', pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, sum(pbgl.acln_annl_bal_amt) \n");
sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append("AND pick.report_flag > 0 \n");
sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append("AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append("AND pbgl.fin_obj_typ_cd in ");
// list of revenue object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum pending budget expenditure records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM01_MT \n");
sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, \n");
sqlText.append(" FIN_COA_CD, INC_EXP_CD, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, ACLN_ANNL_BAL_AMT) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, 'B', pbgl.fin_object_cd, pbgl.fin_sub_obj_cd, sum(pbgl.acln_annl_bal_amt) \n");
sqlText.append("FROM LD_PND_BCNSTR_GL_T pbgl, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append("AND pick.report_flag > 0 \n");
sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append("AND pbgl.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append("AND pbgl.fin_obj_typ_cd in ");
// list of expenditure object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, pbgl.fin_object_cd, pbgl.fin_sub_obj_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum monthly budget income records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
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");
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");
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");
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");
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");
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");
sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append("AND pick.report_flag > 0 \n");
sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append("AND mnth.fin_obj_typ_cd in ");
// list of income object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.univ_fiscal_yr, \n");
sqlText.append(" ctrl.fin_coa_cd, mnth.fin_object_cd, mnth.fin_sub_obj_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* sum monthly budget expenditure records */
sqlText.append("INSERT INTO LD_BCN_BUILD_MNTHSUMM02_MT \n");
sqlText.append("(SESID, SEL_ORG_FIN_COA, SEL_ORG_CD, SEL_SUB_FUND_GRP, UNIV_FISCAL_YR, FIN_COA_CD, INC_EXP_CD, \n");
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");
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");
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");
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");
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");
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");
sqlText.append("FROM LD_BCNSTR_MONTH_T mnth, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_SUBFUND_PICK_T pick \n");
sqlText.append("WHERE pick.person_unvl_id = ? \n");
sqlText.append("AND pick.report_flag > 0 \n");
sqlText.append("AND pick.sub_fund_grp_cd = ctrl.sel_sub_fund_grp \n");
sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
sqlText.append("AND mnth.fdoc_nbr = ctrl.fdoc_nbr \n");
sqlText.append("AND mnth.fin_obj_typ_cd in ");
// list of expenditure object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
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");
sqlText.append(" mnth.fin_object_cd, mnth.fin_sub_obj_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* join the summed values and merge level and consolidation info */
sqlText.append("INSERT INTO LD_BCN_MNTH_SUMM_T \n");
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");
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");
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");
sqlText.append(" FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD) \n");
sqlText.append("SELECT ?, LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_fin_coa, LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_cd, \n");
sqlText.append(" LD_BCN_BUILD_MNTHSUMM01_MT.sel_sub_fund_grp, LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd, \n");
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");
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");
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");
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");
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");
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");
sqlText.append("FROM CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc, (LD_BCN_BUILD_MNTHSUMM01_MT \n");
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");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_fin_coa = LD_BCN_BUILD_MNTHSUMM02_MT.sel_org_fin_coa) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_org_cd = LD_BCN_BUILD_MNTHSUMM02_MT.sel_org_cd) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.sel_sub_fund_grp = LD_BCN_BUILD_MNTHSUMM02_MT.sel_sub_fund_grp) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.univ_fiscal_yr = LD_BCN_BUILD_MNTHSUMM02_MT.univ_fiscal_yr) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_coa_cd) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.inc_exp_cd = LD_BCN_BUILD_MNTHSUMM02_MT.inc_exp_cd) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_object_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_object_cd) AND \n");
sqlText.append(" (LD_BCN_BUILD_MNTHSUMM01_MT.fin_sub_obj_cd = LD_BCN_BUILD_MNTHSUMM02_MT.fin_sub_obj_cd))) \n");
sqlText.append("WHERE LD_BCN_BUILD_MNTHSUMM01_MT.sesid = ?\n");
sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.univ_fiscal_yr = objt.univ_fiscal_yr \n");
sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append("AND LD_BCN_BUILD_MNTHSUMM01_MT.fin_object_cd = objt.fin_object_cd \n");
sqlText.append("AND objt.fin_coa_cd = objl.fin_coa_cd \n");
sqlText.append("AND objt.fin_obj_level_cd = objl.fin_obj_level_cd \n");
sqlText.append("AND objl.fin_coa_cd = objc.fin_coa_cd \n");
sqlText.append("AND objl.fin_cons_obj_cd = objc.fin_cons_obj_cd \n");
updateReportsMonthSummaryTable.add(new SQLForStep(sqlText));
sqlText.delete(0, sqlText.length());
}
@Override
public void cleanReportsMonthSummaryTable(String principalName) {
clearTempTableByUnvlId("LD_BCN_MNTH_SUMM_T", "PERSON_UNVL_ID", principalName);
}
/**
* sums general ledger and montly budgets by subfund and organization to the object-code level
*
* @param principalName--the user requesting the report
* @param idForSession--the session id for the user
* @param revenueINList a SQL IN list containing the budget construction revenue object types
* @param expenditureINList a SQL IN list containing the budget construction expenditure object types
*/
protected void consolidateMonthSummaryReportToObjectCodeLevel(String principalName, String idForSession, String revenueINList, String expenditureINList) {
// set up the things that need to be inserted into the SQL (default sub object code and an object type IN list)
ArrayList<String> revenueInsertions = new ArrayList<String>(2);
ArrayList<String> expenditureInsertions = new ArrayList<String>(2);
revenueInsertions.add(KFSConstants.getDashFinancialSubObjectCode());
revenueInsertions.add(revenueINList);
expenditureInsertions.add(KFSConstants.getDashFinancialSubObjectCode());
expenditureInsertions.add(expenditureINList);
// sum revenue from the pending general ledger to the object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(0).getSQL(revenueInsertions), idForSession, principalName);
// sum expenditure from the pending general ledger to the object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(1).getSQL(expenditureInsertions), idForSession, principalName);
// sum revenue from the monthly budgets to the object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(2).getSQL(revenueInsertions), idForSession, principalName);
// sum expenditure from the monthly budgets to the object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(3).getSQL(expenditureInsertions), idForSession, principalName);
}
/**
* sums general ledger and monthly amounts by organization and subfund group to the sub-object level
*
* @param principalName--the user requesting the report
* @param idForSession--the ID for the user's session
* @param revenueINList a SQL IN list containing the budget construction revenue object types
* @param expenditureINList a SQL IN list containing the budget construction expenditure object types
*/
protected void detailedMonthSummaryTableReport(String principalName, String idForSession, String revenueINList, String expenditureINList) {
// set up the strings to be inserted into the SQL (revenue and expenditure object types
ArrayList<String> revenueInsertions = new ArrayList<String>(2);
ArrayList<String> expenditureInsertions = new ArrayList<String>(2);
revenueInsertions.add(revenueINList);
expenditureInsertions.add(expenditureINList);
// sum revenue from the pending general ledger to the sub-object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(4).getSQL(revenueInsertions), idForSession, principalName);
// sum expenditure from the pending general ledger to the sub-object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(5).getSQL(expenditureInsertions), idForSession, principalName);
// sum revenue from the monthly budgets to the sub-object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(6).getSQL(revenueInsertions), idForSession, principalName);
// sum expenditure from the monthly budgets to the sub-object code level
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(7).getSQL(expenditureInsertions), idForSession, principalName);
}
/**
* @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthSummaryReportDao#updateReportsMonthSummaryTable(java.lang.String,
* boolean, java.lang.String, java.lang.String)
*/
@Override
public void updateReportsMonthSummaryTable(String principalName, boolean consolidateToObjectCodeLevel, String revenueINList, String expenditureINList) {
String idForSession = UUID.randomUUID().toString();
// remove any previous reporting rows for this user
this.cleanReportsMonthSummaryTable(principalName);
if (consolidateToObjectCodeLevel) {
consolidateMonthSummaryReportToObjectCodeLevel(principalName, idForSession, revenueINList, expenditureINList);
}
else {
detailedMonthSummaryTableReport(principalName, idForSession, revenueINList, expenditureINList);
}
// join monthly budgets and general ledger to build the final table for the report
getSimpleJdbcTemplate().update(updateReportsMonthSummaryTable.get(8).getSQL(), principalName, idForSession);
// clear out the user's work table rows for this session
this.clearTempTableBySesId("LD_BCN_BUILD_MNTHSUMM01_MT", "SESID", idForSession);
this.clearTempTableBySesId("LD_BCN_BUILD_MNTHSUMM02_MT", "SESID", idForSession);
}
}