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	SELECT / JOIN	TOWID, BATID, CRTRID, DELCD, IDXFL, IFNDS, IFNID, NPAGES, SCANDT, SLEVEL, UPDRID, UPDTTS
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	SELECT / JOIN	CODE, CODE_DESCR, IMAGE_FILE_CODE, EXTERNAL_DESCR
IMAGE_DOCUMENT_TYPE_CODE	SELECT / JOIN	CODE, CODE_DESCR, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE
IMAGE_DOCUMENT_NAME_CODE	SELECT / LEFT JOIN	CODE, CODE_DESCR, IMAGE_DOCUMENT_TYPE_CODE, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE
PDF_CACHE	SELECT / LEFT JOIN	TOWID
PDF_CACHE_REQUEST	SELECT / LEFT JOIN	TOWID, PDF_CACHE_STATUS_CODE
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*.

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

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