Created by Corina C Larsen, last modified by Miki Yanagi on Oct 08, 2019
Complete view of item types with tree information by aid year.
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| AGGREGATE_AREA - Aggregate Area | Char10 | Aggr Area | ||
| → | AID_YEAR - Aid Year | Char4 | Aid Yr | |
| CHARGE_PRIORITY - Charge Priority List | Char8 | Charge Lst | ||
| DESCR - Description | Char30 | Descr | ||
| DESCRSHORT - Short Description | Char10 | Short Desc | ||
| DISBURSE_METHOD - Disbursement Method | Char1 | N | Method | |
| → | EFFDT - Effective Date | Date | Eff Date | |
| EFF_STATUS - Status as of Effective Date | Char1 | N | Status | |
| FA_SOURCE - Source | Char1 | N | Source | |
| FEDERAL_ID - Federal ID | Char4 | N | Federal ID | |
| FED_OR_INST - Fed/Inst Affected | Char1 | N | Apply to | |
| FIN_AID_TYPE - Fin Aid Type | Char4 | N | Finaid Typ | |
| → | ITEM_TYPE - Item Type | Char12 | Item Type | |
| ITEM_TYPE_CD - Item Type Code | Char1 | N | Item Code | |
| KEYWORD1 - Key Word | Char10 | Key Word | ||
| KEYWORD2 - Key Word 2 | Char10 | Key Word 2 | ||
| KEYWORD3 - Key Word 3 | Char10 | Key Word 3 | ||
| LOAN_PROGRAM - Loan Program | Char1 | N | Loan Prog | |
| NEED_BASED - Need Based | Char1 | Need Based | ||
| REPORT_CODE - Institution Reporting Cd | Char4 | Rprt Cd | ||
| → | SETID - SetID | Char5 | SetID | |
| TITLE_IV - Title IV Aid | Char1 | Title IV | ||
| UM_GASB_1 - Item Type GASB Node 1 | Char20 | GASB Node 1 | ||
| UM_GASB_2 - Item Type GASB Node 2 | Char20 | GASB Node 2 | ||
| UM_GASB_3 - Item Type GASB Node 3 | Char20 | GASB Node 3 | ||
| UM_GASB_4 - Item Type GASB Node 4 | Char20 | GASB Node 4 | ||
| UM_GASB_5 - Item Type GASB node 5 | Char20 | GASB Node 5 | ||
| UM_SCRTY_1 - Item Type Security Node 1 | Char20 | Scrty Node 1 | ||
| UM_SCRTY_2 - Item Type Security Node 2 | Char20 | Sctry Node 2 | ||
| UM_SCRTY_3 - Item Type Security Node 3 | Char20 | Scrty Node 3 | ||
| UM_SCRTY_4 - Item Type Security Node 4 | Char20 | Scrty Node 4 | ||
| UM_SCRTY_5 - Item Type Security Node 5 | Char20 | Scrty Node 5 | ||
| UM_SCRTY_6 - Item Type Security Node 6 | Char20 | Sctry Node 6 | ||
| UM_SCRTY_7 - Item Type Security Node 7 | Char20 | Scrty Node 7 | ||
| UM_SCRTY_8 - Item Type Security Node 8 | Char20 | Scrty Node 8 |
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_ITYP_FA_VW Expand source
SELECT A.SETID,
A.ITEM_TYPE,
A.AID_YEAR,
A.EFFDT,
A.EFF_STATUS,
A.DESCR,
A.DESCRSHORT,
A.FA_SOURCE,
A.FEDERAL_ID,
A.LOAN_PROGRAM,
A.TITLE_IV,
A.FIN_AID_TYPE,
A.FED_OR_INST,
A.NEED_BASED,
A.AGGREGATE_AREA,
A.REPORT_CODE,
A.DISBURSE_METHOD,
B.ITEM_TYPE_CD,
B.CHARGE_PRIORITY,
B.KEYWORD1,
B.KEYWORD2,
B.KEYWORD3,
C.UM_SCRTY_1,
C.UM_SCRTY_2,
C.UM_SCRTY_3,
C.UM_SCRTY_4,
C.UM_SCRTY_5,
C.UM_SCRTY_6,
C.UM_SCRTY_7,
C.UM_SCRTY_8,
C.UM_GASB_1,
C.UM_GASB_2,
C.UM_GASB_3,
C.UM_GASB_4,
C.UM_GASB_5
FROM PS_ITEM_TYPE_FA A, PS_ITEM_TYPE_TBL B, PS_UM_ITYP_TREENDS C
WHERE A.SETID = B.SETID
AND A.ITEM_TYPE = B.ITEM_TYPE
AND B.EFFDT = (SELECT MAX(BEF.EFFDT)
FROM PS_ITEM_TYPE_TBL BEF
WHERE B.SETID = BEF.SETID
AND B.ITEM_TYPE = BEF.ITEM_TYPE
AND BEF.EFFDT <= SYSDATE)
AND A.SETID = C.SETID(+)
AND A.ITEM_TYPE = C.ITEM_TYPE(+)
AND A.EFFDT = (SELECT MAX(AEF.EFFDT)
FROM PS_ITEM_TYPE_FA AEF
WHERE A.SETID = AEF.SETID
AND A.ITEM_TYPE = AEF.ITEM_TYPE
AND A.AID_YEAR = AEF.AID_YEAR
AND AEF.EFFDT <= SYSDATE)
UNION ALL
SELECT B.SETID,
B.ITEM_TYPE,
' ',
B.EFFDT,
B.EFF_STATUS,
B.DESCR,
B.DESCRSHORT,
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
B.ITEM_TYPE_CD,
B.CHARGE_PRIORITY,
B.KEYWORD1,
B.KEYWORD2,
B.KEYWORD3,
C.UM_SCRTY_1,
C.UM_SCRTY_2,
C.UM_SCRTY_3,
C.UM_SCRTY_4,
C.UM_SCRTY_5,
C.UM_SCRTY_6,
C.UM_SCRTY_7,
C.UM_SCRTY_8,
C.UM_GASB_1,
C.UM_GASB_2,
C.UM_GASB_3,
C.UM_GASB_4,
C.UM_GASB_5
FROM PS_ITEM_TYPE_TBL B, PS_UM_ITYP_TREENDS C
WHERE B.SETID = C.SETID(+)
AND B.ITEM_TYPE = C.ITEM_TYPE(+)
AND B.EFFDT = (SELECT MAX(BEF.EFFDT)
FROM PS_ITEM_TYPE_TBL BEF
WHERE B.SETID = BEF.SETID
AND B.ITEM_TYPE = BEF.ITEM_TYPE
AND BEF.EFFDT <= SYSDATE)
AND NOT EXISTS (SELECT 'X'
FROM PS_ITEM_TYPE_FA A
WHERE B.SETID = A.SETID
AND B.ITEM_TYPE = A.ITEM_TYPE);
