Synchronization Between CC&B and WAM v1.9.1.x

This section provides details on the required synchronization between Oracle Utilities Customer Care and Billing and Oracle Utilities Work and Asset Management v1.9.1.x, and discusses how to complete these synchronizations.

Premises and Service Points translate to Oracle Utilities Work and Asset Management v1.9.1.x Assets. Oracle Utilities Customer Care and Billing Accounts with valid Service Agreements translate to Oracle Utilities Work and Asset Management v1.9.1.x Customers. The synchronization of this data is done by batch processing in an initial load. The system then keeps the data in sync using periodic incremental updates. No user action is required to complete these updates.

Both participating applications must be installed, set up and working properly to complete the tasks described in this section.

Synchronizing Premises and Service Points

The integration supports synchronizing Oracle Utilities Customer Care and Billing premise and service point information so that a corresponding asset is created in Oracle Utilities Work and Asset Management v1.9.1.x. As premises and service points are added or changed, database triggers capture these actions and write information to a change log table.

Important! Database configuration is required. Be sure that your system administrator has performed all set up requirements to configure the database for the integration.

If the functionality provided by the base integration does not meet your business requirements, you can create your own batch processes for synchronizing data and/or create Customer Modification (CM) triggers to capture different premise and service point changes.

Processing Details

• When users update Oracle Utilities Work and Asset Management v1.9.1.x Asset records that originated from Oracle Utilities Customer Care and Billing, Oracle Utilities Work and Asset Management v1.9.1.x does not send updates back to Oracle Utilities Customer Care and Billing and Asset fields are not restricted from update in Oracle Utilities Work and Asset Management v1.9.1.x.

• Records can be downloaded from Oracle Utilities Customer Care and Billing in two modes - initial load and incremental update.

• The integration extracts records from Oracle Utilities Customer Care and Billing and uploads them into the Oracle Utilities Work and Asset Management v1.9.1.x Asset Interface table. At the scheduled run time, the Oracle Utilities Work and Asset Management v1.9.1.x batch job creates new Asset records or updates existing Asset records with data from the Asset Interface Table and the defaults from the business rules.

• The system assigns a Premise Asset Record Type when an Asset is created from an Oracle Utilities Customer Care and Billing Premise or assigns a SP Asset record Type when an Asset is created from an Oracle Utilities Customer Care and Billing Service Point. The Asset record types are defined in code table 230. The Oracle Utilities Customer Care and Billing Premise Types and Asset Types are defined in the Oracle Utilities Work and Asset Management v1.9.1.x Asset Type Table (Code Table 29).

• An Asset record is not created for a Premise until a Service Point has also been created in Oracle Utilities Customer Care and Billing for the Premise. The system does not transfer information for a Premise or Account without a Service Point in Oracle Utilities Customer Care and Billing.

• When users create or update customer records in Oracle Utilities Customer Care and Billing, the integration populates related information in Oracle Utilities Work and Asset Management v1.9.1.x. This information can be found in the Oracle Utilities Customer Care and Billing Account Information section on the Oracle Utilities Work and Asset Management v1.9.1.x Customer module header. The Oracle Utilities Work and Asset Management v1.9.1.x Customer ID is equivalent to the Oracle Utilities Customer Care and Billing Account Number.

• Related Service Agreement information is displayed in the Oracle Utilities Work and Asset Management v1.9.1.x Customer module Address (Detail) view in the Oracle Utilities Customer Care and Billing Service Agreements section. The Premise ID is populated in the Oracle Utilities Customer Care and Billing Premise ID field on the same screen. The account may have multiple addresses, so the Contact Info flag is set on the Oracle Utilities Customer Care and Billing mailing address record.

Updating Information

When an existing Premise and/or Service Point are updated in Oracle Utilities Customer Care and Billing, the integration records are changed in the Oracle Utilities Work and Asset Management v1.9.1.x Interface Table and uploaded into Oracle Utilities Work and Asset Management v1.9.1.x.

Premise Alternate Addresses and Non-Badged items associated to Service Points are not created or updated on the Oracle Utilities Work and Asset Management v1.9.1.x Asset record.

Before Running CC&B Background Download Process

Before you run the Oracle Utilities Customer Care and Billing background download process, complete the following configurations, data backup, and create a database link.

WAM v1.9.1.x Configurations to Complete

Manually configure Oracle Utilities Work and Asset Management v1.9.1.x Asset Type Codes Code Table.

Synchronize asset types between Oracle Utilities Work and Asset Management v1.9.1.x and Oracle Utilities Customer Care and Billing to use this data as part of the integration business processes.

