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, PeopleSoft 8.9 and 9.0, and the Universal adapter.

Note: For Oracle Employee Expenses (OIE), Release 7.9.6.3 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 R12.
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.
Supplier Performance. The Suppliers functional area (available with Supplier Performance Analytics) 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 (available with Supplier Performance Analytics) 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".

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

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

This section contains configuration steps that you must 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"

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. It contains the following topic:

Section 4.2.1.1, "About Integrating Oracle Procurement with Spend Analytics with Oracle Spend Classification"

Note:

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

4.2.1.1 About Integrating Oracle Procurement with Spend Analytics with Oracle Spend Classification

This section contains configuration steps that apply to Oracle Procurement and Spend Analytics when deployed with Oracle Spend Classification. For implementing Oracle Spend Classification and required patches, refer to the Oracle Spend Classification product documentation.

If you are not implementing Oracle Spend Classification, you might choose to remove or hide the Oracle Spend Classification integration metadata from the Presentation layer of the BI repository (for more information, see Section 4.2.1.1.3, "How to Remove or Hide Oracle Spend Classification Integration Metadata").

Note: Oracle Spend Classification is not part of the core Oracle BI Applications product suite, and is not packaged with any module of Oracle BI Applications. It is a separate solution offered by Oracle, and a separate license is required. If you are interested in licensing and implementing Oracle Spend Classification, contact your Oracle Sales Representative.

4.2.1.1.1 Overview to Oracle Spend Classification Integration

Oracle Spend Classification is a complementary product that can be used in conjunction with Oracle Procurement and Spend Analytics to improve the accuracy of Spend by converting 'unclassified' Spend into item categories. Oracle Procurement and Spend Analytics is designed to work with or without Oracle Spend Classification.

Typical procurement systems will have many PO, Invoice, and Expense Transactions without reference to item and item categories, and in most cases they might have item descriptions in a free text format. When you implement Oracle Procurement and Spend Analytics, these transactions will come into the system as 'Unclassified' because they do not have corresponding items and/or item categories. This issue is more prominent if your organization's Spend constitutes a major portion of Indirect Spend.

Oracle Procurement and Spend Analytics is installed with infrastructure required to feed data from the data warehouse to Oracle Spend Classification, and feed the classified data back into the data warehouse. This Infrastructure is provided as an additional feature for those customers who would like to take the advantage of both Oracle Procurement and Spend Analytics and Oracle Spend Classification.

If you choose not to use Oracle Spend Classification, Oracle Procurement and Spend Analytics can be deployed as a stand alone solution, and all the features of Procurement and Spend Analytics can be deployed without any dependency on Oracle Spend Classification.

4.2.1.1.2 About the Oracle Spend Classification Metadata

This section describes the Oracle Spend Classification metadata and repository metadata that is available for use with Oracle Spend Classification.

The following facts are integrated with Oracle Data Classification to enrich and automatically assign category codes:

W_AP_INV_DIST_F
W_PURCH_COST_F
W_RQSTN_LINE_COST_F

There are five types of taxonomy supported: UNSPSC, EBS Purchasing Categories, and three custom categories. The classification results are stored in these columns:

AUTO_UNSPSC_WID
AUTO_PURCHASING_CATEGORY_WID
AUTO_CUSTOM_CATEGORY1_WID
AUTO_CUSTOM_CATEGORY2_WID
AUTO_CUSTOM_CATEGORY3_WID

In the Analytics metadata repository (RPD), the following is configured by default:

UNSPSC, EBS Purchasing Categories, and Custom Category1 are configured up to the Business Model and Mapping layer. The facts and dimension names are as follows:
- Fact - Spend and AP Invoice Distribution
- Fact - Purchasing – Order
- Fact - Purchasing – Requisition
- Dim - Auto UNSPSC
- Dim - Auto Purchasing Category
- Dim - Auto Custom Category1
In the Presentation layer, 'Procurement and Spend - Invoice Lines' contains the columns for data classification, under the following folders:
- Data Classification
- Auto UNSPSC
- Auto Purchasing Category
- Auto Custom Category 1

4.2.1.1.3 How to Remove or Hide Oracle Spend Classification Integration Metadata

If you are not implementing Oracle Spend Classification, Oracle recommends that you remove or hide the Oracle Spend Classification integration metadata that is included in the Presentation layer of the BI repository as installed out-of-the-box. Hiding or deleting this metadata avoids potential confusion among business end users.

To remove or hide Oracle Spend Classification Integration Metadata:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
In the Presentation layer pane, expand the folder 'Procurement and Spend - Invoice Lines.'

The Oracle Spend Classification metadata in the Presentation layer consists of the following objects:

Data Classification

Auto UNSPSC

Auto Purchasing Category

Auto Custom Category 1
To remove the metadata objects listed above, right-click the objects and select Delete.
Note:
If you decide later to implement Oracle Spend Classification, you need to do the following:
1. In the Business Model and Mapping layer, drag and drop the following dimensions to the 'Procurement and Spend - Invoice Lines' folder in the Presentation layer:
  
  - Dim - Auto UNSPSC
  
  - Dim - Auto Purchasing Category
  
  - Dim - Auto Custom Category1
To hide these objects from end users, right-click each object and select Properties, then Permissions, and then select No Access for the appropriate application roles.
Note:
If you decide later to implement Oracle Spend Classification, you need to do the following:
1. To display the following objects to end users, right-click each object and select Properties, then Permissions, and then select Read for the appropriate application roles:
  
  - Data Classification
  
  - Auto UNSPSC
  
  - Auto Purchasing Category
  
  - Auto Custom Category 1
Save and close the repository.

4.2.1.1.4 How to Deploy UNSPSC, EBS Purchasing Categories, and Custom Category1

Follow these steps if you want to expose UNSPSC, EBS Purchasing Categories, and Custom Category1 for your Purchase Order and Purchase Requisition Subject Area.

To deploy UNSPSC, EBS Purchasing Categories, and Custom Category1:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
In the Presentation layer, do the following:
1. Expand the folder "Procurement and Spend - Invoice Lines".
2. Multi-select the following folders and right-click to copy:
  
  Data Classification
  
  Auto UNSPSC
  
  Auto Purchasing Category
  
  Auto Custom Category 1
3. To implement Oracle Spend Classification in Purchase Orders, select the folder "Procurement and Spend - Purchase Orders" and right-click to paste in the folders.
4. To implement Oracle Spend Classification in Purchase Requisitions, select the folder "Procurement and Spend - Purchase Requisitions" and right-click to paste in the selected folders.
5. Verify the new folders.
6. If required, reorder the folders as you would like the folders to be displayed to business users in the Presentation Services catalog.
Save and close the repository.

4.2.1.1.5 How to Deploy the Additional Custom Category2 and Custom Category3

This section explains how to deploy Custom Category2 and Custom Catogory3.

Note: This task uses the Fact_W_AP_INV_DIST_F fact as an example, though you can also apply the steps to deploy other facts.

To deploy Custom Category2 and Custom Catogory3:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
In the Physical layer, do the following:
1. Right-click "Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY1" under "Oracle Data Warehouse" and select Duplicate.
2. Rename it to "Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2".
3. Join dimension "Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2" and fact "Fact_W_AP_INV_DIST_F" using the following condition:
```
Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2.ROW_WID = Fact_W_AP_INV_DIST_F."AUTO_CUSTOM_CATEGORY2_WID
```
In the Business Model and Mapping layer, do the following:
1. Immediately below table "Dim - Auto Custom Category1", create "Dim - Auto Custom Category2".
2. Immediately below hierarchy "Auto Custom Category1", create "Dim - Auto Custom Category2" based on the physical table "Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2".
3. Join "Dim - Auto Custom Category1" to "Fact - Spend and AP Invoice Distribution".
4. Edit "Fact - Spend and AP Invoice Distribution". Fact_W_AP_INV_DIST_F. Display the Content tab, and set the level of "Auto Custom Category2" to "Custom Hierarchy Base Level".
In the Presentation layer, do the following:
1. Create a sub-folder called "Auto Custom Category 2" in the "Procurement and Spend - Invoice Lines" folder. Edit folder and add this exact string to the Description box.
  
  Auto Custom Category2 becomes a sub-folder of Data Classification.
2. Order this folder so that it is after "Auto Custom Category 1".
3. Drag the "Dim - Auto Custom Category1" columns from the Business Model and Mapping layer into the "Auto Custom Category 2" folder in the Presentation layer.
Save and close the repository.
Repeat steps 2 - 5 for Custom Category3.

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 Invoice Types for Spend Analysis"
Section 4.2.2.4, "Configuration Specific to Certain Versions of Oracle EBS"

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 between data coming from Oracle EBS applications and data that might be coming from other applications such as PeopleSoft. This section applies to all versions of Oracle EBS.

To configure the DAC parameter for purchase cycle line:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and query for task SIL_PurchaseCycleLinesFact.
Display the Parameters tab in the lower pane.
Edit the value of the parameter $$ORA_DATASOURCE_NUM_ID_LIST and specify a comma-separated list of Data Source NUM IDs that you defined for your Oracle data sources.

You specify Data Source NUM IDs on the DAC\Setup\Physical Data Sources tab in the Data Source Number field for each data source.
Repeat the same steps 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.

This section applies to all versions of Oracle EBS. See also Section 4.2.2.4, "Configuration Specific to Certain Versions of Oracle EBS," which describes CSV files not included in this list.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles). The table also lists the CSV worksheet source files for Oracle Procurement and Spend Analytics in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

Table 4-1 Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

