---
title: "PK82001_CLAIM stored procedure documentation"
summary: Analysis of Oracle package/procedure including business rules and operations
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data.NHibernate
reference_type: package
source_schema: "WCIS_DBA"
source_name: "PK82001_CLAIM"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name PK82001_CLAIM --output analysis/oracle-source/pk82001-claim.sql"
retrieval_date: "2026-09-24"
---
# PK82001_CLAIM
## Overview
| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package (database object type: `PACKAGE`) |
| **Database** | Oracle (WCIS) |
| **Calling libraries** | `SAIF.Data.NHibernate` |
| **Calling classes** | MissingTimeLossRepository (⚠️ possibly dead code — the call would fail at runtime) |
## Purpose
This package implements: getMissingTLPayments — ⚠️ **not present** in the retrieved spec or body (package contains only getPersonAddress_GW, typeAddressExists_GW)
> **Scope:** `PK82001_CLAIM` is a large package (2 members). Only the 2 member(s) actually called by SAIF.Data.NHibernate (`getPersonAddress_GW, typeAddressExists_GW`) are reproduced below, extracted from the Legends CLI retrieval's per-member `codeItems`; the full package specification/body is not reproduced in this document. This is a deliberate scoping decision for readability, not a retrieval gap — the full source is available at the retrieval path in Sources.
## Procedures / Methods
### getPersonAddress_GW member
| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | MissingTimeLossRepository (⚠️ possibly dead code — the call would fail at runtime) |
#### Parameters for getPersonAddress_GW
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | person_address_cursor_GW | — | Not individually described in the retrieved source* |
| `inPersonID` | IN | NUMBER | — | Not individually described in the retrieved source* |
| `inAddrType` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inDate` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inRunId` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
### typeAddressExists_GW member
| Field | Value |
| --- | --- |
| **Returns** | scalar/function value* |
| **Called by** | MissingTimeLossRepository (⚠️ possibly dead code — the call would fail at runtime) |
#### Parameters for typeAddressExists_GW
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inPersonID` | IN | NUMBER | — | Not individually described in the retrieved source* |
| `inRunId` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inAddressType` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inPayFlag` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inMaxTSFlag` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inEffDtFlag` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |
| `inDate` | IN | DATE | SYSDATE | Not individually described in the retrieved source* |
## Source Code
getPersonAddress_GW (procedure)
```sql
PROCEDURE getPersonAddress_GW(recordset OUT person_address_cursor_GW,
inPersonID IN NUMBER,
inAddrType IN VARCHAR2,
inDate IN VARCHAR2,
inRunId IN VARCHAR2
)
AS
-- recordset ws area
v_isvalidaddress VARCHAR2(1);
v_person_name VARCHAR2(50);
v_address_line1 VARCHAR2(40);
v_address_line2 VARCHAR2(40);
v_address_line3 VARCHAR2(40);
v_address_line4 VARCHAR2(40);
v_address_line5 VARCHAR2(40);
v_updater_mailid VARCHAR2(30);
v_bank_acct_id VARCHAR2(50);
v_update_timestamp CHAR(26);
v_addressee VARCHAR2(50);
-- switches and flags
v_pay_flag VARCHAR2(1) := 'Y';
v_eff_date_flag VARCHAR2(1) := 'Y';
v_max_ts_flag VARCHAR2(1) := 'Y';
v_addresstypefound VARCHAR2(1) := 'N';
v_empty VARCHAR2(1) := 'N';
-- misc variables
v_addrtype VARCHAR2(1);
v_date DATE := SYSDATE;
v_address_code VARCHAR2(1);
v_person_id NUMBER;
v_guardian_name VARCHAR2(50);
v_last_name VARCHAR2(50);
v_gender VARCHAR2(1);
v_city_name VARCHAR2(60);
v_state_code VARCHAR2(2);
v_foreign_address_flag VARCHAR2(1);
v_zip_code VARCHAR2(5);
v_zip_code_ext VARCHAR2(4);
v_first_name VARCHAR2(50);
v_middle_name VARCHAR2(50) ;
v_name_extension_code VARCHAR2(5) ;
v_country_code VARCHAR2(10) ;
BEGIN
--Use the parameter date if passed otherwise use defaulted
--system date
IF inDate IS NOT NULL THEN
v_date := TO_DATE(inDate, 'MM/DD/YYYY');
END IF;
CASE inAddrType
--Request is for default correspondence address
WHEN '1' THEN
v_addrtype := 'G';
v_pay_flag := 'A';
v_max_ts_flag := 'Y';
v_eff_date_flag := 'A';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'G';
ELSE
v_addrtype := 'M';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'M';
ELSE
v_addrtype := 'H';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'H';
END IF;
END IF;
END IF;
--Request is for default payment address
WHEN '2' THEN
v_addrtype := 'G';
v_pay_flag := 'Y';
v_eff_date_flag := 'A';
v_max_ts_flag := 'Y';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'G';
ELSE
v_addrtype := 'P';
v_pay_flag := 'A';
v_eff_date_flag := 'Y';
v_max_ts_flag := 'A';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'P';
ELSE
v_addrtype := 'M';
v_pay_flag := 'A';
v_eff_date_flag := 'A';
v_max_ts_flag := 'Y';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'M';
ELSE
v_addrtype := 'H';
v_pay_flag := 'A';
v_eff_date_flag := 'A';
v_max_ts_flag := 'Y';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'H';
END IF;
END IF;
END IF;
END IF;
--Request is for payment address
WHEN 'P' THEN
v_addrtype := 'P';
v_pay_flag := 'A';
v_eff_date_flag := 'Y';
v_max_ts_flag := 'A';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := 'P';
END IF;
--Request is for home, mailing or guardian address
ELSE
v_addrtype := inaddrtype;
v_pay_flag := 'A';
v_eff_date_flag := 'A';
v_max_ts_flag := 'Y';
IF typeAddressExists_GW(inPersonID,inRunId,
v_addrtype,
v_pay_flag,
v_max_ts_flag,
v_eff_date_flag,
v_date) THEN
v_addresstypefound := v_addrtype;
END IF;
END CASE;
--If the default or requested address type was found
--continue processing for either guardian address or
--worker/beneficiary address
IF v_addresstypefound = v_addrtype THEN
v_person_id := inPersonID;
v_address_code := v_addrtype;
IF v_addrtype = 'G' THEN
v_address_code := 'M';
SELECT per.person_id
INTO v_person_id
FROM CLM_DBA.TEMP_DATA_PERSON_ADDRESS per
WHERE per.person_id = inPersonID
AND per.run_id = inRunId
AND per.person_code = 'G';
END IF;
SELECT DECODE(pra.death_date, NULL, RTRIM(pra.primary_first_name) || ' ' ||
DECODE(pra.primary_middle_name,'',NULL,' ',NULL,
SUBSTR(pra.primary_middle_name,1,1) || ' ') || RTRIM(pra.primary_last_name) || ' ' || RTRIM(pra.primary_name_extension_code),
'Estate of ' || RTRIM(pra.primary_first_name) || ' ' ||
DECODE(pra.primary_middle_name,'',NULL,' ',NULL,
SUBSTR(pra.primary_middle_name,1,1) || ' ') || RTRIM(pra.primary_last_name) || ' ' || RTRIM(pra.primary_name_extension_code)) AS person_name,
trim(pra.address_line1) address_line1,
trim(pra.address_line2) address_line2,
trim(pra.address_line3) address_line3,
DECODE(pra.foreign_address_flag,'Y', pra.address_line4,
trim(pra.city_name) || ', ' || trim(pra.STATE_CODE) || ' ' || Decode(trim(pra.ZIP_CODE), '0', '', trim(to_char(pra.ZIP_CODE, 'FM00000')))
|| DECODE(TRIM(pra.zip_code_ext),NULL,'',' ','','','','-' || pra.zip_code_ext)) address_line4,
pra.update_user,
pra.direct_deposit_bank_acct_id,
pra.update_date,
pra.GENDER_CODE,
RTRIM(pra.primary_last_name),
trim(pra.city_name),
trim(pra.state_code),
pra.foreign_address_flag,
pra.zip_code,
pra.zip_code_ext,
pra.primary_first_name,
pra.primary_middle_name,
pra.primary_name_extension_code,
pra.country_code
INTO v_person_name,
v_address_line1,
v_address_line2,
v_address_line3,
v_address_line4,
v_updater_mailid,
v_bank_acct_id,
v_update_timestamp,
v_gender,
v_last_name,
v_city_name,
v_state_code,
v_foreign_address_flag,
v_zip_code,
v_zip_code_ext,
v_first_name,
v_middle_name,
v_name_extension_code,
v_country_code
FROM
CLM_DBA.TEMP_DATA_PERSON_ADDRESS pra
WHERE pra.person_id = v_person_id
AND pra.run_id = inRunId
AND pra.ADDRESS_CODE = v_address_code
AND (RTRIM(pra.address_line1) > ' '
OR RTRIM(pra.address_line2) > ' '
OR RTRIM(pra.address_line3) > ' '
OR RTRIM(pra.address_line4) > ' '
OR RTRIM(pra.city_name) > ' ')
AND (pra.update_date =
(SELECT MAX(pra2.update_date)
FROM CLM_DBA.TEMP_DATA_PERSON_ADDRESS pra2
WHERE pra2.person_id = v_person_id
AND pra.run_id = inRunId
AND pra2.ADDRESS_CODE = v_address_code)
OR v_max_ts_flag = 'A')
AND (pra.payment_flag_code = 'Y'
OR v_pay_flag = 'A')
AND (((v_date BETWEEN pra.eff_date
AND pra.exp_Date )
OR (v_date >= pra.eff_date
AND pra.exp_Date IS NULL)
OR v_eff_date_flag = 'A'));
--If the address retrieval was for guardian, then perform
--a separate retrieval for the beneficiary/worker name
IF v_addrtype = 'G' THEN
v_address_line5 := v_address_line4;
v_address_line4 := v_address_line3;
v_address_line3 := v_address_line2;
v_address_line2 := v_address_line1;
v_bank_acct_id := '';
SELECT 'For: ' || RTRIM(per.primary_first_name) || ' ' ||
DECODE(per.primary_middle_name,'',NULL,' ',NULL,
SUBSTR(per.primary_middle_name,1,1) || ' ') || RTRIM(per.primary_last_name) || ' ' || RTRIM(per.primary_name_extension_code) AS person_name
INTO v_address_line1
FROM CLM_DBA.TEMP_DATA_PERSON_ADDRESS per
WHERE per.person_id = inPersonID
AND per.run_id = inRunId;
END IF;
--This routine is to bump up non-blank address lines into blank ones
IF v_address_line1 IS NULL OR v_address_line1 = '' THEN
IF v_address_line2 > ' ' THEN
v_address_line1 := v_address_line2;
v_address_line2 := v_address_line3;
v_address_line3 := v_address_line4;
v_address_line4 := v_address_line5;
v_address_line5 := '';
ELSE
IF v_address_line3 > ' ' THEN
v_address_line1 := v_address_line3;
v_address_line2 := v_address_line4;
v_address_line3 := v_address_line5;
v_address_line4 := '';
v_address_line5 := '';
ELSE
IF v_address_line4 > ' ' THEN
v_address_line1 := v_address_line4;
v_address_line2 := v_address_line5;
v_address_line3 := '';
v_address_line4 := '';
v_address_line5 := '';
ELSE
IF v_address_line5 > ' ' THEN
v_address_line1 := v_address_line5;
v_address_line2 := '';
v_address_line3 := '';
v_address_line4 := '';
v_address_line5 := '';
END IF;
END IF;
END IF;
END IF;
END IF;
IF v_address_line2 IS NULL OR v_address_line2 = '' THEN
IF v_address_line3 > ' ' THEN
v_address_line2 := v_address_line3;
v_address_line3 := v_address_line4;
v_address_line4 := v_address_line5;
v_address_line5 := '';
ELSE
IF v_address_line4 > ' ' THEN
v_address_line2 := v_address_line4;
v_address_line3 := v_address_line5;
v_address_line4 := '';
v_address_line5 := '';
ELSE
IF v_address_line5 > ' ' THEN
v_address_line2 := v_address_line5;
v_address_line3 := '';
v_address_line4 := '';
v_address_line5 := '';
END IF;
END IF;
END IF;
END IF;
IF v_address_line3 IS NULL OR v_address_line3 = '' THEN
IF v_address_line4 > ' ' THEN
v_address_line3 := v_address_line4;
v_address_line4 := v_address_line5;
v_address_line5 := '';
ELSE
IF v_address_line5 > ' ' THEN
v_address_line3 := v_address_line5;
v_address_line4 := '';
v_address_line5 := '';
END IF;
END IF;
END IF;
IF v_address_line4 IS NULL OR v_address_line4 = '' THEN
IF v_address_line5 > ' ' THEN
v_address_line4 := v_address_line5;
v_address_line5 := '';
END IF;
END IF;
CASE v_gender
WHEN 'M' THEN v_addressee := 'Mr. ' || v_last_name;
WHEN 'F' THEN v_addressee := 'Ms. ' || v_last_name;
ELSE v_addressee := 'M. ' || v_last_name;
END CASE;
END IF;
--Create the recordset to be returned
--Note: the column names must match the .net dataset columns
-- address line 1 and 2 are checked since the city/state zip line counts as 1 address line
v_isvalidaddress := 'N';
IF v_person_name > ' ' and v_address_line1 > ' ' and v_address_line2 > ' ' then
IF v_foreign_address_flag = 'Y' then
v_isvalidaddress := 'Y';
ELSE
IF v_city_name > ' ' and v_state_code > ' ' then
v_isvalidaddress := 'Y';
END IF;
END IF;
END IF;
IF v_addresstypefound = v_addrtype THEN
OPEN recordset FOR SELECT
v_isvalidaddress AS isvalidaddress,
v_person_name AS person_name,
v_address_line1 AS address_line1,
v_address_line2 AS address_line2,
v_address_line3 AS address_line3,
v_address_line4 AS address_line4,
v_address_line5 AS address_line5,
v_updater_mailid AS updater_mailid,
v_bank_acct_id AS bank_acct_id,
v_update_timestamp AS update_timestamp,
v_addressee AS addressee,
v_foreign_address_flag as isforeignaddress,
v_city_name AS city,
v_state_code AS state_code,
v_zip_code AS zip_code,
v_zip_code_ext AS zip_code_ext,
v_first_name as first_name,
v_middle_name as middle_name,
v_name_extension_code as name_extension_code,
v_last_name as last_name,
v_country_code as country_code
FROM dual;
ELSE
OPEN recordset FOR SELECT
v_isvalidaddress AS isvalidaddress,
v_person_name AS person_name,
v_address_line1 AS address_line1,
v_address_line2 AS address_line2,
v_address_line3 AS address_line3,
v_address_line4 AS address_line4,
v_address_line5 AS address_line5,
v_updater_mailid AS updater_mailid,
v_bank_acct_id AS bank_acct_id,
v_update_timestamp AS update_timestamp,
v_addressee AS addressee,
v_foreign_address_flag as isforeignaddress,
v_city_name AS city,
v_state_code AS state_code,
v_zip_code AS zip_code,
v_zip_code_ext AS zip_code_ext,
v_first_name as first_name,
v_middle_name as middle_name,
v_name_extension_code as name_extension_code,
v_last_name as last_name,
v_country_code as country_code
FROM dual
WHERE v_EMPTY = 'Y';
END IF;
END getPersonAddress_GW;
-- Does this Person Address Type Exist ?
```
typeAddressExists_GW (function)
```sql
FUNCTION typeAddressExists_GW(inPersonID IN NUMBER,inRunId IN VARCHAR2,
inAddressType IN VARCHAR2,
inPayFlag IN VARCHAR2,
inMaxTSFlag IN VARCHAR2,
inEffDtFlag IN VARCHAR2,
inDate IN DATE := SYSDATE)
RETURN BOOLEAN
AS
v_address_code VARCHAR2(1);
v_date DATE := SYSDATE;
v_cnt NUMBER := 0;
BEGIN
--Used the parameter date if passed otherwise
--use the defaulted system date
IF inDate IS NOT NULL THEN
v_date := inDate;
END IF;
```
## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
> **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
- Oracle DBA_SOURCE: `WCIS_DBA.PK82001_CLAIM`
- Legends CLI query executed 2026-09-24: `legends oracle query --name PK82001_CLAIM --output analysis/oracle-source/pk82001-claim.sql`
- Calling code: `Data/SAIF.Data.NHibernate/Mappings/Claims/Information/MissingTimeLoss.hbm.xml`
- Calling code: `Data/SAIF.Data.NHibernate/Repositories/Claims/Information/MissingTimeLossRepository.cs`