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	SELECT / JOIN	CLAIM_ID
EAIP_PERIOD	SELECT / JOIN	CLAIM_ID, EAIP_PERIOD_SEQ_NUM, EAIP_PERIOD_EFF_DATE, EAIP_PERIOD_EXP_DATE, EAIP_STATUS_CODE
EAIP_STATUS_CODE	SELECT / JOIN	CODE, CODE_DESCR

Sample 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

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

Sources¶

Business\SAIF.Business.Imaging\clsEAIP.vb