In Fusion Applications, there are several entities that are sourced from non-Fusion Applications systems. Fusion Applications CRM is leveraging OBIA (Oracle Business Intelligence Applications) to integrate data from Fusion Applications and non-Fusion Applications source systems.
The Oracle BI Applications metadata layer consolidates disparate physical data sources and makes it ready for analysis by Fusion Applications users. Sales Prospector (SPE) is a brand new Fusion application for sales users helping them to manage their pipeline and whitespace effectively. SPE expects Order Item and Service Request data to be supplied from non-Fusion applications.
The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:
Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.
Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.
Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
ETL from Flat Files
Non-Fusion Applications data such as Order Item and Service Request can be directly loaded into Oracle Business Analytics Warehouse as long as the data can be presented in the specified flat file format. The ETL process loads the non-Fusion Applications data from the flat files and Fusion Applications data from Fusion Applications database tables into staging tables; then loads data from the staging tables into Oracle Business Analytics Warehouse.
SPE ETL Preparation
SPE needs non-Fusion Applications data for Order Item Fact, Service Request Fact and Service Request Dimension. The data should be presented in flat files according to the following specifications:
Data should be in CSV files (*.csv).
For full ETL, the files should contain all initial records that are supposed to be loaded into Oracle Business Analytics Warehouse; for incremental ETL, the files should contain only new or updated records.
The files are specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only. All columns in the files should follow Fusion application data model terms and standards, and all ID columns in the files are expected to have corresponding Fusion Integration ID.
Data should start from line six of each file. The first five lines of each file will be skipped during ETL process.
Each row represents one record in staging table.
All date values should be in the format of YYYYMMDDHH24MISS. For example, 20071231140300 should be used for December 31, 2007, 2:03 pm.
Columns DATASOURCE_NUM_ID and INTEGRATION_ID in all flat files cannot be NULL.
Column DATASOURCE_NUM_ID needs to be fixed to 200, which is also the Fusion Applications data source number.
The Flat files for Order Item Fact, Service Request Fact and Service Request Dimension are:
file_orderitem_fs.csv — for more information about the structure of this file, see Flat file file_orderitem_fs.csv
file_srvreq_fs.csv — for more information about the structure of this file, see Flat file file_srvreq_fs.csv.
file_srvreq_ds.csv — for more information about the structure of this file, see Flat file file_srvreq_ds.csv.
Before starting the ETL run, the flat files should be prepared based on the formats provided in sections below.
The generic file does not support any source order system-specific features, such as recurring order lines and etc. Each line in this file will contribute to the total order amount. The granularity of this file is each order line. The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
| Column Name | Data Type | Sample Data | Description |
|---|---|---|---|
|
CUSTOMER_ID |
VARCHAR(80) |
999997551042159 |
Customer Party Id. There could be more than one customer IDs in an order. Among the possible customer IDs of bill to, ship to, invoice to and so on; this is the primary ID for BI analysis use. Foreign key to HZ_PARTIES.PARTY_ID. |
|
CURCY_CD |
VARCHAR(20) |
USD |
Currency Code, the currency that the order line amounts are based on. |
|
CRM_CURR_EXCHANGE_RATE |
NUMBER(28,10) |
1.00 |
CRM Currency Exchange Rate, which is for the conversion of the order line amounts to the CRM common currency. |
|
CRM_CORP_CURR_CODE |
VARCHAR(20) |
USD |
CRM Common Currency Code. |
|
ORDER_ID |
VARCHAR(80) |
4171787 |
Order header ID. |
|
PROD_ID |
VARCHAR(80) |
999997500678718 |
Product Inventory Item ID. Foreign key to EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID. |
|
PROD_GROUP_ID |
VARCHAR(80) |
Null |
Product Group ID. Optional for SPE ETL use. Leave null. |
|
RESOURCE_ID |
VARCHAR(80) |
123445623 |
Resource ID, order owner Resource ID for order. Foreign key to HZ_PARTIES.PARTY_ID |
|
RESOURCE_ORG_ID |
VARCHAR(80) |
3453453453 |
Resource Organization ID, order owner's organization ID. Foreign key to HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID. |
|
SOURCE_ID |
VARCHAR(80) |
100000016742344 |
Marketing campaign source code defined in MKT_SC_SOURCE_CODES. |
|
ORDER_DT |
DATE |
20061220000000 |
Order Date in the format of YYYYMMDDHH24MISS. It is the date when order is placed. This date is used in ETL as canonical date for resolving dimensional FKs. |
|
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number ID. Need to be fixed to 200, which is the same value for Fusion Applications data source in ETL. |
|
INTEGRATION_ID |
VARCHAR(80) |
12149813 |
Integration ID, the order Line ID. Typically, each order has one order header and multiple order lines. |
|
DISCNT_AMT |
NUMBER(28,10) |
2.33 |
Discount Amount, deduction made to the unit price. |
|
NET_PRI |
NUMBER(28,10) |
45.752 |
Net Price of order line item. This is the final price after deducting discount amount. |
|
QTY_REQ |
NUMBER(28,10) |
12 |
Quantity Ordered for the line item. |
|
PR_TERR_ID |
VARCHAR(80) |
1000000000023112 |
Primary Territory ID, ID of primary sales territory where order is placed. Territory ID is defined in MOT_TERRITORIES. |
|
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of the user who created the row. |
|
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of YYYYMMDDHH24MISS. |
|
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
|
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of YYYYMMDDHH24MISS. |
|
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
|
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |
The columns listed below are required for SPE ETL use. The granularity of this file is each Service Request. The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
| Column Name | Data Type | Sample Data | Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number ID. Data Source Number ID needs to be fixed to 200, the same value for Fusion Applications data source in ETL. |
|
INTEGRATION_ID |
VARCHAR(80) |
12149813 |
Integration ID, unique IDentifier ID for each Service Request. |
|
CLOSE_DT |
DATE |
20030616174947 |
Closed Date, date in the format of |
|
OPEN_DT |
DATE |
20020516174947 |
Open Date, date in the format of |
|
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
|
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of user who created the row. |
|
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of |
|
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
|
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of |
|
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |
|
CUSTOMER_ID |
VARCHAR(80) |
999997551042159 |
Customer Party Id. Foreign key to HZ_PARTIES.PARTY_ID. |
|
PROD_ID |
VARCHAR(80) |
999997500678718 |
Product Inventory Item ID. Foreign key to EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID. |
The columns listed below are required for SPE ETL use. The granularity of this file is each Service Request. The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
| Column Name | Data Type | Sample Data | Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number Id. Data Source Number Id needs to be fixed to 200, the same value for Fusion Applications data source in ETL. |
|
INTEGRATION_ID |
VARCHAR(80) |
1-10E-5 |
Integration ID, unique Identifier ID for each Service Request. |
|
CLOSE_DT |
DATE |
20020516174947 |
Closed Date, date in the format of |
|
OPEN_DT |
DATE |
20020516174947 |
Open Date, date in the format of |
|
SEV_CD |
VARCHAR(80) |
SR_SEVERITY~3-Medium |
Severity Code of the Service Request. Possible values are: SR_SEVERITY~1-Critical, SR_SEVERITY~2-High, SR_SEVERITY~3-Medium, SR_SEVERITY~4-Low. |
|
STATUS |
VARCHAR(80) |
SR_STATUS~Open |
Service Request Status. Possible values are: SR_STATUS~Approved, SR_STATUS~Cancelled, SR_STATUS~Closed, SR_STATUS~Completed, SR_STATUS~Open, SR_STATUS~Pending. |
|
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
|
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of the user who created the row. |
|
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of |
|
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
|
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of |
|
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |