Mapping Legacy Data to the BRM Data Schema

3 Mapping Legacy Data to the BRM Data Schema

This chapter explains, in general terms, what you need to understand about your legacy data to successfully convert it to an Oracle Communications Billing and Revenue Management (BRM) database.

About Creating XML Files

To migrate data, you create XML files that contain the data. You then migrate the data to the BRM database by using the pin_cmt utility.

Note:

Ensure that there are no extra spaces in the input XML file. If you need to indent text in the XML file, use the TAB key to add space.

Sample XML files for each type of data are available in BRM_home/apps/cmt/sample_data. BRM_home is the directory in which the BRM server software is installed.

About the XSD Files

Conversion Manager includes two sets of XSD files:

A set of conceptual files that you can use for mapping legacy data to BRM objects. These files are easier to read than the set of files used for converting data.
A set of physical files that are used by Conversion Manager when converting data. These files are not as easy to read, but are written in a way that optimizes performance.

The difference between the types of files is how the XML tags are named. In the conceptual files, the XML tags use the same field names as the BRM object fields.

For example:

In the conceptual files:

PIN_FLD_ACCOUNT_NO = <FldAccountNo>
In the physical files:

PIN_FLD_ACCOUNT_NO = <ActNo>

To set up mapping, you can use the easily-readable conceptual files to determine how to map data. Then you can edit the physical files, after you know how the data is mapped.

The conceptual files are in BRM_home/apps/cmt/schema_files/conceptual.

The physical files are in BRM_home/apps/cmt/schema_files/physical.

Use the following physical XSD files to create your XML files:

Use the CMT_Subscriber.xsd file for subscriber data.
Use the CMT_Balances.xsd file for balance data.
Note:
- Before you use the pin_cmt utility to migrate data, validate the XML files with the physical XSD files.
- Do not use spaces while generating the XML files. Use tabs (\t) and new lines (\n).

About the Types of Data to Convert

Table 3-1 shows the types of legacy data that can be loaded into the BRM database:

Table 3-1 Legacy Data Storable in BRM Database

Type of Data	Description
Account data	Subscriber data such as name, address, profile, current account balances, charge sharing group, and account hierarchy.
Charge offers, discount offers, and bundles	Bundles purchased by the customer and associated with the account.
Bill	Bill information such as billing cycle and balances from the legacy billing system.
Services	Data pertaining to services owned by the account, such as a wireless data service. For example, when converting data for a wireless data service, the following data is also converted: Device SIM Device number

Tables Affected by the Conversion Process

Table 3-2 shows the BRM tables that are affected by the conversion process. For more information on the BRM tables and the fields in each table, see the following documents:

Storable Class-to-SQL Mapping in BRM Developer's Reference
Storable Class Definitions in BRM Developer's Reference

Note:

The database tables are created during BRM installation.

Table 3-2 Tables Affected by the Conversion Process

