Oracle Utilities Customer Care and Billing Integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payable

Release 3.1.0

Implementation Guide

E21391-02

October 2015

Oracle Utilities Customer Care and Billing Integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payable Implementation Guide, Release 3.1.0

E21391-02

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

Primary Author: Oracle Corporation

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, 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 on 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. 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.

Overview

This document provides configuration and administration information for the integration between Oracle Utilities Customer Care and Billing and PeopleSoft Financials for General Ledger (GL) and Accounts Payable (AP).

•

Additional Resources

•

Prerequisites

•

About the Integration Product

•

Supported Business Processes

•

Process Scheduling

•

Best Practices

1.1

Additional Resources

The following additional resources are available:

Resource

Location

Oracle Utilities Customer Care and Billing Integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payable Installation Guide

Same folder as this document, with the distribution for this product.

Oracle Utilities Customer Care and Billing Integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payable Release Notes

Same folder as this document, with the distribution for this product.

Oracle Utilities Customer Care and Billing Documentation

Refer to Oracle Utilities Customer Care and Billing documentation located on the Oracle Software Delivery Cloud or on the Oracle Technology Network.

https://edelivery.oracle.com/

http://www.oracle.com/technetwork/documentation

PeopleSoft FinancialsDocumentation

Refer to PeopleSoft Financials documentation located on the Oracle Software Delivery Cloud or on the Oracle Technology Network.

https://edelivery.oracle.com/

http://www.oracle.com/technetwork/documentation

Managing BPEL

http://www.oracle.com/technology/documentation/index.html

Note: For specific edge application patch level details, refer to the Oracle Utilities Integrations page at:

http://my.oracle.com/site/tugbu/productsindustry/productinfo/utilities/integration/index.htm

Note : The latest versions of these documents are available on the Oracle Technology Network at http://www.oracle.com/technetwork/index.html

1.2

Prerequisites

All participating applications must be installed, set up, and working properly.

1.3

About the Integration Product

This section provides general information about the functionality and processing of the Oracle Utilities Customer Care and Billing Integration to PeopleSoft Financials for General Ledger and Accounts Payable. This is an AIA Direct Integration using SOA and does not require AIA Foundation Pack to be installed.

1.3.1

About the Products

The following products are involved in the integration:

•

Oracle Utilities Customer Care and Billing

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

1.3.1.1

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: 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 Additional Resources section for current application version details.

1.3.1.2

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

Oracle PeopleSoft Financials for General Ledger and Accounts Payable (PS) is a family of applications in Oracle's PeopleSoft product line. This system helps to automate and standardize business processes by providing a single repository of physical and financial data on an organization's equipment and assets. This information can then be incorporated into the budgeting and planning process. Accounts Payable, Accounts Receivable, and General Ledger functionality can be used to manage disbursements and collections, monitor credit risks, and capture and access financial information based on configurable requirements.

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

1.3.1.3

Oracle BPEL Process Manager

The Oracle Fusion middleware product, Oracle BPEL Process Manager, coordinates the data flow and data mapping of the integration. Used as an Oracle standard across most integrations, Oracle BPEL Process Manager provides a comprehensive solution for creating, deploying, and managing cross-application business processes with both automated and manual workflow steps. It supports standards such as BPEL, XML, XSLT, XPATH, JMS, JCA, and Web Services.

1.4

Supported Business Processes

The Oracle Fusion Middleware product, Oracle BPEL Process Manager, coordinates the data flow and data mapping of the integration. Used as an Oracle standard across most integrations, Oracle BPEL Process Manager provides a comprehensive solution for creating, deploying, and managing cross-application business processes with both automated and manual workflow steps. It supports standards such as BPEL, XML, XSLT, XPATH, JMS, JCA, and Web Services:

Business Process

Description

A bill is created/ cancelled

The general ledger is updated with journal information.

A payment is created/ cancelled

An adjustment is created/ cancelled

An adjustment whose type indicates AP Request is created

Customer and AP Request information is used to create a one-time supplier and supplier site. An account payable voucher is created and associated with this supplier and supplier site.

Oracle JD Edwards

Oracle Utilities Customer Care and Billing

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

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

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

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

The AP request and its associated adjustment are cancelled.

The following diagram provides a visual representation of these processes:

Integration Points

1.5

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.

1.6

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 PeopleSoft Financials for General Ledger and Accounts Payable.

•

One-Time Configuration Settings

•

General Ledger Integration

•

Accounts Payable (AP) Request and AP Data Integrations

Note : Information on how to configure settings that are specific to the integration is provided in the chapter titled Configuring the Integration . Refer to product specific documentation for information on how to complete product specific configuration tasks.

1.6.1

One-Time Configuration Settings

Some one-time configuration settings must be coordinated manually to ensure proper results from the movement of data between Oracle PeopleSoft Financials for General Ledger and Accounts Payable and Oracle Utilities Customer Care and Billing.

General Ledger Configuration

Oracle PeopleSoft Financials for General Ledger and Accounts Payable General Ledger is the overriding source 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 Oracle PeopleSoft Financials for General Ledger and Accounts Payable 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 should not be integrated to your General Ledger you should configure a separate General Ledger Division for these. You should then configure the integration product to distinguish which General Ledger Division should be integrated with the General Ledger (it ignores all others).

Note : Refer to the Implementation Guide, Oracle Utilities Customer Care and Billing documentation, or Oracle PeopleSoft Financials for General Ledger and Accounts Payable documentation for more information on any of the configuration settings referenced in this section.

1.6.2

General Ledger Integration

Oracle PeopleSoft Financials for General Ledger and Accounts Payable General Ledger accounts is structured using account segments. These are set up in your existing Oracle PeopleSoft Financials for General Ledger and Accounts Payable configuration according to your business practices. Oracle Utilities Customer Care and Billing distribution codes must be configured to mirror the segments in Oracle PeopleSoft Financials for General Ledger and Accounts Payable. The segment positions are separated by dots '.' in Oracle Utilities Customer Care and Billing so that the first segment is Account, the second segment is Department ID, and so on.

Create your chart of accounts in the Oracle PeopleSoft Financials for General Ledger and Accounts Payable General Ledger then set up your Oracle Utilities Customer Care and Billing distribution codes to map to the General Ledger account structure using dot separators.

Do not become confused by the dual use of the word account, for the Oracle PeopleSoft Financials for General Ledger and Accounts Payable general ledger accounts and the Oracle Utilities Customer Care and Billing customer billing accounts. A customer billing account is simply 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 PeopleSoft Financials for General Ledger and Accounts Payable accounting definition of account (General Ledger Account). The Oracle Utilities Customer Care and Billing distribution code translates to the Oracle PeopleSoft Financials for General Ledger and Accounts Payable general ledger account.

The following sections cover:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable General Ledger Settings

•

Oracle Utilities Customer Care and Billing General Ledger Settings

•

Integration Software General Ledger Settings

•

Accounting

1.6.2.1

Oracle PeopleSoft Financials for General Ledger and Accounts Payable General Ledger Settings

Configure General Ledger settings in Oracle PeopleSoft Financials for General Ledger and Accounts Payable according to the following guidelines and keeping in mind that Oracle PeopleSoft Financials for General Ledger and Accounts Payable is the overriding source for the general ledger.

•

Journal Generator Process : Schedule the Journal Generator process to create journal vouchers from Oracle Utilities Customer Care and Billing information inserted into interface tables by the integration software. When you configure Oracle PeopleSoft Financials for General Ledger and Accounts Payable to run this process automatically at a preset time please ensure you have matched this timing with the timing of other actions completed by Oracle Utilities Customer Care and Billing and the integration product. Alternatively you may use the standard user interface within Oracle PeopleSoft Financials for General Ledger and Accounts Payable to run the Journal Generator process manually.

•

Accounting Entry Definition : If not already configured, pre-configure an accounting entry definition in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to indicate the staging table where incoming accounting entries should be stored, and the mapping from staging table fields to active chartfields. Ensure that all mandatory fields on the staging tables are mapped.

•

Journal Generator Template : If not already configured, pre-configure a Journal Generator Template in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to set the journal processing defaults for incoming Oracle Utilities Customer Care and Billing data.

•

The integration software relies on other Oracle PeopleSoft Financials for General Ledger and Accounts Payable configuration information including: Business Unit, Calendars, Ledger groups and Ledger. These are normally already configured as part of your implementation. If these do not already exist please configure them for the Integration software to run correctly.

1.6.2.2

Oracle Utilities Customer Care and Billing General Ledger Settings

Configure General Ledger settings in Oracle Utilities Customer Care and Billing according to the following guidelines and keeping in mind that Oracle PeopleSoft 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 not previously integrated to the General Ledger 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 please ensure you have matched this timing with the timing of other actions done by Oracle PeopleSoft 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).

•

Verify that the Oracle Utilities Customer Care and Billing General Ledger Division matches the General Ledger Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

•

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

1.6.2.3

Integration Software General Ledger Settings

As part of the technical configuration of this integration product you must indicate certain values to the product so that it can properly extract data from one application, transform it, and insert it for use by the other application. The following information must be configured:

•

Email address of the person who should notified if the integration software detect and logs an error while performing the integration.

•

General Ledger line description to use for journal voucher lines in Oracle PeopleSoft Financials for General Ledger and Accounts Payable that came from Oracle Utilities Customer Care and Billing through the integration.

•

The Journal ID, General Ledger, and Ledger group to use for journal vouchers in Oracle PeopleSoft Financials for General Ledger and Accounts Payable that came from Oracle Utilities Customer Care and Billing through the integration.

•

If you enter a Oracle Utilities Customer Care and Billing GL Division name in the BPEL configuration table then only financial transactions associated with this GL Division are extracted for integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL. NOTE: Leave this configuration information blank if you want all Oracle Utilities Customer Care and Billing financial transactions to be integrated to Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL.

Note : If all Oracle Utilities Customer Care and Billing financial transactions should come across the integration then do not name any General Ledger Divisions in the integration product configuration table.

1.6.2.4

Accounting

The following 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

1.6.3

Accounts Payable (AP) Request and AP Data Integrations

This section covers:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Settings

•

Oracle Utilities Customer Care and Billing AP Settings

•

Integration Software AP Settings

•

Accounting

1.6.3.1

Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Settings

Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Voucher Build Application Engine Process (AP_VCHRBLD) is run as scheduled to read the data from the AP Voucher Build Interface tables and create single payment vouchers corresponding to the Oracle Utilities Customer Care and Billing AP Requests.

Oracle PeopleSoft Financials for General Ledger and Accounts Payable single payment vendors are used to identify standard customer related information used for all AP Requests coming from Oracle Utilities Customer Care and Billing. These must be setup in Oracle PeopleSoft Financials for General Ledger and Accounts Payable for the integration to process these transactions correctly.

Configuration needed for AP Request and AP Payment integrations includes:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Voucher Build Application Engine Process : Schedule this process to create AP Vouchers from Oracle Utilities Customer Care and Billing information inserted into interface tables by the integration product software. When you configure Oracle PeopleSoft Financials for General Ledger and Accounts Payable to run this process automatically at a preset time please ensure you have matched this timing with the timing of other actions done by Oracle Utilities Customer Care and Billing and the integration product. (Alternatively you may use the standard user interface within Oracle PeopleSoft Financials for General Ledger and Accounts Payable to run the Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Voucher Build Application Engine Process manually).

•

Single Payment Vendor : Pre-configure a single payment vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to accommodate payments sent to customers within Oracle Utilities Customer Care and Billing. A Oracle PeopleSoft Financials for General Ledger and Accounts Payable single payment vendor translates the concept of a customer from Oracle Utilities Customer Care and Billing to the concept of vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, and helps avoid the creation of a new vendor for every customer that needs to be issued a payment. The single payment vendor record holds default information for customers from Oracle Utilities Customer Care and Billing. If you have already set up single payment vendor you can choose to use the existing vendor. No Oracle Utilities Customer Care and Billing specific setup is required while configuring the single payment vendor for this Integration Point.

•

Accounting Entry Template : Pre-configure an accounting entry template in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to indicate the accounts that accompany the refund. Each voucher that comes from the Oracle Utilities Customer Care and Billing system is accompanied by a General Ledger account. An Accounting Entry template is needed in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to define the off-set account for the incoming Oracle Utilities Customer Care and Billing account. If you have already set up an accounting entry template you can choose to use the existing template. No Oracle Utilities Customer Care and Billing specific setup is required while configuring the accounting entry template for this Integration Point.

•

Payment Terms Cod e: Create a new payment terms code for processing the payments for Oracle Utilities Customer Care and Billing customers. This Payment terms code needs to be of the type 'Single Payment'. These codes are used to define defaults for when payments should be made based on the invoice date, which status should be paid, any applicable discounts, rebates etc.

Note : For specific guidelines on configuring these settings, refer the Oracle PeopleSoft Financials for General Ledger and Accounts Payable User documentation.

1.6.3.2

Oracle Utilities Customer Care and Billing AP Settings

Configure Accounts Payable settings in Oracle Utilities Customer Care and Billing according to the following guidelines and keeping in mind that Oracle PeopleSoft 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 Business Unit must match the AP Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable,

•

Characteristic for Distribution Code : An Oracle Utilities Customer Care and Billing characteristic associated with the distribution code assigned to an adjustment type used to create AP Requests in Oracle Utilities Customer Care and Billing, must be setup and its value must match the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Accounting Entry template to be used.

Note : For more information on the configuration settings referenced in this section, refer the Oracle Utilities Customer Care and Billing user documentation.

1.6.3.3

Integration Software AP Settings

The integration product extracts the unprocessed AP Requests from Oracle Utilities Customer Care and Billing and loads the Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Voucher Build Interface tables after applying the necessary translations and transformations on the Supplier/Invoice data extracted from Oracle Utilities Customer Care and Billing.

As part of the technical configuration of this integration product you must indicate certain values to the product so that it can properly extract data from one application, transform it, and insert it for use by the other application. It is assumed that you have configured this information including:

•

The email address of the person who should notified if the integration software detects and logs an error while performing the AP Request and AP Data integrations,

•

AP Voucher information required by Oracle PeopleSoft Financials for General Ledger and Accounts Payable including build keys, Voucher Style, and Vendor Set ID.

•

The Single Vendor ID, Location, Address Sequence Number, Payment Terms code, AP Rate Type, Multiplier, Division, Match Action, Voucher Source, Physical Nature Code, AP Business Unit Code, Bank Code, Bank account key, payment method, payment handling code, and the Oracle Utilities Customer Care and Billing characteristic types holding the Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Business Unit must all be setup to valid values in the integration settings table,

•

The AP data remit vendor must be set to valid values in the integration settings table.

1.6.3.4

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 PeopleSoft Financials for General Ledger and Accounts Payable

Accounts Payable Clearing

AP Liability

AP Invoice Payment in Oracle PeopleSoft Financials for General Ledger and Accounts Payable

AP Liability

Cash

Understanding the Integration Process

This section provides detailed business process overviews and technical overviews of each of the business processes facilitated by this integration. These include the following:

•

About the Integration

•

Integration Business Processes

2.1

About the Integration

Oracle BPEL Process manager uses standard data mapping to extract, transform and load data to take it from the source system and insert into the target system.

•

After the source system generates financial data the financial data:

•

BPEL extracts and consolidates the data as XML-based data.

•

BPEL then transforms the data into the appropriate format based on the mapping XSLT.

•

BPEL then loads the data into the appropriate table in the target.

•

When the target system receives this data, it validates and converts imported data into the appropriate format of entries in the target application.

•

The process integration points do not use Enterprise Business Objects (EBOs) or other Application Integration Architecture (AIA) objects to complete the integration. Rather, the integration uses the Oracle BPEL Process Manager to extract, transform and load the data into the target system.

The following table shows the integration process, source application, and target application, tables, and process used to load the data that is imported from the other system:

Integration Process

Source System

Target System

Process

Target Table

General Ledger

CCB

PS

Journal Generator

PS_JGEN_ACCT_ENTRY

AP Request

CCB

PS

Voucher Build

PS_VCHR_HDR_STG

PS_VCHR_LINE_STG

PS_VCHR_DIST_STG

PS_VCHR_PYMT_STG

PS_VCHR_VNDR_STG

PS_VCHR_BANK_STG

AP Data

PS

CCB

The appropriate AP Request within Oracle Utilities Customer Care and Billing.

High Level Architecture Diagram

2.1.1

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 here 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 PeopleSoft Financials for General Ledger and Accounts Payable for General Ledger and Accounts Payable databases other than the objects mentioned here.

The following new database tables are required to operate the Oracle Utilities Customer Care and Billing process integration for the Oracle PeopleSoft Financials for General Ledger and Accounts Payable product:

Table Name

Description

INTEGRATION_LOOKUP_TABLE

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

INTEGRATION_PROCESS_ACTIVATION

This table is used to activate or de-activate 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

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

2.2

Integration Business Processes

This section covers the following:

•

General Ledger

•

AP Request

•

AP Data

2.2.1

General Ledger

For general ledger transactions, Oracle Utilities Customer Care and Billing is considered the sub-ledger and Oracle PeopleSoft Financials for General Ledger and Accounts Payable is considered the general ledger.

•

General Ledger transactions are written in one direction; from Oracle Utilities Customer Care and Billing to Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

•

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 the financial transactions in Oracle Utilities Customer Care and Billing that must be included in a batch. The GL 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 writes it to the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Generic Accounting Entry Table: PeopleSoft_JGEN_ACCNT_ENTRY.

•

Once the entries are created in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, the standard journal generation process must be executed to create the necessary journal entries within the General Ledger.

This can be accomplished by scheduling the standard Oracle PeopleSoft Financials for General Ledger and Accounts Payable journal generator process or by manually running this process through the standard user interface provided within the Oracle PeopleSoft Financials for General Ledger and Accounts Payable product.

You must set up a journal generator template to facilitate the Journal Generator's upload of data from the Oracle PeopleSoft Financials for General Ledger and Accounts Payable staging tables.

Note : For more information on GLASSIGN and GLS Batch jobs, refer to the Oracle Utilities Customer Care and Billing documentation.

GL Process Diagram

These are the steps for the GL Process flow:

1.

Run the GLASSIGN batch program in Oracle Utilities Customer Care and Billing.

2.

Run GLS batch program in Oracle Utilities Customer Care and Billing.

3.

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

4.

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

5.

GL BPEL process transforms and inserts the data into Oracle PeopleSoft Financials for General Ledger and Accounts Payable Generic Accounting Entry Table: PeopleSoft_JGEN_ACCNT_ENTRY

6.

GL BPEL process updates the Last_Update_DateTime in CI_BATCH_CTRL table in Oracle Utilities Customer Care and Billing.

7.

Run Journal Import process in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to import FTs.

8.

Any errors in PS interface tables must be corrected in Oracle PeopleSoft Financials for General Ledger and Accounts Payable and Journal Import process must be re-run.

2.2.2

AP Request

•

AP Request transactions are written in one direction from Oracle Utilities Customer Care and Billing to Oracle PeopleSoft Financials for General Ledger and Accounts Payable. Customer, customer account, and AP Request information is extracted from Oracle Utilities Customer Care and Billing and exported to Oracle PeopleSoft Financials for General Ledger and Accounts Payable as single payment voucher information.

•

Once the customer and refund request data is loaded into Oracle PeopleSoft Financials for General Ledger and Accounts Payable, by the integration product, the standard voucher build process must be executed. This can be accomplished using a scheduled process or by manually running the process through the standard user interface provided within the Oracle PeopleSoft Financials for General Ledger and Accounts Payable product.

•

You must have set up a single payment vendor to represent the Oracle Utilities Customer Care and Billing refund customers.

•

It is also necessary to pre-configure an accounting entry template in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to indicate the accounts that accompany the refund.

•

The integration extracts AP Requests from Oracle Utilities Customer Care and Billing where the status of the AP Request is 'N' indicating 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'.

AP Request Business Process Diagram

These are the steps in AP request process:

1.

Create and Freeze Adjustment in Oracle Utilities Customer Care and Billing.

2.

BPEL 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.

BPEL process transforms and inserts the data into PS AP Voucher Build Interface tables.

5.

BPEL updates the status of AP Request in Oracle Utilities Customer Care and Billing.

6.

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

7.

Run AP Voucher Build Process in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to import Invoices. Any errors in Interface tables must be corrected in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

2.2.3

AP Data

•

AP Data transactions are written in one direction from Oracle PeopleSoft Financials for General Ledger and Accounts Payable to Oracle Utilities Customer Care and Billing.

•

Payment information for system-generated checks is created and processed in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, then exported to Oracle Utilities Customer Care and Billing.

This Payment information corresponds to the AP Refund Requests originally generated in Utilities Customer Care and Billing and exported to Oracle PeopleSoft Financials for General Ledger and Accounts Payable for payment processing. The AP Data 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 PeopleSoft 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 Held and 'X' - Cancelled.

Refer to the table below to review how cancelled payments are handled.

2.2.3.1

Payment Cancellation

Oracle PeopleSoft 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 cancelled 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

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

•

Re-Open /Re-Issue

•

Re-Open/Hold

•

Do not Re-Issue/Close Liability

Re-Open /Re-Issue

If a check is cancelled for any reason in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, the new information is updated on the AP Request in Oracle Utilities Customer Care and Billing and the AP Request status is set to 'C' indicating that the AP Request has been closed.

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

A new payment schedule is created for the voucher and is picked up for payment processing by the pay cycle. When the payment is re-issued, the new payment information is sent to Oracle Utilities Customer Care and Billing and the AP Request status in Oracle Utilities Customer Care and Billing is set to 'P' indicating that the AP Request has been paid. A payment cannot be re-issued if the corresponding voucher is posted.

Re-Open/Hold

If a payment is stopped or voided to be re-opened and 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 the Oracle Utilities Customer Care and Billing is set to 'C' indicating a 'Closed' status. This affects only the AP Request, the adjustment in Oracle Utilities Customer Care and Billing is not impacted.

Do not Re-Issue/Close Liability

If the payment is voided or stopped and all liability is closed, the integration cancels the AP Request and then calls a service in Oracle Utilities Customer Care and Billing 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 transaction 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 adjustment cancellation algorithm, named 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. If this algorithm is configured to the adjustment type you are using for AP Refunds, the Oracle Utilities Customer Care and Billing system creates a To Do Entry when an adjustment of this type is cancelled.

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

AP Data Process Diagrams

These are the steps in AP Data process:

1.

Payment is made in Oracle PeopleSoft Financials for General Ledger and Accounts Payable for invoices originated from Oracle Utilities Customer Care and Billing.

2.

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

3.

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

AP Payment Cancellation Process

These are the steps in the AP Data process flow when a payment is cancelled in Oracle PeopleSoft Financials for General Ledger and Accounts Payable:

1.

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

2.

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

3.

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

4.

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

Configuring the Integration

This section provides a configuration checklist which shows an overview of configuration tasks. Detailed steps and information are included in following sections along with instructions on how to set up security, configure error handling and how to verify the implementation once all steps are complete:

•

Prerequisites

•

Assumptions

•

Configuration Checklist

•

Configuring the Integration

•

Setting up Security

•

Verifying the Implementation

3.1

Prerequisites

To implement and configure this product you must have installed the appropriate pre-requisite edge applications and operating platforms.

Prior to starting this implementation, you should have completed and verified the following:

•

Installed and Configured Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

•

Installed and configured Oracle SOA Suite 11g and all of its pre-requisite components.

•

Installed Oracle Utilities Customer Care and Billing, and either installed the provided sample data or pre-configured the software based on the additional configuration identified in this document.

•

Completed, and verified, all of the steps identified in the installation guide for this product.

Note : Refer to the installation guide for this product for complete installation details.

3.2

Assumptions

It is assumed that the person installing the product has basic knowledge of Oracle SOA Suite programs including the ability to log into BPEL, access the process monitor, invoke a BPEL process, and view a process flow. Knowledge of and access to database tools, such as SQLDeveloper, are also required to implement this product.

3.3

Configuration Checklist

This section provides a quick list of configuration tasks. You can print the checklists to use as a reference as you complete the tasks.

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Configuration

•

Oracle Utilities Customer Care and Billing Configuration

•

Integration Product Configuration

•

Verifying Configuration

Refer to the section of this document titled Configuring the Integration for detailed steps.

3.3.1

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Configuration

Step

Information

Comments

A1

GL Business Unit

Identify and document the GL Business Unit(s) to be used with the integrated data. Example: US001. This is used in checklist steps B1 and D16.

A2

Accounting Entry Definition

Identify or Create the accounting entry definition you will use with the Journal Generator process. This definition is used as an input parameter for the Journal Generator Process. It specifies the staging table from which the Journal Generator gets the accounting data. Your definition must specify the JGEN_ACCT_ENTRY table. An example is the GENERIC accounting entry definition.

A3

AP Business Unit

Document the AP Business Unit(s) to be used with the integrated data. Example: US001. This is used in checklist step B4.

A4

AP Single Payment Vendor

Create and document the Single Payment Vendor to be used with the integrated data. Example: CCBVENDOR. This is used in checklist steps D7 and E2.

A5

Payment Terms Code

Create or document the payment terms code to be used for paying AP vouchers coming from Oracle Utilities Customer Care and Billing. Example: 07 (CCBREFUND). This is used in checklist step D10.

A6

Accounting Entry Template

Identify and document the Accounting Entry Template to be used with the integrated data. This template defines the offset account. Example: STANDARD. This is used in checklist step B3.

3.3.2

Oracle Utilities Customer Care and Billing Configuration

Step

Information

Comments

B1

GL Division

Configure the GL Division(s) to be used in the integration. Example: US001. This must match the GL Business Unit, in step A1 above, exactly.

B2

Distribution Codes

Configure your distribution codes. See details of required setup in this document. Example: 111.222.333.… With ‘111’ corresponding to Account, ‘222’ corresponding to Department ID, and so on. See details of all mapping segments later in this document.

B3

Accounting Entry Template Characteristic Type

Configure a characteristic type to hold the value of the Accounting Entry Template to be used. Example characteristic type: CCBTMPLT. This is used in checklist step D22.

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

B4

AP Business Unit Characteristic Type

Configure a characteristic type to hold the value of the AP Business Unit to be used. Example characteristic type: PSBU. This is used in checklist step D23.

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

B5

Verify Service and Cancel Reason

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

B6

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

The CIS Division you are using (Example: CA) will now have a characteristic type linked to it (Example: PSBU) that holds the name of the AP Business Unit to use (Example: US001).

B7

Link the characteristic type created in step B3 with the appropriate distribution codes.

For each distribution code, linked with an adjustment type that has an associated AP Request configured, you must link the characteristic type that indicates the accounting entry template.

The distribution code you are using (Example: AP-OVPY) will now have a characteristic type linked to it (Example: CCBTMPLT) that holds the name of the Accounting Entry Template to use (Example: STANDARD).

3.3.3

Integration Product Configuration

The default settings for the INTEGRATION_PROCESS_ACTIVATION table are shown below:

PROCESS_NAME

START_PROCESS (Y/N)

RUN_FREQUENCY (Seconds)

NEXT_RUN_INTERVAL (System Use)

CCB_PS_GL

Y

0

0

CCB_PS_AP

Y

0

0

PS_CCB_APDATA

Y

0

0

Configuration is done in the INTEGRATION_LOOKUP_TABLE, except item C1 that uses a different table as identified in the comments. These columns receive a default value as part of the installation of the product. You may choose to override the default as needed.

Note : No user interface exists in this release. Use approved database tools to set column values.

Step

INTEGRATION_KEY

INTEGRATION_VALUE

Comments

C1

CCB_PS_GL

CCB_PS_AP

PS_CCB_APDATA

1800 seconds or 30 minutes.

Ensure that integration points are enabled in the INTEGRATION_PROCESS_ACTIVATION table as needed. Set RUN_FREQUENCY times, in seconds, for each integration point as needed. The default is every 1800 seconds or 30 minutes.

C2

CCB.PS.GL.EMAIL

abc.gl@xyz.com

Enter the e-mail address to be notified if errors occur in the GL integration point..

C3

CCB.PS.GL.LINE_DESCR

CCB Journal Line.

The Journal Voucher line description to be used.

C4

CCB.PS.GL.APPL_JRNL_ID

GENERIC

This is journal generator template and it is used in conjunction with the Accounting Entry Definition by the GL Integration point.

C5

CCB.PS.GL.LEDGER

US1 or UGBUCCB

Create or identify the ledger to be used.

C6

CCB.PS.GL.LEDGER_GROUP

US or UGBUCCB

Create or identify the ledger group to be used. .

C7

CCB.PS.GL.GL_DIVISION

blank or US1

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. .

C8

CCB.PS.GL.COLLECTION.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 CCB and before any transformation is done.

C9

CCB.PS.GL.COLLECTION.POST.EXTN.FLAG

false

If set to true, the post transformation extension service is invoked at the record level after transforming the FT record from CCB to PS format.

C10

CCB.PS.GL.OVERRIDE.GLDATA

false

If set to 'true' TransformationCCBToPSGLData.xsl result will be overridden by TransformationCCBToPSGLData_Override_Custom.xsl mappings.

Configuration is done in the INTEGRATION_LOOKUP_TABLE (AP Request Integration Point). Most of these columns receive a default value as part of the installation of the product. You may choose to override the default as needed.

Note : No user interface exists in this release. Use approved database tools to set column values.

Step

INTEGRATION_KEY

INTEGRATION_VALUE

Comments

D1

CCB.PS.AP.EMAIL

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

D2

CCB.PS.AP.VCHR_BLD_KEY_N1

0

Voucher Build Key Num 1

D3

CCB.PS.AP.VCHR_BLD_KEY_N2

0

Voucher Build Key Num 2

D4

CCB.PS.AP.VOUCHER_STYLE

SGLP

This indicates to the system a Single Payment voucher style.

D5

CCB.PS.AP.VENDOR_SETID

SHARE

Vendor SetID

D6

CCB.PS.AP.VENDOR_ID

CCBVENDOR

Vendor ID. This must match the vendor ID setup in step A4. Example: CCBVENDOR.

D7

CCB.PS.AP.VNDR_LOC

1

Vendor Location

D8

CCB.PS.AP.ADDRESS_SEQ_NUM

1

Address Sequence Number

D9

CCB.PS.AP.PYMNT_TERMS_CD

07

Payment Terms ID. This must match what you documented in step A5.

D10

CCB.PS.AP.RT_TYPE

CRRNT

Rate Type

D11

CCB.PS.AP.RATE_MULT

1

Rate Multiplier

D12

CCB.PS.AP.RATE_DIV

1

Rate Divisor

D13

CCB.PS.AP.MATCH_ACTION

N

Match Action

D14

CCB.PS.AP.VCHR_SRC

XML

Voucher Source

D15

CCB.PS.AP.BUSINESS_UNIT_GL

US001

GL Business Unit. This must match what you documented in step A1.

D16

CCB.PS.AP.PHYSICAL_NATURE

S

Physical Nature

D17

CCB.PS.AP.BANK_CD

USBNK

Bank Code

D18

CCB.PS.AP.BANK_ACCT_KEY

CHCK

Bank Account

D19

CCB.PS.AP.PYMNT_METHOD

CHK

Payment Method

D20

CCB.PS.AP.PYMNT_HANDLING_CD

RE

Payment Handling

D21

CCB.PS. AP.CHAR_TYPE_CD

TEMPLATE

Characteristic Type to store the PS Accounting Entry Template. This must match what you documented in step B3. Example: CCBTMPLT.

D22

CCB.PS. AP. CHAR_TYPE_CD_BU

PSBU

Characteristic Type to store PS AP Business Unit. This must match what you documented in step B4. Example: PSBU.

D24

CCB.PS.AP.DST_CNTRL_ID

CCBTMPLT

D25

CCB.PS.AP.VOUCHER_ORIGIN

Origin field on voucher header. If a value is defined, make sure it is a valid origin value in PeopleSoft code table.Default is blank.

D26

CCB.PS.AP.XFORMCCBAPREQCOLL.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 CCB and before any transformation is done.

D27

CCB.PS.AP.INSERTVOUCHER.INVOKEVOUCHER.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 CCB to PS format.

D28

CCB.PS.AP.INSERTVOUCHER.INVOKEVOUCHER.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 PeopleSoft Voucher tables.

D29

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERDIST

false

If set to 'true' TransformationCCBVoucherToPSVoucherDist.xsl result will be overridden by XformCCBVoucherToPSVoucherDist_Override_Custom.xsl mappings.

D30

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERVENDOR

false

If set to 'true' TransformationCCBVoucherTOPSVoucherVendor-1.xsl result will be overridden by XformCCBVoucherTOPSVoucherVendor_Override_Custom.xsl mappings.

D31

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERPYMNT

false

If set to 'true' TransformCCBVoucherToPSVoucherPayment.xsl result will be overridden by XformCCBVoucherToPSVoucherPayment_Override_Custom.xsl mappings.

D32

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERHEADER

false

If set to 'true' TransformationCCBVoucherToPSVoucherHeader.xsl result will be overridden by XformCCBVoucherToPSVoucherHeader_Override_Custom.xsl mappings.

D33

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERLINE

false

If set to 'true' TransformationCCBVoucherToPSVoucherLine.xsl result will be overridden by XformCCBVoucherToPSVoucherLine_Override_Custom.xsl mappings.

D34

CCB.PS.AP.INSERTVOUCHER.OVERRIDE.VOUCHERBANK

false

If set to 'true' TransformationCCBVoucherTOPSVoucherBank-1.xsl result will be overridden by XformCCBVoucherTOPSVoucherBank_Override_Custom.xsl mappings.

Configuration is done in the INTEGRATION_LOOKUP_TABLE (AP Data Integration Point).

Note : No user interface exists in this release. Use approved database tools to set column values.

Step

INTEGRATION_KEY

INTEGRATION_VALUE

Comments

E1

PS.CCB.APDATA.EMAIL

abc@oracle.com

Enter the e-mail address to be notified if errors occur in the AP Data integration point.

E2

PS.CCB.APDATA. REMIT_VENDOR

CCBVENDOR

Remit Vendor. This must match what you documented in step A4.

E3

PS.CCB.APDATA.LASTRUNDTTM

01-01-2011 10: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 point. This column is updated by the integration application each time it is run.

E4

PS.CCB.APDATA.XFORMPSPAYMENTSCOLL.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 PeopleSoft Financials for General Ledger and Accounts Payable and before any transformation is done.

E5

PS.CCB.APDATA.XFORMPSPAYMENTSRECORD.PRE.EXTN.FLAG

false

If set to true, the pre transformation extension service is invoked at the record level before transforming the PeopleSoft Financials for General Ledger and Accounts Payable payment record from PS to CCB format.

E6

PS.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.

E7

PS.CCB.APDATA.POSTPROCESSPAYMENTINFO.PRE.EXTN.FLAG

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.

E8

PS.CCB.CANCEL.CANCEL_REASON

APVC

Cancel reason code. This must match the cancel reason setup in Oracle Utilities Customer Care and Billing

3.3.4

Verifying Configuration

To verify the configuration, you must manually create data and run each integration point to verify results.

Step

Information

Success

(Y /N)

Comments

F1

GL Integration Point

Use the steps outlined in this document to test this integration point. Verifying the Implementation : AP Request Integration Point .

F2

AP Request Integration Point

Use the steps outlined in this document to test this integration point. Verifying the Implementation : GL Integration Point .

F3

AP Data Integration Point

Use the steps outlined in this document to test this integration point. Verifying the Implementation : AP Data Integration Point .

Note: If the integration point tests are not successful, refer to the troubleshooting and error correction information in this document.

3.4

Configuring the Integration

The integration between Oracle Utilities Customer Care and Billing and Oracle PeopleSoft Financials for General Ledger and Accounts Payable incorporates three integration points to facilitate transfer of information between the two applications. Configuration must be completed for all involved products to prepare the integration product for use.

The following sections describe how to configure each area for each integration point:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Configuration

•

Oracle Utilities Customer Care and Billing Configuration

•

Integration Product Configuration

•

Process Scheduling

3.4.1

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Configuration

Configure GL accounts and other information in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

Note : For steps to configure the GL, refer to your product-specific user documentation.

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

3.4.1.1

GL Integration Point

As long as your chart of accounts and other GL definitions and settings are configured in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, there are no new settings required in Oracle PeopleSoft Financials for General Ledger and Accounts Payable for the GL integration point.

If you plan to send Oracle Utilities Customer Care and Billing data to an existing GL business unit, your existing Oracle PeopleSoft Financials for General Ledger and Accounts Payable configuration suffices. If you need to segregate Oracle Utilities Customer Care and Billing data by GL business unit from other financial data within Oracle PeopleSoft Financials for General Ledger and Accounts Payable, create a new GL Business Unit definition in the GL Definition. Take note of the GL Business Unit being used and document it in the implementation checklist, as you require this in subsequent steps.

