!************************************************************************
!umfaipds.SQR															*
!************************************************************************
! DESCRIPTION:  Financial Aid Ipeds Report                              *
! OWNER:    							                                *
! FREQUENCY:   Manual		                                        	*
!************************************************************************
! SPECIFICATIONS BY:  replicates  CRYSTAL Report UMIPED16          	   	*
!                     but with user entered aid years                   *
! DATE:                    					                            *
!************************************************************************
! CREATED BY:  Peter Andersen                                           *
! CREATION DT: December 20, 2017                                      	*
!************************************************************************
!                      MODIFICATION LOG				                    *
!************************************************************************
! Mod #        Programmer  Start Date  Desription                	    *
! -----------  ----------  ----------  ---------------------------------*
! REPORT-360      PSA      02/26/2018  Add CSV detail output            *
! REPORT-787      JAJ      01/21/2021  IPEDS Financial Aid report change*
!                                      due to CARES Act                 *
! REPORT-857      JAJ      02/03/2022  Correct column AH of csv         *
!************************************************************************

#include 'setenv.sqc'   ! Set environment

!************************************************************************
!        Begin Setup                                                    *
!************************************************************************
Begin-Setup
#include 'ptset02.sqc' 
#define DLim ';'

declare-variable
 date $input_Date
 text $Input_Term1
 text $input_Career
 text $Heading_string
 text $Table
 text $Main_Subquery
end-declare

begin-sql
ALTER SESSION SET CURRENT_SCHEMA = SYSADM
end-sql

BEGIN-SQL ON-ERROR=SKIP
DROP TABLE PS_UM_UMGROUP4
END-SQL

BEGIN-SQL
COMMIT
END-SQL

BEGIN-SQL ! ON-ERROR=SKIP
CREATE GLOBAL TEMPORARY TABLE PS_UM_UMGROUP4
	( FLAG CHAR(1),
	  EMPLID CHAR(7),
	  INSTITUTION CHAR(5)  )
	ON COMMIT PRESERVE ROWS 
END-SQL

End-Setup




!***********************************************************************
! Proc Options(Main);
!***********************************************************************
Begin-Program
   
 
   let $Reportid = 'umfaipds'
   Let $Reportid_upper = $Reportid
   
   uppercase $Reportid_upper
   
   do Stdapi-Init
   
   do Init-DateTime
   
   do Get-Current-DateTime
   
BEGIN-SELECT                                                             
DBNAME                                                                   
        Let $DB = &DBNAME                                                
FROM PSDBOWNER                                                           
END-SELECT     
  
   show 'Start: ' $AsOfNow
   !REPORT-362 PSA Descriptions for Housing_Code_1 are wrong for values '2' and '3'
   ! current PSXLATITEM values for um_house_cd_descr are
   !FIELDVALUE	XLATLONGNAME
   !1	On-Campus
   !2	Off-Campus
   !3	Parents

   Let $Parentz = 'Parents' ! Substitute this for 'Parents' should be housing_code_1 = 2
   Let $Off_CampusZ = 'Off-Campus' ! Substitute this for 'Off-Campus' should be housing_code_1 = 3
   Let $Summer_filter = '%Summer%' ! Original report filtered for B.ITEM_TYPE_DESCR NOT LIKE '%Summer%'
   
   ! disable $summer_filter for testing
   !Let $Summer_filter = 'BadWolf' ! This value will never be found
  
   !
   !  switch descrs for real to fix xlat values
   Let $Temp = $Parentz
   Let $Parentz = $off_campusZ
   Let $Off_campusZ = $Temp
 
   !
   if $prcs_process_instance = ''
    let $prcs_oprid = 'PANDERSEN'
    let $prcs_run_cntl_id = 'BATCH'
    do Select-Parameters
   else
    do Select-Parameters
   end-if
   Let $Reporttitle = $Last_year ||' - '||$Input_AidYear||' IPEDS Financial Aid Report'
   !
   !REPORT-360 PSA Standard CSV file setup
   !follows
   !
   if $prcs_process_instance = ''                                          
	  Let $out_dir = 'c:\temp\'
   else                                                    
     let $out_dir = getenv('PS_CFG_HOME') 
     let $out_dir = $out_dir || '/appserv/prcs/' || $DB         
     let $out_dir = $out_dir || '/log_output/SQR_' || $ReportID_upper || '_' ||$Prcs_Process_Instance || '/'  
   end-if
   let $out_file1 = 'IPD'||datetostr($input_date,'MMDDYY')||$Input_institution||'.CSV'         
   let $out_file1 = $out_dir || $out_file1                                
                                              
   show 'Write to : ' $out_file1
   open $out_file1 as 1 for-writing record=1300
 
   
   let $ListString1 = 
       'EMPLID,'||
       'CALC_FIRST_TIME,'||
       'CALC_FULL_PART,'||
       'CALC_DEGREE,'||
       'FIRST_TIME_FULL_TIME,'||
       'SUM_ACCEPT_OR_OFFER_AMOUNT,'||
       'DISBURSEMENT_PLAN,'||
       'FEDERAL_ID,'||
       'FA_SOURCE,'||
       'FIN_AID_TYPE,'|| 
       'STDNT_GROUP,'|| 
       'UM_HOUSE_CD_DESCR,'||
       'FISAP_TOT_INC,'||
       'I1Group1,'||
       'I2aGroup2,'||
       'I2bGroup2,'||
       'I3Group3,'||
       'I4Group4,'||
       'IB01_Num,'||
       'IB01_Amt,'||
       'IB02_Num3,'||
       'IB02_Amt,'||
       'IB03_Num,'||
       'IB03_Amt,'||
       'IC04a,'||
       'IC04b,'||
       'IC04c,'||
       'ICPage201,'||
       'ICPage202,'||
       'ICPage202a_Num,'||
       'ICPage202a_Amt,'||
       'ICPage202b_Num,'||
       'ICPage202b_Amt,'|| 
       'ICPage203_Num,'||
       'ICPage203_Amt,'|| 
       'ICPage204_Num,'||
       'ICPage204_Amt,'||  
       'ICPage205,'||
       'ICPage205a_Num,'||
       'ICPage205a_Amt,'|| 
       'ICPage205b_Num,'||
       'ICPage205b_Amt,'|| 
       'ID01,'||
       'ID02a,'|| 
       'ID02b,'||
       'ID02c,'||  
       'ID03,' 
       

       
    let $ListString1 = $ListString1 ||
        'IE01A,'||
       'IE01B,'||
       'IE01C,'||
       'I02ACOL1,'||
       'I02ACOL2,'||
       'I02ACOL3,'||
       'I02BCOL1,'||
       'I02BCOL2,'||
       'I02BCOL3,'||
       'I02CCOL1,'||
       'I02CCOL2,'||
       'I02CCOL3,'||  
       'I02DCOL1,'||
       'I02DCOL2,'||
       'I02DCOL3,'||  
       'I02ECOL1,'||
       'I02ECOL2,'||
       'I02ECOL3,'
       

  write 1 from $ListString1

  let $OutDtu_dt = ''

  do CONVERT-TO-DTU-DATE($input_date, $OutDtu_Dt)

  let $fn_month = substr($Outdtu_dt, 6, 2)
  let $fn_day = substr($OutDtu_dt, 9, 2)
  
  ! build group 4 table to replace in-line view because of SQR's tiny SQL buffer
  ! is similar to query UMS_AL_IPEDS_AY2016_Group_4 

  begin-sql ON-ERROR=SQL-Recover
  insert into PS_UM_UMGROUP4 (SELECT DISTINCT 'X' as FLAG, A.EMPLID, A.INSTITUTION  FROM PS_UM_STUD_CENSUS A, PS_UM_FIN_AID B, PS_STDNT_GRPS C, PS_STDNT_AWARDS D 
      WHERE ( A.STRM = ltrim(substr($input_aidyear,3,2),' ')||'10'  AND A.ROW_CHECK = 'Y' 
     AND CASE WHEN  A.ACAD_PROG_PRIMARY = 'NDUG' THEN 'Undergraduate' 
              WHEN  A.ACAD_PROG_PRIMARY = 'DIST' THEN 'Undergraduate' 
              WHEN  A.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Undergraduate' 
              WHEN  A.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Undergraduate' 
              WHEN  A.ACAD_PROG_PRIMARY = 'NDGR' THEN 'Graduate' 
              WHEN  A.ACAD_PROG_PRIMARY = 'NDPR' THEN 'Law' 
              WHEN  A.LEVEL_LOAD_RULE = 'UG1YR' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'UG2YR' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'UG4YR' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'UG' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'UGAS' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'UGA' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'AA' THEN 'Undergraduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'GR' THEN 'Graduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'GRAD' THEN 'Graduate' 
              WHEN  A.LEVEL_LOAD_RULE = 'LAW' THEN 'Law' 
              WHEN  A.ACAD_CAREER = 'UGRD' THEN 'Undergraduate' 
              WHEN  A.ACAD_CAREER = 'GRAD' THEN 'Graduate' 
              WHEN  A.ACAD_CAREER = 'LAW' THEN 'Law' 
              WHEN  A.ACAD_CAREER = 'PBAC' THEN 'Undergraduate' 
              ELSE 'Other' 
         END = 'Undergraduate' 
     AND A.EMPLID =  B.EMPLID(+) 
     AND A.ACAD_CAREER =  B.ACAD_CAREER(+) 
     AND A.INSTITUTION =  B.INSTITUTION(+) 
     AND  B.AID_YEAR(+) = $input_aidyear
     AND A.EMPLID =  C.EMPLID(+) 
     AND A.INSTITUTION =  C.INSTITUTION(+) 
     AND  C.STDNT_GROUP(+) = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_aidyear,3,2)) - 1 ,'99') ,' ')||'A'  
     AND B.EMPLID =  D.EMPLID(+) 
     AND B.AID_YEAR =  D.AID_YEAR(+) 
     AND B.INSTITUTION =  D.INSTITUTION(+) 
     AND B.ITEM_TYPE =  D.ITEM_TYPE(+) 
     AND B.ACAD_CAREER =  D.ACAD_CAREER(+) 
     AND A.RESIDENCY = 'IS' 
     AND CASE WHEN  B.FIN_AID_TYPE IS NULL THEN 0 
              WHEN  B.FIN_AID_TYPE = 'L' THEN  B.ACCEPT_AMOUNT 
              ELSE  B.OFFER_AMOUNT 
              END > 0 
     AND B.FEDERAL_ID IN ('PERK','PELL','SEOG','FWSP','STFS','STFU','ACG','SMRT','TCH') 
     AND ((A.INSTITUTION = 'UMS01' 
     AND D.DISBURSEMENT_PLAN NOT IN ('D5','DR','OR','RO')) 
     OR (A.INSTITUTION = 'UMS02' 
     AND D.DISBURSEMENT_PLAN NOT IN ('DR','RO')) 
     OR (A.INSTITUTION = 'UMS03' 
     AND D.DISBURSEMENT_PLAN NOT IN ('D2','DR','RO')) 
     OR (A.INSTITUTION = 'UMS04' 
     AND D.DISBURSEMENT_PLAN NOT IN ('D3','DR','RO')) 
     OR (A.INSTITUTION = 'UMS05' 
     AND D.DISBURSEMENT_PLAN NOT IN ('DR','RO','R1','R2','R3','RP')) 
     OR (A.INSTITUTION = 'UMS06' 
     AND D.DISBURSEMENT_PLAN NOT IN ('D2','DR','PX','R1','RO')) 
     OR (A.INSTITUTION = 'UMS07' 
     AND D.DISBURSEMENT_PLAN NOT IN ('DR','P2','RO'))) 
     AND B.ITEM_TYPE_DESCR NOT LIKE $Summer_filter 
     AND ((A.INSTITUTION <> 'UMS06' 
     AND CASE 
         WHEN  A.ACAD_LEVEL_BOT = 'GR' AND  A.UM_UNT_TAK_PRG_RC >= 6 THEN 'Full-Time' 
         WHEN  A.ACAD_LEVEL_BOT = 'MAS' AND  A.UM_UNT_TAK_PRG_RC >= 6 THEN 'Full-Time' 
         WHEN  A.ACAD_LEVEL_BOT = 'PHD' AND  A.UM_UNT_TAK_PRG_RC >= 6 THEN 'Full-Time' 
         WHEN  A.UM_UNT_TAK_PRG_RC >= 12 THEN 'Full-Time' 
         ELSE 'Part-Time' 
         END = 'Full-Time' 
     AND CASE 
         WHEN ( A.ADMIT_TERM = ltrim(substr($input_aidyear,3,2),' ')||'10'  or  A.ADMIT_TERM = ltrim(to_char(to_number(substr($input_aidyear,3,2)) - 1,'99'),' ')||'30') AND 
        ( A.ADMIT_TYPE = 'FYR' OR  A.ADMIT_TYPE = 'EFY') AND 
        ( A.UM_LAST_SCH_TYPE = 'SCD' OR  A.UM_LAST_SCH_TYPE = 'SHS' OR  A.ACAD_LEVEL_BOT = '10' OR  A.ACAD_LEVEL_BOT = '01') AND  A.PROG_REASON <> 'RADM' THEN 'Y' 
        ELSE 'N' 
        END = 'Y' 
     AND CASE 
         WHEN  A.FA_ELIGIBILITY = 'Y' THEN 'Degree' 
         WHEN  A.FA_ELIGIBILITY = 'N'  and  A.INSTITUTION = 'UMS01' and   A.ACAD_PROG_PRIMARY = 'BSAVI' THEN 'Degree' 
         ELSE   'Non-Degree' 
         END = 'Degree') 
     OR ( A.INSTITUTION = 'UMS06' 
     AND C.STDNT_GROUP = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_aidyear,3,2)) - 1,'99'),' ')||'A')) )
     AND A.INSTITUTION = $input_institution  )
end-sql
  
  do Count_Students

  do Get-Current-DateTime

  show 'End: ' $asOfNow

  do Stdapi-Term

End-Program

!***********************************************************************
!  Page Heading   1                                    
!***********************************************************************
Begin-Heading 11
#include 'umstdhdg.sqc'
	print $INSTITUTION_NAME (-1,1) center bold 


 print ' ' (+3,1)
   graphic (+1,1,180) horz-line 3
 !  graphic (+4,1,115) box 1 0 10

   
End-Heading




!***********************************************************************
!  Procedure  Count Students                                           *
!                                                                      *                    
! The COUNTS and SUMS are very close to the logic of the fields in the *
! original CRYSTAL reports.  The CRYSTAL reports used PSQUERIES which  *
! are approximated here by PS_UM_UMGROUP4 and an in-line view          *
!                                                                      *                    
!***********************************************************************

Begin-Procedure Count_Students

	Let #STU_COUNT = 0	
	Let #Group_1 = 0
	Let #Group_1_SUM = 0
	Let #Group_2 = 0
	Let #Group_2_SUM = 0
	Let #Group2a = 0                        
	Let #Group2a_SUM = 0
	Let #Group2b = 0
	Let #Group2b_SUM = 0
	Let #Group3 = 0
	Let #Group3_SUM = 0  
	Let #Group4 = 0
	Let #Group4_SUM = 0
	Let #PARTB_01_Number = 0
	Let #PARTB_01_Number_SUM = 0
	Let #PARTB_01_Amount = 0
	Let #PARTB_01_Amount_SUM = 0
	Let #PARTB_02_Number = 0
	Let #PARTB_02_Number_SUM = 0
	Let #PARTB_03_Number = 0
	Let #PARTB_03_Number_SUM = 0
	Let #PARTB_03_Amount = 0
	Let #PARTB_03_Amount_SUM = 0
	Let #PARTC_04a = 0
	Let #PARTC_04a_SUM = 0
	Let #PARTC_04b = 0 
	Let #PARTC_04b_SUM = 0 
	Let #PARTC_04c = 0
	Let #PARTC_04c_SUM = 0
	Let #PARTC_01 = 0
	Let #PARTC_01_SUM = 0
	Let #PARTC_02 = 0
	Let #PARTC_02_SUM = 0
	Let #PARTC_02a_Number = 0
	Let #PARTC_02a_Number_SUM = 0
	Let #PARTC_02a_Amount = 0
	Let #PARTC_02a_Amount_SUM = 0
	Let #PARTC_02b_Number = 0
	Let #PARTC_02b_Number_SUM = 0
	Let #PARTC_02b_Amount = 0
	Let #PARTC_02b_Amount_SUM = 0
	Let #PARTC_03_Number = 0
	Let #PARTC_03_Number_SUM = 0
	Let #PARTC_03_Amount = 0
	Let #PARTC_03_Amount_SUM = 0
	Let #PARTC_04_Number = 0
	Let #PARTC_04_Number_SUM = 0
	Let #PARTC_04_Amount = 0
	Let #PARTC_04_Amount_SUM = 0
	Let #PARTC_05_Number = 0
	Let #PARTC_05_Number_SUM = 0
	Let #PARTC_05a_Number = 0
	Let #PARTC_05a_Number_SUM = 0
	Let #PARTC_05a_Amount = 0
	Let #PARTC_05a_Amount_SUM = 0
	Let #PARTC_05b_Number = 0
	Let #PARTC_05b_Number_SUM = 0
	Let #PARTC_05b_Amount = 0
	Let #PARTC_05b_Amount_SUM = 0
	Let #PARTD_02a = 0
	Let #PARTD_02a_SUM = 0
	Let #PARTD_02b = 0
	Let #PARTD_02b_SUM = 0
	Let #PARTD_02c = 0
	Let #PARTD_02c_SUM = 0 
	Let #PARTD_03 = 0
	Let #PARTD_03_SUM = 0
	
	Let #PARTE_1a =  0
	Let #PARTE_1a_SUM = 0
	Let #PARTE_1b = 0
	Let #PARTE_1b_SUM = 0
	Let #PARTE_1c = 0
	Let #PARTE_1c_SUM = 0

	Let #PARTE_2a_Col1 = 0
	Let #PARTE_2a_Col1_SUM = 0
	Let #PARTE_2a_Col2 = 0
	Let #PARTE_2a_Col2_SUM = 0
	Let #PARTE_2a_Col3 = 0
	Let #PARTE_2a_Col3_SUM = 0

	Let #PARTE_2b_Col1 = 0
	Let #PARTE_2b_Col1_SUM = 0
	Let #PARTE_2b_Col2 = 0
	Let #PARTE_2b_Col2_SUM = 0
	Let #PARTE_2b_Col3 = 0
	Let #PARTE_2b_Col3_SUM = 0
	
	Let #PARTE_2a_Col1 = 0
	Let #PARTE_2a_Col1_SUM = 0
	Let #PARTE_2a_Col2 = 0
	Let #PARTE_2a_Col2_SUM = 0
	Let #PARTE_2a_Col3 = 0
	Let #PARTE_2a_Col3_SUM = 0
	
	Let #PARTE_2c_Col1 = 0
	Let #PARTE_2c_Col1_SUM = 0
	Let #PARTE_2c_Col2 = 0
	Let #PARTE_2c_Col2_SUM = 0
	Let #PARTE_2c_Col3 = 0
	Let #PARTE_2c_Col3_SUM = 0
	
	Let #PARTE_2d_Col1 = 0
	Let #PARTE_2d_Col1_SUM = 0
	Let #PARTE_2d_Col2 = 0
	Let #PARTE_2d_Col2_SUM = 0
	Let #PARTE_2d_Col3 = 0
	Let #PARTE_2d_Col3_SUM = 0
   
	Let #PARTE_2e_Col1 = 0
	Let #PARTE_2e_Col1_SUM = 0
	Let #PARTE_2e_Col2 = 0
	Let #PARTE_2e_Col2_SUM = 0
	Let #PARTE_2e_Col3 = 0
	Let #PARTE_2e_Col3_SUM = 0
 
BEGIN-SELECT 
A.EMPLID,
A.CALC_FIRST_TIME,
A.CALC_FULL_PART,
A.CALC_DEGREE,
A.FIRST_TIME_FULL_TIME,
SUM(A.CALC_ACCEPT_OR_OFFER_AMOUNT) &SUM_ACCEPT_OR_OFFER_AMOUNT,
RTRIM((listagg(A.DISBURSEMENT_PLAN,'/') WITHIN GROUP (ORDER BY A.FEDERAL_ID)), '/') &DISBURSEMENT_PLAN,
RTRIM((listagg(A.FEDERAL_ID,'/') WITHIN GROUP (ORDER BY A.FEDERAL_ID)), '/') &FEDERAL_ID,
RTRIM((listagg(A.FA_SOURCE,'/') WITHIN GROUP (ORDER BY A.FEDERAL_ID)), '/') &FA_SOURCE,
RTRIM((listagg(A.FIN_AID_TYPE,'/') WITHIN GROUP (ORDER BY A.FEDERAL_ID)), '/') &FIN_AID_TYPE,
RTRIM((listagg(A.STDNT_GROUP,'/') WITHIN GROUP (ORDER BY A.FEDERAL_ID)), '/') &STDNT_GROUP,
RTRIM(REGEXP_REPLACE((listagg(A.UM_HOUSE_CD_DESCR,'/') WITHIN GROUP (ORDER BY A.UM_HOUSE_CD_DESCR)), '([^/]*)(/\1)+($|/)', '\1\3'), '/') &UM_HOUSE_CD_DESCR,
A.RESIDENCY,
SUM(A.TOTAL_INCOME) &TOTAL_INCOME,
COUNT(DISTINCT a.emplid) &Group_1 ,
COUNT(DISTINCT CASE WHEN (a.institution <> 'UMS06' 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.CALC_Degree = 'Degree') 
                      OR a.First_time_full_time = 'Y' THEN a.emplid ELSE NULL END ) &Group2, 
COUNT(DISTINCT CASE WHEN (a.institution <> 'UMS06' 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.CALC_Degree = 'Degree' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0
                     and a.disbursement_plan > ' '
                     AND a.federal_id <> 'PLUS' ) 
                      OR ( a.institution = 'UMS06'
                     AND a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     AND a.federal_id <> 'PLUS') THEN a.emplid ELSE NULL END ) &Group2a,
COUNT(DISTINCT CASE WHEN ((a.institution <> 'UMS06' 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.CALC_Degree = 'Degree') 
                      OR a.First_time_full_time = 'Y' ) 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     AND a.federal_id <> 'PLUS' 
                     AND (( a.institution NOT IN ('UMS06', 'UMS02') 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U') 
                     AND a.fin_aid_type IN ('A', 'D', 'L', 'S', 'G', 'V')) 
                      OR (a.institution = 'UMS02' 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U') 
                     AND a.fin_aid_type IN ('A', 'L', 'S', 'G', 'V')) 
                      OR (a.institution = 'UMS06' 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U') 
                     AND a.fin_aid_type IN ('A', 'D', 'L', 'S', 'G', 'V', 'O')) ) THEN a.emplid ELSE NULL END ) &Group2b,
COUNT(DISTINCT CASE WHEN a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     AND a.residency = 'IS' 
                     AND (( a.institution NOT IN ('UMS06', 'UMS02') 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U')
                     AND a.fin_aid_type IN ('A', 'D', 'S', 'G', 'V')) 
                      OR ( a.institution = 'UMS02' 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U')
                     AND a.fin_aid_type IN ('A', 'S', 'G', 'V')) 
                      OR (a.institution = 'UMS06' 
                     AND a.stdnt_group = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear, 3, 2)) - 1, '99'), ' ') || 'A' 
                     AND a.fa_source IN ('F', 'S', 'N', 'I', 'U')
                     AND a.fin_aid_type IN ('A', 'D', 'S', 'G', 'V')) ) THEN a.emplid ELSE NULL END ) &Group3, 
