FA_ADDR_VW(SQL View) |
Index Back |
---|---|
Financial Aid Address ViewThis 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 , E.INSTITUTION , E.CPS_SCHOOL_CODE , C.ADDRESS_TYPE , C.ADDR_USAGE_ORDER ,%subrec(ADDRESS_SBR,A) FROM PS_HCR_PER_ADDR_I A , PS_ADDR_ORDER_TBL C , PS_FA_ADDR_USAGE E WHERE A.ADDRESS_TYPE = C.ADDRESS_TYPE AND C.ADDR_USAGE = E.ADDR_USAGE 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 G.EFF_STATUS = 'A') 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 X.EFF_STATUS = 'A') AND C.ADDR_USAGE_ORDER = ( SELECT MIN(Y.ADDR_USAGE_ORDER) FROM PS_ADDR_ORDER_TBL Y WHERE Y.ADDR_USAGE = C.ADDR_USAGE AND Y.ADDRESS_TYPE IN ( SELECT DISTINCT(T.ADDRESS_TYPE) FROM PS_HCR_PER_ADDR_I T WHERE A.EMPLID = T.EMPLID AND T.EFFDT <=%CurrentDateIn AND T.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 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL |
Academic Institution
Prompt Table: INSTITUTION_TBL |
3 | CPS_SCHOOL_CODE | Character(6) | VARCHAR2(6) NOT NULL | CPS School Code. Also known as Title IV School Code |
4 | ADDRESS_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
Address Type
Prompt Table: ADDRESS_TYP_TBL |
5 | ADDR_USAGE_ORDER | Number(4,0) | SMALLINT NOT NULL | Address Usage Order |
6 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: SCC_CNT_ADFMTVW |
7 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
8 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
9 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
10 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
11 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
12 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
13 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
14 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat Reference WW=House Trailer Reference |
15 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
16 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
17 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
18 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
19 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: STATE_TBL |
20 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
21 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
22 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |