Inquire on Account Balances

The “Inquire on Account Balances” form in EAS allows the retrieval of Accounting Flexfield balances:

  • For a Range of Accounting Periods
  • For a selected Currency
    • USD
    • STAT (used to store statistical amounts that can be used to build allocation journals to re-distribute costs based on some statistical value such as square footage, headcount, etc.)
  • For a selected Balance Type
    • Actuals
    • Budgets
      • Budget Name
    • Encumbrances
      • Commitments (Requisitions)
      • Obligations (Purchase Orders)
      • Manual (Invoices)
      • Manual Projections (budget reservations for future expenditures; currently only used by ISS)
  • For a range of Accounting Flexfields or Summary Accounts

Other information you should know when using the Account Inquiry form:

  • Only posted transactions are included. Stated another way, the Funds Available Inquiry Form includes all amounts where the funds have been reserved. For inclusion on the Account Inquiry form, the funds must have been reserved, approved, and posted.
  • Data is displayed on the Account Inquiry form in the order in which the AFF records were created. It is not sorted in the order of the AFFs. Therefore, it is important to look at all retrieved records when analyzing the data and not presume that gaps in the retrieved records mean that certain combinations don’t exist.
  • Currently, you can request data for Accounting Periods from Jun-95 through the latest period defined in the Calendar, which is Jun-06. However, the first period to which any dollar data was posted is Jul-96.
  • Since Actuals can only post to an Accounting Period that has been opened at some time, Actuals can generally only be found through the current period.
  • For Accounts classified as assets, liabilities, and fund balances, the amount in the YTD field is from inception to date, not just for the current fiscal year.
  • For Accounts classified as revenues and expenses, the amount in the YTD field is for the current fiscal year only. In other words, the beginning balance for the YTD field is reset to zero for the July Accounting Periods.
  • The QTD amount field resets to zero at the beginning of each quarter for all account types (assets, liabilities, fund balance, revenues, and expenditures). In other words, unlike the YTD field that shows inception to date for assets, liabilities, and fund balances, the QTD field only shows accumulated data for that quarter.
  • The PJTD amount field is never reset to zero and will always show inception to date for the record. In other words, for assets, liabilities, and fund balances, this amount field will always agree to the YTD field. For revenues and expenses, this amount field will reflect inception to date. Since revenues and expenses began to be loaded with fiscal year
  • Budgets can be posted to any period in a fiscal year where the Budget has been defined. Therefore, you can retrieve budget data through the end of the current fiscal year, or into the next fiscal year after the Budget Office has defined the next fiscal year’s budget and loaded the budgets into the next fiscal year.
  • Encumbrance data can be posted to any period for which the Encumbrance year has been opened. The next fiscal year’s encumbrance year is generally opened in early June so the Supply Chain organization can begin creating purchase orders for the next fiscal year. Therefore, generally up through early June, you may find Encumbrance data in future Accounting Periods for the current fiscal year.
  • Encumbrance data can only be looked at by Encumbrance type (commitments, obligations, manual, and manual projections). Unlike the Funds Available Inquiry form where you call see encumbrance totals grouping all four types together, the Account Inquiry form cannot group them.
  • More research needs to be done on Statistical data. Current suspicion is that, like Actuals, Statistical data can only be posted to an Accounting Period that has been opened at some time, and can generally only be found through the current period. Currently, no statistical data has been loaded.
  • Statistical data loaded to accounts classified as assets, carry its data across fiscal years; therefore, amount in the YTD amount field continue to accumulate. Statistical data loaded to accounts classified as expenses, do not carry its data across fiscal years; therefore, the beginning balance for the YTD amount field is reset to zero for July Accounting Periods.

Example #1

Find all “ACTUAL” USD transactions posted to a cost center for this fiscal year.
Navigate to the Account Inquiry form.

Account Inquiry Form selection

Provide a From Accounting Period of Jul-03 and a To Accounting Period of Feb-04 and allow the defaulted balance type of Actual.

Enter accounting period

Click your cursor on the LOV icon LOV iconand select Total by Org/Fund Source as the Summary Template.

Total by Org/FundSource selection

Click the OK button to return to the Account Inquiry form.
A Find Accounts window will appear. Provide your Organization and Funding Source values. Note that the format for the summary account of “Total by Org/Fund Source” retrieves totals for all segments other than Organization and Funding Source. For Organization and Funding Source, it summarizes at the detail values for these segments.

Add organization and funding source

Click the OK button to accept this “find” criteria. The Account Inquiry form will return with one selected AFF.

Completed account inquiry form

Click the Show Journal Details button to view the journals posted to your cost center.

Show journal details results page

Note that there are more journal lines than will fit in the window. Also note that the default format of fields displayed on the Journals form does not show the period to which these journals posted, the detail AFF to which the journals posted (we can only see the summary account in the blue bar), and the effective date for the journals.

Folder Technology

With folder functionality, you can add this data to the form and save the folder so next time you retrieve journals, you don’t have to re-add the columns. To do this, click on the folder icon Folder icon in the toolbar. The Folders window will open.

Folders window

Click on the “add a column” icon Add a column iconwithin the Folder window and select Period from the fields listed in the Show Field window.
Selection of Period in show field window

Click the OK button to add this field to your Journals form. Since we did not tell the Journals form where to place the field and since the cursor was residing in the Batch name field, this new field is added to the Journals form after the field where the cursor is located.

New column added to Journals form

For appearances sake, you can move the Period field to the first columnar position by clicking the left arrow icon Left arrow icon in the Folder window. The Period field is now moved to the left of Batch.

Column moved to left on form

To add the field Account to the Journals form, repeat the steps above to add the Account field (by clicking the “Add a field” icon in the Folder window, selecting the field Account, and clicking the Add button. Again, with the cursor located in the Period field, the Account field is added to the right of the Period field.

Addition of Account field

Next, remove the Currency field from the Journals form. To do this, use the scroll bar to bring the Currency column into view and click your cursor in the Currency field.

View of form showing currency field to be removed

Click the “Delete a field” icon Delete a field icon in the Folder window. Your Currency column has now been removed.

Currency field eliminated

Because you’ll generally want to view the line description, add the line description next to Source.

Addition of line description field

For appearances sake, size the fields so all the columns will show without a scroll bar. To do this, click your cursor in the fields to reduce in display size, and then click the “reduce column” Reduce column iconicon . Repeat this procedure to reduce all fields that you’d like reduced in display size. For this example, the columns of Entered Debits, Entered Credits, Period, Account, and Journal Entry were reduced.

Showing all columns without a scroll bar

Once you’ve gotten your window to the size that you want it to be, save your folder so you can use it again in the future. To do this, click the “save folder” icon Save folder iconin the Folder window. Name your folder. Since you may end up creating and saving multiple folders for a particular form, you should begin your folder’s name with your user name followed by abbreviated columnar names so when you look at your saved folders, you can recognize the columnar layout for your folders.

Saving of folder name

Click the OK button to save your folder. This folder will now be used for all journals viewed by this form automatically until you close your Account Inquiry form.

Folder view showing journals viewed by Account Inquiry session

NOTE: Do not change the Autoquery selection from Autoquery selection "Never" choice . Do not select Autoquery "Open as Default" selectionor Autoquery selection "Public" . If you accidentally change the Autoquery selection from Never to Always and check “Open as Default”, every time you go to the Journals form from the Account Inquiry form, this folder would open. If the folder only remembered the columns and not the data selected, it would be okay. But, instead the folder remembers the data selected. That means, if you saved your folder by looking at Org=617013 and the range of periods Jul-03 through Feb-04, and later used the Account Inquiry form to look at Org=617001 for Feb-04 only, instead of retrieving the journals for 617001, the auto-queried journals would be retrieved for the original selection when the form was saved.

If you select Public, this form becomes available to all users. EAS prefers to control the forms that are available for Public use, and not have user-created folders fill the available folders list of other users.

Close the Journals form and the Account Inquiry form.

Repeat these steps to open the Account Inquiry form and retrieve all journals for the same criteria as before (Accounting Periods of Jul-03 through Feb-04 for Actuals using the summary template of Total by Org/Fund Source for Org=617013, Funding Source = C100001). Note that the default Journals form is used.

Example of default journals form

To use a previously saved folder, click the folder icon within the Journals form.

Selection of folder icon

The Open Folder window appears for you to select from your saved folders list.

Open folder window

Click the OK button to enact your selection. Your retrieved journals will now display using your stored folder’s layout.

Stored folder's layout

Example #2

Find where Judith Umberger paid by credit card for a ski trip deposited to an unknown AFF. Amount should have been $499.00.

Use the Summary Template 40000 Revenue Rollup to search all revenues with Entity = UN.

Summary Template 40000 Revenue Rollup

Click the Show Journal Details.

Depiction of journal details

Open a folder that shows line description.

Folder showing line description

Enter query mode.

Entry into query mode

Run your query and the answer is found.
Answer shown on screenshot

Example #3

Find all journals posted to accounts = 44xxxx (gifts) for this fiscal year that did not feed from Development, Banner Student Accounts, or Banner Payroll Payroll (potential deposit or other data that may not be known by Advancement).

Initial screen to query gifts

Second screen querying gifts

WHERE Clause

At this point, the “where clause” functionality is handy because the field size of Source is too small to handle the statement “!= GW DEV and != GW StuAcct) and is too small to handle the same statement written as # NOT IN (GW DEV, GW StuAcct).

To open a “where clause” window, enter query mode. Type a colon in the key field for the form. For this form, the key field is Batch.

Query mode screen

Run your query either by selecting Run found under Query on the action line or by pressing the <F11> key while holding down the <Ctrl> key. A Where Clause window will open.

Where Query window

To use the Where Clause, you must know the names of the fields. To get them, type some junk in the Where Clause window and click the OK button.

First entry in Where query screen

The following message appears.

Oracle error message

Click the OK button to close this Error window. Select Display Database Error from the Help menu.

Help menu options

Find the name of the field that you need for your Where Clause and copy the field name.

SQL field name examples

Close the Database Error window. Run your query again for the Journals form (or press the <F11> to run the query). The Where Clause window will report showing the junk. Exchange the junk as shown below.

Correct entry in Where Clause window

Click the OK button to run the query. The results are shown below.

Query results

Use the stored journal folder to re-display this information showing line description and the other fields added to the stored folder.

Depiction of query results in saved folder format

If you scroll down the list of retrieved journals, you’ll note that some of the retrieved journals are fed from Payables.

View showing journals fed fro Payable invoices

Example #4

Knowing an account balance, find where the balance originated and the transactions causing the balance.

Initial appropriate account inquiry screen

Click the Show Balances button to determine when the transactions occurred.

Transaction date list

Use the scroll bar to find the first occurrence of the amount.

Reveal first occurrence of the amount

Secondary occurrence screen

Now knowing when the transactions occurred, seeing that they occurred in the Adjusting periods, and knowing that only the collapse of revenues and expenses to fund balances should occur in the Adjusting periods, we can now analyze the appropriateness of why these transactions collapsed to the fund balance to which they collapsed. (NOTE: What this appears to be in balances left in clearing Awards at fiscal year end.)

Example #5

Show the effects of encumbrances across fiscal years and view detail encumbrance journals. (NOTE: GL BPRs do not show detail encumbrance journals. Detail BPRs net all encumbrance journals into a one line summary journal and only show the net change in encumbrance balances between the beginning encumbrance balance for an Accounting Period and the ending encumbrance balance for an Accounting Period.)

Appropriate account inquiry screen entry

Because encumbrance cross fiscal years, to monitor the encumbrance activity you must always look at all periods from Jul-96 to current to retrieve all journals that affect the balances of encumbrances. Also, note that you can only view each Encumbrance Type individually. After retrieving the AFFs, click the show balances to see if the encumbrance balances across periods.

Show balances reveal encumbrance periods

Use the scroll bar to find the activity.

Screens showing no activity

Screens showing no activity

Screens showing no activity

Note that there is no Commitment (requisition) encumbrance balances.

Next, retrieve the Obligation (purchase order) encumbrances.

Screen #1 retrieving purchase order encumbrances

Screen #2 retrieving purchase order encumbrance

Screen #3 retrieving purchase order encumbrance

Note that the YTD balance as it crosses the fiscal year doesn’t appear to calculate properly (71,366.58 for May-03 minus 71,366.58 for Jun-03 minus 14.526.66 for Jul-03 = -14.526.66, not 66,683.34). To see what happened in Jun-03, click the field for Jun-03 and click the Detail Balances button.

Screen showing end of year discrepancy

Note that there are two detail AFFs making up the amount, so we must either look at each separately, or go back to the Inquiry Accounts form and retrieve the journals just for Jun-03. I’ll demonstrate the latter. Therefore, close this window and return to the Account Inquiry form.

Return to Account Inquiry form

Change the Accounting period as shown above and click the Show Journals button to retrieve all journals posted to the Jun-03 Accounting Period only.

View of journals posted only to Jun accounting period

Note that during the Accounting Period of Jun-03 that there was several transactions of normal Supply Chain activity (Source = Purchasing and Payables) and two transactions at year end to zero out or remove the encumbrance from the final BPRS. To emphasize this, shown below is a editor window showing the complete contents of the Journal Entry column (this is performed by clicking the Show Field icon Show Field iconwhile the cursor is located in the field that your want to see).

Editor window showing contents of Journal Entry column

Therefore, to understand the calculation recognizing that the amount shown in the Account Inquiry form is the net of all transactions for the month, here is what happened.

    Jun-03 Activity
Obligation balance at month end of May-03 71,366.58  
Cancellation of PO in Jun-03 -40,000.00 -40,000.00
New PO lines added in Jun-03 65,986.83 65,986.83
Payments on Pos in Jun-03 -16,143.41 -16,143.41
Total Jun-03 SC activity 9,843.42  
Balance of Jun-03 carried forward into Jul-03 81,210.00  
Zeroing out journal activity for Jul-03 -81,210.00 -81,210.00
Balance at end of Jun-03 0.00  
Net activity in PTD column for Jun-03   -71,366.58
Balance at beginning of Jul-03 81,210.00  
Jul-03 activity -14,526.66  
Balance at end of Jul-03 66,683.34