Created by Miki Yanagi, last modified by Corina C Larsen on Mar 04, 2019
Tables or Views:
a. PS_UM_APPL_EXT_VW
(This view is based onĀ PS_UM_APPL_EXTRACT, and UMAPPEXT uses these 33 tables:
| PS_ACAD_GROUP_TBL |
| PS_ACAD_PLAN |
| PS_ACAD_PLAN_TBL |
| PS_ACAD_PROG |
| PS_ACAD_PROG_TBL |
| PS_ACAD_SUBPLAN |
| PS_ACAD_SUBPLN_TBL |
| PS_ADM_APPL_CMP |
| PS_ADM_APPL_DATA |
| PS_ADM_APPL_PLAN |
| PS_ADM_APPL_PROG |
| PS_ADM_APPL_SBPLAN |
| PS_CITIZENSHIP |
| PS_DIVERS_ETHNIC |
| PS_EMAIL_ADDRESSES |
| PS_ETHNIC_GRP_TBL |
| PS_EXT_ACAD_DATA |
| PS_EXT_ACAD_SUM |
| PS_EXT_ORG_PRI_VW |
| PS_EXT_ORG_TBL |
| PS_EXT_ORG_TBL_ADM |
| PS_NAMES |
| PS_PERS_NID |
| PS_PERSONAL_DATA |
| PS_PERSONAL_PHONE |
| PS_PROG_RSN_TBL |
| PS_RESIDENCY_OFF |
| PS_RESP_RSN_TBL |
| PS_STDNT_CAR_TERM |
| PS_STDNT_RESPONSE |
| PS_STDNT_TEST_COMP |
| PS_TERM_TBL |
| PS_TERM_VAL_TBL |
SQR code for UMAPPEXT Expand source
!************************************************************************
!umappext2.SQR *
!************************************************************************
! DESCRIPTION: Admissions Table Extract *
! OWNER: *
! FREQUENCY: Manual *
!************************************************************************
! SPECIFICATIONS BY: Gregory J. Patterson *
! ISSUE: ISSUE #47 *
! DATE: *
!************************************************************************
! CREATED BY: Peter Andersen *
! CREATION DT: Jan 6, 2006 *
!************************************************************************
! MODIFICATION LOG *
!************************************************************************
! Mod # Programmer Start Date End Date Desription *
! ----- ---------- ---------- ---------- ------------------------- *
! 1 PSA 11/09/06 11/09/06 New field - *
! UM_FST_PROG_ACTION *
! comment = ! ??1109 *
! 2 PSA 02/12/07 New field - *
! UM_FST_PROG_DATE *
! comment = ! ??0212 *
! 3 PSA 05/22/07 Get first ACAD_PLAN ??0522 *
! 4 PSA 06/07/07 More new fields etc ??0607 *
! 5 PSA 10/04/07 16 new status dates *
! remove test scores *
! 6 PSA 11/02/07 New Fields for Withdrawal *
! deferral !??1107 *
! 7 PSA 04/22/08 Use Extract Control Record *
! 8 PSA 06/11/08 Add UM_DISCONTINUE_DATE ??0611*
! 9 PSA 07/15/08 Skip Minor ACAD_PLANs ??0715*
! 10 PSA 06/01/10 Simplify Loops *
! 11 PSA 07/21/10 Remove DDEF from defer logic*
! 12 PSA 09/09/10 Look in both admissions & in*
! student for matric/defr/canc*
! check dates ??0910 *
! 13 PSA 09/10/12 standardize cutoff dates *
! UMREPORT-2 Peter Andersen 09/12*
! *
! 14 PSA 04/30/14 Fix net confirm for defers *
! REPORT-170 PSA 04/14 *
!************************************************************************
#include 'setenv.sqc' ! Set environment
!***********************************************************************
! Begin Setup
!***********************************************************************
Begin-Setup
#include 'setup01a.sqc'
#define DLim ';'
declare-variable
date $input_Date
date $FiveYearsAgo
date $TwoYearsAgo
date $Residency_Dt
date $Response_Dt
date $From_date1
date $From_date2
date $From_date3
date $From_date4
date $From_date5
date $From_date6
date $From_date7
date $From_date8
date $From_date9
date $From_date10
date $to_Dt1
date $to_Dt2
date $to_Dt3
date $to_Dt4
date $to_Dt5
date $to_Dt6
date $to_Dt7
date $to_Dt8
date $to_Dt9
date $to_Dt10
date $TSTDT1X1
date $TSTDT1X2
date $TSTDT1X3
date $TSTDT1X4
date $TSTDT1X5
date $TSTDT2X1
date $TSTDT2X2
date $TSTDT2X3
date $TSTDT2X4
date $TSTDT2X5
date $TSTDT3X1
date $TSTDT3X2
date $TSTDT3X3
date $TSTDT3X4
date $TSTDT3X5
date $TSTDT4X1
date $TSTDT4X2
date $TSTDT4X3
date $TSTDT4X4
date $TSTDT4X5
date $TSTDT5X1
date $TSTDT5X2
date $TSTDT5X3
date $TSTDT5X4
date $TSTDT5X5
date $Fubar_Date
date $First_Prog_Action_Date
text $Input_Trm1
text $Input_Term2
text $input_Flag1
text $input_Flag2
text $First_Prog_Action !??1109
text $Response_Reason_Descr !??0607
text $Academic_Group !??0607
text $Academic_Group_Descr !??0607
date $UM_APP_DATE !??0104
date $UM_COMPLETE_DATE
date $UM_ADMIT_DATE
date $UM_NETCONFIRM_DATE
date $UM_GROSSCONFIRM_DATE
date $UM_ENROLL_DATE
date $UM_DEFER_DATE
date $UM_DENY_DATE
date $UM_DISCONTINUE_DATE
date $UM_WADMINC_DATE
date $UM_WADMTOT_DATE
date $UM_WAPPINC_DATE
date $UM_WAPPTOTAL_DATE
date $UM_WADMADMIT_DATE
date $UM_NOSHOW_DATE
date $UM_NODECISION_DATE
date $UM_WAPPDEF_DATE
date $UM_WAIT_DATE
date $UM_DEIN_DATE
date $UM_MATRIC_DATE
date $This_Term_end_date
date $cancel_row_date
date $matr_row_date
date $defer_row_date
date $ac_row_date
date $wadm_row_date
date $wapp_row_date
text $UM_WAPPDEF_TERM
text $UM_WAPPDEF_PLAN
text $UM_WAPPDEF_SUBPLAN
text $UM_WAPPDEF_PRGSTAT
text $UM_WAPPDEF_PROGACT
text $UM_WAPPDEF_PRGREAS
text $Row_Check
end-declare
End-Setup
!***********************************************************************
! Proc Options(Main);
!***********************************************************************
Begin-Program
Let $Reporttitle = 'Make Applicant Reporting Extract'
let $Reportid = 'umappext'
let $first_time = 'Y'
do Stdapi-Init
do Init-DateTime
do Get-Current-DateTime
BEGIN-SQL
ALTER SESSION SET "_optimizer_cost_based_transformation" = ON
end-sql
show 'Get Parameters:'
if $prcs_process_instance = '' or $prcs_oprid = 'BATCH'
let $prcs_oprid = 'PANDERSEN'
let $prcs_run_cntl_id = 'BATCH'
do Select-Parameters
else
do Select-Parameters
end-if
Show $Input_Trm1
if $input_Flag1 = 'Y'
let $input_date = datenow()
end-if
show $input_date
Let $FiveYearsAgo = dateadd($input_date,'YEAR',-5)
Let $TwoYearsAgo = dateadd($input_date,'YEAR',-5)
begin-SELECT
count(*) &GobSTOP
if &GobStop < 450000
show 'Not enough records in PS_PERSONAL_DATA'
stop quiet
else
show &Gobstop ' records in PS_PERSONAL_DATA'
end-if
from PS_PERSONAL_DATA
end-select
Let $Control_now = edit(datenow(),'yyyy/mm/dd:hh:mi:ssam')
show $Control_now
Do Extract_Control('7', 'PS_UM_APPL_EXTRACT','UMAPPEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
! if $input_Flag2 = 'Y'
! BEGIN-SQL on-error=SQL-Recover
! TRUNCATE TABLE SYSADM.PS_UM_APPL_EXTRACT
! END-SQL
! else
! begin-sql on-error=SQL-Recover
! DELETE FROM SYSADM.PS_UM_APPL_EXTRACT dxx where dxx.admit_term >= $Input_Trm1 and dxx.admit_term <= $Input_Term2
! end-sql
! end-if
Begin-sql
TRUNCATE TABLE SYSADM.PS_UM_APPL_EXT_BLD
end-sql
BEGIN-SQL on-error=SQL-Recover
COMMIT
END-SQL
show 'Table trimmed.'
Show $Input_Trm1
Show $Input_Term2
do List_Applicants
do blasto_data
Do Extract_Control('9', 'PS_UM_APPL_EXTRACT','UMAPPEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
BEGIN-SQL on-error=SQL-Recover
COMMIT
END-SQL
do Stdapi-Term
End-Program
!***********************************************************************
! Page Heading
!***********************************************************************
Begin-Heading 8
! #include 'umstdhdg.sqc'
print $INSTITUTION_NAME (+1,1) center bold box
Print 'In Date:' (+1,1)
Print $input_Date (,+1) DATE bold
Print 'Beginning Term: ' (,41)
Print $Input_Trm1 (,+1) bold
Print 'Ending Term ' (,+2)
Print $Input_term2 (,+1) bold
End-Heading
!***********************************************************************
! Procedure List_Applicants *
! *
!***********************************************************************
Begin-Procedure List_Applicants
Let #APP_COUNT = 0
BEGIN-SELECT
A.EMPLID,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.ADDRESS4,
A.CITY,
A.STATE,
A.POSTAL,
A.COUNTY,
A.COUNTRY,
A.SEX,
A.BIRTHDATE,
A.BIRTHPLACE,
A.BIRTHSTATE,
A.BIRTHCOUNTRY,
A.MAR_STATUS,
A.MAR_STATUS_DT,
A.DT_OF_DEATH,
B.ACAD_CAREER,
C.STDNT_CAR_NBR,
B.ADM_APPL_NBR,
B.INSTITUTION,
B.ADM_APPL_CTR,
B.ADMIT_TYPE,
B.FIN_AID_INTEREST,
B.HOUSING_INTEREST,
B.REGION,
B.LAST_SCH_ATTEND,
B.GRADUATION_DT,
B.ACADEMIC_LEVEL,
B.ADM_APPL_COMPLETE,
B.ADM_APPL_DT,
B.ADM_APPL_CMPLT_DT,
B.ADM_APPL_METHOD,
B.ADM_CREATION_DT,
B.ADM_CREATION_BY,
B.ADM_UPDATED_DT,
B.ADM_UPDATED_BY,
B.APPL_FEE_TYPE,
B.RECRUITER_ID,
C.CAMPUS,
C.APPL_PROG_NBR,
C.EFFDT,
C.EFFSEQ,
C.ACAD_PROG,
C.PROG_STATUS,
C.PROG_ACTION,
C.ACTION_DT,
C.PROG_REASON,
C.ADMIT_TERM,
C.EXP_GRAD_TERM,
C.ADM_APPL_NBR,
'AD' &DATASOURCE,
to_date($input_date) &Recalculated_input_date,
C.ACAD_LOAD_APPR,
C.REQ_TERM
LET #APP_COUNT = #APP_COUNT + 1
Let $UM_APP_DATE = '01-JAN-1901' !??0104
Let $UM_COMPLETE_DATE = '01-JAN-1901' !??0104
Let $UM_ADMIT_DATE = '01-JAN-1901' !??0104
Let $UM_NETCONFIRM_DATE = '01-JAN-1901' !??0104
Let $UM_GROSSCONFIRM_DATE = '01-JAN-1901' !??0104
Let $UM_ENROLL_DATE = '01-JAN-1901' !??0104
Let $UM_DEFER_DATE = '01-JAN-1901' !??0104
Let $UM_DENY_DATE = '01-JAN-1901' !??0104
Let $UM_DISCONTINUE_DATE = '01-JAN-1901' !??0611
Let $UM_WADMINC_DATE = '01-JAN-1901' !??0104
Let $UM_WADMTOT_DATE = '01-JAN-1901' !??0104
Let $UM_WAPPINC_DATE = '01-JAN-1901' !??0104
Let $UM_WAPPTOTAL_DATE = '01-JAN-1901' !??0104
Let $UM_WADMADMIT_DATE = '01-JAN-1901' !??0104
Let $UM_NOSHOW_DATE = '01-JAN-1901' !??0104
Let $UM_NODECISION_DATE = '01-JAN-1901' !??0104
Let $UM_WAPPDEF_DATE = '01-JAN-1901' !??0104
Let $UM_WAIT_DATE = '01-JAN-1901'
Let $UM_DEIN_DATE = '01-JAN-1901'
Let $UM_MATRIC_DATE = '01-JAN-1901'
let $UM_WAPPDEF_TERM = '0000'
Let $UM_WAPPDEF_PLAN = ' '
Let $UM_WAPPDEF_SUBPLAN = ' '
Let $UM_WAPPDEF_PRGSTAT = ' '
Let $UM_WAPPDEF_PROGACT = ' '
Let $UM_WAPPDEF_PRGREAS = ' '
Let $PROG_ACTION = &C.PROG_ACTION
Let $PROG_STATUS = &C.PROG_STATUS
Let $PROG_REASON = &C.PROG_REASON
let $Row_Check = ' ' !Top Row Y
!Top Program but not Top Row P
DO GET_PREVIOUS_NAME
DO GET_EMAIL
DO GET_PHONE
DO GET_NID
DO GET_ETHNIC
DO GET_COUNTRY
DO GET_RESIDENCY
DO GET_A_DESCR
DO Get_Last_Sch_info
DO Scan_Student_Car_Term ! Values need for Scan subs and first and last
if &DATASOURCE = 'AD'
DO GET_APPL_PLAN
else
DO GET_STU_PLAN
end-if
DO GET_RESPONSE
Do GET_EXT_TABLE
Do Get_ACAD_PROG_APPL
Do Get_Prog_Rsn_Descr
Do MAXXY
Let #nl_index = 0
Let #Temp_satvm = #max_sat_verb + #max_sat_math
if &B.INSTITUTION = 'UMS05' and &B.ACAD_CAREER = 'UGRD'
and (&B.ADMIT_TYPE = 'EFY' or &B.ADMIT_TYPE = 'FYR' or &B.ADMIT_TYPE = 'TRF' )
DO GET_NL_INDEX(&B.INSTITUTION,&A.EMPLID,&B.ACAD_CAREER,&B.adm_appl_nbr,#Temp_satvm,#nl_index)
end-if
!Do first_or_last_action
!if $Row_Check = ' ' and #Most_recent_appl_prog_nbr = &C.APPL_PROG_NBR
! Let $Row_Check = 'Y'
!end-if
do Scan_em_all
!do scan_acad_prog
if $row_check = ' ' and #Max_appl_prog_nbr = &C.APPL_PROG_NBR
Let $Row_Check = 'Y'
end-if
Let $last_prog_action = 'APPL'
if $UM_ADMIT_DATE > strtodate('01-JAN-1902')
Let $last_prog_action = 'ADMT'
end-if
if ($UM_MATRIC_DATE >= $UM_APP_DATE
and $UM_MATRIC_DATE >= $UM_DEFER_DATE
and $UM_MATRIC_DATE >= $UM_DENY_DATE
and $UM_MATRIC_DATE >= $UM_WADMTOT_DATE
and $UM_MATRIC_DATE >= $UM_WAPPTOTAL_DATE
and $UM_MATRIC_DATE >= $UM_ADMIT_DATE
and $UM_MATRIC_DATE >= $UM_WAIT_DATE
And $UM_MATRIC_DATE >= $UM_DEIN_DATE
and $UM_MATRIC_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'MATR'
else
if ($UM_DEIN_DATE >= $UM_APP_DATE
and $UM_DEIN_DATE >= $UM_DEFER_DATE
and $UM_DEIN_DATE >= $UM_DENY_DATE
and $UM_DEIN_DATE >= $UM_WADMTOT_DATE
and $UM_DEIN_DATE >= $UM_WAPPTOTAL_DATE
and $UM_DEIN_DATE >= $UM_WAIT_DATE
and $UM_DEIN_DATE >= $UM_ADMIT_DATE
and $UM_DEIN_DATE >= $UM_WAPPTOTAL_DATE
and $UM_DEIN_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'DEIN'
else
if ($UM_WADMTOT_DATE >= $UM_APP_DATE
and $UM_WADMTOT_DATE >= $UM_DEFER_DATE
and $UM_WADMTOT_DATE >= $UM_DENY_DATE
and $UM_WADMTOT_DATE >= $UM_WAIT_DATE
and $UM_WADMTOT_DATE >= $UM_ADMIT_DATE
and $UM_WADMTOT_DATE >= $UM_WAPPTOTAL_DATE
and $UM_WADMTOT_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'WADM'
else
if ($UM_WAPPTOTAL_DATE >= $UM_APP_DATE
and $UM_WAPPTOTAL_DATE >= $UM_DEFER_DATE
and $UM_WAPPTOTAL_DATE >= $UM_DENY_DATE
and $UM_WAPPTOTAL_DATE >= $UM_WAIT_DATE
and $UM_WAPPTOTAL_DATE >= $UM_ADMIT_DATE
and $UM_WAPPTOTAL_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'WAPP'
else
if ($UM_DENY_DATE >= $UM_APP_DATE
and $UM_DENY_DATE >= $UM_DEFER_DATE
and $UM_DENY_DATE >= $UM_WAIT_DATE
and $UM_DENY_DATE >= $UM_ADMIT_DATE
and $UM_DENY_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'DENY'
else
if ($UM_DEFER_DATE >= $UM_APP_DATE
and $UM_DEFER_DATE >= $UM_WAIT_DATE
and $UM_DEFER_DATE >= $UM_ADMIT_DATE
and $UM_DEFER_DATE > strtodate('01-JAN-1902'))
Let $last_prog_action = 'DEFR'
else
if $UM_WAIT_DATE >= $UM_APP_DATE
and $UM_WAIT_DATE >= $UM_ADMIT_DATE
and $UM_WAIT_DATE > strtodate('01-JAN-1902')
Let $last_prog_action = 'WAIT'
end-if
end-if
end-if
end-if
end-if
end-if
end-if
if $UM_GROSSCONFIRM_DATE >= $UM_APP_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_DEFER_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_DENY_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_DISCONTINUE_DATE !?0611
and $UM_GROSSCONFIRM_DATE >= $UM_WADMTOT_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_WAPPTOTAL_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_WAIT_DATE
and $UM_GROSSCONFIRM_DATE >= $UM_ADMIT_DATE
And $UM_GROSSCONFIRM_DATE >= $UM_DEIN_DATE
and $UM_GROSSCONFIRM_DATE >= strtodate('01-JAN-1902')
Let $last_prog_action = 'MATR'
end-if
DO INSERT_ROWS
FROM PS_PERSONAL_DATA A, PS_ADM_APPL_DATA B, PS_ADM_APPL_PROG C
WHERE A.EMPLID = B.EMPLID
AND B.EMPLID = C.EMPLID
AND B.ACAD_CAREER = C.ACAD_CAREER
AND B.ADM_APPL_NBR = C.ADM_APPL_NBR
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_ADM_APPL_PROG C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.ACAD_CAREER = C_ED.ACAD_CAREER
AND C.ADM_APPL_NBR = C_ED.ADM_APPL_NBR
AND C.APPL_PROG_NBR = C_ED.APPL_PROG_NBR ! comment out to Use Most Recent Prog_Nbr
! AND C_ED.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
AND C_ED.EFFDT <= $input_date)
AND C.EFFSEQ =
(SELECT MAX(C_ES.EFFSEQ) FROM PS_ADM_APPL_PROG C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.ACAD_CAREER = C_ES.ACAD_CAREER
AND C.ADM_APPL_NBR = C_ES.ADM_APPL_NBR
AND C.APPL_PROG_NBR = C_ES.APPL_PROG_NBR
! AND C_ES.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
AND C.EFFDT = C_ES.EFFDT)
AND (C.ADMIT_TERM >= $Input_Trm1 and C.ADMIT_TERM <= $Input_Term2)
and not exists (select
'QTB'
FROM PS_ADM_APPL_DATA QTB, PS_ACAD_PROG QTC
WHERE C.EMPLID = QTC.EMPLID
AND C.ACAD_CAREER = QTC.ACAD_CAREER
AND C.ADM_APPL_NBR = QTC.ADM_APPL_NBR
AND C.APPL_PROG_NBR = QTC.APPL_PROG_NBR
AND QTB.EMPLID = QTC.EMPLID
AND QTB.ACAD_CAREER = QTC.ACAD_CAREER
AND QTB.ADM_APPL_NBR = QTC.ADM_APPL_NBR
AND QTC.EFFDT =
(SELECT MAX(QTC_ED.EFFDT) FROM PS_ACAD_PROG QTC_ED
WHERE QTC.EMPLID = QTC_ED.EMPLID
AND QTC.ACAD_CAREER = QTC_ED.ACAD_CAREER
AND QTC.ADM_APPL_NBR = QTC_ED.ADM_APPL_NBR
AND QTC.APPL_PROG_NBR = QTC_ED.APPL_PROG_NBR
! AND QTC_ED.PROG_ACTION not in ('RADM','RLOA','LEAV') !?061608 Exclusions
! UMREPORT-2 Peter Andersen 09/12
AND QTC_ED.EFFDT <= greatest (
(case when c.admit_term > '0410' then
case when substr(c.admit_term,3,2) = '10' then to_date('15-OCT-'||to_char(to_number(substr(c.admit_term,1,2)) + 1999,'9999'),'DD-MON-YYYY') else
case when substr(c.admit_term,3,2) = '20' then to_date('15-FEB-'||to_char(to_number(substr(c.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') else
to_date('15-AUG-'||to_char(to_number(substr(c.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') end
end else to_date('15-OCT-2004','DD-MON-YYYY') end ),
(select endo.ssr_trmac_last_dt from ps_term_tbl endo where
endo.institution = QTB.institution
and endo.acad_career = QTC.acad_career
and endo.strm = (select max(li.admit_term) from ps_acad_prog li
where li.emplid = qtc.emplid and li.acad_career = qtc.acad_career and li.adm_appl_nbr = qtc.adm_appl_nbr )))
)
AND QTC.EFFSEQ =
(SELECT MAX(QTC_ES.EFFSEQ) FROM PS_ACAD_PROG QTC_ES
WHERE QTC.EMPLID = QTC_ES.EMPLID
AND QTC.ACAD_CAREER = QTC_ES.ACAD_CAREER
AND QTC.ADM_APPL_NBR = QTC_ES.ADM_APPL_NBR
AND QTC.APPL_PROG_NBR = QTC_ES.APPL_PROG_NBR
! AND QTC_ES.PROG_ACTION not in ('RADM','RLOA','LEAV') !?061608 Exclusions
AND QTC.EFFDT = QTC_ES.EFFDT)
AND (QTC.ADMIT_TERM >= $Input_Trm1 and QTC.ADMIT_TERM <= $Input_Term2)
! and QTC.prog_reason not in ('APRM','NSHW','AADM','PMAT','PMNO','WDFA','WMRG','WMRA')
! and QTC.PROG_STATUS <> 'CN'
! and QTC.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
)
AND NOT exists ( select 'Dup' from PS_ADM_APPL_PROG DUPP ! J.B. s mod for applications
WHERE C.EMPLID = DUPP.EMPLID ! that have been deleted for
AND C.ACAD_CAREER = DUPP.ACAD_CAREER ! being duplicates
! AND C.STDNT_CAR_NBR = DUPP.STDNT_CAR_NBR
AND C.ADM_APPL_NBR = DUPP.ADM_APPL_NBR
AND DUPP.PROG_ACTION = 'WADM'
AND DUPP.PROG_REASON = 'DUP')
UNION
select
STA.EMPLID,
STA.LAST_NAME,
STA.FIRST_NAME,
STA.MIDDLE_NAME,
STA.NAME,
STA.ADDRESS1,
STA.ADDRESS2,
STA.ADDRESS3,
STA.ADDRESS4,
STA.CITY,
STA.STATE,
STA.POSTAL,
STA.COUNTY,
STA.COUNTRY,
STA.SEX,
STA.BIRTHDATE,
STA.BIRTHPLACE,
STA.BIRTHSTATE,
STA.BIRTHCOUNTRY,
STA.MAR_STATUS,
STA.MAR_STATUS_DT,
STA.DT_OF_DEATH,
STB.ACAD_CAREER,
STC.STDNT_CAR_NBR,
STB.ADM_APPL_NBR,
STB.INSTITUTION,
STB.ADM_APPL_CTR,
STB.ADMIT_TYPE,
STB.FIN_AID_INTEREST,
STB.HOUSING_INTEREST,
STB.REGION,
STB.LAST_SCH_ATTEND,
STB.GRADUATION_DT,
STB.ACADEMIC_LEVEL,
STB.ADM_APPL_COMPLETE,
STB.ADM_APPL_DT,
STB.ADM_APPL_CMPLT_DT,
STB.ADM_APPL_METHOD,
STB.ADM_CREATION_DT,
STB.ADM_CREATION_BY,
STB.ADM_UPDATED_DT,
STB.ADM_UPDATED_BY,
STB.APPL_FEE_TYPE,
STB.RECRUITER_ID,
STC.CAMPUS,
STC.APPL_PROG_NBR,
STC.EFFDT,
STC.EFFSEQ,
STC.ACAD_PROG,
STC.PROG_STATUS,
STC.PROG_ACTION,
STC.ACTION_DT,
STC.PROG_REASON,
STC.ADMIT_TERM,
STC.EXP_GRAD_TERM,
STC.ADM_APPL_NBR,
'ST',
! UMREPORT-2 Peter Andersen 09/12
case when stc.admit_term > '0410' then
case when substr(stc.admit_term,3,2) = '10' then to_date('15-OCT-'||to_char(to_number(substr(stc.admit_term,1,2)) + 1999,'9999'),'DD-MON-YYYY') else
case when substr(stc.admit_term,3,2) = '20' then to_date('15-FEB-'||to_char(to_number(substr(stc.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') else
to_date('15-AUG-'||to_char(to_number(substr(stc.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') end
end else to_date('15-OCT-2004','DD-MON-YYYY') end,
!(select ndo.ssr_trmac_last_dt from ps_term_tbl ndo where
! ndo.institution = stb.institution
! and ndo.acad_career = stc.acad_career
! and ndo.strm = (select max(lop.admit_term) from ps_acad_prog lop
! where lop.emplid = stc.emplid and lop.acad_career = stc.acad_career and lop.adm_appl_nbr = stc.adm_appl_nbr
! and lop.prog_action not in ('RADM','RLOA','LEAV')
! )), !??061608 Exclusions
STC.ACAD_LOAD_APPR,
STC.REQ_TERM
FROM PS_PERSONAL_DATA STA, PS_ADM_APPL_DATA STB, PS_ACAD_PROG STC
WHERE STA.EMPLID = STB.EMPLID
AND STB.EMPLID = STC.EMPLID
AND STB.ACAD_CAREER = STC.ACAD_CAREER
AND STB.ADM_APPL_NBR = STC.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.ADM_APPL_NBR = STC_ED.ADM_APPL_NBR
AND STC.APPL_PROG_NBR = STC_ED.APPL_PROG_NBR
! AND STC_ED.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
! UMREPORT-2 Peter Andersen 09/12
AND STC_ED.EFFDT <= GREATEST (
(case when stc.admit_term > '0410' then
case when substr(stc.admit_term,3,2) = '10' then to_date('15-OCT-'||to_char(to_number(substr(stc.admit_term,1,2)) + 1999,'9999'),'DD-MON-YYYY') else
case when substr(stc.admit_term,3,2) = '20' then to_date('15-FEB-'||to_char(to_number(substr(stc.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') else
to_date('15-AUG-'||to_char(to_number(substr(stc.admit_term,1,2))+2000,'9999'),'DD-MON-YYYY') end
end else to_date('15-OCT-2004','DD-MON-YYYY') end ),
(select endo.ssr_trmac_last_dt from ps_term_tbl endo where
endo.institution = STB.institution
and endo.acad_career = STC.acad_career
and endo.strm = (select max(li.admit_term) from ps_acad_prog li
where li.emplid = stc.emplid and li.acad_career = stc.acad_career and li.adm_appl_nbr = stc.adm_appl_nbr )
)))
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.ADM_APPL_NBR = STC_ES.ADM_APPL_NBR
AND STC.APPL_PROG_NBR = STC_ES.APPL_PROG_NBR
! AND STC_ES.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
AND STC.EFFDT = STC_ES.EFFDT)
AND (STC.ADMIT_TERM >= $Input_Trm1 and STC.ADMIT_TERM <= $Input_Term2)
! AND STC.ADMIT_TERM < NVL((SELECT MIN(VO.ADMIT_TERM) FROM PS_ACAD_PROG VO !??061608 Possible Term Limiter
! WHERE VO.EMPLID = STC.EMPLID !??061608 can overide exclusions
! AND VO.ACAD_CAREER = STC.ACAD_CAREER
! AND VO.ADM_APPL_NBR = STC.ADM_APPL_NBR
! AND VO.APPL_PROG_NBR = STC.APPL_PROG_NBR
! AND VO.PROG_ACTION in ('RADM','RLOA','LEAV')
! ),'9999')
! AND STC.PROG_ACTION not in ('RADM','RLOA','LEAV') !??061608 Exclusions
END-SELECT
show 'Applicants selected = ' #APP_COUNT
End-Procedure ! List_Applicants
!***********************************************************************
! Select Parameters
!***********************************************************************
Begin-Procedure Select-Parameters
Let $input_date = datenow()
Let $Input_Trm1 = '0610'
Let $Input_Term2 = '9999'
Let $Input_Flag3 = 'Y'
begin-select
R.AS_OF_DATE,
R.UM_BEGIN_TERM,
R.UM_END_TERM,
R.FLAG1,
R.FLAG2,
R.FLAG3
Let $input_date = &R.AS_OF_DATE
if &R.FLAG1 = 'Y'
Let $input_date = datenow()
end-if
Let $Input_Trm1 = &R.UM_BEGIN_TERM
Let $Input_term = &R.UM_BEGIN_TERM
Let $Input_Term2 = &R.UM_END_TERM
Let $input_Flag2 = &R.FLAG2
Let $input_Flag3 = &R.FLAG3
FROM PS_UM_RUN_UMAPPEXT R
WHERE R.OPRID = $prcs_oprid
AND R.RUN_CNTL_ID = $prcs_run_cntl_id
end-select
show $input_date
if $input_Flag3 <> 'Y'
show 'Using from SP Term1: ' $Input_Trm1 ' Term2: ' $Input_Term2
else
Let $compare = '0'||to_char( to_number(substr($input_date,10,2)) - 1 )
Let $compare = substr($compare,length($compare)-1,2)
show 'AUTO: ' $input_date '--> ' $compare
begin-select
VIK.STRM,
VIK.DESCR,
VIK.DESCRSHORT
show &VIK.STRM '--' &VIK.DESCR '--' &VIK.DESCRSHORT
Let $input_term = &VIK.STRM
LET $Input_trm1 = &VIK.STRM
Let $input_term2 = '9999'
from ps_term_val_tbl VIK
where substr(VIK.STRM,1,2) = $compare
and substr(VIK.STRM,3,1) = decode(substr(to_char($input_date),4,3),'JAN','2','FEB','2','MAR','2','APR','2','MAY','2',
'JUN','3','JUL','3','AUG','3','SEP','1','OCT','1','NOV','1','DEC','1','1')
end-select
show 'Automatic Term1: ' $Input_Term ' to ' $Input_Term2
end-if
Let $Input_Trm1_M = ''''||$Input_Trm1||''''
Let $Input_Term2_M = ''''||$Input_Term2||''''
End-Procedure
!***********************************************************************
! Get Input for Terms
!***********************************************************************
Begin-Procedure Get-Input
Let #input_bad = 1
Input $input_date type=date 'Enter Date'
Input $Input_Trm1 type=text status=#input_status 'Please Enter the First Term'
Input $input_term2 type=text status=#input_status 'Please Enter the Second Term'
End-Procedure
!***********************************************************************
! Get Previous Name
!***********************************************************************
Begin-Procedure Get_Previous_Name
Let $Previous_Name = ' '
Begin-select
SB1.Name
if &SB1.Name > ' '
Let $Previous_Name = &SB1.Name
end-if
FROM PS_NAMES SB1
WHERE SB1.NAME_TYPE = 'FR1'
and SB1.EMPLID = &A.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 <= $input_date)
end-select
End-Procedure
!***********************************************************************
! Get Preferred email address
!***********************************************************************
Begin-Procedure Get_email
Let $Email_Addr = ' '
Let $Email_Hom_Addr = ' '
Begin-select
SB2.EMAIL_ADDR,
SB2.E_ADDR_TYPE,
SB2.PREF_EMAIL_FLAG
if &SB2.EMAIL_ADDR > ' ' and &SB2.PREF_EMAIL_FLAG = 'Y'
Let $Email_Addr = &SB2.EMAIL_ADDR
end-if
if &SB2.EMAIL_ADDR > ' ' and &SB2.E_ADDR_TYPE = 'HOME'
Let $Email_Hom_Addr = &SB2.EMAIL_ADDR
end-if
FROM PS_EMAIL_ADDRESSES SB2
WHERE SB2.EMPLID = &A.EMPLID
end-select
End-Procedure
!***********************************************************************
! Get Preferred Phone Number
!***********************************************************************
Begin-Procedure Get_phone
Let $Phone = ' '
Begin-select
SB3.PHONE
if &SB3.PHONE > ' '
Let $Phone = &SB3.PHONE
end-if
FROM PS_PERSONAL_PHONE SB3
WHERE SB3.PREF_PHONE_FLAG = 'Y'
and SB3.EMPLID = &A.EMPLID
end-select
End-Procedure
!***********************************************************************
! Get NID for USA and Canada
!***********************************************************************
Begin-Procedure Get_nid
Let $Nid_Usa = ' '
Let $Nid_Can = ' '
Begin-select
SB4.NATIONAL_ID,
SB4.COUNTRY
if &SB4.COUNTRY = 'USA' and &SB4.NATIONAL_ID > ' '
Let $Nid_Usa = &SB4.NATIONAL_ID
end-if
if &SB4.COUNTRY = 'CAN' and &SB4.NATIONAL_ID > ' '
Let $Nid_Can = &SB4.NATIONAL_ID
end-if
FROM PS_PERS_NID SB4
WHERE SB4.EMPLID = &A.EMPLID
end-select
End-Procedure
!***********************************************************************
! Get Ethnic Data
!***********************************************************************
Begin-Procedure Get_ethnic
Let $Ethnic_Grp_cd = ' '
Let $Ethnic_Category = ' '
Let $Ethnic_Descr = ' '
Let $Hisp_Latino = 'N'
Let $Multi_Race = 'N'
Let $Old_Group = ' '
Let $Ethnic_Group = ' '
Let $UM_ETH_GRP_DESCR = ' '
Let #Race_multi = 0
Begin-select
SB6.ETHNIC_GRP_CD,
SB6.APS_EC_NDS_AUS,
SB7.ETHNIC_CATEGORY,
SB7.DESCR50,
SB7.SETID,
SB7.ETHNIC_GRP_CD,
SB7.ETHNIC_GROUP
if &SB6.ETHNIC_GRP_CD > ' ' and $Ethnic_grp_cd = ' '
Let $Ethnic_Grp_cd = &SB6.ETHNIC_GRP_CD
Let $Ethnic_Category = &SB7.ETHNIC_CATEGORY
Let $Ethnic_Descr = &SB7.DESCR50
Let $Ethnic_Group = &SB7.ETHNIC_GROUP
end-if
if &SB7.ETHNIC_GROUP = '3'
Let $Hisp_Latino = 'Y'
else
if &SB7.ETHNIC_GROUP <> $Old_Group
if &SB7.ETHNIC_GROUP <> '6'
Add 1 to #Race_multi ! Not a true count
end-if
Let $Old_Group = &SB7.ETHNIC_GROUP
end-if
end-if
FROM PS_DIVERS_ETHNIC SB6, PS_ETHNIC_GRP_TBL SB7
WHERE SB6.ETHNIC_GRP_CD = SB7.ETHNIC_GRP_CD
AND SB6.EMPLID = &A.EMPLID
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 <= $input_date)
AND SB7.SETID = 'USA'
order by decode(sb7.ethnic_group,'3','1','2'), decode(sb6.primary_indicator,'Y','1','2')
end-select
if #Race_multi > 1
Let $Multi_Race = 'Y'
end-if
DO GET_XLAT('ETHNIC_GROUP',$Ethnic_Group,$UM_ETH_GRP_DESCR)
End-Procedure
!***********************************************************************
! Get Country Data
!***********************************************************************
Begin-Procedure Get_Country
Let $Country_Usa = ' '
Let $Citizenship_Status_Usa = ' '
Let $Country_Oth = ' '
Let $Citizenship_Status_Oth = ' '
Begin-select
SB8.COUNTRY,
SB8.CITIZENSHIP_STATUS
if &SB8.COUNTRY = 'USA'
Let $Country_Usa = &SB8.COUNTRY
Let $Citizenship_Status_Usa = &SB8.CITIZENSHIP_STATUS
end-if
if &SB8.COUNTRY <> 'USA' and (&SB8.CITIZENSHIP_STATUS = 'N' or &SB8.CITIZENSHIP_STATUS = ' ' )
Let $Country_Oth = &SB8.COUNTRY
Let $Citizenship_Status_Oth = &SB8.CITIZENSHIP_STATUS
end-if
FROM PS_CITIZENSHIP SB8
WHERE SB8.EMPLID = &A.EMPLID
end-select
End-Procedure
!***********************************************************************
! Get Calculated Noel Levitz Index
!***********************************************************************
Begin-Procedure GET_NL_INDEX($in_institution,$in_emplid,$in_acad_career,$in_adm_appl_nbr,#in_combined_sat,:#out_index)
Let #out_index = 0
Let #hsgpa = 0
Let #nl_rating = 0
Begin-select
ug.emplid
ug.acad_career
ug.adm_appl_nbr
ug.evaluation_code
ug.rating_cmp
ug.rating_cmp_value
Let #hsgpa = &ug.rating_cmp_value
from sysadm.ps_adm_appl_cmp ug
where ug.rating_cmp = 'HSGPA'
and ug.emplid = $in_emplid
and ug.acad_career = $in_acad_career
and ug.adm_appl_nbr = $in_adm_appl_nbr
end-select
if #hsgpa > 0 and #in_combined_sat > 0
Let #nl_rating = #hsgpa*12.5 + #in_combined_sat*.03125
evaluate #nl_rating
when >= 85
Let #out_index = 1
break
when >= 78
Let #out_index = 2
break
when >= 71
Let #out_index = 3
break
when >= 66
Let #out_index = 4
break
when-other
Let #out_index = 5
end-evaluate
end-if
End-Procedure
!***********************************************************************
! Get Residency Data
!***********************************************************************
Begin-Procedure Get_Residency
Let $Effective_Term = ' '
Let $Residency = ' '
Let $Residency_Dt = '01-JAN-1901'
Let $Admission_Res = ' '
Let $Admission_Excpt = ' '
Begin-select
SB9.EFFECTIVE_TERM,
SB9.RESIDENCY,
SB9.RESIDENCY_DT,
SB9.ADMISSION_RES,
SB9.ADMISSION_EXCPT
if &SB9.EFFECTIVE_TERM > ' '
Let $Effective_Term = &SB9.EFFECTIVE_TERM
end-if
if &SB9.RESIDENCY > ' '
Let $Residency = &SB9.RESIDENCY
end-if
if &SB9.RESIDENCY > ' '
Let $Residency_Dt = &SB9.RESIDENCY_DT
end-if
if &SB9.ADMISSION_RES > ' '
Let $Admission_Res = &SB9.ADMISSION_RES
end-if
if &SB9.ADMISSION_EXCPT > ' '
Let $Admission_Excpt = &SB9.ADMISSION_EXCPT
end-if
FROM PS_RESIDENCY_OFF SB9
WHERE SB9.EMPLID = &A.EMPLID
AND SB9.ACAD_CAREER = &B.ACAD_CAREER
AND SB9.INSTITUTION = &B.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 <= &C.ADMIT_TERM) ! ??1006
OR (SB9.EFFECTIVE_TERM = (SELECT MAX(EC9.EFFECTIVE_TERM) FROM PS_RESIDENCY_OFF EC9 ! ??1006
WHERE EC9.EMPLID = SB9.EMPLID AND EC9.ACAD_CAREER = EC9.ACAD_CAREER AND EC9.INSTITUTION = SB9.INSTITUTION) ! ??1006
AND NOT EXISTS (SELECT 'X' FROM PS_RESIDENCY_OFF ED9 ! ??1006
WHERE ED9.EMPLID=SB9.EMPLID AND ED9.ACAD_CAREER = SB9.ACAD_CAREER AND ED9.INSTITUTION = SB9.INSTITUTION ! ??1006
AND ED9.EFFECTIVE_TERM <= &C.ADMIT_TERM)) ! ??1006
)
end-select
End-Procedure
!***********************************************************************
! Get Acad Prog Descr
!***********************************************************************
Begin-Procedure Get_A_Descr
Let $Acad_Prog_Descr = ' '
Let $Academic_Group = ' ' !??0607
Let $Academic_Group_descr = ' ' !??0607
Begin-select
SB10.DESCR,
SB10.INSTITUTION,
SB10.ACAD_PROG,
SB10.ACAD_GROUP,
CR10.DESCR
if &SB10.DESCR > ' '
Let $Acad_Prog_Descr = &SB10.DESCR
end-if
if &SB10.ACAD_GROUP > ' '
Let $Academic_Group = &SB10.ACAD_GROUP
end-if
if &CR10.DESCR > ' '
Let $Academic_Group_Descr = &CR10.DESCR
end-if
FROM PS_ACAD_PROG_TBL SB10, PS_ACAD_GROUP_TBL CR10
WHERE SB10.ACAD_PROG = &C.ACAD_PROG
AND SB10.INSTITUTION = &B.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 <= $input_date)
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 <= $input_date)
or CR10.EFFDT is null)
end-select
End-Procedure
!***********************************************************************
! Get Last School Info
!***********************************************************************
Begin-Procedure Get_Last_Sch_info
Let $Last_Sch_Descr = ' '
Let $Last_Sch_Type = ' '
Let $Ext_Org_Id_Descr = ' '
Let $Ext_org_id = &B.LAST_SCH_ATTEND
Begin-select
lbs.LS_SCHOOL_TYPE,
las.descr
if &las.descr > ' '
Let $Last_Sch_Descr = &las.descr
Let $Ext_Org_Id_Descr = &las.descr
end-if
if &lbs.LS_SCHOOL_TYPE > ' '
Let $Last_Sch_type = &lbs.LS_SCHOOL_type
end-if
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 <= $input_date)
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 <= $input_date)
AND LBS.EFF_STATUS = 'A'
!PS_EXT_ORG_TBL LAS
AND las.ext_org_id = &B.last_sch_attend
!AND las.effdt =
! (SELECT MAX(LAS_ED.EFFDT) FROM PS_EXT_ORG_TBL LAS_ED
! WHERE LAS.EXT_ORG_ID = LAS_ED.EXT_ORG_ID
! AND LAS_ED.EFFDT <= [$input_date_M])
end-select
End-Procedure
!***********************************************************************
! Get UM_ACAD_PROG_APPL, UM_ACAD_APPL_PLAN from applied row
!***********************************************************************
begin-procedure get_ACAD_PROG_APPL
Let $Um_Acad_Prog_Appl = ' '
Let $Um_Acad_Plan_Appl = ' '
begin-select
nvl(CPX.ACAD_PROG,' ') &CPX.ACAD_PROG,
nvl(DPX.ACAD_PLAN,' ') &DPX.ACAD_PLAN
Let $Um_Acad_Prog_Appl = &CPX.ACAD_Prog
Let $Um_Acad_Plan_Appl = &DPX.ACAD_PLAN
FROM PS_ADM_APPL_PROG CPX, PS_ADM_APPL_PLAN DPX
where CPX.EMPLID = DPX.EMPLID(+)
AND CPX.ACAD_CAREER = DPX.ACAD_CAREER(+)
AND CPX.STDNT_CAR_NBR = DPX.STDNT_CAR_NBR(+)
AND CPX.ADM_APPL_NBR = DPX.ADM_APPL_NBR(+)
AND CPX.APPL_PROG_NBR = DPX.APPL_PROG_NBR(+)
AND CPX.EFFDT = DPX.EFFDT(+)
AND CPX.EFFSEQ = DPX.EFFSEQ(+)
AND CPX.EMPLID = &A.EMPLID
AND CPX.INSTITUTION = &B.INSTITUTION
AND CPX.ACAD_CAREER = &B.ACAD_CAREER
AND CPX.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND CPX.ADMIT_TERM = &C.ADMIT_TERM
AND CPX.APPL_PROG_NBR = &C.APPL_PROG_NBR
AND CPX.PROG_STATUS = 'AP'
AND CPX.EFFDT =
(SELECT MAX(CPX_ED.EFFDT) FROM PS_ADM_APPL_PROG CPX_ED
WHERE CPX.EMPLID = CPX_ED.EMPLID
AND CPX.ACAD_CAREER = CPX_ED.ACAD_CAREER
AND CPX.STDNT_CAR_NBR = CPX_ED.STDNT_CAR_NBR
AND CPX.ADM_APPL_NBR = CPX_ED.ADM_APPL_NBR
AND CPX.APPL_PROG_NBR = CPX_ED.APPL_PROG_NBR
AND CPX.ADMIT_TERM = CPX_ED.ADMIT_TERM
AND CPX.ACTION_DT = CPX_ED.ACTION_DT
AND CPX.PROG_ACTION = CPX_ED.PROG_ACTION
AND CPX_ED.EFFDT <= $input_date)
AND CPX.EFFSEQ =
(SELECT MAX(CPX_ES.EFFSEQ) FROM PS_ADM_APPL_PROG CPX_ES
WHERE CPX.EMPLID = CPX_ES.EMPLID
AND CPX.ACAD_CAREER = CPX_ES.ACAD_CAREER
AND CPX.STDNT_CAR_NBR = CPX_ES.STDNT_CAR_NBR
AND CPX.ADM_APPL_NBR = CPX_ES.ADM_APPL_NBR
AND CPX.APPL_PROG_NBR = CPX_ES.APPL_PROG_NBR
AND CPX.ADMIT_TERM = CPX_ES.ADMIT_TERM
AND CPX.ACTION_DT = CPX_ES.ACTION_DT
AND CPX.PROG_ACTION = CPX_ES.PROG_ACTION
AND CPX.EFFDT = CPX_ES.EFFDT)
order by cpx.effdt, cpx.effseq
end-select
end-procedure
!***********************************************************************
! Get Description for Program Reason
!***********************************************************************
begin-procedure Get_Prog_Rsn_Descr
Let $Um_Prog_Rsn_Descr = ' '
begin-select
nvl(RSN.DESCR,' ') &rsn.descr
Let $Um_Prog_Rsn_Descr = &RSN.DESCR
FROM PS_PROG_RSN_TBL RSN
WHERE RSN.PROG_REASON = &C.PROG_REASON
AND RSN.EFF_STATUS = 'A'
AND RSN.SETID = 'UMSYS'
AND RSN.PROG_ACTION = &C.PROG_ACTION
AND RSN.EFFDT =
(SELECT MAX(RSN_ED.EFFDT) FROM PS_PROG_RSN_TBL RSN_ED
WHERE RSN.PROG_ACTION = RSN_ED.PROG_ACTION
AND RSN.PROG_REASON = RSN_ED.PROG_REASON
AND RSN.EFF_STATUS = RSN_ED.EFF_STATUS
AND RSN.SETID = RSN_ED.SETID
AND RSN_ED.EFFDT <= $input_date)
end-select
end-procedure
!***********************************************************************
! Get Application PlanData
!***********************************************************************
begin-procedure Get_Appl_Plan
Let $Acad_Plan = ' '
Let $Acad_Plan_Descr = ' '
Let $Acad_Plan_Type = ' '
Let $Degree = ' '
Let $Acad_Sub_Plan = ' '
Let $Acad_SubPlan_Descr = ' '
Let $Acad_SubPlan_Type = ' '
Let $Min_Acad_Plan = ' '
Let $Min_Acad_Plan_Descr = ' '
Let $Min_Acad_Plan_Type = ' '
Let $Min_Degree = ' '
Let $Min_Acad_Sub_Plan = ' '
Let $Min_Acad_SubPlan_Descr = ' '
Let $Min_Acad_SubPlan_Type = ' '
Let $Min_Acad_Prog = ' '
begin-select
NVL(SB12.ACAD_PLAN,' ') &SB12.ACAD_PLAN,
NVL(SB13.DESCR,' ') &SB13.DESCR,
NVL(SB13.ACAD_PLAN_TYPE,' ') &SB13.ACAD_PLAN_TYPE,
NVL(SB13.DEGREE,' ') &SB13.DEGREE,
NVL(SB14.ACAD_PLAN,' ') &SB14.ACAD_PLAN,
NVL(SB14.ACAD_SUB_PLAN,' ' ) &SB14.ACAD_SUB_PLAN,
NVL(SB15.ACAD_SUBPLAN_TYPE,' ') &SB15.ACAD_SUBPLAN_TYPE
NVL(SB15.DESCR,' ') &SB15.DESCR
SB12.REQ_TERM,
SB13.ACAD_PROG
Let $Acad_Plan = &SB12.ACAD_PLAN
Let $Acad_Plan_Descr = &SB13.DESCR
Let $Acad_Plan_Type = &SB13.ACAD_PLAN_TYPE
Let $Degree = &SB13.DEGREE
Let $Acad_Sub_Plan = &SB14.ACAD_SUB_PLAN
Let $Acad_SubPlan_Descr = &SB15.DESCR
Let $Acad_SubPlan_Type = &SB15.ACAD_SUBPLAN_TYPE
if $Min_Acad_plan <= ' '
Let $Min_Acad_Plan = &SB12.ACAD_PLAN
Let $Min_Acad_Plan_Descr = &SB13.DESCR
Let $Min_Acad_Plan_Type = &SB13.ACAD_PLAN_TYPE
Let $Min_Degree = &SB13.DEGREE
Let $Min_Acad_Sub_Plan = &SB14.ACAD_SUB_PLAN
Let $Min_Acad_SubPlan_Descr = &SB15.DESCR
Let $Min_Acad_SubPlan_Type = &SB15.ACAD_SUBPLAN_TYPE
Let $Min_ACAD_PROG = &SB13.ACAD_PROG
end-if
FROM PS_ADM_APPL_PLAN SB12, PS_ACAD_PLAN_TBL SB13, PS_ADM_APPL_SBPLAN SB14, PS_ACAD_SUBPLN_TBL SB15
WHERE SB12.EMPLID = &A.EMPLID
AND SB12.ACAD_CAREER = &B.ACAD_CAREER
AND SB12.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND SB12.APPL_PROG_NBR = &C.APPL_PROG_NBR
AND SB13.INSTITUTION = &B.INSTITUTION
AND SB13.ACAD_PLAN_TYPE in ('MAJ','DMJ','SP','PRP','CER') !??0715
AND SB12.ACAD_PLAN = SB13.ACAD_PLAN
AND SB13.EFFDT =
(SELECT MAX(SB13_ED.EFFDT) FROM PS_ACAD_PLAN_TBL SB13_ED
WHERE SB13.INSTITUTION = SB13_ED.INSTITUTION
AND SB13.ACAD_PLAN = SB13_ED.ACAD_PLAN
AND SB13_ED.EFFDT <= $input_date)
AND SB12.EMPLID = SB14.EMPLID(+)
AND SB12.ACAD_CAREER = SB14.ACAD_CAREER(+)
AND SB12.STDNT_CAR_NBR = SB14.STDNT_CAR_NBR(+)
AND SB12.ADM_APPL_NBR = SB14.ADM_APPL_NBR(+)
AND SB12.APPL_PROG_NBR = SB14.APPL_PROG_NBR(+)
AND SB12.ACAD_PLAN = SB14.ACAD_PLAN(+)
AND SB12.EFFDT = SB14.EFFDT(+)
AND SB12.EFFSEQ = SB14.EFFSEQ(+)
! AND ( SB14.EFFDT =
! (SELECT MAX(SB14_ED.EFFDT) FROM PS_ADM_APPL_SBPLAN SB14_ED
! WHERE SB14.EMPLID = SB14_ED.EMPLID
! AND SB14.ACAD_CAREER = SB14_ED.ACAD_CAREER
! AND SB14.ACAD_PLAN = SB14_ED.ACAD_PLAN
! AND SB14.STDNT_CAR_NBR = SB14_ED.STDNT_CAR_NBR
! AND SB14.ADM_APPL_NBR = SB14_ED.ADM_APPL_NBR
! AND SB14.APPL_PROG_NBR = SB14_ED.APPL_PROG_NBR
! AND SB14_ED.EFFDT <= $input_date)
!
! AND SB14.EFFSEQ =
! (SELECT MAX(SB14_ES.EFFSEQ) FROM PS_ADM_APPL_SBPLAN SB14_ES
! WHERE SB14.EMPLID = SB14_ES.EMPLID
! AND SB14.ACAD_CAREER = SB14_ES.ACAD_CAREER
! AND SB14.ACAD_PLAN = SB14_ES.ACAD_PLAN
! AND SB14.STDNT_CAR_NBR = SB14_ES.STDNT_CAR_NBR
! AND SB14.ADM_APPL_NBR = SB14_ES.ADM_APPL_NBR
! AND SB14.APPL_PROG_NBR = SB14_ES.APPL_PROG_NBR
! AND SB14.EFFDT = SB14_ES.EFFDT)
! OR SB14.EFFDT IS NULL)
AND SB14.ACAD_PLAN = SB15.ACAD_PLAN(+)
AND SB14.ACAD_SUB_PLAN = SB15.ACAD_SUB_PLAN(+)
AND ( SB15.EFFDT =
(SELECT MAX(SB15_ED.EFFDT) FROM PS_ACAD_SUBPLN_TBL SB15_ED
WHERE SB15.INSTITUTION = SB15_ED.INSTITUTION
AND SB15.ACAD_PLAN = SB15_ED.ACAD_PLAN
AND SB15.ACAD_SUB_PLAN = SB15_ED.ACAD_SUB_PLAN
AND SB15_ED.EFFDT <= $input_date)
OR SB15.EFFDT IS NULL)
order by SB12.EFFDT, SB12.EFFSEQ, SB12.PLAN_SEQUENCE
end-select
end-procedure
!***********************************************************************
! Get Student PlanData
!***********************************************************************
begin-procedure Get_Stu_Plan
Let $Acad_Plan = ' '
Let $Acad_Plan_Descr = ' '
Let $Acad_Plan_Type = ' '
Let $Degree = ' '
Let $Acad_Sub_Plan = ' '
Let $Acad_SubPlan_Descr = ' '
Let $Acad_SubPlan_Type = ' '
Let #N_plans = 0
Let $Min_Acad_Plan = ' '
Let $Min_Acad_Plan_Descr = ' '
Let $Min_Acad_Plan_Type = ' '
Let $Min_Degree = ' '
Let $Min_Acad_Sub_Plan = ' '
Let $Min_Acad_SubPlan_Descr = ' '
Let $Min_Acad_SubPlan_Type = ' '
Let $Min_Acad_Prog = ' '
! Find a date appropriate to the admisssions term
Let $This_Term_end_date = datenow()
Begin-select
nvl(min(term_begin_dt),SYSDATE) &THIS_TERM_END_DATE
Let $This_Term_end_date = &THIS_TERM_END_DATE
from ps_term_tbl rqe
where
rqe.institution = &B.INSTITUTION
and rqe.acad_career = &B.ACAD_CAREER
and rqe.strm > &C.ADMIT_TERM
end-select
begin-select
NVL(ST12.ACAD_PLAN,' ') &ST12.ACAD_PLAN,
NVL(ST12.EFFDT,to_date('01-JAN-1901','DD-MON-YYYY')) &ST12.EFFDT,
NVL(ST13.DESCR,' ') &ST13.DESCR,
NVL(ST13.ACAD_PLAN_TYPE,' ') &ST13.ACAD_PLAN_TYPE,
NVL(ST13.DEGREE,' ') &ST13.DEGREE,
NVL(ST14.ACAD_PLAN,' ') &ST14.ACAD_PLAN,
NVL(ST14.ACAD_SUB_PLAN,' ' ) &ST14.ACAD_SUB_PLAN,
NVL(ST15.ACAD_SUBPLAN_TYPE,' ') &ST15.ACAD_SUBPLAN_TYPE
NVL(ST15.DESCR,' ') &ST15.DESCR
ST12.REQ_TERM,
ST12.ACAD_CAREER,
ST12.STDNT_CAR_NBR,
ST13.ACAD_PROG
if (&ST12.EFFDT < $This_Term_end_date or $Acad_plan <= ' ')
and &ST12.ACAD_CAREER = &B.ACAD_CAREER
AND &ST12.STDNT_CAR_NBR = &C.STDNT_CAR_NBR
Let $Acad_Plan = &ST12.ACAD_PLAN
Let $Acad_Plan_Descr = &ST13.DESCR
Let $Acad_Plan_Type = &ST13.ACAD_PLAN_TYPE
Let $Degree = &ST13.DEGREE
Let $Acad_Sub_Plan = &ST14.ACAD_SUB_PLAN
Let $Acad_SubPlan_Descr = &ST15.DESCR
Let $Acad_SubPlan_Type = &ST15.ACAD_SUBPLAN_TYPE
add 1 to #N_plans
end-if
if $Min_Acad_plan <= ' '
and &ST12.ACAD_CAREER = &B.ACAD_CAREER
AND &ST12.STDNT_CAR_NBR = &C.STDNT_CAR_NBR
Let $Min_Acad_Plan = &ST12.ACAD_PLAN
Let $Min_Acad_Plan_Descr = &ST13.DESCR
Let $Min_Acad_Plan_Type = &ST13.ACAD_PLAN_TYPE
Let $Min_Degree = &ST13.DEGREE
Let $Min_Acad_Sub_Plan = &ST14.ACAD_SUB_PLAN
Let $Min_Acad_SubPlan_Descr = &ST15.DESCR
Let $Min_Acad_SubPlan_Type = &ST15.ACAD_SUBPLAN_TYPE
Let $Min_ACAD_PROG = &ST13.ACAD_PROG
end-if
FROM PS_ACAD_PLAN ST12, PS_ACAD_PLAN_TBL ST13, PS_ACAD_SUBPLAN ST14, PS_ACAD_SUBPLN_TBL ST15
WHERE ST12.EMPLID = &A.EMPLID
!-- AND ST12.ACAD_CAREER = &B.ACAD_CAREER
AND ST13.INSTITUTION = &B.INSTITUTION
AND ST13.ACAD_PLAN_TYPE in ('MAJ','DMJ','SP','PRP','CER') !??0715
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 <= $input_date)
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 ( ST14.EFFDT =
! (SELECT MAX(ST14_ED.EFFDT) FROM PS_ACAD_SUBPLAN ST14_ED
! WHERE ST14.EMPLID = ST14_ED.EMPLID
! AND ST14.ACAD_CAREER = ST14_ED.ACAD_CAREER
! AND ST14.ACAD_PLAN = ST14_ED.ACAD_PLAN
! AND ST14.STDNT_CAR_NBR = ST14_ED.STDNT_CAR_NBR
! AND ST14.ACAD_PLAN = ST14_ED.ACAD_PLAN
! AND ST14_ED.EFFDT <= $input_date)
! AND ST14.EFFSEQ =
! (SELECT MAX(ST14_ES.EFFSEQ) FROM PS_ACAD_SUBPLAN ST14_ES
! WHERE ST14.EMPLID = ST14_ES.EMPLID
! AND ST14.ACAD_CAREER = ST14_ES.ACAD_CAREER
! AND ST14.ACAD_PLAN = ST14_ES.ACAD_PLAN
! AND ST14.STDNT_CAR_NBR = ST14_ES.STDNT_CAR_NBR
! AND ST14.ACAD_PLAN = ST14_ES.ACAD_PLAN
! AND ST14.EFFDT = ST14_ES.EFFDT)
! OR ST14.EFFDT IS NULL)
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 <= $input_date)
OR ST15.EFFDT IS NULL)
order by st12.effdt, st12.effseq, st14.effdt, st14.effseq,
decode(ST13.ACAD_PLAN_TYPE,'MAJ',1,'DMJ',3,'SP',1,'PRP',1,'CON',1,'COS',1,'HON',1,'RTC',1,'CER',2,5) desc, st12.plan_sequence desc
end-select
if #n_plans = 0
show 'No plan for ' &A.EMPLID
end-if
end-Procedure
!***********************************************************************
! Get Student Response
!***********************************************************************
Begin-Procedure Get_Response
Let $Response_Reason = ' '
Let $Response_Dt = '01-JAN-1901'
Let $Response_Reason_Descr = ' '
begin-select
sb16.response_reason,
sb16.response_dt,
sb16.ext_org_id,
sb16.descr,
uj.descr
if &sb16.response_reason > ' '
Let $Response_Reason = &sb16.response_reason
end-if
if &sb16.response_dt > '01-JAN-06'
Let $Response_Dt = &sb16.response_dt
end-if
if &uj.descr > ' '
Let $Response_Reason_Descr = &uj.descr
end-if
from ps_stdnt_response sb16, ps_resp_rsn_tbl uj
where sb16.acad_career = uj.acad_career(+)
and sb16.response_reason = uj.response_reason(+)
and sb16.emplid = &a.emplid
and sb16.acad_career = &b.acad_career
and sb16.adm_appl_nbr = &b.adm_appl_nbr
and &b.institution = uj.institution(+)
end-select
end-Procedure
!***********************************************************************
! Get External Organization Table
!***********************************************************************
begin-procedure get_ext_table
Let $Ext_Org_ID1 = '0'
Let $Descr1 = ' '
Let $Ls_School_type1 = ' '
Let $City1 = ' '
Let $State1 = ' '
Let $Country1 = ' '
Let $Ext_Career1 = ' '
Let $From_Date1 = '01-JAN-1901'
Let $To_Dt1 = '01-JAN-1901'
Let $Ext_Summ_Type1 = ' '
Let $Ext_Acad_Level1 = ' '
Let #Class_Rank1 = 0
Let #Class_Size1 = 0
Let $Gpa_Type1 = ' '
Let #Ext_GPA1 = 0
Let #Convert_GPA1 = 0
Let #Percentile1 = 0
Let $Rank_type1 = ' '
Let $Ext_Org_ID2 = '0'
Let $Descr2 = ' '
Let $Ls_School_type2 =' '
Let $City2 =' '
Let $State2 = ' '
Let $Country2 =' '
Let $Ext_Career2 = ' '
Let $From_Date2 = '01-JAN-1901'
Let $To_Dt2 = '01-JAN-1901'
Let $Ext_Summ_Type2 = ' '
Let $Ext_Acad_Level2 = ' '
Let #Class_Rank2 = 0
Let #Class_Size2 = 0
Let $Gpa_Type2 = ' '
Let #Ext_GPA2 = 0
Let #Convert_GPA2 =0
Let #Percentile2 = 0
Let $Rank_type2 = ' '
Let $Ext_Org_ID3 = '0'
Let $Descr3 = ' '
Let $Ls_School_type3 = ' '
Let $City3 = ' '
Let $State3 = ' '
Let $Country3 = ' '
Let $Ext_Career3 = ' '
Let $From_Date3 = '01-JAN-1901'
Let $To_Dt3 = '01-JAN-1901'
Let $Ext_Summ_Type3 = ' '
Let $Ext_Acad_Level3 = ' '
Let #Class_Rank3 = 0
Let #Class_Size3 = 0
Let $Gpa_Type3 = ' '
Let #Ext_GPA3 = 0
Let #Convert_GPA3 = 0
Let #Percentile3 = 0
Let $Rank_type3 = ' '
Let $Ext_Org_ID4 = '0'
Let $Descr4 = ' '
Let $Ls_School_type4 = ' '
Let $City4 = ' '
Let $State4 = ' '
Let $Country4 = ' '
Let $Ext_Career4 = ' '
Let $From_Date4 = '01-JAN-1901'
Let $To_Dt4 = '01-JAN-1901'
Let $Ext_Summ_Type4 = ' '
Let $Ext_Acad_Level4 = ' '
Let #Class_Rank4 = 0
Let #Class_Size4 = 0
Let $Gpa_Type4 = ' '
Let #Ext_GPA4 = 0
Let #Convert_GPA4 = 0
Let #Percentile4 = 0
Let $Rank_type4 = ' '
Let $Ext_Org_ID5 = '0'
Let $Descr5 = ' '
Let $Ls_School_type5 = ' '
Let $City5 = ' '
Let $State5 = ' '
Let $Country5 = ' '
Let $Ext_Career5 = ' '
Let $From_Date5 = '01-JAN-1901'
Let $To_Dt5 = '01-JAN-1901'
Let $Ext_Summ_Type5 = ' '
Let $Ext_Acad_Level5 = ' '
Let #Class_Rank5 = 0
Let #Class_Size5 = 0
Let $Gpa_Type5 = ' '
Let #Ext_GPA5 = 0
Let #Convert_GPA5 = 0
Let #Percentile5 = 0
Let $Rank_type5 = ' '
Let $Ext_Org_ID6 = '0'
Let $Descr6 = ' '
Let $Ls_School_type6 = ' '
Let $City6 = ' '
Let $State6 = ' '
Let $Country6 = ' '
Let $Ext_Career6 = ' '
Let $From_Date6 = '01-JAN-1901'
Let $To_Dt6 = '01-JAN-1901'
Let $Ext_Summ_Type6 = ' '
Let $Ext_Acad_Level6 = ' '
Let #Class_Rank6 = 0
Let #Class_Size6 = 0
Let $Gpa_Type6 = ' '
Let #Ext_GPA6 = 0
Let #Convert_GPA6 = 0
Let #Percentile6 = 0
Let $Rank_type6 = ' '
Let $Ext_Org_ID7 = '0'
Let $Descr7 = ' '
Let $Ls_School_type7 = ' '
Let $City7 = ' '
Let $State7 = ' '
Let $Country7 = ' '
Let $Ext_Career7 = ' '
Let $From_Date7 = '01-JAN-1901'
Let $To_Dt7 = '01-JAN-1901'
Let $Ext_Summ_Type7 = ' '
Let $Ext_Acad_Level7 = ' '
Let #Class_Rank7 = 0
Let #Class_Size7 = 0
Let $Gpa_Type7 = ' '
Let #Ext_GPA7 = 0
Let #Convert_GPA7 = 0
Let #Percentile7 = 0
Let $Rank_type7 = ' '
Let $Ext_Org_ID8 = '0'
Let $Descr8 = ' '
Let $Ls_School_type8 = ' '
Let $City8 = ' '
Let $State8 = ' '
Let $Country8 = ' '
Let $Ext_Career8 = ' '
Let $From_Date8 = '01-JAN-1901'
Let $To_Dt8 = '01-JAN-1901'
Let $Ext_Summ_Type8 = ' '
Let $Ext_Acad_Level8 = ' '
Let #Class_Rank8 = 0
Let #Class_Size8 = 0
Let $Gpa_Type8 = ' '
Let #Ext_GPA8 = 0
Let #Convert_GPA8 = 0
Let #Percentile8 = 0
Let $Rank_type8 = ' '
Let $Ext_Org_ID9 = '0'
Let $Descr9 = ' '
Let $Ls_School_type9 = ' '
Let $City9 = ' '
Let $State9 = ' '
Let $Country9 = ' '
Let $Ext_Career9 = ' '
Let $From_Date9 = '01-JAN-1901'
Let $To_Dt9 = '01-JAN-1901'
Let $Ext_Summ_Type9 = ' '
Let $Ext_Acad_Level9 = ' '
Let #Class_Rank9 = 0
Let #Class_Size9 = 0
Let $Gpa_Type9 = ' '
Let #Ext_GPA9 = 0
Let #Convert_GPA9 = 0
Let #Percentile9 = 0
Let $Rank_type9 = ' '
Let $Ext_Org_ID10 = '0'
Let $Descr10 = ' '
Let $Ls_School_type10 = ' '
Let $City10 = ' '
Let $State10 = ' '
Let $Country10 = ' '
Let $Ext_Career10 = ' '
Let $From_Date10 = '01-JAN-1901'
Let $To_Dt10 = '01-JAN-1901'
Let $Ext_Summ_Type10 = ' '
Let $Ext_Acad_Level10 = ' '
Let #Class_Rank10 = 0
Let #Class_Size10 = 0
Let $Gpa_Type10 = ' '
Let #Ext_GPA10 = 0
Let #Convert_GPA10 = 0
Let #Percentile10 = 0
Let $Rank_type10 = ' '
Let #Tindex = 0
BEGIN-SELECT
NVL(ETA.EXT_ORG_ID,' ') &ETA.EXT_ORG_ID,
NVL(ETB.DESCR,' ') &ETB.DESCR,
NVL(ETB.LS_SCHOOL_TYPE,' ') &ETB.LS_SCHOOL_TYPE,
NVL(ETB.CITY,' ') &ETB.CITY,
NVL(ETB.STATE,' ') &ETB.STATE,
NVL(ETB.COUNTRY,' ') &ETB.COUNTRY,
NVL(ETA.EXT_CAREER,' ') &ETA.EXT_CAREER,
NVL(ETA.FROM_DT,'01-JAN-01 ') &ETA.FROM_DT,
NVL(ETA.TO_DT,'01-JAN-1901') &ETA.TO_DT,
NVL(ETB.EXT_ORG_ID,' ') &ETB.EXT_ORG_ID,
NVL(ETB.EFFDT,'01-JAN-1901') &ETB.EFFDT,
NVL(ETA.EXT_DATA_NBR,0) &ETA.EXT_DATA_NBR,
NVL(AC.EXT_SUMM_TYPE,' ') &AC.EXT_SUMM_TYPE,
NVL(AC.EXT_ACAD_LEVEL,' ') &AC.EXT_ACAD_LEVEL,
NVL(AC.CLASS_RANK,0) &AC.CLASS_RANK,
NVL(AC.CLASS_SIZE,0) &AC.CLASS_SIZE,
NVL(AC.GPA_TYPE,' ') &AC.GPA_TYPE,
NVL(AC.EXT_GPA,0) &AC.EXT_GPA,
NVL(AC.CONVERT_GPA,0) &AC.CONVERT_GPA,
NVL(AC.PERCENTILE,0) &AC.PERCENTILE,
NVL(AC.RANK_TYPE,' ') &AC.RANK_TYPE,
CASE WHEN ETA.EXT_ORG_ID = $ext_org_id then ' ' else 'X' end &WOODOO
Let #Tindex = #Tindex + 1
evaluate #Tindex
when = 1
Let $Ext_Org_Id1 = &ETA.EXT_ORG_ID
Let $Descr1 = &ETB.DESCR
Let $Ls_School_Type1 = &ETB.LS_SCHOOL_TYPE
Let $City1 = &ETB.CITY
Let $State1 = &ETB.STATE
Let $Country1 = &ETB.COUNTRY
Let $Ext_Career1 = &ETA.EXT_CAREER
Let $From_Date1 = &ETA.FROM_DT
Let $To_Dt1 = &ETA.TO_DT
if $ext_org_id <= '0' !if not obtained from previous school
Let $ext_org_id = $ext_org_id1 ! get ext_org_id here
Let $Ext_Org_Id_Descr = $Descr1
end-if
Let $Inca = 'HSOV'
If $Ls_School_type1= 'col' or $Ls_School_type1 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type1 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level1 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank1 = &AC.CLASS_RANK
Let #Class_Size1 = &AC.CLASS_SIZE
Let $Gpa_Type1 = &AC.GPA_TYPE
Let #Ext_GPA1 = &AC.EXT_GPA
Let #Convert_GPA1 = &AC.CONVERT_GPA
Let #Percentile1 = &AC.PERCENTILE
Let $Rank_type1 = &AC.RANK_TYPE
break
when = 2
Let $Ext_Org_Id2 = &ETA.EXT_ORG_ID
Let $Descr2 = &ETB.DESCR
Let $Ls_School_Type2 = &ETB.LS_SCHOOL_TYPE
Let $City2 = &ETB.CITY
Let $State2 = &ETB.STATE
Let $Country2 = &ETB.COUNTRY
Let $Ext_Career2 = &ETA.EXT_CAREER
Let $From_Date2 = &ETA.FROM_DT
Let $To_Dt2 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type2= 'col' or $Ls_School_type2 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type2 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level2 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank2 = &AC.CLASS_RANK
Let #Class_Size2 = &AC.CLASS_SIZE
Let $Gpa_Type2 = &AC.GPA_TYPE
Let #Ext_GPA2 = &AC.EXT_GPA
Let #Convert_GPA2 = &AC.CONVERT_GPA
Let #Percentile2 = &AC.PERCENTILE
Let $Rank_type2 = &AC.RANK_TYPE
break
when = 3
Let $Ext_Org_Id3 = &ETA.EXT_ORG_ID
Let $Descr3 = &ETB.DESCR
Let $Ls_School_Type3 = &ETB.LS_SCHOOL_TYPE
Let $City3 = &ETB.CITY
Let $State3 = &ETB.STATE
Let $Country3 = &ETB.COUNTRY
Let $Ext_Career3 = &ETA.EXT_CAREER
Let $From_Date3 = &ETA.FROM_DT
Let $To_Dt3 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type3= 'col' or $Ls_School_type3 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type3 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level3 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank3 = &AC.CLASS_RANK
Let #Class_Size3 = &AC.CLASS_SIZE
Let $Gpa_Type3 = &AC.GPA_TYPE
Let #Ext_GPA3 = &AC.EXT_GPA
Let #Convert_GPA3 = &AC.CONVERT_GPA
Let #Percentile3 = &AC.PERCENTILE
Let $Rank_type3 = &AC.RANK_TYPE
break
when = 4
Let $Ext_Org_Id4 = &ETA.EXT_ORG_ID
Let $Descr4 = &ETB.DESCR
Let $Ls_School_Type4 = &ETB.LS_SCHOOL_TYPE
Let $City4 = &ETB.CITY
Let $State4 = &ETB.STATE
Let $Country4 = &ETB.COUNTRY
Let $Ext_Career4 = &ETA.EXT_CAREER
Let $From_Date4 = &ETA.FROM_DT
Let $To_Dt4 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type4= 'col' or $Ls_School_type4 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type4 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level4 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank4 = &AC.CLASS_RANK
Let #Class_Size4 = &AC.CLASS_SIZE
Let $Gpa_Type4 = &AC.GPA_TYPE
Let #Ext_GPA4 = &AC.EXT_GPA
Let #Convert_GPA4 = &AC.CONVERT_GPA
Let #Percentile4 = &AC.PERCENTILE
Let $Rank_type4 = &AC.RANK_TYPE
break
when = 5
Let $Ext_Org_Id5 = &ETA.EXT_ORG_ID
Let $Descr5 = &ETB.DESCR
Let $Ls_School_Type5 = &ETB.LS_SCHOOL_TYPE
Let $City5 = &ETB.CITY
Let $State5 = &ETB.STATE
Let $Country5 = &ETB.COUNTRY
Let $Ext_Career5 = &ETA.EXT_CAREER
Let $From_Date5 = &ETA.FROM_DT
Let $To_Dt5 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type5= 'col' or $Ls_School_type5 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type5 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level5 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank5 = &AC.CLASS_RANK
Let #Class_Size5 = &AC.CLASS_SIZE
Let $Gpa_Type5 = &AC.GPA_TYPE
Let #Ext_GPA5 = &AC.EXT_GPA
Let #Convert_GPA5 = &AC.CONVERT_GPA
Let #Percentile5 = &AC.PERCENTILE
Let $Rank_type5 = &AC.RANK_TYPE
break
when = 6
Let $Ext_Org_Id6 = &ETA.EXT_ORG_ID
Let $Descr6 = &ETB.DESCR
Let $Ls_School_Type6 = &ETB.LS_SCHOOL_TYPE
Let $City6 = &ETB.CITY
Let $State6 = &ETB.STATE
Let $Country6 = &ETB.COUNTRY
Let $Ext_Career6 = &ETA.EXT_CAREER
Let $From_Date6 = &ETA.FROM_DT
Let $To_Dt6 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type6= 'col' or $Ls_School_type6 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type6 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level6 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank6 = &AC.CLASS_RANK
Let #Class_Size6 = &AC.CLASS_SIZE
Let $Gpa_Type6 = &AC.GPA_TYPE
Let #Ext_GPA6 = &AC.EXT_GPA
Let #Convert_GPA6 = &AC.CONVERT_GPA
Let #Percentile6 = &AC.PERCENTILE
Let $Rank_type6 = &AC.RANK_TYPE
break
when = 7
Let $Ext_Org_Id7 = &ETA.EXT_ORG_ID
Let $Descr7 = &ETB.DESCR
Let $Ls_School_Type7 = &ETB.LS_SCHOOL_TYPE
Let $City7 = &ETB.CITY
Let $State7 = &ETB.STATE
Let $Country7 = &ETB.COUNTRY
Let $Ext_Career7 = &ETA.EXT_CAREER
Let $From_Date7 = &ETA.FROM_DT
Let $To_Dt7 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type7= 'col' or $Ls_School_type7 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type7 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level7 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank7 = &AC.CLASS_RANK
Let #Class_Size7 = &AC.CLASS_SIZE
Let $Gpa_Type7 = &AC.GPA_TYPE
Let #Ext_GPA7 = &AC.EXT_GPA
Let #Convert_GPA7 = &AC.CONVERT_GPA
Let #Percentile7 = &AC.PERCENTILE
Let $Rank_type7 = &AC.RANK_TYPE
break
when = 8
Let $Ext_Org_Id8 = &ETA.EXT_ORG_ID
Let $Descr8 = &ETB.DESCR
Let $Ls_School_Type8 = &ETB.LS_SCHOOL_TYPE
Let $City8 = &ETB.CITY
Let $State8 = &ETB.STATE
Let $Country8 = &ETB.COUNTRY
Let $Ext_Career8 = &ETA.EXT_CAREER
Let $From_Date8 = &ETA.FROM_DT
Let $To_Dt8 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type8= 'col' or $Ls_School_type8 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type8 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level8 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank8 = &AC.CLASS_RANK
Let #Class_Size8 = &AC.CLASS_SIZE
Let $Gpa_Type8 = &AC.GPA_TYPE
Let #Ext_GPA8 = &AC.EXT_GPA
Let #Convert_GPA8 = &AC.CONVERT_GPA
Let #Percentile8 = &AC.PERCENTILE
Let $Rank_type8 = &AC.RANK_TYPE
break
when = 9
Let $Ext_Org_Id9 = &ETA.EXT_ORG_ID
Let $Descr9 = &ETB.DESCR
Let $Ls_School_Type9 = &ETB.LS_SCHOOL_TYPE
Let $City9 = &ETB.CITY
Let $State9 = &ETB.STATE
Let $Country9 = &ETB.COUNTRY
Let $Ext_Career9 = &ETA.EXT_CAREER
Let $From_Date9 = &ETA.FROM_DT
Let $To_Dt9 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type9= 'col' or $Ls_School_type9 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type9 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level9 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank9 = &AC.CLASS_RANK
Let #Class_Size9 = &AC.CLASS_SIZE
Let $Gpa_Type9 = &AC.GPA_TYPE
Let #Ext_GPA9 = &AC.EXT_GPA
Let #Convert_GPA9 = &AC.CONVERT_GPA
Let #Percentile9 = &AC.PERCENTILE
Let $Rank_type9 = &AC.RANK_TYPE
break
when = 10
Let $Ext_Org_Id10 = &ETA.EXT_ORG_ID
Let $Descr10 = &ETB.DESCR
Let $Ls_School_Type10 = &ETB.LS_SCHOOL_TYPE
Let $City10 = &ETB.CITY
Let $State10 = &ETB.STATE
Let $Country10 = &ETB.COUNTRY
Let $Ext_Career10 = &ETA.EXT_CAREER
Let $From_Date10 = &ETA.FROM_DT
Let $To_Dt10 = &ETA.TO_DT
Let $Inca = 'HSOV'
If $Ls_School_type1= 'col' or $Ls_School_type1 = 'UGOV'
Let $Inca = 'UGOV'
end-if
Let $Ext_Summ_Type10 = &AC.EXT_SUMM_TYPE
Let $Ext_Acad_Level10 = &AC.EXT_ACAD_LEVEL
Let #Class_Rank10 = &AC.CLASS_RANK
Let #Class_Size10 = &AC.CLASS_SIZE
Let $Gpa_Type10 = &AC.GPA_TYPE
Let #Ext_GPA10 = &AC.EXT_GPA
Let #Convert_GPA10 = &AC.CONVERT_GPA
Let #Percentile10 = &AC.PERCENTILE
Let $Rank_type10 = &AC.RANK_TYPE
end-evaluate
FROM PS_EXT_ACAD_DATA ETA,
(SELECT LAS.EXT_ORG_ID
, LAS.EFFDT
, LAS.EFF_STATUS
, LAS.DESCR
, LAS.OTH_NAME_SORT_SRCH
, LAS.EXT_ORG_TYPE
, LBS.LS_SCHOOL_TYPE
, LAS.CITY
, LAS.STATE
, LAS.COUNTRY
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' ) ETB,
PS_EXT_ACAD_SUM AC
WHERE ETA.EXT_ORG_ID = ETB.EXT_ORG_ID(+)
! AND ( ETB.EFFDT =
! (SELECT MAX(ETB_ED.EFFDT) FROM PS_EXT_ORG_TBL ETB_ED
! WHERE ETB.EXT_ORG_ID = ETB_ED.EXT_ORG_ID
! AND ETB_ED.EFFDT <= $input_date)
! OR ETB.EFFDT IS NULL)
and AC.EMPLID(+) = ETA.EMPLID
and AC.EXT_ORG_ID(+) = ETA.EXT_ORG_ID
and AC.EXT_CAREER(+) = ETA.EXT_CAREER
and AC.EXT_DATA_NBR(+) = ETA.EXT_DATA_NBR
AND ETA.EMPLID = &A.EMPLID
order by 22, 9 DESC
END-SELECT
end-procedure
begin-procedure zedo
Let #Num_Testo = 0
Let #Num_Compy = 0
end-procedure
begin-procedure maxxy
Let #MAX_SAT_MATH = 0
Let #MAX_SAT_VERB = 0
Let #MAX_SAT_ERWS = 0
Let #MAX_SAT_MSS = 0
Let #MAX_GRE_QUAN = 0
Let #MAX_GRE_VERB = 0
Let #MAX_MAT_WAC = 0
Let #MAX_MAT_WAP = 0
Let #MAX_GMAT_ANALYW = 0
Let #MAX_GMAT_ANALY = 0
Let #MAX_GMAT_QUAN = 0
Let #MAX_GMAT_VERB = 0
! 5 years
Let #MAX_SAT_WRIT = 0
Let #MAX_ACT_ENGL = 0
Let #MAX_ACT_MATH = 0
Let #MAX_ACT_READ = 0
Let #Max_ACT_COMP = 0
! 2 years
Let #MAX_TOEFL_COMPI = 0
Let #MAX_TOEFL_COMPP = 0
Let #MAX_TOEFL_COMPC = 0
BEGIN-SELECT
MXT.TEST_COMPONENT,
MAX(MXT.SCORE) &MXT.Maxxy
if &MXT.TEST_COMPONENT = 'MATH'
Let #MAX_SAT_MATH = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'VERB'
Let #MAX_SAT_VERB = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'WRIT'
Let #MAX_SAT_WRIT = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'ERWS'
Let #MAX_SAT_ERWS = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'MSS'
Let #MAX_SAT_MSS = &MXT.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MXT
WHERE MXT.EMPLID = &A.EMPLID
AND ( MXT.TEST_COMPONENT = 'MATH' or MXT.TEST_COMPONENT = 'VERB' or MXT.TEST_COMPONENT = 'WRIT' or MXT.TEST_COMPONENT = 'ERWS' or MXT.TEST_COMPONENT = 'MSS')
AND MXT.TEST_ID = 'SAT 1'
AND MXT.TEST_DT >= $FiveYearsAgo
group by MXT.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MOOS.TEST_COMPONENT,
MAX(MOOS.SCORE) &MOOS.Maxxy
if &MOOS.TEST_COMPONENT = 'MATH'
Let #MAX_ACT_MATH = &MOOS.Maxxy
end-if
if &MOOS.TEST_COMPONENT = 'ENGL'
Let #MAX_ACT_ENGL = &MOOS.Maxxy
end-if
if &MOOS.TEST_COMPONENT = 'COMP'
Let #MAX_ACT_COMP = &MOOS.Maxxy
end-if
if &MOOS.TEST_COMPONENT = 'READ'
Let #MAX_ACT_READ = &MOOS.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MOOS
WHERE MOOS.EMPLID = &A.EMPLID
AND ( MOOS.TEST_COMPONENT = 'MATH' or MOOS.TEST_COMPONENT = 'ENGL' or MOOS.TEST_COMPONENT = 'COMP' or MOOS.TEST_COMPONENT = 'READ' )
AND MOOS.TEST_ID = 'ACT'
AND MOOS.TEST_DT >= $FiveYearsAgo
group by MOOS.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MEEC.TEST_COMPONENT,
MAX(MEEC.SCORE) &MEEC.Maxxy
if &MEEC.TEST_COMPONENT = 'COMPI'
Let #MAX_TOEFL_COMPI = &MEEC.Maxxy
end-if
if &MEEC.TEST_COMPONENT = 'COMPP'
Let #MAX_TOEFL_COMPP = &MEEC.Maxxy
end-if
if &MEEC.TEST_COMPONENT = 'COMPC'
Let #MAX_TOEFL_COMPC = &MEEC.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MEEC
WHERE MEEC.EMPLID = &A.EMPLID
AND ( MEEC.TEST_COMPONENT = 'COMPI' or MEEC.TEST_COMPONENT = 'COMPP' or MEEC.TEST_COMPONENT = 'COMPC')
AND MEEC.TEST_ID = 'TOEFL'
AND MEEC.TEST_DT >= $TwoYearsAgo
group by MEEC.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MXV.TEST_COMPONENT,
MAX(MXV.SCORE) &MXV.Maxxy,
if &MXV.TEST_COMPONENT = 'QUAN'
Let #MAX_GRE_QUAN = &MXV.Maxxy
end-if
if &MXV.TEST_COMPONENT = 'VERB'
Let #MAX_GRE_VERB = &MXV.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MXV
WHERE MXV.EMPLID = &A.EMPLID
AND (MXV.TEST_COMPONENT = 'VERB' or MXV.TEST_COMPONENT = 'QUAN')
AND MXV.TEST_ID = 'GRE'
group by MXV.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MAT.TEST_COMPONENT,
MAX(MAT.SCORE) &MAT.Maxxy,
if &MAT.TEST_COMPONENT = 'WAC'
Let #MAX_MAT_WAC = &MAT.Maxxy
end-if
if &MAT.TEST_COMPONENT = 'WAP'
Let #MAX_MAT_WAP = &MAT.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MAT
WHERE MAT.EMPLID = &A.EMPLID
AND (MAT.TEST_COMPONENT = 'WAC' or MAT.TEST_COMPONENT = 'WAC')
AND MAT.TEST_ID = 'MAT'
group by MAT.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
GMAT.TEST_COMPONENT,
MAX(GMAT.SCORE) &GMAT.Maxxy,
if &GMAT.TEST_COMPONENT = 'ANLY'
Let #MAX_GMAT_ANALY = &GMAT.Maxxy
end-if
if &GMAT.TEST_COMPONENT = 'ANALYW'
Let #MAX_GMAT_ANALYW = &GMAT.Maxxy
end-if
if &GMAT.TEST_COMPONENT = 'QUAN'
Let #MAX_GMAT_QUAN = &GMAT.Maxxy
end-if
if &GMAT.TEST_COMPONENT = 'VERB'
Let #MAX_GMAT_VERB = &GMAT.Maxxy
end-if
FROM PS_STDNT_TEST_COMP GMAT
WHERE GMAT.EMPLID = &A.EMPLID
AND (GMAT.TEST_COMPONENT = 'ANLY' or GMAT.TEST_COMPONENT = 'ANALYW'
or GMAT.TEST_COMPONENT = 'QUAN' or GMAT.TEST_COMPONENT = 'VERB')
AND GMAT.TEST_ID = 'GMAT'
group by GMAT.TEST_COMPONENT
END-SELECT
end-procedure
begin-procedure incremento
Let #Num_Testo = #Num_Testo + 1
Let #Num_Compy = 0
end-procedure
!***********************************************************************
! Sql insert rows
!***********************************************************************
Begin-Procedure Insert_Rows
Let $Calling_Procedure = 'Insert_Rows'
if mod(#app_count,10000) = 0
show #app_count ' records so far.'
BEGIN-SQL on-error=SQL-Recover
COMMIT
END-SQL
end-if
let $Fubar_Date = &C.EFFDT
BEGIN-SQL ON-ERROR=SQL-Recover
INSERT INTO SYSADM.PS_UM_APPL_EXT_BLD
VALUES
(&A.EMPLID,
&A.LAST_NAME,
&A.FIRST_NAME,
&A.MIDDLE_NAME,
&A.NAME,
$Previous_Name,
&A.ADDRESS1,
&A.ADDRESS2,
&A.ADDRESS3,
&A.ADDRESS4,
&A.CITY,
&A.STATE,
&A.POSTAL,
&A.COUNTY,
&A.COUNTRY,
$Email_Addr,
$Phone,
&A.SEX,
&A.BIRTHDATE,
&A.BIRTHPLACE,
&A.BIRTHSTATE,
&A.BIRTHCOUNTRY,
$Nid_Usa,
$Nid_Can,
$Ethnic_Grp_Cd,
$Ethnic_Category,
$Ethnic_Descr,
&A.MAR_STATUS,
&A.MAR_STATUS_DT,
&A.DT_OF_DEATH,
$Country_Usa,
$Citizenship_Status_Usa,
$Country_Oth,
$Citizenship_Status_Oth,
$Effective_Term,
$Residency,
$Residency_Dt,
$Admission_Res,
$Admission_Excpt,
&B.ACAD_CAREER,
&C.STDNT_CAR_NBR,
&B.ADM_APPL_NBR,
&B.INSTITUTION,
&B.ADM_APPL_CTR,
&B.ADMIT_TYPE,
&B.FIN_AID_INTEREST,
&B.HOUSING_INTEREST,
&B.REGION,
&B.LAST_SCH_ATTEND,
&B.GRADUATION_DT,
&B.ACADEMIC_LEVEL,
&B.ADM_APPL_COMPLETE,
&B.ADM_APPL_DT,
&B.ADM_APPL_CMPLT_DT,
&B.ADM_APPL_METHOD,
&B.ADM_CREATION_DT,
&B.ADM_CREATION_BY,
&B.ADM_UPDATED_DT,
&B.ADM_UPDATED_BY,
&C.APPL_PROG_NBR,
$Fubar_Date,
&C.EFFSEQ,
&C.ACAD_PROG,
$Acad_Prog_Descr,
$Um_Acad_Prog_appl,
$PROG_STATUS,
$PROG_ACTION,
&C.ACTION_DT,
$PROG_REASON,
$Um_Prog_Rsn_Descr,
&C.ADMIT_TERM,
&C.EXP_GRAD_TERM,
$Acad_Plan,
$Acad_Plan_Descr,
$Um_Acad_Plan_Appl,
$Acad_Plan_Type,
$Academic_Group, !??0607
$Academic_Group_Descr, !??0607
$Degree,
$Acad_Sub_Plan,
$Acad_SubPlan_Descr,
$Acad_SubPlan_Type,
$Response_Reason,
$Response_Reason_Descr,
$Response_Dt,
$Ext_Org_Id,
$Ext_Org_Id_Descr,
$Ext_Org_Id1, $Descr1, $Ls_School_Type1, $City1, $State1, $Country1, $Ext_Career1, $From_Date1, $To_Dt1, $Ext_Summ_Type1, $Ext_Acad_Level1,
#Class_Rank1, #Class_Size1, $Gpa_Type1, #Ext_GPA1, #Convert_GPA1, #Percentile1, $Rank_type1,
$Ext_Org_Id2, $Descr2, $Ls_School_Type2, $City2, $State2, $Country2, $Ext_Career2, $From_Date2, $To_Dt2, $Ext_Summ_Type2, $Ext_Acad_Level2,
#Class_Rank2, #Class_Size2, $Gpa_Type2, #Ext_GPA2, #Convert_GPA2, #Percentile2, $Rank_type2,
$Ext_Org_Id3, $Descr3, $Ls_School_Type3, $City3, $State3, $Country3, $Ext_Career3, $From_Date3, $To_Dt3, $Ext_Summ_Type3, $Ext_Acad_Level3,
#Class_Rank3, #Class_Size3, $Gpa_Type3, #Ext_GPA3, #Convert_GPA3, #Percentile3, $Rank_type3,
$Ext_Org_Id4, $Descr4, $Ls_School_Type4, $City4, $State4, $Country4, $Ext_Career4, $From_Date4, $To_Dt4, $Ext_Summ_Type4, $Ext_Acad_Level4,
#Class_Rank4, #Class_Size4, $Gpa_Type4, #Ext_GPA4, #Convert_GPA4, #Percentile4, $Rank_type4,
$Ext_Org_Id5, $Descr5, $Ls_School_Type5, $City5, $State5, $Country5, $Ext_Career5, $From_Date5, $To_Dt5, $Ext_Summ_Type5, $Ext_Acad_Level5,
#Class_Rank5, #Class_Size5, $Gpa_Type5, #Ext_GPA5, #Convert_GPA5, #Percentile5, $Rank_type5,
$Ext_Org_Id6, $Descr6, $Ls_School_Type6, $City6, $State6, $Country6, $Ext_Career6, $From_Date6, $To_Dt6, $Ext_Summ_Type6, $Ext_Acad_Level6,
#Class_Rank6, #Class_Size6, $Gpa_Type6, #Ext_GPA6, #Convert_GPA6, #Percentile6, $Rank_type6,
$Ext_Org_Id7, $Descr7, $Ls_School_Type7, $City7, $State7, $Country7, $Ext_Career7, $From_Date7, $To_Dt7, $Ext_Summ_Type7, $Ext_Acad_Level7,
#Class_Rank7, #Class_Size7, $Gpa_Type7, #Ext_GPA7, #Convert_GPA7, #Percentile7, $Rank_type7,
$Ext_Org_Id8, $Descr8, $Ls_School_Type8, $City8, $State8, $Country8, $Ext_Career8, $From_Date8, $To_Dt8, $Ext_Summ_Type8, $Ext_Acad_Level8,
#Class_Rank8, #Class_Size8, $Gpa_Type8, #Ext_GPA8, #Convert_GPA8, #Percentile8, $Rank_type8,
$Ext_Org_Id9, $Descr9, $Ls_School_Type9, $City9, $State9, $Country9, $Ext_Career9, $From_Date9, $To_Dt9, $Ext_Summ_Type9, $Ext_Acad_Level9,
#Class_Rank9, #Class_Size9, $Gpa_Type9, #Ext_GPA9, #Convert_GPA9, #Percentile9, $Rank_type9,
$Ext_Org_Id10, $Descr10, $Ls_School_Type10, $City10, $State10, $Country10, $Ext_Career10, $From_Date10, $To_Dt10, $Ext_Summ_Type10, $Ext_Acad_Level10,
#Class_Rank10, #Class_Size10, $Gpa_Type10, #Ext_GPA10, #Convert_GPA10, #Percentile10, $Rank_type10,
#Max_SAT_Math, #Max_SAT_Verb,
$First_Prog_Action, $First_Prog_Action_date, #Max_GRE_QUAN, #Max_GRE_VERB, $Last_Sch_Descr, $Last_Sch_Type,
#Max_SAT_WRIT, #Max_ACT_MATH, #Max_ACT_ENGL, #MAX_ACT_COMP, #MAX_ACT_READ, #MAX_TOEFL_COMPI, #MAX_TOEFL_COMPP, #MAX_TOEFL_COMPC,
$Min_Acad_prog, $Min_Acad_Plan, $Min_Acad_Sub_Plan ,&C.CAMPUS,
$ADMT_PROG1, $ADMT_PROG2, $ADMT_PROG3, $ADMT_PROG4,
$COND_PROG1, $COND_PROG2,$COND_PROG3,$COND_PROG4,
$APPL_PROG1, $APPL_PROG2,$APPL_PROG3,$APPL_PROG4,
$DEIN_PROG1, $DEIN_PROG2, $DEIN_PROG3, $DEIN_PROG4,
$WADM_PROG1, $WADM_PROG2, $WADM_PROG3, $WADM_PROG4,
$DENY_PROG1, $DENY_PROG2, $DENY_PROG3, $DENY_PROG4,
$RECN_PROG1, $RECN_PROG2, $RECN_PROG3, $RECN_PROG4,
$WAPP_PROG1, $WAPP_PROG2, $WAPP_PROG3, $WAPP_PROG4 ,
$UM_APP_DATE, $UM_COMPLETE_DATE, $UM_ADMIT_DATE, $UM_NETCONFIRM_DATE, $UM_GROSSCONFIRM_DATE,
$UM_ENROLL_DATE, $UM_DEFER_DATE, $UM_DENY_DATE, $UM_WADMINC_DATE, $UM_WADMTOT_DATE,
$UM_WAPPINC_DATE, $UM_WAPPTOTAL_DATE, $UM_WADMADMIT_DATE, $UM_NOSHOW_DATE, $UM_NODECISION_DATE, $UM_WAPPDEF_DATE, $UM_WAPPDEF_TERM,
$UM_WAPPDEF_PLAN, $UM_WAPPDEF_SUBPLAN, $UM_WAPPDEF_PRGSTAT, $UM_WAPPDEF_PROGACT, $UM_WAPPDEF_PRGREAS,
&DATASOURCE, &Recalculated_input_date, $Row_check ,$Last_Prog_Action, $UM_WAIT_DATE, $UM_DEIN_DATE, $UM_MATRIC_DATE,
#MAX_MAT_WAC, #MAX_MAT_WAP, #MAX_GMAT_ANALY, #MAX_GMAT_ANALYW, #MAX_GMAT_QUAN, #MAX_GMAT_VERB
,&C.ACAD_LOAD_APPR, $UM_DISCONTINUE_DATE, $Email_Hom_Addr,$Ethnic_group, $Hisp_latino, $Multi_Race, $UM_ETH_GRP_DESCR
,&B.APPL_FEE_TYPE, &C.REQ_TERM,
&B.RECRUITER_ID,#nl_index,#MAX_SAT_ERWS, #MAX_SAT_MSS
)
END-SQL
End-Procedure
!***********************************************************************! ??1004
! Scan Student Career Term Table ! ??1004
!***********************************************************************! ??1004
begin-procedure Scan_Student_Car_Term !??1004
Let #Student_Car_term_no = 0 !??1004
let $fully_enrl_dt = '01-JAN-1901'
begin-select !??1004
STUCAR.REG_CARD_DATE,
stucar.fully_enrl_dt !??1004
rownum &Student_Car_term_no !??1004
Let #Student_Car_term_no = &Student_Car_term_no !??1004
Let $Fully_enrl_dt = &stucar.fully_enrl_dt !??1004
FROM PS_STDNT_CAR_TERM STUCAR !??1004
WHERE STUCAR.EMPLID = &A.EMPLID !??1004
AND STUCAR.INSTITUTION = &B.INSTITUTION !??1004
AND STUCAR.ACAD_CAREER = &B.ACAD_CAREER !??1004
AND STUCAR.STRM = &C.ADMIT_TERM !??1004
end-select
end-procedure
!***********************************************************************
! Scan Student and Admissions Stack from old to new
!***********************************************************************
begin-procedure Scan_Em_all
Let #Max_appl_prog_nbr = 0
Let $Found_Defer_date = '01-JAN-1901'
Let #found_Defer_effseq = 0
Let $Found_Defer_term = '0000'
Let $Found_Defer_plan = ' '
Let $Found_Defer_subplan = ' '
Let $Found_Defer_prgstat = ' '
Let $Found_Defer_progact = ' '
Let $Found_Defer_prgreas = ' '
Let $Found_defer = 'N'
Let $Top_date_last_term = '01-JAN-1901'
Let #Top_seq_last_term = 0
Let $Top_plan_last_term = ' '
Let $Top_subplan_last_term = ' '
Let $Top_prgstat_last_term = ' '
Let $Top_progact_last_term = ' '
Let $Top_prgreas_last_term = ' '
Let $Exists_Term_After_Defer = 'N'
Let $last_term = '0000'
Let $Top_action_date_last_term = '01-JAN-1901'
let $NET_CONFIRM_PROG_STATUS = ' '
Let $NET_CONFIRM_PROG_ACTION = ' '
Let $Net_Confirm_ADM_APPL_NBR = '0000000'
Let $First_Prog_Action = ' '
Let $First_Prog_Action_date = '01-JAN-1901'
Let $ADMT_PROG1 = 'N'
Let $ADMT_PROG2 = 'N'
let $ADMT_PROG3 = 'N'
let $ADMT_PROG4 = 'N'
Let $COND_PROG1 = 'N'
Let $COND_PROG2 = 'N'
let $COND_PROG3 = 'N'
let $COND_PROG4 = 'N'
Let $APPL_PROG1 = 'N'
Let $APPL_PROG2 = 'N'
let $APPL_PROG3 = 'N'
let $APPL_PROG4 = 'N'
Let $DEIN_PROG1 = 'N'
Let $DEIN_PROG2 = 'N'
let $DEIN_PROG3 = 'N'
let $DEIN_PROG4 = 'N'
Let $WADM_PROG1 = 'N'
Let $WADM_PROG2 = 'N'
let $WADM_PROG3 = 'N'
let $WADM_PROG4 = 'N'
Let $DENY_PROG1 = 'N'
Let $DENY_PROG2 = 'N'
let $DENY_PROG3 = 'N'
let $DENY_PROG4 = 'N'
Let $RECN_PROG1 = 'N'
Let $RECN_PROG2 = 'N'
let $RECN_PROG3 = 'N'
let $RECN_PROG4 = 'N'
Let $WAPP_PROG1 = 'N'
Let $WAPP_PROG2 = 'N'
let $WAPP_PROG3 = 'N'
let $WAPP_PROG4 = 'N'
! This loop finds critical dates that match the student career number
Let $cancel_row_date = '01-JAN-1901'
Let $matr_row_date = '01-JAN-1901'
Let $defer_row_date = '01-JAN-1901'
! Look in either student or admissions
if &datasource = 'ST' !??0910
Begin-Select
cnx.emplid,
cnx.acad_career,
cnx.adm_appl_nbr,
cnx.appl_prog_nbr,
cnx.stdnt_car_nbr,
cnx.effdt,
cnx.effseq,
cnx.institution,
cnx.acad_prog,
cnx.prog_status,
cnx.prog_action,
cnx.action_dt,
cnx.prog_reason,
cnx.admit_term
!cnx.datasource
if &CNX.PROG_STATUS = 'CN'
let $cancel_row_date = &CNX.EFFDT
end-if
if &CNX.PROG_ACTION = 'MATR'
let $matr_row_date = &CNX.EFFDT
end-if
if &CNX.PROG_ACTION = 'DEFR'
let $defer_row_date = &CNX.EFFDT
end-if
if &cnx.prog_action = 'DISC' ! REPORT-170 PSA 04/14 **** latest date like the rest
let $UM_DISCONTINUE_DATE = &cnx.ACTION_DT ! REPORT-170 PSA 04/14 ****
end-if ! REPORT-170 PSA 04/14 ****
FROM
PS_ACAD_PROG CNX
WHERE
CNX.INSTITUTION = &B.INSTITUTION
AND CNX.ACAD_CAREER = &B.ACAD_CAREER
AND CNX.EMPLID = &A.EMPLID
AND CNX.ADMIT_TERM = &C.ADMIT_TERM
AND CNX.STDNT_CAR_NBR = &C.STDNT_CAR_NBR
AND CNX.EFFDT <= &Recalculated_input_date
order by cnx.effdt , cnx.effseq
end-select
else !??0910
Begin-Select !??0910 start
acnx.emplid,
acnx.acad_career,
acnx.adm_appl_nbr,
acnx.appl_prog_nbr,
acnx.effdt,
acnx.effseq,
acnx.institution,
acnx.acad_prog,
acnx.prog_status,
acnx.prog_action,
acnx.action_dt,
acnx.prog_reason,
acnx.admit_term
!cnx.datasource
if &ACNX.PROG_STATUS = 'CN'
let $cancel_row_date = &ACNX.EFFDT
end-if
if &ACNX.PROG_ACTION = 'MATR'
let $matr_row_date = &ACNX.EFFDT
end-if
! if &CNX.PROG_ACTION = 'DEFR' or &CNX.PROG_ACTION = 'DDEF'
if &ACNX.PROG_ACTION = 'DEFR'
let $defer_row_date = &ACNX.EFFDT
end-if
if &acnx.prog_action = 'DISC' ! REPORT-170 PSA 04/14 **** latest date like the rest
let $UM_DISCONTINUE_DATE = &acnx.ACTION_DT ! REPORT-170 PSA 04/14
end-if ! REPORT-170 PSA 04/14
FROM
PS_ADM_APPL_PROG ACNX
WHERE
ACNX.INSTITUTION = &B.INSTITUTION
AND ACNX.ACAD_CAREER = &B.ACAD_CAREER
AND ACNX.EMPLID = &A.EMPLID
AND ACNX.ADMIT_TERM = &C.ADMIT_TERM
AND ACNX.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND ACNX.EFFDT <= &Recalculated_input_date
order by acnx.effdt , acnx.effseq
end-select
end-if !??0910 end
! most dates are set here - student career number is not always a factor
Begin-Select
x.emplid,
x.acad_career,
x.adm_appl_nbr,
x.appl_prog_nbr,
x.stdnt_car_nbr,
xplus.adm_appl_complete,
xplus.adm_appl_cmplt_dt,
xplus.adm_appl_dt,
x.effdt,
x.effseq,
x.institution,
x.acad_prog,
!x.descr_prog,
x.prog_status,
x.prog_action,
x.action_dt,
x.prog_reason,
x.admit_term,
x.exp_grad_term,
x.req_term,
x.acad_load_appr,
x.campus,
x.acad_prog_dual,
x.joint_prog_appr,
x.acad_plan,
!x.acad_plan_type,
!x.plan_descr,
xplan.acad_plan_type
xplan.descr
x.declare_dt,
x.plan_sequence,
nvl(x.acad_sub_plan,' ') &X.ACAD_SUB_PLAN,
!x.acad_subplan_type,
!x.um_subpln_descr,
nvl(xsub.acad_subplan_type,' ') &XSUB.ACAD_SUBPLAN_TYPE,
nvl(xsub.descr,' ') &XSUB.DESCR,
!x.row_check,
!x.row_count3
CASE WHEN (row_number () over ( partition BY x.emplid
, x.acad_career
, x.adm_appl_nbr
ORDER BY x.effdt DESC
, x.effseq DESC nulls LAST)) = 1 THEN 'Y' ELSE 'N' END &Row_check,
!LEAD (X.PROG_STATUS,1,' ') OVER ( order by x.effdt, x.effseq) as &Next_PROG_STATUS,
!LEAD (X.EFFDT,1,'01-JAN-1901') OVER (order by x.effdt, x.effseq) as &Next_EFFDT,
MAX(X.EFFDT) OVER (partition by x.institution, x.acad_career, x.adm_appl_nbr, x.appl_prog_nbr, x.emplid) as &MAXEFFDT,
MAX(X.EFFSEQ) OVER (partition by x.institution, x.acad_career, x.adm_appl_nbr, x.appl_prog_nbr, x.emplid, x.effdt) as &MAXEFFSEQ,
x.datasource,
(SELECT count(*) FROM ps_acad_prog qtz
WHERE qtz.emplid = x.emplid
and qtz.institution = x.institution
and qtz.admit_term = x.admit_term
AND qtz.acad_career = x.acad_career
AND qtz.adm_appl_nbr = x.adm_appl_nbr
AND qtz.appl_prog_nbr = x.appl_prog_nbr
AND qtz.prog_status = x.prog_status
AND qtz.prog_action = x.prog_action
and qtz.action_dt = x.action_dt) as &itexists
if &X.APPL_PROG_NBR > #Max_appl_prog_nbr
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.ADMIT_TERM <= &C.ADMIT_TERM
Let #Max_appl_prog_nbr = &X.APPL_PROG_NBR
end-if
if (&X.PROG_ACTION = 'ADMT' or &X.PROG_ACTION = 'COND')
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.ADMIT_TERM <= &C.ADMIT_TERM
! if $First_Prog_Action_date = '01-JAN-1901'
Let $First_Prog_Action = 'ADMT'
Let $First_Prog_Action_date = &x.ACTION_DT
! end-if
end-if
! <<Deferred>>
! if (&X.prog_action = 'DEFR' or &X.prog_action = 'DDEF')
if &X.prog_action = 'DEFR'
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and (
&X.DATASOURCE = 'AD'
or &X.DATASOURCE = &DATASOURCE
)
let $UM_DEFER_DATE = &X.ACTION_DT
end-if
! <<Denied>> (only look on admissions stack) (changed this)
if &X.PROG_ACTION = 'DENY'
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
and &X.DATASOURCE = 'AD'
let $UM_DENY_DATE = &x.ACTION_DT
end-if
if (&X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.ADM_APPL_NBR = &B.ADM_APPL_NBR and &X.DATASOURCE = 'AD' )
or ( &X.ADMIT_TERM = &C.ADMIT_TERM and &X.STDNT_CAR_NBR = &C.STDNT_CAR_NBR and &X.DATASOURCE = 'ST')
! and (&X.ADMIT_TERM = &C.ADMIT_TERM or &X.DATASOURCE = 'AD')
evaluate &X.PROG_ACTION
when = 'ADMT'
evaluate &X.APPL_PROG_NBR
when = 0
let $ADMT_PROG1 = 'Y'
break
when = 1
let $ADMT_PROG2 = 'Y'
when = 2
let $ADMT_PROG3 = 'Y'
when = 3
let $ADMT_PROG4 = 'Y'
end-evaluate
break
when = 'COND'
evaluate &X.APPL_PROG_NBR
when = 0
let $COND_PROG1 = 'Y'
break
when = 1
let $COND_PROG2 = 'Y'
when = 2
let $COND_PROG3 = 'Y'
when = 3
let $COND_PROG4 = 'Y'
end-evaluate
break
when = 'APPL'
evaluate &X.APPL_PROG_NBR
when = 0
let $APPL_PROG1 = 'Y'
break
when = 1
let $APPL_PROG2 = 'Y'
when = 2
let $APPL_PROG3 = 'Y'
when = 3
let $APPL_PROG4 = 'Y'
end-evaluate
break
when = 'DEIN'
evaluate &X.APPL_PROG_NBR
when = 0
let $DEIN_PROG1 = 'Y'
break
when = 1
let $DEIN_PROG2 = 'Y'
when = 2
let $DEIN_PROG3 = 'Y'
when = 3
let $DEIN_PROG4 = 'Y'
end-evaluate
break
when = 'WADM'
evaluate &X.APPL_PROG_NBR
when = 0
let $WADM_PROG1 = 'Y'
break
when = 1
let $WADM_PROG2 = 'Y'
when = 2
let $WADM_PROG3 = 'Y'
when = 3
let $WADM_PROG4 = 'Y'
end-evaluate
break
when = 'DENY'
evaluate &X.APPL_PROG_NBR
when = 0
let $DENY_PROG1 = 'Y'
break
when = 1
let $DENY_PROG2 = 'Y'
when = 2
let $DENY_PROG3 = 'Y'
when = 3
let $DENY_PROG4 = 'Y'
end-evaluate
break
when = 'RECN'
evaluate &X.APPL_PROG_NBR
when = 0
let $RECN_PROG1 = 'Y'
break
when = 1
let $RECN_PROG2 = 'Y'
when = 2
let $RECN_PROG3 = 'Y'
when = 3
let $RECN_PROG4 = 'Y'
end-evaluate
break
when = 'WAPP'
evaluate &X.APPL_PROG_NBR
when = 0
let $WAPP_PROG1 = 'Y'
break
when = 1
let $WAPP_PROG2 = 'Y'
when = 2
let $WAPP_PROG3 = 'Y'
when = 3
let $WAPP_PROG4 = 'Y'
end-evaluate
break
end-evaluate
end-if
! <<Defer Action >>
! if (&X.PROG_ACTION = 'DEFR' or ( &X.PROG_ACTION = 'DDEF' and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR))
if &X.PROG_ACTION = 'DEFR'
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.DATASOURCE = 'AD'
Let $Found_Defer = 'Y'
Let $Found_defer_date = &X.effdt
Let #found_defer_effseq = &x.effseq
Let $Found_defer_term = &X.ADMIT_TERM
Let $Found_defer_plan = &X.ACAD_PLAN
Let $Found_defer_subplan = &X.ACAD_SUB_PLAN
Let $Found_defer_prgstat = &X.PROG_STATUS
Let $Found_defer_progact = &X.PROG_ACTION
Let $Found_defer_prgreas = &X.PROG_REASON
end-if
if &X.ADMIT_TERM < &C.ADMIT_TERM
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.DATASOURCE = 'AD'
and (&X.DATASOURCE = 'AD' or &X.DATASOURCE = &DATASOURCE)
let #top_seq_last_term = &X.EFFSEQ
let $Top_action_date_last_term = &X.ACTION_DT
let $Last_term = &X.ADMIT_TERM
Let $Top_plan_last_term = &X.ACAD_PLAN
Let $Top_subplan_last_term = &X.ACAD_SUB_PLAN
Let $Top_prgstat_last_term = &X.PROG_STATUS
Let $Top_progact_last_term = &X.PROG_ACTION
Let $Top_prgreas_last_term = &X.PROG_REASON
end-if
if $Found_Defer = 'Y' and &X.ADMIT_TERM > $Found_Defer_term
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.DATASOURCE = 'AD'
Let $Exists_Term_After_Defer = 'Y'
end-if
! <<Applied>>
if
$UM_APP_DATE = '01-JAN-1901' and
&X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &x.datasource = 'AD'
Let $UM_APP_DATE = &XPLUS.ADM_APPL_DT
end-if
! << Application Complete>>
if &XPLUS.ADM_APPL_COMPLETE = 'Y'
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
AND &X.ADMIT_TERM = &C.ADMIT_TERM
if $UM_COMPLETE_DATE = '01-JAN-1901'
Let $UM_COMPLETE_DATE = &XPLUS.ADM_APPL_CMPLT_DT
end-if
end-if
! <<Admitted>>
if (&X.PROG_STATUS = 'AD' or
&X.PROG_STATUS = 'PM' or
&X.PROG_STATUS = 'AC' )
and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.DATASOURCE = 'AD'
if $UM_ADMIT_DATE = '01-JAN-1901'
let $UM_ADMIT_DATE = &X.ACTION_DT
end-if
end-if
! <<WaitList Date >>
if (&X.prog_action = 'WAIT' or
&X.prog_action = 'WAOF')
and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and ( &X.ADM_APPL_NBR = &C.ADM_APPL_NBR or &X.ADM_APPL_NBR <= '' )
! if $UM_WAIT_DATE = '01-JAN-1901'
let $UM_WAIT_DATE = &X.ACTION_DT
! end-if
end-if
! <<DEIN Date >>
if (&X.prog_action = 'DEIN'
and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and (&X.ADM_APPL_NBR = &C.ADM_APPL_NBR or &X.ADM_APPL_NBR <= '' ) )
or (&X.prog_action = 'DEIN' and &X.DATASOURCE = 'ST'
and &X.ADMIT_TERM = &C.ADMIT_TERM )
! if $UM_DEIN_DATE = '01-JAN-1901'
let $UM_DEIN_DATE = &X.ACTION_DT
! end-if
end-if
! <<MATRIC Date >>
if &X.prog_action = 'MATR'
and &X.ADMIT_TERM <= &C.ADMIT_TERM
! and (&X.APPL_PROG_NBR = &C.APPL_PROG_NBR)
! and (&X.ADM_APPL_NBR = &C.ADM_APPL_NBR or &X.ADM_APPL_NBR <= '' or &X.APPL_PROG_NBR = &C.APPL_PROG_NBR or &DATASOURCE = 'ST')
and
(
(&X.ADM_APPL_NBR = &C.ADM_APPL_NBR and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR)
or (&X.DATASOURCE = 'ST' AND &X.ADMIT_TERM = &C.ADMIT_TERM))
let $UM_MATRIC_DATE = &X.ACTION_DT
end-if
! <<Gross Confirmed>> (same as net confirmed here)
if (&X.prog_status = 'PM' and &X.prog_action <> 'ACTI' )
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.DATASOURCE = 'AD'
if $UM_GROSSCONFIRM_DATE = '01-JAN-1901' or $UM_GROSSCONFIRM_DATE > &X.ACTION_DT
let $UM_GROSSCONFIRM_DATE = &X.ACTION_DT
end-if
end-if
! <<Gross Confirm>>
if (( &x.prog_status = 'AC' and &x.prog_action = 'MATR' ) or
! (&x.prog_status = 'AC' and (&x.prog_action = 'DEFR' or &x.prog_action = 'DDEF')) )
(&x.prog_status = 'AC' and &x.prog_action = 'DEFR' ) )
and &X.ADMIT_TERM = &C.ADMIT_TERM
and &X.DATASOURCE = 'ST'
if $UM_GROSSCONFIRM_DATE = '01-JAN-1901'
Let $UM_GROSSCONFIRM_DATE = &x.ACTION_DT
end-if
end-if
! <<Net Confirmed>>
if ((&X.prog_status = 'PM' and &X.prog_action <> 'ACTI' )
and &X.ADM_APPL_NBR = &C.ADM_APPL_NBR and &X.ADMIT_TERM <= &C.ADMIT_TERM
and &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
)
or (
(( &X.prog_action = 'MATR' and $matr_row_date > $cancel_row_date and $matr_row_date > $UM_DISCONTINUE_DATE) ! REPORT-170 PSA 04/14
or ! **** REPORT-170 PSA 04/14 ****
! ((&X.prog_action = 'DEFR' or &X.prog_action = 'DDEF') and $defer_row_date > $cancel_row_date))
(&X.prog_action = 'DEFR' and $defer_row_date > $cancel_row_date
and $UM_GROSSCONFIRM_DATE <> '01-JAN-1901' and $defer_row_date > $UM_DISCONTINUE_DATE) ! REPORT-170 PSA 04/14
)
! AND &DATASOURCE = 'ST'
! AND &X.STDNT_CAR_NBR = &C.STDNT_CAR_NBR
AND ((&itexists = 0 and &X.STDNT_CAR_NBR = &C.STDNT_CAR_NBR) or &X.DATASOURCE = 'ST')
AND &X.ADMIT_TERM = &C.ADMIT_TERM
AND &X.EFFDT <= &Recalculated_input_date
)
if $UM_NETCONFIRM_DATE = '01-JAN-1901' or $UM_NETCONFIRM_DATE > &X.ACTION_DT
let $UM_NETCONFIRM_DATE = &X.ACTION_DT
let $Net_confirm_prog_status = &X.prog_status
let $Net_confirm_Prog_action = &X.PROG_ACTION
Let $Net_Confirm_ADM_APPL_NBR = &X.ADM_APPL_NBR
end-if
! <<Confirmed & Enrolled >>
if #student_car_term_no > 0 ! From scan_student_car_term
if $UM_ENROLL_DATE = '01-JAN-1901'
let $UM_ENROLL_DATE = $Fully_enrl_dt ! From scan_student_car_term
end-if
! <<No Decision>> Part 1
if $UM_NODECISION_DATE = '01-JAN-1901' and
(&X.prog_status = 'PM' and &X.prog_action <> 'ACTI' )
let $UM_NODECISION_DATE = &X.ACTION_DT
end-if
end-if
end-if
! << DISC Date >> !??0611 04/30 PSA
! if &x.prog_action = 'DISC' !
! and &x.admit_term = &C.admit_term !
! if $UM_DISCONTINUE_DATE = '01-JAN-1901' !
! let $UM_DISCONTINUE_DATE = &x.ACTION_DT !
! end-if !
! end-if !
! <<< POTENTIAL LAST ACTION >>>>>
!
! <<Administrative Withdrwal incomplete>> (Last action)
if &X.PROG_ACTION = 'WADM' and &X.PROG_REASON = 'INC'
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
AND &X.EFFDT = &MAXEFFDT
AND &X.EFFSEQ = &MAXEFFSEQ
let $UM_WADMINC_DATE = &X.ACTION_DT
! <<No Decision>> Part 2
let $UM_NODECISION_DATE = &X.ACTION_DT
end-if
if &X.PROG_ACTION = 'WAPP' and &X.PROG_REASON = 'AAPP'
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
let $UM_WAPPINC_DATE = &X.ACTION_DT
! <<No Decision>> Part 3
let $UM_NODECISION_DATE = &X.ACTION_DT
end-if
! <<Administrative Withdrwal total>>
if &X.PROG_ACTION = 'WADM'
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and (&X.DATASOURCE = 'AD'
or (&X.STDNT_CAR_NBR = &C.STDNT_CAR_NBR and &X.ADMIT_TERM = &C.ADMIT_TERM) )
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
let $UM_WADMTOT_DATE = &X.ACTION_DT
end-if
! <<Applicant withdrawal total>>
if &X.PROG_ACTION = 'WAPP'
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
and &X.ADMIT_TERM = &C.ADMIT_TERM
let $UM_WAPPTOTAL_DATE = &X.ACTION_DT
end-if
! <<Admitted and withdrawn>>
if &X.PROG_STATUS = 'CN'
and (&X.PROG_REASON = 'AADM' or &X.PROG_REASON = 'PMAT' or &X.PROG_REASON = 'PMNO'
or &X.PROG_REASON = 'DEFR' or &X.PROG_REASON = 'WFDA' or &X.PROG_REASON = 'APRM'
or &X.PROG_REASON = 'NSHW' or &X.PROG_REASON = 'WMRG' or &X.PROG_REASON = 'WMRA'
or &X.PROG_REASON = 'AUTO' or &X.PROG_REASON = 'DDEF' )
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
let $UM_WADMADMIT_DATE = &X.ACTION_DT
end-if
! <<No Show>>
if &X.PROG_STATUS = 'CN' and &X.PROG_REASON = 'NSHW'
AND &X.INSTITUTION = &B.INSTITUTION
AND &X.ACAD_CAREER = &B.ACAD_CAREER
AND &X.EMPLID = &A.EMPLID
AND &X.ADM_APPL_NBR = &B.ADM_APPL_NBR
AND &X.APPL_PROG_NBR = &C.APPL_PROG_NBR
and &X.EFFDT = &MAXEFFDT
and &X.EFFSEQ = &MAXEFFSEQ
Let $UM_NOSHOW_DATE = &X.ACTION_DT
end-if
FROM
( SELECT a.emplid
, a.acad_career
, a.stdnt_car_nbr
, a.adm_appl_nbr
, a.appl_prog_nbr
, a.effdt
, a.effseq
, a.institution
, a.acad_prog
, a.prog_status
, a.prog_action
, a.action_dt
, a.prog_reason
, a.admit_term
, a.exp_grad_term
, a.req_term
, a.acad_load_appr
, a.campus
, a.acad_prog_dual
, a.joint_prog_appr
, b.acad_plan
, b.declare_dt
, b.plan_sequence
, c.acad_sub_plan
,'AD' as DATASOURCE
FROM ps_adm_appl_prog a
, ps_adm_appl_plan b
, ps_adm_appl_sbplan c
WHERE b.emplid(+) = a.emplid
AND b.acad_career(+) = a.acad_career
AND b.adm_appl_nbr(+) = a.adm_appl_nbr
AND b.appl_prog_nbr(+) = a.appl_prog_nbr
AND b.effdt(+) = a.effdt
AND b.effseq(+) = a.effseq
AND c.emplid(+) = b.emplid
AND c.acad_career(+) = b.acad_career
AND c.adm_appl_nbr(+) = b.adm_appl_nbr
AND c.appl_prog_nbr(+) = b.appl_prog_nbr
AND c.effdt(+) = b.effdt
AND c.effseq(+) = b.effseq
AND c.acad_plan(+) = b.acad_plan
UNION
SELECT a2.emplid
, a2.acad_career
, a2.stdnt_car_nbr
, a2.adm_appl_nbr
, a2.appl_prog_nbr
, a2.effdt
, a2.effseq
, a2.institution
, a2.acad_prog
, a2.prog_status
, a2.prog_action
, a2.action_dt
, a2.prog_reason
, a2.admit_term
, a2.exp_grad_term
, a2.req_term
, a2.acad_load_appr
, a2.campus
, a2.acad_prog_dual
, a2.joint_prog_appr
, b2.acad_plan
, b2.declare_dt
, b2.plan_sequence
, c2.acad_sub_plan
,'ST'
FROM ps_ACAD_prog a2
, ps_ACAD_plan b2
, ps_ACAD_subplan c2
WHERE b2.emplid(+) = a2.emplid
AND b2.acad_career(+) = a2.acad_career
AND b2.stdnt_car_nbr(+) = a2.stdnt_car_nbr
AND b2.effdt(+) = a2.effdt
AND b2.effseq(+) = a2.effseq
AND c2.emplid(+) = b2.emplid
AND c2.acad_career(+) = b2.acad_career
AND c2.stdnt_car_nbr(+) = b2.stdnt_car_nbr
AND c2.acad_plan(+) = b2.acad_plan
AND c2.effdt(+) = b2.effdt
AND c2.effseq(+) = b2.effseq
AND c2.acad_plan(+) = b2.acad_plan
) X, ps_acad_plan_tbl XPLAN , ps_acad_prog_tbl XPROG, ps_acad_subpln_tbl XSUB, PS_ADM_APPL_DATA XPLUS
WHERE x.institution = xplan.institution(+)
AND x.acad_plan = xplan.acad_plan(+)
AND ( xplan.effdt = (
SELECT MAX(xplan_ed.effdt)
FROM ps_acad_plan_tbl xplan_ed
WHERE xplan_ed.institution = xplan.institution
AND xplan_ed.acad_plan = xplan.acad_plan
AND xplan_ed.eff_status = 'A')
OR xplan.effdt IS NULL)
AND x.institution = xprog.institution(+)
AND x.acad_prog = xprog.acad_prog(+)
AND ( xprog.effdt = (
SELECT MAX(xprog_ed.effdt)
FROM ps_acad_prog_tbl xprog_ed
WHERE xprog_ed.institution = xprog.institution
AND xprog_ed.acad_prog = xprog.acad_prog
AND xprog_ed.eff_status = 'A')
OR xprog.effdt IS NULL)
AND x.institution = xsub.institution(+)
AND x.acad_plan = xsub.acad_plan(+)
AND x.acad_sub_plan = xsub.acad_sub_plan(+)
AND ( xsub.effdt = (
SELECT MAX(xsub_ed.effdt)
FROM ps_acad_subpln_tbl xsub_ed
WHERE xsub_ed.institution = xsub.institution
AND xsub_ed.acad_plan = xsub.acad_plan
AND xsub_ed.acad_sub_plan = xsub.acad_sub_plan
AND xsub_ed.eff_status = 'A')
OR xsub.effdt IS NULL)
AND xplus.emplid(+) = x.emplid
and xplus.acad_career(+) = x.acad_career
and xplus.adm_appl_nbr(+) = x.adm_appl_nbr
and xplus.institution(+) = x.institution
AND X.INSTITUTION = &B.INSTITUTION
AND X.ACAD_CAREER = &B.ACAD_CAREER
AND X.EMPLID = &A.EMPLID
AND ( X.EFFDT <= &Recalculated_input_date or ( x.datasource = 'AD' and X.EFFDT <= $input_date))
order by x.effdt , x.effseq
end-select
! <<Administrative Withdrwal incomplete>> (Last action)
! <<Applicant Withdrawal deferral>>
if $Found_Defer = 'Y' and $Found_defer_term >= $last_term
!and &X.DATASOURCE = 'AD'
if $Found_defer_term = $last_term
and $Exists_Term_After_Defer = 'Y' ! Defer out
Let $UM_WAPPDEF_DATE = $Found_Defer_date
Let $UM_WAPPDEF_TERM = $last_term
Let $UM_WAPPDEF_PLAN = $Top_plan_last_term
Let $UM_WAPPDEF_SUBPLAN = $Top_subplan_last_term
Let $UM_WAPPDEF_PRGSTAT = $Top_prgstat_last_term
Let $UM_WAPPDEF_PROGACT = $Top_progact_last_term
Let $UM_WAPPDEF_PRGREAS = $Top_prgreas_last_term
end-if
if $Found_defer_term > $Last_term ! Defer in
and $last_term <> '0000'
Let $UM_WAPPDEF_DATE = $Found_Defer_date
Let $UM_WAPPDEF_TERM = $Last_term
Let $UM_WAPPDEF_PLAN = $Top_plan_last_term
Let $UM_WAPPDEF_SUBPLAN = $Top_subplan_last_term
Let $UM_WAPPDEF_PRGSTAT = $Top_prgstat_last_term
Let $UM_WAPPDEF_PROGACT = $Top_progact_last_term
Let $UM_WAPPDEF_PRGREAS = $Top_prgreas_last_term
end-if
if $UM_WAPPDEF_TERM = '0000' ! Default (defer out)
if $last_term > '0000'
Let $UM_WAPPDEF_DATE = $Found_Defer_date
Let $UM_WAPPDEF_TERM = $Last_term
Let $UM_WAPPDEF_PLAN = $Top_plan_last_term
Let $UM_WAPPDEF_SUBPLAN = $Top_subplan_last_term
Let $UM_WAPPDEF_PRGSTAT = $Top_prgstat_last_term
Let $UM_WAPPDEF_PROGACT = $Top_progact_last_term
Let $UM_WAPPDEF_PRGREAS = $Top_prgreas_last_term
else
Let $UM_WAPPDEF_DATE = $Found_Defer_date
Let $UM_WAPPDEF_TERM = $Found_defer_term
Let $UM_WAPPDEF_PLAN = $Found_defer_plan
Let $UM_WAPPDEF_SUBPLAN = $Found_defer_subplan
Let $UM_WAPPDEF_PRGSTAT = $Found_defer_prgstat
Let $UM_WAPPDEF_PROGACT = $Found_defer_progact
Let $UM_WAPPDEF_PRGREAS = $Found_defer_prgreas
end-if
end-if
end-if
! If you have a record you must have applied somewhere
if $APPL_PROG1 = 'N' and $APPL_PROG2 = 'N' and $APPL_PROG3 = 'N' and $APPL_PROG4 = 'N'
Let $APPL_PROG1 = 'Y'
end-if
if $UM_APP_DATE = '01-JAN-1901' and &XPLUS.ADM_APPL_DT > '01-JAN-1901'
Let $UM_APP_DATE = &XPLUS.ADM_APPL_DT
show 'Missing entry for ' &X.EMPLID ' at ' &XPLUS.ADM_APPL_DT
end-if
end-procedure
!***********************************************************************
! Get xlatlongname from good ol PSXLATITEM
!***********************************************************************
begin-procedure get_xlat($in_name,$in_value,:$xlat_val)
Let $xlat_val = ' '
begin-select
xlt.xlatlongname,
Let $xlat_val = &xlt.xlatlongname
from psxlatitem xlt
where xlt.fieldname = $in_name
and xlt.fieldvalue = $in_value
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 <= $_input_date)
end-select
end-procedure
!***********************************************************************
! Move data from um_appl_ext_bld to um_appl_extract
!***********************************************************************
begin-procedure Blasto_Data
Begin-select
count(*) &CTR_UM_BLD
FROM SYSADM.PS_UM_APPL_EXT_BLD buildies
end-select
if &CTR_UM_BLD > 0
if $input_Flag2 = 'Y'
BEGIN-SQL
TRUNCATE TABLE PS_UM_appl_EXTRACT
END-SQL
else
begin-sql
DELETE FROM ps_um_appl_extract dxx where dxx.admit_term >= [$Input_Trm1_M] and dxx.admit_term <= [$Input_Term2_M]
end-sql
end-if
BEGIN-SQL
COMMIT
END-SQL
show 'Extract trimmed.'
BEGIN-SQL
INSERT INTO PS_UM_appl_EXTRACT ( SELECT * FROM SYSADM.PS_UM_appl_EXT_BLD)
END-SQL
BEGIN-SQL
COMMIT
END-SQL
show 'Rows inserted from UM_APPL_EXT_BLD.'
end-if
end-procedure
!***********************************************************************
! SQL-Recover Procedure
!***********************************************************************
Begin-Procedure SQL-Recover
show '$Calling_Procedure => ' $Calling_Procedure
display $SQL-Error
Do Extract_Control('3', 'PS_UM_APPL_EXTRACT','UMAPPEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
End-Procedure SQL-Recover
!***********************************************************************
!SQC Include Files *
!***********************************************************************
#include 'stdapi.sqc' !Update Process API
#include 'datetime.sqc' !Date and Time Functions
#include 'datemath.sqc' !Date Manipulation Routines
#include 'curdttim.sqc' !Get-Current-DateTime procedure
#include 'tranctrl.sqc' !Transaction Control
#include 'umextctl.sqc' !Extract Control
b. PS_UM_STUD_EXTRACT
(PS_UM_STUD_EXT_ARC is better for daily tracking data. The archive table is based on extract and UMSTUEXT uses these 35 tables:
| PS_ACAD_GROUP_TBL |
| PS_ACAD_PLAN |
| PS_ACAD_PLAN_TBL |
| PS_ACAD_PROG |
| PS_ACAD_PROG_TBL |
| PS_ACAD_SUBPLAN |
| PS_ACAD_SUBPLN_TBL |
| PS_ADDRESSES |
| PS_ADM_APPL_DATA |
| PS_CITIZENSHIP |
| PS_CLASS_ATTRIBUTE |
| PS_CLASS_TBL |
| PS_COUNTRY_TBL |
| PS_DEGREE_TBL |
| PS_DIVERS_ETHNIC |
| PS_EMAIL_ADDRESSES |
| PS_ETHNIC_GRP_TBL |
| PS_EXT_ORG_PRI_VW |
| PS_EXT_ORG_TBL_ADM |
| PS_INSTITUTION_TBL |
| PS_ITEM_SF |
| PS_NAMES |
| PS_PERS_NID |
| PS_PERSONAL_DATA |
| PS_PROG_RSN_TBL |
| PS_RESIDENCY_OFF |
| PS_RESP_RSN_TBL |
| PS_STDNT_ADVR_HIST |
| PS_STDNT_CAR_TERM |
| PS_STDNT_ENRL |
| PS_STDNT_RESPONSE |
| PS_STDNT_TEST_COMP |
| PS_TERM_TBL |
| PS_TERM_VAL_TBL |
| PS_UM_ENRL_PIN |
c. PS_ACAD_PLAN_TBL
d. PS_TERM_VAL_TBL
e. PS_INSTITUTION_TBL
f. PS_ADMIT_TYPE_TBL
g. PS_LS_SCHL_TYP_TBL
h.Ā PS_RESIDENCY_TBL
i. PS_ACAD_CAR_TBL
j. PSXLATITEM
Fields needed:
a. From PS_UM_APPL_EXT_VW:
- institutionĀ ā join table to get e.DESCRSHORT
- emplid
- admit_term ā join table to get d.DESCR
- acad_prog
- acad_prog_descr
- acad_plan ā join table to get c.DESCR
- acad_career āĀ Ā join table to get i.DESCR
- sexĀ āĀ join table to get j.XLATSHORTNAME
- um_eth_grp_descr
- admission_resĀ āĀ Ā join table to get h.DESCRSHORT
- admit_typeĀ ā join table to get f.DESCRSHORT
- birthdateĀ ā need to calculate age as of applied term and also categorize age
- city
- county
- state
- country
- postal
- um_app_dateā need to calculate Applied
- um_admit_dateā need to calculate Admitted
- um_netconfirm_dateā need to calculate NetConfirmed
- um_deny_dateā need to calculate Denied
- last_sch_attend
- um_last_sch_descr
- ext_org_id
- um_ext_orgid_descr
- um_last_sch_typeĀ āĀ join table to get g.DESCR
b. From PS_UM_STUD_EXTRACT:
- unt_taken_prgrssĀ
c. From PS_ACAD_PLAN_TBL:
- DESCR
d. FromĀ PS_TERM_VAL_TBL:
- DESCR
e. From PS_INSTITUTION_TBL:
- DESCRSHORT
f. FromĀ PS_ADMIT_TYPE_TBL:
- DESCRSHORTĀ
g. From PS_LS_SCHL_TYP_TBL
- DESCRĀ
h.Ā From PS_RESIDENCY_TBL
- DESCRSHORT
i. From PS_ACAD_CAR_TBL
- DESCRĀ
j. From PSXLATITEM
- XLATSHORTNAME (where FIELDNAME= 'SEX')
Calculations:
Applied = (case when a.um_app_date > to_date('01/01/1901', 'MM/DD/YYYY') and a.um_app_date <=Ā date_users_pickedĀ then 1 else 0Ā end)Ā
Admitted = (case when a.um_admit_dateĀ Ā > to_date('01/01/1901', 'MM/DD/YYYY') and a.um_admit_dateĀ Ā <=Ā date_users_pickedĀ then 1 else 0Ā end)Ā
NetConfirmed = (case when um_netconfirm_dateĀ > to_date('01/01/1901', 'MM/DD/YYYY') and um_netconfirm_dateĀ <=Ā date_users_pickedĀ then 1 else 0Ā end)Ā
DeniedĀ = (case when a.um_deny_dateĀ Ā > to_date('01/01/1901', 'MM/DD/YYYY') and a.um_deny_dateĀ Ā <=Ā date_users_pickedĀ then 1 else 0Ā end)Ā
Enrolled = (case when a.um_admit_date > to_date('01/01/1901', 'MM/DD/YYYY') and a.um_admit_date <=Ā date_users_pickedĀ and b.unt_taken_prgrss >0 then 1 else 0)Ā Ā
Problems:
- Not sure how the calculations for Applied, Admitted, NetConfirmed, Denied, and Enrolled work the best
- I had issues with duplicated records when I joined tables because I didn't incorporate max(effdt) logic in sub queries,Ā
- Data issues- not every institutions use in the same way
SQR code for UMSTUEXT Expand source
!************************************************************************
!umstuext.SQR *
!************************************************************************
! DESCRIPTION: Simple Student Table Extract *
! OWNER: *
! FREQUENCY: Manual *
!************************************************************************
! SPECIFICATIONS BY: *
! ISSUE: *
! DATE: *
!************************************************************************
! CREATED BY: Peter Andersen *
! CREATION DT: Jan 6, 2006 *
!************************************************************************
! MODIFICATION LOG *
!****************************************************************************
! Mod # Programmer Start Date End Date Description *
! ----- ---------- ---------- ---------- ------------------------- *
! REPORT-57 PSA 03/15/13 04/15/13 Get_Advisor change
! REPORT-193 PSA 09/22/14 Null Acad Level Description *
! REPORT-195 PSA 10/9/14 Revert part of Advisor change*
! REPORT-313 PSA 8/17 Address logic change *
!****************************************************************************
#include 'setenv.sqc' ! Set environment
!***********************************************************************
! Begin Setup
!***********************************************************************
Begin-Setup
#include 'setup01a.sqc'
#define DLim ';'
declare-variable
date $input_Date
date $FiveYearsAgo
date $TwoYearsAgo
date $Residency_Dt
text $Row_Check
date $Declare_dt
date $Response_Dt
date $Term_begin_dt
date $Term_end_dt
end-declare
Load-Lookup Name=State_Descr
table =PS_STATE_TBL
key =STATE
return_Value=DESCR
where=COUNTRY='USA'
Load-Lookup Name=Country_Descr
table= PS_COUNTRY_TBL
key = COUNTRY
return_Value = DESCR
Load-Lookup Name=Institution_Descr
table = 'PS_INSTITUTION_TBL INS'
key = INSTITUTION
Return_Value = DESCR
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'''
Load-Lookup Name=Get_Xlat_Academic_Level
table='psxlatitem xlt'
key = fieldvalue
return_value = xlatlongname
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)'
Load-Lookup Name=Get_Xlat_Ethnic_Group
table='psxlatitem xleg'
key = fieldvalue
return_value = xlatlongname
where='xleg.fieldname= ''ETHNIC_GROUP'' and xleg.effdt= ( select max(xleg_ed.effdt) from psxlatitem xleg_ed where xleg_ed.fieldvalue = xleg.fieldvalue and xleg_ed.fieldname = xleg.fieldname and xleg.effdt <= SYSDATE)'
End-Setup
!***********************************************************************
! Proc Options(Main);
!***********************************************************************
Begin-Program
Let $Reporttitle = 'Make Student Reporting Extract'
let $Reportid = 'umstuext'
let $first_time = 'Y'
! BEGIN-SQL
! ALTER SESSION SET cursor_sharing='SIMILAR'
! end-sql
do Stdapi-Init
do Init-DateTime
do Get-Current-DateTime
show 'Get Parameters:'
if $prcs_process_instance = '' or $prcs_oprid = 'BATCH'
let $prcs_oprid = 'PANDERSEN'
let $prcs_run_cntl_id = 'BATCH'
do Select-Parameters
else
do Select-Parameters
end-if
Show $Input_Trm1
if $input_Flag1 = 'Y'
let $input_date = datenow()
end-if
show $input_date
Let $FiveYearsAgo = dateadd($input_date,'YEAR',-5)
Let $TwoYearsAgo = dateadd($input_date,'YEAR',-5)
!BEGIN-SQL
!ALTER SESSION SET "_optimizer_cost_based_transformation" = ON
!end-sql
BEGIN-SELECT
MAX(ENDA.STRM) &ENDA.STRM
Let $This_Term = &ENDA.STRM
FROM PS_TERM_TBL ENDA
WHERE
ENDA.TERM_END_DT >= $input_date
AND ENDA.TERM_BEGIN_DT <= $input_date
END-SELECT
begin-SELECT
count(*) &GobSTOP
if &GobStop < 450000
show 'Not enough records in PS_PERSONAL_DATA'
stop quiet
else
show &Gobstop ' records in PS_PERSONAL_DATA'
end-if
from PS_PERSONAL_DATA
end-select
Let $Control_now = edit(datenow(),'yyyy/mm/dd:hh:mi:ssam')
show $Control_now
Do Extract_Control('7', 'PS_UM_STUD_EXTRACT','UMSTUEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
Do Get_Ranges
! if $input_Flag2 = 'Y'
! BEGIN-SQL on-error=SQL-Recover
! TRUNCATE TABLE PS_UM_STUD_EXTRACT
! END-SQL
! else
! begin-sql on-error=SQL-Recover
! DELETE FROM ps_um_stud_extract dxx where dxx.strm >= [$Input_Trm1_M] and dxx.strm <= [$Input_Term2_M]
! end-sql
! end-if
! BEGIN-SQL on-error=SQL-Recover
! COMMIT
! END-SQL
! show 'Table trimmed.'
BEGIN-SQL
TRUNCATE TABLE SYSADM.PS_UM_STUD_EXT_BLD
END-SQL
Show $Input_Trm1
Show $Input_Term2
do List_Students
do blasto_data
Do Extract_Control('9', 'PS_UM_STUD_EXTRACT','UMSTUEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
BEGIN-SQL on-error=SQL-Recover
COMMIT
END-SQL
do Stdapi-Term
End-Program
!***********************************************************************
! Page Heading
!***********************************************************************
Begin-Heading 8
! #include 'umstdhdg.sqc'
print $INSTITUTION_NAME (+1,1) center bold box
Print 'In Date:' (+1,1)
Print $input_Date (,+1) DATE bold
Print 'Beginning Term: ' (,41)
Print $Input_Trm1 (,+1) bold
Print 'Ending Term ' (,+2)
Print $Input_term2 (,+1) bold
End-Heading
!***********************************************************************
! Procedure List_Students *
! *
!***********************************************************************
Begin-Procedure List_Students
Let #STU_COUNT = 0
Let #Student_Car_term_no = 0
BEGIN-SELECT !loops=1000
STA.EMPLID,
NVL(STA.LAST_NAME,' ') &STA.LAST_NAME,
NVL(STA.FIRST_NAME,' ') &STA.FIRST_NAME,
NVL(STA.MIDDLE_NAME,' ') &STA.MIDDLE_NAME,
NVL(STA.NAME_PREFIX,' ') &STA.NAME_PREFIX,
NVL(STA.NAME_SUFFIX,' ') &STA.NAME_SUFFIX,
NVL(STA.NAME,' ') &STA.NAME,
NVL(STA.ADDRESS1,' ') &STA.ADDRESS1,
NVL(STA.ADDRESS2,' ') &STA.ADDRESS2,
NVL(STA.ADDRESS3,' ') &STA.ADDRESS3,
NVL(STA.ADDRESS4,' ') &STA.ADDRESS4,
NVL(STA.CITY,' ') &STA.CITY,
NVL(STA.STATE,' ') &STA.STATE,
NVL(STA.POSTAL,' ') &STA.POSTAL,
NVL(STA.COUNTY,' ') &STA.COUNTY,
NVL(STA.COUNTRY,' ') &STA.COUNTRY,
NVL(STA.SEX,' ') &STA.SEX,
STA.BIRTHDATE,
NVL(STA.BIRTHPLACE,' ') &STA.BIRTHPLACE,
NVL(STA.BIRTHSTATE,' ') &STA.BIRTHSTATE,
NVL(STA.BIRTHCOUNTRY,' ') &STA.BIRTHCOUNTRY,
NVL(STA.MAR_STATUS,' ') &STA.MAR_STATUS,
NVL(STA.MAR_STATUS_DT,'01-JAN-1901') &STA.MAR_STATUS_DT,
NVL(STA.DT_OF_DEATH,'01-JAN-1901') &STA.DT_OF_DEATH,
NVL(STA.PHONE,' ') &STA.PHONE,
STC.EMPLID,
STC.ACAD_CAREER,
STC.STDNT_CAR_NBR,
STC.ADM_APPL_NBR,
STC.INSTITUTION,
STC.CAMPUS,
STC.APPL_PROG_NBR,
STC.EFFDT,
STC.EFFSEQ,
STC.ACAD_PROG,
STC.PROG_STATUS,
STC.PROG_ACTION,
STC.ACTION_DT,
STC.PROG_REASON,
STC.ADMIT_TERM,
STC.EXP_GRAD_TERM,
STC.REQ_TERM,
STC.ACAD_LOAD_APPR,
STC.COMPLETION_TERM,
STC.ACAD_PROG_DUAL,
STC.JOINT_PROG_APPR,
NVL(STC.DEGR_CHKOUT_STAT,' ') &STC.DEGR_CHKOUT_STAT,
STUCAR.REG_CARD_DATE,
stucar.fully_enrl_dt,
stucar.acad_level_bot,
Stucar.acad_level_eot,
stucar.acad_level_proj,
stucar.unt_term_tot,
stucar.grade_points,
stucar.cur_gpa,
stucar.cum_gpa,
stucar.academic_load,
stucar.fa_load,
rownum &Student_Car_term_no,
stucar.unt_taken_prgrss,
stucar.unt_passd_prgrss,
stucar.unt_trnsfr,
stucar.tot_passd_prgrss,
stucar.tot_grade_points,
STUCAR.STRM,
STUCAR.WITHDRAW_CODE,
STUCAR.WITHDRAW_REASON,
STUCAR.WITHDRAW_DATE,
STUCAR.LAST_DATE_ATTENDED,
STUCAR.ACAD_PROG_PRIMARY,
STUCAR.NSLDS_LOAN_YEAR,
STUCAR.OVRD_ACAD_LVL_ALL,
STUCAR.OVRD_ACAD_LVL_PROJ,
STUCAR.ELIG_TO_ENROLL,
STUCAR.OVRD_MAX_UNITS,
STUCAR.MAX_TOTAL_UNIT,
STUCAR.MAX_NOGPA_UNIT,
STUCAR.MAX_AUDIT_UNIT,
STUCAR.MAX_WAIT_UNIT,
STUCAR.MIN_TOTAL_UNIT,
STUCAR.OVRD_BILL_UNITS,
STUCAR.PROJ_BILL_UNT,
STUCAR.UNT_TAKEN_GPA, !
STUCAR.UNT_PASSD_GPA,
STUCAR.UNT_TAKEN_NOGPA,
STUCAR.UNT_PASSD_NOGPA,
STUCAR.UNT_INPROG_GPA,
STUCAR.UNT_INPROG_NOGPA,
STUCAR.UNT_AUDIT,
STUCAR.TRF_TAKEN_GPA,
STUCAR.TRF_TAKEN_NOGPA,
STUCAR.TRF_PASSED_GPA,
STUCAR.TRF_PASSED_NOGPA,
STUCAR.TRF_GRADE_POINTS,
STUCAR.UNT_TEST_CREDIT,
STUCAR.UNT_OTHER,
STUCAR.UNT_TAKEN_FA,
STUCAR.UNT_PASSD_FA,
STUCAR.UNT_TAKEN_FA_GPA,
STUCAR.GRADE_POINTS_FA,
STUCAR.RESET_CUM_STATS,
STUCAR.TOT_TAKEN_PRGRSS,
STUCAR.TOT_TAKEN_GPA,
STUCAR.TOT_PASSD_GPA,
STUCAR.TOT_TAKEN_NOGPA,
STUCAR.TOT_PASSD_NOGPA,
STUCAR.TOT_INPROG_GPA,
STUCAR.TOT_INPROG_NOGPA,
STUCAR.TOT_AUDIT,
STUCAR.TOT_TRNSFR,
STUCAR.TOT_TEST_CREDIT,
STUCAR.TOT_OTHER,
STUCAR.TOT_CUMULATIVE,
STUCAR.TOT_TAKEN_FA,
STUCAR.TOT_PASSD_FA,
STUCAR.TOT_TAKEN_FA_GPA,
STUCAR.TOT_GRD_POINTS_FA,
STUCAR.FORM_OF_STUDY,
STUCAR.TERM_TYPE,
STUCAR.CLASS_RANK_NBR,
STUCAR.CLASS_RANK_TOT,
STUCAR.SEL_GROUP,
STUCAR.TUIT_CALC_REQ,
STUCAR.TUIT_CALC_DTTM,
STUCAR.FA_STATS_CALC_REQ,
STUCAR.FA_STATS_CALC_DTTM,
STUCAR.FA_ELIGIBILITY,
STUCAR.BILLING_CAREER,
STUCAR.UNIT_MULTIPLIER,
STUCAR.ACAD_YEAR,
STUCAR.ACAD_GROUP_ADVIS,
STUCAR.CUR_RESIDENT_TERMS,
STUCAR.TRF_RESIDENT_TERMS,
STUCAR.CUM_RESIDENT_TERMS,
STUCAR.REFUND_PCT,
STUCAR.REFUND_SCHEME,
STUCAR.PRO_RATA_ELIGIBLE,
STUCAR.ENRL_ON_TRANS_DT,
STUCAR.STATS_ON_TRANS_DT,
STUCAR.FULLY_GRADED_DT,
STUCAR.STUDY_AGREEMENT,
STUCAR.START_DATE,
STUCAR.END_DATE,
STUCAR.MAX_CRSE_COUNT,
STUCAR.REGISTERED,
STUCAR.OVRD_TUIT_GROUP,
STUCAR.OVRD_WDRW_SCHED,
STUCAR.TUITION_RES_TERMS,
STUCAR.OVRD_INIT_ADD_FEE,
STUCAR.OVRD_INIT_ENR_FEE,
STUCAR.TC_UNITS_ADJUST,
STUCAR.LOCK_IN_AMT,
STUCAR.LOCK_IN_DT,
STUCAR.ACAD_CAREER_FIRST,
STUCAR.ACADEMIC_LOAD_DT,
STUCAR.UNTPRG_CHG_NSLC_DT,
STUCAR.SSR_ACTIVATION_DT,
STUCAR.EMPLID,
STUCAR.ACAD_CAREER,
STUCAR.INSTITUTION,
STUCAR.STDNT_CAR_NBR,
NVL(APX.ADM_APPL_CTR,' ') &APX.ADM_APPL_CTR,
NVL(APX.ADMIT_TYPE,' ') &APX.ADMIT_TYPE,
NVL(APX.ADM_APPL_DT,
TO_DATE('01-JAN-1901 ','DD-MON-YYYY')) &APX.ADM_APPL_DT,
NVL(PIN.PIN_NUM,0) &PIN.PIN_NUM,
NVL(PIN.VALIDATED,' ') &PIN.VALIDATED,
NVL(PIN.VALIDATED_DT,TO_DATE('01-JAN-1901 ','DD-MON-YYYY')) &PIN.VALIDATED_DT,
NVL(APX.LAST_SCH_ATTEND,' ') &APX.LAST_SCH_ATTEND,
NVL(APX.GRADUATION_DT,TO_DATE('01-JAN-1901 ','DD-MON-YYYY')) &APX.GRADUATION_DT
let $Row_Check = 'N'
! Flag = 'Y' for highest acad_career among career with unt_taken_prgrss > 0
! if only one then Flag = 'Y'
!do get_calc_units
DO career_rank(&STUCAR.STRM, &STUCAR.INSTITUTION, &STUCAR.EMPLID, &STUCAR.ACAD_CAREER, $Row_Career_Rank)
do MULTI_REG
if #units_this_career > 0 and $Row_career_rank = 'Y' and &STUCAR.ACAD_CAREER <> 'NCRD'
let $Row_Check = 'Y'
end-if
Let #Student_Car_term_no = &Student_Car_term_no
Lookup Get_Xlat_Academic_Level &stucar.acad_level_bot $Acad_Level_bot_descr ! call get_xlat
! REPORT 193 PSA 09/22/14
If $Acad_Level_bot_descr <= ' '
Let $Acad_Level_bot_descr = ' '
show 'Bad Acad_Level_bot ' &stucar.emplid
end-if
Let $Adm_appl_ctr = &APX.ADM_APPL_CTR
Let $Admit_type = &APX.ADMIT_TYPE
Let $ADM_APPL_DT = &APX.ADM_APPL_DT
Let $Degr_chkout_stat = &STC.DEGR_CHKOUT_STAT ! get from PS_ACAD_PROG instead
DO GET_MISSING_ADDRESS
if $address1 = ' ' and $address2 = ' ' and $address3 = ' ' and $address4 = ' '
Let $address1 = &STA.ADDRESS1
Let $address2 = &STA.ADDRESS2
Let $address3 = &STA.ADDRESS3
Let $address4 = &STA.ADDRESS4
Let $city = &STA.CITY
Let $state = &STA.STATE
Let $postal = &STA.POSTAL
Let $county = &STA.COUNTY
Let $COUNTRY = &STA.COUNTRY
end-if
if &APX.ADMIT_TYPE <= ' ' and &STC.ACAD_PROG <> 'NDUG' and &STC.ACAD_PROG <> 'DIST' and &STC.ACAD_PROG <> 'DLL'
do Term_Stats(&STC.INSTITUTION, &STC.ACAD_CAREER, &STUCAR.STRM, $Term_begin_dt, $Term_end_Dt)
do Missing_Admit_type
end-if
DO GET_PREVIOUS_NAME
DO GET_EMAIL
! DO GET_PHONE
Let $Phone = &STA.PHONE
DO GET_NID
DO GET_ETHNIC
DO GET_COUNTRY
Lookup State_Descr &STA.STATE $State_Descr
if $State_Descr <= ' '
Let $State_Descr = ' '
end-if
Lookup Country_Descr &STA.COUNTRY $Country_Descr
if $Country_Descr <= ' '
Let $Country_Descr = ' '
end-if
Lookup Institution_Descr &STC.INSTITUTION $Institution_Descr
if $Institution_Descr <= ' '
Let $Institution_Descr = ' '
end-if
DO GET_RESIDENCY
DO GET_A_DESCR
DO GET_RESPONSE
DO Get_Last_Sch_info
DO GET_STU_PLAN
Do GET_TUITION_FEES
do GET_DISTANCE_WEB
do GET_ADVISOR
do maxxy
add 1 to #STU_COUNT
do interval_commit
! if #um_unt_taken_prgrss_rowc <> &stucar.unt_taken_prgrss and &stucar.strm = '0910' and $Row_Check = 'Y'
! show &stucar.institution ' - ' &stucar.emplid ' ' &stucar.unt_taken_prgrss ' ' #um_unt_taken_prgrss_rowc
! end-if
DO INSERT_ROWS
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 [$input_Trm1_M] AND [$input_Term2_M]
AND PIN.INSTITUTION(+) = STUCAR.INSTITUTION
AND PIN.EMPLID(+) = STUCAR.EMPLID
AND PIN.STRM(+) = STUCAR.STRM
! AND (STUCAR.UNT_TERM_TOT > 0 or STUCAR.UNT_TAKEN_PRGRSS > 0 or STUCAR.UNT_AUDIT > 0 or STUCAR.ACADEMIC_LOAD <> 'N')
END-SELECT
show 'Students selected = ' #STU_COUNT
End-Procedure ! List_Applicants
!***********************************************************************
! Select Parameters
!***********************************************************************
Begin-Procedure Select-Parameters
Let $input_date = datenow()
Let $Input_Trm1 = '0910'
Let $Input_Term2 = '9999'
Let $Input_Flag3 = 'Y'
begin-select
R.AS_OF_DATE,
R.UM_BEGIN_TERM,
R.UM_END_TERM,
R.FLAG1,
R.FLAG2,
R.FLAG3
Let $input_date = &R.AS_OF_DATE
if &R.FLAG1 = 'Y'
Let $input_date = datenow()
end-if
Let $Input_Trm1 = &R.UM_BEGIN_TERM
Let $Input_term = &R.UM_BEGIN_TERM
Let $Input_Term2 = &R.UM_END_TERM
Let $input_Flag2 = &R.FLAG2
Let $input_Flag3 = &R.FLAG3
FROM PS_UM_RUN_UMSTUEXT R
WHERE R.OPRID = $prcs_oprid
AND R.RUN_CNTL_ID = $prcs_run_cntl_id
end-select
show $input_date
if $input_Flag3 <> 'Y'
show 'Using from SP Term1: ' $Input_Trm1 ' Term2: ' $Input_Term2
else
Let $compare = '0'||to_char( to_number(substr($input_date,10,2)) - 1 )
Let $compare = substr($compare,length($compare)-1,2)
show 'AUTO: ' $input_date '--> ' $compare
begin-select
VIK.STRM,
VIK.DESCR,
VIK.DESCRSHORT
show &VIK.STRM '--' &VIK.DESCR '--' &VIK.DESCRSHORT
Let $input_term = &VIK.STRM
LET $Input_trm1 = &VIK.STRM
Let $input_term2 = '9999'
from ps_term_val_tbl VIK
where substr(VIK.STRM,1,2) = $compare
and substr(VIK.STRM,3,1) = decode(substr(to_char($input_date),4,3),'JAN','2','FEB','2','MAR','2','APR','2','MAY','2',
'JUN','3','JUL','3','AUG','3','SEP','1','OCT','1','NOV','1','DEC','1','1')
end-select
show 'Automatic Term1: ' $Input_Term ' to ' $Input_Term2
end-if
Let $Input_Trm1_M = ''''||$Input_Trm1||''''
Let $Input_term_M = ''''||$Input_term||''''
Let $Input_Term2_M = ''''||$Input_Term2||''''
Let $input_Flag2_M = ''''||$input_Flag2||''''
Let $input_date_M = ''''||$input_date||''''
End-Procedure
!***********************************************************************
! Get Input for Terms
!***********************************************************************
Begin-Procedure Get-Input
Let #input_bad = 1
Input $input_date type=date 'Enter Date'
Input $Input_Trm1 type=text status=#input_status 'Please Enter the First Term'
Input $input_term2 type=text status=#input_status 'Please Enter the Second Term'
End-Procedure
!***********************************************************************
! Get Previous Name
!***********************************************************************
Begin-Procedure Get_Previous_Name
Let $Previous_Name = ' '
Begin-select
SB1.Name
if &SB1.Name > ' '
Let $Previous_Name = &SB1.Name
end-if
FROM PS_NAMES SB1
WHERE SB1.NAME_TYPE = 'FR1'
and SB1.EMPLID = &STA.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 <= [$input_date_M])
end-select
End-Procedure
!***********************************************************************
! Get Preferred email address
!***********************************************************************
Begin-Procedure Get_email
Let $Email_Addr = ' '
Let $Hom_Email_Addr = ' '
Begin-select
SB2.EMAIL_ADDR,
SB2.PREF_EMAIL_FLAG,
SB2.E_ADDR_TYPE
if &SB2.EMAIL_ADDR > ' ' and &SB2.PREF_EMAIL_FLAG = 'Y'
Let $Email_Addr = &SB2.EMAIL_ADDR
end-if
if &SB2.EMAIL_ADDR > ' ' and &SB2.E_ADDR_TYPE = 'HOME'
Let $Hom_Email_Addr = &SB2.EMAIL_ADDR
end-if
FROM PS_EMAIL_ADDRESSES SB2
WHERE SB2.EMPLID = &STA.EMPLID
order by sb2.E_ADDR_TYPE,decode(SB2.PREF_EMAIL_FLAG,'Y',1,9)
end-select
End-Procedure
!***********************************************************************
! Get NID for USA and Canada
!***********************************************************************
Begin-Procedure Get_nid
Let $Nid_Usa = ' '
Let $Nid_Can = ' '
Begin-select
SB4.NATIONAL_ID,
SB4.COUNTRY
if &SB4.COUNTRY = 'USA' and &SB4.NATIONAL_ID > ' '
Let $Nid_Usa = &SB4.NATIONAL_ID
end-if
if &SB4.COUNTRY = 'CAN' and &SB4.NATIONAL_ID > ' '
Let $Nid_Can = &SB4.NATIONAL_ID
end-if
FROM PS_PERS_NID SB4
WHERE SB4.EMPLID = &STA.EMPLID
end-select
End-Procedure
!***********************************************************************
! Get Ethnic Data
!***********************************************************************
Begin-Procedure Get_ethnic
Let $Ethnic_Grp_cd = ' '
Let $Ethnic_Category = ' '
Let $Ethnic_Descr = ' '
Let $Hisp_Latino = 'N'
Let $Multi_Race = 'N'
Let $Old_Group = ' '
Let $Ethnic_Group = ' '
Let $UM_ETH_GRP_DESCR = ' '
Let #Race_multi = 0
Begin-select
SB6.ETHNIC_GRP_CD,
SB6.APS_EC_NDS_AUS,
SB7.ETHNIC_CATEGORY,
SB7.DESCR50,
SB7.SETID,
SB7.ETHNIC_GRP_CD,
SB7.ETHNIC_GROUP
if &SB6.ETHNIC_GRP_CD > ' ' and $Ethnic_grp_cd = ' '
Let $Ethnic_Grp_cd = &SB6.ETHNIC_GRP_CD
Let $Ethnic_Category = &SB7.ETHNIC_CATEGORY
Let $Ethnic_Descr = &SB7.DESCR50
Let $Ethnic_Group = &SB7.ETHNIC_GROUP
end-if
if &SB7.ETHNIC_GROUP = '3'
Let $Hisp_Latino = 'Y'
else
if &SB7.ETHNIC_GROUP <> $Old_Group
if &SB7.ETHNIC_GROUP <> '6'
Add 1 to #Race_multi ! Not a true count
end-if
Let $Old_Group = &SB7.ETHNIC_GROUP
end-if
end-if
FROM PS_DIVERS_ETHNIC SB6, PS_ETHNIC_GRP_TBL SB7
WHERE SB6.ETHNIC_GRP_CD = SB7.ETHNIC_GRP_CD
! AND SB6.PRIMARY_INDICATOR = 'Y'
AND SB6.ETHNIC_GRP_CD <> 'NOTHIS'
AND SB6.EMPLID = &STA.EMPLID
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 <= [$input_date_M])
AND SB7.SETID = 'USA'
order by decode(sb7.ethnic_group,'3','1','2'), decode(sb6.primary_indicator,'Y','1','2')
end-select
if #Race_multi > 1
Let $Multi_Race = 'Y'
end-if
!DO GET_XLAT('ETHNIC_GROUP',$Ethnic_Group,$UM_ETH_GRP_DESCR)
Lookup Get_Xlat_Ethnic_Group $Ethnic_Group $UM_ETH_GRP_DESCR
if $UM_ETH_GRP_DESCR <= ' '
Let $UM_ETH_GRP_DESCR = ' '
end-if
End-Procedure
!***********************************************************************
! Get Country Data
!***********************************************************************
Begin-Procedure Get_Country
Let $Country_Usa = ' '
Let $Citizenship_Status_Usa = ' '
Let $Country_Oth = ' '
Let $Citizenship_Status_Oth = ' '
Begin-select
SB8.COUNTRY,
SB8.CITIZENSHIP_STATUS
if &SB8.COUNTRY = 'USA'
Let $Country_Usa = &SB8.COUNTRY
Let $Citizenship_Status_Usa = &SB8.CITIZENSHIP_STATUS
end-if
if &SB8.COUNTRY <> 'USA' and $Country_Oth = ' ' ! Ticket 59071 from Bill G.
! (&SB8.CITIZENSHIP_STATUS = 'N' or &SB8.CITIZENSHIP_STATUS = ' ' )
Let $Country_Oth = &SB8.COUNTRY
Let $Citizenship_Status_Oth = &SB8.CITIZENSHIP_STATUS
end-if
FROM PS_CITIZENSHIP SB8
WHERE SB8.EMPLID = &STA.EMPLID
order by decode(sb8.country,'USA',1,2), decode(SB8.CITIZENSHIP_STATUS,'N',1,' ',2,3)
end-select
End-Procedure
!***********************************************************************
! Get item_type ranges
!***********************************************************************
begin-procedure get_ranges
Let $UMA_RANGE_FROM_TUI = '000000000000'
Let $UMA_RANGE_TO_TUI = '999999999999'
Let $UMA_RANGE_FROM_ADM = '000000000000'
Let $UMA_RANGE_TO_ADM = '999999999999'
Let $UMA_RANGE_FROM_ENR = '000000000000'
Let $UMA_RANGE_TO_ENR = '999999999999'
Let $UMF_RANGE_FROM_TUI = '000000000000'
Let $UMF_RANGE_TO_TUI = '999999999999'
Let $UMF_RANGE_FROM_ADM = '000000000000'
Let $UMF_RANGE_TO_ADM = '999999999999'
Let $UMF_RANGE_FROM_ENR = '000000000000'
Let $UMF_RANGE_TO_ENR = '999999999999'
Let $UMPI_RANGE_FROM_TUI = '000000000000'
Let $UMPI_RANGE_TO_TUI = '999999999999'
Let $UMPI_RANGE_FROM_ADM = '000000000000'
Let $UMPI_RANGE_TO_ADM = '999999999999'
Let $UMPI_RANGE_FROM_ENR = '000000000000'
Let $UMPI_RANGE_TO_ENR = '999999999999'
Let $UMFK_RANGE_FROM_TUI = '000000000000'
Let $UMFK_RANGE_TO_TUI = '999999999999'
Let $UMFK_RANGE_FROM_ADM = '000000000000'
Let $UMFK_RANGE_TO_ADM = '999999999999'
Let $UMFK_RANGE_FROM_ENR = '000000000000'
Let $UMFK_RANGE_TO_ENR = '999999999999'
Let $UMM_RANGE_FROM_TUI = '000000000000'
Let $UMM_RANGE_TO_TUI = '999999999999'
Let $UMM_RANGE_FROM_ADM = '000000000000'
Let $UMM_RANGE_TO_ADM = '999999999999'
Let $UMM_RANGE_FROM_ENR = '000000000000'
Let $UMM_RANGE_TO_ENR = '999999999999'
Let $UM_RANGE_FROM_TUI = '0000000000'
Let $UM_RANGE_TO_TUI = '9999999999'
Let $UM_RANGE_FROM_ADM = '0000000000'
Let $UM_RANGE_TO_ADM = '9999999999'
Let $UM_RANGE_FROM_ENR = '0000000000'
Let $UM_RANGE_TO_ENR = '9999999999'
Let $USM_RANGE_FROM_TUI = '0000000000'
Let $USM_RANGE_TO_TUI = '9999999999'
Let $USM_RANGE_FROM_ADM = '0000000000'
Let $USM_RANGE_TO_ADM = '9999999999'
Let $USM_RANGE_FROM_ENR = '0000000000'
Let $USM_RANGE_TO_ENR = '9999999999'
BEGIN-SELECT
RATF.TREE_NAME
RATF.TREE_NODE
RATF.SETID
MIN(RBTF.RANGE_FROM) &RANGE_FROM
MAX(RBTF.RANGE_TO) &RANGE_TO
RATF.TREE_NODE_NUM
RATF.TREE_NODE_NUM_END
RATF.PARENT_NODE_NAME
evaluate &RATF.TREE_NAME
when = 'UMA_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UMA_RANGE_FROM_TUI = &RANGE_FROM
Let $UMA_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UMA_RANGE_FROM_ADM = &RANGE_FROM
Let $UMA_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UMA_RANGE_FROM_ENR = &RANGE_FROM
Let $UMA_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'UMF_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UMF_RANGE_FROM_TUI = &RANGE_FROM
Let $UMF_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UMF_RANGE_FROM_ADM = &RANGE_FROM
Let $UMF_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UMF_RANGE_FROM_ENR = &RANGE_FROM
Let $UMF_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'UMFK_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UMFK_RANGE_FROM_TUI = &RANGE_FROM
Let $UMFK_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UMFK_RANGE_FROM_ADM = &RANGE_FROM
Let $UMFK_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UMFK_RANGE_FROM_ENR = &RANGE_FROM
Let $UMFK_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'UMM_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UMM_RANGE_FROM_TUI = &RANGE_FROM
Let $UMM_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UMM_RANGE_FROM_ADM = &RANGE_FROM
Let $UMM_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UMM_RANGE_FROM_ENR = &RANGE_FROM
Let $UMM_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'UM_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UM_RANGE_FROM_TUI = &RANGE_FROM
Let $UM_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UM_RANGE_FROM_ADM = &RANGE_FROM
Let $UM_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UM_RANGE_FROM_ENR = &RANGE_FROM
Let $UM_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'USM_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $USM_RANGE_FROM_TUI = &RANGE_FROM
Let $USM_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $USM_RANGE_FROM_ADM = &RANGE_FROM
Let $USM_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $USM_RANGE_FROM_ENR = &RANGE_FROM
Let $USM_RANGE_TO_ENR = &RANGE_TO
end-evaluate
when = 'UMPI_CHARGES'
evaluate &RATF.TREE_NODE
when = 'TUI'
Let $UMPI_RANGE_FROM_TUI = &RANGE_FROM
Let $UMPI_RANGE_TO_TUI = &RANGE_TO
when = 'ADM'
Let $UMPI_RANGE_FROM_ADM = &RANGE_FROM
Let $UMPI_RANGE_TO_ADM = &RANGE_TO
when = 'ENR'
Let $UMPI_RANGE_FROM_ENR = &RANGE_FROM
Let $UMPI_RANGE_TO_ENR = &RANGE_TO
end-evaluate
end-evaluate
from PSTREENODE RATF, PSTREELEAF RBTF WHERE
RATF.EFFDT = ( SELECT MAX(RATF_ED.EFFDT) FROM PSTREENODE RATF_ED
WHERE RATF_ED.SETID = RATF.SETID AND RATF_ED.SETCNTRLVALUE = RATF.SETCNTRLVALUE AND RATF_ED.TREE_NAME = RATF.TREE_NAME
AND RATF_ED.TREE_NODE_NUM = RATF.TREE_NODE_NUM AND RATF_ED.TREE_NODE = RATF.TREE_NODE AND RATF_ED.TREE_BRANCH = RATF.TREE_BRANCH
AND RATF.EFFDT <= SYSDATE)
AND RBTF.EFFDT = ( SELECT MAX(RBTF_ED.EFFDT) FROM PSTREELEAF RBTF_ED WHERE RBTF_ED.SETID = RBTF.SETID
AND RBTF_ED.SETCNTRLVALUE = RBTF.SETCNTRLVALUE AND RBTF_ED.TREE_NAME = RBTF.TREE_NAME AND RBTF_ED.TREE_NODE_NUM = RBTF.TREE_NODE_NUM
AND RBTF_ED.TREE_BRANCH = RBTF.TREE_BRANCH AND RBTF.EFFDT <= SYSDATE)
AND RATF.PARENT_NODE_NAME = 'ALL'
AND RATF.TREE_NAME IN ('UMA_CHARGES','UMF_CHARGES','UMFK_CHARGES','UMM_CHARGES','UM_CHARGES','USM_CHARGES','UMPI_CHARGES')
AND RATF.TREE_NAME = RBTF.TREE_NAME
AND RATF.SETID = RBTF.SETID
AND RATF.SETCNTRLVALUE = RBTF.SETCNTRLVALUE
AND RATF.TREE_NODE_NUM = RBTF.TREE_NODE_NUM
AND RATF.TREE_BRANCH = RBTF.TREE_BRANCH
AND RBTF.TREE_NODE_NUM >= RATF.TREE_NODE_NUM
AND RBTF.TREE_NODE_NUM <= RATF.TREE_NODE_NUM_END
GROUP BY RATF.TREE_NAME, RATF.TREE_NODE, RATF.SETID, RATF.TREE_NODE_NUM, RATF.TREE_NODE_NUM_END, RATF.PARENT_NODE_NAME
ORDER BY RATF.TREE_NAME, RATF.TREE_NODE
END-SELECT
show 'Item type Ranges established:'
show 'UMA TUI ' $UMA_RANGE_FROM_TUI
show 'UMA TUI ' $UMA_RANGE_TO_TUI
show 'UMA ADM ' $UMA_RANGE_FROM_ADM
show 'UMA ADM ' $UMA_RANGE_TO_ADM
show 'UMA ENR ' $UMA_RANGE_FROM_ENR
show 'UMA ENR ' $UMA_RANGE_TO_ENR
show 'UMF TUI ' $UMF_RANGE_FROM_TUI
show 'UMF TUI ' $UMF_RANGE_TO_TUI
show 'UMF ADM ' $UMF_RANGE_FROM_ADM
show 'UMF ADM ' $UMF_RANGE_TO_ADM
show 'UMF ENR ' $UMF_RANGE_FROM_ENR
show 'UMF ENR ' $UMF_RANGE_TO_ENR
show 'UMPI TUI ' $UMPI_RANGE_FROM_TUI
show 'UMPI TUI ' $UMPI_RANGE_TO_TUI
show 'UMPI ADM ' $UMPI_RANGE_FROM_ADM
show 'UMPI ADM ' $UMPI_RANGE_TO_ADM
show 'UMPI ENR ' $UMPI_RANGE_FROM_ENR
show 'UMPI ENR ' $UMPI_RANGE_TO_ENR
show 'UMFK TUI ' $UMFK_RANGE_FROM_TUI
show 'UMFK TUI ' $UMFK_RANGE_TO_TUI
show 'UMFK ADM ' $UMFK_RANGE_FROM_ADM
show 'UMFK ADM ' $UMFK_RANGE_TO_ADM
show 'UMFK ENR ' $UMFK_RANGE_FROM_ENR
show 'UMFK ENR ' $UMFK_RANGE_TO_ENR
show 'UMM TUI ' $UMM_RANGE_FROM_TUI
show 'UMM TUI ' $UMM_RANGE_TO_TUI
show 'UMM ADM ' $UMM_RANGE_FROM_ADM
show 'UMM ADM ' $UMM_RANGE_TO_ADM
show 'UMM ENR ' $UMM_RANGE_FROM_ENR
show 'UMM ENR ' $UMM_RANGE_TO_ENR
show 'UM TUI ' $UM_RANGE_FROM_TUI
show 'UM TUI ' $UM_RANGE_TO_TUI
show 'UM ADM ' $UM_RANGE_FROM_ADM
show 'UM ADM ' $UM_RANGE_TO_ADM
show 'UM ENR ' $UM_RANGE_FROM_ENR
show 'UM ENR ' $UM_RANGE_TO_ENR
show 'USM TUI ' $USM_RANGE_FROM_TUI
show 'USM TUI ' $USM_RANGE_TO_TUI
show 'USM ADM ' $USM_RANGE_FROM_ADM
show 'USM ADM ' $USM_RANGE_TO_ADM
show 'USM ENR ' $USM_RANGE_FROM_ENR
show 'USM ENR ' $USM_RANGE_TO_ENR
end-procedure
!***********************************************************************
! Get tuition and fees
!***********************************************************************
! Darren Michael says use CHARGES trees 10/19/09
begin-procedure GET_TUITION_FEES
let #Tuition = 0
let #Fees = 0
evaluate &STUCAR.INSTITUTION
when = 'UMS01'
Let $tname = 'UMA_CHARGES'
Let $RANGE_FROM_TUI = $UMA_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UMA_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UMA_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UMA_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UMA_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UMA_RANGE_TO_ENR
when = 'UMS02'
Let $tname = 'UMF_CHARGES'
Let $RANGE_FROM_TUI = $UMF_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UMF_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UMF_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UMF_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UMF_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UMF_RANGE_TO_ENR
when = 'UMS03'
Let $tname = 'UMFK_CHARGES'
Let $RANGE_FROM_TUI = $UMFK_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UMFK_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UMFK_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UMFK_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UMFK_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UMFK_RANGE_TO_ENR
when = 'UMS04'
Let $tname = 'UMM_CHARGES'
Let $RANGE_FROM_TUI = $UMM_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UMM_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UMM_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UMM_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UMM_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UMM_RANGE_TO_ENR
when = 'UMS05'
Let $tname = 'UM_CHARGES'
Let $RANGE_FROM_TUI = $UM_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UM_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UM_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UM_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UM_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UM_RANGE_TO_ENR
when = 'UMS06'
Let $tname = 'USM_CHARGES'
Let $RANGE_FROM_TUI = $USM_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $USM_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $USM_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $USM_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $USM_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $USM_RANGE_TO_ENR
when = 'UMS07'
Let $tname = 'UMPI_CHARGES'
Let $RANGE_FROM_TUI = $UMPI_RANGE_FROM_TUI
Let $RANGE_TO_TUI = $UMPI_RANGE_TO_TUI
Let $RANGE_FROM_ADM = $UMPI_RANGE_FROM_ADM
Let $RANGE_TO_ADM = $UMPI_RANGE_TO_ADM
Let $RANGE_FROM_ENR = $UMPI_RANGE_FROM_ENR
Let $RANGE_TO_ENR = $UMPI_RANGE_TO_ENR
end-evaluate
Let $Tname_M = ''''||$tname||''''
Let $RANGE_FROM_TUI_M = ''''||$UMPI_RANGE_FROM_TUI||''''
Let $RANGE_TO_TUI_M = ''''||$UMPI_RANGE_TO_TUI||''''
Let $RANGE_FROM_ADM_M = ''''||$UMPI_RANGE_FROM_ADM||''''
Let $RANGE_TO_ADM_M = ''''||$UMPI_RANGE_TO_ADM||''''
Let $RANGE_FROM_ENR_M = ''''||$UMPI_RANGE_FROM_ENR||''''
Let $RANGE_TO_ENR_M = ''''||$UMPI_RANGE_TO_ENR||''''
begin-select
!x.tree_node,
ctf.item_type,
ctf.item_amt
if &ctf.item_type >= $RANGE_FROM_TUI
and &ctf.item_type <= $RANGE_TO_TUI
add &ctf.item_amt to #Tuition
else
add &ctf.item_amt to #FEES
end-if
FROM PS_ITEM_SF ctf
!, ( Select ATF.TREE_NODE, ATF.SETID, MIN(BTF.RANGE_FROM) AS RANGE_FROM, MAX(BTF.RANGE_TO) AS RANGE_TO, atf.tree_node_num, atf.tree_node_num_end, atf.PARENT_NODE_NAME from PSTREENODE ATF, PSTREELEAF BTF WHERE
! atf.effdt = ( select max(atf_ed.effdt) from pstreenode atf_ed where atf_ed.setid = atf.setid and atf_ed.setcntrlvalue = atf.setcntrlvalue and atf_ed.tree_name = atf.tree_name and atf_ed.tree_node_num = atf.tree_node_num and atf_ed.tree_node = atf.tree_node and atf_ed.tree_branch = atf.tree_branch and atf.effdt <= $input_date )
! and btf.effdt = ( select max(btf_ed.effdt) from pstreeleaf btf_ed where btf_ed.setid = btf.setid and btf_ed.setcntrlvalue = btf.setcntrlvalue and btf_ed.tree_name = btf.tree_name and btf_ed.tree_node_num = btf.tree_node_num and btf_ed.tree_branch = btf.tree_branch and btf.effdt <= $input_date )
! and atf.parent_node_name = 'ALL' and atf.tree_name = $Tname and atf.setid = btf.setid and atf.setcntrlvalue = btf.setcntrlvalue and atf.tree_node_num = btf.tree_node_num and atf.tree_branch = btf.tree_branch and atf.tree_name = btf.tree_name and btf.tree_node_num >= atf.tree_node_num and btf.tree_node_num <= atf.tree_node_num_end
! group by ATF.TREE_NODE, ATF.SETID, atf.tree_node_num, atf.tree_node_num_end, atf.PARENT_NODE_NAME ) X
WHERE CTF.ITEM_TERM = &STUCAR.STRM
AND CTF.BUSINESS_UNIT = &STUCAR.INSTITUTION
AND CTF.COMMON_ID = &STUCAR.EMPLID
AND CTF.ACAD_CAREER = &STC.ACAD_CAREER
AND CTF.STDNT_CAR_NBR = &STC.STDNT_CAR_NBR
AND CTF.SA_ID_TYPE = 'P'
!AND ctf.business_unit = x.setid
and (
(ctf.item_type >= [$RANGE_FROM_ADM_M]
and ctf.item_type <= [$RANGE_TO_ADM_M])
or
( ctf.item_type >= [$RANGE_FROM_ENR_M]
and ctf.item_type <= [$RANGE_TO_ENR_M] )
or
( ctf.item_type >= [$RANGE_FROM_TUI_M] !x.range_from
and ctf.item_type <= [$RANGE_TO_TUI_M] ) !x.range_to
)
!group by x.tree_node
end-select
end-procedure
!***********************************************************************
! Get Residency Data
!***********************************************************************
Begin-Procedure Get_Residency
Let $Effective_Term = ' '
Let $Residency = ' '
Let $Residency_Dt = STRTODATE('01-JAN-1901','DD-MON-YYYY')
Let $Admission_Res = ' '
Let $Admission_Excpt = ' '
Begin-select
SB9.EFFECTIVE_TERM,
SB9.RESIDENCY,
SB9.TUITION_RES,
SB9.RESIDENCY_DT,
SB9.ADMISSION_RES,
SB9.ADMISSION_EXCPT
if &SB9.EFFECTIVE_TERM > ' '
Let $Effective_Term = &SB9.EFFECTIVE_TERM
end-if
if &SB9.RESIDENCY > ' '
Let $Residency = &SB9.TUITION_RES
end-if
if &SB9.RESIDENCY > ' '
Let $Residency_Dt = &SB9.RESIDENCY_DT
end-if
if &SB9.ADMISSION_RES > ' '
Let $Admission_Res = &SB9.ADMISSION_RES
end-if
if &SB9.ADMISSION_EXCPT > ' '
Let $Admission_Excpt = &SB9.ADMISSION_EXCPT
end-if
FROM PS_RESIDENCY_OFF SB9
WHERE SB9.EMPLID = &STUCAR.EMPLID
AND SB9.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND SB9.INSTITUTION = &STUCAR.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 <= &STUCAR.STRM) ! ??1006
OR (SB9.EFFECTIVE_TERM = (SELECT MAX(EC9.EFFECTIVE_TERM) FROM PS_RESIDENCY_OFF EC9 ! ??1006
WHERE EC9.EMPLID = SB9.EMPLID AND EC9.ACAD_CAREER = EC9.ACAD_CAREER AND EC9.INSTITUTION = SB9.INSTITUTION) ! ??1006
AND NOT EXISTS (SELECT 'X' FROM PS_RESIDENCY_OFF ED9 ! ??1006
WHERE ED9.EMPLID=SB9.EMPLID AND ED9.ACAD_CAREER = SB9.ACAD_CAREER AND ED9.INSTITUTION = SB9.INSTITUTION ! ??1006
AND ED9.EFFECTIVE_TERM <= &STUCAR.STRM)) ! ??1006
)
end-select
End-Procedure
!***********************************************************************
! Get Acad Prog Descr
!***********************************************************************
Begin-Procedure Get_A_Descr
Let $Acad_Prog_Descr = ' '
Let $Acad_prog_Primary_Descr = ' '
Let $Academic_Group = ' ' !??0607
Let $Academic_Group_descr = ' ' !??0607
Let $Level_Load_Rule = ' '
Let $Fa_eligibility2 = ' ' ! REPORT-201
Let #I = 1
while #I <= 2
if #i = 1
let $look_Acad_prog = &Stucar.ACAD_prog_primary
else
let $look_acad_prog = &Stc.acad_prog
end-if
Begin-select
SB10.DESCR,
SB10.INSTITUTION,
SB10.ACAD_PROG,
SB10.ACAD_GROUP,
SB10.LEVEL_LOAD_RULE,
SB10.FA_ELIGIBILITY
CR10.DESCR
if &SB10.DESCR > ' ' and #i = 1
Let $Acad_Prog_Primary_Descr = &SB10.DESCR
end-if
if &SB10.DESCR > ' ' and #i = 2
Let $Acad_Prog_Descr = &SB10.DESCR
end-if
if &SB10.ACAD_GROUP > ' ' and #i = 1
Let $Academic_Group = &SB10.ACAD_GROUP
end-if
if &CR10.DESCR > ' ' and #i = 1
Let $Academic_Group_Descr = &CR10.DESCR
end-if
if &SB10.LEVEL_LOAD_RULE > ' ' and #i = 1
Let $Level_Load_Rule = &SB10.LEVEL_LOAD_RULE
end-if
if &SB10.FA_ELIGIBILITY > ' ' and #i = 1 ! REPORT-201
Let $Fa_eligibility2 = &SB10.FA_ELIGIBILITY
end-if
FROM PS_ACAD_PROG_TBL SB10, PS_ACAD_GROUP_TBL CR10
WHERE SB10.ACAD_PROG = $look_Acad_prog
AND SB10.INSTITUTION = &STUCAR.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 <= [$input_date_M])
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 <= [$input_date_M])
or CR10.EFFDT is null)
end-select
add 1 to #I
end-while
End-Procedure
!***********************************************************************
! Get Last School Info
!***********************************************************************
Begin-Procedure Get_Last_Sch_info
Let $Last_Sch_Descr = ' '
Let $Last_Sch_Type = ' '
Let $Ext_Org_Id_Descr = ' '
Let $Ext_org_id = &apx.LAST_SCH_ATTEND
Begin-select
lbs.LS_SCHOOL_TYPE,
las.descr
if &las.descr > ' '
Let $Last_Sch_Descr = &las.descr
Let $Ext_Org_Id_Descr = &las.descr
end-if
if &lbs.LS_SCHOOL_TYPE > ' '
Let $Last_Sch_type = &lbs.LS_SCHOOL_type
end-if
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 <= $input_date)
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 <= $input_date)
AND LBS.EFF_STATUS = 'A'
AND las.ext_org_id = &apx.last_sch_attend
end-select
End-Procedure
!***********************************************************************
! Get Description for Program Reason
!***********************************************************************
begin-procedure Get_Prog_Rsn_Descr
Let $Um_Prog_Rsn_Descr = ' '
begin-select
nvl(RSN.DESCR,' ') &rsn.descr
Let $Um_Prog_Rsn_Descr = &RSN.DESCR
FROM PS_PROG_RSN_TBL RSN
WHERE RSN.PROG_REASON = &STC.PROG_REASON
AND RSN.EFF_STATUS = 'A'
AND RSN.SETID = 'UMSYS'
AND RSN.PROG_ACTION = &STC.PROG_ACTION
AND RSN.EFFDT =
(SELECT MAX(RSN_ED.EFFDT) FROM PS_PROG_RSN_TBL RSN_ED
WHERE RSN.PROG_ACTION = RSN_ED.PROG_ACTION
AND RSN.PROG_REASON = RSN_ED.PROG_REASON
AND RSN.SETID = RSN_ED.SETID
AND RSN_ED.EFFDT <= [$input_date_M])
end-select
end-procedure
!***********************************************************************
! Get Distance and Web hours
!***********************************************************************
begin-procedure Get_Distance_Web
Let #Web_Hrs = 0
Let #Dist_Hrs = 0
Let #Chk_Hrs = 0
Let #Itv_Hrs = 0
BEGIN-SELECT
CU.EMPLID,
CU.ACAD_CAREER,
CU.INSTITUTION,
CU.STRM,
CU.CLASS_NBR,
!CU.CRSE_CAREER,
CU.SESSION_CODE,
CU.STDNT_ENRL_STATUS,
!CU.ENRL_STATUS_REASON,
!CU.ENRL_ACTION_LAST,
!CU.ENRL_ACTN_RSN_LAST,
!CU.ENRL_ACTN_PRC_LAST,
!CU.STATUS_DT,
!CU.ENRL_ADD_DT,
CU.ENRL_DROP_DT,
!CU.UNT_TAKEN,
CU.UNT_PRGRSS,
!CU.UNT_PRGRSS_FA,
!CU.UNT_BILLING,
!CU.CRSE_COUNT,
!CU.GRADING_BASIS_ENRL,
!CU.GRADING_BASIS_DT,
!CU.OVRD_GRADING_BASIS,
!CU.CRSE_GRADE_OFF,
!CU.CRSE_GRADE_INPUT,
!CU.GRADE_DT,
!CU.REPEAT_CODE,
!CU.REPEAT_DT,
!CU.ASSOCIATED_CLASS,
CU.AUDIT_GRADE_BASIS,
CU.EARN_CREDIT,
!CU.INCLUDE_IN_GPA,
CU.UNITS_ATTEMPTED,
!CU.GRADE_POINTS,
!CU.GRADE_POINTS_FA,
!CU.GRD_PTS_PER_UNIT,
!CU.MANDATORY_GRD_BAS,
!CU.DROP_CLASS_IF_ENRL,
!CU.ASSOCIATION_99,
!CU.TSCRPT_NOTE_ID,
!CU.TSCRPT_NOTE_EXISTS,
!CU.NOTIFY_STDNT_CHNG,
!CU.REPEAT_CANDIDATE,
!CU.VALID_ATTEMPT,
!CU.GRADE_CATEGORY,
!CU.SEL_GROUP,
!CU.DYN_CLASS_NBR,
!CU.UNT_EARNED,
!CU.LAST_UPD_DT_STMP,
!CU.LAST_UPD_TM_STMP,
!CU.LAST_ENRL_DT_STMP,
!CU.LAST_ENRL_TM_STMP,
!CU.LAST_DROP_DT_STMP,
!CU.LAST_DROP_TM_STMP,
!CU.ENRL_REQ_SOURCE,
!CU.LAST_UPD_ENREQ_SRC,
!CU.GRADING_SCHEME_ENR,
!CU.RELATE_CLASS_NBR_1,
!CU.RELATE_CLASS_NBR_2,
!CU.ACAD_PROG,
CU2.CRSE_ID,
CU2.CRSE_OFFER_NBR,
CU2.CLASS_SECTION,
!CU2.ACAD_GROUP,
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
if &UM_DIST_ATTR_FLAG = 'Y'
Let #Dist_Hrs = #Dist_Hrs + &cu.unt_prgrss
end-if
if &UM_IMODE = 'ONLINE'
Let #Web_Hrs = #Web_hrs + &cu.unt_prgrss
end-if
if &UM_DISTANCE_FLAG = 'R'
Let #ITV_HRS = #ITV_HRS + &cu.unt_prgrss
end-if
Let #Chk_Hrs = #Chk_Hrs + &cu.unt_prgrss
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 = &STUCAR.EMPLID
AND CU.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND CU.INSTITUTION = &STUCAR.INSTITUTION
AND CU.STRM = &STUCAR.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 )
END-SELECT
end-procedure
!***********************************************************************
! Get multi registrations
!***********************************************************************
begin-procedure multi_reg
Let $UMS01_FLAG = ' '
Let $UMS02_FLAG = ' '
Let $UMS03_FLAG = ' '
Let $UMS04_FLAG = ' '
Let $UMS05_FLAG = ' '
Let $UMS06_FLAG = ' '
Let $UMS07_FLAG = ' '
Let #UMS01_UNT_TAKEN_PRGRSS = 0
Let #UMS02_UNT_TAKEN_PRGRSS = 0
Let #UMS03_UNT_TAKEN_PRGRSS = 0
Let #UMS04_UNT_TAKEN_PRGRSS = 0
Let #UMS05_UNT_TAKEN_PRGRSS = 0
Let #UMS06_UNT_TAKEN_PRGRSS = 0
Let #UMS07_UNT_TAKEN_PRGRSS = 0
Let #um_unt_taken_prgrss_rowc = 0
Let #um_unt_taken_fa_rowc = 0
Let #units_this_career = 0
Let $UM_ACAD_LOAD_RC = ' '
begin-select
multi.institution,
multi.unt_prgrss,
multi.acad_career,
multi.unt_prgrss_fa
evaluate &multi.institution
when = 'UMS01'
IF &multi.acad_career <> 'NCRD'
Let $UMS01_FLAG = 'Y'
Let #UMS01_UNT_TAKEN_PRGRSS = #UMS01_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS02'
IF &multi.acad_career <> 'NCRD'
Let $UMS02_FLAG = 'Y'
Let #UMS02_UNT_TAKEN_PRGRSS = #UMS02_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS03'
IF &multi.acad_career <> 'NCRD'
Let $UMS03_FLAG = 'Y'
Let #UMS03_UNT_TAKEN_PRGRSS = #UMS03_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS04'
IF &multi.acad_career <> 'NCRD'
Let $UMS04_FLAG = 'Y'
Let #UMS04_UNT_TAKEN_PRGRSS = #UMS04_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS05'
IF &multi.acad_career <> 'NCRD'
Let $UMS05_FLAG = 'Y'
Let #UMS05_UNT_TAKEN_PRGRSS = #UMS05_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS06'
IF &multi.acad_career <> 'NCRD'
Let $UMS06_FLAG = 'Y'
Let #UMS06_UNT_TAKEN_PRGRSS = #UMS06_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
when = 'UMS07'
IF &multi.acad_career <> 'NCRD'
Let $UMS07_FLAG = 'Y'
Let #UMS07_UNT_TAKEN_PRGRSS = #UMS07_UNT_TAKEN_PRGRSS + &multi.UNT_PRGRSS
END-IF
break
end-evaluate
if &STUCAR.INSTITUTION = &multi.institution and &multi.acad_career <> 'NCRD'
Let #um_unt_taken_prgrss_rowc = #um_unt_taken_prgrss_rowc + &multi.UNT_PRGRSS
Let #um_unt_taken_fa_rowc = #um_unt_taken_fa_rowc + &multi.UNT_PRGRSS_FA
end-if
if &STUCAR.INSTITUTION = &multi.institution and &STUCAR.ACAD_CAREER = &multi.ACAD_CAREER
Let #units_this_career = #units_this_career + &multi.UNT_PRGRSS
end-if
from PS_STDNT_ENRL multi
WHERE multi.emplid = &STUCAR.EMPLID
AND multi.strm = &STUCAR.STRM
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 )
end-select
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
end-procedure
begin-procedure GET_MISSING_ADDRESS
Let $address1 = ' '
Let $address2 = ' '
Let $address3 = ' '
Let $address4 = ' '
Let $city = ' '
Let $state = ' '
Let $postal = ' '
Let $county = ' '
Let $COUNTRY = ' '
begin-select
miss.address1,
miss.address2,
miss.address3,
miss.address4,
miss.city,
miss.state,
miss.postal,
miss.county,
miss.country,
miss.address_type
if $city = ' ' ! REPORT-313
Let $address1 = &miss.address1
Let $address2 = &miss.address2
Let $address3 = &miss.address3
Let $address4 = &miss.address4
Let $city = &miss.city
Let $state = &miss.state
Let $postal = &miss.postal
Let $county = &miss.county
Let $COUNTRY = &miss.country
end-if
from ps_addresses miss
where miss.emplid = &STA.EMPLID
and miss.effdt = ( select max(miss_ed.effdt) from ps_addresses miss_ed
where miss_ed.emplid = miss.emplid
and miss_ed.address_type = miss.address_type
! and miss_ed.eff_status = miss.eff_status
)
and miss.eff_status = 'A'
order by decode(miss.address_type,'HOME','1','BILL','2','MAIL','3','CHK','4','BUSN','5','CAMP','6','7')
end-select
end-procedure
!***********************************************************************
! Get Student PlanData
!***********************************************************************
begin-procedure Get_Stu_Plan
Let $Acad_Plan = ' '
Let $Acad_Plan_Descr = ' '
Let $Acad_Plan_Type = ' '
Let $Degree = ' '
Let $Acad_Sub_Plan = ' '
Let $Acad_SubPlan_Descr = ' '
Let $Acad_SubPlan_Type = ' '
Let $Declare_dt = STRTODATE('01-JAN-1901','DD-MON-YYYY')
Let #Plan_sequence = 0
Let $STDNT_DEGR = ' '
!Let $Degr_chkout_stat = ' ' ! get from PS_ACAD_PROG instead
Let $Advis_status = ' '
Let $Education_Lvl = ' '
let #n_plans = 0
! Find a date appropriate to the strm
Begin-select
nvl(max(SSR_TRMAC_LAST_DT),SYSDATE) &THIS_TERM_END_DATE
from ps_term_tbl rqe
where
rqe.institution = &STUCAR.INSTITUTION
and rqe.acad_career = &STUCAR.ACAD_CAREER
and rqe.strm = &STUCAR.STRM
end-select
begin-select
NVL(ST12.ACAD_PLAN,' ') &ST12.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,' ') &ST13.DESCR,
NVL(ST13.ACAD_PLAN_TYPE,' ') &ST13.ACAD_PLAN_TYPE,
NVL(ST13.DEGREE,' ') &ST13.DEGREE,
NVL(ST14.ACAD_PLAN,' ') &ST14.ACAD_PLAN,
NVL(ST14.ACAD_SUB_PLAN,' ' ) &ST14.ACAD_SUB_PLAN,
NVL(ST15.ACAD_SUBPLAN_TYPE,' ') &ST15.ACAD_SUBPLAN_TYPE,
NVL(ST15.DESCR,' ') &ST15.DESCR,
NVL(DG.EDUCATION_LVL,' ') &DG.EDUCATION_LVL
if &ST12.EFFDT < &THIS_TERM_END_DATE or $Acad_plan <= ' '
Let $Acad_Plan = &ST12.ACAD_PLAN
Let $Declare_dt = &ST12.DECLARE_DT
Let #Plan_sequence = &ST12.PLAN_SEQUENCE
Let $STDNT_DEGR = &ST12.STDNT_DEGR
! Let $Degr_chkout_stat = &ST12.DEGR_CHKOUT_STAT ! get from PS_ACAD_PROG instead
Let $Advis_status = &ST12.ADVIS_STATUS
Let $Acad_Plan_Descr = &ST13.DESCR
Let $Acad_Plan_Type = &ST13.ACAD_PLAN_TYPE
Let $Degree = &ST13.DEGREE
Let $Acad_Sub_Plan = &ST14.ACAD_SUB_PLAN
Let $Acad_SubPlan_Descr = &ST15.DESCR
Let $Acad_SubPlan_Type = &ST15.ACAD_SUBPLAN_TYPE
Let $Education_Lvl = &DG.EDUCATION_LVL
add 1 to #n_plans
end-if
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 = &STUCAR.EMPLID
AND ST12.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND ST12.STDNT_CAR_NBR = &STUCAR.STDNT_CAR_NBR
AND ST13.INSTITUTION = &STUCAR.INSTITUTION
AND ST13.ACAD_PLAN_TYPE in ('MAJ','DMJ','SP','PRP','CER')
! AND ST12.EFFDT =
! (SELECT MAX(ST12_ED.EFFDT) FROM PS_ACAD_PLAN ST12_ED
! WHERE ST12.EMPLID = ST12_ED.EMPLID
! AND ST12.ACAD_CAREER = ST12_ED.ACAD_CAREER
! AND ST12.ACAD_PLAN = ST12_ED.ACAD_PLAN
! AND ST12.STDNT_CAR_NBR = ST12_ED.STDNT_CAR_NBR
! AND ST12_ED.EFFDT <= [$input_date_M])
! AND ST12.EFFSEQ =
! (SELECT MAX(ST12_ES.EFFSEQ) FROM PS_ACAD_PLAN ST12_ES
! WHERE ST12.EMPLID = ST12_ES.EMPLID
! AND ST12.ACAD_CAREER = ST12_ES.ACAD_CAREER
! AND ST12.ACAD_PLAN = ST12_ES.ACAD_PLAN
! AND ST12.STDNT_CAR_NBR = ST12_ES.STDNT_CAR_NBR
! AND ST12.EFFDT = ST12_ES.EFFDT)
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 <= [$input_date_M])
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 <= [$input_date_M])
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)
order by st12.effdt , st12.effseq ,
decode(ST13.ACAD_PLAN_TYPE,'MAJ',1,'DMJ',3,'SP',1,'PRP',1,'CON',1,'COS',1,'HON',1,'RTC',1,'CER',2,5) desc, st12.plan_sequence desc
end-select
! Find the first prog/plan value for this student career
Let $Min_Acad_Plan = ' '
Let $Min_Acad_Plan_Descr = ' '
Let $Min_Acad_Plan_Type = ' '
Let $Min_Degree = ' '
Let $Min_Acad_Sub_Plan = ' '
Let $Min_Acad_SubPlan_Descr = ' '
Let $Min_Acad_SubPlan_Type = ' '
Let $Min_Acad_Prog = ' '
begin-select
NVL(MNS12.ACAD_PLAN,' ') &MNS12.ACAD_PLAN,
NVL(MNS13.DESCR,' ') &MNS13.DESCR,
NVL(MNS13.ACAD_PLAN_TYPE,' ') &MNS13.ACAD_PLAN_TYPE,
NVL(MNS13.DEGREE,' ') &MNS13.DEGREE,
NVL(MNS14.ACAD_PLAN,' ') &MNS14.ACAD_PLAN,
NVL(MNS14.ACAD_SUB_PLAN,' ' ) &MNS14.ACAD_SUB_PLAN,
NVL(MNS15.ACAD_SUBPLAN_TYPE,' ') &MNS15.ACAD_SUBPLAN_TYPE,
NVL(MNS15.DESCR,' ') &MNS15.DESCR
MNSC.ACAD_PROG
Let $Min_Acad_Plan = &MNS12.ACAD_PLAN
Let $Min_Acad_Plan_Descr = &MNS13.DESCR
Let $Min_Acad_Plan_Type = &MNS13.ACAD_PLAN_TYPE
Let $Min_Degree = &MNS13.DEGREE
Let $Min_Acad_Sub_Plan = &MNS14.ACAD_SUB_PLAN
Let $Min_Acad_SubPlan_Descr = &MNS15.DESCR
Let $Min_Acad_SubPlan_Type = &MNS15.ACAD_SUBPLAN_TYPE
Let $Min_ACAD_PROG = &MNSC.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 = &STUCAR.EMPLID
AND MNSC.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND MNSC.STDNT_CAR_NBR = &STUCAR.STDNT_CAR_NBR
! AND MNSC.ADMIT_TERM = &STUCAR.STRM
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 ( MNS14.EFFDT =
! (SELECT MIN(MNS14_ED.EFFDT) FROM PS_ACAD_SUBPLAN MNS14_ED
! WHERE MNS14.EMPLID = MNS14_ED.EMPLID
! AND MNS14.ACAD_CAREER = MNS14_ED.ACAD_CAREER
! AND MNS14.ACAD_PLAN = MNS14_ED.ACAD_PLAN
! AND MNS14.STDNT_CAR_NBR = MNS14_ED.STDNT_CAR_NBR)
! AND MNS14.EFFSEQ =
! (SELECT MIN(MNS14_ES.EFFSEQ) FROM PS_ACAD_SUBPLAN MNS14_ES
! WHERE MNS14.EMPLID = MNS14_ES.EMPLID
! AND MNS14.ACAD_CAREER = MNS14_ES.ACAD_CAREER
! AND MNS14.ACAD_PLAN = MNS14_ES.ACAD_PLAN
! AND MNS14.STDNT_CAR_NBR = MNS14_ES.STDNT_CAR_NBR
! AND MNS14.EFFDT = MNS14_ES.EFFDT)
! OR MNS14.EFFDT IS NULL)
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)
order by MNS12.EFFDT desc
end-select
! Find the prog/plan value at the end of the term
Let $Orig_Acad_Plan = ' '
Let $Orig_Acad_Plan_Descr = ' '
Let $Orig_Acad_Plan_Type = ' '
Let $Orig_Degree = ' '
Let $Orig_Acad_Prog = ' '
begin-select
ORIG.EMPLID,
NVL(ORG12.ACAD_PLAN,' ') &ORG12.ACAD_PLAN,
NVL(ORG13.DESCR,' ') &ORG13.DESCR,
NVL(ORG13.ACAD_PLAN_TYPE,' ') &ORG13.ACAD_PLAN_TYPE,
NVL(ORG13.DEGREE,' ') &ORG13.DEGREE,
NVL(ORIG.ACAD_PROG,' ') &ORIG.ACAD_PROG
Let $Orig_Acad_Plan = &ORG12.ACAD_PLAN
Let $Orig_Acad_Plan_Descr = &ORG13.DESCR
Let $Orig_Acad_Plan_Type = &ORG13.ACAD_PLAN_TYPE
Let $Orig_Degree = &ORG13.DEGREE
Let $Orig_Acad_Prog = &ORIG.ACAD_PROG
FROM PS_ACAD_PROG ORIG, PS_ACAD_PLAN ORG12
,PS_ACAD_PLAN_TBL ORG13
WHERE ORIG.EMPLID = &STUCAR.EMPLID
AND ORIG.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND ORIG.STDNT_CAR_NBR = &STUCAR.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 = &STUCAR.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 =
(SELECT MAX(ORG12_ED.EFFDT) FROM PS_ACAD_PLAN ORG12_ED
WHERE ORG12.EMPLID = ORG12_ED.EMPLID
AND ORG12.ACAD_CAREER = ORG12_ED.ACAD_CAREER
AND ORG12.ACAD_PLAN = ORG12_ED.ACAD_PLAN
AND ORG12.STDNT_CAR_NBR = ORG12_ED.STDNT_CAR_NBR
AND ORG12_ED.EFFDT <= (SELECT OD12.TERM_END_DT FROM PS_TERM_TBL OD12
WHERE OD12.INSTITUTION = ORIG.INSTITUTION
AND OD12.ACAD_CAREER = ORIG.ACAD_CAREER
AND OD12.STRM = &STUCAR.STRM)
) OR ORG12.EFFDT IS NULL)
AND (ORG12.EFFSEQ =
(SELECT MAX(ORG12_ES.EFFSEQ) FROM PS_ACAD_PLAN ORG12_ES
WHERE ORG12.EMPLID = ORG12_ES.EMPLID
AND ORG12.ACAD_CAREER = ORG12_ES.ACAD_CAREER
AND ORG12.ACAD_PLAN = ORG12_ES.ACAD_PLAN
AND ORG12.STDNT_CAR_NBR = ORG12_ES.STDNT_CAR_NBR
AND ORG12.EFFDT = ORG12_ES.EFFDT) or ORG12.EFFSEQ is NULL)
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 <= [$input_date_M])
order by decode(ORG13.ACAD_PLAN_TYPE,'MAJ',1,'DMJ',3,'SP',1,'PRP',1,'CON',1,'COS',1,'HON',1,'RTC',1,'CER',2,5) desc,
org12.effdt , org12.effseq , ORG12.plan_sequence desc
end-select
! If there is no prog/plan value at start of term get min value after start of term
if $Orig_Acad_Plan <= ' ' or $Orig_Acad_Prog <= ' '
begin-select
ERIG.EMPLID,
NVL(ERIG12.ACAD_PLAN,' ') &ERIG12.ACAD_PLAN,
NVL(ERIG13.DESCR,' ') &ERIG13.DESCR,
NVL(ERIG13.ACAD_PLAN_TYPE,' ') &ERIG13.ACAD_PLAN_TYPE,
NVL(ERIG13.DEGREE,' ') &ERIG13.DEGREE,
NVL(ERIG.ACAD_PROG,' ') &ERIG.ACAD_PROG
Let $ORIG_Acad_Plan = &ERIG12.ACAD_PLAN
Let $ORIG_Acad_Plan_Descr = &ERIG13.DESCR
Let $Orig_Acad_Plan_Type = &ERIG13.ACAD_PLAN_TYPE
Let $Orig_Degree = &ERIG13.DEGREE
Let $Orig_Acad_Prog = &ERIG.ACAD_PROG
FROM PS_ACAD_PROG ERIG, PS_ACAD_PLAN ERIG12
,PS_ACAD_PLAN_TBL ERIG13
WHERE ERIG.EMPLID = &STUCAR.EMPLID
AND ERIG.ACAD_CAREER = &STUCAR.ACAD_CAREER
AND ERIG.STDNT_CAR_NBR = &STUCAR.STDNT_CAR_NBR
AND ERIG.EFFDT =
(SELECT MIN(ERIG_ED.EFFDT) FROM PS_ACAD_PROG ERIG_ED
WHERE ERIG.EMPLID = ERIG_ED.EMPLID
AND ERIG.ACAD_CAREER = ERIG_ED.ACAD_CAREER
AND ERIG.STDNT_CAR_NBR = ERIG_ED.STDNT_CAR_NBR
AND ERIG_ED.EFFDT > (SELECT OD.TERM_END_DT FROM PS_TERM_TBL OD
WHERE OD.INSTITUTION = ERIG.INSTITUTION
AND OD.ACAD_CAREER = ERIG.ACAD_CAREER
AND OD.STRM = &STUCAR.STRM)
)
AND ERIG.EFFSEQ =
(SELECT MAX(ORG_ES.EFFSEQ) FROM PS_ACAD_PROG ORG_ES
WHERE ERIG.EMPLID = ORG_ES.EMPLID
AND ERIG.ACAD_CAREER = ORG_ES.ACAD_CAREER
AND ERIG.STDNT_CAR_NBR = ORG_ES.STDNT_CAR_NBR
AND ERIG.EFFDT = ORG_ES.EFFDT)
AND ERIG13.ACAD_PLAN_TYPE in ('MAJ','DMJ','SP','PRP','CER')
AND ERIG12.EMPLID(+) = ERIG.EMPLID
AND ERIG12.ACAD_CAREER(+) = ERIG.ACAD_CAREER
AND ERIG12.STDNT_CAR_NBR(+) = ERIG.STDNT_CAR_NBR
AND ERIG13.INSTITUTION = ERIG.INSTITUTION
AND (ERIG12.EFFDT =
(SELECT MIN(ERIG12_ED.EFFDT) FROM PS_ACAD_PLAN ERIG12_ED
WHERE ERIG12.EMPLID = ERIG12_ED.EMPLID
AND ERIG12.ACAD_CAREER = ERIG12_ED.ACAD_CAREER
AND ERIG12.ACAD_PLAN = ERIG12_ED.ACAD_PLAN
AND ERIG12.STDNT_CAR_NBR = ERIG12_ED.STDNT_CAR_NBR
AND ERIG12_ED.EFFDT > (SELECT OD12.TERM_END_DT FROM PS_TERM_TBL OD12
WHERE OD12.INSTITUTION = ERIG.INSTITUTION
AND OD12.ACAD_CAREER = ERIG.ACAD_CAREER
AND OD12.STRM = &STUCAR.STRM)
) OR ERIG12.EFFDT IS NULL)
AND (ERIG12.EFFSEQ =
(SELECT MAX(ERIG12_ES.EFFSEQ) FROM PS_ACAD_PLAN ERIG12_ES
WHERE ERIG12.EMPLID = ERIG12_ES.EMPLID
AND ERIG12.ACAD_CAREER = ERIG12_ES.ACAD_CAREER
AND ERIG12.ACAD_PLAN = ERIG12_ES.ACAD_PLAN
AND ERIG12.STDNT_CAR_NBR = ERIG12_ES.STDNT_CAR_NBR
AND ERIG12.EFFDT = ERIG12_ES.EFFDT) or ERIG12.EFFSEQ is NULL)
AND ERIG12.ACAD_PLAN = ERIG13.ACAD_PLAN
AND ERIG13.EFFDT =
(SELECT MAX(ERIG13_ED.EFFDT) FROM PS_ACAD_PLAN_TBL ERIG13_ED
WHERE ERIG13.INSTITUTION = ERIG13_ED.INSTITUTION
AND ERIG13.ACAD_PLAN = ERIG13_ED.ACAD_PLAN
AND ERIG13_ED.EFFDT <= [$input_date_M])
order by decode(ERIG13.ACAD_PLAN_TYPE,'MAJ',1,'DMJ',3,'SP',1,'PRP',1,'CER',2,5) desc , ERIG12.effdt, ERIG12.plan_sequence desc
end-select
end-if
if #n_plans = 0
show 'No plan for ' &STUCAR.EMPLID
end-if
end-Procedure
!***********************************************************************
! Get Current Term
!***********************************************************************
begin-procedure Current_term($in_institution,$in_career,:$This_Term_is)
Let $This_Term_is = '0000'
BEGIN-SELECT
ENDO.STRM
Let $This_term_is = &ENDO.STRM
FROM PS_TERM_TBL ENDO
WHERE ENDO.INSTITUTION = $in_institution
AND ENDO.ACAD_CAREER = $in_career
AND ENDO.TERM_BEGIN_DT =
(SELECT MAX(ENDO_IN.TERM_BEGIN_DT) FROM PS_TERM_TBL ENDO_IN
WHERE ENDO_IN.INSTITUTION = ENDO.INSTITUTION
and ENDO_IN.ACAD_CAREER = ENDO.ACAD_CAREER
AND ENDO_IN.TERM_BEGIN_DT <= $_input_date)
END-SELECT
end-procedure
!***********************************************************************
! Get Term Stats
!***********************************************************************
begin-procedure Term_stats($in_institution, $in_career, $in_strm, :$Term_begin_dt_out, :$Term_end_Dt_out)
Let $Term_begin_dt_out = STRTODATE('01-JAN-1901','DD-MON-YYYY')
Let $Term_End_Dt_Out = STRTODATE('01-JAN-1901','DD-MON-YYYY')
BEGIN-SELECT
ENDO.STRM,
ENDO.TERM_BEGIN_DT,
ENDO.TERM_END_DT
Let $Term_begin_dt_out = &ENDO.TERM_BEGIN_DT
Let $Term_End_Dt_Out = &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
END-SELECT
end-procedure
!***********************************************************************
! Get Missing Admit_type
!***********************************************************************
begin-procedure Missing_Admit_type
Let $Adm_appl_ctr = ' '
Let $Admit_type = ' '
Let $ADM_APPL_DT = STRTODATE('01-JAN-1901','DD-MON-YYYY')
BEGIN-SELECT
NVL(VAP.ADM_APPL_CTR,' ') &VAP.ADM_APPL_CTR,
NVL(VAP.ADMIT_TYPE,' ') &VAP.ADMIT_TYPE,
NVL(VAP.ADM_APPL_DT, TO_DATE('01-JAN-1901 ','DD-MON-YYYY')) &VAP.ADM_APPL_DT
Let $Adm_appl_ctr = &VAP.ADM_APPL_CTR
Let $Admit_type = &VAP.ADMIT_TYPE
Let $ADM_APPL_DT = &VAP.ADM_APPL_DT
FROM PS_ADM_APPL_DATA VAP
WHERE VAP.INSTITUTION = &STC.INSTITUTION
AND VAP.ACAD_CAREER = &STC.ACAD_CAREER
AND VAP.EMPLID = &STC.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 <= $Term_end_Dt)
end-select
!PRINT 'Missing Admit Type for ' (+1,1)
!PRINT &STC.EMPLID (,+1)
!PRINT 'is' (,+1)
!PRINT $Admit_type (,+1)
!PRINT 'in Term' (,+1)
!PRINT &STUCAR.STRM (,+1)
!PRINT 'appl date =' (,+1)
!PRINT $ADM_APPL_DT (,+1)
!PRINT 'at Institution ' (,+1)
!Print &STC.INSTITUTION (,+1)
!PRINT 'with Term ending at' (,+1)
!PRINT $Term_end_dt (,+1)
end-procedure
!***********************************************************************
! Get Student Response
!***********************************************************************
Begin-Procedure Get_Response
Let $Response_Reason = ' '
Let $Response_Dt = STRTODATE('01-JAN-1901','DD-MON-YYYY')
Let $Ext_Org_Id = ' ' ! I will reset this later in get_last_sch_info
Let $Ext_Org_Id_Descr = ' '
Let $Response_Reason_Descr = ' '
begin-select
sb16.response_reason,
sb16.response_dt,
sb16.ext_org_id,
sb16.descr,
uj.descr
if &sb16.response_reason > ' '
Let $Response_Reason = &sb16.response_reason
end-if
if &sb16.response_dt > '01-JAN-06'
Let $Response_Dt = &sb16.response_dt
end-if
if &sb16.ext_org_id > ' '
Let $Ext_Org_Id = &sb16.ext_org_id ! See above - this is no longer reliable
end-if
if &sb16.descr > ' '
Let $Ext_Org_Id_Descr = &sb16.descr
end-if
if &uj.descr > ' '
Let $Response_Reason_Descr = &uj.descr
end-if
from ps_stdnt_response sb16, ps_resp_rsn_tbl uj
where sb16.acad_career = uj.acad_career(+)
and sb16.response_reason = uj.response_reason(+)
and sb16.emplid = &stucar.emplid
and sb16.acad_career = &stucar.acad_career
and sb16.stdnt_car_nbr = &stucar.stdnt_car_nbr
and &stucar.institution = uj.institution(+)
end-select
end-Procedure
begin-procedure zedo
Let #Num_Testo = 0
Let #Num_Compy = 0
end-procedure
begin-procedure maxxy
Let #MAX_SAT_MATH = 0
Let #MAX_SAT_VERB = 0
Let #MAX_SAT_ERWS = 0
Let #MAX_SAT_MSS = 0
Let #MAX_GRE_QUAN = 0
Let #MAX_GRE_VERB = 0
! 5 years
Let #MAX_SAT_WRIT = 0
Let #MAX_ACT_ENGL = 0
Let #MAX_ACT_MATH = 0
Let #Max_ACT_COMP = 0
! 2 years
Let #MAX_TOEFL_COMPI = 0
Let #MAX_TOEFL_COMPP = 0
Let #MAX_TOEFL_COMPC = 0
BEGIN-SELECT
MXT.TEST_COMPONENT,
MAX(MXT.SCORE) &MXT.Maxxy
if &MXT.TEST_COMPONENT = 'MATH'
Let #MAX_SAT_MATH = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'VERB'
Let #MAX_SAT_VERB = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'WRIT'
Let #MAX_SAT_WRIT = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'ERWS'
Let #MAX_SAT_ERWS = &MXT.Maxxy
end-if
if &MXT.TEST_COMPONENT = 'MSS'
Let #MAX_SAT_MSS = &MXT.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MXT
WHERE MXT.EMPLID = &STA.EMPLID
AND ( MXT.TEST_COMPONENT = 'MATH' or MXT.TEST_COMPONENT = 'VERB' or MXT.TEST_COMPONENT = 'WRIT' or MXT.TEST_COMPONENT = 'ERWS' or MXT.TEST_COMPONENT = 'MSS')
AND MXT.TEST_ID = 'SAT 1'
AND MXT.TEST_DT >= $FiveYearsAgo
group by MXT.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MOOS.TEST_COMPONENT,
MAX(MOOS.SCORE) &MOOS.Maxxy
if &MOOS.TEST_COMPONENT = 'MATH'
Let #MAX_ACT_MATH = &MOOS.Maxxy
end-if
if &MOOS.TEST_COMPONENT = 'ENGL'
Let #MAX_ACT_ENGL = &MOOS.Maxxy
end-if
if &MOOS.TEST_COMPONENT = 'COMP'
Let #MAX_ACT_COMP = &MOOS.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MOOS
WHERE MOOS.EMPLID = &STA.EMPLID
AND ( MOOS.TEST_COMPONENT = 'MATH' or MOOS.TEST_COMPONENT = 'ENGL' or MOOS.TEST_COMPONENT = 'COMP' )
AND MOOS.TEST_ID = 'ACT'
AND MOOS.TEST_DT >= $FiveYearsAgo
group by MOOS.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MEEC.TEST_COMPONENT,
MAX(MEEC.SCORE) &MEEC.Maxxy
if &MEEC.TEST_COMPONENT = 'COMPI'
Let #MAX_TOEFL_COMPI = &MEEC.Maxxy
end-if
if &MEEC.TEST_COMPONENT = 'COMPP'
Let #MAX_TOEFL_COMPP = &MEEC.Maxxy
end-if
if &MEEC.TEST_COMPONENT = 'COMPC'
Let #MAX_TOEFL_COMPC = &MEEC.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MEEC
WHERE MEEC.EMPLID = &STA.EMPLID
AND ( MEEC.TEST_COMPONENT = 'COMPI' or MEEC.TEST_COMPONENT = 'COMPP' or MEEC.TEST_COMPONENT = 'COMPC')
AND MEEC.TEST_ID = 'TOEFL'
AND MEEC.TEST_DT >= $TwoYearsAgo
group by MEEC.TEST_COMPONENT
END-SELECT
BEGIN-SELECT
MXV.TEST_COMPONENT,
MAX(MXV.SCORE) &MXV.Maxxy,
if &MXV.TEST_COMPONENT = 'QUAN'
Let #MAX_GRE_QUAN = &MXV.Maxxy
end-if
if &MXV.TEST_COMPONENT = 'VERB'
Let #MAX_GRE_VERB = &MXV.Maxxy
end-if
FROM PS_STDNT_TEST_COMP MXV
WHERE MXV.EMPLID = &STA.EMPLID
AND (MXV.TEST_COMPONENT = 'VERB' or MXV.TEST_COMPONENT = 'QUAN')
AND MXV.TEST_ID = 'GRE'
group by MXV.TEST_COMPONENT
END-SELECT
end-procedure
begin-procedure incremento
Let #Num_Testo = #Num_Testo + 1
Let #Num_Compy = 0
end-procedure
!***********************************************************************
! Get Advisor info
!***********************************************************************
Begin-Procedure Get_Advisor
Let $Advisor_id = ' '
Let $Advisor_Last_Name = ' '
Let $Advisor_First_Name = ' '
Let $Advisor_Middle_Name = ' '
Let $Advisor_Name_Suffix = ' '
Let #n_advisor_match = 0
BEGIN-SELECT
B.ADVISOR_ID,
B.COMMITTEE_ID
NVL(C.LAST_NAME,' ') &C.LAST_NAME,
NVL(C.FIRST_NAME,' ') &C.FIRST_NAME,
NVL(C.MIDDLE_NAME,' ') &C.MIDDLE_NAME,
NVL(C.NAME_SUFFIX,' ') &C.NAME_SUFFIX
! role of 'ADVR' is last one through ! REPORT-57 no longer true
Let $Advisor_id = &B.ADVISOR_ID
if $Advisor_id <= ' '
Let $Advisor_id = &B.COMMITTEE_ID
end-if
Let $Advisor_Last_Name = &C.LAST_NAME
Let $Advisor_First_Name = &C.FIRST_NAME
Let $Advisor_Middle_Name = &C.MIDDLE_NAME
Let $Advisor_Name_Suffix = &C.NAME_SUFFIX
if &Sta.emplid ='0415762' or &STA.EMPLID = '0704565' or &STA.EMPLID = '0017881'
show &Sta.emplid ' ' &Stucar.STRM ' ' $Advisor_id
end-if
FROM PS_STDNT_ADVR_HIST B, PS_NAMES C, PS_TERM_TBL CSTOP ! REPORT-57 TERM_TBL add
WHERE B.EMPLID = &STA.EMPLID
AND B.INSTITUTION = &STC.INSTITUTION
AND B.ACAD_CAREER = &STC.ACAD_CAREER
!AND B.ADVISOR_ID > ' ' ! REPORT-57
AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_ADVR_HIST B_ED ! REPORT-57 restore subqueries
WHERE B_ED.EMPLID = B.EMPLID
AND B_ED.INSTITUTION = B.INSTITUTION
! AND B_ED.ADVISOR_ROLE = B.ADVISOR_ROLE ! REPORT-57
! AND B_ED.STDNT_ADVISOR_NBR = B.STDNT_ADVISOR_NBR ! REPORT-57
! AND B_ED.EFFDT <= CSTOP.TERM_BEGIN_DT
AND B_ED.EFFDT <= CSTOP.TERM_END_DT ! REPORT-195
)
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_N.ADVISOR_ROLE = B.ADVISOR_ROLE ! REPORT-57
)
AND B.ADVISOR_ID = C.EMPLID(+)
AND B.INSTITUTION = CSTOP.INSTITUTION
AND B.ACAD_CAREER = CSTOP.ACAD_CAREER
AND CSTOP.STRM = &STUCAR.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)
! order by decode (B.Advisor_Role,'ADVR','9','0'), ! REPORT-57 remove the order by
! case when b.acad_prog = &STC.ACAD_PROG then '9' else '0' end,
! case when b.acad_plan = $Acad_Plan then '9' else '0' end,
! b.effdt
END-SELECT
end-procedure
!***********************************************************************
! Sql insert rows
!***********************************************************************
Begin-Procedure Insert_Rows
Let $Calling_Procedure = 'Insert_Rows'
let $Fubar_Date = &STC.EFFDT
BEGIN-SQL ON-ERROR=SQL-Recover
INSERT INTO SYSADM.PS_UM_STUD_EXT_BLD
VALUES
(&STUCAR.EMPLID,
&STA.LAST_NAME,
&STA.FIRST_NAME,
&STA.MIDDLE_NAME,
&STA.NAME_PREFIX,
&STA.NAME_SUFFIX,
&STA.NAME,
$Previous_Name,
$address1,
$address2,
$address3,
$address4,
$city,
$state,
$State_Descr,
$postal,
$county,
$country,
$Country_Descr,
$Email_Addr,
$Phone,
&STA.SEX,
&STA.BIRTHDATE,
&STA.BIRTHPLACE,
&STA.BIRTHSTATE,
&STA.BIRTHCOUNTRY,
$Nid_Usa,
$Nid_Can,
$Ethnic_Grp_Cd,
$Ethnic_Category,
$Ethnic_Descr,
&STA.MAR_STATUS,
CASE WHEN &STA.MAR_STATUS_DT < TO_DATE('01-JAN-1901','DD-MON-YYYY') OR &STA.MAR_STATUS_DT IS NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STA.MAR_STATUS_DT END,
CASE WHEN &STA.DT_OF_DEATH < TO_DATE('01-JAN-1901','DD-MON-YYYY') OR &STA.DT_OF_DEATH is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STA.DT_OF_DEATH END,
$Country_Usa,
$Citizenship_Status_Usa,
$Country_Oth,
$Citizenship_Status_Oth,
$Effective_Term,
NVL(&STC.ADMIT_TERM,'0000'),
$Residency,
CASE WHEN $Residency_Dt < TO_DATE('01-JAN-1901','DD-MON-YYYY') OR $Residency_dt is null THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE $Residency_Dt END,
$Admission_Res,
$Admission_Excpt,
&STUCAR.ACAD_CAREER,
&STUCAR.STDNT_CAR_NBR,
nvl(&STC.ADM_APPL_NBR,' '),
&STUCAR.INSTITUTION,
$Institution_Descr,
NVL(&STC.ACAD_PROG,' '),
NVL(&STC.PROG_STATUS,' '),
NVL(&STC.PROG_ACTION,' '),
CASE WHEN &STC.ACTION_DT < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STC.ACTION_DT is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STC.ACTION_DT END,
NVL(&STC.PROG_REASON,' '),
NVL(&STC.EXP_GRAD_TERM,'0000'),
NVL(&STC.REQ_TERM,'0000'),
NVL(&STC.ACAD_LOAD_APPR,' '),
NVL(&STC.COMPLETION_TERM,'0000'),
NVL(&STC.ACAD_PROG_DUAL,' '),
NVL(&STC.JOINT_PROG_APPR,' '),
$Acad_Prog_Descr,
$Acad_Plan,
CASE WHEN $Declare_dt < TO_DATE('01-JAN-1901','DD-MON-YYYY') or $Declare_dt is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE $Declare_dt END,
#Plan_sequence,
$STDNT_DEGR,
$Degr_chkout_stat,
$Advis_status,
&STUCAR.STRM,
CASE WHEN &STUCAR.REG_CARD_DATE < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.REG_CARD_DATE is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.REG_CARD_DATE END,
&STUCAR.WITHDRAW_CODE,
&STUCAR.WITHDRAW_REASON,
CASE WHEN &STUCAR.WITHDRAW_DATE < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.WITHDRAW_DATE is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.WITHDRAW_DATE END,
CASE WHEN &STUCAR.LAST_DATE_ATTENDED < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.LAST_DATE_ATTENDED is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.LAST_DATE_ATTENDED END,
&STUCAR.ACAD_PROG_PRIMARY,
&STUCAR.NSLDS_LOAN_YEAR,
&STUCAR.OVRD_ACAD_LVL_ALL,
&STUCAR.OVRD_ACAD_LVL_PROJ,
&STUCAR.ELIG_TO_ENROLL,
&STUCAR.OVRD_MAX_UNITS,
&STUCAR.MAX_TOTAL_UNIT,
&STUCAR.MAX_NOGPA_UNIT,
&STUCAR.MAX_AUDIT_UNIT,
&STUCAR.MAX_WAIT_UNIT,
&STUCAR.MIN_TOTAL_UNIT,
&STUCAR.OVRD_BILL_UNITS,
&STUCAR.PROJ_BILL_UNT,
&STUCAR.UNT_TAKEN_GPA,
&STUCAR.UNT_PASSD_GPA,
&STUCAR.UNT_TAKEN_NOGPA,
&STUCAR.UNT_PASSD_NOGPA,
&STUCAR.UNT_INPROG_GPA,
&STUCAR.UNT_INPROG_NOGPA,
&STUCAR.UNT_AUDIT,
&STUCAR.TRF_TAKEN_GPA,
&STUCAR.TRF_TAKEN_NOGPA,
&STUCAR.TRF_PASSED_GPA,
&STUCAR.TRF_PASSED_NOGPA,
&STUCAR.TRF_GRADE_POINTS,
&STUCAR.UNT_TEST_CREDIT,
&STUCAR.UNT_OTHER,
&STUCAR.UNT_TAKEN_FA,
&STUCAR.UNT_PASSD_FA,
&STUCAR.UNT_TAKEN_FA_GPA,
&STUCAR.GRADE_POINTS_FA,
&STUCAR.RESET_CUM_STATS,
&STUCAR.TOT_TAKEN_PRGRSS,
&STUCAR.TOT_TAKEN_GPA,
&STUCAR.TOT_PASSD_GPA,
&STUCAR.TOT_TAKEN_NOGPA,
&STUCAR.TOT_PASSD_NOGPA,
&STUCAR.TOT_INPROG_GPA,
&STUCAR.TOT_INPROG_NOGPA,
&STUCAR.TOT_AUDIT,
&STUCAR.TOT_TRNSFR,
&STUCAR.TOT_TEST_CREDIT,
&STUCAR.TOT_OTHER,
&STUCAR.TOT_CUMULATIVE,
&STUCAR.TOT_TAKEN_FA,
&STUCAR.TOT_PASSD_FA,
&STUCAR.TOT_TAKEN_FA_GPA,
&STUCAR.TOT_GRD_POINTS_FA,
&STUCAR.FORM_OF_STUDY,
&STUCAR.TERM_TYPE,
&STUCAR.CLASS_RANK_NBR,
&STUCAR.CLASS_RANK_TOT,
&STUCAR.SEL_GROUP,
&STUCAR.TUIT_CALC_REQ,
&STUCAR.TUIT_CALC_DTTM,
&STUCAR.FA_STATS_CALC_REQ,
&STUCAR.FA_STATS_CALC_DTTM,
&STUCAR.FA_ELIGIBILITY,
&STUCAR.BILLING_CAREER,
&STUCAR.UNIT_MULTIPLIER,
&STUCAR.ACAD_YEAR,
&STUCAR.ACAD_GROUP_ADVIS,
&STUCAR.CUR_RESIDENT_TERMS ,
&STUCAR.TRF_RESIDENT_TERMS ,
&STUCAR.CUM_RESIDENT_TERMS ,
&STUCAR.REFUND_PCT ,
&STUCAR.REFUND_SCHEME ,
&STUCAR.PRO_RATA_ELIGIBLE ,
CASE WHEN &stucar.fully_enrl_dt < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &stucar.fully_enrl_dt is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &stucar.fully_enrl_dt END,
CASE WHEN &STUCAR.ENRL_ON_TRANS_DT < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.ENRL_ON_TRANS_DT is null THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.ENRL_ON_TRANS_DT END,
CASE WHEN &STUCAR.STATS_ON_TRANS_DT < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.STATS_ON_TRANS_DT is null THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.STATS_ON_TRANS_DT END,
CASE WHEN &STUCAR.FULLY_GRADED_DT < TO_DATE('01-JAN-1901','DD-MON-YYYY') or &STUCAR.FULLY_GRADED_DT is null THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE &STUCAR.FULLY_GRADED_DT END,
&STUCAR.STUDY_AGREEMENT ,
&STUCAR.START_DATE,
&STUCAR.END_DATE,
&STUCAR.MAX_CRSE_COUNT ,
&STUCAR.REGISTERED ,
&STUCAR.OVRD_TUIT_GROUP ,
&STUCAR.OVRD_WDRW_SCHED ,
&STUCAR.TUITION_RES_TERMS ,
&STUCAR.OVRD_INIT_ADD_FEE ,
&STUCAR.OVRD_INIT_ENR_FEE ,
&STUCAR.TC_UNITS_ADJUST ,
&STUCAR.LOCK_IN_AMT ,
&STUCAR.LOCK_IN_DT,
&STUCAR.ACAD_CAREER_FIRST,
&STUCAR.ACADEMIC_LOAD_DT,
&STUCAR.UNTPRG_CHG_NSLC_DT,
&STUCAR.SSR_ACTIVATION_DT,
$Acad_Plan_Descr,
$Acad_Plan_Type,
$Academic_Group,
$Academic_Group_Descr,
$Degree,
$Acad_Sub_Plan,
$Acad_SubPlan_Descr,
$Acad_SubPlan_Type,
$Response_Reason,
$Response_Reason_Descr,
CASE WHEN $Response_Dt < TO_DATE('01-JAN-1901','DD-MON-YYYY') or $Response_Dt is NULL THEN TO_DATE('01-JAN-1901','DD-MON-YYYY') ELSE $Response_Dt END,
$Ext_Org_Id,
$Ext_Org_Id_Descr,
#Max_SAT_Math, #Max_SAT_Verb,
#Max_GRE_QUAN, #Max_GRE_VERB,
$Last_Sch_Descr, $Last_Sch_Type,
#Max_SAT_WRIT, #Max_ACT_MATH, #Max_ACT_ENGL, #MAX_ACT_COMP, #MAX_TOEFL_COMPI, #MAX_TOEFL_COMPP, #MAX_TOEFL_COMPC,
$Min_Acad_prog, $Min_Acad_Plan,
$Min_Acad_Sub_Plan ,NVL(&STC.CAMPUS,' '), &Stucar.unt_term_tot, &stucar.grade_points, &Stucar.cur_gpa, &stucar.cum_gpa,
&stucar.acad_level_bot, &stucar.acad_level_eot, &stucar.acad_level_proj, $Acad_Level_bot_descr,
&stucar.academic_load, &stucar.fa_load,
&Stucar.unt_taken_prgrss, &stucar.unt_passd_prgrss,
&stucar.unt_trnsfr, &stucar.tot_passd_prgrss, &stucar.tot_grade_points, $Row_check,
#Tuition, #Fees ,#Web_Hrs, #Dist_Hrs, #Itv_Hrs, $ADM_APPL_CTR, $ADMIT_TYPE,
$UMS01_FLAG,$UMS02_FLAG,$UMS03_FLAG,$UMS04_FLAG,$UMS05_FLAG,$UMS06_FLAG,$UMS07_FLAG,
$Level_Load_Rule,$Advisor_id,$Advisor_Last_Name,$Advisor_First_Name,$Advisor_Middle_Name,$Advisor_Name_Suffix
,$Education_Lvl, #UMS01_UNT_TAKEN_PRGRSS, #UMS02_UNT_TAKEN_PRGRSS,#UMS03_UNT_TAKEN_PRGRSS,#UMS04_UNT_TAKEN_PRGRSS,
#UMS05_UNT_TAKEN_PRGRSS, #UMS06_UNT_TAKEN_PRGRSS, #UMS07_UNT_TAKEN_PRGRSS,
$Acad_Prog_Primary_Descr, &PIN.PIN_NUM, &PIN.VALIDATED, &PIN.VALIDATED_DT,
#um_unt_taken_prgrss_rowc, $Hom_Email_Addr, $UM_ACAD_LOAD_RC, $Orig_Acad_Prog, $Orig_Acad_Plan,
$Ethnic_group, $Hisp_latino, $Multi_Race, $UM_ETH_GRP_DESCR, &APX.GRADUATION_DT
,#units_this_career,#MAX_SAT_ERWS, #MAX_SAT_MSS)
END-SQL
! if #rec_mark = 0
! begin-sql
! commit
! end-sql
! end-if
End-Procedure
!***********************************************************************
! Procedure Get Rows
! *
!***********************************************************************
Begin-Procedure GET_ROWS
Let $Calling_Procedure = 'GET_ROWS'
Let #APPLES1 = 0
Let #APPLES2 = 0
Let #APPLES3 = 0
Let #APPLES4 = 0
Let #APPLES5 = 0
Let #APPLES6 = 0
Let #APPLES7 = 0
Let #APP_COUNT = 0
Begin-Select
S.EMPLID,
S.LAST_NAME,
S.FIRST_NAME,
S.Email_Addr,
S.SEX,
S.BIRTHDATE,
S.Ethnic_Grp_Cd,
S.Ethnic_Category,
S.UM_Ethnic_Descr,
S.UM_Country_Usa,
S.Citizenship_Status,
S.Residency,
S.ACAD_CAREER,
S.STDNT_CAR_NBR,
S.INSTITUTION,
S.ACAD_PROG,
S.Acad_Prog_Descr,
S.Acad_Plan,
S.UM_Acad_Plan_Descr
Let #APP_COUNT = #APP_COUNT + 1
evaluate &S.INSTITUTION
when = 'UMS01'
Let #APPLES1 = #APPLES1 + 1
break
when = 'UMS02'
Let #APPLES2 = #APPLES2 + 1
break
when = 'UMS03'
Let #APPLES3 = #APPLES3 + 1
break
when = 'UMS04'
Let #APPLES4 = #APPLES4 + 1
break
when = 'UMS05'
Let #APPLES5 = #APPLES5 + 1
break
when = 'UMS06'
Let #APPLES6 = #APPLES6 + 1
break
when = 'UMS07'
Let #APPLES7 = #APPLES7 + 1
break
end-evaluate
! Print &S.INSTITUTION (+1,1) on-break print=never before=Before_INS
! Print &S.EMPLID (+1,1,20) bold shade
! Print &S.FIRST_NAME (,+2,25) bold shade
! Print &S.LAST_NAME (,+2,50) bold shade
! Print &S.SEX (,+2)
! Print &S.BIRTHDATE (,+2)
! Print &S.Ethnic_Grp_Cd (,+2)
! Print &S.ACAD_CAREER (+1,1)
! Print &S.ACAD_PROG (,+2)
! Print &S.ADMIT_TERM (,+2)
! Print &S.Acad_Plan (,+2)
! print '=================================================================================' (+1,1)
FROM PS_UM_STUD_EXTRACT S
where S.ADMIT_TERM >= [$Input_Trm1_M] and S.ADMIT_TERM <= [$Input_Term2_M]
ORDER by S.INSTITUTION, S.LAST_NAME, S.FIRST_NAME
End-Select
print '=================================================================================' (+1,1)
Print 'Students Selected:' (+1,1)
print '---------------------------------------------------------------------------------' (+1,1)
print ' UMS01 ' (+1,1) underline
print ' UMS02 ' (,12) underline
print ' UMS03 ' (,24) underline
print ' UMS04 ' (,36) underline
print ' UMS05 ' (,48) underline
print ' UMS06 ' (,60) underline
print ' UMS07 ' (,72) underline
print ' TOTAL ' (,84) underline
print #APPLES1 (+1,1) edit '9999,999'
print #APPLES2 (,12) edit '9999,999'
print #APPLES3 (,24) edit '9999,999'
print #APPLES4 (,36) edit '9999,999'
print #APPLES5 (,48) edit '9999,999'
print #APPLES6 (,60) edit '9999,999'
print #APPLES7 (,72) edit '9999,999'
print #APP_COUNT (,84) edit '9999,999'
print '=================================================================================' (+1,1)
End-Procedure ! Get_Rows
!***********************************************************************
! Long Institution Name
!
!***********************************************************************
Begin-Procedure Pretty_INS
evaluate &S.INSTITUTION
when = 'UMS01'
Let $INSTITUTION_NAME = 'University of Maine at Augusta'
break
when = 'UMS02'
Let $INSTITUTION_NAME = 'Univ of Maine at Farmington'
break
when = 'UMS03'
Let $INSTITUTION_NAME = 'Univ of Maine at Fort Kent'
break
when = 'UMS04'
Let $INSTITUTION_NAME = 'University of Maine at Machias'
break
when = 'UMS05'
Let $INSTITUTION_NAME = 'University of Maine'
break
when = 'UMS06'
Let $INSTITUTION_NAME = 'University of Southern Maine'
break
when = 'UMS07'
Let $INSTITUTION_NAME = 'Univ of Maine at Presque Isle'
break
when-other
Let $INSTITUTION_NAME = &S.INSTITUTION
end-evaluate
end-procedure
!***********************************************************************
! Rank Academic Career
!***********************************************************************
begin-procedure career_rank($strm, $institution, $emplid, $acad_career_in, :$funny_cars)
Let $funny_cars = 'N'
begin-select
caz.acad_career,
!rank() over (order by case when caz.fa_eligibility = 'Y' then 'X' else 'Z' end, decode(caz.acad_career,'LAW','1','GRAD','2','UGRD','3','4')) &Ranko
rank() over (order by decode(caz.acad_career,'LAW','1','GRAD','2','PBAC','3','UGRD','4','5')) &Ranko
if &caz.acad_career = $acad_career_in and &Ranko = 1
Let $funny_cars = 'Y'
end-if
from PS_STDNT_ENRL caz
where caz.strm = &_STUCAR.STRM
and caz.institution = &_STUCAR.INSTITUTION
and caz.emplid = &_STUCAR.EMPLID
!and caz.acad_career = $acad_career_in
and caz.unt_prgrss > 0
and caz.stdnt_enrl_status = 'E'
AND (CAZ.EARN_CREDIT = 'Y'
or (CAZ.UNITS_ATTEMPTED <> 'N' AND CAZ.AUDIT_GRADE_BASIS = 'N') and CAZ.ENRL_DROP_DT is null )
end-select
end-procedure
!***********************************************************************
! Institution Break
!
!***********************************************************************
Begin-Procedure Before_INS
do Pretty_ins
if $first_time = 'Y'
Let $First_time = 'N'
else
new-page
end-if
end-procedure
begin-procedure interval_commit
Let #rec_mark = mod(#STU_COUNT,500)
if #rec_mark = 0
show #stu_count ' records so far.'
begin-sql
commit
end-sql
end-if
end-procedure
!***********************************************************************
! Move data from um_stud_ext_bld to um_stud_extract
!***********************************************************************
begin-procedure Blasto_Data
Begin-select
count(*) &CTR_UM_BLD
FROM SYSADM.PS_UM_STUD_EXT_BLD buildies
end-select
if &CTR_UM_BLD > 0
if $input_Flag2 = 'Y'
BEGIN-SQL
TRUNCATE TABLE PS_UM_STUD_EXTRACT
END-SQL
else
begin-sql
DELETE FROM ps_um_stud_extract dxx where dxx.strm >= [$Input_Trm1_M] and dxx.strm <= [$Input_Term2_M]
end-sql
end-if
BEGIN-SQL
COMMIT
END-SQL
show 'Extract trimmed.'
BEGIN-SQL
INSERT INTO PS_UM_STUD_EXTRACT ( SELECT * FROM SYSADM.PS_UM_STUD_EXT_BLD)
END-SQL
BEGIN-SQL
COMMIT
END-SQL
show 'Rows inserted from UM_STUD_EXT_BLD.'
end-if
end-procedure
!***********************************************************************
! SQL-Recover Procedure
!***********************************************************************
Begin-Procedure SQL-Recover
show '$Calling_Procedure => ' $Calling_Procedure
display $SQL-Error
Do Extract_Control('3', 'PS_UM_STUD_EXTRACT','UMSTUEXT', $Control_now, $prcs_oprid, $prcs_run_cntl_id,
$input_date, $Input_Term, $Input_Term2, &R.FLAG1, &R.FLAG2, &R.FLAG3)
show 'Fields: ' &STUCAR.EMPLID ' '&STA.NAME ' ' &STUCAR.ACAD_CAREER ' ' &STUCAR.INSTITUTION
!show '1 ' &STUCAR.EMPLID ' ' &STA.LAST_NAME ' ' &STA.FIRST_NAME ' ' &STA.MIDDLE_NAME ' ' &STA.NAME
!show '2 '$Previous_Name ' ' &STA.ADDRESS1 ' ' &STA.ADDRESS2 ' ' &STA.ADDRESS3 ' ' &STA.ADDRESS4
!show '3 ' &STA.CITY ' ' &STA.STATE ' ' $State_Descr ' ' &STA.POSTAL ' ' &STA.COUNTY ' ' &STA.COUNTRY ' ' $Country_Descr
!show '4 ' $Email_Addr ' ' $Phone ' ' &STA.SEX
!show '5D ' &STA.BIRTHDATE
!show '6 ' &STA.BIRTHPLACE ' ' &STA.BIRTHSTATE ' ' &STA.BIRTHCOUNTRY ' ' $Nid_Usa ' ' $Nid_Can ' ' $Ethnic_Grp_Cd
!show '7 ' $Ethnic_Category ' ' $Ethnic_Descr ' ' &STA.MAR_STATUS
!show '8D >' &STA.MAR_STATUS_DT '<'
!show '9D >' &STA.DT_OF_DEATH '<'
!show '10 ' $Country_Usa ' ' $Citizenship_Status_Usa ' ' $Country_Oth ' ' $Citizenship_Status_Oth ' ' $Effective_Term ' '&STC.ADMIT_TERM
!show '11 ' $Residency
!show '12D >' $Residency_Dt '<'
show '13 ' $Admission_Res ' ' $Admission_Excpt ' ' &STUCAR.ACAD_CAREER ' ' &STUCAR.STDNT_CAR_NBR ' ' &STC.ADM_APPL_NBR
!show '14 ' &STUCAR.INSTITUTION ' ' $Institution_Descr ' ' &STC.ACAD_PROG ' ' &STC.PROG_STATUS ' ' &STC.PROG_ACTION
!show '15 ' &STC.ACTION_DT
!show '16 ' &STC.PROG_REASON ' ' &STC.EXP_GRAD_TERM ' ' &STC.REQ_TERM ' ' &STC.ACAD_LOAD_APPR ' ' &STC.COMPLETION_TERM ' ' &STC.ACAD_PROG_DUAL
!show '17 ' &STC.JOINT_PROG_APPR ' ' $Acad_Prog_Descr ' ' $Acad_Plan ' '
!show '18D >' $Declare_dt '<'
show '19 ' #Plan_sequence ' ' $STDNT_DEGR ' ' $Degr_chkout_stat ' ' $Advis_status ' ' &STUCAR.STRM ' '
!show '20D >' &STUCAR.REG_CARD_DATE '<'
!show '21 ' &STUCAR.WITHDRAW_CODE ' ' &STUCAR.WITHDRAW_REASON
!show '22D >' &STUCAR.WITHDRAW_DATE '<'
!show '23D >' &STUCAR.LAST_DATE_ATTENDED '<'
!show '24 ' &STUCAR.ACAD_PROG_PRIMARY ' ' &STUCAR.NSLDS_LOAN_YEAR ' ' &STUCAR.OVRD_ACAD_LVL_ALL ' ' &STUCAR.OVRD_ACAD_LVL_PROJ ' '&STUCAR.ELIG_TO_ENROLL
!show '25 ' &STUCAR.OVRD_MAX_UNITS ' ' &STUCAR.MAX_TOTAL_UNIT ' ' &STUCAR.MAX_NOGPA_UNIT ' ' &STUCAR.MAX_AUDIT_UNIT ' ' &STUCAR.MAX_WAIT_UNIT
!show '26 ' &STUCAR.MIN_TOTAL_UNIT ' ' &STUCAR.OVRD_BILL_UNITS ' ' &STUCAR.PROJ_BILL_UNT ' ' &STUCAR.UNT_TAKEN_GPA ' ' &STUCAR.UNT_PASSD_GPA
!show '27 ' &STUCAR.UNT_TAKEN_NOGPA ' ' &STUCAR.UNT_PASSD_NOGPA ' ' &STUCAR.UNT_INPROG_GPA ' ' &STUCAR.UNT_INPROG_NOGPA ' ' &STUCAR.UNT_AUDIT
!show '28 ' &STUCAR.TRF_TAKEN_GPA ' ' &STUCAR.TRF_TAKEN_NOGPA ' ' &STUCAR.TRF_PASSED_GPA ' ' &STUCAR.TRF_PASSED_NOGPA ' ' &STUCAR.TRF_GRADE_POINTS
!show '29 ' &STUCAR.UNT_TEST_CREDIT ' ' &STUCAR.UNT_OTHER ' ' &STUCAR.UNT_TAKEN_FA ' ' &STUCAR.UNT_PASSD_FA ' ' &STUCAR.UNT_TAKEN_FA_GPA ' ' &STUCAR.GRADE_POINTS_FA
!show '30 ' &STUCAR.RESET_CUM_STATS ' ' &STUCAR.TOT_TAKEN_PRGRSS ' ' &STUCAR.TOT_TAKEN_GPA ' ' &STUCAR.TOT_PASSD_GPA ' ' &STUCAR.TOT_TAKEN_NOGPA
!show '31 ' &STUCAR.TOT_PASSD_NOGPA ' ' &STUCAR.TOT_INPROG_GPA ' ' &STUCAR.TOT_INPROG_NOGPA ' ' &STUCAR.TOT_AUDIT ' ' &STUCAR.TOT_TRNSFR ' ' &STUCAR.TOT_TEST_CREDIT
!show '32 ' &STUCAR.TOT_OTHER ' ' &STUCAR.TOT_CUMULATIVE ' ' &STUCAR.TOT_TAKEN_FA ' ' &STUCAR.TOT_PASSD_FA ' ' &STUCAR.TOT_TAKEN_FA_GPA ' ' &STUCAR.TOT_GRD_POINTS_FA
!show '33 ' &STUCAR.FORM_OF_STUDY ' ' &STUCAR.TERM_TYPE ' ' &STUCAR.CLASS_RANK_NBR ' ' &STUCAR.CLASS_RANK_TOT ' ' &STUCAR.SEL_GROUP ' ' &STUCAR.TUIT_CALC_REQ
!show '34DT >' &STUCAR.TUIT_CALC_DTTM '<'
!show '35 ' &STUCAR.FA_STATS_CALC_REQ
!show '36DT >' &STUCAR.FA_STATS_CALC_DTTM '<'
!show '37 ' &STUCAR.FA_ELIGIBILITY ' ' &STUCAR.BILLING_CAREER ' ' &STUCAR.UNIT_MULTIPLIER ' ' &STUCAR.ACAD_YEAR ' ' &STUCAR.ACAD_GROUP_ADVIS
!show '38 ' &STUCAR.CUR_RESIDENT_TERMS ' ' &STUCAR.TRF_RESIDENT_TERMS ' ' &STUCAR.CUM_RESIDENT_TERMS ' ' &STUCAR.REFUND_PCT ' ' &STUCAR.REFUND_SCHEME
!show '39 ' &STUCAR.PRO_RATA_ELIGIBLE
!show '40D >' &stucar.fully_enrl_dt
!show '41D >' &STUCAR.ENRL_ON_TRANS_DT '<'
!show '42D >' &STUCAR.STATS_ON_TRANS_DT '<'
!show '43D >' &STUCAR.FULLY_GRADED_DT '<'
!show '44 ' &STUCAR.STUDY_AGREEMENT
!show '45D >' &STUCAR.START_DATE '<'
!show '46D >' &STUCAR.END_DATE '<'
!show '47 ' &STUCAR.MAX_CRSE_COUNT ' ' &STUCAR.REGISTERED ' ' &STUCAR.OVRD_TUIT_GROUP ' ' &STUCAR.OVRD_WDRW_SCHED ' ' &STUCAR.TUITION_RES_TERMS
!show '48 ' &STUCAR.OVRD_INIT_ADD_FEE ' ' &STUCAR.OVRD_INIT_ENR_FEE ' ' &STUCAR.TC_UNITS_ADJUST ' ' &STUCAR.LOCK_IN_AMT ' '
!show '49 ' &STUCAR.LOCK_IN_DT
!show '50 ' &STUCAR.ACAD_CAREER_FIRST
!show '51D >' &STUCAR.ACADEMIC_LOAD_DT '<'
!show '52D >' &STUCAR.UNTPRG_CHG_NSLC_DT '<'
!show '53D >' &STUCAR.SSR_ACTIVATION_DT '<'
!show '54 ' $Acad_Plan_Descr ' ' $Acad_Plan_Type ' ' $Academic_Group ' ' $Academic_Group_Descr ' ' $Degree ' ' $Acad_Sub_Plan
!show '55 ' $Acad_SubPlan_Descr ' ' $Acad_SubPlan_Type ' ' $Response_Reason ' ' $Response_Reason_Descr ' '
!show '56D >' $Response_Dt '<'
!show '57 ' $Ext_Org_Id ' ' $Ext_Org_Id_Descr ' ' #Max_SAT_Math ' ' #Max_SAT_Verb ' ' #Max_GRE_QUAN ' ' #Max_GRE_VERB ' ' $Last_Sch_Descr
!show '58 ' $Last_Sch_Type ' ' #Max_SAT_WRIT ' ' #Max_ACT_MATH ' ' #Max_ACT_ENGL ' ' #MAX_ACT_COMP ' ' #MAX_TOEFL_COMPI ' ' #MAX_TOEFL_COMPP
!show '59 ' #MAX_TOEFL_COMPC ' ' $Min_Acad_prog ' ' $Min_Acad_Plan ' ' $Min_Acad_Sub_Plan ' ' &STC.CAMPUS
!show '60 ' &Stucar.unt_term_tot ' ' &stucar.grade_points ' ' &Stucar.cur_gpa ' ' &stucar.cum_gpa
!show '61 ' &stucar.acad_level_bot ' ' &stucar.acad_level_eot ' ' &stucar.acad_level_proj ' ' $Acad_Level_bot_descr
!show '62 ' &stucar.academic_load ' ' &stucar.fa_load
!show '63 ' &stucar.unt_taken_prgrss ' ' &stucar.unt_passd_prgrss ' ' &stucar.unt_trnsfr ' ' &stucar.tot_passd_prgrss
!show '64 ' &stucar.tot_grade_points ' ' $Row_check,
!show '65 ' #Tuition ' ' #Fees ' '#Web_Hrs ' ' #Dist_Hrs ' ' $ADM_APPL_CTR ' ' $ADMIT_TYPE
!show '66 ' $UMS01_FLAG ' ' $UMS02_FLAG ' ' $UMS03_FLAG ' ' $UMS04_FLAG ' '$UMS05_FLAG ' '$UMS06_FLAG ' '$UMS07_FLAG
End-Procedure SQL-Recover
!***********************************************************************
!SQC Include Files *
!***********************************************************************
#include 'stdapi.sqc' !Update Process API
#include 'datetime.sqc' !Date and Time Functions
#include 'datemath.sqc' !Date Manipulation Routines
#include 'curdttim.sqc' !Get-Current-DateTime procedure
#include 'tranctrl.sqc' !Transaction Control
#include 'umextctl.sqc' !Extract Control