Rate Matrix Extraction Job fails with ORA-00904: "VALUE": invalid identifier

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Scheduled Rate Matrix Extraction job fails with given error while performing full extract:

Error executing job: 5205201 error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in CMN_JOB_RATE_MATRIX_SP - ORA-20000: Error in NBI_PROJ_RATES_AND_COSTS_SP - Calling NBI_PREPARE_MATRIX_DATA_SP: ORA-20000: Error in NBI_PREPARE_PROJ_DATA_SP - : ORA-00904: "VALUE": invalid identifier
ORA-06512: at "CMN_JOB_RATE_MATRIX_SP", line 15
ORA-06512: at line 1

Environment

CA PPM SaaS and CA PPM On Premise

Cause

When the Rate Matrix is first created with the order of Assign Columns if that's switched or changed potentially creates this scenario.

Resolution

Revert back 'Assigned Columns' in the order they were defined while creating Rate Matrix using the UP / DOWN arrows for the selected columns and reran the Rate Matrix Extraction job with Full Load.

<Please see attached file for image>

Additional Information

NOTE: Never REMOVE the columns from the selected list under 'Assign Columns' tab as it will wipe out the placeholder column values from the PPA_MATRIXVALUES table.