Oracle Utilities Smart Grid Gateway Integration for Outage Operations

Implementation Guide

E82604-01

Updated May 2017

Oracle Utilities Smart Grid Gateway Integration for Outage Operations, Release 12.1 Implementation Guide

Copyright ©20162017 Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Introduction

This section provides configuration and administration information for the integration between Oracle Utilities Customer Care and Billing (CCB) and Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable (ERP) using the ICS Utilities Adapter including:

•

Prerequisites

•

About the Integration Product

•

Supported Business Processes

•

Process Scheduling

•

Best Practices

Prerequisites

All participating applications (namely Oracle Utilities Customer Care and Billing, Oracle Enterprise Resource Planning Cloud and Oracle Service Oriented Architecture) must be installed, set up, and working properly.

About the Integration Product

This section provides general information about the functionality and processing of the Oracle Utilities Customer Care and Billing Integration to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable using the Integration Cloud Service Utilities Adapter.

Note : This is an AIA Direct Integration using SOA and does not require AIA Foundation Pack to be installed.

The following products are involved in this integration:

•

Oracle Integration Cloud Service (ICS) Utilities Adapter

•

Oracle Utilities Customer Care and Billing

•

Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable

Oracle Integration Cloud Service (ICS) Utilities Adapter

Oracle Integration Cloud Service (ICS) simplifies building integrations in the cloud, enabling to connect securely to applications and services, both in cloud and on-premises.

Oracle Utilities ICS Adapter integrates the Oracle Utilities Suite with your SaaS and on- premise applications to allow faster implementation, increased control, and lower costs.

Trigger (inbound) and Invoke (Outbound) support is provided for Oracle Utilities ICS Adapter. The trigger capability enables Oracle Utilities Applications to trigger integration in Oracle Utilities ICS Adapter and the invoke capability allows invocation of an Oracle Utilities Application using web services from Oracle Integration Cloud Service.

Both Inbound and Outbound Services are exposed using the Oracle Utilities Service Catalog. The Service Catalog provides a comprehensive list of services available for integration. Oracle Utilities ICS Adapter provides a simplified user experience in terms of creating data mappings at design time while constructing integrations with Utilities applications using the Oracle Utilities ICS Adapter.

While creating a connection using Oracle Utilities ICS Adapter, specify a web service that returns a list of all Inbound/Outbound Services currently exposed as part of the Catalog Service by one or more OUAF based applications.

ICS Integration Overview

Oracle Utilities Customer Care and Billing

Oracle Utilities Customer Care and Billing (CCB) is a customer and billing system that manages all aspects of customer service needed by most utilities to operate their business. Basic objects form the core of the system are: person, account, premise, service agreement, and service point. These objects hold demographic, geographic, and financial information about a company's customers and properties. Related to these objects are the processes that they manage: bills, payments, meter readings, field activities, etc.

Note : Refer to the Documentation and Resources section for current application version details.

Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable

Oracle Enterprise Resource Planning Cloud is resource-planning software which enforces industry best-practice controls on data input and process flows as tasks are completed within the system. Processes are separated into modules, and each module consists of a collection of forms where data is entered at each stage of the business process.

As part of the Enterprise Resource Planning functionality, Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable offers flexible ledger architecture, consistent financial and operational information, dynamic planning, budgeting and forecasting, and multi-dimensional profitability analysis.

Note : Refer to the Documentation and Resources section for current application version details.

Supported Business Processes

This integration of products incorporates three areas of key functionality to facilitate the transfer of information between two applications. Data is sent from Oracle Utilities Customer Care and Billing to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and vice versa using ICS Utilities Adapter to support the following transactions and actions.

Processes from Oracle Utilities Customer Care and Billing to Oracle ERP Cloud

Oracle Utilities Customer Care and Billing

Oracle ICS Utilities Adapter

Oracle ERP Cloud

A bill is created/ cancelled.

A payment is created/cancelled.

Adjustment is created/cancelled.

SOA reads and invokes the ICS endpoint for GL Data.Using ICS Utilities Adapter to map data from CCB to ERP, invokes the ICS ERP adapter and invokes ERP endpoint - JournalImportService.

The general ledger is updated with the journal information.

An adjustment whose type indicates AP Request is created

SOA reads and invokes the ICS Endpoint for AP Request.Using ICS Utilities Adapter to map from CCB to ERP invokes the ICS ERP adapter and invokes ERP endpoint - InvoiceInterfaceService.

Customer and AP Request information is used to create a one-time supplier and supplier site. An accounts payable invoice is created and associated with the supplier and supplier site.

Flows from Oracle ERP Cloud to Oracle Utilities Customer Care and Billing

Oracle ERP Cloud

Oracle ICS Utilities Adapter

Oracle Utilities Customer Care and Billing

A payment is created for an invoice related to an Oracle Utilities Customer Care and Billing AP Request.

A check related to an invoice linked to an AP request is re-issued.

Using ICS ERP Adapter to map data from ERP to CCB, invoke the ICS Utilities adapter and invoke SOA Endpoint to update CCB to make payment.

Payment information is sent from ERP Cloud for General Ledger and Accounts Payable to Oracle Utilities Customer Care and Billing. The AP Request is updated with the payment information.

A check related to an invoice linked to an AP request is voided and liability is closed.

Using ICS ERP Adapter to map from ERP to CCB, invoke the ICS Utilities adapter and invoke SOA Endpoint to update CCB to cancel payment.

The AP request and its associated adjustment are cancelled.

Process Scheduling

Depending on the size and complexity of your accounting system and business practices, transactions generated in either of the participating applications are sent to the alternate application on a daily or weekly schedule. Schedule the transfer of this information between applications to occur according to a frequency that is most appropriate for your organization.

Best Practices

The following sections provide business information that helps achieve accurate and error-free movement of data between Oracle Utilities Customer Care and Billing and Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable:

•

Updating DVM in SOA Based ICS Catalog Service

•

Importing CCB-ERP ICS Flows in ICS Environment

•

One-Time Configuration Settings

•

General Ledger Integration

•

Accounts Payable (AP) Request and AP Data Integrations

Note : For information about how to configure the integration specific settings, refer to the Configuring the Integration chapter. Refer to product specific documentation for information on how to complete the product specific configuration tasks.

Updating DVM in SOA Based ICS Catalog Service

The SOA Based ICS Catalog is a pre-requisite for this integration. After installing the CCB-ERP flows, ensure that the DVMs in ServiceCatalog.dvm (which is part of ICSCatalog partition of SOA MDS) is updated with the accurate location of the following:

•

AP Request and GL Data WSDLs in the SOA environment

•

AP Data endpoint

Importing CCB-ERP ICS Flows in ICS Environment

Refer to the CCB-ERP Integration Using Oracle Utilities ICS Adapter and ERP ICS Adapter Installation Guide for instructions on how to import the CCB-ERP ICS flows into the ICS environment.

One-Time Configuration Settings

Certain one-time configuration settings must be coordinated manually to ensure proper results from the movement of data between the two applications.

General Ledger Configuration

Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable is the source of truth for all General Ledger information. Oracle Utilities Customer Care and Billing is considered to be the sub-ledger. It is assumed that the General Ledger has already been configured to accommodate your business needs.

Distribution Codes

Oracle Utilities Customer Care and Billing uses distribution codes to map sub-ledger transactions to the General Ledger Accounts. As part of your Oracle Utilities Customer Care and Billing setup, it is assumed that you have properly mapped your distribution codes to the General Ledger chart of accounts.

General Ledger Divisions for Non-Integrated Transactions

If some of the transactions created in Oracle Utilities Customer Care and Billing must not be integrated to your General Ledger, configure a separate General Ledger Division for these transactions. Then, configure the integration product to distinguish which General Ledger Division must be integrated with the General Ledger (it ignores all others).

General Ledger Integration

Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable accounts are structured using account segments. These are set up in your existing Oracle Enterprise Resource Planning Cloud for General Ledger and Accounts Payable according to your business practices. Oracle Utilities Customer Care and Billing distribution codes must be configured to mirror the segments in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable. The segment positions are separated by dots ‘.’ in Oracle Utilities Customer Care and Billing according to the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable segments.

Create your Accounting field in the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable General Ledger (if it does not exist) then set up your Oracle Utilities Customer Care and Billing distribution codes to map to the General Ledger account structure using dot separators.

There is a dual use of the word "account" for the Oracle Enterprise Resource Planning Cloud general ledger accounts and the Oracle Utilities Oracle Utilities Customer Care and Billing customer billing accounts. A customer billing account is a collection of information associated with a customer that is used in the Oracle Utilities Customer Care and Billing payment and billing process, and does not relate to the Oracle Enterprise Resource Planning Cloud accounting definition of account (General Ledger Account). The Oracle Utilities Customer Care and Billing distribution code is used to map to the Oracle Enterprise Resource Planning Cloud general ledger account.

This section includes the following:

•

Oracle ERP Cloud Financials for General Ledger and Accounts Payable General Ledger Settings

•

Oracle Utilities Customer Care and Billing General Ledger Settings

•

General Ledger Settings in Integration Layer

•

Accounting

Oracle ERP Cloud Financials for General Ledger and Accounts Payable General Ledger Settings

To configure the General Ledger settings in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable, refer to the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable documentation, ensuring that Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable is the source of truth for the general ledger.

The integration software relies on other Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable configuration information, including Calendars, Ledger, Journal source, and Journal Category. These are usually already configured as part of the implementation. If these do not already exist, configure them for the integration software to run appropriately.

Oracle Utilities Customer Care and Billing General Ledger Settings

To configure the General Ledger settings in Oracle Utilities Customer Care and Billing, refer to the Oracle Utilities Customer Care and Billing documentation, ensuring that Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable is the overriding source for the general ledger.

•

Schedule the GLASSIGN and GLS batch processes to run at an appropriate time of day. These processes get sub ledger information in Oracle Utilities Customer Care and Billing and all information that has not been posted to the General Ledger, making them ready to extract, transform and load to the General Ledger. When you configure Oracle Utilities Customer Care and Billing to run this process automatically at a preset time, ensure you have coordinated this timing with the timing of other actions done by Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and the integration product. (Alternatively, you may use the standard user interface within Oracle Utilities Customer Care and Billing to run these batch processes manually).

•

Ensure that the Distribution Codes are configured in Oracle Utilities Customer Care and Billing to properly reflect the General Ledger accounts that must be debited and credited for each type of financial transaction created.

General Ledger Settings in Integration Layer

As part of the technical configuration, configure the following:

•

The E-mail address of the person who must be notified if the integration software detects and logs an error while performing the integration.

•

The Ledger ID, Ledger Source, and Ledger Category to use for journals in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable that came from Oracle Utilities Customer Care and Billing through the integration.

•

If you enter an Oracle Utilities Customer Care and Billing GL Division name in the Integration configuration table (INTEGRATION_LOOKUP_TABLE), then only financial transactions associated with this GL Division are extracted for integration to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable GL.

If all Oracle Utilities Customer Care and Billing financial transactions must come across the integration, then do not specify any General Ledger Divisions in the integration product configuration table.

Accounting

The following table shows the basic accounting debits and credits that can be achieved through the setup indicated above.

Oracle Utilities Customer Care and Billing Event

Debit Account

Credit Account

Charges generated by billing

Accounts Receivable

Revenue

Customer making payment

Cash

Accounts Receivable

AP Request adjustment

Accounts Receivable

Accounts Payable Clearing

Accounts Payable (AP) Request and AP Data Integrations

This section includes the following:

•

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Settings

•

Oracle Utilities Customer Care and Billing Accounts Payable Settings

•

Accounts Payable Settings in Integration Layer

•

Accounting

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Settings

Refer to the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable User documentation for specific guidelines on configuring these settings.

Oracle Utilities Customer Care and Billing Accounts Payable Settings

To configure the Accounts Payable settings in Oracle Utilities Customer Care and Billing, refer to the Oracle Utilities Customer Care and Billing documentation. Ensure that Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable is the overriding source for the general ledger account information.

•

CIS Division: The Oracle Utilities Customer Care and Billing CIS Division characteristic value for AP Operating Unit must match the AP Org ID in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

Note : Refer to the Oracle Utilities Oracle Utilities Customer Care and Billing documentation for more information on the configuration settings referenced in this section.

Accounts Payable Settings in Integration Layer

The integration product extracts the AP Requests that have not been processed yet from Oracle Utilities Customer Care and Billing. After the necessary translations and transformations on the Supplier/Invoice data extracted from Oracle Utilities Customer Care and Billing are applied, the data is loaded into the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable AP Invoice Interface tables.

The following need to be configured in this integration:

•

E-mail address of the person who must be notified if the integration software detects and logs an error while performing the AP Request and AP Data integrations.

•

Oracle Utilities Customer Care and Billing characteristic type holding the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable AP Org ID.

•

The AP Invoice information required by Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable, including Vendor, Vendor Site, Invoice Source, Payment terms, Payment Method, and AP Org ID.

Accounting

The following shows the basic accounting debits and credits that can be achieved through the above AP setup:

Event

Debit Account

Credit Account

AP Request Adjustment in Oracle Utilities Customer Care and Billing (as part of the General Ledger Integration)

Accounts Receivable

Accounts Payable Clearing

AP Invoice Created in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable

Accounts Payable Clearing

AP Liability

AP Invoice Payment in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable

AP Liability

Cash

Understanding the Integration Process

This section provides a detailed business process overview and a technical overview of each of the business processes facilitated by this integration, including:

•

Technical Overview

•

Integration Points

Technical Overview

This is an integration between Oracle Utilities Customer Care and Billing and Oracle ERP Cloud using ICS.

•

The integration use two types of end-to-end integration processes: Asynchronous and Synchronous.

•

Most of the BPEL integration processes interact with Oracle Customer Care and Billing using database adapters, except the CCBCancellationWebService process where it invokes the Oracle Utilities Customer Care and Billing web service.

•

All of the BPEL integration processes do not directly interact with Oracle Enterprise Resource Planning Cloud Financials. The integration BPEL processes interact with Oracle Enterprise Resource Planning Cloud Financials using ICS. There are three integration flows: AP Request, AP Data, and GL in ICS for this integration. Each of the ICS integrations uses Oracle Utilities ICS Adapter and ICS ERP Cloud Adapter as source or target depending on the direction of the flow.

•

When Oracle Utilities Customer Care and Billing is the source, the BPEL integration processes extract data from Oracle Utilities Customer Care and Billing application database tables and convert the data extracted from the database table into an XML format. Then, the XML data is transformed into ICS target application format using XSLT. The ICS flow uses Oracle Utilities ICS Adapter as the source and ERP Adapter as the target. The ERP Adapter invokes ERP web service endpoint.

•

When Oracle ERP Cloud is the source, ICS uses the ERP Adapter as the source and Oracle Utilities ICS Adapter as the target and the Utilities Adapter invokes a BPEL integration process. The BPEL process then transforms data and invokes either the CCBCancellationWebService or data is inserted into Oracle Utilities Customer Care and Billing database tables. When the Oracle Utilities Customer Care and Billing system receives this data, it validates and converts imported data into the appropriate format of entries in the application.

•

In case of any error in the process, the error data is inserted into INTEGRATION_ERROR_STORE table and an optional email notification is sent based on the configuration.

•

For the main integration BPEL processes extensible scopes have been provided.

•

The pre- and post- extension scopes are executed based on the extension flags enabled in INTEGRATION_LOOKUP_TABLE.

•

The extension point flags are defaulted to 'false' in INTEGRATION_LOOKUP_TABLE.

The following table shows the integration points, source and target applications, tables, and processes used to load the data imported from the other system.

Integration Points

Source System

Target System

ICS ERP

ERP Target Table

CCB Target

General Ledger

CCB

ERP

JournalImportService - Business Service

GL_INTERFACE

AP Request

CCB

ERP

InvoiceInterfaceService - Business Service

AP_INVOICES_INTERFACEAP_INVOICE_LINES_INTERFACE

AP Data

ERP

CCB

InvoiceStatus Business Object

The appropriate AP Request is updated with Oracle Utilities Customer Care and Billing.

Below is a high-level architecture diagram of the integration.

Architecture Diagram

Integration Schema

The integration requires a database to host the required integration schema. This schema can be created in any of the following:

•

An integration database, if one exists.

•

As part of the Oracle Utilities Customer Care and Billing database.

•

As part of another database as determined by your specific technical needs.

The tables listed below are created in the integration schema defined during installation, for the purpose of this integration product.

Note : The integration does not require any database objects to be added to Oracle Utilities Customer Care and Billing or Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable databases other than the objects mentioned here.

The following database tables are required to operate the Oracle Utilities Customer Care and Billing process integration for the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable Financials product.

Table Name

Description

INTEGRATION_LOOKUP_ TABLE

A lookup table to store all the configuration parameters used by the integration processes. This table is also used to configure the email addresses to be notified if errors occur. It is seeded with data at the time of integration product installation.

INTEGRATION_PROCESS_ ACTIVATION

Used to activate or deactivate various integration points available in the Integration product. This table is seeded with data at the time of integration pack installation and is, by default, populated to activate all the available integration points in the product.

INTEGRATION_ERROR_ STORE

Used to hold the information regarding the errors encountered during integration transactions. A record is inserted for each error encountered by the integration process. The MailNotification process accesses this table to get the error information needed to construct the notification email. The table is delivered with no data.

Integration Points

This integration supports the following integration points:

•

General Ledger

•

AP Request

•

AP Data

•

Shared Integration Points

General Ledger

For general ledger transactions, Oracle Utilities Customer Care and Billing is considered the sub-ledger and Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable considered the general ledger:

•

The General Ledger transactions are written in one direction; from Oracle Utilities Customer Care and Billing to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

•

The financial transactions are moved from the sub-ledger to the general ledger when two consecutive Oracle Utilities Customer Care and Billing batch processes, GLASSIGN and GLS, are run according to a set schedule. These are standard processes released with Oracle Utilities Customer Care and Billing.

•

The GLASSIGN and GLS processes group all financial transactions in Oracle Utilities Customer Care and Billing that must be included in a batch. The Integration Point looks for batches of financial transactions that are ready to be sent, extracts and summarizes the data, translates the data from a sub-ledger format to the format required by the general ledger, and invokes Oracle Utilities ICS Adapter. The Oracle Utilities ICS Adapter maps it to the format required by Oracle ICS ERP Adapter that invokes the ERP web service JournalImportService with operation importJournals.

Note : For information about journal creation, editing, and posting to the ledger, refer to the documentation released with the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable product.

Note : Refer to the Oracle Utilities Customer Care and Billing documentation for more information on GLASSIGN and GLS.

GL Process Diagram

The GL Process flow is as follows:

1.

Run GLASSIGN in Oracle Utilities Customer Care and Billing.

2.

Run GLS in Oracle Utilities Customer Care and Billing.

3.

The BPEL process extracts FT information from Oracle Utilities Customer Care and Billing.

4.

The BPEL process updates the Next_Batch_Nbr for GLDL in CI_Batch_Ctrl table in Oracle Utilities Customer Care and Billing.

5.

The BPEL process transforms and invokes ICS endpoint for GL -CCBGL Integration.

6.

Oracle Utilities ICS Adapter receives the request, maps it to the format needed by ERP Adapter and ERP endpoint - JounralImportService web service is invoked and sends a response back to the BPEL Process.

7.

The BPEL process updates the Last_Update_DateTime in CI_Batch_Ctrl table in Oracle Utilities Customer Care and Billing.

8.

Run the Journal Import process in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable to import FTs.

9.

Any errors in interface tables must be corrected in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and Journal Import process must be re-run.

GL Integration Point Logic

The logic used in the GL Integration Point is as follows:

1.

BPEL polls to verify if FTs are ready for extraction.GetCCBGLData integration process polls Oracle Utilities Customer Care and Billing to verify whether financial tractions are ready for extraction.

If GLS has run since the last run of the integrationAND the GLS run has completed successfullyRetrieve the Batch of Rows identified in CI_BATCH_RUN (created by GLS) ElseDo nothing

2.

Update the NEXT BATCH NUMBER in CI_BATCH_CTRL.UpdateCCBGLControlTable runs the query below to update the batch number in the Oracle Utilities Customer Care and Billing batch control table.

Increment by 1 the NEXT_BATCH_NBR in CI_BATCH_CTRL WHERE BATCH_CD is GLDL

3.

Extract Financial Transactions from Oracle Utilities Customer Care and Billing.GetCCBGLData process runs the query below to extract the FTs from Oracle Utilities Customer Care and Billing.Select the information shown in the table below.

FROM CI_FT_PROC, CI_FT_GL, CI_FT FT, CI_DST_CODE_EFF

WHERE the rows were marked by GLS as belonging to the latest batch that is ready to be integrated

Summarize and group the rows:

BY FTPR.BATCH_NBR, FTGL.DST_ID, FTGL.GL_ACCT, FT.CIS_DIVISION, FT.GL_DIVISION, FT.CURRENCY_CD, DST.STATISTICS_CD, FT.ACCOUNTING_DT, DST.FUND_CD

Extract Information

The following table shows the extract information.

Field

Description

Source System

Set to CCB

BATCH_NBR

Batch number for the group of FTs extracted. It is assigned to the financial transaction when GLS is run.

DIST_ID

Distribution code used in Oracle Utilities Customer Care and Billing to derive the GL account information. For example: R - ELERES for electric residential revenue financial transactions

GL_ACCT

Actual GL account with '.' separating the substructure numbers like department. For example 101.73653.8873.87

CIS_DIVISION

CIS Division

GL_DIVISION

GL Division

CURRENCY_CD

Currency type, such as USD

STATISTICS_CD

Identifier of the type of statistical amount being sent to GL, such as KWH for electricity or CCF for gas.

ACCOUNTING_DT

Effective accounting date for the GL transactions

AMOUNT

Dollar amount of the GL debit or credit

STATISTIC_AMOUNT

Quantity associated with the Statics Code

When the fields listed above are executed successfully, continue to execute the following:

•

Update LAST UPDATE TIME FOR GLDL in CI_BATCH_CTRL.

•

Set the last update date and time by setting CI_BATCH_CTRL LAST_UPDATE_DTTM to SYSDATE WHERE BATCH_CD = 'GLDL'.

Composites

The following is the list of composite processes that comprise the GL Integration between Oracle Utilities Customer Care and Billing and ICS that invoke Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

Composite Name

Description

CCBToERPGLRequestSchedu ler

Used to poll the INTEGRATION_PROCESS_ACTIVATION table at set intervals for GL interface

CCBToERPGLBPELProcess

Main process of the GL Integration. Get the GL data from Oracle Utilities Customer Care and Billing and invokes ICS endpoint

GetCCBGLData

To check if GL data is available for transfer

UpdateCCBGLControlTable

To update the Oracle Utilities Customer Care and Billing batch control table when applicable

Integration Process/Composite Name

CCBToERPGLRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the Integration Schema at predefined intervals and invokes the CCBToERPAPBPELProcess

Calls to

CCBToERPGLBPELProcess

ErrorhandlingProcess (if an error occurs)

Calls from

None

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Integration Process/Composite Name

CCBToERPGLBPELProcess

Description

Main process of the GL Integration.

1.

Gets the GL Data from Oracle Utilities Customer Care and Billing, transforms it, and invokes ICS Endpoint to invoke ERP using ICS Utilites adapter and ICS ERP Adapter.

2.

Invokes ERP webservice, JournalImportService.

3.

Records get inserted it into Oracle Enterprise Resource Planning Cloud Financials for General Ledger Interface table (GL_INTERFACE).

4.

Invokes services for error handling and email notification.

Calls to

GetCCBAPData

UpdateCCBAPControlTable

ErrorhandlingProcess (if an error occurs)

MailNotification

Calls from

CCBToERPAPRequestScheduler

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Integration Process/Composite Name

GetCCBGLData

Description

Verify that GL data is available in Oracle Utilities Customer Care and Billing for transfer. If data is available, it returns a dataset to the calling program.

Calls to

Calls from

CCBToERPGLBPELProcess

Inputs

Input String object contains the Integration Point name invoking this BPEL process. For example: ERP

Outputs

SelectCCBGLRecordsOutput xml object

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions: BPEL Remote Fault, BPEL Binding Fault

Integration Process/Composite Name

UpdateCCBGLControlTable

Description

Update the next batch number in the GL Batch Control table available in Oracle Utilities Customer Care and Billing database

Calls to

Calls from

CCBToERPGLBPELProcess

Inputs

None

Outputs

None

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions: BPEL Remote Fault, BPEL Binding Fault

Database Tables

The following Oracle Utilities Customer Care and Billing tables are used when extracting Financial Transaction data for sending to GL as Journal Vouchers.

Table Name

Description

Application Name

CI_FT

Financial Transaction

OUCCB

CI_FT_GL

Financial Transaction General Ledger

OUCCB

CI_DST_CODE_EFF

Distribution Code GL Account

OUCCB

CI_FT_PROC

FT Process

OUCCB

CI_BATCH_CTRL

Batch Control

OUCCB

CI_BATCH_RUN

Batch Run

OUCCB

CI_BATCH_JOB

Batch Job

OUCCB

GL_INTERFACE

Used to stage the incoming accounting entries from Oracle Utilities Customer Care and Billing

ERP

AP Request

For AP transactions, following are the details:

•

The AP Request transactions are written in one direction from Oracle Utilities Customer Care and Billing to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable. Customer, customer account, and AP Request information is extracted from Oracle Utilities Customer Care and Billing and imported to the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable Payables as Invoice import information using ICS. Customer and AP Request information is used to create a one-time supplier and supplier site that is used for invoice creation.

