---
title: "SQL-016: Retrieve external image info"
summary: Retrieves file code, category code, and document type code data with culture-specific localized descriptions for external (portal-facing) document display.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.Imaging
reference_type: package
---

# SQL-016: Retrieve external image info

This statement retrieves a joined result set of imaging code table data — file code, category code, and document type code — with culture-specific external descriptions sourced from the corresponding `GCODE` tables. Only currently effective document types are returned (filtered by `effective_date <= sysdate` and `expiration_date >= sysdate` or null). Called from `clsExternalImageInfo.GetExternalDocumentInfo`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-016 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsExternalImageInfo` |
| **Criteria property** | `RETRIEVE_SQL` |
| **Lifecycle method** | `GetExternalDocumentInfo` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:culture_code` | Input | [not specified]** | 5 | Culture code used for localized description lookup (upper-cased before binding). |
| `:file_code` | Input | [not specified]** | 5 | Image file code to filter results. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [IMAGE_FILE_CODE](../database-tables.md#image_file_code) | SELECT / JOIN | CODE, CODE_DESCR |
| [IMAGE_CATEGORY_CODE](../database-tables.md#image_category_code) | SELECT / JOIN | CODE, IMAGE_FILE_CODE, EXTERNAL_DESCR |
| [IMAGE_DOCUMENT_TYPE_CODE](../database-tables.md#image_document_type_code) | SELECT / JOIN | CODE, IMAGE_FILE_CODE, IMAGE_CATEGORY_CODE, EXTERNAL_DESCR, SORT_ORDER_NUM, EFFECTIVE_DATE, EXPIRATION_DATE |
| [IMAGE_CATEGORY_GCODE](../database-tables.md#image_category_gcode) | SELECT / LEFT JOIN | CODE, IMAGE_FILE_CODE, CULTURE_CODE, EXTERNAL_DESCR |
| [IMAGE_DOCUMENT_TYPE_GCODE](../database-tables.md#image_document_type_gcode) | SELECT / LEFT JOIN | CODE, IMAGE_FILE_CODE, IMAGE_CATEGORY_CODE, CULTURE_CODE, EXTERNAL_DESCR |

## Sample SQL

```sql
select
    f.code as file_code,
    cat.code as category_code,
    doc.code as document_type_code,
    f.code_descr as file_description,
    case when gcat.code is not null
         then gcat.external_descr
         else cast(cat.external_descr as nvarchar2(200))
    end as category_ext_description,
    cat.external_descr as category_ext_description_code,
    doc.external_descr as doc_type_ext_description_code,
    case when gdoc.code is not null
         then gdoc.external_descr
         else cast(doc.external_descr as nvarchar2(200))
    end as document_type_ext_description,
    doc.sort_order_num,
    doc.effective_date,
    doc.expiration_date
from image_file_code f
join image_category_code cat
    on cat.image_file_code = f.code
join image_document_type_code doc
    on doc.image_file_code = f.code
    and doc.image_category_code = cat.code
    and doc.image_file_code = cat.image_file_code
left join image_category_gcode gcat
    on gcat.code = cat.code
    and gcat.image_file_code = cat.image_file_code
    and gcat.culture_code = :culture_code
left join image_document_type_gcode gdoc
    on gdoc.code = doc.code
    and gdoc.image_file_code = doc.image_file_code
    and gdoc.image_category_code = doc.image_category_code
    and gdoc.culture_code = :culture_code
where f.code = :file_code
  and doc.effective_date <= sysdate
  and (doc.expiration_date is null or doc.expiration_date >= sysdate)
order by doc.image_file_code, doc.image_category_code, doc.sort_order_num
```

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

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.

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