9 Configuring Oracle's Supply Chain Family of Products

This chapter describes how to configure Oracle's Supply Chain Analytics family of products for particular sources to meet your business needs.

It contains the following topics:

• Section 9.1, "Overview of Oracle's Supply Chain Analytics Family of Products"

• Section 9.2, "Configuration Required Before A Full Load for Supply Chain Analytics"

9.1 Overview of Oracle's Supply Chain Analytics Family of Products

Oracle's Supply Chain Analytics family of products consists of the following:

• Oracle Inventory Analytics

• Oracle Procurement and Spend Analytics

• Oracle Supplier Performance Analytics

Oracle's Supply Chain Analytics family of products enable organizations to optimize their supply side performance by integrating data from across the enterprise value chain and enabling executives, managers, and frontline employees to make -more informed and actionable decisions. Organizations using Supply Chain Analytics benefit from increased visibility into the complete Supply chain process, including comprehensive procurement and spend analysis, supplier performance analysis, inventory analysis, and supplier payables analysis. Through complete end-to-end insight into the factors that impact supply chain performance, organizations can significantly reduce costs, enhance profitability, increase customer satisfaction, and gain competitive advantage. Supply Chain Analytics also integrate with the other applications in Oracle BI Applications product line. They deliver this insight across the organization to increase the company's effectiveness in managing its customers, suppliers, and financial decisions. Supply Chain Analytics consists of the following modules:

• Section 9.1.1, "Procurement and Spend Analytics Module"

• Section 9.1.2, "Supplier Performance Analytics Module"

• Section 9.1.3, "Inventory Analytics Module"

9.1.1 Procurement and Spend Analytics Module

Provides complete visibility into direct and indirect spend across the enterprise, payment, and employee expenses. Example analyses are spend by Commodity & Supplier, by Purchase Org, Cost Center, and expense by Employee, Buyer, etc.

The Procurement and Spend Analytics application is comprised of these subject areas:

• Total Spend: This is a summary subject area that provides the ability to do comparative analysis and report on requested spend, committed spend and actual spend across suppliers, company, products, commodities and associated hierarchies for both direct and indirect spend (indirect spend being MRO and employee expenses) in detail to allow complete visibility of spending across your organization.

• Purchase Orders: This is a detailed subject area that provides the ability to report on committed spend, and Purchase orders of the suppliers of an organization across suppliers, company, products, commodities and associated hierarchies at purchase order line level

• Purchase Order Costs: This is a detailed subject area that provides the ability to report on committed spend and Purchase orders of the suppliers of an organization across suppliers, company, products, and commodities and associated hierarchies at cost center (distribution line) level.

• Purchase Cycle Lines: This is a summary subject area that provides the ability to report cycle time performance such as Requisition to PO lead time, PO to Receipt lead time, P2P lead time of the Suppliers of an organization.

• Purchase Schedules: This is a detailed subject area that provides the ability to report on purchase order shipments of an organization across suppliers, company, products, commodities and associated hierarchies at purchase schedule line level

• Purchase Requisitions: This is a detailed subject area that provides the ability to report on requested spend and Purchase requisitions of the suppliers of an organization across suppliers, company, products, commodities and associated hierarchies at purchase requisition line level

• Purchase Requisition Status: This is a summary subject area that provides the ability to report on requisition status along the approval cycle of Purchase requisitions of the suppliers of an organization. It's populated only by Universal adapter.

• Purchase Receipts: This is a detailed subject area that provides the ability to report on actual spend and Purchase Receipts of the suppliers of an organization across suppliers, company, location, products, commodities and associated hierarchies at purchase receipt line level

• Employee Spend: This is a detailed subject area that provides the ability to report on employee spend of an organization across employees, company, cost center and associated hierarchies. The Expenses subject area contains targeted metrics and reports that examine travel and expense costs in relationship to your organization's overall spending patterns. In contrast to analyzing direct spending patterns, where you may review purchasing, Expenses examines indirect spending—the cost of employee related expenses. It's populated only by Universal adapter.

9.1.2 Supplier Performance Analytics Module

Enables organizations to have a complete picture of the performance of their suppliers, including complete supplier scorecards, procurement cycle times, supplier price performance, delivery performance, product receipt quality, on-time payment ratings, payment activity and volume and payments due / overdue analysis.

The Supplier Performance Analytics application is comprised of these subject areas:

• Supplier Performance. The Suppliers functional area contains targeted reports and metrics that allow you to analyze the timeliness, reliability, cost, and quality of goods provided by your suppliers. It helps you to understand how well suppliers are contributing to success of your organization, and to evaluate the price, quality, and delivery timing in procuring materials

• Supplier AP Transactions: This is a summary subject area that provides the ability to analyze payment performance and payment due analysis of the suppliers of an organization across suppliers, company, location, products, commodities and associated hierarchies. In addition to monitoring supplier performance, it is important to monitor organization's performance of making on time payments. This will help the Organizations to maintain better relationships with their best suppliers.

9.1.3 Inventory Analytics Module

Incorporates analysis of inventory held by an organization; bill of materials; and inventory movements in, out, and through manufacturing plants, distribution centers, or storage locations. This enables companies to monitor inventory levels trend to Sales performance to improve cost exposure, increase turnover through inventory level reduction and increased velocity, properly deploy inventory at the right place / right time and better understand Customer and Supplier Returns to maintain quality.

The Inventory Analytics application is comprised of these subject areas:

• Inventory Transactions. The Inventory Transactions subject area allows you to analyze the various types of events and tasks that occur. Examples of these activities include tracking inventory by type of movement. For example, transfer, issues, receipts, returns, sales, and so on. It allows the user to understand the impact of these activities on business operations, and allows the identification of problematic trends early. For example, large quantities of product in-transit.

• Inventory Balances. The Inventory Balances subject area allows you to analyze the inventory held by an organization in relation to a number of different dimensions. For example, Product type, Product number, Storage location, Plant, Consigned Inventory, Restricted, and so on. It allows the user the ability to understand and determine the optimal distribution of assets as well as identify potential issues such as unnecessary build up of inventories.

• Bill of Materials. The Bill of Materials (BOM) subject area allows you to determine the profit margin of the components that comprise the finished goods. BOM allows you to keep up with the most viable vendors in terms of cost and profit, and to keep your sales organization aware of product delivery status, including shortages.

• Customer and Supplier Returns. The Customer and Supplier Returns subject area allows the user to specifically monitor the return of product by both Customers and Suppliers. At a Product level, it allows the user to identify early, potential, Customer-satisfaction issues relating to problematic Suppliers and Product.

9.2 Configuration Required Before A Full Load for Supply Chain Analytics

This section contains configuration steps that you need to perform on Oracle's Supply Chain Analytics family of products before you do a full data load, and contains the following topics:

• Section 9.2.1, "Configuration Steps for Supply Chain For All Source Systems"

• Section 9.2.2, "Configuration Steps for Supply Chain For Oracle EBS"

• Section 9.2.3, "Configuration Steps for Supply Chain For PeopleSoft"

• Section 9.2.4, "Configuration Steps for Supply Chain For Universal"

• Section 9.2.5, "Configuration Steps for Controlling Your Data Set"

9.2.1 Configuration Steps for Supply Chain For All Source Systems

This section contains configuration steps that apply to all source systems before you do a full data load, and contains the following topics:

• Section 9.2.1.1, "How to Deploy Stored Procedures"

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").

9.2.1.1 How to Deploy Stored Procedures

Stored procedures are a group of SQL statements that perform particular tasks on the database. For example, stored procedures can help to improve the performance of the database.

You can deploy stored procedures by copying the stored procedure files from your Oracle BI Analytics installation and deploying them to the target data warehouse.

Note: Some sessions may fail if these procedures are not compiled in the database before running the workflows.

To deploy stored procedures

1. Navigate to the OracleBI\dwrep\Informatica\Stored_Procedure_Scripts folder. copy the source codes Compute_Bounds_Ora11i.sql into the target data warehouse schema.

2. Compile the stored procedures in the target data warehouse database.

   
   

   Note: If you have problems deploying the stored procedures, see your database reference guide, or contact your database administrator.

   
   

9.2.2 Configuration Steps for Supply Chain For Oracle EBS

This section contains configuration steps that apply to Oracle EBS before you do a full data load, and contains the following topics:

• Section 9.2.2.1, "How to Configure the Bill of Materials Explosion for Oracle Version 11.5.9 and above"

• Section 9.2.2.2, "How to Configure the DAC parameter for Purchase Cycle Line"

• Section 9.2.2.3, "How to Configure Quantity Types for Product Transactions"

• Section 9.2.2.4, "Domain Values and CSV Worksheet Files for Supply Chain Analytics"

9.2.2.1 How to Configure the Bill of Materials Explosion for Oracle Version 11.5.9 and above

Starting from 11.5.9 version, the BOM_EXPLOSION_TEMP table is changed to temp table, duration = SYS$SESSION. To be able to extract data from the BOM_EXPLOSION_TEMP table, you need to follow these configuration steps:

To configure the bill of materials explosion options for Oracle Version 11.5.9

1. On the Informatica Server machine, Click Start, click Run, type regedit, click OK.

2. Go to the following registry key:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PowerMart\Parameters\Configuration
   

3. On the Edit menu, point to New, and then click String Value.

4. Enter the String Value as:

   • Name: ShareConnections_<folder name>.SDE_ORA_BOMItemFact

   • Data: Yes

   • The folder name is one of the following:

     • For Oracle 11.5.9 Version, use SDE_ORA1159_adapter.

     • For Oracle 11.5.10 Version, use SDE_ORA11510_adapter.

     • For Oracle R12 Version, use SDE_ORAR12_adapter.

5. Re-start the PowerCenter server (Informatica Service).

To configure the bill of materials explosion options for Oracle 11.5.9 and above on a Unix machine

1. Add the following line in /<INFA_home>/informatica/pm/pmserver.cfg file:

   ShareConnections_<folder name>.SDE_ORA_BOMItemFact=Yes

2. Enter the Folder Name value as one of the following:

   • For Oracle 11.5.9 Version, use SDE_ORA1159_adapter.

   • For Oracle R12 Version, use SDE_ORAR12_adapter.

3. Re-start the PowerCenter server (Informatica Service).

9.2.2.2 How to Configure the DAC parameter for Purchase Cycle Line

To load the purchase cycle line table (W_PURCH_CYCLE_LINE_F), the ETL tasks needs to distinguish data coming from Oracle 11i applications from data from other applications such as SAP and PeopleSoft. This configuration explains the detail steps.

To configuring the DAC parameter for purchase cycle line

1. Login to your DAC repository

2. Go to the Tasks Tab, query for task SIL_PurchaseCycleLinesFact.

3. Click Parameters Child tab. Change the values for parameter $$ORA_DATASOURCE_NUM_ID_LIST from (2,4,5,9) to the list of Data Source NUM ID you defined for your Oracle connections

4. Repeat the same for task SIL_PurchaseCycleLinesFact_Extract

For more information on configuring DAC, please refer to Oracle Business Intelligence Data Warehouse Administration Console Guide.

9.2.2.3 How to Configure Quantity Types for Product Transactions

Oracle 11i categorize quantities into three different types:

• Goods Received quantities. Goods Received quantity refers to the number of goods received.

• Delivery quantities. Delivery quantity refers to the number of goods delivered.

• Base quantities. Base quantity refers to any transaction quantity.

The Oracle Business Analytics Warehouse extracts the transaction type and loads this value into the XACT_SRC_TYPE column. In this column, the value 1 denotes a Goods Received quantity, and 2 denotes a Delivery quantity.

To find out more about XACT_SRC_TYPE column, please execute the following SQL against your EBS instance:

select TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_TYPE_NAME, DESCRIPTION from MTL_TXN_SOURCE_TYPES order by 1

If you have rows equivalent to Purchase order (1), you should include the TRANSACTION_SOURCE_TYP E_ID in the Goods Received quantity column (EXT_GR_QTY). If you have rows equivalent to Sales Order (2), you should include the TRANSACTION_SOURCE_TYPE_ID in the Delivery quantity column (EXT_DELIVERY_QTY).

All quantities extracted from the source system are always loaded into the Base quantity column (EXT_BASE_QTY). However, only the receipt quantity is loaded into the Goods Received quantity column (EXT_GR_QTY), and only delivered quantities are loaded into the Delivery quantity column (EXT_DELIVERY_QTY).

If your definition of goods received or delivery quantity is different from the prepackaged condition, then you can edit the condition to suit your business needs.

To configure the Quantity type

1. In PowerCenter Designer, open the SDE_ORA<Ver>_adapter.

2. Open the mplt_SA_ORA_ProductTransactionFact mapplet.

3. Double-click the Expression transformation to open the Edit Transformations dialog box, and click the Port tab to display the EXT_GR_QTY and EXT_DELIVERY_QTY port.

4. Edit the quantity types by substituting your desired condition for the prepackaged expression.

5. Click Apply.

6. Validate the mapplet, and save your changes to the repository.

9.2.2.4 Domain Values and CSV Worksheet Files for Supply Chain Analytics

If you have modify or extend a seeded list of values, you must configure the CSV files for Oracle Supply Chain Analytics by mapping values from your source systems to the domain values.

This section explains how to extract the lists of values from your source system, which you then compare with the seeded values. If the lists of values are different to the seeded values, you need to follow the instructions to configure the Domain Values and CSV Worksheet Files.

9.2.2.4.1 List of Domain Values and CSV Worksheet Files for Supply Chain Analytics

Table 9-4 lists the CSV worksheet files and the domain values for Supply Chain Analytics in the $pmserver\LkpFiles folder.

Table 9-1 Domain Values and CSV Worksheet Files for Supply Chain Analytics

Worksheet File Name	Description	Session
domainValues_Movement_Types_ora11i.csv	List the Movement Type and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 9.2.2.4.2, "To configure domainValues_Movement_Types_ora11i.csv".	SDE_ORA_MovementTypeDimension
domainValues_PaymentType_ora12.csv	Lists the Payment Type and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 9.2.2.4.3, "To configure domainValues_PaymentType_ora12.csv".	SDE_ORA_TransactionTypeDimension_PO_Payment_Type
domainValues_ShipmentType_ora12.csv	List the Shipment Type and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 9.2.2.4.10, "To configure domainValues_ShipmentType_ora12.csv".	SDE_ORA_TransactionTypeDimension_PO_Shipment_Type
domainValues_Status_Purch_Approve_ora11i.csv	Lists the Purchasing Approval Status column and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 9.2.2.4.4, "To configure domainValues_Status_Purch_Approve_ora11i.csv".	SDE_ORA_StatusDimension_PurchaseApprove
domainValues_Status_Purch_Cycle_ora11i.csv	Lists the Purchasing Cycle Status column and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 9.2.2.4.5, "To configure domainValues_Status_Purch_Cycle_ora11i.csv".	SDE_ORA_StatusDimension_PurchaseCycle
domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv	List the Purchase Basis Type and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 9.2.2.4.9, "To configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv".	SDE_ORA_TransactionTypeDimension_PO_Line_Type
domainValues_Xact_Types_PO_Line_Type_ora11i.csv	Lists the Purchasing Line Type and the corresponding domain Values for the Oracle EBS application. For information about how to edit this file, see Section 9.2.2.4.8, "To configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv".	SDE_ORA_TransactionTypeDimension_PO_Line_Type
domainValues_Xact_Types_Purch_Orders_ora11i.csv	Lists the Purchase Order Transaction Type column and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 9.2.2.4.6, "To configure domainValues_Xact_Types_Purch_Orders_ora11i.csv".	SDE_ORA_TransactionTypeDimension_PurchaseOrder
domainValues_Xact_Types_Purch_Requisitions_ora11i.csv	Lists the Purchase Requisition Transaction Type column and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 9.2.2.4.7, "To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv".	SDE_ORA_TransactionTypeDimension_PurchaseRequest

9.2.2.4.2 To configure domainValues_Movement_Types_ora11i.csv

This section explains how to configure domainValues_Movement_Types_ora11i.csv.

1. Identify the Inventory Movement Type in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_NAME FROM MTL_TRANSACTION_TYPES

2. From the $pmserver\lkpfiles folder, open the domainValues_Movement_Types_ora11i.csv file in a text editor.

3. Copy the TRANSACTION_TYPE_NAME to the TRANSACTION_TYPE_NAME column in the file.

   The data must be copied starting from the 2nd line.

4. Map each TRANSACTION_TYPE_NAME to one Inventory Movement Type domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.3 To configure domainValues_PaymentType_ora12.csv

This section explains how to configure domainValues_PaymentType_ora12.csv. This file is only applicable to Oracle R12.

1. Identify the Purchase Order Payment Type in your Oracle EBS source system by using the following SQL:

   SELECT PO_LOOKUP_CODES.LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE PO_LOOKUP_CODES.LOOKUP_TYPE = 'PAYMENT TYPE'

2. From the $pmserver\lkpfiles folder, open the domainValues_PaymentType_ora12.csv file in a text editor.

3. Copy the LOOKUP_CODE to the XACT_TYPE_CODE column in the file.

   The data must be copied starting from the 2nd line.

4. Map each LOOKUP_CODE to one PO Payment Type (PO_PAYMENT_TYPE) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.4 To configure domainValues_Status_Purch_Approve_ora11i.csv

This section explains how to configure domainValues_Status_Purch_Approve_ora11i.csv.

1. Identify the Purchase Approval Status in your Oracle EBS source system by using the following SQL:

   SELECT A.LOOKUP_CODE FROM FND_LOOKUP_VALUES A WHERE A.LOOKUP_TYPE='AUTHORIZATION STATUS' AND A.LANGUAGE = 'US'

2. From the domainValues_Status_Purch_Approve_ora11i.csv file in a text editor.

3. Copy the LOOKUP_CODE to the STATUS_CODE column in the file.

   The data must be copied starting from the 8th line.

4. Map each LOOKUP_CODE to one Purchase Approval (PURCH_APPROVAL) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.5 To configure domainValues_Status_Purch_Cycle_ora11i.csv

This section explains how to configure domainValues_Status_Purch_Cycle_ora11i.csv.

1. Identify the Purchase Cycle Status in your Oracle EBS source system by using the following SQL:

   SELECT A.LOOKUP_CODE FROM FND_LOOKUP_VALUES A WHERE A.LOOKUP_TYPE='DOCUMENT STATE' AND A.LANGUAGE = 'US'

2. From the $pmserver\lkpfiles folder, open domainValues_Status_Purch_Cycle_ora11i.csv file in a text editor.

3. Copy the LOOKUP_CODE to the STATUS_CODE column in the file.

   The data must be copied starting from the 8th line.

4. Map each LOOKUP_CODE to one Purchase Cycle (PURCH_CYCLE) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.6 To configure domainValues_Xact_Types_Purch_Orders_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Purch_Orders_ora11i.csv.

1. Identify the Purchase Order Types in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE

   FROM PO_DOCUMENT_TYPES_ALL_TL

   WHERE PO_DOCUMENT_TYPES_ALL_TL.LANGUAGE='US' AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE <> 'CONTRACT'

   UNION SELECT 'COMPLEXWORK_ACTUAL' FROM DUAL

   UNION SELECT 'COMPLEXWORK_FINANCING' FROM DUAL;

2. From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Purch_Orders_ora11i.csv file in a text editor.

3. Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.

   The data must be copied starting from the 8th line.

4. Map each DOCUMENT_SUBTYPE to one Purchase Order transaction type (PURCH_ORDERS) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.7 To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv.

1. Identify the Purchase Requisition Type in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE

   FROM PO_DOCUMENT_TYPES_ALL_TL

   WHERE PO_DOCUMENT_TYPES_ALL_TL.LANGUAGE='US' AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_TYPE_CODE = 'REQUISITION'

