---
title: Task collection retrieval
summary: Builds and executes a dynamic, criteria-driven search of the TASK table and materializes typed task instances per source system.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.Tasks
reference_type: package
operation_id: OP-TASK-02
trigger: Caller populates a clsTaskCollection.Criteria object and retrieves through the BOF data portal
---

# Task collection retrieval

`clsTaskCollection` searches the `TASK` table with a caller-assembled `Criteria` object that supports source system, category, priority, date range, assignee, claim, policy, action code, and task-context filters. Because every filter is optional and additive, the same retrieval path serves everything from "all open tasks for a worker" to "every urgent third-party task assigned to a mail ID." The retrieved rows are handed to a source-code-specific factory so each row becomes an instance of the correct concrete task subclass.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-TASK-02 |
| **Trigger** | Caller builds a `clsTaskCollection.Criteria`, sets zero or more filters, and calls `clsTaskCollection.GetTasks(dataPortalInfo, criteria)` |
| **Inputs** | Optional filters: task source code(s)/system(s), category code(s), priority code or `TaskPriority` level, begin/end date range, mail ID, claim ID(s), policy number(s), action code(s), task-context values (code, key, category, text, date, numeric), `ShowCompleted` flag, `TaskText` substring, row-count limit, inner/outer sort |
| **Outputs** | `clsTaskCollection` populated with one typed task instance per matching, non-deleted (by default) `TASK` row |
| **Primary components** | `clsTaskCollection.BeforeRetrieve`, `clsTaskCollection.AfterRetrieve`, `clsTaskCollection.Criteria.FormatRequest`, `clsTaskCollection.NewTask`, `clsTaskSourceCodeCollection.TaskSource` |
| **Entry point** | `Business\SAIF.Business.Tasks\clsTaskCollection.vb` |

## Steps

