SFA_SLC_TFRAVW2

(SQL View)
 Index Back

Award data without TFRA data

Joins all fields in the Award Group View with the SLC Setup table and the TFRA View to display Student Awards data that does not have corresponding TFRA data.

SELECT S.INSTITUTION ,S.AID_YEAR ,S.SFA_SLC_HEI_CD ,S.SFA_SLC_ACAD_YR ,B.EMPLID ,%Substring(E.EXTERNAL_SYSTEM_ID, 1,13) ,MAX(N.NAME) ,SUM(B.OFFER_AMOUNT) ,SUM(B.ACCEPT_AMOUNT) ,SUM(B.DISBURSED_AMOUNT) ,B.ITEM_TYPE_GROUP ,MAX(B.CURRENCY_CD) FROM PS_SFA_SLC_SETUP S , PS_SFA_SLC_AWDGPVW B LEFT OUTER JOIN PS_SFA_SLC_EXTIDV2 E ON B.EMPLID = E.EMPLID , PS_SCC_PRI_NAME_VW N WHERE B.INSTITUTION = S.INSTITUTION AND B.AID_YEAR = S.AID_YEAR AND B.EMPLID = N.EMPLID AND ( B.ITEM_TYPE_GROUP = S.SFA_SLC_TGRANT_GRP OR B.ITEM_TYPE_GROUP = S.SFA_SLC_TLOAN_GRP ) AND NOT EXISTS ( SELECT 'X' FROM PS_SFA_SLC_TFRA_VW V1 WHERE V1.EMPLID = B.EMPLID AND ((V1.SFA_SLC_PAYMNT_TYP = 'T' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TLOAN_GRP) OR (V1.SFA_SLC_PAYMNT_TYP = 'G' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TGRANT_GRP) ) AND V1.SFA_SLC_HEI_CD = S.SFA_SLC_HEI_CD AND V1.SFA_SLC_ACAD_YR = S.SFA_SLC_ACAD_YR) GROUP BY S.INSTITUTION, S.AID_YEAR, S.SFA_SLC_HEI_CD, S.SFA_SLC_ACAD_YR, B.ITEM_TYPE_GROUP, B.EMPLID, E.EXTERNAL_SYSTEM_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year
3 SFA_SLC_HEI_CD Character(4) VARCHAR2(4) NOT NULL HEI Code

Prompt Table: SFA_SLC_HEI_VW

4 SFA_SLC_ACAD_YR Character(4) VARCHAR2(4) NOT NULL SLC Acad Year

Prompt Table: SFA_SLC_ACYR_VW

5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
6 SFA_SLC_SSN Character(13) VARCHAR2(13) NOT NULL Student Support Nbr
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 OFFER_AMOUNT Number(12,2) DECIMAL(11,2) NOT NULL Offer Amount
9 ACCEPT_AMOUNT Number(12,2) DECIMAL(11,2) NOT NULL Accept Amount
10 DISBURSED_AMOUNT Number(12,2) DECIMAL(11,2) NOT NULL Disbursed Amount
11 ITEM_TYPE_GROUP Character(10) VARCHAR2(10) NOT NULL Item Type Group
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY