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.List;
021 import java.util.Map;
022
023 import org.kuali.kfs.gl.GeneralLedgerConstants;
024 import org.kuali.kfs.gl.dataaccess.AccountBalanceLevelDao;
025 import org.kuali.kfs.gl.service.AccountBalanceService;
026 import org.kuali.kfs.sys.KFSConstants;
027 import org.kuali.kfs.sys.businessobject.SystemOptions;
028 import org.kuali.kfs.sys.businessobject.UniversityDate;
029 import org.kuali.kfs.sys.context.SpringContext;
030 import org.kuali.kfs.sys.service.UniversityDateService;
031 import org.kuali.rice.kns.util.Guid;
032 import org.springframework.dao.IncorrectResultSizeDataAccessException;
033 import org.springframework.jdbc.support.rowset.SqlRowSet;
034
035 /**
036 * Calculate Balance By Level Balance Inquiry Screen
037 */
038 public class AccountBalanceLevelDaoJdbc extends AccountBalanceDaoJdbcBase implements AccountBalanceLevelDao {
039 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(AccountBalanceLevelDaoJdbc.class);
040
041 /**
042 * Summarizes all of the qualifying account balance information for the balance by level inquiry
043 *
044 * @param universityFiscalYear the university fiscal year of reported on account balances
045 * @param chartOfAccountsCode the chart of accounts code of reported on account balances
046 * @param accountNumber the account number of reported on account balances
047 * @param financialConsolidationObjectCode the consolidation code of reported on account balances
048 * @param isCostShareExcluded whether cost share account balances should be excluded from the query or not
049 * @param isConsolidated whether the results of the query should be consolidated
050 * @param pendingEntriesCode whether this query should account for no pending entries, approved pending entries, or all pending entries
051 * @return a List of Maps with appropriate report data
052 * @see org.kuali.kfs.gl.dataaccess.AccountBalanceLevelDao#findAccountBalanceByLevel(java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, boolean, boolean, int)
053 */
054 public List findAccountBalanceByLevel(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String financialConsolidationObjectCode, boolean isCostShareExcluded, boolean isConsolidated, int pendingEntriesCode) {
055
056 // Set the default sort so that income entries are first, then expense below.
057 String financialReportingSortCode = "A";
058
059 SystemOptions options = optionsService.getOptions(universityFiscalYear);
060 String sessionId = new Guid().toString();
061 List<Map<String, Object>> data = null;
062
063 try {
064 // Delete any data for this session if it exists already (unlikely, but you never know)
065 clearTempTable("FP_BAL_BY_LEVEL_MT", "SESID", sessionId);
066 clearTempTable("FP_INTERIM1_LEVEL_MT", "SESID", sessionId);
067
068 // Add in all the data we need
069 getSimpleJdbcTemplate().update("INSERT INTO FP_INTERIM1_LEVEL_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, "
070 + " CURR_BDLN_BAL_AMT, "
071 + "ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, "
072 + " FIN_OBJ_LEVEL_CD, SESID) "
073 + " SELECT a.UNIV_FISCAL_YR, a.FIN_COA_CD, a.ACCOUNT_NBR, a.SUB_ACCT_NBR,a.FIN_OBJECT_CD, "
074 + " a.FIN_SUB_OBJ_CD,a.CURR_BDLN_BAL_AMT, a.ACLN_ACTLS_BAL_AMT, a.ACLN_ENCUM_BAL_AMT, "
075 + " a.TIMESTAMP, l.fin_report_sort_cd, l.fin_obj_level_cd, ? "
076 + " FROM GL_ACCT_BALANCES_T a, CA_OBJECT_CODE_T o, CA_OBJ_LEVEL_T l "
077 + " WHERE a.univ_fiscal_yr = ? AND a.fin_coa_cd = ? AND a.account_nbr = ?"
078 + " AND a.univ_fiscal_yr = o.univ_fiscal_yr AND a.fin_coa_cd = o.fin_coa_cd "
079 + " 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"
080 + " AND l.fin_cons_obj_cd = ? AND o.univ_fiscal_yr = ? AND o.fin_coa_cd = ? ", sessionId, universityFiscalYear,
081 chartOfAccountsCode, accountNumber, financialConsolidationObjectCode, universityFiscalYear, chartOfAccountsCode);
082
083 // Summarize pending entries into fp_interim1_level_mt if necessary
084 if ((pendingEntriesCode == AccountBalanceService.PENDING_ALL) || (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED)) {
085 if (getMatchingPendingEntriesByLevel(options, universityFiscalYear, chartOfAccountsCode, accountNumber, financialConsolidationObjectCode, isCostShareExcluded, pendingEntriesCode, sessionId)) {
086 summarizePendingEntriesByLevel(options, sessionId);
087 }
088 }
089
090 // Delete what we don't need
091 if (isCostShareExcluded) {
092 purgeCostShareEntries("FP_INTERIM1_LEVEL_MT", "sesid", sessionId);
093 }
094
095 // Summarize
096 if (isConsolidated) {
097 getSimpleJdbcTemplate().update("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, "
098 + "TYP_FIN_REPORT_SORT_CD, SESID) SELECT '*ALL*', fin_obj_level_cd,fin_report_sort_cd, SUM(curr_bdln_bal_amt), "
099 + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt),?, ?"
100 + " FROM FP_INTERIM1_LEVEL_MT "
101 + " WHERE FP_INTERIM1_LEVEL_MT.SESID = ? "
102 + " GROUP BY fin_report_sort_cd, fin_obj_level_cd", financialReportingSortCode, sessionId, sessionId);
103 }
104 else {
105 getSimpleJdbcTemplate().update("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, "
106 + "TYP_FIN_REPORT_SORT_CD, SESID) SELECT sub_acct_nbr, fin_obj_level_cd, fin_report_sort_cd, SUM(curr_bdln_bal_amt), "
107 + "SUM(acln_actls_bal_amt), SUM(acln_encum_bal_amt), ?, ? "
108 + " FROM FP_INTERIM1_LEVEL_MT "
109 + " WHERE FP_INTERIM1_LEVEL_MT.SESID = ? "
110 + " GROUP BY sub_acct_nbr, fin_report_sort_cd, fin_obj_level_cd", financialReportingSortCode, sessionId, sessionId);
111 }
112
113 // Here's the data
114 data = getSimpleJdbcTemplate().queryForList("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 "
115 + "from FP_BAL_BY_LEVEL_MT where SESID = ? order by fin_report_sort_cd", sessionId);
116 }
117 finally {
118 // Clean up everything
119 clearTempTable("FP_BAL_BY_LEVEL_MT", "SESID", sessionId);
120 clearTempTable("FP_INTERIM1_LEVEL_MT", "SESID", sessionId);
121 clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
122 }
123 return data;
124 }
125
126 /**
127 * Summarizes all pending entries by level, so they can be added to the general query if necessary
128 * @param options a given set of system options
129 * @param sessionId the unique web id of the currently inquiring user, which acts as a key for the temporary table
130 */
131 protected void summarizePendingEntriesByLevel(SystemOptions options, String sessionId) {
132 LOG.debug("summarizePendingEntriesByLevel() started");
133
134 try {
135 String balanceStatementSql = "SELECT CURR_BDLN_BAL_AMT,ACLN_ACTLS_BAL_AMT,ACLN_ENCUM_BAL_AMT "
136 + "FROM FP_INTERIM1_LEVEL_MT "
137 + "WHERE sesid = ? AND univ_fiscal_yr = ? AND fin_coa_cd = ? AND account_nbr = ? AND sub_acct_nbr = ?"
138 + " AND fin_object_cd = ? AND fin_sub_obj_cd = ?";
139
140 String updateBalanceStatementSql = "UPDATE FP_INTERIM1_LEVEL_MT " + " SET curr_bdln_bal_amt = ?,acln_actls_bal_amt = ?,acln_encum_bal_amt = ? "
141 + " WHERE sesid = ? AND univ_fiscal_yr = ? AND fin_coa_cd = ? AND account_nbr = ? AND sub_acct_nbr = ?"
142 + " AND fin_object_cd = ? AND fin_sub_obj_cd = ?";
143
144 String insertBalanceStatementSql = "INSERT INTO FP_INTERIM1_LEVEL_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, "
145 + "FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, TIMESTAMP, FIN_REPORT_SORT_CD, "
146 + "FIN_OBJ_LEVEL_CD, SESID) "
147 + "VALUES (?,?,?,?,?,?,?,?,?," + getDbPlatform().getCurTimeFunction() + ",?,?,?)";
148
149 SqlRowSet pendingEntryRowSet = getJdbcTemplate().queryForRowSet("SELECT o.FIN_OBJ_LEVEL_CD,b.FIN_OFFST_GNRTN_CD,t.FIN_OBJTYP_DBCR_CD,l.fin_report_sort_cd,e.*"
150 + " 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"
151 + " WHERE e.SESID = ?"
152 + " AND e.FIN_OBJ_TYP_CD = t.FIN_OBJ_TYP_CD"
153 + " AND e.fin_balance_typ_cd = b.fin_balance_typ_cd"
154 + " AND e.univ_fiscal_yr = o.univ_fiscal_yr"
155 + " AND e.fin_coa_cd = o.fin_coa_cd"
156 + " AND e.fin_object_cd = o.fin_object_cd"
157 + " AND o.fin_coa_cd = l.fin_coa_cd"
158 + " AND o.fin_obj_level_cd = l.fin_obj_level_cd "
159 + "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 });
160
161
162 int updateCount = 0;
163 int insertCount = 0;
164 while (pendingEntryRowSet.next()) {
165 String sortCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.REPORT_SORT_CODE);
166 if (sortCode.length() > 1) {
167 sortCode = sortCode.substring(0, 1);
168 }
169
170 Map<String, Object> balance = null;
171 try {
172 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));
173 }
174 catch (IncorrectResultSizeDataAccessException ex) {
175 if (ex.getActualSize() != 0) {
176 LOG.error("balance request sql returned more than one row, aborting", ex);
177 throw ex;
178 }
179 // no rows returned - that's ok
180 }
181
182 String balanceType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE);
183 String objectType = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE);
184 String debitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.DEBIT_CREDIT_CODE);
185 String objectTypeDebitCreditCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_DEBIT_CREDIT_CODE);
186 String offsetGenerationCode = pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OFFSET_GENERATION_CODE);
187
188 if (balance != null) {
189 updateCount++;
190
191 BigDecimal budget = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.CURRENT_BDLN_BALANCE_AMOUNT);
192 BigDecimal actual = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ACTUALS_BALANCE_AMOUNT);
193 BigDecimal encumb = (BigDecimal) balance.get(GeneralLedgerConstants.ColumnNames.ACCOUNTING_LINE_ENCUMBRANCE_BALANCE_AMOUNT);
194
195 if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
196 budget = budget.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
197 }
198 else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
199 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
200 actual = actual.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
201 }
202 else {
203 actual = actual.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
204 }
205 }
206 else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
207 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
208 encumb = encumb.add(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
209 }
210 else {
211 encumb = encumb.subtract(pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT));
212 }
213 }
214
215 // A balance exists, so we need to update it
216 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));
217 }
218 else {
219 insertCount++;
220
221 BigDecimal budget = new BigDecimal("0");
222 BigDecimal actual = new BigDecimal("0");
223 BigDecimal encumb = new BigDecimal("0");
224
225 if (balanceType.equals(options.getBudgetCheckingBalanceTypeCd())) {
226 budget = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
227 }
228 else if (balanceType.equals(options.getActualFinancialBalanceTypeCd())) {
229 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
230 actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
231 }
232 else {
233 actual = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
234 }
235 }
236 else if (balanceType.equals(options.getExtrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getIntrnlEncumFinBalanceTypCd()) || balanceType.equals(options.getPreencumbranceFinBalTypeCd()) || "CE".equals(balanceType)) {
237 if (debitCreditCode.equals(objectTypeDebitCreditCode) || (("N".equals(offsetGenerationCode) && KFSConstants.GL_BUDGET_CODE.equals(debitCreditCode)))) {
238 encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT);
239 }
240 else {
241 encumb = pendingEntryRowSet.getBigDecimal(GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT).negate();
242 }
243 }
244
245 // No balance exists, so we need to insert one
246 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, pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.REPORT_SORT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_LEVEL_CODE2), sessionId);
247 }
248 }
249 LOG.info("summarizePendingEntriesByLevel() INSERTS: " + insertCount);
250 LOG.info("summarizePendingEntriesByLevel() UPDATES: " + updateCount);
251 }
252 catch (RuntimeException ex) {
253 LOG.error("summarizePendingEntriesByLevel() Exception running sql", ex);
254 throw ex;
255 }
256 }
257
258 /**
259 * Fetches pending entries summarized by level matching the keys passed in as parameter, and then saves
260 * those summaries in a temporary table
261 * @param options a given set of system options
262 * @param universityFiscalYear the university fiscal year of pending entries to find
263 * @param chartOfAccountsCode the chart of accounts code of pending entries to find
264 * @param accountNumber the account number of pending entries to find
265 * @param financialConsolidationObjectCode the consolidation code of pending entries to find
266 * @param isCostShareExcluded whether to exclude cost share entries or not
267 * @param pendingEntriesCode whether to include all, approved, or no pending entries in this inquiry
268 * @param sessionId the unique web id of the currently inquiring user, used as a key for the temporary tables
269 * @return true if summarization process found pending entries to process, false otherwise
270 */
271 protected boolean getMatchingPendingEntriesByLevel(SystemOptions options, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String financialConsolidationObjectCode, boolean isCostShareExcluded, int pendingEntriesCode, String sessionId) {
272 LOG.debug("getMatchingPendingEntriesByLevel() started");
273
274 // If they have specified this year, we will get all the pending entries where the year is equal or the year is null
275 // (because most eDocs don't fill in the year field).
276 // If they have specified a previous year, we will get all the pending entries where the year is equal to their selection
277 // without the nulls (because we will post eDocs
278 // with blank years tonight most probably.
279
280 UniversityDate today = SpringContext.getBean(UniversityDateService.class).getCurrentUniversityDate();
281
282 clearTempTable("GL_PENDING_ENTRY_MT", "SESID", sessionId);
283
284 List<Object> params = new ArrayList<Object>(20);
285
286 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,"
287 + "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) ";
288
289 String selectSql = "SELECT ?, p.FS_ORIGIN_CD, p.FDOC_NBR, p.TRN_ENTR_SEQ_NBR," + " p.FIN_COA_CD, p.ACCOUNT_NBR, "
290 + getDbPlatform().getIsNullFunction("p.SUB_ACCT_NBR", "'-----'")
291 + ", p.FIN_OBJECT_CD, "
292 + getDbPlatform().getIsNullFunction("p.FIN_SUB_OBJ_CD", "'---'")
293 + ", 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,"
294 + " 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 "
295 + " FROM GL_PENDING_ENTRY_T p,CA_OBJECT_CODE_T o,CA_OBJ_LEVEL_T l,KRNS_DOC_HDR_T d,FS_DOC_HEADER_T fd "
296 + " WHERE o.FIN_COA_CD = p.FIN_COA_CD AND o.FIN_OBJECT_CD = p.FIN_OBJECT_CD AND o.UNIV_FISCAL_YR = ? "
297 + " 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.DOC_HDR_ID AND d.DOC_HDR_ID = fd.fdoc_nbr "
298 + " AND l.FIN_CONS_OBJ_CD = ?" + " AND p.FIN_COA_CD = ? AND p.account_nbr = ? ";
299 params.add(sessionId);
300 params.add(universityFiscalYear);
301 params.add(financialConsolidationObjectCode);
302 params.add(chartOfAccountsCode);
303 params.add(accountNumber);
304
305 if (pendingEntriesCode == AccountBalanceService.PENDING_APPROVED) {
306 selectSql = selectSql + " AND fd.fdoc_status_cd = '" + KFSConstants.DocumentStatusCodes.APPROVED + "' ";
307 }
308 else {
309 selectSql = selectSql + " AND fd.fdoc_status_cd <> '" + KFSConstants.DocumentStatusCodes.DISAPPROVED + "' ";
310 }
311 selectSql = selectSql + " AND fd.fdoc_status_cd <> '" + KFSConstants.DocumentStatusCodes.CANCELLED + "' ";
312 selectSql = selectSql + " AND p.FDOC_APPROVED_CD <> '" + KFSConstants.DocumentStatusCodes.CANCELLED + "' ";
313
314 if (today.getUniversityFiscalYear().equals(universityFiscalYear)) {
315 selectSql = selectSql + "AND (p.univ_fiscal_yr is null OR p.univ_fiscal_yr = ? )";
316 params.add(universityFiscalYear);
317 }
318 else {
319 selectSql = selectSql + "AND p.univ_fiscal_yr = ?";
320 params.add(universityFiscalYear);
321 }
322 getSimpleJdbcTemplate().update(insertSql + selectSql, params.toArray());
323
324 if (isCostShareExcluded) {
325 purgeCostShareEntries("GL_PENDING_ENTRY_MT", "sesid", sessionId);
326 }
327
328 if (!hasEntriesInPendingTable(sessionId)) {
329 return false;
330 }
331
332 fixPendingEntryDisplay(options.getUniversityFiscalYear(), sessionId);
333
334 return true;
335 }
336 }