Created by Miki Yanagi on Feb 08, 2022
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| ACAD_CAREER - Academic Career | Char4 | N | Career | |
| EMPLID - Empl ID | Char11 | ID | ||
| INSTITUTION - Academic Institution | Char5 | Institution | ||
| UM_ALT_PRIV - Alt_Priv Loans | SNm9.2 | Alt_Priv Loans | ||
| UM_GRAD_PLUS - Grad Plus | SNm9.2 | Grad Plus | ||
| UM_INST - Institutional Loans | SNm9.2 | Inst Loans | ||
| UM_NURSING - Nursing | SNm9.2 | Nursing | ||
| UM_OTHER - Other Loans | SNm9.2 | Other Loans | ||
| UM_PERKINS - Perkins | SNm9.2 | Perkins | ||
| UM_PLUS - Plus Loan | SNm9.2 | Plus Loan | ||
| UM_STATE - State Loan | SNm9.2 | State Loan | ||
| UM_SUB_UNSUB_TOTAL - Subsidized/Unsubsidized Total | SNm9.2 | Sub/Unsub Total | ||
| UM_SUBSIDIZED - Subsidized | SNm9.2 | Subsidized | ||
| UM_TOTAL_LOANS - Total Loans | SNm9.2 | Total Loans | ||
| UM_UNSUBSIDIZED - Usubsidized | SNm9.2 | Unsubsidized |
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.
SQL code for UM_AGGR_LOAN_VW Expand source
SELECT PART1.INSTITUTION,
PART1.EMPLID,
PART1.ACAD_CAREER,
NVL(PART1.SUBSIDIZED, 0) SUBSIDIZED,
NVL(PART1.UNSUBSIDIZED, 0) UNSUBSIDIZED,
NVL(PART1.SUBSIDIZED, 0) + NVL(PART1.UNSUBSIDIZED, 0) SUB_UNSUB_TOTAL,
NVL(PART1.PERKINS, 0) PERKINS,
NVL(PART1.NURSING, 0) NURSING,
NVL(PART1.PLUS, 0) PLUS,
NVL(PART1.GRAD_PLUS, 0) GRAD_PLUS,
NVL(PART1.INST, 0) INST,
NVL(PART1.STATE, 0) STATE,
NVL(PART1.ALT_PRIV, 0) ALT_PRIV,
NVL(PART1.OTHER, 0) OTHER,
NVL(PART1.SUBSIDIZED, 0) + NVL(PART1.UNSUBSIDIZED, 0) +
NVL(PART1.PERKINS, 0) + NVL(PART1.NURSING, 0) +
NVL(PART1.GRAD_PLUS, 0) + NVL(PART1.OTHER, 0) + NVL(PART1.PLUS, 0) +
NVL(PART1.STATE, 0) + NVL(PART1.INST, 0) + NVL(PART1.ALT_PRIV, 0) TOTAL_LOANS
FROM (SELECT EMPLID,
INSTITUTION,
ACAD_CAREER,
SUBSIDIZED,
UNSUBSIDIZED,
PERKINS,
PLUS,
GRAD_PLUS,
NURSING,
OTHER,
STATE,
ALT_PRIV,
INST
FROM (SELECT C.EMPLID,
B.INSTITUTION,
CASE
WHEN A.MF_FUND_ID IN ('GRAD-PLUS', 'GPLUS') THEN
'GRAD'
ELSE
'UGRD'
END ACAD_CAREER,
CASE
WHEN D.MF_FUND_ID = 'DIRECT-P' THEN
'PLUS'
WHEN D.MF_FUND_ID = 'DIRECT-S' THEN
'Subsidized'
WHEN D.MF_FUND_ID = 'DIRECT-U' THEN
'Unsubsidized'
WHEN D.MF_FUND_ID = 'GPLUS' THEN
'Grad_Plus'
WHEN D.MF_FUND_ID = 'GRAD-PLUS' THEN
'Grad_Plus'
WHEN D.MF_FUND_ID = 'NURSE' THEN
'Nursing'
WHEN D.MF_FUND_ID = 'PERKINS' THEN
'Perkins'
WHEN D.MF_FUND_ID = 'PLUS' THEN
'PLUS'
WHEN D.MF_FUND_ID = 'STAFF-S' THEN
'Subsidized'
WHEN D.MF_FUND_ID = 'STAFF-U' THEN
'Unsubsidized'
WHEN D.MF_FUND_ID = 'STAFF-X' THEN
'Unsubsidized'
WHEN D.MF_FUND_ID = 'XSTAFF-S' THEN
'Subsidized'
WHEN D.MF_FUND_ID = 'XSTAFF-U' THEN
'Unsubsidized'
WHEN D.MF_FUND_ID = 'TEACHME' THEN
'State'
WHEN D.MF_FUND_ID = 'BLAINE' THEN
'State'
WHEN D.MF_FUND_ID = 'MELA' THEN
'Alt_Priv'
WHEN D.MF_FUND_ID = 'MELOAN' THEN
'Alt_Priv'
WHEN D.MF_FUND_ID = 'L-ALASKA' THEN
'Alt_Priv'
WHEN D.MF_FUND_ID = 'L-CANADA' THEN
'Alt_Priv'
WHEN D.MF_FU_SRC = 'I' THEN
'Inst'
WHEN D.MF_FU_SRC = 'U' THEN
'Inst'
WHEN D.MF_FU_SRC = 'P' THEN
'Alt_Priv'
ELSE
'Other Loan'
END LOAN_TYPE,
NVL(A.MF_AW_ACC, 0) ACCEPT_AMOUNT
FROM SYSADM.MF_AWD_SUM_VW A,
SYSADM.MF_INST_FAOSFOE B,
SYSADM.MF_STDNT_SRCHVW C,
SYSADM.MF_FUND D
WHERE A.MF_FAO = B.MF_FAO
AND A.MF_STUDENT_ID = C.MF_STUDENT_ID
AND A.MF_FAO = D.MF_FAO
AND A.MF_FUND_ID = D.MF_FUND_ID
AND A.MF_AID_YEAR = D.MF_FISCAL_YEAR
AND D.MF_FU_TYPE = 'L'
AND CASE
WHEN D.MF_FUND_ID = 'ACAD-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'ACG-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'ALT-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'E-EXUNSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'E-PLUS' THEN
'N'
WHEN D.MF_FUND_ID = 'E-SUB' THEN
'N'
WHEN D.MF_FUND_ID = 'E-UNSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'E-WINDIAN' THEN
'N'
WHEN D.MF_FUND_ID = 'E-XUNSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'EBLAINE' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTACADEM' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTACG' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTACG2' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTOUT' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTPLUS' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTSMART' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTSME' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTUNSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTXSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'ESTXUNSUB' THEN
'N'
WHEN D.MF_FUND_ID = 'ETEACHME' THEN
'N'
WHEN D.MF_FUND_ID = 'PLUS-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SCT-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SDC-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SDE-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SIND-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SMA-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SMART-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SMD-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SME-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SMI-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SNH-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SPA-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SPLAT-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SQCCS-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SRI-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SSCPP-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'SSQCC-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'STAFF-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'STAFF-OTH' THEN
'N'
WHEN D.MF_FUND_ID = 'STAFF-REQ' THEN
'N'
WHEN D.MF_FUND_ID = 'STAFF-UNS' THEN
'N'
WHEN D.MF_FUND_ID = 'SVT-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'TEACH-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'TECHM-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'TENTLOAN' THEN
'N'
WHEN D.MF_FUND_ID = 'TNTLOAN' THEN
'N'
WHEN D.MF_FUND_ID = 'UNIHG-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'UNITG-EST' THEN
'N'
WHEN D.MF_FUND_ID = 'UNIV-SPEC' THEN
'N'
WHEN UPPER(D.MF_FU_NAME) LIKE '%EST.%' THEN
'N'
WHEN UPPER(D.MF_FU_NAME) LIKE '%ESTIMATED%' THEN
'N'
ELSE
'Y'
END = 'Y'
UNION ALL
SELECT A.EMPLID,
A.INSTITUTION,
A.ACAD_CAREER,
CASE
WHEN B.FEDERAL_ID = 'STFS' THEN
'Subsidized'
WHEN B.FEDERAL_ID = 'STFU' THEN
'Unsubsidized'
WHEN B.FEDERAL_ID = 'GPLS' THEN
'Grad_Plus'
WHEN B.FEDERAL_ID = 'PLUS' THEN
'PLUS'
WHEN B.FEDERAL_ID = 'NURS' THEN
'Nursing'
WHEN B.FEDERAL_ID = 'PERK' THEN
'Perkins'
WHEN B.FA_SOURCE = 'I' THEN
'Inst'
WHEN B.FA_SOURCE = 'U' THEN
'Inst'
WHEN B.FA_SOURCE = 'P' THEN
'Alt_Priv'
WHEN B.FA_SOURCE = 'S' THEN
'State'
ELSE
'Other Loan'
END LOAN_TYPE,
CASE
WHEN B.FIN_AID_TYPE <> 'W' AND
B.DISBURSE_METHOD = 'N' AND
A.DISBURSED_AMOUNT = 0 THEN
A.ACCEPT_AMOUNT
WHEN B.FIN_AID_TYPE <> 'W' AND
B.DISBURSE_METHOD = ' ' AND
A.DISBURSED_AMOUNT = 0 THEN
A.ACCEPT_AMOUNT
WHEN A.DISBURSED_AMOUNT > 0 THEN
A.ACCEPT_AMOUNT
ELSE
A.DISBURSED_AMOUNT
END ACCEPT_AMOUNT
FROM SYSADM.PS_STDNT_AWARDS A, SYSADM.PS_UM_ITYP_FA_VW B
WHERE A.AID_YEAR = B.AID_YEAR
AND A.ITEM_TYPE = B.ITEM_TYPE
AND A.INSTITUTION = B.SETID
AND B.FIN_AID_TYPE = 'L'
AND CASE
WHEN UPPER(B.DESCR) LIKE '%EST.%' THEN
'N'
WHEN UPPER(B.DESCR) LIKE '%ESTIMATED%' THEN
'N'
WHEN UPPER(B.DESCR) LIKE 'EST %' THEN
'N'
WHEN UPPER(B.DESCR) LIKE '%EST''D%' THEN
'N'
WHEN UPPER(B.DESCR) LIKE '%E-%' THEN
'N'
ELSE
'Y'
END = 'Y')
PIVOT(SUM(ACCEPT_AMOUNT)
FOR LOAN_TYPE IN('Subsidized' AS SUBSIDIZED,
'Unsubsidized' AS UNSUBSIDIZED,
'Perkins' AS PERKINS,
'PLUS' AS PLUS,
'Grad_Plus' AS GRAD_PLUS,
'Nursing' AS NURSING,
'Inst' AS INST,
'State' AS STATE,
'Alt_Priv' AS ALT_PRIV,
'Other' AS OTHER))) PART1
WHERE NVL(PART1.SUBSIDIZED, 0) + NVL(PART1.UNSUBSIDIZED, 0) +
NVL(PART1.PERKINS, 0) + NVL(PART1.NURSING, 0) +
NVL(PART1.GRAD_PLUS, 0) + NVL(PART1.PLUS, 0) + NVL(PART1.INST, 0) +
NVL(PART1.STATE, 0) + NVL(PART1.ALT_PRIV, 0) + NVL(PART1.OTHER, 0) > 0
AND PART1.EMPLID > ' ';
