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 ?