Clarity: Changing Currency Type in WIP Adjustment Errors Out When Exchange Rates Do Not Exist For Both Directions Between The From AND To Currency Types

Document ID : KB000025371
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Changing Currency Type in WIP Adjustment generates a cryptic error that is not helpful to resolve the issue.

An cryptic error is displayed when user attempts to change the currency type for Cost and/or Rate, and exchange rates only exist in the matching direction for the 'From' and 'To' currency types.
If you have exchange rate set up for LTL to USD, and not USD to LTL, you will receive the error when you try to change the currency type from USD to LTL in a WIP Adjustment. Although the codes may require the exchange rate in the reverse direction behind the scene, this behavior is not user friendly. The error (specially the Oracle error) is not descriptive while the requirement of having exchange rates in both directions is not documented anywhere in user guides.

The error message needs to be converted into something that the user can understand and take appropriate action.

With Oracle, the error reads:
Internal Server Error. Contact your site administrator.
([CA Clarity][Oracle JDBC Driver][Oracle]ORA-21000: error number argument to raise_application_error of -21000 is out of range
ORA-06512: at "CLARITYDB.REV_PRC_ADJWIPMODXFERXACTION", line 441
ORA-06512: at "CLARITYDB.PAC_PRC_WIPADJMOD_SP", line 74
ORA-06512: at line1)

With Microsoft SQL Server, the error reads:
Internal Server Error. Contact your site administrator. ([CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'RATE_EXCHANGE_RATE', table 'CLARITYDB.PPA_TRANSWIPADJUST_VALUES'; column does not allow nulls. INSERT fails.)

Steps To Reproduce:

  1. Enable multi-currency
  2. Administration, Finance: Setup, Currency - activate at least two currencies, such as USD and LTL
  3. Administration, Finance: Setup, Foreign Exchange Rates - create exchange rate for LTL to USD, with NO exchange rate for USD to LTL
  4. Create a new project 'ProjectA' with USD as the currency
  5. Set Financial configuration and associate the project to a Rate Matrix that has matching rows using USD
  6. Financially enable a resource 'ResourceX' by populating all required fields on the Financial tab using USD
  7. Add the resource and assign to some tasks
  8. Create a new voucher Transaction Entry for this project, resource using USD for the currency
  9. Post the new transaction into WIP
  10. Go to Administration, Manage Matrix and update the matching matrix row - changing the currency from USD to LTL
  11. Go to 'Create WIP Adjustment' and find the posted transaction
  12. Click into the transaction to make an adjustment - the only change on this transaction is to change the currency from USD to LTL on both the Cost and Rate fields
  13. Click 'Save and Return' button

Expected : The WIP adjustment page should display a coherent message to indicate missing foreign exchange rate from USD to LTL
Actual : Error is displayed without helpful information on the problem, WIP adjustment fails

Solution:

To allow the WIP adjustment to complete successfully, add the exchange rate for the reverse direction. Using the example described above, create a foreign exchange rate definition From: USD To: LTL

Status/Resolution:
An issue CLRT-74458, has been logged with development asking for the error message to be converted into something that the user can understand and take appropriate action.
A documentation issue CLRT-74457, has been logged asking for an update to the section regarding Setting Up Financial Management, Currencies, Set Up Conversion Rates.

Keywords :CLARITYKB, CLRT-74458, CLRT-74457, CLRT-25256 , financial, revmgr, clarity13open