001: /*
002: * Copyright 2007 The Kuali Foundation.
003: *
004: * Licensed under the Educational Community License, Version 1.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/ecl1.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.module.gl.dao.jdbc;
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.core.dbplatform.RawSQL;
025: import org.kuali.core.util.Guid;
026: import org.kuali.kfs.KFSConstants;
027: import org.kuali.kfs.bo.Options;
028: import org.kuali.module.gl.GLConstants;
029: import org.kuali.module.gl.bo.UniversityDate;
030: import org.kuali.module.gl.dao.AccountBalanceConsolidationDao;
031: import org.kuali.module.gl.service.AccountBalanceService;
032: import org.springframework.dao.IncorrectResultSizeDataAccessException;
033: import org.springframework.jdbc.support.rowset.SqlRowSet;
034:
035: /**
036: * A class to do the database queries needed to calculate Balance By Consolidation Balance Inquiry Screen
037: */
038: @RawSQL
039: public class AccountBalanceConsolidationDaoJdbc extends
040: AccountBalanceDaoJdbcBase implements
041: AccountBalanceConsolidationDao {
042: private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger
043: .getLogger(AccountBalanceConsolidationDaoJdbc.class);
044:
045: /**
046: * Returns account balance information that qualifies, based on the inquiry formed out of the parameters
047: *
048: * @param objectTypes the object types of account balances to include in the inquiry
049: * @param universityFiscalYear the fiscal year of account balances to include in the inquiry
050: * @param chartOfAccountsCode the chart of accounts of account balances to include in the inquiry
051: * @param accountNumber the account number of account balances to include in the inquiry
052: * @param isExcludeCostShare whether to exclude cost share entries from this inquiry or not
053: * @param isConsolidated whether the results of the inquiry should be consolidated
054: * @param pendingEntriesCode whether the inquiry should also report results based on no pending entries, approved pending entries, or all pending entries
055: * @return a List of Maps with the report information from this inquiry
056: * @see org.kuali.module.gl.dao.AccountBalanceConsolidationDao#findAccountBalanceByConsolidationObjectTypes(java.lang.String[], java.lang.Integer, java.lang.String, java.lang.String, boolean, boolean, int)
057: */
058: @RawSQL
059: public List<Map<String, Object>> findAccountBalanceByConsolidationObjectTypes(
060: String[] objectTypes, Integer universityFiscalYear,
061: String chartOfAccountsCode, String accountNumber,
062: boolean isExcludeCostShare, boolean isConsolidated,
063: int pendingEntriesCode) {
064: LOG
065: .debug("findAccountBalanceByConsolidationObjectTypes() started");
066:
067: Options options = optionsService
068: .getOptions(universityFiscalYear);
069: String sessionId = new Guid().toString();
070: List<Map<String, Object>> data = null;
071:
072: try {
073: // Add in all the source data
074: List<Object> params = new ArrayList<Object>(
075: 6 + objectTypes.length);
076: params.add(sessionId);
077: params.add(universityFiscalYear);
078: params.add(chartOfAccountsCode);
079: params.add(accountNumber);
080: params.add(universityFiscalYear);
081: params.add(chartOfAccountsCode);
082: Collections.addAll(params, objectTypes);
083: getSimpleJdbcTemplate()
084: .update(
085: "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, "
086: + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, FIN_OBJ_TYP_CD, SESID ) "
087: + "SELECT A.UNIV_FISCAL_YR, A.FIN_COA_CD, A.ACCOUNT_NBR, A.SUB_ACCT_NBR, "
088: + "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), "
089: + "o.fin_obj_typ_cd,?"
090: + " FROM gl_acct_balances_t a, ca_object_code_t o, ca_obj_type_t t"
091: + " WHERE a.univ_fiscal_yr = ?"
092: + " AND a.fin_coa_cd = ?"
093: + " AND a.account_nbr = ?"
094: + " AND a.univ_fiscal_yr = o.univ_fiscal_yr AND a.fin_coa_cd = o.fin_coa_cd "
095: + " 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 = ? "
096: + " AND o.fin_obj_typ_cd IN "
097: + inString(objectTypes.length),
098: params.toArray());
099:
100: // Summarize pending entries into fp_interim1_cons_mt if necessary
101: if ((pendingEntriesCode == AccountBalanceService.PENDING_ALL)
102: || (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED)) {
103: if (getMatchingPendingEntriesByConsolidation(
104: objectTypes, options, universityFiscalYear,
105: chartOfAccountsCode, accountNumber,
106: isExcludeCostShare, pendingEntriesCode,
107: sessionId)) {
108: summarizePendingEntriesByConsolidation(options,
109: sessionId);
110: }
111: }
112:
113: // Add some reference data
114: getSimpleJdbcTemplate()
115: .update(
116: "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, "
117: + "ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD,FIN_OBJ_TYP_CD, SESID, CONS_FIN_REPORT_SORT_CD, FIN_CONS_OBJ_CD ) "
118: + "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, "
119: + "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"
120: + " 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 "
121: + " 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 "
122: + " 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 = ?"
123: + " AND o.fin_coa_cd = ?"
124: + " AND l.fin_coa_cd = ?"
125: + " AND a.SESID = ?",
126: universityFiscalYear, chartOfAccountsCode,
127: chartOfAccountsCode, sessionId);
128:
129: // Get rid of stuff we don't need
130: if (isExcludeCostShare) {
131: purgeCostShareEntries("fp_interim2_cons_mt", "sesid",
132: sessionId);
133: }
134:
135: // Summarize
136: if (isConsolidated) {
137: getSimpleJdbcTemplate()
138: .update(
139: "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, "
140: + "ACLN_ENCUM_BAL_AMT, SESID) "
141: + "SELECT '*ALL*',fin_report_sort_cd,cons_fin_report_sort_cd,fin_cons_obj_cd,SUM(curr_bdln_bal_amt), "
142: + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid)"
143: + " FROM fp_interim2_cons_mt WHERE fp_interim2_cons_mt.SESID = ?"
144: + " GROUP BY cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd",
145: sessionId);
146: } else {
147: getSimpleJdbcTemplate()
148: .update(
149: "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, "
150: + "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), "
151: + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), MAX(sesid) "
152: + " FROM fp_interim2_cons_mt WHERE fp_interim2_cons_mt.SESID = ?"
153: + " GROUP BY sub_acct_nbr, cons_fin_report_sort_cd, fin_report_sort_cd, fin_cons_obj_cd",
154: sessionId);
155: }
156:
157: // Here's the data
158: data = getSimpleJdbcTemplate()
159: .queryForList(
160: "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 "
161: + "from fp_bal_by_cons_mt where SESID = ?"
162: + " order by fin_report_sort_cd,cons_fin_report_sort_cd",
163: sessionId);
164: } finally {
165: // Clean up everything
166: clearTempTable("fp_bal_by_cons_mt", "SESID", sessionId);
167: clearTempTable("fp_interim1_cons_mt", "SESID", sessionId);
168: clearTempTable("fp_interim2_cons_mt", "SESID", sessionId);
169: clearTempTable("gl_pending_entry_mt", "SESID", sessionId);
170: }
171: return data;
172: }
173:
174: /**
175: * Finds whether pending entries exist that would change the results of this inquiry
176: *
177: * @param objectTypes the object types to search for
178: * @param options the options table for the fiscal year being inquiring on
179: * @param universityFiscalYear the university fiscal year of account balances being inquired upon
180: * @param chartOfAccountsCode the chart of accounts of account balances being inquired upon
181: * @param accountNumber the account number of account balances being inquired upon
182: * @param isCostShareExcluded whether cost share entries should be excluded
183: * @param pendingEntriesCode is the inquiry for no pending entries, approved pending entries, or all pending entries
184: * @param sessionId the unique session id of the web session of the currently inquiring users, so temp table entries have a unique identifier
185: * @return true if pending entries exist that would affect this inquiry, false otherwise
186: */
187: @RawSQL
188: private boolean getMatchingPendingEntriesByConsolidation(
189: String[] objectTypes, Options options,
190: Integer universityFiscalYear, String chartOfAccountsCode,
191: String accountNumber, boolean isCostShareExcluded,
192: int pendingEntriesCode, String sessionId) {
193: LOG.debug("getMatchingPendingEntriesByConsolidation() started");
194:
195: // If they have specified this year, we will get all the pending entries
196: // where the year is equal or the year is null
197: // (because most eDocs don't fill in the year field).
198: // If they have specified a previous year, we will get all the pending
199: // entries where the year is equal to their selection
200: // without the nulls (because we will post eDocs
201: // with blank years tonight most probably.
202:
203: UniversityDate today = universityDateService
204: .getCurrentUniversityDate();
205:
206: clearTempTable("gl_pending_entry_mt", "SESID", sessionId);
207:
208: 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, "
209: + " 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, "
210: + " 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, "
211: + " ACCT_SF_FINOBJ_CD, TRN_ENTR_OFST_CD,TRNENTR_PROCESS_TM) ";
212:
213: List<Object> params = new ArrayList<Object>(20);
214:
215: String selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR,"
216: + "p.FIN_COA_CD, p.ACCOUNT_NBR, "
217: + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR",
218: "'-----'")
219: + ", p.FIN_OBJECT_CD, "
220: + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD",
221: "'---'")
222: + " , p.FIN_BALANCE_TYP_CD,p.FIN_OBJ_TYP_CD, p.UNIV_FISCAL_YR, p.UNIV_FISCAL_PRD_CD, "
223: + "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, "
224: + "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 "
225: + " FROM gl_pending_entry_t p,fp_doc_header_t d "
226: + " WHERE p.fdoc_nbr = d.fdoc_nbr "
227: + "AND p.FIN_COA_CD = ? "
228: + "AND p.account_nbr = ? "
229: + " AND d.FDOC_STATUS_CD NOT IN('"
230: + KFSConstants.DocumentStatusCodes.CANCELLED
231: + "', '"
232: + KFSConstants.DocumentStatusCodes.DISAPPROVED
233: + "') "
234: + " AND p.fin_obj_typ_cd IN "
235: + inString(objectTypes.length);
236: params.add(sessionId);
237: params.add(chartOfAccountsCode);
238: params.add(accountNumber);
239: Collections.addAll(params, objectTypes);
240:
241: if (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED) {
242: selectSql = selectSql + " AND d.fdoc_status_cd = '"
243: + KFSConstants.DocumentStatusCodes.APPROVED + "' ";
244: } else {
245: selectSql = selectSql + " AND d.fdoc_status_cd <> '"
246: + KFSConstants.DocumentStatusCodes.DISAPPROVED
247: + "' ";
248: }
249:
250: if (today.getUniversityFiscalYear()
251: .equals(universityFiscalYear)) {
252: selectSql = selectSql
253: + "AND (p.univ_fiscal_yr is null OR p.univ_fiscal_yr = ? )";
254: params.add(universityFiscalYear);
255: } else {
256: selectSql = selectSql + "AND p.univ_fiscal_yr = ?";
257: params.add(universityFiscalYear);
258: }
259: getSimpleJdbcTemplate().update(insertSql + selectSql,
260: params.toArray());
261:
262: if (isCostShareExcluded) {
263: purgeCostShareEntries("gl_pending_entry_mt", "sesid",
264: sessionId);
265: }
266:
267: if (!hasEntriesInPendingTable(sessionId)) {
268: return false;
269: }
270:
271: fixPendingEntryDisplay(universityFiscalYear, sessionId);
272:
273: return true;
274: }
275:
276: /**
277: * This method summarizes pending entries to temporary tables for easier inclusion into the inquiry
278: *
279: * @param options the system options of the fiscal year that is being inquired upon
280: * @param sessionId the session id of the inquiring user, for a unique primary key in the temporary tables
281: */
282: @RawSQL
283: private void summarizePendingEntriesByConsolidation(
284: Options options, String sessionId) {
285: LOG.debug("summarizePendingEntriesByConsolidation() started");
286:
287: try {
288: 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 = ? "
289: + "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 = ?";
290:
291: String updateBalanceStatementSql = "UPDATE fp_interim1_cons_mt SET curr_bdln_bal_amt = ?,acln_actls_bal_amt = ?,acln_encum_bal_amt = ? WHERE "
292: + "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 = ?";
293:
294: String insertBalanceStatementSql = "INSERT INTO fp_interim1_cons_mt (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, "
295: + "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) "
296: + "VALUES (?,?,?,?,?,"
297: + "?,?,?,?,"
298: + getDbPlatform().getCurTimeFunction() + ",?,?,? )";
299:
300: SqlRowSet pendingEntryRowSet = getJdbcTemplate()
301: .queryForRowSet(
302: "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 "
303: + "FROM gl_pending_entry_mt e,CA_OBJ_TYPE_T t,CA_BALANCE_TYPE_T b, ca_object_code_t oc "
304: + "WHERE e.SESID = ? "
305: + "AND e.fin_coa_cd = oc.fin_coa_cd "
306: + "AND e.fin_object_cd = oc.fin_object_cd "
307: + "AND e.univ_fiscal_yr = oc.univ_fiscal_yr "
308: + "AND oc.FIN_OBJ_TYP_CD = t.FIN_OBJ_TYP_CD "
309: + "AND e.fin_balance_typ_cd = b.fin_balance_typ_cd "
310: + "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",
311: new Object[] { sessionId });
312:
313: int updateCount = 0;
314: int insertCount = 0;
315:
316: while (pendingEntryRowSet.next()) {
317: String sortCode = pendingEntryRowSet
318: .getString(GLConstants.ColumnNames.REPORT_SORT_CODE);
319: if (sortCode.length() > 1) {
320: sortCode = sortCode.substring(0, 1);
321: }
322: Map<String, Object> balance = null;
323: try {
324: balance = getSimpleJdbcTemplate()
325: .queryForMap(
326: balanceStatementSql,
327: sessionId,
328: pendingEntryRowSet
329: .getInt(GLConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR),
330: pendingEntryRowSet
331: .getString(GLConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE),
332: pendingEntryRowSet
333: .getString(GLConstants.ColumnNames.ACCOUNT_NUMBER),
334: pendingEntryRowSet
335: .getString(GLConstants.ColumnNames.SUB_ACCOUNT_NUMBER),
336: pendingEntryRowSet
337: .getString(GLConstants.ColumnNames.OBJECT_CODE),
338: pendingEntryRowSet
339: .getString(GLConstants.ColumnNames.SUB_OBJECT_CODE),
340: pendingEntryRowSet
341: .getString(GLConstants.ColumnNames.OBJECT_TYPE_CODE));
342: } catch (IncorrectResultSizeDataAccessException ex) {
343: if (ex.getActualSize() != 0) {
344: LOG
345: .error(
346: "balance request sql returned more than one row, aborting",
347: ex);
348: throw ex;
349: }
350: // no rows returned - that's ok
351: }
352:
353: String balanceType = pendingEntryRowSet
354: .getString(GLConstants.ColumnNames.BALANCE_TYPE_CODE);
355: String objectType = pendingEntryRowSet
356: .getString(GLConstants.ColumnNames.OBJECT_TYPE_CODE);
357: String debitCreditCode = pendingEntryRowSet
358: .getString(GLConstants.ColumnNames.DEBIT_CREDIT_CODE);
359: String objectTypeDebitCreditCode = pendingEntryRowSet
360: .getString(GLConstants.ColumnNames.OBJECT_TYPE_DEBIT_CREDIT_CODE);
361: String offsetGenerationCode = pendingEntryRowSet
362: .getString(GLConstants.ColumnNames.OFFSET_GENERATION_CODE);
363:
364: if (balance != null) {
365: updateCount++;
366:
367: BigDecimal budget = (BigDecimal) balance
368: .get(GLConstants.ColumnNames.CURRENT_BDLN_BALANCE_AMOUNT);
369: BigDecimal actual = (BigDecimal) balance
370: .get(GLConstants.ColumnNames.ACCOUNTING_LINE_ACTUALS_BALANCE_AMOUNT);
371: BigDecimal encumb = (BigDecimal) balance
372: .get(GLConstants.ColumnNames.ACCOUNTING_LINE_ENCUMBRANCE_BALANCE_AMOUNT);
373:
374: if (balanceType.equals(options
375: .getBudgetCheckingBalanceTypeCd())) {
376: budget = budget
377: .add(pendingEntryRowSet
378: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
379: } else if (balanceType.equals(options
380: .getActualFinancialBalanceTypeCd())) {
381: if (debitCreditCode
382: .equals(objectTypeDebitCreditCode)
383: || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE
384: .equals(debitCreditCode)))) {
385: actual = actual
386: .add(pendingEntryRowSet
387: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
388: } else {
389: actual = actual
390: .subtract(pendingEntryRowSet
391: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
392: }
393: } else if (balanceType.equals(options
394: .getExtrnlEncumFinBalanceTypCd())
395: || balanceType.equals(options
396: .getIntrnlEncumFinBalanceTypCd())
397: || balanceType.equals(options
398: .getPreencumbranceFinBalTypeCd())
399: || "CE".equals(balanceType)) {
400: if (debitCreditCode
401: .equals(objectTypeDebitCreditCode)
402: || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE
403: .equals(debitCreditCode)))) {
404: encumb = encumb
405: .add(pendingEntryRowSet
406: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
407: } else {
408: encumb = encumb
409: .subtract(pendingEntryRowSet
410: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
411: }
412: }
413:
414: // A balance exists, so we need to update it
415: getSimpleJdbcTemplate()
416: .update(
417: updateBalanceStatementSql,
418: budget,
419: actual,
420: encumb,
421: sessionId,
422: pendingEntryRowSet
423: .getInt(GLConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR),
424: pendingEntryRowSet
425: .getString(GLConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE),
426: pendingEntryRowSet
427: .getString(GLConstants.ColumnNames.ACCOUNT_NUMBER),
428: pendingEntryRowSet
429: .getString(GLConstants.ColumnNames.SUB_ACCOUNT_NUMBER),
430: pendingEntryRowSet
431: .getString(GLConstants.ColumnNames.OBJECT_CODE),
432: pendingEntryRowSet
433: .getString(GLConstants.ColumnNames.SUB_OBJECT_CODE),
434: pendingEntryRowSet
435: .getString(GLConstants.ColumnNames.OBJECT_TYPE_CODE));
436: } else {
437: insertCount++;
438:
439: BigDecimal budget = new BigDecimal("0");
440: BigDecimal actual = new BigDecimal("0");
441: BigDecimal encumb = new BigDecimal("0");
442:
443: if (balanceType.equals(options
444: .getBudgetCheckingBalanceTypeCd())) {
445: budget = pendingEntryRowSet
446: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
447: } else if (balanceType.equals(options
448: .getActualFinancialBalanceTypeCd())) {
449: if (debitCreditCode
450: .equals(objectTypeDebitCreditCode)
451: || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE
452: .equals(debitCreditCode)))) {
453: actual = pendingEntryRowSet
454: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
455: } else {
456: actual = pendingEntryRowSet
457: .getBigDecimal(
458: GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT)
459: .negate();
460: }
461: } else if (balanceType.equals(options
462: .getExtrnlEncumFinBalanceTypCd())
463: || balanceType.equals(options
464: .getIntrnlEncumFinBalanceTypCd())
465: || balanceType.equals(options
466: .getPreencumbranceFinBalTypeCd())
467: || "CE".equals(balanceType)) {
468: if (debitCreditCode
469: .equals(objectTypeDebitCreditCode)
470: || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE
471: .equals(debitCreditCode)))) {
472: encumb = pendingEntryRowSet
473: .getBigDecimal(GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
474: } else {
475: encumb = pendingEntryRowSet
476: .getBigDecimal(
477: GLConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT)
478: .negate();
479: }
480: }
481:
482: // No balance exists, so we need to insert one
483: getSimpleJdbcTemplate()
484: .update(
485: insertBalanceStatementSql,
486: pendingEntryRowSet
487: .getInt(GLConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR),
488: pendingEntryRowSet
489: .getString(GLConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE),
490: pendingEntryRowSet
491: .getString(GLConstants.ColumnNames.ACCOUNT_NUMBER),
492: pendingEntryRowSet
493: .getString(GLConstants.ColumnNames.SUB_ACCOUNT_NUMBER),
494: pendingEntryRowSet
495: .getString(GLConstants.ColumnNames.OBJECT_CODE),
496: pendingEntryRowSet
497: .getString(GLConstants.ColumnNames.SUB_OBJECT_CODE),
498: budget,
499: actual,
500: encumb,
501: sortCode,
502: pendingEntryRowSet
503: .getString(GLConstants.ColumnNames.OBJECT_TYPE_CODE),
504: sessionId);
505: }
506: }
507: LOG
508: .info("summarizePendingEntriesByConsolidation() INSERTS: "
509: + insertCount);
510: LOG
511: .info("summarizePendingEntriesByConsolidation() UPDATES: "
512: + updateCount);
513: } catch (Exception e) {
514: LOG
515: .error(
516: "summarizePendingEntriesByConsolidation() Exception running sql",
517: e);
518: throw new RuntimeException("Unable to execute: "
519: + e.getMessage(), e);
520: }
521: }
522:
523: }
|