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 | 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 | 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 | 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 | 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 | 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 | 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: <policy_name>. |
| PROC-RULE-028 | 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.
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.
Source Code¶
PL/SQL procedure signature
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
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¶
Sources¶
Business\SAIF.Business\PolicyServices.vb