Clarity PPM: API-1019 : Could not process the request due to internal error when adding task to a timesheet

Document ID : KB000107624
Last Modified Date : 01/02/2019
Show Technical Document Details
Issue:

In the New User Experience/Modern UX, the below error appears when adding work (task) to a timesheet:


API-1019 : Could not process the request due to internal error
 

The app-ca logs show the error:
ERROR 2018-07-23 16:05:23,986 [https-jsse-nio-8043-exec-56] ppm.rest (clarity:xxxxx:379001288__06A24A6B-73A8-4591-AC17-0A57751415F3:PPM_REST_API) FunctionResourceImpl :: Problem occurred while getting : timesheetAssignableTasks
ERROR 2018-07-23 16:05:23,987 [https-jsse-nio-8043-exec-56] ppm.rest (clarity:xxxxx:379001288__06A24A6B-73A8-4591-AC17-0A57751415F3: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

When running the following sample query directly against the database, the same error occurs in the SQL query tool:

SELECT I.ID, I.NAME, I.CODE, I.IS_OPEN_FOR_TE, I.TRACK_MODE, I.TRACK_ASSGN_ONLY, T.PRISOPEN
            FROM PRTEAM T, INV_INVESTMENTS I LEFT OUTER JOIN INV_PROJECTS P ON I.ID = P.PRID
           WHERE T.PRRESOURCEID = 5012856
             AND T.PRPROJECTID = I.ID
             AND I.IS_ACTIVE = 1
             AND NVL(P.IS_TEMPLATE, 0) = 0
             AND NVL(P.IS_PROGRAM, 0) = 0  AND 1=1
        ), TSTASKS AS (
          SELECT DISTINCT A1.PRTASKID
            FROM PRASSIGNMENT A1, PRTIMEENTRY TE1
           WHERE TE1.PRASSIGNMENTID = A1.PRID
             AND TE1.PRTIMESHEETID = xxxxxx
             and a1.prtaskid in (
yyyyyyy)
        )
      
        , TASKS AS (
          SELECT T.PRID TP, T.PRPROJECTID INV_ID, I.NAME, I.CODE, T.PRNAME
               , I.IS_OPEN_FOR_TE INV_OPEN, I.TRACK_MODE INV_TRACK_MODE, I.TRACK_ASSGN_ONLY, I.PRISOPEN TEAM_OPEN, T.PRSTART, T.PRFINISH
               , ( SELECT P.PRNAME
                     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
                      AND C.PRID = T.PRID ) AS PHASE
                , (SELECT MIN(P.IS_OPEN_TE) FROM PRTASK P, PRTASK C
                    WHERE P.PRPROJECTID = C.PRPROJECTID
                      AND P.PRWBSLEVEL <= C.PRWBSLEVEL
                      AND P.PRWBSSEQUENCE <= C.PRWBSSEQUENCE
                      AND P.WBS_NNBSEQ > C.PRWBSSEQUENCE
                      AND C.PRID = T.PRID ) AS TASK_OPEN
            FROM INVESTMENTS I, PRTASK T
           WHERE I.ID = T.PRPROJECTID
             AND T.PRISMILESTONE = 0
             AND T.PRISTASK = 1
             AND T.PRID NOT IN (SELECT PRTASKID FROM TSTASKS)
                  AND 1 = 1     AND 1=1
        )
        , ASSIGNMENTS AS (
          SELECT A.PRID AP, T.PRID TP, T.PRPROJECTID INV_ID
            FROM PRASSIGNMENT A, PRTASK T, INVESTMENTS I
           WHERE A.PRRESOURCEID = 5012856
             AND T.PRID = A.PRTASKID
             AND T.PRPROJECTID = I.ID
             AND T.PRID NOT IN (SELECT PRTASKID FROM TSTASKS)  ), RESULTS AS (
          SELECT T.PRNAME NAME, T.PHASE PHASE, T.NAME||' / '||T.CODE PROJECT_NAME, T.TP TASK_ID, T.PRSTART START_DATE, T.PRFINISH FINISH_DATE
               , ( CASE WHEN T.TASK_OPEN = 1 AND T.INV_OPEN = 1 AND T.INV_TRACK_MODE = 2 AND T.TEAM_OPEN = 1 THEN 1 ELSE 0 END) OPEN_FOR_ENTRY
               , ( CASE WHEN A.AP IS NULL THEN 0 ELSE 1 END ) ASSIGNED
               , T.TRACK_ASSGN_ONLY
            FROM TASKS T LEFT OUTER JOIN ASSIGNMENTS A ON T.TP = A.TP
           WHERE 1=1  )
        SELECT *
          FROM RESULTS
         WHERE 1 = 1  ORDER BY PROJECT_NAME , NAME , PHASE 

Environment:
15.4+
Cause:

This issue was reviewed as DE42744. This error is due to an issue retrieving the task dates. This issue is due to an issue with the sub-hierarchy within a task hierarchy, which results in the query fetching error.

Resolution:

Please contact the Support team to have the following query ran:

SELECT * FROM prtask
WHERE prwbslevel > 1 AND wbs_parseq = -1

If results are returned, the resolution is to go to the UI and update the task with the correct sequence with the following steps:
1. Open the task list of the project into the PPM Gantt view
2. Locate the problematic task, and select the checkbox beside it.
3. Click on "Move" (up-arrow icon in menu bar) and place the task somewhere else in the hierarchy.
4. Click on "Move" again for the same task, and now put it back to where it was originally.
5. Re-indent the task if needed to its correct prior position.