5 Schema and Index Changes

This chapter provides an overview of the schema and index changes introduced in Oracle Communications Billing and Revenue Management (BRM) 7.5 Patch Set 1 through BRM 7.5 Maintenance Patch Set 1.

Schema and Index Changes (Maintenance Patch Set 1)

BRM 7.5 Maintenance Patch Set 1 included the following schema and index changes:

  • The following table was added:

    • CONFIG_GL_CALENDAR_T

  • The following column was added in the JOURNAL_T table:

    • EFFECTIVE_T

  • The following column was added in the TMP_JOURNALS_TO_PROCESS_T table:

    • EFFECTIVE_T

  • The size of the RECORD_TYPE column in the SUSPENDED_USAGE_T table was changed to VARCHAR2(10).

  • The following indexes were modified:

    • I_ITEM_AR_BILL_OBJ__ID

    • I_ITEM_BILL_OBJ__ID

  • The following indexes were added:

    • I_ACTIVE_SESSION_T_AMT__ID

    • I_ADMIN_ACTION_T_AMT__ID

    • I_ASSOCIATED_BUS_T_AMT__ID

    • I_AU_BILLINFO_T_AMT__ID

    • I_AU_DEVICE_SERV_T_AMT__ID

    • I_AU_DEVICE_T_AMT__ID

    • I_AU_GROUP_T_AMT__ID

    • I_AU_ORDERED_BAL_T_AMT__ID

    • I_AU_PAYINFO_T_AMT__ID

    • I_BATCH_T_AMT__ID

    • I_BEST_PRICING_T_AMT__ID

    • I_BULKACCT_T_AMT__ID

    • I_COLLECTIONS_T_AMT__ID

    • I_DATA_T_AMT__ID

    • I_DEVICE_T_AMT__ID

    • I_FILTER_SET_T_AMT__ID

    • I_HISTORY_BILLS_T_AMT__ID

    • I_INVOICE_DETAIL_T_AMT__ID

    • I_INVOICE_T_AMT__ID

    • I_JOB_AMT_T_AMT__ID

    • I_JOB_BATCH_AMT_T_AMT__ID

    • I_LEDGER_REPORT_T_AMT__ID

    • I_MONITOR_QUEUE_T_AMT__ID

    • I_OFFER_PROFILE_T_AMT__ID

    • I_ORDER_T_AMT__ID

    • I_PROC_AUD_T_AMT__ID

    • I_PURCHASED_BUNDLE_T_AMT__ID

    • I_RA_THRESHOLD_T_AMT__ID

    • I_REL_EVENT_T_AMT__ID

    • I_REMIT_INFO_T_AMT__ID

    • I_RERATE_SESSION_T_AMT__ID

    • I_RESERVATION_LIST_T_AMT__ID

    • I_SMS_SETTLE_RPT_T_AMT__ID

    • I_SUSPENDED_BATCH_T_AMT__ID

    • I_TMP_UNPROCESSED_T_AMT__ID

    • I_USER_ACTIVITY_T_AMT__ID

Schema and Index Changes (Patch Set 12)

BRM 7.5 Patch Set 12 included the following schema and index changes:

  • The following columns were added in the EVENT_CUSTOMER_BILLINFO_T table:

    • BUSINESS_PROFILE_OBJ_DB

    • BUSINESS_PROFILE_OBJ_ID0

    • BUSINESS_PROFILE_OBJ_REV

    • BUSINESS_PROFILE_OBJ_TYPE

  • The following columns were added in the USER_ACTIVITY_T table:

    • API_USER_ID

    • CORRELATION_ID

    • EXTERNAL_USER_ID

  • The following columns were added in the USER_ACTIVITY_EVENT_LIST_T table:

    • EVENT_OBJ_DB

    • EVENT_OBJ_ID0

    • EVENT_OBJ_REV

    • EVENT_OBJ_TYPE

  • The following columns were removed in the USER_ACTIVITY_T table:

    • EVENT_OBJ_DB

    • EVENT_OBJ_ID0

    • EVENT_OBJ_REV

    • EVENT_OBJ_TYPE

  • The following indexes were added:

    • I_CMT_REFERENCE_T

    • I_PURCHASED_BUNDLE_T

Schema and Index Changes (Patch Set 11)

BRM 7.5 Patch Set 11 included the following schema and index changes:

  • The following column was added in the EVENT_CUSTOMER_BILLINFO_T table:

    • ACTG_TYPE

  • The following indexes were added in the NEWSFEED_T table:

    • I_NEWSFEED_ACCOUNT_OBJ__ID

    • I_NEWSFEED_BILLINFO_OBJ__ID

    • I_NEWSFEED__ID

Schema and Index Changes (Patch Set 10)

BRM 7.5 Patch Set 10 included the following schema and index changes:

  • The following table was added:

    • NEWSFEED_T

  • The following column was added in the EVENT_BILLING_CHARGE_CC_T table:

    • CARD_TYPE

  • The following column was added in the EVENT_BILLING_VALID_CC_T table:

    • CARD_TYPE

  • The following column was added in the PAYINFO_CC_T table:

    • CARD_TYPE

  • There were no index changes in this release.

Schema and Index Changes (Patch Set 9)

BRM 7.5 Patch Set 9 included the following schema and index changes:

  • The following tables were added:

    • CONFIG_CREDITOR_T

    • DEBTOR_INFO_T

    • EVENT_ACT_SEPA_MAND_AMEND_T

    • EVENT_BILLING_CHARGE_SEPA_T

    • EVENT_BILLING_PAYMENT_SEPA_T

    • EVENT_BILLING_REFUND_SEPA_T

    • EVENT_BILLING_REVERSAL_SEPA_T

    • INVOICE_BILLS_T

    • PAYINFO_SEPA_T

    • SEPA_DD_REVERSAL_T

    • SEPA_T

  • The following columns were added in the EVENT_T table:

    • LINK_OBJ_DB

    • LINK_OBJ_ID0

    • LINK_OBJ_REV

    • LINK_OBJ_TYPE

  • The following column was added in the INVOICE_T table:

    • CHECK_SPLIT_FLAGS

  • There were no index changes in this release.

Schema and Index Changes (Patch Set 8)

BRM 7.5 Patch Set 8 included the following schema and index changes:

  • The following tables were added:

    • CONFIG_NOTE_STATUS_T

    • CONFIG_NOTE_SUB_TYPES_T

    • CONFIG_NOTE_TYPES_T

    • EVENT_CUSTOMER_NOTE_DATA_T

    • EVENT_CUSTOMER_NOTE_T

    • NOTE_DATA_T

  • The following columns were added in the ACCOUNT_NAMEINFO_T table:

    • GEOCODE

    • TAXPKG_TYPE

  • The following column was added in the BILL_T table:

    • CLOSED_T

  • The following column was added in the HISTORY_BILLS_T table:

    • CLOSED_T

  • The following columns were added in the CONFIG_TAXS_T table:

    • GEOCODE

    • TAXPKG_TYPE

  • The following columns were added in the EVENT_CUSTOMER_NAMEINFO_T table:

    • GEOCODE

    • TAXPKG_TYPE

  • The following columns were added in the NOTE_T table:

    • AMOUNT

    • BILLINFO_OBJ_DB

    • BILLINFO_OBJ_ID0

    • BILLINFO_OBJ_REV

    • BILLINFO_OBJ_TYPE

    • CLOSED_T

    • COUNT

    • EFFECTIVE_T

    • HEADER_STR

    • ITEM_OBJ_DB

    • ITEM_OBJ_ID0

    • ITEM_OBJ_REV

    • ITEM_OBJ_TYPE

    • NOTE_SUB_TYPE

    • NOTE_TYPE

    • REASON_DOMAIN_ID

    • REASON_ID

    • SERVICE_OBJ_DB

    • SERVICE_OBJ_ID0

    • SERVICE_OBJ_REV

    • SERVICE_OBJ_TYPE

    • STATUS

  • The following columns were removed in the NOTE_T table:

    • CSR_ACCOUNT_OBJ_DB

    • CSR_ACCOUNT_OBJ_ID0

    • CSR_ACCOUNT_OBJ_REV

    • CSR_ACCOUNT_OBJ_TYP

    • NOTE

    • TYPE

Schema and Index Changes (Patch Set 7)

BRM 7.5 Patch Set 7 included the following schema and index changes:

  • The following columns were added in the IFW_NETWORKOPER table:

    • CAMELFLAG

    • COUNTRYCODE

    • DECIMAL_PLACES

    • NRTRDEFLAG

  • The following column was added in the EVENT_BILLING_DISC_ACTION_T table:

    • STR_VAL

  • The following column was added in the PURCHASED_DISCOUNT_T table:

    • STR_VAL

  • The permission value for the following fields in the PROC_AUD_BILL_ERR_ACCT_T table was set to 2 (optional):

    • BILL_OBJ_DB

    • BILL_OBJ_ID0

    • BILL_OBJ_REV

    • BILL_OBJ_TYPE

  • The following index was added:

    • I_BATCH_REL_INPUT_FNAME__ID in BATCH_REL_T

