Technical Details

Current Release Version: 2024.1.0

Release Date: 16 Jul 2024

Report Location: PowerBI / Apps / SPS Operations

Security Roles: Admissions, Finance

Requested Features

Current Cycle

Release 2024.1.1 [September 2024]

Features to Complete

In cases where the discount/scholarship that is posted does not align with the discount rate, there is a defined "Post Discount Debit" flag, but it is not exposed in the report by default
- When it is exposed, the number in the SFS feed is negative (must be positive), and is the full posted discount
- Need to do the calculation on the true amount to debit to get the discount corrected, if it is a partial debit
Allow for separate BRS codes for AY and Summer discounts and scholarships
- AY Discount = 77308 / SHTQ; AY Scholarship = 77413 / SHQ1
- Summer Discount = 61602 / SHSE; Summer Scholarship = 61367 / HSBS
For Summer residential programs, need to reduce the employee discount rate to 25%
- Create a table of rates by program code to allow for future changes to rates by program

Features to Consider

Update the discount feed logic to check for the correct amount of discount based on the tuition and fees
- Allow for adding or reducing the amount based on changes to tuition and fees with program changes
- Allow for pulling back the discount for withdrawn students
Add a page for processing REGI courses that are required for billing on-campus Pre-College Summer sessions
- Lookup current course enrollments for each student, and map to correct REGI
- Ignore cases where correct REGI has already been added
- Catch cases where enrollment has changed, and current REGI should be replaced with another

Features Ready for Release

None

Previous Cycles

Release 2024.1.0 [July 2024]

On the Send to SFS page, filter out the Discount Type (%) = A from the SFS Discount/Scholarship Feed visual.

Release 2023.1.3 [October 2023]

SQL-ONLY: Add SHTQ and SHQ1 to expected discount codes.

Release 2023.1.2 [September 2023]

Update the discount feed process to accommodate the fellowships offered for alum dependents and collaborator students
- Requires new payment and BRS codes
- 100% for collaborators, 25% for alum dependents
- Change the visualization header to Discounts/Fellowships

Release 2023.1.1a [August 2023]

SQL-ONLY: Account for cases where a discount was applied and then removed due to withdrawal

Release 2023.1.1 [August 2023]

Add conditional formatting to the PID column whenever there is more than one application for the same person in the same term for each SFS feed visual

Release 2023.1.0a [February 2023]

SQL-ONLY: Account for forfeiture cases where the student matriculated in a non-SPS program

Release 2023.1.0 [January 2023]

SQL-ONLY: Update the BRS forfeiture code to the latest value for degree programs
BUG: SQL-ONLY: Applied discounts are not being properly recognized so the action case can be cleared
BUG: When there are no rows in the SFS Deposits Feed visual, the header visual is still showing an amount
BUG: Application counter shows cases where the flag = "Unrecognized Case"; filter these out of the page
Add discounts to the relevant grids to show what has been posted per application (Summary, Details pages)
Remove BETA images

Release 2022.0.5 [December 2022]

Send to SFS Page
- Add visual for Discounts to be posted for HSP
  - Auto-calculation of 50% (total fees + tuition for the entering term)
    - Only send cases where the student has registered, with billed tuition and fees, and the total discount can be calculated
  - Pull CU Employee flag (FLAG-9) from SIS to determine population
    - Ensure that flag is set in the MDE new application feed for the SHAD office code
    - Ensure that flag is included in GTP update feed for the SHAD office code
    - Can also be manually set in SIS APPL screen
- Action Flags
  - Action
    - Post Discount Credit
    - Post Discount Debit

Release 2022.0.4 [December 2022]

Send to SFS Page
- Remove QA and Action placeholders from feed visuals
- Remove totals from feed visuals
- Add cards to show total dollars

Release 2022.0.3 [November 2022]

All pages
- Update from applying to entering term in the Slate views
Clear out the filtered terms in the SFS page in the default view
Include the Underlying Data tab in the main navigation (instead of hidden through bookmarks)
DB-ONLY: When there is more than one application in a single term, the term totals are off and the actions don't account for mismatched values by term

Release 2022.0.2 [November 2022]

Updated information link to point to development documentation
DB-ONLY: Update ignore table with full master list of forfeitures from Finance team
Change the data type for the SFS Amount to a number since that is a requirement on Barry's side for the Excel file

