SELECT
  t.taskID,
  t.priority,
  t.reservedBy,
  CASE 
    WHEN e.fullName is null 
    THEN u.fullName 
    ELSE e.fullName 
  END
  ,
  t.assignedDateTime,
  t.wdoSnapshot,
  t.overflowInd,
  t.versionNo,
  w.deadlineTime
INTO
  :taskID,
  :taskPriority,
  :taskReservedByUserName,
  :taskReservedByFullUserName,
  :taskAssignedDateTime,
  :wdoSnapshot,
  :overflowInd,
  :versionNo,
  :taskDeadlineDateTime 
FROM
  Task t 
  
  LEFT OUTER JOIN
  
  Users u 
  ON (t.reservedBy = u.userName) 
  
  LEFT OUTER JOIN
  
  ExternalUser e 
  ON (t.reservedBy = e.userName) 
  
  LEFT OUTER JOIN
  
  WorkflowDeadline w 
  ON (t.taskID = w.taskID 
  AND w.deadlineTime >= :fromDeadlineDateTime 
  AND w.deadlineTime < :toDeadlineDateTime) 
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,
        OrgUnitPositionLink oup ,
        Job j 
      WHERE u.userName = :relatedName 
        AND u.userName = ph.userName 
        AND ph.positionID = p.positionID 
        AND p.positionID = oup.positionID 
        AND j.jobID =p.jobID 
        AND ph.recordStatus = :recordStatus 
        AND :currentDate >= ph.fromDate 
        AND 
        (
          :currentDate <= ph.toDate 
          OR ph.toDate IS NULL 
        ) 
      ) 
    ) 
  ) 
  AND 
  (
    
    (
      :allTasksInd = '0' 
      OR 
      (
        t.reservedBy IS NULL 
        OR t.reservedBy IS NOT NULL 
      ) 
    ) 
    AND 
    (
      :allReservedTasksInd = '0' 
      OR t.reservedBy IS NOT NULL 
    ) 
    AND 
    (
      :allAssignedTasksInd = '0' 
      OR t.reservedBy IS NULL 
    ) 
  ) 
ORDER BY assignedDateTime ASC