Installation Guide for Oracle Billing Insight > Migrating to Oracle Billing Insight 7.0 > Process of Migrating Oracle Self-Service E-Billing Version 6.1 to Oracle Billing Insight Version 7.0 >

Prepopulating and Migrating Product Catalog Tables

You must prepopulate the product catalog tables with new data required for Oracle Billing Insight Version 7.0, and then migrate your existing data. The product catalog tables that you must update contain the following types of information:

• EDX_RPT_PLAN_TYPE_DIM. Brief definitions of plan types. For example, some organizations have Family and Individual plan types, or options such as Road-Side Assistance.
• EDX_RPT_PRODUCT_DIM. Brief product definitions.
• EDX_RPT_SUB_PRODUCT_DIM. Details of what the product comprises.
• EDX_RPT_SERVICE_PRODUCT_FACT. Costs of the plans, or products, add-on products acquired.

Specifications for the Oracle Billing Insight database tables are described in the Data Dictionary, provided in your product. For details on accessing the Data Dictionary as well as additional details on file specifications required with the ETL process, see Implementation Guide for Oracle Billing Insight.

To prepopulate and migrate product catalog tables

1. Prepopulate the product catalog tables with required data. Create a Master data file with the following records, and load it into the Oracle Billing Insight database using the ETL process.
   • For each plan type, add a record type 140 with the following columns in the EDX_RPT_PLAN_TYPE_DIM table.

     A default entry with a value of UNK appears in the column PLAN_TYPE_CD after installation. This value represents Unknown plan types.

     EDX_RPT_PLAN_TYPE_DIM Column Name	Description
     PLAN_TYPE_KEY	A surrogate key that uniquely identifies a record that uses the sequence table, SEQ_EDX_RPT_PLAN_TYPE_DIM.
     PLAN_TYPE_CD	A unique code that identifies the plan type.
     PLAN_TYPE_NAME	The name of the plan type, such as Current Calling Plan, Insurance Plan, and so on.

   • For each product, add a record type 150 with the following columns in the EDX_RPT_PRODUCT_DIM table.

     A default entry with a value of UNK appears in the column PRODUCT_CD after installation. This value represents Unknown products.

     EDX_RPT_PRODUCT_DIM Column Name	Description
     PRODUCT_KEY	A surrogate key that uniquely identifies a record that uses the sequence table SEQ_EDX_RPT_PRODUCT_DIM.
     PRODUCT_CD	A unique code that identifies the product.
     PRODUCT_NAME	The product name, such as Anywhere Anytime 1500.
     ADD_ON_FLAG	Identifies add-on products. Add-ons are products that consumers purchase in addition to standard plans, such as Roadside Assistance, Insurance, and so on.
     PRODUCT_DESC	The product description that displays in the applications, such as Anywhere Anytime 1500, 20$ per month, contains 1500 free minutes.

   • For each subproduct, add a record type 150 with the following columns in the EDX_RPT_SUB_PRODUCT_DIM table.

     A default entry with a value of UNK appears in the column SUB_PRODUCT_CD after installation. This value represents Unknown products.

     EDX_RPT_SUB_PRODUCT_DIM Column Name	Description
     PRODUCT_KEY	A surrogate key that uniquely identifies a record that uses the sequence table, SEQ_EDX_RPT_PRODUCT_DIM.
     SUB_PRODUCT_KEY	A surrogate key that uniquely identifies a record that uses the sequence table, SEQ_EDX_RPT_SUB_PRODUCT_DIM. The combination of PRODUCT_KEY and SUB_PRODUCT_KEY make up the primary key for the EDX_RPT_SUB_PRODUCT_DIM table.
     SUB_PRODUCT_CD	A code that identifies the subproduct.
     SUB_PRODUCT_NAME	The name of the subproduct.
     SUB_PRODUCT_DESC	The description of the subproduct.

   • For each product, add a record type 3200 with the following columns in the EDX_RPT_SERVICE_PRODUCT_FACT table.

     EDX_RPT_SERVICE_PRODUCT_FACT Column Name	Description
     PRODUCT_CHILD_KEY	A reference to the SUB_PRODUCT_KEY column in the EDX_RPT_SUB_PRODUCT_DIM table.
     SERVICE_KEY	A reference to the SERVICE_KEY column in the EDX_OMF_SERVICEAGREEMENT table in the OLTP schema.
     ACCOUNT_KEY	A reference to the ACCOUNT_KEY column in the EDX_OMF_SERVICEAGREEMENT table in the OLTP schema.
     PERIOD_KEY	A reference to the PERIOD_KEY column in the EDX_RPT_PERIOD_DIM table.
     SERVICE_TYPE_KEY	A reference to the SERVICE_TYPE_KEY column in the EDX_RPT_SERVICE_TYPE_DIM table (if used).
     PRODUCT_PARENT_KEY	A reference to the PRODUCT_KEY column in the EDX_RPT_PRODUCT_DIM table.
     CHARGE_AMT	The product cost.
     STATEMENT_KEY	A reference to the STATEMENT_KEY column in the EDX_RPT_STATEMENT_FACT table.
     ETL_KEY	A reference to the ETL_FILE_KEY column in the EDX_RPT_ETL_FILE_DIM table.
     PLAN_TYPE_KEY	A reference to the PLAN_TYPE_KEY column in the EDX_RPT_PLAN_TYPE_DIM table.
     PRODUCT_NOTE	A product note.
     DISPUTE_DETAIL_ID	A surrogate key to uniquely identify the dispute detail. This is a reference to the EDX_RPT_DISPUTE_DETAIL table.
     GROUP_KEY	A group key reference to the EDX_RPT_PARTN_MGMT table.

2. Migrate your existing product catalog data. You must build a SQL script to ensure that your usage of the product catalog tables prior to Oracle Billing Insight version 7.0 properly conforms to the following standards:
   • EDX_RPT_PLAN_TYPE_DIM. Data stored in this table can remain the same if you used this table to associate plans to a plan type.
   • EDX_RPT_PRODUCT_DIM. The ADD_ON_FLAG column was added in Oracle Self-Service E-Billing Version 6.2, with a default value of N. Determine which of your products are add-on's and update those records as follows:
     • Use the following statement to update the product key value: UPDATE EDX_RPT_PRODUCT_DIM SET ADD_ON_FLAG = 'Y' WHERE PRODUCT_KEY = product_key_value)
     • Populate the PRODUCT_DESC column with the description to display in applications. This column was added in Oracle Billing Insight version 7.0.

       You can optionally use an ETL load to populate this table. Column 11 (position 12) on record type 150 is where the ADD ON FLAG value is located. Column 12 (position 13) on record type 150 is where the PRODUCT_DESC value is located. You can reload all of your products with a value for the ADD_ON_FLAG column and the PRODUCT_DESC column.

   • EDX_RPT_SUB_PRODUCT_DIM. Each product in the EDX_RPT_PRODUCT_DIM table must have its own set of records in the EDX_RPT_SUB_PRODUCT_DIM table:
     • The ALLOWANCE_ID, SUB_PRODUCT_USAGE, SUB_PRODUCT_UNIT columns have been deprecated in Oracle Billing Insight version 7.0.
     • Populate the SUB_PRODUCT_DESC column with the description to display in applications. This column was added in Oracle Billing Insight version 7.0.

       You can optionally use an ETL load to populate this table. Column 13 (position 14) on record type 150 is where the SUB_PRODUCT_DESC column is located. You can reload all of your subproducts with a value for the SUB_PRODUCT_DESC column.

   • EDX_RPT_SERVICE_PRODUCT_FACT. Data must be stored in the columns as follows:
     • PRODUCT_CHILD_KEY. The value of the SUB_PRODUCT_KEY from the EDX_RPT_SUB_PRODUCT_DIM table.
     • PRODUCT_PARENT_KEY. The value of the PRODUCT_KEY from the EDX_RPT_PRODUCT_DIM table.
     • PLAN_TYPE_KEY. The value of the PLAN_TYPE_KEY from the EDX_RPT_PLAN_TYPE_DIM table.

Examples of Populated Product Catalog Tables

The following examples show sample data records in the product catalog tables.

Example 1- Family Circle 750 Calling Plan

This example shows how data can be set up in the product catalog tables for a calling plan called Family Circle 750.

Table 9 shows a sample data record for a plan type called Family Plan in the EDX_RPT_PLAN_TYPE_DIM table.

Table 10 shows a sample data record for the Family Circle 750 product in the EDX_RPT_PRODUCT_DIM table.

Table 11 shows three sample data records for the subproduct components of the Family Circle 750 plan in the EDX_RPT_SUB_PRODUCT_DIM table.

Example 2- Insurance Plan

This example shows how data can be set up in the tables for additional insurance options.

Table 12 shows a sample data record for a plan type called Insurance in the EDX_RPT_PLAN_TYPE_DIM table.

Table 13 shows a sample data record for the Insurance plan product in the EDX_RPT_PRODUCT_DIM table.

Table 14 shows a sample data record for the subproduct components of the Insurance plan in the EDX_RPT_SUB_PRODUCT_DIM table.

Example 1 - Customer Service Plan

This example shows how to populate the EDX_RPT_SERVICE_PRODUCT_FACT table for a customer's chosen service plan.

In this example, the customer chose the Family Circle 750 plan for $74.99 along with the Basic Phone insurance option for $4.99. This customer did not choose an additional line with their family plan. Table 15 shows two records for these two service plan products in the EDX_RPT_SERVICE_PRODUCT_FACT table.