Release 2022.0.1 [October 2022]

Filters on all pages
- CUID, Action Flag, QA Check
Summary Page
- Add cards for top level amounts for deposits paid and posted, as well as forfeitures
Deposit Tracking Page
- TDET Transactions by term and charge code
  - Break out the deposits, forfeitures, and refunds into separate visuals
  - All other transactions should be in another table
- Deposits by Application and Term
  - Use the overview procedure to get the deposit overview information for each application
- Admissions Outcomes
  - Show Application and Enrollment Terms and deferral indicators
- Slate and SIS application details
- List of all courses from TOOK
- Link to open the Slate profile for a specific applicant
- Link to open the student profile in SSOL
- Slicers for Program and Degree Code
- Ignore List
  - Support for a lookup table that can be refreshed by the Finance team that contains all of the applications that have been fully processed and can be safely ignored in future cycles
  - Update the report with a clear indicator whether or not the ignore list is in effect
- Validation Checks
  - QA - Valid Scenarios
    - All Terms
      - No collected deposit, credit, or forfeiture required
      - Forfeiture posts have a matching payment and account credit
      - Deposit amounts match credits for matriculant
    - Per Term
      - No collected deposit, credit, or forfeiture required
      - Forfeiture posts have a matching payment and account credit
      - Deposit amount matches credit for matriculant
      - Deposit posted and pending forfeiture
  - QA - Invalid Scenarios
    - All Terms
      - Collected deposits do not match credits
    - Per Term
      - Over-credited deposit amount for entering term
      - Under-credited deposit amount for entering term
      - Forfeiture does not match the deposit amount
- Action Flags
  - No Action
    - No Deposit
    - Processed Matriculant
    - Processed Forfeiture
    - Processed Late Withdrawal (no forfeiture)
  - Action
    - Post Full/Partial Credit
    - Post Full/Partial Debit
    - Post Forfeiture
Send to SFS Page
- SFS Deposit Output
  - Create all of the required columns for an Excel feed to SFS
    - ID = CUID
    - brs_code = BRS Code (use lookup table based on the program code)
    - action = always 'ADDP' (use static measure)
    - last_name = person name, no data
    - first_name = person name, no data
    - amount = dollar amount converted to remove decimal but keep the two digits (e.g. 350.00 = 35000)
    - credit = C or D depending on charge type (C = credit / D = debit)
    - date = date deposit received in MMDDYY format (not required)
    - term = term identifier converted to three digit representation (e.g., 20222 = 622 - see spec)
  - Allow for posting credit/debit for deposits and forfeitures
SQL Procedure: a2_transaction_management
- Allow for a single procedure to pull all facets of the underlying data required by the Power BI report based on the _output parameter
  - _output = 'overview'
    - Pull the high-level aggregated data by term for every application in Slate with a matching SIS Application_ID_Number
    - Returns basic IDs and deposit paid amount (from SIS), if there is a deposit posted in TDET (based on charge code), the net deposit amount on the account (deposit charges and payments), and the net account balance overall (all charge and payments)
    - Add QA indicator status field to display potential cases for review
      - Under/Over-credited deposit amount for entering term
      - Net collected deposits not aligned with credits
    - Add Action_Flag status field to display potential actions
    - Add SFS_Term field to convert the term identifier to the required format for SFS feeds
    - Add SFS_Amount field to convert the paid deposit amount to the format required for SFS feeds
    - Add BRS codes for forfeiture, deposit, and discount based on the program code
  - _output = 'tdet'
    - Returns all relevant values from TDET for the population of PIDs
  - _output = 'course'
    - Returns all relevant values from TOOK for the population of PIDs
      - Include registered courses to confirm actual matriculation
  - _output = 'slate'
    - Returns all relevant values from APPL_SLATE for the population of PIDs
      - Full submitted application history, including multiple programs
  - _output = 'sis'
    - Returns all relevant values from APPL_APPLICANT_INFORMATION for the population of PIDs
      - Full submitted application history, including multiple programs (that have a matching app in Slate)
  - _output = 'asr'
    - Returns all relevant values from a1_admissions_summary view for the population of PIDs
      - List of relevant fields to include, rather than everything
  - _output = 'ignore_list'
    - Returns a list of PIDs and APPIDs that have been processed and can be safely excluded in the report views

Browser not supported