SSR_BR_RGVW_NLD

(SQL View)
 Index Back

Results Service Current View 1

Results Servicecurrent view 1 -Generic Test 2018-02-26: fix for bug 27102820 2019-08-08 Changes for bug 30014996 2023-02-27, fix for bug 35141048

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.EMPLID , ES.SSR_BR_ENRL_SQ_NLD , ES.SSR_BR_ENRL_SQ_NLD , ES.SSR_BR_ENRL_SQ_NLD , APT.HEGIS_CODE , ITMVWS.SCC_CAF_ATTR_VAL AS SSR_BR_GEN_TST_NLD , AP.EFFDT , %Coalesce ( (%Sql(SSR_BR_RSLT_GRD_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, 'IE', INST.SSR_RES_MAP_ID, INST.GRADING_SCHEME, INST.GRADING_BASIS, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE, ITMVWS.SCC_CAF_ATTR_VAL)), ' ') AS SSR_BR_SCH_GRD_NLD , %Coalesce ( (%Sql(SSR_BR_EXM_CD_SCH_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, INST.SSR_RES_MAP_ID, INST.GRADING_SCHEME, INST.GRADING_BASIS, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE, ITMVWS.SCC_CAF_ATTR_VAL)) , ' ') AS SSR_BR_EXMP_CD_NLD , %Coalesce ( (%Sql(SSR_BR_RSLT_GRD_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, 'CE', INST.SSR_RES_MAP_ID, INST.GRADING_SCHEME, INST.GRADING_BASIS, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE,ITMVWS.SCC_CAF_ATTR_VAL)), ' ') AS SSR_BR_CNT_GRD_NLD , %Coalesce ( (%Sql(SSR_BR_EXM_CD_CNT_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, INST.SSR_RES_MAP_ID, INST.GRADING_SCHEME, INST.GRADING_BASIS, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE, ITMVWS.SCC_CAF_ATTR_VAL)) , ' ') AS SSR_BR_EXMP_CD_CNT , %Coalesce ( (%Sql(SSR_BR_RSLT_GRD_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, 'EE', INST.SAD_BR_RES_SCL_NLD, INST.SAD_BR_GRD_SCH_NLD, INST.SAD_BR_GRD_BAS_NLD, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE,ITMVWS.SCC_CAF_ATTR_VAL)), ' ') AS SSR_BR_END_GRD_NLD , %Coalesce ( (%Sql(SSR_BR_EXM_CD_END_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, ES.EMPLID, INST.SAD_BR_RES_SCL_NLD, INST.SAD_BR_GRD_SCH_NLD, INST.SAD_BR_GRD_BAS_NLD, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE, ITMVWS.SCC_CAF_ATTR_VAL)) , ' ') AS SSR_BR_EXMP_CD_END , CASE WHEN RIO.SSR_RI_BRON_NLD = 'Y' THEN CASE WHEN AP.EFFDT >= SETUP.SSR_BR_RIO_BDT_NLD THEN CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'HM' THEN (%Sql(SSR_BR_RIO_EPV_HMCAMP_NLD, ES.INSTITUTION, AP.ACAD_PROG, AP.CAMPUS)) ELSE CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'AP' THEN (%Sql(SSR_BR_RIO_EPV_ACADPROG_NLD, APT.ACAD_ORG)) ELSE CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'AC' THEN (%Sql(SSR_BR_RIO_EPV_ACADPROG_NLD, SETUP.ACAD_ORG)) END END END END END AS SSR_RI_EPV_COD_NLD , ES.ACAD_CAREER ,ES.STDNT_CAR_NBR FROM PS_SSR_BR_ENRVWNLD 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_SSR_BR_ENRS_NLD SETUP ON SETUP.INSTITUTION = ES.INSTITUTION AND SETUP.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND %EffdtCheck(SSR_BR_ENRS_NLD SETUP_ED, SETUP, AP.EFFDT) JOIN PS_SSR_BR_RS2_NLD INST ON INST.INSTITUTION = ES.INSTITUTION AND INST.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND INST.EFFDT = SETUP.EFFDT JOIN PS_SSR_APT_RSLT RSLT ON RSLT.INSTITUTION = ES.INSTITUTION AND RSLT.EMPLID = ES.EMPLID AND RSLT.SSR_APT_INSTANCE = AP.SSR_APT_INSTANCE 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_BR_AIGT_NLD AIGT2 ON AIGT2.INSTITUTION = ES.INSTITUTION AND AIGT2.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND ITM.SSR_ITEM_TYPE = AIGT2.SSR_ITEM_TYPE 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 = ( SELECT RSA.SCC_CAF_ATTRIB_NM FROM PS_SSR_BR_RSA_NLD RSA WHERE RSA.INSTITUTION = INST.INSTITUTION AND RSA.SCC_BRINCD_NLD = INST.SCC_BRINCD_NLD AND RSA.EFFDT = INST.EFFDT) AND ITMVWS.SCC_CAF_ATTR_VAL IN ( SELECT HG.HEGIS_CODE FROM PS_SSR_BR_HGGT_NLD HG WHERE INST.INSTITUTION = HG.INSTITUTION AND INST.SCC_BRINCD_NLD = HG.SCC_BRINCD_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 AIGT2.EFFDT = ( SELECT MAX(AIGT1.EFFDT) FROM PS_SSR_BR_AIGT_NLD AIGT1 WHERE AIGT1.INSTITUTION = AIGT2.INSTITUTION AND AIGT1.SCC_BRINCD_NLD = AIGT2.SCC_BRINCD_NLD AND AIGT1.SAD_BR_TST_TYP_NLD = AIGT2.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 WHERE RSLT.EMPLID = RSLT1.EMPLID AND RSLT.INSTITUTION = RSLT1.INSTITUTION AND RSLT.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND RSLT.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ 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 ATT.SSR_INCL_IN_CALC ='Y' 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.SSR_RES_MAP_ID = INST.SAD_BR_RES_SCL_NLD OR RSLT1.SSR_RES_MAP_ID = INST.SSR_RES_MAP_ID) AND (RSLT1.GRADING_SCHEME = INST.SAD_BR_GRD_SCH_NLD OR RSLT1.GRADING_SCHEME = INST.GRADING_SCHEME) AND (RSLT1.GRADING_BASIS = INST.SAD_BR_GRD_BAS_NLD OR RSLT1.GRADING_BASIS = INST.GRADING_BASIS) AND RSLT1.SSR_RSLT_TYPE = INST.SSR_RSLT_TYPE AND RSLT1.SSR_INCL_IN_CALC = 'Y' AND RSLT1.SSR_RSLT_TYPE_STAT >= '30' AND ATT.SSR_ATT_STAT >= '80' AND AP.PROG_ACTION IN ( SELECT 'COMP' FROM PS_INSTALLATION WHERE AP.ACAD_LOAD_APPR IN (%Sql(SSR_BR_GET_ODT_LOAD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD)) UNION %Sql(SSR_BR_GET_ACTION_RSN_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD)) 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 %Coalesce ( (%Sql(SSR_BR_EXM_CD_ALL_NLD, AP.INSTITUTION, ES.SCC_BRINCD_NLD, AIGT2.SAD_BR_TST_TYP_NLD, ES.EMPLID , INST.SAD_BR_RES_SCL_NLD, INST.SAD_BR_GRD_SCH_NLD, INST.SAD_BR_GRD_BAS_NLD, RSLT.ATTEMPT_NBR, AP.SSR_APT_INSTANCE, ITMVWS.SCC_CAF_ATTR_VAL, INST.SSR_RES_MAP_ID, INST.GRADING_SCHEME, INST.GRADING_BASIS)) , ' ') = ' ' LEFT OUTER JOIN PS_SSR_RI_EPS_NLD RIO ON RIO.INSTITUTION = ES.INSTITUTION WHERE (RIO.EFFDT = ( SELECT MAX(RIO_ED.EFFDT) FROM PS_SSR_RI_EPS_NLD RIO_ED WHERE RIO.INSTITUTION = RIO_ED.INSTITUTION AND RIO_ED.EFFDT <= %CurrentDateIn) OR RIO.EFFDT IS NULL)

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 SSR_BR_ENRL_SQ_NLD Character(20) VARCHAR2(20) NOT NULL 2020-04-15 bug 31180485 - enlarged enrollment seq nbr
5 SSR_BR_RESL_SQ_NLD Character(20) VARCHAR2(20) NOT NULL Result Sequence Number
6 SSR_BR_RESL_SQ2NLD Character(20) VARCHAR2(20) NOT NULL Result Sequence
7 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code
8 SSR_BR_GEN_TST_NLD Character(4) VARCHAR2(4) NOT NULL Generic Test
9 SSR_BR_RES_DT_NLD Date(10) DATE Result Date
10 SSR_BR_SCH_GRD_NLD Character(3) VARCHAR2(3) NOT NULL School Exam Grade
11 SSR_BR_EXMP_CD_NLD Character(4) VARCHAR2(4) NOT NULL Exemption Code
HAVO=HAVO
KZDL=Elective module
MBO=MBO
NT2=NT2
VWO=VWO
12 SSR_BR_CNT_GRD_NLD Character(3) VARCHAR2(3) NOT NULL Central End Exam Grade
13 SSR_BR_EXMP_CD_CNT Character(4) VARCHAR2(4) NOT NULL Exemption Central Exam
HAVO=HAVO
MBO=MBO
NT2=NT2
VWO=VWO
14 SSR_BR_END_GRD_NLD Character(2) VARCHAR2(2) NOT NULL End Exam Grade
15 SSR_BR_EXMP_CD_END Character(4) VARCHAR2(4) NOT NULL Exemption End Exam
HAVO=HAVO
KZDL=Elective Module
MBO=MBO
NT2=NT2
VWO=VWO
16 SSR_RI_EPV_COD_NLD Character(12) VARCHAR2(12) NOT NULL Educational Provider Code
17 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
18 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr