Controller

Chapter 5: Financial Data Queries

Quick Links

On this page:

Overview

WINGS Express Finance offers many ways to query financial data. The three budget queries are Query by Account, Query by Organizational Hierarchy, and Budget Quick Query. There is also a separate Encumbrance Query, Transaction Detail Report, and Balance Available Report.

  • View a comparison of three different Budget Queries (Note that we don't recommend using the Budget Quick Query due to its limitations, so it isn't discussed here.)
  • When you view these queries you will not be able to change underlying data. You cannot "mess anything up" that you are viewing.
  • Depending on the query you use, you will be able to view data at the data-enterable level and the nondata-enterable level. As a reminder,
    • Data-enterable orgs are the 6-digit lowest level of hierarchy where we post (e.g. 218305).
    • Non data-enterable orgs represent alpha-numeric intermediate roll-up elements that may be used for report subtotaling, granting access to users, etc. (e.g. CAT555 or CAT55).
  • When navigating through a query and you find a number that is green and underlined, you may find additional details about this number by clicking on it or "drilling down". This is described in the step-by-step document below.
  • Generally budgets are input at a more general budget pool level (e.g. account 730000 Supplies Budget Pool) and then expensed at more detailed levels (e.g. 730100 Office Supplies, 730200 Software Supplies).
  • Salary detail is not provided in WINGS Express Finance, altho users granted access may drill through a Finance Query Budget salary or benefit account YTD amount into the related Payroll module detail.
     


Operating Ledger Column Data

  • Since we talk about the Operating Ledger Column Data for the next couple of bullets, please see the image below to be familiar with the screen:

    Operating Ledger Columns
    Operating Ledger Columns
  • After you choose the budget query type and click the create query button, you will choose the Operating Ledger Columns to display. Recommended columns are Adopted Budget, Budget Adjustment, Year to Date, Encumbrances, Reservations, and Available Balance. Other columns are available that may be useful for certain data. Also, view the Operating Ledger Data Columns Guide for additional details about each column and the information it provides.
  • To understand what the columns mean -- here is a comparison with a personal checkbook:
WINGS Express Finance Checkbook
Adopted Budget Initial Balance
Budget Adjustments Deposits/Bank Fees
Year to Date (Expenditures) Checks Cleared
Encumbrances&Reservations Outstanding Checks
Available Balance Available Balance


FOAPAL Parameter Screen

  • Since we will talk about the FOAPAL parameter screen throughout the next few bullets, please see the image below to be familiar with the screen:

    Budget Query FOAPAL Parameters
    Budget Query FOAPAL Parameters
  • The image above shows a table describing accounting information you may use to query and highlights those fields that are required for a successful query. Also, view the Information Used to Query Guide.
  • Wildcards: You can use the % wildcard when filling in the Fund, Org, Account, or Program information at the FOAPAL parameter screen. For example, org 2183% would return all orgs that begin with 2183xx for which you have fund/org security.
  • Code Lookup within the FOAPAL parameter screen: You may click on the parameter button (e.g., fund, org, etc.) to go to Code Lookup and find the information you need to complete that particular field. View the Step-by-Step Guide to Budget Query Code Lookup.
  • At the Budget Query Parameter screen, you can use the Index field to get the fund/org combination.
  • At the Budget Query Parameter screen, you can save a query if this is information that you will access often.
  • The first time you run a query in WINGS Express Finance, you will need to manually input the Chart of Accounts = W (capital W). Afterwards, when you run queries the W defaults in for you.
  • The Comparison Fiscal Year and Fiscal Period fields are discussed in the subsection below.
     


Fiscal Year and Periods

  • The WSU fiscal year runs July 1 through June 30.
  • See Chapter 2 Chart of Accounts Structure for more detail about Fiscal Year & Period.
     


