4 Configuring Oracle Procurement and Spend Analytics

This section describes how to configure Oracle Procurement and Spend Analytics. It contains the following topics:

• Section 4.1, "Overview of Oracle Procurement and Spend Analytics"

• Section 4.2, "Configuration Required Before a Full Load for Oracle Procurement and Spend Analytics"

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

4.1 Overview of Oracle Procurement and Spend Analytics

Oracle Procurement and Spend Analytics comprises Oracle Procurement and Spend Analytics and Oracle Supplier Performance Analytics.

Oracle Procurement and Spend Analytics enables 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 spend and procurement processes, including comprehensive supplier performance analysis, supplier payables analysis and employee expenses 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 integrates with the other applications in the Oracle Business Intelligence Applications product line, such as Oracle Financial Analytics. They deliver this insight across the organization to increase the company's effectiveness in managing its customers, suppliers, and financial decisions.

Oracle Procurement and Spend Analytics provides visibility into direct and indirect spending across the enterprise, payment, and employee expenses. Oracle Procurement and Spend Analytics comprises the following subject areas:

• Procure to Pay: This is a summary subject area that provides the ability to do comparative analysis and report on requested spend, committed spend and actual spend and receipts across business units, buying locations, suppliers, products, item categories 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 combines the information from Purchase Order Costs and Purchase Schedules with the ability to report on committed spend, contract compliance and Purchase orders of the suppliers of an organization across suppliers, company, products, item categories and associated hierarchies at purchase order line level.

• Purchase Cycle Lines: This is a summary subject area that provides the ability to report cycle time performance, such as requisition to purchase order lead time, purchase order to receipt lead time, P2P lead time of the suppliers of an organization.

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

• 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, item categories and associated hierarchies at purchase receipt line level, including reporting based on receiving time.

• Employee Expenses: 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, including Approvers and cycle time measurements related to Approval, and Employee Expenses by various expense types. This subject area is only applicable for Oracle EBS 11.5.10 (family Pack M) and R12, and the Universal adapter. This subject area is not populated for PeopleSoft sources in Version 7.9.6.

  Note: For Oracle Employee Expenses (OIE), Version 7.9.6 support comes in two different adapters. SDE_ORA11510_Adaptor supports Oracle EBS release 11i.FIN_PF.F/11i.OIE.I minipack and SDE_ORAR12_Adaptor supports Oracle EBS release 11i.FIN_PF.G/11i.OIE.J minipack and R12 release.

• Invoice Lines: This is a detailed subject area that provides the ability to report on total spend of an organization across suppliers, products, item categories, business units, cost centers, buying locations, supplier locations and associated hierarchy. In addition, this subject area also provides detailed information at invoice distribution 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. This subject area is only populated by the Universal adapter.

Oracle Procurement and Spend Analytics provides the following dashboards:

• Spend Analyzer - enables organizations in getting complete visibility into the spend patterns of the company by consolidating the data into a single source of truth. By gaining insight, business users are empowered in identifying saving opportunities and meeting organizational goals. Spend Analyzer dashboards provides different views such as Spend by Supplier, Spend by Category, Spend by Organization with drill downs to identify the savings potential at item level.

• Procurement Performance - a re-designed dashboard that provides insight into the procure to pay process, including current status and procurement effectiveness that helps organizations to monitor the procurement performance on regular basis, identify bottlenecks, and to pro-actively take corrective actions before they become issues.

• Supplier Performance - 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 analysis of payments due and overdue.

  The Supplier Performance Analytics application comprises the following 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 enables organizations to maintain better relationships with their best suppliers.

    Note: In order to populate Supplier Payables component, you must implement the Accounts Payables module of Oracle Financial Analytics. If you do not implement the Accounts Payables module, then some of the Supplier Payables reports will not be populated. For more information about configuring Oracle Financial Analytics, see Chapter 5, "Configuring Oracle Financial Analytics".

