2 Initial Loading and Synchronization of Data

This chapter provides an overview of batch data loads and synchronization and includes the following sections:

Section 2.1, "Overview"
Section 2.2, "Batch Loading of Data PL/SQL Script"
Section 2.3, "Batch Loading Customer Data"
Section 2.4, "Batch Loading Product Data"

2.1 Overview

The Oracle CRM On Demand Integration Pack for Oracle E-Business Suite Process Integration Pack (PIP) integrates customer data (account, account-address, and account-contact) and product information between Oracle CRM On Demand and Oracle E-Business Suite. This requires that you initially load data from Oracle E-Business Suite into CRM On-Demand, and that the data in the two systems is continuously synchronized. The Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP enables you to:

Load the Oracle E-Business Suite customer and product data to be synchronized between Oracle E-Business Suite and CRM On Demand.
Establish a cross-reference between the synchronized entities.
Synchronize changes to the data that exists in both databases after the initial load is complete.

Batch loading of business data is a one-way process that loads data from Oracle E-Business Suite into CRM On Demand. To make use of customer and product data that exists in your Oracle E-Business Suite, Oracle recommends that you plan an initial data load strategy.

To load large amounts of data, Oracle recommends that you identify and load the data that is to be used by the integration in priority sequence. Extract and load the data into CRM On Demand in several batches, based on the time allotted for initial data loads.

To batch load data from Oracle E-Business Suite to CRM On Demand, you must run a batch load PL/SQL script supplied with the PIP to generate batch load events. These events are then processed by the integration flows for batch loading account data and batch loading product data.

2.2 Batch Loading of Data PL/SQL Script

This topic describes the PL/SQL script that you use to perform the initial batch load of data from Oracle E-Business Suite to CRM On Demand. You must run a separate script for the batch load of customer and product data.

The PL/SQL script includes these procedures:

GENERATE_EVENTS
PURGE_ EVENTLOG
REGENERATE_EVENT

2.2.1 GENERATE_EVENTS Procedure

Two GENERATE_EVENTS procedures are provided with the Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP:

HZ_EBI_CUST_LOAD.GENERATE_EVENTS

Use to perform the batch load and synchronization of account and account-contact data from Oracle E-Business Suite to CRM On Demand.
EGO_EBI_ITEM_LOAD.GENERATE_EVENTS

Use to perform the batch load and synchronization of product data from Oracle E-Business Suite to CRM On Demand.

You must run the GENERATE_EVENTS procedure to generate events for the customer or product data to be loaded during the initial batch load. The script uses filter criteria (which can be customized) to identify the records to be loaded, then generates a business event for each set of records as determined by the batch size. When each event is generated, the event ID and the primary keys of the processed records are logged to a log table. The subscriber, created during the installation of the Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP, retrieves the records using the provided Application Program Interface (API) or view using the event ID. The log table is also used to filter out records that are processed in case the script must be restarted.

The GENERATE_EVENTS procedure accepts the parameters listed in Section 2.2.1, "GENERATE_EVENTS Procedure Parameters":

Table 2-1 GENERATE_EVENTS Procedure Parameters

Parameter	Value	Description
BatchSize	Number	Specify the number of records to include in each batch event. Because CRM On Demand can process a maximum of 20 records in a batch, do not specify a batch size greater than 20. The default batch size is also 20.
MaxEvents number	Number	Specify the maximum number of batch events to generate. If a value is not specified, events are raised until all records returned by the query are processed.

When you run the GENERATE_EVENTS procedure for the first time, all the eligible item IDs are entered into a log file (EGO_EBI_ITEM_LOAD_LOG for products or HZ_EBI_CUST_LOAD_LOG for accounts) and the number of events specified in the Max_Events parameter are generated. Each event contains the number of items specified by the Batch_Size parameter. If you limit the number of events generated by specifying a value for the Max_Events parameter, the X_ERR_MSG indicates the number of events raised and whether any items are pending in the log file. If records are pending in the event table, run the appropriate GENERATE_EVENTS procedure again after allowing sufficient time for the raised events to be consumed successfully.

2.2.2 PURGE_EVENTLOG Procedure

A separate log table is created the first time the initial load script is run for the accounts or products batch load. The table can be deleted after the initial load is complete. However, to use the batch load process for any future bulk load of account or product data, do not delete the log table; instead, run the PURGE_EVENTLOG procedure after the initial batch load is completed. You can also run this procedure to restart the batch load from the beginning.

Table 2-2 lists the structure of the log table:

