TOAD Queries

Accounts Receivable

Reason for Query 
This query is designed to provide Financial Aid offices a list of students that may need to have financial aid returned to the federal government. 
 
Overview
The query stale_dated_title_IV_checks.sql provides a list of uncashed checks by campus or by MAU. A check is stale dated at 180 days, however, with this query, you can request uncashed checks created on any date.
 
The parameters ask about three things:
  • the date range for checks (dd-mmm-yy)
  • the aid-year(s) for refund checks with the date range(yyyy ex:1920)
  • the MAU (A, F, S) or campus account used for the check
How it Works
The query grabs all uncashed checks for the campus or MAU within the date range. It then looks for Title IV financial aid detail codes on the student account for the aid-year(s). The student and check will be listed if there is financial aid for the aid-year(s) listed and an uncashed check.
 
Suggestion
Track the check date ranges you are running. All checks for the timeframe will be reported and you don't want to have to review the same check multiple times.
 
Sample of the parameters
query image
 
Reason for Query 
This query looks at all transactions for a given PFD year with the detail code SPFD. It is looking for students that might need a refund.
 
Overview
The query, Refund PFD - Negative Balance on SPFD.sql pulls any student with a negative balance on the transaction. If there is a negative balance, the payment has not applied against any charges.  There may be a refund required or a review of application of payment.
 
The parameters :
  • PFD Year
How it Works
The query looks at all students listed in the PFD year and then reviews any SPFD transactions. If any SPFD transaction has a negative balance - regardless of the PFD year or term - it will show on the report.
 
This mean that if you run 2021 and it lists a student that has a pfd garnishment record for 2020, then the student will show on the 2021 report and the 2020 report.   
 
TSAAREV Negative Balance Transaction
 
Reason for Query 
This query finds positive or negative transactions for the total of the student account - by term and by camps.
 
Overview
The query, V3_Student_Balance_by_Term_12-2021.sql, allows the user to select students from a campus with Negative balances, Positive balance or All balance for a selected term. The added benefit is that it reviews financial aid and payment plan information.
 
The query, V4_Student_Balance_by_Term_with_Programs_11-2023.sql includes student program information.
 
The report includes degree campus, most credits campus, term balance, net balance, holds, payment plan, VA indicator, Title IV Aid. 
 
The parameters :
  • Term_code 
  • Campus_code (not MAU)
  • Mode_Flag N = Negative, P = Positive, A = All
    • Selecting All will give you zero balance accounts.
 
 
Reason for Query
This query identifies students with a negative balance on a detail code that WILL NOT generate a refund.
 
Overview
The query, Negative_Balance_on_NonRefundable_Account, is reviewing detail codes, not accounts. You will receive one line for every detail code in your parameter set that has an issue rather than a single student ID.
 
Team members need to review and unapply/reapply accounts on this list so the credit is on the correct detail code. 

Parameters
  • Term Code - Enter a Single Term Code or leave blank for all terms
  • PAYT Code - Enter the Single Campus Letter for the Detail Code's PAYT code.
Reason for Query 
This query, /financial-systems/files/Clearing-Detail-Codes-With-Balance.sql identifies transactions moving funds between terms that have a balance. If this balance is not applied it can throw off your balance sheet.
 
Overview
The query allows the user to enter the detail codes for the charge to and credit from transactions. It identifies any student account where those codes have a balance.
 
These transactions should never carry a postive or negative balance because they are not tied to revenue or expense accounts. 
 
The parameters :
  • Detail Codes