• Employee Expenses - Enables organizations to have a complete picture of the Employee Expenses, including approval cycle times, expenses by expense type, expense reports by status. This dashboard provides different views such as Manager view, Cost center view and submissions. While Spend Analyzer provides high level spend at Invoice level, Employee expenses provides detailed analysis of how the money related to travel and expenses is being spent. This Dashboard is only applicable for Oracle EBS 11.5.10 (family Pack J) and R12, and Universal adapter. This Dashboard is not populated for PeopleSoft source in Version 7.9.6.

4.2 Configuration Required Before a Full Load for Oracle Procurement and Spend Analytics

This section contains configuration steps that you need to perform on Oracle Procurement and Spend Analytics before you do a full data load. It contains the following topics:

• Section 4.2.1, "Configuration Steps for Oracle Procurement and Spend Analytics for All Source Systems"

• Section 4.2.2, "Configuration Steps for Oracle Procurement and Spend Analytics for Oracle EBS"

• Section 4.2.3, "Configuration Steps for Oracle Procurement and Spend Analytics for PeopleSoft"

• Section 4.2.4, "Configuration Steps for Oracle Procurement and Spend Analytics for Universal"

4.2.1 Configuration Steps for Oracle Procurement and Spend Analytics for All Source Systems

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

Note:

For configuration steps that apply to all BI Applications modules see Chapter 3, "Configuring Common Areas and Dimensions."

4.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 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

1. Navigate to the OracleBI\dwrep\Informatica\Stored_Procedure_Scripts folder.

2. Copy the source code in Compute_Bounds_Ora11i.sql file into the target data warehouse schema.

3. 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.

4.2.2 Configuration Steps for Oracle Procurement and Spend Analytics for Oracle EBS

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

• Section 4.2.2.1, "How to Configure the DAC Parameter for Purchase Cycle Line"

• Section 4.2.2.2, "Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics"

• Section 4.2.2.3, "How to configure AP invoices types for Spend Analysis"

• Section 4.2.2.4, "How to configure Oracle Employee Expense Analytics for Oracle EBS 11.5.10"

4.2.2.1 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 need to distinguish data coming from Oracle 11i applications from data from other applications such as SAP and PeopleSoft.

To configure the DAC parameter for purchase cycle line

1. In DAC, go to the Design view, then click the Tasks tab, and query for task SIL_PurchaseCycleLinesFact.

2. Click 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.

3. Repeat the same for task SIL_PurchaseCycleLinesFact_Extract

For more information about DAC, see Oracle Business Intelligence Data Warehouse Administration Console Guide.

4.2.2.2 Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

If you have modified or extended 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 from the seeded values, you need to follow the instructions for configuring the domain values and CSV worksheet files in this section.

4.2.2.2.1 List of Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

Table 4-1 lists the domain values for Oracle Procurement and Spend Analytics in the $pmserver\LkpFiles folder (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles). The table also lists the CSV worksheet source files for Oracle Procurement and Spend Analytics in the $pmserver\SrcFiles folder.

