| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter describes how to configure Order Management and Fulfillment Analytics for particular sources to meet your business needs.
It contains the following topics:
The Oracle Order Management and Fulfillment Analytics application allows you to analyze the movement of sales orders through different stages of the sales cycle. This analysis includes insight into which items are booked, backlogged, and invoiced. It also provides you with information that allows you to evaluate the sales performance of individual sales representatives or departments. The Oracle Order Management and Fulfillment Analytics application contains the functional area, Orders and Revenue.
The Oracle Order Management and Fulfillment Analytics application consists of orders, invoices and backlogs. Sales orders are the entry point for the sales process. Invoices are the exit point from the fulfillment process. Backlogs are points of congestion in your fulfillment process.
In the Oracle Order Management and Fulfillment Analytics application, two main types of backlog exist:
Operational
Financial
The scheduled, unscheduled, delinquent, and blocked backlogs belong to the Operational backlog. The following sources can populate Orders and Revenue:
Oracle 11i
Oracle R12
Universal source
The Oracle Order Management and Fulfillment Analytics application also requires post-load processing mappings to populate its tables.
This section contains configuration steps that you need to perform on Oracle Order Management and Fulfillment Analytics before you do a full data load, and contains the following topics:
This section contains configuration steps that apply to all source systems.
|
Note: For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, Oracle Sales Analytics, see Chapter 8, "Configuring Common Areas and Dimensions"). |
This section contains configuration steps required before you do a full data load that apply to Oracle EBS.
Section 11.2.2.2, "How to Configure Invoice Type Domain Values".
Section 11.2.2.3, "How to Configure Pick Types Domain Values".
Section 11.2.2.4, "How to Configure Order Types Domain Values"
Section 11.2.2.5, "How to Configure Pick Status Domain Values"
Section 11.2.2.6, "How to Configure Invoice Status Domain Values"
Section 11.2.2.7, "How to Configure Order Overall Status Domain Values"
Section 11.2.2.8, "How to Configure Pay Method Domain Values"
The table below lists the CSV worksheet files and the domain values for Oracle Order Management and Fulfillment Analytics in the $pmserver\LkpFiles folder.
Table 11-1 Domain Values and CSV Worksheet Files for Oracle Order Management and Fulfillment Analytics
| Worksheet File Name | Description | Session |
|---|---|---|
|
domainValues_InvoiceTypes_ora11i.csv |
Lists the Invoice Document Type column and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.2, "How to Configure Invoice Type Domain Values". |
SDE_ORA_TransactionTypeDimension_SalesInvoiceLines |
|
domainValues_PickTypes_ora11i.csv |
Lists the Picking Document Type column and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.3, "How to Configure Pick Types Domain Values". |
SDE_ORA_TransactionTypeDimension_SalesPickLines |
|
domainValues_OrderTypes_ora11i.csv |
Lists the Order Document Type column and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.4, "How to Configure Order Types Domain Values". |
SDE_ORA_TransactionTypeDimension_SalesOrderLines |
|
domainValues_PickStatus_ora11i.csv |
Lists the Picking Status Code and the Status Desc columns, and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.5, "How to Configure Pick Status Domain Values". |
SDE_ORA_StatusDimension_SalesPickLines |
|
domainValues_PayMethodCode_ora11i.csv |
Lists the method code column and the corresponding domain value for the application. |
SDE_ORA_PaymentMethodDimension |
|
domainValues_InvoiceStatus_ora11i.csv |
Lists the Invoice Status Code and the Status Desc columns, and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.6, "How to Configure Invoice Status Domain Values". |
SDE_ORA_StatusDimension_SalesInvoiceLine |
|
DomainValue_OrderOverallStatus_ora11i.csv |
List the Order Status Code column and the corresponding domain values for the Oracle 11i or Oracle R12 application. For more information about updating the values in this file, see Section 11.2.2.7, "How to Configure Order Overall Status Domain Values". |
SDE_ORA_StatusDimension_SalesOrderLineCycle |
For general information about domain values with CSV worksheet files, see Section 6.12, "About Domain Values" and Section 6.13, "Configuring the Domain Value Set with CSV Worksheet Files".
This section explains how to configure Invoice Type Domain Values using the domainValues_InvoiceTypes_ora11i.csv file.
To configure Invoice Type Domain Values:
Identify the Invoice Types in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT RA_CUST_TRX_TYPES_ALL.TYPE
FROM RA_CUST_TRX_TYPES_ALL
ORDER BY 1;
Open the domainValues_InvoiceType_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the TYPE column to the XACT_TYPE_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Transaction Type Code to one domain value.
For more information on Transaction Type Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Pick Types Domain Values using the domainValues_PickTypes_ora11i.csv file.
To configure Pick Types Domain Values:
Identify the Pick Types in your Oracle 11i source system.
Open the domainValues_PickType_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Put 'STANDARD' in the XACT_TYPE_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Transaction Type Code to one domain value.
For more information on Transaction Type Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Order Types Domain Values using the domainValues_OrderTypes_ora11i.csv file.
To configure Order Types Domain Values:
Identify the Pick Types in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE FND_LOOKUP_VALUES.VIEW_APPLICATION_ID = 660
AND FND_LOOKUP_VALUES.LANGUAGE = 'US'
AND FND_LOOKUP_VALUES.LOOKUP_TYPE = 'LINE_CATEGORY'
ORDER BY 1;
Open the domainValues_OrderType_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the LOOKUP_TYPE column to the XACT_TYPE_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Transaction Type Code to one domain value.
For more information on Transaction Type Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Pick Status Domain Values using the domainValues_PickStatus_ora11i.csv file.
To configure Pick Status Domain Values:
Identify the Pick Statuses in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE= 'PICK_STATUS'
AND FND_LOOKUP_VALUES.LANGUAGE = 'US'
AND FND_LOOKUP_VALUES.VIEW_APPLICATION_ID = 665
AND FND_LOOKUP_VALUES.SECURITY_GROUP_ID = 0
ORDER BY 1;
Open the domainValues_PickStatus_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the LOOKUP_CODE column to the STATUS_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Status Code to one domain value.
For more information on Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Invoice Status Domain Values using the domainValues_InvoiceStatus_ora11i.csv file.
To configure Invoice Status Domain Values:
Identify the Invoice Statuses in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE= 'INVOICE_TRX_STATUS' AND
FND_LOOKUP_VALUES.LANGUAGE = 'US'
AND FND_LOOKUP_VALUES.VIEW_APPLICATION_ID = 222
AND FND_LOOKUP_VALUES.SECURITY_GROUP_ID = 0
ORDER BY 1;
Open the domainValues_InvoiceStatus_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the LOOKUP_CODE column to the STATUS_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Status Code to one domain value.
For more information on Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Order Overall Status Domain Values using the domainValues_OrderOverallStatus_ora11i.csv file.
To configure Order Overall Status Domain Values:
Identify the Order Overall Statuses in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND FND_LOOKUP_VALUES.LANGUAGE = 'US'
AND FND_LOOKUP_VALUES.VIEW_APPLICATION_ID = 660
AND FND_LOOKUP_VALUES.SECURITY_GROUP_ID = 0
ORDER BY 1;
Open the domainValues_OrderOverallStatus_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the LOOKUP_CODE column to the STATUS_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Status Code to one domain value.
For more information on Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure Pay Method Status Domain Values using the domainValues_PayMethodCode_ora11i.csv file.
To configure Pay Method Domain Values:
Identify the Pay Methods in your Oracle 11i source system by using the following SQL:
SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PAYMENT TYPE'
AND VIEW_APPLICATION_ID = 660
AND LANGUAGE = 'US'
AND FND_LOOKUP_VALUES.SECURITY_GROUP_ID = 0
ORDER BY 1;
Open the domainValues_PayMethodCode_ora11i.csv file using a text editor in the $pmserver\lkpfiles folder.
Copy the LOOKUP_CODE column to the METHOD_CODE column in the file.
The data must be copied starting from the 2nd line. The first line is the column header.
Map each Method Code to one domain value.
For more information on Method Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section contains configuration steps required before you do a full data load that apply to PeopleSoft.
Not applicable to Oracle BI Applications Version 7.9.4.
This section contains configuration steps required before you do a full data load that apply to Universal.
Not applicable to Oracle BI Applications Version 7.9.4.
This section contains additional configuration steps for Oracle Order Management and Fulfillment Analytics, and contains the following topics:
This section contains configuration steps that apply to all source systems.
When you modify the default VAR_BOOKING_ID column, the SQL statement is configured as follows for Oracle 11i and Oracle R12:
TO_CHAR(INP_LINE_ID)||'~'||TO_CHAR(INP_INV_ITEM_ID)||'~'||to_char(INP_WAREHOUSE_ID)
However, if you want to track changes based on more than one attribute, in the SQL statement you must concatenate the attribute column IDs in the VAR_BOOKING_ID column. For example, if you want to track changes in Salespersons and Customer, then concatenate the technical name IDs in the VAR_BOOKING_ID column as follows:
TO_CHAR(INP_LINE_ID)||'~'||TO_CHAR(INP_INV_ITEM_ID)||'~'||TO_CHAR(INP_WAREHOUSE_ID)||'~'||TO_CHAR(INP_SALESREP_ID)||'~'||TO_CHAR(INP_CUSTOMER_ID)
To track dimensional attribute changes in bookings
In PowerCenter Designer, open theSDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_SA_ORA_SalesOrderLinesFact mapplet.
Double-click the EXP_SALES_ORDLNS Expression transformation to open the Edit Transformation box.
In the Ports tab, edit the expression for the VAR_BOOKING_ID port, and enter the ID of the attribute for which you want to track changes.
If you want to track changes in multiple attributes, concatenate the IDs of all attributes and put the concatenated value in the VAR_BOOKING_ID column.
Validate and save your changes to the repository.
This section contains Oracle Order Management and Fulfillment Analytics configuration points for aggregating the Sales Invoice Lines and Sales Order Lines tables.
The aggregation processes use the following Teradata parameters:
Hint_Tera_Pre_Cast
Hit_Tera_Post_Cast
To aggregate the Sales Invoice Lines and Sales Order Lines tables, perform the following tasks:
Configure the Sales Invoice Lines Aggregate Table
Configure the Sales Order Lines Aggregate Table
About Configuring the Sales Invoice Lines Aggregate Table
The Sales Invoice Lines aggregate table (W_SALES_INVOICE_LINE_F_A) is used to capture information about the invoices issued for your sales orders. You need to configure the Sales Invoice Lines aggregate table in order to run initial ETL and incremental ETL.
For your initial ETL run, you need to configure the TIME_GRAIN parameter for the time aggregation level in the Sales Invoice Lines aggregate fact table.
For the incremental ETL run, you need to configure the time aggregation level.
To aggregate the Sales Invoice Lines table for your incremental run, you need to configure the TIME_GRAIN parameter.
The TIME_GRAIN parameter has a preconfigured value of Month. The possible values for the TIME_GRAIN parameter are:
'DAY'
'WEEK'
'MONTH'
'QUARTER'
'YEAR'
The aggregation processes use the following Teradata parameters:
Hint_Tera_Pre_Cast
Hit_Tera_Post_Cast
The Sales Invoice Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Thus, the Sales Invoice aggregate table is not fully reloaded from the base table after each incremental ETL run. Oracle Business Analytics Warehouse minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. This process is described below.
Oracle Business Analytics Warehouse finds the records to be deleted in the base table since the last ETL run, and loads them into the W_SALES_INVOICE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_SalesInvoiceLinesAggregate_Derive_PreSoftDeleteImage, which is run before SIL_SalesInvoiceLinesFact_SoftDelete deletes the records from the base table.
Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the W_SALES_INVOICE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_SalesInvoiceLinesFact_Derive_PreLoadImage, which is run before SIL_SalesInvoiceFact deletes the records from the base table.
Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_SALES_INVOICE_LINE_TMP table, without changing their sign. The mapping responsible for this task is SIL_SalesInvoiceLinesFact_Derive_PreLoadImage, which is run before PLP_SalesInvoiceLinesFact_Derive_PostLoadImage updates or inserts records in the base table.
Oracle Business Analytics Warehouse aggregates the W_SALES_INVOICE_LINE_TMP table and load to W_SALES_INVOICE_LINE_A_TMP, which has the same granularity as the W_SALES_INVOICE_LINE_A table.
The PLP_SalesInvoiceLinesAggregate_Derive mapping looks up the W_SALES_INVOICE_LINE_A aggregate table to update existing buckets or insert new buckets in the aggregate table (the mapping is PLP_SalesInvoiceLinesAggregate_Load).
How to Configure the Sales Invoice Lines Aggregate Table
To load the Sales Invoice Lines aggregate table (W_SALES_INVOICE_LINE_A), you need to configure the parameterfileDW.txt file, and run the initial and then the incremental workflows.
To configure the parameterfileDW.txt parameters file
Open the parameterfileDW.txt file in a text editor.
This file is located in the OracleBI\DAC\Informatica\parameters\input folder.
Replace the default values with your new values.
Save and close the file.
About Configuring the Sales Order Lines Aggregate Table
The Sales Order Lines aggregate table (W_SALES_ORDER_LINE_A) is used to capture information about the order lines issued for your sales orders. You need to configure the Sales Order Lines aggregate table in order to run initial ETL and incremental ETL.
For your initial ETL run, you need to configure the TIME_GRAIN parameter for the time aggregation level in the Sales Order Lines aggregate fact table.
For the incremental ETL run, you need to configure the time aggregation level.
To aggregate the Sales Invoice Lines table for your incremental run, you need to configure the TIME_GRAIN parameter.
The TIME_GRAIN parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:
'DAY'
'WEEK'
'MONTH'
'QUARTER'
'YEAR'
The aggregation processes use the following Teradata parameters:
Hint_Tera_Pre_Cast
Hit_Tera_Post_Cast
The Sales Order Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Thus, the Sales Order aggregate table is not fully reloaded from the base table after each incremental ETL run. Oracle Business Analytics Warehouse minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. This process is described below.
Oracle Business Analytics Warehouse finds the records to be deleted in the base table since the last ETL run, and loads them into the W_SALES_ORDER_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_SalesOrderLinesAggregate_Derive_PreSoftDeleteImage, which is run before SIL_SalesOrderLinesFact_SoftDelete deletes the records from the base table.
Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the W_SALES_ORDER_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_SalesOrderLinesFact_Derive_PreLoadImage, which is run before SIL_SalesOrderFact updates the records from the base table.
Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_SALES_ORDER_LINE_TMP table, without changing their sign. The mapping responsible for this task is SIL_SalesOrderLinesFact_Derive_PreLoadImage, which is run before PLP_SalesOrderLinesFact_Derive_PostLoadImage updates or inserts records in the base table.
Oracle Business Analytics Warehouse uses the PLP_SalesOrderLinesAggregate_Derive mapping to aggregate the W_SALES_ORDER_LINE_TMP table and load to W_SALES_ORDER_LINE_A_TMP, which has the same granularity as the W_SALES_ORDER_LINE_A table.
W_SALES_ORDER_LINE_A_TMP looks up the W_SALES_ORDER_LINE_A aggregate table to update existing buckets or insert new buckets in the aggregate table (the mapping is PLP_SalesOrderLinesAggregate_Load).
How to Configure the Sales Order Lines Aggregate Table
To load the Sales Order Lines aggregate table (W_SALES_ORDER_LINE_A), you need to configure the post-load-processing parameter file and the source system parameter files, and run the initial and then the incremental workflows.
To configure the Sales Order Lines Aggregate Table
Open the parameterfileDW.txt file in a text editor.
This file is located in the OracleBI\DAC\Informatica\parameters\input folder.
Replace the default values with your new values.
Save and close the file.
The Sales Order Lines table contains two columns, ORDHD_KEY_ID and ORDLN_KEY_ID, that track individual products when they are grouped and sold as a single package. These two columns allow you to analyze the relationship of all products sold as a single unit. The ORDHD_KEY_ID column stores the Order ID of the entire sales order. The ORDLN_KEY_ID column stores the Line Item ID of the parent product.
For example, assume a customer purchases a package that includes a computer, scanner, and printer. In addition, the customer purchases a monitor separately. In this case, there are two parent items: the package and the monitor. The computer, scanner, and printer are all child orders of the parent order package, while the parent order monitor is a single-item purchase.
Your data warehouse may store this sales information in the Sales Order Lines table as seen in Table 11-2. The ORDLN_KEY_ID field contains the Line Item ID of the parent product in order to maintain the relationship between the parent and child products in a package. In this example, the ORDLN_KEY_ID field is Line_1 for each of the three child products (A1, A2, A3) that were sold as a part of the parent package, Parent A.
Table 11-2 Sales Order Table Columns With Parent/Child Relationships
| Key_ID | SALES_ORDER_NUM | PRODUCT_ID | ORDHD_ KEY_ID | ORDLN_ KEY _ID | Relationship (Not a column in the table.) |
|---|---|---|---|---|---|
|
Line_1 |
1000 |
Package |
1000 |
Line_1 |
Parent A |
|
Line_2 |
1000 |
Computer |
1000 |
Line_1 |
Child A1 |
|
Line_3 |
1000 |
Scanner |
1000 |
Line_1 |
Child A2 |
|
Line_4 |
1000 |
Printer |
1000 |
Line_1 |
Child A3 |
|
Line_5 |
1000 |
Monitor |
1000 |
Line_5 |
Parent B (no children) |
In contrast, if each of the four items described in Table 11-2 were bought individually, the ORDLN_KEY_ID would have a different Line Item ID for every row. In this case, the Sales Order Lines table would look like Table 11-3.
Table 11-3 Sales Order Table Columns Without Parent/Child Relationships
| Key_ID | SALES_ORDER_NUM | PRODUCT_ID | ORDHD_ KEY_ID | ORDLN_ KEY _ID | Relationship (Not a column in the table.) |
|---|---|---|---|---|---|
|
Line_1 |
1000 |
Computer |
1000 |
Line_1 |
None |
|
Line_2 |
1000 |
Scanner |
1000 |
Line_2 |
None |
|
Line_3 |
1000 |
Printer |
1000 |
Line_3 |
None |
|
Line_4 |
1000 |
Monitor |
1000 |
Line_4 |
None |
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), then you have to modify the PLP_SalesCycleLinesFact_Load and PLP_SalesCycleLinesFact_Load_Full mappings that take the dates from the W_* tables and load them into the Cycle Time table.
To add dates to the Cycle Time table load
In the PowerCenter Designer, open the Configuration for Post Load Processing folder.
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 table, then you need to create a new column, VALIDATED_ON_DT, and modify the target definition of the W_SALES_CYCLE_LINE_F table.
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 source table.
Modify the PLP_SalesCycleLinesFact_Load and PLP_SalesCycleLinesFact_Load_Full mappings to select the new column from any of the following source tables, and load it to the W_SALES_CYCLE_LINE_F target table:
W_SALES_ORDER_LINE_F
W_SALES_INVOICE_LINE_F
W_SALES_PICK_LINE_F
W_SALES_SCHEDULE_LINE_F
Modify The Source Qualifier SQL Override for the mapping, and map the column in the Transformation to map it to the target table.
The Backlog table (W_SALES_BACKLOG_LINE_F) stores backlog data for the current month. In contrast, the Backlog History table (W_SALES_BACKLOG_LINE_F) stores snapshots of all previous months' historical backlog data. The periods for which the Backlog History table tracks backlog data is defined by the Backlog Period Date. By default, the date is set as the last calendar day of the month; however you may configure this date. You may want to view backlog history at a more detailed level, such as by day or by week, instead of by month. The following example describes how historical backlog data is stored and what the implications are for changing the backlog time period.
Assume you represent a manufacturing company where financial backlog is defined as any item that is ordered, but not invoiced. On February 1, 2001, you received an order (Sales Order #1) for 30 products. 20 were shipped and invoiced and 10 were shipped, but not invoiced. At the end of the day, there is an entry in the Backlog table and in the Backlog History table. The entry in the Backlog History table looks like that shown in Table 11-4.
Table 11-4 Oracle 11i and Oracle R12: Backlog History Table Entry as of February 1, 2001
| SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
|---|---|---|---|
|
1 |
02/01/2001 |
02/28/2001 |
10 |
On February 2, 5 of the 10 financial backlog items are invoiced and, thus, removed from the backlog. Thus, there is an update to the existing row in the Backlog History table, as shown in Table 11-5.
Table 11-5 Oracle 11i and Oracle R12: Backlog History Table Entry as of February 2, 2001
| SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
|---|---|---|---|
|
1 |
02/01/2001 |
02/28/2001 |
Old value: 10 New value: 5 |
No further activity happens until March 1st. On March 1st, the remaining 5 items on financial backlog are invoiced and removed from financial backlog. In addition, a new sales order (Sales Order #2) comes in for 50 new items. All of the items are put on financial backlog.
Even though all items from Sales Order #1 are cleared from financial backlog, the last backlog row remains in the Backlog History table. The purpose in retaining the last row is to indicate that there was backlog for this particular order. The quantity, in this case 5 items, does not tell you how many items were initially on backlog, which was 10.
For the 50 new financial backlog items, there is a new entry into the Backlog History table. So, as of February 28, 2001, the Backlog History table looks like the Table 11-6.
Table 11-6 Oracle 11i: Backlog History Table Entry as of February 28, 2001
| SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
|---|---|---|---|
|
1 |
Old value: 02/01/2001 New value: 02/02/2001 |
02/28/2001 |
Old value: 10 New value: 5 |
On March 1, 30 more items are ordered (Sales Order #3), all of which are on financial backlog. The resulting Backlog History table looks like Table 11-7.
Table 11-7 Oracle 11i and Oracle R12: Backlog History Table Entry as of March 1, 2001
| SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
|---|---|---|---|
|
1 |
Old value: 02/01/2001 New value: 02/02/2001 |
02/28/2001 |
5 |
|
2 |
03/01/2001 |
03/31/2001 |
50 |
|
3 |
03/01/2001 |
03/31/2001 |
30 |
Because backlog history is maintained at the monthly level, you have a partial history of your backlogs. Based on the latest state of the Backlog History table shown in Table 11-7, you can see that sales order number 1 ended up with 5 financial backlogged items. You do not have visibility into what the initial financial backlogged item quantities were for the sales orders; you only have their ending quantities.
If you decide that you want to track more details on how the items moved out of backlog, then you'll have to maintain the history at a more granular level. For instance, if you want to know the number of items that were on backlog when the it was first opened, you would have to track the backlog history by day, instead of by month.
For example, if you maintained backlog history at the daily level you would be able to capture that sales order 1 had an initial backlog of 10 as of February 1 and the backlog quantity shrank to 5 as of February 2. So, by capturing history at the daily level, you could then compute cycle times on how long it took to move items out of backlog. However, if you decide to capture backlog history at a more detailed level, you may compromise performance because tracking backlog history at the daily level can increase the size of the Backlog History table exponentially.
If you choose to change the time period for which historical backlog data is kept, you must verify that all types of backlog are being stored at the same grain; which requires modification to multiple mappings. Table 11-8 provides a list of all applicable mappings and their corresponding Expression transformations that you must modify.
Table 11-8 Oracle 11i and Oracle R12: Backlog History Applicable Mappings and Expression Transformations
| Mapping | Expression Transformation |
|---|---|
|
|
|
|
|
|
The backlog history period is monthly by default. The default SQL statement in the Expression transformation for the port BACKLOG_PERIOD_DK is:
TO_DECIMAL(TO_CHAR(LAST_DAY(CALENDAR_DATE),'YYYYMMDD'))
You can edit the backlog period date so that you can capture a more detailed backlog history with the following procedure. Possible periods include daily (CAL_DAY_DT), weekly (CAL_WEEK_DT), monthly (CAL_MONTH_DT), and quarterly (CAL_QTR_DT).
In Oracle Order Management and Fulfillment Analytics, W_CUSTOMER_STATUS_HIST_F is a fact table that tracks the status of customers based on the frequency of orders they place with the organization. Possible statuses are NEW, RECENT, DORMANT and LOST. The time duration for each status bucket is configurable, out of the box being a calendar year. The grain of this table is at a Customer, Customer Status and the Status Start Date level. This section explains the possible configurations available for this table, what they mean and how to implement them.
This section talks about the following configurations that are available for the Customer Status History Fact table:
Configure the Data Warehouse Identifier
Configure the Period for each status bucket
Configuring the Data Warehouse Identifier
This table uses some of the Oracle Business Intelligence Applications defined statuses, like NEW, RECENT, DORMANT and LOST. These status data gets loaded into the Data Warehouse directly through an out of box pre-packaged CSV file. The data in the file is independent of any specific OLTP source systems where your Customer or Sales data resides. In order to differentiate between source-based statuses from the pre-packaged out of box Data Warehouse statuses, a definite identifier is required. The Informatica mapping parameter $$WH_DATASOURCE_NUM_ID serves that purpose.
A pre-packaged value equal to 999 is set out of the box. Ideally you would not need to configure this value unless you have chosen to use this number (999) for a specific data source of yours, like Oracle EBS 11.5.10, etc.
For information about how to configure the $$WH_DATASOURCE_NUM_ID value, see:Section 11.2.5.1.9, "How to Configure the Customer Status History Fact table".
Configuring the Period for each status bucket
When a customer orders some products/services from your organization for the first time, Oracle Business Intelligence Applications sets the status for the customer as NEW. The customer maintains the same status if he/she shows a constant order pattern, as long as the duration between any two of his/her orders is less than a configurable/business defined period. The value (out of box being 365 days) of this Informatica parameter $$PERIOD is configurable. An use case for that would be a Fast Moving / Retail Goods company many define 30 days as their choice of period, whereas a Slow Moving company may be even happy with 730 days as period.
In case the customer is seen to have not ordered anything for more than one period, he/she is moved to the next status, RECENT. Similarly, no orders for one more period since he/she became RECENT would make him/her DORMANT. And lastly, he/she is set to LOST if no orders were seen for more than one more period after he/she became DORMANT.
However, if a customer orders while in DORMANT status, for instance, Oracle Business Intelligence Applications would upgrade his/her status back to RECENT. If the customer were in LOST status, and he/she orders, then he/she will be upgraded back to RECENT.
All these examples above illustrate how important it is for the business to set the right value for the period. Organizations would tend to launch different campaigns targetting to different customers based on their current status, or order-patterns, putting it in a different way.
For information about how to configure the $$PERIOD value, see:Section 11.2.5.1.9, "How to Configure the Customer Status History Fact table".
This section explains the steps involved in configuring the Customer Status History Fact table using the $$WH_DATASOURCE_NUM_ID and $$PERIOD variables (for more information about these variables, see Section 11.2.5.1.8, "Configuring the Customer Status History Fact table").
To modify the value of $$WH_DATASOURCE_NUM_ID:
Log on to the DAC repository and display the Design tab.
Navigate to the appropriate container for your OLTP source system.
Display the Source System Parameters tab and locate the parameter $$WH_DATASOURCE_NUM_ID.
In the Edit pane, use the Value field to specify a value.
Save the changes.
To modify the value of $$PERIOD:
Log on to the DAC repository and display the Design tab.
Navigate to the appropriate container for your OLTP source system.
Click on the Tasks tab and query for the following two tasks:
PLP_CustomerStatusHistoryFact_New_Customers_Load
PLP_CustomerStatusHistoryFact_Status_Revalidate
For each task, display the Parameters tab, and use the Value field to specify a value.
Make sure you the set same value for both tasks.
Save the changes.
This section contains configuration steps that apply to Oracle EBS.
Sales order lines are the itemized lines that make up a sales order. This information is stored in the W_SALES_ORDER_LINE_F table. This topic describes how to modify the type of information stored in this table.
By default, only booked orders are extracted from the Oracle source system as shown in Figure 11-1. Therefore, all orders loaded into the Sales Order Lines and Bookings tables are booked.
However, if you want to load nonbooked orders into the Sales Order Lines table, you have to configure the extract so that it does not filter out nonbooked orders. In Oracle 11i and Oracle R12, the OE_ORDER_LINES_ALL.BOOKED_FLAG = Y condition indicates that an order is booked; therefore, this statement is used to filter out nonbooked orders. To load all orders, including nonbooked orders, remove the filter condition from the WHERE clause in the SDE_ORA_SalesOrderLinesFact and SDE_ORA_SalesOrderLinesFact_Primary mappings.
By default, only booked orders are loaded into the Sales Order Lines (W_SALES_ORDER_LINES_F) and Sales Booking Lines (W_SALES_BOOKING_LINE_F) tables. However, you can also load non-booked orders in Sales Order Lines (W_SALES_ORDERS_LINES_F).
To include nonbooked orders in the Sales Order Lines tables
In PowerCenter Designer, open the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_BC_ORA_SalesOrderFact mapplet in the Mapplet Designer.
Double-click the SQ_BCI_SALES_ORDLNS source qualifier to open the Edit Transformations box.
Display the Properties tab.
For both the Sql Query Transformation Attribute and the User Defined Join Transformation Attribute, do the following:
Select the down arrow in the Value field to display the SQL Editor box.
In the SQL box, remove the line 'AND OE_ORDER_LINES_ALL.BOOKED_FLAG='Y''.
Select OK to save the changes.
Validate and save your changes to the repository.
Repeat steps 3 - 5 for the SDE_ORA_SalesOrderLinesFact_Primary mapping.
Sales schedule lines detail when each order's items are slated for shipment. Each sales order is broken into sales order lines, and each sales order line can have multiple schedule lines.
For example, you might not have enough stock to fulfill a particular sales order line, therefore you create two schedules to fulfill it. One schedule ships what you currently have in stock, and the other schedule includes enough time for you to manufacture and ship the remaining items of the sales order line. This information is stored in the W_SALES_SCHEDULE_LINE_F table. This topic describes how to modify the type of information stored in this table.
By default, all orders loaded into the Sales Schedule Lines are booked.
However, if you want to load nonbooked orders into the Sales Schedule Lines table, you have to configure the extract so that it does not filter out nonbooked orders. In Oracle 11i and Oracle R12, the OE_ORDER_LINES_ALL.BOOKED_FLAG = Y condition indicates that an order is booked; therefore, this statement is used to filter out nonbooked orders. To load all orders, including nonbooked orders, remove the filter condition from the WHERE clause in the SDE_ORA_SalesScheduleLinesFact and SDE_ORA_SalesScheduleLineLines_Fact_Primary mappings.
To include nonbooked orders in the Sales Schedule Lines tables
In PowerCenter Designer, open the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_BC_ORA_SalesScheduleLinesFact mapplet in the Mapplet Designer.
Double-click the SQ_BCI_SALES_ORDLNS source qualifier to open the Edit Transformations box.
Display the Properties tab.
For both the Sql Query Transformation Attribute and the User Defined Join Transformation Attribute, do the following:
Select the down arrow in the Value field to display the SQL Editor box.
In the SQL box, remove the line 'AND OE_ORDER_LINES_ALL.BOOKED_FLAG='Y''.
Select OK to save the changes.
Validate and save your changes to the repository.
Repeat steps 3 - 5 for the SDE_ORA_SalesScheduleLinesFact_Primary mapping.
As initially configured for Oracle 11i and Oracle R12, bookings are recorded at the Sales Order Line level. For each booked order, there is at least one row in the Bookings table, as shown in the figure below.
There are two subject areas in the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter containers:
Enterprise Sales - Booking Lines & Order Lines
Enterprise Sales - Booking Lines & Schedule Lines
The execution plans that are installed with Oracle BI Applications use the Enterprise Sales - Booking Lines & Order Lines subject area by default. If you want to load booking lines at the schedule line level, create a new execution plan and include the Enterprise Sales - Booking Lines & Schedule Lines subject area instead of Enterprise Sales - BookingLines & Order Lines.
Bookings may be recorded at the Sales Schedule Line level instead of the Sales Order Line level. At the Sales Schedule Line level, bookings provide a more granular view, as the orders are segmented by schedule line. Bookings recorded at the Schedule Line level provide one row in the Bookings table for each schedule line, as shown in the figure below. Oracle Applications schedule lines have the same granularity as order lines. Therefore, if you pull booking lines from schedule lines, the booking lines are limited to scheduled order lines.
You configure the definition of early and late shipments by editing the EXP_SALES_PCKLNS expression in the mplt_SA_ORA_SalesPickLinesFact mapplet. The mplt_SA_ORA_SalesPickLinesFact mapplet is used by the SDE_ORASalesPickLinesFact mapping.
This mapplet compares the pick date and ship date with the scheduled ship date to determine whether or not orders are late.
To configure early and late tolerances for shipments
In PowerCenter Designer, open the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_SA_ORA_SalesPickLinesFact mapplet in the Mapplet Designer.
Double-click the EXP_SALES_PCKLNS expression to open the Edit Transformation box.
Display the Ports tab.
Edit the Expression for the ports that you want to modify.
For example:
If you want to allow two days after the scheduled pick date before you flag the pick as late, set the Expression value for the VAR_PICK_LATE_TIME_TOL port to 2.
To set the number of days before a pick is flagged as early, set the Expression value the VAR_PICK_EARLY_TIME_TOL port.
To set the number of days before a pick is flagged as late, set the Expression value for the VAR_PICK_LATE_TIME_TOL port.
If you want to change the shipping tolerances, set the Expression values for the shipping ports (for example, VAR_SHIP_LATE_TIME_TOL, VAR_SHIP_EARLY_TIME_TOL).
Validate and save your changes to the repository.
Sales invoice lines are payments for items ordered by a customer. This information is stored in the W_SALES_INVOICE_LINE_F table. This topic describes how to modify the type of information stored in this table.
By default, the Oracle Order Management and Fulfillment Analytics application is configured to extract completed sales invoices when performing the Sales Invoice data extract. Oracle 11i and Oracle R12 use a flag to indicate whether a sales invoice is complete. In particular, completed sales invoices are those where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y in Oracle 11i and Oracle R12.
To extract incomplete sales invoices, as well as complete invoices, remove the extract filter statement.
To remove the extract filter for sales invoices
In PowerCenter Designer, open the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_BC_ORA_SalesInvoiceLinesFact mapplet in the Mapplet Designer.
Double-click the SQ_BCI_SALES_IVCLNS source qualifier to open the Edit Transformations box.
Display the Properties tab.
For the SQL Query Transformation Attribute, select the down arrow in the Value field to display the SQL Editor box.
In the SQL box, remove the line 'AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG='Y''.
Validate and save your changes to the repository.
Repeat steps 2 - 7 for the mplt_BC_ORA_SalesInvoiceLinesFact_Primary mapplet.
The Backlogs and Cycle Lines (OTB ETL) component assumes that the Backlog, Picking and Cycle Lines tables have been updated with shipping and invoicing information (for example, using Oracle EBS Interface programs). If the Oracle Order Line Tables have not been updated with shipping and invoicing information, you need to update the OTB ETL and Execution Plan as follows:
To configure the Order Lines Execution Plan and OTB ETL
In PowerCenter Designer, open the PLP folder.
Open the PLP_SalesCycleLinesFact mapplet in the Mapplet Designer.
Double-click the SQ_W_SALES_ORDER_LINE_F source qualifier to open the Edit Transformations box.
Display the Properties tab.
For the SQL Query Transformation Attribute, select the down arrow in the Value field to display the SQL Editor box.
In the SQL box, replace the SQL text 'X.TOTAL_SHIPPED_QTY' with 'PICKLINE.TOTAL_SHIPPED_QTY'.
In the SQL box, replace the SQL text 'X.TOTAL_INVOICED_QTY' with 'IVCLINE.TOTAL_INVOICE_QTY'.
Validate and save your changes to the repository.
Log into the DAC (for more information, see Section 4.10.2, "How to Log Into the DAC").
In the Design tab, expand the Configuration Points node, and select the Sales PLP Optional Tasks.
Activate the appropriate Subject Areas.
Open the Source Qualifier in SDE_ORA_SalesPickLinesFact.
Modify the SQL Query as follows
Add to the following join condition:
AND WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID=WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID (+) AND WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID=WSH_NEW_DELIVERIES.DELIVERY_ID (+)
Nest the following filter condition within the parenthesis:
OR WSH_NEW_DELIVERIES.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS')
Change select OE_ORDER_LINES_ALL.ACTUAL_SHIPMENT_DATE to select WSH_NEW_DELIVERIES.INTIAL_PICKUP_DATE.
Select WSH_NEW_DELIVERIES.LAST_UPDATE_DATE and link it to EXP_SALES_PCKLNS.LAST_UPDATE_DATE1.
The Oracle Order Management and Fulfillment Analytics application uses tables that are also used in Oracle's Supply Chain Analytics family of products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics).
For Oracle 11i and Oracle R12, you need to use the following configuration steps for Supply Chain Analytics to configure Oracle Order Management and Fulfillment Analytics:
The Oracle Order Management and Fulfillment Analytics application uses tables that are also used in the Oracle Financial Analytics application.
For Oracle 11i and Oracle R12, you need to use the following configuration steps for Oracle Financial Analytics to configure Oracle Order Management and Fulfillment Analytics:
Changes in booked orders are tracked in the Booking Lines table (W_SALES_BOOKING_LINE_F), not in the Sales Order Lines table (W_SALES_ORDER_LINE). By default, the only changes tracked in the W_SALES_BOOKING_LINE_F table are changes in the ordered amount, ordered quantity, or Booking ID. By default, the Booking ID is defined as:
TO_CHAR(INP_LINE_ID)||'~'||TO_CHAR(INP_INV_ITEM_ID)||'~'||TO_CHAR(INP_WAREHOUSE_ID)
Any changes in these fields results in another row in the W_SALES_BOOKING_LINE_F table. However, changes in any other fields does not result in a new row; instead, the existing information are overwritten with the changed information. No history is kept for changes to these other field values. If you want to track other changes you can do so. For example, you may want to track changes to the sales representative who is handling the order. The ETL processes are prepackaged to overwrite sales representative changes; however, if you want to retain them, you must add the attribute to the Booking ID definition in the Booking ID expression in the Source Adapter mapplet (mplt_SA_ORA_SalesOrderLinesFact). The following section describes what happens if you modify the Booking ID to include the sales representative.
Assume you want to track changes to the sales representative for bookings and debookings. You decide to do this to better evaluate each representative's sales performance. To track changes by Salesperson ID, you have to modify the VAR_BOOKING_ID to use the value:
TO_CHAR(INP_LINE_ID)||'~'||TO_CHAR(INP_INV_ITEM_ID)||'~'||to_char(INP_WAREHOUSE_ID)
For example, to edit the VAR_BOOKING_ID value, do the following:
In Informatica Designer, open the mplt_SA_ORA_SalesOrderLinesFact mapplet in the Mapplet Designer.
Double click the MAPI_SALES_ORDLNS transformation to open the Edit Transformation box.
Display the Ports tab.
Select the EXP_SALES_ORDLNS transformation.
Edit the expression for the VAR_BOOKING_ID port.
The following paragraphs and tables describe what happens in the source system and the W_SALES_BOOKING_LINE_F table when you change sales representatives under this scenario.
Day 1: One order is placed with Salesperson 1001. The source system displays the information as shown in Table 11-9.
Table 11-9 Oracle 11i and Oracle R12: Source System Table Row After Day One Activity
| Sales Order Number | Sales Order Line Number | Salesperson ID | Quantity | Selling Price | Date |
|---|---|---|---|---|---|
|
1 |
1 |
1001 |
100 |
25 |
1-June-2000 |
The row in Table 11-9 is entered into the IA Bookings table (W_SALES_BOOKING_LINE_F) as shown in Table 11-10.
Table 11-10 Oracle 11i and Oracle R12: W_SALES_BOOKING_LINE_F Table Row After Day One Activity
| SALES_ORDER_NUM | SALES_ORDER_ITEM | SALESREP_ID | SALES_QTY | NET_DOC_AMT | BOOKED_ON_DT |
|---|---|---|---|---|---|
|
1 |
1 |
1001 |
100 |
2500 |
1-June-2000 |
Day 2: Salesperson 1002 takes over this order, replacing Salesperson 1001. Thus, the salesperson associated with the order is changed from 1001 to 1002 in the source system. The row in the source system looks like the row shown in Table 11-11.
Table 11-11 Oracle 11i and Oracle R12: Source System Table Row After Day Two Activity
| Sales Order Number | Sales Order Line Number | Salesperson ID | Quantity | Selling Price | Date |
|---|---|---|---|---|---|
|
1 |
1 |
1002 |
100 |
25 |
2-June-2000 |
The SIL_SalesBookingLinesFact_Load_OrderLine_Credit, which also writes to the booking table, now does a debooking for the old line and SIL_SalesBookingLinesFact_Load_OrderLine_Debt inserts a new row into the W_SALES_BOOKING_LINE_F booking table. On day two, the row in the W_SALES_BOOKING_LINE_F table looks like the row shown in the Table 11-12.
Backlog information is stored in the W_SALES_BACKLOG_LINE_F and W_SALES_BACKLOG_HISTORY_F tables. This topic describes how to modify the type of information stored in these tables. Many types of backlog exist in the Oracle Order Management and Fulfillment Analytics application—financial backlog, operational backlog, delinquent backlog, scheduled backlog, unscheduled backlog, and blocked backlog. Each type of backlog is defined by two particular dates in the sales process; therefore, calculations of backlog hits multiple fact tables.
For example, financial backlog records which items have been ordered but payment has not been received. Thus, to calculate the number of financial backlog items, you use the Sales Order Lines table (to determine which items have been ordered) and the Sales Invoice Lines table (to see which orders have been paid for). Using these two tables, you can determine the number of items and the value of those items that are on financial backlog.
By default, the Oracle Order Management and Fulfillment Analytics application only extracts open sales orders from the Sales Order Lines (W_SALES_ORDER_LINE_F) table and Sales Schedule Lines table (W_SALES_SCHEDULE_LINE_F) for backlog calculations to populate the Backlog tables. Open sales orders are defined as orders that are not canceled or not complete. The purpose in extracting only open orders is that in most organizations those orders that are closed are no longer a part of backlog. However, if you want to extract sales orders that are marked as closed, you may remove the default filter condition from the extract mapping.
For example, assume your customer orders ten items. Six items are invoiced and shipped, but four items are placed on operational and financial backlog. This backlog status continues until one of two things happens:
The items are eventually shipped and invoiced.
The remainder of the order is canceled.
If you choose to extract sales orders that are flagged as closed, you must remove the condition in the Backlog flag. To do so, use the following procedure.
The BACKLOG_FLAG in the W_SALES_ORDER_LINE_F table is also used to identify which sales orders are eligible for backlog calculations. By default, all sales order types have their Backlog flag set to Y. As a result, all sales orders are included in backlog calculations.
To remove open order extract filters
In PowerCenter Designer, open the SDE_ORA115<ver>_adapter or SDE_ORAR12_adapter folder.
Open the mplt_BC_ORA_SalesOrderLinesFact mapplet in the Mapplet Designer.
Double click on the EXP_SALES_ORDLNS, and display the Ports tab.
Edit the VAR_OPR_BACKLOG_FLG and remove the OPEN_FLAG='Y'.
Edit the VAR_FIN_BACKLOG_FLG and remove the OPEN_FLAG='Y'.
Open the mplt_BC_ORA_SalesScheduleLinesFact mapplet in the Mapplet Designer.
Double click on the EXP_SALES_SCHLNS, and display the Ports tab.
Edit the VAR_OPR_BACKLOG_FLG and remove the OPEN_FLAG='Y'.
Edit the VAR_FIN_BACKLOG_FLG and remove the OPEN_FLAG='Y'.
Validate and save your changes to the repository.
Open the PLP folder.
Open the mappings PLP_SalesBacklogLinesFact_LoadOrderLines and PLP_SalesBacklogLinesFact_LoadScheduleLines.
Remove the condition W_STATUS_CODE <> 'Closed' from the SQL Query in the Source Qualifier.
This section contains configuration steps that apply to PeopleSoft.
Not applicable to Oracle BI Applications Version 7.9.4.
This section contains configuration steps that apply to Universal.
Not applicable to Oracle BI Applications Version 7.9.4.