•

The BPEL Integration integration extracts AP Requests from Oracle Utilities Customer Care and Billing where the status of the AP Request is 'N' which indicates that the AP Request is 'Not Selected for Payment'. Once the data has been integrated, the integration software updates the AP Request status in Oracle Utilities Customer Care and Billing to 'R' indicating it has been 'Requested for Payment'.

•

The BPEL Integration process invokes ICS Endpoint for AP Request using the ICS Utilities Adapter. The data is mapped by Utilities Adapter to ERP Adapter format and the ERP webservice InvoiceInterfaceService is invoked by the ICS ERP adapter.

•

Once the customer and refund request data reaches Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable by ICS, the standard Payables Open Interface Import (APXIIMPT) process must be executed to create invoices. This can be accomplished using a scheduled process or by manually running the process through the standard user interface provided within the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable product.

Account Payable Request Process Diagram

The Account Payable Request flow includes the following steps:

1.

Create and freeze an adjustment in Oracle Utilities Customer Care and Billing and run the GLASSIGN batch program.

2.

The integration process extracts AP Refund Request information from Oracle Utilities Customer Care and Billing.

3.

BPEL process updates the Next_Batch_Nbr for APDL in CI_Batch_Ctrl table in Oracle Utilities Customer Care and Billing.

4.

The BPEL process invokes ICS Endpoint for AP Request data using the ICS Utilities Adapter. The ICS Utilities Adapter maps data to a format that the ICS ERP Adapter understands. The ERP Adapter then invokes the ERP Webservice InvoiceInterfaceService in Oracle ERP Cloud for General Ledger and Accounts Payable interface tables.

5.

The BPEL updates the status of A/P Request in Oracle Utilities Customer Care and Billing.

6.

In case of an error, the BPEL decrements the Next_Batch_Nbr for APDL batch code in CI_Batch_Ctrl table in Oracle Utilities Customer Care and Billing.

7.

Run the Payables Open Interface Import process in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable to import the invoices.

8.

Any errors in Interface tables must be corrected in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and the Payables Open Interface Import process must be re-run.

AP Request Integration Point Logic

The logic used in the AP Request integration point is as follows:

1.

Update the NEXT BATCH NUMBER in the CI_BATCH_CTRL.

Increment by 1 the NEXT_BATCH_NBR in CI_BATCH_CTRL where the BATCH_CD is APDL

2.

BPEL polls to verify whether AP Requests are ready for extraction.GetCCBAPData integration process polls Oracle Utilities Customer Care and Billing to verify whether AP Requests are ready for extraction.

If there are AP Requests where the CI_ADJ_APREQ PYMNT_SEL_STAT_FLG is N (Not selected for Payment)

AND the associated adjustment is in a frozen status

Process the AP Requests that have not been integrated before and mark them all with the next APDL Batch Number

Else do nothing

3.

Extract the Customer and AP Refund Request.An extract of Customer and AP refund request is made from Oracle Utilities Customer Care and Billing.Select the following information:

AP_REQ_ID, GL_ACCT,CHAR_VAL, SA_ID, ADJ_ID, CRE_DT, ADJ_TYPE_CD, CIS_DIVISION, CHAR_VAL, ENTITY_NAME, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4,

CITY, NUM1, NUM2, COUNTY, HOUSE_TYPE, STATE, POSTAL, CURRENCY_CD,

CURRENCY_PYMNT, GEO_CODE, IN_CITY_LIMIT, PYMNT_METHOD_FLG, ADJ_AMT, SCHEDULED_PAY_DT

(Refer to the mapping table within this document for more details)

FROM CI_ADJ_APREQ, CI_ADJ, CI_SA, CI_ADJ_TYPE, CI_DST_CODE_EFF, CI_DST_CD_CHAR, CI_CIS_DIV_CHAR

Where the PYMNT_SEL_STAT_FLG status flag is N (Not Selected for

Payment)

AND the Adjustment is frozen

4.

Update the CI_ADJ_APREQ status. In UpdateCCBAPAdjReqTable integration process payment status flag is updated to R.

UPDATE CI_ADJ_APREQ

SET PYMNT_SEL_STAT_FLG to R (Requested for Payment)

5.

Decrement the BATCH NUMBER in CI_BATCH_CTRL and CI_ADJ_APREQ tables in case of an error.In case of an error:

Decrement by 1 the NEXT_BATCH_NBR in CI_BATCH_CTRL where the BATCH_CD is APDL

Update the Batch Number in CI_ADJ_APREQ table to previous value so that the same set of records can be processed again

The BPEL flow for AP Request Integration Point is shown below:

Composites

The following is the list processes that comprise the AP Request Integration between Oracle Utilities Customer Care and Billing and ICS that invokes Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

Composite Name

Description

CCBToERPAPRequestScheduler

Used to poll the INTEGRATION_PROCESS_ACTIVATION table at set intervals for AP interface.

CCBToERPAPBPELProcess

Main process of the AP Integration. Gets the AP Data from Oracle Utilities Customer Care and Billing and invokes ICS Endpoint for AP Request

GetCCBAPData

Checks if AP Data is available in Oracle Utilities Customer Care and Billing for transfer

UpdateCCBAPControlTable

To update the next batch number in the AP Batch Control table

UpdateCCBAPAdjReqTable

To update payment flag to R in the Oracle Utilities Customer Care and Billing CI_ADJ_APREQ table

Integration Process/Composite Name

CCBToERPAPRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the Integration Schema at predefined intervals and invokes the CCBToERPAPBPELProcess.

Calls to

CCBToERPAPBPELProcess

Calls from

None

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Integration Process/Composite Name

CCBToERPAPBPELProcess

Description

Main process of the AP Integration:

•

Gets the AP Data from Oracle Utilities Customer Care and Billing, transforms it, and invokes ICS Endpoint for AP Request.

•

ICS Utilities Adapter uses the mapper and invokes ICS ERP Adapter.

•

The ERP Adapter invokes web service InvoiceInterfaceService that inserts records into Oracle Enterprise Resource Planning Cloud Financials for Accounts Payable AP Invoice Interface tables (AP_INVOICES_INTERFACE, AP_INVOICE_LINES_INTERFACE).

•

Additionally, updates status in two of the Oracle Utilities Customer Care and Billing tables.

•

Invokes services for error handling and email notification.

Calls to

•

GetCCBAPData

•

UpdateCCBAPControlTable

•

UpdateCCBAPAdjReqTable

•

ErrorhandlingProcess (if an error occurs)

•

MailNotification

Calls from

CCBToERPAPRequestScheduler

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault

Integration Process/Composite Name

GetCCBAPData

Description

Checks if AP Data is available in Oracle Utilities Customer Care and Billing for transfer. If data is available, it returns a dataset to the calling process.

Calls to

Calls from

CCBToERPAPBPELProcess

Inputs

Input String object containing the Integration Point name invoking this BPEL process. For example: ERP

Outputs

SelectCCBRecordsWithTemplate xml object

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault

Integration Process/Composite Name

UpdateCCBAPControlTable

Description

Updates the next batch number in the AP Batch Control table available in the Oracle Utilities Customer Care and Billing database.

Calls to

Calls from

CCBToERPAPBPELProcess

Inputs

None

Outputs

None

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions - None

Integration Process/Composite Name

UpdateCCBAPAdjReqTable

Description

Updates PYMNT_SEL_STAT_FLG to R in the Oracle Utilities Customer Care and Billing AP Adjustment Request table CI_ADJ_APREQ for the provided AP Request ID

Calls to

Calls from

CCBToERPAPBPELProcess

Inputs

CiAdjApreqCollection xml object

Outputs

None

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions - None

Database Tables

The Oracle Utilities Customer Care and Billing APREQ table is considered an interface table for this integration point even though it is a core table within Oracle Utilities Customer Care and Billing. BPEL extracts the data directly from the core tables.

The following tables are used while extracting AP Request information from Oracle Utilities Customer Care and Billing.

Table Name

Description

Application Name

CI_ADJ_APREQ

A/P Check Request

OUCCB

CI_ADJ

Adjustment

OUCCB

CI_SA

Service Agreement

OUCCB

CI_ACCT

Account

OUCCB

CI_ACCT_PER

Account Person Relationship

OUCCB

CI_PER

Person

OUCCB

CI_PER_NAME

Person Name

OUCCB

AP_INVOICES_ INTERFACE

Used to stage the incoming AP Requests from Oracle Utilities Customer Care and Billing

ERP

AP_INVOICE_LINES_ INTERFACE

Used to stage the incoming AP Requests from Oracle Utilities Customer Care and Billing

ERP

AP_INTERFACE_ REJECTIONS

Error messages are stored in this table

ERP

The Oracle Application APIs used to create/update Vendor and Vendor Site are as follows:

•

AP_VENDOR_PUB_PKG.CREATE_VENDOR

•

AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE

•

AP_VENDOR_SITES_PKG.UPDATE_ROW

AP Data

The details about AP Data integration point as are below:

•

The AP Data transactions are written in one direction from Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable to Oracle Utilities Customer Care and Billing using ICS.

•

The payment information for system-generated checks to customers is generated and processed in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and then sent to ICS. ICS ERP Adapter receives the request and maps it to a format ICS Utilities Adapter understands. ICS Utilities Adapter invokes the BPEL process to handle AP Data which then exports data to Oracle Utilities Customer Care and Billing using DB Adapter calls or direct web service call.

•

This Payment information corresponds to the AP Refund Requests originally generated in Oracle Utilities Customer Care and Billing and exported to Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable for payment processing. The Integration Point updates the original AP Request in Oracle Utilities Customer Care and Billing with the details of the payment including the check number and date.

•

Once a payment has been created in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable and the information is integrated to Oracle Utilities Customer Care and Billing, the AP Request status in Oracle Utilities Customer Care and Billing is updated to 'P' indicating that the AP Request has been paid. Additional statuses that can occur include 'C' - Closed or on Hold and 'X' - Cancelled.

The table below lists shows how canceled payments are handled:

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Action

Oracle Utilities Customer Care and Billing AP Request Resulting Action

Oracle Utilities Customer Care and Billing Adjustment Resulting Action

Payment is completed

Payment information updated and status changes to "P" for Paid

No change

Payment stopped and placed on hold

Payment status changes to "C" for Closed

No change

Payment is re-issued

Payment information updated and status changes to "P" for Paid

No change

Payment is cancelled and the liability is closed

Payment status changes to "X" for Cancelled

Adjustment is cancelled

Payment Cancellation

When a payment is cancelled in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable, the following options are available:

•

Re-Issue

•

Initiate Stop

•

Void Hold

•

Void Cancel

Re-Issue

If a check is re-issued for any reason in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable, the new information is sent across the integration and is updated on the AP Request in Oracle Utilities Customer Care and Billing and the AP Request status is set to 'P' indicating that the AP Request has been paid.

The AP Request in Oracle Utilities Customer Care and Billing only holds the most recent check information sent (no history of checks re-issued).

Initiate Stop

If a payment is stopped, the cancellation information is sent to Oracle Utilities Customer Care and Billing as updates to the AP Request. The AP Request payment status flag in Oracle Utilities Customer Care and Billing is set to 'C' indicating a 'Closed' status. Only the AP Request is affected, the adjustment in Oracle Utilities Customer Care and Billing is not impacted.

Void Hold

If an invoice is put on hold, the cancellation information is sent to Oracle Utilities Customer Care and Billing as updates to the AP Request. The AP Request payment status flag in Oracle Utilities Customer Care and Billing is set to 'C' indicating a 'Closed' status. Only the AP Request is affected, the adjustment in Oracle Utilities Customer Care and Billing is not impacted.

Void Cancel

If the payment is void cancelled and all liability is closed, the integration cancels the AP Request and then calls an Oracle Utilities Customer Care and Billing service to cancel the adjustment related to the request. The AP Request payment status flag in Oracle Utilities Customer Care and Billing is set to 'X' indicating a 'Cancelled' status. The adjustment is also cancelled using the standard adjustment maintenance object within the Oracle Utilities Customer Care and Billing application software.

An Oracle Utilities Customer Care and Billing algorithm CI_ADCA-CRTD 'Adjustment Cancellation - Create To Do Entry' can be configured to create a To Do List entry to notify the users about the cancellation of the adjustment and AP Request within Oracle Utilities Customer Care and Billing.

The adjustment cancellation algorithm is shipped and documented as part of standard Oracle Utilities Customer Care and Billing application software.

AP Data Process Diagram

Below are the steps in the AP Data process flow when a payment is made in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable:

•

Payment is made in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable for Invoices originated from Oracle Utilities Customer Care and Billing

•

An outbound message is sent from ERP Cloud to invoke ICS endpoint using ERP Adapter for payment. Message is mapped to a format Utilities adapter understands. The Oracle Utilities ICS Adapter then invokes BPEL flow for AP Data.

•

The BPEL process transforms and updates the Payment information in Oracle Utilities Customer Care and Billing.

•

The BPEL process updates the Last Run Date of the AP Data process in Integration schema.

AP Data Cancellation Diagrams

The steps in the AP Data process flow when a payment is cancelled in Oracle ERP Suite Financials for General Ledger and Accounts Payable are as below:

•

Payment is cancelled in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable for invoices originated from Oracle Utilities Customer Care and Billing.

•

An outbound message is sent from ERP Cloud to invoke ICS endpoint using ERP Adapter for cancel. Message is mapped to a format Utilities adapter understands. The Utilities adapter then invokes BPEL flow for AP Data.

•

The BPEL process updates the Cancellation information in Oracle Utilities Customer Care and Billing.

•

The BPEL process invokes AdjustmentMaintenance Web Service in Oracle Utilities Customer Care and Billing to cancel the adjustment.

•

The BPEL process updates the Last Run Date of AP Data process in the integration schema.

AP Data Integration Point Logic

The logic used in AP Data Integration Point is as follows:

1.

Receive the Payment Information from Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable using the InvoiceStatus Business Object in ICS ERP Adapter.

2.

In the BPEL process, for each payment selected above, check if this payment is already applied in Oracle Utilities Customer Care and Billing.

If AP_INVOICES_ALL.CANCELLED_DATE <> NULL and AP_INVOICES_ALL. PAYMENT_STATUS_FLAG<>'Y' (Payment is void /cancelled)

If the Payment has been canceled in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable, update the CI_ADJ_APREQ. PYMNT_SEL_STAT_FLG to

'X'(Canceled) and invoke C1AdjustmentMaintenance service to cancel the Adjustment corresponding to this payment.

The BPEL flow for AP Data Integration Point is shown below:

AP Data Integration Point BPEL Flow

Composites

The following is the list of composites involved in GL integration point.

Composite Name

Description

ERPToCCBAPDataRequestScheduler

Polls the INTEGRATION_PROCESS_ACTIVATION table at predefined intervals for AP Data interface

ERPToCCBAPDataBPELProcess

Main process of the AP Data Integration which collects the payment information from Oracle ERP Cloud and sends it to Oracle Utilities Customer Care and Billing

CCBCancellationWebService

To cancel an adjustment in Oracle Utilities Customer Care and Billing

Integration Process/Composite Name

ERPToCCBAPDataRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the Integration Schema at predefined intervals and invokes the ERPToCCBAPDataBPELProcess

Calls to

•

ERPToCCBAPDataBPELProcess

•

ErrorhandlingProcess (if an error occurs)

Calls from

None

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Integration Process/Composite Name

ERPToCCBAPDataBPELProcess

Description

Main BPEL process of the AP Data Integration receives all the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable payment information from ICS, transforms the data, and updates the information in the Oracle Utilities Customer Care and Billing table application table CI_ADJ_APREQ.

Calls to

•

CCBCancellationWebService

•

ErrorhandlingProcess (if an error occurs)

•

MailNotification

Calls from

ERPToCCBAPDataRequestScheduler

Inputs

None

Outputs

None

Interaction pattern

Asynchronous

Exceptions/Errors

Possible Exceptions - BPEL Binding fault, BPEL Remote Fault

Integration Process/Composite Name

CCBCancellationWebService

Description

This process is a BPEL wrapper to call the Adjustment Maintenance web service in Oracle Utilities Customer Care and Billing to cancel the Adjustment ID corresponding to the AP Request ID for the given payment.

Calls to

ErrorhandlingProcess (if an error occurs)

Calls from

ERPToCCBAPDataBPELProcess

Inputs

String object - AdjustmentID

Outputs

String result - containing APRequest ID for success or error messages

Interaction pattern

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Binding Fault, BPEL Remote Fault

Shared Integration Points

The BPEL processes listed below are used across multiple integration points within this integration product.

Process Name

Error Handling Process

Description

Invoked only when errors occur. Inserts data into error table INTEGRATION_ERROR_STORE in the Integration Schema.

Calls To

None

Calls From

•

CCBToERPGLBPELProcess

•

UpdateCCBAPControlTable

•

CCBToERPAPBPELProcess

•

CCBCancellationWebService

•

ERPToCCBAPDataBPELProcess

Inputs

ErrorHandlingProcessRequest xml object

Outputs

String result

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - None

Process Name

MailNotification

Description

This process is invoked at the end of each batch run. If there was any error generated, it assimilates all these messages into an e-mail and sends the email to configured email address.

Calls To

None

Calls From

•

CCBToERPGLBPELProcess

•

CCBToERPAPBPELProcess

•

ERPToCCBAPDataBPELProcess

Inputs

MailNotificationInputParameters xml object

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Configuring the Integration

This section provides details regarding the configuration settings required for the integration, including:

•

Integration Configuration Checklist

•

Configuring Oracle ERP Cloud Financials for General Ledger and Accounts Payable

•

Configuring Oracle Utilities Customer Care and Billing

•

Configuring the Integration Product

•

Verifying the Implementation

Integration Configuration Checklist

Extensive configuration is required to implement the integration between Oracle Customer Care and Billing and Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

This section provides a list of configuration tasks used as a reference or roadmap, including:

•

Configuring Oracle ERP Cloud Financials for General Ledger and Accounts Payable

•

Configuring Oracle Utilities Customer Care and Billing

•

Configuring Integration Product

Configuring Oracle ERP Cloud Financials for General Ledger and Accounts Payable

Refer to the ERP product documentation to configure the following in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

Step

Information

Comments

A1

Accounting ID

Identify and document the Accounting ID to be used with the integrated data. Decide this at the start of the integrations, so all the journals are accounted and posted to these accounts.

A2

Ledger ID/ Set of Books ID

Document the ledger(s) to which all the accounting entries are to be created and posted. In ERP Cloud, the Ledger ID is used.

A3

Journal Source

Specify the Source of the journal from which it is created.

A4

Journal Category

Specify the Category where all the journals belong.

A5

Organization ID

Document the AP Operating Unit(s) to be used with the integrated data. For example: 'Vision Operations'. Derive the Organization ID with respect to the Operating Unit.For example: '204'. This is used in checklist step B3.

A6

Payment Terms Code

Create or document the payment terms code(s) to be used for paying AP vouchers coming from Customer Care and Billing. For example: Net07 (CCBREFUND)

A7

Invoice Source

Create the invoice source to be used in Payables Import program.For example: 'CCB' to group all invoices coming from Oracle Utilities Customer Care and Billing.

A8

Lookup for Multi-Org setup

Populate values in INT_CCB_ERP_MORG_SETUPS lookup for Multi-Org setup.

A9

Country and State Codes

Define/identify the Country and State codes for addresses. These should match the codes in Oracle Utilities Customer Care and Billing.

A10

Currency Codes

Currency Codes should match with the currency codes in Oracle Utilities Customer Care and Billing.

Configuring Oracle Utilities Customer Care and Billing

Refer to the Oracle Utilities Customer Care and Billing product documentation to configure the following in Oracle Utilities Customer Care and Billing.

Step

Information

Comments

B1

GL Division

Configure the GL Division(s) to be used in the integration. For example: US1. This must match the GL Division specified in Integration_Lookup_table.

B2

Distribution Codes

Configure your distribution codes. For example: 01.520.5250.0000.000 with '01' corresponding to Company, '520' corresponding to Department and so on. Refer to the details of all mapping segments later in this document. This needs to be set up in sync with the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Flexfield.

B3

Operating Unit Characteristic Type

Configure a characteristic type to hold the value of the Operating Unit to be used.Example characteristic type: ERPORGIDThe value you create in this characteristic (Example: 204) must match what you documented in step A5.

B4

Create Cancel Reason Code

Create a Cancel Reason code.

B5

Link the characteristic type created in step B3 with the CIS Division

The CIS Division you are using (Example: CA) now has a characteristic type linked to it (Example: ERPORGID) that holds the name of the Org ID to use (Example: 204).

B6

General Ledger Characteristic Type

Configure a characteristic type to hold the value of the General Ledger ID for ERP Suite. Example characteristic type: GLEDGER.

The value you create in this characteristic (Example: 1) must match what you documented in step A2.

B7

Link the characteristic type created in step B6 with the CIS Division

Add the characteristic type (Example: GLEDGER) to all CIS Divisions and specify the Ledger/set of Books value to be used (Example: 1).

B8

Payment Terms Characteristic Type

Configure a characteristic type to hold the value of the Payment Terms ID to be used when creating Invoices in ERP Cloud.

The value you create in this characteristic must match what you documented in step A6.

B9

Link the characteristic type created in step B8 with the CIS Division

Add the characteristic type (Example: TERMS) to all CIS Divisions and specify the Payment Terms ID value to be used.

Configuring Integration Product

Configure the following in the integration.

Task

Description

Setting Configuration Properties

Set Configurations properties that are used by integration processes. Configuration is done in the INTEGRATION_LOOKUP_TABLE and INTEGRATION_PROCESS_ACTIVATION tables.

Setting Error Handling for the integration

Setup error notification

Configuring Oracle ERP Cloud Financials for General Ledger and Accounts Payable

GL accounts (Accounting Flexfield) and other information has to be configured in Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable.

This section includes configuration information about the following integration points:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

Refer to the product-specific user documentation for steps to configure GL.

GL Integration Point

Refer to the Oracle Enterprise Resource Planning Cloud Financials for General Ledger and Accounts Payable documentation for steps to configure the Accounting and respective segments, as well as other GL definitions and settings (Ledger) required for the GL integration point.

AP Request Integration Point

This section includes the following:

•

Configuring the Payment Terms in Oracle ERP Cloud Financials for General Ledger and Accounts Payables

•

Configuring the Invoice Source in Oracle ERP Cloud Financials for General Ledger and Accounts Payables

•

Configuring the Journal Source in Oracle ERP Cloud Financials for General Ledger and Accounts Payables

•

Configuring the Journal Category in Oracle ERP Cloud Financials for General Ledger and Accounts Payables

•

Configuring the Lookup for Multi-Org setup in Oracle ERP Cloud Financials for General Ledger and Accounts Payables

Refer to the Oracle ERP Cloud Financials for General Ledger and Accounts Payables documentation for information about how to configure payment terms, invoice source, journal source, journal category, and lookup for multi-org setup.

AP Data Integration Point

Refer to the Oracle ERP Cloud Financials for General Ledger and Accounts Payable documentation for this integration point to send the InvoiceStatus outbound from ERP.

Configuring Oracle Utilities Customer Care and Billing

To configure the Oracle Utilities Customer Care and Billing portion of the integration, settings for all the three integration points must be defined:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

Refer to the user documentation for specific instructions in Oracle Utilities Customer Care and Billing.

GL Integration Point

To enable the GL integration point, configure the following in Oracle Utilities Customer Care and Billing:

•

Configuring GL Division

•

Configuring Distribution Codes

•

GL Account String

•

Configuring GLASSIGN and GLS for Oracle Utilities Customer Care and Billing Extract

•

Configuring General Ledger ID Characteristic Type

Configuring GL Division

To integrate the financial transactions for a specific GL Division in Oracle Utilities Customer Care and Billing to ERP Suite, the GL Division has to be identified. It must match the GL Division specified in Integration_Lookup_table.

Configuring Distribution Codes

The distribution codes in Oracle Utilities Customer Care and Billing need to be mapped to appropriate GL Accounts in the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL. First, configure the distribution codes, and then, assign them to various entities within Oracle Utilities Customer Care and Billing.

The following table shows a sample configuration of one distribution code. It includes only those fields relevant to the integration.

Field Label

Value

Comment

Distribution Code

Example: R-ELERES

The distribution code to be used for financial transactions of a certain type.

Description

Example: Electric residential revenue

A description of how the distribution code is used.

GL Account Algorithm

GLCNST-DFLT

The standard product, or customer modified, algorithm you use for determining the GL Account String from the distribution code.

GL Account Details

1 of 1

Create at least one set of account details as needed by your chosen algorithm above. Only one is used based on status and effective date.

Effective Date

01-01-1900

The date you wish the following GL Account string to become active and used by the system, and therefore the integration software.

Status

Active

Only active status accounts are used by the product and therefore the integration.

GL Account

01.520.5250.0000.000

Input the GL Account String as explained below.

The distribution code links the GL Account to activities. As in the example above, a bill payment receives the code R-ELERES so that this revenue for electric residential service is applied to the account 01.520.5250.0000.000. The algorithm, GLCNST-DFLT, provides the hook which allows the integration to get the GL Account from the distribution code and recognize it in Oracle ERP Cloud Financials for General Ledger and Accounts Payable to the correct GL Journal entry.

GL Account String

Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL accounts are structured using account segments. These are set up in your existing Oracle ERP Cloud Financials for General Ledger and Accounts Payable system according to your business practices.

Oracle Utilities Customer Care and Billing GL Account positions must be configured to mirror the segments and values in Oracle ERP Cloud Financials for General Ledger and Accounts Payable. The segment positions are fixed in Oracle Utilities Customer Care and Billing so that the first segment is Company, the second segment is Department ID, etc, as shown in the following table.

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Account Column Name

Oracle Utilities Customer Care and Billing Distribution (GL_ACCT) segment position

Company

Position 1

Department

Position 2

Account

Position 3

Sub-Account

Position 4

Product

Position 5

As you configure the segments, separate each with a dot (.).

For example: A sample GL Account string is 01.520.5250.0000.000.

When interpreted by the standard mapping in the product, this GL Account String in the sub ledger equates to the following in the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL:

•

Company - 01

•

Department - 520

•

Account - 5250

•

Sub-Account - 0000

•

Product - 000

Refer to the data mapping table for details about how the segments must be mapped.

Configuring GLASSIGN and GLS for Oracle Utilities Customer Care and Billing Extract

To successfully execute extracts from Oracle Utilities Customer Care and Billing, two processes must be configured with the appropriate batch parameters and set to run on a scheduled basis. These processes can be scheduled using the Oracle Utilities Customer Care and Billing scheduling tool or an enterprise scheduler that meets the open architecture standards used by Oracle Utilities Customer Care and Billing.

Configuring General Ledger ID Characteristic Type

For each CIS Division used in Oracle Utilities Customer Care and Billing, configure a Characteristic Value to have the General Ledger ID to be used in Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL.

To configure the GL ID Characteristics Type, follow these steps:

1.

Navigate to Admin Menu > C > Characteristic Type.

2.

Create a Characteristic Type. The value for this characteristic type stores the value of the Ledger ID in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

3.

Set up the details on the Characteristic Type as follows:

Field Label

Value

Comment

Characteristic Type

GLEDGER

The code associated with your characteristic type.

Description

General Ledger ID

A description of the use for this characteristic type.

Type of Char Value

Predefined Value

No free-form text is allowed, only a predefined set of values.

Allow Search by Char Val

Allowed

Allow Searches

Characteristic Value

xxx

4.

Select the Characteristic Entities tab to associate Characteristic Type with the CIS Division.

Field Label

Value

Comment

Characteristic Entity

CIS Division

This characteristic type can be inserted on a CIS Division.

5.

Navigate to Admin Menu > C > CIS Division.

6.

Attach the Characteristic Type, (created in the steps above) to any CIS Divisions that are used.

Field Label

Value

Comment

CIS Division

Example: CA

The CIS Division to be used.

Description

Example: California

A description of how the CIS Division is used.

Characteristic Tab

Effective Date

Example: 01-01-1900

The date you wish the characteristic type and value to become active and used by the system, and therefore the integration software.

Characteristic Type

GLEDGER

The characteristic type you created above.

Characteristic Value

xxx

The characteristic value created above.

AP Request Integration Point

The AP request integration point includes the following:

•

Language to Fetch Adjustment Description

•

Configuring AP Operating Unit Characteristic Type

•

Configuring the Payment Terms ID Characteristic Type

Language to Fetch Adjustment Description

The language used to fetch the Adjustment Description is obtained from the Account's Main Person's Language Preference.

Configuring AP Operating Unit Characteristic Type

For each CIS Division used in Oracle Utilities Customer Care and Billing, configure a Characteristic Value to have the AP Operating Unit to be used in Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL.

Complete the following configuration in Oracle Utilities Customer Care and Billing to reference the AP Operating Unit corresponding to the CIS Division.

1.

Navigate to Admin Menu > C > Characteristic Type to create a Characteristic Type.The value for this characteristic type stores the value of the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Org ID. In this example, it is ERPORGID. Add the Org ID of Oracle ERP Cloud Financials for General Ledger and Accounts Payable as a Characteristic Value.

2.

Set up the details on the Characteristic Type as follows:

Field Label

Value

Comment

Characteristic Type

ERPORGID

The code associated with your characteristic type. It is used in future steps.

Description

ERP Org ID

A description of the use for this characteristic type.

Type of Char Value

Predefined Value

No free-form text is allowed, only a predefined set of values.

Allow Search by Char Val

Allowed

Allow Searches

Characteristic Value

204

The name of the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Org ID to be used.

Description

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Operating Unit

3.

Select the Characteristic Entities tab to associate the Characteristic Type with the CIS Division.

Field Label

Value

Comment

Characteristic Entity

CIS Division

This characteristic type can be inserted on a CIS Division.

4.

Attach the Characteristic Type (created in the steps above) to any CIS Divisions that are used for AP Request Adjustments. In sample data an example is provided as the CA - CIS Division (navigate to Admin Menu > C > CIS Division).

Field Label

Value

Comment

CIS Division

Example: CA

The CIS Division to be used.

Description

Example: California

A description of how the CIS Division is used.

Characteristic Tab

Effective Date

Example: 01-01-1900

The date you wish the characteristic type and value to become active and used by the system, and therefore the integration software.

Characteristic Type

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Org ID

The characteristic type you created above.

Characteristic Value

Example: 204

The value you gave to the characteristic type created above.

Configuring the Payment Terms ID Characteristic Type

For each CIS Division used in Oracle Utilities Customer Care and Billing, configure a Characteristic Value to have the Payment Terms ID to be used in Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL.

Complete the following configuration in Oracle Utilities Customer Care and Billing:

1.

Navigate to Admin Menu > C > Characteristic Type to create a Characteristic Type.The value for this characteristic type stores the value of the Payment Terms ID in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

2.

Set up the details on the Characteristic Type as follows:

Field Label

Value

Comment

Characteristic Type

TERMS_ID

The code associated with your characteristic type.

Description

Payment Terms ID

A description of the use for this characteristic type.

Type of Char Value

Predefined Value

No free-form text is allowed, only a predefined set of values.

Allow Search by Char Val

Allowed

Allow Searches

Characteristic Value

xxx

Payment Terms ID in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

3.

Select the Characteristic Entities tab to allow the Characteristic Type to be associated with the CIS Division.

4.

Attach the Characteristic Type, created above, to any CIS Divisions that are used for AP Request Adjustments.The table below lists only those fields that are relevant to the integration.

Field Label

Value

Comment

CIS Division

Example: CA

The CIS Division to be used.

Description

Example: California

A description of how the CIS Division is used.

Characteristic Tab

Effective Date

Example: 01-01-1900

The date you wish the characteristic type and value to become active and used by the system, and therefore the integration software.

Characteristic Type

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Org ID

The characteristic type you created above.

Characteristic Value

Example: 204

The value you gave to the characteristic type created above.

AP Data Integration Point

This section includes information about configuring Adjustment Cancel Reason.

Configuring Adjustment Cancel Reason

To create Adjustment Cancel Reason to be used when canceling an adjustment, follow these steps:

1.

Navigate to Admin Menu > A >Adjustment Cancel Reason and create an Adjustment Cancel Reason.

2.

Provide the Cancel Reason and Description.

AP payment data is extracted from Oracle ERP Cloud Financials for General Ledger and Accounts Payable when an AP Request invoice is paid.This data is then translated by the ERPToCCBAPDataBPELProcess integration process and inserted into the Oracle Utilities Customer Care and Billing AP Request that initiated the invoice in the first place.

The CCBCancellationWebService process invokes the Oracle Utilities Customer Care and Billing service, named C1AdjustmentMaintenance, when a payment is canceled in Oracle ERP Cloud Financials for General Ledger and Accounts Payable. The service uses the cancel reason specified in the Integration configuration when canceling the adjustment associated with an AP Request. The sample data cancel reason comes pre-configured as "APVC" (Accounts Payable Void Check) in Oracle Utilities Customer Care and Billing version 2.2.0 and later.

You must verify that the cancel service C1AdjustmentMaintenance is configured and that the Cancel Reason to which it refers is also configured correctly.

Navigate to Admin Menu >X >XAI Inbound Service to verify.

Field Label

Value

Comment

XAI/IWS In Service Name

Adjustment Maintenance

This service is used to change data associated with adjustment transactions.

Description

Adjustment Maintenance for AP Cancel

Long Description

Adjustment Maintenance for AP Cancel

Active

Checked

Active check box checked

Request Schema

C1AdjustmentMaintenance.xsd

Used by BPEL to call this service

Response Schema

C1AdjustmentMaintenance.xsd

Used by BPEL to receive the response from this service

Transaction Type

Update

Service used to update an existing adjustment transaction

Navigate to Admin Menu > X > XAI Dynamic Submission to test this service using XAI Dynamic Submission.

Field Label

Value

Comment

XAI In Service Name

Adjustment Maintenance

This service is used to change data associated with adjustment transactions.

Transaction Type

Update

Cancel

Checked

Cancel check box selected.

Adjustment ID

Example: 078644601179

The key value of the adjustment for which you wish to test the cancel service.

Cancel Reason

Example: APVC

AP Void Check cancel reason. It must be a configured cancel reason.

3.

Click Submit and review the results.

Configuring the Integration Product

At this time, there is no user interface for entering the configuration parameters associated with the integration. Use an approved database access tool to establish the appropriate configuration parameters in the tables and columns listed in this section.

This section includes the following:

•

Setting Configuration Properties

•

Error Handling

Setting Configuration Properties

Set the configurations properties that are used by specific integration processes. Scheduler process configuration is done in INTEGRATION_PROCESS_ACTIVATION table and remaining process configuration is done in INTEGRATION_LOOKUP_TABLE.

Process Activation

The table INTEGRATION_PROCESS_ACTIVATION controls the activation or deactivation of the specific integration points. The initial install defaults all of the START_PROCESS values to 'Y'. Set the START_PROCESS value to 'N' for any given PROCESS_NAME that is not in use.

Set the run frequency to the time interval between integration runs for each of the integration points.

Note : You cannot use APDATA if you do not use AP Request.

PROCESS_NAME

START_PROCESS(Y/N)

RUN_FREQUE NCY (Seconds)

NEXT_RUN_I NTERVAL(System Use)

CCB_ERP_GL

Y

0

0

CCB_ERP_AP

Y

0

0

Process Scheduling

These processes can be scheduled independently or using an enterprise scheduling tool. To schedule the processes independently, schedule the Oracle Utilities Customer Care and Billing processes using standard tools available with Oracle Utilities Customer Care and Billing. Schedule the BPEL processes to the time intervals configured. Each of the main BPEL processes that form this process integration has been designed to check if data that must be extracted exists. If data exists, it is extracted, transformed, and loaded to the target system. If data does not exist, then the process does nothing until the next time it tries again.

For example: Schedule the Oracle Utilities Customer Care and Billing GL processes to run at 6:00 A.M. each weekday. Then, initiate the BPEL GL process at 7:00 AM and have it run every 4 hours to see if data exists.

Run the integration manually by initiating each process using the tools provided with each application. Or, use an enterprise scheduler to initiate all, or some, of the processes mentioned above.

The frequency of the run for each integration point is at the client's discretion. As a default, each integration point runs every two minutes from the time you start them the first time. If data exists the integration is completed for the integration point. Else, the system does nothing and tries again two minutes later.

To extend the time between runs, insert a value of time, in seconds, in the RUN_FREQUENCY column of the INTEGRATION_PROCESS_ACTIVATION table for the appropriate integration point. This column controls the timing of the integration points 'waking up' to see if there is data to integrate. The integration points only check this table every two minutes so you cannot schedule the runs in less than two minute increments. Set the run frequency to the time interval, in 120 second increments, between integration runs for each of the integration points.

PROCESS_NAME

START_PROCESS(Y/N)

RUN_FREQUE NCY (Seconds)

NEXT_RUN_I NTERVAL(System Use)

CCB_ERP_GL

Y

14400

0

CCB_ERP_AP

Y

7200

0

In the above example, the GL integration point looks for data every four hours (14,400 seconds), and the AP Request integration points look for data to integrate every two hours (7200 seconds).

Every two minutes, each integration point checks this table with the following logic:

If START_Process flag is NOT Y do nothing else

If Run Frequency is 0 run the IP

else

If next run interval is =< 0 run the integration point AND set

Next_run_interval = run_frequency else

DO not run the Integration Point, just set Next_run_interval =

Next_run_interval - 120 (the polling interval set in BPEL)

General Ledger (GL)

The following processes must be run in sequence to extract, transform, and load GL transactions from Oracle Utilities Customer Care and Billing to Oracle ERP Cloud Financials for General Ledger and Accounts Payable using ICS.

Product

Process Name

Description

CCB

GLASSIGN

Assigns GL account numbers to the GL details associated with financial transactions by referencing the distribution code that calls the appropriate assignment algorithm.

CCB

GLS

Follows GLASSIGN to create financial transaction (FT) download staging records for all financial transactions that are ready to be posted to the GL.

BPEL

CCBToERPGLBPELPrOcess

Extracts financial transactions from Oracle Utilities Customer Care and Billing into BPEL to be transformed and prepared to invoke ICS Endpoint. ICS invokes Oracle ERP Cloud Financials for General Ledger and Accounts Payable using the ICS Utilities adapter ICS ERP Adapter.

ICS

CCBGLINTEGRATION ERP Endpoint: JournalImportService

Receives the message from BPEL and invokes ICS Utilities Adapter which maps to ICS ERP Adapter and ERP Adapter invokes web service JournalImportService

ERP

Journal Import Process

Reads GL_INTERFACE Interface Table and creates journal entries in the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL. This process can be scheduled or run manually.

AP Request

The following processes must be run in sequence to extract, transform, and load AP Requests from Oracle Utilities Customer Care and Billing to Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

Product

Process Name

Description

BPEL

CCBToERPAPBPELProcess

Extracts AP Requests from Oracle Utilities Customer Care and Billing into BPEL to be transformed and invokes ICS endpoint. ICS invokes Oracle ERP Cloud Financials for General Ledger and Accounts Payable using the ICS Utilities Adapter and ICS ERP Adapter.

ICS

CCBERPAPREQUEST ERP Endpoint: InvoiceInterfaceService

Receives the message from BPEL and invokes ICS Utilities Adapter which maps to ICS ERP Adapter and ERP Adapter invokes web service InvoiceInterfaceService.

ERP

Payables Open Interface Import (APXIIMPT)

Creates Invoices in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

AP Data

Appropriate process must be run sometime after the Oracle ERP Cloud Financials for General Ledger and Accounts Payable in ERP to trigger an outbound message with InvoiceStatus to be sent to ICS. The ICS ERP Adapter receives the request and invokes Oracle Utilities ICS Adapter after mapping the data. The ICS Utilities Adapter invokes the following BPEL process to load AP Data to Oracle Utilities Customer Care and Billing.

Product

Process Name

Description

ICS

CCBERPAPREQUEST ERP Endpoint: InvoiceInterfaceService

ERP sends message to ICS using the ICS ERP Adapter. ICS ERP Adapter maps message to Oracle Utilities ICS Adapter and Oracle Utilities ICS Adapter invokes BPEL service listed below.

BPEL

ERPToCCBAPDataBPE LProcess

Receives all payments and cancellations created during pay cycle processing.

Note : The BPEL processes mentioned above are exposed as standard, stand-alone SOAP web services. They can therefore be invoked as regular web services by the BPEL console or any platform supporting scheduling web service invoke activities. These services do not require any external inputs to run. Industry standard enterprise scheduling tools that support this capability may therefore be used to initiate these processes if desired.

Lookup Table

The database table INTEGRATION_LOOKUP_TABLE contains configurable parameters used in the integration for the GL, AP Data, and AP Request integration points. The values for these parameters must be set to match your configuration of Oracle Utilities Customer Care and Billing and Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

INTEGRATION_KEY

INTEGRATION_VALUE

Comment

CCB.ERP.GL.LEDGER_ID

1

General Ledger ID

CCB.ERP.GL.EMAIL

abc.gl@xyz.com

Enter the e-mail address to be notified if errors occur in the GL integration point.For example: abc.gl@xyz.com

CCB.ERP.GL.CUSTOMGLDATA.ENDPOINT

(space)

Custom GL component end point

CCB.ERP.GL.USER_JE_CATEGORY_NAME

Adjustment

This is journal category.

For example: 'CCB ERP'

CCB.ERP.GL.USER_JE_SOURCE_NAME

Manual

This is the Journal Source.For example: 'CCB ERP'

CCB.ERP.GL. ACTUAL_FLAG

A

To create the Actual Journals.

For example: 'A'

CCB.ERP.GL.GL_DIVISION

US1 or Leave Blank

If this value is blank, then financial transactions associated with all GL divisions in Oracle Utilities Customer Care and Billing are integrated. If this column has a value, then only financial transactions associated with this specific GL Division indicated are integrated.For examples: blank or US1

CCB.ERP.GL.XFORMCCBCOLL.PRE.EXTN.FLAG

false

If set to true, the pre-transformation extension service is invoked at the collection level after retrieving all the FT records from Oracle Utilities Customer Care and Billing and before any transformation is done.

CCB.ERP.GL.AFTERERPCOLLINSERT.POST.EXTN.FLAG

false

If set to true, the posttransformation extension service is invoked after all the record are inserted in the Oracle ERP Suite GL_INTERFACE table.

CCB.ERP.APREQUEST.EMAIL

abc@oracle.com

Enter the e-mail address to be notified if errors occur in the AP Request integration point.For example: abc.ap@oracle.com

CCB.ERP.APREQUEST.INVOICE_SOURCE

CCB

Invoice Source to use when the integration creates invoices in Oracle ERP Cloud.

CCB.ERP.AP.CHAR_TYPE_CD_TERMSID

TERMS_ID

Characteristic Type to store Payment Terms ID of Oracle ERP Cloud. This must match what you documented in step B8.

CCB.ERP.AP.CHAR_TYPE_CD_ORGID

ERPORGID

Characteristic Type to store Oracle ERP Cloud Financials for General Ledger and Accounts Payable AP Operating Unit. This must match what you documented in step B3. For example: ERPORGID

CCB.ERP.APREQUEST.ORG_ID

204

For ERP Cloud, the Org ID will be retrieved from the CIS Division Characteristic value.

CCB.ERP.APREQUEST.PAYMENT_METHOD

CHECK

Specifies the payment method for the invoices created by the integration. The supported value is 'CHECK'. Do not modify this default value.

CCB.ERP.APREQUEST.DEFAULT.HEADER.DESCRIPTION

Refund Request from CCB

Default Header description for Invoices in ERP.

CCB.ERP.APREQUEST.DEFAULT.LINE.DESCRIPTION

Refund Request from CCB

Default Line description for Invoices in Oracle ERP Cloud.

CCB.ERP.APREQUEST.VENDOR.NAME.SUFFIX

ADJ_ID

The column whose value is suffixed to the vendor name when creating a supplier in ERP. Possible values are PER_ID,ACCT_ID, ADJ_ID, SA_ID, AP_REQ_ID.

CCB.ERP.APREQUEST.XFORMCCBCOLL.PRE.EXTN.FLAG

false

If set to true, the pre-transformation extension service is invoked at the collection level after retrieving all the AP Request records from Oracle Utilities Customer Care and Billing and before any transformation is done.

CCB.ERP.APREQUEST.XFORMCCBRECORD.PRE.EXTN.FLAG

false

If set to true, the pre-transformation extension service is invoked at the record level before transforming the AP Request record from Oracle Utilities Customer Care and Billing to Oracle ERP Cloud format.

CCB.ERP.APREQUEST.XFORMERPRECORD.POST.EXTN.FLAG

false

If set to true, the post transformation extension service is invoked at the record level after the record is inserted in the Oracle ERP Cloud Invoice Interface tables.

CCB.ERP.CCB.ADJ.MAINT.URL

http://hostname:port/ouaf/XAIApp/xaiserver/C1AdjustmentMaintenance

For cancellation of an adjustment, place the correct URL here.

CCB.ERP.ADMIN_EMAIL

abc@xyz.com

CCB.ERP.CCB.LANGUAGE.CODE

ENG

Language Code

CCB.ERP.AP.CHAR_TYPE_CD_GLEDGERID

GLEDGER

Characteristic Type to store Ledger ID for ERP Cloud. This must match the step B6.

ERP.CCB.APDATA.EMAIL

Enter the e-mail address to be notified if errors occur in the AP Request integration point. For example: abc.ap@oracle.com

ERP.CCB.APDATA.LASTRUNDTTM

11-02-200810:01:01

Last updated time of BPEL process run. This is used to determine the payment data to be extracted and moved across the integration.

ERP.CCB.CANCEL.CANCEL_REASON

APVC

Valid cancel reason code to be passed to CCB when canceling the adjustment associated with the AP Request.

ERP.CCB.APDATA.XFORMERPPAYMENTSCOLL.PRE.EXTN.FLAG

false

If set to true, the pre-transformation extension service is invoked at the collection level after retrieving all the payment records from ERP and before any transformation is done.

ERP.CCB.APDATA.XFORMERPPAYMENTSRECORD.PRE.EXTN.FLAG

false

If set to true, the pre- transformation extension service is invoked at the record level before transforming the Oracle ERP Cloud payment record from Oracle ERP Cloud to Oracle Utilities Customer Care and Billing format.

ERP.CCB.APDATA.PROCESSPAYMENTINFO.PRE.EXTN.FLAG

false

If set to true, the pre processing extension point is invoked. Base payment and cancellation processing are not invoked.

ERP.CCB.APDATA.PROCESSPAYMENTINFO.POST.EXTN.FLA G

false

If set to true, the post processing extension point is invoked. After the base payment and cancellation processing are invoked, additional processing can be done here.

Note : The INTEGRATION_PROCESS_ACTIVATION and INTEGRATION_LOOKUP_TABLE tables are created in the integration schema, during installation, for the BPEL processes to access these tables.

Error Handling

If errors occur during the main integration processes, they are logged into the Integration Error table (INTEGRATION_ERROR_STORE) and the Mail Notification sub process is invoked.

The integration has an error table in the integration schema that keeps a record of all transactions that have failed either during BPEL processing, including insertion of data in the target system.

When errors are found during data extraction integration process inserts errors into the error table (INTEGRATION_ERROR_STORE). There is no user interface to access this table; however, the MailNotification process, if configured, notifies the user by e-mail of the error and the error details. The layout of the error table INTEGRATION_ERROR_STORE is shown below:

Column

Data Type

SOURCE_SYSTEM

VARCHAR2 (3)

INT_BATCH_NUMBER

NUMBER

INTERFACE_NAME

VARCHAR2 (30)

BPEL_INSTANCE_ID

NUMBER

ERROR_CODE

VARCHAR2 (400)

ERROR_SUMMARY

VARCHAR2 (3000)

ERROR_MESSAGE

VARCHAR2 (3000)

NOTIFIED

VARCHAR2 (1)

LAST_UPDATE_DATE_TIME

DATE

If errors occur during the extraction or load process for any of the integration points, the system logs an error in INTEGRATION_ERROR_STORE. Business data is stored in the ERROR_MESSAGE field of the table, and the information is also included in the notification email.

The e-mail notification sub process reads the information in the error table and sends an e-mail notification, based on settings configured for the integration layer.

Each time the e-mail notification process runs, it picks up records from the integration error table where NOTIFIED = 'N' (meaning that notifications have not yet been sent). It loops through all the distinct INTERFACE_NAME entries and sends one email to the corresponding email address (specific to the interface name) with the error information of all the entries in the table. So, if there are 6 records with INTERFACE_NAME="GL", one e-mail is sent out to the email address corresponding to the GL interface. After the email is sent, the system updates the indicator so that NOTIFIED = 'Y.'

This e-mail contains information about each of the 6 failed GL transactions as below:

•

Subject: "Source System" "Target System" "Interface Name" "Process Instance"

•

Body

•

Source System

•

Integration Batch Number

•

BPEL Instance ID

•

Error Code

•

Error Summary

•

Error Message

Error Notification Setup

To enable email notification for the error handling module, follow these steps:

1.

Login to the Enterprise Manager console.

2.

Expand SOA, and then right-click SOA Infra. From the menu, select SOA Administration, and then click Workflow Properties.

3.

From the drop-down list, select EMAIL.

4.

Enter the Email IDs in the From address field.

Verifying the Implementation

The best way to verify the implementation is to start each application individually, and then manually run the integration points.

This section includes the steps to run the following integration points:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

GL Integration Point

To verify the GL integration point implementation, follow these steps:

1.

Identify the financial transactions in CI_FT table that must be sent to Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL for creating Journal Entries. If needed, generate a bill, adjustment, or payment event to create financial transactions.

2.

Run GLASSIGN to assign the Account Number to the FT in CI_FT.

3.

Run GLS to mark the FTs in the CI_FT table for download. The staging process to create a GL download (GLS) creates a staging record for every financial transaction that is ready for download. This process populates the FT / Batch Process table with the unique ID of all financial transactions to be interfaced to the GL. This process marks each staging record with the batch process ID (defined on the installation record) for the GL interface. It also stamps the current run number for the respective batch control record.

Note : The integration BPEL process uses the information on this staging table to create the consolidated journal entries that are interfaced to your GL. The integration process reads the CI_BATCH table to check for new BATCH_JOB_ID and BATCH_JOB_STAT_FLG.

4.

Run the GLS process.

5.

Invoke the GL integration point process from Enterprise Manager or wait for its next run to occur. The package does the following:

•

Select the FT in the CI_FT table based on the batch code and the run number provided to it by Oracle BPEL Process Manager.

•

Extract and group (summarize) the Financial Transactions (FT) and push them into Oracle BPEL Process Manager.

•

