| Oracle® Business Intelligence Applications Release Notes Release 7.9.6.3 Part Number E20489-05 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Release Notes Release 7.9.6.3 Part Number E20489-05 |
|
|
View PDF |
These release notes describe known issues and workarounds for Oracle Business Intelligence Applications Release 7.9.6.3, and contain the following sections:
These release notes are updated periodically as new information becomes available. To ensure that you are reading the latest version of the release notes, check the Oracle Business Intelligence Applications Documentation set. The most current version of the Oracle Business Intelligence Applications Release Notes is available:
On the Oracle Technology Network at http://www.oracle.com/technology/documentation/bi_apps.html (to register for a free account on the Oracle Technology Network, go to http://www.oracle.com/technetwork/index.html).
For information about known issues and workarounds relating to DAC, refer also to Oracle Business Intelligence Data Warehouse Administration Console Release Notes.
This section describes general issues and workarounds for Oracle Business Intelligence Applications products. It contains the following topics:
To open the OracleBIAnalyticsApps.rpd file in Oracle BI Administration Tool, you must specify the Administrator\Admin123 credentials.
To open the OracleBIAnalyticsApps.rpd file, log in as Administrator with the password 'Admin123'.
For certification information, refer to the System Requirements and Supported Platforms for Oracle Business Intelligence Applications document. This document is part of the Oracle Business Intelligence Applications documentation set. It is also available from the Certifications tab on My Oracle Support at the following location:
This section provides release notes on installing and upgrading Oracle Business Intelligence Applications. It contains the following topics:
Section 1.2.3.2, "Mandatory Patch for Data Warehouse Administration Console"
Section 1.2.3.4, "Setting the Accept Source Flag when using the DAC Upgrade/Merge Wizard"
Section 1.2.3.5, "Link to Upgrade Guide in the Documentation Media Library Might Not Work"
Section 1.2.3.6, "Errors and Warnings Reported During RPD Upgrade"
Section 1.2.3.7, "Upgrading the DAC Repository When Also Upgrading the Source System"
Section 1.2.3.8, "Unzipping Informatica PowerCenter ZIP Files"
Section 1.2.3.9, "Configuring the Policy Store in Non-English Environments"
Section 1.2.3.10, "Setting Integration Services Custom Property 'OraDateToTimestamp'"
While running the Oracle BI Applications installer, you might see the following error message when you click the Next button on the WebLogic Administration Server Details screen:
"Unable to connect to Oracle WebLogic NodeManager. Make sure the NodeManager windows service is running"
Start Oracle WebLogic NodeManager before running the Oracle BI Applications installer. To start Oracle WebLogic Node Manager either start the Windows service, or run the startNodeManager.cmd from BIEE Home\wlserver_10.3\server\bin\startNodeManger.cmd.
Note: When the machine is restarted, the NodeManager will not start automatically if the windows service is not set to automatic.
Oracle Business Intelligence Applications V7.9.6.3 is supported on and requires Oracle Business Intelligence Data Warehouse Administration Console (DAC) platform Version 10.1.3.4.1. with Patch 10052370.
Download and Install Oracle Business Intelligence Data Warehouse Administration Console v10.1.3.4.1 from edelivery.oracle.com.
Then apply the Oracle Business Intelligence Data Warehouse Administration Console (DAC) platform version 10.1.3.4.1. Patch 10052370.
Patch 10052370 is available for download on the Patches and Updates tab on My Oracle Support at http://support.oracle.com.
Oracle Business Intelligence Applications Installation Guide was not updated for release 7.9.6.3. Make sure that you review the System Requirements and Supported Platforms guide for updated requirements for release 7.9.6.3, including the requirement for Informatica PowerCenter 9.0.1 with Hotfix 2. The System Requirements and Supported Platforms guide is available on the Getting Started tab of the Oracle Business Intelligence Applications Documentation Library Release 7.9.6.3, on the Oracle Technology Network. The library is located at:
http://www.oracle.com/technetwork/middleware/bi-foundation/documentation/bi-apps-098545.html
Not applicable.
This release note applies to you if you are upgrading to Oracle BI Applications Version 7.9.6.3 and are using the Refresh Base option of the DAC's Upgrade/Merge Wizard to complete the upgrade of your existing DAC Repository. In DAC Upgrade/Merge Wizard, Difference Reports display various types of objects in the Change Description column - for example, 'added-source', 'modified', 'cloned-target', and so on. Filtering between the types can be done via the drop down above the record list of changed objects. Depending on the change type, objects might have Accept Source selected or they might be left inactive.
Two types are of particular interest:
'deleted-source' objects. These are the objects that existed in the earlier release (target side), but were deleted from new release (source side).
'added-target' - this is a legitimate type for Peer-To Peer upgrade. It denotes objects added in the earlier release (target side). However, some objects deleted from the newer release (source side), may not be detected as such, and are categorized as added-target instead.
For the above object types, accepting the source deletes the target object since it no longer exists on the source. Rejecting the source retains the target object while transferring its ownership to custom container(s). The Accept Source flag is unchecked out-of-the-box to ensure that no objects are lost without a user explicitly authorizing the deletion. However, if your upgrade requirement is to move as close in state to the new release as possible, then the Accept Source flag must be checked for all object types - not only on the parent (upper) tab, but also on the child (lower) ones. While mass updates and flat view make this easier, these still require the change to be made one child object type at a time.
The following SQL statement sets the Accept Source flag to yes for the two object types:
UPDATE W_ETL_MERGEDATA SET ACCEPT_FLG='Y' WHERE DIS_CHANGE_TYPE IN ('added-target', 'deleted-source').
Run the SQL on your DAC repository after the Difference Report is generated and before the merge process is started. Note that you still have the option to change the Accept Source choices for individual objects prior to merging.
The media pack for Oracle Business Intelligence Applications Release 7.9.6.3 contains a documentation library named Oracle Business Intelligence Applications Documentation Media Library Release 7.9.6.3. The Documentation tab on this library contains a link to the Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users. The link to the guide points to its eventual location on the Oracle Technology Network (OTN). Clicking the link to the guide will result in a Not Found message until the book is present at the designated OTN location.
Oracle BI EE 11g is more stringent in reporting metadata repository errors and warnings than in Oracle BI EE 10g. Therefore, when you upgrade to Release 7.9.6.3, Oracle BI EE might report errors and warnings that were not reported previously. When you merge the metadata repository with the Release 7.9.6.3 RPD, most of these errors will be fixed. However if any of the errors or warnings listed in the tables below are reported on customized metadata after upgrade and merge, then you must perform the steps in the Workaround column to fix the issue.
Table 1-1 List of Fixes For Errors Reported During RPD Merge
| Error Number and Description | Workaround |
|---|---|
|
38028 - Logical column <Column Name> does not have a valid data type. All LTSs of the Logical Dimension are disabled. |
Enable related LTSs to avoid this Error. |
|
38126 - Logical Level/Column' <Level/Column Name> has name with leading or trailing space(s). Leading/Trailing spaces are not allowed logical/presentation objects' names. |
Remove leading or trailing spaces to fix these errors. |
Table 1-2 List of Errors Reported During RPD Merge
| Error Number and Description | Workaround |
|---|---|
|
39002 - Level <Level Name> has no defined key to uniquely identify members. |
Define key for the level. |
|
39003 - Missing functional dependency association for column: <Column>. |
Enable related LTSs. |
|
39028 - The features in Database '<Physical Layer Database Name>' do not match the defaults. This can cause query problems. |
Click on <Physical Data Source>, go to feature, and click button "Reset to defaults". |
|
39048 - The variable, '<Variable Name>' does not have a constant default initializer. |
Set the default value to some constant value. For example, for Date initialization use TIMESTAMP '2010-01-01 00:00:00'. |
|
39050 - Initialization block '<Initialization block Name>' is not associated with any variables. |
Assign variable to the initialization Block. |
|
39054 - Fact table <Fact> is not joined to logical dimension table <Dimension>. This causes problems when extracting project(s). |
Add a logical join to <Dimension>. |
|
39055 - Fact table <Fact> is not joined to tables in logical dimension <Dimension>. This causes problems when extracting project(s). |
Add a logical join to <Dimension>. |
|
39057 - There are physical tables mapped in Logical Table Source <LTS Name> that are not used in any column mappings or expressions. |
Remove physical table from LTS. |
|
39062 - Initialization Block '<Initialization block Name>' uses Connection Pool '<Connection Pool>', which is used for report queries. This might adversely affect query performance. |
Use separate connection pools for initialization blocks. |
When upgrading Oracle BI Applications to release 7.9.6.3, if you are also upgrading your source system to a newer version, you need to follow the steps below to upgrade the DAC Repository. These steps apply to any upgrade of the source system (whether or not you are also upgrading Oracle BI Applications).
Note: If you are upgrading your source system, do not follow the steps in Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users to upgrade the DAC Repository. The steps in the guide document how to upgrade the DAC Repository when the source system is not being upgraded.
Use the Replace Base option of the DAC Upgrade/Merge Wizard to upgrade your existing DAC Repository, including customizations, to the new version.
For instructions, see the topic titled, "About the Replace Base Option," in "Chapter 10: Upgrading, Comparing and Merging DAC Repositories," in Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
In the custom container, delete all of the preconfigured (out-of-the-box) mappings for logical folders to physical folders, which are listed in the Source System Folders tab in the DAC Design view.
In the custom container, reference the mappings for the logical folders to physical folders from the new base container.
Click Reference in the upper pane toolbar.
In the Reference dialog, select the new base container from the drop-down list.
Select all the mapping records that appear in the list, and click Add.
The Adding… dialog lists the mappings that were added to the custom container.
Click OK to close the Add… dialog.
Click OK to close the Reference dialog.
Change the name of the physical data source connection to reflect the name of the upgraded source system.
Go to the Physical Data Sources tab in the Setup view.
Locate the record for the source connection.
Change the name of the source connection to reflect the name of the upgraded source system.
For example, if you are upgrading from Oracle EBS R11 to R12, and the source connection name was Ora_R11, you would change it to Ora_R12. Do not change any other value in this record.
Click Save.
In Informatica Workflow Manager, open the Relational Connection Browser (in the menu bar, select Connections, and then select Relational), and edit the name of the connection to match the name you entered in step 4.
Rebuild all execution plans in the custom container.
For instructions, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
The Informatica files are provided as zip files on eDelivery. The zip files may be split into multiple downloads - for example, Informatica PowerCenter and PowerConnect Adapters Client 9.0.1 which contains the following 2 downloads:
Informatica PowerCenter and PowerConnect Adapters Client 9.0.1 (Part 1 of 2)
Informatica PowerCenter and PowerConnect Adapters Client 9.0.1 (Part 2 of 2)
To unzip the Informatica files with multiple downloads on Windows, follow the instructions below.
Download all parts (example, Part 1 of 2 and Part 2 of 2) into the same folder location on Windows.
Use WinZip to unzip the file.
You must use WinZip to unzip the file; other compression tools are not supported.
Create a directory with a short path name (example, D:\INFA) in which to extract the contents of the downloaded zip files.
Tip: Do not use the My Documents folder.
Extract the contents of the first zip file into the directory created in the previous step.
WinZip will automatically extract the contents of the second zip, and any additional zip files.
The following steps are missing from Oracle Business Intelligence Applications Installation Guide, and must be performed for localization as part of an Oracle Business Intelligence Applications installation.
Localize the Policy Store.
Import the Oracle BI Applications Version 7.9.6.3 Identity Store (LDIF) File Into the Embedded LDAP Server.
Refresh the GUIDs after importing the LDIF file.
Note: These steps should be performed after step "4.17 Applying the Oracle BI Applications Security Policy to the BI Domain" in Oracle Business Intelligence Applications Installation Guide.
The Oracle BI Applications 7.9.6.3 installer contains different policy stores for different languages. The default policy store that is deployed during the installation is in English. If you want to configure the policy store for any of the other available languages, perform the procedure below.
To configure the policy store for languages other than English:
Stop Oracle BI Services.
Copy the system-jazn-data_<LN>.xml file from:
$ORACLE_BI_HOME\biapps\admin\provisioning\localization\
to:
$DOMAIN_HOME\config\fmwconfig\
For example, to implement the policy store for French, you copy the file system-jazn-data_fr.xml into the \fmwconfig directory.
Back up the existing system-jazn-data.xml file in $DOMAIN_HOME\config\fmwconfig\.
Rename $DOMAIN_HOME\config\fmwconfig\system-jazn-data_<LN>.xml to system-jazn-data.xml.
Start Oracle BI Services.
Importing the Oracle BI Applications Version 7.9.6.3 Identity Store (LDIF) File into the Embedded LDAP Server
In this procedure, you import the standard (out-of-the-box) Oracle BI Applications version 7.9.6.3 LDIF file into the WebLogic Server (embedded LDAP server). This is available under directory $ORACLE_BI_HOME\biapps\admin\provisioning\EnterpriseBusinessAnalytics.ldif.
To import the Oracle BI Applications version 7.9.6.3 LDIF file:
Log in to the WebLogic Server Administration Console (for example: http://<host name>:7001/console).
Select the name of the security realm into which the LDIF file is to be imported (for example, myrealm).
Select Providers TAB, and select Authentication TAB, then choose the provider into which the LDIF file is to be imported (for example, Providers, and then Authentication and then DefaultAuthenticator).
Select Migration, and then select Import. Enter the full path of LDIF file into text box "Import File on Server" (for example, D:\BISHIPHOME111150\Oracle_BI1\biapps\admin\provisioning\EnterpriseBusinessAnalytics.ldif).
Click Save.
Refreshing the GUIDs after importing the LDIF file
Refresh the User GUIDs using the steps in section “Refreshing the User GUIDs” in Oracle Fusion Middleware Administrator's Guide.
If you are deploying the Oracle Business Analytics Warehouse on a database other than an Oracle database, you are required to create the OraDateToTimestamp custom property in Informatica Integration Services.
The value of this property must be set to 'Yes'.
Issue Description: The OracleBIAnalyticsApps.rpd is empty when installing Oracle BI Applications 7.9.6.3 on a Japanese Windows machine.
To work around this issue, change the locale of the machine to English before running the Oracle BI Applications Installer.
To change the locale:
Run <BIEE_INSTALL_HOME>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd to open a BI prompt window.
Run the command : set oracle_bi_lang=en.
Run the Oracle BI Applications Installer to install Oracle BI Applications.
After the installation is complete, reset oracle_bi_lang back to the original locale.
Follow this procedure for installing DAC on a 64-bit system.
To install DAC version 10g on a 64-bit Windows system:
Invoke the 32-bit installer that is located in the installation directory dac\Disk1\install\win32.
During the installation, at the "Prerequisite Checks" installer screen, you may receive a warning related to "Checking operating system certification," and you may receive an error related to "Checking service pack." Ignore this warning and error, and click Continue.
After the installation is complete, install a 64-bit Java Development Kit (JDK).
Edit the cofig.bat file to point to the 64-bit JDK:
Navigate to the \dac directory and open the config.bat file.
Set the following variables to point to the 64-bit JDK:
- JAVA_HOME
- JAVAW
- JAVA
Note: The JAVAW and JAVA variables appear in the config.bat file in a section below the instruction "DO NOT EDIT THE FILE BELOW THIS LINE." Ignore this instruction and edit these variables to point to the 64-bit JDK
To install DAC version 10g on a 64-bit UNIX system:
Follow the instructions for installing DAC on a UNIX system, as described in the section titled “Oracle Universal Installer Cannot Install DAC on a UNIX Platform,” in the Oracle Business Intelligence Data Warehouse Administration Console Release Notes, Version 10.1.3.4.1.
After the installation is complete, install a 64-bit JDK.
Edit the cofig.sh file to point to the 64-bit JDK:
Navigate to the \dac directory and open the config.sh file.
Set the following variables to point to the 64-bit JDK:
- JAVA_HOME
- JAVA
Note: The JAVA variable appears in the config.sh file in a section below the instruction "DO NOT EDIT THE FILE BELOW THIS LINE." Ignore this instruction and edit this variable to point to the 64-bit JDK
This section provides corrections for various pieces of the documentation set for Oracle Business Intelligence Applications. It contains the following topics:
Section 1.2.4.5, "Corrections to Oracle Business Analytics Warehouse Data Model Reference"
Section 1.2.4.6, "Corrections to Oracle Business Intelligence Applications Security Guide"
Note the following corrections:
In section 4.7.2, “Installing the DAC Server on UNIX,” the first paragraph erroneously refers to Linux, and should read:
“The DAC Server can run on UNIX, but it must first be installed on a Windows machine, then copied over to a UNIX machine, as described in the steps below. Oracle does not provides an installer for DAC on UNIX.”
The procedure in section 4.10.4, "Activating Join Indexes for Teradata Databases," is optional. If you are using a Teradata database and you want to create join indexes, do not follow the steps in Section 4.10.4. Instead, follow the instructions in "Defining Join Indexes on a Teradata Database," in revision 2 or later of Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console, 11g Release 1 (11.1.1).
Note the following corrections:
Step 2 in the following sections is incorrect:
Section 5.2.4.2, "How to Configure domainvalues_payment_method_jde.csv"
Section 5.2.4.3, "How to Configure domainvalues_src_transaction_type_codes_jde.csv and domainvalues_lkp_transaction_type_codes_jde.csv"
Section 5.2.4.4, "How to Configure domainvalues_make_buy_indicator_product_jde.csv"
Step 2 should read:
Using a text editor, open the flat file <file name>.csv, which is located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Step 1 in Section 4.2.3.2.1, "How to Configure file_ap_invoice_config_spend_voucher_psft.csv," is incorrect.
Step 1 should read:
Using a text editor, open the flat file <file name>.csv, which is located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
The procedure in Section 4.2.3.1.1, "How to Configure domainValues_Status_Purch_Approve_psft.csv," is incorrect. Note the following corrections:
The query in Step 1 should be replaced with the following:
SELECT DISTINCT A.FIELDVALUE, A.XLATLONGNAME
FROM PSXLATITEM A
WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
AND A.FIELDNAME IN('PO_STATUS','CURR_STATUS')
ORDER BY 1
Step 3 should include the following instructions:
When copying the FIELDVALUE to STATUS_CODE:
Replace C with CL for XLATLONGNAME = 'Closed'
Replace D with DE for XLATLONGNAME = 'Denied'
The procedure in Section 4.2.3.1.2, "How to Configure domainValues_Status_Purch_Cycle_psft.csv," is incorrect. Note the following corrections:
The query in Step 1 should be replaced with the following:
SELECT DISTINCT A.FIELDVALUE, A.XLATLONGNAME
FROM PSXLATITEM A
WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE
C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)
AND A.FIELDNAME IN('PO_STATUS','CURR_STATUS')
ORDER BY 1
Step 3 should include the following instructions:
When copying the FIELDVALUE to STATUS_CODE:
Replace C with CL for XLATLONGNAME = 'Closed'
Replace D with DE for XLATLONGNAME = 'Denied'
In Section 4.2.2.2.8, step 2 should read: “From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open domainValues_ShipmentType_ora12.csv file in a text editor.”
In Section 4.3.2.1, step 9 should read: “Repeat Step 2 to Step 8 for the mapplets mplt_BC_ORA_PurchaseScheduleLinesFact and mplt_BC_ORA_PurchaseCostFact.”
There are no errors reported for this book.
There are no errors reported for this book.
There are no errors reported for this book.
Note the following corrections:
The SQL statement that appears in the second bullet point (for Oracle EBS 11i) of Step 5 Section 2.6.4.2 is incorrect.
The correct SQL is as follows:
SELECT DISTINCT 'LEDGER', FND_PROFILE.VALUE_SPECIFIC('GL_SET_OF_BKS_ID', USER_ID,
RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID)
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM
FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID IN (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE') = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE UPPER(USER_NAME) = UPPER(':USER')) END)
AND RESPONSIBILITY_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE') = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN
'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE') = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END))
Note: The difference between the correct and incorrect versions is that the correct version has single quotes around the VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) statements. The correct version reads: 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE').
The initialization block SQL that appears in Section 2.6.1, "Oracle BI Applications Authorization for Oracle EBS," is incorrect.
The correct SQL is as follows:
SELECT DISTINCT 'GROUP', RESPONSIBILITY_NAME
FROM FND_USER,FND_USER_RESP_GROUPS, FND_RESPONSIBILITY_VL
WHERE FND_USER.user_id=FND_USER_RESP_GROUPS.user_id
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID =
FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID =
FND_RESPONSIBILITY_VL.APPLICATION_ID
AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE
AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE ELSE
TO_DATE(FND_USER_RESP_GROUPS.END_DATE) END) >= SYSDATE
AND FND_USER.USER_ID = (SELECT USER_ID FROM FND_USER WHERE UPPER(USER_NAME) =
UPPER('VALUEOF(NQ_SESSION.USER)'))
This section provides release notes for Oracle Business Intelligence Applications in general. It contains the following topics:
Section 1.3.1, "Lack of Time Zone Setting Prevents Delivery of iBots to Applications Users"
Section 1.3.2, "Issue with Exchange Rates and Transaction Currencies"
Section 1.3.6, "Error in Reports Based on 'Opportunity' Under Opportunity Contact Segmentation"
Section 1.3.7, "'STAT' Currency Journals or Accounts in Financial Analytics"
Section 1.3.11, "Division Name Pointing To An Obsolete Column In Oracle Sales Analytics"
Section 1.3.15, "Writeoff LOC Amounts and Exchange Rates Incorrect in Oracle Project Analytics"
Section 1.3.16, "Potential Performance Issue in Absence ETL Mapping SDE_ORA_ABSENCEEVENT_FULL"
Section 1.3.19, "Loyalty AN Metric "# OF MEMBERS" Gives Incorrect Results When Grouped by Quarter"
Section 1.3.22, "Numeric Overflow Error When Targeting Teradata"
Section 1.3.23, "How to Secure the Employee Dimension in Oracle HR Analytics"
Section 1.3.26, "Employee Name (From Position Dimension) Shows Incorrectly In Reports"
Section 1.3.29, "Performance Issue with PLP_GLBALANCEAGGRBYACCTSEGCODS"
Section 1.3.30, "'Other Operating Expenses' Not Included in Profit and Loss Reports"
Section 1.3.31, "Chargeback Transactions Not Included in AR Aging Report"
Section 1.3.32, "'Days Payables Outstanding' and 'AP Turnover' Columns Are Missing"
Section 1.3.33, "Error During Import Of New Schema Definitions Into Siebel Transactional Database"
Section 1.3.34, "SDE_PSFT_GLJournals_Extract_Full Fails Due to Data Type Mismatch"
Section 1.3.35, "SIL_GLAccountDimension_HierarchyUpdate Task Fails on DB2"
Section 1.3.36, "Error in SIL_HouseholdDimension_SCDUpdate_Full Mapping"
Section 1.3.37, "Intermittent Communication Failure Between DAC and Informatica"
Section 1.3.38, "Recruitment Metric "Time to Fill (Days)" Has Incorrect Denominator"
Section 1.3.39, "SIL_EmployeeDimension_SCDUpdate Hangs in Incremental Load With SQL Server 2005"
Section 1.3.40, "Issue with DB2 9.1 Databases During Full ETL Loads"
Section 1.3.44, "Task PLP_APXACTSGROUPACCOUNT_A1_LOAD Takes a Long Time to Complete"
Section 1.3.45, "Project Chartfields Missed in GL Balance Extract"
Section 1.3.46, "COGS SDE Mappings Might Fail When Sourcing From Multiple EBS 11i Instances"
Section 1.3.47, "GL Journals Using Wrong Currency Conversion Date"
Section 1.3.48, "W_GL_BALANCE_F_U1 Index Creation Fails When Loading From PeopleSoft Financials"
Section 1.3.49, "SDE_ORA_APTRANSACTIONFACT_DISTRIBUTIONS Uses Incorrect Extract Date"
Section 1.3.50, "Verify Data Warehouse Tables After ETL with DB2 UDB v9.7 and Siebel 8.1.1"
Section 1.3.52, "Dashboard Tooltips Are Displayed In English Only"
Section 1.3.53, "Metrics Error in Next Product Purchased Report in B2B Customer Insight Dashboard"
Section 1.3.54, "Truncate Should Always Be Set For W_POSITION_DH_PRE_CHG_TMP TABLE In DAC"
Section 1.3.55, "Recruitment Showing Incorrect Hire Date for Internal Applicant"
The Oracle BI Delivers iBots use a predefined query against the SA System subject area to retrieve a list of applications users who are associated with the iBot's Recipient Group. When an iBot is run, users who do not have a time zone specified in user preferences are considered invalid users and iBots are not delivered to them.
This issue occurs because the join type for the S_TIMEZONE join in the S_USER Logical Table Source in the SA System Business Model is defined as INNER when it should be defined as RIGHT OUTER.
To work around this issue, perform these steps:
In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.
In the Business Model and Mapping layer, expand the SA System Business Model and the USER Logical Table.
Double-click the S_USER Logical Table Source under Sources in the USER Logical Table.
In the Logical Table Source - S_USER Dialog Box, change the type to RIGHT OUTER from INNER for the S_TIMEZONE join in the Joins section of the General tab.
For information on the SA System subject area, see the Oracle Business Intelligence Server Administration Guide.
Current design and support of multiple currencies within Oracle Business Intelligence Applications and the data warehouse assumes that the transactional system (or OLTP system) provides exchange rates and table structures that store exchange rates.
If the OLTP system does not provide exchange rates from the 'transaction currency' to the chosen 'one or more data warehouse currencies', then the Fact table has a null exchange rate value for 'transaction' currency to 'Global1' currency, and hence, analysis based on Global currencies is not possible for these transactions. It also impacts the correctness of the data for data that resides in various aggregate tables. This issue is also seen in the other two supported currencies (Global2 and Global3).
To work around this issue, ensure that the OLTP system has all currency exchange rates from all possible transaction currencies added to all the three chosen data warehouse currencies, up front. If this is not taken care of beforehand and you encounter a missing exchange rate issue, then you can rerun transactions in 'full' mode after you have fixed the missing exchange rate issue in the OLTP system.
The Contact Geography dimension attributes on the Campaign Contacts_Segmentation catalog, which map to the Person Geography dimension table, are not joined appropriately to the Campaign History Fact table. Therefore, Oracle Business Intelligence Server cannot find an appropriate navigation path when this dimension is used.
To work around this issue, remove the Geography dimension from the Campaign Contacts_Segmentation catalog. If users want to use this dimension, then they can switch to another subject area that has this dimension and reference it there, for example, Customer Profile_Segmentation.
The DAC Deployment procedure causes sequence generator transformations in the ETL to be reset to start from the number 1 again. This can cause some issues when running Source Independent Load (SIL) mappings for the different applications and adapters together. These SIL mappings are unable to run together.
To illustrate this limitation, two examples of ETLs being unable to run together are shown below:
When the Siebel Vertical adapter and Oracle EBS adapter is used, the Siebel adapter leverages the SIL mappings in the SIL_Vert folder, while the Oracle EBS adapter leverages the SIL mappings in the SILOS folder. The sequence generators in the SIL_Vert folder gets updated to new values, while the same ones in the SILOS folder do not. This results in all SILOS mappings for common dimensions (like Employee, Exchange Rate, and so on.) to fail.
The same dimensional tables are loaded from mappings within the SILOS folder and the PLP folders. This results in the same issue as above, and when the mappings are run, they fail.
The workaround is to set the Sequence Generator value to run between 1 and a sufficiently large finite value (for example, 1,000,000,000) for one of the folders and set the SILOS folder Sequence Generator value to run between 1,000,000,001 and its maximum limit of 2,000,000,000.
The email personalization formats that are installed out-of-the-box in Siebel 8.0 are not constraining the generated lists by treatment ID. As a result, when a campaign launch issues the SOAP call to generate list files for a given treatment, everyone that qualifies for the campaign is being returned in the list. For example, if the campaign has two email treatments that have been allocated to two different sets of campaign members, this issue causes all campaign members to receive both treatments.
This section explains the workaround for this issue.
This section explains how to update the repository in Oracle BI Administration Tool.
In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.
In the Physical Layer, add a physical column DCP_ID to the Campaign Promotion physical table in Marketing OLTP database and specify the following values in the Physical Column dialog:
Name: DCP_ID
Type: VARCHAR
Length: 15
Nullable: yes
In the Business Model and Mapping Layer, add a logical column Treatment Id to the OLTP Campaign Promotion logical table in Marketing Contact List business model and specify the following values in the Logical Column dialog:
Name: Treatment Id
Logical Table Source: S_CAMP_CON
Mapped as: "Marketing OLTP".dbo."Campaign Promotion".DCP_ID
In the Business Model and Mapping Layer, add a logical column Treatment Id to the OLTP Campaign Promotion logical table in Marketing Account List business model and specify the following values in the Logical Column dialog:
Name: Treatment Id
Logical Table Source: S_CAMP_CON
Mapped as: "Marketing OLTP".dbo."Campaign Promotion".DCP_ID
In the Presentation Layer, add the Treatment Id presentation column to the Campaign History (Transaction Database) presentation table in the Marketing Contact List presentation catalog and specify the following values in the Presentation Column dialog:
Name: Treatment Id
Logical Column: "Marketing Contact List"."OLTP Campaign Promotion"."Treatment Id".
In the Presentation Layer, add the Treatment Id presentation column to the Campaign History (Transaction Database) presentation table in the Marketing Account List presentation catalog and specify the following values in the Presentation Column dialog:
Name: Treatment Id
Logical Column: "Marketing Account List"."OLTP Campaign Promotion"."Treatment Id".
This section explains how to update the Campaign Load Format and Email Server Format in Siebel Marketing.
Log in to Siebel Marketing.
Add Treatment Id to the Campaign Contact integration component and specify the following values in the Edit Column Formula dialog:
Table heading: Campaign Contact
Column Heading: Treatment Id
Column Formula: '@{treatmentID}{0}'
Add a filter to constrain the output based on the Treatment Id column and specify the following values in the Create/Edit Filter dialog:
Operator: is equal to / is in
Expression: '@{treatmentID}{0}'
This issue occurs when creating a report by selecting all of the attributes from OPPORTUNITY under the subject area, OPPORTUNITY CONTACT SEGMENTATION. If you create a report with this criteria, your system reports the following ODBC error:
Odbc driver returned an error (SQLExecDirectW) Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14026] Unable to navigate requested expression: Fact - Marketing - Segmentation Opportunity Contact.Implicit Fact Column. Please fix the metadata consistency warnings. (HY000) SQL Issued: SELECT Opportunity."Opportunity Name" saw_0, Opportunity."Opportunity Account Name" saw_1, Opportunity."Opty Status" saw_2, Opportunity."Lead Quality" saw_3, Opportunity."Lead Age Category" saw_4, Opportunity."Deal Size" saw_5, Opportunity."Primary Competitor" saw_6, Opportunity."Sales Stage Name" saw_7, Opportunity."Sales Method" saw_8, Opportunity."Targeted Opportunity Flag" saw_9, Opportunity."Reason Won or Lost" saw_10, Opportunity."Competitor ROW_ID" saw_11, Opportunity.ROW_ID saw_12 FROM "Opportunity Contact_segmentation" ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12 "
There is no workaround for this issue.
This issue is specific to Oracle eBusiness Suite adaptors used in conjunction with Financial Analytics.
Currently Oracle BI Applications doesn't support extracting statistical GL balances or journals. No accounts or transactions with a STAT currency code are brought into the warehouse. The existing out-of-the-box mappings SDE_ORA_GLBalanceFact and SDE_ORA_GLJournals contain a filter to filter out the 'STAT' records.
To support 'STAT' currency, perform the task specified below:
Log into Informatica Designer.
Open the SDE folder.
Locate the mapping SDE_ORA_GLBalanceFact.
Open the mapplet mplt_BC_ORA_GL_Balance_Fact.
Remove or comment out the line 'AND GL_BALANCES.CURRENCY_CODE <> 'STAT' from SQL Qualifier.
Save the changes.
Perform similar steps for the mapping SDE_ORA_GLJournals:
Log into Informatica Designer.
Open the SDE folder.
Locate the mapping SDE_ORA_GLJournals.
Open the mapplet mplt_BC_ORA_GLXactsJournalsExtract.
Remove or comment out the line 'AND GL_JE_HEADERS.CURRENCY_CODE<>'STAT'' from SQL Qualifier.
Save the changes.
This issue affects the Campaign Contacts Segmentation subject area in Oracle Marketing Analytics. If you try to create a report in Oracle BI-EE Answers that joins Campaign History Fact with any of the fields in the Industry dimension, then you get a metadata inconsistency error. This error is caused by a missing join between the Industry dimension and the W_CAMP_F table at the star schema level.
There is no workaround for this issue.
This issue affects the Working Days Lost report on the Absence Trend dashboard page in Oracle HR Analytics. The Working Days Lost report calculates the percentage of employees who are absent, using the following calculation:
/* % of employees who were absent */ (COUNT(DISTINCT "Employee Attributes"."Employee Number")/"Headcount Facts"."Employee Headcount")*100
However, when the dashboard is filtered by Absence Category or Type (for example, sickness or maternity), then the employee head count and employee absent rate returns 'null'.
To calculate the employee absence rate for a specific absence type, use Oracle BI Administration Tool to create a derived metric in the RPD. For example, perform the following steps to calculate the percentage of Employees on sick leave. The exact metric name can vary.
Add a new measure 'Absence Days (Due to Sickness)' in the RPD presentation layer with the following formula:
sum(case when Dim_W_ABSENCE_TYPE_RSN_D.ABSENCE_CATEGORY_CODE = 'S' then Fact_W_ABSENCE_EVENT_F_Event.DAYS_DURATION else 0 END )
In Oracle BI Answers, calculate the percentage of employees on sick leave using the formula “Absence Days (Due to Sickness)”/Employee Headcount.
An error occurs when creating a report that selects only Learning Course and Learning Activity dimensions. The logical SQL is:
SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14070] Cannot find logical table source coverage for logical columns: [Learning Course]. Please check more detailed level keys are mapped correctly. (HY000) SQL Issued: SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1. Learning Course and Activity presentation table is a logical grouping of common learning dimensions to be used when viewing learning enrollment facts. Because there is no logical relationship between the course dimension and the activity dimension, there is no way to present data between the two dimensions without the presence of an enrollment fact.
To report on learning activities available for a learning course, a metric must be included in the report. For example, including Learning Course, Learning Activity and Enrollment Count in the report.
In Oracle Sales Analytics deployments, the dimension column 'Organization ->Division Name' refers to the Employee's Division, which is the Business Unit Name in Siebel. Refer to web catalog folder under the Usage Accelerator Subject Areas named 'Usage Accelerator Current' and 'Usage Accelerator Summary'. Division Name points to the physical column, W_INT_ORG_D.BU_NAME, which is obsolete from Oracle Business Intelligence Applications Version 7.9.5 and later.
The workaround is to add BU_WID to UA facts that are in use, as follows:
In the ETL, do the following:
Extend each fact, including temp tables (e.g. W_UAEMP_TMP) with BU_WID.
This enables you to make the new physical alias of W_INT_ORG_D for BU conform across all facts in UA (see RPD steps below).
Verify that the BU_ID currently in WS_POSTN is correct.
If it is incorrect, add new column.
Extend W_UAPOS_TMP with BU_WID and modify the mapping to load it.
Modify related SIL mappings to populate the BU_WID in each fact from W_UAPOS_TMP.
Note: There are 15 fact tables and three temp tables (WS_POSTN, W_UAEMP_TMP, W_UAPOS_TMP) that must be extended with the new column BU_WID. You must also:
- Modify the mappings that populate the data in the temp tables mentioned above.
- Change all the mappings that are involved in populating the fact tables, to fetch the data for the new column BU_WID.
In the RPD, do the following:
In the Physical Layer, create a new physical alias of W_INT_ORG_D.
In the Business Model and Mapping layer, create a new logical dimension for BU from the alias.
Create a simple hierarchy for the new BU logical dim.
Join (logically and physically) to the facts by BU_WID.
In the Presentation Layer, add the BU name from new BU logical dimension.
Update the reports (if any) following the presentation changes.
Note: If you are upgrading from a version prior to Oracle Business Intelligence Applications Version 7.9.5, you might have to fix the old fact records by a suitable means, such as creating a Workflow.
The following tasks are not auto generated and therefore have to be manually added to the JDE container as follows:
For Financials - General Ledger Subject area, add the following 2 tasks:
SDE_JDE_GL_Other_Fact
SDE_JDE_CodeDimension_UDC
For Financials - Cost of Goods Sold Subject area, add the following 2 tasks:
SDE_JDE_GL_COGS_Fact
SDE_JDE_CodeDimension_UDC
For Financials - Revenue Subject area, add the following 2 tasks:
SDE_JDE_GL_Revenue
SDE_JDE_CodeDimension_UDC
In DAC, display the Design pane and select the appropriate JDE container from the drop-down list.
Display the Subject Areas tab.
Select the applicable Subject Area (for example, Financials - General Ledger).
For a list of applicable Subject Areas, see the list above.
In the lower pane, display the Tasks tab, which displays the list of tasks that are part of the Subject Area.
Click the Add/Remove button.
In the pop-up window highlight the appropriate tasks (for example, SDE_JDE_GL_Other_Fact) from the left pane and then click Add.
For a list of applicable Tasks, see the list above.
Once the task has been added it is displayed in the right pane.
Click OK to close the pop-up window.
Select the newly added task and click Save.
Click the Save button in the top pane.
Click the Assemble button.
PeopleSoft does not support Revenue Ago Metrics Using GL_Accounting_Period_WID.
To use the metrics available in table "Ago Measures" from the Subject Area "Project Revenue", you must remove the foreign key joins on Period from these facts in the repository (RPD). The server then uses the Date foreign keys, resolving the problem.
There are cases when the data load process is not disabling records that are no longer valid in the table W_XACT_TYPE_D. This happens when the update process incorrectly populates column W_XACT_TYPE_D.ACTIVE_FLG with 'Y' when in the OLTP table the value in column END_DATE_ACTIVE is less than SYSDATE. This issue does not affect the data shown in the dashboards no action is required.
Not applicable.
The data sourced from PeopleSoft for the W_PROJ_RETENTION_F table shows incorrect data for Project Retention write off amounts if the transaction currency code is different from the GL currency code.
There is no workaround for this issue.
This issue applies to EBS iRecruitment only. This applies to Absence ETL mapping SDE_ORA_ABSENCEEVENT_FULL. This mapping has an API call to EBS iRecruitment function hri_bpl_utilization.convert_days_to_hours() in the source qualifier that converts absence days to hours. This API has poor performance and can potentially increase ETL load performance for this mapping performance. It is optional to use this API call hri_bpl_utilization.convert_days_to_hours() in the ETL source adaptor.
To improve performance, remove the API call and convert absence days to hours by multiplying it with a constant number such as 8 hours a day.
When running some mappings for Product sourcing from Siebel CRM, there may be duplicate data found in W_PRODUCT_DH.
Mapping SIL_ProductCategoryDimension_Hierarchy is vertical specific. The join in sql override to W_PRODUCT_D is missing the condition PRODUCT.CURRENT_FLG='Y'. This allows duplicate data to enter the product table.
To avoid this, a join condition must be added, as follows:
In Informatica PowerCenter Designer, navigate to the folder SIL_Vert\Mappings, and open the mapping "SIL_ProductCategoryDimension_Hierarchy" in the Mapping Designer tool.
Edit the Source Qualifier SQ_W_PRODCAT_DS to display the Edit Transformations dialog, and display the Properties tab.
Select the Transformation Attribute name Sql Query, and modify the sql override to change the left outer join condition to w_product_d as:
W_PRODCAT_DS BASE LEFT OUTER JOIN W_PRODUCT_D PROD ON BASE.PROD_ID = PROD.INTEGRATION_ID AND PROD.CURRENT_FLG = 'Y'
Save the details and check in.
The incremental ETL fails intermittently. When looking at the log it complains about ORA-00604: error occurred at recursive SQL level on the table W_XACT_TYPE_DS.
In Informatica Workflow Manager, change the Target Load Type from Bulk to Normal for the following sessions:
SDE_ORA_TransactionTypeDimension_APDerive
SDE_ORA_TransactionTypeDimension_ARDerive
SDE_ORA_TransactionTypeDimension_GLRevenueDerive
To locate the Target load type setting, log into Informatica Workflow Manager, and select Session Properties, then Mapping, then Targets, and select TARGET_TABLE, then select Target load type setting under Properties.
If # of members and quarter are selected to form a report, and if there is no status change for a user in the "W_LOY_MEMBER_STATUS_HIST_F" table in a given quarter, that member does not get counted for that quarter. This is a known issue causing the report to render the wrong count for members (only under this specific criteria).
There is no workaround for this issue.
This is a known issue that affects setting the value of segment tree flag within Oracle Marketing Analytics. This flag is used to determine whether a segment or segment tree that is selected. The ETL is using the following expression, which is causing the issue:
IIF(ISNULL(PAR_CALL_LST_ID) AND SEGMENT_TYPE_I='Tree','Y,'N')
Compare the segment type with "Segment_Tree" string instead of "Tree". The modified expression should be:
IIF(SA_FLG_lv = 'N' AND TREE_FLG_lv ='N' AND PARENT_TYPE_I = 'Segment Tree', 'Y', 'N')
Marketing Cost (Actual) metric is not giving the Actual Expense, since it is mapped to the forecasted value. If you try to add this field to any report, it displays the forecasted amount instead of the actual amount.
There is no workaround for this issue.
Several mappings are failing with a numeric overflow error when using Teradata as a source system. The definitions of table length and precision within the DAC metadata to handle the Teradata ETL is not consistent with the data warehouse schema definitions and this can cause the numeric overflow issues.
If a numeric overflow issue is encountered, make the necessary changes to the DAC metadata within the custom DAC container to increase the data size manually to avoid numeric overflow.
Oracle HR Analytics secures user data access using security filters applied to fact tables. These security filters restrict a user's access to a subset of the data based on his or her security profile by the securable dimensions e.g. organization, supervisor hierarchy or business group. As delivered, Employee dimension itself is not a securable dimension. This means that when a user browses Employee dimension directly without selecting a metric that is secured by one of the securable dimensions, he or she sees all people in the Employee dimension regardless of his/her security access. However, it is important to point out that a user's data security is applied once the user includes one or more metrics along with the Employee dimension attributes. By combining metrics with the Employee dimension, it indirectly secures the Employee dimension through the logical join between Employee dimension and the secured fact tables.
However, occasions may arise that require securing the Employee dimension so that a user can only view people within his/her security access when he/she browses the Employee dimension directly. Customers can decide to secure the Employee dimension during implementation.
For instructions on how to secure the Employee dimension, refer to Tech Note How to Secure Employee Dimension in OBIApps 7.9.6 and 7.9.6.1 (Doc ID 948928.1) that is available at My Oracle Support.
DIM - CUSTOMER"."HIERARCHY BASED LOGIN" is not defined correctly in the BI Repository RPD.
For more information about working with this issue, refer to Tech Note 949432.1, which is available at My Oracle Support.
If dashboard users change the value of attribute ORG_INFORMATION2 from 'Y' to 'N' for an organization in the OLTP dimension table HR_ORGANIZATION_INFORMATION, the ETL process that populates table W_INT_ORG_D may return records for which column PROJECT_ORG_FLG is not set to 'Y'. This is not correct and might cause records to be missing from reports when the user is filtering reports by Project Organization Name.
The workaround for this issue is to remove the Logical Table Source (LTS) content filter condition for Dimension: "Dim - Project Organization", as follows:
In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.
In the Business Model and Mapping layer, do the following:
Double-click the Logical Table in the \Core\ folder named 'Dim - Project Organization', to display the Logical Table - <Name> dialog.
Display the Sources tab.
Edit the source named Dim_E_INT_ORG_D_Project_Organization, to display the Logical Table Source - <Name> dialog.
Display the Content tab.
Delete the text from the 'Use this "WHERE clause" filter to limit rows returned (exclude the "WHERE")' box.
Save the details.
Previous releases are also impacted if you have upgraded Oracle Business Intelligence Enterprise Edition to version 10.1.3.4.x or later.
This issue affects customers using out-of-the-box reports in Oracle Sales Analytics (including Usage Accelerator module of Sales Analytics).
In many out-of-the-box reports, the 'Employee Full Name' column is defaulted as the full name of the logged in user, sourced from the Position Dimension. To achieve this, the CHOOSE function is used in the expression builder in the Oracle BI Answers report. However, the CHOOSE function fails to fetch the name of the logged in user correctly, since some changes were made in Oracle BI-EE 10.1.3.4.x. Instead, it displays the name of the top level employee (the first column in the CHOOSE statement), for users at any level. However, the metrics are shown correctly. Examples of reports impacted:
Shared Folders: Sales: Pipeline: Overview/My Top Stalled Opportunities
Shared Folders: Sales: Pipeline: Subordinates/Pipeline by Subordinate
Note: The IndexCol function in this definition makes the Hierarchy-Based Column default to one of the columns in the Position table based on the value of HIER_LEVEL. So, if the value of HIER_LEVEL is 0, the new column defaults to the first column in the list, and so on.
Open the report and Click on 'Modify'.
On the Criteria tab, go to 'Full Name' column and click on 'fx' to display the 'Edit Column Formula' window.
Replace the existing column formula with:
INDEXCOL(VALUEOF(NQ_SESSION.HIER_LEVEL),Position."Current Top Employee Full Name", Position."Current Level 16 Employee Full Name", Position."Current Level 15 Employee Full Name", Position."Current Level 14 Employee Full Name", Position."Current Level 13 Employee Full Name", Position."Current Level 12 Employee Full Name",Position."Current Level 11 Employee Full Name", Position."Current Level 10 Employee Full Name", Position."Current Level 9 Employee Full Name", Position."Current Level 8 Employee Full Name", Position."Current Level 7 Employee Full Name", Position."Current Level 6 Employee Full Name", Position."Current Level 5 Employee Full Name", Position."Current Level 4 Employee Full Name", Position."Current Level 3 Employee Full Name", Position."Current Level 2 Employee Full Name", Position."Current Level 1 Employee Full Name", Position."Current Base Employee Full Name")
Click OK and save the report.
This issue applies to Data Warehouse Business Adapter for Oracle Version 7.9.6.1 or lower with Oracle EBS and using Inventory Daily Balance Fact. SDE_ORA_InventoryDailyBalanceFact only calculates the Amount columns for Standard Costing Organizations, not for the Average Costing Organizations. Therefore, the Amount columns shows NULL values for the Average Costing.
To calculate the item_cost for both Standard and Average Cost, make the following changes to the SA mapplet mplt_SA_ORA_InventoryDailyBalanceFact:
Create a new lookup named LKP_ITEM_COST based on the existing lookup LKP_STD_COST on the table W_STANDARD_COST_G.
Edit the new lookup named LKP_ITEM_COST and change the lookup SQL override to the following query:
SELECT NVL(W_STANDARD_COST_G.STD_COST, W_STANDARD_COST_G.MOVING_AVG_PRICE) as ITEM_COST, W_STANDARD_COST_G.INTEGRATION_ID as INTEGRATION_ID,W_STANDARD_COST_G.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID, W_STANDARD_COST_G.EFFECTIVE_FROM_DT as EFFECTIVE_FROM_DT, W_STANDARD_COST_G.EFFECTIVE_TO_DT as EFFECTIVE_TO_DT FROM W_STANDARD_COST_G
In the lookup LKP_ITEM_COST, change the column name from STD_COST to ITEM_COST.
In the SA mapplet mplt_SA_ORA_InventoryDailyBalanceFact, replace the LKP_STD_COST with new lookup LKP_ITEM_COST.
Note: Delete the short cut to the old lookup and replace it with the new lookup.
In EXP_INV_BALANCE, open the expression for the column VAR_UNIT_LOC_STD_COST, and change the expression from:
:LKP.LKP_STD_COST(VAR_STD_COST_ID,INP_DATASOURCE_NUM_ID,SESSSTARTTIME)
To:
:LKP.LKP_ITEM_COST(VAR_STD_COST_ID,INP_DATASOURCE_NUM_ID,SESSSTARTTIME)
Save the mapplet mplt_SA_ORA_InventoryDailyBalanceFact and check in the code.
After the ETL is re-executed using the updated mapplet, all amount columns are populated for both standard and average costing organizations.
The calculation for MIN PRICE does not take into account currency conversion in Purchase Agreement, and therefore produces an invalid value.
There is no workaround for this issue.
Performance Issue with PLP_GLBALANCEAGGRBYACCTSEGCODS.
Create a new connection for the session, as follows:
For details, see Note: 870314.1 - Oracle Business Intelligence Applications Version 7.9.6. Performance Recommendation.
Create a new connection for the source of the session.
Click Connection Environment SQL and enter the following.
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;
alter session set hash_area_size=2000000000;
Note: If workarea_size_policy is already manual, you can omit the first command.
If the database version is 10.2.0.4, then you must include the following additional command:
alter session set "_GBY_HASH_AGGREGATION_ENABLED" = true;
Open session PLP_GLBalanceAggrByAcctSegCodes in Workflow Designer (Mapping Tab -> Connections).
Assign the new connection to SQ Connection, mapplet and $Source connection value.
Click OK.
Modify the override SQL for session PLP_GLBalanceAggrByAcctSegCodes, as follows:
Open Informatica Workflow Manager.
Check out the session PLP_GLBalanceAggrByAcctSegCodes.
In the Mapping tab, modify the override SQL as follows (Remove the table W_GLACCT_SEG_CONFIG_TMP and related joins from the override SQL. See the next step for changing aggregate segments).
Example – Use Segment 1 – 4 for Aggregate
SELECT $$HINT1
W_GL_BALANCE_F.LEDGER_WID,
W_GL_BALANCE_F.PROFIT_CENTER_WID,
W_GL_BALANCE_F.COMPANY_ORG_WID,
W_GL_BALANCE_F.BUSN_AREA_ORG_WID,
W_GL_ACCOUNT_D.GROUP_ACCT_WID,
W_GL_BALANCE_F.BALANCE_DT_WID,
W_GL_BALANCE_F.BALANCE_TM_WID,
W_GL_BALANCE_F.TREASURY_SYMBOL_WID,
W_GL_BALANCE_F.MCAL_CAL_WID,
W_GL_BALANCE_F.DB_CR_IND,
SUM(W_GL_BALANCE_F.BALANCE_ACCT_AMT) BALANCE_ACCT_AMT,
SUM(W_GL_BALANCE_F.BALANCE_LOC_AMT) BALANCE_LOC_AMT,
SUM(W_GL_BALANCE_F.BALANCE_GLOBAL1_AMT) BALANCE_GLOBAL1_AMT,
SUM(W_GL_BALANCE_F.BALANCE_GLOBAL2_AMT) BALANCE_GLOBAL2_AMT,
SUM(W_GL_BALANCE_F.BALANCE_GLOBAL3_AMT) BALANCE_GLOBAL3_AMT,
SUM(W_GL_BALANCE_F.ACTIVITY_ACCT_AMT) ACTIVITY_ACCT_AMT,
SUM(W_GL_BALANCE_F.ACTIVITY_LOC_AMT) ACTIVITY_LOC_AMT,
SUM(W_GL_BALANCE_F.ACTIVITY_GLOBAL1_AMT) ACTIVITY_GLOBAL1_AMT,
SUM(W_GL_BALANCE_F.ACTIVITY_GLOBAL2_AMT) ACTIVITY_GLOBAL2_AMT,
SUM(W_GL_BALANCE_F.ACTIVITY_GLOBAL3_AMT) ACTIVITY_GLOBAL3_AMT,
W_GL_BALANCE_F.ACCT_CURR_CODE,
W_GL_BALANCE_F.LOC_CURR_CODE,
W_GL_BALANCE_F.DATASOURCE_NUM_ID,
W_GL_BALANCE_F.TENANT_ID,
W_GL_BALANCE_F.TRANSLATED_FLAG,
W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG3_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG3_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG4_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG4_ATTRIB,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_CODE,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_ATTRIB,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG6_CODE,
NULL --W_GL_ACCOUNT_D.ACCOUNT_SEG6_ATTRIB
FROM W_GL_BALANCE_F,
(SELECT /*+ USE_HASH(W_GLACCT_GRPACCT_TMP, W_GL_ACCOUNT_D)*/
W_GLACCT_GRPACCT_TMP.GROUP_ACCT_WID, W_GL_ACCOUNT_D.*
FROM W_GL_ACCOUNT_D W_GL_ACCOUNT_D,
W_GLACCT_GRPACCT_TMP W_GLACCT_GRPACCT_TMP
WHERE W_GL_ACCOUNT_D.ROW_WID =
W_GLACCT_GRPACCT_TMP.GL_ACCOUNT_WID) W_GL_ACCOUNT_D
WHERE 1 = 1
AND W_GL_BALANCE_F.GL_ACCOUNT_WID = W_GL_ACCOUNT_D.ROW_WID
GROUP BY W_GL_BALANCE_F.LEDGER_WID,
W_GL_BALANCE_F.PROFIT_CENTER_WID,
W_GL_BALANCE_F.COMPANY_ORG_WID,
W_GL_BALANCE_F.BUSN_AREA_ORG_WID,
W_GL_ACCOUNT_D.GROUP_ACCT_WID,
W_GL_BALANCE_F.BALANCE_DT_WID,
W_GL_BALANCE_F.BALANCE_TM_WID,
W_GL_BALANCE_F.TREASURY_SYMBOL_WID,
W_GL_BALANCE_F.MCAL_CAL_WID,
W_GL_BALANCE_F.DB_CR_IND,
W_GL_BALANCE_F.ACCT_CURR_CODE,
W_GL_BALANCE_F.LOC_CURR_CODE,
W_GL_BALANCE_F.DATASOURCE_NUM_ID,
W_GL_BALANCE_F.TENANT_ID,
W_GL_BALANCE_F.X_CUSTOM,
W_GL_BALANCE_F.TRANSLATED_FLAG,
W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG3_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG3_ATTRIB,
W_GL_ACCOUNT_D.ACCOUNT_SEG4_CODE,
W_GL_ACCOUNT_D.ACCOUNT_SEG4_ATTRIB,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_CODE,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_ATTRIB,
NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG6_CODE,
NULL --W_GL_ACCOUNT_D.ACCOUNT_SEG6_ATTRIB
--
Replace the columns not used for aggregate from the selected and group by columns with NULL in the SQL.
For example, if you only use seg1,2,3, and 4 (cf. file_glacct_segment_config_ora.csv), you change the SQL to use NULL for seg5 and 6, as follows:
W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE, W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB, W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE, W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB, W_GL_ACCOUNT_D.ACCOUNT_SEG3_CODE, W_GL_ACCOUNT_D.ACCOUNT_SEG3_ATTRIB, W_GL_ACCOUNT_D.ACCOUNT_SEG4_CODE, W_GL_ACCOUNT_D.ACCOUNT_SEG4_ATTRIB, NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_CODE, NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG5_ATTRIB, NULL, --W_GL_ACCOUNT_D.ACCOUNT_SEG6_CODE, NULL --W_GL_ACCOUNT_D.ACCOUNT_SEG6_ATTRIB
Replace $$HINT1 with the following value:
A) /*+ OPT_PARAM('_GBY_HASH_AGGREGATION_ENABLED', 'true')
USE_HASH(W_GL_BALANCE_F, W_GL_ACCOUNT_D) */
Use the following parallel hint when further improvement is needed.
B) /*+ OPT_PARAM('_GBY_HASH_AGGREGATION_ENABLED', 'true')
USE_HASH(W_GL_BALANCE_F, W_GL_ACCOUNT_D) PARALLEL(W_GL_BALANCE_F, 4) */
Save the session and check it in.
The logic to calculate the 'Other Operating Expenses' line item in the 'Financials > Profitability > P&L > 'Profit and Loss Quarterly' & 'Profit and Loss YTD' reports is missing expenses defined in Group Account OTHER_OPER_EXP (Other Operating Expenses). In order to avoid confusion between the line item and the group account name, since they are both named 'Other Operating Expenses', a new name (“Miscellaneous Operating Expenses”) has been associated with the OTHER_OPER_EXP group account.
See the document titled "The amount of 'Other Operating Expenses' is incorrect in P/L reports [ID 1102695.1]" on My Oracle Support.
This issue is specific to Oracle eBusiness Suite 11i adaptors used in conjunction with Financial Analytics. Currently 'Chargeback' Transactions are not included in AR Aging Report. The issue only applies to 11i customers. The issue is correctly handled in R12 adapter where Chargeback is marked as INVOICE subtype code.
See the document titled "Ar Aging Transactions Are Missing Chargebacks [ID 1094045.1]" on My Oracle Support.
This issue applies to only those Oracle BI Applications customers who have implemented Procurement and Spend Analytics without implementing Financial Analytics.
The metrics "Days Payables Outstanding" and "AP Turnover" that were part of “Supplier Performance – Supplier AP Transactions” Subject Area under Procurement and Spend Analytics require implementation of additional subject areas from Financial Analytics in order to have those metrics work correctly.
To resolve this packaging issue, these metrics and their associated reports have been removed from Procurement and Spend Analytics V7.9.6.2. If you have a standalone implementation of a previous version of Procurement and Spend Analytics, then you must follow the steps listed in the workaround below.
Modify the metadata repository file (RPD), as follows:
Remove 'Days Payables Outstanding' and 'AP Turnover' metrics from Subject Area, 'Supplier Performance – Supplier AP Transactions' ('Fact - Supplier AP Transactions' presentation table).
Modify Presentation Catalog (Webcat), as follows:
Edit the 'Supplier Performance' dashboard, 'Overview' page and remove 'Days Payable Outstanding' from KPI list in report 'Supplier Performance Key Metrics – 2'.
Edit 'Supplier Performance' dashboard, 'Overview' page, and remove 'Supplier DPO Trend' report from that page.
Edit 'Supplier Performance' dashboard, 'Trends' page and remove 'DPO Trends' report and the guided navigation below it.
Edit 'Supplier Performance' dashboard, 'Supplier Payables' page and remove 'Supplier DPO Trend' report from that page.
Optional step - you may also rearrange the reports on these modified dashboard pages for a better page layout.
Edit 'Top 10 Numbers Of Supplier Payments' report in Answers, and remove 'AP Turnover' column from Criteria tab.
When running the DDL_OLTP.ctl file to import new schema definitions into the Siebel OLTP database, you may receive an error stating that one or more objects already exist in the database.
To resolve the error, use the DDLimp Merge argument (/M Y) in the DDLimp command.
SDE_PSFT_GLJournals_Extract_Full fails due to a data type mismatch for a date column on DB2. SDE_PSFT_GLJournals_Extract_Full returns the following error when the source database is DB2:
The data types of the operands for the operation ">=" are not compatible or comparable. SQLSTATE=42818
The data type of PS_JRNL_HEADER.JOURNAL_DATE is date type. The parameter $$INITIAL_EXTRACT_DATE returns time stamp. Thus, the following condition fails due to data type mismatch:
S_JRNL_HEADER.JOURNAL_DATE >= $$INITIAL_EXTRACT_DATE.
See tech note 1086676.1.
Add the following task level parameter for the SDE_PSFT_GLJournals_Extract task in DAC:
[Task Level Parameter (New)]
Static:
Date: Jan 1, 1970 12:00:00 AM
Variable @DAC_ETL_START_TIME
SQL: Null
Function: SQL Syntax (Date Only)
Format: Null
Connection Type: @DAC_SOURCE_DBTYPE
When the source database is DB2, the SIL_GLAccountDimension_HierarchyUpdate Task fails with the following error:
"[IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001"
Change the 'Commit Interval' to '1' in the PROPERTIES tab of the session WorkFlow, as follows:
Open Informatica Workflow Manager.
Open the SILOS folder.
Open session SIL_GLAccountDimension_HierarchyUpdate.
Navigate to Properties tab.
Set the parameter value 'Commit Interval' to '1'.
Save and check in the session.
The SCD mapping SIL_HouseholdDimension_SCDUpdate_Full is incorrectly inserting records instead of updating them.
In Informatica Designer, navigate to the SIL_VERT folder.
Locate the task SIL_HouseholdDimension_SCDUpdate_Full.
Edit the task and change the 'Treat Source Rows of' parameter value from 'Insert' to 'Update'.
Save and check in the session.
DAC uses the PMCM command line interface to communicate with Informatica. The –lpf switch for passing parameters in a parameter file to Informatica fails intermittently.
There is no workaround for this issue.
The recruitment metric 'Time To Fill (Days)' calculates the number of days lapsed between when a requisition is opened and when it is closed. The formula is calculated as follows:
sum(W_RCRTMNT_EVENT_F.RQSTN_OPEN_TO_RQSTN_CLOSE_DAYS) / nullif(count(distinct W_RCRTMNT_EVENT_F.JOB_RQSTN_WID), 0)
The denominator should be Closed Requisitions not All Requisitions.
The workaround for this issue is to apply the following change to the Oracle BI Enterprise Edition repository file (OracleBIAnalyticsApps.rpd):
Backup the existing repository file.
In the repository file, identify the logical fact table 'Fact - HR - Recruitment Event Information'.
Locate the logical column 'Time To Fill (Days)'.
Double-click the logical column and change the expression, as follows:
From:
Core."Fact - HR - Recruitment Event Information"."Time To Fill (Days) - Internal" / Core."Fact - HR - Recruitment Event Information"."Job Requisitions Count"
To:
Core."Fact - HR - Recruitment Event Information"."Time To Fill (Days) - Internal" / Core."Fact - HR - Recruitment Event Information"."Job Requisitions Closed"
SIL_EmployeeDimension_SCDUpdate map hangs in incremental load when using a SQL Server 2005 target database as the warehouse due to locking issues.
Turn on the read committed snapshot feature in the target database, which avoids the locking issues and allow the maps to complete.
During full ETL loads using DB2 9.1 databases, the truncate task fails. This issue generates an error message in the DAC log file similar to the following:
Failed: SIEBTRUN ('@TABLEOWNER.W_QUOTE_MD') With error message: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/AIX64] SQL0668N Operation not allowed for reason code "3" on table "SIEBEL.W_QUOTE_MD". SQLSTATE=57016.
This issue occurs only during full loads. It does not occur during incremental loads, because tables are not truncated during incremental loads.
This issue is described as "table in load pending state," and no code fix is required. The table must be reverted back to its normal state. The ETL should then run without any further issues. It is recommended that a DBA assists in performing this workaround.
For JD Edwards source systems, the W_CODE_D column is sourced from a UDC table F0005 table. This table has a column length of 10 for the CODE column, with the code left-padded with space characters.
In this release of Oracle Business Intelligence Applications, trim logic has been added to remove extraneous spaces. However, the Source Dependent Extract mapplet does not use trim logic, which means that the resolving of Country and State Names in the mplt_CODES_GeographDimension mapplet fails.
Connect to the Informatica repository using the Informatica Designer.
Connect to and open the SILOS folder.
Locate and then open the mapplet mplt_CODES_GeographDimension.
Check out the mapplet.
Navigate to the expression Exp_Default.
Change the port mapping for the COUNTRY_CODE Output port from this:
LTRIM(RTRIM(IN_COUNTRY_CODE))
To this:
IN_COUNTRY_CODE
Save and check in the updated mapplet.
In Oracle BI Enterprise Edition 11g, an Answers user can create a query that selects data from multiple subject areas that he/she has access to. While this feature provides a user greater flexibility in constructing queries cross multiple subject areas, a user can not select data from different subject areas freely. In order to leverage across subject area querying, the subject areas from which the metrics are sourced must share the same dimensionality.
When a user queries a single subject area, all the metrics exposed in a subject area are compatible with all the dimensions exposed in the same subject area. However, when a user combines metrics and dimensions from multiple subject areas, it is easy to select combinations of metrics and dimensions that are incompatible with one another. For example, a metric in one subject area might not be dimensioned by Project. If attributes from the Project dimension from another subject area are added to the request along with metrics that are not dimensioned by Project, then the query might fail to return results, and could return no data or the BI Server error "No fact table exists at the requested level of detail: XXXX."
No workaround is available for this issue.
This issue applies to only those Oracle BI Applications customers who have implemented Procurement and Spend Analytics for E-Business Suite (EBS) 12.1.3 along with Feature Pack for Oracle Contract Lifecycle Management (CLM) for Public Sector. CLM Feature Pack is deployed as an add-on to Oracle EBS 12.1.3 for delivering an integrated acquisition management solution for federal government procurement.
CLM functionality has introduced changes to the Oracle EBS data model that affects data extraction and loading for Oracle Procurement and Spend Analytics. Customers with Oracle EBS 12.1.3 and the CLM feature pack must apply additional filters to exclude CLM records and to ensure that it does not interfere with V7.9.6.3 Procurement and Spend Analytics functionality.
No workaround is available for this issue.
Two indices are required during ETL task PLP_APXACTSGROUPACCOUNT_A1_LOAD that are marked as 'Query' rather than 'ETL', and are therefore dropped in DAC before the task runs. This affects performance. DAC metadata needs to be changed to mark them as ETL indices.
Objects : Indices W_AP_XCT_GAD_D_F11 and W_AP_XCT_GAD_D_F6. Modification: Change the Index Usage for these indices from Query to ETL so that they may be used during the task run in incremental mode.
In PSFT, the GL Account IDs from various transaction fact tables are inserted into a temporary table where the chartfields are split and stored in respective columns. From V7.9.6.2, additional project chartfields are included that must be used in every transaction fact table. If the project chartfields are not applicable for a transaction fact, then nulls have to be concatenated in order to have an equal number of fields concatenated so that the derive logic will derive based on the total supported set. This functionality is missing in GL Balance, and must be implemented by following the workaround below.
Open Informatica PowerCenter Designer and edit the mapping SDE_PSFT_Stage_GLBalance_Extract in the appropriate PSFT adaptor to make the following changes.
Edit the expression transformation Exp_GL_Balance_ID_Formation and change the expression of VAR_GL_ACCOUNT_ID port.
Change the expression value of VAR_GL_ACCOUNT_ID to:
VAR_ACCOUNT_SETID||'~'||INP_BUSINESS_UNIT||'~'||INP_ACCOUNT||'~'||INP_ALTACCT| |'~'||INP_DEPTID||'~'||INP_OPERATING_UNIT||'~'||INP_PRODUCT||'~'||INP_FUND_COD E||'~'||INP_CLASS_FLD||'~'||INP_PROGRAM_CODE||'~'||INP_BUDGET_REF||'~'||INP_AF FILIATE||'~'||INP_AFFILIATE_INTRA1||'~'||INP_AFFILIATE_INTRA2||'~'||INP_CHARTF IELD1||'~'||INP_CHARTFIELD2||'~'||INP_CHARTFIELD3||'~'||INP_PROJECT_ID||'~'||I NP_STATISTICS_CODE||'~'||'~'||'~'||'~'||'~'||'~'
Note that the project chartfields are not applicable in PS_LEDGER, which is the source for GL balances, and hence you must concatenate nulls for the remaining chartfields.
Validate and save the changes.
If sourcing from multiple Oracle EBS 11i instances and using more than one DAC execution plan, COGS SDE mappings might fail due to duplicate records generated in W_GL_COGS_FS. In the extract SQL of SDE_ORA_GLCOGSFact_Derive (source table W_GL_COGS_F_TMP / W_GL_ACCOUNT_D, target table W_GL_COGS_FS), the join condition does not have a check on the DATASOURCE_NUM_ID.
See the document titled "How to avoid duplicate records in W_GL_COGS_FS for multi-instances EBS source? [ID 1312078.1]" on My Oracle Support.
ETL logic to populate the exchange date in the General Ledger (GL) fact derives the date from the posted date. In cases where a journal is created in one period but then is posted in the next period, the ETL populates the exchange rate based on the posted date. However, in GL the currency conversion rate for any foreign currency journals should lie within the accounting period in which the journal is created. Therefore, by using posted date as exchange date, the derived conversion rate is thereafter incorrect.
See the document titled "Altering Currency Conversion Exchange Rate When Using Adjustment Date Instead of Posted Date for the GL Module [ID 887647.1]" on My Oracle Support.
Three additional chart fields CHARTFIELD1, CHARTFIELD2 and CHARTFIELD3 must be included in the INTEGRATION_ID for W_GL_BALANCE_F. Otherwise, if all other chart fields included are null, then Integration ID will be duplicated, leading to a unique constraint violation.
See the document titled "Load for Fact Table W_GL_BALANCE_F fails in BI Apps 7.9.6.2 for Peoplesoft 9.0 source [ID 1308336.1]" on My Oracle Support.
The full session of the SDE_ORA_APTransactionFact_Distributions task extracts data from the Oracle EBS source system based on the $$LAST_EXTRACT_DATE, instead of the $$INITIAL_EXTRACT_DATE. The correct extraction should be based on the $$INITIAL_EXTRACT_DATE.
See the document titled "SQ FOR SDE_ORA_APTRANSACTIONFACT_DISTRIBUTIONS_FULL USING INCORRECT EXTRACT DATE PARAMETER [ID 1313832.1]" on My Oracle Support.
This issue affects Siebel 8.1.1 Source System with DB2 V9.7. After ETL, you must verify the following data issues in the data warehouse tables:
The time stamp is rounded off to 00:00:00 for all Date columns. For example, in the table W_ACTIVITY_D, the column ACTUAL_END_DT has the time stamp rounded off to 00:00:00, though the bench has a time stamp value. E.g:
Bench pre-ETL:
Integration ID ACTUAL_END_DT 05-HMDY5 2006-06-06 05:17:01
Bench post-ETL:
Integration ID ACTUAL_END_DT 05-HMDY5 2006-06-06 00:00:00
Decimal values for numeric date are not truncated after the decimal. For example, in the table W_ACTIVITY_D, the column A_JULIAN_END_DT truncates decimal values. E.g:
Bench pre-ETL:
Integration ID A_JULIAN_END_DT 1-10RIU 2452128.9046875
Bench post-ETL:
Integration ID A_JULIAN_END_DT 1-10RIU 2452128
This is a known issue affecting non-Oracle databases. The ETL for "MEMBER TIER MOVEMENT AGGREGATION" fails with a transformation error if the target warehouse database is non-Oracle.
In Informatica PowerCenter Designer, use the Mapping Designer tool to modify the PLP_LoyMemberTierMovementQtrAggr mapping as follows:
For Microsoft SQL Server databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.
Change the Database Type to Microsoft SQL Server.
Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'bit' and change the value to 'varchar'.
For DB2 databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.
Change the Database Type to DB2.
Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.
For Teradata databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.
Change the Database Type to TeraData.
Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.
Module: All.
Source OLTP: All.
Database: All.
This issue applies to Oracle BI Applications customers who are using Oracle Business Intelligence Enterprise Edition Release 11.1.1. This issue is related to multilingual support and is applicable only to languages other than US English.
Whenever Oracle BI Applications dashboard descriptions are available, they are displayed as mouse-over tool tips within the Dashboards drop-down menu in Oracle BI EE. However, due to a known issue in Oracle BI EE 11.1.1 (11.1.1.5.0 and lower), non-English translations are also displayed in US English.
Note: The following work-around lists the steps to view a dashboard description in the desired target language. It does not resolve the issue of tooltips being displayed only in US English.
Open your web browser and connect to your Oracle Business Intelligence environment.
At the sign in screen, provide your user ID and password, and the language in which you want to work.
From your My Dashboard page or from the Oracle Business Intelligence Home page, select the Catalog option from the global header to open the Catalog page.
From the Folders pane, navigate to the Shared Folders subfolder, select the folder for the application that you are working with, and then select the Dashboards folder.
In the Dashboards folder, the dashboard descriptions will be displayed below the dashboard name in the language you selected at the sign on screen.
Descriptions are also displayed under dashboard properties. To access dashboard properties, select the dashboard, right mouse click and then select Properties option from the dropdown.
Module: Marketing Analytics - B2B Customer Insight Analysis.
Source OLTP: All supported Siebel versions.
Database: All supported databases.
The Next Product Purchased report under B2B Customer Insight dashboard does not report the right count of the metrics. This is due to the usage of an obsolete foreign key W_ORDERITEM_F.ACCNT_WID. The new customer dimension model should use W_ORDERITEM_F.CUSTOMER_WID instead of W_ORDERITEM_F.ACCNT_WID.
In Oracle BI Administration Tool, open your RPD, go to Physical Layer and expand the physical database: Oracle Data Warehouse.
Scroll down to object: Fact_W_ORDERITEM_F_PRODUCTS_SAME_ACCOUNT_OV_F.
Double click on the object, display the General tab, and replace the SQL statement with the following:
SELECT F.PROD_WID AS PROD_WID, F2.PROD_WID AS NEXTORDER_PROD_WID, F.CUSTOMER_WID AS ACCT_WID, F.ORDER_WID, F2.ORDER_WID AS NEXTORDER_WID FROM VALUEOF(OLAPTBO).W_ORDERITEM_F F, VALUEOF(OLAPTBO).W_ORDERITEM_F F2 WHERE F.CUSTOMER_WID = F2.CUSTOMER_WID and F.PROD_WID <>0 and F2.PROD_WID <>0 AND F2.ORDER_WID = (SELECT MIN(ORDER_WID) FROM VALUEOF(OLAPTBO).W_ORDERITEM_F FIN WHERE FIN.CUSTOMER_WID = F.CUSTOMER_WID AND FIN.ORDER_WID > F.ORDER_WID) AND F.CUSTOMER_WID <> 0
Save the RPD.
Restart Oracle BI EE.
W_POSITION_DH_PRE_CHG_TMP is a temporary table used in the incremental run of the position hierarchy. This table should be truncated prior to each incremental run. This setting is missing in the installed DAC metadata. Without this setting, the load of W_POSITION_DH_PRE_CHG_TMP could become slower. In addition, some of the post load fact updates like PLP_PayrollFact_PositionHierarchy_Update could report the following error:
Error: ORA-01427: single-row subquery returns more than one row
Turn on the 'Truncate Always' setting in DAC metadata for the table W_POSITION_DH_PRE_CHG_TMP, as follows:
In DAC, navigate to Design, then [In your specific Container], then Tasks.
Query for and select "SIL_PositionDimensionHierarchy_PreChangeTmp".
Navigate to Task, then the "Target Tables" sub-tab, then select the "Truncate Always" property for the W_POSITION_DH_PRE_CHG_TMP table.
Save the changes.
This issue affects Oracle E-Business Suite customers implementing Recruitment Analytics only. The hire date on the recruitment fact is not the original hire date for internal job applicants. The hire date is the date that an applicant is hired regardless whether the applicant is an internal or external applicant. When an employee applies for a job internally, the hire date in the recruitment fact stores the date of joining the new job and not the original hire date for the internally hired/transferred employee. This is by design.
When an employee applies for a job internally, the employee becomes an employee-applicant. Recruitment analytics correlates the internal applicant with that of the employee getting hired in the job. This process is done in the mapping SDE_ORA_ApplicantEventFact_EmpAplAssignments. This process correlates the applicant assignment with the employee assignment. The information is stored in table W_ORA_APPL_EVENT_F_TMP. The logic for this correlation can vary based on the implementations. During implementation, the conditions specified in the mapplet source qualifier SQL mplt_BC_ORA_ApplicantEventFact_EmpAplAssignments can be made more restrictive. You can add more conditions in the source qualifier SQL so that the correlation is more accurate. For example, more restrictions can be applied with a condition like:
a. asg_apl.vacancy_id = asg_emp.vacancy_id b. asg_apl.application_id = asg_emp.application_id c. asg_apl.vacancy_id = asg_emp.vacancy_id and asg_apl.application_id = asg_emp.application_id
This issue affects Oracle Procurement and Spend Analytics. The measure 'Expense Reimbursement Cycle Time' as shipped with this release does not work in the subject area Employee Expenses – Overview. The measure uses the column EXPENSE_INVOICE_POSTED_DT_WID in the fact table W_EXPENSE_F fact, but this column is sourced from AP_EXPENSE_REPORT_HEADERS_ALL.EXPENSE_LAST_STATUS_DATE during ETL.
This issue is addressed by Oracle E-Business Suite patch 10038359.
This sections contains issues that are specific to Teradata databases, and contains the following topics:
Section 1.4.2, "Sales Analytics Reports With Top 'N' Filters Error Against Teradata"
Section 1.4.3, "PLP_LoyMemberTierMovementQtrAggr Fails on Teradata Databases"
Section 1.4.4, "SIEBEL SIA: Interval Field Overflow Error On Teradata Database"
This section is only relevant if you are running your data warehouse in a Teradata database. For detailed information about using the TPump external loader, refer to Informatica documentation and Teradata documentation.
When using a TPump command, if get the following error messages (or similar error messages), ensure that you have set the parameters listed below:
TRANSF_1_1_1> DBG_21216 Finished transformations for Source Qualifier [Sq_W_POSITION_DS]. Total errors [0] WRITER_1_*_1> WRT_8047 Error: External loader process [2192] exited with error [12] WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] WRITER_1_*_1> WRT_8004 Writer initialization failed [Error opening session output file [\\.\pipe\w_XXXX_d1.out] [error=]]. Writer terminating. WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] WRITER_1_*_1> WRT_8047 Error: External loader process [2192] exited with error [12] WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] WRITER_1_*_1> WRT_8088 Writer run terminated. [External loader error.]
To run the Tpump loader, you specify the following parameters at the command line in the order specified:
Database Name: Enter the Teradata Database name.
Error Database Name: Enter the Teradata Database name.
Log Table Database Name: Enter the Teradata Database name.
Error Table name: Enter the name of the table to use as the error table.
Log Table name: Enter the name of table to use as the log table.
The following attributes can be specified at the connection level:
Database
Error Database
Log Table Database
The following attributes are specified at the workflow level:
Error Table
Log Table
Not applicable.
This issue occurs in Sales Analytics with Teradata when a Top 'N' report is executed that returns no results for the particular metrics that have this filter applied. The following out-of-the-box Sales Analytics reports are affected:
Sales -> Customers ->Overview tab ->Top Accounts.
Sales -> Customers ->Overview tab -> Accounts by Region.
Sales -> Customers ->Account Summary tab -> Account Order History.
Sales -> Customers ->Account Summary tab -> Top Account Opportunities.
Sales -> Customers ->Account Summary tab -> Quote & Order History.
Sales -> Customers ->Account Summary tab -> Account Activity.
Sales -> Pipeline -> Details tab -> Top 10 Deals by Number of Days in Stage.
Sales -> Pipeline -> My Pipeline tab -> My Top Stalled Opportunities.
Open the report in 'Edit' mode.
Go to 'Criteria' - > Click on the 'Edit Formula' option available on the column for which the Top 'N' filter is applied.
If the formula is "X.Y" (where X = Name of the fact folder and Y = name of the metric), then change this to IFNULL("X.Y", 0).
For example, if the metric is "Pipeline Facts. Number of Days in Stage", then change this to IFNULL ("Pipeline Facts. Number of Days in Stage", 0).
Replace the filter already present for Top 'N' with the following two filters:
Top 'N' (as the existing filter but on the column with the formula updated in Step 3.)
AND is greater than 0.
Save the report.
Note: The change is testing for NULL values and making NULLS as zeroes, otherwise the syntax fails against Teradata.
For details, see Section 1.3.51, "PLP_LoyMemberTierMovementQtrAggr fails On Non-Oracle Databases for Oracle Loyalty Analytics".
If you encounter an "Interval Field Overflow" error on the Teradata database platform, make sure that your date/time interval does not exceed the Teradata DB limit. For example, Teradata's allowed maximum number of days interval between two dates is 9999. For more information, please consult Teradata documentation.
|
 Copyright © 2009, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|