OFSAA EFPA Is Not Using FSI_EXCHNG_RATE_DIRECT_ACCESS for MGMT_REPORTING_MGMT_LEDGER_TRM When Calculating Exchange Rates
Last updated on DECEMBER 28, 2017
Applies to:Oracle Financial Services Enterprise Financial Performance Analytics - Version 8.0.4 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Business Intelligence Enterprise Edition (OBIEE)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA/PFTBI) previously known as Oracle Financial Services Profitability Analytics
On EFPA 8.0.4, the data load function is using incorrect table to pull exchange rates.
Below is the update query used in the EFPA data load function : MGMT_REPORTING_MGMT_LEDGER_TRM
UPDATE FCT_MGMT_REPORTING F SET (N_EOP_BAL_RCY,N_AVG_BAL_RCY,N_MOVEMENT_RCY) =
FROM fsi_exchange_rate_hist E
AND E.to_currency_cd = 'USD' AND TO_NUMBER(TO_CHAR(EFFECTIVE_DATE,'YYYYMMDD')) = F.N_DATE_SKEY
) where v_iso_currency_cd!='USD' AND N_DATE_SKEY= to_NUMBER(to_char(last_day(to_date(2016||12, 'yyyymm')),'yyyymmdd')) and N_RUN_SKEY=0
This update query will never update any records in THE reporting currency due to below reason.
1. The PFT engine uses the FSI_EXCHNG_RATE_DIRECT_ACCESS table instead of the FSI_EXCHANGE_RATE_HIST table for exchange rates.
For example there is a currency conversion from INR to USD conversion rate = 0.01538
As per the general notation
from_currency_cd = INR
to_currency_cd = USD
EXCHANGE_RATE = 0.01538
However, for OFSAA it is populated in the FSI_EXCHANGE_RATE_HIST table in the below way:
from_currency_cd = USD
to_currency_cd = INR
EXCHANGE_RATE = 0.01538
Only then the PFT engines takes the correct exchange rate.
2. All the PFT exchange rates data has been populated in the format mentioned in point no : 1
3. Only one side of the exchange rates are populated to the FSI_EXCHANGE_RATE_HIST table. The reversal is automatically taken care by the rate validation program in PFT and populated to the FSI_EXCHNG_RATE_DIRECT_ACCESS table.
4. However, when the EFPA load is executed, it looks for the from_currency_cd != 'USD' for which it will never find any data and hence will not update any of the reporting balance (N_MOVEMENT_RCY) from FSI_EXCHANGE_RATE_HIST table. It should be looking at the FSI_EXCHNG_RATE_DIRECT_ACCESS table.
The issue can be reproduced at will with the following steps:
1. Run MGMT_REPORTING_MGMT_LEDGER_TRM data load and check output
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