Overview

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

Stakeholders

SPS Admissions

Special Considerations

Universal Data Requirements

The following are known requirements for the SIS data processing of this feed to function properly:

• Address Line 1 cannot be NULL (for any address record)
• Only USA is allowed when Citizen Flag = Y (just a warning, record will still load)
• Non-alpha characters in the first, last, middle names can cause a failed load (“.” is allowed)
• First or last name cannot be a single character; in cases where the name is actually a single character the name MUST be extended to two characters to get the application loaded and can be changed in SIS after the load if necessary
• Names of series of the same character are not allowed (e.g., ‘xxxxx’)
• This case is corrected in the feed creation logic
• SSN, if present, cannot be a series of the same number (e.g., 000000000) or the series from 1-9 or 9-1 (e.g., 123456789, 987654321)
• These cases are corrected in the feed creation logic

Program Differences

There are three variations of this feed based on the program to which the application applies. Each feed specification is identical except in the ways noted below.

ALP

• The office code is ALAD
• The ODAT screen code is ODAL
• The addresses that are sent include PM (permanent--required), AE (email), AC (current), ER (emergency)

MS/NDG

• Briefcase ID: 56e23a51-6529-cf68-2058-975766567bf4@sps (7/31/20)
• The office code is SPAD
• The ODAT screen code is ODCE
• The addresses that are sent include PM (permanent--required), AE (email), AC (current), ER (emergency)

HSP

• The office code is SHAD
• The ODAT screen code is ODCE
• The addresses that are sent include PM (permanent--required), AE (email), AC (current), ER (emergency), 1E (parent 1 email), 2E (parent 2 email)

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 file transfers between Slate and SIS should be directed to cuit-deploy@columbia.edu to open a ticket.

Requested Features/Issues

Current Cycle

Release 2024.1.0 [January 2024]

Features to Complete

• Not started

Features to Consider

• Not started

Features Ready for Release

• Not started

Previous Cycles

Release 2023.1.1 [December 2023]

• Pre-College feed ONLY
  • Pre-College Summer
    • Pool code is now stored in the hsp_session_dates prompt for HSP summer applications
    • Value stored in hs_sessions_custom_term_select / export 1 
    • Update feed to send new pool codes for this specific set of programs

Release 2023.1.1 [September 2023]

• Pre-College feed ONLY
  • Create export for collaborator/partner status - Is Verified Student Collaborator
    • Slate field = "Collaborator Student Verification Status" for partners
  • Create export for alumni dependent status - Is Verified Alumni Dependent
    • Slate field = HS CU Alum Dependent / Decision
  • Add values to the CU-DEPENDENT flag field to cover new discount cases for Pre-College students
    • Use new export to determine status
    • "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.0 [January 2023]

• Add 'Fnu' in the First Name field when the value is NULL in Slate
• Add 'Lnu' in the Last Name field when the value is NULL in Slate

Release 2022.1.1b [December 2023]

• HSP/SHAD ONLY
  • Added a filter criteria to allow apps with no fee to be passed through using the new workflow bin logic (CSS, among others)

Release 2022.1.1a [December 2022]

• HSP/SHAD ONLY
  • Added the CU Employee Dependent value to the Flag-29 position to be included in the SIS APPL screen flags

Release 2022.1.1 [November 2022]

• Update the HSP feed to change the method for determining new applications based on changes to the underlying bin movement changes (from Bins to Workflow Bins)
  • Use Application Submission Status + Payment Complete (App Fee) to determine a submitted application
  • Remove Bin values from criteria

Release 2022.1.0c [April 2022]

• Added exclusions for legacy Incomplete, Inactive, and Withdrawn round keys to keep from resending misconfigured older applications

Release 2022.1.0b [April 2022]

• Added three new bins to the HSP feed to ensure they are picked up by the send query
  • Housing waitlist X 2
  • Residential hold

Release 2022.1.0a [March 2022]

• Changed the catch up feeds to run on Mondays due to inconsistent processing on Saturdays

Release 2022.1.0 [February 2022]

• Update the feed schedule and coding to send a full feed regardless of date to catch any cases where the application was not sent initially
  • Do for all three new application feeds (ALP, MS, HSP)
  • Scheduled for Saturdays

Release 2021.1.2c [December 2021]

• Added "Application Submission Status = Submitted" to each new application export

Release 2021.1.2b [August 2021]

• The new SPCSEC program app must have a NULL value n the FLAG-27 location of the feed to successfully load; add a workaround to the field to send a ‘P’ in the feed until the program is updated in SIS to allow the normal process to work
• Update the MS feed to include the SPCSEC pre-college program since it is coded in SIS for the MS feed file based on school and office code
• Ensure that College Edge apps are sent through the MS feed, since something has caused them to begin flowing through HS

 Release 2021.1.2a [July 2021]

• Add a format validation to the SSN field to confirm it is SIS-ready
• Nine digits, no other chars

 Release 2021.1.2 [May 2021]

• Pre-college ONLY:
  Change the NK and N2 address values to 1E and 2E and include only the parent 1 and 2 email addresses instead of the mailing addresses

Release 2021.1.1 [February 2021]

• Cleaned up overall mapping of fields to eliminate filler values; one to one alignment with the master MDE specs for each feed
• Resolved issue with Application Fee Code in ALP feed
• Include the entry term in the feed to SIS now that we are collecting that data in the application; do this for all application types (ALP, MS, HS)
• There is rarely an entry term value set for new applications, as the enrollment term field is not populated until a student has committed

Release 2021.1.0 [January 2021]

• Removed ADDR-4-GOOD-FROM and ADDR-4-GOOD-UNTIL due to mapping errors on the SIS side (all feeds)

Release 2020.1.4 [November 2020]

• The RESI screen data will not be stored in SIS if the citizenship flag is set to Y but the country code is not set to USA [Required for all three MDE feeds]
• This occurs in cases where a student has dual citizenship, with the primary being outside the US
• New logic added to account for dual citizenship and to send the proper value to SIS for RESI
• Synchronize the STUD record status in SIS back to Slate
• Create a boolean field in Slate called ‘STUD Exists in SIS’ (Person-scoped)
• Add a Y/N column for STUD status to the data sync output in the SPS Operations PowerBI report
• Remap the SIS synchronization source format to include the new value

Release 2020.1.3 [October 2020]

• Due to a slight difference in the SIS WALP format, the field sizing in the APP-FEE-CODE and AMOUNT fields needed to be adjusted; this resolved an issue with the amount always being equal to zero regardless of what was being sent

Release 2020.1.2 [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.1.1 [July 10, 2020]

• NON-BLOCKING: Do not send the address flag, good from, and good until date values if there is no address to be sent
• For example, if there is no Emergency Address, should not include the “ER” code in the output file
• This will clean up the output reports for the failed address records
• This must be done for all three feeds (AL,HS,MS)
• Add requirement for a permanent address before sending to SIS for all feeds

Release 2020.1.0 [April 2020]

• Initial release to production