Worksheet File Name	Description	Session
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.13, "How to Configure domainValues_Employee_Expense_Status.csv."	SDE_ORA_EmployeeExpenseStatusDimension
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.10, "How to Configure domainValues_Employee_Expense_Type_ora11i.csv."	SDE_ORA_Employee ExpenseTypeDimension
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.8, "How to Configure domainValues_ShipmentType_ora12.csv."	SDE_ORA_Transaction TypeDimension_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.2, "How 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.3, "How to Configure domainValues_Status_Purch_Cycle_ora11i.csv."	SDE_ORA_StatusDimension_PurchaseCycle
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.12, "How to Configure domainValues_Xact_Sources_APSources_ora.csv."	SDE_ORA_Transaction SourceDimension_AP_CC_Extract, SDE_ORA_Transaction SourceDimension_AP_LKP_Extract
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.7, "How to Configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv."	SDE_ORA_Transaction TypeDimension_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.6, "How to Configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv."	SDE_ORA_Transaction TypeDimension_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.4, "How to Configure domainValues_Xact_Types_Purch_Orders_ora11i.csv."	SDE_ORA_Transaction TypeDimension_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.5, "How to Configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv."	SDE_ORA_Transaction TypeDimension_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.9, "How to Configure domainValues_Xact_Types_PurchaseRequisition.csv." Note: See also the file domainValues_Xact_Types_PurchaseReceipt.csv, which is used to configure Receipt transaction types.	SDE_ORA_Transaction TypeDimension_PurchaseRequisition
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_Employee ExpensePaymentMethodDimension
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_Transaction TypeDimension_Expense_Pay_Type
File_StatusDimension_PurchaseRequisition.csv	Lists the Purchasing Requisition Status column and the corresponding domain values for the Oracle EBS application.	SDE_ORA_Status Dimension_PurchaseRequisition
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, "How to Configure file_xact_type_AgreementLeverageType.csv."	SDE_ORA_Transaction TypeDimension_AgreementLeverage Type
domainValues_Status_APInvoicePaymentStatus_ora11i.csv	Lists the AP Invoice Payment statuses and the corresponding domain values for the Oracle EBS Application.The values of NOT PAID,PARTIALLY PAID,FULLY PAID have been defined for the source values of 'N','P','Y' respectively. No configuration should be required for this file.	SDE_ORA_StatusDimension_APInvoicePayment Status
file_xact_type_AgreementLeverageType_2.csv	Configures the value of W_XACT_TYPE_CODE2 to identify types of non-agreement purchase. The values Others, Leakage, and Potential Leakage have been defined for Non-agreement. No configuration should be required for this file.	SDE_ORA_Transaction TypeDimension_AgreementLeverageType
file_Consigned_Type.csv	Lists the Purchase Order Consigned Code Transaction Type column and the corresponding domain values for the Oracle EBS application. The Consigned Code CONSIGNED, REGULAR, and CONSIGNED-CONSUMED have been identified as different consigned types. No configuration should be required for this file.	SDE_ORA_TransactionType Dimension_ConsignedCode
file_APInvoice_Approval_Status.csv	Lists the AP invoice approval status codes and the corresponding domain values for the Oracle EBS Application. This file contains both source values and domain values for AP Invoice Approval status. The values of CANCELLED, ON HOLD, REQUIRES APPROVAL, and APPROVED have been defined for the payables invoices. No configuration should be required for this file.	SDE_ORA_StatusDimension_APInvoiceApproval Status
file_APInvoice_spend_type.csv	Lists the AP Invoice distribution types and the corresponding domain values for the spend types for the Oracle EBS 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 required for this file.	SDE_ORA_TransactionType Dimension_APSpend
domainValues_Xact_Types_DocTypes_ora11i.csv	The file lists the Transaction types and the corresponding domain values for the Oracle EBS application. The PAYABLES related values are considered. The source invoice type and source invoice distribution type is defined, and corresponding domain values are mapped. No configuration should be required for this file.	SDE_ORA_TransactionType Dimension_APDerive
file_ap_invoice_config_spend_ora12.csv	This file lists the AP invoice types and corresponding designation on whether to include in the spend calculation. For information about how to edit this file, see Section 4.2.2.3, "How to Configure AP Invoice Types for Spend Analysis."	SDE_ORA_APInvoice DistributionFact

4.2.2.2.2 How to Configure domainValues_Status_Purch_Approve_ora11i.csv

This section explains how to configure the domainValues_Status_Purch_Approve_ora11i.csv file.

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 $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open 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.

4.2.2.2.3 How to Configure domainValues_Status_Purch_Cycle_ora11i.csv

This section explains how to configure the domainValues_Status_Purch_Cycle_ora11i.csv file.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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.

4.2.2.2.4 How to Configure domainValues_Xact_Types_Purch_Orders_ora11i.csv

This section explains how to configure the domainValues_Xact_Types_Purch_Orders_ora11i.csv file.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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.

4.2.2.2.5 How to Configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv

This section explains how to configure the domainValues_Xact_Types_Purch_Requisitions_ora11i.csv file.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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.

4.2.2.2.6 How to Configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv

This section explains how to configure the domainValues_Xact_Types_PO_Line_Type_ora11i.csv file.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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.

4.2.2.2.7 How to Configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv

This section explains how to configure the domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv file.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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.

4.2.2.2.8 How to Configure domainValues_ShipmentType_ora12.csv

This section explains how to configure the domainValues_ShipmentType_ora12.csv file. 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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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 second 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.

4.2.2.2.9 How to Configure domainValues_Xact_Types_PurchaseRequisition.csv

This section describes how to configure the domainValues_Xact_Types_PurchaseRequisition.csv file.

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 must be set for non-English deployments.

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Xact_Types_PurchaseRequisition.csv in a text editor.
Copy the columns LOOKUP_CODE to XACT_TYPE_CODE starting from the second line of data.
Map each LOOKUP_CODE to either the MANUAL or AUTO domain value. Use commas to separate the entries.
Save and close the file.

4.2.2.2.10 How to Configure domainValues_Employee_Expense_Type_ora11i.csv

This section describes how to configure the domainValues_Employee_Expense_Type_ora11i.csv file.

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')

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Employee_Expense_Type_ora11i.csv in a text editor.
Copy the columns LOOKUP_CODE to XACT_TYPE_CODE starting from the second line of data.
Map each LOOKUP_CODE to one of the respective EXPENSE_TYPE domain values. Use commas to separate the entries.
Save and close the file.

4.2.2.2.11 How to Configure file_xact_type_AgreementLeverageType.csv

This section describes how to configure the file_xact_type_AgreementLeverageType.csv file.

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file file_xact_type_AgreementLeverageType.csv in a text editor.

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.

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.12 How to Configure domainValues_Xact_Sources_APSources_ora.csv

This section describes how to configure the domainValues_Xact_Sources_APSources_ora.csv file.