Description	Asset Type Codes
Used by Integration Point	Work Order and Asset Data Synchronization
CC&B Entity	SP_TYPE or PREMISE_TYPE
WAM Entity	Asset Type Codes Code Table
Required Mapping	Asset Type Code == SP_TYPE Code (if Asset is an SP)
Required Mapping	Asset Type Code == PREMISE_TYPE Code (if Asset is a Premise)

• Configure Oracle Utilities Work and Asset Management v1.9.1.x Batch Processes.

• Configure the following Oracle Utilities Work and Asset Management v1.9.1.x Business Rules.

• Default Accts for Interfaces Business Rule

• Interface Parameters Rule

• Interfaces Rules

• Product Integration CCB Rule

Data Backup

Ensure that you have created a backup of the database prior to running the Oracle Utilities Customer Care and Billing background download process.

Create Database Link

Create a database link called WAM from the Oracle Utilities Customer Care and Billing database to the Oracle Utilities Work and Asset Management v1.9.1.x database before running the Oracle Utilities Customer Care and Billing Background Download Process. This database link should have access to synergen objects in Oracle Utilities Work and Asset Management v1.9.1.x database.

Copy and unzip the file WAM.zip to a local drive on the desktop. This zip file is shipped with Oracle Utilities Customer Care and Billing and can be located on the Oracle Utilities Customer Care and Billing shipment. After the file is unzipped, follow the steps to complete the synchronization:

Create A Database Link

1. Navigate to the Scripts sub-folder.

This sub-folder contains the SQL files that update your database for this installation. Some of the SQL files in this folder may contain DDL (data definition language) statements that may change the object definition or create new objects in your database. You may customize these DDL statements to match your storage requirements.

2. Login to the Oracle Utilities Customer Care and Billing database as application owner (CISADM) using SQLPlus.

The integration requires that you have triggers defined in the Oracle Utilities Customer Care and Billing database.

3. Run the following sql scripts to generate the triggers:

• C1_CCB_WAM_ACCT.SQL

• C1_CCB_WAM_ACCTPER.SQL

• C1_CCB_WAM_PER.SQL

• C1_CCB_WAM_PERN.SQL

• C1_CCB_WAM_PERP.SQL

• C1_CCB_WAM_SA.SQL

• C1_CCB_WAM_SP.SQL

• C1_CCB_WAM_PREM_AST.SQL

• C1_CCB_WAM_PREM_CUST.SQL

4. Login to the Oracle Utilities Customer Care and Billing database as SYSTEM and run the following SQL script:

• C1_CCB_WAM_SYNON.SQL

Oracle Utilities Customer Care and Billing Background Download Process

Run the download batch process as an initial sync or as an incremental update. Run this process for your initial synchronization.

Process	C1-WAMAS
WAM Interface Table	WAIF_ASSET
Description	C1-WAMAS is responsible for extracting the information for the integration and updating WAIF_ASSET.

Oracle Utilities Work and Asset Management v1.9.1.x Upload Process

Run WIFP_ASSET_INTERFACE to update the Oracle Utilities Work and Asset Management v1.9.1.x application tables with the Asset information extracted from Oracle Utilities Customer Care and Billing into WAIF_ASSET. WIFP_ASSET_INTERFACE was set up as part of configurations.

WAM Interface Table	WAIF_ASSET
WAM Batch Process	WIFP_ASSET_INTERFACE

Synchronizing Account/Service Agreements and Customers

A customer in Oracle Utilities Customer Care and Billing is defined as the main person on an account with an active service agreement (Pending Start, Active or Pending Stop statuses) with a Service Agreement / Service Point relationship.

When Oracle Utilities Customer Care and Billing users create new Accounts and Service Agreements, batch processing automatically creates a Customer record in Oracle Utilities Work and Asset Management v1.9.1.x. Additions and changes to Oracle Utilities Customer Care and Billing Accounts are also synchronized with Customer records in Oracle Utilities Work and Asset Management v1.9.1.x as needed.

Processing Details

• Records are extracted and uploaded into the Oracle Utilities Work and Asset Management v1.9.1.x Customer Interface table. At the scheduled run time, the Oracle Utilities Work and Asset Management v1.9.1.x batch job manager creates new Customer records containing the Customer’s name and ID and associated Premise Address data and Service Agreements for the Account.

• Customer records become Active or Inactive in Oracle Utilities Work and Asset Management v1.9.1.x based on whether a currently active Service Agreement/Service Point relationship exists (SA start/stop). Oracle Utilities Work and Asset Management v1.9.1.x uses the Account ID assigned in Oracle Utilities Customer Care and Billing as the Customer ID.

