IER 12 Implementation Part 1: Implementation of IER12 fields as per analysis

[dheepak-aot]

Create the IER12 file object as per the IER12 official document. This file object must include all the IER 12 fields that will be part of the IER 12 file including the ones which are not part of the analysis. The fields whose mapping are unknown are suppose to be filled with fillers.

Implement the mapping all the IER 12 fields available in the mapping document. No changes are required on fields which as marked as Already Implemented in the spreadsheet.

Update the getPendingAssessment query to include workflow_data, program year and student restrictions.

• Mapping document - Documents > General > Analysis > Institution File Specs > IER12_Analysis_Output_and_Mapping_Part_1_V2.xlsx
• IER12 Official document - Documents > General > Analysis > Institution File Specs > SIMS Integration File Layouts (Shared 2023_06_14) Revised (2023_07_26).docx Acceptance Criteria
• [x] Create the IER12 file object as per the IER12 official document
• [x] Implement the mapping all the IER 12 fields available in the mapping document

Discussion outcomes during implementation (updated by @dheepak-aot )

As per discussion with @JasonCTang

• Any date field which does not have a value will be filled with spaces
• Any amount field which does not have value will be filled with 0s (e.g. 0000000000)
• The field institution student number is updated as alphanumeric from numeric in the official document.
• As BCSG is bc grant total there are only 9 funding type slots instead of 10.

Question during implementation (Updated by @dheepak-aot )

• Currently we send an IER file to the institutions, when there is a new assessment or when there is any update to the disbursement(enrolment confirmation, disbursement sent, disbursement cancelled). But as part of the new implementation now we send other data like restrictions, disburse_date(when disbursement receipt received), overawards and also sin(which already exist). Should we send IER when any of these details change ? - Potentially this question must be part of next analysis ticket. - Answer from @HRAGANBC and @JasonCTang : Assessment needs to sent in a IER file only when a new assessment is created or an update happens to the disbursement or update happens to student profile(update from BCSC) or SIN.

• Looking at the current code(criteria to get an assessment to send IER12 data) an assessment is considered for generation of IER12 only when the assessment calculations are completed in workflow. i.e. when the workflow process is complete. Is that a right expectation ? I was not able to see any information regarding the same on any of the previous IER tickets #1347 and #1602 if not then we will have do a huge refactor to the existing code making many of the IER12 fields optional and setting filler values. Potentially this question must be part of next analysis ticket. Answer from @HRAGANBC and @JasonCTang : Considering an assessment right after the submission (irrespective of workflow state) for IER12 data will be nice to have not a blocker.

• The field disburse date is read from disbursement receipt table for a disbursement schedule, but a disbursement schedule there will be 2 disbursement receipts (One for FE and another BC) not just one, and hence which record of disbursement receipt we must consider for the disburse date ? any one among FE and BC record ? if so that means FE and BC will have same disburse date ? - Answer from @JasonCTang : The disburse date must come from date_sent column in sims.disbursement_schedules instead of sims.disbursement_receipts

• Should we consider only current assessment to send IER 12 data or all the assessments? Currently the existing code sends all the assessments.

• Currently in sims.applications table we have a column relationship_status from where relationship status is mapped to e-cert. should e-cert use workflow_data in sims.student_assessments table ?

IER12 Output file

IER_012_2023-09-05_08.32.57.txt

[JasonCTang]

Updated the file layout for the award configuration fields at the end of the record. Change dollar amounts from 8 digits to 10 digits (8 for dollars and 2 for cents) to be consistent with other dollar amounts in file. Instead of the repeating the field combination based on the number of awards in the current record, allocate all 10 spots everytime and each type of award will always be assigned to the same spot in the record (similar to the ECERT).

[dheepak-aot]

Question on when we send IER files to institution -

Currently we send an IER file to the institutions, when there is a new assessment or when there is any update to the disbursement(enrolment confirmation, disbursement sent, disbursement cancelled). But as part of the new implementation now we send other data like restrictions, disburse_date(when disbursement receipt received), overawards and also sin(which already exist). Should we send IER when any of these details change ? - Potentially this question must be part of next analysis ticket.