SAD_SL_ADDR_VW(SQL View) |
Index Back |
|---|---|
Studielink 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 , D.SAD_SL_ADDR_XLAT , X.XLATLONGNAME , C.ADDRESS_TYPE , C.ADDR_USAGE_ORDER , %subrec(ADDRESS_SBR, A) FROM PS_ADDRESSES A , PS_ADDR_ORDER_TBL C , PS_SAD_SL_AD_USAGE D , PSXLATITEM X 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 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')) AND X.FIELDNAME = 'SAD_SL_ADDR_XLAT' AND X.FIELDVALUE = D.SAD_SL_ADDR_XLAT |
| # | 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 | SAD_SL_ADDR_XLAT |
Character(4) | VARCHAR2(4) NOT NULL |
Used to allow prompting for valid Studielink address type values.
BILL=Billing LIVE=Living MAIL=Mailing PERM=Permanent |
| 3 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
| 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 |