Institution-Specific Reports - COE Requests

[CarlyCotton]

Follow-Up to #2580

User Story

As a BC Public Institution Admin user, I want to be able to extract dedicated reports to support day to day operations and workload. These reports will help me understand the enrollment status of students at my institution and triage my work related to confirming or declining the enrollments. This report is primarily to assist institutions manage their workload around confirmation of enrolment beyond functionally completing it in SIMS.

Acceptance Criteria

• [x] Build a report titled "COE Requests".
  • Access: Make this report available only through Institution account (to only BC Public Admin user types)
  • All records shown should only relate to the institution requesting the report.
  • Can keep database values instead of translating where they provide enough information to the user. Date format: YYYY-MM-DD
  • File Layouts should be consistent with the table in the "Sample Reports" worksheet of the attached file: Sample Reports Worksheet
• [x] Required Filter Selections: Standard selections are required (Program Year and Intensity)
  • The records should be all student applications that are at enrolment or completed (and not archived), are attached to the institution and meet the PY, Intensity filters selected.
  • The records should be per COE and only consider those for the most current assessment. (2 disbursements, 2 records)
• [x] COE Actioned: This should be blank unless the COE has been confirmed or declined. It would be the date that a user confirmed or declined the COE. Use BC Time (Vancouver) to prevent confusion between the UTC date which is stored
• [x] Disb Amt: SUM of all 'estimated award; values from each disbursement (i.e. the numbers displayed on the NoA summary card). This will not include overawards and remittance, that's fine)
• [x] Disb Date: Earliest date of disbursement for each disbursement unless disbursement has been sent, then use actual disbursement date.
• [x] Default Sorting: Required status first, among required show oldest ones first.

Business Context

Sample Reports Worksheet

Technical

• [ ] Related to "Make this report available only through Institution account (to only Admin user types)", no actions are required. Institution reports are already following this.
• [ ] "COE Actioned" is represented in the sims.disbursement_schedules.coe_updated_at.
• [ ] "Disb Date" use sims.disbursement_schedules.date_sent, if null, use sims.disbursement_schedules.disbursement_date.
• [ ] Awards for the disbursements should be SUM. If subqueries are used for the SUM, please ensure the data in the sub-query result are as restricted as the main query.
• [ ] E2E follows a similar approach to other reports.

[andrewsignori-aot]

@CarlyCotton @Joshua-Lakusta @HRAGANBC When retrieving the report dates save with time, for instance, for the e-Cert sent date, we are not converting it to PDT, it is returning in its original saved UTC. It can potentially cause a date to be returned as the next day, for instance, an e-cert generated at 9 PM PDT will actually be saved to DB as 4 AM of the next day. Question, should we ensure all the similar cases would be included in the report using PDT time? In comparison, when a similar case happens in the UI along the applications, the date and time will be displayed in the current user time zone.

[CarlyCotton]

@andrewsignori-aot Is it much work to convert the UTC to PDT? I think the opportunity to have confusion between the mismatch of "days" between the report and what can be seen in SIMS is a problem.

If we're changing the time zone for this report, I would like all the other reports to display PDT for dates to be consistent. Unmet need has a most recently assessed date which could create the same issue (though with less impact since it's not $ related) #3050 / #3133

This has the potential to create the same confusion in reverse for institution users NOT in PDT. Any concerns with pushing them all PDT? @HRAGANBC @Joshua-Lakusta

[andrewsignori-aot]

@andrewsignori-aot Is it much work to convert the UTC to PDT? I think the opportunity to have confusion between the mismatch of "days" between the report and what can be seen in SIMS is a problem.

If we're changing the time zone for this report, I would like all the other reports to display PDT for dates to be consistent. Unmet need has a most recently assessed date which could create the same issue (though with less impact since it's not $ related) #3050 / #3133

This has the potential to create the same confusion in reverse for institution users NOT in PDT. Any concerns with pushing them all PDT? @HRAGANBC @Joshua-Lakusta

@CarlyCotton the amount of work is minimal. We can have one report doing it, maybe this one, and then apply the same solution to the others. It should not be an extensive work. Does it sound good?

[CarlyCotton]

@andrewsignori-aot Yes please, use the date in PDT. I'll update the excel & ACs to specify.

[andrewsignori-aot]

@andrewsignori-aot Yes please, use the date in PDT. I'll update the excel & ACs to specify.

Thanks @CarlyCotton can we have a ticket to verify existing reports and adjust them accordingly?

[CarlyCotton]

@dheepak-aot Added an AC to confirm your question regarding default sorting based on our discussion.