Table 4-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 4.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 4.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 4.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 4.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 4.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 4.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 4.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 4.2.2.2.6, "To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv.".	SDE_ORA_TransactionTypeDimension_PurchaseRequest
domainValues_Xact_Types_PurchaseRequisition.csv	Used to identify whether the purchase order creation method is manual or auto. For information about how to edit this file, see Section 4.2.2.2.2, "To configure domainValues_PaymentType_ora12.csv." Note: See also the file domainValues_Xact_Types_PurchaseReceipt.csv, which is used to configure Receipt transaction types.	SDE_ORA_TransactionTypeDimension_PurchaseRequisition
domainValues_Employee_Expense_Type_ora11i.csv	Used to identify the expense transaction type, such as AIRFARE, ACCOMODATION, and so on. For information about how to edit this file, see Section 4.2.2.2.2, "To configure domainValues_PaymentType_ora12.csv."	SDE_ORA_EmployeeExpenseTypeDimension
file_xact_type_AgreementLeverageType.csv	Used to identify the agreement leverage types. For information about how to edit this file, see Section 4.2.2.2.11, "To configure domainValues_Employee_Expense_Type_ora11i.csv."	SDE_ORA_TransactionTypeDimension_AgreementLeverageType
domainValues_Xact_Sources_APSources_ora.csv	Used to identify whether an AP invoice source is considered manual or electronic. For information about how to edit this file, see Section 4.2.2.2.13, "To configure domainValues_Xact_Sources_APSources_ora.csv."	SDE_ORA_TransactionSourceDimension_AP_CC_Extract, SDE_ORA_TransactionSourceDimension_AP_LKP_Extract
domainValues_Employee_Expense_Status.csv	Used to configure the Employee Expense Approval Status and the corresponding domain values for the Oracle application. For information about how to edit this file, see Section 4.2.2.2.14, "To configure domainValues_Employee_Expense_Status.csv."	SDE_ORA_EmployeeExpenseStatusDimension
file_employee_expense_payment_method.csv	Lists the Employee Expenses Payment Method codes and the corresponding domain values for the Oracle application. This file contains both source values and domain values for the Employee Expenses Payment Method. The values of CASH and CREDIT_CARD have been defined for the Cash Payment and Payment through Credit Card. No configuration should be required for this file.	SDE_ORA_EmployeeExpensePaymentMethodDimension
file_Expense_pay_type.csv	Lists the Employee Expenses type codes and the corresponding domain values for the Oracle application. This file contains both source value and domain value for the Employee Expenses Payment Type. The value 'E' has been defined for the Actual Expense Incurred By The Requestor. No configuration should be required for this file.	SDE_ORA_TransactionTypeDimension_Expense_Pay_Type

4.2.2.2.2 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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 second 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.

4.2.2.2.3 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 $pmserver\LkpFiles folder (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), open 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.

4.2.2.2.4 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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.

4.2.2.2.5 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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.

4.2.2.2.6 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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.

4.2.2.2.7 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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.

4.2.2.2.8 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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.

4.2.2.2.9 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 (for example, \PowerCenter8.6.0\server\infa_shared\LkpFiles), 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 second 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.

4.2.2.2.10 To configure domainValues_Xact_Types_PurchaseRequisition.csv

This section describes how to configure domainValues_Xact_Types_PurchaseRequisition.csv.

1. Identify the different PO creation methods in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT FND_LOOKUP_VALUES.LOOKUP_CODE FROM FND_LOOKUP_VALUES

   WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE = 'PO_DOCUMENT_CREATION_METHOD' AND

   FND_LOOKUP_VALUES.LANGUAGE = 'US'

   Note: The language code needs to be set for non-English deployments.

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

3. Copy the columns LOOKUP_CODE to XACT_TYPE_CODE starting from the second line of data.

4. Map each LOOKUP_CODE to either the MANUAL or AUTO domain value. Use commas to separate the entries.

5. Save and close the file.

4.2.2.2.11 To configure domainValues_Employee_Expense_Type_ora11i.csv

This section describes how to configure domainValues_Employee_Expense_Type_ora11i.csv.

1. Identify the different transaction types of purchase receipts in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT FND.LOOKUP_CODE FROM FND_LOOKUP_VALUES FND

   WHERE LOOKUP_TYPE = 'OIE_EXPENSE_CATEGORY' AND

   LANGUAGE = USERENV('LANG')

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

3. Copy the columns LOOKUP_CODE to XACT_TYPE_CODE starting from the second line of data.

4. Map each LOOKUP_CODE to one of the respective EXPENSE_TYPE domain values. Use commas to separate the entries.

5. Save and close the file.

4.2.2.2.12 To configure file_xact_type_AgreementLeverageType.csv

This section describes how to configure file_xact_type_AgreementLeverageType.csv.

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

2. Configure the value of W_XACT_TYPE_CODE1.

   XACT_TYPE_CODE and XACT_TYPE _DESC list the seven cases of business scenarios. Scenarios one through six use some kind of negotiation. Scenario seven does not use negotiation.

   XACT_TYPE_CODE	XACT_TYPE_DESC
   1. Purchase releases	Orders or releases against local blanket agreement, global blanket agreement or planned orders.
   2. Punch-out and catalog purchase	Purchases through the catalog or through a "punch-out" from the catalog to an external supplier site.
   3. Contract reference	Orders against a contract agreement.
   4. Sourcing document reference	Orders against a sourcing document.
   5. Complex work	Complex work procurement.
   6. Other negotiated	Other negotiated purchases as indicated by the buyer.
   7. Non-negotiated	Non-agreement purchases without existing matching blanket purchase agreement.

   
   

3. In the preconfigured application, scenarios one through six are considered to be agreement purchases (W_XACT_TYPE_CODE1='Agreement'). If your business rule does not consider one of these scenarios as agreement purchases, you can change the value to 'Non-Agreement'.

4.2.2.2.13 To configure domainValues_Xact_Sources_APSources_ora.csv

This section describes how to configure domainValues_Xact_Sources_APSources_ora.csv.

Note:

Oracle Procurement and Spend Analytics and Oracle Financial Analytics share the same configuration file.

1. Identify the different transaction types of payable transaction sources in your Oracle EBS source system by using the following SQL:

   SELECT DISTINCT UPPER(SOURCE) FROM AP_INVOICES_ALL
   

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

3. Copy the results of the query in step 1 to the SOURCE_NAME column in the file, starting from the second line of data.

4. Map each source name to the respective domain values.

   Map each source name to the respective domain values. There are two domain values for each source. The first domain value specifies whether the source is a CREDIT_CARD application. Values are CREDIT_CARD or OTHER. The second domain value specifies whether the application is for MANUAL or ELECTRONIC entry. Values are MANUAL or ELECTRONIC.

5. Save and close the file.

4.2.2.2.14 To configure domainValues_Employee_Expense_Status.csv

This section describes how to configure domainValues_Employee_Expense_Status.csv.

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

   SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE= 'EXPENSE REPORT STATUS' AND FND_LOOKUP_VALUES.LANGUAGE = USERENV('LANG')

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

3. Copy the columns LOOKUP_CODE to STATUS_CODE.

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

4. Map each LOOKUP_CODE to one W_STATUS_CODE domain value.

   Use commas to separate the entries.

5. Save and close the file.

4.2.2.3 How to configure AP invoices types for Spend Analysis

The configuration file for AP invoice types for Spend Analysis is file_ap_invoice_config_spend_ora12.csv in $pmserver\SrcFiles folder. This file lists the AP invoice types and corresponding designation on whether to include in the spend calculation. The lists show all types for Oracle EBS R12, which are a superset of oracle EBS 11i. If an invoice type is not listed, it will not be included in spend calculation. Prepayment and AWT invoice types are not included in spend calculation, which is not configurable.

To configure file_ap_invoice_config_spend_ora12.csv:

1. From the $pmserver\SrcFiles folder (for example, \PowerCenter8.6.0\server\infa_shared\SrcFiles), open file_ap_invoice_config_spend_ora12.csv file in a text editor.

2. Update the Y/N flag to designate whether to include invoice types in spend calculations.

3. Save and close the file.

4.2.2.4 How to configure Oracle Employee Expense Analytics for Oracle EBS 11.5.10

If you want to implement Oracle Employee Expense Analytics with Oracle EBS 11.5.10, you need to have at least 11i.FIN_PF.F/11i.OIE.I family pack and above patch set level. In addition, if your 11.5.10 patch set level is on or above 11i.FIN_PF.G/11i.OIE.J family pack, then you need to follow these configuration steps.

To configure Oracle Employee Expenses for Oracle EBS 11.5.10:

1. In DAC, go to the Design view, display the Tasks tab, and query for task SDE_ORA_EmployeeExpenseFact.

2. Display the Edit tab, and change the value for 'Command for Incremental Load' from SDE_ORA_EmployeeExpenseFact to SDE_ORA_EmployeeExpenseFact_FP.

3. On the Edit tab, change the value for 'Command for Full Load' from SDE_ORA_EmployeeExpenseFact_Full to SDE_ORA_EmployeeExpenseFact_FP_Full.

4. Save the changes.

5. Display the Subject Areas tab, and query for the 'Employee Expenses' subject area.

6. Select the queried record for 'Employee Expenses', and click Assemble to assemble the Subject Area.

7. Go to Execute view, select your execution plan that contains the 'Employee Expenses' subject area, then click 'Build' to build the Execution Plan.

   The Execution Plan is now ready to Execute.

4.2.3 Configuration Steps for Oracle Procurement and Spend Analytics for PeopleSoft

This section contains configuration steps that apply to PeopleSoft before you do a full data load. It contains the following topics:

• Section 4.2.3.1, "Domain Values and CSV Worksheet Files for PeopleSoft Procurement and Spend Analytics"

• Section 4.2.3.2, "CSV Worksheet Source Files for PeopleSoft Procurement and Spend Analytics"

4.2.3.1 Domain Values and CSV Worksheet Files for PeopleSoft Procurement and Spend Analytics

If you have to modify or extend a seeded list of values, you must configure the CSV files for PeopleSoft 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. Compare the lists of values with the seeded values and if the lists of values are different than the seeded values, you need to follow the instructions to configure the Domain Values and CSV Worksheet Files.

Note:

Some sessions may fail if these procedures are not compiled in the database before running the workflows. If you have problems deploying the stored procedures, see your database reference guide, or contact your database administrator.

Table 4-2 lists the domain value csv files for PeopleSoft Procurement and Spend Analytics in the $pmserver\LkpFiles folder.

Table 4-2 Domain Values Files for PeopleSoft Procurement and Spend Analytics

Worksheet File Name	Description	Session
domainValues_Status_Purch_Approve_psft.csv	Lists the Purchasing Approval Status column and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.1, "To configure domainValues_Status_Purch_Approve_psft.csv."	SDE_PSFT_StatusDimension_PurchApprove
domainValues_Status_Purch_Cycle_psft.csv	Lists the Purchasing Cycle Status column and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.2, "To configure domainValues_Status_Purch_Cycle_psft.csv."	SDE_PSFT_StatusDimension_PurchCycle
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchasing Line Type and the corresponding domain Values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.3, "To configure domainValues_Xact_Types_Procurement_psft.csv - PO Line Type."	SDE_PSFT_TransactionTypeDimension_PO_Line_Type
domainValues_Xact_Types_Procurement_psft.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 4.2.3.1.4, "To configure domainValues_Xact_Types_Procurement_psft.csv - Purchase Order."	SDE_PSFT_TransactionTypeDimension_PurchaseOrder
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Requisition Transaction Type column and the corresponding domain values for the Oracle EBS application. The Purchase Requisition in PeopleSoft does not have a type defined. Two records have been added to the domain values to designate whether the requisition is on hold. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_PurchaseRequestion
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Receipt Transaction Type column and the corresponding domain values for the Oracle EBS application. The Purchase Receipt in PeopleSoft does not have a type defined. Two records have been added to the domain values file to designate whether the Receipt is a receipt or is a Return to Vendor. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_PurchaseReceipt
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Order Consigned Code Transaction Type column and the corresponding domain values for the Oracle EBS application. The Consigned Code in PeopleSoft does not have a type defined. Two records have been added to the domain values file to designate whether consigned or not, Y/N. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_Consigned_Code
domainValues_Status_APInvoice_psft.csv	Lists the AP Invoice Approval Status column and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.5, "To configure domainValues_Status_APInvoice_psft.csv - Approval Status."	SDE_PSFT_StatusDimension_APInvoiceApproval
domainValues_Xact_Source_AP_Invoice_psft.csv	Lists the AP Invoice Transaction Source column and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.6, "To configure domainValues_Xact_Source_AP_Invoice_psft.csv."	SDE-PSFT_TransactionSourceDimension_APInvoice
domainValues_Xact_Types_APInvoice_DistributionTypes_psft.csv	Lists the AP Invoice Distribution Type Transaction Type column and the corresponding domain values for the Oracle EBS application. Distribution types of Freight, AWT, Item, Miscellaneous, Tax, and Prepay have been added to the domain values file. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_APInvoiceDistributionType

4.2.3.1.1 To configure domainValues_Status_Purch_Approve_psft.csv

This section explains how to configure domainValues_Status_Purch_Approve_psft.csv.

1. Identify the Purchase Approval Status for Purchase Orders and Purchase Requisitions in your PeopleSoft source system by using the following SQL:

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME IN('PO_STATUS','CURR_STATUS')
   ORDER BY 1
   

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

3. Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 9th line.

4. Map each FIELDVALUE to one Purchase Approval (PURCH_APPROVAL) domain value. Use commas to separate the entries.

5. Save and close the file.

4.2.3.1.2 To configure domainValues_Status_Purch_Cycle_psft.csv

This section explains how to configure domainValues_Status_Purch_Cycle_psft.csv.

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

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME IN('PO_STATUS','CURR_STATUS')
   ORDER BY 1
   

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

3. Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 9th line.

4. Map each FIELDVALUE to one Purchase Cycle (PURCH_CYCLE) domain value. Use commas to separate the entries.

5. Save and close the file.

4.2.3.1.3 To configure domainValues_Xact_Types_Procurement_psft.csv - PO Line Type

This section explains how to configure domainValues_Xact_Types_Procurement _psft.csv for the PO Line Type transaction type.

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

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME =('POAMOUNT_YN')
   ORDER BY 1
   

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

3. Copy the FIELDVALUE to the XACT_SUBTYPE_CODE column in the file. The data must be copied starting from the 11th line.

4. Map each FIELDVALUE to one PO Line Type transaction type (PO_LINE_TYPE) domain value. Use commas to separate the entries.

5. Save and close the file.

4.2.3.1.4 To configure domainValues_Xact_Types_Procurement_psft.csv - Purchase Order

This section explains how to configure domainValues_Xact_Types_Procurment_psft.csv for the Purchase Order transaction type.

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

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME =('PO_TYPE')
   ORDER BY 1
   

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

3. Copy the FIELDVALUE to the XACT_SUBTYPE_CODE column in the file. The data must be copied starting from the 11th line.

4. Map each FIELDVALUE to one Purchase Order transaction type (PURCH_ORDERS) domain value. Use commas to separate the entries.

5. The delivered domain values file has values for GEN, defined as STANDARD, and KAN, defined as PLANNED, purchase order types and an additional type for BLANKET.

6. Save and close the file.

4.2.3.1.5 To configure domainValues_Status_APInvoice_psft.csv - Approval Status

This section explains how to configure domainValues_Status_APInvoice_psft.csv.

1. Identify the AP Invoice Approval Status in your PeopleSoft source system by using the following SQL:

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME =('APPR_STATUS')
   ORDER BY 1
   

   In addition, the domain values file contains an entry for a cancelled status. This entry is defined as 'X' which is an 'ENTRY_STATUS' value.

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

3. Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 10th line.

4. Map each FIELDVALUE to one Approval (APPROVAL) domain value. Use commas to separate the entries.

5. Save and close the file.

4.2.3.1.6 To configure domainValues_Xact_Source_AP_Invoice_psft.csv

This section explains how to configure domainValues_Xact_Types_Procurement_psft.csv for the AP Invoice transaction sources.

1. Identify the AP Invoice source type in your PeopleSoft source system by using the following SQL:

   SELECT A.FIELDVALUE, A.XLATLONGNAME
   FROM PSXLATITEM A
   WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
   C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
   AND A.FIELDNAME IN('VCHR_SRC')
   ORDER BY 1
   

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

3. Copy the FIELDVALUE to the SOURCE_CODE column in the file. The data must be copied starting from the 10th line.

4. Map each FIELDVALUE to one AP Invoice domain value. Use commas to separate the entries.

