ISIR_ADDR_VW

(SQL View)
 Index Back

ISIR Aid Address View

This view is used for selecting the address based upon the address usage for the address search panel. This view utilizes the address order table to find the matching address, if any.

SELECT A.EMPLID , C.ADDRESS_TYPE , C.ADDR_USAGE_ORDER , %subrec(ADDRESS_SBR, A) FROM PS_ADDRESSES A , PS_ADDR_ORDER_TBL C , PS_FA_PRCSDEMO_TBL D WHERE A.ADDRESS_TYPE = C.ADDRESS_TYPE AND A.EFFDT = ( SELECT MAX(A11.EFFDT) FROM PS_ADDRESSES A11 WHERE A11.EMPLID = A.EMPLID AND A11.ADDRESS_TYPE = A.ADDRESS_TYPE AND A11.EFFDT <=%CurrentDateIn) AND C.ADDR_USAGE = D.ADDR_USAGE AND D.FA_PROCESS_NAME = 'I' AND C.ADDR_USAGE_ORDER = ( SELECT MIN(C1.ADDR_USAGE_ORDER) FROM PS_ADDR_ORDER_TBL C1 WHERE C1.ADDR_USAGE = D.ADDR_USAGE AND C1.ADDRESS_TYPE IN ( SELECT A1.ADDRESS_TYPE FROM PS_ADDRESSES A1 WHERE A1.EMPLID = A.EMPLID AND A1.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_ADDRESSES A2 WHERE A2.EMPLID = A1.EMPLID AND A2.ADDRESS_TYPE = A1.ADDRESS_TYPE AND A2.EFFDT<=%CurrentDateIn) AND A1.EFF_STATUS = 'A'))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: STDNT_AID_SRCH

2 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

Prompt Table: ADDRESS_TYP_TBL

3 ADDR_USAGE_ORDER Number(4,0) SMALLINT NOT NULL Address Usage Order
4 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: SCC_CNT_ADFMTVW

5 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
6 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
7 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
8 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
9 CITY Character(30) VARCHAR2(30) NOT NULL City
10 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
11 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
12 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
13 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
14 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
15 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
16 COUNTY Character(30) VARCHAR2(30) NOT NULL County
17 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

18 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
19 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
20 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit