API-1019 when adding a task to the timesheet in Modern UX due to Corrupted WBS sequence
search cancel

API-1019 when adding a task to the timesheet in Modern UX due to Corrupted WBS sequence

book

Article ID: 187605

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

  • Corrupted WBS order sequence leads to API-1019 Internal Error message when adding the task to the timesheet in New / Modern UX (MUE)
  • When we are trying to add a task ( click "+WORK" button) on a timesheet, a message is thrown:

API-1019 Internal Error

In app-ca.logs we can see the error message:

ERROR 2020-03-25 10:26:41,641 [http-nio-14001-exec-17] ppm.rest (clarity:admin:33939535__113C5F30-2A44-4853-9A79-C0495798C16B:PPM_REST_API) 
com.niku.union.persistence.PersistenceException: 
SQL error code: 1427
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row

Environment

Release : All Supported Clarity releases

Component : CA PPM SAAS TIME MANAGEMENT

Cause

This is due to a corrupted WBS entry for a task.

  • One possible cause is suspected to be MSP although we do not have exact steps to reproduce this corruption.

Resolution

To retrieve all affected subtasks, run the following query:

select prprojectid, prid, prname, prexternalid, prwbssequence, prwbslevel, wbs_parseq, wbs_nnbseq from prtask where prid in (
 SELECT c.prid
                     FROM PRTASK P, PRTASK C
                    WHERE P.PRPROJECTID = C.PRPROJECTID
                      AND ( P.PRWBSSEQUENCE < C.PRWBSSEQUENCE AND P.WBS_NNBSEQ > C.PRWBSSEQUENCE )
                     AND P.PRWBSLEVEL < C.PRWBSLEVEL
                      AND P.WBS_PARSEQ = -1
                     group by c.prid
                      having count(*) > 1
                      ) order by prwbssequence

 

The issue will be with parent or grandparent for these. Example of a parent that's corrupted and the actual grandparent that shows as parent to itself:

The task with -1 and -1 will NOT display in UI

To fix the issue:

Workaround:

1. Backup the PRTASK table

2. Update the task PRWBSLEVEL to 1 with query:

update prtask set prwbslevel =1 where prid =<insert here the faulty task PRID>

commit

3. Check wbs_nnbseq to see if it's containing a larger number than it should (not conflicting with the next task). If it is, reset it as follows:

update prtask set wbs_nnbseq=<xx> where prid = <insert here the faulty task PRID>
commit

4. Then you may move the task around as appropriate for the WBS level. 

 

There is a second scenario where a sequence number may be used more than once.

For example:

In this example, we would want to reset the sequence number "prwbssequence" column for both rows.

Sample Update statements:

update prtask set prwbslevel =1 where prid = 6636133;

update prtask set prwbslevel =1 where prid = 6602552;

update prtask set WBS_NNBSEQ = 1 where prid = 6602552;

update prtask set WBS_NNBSEQ = 2 where prid = 6636133;

 

Note: If you need guidance, raise a case with Broadcom Support