STDNT_INS_BU_VW

(SQL View)
 Index Back

Student Inst Bus Unit View


SELECT DISTINCT A.EMPLID , A.INSTITUTION , A.STRM , D.BUSINESS_UNIT FROM PS_STDNT_CAR_TERM A , PS_TERM_TBL B , PS_ACAD_PROG C , PS_HOME_CAMPUS_TBL D WHERE A.BILLING_CAREER = A.ACAD_CAREER AND A.INSTITUTION = B.INSTITUTION AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STRM = B.STRM AND A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND C.EFFDT = ( SELECT MAX(CA.EFFDT) FROM PS_ACAD_PROG CA WHERE C.EMPLID = CA.EMPLID AND C.ACAD_CAREER = CA.ACAD_CAREER AND C.STDNT_CAR_NBR = CA.STDNT_CAR_NBR AND CA.EFFDT <= B.SSR_TRMAC_LAST_DT) AND C.EFFSEQ = ( SELECT MAX(CB.EFFSEQ) FROM PS_ACAD_PROG CB WHERE C.EMPLID = CB.EMPLID AND C.ACAD_CAREER = CB.ACAD_CAREER AND C.STDNT_CAR_NBR = CB.STDNT_CAR_NBR AND C.EFFDT = CB.EFFDT) AND A.INSTITUTION = D.INSTITUTION AND C.ACAD_PROG = D.ACAD_PROG AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_HOME_CAMPUS_TBL DD WHERE DD.INSTITUTION = D.INSTITUTION AND DD.ACAD_PROG = D.ACAD_PROG AND DD.EFFDT <= B.TERM_BEGIN_DT AND DD.CAMPUS = D.CAMPUS) AND C.CAMPUS = D.CAMPUS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

3 STRM Character(4) VARCHAR2(4) NOT NULL Term
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit