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

book

Article ID: 111482

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL 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

Cause

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

Environment

CA PPM SaaS and CA PPM On Premise

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>

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. 
 

Attachments

1558697432543000111482_sktwi1f5rjvs16ixs.jpeg get_app
1558618276698000111482_sktwi9tkjvsehwty.jpeg get_app