SQL-043: Unit-stat lookup family¶
This document describes SQL-043, the unit-stat lookup family statement or call used by SAIF.Business.
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-043 |
| Operation type | Read |
| Execution method | Inline |
| Database | Oracle |
| Schema | [unqualified]** |
| Command type | Text |
| Business object | UnitStatServices |
| Criteria property | N/A |
| Lifecycle method | GetNCCIPolicyNumber, GetPolicyUnitEffectiveDate |
Parameters¶
This SQL statement has no explicit parameters in the provided findings.
Tables¶
| Table | Access | Columns used |
|---|---|---|
| POLICY_PERIOD_MASTER | SELECT | See sample SQL and source implementation. |
| POLICY | SELECT | See sample SQL and source implementation. |
| US_POLICY_UNIT | SELECT | See sample SQL and source implementation. |
Sample SQL¶
SELECT CASE p.market_type_code WHEN 'VOL' THEN 'A' ELSE 'C' END
|| CASE WHEN LENGTH(TRIM(p.policy_num)) > 6 THEN TRIM(p.policy_num) ELSE LPAD(p.policy_num, 6, '0') END
|| TRIM(p.pms_account_subcd)
|| pm.policy_sfx AS NCCIPolicyNumber
FROM policy_period_master pm, policy p
WHERE pm.policy_num = :PolicyNumber
AND pm.policy_period_eff_date = :PolicyPeriodEffectiveDate
AND p.policy_num = pm.policy_num
SELECT US_POLICY_UNIT.UNIT_EFF_DATE AS UnitEffectiveDate FROM US_POLICY_UNIT WHERE US_POLICY_UNIT.POLICY_NUM = :PolicyNumber AND :ProcessDate BETWEEN US_POLICY_UNIT.UNIT_EFF_DATE AND US_POLICY_UNIT.UNIT_EXP_DATE AND US_POLICY_UNIT.ACTIVE_UNIT_FLAG = 'Y'
Operations and processes¶
Business rules¶
- BR-SB-003: Extended policy periods split only when the period exceeds one year plus sixteen days
- BR-SB-004: Report valuation schedules switch from five to ten periods in 2002
- BR-SB-012: NCCI policy numbers prefix voluntary policies with A and others with C
Note: Statements marked with
**could not be confirmed; treat as unknown until verified.
Sources¶
Business\SAIF.Business\UnitStatServices.vb