Rate Matrix Extraction Job fails with ORA-00904: "VALUE": invalid identifier
Article ID: 111482
Clarity PPM SaaS
Clarity PPM On Premise
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
When the Rate Matrix is first created with the order of Assign Columns if that's switched or changed potentially creates this scenario.
CA PPM SaaS and CA PPM On Premise
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>
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.