🪴 Quartz 4.0

20250110 missing collection cases

4 min read

Missing collection cases reported by Karin

https://app.sparkmailapp.com/web-share/HcWqqDa2DbR2bF614jXAPgvuuDkiDStEOqTzxn2c

The issue seems to be related to these cases, customer’s where were not charged a collection fee in December

create table del_20250110_clients as
select cca.service_contract_id, it.record_date, it.client_number, it.acct_number
from int_transactions it
    inner join cas_client_account cca on cca.institution_number = it.institution_number
    and cca.acct_number = it.acct_number
where it.record_date = '20241204'
  and it.transaction_type = '407'
  and it.reversal_flag = '000'

This SQL should select the correct cases for correction

  • the base list is the clients with collection notes from 20241213
  • the payment status on the December statement should be 013 or 014
  • the payment status on the November statement should be 013 or 014
  • the customer must have received a collection fee in early December

Statement history records that fulfill the criteria should have their payment status increased by 2

create table del_20250110_candidates as
select csh.record_date,
       cca.client_number,
       cca.acct_number,
       csh.payment_status,
       lpad(csh.payment_status + 2, 3, '0') as payment_status_plus2,
       oi9e.days_overdue,
       oi9e.payment_status                     ifrs9_payment_status
from del_20250110_clients c
    inner join int_transactions it
        on it.institution_number = '00000051'
        and it.client_number = c.client_number
        and it.record_date between '20241201' and '20241208'
        and it.transaction_type = '407'
        and it.reversal_flag = '000'
    inner join cas_client_account cca
        on cca.institution_number = '00000051'
        and cca.client_number = c.client_number
        and cca.acct_number = it.acct_number
        and cca.account_type_id = '200'
        and cca.billing_level = '001'
    inner join cas_statement_history csh
        on csh.institution_number = cca.institution_number
        and csh.group_number = cca.group_number
        and csh.account_type_id = cca.account_type_id
        and csh.billing_level = cca.billing_level
        and csh.payment_status in ('013', '014')
        and csh.record_date = '20241211'
    inner join cas_statement_history csh2
        on csh2.institution_number = cca.institution_number
        and csh2.group_number = cca.group_number
        and csh2.account_type_id = cca.account_type_id
        and csh2.billing_level = cca.billing_level
        and csh2.payment_status in ('013', '014')
        and csh2.record_date = '20241112'
    inner join okq_ifrs9_extract oi9e
        on oi9e.acct_number = cca.acct_number and oi9e.record_date = '20250109'

Set the correct payment status in the December statement

merge into cas_statement_history csh
using (select *
       from del_20250110_candidates) x
on (csh.record_date = x.record_date and csh.acct_number = x.acct_number)
when matched then
    update
    set csh.payment_status = x.payment_status_plus2

Confirm that the issue is addressed

select omic.institution_number,
       omic.acct_number,
       omic.days_overdue,
       omic.cph_current_balance,
       omic.group_number,
       omic.account_type_id,
       cca.last_open_payment_date,
       decode(
               sign(to_number(omic.current_balance) + 350 + 180), --If bal more than theshold (pos), prepare WO, else send to Coll
               1,
               '808',
               '800')      as os_tobe, --808 Pend WO, 800 Pend coll, threshold conditions
       ccat.current_cycle_end,
       cbcd.date_cycle_end as next_cycle_end
from okq_mview_ifrs9_current omic
    inner join cas_client_account cca --Get more acct details
        on cca.institution_number = omic.institution_number
        and cca.acct_number = omic.acct_number
    left join okq_view_latest_okq8_status ovlos_p --Make sure Pending status not already set
        on ovlos_p.institution_number = cca.institution_number
        and ovlos_p.group_number = cca.group_number
        and ovlos_p.active = '001'
        and ovlos_p.okq8_status in ('800', '808')
    left join okq_view_latest_okq8_status ovlos_c --Make sure Coll Agcy (already sent or WO) status not already set
        on ovlos_c.institution_number = cca.institution_number
        and ovlos_c.group_number = cca.group_number
        and ovlos_c.active = '001'
        and ovlos_c.expiry_date >= '20250109'
        and ovlos_c.okq8_status in ('802', '810', '815', '816', '817')
    inner join okq_cbr_acct_status_action ocasa --Make sure acct status is "eligable" to send (or WO)
        on ocasa.institution_number = cca.institution_number
        and decode(ocasa.service_contract_id,
                   '999',
                   cca.service_contract_id,
                   ocasa.service_contract_id) =
            cca.service_contract_id
        and ocasa.acct_status = cca.acct_status
        and ocasa.acct_action = 'INCL_TO_COLL_AGENCY'
        and ocasa.action = '001'
    left join okq_cbr_acct_status_action ocasa2 --Make sure acct status is not on "exclusion" list
        on ocasa2.institution_number = cca.institution_number
        and ocasa2.service_contract_id in ('999', cca.service_contract_id)
        and ocasa2.acct_status = cca.acct_status
        and ocasa2.action = '000'
        and ocasa2.acct_action = 'INCL_TO_COLL_AGENCY'
    left join okq_cbr_data_ret_except ocdre --Make sure acct is not in exception list (special clients)
        on ocdre.institution_number = cca.institution_number
        and ocdre.client_number = cca.client_number
    inner join cbr_contract_acct_types ccat --Get cycle end date
        on ccat.institution_number = cca.institution_number
        and ccat.account_type_id = cca.account_type_id
        and ccat.service_contract_id = cca.service_contract_id
    inner join cbr_billing_cycle_date cbcd --Get next cycle date
        on cbcd.institution_number = ccat.institution_number
        and cbcd.billing_cycle = ccat.billing_cycle
        and cbcd.date_cycle_start = to_char(to_date(ccat.current_cycle_end, 'YYYYMMDD') + 1, 'YYYYMMDD')
where omic.institution_number = '00000051'
  and omic.account_type_id = '200'
  and omic.days_overdue >= 60
  and ovlos_p.okq8_status is null
  and ovlos_c.okq8_status is null
  and ocasa2.acct_status is null
  and ocdre.client_number is null

This now returns 82 rows

Graph View