Exchange Rate Validation Gets 'ORA-06502: PL/SQL: numeric or value error' and Populates Wrong EFFECTIVE_TO_DATE
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)
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 Currency||To Currency||Effective From Date||Effective To Date||Exchange Rate|
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.
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