Copy of Minor updates to Provincial Daily Disbursement report sent to Finance

[andrewsignori-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

• [ ] Save the file as .csv as opposed to .txt
• [ ] Instead of writing the file to the SFTP location, email the file (as attachment) to ministry of finance. Should be sent to the following emails: FINRSBSL@gov.bc.ca; Kina.He@gov.bc.ca; Joaquin.LozanoGonzalez@gov.bc.ca (in TEST, just use SABCTechHelp@gov.bc.ca)
• [ ] Create a GC Notify template for the email, which can accommodate an attachment within the email
• [ ] Read-in only the records from the file that reflect provincial funding, i.e. IFF DISB-CERTNO starts with "BC" (for FT) or "BP" (for PT)
• [ ] 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)
• PT BC total (same as above field, i.e. PT BC Grants, because there are no BC loans)
• BC Total (FT+PT) (NEW) (SUM of FT BC Total + PT BC total)
• 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
• [ ] persist the file date and sequence number to database (to assist with troubleshooting when file creation fails)
• [ ] Create and send the report through email after each EDU.PBC.DIS.yyyyjjj.001 is read-into SIMS.

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 Technical

• [ ] Prepare the manual SQL script to update email contacts on PROD.
• [ ] Create the new GC Notify template. The same attachment process can be in email as attachment existing code - https://github.com/bcgov/SIMS/blob/b70bf8a6fbbbab11a80c0c396f9d5eb5439e5b11/sources/packages/backend/libs/services/src/notifications/notification/notification-actions.service.ts#L575
  • [ ] Create the DB migration for the new GC notify.
• [ ] Adjust the report config Daily_Disbursement_File
  • [ ] Consider only the BC funding (BC or BP) Andrew??? Check the origin from FE/BC
• [ ] Generate the CSV from the scheduler check the name after every file is imported.
• [ ] Remove current scheduler for the daily report
  • [ ] Create a migration to remove the queue-configuration.
• [ ] Adapt the process of importing the receipt file to save the new fields.
  • [ ] Created a DB migration to insert new data
  • [ ] File date (to be checked the origin - probably DISB-CREATE-DT)
  • [ ] Sequence number (number - DISB-SEQ)
• [ ] The input parameter for CSV generation will be the unique combination between "File date" + "Sequence number"

Suggested breaking parts

• Import the file including all necessary data for the report (disbursement receipt)
  • Check BC/BP
• New Disbursement receipt CSV generation and send the report by email.

[HRAGANBC]

@andrewsignori-aot Not clear to me if this is needed? Is this seperate in any way from #3178 and #2869?

[michesmith]

@dheepak-aot whats this- lets talk

[andrewsignori-aot]

@HRAGANBC @michesmith This ticket was created by mistake while duplicating #2869 to break it for implementation as mentioned by @HRAGANBC here https://github.com/bcgov/SIMS/issues/3177#issuecomment-2086499427

It can be closed.