Student Extract Secrets Finally Revealed!!
Use your left and right arrow keys to see text outside of margins.
Student Extract
The student extract is an sqr that runs daily except for Sunday and updates the PS_UM_STUD_EXTRACT table. The sqr is umstuext.sqr . It builds new rows for the
PS_UM_STUD_EXTRACT in a table called PS_UM_STUD_EXT_BLD and when finished does a quick deletion of the old rows and insertion of new rows from PS_UM_STUD_EXT_BLD into
PS_UM_STUD_EXTRACT.The PS_UM_STUD_EXTRACT table is based on the table PS_STDNT_CAR_TERM which is keyed by the fields EMPLID, ACAD_CAREER, INSTITUTION and STRM.
For the purposes of enrollment reporting we are concerned with students that are taking credits toward a degree. Student reporting is done by term and students
may be counted at more than one institution but students may also have more than one ACAD_CAREER which could cause a problem with enrollment counts.
There is a field called ROW_CHECK on the PS_UM_STUD_EXTRACT table that is set to 'Y' for the most advanced ACAD_CAREER a student has for a given STRM and INSTITUTION with course credit.
To select students using the student extract you could use the following SQL to get one row for each STRM, EMPLID and INSTITUTION:
SELECT *
FROM PS_UM_STUD_EXTRACT A
WHERE A.STRM = '1710'
AND ROW_CHECK = 'Y';
To use PS_STDNT_CAR_TERM the equivalent would be:
SELECT *
FROM PS_STDNT_CAR_TERM STUCAR
WHERE STUCAR.STRM = '1710'
AND STUCAR.ACAD_CAREER <> 'NCRD'
AND DECODE(STUCAR.ACAD_CAREER,
'LAW',
'5',
'GRAD',
'4',
'PBAC',
'3',
'UGRD',
'2',
'1') =
(SELECT MAX(DECODE(B.ACAD_CAREER,
'LAW',
'5',
'GRAD',
'4',
'PBAC',
'3',
'UGRD',
'2',
'1'))
FROM PS_STDNT_CAR_TERM B
WHERE B.STRM = STUCAR.STRM
AND B.INSTITUTION = STUCAR.INSTITUTION
AND B.EMPLID = STUCAR.EMPLID
AND (SELECT SUM(MULTI.UNT_PRGRSS)
FROM PS_STDNT_ENRL MULTI
WHERE MULTI.EMPLID = B.EMPLID
AND MULTI.STRM = B.STRM
AND MULTI.INSTITUTION = B.INSTITUTION
AND MULTI.ACAD_CAREER = B.ACAD_CAREER
AND MULTI.STDNT_ENRL_STATUS = 'E'
AND (MULTI.EARN_CREDIT = 'Y' OR (MULTI.UNITS_ATTEMPTED <> 'N' AND
MULTI.AUDIT_GRADE_BASIS = 'N') AND
MULTI.ENRL_DROP_DT IS NULL)) > 0);
You could add a ROW_CHECK like calculation to PS_STDNT_CAR_TERM like this:
SELECT STUCAR.*,
CASE
WHEN DECODE(STUCAR.ACAD_CAREER,
'LAW',
'5',
'GRAD',
'4',
'PBAC',
'3',
'UGRD',
'2',
'1') =
(SELECT MAX(DECODE(B.ACAD_CAREER,
'LAW',
'5',
'GRAD',
'4',
'PBAC',
'3',
'UGRD',
'2',
'1'))
FROM PS_STDNT_CAR_TERM B
WHERE B.STRM = STUCAR.STRM
AND B.INSTITUTION = STUCAR.INSTITUTION
AND B.EMPLID = STUCAR.EMPLID
AND (SELECT SUM(MULTI.UNT_PRGRSS)
FROM PS_STDNT_ENRL MULTI
WHERE MULTI.EMPLID = B.EMPLID
AND MULTI.STRM = B.STRM
AND MULTI.INSTITUTION = B.INSTITUTION
AND MULTI.ACAD_CAREER = B.ACAD_CAREER
AND MULTI.STDNT_ENRL_STATUS = 'E'
AND (MULTI.EARN_CREDIT = 'Y' OR
(MULTI.UNITS_ATTEMPTED <> 'N' AND
MULTI.AUDIT_GRADE_BASIS = 'N') AND
MULTI.ENRL_DROP_DT IS NULL)) > 0) THEN
'Y'
ELSE
'N'
END ROW_CHECK
FROM PS_STDNT_CAR_TERM STUCAR
WHERE STUCAR.STRM = '1710'
AND STUCAR.ACAD_CAREER <> 'NCRD'
The student extract uses many fields from PS_STDNT_CAR_TERM as follows:
REG_CARD_DATE,
FULLY_ENRL_DT,
ACAD_LEVEL_BOT,
ACAD_LEVEL_EOT,
ACAD_LEVEL_PROJ,
UNT_TERM_TOT,
GRADE_POINTS,
CUR_GPA,
CUM_GPA,
ACADEMIC_LOAD,
FA_LOAD,
ROWNUM AS STUDENT_CAR_TERM_NO,
UNT_TAKEN_PRGRSS,
UNT_PASSD_PRGRSS,
UNT_TRNSFR,
TOT_PASSD_PRGRSS,
TOT_GRADE_POINTS,
STRM,
WITHDRAW_CODE,
WITHDRAW_REASON,
WITHDRAW_DATE,
LAST_DATE_ATTENDED,
ACAD_PROG_PRIMARY,
NSLDS_LOAN_YEAR,
OVRD_ACAD_LVL_ALL,
OVRD_ACAD_LVL_PROJ,
ELIG_TO_ENROLL,
OVRD_MAX_UNITS,
MAX_TOTAL_UNIT,
MAX_NOGPA_UNIT,
MAX_AUDIT_UNIT,
MAX_WAIT_UNIT,
MIN_TOTAL_UNIT,
OVRD_BILL_UNITS,
PROJ_BILL_UNT,
UNT_TAKEN_GPA, !
UNT_PASSD_GPA,
UNT_TAKEN_NOGPA,
UNT_PASSD_NOGPA,
UNT_INPROG_GPA,
UNT_INPROG_NOGPA,
UNT_AUDIT,
TRF_TAKEN_GPA,
TRF_TAKEN_NOGPA,
TRF_PASSED_GPA,
TRF_PASSED_NOGPA,
TRF_GRADE_POINTS,
UNT_TEST_CREDIT,
UNT_OTHER,
UNT_TAKEN_FA,
UNT_PASSD_FA,
UNT_TAKEN_FA_GPA,
GRADE_POINTS_FA,
RESET_CUM_STATS,
TOT_TAKEN_PRGRSS,
TOT_TAKEN_GPA,
TOT_PASSD_GPA,
TOT_TAKEN_NOGPA,
TOT_PASSD_NOGPA,
TOT_INPROG_GPA,
TOT_INPROG_NOGPA,
TOT_AUDIT,
TOT_TRNSFR,
TOT_TEST_CREDIT,
TOT_OTHER,
TOT_CUMULATIVE,
TOT_TAKEN_FA,
TOT_PASSD_FA,
TOT_TAKEN_FA_GPA,
TOT_GRD_POINTS_FA,
FORM_OF_STUDY,
TERM_TYPE,
CLASS_RANK_NBR,
CLASS_RANK_TOT,
SEL_GROUP,
TUIT_CALC_REQ,
TUIT_CALC_DTTM,
FA_STATS_CALC_REQ,
FA_STATS_CALC_DTTM,
FA_ELIGIBILITY,
BILLING_CAREER,
UNIT_MULTIPLIER,
ACAD_YEAR,
ACAD_GROUP_ADVIS,
CUR_RESIDENT_TERMS,
TRF_RESIDENT_TERMS,
CUM_RESIDENT_TERMS,
REFUND_PCT,
REFUND_SCHEME,
PRO_RATA_ELIGIBLE,
ENRL_ON_TRANS_DT,
STATS_ON_TRANS_DT,
FULLY_GRADED_DT,
STUDY_AGREEMENT,
START_DATE,
END_DATE,
MAX_CRSE_COUNT,
REGISTERED,
OVRD_TUIT_GROUP,
OVRD_WDRW_SCHED,
TUITION_RES_TERMS,
OVRD_INIT_ADD_FEE,
OVRD_INIT_ENR_FEE,
TC_UNITS_ADJUST,
LOCK_IN_AMT,
LOCK_IN_DT,
ACAD_CAREER_FIRST,
ACADEMIC_LOAD_DT,
UNTPRG_CHG_NSLC_DT,
SSR_ACTIVATION_DT,
EMPLID,
ACAD_CAREER,
INSTITUTION,
STDNT_CAR_NBR .
The extract also joins in fields from other tables and has fields are calculated by the sqr.
From the PS_PERSONAL_DATA table come these fields:
NVL(LAST_NAME,' ') ,
NVL(FIRST_NAME,' ') ,
NVL(MIDDLE_NAME,' '),
NVL(NAME_PREFIX,' ') ,
NVL(NAME_SUFFIX,' '),
NVL(NAME,' ') ,
NVL(ADDRESS1,' ') ,,
NVL(ADDRESS2,' ') ,
NVL(ADDRESS3,' ') ,
NVL(ADDRESS4,' ') ,
NVL(CITY,' ') ,
NVL(STATE,' ') ,
NVL(POSTAL,' ') ,
NVL(COUNTY,' ') ,
NVL(COUNTRY,' ') ,
NVL(SEX,' ') ,
STA.BIRTHDATE,
NVL(BIRTHPLACE,' ') ,
NVL(BIRTHSTATE,' ') ,
NVL(BIRTHCOUNTRY,' ') ,
NVL(MAR_STATUS,' ') ,
NVL(MAR_STATUS_DT,'01-JAN-1901') ,
NVL(DT_OF_DEATH,'01-JAN-1901') ,
NVL(PHONE,' ')
From the PS_ACAD_PROG table the fields are taken from the most recent row. For terms before the current term the row that is
current at the end of the term is used. The date would be similar to what is returned from this example:
SELECT LEAST(STOP.SSR_TRMAC_LAST_DT,SYSDATE)
FROM PS_TERM_TBL STOP WHERE STOP.INSTITUTION = 'UMS05'
AND STOP.ACAD_CAREER = 'UGRD' AND STOP.STRM = '1710';
The fields from PS_ACAD_PROG are:
ADM_APPL_NBR,
INSTITUTION,
CAMPUS,
APPL_PROG_NBR,
EFFDT,
EFFSEQ,
ACAD_PROG,
PROG_STATUS,
PROG_ACTION,
ACTION_DT,
PROG_REASON,
ADMIT_TERM,
EXP_GRAD_TERM,
REQ_TERM,
ACAD_LOAD_APPR,
COMPLETION_TERM,
ACAD_PROG_DUAL,
JOINT_PROG_APPR,
NVL(DEGR_CHKOUT_STAT,' ')
The PS_ADM_APPL_DATA table is joined to PS_ACAD_PROG and returns these fields:
NVL(ADM_APPL_CTR,' ') ,
NVL(ADMIT_TYPE,' '),
NVL(LAST_SCH_ATTEND,' ') as EXT_ORG_ID,
NVL(GRADUATION_DT,TO_DATE('01-JAN-1901 ','DD-MON-YYYY'))
The table PS_UM_ENRL_PIN is joined to PS_STDNT_CAR_TERM and returns these fields
NVL(PIN.PIN_NUM,0),
NVL(PIN.VALIDATED,' '),
NVL(PIN.VALIDATED_DT,TO_DATE('01-JAN-1901 ','DD-MON-YYYY'))
The join relationships between the tables is shown in the following (which does not include the highest
ACAD_CAREER logic):
SELECT *
FROM PS_STDNT_CAR_TERM STUCAR,
PS_PERSONAL_DATA STA,
PS_ACAD_PROG STC,
PS_ADM_APPL_DATA APX,
PS_UM_ENRL_PIN PIN
WHERE STUCAR.EMPLID = STC.EMPLID(+)
AND STUCAR.ACAD_CAREER = STC.ACAD_CAREER(+)
AND STUCAR.STDNT_CAR_NBR = STC.STDNT_CAR_NBR(+)
AND STUCAR.INSTITUTION = STC.INSTITUTION(+)
AND STUCAR.EMPLID = STA.EMPLID(+)
AND STC.EMPLID = APX.EMPLID(+)
AND STC.ACAD_CAREER = APX.ACAD_CAREER(+)
AND STC.ADM_APPL_NBR = APX.ADM_APPL_NBR(+)
AND (STC.EFFDT = (SELECT MAX(STC_ED.EFFDT)
FROM PS_ACAD_PROG STC_ED
WHERE STC.EMPLID = STC_ED.EMPLID
AND STC.ACAD_CAREER = STC_ED.ACAD_CAREER
AND STC.STDNT_CAR_NBR = STC_ED.STDNT_CAR_NBR
AND STC.INSTITUTION = STC_ED.INSTITUTION
AND STC_ED.EFFDT <=
(SELECT LEAST(STOP.SSR_TRMAC_LAST_DT, SYSDATE)
FROM PS_TERM_TBL STOP
WHERE STOP.INSTITUTION = STUCAR.INSTITUTION
AND STOP.ACAD_CAREER = STUCAR.ACAD_CAREER
AND STOP.STRM = STUCAR.STRM)) OR
STC.EFFDT IS NULL)
AND (STC.EFFSEQ = (SELECT MAX(STC_ES.EFFSEQ)
FROM PS_ACAD_PROG STC_ES
WHERE STC.EMPLID = STC_ES.EMPLID
AND STC.ACAD_CAREER = STC_ES.ACAD_CAREER
AND STC.STDNT_CAR_NBR = STC_ES.STDNT_CAR_NBR
AND STC.INSTITUTION = STC_ES.INSTITUTION
AND STC.EFFDT = STC_ES.EFFDT) OR
STC.EFFSEQ IS NULL)
AND STUCAR.STRM BETWEEN '1810' AND '1810'
AND PIN.INSTITUTION(+) = STUCAR.INSTITUTION
AND PIN.EMPLID(+) = STUCAR.EMPLID
AND PIN.STRM(+) = STUCAR.STRM
;
Many of the additional fields in the extract are calculated by SQR procedures. In lieu of the SQR code I
will give SQL equivalents where I can.
The extract contains some credit hour calculations that are summed from the student course table instead of coming directly from PS_STDNT_CAR_TERM.
The calculation does not include dropped courses. The calculation sums for all ACAD_CAREERs except for UM_UNT_TAK_CAREER.
The calculated fields are:
UM_UMS01_UNITS,
UM_UMS01_FLG,
UM_UMS02_UNITS,
UM_UMS02_FLG,
UM_UMS03_UNITS,
UM_UMS03_FLG,
UM_UMS04_UNITS,
UM_UMS04_FLG,
UM_UMS05_UNITS,
UM_UMS05_FLG,
UM_UMS06_UNITS,
UM_UMS06_FLG,
UM_UMS07_UNITS,
UM_UMS07_FLG,
UM_UNT_TAK_PRG_RC,
UM_UNT_TAK_CAREER
This SQL is similar to the SQR procedure in umstuext.sqr where you are supplying values for INSTITUTION, ACAD_CAREER, EMPLID and STRM from the
main loop.
SELECT
MULTI.STRM,
MULTI.INSTITUTION,
MULTI.ACAD_CAREER,
MULTI.EMPLID,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS01' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS01_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS01' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS01_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS02' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS02_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS02' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS02_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS03' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS03_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS03' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS03_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS04' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS04_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS04' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS04_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS05' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS05_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS05' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS05_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS06' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS06_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS06' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS06_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = 'UMS07' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) UMS07_UNT_TAKEN_PRGRSS,
CASE WHEN SUM(CASE WHEN MULTI.INSTITUTION = 'UMS07' AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END) > 0 THEN 'Y' ELSE 'N' END UMS07_FLG,
SUM(CASE WHEN MULTI.INSTITUTION = &1 AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS ELSE 0 END ) UM_UNT_TAK_PRGR_RC,
SUM(CASE WHEN MULTI.INSTITUTION = &1 AND MULTI.ACAD_CAREER <> 'NCRD' THEN MULTI.UNT_PRGRSS_FA ELSE 0 END ) UM_UNT_TAKEN_FA_ROWC,
SUM(CASE WHEN MULTI.INSTITUTION = &1 AND MULTI.ACAD_CAREER = &2 THEN MULTI.UNT_PRGRSS_FA ELSE 0 END ) UM_UNT_TAK_CAREER
FROM PS_STDNT_ENRL MULTI
WHERE MULTI.EMPLID = &3
AND MULTI.STRM = &4
AND MULTI.STDNT_ENRL_STATUS = 'E'
AND (MULTI.EARN_CREDIT = 'Y'
OR (MULTI.UNITS_ATTEMPTED <> 'N' AND MULTI.AUDIT_GRADE_BASIS = 'N') AND MULTI.ENRL_DROP_DT IS NULL )
GROUP BY
MULTI.STRM,
MULTI.INSTITUTION,
MULTI.ACAD_CAREER,
MULTI.EMPLID
UM_ACAD_LOAD_RC is used to calculate academic load:
if #um_unt_taken_prgrss_rowc >= 12
Let $UM_ACAD_LOAD_RC = 'F'
else
if (&stucar.acad_level_bot = 'GR' or &stucar.acad_level_bot = 'MAS' or &stucar.acad_level_bot = 'PHD') and #um_unt_taken_prgrss_rowc >= 6
Let $UM_ACAD_LOAD_RC = 'F'
else
Let $UM_ACAD_LOAD_RC = 'P'
end-if
end-if
UM_ACAD_LVL_DESCR is a description of ACAD_LEVEL_BOT . The equivalent SQL is:
SELECT XLATLONGNAME
FROM PSXLATITEM XLT
WHERE XLT.FIELDNAME = 'ACADEMIC_LEVEL'
AND XLT.EFFDT = (SELECT MAX(XLT_ED.EFFDT)
FROM PSXLATITEM XLT_ED
WHERE XLT_ED.FIELDVALUE = XLT.FIELDVALUE
AND XLT_ED.FIELDNAME = XLT.FIELDNAME
AND XLT.EFFDT <= SYSDATE)
AND FIELDVALUE = &1
;
If the PS_ADDRESSES table has an active address that address is used instead of the one from PS_PERSONAL_DATA. The equivalent SQL is:
select a.emplid,
decode(a.address_type, 'LOCL', '1', 'RESH', '2', 'HOME', '3', 'BILL', '4', 'MAIL', '5', 'CHK', '6', 'BUSN', '7', 'CAMP', '8', '9') crackerjack, !JAJ REPORT-919
a.address1,
a.address2,
a.address3,
a.address4,
a.city,
a.state,
a.postal,
a.county,
a.country
from sysadm.ps_addresses a
where a.effdt = (select max(miss_ed.effdt)
from sysadm.ps_addresses miss_ed
where miss_ed.emplid = a.emplid
and miss_ed.address_type = a.address_type)
and a.eff_status = 'A') miss
inner join (select
misd.emplid,
min(decode(misd.address_type, 'LOCL', '1', 'RESH', '2', 'HOME', '3', 'BILL', '4', 'MAIL', '5', 'CHK', '6', 'BUSN', '7', 'CAMP', '8', '9')) crackerjack
from sysadm.ps_addresses misd
where misd.effdt = (select max(misd_ee.effdt)
from sysadm.ps_addresses misd_ee
where misd_ee.emplid = misd.emplid
and misd_ee.address_type = misd.address_type
and misd_ee.eff_status = 'A')
group by misd.emplid) mise
on miss.emplid = mise.emplid
and miss.crackerjack = mise.crackerjack
order by 1, 2
The fields ADM_APPL_CTR and ADMIT_TYPE are recalculated when ADMT_TYPE <= ' ' with the following SQL equivalent:
SELECT NVL(VAP.ADM_APPL_CTR, ' ') ADM_APPL_CTR,
NVL(VAP.ADMIT_TYPE, ' ') ADMIT_TYPE,
NVL(VAP.ADM_APPL_DT, TO_DATE('01-JAN-1901 ', 'DD-MON-YYYY')) ADM_APPL_DT
FROM PS_ADM_APPL_DATA VAP
WHERE VAP.INSTITUTION = &IN_INSTITUTION
AND VAP.ACAD_CAREER = &IN_CAREER
AND VAP.EMPLID = &IN_EMPLID
AND VAP.ADM_APPL_DT =
(SELECT MAX(VAP_IN.ADM_APPL_DT)
FROM PS_ADM_APPL_DATA VAP_IN
WHERE VAP_IN.EMPLID = VAP.EMPLID
AND VAP_IN.ACAD_CAREER = VAP.ACAD_CAREER
AND VAP_IN.INSTITUTION = VAP.INSTITUTION
AND VAP_IN.ADM_APPL_DT <=
(SELECT ENDO.TERM_END_DT
FROM PS_TERM_TBL ENDO
WHERE ENDO.INSTITUTION = &IN_INSTITUTION
AND ENDO.ACAD_CAREER = &IN_CAREER
AND ENDO.STRM = &IN_STRM));
The field UM_PREVIOUS_NAME is calculated with the following SQL equivalent:
SELECT SB1.NAME
FROM PS_NAMES SB1
WHERE SB1.NAME_TYPE = 'FR1'
AND SB1.EMPLID = &EMPLID
AND SB1.EFFDT = (SELECT MAX(SB1_ED.EFFDT)
FROM PS_NAMES SB1_ED
WHERE SB1.EMPLID = SB1_ED.EMPLID
AND SB1.NAME_TYPE = SB1_ED.NAME_TYPE
AND SB1_ED.EFFDT <= SYSDATE)
The fields EMAIL_ADDR and UM_EMAIL_HOM_ADDR are calculated with the following SQL equivalent:
SELECT
SB2B.EMPLID,
MAX(SB2B.PREF_EMAIL) PREF_EMAIL,
MAX(SB2B.HOME_EMAIL) HOME_EMAIL
FROM (SELECT
SB2A.EMPLID,
SB2A.EMAIL_ADDR,
SB2A.PREF_EMAIL_FLAG,
SB2A.E_ADDR_TYPE,
DECODE(SB2A.PREF_EMAIL_FLAG, 'Y', SB2A.EMAIL_ADDR, ' ') PREF_EMAIL,
DECODE(SB2A.E_ADDR_TYPE, 'HOME', SB2A.EMAIL_ADDR, ' ') HOME_EMAIL
FROM SYSADM.PS_EMAIL_ADDRESSES SB2A
WHERE (SB2A.PREF_EMAIL_FLAG = 'Y'
OR SB2A.E_ADDR_TYPE = 'HOME')
ORDER BY 1) SB2B
GROUP BY SB2B.EMPLID
ORDER BY 1
The fields UM_NID_USA and UM_NID_CAN are calculated with the following SQL equivalent:
select
SB4.EMPLID,
SB4.NATIONAL_ID,
SB4.COUNTRY,
CASE
WHEN SB4.NATIONAL_ID > ' ' AND SB4.COUNTRY = 'USA' THEN SB4.NATIONAL_ID
ELSE ' '
END NID_USA,
CASE
WHEN SB4.NATIONAL_ID > ' ' AND SB4.COUNTRY = 'CAN' THEN SB4.NATIONAL_ID
ELSE ' '
END NID_CAN
FROM SYSADM.PS_PERS_NID SB4
WHERE SB4.PRIMARY_NID = 'Y'
The fields ETHNIC_GRP_CD, ETHNIC_CATEGORY, UM_ETHNIC_DESCR, ETHNIC_GROUP, HISP_LATINO, UM_MULTIPLE_ETHNIC, UM_ETH_GRP_DESCR are calculated with the following SQL equivalent:
WITH X AS
(SELECT SB6.EMPLID,
SB6.ETHNIC_GRP_CD,
SB7.ETHNIC_CATEGORY,
SB7.DESCR50 UM_ETHNIC_DESCR,
SB7.ETHNIC_GROUP,
COUNT(DISTINCT(CASE
WHEN ETHNIC_GROUP > ' ' AND ETHNIC_GROUP <> '6' AND
ETHNIC_GROUP <> '3' THEN
ETHNIC_GROUP
ELSE
NULL
END)) OVER(PARTITION BY EMPLID) RACE_COUNT,
MAX(CASE
WHEN SB7.ETHNIC_GROUP = '3' THEN
'Y'
ELSE
' '
END) OVER(PARTITION BY EMPLID) HISP_LATINO
FROM PS_DIVERS_ETHNIC SB6, PS_ETHNIC_GRP_TBL SB7
WHERE SB6.ETHNIC_GRP_CD = SB7.ETHNIC_GRP_CD
AND SB6.ETHNIC_GRP_CD <> 'NOTHIS'
AND SB7.EFFDT = (SELECT MAX(SB7_ED.EFFDT)
FROM PS_ETHNIC_GRP_TBL SB7_ED
WHERE SB7.SETID = SB7_ED.SETID
AND SB7.ETHNIC_GRP_CD = SB7_ED.ETHNIC_GRP_CD
AND SB7_ED.EFFDT <= SYSDATE)
AND SB7.SETID = 'USA'),
P AS
(SELECT FIELDVALUE, XLATLONGNAME
FROM PSXLATITEM XLT
WHERE XLT.FIELDNAME = 'ETHNIC_GROUP'
AND XLT.EFFDT = (SELECT MAX(XLT_ED.EFFDT)
FROM PSXLATITEM XLT_ED
WHERE XLT_ED.FIELDVALUE = XLT.FIELDVALUE
AND XLT_ED.FIELDNAME = XLT.FIELDNAME
AND XLT.EFFDT <= SYSDATE))
SELECT X.EMPLID,
X.ETHNIC_GRP_CD,
X.ETHNIC_CATEGORY,
X.UM_ETHNIC_DESCR,
X.ETHNIC_GROUP,
P.XLATLONGNAME UM_ETH_GRP_DESCR,
CASE
WHEN X.RACE_COUNT > 1 THEN
'Y'
ELSE
'N'
END UM_MULTIPLE_ETHNIC,
X.HISP_LATINO
FROM X,P
WHERE P.FIELDVALUE(+) = X.ETHNIC_GROUP
AND X.EMPLID = &EMPLID;
The fields UM_COUNTRY_USA, CITIZENSHIP_STATUS,UM_COUNTRY_OTH and UM_CITIZEN_STAT_OT are calculated with the following SQL equivalent:
select
MAX(case when SB8.COUNTRY = 'USA' then SB8.COUNTRY else ' ' end) UM_COUNTRY_USA,
MAX(case when SB8.COUNTRY = 'USA' then SB8.CITIZENSHIP_STATUS else ' ' end) CITIZENSHIP_STATUS,
MAX(case when SB8.COUNTRY <> 'USA' then SB8.COUNTRY else ' ' end) UM_COUNTRY_OTH,
MAX(case when SB8.COUNTRY <> 'USA' then SB8.CITIZENSHIP_STATUS else ' ' end) UM_CITIZEN_STAT_OT
FROM PS_CITIZENSHIP SB8
WHERE SB8.EMPLID = &EMPLID;
The field STATE_DESCR1 is calculated as follows:
SELECT DESCR STATE_DESCR1 FROM
PS_STATE_TBL WHERE COUNTRY = 'USA' AND STATE = &STATE;
The field COUNTRY_DESCR1 is calculated as follows:
select DESCR COUNTRY_DESCR1 from PS_COUNTRY_TBL
where COUNTRY = &COUNTRY;
The field INSTITUTION_DESCR is calculated as follows:
SELECT DESCR INSTITUTION_DESCR
FROM PS_INSTITUTION_TBL INS
where INS.EFFDT = (select max(ins_ed.effdt)
from ps_institution_tbl ins_ed
where ins_ed.institution = ins.institution)
AND INS.EFF_STATUS = 'A'
AND INS.INSTITUTION = &INSTITUTION;
The fields EFFECTIVE_TERM, RESIDENCY, RESIDENCY_DT, ADMISSION_RES, ADMISSION_EXCPT are calculated as follows:
select
SB9.EFFECTIVE_TERM,
SB9.TUITION_RES as RESIDENCY,
SB9.RESIDENCY_DT,
SB9.ADMISSION_RES,
SB9.ADMISSION_EXCPT
FROM PS_RESIDENCY_OFF SB9
WHERE
SB9.EMPLID = &EMPLID
AND SB9.ACAD_CAREER = &ACAD_CAREER
AND SB9.INSTITUTION = &INSTITUTION
AND (SB9.EFFECTIVE_TERM = (SELECT MAX(EB9.EFFECTIVE_TERM) FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = SB9.EMPLID AND EB9.ACAD_CAREER = SB9.ACAD_CAREER and EB9.INSTITUTION = SB9.INSTITUTION
AND EB9.EFFECTIVE_TERM <= &STRM)
OR (SB9.EFFECTIVE_TERM = (SELECT MAX(EC9.EFFECTIVE_TERM) FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = SB9.EMPLID AND EC9.ACAD_CAREER = EC9.ACAD_CAREER AND EC9.INSTITUTION = SB9.INSTITUTION)
AND NOT EXISTS (SELECT 'X' FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID=SB9.EMPLID AND ED9.ACAD_CAREER = SB9.ACAD_CAREER AND ED9.INSTITUTION = SB9.INSTITUTION
AND ED9.EFFECTIVE_TERM <= &STRM))
)
;
The fields UM_ACAD_PROGP_DESC, ACAD_GROUP, UM_ACAD_GRP_DESCR and LEVEL_LOAD_RULE as follows:
select
SB10.DESCR UM_ACAD_PROGP_DESC,
SB10.ACAD_GROUP ACAD_GROUP,
SB10.LEVEL_LOAD_RULE LEVEL_LOAD_RULE,
CR10.DESCR UM_ACAD_GRP_DESCR
FROM PS_ACAD_PROG_TBL SB10, PS_ACAD_GROUP_TBL CR10
WHERE SB10.ACAD_PROG = &Acad_prog_primary
AND SB10.INSTITUTION = &INSTITUTION
AND SB10.INSTITUTION = CR10.INSTITUTION(+)
AND SB10.ACAD_GROUP = CR10.ACAD_GROUP(+)
AND SB10.EFFDT =
(SELECT MAX(SB10_ED.EFFDT) FROM PS_ACAD_PROG_TBL SB10_ED
WHERE SB10.INSTITUTION = SB10_ED.INSTITUTION
AND SB10.ACAD_PROG = SB10_ED.ACAD_PROG
AND SB10_ED.EFFDT <= SYSDATE)
AND ( CR10.EFFDT =
(SELECT MAX(CR10_ED.EFFDT) FROM PS_ACAD_GROUP_TBL CR10_ED
WHERE CR10.INSTITUTION = CR10_ED.INSTITUTION
AND CR10.ACAD_GROUP = CR10_ED.ACAD_GROUP
AND CR10_ED.EFFDT <= SYSDATE)
or CR10.EFFDT is null);
The field ACAD_PROG_DESCR is calculated as follows:
select
SB10.DESCR ACAD_PROG_DESCR
FROM PS_ACAD_PROG_TBL SB10
WHERE SB10.ACAD_PROG = &Acad_prog
AND SB10.INSTITUTION = &INSTITUTION
AND SB10.EFFDT =
(SELECT MAX(SB10_ED.EFFDT) FROM PS_ACAD_PROG_TBL SB10_ED
WHERE SB10.INSTITUTION = SB10_ED.INSTITUTION
AND SB10.ACAD_PROG = SB10_ED.ACAD_PROG
AND SB10_ED.EFFDT <= SYSDATE);
The fields RESPONSE_REASON,RESPONSE_DT and UM_RSP_REASN_DESCR are calculated as follows:
SELECT STUCAR.EMPLID,
STUCAR.ACAD_CAREER,
STUCAR.STDNT_CAR_NBR,
STUCAR.INSTITUTION,
STUCAR.STRM,
SR16.RESPONSE_REASON,
SR16.RESPONSE_DT,
SR16.SEQNUM
FROM SYSADM.PS_STDNT_CAR_TERM STUCAR
LEFT OUTER JOIN (SELECT
SB16.EMPLID,
SB16.ACAD_CAREER,
SB16.STDNT_CAR_NBR,
SB16.RESPONSE_REASON,
SB16.RESPONSE_DT,
SB16.SEQNUM
FROM SYSADM.PS_STDNT_RESPONSE SB16
WHERE SB16.RESPONSE_DT = (SELECT MAX(SB16A.RESPONSE_DT)
FROM SYSADM.PS_STDNT_RESPONSE SB16A
WHERE SB16.EMPLID = SB16A.EMPLID
AND SB16.ACAD_CAREER = SB16A.ACAD_CAREER
AND SB16.STDNT_CAR_NBR = SB16A.STDNT_CAR_NBR)
AND SB16.SEQNUM = (SELECT MAX(SB16B.SEQNUM)
FROM SYSADM.PS_STDNT_RESPONSE SB16B
WHERE SB16.EMPLID = SB16B.EMPLID
AND SB16.ACAD_CAREER = SB16B.ACAD_CAREER
AND SB16.STDNT_CAR_NBR = SB16B.STDNT_CAR_NBR
AND SB16.RESPONSE_DT = SB16B.RESPONSE_DT)
ORDER BY 1, 2, 3) SR16
ON SR16.EMPLID = STUCAR.EMPLID
AND SR16.ACAD_CAREER = STUCAR.ACAD_CAREER
AND SR16.STDNT_CAR_NBR = STUCAR.STDNT_CAR_NBR) SR16A
LEFT OUTER JOIN SYSADM.PS_RESP_RSN_TBL UJ
ON SR16A.ACAD_CAREER = UJ.ACAD_CAREER
AND SR16A.RESPONSE_REASON = UJ.RESPONSE_REASON
AND SR16A.INSTITUTION = UJ.INSTITUTION
WHERE SR16A.RESPONSE_REASON IS NOT NULL
The fields UM_LAST_SCH_TYPE, UM_EXT_ORGID_DESCR and UM_LAST_SCH_DESCR are calculated as follows:
SELECT
NVL(LBS.LS_SCHOOL_TYPE,' ') UM_LAST_SCH_TYPE,
NVL(LAS.DESCR,' ') UM_EXT_ORGID_DESCR,
NVL(LAS.DESCR,' ') UM_LAST_SCH_DESCR
FROM PS_EXT_ORG_PRI_VW LAS
, PS_EXT_ORG_TBL_ADM LBS
WHERE LAS.EFFDT = ( SELECT MAX(A_AED.EFFDT)
FROM PS_EXT_ORG_PRI_VW A_AED WHERE A_AED.EXT_ORG_ID = LAS.EXT_ORG_ID
AND A_AED.EFFDT <= SYSDATE)
AND LAS.EFF_STATUS = 'A'
AND LAS.EXT_ORG_ID = LBS.EXT_ORG_ID
AND LBS.EFFDT = ( SELECT MAX(LBS_ED.EFFDT)
FROM PS_EXT_ORG_TBL_ADM LBS_ED WHERE LBS_ED.EXT_ORG_ID = LBS.EXT_ORG_ID AND LBS_ED.EFFDT <= SYSDATE)
AND LBS.EFF_STATUS = 'A'
AND LAS.EXT_ORG_ID = &LAST_SCH_ATTEND;
The fields UM_MIN_ACAD_PROG, UM_MIN_ACAD_PLAN and UM_MIN_ACAD_SUBPLN are calculated as follows:
select NVL(MNS12.ACAD_PLAN, ' ') MIN_ACAD_PLAN,
NVL(MNS14.ACAD_SUB_PLAN, ' ') MIN_ACAD_SUB_PLAN,
MNSC.ACAD_PROG MIN_ACAD_PROG
FROM PS_ACAD_PROG MNSC,
PS_ACAD_PLAN MNS12,
PS_ACAD_PLAN_TBL MNS13,
PS_ACAD_SUBPLAN MNS14,
PS_ACAD_SUBPLN_TBL MNS15
WHERE MNSC.EMPLID = &EMPLID
AND MNSC.ACAD_CAREER = &ACAD_CAREER
AND MNSC.STDNT_CAR_NBR = &STDNT_CAR_NBR
AND MNS13.ACAD_PLAN_TYPE in ('MAJ', 'DMJ', 'SP', 'PRP', 'CER')
AND MNSC.EFFDT =
(SELECT MIN(MNSC_ED.EFFDT)
FROM PS_ACAD_PROG MNSC_ED
WHERE MNSC.EMPLID = MNSC_ED.EMPLID
AND MNSC.ACAD_CAREER = MNSC_ED.ACAD_CAREER
AND MNSC.STDNT_CAR_NBR = MNSC_ED.STDNT_CAR_NBR)
AND MNSC.EFFSEQ = (SELECT MIN(MNSC_ES.EFFSEQ)
FROM PS_ACAD_PROG MNSC_ES
WHERE MNSC.EMPLID = MNSC_ES.EMPLID
AND MNSC.ACAD_CAREER = MNSC_ES.ACAD_CAREER
AND MNSC.STDNT_CAR_NBR = MNSC_ES.STDNT_CAR_NBR
AND MNSC.EFFDT = MNSC_ES.EFFDT)
AND MNS12.EMPLID(+) = MNSC.EMPLID
AND MNS12.ACAD_CAREER(+) = MNSC.ACAD_CAREER
AND MNS12.STDNT_CAR_NBR(+) = MNSC.STDNT_CAR_NBR
AND MNS13.INSTITUTION(+) = MNSC.INSTITUTION
AND (MNS12.EFFDT =
(SELECT MIN(MNS12_ED.EFFDT)
FROM PS_ACAD_PLAN MNS12_ED
WHERE MNS12.EMPLID = MNS12_ED.EMPLID
AND MNS12.ACAD_CAREER = MNS12_ED.ACAD_CAREER
AND MNS12.ACAD_PLAN = MNS12_ED.ACAD_PLAN
AND MNS12.STDNT_CAR_NBR = MNS12_ED.STDNT_CAR_NBR) or
MNS12.EFFDT is NULL)
AND (MNS12.EFFSEQ =
(SELECT MIN(MNS12_ES.EFFSEQ)
FROM PS_ACAD_PLAN MNS12_ES
WHERE MNS12.EMPLID = MNS12_ES.EMPLID
AND MNS12.ACAD_CAREER = MNS12_ES.ACAD_CAREER
AND MNS12.ACAD_PLAN = MNS12_ES.ACAD_PLAN
AND MNS12.STDNT_CAR_NBR = MNS12_ES.STDNT_CAR_NBR
AND MNS12.EFFDT = MNS12_ES.EFFDT) or MNS12.EFFSEQ is null)
AND MNS12.ACAD_PLAN = MNS13.ACAD_PLAN
AND MNS13.EFFDT =
(SELECT MIN(MNS13_ED.EFFDT)
FROM PS_ACAD_PLAN_TBL MNS13_ED
WHERE MNS13.INSTITUTION = MNS13_ED.INSTITUTION
AND MNS13.ACAD_PLAN = MNS13_ED.ACAD_PLAN)
AND MNS12.EMPLID = MNS14.EMPLID(+)
AND MNS12.ACAD_CAREER = MNS14.ACAD_CAREER(+)
AND MNS12.STDNT_CAR_NBR = MNS14.STDNT_CAR_NBR(+)
AND MNS12.ACAD_PLAN = MNS14.ACAD_PLAN(+)
AND MNS12.effdt = MNS14.effdt(+)
and MNS12.effseq = MNS14.effseq(+)
AND MNS14.ACAD_PLAN = MNS15.ACAD_PLAN(+)
AND MNS14.ACAD_SUB_PLAN = MNS15.ACAD_SUB_PLAN(+)
AND (MNS15.EFFDT =
(SELECT MIN(MNS15_ED.EFFDT)
FROM PS_ACAD_SUBPLN_TBL MNS15_ED
WHERE MNS15.INSTITUTION = MNS15_ED.INSTITUTION
AND MNS15.ACAD_PLAN = MNS15_ED.ACAD_PLAN
AND MNS15.ACAD_SUB_PLAN = MNS15_ED.ACAD_SUB_PLAN) OR
MNS15.EFFDT IS NULL);
The fields UM_ORIG_ACAD_PLAN and UM_ORIG_ACAD_PROG = ' ' are calculated as follows:
WITH MOO AS
(SELECT ORIG.EMPLID,
ORIG.STDNT_CAR_NBR,
ORG13.ACAD_PLAN_TYPE,
NVL(ORG12.ACAD_PLAN, ' ') UM_ORIG_ACAD_PLAN,
NVL(ORIG.ACAD_PROG, ' ') UM_ORIG_ACAD_PROG,
DECODE(ORG13.ACAD_PLAN_TYPE,
'MAJ',
1,
'DMJ',
2,
'SP',
3,
'PRP',
4,
'CON',
5,
'COS',
6,
'HON',
7,
'RTC',
8,
'CER',
9,
10) SORTX
FROM PS_ACAD_PROG ORIG, PS_ACAD_PLAN ORG12, PS_ACAD_PLAN_TBL ORG13
WHERE ORIG.EMPLID = &EMPLID
-- AND ORIG.ACAD_CAREER = &ACAD_CAREER
AND ORIG.STDNT_CAR_NBR = &STDNT_CAR_NBR
AND ORIG.EFFDT =
(SELECT MAX(ORIG_ED.EFFDT)
FROM PS_ACAD_PROG ORIG_ED
WHERE ORIG.EMPLID = ORIG_ED.EMPLID
AND ORIG.ACAD_CAREER = ORIG_ED.ACAD_CAREER
AND ORIG.STDNT_CAR_NBR = ORIG_ED.STDNT_CAR_NBR
AND ORIG_ED.EFFDT <= (SELECT OD.TERM_END_DT
FROM PS_TERM_TBL OD
WHERE OD.INSTITUTION = ORIG.INSTITUTION
AND OD.ACAD_CAREER = ORIG.ACAD_CAREER
AND OD.STRM = &STRM))
AND ORIG.EFFSEQ = (SELECT MAX(ORG_ES.EFFSEQ)
FROM PS_ACAD_PROG ORG_ES
WHERE ORIG.EMPLID = ORG_ES.EMPLID
AND ORIG.ACAD_CAREER = ORG_ES.ACAD_CAREER
AND ORIG.STDNT_CAR_NBR = ORG_ES.STDNT_CAR_NBR
AND ORIG.EFFDT = ORG_ES.EFFDT)
AND ORG13.ACAD_PLAN_TYPE IN ('MAJ', 'DMJ', 'SP', 'PRP', 'CER')
AND ORG12.EMPLID = ORIG.EMPLID
AND ORG12.ACAD_CAREER = ORIG.ACAD_CAREER
AND ORG12.STDNT_CAR_NBR = ORIG.STDNT_CAR_NBR
AND ORG13.INSTITUTION = ORIG.INSTITUTION
AND ORG12.EFFDT = ORIG.EFFDT
AND ORG12.EFFSEQ = ORIG.EFFSEQ
AND ORG12.ACAD_PLAN = ORG13.ACAD_PLAN
AND ORG13.EFFDT = (SELECT MAX(ORG13_ED.EFFDT)
FROM PS_ACAD_PLAN_TBL ORG13_ED
WHERE ORG13.INSTITUTION = ORG13_ED.INSTITUTION
AND ORG13.ACAD_PLAN = ORG13_ED.ACAD_PLAN
AND ORG13_ED.EFFDT <= SYSDATE))
SELECT M.EMPLID,
M.STDNT_CAR_NBR,
M.UM_ORIG_ACAD_PLAN,
M.UM_ORIG_ACAD_PROG,
M.SORTX
FROM MOO M
WHERE M.SORTX =
(SELECT MIN(CURLY.SORTX) FROM MOO CURLY WHERE CURLY.EMPLID = M.EMPLID)
ORDER BY 1, 2
The fields UM_TUITION_PAID and UM_FEES_PAID are calculated as follows:
select
ctf.emplid, --
SUM(case when c.um_scrty_3 = 'TUI' then ctf.item_amt else 0 end) TUITION_PAID,
SUM(case when c.um_scrty_3 <> 'TUI' then ctf.item_amt else 0 end) FEES_PAID
FROM PS_ITEM_SF ctf, ps_um_ityp_treends c
WHERE
CTF.ITEM_TERM = &STRM
AND CTF.BUSINESS_UNIT = &INSTITUTION
AND CTF.COMMON_ID = &EMPLID
AND CTF.ACAD_CAREER = &ACAD_CAREER
AND CTF.SA_ID_TYPE = 'P'
AND ctf.business_unit = c.setid(+)
AND ctf.item_type = c.item_type(+)
and c.um_scrty_3 in ('TUI','ADM','ENR')
group by ctf.emplid
order by 1, 2
;
The fields UM_UNT_DIST, UM_UNT_WEB and UM_UNT_ITV are calculated as follows:
WITH FOO AS (
SELECT
CU.EMPLID,
CU.ACAD_CAREER,
CU.INSTITUTION,
CU.STRM,
CU.CLASS_NBR,
CU.SESSION_CODE,
CU.STDNT_ENRL_STATUS,
CU.ENRL_DROP_DT,
CU.UNT_PRGRSS,
CU.AUDIT_GRADE_BASIS,
CU.EARN_CREDIT,
CU.UNITS_ATTEMPTED,
CU2.CRSE_ID,
CU2.CRSE_OFFER_NBR,
CU2.CLASS_SECTION,
decode(( SELECT SUM(decode(z.crse_attr ,'DIST' ,1 ,'BRD' ,2 ,0)) FROM ps_class_attribute z WHERE CU2.crse_id = z.crse_id AND CU2.crse_offer_nbr = z.crse_offer_nbr AND CU2.strm = z.strm AND CU2.session_code = z.session_code AND CU2.class_section = z.class_section AND ((z.crse_attr = 'DIST' AND (z.crse_attr_value = 'ITV' OR z.crse_attr_value = 'VIDEOCONF')) OR (z.crse_attr ='BRD'))),1,'R',3,'B',' ') UM_DISTANCE_FLAG,
NVL(( SELECT DISTINCT 'Y' FROM ps_class_attribute z WHERE CU2.crse_id = z.crse_id AND CU2.crse_offer_nbr = z.crse_offer_nbr AND CU2.strm = z.strm AND CU2.session_code = z.session_code AND CU2.class_section = z.class_section AND z.crse_attr IN ('DIST','DIST' )),'N') UM_DIST_ATTR_FLAG,
decode ( ( SELECT MIN( decode(z.crse_attr_value ,'ITV' ,'1' ,'VIDEOCONF' ,'2' ,'ONLINE' ,'3' ,'ONSITE' ,'4' ,'BROADCAST' ,'5' ,'0') ) FROM ps_class_attribute z WHERE CU2.crse_id = z.crse_id AND CU2.crse_offer_nbr = z.crse_offer_nbr AND CU2.strm = z.strm AND CU2.session_code = z.session_code AND CU2.class_section = z.class_section AND ( ( z.crse_attr = 'DIST' AND z.crse_attr_value = 'ITV' )
OR (z.crse_attr = 'DIST' AND z.crse_attr_value = 'VIDEOCONF') OR (z.crse_attr = 'DIST' AND z.crse_attr_value = 'ONLINE') OR (z.crse_attr = 'DIST' AND z.crse_attr_value = 'ONSITE') OR (z.crse_attr ='BRD' AND z.crse_attr_value = 'BROADCAST') ) ),'1','ITV','2','VIDEOCONF','3','ONLINE','4','ONSITE','5','BROADCAST','0','?') UM_IMODE,
CU2.ENRL_CAP,
CU2.ACAD_ORG
FROM PS_STDNT_ENRL CU , PS_CLASS_TBL CU2
WHERE CU.CLASS_NBR = CU2.CLASS_NBR
AND CU.STRM = CU2.STRM
AND CU.SESSION_CODE = CU2.SESSION_CODE
AND CU.INSTITUTION = CU2.INSTITUTION
AND CU.EMPLID = &EMPLID
AND CU.ACAD_CAREER = &ACAD_CAREER
AND CU.INSTITUTION = &INSTITUTION
AND CU.STRM = &STRM
AND CU.STDNT_ENRL_STATUS = 'E'
AND (CU.EARN_CREDIT = 'Y'
or (CU.UNITS_ATTEMPTED <> 'N' AND CU.AUDIT_GRADE_BASIS = 'N') and CU.ENRL_DROP_DT is null ))
SELECT A.EMPLID, A.INSTITUTION, A.ACAD_CAREER,
SUM(case when A.UM_DIST_ATTR_FLAG = 'Y' then A.UNT_PRGRSS else 0 end) DIST_HRS,
SUM(case when A.UM_DISTANCE_FLAG = 'R' then A.UNT_PRGRSS else 0 end) ITV_HRS,
SUM(case when A.UM_IMODE = 'ONLINE' then A.UNT_PRGRSS else 0 end) WEB_HRS
from FOO A
group by A.EMPLID, A.INSTITUTION, A.ACAD_CAREER
The fields ADVISOR_ID, UM_ADVISR_LST_NAME, UM_ADVISR_FST_NAME, UM_ADVISR_MID_NAME and UM_ADVISR_NAME_SFX are calculated as follows:
SELECT
B.EMPLID,
B.INSTITUTION,
B.ACAD_CAREER,
CASE WHEN B.ADVISOR_ID > ' ' THEN B.ADVISOR_ID else B.COMMITTEE_ID end ADVISOR_ID,
NVL(C.LAST_NAME,' ') LAST_NAME,
NVL(C.FIRST_NAME,' ') FIRST_NAME,
NVL(C.MIDDLE_NAME,' ') MIDDLE_NAME,
NVL(C.NAME_SUFFIX,' ') NAME_SUFFIX
FROM PS_STDNT_ADVR_HIST B, PS_NAMES C, PS_TERM_TBL CSTOP
WHERE
B.EMPLID = &EMPLID
AND
B.INSTITUTION = &INSTITUTION
AND B.ACAD_CAREER = &ACAD_CAREER
AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_ADVR_HIST B_ED
WHERE B_ED.EMPLID = B.EMPLID
AND B_ED.INSTITUTION = B.INSTITUTION
AND B_ED.EFFDT <= CSTOP.TERM_END_DT
)
AND B.STDNT_ADVISOR_NBR = (SELECT MIN(B_N.STDNT_ADVISOR_NBR) FROM PS_STDNT_ADVR_HIST B_N
WHERE B_N.EMPLID = B.EMPLID
AND B_N.INSTITUTION = B.INSTITUTION
AND B_N.EFFDT = B.effdt
)
AND B.ADVISOR_ID = C.EMPLID(+)
AND B.INSTITUTION = CSTOP.INSTITUTION
AND B.ACAD_CAREER = CSTOP.ACAD_CAREER
AND CSTOP.STRM = &STRM
AND (C.NAME_TYPE = 'PRI' OR C.NAME_TYPE IS NULL)
AND (C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_NAMES C_ED
WHERE C_ED.EMPLID = C.EMPLID
AND C_ED.NAME_TYPE = C.NAME_TYPE) OR C.EFFDT IS NULL)
The fields UM_MAX_SAT_MATH, UM_MAX_SAT_VERB, UM_MAX_GRE_QUAN, UM_MAX_GRE_VERB, UM_MAX_SAT_WRIT, UM_MAX_ACT_MATH, UM_MAX_ACT_ENGL, UM_MAX_ACT_COMP, UM_MAX_TOEFL_COMPI
,UM_MAX_TOEFL_COMPP and UM_MAX_TOEFL_COMPC are calculated as follows:
SELECT
MXT.EMPLID,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'MATH' AND MXT.TEST_ID = 'SAT 1' THEN MXT.SCORE else 0 END) UM_MAX_SAT_MATH,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'VERB' AND MXT.TEST_ID = 'SAT 1' THEN MXT.SCORE else 0 END) UM_MAX_SAT_VERB,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'ERWS' AND MXT.TEST_ID = 'SAT 1' THEN MXT.SCORE else 0 END) UM_MAX_SAT_ERWS,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'MSS' AND MXT.TEST_ID = 'SAT 1' THEN MXT.SCORE else 0 END) UM_MAX_SAT_MSS,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'MATH' AND MXT.TEST_ID = 'ACT' THEN MXT.SCORE else 0 END) UM_MAX_ACT_MATH,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'ENGL' AND MXT.TEST_ID = 'ACT' THEN MXT.SCORE else 0 END) UM_MAX_ACT_ENGL,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'COMP' AND MXT.TEST_ID = 'ACT' THEN MXT.SCORE else 0 END) UM_MAX_ACT_COMP,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'COMPI' AND MXT.TEST_ID = 'TOEFL' THEN MXT.SCORE else 0 END) UM_MAX_TOEFL_COMPI,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'COMPP' AND MXT.TEST_ID = 'TOEFL' THEN MXT.SCORE else 0 END) UM_MAX_TOEFL_COMPP,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'COMPC' AND MXT.TEST_ID = 'TOEFL' THEN MXT.SCORE else 0 END) UM_MAX_TOEFL_COMPC,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'VERB' AND MXT.TEST_ID = 'GRE' THEN MXT.SCORE else 0 END) UM_MAX_GRE_VERB,
MAX(CASE WHEN MXT.TEST_COMPONENT = 'QUAN' AND MXT.TEST_ID = 'GRE' THEN MXT.SCORE else 0 END) UM_MAX_GRE_QUAN
FROM PS_STDNT_TEST_COMP MXT
WHERE
MXT.EMPLID = &EMPLID
AND ((MXT.TEST_ID IN( 'SAT 1', 'ACT')AND MXT.TEST_DT >= ADD_MONTHS(SYSDATE,-60))
OR (MXT.TEST_ID = 'TOEFL' AND MXT.TEST_DT >= ADD_MONTHS(SYSDATE,-24))
OR MXT.TEST_ID = 'GRE')
group by MXT.EMPLID
T0 calculate the fields ACAD_PLAN, DECLARE_DT, PLAN_SEQUENCE, STDNT_DEGR, ADVIS_STATUS, UM_ACAD_PLAN_DESCR, ACAD_PLAN_TYPE, DEGREE, ACAD_SUB_PLAN, UM_ACAD_SUBPLN_DSC AND ACAD_SUBPLAN_TYPE :
WITH WIFF AS
(SELECT ST12.EMPLID,
ST12.ACAD_CAREER,
ST12.STDNT_CAR_NBR,
NVL(ST12.ACAD_PLAN, ' ') ACAD_PLAN,
ST12.DECLARE_DT,
ST12.EFFDT,
ST12.PLAN_SEQUENCE,
ST12.REQ_TERM,
ST12.STDNT_DEGR,
ST12.DEGR_CHKOUT_STAT,
ST12.ADVIS_STATUS,
NVL(ST13.DESCR, ' ') UM_ACAD_PLAN_DESCR,
NVL(ST13.ACAD_PLAN_TYPE, ' ') ACAD_PLAN_TYPE,
NVL(ST13.DEGREE, ' ') DEGREE1,
NVL(ST14.ACAD_SUB_PLAN, ' ') ACAD_SUB_PLAN,
NVL(ST15.ACAD_SUBPLAN_TYPE, ' ') ACAD_SUBPLAN_TYPE,
NVL(ST15.DESCR, ' ') UM_ACAD_SUBPLN_DSC,
NVL(DG.EDUCATION_LVL, ' ') EDUCATION_LVL,
ROW_NUMBER() OVER(PARTITION BY ST12.EMPLID, ST12.ACAD_CAREER, ST12.STDNT_CAR_NBR, ST13.INSTITUTION ORDER BY ST12.EFFDT, ST12.EFFSEQ, DECODE(ST13.ACAD_PLAN_TYPE, 'MAJ', 1, 'DMJ', 5, 'SP', 2, 'PRP', 3, 'CER', 4, 6) DESC, ST12.PLAN_SEQUENCE DESC) ROWX
FROM PS_ACAD_PLAN ST12,
PS_ACAD_PLAN_TBL ST13,
PS_ACAD_SUBPLAN ST14,
PS_ACAD_SUBPLN_TBL ST15,
PS_DEGREE_TBL DG
WHERE ST12.EMPLID = &EMPLID
AND ST12.ACAD_CAREER = &ACAD_CAREER
AND ST12.STDNT_CAR_NBR = &STDNT_CAR_NBR
AND ST13.INSTITUTION = &INSTITUTION
AND ST13.ACAD_PLAN_TYPE IN ('MAJ', 'DMJ', 'SP', 'PRP', 'CER')
AND ST12.ACAD_PLAN = ST13.ACAD_PLAN
AND ST13.EFFDT = (SELECT MAX(ST13_ED.EFFDT)
FROM PS_ACAD_PLAN_TBL ST13_ED
WHERE ST13.INSTITUTION = ST13_ED.INSTITUTION
AND ST13.ACAD_PLAN = ST13_ED.ACAD_PLAN
AND ST13_ED.EFFDT <= SYSDATE)
AND ST12.EMPLID = ST14.EMPLID(+)
AND ST12.ACAD_CAREER = ST14.ACAD_CAREER(+)
AND ST12.STDNT_CAR_NBR = ST14.STDNT_CAR_NBR(+)
AND ST12.ACAD_PLAN = ST14.ACAD_PLAN(+)
AND ST12.EFFDT = ST14.EFFDT(+)
AND ST12.EFFSEQ = ST14.EFFSEQ(+)
AND ST14.ACAD_PLAN = ST15.ACAD_PLAN(+)
AND ST14.ACAD_SUB_PLAN = ST15.ACAD_SUB_PLAN(+)
AND (ST15.EFFDT = (SELECT MAX(ST15_ED.EFFDT)
FROM PS_ACAD_SUBPLN_TBL ST15_ED
WHERE ST15.INSTITUTION = ST15_ED.INSTITUTION
AND ST15.ACAD_PLAN = ST15_ED.ACAD_PLAN
AND ST15.ACAD_SUB_PLAN = ST15_ED.ACAD_SUB_PLAN
AND ST15_ED.EFFDT <= SYSDATE) OR
ST15.EFFDT IS NULL)
AND ST13.DEGREE = DG.DEGREE(+)
AND (DG.EFFDT = (SELECT MAX(DG_ED.EFFDT)
FROM PS_DEGREE_TBL DG_ED
WHERE DG.DEGREE = DG_ED.DEGREE) OR DG.EFFDT IS NULL))
SELECT *
FROM WIFF W
WHERE W.ROWX = (SELECT MAX(V.ROWX)
FROM WIFF V
WHERE V.EMPLID = W.EMPLID
AND V.ACAD_CAREER = W.ACAD_CAREER
AND V.STDNT_CAR_NBR = W.STDNT_CAR_NBR)