Postgres Load Data Warehouse fails custom object in Time Entry User Value 1

Products

• Clarity PPM SaaS

Issue/Introduction

SUMMARY: Postgres Load Data Warehouse fails with custom lookup for Time Entry User Value 1 using a custom object 

 

 

 

STR:

 

1. Create an object

   - lw_cust

 

2. Add a custom field to the object

   - lw_worker

   

3.  Create 2 or 3 instances of the custom object

    Examples:

Instance 1:  Name = 5006247 , ID  =5006247  LW Worker = EW1234567

Instance 2:  Name = 5038041 , ID  =5038041  LW Worker = EW1672573

    

4. Create a lookup with ID using this:

   

    SELECT  @SELECT:TE.PRID:PRTIMEENTRYID@,

@SELECT:SUBSTR(WI.LW_WORKER,1,30):USERVALUE@,

@SELECT:SUBSTR(WI.LW_WORKER,1,30):DISPLAYVALUE@,

@SELECT:'en':LANGUAGE_CODE@,

@SELECT:1:LANGUAGE_ID@

FROM  PRTIMESHEET TS,PRTIMEENTRY TE, ODF_CA_LW_CUST WI

WHERE 1 = 1

AND TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@

AND TS.PRID = TE.PRTIMESHEETID

AND @FILTER@

 

5.  Replace the OOTB lookup on Timesheet Options with the one created in step 3

 

6.  Create some timesheets and fill in the values for User Value 1 on the time entry lines.

    - Configure the timesheet if the field is not already showing

7. Run Full Load of DWH

 

Expected Results:  The job completes

 

Actual Results:  Job fails on this lookup with error 

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_LW_UV2. duplicate key value violates unique constraint "dwh_lkp_lw_uv2_u1"

  Where: PL/pgSQL function inline_code_block line 20 at RAISE

Environment

Release : 16.1.1 16.1.2

Cause

Reported as DE79937

Resolution

Update the query to add the yellow highlighted parts below.  It will need row_number as a unique_Id and TS.PRID = COALESCE(@WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@, TS.PRID; which will be used to map the appropriate time entry.

The hidden key should be the row_number field so it will be unique

SELECT ROW_NUMBER() OVER(order by null) as row_number,
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(WI.LW_WORKER,1,30):USERVALUE@,
@SELECT:SUBSTR(WI.LW_WORKER,1,30):DISPLAYVALUE@,
@SELECT:'en':LANGUAGE_CODE@,
@SELECT:1:LANGUAGE_ID@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, ODF_CA_LW_CUST WI 
WHERE 1=1 and TS.PRID = COALESCE(@WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@, TS.PRID) 
AND TS.PRID = TE.PRTIMESHEETID AND @FILTER@

Additional Information

 The Load Data Warehouse may fail if you run it after updating or clicking Save for Time Entry User Value 1 without any updates.  This is part of another defect fix.  Please use the below delete statement before you run the LDWH job to avoid this error.

delete from dwh_meta_columns where DWH_TABLE_NAME in ('dwh_tme_entry', 'dwh_tme_entry_ln') and ATTRIBUTE_CODE like 'user_value1%' and is_deleted =1;