Schema and Index Changes (Patch Set 6)

BRM 7.5 Patch Set 6 included the following schema and index changes:

  • By default, the PASSWD column is encrypted in the SERVICE_T table.

  • By default, the DEBIT_NUM column is encrypted in the following tables:

    • AU_PAYINFO_CC_T

    • AU_PAYINFO_DD_T

    • EVENT_BILLING_CHARGE_CC_T

    • EVENT_BILLING_CHARGE_DD_T

    • EVENT_BILLING_REVERSAL_CC_T

    • EVENT_BILLING_REVERSAL_DD_T

    • EVENT_BILLING_VALID_CC_T

    • EVENT_BILLING_VALID_DD_T

    • PAYINFO_CC_T

    • PAYINFO_DD_T

  • The following columns were added in the EVENT_AUDIT_SERVICES_T table:

    • TARGET_SERVICE_OBJ_DB

    • TARGET_SERVICE_OBJ_ID0

    • TARGET_SERVICE_OBJ_REV

    • TARGET_SERVICE_OBJ_TYPE

  • The following columns were added in the EVENT_AUDIT_BALGRPS_T table:

    • TARGET_BAL_GRP_OBJ_DB

    • TARGET_BAL_GRP_OBJ_ID0

    • TARGET_BAL_GRP_OBJ_REV

    • TARGET_BAL_GRP_OBJ_TYPE

  • The following columns were added in the EVENT_AUDIT_PROFILES_T table:

    • TARGET_PROFILE_OBJ_DB

    • TARGET_PROFILE_OBJ_ID0

    • TARGET_PROFILE_OBJ_REV

    • TARGET_PROFILE_OBJ_TYPE

    • TARGET_SERVICE_OBJ_DB

    • TARGET_SERVICE_OBJ_ID0

    • TARGET_SERVICE_OBJ_REV

    • TARGET_SERVICE_OBJ_TYPE

  • The following columns were added in the EVENT_AUDIT_DEVICES_T table:

    • TARGET_SERVICE_OBJ_DB

    • TARGET_SERVICE_OBJ_ID0

    • TARGET_SERVICE_OBJ_REV

    • TARGET_SERVICE_OBJ_TYPE

  • The following columns were added in the EVENT_AUDIT_SVC_BG_TRANSFER_T table:

    • TARGET_SERVICE_OBJ_DB

    • TARGET_SERVICE_OBJ_ID0

    • TARGET_SERVICE_OBJ_REV

    • TARGET_SERVICE_OBJ_TYPE

  • The following indexes were added:

    • I_EVENT_AUDIT__ID in EVENT_AUDIT_T

    • I_EVENT_BILLING_PROD_OBJ__ID in EVENT_BILLING_PRODUCT_T

    • I_EVENT_CUST_LGN_ALS__ID in EVENT_CUSTOMER_LOGIN_ALIAS_T

    • I_EVENT_RUM_MAP__ID in EVENT_RUM_MAP_T

Schema and Index Changes (Patch Set 5)

BRM 7.5 Patch Set 5 included the following schema and index changes:

  • The following tables were added:

    • TMP_EVENTS_TO_PROCESS_T

    • TMP_JOURNALS_TO_PROCESS_T

  • The following indexes were added:

    • i_au_bal_grp_acct__id in AU_BAL_GRP_T

    • i_au_billinfo_parent__id in AU_BILLINFO_T

    • i_au_group_sharing_prof__id in AU_GROUP_SHARING_PROFILES_T

    • i_au_ordered_balgroup_poid_rev in AU_ORDERED_BALGROUP_T

    • i_au_ordered_balgroup_serv__id in AU_ORDERED_BALGROUP_T

    • i_au_profile_account_obj_id in AU_PROFILE_T

    • i_au_profile_au_parent_obj_id in AU_PROFILE_T

    • i_au_profile_poid_acc_obj__id in AU_PROFILE_T

    • i_au_profile_service_obj_id in AU_PROFILE_T

    • i_au_profile_srv_era_da__id in AU_PROFILE_SERV_EXTRATING_DA_T

    • i_au_profile_acct_era_da__id in AU_PROFILE_ACCT_EXTRATING_DA_T

    • i_au_profile_serv_era__id in AU_PROFILE_SERV_EXTRATING_T

    • i_au_service_alias_list__name in AU_SERVICE_ALIAS_LIST_T

    • i_au_uniqueness__login in AU_UNIQUENESS_T

    • i_tmp_events_to_process__id in TMP_EVENTS_TO_PROCESS_T

    • i_tmp_events_to_process__ac in TMP_EVENTS_TO_PROCESS_T

    • i_tmp_journals_to_process__id in TMP_JOURNALS_TO_PROCESS_T

    • i_tmp_journals_to_process__ac in TMP_JOURNALS_TO_PROCESS_T

    • i_unpr_events_st_end__id in TMP_UNPROCESSED_EVENTS_T

  • The following indexes were dropped:

    • i_unpr_events_start__id

    • i_unpr_events_end__id

