SAD_SL_06_VW

(SQL View)
 Index Back

Studielink Msg 06 Stage View

This view selects the latest transmitted address with the Studielink.

SELECT B.EMPLID ,A.SAD_SL_TRANS_INOUT , A.SAD_SL_TRANS_DTTM , A.SAD_SL_TRANS_SEQ , A.SAD_SL_INDEX_ID , A.SAD_SL_STDLNK_NBR , A.SAD_SL_NAME_TEXT , A.SAD_SL_ADDR_TYPE , A.SAD_SL_STREET , A.SAD_SL_APT_TYPE , A.SAD_SL_APT_PREFIX , A.SAD_SL_APT_NBR , A.SAD_SL_APT_SUFFIX , A.SAD_SL_POST_CODE , A.SAD_SL_CITY , A.SAD_SL_CNTRY , A.SAD_SL_FORGN_TYPE , A.SAD_SL_FORGN_ADDR1 , A.SAD_SL_FORGN_ADDR2 , A.SAD_SL_FORGN_ADDR3 , A.SAD_SL_FORGN_CNTRY , A.SAD_SL_O_NBR , A.SAD_SL_CIOP_NBR , A.SAD_SL_SOFI_NBR , A.SAD_SL_POST_CD_CHK FROM PS_SAD_SL_06 A , PS_EXTERNAL_SYSTEM B WHERE A.SAD_SL_STDLNK_NBR = B.EXTERNAL_SYSTEM_ID AND B.EXTERNAL_SYSTEM = 'SLN' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EXTERNAL_SYSTEM B1 WHERE B.EMPLID = B1.EMPLID AND B.EXTERNAL_SYSTEM = B1.EXTERNAL_SYSTEM AND B1.EFFDT <= %CurrentDateIn) AND A.SAD_SL_TRANS_DTTM =( SELECT MAX(D.SAD_SL_TRANS_DTTM) FROM PS_SAD_SL_06 D ,PS_SAD_SL_TRNS_INF E WHERE D.SAD_SL_STDLNK_NBR= A.SAD_SL_STDLNK_NBR AND D.SAD_SL_TRANS_DTTM=E.SAD_SL_TRANS_DTTM AND E.SAD_SL_TRANS_STAT='P' )

  • Parent record: SAD_SL_TRNS_INF

    • # 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_NAME_TEXT Character(220) VARCHAR2(220) NOT NULL Merged name
    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_FORGN_TYPE Character(9) VARCHAR2(9) NOT NULL Foreign Address Type
    18 SAD_SL_FORGN_ADDR1 Character(35) VARCHAR2(35) NOT NULL Street Name
    19 SAD_SL_FORGN_ADDR2 Character(35) VARCHAR2(35) NOT NULL City
    20 SAD_SL_FORGN_ADDR3 Character(35) VARCHAR2(35) NOT NULL Additional Address
    21 SAD_SL_FORGN_CNTRY Character(4) VARCHAR2(4) NOT NULL Foreign Address Country
    22 SAD_SL_O_NBR Character(9) VARCHAR2(9) NOT NULL Onumber
    23 SAD_SL_CIOP_NBR Character(12) VARCHAR2(12) NOT NULL Correspondence number
    24 SAD_SL_SOFI_NBR Character(9) VARCHAR2(9) NOT NULL Social Fiscal Number
    25 SAD_SL_POST_CD_CHK Character(1) VARCHAR2(1) NOT NULL Postcodecheck
    F=False
    T=True