If you already have an Accounting Entry Definition defined to specify the staging table from which the Journal Generator should get the accounting data sent in by the GL Integration Point, then you do not need to setup a new one. You should however document the name of this Accounting Entry Definition in your implementation checklist.

3.4.1.2

AP Request Integration Point

This covers the following:

•

Configuring an AP Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable (Optional)

•

Identifying the Accounting Entry Template

•

Configuring the Single Payment Vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable

•

Configuring the Payment Terms Code in Oracle PeopleSoft Financials for General Ledger and Accounts Payable

Configuring an AP Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable (Optional)

If you plan to send Oracle Utilities Customer Care and Billing data to an existing AP business unit, your existing Oracle PeopleSoft Financials for General Ledger and Accounts Payable configuration suffices. If you need to segregate Oracle Utilities Customer Care and Billing data by AP business unit from other data within Oracle PeopleSoft Financials for General Ledger and Accounts Payable, create a new AP Business Unit definition. Take note of the AP Business Unit being used, as you require this in subsequent steps.

Identifying the Accounting Entry Template

If you already have an Accounting Entry Template defined to offset incoming Oracle Utilities Customer Care and Billing Accounts, then you do not need to setup a new one. You should however document in the implementation checklist the name of this Accounting Entry Template as it is used to accommodate the accounts that accompany the vouchers coming from Oracle Utilities Customer Care and Billing and you require it in subsequent implementation steps.

Configuring the Single Payment Vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable

You must set up a Single Payment Vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to represent default accounting information for all of the incoming AP Requests from Oracle Utilities Customer Care and Billing. To do this, you require the name of the Accounting Entry template in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to offset incoming Oracle Utilities Customer Care and Billing Accounts.

The field values described below in the table are minimum setup values required to setup a single payment vendor. Based on the client requirements values for other fields may or may not be required. To Configure the Single Payment Vendor in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, perform the following steps:

1.

Open Oracle PeopleSoft Financials for General Ledger and Accounts Payable Financials and navigate to Vendors Page.Navigation path: Vendors > Vendor Information > Add/Update > Vendor

2.

Create Single Payment Vendor by selecting the Add a New Value tab. Choose the Single Payment Vendor (SGLP) persistence.

3.

Use the following settings:

Field Label

Value

Comments

SetID

SHARE

VENDOR

CCBVENDOR

This is an example value. You may use any value you wish but take note of it for future configuration steps.

ShortName

CCBVENDOR

Example.

Name

CCB Single Payment Vendor

Example.

Description

CCB Single Payment Vendor

Example.

Terms Options

Default

This field denotes the payment terms of the voucher. It has two possible values:

1. Default: Values defaulted from higher level setup.

2. Specify: Terms must be specified in the Terms field.

Currency Options

Default

This field denotes the currency code specifications. Two possible values are:

1. Default: Values defaulted from higher level setup.

2. Specify: Terms must be specified in the Terms field.

Handling Options

Default

This field denotes Payment Handling options for individual payments of this vendor. Two possible values are:

1. Default: Values defaulted from higher level setup.

2. Specify: Terms must be specified in the Terms field.

Banking Options

Default

This field denotes the Banking options for this Vendor. Two possible values are:

1. Default: Values defaulted from higher level setup.

2. Specify: Terms must be specified in the Terms field.

Refer to your Oracle PeopleSoft Financials for General Ledger and Accounts Payable documentation for further instructions.

Configuring the Payment Terms Code in Oracle PeopleSoft Financials for General Ledger and Accounts Payable

To Configure the Payment Terms Code in Oracle PeopleSoft Financials for General Ledger and Accounts Payable, follow these steps:

1.

Open Oracle PeopleSoft Financials for General Ledger and Accounts Payable Financials and navigate to the Procurement Options Payment Terms Timing Codes tab. Navigation Path: Set Up Financials/Supply Chain > Product Related > Procurement Options > Payments > Payment Terms Timing Codes

2.

Create a Payment Terms Timing Code by selecting the Add a New Value tab.

SetID : Share

Timing Definition ID : 07 (example)

3.

Define additional timing code values on the Payment Terms Timing Codes screen.

Field Label

Value

Comments

SetID

SHARE

SetID

Timing ID

07

Timing Definition ID

Description

7 Days

Description

Short Description

7 Days

Short Description

Timing Basis Option

None

End of Relative Month

Fixed Month Day Values

Specific Due Date

Timing Basis Option

Timing Adjustment

Day Increment

Month Increment

Year Increment

Timing Adjustment

In this example the Days Increment is set to "7" to indicate that the amount will be paid seven days after the Invoice date.

4.

Create a Payment Terms Code using the Payment Terms Timing Code that was created.

Navigation Path: Set Up Financials/Supply Chain > Product Related > Procurement Options > Payments > Payment Terms-Single Payment

5.

Create a Payment Terms - Single Payment Code by selecting the Add a New Value tab.

SetID: Share

Timing Definition ID: 07 (example)

6.

Select the desired Payment Terms Timing code in the Timing ID field to complete the creation of the Payment Terms Code.

Field Label

Value

Comments

SetID

SHARE

SetID

Description

CCB Refund Payment Terms Code

Description

Payment Terms ID

07

Payment Terms ID

Short Description

CCBREFUND

Short Description

Effective Date

01/01/1900

Effective Date

Terms Applicability

Vendor-Only Terms

Terms Applicability

Status

Active

Effective Status

Split Net Terms

No

Split Net Terms

Basis From and To Days

01 and 31

Only apply when… Starting and Ending Day of the month

Timing ID

07

Net Terms Timing ID

Rebate Term Available

No

Rebate Term Available

Daily Rebate Percent

0

Daily Rebate Percent

Maximum Rebate

0

Maximum Rebate Percent

Discount Terms Available

No

Discount Terms Available

Discount Terms

No

Discount Terms

3.4.1.3

AP Data Integration Point

No configuration is required in Oracle PeopleSoft Financials for General Ledger and Accounts Payable for this integration point. Product-delivered application tables are used for selecting data from Oracle PeopleSoft Financials for General Ledger and Accounts Payable to export payment information to Oracle Utilities Customer Care and Billing.

3.4.2

Oracle Utilities Customer Care and Billing Configuration

To configure the Oracle Utilities Customer Care and Billing portion of the integration define settings for all three integration points.

Note : For instructions on specific steps in Oracle Utilities Customer Care and Billing, refer the user documentation.

This section includes:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

3.4.2.1

GL Integration Point

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

•

Configuring GL Division

•

Configuring Distribution Codes

•

Configuring Fund Code

•

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

Configuring GL Division

You must map your GL Division in Oracle Utilities Customer Care and Billing to the GL Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL Business Unit. To do this, you must know what GL Business Unit(s) you will be using within the GL and create GL Divisions in Oracle Utilities Customer Care and Billing to match these exactly.

You can then associate these GL Divisions with the appropriate Service Agreement Types in Oracle Utilities Customer Care and Billing.

Configuring Distribution Codes

You must map your distribution codes in Oracle Utilities Customer Care and Billing with the appropriate GL Accounts in the Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL. First configure the distribution codes and then assign them to various entities within the Oracle Utilities Customer Care and Billing applications.

Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL accounts are structured using account segments. These are set up in your existing Oracle PeopleSoft Financials for General Ledger and Accounts Payable configuration according to your business practices. Oracle Utilities Customer Care and Billing distribution codes must be configured to mirror the segments in Oracle PeopleSoft Financials for General Ledger and Accounts Payable. The segment positions in Oracle Utilities Customer Care and Billing are dot '.' separated so that the first segment is Account, the second segment is Department ID, and so on, as shown in the following table:

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Account Column name

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

ACCOUNT

Position1

DEPTID

Position 2

OPERATING_UNIT

Position 3

PRODUCT

Position 4

CLASS_FLD

Position 5

PROGRAM_CODE

Position 6

ALTACCT

Position 7

PROJECT_ID

Position 8

AFFILIATE

Position 9

AFFILIATE_INTRA1

Position 10

AFFILIATE_INTRA2

Position 11

BUDGET_REF

Position 12

CHARTFIELD1

Position 13

CHARTFIELD2

Position 14

CHARTFIELD3

Position 15

* Use a dot (.) as the delimiter to indicate a break between positions. Use 2 dots (..) to indicate skipping a position and giving that position a null value.

A sample GL Account string is 400000.10000.NEWYORK.ALLPR...211004

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

•

Account 400000

•

Department ID 10000

•

Operating Unit NEWYORK

•

Product ALLPRD

•

Alternate account 211004

Note : Only fields relevant to the integration are included in this table:

Field Label

Value

Comments

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

400000.10000.NEWYORK.ALLPRD…211004.

Input the GL Account String as explained above.

Configuring Fund Code

Note : This section is only relevant for some organizations. The Fund Code configuration is needed only if your organization practices fund accounting (this type of accounting is typically performed by municipal utilities).

If you are using fund accounting, you must map your fund codes in Oracle Utilities Customer Care and Billing with the appropriate fund codes in the Oracle PeopleSoft Financials for General Ledger and Accounts Payable. First configure the appropriate fund codes and then assign them to their respective distribution codes within the Oracle Utilities Customer Care and Billing applications

For more information about enabling Fund Accounting and configuration of Fund Code, refer to Oracle Utilities Customer Care and Billing Implementation and User Guides.

Note : When setting up the fund code in Oracle Utilities Customer Care and Billing, please ensure that the length of the FUND_CD is not greater than five (5) characters as this is the maximum number of characters supported by the Oracle PeopleSoft Financials for General Ledger and Accounts Payable fund code.

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.

3.4.2.2

AP Request Integration Point

This includes the following:

•

Configuring the Accounting Entry Template Characteristic Type

•

Note: Only fields relevant to the integration are included in this table. Configuring the AP Business Unit Characteristic Type

Configuring the Accounting Entry Template Characteristic Type

For each Adjustment Type that has an associated AP Request, you must identify, in Oracle Utilities Customer Care and Billing, the accounting entry template to be used in Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL. This template is used by the Oracle PeopleSoft Financials for General Ledger and Accounts Payable applications to determine the offset account associated with the distribution code and GL account information sent from Oracle Utilities Customer Care and Billing with the AP Request information.

Complete the following configuration in Oracle Utilities Customer Care and Billing to reference the Accounting Entry Template corresponding to the distribution code as follows.

1.

Create a Characteristic Type. Admin menu>C>Characteristic Type

The value for this characteristic type stores the value of the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Accounting Entry Template. In this example, it is CCBTMPLT. Use the name of the template you have established for this purpose in Oracle PeopleSoft Financials for General Ledger and Accounts Payable Enterprise Management.

2.

Set up the details on the Characteristic Type as follows:

Field Label

Value

Comments

Characteristic Type

TEMPLATE

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

Description

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Template

A description of the use for this characteristic type.

Type of Char Value

Predefined Value

No freeform text is allowed, only a predefined set of values.

Allow Search by Char Val

Allowed

Allow Searches

Characteristic Value

CCBTMPLT

The name of the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Template to be used.

Description

CCB Account Template

3.

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

Field Label

Value

Comments

Characteristic Entity

Distribution Code

This characteristic type can be inserted on a distribution code.

4.

Attach the Characteristic Type, created above, to any distribution codes that will be used for AP Request Adjustments. In sample data an example is provided as the A/P - OVPY Distribution Code that is attached to the REFUNDAP Adjustment Code. Admin menu > D > Distribution Code

Field Label

Value

Comments

Distribution Code

Example: A/P-OVPY

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

Description

Example: AP overpayment refund

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

400000.10000.NEWYORK.ALLPRD…211004.

Input the GL Account String as explained above.

Characteristic Type

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Template

The characteristic type you created above.

Characteristic Value

Example: CCBTMPLT

The value you gave to the characteristic type created above.

Note : Only fields relevant to the integration are included in this table.

Configuring the AP Business Unit Characteristic Type

For each CIS Division used in Oracle Utilities Customer Care and Billing, you must configure the AP Business Unit to be used in Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL.

Complete the following configuration in Oracle Utilities Customer Care and Billing to reference the AP Business Unit corresponding to the CIS Division as follows:

1.

Create a Characteristic Type. Admin menu > C > Characteristic Type

The value for this characteristic type stores the value of the Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Business Unit. In this example it is CCBTMPLT. You will use the name of the template you have established for this purpose in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

2.

Set up the details on the Characteristic Type as follows:

Field Label

Value

Comments

Characteristic Type

PSBU

The code associated with your characteristic type. This will be used in future steps.

Description

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Business Unit

A description of the use for this characteristic type.

Type of Char Value

Predefined Value

No freeform text is allowed, only a predefined set of values.

Allow Search by Char Val

Allowed

Allow Searches

Characteristic Value

US001

The name of the Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Business Unit to be used.

Description

US001 Business Unit

3.

Select the Characteristic Entities tab to allow the Characteristic Type to be associated with the Distribution Code:

Field Label

Value

Comments

Characteristic Entity

CIS Division

This characteristic type can be inserted on a CIS Division.

4.

Attach the Characteristic Type, created above, to any CIS Divisions that will be used for AP Request Adjustments. In sample data an example is provided as the CA CIS Division. Admin menu > C > CIS Division

Field Label

Value

Comments

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 PeopleSoft Financials for General Ledger and Accounts Payable Business Unit

The characteristic type you created above.

Characteristic Value

Example: US001

The value you gave to the characteristic type created above.

Note : Only fields relevant to the integration are included in this table.

3.4.2.3

AP Data Integration Point

No Oracle Utilities Customer Care and Billing configuration is required to enable this integration point.

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

Oracle BPEL Process Manager invokes the Oracle Utilities Customer Care and Billing service, named C1AdjustmentMaintenance, when a payment is canceled and the liability is closed in Oracle PeopleSoft Financials for General Ledger and Accounts Payable. The service uses the cancel reason contained in its 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 and later.

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

. Admin Menu > X > XAI Inbound Service

Field Label

Value

Comments

XAI In Service Name

AdjustmentMaintenance

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.

You may wish to test this service using XAI Dynamic Submission as follows:

Admin menu >X >XAI Dynamic Submission

Field Labels

Value

Comments

XAI In Service Name

AdjustmentMaintenance

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

Transaction Type

Update

Cancel

Checked

Cancel checkbox checked.

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. This must be a configured cancel reason.

Click Submit and review the results.

3.4.3

Integration Product Configuration

At this time there is no user interface for entering the configuration parameters associated with Customer Care and Billing Integration Pack for Oracle PeopleSoft Financials for General Ledger and Accounts Payable. The implementer must use an approved database access tool to establish the appropriate configuration parameters in the following tables and columns.

3.4.3.1

Process Activation Manager

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 you are not using.

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

PROCESS_NAME

START_PROCESS (Y/N)

RUN_FREQUENCY

(Seconds)

NEXT_RUN_INTERVAL

(System Use)

CCB_PS_GL

Y

0

0

CCB_PS_AP

Y

0

0

PS_CCB_APDATA

Y

0

0

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

3.4.4

Process Scheduling

You may schedule these processes independently or using an enterprise scheduling tool. To schedule the processes independently, you may schedule the Oracle Utilities Customer Care and Billing processes using the standard tools available with the product. You may schedule the Oracle PeopleSoft Financials for General Ledger and Accounts Payable processes using the standard tools shipped with the product. You may schedule the BPEL processes to at the time intervals you have configured. Each of the main BPEL processes that form this process integration have been designed to first look and see if data exists that should be extracted. 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 6AM each weekday morning. Then initiate the BPEL GL process at 7:00 AM and have it run every 4 hours to see if data exists. Accounting staff could then schedule the Journal Generator to run at 9:00 AM each morning using the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Process Scheduler.

You may run the integration manually by initiating each process using the tools provided with each application. Or you can 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 2 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 the integration point tries again 2 minutes later.

If you wish to extend the time between runs you may 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 2 minutes so you cannot schedule the runs in less than 2 minute increments.

Set the run frequency to the time interval, in 120 second increments, you wish to have between integration runs for each of the integration points.

PROCESS_NAME

START_PROCESS (Y/N)

RUN_FREQUENCY

(Seconds)

NEXT_RUN_INTERVAL

(System Use)

CCB_PS_GL

Y

14400

0

CCB_PS_AP

Y

7200

0

PS_CCB_APDATA

Y

7200

0

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

Every 2 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)

This section includes:

•

General Ledger (GL)

•

AP Request

•

AP Data

3.4.4.1

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 PeopleSoft Financials for General Ledger and Accounts Payable.

Product

Process Name

Description

Oracle Utilities Customer Care and Billing

GLASSIGN

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

Oracle Utilities Customer Care and Billing

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

CCBToPSGLBPELProcess

Extracts financial transactions from CCB into BPEL to be transformed and prepared for upload to PS.

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

PeopleSoft Journal Generator

Reads the staged data in the Generic Accounting Entry Table and creates journal entries in the PS GL. This process can be scheduled or run manually.

3.4.4.2

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 PeopleSoft Financials for General Ledger and Accounts Payable:

Product

Process Name

Description

BPEL

CCBToPSAPBPELProcess

Extracts AP Requests from Oracle Utilities Customer Care and Billing into BPEL to be transformed and prepared for upload to Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

AP Voucher Build Application Engine Process (AP_VCHRBLD)

Creates single payment vouchers in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

3.4.4.3

AP Data

The following process must be run, sometime after the Oracle PeopleSoft Financials for General Ledger and Accounts Payable check run, to extract, transform, and load AP Data from Oracle PeopleSoft Financials for General Ledger and Accounts Payable to Oracle Utilities Customer Care and Billing.

Product

Process Name

Description

BPEL

CCBToPSDataBPELProcess

Extracts all payments and cancelations 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.

There is an open source tool bundled with the SOA Suite called Quartz, which can be used as a scheduler. In fact the BPEL Process Manager uses the Quartz scheduler internally.

3.5

Setting up Security

Steps to enable security for connecting to Oracle Customer Care and Billing from SOA11g middleware:

1.

Login to the WebLogic EM console using admin username and password.

2.

Expand WebLogic Domain and right click soa_domain --> Security --> Credentials.

3.

Select Create Map and enter the oracle.wsm.security as the MAP name.

4.

Click the OK button.

5.

Select the newly created oracle.wsd.security map then click the Create Key. This opens a pop-up window where you can select oracle.wsd.security MAP.

6.

Enter the Key name as OU_CCBPS_01.The key name must set to this value or else the authentication fails.

7.

From the dropdown select Type as Password.User Name and Password should be valid userid and password for the installed Oracle Utilities Customer Care and Billing instance.

8.

Click OK.

3.6

Verifying the Implementation

The best way to verify the implementation is to start each application individually then manually running the integration points:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

3.6.1

GL Integration Point

1.

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

2.

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

3.

Run the GLS process 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 Oracle BPEL process reads the CI_BATCH_JOB table to check for new BATCH_JOB_ID and BATCH_JOB_STAT_FLG.

4.

Invoke the GL Integration Point process from Oracle BPEL Process Manager or wait for its next run to occur. The package should do 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 PeopleSoft Financials for General Ledger and Accounts Payable.

5.

Use the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator to Load the GL data into the Generic Accounting Entry Table: PeopleSoft_JGEN_ACCNT_ENTRY.

3.6.2

AP Request Integration Point

1.

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

2.

Invoke the Oracle BPEL Integration Point Process to extract the AP Request Information, and the corresponding customer information from Oracle Customer Care and Billing, transform it, and load it into Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP voucher build integration tables.

3.

Run the voucher build process in Oracle PeopleSoft Financials for General Ledger and Accounts Payable to create vouchers from the AP Check Request and Customer data that is staged in the voucher build integration tables.

3.6.3

AP Data Integration Point

1.

Generate a payment in Oracle PeopleSoft Financials for General Ledger and Accounts Payable Payables for a voucher created by the Oracle Utilities Customer Care and Billing AP Request process above.

2.

Invoke the Oracle BPEL Integration Point process to update the AP Check Request table (CI_ADJ_APREQ) with the Payment Information from Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

3.

If you wish to further test a cancellation of payment functionality, cancel the Payment you made above in Oracle PeopleSoft Financials for General Ledger and Accounts Payable Payables and close the liability.

4.

Invoke the Oracle BPEL Process Manager process to update the AP Check Request table (CI_ADJ_APREQ) with the Payment Information from Oracle PeopleSoft Financials for General Ledger and Accounts Payable. This should also cancel the AP request and adjustment.

Monitoring and Troubleshooting

If your integration is configured properly and your data entry into Oracle Utilities Customer Care and Billing and Oracle PeopleSoft Financials for General Ledger and Accounts Payable is correct, you should not experience errors related to the integration. The following sections address common scenarios which may produce errors and offer possible solutions toward error resolution.

It covers the following sections:

•

E-mail Notification

•

Locating Error Logs

•

Error Resolution

4.1

E-mail Notification

If errors occur during the main integration processes, they are logged and stored in the Oracle BPEL Process Manager error table, INTEGRATION_ERROR_STORE and the notification sub process is invoked.

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 BPEL 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 e-mail to the corresponding e-mail 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 e-mail address corresponding to the GL interface. After the e-mail is sent, the system updates the indicator so that NOTIFIED = 'Y.'

This e-mail contains the following information about each of the 6 GL transactions that failed:

•

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

•

Body:

•

Source system

•

Integration batch number

•

BPEL Instance ID

•

Error Code

•

Error Summary

•

Error Message

4.1.1

Steps to Enable E-mail Notification

Perform the following steps:

1.

Log in to the Enterprise Manager console.

2.

Navigate to SOA, right-click soa-infra, and then navigate to SOA Administration > Workflow Notification Properties.

3.

Select E-mail from the drop-down list.

4.

Provide the e-mail IDs in the From address field.

4.2

Locating Error Logs

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

In general, the following types of errors are reported in the location indicated.

Error

Reported In

Errors occurring when GLASSIGN or GLS batches are run

Oracle Utilities Customer Care and Billing product batch run tree.

If an error occurs in the Oracle Utilities Customer Care and Billing batch processes, you must correct the underlying condition causing the error and then rerun the batch processes. The rest of the integration cannot occur until the two CCB batch processes have successfully completed.

Errors occurring during the execution of the journal generator

Oracle PeopleSoft Financials for General Ledger and Accounts Payable process monitor.

The monitor shows the status of the process and an error log.

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

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 should include all of the information necessary to investigate and correct the error.

4.2.1

BPEL Processing Errors

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

When errors are found during data extraction Oracle BPEL Process Manager inserts errors into the error table, INTEGRATION_ERROR_STORE. There is no user interface to access this table. The INTEGRATION_ERROR_STORE table includes the following information:

COLUMN

DATA TYPE

SOURCE_SYSTEM

VARCHAR2 (3)

INT_BATCH_NUMBER

NUMBER

INTERFACE_NAME

VARCHAR2 (30)

BPEL_INSTANCE_ID

NUMBER

ERROR_CODE

NUMBER

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 e-mail.

This section covers:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

4.2.1.1

GL Integration Point

Extraction of financial transactions from Oracle Utilities Customer Care and Billing or load of transactions into the PeopleSoft_JGEN_ACCT_ENTRY table.

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)

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

4.2.1.2

AP Request Integration Point

Extraction of financial transactions from Oracle Utilities Customer Care and Billing tables or load of transactions into the voucher build integration tables.

TABLE

CLOUMN

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)

4.2.1.3

AP Data Integration Point

Extraction of financial transactions from Oracle Utilities Customer Care and Billing tables or load of transactions into the voucher build integration tables.

TABLE

COLUMN

DATA TYPE

PS_PAYMENT_TBL

BANK_CD

VARCHAR2 (5)

BANK_ACCT_KEY

VARCHAR2 (4)

PYMNT_ID

VARCHAR2 (10)

PYMNT_ID_REF

VARCHAR2 (20)

BANK_ACCOUNT_NUM

VARCHAR2 (17)

REMIT_VENDOR

VARCHAR2 (10)

PS_PYMNT_VCHR_XREF

VOUCHER_ID

VARCHAR2 (8)

PYMNT_MESSAGE

VARCHAR2 (70)

PS_VOUCHER

INVOICE_ID

VARCHAR2 (30)

4.3

Error Resolution

The following sections detail error scenarios which may occur, and how to resolve them. Usually when errors occur, you must correct configuration settings, data errors, or both:

•

Any Integration Point

•

General Ledger

•

AP Request

•

AP Data

4.3.1

Any Integration Point

Error Scenario

Process

Details

Resolution

System or Network Down

BPEL Process

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

If BPEL goes down in the middle of a long running process, it can be restarted and it resumes 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.

4.3.2

General Ledger

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

Error Scenario

Process

Details

Resolution

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

BPEL Process

If one row fails to insert into the Oracle PeopleSoft 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 e-mail.

Re-establish the connections between BPEL and the edge applications if necessary. Correct the configuration and/or transactional data in the Oracle Utilities Customer Care and Billing database if necessary. Make sure that the GLASSIGN and GLS processes are run again. The Integration process must also be re-run once you have taken the above actions.

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

PS Process

If the integration process completes successfully and data is inserted into the Oracle PeopleSoft 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 the Oracle PeopleSoft Financials for General Ledger and Accounts Payable application and load the journal voucher using the online tools provided in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

Journal Generator process cannot complete successfully.

PS Process

When the Journal Generator process encounters errors, the error status/reason associated with the Journal Generator process is also identified in the Oracle PeopleSoft 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'.

Journal Generator process completes successfully with bad data.

PS Process

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 the Oracle PeopleSoft Financials for General Ledger and Accounts Payable application, and load the journal voucher using the online tools provided in Oracle PeopleSoft 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 PeopleSoft Financials for General Ledger and Accounts Payable interface table does not get picked up.

PS Process

The Journal Generator process does not error out and the row of bad data in the Oracle PeopleSoft 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 the Oracle PeopleSoft Financials for General Ledger and Accounts Payable application.

After the process has completed successfully, the Distribution Status of the row still remains in ‘N’.(does not change to ‘D’).

GL Account Mapping inconsistency

BPEL, CCB Setup

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

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

Wrong GL Business Unit

CCB

The financial information being sent to Oracle PeopleSoft Financials for General Ledger and Accounts Payable has the wrong business unit associated with it.

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

4.3.3

AP Request

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

Error Scenario

Process

Details

Resolution

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

CCB Process

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 will be 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 e-mail 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.

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

PS Process

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

Load the vouchers directly into the Oracle PeopleSoft Financials for General Ledger and Accounts Payable application and resolve any incorrect data.

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

PS Process

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Voucher Build process ends successfully but the vouchers are in recycle status.

Load the vouchers directly in the Oracle PeopleSoft Financials for General Ledger and Accounts Payable application and resolve any incorrect data.

4.3.4

AP Data

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

Error Scenario

Details

Details

Resolution

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

BPEL Process

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

Review BPEL error table and product error logs. Update BPEL and/or product configurations as needed to correct the errors reported. Re-run the integration once corrections have been made.

The integration is unable to invoke the Adjustment Maintenance Service.

BPEL Process

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

Review BPEL error table and product error logs. Update BPEL and/or product configurations as needed to correct the errors reported. Re-run the integration once corrections have been made.

Customization Options

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

•

Extension Methods

•

Available Extension Points

5.1

Extension Methods

The Integration Process allows extensibility of transaction messages using the following methods:

•

Pre-transformation Extension Point

•

Post-transformation Extension Point

•

Custom Transformations

5.1.1

Pre-transformation Extension Point

The pre-transformation extension point is invoked before the main transformation is executed. This transformation helps in transforming the source xml coming as an input to the integration process.

The integration layer defines an external call from the pre-transformation extension point which accepts the source xml as input and gives the source xml as output. The wsdl the integration layer points to an abstract wsdl and can be plugged in by a concrete wsdl by the implementation team.

This helps the implementation to invoke any external web service and transform the input xml.

5.1.2

Post-transformation Extension Point

The post transformation extension point is invoked after the main transformation is executed. This transformation helps in transforming the target xml going as an input to the Target queue.

The Integration layer defines an external call from the Post transformation extension point which accepts the target xml as input and gives the target xml as output. The wsdl the integration layer points to an abstract wsdl and can be plugged in by a concrete wsdl by the implementation team.

This helps the implementation to invoke any external web service and transform the output xml.

5.1.3

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. This 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.

Note : The custom elements in the Target variable are not passed to the Database Adapter but they are passed to Post Collection Extension point.

5.2

Available Extension Points

The integration includes the following extension points. This section provides information on these in detail.

Integration Point

Main BPEL Process

GL

CCBToPSGLBPELProcess

AP Request

CCBToPSAPBPELProcess or InsertPSVoucher

AP Data

PSToCCBAPDataBPELProcess

This section includes:

•

Extension Points Available in GL

•

Extension Points Available in AP Request

•

Extension Points Available in AP Data

5.2.1

Extension Points Available in GL

Main BPEL Process - CCBToPSGLBPELProcess

Process where extensibility options are stored:

CCBToPSGLBPELProcess

Pre Transformation Collection Extension Point

CCB.PS.GL.XFORMCCBCOLL.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the collection level before any transformation is executed.

Post Transformation Collection Extension Point

CCB.PS.GL.AFTERPSCOLLINSERT.POST.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the post transformation extension point is invoked after all the records are insert to the Peoplesoft_GEN_ACCT_ENTRY table.

•

The custom transformation is used to map elements coming from the GL/FT tables in Oracle Utilities Customer Care and Billing to fields in the Peoplesoft_JGEN_ACCT_ENTRY table that are still unmapped.

•

The main transformation invokes the custom transformation. (Example: main transformation is TransformationCCBToPSGLData.xsl; custom transformation is TransformationCCBToPSGLData_Custom.xsl).

5.2.2

Extension Points Available in AP Request

Main BPEL Process - CCBToPSAPBPELProcess

Process where extensibility options are stored:

CCBToPSAPBPELProcess

Pre Transformation Collection Extension Point

CCB.PS.APREQUEST.XFORMCCBCOLL.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the collection level before any transformation is executed.

Pre Transformation Record extension point

CCB.PS.APREQUEST.XFORMCCBRECORD.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the record level before the AP Request record coming from Oracle Utilities Customer Care and Billing is transformed to the PeopleSoft Financials for General Ledger and Accounts Payable format. Note: This extension point is being invoked twice before inserting into PS Journal table and Voucher table.

Post Transformation Record extension point

CCB.PS.APREQUEST.XFORMPSRECORD.POST.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the post transformation extension point is invoked at the record level after the AP record coming from Oracle Utilities Customer Care and Billing is transformed to the PeopleSoft Financials for General Ledger and Accounts Payable format but before inserting into the F0411Z1 AND F0911Z1 tables in PeopleSoft Financials for General Ledger and Accounts Payable.

Insert Voucher BPEL Process - InsertPSVoucher

Process where extensibility options are stored:

CCBToPSAPBPELProcess

InsertPSVoucher

Pre Transformation Collection Extension Point

CCB.PS.AP.XFORMCCBAPREQCOLL.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the collection level before any transformation is executed.

Pre Transformation Record extension point

CCB.PS.AP.INSERTVOUCHER.INVOKEVOUCHER.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the record level before the AP Request record coming from Oracle Utilities Customer Care and Billing is transformed to the Oracle PeopleSoft Financials for General Ledger and Accounts Payable format.

Post Transformation Record extension point

CCB.PS.AP.INSERTVOUCHER.INVOKEVOUCHER.POST.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the post transformation extension point is invoked at the record level after the GL record coming from Oracle Utilities Customer Care and Billing is transformed to the Oracle PeopleSoft Financials for General Ledger and Accounts Payable format but before the insert to the Peoplesoft_JGEN_ACCT_ENTRY table.

•

The custom transformation is used to map elements coming from the Adjustment AP Request tables in Oracle Utilities Customer Care and Billing to fields in the PeopleSoft Voucher Staging tables that are still unmapped.

•

The main transformation invokes the equivalent custom transformation. (Example: main transformation is Transformation_Header.xsl; custom transformation is Transformation_Header_Custom.xsl).

5.2.3

Extension Points Available in AP Data

Main BPEL Process - PSToCCBAPDataBPELProcess

Process where extensibility options are stored:

PSToCCBAPDataBPELProcess

Pre Transformation Collection Extension Point

PS.CCB.APDATA.XFORMPSPAYMENTSCOLL.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the collection level before any transformation is executed.

Pre Transformation Record extension point

PS.CCB.APDATA.XFORMPSPAYMENTSRECORD.PRE.EXTN.FLAG

If this value defined in the integration lookup table is set to true, the pre transformation extension point is invoked at the record level before the Payment or Cancellation record coming from Oracle PeopleSoft Financials for General Ledger and Accounts Payable is transformed to the CCB format.

Pre Processing extension point

PS.CCB.APDATA.PROCESSPAYMENTINFO.PRE.EXTN.FLAG

If this value is defined in the integration lookup table is set to true, the pre processing extension point is invoked.

Post Processing extension point

If PS.CCB.APDATA.POSTPROCESSPAYMENTINFO.PRE.EXTN.FLAG defined in the integration lookup table is set to true, the ipost processing extension point is invoked.

•

The custom transformation is used to map elements coming from the PeopleSoft Payment table to fields in the Oracle Utilities Customer Care and Billing Adjustment AP Request table that are still unmapped.

•

The main transformation invokes the equivalent custom transformation. (Example: main transformation is Transformation_PSPaymentToCCBPayment-P.xsl; custom transformation is Transformation_PSPaymentToCCBPayment-P_Custom.xsl).

5.3

Steps to Implement Extension Points

To implement extension points, perform the following:

1.

Each process in the integration has pre and post transformation extension points which can be used to invoke web services and transform the payload.

2.

The desired extension point can be triggered from the process by enabling the pre and post transformation extension flags defined in the integration lookup table.

3.

The processes that include extension points have their own concrete wsdl. This is used to read the endpoint location for the extension service.

4.

The binding and service elements for the extension service must be added to the concrete wsdl in the product install home at CCB_PS_INSTALL_HOME/MDS-Artifacts/ MetaData/ExtensionServiceLibrary and the wsdl must be updated in MDS.

5.

These concrete wsdl files are located in MDS under the directories /apps/CCB-PS/MetaData/ExtensionServiceLibrary. Refer to the instructions in the Installation Guide for updating MDS.

6.

Re-deploy the composite or restart the SOA server for the extension point to invoke the web service in the concrete wsdl. For example: To enable the extension points for CCBToPSGLBPELProcessExtension add the binding and service elements to the CCBToPSGLBPELProcessExtensionConcrete.wsdl

<binding name="CCBToPSGLBPELProcessV1ExtensionServiceSOAP11Binding"

type="ccbext:CCBToPSGLBPELProcessV1ExtensionService">

<soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>

<operation name="PreXformCollectionCCBtoPS">

<soap:operation style="document" soapAction="http://xmlns.oracle.com/CCBToPSGLBPELProcess/CCBToPSGLBPELProcessExtension/V1/PreXformCollectionCCBtoPS"/>

<input>

<soap:body use="literal" parts="CollectionCCBtoPS"/>

</input>

<output>

<soap:body use="literal" parts="CollectionCCBtoPS"/>

</output>

<fault name="fault">

<soap:fault name="fault" use="literal"/>

</fault>

</operation>

<operation name="PreInvokePSJGenTable">

<soap:operation style="document" soapAction="http://xmlns.oracle.com/CCBToPSGLBPELProcess/CCBToPSGLBPELProcessExtension/V1/PreInvokePSJGenTable"/>

<input>

<soap:body use="literal" parts="PSRecord"/>

</input>

<output>

<soap:body use="literal" parts="PSRecord"/>

</output>

<fault name="fault">

<soap:fault name="fault" use="literal"/>

</fault>

</operation>

</binding>

<service name="CCBToPSGLBPELProcessV1ExtensionService">

<!-- Sample: Port name must match the port name used for the Extension service in the composite.xml for the process -->

<port name="CCBToPSGLBPELProcessV1ExtensionService"

binding="ccbext:CCBToPSGLBPELProcessV1ExtensionServiceSOAP11Binding">

<soap:address location="http://intser9.idc.oracle.com:8001/soa-infra/services/default/GLTestExtensionService/GLExtService"/>

</port>

</service>

7.

For the custom BPEL process invoked from the extension point to be in the same global transaction as the main BPEL process, make sure that the transaction flag on the Custom BPEL component is set to "Required" :

<component name="ExtensionService">

<implementation.bpel src="ExtensionService.bpel"/>

<property name="bpel.config.transaction"

many="false" type="xs:string">required</property>

</component>

8.

Also, if security policies are attached to the composites, then "oracle/Utilities_wss_http_token_service_policy_OPT_ON" should be attached to the service and "oracle/Utilities_wss_http_token_client_policy_OPT_ON" should be attached to all the references of the custom BPEL process invoked from the Extension Points.

5.4

Steps to Implement Custom Transformations

To implement custom transformations, perform the following:

1.

Each process in the integration has its own xsd files for the incoming and outgoing messages. The messages have custom elements at record level which can be used to pass additional data.

2.

Each xsd has a corresponding CustomType xsd in which the complexType elements for each customElements tag are defined.

3.

To pass additional elements in the customElements tag the corresponding complexType needs to be modified. Add the additional elements required in both the complexType elements i.e. xsd for both edge applications.

4.

The custom xsd files are located in the product install home under the directories CCB_PS_INSTALL_HOME/MDS-Artifacts/CCB-PS/MetaData/ApplicationObjectLibrary/OUCCB/V1/schemas and CCB_PS_INSTALL_HOME/MDS-Artifacts/CCB-PS/MetaData/ApplicationObjectLibrary/Peoplesoft/V1/schemas

5.

The custom elements in the incoming message can be populated through the Extension points.

6.

Each transformation file has a corresponding Custom xsl, and standard and custom templates are defined in the Custom xsl.

7.

Each transformation invokes the standard and custom templates at the record level.

8.

The Custom xsl has a standard template to map elements that are still unmapped coming from the Oracle Utilities Customer Care and Billing DB Adapter Table schema to fields in the Oracle PeopleSoft Financials for General Ledger and Accounts Payable DB Adapter Table schema or vice versa.

9.

The Custom xsl has a custom template to map custom elements.

10.

The custom xsl files are located in the product install home under the directory CCB_PS_INSTALL_HOME/services/industry/Utilities/EnterpriseBusinessFlow/<Process Name>/xsl

11.

After updating the xsd and xsl files in the product install home, update MDS using the ant scripts and restart the SOA server.

Refer to the instructions for updating MDS located in the Installation Guide.

Example:

CCB Schema: GetCCBGLData.xsd

PS Schema: InsertIntoPS_table.xsd

To modify the GL integration process to map Accounting Date from CCB to PS.Modify TransformationCCBToPSGLData_Custom.xsl

<xsl:template name="PsJgenAcctEntry_standardElements">

<!-- this template is use for custom transformation of CCB GL Data to PS Journal Entry -->

<ns0:journalDate>

<xsl:value-of select=" ccbgl:ACCOUNTING_DT"/>

</ns0:journalDate>

</xsl:template>

</xsl:stylesheet>

Appendix A - Data Mapping

Data Mapping

This section provides mapping details for each integration point, including:

•

GL Transaction

•

AP Request

•

AP Data

•

Database Tables Involved in Integration

•

Logic Used in the Integration Points

•

BPEL Process Overview

•

Shared Integration Processes

GL Transaction

This section covers the following:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL Table Mapping to Oracle Utilities Customer Care and Billing

Oracle PeopleSoft Financials for General Ledger and Accounts Payable GL Table Mapping to Oracle Utilities Customer Care and Billing

Column

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Column

Data Type

Remarks

BUSINESS_UNIT

CHAR (5)

BUSINESS UNIT

CI_FT

GL_DIVISION

Derived from BPEL, Identifies the GL Business

Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

TRANSACTION_ID

CHAR (10)

REPORT ID

CI_FT_PROC

BATCH_NBR

NUMBER (10)

Identifies a Transaction. This is just a cross reference back to the source system. This is the Oracle Utilities Customer Care and Billing GLDL Batch Number.

SEQUENCENO

NBR (3)

SEQUENCENO

Derived from BPEL, It identifies a line within a transaction ID.

BPEL inserts number starting with 1.

LEDGER_GROUP

CHAR(10)

LEDGER GROUP

Derived from BPEL. Identifies the Oracle PeopleSoft Financials for General Ledger and Accounts Payable Ledger Group to use for posting.

Value = UGBUCCB

LEDGER

CHAR(10)

LEDGER

Derived from BPEL. Identifies the Oracle PeopleSoft Financials for General Ledger and Accounts Payable ledger to use for posting.

Value = UGBUCCB

ACCOUNTING_DT

DATE(10)

ACCOUNTING DATE

CI_FT

ACCOUNTING_DT

DATE

Date used by GL to define the accounting period into which the Financial Transaction is booked.

APPL_JRNL_ID

CHAR(10)

JOURNAL TEMPLATE

Derived from BPEL. Identifies the Journal Template to use.

Value = UGBUCCB

BUSINESS_UNIT_GL

CHAR(5)

GL BUSINESS UNIT

CI_FT

GL_DIVISION

Derived from BPEL, Identifies the GL Business Unit in Oracle PeopleSoft Financials for General Ledger and Accounts Payable.

FISCAL_YEAR

NBR(4)

FISCAL YEAR

Populated by Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator.

Initial value derived by BPEL

Value = 0

ACCOUNTING_PERIOD

NBR(3)

ACCOUNTING PERIOD

Populated by Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator.

Initial value derived by BPEL

Value = 0

JOURNAL_ID

CHAR(10)

JOURNAL ID

Populated by Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator.

Initial value derived by BPEL

Value = NEXT

JOURNAL_DATE

DATE(10)

JOURNAL DATE

Populated by Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator.

Initial value derived by BPEL

Value = NULL

JOURNAL_LINE

NBR(9)

GL JOURNAL LINE NUMBER

Populated by Oracle PeopleSoft Financials for General Ledger and Accounts Payable Journal Generator.

Initial value derived by BPEL

Value = 0

ACCOUNT

CHAR(10)

ACCOUNT

CI_FT_GL

GL_ACCT Position1

Varchar2(254)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

ALTACCT

CHAR (10)

ALTERNATE ACCOUNT

CI_FT_GL

GL_ACCT

Position 7

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

DEPTID

CHAR (10)

DEPARTMENT

CI_FT_GL

GL_ACCT

Position 2

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

OPERATING_UNIT

CHAR (8)

OPERATING UNIT

CI_FT_GL

GL_ACCT

Position 3

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

PRODUCT

CHAR (6)

PRODUCT

CI_FT_GL

GL_ACCT

Position 4

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

FUND_CODE

CHAR (5)

FUND CODE

CI_DST_CODE_EFF

FUND_CD

Varchar2(12)

Null unless fund accounting is enabled in Oracle Utilities Customer Care and Billing.

CLASS_FLD

CHAR (5)

CLASS FIELD

CI_FT_GL

GL_ACCT

Position 5

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

PROGRAM_CODE

CHAR (5)

PROGRAM CODE

CI_FT_GL

GL_ACCT

Position 6

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

BUDGET_REF

CHAR (8)

BUDGET REFERENCE

CI_FT_GL

GL_ACCT

Position 12

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

AFFILIATE

CHAR (5)

AFFILIATE

CI_FT_GL

GL_ACCT

Position 9

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

AFFILIATE_INTRA1

CHAR (10)

FUND AFFILIATE

CI_FT_GL

GL_ACCT

Position 10

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

AFFILIATE_INTRA2

CHAR (10)

OPERATING UNIT AFFILIATE

CI_FT_GL

GL_ACCT

Position 11

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

CHARTFIELD1

CHAR (10)

CHARTFIELD1

CI_FT_GL

GL_ACCT

Position 13

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

CHARTFIELD2

CHAR (10)

CHARTFIELD2

CI_FT_GL

GL_ACCT

Position 14

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

CHARTFIELD3

CHAR (10)

CHARTFIELD3

CI_FT_GL

GL_ACCT

Position 15

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

PROJECT_ID

CHAR (15)

PROJECT

CI_FT_GL

GL_ACCT

Position 8

Varchar2(48)

Use (dot) as the delimiter to extract this information from Gl_Acct. 2 dots (..) indicates skip or null.

CURRENCY_CD

CHAR (3)

CURRENCY CODE

CI_FT

CURRENCY_CD

CHAR(3)

STATISTICS_CODE

CHAR (3)

STATISTICS CODE

CI_DST_CODE_EFF

STATISTICS_CODE

CHAR(8)

FOREIGN_CURRENCY

CHAR (3)

FOREIGN CURRENCY CODE

This field is not mapped.

Leave the field Blank

RT_TYPE

CHAR (5)

RATE TYPE

This field is not mapped.

Leave the field Blank.

RATE_MULT

SIGNNBR (7.8)

RATE MULTIPLIER

This field is mapped with '1' in BPEL process.

RATE_DIV

NBR (7.8)

RATE DIVISOR

This field is mapped with '1' in BPEL process.

MONETARY_AMOUNT

SIGNNBR (23.3)

MONETARY AMOUNT

CI_FT_GL

AMOUNT

NUMBER(15,2)

Base Currency Amount

OREIGN_AMOUNT

SIGNNBR (23.3)

FOREIGN AMOUNT

This field is not mapped.

STATISTIC_AMOUNT

SIGNNBR (13.2)

STATISTIC AMOUNT

CI_FT_GL

STATISTIC_AMOUNT

NUMBER(15,2)

MOVEMENT_FLAG

CHAR (1)

MOVEMENT FLAG

Defines the sign of the Amount when debit/credit options are separate.

Value = N

DOC_TYPE

CHAR (8)

DOCUMENT TYPE

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

DOC_SEQ_NBR

CHAR (12)

DOCUMENT SEQUENCE NUMBER

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

DOC_SEQ_DATE

DATE (10)

DOCUMENT SEQUENCE DATE

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

JRNL_LN_REF

CHAR (10)

JOURNAL LINE REFERENCE

CI_FT_PROC

BATCH_NBR

NUMBER (10)

Identifies the Source of the Transaction.

Mapped to Oracle Utilities Customer Care and Billing Batch Number for reference.

LINE_DESCR

CHAR (30)

JOURNAL LINE DESCRIPTION

Derived in BPEL. Describes a transaction.

IU_SYS_TRAN_CD

CHAR (8)

SYSTEM TRANSACTION

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

IU_TRAN_CD

CHAR (8)

TRANSACTION CODE

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

IU_ANCHOR_FLG

CHAR (1)

INTERUNIT ANCHOR

This field is not mapped to Oracle Utilities Customer Care and Billing field.

Blank

GL_DISTRIB_STATUS

CHAR (1)

DISTRIBUTION STATUS

Derived from BPEL. Value = N (Ready for Distribution).

PROCESS_INSTANCE

NBR (10)

PROCESS INSTANCE

Populated by Journal Generator

Initial value Derived from BPEL.

Value =0

DTTM_STAMP

DATE(10)

DTTM_STAMP

CI_FT

ACCOUNTING_DT

DATE

Date used by GL to define the accounting period into which the Financial Transaction is booked.

AP Request

This section covers the following:

•

PS_VCHR_HDR_STG

•

PS_VCHR_LINE_STG

•

PS_VCHR_DIST_STG

•

PS_VCHR_PYMT_STG

•

PS_VCHR_VNDR_STG

•

PS_VCHR_BANK_STG

PS_VCHR_HDR_STG