Table 2-2 Structure of the Log Table

Name	Description
Key column(s)	It stores the primary key of the top-level entity. The column names match the name of the entity. A unique index is created for these columns.
Event-Id	It is ID of the event that was raised for this entity. Because multiple entities are included in each event, this is not unique.

Each row in the log table represents one entity record. The keys for all the records included in an initial load are inserted at the front of the log table. As each event is raised, the event ID column is populated for the processed records.

2.2.3 REGENERATE_EVENT Procedure

The REGENERATE_EVENT procedure is provided for error handling. Each event contains a batch of product or account records. CRM On Demand synchronizes all the records in each batch or none. If a batch fails to synchronize to CRM On Demand, either because of a record-level error or because of a system error, use the REGENERATE_EVENT procedure to resubmit the event and synchronize all the records again. To modify the batch, for example, by removing a product or account record that is causing an error, inactivate the record before running the REGENERATE_EVENT procedure. A new event is created that does not include the inactivated record.

2.3 Batch Loading Customer Data

This section provides an overview of customer data load and synchronization and discusses how to batch load and synchronize customer data.

The Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP provides a single, consistent, and accurate view of a customer account across the CRM On Demand and Oracle E-Business Suite applications.

Because customer data is entered, managed, and maintained in both CRM On Demand and Oracle E-Business Suite, you must initially load and synchronize existing customer data from Oracle E-Business Suite to CRM On Demand; subsequent updates to mapped attributes are then synchronized in near real time, bidirectionally.

The integration supports the initial load of customer data from Oracle E-Business Suite to CRM On Demand, but not the other way around. When you begin to deploy the integration, you must evaluate whether to load your existing customer data from within your Oracle E-Business Suite deployment to your CRM On Demand deployment. You might require only that a subset of your customer data that exists in Oracle E-Business Suite is represented in CRM On Demand, or you might choose to have all of your customer data represented in both systems.

The Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP enables you to define and extract a specific set of customer data, which includes accounts, account-contacts, and account-addresses, from Oracle E-Business Suite and to load that subset of data into CRM On Demand. You can review the number of records to be loaded before performing the load into CRM On Demand. Organize and load data based on the priority in which the data is used by end users and the time available for data load.

2.3.1 Understanding the Customer Account Batch Flow Process

The Batchload Customer Account Integration flow uses these interfaces:

BatchLoadCustomerPartyListEbizEventConsumer
BatchLoadCustomerPartyListEbizAdapter
BatchLoadCustomerPartyListEbizReqABCSImpl
CustomerPartyOracleEBSV2
SyncCustomerCRMODProvABCSImpl

Figure 2-1 illustrates the flow for synchronizing accounts between Oracle E-Business Suite and CRM On Demand:

Figure 2-1 Batchload Customer Account Flow Sequence Diagram

This image is described in surrounding text.

The Batchload Customer Account process comprises these events:

You initiate the Batchload Account process by launching the SQL script to run the procedure HZ_EBI_CUST_LOAD.GENERATE_EVENTS, which generates the batch load event.
The BatchLoadCustomerPartyListEbizEventConsumer retrieves the event payload from the queue and routes the request to the BatchLoadCustomerPartyListEbizReqABCSImpl service.
The event payload received by the BatchLoadCustomerPartyListEbizReqABCSImpl contains only the event identifier, not the entire account detail.

Therefore, account details must be fetched from Oracle E-Business Suite based on the event ID using the Query Apps adapters, BatchLoadCustomerPartyListEbizAdapter.
The Query adapters BatchLoadCustomerPartyListEbizAdapter query the Oracle E-Business Suite database with the EbizQueryCustomerPartyRequestABM and fetch the account payload corresponding to the event ID, which is the EbizQueryCustomerPartyResponseABM.
In the BatchLoadCustomerPartyListEbizReqABCSImpl, the QueryResponseABM is transformed into the SyncCustomerPartyListEBM, and the SyncCustomerPartyList operation of the CustomerPartyEBSV2 is invoked with this Enterprise Business Message (EBM).
The CustomerPartyEBSV2 routes the SyncCustomerPartyListEBM to the SyncCustomerCRMODProvABCSImpl.

In this service, the SyncCustomerPartyListEBM is transformed into the SiebelAccountUpsertRequestABM, and the Siebel web service for account creation or update is invoked.
The CRM On Demand Account/Contact InsertOrUpdate web service does the actual creation or update of the account, address, and account-contact in CRM On Demand and returns the appropriate response, CRMODAccount/ContactInsertOrUpdateResponseABM.

This response is received by the SyncCustomerCRMODProvABCSImpl service.
In the SyncCustomerCRMODProvABCSImpl service, the CRMODAccount/ContactInsertOrUpdateResponseABM is transformed into the SyncCustomerPartyListResponseEBM, during which the cross-reference data is updated with the CRM On Demand identifiers, and then the flow ends without sending a response back to Oracle E-Business Suite.

2.3.2 Solution Assumptions and Constraints

A customer in Oracle E-Business Suite is defined as an organization party with an associated account.
Before running the batch data loads, you must run the HZ_EBI_CUST_LOAD.GENERATE_EVENTS procedure to generate the batch load events.

The procedure selects all active accounts. To select only a subset of the accounts for batch data load, you must modify the PL/SQL filter clause in the HZ_EBI_CUST_LOAD.GENERATE_EVENTS PL SQL procedure.
The batch size specified for the HZ_EBI_CUST_LOAD.GENERATE_EVENTS procedure must not be greater than 20, a limitation imposed by the batch size supported within CRM On Demand.

2.3.3 Running the Account Batch Load Process

To run the account batch load:

Run the HZ_EBI_CUST_LOAD.GENERATE_EVENTS procedure, specifying values for the Batch_Size and Max_Events parameters.

All active accounts are selected by default.

To select a subset of accounts for the account batch load, modify the query in Example 2-1 within the procedure, then recompile the procedure before running it:
Example 2-1 Query for Account Batch Load
```
Select distinct party.PARTY_ID, NULLfrom HZ_PARTIES party, HZ_CUST_ACCOUNTS accntwhere accnt.status='A' and party.PARTY_ID = accnt.party_id;
```
When you run the HZ_EBI_CUST_LOAD.GENERATE_EVENTS procedure for the first time, all the active party_id (customer_id) records are loaded into the HZ_EBI_CUST_LOAD_LOG file and the number of events specified by the Max_Events parameter is generated. Each event contains the number of account records specified by the Batch_Size parameter.
After all the events are raised and consumed successfully, you can run the HZ_EBI_CUST_LOAD.PURGE_EVENTLOG to purge all the records from the HZ_EBI_CUST_LOAD_LOG safely.

Example 2-2 illustrates the script provided with the Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP for the account batch load process:
Example 2-2 Script for Account Batch Load Process
```
DECLARE
l_BATCH_SIZE NUMBER;
l_MAX_EVENTS NUMBER;
X_ERR_MSG VARCHAR2 (200);
BEGIN
l_BATCH_SIZE := 20;
l_MAX_EVENTS := 50;
X_ERR_MSG := NULL;
HZ_EBI_CUST_LOAD.GENERATE_EVENTS(
p_batch_size => l_BATCH_SIZE,
p_max_events => l_MAX_EVENTS,
X_ERR_MSG => X_ERR_MSG
);
END;
```

2.4 Batch Loading Product Data

This topic provides an overview of product data load and synchronization and discusses how to perform the initial batch load of product data.

The Oracle Lead to Order Integration Pack for Oracle CRM On Demand and Oracle E-Business Suite PIP provides product data integration between Oracle E-Business Suite and Oracle CRM On Demand to facilitate the Opportunity to Quote and Opportunity to Order business processes and to enable users to view available products from within CRM On Demand. After the product data is loaded into CRM On Demand, subsequent updates to mapped product attributes made within Oracle E-Business Suite propagate to CRM On Demand, but not the other way around from CRM On Demand to Oracle E-Business Suite.

If you plan to enable the Opportunity-to-Quote or Opportunity-to-Order integration business flows, having the product data from Oracle E-Business Suite represented within CRM On Demand is a required step. You might require that only a subset of the product data that exists in Oracle E-Business Suite be represented in CRM On Demand, or you might want to represent all your product data in both systems.

This PIP enables you to define and extract a specific set of product data from Oracle E-Business Suite and provide a means to have that data loaded into CRM On Demand. If you have large data volumes to be extracted and loaded from Oracle E-Business Suite to CRM On Demand, you can analyze the data to be loaded and can segment the data to be loaded in batches, depending on the time you have allocated to complete the load. Data should be organized and loaded based on the priority in which the data is used by end users.

2.4.1 Understanding the Product Batch Flow Process

Figure 2-2 illustrates the flow for synchronizing product data between Oracle E-Business Suite and CRM On Demand:

Figure 2-2 Batch Load Item Flow Sequence Diagram

When you initiate the product batch load process, the following events occur:

The BatchLoadItemEbizEventConsumer listens to business events and receives the WF_EVENT_T_message event payload for the batch itemLoad event.

The BatchLoadItemEbizEventConsumer routes the complete event payload to BatchLoadProductEbizReqABCSImpl.
The BatchLoadProductEbizReqABCSImpl service calls the Enterprise Business Service (EBS) based on the event payload, and then transforms to the SyncItemListEBM and invokes the ItemEBSV2 with operation SyncItemList.
1. As part of the enrichment process, the BatchLoadProductEbizReqABCSImpl queries the EGO_ITEM_SYNC_OP_V view object through the Oracle E-Business Suite adapter QuerySimpleItemBatchLoadAdapter based on the event payload to get the EgoItemSyncOpVCollection_msg.
2. As part of the transformation process, the BatchLoadProductEbizReqABCSImpl transforms the EgoItemSyncOpVCollection_msg message into the SyncItemListEBM.
3. SyncItemListEBM invokes the ItemEBSV2 with the operation SyncItemList. The ItemEBSV2 is a routing service with several operations on the Item Enterprise Business Message (EBM).
4. In the process, the cross-reference is created by concatenation of the inventory item ID, organization ID, and operating unit.
The ItemEBSV2 routes the SyncItemListEBM to the SyncItemListCRMODProvABCSImpl.
The SyncItemListCRMODProvABCSImpl transforms the SyncItemListEBM to the CRM On Demand product message and then calls the CRM On Demand product Upsert web service to synchronize the product.

The CRM On Demand web service completes the request and returns a response message. In the process, the cross-reference is linked to the CRM On Demand IDs of the product.

2.4.2 Solution Assumptions and Constraints

Before running the batch data loads, you must run the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure to generate the batchload events.

The procedure selects all active products for a given organization from Oracle E-Business Suite. To select only a subset of the products in Oracle E-Business Suite for batch data load, you must modify the PL/SQL filter clause in the GENERATE_EVENTs procedure.
To synchronize products from multiple organizations, you must run the procedure for each organization individually.

You must run the EGO_EBI_ITEM_LOAD.PURGE_EVENTLOG procedure after synchronizing all the products from one organization and before synchronizing the next organization.
The batch size specified for the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure must not be greater than 20.
Only orderable products from the Inventory Validation organization are synchronized.

2.4.3 Running the Product Batch Load Process

This topic describes how to perform the product batch load.

To run the product batch load:

Run the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure once for each organization for which you want to synchronize product data from Oracle E-Business Suite to CRM On Demand, specifying values for the BatchSize and MaxEvents parameters.

All active products for a given organization from Oracle E-Business Suite are selected.
To select a subset of the products in an organization for batch data load, modify the PL/SQL filter clause shown in Example 2-3 in the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure:
Example 2-3 PL/SQL Filter Clause
```
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, NULL
FROM MTL_SYSTEM_ITEMS_B
WHERE ORGANIZATION_ID = p_organization_id
AND bom_item_type in (1, 2, 4)
AND customer_order_flag = 'Y'
AND customer_order_enabled_flag = 'Y';
```
The procedure publishes items from the organization provided in the parameter p_organization_id. The p_organization_id is the ID of the inventory validation organization.

When you run the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure for the first time, all the eligible item IDs are entered into the EGO_EBI_ITEM_LOAD_LOG file and the number of events specified in the Max_Events parameter are generated. Each event contains the number of items specified by the Batch_Size parameter.
After all the events are raised and consumed successfully, run the EGO_EBI_ITEM_LOAD.PURGE_EVENTLOG procedure to purge all the records from the EGO_EBI_ITEM_LOAD_LOG.

To publish items from another organization, repeat steps 1 through 3.

You can use the script in Example 2-4 for loading batches of product data. Copy the script and edit as appropriate for your implementation:

Example 2-4 Script to Load Batches of Product Data

DECLARE
l_BATCH_SIZE NUMBER; l_MAX_EVENTS NUMBER; l_ERR_MSG VARCHAR2(200); p_org_id NUMBER;
BEGIN
l_BATCH_SIZE := 20; l_MAX_EVENTS := 50; l_ERR_MSG := NULL; p_org_id := 204;
EGO_EBI_ITEM_LOAD.GENERATE_EVENTS(
p_organization_id=> p_org_id, p_batch_size => l_BATCH_SIZE, p_max_events => l_MAX_EVENTS, X_ERR_MSG => l_ERR_MSG
); END;