SELECT
  DISTINCT wq.workQueueID,
  wq.name,
  COUNT(taskID)
INTO
  :workQueueID,
  :workQueueName,
  :taskCount 
FROM
  WorkQueue wq 
  
  LEFT 
  
  JOIN
  
  TaskAssignment ta 
  ON wq.workQueueID = ta.relatedID 
  AND ta.assigneeType = :workQueue,
  WorkQueueSubscription wqs,
  Position p 
WHERE wqs.subscriberType = :subscriberType 
  AND wq.workQueueID = wqs.workQueueID 
  AND p.positionID = wqs.subscriberID 
  AND wqs.subscriberID IN 
  ( 
  SELECT
    DISTINCT p1.positionID 
  FROM
    position p1,
    OrgUnitPositionLink oup,
    PositionHolderLink phl 
  WHERE oup.organisationUnitID IN 
    ( 
    SELECT
      oup1.organisationUnitID 
    FROM
      OrgUnitPositionLink oup1 
    WHERE oup1.recordStatus <> :recordStatus 
      AND oup1.positionID IN 
      ( 
      SELECT
        DISTINCT p2.positionID 
      FROM
        Users u,
        PositionHolderLink ph,
        Position p2 
      WHERE u.userName = :userName 
        AND u.userName = ph.userName 
        AND ph.positionID = p2.positionID 
        AND p2.leadPositionInd ='1' 
        AND ph.recordStatus <> :recordStatus 
        AND 
        (
          
          (
            ph.fromDate <= :currentDate 
            AND ph.toDate IS NULL 
          ) 
          OR 
          (
            ph.fromDate <= :currentDate 
            AND ph.toDate >= :currentDate 
          ) 
        ) 
      ) 
    ) 
    AND p1.positionID = oup.positionID 
  ) 
GROUP
  BY wq.workQueueID,
  wq.name,
  p.name,
  wqs.subscriberType 
ORDER BY wq.name