Query 1: Hierarchical Financial Reporting (Oracle ERP)
SELECT
campus,
college_name,
dept_name,
account_category,
fiscal_year,
SUM(amount) as total_spending
FROM vw_financial_summary
WHERE account_type = 'Expense'
AND fiscal_year = 2024
GROUP BY ROLLUP(campus, college_name, dept_name, account_category, fiscal_year)
ORDER BY campus, college_name, dept_name; ```
This query demonstrates hierarchical rollup capabilities—subtotals at campus, college, and department levels—essential for university financial reporting.
Query 2: Student Enrollment Analysis (Banner SIS)
SELECT
program_name,
college, term_code,
COUNT(DISTINCT student_banner_id) as student_count,
COUNT(*) as total_enrollments,
ROUND(AVG(grade_points), 2) as avg_gpa,
SUM(credit_hours) as total_credit_hours,
SUM(CASE WHEN is_passing = TRUE THEN 1 ELSE 0 END) as passing_count,
ROUND(100.0 * SUM(CASE WHEN is_passing = TRUE THEN 1 ELSE 0 END) / COUNT(*), 1) as pass_rate FROM vw_enrollment_summary
WHERE term_code = 'Fall 2024'
AND is_completed = TRUE
GROUP BY program_name, college, term_code
ORDER BY student_count DESC;
This query analyzes enrollment patterns, academic performance, and pass rates by program—demonstrating Banner SIS integration for academic analytics.
Query 3: Integrated Student Financial Analysis (Banner + Oracle)
SELECT
student_name,
program_name,
student_level,
term_code,
SUM(CASE WHEN is_charge = TRUE THEN amount ELSE 0 END) as total_charges,
SUM(CASE WHEN is_aid = TRUE THEN amount ELSE 0 END) as total_aid,
SUM(CASE WHEN is_payment = TRUE THEN amount ELSE 0 END) as total_payments,
SUM(CASE WHEN is_charge = TRUE THEN amount ELSE 0 END) -
SUM(CASE WHEN is_aid = TRUE THEN amount ELSE 0 END) as net_tuition,
STRING_AGG(DISTINCT aid_type, ', ') as aid_types_received
FROM vw_student_revenue_analysis
WHERE term_code = 'Fall 2024'
GROUP BY student_name, program_name, student_level, term_code
HAVING SUM(CASE WHEN is_charge = TRUE THEN amount ELSE 0 END) > 0
ORDER BY total_charges DESC;
This query demonstrates the key integration point: combining Banner financial aid data with Oracle billing/payment data to provide a complete student financial picture—charges, aid disbursements, payments, and net revenue per student.
