SIMS Create new 'Application Changes' Report to be sent to Finastra

Description of Task As an NSLSC partner, I need to be aware of relevant changes to student applications so that I can accurately adjust a student's interest-free status and adjust validations on study date overlap within the NSLSC system (among other things).

Acceptance Criteria:

[x] Create .csv file with file name TPBC.EDU.APPCHANGES.YYYY-MM-DD_HH.MM.SS.csv (where 'T' is environment identifier)
[x] Create a scheduler to produce this file every Monday at 8:00am and deposit it on MSFT folder
[x] Include student records on the file only if ALL the following three criteria are true:
- Student application already has successfully issued at least one eCert record to NSLSC, and;
- The student's study dates (start OR end) have changed for ANY reason (this includes Program/Offering change is approved, Early Completion reported, Withdrawal reported, School Transfer Reported, Bulk Offering change approved); and
- The study date change has not yet been reported on any previous version of this file
[x] If student application dates changes twice since last report was generated, only include the latest date change in the next report
[x] Create file in alignment with the file specifications listed below. Report should include the Column name, but not the example or logic/variable name
[x] In the case of no change records, still send the file but with no records. Leave the headers and the field about the total records (0 in this case).
[x] All date/times in UTC.

File Specifications

Column name (to be included in report)	Example (for context only)	Logic or Variable Name (for context only)
Student SIN	123456789
Student First Name	David
Student Last Name	Brooks
Application Number	2023001260
Loan Type	FT	Study intensity of application offering (PT or FT)
Education Institution Code	AUBC	Use institution location code
Original Study Start Date	2024-02-03 (iso format )	Study start date of application the last time this application was included on a prior report
Original Study End Date	2024-07-23	Study End Date of application the last time this application was included on a prior report
Activity	Early Withdrawal	Possible values are: "Early Withdrawal" iff student was reported as "Student withdrew from program", "Reassessment" iff study start or end date changed for any other reason, including early completion, program/offering change, or bulk offering change
Activity Time	2024-05-04T16:54:59 (please use this exact format for date and time)	YYYY-MM-DDTHH.MM.SS - The date & time the activity occurred, i.e the time the new assessment record was created
New Study Start Date	2024-02-03 (iso format fine)	Study start date of application after activity was processed.
New Study End Date	2024-05-04 (iso format fine)	Study end date of application after activity was processed.
Number of Records	Alphanumeric (#of#)	1of3

Biz Context

Federal policy dictates that students can only receive funding once for any given time period. If the Feds receive a disbursement request for study dates which are already associated with that student. they will reject the request. This means that we need a new file which is sent to the Feds capturing anytime a student's study dates change, so that the Feds can always process subsequent eCert files using the most up to date student status info.
File specifications and analysis found here Type B Analysis.xlsx

Technical

Background: This is a new file which needs to be generated from SIMS data and sent as a CSV file to the SFTP location. We have many files which are created and sent to SFTP.

New Integration(ESDC): Create the CSV file.

[ ] Create a CSV file, with a specified file name and file format.
[ ] The CSV generation will be entirely based on the two new columns introduced by #3282.
1. The application should be included in the report when its current assessment has a previous_date_changed_reported_assessment_id and reported_date is null.
2. Once the report is generated the reported_date of all the applications in the report must be updated with the same date.
[ ] Use typeorm query builder to retrieve all the records using aliases in the column as expected by the CSV allowing papaparse to convert the records directly into the CSV.
[ ] If the above does not fits the solution we can have the SQL result mapped to an object model.
[ ] Ensure that only the records present in the query result will have its reported_date updated.
[ ] Generate the report even when there are no records to allow the federal government that there are no changes to be reported (as per @HRAGANBC comment in this ticket).
[ ] As per #3207 The total records number can be appended to the end of the file without harm to the CSV format. It will be considered as a value in the first column.

Creating scheduler

[ ] Create DB migrations to add the new queue-configurations.
[ ] Add migration(queue_config) enum value to a new scheduler possibly called application-changes-report-integration.
[ ] Ensure this scheduler comes under esdc-integration.
[ ] Ensure the scheduler follows the process summary framework to log as per recent implementations Source code

E2E Tests:

[ ] Create E2E test for an Application Changes report generated on offering date changes.
[ ] Create E2E test for an Application Changes report generated on offering date changes after previous report generation.
[ ] Create E2E test for NO Application Changes report generated.

PR breakdown suggestion

[ ] Queue db-migrations and schedulers @Process() method preparations (logs and all but no calling any process service).
[ ] CSV generation and E2Es or (some E2Es).

Basic Scheduler @Process method sample.

  @Process()
  async processFullTimeResponses(job: Job<void>): Promise<string[]> {
    const processSummary = new ProcessSummary();
    processSummary.info(
      "Processing e-Cert full-time feedback integration job.",
    );
    try {
      // TODO: Add method to process it.
      processSummary.info("Completed E-Cert Full-time integration job.");
      return getSuccessMessageWithAttentionCheck(
        ["Process finalized with success."],
        processSummary,
      );
    } catch (error: unknown) {
      const errorMessage =
        "Unexpected error while executing the e-Cert full-time feedback integration job.";
      processSummary.error(errorMessage, error);
      return [errorMessage];
    } finally {
      this.logger.logProcessSummary(processSummary);
      await logProcessSummaryToJobLogger(processSummary, job);
      await this.cleanSchedulerQueueHistory();
    }
  }

Nov 10 '23 04:11 HRAGANBC

@CarlyCotton Ready for lather?

In meeting with Finastra they brought up "Withdrawal Reversal Flag", but SIMS doesn't currently have that functionality so I think better to ammend the file in future once it's relevant.

Also, if memory serves me they agreed that they calculate all interest free dates for the student, so don't need us sending any such calculations on their behalf, thereby eliminating the interest-free report altogether from SIMS.

Apr 12 '24 21:04 HRAGANBC

@HRAGANBC

Student application start or end date changed for any reason since the last time the Date Range End Date of the last file produced (request an offering change, scholastic standing, etc...)

Based on our discussion this morning I think we'll need to clarify this section a little more. Does this criteria capture all withdrawals and date changes? We may need to further clarify which records to report. Send records for applications that have sent an ecert AND:

program/offering changed? (anything through report a change); OR,
early completion; OR,
transfer reported; OR,
withdrawal.

Apr 15 '24 17:04 CarlyCotton

@HRAGANBC @CarlyCotton The student's data used for the e-Cert generation is based on the most updated data from the Student profile which means that it can change between reassessments. Is it correct to assume that first name, last name, and SIN must always come from the most updated data (same as the e-cert) and this is the desired data to be sent in this report?

May 09 '24 01:05 andrewsignori-aot

@andrewsignori-aot My apologies for not getting this revision in before you started on the ticket. We got some additional clarification, which is contrary to what the ticket said. Please see the changes below, and the new proposed requirements in bold

I suspect this will have spillover into #3207 as the first_reported_assessment_id is no longer the relevant variable.

Sorry this was my bad for not changing the ticket sooner. I can adjust the actual ACs in the ticket accordingly if you're OK with it.

Column name (to be included in report)	Example (for context only)	Logic or Variable Name (for context only)
Original Study Start Date	2024-02-03 (iso format )	Study start date of application ~when first ever disbursement was sent for that application~ the last time this application was included on a prior report
Original Study End Date	2024-07-23	Study End Date of application ~when first ever disbursement was sent for that application~ the last time this application was included on a prior report
Activity	Early Withdrawal	Possible values are: "Early Withdrawal" iff student was reported as "Student withdrew from program", ~"Other Date Change"~ "Reassessment" iff study start or end date changed for any other reason, including early completion, program/offering change, or bulk offering change
Activity Time	2024-05-04T16:54:59 (please use this exact format for date and time)	YYYY-MM-DDTHH.MM.SS - The date & time the activity occurred, i.e the time the new assessment record was created
New Study Start Date	2024-02-03 (iso format fine)	Study start date of application after activity was processed.
New Study End Date	2024-05-04 (iso format fine)	Study end date of application after activity was processed.

May 09 '24 14:05 HRAGANBC

@andrewsignori-aot

The student's data used for the e-Cert generation is based on the most updated data from the Student profile which means that it can change between reassessments. Is it correct to assume that first name, last name, and SIN must always come from the most updated data (same as the e-cert) and this is the desired data to be sent in this report?

Yes, you're right you can include the most recent personal info for the student, even if it has changed since the last time they were included on a report.

May 09 '24 14:05 HRAGANBC

@andrewsignori-aot My apologies for not getting this revision in before you started on the ticket. We got some additional clarification, which is contrary to what the ticket said. Please see the changes below, and the new proposed requirements in bold

I suspect this will have spillover into #3207 as the first_reported_assessment_id is no longer the relevant variable.

Sorry this was my bad for not changing the ticket sooner. I can adjust the actual ACs in the ticket accordingly if you're OK with it.

Column name (to be included in report) Example (for context only) Logic or Variable Name (for context only) Original Study Start Date 2024-02-03 (iso format ) Study start date of application ~when first ever disbursement was sent for that application~ the last time this application was included on a prior report Original Study End Date 2024-07-23 Study End Date of application ~when first ever disbursement was sent for that application~ the last time this application was included on a prior report Activity Early Withdrawal Possible values are: "Early Withdrawal" iff student was reported as "Student withdrew from program", ~"Other Date Change"~ "Reassessment" iff study start or end date changed for any other reason, including early completion, program/offering change, or bulk offering change Activity Time 2024-05-04T16:54:59 (please use this exact format for date and time) YYYY-MM-DDTHH.MM.SS - The date & time the activity occurred, i.e the time the new assessment record was created New Study Start Date 2024-02-03 (iso format fine) Study start date of application after activity was processed. New Study End Date 2024-05-04 (iso format fine) Study end date of application after activity was processed.

Hi @HRAGANBC, please adjust the ACs as needed as per the latest information received. I will adjust the analysis.

May 10 '24 23:05 andrewsignori-aot

@HRAGANBC about the below AC:

After the first e-Cert is generated, a reassessment changing the date should be included in the report only if it already had its e-Cert generated? Should the change be included in the report even before the e-Cert for that disbursement was generated?

May 10 '24 23:05 andrewsignori-aot

After the first e-Cert is generated, a reassessment changing the date should be included in the report only if it already had its e-Cert generated? Should the change be included in the report even before the e-Cert for that disbursement was generated?

If I understand correctly, you're wondering whether we can report the new assessment dates before the new assessment disbursement has been sent. Correct, you should be including those changes on the report. We want the Feds to be advised about date changes prior to them getting the new eCert request with the new dates

@andrewsignori-aot

May 13 '24 16:05 HRAGANBC

@HRAGANBC just to double-check, if no changes need to be reported, what should we do? Option 1: generate an empty report to let Federal Government be aware of it. Option 2: do not generate the report.

May 15 '24 17:05 andrewsignori-aot

@andrewsignori-aot Please create blank file.

It helps them know troubleshoot if they still continue to receive a report on the same weekly schedule regardless of its contents

May 15 '24 20:05 HRAGANBC

@dheepak-aot Based on our conversation I've updated the AC's:

Hugh's comment from May 15 "Please create blank file." Please create a file with NO records if there are none. Keep headers and note that there are 0 records per the layout.

Aug 20 '24 20:08 CarlyCotton

@dheepak-aot and I had a discussion and updated two ACs.

First AC we moved to an underscore between date/time in the file name. (Confirmed OK with Finastra) Added an AC to clarify all date&time (file name and activity time field) will be UTC.

Aug 21 '24 23:08 CarlyCotton