DW - All Partners Monthly reports with details of transactions

[pstemkens]

We've decided the scope of this ticket includes modernized payments only (NOT BCOL). Should state refer to mainframe for BCOL transactions. Also should potentially show a legend where the funds are being disbursed to EG. Mainframe -> Bank account -> GL for BCOL... 112 (Courts Online) -> 105 (Courts Online - Disbursement) GL for Disbursement for CSO for example.

BCROS MONTHLY REVENUE STATISTICS
FOR THE MONTH OF XXX
the report should have the name of the partner on it also

BCA + CSO + VS (Wills) should all be consistent.. right now they are differing a bit. (ESRA, RPT probably should be included as well although some of them don't have disbursements, funds will stay on client code 112 which are Citizen Services GL's)

These reports will have to be sent to Finance as well, they weren't included previously.

Existing reports:

https://github.com/bcgov/sbc-pay/blob/main/jobs/notebook-report/monthly/cso_reconciliation_summary.ipynb

Should roughly be based off of here:

https://github.com/bcgov/sbc-pay/blob/625e07f2bba84a77c8d22894a056e348d4f6ebcb/jobs/payment-jobs/tasks/ejv_partner_distribution_task.py#L57

Before Sept 2022 the reports for CSO were broken down by product in each respective fee code, in modernized we didn't have that option.

We need to change the monthly reports that are sent out for CSO to display the PAY side of things, broken down by each fee code (and product).

NOTE: ONLY REQUIRED to send left side - PENDING REQUIREMENTS FROM CSO

Example left is SBC-PAY, right is from DW (BCOL):

Original Mainframe document:

Queries payments only:

Left:

select	
	min(created_on),
	max(created_on),
	count(*),
	sum(pli.total) as sub_total,
	sum(pli.service_fees) as service_fees,
	fs.filing_type_code
from	
	invoices i
join payment_line_items pli on	
	i.id = pli.invoice_id
join fee_schedules fs on	
	fs.fee_schedule_id = pli.fee_schedule_id
where	
	i.corp_type_code = 'CSO'
	and payment_method_code = 'DRAWDOWN'
	and invoice_status_code = 'PAID'
	and (created_on - interval '8 hour') >= '2024-01-01 00:00:00'
	and (created_on - interval '8 hour') <= '2024-01-31 23:59:59'
group by	
	fs.filing_type_code;

Right:

select		
	min(bbr.date_applied),	
	max(bbr.date_applied),	
	sum(total_amt) as total_amt,	
	sum(total_amt - ts_fee) as sub_total,	
	sum(ts_fee) as ts_fee,	
	count(*),	
	fee_code	
from		
	colin.bconline_billing_record bbr	
where		
	date_applied >= '2024-01-01'	
	and date_applied <= '2024-01-31 23:59:59'	
	and fee_code in ('BCSBRVC1', 'BCSBRVC2', 'BCSBRVC3', 'CCSBRVC1')	
	and qty = 1	
	and rec_type = 'DB'	
	and not exists (	
	select	
		*
	from	
		colin.bconline_billing_record bbr2
	where	
		key = bbr.key
		and qty = -1)
group by		
	fee_code;	

#20125 has more info.

Letterhead example
https://bcgov-my.sharepoint.com/:w:/g/personal/megan_fedora_gov_bc_ca/EXmYIGnfzXxHg8MjZ1CVbiwBaePfbFUmsC_abRAdgd655g?e=51ye3j

[seeker25]

Hey team! Please add your planning poker estimate with Zenhub @Jxio @ochiu @rodrigo-barraza

[seeker25]

Can estimate based on LEFT side using the existing notebook process. If we need RIGHT side i'll convert to an epic and we'll estimate that as well

[JohnamLane]

From modern side:
1. Summary of all CSO transactions
2. Summary of revenue disbursed
3. Statement from CAS that money sent to a particular GL is 100% guaranteed to be deposited in Courts bank account. OR proof at the bank level that money is making there.
A formal statement or letter stating that xxx transactions happened, totaling xxx dollars

[seeker25]

If we're building PDF's, will need to call REPORT-API to generate the PDF

[lizhuomeng71]

This issue is verified, csv file in the data folder is generated

[pstemkens]

Ken is going to complete further testing. We determined that most of the work has in fact already been completed.