SAD_BR_M324_WD1

(SQL View)
 Index Back

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID , ES.SAD_BR_ENRL_SQ_NLD , APT.HEGIS_CODE , ITMVWS.SCC_CAF_ATTR_VAL AS SAD_BR_GEN_TST_NLD , INST.SSR_RES_MAP_ID , INST.GRADING_SCHEME , INST.GRADING_BASIS , RSLT.ATTEMPT_NBR , AP.SSR_APT_INSTANCE , INST.SAD_BR_RES_SCL_NLD , INST.SAD_BR_GRD_SCH_NLD , INST.SAD_BR_GRD_BAS_NLD , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SAD_BR_ENRSQNLD ES JOIN PS_ACAD_PROG AP ON ES.INSTITUTION = AP.INSTITUTION AND ES.EMPLID = AP.EMPLID AND ES.ACAD_CAREER =AP.ACAD_CAREER AND ES.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP.EFFDT = ( SELECT MAX(AP_ED.EFFDT) FROM PS_ACAD_PROG AP_ED WHERE AP_ED.INSTITUTION = AP.INSTITUTION AND AP_ED.EMPLID = AP.EMPLID AND AP_ED.ACAD_CAREER =AP.ACAD_CAREER AND AP_ED.STDNT_CAR_NBR = AP.STDNT_CAR_NBR) AND AP.EFFSEQ = ( SELECT MAX(AP_E.EFFSEQ) FROM PS_ACAD_PROG AP_E WHERE AP_E.INSTITUTION = AP.INSTITUTION AND AP_E.EMPLID = AP.EMPLID AND AP_E.ACAD_CAREER = AP.ACAD_CAREER AND AP_E.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP_E.EFFDT = AP.EFFDT) JOIN PS_ACAD_PROG_TBL APT ON ES.INSTITUTION = APT.INSTITUTION AND AP.ACAD_PROG = APT.ACAD_PROG AND APT.EFFDT = ( SELECT MAX(APT1.EFFDT) FROM PS_ACAD_PROG_TBL APT1 WHERE APT1.INSTITUTION = APT.INSTITUTION AND APT1.ACAD_PROG = APT.ACAD_PROG AND APT1.EFFDT <= AP.EFFDT) JOIN PS_SAD_BR_BO_NLD INST ON INST.INSTITUTION = ES.INSTITUTION AND INST.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND ES.SAD_BR_EDU_SEC_NLD = INST.SAD_BR_EDU_SEC_NLD AND INST.EFFDT = ( SELECT MAX(INST_ED.EFFDT) FROM PS_SAD_BR_BO_NLD INST_ED WHERE INST.INSTITUTION = INST_ED.INSTITUTION AND INST.SCC_BRINCD_NLD = INST_ED.SCC_BRINCD_NLD AND INST_ED.EFFDT <= AP.EFFDT) JOIN PS_SSR_APT_RSLT RSLT ON RSLT.INSTITUTION = ES.INSTITUTION AND RSLT.EMPLID = ES.EMPLID JOIN PS_SSR_APT_ITEM ITM ON RSLT.INSTITUTION = ITM.INSTITUTION AND RSLT.EMPLID = ITM.EMPLID AND ITM.SSR_APT_INSTANCE = RSLT.SSR_APT_INSTANCE AND ITM.SSR_APT_ITEM_SEQ = RSLT.SSR_APT_ITEM_SEQ JOIN PS_SSR_AIR_ADD ITMVWS ON RSLT.INSTITUTION = ITMVWS.INSTITUTION AND ITM.SSR_ITEM_ID = ITMVWS.SSR_ITEM_ID AND ITMVWS.SCC_CAF_ATTRIB_NM = INST.SCC_CAF_ATTRIB_NM AND ITMVWS.SCC_CAF_ATTR_VAL IN ( SELECT HG.HEGIS_CODE FROM PS_SAD_BR_HGGT_NLD HG WHERE INST.INSTITUTION = HG.INSTITUTION AND INST.SCC_BRINCD_NLD = HG.SCC_BRINCD_NLD AND HG.SAD_BR_EDU_SEC_NLD = INST.SAD_BR_EDU_SEC_NLD AND INST.EFFDT = HG.EFFDT) AND ITMVWS.EFFDT = ( SELECT MAX(ITMVWS1.EFFDT) FROM PS_SSR_AIR_HDR ITMVWS1 WHERE ITMVWS1.INSTITUTION = ITMVWS.INSTITUTION AND ITMVWS1.SSR_ITEM_ID = ITMVWS.SSR_ITEM_ID AND ITMVWS1.EFFDT <= AP.EFFDT) AND ITM.SSR_ITEM_TYPE = ( SELECT AIGT.SSR_ITEM_TYPE FROM PS_SAD_BR_AIGT_NLD AIGT WHERE AIGT.INSTITUTION = ES.INSTITUTION AND AIGT.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND AIGT.SAD_BR_EDU_SEC_NLD = ES.SAD_BR_EDU_SEC_NLD AND AIGT.SAD_BR_TST_TYP_NLD = 'EE' AND AIGT.EFFDT = ( SELECT MAX(AIGT1.EFFDT) FROM PS_SAD_BR_AIGT_NLD AIGT1 WHERE AIGT1.INSTITUTION = AIGT.INSTITUTION AND AIGT1.SCC_BRINCD_NLD = AIGT.SCC_BRINCD_NLD AND AIGT1.SAD_BR_EDU_SEC_NLD = AIGT.SAD_BR_EDU_SEC_NLD AND AIGT1.SAD_BR_TST_TYP_NLD = AIGT.SAD_BR_TST_TYP_NLD)) AND RSLT.ATTEMPT_NBR = ( SELECT MAX(RSLT1.ATTEMPT_NBR) FROM PS_SSR_APT_RSLT RSLT1 , PS_SSR_APT_ATT ATT , PS_SSR_APT_SCHD SCHD , PS_SSR_APT_ITEM ITM1 , PS_SSR_AIR_ADD ITMVWS1 WHERE RSLT.EMPLID = RSLT1.EMPLID AND RSLT.INSTITUTION = RSLT1.INSTITUTION AND RSLT.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND RSLT1.INSTITUTION = ITM1.INSTITUTION AND RSLT1.EMPLID = ITM1.EMPLID AND ITM1.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND ITM1.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ AND ITM1.SSR_ITEM_TYPE = ITM.SSR_ITEM_TYPE AND RSLT1.INSTITUTION = ATT.INSTITUTION AND RSLT1.EMPLID = ATT.EMPLID AND ATT.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND ATT.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ AND RSLT1.ATTEMPT_NBR = ATT.ATTEMPT_NBR AND SCHD.INSTITUTION = RSLT1.INSTITUTION AND SCHD.EMPLID = RSLT1.EMPLID AND SCHD.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND SCHD.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ AND SCHD.ATTEMPT_NBR = RSLT1.ATTEMPT_NBR AND RSLT1.INSTITUTION = ITMVWS1.INSTITUTION AND ITM1.SSR_ITEM_ID = ITMVWS1.SSR_ITEM_ID AND ITMVWS1.SCC_CAF_ATTRIB_NM = INST.SCC_CAF_ATTRIB_NM AND ITMVWS1.SCC_CAF_ATTR_VAL = ITMVWS.SCC_CAF_ATTR_VAL AND RSLT1.SSR_RES_MAP_ID = INST.SAD_BR_RES_SCL_NLD AND RSLT1.GRADING_SCHEME = INST.SAD_BR_GRD_SCH_NLD AND RSLT1.GRADING_BASIS = INST.SAD_BR_GRD_BAS_NLD AND RSLT1.SSR_INCL_IN_CALC = 'Y' AND RSLT1.SSR_RSLT_TYPE_STAT >= '30' AND ATT.SSR_ATT_STAT >= '80' AND SCHD.STRM = ( SELECT MAX(SCHD1.STRM) FROM PS_SSR_APT_SCHD SCHD1 WHERE SCHD.EMPLID = SCHD1.EMPLID AND SCHD.INSTITUTION = SCHD1.INSTITUTION AND SCHD.SSR_APT_INSTANCE = SCHD1.SSR_APT_INSTANCE AND SCHD.SSR_APT_ITEM_SEQ = SCHD1.SSR_APT_ITEM_SEQ AND SCHD1.ATTEMPT_NBR = SCHD.ATTEMPT_NBR) AND SCHD.END_DT <= AP.EFFDT AND ((%Sql(SAD_BR_TRM_YEAR_NLD, AP.INSTITUTION, AP.ACAD_CAREER, AP.COMPLETION_TERM)) - (%Sql(SAD_BR_TRM_YEAR_NLD, AP.INSTITUTION, AP.ACAD_CAREER, SCHD.STRM))) BETWEEN 0 AND 2 ) AND RSLT.SSR_RSLT_SEQ = ( SELECT MAX(RSLT2.SSR_RSLT_SEQ) FROM PS_SSR_APT_RSLT RSLT2 WHERE RSLT.EMPLID = RSLT2.EMPLID AND RSLT.INSTITUTION = RSLT2.INSTITUTION AND RSLT.SSR_APT_INSTANCE = RSLT2.SSR_APT_INSTANCE AND RSLT.SSR_APT_ITEM_SEQ = RSLT2.SSR_APT_ITEM_SEQ AND RSLT.ATTEMPT_NBR = RSLT2.ATTEMPT_NBR AND RSLT2.SSR_INCL_IN_CALC = 'Y' ) AND ((%Sql(SAD_BR_EDU_LVL_NLD, ITMVWS.SCC_CAF_ATTR_VAL, AP.EFFDT)) >= (%Sql(SAD_BR_EDU_LVL_NLD, APT.HEGIS_CODE, AP.EFFDT)))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
3 SAD_BR_EDU_SEC_NLD Character(2) VARCHAR2(2) NOT NULL Educational Sector
BO=Professional Education
ED=Education
VA=VAVO
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 SAD_BR_ENRL_SQ_NLD Character(3) VARCHAR2(3) NOT NULL Enrollment Sequence number
6 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code
7 SAD_BR_GEN_TST_NLD Character(4) VARCHAR2(4) NOT NULL Generic Test
8 SSR_RES_MAP_ID Character(12) VARCHAR2(12) NOT NULL Result Scale
9 GRADING_SCHEME Character(3) VARCHAR2(3) NOT NULL Grading Scheme
10 GRADING_BASIS Character(3) VARCHAR2(3) NOT NULL Grading Basis
ANC=ABC/NC Grading
AUD=Audit
BMT=Multi-Term Course: Not Graded
CNC=Credit / No Credit
EQV=Equivalent
GRD=Graded
HSC=Hundred Point Scale (NLD)
LAW=Law
MED=Medical School Grades
NOG=No Grade Associated
NON=Non-Graded Component
NQF=NQF Scale
OPT=Student Option
PNP=Pass/Not Pass
SUS=Satisfactory/Unsatisfactory
TRN=Transfer Grading Basis
TSC=Ten Point Scale (NLD)
11 ATTEMPT_NBR Number(3,0) SMALLINT NOT NULL Attempt Nbr
12 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
13 SAD_BR_RES_SCL_NLD Character(12) VARCHAR2(12) NOT NULL Result Scale
14 SAD_BR_GRD_SCH_NLD Character(3) VARCHAR2(3) NOT NULL Grading Scheme
15 SAD_BR_GRD_BAS_NLD Character(3) VARCHAR2(3) NOT NULL Grading Basis
16 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
17 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr