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

Document ID : KB000111482
Last Modified Date : 17/08/2018
Show Technical Document Details
Issue:
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.

CA PPM Rate Matrix Assign Columns
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.