DARTS : Financial Aid Calculations and Translations

AWARD STATUS DESCR
CASE
WHEN Award Status = 'A' WHEN Award Status = 'C' WHEN Award Status = 'D' WHEN Award Status = 'O' 
END 


FINANCIAL AID TYPE
CASE
WHEN Fin Aid Type = 'V' WHEN Fin Aid Type = 'S' WHEN Fin Aid Type = 'W' WHEN Fin Aid Type = 'F' WHEN Fin Aid Type = 'A' WHEN Fin Aid Type = 'D' WHEN Fin Aid Type = 'G' WHEN Fin Aid Type = 'O' WHEN Fin Aid Type = 'L' WHEN Fin Aid Type = 'B' 
END



CAMP
CASE
WHEN Institution = 'UMS01' WHEN Institution = 'UMS02' WHEN Institution = 'UMS03' WHEN Institution = 'UMS04' WHEN Institution = 'UMS07' WHEN Institution = 'UMS05' WHEN Institution = 'UMS06' 
END



FED ID
CASE
WHEN Federal Id = 'LOS' WHEN Federal Id = 'NSF' WHEN Federal Id = 'ACG'

THEN 'Accepted' THEN 'Cancelled' THEN 'Declined' THEN 'Offered' ELSE 'Other' 

THEN 'Waiver' THEN 'Scholarship' THEN 'Work Study' THEN 'Fellowship' THEN 'Athletic' THEN 'Department' THEN 'Grant'
THEN 'Other' THEN 'Loan' THEN 'Bursary' ELSE 'Other' 

THEN 'UMA' THEN 'UMF' THEN 'UMFK' THEN 'UMM' THEN 'UMPI' THEN 'UM' THEN 'USM'
ELSE-'Other' 

THEN 'Loans for Disadvantaged Stdnts' THEN 'National Science Foundation' THEN 'Academic Competitiveness Grant

WHEN Federal Id = 'PCL' WHEN Federal Id = 'JVTS' WHEN Federal Id = 'NIH' WHEN Federal Id = 'NURS' WHEN Federal Id = 'OTHR' WHEN Federal Id = 'STFS' WHEN Federal Id = 'FWSP' WHEN Federal Id = 'HEAL' WHEN Federal Id = 'HPSL' WHEN Federal Id = 'IASA' WHEN Federal Id = 'PERK' WHEN Federal Id = 'STFU'
WHEN Federal Id = 'TCH' WHEN Federal Id = 'PLUS'
WHEN Federal Id = 'GAAN' WHEN Federal Id = 'GPLS' WHEN Federal Id = 'PELL' WHEN Federal Id = 'SEOG' WHEN Federal Id = 'SMRT' 
END



ITEM TYPE CODE
CASE
WHEN Item Type Cd = 'D' WHEN Item Type Cd = 'H' WHEN Item Type Cd = 'L' WHEN Item Type Cd = 'F' WHEN Item Type Cd = 'P' WHEN Item Type Cd = 'V' WHEN Item Type Cd = 'X' WHEN Item Type Cd = 'G' WHEN Item Type Cd = 'Z' WHEN Item Type Cd = 'T' WHEN Item Type Cd = 'C' WHEN Item Type Cd = 'W' WHEN Item Type Cd = 'I' WHEN Item Type Cd = 'A' WHEN Item Type Cd = 'B' WHEN Item Type Cd = 'R' 
END 
THEN 'Primary Care Loan'
THEN 'Jacob K Javits Fellowship' THEN 'National Institute of Health' THEN 'Nursing Loan'
THEN 'Other Federal Fund'
THEN 'Fed Stafford Loan - Subsidized' THEN 'Federal Work Study Program' THEN 'Health Education Assistance Ln' THEN 'Health Profession Student Loan' THEN 'Iraq/Afghanistan Service Award' THEN 'Federal Perkins Loan Program' THEN 'Fed Stafford Loan-Unsubsidized' THEN 'TEACH Grant'
THEN 'Federal Stafford Loan, PLUS' THEN 'Grad Assistance in Natl Need' THEN 'Graduate Plus Loan'
THEN 'Federal PELL Grant' THEN 'Federal SEOG'
THEN 'National Smart Grant' 
ELSE 'Other' 

THEN 'Deposit' THEN 'Withholding'
THEN 'Pre-Paid Tuition' THEN 'Financial Aid' THEN 'Payment'
THEN 'Contributor Relations' THEN 'Write-off'
THEN 'GL Interface Only' THEN 'Pay Plan Credit' THEN 'Transfers'
THEN 'Charge' THEN 'Waiver' THEN 'Interest'
THEN 'Application Fee' THEN 'Billing Only' THEN 'Refund'
ELSE 'Other'



