Skip to content

SQL-028: Association period date range retrieval

clsAssociationTypeCode uses this statement to attach a coverage date range to each associated policy shown in a policy header. Only the most recent issued period counts, where "issued" means a printed P401 endorsement or a non-WCIS period in status E.

Classification

Field Value
SQL ID SQL-028
Operation type Read
Execution method Inline SQL with String.Format substitution
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsAssociationTypeCode
Criteria property N/A (module-level Const, not IBusinessCriteria)
Lifecycle method GetPeriodDates

Parameters

Parameter Direction Type Max length Description
{0} (substituted list) Input (literal substitution) Decimal list N/A Comma-joined list of policy numbers written directly into the statement text by String.Format; not a bind variable.

Tables

Table Access Columns used
POLICY_PERIOD_ALL SELECT POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_EXP_DATE, CANCEL_DATE
ENDORSEMENT JOIN POLICY_NUM, POLICY_PERIOD_EFF_DATE, ENDORSE_CODE, PRINTED_DATE
POLICY_PERIOD_NON_WCIS JOIN POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_STATUS_CODE

Sample SQL

select pp.policy_num as PolicyNumber, pp.policy_period_eff_date as EffectiveDate,
       nvl(pp.cancel_date, pp.policy_period_exp_date) as ExpirationDate
from policy_period_all pp
join (  -- get the most recent issued period per policy
       select  pp.policy_num, max(pp.policy_period_eff_date) as policy_period_eff_date
       from policy_period_all pp
       join (      -- get all issued periods for all policies
                   select policy_period_eff_date, policy_num
                     from endorsement
                    where endorse_code = 'P401' and
                          printed_date IS NOT NULL
               union
                   select policy_period_eff_date, policy_num
                     from policy_period_non_wcis
                    where policy_period_status_code = 'E'
             ) issued_periods on pp.policy_num = issued_periods.policy_num and
                                 pp.policy_period_eff_date = issued_periods.policy_period_eff_date
       where pp.policy_period_eff_date <> nvl(cancel_date, policy_period_exp_date)
       group by pp.policy_num
   ) max_periods on pp.policy_num = max_periods.policy_num and
                    pp.policy_period_eff_date = max_periods.policy_period_eff_date
where pp.policy_num in ({0})

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. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • Business/SAIF.Business.View.Policy/Associations/clsAssociationTypeCode.vb