SFA_SLC_TFRAVW1

(SQL View)
 Index Back

TFRA data without Award data

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

SELECT S.INSTITUTION ,S.AID_YEAR ,S.SFA_SLC_HEI_CD ,S.SFA_SLC_ACAD_YR ,A.SFA_SLC_SSN ,MAX(A.SFA_SLC_COURSE_PER) ,MAX(A.SFA_SLC_LAST_NAME) ,MAX(A.SFA_SLC_FIRST_NAME) ,MAX(A.SFA_SLC_BIRTHDATE) ,MAX(A.SFA_SLC_UCAS_NBR) ,MAX(A.SFA_SLC_COURSE_CD) ,MAX(A.SFA_SLC_COURSE_NM) ,MAX(A.SFA_SLC_COURSE_YR) ,SUM(A.SFA_SLC_FEE_AMT) ,MAX(A.SFA_SLC_REVISION) ,MAX(A.SFA_SLC_PAYMNT_DT) ,A.SFA_SLC_PAYMNT_TYP ,MAX(A.CURRENCY_CD) ,A.EMPLID FROM PS_SFA_SLC_TFRA_VW A , PS_SFA_SLC_SETUP S 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 <= ' ' OR A.EMPLID IS NULL OR ( A.EMPLID > ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_SFA_SLC_AWDGPVW B WHERE B.EMPLID = A.EMPLID AND B.INSTITUTION = S.INSTITUTION AND B.AID_YEAR = S.AID_YEAR AND ( ( A.SFA_SLC_PAYMNT_TYP = 'T' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TLOAN_GRP ) OR ( A.SFA_SLC_PAYMNT_TYP = 'G' AND B.ITEM_TYPE_GROUP = S.SFA_SLC_TGRANT_GRP ) ) ) ) ) GROUP BY S.INSTITUTION, S.AID_YEAR, S.SFA_SLC_HEI_CD, S.SFA_SLC_ACAD_YR, A.EMPLID, A.SFA_SLC_SSN, A.SFA_SLC_PAYMNT_TYP

# 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 SFA_SLC_SSN Character(13) VARCHAR2(13) NOT NULL Student Support Nbr
6 SFA_SLC_COURSE_PER Character(3) VARCHAR2(3) NOT NULL Course Start Period
AUT=AUT
SPR=SPR
SUM=SUM
WIN=WIN
7 SFA_SLC_LAST_NAME Character(50) VARCHAR2(50) NOT NULL Last Name
8 SFA_SLC_FIRST_NAME Character(50) VARCHAR2(50) NOT NULL First Name
9 SFA_SLC_BIRTHDATE Date(10) DATE Date of Birth
10 SFA_SLC_UCAS_NBR Character(9) VARCHAR2(9) NOT NULL UCAS Number
11 SFA_SLC_COURSE_CD Character(15) VARCHAR2(15) NOT NULL SLC Course Code
12 SFA_SLC_COURSE_NM Character(120) VARCHAR2(120) NOT NULL Course Name
13 SFA_SLC_COURSE_YR Character(1) VARCHAR2(1) NOT NULL Year of Course
14 SFA_SLC_FEE_AMT Signed Number(20,3) DECIMAL(18,3) NOT NULL Fee Amount
15 SFA_SLC_REVISION Character(1) VARCHAR2(1) NOT NULL Revision Indicator
16 SFA_SLC_PAYMNT_DT Date(10) DATE Payment Date
17 SFA_SLC_PAYMNT_TYP Character(1) VARCHAR2(1) NOT NULL Payment Type
G=Tuition Fee Grant
T=Tuition Fee Loan
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY

19 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID