OFSAA EFPA Is Not Using FSI_EXCHNG_RATE_DIRECT_ACCESS for MGMT_REPORTING_MGMT_LEDGER_TRM When Calculating Exchange Rates

(Doc ID 2343760.1)

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
Reporting

Symptoms

On EFPA 8.0.4, the data load function is using incorrect table to pull exchange rates.

ACTUAL BEHAVIOR
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) =
( SELECT
EXCHANGE_RATE*F.N_EOP_BAL,
EXCHANGE_RATE*F.N_AVG_BAL,
EXCHANGE_RATE*F.N_MOVEMENT
FROM fsi_exchange_rate_hist E
WHERE E.from_currency_cd=F.v_iso_currency_cd
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

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