1. **Caller configures the criteria.** The caller adds zero or more filters to a `Criteria` instance: `AddTaskSourceSystem`/`TaskSource` (by enum or code string), `AddTaskCategory`, `TaskPriorityCode` or `PriorityLevel`, a begin/end date window, `MailID`, `AddClaimID`, `AddPolicyNumber`, `AddTaskActionCode`, `TaskText`, `ShowCompleted`, a `RowNumLimit`, and inner/outer sort expressions. List-based filters (claim, policy, action, category, source) silently ignore non-positive or blank values.
2. **Assemble the base SELECT.** `BeforeRetrieve` sets the request's SQL statement to the criteria's fixed `RetrieveSQL` projection of all `TASK` columns ([SQL-008](../sql/SQL-008-task-filtered-collection-select.md), [TASK](../database-tables.md#task)), then calls `Criteria.FormatRequest` to append the dynamic `WHERE` clause and an optional row-limiting wrapper.
3. **Apply the base filter.** `FormatOtherParameters` always restricts to non-deleted rows (`DELETE_FLAG = 'N'`) unless `ShowCompleted` is set, in which case both deleted and active rows are returned ([BR-023](../business-rules.md#br-023-task-collection-retrieval-excludes-soft-deleted-tasks-by-default)).
   - If `TaskText` is non-blank, an unparameterized `TASK_TEXT LIKE '%<value>%'` fragment is appended directly to the SQL text rather than bound as a parameter.*
4. **Apply the source, category, and priority filters.** `FormatSourceCodeParameters` and `FormatCategoryParameters` bind a single `=` predicate for one value or an `IN (...)` list with indexed bind parameters (`:SOURCE1..N`, `:CAT1..N`) for multiple values. `FormatPriorityParameter` binds a single `:PriorityCode` when an explicit code is set; otherwise, if `PriorityLevel` is not `Informational`, it expands to an inline `IN (...)` list of literal priority codes cascading upward from the requested level — `Low` includes Low/Medium/High/Urgent, `Medium` includes Medium/High/Urgent, `High` includes High/Urgent, and `Urgent` matches only Urgent ([BR-024](../business-rules.md#br-024-priority-level-filter-cascades-to-include-higher-priority-codes)).
5. **Apply the date, assignee, claim, policy, and action filters.** `FormatDateParameters` binds `:BeginDate`/`:EndDate` against `TRUNC(TASK_DATE)` only when the caller narrowed the corresponding sentinel (`Date.MinValue`/`Date.MaxValue`). `FormatMailIDParameter` binds `:MailID` (uppercased) when set. `FormatClaimParameter`, `FormatPolicyParameter`, and `FormatActionCodeParameter` each follow the same single-value/`IN`-list pattern as source and category.
6. **Apply the task-context filters.** `FormatContextParameters` appends `CONTEXT_CODE`, `CONTEXT_KEY`, `CONTEXT_CATEGORY`, `CONTEXT_TEXT`, `CONTEXT_DATE`, and `CONTEXT_NUM` predicates directly as literal values in the SQL text (not bound parameters) whenever the corresponding criteria property is non-default.*
7. **Apply row limiting and sorting.** If `RowNumLimit > 0`, the statement is wrapped as `SELECT * FROM ( <query> ORDER BY <InnerSort> ) WHERE ROWNUM <= <limit>` followed by an optional `ORDER BY <OuterSort>`; if no limit is set, only the outer sort is appended.
8. **Execute and materialize rows.** `AfterRetrieve` reads each row from the cursor, extracts the trimmed `TaskSourceCode`, and calls the collection's abstract `NewTask()` factory (implemented per concrete collection subclass) to obtain an empty task of the correct concrete type.
   - If the factory returns `Nothing`: throw an `ApplicationException` naming the unsupported source system, resolved through [`clsTaskSourceCodeCollection.TaskSource`](task-reference-code-lookups.md) for display.
   - Otherwise: call `Task.Load(dataPortalInfo, reader)` to fill the concrete instance and add it to the collection.

## Process flow

The following diagram visualizes how criteria filters combine into the final query and how each row is materialized.

```mermaid
flowchart TD
	Start([Caller configures Criteria]) --> Base[Base SELECT from<br>TASK, all columns]
	Base --> Filter1[Apply DELETE_FLAG filter<br>unless ShowCompleted]
	Filter1 --> Filter2[Apply source, category,<br>priority filters]
	Filter2 --> Filter3[Apply date, mail ID,<br>claim, policy, action filters]
	Filter3 --> Filter4[Apply task-context filters]
	Filter4 --> Limit{RowNumLimit set?}
	Limit -->|Yes| Wrap[Wrap in ROWNUM<br>subquery with inner sort]
	Limit -->|No| Sort[Append outer sort only]
	Wrap --> Sort
	Sort --> Exec[Execute query]
	Exec --> Loop{More rows?}
	Loop -->|Yes| Factory{NewTask factory<br>returns instance?}
	Factory -->|No| Throw([ApplicationException:<br>unsupported source system])
	Factory -->|Yes| LoadRow[Load row into<br>concrete task, add to collection]
	LoadRow --> Loop
	Loop -->|No| Done([Collection returned])
```

## Failure and edge-case behavior

- `TASK_TEXT`, `CONTEXT_CODE`, `CONTEXT_CATEGORY`, `CONTEXT_TEXT`, `CONTEXT_DATE`, and `CONTEXT_NUM` filters are concatenated directly into the SQL text instead of using bind parameters, unlike every other filter in the criteria; this is a latent SQL-construction risk if caller-supplied values are not otherwise sanitized upstream.*
- An unsupported `TaskSourceCode` on a retrieved row is a hard failure: the collection throws rather than skipping the row or returning a partial result.
- `PriorityLevel` and `TaskPriorityCode` are independent properties; setting an explicit `TaskPriorityCode` takes precedence and the cascading `PriorityLevel` expansion is only evaluated when no explicit code is set.
- List-based filters (claim, policy, action, category, source) silently drop non-positive numeric values or blank strings rather than raising an error, so a caller that passes only invalid values effectively applies no filter for that dimension.*
- `ClearCriteria()` resets filter lists and dates but does not reset `RowNumLimit`, `InnerSort`, or `OuterSort`, so a criteria object reused after `ClearCriteria()` keeps any previously configured limit and sort behavior.*

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources
- `Business\SAIF.Business.Tasks\clsTaskCollection.vb`