Finance Sub Modules to GL Recon Queries

The following queries are provided to reconcile Finance Sub Modules to the General Ledger. Please run each query monthly to ensure each module has processed a Journal ID for GL. Inform SBCTC on missing Journal IDs and SBCTC will run a manual journal generator job. Query lists: Accounting Line/Line Exceptions, Sub System Reconciliation, and Smarter.

You must have at least one of these local college managed security roles:

  • ZD_DS_QUERY_VIEWER

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

ACCTG_LINE & ACCTG_LINE_EXCEPT Query List
Module Query Name Query Description
Accounts Receivable QFS_GL_AR_ACCTG_LINE AR Accounting Line Detail

QFS_AR_ITEM_DST_GL AR Item Distribution GL

QFS_AR_DIRECT_JOURNAL_PAYMENTS AR Direct Journal Payments

QFS_GL_AR_ACCTG_LINE_EXCEPT AR Accounting Line Exceptions
Accounts Payable QFS_AP_VCHR_ACCTG_LINE Voucher Accounting Line Data

QFS_GL_AP_ACCTG_LINE AP Accounting Line Detail

QFS_GL_AP_ACCTG_LINE_EXCEPT AP Accounting Line Exceptions
Asset Management QFS_GL_AM_ACCTG_LINE AM Accounting Line Detail

QFS_GL_AM_ACCTG_LINE_EXCEPT AM Accounting Line Exceptions
Billing QFS_GL_BI_ACCTG_LINE BI Accounting Line Detail

QFS_GL_BI_ACCTG_LINE_EXCEPT BI Accounting Line Exceptions
Cash Management/Treasury QFS_GL_TR_ACCTG_LINE TR Accounting Line Detail

QFS_GL_TR_ACCTG_LINE_EXCEPT TR Accounting Line Exceptions
Contracts QFS_GL_CA_ACCTG_LINE CA Accounting Line Detail

QFS_GL_CA_ACCTG_LINE_EXCEPT CA Accounting Line Exceptions
Grants/Projects QFS_GL_CAPC_ACCTG_LINE CAPC Accounting Line Detail

QFS_GL_CAPC_ACCTG_LINE_EXCEPT CAPC Accounting Line Exceptions
Travel & Expense QFS_EX_ACCTG_LINE EX Accounting Line Detail

QFS_GL_EX_ACCTG_LINE EX Accounting Line Detail

QFS_GL_EX_ACCTG_LINE_EXCEPT EX Accounting Line Exceptions
FSCM & HCM Payroll QFS_GL_PAY_ACCTG_LINE PAY Accounting Line Detail

QFS_GL_PAY_ACCTG_LINE_EXCEPT PAY Accounting Line Exceptions

QHC_HR_ACCTG_LN_ACCT_DT_CF_SUM HCM Accounting Line Detail (HCM Pillar)
FSCM Student Financials QFS_GL_SF_ACCTG_LINE SF Accounting Line Detail

QFS_GL_SF_ACCTG_LINE_EXCEPT SF Accounting Line Exceptions
Sub System Reconciliation

The following queries both drill into the details of Journal ID and into the detail from sub-system tables and include 1-2 key fields depending on sub-system (there are Key field mapping descriptions in the query output).  The drill feature is only available when the query is Run to HTML.  The drill reports will open in a new window, so it is important to have the pop-up blocker turned off.  Due to the number of tables and complexity of reporting this data, we recommend the queries be run from Schedule Query instead of Query Viewer.

More information and details on Sub System Source Tables and Key Field mapping can be found in metaLink when performing a search by query name in the Free Text Search box. 

Queries that show balances for all Accounts by Sub-System and Journal:

  • QFS_GL_SUB_SYS_RECON - Prompted by Accounting Period
  • QFS_GL_SUB_SYS_RECON_DAILY - Prompted by Journal Date

Queries that show only Accounts that do not balance between the sub system and the Journal:

  • QFS_GL_SUB_SYS_RECON_DIFF  - Prompted by Accounting Period
  • QFS_GL_SUB_SYS_RECON_DAILYDIFF - Prompted by Journal Date
SMARTER & DRILLDOWN SMARTER QUERY List

Queries were modeled from Legacy Smart System and adapted to PeopleSoft. Note these queries fall under the SMARTER or DRILLDOWN SMARTER Folders.

For more detailed instructions, please select the link to view the SBCTC SMARTER Query Instructions and/or watch the SMARTER Query Training Recording.

Navigation: Reporting Tools > Query > Query Viewer

  1. Select the Advance Search link.
  2. Set the Folder Name to contains.
  3. Enter Smarter and select Search.
  4. Use the Folder View drop down arrow menu option to filter by DRILLDOWN SMARTER and SMARTER results.

Please select 'Run to HTML' to produce accurate results and then export the results for further analysis.

Query Viewer image
  1. Process complete.

Video Tutorial

The video below demonstrates the process actions described in the steps listed above. There is no audio included with this video. Select the play button to start the video.

Video Tutorial Via Panopto

View the external link to SMARTER & DRILLDOWN SMARTER Queries. This link will open in a new window.

0 Comments

Add your comment

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