Adding Custom Tables for Oracle Utilities Network Management System
Most of the tables related to tables used for populating the out-of-the-box star schemas are listed in the metadata configuration “Source Tables”. It is possible that the table required to be extended is not listed.
To include the table to be extended in the source tables list:
1. Create the CM_NMS_CREATE_METADATA procedure.
2. Create a new task for each metadata entry into B1_OBJECT_MAP. The tasks within the procedure should have the logical schema set to “Metadata”.
3. Add an entry in B1_OBJECT_MAP setting SOURCE_OBJECT_NAME as the table name and TARGET_OBJECT_NAME as the target fact or dimension, which has attributes loaded from this table.
These should be written as merge statements so that existing rows are skipped and only new rows are added. If the metadata requires corrections, use the update clause of the merge statement. The schema names should not be hardcoded.
For example: The following merge statement sets the tables under a maintenance object in Oracle Utilities Customer Care and Billing for inclusion in the replication process.
• Source Product Flag is the product flag of the source. In this example, it is 'NMS' for Oracle Utilities Network Management System.
• Source Object Name is the source table. In this example, the table ‘CM_XYZ’ is included.
• Target Object Name is the ETL view that uses the tables of this maintenance object. In this example, CM_TEST_VW is specified as dummy value.
• Object Type Flag is the type of object that is being replicated. In this example, the replication table is specified as 'TBL'.
merge
into b1_object_map tgt
using (select 'NMS' prod_flg
, 'CM_XYZ' source_object_name
, 'CM_TEST_VW' target_object_name
, 1 seq
, 'TBL' object_type_flg
from dual ) tgt_val
on ( tgt.prod_flg = tgt_val.prod_flg
and tgt.source_object_name = tgt_val.source_object_name
and tgt.target_object_name = tgt_val.target_object_name
and tgt.seq = tgt_val.seq)
when not matched
then insert
(
tgt.object_map_id
, tgt.prod_flg
, tgt.source_object_name
, tgt.target_object_name
, tgt.seq
, tgt.object_type_flg
, tgt.char_entity_flg
, tgt.upd_dttm
, tgt.upd_user
, tgt.owner_flg
)
values
(
b1_object_map_seq.nextval
, tgt_val.prod_flg
, tgt_val.source_object_name
, tgt_val.target_object_name
, tgt_val.seq
, tgt_val.object_type_flg
, null
, sysdate
, sys_context('userenv', 'os_user')
,'B1');
 
4. Execute the following Insert statement to specify that the ETL view CM_TEST_VW populates the target CM_F_ZZZ. The Source Product Flag is ‘NMS’.
merge
into b1_object_map tgt
using (select 'NMS' prod_flg
, 'CM_TEST_VW' source_object_name
, 'CM_F_ZZZ' target_object_name
, 1 seq
, 'PRVW' object_type_flg
from dual ) tgt_val
on ( tgt.prod_flg = tgt_val.prod_flg
and tgt.source_object_name = tgt_val.source_object_name
and tgt.target_object_name = tgt_val.target_object_name
and tgt.seq = tgt_val.seq)
when not matched
then insert
(
tgt.object_map_id
, tgt.prod_flg
, tgt.source_object_name
, tgt.target_object_name
, tgt.seq
, tgt.object_type_flg
, tgt.char_entity_flg
, tgt.upd_dttm
, tgt.upd_user
, tgt.owner_flg
)
values
(
b1_object_map_seq.nextval
, tgt_val.prod_flg
, tgt_val.source_object_name
, tgt_val.target_object_name
, tgt_val.seq
, tgt_val.object_type_flg
, null
, sysdate
, sys_context('userenv', 'os_user')
,'B1');
 
5. Create the CM_NMS_CREATE_METADATA package.
a. Add the procedure created in the steps mentioned above.
b. Add the B1_CFG_METADATA scenario.
c. Add the B1_CFG_INSTANCE_JOBS scenario.
d. After migrating the CM Project to new environment, execute the package after adding the product instance.
This job should be executed in the context for the product.
Executing the created package in the appropriate context ensures that the required tables are present in the metadata configuration tables. For instructions, refer to the Enabling Replication section.