Create job to schedule reports - invoice reconciliation

[Jxio]

Currently, Travis just manually send reports to partners.
There is a lot going on where it's hard to reconcile and figure out where the bugs are, because partners don't have access to the invoice data.

The new job should run regularly and auto-send reports to partners.

[seeker25]

1. Data dump of invoices:

SET timezone TO 'America/Vancouver' ;
select created_on, id, (total - service_fees) as subtotal, service_fees, total, disbursement_status_code, payment_method_code, invoice_status_code from invoices where corp_type_code = 'CSO' and invoice_status_code in ('PAID', 'REFUNDED', 'CANCELLED', 'CREDITED') and payment_method_code in ('PAD','EJV', 'DRAWDOWN') order by 1;

2. Daily summary:

select count(*) as transaction_count, sum(total - service_fees) as subtotal, sum(service_fees) as service_fees, sum(total) as total, (created_on)::date, payment_method_code from invoices where corp_type_code = 'CSO' and invoice_status_code = 'PAID' and payment_method_code in ('PAD','EJV', 'DRAWDOWN') group by (created_on)::date, payment_method_code;

3. What was disbursed from our internal GL for CSO to their external GL

select count(*) as transaction_count, sum(total - service_fees) as subtotal, sum(service_fees) as service_fees, sum(total) as total, (created_on)::date, payment_method_code from invoices where corp_type_code = 'CSO' and invoice_status_code = 'PAID' and payment_method_code in ('PAD','EJV') and disbursement_status_code = 'COMPLETED' group by (created_on)::date, payment_method_code;

[pwei1018]

Can it schedule on Payment Jobs? https://github.com/bcgov/sbc-pay/blob/main/jobs/payment-jobs/cron/crontab

[pwei1018]

@stevenc987 Can you help @Jxio setup this report in Pay?

[stevenc987]

This job was migrated to Prod yesterday.