SIMS to SFAS Bridge File

[JasonCTang]

Story Description

As a SFAS system administrator, I want to receive data from SIMS to correlate with the data in SFAS. This requires creating a bridge file to communicate SIMS data to SFAS so that SFAS can enforce rules and limits in its own system.

Acceptance Criteria

File Specification

• [ ] Create a file to send to SFAS that aligns with the layout (see Business Context) in the file
• [ ] Each file will have the following naming structure, which will allow for multiple files per day if needed.
  • “SIMS-TO-SFAS-YYYYMMDD-HHMMSS.TXT”
    • YYYY = year of extract date
    • MM = month of extract date
    • DD = day of extract date
    • HH = hour of extract date
    • MM = minute of extract date
    • SS = second of extract date
  • Eg: “SIMS-TO-SFAS-20240205-153236.TXT”
• [ ] The destination of the file will be on the SIMS SFTP server, in the /OUT folder
• [ ] The bridge file will be a flat text file encoded in ASCII .
• [ ] The job will be run from a queue every 4 hours, schedule should be modifiable, time starting at midnight.
• [ ] Field formatting:
  • Dates: All dates will use the format yyyymmdd
  • Dollars: All dollar amounts will but 10 digits long with the last 2 digits representing cents, e.g. “0000080000” for $800.00

Records to Include

• [ ] Include student as a record on this file if one of below conditions are met since the last execution of this file:

  • Only send after student submits application AND any time a new record is inserted or an existing record is updated in any of the table that contain the fields in the bridge file.

• [ ] Application records:

  • ONLY include application records for a student if there is new or updated application information for the student.
  • Only included submitted applications.
  • If there are updates to multiple applications for a single student, include one record for each application.
  • If there are no new or updated applications for a student, do not include any application records for the student.
  • Include an application record for a student for each application that is:
    • Newly submitted.
    • An existing application that has been reassessed.
    • Has had disbursement status and/or data updated.

• [ ] Restriction Records:

  • Anytime a student is included in the bridge file, include PROVINCIAL restriction records for the student.
  • If a student has had new or updated account/application information, include restriction records for the student even if there are no new or updated restriction records.
  • If there are new or updated restrictions for a student, include a record per restriction for the student.
  • If there are no restrictions for a student, do not include any restriction records for the student.

• [ ] If any information has been created or updated for a student, regardless of the category, include an account information record for the student.

• [ ] Ensure that we can map our funding type produce the information to match what SFAS expects

Business Context

Full File Specifications and Ongoing Comments https://bcgov.sharepoint.com/:w:/r/teams/00371/Shared%20Documents/General/b)%20Development%20and%20Testing/SIMS%20Business%20Requirements/Under%20Development%20Requirements/SIMS%20to%20SFAS%20Data%20Bridge.docx?d=w588bf9022725400f998ea8cce1c27448&csf=1&web=1&e=JvpAU1

Technical

• [ ] For students, applications, and restrictions we should monitor the updated_at columns for every table participant in the query result.
• [ ] Persis the timestamp of the last time a file was generated to retrieve only the delta changes.
• [ ] Do "best effort" to map all the required data in the documentation. The ones not possible should receive blank characters for now.
  • [ ] Add comments to the document on every field that will not be mapped due to some unknown.
• [ ] The goal of the ticket is delivery as close to the final logic as possible, a file generated in the 4-hour interval, with the delta.
• [ ] Final E2E tests are not part of this effort but it would be acceptable to some sort of automation that would make it easier for the development.

[CarlyCotton]

SBSD/CSG-Disability - These two need to be highlighted for FT/PT limits being shared.

[Joshua-Lakusta]

Outstanding:

• award mapping for SIMS produced labels
• any other questions on file
• more alignment of AC's to the context for clarity

[andrewsignori-aot]

@JasonCTang the criteria to have a student added asks to only include the ones with "application submitted". The criteria for adding an application is that the assessment must be calculated. Should we consider the same for the student also, which means, include students with an application submitted and assessed?

[andrewsignori-aot]

@JasonCTang should we not include submitted applications without assessment data (no awards) to allow the application overlaps?

[JasonCTang]

@JasonCTang should we not include submitted applications without assessment data (no awards) to allow the application overlaps?

Ok, so let's include submitted applications that haven't been assessed yet.

[JasonCTang]

@JasonCTang should we not include submitted applications without assessment data (no awards) to allow the application overlaps?

We have decided to include applications that have not been assessed. (See below.) So include students with submitted applications and include applications that have been submitted (regardless of assessment status)

[andrewsignori-aot]

@CarlyCotton when the application was requested and needs a PIR, we will not have values like start and dates. Is the expectation to just send start and end dates blank?

[dheepak-aot]

@CarlyCotton while generating the restriction data records, should we should refrain from sending SINR restrictions right?

[CarlyCotton]

@andrewsignori-aot

when the application was requested and needs a PIR, we will not have values like start and dates. Is the expectation to just send start and end dates blank?

Can we use the start/end dates that the student submits? As long as a delta change from a PIR being attached would then update the dates (if necessary).

@dheepak-aot

while generating the restriction data records, should we should refrain from sending SINR restrictions right?

Can we send all provincial restrictions? They can determine which ones they need to action.

[dheepak-aot]

Can we use the start/end dates that the student submits? (Discussed with @andrewsignori-aot ) - Yes. Can you please add this context to the file documentation and also mention in AC?

Can we send all provincial restrictions? Yes we can considering SFAS will ignore them.

[guru-aot]

As discussed with @CarlyCotton, we had a question whether to send CSGP and SBSD total awards as 0, for cancelled applications, but the outcome was to send the awards as it is and SFAS can handle it.

[guru-aot]

The reason for not looking into the disbursement_schedules and the disbursement_valuesis because the change in the awards will never happen after the saveDisbursement(As the first entry to the disbursement_schedules table happens here) step mentioned below. Even if there are any updates that are happening in the workflow, those are captured by the student_assessment table and the applications table changes updated_at. This was already captured while trying to fetch the changes.

[guru-aot]

Attaching sample file from demo SIMS-TO-SFAS-20241118-175815.TXT SIMS-TO-SFAS-20241118-180321.TXT