Updates to Provincial Daily Disbursement - Import Data Changes

[dheepak-aot]

Story Description As a Ministry of Finance partner, I want to receive a daily summary log of all disbursements which were issued the previous day in SIMS, so I can keep my accounting records up to date.

Acceptance Criteria

• [x] Ensure the following data from EDU.PBC.DIS.yyyyjjj.001 file is captured in each report. (See mockups for mapping).
• FT BC loans (DISB-TOT-DISB-AMT, total Federal or BC loan disbursed amount).
• FT BC grants (DISB-TOT-GRANT-DISB, total Federal or BC grant disbursed amount).
• FT BC total (SUM of the above two fields, i.e. FT BC loans + FT BC Grants).
• PT BC Grants (DISB-TOT-GRANT-DISB - total Federal or BC grant disbursed amount).
• File Date (NEW) (Row 11 in File Layouts) (Note: this reflects the date when the NSLSC file was produced).
• Batch Run Date (NEW) This should correspond to the date the disbursement receipts were received through EDU.PBC.DIS.yyyyjjj.001.
• Sequence Number (NEW) (Row 10 in Excel) (Note: this reflects sequence number as produced by NSLSC report.
• [x] Persist the file date and sequence number to the database (to assist with troubleshooting when file creation fails).

Business Context: EDU.PBC.DISyyyyjjj.001 File Layouts

Funding Totals Mapped to PBC.EDU.DIS.yyyyjjj.001 "Receipts File":

Technical Context Currently we have a scheduler fin-process-provincial-daily-disbursements-integration which runs daily and creates Provincial Daily Disbursement report and uploads the report to SFTP location.

Save data in database, generate email as needed, and email or report can be re-generated on demand in case of issue.

Current Report looks like this:

Technical

• [x] Validate if the existing ones are the desired ones (e.g. are we already saving DISB-TOT-DISB-AMT, DISB-TOT-DISB-AMT, DISB-TOT-GRANT-DISB).
• [x] Adapt the process of importing the receipt file to save the new fields. Source reference for the existing import process: source reference.
  • [ ] Created a DB migration to insert new data
  • [x] File date (to be checked the origin - probably DISB-CREATE-DT), if not time is present ensure that a date-only filed will be created on DB and also mapped on the DB model as date-only.
  • [x] Sequence number (number - DISB-SEQ)
• [x] E2E test

[HRAGANBC]

@HRAGANBC to precisely map specific award types as displayed through Receipt file formatting into ACs to avoid miscommunication

[HRAGANBC]

@andrewsignori-aot

From EDU.PBC.DIS.yyyyjjj.001 layouts:

You're right, the trailer records already SUM certain columns from the individual records. However, the SUM values in the trailer are a SUM of PT and FT records. On the DISB001 we want to report PT and FT SUM values seperately.

So from my perspective, we can't do a 1:1 from the EDU.PBC.DIS.yyyyjjj.001 to DISB001. Can we still SUM the columns ourselves even with the proposed file generation process we landed on? I.e. to SUM the values ourselves do we need to go back to a scheduler, etc...?

[andrewsignori-aot]

• FT BC loans
• FT BC grants
• FT BC total
• PT BC Grants (NEW)
• PT BC total (NEW)
• **BC Total (FT+PT) (NEW)
• Total # of records
• File Date (NEW) (Row 11 in File Layouts) (Note: this reflects the date when the NSLSC file was produced)
• Batch Run Date (NEW) This should correspond to the date the disbursement receipts were received through EDU.PBC.DIS.yyyyjjj.001.
• Sequence Number (NEW) (Row 10 in Excel) (Note: this reflects sequence number as produced by NSLSC report

@HRAGANBC I was not referring to the trailer information. My point was that we have total grants described award per award (DISB-GR-TYPE and DISB-GR-AMT) and we also have the total for each disbursement (e.g. DISB-TOT-DISB-AMT, DISB-TOT-GRANT-DISB) and we also have total entitled, total paid for to the student and total paid for the institution.

So, can we assume that the below ones are the ones that we will SUM per offering intensity (also per record type FE vs BC)?

• FT BC loans (DISB-TOT-DISB-AMT - total Federal or BC loan disbursed amount)
• FT BC grants (DISB-TOT-GRANT-DISB - total Federal or BC grant disbursed amount )
• FT BC total (Is it the SUM of the above fields)
• PT BC Grants (same data as FT above)
• PT BC total (same from PT BC Grants since there are no loans for BC Part-time) **BC Total (FT+PT) (NEW)