Skip Headers
Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users
Version 7.9.6.1
Part Number E14844-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

5 Configuring Oracle Financial Analytics

This section describes how to configure Oracle Financial Analytics. It contains the following topics:

5.1 Overview of Oracle Financial Analytics

Oracle Financial Analytics consists of the following:

5.2 Configuration Required Before a Full Load for Financial Analytics

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

5.2.1 Configuration Steps for Financial Analytics for All Source Systems

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

Note:

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

Note: If you want to deploy multi-calendar with Oracle Financial Analytics, you must have enabled a number of Initialization Blocks that are disabled out-of-the-box. For more information, see Section 3.1.4.1.1, "About enabling Initialization Blocks Required For Calendars".

5.2.2 Configuration Steps for Financial Analytics for Oracle EBS

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

5.2.2.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics

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

The following sections explain 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 to configure the domain values and CSV worksheet files.

Typically, the number of rows returned by the source system will not match the number of rows provided in the domain valuesXXX.csv files. If so, you need to delete extra rows or insert additional rows in the CSV file to accommodate all the values returned by the source system in context. You also need to determine which row should have 'Return' and which row should have 'Delivery' in the W_XACT_TYPE_CODE column.

Table 5-1 lists the CSV worksheet files and the domain values for Financial Analytics and Oracle EBS in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

Table 5-1 Domain Values and CSV Worksheet Files for Oracle Financial Analytics and Oracle EBS

Worksheet File Name Description Session

domainValues_Xact_Types_DocTypes_ora12.csv

List the Transaction types and the corresponding domain values for the Oracle EBS R12 application.

For more information about updating the values in this file, see Section 5.2.2.2, "How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R12)").

SDE_ORA_TransactionTypeDimension_GLCOGSDerive

domainValues_Xact_Types_DocTypes_ora11i.csv

List the Transaction types and the corresponding domain values for the Oracle EBS 11i application.

For more information about updating the values in this file, see Section 5.2.2.3, "How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R11i)").

SDE_ORA_TransactionTypeDimension_GLCOGSDerive

5.2.2.2 How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R12)

This section explains how to configure Transaction Types for Oracle General Ledger and Profitability Analytics, Release 12, using the configuration file domainValues_Xact_Types_DocTypes_ora12.csv.

To configure Transaction types for Oracle General Ledger and Profitability Analytics:

  1. Identify the entry types in the Oracle Inventory application by using the following SQL:

    select mtt.transaction_type_id, mtt.description from mtl_transaction_types mtt;

    This query gives the transaction type codes in the Oracle Inventory application and their corresponding descriptions.

  2. Open the domainValues_Xact_Types_DocTypes_ora12.csv file using a text editor in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

    Look for only the rows in the file which has XACT_CODE = 'COGS' or XACT_CODE = 'DEFERRED_COGS'. From among these records, look for those with W_XACT_TYPE_CODE = 'DELIVERY' or 'RETURN'. The column XACT_TYPE_CODE will contain the entry type codes from the Oracle Inventory application, and the column W_XACT_TYPE_CODE is the corresponding domain value to which this entry type code will be mapped to.

  3. Map the entry type codes returned by the query above from the Oracle Inventory application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the DELIVERY/RETURN domain value. Be careful not to map the same entry type to two different domain values.

  4. Save and close the file.

  5. In Informatica PowerCenter Designer, open the mapping SDE_ORA_GLCOGSFact in the folder SDE_ORA<Version>_Adaptor.

  6. Open the mapplet mplt_BC_ORA_GLCOGSFact contained inside this mapping

  7. Open the Source Qualifier SQ_MTL_TRANSACTION_ACCOUNTS.

  8. Modify the SQL so that it extracts the additional transaction type IDs, from step 1, that need to be extracted.

  9. In Informatica PowerCenter Workflow Manager, make the same change to the SQL Override in the full load session SDE_ORA_GLCOGSFact_Full.

5.2.2.3 How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R11i)

This section explains how to configure Transaction Types for Oracle General Ledger and Profitability Analytics, Release 11i, using the configuration file domainValues_Xact_Types_DocTypes_ora11i.csv.

To configure Transaction types for Oracle General Ledger and Profitability Analytics:

  1. Identify the entry types in the Oracle Inventory application by using the following SQL:

    select mtt.transaction_type_id, mtt.description from mtl_transaction_types mtt;

    This query gives the transaction type codes in the Oracle Inventory application and their corresponding descriptions. The Oracle Inventory application is a data source from which Oracle General Ledger and Profitability Analytics extract data.

  2. Using a text editor, open the domainValues_Xact_Types_DocTypes_ora11i.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

    Look for only the rows in the file which has XACT_CODE = 'COGS'. From among these records, look for those with W_XACT_TYPE_CODE = 'DELIVERY' or 'RETURN'. The column XACT_TYPE_CODE will contain the entry type codes from the Oracle Inventory application, and the column W_XACT_TYPE_CODE is the corresponding domain value to which this entry type code will be mapped to.

  3. Map the entry type codes returned by the query above from your Oracle Inventory Application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the DELIVERY/RETURN domain value. Be careful not to map the same entry type to two different domain values.

  4. Save and close the file.

5.2.2.4 How to Specify the Ledger or Set of Books for which GL Data is Extracted

If you have an Oracle EBS source system, you can specify from which set of books or ledgers you extract the GL data.

Oracle BI Applications enables you to configure:

  • The list of ledgers or set of books from which to extract the GL data.

  • The types of ledgers or set of books for which to extract the GL data.

You can use either of these configuration points separately and combine them. When installed out-of-the-box, Oracle BI Applications extract all GL data for all ledgers or set of books.

For Oracle 11i customers, to configure the list of set of books to extract the GL data for, do the following:

  1. In DAC, go to the Design view, and select the custom container that you want to configure.

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.

  4. Double-click the Value column to open the text box, and enter 'Y'.

    Make sure that you put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$SET_OF_BOOKS_ID_LIST.

  7. Double-click the Value column to open the text box, and enter the IDs of the set of books for which you want to extract GL data for.

    Specify the list of set of book IDs separated by commas (do not enter single or double quotes).

    For example, if you want to extract GL data for set of books with IDs: 101, 207, and 303, then set the value of this parameter to the following:

    101, 207, 303

  8. Save the changes.

You can also specify the Set of Books type you want to extract GL data for. In Oracle 11i, there are three set of books types (this is based on the column GL_SETS_OF_BOOKS.mrc_sob_type_code):

  • P (Parent)

  • R (Reporting)

  • N (None)

For Oracle 11i customers, to configure the types of set of books to extract the GL data, do the following:

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

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.

  4. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure that you put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$SET_OF_BOOKS_TYPE_LIST.

  7. Double-click the Value column to open the text box, and enter the types of set of books for which you want to extract GL data.

    Specify the list of set of book IDs in single quotes and separated by commas.

    For example, if you want to extract GL data for all Parent set of books and all Reporting set of books, then set the value of this parameter as follows:

    'P', 'R'

  8. Save the changes.

For Oracle EBS R12 customers, to configure the list of ledgers to extract the GL data for, do the following:

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

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_LEDGER_ID.

  4. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure that you put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$LEDGER_ID_LIST.

  7. Double-click the Value column to open the text box, and enter the IDs of ledgers for which you want to extract GL data for.

    Specify the list of ledger IDs separated by commas (do not use single quotes).

    For example, if you want to extract GL data for ledgers with IDs: 101, 207, and 303, then set the value of this parameter as follows:

    101, 207, 303

  8. Save the changes.

You can also specify types of ledgers you want to extract GL data for. In Oracle EBS R12, there are four types of ledgers (this is based on the column GL_LEDGERS.ledger_category_code):

  • PRIMARY

  • SECONDARY

  • ALC

  • NONE

For R12 customers, to configure the types of ledgers to extract the GL data for, do the following:

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

  2. Display the Source System Parameters tab, and query for the parameter $$FILTER_BY_LEDGER_TYPE.

  3. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure that you put single quotes around the Y.

  4. Save the changes.

  5. Query for the parameter $$LEDGER_TYPE_LIST.

  6. Double-click the Value column to open the text box, and enter the types of ledgers for which you want to extract GL data for.

    Specify the list of set of book IDs in single quotes and separated by commas.

    For example, if you want to extract GL data for all Primary ledgers and all Secondary ledgers, then simply set the value of this parameter to as follows:

    'PRIMARY', 'SECONDARY'

  7. Save the changes.

5.2.3 Configuration Steps for Financial Analytics for PeopleSoft

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

5.2.3.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics

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

The section Section 5.2.3.2, "How to Configure Domain Values for Oracle Receivables Analytics" explains how to extract the lists of values from your source system, which you then compare with the seeded values. If the lists of values are different to the seeded values, you need to follow the instructions to configure the domain values and CSV worksheet files.

Table 5-2 lists the CSV worksheet files and the domain values for Financial Analytics and PeopleSoft in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

Table 5-2 Domain Values and CSV Worksheet Files for Oracle Financial Analytics

Worksheet File Name Description Session

domainValues_Xact_Types_DocTypes_psft.csv

List the Transaction types and the corresponding domain values for the PeopleSoft application.

For more information about updating the values in this file, see Section 5.2.3.2, "How to Configure Domain Values for Oracle Receivables Analytics.".

SDE_PSFT_TransactionTypeDimension_ARDerive

5.2.3.2 How to Configure Domain Values for Oracle Receivables Analytics

This section explains how to configure domain values for Oracle Receivables Profitability Analytics.

To configure domain values for Oracle Receivables Analytics:

  1. Identify the entry types in your Receivables application by using the following SQL:

    SELECT DISTINCT ENTRY_TYPE, DESCR FROM PS_ENTRY_TYPE_TBL

    This query gives the transaction type codes in your Oracle Inventory application and their corresponding descriptions. The Oracle Inventory application is a data source from which Oracle General Ledger and Profitability Analytics extract data.

  2. Using a text editor, open the domainValues_ Xact_Types_DocTypes_psft.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

    Look for only the rows in the file that have XACT_CODE = 'RECEIVABLE' and W_XACT_TYPE_CODE = 'ORIGINAL' or 'APPLICATION'. The column XACT_SUBTYPE_CODE will contain the entry type codes from PeopleSoft Receivables and the columns W_XACT_TYPE_CODE and W_XACT_SUBTYPE_CODE are the corresponding domain values these entry type codes will be mapped to.

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

    The data must be copied starting from the second line. The first line is the column header.

  4. Map the entry type codes returned by the query above from your PeopleSoft Receivable Application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the ORIGINAL/INVOICE domain value. Be careful not to map the same entry type to two different domain values.

  5. Save and close the file.

  6. In Informatica PowerCenter Designer, open mapplets mplt_BC_PSFT_ARTransactionFact_Item and mplt_BC_PSFT_ARTransactionFact_ItemActivity in the SDE_PSFT<Version>_Adaptor folder.

  7. Open the Source Qualifier transformation.

    At the end of the SQL, there is a 'IN' clause that should include all the entry type codes that map into W_XACT_TYPE_CODE = 'ORIGINAL' in the CSV file prepared above.

  8. In Informatica PowerCenter Workflow Manager, make the same change to the full load sessions that correspond to the SDE_PSFT_ARTransactionFact_Item_Full and SDE_PSFT_ARTransactionFact_ItemActivity_Full mappings.

5.2.3.3 How to insert beginning GL Balances into Oracle BI Applications

Before you run the initial ETL, make sure that you set the source system parameters in DAC in order to compute the GL balances correctly.

  1. In DAC, go to the Design View, then display the Source System Parameters tab.

  2. Set the following parameters:

    • Set the parameter $$INITIAL_EXTRACT_DATE to the date from which you want to extract data from your PeopleSoft application to the data warehouse. Ensure that this Parameter is the beginning of an accounting period and not just any date in the middle of a period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts from 5th June, set the date to 5th June, 2005.

    • Setup the parameter $$YEAR to the same as the fiscal year of $$INITIAL_EXTRACT_DATE parameter above. For example, if June 2005 is fiscal year 2005, set this parameter to 2005.

    • Set the parameter $$ACCOUNTING_PERIOD to the accounting period number of the $$INITIAL_EXTART_DATE parameter above. For example, if June 2005 is the 6th period of fiscal year 2005, set this parameter to 6.

5.2.3.4 Oracle GL Budget Analytics for PeopleSoft Configuration

PeopleSoft GL Budget Analytics provides data from the following two areas:

  • Commitment Control Budget

  • Standard General Ledger Budget

By default, the Data Warehouse is updated with Commitment Control Budget data. To add data from Standard General Ledger Budget to the data warehouse, you have to set up a configuration tag called Budget Standard in DAC. The tag should provide two options-an option for running only Commit Control Budget and an option for running both Commit Control Budget and Standard General Ledger Budget. DAC uses the Commitment Control Budget in the tag by default.

5.2.3.4.1 PeopleSoft Tree for Commitment Control Budget

You can set up Commitment Control Budget based on Chartfield and PeopleSoft trees. For instructions about how to perform this configuration, which involves adding the Chartfield hierarchy to the data warehouse, see Section 5.3.3.3, "General Ledger Chartfield Hierarchy Extraction Customization."

5.2.4 Configuration Steps for Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

This section contains configuration steps before you do a full data load that apply to Oracle's JD Edwards EnterpriseOne and JD Edwards World. It contains the following topics:

5.2.4.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

Table 5-3 lists the CSV worksheet files and the domain values for Financial Analytics for JD Edwards EnterpriseOne and JD Edwards World.

Table 5-3 Domain Values and CSV Worksheet Files for Oracle Financial Analytics for JD Edwards EnterpriseOne and JD Edwards World

Worksheet File Name Description Session

domain_values_payment_method_jde.csv

Lists the Payment Method and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Payment_Method_Dimension

domainvalues_src_Transaction_Type_Codes_jde.csv

domainvalues_lkp_Transaction_Type_Codes_jde.csv

Lists the Transaction Types and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Transaction_Type_Dimension

domainvalues_make_buy_indicator_product_jde.csv

Lists the Make Buy Indicator Product and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_ProductDimensions

file_fin_stmt_item_names_jde.csv

file_fin_sub_names_jde.csv

Lists the Financial Statement Item Name and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_CodeDimension_Fin_Stmt_Item_Names and SDE_JDE_CodeDimension_Fin_Sub_Names

file_group_acct_codes_jde.csv

Lists the Group Account Codes and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GroupAccountNumberDimension

file_grpact_fstmt.csv

Lists the Group Account financial Statement and the corresponding domain values for the JD Edwards EnterpriseOne or the JD Edwards World application.This file works in conjunction with the group_account_codes flatfile.

SIL_Stage_GroupAcctNumber_FinStatementItem

file_glacct_catcodes_segment_config_jde.csv

Lists the GL Account Category Codes Segment and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GLAccountDimension_CatCodesFlatFile

file_glacct_segment_config_jde.csv

Lists the GL Account Segment and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GLAccount_SegmentConfig_Extract

file_lkp_fiscal_period_Qtr_Config_jde.csv

Lists the Time Dimension Fiscal Period and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Timedimension_FiscalPeriod_Extract

5.2.4.2 How to Configure domainvalues_payment_method_jde.csv

This section explains how to configure the domainvalues_payment_method_jde.csv. You populate this lookup flat file with data before running the mapping to load W_PAYMENT_METHOD_D.

To configure this file

  1. Identify the Payment Methods in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select  
DRKY 
from F0005 
WHERE 
DRSY ='00  ' AND DRRT ='PY'

  2. Using a text editor, open the flat file domainvalues_payment_method_jde.csv located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).

  3. Copy the output of the query to the UDC column of the flat file.

  4. Map each of the values under the UDC column to one of the Payment Methods defined for the Oracle Business Analytics Warehouse and enter it under column W_PAYMENT_METHOD_D.

    For more information about the W_PAYMENT_METHOD_D domain values, see Oracle Business Analytics Warehouse Data Model Reference.

  5. Use commas to separate the entries under the UDC column and W_PAYMENT_METHOD_D. Ensure that you do not include any spaces.

  6. Save and close the file.

5.2.4.3 How to Configure domainvalues_src_transaction_type_codes_jde.csv and domainvalues_lkp_transaction_type_codes_jde.csv

This section explains how to configure the domainvalues_src_transaction_type_codes_jde.csv and domainvalues_lkp_transaction_type_codes_jde.csv files. These flat files are used as source flat files in SDE_JDE_Transaction_Type_Dimension to retrieve the value of W_XACT_TYPE_CODE, W_XACT_SUBTYPE_CODE, and RECEIPT_TYPE by matching the UDC value of the lookup table with the DRKY value from the source table.

If a UDC value from 00/DT or 98/IT does not map to one of the XACT_CODEs listed in the Transaction Types dimension class table for Financial Analytics, then do not include it in the domainvalues_src_transaction_type_codes_jde.csv file. For example, Document Type IA (Inventory Adjustments) does not map to a XACT_CODE domain value and therefore it should not be included in the CSV file.

Furthermore, review the UDC values that come pre-populated in the domainvalues_src_transaction_type_codes_jde.csv file and make any necessary changes to the pre-populated domain values in order to align with how these UDCs are used in your JD Edwards EnterpriseOne or JD Edwards World implementation. For example, if UDC = '%' does not correspond to any of the XACT_CODE domain values then remove the row from the domainvalues_src_transaction_type_codes_jde.csv file.

All UDCs that do correspond to a XACT_CODE domain values must be included in this CSV file and properly configured. If not, then metrics pertaining to the domain values (for example Payables and Receivables) are at risk for not calculating correctly.

For more information on XACT_CODE domain values, see Transaction Types Dimension Class Table for Financial Analytics which appears under the W_XACT_TYPE_D Domain Values in the Oracle Business Analytics Warehouse Data Model Reference.

To configure the CSV file

  1. Identify XACT_TYPE_CODE in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select  
 DRKY 
 From F0005 
 WHERE (DRSY ='00  ' AND DRRT ='DT') OR 
 (DRSY ='98  ' AND DRRT ='IT')

  2. Using a text editor, open the domainvalues_src_transaction_type_codes_jde.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).

  3. Using the output from the query, append any UDC values that are not in the CSV file to the end of the CSV file. This is necessary only if the UDC pertains to the XACT_CODE domain value.

  4. Map each UDC value to one domain value in each of these domain columns: XACT_CODE, W_XACT_TYPE_CODE, and W_XACT_SUBTYPE_CODE.

  5. Save and close the file.

  6. Copy and paste the domainvalues_src_transaction_type_codes_jde.csv into the $PMServer\lkpfiles location and change the file name to domainvalues_lkp_transaction_type_codes_jde.csv.

  7. Update the above files when necessary.

5.2.4.4 How to Configure domainvalues_make_buy_indicator_product_jde.csv

This section explains how to configure the domainvalues_make_buy_indicator_product_jde.csv file. Use this file to identify the Product Stocking Types in your JD Edwards EnterpriseOne or JD Edwards World source system. In W_PRODUCT_D, the MAKE_BUY_IND column represents the source system domain value.

To configure the domainvalues_make_buy_indicator_product_jde.csv

  1. Identify XACT_TYPE_CODE in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select  
 DRKY 
 From F0005 
 WHERE DRSY ='41  ' AND DRRT =' I'

  2. Using a text editor, open the domainvalues_make_buy_indicator_product_jde.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).

  3. Copy the output of the query to the UDC column of the flat file.

  4. Map each of the values under the Product Stocking Type column to the Make Buy Indicator column in the Oracle Business Analytics Warehouse.

    For more information about the W_PRODUCT_D domain values, see Oracle Business Analytics Warehouse Data Model Reference.

  5. Use commas to separate the entries under the Product Stocking Type and Make Buy Indicator columns. Make sure that you do not include any spaces.

  6. Save and close the file.

5.2.4.5 About the file_fin_stmt_item_names_jde.csv and file_fin_sub_names_jde.csv Files

The file_fin_stmt_item_names_jde.csv is used to identify the CODE-NAME combinations of Financial Statement Item Names. For more information about the published values, see Oracle Business Analytics Warehouse Data Model Reference. The file_fin_sub_names_jde.csv can be ignored.

The file_fin_stmt_item_names_jde.csv flat file is pre-populated; you do not have to configure it. However, you should review it to make sure the file is populated with values. The file_fin_sub_names_jde.csv flat file is also pre-populated. You can ignore this file, but do not delete it.

To review the files

  1. Using a text editor, open the file_fin_stmt_item_names_jde.csv located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

    The target for this data is W_CODE_D and therefore follows the typical CODE dimension load format.

  2. Save and close the file.

5.2.4.6 How to Configure the file_group_acct_codes_jde.csv

This section explains how to configure the file_group_acct_codes_jde.csv. This flat file is used to identify the Group Account Codes for each object account range for each company. For example, for company 00001 you might specify group account codes for accounts 1000 to 2000 as REVENUE.

For a detailed list of the domain values for all possible Group Account Codes, see Oracle Business Analytics Warehouse Data Model Reference.

To configure this file

  1. Using a text editor, open the file_group_acct_codes_jde.csv located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

  2. In the Company field, populate the company that you are setting up and specify the ranges in the From and To column of that row with the corresponding Group Account Code.

  3. Save and close the file.

5.2.4.7 How to Configure the file_glacct_catcodes_segment_config_jde.csv

This section explains how to configure the file_glacct_catcodes_segment_config_jde.csv. This file is used to determine which Account Segment Code (1 to 30) takes which F0901 column value for each record. You associate a segment code (1 to 30) to these columns in the F0901 account master: CO, MCU, OBJ, SUB, SBL, SBLT, and any of 43 category codes based on your reporting needs.

