Configuring the Integration Product

At this time, there is no user interface for entering the configuration parameters associated with the integration. You must update configuration parameters from the database using a database access tool.

This section includes the following:

• Setting Configuration Properties

• Setting BPEL Preference GL_AccountTransformationRequired

• Setting Domain Value Maps

• Error Handling

Setting Configuration Properties

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

Process Activation

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

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_EBS_GL	Y	0	0
CCB_EBS_AP	Y	0	0
EBS_CCB_APDATA	Y	0	0

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

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 Oracle Utilities Customer Care and Billing product. You may schedule the Oracle E-Business Suite Financials for General Ledger and Accounts Payable processes using the standard tools shipped with the Oracle E-Business Suite Financials for General Ledger and Accounts Payable product. You may schedule the BPEL processes to the time intervals you have configured. Each of the main BPEL processes that form this process integration have been designed to verify that data that must be extracted exists. If data exists, it is extracted, transformed, and loaded to the target system. If data does not exist, then the process does nothing until the next time it tries again.

Example: Schedule the Oracle Utilities Customer Care and Billing GL processes to run at 6:00 A.M. each weekday. Then initiate the BPEL GL process at 7:00 AM and have it run every 4 hours to see if data exists. Accounting staff could then schedule the Journal Generator to run at 9:00 AM each morning using the Oracle E-Business Suite Standard Request Submission (SRS).

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 two minutes from the time you start them the first time. If data exists the integration is completed for the integration point. Otherwise, the system does nothing and tries again two minutes later.

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

PROCESS_NAME	START_PROCESS (Y/N)	RUN_FREQUENCY (Seconds)
CCB_EBS_GL	Y	14400
CCB_EBS_AP	Y	7200
EBS_CCB_APDATA	Y	7200

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

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

If START_Process flag is NOT Y do nothing

else

If Run Frequency is 0 run the IP

else

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

else

DO not run the Integration Point, just set Next_run_interval = Next_run_interval - 120 (the polling interval set in BPEL)

General Ledger (GL)

The following processes must be run in sequence to extract, transform, and load GL transactions from Oracle Utilities Customer Care and Billing to Oracle E-Business Suite Financials for General Ledger and Accounts Payable.

Product	Process Name	Description
CCB	GLASSIGN	Assigns GL account numbers to the GL details associated with financial transactions by referencing the distribution code that calls the appropriate assignment algorithm.
CCB	GLS	Follows GLASSIGN to create financial transaction (FT) download staging records for all financial transactions that are ready to be posted to the GL.
BPEL	CCBToEBSGLBPELProcess	Extracts financial transactions from Oracle Utilities Customer Care and Billing into BPEL to be transformed and prepared for upload to Oracle E-Business Suite Financials for General Ledger and Accounts Payable.
EBS	EBS Journal Import Process	Reads the staged data in the GL_INTERFACE Interface Table and creates journal entries in the Oracle E-Business Suite Financials for General Ledger and Accounts Payable GL. This process can be scheduled or run manually.

AP Request

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

Product	Process Name	Description
BPEL	CCBToEBSAPBPELProcess	Extracts AP Requests from Oracle Utilities Customer Care and Billing into BPEL to be transformed and prepared for upload to Oracle E-Business Suite Financials for General Ledger and Accounts Payable.
EBS	Payables Open Interface Import (APXIIMPT)	Creates Invoices in Oracle E-Business Suite Financials for General Ledger and Accounts Payable.

AP Data

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

Product	Process Name	Description
BPEL	EBSToCCBAPDataBPELProcess	Extracts all payments and cancellations created during pay cycle processing.

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

There is an open-source tool bundled with the SOA Suite called Quartz, which can be used as a scheduler.

Lookup Table

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

