SAA_ADVNOTE_VW

(SQL View)
 Index Back

Advisor Notes View

This view retrieves advisor notes data

SELECT A.EMPLID , A.INSTITUTION , A.SAA_NOTE_ID , B.SAA_ITEM_SEQ , A.SAA_NOTE_TYPE , A.SAA_NOTE_SUBTYPE , A.ADVISOR_ID , A.SAA_NOTE_STATUS , A.SCC_ROW_ADD_OPRID , A.SCC_ROW_ADD_DTTM , A.SAA_NOTE_ADD_DT , A.SAA_NOTE_UPD_DT , A.SAA_NOTE_ACCESS , A.SAA_NOTE_CONTACT , A.SAA_NOTE_SUBJ , CASE WHEN MIN(B.SAA_REM_DUE_DT) >= %CurrentDateIn THEN 'P' ELSE CASE WHEN MIN(B.SAA_REM_DUE_DT) < %CurrentDateIn THEN 'O' ELSE 'N' END END FROM PS_SAA_ADV_NOTE A LEFT JOIN PS_SAA_NOTEIREM_VW B ON A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.SAA_NOTE_ID = B.SAA_NOTE_ID GROUP BY A.EMPLID , A.INSTITUTION , A.SAA_NOTE_ID , B.SAA_ITEM_SEQ , A.SAA_NOTE_TYPE , A.SAA_NOTE_SUBTYPE , A.ADVISOR_ID , A.SAA_NOTE_STATUS , A.SCC_ROW_ADD_OPRID , A.SCC_ROW_ADD_DTTM , A.SAA_NOTE_ADD_DT , A.SAA_NOTE_UPD_DT , A.SAA_NOTE_ACCESS , A.SAA_NOTE_CONTACT , A.SAA_NOTE_SUBJ

  • Related Language Record: SAA_NOTE_VW_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PEOPLE_SRCH

    2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    3 SAA_NOTE_ID Character(5) VARCHAR2(5) NOT NULL This field denotes the note sequence ID for a particular advisee and institution.
    4 SAA_ITEM_SEQ Number(6,0) INTEGER NOT NULL Denotes the sequence of the note items logged.
    5 SAA_NOTE_TYPE Character(10) VARCHAR2(10) NOT NULL This field denotes an advising note's category code.

    Prompt Table: SAA_NOTE_TYPE

    6 SAA_NOTE_SUBTYPE Character(10) VARCHAR2(10) NOT NULL This field denotes an advising note's subcategory code.

    Prompt Table: SAA_NOTE_STYPE

    7 ADVISOR_ID Character(11) VARCHAR2(11) NOT NULL This field is used to identify advisors assigned to a student.
    8 SAA_NOTE_STATUS Character(2) VARCHAR2(2) NOT NULL This field denoes the status of an advising note.
    CL=Closed
    OP=Open
    9 SCC_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Created By
    10 SCC_ROW_ADD_DTTM DateTime(26) TIMESTAMP Created
    11 SAA_NOTE_ADD_DT Date(10) DATE Denotes when the advisor note was created
    12 SAA_NOTE_UPD_DT Date(10) DATE This field denotes when an advising note was last updated.
    13 SAA_NOTE_ACCESS Character(1) VARCHAR2(1) NOT NULL Denotes the advisor note access
    N=No
    Y=Yes
    14 SAA_NOTE_CONTACT Character(10) VARCHAR2(10) NOT NULL Advising Note Contact Type

    Prompt Table: SAA_NOTE_CNTACT

    15 SAA_NOTE_SUBJ Character(50) VARCHAR2(50) NOT NULL This field denote the subject / title of an advisor note.
    16 SAA_REM_OVR_STAT Character(1) VARCHAR2(1) NOT NULL Denotes the advisor note overall reminder / action items status
    N=None
    O=Overdue
    P=Pending