Description:
We are getting the system error when trying to create any fiscal time period. The error is not specific to any period type, it occurs on Monthly, Annually, and all other period types
ERROR 500: Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems. Contact your site administrator.
This is the first time we try creating new fiscal time periods after an upgrade from 8.1.0 to 12.0.6
Steps to Reproduce:
Expected Result: The records is created successfully without error
Actual Result: An Error 500 is generated
Solution:
A sql trace obtained from the replication of this error is as follow:
ERROR 2011-06-29 14:39:27,079 [http-server-name] service.ODFService (testuser:28753020__74eea9d5:revmgr.insertTimePeriod) processRequest Exception com.niku.union.odf.exception.ODFException: com.niku.union.persistence.PersistenceException: SQL error code: 1 Error message: ORA-00001: unique constraint (NIKU.CMN_CAPTIONS_NLS_U1) violated Executed: INSERT INTO CMN_CAPTIONS_NLS (ID, TABLE_NAME, PK_ID, LANGUAGE_CODE, NAME, DESCRIPTION, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY) SELECT ?,?,?,?,?,?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT 'CAPTION ALREADY EXISTS' FROM CMN_CAPTIONS_NLS WHERE TABLE_NAME = ? AND PK_ID = ? AND LANGUAGE_CODE = ?) ... Using input: {description=2016-jan-01-2016-dec-31, language_code=sv, table_name=BIZ_COM_PERIODS, pk_id=5001580, name=2016-jan-01-2016-dec-31} {description=2016/01/01-2016/12/31, language_code=ja, table_name=BIZ_COM_PERIODS, pk_id=5001580, name=2016/01/01-2016/12/31} ... {description=2016/01/01-2016/12/31, language_code=ja, table_name=BIZ_COM_PERIODS, pk_id=5001580, name=2016/01/01-2016/12/31} ... at java.lang.Thread.run(Thread.java:595) Caused by: com.niku.union.persistence.PersistenceException: SQL error code: 1
Root Cause:
The input string for language_code=ja appears twice, this violates the unique constraint on CMN_CAPTIONS_NLS table.
On 8.1.1 a reported issue with language locales was resolved, the locale for Japanese was corrected from "ja_JA" to "ja_JP". When Clarity is upgraded from a pre-8.1.1 version to a later version, the upgrade brings over the old locale value "ja_JA". As the old and new locale values are both associated to the language_code "ja", they cause the duplicate input strings to be generated for insertion into table CMN_CAPTIONS_NLS.
Resolution:
To resolve the error you will need to remove the old locale value of "ja_JA" using the following steps:
Once the Application services are restarted, the above file will be recreated in the same folder without an entry for "ja_JA". You should now be able to create new fiscal time period without getting the system error.
This is a one-time fix that can be accomplished after the upgrade.
Keywords: CLARITYKB, Error 500, entity, post-upgrade, upgrade, financial.