!************************************************************************ !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 * ! REPORT-858 JAJ 02/11/2022 Add HEERF II logic * ! REPORT-860 JAJ 02/11/2022 Fix csv file headers * ! REPORT-883 JAJ 06/21/2022 Use cohort table for counting * ! FYR and EFY students * ! REPORT-905 JAJ 11/16/2022 Disaggregate Part B * !************************************************************************ #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 = 'Off-Campus' ! Substitute this for 'Parents' should be housing_code_1 = 2 Let $Off_CampusZ = 'Parents' ! Substitute this for 'Off-Campus' should be housing_code_1 = 3 if $prcs_process_instance = '' let $prcs_oprid = 'JJENSEN' 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,'|| !Column A 'CALC_FIRST_TIME,'|| !Column B 'CALC_FULL_PART,'|| !Column C 'CALC_DEGREE,'|| !Column D 'FIRST_TIME_FULL_TIME,'|| !Column E 'SUM_ACCEPT_OR_OFFER_AMOUNT,'|| !Column F 'DISBURSEMENT_PLAN,'|| !Column G 'FEDERAL_ID,'|| !Column H 'FA_SOURCE,'|| !Column I 'FIN_AID_TYPE,'|| !Column J 'STDNT_GROUP,'|| !Column K 'UM_HOUSE_CD_DESCR,'|| !Column L 'FISAP_TOT_INC,'|| !Column M 'I1Group1,'|| !Column N 'I1Group1a,'|| !Column O 'I2Group2,'|| !Column P 'I2aGroup2,'|| !Column Q 'I2bGroup2,'|| !Column R 'I3Group3,'|| !Column S 'I4Group4,'|| !Column T 'IB01_Num,'|| !Column U 'IB01_Amt,'|| !Column V 'IB01_Col5,'|| !Column W 'IB01_Col7,'|| !Column X 'IB02_Num,'|| !Column Y 'IB02_Amt,'|| !Column Z 'IB03_Num,'|| !Column AA 'IB03_Amt,'|| !Column AB 'IC04a,'|| !Column AC 'IC04b,'|| !Column AD 'IC04c,'|| !Column AE 'ICPage201,'|| !Column AF 'ICPage202,'|| !Column AG 'ICPage202a_Num,'|| !Column AH 'ICPage202a_Amt,'|| !Column AI 'ICPage202b_Num,'|| !Column AJ 'ICPage202b_Amt,'|| !Column AK 'ICPage203_Num,'|| !Column AL 'ICPage203_Amt,'|| !Column AM 'ICPage204_Num,'|| !Column AN 'ICPage204_Amt,'|| !Column AO 'ICPage205,'|| !Column AP 'ICPage205a_Num,'|| !Column AQ 'ICPage205a_Amt,'|| !Column AR 'ICPage205b_Num,'|| !Column AS 'ICPage205b_Amt,'|| !Column AT 'ID01,'|| !Column AU 'ID02a,'|| !Column AV 'ID02b,'|| !Column AW 'ID02c,'|| !Column AX 'ID03,' !Column AY let $ListString1 = $ListString1 || 'IE00,'|| !Column AZ 'IE01A,'|| !Column BA 'IE01B,'|| !Column BB 'IE01C,'|| !Column BC 'I02ACOL1,'|| !Column BD 'I02ACOL2,'|| !Column BE 'I02ACOL3,'|| !Column BF 'I02BCOL1,'|| !Column BG 'I02BCOL2,'|| !Column BH 'I02BCOL3,'|| !Column BI 'I02CCOL1,'|| !Column BJ 'I02CCOL2,'|| !Column BK 'I02CCOL3,'|| !Column BL 'I02DCOL1,'|| !Column BM 'I02DCOL2,'|| !Column BN 'I02DCOL3,'|| !Column BO 'I02ECOL1,'|| !Column BP 'I02ECOL2,'|| !Column BQ 'I02ECOL3,' !Column BR 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 SYSADM.PS_UM_STUD_CENS_VW A LEFT OUTER JOIN SYSADM.PS_UM_FIN_AID B ON A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.INSTITUTION = B.INSTITUTION LEFT OUTER JOIN SYSADM.PS_STDNT_GRPS C ON A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION LEFT OUTER JOIN SYSADM.PS_STDNT_AWARDS D ON 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 WHERE (A.STRM = ltrim(substr($input_aidyear,3,2),' ')||'10' AND A.ROW_CHECK = 'Y' AND UM_STUD_LEVEL_SUM = 'Undergraduate' AND B.AID_YEAR = $input_aidyear AND C.STDNT_GROUP = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_aidyear,3,2)) - 1 ,'99') ,' ')||'A' ! AND B.AID_YEAR(+) = $input_aidyear ! AND C.STDNT_GROUP(+) = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_aidyear,3,2)) - 1 ,'99') ,' ')||'A' 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%' AND ((A.INSTITUTION <> 'UMS06' AND UM_FULL_PART = 'Full-Time' !*********************** ! REPORT-883 changes * !*********************** ! 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 A.EMPLID IN (SELECT CA.EMPLID FROM SYSADM.PS_UM_STDNT_COHORT CA WHERE CA.UM_COHORT_CODE = 'RETENTION' AND CA.UM_COHORT_TYPE in ('FYR', 'EFY') AND CA.UM_COHORT_STRM = ltrim(substr($input_aidyear,3,2),' ')||'10') AND A.UM_DEGREE_NONDEG = 'Degree') ! 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') !*********************** ! REPORT-883 changes * !*********************** 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_1a = 0 Let #Group_1a_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_Col1 = 0 Let #PARTB_01_Col1_SUM = 0 Let #PARTB_01_Col3 = 0 Let #PARTB_01_Col3_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_ISF_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_ISFI_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_01 = ' ' !JAJ 860 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_4 = ' ' !JAJ 860 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 B.EMPLID, B.CALC_FIRST_TIME &CALC_FIRST_TIME, B.CALC_FULL_PART &CALC_FULL_PART, B.CALC_DEGREE &CALC_DEGREE, B.FIRST_TIME_FULL_TIME &FIRST_TIME_FULL_TIME, SUM(B.CALC_ACCEPT_OR_OFFER_AMOUNT) &SUM_ACCEPT_OR_OFFER_AMOUNT, RTRIM((listagg(DISTINCT B.DISBURSEMENT_PLAN,'/') WITHIN GROUP (ORDER BY B.FEDERAL_ID)), '/') &DISBURSEMENT_PLAN, RTRIM((listagg(DISTINCT B.FEDERAL_ID,'/') WITHIN GROUP (ORDER BY B.FEDERAL_ID)), '/') &FEDERAL_ID, RTRIM((listagg(DISTINCT B.FA_SOURCE,'/') WITHIN GROUP (ORDER BY B.FEDERAL_ID)), '/') &FA_SOURCE, RTRIM((listagg(DISTINCT B.FIN_AID_TYPE,'/') WITHIN GROUP (ORDER BY B.FEDERAL_ID)), '/') &FIN_AID_TYPE, RTRIM((listagg(DISTINCT B.STDNT_GROUP,'/') WITHIN GROUP (ORDER BY B.FEDERAL_ID)), '/') &STDNT_GROUP, RTRIM(REGEXP_REPLACE((listagg(DISTINCT B.UM_HOUSE_CD_DESCR,'/') WITHIN GROUP (ORDER BY B.UM_HOUSE_CD_DESCR)), '([^/]*)(/\1)+($|/)', '\1\3'), '/') &UM_HOUSE_CD_DESCR, SUM(B.TOTAL_INCOME) &TOTAL_INCOME, COUNT(DISTINCT B.emplid) &Group_1 , COUNT(DISTINCT CASE WHEN B.CALC_Degree = 'Degree' THEN B.emplid ELSE NULL END) &Group_1a , COUNT(DISTINCT CASE WHEN (B.institution <> 'UMS06' AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.CALC_Degree = 'Degree') OR B.First_time_full_time = 'Y' THEN B.emplid ELSE NULL END ) &Group2, COUNT(DISTINCT CASE WHEN (B.institution <> 'UMS06' AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.CALC_Degree = 'Degree' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.federal_id <> 'PLUS' ) OR ( B.institution = 'UMS06' AND B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.federal_id <> 'PLUS') THEN B.emplid ELSE NULL END ) &Group2a, COUNT(DISTINCT CASE WHEN ((B.institution <> 'UMS06' AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.CALC_Degree = 'Degree') OR B.First_time_full_time = 'Y' ) AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.federal_id <> 'PLUS' AND (( B.institution NOT IN ('UMS06', 'UMS02') AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'D', 'L', 'S', 'G', 'V')) OR (B.institution = 'UMS02' AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'L', 'S', 'G', 'V')) OR (B.institution = 'UMS06' AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'D', 'L', 'S', 'G', 'V', 'O')) ) THEN B.emplid ELSE NULL END ) &Group2b, COUNT(DISTINCT CASE WHEN B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 AND B.residency = 'IS' AND (( B.institution NOT IN ('UMS06', 'UMS02') AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'D', 'S', 'G', 'V')) OR ( B.institution = 'UMS02' AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'S', 'G', 'V')) OR (B.institution = 'UMS06' AND B.stdnt_group = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear, 3, 2)) - 1, '99'), ' ') || 'A' AND B.fa_source IN ('F', 'S', 'N', 'I', 'U') AND B.fin_aid_type IN ('A', 'D', 'S', 'G', 'V')) ) THEN B.emplid ELSE NULL END ) &Group3, COUNT(DISTINCT CASE WHEN ((B.institution <> 'UMS06' AND B.CALC_FIRST_TIME = 'Y' AND B.calc_full_part = 'Full-Time' AND B.CALC_Degree = 'Degree') OR B.First_time_full_time = 'Y' ) AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.residency = 'IS' AND FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') THEN B.emplid ELSE NULL END) &Group4, ! Start changes for REPORT-905 COUNT(DISTINCT CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS02' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','S','V','G')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS06' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G','O')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid else NULL END) &PARTB_01_Col1, COUNT(DISTINCT CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS02' and B.CALC_Degree = 'Degree' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','S','V','G')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS06' and B.CALC_Degree = 'Degree' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G','O')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid else NULL END) &PARTB_01_Col5, SUM(CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS02' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS06' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G','O')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT else NULL END) &PARTB_01_Col3, SUM(CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS02' and B.CALC_Degree = 'Degree' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS06' and B.CALC_Degree = 'Degree' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G','O')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT else NULL END) &PARTB_01_Col7, ! End changes for REPORT-905 COUNT(DISTINCT CASE WHEN B.federal_id = 'PELL' and B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' THEN B.emplid else NULL END) &PARTB_02_Number, SUM(CASE WHEN B.federal_id = 'PELL' and B.disbursement_plan > ' ' then B.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTB_02_Amount, COUNT(DISTINCT CASE WHEN B.FIN_AID_TYPE = 'L' and B.FA_SOURCE IN ('F','N') AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.FEDERAL_ID <> 'PLUS' then B.EMPLID else NULL end ) &PARTB_03_NUMBER, SUM(CASE WHEN B.FIN_AID_TYPE = 'L' and B.FA_SOURCE IN ('F','N') AND B.FEDERAL_ID <> 'PLUS' and B.disbursement_plan > ' ' then B.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 end ) &PARTB_03_AMOUNT, COUNT(DISTINCT CASE WHEN B.RESIDENCY = 'ID' THEN B.EMPLID ELSE NULL END) &PARTC_04a, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.CALC_FIRST_TIME = 'Y' and B.calc_full_part = 'Full-Time' and B.RESIDENCY = 'IS' ) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.RESIDENCY = 'IS') THEN B.EMPLID ELSE NULL END ) &PARTC_04b, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.CALC_FIRST_TIME = 'Y' and B.calc_full_part = 'Full-Time' and B.RESIDENCY <> 'IS' ) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.RESIDENCY <> 'IS' ) THEN B.EMPLID ELSE NULL END ) &PARTC_04c, COUNT(DISTINCT CASE WHEN B.INSTITUTION in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.CALC_FIRST_TIME = 'Y' and B.calc_full_part = 'Full-Time' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('F','S','I','N','U')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.EMPLID WHEN B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.CALC_FIRST_TIME = 'Y' and B.calc_full_part = 'Full-Time' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','S','V','G') and B.FA_SOURCE in ('F','S','I','N','U')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.EMPLID WHEN B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G','O') and B.FA_SOURCE in ('F','S','I','N','U')) OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.EMPLID else null end) &PARTC_01, COUNT(DISTINCT CASE WHEN B.INSTITUTION = 'UMS01' and ((B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.FA_SOURCE in ('F', 'N')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISF_AMT <> 0)) then B.emplid WHEN B.INSTITUTION = 'UMS06' and ((B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE in ('S','G','V') and B.FA_SOURCE in ('F','N')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISF_AMT <> 0)) then B.emplid WHEN B.INSTITUTION in ('UMS02', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and ((B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE in ('S','G','V') and B.FA_SOURCE in ('F','N')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISF_AMT <> 0)) then B.emplid ELSE null END) &PARTC_02, COUNT(DISTINCT CASE WHEN B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.federal_id = 'PELL' then B.emplid WHEN B.INSTITUTION in ('UMS01', 'UMS02', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.federal_id = 'PELL' then B.emplid ELSE null END) &PARTC_02a_Number, SUM(CASE WHEN B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.federal_id = 'PELL' then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.INSTITUTION in ('UMS01', 'UMS02', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.federal_id = 'PELL' then B.CALC_ACCEPT_OR_OFFER_AMOUNT ELSE null END) &PARTC_02a_Amount, COUNT(DISTINCT CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G') and B.FA_SOURCE in ('F','N') and B.FEDERAL_ID <> 'PELL') OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','S','V','G') and B.FA_SOURCE in ('F','N') and B.FEDERAL_ID <> 'PELL') OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid WHEN B.institution = 'UMS06' AND B.First_time_full_time = 'Y' AND ((B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' AND B.fin_aid_type in ('A','D','S','V','G','O') and B.FA_SOURCE in ('F','N') and B.FEDERAL_ID <> 'PELL') OR (B.ISF_AMT <> 0) OR (B.ISFI_AMT <> 0)) then B.emplid else NULL END) &PARTC_02b_Number, SUM(CASE WHEN B.institution in ('UMS01', 'UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.federal_id <> 'PELL' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') AND B.fin_aid_type in ('A','D','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.federal_id <> 'PELL' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') AND B.fin_aid_type in ('A','S','V','G')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.institution = 'UMS06' AND B.First_time_full_time = 'Y' and B.federal_id <> 'PELL' AND (B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') AND B.fin_aid_type in ('A','D','S','V','G','O')) then B.CALC_ACCEPT_OR_OFFER_AMOUNT else NULL END) &PARTC_02b_Amount, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('S','V','G') and B.FA_SOURCE in ('S')) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('S','V','G') and B.FA_SOURCE in ('S')) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.item_type = '000012000005') then B.emplid else null END) &PARTC_03_Number, SUM(CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('S','V','G') and B.FA_SOURCE in ('S')) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('S','V','G') and B.FA_SOURCE in ('S')) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.disbursement_plan > ' ' and B.item_type = '000012000005' ) then B.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_03_Amount, COUNT(DISTINCT CASE WHEN B.INSTITUTION = 'UMS01' and ((B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISFI_AMT <> 0)) then B.emplid WHEN B.INSTITUTION = 'UMS02' and ((B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.FIN_AID_TYPE IN ('A','S','V','G') and B.FA_SOURCE in ('I','U')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISFI_AMT <> 0)) then B.emplid WHEN B.INSTITUTION = 'UMS06' and ((B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISFI_AMT <> 0)) then B.emplid WHEN B.INSTITUTION in ('UMS03', 'UMS04', 'UMS05', 'UMS07') and ((B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U')) or (B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.ISFI_AMT <> 0)) then B.emplid ELSE null END) &PARTC_04_Number, SUM(CASE WHEN B.INSTITUTION = 'UMS01' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U') then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.FIN_AID_TYPE IN ('A', 'S', 'V', 'G') and B.FA_SOURCE in ('I','U') then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U') then B.CALC_ACCEPT_OR_OFFER_AMOUNT WHEN B.INSTITUTION in ('UMS03', 'UMS04', 'UMS05', 'UMS07') and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE IN ('A','D','S','V','G') and B.FA_SOURCE in ('I','U') then B.CALC_ACCEPT_OR_OFFER_AMOUNT ELSE null END) &PARTC_04_Amount, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') then B.emplid else null END) &PARTC_05_Number, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') then B.emplid else null END) &PARTC_05a_Number, SUM(CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.disbursement_plan > ' ' and B.FA_SOURCE in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') then B.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_05a_Amount , COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE not in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.FA_SOURCE not in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') then B.emplid else null END) &PARTC_05b_Number, SUM(CASE WHEN (B.INSTITUTION <> 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.disbursement_plan > ' ' and B.FA_SOURCE not in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.disbursement_plan > ' ' and B.FA_SOURCE not in ('F','N') and B.FIN_AID_TYPE = 'L' and B.FEDERAL_ID <> 'PLUS') then B.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTC_05b_Amount, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.INSTITUTION <> 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = 'On-Campus') or (B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = 'On-Campus') or (B.INSTITUTION = 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = 'On-Campus') then B.emplid else null END) &PARTD_02a, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.INSTITUTION <> 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ('A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Parentz) or (B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Parentz) or (B.INSTITUTION = 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Parentz) then B.emplid else null END) &PARTD_02b, COUNT(DISTINCT CASE WHEN (B.INSTITUTION <> 'UMS06' and B.INSTITUTION <> 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Off_CampusZ) or (B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Off_CampusZ) or (B.INSTITUTION = 'UMS06' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.First_time_full_time = 'Y' AND B.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' and B.strm = B.fin_aid_strm and B.um_house_cd_descr = $Off_CampusZ) then B.emplid else null END) &PARTD_02c, SUM(CASE WHEN (B.INSTITUTION <> 'UMS06' and B.INSTITUTION <> 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' ) or (B.INSTITUTION = 'UMS02' and B.CALC_Degree = 'Degree' and B.calc_full_part = 'Full-Time' and B.CALC_FIRST_TIME = 'Y' and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' ) or (B.INSTITUTION = 'UMS06' and B.First_time_full_time = 'Y' and B.disbursement_plan > ' ' and B.fin_aid_type in ( 'A','D','G','S','V') and B.fa_source in ( 'F','I','N','S','U') and B.residency = 'IS' ) then B.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_1a = &Group_1a Let #Group_1a_SUM = #Group_1a_SUM + &Group_1a 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 ! Part B Let #PARTB_01_Col1 = &PARTB_01_Col1 Let #PARTB_01_Col1_SUM = #PARTB_01_Col1_SUM + &PARTB_01_Col1 Let #PARTB_01_Col3 = &PARTB_01_Col3 + #CA_Amt + #CA_Inst_Amt !JAJ 858 Let #PARTB_01_Col3_SUM = #PARTB_01_Col3_SUM + #PARTB_01_Col3 !JAJ 858 Let #PARTB_01_Col5 = &PARTB_01_Col5 Let #PARTB_01_Col5_SUM = #PARTB_01_Col5_SUM + &PARTB_01_Col5 If &CALC_DEGREE = 'Degree' Let #PARTB_01_Col7 = &PARTB_01_Col7 + #CA_Amt + #CA_Inst_Amt !JAJ 858 Else Let #PARTB_01_Col7 = 0 !JAJ 858 End-If Let #PARTB_01_Col7_SUM = #PARTB_01_Col7_SUM + #PARTB_01_Col7 !JAJ 858 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 ! Part C 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 Evaluate $input_Institution When = 'UMS06' If &FIRST_TIME_FULL_TIME = 'Y' Let #PARTC_02b_Amount = &PARTC_02b_Amount + #CA_Amt Else Let #PARTC_02b_Amount = 0 End-If Break When-Other If &CALC_DEGREE = 'Degree' and &CALC_FULL_PART = 'Full-Time' and &CALC_FIRST_TIME = 'Y' Let #PARTC_02b_Amount = &PARTC_02b_Amount + #CA_Amt Else Let #PARTC_02b_Amount = 0 End-If Break End-Evaluate Let #PARTC_02b_Amount_SUM = #PARTC_02b_Amount_SUM + #PARTC_02b_Amount !JAJ 858 If &FIRST_TIME_FULL_TIME = 'Y' or (&CALC_DEGREE = 'Degree' and &CALC_FULL_PART = 'Full-Time' and &CALC_FIRST_TIME = 'Y') Show 'B.EMPLID = ' &B.EMPLID Show 'B.INSTITUTION = ' $input_Institution Show 'B.FIRST_TIME_FULL_TIME = ' &FIRST_TIME_FULL_TIME Show 'B.CALC_DEGREE = ' &CALC_DEGREE Show 'B.CALC_FULL_PART = ' &CALC_FULL_PART Show 'B.CALC_FIRST_TIME = ' &CALC_FIRST_TIME Show '&PARTC_02b_Number = ' &PARTC_02b_Number Show '&PARTC_02b_Amount = ' &PARTC_02b_Amount Show '#CA_Amt = ' #CA_Amt Show '#CA_Inst_Amt = ' #CA_Inst_Amt Show '=' Show '#PARTC_02b_Amount = ' #PARTC_02b_Amount Show '#PARTC_02b_Amount_SUM = ' #PARTC_02b_Amount_SUM Show ' ' 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 !JAJ 858 Let #PARTC_03_Amount_SUM = #PARTC_03_Amount_SUM + &PARTC_03_Amount !JAJ 858 Let #PARTC_04_Number = &PARTC_04_Number Let #PARTC_04_Number_SUM = #PARTC_04_Number_SUM + &PARTC_04_Number Evaluate $input_Institution When = 'UMS06' If &FIRST_TIME_FULL_TIME = 'Y' Let #PARTC_04_Amount = &PARTC_04_Amount + #CA_Inst_Amt Else Let #PARTC_04_Amount = 0 End-If Break When-Other If &CALC_DEGREE = 'Degree' and &CALC_FULL_PART = 'Full-Time' and &CALC_FIRST_TIME = 'Y' Let #PARTC_04_Amount = &PARTC_04_Amount + #CA_Inst_Amt Else Let #PARTC_04_Amount = 0 End-If Break End-Evaluate Let #PARTC_04_Amount_SUM = #PARTC_04_Amount_SUM + #PARTC_04_Amount !JAJ 858 If &B.EMPLID = '1110593' Show 'B.EMPLID = ' &B.EMPLID Show 'B.INSTITUTION = ' $input_Institution Show 'B.CALC_FIRST_TIME = ' &CALC_FIRST_TIME Show 'B.CALC_FULL_PART = ' &CALC_FULL_PART Show 'B.CALC_DEGREE = ' &CALC_DEGREE Show 'B.FIRST_TIME_FULL_TIME = ' &FIRST_TIME_FULL_TIME Show 'B.FA_SOURCE = ' &FA_SOURCE Show '&SUM_ACCEPT_OR_OFFER_AMOUNT = ' &SUM_ACCEPT_OR_OFFER_AMOUNT Show 'B.DISBURSEMENT_PLAN = ' &DISBURSEMENT_PLAN Show 'B.FIN_AID_TYPE = ' &FIN_AID_TYPE Show '#CA_Inst_Amt = ' #CA_Inst_Amt Show '&PARTC_02b_Amount = ' &PARTC_02b_Amount Show '#CA_Amt = ' #CA_Amt Show '#PARTC_02b_Amount = ' #PARTC_02b_Amount Show '#PARTC_02b_Amount_SUM = ' #PARTC_02b_Amount_SUM Show ' ' End-If 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 ! Part D 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 = &B.EMPLID Let $Liststring1 = '"'||$in_emplid||'"'||','|| !Column A '"'||&CALC_FIRST_TIME||'"'||','|| !Column B '"'||&CALC_FULL_PART||'"'||','|| !Column C '"'||&CALC_DEGREE||'"'||','|| !Column D '"'||&FIRST_TIME_FULL_TIME||'"'||','|| !Column E edit(&SUM_ACCEPT_OR_OFFER_AMOUNT,'999999999')||','|| !Column F '"'|| &DISBURSEMENT_PLAN||'"'||','|| !Column G '"'|| &FEDERAL_ID||'"'||','|| !Column H '"'|| &FA_SOURCE||'"'||','|| !Column I '"'|| &FIN_AID_TYPE||'"'||','|| !Column J '"'||&STDNT_GROUP||'"'||','|| !Column K '"'||&UM_HOUSE_CD_DESCR||'"'||','|| !Column L edit(&TOTAL_INCOME,'999999999')||','|| !Column M edit(#Group_1,'9999999')||','|| !Column N edit(#Group_1a,'9999999')||','|| !Column O edit(#Group_2,'9999999')||','|| !Column P edit(#Group2a,'9999999')||','|| !Column Q edit(#Group2b,'9999999')||','|| !Column R edit(#Group3,'9999999')||','|| !Column S edit(#Group4,'9999999')||','|| !Column T edit(#PARTB_01_Col1,'9999999')||','|| !Column U edit(#PARTB_01_Col3,'9999999.99')||','|| !Column V edit(#PARTB_01_Col5,'9999999')||','|| !Column W edit(#PARTB_01_Col7,'9999999.99')||','|| !Column X edit(#PARTB_02_Number,'9999999')||','|| !Column Y edit(#PARTB_02_Amount,'9999999.99')||','|| !Column Z edit(#PARTB_03_Number,'9999999')||','|| !Column AA edit(#PARTB_03_Amount,'9999999.99')||','|| !Column AB edit(#PARTC_04a,'9999999')||','|| !Column AC edit(#PARTC_04b,'9999999')||','|| !Column AD edit(#PARTC_04c,'9999999')||','|| !Column AE edit(#PARTC_01,'9999999')||','|| !Column AF edit(#PARTC_02,'9999999')||','|| !Column AG edit(#PARTC_02a_Number,'9999999')||','|| !Column AH edit(#PARTC_02a_Amount,'9999999.99')||','|| !Column AI edit(#PARTC_02b_Number,'9999999')||','|| !Column AJ edit(#PARTC_02b_Amount,'9999999.99')||','|| !Column AK edit(#PARTC_03_Number,'9999999')||','|| !Column AL edit(#PARTC_03_Amount,'9999999.99')||','|| !Column AM edit(#PARTC_04_Number,'9999999')||','|| !Column AN edit(#PARTC_04_Amount,'9999999.99')||','|| !Column AO edit(#PARTC_05_Number,'9999999')||','|| !Column AP edit(#PARTC_05a_Number,'9999999')||','|| !Column AQ edit(#PARTC_05a_Amount,'9999999.99')||','|| !Column AR edit(#PARTC_05b_Number,'9999999')||','|| !Column AS edit(#PARTC_05b_Amount,'9999999.99')||','|| !Column AT $PARTD_01||','|| !Column AU edit(#PARTD_02a,'9999999')||','|| !Column AV edit(#PARTD_02b,'9999999')||','|| !Column AW edit(#PARTD_02c,'9999999')||','|| !Column AX edit(#PARTD_03,'9999999')||',' !Column AY do Count_Students_Income ! in-line view is similar to query UMS_AL_IPEDS_AY2016_DATA it is referenced as A FROM (SELECT A.EMPLID, A.INSTITUTION, A.STRM, A.RESIDENCY, A.AID_YEAR, A.OFFER_AMOUNT, A.ACCEPT_AMOUNT, A.FIN_AID_TYPE, A.CALC1_Undergraduate_etc, A.CALC_ACCEPT_OR_OFFER_AMOUNT, A.TOTAL_INCOME, A.ADMIT_TERM, A.UM_LAST_SCH_TYPE, A.ACAD_LEVEL_BOT, A.ADMIT_TYPE, A.GRADUATION_DT, A.ACAD_PROG_PRIMARY, A.UM_UNT_TAK_PRG_RC, A.FA_SOURCE, A.FEDERAL_ID, A.CALC_Degree, A.CALC_FIRST_TIME, A.CALC_FULL_PART, A.ITEM_TYPE, A.ITEM_TYPE_DESCR, A.STDNT_GROUP, A.PROG_ACTION, A.PROG_REASON, A.ACAD_PLAN, A.UM_HOUSE_CD_DESCR, A.HOUSING_CODE_1, A.DISBURSEMENT_PLAN, A.FIN_AID_STRM, A.First_time_full_time, SE.ISF_AMT, SF.ISFI_AMT FROM (SELECT SC.EMPLID, SC.INSTITUTION, SC.STRM, SC.RESIDENCY, SB.AID_YEAR, SB.OFFER_AMOUNT, SB.ACCEPT_AMOUNT, SB.FIN_AID_TYPE, SC.UM_STUD_LEVEL_SUM CALC1_Undergraduate_etc, CASE WHEN SB.FIN_AID_TYPE IS NULL THEN 0 WHEN SB.FIN_AID_TYPE = 'L' THEN SB.ACCEPT_AMOUNT ELSE SB.OFFER_AMOUNT END CALC_ACCEPT_OR_OFFER_AMOUNT, SB.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') GRADUATION_DT, SC.ACAD_PROG_PRIMARY, SC.UM_UNT_TAK_PRG_RC, SB.FA_SOURCE, SB.FEDERAL_ID, SC.UM_DEGREE_NONDEG CALC_Degree, CASE WHEN SC.EMPLID IN (SELECT CB.EMPLID FROM SYSADM.PS_UM_STDNT_COHORT CB WHERE CB.UM_COHORT_CODE = 'RETENTION' AND CB.UM_COHORT_TYPE in ('FYR', 'EFY') AND CB.UM_COHORT_STRM = ltrim(substr($input_Aidyear,3,2),' ')||'10') THEN 'Y' ELSE 'N' END CALC_FIRST_TIME, SC.UM_FULL_PART CALC_FULL_PART, SB.ITEM_TYPE, SB.ITEM_TYPE_DESCR, SG.STDNT_GROUP, SC.PROG_ACTION, SC.PROG_REASON, SC.ACAD_PLAN, SB.UM_HOUSE_CD_DESCR, SB.HOUSING_CODE_1, SD.DISBURSEMENT_PLAN, SB.STRM FIN_AID_STRM, SC.ACAD_YEAR, 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 FROM SYSADM.PS_UM_STUD_CENS_VW SC LEFT OUTER JOIN (SELECT UFA.AID_YEAR, UFA.OFFER_AMOUNT, UFA.ACCEPT_AMOUNT, UFA.FIN_AID_TYPE, UFA.FISAP_TOT_INC TOTAL_INCOME, UFA.FA_SOURCE, UFA.FEDERAL_ID, UFA.ITEM_TYPE, UFA.ITEM_TYPE_DESCR, UFA.UM_HOUSE_CD_DESCR, UFA.HOUSING_CODE_1, UFA.STRM, UFA.ACAD_CAREER, UFA.INSTITUTION, UFA.EMPLID FROM SYSADM.PS_UM_FIN_AID UFA WHERE UFA.ITEM_TYPE_DESCR NOT LIKE '%Summer%' AND UFA.AID_YEAR = $input_Aidyear) SB ON SC.EMPLID = SB.EMPLID AND SC.ACAD_CAREER = SB.ACAD_CAREER AND SC.INSTITUTION = SB.INSTITUTION LEFT OUTER JOIN SYSADM.PS_STDNT_GRPS SG ON 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' LEFT OUTER JOIN (SELECT SA.EMPLID, SA.AID_YEAR, SA.INSTITUTION, SA.ITEM_TYPE, SA.ACAD_CAREER, SA.DISBURSEMENT_PLAN FROM SYSADM.PS_STDNT_AWARDS SA WHERE SA.AID_YEAR = $input_Aidyear AND SA.INSTITUTION = $input_Institution AND (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'))) ) SD ON SB.EMPLID = SD.EMPLID AND SB.AID_YEAR = SD.AID_YEAR AND SB.INSTITUTION = SD.INSTITUTION AND SB.ITEM_TYPE = SD.ITEM_TYPE AND SB.ACAD_CAREER = SD.ACAD_CAREER WHERE SC.STRM = substr($input_Aidyear,3,2)||'10' AND SC.UM_STUD_LEVEL_SUM = 'Undergraduate' AND SC.INSTITUTION = $input_Institution) A LEFT OUTER JOIN (SELECT ISF.EMPLID, ISF.BUSINESS_UNIT, ISF.ACAD_YEAR, sum(ISF.ITEM_AMT * -1) ISF_AMT FROM SYSADM.PS_ITEM_SF ISF WHERE ISF.BUSINESS_UNIT = $input_Institution AND ISF.ACAD_YEAR = $input_Aidyear AND ((ISF.BUSINESS_UNIT = 'UMS01' AND ISF.ITEM_TYPE in ('000012399985', '000012399986', '000012399990', '000012399991', '000012399995', '000012399996', '000012399997')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS02' AND ISF.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399991', '000012399985', '000012399986', '000000490055')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS03' AND ISF.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399997', '000012399985', '000012399986')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS04' AND ISF.ITEM_TYPE in ('000012399995', '000012399996', '000012399988', '000012399989', '000012399990', '000012399991', '000012399983', !JAJ 858 '000012399984', '000012399985', '000012399986')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS05' AND ISF.ITEM_TYPE in ('000012399995', '000012399996', '000012399988', '000012399989', '000012399990', '000012399991', '000012399983', !JAJ 858 '000012399984', '000012399985', '000012399986')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS06' AND ISF.ITEM_TYPE in ('000012399984', '000012399985', '000012399986', '000012399987', '000012399988', '000012399989', '000012399990', !JAJ 858 '000012399991', '000012399993', '000012399994', '000012399995', '000012399996', '000012399997', '000012399998', !JAJ 858 '000012399999')) !JAJ 858 OR (ISF.BUSINESS_UNIT = 'UMS07' AND ISF.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399991', '000012399985', '000012399986'))) !JAJ 858 GROUP BY ISF.EMPLID, ISF.BUSINESS_UNIT, ISF.ACAD_YEAR) SE ON A.EMPLID = SE.EMPLID AND A.INSTITUTION = SE.BUSINESS_UNIT AND A.ACAD_YEAR = SE.ACAD_YEAR LEFT OUTER JOIN (SELECT ISFI.EMPLID, ISFI.BUSINESS_UNIT, ISFI.ACAD_YEAR, sum(ISFI.ITEM_AMT * -1) ISFI_AMT FROM SYSADM.PS_ITEM_SF ISFI WHERE ISFI.BUSINESS_UNIT = $input_Institution AND ISFI.ACAD_YEAR = $input_Aidyear AND (ISFI.BUSINESS_UNIT = 'UMS01' AND ISFI.ITEM_TYPE in ('000006000035')) !JAJ 858 GROUP BY ISFI.EMPLID, ISFI.BUSINESS_UNIT, ISFI.ACAD_YEAR) SF ON A.EMPLID = SF.EMPLID AND A.INSTITUTION = SF.BUSINESS_UNIT AND A.ACAD_YEAR = SF.ACAD_YEAR ) B GROUP BY B.EMPLID, B.CALC_FIRST_TIME, B.CALC_FULL_PART, B.CALC_DEGREE, B.FIRST_TIME_FULL_TIME 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 AJ.EMPLID, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = 'On-Campus' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = 'On-Campus' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG','FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AJ.emplid else NULL END) &PARTE_1a, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = $Parentz ! was AJ.um_house_cd_descr = $Parentz AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = $Parentz AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AJ.emplid else NULL END) &PARTE_1b, ! Off-campus with family COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = $Off_Campusz ! was AJ.um_house_cd_descr = $Off_Campusz AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.strm = AJ.fin_aid_strm and AJ.um_house_cd_descr = $Off_Campusz AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH')) THEN AJ.emplid else NULL END) &PARTE_1c, ! Off-campus not with family COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME < 30000) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME <= 30000) THEN AJ.emplid else NULL END) &PARTE_2a_Col1, ! These counts check group4 COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) or (AJ.INSTITUTION = 'UMS02' and AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) or (AJ.INSTITUTION = 'UMS06' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) THEN AJ.EMPLID else NULL END) &PARTE_2a_Col2, SUM(CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) or (AJ.INSTITUTION = 'UMS02' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) or (AJ.INSTITUTION = 'UMS06' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME < 30001) THEN AJ.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTE_2a_Col3, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME <= 48000) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) THEN AJ.emplid else NULL END) &PARTE_2b_Col1, COUNT( DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) or (AJ.INSTITUTION = 'UMS02' and AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) or (AJ.INSTITUTION = 'UMS06' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) THEN AJ.EMPLID else NULL END) &PARTE_2b_Col2, SUM(CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) or (AJ.INSTITUTION = 'UMS02' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) or (AJ.INSTITUTION = 'UMS06' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 30000 AND AJ.TOTAL_INCOME < 48001) THEN AJ.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTE_2b_Col3, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) THEN AJ.emplid else NULL END) &PARTE_2c_Col1, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) or (AJ.INSTITUTION = 'UMS02' and AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) or (AJ.INSTITUTION = 'UMS06' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) THEN AJ.EMPLID else NULL END) &PARTE_2c_Col2, SUM(CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) or (AJ.INSTITUTION = 'UMS02' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) or (AJ.INSTITUTION = 'UMS06' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 48000 AND AJ.TOTAL_INCOME < 75001) THEN AJ.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTE_2c_Col3, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) THEN AJ.emplid else NULL END) &PARTE_2d_Col1, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) or (AJ.INSTITUTION = 'UMS02' and AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) or (AJ.INSTITUTION = 'UMS06' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) THEN AJ.EMPLID else NULL END) &PARTE_2d_Col2, SUM(CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) or (AJ.INSTITUTION = 'UMS02' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) or (AJ.INSTITUTION = 'UMS06' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 75000 AND AJ.TOTAL_INCOME < 110001) THEN AJ.CALC_ACCEPT_OR_OFFER_AMOUNT else 0 END) &PARTE_2d_Col3, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.CALC_Degree = 'Degree' and AJ.CALC_FIRST_TIME = 'Y' and AJ.calc_full_part = 'Full-Time' and AJ.RESIDENCY = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 110000) or (AJ.INSTITUTION = 'UMS06' and AJ.First_time_full_time = 'Y' and AJ.residency = 'IS' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FEDERAL_ID IN ('PERK', 'PELL', 'SEOG', 'FWSP', 'STFU', 'STFS', 'ACG', 'SMRT', 'TCH') AND AJ.TOTAL_INCOME > 110000) THEN AJ.emplid else NULL END) &PARTE_2e_Col1, COUNT(DISTINCT CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) or (AJ.INSTITUTION = 'UMS02' and AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) or (AJ.INSTITUTION = 'UMS06' AND AJ.CALC_ACCEPT_OR_OFFER_AMOUNT > 0 and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) THEN AJ.EMPLID else NULL END) &PARTE_2e_Col2, SUM(CASE WHEN (AJ.INSTITUTION <> 'UMS06' and AJ.INSTITUTION <> 'UMS02' AND AJ.FLAG = 'X' AND AJ.disbursement_plan > ' ' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) or (AJ.INSTITUTION = 'UMS02' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) or (AJ.INSTITUTION = 'UMS06' and AJ.disbursement_plan > ' ' AND AJ.FLAG = 'X' and AJ.FIN_AID_TYPE IN ('A','D','S','G','V') and AJ.FA_SOURCE in ('F','I','N','U','S') AND AJ.TOTAL_INCOME > 110000) THEN AJ.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 || $PARTE_4||','|| !Column AZ edit(#PARTE_1a,'9999999')||','|| !Column BA edit(#PARTE_1b,'9999999')||','|| !Column BB edit(#PARTE_1c,'9999999')||','|| !Column BC edit(#PARTE_2a_Col1,'9999999')||','|| !Column BD edit(#PARTE_2a_Col2,'9999999')||','|| !Column BE edit(#PARTE_2a_Col3,'9999999.99')||','|| !Column BF edit(#PARTE_2b_Col1,'9999999')||','|| !Column BG edit(#PARTE_2b_Col2,'9999999')||','|| !Column BH edit(#PARTE_2b_Col3,'9999999.99')||','|| !Column BI edit(#PARTE_2c_Col1,'9999999')||','|| !Column BJ edit(#PARTE_2c_Col2,'9999999')||','|| !Column BK edit(#PARTE_2c_Col3,'9999999.99')||','|| !Column BL edit(#PARTE_2d_Col1,'9999999')||','|| !Column BM edit(#PARTE_2d_Col2,'9999999')||','|| !Column BN edit(#PARTE_2d_Col3,'9999999.99')||','|| !Column BO edit(#PARTE_2e_Col1,'9999999')||','|| !Column BP edit(#PARTE_2e_Col2,'9999999')||','|| !Column BQ edit(#PARTE_2e_Col3,'9999999.99') !Column BR write 1 from $ListString1 ! first in-line view is similar to query UMS_AL_IPEDS_AY2016_DATA it is AA FROM (SELECT SCV.EMPLID, SCV.INSTITUTION, SCV.STRM, SCV.RESIDENCY, UFAB.AID_YEAR, UFAB.OFFER_AMOUNT, UFAB.ACCEPT_AMOUNT, UFAB.FIN_AID_TYPE, SCV.UM_STUD_LEVEL_SUM CALC1_Undergraduate_etc, CASE WHEN UFAB.FIN_AID_TYPE IS NULL THEN 0 WHEN UFAB.FIN_AID_TYPE = 'L' THEN UFAB.ACCEPT_AMOUNT ELSE UFAB.OFFER_AMOUNT END CALC_ACCEPT_OR_OFFER_AMOUNT, UFAB.TOTAL_INCOME, SCV.ADMIT_TERM, SCV.UM_LAST_SCH_TYPE, SCV.ACAD_LEVEL_BOT, SCV.ADMIT_TYPE, TO_CHAR(SCV.GRADUATION_DT,'YYYY-MM-DD') GRADUATION_DT, SCV.ACAD_PROG_PRIMARY, SCV.UM_UNT_TAK_PRG_RC, UFAB.FA_SOURCE, UFAB.FEDERAL_ID, !*********************** ! REPORT-883 changes * !*********************** SCV.UM_DEGREE_NONDEG CALC_Degree, ! CASE ! WHEN SCV.FA_ELIGIBILITY = 'Y' THEN 'Degree' ! WHEN SCV.FA_ELIGIBILITY = 'N' AND SCV.INSTITUTION = 'UMS01' AND SCV.ACAD_PROG_PRIMARY = 'BSAVI' THEN 'Degree' ! ELSE 'Non-Degree' ! END CALC_Degree, ! ,CASE WHEN (SCV.ADMIT_TERM = ltrim(substr($input_Aidyear, 3, 2), ' ') || '10' ! OR SCV.ADMIT_TERM = ltrim(to_char(to_number(substr($input_Aidyear, 3, 2)) - 1, '99'), ' ') || '30') ! AND (SCV.ADMIT_TYPE = 'FYR' ! OR SCV.ADMIT_TYPE = 'EFY') ! AND (SCV.UM_LAST_SCH_TYPE = 'SCD' ! OR SCV.UM_LAST_SCH_TYPE = 'SHS' ! OR SCV.ACAD_LEVEL_BOT = '10' ! OR SCV.ACAD_LEVEL_BOT = '01') ! AND SCV.PROG_REASON <> 'RADM' THEN 'Y' ! ELSE 'N' ! END CALC_FIRST_TIME CASE WHEN SCV.EMPLID IN (SELECT CC.EMPLID FROM SYSADM.PS_UM_STDNT_COHORT CC WHERE CC.UM_COHORT_CODE = 'RETENTION' AND CC.UM_COHORT_TYPE in ('FYR', 'EFY') AND CC.UM_COHORT_STRM = ltrim(substr($input_aidyear,3,2),' ')||'10') THEN 'Y' ELSE 'N' END CALC_FIRST_TIME, !*********************** ! REPORT-883 changes * !*********************** SCV.UM_FULL_PART CALC_FULL_PART, UFAB.ITEM_TYPE, UFAB.ITEM_TYPE_DESCR, SG1.STDNT_GROUP, SCV.PROG_ACTION, SCV.PROG_REASON, SCV.ACAD_PLAN, UFAB.UM_HOUSE_CD_DESCR, UFAB.HOUSING_CODE_1, DA.DISBURSEMENT_PLAN, UFAB.STRM FIN_AID_STRM, GRP4.FLAG, CASE WHEN SCV.INSTITUTION = 'UMS06' AND SG1.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 SYSADM.PS_UM_STUD_CENS_VW SCV LEFT OUTER JOIN (SELECT UFA1.AID_YEAR, UFA1.OFFER_AMOUNT, UFA1.ACCEPT_AMOUNT, UFA1.FIN_AID_TYPE, UFA1.FISAP_TOT_INC TOTAL_INCOME, UFA1.FA_SOURCE, UFA1.FEDERAL_ID, UFA1.ITEM_TYPE, UFA1.ITEM_TYPE_DESCR, UFA1.UM_HOUSE_CD_DESCR, UFA1.HOUSING_CODE_1, UFA1.STRM, UFA1.ACAD_CAREER, UFA1.INSTITUTION, UFA1.EMPLID FROM SYSADM.PS_UM_FIN_AID UFA1 WHERE UFA1.ITEM_TYPE_DESCR NOT LIKE '%Summer%') UFAB ON SCV.EMPLID = UFAB.EMPLID AND SCV.ACAD_CAREER = UFAB.ACAD_CAREER AND SCV.INSTITUTION = UFAB.INSTITUTION AND UFAB.AID_YEAR = $input_Aidyear LEFT OUTER JOIN SYSADM.PS_STDNT_GRPS SG1 ON SCV.EMPLID = SG1.EMPLID AND SCV.INSTITUTION = SG1.INSTITUTION AND SG1.STDNT_GROUP = 'C'||LTRIM(TO_CHAR(TO_NUMBER(SUBSTR($input_Aidyear,3,2)) - 1,'99'),' ')||'A' LEFT OUTER JOIN (SELECT ASCV.EMPLID, ASCV.AID_YEAR, ASCV.INSTITUTION, ASCV.ITEM_TYPE, ASCV.ACAD_CAREER, ASCV.DISBURSEMENT_PLAN FROM SYSADM.PS_STDNT_AWARDS ASCV WHERE (NOT (ASCV.INSTITUTION = 'UMS01' AND ASCV.DISBURSEMENT_PLAN IN ('D5','DR','OR','RO')) AND NOT (ASCV.INSTITUTION = 'UMS02' AND ASCV.DISBURSEMENT_PLAN IN ('DR','RO')) AND NOT (ASCV.INSTITUTION = 'UMS03' AND ASCV.DISBURSEMENT_PLAN IN ('D2','DR','RO')) AND NOT (ASCV.INSTITUTION = 'UMS04' AND ASCV.DISBURSEMENT_PLAN IN ('D3','DR','RO')) AND NOT (ASCV.INSTITUTION = 'UMS05' AND ASCV.DISBURSEMENT_PLAN IN ('DR','RO','R1','R2','R3','RP')) AND NOT (ASCV.INSTITUTION = 'UMS06' AND ASCV.DISBURSEMENT_PLAN IN ('D2','DR','PX','RO','R1')) AND NOT (ASCV.INSTITUTION = 'UMS07' AND ASCV.DISBURSEMENT_PLAN IN ('DR','RO','P2')))) DA ON UFAB.EMPLID = DA.EMPLID AND UFAB.AID_YEAR = DA.AID_YEAR AND UFAB.INSTITUTION = DA.INSTITUTION AND UFAB.ITEM_TYPE = DA.ITEM_TYPE AND UFAB.ACAD_CAREER = DA.ACAD_CAREER LEFT OUTER JOIN SYSADM.PS_UM_UMGROUP4 GRP4 ON DA.INSTITUTION = GRP4.INSTITUTION AND DA.EMPLID = GRP4.EMPLID WHERE SCV.STRM = substr($input_Aidyear, 3, 2) || '10' AND SCV.ROW_CHECK = 'Y' AND SCV.UM_STUD_LEVEL_SUM = 'Undergraduate') AJ WHERE AJ.INSTITUTION = $input_institution AND AJ.EMPLID = $in_emplid group by AJ.EMPLID END-SELECT End-Procedure ! List_aid guys Income begin-procedure print_em ! New headers \/ graphic (+1,1,180) box 2 0 10 print 'YOUR' (,160) graphic (+1,1,180) box 2 0 10 print 'FALL ' (,140) Print $Last_year (,+1) print 'PRIOR YEAR' (,160) graphic (+1,1,180) box 2 0 10 print 'DATA' (,160) graphic (+2,1,180) box 2 0 10 print ' 01 ' (,1) print 'Group 1' (,7) graphic (+1,1,180) box 2 0 10 print 'All' (,7) print 'undergraduate' (,+1) Underline print 'students' (,+1) graphic (,140,9) box 1 3 0 Print #Group_1_SUM (,140) edit '999,999' graphic (+2,1,180) box 2 0 10 print '01a' (,7) print 'Of those in Group 1, those who are degree/certificate-seeking' (,+5) graphic (,140,9) box 1 3 0 Print #Group_1a_SUM (,140) edit '999,999' graphic (+2,1,180) box 2 0 10 print '01b' (,7) print 'Of those in Group 1, those who are non-degree/non-certificate-seeking (Line 01 - Line 01a)' (,+5) ! New headers /\ 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 ! Remove eventually \/ graphic (+4,1,180) box 2 0 10 print 'Part B - Enter Financial Aid About Group 1' (,1) bold ! Remove eventually /\ ! New headers \/ graphic (+2,1,180) box 2 0 10 Print $last_year (,84) bold print '-' (,+1) bold print $input_aidyear (,+1) bold graphic (+1,1,180) box 2 0 10 print 'All undergraduate students (Group 1)' (,32) bold print 'All degree/certificate-seeking undergraduates' (,70) bold print 'All non-degree/non-certificate-seeking' (,130) bold graphic (+1,1,180) box 2 0 10 print 'undergraduates' (,135) bold graphic (+1,1,180) box 2 0 10 print 'Number' (,134) bold graphic (+1,1,180) box 2 0 10 print 'Number' (,32) bold print 'Percentage' (,42) bold print 'Number' (,83) bold print 'Percentage' (,93) bold print 'students' (,134) bold print 'Percentage' (,144) bold print 'Total' (,158) bold graphic (+1,1,180) box 2 0 10 print 'students' (,32) bold print 'of students' (,42) bold print 'Total' (,55) bold print 'Average' (,68) bold print 'students' (,83) bold print 'of students' (,93) bold print 'Total' (,106) bold print 'Average' (,119) bold print 'who' (,134) bold print 'of students' (,144) bold print 'amount' (,158) bold print 'Average' (,168) bold graphic (+1,1,180) box 2 0 10 print 'who were' (,32) bold print 'who were' (,42) bold print 'amount of' (,55) bold print 'amount of' (,68) bold print 'who were' (,83) bold print 'who were' (,93) bold print 'amount of' (,106) bold print 'amount of' (,119) bold print 'were' (,134) bold print 'who were' (,144) bold print 'of aid' (,158) bold print 'amount' (,168) bold graphic (+1,1,180) box 2 0 10 print 'awarded' (,32) bold print 'awarded' (,42) bold print 'aid awarded' (,55) bold print 'aid awarded' (,68) bold print 'awarded' (,83) bold print 'awarded' (,93) bold print 'aid awarded' (,106) bold print 'aid awarded' (,119) bold print 'awarded' (,134) bold print 'awarded' (,144) bold print 'awarded' (,158) bold print 'of aid' (,168) bold graphic (+1,1,180) box 2 0 10 print 'aid' (,32) bold print 'aid' (,42) bold print '(Col.3/Col.1)' (,68) bold print 'aid' (,83) bold print 'aid' (,93) bold print '(Col.7/Col.5)' (,119) bold print 'aid' (,134) bold print 'aid' (,144) bold print '(Col.3-' (,158) bold print 'awarded' (,168) bold graphic (+1,1,180) box 2 0 10 print '(Col.1-' (,134) bold print 'Col.7)' (,158) bold graphic (+1,1,180) box 2 0 10 print 'Col.5)' (,134) bold graphic (+1,1,28) box 2 0 10 print 'Grant ot scholarship' (,6) bold graphic (+1,1,28) box 2 0 10 print 'aid from the federal' (,6) bold graphic (+1,1,28) box 2 0 10 print 'government,' (,6) bold graphic (+1,1,28) box 2 0 10 print 'state/local' (,6) bold graphic (+1,1,28) box 2 0 10 print ' 01 ' (,1) bold print 'government, the' (,6) bold print #PARTB_01_Col1_SUM (,32) edit '99,999' graphic (,32,7) box 1 3 0 print #PARTB_01_Col3_SUM (,53) edit '$$$,$$$,$$9.99' graphic (,52,15) box 1 3 0 print #Partb_01_Col5_SUM (,83) edit '99,999' graphic (,83,7) box 1 3 0 print #PARTB_01_Col7_SUM (,106) edit '$$$,$$$,$$9.99' graphic (,105,15) box 1 3 0 graphic (+1,1,28) box 2 0 10 print 'institution, and other' (,6) bold graphic (+1,1,28) box 2 0 10 print 'sources known to' (,6) bold graphic (+1,1,28) box 2 0 10 print 'the institution (Do' (,6) bold graphic (+1,1,28) box 2 0 10 print 'NOT include federal' (,6) bold graphic (+1,1,28) box 2 0 10 print 'student loans)' (,6) bold graphic (+2,1,28) box 2 0 10 print ' 02 ' (,1) bold print 'Federal PELL' (,6) bold print #PARTB_02_Number_SUM (,32) edit '99,999' graphic (,32,7) box 1 3 0 print #PARTB_02_Amount_SUM (,53) edit '$$$,$$$,$$9.99' graphic (,52,15) box 1 3 0 graphic (+1,1,28) box 2 0 10 print 'Grants' (,6) bold graphic (+2,1,28) box 2 0 10 print ' 03 ' (,1) bold print 'Federal student' (,6) bold print #PARTB_03_Number_SUM (,32) edit '99,999' graphic (,32,7) box 1 3 0 print #PARTB_03_Amount_SUM (,53) edit '$$$,$$$,$$9.99' graphic (,52,15) box 1 3 0 graphic (+1,1,28) box 2 0 10 print 'loans' (,6) bold ! New headers /\ 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 '04c. 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 '01c.' (+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 !JAJ Let #CA_Amt = 0 Let #CA_Inst_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 = &B.EMPLID AND ISF2.BUSINESS_UNIT = $input_Institution AND ISF2.ACAD_YEAR = $Input_AidYear AND ((ISF2.BUSINESS_UNIT = 'UMS01' AND ISF2.ITEM_TYPE in ('000012399985', '000012399986', '000012399990', '000012399991', '000012399995', '000012399996', '000012399997')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS02' AND ISF2.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399991', '000012399985', '000012399986', '000000490055')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS03' AND ISF2.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399997', '000012399985', '000012399986')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS04' AND ISF2.ITEM_TYPE in ('000012399995', '000012399996', '000012399988', '000012399989', '000012399990', '000012399991', '000012399983', !JAJ 858 '000012399984', '000012399985', '000012399986')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS05' AND ISF2.ITEM_TYPE in ('000012399995', '000012399996', '000012399988', '000012399989', '000012399990', '000012399991', '000012399983', !JAJ 858 '000012399984', '000012399985', '000012399986')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS06' AND ISF2.ITEM_TYPE in ('000012399984', '000012399985', '000012399986', '000012399987', '000012399988', '000012399989', '000012399990', !JAJ 858 '000012399991', '000012399993', '000012399994', '000012399995', '000012399996', '000012399997', '000012399998', !JAJ 858 '000012399999')) !JAJ 858 OR (ISF2.BUSINESS_UNIT = 'UMS07' AND ISF2.ITEM_TYPE in ('000012399995', '000012399996', '000012399990', '000012399991', '000012399985', '000012399986'))) !JAJ 858 End-Select BEGIN-SELECT sum(ISF3.ITEM_AMT * -1) &ITEM_Inst_AMT Let #CA_Inst_Amt = &ITEM_Inst_AMT FROM SYSADM.PS_ITEM_SF ISF3 WHERE ISF3.EMPLID = &B.EMPLID AND ISF3.BUSINESS_UNIT = $input_Institution AND ISF3.ACAD_YEAR = $Input_AidYear AND (ISF3.BUSINESS_UNIT = 'UMS01' AND ISF3.ITEM_TYPE in ('000006000035')) !JAJ 858 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