StudentDatabase_Fianacial_Aid_SQL/STUDENT_DATABASE_OVERAWARD_COA_NEED_SQL.sql at master · haijunghsu/StudentDatabase_Fianacial_Aid_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
SELECT A.AID_YEAR, A.EMPLID, A.AWARD_PERIOD, A.FED_OVRAWD_AMT, A.FED_OVRAWD_COA, B.ITEM_TYPE, B.OFFER_BALANCE, B.ACCEPT_BALANCE, B.AUTHORIZED_BALANCE, B.DISBURSED_BALANCE, B.STRM, B.ACAD_CAREER, E.NET_AWARD_AMT, E.DISBURSED_AMOUNT, MAX( TO_CHAR(CAST((F.ACTION_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), E.DISBURSEMENT_PLAN, P.FA_LOAD
  FROM PS_STDNT_AWD_PER A, PS_STDNT_AWRD_DISB B, PS_ITEM_TYPE_FA C, PS_AID_YR_CAR_TERM D, PS_STDNT_AWARDS E, PS_STDNT_AWRD_ACTV F, PS_AID_YR_CAR_TERM O, PS_STDNT_FA_TERM P
  WHERE ( A.AID_YEAR > '2017'
     AND (( A.FED_OVRAWD_AMT > 0
     AND C.NEED_BASED = 'Y')
     OR A.FED_OVRAWD_COA > 0)
     AND B.OFFER_BALANCE > 0
     AND C.FA_SOURCE = 'F'
     AND C.FIN_AID_TYPE = 'L'
     AND ( D.LN_PERIOD_END > SYSDATE
     OR EXISTS (SELECT 'X'
  FROM PS_STDNT_AWRD_ACTV N
  WHERE N.EMPLID = B.EMPLID
     AND N.INSTITUTION = B.INSTITUTION
     AND N.AID_YEAR = B.AID_YEAR
     AND N.ITEM_TYPE = B.ITEM_TYPE
     AND N.AWARD_DISB_ACTION = 'P'
     AND N.DISB_AMOUNT > 0
     AND N.ACTION_DTTM <  O.LN_PERIOD_START + 10))
     AND NOT EXISTS (SELECT 'X'
  FROM PS_STDNT_AWARDS I
  WHERE I.EMPLID = B.EMPLID
     AND I.INSTITUTION = B.INSTITUTION
     AND I.AID_YEAR = B.AID_YEAR
     AND I.ACAD_CAREER = B.ACAD_CAREER
     AND I.DISBURSED_AMOUNT = I.NET_OFFER_AMT
     AND EXISTS (SELECT 'X'
  FROM PS_STDNT_FA_MSGS J
  WHERE J.EMPLID = B.EMPLID
     AND J.INSTITUTION = B.INSTITUTION
     AND J.AID_YEAR = B.AID_YEAR
     AND J.STRM = B.STRM
     AND J.EDIT_MSG_CD = 'OVERRV'
     AND J.FA_MSG_ACTION = '2'
     AND NOT EXISTS (SELECT 'X'
  FROM PS_STDNT_AWRD_ACTV M
  WHERE M.EMPLID = B.EMPLID
     AND M.INSTITUTION = B.INSTITUTION
     AND M.AID_YEAR = B.AID_YEAR
     AND M.ITEM_TYPE = B.ITEM_TYPE
     AND M.DISBURSEMENT_ID = B.DISBURSEMENT_ID
     AND M.AWARD_DISB_ACTION = 'P'
     AND M.DISB_AMOUNT > 0
     AND M.ACTION_DTTM > J.FA_MSG_DTTIME)))
     AND NOT EXISTS (SELECT 'X'
  FROM PS_SRVC_IND_DATA Q
  WHERE Q.EMPLID = A.EMPLID
     AND Q.SRVC_IND_CD = 'DFA')
     AND A.EMPLID = B.EMPLID
     AND A.INSTITUTION = B.INSTITUTION
     AND A.AID_YEAR = B.AID_YEAR
     AND B.AID_YEAR = C.AID_YEAR
     AND B.ITEM_TYPE = C.ITEM_TYPE
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_ITEM_TYPE_FA C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.AID_YEAR = C_ED.AID_YEAR
          AND C_ED.EFFDT <= SYSDATE)
     AND A.INSTITUTION = D.INSTITUTION
     AND A.AID_YEAR = D.AID_YEAR
     AND A.AWARD_PERIOD = D.AWARD_PERIOD
     AND B.STRM = D.STRM
     AND B.ACAD_CAREER = D.ACAD_CAREER
     AND B.EMPLID = E.EMPLID
     AND B.INSTITUTION = E.INSTITUTION
     AND B.AID_YEAR = E.AID_YEAR
     AND B.ITEM_TYPE = E.ITEM_TYPE
     AND B.ACAD_CAREER = E.ACAD_CAREER
     AND E.EMPLID = F.EMPLID
     AND E.INSTITUTION = F.INSTITUTION
     AND E.AID_YEAR = F.AID_YEAR
     AND E.ITEM_TYPE = F.ITEM_TYPE
     AND E.ACAD_CAREER = F.ACAD_CAREER
     AND A.INSTITUTION = O.INSTITUTION
     AND A.AID_YEAR = O.AID_YEAR
     AND A.AWARD_PERIOD = O.AWARD_PERIOD
     AND O.STRM = B.STRM
     AND B.EMPLID = P.EMPLID
     AND B.INSTITUTION = P.INSTITUTION
     AND B.AID_YEAR = P.AID_YEAR
     AND P.STRM = B.STRM
     AND P.EFFDT =
        (SELECT MAX(P_ED.EFFDT) FROM PS_STDNT_FA_TERM P_ED
        WHERE P.EMPLID = P_ED.EMPLID
          AND P.INSTITUTION = P_ED.INSTITUTION
          AND P.STRM = P_ED.STRM
          AND P_ED.EFFDT <= SYSDATE)
    AND P.EFFSEQ =
        (SELECT MAX(P_ES.EFFSEQ) FROM PS_STDNT_FA_TERM P_ES
        WHERE P.EMPLID = P_ES.EMPLID
          AND P.INSTITUTION = P_ES.INSTITUTION
          AND P.STRM = P_ES.STRM
          AND P.EFFDT = P_ES.EFFDT))
  GROUP BY  A.AID_YEAR,  A.EMPLID,  A.AWARD_PERIOD,  A.FED_OVRAWD_AMT,  A.FED_OVRAWD_COA,  B.ITEM_TYPE,  B.OFFER_BALANCE,  B.ACCEPT_BALANCE,  B.AUTHORIZED_BALANCE,  B.DISBURSED_BALANCE,  B.STRM,  B.ACAD_CAREER,  E.NET_AWARD_AMT,  E.DISBURSED_AMOUNT,  E.DISBURSEMENT_PLAN,  P.FA_LOAD
  ORDER BY 1 DESC, 2, 3 DESC, 6, 11