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

Prepopulating and Migrating Product Catalog Tables

You must prepopulate the product catalog tables with new data required for to Oracle Self-Service E-Billing Version 6.2, 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_PRODUCT_RATE_MAP_DIM. Mappings between the detail subproduct record in the EDX_RPT_SUB_PRODUCT_DIM table and a tariff code in the EDX_RPT_TARIFF_DIM table.
• EDX_RPT_SERVICE_PRODUCT_FACT. Costs of the plans, or products, add-on products acquired.

Specifications for the Oracle Self-Service E-Billing 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 Self-Service E-Billing.

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 Self-Service E-Billing 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.

   • 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_USAGE	Stores the subproduct cost or how much allowance is available.
     SUB_PRODUCT_UNIT	The unit of measure associated with the usage data in the SUB_PRODUCT_USAGE column. For example, if the usage is for product allowance, the SUB_PRODUCT_UNIT value could be Minutes, Message, and so on.
     ALLOWANCE_ID	Identifies which records are for product allowance. The EDX_RPT_SUB_PRODUCT_DIM table stores both allowance and overage records. An allowance record must have a value of Y. The default value is N.

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

     EDX_RPT_PRODUCT_RATE_MAP_DIM Column Name	Description
     SUB_PRODUCT_KEY	A reference to the SUB_PRODUCT_KEY column in the EDX_RPT_SUB_PRODUCT_DIM table.
     TARIFF_KEY	A unique code that identifies the product.

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

     EDX_RPT_SERVICE_PRODUCT_DIM 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_RPT_SERVICE_DIM table.
     ACCOUNT_KEY	A reference to the ACCOUNT_KEY column in the EDX_RPT_SERVICE_DIM table.
     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 Self-Service E-Billing Version 6.2 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 has been 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:

     UPDATE EDX_RPT_PRODUCT_DIM SET ADD_ON_FLAG = 'Y' WHERE PRODUCT_KEY = product_key_value)

     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. You can reload all of your products with a value for the ADD_ON_FLAG 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 column has been added in Oracle Self-Service E-Billing Version 6.2, with a default value of N.

     Determine which of your subproducts are an allowance and update those records as follows:

     UPDATE EDX_RPT_SUB_PRODUCT_DIM SET ALLOWANCE_ID = 'Y' WHERE SUB_PRODUCT_KEY = sub_product_key_value)

     You can optionally use an ETL load to populate this table. Column 10 (position 11) on record type 150 is where the ALLOWANCE_ID column is located. You can reload all of your subproducts with a value for the ALLOWANCE_ID 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 - How to Populate the EDX_RPT_PRODUCT_RATE_MAP_DIM table

This example shows how to populate the link table, EDX_RPT_PRODUCT_RATE_MAP_DIM, which maps the detail lines in the subproduct table, EDX_RPT_SUB_PRODUCT_DIM, with the tariff table, EDX_RPT_TARIFF_DIM.

Table 6 shows sample data for three records in the EDX_RPT_PRODUCT_RATE_MAP_DIM table.

Example 2 - 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 7 shows a sample data record for a plan type called Family Plan in the EDX_RPT_PLAN_TYPE_DIM table.

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

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

Example 3 - Insurance Plan

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

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

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

Table 12 shows two sample data records 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 13 shows two records for these two service plan products in the EDX_RPT_SERVICE_PRODUCT_FACT table.