SELECT
  t.taskID,
  t.priority,
  t.reservedBy,
  u.fullName,
  t.assignedDateTime,
  w.deadlineTime,
  t.wdoSnapshot,
  t.overflowInd,
  t.status,
  t.restartTime,
  t.versionNo
INTO
  :taskID,
  :taskPriority,
  :taskReservedByUserName,
  :taskReservedByFullUserName,
  :taskAssignedDateTime,
  :taskDeadlineDateTime,
  :wdoSnapshot,
  :overflowInd,
  :status,
  :restartTime,
  :versionNo 
FROM
  Task t 
  
  LEFT OUTER JOIN
  
  Users u 
  ON (t.reservedBy = u.userName) 
  
  LEFT OUTER JOIN
  
  WorkflowDeadline w 
  ON (t.taskID = w.taskID) 
WHERE t.taskID IN 
  ( 
  SELECT
    taskID 
  FROM
    TaskAssignment ta 
  WHERE ta.assigneeType = :assigneeType 
    AND ta.relatedID IN 
    ( 
    SELECT
      DISTINCT j.jobID 
    FROM
      Users u,
      PositionHolderLink ph,
      Position p,
      Job j 
    WHERE u.userName = :relatedName 
      AND u.userName = ph.userName 
      AND ph.positionID = p.positionID 
      AND j.jobID =p.jobID 
      AND ph.recordStatus = :recordStatus 
      AND :currentDate >= ph.fromDate 
      AND 
      (
        :currentDate <= ph.toDate 
        OR ph.toDate IS NULL 
      ) 
    ) 
  ) 
  AND 
  (
    
    (
      :allUserTasksInd = '0' 
      OR 
      (
        t.reservedBy IS NULL 
        OR t.reservedBy IS NOT NULL 
      ) 
    ) 
    AND 
    (
      :allReservedUserTasksInd = '0' 
      OR t.reservedBy IS NOT NULL 
    ) 
    AND 
    (
      :allAssignedUserTasksInd = '0' 
      OR t.reservedBy IS NULL 
    ) 
    AND 
    (
      :searchByPriorityInd ='0' 
      OR t.priority = :priority 
    ) 
    AND 
    (
      :searchByStatusInd ='0' 
      OR t.status = :status 
    ) 
    AND 
    (
      :searchByDueDateInd ='0' 
      OR 
      (
        w.deadlineTime >= :fromDeadlineDateTime 
        AND w.deadlineTime <= :toDeadlineDateTime 
      ) 
    ) 
  ) 
ORDER BY deadlineTime ASC