Clarity: System Error When Creating New Fiscal Time Period of Any Period Type

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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:

1. Upgrade from Clarity 8.1.0 to Clarity 12.0.6
2. Login as a Clarity Administrator
3. Admin Tool > Financial Management: Settings > Entities
4. Select an existing Entity > Fiscal Time Periods
5. Click 'New' button
6. Fill in all required fields
7. Click 'Save' or 'Submit' button

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:

1. Locate this file
   
   <clarity_install_dir>\install\config\i18n.xml
   
   
2. Make a backup of this file. You can save the file with a different name or copy and paste to a different folder
   
   
3. Delete the file
   
   
4. Restart Clarity app server(s)
   
   
   • Run "niku stop app" command
     
     
   • Run "niku start app" command

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.

Environment

Release: ESPCLA99000-12.1-Clarity-Extended Support Plus
Component: