Accounting Query Information

In addition to IMS databases, Accounting data is stored in DB2 tables, which provide ad hoc reporting capability for campus clients. These tables are accessible through Query Management Facility (QMF) which is a tool for querying DB2 data tables and creating ad hoc reports. QMF can be accessed through a TSO session or the new QMF for Windows client. QMF for Windows allows a user to point and click various options for reporting and data retrieval in a user friendly environment. This software is site licensed and available to UGA employees via download from link above.

For new QMF users, a prompted query option is available through QMF for Windows which allows a user to choose available options from a list. Below are steps for creating a basic prompted query to get budget balance of an account(s):

• To create a prompted query using Accounting tables, click on File-New-Prompted Query .
• Then click on the yellow plus (+) button on the Tables option. Type in QUERY in the table owner field, leaving the table name field blank, and click on Add From List.
• When requested, key in your IMS UserID and password. A list of available tables will be displayed to choose from. Click here for a more descriptive list of the content of the accounting tables including the column (field) names and definitions.
• Choose table ACC_SUMMARY_OBJECT and click on Add. (For advanced queries, the join table option can be used to join 2 or more tables together with a common field.) Then click on Close.
• Then choose the columns for your report.
  • Choose ACCOUNT and click on Add.
  • Then choose SUMMARY_OBJECT and click on Add.
  • Then choose FY_BUDGET and click on Add.
  • Then choose CURR_ENCUMBRANCE and click on Add.
  • Then choose FY_ACTUAL and click on Add.
  • Finally choose BUDGET_BALANCE, click on Add, and then click on Close.
• The next item is optional where sort conditions can be added for sorting the choosen columns.
• To select the items for the query, use row conditions.
  • Choose FISCAL_YEAR is equal to '2000' and click on Add. Note that all selection data should be keyed in uppercase with single quotes except for dollar amounts. (QMF for Windows version 6.1.1f or greater will put the single quotes automatically.)
  • Then choose DEPARTMENT is equal to a 3 digit department number and click Add.
  • Then choose SUMMARY_OBJECT is greater than or equal to '64000' for all summary object codes equal to or greater than 64000. Finally, click on Close.
• Now run the query by clicking on the exclamation point button on the toolbar or by clicking on Query-Run.
• A user can save a query to their library by clicking on the toolbar button with the arrow pointing into the yellow cylinder or by clicking on File-Save At Server.
• To open a saved query from the server, click on the toolbar button with the arrow pointing out of the yellow cylinder or by clicking on File-Open From Server. Then type in a userID in the Owner field and click on List Objects. This will list all members of that owner's (userID) library.
• Forms (reports) can then be created for a formatted printed output or you can copy & paste the data directly into a spreadsheet program. Click here for some basic steps on creating and formatting a form.

Additional Information:

The tables are related to IMS screens, like the Actual Transaction table contains data found on the AT screen in the IMS Financial Accounting system . The Summary Object table is equivalent to the OT screen.

Note: All IMS UserIDs with access to Financial Accounting have access to these Accounting DB2 tables.

QMF manuals are available at the University Bookstore, including Getting Started with QMF for Windows.