Project Grid displays ERR and does not load

Products

Clarity FedRAMP Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Specific projects do not load and the Project grid displays ERR when a project contains multiple schedule tentatives at the PRJ_TENTATIVE_SCHEDULES table.

STEPS TO REPRODUCE:

Login to Clarity MUX as User A
Open any project in the system
Go to Tasks module > Timeline view > Actions > Create Tentative Schedule
Sample project: http://myclarityserver/pm/#/project/5002001/tasks
Login to Clarity MUX as User B and open the same project(http://myclarityserver/pm/#/project/5002001/tasks)
Go to Tasks module > Timeline view > Notice the project is locked.
Actions > Create Tentative Schedule > You get an error: com.niku.union.utility.MessageKeyException: projmgr.COULD_NOT_ACQUIRE_PROJECT_LOCK
This is fine
At the DB side run Delete FROM prlock WHERE prrecordid = 5002001
Actions > Create Tentative Schedule > A tentative scheudle is created
Go to the project grid

Expected Results: Reload the project and it loads fine. Go to the project grid and this loads
Actual Results: Reload the project and it errors. Go to the project grid and this does not load and shows ERR

Environment

Any Clarity release

Cause

A project contains more than one entry on the PRJ_TENTATIVE_SCHEDULES table

Resolution

This query should help finding affected projects with more than 1 tentative schedule

SELECT
inv.code AS project_id,
inv.name AS project_name,
pts.project_id AS internal_project_id,
COUNT(pts.project_id) AS tentative_schedule_count
FROM
prj_tentative_schedules pts
JOIN
inv_investments inv ON pts.project_id = inv.id
GROUP BY
inv.code,
inv.name,
pts.project_id
HAVING
COUNT(pts.project_id) > 1

If it returns any project, open a case with Product Support for a potential solution.

Additional Information

app-ca.log file shows:

Using input:
{requiredDataProviderItems_prformat=prformat, ___odf_dummy=xyzzy, requiredDataProviderItems_locked_by_id=locked_by_id, requiredDataProviderItems_unique_code=unique_code, is_program=0, odf_pk=5002001, requiredDataProviderItems_entity_id=entity_id, requiredDataProviderItems_inv_tentative_id=inv_tentative_id, requiredDataProviderItems_manager_id=manager_id, requiredDataProviderItems_inv_type=inv_type, requiredDataProviderItems_locked_by=locked_by, @ODF_SELECT@=,tt.tentative_schedule_id as tentative_id,ts.auto_shift_successors as autoshift,tt.finish_date,tt.start_date, requiredDataProviderItems_odf_blp_active_id=odf_blp_active_id, requiredDataProviderItems_is_active=is_active, requiredDataProviderItems_budget_cst_total=budget_cst_total, requiredDataProviderItems_labor_eacsum=labor_eacsum, requiredDataProviderItems_odf_banner_color=odf_banner_color, requiredDataProviderItems_aggr_actual_cost=aggr_actual_cost, requiredDataProviderItems_odf_template=odf_template, requiredDataProviderItems_name=name, @TENTATIVE_SCHEDULE_JOIN@= LEFT OUTER JOIN PRJ_TENTATIVE_PROJECTS TT on TT.project_id = inv_investments.id and TT.is_publishable = 1 and tt.created_by = 1 and TT.tentative_schedule_id = 5002009 LEFT OUTER JOIN PRJ_TENTATIVE_SCHEDULES TS on TS.PROJECT_ID = TT.project_id and TS.id = 5002009, requiredDataProviderItems_schedule_finish=schedule_finish, requiredDataProviderItems_currency_code=currency_code, requiredDataProviderItems_actuals=actuals, requiredDataProviderItems_schedule_start=schedule_start}
Using dynamic SQL:
tag: @ODF_SELECT@ SQL:,tt.tentative_schedule_id as tentative_id,ts.auto_shift_successors as autoshift,tt.finish_date,tt.start_date
tag: @TENTATIVE_SCHEDULE_JOIN@ SQL: LEFT OUTER JOIN PRJ_TENTATIVE_PROJECTS TT on TT.project_id = inv_investments.id and TT.is_publishable = 1 and tt.created_by = 1 and TT.tentative_schedule_id = 5002009 LEFT OUTER JOIN PRJ_TENTATIVE_SCHEDULES TS on TS.PROJECT_ID = TT.project_id and TS.id = 5002009
tag: @ODF_ORDER_BY@ SQL: order by odf_pk
tag: @ODF_ORDER_BY_INNER@ SQL: order by odf_pk
tag: @ODF_ROWS_PARTITION_BY@ SQL:pmd_analytical_partition_by

.....
Caused by: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row