Note:

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

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
```
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Xact_Sources_APSources_ora.csv in a text editor.
Copy the results of the query in step 1 to the SOURCE_NAME column in the file, starting from the second line of data.
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.
Save and close the file.

4.2.2.2.13 How to Configure domainValues_Employee_Expense_Status.csv

This section describes how to configure the domainValues_Employee_Expense_Status.csv file.

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')

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Employee_Expense_Status.csv in a text editor.
Copy the columns LOOKUP_CODE to STATUS_CODE.

The data must be copied starting from the 8th line.
Map each LOOKUP_CODE to one W_STATUS_CODE domain value.

Use commas to separate the entries.
Save and close the file.

4.2.2.3 How to Configure AP Invoice Types for Spend Analysis

The configuration file for AP invoice types for Spend Analysis is file_ap_invoice_config_spend_ora12.csv in $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles). 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.

This section applies to all versions of Oracle EBS.

To configure file_ap_invoice_config_spend_ora12.csv:

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open file_ap_invoice_config_spend_ora12.csv file in a text editor.
Update the Y/N flag to designate whether to include invoice types in spend calculations.
Save and close the file.

4.2.2.4 Configuration Specific to Certain Versions of Oracle EBS

This section describes configuration that must be performed only for certain Oracle EBS versions. This section contains the following topics:

Section 4.2.2.4.1, "How to Configure domainValues_PaymentType_ora12.csv"
Section 4.2.2.4.2, "How to Configure Oracle Employee Expense Analytics for Oracle EBS 11.5.10"

4.2.2.4.1 How to Configure domainValues_PaymentType_ora12.csv

This section explains how to configure the domainValues_PaymentType_ora12.csv file. This file lists the Payment Type and the corresponding domain values for the Oracle EBS R12 application. This file is only applicable to Oracle R12.

Note that the session for this file is SDE_ORA_TransactionTypeDimension_PO_Payment_Type.

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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles), 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 second 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.

4.2.2.4.2 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.OIE.K family pack, then you need to follow these configuration steps.

To configure Oracle Employee Expenses for Oracle EBS 11.5.10:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and query for task SDE_ORA_EmployeeExpenseFact.
In the lower pane, display the Edit tab, and change the value for 'Command for Incremental Load' from SDE_ORA_EmployeeExpenseFact to SDE_ORA_EmployeeExpenseFact_FP.
On the Edit tab, change the value for 'Command for Full Load' from SDE_ORA_EmployeeExpenseFact_Full to SDE_ORA_EmployeeExpenseFact_FP_Full.
Save the changes.
Display the Subject Areas tab, and query for the 'Employee Expenses' Subject Area.
Select the queried record for 'Employee Expenses', and click Assemble to assemble the Subject Area.
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"
Section 4.2.3.3, "Enabling Reporting on Product/Purchasing Categories 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 directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

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

4.2.3.1.1 How to Configure domainValues_Status_Purch_Approve_psft.csv

This section explains how to configure the domainValues_Status_Purch_Approve_psft.csv file.

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

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Status_Purch_Approve_psft.csv file in a text editor.
Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 9th line.
Map each FIELDVALUE to one Purchase Approval (PURCH_APPROVAL) domain value. Use commas to separate the entries.
Save and close the file.

4.2.3.1.2 How to Configure domainValues_Status_Purch_Cycle_psft.csv

This section explains how to configure the domainValues_Status_Purch_Cycle_psft.csv file.

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

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Status_Purch_Cycle_psft.csv file in a text editor.
Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 9th line.
Map each FIELDVALUE to one Purchase Cycle (PURCH_CYCLE) domain value. Use commas to separate the entries.
Save and close the file.

4.2.3.1.3 How to Configure domainValues_Xact_Types_Procurement_psft.csv - PO Line Type

This section explains how to configure the domainValues_Xact_Types_Procurement_psft.csv file for the PO Line Type transaction type.

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

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Xact_Types_Procurement_psft.csv file in a text editor.
Copy the FIELDVALUE to the XACT_SUBTYPE_CODE column in the file. The data must be copied starting from the 11th line.
Map each FIELDVALUE to one PO Line Type transaction type (PO_LINE_TYPE) domain value. Use commas to separate the entries.
Save and close the file.

4.2.3.1.4 How to Configure domainValues_Xact_Types_Procurement_psft.csv - Purchase Order

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

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

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Xact_Types_Procurement_psft.csv file in a text editor.
Copy the FIELDVALUE to the XACT_SUBTYPE_CODE column in the file. The data must be copied starting from the 11th line.
Map each FIELDVALUE to one Purchase Order transaction type (PURCH_ORDERS) domain value. Use commas to separate the entries.
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.
Save and close the file.

4.2.3.1.5 How to Configure domainValues_Status_APInvoice_psft.csv - Approval Status

This section explains how to configure the domainValues_Status_APInvoice_psft.csv file.

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.
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Status_Purch_Approve_psft.csv file in a text editor.
Copy the FIELDVALUE to the STATUS_CODE column in the file. The data must be copied starting from the 10th line.
Map each FIELDVALUE to one Approval (APPROVAL) domain value. Use commas to separate the entries.
Save and close the file.

4.2.3.1.6 How to Configure domainValues_Xact_Source_AP_Invoice_psft.csv

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

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

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Xact_Source_AP_Invoice_psft.csv file in a text editor.
Copy the FIELDVALUE to the SOURCE_CODE column in the file. The data must be copied starting from the 10th line.
Map each FIELDVALUE to one AP Invoice domain value. Use commas to separate the entries.
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.
Save and close the file.

4.2.3.1.7 How to Configure file_xact_type_AgreementLeverageType_psft.csv

This section explains how to configure the file_xact_type_AgreementLeverageType_psft.csv file.

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file_xact_type_AgreementLeverageType_psft.csv in a text editor.

Configure the value of W_XACT_TYPE_CODE1.

The following table lists the seven business scenarios for XACT_TYPE_CODE and XACT_TYPE_DESC. Scenarios one through six use a type of negotiation. Scenario seven does not use negotiation. Only Oracle EBS supports all seven scenarios.

Note:

PeopleSoft does not support these scenarios: 5. Complex work and 6. Other Negotiated.

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.

In the preconfigured application, scenarios one through six are considered agreement purchases, for example W_XACT_TYPE_CODE1=Agreement. If your business rule does not consider one of these scenarios as an agreement purchase, change the value to "Non-Agreement."

4.2.3.1.8 How to Configure the domainValues_Xact_Types_Expense_psft.csv

This section describes how to configure the domainValues_Xact_Types_Expense_psft.csv file.

Identify the different expense types of employee expense reports in your PeopleSoft source system by using the following SQL:
```
SELECT DISTINCT EXPENSE_TYPE FROM PS_EX_TYPES_TBL
```
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_Xact_Types_Expense_psft.csv in a text editor.
Copy the EXPENSE_TYPE to XACT_TYPE_CODE column. The data must be copied starting from the second line.
Map each expense type to one of the respective EXPENSE_TYPE domain values. Use commas to separate the entries.
Save and close the file.

4.2.3.1.9 How to Configure the domainValues_Xact_Types_ExpensePay_psft.csv

This section describes how to configure the domainValues_Xact_Types_ExpensePay_psft.csv file.

Identify the different expense pay types of employee expense reports in your PeopleSoft source system by using the following SQL:

SELECT A.FIELDVALUE FIELDVALUE, A.XLATLONGNAME XLATLONGNAME
FROM PSXLATITEM A LEFT OUTER JOIN PSXLATITEMLANG B ON A.FIELDNAME=B.FIELDNAME AND A.FIELDVALUE=B.FIELDVALUE AND B.LANGUAGE_CD='ENG'
WHERE A.EFFDT=(SELECT MAX(C.EFFDT) FROM PSXLATITEM C
WHERE C.FIELDNAME=A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE )
AND A.FIELDNAME='EX_PYMNT_TYPE'

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Xact_Types_ExpensePay_psft.csv in a text editor.
Copy the FIELDVALUE and XLATLONGNAME to the XACT_TYPE_CODE and XACT_TYPE_DESC columns in the file respectively. The data must be copied starting from the second line.
Map each expense pay type to one of the respective EXPENSE_PAY_TYPE domain values. Use commas to separate the entries.
Save and close the file.

4.2.3.1.10 How to Configure the domainValues_Status_Expense_psft.csv

This section describes how to configure the domainValues_Status_Expense_psft.csv file.

Identify the different expense statuses of employee expense reports in your PeopleSoft source system by using the following SQL:

SELECT A.FIELDVALUE FIELDVALUE, A.XLATSHORTNAME XLATSHORTNAME,
A.XLATLONGNAME XLATLONGNAME FROM PSXLATITEM A LEFT OUTER JOIN PSXLATITEMLANG B
ON A.FIELDNAME=B.FIELDNAME AND A.FIELDVALUE=B.FIELDVALUE
AND B.LANGUAGE_CD='ENG'
WHERE A.EFFDT = (SELECT MAX(C.EFFDT) FROM PSXLATITEM C
  WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE )
    AND A.FIELDNAME='SHEET_STATUS'

From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Status_Expense_psft.csv in a text editor.
Copy the FIELDVALUE to STATUS_CODE, XLATESHORTNAME to STATUS_NAME, and XLATLONGNAME to STATUS_DESC. The data must be copied starting from the second line.
Map each expense status to one of the respective EXPENSE REPORT STATUS domain values. Use commas to separate the entries.
Save and close the file.

4.2.3.1.11 How to Configure the domainValues_Payment_Method_psft.csv

This section describes how to configure the domainValues_Payment_Method_psft.csv file.

Identify the different expense payment methods of employee expense in your PeopleSoft source system by using the following SQL:
```
SELECT DISTINCT EXPEND_MTHD, DESCR FROM PS_EX_EXP_MTHD_TBL
```
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the file domainValues_Payment_Method_psft.csv in a text editor.
Copy the EXPEND_MTHD and DESCR to the PAYMENT_METHOD_CODE and PAYMENT_METHOD_DESC columns in the file respectively. The data must be copied starting from the second line.
Map each expense payment method to one of the respective expense payment method domain values. Use commas to separate the entries.
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 directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

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_TransactionType Dimension_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_Transaction TypeDimension_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_Transaction SourceDimension_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, "How 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_Transaction TypeDimension_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_Transaction TypeDimension_Consigned_Code, SDE_PSFT_CodeDimension_Supply_Chain_Purchasing_Consigned_Code
file_xact_type_AgreementLeverageType_psft.csv	Lists the agreement leverage types and the corresponding domain values for the PeopleSoft application. For more information about how to edit this file, see Section 4.2.3.2.2, "How to Configure file_xact_type_AgreementLeverageType_psft.csv."	SDE_PSFT_Transaction TypeDimension_AgreementLeverageType

4.2.3.2.1 How to Configure file_ap_invoice_config_spend_voucher_psft.csv

This section explains how to configure the file_ap_invoice_config_spend_voucher_psft.csv file 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.

From the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles), open file_ap_invoice_config_spend_voucher_psft.csv file in a text editor.
Update the Y/N flag to designate whether to include the type in spend calculations.
Save and close the file.

4.2.3.2.2 How to Configure file_xact_type_AgreementLeverageType_psft.csv

This section describes how to configure the file_xact_type_AgreementLeverageType_psft.csv file.

From the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles), open the file_xact_type_AgreementLeverageType_psft.csv file in a text editor.

Configure the value of W_XACT_TYPE_CODE1.

Note:

PeopleSoft does not support these scenarios: 5. Complex work and 6. Other negotiated.

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.

In the preconfigured application, scenarios one through six are considered agreement purchases, for example W_XACT_TYPE_CODE1=Agreement. If your business rule does not consider one of these scenarios as an agreement purchase, change the value to "Non-Agreement."

4.2.3.3 Enabling Reporting on Product/Purchasing Categories for PeopleSoft Procurement and Spend Analytics

Procurement and Spend Analytics supports reporting by Product/Purchasing Category hierarchy for organizations that use Product/Purchasing Category to create requisition and purchase orders.

In PeopleSoft Enterprise applications, Product/Purchasing Category hierarchies are stored in trees. A PeopleSoft Enterprise application user chooses a tree and then assigns a category from that tree to an item using the Create Requisition option from the eProcurement menu.

The default value of TREE_NAME in the PeopleSoft source system is ALL_PURCHASE_ITEMS. If your organization has not changed the value of TREE_NAME to another value, and you want to report by this TREE_NAME, then no additional configuration is required to report on Product/Purchasing Category hierarchies.

If your organization has changed the default value of TREE_NAME, then you need to perform the following tasks described in this section.

Use the following SQL query to extract the tree names from the PeopleSoft Enterprise application to confirm the value of TREE_NAME:

SELECT * FROM PSTREEDEFN WHERE TREE_STRCT_ID = 'ITEMS'

To enable business intelligence reporting on Product/Purchasing Categories, use DAC to configure the following tasks:

SDE_PSFT_ProductDimension_ItemCategory
SDE_PSFT_ProductDimension_ItemMaster

In both of these tasks, set the value of the $$TREE_NAME1 variable to the tree name that you want to use for reporting, and then save your changes.

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. Valid values 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.

When the Supply Chain - Purchase Receipts Subject Area is included in an Execution Plan in DAC, the Purchase Receipts data is extracted using the following tasks:

SIL_PurchaseReceiptAggregate_Derive_PreSoftDeleteImage 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 task is run in the source-specific workflow before the records are deleted from the base table.
SIL_PurchaseReceiptAggregate_Derive_PreLoadImage 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 task is run in the source-specific workflow before the records are updated in the base table.
PLP_PurchaseReceiptAggregate_Derive_PostLoadImage 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 task is run in the post load-processing workflow after the records are updated or inserted into the base table.
PLP_PurchaseReceiptAggregate_Load 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.

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:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab.
For each of the following tasks, display the Parameters subtab and create the parameter name and parameter value pairs as follows:
- SIL_PurchaseReceiptAggregate_Derive_PreLoadImage $$TIME_GRAIN MONTH
- SIL_PurchaseReceiptAggregate_Derive_PreSoftDeleteImage $$TIME_GRAIN MONTH
- PLP_PurchaseReceiptAggregate_Derive_PostLoadImage $$TIME_GRAIN MONTH
- PLP_PurchaseReceiptAggregate_Load $$GRAIN 'MONTH'

To configure the Purchase Receipts aggregate table for Universal Source:

Using a text editor, open the file parameterfileDW.txt, located in the Dac\Informatica\parameters\input folder on the DAC Server computer.

For example:

C:\orahome\10gversion\bifoundation\dac\Informatica\parameters\input
Locate the parameter entries for the PLP_PurchaseReceiptAggregate* and SIL_PurchaseReceiptAggregate* tasks.
Replace the default parameter values with your new values.
Save and close the file.

4.3.1.3 About Configuring the Purchase Cycle Lines Aggregate Table

The Purchase Cycle Lines aggregate table (W_PURCH_CYCLE_LINE_A) is used to capture information about the purchasing cycle lines.

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 have a preconfigured value of Month. Valid values 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.

When the Supply Chain - Purchase Cycle Lines Subject Area is included in an Execution Plan in DAC, the Purchase Cycle Lines data is extracted using the following tasks:

SIL_PurchaseCycleLinesAggregate_Derive_PreSoftDeleteImage 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 task is run in the source-specific window before the records are deleted from the base table.
SIL_PurchaseCycleLinesAggregate_Derive_PreLoadImage 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 task is run in the source-specific workflow before the records are updated in the base table.
PLP_PurchaseCycleLinesAggregate_Derive_PostLoadImage 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 task is run in the post load-processing workflow after the records are updated or inserted into the base table.
PLP_PurchaseCycleLinesAggregate_Load 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.

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:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab.
For each of the following tasks, display the Parameters subtab and create the parameter name and parameter value pairs as follows:
- SIL_PurchaseCycleLinesAggregate_Derive_PreLoadImage $$TIME_GRAIN MONTH
- SIL_PurchaseCycleLinesAggregate_Derive_PreSoftDeleteImage $$TIME_GRAIN MONTH
- PLP_PurchaseCycleLinesAggregate_Derive_PostLoadImage $$TIME_GRAIN MONTH
- PLP_PurchaseCycleLinesAggregate_Load $$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:

In Informatica PowerCenter Designer, open the SDE_ORAVersion_Adaptor.
Open the mapplet mplt_BC_ORA_PurchaseOrderFact.
Double-click the Source Qualifier to display the Edit Transformations dialog, and display the Properties tab.
Select 'Sql Query' in the Transformation Attribute column, and click the down arrow in the Value column to display the SQL Editor dialog.
In the SQL box, 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 mapplet mplt_BC_ORA_PurchaseScheduleLinesFact.

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:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Subject Areas tab.
For each Subject Area that you want to deploy, do the following:
1. Select the Subject Area.
2. Display the Configuration Tags tab in the upper pane.
3. Select the Enable Project Dimensions tag.
4. Clear the Inactive check box.
Assemble the Subject Areas.
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:

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Subject Areas tab.
For each Subject Area that you want to deploy, do the following:
1. Select the Subject Area.
2. Display the Configuration Tags tab in the upper pane.
3. Select the Enable Project Dimensions tag.
4. Clear the Inactive check box.
Assemble the Subject Areas.
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 the Default Expense Distribution Percentage"
Section 4.3.4.6, "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:

In Informatica PowerCenter Designer, open the SDE_Universal_Adaptor folder.
Open the SDE_Universal_ExpenseFact mapping in the Mapping Designer.
Select the Expression transformation to open the Edit Transformations dialog and display the Ports tab.
If a port named PREF_MERCHANT_FLG is not present, click the Add Port icon and add a port named PREF_MERCHANT_FLG.
Configure the PREF_MERCHANT_FLG port logic as required.
Validate the mapplet and click OK to exit.
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:

In Informatica PowerCenter Designer, open the SDE_Universal_Adaptor folder.
Open the SDE_Universal_ExpenseFact mapping in the Mapping Designer.
Select the Expression transformation to open the Edit Transformations dialog, and display the Ports tab.
If a port named CUST_BILLABLE_FLG is not present, click the Add Port icon. Then, add a port called CUST_BILLABLE_FLG = your_expression.
Validate the mapplet and click OK to exit.
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:

In Informatica PowerCenter Designer, open the SDE_Universal_Adaptor folder.
Open the SDE_Universal_ExpenseFact mapping in the Mapping Designer.
Select the Expression transformation to open the Edit Transformations dialog, and display the Ports tab.
If a port named RECEIPT_FLG is not present, click the Add Port icon and add a port named RECEIPT_FLG.

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.

Re-map the RECEIPT_FLG port to the target and validate the mapping.
Save your changes to the repository.

4.3.4.5 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:

In Informatica PowerCenter Designer, open the SDE_Universal_Adaptor folder.
Open the SDE_Universal_ExpenseFact mapping in the Mapping Designer.
Select the Expression transformation to open the Edit Transformations dialog, and display the Ports tab.
If a port named DIST_PERCENTAGE is not present, click the Add Port icon. Then, add a port named DIST_PERCENTAGE = expression_that_sets_this_to_100%.
Validate your mapplet.
Save your changes.

4.3.4.6 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:

In Informatica PowerCenter Designer, open the SDE_Universal_Adaptor folder.
Open the SDE_Universal_ExpenseFact mapping in the Mapping Designer.
Select the Expression transformation to open the Edit Transformations dialog, and display the Ports tab.
If a port named EXT_XRATE_LOOKUP_DATE is not present, click the Add Port icon and add a port named EXT_XRATE_LOOKUP_DATE.
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.

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, "How 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, "How 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, "How to Configure domainValues_Xact_Types_Procurement_psft.csv - PO Line Type."	SDE_PSFT_Transaction TypeDimension_PO_Line_Type
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Order Transaction Type column and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.4, "How to Configure domainValues_Xact_Types_Procurement_psft.csv - Purchase Order."	SDE_PSFT_Transaction TypeDimension_PurchaseOrder
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Requisition Transaction Type column and the corresponding domain values for the PeopleSoft 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_Transaction TypeDimension_PurchaseRequestion
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Receipt Transaction Type column and the corresponding domain values for the PeopleSoft 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_Transaction TypeDimension_PurchaseReceipt
domainValues_Xact_Types_Procurement_psft.csv	Lists the Purchase Order Consigned Code Transaction Type column and the corresponding domain values for the PeopleSoft 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_Transaction TypeDimension_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, "How 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, "How to Configure domainValues_Xact_Source_AP_Invoice_psft.csv."	SDE-PSFT_Transaction SourceDimension_APInvoice
domainValues_Xact_Types_APInvoice_DistributionTypes_psft.csv	Lists the AP Invoice Distribution Type Transaction Type column and the corresponding domain values for the PeopleSoft 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_Transaction TypeDimension_APInvoiceDistribution Type
file_xact_type_AgreementLeverageType_psft.csv	Lists the agreement leverage types and the corresponding domain values for the PeopleSoft application. For more information about how to edit this file, see Section 4.2.3.1.7, "How to Configure file_xact_type_AgreementLeverageType_psft.csv."	SDE_PSFT_Transaction TypeDimension_AgreementLeverageType
domainValues_Xact_Types_Expense_psft.csv	Lists the different expense types and the corresponding domain values for the PeopleSoft application. For more information, see Section 4.2.3.1.8, "How to Configure the domainValues_Xact_Types_Expense_psft.csv."	SDE_PSFT_Transaction TypeDimension_EmployeeExpense
domainValues_Xact_Types_ExpensePay_psft.csv	Lists the expense pay types and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.9, "How to Configure the domainValues_Xact_Types_ExpensePay_psft.csv."	SDE_PSFT_Transaction TypeDimension_ExpensePayType
domainValues_Status_Expense_psft.csv	Lists the expense statuses and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.10, "How to Configure the domainValues_Status_Expense_psft.csv."	SDE_PSFT_StatusDimension_EmployeeExpense
domainValues_Payment_Method_psft.csv	Lists the expense payment methods and the corresponding domain values for the PeopleSoft application. For information about how to edit this file, see Section 4.2.3.1.11, "How to Configure the domainValues_Payment_Method_psft.csv."	SDE_PSFT_Payment MethodDimension_EmployeeExpense