Exponential Exchange Rates in PFT Allocation Execution Query Causing ORA-00942 Errors and Failed Allocation (Doc ID 2208283.1)

Last updated on NOVEMBER 28, 2016

Applies to:

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

Symptoms

On PFT 8.0.2, Exponential Exchange rates are formed in PFT Allocation create the global temp table (GTT) execution query which causes the allocation to error and return no results.

ERROR

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [85] - SQL function not found! Oracle Error:
ORA- 00942: table
or view does not exist Driver Function: drv_oci::Execute() SQL
Statement: TRUNCATE TABLE GLBL16593TMP


Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [8] -
DROP TABLE Oracle Error: ORA- 00942: table
or view does not exist Driver Function: drv_oci::Execute() SQL
Statement:
DROP TABLE GLBL16593TMP

Executing an Allocation Run and the status is Success in OFSAA UI, However, FSI_MESSAGE_LOG table shows table or View does not exists errors. Upon verifying the log, the global temp table is not getting created because the exchange rates are coming in exponential form (Example : mv.ENTERED_BALANCE/ 4.7e- 05). When running this query in the back end, it gives missing right parenthesis error:

Module Logging OFS Access module data: CREATE
CREATE GLOBAL TEMPORARY TABLE GLBL16593TMP ON COMMIT PRESERVE
ROWS AS
SELECT *
FROM (SELECT mv.AS_OF_DATE, mv.BALANCE_TYPE_CD, mv.BUSS_SEG_ID,
mv.COMMON_COA_ID, mv.CONSOLIDATION_CD, mv.COSTCTR_ID,
mv.DATASET_ID, DECODE(FISCAL_YEAR, 2017,
DECODE(mv.ISO_CURRENCY_CD, 'AED', mv.ENTERED_BALANCE/ 0.272253,
'ALL', mv.ENTERED_BALANCE/ 0.008057, 'AMD', mv.ENTERED_BALANCE/
0.002101, 'ANG', mv.ENTERED_BALANCE/ 0.571429, 'ARS',
mv.ENTERED_BALANCE/ 0.115902, 'ATS', mv.ENTERED_BALANCE/
0.082364, 'AUD', mv.ENTERED_BALANCE/ 0.79435, 'AWG',
mv.ENTERED_BALANCE/ 0.558659, 'AZM', mv.ENTERED_BALANCE/
0.612257, 'AZN', mv.ENTERED_BALANCE/ 1.276976, 'BAM',
mv.ENTERED_BALANCE/ 0.575968, 'BBD', mv.ENTERED_BALANCE/ 0.5,
'BDT', mv.ENTERED_BALANCE/ 0.012853, 'BEF', mv.ENTERED_BALANCE/
0.028095, 'BGN', mv.ENTERED_BALANCE/ 0.579542, 'BHD',
mv.ENTERED_BALANCE/ 2.652168, 'BIF', mv.ENTERED_BALANCE/
0.000641, 'BMD', mv.ENTERED_BALANCE/ 1, 'BND',
mv.ENTERED_BALANCE/ 0.745684, 'BOB', mv.ENTERED_BALANCE/
0.144718, 'BRC', mv.ENTERED_BALANCE/ 0.244852, 'BRL',
mv.ENTERED_BALANCE/ 0.388674, 'BSD', mv.ENTERED_BALANCE/ 1,
'BWP', mv.ENTERED_BALANCE/ 0.10385, 'BYR', mv.ENTERED_BALANCE/
4.7e- 05, 'BZD', mv.ENTERED_BALANCE/ 0.501253, 'CAD'
, <== example rate

Exchange rates:
Have loaded the exchange rates from other currencies to USD and run the Rate Validation which in turn populated the inverse exchange rates and these exchange rates are used in the allocation query. For the following 5 currencies exchange rates, the exponential rates are coming in the query. The exchange rates for these are more than 10000.
1) USD -> BYR
2) USD -> IDR
3) USD -> IRR
4) USD -> ROL
5) USD -> VND

Confirmed that if the large / inversely small rates which are generating as exponents are removed then the issue is resolved.

The issue can be reproduced at will with the following steps:
1. Activate all currencies
2. Load appropriate exchange rates
3. Run Rate Validation
4. Run allocation for Management Ledger

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