Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle Order Management and Fulfillment Analytics Option >

Adding Dates to the Order Cycle Time Table for Post-Load Processing

It is recommended that you do not change the structure of any of the data warehouse tables, with the exception of the Order Cycle Time table—W_SALES_CYCLE_LINE_F_HOLD_TMP. In particular, the Order Cycle Time table may not store every sales-related date. As a result, you want to create a new date field in this table to accommodate your additional dates.

To add more dates, you need to understand how the Order Cycle Times table is populated. Thus, if you want to change the dates loaded into the Order Cycle Time table (W_SALES_CYCLE_LINE_F_HOLD_TMP), then you have to modify the M_PLP_SALES_CYCHDR_LOAD mappings that take the dates from the W_* tables and load them into the Cycle Time table.

NOTE:  Be sure that the date is already being extracted and stored in IA and OD data warehouse tables.

To add dates to the Cycle Time table load

1. In the PowerCenter Designer, open the Configuration for Post Load Processing folder.
2. In Warehouse Designer, modify the table definition for the target table to verify that it has a field to store this date.

   For example, if you are loading the Validated on Date in the W_SALES_CYCLE_LINE_F_HOLD_TMP table, then you need to create a new column, VALIDATED_ON_DT, and modify the target definition of the W_SALES_CYCLE_LINE_F_HOLD_TMP table.

3. In Source Analyzer, modify the table definition of the source table to include this new column.

   Continuing with the example, you would include the VALIDATED_ON_DT column in the W_SALES_CYCLE_LINE_F_HOLD_TMP source table.

4. Create the table in the database with the new table structure.

   TIP:   If you have already loaded data in the W_SALES_CYCLE_LINE_F_HOLD_TMP table, then make sure that you backup the data before you recreate this table.

5. Modify the M_PLP_SALES_CYCLNS_INCR_LOAD mapping to select the new column from any of the following source tables, and load it to the W_SALES_CYCLE_LINE_F_HOLD_TMP target table:
   • W_SALES_ORDER_LINE_F
   • W_SALES_INVOICE_LINE_F
   • W_SALES_PICK_LINE_F
   • W_SALES_SCHEDULE_LINE_F
6. Modify The Source Qualifier SQL Override for the mapping, and map the column in the Transformation to map it to the target table.