TSV Audit hardcodes schema name in audit triggers

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Audit on TSV fields would hardcode schema names which fails Time Slicing job, throws errors on updating Resources on admin side and may fail other actions after database refreshes to different schema names

STEPS TO REPRODUCE:

1. Connect to Clarity 16.2.2
2. Go to Team and Assignment and enable all TSV attributes for auditing on each item for I, U, D (or you can enable all attributes altogether)
3. Save
4. Now stop the services and take a database backup
5. Restore the same backup with a different schema name
6. Point the application to this new schema name
7. Run Time Slicing job

Expected Results: The Time Slicing job to complete successfully

Actual Results: The Time Slicing job fails with error on Audit triggers:

• Caused by: java.sql.SQLSyntaxErrorException: ORA-04098: trigger 'NEW_SCHEMA.T_ASSIGNMENT_PPRASSIGNMENT' is invalid and failed re-validation
• ORA-06512: at "NEW_SCHEMA.PRJ_BLB_SLICE_RESET_ALL_SP", line 11
• ORA-06512: at line 1
• Caused by: java.sql.SQLSyntaxErrorException: ORA-04098: trigger 'NEW_SCHEMA.T_TEAM_PPRTEAM' is invalid and failed re-validation
• ORA-06512: at "NEW_SCHEMA.PRJ_BLB_SLICE_RESET_ALL_SP", line 11
• ORA-06512: at line 1

Upon checking we can see the two triggers fail the validation on the database due to the fact the old schema is hardcoded:

• Error(630,339): PL/SQL: ORA-00904: "OLD_SCHEMA"."AUDIT_FCT_TEAM_POR_PRAVAILFINISH": invalid identifier
• Error(630,339): PL/SQL: ORA-00904: "OLD_SCHEMA"."AUDIT_FCT_TEAM_POR_PRAVAILFINISH": invalid identifier
• Error(489,339): PL/SQL: ORA-00904: "OLD_SCHEMA"."AUDIT_FCT_ASSIGNMENT_PRFINISH": invalid identifier

Example code snippet from trigger:

/* START BLOCK FOR COLUMN: prExtension*/
 TSV_OLD_VALUE := OLD_SCHEMA.NK_EXTRACT_CURVE_FCT( :OLD.prExtension , 'prActCurve' );
 TSV_NEW_VALUE := OLD_SCHEMA.NK_EXTRACT_CURVE_FCT( :NEW.prExtension , 'prActCurve' );

Environment

Clarity 16.2.0, 16.2.1, 16.2.2

Resolution

This is DE152846, fixed in 16.2.3 and 16.2.2.1

Workaround:

1. Open the affected trigger in SQL analyzer tool
2. Replace all references to the old schema with the new schema
3. Run Create or Replace & then compile the trigger