SQL-VP-028 Retrieve period dates for a set of policy numbers¶

Used by clsAssociationTypeCode.GetPeriodDates() (PeriodDateSQL) to resolve period dates when grouping policies for experience/discount-combination association types.

Classification¶

Field	Value
SQL ID	SQL-VP-028
Operation type	Read
Execution method	Inline SQL (`String.Format`-composed `IN (...)` list)
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsAssociationTypeCode`
Criteria property	`GetPeriodDates()` (ad hoc static/shared method)
Lifecycle method	ad hoc static/shared method

Parameters¶

Parameter	Direction	Type	Max length	Description
policy-number list (`{0}`)	Input	`String.Format` `IN ({0})` substitution	N/A	Comma-separated list of policy numbers, composed internally (not raw user input) rather than passed as a bind parameter.*

Tables¶

Table	Access	Columns used
POLICY_PERIOD_ALL	SELECT / JOIN	`POLICY_NUM`, `POLICY_PERIOD_ALL_ID`, `POLICY_PERIOD_EFF_DATE`, `POLICY_PERIOD_EXP_DATE`
ENDORSEMENT	LEFT JOIN	`POLICY_PERIOD_ALL_ID`, `ENDORSEMENT_EFF_DATE`
POLICY_PERIOD_NON_WCIS	LEFT JOIN	`POLICY_PERIOD_ALL_ID`, `POLICY_PERIOD_NON_WCIS_ID`

Sample SQL¶

SELECT
  POLICY_PERIOD_ALL.POLICY_NUM,
  POLICY_PERIOD_ALL.POLICY_PERIOD_EFF_DATE,
  POLICY_PERIOD_ALL.POLICY_PERIOD_EXP_DATE,
  ENDORSEMENT.ENDORSEMENT_EFF_DATE,
  POLICY_PERIOD_NON_WCIS.POLICY_PERIOD_NON_WCIS_ID
FROM
  POLICY_PERIOD_ALL
  LEFT JOIN ENDORSEMENT
    ON POLICY_PERIOD_ALL.POLICY_PERIOD_ALL_ID = ENDORSEMENT.POLICY_PERIOD_ALL_ID
  LEFT JOIN POLICY_PERIOD_NON_WCIS
    ON POLICY_PERIOD_ALL.POLICY_PERIOD_ALL_ID = POLICY_PERIOD_NON_WCIS.POLICY_PERIOD_ALL_ID
WHERE
  POLICY_PERIOD_ALL.POLICY_NUM IN ({0})

The {0} placeholder is substituted via String.Format with a comma-separated list of policy numbers (not raw free-text user input), then executed as CommandType.Text; exact join columns are inferred from typical POLICY_PERIOD_ALL schema naming conventions and were not verbatim-confirmed against the live constant text.*

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¶

SAIF.Business.View.Policy\Associations\clsAssociationTypeCode.vb