Query Budget Status by Account

  • This query allows you to look up budget information by account.
  • Note that you may click any of the buttons next to the FOAPAL input boxes to get a list or search for specific codes, see FOAPAL parameter screen section above.
  • This query provides accounting information for a specific organization or account, sorted in account number order.
  • To enter the organization code, you must use 6-digit data-enterable level orgs. This query does not allow for hierarchy roll up, but is the quickest way to get to details on transactions.
  • Type a % (percent sign) into the Program field to add a column to the output where the Program Code will display.
  • You may choose the parameters for the budget query.
  • View the Step-by-Step Guide to Budget Query by Account.
  • Columns resulting from your query can be downloaded to Excel. See chapter on Using Excel to Analyze Finance Data.
  • See subsection below to learn how to Compute Additional Columns in your query.
     


Query Budget Status by Organizational Hierarchy

  • This query allows you to review budget information for the Fiscal Period and Year-to-Date by: specific funds, high-level/hierarchy organizations, accounts, programs, fund type, & account type.
  • Note that you may click any of the buttons next to the FOAPAL input boxes to get a list or search for specific codes, see FOAPAL parameter screen section above.
  • For basic information on the organizational hierarchy, see Chapter 2.
  • Detail transactions can be viewed by drilling down to individual organizations, budget pool summaries, and then actual transactions.
  • You may choose the parameters for the budget query. You may enter non data-enterable orgs to see subtotals.
  • View the Step-by-Step Guide to Query Budget Status by Organizational Hierarchy
  • Columns resulting from your query can be downloaded to Excel. See chapter on Using Excel to Analyze Finance Data.
  • See subsection below to learn how to Compute Additional Columns in your query.
  • Known defect: If you are querying an org hierarchy and there are orgs within that section of the hierarchy that are now closed, the subtotals will reflect the amounts of the closed orgs but the individual closed orgs themselves won't appear in the detail. The software vendor is aware of this Oracle-related issue and we look forward to a "patch" to fix it. In the meantime, querying the individual closed orgs, or querying as of a date when the orgs were still active/open will provide detail information. The subtotals are correct as displayed.
     


Comparing Fiscal Years and Periods and Computing Additional Columns

  • The FOAPAL Parameter screens for Query Budget Status by Account and Query Budget Status by Organizational Hierarchy offer an option to compare Fiscal Year and Fiscal Period data. Additional columns for the comparison period are added to the report.

