Instrument to Ledger Allocation Fails to Convert Currency Back to Original Currency (Doc ID 2267362.1)

Last updated on MAY 23, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 6.1.1.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Profitability Management (PFT) 6.1.1.1, when you run an Allocation that moves data from an instrument table to Ledger Stat, with 'Currency' set to <Same As Source> in the Output screen, the currency conversion is wrong in the output records.  The Allocation converts balances from the foreign currency to the functional currency and but not back to the foreign currency.

When you check the "merge into" SQL in the ofspa.xxxxxxx.log in $FIC_DB_HOME/log/FusionApps, the currency conversion code is missing from the top section:

Example of correct code:

Module Logging OFS Access module data: MERGE INTO LEDGER_STAT TARGET USING (SELECT *
FROM (
SELECT SRC_IOVW.* , 115.000000 AGGREGATE_METHOD, (CASE WHEN
          AGGRLVS.ACCOUNT_TYPE = 150
OR      AGGRLVS.ACCOUNT_TYPE = 610
OR      AGGRLVS.ACCOUNT_TYPE = 300
OR      AGGRLVS.ACCOUNT_TYPE = 310
OR      AGGRLVS.ACCOUNT_TYPE = 400
OR      AGGRLVS.ACCOUNT_TYPE = 500 THEN SRC_IOVW.SOURCE_AMOUNT* - 1
          ELSE SRC_IOVW.SOURCE_AMOUNT END ) BALANCE_AMOUNT
FROM (SELECT SUM(src_amount * DECODE (ISO_CURRENCY_CD, 'USD', 1,
          'CNY', 1/ 0.15, 1) ) SOURCE_AMOUNT, ORG_UNIT_ID, GL_ACCOUNT_ID,
           COMMON_COA_ID, ISO_CURRENCY_CD
FROM (SELECT SUM(NVL(CUR_NET_BOOK_BAL_C, 0) * 1 ) src_amount ,
         a.ORG_UNIT_ID, a.GL_ACCOUNT_ID, a.COMMON_COA_ID,
         a.ISO_CURRENCY_CD
from (

Example of wrong code:

Module Logging OFS Access module data: MERGE INTO LEDGER_STAT TARGET USING (SELECT *
FROM   (SELECT SUM(src_amount * - 1 ) BALANCE_AMOUNT, ORG_UNIT_ID,
           GL_ACCOUNT_ID, COMMON_COA_ID, ISO_CURRENCY_CD
FROM (SELECT SUM(NVL(END_BAL, 0) * 1 ) src_amount ,
          a.ORG_UNIT_ID, a.GL_ACCOUNT_ID, a.COMMON_COA_ID,
          a.ISO_CURRENCY_CD
from (


The "merge into" SQL is missing the currency logic in the code above.

All currencies are reporting currencies.  Both sets of rate data are populated in FSI_EXCHNG_RATE_DIRECT_ACCESS.

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