We included a new User Value 1 lookup on the Timesheet Options page, and saved. Ever since then the Load Data Warehouse job fails with error:
Error message (lookup name may vary):
An error occurred executing this job entry :
Couldn't execute SQL: DELETE FROM DWH_LKP_TIME_ENTRY_USER__00
WHERE NOT (EXISTS (SELECT 1
FROM DWH_LKP_TIME_ENTRY_USER__00_V SRC
WHERE SRC.PRTIMEENTRYID = DWH_LKP_TIME_ENTRY_USER__00.TIME_ENTRY_USER__00_KEY
AND SRC.LANGUAGE_CODE = DWH_LKP_TIME_ENTRY_USER__00.LANGUAGE_CODE))
ERROR: column src.language_code does not exist
Position: 227
The lookup selected was not created by Load Data Warehouse best practices and does not contain language code (it looks for it as per the error).
Note: the lookup selected also MUST be having PRTIMEENTRY.PRID as hidden key.
Release : 15.8.1, 15.9
Component : CA PPM SAAS DATA WAREHOUSE
Logged as DE57004, Not a Bug
Get the job to run successfully:
For the time being we recommend removing the User Value lookup from Timesheet Options (set back to the default lookup) and running a Full Load to get the job to run. Once the job is stabilized, please work with your developer team to correct the Timeentry User Value 1 lookup query.
Fix the custom lookup query if you still want to use it, before enabling it again:
If at some point you want still to use a custom lookup, please modify the custom dynamic query for Time Entry User Value 1 to include LANGUAGE_ID as in the tips below:
Use a lookup query to similar to this (must include LANGUAGE_CODE and LANGUAGE_ID)
SELECT @SELECT:A.PRTIMEENTRYID:[email protected],
@SELECT:A.USER_VALUE:[email protected],
@SELECT:A.DISPLAY_VALUE:[email protected],
@SELECT:A.LANGUAGE_CODE:[email protected],
@SELECT:A.LANGUAGE_ID:[email protected],
@SELECT:A.LAST_UPDATED_DATE:[email protected]
FROM
(SELECT TE.PRID PRTIMEENTRYID ,
SUBSTR(OT.CODE,1,30) USER_VALUE ,
SUBSTR(OT.CODE,1,30) DISPLAY_VALUE ,
LANG.LANGUAGE_CODE LANGUAGE_CODE ,
LANG.ID LANGUAGE_ID ,
TE.PRMODTIME LAST_UPDATED_DATE
FROM PRTIMESHEET TS
INNER JOIN PRTIMEENTRY TE ON TS.PRID = TE.PRTIMESHEETID
INNER JOIN CMN_LANGUAGES LANG ON LANG.IS_DW_ENABLED = 1
JOIN ODF_CA_TESTING_TIME OT ON OT.CODE = TE.USER_LOV1
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:[email protected]
@BROWSE-ONLY:
UNION
SELECT TE.PRID PRTIMEENTRYID ,
SUBSTR(OT.CODE,1,30) USER_VALUE ,
SUBSTR(OT.CODE,1,30) DISPLAY_VALUE ,
'en' LANGUAGE_CODE ,
1 LANGUAGE_ID ,
TE.PRMODTIME LAST_UPDATED_DATE
FROM PRTIMESHEET TS
INNER JOIN PRTIMEENTRY TE ON TS.PRID = TE.PRTIMESHEETID
LEFT JOIN ODF_CA_TESTING_TIME OT ON 1=1
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
AND TE.USER_LOV1 IS NULL
:[email protected]
) A
WHERE @[email protected]
Here are some of the best practices:
We also recommend all changes like this to be done on a lower environment first, ensure the job is running well, and then you can repeat the same in Production.