SAD_SL_03_ADRVW

(SQL View)
 Index Back

Studielink Msg 03 Address View

This view fetches the latest received address from the studielink using Message 03

SELECT A.EMPLID , B.SAD_SL_TRANS_INOUT , B.SAD_SL_TRANS_DTTM , B.SAD_SL_TRANS_SEQ , B.SAD_SL_INDEX_ID , B.SAD_SL_STDLNK_NBR , B.SAD_SL_ADDR_BLOCK , B.SAD_SL_ADDR_TYPE , B.SAD_SL_STREET , B.SAD_SL_APT_TYPE , B.SAD_SL_APT_PREFIX , B.SAD_SL_APT_NBR , B.SAD_SL_APT_SUFFIX , B.SAD_SL_POST_CODE , B.SAD_SL_CITY , B.SAD_SL_CNTRY , B.SAD_SL_POST_CD_CHK , C.SAD_SL_PHONE_NBR FROM PS_EXTERNAL_SYSTEM A , PS_SAD_SL_03_ADDR B , PS_SAD_SL_03 C WHERE A.EXTERNAL_SYSTEM_ID=B.SAD_SL_STDLNK_NBR AND B.SAD_SL_STDLNK_NBR=C.SAD_SL_STDLNK_NBR AND A.EXTERNAL_SYSTEM = 'SLN' AND B.SAD_SL_ADDR_TYPE = 'Mailing' AND B.SAD_SL_TRANS_DTTM = ( SELECT MAX(D.SAD_SL_TRANS_DTTM) FROM PS_SAD_SL_03_ADDR D ,PS_SAD_SL_TRNS_INF E WHERE D.SAD_SL_STDLNK_NBR=B.SAD_SL_STDLNK_NBR AND D.SAD_SL_TRANS_DTTM=E.SAD_SL_TRANS_DTTM AND E.SAD_SL_TRANS_STAT='P' )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SAD_SL_TRANS_INOUT Character(1) VARCHAR2(1) NOT NULL Transaction Direction
I=Inbound
O=Outbound
3 SAD_SL_TRANS_DTTM DateTime(26) TIMESTAMP Transaction DateTime
4 SAD_SL_TRANS_SEQ Number(3,0) SMALLINT NOT NULL Sequence Number
5 SAD_SL_INDEX_ID Character(40) VARCHAR2(40) NOT NULL Index ID
6 SAD_SL_STDLNK_NBR Character(9) VARCHAR2(9) NOT NULL Studielink Number
7 SAD_SL_ADDR_BLOCK Character(20) VARCHAR2(20) NOT NULL Address Block type
8 SAD_SL_ADDR_TYPE Character(9) VARCHAR2(9) NOT NULL Address Type
9 SAD_SL_STREET Character(80) VARCHAR2(80) NOT NULL Street Name
10 SAD_SL_APT_TYPE Character(35) VARCHAR2(35) NOT NULL Apartment Type
11 SAD_SL_APT_PREFIX Character(2) VARCHAR2(2) NOT NULL Apartment Number Prefix
12 SAD_SL_APT_NBR Character(5) VARCHAR2(5) NOT NULL Apartment Number
13 SAD_SL_APT_SUFFIX Character(5) VARCHAR2(5) NOT NULL Apartment Num Suffix
14 SAD_SL_POST_CODE Character(6) VARCHAR2(6) NOT NULL Postal Code
15 SAD_SL_CITY Character(30) VARCHAR2(30) NOT NULL City
16 SAD_SL_CNTRY Character(4) VARCHAR2(4) NOT NULL Country Code
17 SAD_SL_POST_CD_CHK Character(1) VARCHAR2(1) NOT NULL Postcodecheck
F=False
T=True
18 SAD_SL_PHONE_NBR Character(13) VARCHAR2(13) NOT NULL Phonenumber