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