Cost and Margin Fields From Revenue as Currency (Doc ID 2102154.1)

Last updated on FEBRUARY 01, 2016

Applies to:

Oracle Fusion Sales Cloud Service - Version 11.1.9.2.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.9.2.0 version, Analytics

ACTUAL BEHAVIOR
---------------
Customer is having lost of custom calculations to convert fields such as Margin and Costs from Revenue to currencies and it's also hindering what they can do using the 'FILTER(x USING)' function as it just ignores the filter criteria when set to something like 'FILTER(SUM("Revenue"."Revenue Margin Amt")...'

EXPECTED BEHAVIOR
-----------------------
Expect that Margin and Cost fields become Currency fields.

The following report was created by customer:

SELECT
0 s_0,
"Sales - CRM Opportunities and Products Real Time"."Revenue"."CRM Currency Conversion Rate" s_1,
"Sales - CRM Opportunities and Products Real Time"."Revenue"."Revenue Margin Amt" s_2,
"Sales - CRM Opportunities and Products Real Time"."Time"."Quarter" s_3,
"Sales - CRM Opportunities and Products Real Time"."Time"."Year" s_4,
VALUEOF(NQ_SESSION.USER_PREFERRED_CURR_Conversion_Rate) s_5,
"Sales - CRM Opportunities and Products Real Time"."Pipeline Detail Facts"."Opportunity Line Revenue" s_6,
FILTER(SUM("Sales - CRM Opportunities and Products Real Time"."Revenue"."Revenue Margin Amt"*"Sales - CRM Opportunities and Products Real Time"."Revenue"."CRM Currency Conversion Rate"*VALUEOF("NQ_SESSION"."USER_PREFERRED_CURR_Conversion_Rate")) USING ("Sales - CRM Opportunities and Products Real Time"."Opportunity"."Sales Stage Name" IN ('07 - Closed / Won','05 - Commit'))) s_7,
FILTER(SUM("Sales - CRM Opportunities and Products Real Time"."Revenue"."Revenue Margin Amt"*"Sales - CRM Opportunities and Products Real Time"."Revenue"."CRM Currency Conversion Rate"*VALUEOF("NQ_SESSION"."USER_PREFERRED_CURR_Conversion_Rate")) USING ("Sales - CRM Opportunities and Products Real Time"."Opportunity"."Sales Stage Name" IN ('07 - Closed'))) s_8,
FILTER(SUM("Sales - CRM Opportunities and Products Real Time"."Revenue"."Revenue Margin Amt"*"Sales - CRM Opportunities and Products Real Time"."Revenue"."CRM Currency Conversion Rate"*VALUEOF("NQ_SESSION"."USER_PREFERRED_CURR_Conversion_Rate")) USING ("Sales - CRM Opportunities and Products Real Time"."Opportunity"."Sales Stage Name" IN ('07 - Closed','05 - Commit','04 - Expected'))) s_9
FROM "Sales - CRM Opportunities and Products Real Time"
ORDER BY 1, 5 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot use in the report FILTER() function

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms