RPT Database views for FTE percentage is inflated because definition is missing 'period_type' criteria

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Problem:

RPT_RES_M_ALLOC_FTE_V. ALLOC_PERCENT value seems inflated.

If you analyze the database view definition, it is a union statement that in the first select statement correctly puts criteria for the period type "c.period_type = 'MONTHLY", but in the second select statement, the WHERE clause does not specify the period type.

If you add the following criteria at the end of the second select statement for the inner join on the 'rpt_calendar' table, the numbers are calculated as expected

INNER JOIN rpt_calendar c ON al_s.slice_date = c.start_date and c.PERIOD_TYPE ='MONTHLY'

This issue may be exhibited in other RPT database views as well. Please be sure to check all database views that provide similar output.

Steps to Reproduce:

1. Query the stock database view RPT_RES_M_ALLOC_FTE_V for a specific project, resource
2. Save the output in an Excel worksheet
3. Perform a query with the proposed modification for the same specific project, resource
4. Save the output in an Excel worksheet
5. Compare the ALLOC_PERCENT and HARD_ALLOC_PERCENT values

Expected Result: Correct output
Actual Result: Incorrect output

Cause:

Caused by CLRT-73294

Resolution:

Our sustaining team reviewed CLRT-73294 and closed it as they are not planning to make any changes to this area of the product.

Workaround:

Create or Update the Database view to include criteria for correct period type.

Add the following criteria at the end of the second select statement for the inner join on the 'rpt_calendar' table, the numbers are calculated as expected

INNER JOIN rpt_calendar c ON al_s.slice_date = c.start_date and c.PERIOD_TYPE ='MONTHLY'

This issue may be exhibited in other RPT database views as well. Please be sure to check all database views that provide similar output.

Additional Information:

For more details please review the Customer Reported Defect Resolution Policy 

 

Environment

Release: ESPCLA99000-13.2-Clarity-Extended Support Plus
Component: