---
title: "SQL-013: EAIP period dropdown by claim ID"
summary: Retrieves EAIP period records for a claim using the claim ID, joining period and status code tables, ordered by status priority and effective date.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.Imaging
reference_type: package
---

# SQL-013: EAIP period dropdown by claim ID

This statement retrieves EAIP period data for building a dropdown list, filtering by `clm.claim_id = :inClaimID`. It joins `CL_INJURY_CLAIM`, `EAIP_PERIOD`, and `EAIP_STATUS_CODE` and orders results by a CASE-based status priority (N=1, A=2, I=3, C=4) and begin date descending. Called from `clsEAIP.BuildEAIPPeriodDropDown` when the input is a claim ID.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-013 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsEAIP` |
| **Criteria property** | `BuildEAIPPeriodDropDown (claim ID)` |
| **Lifecycle method** | `BuildEAIPPeriodDropDown` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:inClaimID` | Input | [not specified]** | N/A | The claim ID to filter EAIP periods. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CL_INJURY_CLAIM](../database-tables.md#cl_injury_claim) | SELECT / JOIN | CLAIM_ID |
| [EAIP_PERIOD](../database-tables.md#eaip_period) | SELECT / JOIN | CLAIM_ID, EAIP_PERIOD_SEQ_NUM, EAIP_PERIOD_EFF_DATE, EAIP_PERIOD_EXP_DATE, EAIP_STATUS_CODE |
| [EAIP_STATUS_CODE](../database-tables.md#eaip_status_code) | SELECT / JOIN | CODE, CODE_DESCR |

## Sample SQL

```sql
SELECT
    EP.EAIP_PERIOD_SEQ_NUM as EaipPeriodSeqNum,
    TRIM(es.code_descr) || ' (' ||
        TO_CHAR(EP.EAIP_PERIOD_EFF_DATE, 'MM/DD/YYYY') || ' - ' ||
        TO_CHAR(EP.EAIP_PERIOD_EXP_DATE, 'MM/DD/YYYY') || ')' descr,
    CASE
        WHEN eaip_status_code = 'N' THEN '1'
        WHEN eaip_status_code = 'A' THEN '2'
        WHEN eaip_status_code = 'I' THEN '3'
        WHEN eaip_status_code = 'C' THEN '4'
    END status_code_order,
    CASE
        WHEN ep.EAIP_PERIOD_EFF_DATE IS NULL THEN SYSDATE
        ELSE EP.EAIP_PERIOD_EFF_DATE
    END begin_date_order
FROM cl_injury_claim clm
JOIN eaip_period ep ON (clm.claim_id = ep.claim_id)
JOIN eaip_status_code es ON (ep.eaip_status_code = code)
WHERE clm.claim_id = :inClaimID
order by status_code_order, begin_date_order desc
```

## Operations and processes
- [EAIP specialist lookup](../operations/eaip-specialist-lookup.md)

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

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