Portfolio Sync failed - Can't use Portfolios

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

Symptoms:

Trying to synchronize any portfolio. Get Alert: Synchronize Portfolio: unknown error.
 
Steps to Reproduce:
1. Login to the application as a Portfolio Manager
2. Home, Portfolios, open an existing portfolio or create a new portfolio with investments
3. Go to Content Editor tab, click 'Sync Now' button
 
Expected Result: To sync investments without an error message
Actual Result: Alert: Synchronize Portfolio: unknown error.

Environment:

Applies to all supported PAS environments for specified releases.

Cause:

Caused by CLRT-79097

The System Administrator created an attribute with a data type of 'Large String' and deleted it from Studio Investment Object.  Then later, created a new attribute with a data type of 'String' using the exact same attribute ID value for the new attribute.  The sync job is trying to remember the old attribute and convert it to the new data type for the same attribute ID.

Steps to Reproduce

  1. Create a new attribute on the Investment Object 
    • Attribute ID = strategy_tie
    • Attribute Data Type = Large String
  2. Put the field on a property page on the Idea Object
  3. Create a Portfolio with ideas and Sync
    • Sync is successful
  4. Go back to Investment Object, Attributes and DELETE the attribute
    • This removes the attribute from the table and from the views
  5. Create a new attribute on the Investment Object
    • Attribute ID = strategy_tie (important to make it the same ID)
    • Attribute Data Type = String
  6. Put the field on a property page on the Idea Object
  7. Go back to the existing Portfolio, Contents Editor page and click 'Sync Now' button
    • Alert: Synchronize Portfolio: unknown error
  8. Create a new Portfolio, to see if this works
    • Alert: Synchronize Portfolio: unknown error

BG-CA.LOG:

ERROR 2015-10-19 18:11:10,504 [Dispatch Synchronize portfolio investments : bg@ppmserver (tenant=clarity)] job.PFMBatchPersistUtil (clarity:admin:5152086__87CEBDE6-71B8-41E1-914F-61199EBEB2FA:Synchronize portfolio investments) One of the following statements caused a fatal exception: [
ALTER TABLE TEMP_PFM_CA_INV MODIFY OBJ_WORK_STATUS VARCHAR ( 480 ), 
ALTER TABLE TEMP_PFM_CA_INV MODIFY OBJ_PFL_CATEGORY3 VARCHAR ( 480 ), 
ALTER TABLE TEMP_PFM_CA_INV MODIFY OBJ_PFL_CATEGORY4 VARCHAR ( 480 ), 
ALTER TABLE TEMP_PFM_CA_INV MODIFY OBJ_PFL_CATEGORY2 VARCHAR ( 480 ), 
ALTER TABLE TEMP_PFM_CA_INV MODIFY OBJ_PFL_CATEGORY1 VARCHAR ( 480 ), 
ALTER TABLE TEMP_PFM_CA_INV MODIFY STRATEGY_TIE VARCHAR ( 4000 )]
….
 
Analyzing the BG Log file you see a few attributes listed in the error message, but the cause can be traced to this specific attribute based on the following error ORA-22859
ERROR 2015-10-19 18:11:10,505 [Dispatch Synchronize portfolio investments : bg@ppmserver (tenant=clarity)] job.PFMSyncManager (clarity:admin:5152086__87CEBDE6-71B8-41E1-914F-61199EBEB2FA:Synchronize portfolio investments) 
com.niku.dbtools.DBUpdateException: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-22859: invalid modification of columns

Resolution:

If you are going to use the same attribute ID, the System Administrator needs to trick the application into thinking there is no change to the attribute data type, by deleting the attribute once again and then recreating it with the same data type created the second time (String).

Steps to Resolve:

  1. Go back to Investment Object, Attributes and DELETE the attribute (again!)
    • This removes the attribute from the table and from the views
  2. Run the Sync job or Sync Now on a Portfolio - it should be successful without this attribute
  3. Create a new attribute on the Investment Object
    • Attribute ID = strategy_tie (important to make it the same ID)
    • Attribute Data Type = String (this is the same data type created 2nd time)
  4. Run the Sync job or Sync Now on a Portfolio - it will be successful

Additional Information:

ORA-22859: invalid modification of columns : 
Cause: An attempt was made to modify an object, REF, VARRAY, nested table, or LOB column type.
Action: Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.