View Javadoc
1   /*
2    * Copyright 2007 The Kuali Foundation
3    * 
4    * Licensed under the Educational Community License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    * 
8    * http://www.opensource.org/licenses/ecl2.php
9    * 
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  package org.kuali.ole.gl.dataaccess.impl;
17  
18  import java.math.BigDecimal;
19  import java.util.ArrayList;
20  import java.util.Collections;
21  import java.util.List;
22  import java.util.Map;
23  
24  import org.kuali.ole.gl.GeneralLedgerConstants;
25  import org.kuali.ole.gl.dataaccess.AccountBalanceConsolidationDao;
26  import org.kuali.ole.gl.service.AccountBalanceService;
27  import org.kuali.ole.sys.OLEConstants;
28  import org.kuali.ole.sys.businessobject.SystemOptions;
29  import org.kuali.ole.sys.businessobject.UniversityDate;
30  import org.springframework.dao.IncorrectResultSizeDataAccessException;
31  import org.springframework.jdbc.support.rowset.SqlRowSet;
32  
33  /**
34   * A class to do the database queries needed to calculate Balance By Consolidation Balance Inquiry Screen
35   */
36  public class AccountBalanceConsolidationDaoJdbc extends AccountBalanceDaoJdbcBase implements AccountBalanceConsolidationDao {
37      private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(AccountBalanceConsolidationDaoJdbc.class);
38  
39      /**
40       * Returns account balance information that qualifies, based on the inquiry formed out of the parameters
41       * 
42       * @param objectTypes the object types of account balances to include in the inquiry
43       * @param universityFiscalYear the fiscal year of account balances to include in the inquiry
44       * @param chartOfAccountsCode the chart of accounts of account balances to include in the inquiry
45       * @param accountNumber the account number of account balances to include in the inquiry
46       * @param isExcludeCostShare whether to exclude cost share entries from this inquiry or not
47       * @param isConsolidated whether the results of the inquiry should be consolidated
48       * @param pendingEntriesCode whether the inquiry should also report results based on no pending entries, approved pending
49       *        entries, or all pending entries
50       * @return a List of Maps with the report information from this inquiry
51       * @see org.kuali.ole.gl.dataaccess.AccountBalanceConsolidationDao#findAccountBalanceByConsolidationObjectTypes(java.lang.String[],
52       *      java.lang.Integer, java.lang.String, java.lang.String, boolean, boolean, int)
53       */
54      @Override
55      public List<Map<String, Object>> findAccountBalanceByConsolidationObjectTypes(String[] objectTypes, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isExcludeCostShare, boolean isConsolidated, int pendingEntriesCode, SystemOptions options, UniversityDate today) {
56          LOG.debug("findAccountBalanceByConsolidationObjectTypes() started");
57  
58          String sessionId = java.util.UUID.randomUUID().toString();
59          List<Map<String, Object>> data = null;
60  
61          try {
62              // Add in all the source data
63              List<Object> params = new ArrayList<Object>(6 + objectTypes.length);
64              params.add(sessionId);
65              params.add(universityFiscalYear);
66              params.add(chartOfAccountsCode);
67              params.add(accountNumber);
68              params.add(universityFiscalYear);
69              params.add(chartOfAccountsCode);
70              Collections.addAll(params, objectTypes);
71              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, " + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, FIN_OBJ_TYP_CD, SESID ) " + "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, a.ACLN_ENCUM_BAL_AMT, a.TIMESTAMP, SUBSTR(fin_report_sort_cd, 1, 1), " + "o.fin_obj_typ_cd,?" + " FROM GL_ACCT_BALANCES_T a, CA_OBJECT_CODE_T o, CA_OBJ_TYPE_T t" + " WHERE a.univ_fiscal_yr = ?" + " AND a.fin_coa_cd = ?" + " AND a.account_nbr = ?" + " AND a.univ_fiscal_yr = o.univ_fiscal_yr AND a.fin_coa_cd = o.fin_coa_cd " + " AND a.fin_object_cd = o.fin_object_cd AND o.fin_obj_typ_cd = t.fin_obj_typ_cd " + " AND o.univ_fiscal_yr = ? AND o.fin_coa_cd = ? " + " AND o.fin_obj_typ_cd IN " + inString(objectTypes.length), params.toArray());
72  
73              // Summarize pending entries into fp_interim1_cons_mt if necessary
74              if ((pendingEntriesCode == AccountBalanceService.PENDING_ALL) || (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED)) {
75                  if (getMatchingPendingEntriesByConsolidation(objectTypes, options, universityFiscalYear, chartOfAccountsCode, accountNumber, isExcludeCostShare, pendingEntriesCode, sessionId, today)) {
76                      summarizePendingEntriesByConsolidation(options, sessionId);
77                  }
78              }
79  
80              // Add some reference data
81              getSimpleJdbcTemplate().update(
82                      "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, " + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD,FIN_OBJ_TYP_CD, SESID, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD, ACCTG_CTGRY_CD ) " + 
83                              " 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, " + "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,t.acctg_ctgry_cd" + 
84                              " FROM FP_INTERIM1_CONS_MT a,CA_OBJECT_CODE_T o,CA_OBJ_LEVEL_T l,CA_OBJ_CONSOLDTN_T c, CA_OBJ_TYPE_T t" +
85                              " WHERE a.univ_fiscal_yr = o.univ_fiscal_yr " + 
86                              " AND a.fin_coa_cd = o.fin_coa_cd " +
87                              " AND a.fin_object_cd = o.fin_object_cd " +
88                              " AND o.fin_coa_cd = l.fin_coa_cd " +
89                              " AND o.fin_obj_level_cd = l.fin_obj_level_cd " + 
90                              " AND c.fin_coa_cd = l.fin_coa_cd " +
91                              " AND c.fin_cons_obj_cd = l.fin_cons_obj_cd " +
92                              " AND o.fin_obj_typ_cd = t.fin_obj_typ_cd " + 
93                      		" AND o.univ_fiscal_yr = ?" + 
94                              " AND o.fin_coa_cd = ?" + 
95                              " AND l.fin_coa_cd = ?" + " AND a.SESID = ?", universityFiscalYear, chartOfAccountsCode, chartOfAccountsCode, sessionId);
96  
97              // Get rid of stuff we don't need
98              if (isExcludeCostShare) {
99                  purgeCostShareEntries("FP_INTERIM2_CONS_MT", "sesid", sessionId);
100             }
101 
102             // Summarize
103             if (isConsolidated) {
104                 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, ACCTG_CTGRY_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, SESID) SELECT '*ALL*',fin_report_sort_cd,cons_fin_report_sort_cd,fin_cons_obj_cd, acctg_ctgry_cd, SUM(curr_bdln_bal_amt), " + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid)" + " FROM FP_INTERIM2_CONS_MT WHERE FP_INTERIM2_CONS_MT.SESID = ?" + " GROUP BY cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd, acctg_ctgry_cd", sessionId);
105             }
106             else {
107                 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, ACCTG_CTGRY_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, SESID) SELECT sub_acct_nbr, fin_report_sort_cd, cons_fin_report_sort_cd, fin_cons_obj_cd, acctg_ctgry_cd, SUM(curr_bdln_bal_amt), " + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid) " + " FROM FP_INTERIM2_CONS_MT WHERE FP_INTERIM2_CONS_MT.SESID = ?" + " GROUP BY sub_acct_nbr, cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd, acctg_ctgry_cd", sessionId);
108             }
109 
110             // Here's the data
111             data = getSimpleJdbcTemplate().queryForList("select SUB_ACCT_NBR, FIN_REPORT_SORT_CD, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD, ACCTG_CTGRY_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT " + "from FP_BAL_BY_CONS_MT where SESID = ?" + " order by fin_report_sort_cd,cons_fin_report_sort_cd", sessionId);
112         }
113         finally {
114             // Clean up everything
115             clearTempTable("FP_BAL_BY_CONS_MT", "SESID", sessionId);
116             clearTempTable("FP_INTERIM1_CONS_MT", "SESID", sessionId);
117             clearTempTable("FP_INTERIM2_CONS_MT", "SESID", sessionId);
118             clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
119         }
120         return data;
121     }
122 
123     /**
124      * Finds whether pending entries exist that would change the results of this inquiry
125      * 
126      * @param objectTypes the object types to search for
127      * @param options the options table for the fiscal year being inquiring on
128      * @param universityFiscalYear the university fiscal year of account balances being inquired upon
129      * @param chartOfAccountsCode the chart of accounts of account balances being inquired upon
130      * @param accountNumber the account number of account balances being inquired upon
131      * @param isCostShareExcluded whether cost share entries should be excluded
132      * @param pendingEntriesCode is the inquiry for no pending entries, approved pending entries, or all pending entries
133      * @param sessionId the unique session id of the web session of the currently inquiring users, so temp table entries have a
134      *        unique identifier
135      * @return true if pending entries exist that would affect this inquiry, false otherwise
136      */
137     protected boolean getMatchingPendingEntriesByConsolidation(String[] objectTypes, SystemOptions options, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isCostShareExcluded, int pendingEntriesCode, String sessionId, UniversityDate today) {
138         LOG.debug("getMatchingPendingEntriesByConsolidation() started");
139 
140         // If they have specified this year, we will get all the pending entries
141         // where the year is equal or the year is null
142         // (because most eDocs don't fill in the year field).
143         // If they have specified a previous year, we will get all the pending
144         // entries where the year is equal to their selection
145         // without the nulls (because we will post eDocs
146         // with blank years tonight most probably.
147 
148         clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
149 
150         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, " + " 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, " + " 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, " + " ACCT_SF_FINOBJ_CD, TRN_ENTR_OFST_CD,TRNENTR_PROCESS_TM) ";
151 
152         String selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR," + "p.FIN_COA_CD, p.ACCOUNT_NBR, " + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR", "'-----'") + ", p.FIN_OBJECT_CD, " + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD", "'---'") + " , p.FIN_BALANCE_TYP_CD,p.FIN_OBJ_TYP_CD, p.UNIV_FISCAL_YR, p.UNIV_FISCAL_PRD_CD, " + "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, " + "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 " + " FROM GL_PENDING_ENTRY_T p WHERE p.FIN_COA_CD = ? AND p.account_nbr = ? " + " AND p.fin_obj_typ_cd IN " + inString(objectTypes.length) + " AND p.FDOC_APPROVED_CD <> '" + OLEConstants.PENDING_ENTRY_APPROVED_STATUS_CODE.PROCESSED + "' ";
153 
154         List<Object> params = new ArrayList<Object>(20);
155         params.add(sessionId);
156         params.add(chartOfAccountsCode);
157         params.add(accountNumber);
158         Collections.addAll(params, objectTypes);
159 
160         if (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED) {
161             selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR," + "p.FIN_COA_CD, p.ACCOUNT_NBR, " + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR", "'-----'") + ", p.FIN_OBJECT_CD, " + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD", "'---'") + " , p.FIN_BALANCE_TYP_CD,p.FIN_OBJ_TYP_CD, p.UNIV_FISCAL_YR, p.UNIV_FISCAL_PRD_CD, " + "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, " + "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 " + " FROM GL_PENDING_ENTRY_T p, FS_DOC_HEADER_T d WHERE p.FIN_COA_CD = ? AND p.account_nbr = ? " + " AND p.fin_obj_typ_cd IN " + inString(objectTypes.length) + " AND p.FDOC_APPROVED_CD <> '" + OLEConstants.PENDING_ENTRY_APPROVED_STATUS_CODE.PROCESSED + "' AND p.FDOC_NBR = d.FDOC_NBR AND d.FDOC_STATUS_CD = '"
162                     + OLEConstants.DocumentStatusCodes.APPROVED + "' ";
163         }
164 
165         if (today.getUniversityFiscalYear().equals(universityFiscalYear)) {
166             selectSql = selectSql + "AND (p.univ_fiscal_yr is null OR p.univ_fiscal_yr = ? )";
167             params.add(universityFiscalYear);
168         }
169         else {
170             selectSql = selectSql + "AND p.univ_fiscal_yr = ?";
171             params.add(universityFiscalYear);
172         }
173         getSimpleJdbcTemplate().update(insertSql + selectSql, params.toArray());
174 
175         if (isCostShareExcluded) {
176             purgeCostShareEntries("GL_PENDING_ENTRY_MT", "sesid", sessionId);
177         }
178 
179         if (!hasEntriesInPendingTable(sessionId)) {
180             return false;
181         }
182 
183         fixPendingEntryDisplay(universityFiscalYear, sessionId);
184 
185         return true;
186     }
187 
188     /**
189      * This method summarizes pending entries to temporary tables for easier inclusion into the inquiry
190      * 
191      * @param options the system options of the fiscal year that is being inquired upon
192      * @param sessionId the session id of the inquiring user, for a unique primary key in the temporary tables
193      */
194     protected void summarizePendingEntriesByConsolidation(SystemOptions options, String sessionId) {
195         LOG.debug("summarizePendingEntriesByConsolidation() started");
196 
197         try {
198             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 = ? " + "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 = ?";
199 
200             String updateBalanceStatementSql = "UPDATE FP_INTERIM1_CONS_MT SET curr_bdln_bal_amt = ?,acln_actls_bal_amt = ?,acln_encum_bal_amt = ? WHERE " + "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 = ?";
201 
202             String insertBalanceStatementSql = "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, ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, FIN_OBJ_TYP_CD, SESID) " + "VALUES (?,?,?,?,?,?,?,?,?," + getDbPlatform().getCurTimeFunction() + ",?,?,? )";
203 
204             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 " + "FROM GL_PENDING_ENTRY_MT e,CA_OBJ_TYPE_T t,CA_BALANCE_TYPE_T b, CA_OBJECT_CODE_T oc " + "WHERE e.SESID = ? " + "AND e.fin_coa_cd = oc.fin_coa_cd " + "AND e.fin_object_cd = oc.fin_object_cd " + "AND e.univ_fiscal_yr = oc.univ_fiscal_yr " + "AND oc.FIN_OBJ_TYP_CD = t.FIN_OBJ_TYP_CD " + "AND e.fin_balance_typ_cd = b.fin_balance_typ_cd " + "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 });
205 
206             int updateCount = 0;
207             int insertCount = 0;
208 
209             while (pendingEntryRowSet.next()) {
210                 String sortCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.REPORT_SORT_CODE);
211                 if (sortCode.length() > 1) {
212                     sortCode = sortCode.substring(0, 1);
213                 }
214                 Map<String, Object> balance = null;
215                 try {
216                     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));
217                 }
218                 catch (IncorrectResultSizeDataAccessException ex) {
219                     if (ex.getActualSize() != 0) {
220                         LOG.error("balance request sql returned more than one row, aborting", ex);
221                         throw ex;
222                     }
223                     // no rows returned - that's ok
224                 }
225 
226                 String balanceType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE);
227                 String objectType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE);
228                 String debitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.DEBIT_CREDIT_CODE);
229                 String objectTypeDebitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_DEBIT_CREDIT_CODE);
230                 String offsetGenerationCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OFFSET_GENERATION_CODE);
231 
232                 if (balance != null) {
233                     updateCount++;
234 
235                     BigDecimal budget = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.CURRENT_BDLN_BALANCE_AMOUNT);
236                     BigDecimal actual = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ACTUALS_BALANCE_AMOUNT);
237                     BigDecimal encumb = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ENCUMBRANCE_BALANCE_AMOUNT);
238 
239                     if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
240                         budget = budget.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
241                     }
242                     else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
243                         if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && OLEConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
244                             actual = actual.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
245                         }
246                         else {
247                             actual = actual.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
248                         }
249                     }
250                     else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
251                         if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && OLEConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
252                             encumb = encumb.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
253                         }
254                         else {
255                             encumb = encumb.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
256                         }
257                     }
258 
259                     // A balance exists, so we need to update it
260                     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));
261                 }
262                 else {
263                     insertCount++;
264 
265                     BigDecimal budget = new BigDecimal("0");
266                     BigDecimal actual = new BigDecimal("0");
267                     BigDecimal encumb = new BigDecimal("0");
268 
269                     if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
270                         budget = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
271                     }
272                     else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
273                         if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && OLEConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
274                             actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
275                         }
276                         else {
277                             actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
278                         }
279                     }
280                     else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
281                         if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && OLEConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
282                             encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
283                         }
284                         else {
285                             encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
286                         }
287                     }
288 
289                     // No balance exists, so we need to insert one
290                     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);
291                 }
292             }
293             LOG.info("summarizePendingEntriesByConsolidation() INSERTS: " + insertCount);
294             LOG.info("summarizePendingEntriesByConsolidation() UPDATES: " + updateCount);
295         }
296         catch (Exception e) {
297             LOG.error("summarizePendingEntriesByConsolidation() Exception running sql", e);
298             throw new RuntimeException("Unable to execute: " + e.getMessage(), e);
299         }
300     }
301 
302 }