---
title: "SQL-042: Policy lookup family*"
summary: Policy lookup family*.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business
reference_type: package
---

# SQL-042: Policy lookup family*

This document describes `SQL-042`, the policy lookup family* statement or call used by `SAIF.Business`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-042 |
| **Operation type** | Read |
| **Execution method** | Mixed |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text / StoredProcedure |
| **Business object** | `PolicyServices` |
| **Criteria property** | `N/A` |
| **Lifecycle method** | `address/coverage/validation/default-expiration helpers` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:in_policy_num`..` :out_dba_name`, `:PolicyNumber`, `:inDate`, `:out_exp_date` | Mixed | Mixed | Mixed | PolicyServices uses large output contract and inline policy predicates |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY_PERIOD_ALL](../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. |
| [NON_EMPR_ACCT](../database-tables.md#non-empr-acct) | SELECT | See sample SQL and source implementation. |

## Sample SQL

```sql
sp62087_get_policy_address(:in_policy_num, :in_pol_per_eff_date, :process_date, :in_correspond_type, :out_policy_name, :out_loc_addr1, :out_loc_addr2, :out_loc_addr3, :out_loc_city, :out_loc_county, :out_loc_state, :out_loc_country, :out_loc_country_cd, :out_loc_zip, :out_loc_zip4, :out_loc_phone, :out_loc_phone_ext, :out_loc_fax, :bar_code, :out_legal_name, :out_dba_name)
SELECT COUNT(*) FROM policy_period_all WHERE policy_num = :PolicyNumber AND policy_period_eff_date <= :inDate AND NVL(cancel_date, policy_period_exp_date) > :inDate AND (policy_period_status_code <> 'P' AND policy_period_status_code <> 'PPROS')
SELECT COUNT(*) FROM policy WHERE policy_num = :PolicyNumber
SELECT COUNT(*) FROM non_empr_acct WHERE acct_num = :PolicyNumber
SP72232_Calc_Policy_Exp_Date(:in_function_code, :in_pol_per_eff_date, :in_fs, :out_exp_date)
```

## Operations and processes
- [Policy and unit-stat support](../operations/policy-and-unit-stat-support.md)

## Business rules
- [BR-SB-005: Low-number policies validate against NON_EMPR_ACCT](../business-rules.md#br-sb-005-low-number-policies-validate-against-non-empr-acct)
- [PROC-RULE-022: UW312 and UW310 correspondence prefers the payroll mailing address before standard policy locations.](../business-rules.md#proc-rule-022-uw312-and-uw310-correspondence-prefers-the-payroll-mailing-address-before-standard-policy-locations)
- [PROC-RULE-023: Other correspondence uses the standard policy location first and then the latest eligible expired policy location.](../business-rules.md#proc-rule-023-other-correspondence-uses-the-standard-policy-location-first-and-then-the-latest-eligible-expired-policy-location)
- [PROC-RULE-024: Policy-location rows are filtered against either the policy effective date or the process date depending on whether processing is before or after the policy period start.](../business-rules.md#proc-rule-024-policy-location-rows-are-filtered-against-either-the-policy-effective-date-or-the-process-date-depending-on-whether-processing-is-before-or-after-the-policy-period-start)
- [PROC-RULE-025: Barcode output switches to email address for UW560 correspondence.](../business-rules.md#proc-rule-025-barcode-output-switches-to-email-address-for-uw560-correspondence)
- [PROC-RULE-026: Missing country codes default to the United States before country-name lookup.](../business-rules.md#proc-rule-026-missing-country-codes-default-to-the-united-states-before-country-name-lookup)
- [PROC-RULE-027: The DBA line is only emitted when the legal name differs from the policy name.](../business-rules.md#proc-rule-027-the-dba-line-is-only-emitted-when-the-legal-name-differs-from-the-policy-name)
- [PROC-RULE-028: Missing policy-location data is surfaced as an application error after blanking all output fields.](../business-rules.md#proc-rule-028-missing-policy-location-data-is-surfaced-as-an-application-error-after-blanking-all-output-fields)
- [PROC-RULE-029: MAX mode returns the first day of the month 15 months after the effective date.](../business-rules.md#proc-rule-029-max-mode-returns-the-first-day-of-the-month-15-months-after-the-effective-date)
- [PROC-RULE-030: Default mode uses a 13-month horizon for second-half effective dates and 12 months otherwise.](../business-rules.md#proc-rule-030-default-mode-uses-a-13-month-horizon-for-second-half-effective-dates-and-12-months-otherwise)
- [PROC-RULE-031: AR policies snap the calculated expiration month to quarter boundaries.](../business-rules.md#proc-rule-031-ar-policies-snap-the-calculated-expiration-month-to-quarter-boundaries)
- [PROC-RULE-032: Unhandled calculation failures are surfaced as application errors.](../business-rules.md#proc-rule-032-unhandled-calculation-failures-are-surfaced-as-application-errors)

> **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
- `Business\SAIF.Business\PolicyServices.vb`