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 determines the amount an employee is eligible to recieve in tuition waivers during the summer. It calculates the amount received in the Fall/Spring. 

We will be moving to a new process in the Fall, but use this for Summer 202501.
 
There are about 260 employees, so you can run with no parameters and use Excel to manipulate the data if you would like.

Overview
The query, Employee Tuition Waiver, pulls from all employees taking classes based on the parameters presented.

The Parameters:
section_mau_A_F_S -this is the course section home
waiver_term_code - enter 202501
empl_org_UAA_UAF_UAS_SW

How It Works
If you enter a parmeter for section, you can see the employees with sections at that MAU. If you enter a parameter in empl_org, you can see the employees for a campus and the classes being taken. If you just enter the term, you will see everyone.
 
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, Refunds Loaded to TSAAREV Today, lists the refunds loaded to TSAAREV for your campus for the day. These transactions have not been fed to Finance yet for refunds.
 
Overview
The query allows users to enter their campus and receive a full list of refund transactions for the day.
 
The parameters :
  • Campus (first letter of campus detail codes)
  • Date is not a parameter, but you can adjust the date by adding a -X to the sysdate listed at the end of the query (for example sysdate-2). This will show you any refund transactions not fed from an earlier date.