2. From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Purch_Requisitions_ora11i.csv file in a text editor.

3. Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.

   The data must be copied starting from the 7th line.

4. Map each DOCUMENT_SUBTYPE to one Purchase Requisition Type (PURCH_RQLNS) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.8 To configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv

This section explains how to configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv.

1. Identify the Purchase Order Line Type in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT PO_LINE_TYPES_V.ORDER_TYPE_LOOKUP_CODE FROM PO_LINE_TYPES_V

2. From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_PO_Line_Type_ora11i.csv file in a text editor.

3. Copy the ORDER_TYPE_LOOKUP_CODE to the XACT_TYPE_CODE column in the file.

   The data must be copied starting from the 8th line.

4. Map each ORDER_TYPE_LOOKUP_CODE to one PO Line Type transaction type (PO_LINE_TYPE) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.9 To configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv.

1. Identify the Purchase Order Line Purchase Basis in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT PO_LINE_TYPES_V.PURCHASE_BASIS FROM PO_LINE_TYPES_V

2. From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv file in a text editor.

3. Copy the PURCHASE_BASIS to the XACT_TYPE_CODE1 column in the file.

   The data must be copied starting from the 8th line.

4. Map each PURCHASE_BASIS to one PO Line Type Purchase Basis Type (PO_LINE_TYPE) domain value.

   Use commas to separate the entries.

5. Save and close the file.

9.2.2.4.10 To configure domainValues_ShipmentType_ora12.csv

This section explains how to configure domainValues_ShipmentType_ora12.csv. This file is only applicable to Oracle R12.

1. Identify the Purchase Order Shipment Type in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT PO_LOOKUP_CODES.LOOKUP_CODE FROM PO_LOOKUP_CODES

   WHERE PO_LOOKUP_CODES.LOOKUP_TYPE = 'SHIPMENT TYPE' AND

   PO_LOOKUP_CODES.LOOKUP_CODE IN ('STANDARD', 'PREPAYMENT', 'SCHEDULED', 'BLANKET')

2. From the $pmserver\lkpfiles folder, open domainValues_PaymentType_ora12.csv file in a text editor.

3. Copy the LOOKUP_CODE to the XACT_TYPE_CODE column in the file. The data must be copied starting from the 2nd line.

4. Map each LOOKUP_CODE to one PO Shipment Type (PO_SHIPMENT_TYPE) domain value. Use commas to separate the entries.

5. Save and close the file.

9.2.3 Configuration Steps for Supply Chain For PeopleSoft

This section contains configuration steps that apply to PeopleSoft before you do a full data load.

Not applicable to Oracle BI Applications Version 7.9.4.

9.2.4 Configuration Steps for Supply Chain For Universal

This section contains configuration steps that apply to Universal before you do a full data load.

Not applicable to Oracle BI Applications Version 7.9.4.

9.2.5 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Supply Chain Analytics.

• Section 9.2.5.1, "Configuration Steps for Supply Chain For All Source Systems"

• Section 9.2.5.2, "Configuration Steps for Supply Chain For Oracle EBS"

• Section 9.2.5.3, "Configuration Steps for Supply Chain For PeopleSoft"

• Section 9.2.5.4, "Configuration Steps for Oracle Supply Chain For Universal"

9.2.5.1 Configuration Steps for Supply Chain For All Source Systems

This section contains configuration steps that apply to all source systems, and contains the following topics:

• Section 9.2.5.1.1, "About Configuring the Inventory Monthly Balance Table"

• Section 9.2.5.1.2, "How to Configure the Inventory Monthly Balance Table"

• Section 9.2.5.1.3, "About Configuring the Product Transaction Aggregate Table"

• Section 9.2.5.1.4, "How to Configure the Product Transaction Aggregate Table"

• Section 9.2.5.1.5, "About Configuring the Purchase Receipts Aggregate Table"

• Section 9.2.5.1.6, "How to Configure the Purchase Receipts Aggregate Table"

• Section 9.2.5.1.7, "About Configuring the Purchase Cycle Lines Aggregate Table"

• Section 9.2.5.1.8, "How to Configure the Purchase Cycle Lines Aggregate Table"

9.2.5.1.1 About Configuring the Inventory Monthly Balance Table

To configure the Inventory Monthly Balance (W_INVENTORY_DAILY_BALANCE_F_A1) aggregate table, you need to consider the aggregation level, the time period to update the aggregation, and the time period to keep records in the Inventory Balance table.

You need to configure three parameters to configure the Inventory Monthly Balance table:

• GRAIN

• KEEP_PERIOD

• NUM_OF_PERIOD

The GRAIN parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:

• DAY

• WEEK

• MONTH

• QUARTER

• YEAR

The KEEP_PERIOD parameter has a preconfigured value of Month. Values for the KEEP_PERIOD parameter include:

• DAY

• WEEK

• MONTH

• QUARTER

• YEAR

The NUM_OF_PERIOD parameter has a preconfigured value of 3. The value for the NUM_OF_PERIOD parameter is a positive integer, for example, 1, 2, 3, and so on.

9.2.5.1.2 How to Configure the Inventory Monthly Balance Table

You need to configure the parameterfileDW.txt parameters file, and run the initial ETL session or incremental ETL sessions to load the Inventory Monthly Balance table.

To configure the parameterfileDW.txt parameters file

1. Open the parameterfileDW.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.

2. Replace the default parameter values with your new values.

   The default values for the parameterfileDW.txt file are shown in the following table.

   Table 9-2 Default values in the parameterfileDW.txt file

   SESSION	Parameter	Value
   PLP_InventoryMonthlyBalance	$$GRAIN	'MONTH'
   PLP_InventoryDailyBalance_Trim	$$KEEP_PERIOD	'MONTH'
   PLP_InventoryDailyBalance_Trim	$$NUM_OF_PERIOD	3

   
   
   
   

   Note: You need to use single quotes for the values of the KEEP_PERIOD and GRAIN parameters.

   
   

3. Save and close the file.

To incrementally refresh the Inventory Monthly Balance table

1. Delete the records from the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) aggregate table for a certain time.

   The GRAIN parameter determines the time period for the deletion. For example, if GRAIN=MONTH, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table.

   Running the PLP_InventoryMonthlyBalance workflow mapping implements this step.

2. Retrieve the records in the Inventory Balance (W_INVENTORY_DAILY_BALANCE_F) fact table and load the records to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table at a certain grain level.

   For example, if GRAIN=MONTH, then the month end balance records in the W_INVENTORY_DAILY_BALANCE_F fact table are stored in and aggregated to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F).

   Running the S_M_PLP_INV_BALANCE_A1_AGG session, and the M_PLP_INV_BALANCE_A1_AGG mapping implements this step. For the current month balance, balance records of the previous day (if it is in the same month) are deleted from W_INVENTORY_MONTHLY_BAL_F, and balance records of the current day will be loaded from W_INVENTORY_BALANCE_F to W_INVENTORY_MONTHLY_BAL_F.

   Running the PLP_InventoryMonthlyBalance workflow implements this step.

3. Remove the old records from the W_INVENTORY_DAILY_BALANCE_F fact table.

   To remove old records you need to use the KEEP_PERIOD and the NUM_OF_PERIOD parameters. For example, if KEEP_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then the records for April and the current month (May) are kept and the older records are deleted.

   Running the PLP_InventoryDailyBalance_Trim workflow implements this step.

   
   

   Note: The trimming process is to reduce data size in the table. It is important to emphasize that you will not be able to see the old daily balance records. But you will still be able to see the month-end balance. Therefore, please make sure that you adjust the NUM_OF_PERIOD values to reflect your data volume and data recency requirement.

   
   

9.2.5.1.3 About Configuring the Product Transaction Aggregate Table

There are two aggregation scenarios to configure the Product Transaction aggregate (W_PRODUCT_XACT_A) table—the initial ETL run and then the incremental ETL run.

For your initial ETL run, you need to configure the aggregation level, and the length of history kept in the Product Transaction fact table.

For your initial ETL run, you need to configure the aggregation grain, using the GRAIN parameter.

For the incremental ETL run, you need to configure the aggregation level, the update period in aggregation, and the length of history kept in the Product Transaction fact table, using the following parameters:

• GRAIN

  The GRAIN parameter specifies the aggregation level. Valid values are DAY, WEEK, MONTH (preconfigured value), QUARTER, YEAR.

• REFRESH_PERIOD

  The REFRESH_PERIOD parameter, together with NUM_OF_PERIOD, indicates the number of period of records that will be refresh from the transaction table to the aggregate table. Valid values are DAY, WEEK, MONTH (preconfigured value), QUARTER, YEAR.

• NUM_OF_PERIOD

  The NUM_OF_PERIOD parameter, together with REFRESH_METHOD, indicates the number of period of records that will be refresh from the transaction table to the aggregate table. Valid values are positive integers, for example, 1, 2, 3 (preconfigured value).

9.2.5.1.4 How to Configure the Product Transaction Aggregate Table

You need to configure the parameterfileDW.txt parameters file, and run the initial ETL and then the incremental ETL to load the Product Transaction aggregate table.

To configure the parameterfileDW.txt parameters file

1. Open the parameterfileDW.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.

2. Replace the default parameter values with your new values.

   The default values are shown in the following table.

   Table 9-3 Default values in the parameterfileDW.txt file

   SESSION	PARAMETER	VALUE
   PLP_ProductTransactionAggregate	$$REFRESH_PERIOD	'MONTH'
   PLP_ProductTransactionAggregate	$$GRAIN	'MONTH'
   PLP_ProductTransactionAggregate	$$NUM_OF_PERIOD	3
   PLP_ProductTransactionAggregate_Full	$$GRAIN	'MONTH'

   
   
   
   

   Note: You need to use single quotes for the values of the KEEP_PERIOD, GRAIN, and REFRESH_PERIOD parameters. The KEEP_PERIOD value must be equal to or greater than the GRAIN value. The REFRESH_PERIOD value must equal the GRAIN value.

   
   

3. Save and close the file.

To configure the Product Transaction aggregate table for the initial ETL run

1. Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F) table, and aggregate the records to the Product Transaction aggregate (W_PRODUCT_XACT_A) table at a certain grain level.

   For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F fact table are retrieved and aggregated to the W_PRODUCT_XACT_A table at a monthly level.

   Running the PLP_ProductTransactionAggregate_Full workflow implements this step.

To configure the Product Transaction aggregate table for the incremental ETL run

1. Delete the refreshed records from the Product Transaction aggregate (W_PRODUCT_XACT_A) table for a certain time.

   The REFRESH_PERIOD and the NUM_OF_PERIOD parameters determine the time period for the deletion.

   For example, if REFRESH_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the W_PRODUCT_XACT_A table.

   Running the PLP_ProductTransactionAggregate workflow implements this step.

2. Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F) table, and aggregate the records to the W_PRODUCT_XACT_A table at a certain grain level.

   For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F fact table are retrieved and aggregated to the W_PRODUCT_XACT_A table at a monthly level.

   Running the PLP_ProductTransactionAggregate workflow implements this step.

9.2.5.1.5 About Configuring the Purchase Receipts Aggregate Table

The Purchase Receipts aggregate table (W_PURCH_RCPT_A) is used to capture information about the product receipts received from your suppliers and the purchase orders placed on them by your purchasing organization.

For your initial ETL run, you need to configure the GRAIN parameter for the time aggregation level in the Purchase Receipts Aggregate fact table.

For the incremental ETL run, you need to configure the time aggregation level and the source identification. The source identification value represents the source system you are sourcing data from.

You need to configure two parameters to aggregate the Purchase Receipts table for your incremental run:

• GRAIN

• $$TIME_GRAIN

These parameters have a preconfigured value of Month. The possible values for the parameter are:

• DAY

• WEEK

• MONTH

• QUARTER

• YEAR

The Purchase Receipt 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 Purchase Receipts 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 done in four steps:

1. 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_PURCH_RCPT_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreSoftDeleteImage, and it is run before the records are deleted from the base table. The mapping is run in the source-specific workflow.

2. 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_PURCH_RCPT_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreLoadImage, and it is run before the records are updated in the base table. It is run in the source-specific workflow.

3. 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_PURCH_RCPT_TMP table, without changing their sign. The mapping responsible for this task is suffixed with _Derive_PostLoadImage, and it is run after the records are updated or inserted into the base table. It is run in the post load-processing workflow.

4. Oracle Business Analytics Warehouse aggregates the W_PURCH_RCPT_TMP table, and joins it with the W_PURCH_RCPT_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is suffixed with _Derive.

9.2.5.1.6 How to Configure the Purchase Receipts Aggregate Table

To load the Purchase Receipts aggregate table (W_PURCH_RCPT_A), you need to configure the post-load-processing parameter file and the source system parameter files, and run the initial workflow and then the incremental workflow.

To configure the parameterfiledw.txt parameters file

1. Open the parameterfiledw.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.

2. Replace the default parameter values with your new values.

3. Save and close the file.

To configure the Purchase Receipts aggregate table for Universal Source

1. Open the parameterfileDW.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.

2. Replace the default parameter values with your new values.

3. Save and close the file.

9.2.5.1.7 About Configuring the Purchase Cycle Lines Aggregate Table

