Run Annual Financial Aid Equation Aid Year Rollover SQL

Overview / Purpose

This procedure outlines how to execute the annual SQL routine that rolls Financial Aid equations forward to the upcoming Aid Year by copying prior year equations and related SQL objects.

The script deletes any existing equations for the new Aid Year and recreates them using the previous Aid Year as the source, updating the Aid Year code, effective date, and descriptions.

This ensures the new Aid Year begins with the most current, production-approved equation logic before annual regulatory and packaging updates are applied.

Trigger

This process is initiated once all equation updates for the current Aid Year are finalized and approved, and before any modifications are made to equations for the upcoming Aid Year.

It must be completed prior to beginning new Aid Year configuration changes but after all processing is complete for the prior aid year.

Steps

1. Confirm Readiness
   • Verify all updates to the current Aid Year equations are complete.
   • Confirm with Financial Aid leadership that rollover may proceed.
   • Ensure no users are actively modifying equations.
2. Identify Aid Year Codes
   • Determine the current (old) Aid Year code (e.g., G).
   • Determine the upcoming (new) Aid Year code (e.g., F).
3. Review Script Variables
   • Open the SQL script and verify the following declarations are correct:
   • newAidYearCode CHAR(1)
   • oldAidYearCode CHAR(1)
4. Backup Validation
   • Confirm a recent database backup exists.
   • If required by policy, coordinate with DBA before execution.
5. Connect to Correct Environment
   • Log into the approved SQL tool (e.g., SQL Developer, TOAD, etc.).
   • Confirm connection to the correct database environment (DEV, TEST, PROD).
6. Execute the Script
   • The script performs the following actions:
     • Deletes existing new Aid Year records from:
       • PS_EQUATION_TBL
       • PS_EQUATION_DTL
       • PS_EQTN_TEST_DATA
       • PS_EQUATION_PCODE
       • PS_SCC_APPL_PRMPT
       • PS_EQTN_SQL_TBL
       • PS_EQTN_SQL_CHUNKS
       • PS_EQTN_SQ_SQLAUTH
     • Inserts new Aid Year records by copying from the prior Aid Year and:
       • Replacing FQ<oldAidYearCode> with FQ<newAidYearCode>
       • Setting EFFDT = TRUNC(SYSDATE)
       • Setting compile status to 'NYET'
       • Updating descriptions from Even/Odd as applicable
   • Execute the script.
   • If your environment requires manual commit, remove comment on:
     • COMMIT;
7. Validate Record Counts
   • Compare source and target counts:
     • SELECT COUNT(*)
       FROM PS_EQUATION_TBL
       WHERE EQUATION_NAME LIKE ‘FQ<oldAidYearCode>%’;
     • SELECT COUNT(*)
       FROM PS_EQUATION_TBL
       WHERE EQUATION_NAME LIKE ‘FQ%<newAidYearCode>’;
8. Spot Check in PeopleSoft
   • Navigate to Set Up SACR > Product Related > Financial Aid > Awards > Equations
   • Open several new Aid Year equations.
   • Confirm:
     • Effective Date = today’s date
     • Description reflects correct Even/Odd label
     • Compile Status = NYET
9. Document Execution
   • Record execution date, environment, and responsible staff member.
   • Save script output and validation queries in the change ticket.

Outputs / Definition of Success

The rollover is successful when:

• All prior Aid Year equations are copied to the new Aid Year.
• Record counts between old and new Aid Year match.
• All related SQL objects and authorization records are copied.
• Effective date is set to the execution date.
• Compile status is reset to NYET.
• No SQL errors occur during execution.
• Validation is documented.

Resources

• Approved Aid Year Rollover SQL Script
• SQL Execution Tool (SQL Developer / TOAD / Equivalent)
• PeopleSoft Navigation:
  Set Up SACR > Product Related > Financial Aid > Awards > Equations
• Change Management System
• Database Backup Policy Documentation