SFA_SLC_AWDGTVW

(SQL View)
 Index Back

Award Total by SLC Item Group

Financial Aid Award Total by Item Group View. Joins STDNT_AWARDS to the ITEM_GROUP_TBL to calculate award total by ITEM_TYPE_GROUP.

SELECT S.INSTITUTION ,S.AID_YEAR ,S.SFA_SLC_HEI_CD ,C.ITEM_TYPE_GROUP ,S.SFA_SLC_ACAD_YR ,SUM(A.ACCEPT_AMOUNT) ,A.CURRENCY_CD FROM PS_STDNT_AWARDS A ,PS_SFA_SLC_SETUP S ,PS_ITEM_GROUP_TBL C ,PS_ITEM_GROUP_DTL D ,PSTREEDEFN E ,PSTREENODE F ,PSTREELEAF G WHERE A.INSTITUTION = S.INSTITUTION AND A.AID_YEAR = S.AID_YEAR AND ( C.ITEM_TYPE_GROUP = S.SFA_SLC_TGRANT_GRP OR C.ITEM_TYPE_GROUP = S.SFA_SLC_TLOAN_GRP ) AND C.SETID = A.SETID AND C.EFFDT = ( SELECT MAX (CC.EFFDT) FROM PS_ITEM_GROUP_TBL CC WHERE CC.SETID = C.SETID AND CC.ITEM_TYPE_GROUP = C.ITEM_TYPE_GROUP AND CC.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND D.SETID = C.SETID AND D.ITEM_TYPE_GROUP = C.ITEM_TYPE_GROUP AND D.EFFDT = C.EFFDT AND E.TREE_NAME = C.TREE_NAME AND E.EFFDT = ( SELECT MAX (EE.EFFDT) FROM PSTREEDEFN EE WHERE EE.SETID = E.SETID AND EE.TREE_NAME = E.TREE_NAME AND EE.EFFDT <= %CurrentDateIn) AND E.VALID_TREE = 'Y' AND F.SETID = E.SETID AND F.TREE_NAME = E.TREE_NAME AND F.EFFDT = E.EFFDT AND G.SETID = E.SETID AND G.TREE_NAME = E.TREE_NAME AND G.EFFDT = E.EFFDT AND E.SETID = C.TREE_NAME_SETID AND F.TREE_NODE = D.TREE_NODE AND G.TREE_NODE_NUM = F.TREE_NODE_NUM AND A.ITEM_TYPE >= G.RANGE_FROM AND A.ITEM_TYPE <= G.RANGE_TO GROUP BY C.ITEM_TYPE_GROUP, A.CURRENCY_CD, S.INSTITUTION, S.AID_YEAR, S.SFA_SLC_HEI_CD, S.SFA_SLC_ACAD_YR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

2 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year

Default Value: OPR_DEF_TBL_CS.AID_YEAR

Prompt Table: AID_YEAR_TBL

3 SFA_SLC_HEI_CD Character(4) VARCHAR2(4) NOT NULL HEI Code
4 ITEM_TYPE_GROUP Character(10) VARCHAR2(10) NOT NULL Item Type Group
5 SFA_SLC_ACAD_YR Character(4) VARCHAR2(4) NOT NULL SLC Acad Year
6 ACCEPT_AMOUNT Number(12,2) DECIMAL(11,2) NOT NULL Accept Amount
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY