During the investigation of the NullPointerException occurring in the getAllPendingTasks API, we identified that the issue was caused by inconsistent data in the database.

Root Cause :

Some records in FN_WO_TASK were linked to work orders whose subscriber_id referenced subscribers that do not exist in the FN_SUBSCRIBER table.

This situation likely occurred due to a manual deletion of subscriber records directly from the database without cleaning or updating the related records in FN_WORK_ORDER and FN_WO_TASK, resulting in orphaned references.

Resolution Applied

We identified and removed the affected tasks whose work orders referenced non-existent subscribers.

Verification Query :
SELECT t.id AS task_id, t.task_name, w.id AS wo_id, w.subscriber_id
FROM FN_WO_TASK t
JOIN FN_WORK_ORDER w ON t.wo_id = w.id
LEFT JOIN SUBSCRIBER s ON w.subscriber_id = s.id
WHERE w.subscriber_id IS NOT NULL
AND s.id IS NULL;

Cleanup Query Executed

DELETE FROM FN_WO_TASK
WHERE wo_id IN (
SELECT w.id
FROM FN_WORK_ORDER w
LEFT JOIN SUBSCRIBER s ON w.subscriber_id = s.id
WHERE w.subscriber_id IS NOT NULL
AND s.id IS NULL
);

Optionally, orphaned work orders can also be removed:

DELETE FROM FN_WORK_ORDER
WHERE subscriber_id IS NOT NULL
AND subscriber_id NOT IN (SELECT id FROM SUBSCRIBER);