Overview

The data that feeds from Slate into SIS needs to conform to specific standards for proper processing. This document will cover the GTP update application feed that has been configured, including the detailed file specifications, use cases, and development cycles.

Stakeholders

SPS Admissions

Special Considerations

Feed Processing

• When the feed is processed, the current date is set as the Decision Date in SIS. This cannot be overridden through the feed process. If a record is sent again on another date with a decision value, the new date will be applied.
• The feed is broken out into two queries, one for new decisions, and one for updates:
• The new decision feed pulls all released decisions that do not have the “Decision Added to SIS=Yes” value, and do have a valid SIS Application ID.
• The update feed uses fields to check if values have changed between what is in SIS and the current values in Slate.
• There is a backend sync feed that returns SIS values in all fields that are matched between the two systems. This feed is fully automated and runs daily at 7am.

Automation

This feed has been fully automated from the Slate system to the SIS mainframe import process. The following document details the configuration with the CUIT Deployment team:

https://docs.google.com/spreadsheets/d/14fSDddUl4EbKKkAhh5KUhP9gsbEjhdYeaoBrucMlhyo/edit 

Any issues with the automated processing should be directed to cuit-deploy@columbia.edu to open a ticket.

Requested Features/Issues

Current Cycle

Release 2023.1.3 [September 2023]

Features to Complete

• None

Features to Consider

• Change the logic to allow for a more comprehensive approach for determining if an application has changed in a way that requires a new GTP feed
  • Instead of checking for specific values that don't match, set a new flag that the application requires a resend
    • The flag should support a manual set to force a resend
    • The flag should be set whenever any field that is sent has a change date > than the last send date
  • Requires two new fields
    • Send Update to SIS (Yes/No)
    • Last SIS Update (Date)
  • When complete, can remove the individual filters from the feed query and just use the two new fields
• Add the application fee and status to the values that are checked for changes between the two systems and push new values (same method as for deposits)
• Requires new fields in Slate, an update to the SIS report to export the values, an update to the source format for the import into Slate

Features Ready for Release

• None

Previous Cycles

Release 2023.1.2 [September 2023]

• Add values to the CU-DEPENDENT flag field to cover new discount cases for Pre-College students
  • "P" should be passed if the applicant is designated as a partner student
  • "A" should be passed if the applicant is designated as a dependent of an alumnus
  • "Y" should remain to signify a Columbia employee dependent

Release 2023.1.1 [July 2023]

• Add the CU dependent flag to the new decision version of the GTP feed for HSP

Release 2023.1.0a [March 2023]

• Update Has New Deposit Flag for SIS filter to ensure that the latest payment is used in determining which value to send
  • Fixes issue with some cases where the waived flag was not being sent when that was the only change in the application

Release 2023.1.0 [January 2023]

• Add application and enrollment terms to the values that are checked for changes between the two systems to accommodate re-date scenarios
  • Update the SIS sync report to export the Applying_Term_Identifier and Entering_Term_Identifier values (Mohammed)
  • Update the source format for the import into Slate to map the new values
  • Create a filter to catch when either of the two values do not align across systems
    • FILTER: Check for New Term for SIS
    • Add the filter to the send criteria for the existing GTP updates feed
  • Backload records to get term values from previous terms loaded into Slate

Release 2022.1.0 [December 2022]

• Automate deferrals in SIS
  • Update the GTP query to include deferrals so these apps can be included in the sync back to SIS
  • On deferral, perform the following tasks through the GTP feed
    • Clear the Enrollment Response and Date values in SIS (NULL)
    • Update the Entering Term to the new deferred term value
  • On deferral re-confirmation, perform the following tasks through the GTP feed
    • Set the Enrollment Response to 'Y' (from deferral re-confirm status, not traditional Enrollment Response)
  • Adjust the current Enrollment Response sync logic to ignore syncing when the application is a deferral
    • This will ensure that the traditional Enrollment Response does not overwrite the special deferral case
• Add application and enrollment terms to the values that are checked for changes between the two systems to accommodate re-date scenarios
  • Created new Slate fields for SIS values: SIS Entering Term (maps to Slate Enrollment Term) and SIS Applying Term (maps to Slate Application Term)
• Send the CU Dependent flag value Y to SIS for FLAG-9 when it is Yes in Slate
  • Store in the SHAD office code only (HSP)

Release 2021.1.2d [September 2022]

• REVERTED: Update the GTP query to include deferrals so these apps can be included in the sync back to SIS

Release 2021.1.2c [July 2022]

• Update the GTP query to include deferrals so these apps can be included in the sync back to SIS

Release 2021.1.2b [June 2022]

• Change the deposit value to send to SIS to be the total amount collected instead of the last deposit payment amount
  • This covers cases where multiple deposits are transferred to a single application
  • This also covers cases where all or part of the deposit has been refunded
  • Requires update to the 'Has New Deposit Amount for SIS' filter to SUM all deposits when checking for a new deposit balance

• Replaced the ‘$$$$$$$$$$$’ value for waived deposits with SPACES to fix issues with loading

Release 2021.1.2a [March 2022]

• Added Saturday to the scheduled export to SIS to allow for another day of imports of the New Decision feed

Release 2021.1.2 [July 2021]

