My Oracle Support Banner

During Upgrade To BRM 7.4 Duplicated Node Location Found On BRM 7.2.1 (Doc ID 1265961.1)

Last updated on OCTOBER 12, 2018

Applies to:

Oracle Communications Billing and Revenue Management - Version to [Release 7.4.0]
Information in this document applies to any platform.


Found a data migration problem. There are two or more records on the purchased_product_t (migrated from account_products_t on 7.2.1) table for the same node location, but with different status. This is caused on 7.2.1 when transferring the subscription service from Account A to Account B, and then from Account B to Account A, then from Account A to Account B or from Account B to Account C. First transfer operation leaves a record with status cancelled and the last one with active status; both with the same node_location.

The following query on 7.4 shows

SQL> select count(*), node_location from purchased_product_t where node_location is not null group by node_location having count(*)>1;


2 spes_01#865/1#20090914-113027.428136:spes_01#865/1#20090914-113027.915424#2
2 spes_01#865/1#20090914-113027.428136:spes_01#865/1#20090914-113027.428166#1
2 spes_01#865/1#20090914-113027.126865:spes_01#865/1#20090914-113027.126892#0


Now when trying to run the scripts as indicated in the documentation to migrate the data, one of the scripts (upgrade_account_product_split.sql) required to upgrade from 7.2.1 to 7.3, runs several sqls and catches only the last error status; without checking the result of each sql that runs.

Because there are 2 or more records with the same node location on the purchased_product_t, the sub-select returns more than one records and fails. However this error is not detected, so the script continued with the next sql execution.

By checking the original script to upgrade from 72 to 73 upgrade_account_product_split.sql, one may find:
1- Package ID is generated based on node location so there will be same package ID for different accounts!

2- The script has a lots of this kind upgrade

-- Now update the cross references between the main tables and the audit tables.

UPDATE au_purchased_product_t au_pp
SET effective_t = last_modified_t,
-- the assumption here is that it points to account and not the au_account
(account_obj_db, account_obj_id0, account_obj_type, account_obj_rev,
au_parent_obj_db, au_parent_obj_id0, au_parent_obj_type,
au_parent_obj_rev, package_id) =
(SELECT account_obj_db, account_obj_id0, account_obj_type,
account_obj_rev, poid_db, poid_id0, poid_type, poid_rev,
FROM purchased_product_t pp
WHERE au_pp.node_location = pp.node_location);

this will fail because the sub-query returns more than one result.

BRM node_location must be unique.

Many operations use NODE_LOCATION like a unique key so depending BRM query it doesn't matter whether it picks a product in status 1 or in status 3 the operation will fail.

This is a big problem for the future. For example in this upgrade, if one continues the upgrade without a fix (at least change the node location ) he/she will have the same problem with package ID which should not have the same value for different accounts.

Steps to reproduce (on 7.2.1):
Pricing Plan:
- Create a plan with subscription group, for example,
       Subscription Group:
            /service/ip --------------------- associate it with an OOB deal
                   |----- /service/email -------associate it with an OOB deal

All the deals are optional.

1. Create Acct1 with this plan
2. Create Acct2 with a dummy plan, find out its billinfo
3. Prepare the input flist as below and run opcode:

0 PIN_FLD_POID POID [0] /service/ip 5443529
0 PIN_FLD_FROM_OBJ POID [0] /account 5443401
0 PIN_FLD_TO_OBJ POID [0] /account 5461951
0 PIN_FLD_BILLINFO_OBJ POID [0] /billinfo 5462143
0 PIN_FLD_PROGRAM_NAME STR [0] "testnap"

It will transfer /service/email from Acct1 to Acct2.
Run the following queries

SQL> select PRODUCT_OBJ_ID0, plan_obj_id0, service_obj_id0, service_obj_type, status, node_location from account_products_t where obj_id0=<Acct1_poid>;
SQL> select PRODUCT_OBJ_ID0, plan_obj_id0, service_obj_id0, service_obj_type, status, node_location from account_products_t where obj_id0=<Acct2_poid>;

4. Prepare a similar input flist to transfer the same /service/ip from Acct2 to Acct1. Run the same queries again.
5. Using the same input flist to transfer /service/ip again from Acct1 to Acct2. Run the same queries again
6. Repeat if necessary.

You will find the same NODE_LOCATION will be in Acct1 and Acct2.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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