OOTB "Project Status Report List" report fails with SQL Exception

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Project Status Report List Group By Stage Error. When custom lookup values are added to INV_TYPE, the report will error if the report is grouped by stage and there are projects with stages as well as projects with no stage.

Error Msg = ORA-25137: Data value out of range at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) 

Steps to Reproduce:  

1. Use lookup_id = INV_TYPE
2. Go to Values
3. Use Project Investment Lookup level
4. Create a new Lookup Value called Ideation (same level as IT, Six Sigma...)
5. Create 3 values under this new Ideation level
   • Project Stage 1 Ideation  ID: PROJECT_LIFECYCLE_STAGE_1
   • Project Stage 2. Plan     ID: PROJECT_LIFECYCLE_STAGE_2
   • Project Stage 3. Design   ID: PROJECT_LIFECYCLE_STAGE_3
6. Make sure they are all active
7. Assign some projects to these new stages
8. Make sure some projects have the OOTB IT stages
   Note these projects so you can include them on the report.
9. Make sure all these projects have a final status report.
10. Run Load Data Warehouse (Full Load)
11. Go to Advanced Reporting for Project Status Report List
12. For the parameters select the projects you identified earlier.  (everyone has a stage)
13. Choose Select All for the other parameters where applicable
14. Choose to Group by Stage
    The report should return results grouped by stage.
15. Now go back and add a project that has no Stage but has a status report
16. Run the report again

Expected Results: The report still works. 

Actual Results: Error Appears with Error Msg = ORA-25137: Data value out of range at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ... 37 more. 

Cause

DE62674 

• Report is failing with ORA-25137: Data value out of range when we run the report grouping by stage and there is a project that does not have any stage associated to it.
• The report query handles nulls with NVL(value,999999) to make sure these projects without any stage display as undefined stage and this is the last group on the report output. 
• The problem is that a CAST is required and the CAST in Oracle has this limitation. We must pass the size. 

Environment

Release : 15.9.2, 15.9.3

Component : CLARITY JASPERSOFT, Oracle Database 

Resolution

Fixed in Release 16.0.0