SSR_TREEVAV_VW

(SQL View)
Index Back

Test Admin Vavo View


SELECT C.EMPLID ,C.INSTITUTION ,C.ACAD_CAREER ,C.STDNT_CAR_NBR ,C.ACAD_PROG ,%Substring(B.TREE_NODE,1,8) ,D.DESCR ,A.TREE_NAME ,A.EFFDT FROM PSTREEDEFN A , PSTREENODE B , PS_SSR_STPRGTR_NLD C , PS_SSR_TST_CAT_NLD D WHERE A.TREE_CATEGORY = 'TEST_ADMIN' AND A.SETID = B.SETID AND A.TREE_NAME = B.TREE_NAME AND B.EFFDT = A.EFFDT AND A.TREE_NAME = C.TREE_NAME AND D.SSR_TST_EXAMTP_NLD = 'E' AND D.SSR_TST_ID_NLD = %Substring(B.TREE_NODE,1,8) AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_SSR_TST_CAT_NLD D1 WHERE D.SSR_TST_ID_NLD = D1.SSR_TST_ID_NLD AND D1.EFFDT <= %CurrentDateIn) AND D.EFF_STATUS = 'A' AND A.EFFDT = C.TREE_EFFDT AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SSR_STPRGTR_NLD C1 WHERE C.EMPLID = C1.EMPLID AND C.INSTITUTION =C1.INSTITUTION AND C.ACAD_CAREER =C1.ACAD_CAREER AND C.STDNT_CAR_NBR =C1.STDNT_CAR_NBR AND C.TREE_NAME = C1.TREE_NAME AND C.ACAD_PROG =C1.ACAD_PROG AND C1.EFFDT <= %CurrentDateIn)

# 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
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
4 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
5 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
6 SSR_TST_ID_NLD Character(8) VARCHAR2(8) NOT NULL Test ID
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
9 TREE_EFFDT Date(10) DATE Effective date of the Tree