SQL-010 Policy period expiration date¶

Retrieves a policy period's expiration date, unioning the WCIS (POLICY_PERIOD) and non-WCIS (POLICY_PERIOD_NON_WCIS) stores, used by PolicyInformation.PolicyPeriodExpirationDate (BR-POL-010).

Classification¶

Field	Value
SQL ID	SQL-010
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.PolicyPeriodExpirationDate`, `PolicyPeriodExpirationDate` property getter)

Parameters¶

Parameter	Direction	Type	Max length	Description
`:in_policy_num`	Input	Decimal (Oracle `NUMBER`)	N/A	Policy number; applied to both branches of the `UNION`.
`:in_policy_period_eff_date`	Input	Date (Oracle `DATE`)	N/A	Policy period effective date; applied to both branches of the `UNION`.

Tables¶

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

Sample SQL¶

SELECT
  policy_period_exp_date
FROM policy_period pp
WHERE policy_num = :in_policy_num
  AND policy_period_eff_date = :in_policy_period_eff_date
UNION
SELECT
  policy_period_exp_date
FROM policy_period_non_wcis pp
WHERE policy_num = :in_policy_num
  AND policy_period_eff_date = :in_policy_period_eff_date

Operations and processes¶

OP-05 Policy period expiration date (see Operations and processes)

Business rules¶

BR-POL-010: Dual-source period date resolution

Sources¶

SAIF.Business.Policy/PolicyInformation.vb