Operation Details
Description: Returns the name, the full name and the task status for each task reserved by the members of an organization unit, grouped by the members of that organization unit.
Sterotype: nsmulti
SQL 
SELECT
  count(t.taskID) as count,
  u.userName,
  u.fullName,
  0
INTO
  :count,
  :taskReservedByUserName,
  :taskReservedByFullUserName,
  :taskDeferredIndicator 
FROM
  
  ( 
  SELECT
    DISTINCT Users.userName,
    Users.fullName 
  FROM
    Users,
    PositionHolderLink,
    Position,
    OrgUnitPositionLink 
  WHERE Users.userName = PositionHolderLink.userName 
    AND PositionHolderLink.positionID = Position.positionID 
    AND Position.recordStatus = :positionRecordStatus 
    AND Position.fromDate <= :currentDate 
    AND 
    (
      Position.toDate >= :currentDate 
      OR Position.toDate IS NULL 
    ) 
    AND PositionHolderLink.organisationStructureID = :organizationStructureID 
    AND OrgUnitPositionLink.positionID = Position.positionID 
    AND OrgUnitPositionLink.organisationUnitID = :organizationUnitID 
    AND OrgUnitPositionLink.recordStatus = :orgUnitPositionLinkRecordStatus 
    AND PositionHolderLink.recordStatus = :positionHolderLinkRecordStatus 
    AND PositionHolderLink.fromDate <= :currentDate 
    AND 
    (
      PositionHolderLink.toDate >= :currentDate 
      OR PositionHolderLink.toDate IS NULL 
    ) 
    AND Users.statusCode = :userRecordStatus 
  ) u 
   
  LEFT OUTER JOIN
  
  Task t 
  ON t.reservedBy = u.userName 
  AND t.status = :taskOpenStatus 
GROUP
  BY u.userName,
  u.fullName 

UNION
  
SELECT
  count(t.taskID) as count,
  u.userName,
  u.fullName,
  1 
FROM
  
  ( 
  SELECT
    DISTINCT Users.userName,
    Users.fullName 
  FROM
    Users,
    PositionHolderLink,
    Position,
    OrgUnitPositionLink 
  WHERE Users.userName = PositionHolderLink.userName 
    AND PositionHolderLink.positionID = Position.positionID 
    AND Position.recordStatus = :positionRecordStatus 
    AND Position.fromDate <= :currentDate 
    AND 
    (
      Position.toDate >= :currentDate 
      OR Position.toDate IS NULL 
    ) 
    AND PositionHolderLink.organisationStructureID = :organizationStructureID 
    AND OrgUnitPositionLink.positionID = Position.positionID 
    AND OrgUnitPositionLink.organisationUnitID = :organizationUnitID 
    AND OrgUnitPositionLink.recordStatus = :orgUnitPositionLinkRecordStatus 
    AND PositionHolderLink.recordStatus = :positionHolderLinkRecordStatus 
    AND PositionHolderLink.fromDate <= :currentDate 
    AND 
    (
      PositionHolderLink.toDate >= :currentDate 
      OR PositionHolderLink.toDate IS NULL 
    ) 
    AND Users.statusCode = :userRecordStatus 
  ) u 
  
   LEFT OUTER JOIN
  Task t 

  ON t.reservedBy = u.userName 
  AND t.status = :taskDeferredStatus 
GROUP
  BY u.userName,
  u.fullName 
ORDER BY userName