SSR_HM_HDRUE_VW

(SQL View)
 Index Back

HDR User Engagement codes

HDR codes to be posted to TCSI

SELECT PROG.EMPLID , PROG.ACAD_CAREER , PROG.STDNT_CAR_NBR , PROG.ACAD_PROG , CASE WHEN AUS.SSR_DEST_PLAN= 'Y' THEN APLAN.ACAD_PLAN ELSE ' ' END , PROG.ADMIT_TERM , UECODE.SSR_HM_ENGAGE_NBR , PROG.INSTITUTION , CA.SSR_PROVIDER_TYPE , UECODE.SCC_ROW_UPD_DTTM FROM %Table(ACAD_PROG) PROG , %Table(SSR_HDR_UE_CODE) UECODE , %Table(ACAD_PLAN) APLAN , %Table(SSR_CAREER_AUS) CA , %table(SSR_ACD_PLN_AUS) AUS WHERE UECODE.EMPLID = PROG.EMPLID AND UECODE.ACAD_CAREER = PROG.ACAD_CAREER AND UECODE.STDNT_CAR_NBR = PROG.STDNT_CAR_NBR AND CA.INSTITUTION = PROG.INSTITUTION AND CA.ACAD_CAREER = PROG.ACAD_CAREER AND %EffdtCheck(SSR_CAREER_AUS CAR2, CA , %CURRENTDATEIN) AND PROG.EMPLID = APLAN.EMPLID AND PROG.ACAD_CAREER = APLAN.ACAD_CAREER AND PROG.STDNT_CAR_NBR = APLAN.STDNT_CAR_NBR AND PROG.EFFDT = APLAN.EFFDT AND PROG.EFFSEQ = APLAN.EFFSEQ AND AUS.INSTITUTION = PROG.INSTITUTION AND AUS.ACAD_PLAN = APLAN.ACAD_PLAN AND %EFFDTCHECK(SSR_ACD_PLN_AUS AUS2, AUS, %CURRENTDATEIN) AND PROG.EFFDT = ( SELECT MAX(PROG2.EFFDT) FROM PS_ACAD_PROG PROG2 WHERE UECODE.EMPLID = PROG2.EMPLID AND UECODE.ACAD_CAREER = PROG2.ACAD_CAREER AND UECODE.STDNT_CAR_NBR = PROG2.STDNT_CAR_NBR AND UECODE.START_DATE >= PROG2.EFFDT ) AND PROG.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_ACAD_PROG PROG2 WHERE PROG.EMPLID = PROG2.EMPLID AND PROG.ACAD_CAREER = PROG2.ACAD_CAREER AND PROG.STDNT_CAR_NBR = PROG2.STDNT_CAR_NBR AND PROG.ACAD_PROG = PROG2.ACAD_PROG AND PROG.ADMIT_TERM = PROG2.ADMIT_TERM AND PROG.EFFDT = PROG2.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
3 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
4 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
5 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan
6 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term
7 SSR_HM_ENGAGE_NBR Number(3,0) SMALLINT NOT NULL HEIMS - User Engagement Codes - engagement number
8 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
9 SSR_PROVIDER_TYPE Character(4) VARCHAR2(4) NOT NULL Provider Type
HEP=Higher Education Provider
NA=Not Applicable
TAC=Tertiary Admission Centre
VET=Vocational Education&Training
10 SCC_ROW_UPD_DTTM DateTime(26) TIMESTAMP Last Update Date/Time