Determine the columns that you need for reporting and populate the flat file accordingly. Do not change the values of the SEGMENT_NAME column. The list of values that the JDE_COLUMN can take include GMCO, GMMCU, SBL, SBLT, GMSUB, GMOBJ, and any value from GMR001 through GMR0043.

To configure this file

  1. Using a text editor, open the file_glacct_catcodes_segment_config_jde.csv located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

  2. Add values beside the comma using the above domain values for the configuration. Ensure that you do not include any spaces.

  3. Save and close the file.

5.2.4.8 How to Configure the file_glacct_segment_config_jde.csv

This section explains how to configure the file_glacct_segment_config_jde.csv. This file is generic and determines which among the first six segment codes of account dimension is used for aggregation in the GL_BALANCE_F. The row with row_id = 1 is redundant; whether or not it is populated has no impact. However, the dummy row, available in the sample file, might be necessary. The role of this row with row_id = 1 is taken care of by the file_glacct_catcodes_segment_config_jde.csv file.

To configure this file

  1. Using a text editor, open the file_glacct_segment_config_jde.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

  2. In the row with ROW_ID = AGGREGATION, assign a value of Y to any of the first six segment codes to include these columns in the aggregation. Leave the trailing commas as such. Ensure that you do not include any spaces.

  3. Save and close the file.

5.2.4.9 How to Configure the file_lkp_fiscal_period_Qtr_Config_jde.csv

This section explains how to configure the file_lkp_fiscal_period_Qtr_Config_jde.csv. You must configure this file to support the metrics that are based on the Fiscal Quarter.

To configure this file

  1. Identify the Fiscal Quarter data in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select
CDDTPN,CDFY from F0008

  2. Using a text editor, open the file_lkp_fiscal_period_Qtr_Config_jde.csv located in $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

  3. For each Date pattern, set the following fields:

    Field Value
    FiscalPattern CDDTPN
    YEAR CDFY
    Period Period Number Numeric
    Quarter No As there is no concept of Quarter in JD Edwards EnterpriseOne and JD Edwards World, define a numeric quarter number to which the period belongs.
    Quarter Start Date Customized Quarter Start Date for each period. Each quarter can span as many periods as users configure. The format is DD/MM/YYYY.
    Quarter End Date Customized Quarter End Date for each period. Each quarter can span as many periods as users configure. The format is DD/MM/YYYY.

    Note:

    Make sure that there are no unnecessary spaces in the flat file.

  4. Save and close the file.

5.2.5 Configuration Steps for Financial Analytics for Universal

Not applicable to Oracle BI Applications Version 7.9.6.1.

5.3 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle Financial Analytics. It contains the following topics:

5.3.1 Configuration Steps for Financial Analytics for All Source Systems

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

5.3.1.1 How to Include Unapplied Payments in AR Aging Calculations

To include Unapplied Payments in Aging Calculations for AR Aging tables

  1. In Informatica Designer, open the mapping PLP_ARSnapshotInvoiceAging in the PLP Folder.

  2. Edit the Source Qualifier transformation SQ_IA_AR_XACTS, and open the SQL Query in the Properties tab.

  3. In the WHERE clause of the SQL statement:

    1. Change the SQL text from:

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO') AND

      To:

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO', 'PAYMENT') AND

    2. Change the SQL text from:

      W_XACT_TYPE_D.W_XACT_TYPE_CODE = 'ORIGINAL' AND

      To:

      W_XACT_TYPE_D.W_XACT_TYPE_CODE IN ('ORIGINAL','PAYMENT') AND

  4. Save the changes.

    The next ETL will populate the Aging tables using UnApplied payments in the calculations.

5.3.1.2 How to Configure the AP/AR Aging Tables

This section explains how to control the lengths of the aging buckets in the AP and AR aging snapshot tables. These tables are:

  • W_AP_AGING_INVOICE_A

  • W_AR_AGING_INVOICE_A

  • W_AP_AGING_SUPPLIER_A

  • W_AR_AGING_CUSTOMER_A

In these four tables, outstanding AP/AR balance information is broken out into rows. Each row represents the outstanding balance information for a particular aging bucket. Four aging buckets are provided out-of-the-box, with the following durations:

  • Bucket 1: 0 – 30 days

  • Bucket 2: 31 – 60 days

  • Bucket 3: 61 – 90 days

  • Bucket 4: 90+ days

Use DAC to configure the lengths of these aging buckets, as described below.

To configure the length of aging buckets

  1. In the Design view, display the Tasks tab.

  2. Query for the following tasks:

    • PLP_APSnapshotInvoiceAging

    • PLP_ARSnapshotInvoiceAging

  3. For each of these tasks, display the Parameters tab in the lower pane and update the parameters with names starting with '$$BUCKET'.

    You can also use the $$HISTORY_MONTHS parameter to specify how many historic month end snapshots you want to retain (the out-of-the-box value is 24 months).

    Note: You cannot create aging snapshots for months prior to your initial load ETL. Aging snapshots are created one month at a time, as you go forward with your regular ETL runs. For example, if you start your system in January 2009 (i.e. run the initial ETL some time in January 2009), and you set $$HISTORY_MONTHS to 12, you will have one snapshot at the end of January 2009, two at the end of February 2009, three at the end of March 2009 and so on until the end of December 2009 when you will have 12 snapshots.

  4. Save your changes.

5.3.1.3 How to Set Up Drill Down in Oracle BI Answers from General Ledger to Subledger

Note:

The Drill Down feature is not supported on Oracle JD Edwards source systems.

Oracle BI Applications enables you to trace a GL Journal to the subledger transaction that created that journal. This ability (or drill down) is achieved through the 'Navigation' feature in Oracle BI Answers.

To set up drill down in Oracle BI Answers from General Ledger to subledger

  1. Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.

  2. In your request, add a filter on the column 'GL Journal ID' under the 'Document Details' folder and the set the operator of the filter to 'Is Prompted'.

  3. Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.

  4. To your request, add the column 'GL Journal ID' under the 'Document Details' folder.

  5. Navigate to the Column Properties of this column, and set the Value Interaction property in the Column Format tab to 'Navigate'.

  6. Add a navigation target and set the target location to the sub ledger request you created earlier.

    You may add multiple navigation targets if your GL report shows transactions from multiple subledgers and you want to drill from GL to the appropriate Subledger report. For example, if your GL report shows transactions from AP, AR and Revenue, and you have three subledger reports for each of these, you can add three navigation targets (by selecting the option 'Add Navigation Targets') and set the locations to each of these reports. Subsequently, when you run the GL report and when you click on the "GL Journal ID" column Value, a popup appears, where you need to click on the appropriate target based on the journal you clicked on. This will not happen automatically. For example, if you click on a journal transaction originating from AP, you need to pick the appropriate subledger report (that is, the AP report in this case) to drill into the AP report and see the details. You can add the Group Account Number attribute from GL Account Dimension to your GL report to easily identify the subledger that the GL transaction belongs to.

Note:

For COGS, the 'GL Journal ID' column is not exposed in any presentation catalogs. It is available in the business model layer of the RPD metadata under the logical tables 'Dim - GL COGS Details'. As a workaround, you can create presentation catalogs to report of detail level transactions for COGS and expose this column under the 'Document Details' folder in the presentation catalog and use similar steps are above to setup a drill-down from GL to COGS.

To create a presentation catalog for COGS:

  1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.

  2. Create an empty presentation catalog (e.g. Financials – GL Cost of Goods Sold). Set properties by following other presentation catalogs.

  3. Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the presentation catalog.

  4. Drag other dimensions.

  5. Rename the presentation table 'Dim – GL COGS Details' to 'Document Details'.

  6. Rename the presentation table 'Fact - Fins - GL Cost of Goods Sold Posted' to 'Facts - GL Cost of Goods Sold'. Rename other dimensions if necessary.

You might also follow this same process to create a Presentation Table for Revenue to be able to drill from GL to Revenue level detail transactions.

5.3.1.4 About the Customer Costs Lines and Product Costs Lines Tables for Oracle Profitability Analytics

This configuration is required only if you are implementing Oracle Profitability Analytics and you want to allocate your expenses by product or by customer dimensions. The out-of-the-box adapter does not capture the miscellaneous cost and expenses associated with generating revenue from a customer or from a product (for example, marketing campaign expenses). You must provide this miscellaneous data via the Universal Adapter, as described in this section.

The configuration files file_customer_cost_line.csv and file_product_cost_line.csv are used to configure the Customer Cost Lines table and the Product Cost Lines table before an ETL full load. These configuration files are located out-of-the-box in \OracleBI\dwrep\Informatica\Sample Universal Source Files\.

Note: If these configuration files have file has been previously configured, updated copies of the files are located in the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder on the Informatica Server machine.

  • The file_customer_cost_line.csv file loads the Customer Costs Lines data in the W_CUSTOMER_COST_LINE_F table, and contains the following data:

    • Cost details by customers. The total cost by customers includes both direct cost that is captured in the financial system by products, and the allocated costs that are captured in the costing system.

    • The source of allocations. For example, the Sales and Marketing costs are not captured as direct costs by customers. However, at a later point in time, the costs could be allocated from a combination of General Ledger accounts or Cost Centers into various customers.

    • The source cost centers and General Ledger accounts. The Customer Costs can be categorized by Sales, Marketing, Service, and Operating costs. These could be further broken down to Salaries, Events, and Promotions. The Oracle Business Analytics Warehouse provides a set of categories, and these can be changed depending on the user needs and cost components by which customers are tracked.

    • The actual cost lines are also tracked in this table. In addition to Customers, there are number of other dimensions by which the costs can be tracked such as Sales Region, Sales Geography, Company, Business Area and the associated hierarchies. Not all dimension keys are relevant to all costs.

  • The file_product_cost_line.csv file loads the Product Costs Lines data in the W_PRODUCT_COST_LINE_F table, and contains the following data:

    • Cost details by products. The total cost by products include both direct cost that is captured in the financial system by products, and the allocated costs that are captured in the costing system.

    • The source of allocations. For example, the Sales and Marketing costs are not captured as direct costs by products. However, at a later point in time, the costs are allocated from a combination of General Ledger accounts and Cost Centers into various products.

    • The source cost centers and General Ledger accounts. The product costs can be categorized by Sales, Marketing, Service, and Operating costs. These could be further broken down into Salaries, Events, and Promotions. Oracle Business Analytics Warehouse provides a set of common categories, and these can be changed depending on the user needs and cost components by which products are tracked.

    • The actual cost lines. In addition to Products, there are number of other dimensions by which the costs are tracked such as Sales Region, Sales Geography, Company, Business Area, and the associated hierarchies. Not all dimension keys are relevant to all costs.

5.3.1.5 How to Configure the Customer Costs Lines and Product Costs Lines Tables for Oracle's Profitability Analytics

Follow this procedure to configure the Customer Cost Lines and Product Costs Lines before you perform a full load ETL.

Note: The General Ledger Revenue and General Ledger COGS fact tables are populated by either the Universal source system, the Oracle 11i source system, the JD Edwards EnterpriseOne source system, or the JD Edwards World source system. The Customer Costs Lines and Product Costs Lines fact tables are populated by the universal source system.

To configure the Customer Costs Lines and Product Costs Lines tables

  1. Copy the configuration files file_customer_cost_line.csv and file_product_cost_line.csv from the \OracleBI\dwrep\Informatica\Sample Universal Source Files\ folder to the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder on the Informatica Server machine.

    Note: You can omit this step if the configuration files have already been copied to the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder on the Informatica Server machine

  2. Using a text editor, open the file_customer_cost_line.csv file in the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder.

  3. Insert a record into the file for each customer costing transaction you want to load into the Customer Cost fact table.

  4. Save the file.

  5. Using a text editor, open the file_product_cost_line.csv file in the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder.

  6. Insert a record into the file for each product costing transaction you want to load into the Product Cost fact table.

  7. Save the file.

    You are now ready to perform a full load ETL to load the Customer Cost Lines and Product Costs Lines. To do this, use DAC to create and execute an Execution Plan that includes the Universal adapter Subject Areas 'Financials - Customer Expenses' and 'Financials - Product Expenses'.

5.3.1.6 How to Configure Budgets for Oracle Profitability Analytics

If you are using Oracle EBS, PeopleSoft, JD Edwards EnterpriseOne, or JD Edwards World source systems and would like to extract the budget data from these sources and import it into the data warehouse, you can use the preconfigured adapter mappings provided with Oracle Profitability Analytics. However, if you want to use budget data from other external systems, you can import the data into the data warehouse using the Universal adapter as described in this section. This section includes the following topics:

5.3.1.6.1 About Configuring Universal Source Files

The following tables describe the columns in the universal source CSV files file_budget.csv and file_acct_budget.csv, their data types, and how to populate them where applicable.

The table below shows the structure of the file_budget.csv file. The records in file_budget.csv are loaded into W_BUDGET_D.

Table 5-4 Universal Source for Budget Fact (file_budget.csv)

Column Name Datatype Size Description

BUDGET_NAME

string

80

Budget name.

BUDGET_VERSION

string

30

Budget version.

BUDGET_STATUS

string

30

Budget status.

BUDGET_TYPE

string

30

Budget type.

CREATED_BY_ID

string

80

ID of created by user. Populate with Integration_ID from w_user_d.

CHANGED_BY_ID

string

80

ID of changed by user. Populate with Integration_ID from w_user_d.

CREATED_ON_DT

string

14

Created date.

CHANGED_ON_DT

string

14

Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID already exists in the target table W_BUDGET_D, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_BUDGET_D. If this record's CHANGED_ON_DT is later than the record in W_BUDGET_D, then the load process will perform an update against the record in W_BUDGET_D; otherwise the load process will ignore this record, and no update or insertion will occur. If there is no matching record in W_BUDGET_D with the same integration_ID, then the load process will insert this record into W_BUDGET_D.

AUX1_CHANGED_ON_DT

string

14

  

AUX2_CHANGED_ON_DT

string

14

  

AUX3_CHANGED_ON_DT

string

14

  

AUX4_CHANGED_ON_DT

string

14

  

DELETE_FLG

string

1

  

DATASOURCE_NUM_ID

number

10

A number for your data source. Populate the same datasource_num_id as your main source application.

INTEGRATION_ID

string

80

A unique identifier for the record.

TENANT_ID

string

80

  

X_CUSTOM

string

10

  

The table below shows the structure of the file_acct_budget.csv file. The records in the file_acct_budget.csv will be loaded into W__ACCT_BUDGET_F.

Table 5-5 Universal Source for Budget Fact (file_acct_budget.csv)

Column Name Datatype Size Description

ADJUSTMENT_FLG

string

1

TBD

AUX1_CHANGED_ON_DT

string

14

TBD

AUX2_CHANGED_ON_DT

string

14

TBD

AUX3_CHANGED_ON_DT

string

14

TBD

AUX4_CHANGED_ON_DT

string

14

TBD

BUDG_BUSN_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where business_area_flg = Y.

BUDG_CTRL_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where ctrl_area_flg = Y.

BUDG_FIN_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where fin_area_flg = Y.

BUDGET_CALENDAR_ID

string

80

TBD

BUDGET_DOC_AMT

number

22

Budget amount in document currency.

BUDGET_GRP_AMT

number

22

TBD

BUDGET_ID

string

80

Populate with the value from integration_id in file_budget.csv

BUDGET_LEDGER_ID

string

80

TBD

BUDGET_LOC_AMT

number

22

Budget amount in local currency.

CHANGED_BY_ID

string

80

ID of changed by user. Populate with Integration_ID from w_user_d.

CHANGED_ON_DT

string

14

Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID exists in the target table W_ACCT_BUDGET_F already, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_ACCT_BUDGET_F. If this record's CHANGED_ON_DT is later than the record in W_ACCT_BUDGET_F, then the load process will perform an update against the record in W_ACCT_BUDGET_F; otherwise it'll ignore this record, no update or insertion will occur. If there's no matching record in W_ACCT_BUDGET_F with the same integration_ID, then the load process will insert this record into W_ACCT_BUDGET_F.

COMPANY_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where company_flg = Y.

COST_CENTER_ID

string

80

Cost Center identifier. Populate with integration_id from w_cost_center_d.

CREATED_BY_ID

string

80

ID of created by user. Populate with Integration_ID from w_user_d.

CREATED_ON_DT

string

14

Created date.

DATASOURCE_NUM_ID

number

10

A number for your data source. Populate the same datasource_num_id as your main source application.

DELETE_FLG

string

1

TBD

DOC_CURR_CODE

string

30

Document currency code.

GL_ACCOUNT_ID

string

80

GL Account identifier. Populate with integration_id from w_gl_account_d.

GRP_CURR_CODE

string

30

TBD

INTEGRATION_ID

string

80

A unique identifier for the record.

LOC_CURR_CODE

string

30

Local currency code.

PERIOD_BEGIN_DT

string

14

TBD

PERIOD_END_DT

string

14

Populate with your budget period's end date. If your budget is monthly, populate with the month end date.

POSTED_ON_DT

string

14

A date on which this transaction can be reported.

PRODUCT_ID

string

80

Product identifier. Populate with integration_id from w_product_d.

PROFIT_CENTER_ID

string

80

Profit Center identifier. Populate with integration_id from w_profit_center_d.

PROJECT_ID

string

80

  

TENANT_ID

string

80

TBD

X_CUSTOM

string

10

TBD

Note: Date columns should be populated in the CSV file as a number in the format YYYYMMDDHH24MISS.

Use the following table to understand how the integration_id (key) of some of the key dimensions are constructed for the Oracle EBS source system. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from Oracle EBS.

Table 5-6 Populating the integration_id fields in Oracle EBS Source Systems

Field How to populate

GL_ACCOUNT_ID (w_gl_account_d)

ccid

COMPANY_ORG_ID (w_int_org_d)

No need to populate; will be calculated based on GL Account ID.

COST_CENTER_ID (w_cost_center_d)

No need to populate; will be calculated based on GL Account ID.

PROFIT_CENTER_ID (w_profit_center_d)

No need to populate; will be calculated based on GL Account ID.

LEDGER_ID (w_ledger_d)

For Oracle 11i, populate as set of book ID. For Oracle R12, populate as ledger ID.

Use the following table to understand how the integration_id (key) of some of the key dimensions are constructed for Oracle's JD Edwards EnterpriseOne and JD Edwards World source systems. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from Oracle's JD Edwards EnterpriseOne or JD Edwards World.

Table 5-7 Populating the integration_id fields in Oracle's JD Edwards EnterpriseOne and JD Edwards World Source Systems

Field How to populate

GL_ACCOUNT_ID (w_gl_account_d_)

GBAID||'~'||GBSBL||'~'||GBSBLT

COMPANY_ORG_ID (w_int_org_d)

GBCO

COST_CENTER_ID (w_cost_center_d)

GBMCU

PROFIT_CENTER_ID (w_profit_center_d)

GBCO

LEDGER_ID (w_ledger_d)

GBCO

PRODUCT_ID (w_product_d)

If GBSBLT points to item, then update product ID with that GBSBL.

PROJECT_ID (w_product_d)

Not applicable

BUDG_BUSN_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDG_FIN_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDG_CTRL_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDGET_ID (w_budget_d)

Not applicable

5.3.1.6.2 How to Import Budget Data into the Data Warehouse Through the Universal Adapter

Follow these steps to import budget data into the data warehouse through the Universal adapter.

  1. Populate the file_budget.csv and file_acct_budget.csv files with your budget data.

    The CSV files are located in the $pmserver\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).

    Refer the tables above for details of how to populate these files.

  2. In DAC, create a new Execution Plan using the Subject Area 'Financials – Budget' in the 'Universal' container.

  3. Run the new Execution Plan that you created in the previous step.

    Note: This Execution Plan must be run after the regular Execution Plan to populate the data warehouse for the other Subject Areas has completed.

  4. Load new the new budget data or changes to existing budget data.

    Repeat Step 1 and Step 3 as needed to load new budget for the next fiscal period or make corrections to already loaded budget data.

5.3.2 Configuration Steps for Financial Analytics for Oracle EBS

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

5.3.2.1 How GL Balances Are Populated in Oracle EBS

Oracle BI Applications provides two ways to populate the GL balances (stored in the W_GL_BALANCE_F table), as follows:

  • By extracting the GL balances directly from Oracle General Ledger, as follows:

    1. In the DAC Design view, display the Subject Areas tab, and query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Oracle – Extract GL Balance'. Activate the tag by deselecting the Inactive check box.

    2. Inactivate the tag 'Financials – Calculate GL Balance by selecting the is Inactive check box.

    3. Click Assemble to reassemble the Subject Area.

    4. Rebuild the Execution Plans that contain this Subject Area.

  • By calculating the GL balances based on the records in the W_GL_OTHER_F table, which stores all journal lines, as follows:

    1. In the DAC Design view, display the Subject Areas tab, and query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Financials – Calculate GL Balance'. Activate the tag by deselecting the Inactive check box.

    2. Inactivate the tag 'Oracle – Extract GL Balance' by selecting the is Inactive check box.

    3. Click Assemble to reassemble the Subject Area.

    4. Rebuild the Execution Plans that contain this Subject Area.

5.3.2.2 How to Configure Oracle Profitability Analytics Transaction Extracts

There are two separate transaction extracts for Oracle General Ledger and Profitability Analytics—General Ledger Revenue and General Ledger COGS. By default, the Oracle General Ledger Analytics application extracts only Completed revenue and COGS that have been posted to the general ledger. Completed revenue transactions are those where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y. If you want to extract incomplete revenue transactions, you can remove the filter in the Business Component.

To modify the extract filter for Oracle Profitability Analytics Revenue

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_GLRevenueFact mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y

  4. Validate and save your changes to the repository.

5.3.2.3 How to Configure Cost Of Goods Extract (for Oracle EBS 11i)

