DWH job fails when using a Custom dynamic lookup for User Value 1 under Timesheet Options

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

1. Create a Dynamic Query based test lookup with the below query.

SELECT
@SELECT:TE.PRID:[email protected],
@SELECT:SUBSTR(TSK.PRNAME,1,30):[email protected],
@SELECT:SUBSTR(TSK.PRNAME,1,30):[email protected],
@SELECT:LANG.LANGUAGE_CODE:[email protected],
@SELECT:LANG.ID:[email protected]
FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK, CMN_LANGUAGES LANG
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND LANG.LANGUAGE_CODE = 'en'
AND @[email protected]

Query Reference

2. Navigate to Administration -> Project Management -> Timesheet Options
3. Modify the Value of "User Value 1 Lookup" from the default "Time Entry User Lookup Field 1" to the newly created "Test Lookup".
4. Run Load DatawareHouse Full Load.

Expected Results: Job Completes without issues.

Actual Results: Load DWH job starts failing with error [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables While merging DWH_META_COLUMNS

Cause

This is an expected bug DE46395

Environment

CA PPM 15.4.1, 15.5, 15.5.1

Resolution

However, in order to resolve the issue, please follow the below steps to perform a cleanup of meta data.


1. Login to CA PPM

2. Navigate to Administration -> Project Management -> Timesheet Options.

3. Change "User Value 1 Lookup" from "Custom Defined lookup" to "Time Entry User Lookup field 1".

4. Run the following query on PPM DB to identify DB rows which are left over.

select id,object_code,attribute_code,src_table_name,extended_type,lookup_type,is_deleted from dwh_meta_columns where src_table_name='dwh_timeentry_v' and lookup_type !=  'PRTIMEENTRY_USER_LOV1' and lookup_type !='PRTIMEENTRY_USER_LOV2' and is_deleted=1
 

5. We should see two rows returned where the lookup_type column represents the ID of your custom defined lookup. 

6. Delete these two rows from DWH_META_COLUMNS table.
7. Connect to your PPM_DWH Schema and truncate the table DWH_META_COLUMNS.

7. Run Load Datawarehouse Job Full Load.

8. Stop application services and apply patch.

9. After Patch installation is complete, Navigate to Administration -> Project Management -> Timesheet Options and Change "User Value 1 Lookup" from "Time Entry User Lookup field 1" to "Custom Defined Lookup".

10. Run Load Datawarehouse Job Full Load.

Additional Information

This Defect DE46395 is targeted to be fixed in the upcoming Patch for 15.5.1 version.