---
title: "SQL-043: Unit-stat lookup family"
summary: Unit-stat lookup family.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business
reference_type: package
---

# 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](../database-tables.md#policy-policy-period-all-policy-period-master-us-policy-unit) | SELECT | See sample SQL and source implementation. |
| [POLICY](../database-tables.md#policy-policy-period-all-policy-period-master-us-policy-unit) | SELECT | See sample SQL and source implementation. |
| [US_POLICY_UNIT](../database-tables.md#policy-policy-period-all-policy-period-master-us-policy-unit) | SELECT | See sample SQL and source implementation. |

## Sample SQL

```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
- [Policy and unit-stat support](../operations/policy-and-unit-stat-support.md)

## Business rules
- [BR-SB-003: Extended policy periods split only when the period exceeds one year plus sixteen days](../business-rules.md#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](../business-rules.md#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](../business-rules.md#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`