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

Document ID : KB000126406
Last Modified Date : 11/02/2019
Show Technical Document Details
Issue:
1. Create a Dynamic Query based test lookup with the below query.

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@,
@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:LANG.ID:LANGUAGE_ID@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK, CMN_LANGUAGES LANG
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND LANG.LANGUAGE_CODE = 'en'
AND @FILTER@

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
Environment:
CA PPM 15.4.1, 15.5, 15.5.1
Cause:
This is an expected bug DE46395
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.