Schema and Index Changes (Patch Set 4)

BRM 7.5 Patch Set 4 included the following schema and index changes:

  • The following table was added:

    • PROFILE_COLLECTIONS_PARAMS_T

  • The following column was added in the COLL_SCEN_PROMISETOPAY_T table:

    • AMOUNT

  • The following columns were added in the EVENT_ACT_COLL_PRMSTOPAY_T table:

    • AMOUNT

    • OVERDUE_AMOUNT

  • The size of the ipaddr column in the following tables was changed to RAW (160):

    • AU_SERVICE_IP_T

    • AU_SERVICE_VPDN_T

    • EVENT_SESSION_DIALUP_T

    • SERVICE_IP_T

    • SERVICE_VPDN_T

  • The size of the provider_ipaddr column in the EVENT_T table was changed to VARCHAR2 (160).

  • The size of the cli_ipaddr column in the following tables was changed to VARCHAR2 (80):

    • EVENT_T

    • USER_ACTIVITY_T

  • The size of the mail_server_id column in the following tables was changed to RAW(160):

    • AU_SERVICE_EMAIL_T

    • SERVICE_EMAIL_T

  • The size of the server_ip column in the EVENT_BROADBAND_T table was changed to VARCHAR2 (80).

  • The size of the client_ip column in the EVENT_BROADBAND_T table was changed to VARCHAR2 (80).

  • The following index was added:

    • I_DD_TYPES__NAME

Schema and Index Changes (Patch Set 3)

BRM 7.5 Patch Set 3 included the following schema and index changes:

  • The following tables were added:

    • BATCH_CMT_T

    • CMT_REFERENCE_T

    • DD_TYPES_T

    • OFFER_PROFILE_POLICY_LABELS_T

    • OFFER_PROFILE_T

    • OFFER_PROFILE_TIERS_T

    • TM_EVENT_ACT_CONTENT_T

    • TM_EVENT_ACTIVITY_SETTLEMENT_T

    • TM_EVENT_ACTIVITY_TLCS_T

    • TM_EVENT_ACTV_TLCS_SVC_CODES_T

    • TM_EVENT_SESS_TLCS_SVC_CODES_T

    • TM_EVENT_SESSION_TELCO_GPRS_T

    • TM_EVENT_SESSION_TLCO_GSM_T

    • TM_EVENT_SESSION_TLCS_T

  • The size of the NAME column in the EVENT_AUDIT_COLL_GDTLS_T table was changed from VARCHAR2 (60) to VARCHAR2 (255).

  • The CONSUMED_RESERVED_AMOUNT column was dropped from the AU_BAL_GRP_BALS_T table.

  • The following column was added in the CONFIG_RESERVE_T table:

    • resource_id

  • The following column was added in the EVENT_BILLING_PRODUCT_ACTION_T table:

    • price_list_name

  • The following column was added in the PURCHASED_PRODUCT_T table:

    • price_list_name

  • The following column was added in the RESERVED_BALANCES_T table:

    • CONSUMED_RESERVED_AMOUNT

  • The following column was added in the RESERVATION_BALANCES_T table:

    • CONSUMED_RESERVED_AMOUNT

  • The following column was added in the BAL_GRP_BALS_T table:

    • CONSUMED_RESERVED_AMOUNT

  • The following column was added in the DD_OBJECTS_T table:

    • VIRTUAL_FLAG

  • The following views were added:

    • COLL_ACTION_IF_VIEW

    • COLL_SCENARIO_IF_VIEW

    • VW_EVENT_ACT_CONTENT_T

    • VW_EVENT_ACTIVITY_SETTLEMENT_T

    • VW_EVENT_ACTIVITY_TLCS_T

    • VW_EVENT_ACTV_TLCS_SVC_CODES_T

    • VW_EVENT_SESS_TLCS_SVC_CODES_T

    • VW_EVENT_SESSION_TELCO_GPRS_T

    • VW_EVENT_SESSION_TLCO_GSM_T

    • VW_EVENT_SESSION_TLCS_T

  • The following indexes were added:

    • I_AU_COLL_ACTION_CREATED__ID

    • I_AU_GROUP_SHARING_PROF__ID

    • I_AU_PROFILE_SERV_ERA__ID

    • I_COLL_SCENARIO_CREATED__ID

    • I_COLL_SCENARIO_MOD__ID

    • I_INPUT_ID_N_OBJ_TYPE

    • I_POID_ID0_N_OBJ_TYPE

    • I_STAGE__ID

    • I_SYNC_ID

    • NRTRDE_FILES_PK1

    • NRTRDE_SEQUENCES_PK1

