SQL-014: EAIP period dropdown by claim number¶

This statement retrieves EAIP period data for building a dropdown list, filtering by clm.saif_claim_num = :inClaimNum. It is structurally identical to SQL-013 but accepts a SAIF claim number rather than a claim ID. Called from clsEAIP.BuildEAIPPeriodDropDown when the input is a claim number.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:inClaimNum`	Input	[not specified]**	8	The SAIF claim number to filter EAIP periods.

Tables¶

Table	Access	Columns used
CL_INJURY_CLAIM	SELECT / JOIN	CLAIM_ID, SAIF_CLAIM_NUM
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.saif_claim_num = :inClaimNum
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