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

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.