Discussion of Student Financial and Financial Aid:
Difference between Student Financial and Financial Aid
Student Financials: This is related to bursars and it applies to students once they enrolled any classes, canceled classes, tuition or any education related expense. Charges and payment method apply to all current students. Sometimes student might receive waived of charges based on their eligibility. The payment method could be process through financial aid, cash, checks or money orders depends on students eligibility.
Financial Aid: It does not apply to all students. It only applies those who are eligible in terms of low income or scholarships / awards or loans such as tap/ pall grant. This apply once a student process an application and get accepted. This has nothing to do with payment and charges.
Student Financial query review:
CU_BAR_SF_00002:
In sub-query :
C.NET_AWARD_AMT - Net Award Amount greater than 0 (Why this > 0)?
This is greater than 0 because the query is eliminating students with $ 0 balance. It is also eliminating those students who have 6 or less than 6 credits because they are not eligible for any financial aid.
This query contain with 3 sub-queries and an union. In sub-query we are looking for latest record through A. anticipated _ aid.
CU_BAR_SF_00005:
This query generates a report on revenue detail based on student academic career level of standing. Retrieve all the students through maximum effective date(means their latest activities).
SELECT A.ACAD_CAREER, C.ACAD_PROG_PRIMARY, A.SEL_GROUP, CASE
WHEN C.UNT_TAKEN_PRGRSS BETWEEN 12 AND 30 THEN 'Full'
WHEN C.UNT_TAKEN_PRGRSS BETWEEN 0.01 AND 11.99 THEN 'Part'
ELSE ' '
END, A.ITEM_TYPE, B.DESCR, B.KEYWORD1, count ( A.EMPLID), sum ( C.UNT_TAKEN_PRGRSS), SUM ( A.ITEM_AMT), TO_CHAR(SYSDATE,'YYYY-MM-DD'),B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD')
FROM PS_ITEM_SF A, PS_ITEM_TYPE_TBL B, PS_STDNT_CAR_TERM C
WHERE A.BUSINESS_UNIT = :1
AND A.ACCOUNT_TERM = :2
AND B.ITEM_TYPE = A.ITEM_TYPE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.ITEM_TYPE = B_ED.ITEM_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND A.BUSINESS_UNIT = B.SETID
AND A.ITEM_AMT <> 0
AND A.EMPLID = C.EMPLID (+)
AND A.ACAD_CAREER = C.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+)
AND A.BUSINESS_UNIT = C.INSTITUTION (+)
AND A.ACCOUNT_TERM = C.STRM (+)
GROUP BY A.ACAD_CAREER, C.ACAD_PROG_PRIMARY, A.SEL_GROUP, CASE
WHEN C.UNT_TAKEN_PRGRSS BETWEEN 12 AND 30 THEN 'Full'
WHEN C.UNT_TAKEN_PRGRSS BETWEEN 0.01 AND 11.99 THEN 'Part'
ELSE ' '
END, A.ITEM_TYPE, B.DESCR, B.KEYWORD1,B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD')
ORDER BY 1, 2, 3, 4, 5
A. ITEM_SF - ACCOUNT TERM -
C. STDNT_CAR_TERM - STRM - TERM
prompt: choose ACCOUNT_TERM instead of STRM. WHY?
The account term has been chosen because we are dealing with student financial side that's why its better to take account term. Moreover, in this query Business_Unit represent as an Institution (BAR01) which has been used through ITEM_SF record.
Expression:
Why Length 16 bites?
If a student take 4 course with 4 credits each then it is 16 then based on their academic level it might increase the number. That's why the 16 bites is just the assumption of the output.
CU_BAR_SF_00009:
A. TERM_TBL - STRM
H. STDNT_CAR_TERM - STRM
Choose STRM from H. record STDNT_CAR_TERM over TERM_TBL. Does it matter where to choose from?
Yes, because it totally depends on which record has been used to choose Institution in the criteria.
CU_BAR_SF_00010C:
Why same records have been used twice?
The same records have been twice because the first A.ITEM_SF is looking for charges, it cross referencing to B. ITEM_XREF to look for amount and item description from D.ITEM_TYPE_TBL. The same loop goes to Payment method as well.
In the query it is looking for the latest transaction to find the charges and payment.
CU_BAR_SF_00011:
Expression: '' this empty quotation is just created or where it has been used in query?
No, the quotation was never used in this query. It was created because to make an union with an empty field but later user changed his mind.
Financial Aid query review:
ANTICIPATED_AID (define): A pending financial aid award can be serve as a payment for eligible financial aid student for his/her class. This award can be equal or not greater than tuition fees.
CU_BAR_FA_00003:
HAVING criteria, GROUP BY (how to do this?)
AND B.STRM = A.ACCOUNT_TERM
AND A.ACCOUNT_BALANCE > 0 )
GROUP BY B.EMPLID, A.ACCOUNT_BALANCE
HAVING A.ACCOUNT_BALANCE - sum ( B.NET_AWARD_AMT) <= 0
ORDER BY 1
A.ACCOUNT_BALANCE - sum (B.NET_AWARD_AMT) not greater than 0 (Why?)
The query is not looking for student who has $ 0 balance. Moreover, award amount is always equal or grater than tuition.
SSF_APPR_STATUS - Approval S: For a transaction purpose
