Current Balance Is Wrong Due to Null SUBTYPE in /balance_group (Doc ID 2941370.1)

Last updated on MAY 08, 2023

Applies to:

Symptoms

On : 12.0.0.7.0 version, Balances

After upgrading from Billing and Revenue Management (BRM) 12 Patch Set (PS) 3 to PS7, for the accounts created in PS3 or prior PS, $0 balance is returned from PCM_OP_BAL_GET_BALANCES due to the subtype field being null in /balance_group.

Note: This is happening post upgrade for any monetary transaction happening on the account (Currency/Non-Currency). There are no references to defaulting/updating the subtype field in /balance_group to 0 (Main balance) in the upgrade script from PS3 to PS7.

The subtype field in /balance_group is null, which is causing dm_oracle not getting any data.

DM_Oracle Query to get sub-balances:
select bal_grp_sub_bals_t.rec_id, bal_grp_sub_bals_t.rec_id2, bal_grp_sub_bals_t.contributor, bal_grp_sub_bals_t.valid_to, bal_grp_sub_bals_t.valid_to_details, bal_grp_sub_bals_t.valid_from, bal_grp_sub_bals_t.valid_from_details, bal_grp_sub_bals_t.current_bal, bal_grp_sub_bals_t.granted_bal, bal_grp_sub_bals_t.next_bal, bal_grp_sub_bals_t.delayed_bal, bal_grp_sub_bals_t.rollover_data, bal_grp_sub_bals_t.grantor_obj_DB, bal_grp_sub_bals_t.grantor_obj_ID0, bal_grp_sub_bals_t.grantor_obj_TYPE, bal_grp_sub_bals_t.grantor_obj_REV, bal_grp_sub_bals_t.subtype from bal_grp_sub_bals_t where bal_grp_sub_bals_t.obj_id0 = :1 and ( (bal_grp_sub_bals_t.valid_to = :2 or bal_grp_sub_bals_t.valid_to >= :3 or bal_grp_sub_bals_t.valid_to IS NULL) and bal_grp_sub_bals_t.subtype != :4 ) order by bal_grp_sub_bals_t.rec_id


STEPS
-----------------------
1) Create an account and purchase a Plan having $5 Monthly fee on a BRM12 PS3 Environment.

2) Call PCM_OP_BAL_GET_BALANCES to retrieve account balance.
    Make sure that REALTIME_CNTR as 0 in /balance_group.
    Expected : $5 + 1(Tax)

3) Upgrade the BRM12 PS3 Environment using Out of the Box (OOB) upgrade scripts to BRM12 PS7.

4) Call PCM_OP_BAL_GET_BALANCES to retrieve account balance.
    Expected : $6
    Actual : 0

5) Post a payment for $6.
    Expected: Account Balance should be $0
    Actual : -$6
                The existing sub-balance is updated with wrong balance and valid_from.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.