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