Schema and Index Changes (Patch Set 2)

BRM 7.5 Patch Set 2 included the following schema and index changes:

  • The following tables were added:

    • COLL_SCEN_PROMISETOPAY_T

    • COLLECTIONS_COLLECT_PAYMENT_T

    • COLLECTIONS_PROMISE_TO_PAY_T

    • CONF_COLL_SCEN_PROFILE_DATA_T

    • CONFIG_COLL_SCENARIO_PARAMS_T

    • EVENT_ACT_COLL_PRMSTOPAY_T

    • EVENT_ACT_COLL_REPL_SCEN_T

    • EVENT_AUDIT_COLL_GDTLS_MEMS_T

    • EVENT_AUDIT_COLL_GDTLS_T

    • EVENT_GROUP_COLL_TARGETS_MEM_T

    • GROUP_COLLECTIONS_TARGETS_T

    • IFW_TAPINFO

  • The following column was added in the CONFIG_COLLECTIONS_SCENARIO_T table:

    • scenario_severity

  • The following column was added in the COLLECTIONS_ACTION_T table:

    • target_action

  • The following column was added in the CONFIG_BEID_BALANCES_T table:

    • VALIDITY_IN_DAYS NUMBER(38)

  • The deferred initially deferred constraint was added to the IFW_SERVICE table.

  • The FK_IFW_UMM_FUOM foreign key constraint was dropped from the FROM_UOM column of the IFW_UOM_MAP table.

  • The size of the RUMGROUP column in the following tables was changed from VARCHAR2 (10) to VARCHAR2 (255):

    • IFW_RUMGROUP

    • IFW_RUMGROUP_LNK

    • IFW_SERVICE

  • The size of the SYMBOL column in the IFW_RESOURCE table was changed from VARCHAR2 (10) to VARCHAR2 (255).

  • The size of the TAXCODE column in the IFW_NOPRODUCT table was changed from VARCHAR2 (10) to VARCHAR2 (255).

  • There were no index changes in this release.

Schema and Index Changes (Patch Set 1)

BRM 7.5 Patch Set 1 included the following schema and index changes:

  • The following tables were removed:

    • CONFIG_LCM_STATES_ARRAY_T

    • CONFIG_LCM_STATES_PERM_SRVC_T

    • CONFIG_LCM_STATES_RULES_T

    • CONFIG_LCM_STATES_TRANS_T

    • CONFIG_LIFECYCLE_STATES_T

    • PREFERENCE_VALUES_T

    • SUBSCRIBER_PREFS_T

  • The following tables were added:

    • AU_PROFILE_SUBSCRIBER_PREFS_T

    • CONFIG_LIFECYCLE_PERM_SRVC_T

    • CONFIG_LIFECYCLE_RULES_T

    • CONFIG_LIFECYCLE_STATES_T

    • CONFIG_LIFECYCLE_TRANS_T

    • CONFIG_PREFERENCE_VALUES_T

    • CONFIG_SERVICE_STATE_MAP_T

    • PROFILE_SUBSCRIBER_PREFS_T

  • The following columns were added in the EVENT_CUSTOMER_STATUSES_T table:

    • LIFECYCLE_STATE NUMBER(38)

    • STATE_EXPIRATION_T NUMBER(38)

  • The size of the TAX_CODE column in the Pipeline Manager IFW_DSCBALIMPACT table was changed from VARCHAR2 (5) to VARCHAR2 (255).

  • The size of the DATA column in the Pipeline Manager IFW_DUPLICATECHECK table was changed from VARCHAR2 (255) to VARCHAR2 (4000).

  • There were no index changes in this release.