Query on Calculated Field with Currency Code Field and Exchange Rate does not Work (Doc ID 2884065.1)

Last updated on DECEMBER 01, 2022

Applies to:

Symptoms

When user run a query against a calculated currency field with "exchange date field", the result doesn't return the expected value as triggers a SQL statement against the original field value.

The issue can be reproduced at will with the following steps:
1. Create a calculated field "PF_My_Cvt_Revenue" on Opportunity BC based on database field "Primary Revenue Amount (S_OPTY.SUM_REVN_AMT)", "Currency Code" field with value "EUR" and the "Exchange Date Field" as "Primary Revenue Close Date".
For more details refer to steps on How To Configure Immediate Conversion to Any Currency? (Doc ID 475728.1)
2. Verify conversion works displaying expected value
3. Query using for example  > 100.000" on calculated currency field, note that it returns records where the calculated currency field is "94.488,19 €". In this example the "Primary Revenue Amount (SUM_REVN_AMT)" is "120.000,00 $", i.e., greater than "100.000"
4. Object Manager Logs note SQL statement against column "SUM_REVN_AMT"

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.