For customers of EBS 11i, 11.5.8, 11.5.9, and 11.5.10, the Oracle General Ledger Analytics application extracts only COGS transactions that have been posted to the general ledger, by default. All COGS transactions that have been transferred satisfy the following condition— MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1. If you want to extract all transactions, you can remove the filter in the Business Component mapplet. You need to modify the Business Component mapplet for both the regular extract mapplet as well as the primary extract mapplet. This does not apply to EBS R12; by default all transactions are extracted. The following section applies only to EBS 11i customers.

To modify the extract filter for General Ledger COGS

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1

  4. In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact_Primary.

  5. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1

  6. Validate and save your changes to the repository.

    Note: Steps 4 to 6 are optional. Although the mplt_BC_ORA_GLCOGSFact_Primary mapplet is not used in any of the out-of-the-box Subject Areas, Oracle recommends that you follow steps 4 to 6 in case the mplt_BC_ORA_GLCOGSFact_Primary mapplet is used in a custom Subject Area.

  7. Make appropriate changes to the SQL in the Informatica session, 'SDE_ORA_GLCOGSFact_Full', which is present in the corresponding folder in Informatica PowerCenter Workflow Manager.

  8. If you customized the mapping SDE_ORA_GLCOGSFact to extract from Oracle EBS versions 11.5.8, 11.5.9, or 11.5.10, modify the domain values file domainValues_Xact_Types_DocTypes_ora11i.csv.

  9. To extract from Oracle EBS version R12, modify the file domainValues_Xact_Types_DocTypes_ora12.csv.

5.3.2.4 How to Configure AP Balance ID for Oracle Payables Analytics

The AP Balance ID controls the level at which the balance in W_AP_BALANCE_F is maintained. This section contains configuration information for Oracle Payables Analytics that is specific to Oracle. By default, the Accounts Payable (AP) Balance ID is maintained at the following granularity:

SET_OF_BOOKS_ID||'~'||CODE_COMBINATION_ID||'~'||VENDOR_SITE_ID||'~'||ORG_ID||'~'||VENDOR_ID

However, if you want to maintain your AP balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.

To modify the Accounts Payable Balance ID

Note:

To modify the Accounts Payable Balance ID, you must modify the following mapplets:

  • mplt_SA_ORA_APTransactionFact_LiabilityDistribution

  • mplt_SA_ORA_APTransactionFact_ExpenseDistribution

  • mplt_SA_ORA_APTransactionFact_Payment

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_APTransactionFact_LiabilityDistribution).

  3. Double-click the Expression transformation to open the Edit Transformations dialog.

  4. Display the Ports tab, and edit the expression for the EXT_BALANCE_ID port.

    For example, you might click on the Expression field to display the Expression Editor and modify the text in the Formula box.

  5. Validate and save your changes to the repository.

  6. Repeat steps 1 to 5 for each mapplet that is listed above.

5.3.2.5 How to Configure AR Balance ID for Oracle Receivables Analytics and Oracle General Ledger and Profitability Analytics

The AR Balance ID controls the level at which the balance in W_AR_BALANCE_F is maintained.

By default, the AR Balance ID is maintained at the following granularity:

set_of_books_id || '~' || code_combination_id || '~' || customer_id || '~' || customer_site_use_id || '~' transaction_currency_code || '~' || org_id

However, if you want to maintain your AR balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.

To modify the AR Balance ID

Note:

To modify the AR Balance ID, you must modify the following mapplets:

  • mplt_SA_ORA_ARTransactionFact_Adjust

  • mplt_SA_ORA_ARTransactionFact_ARScheduleDerive

  • mplt_SA_ORA_ARTransactionFact_CreditMemoApplication

  • mplt_SA_ORA_ARTransactionFact_ReceivableApplication

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_ARTransactionFact_Adjust).

  3. Double-click the Expression transformation to open the Edit Transformations dialog.

  4. Display the Ports tab, and edit the expression for the EXT_NU_AR_BALANCE_ID port.

    For example, you might click on the Expression field to display the Expression Editor and modify the text in the Formula box.

  5. Validate and save your changes to the repository.

  6. Repeat steps 1 to 5 for each mapplet that is listed above.

5.3.2.6 How to Configure the AR Adjustments Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only approved adjustment entries against accounts receivable transactions. Approved adjustments are entries where the AR_ADJUSTMENTS_ALL.STATUS = A. If you want to extract additional types of AR adjustment entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that require more research, those that are rejected, and those that are not accrued charges.

To modify the extract filter for Accounts Receivable adjustments

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_Adjust mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the SQL Query field and in the User Defined Join field, modify the statement:

    AND AR_ADJUSTMENTS_ALL.STATUS = A

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_AdjustPrimary mapplet.

5.3.2.7 How to Configure the AR Schedules Extract

By default, Oracle Receivables Analytics extracts only completed schedules; that is, transactions where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y. If you want to extract additional types of AR schedule entries, you must remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that were marked as incomplete.

To modify the extract filter for Accounts Receivable schedules

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ARSchedules mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y

  4. Validate and save your changes to the repository.

5.3.2.8 How to Configure the AR Cash Receipt Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, cash-receipt application entries against accounts receivable transactions. Confirmed receipts are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of cash-receipt application entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as unconfirmed applications.

You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplication) as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary).

To modify the extract filter for AR cash receipt application

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ReceivableApplication mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary mapplet.

5.3.2.9 How to Configure the AR Credit-Memo Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, credit-memo application entries against accounts receivable transactions. Confirmed credit memos are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of AR credit-memo application entries, you can remove the filter. By modifying or removing the filter, you can extract other entries such as unconfirmed, credit memos.

You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplication), as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary). Repeat the following procedure for each mapplet.

To modify the extract filter for Accounts Receivable Credit-Memo Application

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_CreditmemoApplication mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary mapplet.

5.3.2.10 How to Enable Project Analytics Integration with Financial Subject Areas

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

You can configure the following Oracle Financial Analytics for EBS Subject Areas to use Project Analytics for EBS tables:

  • Financials - Budget

  • Financials -Payables

  • Financials -Receivables

  • Financials - Cost of Goods Sold

  • Financials - General Ledger

  • Financials - Revenue

The following Oracle Financial Analytics fact tables integrate with Project Analytics dimensions:

  • W_ACCT_BUDGET_F

  • W_AP_INV_DIST_F

  • W_AP_XACT_F

  • W_AR_XACT_F

  • W_GL_COGS_F

  • W_GL_REVN_F

  • W_AP_AGING_INVOICE_A

  • W_AR_AGING_INVOICE_A

To enable the integration

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

  2. Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.

  3. Assemble the Subject Areas.

  4. Click the Execute button and build the Execution Plan for the Subject Areas that you updated.

5.3.3 Configuration Steps for Financial Analytics for PeopleSoft

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

5.3.3.1 Overview of PeopleSoft Tree Data Structures

For PeopleSoft, the Oracle Financial Analytics application uses data from a data structure called PeopleSoft trees to get information about the organization's GL account hierarchies, profit center hierarchies, cost center hierarchies, and so on.

PeopleSoft trees are a flexible, generic way of constructing hierarchical relationships among PeopleSoft entities, such as chartfields, business units, and so on.

5.3.3.2 About PeopleSoft Trees in Oracle Financial Analytics

Oracle BI Applications provides a set of mechanisms to support various tree structures in PeopleSoft applications. Table 5-8 shows the supported structure types, and Table 5-9 shows the supported level types.

Table 5-8 PeopleSoft Tree Structure Types

Structure Type Support

Winter tree

Y

Detail tree

Y

Summer tree

Y

Table 5-9 PeopleSoft Tree Level Types

Level Type Support

Strict-level tree

Y

Loose-level tree

Y

A flattened structure is used to store the hierarchy-related data. Each record of W_HIERARCHY_D and other hierarchy-related dimension tables (for example, W_INT_ORG_DH) represents a single node in the tree. It stores the path information from the top-most node to that node. For the bottom nodes in the detail tree, the extraction process generates a number of records according to the node's range and the record in the relevant table and puts those records into the hierarchy table. Figure 5-1 provides an illustration of this functionality.

Figure 5-1 Example GL Chartfield Hierarchy

This diagram is described in surrounding text.

For any node in the tree, except for the bottom nodes, W_HIERARCHY_D will have one corresponding record. For example, for the 'E' node in the tree above (the one highlighted with a blue square), the record shown in Table 5-10 will appear in the database.

Table 5-10 How Node E Is Stored in W_HIERARCHY_D Table

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER[5-20]_CODE

US

E

E

E

E

If the actual hierarchy level in the tree ends earlier than the predefined level of W_HIERARCHY_D, then the lower hierarchy level in the table will repeat the same value as its next higher level. The predefined level is 20, which means if one path of the tree only has five levels, then levels six through 20 will repeat the value of level five.

For the leaf node in the detail tree shown in Figure 5-1, W_HIERARCHY_D will have a record for every existing record within this detail range. The extraction process will find the single record in the relevant table according to the tree's structure. For example, in the CA node (the one with the red square in Figure 5-1), the range is 2334-2434 and 3001-3001. The state table (for example, State_TBL) contains two records: 2340 and 3001. When generating the CA node, the extraction process will extract these two records and put them into W_HIERARCH_D, as shown in Table 5-11.

Table 5-11 Example of CSV Chartfield Mapping Values

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER5_CODE

US

W

CA

2340

2340

US

W

CA

3001

3001

With both the Summer tree and detail tree structure types, the lowest level node is always a node with a specific value. For the winter tree structure type, the lowest level code is the same as the higher level, as illustrated in Figure 5-2.

Figure 5-2 Winter Loose-Level Tree

This diagram is described in surrounding text.

The record 'Kuo' is stored in the database as shown in Table 5-12.

Table 5-12 Winter Loose-Level Tree

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER5_CODE

Smith

Cheng

Kuo

Kuo

Kuo

Oracle BI Applications uses temporary tables and staging tables to handle the extraction and load process of the tree. These tables are standard and not tree-specific or tree-structure specific. Table 5-13 lists the tables used during the GL Hierarchy extraction process. The sequence indicates the order in which these tables are populated.

Table 5-13 GL Chartfield Hierarchy

Sequence Table Name

1

W_PSFT_TREE_TMP

2

W_PSFT_GLHIER_CF_DH_TMP

3

W_PSFT_GLHIER_DTLRGE_DH_TMP

4

W_PSFT_GLHIER_VERT_DH_TMP

5

W_PSFT_GLHIER_FLAT_DH_TMP

6

W_HIERARCHY_DS

Table 5-14 lists the tables used during the Internal Organization Hierarchy extraction process.

Table 5-14 Internal Organization Hierarchy

Sequence Table Name

1

W_PSFT_INT_ORG_TREE_TMP

2

W_PSFT_INT_ORG_GLBU_DH_TMP

3

W_PSFT_INT_ORG_DTLRGE_DH_TMP

4

W_PSFT_INT_ORG_VERT_DH_TMP

