Finance Reporting using FZRCTAB (created by FZPHURS/FYPHURS)

Overview

Very early after the installation of Banner, we discovered that the reporting capabilities for General Ledger reports were not capable of meeting the needs.  At that point, Peter Kirby worked with the Finance office to create a table that would enable them to do most of their reporting calling it HURS (Harding University Reporting System).

That system created one main table for reporting that was in a cross-tab format to allow for easier side-by-side reporting and is called FZRCTAB.

Banner General Ledger System

The Banner Finance module was written in the 1980's and accounting software has changed significantly since then.  Part of that change led to the need for improved reporting.

Banner will allow multiple Charts of Accounts.  So far, Harding only uses 1, that being H

Harding's fiscal year goes from July 1 to June 30 each year and the fiscal year is known by the year it ends, so Fiscal Year 23 began July 1 2022 and will end June 30, 2023.

In Banner, the account number consists of six parts, the Fund, Organization, Account, Program, Activity, and Location, aka FOAPAL.  They are abreviated as FUND, ORGN, ACCT, PROG, ACTV, and LOCN and there are FTV tables for each of them.  In normal practice, only the Finance office uses the last two parts, so budget managers only see the first four referred to as the FOAP.

In Accounting 205, you might have learned that there is a General Ledger (which contains Assets, Liabilities, and Capital or Equity) and an Operating Ledger (which contains Revenue and Expenditures).  Banner splits those into two systems.  Most modern accounting systems combine them because there are scenarios where the separation causes trouble.  That is part of the reason for HURS.

One thing you may notice is that the General Ledger only has Funds and Accounts.  The Operating Ledger adds Organizations,, Programs, Activities, and Locations to the account number.  Because of that, the FOAP's for Assets, Liabilities, and Equity are much shorter. 

In revenue and expenditures, there are categories for uncommitted funds and all funds.  Someone in Finance will have to explain that.

There are 15 periods in each fiscal year numbered 0 - 14.  Period 0 is used for beginning balances for GL accounts.  Period 1 is July, 2 is August and period 12 is June.  Period 13 is closing entries and Period 14 will be the final balance.  The way Banner stores balances, period 14 will also always be the current balance of the account so if all you need is current, you can use period 14 instead of calculating which period you need.

Harding University Reporting System

Given that the General Ledger transactions are typically posted once daily, the FZRCTAB table is recreated each night after that posting by an Appworx job called FZPHURS.  That job runs a Banner procedure in the FYPHURS package called P_PopulateHurs.  It follows these steps:

  1. Truncate FZTFOAP
  2. Populate FZTFOAP from FZBFOAP
  3. Save FZBFOAP to FZBFOAP_SAVE
  4. Truncate FZBFOAP
  5. Insert rows into FZBFOAP for all General Ledger accounts (from FGBGENL)
  6. Insert rows into FZBFOAP for all Operating Ledger accounts (from FGBOPAL)
  7. Populate the BTYP, HTYP and other codes in FZBFOAP from the values saved to FZTFOAP in step 2. The BTYP and HTYP codes, especially, are used by Finance to breakdown reports.  The authoritative source of these is a spreadsheet kept in Finance, so we try to preserve them each night.
  8. Truncate FZRFOAP
  9. Populate FZRFOAP with one row per accounting period (0 to 14) for each fiscal year in FTVFSYR for each FOAPAL (represented in FZBFOAP)
  10. For the General Ledger accounts, populate all_ytd_activity for each account for each fiscal year, for each period.  Then copy those numbers to the unc_ytd_activity columns.
  11. Populate the all_ytd_activity, unc_ytd_activity, all_ytd_budget, and unc_ytd_budget columns for each period.  This is cumbersome because of the cross-tab format.  Each of the columns has to be separately updated for each period leading to 15 sections of code that are very similar.
  12. Update FZRFOAP multiplying all of the amounts for Revenue accounts by -1.  This will allow them to be reported correctly as revenue accounts normally have a credit balance.
  13. Delete any transactions for FY02.  They are bogus as we went live with Banner on July 1, 2002.
  14. Strip trailing spaces from the FOAPAL fields in FZBFOAP and FZRFOAP
  15. Truncate FZRCTAB
  16. Insert rows into FZRCTAB from FZRFOAP for each FOAPAL, fiscal year, and period.
  17. Populate the account number pieces, and other codes from FZBFOAP that were created in steps 5-7

Issues and Fixes

Missing HTyp or BTyp codes

This normally happens when the job is restarted before it finishes.  If that happens between step 4 where FZBFOAP is truncated and step 17 where that data is populated into FZRCTAB, the values will be missing.

Because of that, the FZRFOAP_SAVE table was created and a new set of rows is inserted in step 3 each time the FZPHURS process runs.  Step 3 also deletes records older than 30 days.

If this happens, there there are two files attached with scripts to solve the problem.  Follow the steps in the scripts which are also explained here:

  1. From FYPHURS_Fix_1:
    1. Run Query 1 which will look for the number of rows, number of BTyp codes and number of HTyp codes in FZBFOAP..  Likely the rows will be some over 35,000 but the BTyp and HTyp numbers will be zero or a lot less than that.
    2. Run Query 2 which looks at the number of rows in FZBFOAP_SAVE (see above) and whether they have BTyp and HTyp values for each of the recent executions of FYPHURS (as denoted by the distinct seq#'s and timestamps).  Find the most recent execution where the counts of both BTyp and HTyp codes are similar to the number of rows and note the sequence number.
    3. Run Query 3 which will fix the account parts and code fields in the current FZBFOAP table from the values of the chosen sequence number above.  The query will ask for the SeqToUse which should be the number you noted aboverom query 2 
    4. Re-run Query 1 to check your results.  The numbers should be very close to equal (maybe +/- 10)
  2. From FYPHURS_Fix_2:
    1. Run Query 4 which will fill the values from FZBFOAP into FZRCTAB.

That should correct the problem. 

 

Print Article