| Oracle® Application Integration Architecture Siebel CRM Integration Pack for Oracle Order Management: Order to Cash Implementation Guide Release 3.1.1 Part Number E39434-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Application Integration Architecture Siebel CRM Integration Pack for Oracle Order Management: Order to Cash Implementation Guide Release 3.1.1 Part Number E39434-01 |
|
|
PDF · Mobi · ePub |
This chapter provides step-by-step instructions on how to configure the Oracle Data Integrator (ODI) and then perform initial data loads for customer data, product data, price list data, and assets data.
This chapter includes the following sections:
To see the prerequisites for running initial data loads, see Chapter 13, "Reviewing Prerequisites and Data Requirements."
Complete the following steps to deploy the Order to Cash ODI repository components.
Note:
After the scripts run successfully, log in to the ODI Topology Manager and Designer to verify that all of the components loaded successfully. Also, test to ensure that the data server connection works.
This section can be bypassed if ODI details were provided when configuring the Order to Cash: Siebel CRM - EBS integration. Otherwise, complete the following steps to provide the ODI installation and connection details.
For more information about configuring the Order to Cash: Siebel CRM - EBS integration, see the Oracle Application Integration Architecture Installation and Upgrade Guide for Pre-Built Integrations.
To provide the ODI installation and connection details:
To understand the ODI setup related prerequisites, see the Oracle Application Integration Architecture Installation and Upgrade Guide for Pre-Built Integrations, "Configuring and Deploying the Order to Cash: Siebel CRM - EBS Integration", Configuring ODI (Optional).
Navigate to <AIA_Instance>/bin and run the following command to configure the installation environment:
For Windows: aiaenv.bat
For Linux: aiaenv.sh
Navigate to <AIA_HOME>/bin and run the following command to launch the Oracle AIA Configuration Wizard:
For Windows: aiaconfig.bat
For Linux: ./aiaconfig.sh
Click Next.
Select the Order to Cash: Siebel CRM - EBS Initial Loads. Click Next.
Specify ODI access details, master repository details, and work repository for Order to Cash details.
For more information and description of the fields for ODI related configuration screens, see the Oracle Application Integration Architecture Installation and Upgrade Guide for Pre-Built Integrations, "Configuring and Deploying the Order to Cash: Siebel CRM - EBS Pre-Built Integration", Deployment Configuration Wizard Interview.
See the sections related to the following screens:
ODI Access Details Screen (Optional)
ODI Master Repository Details Screen (Optional)
ODI Work Repository Details for Order to Cash Screen (Optional)
Set the environment variables using:
Source: aiaenv.sh from AIA_INSTANCE/bin
To deploy components for individual bulk loads:
ant -f $AIA_HOME/Infrastructure/Install/AID/AIAInstallDriver.xml -DPropertiesFile=$AIA_INSTANCE/config/AIAInstallProperties.xml -DDeploymentPlan=$AIA _HOME/services/core/BulkDataProcess/EbizToSiebel/Customer/ O2C_ODI_Customer_InstallScript.xml
ant -f $AIA_HOME/Infrastructure/Install/AID/AIAInstallDriver.xml -DPropertiesFile=$AIA_INSTANCE/config/AIAInstallProperties.xml -DDeploymentPlan=$AIA _HOME/services/core/BulkDataProcess/EbizToSiebel/Product/ O2C_ODI_Product_InstallScript.xml
ant -f $AIA_HOME/Infrastructure/Install/AID/AIAInstallDriver.xml -DPropertiesFile=$AIA_INSTANCE/config/AIAInstallProperties.xml -DDeploymentPlan=$AIA _HOME/services/core/BulkDataProcess/EbizToSiebel/PriceList/ O2C_ODI_PriceList_InstallScript.xml
ant -f $AIA_HOME/Infrastructure/Install/AID/AIAInstallDriver.xml -DPropertiesFile=$AIA_INSTANCE/config/AIAInstallProperties.xml -DDeploymentPlan=$AIA _HOME/services/core/BulkDataProcess/EbizToSiebel/Assets/ O2C_ODI_Asset_InstallScript.xml
Set environment as:
aiaenv.bat from AIA_INSTANCE\bin
To deploy components for individual bulk loads:
ant -f %AIA_HOME%\Infrastructure\Install\AID\AIAInstallDriver.xml -DPropertiesFile=%AIA_INSTANCE%\config\AIAInstallProperties.xml -DDeploymentPlan=%AIA _HOME%\services\core\BulkDataProcess\EbizToSiebel\Customer\ O2C_ODI_Customer_InstallScript.xml
ant -f %AIA_HOME%\Infrastructure\Install\AID/AIAInstallDriver.xml -DPropertiesFile=%AIA_INSTANCE%\config\AIAInstallProperties.xml -DDeploymentPlan=%AIA _HOME%\services\core\BulkDataProcess\EbizToSiebel\Product\ O2C_ODI_Product_InstallScript.xml
ant -f %AIA_HOME%\Infrastructure\Install\AID\AIAInstallDriver.xml -DPropertiesFile=%AIA_INSTANCE%\config\AIAInstallProperties.xml -DDeploymentPlan=%AIA _HOME%\services\core\BulkDataProcess\EbizToSiebel\PriceList\ O2C_ODI_PriceList_InstallScript.xml
ant -f %AIA_HOME%\Infrastructure\Install\AID\AIAInstallDriver.xml -DPropertiesFile=%AIA_INSTANCE%\config\AIAInstallProperties.xml -DDeploymentPlan=%AIA _HOME%\services\core\BulkDataProcess\EbizToSiebel\Assets\ O2C_ODI_Asset_InstallScript.xml
For more information about how to set up a data server for a non-Oracle database, see Appendix A, "Configuring ODI-Based Initial Loads against a Non-Oracle Target Database."
The following steps are manual changes you must make to the Order to Cash ODI components for ODI version 11.1.1.5.
In ODI 11.1.1.5.0, after the components are deployed, log in to ODI designer and change the ODI Models and then the ODI Interfaces. These changes are textual changes to attribute names only.
Perform these steps:
Click the + sign to open the Models one by one.
Double click the Column attribute to open the Definition page as shown in Figure 14-1. Not all models have a Column attribute. Only those models that have a Column attribute must be modified.
In the Definition page, shown in Figure 14-2, change the Name, Alias, and Resource Name from Column to Column_ and save.
Next, click + to open Column_ click + on Columns, and then open COLUMNORDER as shown in Figure 14-3.
In the Definition page shown in Figure 14-4, change the Name and Short Description from COLUMNORDER to COLUMN_ORDER and click Save.
Repeat these steps for all Models that have a Column attribute.
Perform the following steps:
Under each of the projects, open the interfaces one by one.
In the interface, click the Quick-Edit tab at the bottom of the screen.
Click + sign next to Joins to open the joins.
In the Join Expression, shown in Figure 14-5 change all of the COLUMNORDER to COLUMN_ORDER and click Save.
Repeat these steps for all interfaces in all projects.
Finally, regenerate all of the scenarios.
Perform these steps:
Select Topology Tab from the ODI Menu.
Click the + sign of Technologies to expand it.
Click the + sign of XML folder.
Select CurrencyCodeDS, right click, and select Open.
Click the JDBC tab to view the JDBC URL.
Check the value in the JDBC URL and append the following string &ro=true to the end of the URL. Ensure that this is not present in the URL.
The JDBC URL for this DataSource should look like this:
Xxxx/oracle/AIAHome/AIAMetaData/dvm/CURRENCY_CODE.dvm&s=CURRENCYCODE&standalone=true&ro=true
Figure 14-6 shows the Currency Code DS screen:
Siebel CRM Integration Pack for Oracle Order Management provides a bulk load feature to move data from Oracle E-Business Suite (Oracle EBS) into Siebel Customer Relationship Management (Siebel CRM).
The bulk load feature extracts customer data from Oracle EBS, transforms it into Siebel Enterprise Integration Manager (Siebel EIM) interface table data structures, and moves it into the Siebel EIM interface tables. Siebel EIM tables are intermediate interface tables that act as staging areas between the base tables in the Siebel database and other databases. A Siebel loader program moves the data from the EIM interface tables into the application base tables of Siebel CRM.
During the initial bulk load, only active organization parties and their child objects, including organization contacts that are associated with an account in Oracle, are extracted from Oracle and loaded into Siebel CRM. Organization contacts are person parties that have a relationship with the organization party.
While Oracle organization parties are loaded into Siebel CRM as accounts, Oracle organization contacts are loaded into Siebel CRM as account contacts. (Similarly, a contact record in Siebel CRM is loaded for a contact person party in Oracle EBS).
Note:
Person parties and their child objects are not loaded because Siebel CRM Integration Pack for Oracle Order Management does not support a B2C (business to customer) ordering flow.
The Customer bulk load consists of these steps:
Load customer data from Oracle EBS to Siebel EIM
Use ODI to populate the Siebel EIM table with data from the Oracle EBS table. Account, address, and contact entities are loaded into the respective EIM tables. The Oracle Application Integration Architecture (Oracle AIA) cross-reference table is populated with the Oracle EBS and Common ID values.
Load EIM values to Siebel base tables.
Use an EIM job to transfer the data from EIM to the Siebel base tables.
Load Siebel base tables to the Oracle AIA cross-reference table.
Use ODI to move the Siebel ID values back into the Oracle AIA cross-reference table, correlating to the Oracle EBS and Common ID values that were loaded in step 1.
For initial data loads, you must perform some EIM activities.
For more information about the pre- and post-EIM activities and execution, see the Siebel Enterprise Integration Manager Administration Guide, Siebel EIM Tables.
You can move data from Oracle EBS to Siebel EIM tables using either ODI Designer or a command line prompt.
To move data from Oracle EBS using ODI designer:
Log in to the Designer.
Expand the project CustomerInitialBulkLoad.
Expand the folder Customer Bulk Load.
Expand the package.
Right-click the package titled OracleEbiz_to_XREF_to_EIM and select Execute from the menu.
Select My_Context in the Execution box.
To move data from Oracle EBS using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For Windows: startscen ORACLEEBIZ_TO_XREF_TO_EIM 001 MY_CONTEXT
For UNIX: ./startscen.sh ORACLEEBIZ_TO_XREF_TO_EIM 001 MY_CONTEXT
To move data from EIM to Siebel base tables:
Verify that the Product.ifb, Pricelist.ifb, Customer.ifb, and Asset.ifb files are available in the Admin directory of the installed Siebel server.
Example: in the D:\ses\siebsrvr\Admin directory
Modify Customer.ifb as follows and save.
Under the DEFAULT COLUMN list for [IMPORT CONTACT], add the following:
DEFAULT COLUMN = CON_ONDMNDSYNCFLG, "N"
Under the DEFAULT COLUMN list for [IMPORT ADDRESS], add the following:
DEFAULT COLUMN = ADDR_GEOCD_VLD_FLG,"N"
DEFAULT COLUMN = ADDR_SYNC_SUCC_FLG,"N"
DEFAULT COLUMN = ADDR_VERIFIED_FLG,"N"
Under [UPDATE ACCOUNT NUMBER], ensure that the database user name from the Siebel environment is prefixed to the table name. Here is an example:
SESSION SQL = "UPDATE ORA05223.EIM_ACCOUNT SET OU_NUM = T_ORG_EXT__RID WHERE IF_ROW_BATCH_NUM=100"
Log in to the Siebel E-Business Applications using the Administrator login.
Example: proper credentials like SADMIN/SADMIN
Go to the Site Map icon (the globe that appears at the top left of the home page).
Navigate to the Administration - Server Management screen.
Click the Jobs link and navigate to Server Management, Jobs.
Click New in the Jobs applet.
Ensure that you set the component to Enterprise Integration Manager in this new job.
Go to Job Parameters and enter the following parameter values:
Configuration File: Customer.ifb
Error Flag: 1
SQL Trace Flag: 8
Trace Flag: 1
Ensure that the job finishes with a status of Success.
The color of the status bar changes to green.
Ensure that the data appears properly in the Siebel Business Applications User Interface in relevant Administration screens, such as Accounts, Contacts, Products, Price List, and so on.
To check whether the Siebel EIM loaded all records, check the IF_ROW_STAT column for the corresponding EIM table.
The IF_ROW_STAT field can have following status values:
IF_ROW_STAT = for import: Job to be run or job running.
IF_ROW_STAT = Partially_imported: Some mandatory field is missing for that record.
IF_ROW_STAT = Imported: Job ran successfully for that record.
IF_ROW_STAT= dup_row_exists: Siebel base table has that record.
You can move data from Siebel base tables to Oracle AIA cross-reference tables using either ODI Designer or a command line prompt.
To move data from Siebel base tables using ODI Designer:
Log in to the Designer.
Expand the project CustomerInitialBulkLoad.
Expand the folder Customer_Bulk_Load.
Expand the package.
Right-click the package titled Siebel_to_XREF and select Execute from the menu.
Select My_Context in the Execution box.
To move data from Oracle EBS using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For Windows: Startscen SIEBEL_TO_XREF 001 MY_CONTEXT
For UNIX:./startscen.sh SIEBEL_TO_XREF 001 MY_CONTEXT
Use this table to verify the data:
Table 14-1 Information to verify Data After the Load
| Entity | Database | Query | Inference |
|---|---|---|---|
|
ACCOUNTS |
EBIZ |
select DISTINCT HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID from APPS.HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNTS, APPS.HZ_PARTIES HZ_PARTIES, APPS.HZ_CUST_ACCT_SITES_ALL HZ_CUST_ACCT_SITES_ALL where (HZ_PARTIES.PARTY_TYPE='ORGANIZATION') And (NVL(HZ_CUST_ACCOUNTS.STATUS,'A')='A') And (HZ_PARTIES.STATUS='A') And (HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID=HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID) AND (HZ_PARTIES.PARTY_ID=HZ_CUST_ACCOUNTS.PARTY_ID); |
Number of active accounts in Oracle EBS |
|
ACCOUNTS |
SIEBEL |
select count(*) from s_org_ext where db_last_upd_src='EIM'; |
Number of accounts successfully transferred to Siebel from the EIM job. |
|
CONTACTS |
EBIZ |
Select DISTINCT HZ_PERSON_PROFILES.PARTY_ID C1_VALUE from APPS.HZ_PERSON_PROFILES HZ_PERSON_PROFILES, APPS.HZ_RELATIONSHIPS HZ_RELATIONSHIPS, APPS.HZ_CUST_ACCT_SITES_ALL HZ_CUST_ACCT_SITES_ALL, APPS.HZ_CUST_ACCOUNT_ROLES HZ_CUST_ACCOUNT_ROLES, APPS.HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNTS, APPS.HZ_PARTIES HZ_PARTIES where (1=1) And (HZ_RELATIONSHIPS.OBJECT_TYPE='ORGANIZATION') And (HZ_RELATIONSHIPS.RELATIONSHIP_CODE='CONTACT_OF') And (HZ_RELATIONSHIPS.STATUS='A') And (HZ_CUST_ACCOUNTS.STATUS='A') And (HZ_PARTIES.STATUS='A') And (HZ_CUST_ACCOUNTS.PARTY_ID=HZ_PARTIES.PARTY_ID) AND (HZ_PERSON_PROFILES.PARTY_ID=HZ_RELATIONSHIPS.SUBJECT_ID) AND (HZ_RELATIONSHIPS.OBJECT_ID=HZ_CUST_ACCOUNTS.PARTY_ID) AND (HZ_RELATIONSHIPS.PARTY_ID=HZ_CUST_ACCOUNT_ROLES.PARTY_ID) AND (HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID=HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID) AND (HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ID=HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID) |
Number of contacts of the active accounts and parties. |
|
CONTACTS |
SIEBEL |
select count(*) from s_contact where db_last_upd_src='EIM'; |
Number of contacts successfully transferred to Siebel from the EIM job. |
|
ADDRESS |
EBIZ |
select DISTINCT HZ_LOCATIONS.LOCATION_ID C1_LOCATION_ID from APPS.HZ_LOCATIONS HZ_LOCATIONS, APPS.HZ_PARTY_SITES HZ_PARTY_SITES, APPS.HZ_PARTIES HZ_PARTIES, APPS.HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNTS, APPS.HZ_CUST_ACCT_SITES_ALL HZ_CUST_ACCT_SITES_ALL where (1=1) And (HZ_PARTIES.PARTY_TYPE='ORGANIZATION') And (HZ_PARTIES.STATUS='A') And (HZ_CUST_ACCOUNTS.STATUS='A') And (HZ_PARTY_SITES.PARTY_ID=HZ_PARTIES.PARTY_ID) AND (HZ_LOCATIONS.LOCATION_ID=HZ_PARTY_SITES.LOCATION_ID) AND (HZ_CUST_ACCOUNTS.PARTY_ID=HZ_PARTIES.PARTY_ID) AND (HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID=HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID) |
Number of addresses of the active accounts and parties. |
|
ADDRESS |
SIEBEL |
select count(*) from S_ADDR_PER where db_last_upd_src='EIM'; |
Number of addresses successfully transferred to Siebel from the EIM job. |
Use the bulk load feature to move product data from Oracle EBS into Siebel CRM.
Bulk loading product data is similar to bulk loading customer data. The bulk load feature extracts product data from Oracle EBS, transforms it into the EIM interface table data structures, and moves it into the Siebel EIM interface tables. A Siebel loader program moves the data from the EIM interface tables into the application base tables of Siebel CRM.
The Product bulk load consists of these steps:
Load Oracle EBS to EIM.
Use ODI to populate the Siebel EIM table with data from the Oracle EBS table. The Oracle AIA cross-reference table is populated with the Oracle EBS and Common ID values.
Transfer EIM data to the Siebel Base table.
Use an EIM job to transfer the data from EIM to the Siebel Base table.
Move values from the Siebel Base table to the Oracle AIA cross-reference table.
Use ODI to move the Siebel ID values back into the Oracle AIA cross-reference table, correlating to the Oracle EBS and Common ID values that were loaded in step 1.
For initial data loads, you must perform some EIM activities.
For more information about the pre- and post-EIM activities and execution, see the Siebel Enterprise Integration Manager Administration Guide, Siebel EIM Tables.
You can move data from Oracle EBS to Siebel EIM tables using either ODI Designer or a command line prompt.
To move data from Oracle EBS using the ODI Designer:
Log in to the Designer.
Expand the project Product Initial Load.
Expand the folder Bulk Load.
Expand the package.
Right-click the package titled Load_Oracle_Ebiz_Product_To_Siebel. Select Execute from the menu.
Select the Product Development context in the execution box.
To move data from Oracle EBS using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For Windows: Startscen LOAD_ORACLE_EBIZ_PRODUCT_TO_SIEBEL 001 PRODUCTDEVELOPMENT
For UNIX: ./startscen.sh LOAD_ORACLE_EBIZ_PRODUCT_TO_SIEBEL 001 PRODUCTDEVELOPMENT
To move data from EIM to Siebel base tables:
Verify that the Product.ifb, Pricelist.ifb, Customer.ifb, Asset.ifb files are available in the Admin directory of the installed Siebel server.
For example, in the D:\ses\siebsrvr\Admin directory
Modify Product.ifb as follows and save:
Under the DEFAULT COLUMN list for [IMPORT EIM INT], add the following:
DEFAULT COLUMN = APPLY_MEM_COMP_FLG,"N"
DEFAULT COLUMN = ONDEMAND_SYNC_FLG,"N"
DEFAULT COLUMN = RSLV_AMBIGUITY_FLG,"N"
Log in to the Siebel E-Business Applications using the Administrator login.
For example, use proper credentials such as SADMIN/SADMIN
Go to the Site Map icon (the globe that appears at the top left of the home page).
Navigate to the Administration - Server Management screen.
Click the Jobs link and navigate to Server Management, Jobs.
Click New in the Jobs applet.
Ensure that the component is set to Enterprise Integration Manager in this new job.
Go to Job Parameters and enter the following parameter values:
Configuration File: Product.ifb
Username: The administrator username ex. SADMIN
Password: The administrator password ex. SADMIN
Error Flag: 1
SQL Trace Flag: 8
Trace Flag: 1
Ensure that the job finishes with a status of Success.
The color of the status bar changes to green.
Release the products after getting the product data imported into the base table.
See the EIM documentation provided by Siebel.
Ensure that the data appears correctly in the Siebel Business Applications User Interface in the relevant Administration screens such as Accounts, Contacts, Products, Price List, and so on.
To check whether the Siebel EIM loaded all records, check the IF_ROW_STAT column for the corresponding EIM table.
The IF_ROW_STAT field can have the following status values:
IF_ROW_STAT = for import: Job to be run or job running.
IF_ROW_STAT = Partially_imported: Some mandatory field missing for that record.
IF_ROW_STAT = Imported: Job ran successfully for that record.
IF_ROW_STAT= dup_row_exists: Siebel base table has that record.
Note:
Before they can be used in transactions, all products must be released in Siebel after the EIM import,
For more information about how to release all products within Siebel, see the Siebel 8113 FP installation instructions (MOS Article ID 880452.1).
For the products to be present in the S_VOD table, navigate to Administration - Business Service, Simulator and execute the business service "ISS Authoring Import Export Service" with method Post_EIM_Upgrade.
Name: ISS Authoring Import Export Service
Method: Post_EIM_Upgrade
Name: ReportOnly
Value: FALSE
For more information about how products can be imported using EIM, see MOS Article ID 485530.1.
You can move data from Siebel base tables to Oracle AIA cross-reference tables using either ODI Designer or a command line prompt.
To move data from Siebel base tables using ODI Designer:
Log in to the Designer.
Expand the project Product Initial Load.
Expand the folder Bulk Load.
Expand the package.
Right-click the package titled Load_Siebel_To_XREF_Data. Select Execute from the menu.
Select context as Product Development in the Execution box.
To move data from Siebel base tables using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For Windows: Startscen LOAD_SIEBEL_TO_XREF_DATA 001 PRODUCTDEVELOPMENT
For UNIX: ./startscen.sh LOAD_SIEBEL_TO_XREF_DATA 001 PRODUCTDEVELOPMENT
After the process finishes and before moving data from the Siebel EIM table to the Siebel base table, verify that the product data was loaded into the Siebel EIM table by completing these actions:
Select xref_column_name, count(*) from xref_data where xref_table_name = 'oramds:/apps/AIAMetaData/xref/ITEM_ITEMID.xref'GROUP BY xref_column_name (to be run in FMW DB).
Select count(*) from eim_prod_int (to be run in Siebel DB).
Select count(*) from eim_prod_int1 (to be run in Siebel DB).
Select count(*) from eim_prodinvloc (to be run in Siebel DB).
The count should be the same.
Verify that the Oracle AIA cross-reference tables are populated with the Oracle EBS and Common ID values for the product.
Select count(*) from s_prod_int (to be run in Siebel DB) to verify that the product data was loaded into the Siebel base table after you move data from the Siebel EIM table to the Siebel base table.
Select xref_column_name, count(*) from xref_data where xref_table_name = 'oramds:/apps/AIAMetaData/xref/ITEM_ITEMID.xref'GROUP BY xref_column_name (to be run in FMW DB) to verify that the Siebel ID is populated after you move data from the Siebel base table to the Oracle AIA cross-reference table.
Verify that the Oracle AIA cross-reference tables are populated with the Oracle EBS, Common, and Siebel ID values for the product.
Use the bulk load feature to move active price list data, such as the following data, related to order capture from Oracle EBS into Siebel CRM:
Active header information, such as name, currency, start date, and end date.
Line information, such as product, list price, start date, and end date.
Bulk loading of price list data is similar to loading other data types in that the bulk load feature extracts price list data from Oracle EBS, transforms it into the EIM interface table data structures, and moves it into the Siebel EIM interface tables. A Siebel loader program moves the data from the EIM interface tables into the application base tables of Siebel CRM.
Bulk loading of price list data is different in that you can also synchronize changes to active price list data in the Oracle EBS database to Siebel CRM database. These changes, following the initial bulk load, can be either creation of new price lists or updates to the header and line information of existing price lists. The incremental bulk loads create or update price lists or the header and line information in the Siebel CRM database, depending on whether the price lists or the header and line information exists in the Siebel CRM database.
For more information about initial data loads, see Chapter 2, "Loading Initial Data."
The Price List bulk data load consists of three steps:
Load Oracle EBS to EIM.
Use Oracle Data Integrator (ODI) to populate the Siebel EIM table (EIM_PRI_LST) with the data from the Oracle EBS table (QP_CRMINTEG_PRICELIST_V). The Oracle AIA cross-reference table is populated with the Oracle EBS and Common ID values.
Transfer EIM data to the Siebel Base table.
Use an EIM job to transfer the data from EIM to the Siebel Base table.
Move values from the Siebel Base table to the Oracle AIA cross-reference table.
Use ODI to move the Siebel ID values back into the Oracle AIA cross-reference table (XREF_DATA), correlating to the Oracle EBS and Common ID values that were loaded in step 1.
For initial data loads, you must perform some EIM activities.
For more information about the pre- and post-EIM activities and execution, see the Siebel Enterprise Integration Manager Administration Guide, Siebel EIM Tables.
You can move data from Oracle EBS to Siebel EIM by running the ODI package either from ODI Designer or a command line prompt.
To move data from Oracle EBS using ODI Designer:
Log in to the ODI Designer.
Under the project PriceListBulkLoad, run the package LoadEbizPriceListDataToSiebelPkg.
To move data from Oracle EBS using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Run the following ODI scenario:
For Windows: startscen.bat LOADEBIZPRICELISTDATATOSIEBEL 001 PRICELISTBULKLOAD
For UNIX: ./startscen.sh LOADEBIZPRICELISTDATATOSIEBEL 001 PRICELISTBULKLOAD
After the process finishes, verify that the Price List and Lines data was loaded into the Siebel EIM table by completing these actions:
Select count(*) from QP_CRMINTEG_PRICELIST_V (to be run in EBIZ DB).
Select count(*) from eim_pri_lst (to be run in Siebel DB).
The count should be the same.
Verify that the Oracle AIA cross-reference tables are populated with the Oracle EBS and Common ID values for the price list by completing these actions:
Select distinct list_header_id from QP_CRMINTEG_PRICELIST_V (EBIZ DB).
Select count(*) from xref_data, where xref_table_name is something like 'PRICELIST_ID' and xref_column_name is something like 'EBIZ_01'.
Select count(*) from xref_data, where xref_table_name is something like 'PRICELIST_ID' and xref_column_name is something like 'COMMON'.
The count should be the same.
Note:
QP_CRMINTEG_PRICELIST_V retrieves records for the pricing organization setup in the profile option QP: Item Validation Organization at the site level. Verify that the profile option is set up correctly.
To move data from EIM to Siebel base tables:
Verify that the Product.ifb, Pricelist.ifb, Customer.ifb, and Asset.ifb files are available in the Admin directory of the installed Siebel server.
For example, in the D:\ses\siebsrvr\Admin directory
For Pricelist.ifb, comment the second process INCLUDE = "UPDATE INTEGRATION ID" using a terminate symbol (so that it becomes inactive and only the first process is run), for example:
[IMPORT ALL PRICELISTS] TYPE = SHELL INCLUDE = "IMPORT PRICELIST" ;INCLUDE = "UPDATE INTEGRATION ID"
Log in to Siebel E-Business Applications using the Administrator login.
For example, proper credentials such as SADMIN/SADMIN
Go to the Site Map icon (the globe that appears at the top left of the home page).
Navigate to the Administration - Server Management screen.
Click the Jobs link and navigate to Server Management, Jobs.
Click New in the Jobs applet.
Ensure that the component is set to Enterprise Integration Manager in this new job.
Go to Job Parameters and enter the following parameter values:
Configuration File: Pricelist.ifb
Username: The administrator username ex. SADMIN
Password: The administrator password ex. SADMIN
Error Flag: 1
SQL Trace Flag: 8
Trace Flag: 1
Ensure that the job finishes with a status of Success.
The color of the status bar changes to green.
Ensure that the Siebel Business Applications User Interface displays the data properly in the relevant Administration screens, such as Accounts, Contacts, Products, Price List, and so on.
To check whether the Siebel EIM loaded all records, check the IF_ROW_STAT column for the corresponding EIM table.
The IF_ROW_STAT field can have following status values:
IF_ROW_STAT = for import: Job to be run or job is running.
IF_ROW_STAT = Partially_imported: Some mandatory field is missing for that record.
IF_ROW_STAT = Imported: Job ran successfully for that record.
IF_ROW_STAT= dup_row_exists: Siebel base table has that record.
To create the cross-reference for the Siebel Price List IDs, you can run the ODI package from ODI Designer or a command line prompt.
To create the cross-reference for the Siebel price list IDs from ODI Designer:
Log in to the ODI Designer.
Under the project PriceListBulkLoad, run the package LoadSiebelPriceListDataToXREFPkg.
To create the cross-reference for the Siebel price list IDs from a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Run the following ODI scenario:
For Windows: startscen.bat LOADSIEBELPRICELISTDATATOXREF 001 PRICELISTBULKLOAD
For UNIX: ./startscen.sh LOADSIEBELPRICELISTDATATOXREF 001 PRICELISTBULKLOAD
After the process finishes, the cross-references for the Siebel Price List IDs are created. Perform these actions to verify the data:
Select distinct list_header_id from QP_CRMINTEG_PRICELIST_V (to be run in the Oracle EBS database).
Select count(*) from s_pri_lst, where integration_id is not null (to be run in the Siebel database)
Select count(*) from xref_data, where xref_table_name is something like 'PRICELIST_ID' and xref_column_name is something like 'SEBL_01'
The count should be the same.
Note:
After running the ODI package to populate the X-REF Database, if required, you can run Siebel EIM again to nullify the integration_id in Siebel. This is an optional step. The format of the ifb file for the second run looks like this:
[IMPORT ALL PRICELISTS] TYPE = SHELL ;INCLUDE = "IMPORT PRICELIST" INCLUDE = "UPDATE INTEGRATION ID" A
All the other job parameters remain the same in the ifb file.
The purpose of an incremental load is to move new price lists and lines or any updates to existing price lists into Siebel CRM for use in the order-capture process. Use ODI to manage incremental loads.
To perform the incremental load:
Use ODI to populate the Siebel EIM table (EIM_PRI_LST) with data from the Oracle EBS table (QP_CRMINTEG_PRICELIST_V) as specified in $AIA_HOME/services/core/BulkDataProcess/EbizToSiebel/PriceList/PriceListSource.xml.
The Oracle AIA cross-reference table is populated with the Oracle EBS and Common ID values.
Run the EIM load process to transfer data to the Siebel Base table.
Load the Siebel ID values in the Oracle AIA cross-reference table (XREF_DATA).
To load Price List data from Oracle EBS as specified in the XML file at $AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml into Siebel EIM and to create cross-references for Oracle EBS and Common IDs, you can run the following ODI scenario from the directory ODI_HOME/bin using either ODI Designer or a command line prompt.
Log in to the ODI Designer.
Under the project PriceListBulkLoad, run the package LoadEbizIncPriceListDataToSiebelPkg.
Windows: startscen.bat LOADEBIZINCPRICELISTDATATOSIEBEL 001 PRICELISTBULKLOAD
LINUX: ./startscen.sh LOADEBIZINCPRICELISTDATATOSIEBEL 001 PRICELISTBULKLOAD
After the process finishes, the Price List and Lines data is loaded into the Siebel EIM table. Only those price lists specified in the XML file are loaded.:
To verify the data, select a unique PL_NAME from eim_pri_lst (Siebel DB). The price list names should match the ones in the XML file.
To verify that the Oracle AIA cross-reference tables are populated with the Oracle EBS and Common ID values for the price list, complete this action:
Select list_header_id from QP_CRMINTEG_PRICELIST_V ($AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml) (to be run in the Oracle EBS database) and ensure that the IDs returned are in the cross-reference table.
To run the Siebel EIM load:
Verify that the Product.ifb, Pricelist.ifb, Customer.ifb, and Asset.ifb files are available in the Admin directory of the installed Siebel server.
For example, in the D:\ses\siebsrvr\Admin directory
For Pricelist.ifb, comment the second process using a terminate symbol (so that it becomes inactive and only the first process is run). For example:
[IMPORT ALL PRICELISTS] TYPE = SHELL INCLUDE = "IMPORT PRICELIST" ;INCLUDE = "UPDATE INTEGRATION ID"
Log in to the Siebel E-Business Application using the Administrator login, for example, proper credentials such as SADMIN/SADMIN
Go to the Site Map icon (the globe that appears at the top left of the home page).
Navigate to the Administration - Server Management screen.
Click the Jobs link and navigate to Server Management, Jobs.
Click New in the Jobs applet.
Ensure that the component is set to Enterprise Integration Manager in this new job.
Go to the Job Parameters applet and enter the following parameter values:
Configuration File: The exact name of the ifb file, for example, Product.ifb, Pricelist.ifb and so on.
Username: The administrator username, for example, SADMIN
Password: The administrator password, for example, SADMIN
Error Flag: 1
SQL Trace Flag: 8
Trace Flag: 1
Ensure that the job finishes with a status of Success.
Ensure that the data appears correctly in the Siebel Business Applications User Interface in relevant Administration screens, for example, Accounts, Contacts, Products, Price List, and so on.
To check whether the Siebel EIM loaded all records, check the IF_ROW_STAT column for the corresponding EIM table.
The IF_ROW_STAT field can have following status values:
IF_ROW_STAT = for import: Job to be run or job is running.
IF_ROW_STAT = Partially_imported: A mandatory field is missing for that record.
IF_ROW_STAT = Imported: Job ran successfully for that record.
IF_ROW_STAT= dup_row_exists: Siebel base table has that record.
To create the cross-references for the Siebel price list IDs for those price lists specified in $AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml, you can run the ODI scenario from ODI_HOME/bin either from ODI Designer or a command line prompt.
Log in to ODI Designer.
Under the project PriceListBulkLoad, run the package LoadSiebelIncPriceListDataToXREFPkg.
For Windows:startscen.bat LOADSIEBELINCPRICELISTDATATOXREF 001 PRICELISTBULKLOAD
For LINUX: ./startscen.sh LOADSIEBELINCPRICELISTDATATOXREF 001 PRICELISTBULKLOAD
After the process finishes, verify that the cross-references for the Siebel Price List IDs were created by selecting row_id from s_pri_lst, where the pricelist names are in the xml file ($AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml) (to be run in Siebel database).
Ensure that the returned IDs are created in the Oracle AIA cross-reference table.
Note:
After running the ODI package to populate the X-REF Database, if needed, you can run Siebel EIM again to nullify the integration_id in Siebel. This step is optional. The format of the IFB file for the second run looks like this:
[IMPORT ALL PRICELISTS] TYPE = SHELL ;INCLUDE = "IMPORT PRICELIST" INCLUDE = "UPDATE INTEGRATION ID"
All the other job parameters remain the same in the ifb file.
Pricing administrators are responsible for:
Specifying the list of price list names in the XML file at $AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml for the incremental load.
Populating cross-references with Oracle Price List ID and Common IDs for the price lists in the XML file at $AIA_HOME/staging/O2C2Home/SetupFiles/PriceListSource.xml by running an ODI package.
Loading the specified price lists, new or updated, into the Siebel interface tables (EIM_PRI_LST) by running an ODI package.
Running the Siebel EBO implementation map (EIM) program to populate the Siebel base tables.
Linking cross-references with the Siebel ID by running an ODI package.
For more information about initial data loads, see Chapter 2, "Loading Initial Data."
Source Application: Oracle E-Business Suite
QP_CRMINTEG_PRICELIST_V
Target Application: Siebel
Table: EIM_PRI_LST
QP_CRMINTEG_PRICELIST_V is the view object to fetch all sales order-related active price list headers and lines.
Populate the cross-reference with the Price List ID and Common ID:
ODI package: LoadEbizIncPriceListDataToSiebelPkg
ODI Scenario: LOADEBIZINCPRICELISTDATATOSIEBEL
ODI Interface: Ebiz_To_Xref_PriceList_INCINTERFACE
Source: CRMINTEG_PRICELIST_V
Target: cross-reference table
Join condition: Select * from QP_CRMINTEG_PRICELIST_V
Load the EIM table:
ODI package: LoadEbizIncPriceListDataToSiebelPkg
ODI Scenario: LOADEBIZINCPRICELISTDATATOSIEBEL
ODI Interface: Ebiz_To_Sebl_PriceList_INCINTERFACE
Source: QP_CRMINTEG_PRICELIST_V
Join condition: Select * from QP_CRMINTEG_PRICELIST_V WHERE (name in price list names mentioned in the xml)
Target: EIM_PRI_LST
See Example 14-1, "Siebel EIM Configuration File".
Example 14-1 Siebel EIM Configuration File
[Siebel Interface Manager] USER NAME = "SADMIN" PASSWORD = "SADMIN" PROCESS = "IMPORT ALL PRICELISTS" [IMPORT ALL PRICELISTS] TYPE = SHELL INCLUDE = "IMPORT PRICELIST" INCLUDE = "UPDATE INTEGRATION ID" [IMPORT PRICELIST] BATCH = 100 TYPE = IMPORT TABLE = EIM_PRI_LST ONLY BASE TABLES = S_PRI_LST,S_PRI_LST_ITEM,S_PRI_LST_BU FIXED COLUMN = PL_ENTERPRISE_FLG,"N" FIXED COLUMN = PLI_OVERRIDEROLLUP,"N" FIXED COLUMN = PLI_PRI_CD,"STANDARD" FIXED COLUMN = VIS_ACTIVE_FLG,"Y" FIXED COLUMN = PL_ACTIVE_FLG,"Y" DEFAULT COLUMN = PL_BU,"Vision Operations" DEFAULT COLUMN = VIS_BU,"Vision Operations" DEFAULT COLUMN = PLI_PROD_BU,"Vision Operations" DEFAULT COLUMN = PL_EFF_START_DT,"2007-01-15" DEFAULT COLUMN = PLI_EFF_START_DT,"2007-01-15" DEFAULT COLUMN = PL_TAXABLE_FLG,"N" DEFAULT COLUMN = PL_SUBTYPE_CD,"PRICE LIST" [UPDATE INTEGRATION ID] SESSION SQL = "UPDATE ORA19111.EIM_PRI_LST SET PL_INTEGRATION_ID = NULL WHERE PL_INTEGRATION_ID IS NOT NULL" BATCH = 100 TYPE = IMPORT TABLE = EIM_PRI_LST ONLY BASE TABLES = S_PRI_LST ONLY BASE COLUMNS = S_PRI_LST.NAME,\ S_PRI_LST.BU_ID,\ S_PRI_LST.SUBTYPE_CD,\ S_PRI_LST.INTEGRATION_ID INSERT ROWS = S_PRI_LST,FALSE UPDATE ROWS = S_PRI_LST,TRUE NET CHANGE = FALSE
After Siebel EIM tables are populated, you run the Siebel server EIM component to populate the Siebel base tables. After the price lists are created in Siebel, you must update the Siebel Price List ID and the Price List Line ID in the X-REF table.
For more information about initial data loads, see Chapter 2, "Loading Initial Data."
S_PRI LST
X-REF table
The S_PRICE LIST table has ROW_ID and INTEGRATION_ID that must be mapped to the cross-reference table.
ODI Package name: LoadSiebelIncPriceListDataToXREFPkg
ODI Scenario Name: LOADSIEBELINCPRICELISTDATATOXREF
ODI interface name: Sebl_To_Xref_PriceList_INCINTERFACE
Source: S_PRI_LST
Target: X-REF Table
Use the bulk load feature to move asset data (related to customer-owned item instances) from Oracle EBS into a Siebel CRM Asset.
Bulk loading of asset data is similar to that of other data types, such as customer and product. The bulk load feature extracts asset data from Oracle EBS, transforms it into the EIM interface table data structures, and moves it into the Siebel EIM interface tables. A Siebel loader program moves the data from the EIM interface tables into the application base tables of Siebel CRM.
The INVENTORY_LOCATION_ID cross-reference table must be populated manually after the installation because no process flow exists for this synchronization.
shows how to use the SQL insert statement to manually add the data into the INVENTORY_LOCATION_ID cross-reference table.
Example 14-2 Adding Data Into the INVENTORY_LOCATION_ID XREF Table
SEBL01_INVLOC_BU:
INSERT INTO XREF_DATA (XREF_TABLE_NAME,XREF_COLUMN_NAME,
ROW_NUMBER, VALUE, IS_DELETED, LAST_MODIFIED) VALUES
('oramds:/apps/AIAMetaData/xref/INVENTORY_LOCATION_ID.xref','SEBL01_INVLOC_BU'
,
'ROWNUM_ORG_1', '88-JKO29', 'N', SYSTIMESTAMP)
Note:
The SEBL01_INVLOC_BU column must be populated with the Business Unit Id value from Siebel.
The Asset bulk data load consists of these steps:
Load Oracle EBS to EIM.
Use ODI to populate the Siebel EIM table with data from the Oracle EBS table. The Oracle AIA cross-reference table is populated with the Oracle EBS and Common ID values.
Transfer EIM data to a Siebel Base table.
Use an EIM job to transfer the data from EIM to the Siebel Base tables.
Move Siebel Base table values to the Oracle AIA cross-reference table.
Use ODI to move the Siebel ID values back into the Oracle AIA cross-reference table (XREF_DATA), correlating to the Oracle EBS and Common ID values that were loaded in step 1.
For initial data loads, you must perform some EIM activities.
For more information about the pre- and post-EIM activities and execution, see the Siebel Enterprise Integration Manager Administration Guide, Siebel EIM Tables.
You can move data from Oracle EBS to Siebel EIM tables using either ODI Designer or a command line prompt.
To move data from Oracle EBS using ODI Designer:
Log in to the Designer.
Expand the project: Oracle Ebiz to Siebel Project.
Expand the folder: Assets Bulk Load.
Expand the package.
Right-click the package Load Ebiz Asset Data to Eim Pkg and select Execute from the menu.
Select Global in the execution box.
To move data from Oracle EBS using a command line prompt (Oracle EBS 12.1.1):
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For WINDOWS: startscen LOAD_EBIZ_ASSET_DATA_PKG_R12 001 GLOBAL
For UNIX: ./startscen.sh LOAD_EBIZ_ASSET_DATA_PKG_R12 001 GLOBAL
To move data from Oracle EBS using a command line prompt (Oracle EBS 11.5.10):
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For WINDOWS: startscen LOAD_EBIZ_ASSET_DATA_TO_EIM_PKG 001 GLOBAL
For UNIX: ./startscen.sh LOAD_EBIZ_ASSET_DATA_TO_EIM_PKG 001 GLOBAL
To move data from EIM to Siebel base tables:
Verify that the Product.ifb, Pricelist.ifb, Customer.ifb, and Asset.ifb files are available in the Admin directory of the installed Siebel server.
For example, in the D:\ses\siebsrvr\Admin directory
Log in to Siebel E-Business Applications using the Administrator login.
For example, proper credentials such as SADMIN/SADMIN
Go to the Site Map icon (the globe that appears at the top left of the home page).
Navigate to the Administration - Server Management screen.
Click the Jobs link and navigate to Server Management, Jobs.
Click New in the Jobs applet.
Ensure that the component is set to Enterprise Integration Manager in this new job.
Go to Job Parameters and enter the following parameter values:
Configuration File: Assets.ifb
Username: The administrator username ex. SADMIN
Password: The administrator password ex. SADMIN
Error Flag: 1
SQL Trace Flag: 8
Trace Flag: 1
Ensure that the job finishes with a status of Success.
The color of the status bar changes to green.
Ensure that the data is displayed properly in the Siebel Business Applications User Interface in relevant Administration screens such as Accounts, Contacts, Products, Price List, and so forth.
To check whether the Siebel EIM loaded all records, check the IF_ROW_STAT column for the corresponding EIM table.
The IF_ROW_STAT field can have the following status values:
IF_ROW_STAT = for import: Job to be run or job is running.
IF_ROW_STAT = Partially_imported: A mandatory field is missing for that record.
IF_ROW_STAT = Imported: Job ran successfully for that record.
IF_ROW_STAT= dup_row_exists: Siebel base table has that record.
You can move data from Siebel base tables to AIA cross-reference tables using either ODI Designer or a command line prompt.
To move data from Siebel base tables using ODI Designer:
Log in to the Designer.
Expand the project: Oracle Ebiz to Siebel Project.
Expand the folder: Assets Bulk Load.
Expand the package.
Right-click the package titled Load Siebel Data to XREF Pkg. Select Execute from the menu.
Select context as the global in the execution box.
To move data from Siebel base tables using a command line prompt:
Go to the bin folder of ODI HOME in the Command window.
Enter the following command:
For Windows: Startscen LOAD_SIEBEL_DATA_TO_XREF_PKG 001 GLOBAL
For UNIX: ./startscen.sh LOAD_SIEBEL_DATA_TO_XREF_PKG 001 GLOBAL
After the process finishes and before moving data from the Siebel EIM table to the Siebel base table, run this sql query in the xref database to count the number of records loaded upon running the first ODI scenario of Asset Initial load:
Select count(*),Xref_column_name from xref_data
Where xref_table_name='INSTALLEDPRODUCT_ID' group by xref_column_name. The count of 'COMMON' and 'EBIZ_01' should be the same.
Run this query in the Siebel database (after running the second ODI scenario) to count the number of records in the EIM table of the Siebel:
Select count(*),Xref_column_name from xref_data
Where xref_table_name='INSTALLEDPRODUCT_ID' group by xref_column_name. This option gives the count of E-Business records loaded into Siebel, along with their Common IDs count. The count should match.
Select xref_column_name, count(*) from xref_data, where xref_table_name = 'INSTALLED_PRODUCT_ID' GROUP BY xref_column_name (to be run in FMW database).
If the load failed before the data is loaded into the Siebel base tables (through EIM jobs), you can go directly and delete the data from the corresponding EIM tables and then delete the data from the cross-reference for that load and rerun the load.
If the process fails after you run the EIM job, you must clear the data from the Siebel database tables and then continue with step 1.
The IF_ROW_STAT column can have these statuses: failed, 'for import', duplicate, partially imported
Initially the data is loaded into the EIM tables with IF_ROW_STAT set as 'for import' when you run the EIM job. Then, based on whether that record is loaded into the Siebel database perfectly, the status changes. Duplicate status means that the data is present in the Siebel database so that the record is not loaded into the Siebel base tables. Failed status means that the import of the record was unsuccessful. Partially failed status means that there is some data inconsistency in the EIM table.
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|