SIMS to SFAS Bridge File - Initial Setup (Students only)

[andrewsignori-aot]

Story Description

As an 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.
This ticket's goal is to enable the boilerplate around schedules and initial file creation exporting only the students.

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
• Do not include MSFAA Number or Signed date (PT or FT) - leave fields blank for now.
• When there are NO records to send, do not send the bridge file and wait until next scheduled time to send.

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.

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

• Enable the new file integration under libs\integrations\src\sfas-integration.
  • There is already a file to read SFAS files from the SFTP (sfas-integration.processing.service.ts), create a new processing service dedicated to generating the file.
• Create a new scheduler to generate the new files.
• Query the data for students records. The data will use at least the below tables.
  • sims.users;
  • sims.students;
  • sims.sin_validations;
  • sims.cas_suppliers;
  • sims.msfaa_numbers (one record for Part-time and one for Full-time if present);
  • sims.disbursement_overawards.....
• Persist the timestamp of the last time a file was generated to retrieve only the delta changes.
  • A new table can be created.
    • Name: sims.sfas_bridge_log
      • reference_date (timestamp): date of the file generation.
      • generated_file: generated file name.
• Do "best effort" to map all the required data in the documentation. The ones not possible should receive blank characters for now. A field is considered "not possible to be mapped" when the Team is not able to find it.
  • Add comments to the document on every field that will not be mapped due to some unknown.
• 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]

@dheepak-aot Based on our conversation and the confirmation I have received from SFAS team, I have added an AC:

• Do not include MSFAA Number or Signed date (PT or FT) - leave fields blank for now.

[CarlyCotton]

@dheepak-aot Based on our conversation, I have added an AC to clarify the expectation for when we have 0 records to send when it's scheduled. The expectation is that we WONT send a file if there are no records to send. (Confirmed with HJ confirmed via email 2024-10-22 that this is fine.

[dheepak-aot]

To Support QA:

Records Validation

Header Length: 61

Student Data Record Length: 164

Footer Length: 12

---

Index position Student Data:

Record Type: 1-4

Student ID: 4-14

Student Given Name: 14-29

Student Last Name: 29-54

Student Birth Date: 54-62

Student SIN: 62-71

Student PD Status: 71-72

PD Effective Date: 72-80

Student PPD Status: 80-81

PPD Effective Date: 81-89

--- Blank Fields ----

FT MSFAA Number: 89-99

FT MSFAA Signed Date: 99-107

PT MSFAA Number: 107-117

PT MSFAA Signed Date: 117-125

----- end Blank fields ---

CAS Supplier Number: 125-132

CAS Supplier Site Number: 132-135

FT CSL Overaward: 135-145

FT BCSL Overaward: 145-155

---Blank Field-----
Grant Overaward - 155-165