Operation Details
Description: Returns the work queues and associated number of tasks that the specified user either is administrator of or the work queues that users that this supervisor is responsible for are subscribed to. @deprecated Since Curam 6.0. The TaskWorkQueueAssignment entity has been deleted. This method has been replaced by {@link curam.core.sl.entity.intf.TaskAssignment# searchAdministratorWorkQueueTasks(WorkQueueAssigneeTypeAndTaskCountKey)}. See release note CR00223130.
Sterotype: nsmulti
SQL 
SELECT
  DISTINCT wq.workQueueID,
  wq.name,
  COUNT(taskID)
INTO
  :workQueueID,
  :workQueueName,
  :taskCount 
FROM
  WorkQueue wq 
  
  LEFT 
  
  JOIN
  
  TaskWorkQueueAssignment twqa 
  ON wq.workQueueID = twqa.workQueueID 
WHERE 
  (
    
    (
      administratorUserName = :userName 
    ) 
    OR 
    (
      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 = :userName 
              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 = :userName 
                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 = :userName 
            AND phl2.recordStatus <> :status 
            AND prl.recordStatus <> :status 
          ) 
        ) 
      ) 
    ) 
  ) 
GROUP
  BY wq.workQueueID,
  wq.name 
ORDER BY wq.name