SSR_DREV_EN_SPL

(SQL View)
 Index Back

DEST Rev Enrollment View

DEST Revisions Enrollment View for courses with split ownership.

SELECT A.INSTITUTION , A.EMPLID , A.ACAD_CAREER , A.ACAD_PROG , A.STRM , A.CLASS_NBR , E.SSR_AOU_CD , D.PERCENT_OWNED , G.SSR_HECS_XMPT_STAT , G.SSR_EFTS_CLAS_LOAD , B.START_DT FROM PS_STDNT_ENRL A , PS_CLASS_TBL B , PS_CRSE_OFFER C , PS_CRSE_OFFER_OWNR D , PS_SSR_ACD_ORG_AUS E , PS_CLASS_ASSOC F , PS_SSR_STDENRL_AUS G , PS_SSR_CRS_OFR_AUS H ,PS_TERM_TBL I WHERE NOT EXISTS ( SELECT 'X' FROM PS_SSR_DESTWRK_UNT B WHERE B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.ACAD_CAREER = A.ACAD_CAREER AND B.ACAD_PROG = A.ACAD_PROG AND B.STRM = A.STRM AND B.CLASS_NBR = A.CLASS_NBR) AND A.STDNT_ENRL_STATUS<>'D' AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_DEST_REV_LL B WHERE B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.ACAD_CAREER = A.ACAD_CAREER AND B.ACAD_PROG = A.ACAD_PROG AND B.STRM = A.STRM AND B.CLASS_NBR = A.CLASS_NBR AND B.SSR_DEST_SUBMITTED IS NOT NULL) AND B.INSTITUTION = A.INSTITUTION AND B.STRM = A.STRM AND B.CLASS_NBR = A.CLASS_NBR AND C.CRSE_ID = B.CRSE_ID AND C.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_CRSE_OFFER C1 WHERE C1.CRSE_ID = C.CRSE_ID AND C1.EFFDT <= I.TERM_BEGIN_DT) AND C.SPLIT_OWNER = 'Y' AND D.CRSE_ID = C.CRSE_ID AND D.EFFDT = C.EFFDT AND D.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND E.ACAD_ORG = D.ACAD_ORG AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_SSR_ACD_ORG_AUS E1 WHERE E1.ACAD_ORG = E.ACAD_ORG AND E1.EFFDT <= I.TERM_BEGIN_DT) AND F.CRSE_ID = B.CRSE_ID AND F.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND F.STRM = B.STRM AND F.SESSION_CODE = B.SESSION_CODE AND F.ASSOCIATED_CLASS = B.ASSOCIATED_CLASS AND F.COMPONENT_PRIMARY = B.SSR_COMPONENT AND G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.ACAD_CAREER = A.ACAD_CAREER AND G.STRM = A.STRM AND G.CLASS_NBR = A.CLASS_NBR AND H.CRSE_ID = C.CRSE_ID AND H.EFFDT = C.EFFDT AND H.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND I.INSTITUTION = A.INSTITUTION AND I.ACAD_CAREER = A.ACAD_CAREER AND I.STRM = A.STRM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: SSR_DEST_INSTVW

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

3 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

Prompt Table: ACAD_CAR_TBL

4 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

Prompt Table: ACAD_PROG

5 STRM Character(4) VARCHAR2(4) NOT NULL Term
6 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
7 SSR_AOU_CD Character(3) VARCHAR2(3) NOT NULL AOU Code
8 PERCENT_OWNED Number(7,3) DECIMAL(6,3) NOT NULL Percent Owned
9 SSR_HECS_XMPT_STAT Character(3) VARCHAR2(3) NOT NULL HECS Exempt Status
10 SSR_EFTS_CLAS_LOAD Number(8,5) DECIMAL(7,5) NOT NULL Student Load by Class
11 START_DT Date(10) DATE Start Date