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.gl.dataaccess.impl;
017
018 import java.math.BigDecimal;
019 import java.util.ArrayList;
020 import java.util.Collections;
021 import java.util.List;
022 import java.util.Map;
023
024 import org.kuali.kfs.gl.GeneralLedgerConstants;
025 import org.kuali.kfs.gl.dataaccess.AccountBalanceConsolidationDao;
026 import org.kuali.kfs.gl.service.AccountBalanceService;
027 import org.kuali.kfs.sys.KFSConstants;
028 import org.kuali.kfs.sys.businessobject.SystemOptions;
029 import org.kuali.kfs.sys.businessobject.UniversityDate;
030 import org.kuali.rice.kns.util.Guid;
031 import org.springframework.dao.IncorrectResultSizeDataAccessException;
032 import org.springframework.jdbc.support.rowset.SqlRowSet;
033
034 /**
035 * A class to do the database queries needed to calculate Balance By Consolidation Balance Inquiry Screen
036 */
037 public class AccountBalanceConsolidationDaoJdbc extends AccountBalanceDaoJdbcBase implements AccountBalanceConsolidationDao {
038 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(AccountBalanceConsolidationDaoJdbc.class);
039
040 /**
041 * Returns account balance information that qualifies, based on the inquiry formed out of the parameters
042 *
043 * @param objectTypes the object types of account balances to include in the inquiry
044 * @param universityFiscalYear the fiscal year of account balances to include in the inquiry
045 * @param chartOfAccountsCode the chart of accounts of account balances to include in the inquiry
046 * @param accountNumber the account number of account balances to include in the inquiry
047 * @param isExcludeCostShare whether to exclude cost share entries from this inquiry or not
048 * @param isConsolidated whether the results of the inquiry should be consolidated
049 * @param pendingEntriesCode whether the inquiry should also report results based on no pending entries, approved pending entries, or all pending entries
050 * @return a List of Maps with the report information from this inquiry
051 * @see org.kuali.kfs.gl.dataaccess.AccountBalanceConsolidationDao#findAccountBalanceByConsolidationObjectTypes(java.lang.String[], java.lang.Integer, java.lang.String, java.lang.String, boolean, boolean, int)
052 */
053 public List<Map<String, Object>> findAccountBalanceByConsolidationObjectTypes(String[] objectTypes, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isExcludeCostShare, boolean isConsolidated, int pendingEntriesCode) {
054 LOG.debug("findAccountBalanceByConsolidationObjectTypes() started");
055
056 SystemOptions options = optionsService.getOptions(universityFiscalYear);
057 String sessionId = new Guid().toString();
058 List<Map<String, Object>> data = null;
059
060 try {
061 // Add in all the source data
062 List<Object> params = new ArrayList<Object>(6 + objectTypes.length);
063 params.add(sessionId);
064 params.add(universityFiscalYear);
065 params.add(chartOfAccountsCode);
066 params.add(accountNumber);
067 params.add(universityFiscalYear);
068 params.add(chartOfAccountsCode);
069 Collections.addAll(params, objectTypes);
070 getSimpleJdbcTemplate().update("INSERT INTO FP_INTERIM1_CONS_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, "
071 + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, FIN_OBJ_TYP_CD, SESID ) "
072 + "SELECT a.UNIV_FISCAL_YR, a.FIN_COA_CD, a.ACCOUNT_NBR, a.SUB_ACCT_NBR, "
073 + "a.FIN_OBJECT_CD, a.FIN_SUB_OBJ_CD, a.CURR_BDLN_BAL_AMT, a.ACLN_ACTLS_BAL_AMT, a.ACLN_ENCUM_BAL_AMT, a.TIMESTAMP, SUBSTR(fin_report_sort_cd, 1, 1), "
074 + "o.fin_obj_typ_cd,?"
075 + " FROM GL_ACCT_BALANCES_T a, CA_OBJECT_CODE_T o, CA_OBJ_TYPE_T t"
076 + " WHERE a.univ_fiscal_yr = ?"
077 + " AND a.fin_coa_cd = ?"
078 + " AND a.account_nbr = ?"
079 + " AND a.univ_fiscal_yr = o.univ_fiscal_yr AND a.fin_coa_cd = o.fin_coa_cd "
080 + " AND a.fin_object_cd = o.fin_object_cd AND o.fin_obj_typ_cd = t.fin_obj_typ_cd "
081 + " AND o.univ_fiscal_yr = ? AND o.fin_coa_cd = ? "
082 + " AND o.fin_obj_typ_cd IN " + inString(objectTypes.length), params.toArray());
083
084 // Summarize pending entries into fp_interim1_cons_mt if necessary
085 if ((pendingEntriesCode == AccountBalanceService.PENDING_ALL) || (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED)) {
086 if (getMatchingPendingEntriesByConsolidation(objectTypes, options, universityFiscalYear, chartOfAccountsCode, accountNumber, isExcludeCostShare, pendingEntriesCode, sessionId)) {
087 summarizePendingEntriesByConsolidation(options, sessionId);
088 }
089 }
090
091 // Add some reference data
092 getSimpleJdbcTemplate().update(
093 "INSERT INTO FP_INTERIM2_CONS_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, "
094 + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD,FIN_OBJ_TYP_CD, SESID, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD ) "
095 + "SELECT a.UNIV_FISCAL_YR, a.FIN_COA_CD, a.ACCOUNT_NBR, a.SUB_ACCT_NBR,a.FIN_OBJECT_CD, a.FIN_SUB_OBJ_CD, a.CURR_BDLN_BAL_AMT, a.ACLN_ACTLS_BAL_AMT, "
096 + "a.ACLN_ENCUM_BAL_AMT, a.TIMESTAMP, a.FIN_REPORT_SORT_CD, a.FIN_OBJ_TYP_CD, a.SESID,c.fin_report_sort_cd,c.fin_cons_obj_cd"
097 + " FROM FP_INTERIM1_CONS_MT a,CA_OBJECT_CODE_T o,CA_OBJ_LEVEL_T l,CA_OBJ_CONSOLDTN_T c WHERE a.univ_fiscal_yr = o.univ_fiscal_yr "
098 + " AND a.fin_coa_cd = o.fin_coa_cd AND a.fin_object_cd = o.fin_object_cd AND o.fin_coa_cd = l.fin_coa_cd AND o.fin_obj_level_cd = l.fin_obj_level_cd "
099 + " AND c.fin_coa_cd = l.fin_coa_cd AND c.fin_cons_obj_cd = l.fin_cons_obj_cd AND o.univ_fiscal_yr = ?"
100 + " AND o.fin_coa_cd = ?"
101 + " AND l.fin_coa_cd = ?" + " AND a.SESID = ?", universityFiscalYear, chartOfAccountsCode, chartOfAccountsCode, sessionId);
102
103 // Get rid of stuff we don't need
104 if (isExcludeCostShare) {
105 purgeCostShareEntries("FP_INTERIM2_CONS_MT", "sesid", sessionId);
106 }
107
108 // Summarize
109 if (isConsolidated) {
110 getSimpleJdbcTemplate().update("INSERT INTO FP_BAL_BY_CONS_MT (SUB_ACCT_NBR, FIN_REPORT_SORT_CD, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, "
111 + "ACLN_ENCUM_BAL_AMT, SESID) "
112 + "SELECT '*ALL*',fin_report_sort_cd,cons_fin_report_sort_cd,fin_cons_obj_cd,SUM(curr_bdln_bal_amt), "
113 + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid)"
114 + " FROM FP_INTERIM2_CONS_MT WHERE FP_INTERIM2_CONS_MT.SESID = ?"
115 + " GROUP BY cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd", sessionId);
116 }
117 else {
118 getSimpleJdbcTemplate().update("INSERT INTO FP_BAL_BY_CONS_MT (SUB_ACCT_NBR, FIN_REPORT_SORT_CD, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, "
119 + "ACLN_ENCUM_BAL_AMT, SESID) SELECT sub_acct_nbr, fin_report_sort_cd, cons_fin_report_sort_cd, fin_cons_obj_cd, SUM(curr_bdln_bal_amt), "
120 + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid) "
121 + " FROM FP_INTERIM2_CONS_MT WHERE FP_INTERIM2_CONS_MT.SESID = ?"
122 + " GROUP BY sub_acct_nbr, cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd", sessionId);
123 }
124
125 // Here's the data
126 data = getSimpleJdbcTemplate().queryForList("select SUB_ACCT_NBR, FIN_REPORT_SORT_CD, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT "
127 + "from FP_BAL_BY_CONS_MT where SESID = ?"
128 + " order by fin_report_sort_cd,cons_fin_report_sort_cd", sessionId);
129 }
130 finally {
131 // Clean up everything
132 clearTempTable("FP_BAL_BY_CONS_MT", "SESID", sessionId);
133 clearTempTable("FP_INTERIM1_CONS_MT", "SESID", sessionId);
134 clearTempTable("FP_INTERIM2_CONS_MT", "SESID", sessionId);
135 clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
136 }
137 return data;
138 }
139
140 /**
141 * Finds whether pending entries exist that would change the results of this inquiry
142 *
143 * @param objectTypes the object types to search for
144 * @param options the options table for the fiscal year being inquiring on
145 * @param universityFiscalYear the university fiscal year of account balances being inquired upon
146 * @param chartOfAccountsCode the chart of accounts of account balances being inquired upon
147 * @param accountNumber the account number of account balances being inquired upon
148 * @param isCostShareExcluded whether cost share entries should be excluded
149 * @param pendingEntriesCode is the inquiry for no pending entries, approved pending entries, or all pending entries
150 * @param sessionId the unique session id of the web session of the currently inquiring users, so temp table entries have a unique identifier
151 * @return true if pending entries exist that would affect this inquiry, false otherwise
152 */
153 protected boolean getMatchingPendingEntriesByConsolidation(String[] objectTypes, SystemOptions options, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isCostShareExcluded, int pendingEntriesCode, String sessionId) {
154 LOG.debug("getMatchingPendingEntriesByConsolidation() started");
155
156 // If they have specified this year, we will get all the pending entries
157 // where the year is equal or the year is null
158 // (because most eDocs don't fill in the year field).
159 // If they have specified a previous year, we will get all the pending
160 // entries where the year is equal to their selection
161 // without the nulls (because we will post eDocs
162 // with blank years tonight most probably.
163
164 UniversityDate today = universityDateService.getCurrentUniversityDate();
165
166 clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
167
168 String insertSql = "insert into GL_PENDING_ENTRY_MT (SESID, FS_ORIGIN_CD, FDOC_NBR, TRN_ENTR_SEQ_NBR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, "
169 + " FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD,FIN_OBJ_TYP_CD, UNIV_FISCAL_YR, UNIV_FISCAL_PRD_CD, TRN_LDGR_ENTR_DESC, TRN_LDGR_ENTR_AMT, TRN_DEBIT_CRDT_CD,TRANSACTION_DT, "
170 + " FDOC_TYP_CD, ORG_DOC_NBR, PROJECT_CD, ORG_REFERENCE_ID, FDOC_REF_TYP_CD, FS_REF_ORIGIN_CD,FDOC_REF_NBR, FDOC_REVERSAL_DT, TRN_ENCUM_UPDT_CD, FDOC_APPROVED_CD, "
171 + " ACCT_SF_FINOBJ_CD, TRN_ENTR_OFST_CD,TRNENTR_PROCESS_TM) ";
172
173 String selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR,"
174 + "p.FIN_COA_CD, p.ACCOUNT_NBR, "
175 + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR", "'-----'")
176 + ", p.FIN_OBJECT_CD, "
177 + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD", "'---'")
178 + " , p.FIN_BALANCE_TYP_CD,p.FIN_OBJ_TYP_CD, p.UNIV_FISCAL_YR, p.UNIV_FISCAL_PRD_CD, "
179 + "p.TRN_LDGR_ENTR_DESC, p.TRN_LDGR_ENTR_AMT, p.TRN_DEBIT_CRDT_CD, p.TRANSACTION_DT, p.FDOC_TYP_CD, p.ORG_DOC_NBR, PROJECT_CD, p.ORG_REFERENCE_ID,p.FDOC_REF_TYP_CD, "
180 + "p.FS_REF_ORIGIN_CD,p.FDOC_REF_NBR, p.FDOC_REVERSAL_DT, p.TRN_ENCUM_UPDT_CD, p.FDOC_APPROVED_CD, p.ACCT_SF_FINOBJ_CD,p.TRN_ENTR_OFST_CD,p.TRNENTR_PROCESS_TM "
181 + " FROM GL_PENDING_ENTRY_T p WHERE p.FIN_COA_CD = ? AND p.account_nbr = ? "
182 + " AND p.fin_obj_typ_cd IN " + inString(objectTypes.length)
183 + " AND p.FDOC_APPROVED_CD <> '" + KFSConstants.PENDING_ENTRY_APPROVED_STATUS_CODE.PROCESSED + "' ";
184
185 List<Object> params = new ArrayList<Object>(20);
186 params.add(sessionId);
187 params.add(chartOfAccountsCode);
188 params.add(accountNumber);
189 Collections.addAll(params, objectTypes);
190
191 if (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED) {
192 selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR,"
193 + "p.FIN_COA_CD, p.ACCOUNT_NBR, "
194 + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR", "'-----'")
195 + ", p.FIN_OBJECT_CD, "
196 + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD", "'---'")
197 + " , p.FIN_BALANCE_TYP_CD,p.FIN_OBJ_TYP_CD, p.UNIV_FISCAL_YR, p.UNIV_FISCAL_PRD_CD, "
198 + "p.TRN_LDGR_ENTR_DESC, p.TRN_LDGR_ENTR_AMT, p.TRN_DEBIT_CRDT_CD, p.TRANSACTION_DT, p.FDOC_TYP_CD, p.ORG_DOC_NBR, PROJECT_CD, p.ORG_REFERENCE_ID,p.FDOC_REF_TYP_CD, "
199 + "p.FS_REF_ORIGIN_CD,p.FDOC_REF_NBR, p.FDOC_REVERSAL_DT, p.TRN_ENCUM_UPDT_CD, p.FDOC_APPROVED_CD, p.ACCT_SF_FINOBJ_CD,p.TRN_ENTR_OFST_CD,p.TRNENTR_PROCESS_TM "
200 + " FROM GL_PENDING_ENTRY_T p, FS_DOC_HEADER_T d WHERE p.FIN_COA_CD = ? AND p.account_nbr = ? "
201 + " AND p.fin_obj_typ_cd IN " + inString(objectTypes.length)
202 + " AND p.FDOC_APPROVED_CD <> '" + KFSConstants.PENDING_ENTRY_APPROVED_STATUS_CODE.PROCESSED + "' AND p.FDOC_NBR = d.FDOC_NBR AND d.FDOC_STATUS_CD = '" + KFSConstants.DocumentStatusCodes.APPROVED + "' ";
203 }
204
205 if (today.getUniversityFiscalYear().equals(universityFiscalYear)) {
206 selectSql = selectSql + "AND (p.univ_fiscal_yr is null OR p.univ_fiscal_yr = ? )";
207 params.add(universityFiscalYear);
208 }
209 else {
210 selectSql = selectSql + "AND p.univ_fiscal_yr = ?";
211 params.add(universityFiscalYear);
212 }
213 getSimpleJdbcTemplate().update(insertSql + selectSql, params.toArray());
214
215 if (isCostShareExcluded) {
216 purgeCostShareEntries("GL_PENDING_ENTRY_MT", "sesid", sessionId);
217 }
218
219 if (!hasEntriesInPendingTable(sessionId)) {
220 return false;
221 }
222
223 fixPendingEntryDisplay(universityFiscalYear, sessionId);
224
225 return true;
226 }
227
228 /**
229 * This method summarizes pending entries to temporary tables for easier inclusion into the inquiry
230 *
231 * @param options the system options of the fiscal year that is being inquired upon
232 * @param sessionId the session id of the inquiring user, for a unique primary key in the temporary tables
233 */
234 protected void summarizePendingEntriesByConsolidation(SystemOptions options, String sessionId) {
235 LOG.debug("summarizePendingEntriesByConsolidation() started");
236
237 try {
238 String balanceStatementSql = "SELECT CURR_BDLN_BAL_AMT,ACLN_ACTLS_BAL_AMT,ACLN_ENCUM_BAL_AMT FROM FP_INTERIM1_CONS_MT WHERE sesid = ? AND univ_fiscal_yr = ? "
239 + "AND fin_coa_cd = ? AND account_nbr = ? AND sub_acct_nbr = ? AND fin_object_cd = ? AND fin_sub_obj_cd = ? AND fin_obj_typ_cd = ?";
240
241 String updateBalanceStatementSql = "UPDATE FP_INTERIM1_CONS_MT SET curr_bdln_bal_amt = ?,acln_actls_bal_amt = ?,acln_encum_bal_amt = ? WHERE "
242 + "sesid = ? AND univ_fiscal_yr = ? AND fin_coa_cd = ? AND account_nbr = ? AND sub_acct_nbr = ? AND fin_object_cd = ? AND fin_sub_obj_cd = ? AND fin_obj_typ_cd = ?";
243
244 String insertBalanceStatementSql = "INSERT INTO FP_INTERIM1_CONS_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, "
245 + "FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, FIN_OBJ_TYP_CD, SESID) "
246 + "VALUES (?,?,?,?,?,?,?,?,?," + getDbPlatform().getCurTimeFunction() + ",?,?,? )";
247
248 SqlRowSet pendingEntryRowSet = getJdbcTemplate().queryForRowSet("SELECT b.FIN_OFFST_GNRTN_CD,t.FIN_OBJTYP_DBCR_CD,t.fin_report_sort_cd,e.UNIV_FISCAL_YR, e.FIN_COA_CD, e.ACCOUNT_NBR, e.SUB_ACCT_NBR, e.FIN_OBJECT_CD, e.FIN_SUB_OBJ_CD, e.FIN_BALANCE_TYP_CD, e.TRN_DEBIT_CRDT_CD, e.TRN_LDGR_ENTR_AMT, oc.FIN_OBJ_TYP_CD "
249 + "FROM GL_PENDING_ENTRY_MT e,CA_OBJ_TYPE_T t,CA_BALANCE_TYPE_T b, CA_OBJECT_CODE_T oc "
250 + "WHERE e.SESID = ? "
251 + "AND e.fin_coa_cd = oc.fin_coa_cd "
252 + "AND e.fin_object_cd = oc.fin_object_cd "
253 + "AND e.univ_fiscal_yr = oc.univ_fiscal_yr "
254 + "AND oc.FIN_OBJ_TYP_CD = t.FIN_OBJ_TYP_CD "
255 + "AND e.fin_balance_typ_cd = b.fin_balance_typ_cd "
256 + "ORDER BY e.univ_fiscal_yr,e.account_nbr,e.sub_acct_nbr,e.fin_object_cd,e.fin_sub_obj_cd,e.fin_obj_typ_cd", new Object[] { sessionId });
257
258 int updateCount = 0;
259 int insertCount = 0;
260
261 while (pendingEntryRowSet.next()) {
262 String sortCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.REPORT_SORT_CODE);
263 if (sortCode.length() > 1) {
264 sortCode = sortCode.substring(0, 1);
265 }
266 Map<String, Object> balance = null;
267 try {
268 balance = getSimpleJdbcTemplate().queryForMap(balanceStatementSql, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE));
269 }
270 catch (IncorrectResultSizeDataAccessException ex) {
271 if (ex.getActualSize() != 0) {
272 LOG.error("balance request sql returned more than one row, aborting", ex);
273 throw ex;
274 }
275 // no rows returned - that's ok
276 }
277
278 String balanceType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE);
279 String objectType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE);
280 String debitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.DEBIT_CREDIT_CODE);
281 String objectTypeDebitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_DEBIT_CREDIT_CODE);
282 String offsetGenerationCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OFFSET_GENERATION_CODE);
283
284 if (balance != null) {
285 updateCount++;
286
287 BigDecimal budget = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.CURRENT_BDLN_BALANCE_AMOUNT);
288 BigDecimal actual = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ACTUALS_BALANCE_AMOUNT);
289 BigDecimal encumb = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ENCUMBRANCE_BALANCE_AMOUNT);
290
291 if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
292 budget = budget.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
293 }
294 else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
295 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
296 actual = actual.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
297 }
298 else {
299 actual = actual.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
300 }
301 }
302 else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
303 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
304 encumb = encumb.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
305 }
306 else {
307 encumb = encumb.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
308 }
309 }
310
311 // A balance exists, so we need to update it
312 getSimpleJdbcTemplate().update(updateBalanceStatementSql, budget, actual, encumb, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE));
313 }
314 else {
315 insertCount++;
316
317 BigDecimal budget = new BigDecimal("0");
318 BigDecimal actual = new BigDecimal("0");
319 BigDecimal encumb = new BigDecimal("0");
320
321 if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
322 budget = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
323 }
324 else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
325 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
326 actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
327 }
328 else {
329 actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
330 }
331 }
332 else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
333 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
334 encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
335 }
336 else {
337 encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
338 }
339 }
340
341 // No balance exists, so we need to insert one
342 getSimpleJdbcTemplate().update(insertBalanceStatementSql, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), budget, actual, encumb, sortCode, pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE), sessionId);
343 }
344 }
345 LOG.info("summarizePendingEntriesByConsolidation() INSERTS: " + insertCount);
346 LOG.info("summarizePendingEntriesByConsolidation() UPDATES: " + updateCount);
347 }
348 catch (Exception e) {
349 LOG.error("summarizePendingEntriesByConsolidation() Exception running sql", e);
350 throw new RuntimeException("Unable to execute: " + e.getMessage(), e);
351 }
352 }
353
354 }