Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Disbursement Report - Update BC Grants Displayed #3946

Closed
6 tasks done
CarlyCotton opened this issue Nov 15, 2024 · 0 comments
Closed
6 tasks done

Disbursement Report - Update BC Grants Displayed #3946

CarlyCotton opened this issue Nov 15, 2024 · 0 comments
Assignees
@guru-aot @CarlyCotton
Labels
Ministry Ministry Features
Milestone
Post Part-Time Pr...

Comments

@CarlyCotton
Copy link
Collaborator

CarlyCotton commented Nov 15, 2024 •
edited by guru-aot
Loading

User Story
As a ministry user, I want to be notified of any disbursements that have been confirmed via a DIS file from NSLSC.

Acceptance Criteria

  • If we receive a $0 BCSG disbursement in the receipt, please persist as a $0 grant of what we sent
  • Leave BCSG as the full receipt record from disbursement file
  • Display individual BC grant records for any disbursement receipts
    • PT Student Awards can have BCAG and/or SBSD under BCSG
    • FT Student Awards can have BCAG, BGPD and/or SBSD under BCSG
    • When we send the award to ecert it is combined and when we receive the disbursement
    • If we assessed $0 BCAG or SBSD, display a record of $0.

Additional Context
Disbursement File Processed
Disbursement Report Result:
image.png

This report
image.png

Technical

  • Filter the value_type as 'BC Grant' from sims.disbursement_values to find the BC grants needed.
  • Use the effective_value from the sims.disbursement_values.
  • Adjust any existing E2E test to ensure the BC grants will be part of the report, if present.
@CarlyCotton CarlyCotton added the Business Items under Business Consideration label Nov 15, 2024
@CarlyCotton CarlyCotton self-assigned this Nov 15, 2024
@CarlyCotton CarlyCotton added the Ministry Ministry Features label Nov 15, 2024
@CarlyCotton CarlyCotton changed the title Disbursement Report - Update records Disbursement Report - Update PT records Nov 21, 2024
@CarlyCotton CarlyCotton changed the title Disbursement Report - Update PT records Disbursement Report - Update BC Grants Displayed Nov 21, 2024
@Joshua-Lakusta Joshua-Lakusta added Dev & Architecture Development and Architecture and removed Business Items under Business Consideration labels Nov 26, 2024
@andrewsignori-aot andrewsignori-aot removed the Dev & Architecture Development and Architecture label Nov 26, 2024
@guru-aot guru-aot self-assigned this Dec 23, 2024
github-merge-queue bot pushed a commit that referenced this issue Dec 31, 2024
@guru-aot
#3946-Update BC Grants displayed (#4190)
64ea48f 
Report changed to show the values of BC Grant.

![image](https://github.com/user-attachments/assets/0c2e7f64-b878-4d21-b5a9-d50f5962b9c6)


Migration revert Screenshot


![image](https://github.com/user-attachments/assets/f22e5eba-1ae2-43f7-8193-9a12cea4ee4a)

Sample query to run and check in the DB

> (select
to_char(dr.disburse_date, 'YYYY-MM-DD') as "Date of Disbursement",
        dr.student_sin as "SIN",
        app.application_number as "Application Number",
        ds.document_number as "Certificate Number",
        drv.grant_type as "Funding Code",
        drv.grant_amount as "Disbursement Amount"
      from
        sims.disbursement_receipts dr
inner join sims.disbursement_receipt_values drv on
drv.disbursement_receipt_id = dr.id
inner join sims.disbursement_schedules ds on ds.id =
dr.disbursement_schedule_id
inner join sims.student_assessments sa on sa.id =
ds.student_assessment_id
        inner join sims.applications app on app.id = sa.application_id
inner join sims.education_programs_offerings epo on epo.id =
sa.offering_id
      where
        epo.offering_intensity = 'Full Time'
        and dr.disburse_date between '2024-01-01'
        and '2025-01-01'
      union
      all
      select
to_char(dr.disburse_date, 'YYYY-MM-DD') as "Date of Disbursement",
        dr.student_sin as "SIN",
        app.application_number as "Application Number",
        ds.document_number as "Certificate Number",
        case
          when dr.funding_type = 'BC' then 'BCSL'
          when dr.funding_type = 'FE' then 'CSL'
        end as "Funding Code",
        dr.total_disbursed_amount as "Disbursement Amount"
      from
        sims.disbursement_receipts dr
inner join sims.disbursement_schedules ds on ds.id =
dr.disbursement_schedule_id
inner join sims.student_assessments sa on sa.id =
ds.student_assessment_id
        inner join sims.applications app on app.id = sa.application_id
inner join sims.education_programs_offerings epo on epo.id =
sa.offering_id
      where
        epo.offering_intensity = 'Full Time'
        and dr.disburse_date between '2024-01-01'
        and '2025-01-01'
      union
      all
      select distinct
to_char(dr.disburse_date, 'YYYY-MM-DD') as "Date of Disbursement",
        dr.student_sin as "SIN",
        app.application_number as "Application Number",
        ds.document_number as "Certificate Number",
		dv.value_code as "Funding Code",
		dv.value_amount as "Disbursement Amount"
      from
        sims.disbursement_receipts dr
inner join sims.disbursement_schedules ds on ds.id =
dr.disbursement_schedule_id
inner join sims.disbursement_values dv on dv.disbursement_schedule_id =
ds.id
inner join sims.student_assessments sa on sa.id =
ds.student_assessment_id
        inner join sims.applications app on app.id = sa.application_id
inner join sims.education_programs_offerings epo on epo.id =
sa.offering_id
      where
        epo.offering_intensity = 'Full Time'
		and dv.value_type = 'BC Grant'
        and dr.disburse_date between '2024-01-01'
        and '2025-01-01'
    )
    order by
      "Date of Disbursement",
      "Certificate Number"

Execution plan:

![image](https://github.com/user-attachments/assets/ea7901d5-4221-4877-82d6-e68890490dd8)

---------

Co-authored-by: guru-aot <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@guru-aot guru-aot

@CarlyCotton CarlyCotton

Labels
Ministry Ministry Features
Projects
None yet
Milestone
Post Part-Time Primera Importante Mucho
Development

No branches or pull requests
7 participants
@andrewsignori-aot @guru-aot @andrepestana-aot @Joshua-Lakusta @CarlyCotton @lewischen-aot @AnnaPBashkatova