SFA_SLC_TFRAVW3

(SQL View)
 Index Back

TFRA data vs Award data

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

SELECT S.INSTITUTION ,S.AID_YEAR ,S.SFA_SLC_HEI_CD ,S.SFA_SLC_ACAD_YR ,A.EMPLID ,A.SFA_SLC_SSN ,SUM(A.SFA_SLC_FEE_AMT) ,SUM(B.OFFER_AMOUNT) ,SUM(B.ACCEPT_AMOUNT) ,SUM(B.DISBURSED_AMOUNT) ,SUM(A.SFA_SLC_FEE_AMT) - SUM(B.ACCEPT_AMOUNT) ,A.SFA_SLC_PAYMNT_TYP ,MAX(A.CURRENCY_CD) FROM PS_SFA_SLC_TFRASVW A , PS_SFA_SLC_SETUP S , PS_SFA_SLC_AWDGSVW B WHERE A.SFA_SLC_HEI_CD = S.SFA_SLC_HEI_CD AND A.SFA_SLC_ACAD_YR = S.SFA_SLC_ACAD_YR AND A.EMPLID = B.EMPLID AND A.EMPLID > ' ' AND B.INSTITUTION = S.INSTITUTION AND B.AID_YEAR = S.AID_YEAR AND ( ( A.SFA_SLC_PAYMNT_TYP = 'G' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TGRANT_GRP ) OR ( A.SFA_SLC_PAYMNT_TYP = 'T' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TLOAN_GRP ) ) GROUP BY S.INSTITUTION, S.AID_YEAR, S.SFA_SLC_HEI_CD, S.SFA_SLC_ACAD_YR, A.SFA_SLC_PAYMNT_TYP, A.EMPLID, A.SFA_SLC_SSN

# 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 SFA_SLC_FEE_AMT Signed Number(20,3) DECIMAL(18,3) NOT NULL Fee Amount
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 SFA_SLC_DIFF_AMT Signed Number(20,3) DECIMAL(18,3) NOT NULL Difference Amount
12 SFA_SLC_PAYMNT_TYP Character(1) VARCHAR2(1) NOT NULL Payment Type
G=Tuition Fee Grant
T=Tuition Fee Loan
13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY