/*
* 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.BCConstants;
import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionLevelSummaryReportDao;
/**
* report general ledger amounts and FTE from the pending budget by object level
*/
public class BudgetConstructionLevelSummaryReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionLevelSummaryReportDao {
private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionLevelSummaryReportDaoJdbc.class);
protected static ArrayList<SQLForStep> updateReportsLevelSummaryTable = new ArrayList<SQLForStep>(7);
protected ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
public BudgetConstructionLevelSummaryReportDaoJdbc() {
// builds and updates LevelSummaryReports
StringBuilder sqlText = new StringBuilder(1500);
/* insert the income records */
sqlText.append("INSERT INTO LD_BCN_LEVL_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, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, \n");
sqlText.append(" APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'A', objc.fin_report_sort_cd, \n");
sqlText.append(" objl.fin_report_sort_cd, sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), objl.fin_cons_obj_cd, objt.fin_obj_level_cd, \n");
sqlText.append(" 0, 0, 0, 0 \n");
sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc \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 ");
// IN list of revenue object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND objt.fin_object_cd = pbgl.fin_object_cd \n");
sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
sqlText.append(" AND objc.fin_coa_cd = objl.fin_coa_cd \n");
sqlText.append(" AND objc.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objc.fin_report_sort_cd, \n");
sqlText.append(" objl.fin_report_sort_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* insert expenditure records with FTE place holders */
sqlText.append("INSERT INTO LD_BCN_LEVL_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, ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, FIN_CONS_OBJ_CD, FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, \n");
sqlText.append(" APPT_RQST_FTE_QTY, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objc.fin_report_sort_cd, \n");
sqlText.append(" objl.fin_report_sort_cd, sum(pbgl.acln_annl_bal_amt), sum(pbgl.fin_beg_bal_ln_amt), objl.fin_cons_obj_cd, objt.fin_obj_level_cd, 0, \n");
sqlText.append(" 0, 0, 0 \n");
sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PND_BCNSTR_GL_T pbgl, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl, CA_OBJ_CONSOLDTN_T objc \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 ");
// IN list of expenditure object types
insertionPoints.add(sqlText.length());
sqlText.append("\n");
sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND objt.fin_object_cd = pbgl.fin_object_cd \n");
sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
sqlText.append(" AND objc.fin_coa_cd = objl.fin_coa_cd \n");
sqlText.append(" AND objc.fin_cons_obj_cd = objl.fin_cons_obj_cd \n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objc.fin_report_sort_cd, objl.fin_report_sort_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* get the BCAF FTE values */
sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM03_MT \n");
sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
sqlText.append(" FIN_OBJ_LEVEL_CD, APPT_RQCSF_FTE_QTY, APPT_RQST_FTE_QTY) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
sqlText.append(" objt.fin_obj_level_cd, SUM(bcaf.APPT_RQCSF_FTE_QTY), SUM(bcaf.APPT_RQST_FTE_QTY) \n");
sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \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 bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND objt.fin_object_cd = bcaf.fin_object_cd \n");
sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
sqlText.delete(0, sqlText.length());
/* SQL-92 does not allow the target table of an UPDATE to be aliased. Supposedly, PostgreSQL enforces this (Gennick, p.156) */
/* copy the fte values to the report tables */
sqlText.append("UPDATE LD_BCN_LEVL_SUMM_T \n");
sqlText.append("SET appt_rqcsf_fte_qty =\n");
sqlText.append("(SELECT SUM(fq.appt_rqcsf_fte_qty) \n");
sqlText.append("FROM LD_BCN_BUILD_LEVLSUMM03_MT fq \n");
sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
sqlText.append(" AND fq.sesid = ?), \n");
sqlText.append(" appt_rqst_fte_qty = \n");
sqlText.append("(SELECT SUM(fq.appt_rqst_fte_qty) \n");
sqlText.append("FROM LD_BCN_BUILD_LEVLSUMM03_MT fq \n");
sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
sqlText.append(" AND fq.sesid = ?) \n");
sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND EXISTS (SELECT * FROM LD_BCN_BUILD_LEVLSUMM03_MT fq2 \n");
sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq2.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq2.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq2.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq2.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq2.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq2.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq2.fin_obj_level_cd \n");
sqlText.append(" AND fq2.sesid = ? ) \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
sqlText.delete(0, sqlText.length());
/* get the CSF regular FTE */
sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM02_MT \n");
sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
sqlText.append(" FIN_OBJ_LEVEL_CD, POS_CSF_FNDSTAT_CD, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
sqlText.append(" objt.fin_obj_level_cd, NULL, SUM(pos_csf_fte_qty), 0 \n");
sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_CSF_TRCKR_T bcsf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \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 bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND bcsf.account_nbr = ctrl.account_nbr \n");
sqlText.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
sqlText.append(" AND (bcsf.pos_csf_fndstat_cd <> '");
// CSF funding status code for leave
insertionPoints.add(sqlText.length());
sqlText.append("' OR bcsf.pos_csf_fndstat_cd IS NULL) \n");
sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND objt.fin_object_cd = bcsf.fin_object_cd \n");
sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* get the CSF leave FTE */
sqlText.append("INSERT INTO LD_BCN_BUILD_LEVLSUMM02_MT \n");
sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, SUB_FUND_GRP_CD, FIN_COA_CD, INC_EXP_CD, FIN_CONS_OBJ_CD, \n");
sqlText.append(" FIN_OBJ_LEVEL_CD, POS_CSF_FNDSTAT_CD, POS_CSF_FTE_QTY, POS_CSF_LV_FTE_QTY) \n");
sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, 'B', objl.fin_cons_obj_cd, \n");
sqlText.append(" objt.fin_obj_level_cd, '");
// CSF funding status code for leave
insertionPoints.add(sqlText.length());
sqlText.append("', 0, SUM(pos_csf_fte_qty) \n");
sqlText.append("FROM LD_BCN_SUBFUND_PICK_T pick, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_CSF_TRCKR_T bcsf, CA_OBJECT_CODE_T objt, CA_OBJ_LEVEL_T objl \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 bcsf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND bcsf.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND bcsf.account_nbr = ctrl.account_nbr \n");
sqlText.append(" AND bcsf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
sqlText.append(" AND bcsf.pos_csf_fndstat_cd = '");
// CSF funding status code for leave
insertionPoints.add(sqlText.length());
sqlText.append("' \n");
sqlText.append(" AND objt.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
sqlText.append(" AND objt.fin_coa_cd = ctrl.fin_coa_cd \n");
sqlText.append(" AND objt.fin_object_cd = bcsf.fin_object_cd \n");
sqlText.append(" AND objl.fin_coa_cd = objt.fin_coa_cd \n");
sqlText.append(" AND objl.fin_obj_level_cd = objt.fin_obj_level_cd \n");
sqlText.append("GROUP BY ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ctrl.sel_sub_fund_grp, ctrl.fin_coa_cd, objl.fin_cons_obj_cd, objt.fin_obj_level_cd \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText, insertionPoints));
sqlText.delete(0, sqlText.length());
insertionPoints.clear();
/* copy the fte values to the report table */
sqlText.append("UPDATE LD_BCN_LEVL_SUMM_T \n");
sqlText.append("SET pos_csf_fte_qty = ");
sqlText.append("(SELECT SUM(pos_csf_fte_qty) \n");
sqlText.append(" FROM LD_BCN_BUILD_LEVLSUMM02_MT fq \n");
sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
sqlText.append(" AND fq.sesid = ?), \n");
sqlText.append(" pos_csf_lv_fte_qty =\n");
sqlText.append("(SELECT SUM(pos_csf_lv_fte_qty)\n");
sqlText.append(" FROM LD_BCN_BUILD_LEVLSUMM02_MT fq \n");
sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq.fin_obj_level_cd \n");
sqlText.append(" AND fq.sesid = ?) \n");
sqlText.append("WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND EXISTS (SELECT 1 FROM LD_BCN_BUILD_LEVLSUMM02_MT fq2 \n");
sqlText.append(" WHERE LD_BCN_LEVL_SUMM_T.person_unvl_id = ? \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_fin_coa_cd = fq2.org_fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.org_cd = fq2.org_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.sub_fund_grp_cd = fq2.sub_fund_grp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_coa_cd = fq2.fin_coa_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.inc_exp_cd = fq2.inc_exp_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_cons_obj_cd = fq2.fin_cons_obj_cd \n");
sqlText.append(" AND LD_BCN_LEVL_SUMM_T.fin_obj_level_cd = fq2.fin_obj_level_cd \n");
sqlText.append(" AND fq2.sesid = ?) \n");
updateReportsLevelSummaryTable.add(new SQLForStep(sqlText));
sqlText.delete(0, sqlText.length());
}
@Override
public void cleanReportsLevelSummaryTable(String principalName) {
clearTempTableByUnvlId("LD_BCN_LEVL_SUMM_T", "PERSON_UNVL_ID", principalName);
}
@Override
public void updateReportsLevelSummaryTable(String principalName, String expenditureINList, String revenueINList) {
String idForSession = UUID.randomUUID().toString();
ArrayList<String> stringsToInsert = new ArrayList<String>(10);
cleanReportsLevelSummaryTable(principalName);
// insert revenue by object level from pending budget construction general ledger into the report-by-level table
stringsToInsert.add(revenueINList);
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(0).getSQL(stringsToInsert), principalName, principalName);
// insert expenditure by object level from pending budget construction general ledger into the report-by-level table
stringsToInsert.clear();
stringsToInsert.add(expenditureINList);
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(1).getSQL(stringsToInsert), principalName, principalName);
// sum the FTE from appointment funding
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(2).getSQL(), idForSession, principalName);
// update the appointment FTE in the report-by-level table
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(3).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
// sum the non-leave FTE from the CSF
stringsToInsert.clear();
stringsToInsert.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(4).getSQL(stringsToInsert), idForSession, principalName);
// sum the FTE for leaves from the CSF (the leave flag is used twice in this SQL, so just add it again to stringsToInsert)
stringsToInsert.add(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue());
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(5).getSQL(stringsToInsert), idForSession, principalName);
// update all the CSF FTE fields in the report-by-level table
getSimpleJdbcTemplate().update(updateReportsLevelSummaryTable.get(6).getSQL(), principalName, idForSession, principalName, idForSession, principalName, principalName, idForSession);
clearTempTableBySesId("LD_BCN_BUILD_LEVLSUMM02_MT", "SESID", idForSession);
clearTempTableBySesId("LD_BCN_BUILD_LEVLSUMM03_MT", "SESID", idForSession);
}
}