FUND SOURCE
CASE
WHEN Fa Source = 'O' WHEN Fa Source = 'G' WHEN Fa Source = 'I' WHEN Fa Source = 'N' WHEN Fa Source = 'S' WHEN Fa Source = 'F' WHEN Fa Source = 'P' WHEN Fa Source = 'U' 
END



AID AWARD STATUS
CASE
WHEN AWARD_STATUS = 'D' WHEN AWARD_STATUS = 'O' WHEN AWARD_STATUS = 'A' WHEN AWARD_STATUS = 'C' 
END

THEN 'Other'
THEN 'Government' THEN 'Institutional' THEN 'Fed Non Title IV' THEN 'State'
THEN 'Federal' THEN 'Private' THEN 'System' ELSE 'Other' 

THEN 'Declined' THEN 'Offered' THEN 'Accepted' THEN 'Cancelled' ELSE 'Unknown'




LOAN PROG
CASE
WHEN Loan Program = 'F' WHEN Loan Program = 'U' WHEN Loan Program = 'S' WHEN Loan Program = 'P' WHEN Loan Program = 'D' WHEN Loan Program = 'H' WHEN Loan Program = 'A' 
END



GIFT AID
CASE
WHEN Fin Aid Type = 'V' WHEN Fin Aid Type = 'S' WHEN Fin Aid Type = 'W' WHEN Fin Aid Type = 'F' WHEN Fin Aid Type = 'A' WHEN Fin Aid Type = 'D' WHEN Fin Aid Type = 'G' WHEN Fin Aid Type = 'O' WHEN Fin Aid Type = 'L'

THEN 'FFELP'
THEN 'University' THEN 'State' THEN 'Perkins'
THEN 'Direct Lending' THEN 'Health Professions' THEN 'Alternative'
ELSE 'Not Loan' 

THEN 'Y' THEN 'Y' THEN 'N' THEN 'Y' THEN 'Y' THEN 'Y' THEN 'Y' THEN 'Y' THEN 'N'
WHEN Fin Aid Type = 'B'THEN 'Y'
ELSE 'N'
END



FISAP INCOME CATEGORY
CASE
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 0.0 AND Fisap Tot Inc < 6000 THEN 'A. $0 - $5,999'
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 6000 AND Fisap Tot Inc < 12000 THEN 'B. $6,000 - $11,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 12000 AND Fisap Tot Inc < 24000 THEN 'C. $12,000 - $23,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 24000 AND Fisap Tot Inc < 30000 THEN 'D. $24,000 - $29,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 30000 AND Fisap Tot Inc < 42000 THEN 'E. $30,000 - $41,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 42000 AND Fisap Tot Inc < 60000 THEN 'F. $42,000 - $59,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' AND Fisap Tot Inc >= 60000 THEN 'G. $60,000 and over'
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' THEN 'H. UGRD Dependent Unknown'
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 0.0 AND Fisap Tot Inc < 2000 THEN 'I. $0 - $1,999'
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 2000 AND Fisap Tot Inc < 4000 THEN 'J. $2,000 - $3,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 4000 AND Fisap Tot Inc < 8000 THEN 'K. $4,000 - $7,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 8000 AND Fisap Tot Inc < 12000 THEN 'L. $8,000 - $11,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 12000 AND Fisap Tot Inc < 16000 THEN 'M. $12,000 - $15,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 16000 AND Fisap Tot Inc < 20000 THEN 'N. $16,000 - $19,999' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' AND Fisap Tot Inc >= 20000 THEN 'O. $20,000 and over'
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' THEN 'P. UGRD Independent Unknown' WHEN Academic Career = 'GRAD' THEN 'Q. Grad/Professional'
ELSE 'R. Unknown'- END



FISAP STUDENT CATEGORY
CASE
WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'D' THEN '1. Undergraduate Dependent' WHEN Academic Career = 'UGRD' AND Depndncy Stat = 'I' THEN '2. Undergraduate Independent' WHEN Academic Career = 'GRAD' THEN '3. Grad/Professional'
ELSE '4. Unknown' END



SCHOLARSHIP AND GRANT AID
CASE
WHEN Fin Aid Type = 'V' WHEN Fin Aid Type = 'S' WHEN Fin Aid Type = 'W' WHEN Fin Aid Type = 'F' WHEN Fin Aid Type = 'A' WHEN Fin Aid Type = 'D' WHEN Fin Aid Type = 'G' WHEN Fin Aid Type = 'O'

THEN 'Y' THEN 'Y' THEN 'N' THEN 'Y' THEN 'Y' THEN"Y' THEN 'Y' THEN 'N'
WHEN Fin Aid Type = 'L' WHEN Fin Aid Type = 'B' 
END 



HOUSING TYPE
CASE
WHEN Housing Type = '1' WHEN Housing Type = '2' WHEN Housing Type = '3' 
END
THEN 'N' THEN 'N' ELSE 'N' 

THEN 'On Campus'
THEN 'Off Campus with Parent' THEN 'Off Campus Not With Parent' ELSE 'Other' 



EFC RANGE
CASE
WHEN Prorated Efc < 1
WHEN Prorated Efc >= 1 AND Prorated Efc <= 1000 WHEN Prorated Efc > 1000 AND Prorated Efc <= 2500 WHEN Prorated Efc > 2500 AND Prorated Efc <= 5000 WHEN Prorated Efc > 5000 AND Prorated Efc <= 7500 WHEN Prorated Efc > 7500 AND Prorated Efc <= 10000 WHEN Prorated Efc > 10000 AND Prorated Efc <= 12500 WHEN Prorated Efc > 12500 AND Prorated Efc <= 15000 WHEN Prorated Efc > 15000 AND Prorated Efc <= 20000 WHEN Prorated Efc > 20000 AND Prorated Efc <= 30000 WHEN Prorated Efc > 30000 AND Prorated Efc <= 40000 WHEN Prorated Efc > 40000 
END

THEN '$0'
THEN '$1 - 1,000' THEN '$1,001 - 2,500' THEN '$2,501 - 5,000' THEN '$5,001 - 7,500' THEN '$7,501 - 10,000'
THEN '$10,001 - 12,500' THEN '$12,501 - 15,000' THEN '$15,001 - 20,000' THEN '$20,001 - 30,000' THEN '$30,001 - 40,000' THEN '>$40,000'
ELSE 'Non-FAFSA/Unknown'



EFC RANGE SORT 
THEN '01'
THEN '02'
THEN '03'
THEN '04'
THEN '05'
THEN '06'
THEN '07'
THEN '08'
THEN '09'
THEN '10'
THEN '11'
THEN '12'
ELSE '13' 



CALCULATED AID AMOUNT
CASE
WHEN Fin Aid Type <> 'W' AND Disburse Method = 'N' AND Disbursed Amount = 0 THEN Accept Amount WHEN Fin Aid Type <> 'W' AND Disburse Method = ' ' AND Disbursed Amount = O THEN Accept Amount ELSE Disbursed Amount
END 



MARITAL STATUS DESCR
CASE
WHEN Marital Stat = '1' WHEN Marital Stat = '2' WHEN Marital Stat = '3' WHEN Marital Stat = '4'
THEN 'Married' THEN 'Single'
THEN 'Divorced/Separated' THEN 'Widowed'
ELSE ' 'END



INCLUDED FUNDS
CASE
WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE '%EST.%'
WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE '%ESTIMATED%' WHEN UPPER(A.ITEM_ TYPE_DESCR) LIKE 'EST %'
WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE '%EST"D%' WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE 'E-%' 
END



ESTIMATED AID FLAG
CASE
WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE '%EST.%'
WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE '%ESTIMATED%' WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE 'EST %'
WHEN UPPER(A.ITEM_ TYPE_DESCR) LIKE '%EST"D%' WHEN UPPER(A.ITEM_TYPE_DESCR) LIKE 'E-%' 
END



LOAN GROUP
CASE
WHEN B.FEDERAL_ID = 'STFS' WHEN B.FEDERAL_ID = 'STFU' WHEN B.FEDERAL_ID = 'GPLS' WHEN B.FEDERAL_ID = 'PLUS' WHEN B.FEDERAL_ID = 'NURS' WHEN B.FEDERAL_ID = 'PERK' WHEN B.FA_SOURCE = 'I' WHEN B.FA_SOURCE = 'U' WHEN B.FA_SOURCE = 'P' WHEN B.FA_SOURCE = 'S' 
END

THEN 'N' THEN 'N' THEN 'N' THEN 'N' THEN 'N' ELSE 'Y' 

THEN 'Y' THEN 'Y' THEN 'Y' THEN 'Y' THEN 'Y' ELSE 'N' 

THEN 'Subsidized Stafford Loan' THEN 'Unsubsidized Stafford Loan' THEN 'Graduate PLUS Loan'
THEN 'PLUS Loan'
THEN 'Nursing Loan' THEN 'Perkins Loan' THEN 'Institutional Loan' THEN 'Institutional Loan' THEN 'Private Loan' THEN 'State Loan'
ELSE 'Other Loan'