SQL-008 Max policy effective date¶

Retrieves the policy-period effective date matching the maximum effective date for a given policy number, via a correlated subquery against POLICY_PERIOD, consumed by PolicyInformation.MaxPolicyEffectiveDate.

Classification¶

Field	Value
SQL ID	SQL-008
Operation type	Read
Execution method	Inline SQL, string-concatenation via `SQL.PolicyEffectiveDate`
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	N/A (Application layer)
Criteria property	N/A
Lifecycle method	N/A
Application class	`PolicyInformation` (property `MaxPolicyEffectiveDate`, Get)

Parameters¶

Parameter	Direction	Type	Max length	Description
`:in_policy_num`	Input	Decimal (`inPolicyNumber`)	N/A	Policy number filter, correlated against the max effective date subquery.

Tables¶

Table	Access	Columns used
POLICY_PERIOD	SELECT	`POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`

Sample SQL¶

Select policy_period_eff_date
from policy_period pp
where policy_num = :in_policy_num
  and policy_period_eff_date = (
    select max(policy_period_eff_date)
    from policy_period pp2
    where pp2.policy_num = pp.policy_num)

Failure and edge-case behavior¶

MaxPolicyEffectiveDate casts DataRequest.Result directly to Date without a null/DBNull guard — behavior when no matching row exists is unconfirmed.**

Operations and processes¶

Determine maximum policy effective date

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

Sources¶

SAIF.Business.Policy\PolicyInformation.vb