To aggregate the Purchase Cycle Lines table (W_PURCH_CYCLE_LINE_A), you need to configure the parameterfiledw.txt parameters file and the source system parameters file, and run the initial ETL workflow and then the incremental ETL workflow.

For your initial ETL run, you need to configure the GRAIN parameter for the time aggregation level in the Purchase Cycle Lines Aggregate fact table.

For the incremental ETL run, you need to configure the time aggregation level and the source identification. The source identification value represents the source system you are sourcing data from.

You need to configure two parameters to aggregate the Purchase Cycle Lines table for your incremental run:

• GRAIN

• $$TIME_GRAIN

These parameters has a preconfigured value of Month. The possible values for parameters are:

• DAY

• WEEK

• MONTH

• QUARTER

• YEAR

The Purchase Cycle Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. The Purchase Cycle Lines aggregate table is not fully reloaded from the base table after an ETL run. Oracle Business Analytics Warehouse minimize the incremental aggregation effort, by modifying the aggregate table incrementally as the base table gets updated. This process is done in four steps:

1. 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_PURCH_CYCLE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreSoftDeleteImage, and it is run before the records are deleted from the base table. It is run in the source-specific workflow.

2. 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_PURCH_CYCLE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreLoadImage, and it is run before the records are updated in the base table. It is run in the source-specific workflow.

3. 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_PURCH_CYCLE_LINE_TMP table, without changing their sign. The mapping responsible for this task is suffixed with _Derive_PostLoadImage, and it is run after the records are updated or inserted into the base table. It is run in the post load-processing workflow.

4. Oracle Business Analytics Warehouse aggregates the W_PURCH_CYCLE_LINE_TMP table, and joins it with the W_PURCH_CYCLE_LINE_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is suffixed with _Derive.

9.2.5.1.8 How to Configure the Purchase Cycle Lines Aggregate Table

To load the Purchase Cycle Lines aggregate table (W_PURCH_CYCLE_LINE_A), you need to configure the post-load-processing parameter file and the source system parameter files, and run the initial workflow and then the incremental workflow.

To configure the Purchase Cycle Lines Aggregate Table

1. Open the parameterfiledw.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.

2. Replace the default parameters values with your new values.

3. Save and close the file.

9.2.5.2 Configuration Steps for Supply Chain For Oracle EBS

This section contains configuration steps that apply to Oracle EBS, and contains the following topics:

• Section 9.2.5.2.1, "How to Configure the Bill of Materials Explosion Options"

• Section 9.2.5.2.2, "How to Configure the Left Bound and Right Bound Calculation Option"

• Section 9.2.5.2.3, "How to Extract Particular Purchase Order Records"

9.2.5.2.1 How to Configure the Bill of Materials Explosion Options

The Bill of Materials (BOM) functional area allows you to determine the profit margin of the components that comprise the finished goods. BOM allows you to keep up with the most viable vendors in terms of cost and profit, and to keep your sales organization aware of product delivery status, including shortages.

You can explode the BOM structure with three different options:

• All. All the BOM components are exploded regardless of their effective date or disable date. To explode a BOM component is to expand the BOM tree structure.

• Current. The incremental extract logic considers any changed components that are currently effective, any components that are effective after the last extraction date, or any components that are disabled after the last extraction date.

• Current and Future. All the BOM components that are effective now or in the future are exploded. The disabled components are left out.

These options are controlled by the EXPLODE_OPTION variable. The EXPLODE_OPTION variable is preconfigured with a value of 2, explode Current BOM structure.

In the SDE_ORA_BomItemFact mapping, the bompexpl.exploder_userexit stored procedure explodes the BOM structure. The table below lists the variables for the bompexpl.exploder_userexit stored procedure.

Table 9-4 Variables for the bompexpl.exploder_userexit Stored Procedure

Input Variable	Preconfigured Value	Description
VERIFY_FLAG	0	A verify flag with a value of 1 only applies to standard BOM.
ORG_ID	ORGANIZATION_ID	Organization ID
ORDER_BY	1	Controls the order of the records. 1—Operation Sequence Number, Item Number 2—Item Number, Operation Sequence Number
GRP_ID	Negative Sequence ID -1, -2, and so on.	Unique value to identify current explosion.
SESSION_ID	Negative Sequence ID -1, -2, and so on.	Unique value to identify current session.
LEVELS_TO_EXPLODE	10	Levels to explode.
BOM_OR_ENG	1	1—BOM 2—ENG
IMPL_FLAG	1	1—Implemented Only 2—Implemented and Non-implemented
PLAN_FACTOR	2	1—Yes 2—No
EXPLODE_OPTION	2	1—All 2—Current 3—Current and Future
MODULE	2	1—Costing 2—BOM 3—Order Entry 4—ATO 5—WSM
CST_TYPE_ID	0	Cost type ID for cost explosion.
STD_COMP_FLAG	0	1—Explode only standard components 2—All components
EXPL_QTY	1	Explosion quantity
ITEM_ID	ROUND(TO_DECIMAL(PRODUCT_ID))	Item ID of assembly to explode.
ALT_DESG	ALTERNATE_BOM_DESIGNATOR	Alternate routing designator
COMP_CODE	NULL	Concatenated component code
REV_DATE	TO_CHAR(CREATION_DT, 'YYYY/MM/DD HH24:MI')	Explosion date YYYY/MM/DD HH24:MI

There are five different BOM types in a source system—1- Model, 2 - Option Class, 3 - Planning, 4 - Standard, and 5 - Product Family. By default, only the Standard BOM type is extracted and exploded.

To configure the BOM explosion to the All option

1. In PowerCenter Designer, open the SDE_ORA<Ver>_adapter.

2. Open the SDE_ORA_BomItemFact mapping.

3. Double-click the EXP_BOMPEXPL_EXPLODER_USEREXIT expression transformation to open the Edit Transformations dialog box, and click the Port tab to display the EXPLODE_OPTION port.

4. Change the value to 1, and click Apply.

5. Validate the mapping, and save your changes to the repository.

6. Go to Mapplet Designer, and open mplt_BC_ORA_BOMHeaderDimension.

7. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.

8. Modify the following default Where condition from:

   ((
   
      /* CURRENT valid component changed */ 
   
      INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') AND (INV.EFFECTIVITY_DATE <= TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') and (INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))
   
      OR
   
      /* Component that became effective after last extract date and before today's extract, for CURRENT Option*/
   
      INV.EFFECTIVITY_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')
   
      OR
   
      /* Component that become disabled after last extract date and before today's extract, for CURRENT and CURRENT-FUTURE Option*/
   
      INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')
   
      )
   
      OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')) 
   
   GROUP BY
   

   To:

      (INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') 
   
      OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))
   
   GROUP BY
   

