Load Data Warehouse fails with ORA-01438 on DWH_TME_SHEET

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Your Load Data Warehouse job is failing after upgrade to 16.4.0.1 with the below error.

ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_TME_SHEET). SQLERRM : ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 24

You submitted an adjustment for the bad value in the timesheet, however this did not work

Environment

Clarity 16.4.0+

Cause

A user has filled an extremely long value by mistake (2222222222222222300000000000000000000000000000000000000000000.00) for an OOO vacation task

Resolution

This column was added to the DWH_TME_SHEET table in 16.4.0:

• TIMESHEET_TOTAL (NUMBER(32, 0))

When the user entered that massive number into their timesheet, it saved successfully in PRTIMESHEET.PRACTSUM. This is because PRACTSUM is defined as a FLOAT.

In 16.4.0 the timesheet total now syncs down to the Data Warehouse into the new TIMESHEET_TOTAL column, and it's NUMBER(32, 0) so cannot exceed 32 digits.

Workaround:

1. Identify the affected timesheet internal PRID (engage Broadcom Support if needed)
   <We recommend testing this on a lower environment first>
2. Run the queries with using the exact same PRID you've identified
   • UPDATE prtimeentry
   • SET practsum = 0
   • WHERE prtimesheetid = <internal Timesheet ID>
     
     
   • UPDATE prtimesheet
   • SET practsum = 0
   • WHERE prid =<internal Timesheet ID>
   • commit
3. Run Load Data Warehouse to completion