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 = :assigneeType 

WHERE 

      wq.workQueueID IN 
      ( 
      SELECT
        DISTINCT wqs.workQueueID 
      FROM
        WorkQueueSubscription wqs 
      WHERE wqs.userName IN 
        ( 
        SELECT
          DISTINCT Users.userName 
        FROM
          Position,
          PositionHolderLink,
          OrganisationUnit,
          OrgUnitPositionLink,
          Users 
        WHERE Position.positionID = PositionHolderLink.positionID 
          AND OrgUnitPositionLink.positionID = PositionHolderLink.positionID 
          AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
          AND PositionHolderLink.userName =Users.userName 
          AND PositionHolderLink.recordStatus <> :status 
          AND OrgUnitPositionLink.recordStatus <> :status 
          AND Position.recordStatus <> :status 
          AND Position.positionID IN 
          ( 
          SELECT
            OrgUnitPositionLink.positionID 
          FROM
            OrgUnitPositionLink 
          WHERE OrgUnitPositionLink.recordStatus <> :status 
            AND OrgUnitPositionLink.organisationUnitID IN 
            ( 
            SELECT
              opl.organisationUnitID 
            FROM
              OrgUnitPositionLink opl,
              PositionHolderLink phl,
              Position pos 
            WHERE pos.positionID = phl.positionID 
              AND phl.positionID = opl.positionID 
              AND pos.leadPositionInd = '1' 
              AND phl.userName = :relatedName 
              AND phl.recordStatus <> :status 
              AND opl.recordStatus <> :status 
            
            UNION
              
            SELECT
              OrgUnitParentLink.organisationUnitID 
            FROM
              OrgUnitParentLink 
            WHERE OrgUnitParentLink.recordStatus <> :status 
              AND OrgUnitParentLink.parentOrganisationUnitID IN 
              ( 
              SELECT
                opl1.organisationUnitID 
              FROM
                OrgUnitPositionLink opl1,
                PositionHolderLink phl1,
                Position pos1 
              WHERE pos1.positionID = phl1.positionID 
                AND pos1.leadPositionInd = '1' 
                AND phl1.positionID = opl1.positionID 
                AND phl1.userName = :relatedName 
                AND opl1.recordStatus <> :status 
                AND phl1.recordStatus <> :status 
              ) 
            ) 
          
          UNION
            
          SELECT
            prl.positionID 
          FROM
            PositionReportingLink prl,
            PositionHolderLink phl2,
            Position pos2 
          WHERE phl2.positionID = prl.reportsToID 
            AND pos2.leadPositionInd = '1' 
            AND phl2.userName = :relatedName 
            AND phl2.recordStatus <> :status 
            AND prl.recordStatus <> :status 
          ) 
        ) 
      ) 
GROUP
  BY wq.workQueueID,
  wq.name 
ORDER BY wq.name