Error with recompiling invalid objects when upgrading

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Receiving compilation errors when trying to recompile invalid objects during an attempt to upgrade Clarity:
Errors for FUNCTION COP_SUM_WBS_MATRIX_FCT:

248/88 PL/SQL: ORA-00904: "W"."W"."TRANSNO": invalid identifier
276/45 PL/SQL: SQL Statement ignored

Note: This error can also occur after migrating from Oracle release 11 to release 12 without going via an upgrade, or when updating the compatibility in Oracle

Resolution

• The problem seems to be using a “double alias” format : “W.W.TRANSNO”.
• It should be “W.TRANSNO”. Oracle 11g version seems to accept the invalid syntax, while 12c version rejects it.
• Edit the function using “W.TRANSNO” instead and recompile
• Please consult with your DBA to get this accomplished