INTEGRATION_KEY	INTEGRATION_VALUE	Comments
CCB.EBS.GL.LEDGER_ID	1	General Ledger ID
CCB.EBS.GL.EMAIL	abc.gl@xyz.com	Enter the e-mail address to be notified if errors occur in the GL integration point. Example: abc.gl@xyz.com.
CCB.EBS.GL.CUSTOMGLDATA.ENDPOINT	(space)	Custom GL component end point
CCB.EBS.GL.USER_JE_CATEGORY_NAME	CCB EBS	This is journal category. Example: ‘CCB EBS’
CCB.EBS.GL.USER_JE_SOURCE_NAME	CCB EBS	This is the Journal Source. Example: ‘CCB EBS’
CCB.EBS.GL. ACTUAL_FLAG	A	To create the Actual Journals. Example: ‘A’
CCB.EBS.GL.GL_DIVISION	US1 or Leave Blank	If this value is blank, then financial transactions associated with all GL divisions in Oracle Utilities Customer Care and Billing are integrated. If this column has a value, then only financial transactions associated with this specific GL Division indicated are integrated. Examples: blank or US1.
CCB.EBS.GL.XFORMCCBCOLL.PRE.EXTN.FLAG	false	If set to true, the pretransformation extension service is invoked at the collection level after retrieving all the FT records from Oracle Utilities Customer Care and Billing and before any transformation is done.
CCB.EBS.GL.AFTEREBSCOLLINSERT.POST.EXTN.FLAG	false	If set to true, the posttransformation extension service is invoked after all the record are inserted in the Oracle E-Business Suite GL_INTERFACE table.
CCB.EBS.APREQUEST.EMAIL	abc@oracle.com	Enter the e-mail address to be notified if errors occur in the AP Request integration point. Example: abc.ap@oracle.com.
CCB.EBS.APREQUEST.INVOICE_SOURCE	CCB	Invoice Source to use when the integration creates invoices in Oracle E-Business Suite.
CCB.EBS.AP.CHAR_TYPE_CD_TERMSID	TERMS_ID	Characteristic Type to store Payment Terms ID of E-Business Suite. This must match what you documented in step B8.
CCB.EBS.AP.CHAR_TYPE_CD_ORGID	EBSORGID	Characteristic Type to store Oracle E-Business Suite Financials for General Ledger and Accounts Payable AP Operating Unit. This must match what you documented in step B3. Example: EBSORGID.
CCB.EBS.APREQUEST.ORG_ID	204	Valid ID for Organization code in E-Business Suite to be used when integration creates vendors in E-Business Suite Release 11.5.10. For E-Business Suite Release 12, the Org ID will be retrieved from the CIS Division Characteristic value.
CCB.EBS.APREQUEST.PAYMENT_METHOD	CHECK	Specifies the payment method for the invoices created by the integration. The supported value is 'CHECK'. Do not modify this default value.
CCB.EBS.APREQUEST.DEFAULT.HEADER.DESCRIPTION	Refund Request from CCB	Default Header description for Invoices in EBS.
CCB.EBS.APREQUEST.DEFAULT.LINE.DESCRIPTION	Refund Request from CCB	Default Line description for Invoices in Oracle E-Business Suite.
CCB.EBS.APREQUEST.VENDOR.NAME.SUFFIX	ADJ_ID	The column whose value is suffixed to the vendor name when creating a supplier in EBS. Possible values are PER_ID,ACCT_ID, ADJ_ID, SA_ID,AP_REQ_ID.
CCB.EBS.APREQUEST.XFORMCCBCOLL.PRE.EXTN.FLAG	false	If set to true, the pre transformation extension service is invoked at the collection level after retrieving all the AP Request records from Oracle Utilities Customer Care and Billing and before any transformation is done.
CCB.EBS.APREQUEST.XFORMCCBRECORD.PRE.EXTN.FLAG	false	If set to true, the pre transformation extension service is invoked at the record level before transforming the AP Request record from Oracle Utilities Customer Care and Billing to Oracle E-Business Suite format.
CCB.EBS.APREQUEST.XFORMEBSRECORD.POST.EXTN.FLAG	false	If set to true, the post transformation extension service is invoked at the record level after the record is inserted in the Oracle E-Business Suite Invoice Interface tables.
CCB.EBS.APREQUEST.XFORMCCBSPI.PRE.EXTN.FLAG	false	If set to true,the pre invoke extension service is invoked before the EBS Stored procedure is executed in CCBToEBSAPProcedureCall process.
CCB.EBS.AP.INVOKE_ALT_EBS_TABLE_FLAG	false	Flag to enable the extensible scope , using which the composite can be customized.
CCB.EBS.CCB.ADJ.MAINT.URL	http://hostname:port/ouaf/XAIApp/xaiserver/C1AdjustmentMaintenance (XAI) or http://hostname:port/ouaf/webservices/C1AdjustmentMaintenance (IWS)	For cancellation of an adjustment, place the correct URL here.
CCB.EBS.ADMIN_EMAIL	abc@xyz.com
CCB.EBS.CCB.LANGUAGE.CODE	ENG	Language Code
CCB.EBS.AP.CHAR_TYPE_CD_GLEDGERID	GLEDGER	Characteristic Type to store Ledger ID for E-Business Suite Release 12. This must match what you documented in the step B6.
EBS.CCB.APDATA.EMAIL		Enter the e-mail address to be notified if errors occur in the AP Request integration point. Example: abc.ap@oracle.com.
EBS.CCB.APDATA.LASTRUNDTTM	11-02-2008 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.
EBS.CCB.CANCEL.CANCEL_REASON	APVC	Valid cancel reason code to be passed to CC&B when canceling the adjustment associated with the AP Request.
EBS.CCB.APDATA.XFORMEBSPAYMENTSCOLL.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 EBS and before any transformation is done.
EBS.CCB.APDATA.XFORMEBSPAYMENTSRECORD.PRE.EXTN.FLAG	false	If set to true, the pre-transformation extension service is invoked at the record level before transforming the Oracle E-Business Suite payment record from Oracle E-Business Suite to Oracle Utilities Customer Care and Billing format.
EBS.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.
EBS.CCB.APDATA.PROCESSPAYMENTINFO.POST.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.

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

Setting BPEL Preference GL_AccountTransformationRequired

Preferences are name-value pair properties that are added to a BPEL process to hold configurable parameters. These preferences are accessed at run time by the BPEL process. The value of a preference can be changed at run time in Oracle Enterprise Manager (EM) Console, without having to redeploy the BPEL process.

Updating GL_AccountTransformationRequired Preference at Runtime

In the AP Request Integration process, GL_AccountTransformationRequired preference property determines the delimiter of the GL Account string passed to Oracle E-Business Suite Financials for General Ledger and Accounts Payable AP Invoice Interface tables.

By default, GL_AccountTransformationRequired is set to Y; this means GL Accounts use a hyphen "-" as a delimiter. If your implementation wants to modify this to use the delimiter that is passed from Oracle Utilities Customer Care and Billing, set the value to N. Most of the time, Oracle Utilities Customer Care and Billing passes the GL Account with dots as the delimiter.

Use the Enterprise Manager (EM) console to change the setting of the GL_AccountTransformationRequired process as needed.

1. Login to Enterprise Manager (EM) console.

2. Go to the System MBean browser.

Example: Using 14c Enterprise Manager console

3. Right-click SOA-Infra > Administration > System MBean Browser.

4. Drill down to the CCBToEBSAPBPELProcess composite following this path:

oracle.soa.config > Manage Server (ex.soa_server1) > SCAComposite > CCBToEBSAPBPELProcess > ScaComponent.SCAComposite > CCBToEBSAPBPELProcess

5. In the Attribute tab, click Properties.

6. Change the value of preference.GL_AccountTransformationRequired property and click Apply.

7. Test your interface again and you should see they expected delimiter in the GL Account string.

Note: The settings are reset to default once the application server is restarted.

8. To retain the change values of the preference, do the following:

After you apply the change to the bpel.preference.<PREFERENCE_NAME>, click Return.

a. On the Operations tab, click Save.

b. Click Invoke and then click Return.

Setting Domain Value Maps

Domain value maps (DVMs) are a standard feature of the Oracle SOA Suite which maps codes and other static values across applications.

Example: “US” and “USA”

DVMs are static in nature, though Administrators can add additional maps as needed. Transactional business processes never update DVMs - they only read from them. They are stored in XML files and cached in memory at runtime.

To maintain information within the domain value maps:

1. Open a browser and access the SOA Composer application.

Example: http://soa_host:soa_managerServer_Port/soa/composer/

2. Select the relevant DVM you wish to maintain from the Deployment View pane.

3. To edit the selected DVM, click CreateSession in the top navigation bar.

4. Once the DVM has been edited, click Save in the navigation bar. This saves the DVM data for that session.

5. Click Publish after updating each DVM. This saves the DVM data in MDS.

DVMs

For more information about Domain Value Maps, refer to the chapters Working with Domain Value Maps and Using SOA Composer with Domain Value Maps in Oracle Fusion Middleware Developer's Guide for Oracle SOA Suite.

DVM	Integration Point	Commposite	Description
CCB_EBS_Country	Ap request	CCBToEBSAP ProcedureCall	Transforms the Oracle Utilities Customer Care and Billing Country code to an Oracle E-Business Suite country code.
CCB_EBS_State	AP request	CCBToEBSAP ProcedureCall	Transforms the Oracle Utilities Customer Care and Billing state code to an Oracle E-Business Suite state code.
CCB_EBS_Currency	AP request	CCBToEBSAP BPELProcess	Transforms the Oracle Utilities Customer Care and Billing currency code to an Oracle E-Business Suite currency code.
CCB_EBS_CategoryName	GL	CCBToEBSGL BPELProcess	Transforms the Oracle Utilities Customer Care and Billing financial type flag to an Oracle E- Business Suite the category name.

CCB_EBS_Country

This DVM is used by the integration code to transform Oracle Utilities Customer Care and Billing country code to Oracle E-Business Suite country code.

CCB_Country	EBS_Country
This is valid country code defined in Oracle Utilities Customer Care and Billing. Example: USA	This is valid country code defined in Oracle E-Business Suite. Example: US

CCB_EBS_State

This DVM is used by the integration code to transform Oracle Utilities Customer Care and Billing state code to Oracle E-Business Suite state code.

CCB_State	EBS_State
This is valid state code defined in Oracle Utilities Customer Care and Billing. Example: CA	This is valid state code defined in Oracle Utilities Customer Care and Billing. Example: CA

CCB_EBS_Currency

This DVM is used by the integration code to transform Oracle Utilities Customer Care and Billing currency code to Oracle E-Business Suite currency code.

CCB_Currency	EBS_Currency
This is valid currency code defined in Oracle Utilities Customer Care and Billing. Example: USD	This is valid currency code defined in Oracle Utilities Customer Care and Billing. Example: USD

CCB_EBS_ CategoryName

This DVM is used by the integration code to transform Oracle Utilities Customer Care and Billing financial transaction flag to Oracle E-Business Suite journal category name.

CCB_ FTTypeFlag	EBS_userJeCategoryName
This is valid financial transaction type flag in Oracle Utilities Customer Care and Billing. Example: AD, AX, BS, PS	This is valid currency code defined in Oracle Utilities Customer Care and Billing. Example: USD

Error Handling

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

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

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

Column	Data Type
SOURCE_SYSTEM	VARCHAR2 (3)
INT_BATCH_NUMBER	NUMBER
INTERFACE_NAME	VARCHAR2 (30)
BPEL_INSTANCE_ID	NUMBER
ERROR_CODE	VARCHAR2(400)
ERROR_SUMMARY	VARCHAR2 3000)
ERROR_MESSAGE	VARCHAR2 3000)
NOTIFIED	VARCHAR2 (1)
LAST_UPDATE_DATE_TIME	DATE

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

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

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

This e-mail contains 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

Error Notification Setup

Enable email notification for the error handling module.

1. Login to the Enterprise Manager console.

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

3. From the drop-down list, select EMAIL.

4. Enter the Email IDs in the ‘From’ address field.