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.
| 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 |
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
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
- Select the Advance Search link.
- Set the Folder Name to contains.
- Enter Smarter and select Search.
- 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.
- 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