SQL-024: Retrieve IMAGE_DOCUMENT_TYPE_CODE collection¶

This statement retrieves IMAGE_DOCUMENT_TYPE_CODE rows with effective-date filtering using Oracle TRUNC(SYSDATE) logic. The WhereClause property in Criteria switches between an AllWhere variant (returns all currently effective rows) and a FileWhere variant (scoped to a specific IMAGE_FILE_CODE). The sample SQL shows the AllWhere variant. A CultureCode parameter may exist for culture-specific joins but was not confirmed in live BeforeRetrieve bindings**.

Classification¶

Field	Value
SQL ID	SQL-024
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text**
Business object	`clsImageDocumentTypeCodeCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ImageFileCode`	Input	[not specified]**	5	File code filter (only when FileWhere variant is selected)*.
`:CultureCode`	Input	[not specified]**	N/A	Culture code for localized descriptions (not confirmed in live BeforeRetrieve)**.

Tables¶

Table	Access	Columns used
IMAGE_DOCUMENT_TYPE_CODE	SELECT	CODE, CODE_DESCR, CREATE_DATE, CREATE_USER, DEFAULT_DISCOVERY_FLAG, DEFAULT_CONFIDENTIAL_FLAG, DEFAULT_EXTERNAL_VIEW_CODE, EFFECTIVE_DATE, EXPIRATION_DATE, EXTERNAL_DESCR, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE, PRIORITY_CODE, SORT_ORDER_NUM, UPDATE_DATE, UPDATE_USER

Sample SQL¶

The following shows the AllWhere variant (returns all currently effective rows):

Select
    IMAGE_DOCUMENT_TYPE_CODE.CODE as Code,
    IMAGE_DOCUMENT_TYPE_CODE.CODE_DESCR as CodeDescr,
    IMAGE_DOCUMENT_TYPE_CODE.CREATE_DATE as CreateDate,
    IMAGE_DOCUMENT_TYPE_CODE.CREATE_USER as CreateUser,
    IMAGE_DOCUMENT_TYPE_CODE.DEFAULT_DISCOVERY_FLAG as DefaultDiscoveryFlag,
    IMAGE_DOCUMENT_TYPE_CODE.DEFAULT_CONFIDENTIAL_FLAG as DefaultConfidentialFlag,
    IMAGE_DOCUMENT_TYPE_CODE.DEFAULT_EXTERNAL_VIEW_CODE as DefaultExternalViewCode,
    IMAGE_DOCUMENT_TYPE_CODE.EFFECTIVE_DATE as EffectiveDate,
    IMAGE_DOCUMENT_TYPE_CODE.EXPIRATION_DATE as ExpirationDate,
    IMAGE_DOCUMENT_TYPE_CODE.EXTERNAL_DESCR as ExternalDescription,
    IMAGE_DOCUMENT_TYPE_CODE.IMAGE_CATEGORY_CODE as ImageCategoryCode,
    IMAGE_DOCUMENT_TYPE_CODE.IMAGE_FILE_CODE as ImageFileCode,
    IMAGE_DOCUMENT_TYPE_CODE.PRIORITY_CODE as PriorityCode,
    IMAGE_DOCUMENT_TYPE_CODE.SORT_ORDER_NUM as SortOrderNum,
    IMAGE_DOCUMENT_TYPE_CODE.UPDATE_DATE as UpdateDate,
    IMAGE_DOCUMENT_TYPE_CODE.UPDATE_USER as UpdateUser
From IMAGE_DOCUMENT_TYPE_CODE
Where TRUNC(SYSDATE) BETWEEN TRUNC(effective_date)
    AND NVL(TRUNC(expiration_date) - 1, SYSDATE + 1)
ORDER BY SORT_ORDER_NUM,
    IMAGE_DOCUMENT_TYPE_CODE.IMAGE_CATEGORY_CODE,
    IMAGE_DOCUMENT_TYPE_CODE.CODE_DESCR

Operations and processes¶

Code table management

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\clsImageDocumentTypeCodeCollection.vb