Update the Distribution status to 'D' after extracting the FT and increment the NEXT _BATCH_NBR in the CI_BATCH_CTL table.

•

Data is transformed by the BPEL process and written to the GL journal staging table in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

6.

Use the Oracle ERP Cloud GL Journal Import process to load the GL data into the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL base tables.

AP Request Integration Point

To verify the AP Request integration point implementation, follow these steps:

1.

Create an AP Request for a refund customer in Oracle Utilities Customer Care and Billing. Generate an adjustment of the appropriate type to do this.

2.

Run the GLASSIGN process to assign the Account Number to FT in CI_FT.

3.

Invoke the AP Request integration point to extract the AP Request information and the corresponding customer information from Oracle Utilities Customer Care and Billing, transform it, and load it into Oracle ERP Cloud Financials for General Ledger and Accounts Payable AP Invoice Interface tables.

4.

Run the Payables Open Interface Import (APXIIMPT) in Oracle ERP Cloud Financials for General Ledger and Accounts Payable to create Invoices from the AP Check Request and Customer data that is staged in the Invoice Interface tables.

AP Data Integration Point

To verify the AP Data integration point implementation, follow these steps:

1.

Generate a payment in Oracle ERP Cloud Financials for General Ledger and Accounts Payable for an Invoice created by the Oracle Utilities Customer Care and Billing AP Request process. (For relevant information, refer to AP Request Integration Point .)

2.

Invoke the AP Data integration point to update the AP Check Request table (CI_ADJ_APREQ) with the Payment Information from Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

3.

To further test a cancellation of payment functionality, cancel the Payment made in step 1 in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

4.

Invoke the integration process to update the AP Check Request table (CI_ADJ_APREQ) with the Payment Information from Oracle ERP Cloud Financials for General Ledger and Accounts Payable. This cancels the AP Request and adjustment.

Monitoring and Troubleshooting

If the integration is configured properly and the data is entered correctly into Oracle Utilities Customer Care and Billing and Oracle ERP Cloud Financials for General Ledger and Accounts Payable, you must not experience errors related to the integration.

This chapter addresses some common scenarios which may produce errors and offer possible solutions toward error resolution. It provides information on the following:

•

Monitoring from Oracle Utilities Customer Care and Billing

•

Monitoring from Oracle ERP Cloud

•

Monitoring from Oracle ICS

•

Monitoring from SOA Integration

•

Troubleshooting

Monitoring from Oracle Utilities Customer Care and Billing

Errors related to the online integration invocation from Oracle Utilities Customer Care and Billing are stored in the CCB_ENVIRONMENT_NAME/logs/system folder.

For example: V24_V24020_CCB_SOA12C_CERT_LIN_ORA_WLS /logs/system

Errors related to batch integration invocation from Oracle Utilities Customer Care and Billing are stored in the $SPLOUTPUT/ CCB_ENVIRONMENT_NAME folder.

For example: /spl/sploutput/V24020_CCB_SOA12C_CERT_LIN_ORA_WLS

Below is a sample scenario involving monitoring from Oracle Utilities Customer Care and Billing.

Any error occurs when GLASSIGN or GLS batches are run are reported in the Oracle Utilities Customer Care and Billing product batch run tree. Correct the underlying condition causing the error and then rerun the batch processes. The rest of the integration cannot occur until the two Oracle Utilities Customer Care and Billing batch processes have successfully completed.

For more information about errors and notifications, see the Oracle Utilities Customer Care and Billing documentation.

Monitoring from Oracle ERP Cloud

Any error that occurs during the execution of the journal generator process are reported in Oracle ERP Cloud Financials for General Ledger and Accounts Payable process monitor. The monitor shows the status of the process and an error log.

Monitoring from Oracle ICS

Use the Integration Cloud Service dashboard to see how the integrations are performing. The dashboard provides multiple views to check the services running at any point in time.

The Integration Cloud Service dashboard helps you to see how the running integrations are processing messages, such as the number of messages received and processed, the number of successful messages and errors occurred, and the overall success rate.

To monitor integrations, follow these steps:

1.

On the Integration Cloud Service tool bar, click Monitoring. The Monitoring dashboard is displayed.

2.

In the navigator, click Integrations. A list of running integrations appears, along with processing information about the number of messages received, the number of messages processed, the number of successful messages, and the number of failed messages.

3.

From the Integrations list, select the time period for which to display integration information (for example, one hour, six hours, one day, two days, three days, or since the first activation).

Monitoring from SOA Integration

This section describes the utilities used (any of these) to monitor the integration.

•

Monitoring Using WebLogic SOA Enterprise Manager

•

Monitoring Using WebLogic Logs

•

Monitoring Using Integration Error Store Table

Monitoring Using WebLogic SOA Enterprise Manager

To monitor the integration using WebLogic SOA Enterprise Manager, follow these steps:

1.

Login to the WebLogic SOA Server Enterprise Manager console, and then navigate to SOA > soa-Infra > CCB-ERP.

All composite processes deployed for the integration are available under the partition CCB-ERP.

2.

Select the appropriate process to list all the instances for the processes sorted by time of execution.

3.

Click the appropriate process instance and it displays the process.

The composite process lists all activities in the process instance.

Monitoring Using WebLogic Logs

To monitor using the WebLogic logs, follow these steps:

1.

Login to the machine where the SOA server is installed.

2.

Navigate to where the SOA logs are stored. They are in: <Weblogic installation folder>/user_projects/domains/<SOA Domain name>/servers/<SOA server name>/logs.

For example: /Oracle/Middleware/Oracle_Home/user_projects/domains/soa_domain/servers/soa_server1/logs

Monitoring Using Integration Error Store Table

Errors occurring during the integration (such as when financial transactions are extracted or summarized, when data formats are translated, or when data is inserted into one of the edge applications) are logged and reported by the integration product in the INTEGRATION_ERROR_STORE table.

Use standard database (SQL based) tools to view the error information in the table if necessary. However, the e-mail notification you receive must include all the information necessary to investigate and correct the error.

If errors occur during the extraction or load process for any of the integration points, the system logs an error in INTEGRATION_ERROR_STORE. Business data is stored in the ERROR_MESSAGE field of the table, and the information is also included in the notification email.

The following sections indicate where to locate error messages and logged error data in each of the integration points:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

GL Integration Point

If errors occur during the extraction of Financial Transactions from Oracle Utilities Customer Care and Billing tables or during loading these transactions into Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL_INTERFACE table, integration process inserts the error into INTEGRATION_ERROR_STORE.

The following business data is stored in the ERROR_MESSAGE field of INTEGRATION_ERROR_STORE. This information is included in the notification email.

The GL integration point utilizes set based processing. If there is an error, with any part of the batch, the entire batch is rejected.

Table

Column

Data Type

CI_FT_PROC

BATCH_NBR

NUMBER (10)

CI_FT_GL

DST_ID

CHAR (10)

CI_FT_GL

GL_ACCT

VARCHAR2 (48)

CI_FT

CIS_DIVISION

CHAR (5)

CI_FT

GL_DIVISION

CHAR (5)

AP Request Integration Point

If errors occur during the extraction of Financial Transactions from Oracle Utilities Customer Care and Billing tables or during loading these transactions into Oracle ERP Cloud Financials for General Ledger and Accounts Payable Invoice Interface tables, BPEL inserts the error into INTEGRATION_ERROR_STORE.

The following business data is stored in the ERROR_MESSAGE field of INTEGRATION_ERROR_STORE. This information is included in the notification email.

Table

Column

Data Type

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

BATCH_NBR

NUMBER (10)

ENTITY_NAME

VARCHAR2 (64)

SCHEDULED_PAY_DT

DATE

CI_ADJ

ADJ_ID

CHAR (12)

CRE_DT

CHAR (12)

ADJ_TYPE_CD

CHAR (8)

ADJ_AMT

NUMBER (15,2)

CI_SA

CIS_DIVISION

CHAR (5)

AP Data Integration Point

The following business data is stored in the ERROR_MESSAGE field of INTEGRATION_ERROR_STORE. This information is included in the notification email.

Table

Column

Data Type

AP_CHECKS_ALL

CHECK_NUMBER

NUMBER (15)

CHECK_ID

NUMBER (15)

CHECK_DATE

DATE

AMOUNT

NUMBER

BANK_ACCOUNT_NUM

VARCHAR2 (50)

AP_INVOICES_ALL

INVOICE_NUM

VARCHAR2 (50)

PAYMENT_REASON_COMMENTS

VARCHAR2 (240)

INVOICE_ID

NUMBER (15)

VENDOR_ID

NUMBER (15)

VENDOR_SITE_ID

NUMBER (15)

Troubleshooting

At times, the CCB-ERP BPEL flows or the ICS integration might experience errors or issues with connection, processing, or sending or receiving messages. Following are the common scenarios which help you to troubleshoot the error, if any, and find possible solutions.

Troubleshooting CCB-ERP BPEL Flows

The table below lists the error scenarios which may occur, and the steps to resolve them. Usually when errors occur, rectify the configuration settings, data errors, or both.

•

The General Ledger Integration uses set based processing. This means that either all or none of the transactions in a batch are successful.

•

The AP Request Integration uses row-by-row processing.

•

The AP Data Integration uses row-by-row processing.

Error

Interface Name

Application/Process

Error Details

Error Resolution

GL Account mapping inconsistency

GL Integration Point

CCB

When the Journal Voucher is created in Oracle ERP Cloud Financials for General Ledger and Accounts Payable, the Accounting information is incorrect.

The source of truth is Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL. Hence, correct the Accounting Structure in the Oracle Utilities Customer Care and Billing distribution code using information from the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL.

Incorrect GL Operating Unit

GL Integration Point

CCB

The financial information being sent to Oracle ERP Cloud Financials for General Ledger and Accounts Payable has the wrong Operating Unit associated with it.

Correct the GL Division setup in Oracle Utilities Customer Care and Billing to match the GL Operating Unit in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

Data in AP Request row and BPEL process does not fail.

AP Request Integration Point

CCB

If a particular AP Request has an error in Oracle Utilities Customer Care and Billing, it is not picked by the integration process but the remaining requests of that BPEL run are picked up and inserted into the interface tables and the BPEL process status is successful.If one or more rows have failed at any point in the integration, the information is logged in the integration log table and an error email is generated.

Correct the specific AP Request in error using the tools provided by Oracle Utilities Customer Care and Billing. Then, rerun the BPEL integration process.

Data successfully inserted in Oracle ERP Cloud Financials for General Ledger and Accounts Payable interface table, but data has errors.

GL Integration Point

ERP

If the integration process completes successfully and data is inserted into the Oracle ERP Cloud Financials for General Ledger and Accounts Payable interface tables, but the data has errors in it, the Journal Generator process may not be able to process the data and create journal vouchers from it.

Correct the information directly in Oracle ERP Cloud Financials for General Ledger and Accounts Payable and load the journal voucher using the online tools provided in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

Journal Generator process cannot complete successfully.

GL Integration Point

ERP

When the Journal Generator process encounters errors, the error status/reason associated with the Journal Generator process is also identified in the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Process monitor. All the rows in the interface table remain unprocessed and the Distribution Status remains unchanged as 'N'.

Correct the information directly in Oracle ERP Cloud Financials for General Ledger and Accounts Payable and load the journal voucher using the online tools provided in Oracle ERP Cloud Financials for General Ledger and Accounts Payable.

Journal Generator process completes successfully with bad data.

GL Integration Point

ERP

In this instance, journals are created for the row of bad data, which can be detected and rectified by viewing, editing, and loading the journal online.

Correct the information directly in Oracle ERP Cloud Financials for General Ledger and Accounts Payable and load the journal voucher using the online tools provided in Oracle ERP Cloud Financials for General Ledger and Accounts Payable. After the process is successfully completed, the Distribution Status of all the rows in the Interface table is updated to 'D'.

Row of bad data in Oracle ERP Suite Financials for General Ledger and Accounts Payable interface table does not get picked up.

GL Integration Point

ERP

The Journal Generator process does not error out and the row of bad data in the Oracle ERP Suite Financials for General Ledger and Accounts Payable interface table does not get picked up. This situation can occur if the Accounting Date lies outside the Open Period.

Correct the Accounting Date manually in Oracle ERP Cloud Financials for General Ledger and Accounts Payable. After the process has completed successfully, the Distribution Status of the row still remains in 'N'.(does not change to 'D').

Error Data in Oracle ERP Cloud Financials for General Ledger and Accounts Payable staging table and the process fails.

AP Request Integration Point

ERP

All the data is successfully inserted into the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Interface tables, but there is an error while running the Invoice build process.

Load the Invoices directly into Oracle ERP Cloud Financials for General Ledger and Accounts Payable and resolve any incorrect data.

Error Data in Oracle ERP Cloud Financials for General Ledger and Accounts Payable staging table and the process does not fail.

AP Request Integration Point

ERP

Oracle ERP Cloud Financials for General Ledger and Accounts Payable Invoice Build process ends successfully but the Invoices are in recycle status.

Load the Invoices directly in Oracle ERP Cloud Financials for General Ledger and Accounts Payable and resolve any incorrect data.

System or Network Down

Any integration point

Integration BPEL Process

If server goes down in the middle of an integration process.

If server goes down in the middle of a long running process, it can be restarted and will resume where it went down. A retry policy can be set up in the Oracle BPEL Process Manager which administratively enables BPEL process instances to retry adapter connectivity.

Data failed to insert in the Oracle ERP Cloud Financials for General Ledger and Accounts Payable interface table.

GL Integration Point

Integration BPEL Process

If one row fails to insert into the Oracle ERP Cloud Financials for General Ledger and Accounts Payable Interface table during a batch, the entire batch rolls back. In this instance, the BPEL process shows a status of error and an error notification is sent via email.

5.

Re-establish the connections between BPEL and edge applications, if necessary.

6.

Correct the configuration and/or transactional data in the Oracle Utilities Customer Care and Billing database, if necessary.

7.

Ensure that the GLASSIGN and GLS processes are run again.

The integration process must also be re-run after the above mentioned actions are taken.

GL Account Mapping inconsistency

GL Integration Point

Integration BPEL Process

When the Journal Voucher is created in Oracle ERP Cloud Financials for General Ledger and Accounts Payable, the Accounting information is incorrect.

Since the source of truth is Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL, the user needs to correct the Accounting Structure in the Oracle Utilities Customer Care and Billing distribution code using information from the Oracle ERP Cloud Financials for General Ledger and Accounts Payable GL.

The integration is unable to update the AP Request table with payment information.

AP Data Integration Point

Integration BPEL Process

It is likely that the error is technical in nature (data mapping etc).

1.

Review BPEL error table and product error logs.

2.

Update BPEL and/or product configurations as needed to correct the errors reported.

3.

Re-run the integration after the corrections have been made.

The integration is unable to invoke the Adjustment Maintenance Service.

AP Data Integration Point

Integration BPEL Process

It is likely that the error is technical in nature (service retired etc).

1.

Review BPEL error table and product error logs.

2.

Update BPEL and/or product configurations as needed to correct the errors reported.

3.

Re-run the integration after all corrections have been made.

Troubleshooting ICS Integration

The table below lists the error scenarios which may occur, and the steps to resolve them.

Error

Error Details

Error Resolution

WSIF JCA Execute of operation 'createInvoiceInterface' failed due to: Exception occurred while reading remote WSDL.

ERP is down.

1.

Navigate to the Connections page in ICS.

2.

Test UGBUERPCONNECTION. The connection fails.

3.

Bring up the ERP.

WSIF JCA Execute of operation 'createInvoiceInterface' failed due to:

Access denied ("oracle.wsm.security.WSFunctionPermission" "http://xmlns.oracle.com/apps/financials/payables/invoices/quickInvoices/invoiceInterfaceService/InvoiceInterfaceService#crea "invoke")

The current user in UGBUERPCONNECTION does not have access to the resource.

1.

Either grant the access to this user or update the Connections page with a user who has access.

2.

Save the connection.

Execute of operation 'initiate' failed due to:

"https://hostname:portnumber/integration/flowsvc/oracleutilities/integrationname/v01/" successfully due to: javax.xml.soap.SOAPException: javax.xml.soap.SOAPException: Message send failed: Missing WWW-Authenticate header.

Policy issue

1.

Navigate to the Integrations page in ICS.

2.

Open the endpoint URL for integration.

3.

Ensure that the policy configured in the endpoint WSDL for integration and the policy configured in the SOA composite is same.

Error 401-Unauthorized

Error: Security policy enforcement failed: : OWSM ICS Service request handler failed

Incorrect user name and password

The user name and password provided while triggering the ICS URL are incorrect. Resubmit the request with the correct username/password combination,

The server encountered an unexpected condition which prevented it from fulfilling the request.

Server unable to handle request for URI [/integration/flowsvc/erp/flowname/v01/index.html]. Either the proxy-service is turned off, or no endpoint was found to match the request.

ICS Integration is not Activated

Activate the integration in ICS and re-submit the request.

Error getting response;HttpHostConnectionException.

Connection to https://hostname:portnumber refused

ICS server is down

Bring up the ICS server and re-send the request.

WSIF JCA Execute of operation failed due to:

JBO-GL:::GL_ADFDI_INVALID_ACCT_COMB: &lt;MESSAGE&gt;&lt;NUMBER&gt;GL-780436&lt;/NUMBER&gt;&lt;TEXT&gt;

You must enter a valid account combination

Invalid ERP account number configured in CCB.

Configure the correct account number in CCB and retry the request.

Customization Options

This chapter provides information on the various methods that can be used to extend or customize the integration, including:

•

Extension Methods

•

Customizing SOA Composite Applications

Extension Methods

The integration process allows the extensibility of transaction messages using the following methods:

•

Custom Transformations

•

Customizable Scopes

The integration layer defines custom transformations and customizable scopes that allow the implementation team to add new mappings and also add their custom code in specific scopes respectively.

Custom Transformations

This integration has placeholders for custom elements in the incoming schema and outgoing schema at record level. When querying data into incoming message, the custom elements will be empty. It can be populated through the extension points.

The custom transformations have a standard template to map elements to existing fields that are still unmapped and a custom template to map custom elements. The main transformation invokes custom transformation. Empty custom transformations are shipped with the product.

The custom elements in the target variable are not passed to the database adapter but they are passed to post collection extension point.

Customizable Scopes

The integration layer provides an option to customize the composite at each extension point. These custom scopes are empty by default.

In order to customize a composite or add code in these custom scopes, login into JDeveloper using the ‘Customization Developer’ role. Only then you can customize the composite.xml file, .bpel file (for Oracle BPEL Process Manager), .xsl map file, and .mplan file (for Oracle Mediator).

For example: bpel can be customized at scopes which has "customizable=‘true’".

Refer to the Customizing SOA Composite Applications section for more details.

Note : For more information, refer to the SOA documentation at:http://docs.oracle.com/middleware/12212/soasuite/develop/GUID-46083A5B-B61C-41BA-A9EE-5CEE758BC7C7.htm#SOASE85064.

Customizing SOA Composite Applications

This section provides a summary of the steps required to customize SOA composite applications:

1.

Obtain the Composite Archive (SAR) file for the base composite to be customized. This SAR file may be obtained in any of the following ways:

•

If the composite has already been installed and deployed as part of a process integration pack (PIP), the composite project may be found under the CCB-ERP\services\industry\Utilities\EBF\CCBToERPGLBPELProcess/... directory tree and within the project's deploy subdirectory you may find the SAR file.

•

If the composite has already been deployed, you can export the SAR from the server using EM console or WLST or Ant commands

•

Open the project in JDeveloper (default role) and deploy it to a SAR file.

2.

Open JDeveloper (default role) and create a new SOA application. Then, create a new SOA project with an empty composite. The SOA project should be named with a distinguishing prefix (such as "XX") followed by the original project or composite name. For example: XXCCBToERPGLBPELProcess

3.

In the Application Navigator pane, click the project name to select it.

4.

Select File > Import... from the main JDeveloper menu.

5.

Choose SOA Archive Into SOA Project.

6.

Browse for the SAR file obtained in step 1.The composite name is populated automatically after selecting the SAR file. Verify that it is correct.

7.

Select the Import for Customization checkbox, and click Finish. The project is now ready for customization.

Note : In case of any compilation errors (such as MDS-00054 : MDS Exception), ensure adf-config.xml has the MDS database (where CCB-ERP integration is deployed) details.

8.

Add the customization class jar "ugbucust.jar" to the SOA composite project. This file is located in $PRODUCT_HOME/install/util/lib/ugbucust.jar.

9.

Configure the SOA application in JDeveloper to use the customization class and layer:

a.

In the Applications window, expand Application Resources > Descriptors > ADF META_INF.

b.

Open the adf-config.xml file and select the MDS tab.

c.

Click Add to add the "UGBUCustomerExtensionCustomizationClass" customization class.

d.

To add application-specific layer values, click the Configure Design Time Customization Layer Values link.

e.

Add the below snippet to add "UGBUCustomizationLayer" value in CustomizationLayerValues.xml.

<cust-layers xmlns="http://xmlns.oracle.com/mds/dt">

<cust-layer name="UGBUCustomizationLayer" id-prefix="ugbuext">

<cust-layer-value value="UGBUCustomizationLayer" display-name="UGBU Customer Extension"/>

</cust-layer>

</cust-layers>

10.

Save all the changes.

11.

From the Tools menu, select Switch Roles > Customization Developer.

12.

Restart Oracle JDeveloper.The Customization Context dialog displays the available customization layers and layer values.

13.

Select "UGBUCustomizationLayer" layer and value to customize.

14.

Customize the BPEL process. Make required changes to the composite and its BPEL components.

Note : Only scopes that have been marked as customizable in BPEL are editable. Non-editable activities appear greyed out.After the customizations are complete, the project can be deployed to the SOA server and/or a SAR file.

For more information about implementing custom SOA composites, refer to section 52.4 Customizing the Customer Version in the SOA documentation at: http://docs.oracle.com/middleware/12212/soasuite/develop/GUID-46083A5B-B61C-41BA-A9EE-5CEE758BC7C7.htm#SOASE85064.

Preface

This document is intended for anyone implementing the CCB-ERP Integration Using Oracle Utilities ICS Adapter and ERP ICS Adapter.

Documentation and Resources

For more information regarding this integration, foundation technology and the edge applications, refer to the following documents:

Product Documentation

Topic

Description

Integration documentation:

CCB-ERP Integration Using Oracle Utilities ICS Adapter and ERP ICS AdapterInstallation Guide

CCB-ERP Integration Using Oracle Utilities ICS Adapter and ERP ICS AdapterImplementation Guide

http://docs.oracle.com/cd/E72219_01/documentation.html

Oracle Utilities ICS Adapter documentation

https://docs.oracle.com/cloud/latest/intcs_gs/ICSUT/toc.htm

Oracle Utilities Customer Care and Billing and Oracle Oracle Enterprise Resource Planning Cloud Financials documentation

http://docs.oracle.com/cd/E72219_01/documentation.html

Additional Documentation

Resource

Location

SOA Suite 12c documentation

Refer to the SOA documentation at: http://www.oracle.com/technetwork/middleware/soasuite/documentation/index.html

Oracle Support

Visit My Oracle Support at https://support.oracle.com regularly to stay informed about updates and patches.

Access the support site for the Edge Application Certification Matrix for Oracle Utilities Products (Doc ID 1454143.1) or refer to the Oracle Utilities Integrations page at http://my.oracle.com/site/tugbu/productsindustry/productinfo/utilities/integration/index.htm

Oracle Technology Network (OTN)

Latest versions of documents

http://www.oracle.com/technetwork/index.html

Oracle Universityfor training opportunities

http://education.oracle.com/

Web Services Security

For more information about Web services security using Oracle Fusion Middleware 12c refer to https://docs.oracle.com/middleware/12211/cross/webservicestasks.htm.

Oracle Fusion Middleware 12c documentation

http://docs.oracle.com/en/middleware/

Oracle Fusion Middleware "What's New In Oracle WebLogic Server"

Section: Standards Support, Supported Configurations and WebLogic Server Compatibility, Database Interoperability

For additional information on the type of database to use.

http://docs.oracle.com/middleware/1221/wls/NOTES/whatsnew.htm#NOTES570

Instructions on installing this integration on non-Windows/ Linux platforms

Refer to Oracle Support Knowledge Article ID 1349320.1.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support for the hearing impaired. Visit: http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

Abbreviations

The following table lists the commonly used abbreviations in this guide.

Abbreviation

Definition

AIA

Application Integration Architecture

AP

Accounts Payable

AP Data

Accounts Payable Data

AP Request

Accounts Payable Request

BPEL

Business Process Execution Language

DVM

Domain Value Map

EBF

Enterprise Business Flow

EM

Enterprise Manager

ERP

Oracle ERP Cloud

FT

Financial Transactions

GL

General Ledger

ICS

Integration Cloud Service

MDS

Meta Data Store

OUCCB or CCB

Oracle Utilities Customer Care and Billing

SOA

Service Oriented Architecture

Part 1

Understanding the Integration

This section provides an overview of the participating applications and information regarding the business processes addressed by the integration.

The section includes the following chapters:

•

Introduction

•

Understanding the Integration Process

Part 1 - Understanding the Integration

Part 2

Implementing the Integration Product

This section provides details about how to configure the participating applications and middle layer for the integration. It also includes information on error handling, monitoring, customization options, and data mapping.

The section contains the following chapters:

•

Configuring the Integration

•

Monitoring and Troubleshooting

•

Customization Options

Part 2 - Implementing the Integration Product