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
Release : All Supported Clarity releases
Component : CA PPM SAAS TIME MANAGEMENT
This is due to a corrupted WBS entry for a task.
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