SSR_HM_CAD_SRCH

(SQL View)
 Index Back

Course Admn Search Rec

TCSI Transactions Course Admn Search Rec

SELECT DISTINCT ACAD_PROG.EMPLID , ACAD_PROG.ACAD_CAREER , ACAD_PROG.STDNT_CAR_NBR , ACAD_PROG.ACAD_PROG , CASE WHEN AUS.SSR_DEST_PLAN = 'Y' THEN AUS.ACAD_PLAN ELSE ' ' END , ACAD_PROG.INSTITUTION ,ACAD_PROG.ADMIT_TERM , ACAD_PROG.PROG_REASON ,ACAD_PROG.PROG_ACTION , CAR_AUS.SSR_PROVIDER_TYPE , PE.FIRST_NAME_SRCH , PE.LAST_NAME_SRCH , ACAD_PLAN.ACAD_PLAN FROM PS_ACAD_PROG ACAD_PROG , PS_ACAD_PLAN ACAD_PLAN , PS_SSR_ACD_PLN_AUS AUS , PS_ACAD_PROG_TBL PRG_ANZ , PS_SSR_ACD_PRG_AUS PRG_AUS , PS_SSR_CAREER_AUS CAR_AUS , PS_SSR_PRG_TYP_TBL PTYPE , PS_PEOPLE_SRCH PE , PS_SSR_ACADPRG_AUS A WHERE ACAD_PROG.EFFDT=( SELECT MAX(ACAD_PROG2.EFFDT) FROM PS_ACAD_PROG ACAD_PROG2 , PS_ACAD_PLAN ACAD_PLAN2 WHERE ACAD_PROG2.EMPLID=ACAD_PROG.EMPLID AND ACAD_PROG2.ACAD_CAREER=ACAD_PROG.ACAD_CAREER AND ACAD_PROG2.STDNT_CAR_NBR=ACAD_PROG.STDNT_CAR_NBR AND ACAD_PROG2.ACAD_PROG = ACAD_PROG.ACAD_PROG AND ACAD_PROG2.ADMIT_TERM = ACAD_PROG.ADMIT_TERM AND ACAD_PROG2.EMPLID=ACAD_PLAN2.EMPLID AND ACAD_PROG2.ACAD_CAREER=ACAD_PLAN2.ACAD_CAREER AND ACAD_PROG2.STDNT_CAR_NBR=ACAD_PLAN2.STDNT_CAR_NBR AND ACAD_PROG2.EMPLID=ACAD_PLAN2.EMPLID AND ACAD_PROG2.EFFDT=ACAD_PLAN2.EFFDT AND ACAD_PROG2.EFFSEQ=ACAD_PLAN2.EFFSEQ AND ACAD_PLAN2.ACAD_PLAN = ACAD_PLAN.ACAD_PLAN) AND ACAD_PROG.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_ACAD_PROG PROG2 WHERE ACAD_PROG.EMPLID = PROG2.EMPLID AND ACAD_PROG.ACAD_CAREER = PROG2.ACAD_CAREER AND ACAD_PROG.STDNT_CAR_NBR = PROG2.STDNT_CAR_NBR AND ACAD_PROG.EFFDT = PROG2.EFFDT AND ACAD_PROG.ACAD_PROG = PROG2.ACAD_PROG AND ACAD_PROG.ADMIT_TERM = PROG2.ADMIT_TERM) AND ACAD_PROG.EMPLID=A.EMPLID AND ACAD_PROG.ACAD_CAREER=A.ACAD_CAREER AND ACAD_PROG.STDNT_CAR_NBR =A.STDNT_CAR_NBR AND ACAD_PROG.EFFDT=A.EFFDT AND ACAD_PROG.EFFSEQ = A.EFFSEQ AND A.SSR_HM_EXIT_AWRD = 'N' AND EXISTS ( SELECT AP2.ACAD_PROG FROM PS_ACAD_PROG AP2 WHERE AP2.EMPLID = ACAD_PROG.EMPLID AND AP2.ACAD_CAREER = ACAD_PROG.ACAD_CAREER AND AP2.STDNT_CAR_NBR = ACAD_PROG.STDNT_CAR_NBR AND AP2.INSTITUTION = ACAD_PROG.INSTITUTION AND AP2.ACAD_PROG = ACAD_PROG.ACAD_PROG AND AP2.EFFDT <= %CurrentDateIn AND AP2.PROG_ACTION IN ('MATR','ACTV' , 'PRGC' ,'PLNC') ) AND ACAD_PLAN.EMPLID = ACAD_PROG.EMPLID AND ACAD_PLAN.ACAD_CAREER = ACAD_PROG.ACAD_CAREER AND ACAD_PLAN.STDNT_CAR_NBR = ACAD_PROG.STDNT_CAR_NBR AND ACAD_PLAN.EFFDT = ACAD_PROG.EFFDT AND ACAD_PLAN.EFFSEQ = ACAD_PROG.EFFSEQ AND AUS.INSTITUTION = ACAD_PROG.INSTITUTION AND AUS.ACAD_PLAN = ACAD_PLAN.ACAD_PLAN AND %EffdtCheck (SSR_ACD_PLN_AUS AUS2, AUS, %CURRENTDATEIN) AND PRG_ANZ.INSTITUTION = ACAD_PROG.INSTITUTION AND PRG_ANZ.ACAD_PROG = ACAD_PROG.ACAD_PROG AND PRG_ANZ.EFFDT = ( SELECT MAX(PRG_ANZ2.EFFDT) FROM PS_ACAD_PROG_TBL PRG_ANZ2 WHERE PRG_ANZ.INSTITUTION = PRG_ANZ2.INSTITUTION AND PRG_ANZ.ACAD_PROG = PRG_ANZ2.ACAD_PROG AND PRG_ANZ2.EFFDT <= %CurrentDateIn) AND PRG_AUS.INSTITUTION = ACAD_PROG.INSTITUTION AND PRG_AUS.ACAD_PROG = ACAD_PROG.ACAD_PROG AND PRG_AUS.SSR_HM_AGGR_AWRD<>'Y' AND PRG_AUS.EFFDT = ( SELECT MAX(PRG_AUS2.EFFDT) FROM PS_SSR_ACD_PRG_AUS PRG_AUS2 WHERE PRG_AUS.INSTITUTION = PRG_AUS2.INSTITUTION AND PRG_AUS.ACAD_PROG = PRG_AUS2.ACAD_PROG AND PRG_AUS2.EFFDT <= %CurrentDateIn) AND PTYPE.SSR_PROG_TYPE_CD = PRG_AUS.SSR_PROG_TYPE_CD AND PTYPE.EFFDT = ( SELECT MAX(TYPE2.EFFDT) FROM PS_SSR_PRG_TYP_TBL TYPE2 WHERE TYPE2.SSR_PROG_TYPE_CD = PTYPE.SSR_PROG_TYPE_CD AND TYPE2.EFFDT <= PRG_ANZ.EFFDT) AND PTYPE.SSR_DEST_NON_DEST = 'N' AND ACAD_PROG.INSTITUTION=CAR_AUS.INSTITUTION AND ACAD_PROG.ACAD_CAREER=CAR_AUS.ACAD_CAREER AND %EffdtCheck (SSR_CAREER_AUS CAR_AUS2, CAR_AUS, %CURRENTDATEIN) AND ACAD_PROG.EMPLID=PE.EMPLID AND NOT EXISTS ( SELECT EMPLID FROM PS_ACAD_PROG PROG3 WHERE PROG3.EMPLID = ACAD_PROG.EMPLID AND PROG3.ACAD_CAREER = ACAD_PROG.ACAD_CAREER AND PROG3.STDNT_CAR_NBR = ACAD_PROG.STDNT_CAR_NBR AND PROG3.ACAD_PROG = ACAD_PROG.ACAD_PROG AND PROG3.EFFDT > ACAD_PROG.EFFDT AND PROG3.PROG_ACTION ='DEFR' ) AND NOT EXISTS ( SELECT EMPLID FROM PS_ACAD_PROG PROG4 WHERE PROG4.EMPLID = ACAD_PROG.EMPLID AND PROG4.ACAD_CAREER = ACAD_PROG.ACAD_CAREER AND PROG4.STDNT_CAR_NBR = ACAD_PROG.STDNT_CAR_NBR AND PROG4.ACAD_PROG = ACAD_PROG.ACAD_PROG AND PROG4.EFFDT = ACAD_PROG.EFFDT AND PROG4.PROG_ACTION = 'ADMT' ) AND NOT EXISTS ( SELECT P2.EMPLID FROM PS_ACAD_PROG P2 , PS_ACAD_PLAN PL2 , PS_SSR_ACD_PLN_AUS AUS3 WHERE P2.EMPLID= ACAD_PROG.EMPLID AND P2.STDNT_CAR_NBR = ACAD_PROG.STDNT_CAR_NBR AND P2.ACAD_CAREER = ACAD_PROG.ACAD_CAREER AND P2.EFFDT = ( SELECT MIN(P3.EFFDT) FROM PS_ACAD_PROG P3 WHERE P3.EMPLID=ACAD_PROG.EMPLID AND P3.ACAD_CAREER=ACAD_PROG.ACAD_CAREER AND P3.STDNT_CAR_NBR=ACAD_PROG.STDNT_CAR_NBR AND P3.EFFDT > ACAD_PROG.EFFDT ) AND P2.EMPLID=PL2.EMPLID AND P2.ACAD_CAREER=PL2.ACAD_CAREER AND P2.STDNT_CAR_NBR=PL2.STDNT_CAR_NBR AND P2.EFFDT=PL2.EFFDT AND P2.EFFSEQ=PL2.EFFSEQ AND AUS3.INSTITUTION = ACAD_PROG.INSTITUTION AND AUS3.ACAD_PLAN = PL2.ACAD_PLAN AND P2.PROG_ACTION = 'PLNC' AND AUS3.SSR_DEST_PLAN = 'N' AND AUS.SSR_DEST_PLAN = 'N')

# 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 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
7 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term
8 PROG_REASON Character(4) VARCHAR2(4) NOT NULL Action Reason
9 PROG_ACTION Character(4) VARCHAR2(4) NOT NULL Program Action
ACTV=Activate
ADMT=Admit
ADRV=Admission Revocation
APPL=Application
COMP=Completion of Program
COND=Conditional Admit
DATA=Data Change
DDEF=Defer Decision
DEFR=Defer Enrollment
DEIN=Intention to Matriculate
DENY=Deny
DISC=Discontinuation
DISM=Dismissal
LEAV=Leave of Absence
MATR=Matriculation
PLNC=Plan Change
PRGC=Program Change
RADM=Readmit
RAPP=Readmit Application
RECN=Reconsideration
REVK=Revoke Degree
RLOA=Return from Leave of Absence
SPND=Suspension
TRAN=Transfer to Other Career
VDIS=Voluntarily Discontinued
WADM=Administrative Withdrawal
WAIT=Waitlist
WAOF=Waitlist Offer
WAPP=Applicant Withdrawal
10 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
11 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
12 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
13 SSR_HM_ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan - to hold plan value, used in CAD srch record. This is ot hold the plan value irrespective of whether dest plan =Y