API-1019 : Could not process the request when adding task to a timesheet and ORA-01427 in logs
Article ID: 107624
Updated On:
Products
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
Feedback
