---
title: "SQL-021: Retrieve image collection (dynamic)"
summary: Dynamically assembled SELECT retrieving up to 300 image records with joined code descriptions, PDF cache status, and runtime-appended filter predicates.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.Imaging
reference_type: package
---

# SQL-021: Retrieve image collection (dynamic)

This is the primary image collection query, assembled by `Criteria.FormatRequest` using a `StringBuilder` pattern. The sample SQL shows the default List-mode statement; at runtime, many optional filter predicates (category, document type, file type, claim IDs, EAIP period, date ranges, external view code, batch ID, etc.) are conditionally appended. Results are limited to 300 rows by `ROWNUM` and ordered by `IMAGESEQNUM ASC` in the outer query. Default filters exclude deleted images, set `DiscoveryFlag = 'N'`, and set `SecurityLevelNum = 0`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-021 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (dynamic StringBuilder) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text** |
| **Business object** | `clsImageCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve + Criteria.FormatRequest` |

## Parameters

Parameters are conditionally added at runtime. The following represent the full documented set*:

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:CAT1`..`:CATn` | Input | [not specified]** | 5 | Category code filter values (one per filter entry)*. |
| `:CLAIMNUM1`..`:CLAIMNUMn` | Input | [not specified]** | 8 | Claim number filter values*. |
| `:CLAIMID1`..`:CLAIMIDn` | Input | [not specified]** | N/A | Claim ID filter values*. |
| `:BeginScanDate` | Input | [not specified]** | N/A | Start of scan date range. |
| `:EndScanDate` | Input | [not specified]** | N/A | End of scan date range. |
| `:BeginDocDate` | Input | [not specified]** | N/A | Start of document date range. |
| `:EndDocDate` | Input | [not specified]** | N/A | End of document date range. |
| `:DiscoveryFlag` | Input | [not specified]** | 1 | Discovery flag filter (Y/N). |
| `:DOCTYPE1`..`:DOCTYPEn` | Input | [not specified]** | 5 | Document type filter values*. |
| `:EAIPPeriodSeq` | Input | [not specified]** | N/A | EAIP period sequence number filter. |
| `:FILETYPE1`..`:FILETYPEn` | Input | [not specified]** | 5 | File type filter values*. |
| `:ImageSeqNum1`..`:ImageSeqNumn` | Input | [not specified]** | N/A | Image sequence number filter values*. |
| `:CultureCode` | Input | [not specified]** | 2 | Two-letter ISO culture code for localized descriptions. |
| `:POLICYNUM1`..`:POLICYNUMn` | Input | [not specified]** | N/A | Policy number filter values*. |
| `:EXTERNALVIEWCODE` | Input | [not specified]** | 5 | External view code filter. |
| `:TOWID1`..`:TOWIDn` | Input | [not specified]** | N/A | TOWID filter values*. |
| `:BatchID` | Input | [not specified]** | 30 | Batch ID filter. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [IMGSYS](../database-tables.md#imgsys) | SELECT / JOIN | TOWID, BATID, CRTRID, DELCD, IDXFL, IFNDS, IFNID, NPAGES, SCANDT, SLEVEL, UPDRID, UPDTTS |
| [IMAGE](../database-tables.md#image) | SELECT / JOIN | IMAGE_SEQ_NUM, CLAIM_ID, CLAIM_NUM, COMMENT_TEXT, CREATE_DATE, CREATE_USER, DISCOVERY_FLAG, DISPLAY_FIELD_1–3, DISPLAY_SORT_NUM, DOCUMENT_DATE, IMAGE_CATEGORY_CODE, IMAGE_DELETE_CODE, IMAGE_DOCUMENT_TYPE_CODE, IMAGE_DOCUMENT_NAME_CODE, IMAGE_FILE_CODE, POLICY_NUM, RECEIVED_DATE, SECURITY_LEVEL_NUM, TOWID, UPDATE_DATE, UPDATE_USER, EXTERNAL_VIEW_CODE, EXTERNAL_VIEW_UPDATE_DATE, SYSTEM_GENERATED_FLAG |
| [IMAGE_CATEGORY_CODE](../database-tables.md#image_category_code) | SELECT / JOIN | CODE, CODE_DESCR, IMAGE_FILE_CODE, EXTERNAL_DESCR |
| [IMAGE_DOCUMENT_TYPE_CODE](../database-tables.md#image_document_type_code) | SELECT / JOIN | CODE, CODE_DESCR, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE |
| [IMAGE_DOCUMENT_NAME_CODE](../database-tables.md#image_document_name_code) | SELECT / LEFT JOIN | CODE, CODE_DESCR, IMAGE_DOCUMENT_TYPE_CODE, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE |
| [PDF_CACHE](../database-tables.md#pdf_cache) | SELECT / LEFT JOIN | TOWID |
| [PDF_CACHE_REQUEST](../database-tables.md#pdf_cache_request) | SELECT / LEFT JOIN | TOWID, PDF_CACHE_STATUS_CODE |
| [PDF_CACHE_STATUS_CODE](../database-tables.md#pdf_cache_status_code) | SELECT / LEFT JOIN | CODE, CODE_DESCR |

## Sample SQL

The following shows the default List-mode statement with no optional filters and no culture join. Additional WHERE predicates are appended by `Criteria.FormatRequest` at runtime*.

```sql
select * from (
    Select
        case when cache.towid is not null then 'Cached'
             when cacherequest.towid is not null then cachecode.code_descr
             else 'None' end as CacheStatus,
        IMAGE.CLAIM_ID as ClaimID,
        IMAGE.CLAIM_NUM as ClaimNum,
        IMAGE.COMMENT_TEXT as CommentText,
        IMAGE.CREATE_DATE as CreateDate,
        IMAGE.CREATE_USER as CreateUser,
        IMAGE.DISCOVERY_FLAG as DiscoveryFlag,
        IMAGE.DISPLAY_FIELD_1 as DisplayField1,
        IMAGE.DISPLAY_FIELD_2 as DisplayField2,
        IMAGE.DISPLAY_FIELD_3 as DisplayField3,
        IMAGE.DISPLAY_SORT_NUM as DisplaySortNum,
        IMAGE.DOCUMENT_DATE as DocumentDate,
        IMAGE.EXTERNAL_VIEW_CODE as ExternalViewCode,
        IMAGE.EXTERNAL_VIEW_UPDATE_DATE as ExternalViewUpdateDate,
        IMAGE.SYSTEM_GENERATED_FLAG as SystemGeneratedFlag,
        IMAGE.IMAGE_CATEGORY_CODE as ImageCategoryCode,
        ICC.CODE_DESCR as ImageCategoryDescr,
        IDNC.CODE_DESCR as ImageDocumentNameDescription,
        ICC.EXTERNAL_DESCR as ImageCategoryExternalDescr,
        IDTC.EXTERNAL_DESCR as ImageDocumentTypeExternalDescr,
        IDTC.EXTERNAL_LONG_DESCR as ImageDocTypeExternalLongDescr,
        IMAGE.IMAGE_DELETE_CODE as ImageDeleteCode,
        IMAGE.IMAGE_DOCUMENT_TYPE_CODE as ImageDocumentTypeCode,
        IDNC.CODE as ImageDocumentNameCode,
        IDTC.CODE_DESCR as ImageDocumentTypeDescr,
        IMAGE.IMAGE_FILE_CODE as ImageFileCode,
        IMAGE.IMAGE_SEQ_NUM as ImageSeqNum,
        IMAGE.POLICY_NUM as PolicyNum,
        IMAGE.RECEIVED_DATE as ReceivedDate,
        IMAGE.SECURITY_LEVEL_NUM as SecurityLevelNum,
        IMAGE.UPDATE_DATE as UpdateDate,
        IMAGE.UPDATE_USER as UpdateUser,
        IMGSYS.BATID Batid, IMGSYS.CRTRID Crtrid, IMGSYS.DELCD Delcd,
        IMGSYS.IDXFL Idxfl, IMGSYS.IFNDS Ifnds, IMGSYS.IFNID Ifnid,
        IMGSYS.NPAGES Npages, IMGSYS.SCANDT Scandt, IMGSYS.SLEVEL Slevel,
        IMGSYS.TOWID Towid, IMGSYS.UPDRID Updrid, IMGSYS.UPDTTS Updtts,
        Null As EAIPPeriodSeq
    From IMGSYS
    join IMAGE on (IMGSYS.TOWID = IMAGE.TOWID)
    join IMAGE_CATEGORY_CODE ICC
        on (image.image_file_code = icc.image_file_code
            AND image.image_category_code = icc.code)
    join image_document_type_code IDTC
        on (IMAGE.IMAGE_DOCUMENT_TYPE_CODE = IDTC.CODE
            AND IMAGE.IMAGE_CATEGORY_CODE = IDTC.IMAGE_CATEGORY_CODE
            AND IMAGE.IMAGE_FILE_CODE = IDTC.IMAGE_FILE_CODE)
    left join image_document_name_code idnc
        on (idnc.image_document_type_code = image.image_document_type_code
            AND idnc.image_category_code = image.image_category_code
            AND idnc.image_file_code = image.image_file_code
            AND idnc.code = image.image_document_name_code)
    left join pdf_cache cache on cache.towid = image.towid
    left join pdf_cache_request cacherequest on cacherequest.towid = image.towid
    left join pdf_cache_status_code cachecode
        on cachecode.code = cacherequest.pdf_cache_status_code
    Where 1=1
      AND IMAGE.DISCOVERY_FLAG = :DiscoveryFlag
      AND nvl(IMAGE.IMAGE_DELETE_CODE,' ') = ' '
      AND IMAGE.SECURITY_LEVEL_NUM = 0
    order by scandt desc
) where rownum <= 300
ORDER BY IMAGESEQNUM ASC
```

## Operations and processes
- [Image collection retrieval](../operations/image-collection-retrieval.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Business\SAIF.Business.Imaging\clsImageCollection.vb`