--- title: "sp62087_get_policy_address stored procedure documentation" summary: "Analysis of Oracle package/procedure including business rules and operations." status: draft owner: "SAIF" last_reviewed: "2026-07-24" source_schema: "`WCIS_DBA`" source_name: "sp62087_get_policy_address" source_type: "`Standalone procedure`" oracle_status: "found" library: SAIF.Business reference_type: package --- # sp62087_get_policy_address ## Overview | Field | Value | | --- | --- | | **Schema** | `WCIS_DBA` | | **Type** | `Standalone procedure` | | **Database** | Oracle | | **Calling libraries** | `SAIF.Business` | | **Calling classes** | `PolicyServices` | ## Purpose Policy-address procedure that resolves mailing and correspondence addresses for a policy and correspondence type. ## Procedures / Methods ### sp62087_get_policy_address | Field | Value | | --- | --- | | **Returns** | The procedure fills the `IN OUT` address fields with the resolved mailing data or raises an application error when no qualifying location exists. | | **Called by** | `PolicyServices.getPolicyAddress` | **Description:** Resolves policy correspondence addresses, barcode data, and DBA/country output fields from policy-location rows. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_policy_num` | IN | NUMBER | — | Policy number to look up. | | `in_pol_per_eff_date` | IN | VARCHAR2 | — | Policy-period effective date supplied by the caller. | | `process_date` | IN | DATE | — | Processing date used to choose active versus expired locations. | | `in_correspond_type` | IN | VARCHAR2 | — | Correspondence code that controls payroll-mailing and barcode rules. | | `out_policy_name` | IN OUT | VARCHAR2 | — | Returned policy or mailing-name text. | | `out_loc_addr1` | IN OUT | VARCHAR2 | — | Returned address line 1. | | `out_loc_addr2` | IN OUT | VARCHAR2 | — | Returned address line 2. | | `out_loc_addr3` | IN OUT | VARCHAR2 | — | Returned address line 3. | | `out_loc_city` | IN OUT | VARCHAR2 | — | Returned city name. | | `out_loc_county` | IN OUT | VARCHAR2 | — | Returned county value. | | `out_loc_state` | IN OUT | VARCHAR2 | — | Returned state code. | | `out_loc_country` | IN OUT | VARCHAR2 | — | Returned country name. | | `out_loc_country_cd` | IN OUT | VARCHAR2 | — | Returned country code. | | `out_loc_zip` | IN OUT | VARCHAR2 | — | Returned ZIP code. | | `out_loc_zip4` | IN OUT | VARCHAR2 | — | Returned ZIP+4 extension. | | `out_loc_phone` | IN OUT | VARCHAR2 | — | Returned phone number. | | `out_loc_phone_ext` | IN OUT | VARCHAR2 | — | Returned phone extension. | | `out_loc_fax` | IN OUT | VARCHAR2 | — | Returned fax number. | | `bar_code` | IN OUT | VARCHAR2 | — | Returned barcode or email-address output. | | `out_legal_name` | IN OUT | VARCHAR2 | — | Returned legal-name value. | | `out_dba_name` | IN OUT | VARCHAR2 | — | Returned DBA line when the legal and policy names differ. | ## Business Rules Rules extracted from this procedure's verified logic: | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | [PROC-RULE-022](../business-rules.md#proc-rule-022-uw312-and-uw310-correspondence-prefers-the-payroll-mailing-address-before-standard-policy-locations) | UW312 and UW310 correspondence prefers the payroll mailing address before standard policy locations. | `in_correspond_type = 'UW312'` or `in_correspond_type = 'UW310'`. | Query `special_loc` first, then fall back to `loc`, then `EXP`. | | [PROC-RULE-023](../business-rules.md#proc-rule-023-other-correspondence-uses-the-standard-policy-location-first-and-then-the-latest-eligible-expired-policy-location) | Other correspondence uses the standard policy location first and then the latest eligible expired policy location. | Any other `in_correspond_type`. | Query `loc`, then `EXP`, and raise `no_policy_location` if neither cursor returns a row. | | [PROC-RULE-024](../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) | 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. | `process_date < TO_DATE(in_pol_per_eff_date, 'DD-MON-YYYY')` versus `process_date >= TO_DATE(in_pol_per_eff_date, 'DD-MON-YYYY')`. | Apply the corresponding effective/expiration-date window in the `loc` and `special_loc` cursors. | | [PROC-RULE-025](../business-rules.md#proc-rule-025-barcode-output-switches-to-email-address-for-uw560-correspondence) | Barcode output switches to email address for UW560 correspondence. | `in_correspond_type = 'UW560'`. | Set `bar_code` from `email_addr` instead of `usps_barcode`. | | [PROC-RULE-026](../business-rules.md#proc-rule-026-missing-country-codes-default-to-the-united-states-before-country-name-lookup) | Missing country codes default to the United States before country-name lookup. | `out_loc_country_cd IS NULL`. | Assign `v_country_cd := 'US'`. | | [PROC-RULE-027](../business-rules.md#proc-rule-027-the-dba-line-is-only-emitted-when-the-legal-name-differs-from-the-policy-name) | The DBA line is only emitted when the legal name differs from the policy name. | `out_legal_name = out_policy_name` versus different values. | Leave `out_dba_name` blank when they match; otherwise set `out_dba_name` to `DBA: `. | | [PROC-RULE-028](../business-rules.md#proc-rule-028-missing-policy-location-data-is-surfaced-as-an-application-error-after-blanking-all-output-fields) | Missing policy-location data is surfaced as an application error after blanking all output fields. | `no_policy_location` exception is raised. | Clear the output variables and raise `RAISE_APPLICATION_ERROR(-20001, 'No policy_location exists for this policy.')`. | > These rules are documented in [business-rules.md](../business-rules.md). ## Operations Operations and processes implemented in this procedure: | Operation | Description | Trigger | | --- | --- | --- | | OP-020 | Loads candidate policy-location rows from the standard, payroll-mailing, and expired-location cursors. | `PolicyServices.getPolicyAddress` invokes the procedure. | | OP-021 | Looks up the display country name for the selected country code. | After a location row is chosen. | | OP-022 | Raises application errors for missing policy locations and unexpected failures. | `no_policy_location` or `WHEN OTHERS` exception paths. | > These operations are also listed in [operations-processes.md](../operations-processes.md). ## Source Code
PL/SQL procedure signature ```sql PROCEDURE sp62087_get_policy_address (in_policy_num IN NUMBER ,in_pol_per_eff_date IN VARCHAR2 ,process_date IN DATE ,in_correspond_type IN VARCHAR2 ,out_policy_name IN OUT VARCHAR2 ,out_loc_addr1 IN OUT VARCHAR2 ,out_loc_addr2 IN OUT VARCHAR2 ,out_loc_addr3 IN OUT VARCHAR2 ,out_loc_city IN OUT VARCHAR2 ,out_loc_county IN OUT VARCHAR2 ,out_loc_state IN OUT VARCHAR2 ,out_loc_country IN OUT VARCHAR2 ,out_loc_country_cd IN OUT VARCHAR2 ,out_loc_zip IN OUT VARCHAR2 ,out_loc_zip4 IN OUT VARCHAR2 ,out_loc_phone IN OUT VARCHAR2 ,out_loc_phone_ext IN OUT VARCHAR2 ,out_loc_fax IN OUT VARCHAR2 ,bar_code IN OUT VARCHAR2 ,out_legal_name IN OUT VARCHAR2 ,out_dba_name IN OUT VARCHAR2 ) ```
PL/SQL package/procedure body ```sql PROCEDURE sp62087_get_policy_address (in_policy_num IN NUMBER ,in_pol_per_eff_date IN VARCHAR2 ,process_date IN DATE ,in_correspond_type IN VARCHAR2 ,out_policy_name IN OUT VARCHAR2 ,out_loc_addr1 IN OUT VARCHAR2 ,out_loc_addr2 IN OUT VARCHAR2 ,out_loc_addr3 IN OUT VARCHAR2 ,out_loc_city IN OUT VARCHAR2 ,out_loc_county IN OUT VARCHAR2 ,out_loc_state IN OUT VARCHAR2 ,out_loc_country IN OUT VARCHAR2 ,out_loc_country_cd IN OUT VARCHAR2 ,out_loc_zip IN OUT VARCHAR2 ,out_loc_zip4 IN OUT VARCHAR2 ,out_loc_phone IN OUT VARCHAR2 ,out_loc_phone_ext IN OUT VARCHAR2 ,out_loc_fax IN OUT VARCHAR2 ,bar_code IN OUT VARCHAR2 ,out_legal_name IN OUT VARCHAR2 ,out_dba_name IN OUT VARCHAR2 ) AS v_country_cd LOCATION.loc_country%TYPE; /* DAVMEA 08/10/1999 Added foreign address logic BRICOT 11/01/2000 added order by to exp cursor so that null location exp date will sort to top ELAWOH 06/20/2001 Modified all cursors to extract Policy_name instead of location_name as policy_name now holds the location_name value and location_name will no longer be used. Included legal_name. ELAWOH 06/25/2001 rel 5.8.1 csr4 Modified to include DBA_name to be used as second line of mailing label where appropriate BRICOT 07/02/2004 added email_addr and code for UW560 valrey 06/06/2005 When getting the payroll adress and it's expired look at the location address. Removed looking at special_exp cursor. MELHIL 02/18/2020 Removed commented out code around special_exp Cursor no longer used Added logic to exclude flat canceled address when finding the address with the max effective date */ CURSOR loc IS SELECT b.policy_name ,a.loc_addr1 ,a.loc_addr2 ,a.loc_addr3 ,a.loc_city ,a.loc_county ,a.loc_state ,a.loc_country ,a.loc_zip ,a.loc_zip4 ,a.loc_phone ,a.loc_phone_ext ,a.loc_fax ,a.usps_barcode ,b.legal_name ,a.email_addr FROM LOCATION a, POLICY b WHERE a.location_code = 'POL' AND a.policy_num = in_policy_num AND ((process_date < TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND a.loc_eff_date <= TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND NVL(a.loc_exp_date,TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') + 1) > TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY')) OR (process_date >= TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND process_date >= a.loc_eff_date AND process_date <= NVL(a.loc_exp_date,process_date))) AND b.policy_num = a.policy_num ORDER BY a.loc_eff_date DESC; CURSOR special_loc IS SELECT b.policy_name ,a.loc_addr1 ,a.loc_addr2 ,a.loc_addr3 ,a.loc_city ,a.loc_county ,a.loc_state ,a.loc_country ,a.loc_zip ,a.loc_zip4 ,a.loc_phone ,a.loc_phone_ext ,a.loc_fax ,a.usps_barcode ,b.legal_name ,a.email_addr FROM LOCATION a, POLICY b WHERE a.location_code = 'PAY' AND a.policy_num = in_policy_num AND a.mail_addr_flag = 'Y' AND ((process_date < TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND a.loc_eff_date <= TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND NVL(a.loc_exp_date,TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') + 1) > TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY')) OR (process_date >= TO_DATE(in_pol_per_eff_date,'DD-MON-YYYY') AND process_date >= a.loc_eff_date AND process_date <= NVL(a.loc_exp_date,process_date))) AND b.policy_num = a.policy_num ORDER BY a.loc_eff_date DESC; CURSOR EXP IS SELECT b.policy_name ,a.loc_addr1 ,a.loc_addr2 ,a.loc_addr3 ,a.loc_city ,a.loc_county ,a.loc_state ,a.loc_country ,a.loc_zip ,a.loc_zip4 ,a.loc_phone ,a.loc_phone_ext ,a.loc_fax ,a.usps_barcode ,b.legal_name ,a.email_addr FROM LOCATION a, POLICY b WHERE a.loc_eff_date = ( SELECT MAX(c.loc_eff_date) FROM LOCATION c WHERE c.location_code = 'POL' AND c.policy_num = in_policy_num AND c.loc_eff_date <> NVL(c.loc_exp_date,c.loc_eff_date + 1) /* exclude flat cancelled addresses from being selected */ ) AND a.location_code = 'POL' AND a.policy_num = in_policy_num AND b.policy_num = a.policy_num ORDER BY a.loc_exp_date DESC; CURSOR get_country_cur IS SELECT country_name FROM country WHERE country = v_country_cd; get_country_rec get_country_cur%ROWTYPE; loc_rec loc%ROWTYPE; no_policy_location EXCEPTION; BEGIN /* if the correspond type (which is actually the endorse code */ /* from the endorsement table) is of a certain type, then special */ /* processing is needed because a different cursor must be used */ IF in_correspond_type = 'UW312' OR in_correspond_type = 'UW310' THEN OPEN special_loc; FETCH special_loc INTO loc_rec; IF special_loc%NOTFOUND THEN -- /* if no records were found using the special cursors, then */ /* obtain the information using the original cursors */ OPEN loc; FETCH loc INTO loc_rec; IF loc%NOTFOUND THEN OPEN EXP; FETCH EXP INTO loc_rec; IF EXP%NOTFOUND THEN RAISE no_policy_location; END IF; CLOSE EXP; END IF; END IF; ELSE OPEN loc; FETCH loc INTO loc_rec; IF loc%NOTFOUND THEN OPEN EXP; FETCH EXP INTO loc_rec; IF EXP%NOTFOUND THEN RAISE no_policy_location; END IF; CLOSE EXP; END IF; END IF; /* set out variable values */ out_policy_name := loc_rec.policy_name; out_loc_addr1 := loc_rec.loc_addr1; out_loc_addr2 := loc_rec.loc_addr2; out_loc_addr3 := loc_rec.loc_addr3; out_loc_city := loc_rec.loc_city; out_loc_state := loc_rec.loc_state; out_loc_country_cd := loc_rec.loc_country; out_loc_zip := loc_rec.loc_zip; out_loc_zip4 := loc_rec.loc_zip4; out_loc_phone := loc_rec.loc_phone; out_loc_phone_ext := loc_rec.loc_phone_ext; out_loc_fax := loc_rec.loc_fax; IF in_correspond_type = 'UW560' THEN bar_code := loc_rec.email_addr; ELSE bar_code := loc_rec.usps_barcode; END IF; out_legal_name := loc_rec.legal_name; out_dba_name := ''; IF out_loc_country_cd IS NULL THEN v_country_cd := 'US'; ELSE v_country_cd := out_loc_country_cd; END IF; OPEN get_country_cur; FETCH get_country_cur INTO get_country_rec; CLOSE get_country_cur; out_loc_country := get_country_rec.country_name; IF out_legal_name = out_policy_name THEN out_dba_name := ''; ELSE out_dba_name := 'DBA: '||loc_rec.policy_name; END IF; IF special_loc%ISOPEN THEN CLOSE special_loc; END IF; IF loc%ISOPEN THEN CLOSE loc; END IF; EXCEPTION WHEN no_policy_location THEN /* set out variable values to empty */ out_policy_name := ''; out_loc_addr1 := ''; out_loc_addr2 := ''; out_loc_addr3 := ''; out_loc_city := ''; out_loc_state := ''; out_loc_county := ''; 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 := ''; RAISE_APPLICATION_ERROR (-20001,'No policy_location exists for this policy.'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20002,'SP62087 '||SQLERRM); END sp62087_get_policy_address; ```
## Related Documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) ## Sources - `Business\SAIF.Business\PolicyServices.vb`