API-1019 : Could not process the request when adding task to a timesheet and ORA-01427 in logs
search cancel

API-1019 : Could not process the request when adding task to a timesheet and ORA-01427 in logs

book

Article ID: 107624

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

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:%SESSION%: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:%SESSION%: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.x, 16.0

Cause

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.
The cause of the sub-hierarchy corruption is unknown.
The resolution below helps to identify the corruption and regenerate the sequence of the WBS in order to resolve the error seen on the UI

Resolution

Please run the following query to identify if you are hitting this issue:

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

OR (for more details)

 

SELECT

inv.id

as INVID,

inv.code

as INVCODE,

inv.name

as INVNAME,

tsk.prid

as TSKPRID,

tsk.prexternalid

as TSKEXTID,

tsk.prname

as TSKNAME,

tsk.prwbssequence

as TSKWBSSEQ,

tsk.prwbslevel

as TSKWBSLVL,

tsk.wbs_parseq

as TSKWBSPAR

FROM PRTASK tsk

inner

join INV_INVESTMENTS inv

on tsk.PRPROJECTID = inv.ID

WHERE tsk.prwbslevel > 1

AND tsk.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 (This will recreate the sequences):

Workaround 1 (Move task back and forward to recreate the sequence)

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.

Workaround 2: (run PRJ_WBS_UPDATE_SP to regenerate the sequence) - does not work for all types of corruption:

Get the internal ID for the project (i.e. 5000000 - can be found in the URL when opening the project)  and run the following command on your database editor:

begin
PRJ_WBS_UPDATE_SP(<projectId>);
end;

commit;

This will regenerate correctly the hierarchy for the entire project indicated