For best results, your Comparison Year should be older than your Fiscal Year defined, .e.g FY16 with Comparison FY15. (Otherwise, you may get different $ results in instances where there are FOAPALs in the newer year which didn't yet exist in the older year). The query uses the Fiscal Year defined to pull FOAPAL titles, etc & applies them to the comparison year.

  • In the Query Budget Status by Account and Query Budget Status by Organizational Hierarchy reports, you have the option at the bottom of the screen to compute additional columns for the query. For example, if you want to compare this fiscal period to the last fiscal period for year-to-date expenditures only. Use the dropdown arrows to select appropriate information and then name the new column (e.g., current month expenditures). When you click the perform computation button, the new column will appear showing only the current month expenditures that you computed.


Viewing Pending Documents via Query Budget

Effective with the May 2013 WINGS Express Finance upgrade, users may see a yellow triangle icon at the top right of their Query Budget output if there are Pending Documents for the FOAPAL parameters selected. The initial Query Budget output reflects all transactions which have posted to-date, as it always has displayed.

Query Budget Link
Query Budget Link

Clicking the optional "View Pending Documents" link opens a new web page:

  • Any Pending Documents: These may typically include document types below, but will NOT include 5xxxxx revenue account codes. These are documents which Banner is including in the Available Balance calculation but which are not yet posted.

​

Pending Documents
Pending Documents
  • Starting July 2017, Pending documents here only include Requisitions and Purchase Orders, which should be few and far between since these documents typically feed from WrightBuy. (To see ALL unposted documents in the system, use the View Document link instead of Query Budget. This is a bit confusing in that our vendor uses the same Pending designation to mean ALL unposted documents via View Document and to mean JUST those unposted documents designated to be included in Available Balance calculations via Query Budget.)
  • Pending documents do not include documents which feed to Banner Finance from other systems (e.g. WrightBuy Requisitions & POs) or other Banner modules (e.g. Fxxxxx Journal Vouchers fed from the Bursar's Office or Payroll).
  • The View Pending Documents button will not be present for queries where a grant code is input.
  • Available Balance Summary:
    • This section shows adjusted Available Balance amounts, taking into account any Pending Documents above. This section uses a "key" of Fund, Org, and Account -- so if you use certain wildcards or have also included program, activity, or location codes, the output in this section may be different from that displayed in your original Query Budget.
    • Available Balance Summary groups accounts in your fund-org by Account Pool.
    • It's these Available Balance amounts by Account Budget Pool which Banner checks against when you are creating a Purchase Requisition in WrightBuy which does not have a credit card number associated with it: if you are charging a fund-org-account which does not have enough Available Balance showing for the related Account Budget Pool, you will see a "Warning" message recorded in the History tab for that Requisition in WrightBuy for certain funds. For funds 190000, 190001, 190002, and 4xxxxx (excluding the Nutter Center) WrightBuy will cause the Requisition to ERR OUT and reject instead of issuing a Warning. This budget check in WrightBuy occurs when you first complete your cart & create the Requisition, during the Banner System Validation step.
    • The Available Balance Summary section does not include revenue account codes 5xxxxx.

Clicking the Download button will download both the Pending Documents and Available Balance Summary sections into Excel.
 


Encumbrance Query

The Encumbrance Query displays detail about amounts that are encumbered for the FOAPAL input. The points below provide more detail/definitions re what these amounts represent.

  • Commitments are obligations of the university to purchase goods/services.
    • Commitments are typically established for larger-dollar obligations.
    • They are posted to help us see that part of our monies are obligated, even though they have not yet been spent.
    • The actual expenditure may turn out to be slightly different than the initial commitment.
    • Not all expenditures are committed first (e.g., Procard charges).
    • Commitments do not post to Finance until they are approved. A requisition that is still in the approval process in WrightBuy will not be posted to Finance. 
  • Sample flow of encumbrances
    • Requisition is prepared and approved in WrightBuy > Creates a PO in WINGS Express Finance > PO posts as an Encumbrance > When the item is received and the invoice input, the encumbrance is liquidated and the expenditure is recorded. 
  • Commitments include two types of commitments: Encumbrances and Reservations:
    • Encumbrances make up the majority of commitments and can include:
      • Purchase Orders: POs (other than blanket orders) are contractual obligations of WSU that post as an Encumbrance with a Pxxxxx document code. Standing POs used to have a second alpha character of S & a third alpha character which represents the fiscal year for which they were created (e.g. PSPxxxxx represents a standing order for FY09). As invoices and/or adjustments are posted related to the PO, its balance will be reduced. Once the balance is zero, it may be closed. POs typically post to 7xxxxx non-labor accounts.
      • Salaries & Benefits: Salaries and benefits for certain employee types (e.g. unclassified, classified, faculty) are encumbered in Finance via the HR/Payroll module posting as PRYYxxxx where YY is the fiscal year. Typically, near the beginning of each academic year, the initial encumbrance is loaded for that fiscal year. As expenses post each pay period, the encumbrance outstanding is adjusted/liquidated and also reflects new appointments not included in the beginning of the year encumbrance amount. Payroll encumbrances post to 6xxxxx labor accounts. Finance does not include the pay detail by employee, those are housed in the HR/Payroll module that feeds to Finance.
      • University encumbrances for certain items: At the beginning of each fiscal year, Budget Planning and Resource Analysis encumbers the budgeted amount for some transfers (87xxxx accounts). During the fiscal year, expense is posted in the YTD column and the encumbrance is adjusted/reduced accordingly. These entries hold the balance available for the related accounts to $0. Note that transfer accounts with credit budgets cannot be encumbered, since Banner will not permit encumbering of negative numbers.
      • Phone Usage: After the TExxxx Telecom feeds post each month, encumbrance entries are posted to use the most current month's expense for line charges in account 755100 and project the expected cost through the end of the fiscal year. Each month, the previous month's encumbrance is then fully liquidated.
      • Facilities & Admin encumbrances on sponsored programs: Research and Sponsored Programs encumbers the 799000 account F&A for each 6xxxxx sponsored program fund. Then, as direct expenses post to the sponsored program, the related F&A is expensed in the YTD column and the F&A encumbrance is adjusted/reduced. The balance available for 799000 F&A account is typically $0. This posting of F&A encumbrances occurs nightly as an EFxxxxx document code.
      • Liquidating encumbrances: Some Purchase Orders actually cost less than the original encumbrance. After all expenditures have posted against the PO and if there are still funds encumbered, contact the Purchasing Department to have the funds disencumbered and the PO closed. An easy way to do this is to e-mail Purchasing. Give them the PO#, vendor, FOAP, and amount to be disencumbered.
    • Reservations in Banner only result from a Banner Purchase Requisition. Since the conversion to WrightBuy in Fiscal Year 2008, there are very few Banner Purchase Requisitions or Reservations. A WrightBuy change order request will post to Banner first as a Banner Requisition (Rxxxxxx document code which posts as a positive Reservation) in the Reservations column. Once the Purchasing Department adjusts the related Banner PO, the Reservation is liquidated (credited) in the Reservation column and is added (positive amount) to the Encumbrances column.
      • Note that WrightBuy requisitions for orders other than standing orders never post to Banner. The requisition remains in WrightBuy only. The WrightBuy PO generated for the non-credit card Requisition is what posts to Banner Finance (as a Pxxxxx document code) in the Encumbrances column.
      • Reservations are not included in the WINGS Express Finance Query Encumbrance.
  • Query Encumbrance
    • By selecting the Query Encumbrance link at the WINGS Express Finance home page, you can view all encumbrances as listed above.
    • The query encumbrance parameter page is very similar to the Budget Query Parameter pages and functions the same with wildcard and code lookup features.
      Query Encumbrance
      Query Encumbrance
      ​​
      • The encumbrance status box allows the option to view ALL encumbrances or only those still OPEN or only those that are CLOSED. Note that the status is determined at the time the query is run, not at the end of the period selected. (E.g. an Encumbrance Query run on Nov 12 for period 03 and OPEN encumbrances, will display any encumbrances open at Nov 12 which had balances at Sept 30.)
      • The commitment type box is not a function that is used at WSU.
      • If you are checking encumbrances for direct expenditures only (i.e., your operating budget), you may want to populate the œAccount box with 7% so it will bypass all salary and benefit account code lines. However, there are a few 6xxxxx POs that you may need to watch for especially if you have contract labor.
      • View the Step-by-Step Guide to Encumbrance Query.
  • You can tell an encumbrance has been liquidated when it appears with parentheses in the Encumbrance Liquidated column and has moved to the Year-to-Date column as an expenditure
     


Transaction Detail

Transaction Detail query is very useful to get a list of all transactions for a given fund/org for monthly reconciliations or to search for specific transactions you are interested in. This report was created in-house at WSU.

December 2014 brought some additional enhancements including:

  • Validation of input fields as you are keying them in. Use the arrow or cursor to select the item you want from the picklist.
  • Addition of Index field to assist with data-input.
  • Increase in output rows per page from 50 to 100.
  • Increase in timeout from 30 minutes to 1 hour.
  • Drill-through enabled for General Encumbrance and Direct Cash Receipt documents.
  • Optional calendars to use for date inputs.
  • When you select a single fund, org, and/or account for a given query from the validation picklists, the title for that fund, org, account also appears. (If you include multiple funds, orgs, accounts, or use % wildcards, the multiple titles do not appear. Also, if you tab through the input fields instead of selecting from the picklist, the title won't appear.)

These features continue as well:

  • "Clear Critera" button allows you to start with a clean criteria slate. Double-click in a single input cell to clear only that cell.
  • Ability to query across fiscal years and/or periods.
  • Ability to select multiple specific FOAPALs separated by commas (e.g. org = 215220,215250). Note that if you use comma-separation, please do not include blank spaces or include % wildcards.
  • When running queries for Grants, a Grant column appears in the output.
  • Output columns at far right include OrgLevel5 code and description, which are equivalent to our departments.

After clicking the Transaction Detail link, a pop-up window appears where you may select your criteria and then submit your query.

  • Transaction Detail query displays the criteria inputs you selected the last time you submitted a Transaction Detail query.
  • Operating Ledger Columns: the columns output are the same as those previously available in the prior version of Transaction Detail. Note that selecting only the columns you are interested in speeds up your query. Operating Ledger Data Columns Guide
  • Fiscal Year: Selecting 1 fiscal year, as shown, pulls transactions for that fiscal year only. Selecting different start and end fiscal years will pull transactions for all the years selected & in between.
  • Fiscal Period: Selecting 1 fiscal period, as shown, pulls transactions for that fiscal period only. When selecting a beginning and ending fiscal period (e.g. Begin Period = 01, End Period = 03), the radio buttons directly underneath fiscal period determine whether transactions returned are in the range (e.g. 01 through 03, which would include 02) or the selected periods only (01 and 03 only).
  • FOAPAL elements: to select more than 1 FOAPAL element at a time, use either:
    • Wildcards: % for 1 or more characters, _ for a single character
    • Comma separation: (e.g. Orgs = 215220,215230) Do not include blanks/spaces in the listing, and do not mix comma-separated lists with wildcards.
  • Activity Date, Transaction Date, Document, Rule Code, Description, Include Revenue Accounts, & Suppress Zero Rows: work as they did in the previous Transaction Detail version. (Note that Description is the only case-sensitive input field.)
    • Activity Date: date when the transaction was submitted for posting.
    • Transaction Date: input on the transaction, this determines which fiscal year & period the transaction posted to.
    • Document: Document Code for the transaction (e.g. I% for Accounts Payable Invoices), see Chapter 3 for more details.
    • Rule Code: system rule code used to post the transaction (e.g. BT4 for Temporary Budget Entry that went thru departmental approvals), see Chapter 3 for more details.
    • Description: 35-character transaction description. For Acounts Payable invoices, the description displays the vendor name.
    • Include Revenue Accounts: check to include revenue accounts (accounts starting with 5xxxxx).
    • Suppress Zero Rows: check so that you don't get $0 rows in your results.
  • Clear Criteria: this button clears all of the criteria inputs so that you may easily start with a clean slate.
  • Submit Query: click this button to run your query.
  • Note that when querying Encumbrance transactions across multiple fiscal years, you will see an ENRLxxxx doc each fiscal year in period 00 showing the amount of any open PO encumbrances carried from 1 year to the next. These entries are needed in any single fiscal year to tie back to the remaining balance for a given PO, but may need to be excluded when you are crossing multiple fiscal years.

Once Submit Query is clicked, the web page returns the query results.

  • Download button: allows you to download the output to Excel.
  • Goto Criteria button: takes you back to the Criteria input page.

  • Drill-down to specific View Document pages is available. (Note: If you do not have a WINGS Express Finance session active, the drill-down will first prompt you to log in to WINGS Express Finance.)
    • Output is sorted by Fiscal Year by Fund by Org by Account by Transaction Date, all in ascending order.

If printing, you may want to use the Print Preview functionality of your browser to fit data to page, print landscape, etc especially if you wish to include the new OrgLevel5 columns.

This query runs against the ODS (Operational Data Store) instead of against the Production database, so your results appear quickly and don't compete for resources with daily Banner operations. Note that the results include transactions in the system as of the previous business day. (You may use ViewDocument to query specific document types (e.g. Journal Vouchers) for a given accounting period which would include current day postings.)

In November 2020, a newly updated version includes these enhancements:

  • Runs faster.
  • Input parameters:
    • More selections are auto-populated/validated to help verify parameters input.
    • Ability to select a non-data-enterable org:
      • Input an asterisk & then the non-data-enterable org. Once the org you want appears in the drop-down list, double-click it to auto-populate all the Level 7 data-enterable orgs that roll up to that non-data-enterable org (E.g. *GEN55, then double click the entry in the drop-down list to auto-populate the 215xxx orgs which roll up to GEN55.)
      • (Fund/org security occurs when output is rendered, not when you are inputting parameters. So, if you select an org for which you don't have access, there will be no output for that org when you run the report.)
    • Ability to build a list of orgs by selecting each one from the drop-down, rather than inputting in a comma-separated list.
    • Double-click input boxes to clear the input value & its associated title.
    • The % wildcard is still available. (However, you can't use a combination of a list and % wildcards in a given input selection box.)
  • Outputs:
    • REVENUES NOW APPEAR AS NEGATIVE FIGURES so that totals at the bottom of the page are a sum of what's displayed in the column. This is consistent with other WSU-provided links like Balance Available Report & Cognos Reports, but is different from Query Budget.
    • Ability to sort each column by clicking the arrow at the top.
    • Search/Filter button at the right will limit the rows displayed to just those including the characters you input. This is not case sensitive. Include commas when searching for amounts.
    • Download will DOWNLOAD JUST THE ROWS DISPLAYED on the page.
      • The number of rows displayed per page may be adjusted at the top left via the drop-down menu.
  • Tips to help Transaction Detail run faster:
    • Leave the "End Fiscal Year" as "Optional" when querying just 1 year.
    • Leave the "End Fiscal Period" as "Optional" when querying just 1 period.
    • Leave the Begin & End Fiscal Periods as "Optional" when querying all periods.
    • Leave a field blank, instead of putting in a lone % wildcard.
  • Browser notes: When downloading lots of rows (e.g. > 1200), Edge & Chrome browsers may not work as smoothly (or need settings adjustments) as Internet Explorer & Firefox.
     


Payroll Expense Detail Query

  • For selected users, this query is available to retrieve Year-To-Date (or Grant-To-Date) actual detail for salary and benefit amounts posted to Finance. The by-employee detail itself is pulled from the Payroll module and is similar to output produced by the COGNOS Labor Distribution-Finance View Report. Please contact your Business Manager to discuss possible access to this query.
  • You may access the Payroll Expense Detail information by either
    • Selecting Payroll Expense Detail Query from the Query Budget drop-down list (after Budget Status by Account, Budget Status by Organizational Hierarchy, & Budget Quick Query). See screenshot showing new query in the list.
      This query will run faster if you input as many FOAPAL elements as possible (e.g. when querying a grant, it will run faster if you also include the org.)
    • Using the Query Budget Status by Account & drilling down on a specific 61xxxx or 62xxxx account code (which are now active links) or clicking the Payroll Expense Detail button which appears next to the Download buttons. See screenshot with accounts codes & new query button indicated in red. 

      New Query in List
      New Query in List


      New Links in Query Budget by Account
      New Links in Query Budget by Account
  • The detail presented from the Payroll module includes employee names & IDs, positions, hours, amounts, and earnings codes. Output (which includes additional columns like Activity Code) may be downloaded to Excel. A benefit of having this detail available via WINGS Express Finance is convenience. Please take care with this sensitive information which includes UIDs and last names.
  • View the Earn Code List for a listing of Earn Codes with their descriptions.

Sample Output
Sample Output


Budget Query Templates

  • For queries that you use often, you may save a template & retrieve it later so you don't have to re-enter operating ledger and FOAPAL Parameter information.
  • Click on the images below to view screenshots of how to Save and Retrieve a Budget Query.
  • To delete query templates you are no longer using, see Chapter 10 Managing your Templates

Save Query Templates
Save Query Templates

Use Query Template
Use Query Template


Tips and Techniques

  • Calculate adjusted Base Budget for any 190000-499999 funds budgeted through BPRA: Adjusted Budget less Temporary Budget. Click on image to the right to view a screenshot of the query computation input.
  • If you get no results returned from a query & you are expecting results
    • check your parameter input boxes to make sure there is not a space sitting in one of them. (E.g. Fund, Org, Account, Program, Activity...).
    • check the Fiscal Year & Period selected.
    • check that Chart of Accounts is a capital W.
  • For users with access to Cognos, the Comparison Fiscal Report calculates and displays Base Budget.

Compute Adjusted Base Budget
Compute Adjusted Base Budget


Previous
Next

Financial Operations

Location: 
University Hall

Controller

Financial Operations
Location: 
301 University Hall
Hours: 
Monday–Friday, 8 a.m.–5 p.m.
Fax: 
937-775-2834