Currency Conversion Exchange Rounding Issue in PFT Causing Large Variance in Amount (Doc ID 2177712.1)

Last updated on MAY 10, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 6.1.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
Multicurrency
Multi-currency
Exchange Rates

Symptoms

On PFT 6.1.1, there appears to be a rounding issue during currency conversion in Static Table Driver allocations.

ACTUAL BEHAVIOR
The created Global Temporary Table (GTT) table is using a non rounded rate as entered and saved into FSI_EXCHNG_RATE_DIRECT_ACCESS. However, the merge statement uses an incorrect rounded rate:

Here is an example from this defect.

1. Convert Original to USD: 'JPY', vm.MONTH_01 / 0.008258218
2. Convert back to Original: 'JPY' , 0.008258, When OFSAA converts balance back to USD, it uses rounded rate 0.008258. This creates a big difference from source balance.

The PFT debug log shows the same:

Module Logging OFS Access module data: CREATE
CREATE GLOBAL TEMPORARY TABLE GLBL7730TMP ON COMMIT PRESERVE ROWS
AS
SELECT *
FROM (
SELECT vm.ACCUM_TYPE_CD, vm.ALT_GL_ACCOUNT_ID,
vm.BALANCE_TYPE_CD, vm.BANK_IND_ID, vm.BUSINESS_PROD_ID,
vm.COMMON_COA_ID, vm.CONSOLIDATION_CD, vm.CURRENCY_TYPE_CD,
vm.FINANCIAL_ELEM_ID, vm.GL_ACCOUNT_ID, vm.ISO_CURRENCY_CD,
vm.IDENTITY_CODE, vm.LEGAL_ENTITY_ID, DECODE (YEAR_S, 2016,
DECODE (vm.ISO_CURRENCY_CD, 'ARS', vm.MONTH_01 / 0.071676881,
'AUD', vm.MONTH_01 / 0.70695, 'BRL', vm.MONTH_01 / 0.25005001,
'CAD', vm.MONTH_01 / 0.713876255, 'CHF', vm.MONTH_01 /
0.976258468, 'CLP', vm.MONTH_01 / 0.001405284, 'CNY', vm.MONTH_01
/ 0.151648418, 'DKK', vm.MONTH_01 / 0.145128824, 'EUR',
vm.MONTH_01 / 1.0829229, 'GBP', vm.MONTH_01 / 1.424205, 'HKD',
vm.MONTH_01 / 0.128502001, 'ILS', vm.MONTH_01 / 0.25235383,
'JPY', vm.MONTH_01 / 0.008258218, 'MXN', vm.MONTH_01 / <=== SEE non rounded exchange rate
0.055035531, 'NOK', vm.MONTH_01 / 0.115056051, 'NZD', vm.MONTH_01
/ 0.647899, 'PEN', vm.MONTH_01 / 0.287653895, 'SEK', vm.MONTH_01
/ 0.116447919, 'SGD', vm.MONTH_01 / 0.701943541, 'THB',

...

Module Logging OFS Access module data: MERGE INTO LEDGER_STAT TARGET USING (SELECT *
FROM (
SELECT 10722.000000 identity_code, 2016 year_s , 'D'
accum_type_cd , 100 consolidation_cd , 101 balance_type_cd,
ISO_CURRENCY_CD , BUSINESS_PROD_ID, 120.000000 SEGMENT_ID,
SERVICE_ID, LEGAL_ENTITY_ID, ALT_GL_ACCOUNT_ID, RESP_CC_ID,
BANK_IND_ID, 10457.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, PRODUCT_ID, SUM( (src_amount * DECODE(
ISO_CURRENCY_CD, 'AED' , 1.000000, 'ARS' , 0.071677, 'AUD' ,
0.706950, 'BRL' , 0.250050, 'CAD' , 0.713876, 'CHF' , 0.976258,
'CLP' , 0.001405, 'CNH' , 1.000000, 'CNY' , 0.151648, 'COP' ,
1.000000, 'CZK' , 1.000000, 'DEM' , 1.000000, 'DKK' , 0.145129,
'EUR' , 1.082923, 'FRF' , 1.000000, 'GBP' , 1.424205, 'HKD' ,
0.128502, 'HUF' , 1.000000, 'IDR' , 1.000000, 'ILS' , 0.252354,
'INR' , 1.000000, 'ITL' , 1.000000, 'JPY' , 0.008258, 'KRW' , <== See ROUNDED rate used here
1.000000, 'MXN' , 0.055036, 'MYR' , 1.000000, 'NOK' , 0.115056,
'NZD' , 0.647899, 'OMR' , 1.000000, 'PEN' , 0.287654, 'PHP' ,

EXPECTED BEHAVIOR
Expect the correct non rounded exchange rate to be used in the engine calculations.

The issue can be reproduced at will with the following steps:
1. Created new Static Table Driver
2. Added a new USD -> JPY rate: USD JPY 31-JAN-16 121.0915
3. Ran Rate Validation and confirm proper rates in the tables:

select * from FSI_EXCHNG_RATE_DIRECT_ACCESS where from_currencY_cd in ('USD','JPY');

4. Create new Static Table Driver allocation:
5. Application Preferences: 01/31/2016
6. Confirmed ledger_stat has rowS:

select distinct financial_elem_id, iso_currencY_cd from ledger_stat where year_s = 2016 and month_01 <> 0;

7. Run the allocation
8. Check the debug log

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