Columns in PS_VCHR_HDR_STG

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Columns

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2 (5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

CHAR (16)

VCHR_BLD_KEY_C1

VARCHAR2 (25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

CHAR (5)

VCHR_BLD_KEY_C2

VARCHAR2 (25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

VCHR_BLD_KEY_N1

NUMBER (10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER (10)

Vchr Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2 (8)

Voucher ID

Derived from BPEL. Value = Next

VOUCHER_STYLE

VARCHAR2 (4)

Voucher Style

Derived from BPEL. Value = SGLP

INVOICE_ID

VARCHAR2 (30)

Invoice Number

CI_ADJ

ADJ_ID

CHAR (12)

INVOICE_DT

DATE

Invoice Date

CI_ADJ

CRE_DT

DATE

VENDOR_SETID

VARCHAR2 (5)

Vendor SetID

Derived from BPEL. Value = SHARE

VENDOR_ID

VARCHAR2 (10)

Vendor ID

Derived from BPEL. Value = CCB VENDOR

VNDR_LOC

VARCHAR2 (10)

Vendor Location

Derived from BPEL. Value = 1

ADDRESS_SEQ_NUM

NUMBER (38)

Address Sequence Number

Derived from BPEL. Value = 1

GRP_AP_ID

VARCHAR2 (10)

Control Group ID

Value = Blank

ORIGIN

VARCHAR2 (3)

Origin

Value = Blank

OPRID

VARCHAR2 (30)

User ID

Derived from BPEL. Voucher Build Process inserts the OPRID. Value = Blank

ACCOUNTING_DT

DATE

Accounting Date

Value = Null

POST_VOUCHER

VARCHAR2 (1)

Post Voucher Now

Value = Blank

DST_CNTRL_ID

VARCHAR2 (10)

Accounting Template

CI_DST_CD_CHAR

CHAR_VAL

CHAR (16)

VOUCHER_ID_RELATED

VARCHAR2 (8)

Related Voucher

Value = Blank

GROSS_AMT

NUMBER (26,3)

Gross Invoice Amount

CI_ADJ

ADJ_AMT

NUMBER (15,2)

DSCNT_AMT

NUMBER (26,3)

Discount Amount

Derived from BPEL. Value = 0

TAX_EXEMPT

VARCHAR2 (1)

Tax Exempt Flag

Derived from BPEL. Value = Y

SALETX_AMT

NUMBER (26,3)

Sales Tax Amount

Derived from BPEL. Value = 0

FREIGHT_AMT

NUMBER (26,3)

Freight Amount

Derived from BPEL. Value = 0

MISC_AMT

NUMBER (26,3)

Misc Charge Amount

Derived from BPEL. Value = 0

PYMNT_TERMS_CD

VARCHAR2 (5)

Payment Terms ID

Derived from BPEL. Value = 07

ENTERED_DT

DATE

Entered on

Derived from BPEL. Value = System Date

TXN_CURRENCY_CD

VARCHAR2 (3)

Transaction Currency

CI_ADJ

CURRENCY_CD

CHAR (3)

RT_TYPE

VARCHAR2 (5)

Rate Type

Derived from BPEL. Value = CRRNT

RATE_MULT

NUMBER (15,8)

Rate Multiplier

Derived from BPEL. Value = 1

RATE_DIV

NUMBER (15,8)

Rate Divisor

Derived from BPEL. Value = 1

VAT_ENTRD_AMT

NUMBER (26,3)

Entered VAT Amount

Derived from BPEL. Value = 0

MATCH_ACTION

VARCHAR2 (1)

Match Action

Derived from BPEL. Value = N

CUR_RT_SOURCE

VARCHAR2 (1)

Exchange Rate Source

Derived from BPEL. Value = T

DSCNT_AMT_FLG

VARCHAR2 (1)

Discount Amount Control

Derived from BPEL. Value = T

DUE_DT_FLG

VARCHAR2 (1)

Due Date Control

Derived from BPEL. Value = T

VCHR_APPRVL_FLG

VARCHAR2 (1)

Voucher Approval Flag

Derived from BPEL. Value = P

BUSPROCNAME

VARCHAR2 (30)

Business Process Name

Derived from BPEL. Value = Blank

APPR_RULE_SET

VARCHAR2 (30)

Approval Rule Set

Derived from BPEL. Value = Blank

VAT_DCLRTN_POINT

VARCHAR2 (1)

Declaration Point

Derived from BPEL. Value = Blank

VAT_CALC_TYPE

VARCHAR2 (1)

Calculation Type

Derived from BPEL. Value = E

VAT_CALC_GROSS_NET

VARCHAR2 (1)

Calculate at Gross or Net

Derived from BPEL. Value = Blank

VAT_RECALC_FLG

VARCHAR2 (1)

Recalculate at Payment

Derived from BPEL. Value = Blank

VAT_CALC_FRGHT_FLG

VARCHAR2 (1)

Include Freight

Derived from BPEL. Value = N

VAT_TREATMENT_GRP

VARCHAR2 (4)

VAT Treatment Group

Derived from BPEL. Value = Blank

COUNTRY_SHIP_FROM

VARCHAR2 (3)

Ship From Country

Derived from BPEL. Value = Blank

STATE_SHIP_FROM

VARCHAR2 (6)

Ship From State

Derived from BPEL. Value = Blank

COUNTRY_SHIP_TO

VARCHAR2 (3)

Ship to Country

Derived from BPEL. Value = Blank

STATE_SHIP_TO

VARCHAR2 (6)

Ship to State

Derived from BPEL. Value = Blank

COUNTRY_VAT_BILLFR

VARCHAR2 (3)

Seller Registration Country

Derived from BPEL. Value = Blank

COUNTRY_VAT_BILLTO

VARCHAR2 (3)

Buyer Registration Country

Derived from BPEL. Value = Blank

VAT_EXCPTN_CERTIF

VARCHAR2 (20)

VAT Certificate ID

Derived from BPEL. Value = Blank

VAT_ROUND_RULE

VARCHAR2 (1)

VAT Rounding Rule

Derived from BPEL. Value = Blank

COUNTRY_LOC_SELLER

VARCHAR2 (3)

Seller Location Country

Derived from BPEL. Value = Blank

STATE_LOC_SELLER

VARCHAR2 (6)

Seller Location State

Derived from BPEL. Value = Blank

COUNTRY_LOC_BUYER

VARCHAR2 (3)

Buyer Location Country

Derived from BPEL. Value = Blank

STATE_LOC_BUYER

VARCHAR2 (6)

Buyer Location State

Derived from BPEL. Value = Blank

COUNTRY_VAT_SUPPLY

VARCHAR2 (3)

VAT Place of Supply Country

Derived from BPEL. Value = Blank

STATE_VAT_SUPPLY

VARCHAR2 (6)

VAT Place of Supply State

Derived from BPEL. Value = Blank

COUNTRY_VAT_PERFRM

VARCHAR2 (3)

Service Performed Country

Derived from BPEL. Value = Blank

STATE_VAT_PERFRM

VARCHAR2 (6)

Service Performed State

Derived from BPEL. Value = Blank

STATE_VAT_DEFAULT

VARCHAR2 (6)

Defaulting State

Derived from BPEL. Value = Blank

PREPAID_REF

VARCHAR2 (10)

Prepayment Reference

Derived from BPEL. Value = Blank

PREPAID_AUTO_APPLY

VARCHAR2 (1)

Automatically Apply Prepayment

Derived from BPEL. Value = Blank

DESCR254_MIXED

VARCHAR2 (254)

More Information

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

EIN_FEDERAL

VARCHAR2 (9)

EIN Federal

Derived from BPEL. Value = Blank

EIN_STATE_LOCAL

VARCHAR2 (20)

EIN State Local

Derived from BPEL. Value = Blank

PROCESS_INSTANCE

NUMBER (10)

Process Instance

Derived from BPEL. Value = 0

IN_PROCESS_FLG

VARCHAR2 (1)

In Process

Derived from BPEL. Value = N

BUSINESS_UNIT_PO

VARCHAR2 (5)

PO Business Unit

Derived from BPEL. Value = Blank

PO_ID

VARCHAR2 (10)

PO Number

Derived from BPEL. Value = Blank

PACKSLIP_NO

VARCHAR2 (22)

Packing Slip Number

Derived from BPEL. Value = Blank

PAY_TRM_BSE_DT_OPT

VARCHAR2 (1)

Payment Terms Basis Date Type

Derived from BPEL. Value = I

VAT_CALC_MISC_FLG

VARCHAR2 (1)

Include Miscellaneous

Derived from BPEL. Value = N

IMAGE_REF_ID

VARCHAR2 (12)

Image Reference ID

Derived from BPEL. Value = Blank

IMAGE_DATE

DATE

Image Date

Derived from BPEL. Value = Null

PAY_SCHEDULE_TYPE

VARCHAR2 (3)

Pay Schedule Type

Derived from BPEL. Value = Blank

TAX_GRP

VARCHAR2 (4)

Tax Group Code

Derived from BPEL. Value = Blank

TAX_PYMNT_TYPE

VARCHAR2 (5)

Tax Payment Type Code

Derived from BPEL. Value = Blank

INSPECT_DT

DATE

Inspection Date

Derived from BPEL. Value = Null

INV_RECPT_DT

DATE

Invoice Receipt Date

Derived from BPEL. Value = Null

RECEIPT_DT

DATE

Received Date

Derived from BPEL. Value = Null

BILL_OF_LADING

VARCHAR2 (30)

Bill of Lading

Derived from BPEL. Value = Blank

CARRIER_ID

VARCHAR2 (10)

Carrier ID

Derived from BPEL. Value = Blank

DOC_TYPE

VARCHAR2 (8)

Document Type

Derived from BPEL. Value = Blank

DSCNT_DUE_DT

DATE

Discount Due Date

Derived from BPEL. Value = Null

DSCNT_PRORATE_FLG

VARCHAR2 (1)

Prorate Discount

Derived from BPEL. Value = Blank

DUE_DT

DATE

Due Date

Derived from BPEL. Value = Null

ECQUEUEINSTANCE

NUMBER (38)

ECQUEUEINSTANCE - EC Queue Instance

Derived from BPEL. Value = 0

ECTRANSID

VARCHAR2 (15)

EC Transaction ID

Derived from BPEL. Value = Blank

FRGHT_CHARGE_CODE

VARCHAR2 (10)

Freight Charge

Derived from BPEL. Value = Blank

LC_ID

VARCHAR2 (12)

Letter of Credit ID

Derived from BPEL. Value = Blank

MISC_CHARGE_CODE

VARCHAR2 (10)

MISC_CHARGE_CODE - Miscellaneous Charge

Derived from BPEL. Value = Blank

REMIT_ADDR_SEQ_NUM

NUMBER (38)

Remitting Address

Derived from BPEL. Value = 0

SALETX_CHARGE_CODE

VARCHAR2 (10)

Sales Tax Charge

Derived from BPEL. Value = Blank

VCHR_BLD_CODE

VARCHAR2 (6)

Voucher Build Code

Derived from BPEL. Value = Blank

BUSINESS_UNIT_AR

VARCHAR2 (5)

AR Business Unit

Derived from BPEL. Value = Blank

CUST_ID

VARCHAR2 (15)

Customer ID

Derived from BPEL. Value = Blank

ITEM

VARCHAR2 (30)

Item ID

Derived from BPEL. Value = Blank

ITEM_LINE

NUMBER (38)

Item Line

Derived from BPEL. Value = 0

ERS_INV_SEQ

NUMBER (38)

Invoice Sequence

Derived from BPEL. Value = 0

LS_KEY

NUMBER (15)

Lease Key

Derived from BPEL. Value = 0

VCHR_SRC

VARCHAR2 (4)

VCHR_SRC - Voucher Source

Derived from BPEL. Value = XML

VAT_EXCPTN_TYPE

VARCHAR2 (1)

Exception Type

Derived from BPEL. Value = Blank

TERMS_BASIS_DT

DATE

Payment Terms Basis Date

CI_ADJ

CRE_DT

DATE

BUSINESS_UNIT_AM

VARCHAR2 (5)

AM Business Unit

Derived from BPEL. Value = Blank

ASSET_ID

VARCHAR2 (12)

Asset ID

Derived from BPEL. Value = Blank

LEASE_ID

VARCHAR2 (30)

Lease ID

Derived from BPEL. Value = Blank

CLAIM_NO

VARCHAR2 (30)

Claim Number

Derived from BPEL. Value = Blank

POLCY_NUM

VARCHAR2 (30)

Policy Number

Derived from BPEL. Value = Blank

ENDORSER_PARTY

VARCHAR2 (40)

Endorser Party

Derived from BPEL. Value = Blank

BUSINESS_UNIT_BI

VARCHAR2 (5)

Business Unit BI

Derived from BPEL. Value = Blank

BI_INVOICE

VARCHAR2 (22)

BI Invoice

Derived from BPEL. Value = Blank

CUSTOM_C100_A1

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_A2

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_A3

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_A4

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_DATE_A

Date

Custom Date Field

Value = Null

CUSTOM_C1_A

VARCHAR2 (1)

Custom Field

Derived from BPEL. Value = Blank

VAT_NRCVR_CHRG_CD

VARCHAR2 (10)

Prorate VAT Non-Recovery

Derived from BPEL. Value = Blank

VAT_CF_ANLSYS_TYPE

VARCHAR2 (1)

VAT Chartfield Analysis

Derived from BPEL. Value = Blank

USER_VCHR_CHAR1

VARCHAR2 (1)

User Character Field

Derived from BPEL. Value = Blank

USER_VCHR_CHAR2

VARCHAR2 (1)

User Character Field 2

Derived from BPEL. Value = Blank

USER_VCHR_DEC

NUMBER (26,3)

USER_VCHR_DEC - User Amount Field

Derived from BPEL. Value = 0

USER_VCHR_DATE

DATE

User Date

Value = Null

USER_VCHR_NUM1

NUMBER (38)

USER_VCHR_NUM1 - User Number field

Derived from BPEL. Value = 0

USER_HDR_CHAR1

VARCHAR2 (1)

Header User Field

Derived from BPEL. Value = Blank

PS_VCHR_LINE_STG

Columns in PS_VCHR_LINE_

STG

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Columns

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2 (5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

CHAR (16)

VCHR_BLD_KEY_C1

VARCHAR2 (25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

CHAR (5)

VCHR_BLD_KEY_C2

VARCHAR2 (25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

VCHR_BLD_KEY_N1

NUMBER (10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER (10)

Vchr Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2 (8)

Voucher ID

Derived from BPEL. Value = NEXT

VOUCHER_LINE_NUM

NUMBER (38)

Voucher Line Number

Derived from BPEL. Value = 0

BUSINESS_UNIT_PO

VARCHAR2 (5)

PO Business Unit

Derived from BPEL. Value = Blank

PO_ID

VARCHAR2 (10)

PO Number

Derived from BPEL. Value = Blank

LINE_NBR

NUMBER (38)

Line Number

Derived from BPEL. Value = 0

SCHED_NBR

NUMBER (38)

Schedule Number

Derived from BPEL. Value = 0

DESCR

VARCHAR2 (30)

Description

Derived from BPEL. Value = Blank

MERCHANDISE_AMT

NUMBER (26,3)

Merchandise Amt

CI_ADJ

ADJ_AMT

NUMBER (15,2)

ITM_SETID

VARCHAR2 (5)

Item SetID

Derived from BPEL. Value = Blank

INV_ITEM_ID

VARCHAR2 (18)

Item ID

Derived from BPEL. Value = Blank

QTY_VCHR

NUMBER (15,4)

Quantity Vouchered

Derived from BPEL. Value = 0

STATISTIC_AMOUNT

NUMBER (15,2)

Statistic Amount

Derived from BPEL. Value = 0

UNIT_OF_MEASURE

VARCHAR2 (3)

Unit of Measure

Derived from BPEL. Value = Blank

UNIT_PRICE

NUMBER (15,5)

Unit Price

Derived from BPEL. Value = 0

DSCNT_APPL_FLG

VARCHAR2 (1)

Apply Discount

Derived from BPEL. Value = Blank

TAX_CD_VAT

VARCHAR2 (8)

VAT Code

Derived from BPEL. Value = Blank

BUSINESS_UNIT_RECV

VARCHAR2 (5)

Receiving Business Unit

Derived from BPEL. Value = Blank

RECEIVER_ID

VARCHAR2 (10)

Receipt Number

Derived from BPEL. Value = Blank

RECV_LN_NBR

NUMBER (38)

Receipt Line

Derived from BPEL. Value = 0

RECV_SHIP_SEQ_NBR

NUMBER (38)

Receiver Shipping Sequence

Derived from BPEL. Value = 0

MATCH_LINE_OPT

VARCHAR2 (1)

Match Line Option

Derived from BPEL. Value = N

DISTRIB_MTHD_FLG

VARCHAR2 (1)

Distribute by

Derived from BPEL. Value = A

SHIPTO_ID

VARCHAR2 (10)

Ship To Location

Derived from BPEL. Value = Blank

SUT_BASE_ID

VARCHAR2 (10)

Sales/Use Tax Destination

Derived from BPEL. Value = Blank

TAX_CD_SUT

VARCHAR2 (8)

Tax Code - Sales and Use Tax

Derived from BPEL. Value = Blank

ULTIMATE_USE_CD

VARCHAR2 (8)

Ultimate Use Code

Derived from BPEL. Value = Blank

SUT_EXCPTN_TYPE

VARCHAR2 (1)

Sales/Use Tax Exception Type

Derived from BPEL. Value = Blank

SUT_EXCPTN_CERTIF

VARCHAR2 (20)

Sales/Use Tax Exception Certif

Derived from BPEL. Value = Blank

SUT_APPLICABILITY

VARCHAR2 (1)

Sales/Use Tax Applicability

Derived from BPEL. Value = Blank

VAT_APPLICABILITY

VARCHAR2 (1)

VAT Applicability

Derived from BPEL. Value = Blank

VAT_TXN_TYPE_CD

VARCHAR2 (4)

VAT Transaction Type

Derived from BPEL. Value = Blank

VAT_USE_ID

VARCHAR2 (6)

VAT Use Type

Derived from BPEL. Value = Blank

ADDR_SEQ_NUM_SHIP

NUMBER (38)

Address Sequence Number

Derived from BPEL. Value = 0

BUS_UNIT_RELATED

VARCHAR2 (5)

AP Business Unit

Derived from BPEL. Value = Blank

VOUCHER_ID_RELATED

VARCHAR2 (8)

Related Voucher

Derived from BPEL. Value = Blank

VENDOR_ID

VARCHAR2 (10)

Vendor ID

Derived from BPEL. Value = CCBVENDOR

VNDR_LOC

VARCHAR2 (10)

Vendor Location

Derived from BPEL. Value = 1

DESCR254_MIXED

VARCHAR2 (254)

More Information

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

SPEEDCHART_KEY

VARCHAR2 (10)

SpeedChart Key

Derived from BPEL. Value = Blank

BUSINESS_UNIT_GL

VARCHAR2 (5)

GL Business Unit

Derived from BPEL. Value = CCB

ACCOUNT

VARCHAR2 (10)

Account

CI_DST_CODE_EFF

GL_ACCT (Position 1)

VARCHAR2 (254)

ALTACCT

VARCHAR2 (10)

Alternate Account

Derived from BPEL. Value = Blank

OPERATING_UNIT

VARCHAR2 (8)

Operating Unit

Derived from BPEL. Value = Blank

PRODUCT

VARCHAR2 (6)

Product

Derived from BPEL. Value = Blank

FUND_CODE

VARCHAR2 (5)

Fund Code

Derived from BPEL. Value = Blank

CLASS_FLD

VARCHAR2 (5)

Class Field

Derived from BPEL. Value = Blank

PROGRAM_CODE

VARCHAR2 (5)

Program Code

Derived from BPEL. Value = Blank

BUDGET_REF

VARCHAR2 (8)

Budget Reference

Derived from BPEL. Value = Blank

AFFILIATE

VARCHAR2 (5)

Affiliate

Derived from BPEL. Value = Blank

AFFILIATE_INTRA1

VARCHAR2 (10)

Fund Affiliate

Derived from BPEL. Value = Blank

AFFILIATE_INTRA2

VARCHAR2 (10)

Operating Unit Affiliate

Derived from BPEL. Value = Blank

CHARTFIELD1

VARCHAR2 (10)

ChartField 1

Derived from BPEL. Value = Blank

CHARTFIELD2

VARCHAR2 (10)

ChartField 2

Derived from BPEL. Value = Blank

CHARTFIELD3

VARCHAR2 (10)

ChartField 3

Derived from BPEL. Value = Blank

DEPTID

VARCHAR2 (10)

Department

Derived from BPEL. Value = Blank

PROJECT_ID

VARCHAR2 (15)

Project

Derived from BPEL. Value = Blank

BUSINESS_UNIT_PC

VARCHAR2 (5)

PC Business Unit

Derived from BPEL. Value = Blank

ACTIVITY_ID

VARCHAR2 (15)

Activity

Derived from BPEL. Value = Blank

ANALYSIS_TYPE

VARCHAR2 (3)

Analysis Type

Derived from BPEL. Value = Blank

RESOURCE_TYPE

VARCHAR2 (5)

Source Type

Derived from BPEL. Value = Blank

RESOURCE_CATEGORY

VARCHAR2 (5)

Category

Derived from BPEL. Value = Blank

RESOURCE_SUB_CAT

VARCHAR2 (5)

Subcategory

Derived from BPEL. Value = Blank

ECQUEUEINSTANCE

NUMBER (38)

EC Queue Instance

Derived from BPEL. Value = 0

ECTRANSID

VARCHAR2 (15)

EC Transaction ID

Derived from BPEL. Value = Blank

TAX_DSCNT_FLG

VARCHAR2 (1)

Include Discount

Derived from BPEL. Value = Blank

TAX_FRGHT_FLG

VARCHAR2 (1)

Include Freight

Derived from BPEL. Value = Blank

TAX_MISC_FLG

VARCHAR2 (1)

Include Misc Charges

Derived from BPEL. Value = Blank

TAX_VAT_FLG

VARCHAR2 (1)

Include VAT

Derived from BPEL. Value = Blank

PHYSICAL_NATURE

VARCHAR2 (1)

Physical Nature

Derived from BPEL. Value = S

VAT_RCRD_INPT_FLG

VARCHAR2 (1)

Record Input VAT

Derived from BPEL. Value = Blank

VAT_RCRD_OUTPT_FLG

VARCHAR2 (1)

Record Output VAT

Derived from BPEL. Value = Blank

VAT_TREATMENT

VARCHAR2 (4)

VAT Treatment

Derived from BPEL. Value = Blank

VAT_SVC_SUPPLY_FLG

VARCHAR2 (1)

VAT Place of Supply

Derived from BPEL. Value = Blank

VAT_SERVICE_TYPE

VARCHAR2 (1)

VAT Service Type

Derived from BPEL. Value = Blank

COUNTRY_LOC_BUYER

VARCHAR2 (3)

Buyer Location Country

Derived from BPEL. Value = Blank

STATE_LOC_BUYER

VARCHAR2 (6)

Buyer Location State

Derived from BPEL. Value = Blank

COUNTRY_LOC_SELLER

VARCHAR2 (3)

Seller Location Country

Derived from BPEL. Value = Blank

STATE_LOC_SELLER

VARCHAR2 (6)

Seller Location State

Derived from BPEL. Value = Blank

COUNTRY_VAT_SUPPLY

VARCHAR2 (3)

VAT Place of Supply Country

Derived from BPEL. Value = Blank

STATE_VAT_SUPPLY

VARCHAR2 (6)

VAT Place of Supply State

Derived from BPEL. Value = Blank

COUNTRY_VAT_PERFRM

VARCHAR2 (3)

Service Performed Country

Derived from BPEL. Value = Blank

STATE_VAT_PERFRM

VARCHAR2 (6)

Service Performed State

Derived from BPEL. Value = Blank

STATE_SHIP_FROM

VARCHAR2 (6)

Ship From State

Derived from BPEL. Value = Blank

STATE_VAT_DEFAULT

VARCHAR2 (6)

Defaulting State

Derived from BPEL. Value = Blank

REQUESTOR_ID

VARCHAR2 (30)

Requester

Derived from BPEL. Value = Blank

VAT_ENTRD_AMT

NUMBER (26,3)

Entered VAT Amount

Derived from BPEL. Value = 0

VAT_RECEIPT

VARCHAR2 (1)

No VAT Receipt

Derived from BPEL. Value = Blank

VAT_RGSTRN_SELLER

VARCHAR2 (12)

Seller VAT Registration

Derived from BPEL. Value = Blank

IST_TXN_FLG

VARCHAR2 (1)

Intrastat Transaction Flag

Derived from BPEL. Value = Blank

TRANS_DT

DATE

Transaction Date

Derived from BPEL. Value = Null

WTHD_SW

VARCHAR2 (1)

Withholding Applicable

Derived from BPEL. Value = Blank

WTHD_CD

VARCHAR2 (5)

Withholding Code

Derived from BPEL. Value = Blank

MFG_ID

VARCHAR2 (50)

Manufacturer ID

Derived from BPEL. Value = Blank

USER_VCHR_CHAR1

VARCHAR2 (1)

User Character Field

Derived from BPEL. Value = Blank

USER_VCHR_CHAR2

VARCHAR2 (1)

User Character Field 2

Derived from BPEL. Value = Blank

USER_VCHR_DEC

NUMBER (26,3)

User Amount Field

Derived from BPEL. Value = 0

USER_VCHR_DATE

DATE

User Date

Derived from BPEL. Value = Null

USER_VCHR_NUM1

NUMBER (38)

User Number field

Derived from BPEL. Value = 0

USER_LINE_CHAR1

VARCHAR2 (1)

Line User Field

Derived from BPEL. Value = Blank

CUSTOM_C100_B1

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_B2

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_B3

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_B4

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_DATE_B

DATE

Custom Date Field

Null

CUSTOM_C1_B

VARCHAR2 (1)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_C1

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_C2

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_C3

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_DATE_C1

DATE

Custom Date Field

Null

CUSTOM_DATE_C2

DATE

Custom Date Field

Null

CUSTOM_C1_C

VARCHAR2 (1)

Custom Field

Derived from BPEL. Value = Blank

PACKSLIP_NO

VARCHAR2 (22)

Pack Slip Number

Derived from BPEL. Value = Blank

BUSINESS_UNIT_BI

VARCHAR2 (5)

Business Unit BI

Derived from BPEL. Value = Blank

BI_INVOICE

VARCHAR2 (22)

BI Invoice

Derived from BPEL. Value = Blank

LINE_SEQ_NUM

NUMBER

Line Sequence Number

Derived from BPEL. Value = 0

CATEGORY_ID

VARCHAR2 (5)

Category ID

Derived from BPEL. Value = Blank

USER_SCHED_CHAR1

VARCHAR2 (1)

Schedule User Field

Derived from BPEL. Value = Blank

VAT_RVRSE_CHG_GDS

VARCHAR2 (1)

Domestic Reverse Charge Goods

Derived from BPEL. Value = Blank

PS_VCHR_DIST_STG

Columns in PS_VCHR_DIST_STG

Date Type

Description

Oracle Utilities Customer Care and Billing Table

Columns

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2 (5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

CHAR (16)

VCHR_BLD_KEY_C1

VARCHAR2 (25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

CHAR (5)

VCHR_BLD_KEY_C2

VARCHAR2 (25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

VCHR_BLD_KEY_N1

NUMBER (10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER (10)

Voucher Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2 (8)

Voucher ID

Derived from BPEL. Value = NEXT

VOUCHER_LINE_NUM

NUMBER (38)

Voucher Line Number

Derived from BPEL. Value = 0

DISTRIB_LINE_NUM

NUMBER (38)

Distribution Line

Derived from BPEL. Value = 0

BUSINESS_UNIT_GL

VARCHAR2 (5)

GL Business Unit

Derived from BPEL. Value = CCB

ACCOUNT

VARCHAR2 (10)

Account

CI_DST_CODE_EFF

GL_ACCT (Position 1)

VARCHAR2 (254)

ALTACCT

VARCHAR2 (10)

Alternate Account

Derived from BPEL. Value = Blank

DEPTID

VARCHAR2 (10)

Department

Derived from BPEL. Value = Blank

STATISTICS_CODE

VARCHAR2 (3)

Statistics Code

Derived from BPEL. Value = Blank

STATISTIC_AMOUNT

NUMBER (15,2)

Statistic Amount

Derived from BPEL. Value = 0

QTY_VCHR

NUMBER (15,4)

Quantity Vouchered

Derived from BPEL. Value = 0

DESCR

VARCHAR2 (30)

Description

Derived from BPEL. Value = Blank

MERCHANDISE_AMT

NUMBER (26,3)

Merchandise Amt

CI_ADJ

ADJ_AMT

NUMBER (15,2)

BUSINESS_UNIT_PO

VARCHAR2 (5)

PO Business Unit

Derived from BPEL. Value = Blank

PO_ID

VARCHAR2 (10)

PO Number

Derived from BPEL. Value = Blank

LINE_NBR

NUMBER (38)

Line Number

Derived from BPEL. Value = 0

SCHED_NBR

NUMBER (38)

Schedule Number

Derived from BPEL. Value = 0

PO_DIST_LINE_NUM

NUMBER (38)

PO Distribution Line Number

Derived from BPEL. Value = 0

BUSINESS_UNIT_PC

VARCHAR2 (5)

PC Business Unit

Derived from BPEL. Value = Blank

ACTIVITY_ID

VARCHAR2 (15)

Activity

Derived from BPEL. Value = Blank

ANALYSIS_TYPE

VARCHAR2 (3)

Analysis Type

Derived from BPEL. Value = Blank

RESOURCE_TYPE

VARCHAR2 (5)

Source Type

Derived from BPEL. Value = Blank

RESOURCE_CATEGORY

VARCHAR2 (5)

Category

Derived from BPEL. Value = Blank

RESOURCE_SUB_CAT

VARCHAR2 (5)

Subcategory

Derived from BPEL. Value = Blank

ASSET_FLG

VARCHAR2 (1)

Assets Applicable

Derived from BPEL. Value = N

BUSINESS_UNIT_AM

VARCHAR2 (5)

AM Business Unit

Derived from BPEL. Value = Blank

ASSET_ID

VARCHAR2 (12)

Asset Identification

Derived from BPEL. Value = Blank

PROFILE_ID

VARCHAR2 (10)

Asset Profile Id

Derived from BPEL. Value = Blank

COST_TYPE

VARCHAR2 (1)

Cost Type

Derived from BPEL. Value = Blank

VAT_TXN_TYPE_CD

VARCHAR2 (4)

VAT Transaction Type

Derived from BPEL. Value = Blank

BUSINESS_UNIT_RECV

VARCHAR2 (5)

Receiving Business Unit

Derived from BPEL. Value = Blank

RECEIVER_ID

VARCHAR2 (10)

Receipt Number

Derived from BPEL. Value = Blank

RECV_LN_NBR

NUMBER (38)

Receipt Line

Derived from BPEL. Value = 0

RECV_SHIP_SEQ_NBR

NUMBER (38)

Receiver Shipping Sequence

Derived from BPEL. Value = 0

RECV_DIST_LINE_NUM

NUMBER (38)

Receiver Distribution Line

Derived from BPEL. Value = 0

OPERATING_UNIT

VARCHAR2 (8)

Operating Unit

Derived from BPEL. Value = Blank

PRODUCT

VARCHAR2 (6)

Product

Derived from BPEL. Value = Blank

FUND_CODE

VARCHAR2 (5)

Fund Code

Derived from BPEL. Value = Blank

CLASS_FLD

VARCHAR2 (5)

Class Field

Derived from BPEL. Value = Blank

PROGRAM_CODE

VARCHAR2 (5)

Program Code

Derived from BPEL. Value = Blank

BUDGET_REF

VARCHAR2 (8)

Budget Reference

Derived from BPEL. Value = Blank

AFFILIATE

VARCHAR2 (5)

Affiliate

Derived from BPEL. Value = Blank

AFFILIATE_INTRA1

VARCHAR2 (10)

Fund Affiliate

Derived from BPEL. Value = Blank

AFFILIATE_INTRA2

VARCHAR2 (10)

Operating Unit Affiliate

Derived from BPEL. Value = Blank

CHARTFIELD1

VARCHAR2 (10)

ChartField 1

Derived from BPEL. Value = Blank

CHARTFIELD2

VARCHAR2 (10)

ChartField 2

Derived from BPEL. Value = Blank

CHARTFIELD3

VARCHAR2 (10)

ChartField 3

Derived from BPEL. Value = Blank

PROJECT_ID

VARCHAR2 (15)

Project

Derived from BPEL. Value = Blank

BUDGET_DT

DATE

Budget Date

Value = Null

ENTRY_EVENT

VARCHAR2 (10)

Entry Event

Derived from BPEL. Value = Blank

ECQUEUEINSTANCE

NUMBER (38)

EC Queue Instance

Derived from BPEL. Value = 0

ECTRANSID

VARCHAR2 (15)

EC Transaction ID

Derived from BPEL. Value = Blank

JRNL_LN_REF

VARCHAR2 (10)

Journal Line Reference

Derived from BPEL. Value = Blank

VAT_APORT_CNTRL

VARCHAR2 (1)

VAT Apportionment Control

Derived from BPEL. Value = Blank

USER_VCHR_CHAR1

VARCHAR2 (1)

User Character Field

Derived from BPEL. Value = Blank

USER_VCHR_CHAR2

VARCHAR2 (1)

User Character Field 2

Derived from BPEL. Value = Blank

USER_VCHR_DEC

NUMBER (26,3)

User Amount Field

Derived from BPEL. Value = 0

USER_VCHR_DATE

DATE

User Date

Value = Null

USER_VCHR_NUM1

NUMBER (38)

User Number field

Derived from BPEL. Value = 0

USER_DIST_CHAR1

VARCHAR2 (1)

Distribution User Field

Derived from BPEL. Value = Blank

CUSTOM_C100_D1

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_D2

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_D3

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_C100_D4

VARCHAR2 (100)

Custom Field

Derived from BPEL. Value = Blank

CUSTOM_DATE_D

Date

Custom Date Field

Value = Null

CUSTOM_C1_D

VARCHAR2 (1)

Custom Field

Derived from BPEL. Value = Blank

OPEN_ITEM_KEY

VARCHAR2 (30)

Open Item Key

Derived from BPEL. Value = Blank

VAT_RECOVERY_PCT

NUMBER (5,2)

VAT Recovery Percent

Derived from BPEL. Value = 0

VAT_REBATE_PCT

NUMBER (5,2)

VAT Rebate Percent

Derived from BPEL. Value = 0

VAT_CALC_AMT

NUMBER (26,3)

VAT Calculated Amount

Derived from BPEL. Value = 0

VAT_BASIS_AMT

NUMBER (26,3)

VAT Basis Amt

Derived from BPEL. Value = 0

VAT_RCVRY_AMT

NUMBER (26,3)

VAT Recovery Amt

Derived from BPEL. Value = 0

VAT_NRCVR_AMT

NUMBER (26,3)

VAT Non Recoverable

Derived from BPEL. Value = 0

VAT_REBATE_AMT

NUMBER (26,3)

VAT Rebate Amt

Derived from BPEL. Value = 0

VAT_TRANS_AMT

NUMBER (26,3)

VAT Transaction Amount

Derived from BPEL. Value = 0

TAX_CD_VAT_PCT

NUMBER (7,4)

VAT Tax Code Aggregate Pct

Derived from BPEL. Value = 0

VAT_INV_AMT

NUMBER (26,3)

VAT Invoice Amount

Derived from BPEL. Value = 0

VAT_NONINV_AMT

NUMBER (26,3)

VAT Non-Invoice Amount

Derived from BPEL. Value = 0

BUSINESS_UNIT_WO

VARCHAR2 (5)

Business Unit

Derived from BPEL. Value = Blank

WO_ID

VARCHAR2 (10)

Work Order ID

Derived from BPEL. Value = Blank

WO_TASK_ID

NUMBER (38)

Task Number

Derived from BPEL. Value = 0

RSRC_TYPE

VARCHAR2 (1)

Resource Type

Derived from BPEL. Value = Blank

RES_LN_NBR

NUMBER (38)

Resource Line No.

Derived from BPEL. Value = 0

PS_VCHR_PYMT_STG

Columns in PS_VCHR_PYMT_STG

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Columns

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2 (5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

CHAR (16)

VCHR_BLD_KEY_C1

VARCHAR2 (25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

CHAR (5)

VCHR_BLD_KEY_C2

VARCHAR2 (25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

VCHR_BLD_KEY_N1

NUMBER (10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER (10)

Vchr Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2 (8)

Voucher ID

Derived from BPEL. Value = NEXT

PYMNT_CNT

NUMBER (38)

Payments

Derived from BPEL. Value = 1

BANK_CD

VARCHAR2 (5)

Bank Code

Derived from BPEL. Value = USBNK

BANK_ACCT_KEY

VARCHAR2 (4)

Bank Account

Derived from BPEL. Value = CHCK

PYMNT_METHOD

VARCHAR2 (3)

Payment Method

Derived from BPEL. Value = CHK

PYMNT_MESSAGE

VARCHAR2 (70)

Payment Message

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

CI_ADJ_APREQ

PYMNT_VCHR_PCT

NUMBER (31,15)

Payment Voucher Ratio

Derived from BPEL. Value = 0

PYMNT_HANDLING_CD

VARCHAR2 (2)

Payment Handling

Derived from BPEL. Value = RE

PYMNT_HOLD

VARCHAR2 (1)

Hold Payment

Derived from BPEL. Value = Blank

PYMNT_HOLD_REASON

VARCHAR2 (3)

Hold Reason

Derived from BPEL. Value = Blank

MESSAGE_CD

VARCHAR2 (6)

Message Code

Derived from BPEL. Value = Blank

PYMNT_GROSS_AMT

NUMBER (26,3)

Gross Payment Amount

CI_ADJ

ADJ_AMT

NUMBER (15,2)

PYMNT_SEPARATE

VARCHAR2 (1)

Separate Payment

Derived from BPEL. Value = N

This is retrieved from the configurable property CCB.PS.AP.PYMNT_SEPARATE.

SCHEDULED_PAY_DT

DATE

Scheduled to Pay

CI_ADJ_APREQ

SCHEDULED_PAY_DT

DATE

PYMNT_ACTION

VARCHAR2 (1)

Payment Action

Derived from BPEL. Value = S

PYMNT_ID_REF

VARCHAR2 (20)

Payment Reference

Derived from BPEL. Value = Blank

PYMNT_GROUP_CD

VARCHAR2 (2)

Pay Group Code

Derived from BPEL. Value = Blank

EFT_LAYOUT_CD

VARCHAR2 (10)

EFT Layout Code

Derived from BPEL. Value = Blank

PS_VCHR_VNDR_STG

Columns in PS_VCHR_VNDR_

STG

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Columns

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2 (5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

CHAR (16)

VCHR_BLD_KEY_C1

VARCHAR2 (25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

CHAR (5)

VCHR_BLD_KEY_C2

VARCHAR2 (25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

VCHR_BLD_KEY_N1

NUMBER (10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER (10)

Vchr Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2 (8)

Voucher ID

Derived from BPEL. Value = NEXT

NAME1

VARCHAR2 (40)

Name 1

CI_ADJ_APREQ

ENTITY_NAME

VARCHAR2 (64)

NAME2

VARCHAR2 (40)

Name 2

Derived from BPEL. Value = Blank

EMAILID

VARCHAR2 (70)

Email ID

Derived from BPEL. Value = Blank

COUNTRY

VARCHAR2 (3)

Country

CI_ADJ_APREQ

COUNTRY

CHAR (3)

ADDRESS1

VARCHAR2 (55)

Address Line 1

CI_ADJ_APREQ

ADDRESS1

VARCHAR2 (64)

ADDRESS2

VARCHAR2 (55)

Address Line 2

CI_ADJ_APREQ

ADDRESS2

VARCHAR2 (64)

ADDRESS3

VARCHAR2 (55)

Address Line 3

CI_ADJ_APREQ

ADDRESS3

VARCHAR2 (64)

ADDRESS4

VARCHAR2 (55)

Address Line 4

CI_ADJ_APREQ

ADDRESS4

VARCHAR2 (64)

CITY

VARCHAR2 (30)

City

CI_ADJ_APREQ

CITY

VARCHAR2 (30)

NUM1

VARCHAR2 (6)

Number 1

Derived from BPEL. Value = Blank

NUM2

VARCHAR2 (4)

Number 2

Derived from BPEL. Value = Blank

HOUSE_TYPE

VARCHAR2 (2)

House Type

Derived from BPEL. Value = Blank

ADDR_FIELD1

VARCHAR2 (2)

Address Field 1

Derived from BPEL. Value = Blank

ADDR_FIELD2

VARCHAR2 (4)

Address Field 2

Derived from BPEL. Value = Blank

ADDR_FIELD3

VARCHAR2 (4)

Address Field 3

Derived from BPEL. Value = Blank

COUNTY

VARCHAR2 (30)

County

CI_ADJ_APREQ

COUNTY

VARCHAR2 (30)

STATE

VARCHAR2 (6)

State

CI_ADJ_APREQ

STATE

CHAR (6)

POSTAL

VARCHAR2 (12)

Postal Code

CI_ADJ_APREQ

POSTAL

CHAR (12)

GEO_CODE

VARCHAR2 (11)

Tax Vendor Geographical Code

CI_ADJ_APREQ

GEO_CODE

CHAR (11)

IN_CITY_LIMIT

VARCHAR2 (1)

In City Limit

CI_ADJ_APREQ

IN_CITY_LIMIT

VARCHAR (1)

PS_VCHR_BANK_STG

PS_VCHR_BANK_STG

Data Type

Description

Oracle Utilities Customer Care and Billing Table

Column

Data Type

Remarks

BUSINESS_UNIT

VARCHAR2(5)

Business Unit

CI_CIS_DIV_CHAR

CHAR_VAL

VCHR_BLD_KEY_C1

VARCHAR2(25)

Voucher Build Key Char 1

CI_SA

CIS_DIVISION

VCHR_BLD_KEY_C2

VARCHAR2(25)

Voucher Build Key Char 1

CI_ADJ_APREQ

AP_REQ_ID

VCHR_BLD_KEY_N1

NUMBER(10)

Voucher Build Key Num 1

Derived from BPEL. Value = 0

VCHR_BLD_KEY_N2

NUMBER(10)

Vchr Build Key Num 2

Derived from BPEL. Value = 0

VOUCHER_ID

VARCHAR2(8)

Voucher ID

Derived from BPEL. Value = NEXT

BANK_ID_QUAL

VARCHAR2(3)

Bank ID Qualifier

Derived from BPEL. Value = Blank

BNK_ID_NBR

VARCHAR2(20)

Bank ID Number

Derived from BPEL. Value = Blank

BRANCH_ID

VARCHAR2(10)

Branch ID

Derived from BPEL. Value = Blank

BANK_ACCT_TYPE

VARCHAR2(2)

Bank Account Type

Derived from BPEL. Value = Blank

BANK_ACCOUNT_NUM

VARCHAR2(35)

Bank Account Number

Derived from BPEL. Value = Blank

CHECK_DIGIT

VARCHAR2(2)

Check Digit

Derived from BPEL. Value = Blank

DFI_ID_QUAL

VARCHAR2(2)

DFI Qualifier

Derived from BPEL. Value = Blank

DFI_ID_NUM

VARCHAR2(12)

DFI ID

Derived from BPEL. Value = Blank

BENEFICIARY_BANK

VARCHAR2(30)

Beneficiary Bank

Derived from BPEL. Value = Blank

BENEFICIARY_BNK_AC

VARCHAR2(30)

Beneficiary Bank Alt Char Set

Derived from BPEL. Value = Blank

BENEF_BRANCH

VARCHAR2(30)

Beneficiary Branch

Derived from BPEL. Value = Blank

BENEF_BRANCH_AC

VARCHAR2(30)

Beneficiary Branch Alt Char

Derived from BPEL. Value = Blank

COUNTRY

VARCHAR2(3)

Country

CI_ADJ_APREQ

COUNTRY

CHAR (3)

ADDRESS1

VARCHAR2(55)

Address Line 1

CI_ADJ_APREQ

ADDRESS1

VARCHAR2 (64)

ADDRESS2

VARCHAR2(55)

Address Line 2

CI_ADJ_APREQ

ADDRESS2

VARCHAR2 (64)

ADDRESS3

VARCHAR2(55)

Address Line 3

CI_ADJ_APREQ

ADDRESS3

VARCHAR2 (64)

ADDRESS4

VARCHAR2(55)

Address Line 4

CI_ADJ_APREQ

ADDRESS4

VARCHAR2 (64)

CITY

VARCHAR2(30)

City

CI_ADJ_APREQ

CITY

VARCHAR2 (30)

NUM1

VARCHAR2(6)

Number1

Derived from BPEL. Value = Blank

NUM2

VARCHAR2(4)

Number1

Derived from BPEL. Value = Blank

HOUSE_TYPE

VARCHAR2(2)

House Type

Derived from BPEL. Value = Blank

ADDR_FIELD1

VARCHAR2(2)

Address Field 1

Derived from BPEL. Value = Blank

ADDR_FIELD2

VARCHAR2(4)

Address Field 2

Derived from BPEL. Value = Blank

ADDR_FIELD3

VARCHAR2(4)

Address Field 3

Derived from BPEL. Value = Blank

COUNTY

VARCHAR2(30)

County

CI_ADJ_APREQ

COUNTY

VARCHAR2 (30)

STATE

VARCHAR2(6)

State

CI_ADJ_APREQ

STATE

CHAR (6)

POSTAL

VARCHAR2(12)

Postal Code

CI_ADJ_APREQ

POSTAL

CHAR (12)

GEO_CODE

VARCHAR2(11)

Tax Vendor Geographical Code

CI_ADJ_APREQ

GEO_CODE

CHAR (11)

IN_CITY_LIMIT

VARCHAR2(1)

In City Limit

CI_ADJ_APREQ

IN_CITY_LIMIT

VARCHAR (1)

COUNTRY_CODE

VARCHAR2(3)

Country Code

Derived from BPEL. Value = Blank

PHONE

VARCHAR2(24)

Telephone

Derived from BPEL. Value = Blank

EXTENSION

VARCHAR2(6)

Phone Extension

Derived from BPEL. Value = Blank

FAX

VARCHAR2(24)

Fax Number

Derived from BPEL. Value = Blank

IBAN_CHECK_DIGIT

VARCHAR2(2)

IBAN Check Digit

Derived from BPEL. Value = Blank

IBAN_ID

VARCHAR2(34)

IBAN ID

Derived from BPEL. Value = Blank

EFT_PYMNT_FMT_CD

VARCHAR2(3)

Payment Format

Derived from BPEL. Value = Blank

EFT_TRANS_HANDLING

VARCHAR2(1)

Transaction Handling

Derived from BPEL. Value = Blank

EFT_DOM_COSTS_CD

VARCHAR2(1)

Domestic Costs

Derived from BPEL. Value = Blank

EFT_CORR_COSTS_CD

VARCHAR2(1)

Correspondent's Costs

Derived from BPEL. Value = Blank

EFT_CHECK_DRAW_CD

VARCHAR2(1)

Bank Check Drawn On

Derived from BPEL. Value = Blank

EFT_CHECK_FWRD_CD

VARCHAR2(1)

Check Forwarding

Derived from BPEL. Value = Blank

EFT_PAY_INST_CD1

VARCHAR2(3)

Payment Instruction 1

Derived from BPEL. Value = Blank

AP Data

This section covers the following:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

Oracle PeopleSoft Financials for General Ledger and Accounts Payable

CCB Table

Columns

Data Type

Description

PS Table

Columns

Data Type

Remarks

CI_ADJ_APREQ

PAY_DOC_ID

VARCHAR2 (20)

Advice ID

PS_PAYMENT_TBL

PYMNT_ID_REF

VARCHAR2 (20)

CI_ADJ_APREQ

PAY_DOC_DT

DATE

Advice Date

PS_PAYMENT_TBL

PYMNT_DT

DATE

CI_ADJ_APREQ

PYMNT_ID

CHAR (10)

Payment Number

PS_PAYMENT_TBL

PYMNT_ID

VARCHAR2 (10)

CI_ADJ_APREQ

PAID_AMT

NUMBER (15,2)

Paid Amount

PS_PAYMENT_TBL

PYMNT_AMT

NUMBER (26,3)

CI_ADJ_APREQ

PYMNT_SEL_STAT_FLG

CHAR (1)

Payment Selection Status

Derived from BPEL. Value = P

CI_ADJ_APREQ

AP_REQ_ID

CHAR (12)

A/P Request ID

PS_PYMNT_VCHR_XREF

PYMNT_MESSAGE

VARCHAR2 (70)

CI_ADJ_APREQ

ADJ_ID

CHAR (12)

Adjustment ID

PS_VOUCHER

INVOICE_ID

VARCHAR2 (30)

When liability is closed the AdjustmentMaintenance service is invoked for this Adjustment ID.

CI_ADJ_APREQ

PYMNT_SEL_STAT_FLG

CHAR (1)

Payment Selection Status

Derived from BPEL.

When PS_PAYMENT_TBL.CANCEL_ACTION=R or H then Value=C

When PS_PAYMENT_TBL.CANCEL_ACTION=C then Value=X

CI_ADJ_APREQ

CAN_RSN_CD

CHAR (4)

Cancel Reason Code

APVC

Database Tables Involved in Integration

The following sections identify the database tables involved in this integration, including:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

Note : For more information on Oracle PeopleSoft Financials for General Ledger and Accounts Payable and Oracle Utilities Customer Care and Billing tables refer your product documentation.

GL Integration Point

This section covers:

•

Oracle Utilities Customer Care and Billing

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

Oracle Utilities Customer Care and Billing

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

•

CI_FT_GL

•

CI_DST_CODE_EFF

•

CI_FT_PROC (FT Process)

•

CI_BATCH_CTRL (Batch Control)

•

CI_BATCH_RUN

•

CI_BATCH_JOB

Neither the GLDL flat file header record nor the GLDL flat file detail record layouts are used in this integration.

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

PeopleSoft_JGEN_ACCT_ENTRY is used to stage the incoming accounting entries from Oracle Utilities Customer Care and Billing.

AP Request Integration Point

This section covers:

•

Oracle Utilities Customer Care and Billing

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

Oracle Utilities Customer Care and Billing

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 when extracting AP Request information from Oracle Utilities Customer Care and Billing.

•

CI_ADJ_APREQ

•

CI_ADJ

•

CI_SA

•

CI_ACCT

•

CI_ACCT_PER

•

CI_PER

•

CI_PER_NAME

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

The following AP Invoice staging tables are used to stage the incoming AP Requests from Oracle Utilities Customer Care and Billing.

•

PS_VCHR_HDR_STG

•

PS_VCHR_LINE_STG

•

PS_VCHR_DIST_STG

•

PS_VCHR_PYMT_STG

•

PS_VCHR_VNDR_STG

AP Data Integration Point

This section covers:

•

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

•

Oracle Utilities Customer Care and Billing

Oracle PeopleSoft Financials for General Ledger and Accounts Payable Tables

No Oracle PeopleSoft Financials for General Ledger and Accounts Payable interface tables are used in this integration point.

The Payment Information is extracted from the following application tables:

•

PS_PAYMENT_TBL

•

PS_VOUCHER

•

PS_PYMNT_VCHR_XREF

Oracle Utilities Customer Care and Billing

The CI_ADJ_APREQ application table is updated with the Payment Information received from Oracle PeopleSoft Financials for General Ledger and Accounts Payable. This table is considered an interface table for the purpose of this integration point even though it is a core Oracle Utilities Customer Care and Billing product table.

Logic Used in the Integration Points

The following describes the logic used in the BPEL services that form part of this integration product:

•

GL Integration Point

•

AP Request Integration Point

•

AP Data Integration Point

GL Integration Point

This section covers:

•

BPEL Polls to Verify whether FTs are ready for Extraction

•

Update the NEXT BATCH NUMBER in CI_BATCH_CNTRL

•

Extract Financial Transactions from CCB

•

Update the LAST UPDATE TIME FOR GLDL in CI_BATCH_CNTRL

BPEL Polls to Verify whether FTs are ready for Extraction

Oracle BPEL Process Manager 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 integration

AND the GLS run has completed successfully

Retrieve the Batch of Rows identified in CI_BATCH_RUN (created by GLS)

Else

Do nothing

Update the NEXT BATCH NUMBER in CI_BATCH_CNTRL

Increment by 1 the NEXT_BATCH_NBR in CI_BATCH_CTRL WHERE BATCH_CD is GLDL

Extract Financial Transactions from CCB

Extract Information 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

Field

Description

Source System

Set to CCB

BATCH_NBR

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

DIST_ID

The distribution code used in CCB to derive the GL account information. A sample data example is R - ELERES for electric residential revenue financial transactions.

GL_ACCT

The actual GL account with '.' separating the substructure numbers like department. For example 101.73653.8873..87.

CIS_DIVISION

The CIS Division

GL_DIVISION

The GL Division

CURRENCY_CD

The currency type, such as USD.

STATISTICS_CD

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

ACCOUNTING_DT

The effective accounting date for the GL transactions

AMOUNT

The dollar amount of the GL debit or credit

STATISTIC_AMOUNT

The quantity associated with the Statics Code.

When the above is executed successfully, continue by executing the following:

Update the LAST UPDATE TIME FOR GLDL in CI_BATCH_CNTRL

Set the last update date and time by setting

CI_BATCH_CTRL LAST_UPDATE_DTTM to SYSDATE WHERE BATCH_CD = 'GLDL'

AP Request Integration Point

This section covers:

•

Update the LAST UPDATE TIME FOR GLDL in CI_BATCH_CNTRL

•

Update the NEXT BATCH NUMBER in the CI_BATCH_CNTRL

•

Poll Oracle Utilities Customer Care and Billing to Verify Extraction

•

Extract of Customer and AP Refund Request

•

Updating CI_ADJ_APREQ Status

Update the NEXT BATCH NUMBER in the CI_BATCH_CNTRL

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

Poll Oracle Utilities Customer Care and Billing to Verify Extraction

The Oracle BPEL Process Manager 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

Run the integration point

Else do nothing.

Extract of 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

(See 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

Updating CI_ADJ_APREQ Status

UPDATE CI_ADJ_APREQ

SET PYMNT_SEL_STAT_FLG to R (Requested for Payment)

AP Data Integration Point

This section covers:

•

Extract the Payment Information from Oracle PeopleSoft Financials for General Ledger and Accounts Payable

Extract the Payment Information from Oracle PeopleSoft Financials for General Ledger and Accounts Payable

SELECT the following information

BANK_CD, BANK_ACCT_KEY, PYMNT_ID, PYMNT_ID_REF, BANK_ACCOUNT_NUM, REMIT_VENDOR, PYMNT_AMT, PYMNT_METHOD, PYMNT_STATUS, CANCEL_ACTION, CANCEL_POST_STATUS, VOUCHER_ID, PYMNT_MESSAGE, INVOICE_ID

FROM PS_PAYMENT_TBL, PS_PYMNT_VCHR_XREF, PS_VOUCHER

WHERE REMIT_VENDOR = 'CCBVENDOR' (The Single Payment Vendor Identified in Configuration)

AND the cancel date is greater than the last date the interface was run

OR the CANCEL_POST_STATUS is P (Paid)

OR the create date is greater than the last date the interface was run

'CCBVENDOR' is a configuration parameter.

For each payment selected above, check if this payment is already applied in Oracle Utilities Customer Care and Billing.

If PS_PAYMENT_TBL.CANCEL_DT<> Null and PS_PAYMENT_TBL.CANCEL_ACTION IN ('C','H','R') (This payment has been cancelled after it was created)

If PS_PAYMENT_TBL.CANCEL_POST_STATUS='P' (This cancellation is posted)

If the Payment has been canceled in Oracle PeopleSoft Financials for General Ledger and Accounts Payable and the liability has been closed update the CI_ADJ_APREQ PYMNT_SEL_STAT_FLG to 'X'(Canceled) and Invoke the service C1AdjustmentMaintenance to cancel the Adjustment corresponding to this payment.

BPEL Process Overview

The following describes the process flows within the BPEL services that form this product:

•

GL Transaction Integration

•

AP Request Integration

•

AP Data Integration

GL Transaction Integration

The following is the list processes that comprise the GL Integration between Oracle Utilities Customer Care and Billing and Oracle PeopleSoft Financials for General Ledger and Accounts Payable:

Process Name

CCBPSGLRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the ORABPEL Schema at predefined intervals and invokes the CCBToPSGLBPELProcess.

Calls to

CCBToPSGLBPELProcess

Calls from

None

Inputs

None

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Process Name

CCBToPSGLBPELProcess

Description

Main process of the GL Integration. Gets the GL data from Oracle Utilities Customer Care and Billing, transforms it, and inserts it into Oracle PeopleSoft Financials for General Ledger and Accounts Payable Generic Accounting Entry Table PS_JGEN_ACCT_ENTRY. Additionally, updates Oracle Utilities Customer Care and Billing Batch Control table with the next batch number. Invokes services for error handling and e-mail notification.

Calls to

GetCCBGLData, UpdateCCBGLControlTable, ErrorhandlingProcess (if an error occurs), EmailNotification

Calls from

CCBPSGLRequestScheduler

Inputs

None

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault.

Process Name

GetCCBGLData

Description

Checks if GL 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

ErrorhandlingProcess (if an error occurs).

Calls from

CCBToPSGLBPELProcess.

Inputs

Input String object containing the Integration Point name invoking this BPEL process e.g. PS, EBS, JDE, etc

Outputs

SelectCCBGLRecordsOutput xml object

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault

Process Name

UpdateCCBGLControlTable

Description

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

Calls to

ErrorhandlingProcess (if an error occurs).

Calls from

CCBToPSGLBPELProcess.

Inputs

None

Outputs

None

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault

BPEL flow for GL Transaction Integration Point is shown below:

AP Request Integration

The following is the list processes that comprise the AP Request Integration between Oracle Utilities Customer Care and Billing and Oracle PeopleSoft Financials for General Ledger and Accounts Payable:

Process Name

CCBToPSAPRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the ORABPEL Schema at predefined intervals and invokes the CCBToPSAPBPELProcess.

Calls to

CCBToPSAPBPELProcess

Calls from

Inputs

None

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Process Name

CCBToPSAPBPELProcess

Description

Main process of the AP Integration. Gets the AP data from Oracle Utilities Customer Care and Billing, transforms it, and inserts it into Oracle PeopleSoft Financials for General Ledger and Accounts Payable AP Voucher Build Interface tables (PS_VCHR_HDR_STG, PS_VCHR_LINE_STG, PS_VCHR_DIST_STG, PS_VCHR_VNDR_STG, PS_VCHR_PYMT_STG, PS_VCHR_MSCH_STG, PS_VCHR_BANK_STG, PS_VCHR_IBANK_STG). Additionally, updates status in two of the Oracle Utilities Customer Care and Billing tables. Invokes services for error handling and e-mail notification.

Calls to

GetCCBAPData, UpdateCCBAPControlTable, UpdateCCBAPAdjReqTable, ErrorhandlingProcess (if an error occurs)., EmailNotofication

Calls from

CCBToPSAPRequestScheduler

Inputs

None

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault.

Process 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

ErrorhandlingProcess (if an error occurs).

Calls from

CCBToPSAPBPELProcess.

Inputs

Input String object containing the Integration Point name invoking this BPEL process - PS, EBS, JDE, etc

Outputs

SelectCCBRecordsWithTemplate xml object

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault

Process 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

ErrorhandlingProcess (if an error occurs).

Calls From

CCBToPSAPBPELProcess.

Inputs

None

Outputs

None

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions -

Process Name

UpdateCCBAPAdjReqTable

Description

Updates PYMNT_SEL_STAT_FLG to R in the CCB AP Adjustment Request table CI_ADJ_APREQ for the provided AP request ID.

Calls to

None

Calls from

CCBToPSAPBPELProcess.

Inputs

CiAdjApreqCollection xml object

Outputs

None

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - None

Process Name

InsertPSVoucher

Description

Inserts it into Oracle PeopleSoft Financials for General Ledger and Accounts Payable A/P Voucher Build Interface tables (PS_VCHR_HDR_STG, PS_VCHR_LINE_STG, PS_VCHR_DIST_STG, PS_VCHR_VNDR_STG, PS_VCHR_PYMT_STG, PS_VCHR_MSCH_STG, PS_VCHR_BANK_STG, and PS_VCHR_IBANK_STG).

Calls to

None

Calls from

CCBToPSAPBPELProcess.

Inputs

SelectCCBAPRequestRecordsOutput xml object

Outputs

None

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Remote Fault, BPEL Binding Fault.

BPEL flow for AP Request Integration Point is shown below:

AP Data Integration

The following is the list processes that comprise the AP Data Integration between Oracle Utilities Customer Care and Billing and Oracle PeopleSoft Financials for General Ledger and Accounts Payable:

Process Name

PSCCBAPDataRequestScheduler

Description

Polls the INTEGRATION_PROCESS_ACTIVATION table in the ORABPEL Schema at predefined intervals and invokes the PSToCCBAPDataBPELProcess.

Calls to

PSToCCBAPDataBPELProcess, ErrorhandlingProcess (if an error occurs).

Calls from

None

Inputs

None

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - None

Process 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

PSToCCBAPDataBPELProcess.

Inputs

String object - AdjustmentID

Outputs

String result - Contains APRequest ID to handle success or error messages

Synch/Asynch

Synchronous

Exceptions/Errors

Possible Exceptions - BPEL Binding fault, BPEL Remote Fault.

BPEL flow for AP Data Integration Point is shown below:

Shared Integration Processes

These BPEL processes are used across multiple integration points within this product.

Process Name

Error Handling Process

Description

Invoked only if an error occurs. Inserts data into error table INTEGRATION_ERROR_STORE in the ORABPEL Schema.

Calls to

Calls from

GetCCBGLData, UpdateCCBGLControlTable, CCBToPSGLBPELProcess,GetCCBAPData,UpdateCCBAPControlTable,CCBToPSAPBPELProcess,CCBCancellationWebService,PSToCCBAPDataBPELProcess

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 e-mail to configured e-mail address.

Calls to

None

Calls from

CCBToPSGLBPELProcess

CCBToPSAPBPELProcess

PSToCCBAPDataBPELProcess

Inputs

MailNotificationInpputParameters xml object

Outputs

None

Synch/Asynch

Asynchronous

Exceptions/Errors

Possible Exceptions - None

1 Preface

Audience

This document is intended for anyone implementing the Oracle Utilities Customer Care and Billing Integration to Oracle PeopleSoft Financials for General Ledger and Accounts Payables.

Documentation Accessibility

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

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit: http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info

or

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Part 1

Understanding the Integration

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

This section contains the following chapters:

•

Overview

•

Understanding the Integration Process

Part 1 - Understanding the Integration

Part 2

Implementing the Integration Product

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

This section contains the following chapters:

•

Configuring the Integration

•

Monitoring and Troubleshooting

•

Customization Options

Part 2 - Implementing the Integration Product