DARTS : UM_APPL_EXTRACT - Applicant Reporting Extract

!************************************************************************
!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   * 
!                                                                       *
! 14     PSA         04/30/14               Fix net confirm for defers  *
!                                           REPORT-170 PSA 04/14        *
! 15     JAJ         03/15/19               REPORT-543 Correct          *
!                                           Multi-Race for              *
!                                           Hispanic_Latino logic       *
!************************************************************************

#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'												! 6 = Not Specified
				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 $Hisp_Latino = 'Y'
		Let $Multi_Race = 'N'
	else
		if #Race_multi > 1
			Let $Multi_Race = 'Y'
		else
			Let $Multi_Race = 'N'
		end-if
	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