Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.6.1 Part Number E14844-01 |
|
|
View PDF |
This section describes how to configure Oracle Financial Analytics. It contains the following topics:
Section 5.2, "Configuration Required Before a Full Load for Financial Analytics"
Section 5.3, "Configuration Steps for Controlling Your Data Set"
Oracle Financial Analytics consists of the following:
Oracle General Ledger and Profitability Analytics. The General Ledger Analytics application provides information to support your enterprise's balance sheet and provides a detailed analysis on the overall health of your company. The default configuration for the General Ledger Analytics application is based on what is identified as the most-common level of detail or granularity. However, you can configure and modify the extracts to best meet your business requirements.
The Profitability Analytics application provides cost analysis, revenue trends, and sales performance to provide an accurate picture of profit and loss. The information found in the Profitability Analytics application pertains to data found in the revenue and expense account groupings of your financial statements and chart of accounts. The Profitability Analytics application is designed to provide insight into your enterprise's revenue and profitability information, which ties into your accounts receivable. The default configuration for the Profitability Analytics application is based on what is identified as the most-common level of detail, or granularity. However, the extracts are configurable and you can modify the extracts to meet your business requirements. The Profitability Analytics application provides cost analysis, revenue trends, and profitability analysis at the products and customer level, and the income statement at the company and business divisions level.
Oracle Payables Analytics. The Oracle Payables Analytics application provides information about your enterprise's accounts payable information and identifies the cash requirements to meet your obligations.
The information found in the Oracle Payables Analytics application pertains to data found exclusively under Accounts Payable (AP) in your financial statements and chart of accounts. Analysis of your payables allows you to evaluate the efficiency of your cash outflows. The need for analysis is increasingly important because suppliers are becoming strategic business partners with the focus on increased efficiency for just in time, and quality purchasing relationships.
The default configuration for the Oracle Payables Analytics application is based on what is identified as the most- common level of detail, or granularity. However, you can configure or modify the extracts to best meet your business requirements.
Oracle Receivables Analytics. The Oracle Receivables Analytics application provides information to support your credit and collection activities, and to monitor and identify potential, receivables problems.
The information found in the Oracle Receivables Analytics application pertains to data found exclusively in the Accounts Receivable (AR) account grouping of your financial statements and chart of accounts. Each day that your receivables are past the due date represents a significant, opportunity-cost to your company. Keeping a close eye on the trends, and clearing of AR is one way to assess the efficiency of your sales operations, the quality of your receivables, and the value of key customers.
The default configuration for the Oracle Receivables Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
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:
Section 5.2.1, "Configuration Steps for Financial Analytics for All Source Systems"
Section 5.2.2, "Configuration Steps for Financial Analytics for Oracle EBS"
Section 5.2.3, "Configuration Steps for Financial Analytics for PeopleSoft"
Section 5.2.5, "Configuration Steps for Financial Analytics for Universal"
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".
This section contains configuration steps before you do a full data load that apply to Oracle EBS. It contains the following topics:
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 |
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:
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.
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.
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.
Save and close the file.
In Informatica PowerCenter Designer, open the mapping SDE_ORA_GLCOGSFact in the folder SDE_ORA<Version>_Adaptor.
Open the mapplet mplt_BC_ORA_GLCOGSFact contained inside this mapping
Open the Source Qualifier SQ_MTL_TRANSACTION_ACCOUNTS.
Modify the SQL so that it extracts the additional transaction type IDs, from step 1, that need to be extracted.
In Informatica PowerCenter Workflow Manager, make the same change to the SQL Override in the full load session SDE_ORA_GLCOGSFact_Full.
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:
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.
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.
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.
Save and close the file.
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:
In DAC, go to the Design view, and select the custom container that you want to configure.
Display the Source System Parameters tab.
Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.
Double-click the Value column to open the text box, and enter 'Y'.
Make sure that you put single quotes around the Y.
Save the changes.
Query for the parameter $$SET_OF_BOOKS_ID_LIST.
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
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:
In DAC, go to the Design view, and select your custom container from the drop-down list.
Display the Source System Parameters tab.
Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.
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.
Save the changes.
Query for the parameter $$SET_OF_BOOKS_TYPE_LIST.
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'
Save the changes.
For Oracle EBS R12 customers, to configure the list of ledgers to extract the GL data for, do the following:
In DAC, go to the Design view, and select your custom container from the drop-down list.
Display the Source System Parameters tab.
Query for the parameter $$FILTER_BY_LEDGER_ID.
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.
Save the changes.
Query for the parameter $$LEDGER_ID_LIST.
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
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:
In DAC, in go to the Design view, and select your source container from the drop-down list.
Display the Source System Parameters tab, and query for the parameter $$FILTER_BY_LEDGER_TYPE.
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.
Save the changes.
Query for the parameter $$LEDGER_TYPE_LIST.
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'
Save the changes.
This section contains configuration steps before you do a full data load that apply to PeopleSoft. It contains the following topics:
Section 5.2.3.2, "How to Configure Domain Values for Oracle Receivables Analytics"
Section 5.2.3.3, "How to insert beginning GL Balances into Oracle BI Applications"
Section 5.2.3.4, "Oracle GL Budget Analytics for PeopleSoft Configuration"
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 |
This section explains how to configure domain values for Oracle Receivables Profitability Analytics.
To configure domain values for Oracle Receivables Analytics:
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.
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.
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.
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.
Save and close the file.
In Informatica PowerCenter Designer, open mapplets mplt_BC_PSFT_ARTransactionFact_Item and mplt_BC_PSFT_ARTransactionFact_ItemActivity in the SDE_PSFT<Version>_Adaptor folder.
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.
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.
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.
In DAC, go to the Design View, then display the Source System Parameters tab.
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.
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.
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."
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:
Section 5.2.4.2, "How to Configure domainvalues_payment_method_jde.csv"
Section 5.2.4.4, "How to Configure domainvalues_make_buy_indicator_product_jde.csv"
Section 5.2.4.5, "About the file_fin_stmt_item_names_jde.csv and file_fin_sub_names_jde.csv Files"
Section 5.2.4.6, "How to Configure the file_group_acct_codes_jde.csv"
Section 5.2.4.7, "How to Configure the file_glacct_catcodes_segment_config_jde.csv"
Section 5.2.4.8, "How to Configure the file_glacct_segment_config_jde.csv"
Section 5.2.4.9, "How to Configure the file_lkp_fiscal_period_Qtr_Config_jde.csv"
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 |
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
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'
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).
Copy the output of the query to the UDC column of the flat file.
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.
Use commas to separate the entries under the UDC column and W_PAYMENT_METHOD_D. Ensure that you do not include any spaces.
Save and close the file.
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
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')
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).
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.
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.
Save and close the file.
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.
Update the above files when necessary.
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
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'
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).
Copy the output of the query to the UDC column of the flat file.
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.
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.
Save and close the file.
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
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.
Save and close the file.
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
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).
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.
Save and close the file.
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
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).
Add values beside the comma using the above domain values for the configuration. Ensure that you do not include any spaces.
Save and close the file.
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
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).
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.
Save and close the file.
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
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
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).
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.Save and close the file.
Not applicable to Oracle BI Applications Version 7.9.6.1.
This section contains additional configuration steps for Oracle Financial Analytics. It contains the following topics:
Section 5.3.1, "Configuration Steps for Financial Analytics for All Source Systems"
Section 5.3.2, "Configuration Steps for Financial Analytics for Oracle EBS"
Section 5.3.3, "Configuration Steps for Financial Analytics for PeopleSoft"
Section 5.3.5, "Configuration Steps for Financial Analytics for Universal"
This section contains additional configuration steps that apply to all source systems. It contains the following topics:
Section 5.3.1.1, "How to Include Unapplied Payments in AR Aging Calculations"
Section 5.3.1.3, "How to Set Up Drill Down in Oracle BI Answers from General Ledger to Subledger"
Section 5.3.1.6, "How to Configure Budgets for Oracle Profitability Analytics"
To include Unapplied Payments in Aging Calculations for AR Aging tables
In Informatica Designer, open the mapping PLP_ARSnapshotInvoiceAging in the PLP Folder.
Edit the Source Qualifier transformation SQ_IA_AR_XACTS, and open the SQL Query in the Properties tab.
In the WHERE clause of the SQL statement:
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
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
Save the changes.
The next ETL will populate the Aging tables using UnApplied payments in the calculations.
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
In the Design view, display the Tasks tab.
Query for the following tasks:
PLP_APSnapshotInvoiceAging
PLP_ARSnapshotInvoiceAging
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.
Save your changes.
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
Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.
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'.
Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.
To your request, add the column 'GL Journal ID' under the 'Document Details' folder.
Navigate to the Column Properties of this column, and set the Value Interaction property in the Column Format tab to 'Navigate'.
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:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Create an empty presentation catalog (e.g. Financials – GL Cost of Goods Sold). Set properties by following other presentation catalogs.
Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the presentation catalog.
Drag other dimensions.
Rename the presentation table 'Dim – GL COGS Details' to 'Document Details'.
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.
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.
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
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
Using a text editor, open the file_customer_cost_line.csv file in the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder.
Insert a record into the file for each customer costing transaction you want to load into the Customer Cost fact table.
Save the file.
Using a text editor, open the file_product_cost_line.csv file in the \Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles\ folder.
Insert a record into the file for each product costing transaction you want to load into the Product Cost fact table.
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'.
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:
Section 5.3.1.6.1, "About Configuring Universal Source Files"
Section 5.3.1.6.2, "How to Import Budget Data into the Data Warehouse Through the Universal Adapter"
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 |
Follow these steps to import budget data into the data warehouse through the Universal adapter.
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.
In DAC, create a new Execution Plan using the Subject Area 'Financials – Budget' in the 'Universal' container.
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.
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.
This section contains additional configuration steps that apply to Oracle EBS. It contains the following topics:
Section 5.3.2.1, "How GL Balances Are Populated in Oracle EBS"
Section 5.3.2.2, "How to Configure Oracle Profitability Analytics Transaction Extracts"
Section 5.3.2.3, "How to Configure Cost Of Goods Extract (for Oracle EBS 11i)"
Section 5.3.2.4, "How to Configure AP Balance ID for Oracle Payables Analytics"
Section 5.3.2.6, "How to Configure the AR Adjustments Extract for Oracle Receivables Analytics"
Section 5.3.2.7, "How to Configure the AR Schedules Extract"
Section 5.3.2.10, "How to Enable Project Analytics Integration with Financial Subject Areas"
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:
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.
Inactivate the tag 'Financials – Calculate GL Balance by selecting the is Inactive check box.
Click Assemble to reassemble the Subject Area.
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:
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.
Inactivate the tag 'Oracle – Extract GL Balance' by selecting the is Inactive check box.
Click Assemble to reassemble the Subject Area.
Rebuild the Execution Plans that contain this Subject Area.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mplt_BC_ORA_GLRevenueFact
mapplet.
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
Validate and save your changes to the repository.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact
.
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
In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact_Primary
.
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
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.
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.
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.
To extract from Oracle EBS version R12, modify the file domainValues_Xact_Types_DocTypes_ora12.csv.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_APTransactionFact_LiabilityDistribution).
Double-click the Expression transformation to open the Edit Transformations dialog.
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.
Validate and save your changes to the repository.
Repeat steps 1 to 5 for each mapplet that is listed above.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_ARTransactionFact_Adjust).
Double-click the Expression transformation to open the Edit Transformations dialog.
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.
Validate and save your changes to the repository.
Repeat steps 1 to 5 for each mapplet that is listed above.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_Adjust
mapplet.
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
Validate and save your changes to the repository.
Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_AdjustPrimary mapplet.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ARSchedules
mapplet.
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
Validate and save your changes to the repository.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ReceivableApplication
mapplet.
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
Validate and save your changes to the repository.
Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary mapplet.
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
In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORA<Version>_Adaptor).
In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_CreditmemoApplication
mapplet.
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
Validate and save your changes to the repository.
Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary
mapplet.
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
In DAC, select the Oracle EBS source system container depending on the version of the OLTP that you are using.
Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
Assemble the Subject Areas.
Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
This section contains additional configuration steps that apply to PeopleSoft. It contains the following topics:
Section 5.3.3.1, "Overview of PeopleSoft Tree Data Structures"
Section 5.3.3.2, "About PeopleSoft Trees in Oracle Financial Analytics"
Section 5.3.3.3, "General Ledger Chartfield Hierarchy Extraction Customization"
Section 5.3.3.4, "How to Set Up Default Fiscal Calendars For PeopleSoft"
Section 5.3.3.5, "How to Enable Project Analytics Integration with Financial Subject Areas"
Section 5.3.3.6, "Configuring Number of Days based Metrics for PeopleSoft"
Section 5.3.3.7, "How to Update Dashboard Pages with PeopleSoft Prompts"
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.
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 |
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.
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.
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
In the DAC Design view, select the PeopleSoft custom container from the drop-down list to the right of the Execute button.
Display the Subject Areas tab.
For each Financials Subject Area, do the following:
Select the Financials Subject Area in the top pane, for example Cost of Goods Sold, General Ledger, and so on.
In the bottom pane, display the Configuration Tags subtab.
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.
Save the changes.
Click Assemble to reassemble the Subject Areas that you have modified.
Go to the Execute view, and display the Execute view.
For every Execution Plan that contains the Subject Areas that you modified, rebuild the Execution Plan.
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".
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:
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
Open the mapping SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract and edit the settings as follows:
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.
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.)
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.)
Open the mapping SDE_PSFT_GLSegmentDimension_Department and edit the settings as follows:
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.
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).
In the Expression transformation Exp_CHARTFIELD_NAME (the last Expression transformation of this mapping), change the Expression field SOURCE_CHARTFIELD_NAME to Department.
Save all your changes in the Informatica folder.
Add corresponding sessions and workflows in Informatica PowerCenter Workflow Manager, as follows:
In Informatica PowerCenter Workflow Manager, locate the appropriate PeopleSoft folder (for example, SDE_PSFT_<Version>_Adaptor).
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.
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 |
Save your changes.
In Informatica PowerCenter Workflow Manager, create a workflow with the same name as the session.
Drag the newly created session to the panel.
Connect the start with the session.
Create a similar session and workflow for SDE_PSFT_GLSegmentDimension_Department.
Save and check-in all your changes in Informatica PowerCenter Designer and Informatica PowerCenter Workflow Manager.
Reconfigure the DAC settings, as follows:
Log in to DAC, open your custom container, and display the Tasks tab.
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
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.
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.
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.
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)
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.
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:
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.
Create a new Initialization Block called Operating Unit for Fiscal Calendar, and populate a variable called OU_ORG_FSCL_CALENDAR.
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.
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.
Create a new Initialization Block called Ledger for Fiscal Calendar, and populate a variable called LEDGER_FSCL_CALENDAR.
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.
Modify the Initialization Block to get a default Calendar for a user, as follows:
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'
Replace NQ_SESSION.LEDGER with NQ_SESSION.LEDGER_FSCL_CALENDAR; NQ_SESSION.OU_ORG with NQ_SESSION.OU_ORG_FSCL_CALENDAR.
Edit the Execution Precedence, and remove the existing two Initialization Blocks (Ledgers and Operating Unit Organizations) that are currently set.
Add the two newly created Initialization Blocks above.
Note: If Security is completely turned OFF in PeopleSoft, you may disable all these Initialization Blocks.
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
In DAC, select the PeopleSoft source system container depending on the version of the OLTP that you are using.
Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
Assemble the Subject Areas.
Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
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
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.
Under Sources, select the Fact_W_DAY_D_PSFT logical table source.
Click the Active check box in the General tab and click OK.
Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and clear the Active check box.
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.
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.
Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.
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.
Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.
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.
Access the dashboard page.
Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.
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.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
Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.
Click the Save button to save the dashboard page and exit Dashboard Editor.
This updates the dashboard page with the PeopleSoft prompt.
Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-17.
This section contains additional configuration steps that apply to Oracle's JD Edwards EnterpriseOne and JD Edwards World. It contains the following topics:
Section 5.3.4.1, "Configuring GL Balances in Oracle's JD Edwards EnterpriseOne and JD Edwards World"
Section 5.3.4.2, "Configuring the DAC Parameter for Receivables"
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
In DAC, select the Design view and then select your custom container from the drop-down list.
Display the Tasks tab, and perform a query for the task SDE_JDE_GLBalanceFact.
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'
Save your changes.
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
In DAC, display the Design view.
Select the Financials - Receivables row.
Click Tasks in the Design area on the left side of the screen, and then highlight the SDE_JDE_AR_Transaction_Fact_Extract task.
Display the Parameters sub-tab.
Select the row with the $$WRITE_OFF_DOC_TYPE parameter, and then double-click in the Value column.
In the Enter Parameter Value screen, replace 'RA' with a UDC in the source system. Make sure that the UDC is in single quotes.
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.
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
In the DAC client, click the Design tab.
Select the Source System Parameters tab.
Select the row with the $$DOC_CURR_CODE parameter, and then double-click in the Value column.
In the Enter Parameter Value screen, replace NULL with the domestic or base currency in use.
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
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.
Under Sources, select the Fact_W_DAY_D_JDE logical table source.
Click the Active check box in the General tab and click OK.
Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and clear the Active check box.
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.
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.
Similarly, in the Logical Table diagram, add the following logical tables:
Fact - Fins - Period Days Count
Dim - Ledger
Dim - Receivables Org
Dim - Payables Org
Click the joins between the three Logical Dimension table joins and the Logical Fact table and delete them.
Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.
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.
Set the Logical Level to All for these three dimensions:
Ledger
Payables Org
Receivables Org
Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.
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.
Access the dashboard page.
Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.
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.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
Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.
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.
Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-18.
Not applicable to Oracle BI Applications Version 7.9.6.1.