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