The Number - Hours TSV attributes stored as zero in the database tables when value is greater than 2,777,777

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

For a TSV attribute created using Number - Hours, the slice table is not able to handle values greater than 2,777,777. The DB record is stored with 0 in the slice table for a value greater than 2,777,777.

 

Steps To Reproduce:

1. Create a custom object ABC
2. Navigate to attributes tab and click New
3. Attribute Name: NumberHours
   Attribute ID: numberhours
   Data Type: Time-Varying
   Time-varying Data Units: Number
   Time-varying unit conversion: Hours
   Time-varying Date Constraints - Start On: Created Date
              Finish On: Last Updated Date
4. Navigate to Views tab in the object and configure to show this attribute in the Layout Edit view.
5. Navigate to Admministration - Time Slices and click NEW
6. Time Slice Name: Hours
   Rollover Interval: Monthly
   From Date: 1/1/2017
   Slice Period: Monthly
   Number of Periods: 12
   Item: XYZ - numberhours
7. Save and Return
8. Get back to the Time Slice request and from the page URL, make a note of the id=50XXXX. In this case, will use as 5012345
9. Navigate to Home, under Custom Objects - ABC List
10. Click New and give Name and ID and Save
11. In the Edit layout, the NumberHours attribute should be present
12. Start: 1/7/2017
    Finish: 31/7/2017
    Value: 2777777
13. Create a new row
    Start: 1/8/2017
    Finish: 31/8/2017
    Value: 2777778 and Save
14. Run the Time Slicing job
15. Connect to the DB
     SELECT TABLE_NAME FROM PRJ_BLB_SLICEREQUESTS WHERE ID = 5012345 (This is the Time slice request ID taken from the page url in Step 8)
     Result: ODF_SL_5057019 (The table name will not be the same)
16. Run the below query to see the TSV data in the slice table
    SELECT SLICE FROM ODF_SL_5057019

EXPECTED RESULT:
This attribute being a TSV in hours, the number entered will be multiplied by 3600 and show up in the SLICE column
2777777 * 3600 = 9999997200
2777778 * 3600 = 10000000800

ACTUAL RESULT:
The value entered in the first row in Step 12 gets multiplied by 3600 and shows up correctly as 9999997200 but the value entered in the second row in Step 13
 will show 0 in the slice column

Environment

Release: 451-101-15.1-Clarity-Creator User License
Component:

Cause

After Time Slicing job is executed, a WARN is populated in the BG-ca logs as below.

WARN 2017-09-07 01:06:30,480 [Dispatch Time Slicing : bg@servername (tenant=clarity)] niku.blobcrack (clarity:admin:5558262__85EC4881-C1BD-4C36-854B-BE049E300586:Time Slicing) Slice value is not in the valid range. Object ID = 5000000 Request ID = 5018015 Date = 2017-04-01T00:00:00 Value = 1.00000008E10. Value will be set to 0

This is a known limitation where a value greater than 10 digit will be stored as 0.

Resolution

The workaround is to split the values in the cells to use below 2,777,777 when using Number Hours TSV.