Exchange Rate Validation Gets 'ORA-06502: PL/SQL: numeric or value error' and Populates Wrong EFFECTIVE_TO_DATE (Doc ID 2295642.1)

Last updated on AUGUST 15, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 8.0.0 and later
Oracle Financial Services Asset Liability Management - Version 8.0.0 and later
Oracle Financial Services Funds Transfer Pricing - Version 8.0.0 and later
Oracle Financial Services Analytical Applications Infrastructure - Version 8.0.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Profitability Management (PFT) 8.0, when you run the Exchange Rate Validation function, FN_RATEVALIDATION, the EFFECTIVE_TO_DATE and EXCHANGE_RATE_CONVERT_TYPE_CD columns are not populated correctly in FSI_EXCHNG_RATE_DIRECT_ACCESS for a specific time period.

For example, if you run for the period from '20110131' to '20161130', the EFFECTIVE_TO_DATE is populated with an end of month date instead of the day before the end of the month:

From CurrencyTo CurrencyEffective From DateEffective To DateExchange Rate
INR USD 31-AUG-2013 30-SEP-2013 66.24710169
INR USD 30-SEP-2013 31-OCT-2013 62.44536031
INR USD 31-OCT-2013 30-NOV-2013 61.47037128

The EFFECTIVE_TO_DATE is 31-OCT-13 when it should be 30-OCT-13.  Similarly, the EFFECTIVE_TO_DATE is 30-SEP-13 when it should be 29-SEP-13.

Additionally, all rows have EXCHANGE_RATE_CONVERT_TYPE_CD =3 when half the rates should have 3 and the inverse rates should have 4.

Finally, when you check FSI_MESSAGE_LOG for the Exchange Rate Validation batch, you find the following ORA-06502 error exists:

Entering Into FILLTIMEGAP
ERRORIN FILLTIMEGAP ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define


Note: If you run FN_RATEVALIDATION only one month at a time, only certain dates have the wrong EFFECTIVE_TO_DATE and EXCHANGE_RATE_CONVERT_TYPE_CD.

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