• A Customer record is not created until a service agreement has been created for the Account in Oracle Utilities Customer Care and Billing.

Updating Information

As customer information is added or changed in the integration, database triggers capture these actions and write information to a change log table. The same processing as is used for new records updates fields in the Customer records.

Oracle Utilities Customer Care and Billing Background Download Process

CC&B Background Process	C1-WAMEX
WAM Interface Table	WAIF_CUSTOMER
WAM Batch Process	WIFP_ASSET_INTERFACE
Guideline	C1-WAMEX extracts information from CC&B and updates WAIF_CUSTOMER through a database link. Run the download batch process as an initial sync or as an incremental update.

Oracle Utilities Work and Asset Management v1.9.1.x Upload Process

WAM Interface Table	WAIF_CUSTOMER
WAM Batch Process	WIFP_CUSTOMER_INTERFACE
Guideline	In order to upload the Customer information extracted from CC&B into WAM application tables, run WIFP_CUSTOMER_INTERFACE.

Tracking Data Changes

A change in some of the Premise, Service Point and Customer data in Oracle Utilities Customer Care and Billing populates the Change Log Table using database triggers. The Change Log Table tracks changes to the source data that must be reflected in the asset management system. The database triggers enabled on the affected tables populate the Oracle Utilities Customer Care and Billing Change Log Table with the following fields:

• Batch Control - Responsible for extracting the changes made to records on the Table and moving the data from Oracle Utilities Customer Care and Billing to the Oracle Utilities Work and Asset Management v1.9.1.x Integration tables using db link.

• Table – The table where the data has been manipulated.

• Primary Key Value – The value of the extract driver.

• Change Type – The type of change. Possible values include Insert, Update and Delete.

• Date/Time – The date and time that the change occurred.

Triggers

The following tables show a summary of the triggers used for the batch control indicated. The trigger is required in the Oracle Utilities Customer Care and Billing database to track the indicated changes.

Batch Control: C1-Oracle Utilities Work and Asset Management v1.9.1.xAS

Changes Tracked: Asset

Trigger Name	Table	Action Monitored	PK Values Stored	Fields Monitored
C1_CCB_WAM_SP	CI_SP	Insert/Update	SP_ID	PREM_ID SP_TYPE_CD SP_STATUS_FLG INSTALL_DT
C1_CCB_WAM_PREM_ AST	CI_PREM	Update	PREM_ID	PREM_TYPE_CD PARENT_PREM_ID ADDRESS1 ADDRESS2 CITY STATE POSTAL

Batch Control: C1-Oracle Utilities Work and Asset Management v1.9.1.xEX

Changes Tracked: Customer

Trigger Name	Table	Action Monitored	Filter Condition	PK Values Stored	Fields Monitored
C1_CCB_WAM_ACCT	Account	Update		ACCT_ID	MAILING_PREM_ID BILL_CYC_CD SETUP_DT
C1_CCB_WAM_ACCTPER	Account Person	Insert/Update		ACCT_ID, PER_ID	MAIN_CUST_SW ACCT_REL_TYPE_CD BILL_ADDR_SRCE_FLG PER_ID
C1_CCB_WAM_PER	Person	Update		PER_ID	EMAILID
C1_CCB_WAM_PERN	Person Name	Insert/Update	new.PRIM_NAME_SW = 'Y'	PER_ID	ENTITY_NAME PRIM_NAME_SW
C1_CCB_WAM_PERP	Person Phone	Insert/Update		PER_ID, SEQ_NUM	PHONE EXTENSION
C1_CCB_WAM_PREM_CUST	Premise	Update		PREM_ID	PREM_TYPE_CD PARENT_PREM_ID ADDRESS1 ADDRESS2 CITY STATE POSTAL
C1_CCB_WAM_SA	SA	Update		SA_ID	SA_STATUS_FLG SA_TYPE_CD START_DT
C1_CCB_WAM_SASP	SASP	Insert		ACCT_ID, SA_ID, SA_SP_ID

Normally, the PK Values contain the Prime Key of the table being monitored; however, to reduce duplicate records from being inserted in the Oracle Utilities Work and Asset Management v1.9.1.x interface tables, some of the tables have the ACCT ID in the prime key.

Note: The PLANT can be defined in 3 different places in the system. The triggers do not track changes to PLANT because this field is rarely changed. Plant is synchronized only to Oracle Utilities Work and Asset Management v1.9.1.x when the batch process is run on initial load.