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

Document ID : KB000107624
Last Modified Date : 23/07/2018
Show Technical Document Details
Issue:

In the modern UX, the error appears when adding work (task) to the timesheet

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

Environment:
15.4+
Cause:

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  ;

There is an issue retrieving the task dates. There is an issue with the sub-hierarchy within a task hierarchy, which resulted in the query fetching error.

 

Resolution:

The following query was ran to find out how widespread the data condition is:

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.

 

 

Additional Information:
Reported as DE42744