5. There are two domain values for each source. The first domain value specifies if the source is a CREDIT_CARD application. Values are CREDIT_CARD or OTHER. The second domain value specifies whether the application is for MANUAL or ELECTRONIC entry. Values are MANUAL or ELECTRONIC.

6. Save and close the file.

4.2.3.2 CSV Worksheet Source Files for PeopleSoft Procurement and Spend Analytics

Table 4-3 lists the CSV worksheet source files for PeopleSoft Procurement and Spend Analytics in the $pmserver\SrcFiles folder.

Table 4-3 CSV Worksheet Source Files for PeopleSoft Procurement and Spend Analytics

Worksheet File Name	Description	Session
File_StatusDimension_PurchaseRequisition_psft.csv	Lists the Purchasing Requisition Status column and the corresponding domain values for the PeopleSoft application. This file contains both source values and domain values for the cycle status of the Purchase Requisition. The values of UNAPPROVED, UNPROCESSED, UNFULFILLED, FULFILLED, and NOT APPLICABLE have been defined. No configuration should be needed for this file.	SDE_PSFT_StatusDimension_PurchaseRequisition
File_TransactionTypeDimension_PO_CreationMethod_psft.csv	Lists the Purchase Order Creation Method transaction type column and the corresponding domain values for the PeopleSoft application. This file contains both source values and domain values for the purchase order creation method. The values of BATCH and ONLINE for one domain value have been defined. For the other domain value the values of AUTO and MANUAL have been defined. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_PO_Creation_Method
file_purchasing_transaction_source_psft.csv	Lists the Purchase Order Transaction Source column and the corresponding domain values for the PeopleSoft application. This file contains both source values and domain values for the purchase order receipt source. The value of PO with a domain value of PO for RECEIVING type has been defined. No configuration should be needed for this file.	SDE_PSFT_TransactionSourceDimension_Purchasing
file_ap_invoice_config_spend_voucher_psft.csv	Lists the AP Invoice document types and the corresponding designation on whether to include in spend calculations for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.2.1, "To configure file_ap_invoice_config_spend_voucher_psft.csv."	SDE_PSFT_APInvoiceLineFact
file_ap_invoice_spendtypes_psft.csv	Lists the AP Invoice distribution types and the corresponding domain values for the spend types for the PeopleSoft application. This file contains both source values and domain values for the AP Invoice distribution spend types. The values of PO MATCHED, PAYABLES LEAKAGE, and PO NOT REQUIRED have been defined for the distribution types of ITEM, TAX, FREIGHT, and MISCELLANEOUS. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_APInvoiceSpendType
PROCUREMENT_PO_CONSIGNED_CODE_PSFT.csv	Lists the Purchase Order Consigned Code values for the PeopleSoft application. This file defines the purchase order consigned code source value for the transaction type and the code dimensions. The values of Y - Consigned and N-Regular have been defined. No configuration should be needed for this file.	SDE_PSFT_TransactionTypeDimension_Consigned_CodeandSDE_PSFT_CodeDimension_Supply_Chain_Purchasing_Consigned_Code

4.2.3.2.1 To configure file_ap_invoice_config_spend_voucher_psft.csv

This section explains how to configure file_ap_invoice_config_spend_voucher_psft.csv for the AP Invoice spend calculation.

This file defines AP Invoice types and has a flag to indicate whether the type should be included in spend calculations.

1. From the $pmserver\SrcFiles folder, open file_ap_invoice_config_spend_voucher_psft.csv file in a text editor.

2. Update the Y/N flag to designate whether to include the type in spend calculations.

3. Save and close the file.

4.2.4 Configuration Steps for Oracle Procurement and Spend Analytics for Universal

Not applicable to Oracle BI Applications Version 7.9.6.

4.3 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle Procurement and Spend Analytics.

• Section 4.3.1, "Configuration Steps for Oracle Procurement and Spend Analytics for All Source Systems"

• Section 4.3.2, "Configuration Steps for Oracle Procurement and Spend Analytics for Oracle EBS"

