Oracle Price Analytics sources data from Quoting, Order Management and Advanced Pricing modules by default which are available for E-Business Suite. Additionally, a flat file option has been provided to supplement Dimension attributes and additional Order (or) Quote line prices (for example, Guideline Invoice Price) which are not available in vanilla implementations of above modules.
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
The ETL process loads the non-E-Business Suite data from flat files and data from E-Business Suite Applications database tables into staging tables; then loads data from the staging tables into Oracle Business Analytics Warehouse.
| Flat File | Description | Supplements Target |
|---|---|---|
|
file_pri_strategy_ds |
This file holds data for additional attributes to supplement EBS Sales Channel Codes. |
W_PRI_STRATEGY_D Price Strategy-A grouping of pricing rules that define the approach for achieving a specific goal around selling and pricing and products, targeted at a Pricing Segment, sub-segment and specific selling situation. |
|
file_pri_segment_ds |
This file holds data for additional attributes to supplement EBS Customer Class Codes. |
W_PRI_SEGMENT_D Price Segment - A collection or grouping of customers that exhibit a common set of characteristics and buying behaviors in relation to a vendor of products or services. The Pricing Segment is usually a further refinement of the Market Segment in order to allow the pricing strategist to categorize and understand sets of customers who will respond to common pricing tactics. |
|
file_pwf_element_ds |
This file holds data for additional attributes to supplement EBS Modifier Lines. |
W_PWF_ELEMENT_D Price Waterfall Element - PWF elements are the various components that make up a Price Waterfall and include both: (a) Summed up price/revenues such as ceiling and segment revenues and (b) the various adjustments, which may be either a unitized adjustment or actual dollar adjustment for a discounting program, incentive, expense or cost summed up price/revenue. |
|
file_quoteitem_fs |
This file holds the Approved, Requested and Guideline Invoice/Pocket Price and Margins corresponding to transaction data for Quote Item. |
W_QUOTEITEM_F Quote Item–Stores the various quote line revenue amounts and adjustments. The grain of this table is the Quote Line. |
|
file_orderitem_fs |
This file holds the Approved, Requested and Guideline Invoice/Pocket Price and Margins corresponding to transaction data for Order Item. |
W_ORDERITEM_F Order Item – Stores the various order line revenue amounts and adjustments. The grain of this table is: Order Line. |
Configuring Domains via Flat Files
Source Domain member values for those Code Dimension attributes which are supplemented using these files can be populated in the Warehouse via a domains file that is, file_domain_member_gs.csv.
File Specifications
These files are used across all adaptors and hence only a few columns need to be populated which are supported for the E-Business Suite 12.1.3 Adaptor – the other columns should be populated with NULL.
The columns supported for a file are listed under File Structure in subsequent sections.
These files should exist in E-Business Suite 12.1.3 Source Files Folder even if not being used to supplement data. If not, Extract tasks will fail.
The data in the source files should conform to the following specifications:
Data should be in CSV files (*.csv).
For Full Load 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.
All columns in the files should follow E-Business Suite application data model terms and standards, and all ID columns in the files are expected to have corresponding E-Business Suite IDs.
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 unique 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.
Amount (or) Price column values should be of the same Document Currency Code used in the OLTP transaction.
Column INTEGRATION_ID in all flat files cannot be NULL as it will serve as either the (i) lookup key when supplementing OLTP data or (ii) primary key in case the file serves as the Primary source.