Operation Details
Description: Returns the name, the full name and the task status for each task reserved by the members of an organization unit and are due within a specified date time, grouped by the members of that organization unit.
Sterotype: nsmulti
SQL 
SELECT
  count(w.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
  
  TaskAssignment ta 
  ON ta.relatedName = u.userName 
  AND ta.assigneeType = 'RL9' 
  
  LEFT OUTER JOIN
  
  Task t 
  ON ta.taskID = t.taskID 
  AND t.reservedBy = u.userName 
  AND t.status = :taskOpenStatus 
  
  LEFT OUTER JOIN
  
  WorkflowDeadline w 
  ON t.taskID = w.taskID 
  AND w.deadlineTime >= :fromDeadlineDateTime 
  AND w.deadlineTime < :toDeadlineDateTime 
GROUP
  BY u.userName,
  u.fullName 

UNION
  
SELECT
  count(w.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
  
  TaskAssignment ta 
  ON ta.relatedName = u.userName 
  AND ta.assigneeType = 'RL9' 
  
  LEFT OUTER JOIN
  
  Task t 
  ON ta.taskID = t.taskID 
  AND t.reservedBy = u.userName 
  AND t.status = :taskDeferredStatus 
  
  LEFT OUTER JOIN
  
  WorkflowDeadline w 
  ON t.taskID = w.taskID 
  AND w.deadlineTime >= :fromDeadlineDateTime 
  AND w.deadlineTime < :toDeadlineDateTime 
GROUP
  BY u.userName,
  u.fullName 
ORDER BY userName