9. Click Apply, and Validate the mapping and save your changes to the repository.

To configure the BOM explosion to the Current and Future option

1. In PowerCenter Designer, open the SDE_ORA<Ver>_adapter.

2. Open the SDE_ORA_BOMItemFact mapping.

3. Double-click the EXP_BOMPEXPL_EXPLODER_USEREXIT expression transformation to open the Edit Transformations dialog box, and click the Port tab to display the EXPLODE_OPTION port.

4. Change the value to 3, and click Apply.

5. Validate the mapping, and save your changes to the repository.

6. Go to Mapplet Designer, and open mplt_BC_ORA_BOMHeaderDimension.

7. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.

8. Modify the following default Where condition from:

   ((
   
   /* CURRENT valid component changed */
   
   INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') AND (INV.EFFECTIVITY_DATE <= TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') and (INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))
   
   OR
   
   /* Component that became effective after last extract date and before today's extract, for CURRENT Option*/
   
   INV.EFFECTIVITY_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')
   
   OR
   
   /* Component that become disabled after last extract date and before today's extract, for CURRENT and CURRENT-FUTURE Option*/
   
   INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')
   
   )
   
   OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))
   
   GROUP BY
   

   To:

   ((
   
   INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
   
   AND ((INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))
   
   OR
   
   INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')
   
   )
   
   OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))
   
    GROUP BY
   

9. Click Apply, and Validate the mapping and save your changes to the repository.

To configure the BOM type

1. In PowerCenter Designer, open the SDE_ORA<Ver>_adapter.

2. Open the mplt_BC_ORA_BOMHeaderDimension mapplet.

3. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.

4. Modify BOM_ITEM_TYPE section in Where statement.

   Change the number to your BOM type. For example, change the number to 3 for a Planning BOM type.

   Where INV.BOM_ITEM_TYPE = 3 AND
   M.BOM_ITEM_TYPE = 3 AND
   

   
   

   Note: You can also remove these two filters to extract all types of BOM.

   
   

5. Click Apply, and Validate the mapping and save your changes to the repository.

9.2.5.2.2 How to Configure the Left Bound and Right Bound Calculation Option

You can use the left bound and the right bound calculation to expedite some reporting requirements. For example, you can find the components in a subassembly within a finished product. Left bound and right bound are stored in the W_BOM_ITEM_F table for each BOM node, and they have one row of data in the W_BOM_ITEM_F table. The COMPUTE_BOUNDS stored procedure traverses the exploded BOM tree structure and calculates the left bound and right bound. By default, the COMPUTE_BOUNDS stored procedure is off and the W_BOM_ITEM_F.LEFT_BOUNDS and W_BOM_ITEM_F.RIGHT_BOUNDS columns are empty.

The figure below illustrates a sample BOM structure with the left bound and right bound values listed for each node. To find all the components under node B, you select the components with a top product key value of A, the left bound value is greater than 2, and the right bound value is less than 17.

Figure 9-1 Sample BOM Structure

You can use the following procedure to turn on the left bound and the right bound calculation and populate the W_BOM_ITEM_F.LEFT_BOUNDS and W_BOM_ITEM_F.RIGHT_BOUNDS columns.

To configure the left bound and right bound calculation option

1. In PowerCenter Designer, open the SDE_ORA<Ver>_adapter.

2. Open the SIL_BOMItemFact mapping.

3. Double-click the COMPUTE_BOUNDS stored procedure transformation to open the Edit Transformations dialog box, and click the Properties tab.

4. Change the value for Transformation Attributes Call Text to compute_bounds_ora11i(1).

5. Click Apply.

6. Validate the mapping, and save your changes to the repository.

9.2.5.2.3 How to Extract Particular Purchase Order Records

This configuration also applies to the Spend functional area.

You may not want to extract particular types of records from purchase orders in your source system. In these cases, you can modify the filter condition in the Source Qualifier of the mapplet. By default, the filter condition is set to PLANNED, BLANKET, or STANDARD. However, you can change this value to some conditional statement that only allows particular types of records to be extracted.

To extract particular types of purchase order records

1. In PowerCenter Designer, open the SDE_ORA115<Ver>_adapter.

2. Open the MPLT_BC_ORA_PURCHASEORDERFACT mapplet.

3. Double-click the Source Qualifier to open the Edit Transformations box, and select the Properties tab to display the SQL Query.

4. Double-click the value in the SQL Query to open the SQL Editor box and edit the statement.

5. Replace the prepackaged filter condition with the new filter statement that reflects your business needs.

6. Edit the WHERE clause of the statement.

7. Click Apply to save the changes, and click OK to exit.

8. Validate the expression, and save your changes to the repository.

9. Repeat Step 2 to Step 8 for the MPLT_BC_ORA_PURCHASESCHEDULELINESFACT mapplet.

9.2.5.3 Configuration Steps for Supply Chain For PeopleSoft

This section contains configuration steps that apply to PeopleSoft.

Not applicable to Oracle BI Applications Version 7.9.4.

9.2.5.4 Configuration Steps for Oracle Supply Chain For Universal

This section contains configuration steps that apply to Universal, and contains the following topics:

• Section 9.2.5.4.1, "About Configuring Supply Chain Analytics for Universal Source"

• Section 9.2.5.4.2, "How to Configure the Preferred Merchant Flag"

• Section 9.2.5.4.3, "How to Configure the Customer Billable Indicator"

• Section 9.2.5.4.4, "How to Configure the Receipts Indicator"

• Section 9.2.5.4.5, "How to Configure Expense Payment Types"

• Section 9.2.5.4.6, "How to Configure the Default Expense Distribution Percentage"

• Section 9.2.5.4.7, "How to Configure Lookup Dates for Currency Conversion"

9.2.5.4.1 About Configuring Supply Chain Analytics for Universal Source

Expenses has one fact table (W_EXPENSE_F) that supports metrics and reports for examining employee expenses. Several mappings populate these tables to complete extracts, loads and updates; you may configure these to suit your organization's business rules. The following sections discuss decisions you must make before you begin adapting individual PowerCenter objects, and provide specific configuration procedures for the universal source.

Universal Source Adapter mapplets extract data from a flat file interface to populate the Oracle Business Analytics Warehouse. In this phase of your project, you can configure the following:

• System Flags and Indicators. You may configure various system flags to indicate record rejection settings, as well as to indicate if your employees are using your preferred vendors, if you can forward expenses to your customers, and if receipts are available for expensed items.

• Currency and Payment Options. You may configure the date used to establish your exchange rates, determine if you allow expenses to be distributed across multiple cost centers, and define payment types in your data warehouse.

