View Javadoc
1   /*
2    * Copyright 2006 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.batch.dataaccess.impl;
17  
18  import java.util.Iterator;
19  import java.util.List;
20  
21  import org.apache.ojb.broker.query.Criteria;
22  import org.apache.ojb.broker.query.QueryByCriteria;
23  import org.apache.ojb.broker.query.QueryFactory;
24  import org.apache.ojb.broker.query.ReportQueryByCriteria;
25  import org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao;
26  import org.kuali.ole.gl.businessobject.SufficientFundBalances;
27  import org.kuali.ole.sys.OLEConstants;
28  import org.kuali.ole.sys.OLEPropertyConstants;
29  import org.kuali.ole.sys.businessobject.GeneralLedgerPendingEntry;
30  import org.kuali.ole.sys.util.TransactionalServiceUtils;
31  import org.kuali.rice.core.api.util.type.KualiDecimal;
32  import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
33  
34  /**
35   * An OJB implementation of SufficientFundsDao
36   */
37  public class SufficientFundsDaoOjb extends PlatformAwareDaoBaseOjb implements SufficientFundsDao {
38      private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SufficientFundsDaoOjb.class);
39  
40      private static final String YEAR_END_DOC_PREFIX = "YE%";
41  
42      /**
43       * Constructs a SufficientFundsDaoOjb instance
44       */
45      public SufficientFundsDaoOjb() {
46      }
47  
48      /**
49       * Calculate the Prior Fiscal Year Budget total
50       * 
51       * @param universityFiscalYear the university fiscal year of sufficient funds balances that will be summarized
52       * @param chartOfAccountCode the chart of accounts code of sufficient fund balance records that will be summarized
53       * @param accountNumber the account number of sufficient fund balances that will be summarized
54       * @return the sum of the prior fiscal year budget
55       * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PfyrBudget(java.lang.Integer, java.lang.String,
56       *      java.lang.String)
57       */
58      public KualiDecimal calculateM113PfyrBudget(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
59          Criteria criteria = new Criteria();
60          criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
61          criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
62          criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
63          criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_CODE_PROPERTY_NAME, OLEConstants.SF_TYPE_CASH_AT_ACCOUNT);
64  
65          ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(SufficientFundBalances.class, criteria);
66          reportQuery.setAttributes(new String[] { OLEConstants.CURRENT_BUDGET_BALANCE_AMOUNT_PROPERTY_NAME });
67  
68  
69          return executeReportQuery(reportQuery);
70      }
71  
72      /**
73       * Calculate the prior fiscal year encumbrance total
74       * 
75       * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
76       * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
77       * @param accountNumber the account number of sufficient fund balances to summarize
78       * @return the prior fiscal year encumbrnace total
79       * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PfyrEncum(java.lang.Integer, java.lang.String, java.lang.String)
80       */
81      public KualiDecimal calculateM113PfyrEncum(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
82          Criteria criteria = new Criteria();
83          criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
84          criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
85          criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
86          criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_CODE_PROPERTY_NAME, OLEConstants.SF_TYPE_CASH_AT_ACCOUNT);
87  
88          ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(SufficientFundBalances.class, criteria);
89          reportQuery.setAttributes(new String[] { OLEConstants.ACCOUNT_ENCUMBRANCE_AMOUNT_PROPERTY_NAME });
90  
91          return executeReportQuery(reportQuery);
92      }
93  
94      /**
95       * Calculate the prior fiscal year pending actual amount
96       * 
97       * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
98       * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
99       * @param accountNumber the account number of sufficient fund balances to summarize
100      * @param specialFinancialObjectCodes this actually doesn't seem to be used
101      * @param financialObjectCodeForCashInBank the object code for cash in the bank
102      * @return the prior fiscal year pending actual amount
103      * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PendActual(boolean, java.lang.Integer, java.lang.String,
104      *      java.lang.String, List, String)
105      */
106     public KualiDecimal calculateM113PendActual(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, List specialFinancialObjectCodes, String financialObjectCodeForCashInBank) {
107         // fp_sasfc:61-2...78-3 m113 calculate pendActual
108         KualiDecimal pendActual = calculateM113PendActual1(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, true, financialObjectCodeForCashInBank);
109         pendActual = pendActual.subtract(calculateM113PendActual1(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, false, financialObjectCodeForCashInBank));
110         pendActual = pendActual.add(calculateM113PendActual2(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, false, specialFinancialObjectCodes));
111         pendActual = pendActual.subtract(calculateM113PendActual2(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, true, specialFinancialObjectCodes));
112 
113         return pendActual;
114 
115     }
116 
117     /**
118      * Calculates the current pending actual
119      * 
120      * @param isYearEndDocument should year end documents be included?
121      * @param actualFinancialBalanceTypeCd the actual balance type code
122      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
123      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
124      * @param accountNumber the account number of sufficient fund balances to summarize
125      * @param acctSufficientFundsFinObjCd the object code for sufficient funds
126      * @param expenditureCodes object codes that represent expenditures
127      * @return the current pending actual total
128      * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendActual(boolean, java.lang.String, java.lang.Integer,
129      *      java.lang.String, java.lang.String, java.lang.String, List)
130      */
131     public KualiDecimal calculatePendActual(boolean isYearEndDocument, String actualFinancialBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
132         KualiDecimal pendActual = calculatePendActual1(isYearEndDocument, actualFinancialBalanceTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, true, expenditureCodes);
133         pendActual = pendActual.subtract(calculatePendActual1(isYearEndDocument, actualFinancialBalanceTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, false, expenditureCodes));
134         return pendActual;
135     }
136 
137     /**
138      * calculates the current year pending budget total
139      * 
140      * @param isYearEndDocument should year end documents be included?
141      * @param budgetCheckingBalanceTypeCd the budget balance type code
142      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
143      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
144      * @param accountNumber the account number of sufficient fund balances to summarize
145      * @param acctSufficientFundsFinObjCd the object code for sufficient funds
146      * @param expenditureCodes object codes that represent expenditures
147      * @return calculates the current year pending budget total
148      * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendBudget(boolean, java.lang.String, java.lang.Integer,
149      *      java.lang.String, java.lang.String, java.lang.String, List)
150      */
151     public KualiDecimal calculatePendBudget(boolean isYearEndDocument, String budgetCheckingBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
152         Criteria criteria = new Criteria();
153         criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, budgetCheckingBalanceTypeCd);
154         criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
155         criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
156         criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
157         criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
158         criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
159 
160         if (isYearEndDocument) {
161             criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
162         }
163         else {
164             criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
165         }
166 
167         ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
168         reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
169 
170         return executeReportQuery(reportQuery);
171 
172     }
173 
174     /**
175      * Calculates the current year pending encumbrance total
176      * 
177      * @param isYearEndDocument should year end documents be included?
178      * @param extrnlEncumFinBalanceTypCd the external encumbrance balance type
179      * @param intrnlEncumFinBalanceTypCd the internal encumbrance balance type
180      * @param preencumbranceFinBalTypeCd the pre-encumbrance balance type
181      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
182      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
183      * @param accountNumber the account number of sufficient fund balances to summarize
184      * @param acctSufficientFundsFinObjCd the object code for sufficient funds
185      * @param expenditureCodes object codes that represent expenditures
186      * @return the current year pending encumbrance total
187      * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendEncum(boolean, java.lang.String, java.lang.String,
188      *      java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, List)
189      */
190     public KualiDecimal calculatePendEncum(boolean isYearEndDocument, String extrnlEncumFinBalanceTypCd, String intrnlEncumFinBalanceTypCd, String preencumbranceFinBalTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
191         KualiDecimal pendEncum = calculatePendEncum1(isYearEndDocument, extrnlEncumFinBalanceTypCd, intrnlEncumFinBalanceTypCd, preencumbranceFinBalTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, true, expenditureCodes);
192         pendEncum = pendEncum.subtract(calculatePendEncum1(isYearEndDocument, extrnlEncumFinBalanceTypCd, intrnlEncumFinBalanceTypCd, preencumbranceFinBalTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, false, expenditureCodes));
193         return pendEncum;
194     }
195 
196     /**
197      * Calcluate this part of the encumbrance total
198      * 
199      * @param isYearEndDocument should year end documents be included?
200      * @param extrnlEncumFinBalanceTypCd the external encumbrance balance type
201      * @param intrnlEncumFinBalanceTypCd the internal encumbrance balance type
202      * @param preencumbranceFinBalTypeCd the pre-encumbrance balance type
203      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
204      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
205      * @param accountNumber the account number of sufficient fund balances to summarize
206      * @param acctSufficientFundsFinObjCd the object code for sufficient funds
207      * @param isEqualDebitCode should debits be included in the calculation or not
208      * @return this part of the encumbrance total
209      */
210     protected KualiDecimal calculatePendEncum1(boolean isYearEndDocument, String extrnlEncumFinBalanceTypCd, String intrnlEncumFinBalanceTypCd, String preencumbranceFinBalTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, boolean isEqualDebitCode, List expenditureCodes) {
211         Criteria criteria = new Criteria();
212 
213         Criteria sub1 = new Criteria();
214         sub1.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, extrnlEncumFinBalanceTypCd);
215         Criteria sub1_1 = new Criteria();
216         sub1_1.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, intrnlEncumFinBalanceTypCd);
217         Criteria sub1_2 = new Criteria();
218         sub1_2.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, preencumbranceFinBalTypeCd);
219         sub1_1.addOrCriteria(sub1_2);
220         sub1.addOrCriteria(sub1_1);
221         criteria.addOrCriteria(sub1);
222 
223 
224         criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
225         criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
226         criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
227         criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
228         criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
229 
230         if (isEqualDebitCode) {
231             criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
232         }
233         else {
234             criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
235         }
236 
237         criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
238 
239         if (isYearEndDocument) {
240             criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
241         }
242         else {
243             criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
244         }
245 
246         ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
247         reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
248 
249         return executeReportQuery(reportQuery);
250 
251 
252     }
253 
254     /**
255      * Calculate this part of the actual total
256      * 
257      * @param isYearEndDocument should year end documents be included?
258      * @param actualFinancialBalanceTypeCd the actual balance type code
259      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
260      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
261      * @param accountNumber the account number of sufficient fund balances to summarize
262      * @param acctSufficientFundsFinObjCd the object code for sufficient funds
263      * @return this part of the actual total
264      */
265     protected KualiDecimal calculatePendActual1(boolean isYearEndDocument, String actualFinancialBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, boolean isEqualDebitCode, List expenditureCodes) {
266         Criteria criteria = new Criteria();
267         criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, actualFinancialBalanceTypeCd);
268         criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
269         criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
270         criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
271         criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
272 
273         if (isEqualDebitCode) {
274             criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
275         }
276         else {
277             criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
278         }
279 
280         criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
281 
282         if (isYearEndDocument) {
283             criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
284         }
285         else {
286             criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
287         }
288 
289         ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
290         reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
291         return executeReportQuery(reportQuery);
292     }
293 
294     /**
295      * calculate part of the actual total
296      * 
297      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
298      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
299      * @param accountNumber the account number of sufficient fund balances to summarize
300      * @return thsi part of the actual total
301      */
302     protected KualiDecimal calculateM113PendActual1(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isEqualDebitCode, String financialObjectCodeForCashInBank) {
303         Criteria criteria = new Criteria();
304         criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, OLEConstants.BALANCE_TYPE_ACTUAL);
305 
306         if (financialBeginBalanceLoadInd) {
307             criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
308         }
309         else {
310             Criteria sub1 = new Criteria();
311             sub1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
312             Criteria sub1_1 = new Criteria();
313             sub1_1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
314             sub1.addOrCriteria(sub1_1);
315             criteria.addAndCriteria(sub1);
316         }
317 
318         criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
319         criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
320         criteria.addEqualTo(OLEConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, financialObjectCodeForCashInBank);
321 
322         if (isEqualDebitCode) {
323             criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
324         }
325         else {
326             criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
327         }
328 
329         criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
330 
331         ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
332         reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
333 
334         return executeReportQuery(reportQuery);
335     }
336 
337     /**
338      * Calculate part of the actual total
339      * 
340      * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
341      * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
342      * @param accountNumber the account number of sufficient fund balances to summarize
343      * @param isEqualDebitCode should this query be returning debits or not?
344      * @param specialFinancialObjectCodes include only these financial object codes
345      * @return this part of the actual total
346      */
347     protected KualiDecimal calculateM113PendActual2(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isEqualDebitCode, List specialFinancialObjectCodes) {
348         Criteria criteria = new Criteria();
349         criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, OLEConstants.BALANCE_TYPE_ACTUAL);
350 
351         if (financialBeginBalanceLoadInd) {
352             criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
353         }
354         else {
355             Criteria sub1 = new Criteria();
356             sub1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
357             Criteria sub1_1 = new Criteria();
358             sub1_1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
359             sub1.addOrCriteria(sub1_1);
360             criteria.addAndCriteria(sub1);
361         }
362 
363         criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
364         criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
365         criteria.addIn(OLEConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, specialFinancialObjectCodes);
366 
367         if (isEqualDebitCode) {
368             criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
369         }
370         else {
371             criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
372         }
373 
374         criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
375 
376         ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
377         reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
378 
379         return executeReportQuery(reportQuery);
380     }
381 
382     /**
383      * Purge table by year/chart
384      * 
385      * @param chart the chart of sufficient fund balances to purge
386      * @param year the year of sufficient fund balances to purge
387      */
388     public void purgeYearByChart(String chartOfAccountsCode, int year) {
389         LOG.debug("purgeYearByChart() started");
390 
391         Criteria criteria = new Criteria();
392         criteria.addEqualTo(OLEPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
393         criteria.addLessThan(OLEPropertyConstants.UNIVERSITY_FISCAL_YEAR, new Integer(year));
394 
395         getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(SufficientFundBalances.class, criteria));
396 
397         // This is required because if any deleted account balances are in the cache, deleteByQuery doesn't
398         // remove them from the cache so a future select will retrieve these deleted account balances from
399         // the cache and return them. Clearing the cache forces OJB to go to the database again.
400         getPersistenceBrokerTemplate().clearCache();
401     }
402 
403     /**
404      * This returns the very first value returned by a report query and then makes certain that OJB closes the 
405      * connection that retrieved the query data
406      * 
407      * @param reportQuery the ReportQuery to find the first value for
408      * @return the first value generated from the given query
409      */
410     protected KualiDecimal executeReportQuery(ReportQueryByCriteria reportQuery) {
411         Iterator iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQuery);
412         if (iterator.hasNext()) {
413             KualiDecimal returnResult = (KualiDecimal) ((Object[]) TransactionalServiceUtils.retrieveFirstAndExhaustIterator(iterator))[0];
414             return returnResult;
415         }
416         else {
417             return KualiDecimal.ZERO;
418         }
419     }
420 }