COUNT(DISTINCT CASE WHEN ((a.institution <> 'UMS06' 
                     AND a.CALC_FIRST_TIME = 'Y' 
                     AND a.calc_full_part = 'Full-Time' 
                     AND a.CALC_Degree = 'Degree') 
                      OR a.First_time_full_time = 'Y' ) 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     AND a.residency = 'IS' 
                     AND FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') THEN a.emplid ELSE NULL END) &Group4,
COUNT(DISTINCT CASE WHEN (( a.institution <> 'UMS06' and a.institution <> 'UMS02'
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0
                     and a.disbursement_plan > ' '
                     AND a.fin_aid_type in ('A','D','S','V','G')) 
                      or (a.institution = 'UMS02'
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0
                     and a.disbursement_plan > ' '
                     AND a.fin_aid_type in ('A','S','V','G')) 
                      or (a.institution = 'UMS06'
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0
                     and a.disbursement_plan > ' '
                     AND a.fin_aid_type in ('A','D','S','V','G','O')))
                      or (A.ISF_AMT <> 0) THEN a.emplid else NULL END) &PARTB_01_Number, 
SUM(CASE WHEN (( a.institution <> 'UMS06' and a.institution <> 'UMS02' 
	      and a.disbursement_plan > ' '
          AND a.fin_aid_type in ('A','D','S','V','G'))
           or (a.institution = 'UMS02' and a.disbursement_plan > ' '
          AND a.fin_aid_type in ('A','S','V','G'))
           or (a.institution = 'UMS06' 
          and a.disbursement_plan > ' '
          AND a.fin_aid_type in ('A','D','S','V','G','O'))) THEN a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTB_01_Amount,
COUNT(DISTINCT CASE WHEN a.federal_id = 'PELL' 
	                 and a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
	                 and a.disbursement_plan > ' ' THEN a.emplid else NULL END) &PARTB_02_Number, 
SUM(CASE WHEN a.federal_id = 'PELL' 
	      and a.disbursement_plan > ' ' then a.CALC_ACCEPT_OR_OFFER_AMOUNT  else 0 END) &PARTB_02_Amount,
COUNT(DISTINCT CASE WHEN A.FIN_AID_TYPE = 'L' 
	                 and A.FA_SOURCE IN ('F','N') 
	                 AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
	                 and a.disbursement_plan > ' '
                     AND A.FEDERAL_ID <> 'PLUS' then A.EMPLID else NULL end ) &PARTB_03_NUMBER,
SUM(CASE WHEN A.FIN_AID_TYPE = 'L' 
	      and A.FA_SOURCE IN ('F','N')  
	      AND A.FEDERAL_ID <> 'PLUS' 
	      and a.disbursement_plan > ' ' then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 end ) &PARTB_03_AMOUNT,
COUNT(DISTINCT CASE WHEN A.RESIDENCY = 'ID' THEN A.EMPLID ELSE NULL END) &PARTC_04a,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     and A.RESIDENCY = 'IS' )
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     and A.RESIDENCY = 'IS') THEN A.EMPLID ELSE NULL END ) &PARTC_04b,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     and A.RESIDENCY <> 'IS' )
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     and A.RESIDENCY <> 'IS' ) THEN A.EMPLID ELSE NULL END ) &PARTC_04c,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 AND A.INSTITUTION <> 'UMS02' 
	                 and A.CALC_Degree = 'Degree' 
	                 and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
                     and A.FA_SOURCE in ('F','S','I','N','U') )
                      OR (A.INSTITUTION = 'UMS02' 
                     and A.CALC_Degree = 'Degree' 
                     and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','S','V','G') 
                     and A.FA_SOURCE in ('F','S','I','N','U'))
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G','O') 
                     and A.FA_SOURCE in ('F','S','I','N','U')) then A.EMPLID else null end) &PARTC_01
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and  A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE in ('S','G','V') 
                     and A.FA_SOURCE = 'F')
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE in ('S','G','V') 
                     and A.FA_SOURCE = 'F')
                      or (a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y'
                     and A.ISF_AMT <> 0) then a.emplid else null END) &PARTC_02,						!JAJ
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and  A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and a.federal_id = 'PELL')
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.federal_id = 'PELL') then a.emplid else null END) &PARTC_02a_Number,
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and  A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '
          and a.federal_id = 'PELL')
           or (A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.federal_id = 'PELL') then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_02a_Amount,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and a.federal_id <> 'PELL' 
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
                     and A.FA_SOURCE in ('F','N'))
                      or (A.INSTITUTION = 'UMS06' 
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.federal_id <> 'PELL' 
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
                     and A.FA_SOURCE in ('F','N'))
                      or (a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y'
                     and A.ISF_AMT <> 0) then a.emplid else null END) &PARTC_02b_Number,				!JAJ
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06'
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and  A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '
          and a.federal_id <> 'PELL' 
          and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
          and A.FA_SOURCE in ('F','N'))
           or (A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.federal_id <> 'PELL' 
          and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
          and A.FA_SOURCE in ('F','N')) then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_02b_Amount,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('S','V','G') 
                     and A.FA_SOURCE in ('S'))
                      or (A.INSTITUTION = 'UMS06' 
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('S','V','G') 
                     and A.FA_SOURCE in ('S'))
                      or (A.INSTITUTION = 'UMS06' 
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.item_type =  '000012000005') then a.emplid else null END) &PARTC_03_Number,   
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '
          and A.FIN_AID_TYPE IN ('S','V','G') 
          and A.FA_SOURCE in ('S'))
           or (A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.FIN_AID_TYPE IN ('S','V','G') 
          and A.FA_SOURCE in ('S'))
           or (A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.item_type =  '000012000005' ) then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_03_Amount,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 AND A.INSTITUTION <> 'UMS02' 
	                 and A.CALC_Degree = 'Degree' 
	                 and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
                     and A.FA_SOURCE in ('I','U') )
                      OR (A.INSTITUTION = 'UMS02' 
                     and A.CALC_Degree = 'Degree' 
                     and A.CALC_FIRST_TIME  = 'Y'
                     and a.calc_full_part = 'Full-Time' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','S','V','G') 
                     and A.FA_SOURCE in ('I','U') )
                      or ( A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
                     and A.FA_SOURCE in ('I','U')) then A.EMPLID else null end) &PARTC_04_Number,            
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      AND A.INSTITUTION <> 'UMS02' 
	      and A.CALC_Degree = 'Degree' 
	      and A.CALC_FIRST_TIME  = 'Y'
          and a.calc_full_part = 'Full-Time'  
          and a.disbursement_plan > ' '
          and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
          and A.FA_SOURCE in ('I','U') )
           OR (A.INSTITUTION = 'UMS02' 
          and A.CALC_Degree = 'Degree' 
          and A.CALC_FIRST_TIME  = 'Y'
          and a.calc_full_part = 'Full-Time'  
          and a.disbursement_plan > ' '
          and A.FIN_AID_TYPE IN ('A','S','V','G') 
          and A.FA_SOURCE in ('I','U') )
           or ( A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.FIN_AID_TYPE IN ('A','D','S','V','G') 
          and A.FA_SOURCE in ('I','U')) then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 end) &PARTC_04_Amount ,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE = 'L' 
                     and A.FEDERAL_ID <> 'PLUS')
                      or (A.INSTITUTION = 'UMS06' 
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FIN_AID_TYPE = 'L' 
                     and A.FEDERAL_ID <> 'PLUS') then a.emplid else null END) &PARTC_05_Number,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FA_SOURCE in ('F','N') 
                     and A.FIN_AID_TYPE = 'L' 
                     and A.FEDERAL_ID <> 'PLUS')
                      or (A.INSTITUTION = 'UMS06' 
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FA_SOURCE in ('F','N') 
                     and A.FIN_AID_TYPE = 'L' 
                     and A.FEDERAL_ID <> 'PLUS') then a.emplid else null END) &PARTC_05a_Number,
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and  A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '
          and A.FA_SOURCE in ('F','N') 
          and A.FIN_AID_TYPE = 'L' 
          and A.FEDERAL_ID <> 'PLUS')
           or (A.INSTITUTION = 'UMS06' 
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.FA_SOURCE in ('F','N') 
          and  A.FIN_AID_TYPE = 'L' 
          and A.FEDERAL_ID <> 'PLUS') then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_05a_Amount , 
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and  A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FA_SOURCE not in ('F','N') 
                     and A.FIN_AID_TYPE = 'L' 
                     and A.FEDERAL_ID <> 'PLUS')
                      or (A.INSTITUTION = 'UMS06' 
                     and  a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '
                     and A.FA_SOURCE not in ('F','N') 
                     and  A.FIN_AID_TYPE = 'L' and A.FEDERAL_ID <> 'PLUS') then a.emplid else null END) &PARTC_05b_Number,  
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '
          and A.FA_SOURCE not in ('F','N') 
          and A.FIN_AID_TYPE = 'L' 
          and A.FEDERAL_ID <> 'PLUS')
           or (A.INSTITUTION = 'UMS06' 
          and a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '
          and A.FA_SOURCE not in ('F','N') 
          and  A.FIN_AID_TYPE = 'L' 
          and A.FEDERAL_ID <> 'PLUS') then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_05b_Amount,   
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.INSTITUTION <> 'UMS02' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = 'On-Campus')
                      or (A.INSTITUTION = 'UMS02'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = 'On-Campus')
                      or (A.INSTITUTION = 'UMS06'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = 'On-Campus') then a.emplid else null END) &PARTD_02a,
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.INSTITUTION <> 'UMS02' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ('A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Parentz)
                      or (A.INSTITUTION = 'UMS02'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Parentz)
                      or (A.INSTITUTION = 'UMS06'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Parentz) then a.emplid else null END) &PARTD_02b,    ! Off-campus with family
COUNT(DISTINCT CASE WHEN (A.INSTITUTION <> 'UMS06' 
	                 and A.INSTITUTION <> 'UMS02' 
	                 and A.CALC_Degree = 'Degree' 
	                 and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Off_CampusZ)
                      or (A.INSTITUTION = 'UMS02'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and A.CALC_FIRST_TIME = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Off_CampusZ)
                      or (A.INSTITUTION = 'UMS06'  
                     and A.CALC_Degree = 'Degree' 
                     and a.calc_full_part = 'Full-Time'
                     and a.First_time_full_time = 'Y' 
                     AND a.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and a.disbursement_plan > ' '  
                     and a.fin_aid_type in ( 'A','D','G','S','V') 
                     and a.fa_source in ( 'F','I','N','S','U')         
                     and a.residency = 'IS' 
                     and a.strm = a.fin_aid_strm 
                     and a.um_house_cd_descr = $Off_CampusZ) then a.emplid else null END) &PARTD_02c, ! off-campus not with family
SUM(CASE WHEN (A.INSTITUTION <> 'UMS06' 
	      and A.INSTITUTION <> 'UMS02' 
	      and A.CALC_Degree = 'Degree' 
	      and a.calc_full_part = 'Full-Time'
          and A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '  
          and a.fin_aid_type in ( 'A','D','G','S','V') 
          and a.fa_source in ( 'F','I','N','S','U')         
          and a.residency = 'IS' ) 
           or (A.INSTITUTION = 'UMS02'  
          and A.CALC_Degree = 'Degree' 
          and a.calc_full_part = 'Full-Time'
          and A.CALC_FIRST_TIME = 'Y'  
          and a.disbursement_plan > ' '  
          and a.fin_aid_type in ( 'A','G','S','V') 
          and a.fa_source in ( 'F','I','N','S','U')         
          and a.residency = 'IS' )
           or (A.INSTITUTION = 'UMS06'  
          and  a.First_time_full_time = 'Y'  
          and a.disbursement_plan > ' '  
          and a.fin_aid_type in ( 'A','D','G','S','V') 
          and a.fa_source in ( 'F','I','N','S','U')         
          and a.residency = 'IS' ) then a.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTD_03

    Do Add-CARES-Act

    Let  #Group_1 = &Group_1
    Let  #Group_1_SUM = #Group_1_SUM + &Group_1
    Let  #Group_2 = &Group2
    Let  #Group_2_SUM = #Group_2_SUM + &Group2
    Let #Group2a = &Group2a                        
    Let #Group2a_SUM = #Group2a_SUM + &Group2a 
    Let #Group2b = &Group2b
    Let #Group2b_SUM = #Group2b_SUM + &Group2b
    Let #Group3 = &Group3
    Let #Group3_SUM = #Group3_SUM  + &Group3
    Let #Group4 = &Group4 
    Let #Group4_SUM = #Group4_SUM + &Group4 
    Let #PARTB_01_Number = &PARTB_01_Number
    Let #PARTB_01_Number_SUM = #PARTB_01_Number_SUM + &PARTB_01_Number
    
    If &PARTB_01_Number <> 0
        Let #PARTB_01_Amount =  &PARTB_01_Amount + #CA_Amt									!JAJ
        Let #PARTB_01_Amount_SUM = #PARTB_01_Amount_SUM + &PARTB_01_Amount + #CA_Amt		!JAJ
    End-If
    
    Let #PARTB_02_Number = &PARTB_02_Number
    Let #PARTB_02_Number_SUM = #PARTB_02_Number_SUM + &PARTB_02_Number
    Let #PARTB_02_Amount = &PARTB_02_Amount
    Let #PARTB_02_Amount_SUM = #PARTB_02_Amount_SUM + &PARTB_02_Amount
    Let #PARTB_03_Number = &PARTB_03_Number
    Let #PARTB_03_Number_SUM = #PARTB_03_Number_SUM + &PARTB_03_Number
    Let #PARTB_03_Amount = &PARTB_03_Amount
    Let #PARTB_03_Amount_SUM = #PARTB_03_Amount_SUM + &PARTB_03_Amount
    Let #PARTC_04a = &PARTC_04a
    Let #PARTC_04a_SUM = #PARTC_04a_SUM + &PARTC_04a
    Let #PARTC_04b = &PARTC_04b
    Let #PARTC_04b_SUM = #PARTC_04b_SUM  + &PARTC_04b
    Let #PARTC_04c = &PARTC_04c
    Let #PARTC_04c_SUM = #PARTC_04c_SUM + &PARTC_04c
    Let #PARTC_01 = &PARTC_01 
    Let #PARTC_01_SUM = #PARTC_01_SUM + &PARTC_01 
    
    Let #PARTC_02 = &PARTC_02
    Let #PARTC_02_SUM = #PARTC_02_SUM + &PARTC_02
    
    Let #PARTC_02a_Number = &PARTC_02a_Number
    Let #PARTC_02a_Number_SUM = #PARTC_02a_Number_SUM + &PARTC_02a_Number
    Let #PARTC_02a_Amount = &PARTC_02a_Amount
    Let #PARTC_02a_Amount_SUM = #PARTC_02a_Amount_SUM + &PARTC_02a_Amount
    
    Let #PARTC_02b_Number = &PARTC_02b_Number
    Let #PARTC_02b_Number_SUM = #PARTC_02b_Number_SUM + &PARTC_02b_Number

    If &PARTC_02b_Number <> 0
        Let #PARTC_02b_Amount = &PARTC_02b_Amount + #CA_Amt									!JAJ 787
        Let #PARTC_02b_Amount_SUM = #PARTC_02b_Amount_SUM + &PARTC_02b_Amount + #CA_Amt		!JAJ 787
    Else
        Let #PARTC_02b_Amount = &PARTC_02b_Amount        									!JAJ 857
        Let #PARTC_02b_Amount_SUM = #PARTC_02b_Amount_SUM + &PARTC_02b_Amount       		!JAJ 857
    End-If

    Let #PARTC_03_Number = &PARTC_03_Number
    Let #PARTC_03_Number_SUM = #PARTC_03_Number_SUM + &PARTC_03_Number
    Let #PARTC_03_Amount = &PARTC_03_Amount
    Let #PARTC_03_Amount_SUM = #PARTC_03_Amount_SUM + &PARTC_03_Amount
    Let #PARTC_04_Number = &PARTC_04_Number
    Let #PARTC_04_Number_SUM = #PARTC_04_Number_SUM + &PARTC_04_Number
    Let #PARTC_04_Amount = &PARTC_04_Amount
    Let #PARTC_04_Amount_SUM = #PARTC_04_Amount_SUM + &PARTC_04_Amount
    Let #PARTC_05_Number = &PARTC_05_Number
    Let #PARTC_05_Number_SUM = #PARTC_05_Number_SUM + &PARTC_05_Number
    Let #PARTC_05a_Number = &PARTC_05a_Number
    Let #PARTC_05a_Number_SUM = #PARTC_05a_Number_SUM + &PARTC_05a_Number
    Let #PARTC_05a_Amount = &PARTC_05a_Amount
    Let #PARTC_05a_Amount_SUM = #PARTC_05a_Amount_SUM + &PARTC_05a_Amount
    Let #PARTC_05b_Number = &PARTC_05b_Number
    Let #PARTC_05b_Number_SUM = #PARTC_05b_Number_SUM + &PARTC_05b_Number
    Let #PARTC_05b_Amount = &PARTC_05b_Amount 
    Let #PARTC_05b_Amount_SUM = #PARTC_05b_Amount_SUM + &PARTC_05b_Amount 
    Let #PARTD_02a = &PARTD_02a
    Let #PARTD_02a_SUM = #PARTD_02a_SUM + &PARTD_02a
    Let #PARTD_02b = &PARTD_02b
    Let #PARTD_02b_SUM = #PARTD_02b_SUM + &PARTD_02b
    Let #PARTD_02c = &PARTD_02c
    Let #PARTD_02c_SUM = #PARTD_02c_SUM + &PARTD_02c
    Let #PARTD_03 = &PARTD_03 
    Let #PARTD_03_SUM = #PARTD_03_SUM + &PARTD_03 
    Let $in_emplid = &A.EMPLID
    Let $Liststring1 = 
             '"'||$in_emplid||'"'||','||
             '"'||&A.CALC_FIRST_TIME||'"'||','||
             '"'||&A.CALC_FULL_PART||'"'||','||
             '"'||&A.CALC_DEGREE||'"'||','||
             '"'||&A.FIRST_TIME_FULL_TIME||'"'||','||
             edit(&SUM_ACCEPT_OR_OFFER_AMOUNT,'999999999')||','||
             '"'|| &DISBURSEMENT_PLAN||'"'||','||
             '"'|| &FEDERAL_ID||'"'||','||
             '"'|| &FA_SOURCE||'"'||','||
             '"'|| &FIN_AID_TYPE||'"'||','|| 
             '"'||&STDNT_GROUP||'"'||','|| 
             '"'||&UM_HOUSE_CD_DESCR||'"'||','||
             edit(&TOTAL_INCOME,'999999999')||','||
             edit(#Group_1,'9999999')||','||
             edit(#Group_2,'9999999')||','||
             edit(#Group2a,'9999999')||','||
             edit(#Group2b,'9999999')||','||
             edit(#Group3,'9999999')||','||
             edit(#Group4,'9999999')||','||
             edit(#PARTB_01_Number,'9999999')||','||
             edit(#PARTB_01_Amount,'9999999.99')||','||
             edit(#PARTB_02_Number,'9999999')||','||
             edit(#PARTB_02_Amount,'9999999.99')||','|| 
             edit(#PARTB_03_Number,'9999999')||','||
             edit(#PARTB_03_Amount,'9999999.99')||','||  
             edit(#PARTC_04a,'9999999')||','||
             edit(#PARTC_04b,'9999999')||','||
             edit(#PARTC_04c,'9999999')||','||
             edit(#PARTC_01,'9999999')||','||
             edit(#PARTC_02,'9999999')||','||
             edit(#PARTC_02a_Number,'9999999')||','||
             edit(#PARTC_02a_Amount,'9999999.99')||','||
             edit(#PARTC_02b_Number,'9999999')||','||
             edit(#PARTC_02b_Amount,'9999999.99')||','||
             edit(#PARTC_03_Number,'9999999')||','||
             edit(#PARTC_03_Amount,'9999999.99')||','||     
             edit(#PARTC_04_Number,'9999999')||','||
             edit(#PARTC_04_Amount,'9999999.99')||','||     
             edit(#PARTC_05_Number,'9999999')||','||
             edit(#PARTC_05a_Number,'9999999')||','||
             edit(#PARTC_05a_Amount,'9999999.99')||','||
             edit(#PARTC_05b_Number,'9999999')||','||
             edit(#PARTC_05b_Amount,'9999999.99')||','||
             edit(#PARTD_02a,'9999999')||','||
             edit(#PARTD_02b,'9999999')||','||     
             edit(#PARTD_02c,'9999999')||','||   
             edit(#PARTD_03,'9999999')||','     

	do Count_Students_Income

!  in-line view is similar to query UMS_AL_IPEDS_AY2016_DATA   it is referenced as A

FROM ( SELECT 
         SC.EMPLID
        ,SC.INSTITUTION
        ,SC.STRM
        ,SC.RESIDENCY
        ,B.AID_YEAR
        ,B.OFFER_AMOUNT
        ,B.ACCEPT_AMOUNT
        ,B.FIN_AID_TYPE 
        ,CASE WHEN SC.ACAD_PROG_PRIMARY = 'NDUG'  THEN 'Undergraduate' 
        	  WHEN SC.ACAD_PROG_PRIMARY = 'DIST'  THEN 'Undergraduate' 
        	  WHEN SC.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Undergraduate' 
        	  WHEN SC.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Undergraduate'
 	          WHEN SC.ACAD_PROG_PRIMARY = 'NDGR'  THEN 'Graduate' 
 	          WHEN SC.ACAD_PROG_PRIMARY = 'NDPR'  THEN 'Law' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UG1YR' THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UG2YR' THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UG4YR' THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UG'    THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UGAS'  THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'UGA'   THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'AA'    THEN 'Undergraduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'GR'    THEN 'Graduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'GRAD'  THEN 'Graduate' 
 	          WHEN SC.LEVEL_LOAD_RULE   = 'LAW'   THEN 'Law' 
 	          WHEN SC.ACAD_CAREER       = 'UGRD'  THEN 'Undergraduate' 
 	          WHEN SC.ACAD_CAREER       = 'GRAD'  THEN 'Graduate' 
 	          WHEN SC.ACAD_CAREER       = 'LAW'   THEN 'Law' 
 	          WHEN SC.ACAD_CAREER       = 'PBAC'  THEN 'Undergraduate' 
 	          ELSE 'Other' 
 	     END CALC1_Undergraduate_etc 
        ,CASE WHEN B.FIN_AID_TYPE IS NULL THEN 0 
        	  WHEN B.FIN_AID_TYPE = 'L'   THEN B.ACCEPT_AMOUNT 
        	  ELSE B.OFFER_AMOUNT 
         END CALC_ACCEPT_OR_OFFER_AMOUNT 
        ,B.TOTAL_INCOME
        ,SC.ADMIT_TERM
        ,SC.UM_LAST_SCH_TYPE
        ,SC.ACAD_LEVEL_BOT
        ,SC.ADMIT_TYPE
        ,TO_CHAR(SC.GRADUATION_DT, 'YYYY-MM-DD')
        ,SC.ACAD_PROG_PRIMARY
        ,SC.UM_UNT_TAK_PRG_RC
        ,B.FA_SOURCE
        ,B.FEDERAL_ID
        ,CASE WHEN SC.FA_ELIGIBILITY = 'Y' THEN 'Degree' 
 	          WHEN SC.FA_ELIGIBILITY = 'N' 
 	   	       AND SC.INSTITUTION = 'UMS01' 
               AND SC.ACAD_PROG_PRIMARY = 'BSAVI' THEN 'Degree' ELSE 'Non-Degree' 
         END CALC_Degree
        ,CASE WHEN (SC.ADMIT_TERM = ltrim(substr($input_Aidyear, 3, 2), ' ') || '10' 
                OR  SC.ADMIT_TERM = ltrim(to_char(to_number(substr($input_Aidyear, 3, 2)) - 1, '99'), ' ') || '30')
               AND (SC.ADMIT_TYPE = 'FYR' 
                OR  SC.ADMIT_TYPE = 'EFY') 
               AND (SC.UM_LAST_SCH_TYPE = 'SCD' 
                OR  SC.UM_LAST_SCH_TYPE = 'SHS' 
                OR  SC.ACAD_LEVEL_BOT = '10' 
                OR  SC.ACAD_LEVEL_BOT = '01') 
               AND  SC.PROG_REASON <> 'RADM' THEN 'Y' ELSE 'N' END CALC_FIRST_TIME 
        ,CASE WHEN (SC.ACAD_LEVEL_BOT = 'GR'
        	    OR  SC.ACAD_LEVEL_BOT = 'MAS'
        	    OR  SC.ACAD_LEVEL_BOT = 'PHD')
               AND  SC.UM_UNT_TAK_PRG_RC >= 6  THEN 'Full-Time' 
              WHEN  SC.UM_UNT_TAK_PRG_RC >= 12 THEN 'Full-Time' 
              ELSE 'Part-Time' 
         END CALC_FULL_PART 
        ,B.ITEM_TYPE
        ,B.ITEM_TYPE_DESCR
        ,SG.STDNT_GROUP
        ,SC.PROG_ACTION
        ,SC.PROG_REASON
        ,SC.ACAD_PLAN
        ,B.UM_HOUSE_CD_DESCR
        ,B.HOUSING_CODE_1
        ,D.DISBURSEMENT_PLAN
        ,B.STRM FIN_AID_STRM 
        ,CASE WHEN SC.INSTITUTION = 'UMS06' 
               AND SG.STDNT_GROUP = 'C' || LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear, 3, 2)) - 1, '99'), ' ') || 'A' THEN 'Y' 
              ELSE 'N' 
         END First_time_full_time
        ,E.ISF_AMT
  FROM PS_UM_STUD_CENSUS SC
  ,(SELECT 
      AID_YEAR
     ,OFFER_AMOUNT
     ,ACCEPT_AMOUNT
     ,FIN_AID_TYPE
     ,FISAP_TOT_INC TOTAL_INCOME
     ,FA_SOURCE
     ,FEDERAL_ID
     ,ITEM_TYPE
     ,ITEM_TYPE_DESCR
     ,UM_HOUSE_CD_DESCR
     ,HOUSING_CODE_1
     ,STRM
     ,ACAD_CAREER
     ,INSTITUTION
     ,EMPLID 
      FROM PS_UM_FIN_AID 
     WHERE ITEM_TYPE_DESCR NOT LIKE $Summer_filter) B
  ,PS_STDNT_GRPS SG
  ,(SELECT 
      EMPLID
     ,AID_YEAR
     ,INSTITUTION
     ,ITEM_TYPE
     ,ACAD_CAREER
     ,DISBURSEMENT_PLAN 
      FROM PS_STDNT_AWARDS SA 
     WHERE (NOT (SA.INSTITUTION = 'UMS01' AND  SA.DISBURSEMENT_PLAN IN ('D5','DR','OR','RO')) 
       AND NOT  (SA.INSTITUTION = 'UMS02' AND  SA.DISBURSEMENT_PLAN IN ('DR','RO')) 
       AND NOT  (SA.INSTITUTION = 'UMS03' AND (SA.DISBURSEMENT_PLAN IN ('D2','DR','RO'))) 
       AND NOT  (SA.INSTITUTION = 'UMS04' AND  SA.DISBURSEMENT_PLAN IN ('D3','DR','RO')) 
       AND NOT  (SA.INSTITUTION = 'UMS05' AND  SA.DISBURSEMENT_PLAN IN ('DR','RO','R1','R2','R3','RP')) 
       AND NOT  (SA.INSTITUTION = 'UMS06' AND  SA.DISBURSEMENT_PLAN IN ('D2','DR','PX','RO','R1')) 
       AND NOT  (SA.INSTITUTION = 'UMS07' AND  SA.DISBURSEMENT_PLAN IN ('DR','RO','P2'))) ) D
  ,(SELECT
      ISF.EMPLID
     ,ISF.BUSINESS_UNIT
     ,sum(ISF.ITEM_AMT * -1) ISF_AMT
      FROM SYSADM.PS_ITEM_SF ISF
     WHERE ISF.BUSINESS_UNIT = $input_Institution
       AND ISF.ITEM_TYPE in ('000012399995', '000012399996')
    GROUP BY ISF.EMPLID, ISF.BUSINESS_UNIT) E     											!JAJ
 WHERE (SC.STRM = substr($input_Aidyear,3,2)||'10' 
   AND SC.ROW_CHECK = 'Y' 
   AND CASE WHEN SC.ACAD_PROG_PRIMARY = 'NDUG'  THEN 'Undergraduate' 
   	        WHEN SC.ACAD_PROG_PRIMARY = 'DIST'  THEN 'Undergraduate' 
   	        WHEN SC.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Undergraduate' 
   	  	    WHEN SC.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Undergraduate' 
   	  	    WHEN SC.ACAD_PROG_PRIMARY = 'NDGR'  THEN 'Graduate' 
   	  	    WHEN SC.ACAD_PROG_PRIMARY = 'NDPR'  THEN 'Law' 
   	  	    WHEN SC.LEVEL_LOAD_RULE   = 'UG1YR' THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'UG2YR' THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'UG4YR' THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'UG'    THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'UGAS'  THEN 'Undergraduate'
   	        WHEN SC.LEVEL_LOAD_RULE   = 'UGA'   THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'AA'    THEN 'Undergraduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'GR'    THEN 'Graduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'GRAD'  THEN 'Graduate' 
   	        WHEN SC.LEVEL_LOAD_RULE   = 'LAW'   THEN 'Law' 
   	        WHEN SC.ACAD_CAREER       = 'UGRD'  THEN 'Undergraduate' 
   	        WHEN SC.ACAD_CAREER       = 'GRAD'  THEN 'Graduate' 
   	        WHEN SC.ACAD_CAREER       = 'LAW'   THEN 'Law' 
   	        WHEN SC.ACAD_CAREER       = 'PBAC'  THEN 'Undergraduate' 
   	        ELSE 'Other' 
   	   END = 'Undergraduate' 
   AND SC.EMPLID         = B.EMPLID(+) 
   AND SC.ACAD_CAREER    = B.ACAD_CAREER(+) 
   AND SC.INSTITUTION    = B.INSTITUTION(+) 
   AND B.AID_YEAR(+)     = $input_Aidyear 
   AND SC.EMPLID         = SG.EMPLID(+) 
   AND SC.INSTITUTION    = SG.INSTITUTION(+) 
   AND SG.STDNT_GROUP(+) = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear,3,2)) - 1,'99'),' ')||'A' 
   AND B.EMPLID          = D.EMPLID(+) 
   AND B.AID_YEAR        = D.AID_YEAR(+) 
   AND B.INSTITUTION     = D.INSTITUTION(+) 
   AND B.ITEM_TYPE       = D.ITEM_TYPE(+) 
   AND B.ACAD_CAREER     = D.ACAD_CAREER(+))
   AND SC.EMPLID         = E.EMPLID(+)
   AND SC.INSTITUTION    = E.BUSINESS_UNIT(+)) A
   ,SYSADM.PS_UM_UMGROUP4 GROUP4
 WHERE A.INSTITUTION = $input_institution
   AND A.INSTITUTION = GROUP4.INSTITUTION(+)
   AND A.EMPLID      = GROUP4.EMPLID(+)
GROUP BY A.EMPLID,
         A.CALC_FIRST_TIME,
         A.CALC_FULL_PART,
         A.CALC_DEGREE,
         A.FIRST_TIME_FULL_TIME,	
         A.RESIDENCY

END-SELECT

	do print_em

	do print_em_parte

End-Procedure

!***********************************************************************
!  Procedure  Count Students   Income Levels                           *
!                                                                      *                    
!  This is much like Count_Students and is done as a seperate          *
!  procedure only to avoid problems with SQR's SQL buffer size.        *
!                                                                      *                    
!***********************************************************************
Begin-Procedure Count_Students_Income

 
BEGIN-SELECT 
AA.EMPLID,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = 'On-Campus'
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH'))
                      or (AA.INSTITUTION = 'UMS06' 
                     and  AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = 'On-Campus'
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG','FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AA.emplid 
                    else NULL 
               END) &PARTE_1a,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = $Parentz                                                ! was AA.um_house_cd_descr = $Parentz
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH'))
                      or (AA.INSTITUTION = 'UMS06' 
                     and  AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = $Parentz
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AA.emplid 
                    else NULL 
               END) &PARTE_1b, ! Off-campus with family
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = $Off_Campusz                                            ! was AA.um_house_cd_descr = $Off_Campusz
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH'))
                      or (AA.INSTITUTION = 'UMS06' 
                     and AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     and AA.strm = AA.fin_aid_strm 
                     and AA.um_house_cd_descr = $Off_Campusz
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AA.emplid 
                    else NULL 
               END) &PARTE_1c, ! Off-campus not with family
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME < 30000)
                      or (AA.INSTITUTION = 'UMS06' 
                     and AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME <= 30000) THEN AA.emplid 
                    else NULL 
               END) &PARTE_2a_Col1,
! These counts check group4
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.INSTITUTION <> 'UMS02' 
                     AND GROUP44.FLAG = 'X'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME < 30001)
                      or (AA.INSTITUTION = 'UMS02'
                     and AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME < 30001)
                      or (AA.INSTITUTION = 'UMS06' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME < 30001) THEN AA.EMPLID 
                    else NULL 
               END) &PARTE_2a_Col2,
SUM(CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	      and AA.INSTITUTION <> 'UMS02' 
          AND GROUP44.FLAG = 'X'
          AND AA.disbursement_plan > ' '  
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME < 30001)
           or (AA.INSTITUTION = 'UMS02' 
          and AA.disbursement_plan > ' '  
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME < 30001)
           or (AA.INSTITUTION = 'UMS06' 
          and AA.disbursement_plan > ' ' 
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME < 30001) THEN AA.CALC_ACCEPT_OR_OFFER_AMOUNT  
         else 0 
    END) &PARTE_2a_Col3,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') 
                     AND AA.TOTAL_INCOME > 30000 
                     AND AA.TOTAL_INCOME <= 48000)
                      or (AA.INSTITUTION = 'UMS06' 
                     and AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') 
                     AND AA.TOTAL_INCOME > 30000 
                     AND AA.TOTAL_INCOME < 48001) THEN AA.emplid 
                    else NULL 
               END) &PARTE_2b_Col1,
COUNT( DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                  and AA.INSTITUTION <> 'UMS02' 
                      AND GROUP44.FLAG = 'X'
                      AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                      and AA.disbursement_plan > ' '  
                      and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                      and AA.FA_SOURCE in ('F','I','N','U','S')
                      AND AA.TOTAL_INCOME > 30000 
                      AND AA.TOTAL_INCOME < 48001)
                       or (AA.INSTITUTION = 'UMS02' 
                      and AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                      and AA.disbursement_plan > ' '  
                      AND GROUP44.FLAG = 'X'
                      and AA.FIN_AID_TYPE IN ('A','S','G','V')
                      and AA.FA_SOURCE in ('F','I','N','U','S')
                      AND AA.TOTAL_INCOME > 30000 
                      AND AA.TOTAL_INCOME < 48001)
                       or (AA.INSTITUTION = 'UMS06' 
                      AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                      and AA.disbursement_plan > ' ' 
                      AND GROUP44.FLAG = 'X'
                      and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                      and AA.FA_SOURCE in ('F','I','N','U','S')
                      AND AA.TOTAL_INCOME > 30000 
                      AND AA.TOTAL_INCOME < 48001) THEN AA.EMPLID 
                     else NULL 
                 END) &PARTE_2b_Col2,
SUM(CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	      and AA.INSTITUTION <> 'UMS02' 
          AND GROUP44.FLAG = 'X'
          AND AA.disbursement_plan > ' '  
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 30000 
          AND AA.TOTAL_INCOME < 48001)
           or (AA.INSTITUTION = 'UMS02' 
          and AA.disbursement_plan > ' '  
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 30000 
          AND AA.TOTAL_INCOME < 48001)
           or (AA.INSTITUTION = 'UMS06' 
          and AA.disbursement_plan > ' ' 
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 30000 
          AND AA.TOTAL_INCOME < 48001) THEN AA.CALC_ACCEPT_OR_OFFER_AMOUNT  
         else 0 
    END) &PARTE_2b_Col3,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') 
                     AND AA.TOTAL_INCOME > 48000 
                     AND AA.TOTAL_INCOME < 75001)
                      or (AA.INSTITUTION = 'UMS06' 
                     and  AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME > 48000 
                     AND AA.TOTAL_INCOME < 75001) THEN AA.emplid 
                    else NULL 
               END) &PARTE_2c_Col1,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.INSTITUTION <> 'UMS02' 
                     AND GROUP44.FLAG = 'X'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 48000 
                     AND AA.TOTAL_INCOME < 75001)
                      or (AA.INSTITUTION = 'UMS02' 
                     and AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 48000 
                     AND AA.TOTAL_INCOME < 75001)
                      or (AA.INSTITUTION = 'UMS06' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 48000 
                     AND AA.TOTAL_INCOME < 75001) THEN AA.EMPLID 
                    else NULL 
               END) &PARTE_2c_Col2,
SUM(CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	      and AA.INSTITUTION <> 'UMS02' 
          AND GROUP44.FLAG = 'X'
          AND AA.disbursement_plan > ' '  
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 48000 
          AND AA.TOTAL_INCOME < 75001)
           or (AA.INSTITUTION = 'UMS02' 
          and AA.disbursement_plan > ' '  
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 48000 
          AND AA.TOTAL_INCOME < 75001)
           or (AA.INSTITUTION = 'UMS06' 
          and AA.disbursement_plan > ' ' 
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 48000 
          AND AA.TOTAL_INCOME < 75001) THEN AA.CALC_ACCEPT_OR_OFFER_AMOUNT  
         else 0 
    END) &PARTE_2c_Col3,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME > 75000 
                     AND AA.TOTAL_INCOME < 110001)
                      or (AA.INSTITUTION = 'UMS06' 
                     and AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME > 75000 
                     AND AA.TOTAL_INCOME < 110001) THEN AA.emplid 
                    else NULL 
               END) &PARTE_2d_Col1,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.INSTITUTION <> 'UMS02' 
                     AND GROUP44.FLAG = 'X'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 75000 
                     AND AA.TOTAL_INCOME < 110001)
                      or (AA.INSTITUTION = 'UMS02' 
                     and AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 75000 
                     AND AA.TOTAL_INCOME < 110001)
                      or (AA.INSTITUTION = 'UMS06' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AA.disbursement_plan > ' ' 
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 75000 
                     AND AA.TOTAL_INCOME < 110001) THEN AA.EMPLID 
                    else NULL 
               END) &PARTE_2d_Col2,
SUM(CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	      and AA.INSTITUTION <> 'UMS02' 
          AND GROUP44.FLAG = 'X'
          AND AA.disbursement_plan > ' '  
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 75000 
          AND AA.TOTAL_INCOME < 110001)
           or (AA.INSTITUTION = 'UMS02' 
          and AA.disbursement_plan > ' '  
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 75000 
          AND AA.TOTAL_INCOME < 110001)
           or (AA.INSTITUTION = 'UMS06' 
          and AA.disbursement_plan > ' ' 
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 75000 
          AND AA.TOTAL_INCOME < 110001) THEN AA.CALC_ACCEPT_OR_OFFER_AMOUNT  
         else 0 
    END) &PARTE_2d_Col3,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.CALC_Degree  = 'Degree'
                     and AA.CALC_FIRST_TIME = 'Y'  
                     and AA.calc_full_part = 'Full-Time'
                     and AA.RESIDENCY = 'IS' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AA.disbursement_plan > ' '  
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME > 110000)
                      or (AA.INSTITUTION = 'UMS06' 
                     and AA.First_time_full_time = 'Y' 
                     and AA.residency = 'IS'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND AA.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')
                     AND AA.TOTAL_INCOME > 110000) THEN AA.emplid 
                    else NULL 
               END) &PARTE_2e_Col1,
COUNT(DISTINCT CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	                 and AA.INSTITUTION <> 'UMS02' 
                     AND GROUP44.FLAG = 'X'
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 110000)
                      or (AA.INSTITUTION = 'UMS02' 
                     and AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' '  
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 110000)
                      or (AA.INSTITUTION = 'UMS06' 
                     AND AA.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 
                     and AA.disbursement_plan > ' ' 
                     AND GROUP44.FLAG = 'X'
                     and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
                     and AA.FA_SOURCE in ('F','I','N','U','S')
                     AND AA.TOTAL_INCOME > 110000) THEN AA.EMPLID 
                    else NULL 
               END) &PARTE_2e_Col2,
SUM(CASE WHEN (AA.INSTITUTION <> 'UMS06' 
	      and AA.INSTITUTION <> 'UMS02' 
          AND GROUP44.FLAG = 'X'
          AND AA.disbursement_plan > ' '  
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 110000)
           or (AA.INSTITUTION = 'UMS02' 
          and AA.disbursement_plan > ' '  
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 110000)
           or (AA.INSTITUTION = 'UMS06' 
          and AA.disbursement_plan > ' ' 
          AND GROUP44.FLAG = 'X'
          and AA.FIN_AID_TYPE IN ('A','D','S','G','V')
          and AA.FA_SOURCE in ('F','I','N','U','S')
          AND AA.TOTAL_INCOME > 110000) THEN AA.CALC_ACCEPT_OR_OFFER_AMOUNT  
         else 0 
    END) &PARTE_2e_Col3            


    Let #PARTE_1a = &PARTE_1a
    Let #PARTE_1a_SUM = #PARTE_1a_SUM  + &PARTE_1a
    Let #PARTE_1b = &PARTE_1b
    Let #PARTE_1b_SUM = #PARTE_1b_SUM + &PARTE_1b
    Let #PARTE_1c = &PARTE_1c
    Let #PARTE_1c_SUM = #PARTE_1c_SUM + &PARTE_1c

    Let #PARTE_2a_Col1 = &PARTE_2a_Col1
    Let #PARTE_2a_Col1_SUM = #PARTE_2a_Col1_SUM + &PARTE_2a_Col1
    Let #PARTE_2a_Col2 = &PARTE_2a_Col2 
    Let #PARTE_2a_Col2_SUM = #PARTE_2a_Col2_SUM + &PARTE_2a_Col2 
    Let #PARTE_2a_Col3 = &PARTE_2a_Col3
    Let #PARTE_2a_Col3_SUM = #PARTE_2a_Col3_SUM + &PARTE_2a_Col3

    Let #PARTE_2b_Col1 = &PARTE_2b_Col1
    Let #PARTE_2b_Col1_SUM = #PARTE_2b_Col1_SUM  + &PARTE_2b_Col1
    Let #PARTE_2b_Col2 = &PARTE_2b_Col2
    Let #PARTE_2b_Col2_SUM = #PARTE_2b_Col2_SUM + &PARTE_2b_Col2
    Let #PARTE_2b_Col3 = &PARTE_2b_Col3
    Let #PARTE_2b_Col3_SUM = #PARTE_2b_Col3_SUM + &PARTE_2b_Col3
 	
    Let #PARTE_2c_Col1 = &PARTE_2c_Col1 
    Let #PARTE_2c_Col1_SUM = #PARTE_2c_Col1_SUM + &PARTE_2c_Col1
    Let #PARTE_2c_Col2 = &PARTE_2c_Col2
    Let #PARTE_2c_Col2_SUM = #PARTE_2c_Col2_SUM + &PARTE_2c_Col2
    Let #PARTE_2c_Col3 = &PARTE_2c_Col3
    Let #PARTE_2c_Col3_SUM = #PARTE_2c_Col3_SUM + &PARTE_2c_Col3
	
    Let #PARTE_2d_Col1 = &PARTE_2d_Col1
    Let #PARTE_2d_Col1_SUM = #PARTE_2d_Col1_SUM + &PARTE_2d_Col1
    Let #PARTE_2d_Col2 = &PARTE_2d_Col2
    Let #PARTE_2d_Col2_SUM = #PARTE_2d_Col2_SUM + &PARTE_2d_Col2
    Let #PARTE_2d_Col3 = &PARTE_2d_Col3
    Let #PARTE_2d_Col3_SUM = #PARTE_2d_Col3_SUM + &PARTE_2d_Col3
   
    Let #PARTE_2e_Col1 = &PARTE_2e_Col1
    Let #PARTE_2e_Col1_SUM = #PARTE_2e_Col1_SUM + &PARTE_2e_Col1
    Let #PARTE_2e_Col2 = &PARTE_2e_Col2 
    Let #PARTE_2e_Col2_SUM = #PARTE_2e_Col2_SUM + &PARTE_2e_Col2
    Let #PARTE_2e_Col3 = &PARTE_2e_Col3
    Let #PARTE_2e_Col3_SUM = #PARTE_2e_Col3_SUM  + &PARTE_2e_Col3

    Let $Liststring1 = $ListString1 ||
             edit(#PARTE_1a,'9999999')||','||
             edit(#PARTE_1b,'9999999')||','||
             edit(#PARTE_1c,'9999999')||','||
             edit(#PARTE_2a_Col1,'9999999')||','||
             edit(#PARTE_2a_Col2,'9999999')||','||
             edit(#PARTE_2a_Col3,'9999999.99')||','||
             edit(#PARTE_2b_Col1,'9999999')||','||
             edit(#PARTE_2b_Col2,'9999999')||','||
             edit(#PARTE_2b_Col3,'9999999.99')||','||
             edit(#PARTE_2c_Col1,'9999999')||','||
             edit(#PARTE_2c_Col2,'9999999')||','||
             edit(#PARTE_2c_Col3,'9999999.99')||','||
             edit(#PARTE_2d_Col1,'9999999')||','||
             edit(#PARTE_2d_Col2,'9999999')||','||
             edit(#PARTE_2d_Col3,'9999999.99')||','||
             edit(#PARTE_2e_Col1,'9999999')||','||
             edit(#PARTE_2e_Col2,'9999999')||','||
             edit(#PARTE_2e_Col3,'9999999.99')

    write 1 from $ListString1

! first in-line view is similar to query UMS_AL_IPEDS_AY2016_DATA   it is AA
FROM (SELECT 
        A.EMPLID
       ,A.INSTITUTION
       ,A.STRM
       ,A.RESIDENCY
       ,B.AID_YEAR
       ,B.OFFER_AMOUNT
       ,B.ACCEPT_AMOUNT
       ,B.FIN_AID_TYPE 
       ,CASE WHEN A.ACAD_PROG_PRIMARY = 'NDUG'  THEN 'Undergraduate' 
       	     WHEN A.ACAD_PROG_PRIMARY = 'DIST'  THEN 'Undergraduate' 
       	     WHEN A.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Undergraduate' 
       	     WHEN A.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Undergraduate'
 	         WHEN A.ACAD_PROG_PRIMARY = 'NDGR'  THEN 'Graduate' 
 	         WHEN A.ACAD_PROG_PRIMARY = 'NDPR'  THEN 'Law' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UG1YR' THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UG2YR' THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UG4YR' THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UG'    THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UGAS'  THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'UGA'   THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'AA'    THEN 'Undergraduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'GR'    THEN 'Graduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'GRAD'  THEN 'Graduate' 
 	         WHEN A.LEVEL_LOAD_RULE   = 'LAW'   THEN 'Law' 
 	         WHEN A.ACAD_CAREER       = 'UGRD'  THEN 'Undergraduate' 
 	         WHEN A.ACAD_CAREER       = 'GRAD'  THEN 'Graduate' 
 	         WHEN A.ACAD_CAREER       = 'LAW'   THEN 'Law' 
 	         WHEN A.ACAD_CAREER       = 'PBAC'  THEN 'Undergraduate' 
 	         ELSE 'Other' 
 	    END CALC1_Undergraduate_etc 
       ,CASE WHEN B.FIN_AID_TYPE IS NULL THEN 0 WHEN B.FIN_AID_TYPE = 'L' THEN B.ACCEPT_AMOUNT ELSE B.OFFER_AMOUNT END CALC_ACCEPT_OR_OFFER_AMOUNT 
       ,B.TOTAL_INCOME
       ,A.ADMIT_TERM
       ,A.UM_LAST_SCH_TYPE
       ,A.ACAD_LEVEL_BOT
       ,A.ADMIT_TYPE
       ,TO_CHAR(A.GRADUATION_DT,'YYYY-MM-DD')
       ,A.ACAD_PROG_PRIMARY
       ,A.UM_UNT_TAK_PRG_RC
       ,B.FA_SOURCE
       ,B.FEDERAL_ID
       ,CASE WHEN A.FA_ELIGIBILITY = 'Y' THEN 'Degree' 
       	     WHEN A.FA_ELIGIBILITY = 'N' 
              AND A.INSTITUTION = 'UMS01' 
              AND A.ACAD_PROG_PRIMARY = 'BSAVI' THEN 'Degree' 
             ELSE 'Non-Degree' 
        END CALC_Degree
       ,CASE WHEN (A.ADMIT_TERM = ltrim(substr($input_Aidyear, 3, 2), ' ') || '10' 
               OR  A.ADMIT_TERM = ltrim(to_char(to_number(substr($input_Aidyear, 3, 2)) - 1, '99'), ' ') || '30') 
              AND (A.ADMIT_TYPE = 'FYR' 
               OR  A.ADMIT_TYPE = 'EFY') 
              AND (A.UM_LAST_SCH_TYPE = 'SCD' 
               OR  A.UM_LAST_SCH_TYPE = 'SHS' 
               OR  A.ACAD_LEVEL_BOT = '10' 
               OR  A.ACAD_LEVEL_BOT = '01') 
              AND  A.PROG_REASON <> 'RADM' THEN 'Y' 
             ELSE 'N' 
        END CALC_FIRST_TIME 
       ,CASE WHEN A.ACAD_LEVEL_BOT = 'GR' 
              AND A.UM_UNT_TAK_PRG_RC >= 6  THEN 'Full-Time' 
             WHEN A.ACAD_LEVEL_BOT = 'MAS' 
              AND A.UM_UNT_TAK_PRG_RC >= 6  THEN 'Full-Time' 
             WHEN A.ACAD_LEVEL_BOT = 'PHD' 
              AND A.UM_UNT_TAK_PRG_RC >= 6  THEN 'Full-Time' 
             WHEN A.UM_UNT_TAK_PRG_RC >= 12 THEN 'Full-Time' 
             ELSE 'Part-Time' 
        END CALC_FULL_PART 
       ,B.ITEM_TYPE
       ,B.ITEM_TYPE_DESCR
       ,C.STDNT_GROUP
       ,A.PROG_ACTION
       ,A.PROG_REASON
       ,A.ACAD_PLAN
       ,B.UM_HOUSE_CD_DESCR
       ,B.HOUSING_CODE_1
       ,D.DISBURSEMENT_PLAN
       ,B.STRM FIN_AID_STRM 
       ,CASE WHEN A.INSTITUTION = 'UMS06' 
              AND C.STDNT_GROUP = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear, 3, 2)) - 1, '99'),' ') || 'A' THEN 'Y' 
             ELSE 'N' 
        END First_time_full_time 
       FROM PS_UM_STUD_CENSUS A,
            (SELECT 
               AID_YEAR
              ,OFFER_AMOUNT
              ,ACCEPT_AMOUNT
              ,FIN_AID_TYPE
              ,FISAP_TOT_INC TOTAL_INCOME
              ,FA_SOURCE
              ,FEDERAL_ID
              ,ITEM_TYPE
              ,ITEM_TYPE_DESCR
              ,UM_HOUSE_CD_DESCR
              ,HOUSING_CODE_1
              ,STRM
              ,ACAD_CAREER
              ,INSTITUTION
              ,EMPLID 
               FROM PS_UM_FIN_AID 
              WHERE ITEM_TYPE_DESCR NOT LIKE $Summer_filter) B, 
            PS_STDNT_GRPS C,
            (SELECT 
               EMPLID
              ,AID_YEAR
              ,INSTITUTION
              ,ITEM_TYPE
              ,ACAD_CAREER
              ,DISBURSEMENT_PLAN 
               FROM PS_STDNT_AWARDS AA 
              WHERE (NOT (AA.INSTITUTION = 'UMS01' 
                AND  AA.DISBURSEMENT_PLAN IN ('D5','DR','OR','RO')) 
                AND  NOT (AA.INSTITUTION = 'UMS02' 
                AND  AA.DISBURSEMENT_PLAN IN ('DR','RO')) 
                AND  NOT (AA.INSTITUTION = 'UMS03' 
                AND  (AA.DISBURSEMENT_PLAN IN ('D2','DR','RO'))) 
                AND  NOT(AA.INSTITUTION = 'UMS04'  
                AND  AA.DISBURSEMENT_PLAN IN ('D3','DR','RO')) 
                AND  NOT (AA.INSTITUTION = 'UMS05' 
                AND  AA.DISBURSEMENT_PLAN IN ('DR','RO','R1','R2','R3','RP')) 
                AND  NOT (AA.INSTITUTION = 'UMS06' 
                AND  AA.DISBURSEMENT_PLAN IN ('D2','DR','PX','RO','R1')) 
                AND  NOT (AA.INSTITUTION = 'UMS07' 
                AND  AA.DISBURSEMENT_PLAN IN ('DR','RO','P2')) ) ) D 
      WHERE (A.STRM = substr($input_Aidyear, 3, 2) || '10' 
        AND A.ROW_CHECK = 'Y' 
        AND CASE WHEN A.ACAD_PROG_PRIMARY = 'NDUG'  THEN 'Undergraduate' 
        	     WHEN A.ACAD_PROG_PRIMARY = 'DIST'  THEN 'Undergraduate' 
        	     WHEN A.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Undergraduate' 
   	             WHEN A.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Undergraduate' 
   	             WHEN A.ACAD_PROG_PRIMARY = 'NDGR'  THEN 'Graduate' 
   	             WHEN A.ACAD_PROG_PRIMARY = 'NDPR'  THEN 'Law' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'UG1YR' THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'UG2YR' THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'UG4YR' THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'UG'    THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'UGAS'  THEN 'Undergraduate'
   	             WHEN A.LEVEL_LOAD_RULE   = 'UGA'   THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'AA'    THEN 'Undergraduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'GR'    THEN 'Graduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'GRAD'  THEN 'Graduate' 
   	             WHEN A.LEVEL_LOAD_RULE   = 'LAW'   THEN 'Law' 
   	             WHEN A.ACAD_CAREER       = 'UGRD'  THEN 'Undergraduate' 
   	             WHEN A.ACAD_CAREER       = 'GRAD'  THEN 'Graduate' 
   	             WHEN A.ACAD_CAREER       = 'LAW'   THEN 'Law' 
   	             WHEN A.ACAD_CAREER       = 'PBAC'  THEN 'Undergraduate' 
   	             ELSE 'Other' 
   	        END = 'Undergraduate' 
        AND A.EMPLID = B.EMPLID(+) 
        AND A.ACAD_CAREER = B.ACAD_CAREER(+) 
        AND A.INSTITUTION = B.INSTITUTION(+) 
        AND B.AID_YEAR(+) = $input_Aidyear 
        AND A.EMPLID = C.EMPLID(+) 
        AND A.INSTITUTION = C.INSTITUTION(+) 
        AND C.STDNT_GROUP(+)  = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear,3,2)) - 1,'99'),' ')||'A' 
        AND B.EMPLID = D.EMPLID(+) 
        AND B.AID_YEAR = D.AID_YEAR(+) 
        AND B.INSTITUTION = D.INSTITUTION(+) 
        AND B.ITEM_TYPE = D.ITEM_TYPE(+) 
        AND B.ACAD_CAREER = D.ACAD_CAREER(+))) AA,
     SYSADM.PS_UM_UMGROUP4 GROUP44
  WHERE AA.INSTITUTION = $input_institution
    AND AA.EMPLID = $in_emplid
    AND AA.INSTITUTION = GROUP44.INSTITUTION(+)
    AND AA.EMPLID = GROUP44.EMPLID(+)
 group by AA.EMPLID
END-SELECT

End-Procedure  ! List_aid guys Income

begin-procedure print_em
                        Print '01.Group 1' (+1,1) bold
                        Print 'All undergraduate student enrolled in Fall' (,40)
                        Print $Last_year (,+1)
                        print ':' (,+1)
                        graphic (,105,12) box 1 3 0
                        Print #Group_1_SUM (,112) edit '9,999'
                        
                        Print '02.Group 2' (+3,1) bold
                        Print 'Of those in Group 1, those who were full-time, first-time' (,40)
                        graphic (,105,12) box 1 3 0
                        Print #Group_2_SUM (,112) edit '9,999'
                        Print 'degree/certificate-seeking:' (+1,40)
                        
                        Print '02a.Group 2' (+3,1) bold
                        Print 'Of those in Group 2, those who were awarded any' (,40)
                        Print 'Federal Work Study, loans to students, or grant or' (+1,40)
                        graphic (,105,12) box 1 3 0
                        Print #Group2a_SUM (,112) edit '9,999'
                        Print 'scholarship aid from the federal government, state/local' (+1,40)
                        Print 'government, the institution, or other sources known to' (+1,40)
                        Print 'the institution' (+1,40)
                        
                        Print '02b.Group 2' (+3,1) bold
                        Print 'Of those in Group 2, those who were awarded any' (,40)
                        Print 'loans to students or grant or scholarship aid' (+1,40)
                        graphic (,105,12) box 1 3 0
                        Print #Group2b_SUM (,112) edit '9,999'
                        Print 'from the federal government, state/local' (+1,40)
                        Print 'government, or the institution' (+1,40)
                        
                        Print '03.Group 3' (+3,1) bold
                        Print 'Of those in Group 2, those who were'  (,40)
                        Print 'paying the in-state or' (,+1) bold
                        Print 'in-district tuition rate' (+1,40) bold
                        print ' were awarded grant or scholarship' (,+1)
                        graphic (,105,12) box 1 3 0
                        Print #Group3_SUM (,112) edit '9,999'
                        Print 'aid from the federal government, state/loacl government,' (+1,40)
                        Print 'or the institution. Exclude other sources of grants.' (+1,40)
                        
                         Print '04.Group 4' (+3,1) bold
                        Print 'Of those in Group 2, those who were'  (,40)
                        Print 'paying the in-state or' (,+1) bold
                        Print 'in-district tuition rate' (+1,40) bold
                        print 'were awarded any Title IV federal' (,+1)
                        graphic (,105,12) box 1 3 0
                        Print #Group4_SUM (,112) edit '9,999'
                        Print 'student aid' (+1,40)
 
                        new-page
                        graphic (+4,1,180) box 2 0 10
                        print 'Part B - Enter Financial Aid About Group 1' (,1) bold
                        print 'Number of Group 1 students' (+3,40) bold
                        Print 'Total amount of aid awarded to' (,80) bold
                        Print 'Aid Type' (+1,15) bold
                        Print 'awarded aid in' (,40) bold
                        Print $last_year (,+1) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        Print 'Group 1 students in' (,80) bold
                        Print $last_year (,+1) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        print '01. Grant or scholarship aid' (+2,2)
                        print #PARTB_01_Number_SUM (,48) edit '99,999,999,999' 
                        graphic (,42,20) box 1 3 0
                        print #PARTB_01_Amount_SUM (,90) edit '$$$,$$$,$$9.99' 
                        graphic (,84,20) box 1 3 0
                        print 'received from the federal' (+1,2)
                        print 'government, state/local' (+1,2)
                        print 'government, the institution,' (+1,2)
                        print 'and other sources known' (+1,2)
                        
                        print '02. Pell grants' (+3,2)
                        print #PARTB_02_Number_SUM (,48) edit '99,999,999,999' 
                        graphic (,42,20) box 1 3 0
                        print #PARTB_02_Amount_SUM (,90) edit '$$$,$$$,$$9.99'
                        graphic (,84,20) box 1 3 0
                        
                        print '03. Federal student loans' (+3,2)
                        print #PARTB_03_Number_SUM (,48) edit '99,999,999,999' 
                        graphic (,42,20) box 1 3 0
                        print #PARTB_03_Amount_SUM (,90) edit '$$$,$$$,$$9.99' 
                        graphic (,84,20) box 1 3 0
                        
                        new-page
                        
                        graphic (+1,1,180) box 2 0 10
                        print 'Part C - Enter Financial Aid About Group 2' (,1) bold
                        graphic (+3,2,30) box 1 0 10
                        print '04. Group 2 students:' (,2) bold
                        print 'Number of Group 2 students' (+2,50) bold
                        print 'in Fall' (+1,50) bold
                        print $last_year (,+1) bold
                        print '04a. paying in-district tuition rates' (+2,2)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_04a_SUM (,61) edit '999,999,999' 
                        print '04b. paying in-state tuition rates' (+2,2)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_04b_SUM (,61) edit '999,999,999' 
                         print '04b. paying out-of-state tuition rates' (+2,2)
                         graphic (,52,20) box 1 3 0
                        print #PARTC_04c_SUM (,61) edit '999,999,999' 
                        
                        graphic (+2,1,180) box 2 0 10
                        print 'Part C - Page 2' (,1) bold
                        print 'Aid Type' (+2,12) bold
                        print 'Number of Group 2 students' (,50) bold
                        Print 'Total amount of aid' (,90) bold
                        print 'awarded aid in' (+1,50) bold
                        Print $last_year (,+1) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        print 'awarded to Group 2 students' (,90) bold
                        print 'in' (+1,90) bold
                        Print $last_year (,+1) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        print '01. Grants or scholarships from' (+2,2)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_01_SUM (,61) edit '999,999,999' 
                        print 'the federal government,' (+1,6)
                        print 'state/local government, or the' (+1,6)
                        print 'institution' (+1,6)
                        print '02. Federal grants' (+1,6) underline
                        graphic (,52,20) box 1 3 0
                        print #PARTC_02_SUM (,61) edit '999,999,999' 
                        print '02a. Pell grants' (+2,10)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_02a_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_02a_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        print '02b. Other federal grants' (+2,10)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_02b_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_02b_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        print '03. State/local government' (+2,6)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_03_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_03_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        Print 'grants or scholarships' (+1,10)
                        print '(grants/scholarships/waivers)' (+1,10)
                        print '04. Institutional grants or ' (+2,6)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_04_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_04_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        print 'scholarships' (+1,10)
                        print '(scholarships/fellowships)' (+1,10)
                        print '05. Loans to students' (+2,6)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_05_Number_SUM (,61) edit '999,999,999' 
                        print '05a. Federal loans' (+2,10)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_05a_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_05a_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        print '05b. Other loans' (+2,10)
                        graphic (,52,20) box 1 3 0
                        print #PARTC_05b_Number_SUM (,61) edit '999,999,999' 
                        graphic (,92,20) box 1 3 0
                        print #PARTC_05b_Amount_SUM (,98) edit '$$$,$$$,$$9.99' 
                        print '(including private loans)' (+1,15)
                        
                        new-page
                        
                        graphic (+1,1,180) box 2 0 10
                        print 'Part D - Enter Financial Aid About Group 3' (,1) bold
                        print $last_year (+2,50) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        print $yearminus2 (,90) bold
                        print '-' (,+1) bold
                        print $last_year (,+1) bold
                        print $yearminus3 (,130) bold
                        print '-' (,+1) bold
                        print $yearminus2 (,+1) bold
                        Print '01. Group 3' (+1,2) bold
                        print 'Of those in group 2, who paid the' (+1,6)
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print 'in-state or in-district tuition rate' (+1,6)
                        print 'and were awarded grant or' (+1,6)
                        print 'scholarship aid from the federal' (+1,6)
                        print 'government, state/local' (+1,6)
                        print 'government, or the institution.' (+1,6)
                        print 'Exclude other sources of grants.' (+1,6)
                        print '02. Report the number of Group 3' (+2,2) bold
                        print 'students with the following' (+1,6) bold
                        print 'living arrangements:' (+1,6) bold
                        print '02a. On-campus' (+2,6) 
                        graphic (,46,20) box 1 3 0
                        print #PARTD_02a_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print '02b. Off-campus (with family)' (+2,6) 
                        graphic (,46,20) box 1 3 0
                        print #PARTD_02b_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print '02c. Off-campus (not with family)' (+2,6) 
                        graphic (,46,20) box 1 3 0
                        print #PARTD_02c_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print '03.' (+2,2) bold
                        print 'Total grant or scholarship aid' (,+1)
                        graphic (,46,20) box 1 3 0
                        print #PARTD_03_SUM (,52) edit '$$$,$$$,$$9.99' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print 'from the federal government,' (+1,6)
                        print 'state/local government, or the' (+1,6)
                        print 'institution awarded to' (+1,6)
                        print 'students in Group 3. Exclude' (+1,6)
                        print 'other sources of grants.' (+1,6)
end-procedure


begin-procedure print_em_Parte
	
                        new-page
                        graphic (+1,1,180) box 2 0 10
                        print 'Part E - Enter Financial Aid About Group 4' (,1) bold
                        print $last_year (+3,50) bold
                        print '-' (,+1) bold
                        print $input_aidyear (,+1) bold
                        print $yearminus2 (,90) bold
                        print '-' (,+1) bold
                        print $last_year (,+1) bold
                        print $yearminus3 (,130) bold
                        print '-' (,+1) bold
                        print $yearminus2 (,+1) bold
                        Print 'Group 4' (+1,2) bold
                        print 'Of those in group 2, who paid the' (+1,6)
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print 'in-state or in-district tuition rate and' (+1,6)
                        print 'were awarded Title IV federal' (+1,6)
                        print 'student aid. (This number is carried' (+1,6)
                        print 'forward from Part A, Line 04.)' (+1,6)
                        
                        print '01. Report the number of group 4' (+2,2) bold
                        print 'students with the following' (+1,6) bold
                        print 'living arrangments:' (+1,6) bold
                        print '01a.' (+2,10) bold
                        print 'On-campus' (,+1)
                        graphic (,46,20) box 1 3 0
                        print #PARTE_1a_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print '01b.' (+2,10) bold
                        print 'Off-campus (with family)' (,+1)
                        graphic (,46,20) box 1 3 0
                        print #PARTE_1b_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        print '01b.' (+2,10) bold
                        print 'Off-campus (not with family)' (,+1)
                        graphic (,46,20) box 1 3 0
                        print #PARTE_1c_SUM (,55) edit '999,999,999' 
                        graphic (,86,20) box 1 3 0
                        graphic (,124,20) box 1 3 0
                        
                        print '02. Income Level' (+2,2) bold
                        print 'Col.1' (,53) bold
                        print 'Col.2' (,93) bold
                        print 'Col. 3' (,130) bold
                        print 'Number of Group 4' (+1,46)
                        print 'Number of Group 4 students who' (,82)
                        print 'Total amount of grant or' (,122)
                        print 'students who were' (+1,46)
                        print 'were awarded grant or scholarship aid' (,76)
                        print 'scholarship aid awarded to Group' (,120)
                        print 'awarded any Title IV aid' (+1,44)
                        print 'from the following sources: from the' (,79)
                        print '4 Students from the following'(,121)
                        print 'in' (+1,49)
                        print $Last_year (,+1)
                        print '-' (,+1)
                        print $input_aidyear (,+1)
                        print 'federal govt. state/local govt, or the' (,78)
                        print 'sources: from the federal govt'(,120)
                        print 'institution in' (+1,81)
                        print $Last_year (,+1)
                        print '-' (,+1)
                        print $input_aidyear (,+1)
                        print 'state/local govt, or the institution' (,119)
                        print 'in' (+1,126)
                        print $Last_year (,+1)
                        print '-' (,+1)
                        print $input_aidyear (,+1)
                        
                        
                        print '02a. $0-30,000' (+3,4) bold
                        graphic (,46,20) box 1 3 0
                        print #PARTE_2a_Col1_SUM (,55) edit '999,999,999'
                        graphic (,86,20) box 1 3 0
                        print #PARTE_2a_Col2_SUM (,95) edit '999,999,999'
                        graphic (,124,20) box 1 3 0
                        print #PARTE_2a_Col3_SUM (,130) edit '$$$,$$$,$$9.99' 
                        
                        print '02b. $30,001-48,000' (+3,4) bold
                        graphic (,46,20) box 1 3 0
                        print #PARTE_2b_Col1_SUM (,55) edit '999,999,999'
                        graphic (,86,20) box 1 3 0
                        print #PARTE_2b_Col2_SUM (,95) edit '999,999,999'
                        graphic (,124,20) box 1 3 0
                        print #PARTE_2b_Col3_SUM (,130) edit '$$$,$$$,$$9.99' 
                        
                        print '02c. $48,001-75,000' (+3,4) bold
                        graphic (,46,20) box 1 3 0
                        print #PARTE_2c_Col1_SUM (,55) edit '999,999,999'
                        graphic (,86,20) box 1 3 0
                        print #PARTE_2c_Col2_SUM (,95) edit '999,999,999'
                        graphic (,124,20) box 1 3 0
                        print #PARTE_2c_Col3_SUM (,130) edit '$$$,$$$,$$9.99' 
                        
                        print '02d. $75,001-110,000' (+3,4) bold
                        graphic (,46,20) box 1 3 0
                        print #PARTE_2d_Col1_SUM (,55) edit '999,999,999'
                        graphic (,86,20) box 1 3 0
                        print #PARTE_2d_Col2_SUM (,95) edit '999,999,999'
                        graphic (,124,20) box 1 3 0
                        print #PARTE_2d_Col3_SUM (,130) edit '$$$,$$$,$$9.99' 
                        
                        print '02e. $110,001 and up' (+3,4) bold
                        graphic (,46,20) box 1 3 0
                        print #PARTE_2e_Col1_SUM (,55) edit '999,999,999'
                        graphic (,86,20) box 1 3 0
                        print #PARTE_2e_Col2_SUM (,95) edit '999,999,999'
                        graphic (,124,20) box 1 3 0
                        print #PARTE_2e_Col3_SUM (,130) edit '$$$,$$$,$$9.99' 

end-procedure

!***********************************************************************
!*                                                                     *
!*  Add-CARES-Act                                                      *
!*                                                                     *
!***********************************************************************

Begin-Procedure Add-CARES-Act

    Let #CA_Amt = 0

BEGIN-SELECT
sum(ISF2.ITEM_AMT * -1) &ITEM_AMT

    Let #CA_Amt = &ITEM_AMT

FROM SYSADM.PS_ITEM_SF ISF2
WHERE ISF2.EMPLID = &A.EMPLID
  AND ISF2.BUSINESS_UNIT = $input_Institution
  AND ISF2.ITEM_TYPE in ('000012399995', '000012399996')
End-Select

End-Procedure Add-CARES-Act

!***********************************************************************
!  Select Parameters                                       
!***********************************************************************

Begin-Procedure Select-Parameters

    Let $yearminus3        = '2013'
    Let $yearminus2        = '2014'
    Let $Last_Year         = '2015'
    Let $Input_AidYear     = '2016'
    Let $input_Institution = 'UMS06'

begin-select
R.AID_YEAR,
R.INSTITUTION,
ltrim(to_char(to_number(R.AID_YEAR)- 1 ,'9999'),' ') &RLAST_YEAR
ltrim(to_char(to_number(R.AID_YEAR)- 2 ,'9999'),' ') &RYEARMINUS2
ltrim(to_char(to_number(R.AID_YEAR)- 3 ,'9999'),' ') &RYEARMINUS3

  Let $Last_Year = &RLAST_YEAR
  Let $Input_AIDYEAR = &R.AID_YEAR   
  Let $input_institution = &R.INSTITUTION    
  Let $yearminus2 = &Ryearminus2
  Let $yearminus3 = &Ryearminus3                  

FROM SYSADM.PS_UM_RUN_UMFAIPDS R
WHERE R.OPRID = $prcs_oprid
  AND R.RUN_CNTL_ID = $prcs_run_cntl_id

end-select

    show 'Selected Parameters'
    show $input_institution
    show $input_AIDYEAR
    show 'Calculated Parameters'
    Show $Last_Year
    Show $yearminus2
    Show $yearminus3
    show ' '

    do Pretty_INS

End-Procedure


!***********************************************************************
! Long Institution Name
!
!***********************************************************************
Begin-Procedure Pretty_INS

    evaluate $input_institution
	    when = 'UMS01'                      
		    Let $INSTITUTION_NAME = 'University of Maine at Augusta' 
		    break
	    when = 'UMS02'                      
    		Let $INSTITUTION_NAME = 'Univ of Maine at Farmington' 
	    	break
    	when = 'UMS03'                      
	    	Let $INSTITUTION_NAME = 'Univ of Maine at Fort Kent' 
		    break
	    when = 'UMS04'                     
       		Let $INSTITUTION_NAME = 'University of Maine at Machias' 
		    break
    	when = 'UMS05'                      
	    	Let $INSTITUTION_NAME = 'University of Maine' 
		    break
    	when = 'UMS06'                     
	    	Let $INSTITUTION_NAME = 'University of Southern Maine' 
		    break
	    when = 'UMS07'                      
		    Let $INSTITUTION_NAME = 'Univ of Maine at Presque Isle' 
		    break
    	when-other
	    	Let $INSTITUTION_NAME = $input_institution
    end-evaluate

end-procedure


!***********************************************************************
! SQL-Recover Procedure
!***********************************************************************
Begin-Procedure SQL-Recover

    show '$Calling_Procedure => ' $Calling_Procedure
    display $SQL-Error
    stop

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