Before you begin, you must make the following decisions:

• Cash Advances. Cash advance records have a unique expense item number. If your system allows multiple cash advance records for one expense report, each of these advances must have their own identifiers.

• Violations. Many organizations capture violations of company expense policies at the item level (for example, the line item airfare exceeds $2000), cash advance level (for example, cash advance exceeds $500) and at the expense report level (for example, the report's total expenses exceed $5000). Currently the Oracle Business Analytics Warehouse stores item level violations within the corresponding item record, but the cash advance record stores both cash advance and report-level violations. Furthermore, each record has a VIOLATION_WID that can point to W_REASON_D, where violation details are stored. Depending on how you want your analytic system to perform, you must edit your universal business adapter file to reflect the violation counts and keys appropriately. For example:

  • If a requestor violates a cash advance policy, but there are no other violations at the report level, the VIOLATION_ID refers to the cash advance violation only. The violation count equals the cash advance violation counts.

  • If a requestor violates company policy with their expense report, but has not taken a cash advance, you must add a dummy record in the flat file for a cash advance and set the cash advance amount to zero, and enter the violation count as the total number of expense report violations. In this scenario, VIOLATION_ID refers to the expense report violation data only.

  • If a requestor violates a cash advance policy and an expense report policy, you must total the violation counts and enter them in your flat file record, and the VIOLATION_ID has no value. However, if your organization wants to prioritize the violations and have the VIOLATION_ID point to that which is most important, you may point it to the appropriate entry in W_REASON_D.

9.2.5.4.2 How to Configure the Preferred Merchant Flag

The Oracle Business Analytics Warehouse provides a preferred merchant flag to indicate whether the requestor used a preferred merchant for an expensed item. The flag can have only one value—Y (item acquired from a preferred merchant) or N (item acquired from a merchant not recorded). If you use custom logic to determine merchant status, you must include that logic in the expenses Source Adapter.

To configure the preferred merchant flag

1. In PowerCenter Designer, open the Configuration for Universal Source folder.

2. Open the SDE_Universal_ExpenseFact mapping.

3. Select the Expression transformation to open the Edit Transformations box and select the Port tab.

4. Select the Add Port icon to add the PREF_MERCHANT_FLAG port.

5. Enter preferred merchant flag logic.

6. Validate the mapplet and click OK to exit.

7. Save your changes to the repository.

9.2.5.4.3 How to Configure the Customer Billable Indicator

The Oracle Business Analytics Warehouse provides a customer billable indicator that registers whether an expense item is billed to a customer or paid by your organization. The flag can have only one value—Y (cost is passed to the customer) or N (cost is paid by your organization). If you use custom logic to determine customer billable status, you must include that logic in the expenses Source Adapter.

To configure the customer billable indicator

1. In PowerCenter Designer, open the Configuration for Universal Source folder.

2. Open the SDE_Universal_ExpenseFact mapping.

3. Select the Expression transformation to open the Edit Transformations box, and select the Port tab.

4. Add a port called CUST_BILLABLE_FLG = <insert your expression here>.

5. Validate the mapplet and click OK to exit.

6. Save your changes to the repository.

9.2.5.4.4 How to Configure the Receipts Indicator

The Oracle Business Analytics Warehouse provides a receipts indicator that registers whether requestors have submitted a receipt for a line item in their expense report. The flag can have only one value—Y (receipts are available) or N (receipts are not available). If you use custom logic to indicate receipt availability, you must include that logic in the expenses Source Adapter.

To configure the receipts indicator

1. In PowerCenter Designer, open the SDE_Universal_ExpenseFact mapping.

2. Open the MPLT_SAF_EXPENSES Source Adapter mapplet.

3. Select the Expression transformation to open the Edit Transformations box, and select the Port tab.

4. Add a port called RECEIPT_FLG = <insert your expression here>.

5. Validate the mapplet and click OK to exit.

6. Save your changes to the repository.

9.2.5.4.5 How to Configure Expense Payment Types

The Oracle Business Analytics Warehouse supports analysis on three types of payment—Reimbursable Expense (type E), expenses prepaid by your company (type P), and cash advance (type C). All of your organization's payment types must be mapped to one of these types described earlier; do this by modifying MPLT_SAF_EXPENSES.

To configure additional payment types

1. In PowerCenter Designer, open the SDE_Universal_ExpenseFact mapping.

2. Open the MPLT_SAF_EXPENSES Source Adapter mapplet.

3. Select the Expression transformation to open the Edit Transformations box, and select the Port tab to display the EXPENSE_PAY_TYPE_ID port.

4. Copy and paste this port, rename it EXPENSE_PAY_TYPE_ID.

5. Select O to make your new port an output port.

6. Add a decode logic in the expression to decode source-supplied values to the Oracle Business Analytics Warehouse payment type of your choice.

7. Validate your mapping, and save your changes to the repository.

9.2.5.4.6 How to Configure the Default Expense Distribution Percentage

At times, employee expenses may be distributed across multiple cost centers. For example, technical support associates frequently travel to work in an office with many cost centers; their expenses could be split between those who used their services. This cost center distribution is expected as a percentage from the source system or file; if it is not present a null value is returned. However, this prevents further calculations, so it is preferable to configure the default to be 100% if only one cost center is charged, rather than allow the system to return a null value.

To configure the default expense distribution percentage

1. In PowerCenter Designer, open the Configuration for Universal Source folder.

2. Open the SDE_Universal_ExpenseFact mapping.

3. Select the Expression transformation to open the Edit Transformations box and select the Port tab.

4. Add a port named DIST_PERCENTAGE = <expression that sets this to 100%>.

5. Validate your mapplet.

6. Save your changes.

9.2.5.4.7 How to Configure Lookup Dates for Currency Conversion

The Oracle Business Analytics Warehouse supports conversion of currency to document (transactional, or source, currency) and group (corporate umbrella currency) for exchange rates. The Oracle Business Analytics Warehouse uses a specific lookup date to determine the rate of exchange on the date an expense was incurred (ACTUAL_EXP_DT). If you decide to use a different date as your currency conversion lookup, you must use the following procedure.

To configure the exchange rate lookup date

1. In PowerCenter Designer, open the Configuration for Universal Source folder.

2. Open the SDE_Universal_ExpenseFact mapping.

3. Select the Expression transformation to open the Edit Transformations box, and select the Port tab to display the EXT_XRATE_LKP_DATE port.

   The expression contains the exchange rate lookup date logic.

4. Select the expression in the EXT_XRATE_LOOKUP_DATE port to open the Expression Editor box and edit the expression.

5. Edit the lookup date logic by substituting your lookup date for the prepackaged expression.

6. Validate the mapplet and click OK to exit.

7. Save your changes.