pk82001_claim

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	Oracle
Calling libraries	SAIF.Business
Calling classes	ClaimServices

Purpose

Claim-address package that resolves person mailing or payment addresses using date-sensitive fallback logic.

Procedures / Methods

getPersonAddress_GW

Field	Value
Returns	A REF CURSOR recordset containing the resolved address fields, or an empty cursor when no qualifying address exists.
Called by	ClaimServices.GetBeneficiaryAddresses, ClaimServices.GetWorkerAddress

Description: Resolves person mailing, payment, guardian, and home addresses using staged person-address rows plus fallback rules.

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	person_address_cursor_GW	—	Result cursor returned to the caller.
inPersonID	IN	NUMBER	—	Person identifier used by the package.
inAddrType	IN	VARCHAR2	—	Requested address type code.
inDate	IN	VARCHAR2	—	Effective date used by the package.
inRunId	IN	VARCHAR2	—	Run identifier used to read staged person-address rows.

Business Rules

Rules extracted from this procedure's verified logic:

Rule ID	Rule	Condition	Action
PROC-RULE-005	The procedure uses the caller-supplied date when one is provided.	inDate IS NOT NULL.	Convert inDate with TO_DATE(inDate, 'MM/DD/YYYY') and assign it to v_date.
PROC-RULE-006	Default correspondence address lookup falls back from guardian to mailing to home address.	inAddrType = '1'.	Test address existence for G, then M, then H, and use the first qualifying type.
PROC-RULE-007	Default payment address lookup falls back from guardian to payment to mailing to home address.	inAddrType = '2'.	Test address existence for G, then P, then M, then H, and use the first qualifying type.
PROC-RULE-008	Explicit payment requests only use payment addresses.	inAddrType = 'P' and the payment address exists.	Select address type P.
PROC-RULE-009	Other requests use the requested address type directly.	inAddrType is not 1, 2, or P, and typeAddressExists_GW returns true.	Keep the requested address type as v_addresstypefound.
PROC-RULE-010	Guardian correspondence resolves the guardian mailing address and inserts a For: line for the worker/beneficiary.	v_addrtype = 'G' after address selection.	Resolve the guardian person_id, switch the address code to M, shift address lines, and populate v_address_line1 with For: <name>.
PROC-RULE-011	Deceased people are addressed as estates.	pra.death_date is not null in the address lookup row.	Prefix the person name with Estate of.
PROC-RULE-012	Blank address lines are compacted upward to eliminate gaps.	Any of v_address_line1 through v_address_line4 is blank while later lines contain data.	Shift lower address lines into the first available blank slot.
PROC-RULE-013	Addressee salutation is gender-based.	v_gender = 'M', v_gender = 'F', or any other value.	Set v_addressee to Mr., Ms., or M. plus the last name.
PROC-RULE-014	An address is valid only when it has a person name, two populated address lines, and either foreign-address status or a city/state pair.	v_person_name, v_address_line1, and v_address_line2 are nonblank, plus v_foreign_address_flag = 'Y' or both v_city_name and v_state_code are nonblank.	Set v_isvalidaddress to Y.
PROC-RULE-015	Requests with no qualifying address return no rows.	v_addresstypefound <> v_addrtype.	Open the DUAL cursor with WHERE v_EMPTY = 'Y' to return an empty result set.

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this procedure:

Operation	Description	Trigger
OP-005	Evaluates address availability through typeAddressExists_GW using payment, date, and latest-timestamp filters.	Each address-type fallback branch calls the helper.
OP-006	Resolves the guardian person_id when guardian correspondence is selected.	Guardian address path (v_addrtype = 'G').
OP-007	Selects the latest qualifying person-address row and computes the returned name and address fields.	A qualifying address type is found.
OP-008	Loads the worker/beneficiary name for guardian mailings and shifts address lines to insert the For: line.	Guardian address path (v_addrtype = 'G').
OP-009	Returns the assembled address data through a cursor backed by DUAL.	v_addresstypefound = v_addrtype.
OP-010	Returns an empty cursor when no qualifying address is available.	v_addresstypefound <> v_addrtype.

These operations are also listed in operations-processes.md.

Source Code

PL/SQL package specification

PROCEDURE getPersonAddress_GW(recordset OUT person_address_cursor_GW ,
           inPersonID IN NUMBER,
           inAddrType IN VARCHAR2,
           inDate IN VARCHAR2,
           inRunId IN VARCHAR2);

PL/SQL package/procedure body

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;

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;

--The guardian's id must first be obtained from the person table
--by matching on the worker id and person code of 'G''
IF inAddressType = 'G' THEN
   v_address_code := 'M';
   SELECT COUNT(*)
    INTO v_cnt
    FROM
         CLM_DBA.TEMP_DATA_PERSON_ADDRESS pra
   WHERE pra.worker_id = inPersonId
     AND pra.person_code   = 'G'
        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 = inPersonId
              AND pra2.person_code   = 'G'
                AND pra2.run_id = inRunId
              AND pra2.ADDRESS_CODE  = v_address_code)
                  OR  inMaxTSFlag = 'A')
               AND (pra.payment_flag_code = 'Y'
                  OR  inPayFlag = '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  inEffDTFlag = 'A');

ELSE

--otherwise don't need to select from person table
v_address_code := inAddressType;
SELECT COUNT(*)
    INTO v_cnt
    FROM CLM_DBA.TEMP_DATA_PERSON_ADDRESS pra
   WHERE pra.person_id     = inPersonId
     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     = inPersonId
              AND pra2.run_id = inRunId
              AND pra2.ADDRESS_CODE  = v_address_code)
              OR  inMaxTSFlag = 'A')
     AND (pra.payment_flag_code = 'Y'
            OR  inPayFlag = '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  inEffDTFlag = 'A');
END IF;

IF v_cnt > 0 THEN
  RETURN TRUE;
  ELSE
  RETURN FALSE;
END IF;

END typeAddressExists_GW;

Related Documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

Sources

• Business\SAIF.Business\ClaimServices.vb