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 | SELECT | See sample SQL and source implementation. |
| POLICY | SELECT | See sample SQL and source implementation. |
| NON_EMPR_ACCT | SELECT | See sample SQL and source implementation. |
Sample 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¶
Business rules¶
- 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.
- 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.
- 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.
- 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.
- 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.
- PROC-RULE-031: AR policies snap the calculated expiration month to quarter boundaries.
- 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