SQL-020 Future cancel date¶

This SQL retrieves the latest unreinstated and unrescinded cancellation date for a policy so pending cancellation status can expose the future cancel date.

Classification¶

Field	Value
SQL ID	SQL-020
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsPolicyData`
Criteria property	`N/A`
Lifecycle method	`GetFutureCancelDate`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNumber`	Input	Decimal	N/A	Policy number whose future cancellation date is requested.

Tables¶

Table	Access	Columns used
CANCELLATION	SELECT	`policy_num`, `cancellation_seq_num`, `cancel_date`, `reinstate_date`, `rescind_date

Sample SQL¶

SELECT
  cancel_date
FROM cancellation c1
WHERE (policy_num = :PolicyNumber)
  AND cancellation_seq_num = (
    SELECT
      MAX(cancellation_seq_num)
    FROM cancellation c2
    WHERE c1.policy_num = c2.policy_num)
  AND reinstate_date IS NULL
  AND rescind_date IS NULL

Operations and processes¶

Retrieve policy data

Business rules¶

BR-VP-017: Future cancel date only for Pending Cancellation

Sources¶

SAIF.Business.View.Policy/clsPolicyData.vb