Table Name	Description
ACCOUNT_T	Master account table that represents billable accounts in BRM. Only one row is added to this table for each account in BRM.
ACCOUNT_EXEMPTIONS_T	Contains an entry for each tax exemption that applies to an account.
ACCOUNT_NAMEINFO_T	Contains name and address information for accounts. There is one row for each name and address type (home address, work address, mailing address, and so forth). There must be at least one row in this table for each row in ACCOUNT_T. For example, you can have billing name and address information, technical contact name and address information, and sales name and address information.
ACCOUNT_PHONES_T	Contains a phone number and a phone type for each account. There is one row for each phone type (up to seven types are defined, including home, work, fax, and pager). If there is no phone number information for an account, there are no rows in the table for the account.
BALANCE_GROUP_T	Stores the balance information such as dollars, minutes, bytes, and frequent flyer miles for various balance groups in an account. A balance group includes one or more sub-balances for each balance element. The sub-balance includes its current amount, validity dates, rollover data, and contributors.
BAL_GRP_BALS_T	Stores balance group data.
BAL_GRP_SUB_BALS_T	Stores sub-balance data.
BILL_T	Includes billing information, such as the amount due, amount adjusted, currency, and bill number. A /bill object is created at the end of a billing cycle. A /bill object is created for every account. The account receivable for a bill is stored in the /item objects that point to the balance groups associated with the bill. The /bill object points to the /account object, the account's bill unit (/billinfo object), and the /invoice object.
BILLINFO_T	Stores all billing, payment method, accounting cycle, and hierarchy information necessary to bill an account. A bill unit is created for every account. If the bill unit is nonpaying, the /billinfo object points to the paying parent /billinfo object.
DEVICE_T	Stores information about devices. There is a separate /device object for every device managed by BRM. Generic data applicable to all devices is stored in the parent /device object. Subclasses such as /device/num store information specific to a particular device type.
DEVICE_NUM_T	Stores device information specific to phone numbers managed by Number Manager.
DEVICE_SERVICES_T	Stores device/service mapping data.
DEVICE_SIM_T	Stores device information specific to SIM cards managed by SIM Manager.
EVENT_T	Contains a row for each account that has a nonzero balance forward amount. This table stores data for every event that occurs for an account or service. In the case of conversion of an account that has a balance, you should add a row to reflect the posting of that balance. You also need to write an entry to this table for each entry you write to the EVENT_BILLING_DEAL_INFO_T and EVENT_BILLING_PRODUCT_ACTION_T tables. In addition, you can optionally add rows to this table to store past activity for an account. For example, use this table to add rows for importing past billing and payment history into BRM. These rows can be brought over as memo type events, which can be viewed, but they do not affect the current account balance (which should be calculated and posted separately).
EVENT_BAL_IMPACTS_T	Stores event data.
EVENT_ESSENTIALS_T	Stores event data.
GROUP_T	Represents collections of other objects that have an assigned set of shared attributes. This table is optional. If you use it at conversion time, one row must be added to this table if the account is a parent account.
GROUP_BILLING_MEMBERS_T	Rows for this table are optional and are included only for child accounts (that have a parent account). During conversion, one row needs to be added to this table for each child account (regardless of the child account's payment method).
GROUP_PERMITTEDS_T	Indicates which accounts are group accounts. Rows for this table are optional and are included only for parent accounts that have at least one child account.
GROUP_SHARING_MEMBERS_T	Stores members of a sharing group.
GROUP_SHARING_CHARGES_T	Stores charges of a sharing group.
GROUP_SHARING_DISCOUNTS_T	Stores discounts of a charge sharing group.
GROUP_SHARING_PROFILES_T	Stores the profile data that is shared in a profile sharing group.
ITEM_T	Created to bundle events, this table summarizes billable item activity by type. Rows in this table are added for each row in the BILL_T table. In a conversion scenario, only one type of ITEM_T entry is important, the /usage item. Although there are others (cycle forward item, payment item, dispute item, and so on), they are normally not pertinent for conversion. For conversion, a default /item/misc is created to bundle events. Also, a service item is created during conversion only if the recreate ='/item/<item-type>' is specified in the input XML data.
JOURNAL_T	Stores general ledger (G/L) journal data.
ORDERED_GROUPS_T	Stores ordered balance group data.
ORDERED_BALGROUP_T	Stores ordered balance group data.
PAYINFO_T	Stores generic payment method information for an account. Only one row is added in this table for each row in ACCOUNT_T. In addition to a row in PAYINFO_T, there must also be a row added to the applicable payment method table. For example, you have to add a row to PAYINFO_INV_T if the payment method for an account is Invoice. Additionally, if you have created a custom payment method and a PAYINFO_paymentMethod_T table, where paymentMethod is the custom payment method, you must add a row to that table for each account with that payment method.
PAYINFO_CC_T	Contains a row for each account that has a payment method of Credit Card. This row is in addition to a row in PAYINFO_T. Note: This payment method assumes that you are using the BRM-provided FirstUSAPaymentech or FDC modules to process your credit cards or that the clearing house or bank you use can process the same information. If your credit card processing is significantly different, you might want to set it up as another payment method and store information on that payment method separately. If you migrate tokenized credit or debit card numbers, ensure that you add the card type value for each credit card account. This ensures that the legacy credit and debit card data is migrated in the same format used for storing the credit card data in the PAYINFO_CC_T table. The following card type values are used in the PAYINFO_CC_T table: 1 for VISA card 2 for MASTER card 3 for American Express card 5 for Discover card 6 for Diners Club card 7 for Carte Blanche 8 for JCB 9 for SWITCH 10 for unknown card types
PAYINFO_DD_T	Contains a row for each account that has a payment method of Direct Debit. This row is in addition to a row in PAYINFO_T.
PAYINFO_INV_T	Contains a row for each account that has a payment method of Invoice. Optionally, the table can also contain purchase order (PO) information.
PROFILE_T	Contains profile information, if any. This table can be left empty if you decide not to convert profile information. You can populate only one row in this table for each row in ACCOUNT_T.
PROFILE_customTable_T	Rows are added to this table only if rows are added to PROFILE_T, where customTable is the unique identifier you choose; for example, company name. For each row added to PROFILE_T, a corresponding row is added to PROFILE_customTable_T.
PROFILE_ACCT_EXTRATING_DATA_T	Contains profile information.
PROFILE_SERV_EXTRATING_T	Contains profile information.
PROFILE_SERV_EXTRATING_DATA_T	Contains profile information.
PURCHASED_DISCOUNT_T	Contains an entry for each discount owned by an account at the time of conversion.
PURCHASED_PRODUCT_T	Contains an entry for each charge offer owned by an account at the time of conversion.
SERVICE_T	Stores generic service type information for accounts. There is one row in this table for each applicable service for each entry in ACCOUNT_T. In addition to a row in this table, a row must be created in the service type table, such as broadband service or email.
SERVICE_ALIAS_LIST_T	Stores service aliases, used to identify customers by phone number.
SERVICE_TELCO_T	Stores telco service data.
SERVICE_TELCO_FEATURES_T	Stores telco service data.
SERVICE_TELCO_GSM_T	Stores telco service data.
UNIQUENESS_T	Stores data that enforces uniqueness of logins across different database schemas in a multischema environment.

In addition, the following audit tables are affected:

AU_ACCOUNT_T
AU_BAL_GRP_T
AU_GROUP_SHARING_CHARGES_T
AU_GROUP_SHARING_DISCOUNTS_T
AU_GROUP_SHARING_PROFILES_T
AU_GROUP_T
AU_ORDERED_BALGROUP_T
AU_ORDERED_GROUPS_T
AU_PROFILE_ACCT_EXTRATING_DATA_T
AU_PROFILE_SERV_EXTRATING_DATA_T
AU_PROFILE_SERV_EXTRATING_T
AU_PROFILE_T
AU_PURCHASED_DISCOUNT_T
AU_PURCHASED_PRODUCT_T
AU_SERVICE_ALIAS_T
AU_SERVICE_T
AU_UNIQUENESS_T

Loading Data into Additional Audit Tables

You can configure Conversion Manager to load legacy data into audit tables that are not listed in "Tables Affected by the Conversion Process" by:

Creating control files for each additional audit table
Adding the list of additional audit tables to the pin_cmt utility's Infranet.properties file

The following procedure describes how to configure Conversion Manager to load legacy data into the AU_ACCOUNT_NAMEINFO_T audit table, but you can follow a similar procedure to load legacy data into other audit tables.

To load legacy data into the AU_ACCOUNT_NAMEINFO_T audit table:

Go to the BRM_home/apps/cmt/ctl_files directory.
Copy the control file for the ACCOUNT_NAMEINFO_T table to the BRM_home/apps/cmt/ctl_files/audit directory:
```
cp account_nameinfo_t.ctl BRM_home/apps/cmt/ctl_files/audit/account_nameinfo_t.ctl
```
Open the BRM_home/apps/cmt/ctl_files/audit/account_nameinfo_t.ctl file in a text editor.
Change all instances of ACCOUNT_NAMEINFO_T to AU_ACCOUNT_NAMEINFO_T.

For example, change these lines:
```
LOAD DATA
APPEND
INTO TABLE ACCOUNT_NAMEINFO_T
```
to the following:
```
LOAD DATA 
APPEND 
INTO TABLE AU_ACCOUNT_NAMEINFO_T
```
Save and close the file.
Open the BRM_home/apps/cmt/ctl_files/Infranet.properties file in a text editor.

Add account_nameinfo_t to the infranet.cmt.auditloaders line:

infranet.cmt.auditloaders = account_t; bal_grp_t; group_sharing_charges_t;...; account_nameinfo_t;

Save and close the file.