9.2 Student Financials to Finance GL Recon

Purpose:  Use this document as a reference to understand a method of reconciling Student Financial transactions batched/journalized data in ctcLink posted into the General Ledger in the Finance Pillar. This particular guide involves two separate pillars and assumes that users have access to query viewer.  In addition, the queried data will be downloaded into a spreadsheet and analyzed using pivot tables.

Audience:  Campus Solutions staff, Student Financials staff, Financial Aid staff, Finance Staff.

How to Use this Guide:

Below, we display 4 queries that use dates which correspond with a "period" in Finance.  Each of the querie's data is then analyed for Fund, Class, and Department ID using Pivot tables.   

Student Financials to Finance GL Recon

Navigation:  NavBar > Navigator > Reporting Tools > Query > Query Viewer or Schedule Query

CS Pillar - Student Financials

Query #1

  1. Use the query titled QCS_SF_E214_ACCTG_LN.

The E214 query contains student related transactional data. The results may be larger than the normal Query Viewer can handle, you may need to use Schedule Query.

Screenshot of queried data with highlighted areas
  1. Download into spreadsheet format.
Screenshot of query data in spreadsheet
  1. Create a pivot table using the data from Query #1 and isolate the Fund, Class, and Department ID you are researching.  Notice that within the account numbers, the item types are nested.
Screenshot of queried data in pivot table

Query #2

  1. Use the query titled QCS_SF_E215_ACCTG_LN.
  2. Download into spreadsheet format.

The E215 query contains departmental receipt transactional data (non-student.)

Screenshot of query data in spreadsheet
  1. Create a pivot table using the data from Query #2 and isolate the Fund, Class, and Department ID you are researching.
Screenshot of queried data in pivot table

Results from CS Query #1 and CS Query #2 will differ; however, both sets of data will be batch/journalized into the Finance Pillar.

Finance Pillar

The criteria used in the Finance Queries should match what the end user wants to verify or reconcile. Whether it’s a specific Fund or Fund-Department Combination and/or enter a specific General Ledger Account.

The following example uses Fund 149, Department 49197, Year 2020 Period 4, and GL Account 4000100 Tuition & Fee Waiver.

Query #3

  1. Use the query titled QFS_GL_ACCT_ANALYSIS - GL Ledger Table/Trial Balance Query.

This query provides a Trial Balance or a summary of the data that has been journalized from Student Financials.

query 3 criteria page
  1. Use the query titled QFS_GL_ACCT_ANALYSIS. Enter your desired or a combination of desired criteria: Fund, Department, Account and select From Fiscal Year and To Fiscal Year.
  2. Download into spreadsheet format.

The QFS_GL_ACCT_ANALYSIS Query has been developed and used to verify GL ChartStrings. For the following example, a specific Department’s ChartString, 149-509-49197, will be analyzed. When Student Financial Journals are processed in the system, those lines are batched or journalized together and the outcome summary is posted into the General Ledger.

Screenshot of query data in spreadsheet
  1. Create a pivot table using the data from Query #3 and isolate the Fund/Department ID you are researching.
Screenshot of queried data in pivot table

Query #4

  1. Use the query titled:  QFS_GL_SF_JRNL_VERIFY_BY_CF - SF system to Journal to Ledger.
  2. Use the query QFS_GL_SF_JRNL_VERIFY_BY_CF.  Enter the Fiscal Year, From Acctg Period to Acctg Period. Optional to enter additional criteria to narrow down search results, such as Fund, Class, Department, Project and/or Operating Unit.
  3. Download into spreadsheet format.
Enter criteria on query 4 page

The QFS_GL_SF_JRNL_VERIFY_BY_CF Query provides another source of verification and provides a detailed summary of the data journalized from Student Financials.  By selecting the source tables, CS_SF_ACCTG_LINE or FS_SF_ACCTG_LINE, you are able to identify the data from the CS Pillar and verify has posted to the FS Pillar.  Note: the General Ledger isolates data by the Period. Make sure your criteria for your Pivot table is correct.

FIN SF Item Types Match Spreadsheet
  1. Create a pivot table using the data from Query #4 and isolate the Fund/Department ID you are researching.
Screenshot of queried data in pivot table
Compare SF vs. Finance Pivot Tables

When reconciling the pivot tables data side by side, remember that item types are not viewable within the Finance Pillar.  Use "queried" data from Student Financial's in order to create batched balances manually through the pivot table process in order to balance against the system batched/journalized data that is available from the Finance Pillar.

  1. The below pivot tables show accounts that balance.
Image of SF and Finance pivot tables next to each other for comparison.
Can't Balance?

What happens when the data doesn't balance?

  1. Corrections should always occur from where they originate.
  2. As we are feeding data from Student Financials (Student Account transactions, Cashiering transactions, Financial Aid disbursements/refunds, etc...) into the Finance Pillar, all correction should originate from within the Student Financials Office.
  3. The item type level of detail is not found within the Finance Pillar, that level of data is batched/journalized into a single balance in order to feed (over the fence) into the Finance Pillar.  
  4. Therefore, if the Finance Pillar discovers a balancing issue, then communication should be sent to the Student Financials Office with as much detail as possible.  With that communication, those responsible in the Student Financials Office can investigate the issue and correct using the proper cashiering processes.

Accounting Dates and Periods

As financial data moves between Student Financials and the Finance Pillar, one variable to keep in mind is processing time.  Several Student Financials Office Processes are automated to run throughout the day and it is possible to have processes cross midnight. This may result in balance discrepancies between the Student Financials and the General Ledger in the Finance Pillar.  

Sometimes adding or removing a day from the Accounting Date query parameter fields may help in balancing against a particular Finance Period.

  1. End of procedure.

0 Comments

Add your comment

E-Mail me when someone replies to this comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.