| Oracle® Business Intelligence Applications Fusion Edition Installation and Configuration Guide Release 7.9.5 Part Number E12083-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Fusion Edition Installation and Configuration Guide Release 7.9.5 Part Number E12083-01 |
|
|
View PDF |
This chapter describes how to configure Oracle Procurement and Spend Analytics for particular sources to meet your business needs.
It contains the following topics:
Section 8.1, "Overview of Oracle Procurement and Spend Analytics"
Section 8.2, "Configuration Required Before A Full Load for Oracle Procurement and Spend Analytics"
Oracle Procurement and Spend Analytics comprises the following:
Oracle Procurement and Spend Analytics (for more information, see Section 8.1.1, "Oracle Procurement and Spend Analytics Module").
Oracle Supplier Performance Analytics (for more information, see Section 8.1.2, "Supplier Performance Analytics Module").
Oracle Procurement and Spend Analytics enable organizations to optimize their supply chain performance by integrating data from across the enterprise supply chain and enabling executives, managers, and frontline employees to make more informed and actionable decisions. Organizations using Oracle Procurement and Spend Analytics benefit from increased visibility into the complete Procurement and Spend process, including comprehensive supplier performance analysis and supplier payables analysis. Through complete end-to-end insight into the factors that impact Procurement and Spend performance, organizations can significantly reduce costs, enhance profitability, increase customer satisfaction, and gain competitive advantage. Oracle Procurement and Spend Analytics also integrate with the other applications in Oracle Business Intelligence Applications product line. They deliver this insight across the organization to increase the company's effectiveness in managing its customers, suppliers, and financial decisions.
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 Oracle 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.
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.
This section contains configuration steps that you need to perform on Oracle Procurement and Spend Analytics before you do a full data load, and contains the following topics:
Section 8.2.2, "Configuration Steps for Oracle Procurement and Spend Analytics For Oracle EBS"
Section 8.2.3, "Configuration Steps for Oracle Procurement and Spend Analytics For PeopleSoft"
Section 8.2.4, "Configuration Steps for Oracle Procurement and Spend Analytics For Universal"
Section 8.2.5, "Configuration Steps for Controlling Your Data Set"
This section contains configuration steps that apply to all source systems before you do a full data load, and contains the following topics:
Note:
For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, Oracle Sales Analytics, see Chapter 7, "Configuring Common Areas and Dimensions").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 Business Intelligence 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
Navigate to the OracleBI\dwrep\Informatica\Stored_Procedure_Scripts folder. copy the source codes Compute_Bounds_Ora11i.sql into the target data warehouse schema.
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.This section contains configuration steps that apply to Oracle EBS before you do a full data load, and contains the following topics:
Section 8.2.2.1, "How to Configure the DAC parameter for Purchase Cycle Line"
Section 8.2.2.2, "Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics"
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
In the DAC Client, display the Design view, then display the Tasks tab, and query for task SIL_PurchaseCycleLinesFact.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Display the Parameters tab in the lower pane. 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
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.
If you have modify or extend a seeded list of values, you must configure the CSV files for Oracle Procurement and Spend 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.
The table below lists the CSV worksheet files and the domain values for Oracle Procurement and Spend Analytics in the $pmserver\LkpFiles folder.
Table 8-1 Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics
| Worksheet File Name | Description | Session |
|---|---|---|
|
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 8.2.2.2.2, "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 8.2.2.2.9, "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 8.2.2.2.3, "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 8.2.2.2.4, "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 8.2.2.2.8, "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 8.2.2.2.7, "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 8.2.2.2.5, "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 8.2.2.2.6, "To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv". |
SDE_ORA_TransactionTypeDimension_PurchaseRequest |
This section explains how to configure domainValues_PaymentType_ora12.csv. This file is only applicable to Oracle R12.
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'
From the $pmserver\lkpfiles folder, open the domainValues_PaymentType_ora12.csv file in a text editor.
Copy the LOOKUP_CODE to the XACT_TYPE_CODE column in the file.
The data must be copied starting from the 2nd line.
Map each LOOKUP_CODE to one PO Payment Type (PO_PAYMENT_TYPE) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Status_Purch_Approve_ora11i.csv.
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'
From the domainValues_Status_Purch_Approve_ora11i.csv file in a text editor.
Copy the LOOKUP_CODE to the STATUS_CODE column in the file.
The data must be copied starting from the 8th line.
Map each LOOKUP_CODE to one Purchase Approval (PURCH_APPROVAL) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Status_Purch_Cycle_ora11i.csv.
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'
From the $pmserver\lkpfiles folder, open domainValues_Status_Purch_Cycle_ora11i.csv file in a text editor.
Copy the LOOKUP_CODE to the STATUS_CODE column in the file.
The data must be copied starting from the 8th line.
Map each LOOKUP_CODE to one Purchase Cycle (PURCH_CYCLE) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Xact_Types_Purch_Orders_ora11i.csv.
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;
From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Purch_Orders_ora11i.csv file in a text editor.
Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.
The data must be copied starting from the 8th line.
Map each DOCUMENT_SUBTYPE to one Purchase Order transaction type (PURCH_ORDERS) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv.
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'
From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Purch_Requisitions_ora11i.csv file in a text editor.
Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.
The data must be copied starting from the 7th line.
Map each DOCUMENT_SUBTYPE to one Purchase Requisition Type (PURCH_RQLNS) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv.
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
From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_PO_Line_Type_ora11i.csv file in a text editor.
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.
Map each ORDER_TYPE_LOOKUP_CODE to one PO Line Type transaction type (PO_LINE_TYPE) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv.
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
From the $pmserver\lkpfiles folder, open domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv file in a text editor.
Copy the PURCHASE_BASIS to the XACT_TYPE_CODE1 column in the file.
The data must be copied starting from the 8th line.
Map each PURCHASE_BASIS to one PO Line Type Purchase Basis Type (PO_LINE_TYPE) domain value.
Use commas to separate the entries.
Save and close the file.
This section explains how to configure domainValues_ShipmentType_ora12.csv. This file is only applicable to Oracle R12.
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')
From the $pmserver\lkpfiles folder, open domainValues_PaymentType_ora12.csv file in a text editor.
Copy the LOOKUP_CODE to the XACT_TYPE_CODE column in the file. The data must be copied starting from the 2nd line.
Map each LOOKUP_CODE to one PO Shipment Type (PO_SHIPMENT_TYPE) domain value. Use commas to separate the entries.
Save and close the file.
This section contains configuration steps that apply to PeopleSoft before you do a full data load.
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
This section contains configuration steps that apply to Universal before you do a full data load.
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
This section contains additional configuration steps for Oracle Procurement and Spend Analytics.
Section 8.2.5.2, "Configuration Steps for Oracle Procurement and Spend Analytics For Oracle EBS"
Section 8.2.5.3, "Configuration Steps for Oracle Procurement and Spend Analytics For PeopleSoft"
Section 8.2.5.4, "Configuration Steps for Oracle Procurement and Spend Analytics For Universal"
This section contains configuration steps that apply to all source systems, and contains the following topics:
Section 8.2.5.1.1, "About Configuring the Purchase Receipts Aggregate Table"
Section 8.2.5.1.2, "How to Configure the Purchase Receipts Aggregate Table"
Section 8.2.5.1.3, "About Configuring the Purchase Cycle Lines Aggregate Table"
Section 8.2.5.1.4, "How to Configure the Purchase Cycle Lines 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:
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.
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.
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.
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.
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 Purchase Receipts Aggregate Table
In the DAC Client, display the Design view.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Select the appropriate container for your OLTP source system.
Display the Tasks tab.
For each of the following tasks, display the Parameters tab and create the specified parameter name and parameter value
SIL_PurchaseReceiptAggregate_Derive_PreLoadImage $$TIME_GRAIN 'MONTH'
SIL_PurchaseReceiptAggregate_Derive_PreSoftDeleteImage $$TIME_GRAIN 'MONTH'
PLP_PurchaseReceiptAggregate_Derive_PostLoadImage $$TIME_GRAIN 'MONTH'
PLP_PurchaseReceiptAggregate_Load_Full $$GRAIN 'MONTH'
To configure the Purchase Receipts aggregate table for Universal Source
Open the parameterfileDW.txt file using a text editor in the OracleBI\DAC\Informatica\parameters\input folder.
Replace the default parameter values with your new values.
Save and close the file.
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:
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.
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.
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.
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.
Before you load the Purchase Cycle Lines aggregate table (W_PURCH_CYCLE_LINE_A), you need to configure the post-load-processing parameter run the initial workflow and then the incremental workflow, as follows.
To configure the Purchase Cycle Lines Aggregate Table
In the DAC Client, display the Design view.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Select the appropriate container for your OLTP source system.
Display the Tasks tab.
For each of the following tasks, display the Parameters tab and create the specified parameter name and parameter value
SIL_PurchaseCycleLinesAggregate_Derive_PreLoadImage $$TIME_GRAIN 'MONTH'
SIL_PurchaseCycleLinesAggregate_Derive_PreSoftDeleteImage $$TIME_GRAIN 'MONTH'
PLP_PurchaseCycleLinesAggregate_Derive_PostLoadImage $$TIME_GRAIN 'MONTH'
PLP_PurchaseCycleLinesAggregate_Load_Full $$GRAIN 'MONTH'
This section contains configuration steps that apply to Oracle EBS, and contains the following topics:
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
In Informatica PowerCenter Designer, open the SDE_ORA115<Ver>_Adaptor.
Open the MPLT_BC_ORA_PURCHASEORDERFACT mapplet.
Double-click the Source Qualifier to open the Edit Transformations box, and select the Properties tab to display the SQL Query.
Double-click the value in the SQL Query to open the SQL Editor box and edit the statement.
Replace the prepackaged filter condition with the new filter statement that reflects your business needs.
Edit the WHERE clause of the statement.
Click Apply to save the changes, and click OK to exit.
Validate the expression, and save your changes to the repository.
Repeat Step 2 to Step 8 for the MPLT_BC_ORA_PURCHASESCHEDULELINESFACT mapplet.
This section contains configuration steps that apply to PeopleSoft.
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
This section contains configuration steps that apply to Universal, and contains the following topics:
Section 8.2.5.4.1, "About Configuring Oracle Procurement and Spend Analytics for Universal Source"
Section 8.2.5.4.2, "How to Configure the Preferred Merchant Flag"
Section 8.2.5.4.3, "How to Configure the Customer Billable Indicator"
Section 8.2.5.4.4, "How to Configure the Receipts Indicator"
Section 8.2.5.4.6, "How to Configure the Default Expense Distribution Percentage"
Section 8.2.5.4.7, "How to Configure Lookup Dates for Currency Conversion"
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.
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
In Informatica PowerCenter Designer, open the Configuration for Universal Source folder.
Open the SDE_Universal_ExpenseFact mapping.
Select the Expression transformation to open the Edit Transformations box and select the Port tab.
Select the Add Port icon to add the PREF_MERCHANT_FLAG port.
Enter preferred merchant flag logic.
Validate the mapplet and click OK to exit.
Save your changes to the repository.
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
In Informatica PowerCenter Designer, open the Configuration for Universal Source folder.
Open the SDE_Universal_ExpenseFact mapping.
Select the Expression transformation to open the Edit Transformations box, and select the Port tab.
Add a port called CUST_BILLABLE_FLG = <insert your expression here>.
Validate the mapplet and click OK to exit.
Save your changes to the repository.
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
In Informatica PowerCenter Designer, open the SDE_Universal_ExpenseFact mapping.
Open the MPLT_SAF_EXPENSES Source Adapter mapplet.
Select the Expression transformation to open the Edit Transformations box, and select the Port tab.
Add a port called RECEIPT_FLG = <insert your expression here>.
Validate the mapplet and click OK to exit.
Save your changes to the repository.
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
In Informatica PowerCenter Designer, open the SDE_Universal_ExpenseFact mapping.
Open the MPLT_SAF_EXPENSES Source Adapter mapplet.
Select the Expression transformation to open the Edit Transformations box, and select the Port tab to display the EXPENSE_PAY_TYPE_ID port.
Copy and paste this port, rename it EXPENSE_PAY_TYPE_ID.
Select O to make your new port an output port.
Add a decode logic in the expression to decode source-supplied values to the Oracle Business Analytics Warehouse payment type of your choice.
Validate your mapping, and save your changes to the repository.
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
In Informatica PowerCenter Designer, open the Configuration for Universal Source folder.
Open the SDE_Universal_ExpenseFact mapping.
Select the Expression transformation to open the Edit Transformations box and select the Port tab.
Add a port named DIST_PERCENTAGE = <expression that sets this to 100%>.
Validate your mapplet.
Save your changes.
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
In Informatica PowerCenter Designer, open the Configuration for Universal Source folder.
Open the SDE_Universal_ExpenseFact mapping.
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.
Select the expression in the EXT_XRATE_LOOKUP_DATE port to open the Expression Editor box and edit the expression.
Edit the lookup date logic by substituting your lookup date for the prepackaged expression.
Validate the mapplet and click OK to exit.
Save your changes.