• Section 4.3.3, "Configuration Steps for Oracle Procurement and Spend Analytics for PeopleSoft"

• Section 4.3.4, "Configuration Steps for Oracle Procurement and Spend Analytics for Universal"

4.3.1 Configuration Steps for Oracle Procurement and Spend Analytics for All Source Systems

This section contains configuration steps that apply to all source systems. It contains the following topics:

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

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

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

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

4.3.1.1 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.

4.3.1.2 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 Purchase Receipts Aggregate Table

1. In DAC, go to the Design view, and select your custom container from the drop-down list.

2. Click the Tasks tab.

3. For each of the following tasks, click the Parameters subtab 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

1. Using a text editor, open the file parameterfileDW.txt, located in OracleBI\DAC\Informatica\parameters\input.

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

3. Save and close the file.

4.3.1.3 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.

4.3.1.4 How to Configure the Purchase Cycle Lines Aggregate Table

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

1. In DAC, go to the Design view, and select your custom container from the drop-down list.

2. Click the Tasks tab.

3. For each of the following tasks, click the Parameters subtab 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'

4.3.2 Configuration Steps for Oracle Procurement and Spend Analytics for Oracle EBS

This section contains configuration steps that apply to Oracle EBS. It contains the following topics:

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

• Section 4.3.2.2, "How to Enable Project Analytics Integration with Procurement and Spend Subject Areas"

4.3.2.1 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 Informatica PowerCenter Designer, open the SDE_ORA115<Ver>_Adaptor.

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.

4.3.2.2 How to Enable Project Analytics Integration with Procurement and Spend Subject Areas

You can enable Oracle Procurement and Spend Analytics for EBS to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.

To enable the integration

1. In the DAC client, select the Oracle EBS source system container depending on the version of the OLTP that you are using.

2. Select the subject areas that you are deploying, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions tag.

3. Assemble the subject areas.

4. Click the Execute button and build the execution plan for the subject areas that you updated.

4.3.3 Configuration Steps for Oracle Procurement and Spend Analytics for PeopleSoft

This section contains configuration steps that apply to PeopleSoft. It contains the following topic:

• Section 4.3.3.1, "How to Enable Project Analytics Integration with Procurement and Spend Subject Areas"

4.3.3.1 How to Enable Project Analytics Integration with Procurement and Spend Subject Areas

You can enable Oracle Procurement and Spend Analytics for PeopleSoft to use dimensions in Oracle Project Analytics. You can only perform this configuration if you have licensed Oracle Project Analytics.

To enable the integration

1. In the DAC client, select the PeopleSoft source system container depending on the version of the OLTP that you are using.

2. Select the subject areas that you are deploying, and then in the Configuration Tags tab, clear the Inactive checkbox for the Enable Project Dimensions tag.

3. Assemble the subject areas.

4. Click the Execute button and build the execution plan for the subject areas that you updated.

4.3.4 Configuration Steps for Oracle Procurement and Spend Analytics for Universal

This section contains configuration steps that apply to Universal. It contains the following topics:

• Section 4.3.4.1, "About Configuring Oracle Procurement and Spend Analytics for Universal Source"

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

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

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

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

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

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

4.3.4.1 About Configuring Oracle Procurement and Spend 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.

4.3.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 Informatica 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.

4.3.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 Informatica 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.

4.3.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 Informatica PowerCenter Designer, open the SDE_Universal_ExpenseFact mapping.

2. Open the Exp_FILE_EXPENSE Expression transformation and select the Port tab.

3. Add a port called RECEIPT_FLG_OUT and select only the flag "O". Leave the "I" and "V" flags deselected. Insert your expression for the receipt flag. Your expression can use the input port RECEIPT_FLG or other input columns based on your business logic.

4. Re-map the RECEIPT_FLG port to the target and validate the mapping.

5. Save your changes to the repository.

4.3.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 Informatica 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.

4.3.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 Informatica 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.

4.3.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 Informatica 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.