• Replaced the ‘0.00’ value for waived deposits with ‘$$$$$$$$$$$’ to fix issues with loading 

Release 2021.1.1 [May 2021]

• The filter to check for a new deposit looks only at the amount. In cases where the deposit is newly waived, the amount will not change, but the flag is changed
• Add the flag to the check to determine if there is a new value to send to SIS
• Has New Deposit Flag in SIS filter created and added to the GTP change feed
• Update the filter to pull any of the legacy response flags (person-scoped) and send those records through the sync process
• Update the response flag and response date fields to send the response based on which is actually in place for the record
• Currently it just sends the app-scoped response and date
• Need to check for app-scoped first, but if there is only a person-scoped value, send that one
• For the date, send the current date for app-scoped, and ‘20200901’ for any legacy backloads -- the actual reply date for old records IS NOT calculated
• Deposits are currently only Y/N and there is no check to see if a deposit has been waived
• When the deposit is waived, send a ‘W’ code to SIS
• If a ‘W’ is being sent, set the amount to ‘0.00’

Release 2021.1.0 [February 2021]

• SIS → Slate sync process updates
• Add a check to the STUD check process to look for a REG-GRP indicator (STUD_STUDENT.Registration_Group_Code)
• If present, sync the code back to an application-scoped field in Slate
• SIS Registration Group (sis_reg_group)
• Add a check for any registered courses for the APPL entering term and store them in an application-scoped field in Slate (comma delimited list of course identifiers)
• SIS Registered Courses (sis_reg_courses)
• Update the sync source format to add the new fields

Release 2020.2.4 [October 2020]

• The Slate sync process needs to be updated to include an indicator of whether the student has a valid STUD record in SIS
• Create a new field in Slate to store Y/N for the STUD presence
• Include the value in the SIS to Slate export file and add to the source format for inclusion into SIS
• Changed the Has New Response for SIS filter to look only at the new app-scoped reply code value to fix problems with incorrect values being passed in the new applications.
• Renamed the filter to Has New Response for SIS (App-Scoped)

Release 2020.2.3 [October 2020]

• Changed comparison check between Slate and SIS for decision codes to only look at the first character (‘A’,’R’, etc.) to determine if an update is needed.
• This covers cases where manual codes that are not part of Slate being entered directly in SIS
• Updated the Decision Added to SIS filter to use the same first character only comparison logic to trigger an update
• Updated logic to ensure that any values that are not to be changed contain ‘$$’ notation, particularly around Reply Code/Date and Deposit Flag/Amount

Release 2020.2.2 [September 2020]

• Has New Response for SIS
• Added check for new app-scoped response field; maintained code for person-scoped to catch any legacy responses
• When there is no updated decision response, a blank value was sent instead of the ‘$$’ value to retain the current REPLY-CODE
• This would cause the updated row to fail to import where there was NO updated decision value

Release 2020.2.1 [August 2020]

• Suppress sending records that are in the Defer bin to ensure duplicate applications are not created
• This will also eliminate the issues with competing decision values with the deferred applications containing the same SIS Application ID (from the cloning process)

Release 2020.2.0 [July 2020]

• Split the GTP feed into two separate Slate queries
• Newly released decisions only
• No changes to output
• New schedule: 2x per week (T, Th)
• Updates to the application (non-decision)
• New schedule: 3x per week (M,W,F)
• Reply code, deposit amount and flag
• Only sent if the Slate value differs from the value stored in SIS
• Do not change the deposit amount or flag if a refund has been processed in the payment activities
• New Slate fields
• deposit_added_sis, deposit_added_sis_flag, deposit_added_sis_value
• decision_added_sis_value, enrollment_added_sis_value
• Slate sync process remap
• deposit_added_sis, deposit_added_sis_flag, deposit_added_sis_value (new fields)
• enrollment_added_sis, enrollment_added_sis_date, decision_added_sis_date (already in Slate)
• Create new Slate filters to determine if there are changes that require a resend of the application data
• Has New Deposit for SIS
• Logic: If the deposit amount is different from the value synced from SIS, set the flag to YES
• Has Refunded Deposit for SIS
• Logic: If a deposit refund has been processed, set the flag to YES
• This is used to filter out changes to deposit values when a refund is part of the equation; these must be validated manually to ensure 100% accuracy
• Has New Decision for SIS
• Logic: If the latest released decision export value does not match the value synced from SIS, set the flag to YES
• Has New Response for SIS
• Logic: If the response value does not match the value synced from SIS, set the flag to YES; EXCEPT in cases where the value has been manually set to “W” or “D” -- leave those intact
• Add these three checks to the SIS feed filters; only send when one or more of these is set to YES
• Include checks within each field in the feed query to send if the particular case is set (e.g., send the manual decision field if ‘Has Updated Decision’ = YES)
• Update all feeds to send an empty file when there are no results to ensure that old files are not resent to SIS

Release 2020.1.2 [July 2020]

• Added following fields to the data being sent
• Reply Code (Y/N)
• Reply Date (automatically set to the current date)

Release 2020.1.1 [May 2020]

• Added following fields to the data being sent
• Deposit Paid Amount
• Deposit Paid Flag (Y/N)

Release 2020.1.0 [April 2020]

• Initial release to production
• Field that is sent to SIS using this feed
• Manual Decision Code