5

W_PSFT_INT_ORG_FLAT_DH_TMP

6

W_INT_ORG_DHS

7

W_INT_ORG_DH

Note:

The hierarchy related extraction and load tasks are inactivated in the standard application. To activate the hierarchy extractions and load tasks, you need to activate certain configuration tags in DAC, as described below.

Using DAC Configuration Tags to Activate and Inactivate Tasks

The DAC's Configuration Tag feature allows you to activate or inactivate a certain set of tasks in the ETL process. When inactivated, the set of tasks will not be executed when the Execution Plan runs. When activated, the set of tasks will be executed when the Execution Plan runs.

Oracle BI Applications includes two configuration tags for hierarchy extractions and loads, as follows:

  • 'PeopleSoft - Populate Non Business Unit Related Hierarchies,' which contains all but business unit-related hierarchies' extractions and load tasks.

  • 'PeopleSoft - Populate Business Unit Related Hierarchies', which contains business unit-related hierarchies' extractions and load tasks.

By activating and inactivating configuration tags in a Subject Area, you can control whether the hierarchy extraction and load tasks get executed in the Execution Plan.

To activate or inactivate a set of tasks in your ETL process

  1. In the DAC Design view, select the PeopleSoft custom container from the drop-down list to the right of the Execute button.

  2. Display the Subject Areas tab.

  3. For each Financials Subject Area, do the following:

    1. Select the Financials Subject Area in the top pane, for example Cost of Goods Sold, General Ledger, and so on.

    2. In the bottom pane, display the Configuration Tags subtab.

    3. Deselect the Inactive check box of the hierarchy you want to enable, as follows:

      - For GL Chartfield, deselect the Inactive check box for PeopleSoft - Populate Non Business Unit Related Hierarchies.

      - For Business Unit, deselect the Inactive check box for PeopleSoft - Populate Business Unit Related Hierarchies.

    4. Save the changes.

  4. Click Assemble to reassemble the Subject Areas that you have modified.

  5. Go to the Execute view, and display the Execute view.

  6. For every Execution Plan that contains the Subject Areas that you modified, rebuild the Execution Plan.

  7. Save the Execution Plans that you modified.

Sample Mappings for Internal Organization Hierarchy and GL Chartfield Hierarchy

Oracle BI Applications provides two sets of mappings to extract Internal Organization Hierarchy and General Ledger Chartfield Hierarchy related trees. You can customize the extractions by copying the mappings and modifying the parameters. For instructions, see Section 5.3.3.3, "General Ledger Chartfield Hierarchy Extraction Customization".

5.3.3.3 General Ledger Chartfield Hierarchy Extraction Customization

This section provides an example of how to customize mappings for a GL Chartfield Hierarchy extraction. In this example, the extraction involves a tree named DEPARTMENT_HIERARCHY with SetID XYZ on chartfield Department, which uses DEPT_TBL as its structure's Record Name and DEPTID as the Field Name.

The mappings that require customization for this example are shown in Table 5-15.

Table 5-15 General Ledger Chartfield Hierarchy Mappings

Execution Sequence Mapping Name Needs Customization?

1

SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

Y

2

SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

Y

3

SDE_PSFT_GLSegmentDimension_GLAccount

Y

4

SDE_PSFT_Stage_GLHierarchy_GetHierarchyLevel

N

5

SDE_PSFT_Stage_GLHierarchy_DeriveRange

N

6

SDE_PSFT_Stage_GLHierarchy_Flatten

N

7

SDE_PSFT_GLHierarchy_Derive

N

The steps required to perform this extraction are as follows:

  1. In Informatica PowerCenter Designer, create new mappings by copying the following existing mappings:

    • SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

    • SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

    • SDE_PSFT_GLSegmentDimension_GLAccount

    Name the new mappings as follows:

    • SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract

    • SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField

    • SDE_PSFT_GLSegmentDimension_Department

  2. Open the mapping SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract and edit the settings as follows:

    1. In SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract, open the source definition PSFT_TREE_VERTICAL_FLATTENING. Under the 'Properties' tab, change the Tree Name to DEPARTMENT_HIERARCHY, and the SetID to XYZ.

    2. Open the Exp_TreeName_SetID Expression transformation, and change the values as follows:

      - Change the value of TREE_NAME to DEPARTMENT_HIERARCHY.

      - Change the value of SETID to XYZ.

      - Change the value of CHARTFIELD to Department. (This name is the same as that defined in file_glacct_segment_config_psft.csv.)

  4. Open the mapping SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField and edit the settings as follows:

    - In the SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField mapping, change the source table and Source Qualifier to DEPT_TBL.

    - Connect SETID, DEPTID, EFFDT, EFF_STATUS, and DESCR from the Source Qualifier to the corresponding fields on the filter Filter_GLACCT (connect DEPTID to CHARTFIELD).

    - In the Expression transformation Exp_TMPGLACCT (the last Expression transformation of this mapping), change the Expression field SOURCE_CHARTFIELD_NAME to Department. (This is the same name as the source table.)

  5. Open the mapping SDE_PSFT_GLSegmentDimension_Department and edit the settings as follows:

    1. Delete the source, then import DEPT_TBL from source system through the Source Analyzer view, drag the imported source to this mapping, and delete the original Source and Source Qualifier after the import.

    2. Connect SETID, DEPTID, EFFDT, EFF_STATUS, and DESCR from the Source Qualifier to the corresponding fields in the filter Filter_GLACCT (connect DEPTID to CHARTFIELD).

    3. In the Expression transformation Exp_CHARTFIELD_NAME (the last Expression transformation of this mapping), change the Expression field SOURCE_CHARTFIELD_NAME to Department.

  6. Save all your changes in the Informatica folder.

  7. Add corresponding sessions and workflows in Informatica PowerCenter Workflow Manager, as follows:

    1. In Informatica PowerCenter Workflow Manager, locate the appropriate PeopleSoft folder (for example, SDE_PSFT_<Version>_Adaptor).

    2. Open the Task Developer and create a new Workflow for the mappings you just created.

      Tip: To quickly create a new Workflow, copy the SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract workflow and rename it to SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract.

    3. Edit the task in the new SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract Workflow, and use the various tabs on the Edit Tasks dialog to modify the parameters as specified in the table below.

      Note: Do not change the values of the other mappings.

      Table 5-16 General Ledger Chartfield Hierarchy Mappings

      Tab Name Parameter Name Value

      Properties

      Parameter Filename

      SDE_PSFT_88_Adaptor.<mapping_name>.log

      Properties

      $Source connection value

      $DBConnection_OLTP

      Properties

      $Target connection value

      $DBConnection_OLAP

      Properties

      DTM buffer size

      32000000

      Config Object

      Default buffer block size

      128000

      Config Object

      Stop on errors

      1

      Config Object

      Override tracing

      Normal

      Mapping

      Connection W_PSFT_TREE_TMP - Rational

      $DBConnection_OLAP

      Mapping

      Connection SQ_PSFT_TREE_VERTICAL_FLATTENING - Application

      $DBConnection_OLTP

      Mapping

      Connection W_PSFT_TREE_TMP1 - Rational

      $DBConnection_OLAP

      Mapping

      Connection Lkp_Tree_ChartField - Rational

      $DBConnection_OLTP

      Mapping

      SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract – Session $Source Connection Value

      $DBConnection_OLTP

      Mapping

      SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract – Session $Target Connection Value

      $DBConnection_OLAP

    4. Save your changes.

    5. In Informatica PowerCenter Workflow Manager, create a workflow with the same name as the session.

    6. Drag the newly created session to the panel.

    7. Connect the start with the session.

    8. Create a similar session and workflow for SDE_PSFT_GLSegmentDimension_Department.

  8. Save and check-in all your changes in Informatica PowerCenter Designer and Informatica PowerCenter Workflow Manager.

  9. Reconfigure the DAC settings, as follows:

    1. Log in to DAC, open your custom container, and display the Tasks tab.

    2. Add the three workflows that you just created as new tasks.

      - SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract

      - SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField

      - SDE_PSFT_GLSegmentDimension_Department

      For information about what values to set task property values to, refer to the following tasks:

      - SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

      - SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

      - SDE_PSFT_GLSegmentDimension_GLAccount

    3. Right-click on each task and select Synchronize tasks.

      The Synchronize tasks process reads the source table and target table information for these tasks from the Informatica Repository. To see this information, go to the Tasks tab and display the Source Tables subtab and the Target Tables subtab.

    4. Add the newly created tasks into the DAC task groups.

      Note: Because intermediate temporary tables are reused, it is necessary to include the newly added tasks to task groups to prevent over-loading or pretruncating of data. GL Hierarchy Extract has the following task groups:

      - TASK_GROUP_Extract_GLHierarchy

      - TASK_GROUP_Extract_GLHierarchy_GetChartFieldValues

      - TASK_GROUP_Extract_GLSegmentDimension

      For this example, you need to include the newly added tasks into task groups as follows:

      - Add SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract to the task group TASK_GROUP_Extract_GLHierarchy

      - Add SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField to task group TASK_GROUP_Extract_GLHierarchy_GetChartFieldValues

      - Add SDE_PSFT_GLSegmentDimension_Department to task group TASK_GROUP_Extract_GLSegmentDimension

      The steps for adding a task to a task group are as follows:

      - In the DAC's Design view, select the Task Group tab.

      - In the top pane, select the appropriate task group.

      - Display the Child Tasks subtab, and then click Add/Remove in the bottom pane toolbar.

      - Query for the appropriate task, and then click Add.

      - Click OK, and then click Save.

    5. Add SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract and SDE_PSFT_Stage_GLHierarchy_GetAccountChartField tasks as configuration tags in DAC:

      - In the Design view, click Configuration Tags.

      - Query for the tag PeopleSoft - Populate Non Business Unit Related Hierarchies.

      - Display the Tasks subtab, and then click Add/Remove in the bottom pane toolbar.

      - Query for the new mappings you just entered into DAC.

      - Select the mappings, and add them into the right panel.

      - Click OK, and then click Save.

    6. Reassemble all the financial-related Subject Areas:

      - In the Subject Areas tab, select the Subject Area, and click Assemble in the tool bar.

      - Repeat this step for all of the financial-related Subject Areas (for example, Cost of Goods Sold, General Ledger, Payables, Receivables, Revenue)

    7. Rebuild the Execution Plans that contain the Subject Areas you reassembled:

      - Go to the Execute view, and display the Execution Plans tab.

      - Select the appropriate Execution Plan (for example, Financials_PeopleSoft).

      - Click Build in the toolbar.

5.3.3.4 How to Set Up Default Fiscal Calendars For PeopleSoft

In Oracle Business Intelligence Applications Version 7.9.6.1, the semantic layer (RPD) metadata contains session variables to store the current fiscal year, fiscal quarter, fiscal period and so on for a user. Since multiple fiscal calendars are supported in Version 7.9.6.1, you need to get the default fiscal calendar for a user based on the Ledger or Business Unit assigned to the user, and then get the current fiscal year, quarter and so on based on this default fiscal calendar.

The following initialization blocks need to be created in the RPD metadata to get the default fiscal calendar for a user. These initialization blocks read information from the PeopleSoft security tables such as PS_SEC_BU_OPR, PS_SEC_BU_CLS, PS_SEC_LEDGER_OPR, PS_SEC_LEDGER_CLS.

To set up default Fiscal Calendar for PeopleSoft:

  1. Create an Initialization Block to get the Business Unit for a User, as follows:

    This Initialization Block gets one Business Unit for a user. This could be a GL BU, AP BU, AR BU etc.

    1. Create a new Initialization Block called Operating Unit for Fiscal Calendar, and populate a variable called OU_ORG_FSCL_CALENDAR.

    2. Use the following SQL for the Initialization Block.

      SELECT MAX(BUSINESS_UNIT) FROM (
SELECT BUSEC.BUSINESS_UNIT
FROM PS_SEC_BU_OPR BUSEC, PS_INSTALLATION_FS INST
WHERE INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y'AND BUSEC.OPRID = ':USER'
UNION ALL
SELECT BUSEC.BUSINESS_UNIT
FROM PS_SEC_BU_CLS BUSEC, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND BUSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER');

      Note: You need to use the PeopleSoft OLTP Connection Pool for this initialization block.

  2. Create Initialization Block to get Ledger for a User, as follows:

    This Initialization Block gets one GLBU+Ledger combination for a user, since calendar is defined at a GLBU+Ledger combination in PeopleSoft, not on Ledger alone.

    1. Create a new Initialization Block called Ledger for Fiscal Calendar, and populate a variable called LEDGER_FSCL_CALENDAR.

    2. Use the following SQL for the Initialization Block.

      Note: Please remove the comments in the SQL below (lines beginning with "--") from the actual SQL in the initialization block.

      SELECT MAX(LEDGER_ID_FOR_MCAL) FROM (<insert the appropriate SQL from the SQL code below, based on the user level and business filters>.

      -- If security is set at user level with filters on Business Unit and Ledger.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_OPR BUSEC, PS_SEC_LEDGER_OPR LEDSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY = 'Y' AND BUSEC.OPRID = ':USER' AND LEDSEC.OPRID = ':USER' UNION ALL

      -- If security is set at user level with filters on Business Unit only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_OPR BUSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY = 'N' AND BUSEC.OPRID = ':USER' UNION ALL

      -- If security is set at user level with filters on Ledger only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_LEDGER_OPR LEDSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE 
LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'N' AND LEDGER_SECURITY = 'Y' AND LEDSEC.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Business Unit and Ledger.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_CLS BUSEC, PS_SEC_LEDGER_CLS LEDSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY = 'Y' AND LEDSEC.OPRCLASS = OPR.OPRCLASS AND BUSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Business Unit only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_CLS BUSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY = 'N' AND BUSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Ledger only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_LEDGER_CLS LEDSEC, PS_BU_LED_GRP_TBL A,  PS_SET_CNTRL_REC B,  PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'N' AND LEDGER_SECURITY = 'Y' AND LEDSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER');

      Note: You need to use the PeopleSoft OLTP Connection Pool for this initialization block.

  3. Modify the Initialization Block to get a default Calendar for a user, as follows:

    1. Open the Initialization Block "Mcal Calendar Wid Identification".

      The out-of-the-box SQL code is:

      select max(MCAL_CAL_WID) from VALUEOF(OLAPTBO).W_MCAL_CONTEXT_G where
((ledger_id=valueof(NQ_SESSION.LEDGER)) or (org_id=valueof(NQ_SESSION.OU_ORG)')) AND CLASS='GL'

    2. Replace NQ_SESSION.LEDGER with NQ_SESSION.LEDGER_FSCL_CALENDAR; NQ_SESSION.OU_ORG with NQ_SESSION.OU_ORG_FSCL_CALENDAR.

    3. Edit the Execution Precedence, and remove the existing two Initialization Blocks (Ledgers and Operating Unit Organizations) that are currently set.

    4. Add the two newly created Initialization Blocks above.

      Note: If Security is completely turned OFF in PeopleSoft, you may disable all these Initialization Blocks.

5.3.3.5 How to Enable Project Analytics Integration with Financial Subject Areas

You can enable Oracle Financial Analytics for PeopleSoft 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 DAC, select the PeopleSoft source system container depending on the version of the OLTP that you are using.

  2. Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.

  3. Assemble the Subject Areas.

  4. Click the Execute button and build the Execution Plan for the Subject Areas that you updated.

5.3.3.6 Configuring Number of Days based Metrics for PeopleSoft

For certain metrics to function properly, you must configure the following two internal metrics in the Oracle BI Applications analytics repository (RPD):

  • # of Elapsed Days

  • # of Cumulative Elapsed Days

These metrics affect the calculation of other metrics, such as Days Sales Outstanding, Days Payables Outstanding, AP Turnover, AR Turnover, and so forth.

To configure Number of Days based metrics

  1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.

  2. In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.

  3. Under Sources, select the Fact_W_DAY_D_PSFT logical table source.

  4. Click the Active check box in the General tab and click OK.

  5. Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and clear the Active check box.

  6. Add the "Fact - Fins - Period Days Count" and "Dim - Company" logical tables to the Business Model Diagram. To do so, right click and select Business Model Diagram, Selected Tables Only.

  7. In the Logical Table diagram, create a New Complex Join from "Dim - Company" to "Fact - Fins - Period Days Count." The direction of the foreign key should be from the "Dim - Company" logical table to the "Fact - Fins - Period Days Count" table. For example, on a (0,1):N cardinality join, "Dim - Company" will be on the (0/1) side and "Fact - Fins - Period Days Count" will be on the N side.

  8. Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.

  9. Go to the Levels tab. For the Company dimension, the Logical Level is set to All. Click the X button to the right of the All setting to remove it. Repeat until the Company dimension does not have a Logical Level setting.

  10. Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.

5.3.3.7 How to Update Dashboard Pages with PeopleSoft Prompts

Data Source specific dashboard prompts are provided with Financial Analytics to accommodate source specific filtering across all application Dashboard pages. You need to add each PeopleSoft dashboard prompt listed in Table 5-17 to its associated dashboard page as part of the application configuration process.

Table 5-17 Financial Analytics Dashboard Pages with Pre-configured PeopleSoft Path and Prompt Names

Dashboard Dashboard Page Catalog Shared Folder (Shared Folders/Financials) PeopleSoft Prompt Name

General Ledger

Overview

/General Ledger/Key Ratios

Oracle PSFT - GL Key Ratios Prompt

General Ledger

Balance Sheet

/General Ledger/Balance Sheet

Oracle PSFT - GL Balance Sheet Prompt

General Ledger

Cash Flow

/General Ledger/Cash Flow

Oracle PSFT - GL Cash Flow Prompt

General Ledger

Budget vs. Actual

/General Ledger/Budget Actual

Oracle PSFT - GL Budget Prompt

General Ledger

Asset Usage

/General Ledger/Asset Usage

Oracle PSFT - GL Asset Usage Prompt

General Ledger

Liquidity

/General Ledger/Liquidity

Oracle PSFT - GL Liquidity Prompt

General Ledger

Financial Structure

/General Ledger/Financial Structure

Oracle PSFT - GL Financial Structure Prompt

General Ledger

GL Balance

/General Ledger/Transactions

Oracle PSFT - GL Balance Transactions Prompt

General Ledger

Trial Balance

/General Ledger/Trial Balance

Oracle PSFT - GL Trial Balance Prompt

Payables

Overview

/Payables/Overview

Oracle PSFT - AP Overview Prompt

Payables

AP Balance

/Payables/AP Balance

Oracle PSFT - AP Balance Prompt

Payables

Payments Due

/Payables/Payments Due

Oracle PSFT - AP Payments Due Prompt

Payables

Effectiveness

/Payables/Effectiveness

Oracle PSFT - AP Effectiveness Prompt

Payables

Payment Performance

/Payables/Payment Performance

Oracle PSFT - AP Payment Performance Prompt

Payables

Supplier Report

/Payables/Supplier Report

Oracle PSFT - AP Supplier Report Prompt

Payables

Invoice Details

/Payables/Invoice Details

Oracle PSFT - AP Invoice Details Prompt

Payables

All AP Transactions

/Payables/All AP Transactions

Oracle PSFT - AP Txn Prompt

Profitability

Overview

/Profitability/Overview

Oracle PSFT - GL Profitability Overview Prompt

Profitability

P&L

/Profitability/P&L

Oracle PSFT - GL Profitability P&L Prompt

Profitability

Margins

/Profitability/Margins

Oracle PSFT - GL Profitability Margins Prompt

Profitability

Revenue

/Profitability/Revenue

Oracle PSFT - GL Profitability Revenue Prompt

Profitability

Products

/Profitability/Products

Oracle PSFT - GL Profitability Products Prompt

Profitability

Customers

/Profitability/Customers

Oracle PSFT - GL Profitability Customer Prompt

Receivables

Overview

/Receivables/Overview

Oracle PSFT - AR Overview Prompt

Receivables

AR Balance

/Receivables/AR Balance

Oracle PSFT - AR Balance Prompt

Receivables

Payments Due

/Receivables/Payments Due

Oracle PSFT - AR Payments Due Prompt

Receivables

Effectiveness

/Receivables/Effectiveness

Oracle PSFT - AR Effectiveness Prompt

Receivables

Payment Performance

/Receivables/Payment Performance

Oracle PSFT - AR Payment Performance Prompt

Receivables

Customer Report

/Receivables/Supplier Report

Oracle PSFT - AR Supplier Report Prompt

Receivables

Invoice Details

/Receivables/Invoice Details

Oracle PSFT - AR Invoice Details Prompt

Receivables

All AR Transactions

/Receivables/All AR Transactions

Oracle PSFT - AR Transactions Prompt

To update dashboard pages with PeopleSoft prompts

These instructions explain how to modify the General Ledger dashboard's Overview page prompt as an example of how to modify a prompt.

  1. Access the dashboard page.

  2. Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.

  3. Remove the existing dashboard prompt from the top section in Dashboard Editor.

    For the Overview page in the General Ledger dashboard, remove the "Oracle EBS - GL Key Ratios Prompt" from Section 1.

    Note:

    Remove the prompt, not the Section.

  4. From the selection pane in the Saved Content area, browse to the Shared Folders where the dashboard prompt to be used for this dashboard page is stored.

    For the Overview page in the General Ledger dashboard, the catalog path is stored in the following location:

    /Shared folders/Financials/General Ledger/Key Ratios Prompt name:

    Oracle PSFT - GL Key Ratios Prompt

  5. Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.

  6. Click the Save button to save the dashboard page and exit Dashboard Editor.

    This updates the dashboard page with the PeopleSoft prompt.

  7. Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-17.

5.3.4 Configuration Steps for Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

This section contains additional configuration steps that apply to Oracle's JD Edwards EnterpriseOne and JD Edwards World. It contains the following topics:

5.3.4.1 Configuring GL Balances in Oracle's JD Edwards EnterpriseOne and JD Edwards World

For JD Edwards EnterpriseOne and JD Edwards World GL balances, only Actual Ledger Type is supported. Using a mapplet parameter, you can identify and pass multiple Actual Ledger Types, if required. For Ledger Types AA, A1, and A2, you run the ETL map by passing the value through DAC as AA, A1, A2 (comma separated value) for $$LEDGER_TYPE_ACTUAL. By default, the value for Ledger Type is AA.

W_GL_BALANCE_F supports only Actual ledgers; ledgers with foreign transactions should not be included.

To configure Actual Ledger Type in DAC

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

  2. Display the Tasks tab, and perform a query for the task SDE_JDE_GLBalanceFact.

  3. In the Detail area of the selected task, display the Parameters tab. Modify the value in the $$LEDGER_TYPE_ACTUAL parameter, for example:

    $$LEDGER_TYPE_ACTUAL= 'AA', 'A1', 'A2'

  4. Save your changes.

5.3.4.2 Configuring the DAC Parameter for Receivables

The Transaction Type Code dimension table contains a UDC column for UDCs, which are user defined codes in the JD Edwards EnterpriseOne source system. By default, the UDC is set to "RA" in the dimension table. You can override the value of the $$WRITE_OFF_DOC_TYPE DAC parameter to specify any UDC in the source system.

If you configure the DAC parameter to any other value available in the JD Edwards EnterpriseOne source system, then you must open the domainvalues_src_transaction_type_codes_jde.csv file and modify the UDC column values to reflect the UDC that you entered in the DAC parameter.

The following table is an example of two entries in the CSV file in which you would replace RA in the UDC column with the user defined code specified in the DAC parameter.

DRSY DRRT UDC RECEIPT_TYPE XACT_CAT_CODE XACT_CODE W_XACT_TYPE_CODE W_XACT_SUBTYPE_CODE
0 DT RA   ACCT_DOC RECEIVABLE APPLICATION ADJUSTMENT APPLICATION
0 DT RA A ACCT_DOC RECEIVABLE APPLICATION ADJUSTMENT APPLICATION

To configure the $$WRITE_OFF_DOC_TYPE DAC parameter

  1. In DAC, display the Design view.

  2. Select the Financials - Receivables row.

  3. Click Tasks in the Design area on the left side of the screen, and then highlight the SDE_JDE_AR_Transaction_Fact_Extract task.

  4. Display the Parameters sub-tab.

  5. Select the row with the $$WRITE_OFF_DOC_TYPE parameter, and then double-click in the Value column.

  6. In the Enter Parameter Value screen, replace 'RA' with a UDC in the source system. Make sure that the UDC is in single quotes.

  7. Open the domainvalues_src_transaction_type_codes_jde.csv file and modify the UDC column values to reflect the UDC that you entered in the DAC parameter.

5.3.4.3 Configuring the DAC Parameter when Multicurrency Processing is Disabled in JD Edwards

LOC_CURR_CODE and ACCT_CURR_CODE in the Data Warehouse are non-nullable fields. Unless they are populated, insertion into the Data Warehouse will fail. If multicurrency processing is disabled in JD Edwards EnterpriseOne or JD Edwards World, then you must set the $$DOC_CURR_CODE parameter in DAC to the domestic or base currency in use. By default, the value of this parameter is set to NULL in DAC.

To configure the $$DOC_CURR_CODE parameter

  1. In the DAC client, click the Design tab.

  2. Select the Source System Parameters tab.

  3. Select the row with the $$DOC_CURR_CODE parameter, and then double-click in the Value column.

  4. In the Enter Parameter Value screen, replace NULL with the domestic or base currency in use.

5.3.4.4 Configuring Number of Days based Metrics

For certain metrics to function properly, you must configure the following two internal metrics in the Oracle BI Applications analytics repository (RPD):

  • # of Elapsed Days

  • # of Cumulative Elapsed Days

These metrics affect the calculation of other metrics, such as Days Sales Outstanding, Days Payables Outstanding, AP Turnover, AR Turnover, and so forth.

To configure Number of Days based metrics

  1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.

  2. In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.

  3. Under Sources, select the Fact_W_DAY_D_JDE logical table source.

  4. Click the Active check box in the General tab and click OK.

  5. Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and clear the Active check box.

  6. Add the "Fact - Fins - Period Days Count" and "Dim - Company" logical tables to the Business Model Diagram. To do so, right click and select Business Model Diagram, Selected Tables Only.

  7. In the Logical Table diagram, create a New Complex Join from "Dim - Company" to "Fact - Fins - Period Days Count." The direction of the foreign key should be from the "Dim - Company" logical table to the "Fact - Fins - Period Days Count" table. For example, on a (0,1):N cardinality join, "Dim - Company" will be on the (0/1) side and "Fact - Fins - Period Days Count" will be on the N side.

  8. Similarly, in the Logical Table diagram, add the following logical tables:

    • Fact - Fins - Period Days Count

    • Dim - Ledger

    • Dim - Receivables Org

    • Dim - Payables Org

  9. Click the joins between the three Logical Dimension table joins and the Logical Fact table and delete them.

  10. Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.

  11. Go to the Levels tab. For the Company dimension, the Logical Level is set to All. Click the X button to the right of the All setting to remove it. Repeat until the Company dimension does not have a Logical Level setting.

  12. Set the Logical Level to All for these three dimensions:

    • Ledger

    • Payables Org

    • Receivables Org

  13. Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.

5.3.4.5 How to Update Dashboard Pages with Oracle's JD Edwards EnterpriseOne and JD Edwards World Prompts

Data source specific dashboard prompts are provided with Oracle Financial Analytics to accommodate source specific filtering across all application Dashboard pages. You need to add each JD Edwards EnterpriseOne or JD Edwards World dashboard prompt listed in Table 5-18 to its associated dashboard page as part of the application configuration process. Follow the steps in this section to update existing dashboard prompts with the designated JD Edwards EnterpriseOne or JD Edwards World prompt.

Table 5-18 Financial Analytics Dashboard Pages with Pre-configured JD Edwards EnterpriseOne or JD Edwards World Path and Prompt Names

Dashboard Dashboard Page Catalog Shared Folder (Shared Folders/Financials) JD Edwards EnterpriseOne/JD Edwards World Prompt Name

General Ledger

Overview

/General Ledger/Key Ratios

Oracle JDE - GL Key Ratios Prompt

General Ledger

Balance Sheet

/General Ledger/Balance Sheets

Oracle JDE - GL Balance Sheet Prompt

General Ledger

Cash Flow

/General Ledger/Cash Flow

Oracle JDE - GL Cash Flow Prompt

General Ledger

Budget vs. Actual

/General Ledger/Budget Actual

Oracle JDE - GL Budget Prompt

General Ledger

Asset Usage

/General Ledger/Asset Usage

Oracle JDE - GL Asset Usage Prompt

General Ledger

Liquidity

/General Ledger/Liquidity

Oracle JDE - GL Liquidity Prompt

General Ledger

Financial Structure

/General Ledger/Financial Structure

Oracle JDE - GL Financial Structure Prompt

General Ledger

GL Balance

/General Ledger/Transactions

Oracle JDE - GL Balance Transactions Prompt

General Ledger

Trial Balance

/General Ledger/Trial Balance

Oracle JDE - GL Trial Balance Prompt

Payables

Overview

/Payables/Overview

Oracle JDE - AP Overview Prompt

Payables

AP Balance

/Payables/AP Balance

Oracle JDE - AP Balance Prompt

Payables

Payments Due

/Payables/Payments Due

Oracle JDE - AP Payments Due Prompt

Payables

Effectiveness

/Payables/Effectiveness

Oracle JDE - AP Effectiveness Prompt

Payables

Payment Performance

/Payables/Payment Performance

Oracle JDE - AP Payment Performance Prompt

Payables

Supplier Report

/Payables/Supplier Report

Oracle JDE - AP Supplier Report Prompt

Payables

Invoice Details

/Payables/Invoice Details

Oracle JDE - AP Invoice Details Prompt

Payables

All AP Transactions

/Payables/All AP Transactions

Oracle JDE - AP Txn Prompt

Profitability

Overview

/Profitability/Overview

Oracle JDE - GL Profitability Overview Prompt

Profitability

P&L

/Profitability/P&L

Oracle JDE - GL Profitability P&L Prompt

Profitability

Margins

/Profitability/Margins

Oracle JDE - GL Profitability Margins Prompt

Profitability

Revenue

/Profitability/Revenue

Oracle JDE - GL Profitability Revenue Prompt

Profitability

Products

/Profitability/Products

Oracle JDE - GL Profitability Products Prompt

Profitability

Customers

/Profitability/Customers

Oracle JDE - GL Profitability Customers Prompt

Receivables

Overview

/Receivables/Overview

Oracle JDE - AR Overview Prompt

Receivables

AP Balance

/Receivables/AP Balance

Oracle JDE - AR Balance Prompt

Receivables

Payments Due

/Receivables/Payments Due

Oracle JDE - AR Payments Due Prompt

Receivables

Effectiveness

/Receivables/Effectiveness

Oracle JDE - AR Effectiveness Prompt

Receivables

Payment Performance

/Receivables/Payment Performance

Oracle JDE - AR Payment Performance Prompt

Receivables

Supplier Report

/Receivables/Supplier Report

Oracle JDE - AR Supplier Report Prompt

Receivables

Invoice Details

/Receivables/Invoice Details

Oracle JDE - AR Invoice Details Prompt

Receivables

All AR Transactions

/Receivables/All AR Transactions

Oracle JDE - AR Transaction Prompts

To update dashboard pages with JD Edwards EnterpriseOne or JD Edwards World prompts

These instructions explain how to modify the General Ledger dashboard's Overview page prompt as an example of how to modify a prompt.

  1. Access the dashboard page.

  2. Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.

  3. Remove the existing dashboard prompt from the top section in Dashboard Editor.

    For the Overview page in the General Ledger dashboard, remove the "Oracle EBS - GL Key Ratios Prompt" from Section 1.

    Note:

    Remove the prompt, not the Section.

  4. From the selection pane in the Saved Content area, browse to the Shared Folders where the dashboard prompt to be used for this dashboard page is stored.

    For the Overview page in the General Ledger dashboard, the catalog path is stored in the following location:

    /Shared folders/Financials/General Ledger/Key Ratios Prompt name:

    Oracle JDE - GL Key Ratios Prompt

  5. Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.

  6. Click the Save button to save the dashboard page and exit Dashboard Editor.

    This updates the dashboard page with the JD Edwards EnterpriseOne or JD Edwards World prompt.

  7. Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-18.

5.3.5 Configuration Steps for Financial Analytics for Universal

Not applicable to Oracle BI Applications Version 7.9.6.1.

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us