ISIR_ADDRESS_VW

(SQL View)
 Index Back

First Fin Aid Address

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 DISTINCT A.EMPLID , D.INSTITUTION , C.ADDRESS_TYPE , C.ADDR_USAGE_ORDER ,%subrec(ADDRESS_SBR,A) FROM PS_HCR_PER_ADDR_I A , PS_ADDR_ORDER_TBL C , PS_ISIR_CONTROL D , PS_FA_ADDR_USAGE E WHERE A.ADDRESS_TYPE = C.ADDRESS_TYPE AND A.EMPLID = D.EMPLID AND D.INSTITUTION = E.INSTITUTION AND D.OWNING_SCHOOL_CD = E.CPS_SCHOOL_CODE AND C.ADDR_USAGE = E.ADDR_USAGE AND D.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_ISIR_CONTROL H WHERE D.EMPLID = H.EMPLID AND D.INSTITUTION = H.INSTITUTION AND D.AID_YEAR = H.AID_YEAR AND H.EFFDT <= %CurrentDateIn ) AND E.EFFDT = ( SELECT MAX(G.EFFDT) FROM PS_FA_ADDR_USAGE G WHERE E.INSTITUTION = G.INSTITUTION AND E.CPS_SCHOOL_CODE =G.CPS_SCHOOL_CODE AND G.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_HCR_PER_ADDR_I X WHERE X.EMPLID = A.EMPLID AND X.ADDRESS_TYPE = A.ADDRESS_TYPE AND X.EFFDT<= %CurrentDateIn) AND C.ADDR_USAGE_ORDER= ( SELECT MIN(Y.ADDR_USAGE_ORDER) FROM PS_ADDR_ORDER_TBL Y , PS_HCR_PER_ADDR_I Z WHERE Y.ADDR_USAGE=C.ADDR_USAGE AND Y.ADDRESS_TYPE=Z.ADDRESS_TYPE AND Z.EMPLID=A.EMPLID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

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

Prompt Table: SCC_CNT_ADFMTVW

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

Prompt Table: STATE_TBL

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

Y/N Table Edit