Skip to content

SQL-008 Policy effective date

Retrieves the effective date of a policy's most recent period via a correlated self-join subquery, used by PolicyInformation.MaxPolicyEffectiveDate (BR-POL-009).

Classification

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

Parameters

Parameter Direction Type Max length Description
:in_policy_num Input Decimal (Oracle NUMBER) N/A Policy number whose latest policy-period effective date is returned.

Tables

Table Access Columns used
POLICY_PERIOD SELECT (self-correlated) 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)

A NULL/absent result causes the caller's CDate conversion to throw (unhandled).*

Operations and processes

Business rules

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

  • SAIF.Business.Policy/PolicyInformation.vb