CEO Fees and Charges Export Explained

Modified on Tue, 24 Feb at 4:40 AM

TABLE OF CONTENTS

Summary
- NOTE the left column query provides the amount, the right column query lists all the fee codes involved in the calculation.
- Where available change the 0 after WHERE to a different year level to view / calculate fees for that year level.
School Fees
Capital Fees
- Year Level Capital Fees
- Family Capital Fees
Fees
Excursion Levy
Swimming Levy
Camp Levy
Excursion Levy
SAC Levy
Other Levy
Registration Fee
Enrollment Fee
Enrollment Fee Refundable
Interest Free Loan
Interest Free Loan Comment
Number of Families
List of Families
Excursion Levy Total

Summary

This article will explain where all the data that is used to create the CEO Fees and Charges Export comes from.

To make use of this guide ideally should have access to SQL Server Management Studio and have a basic understanding of SQL queries

NOTE the left column query provides the amount, the right column query lists all the fee codes involved in the calculation.

Where available change the 0 after WHERE to a different year level to view / calculate fees for that year level.

School Fees

Fee Calculation

Fees Included in Calculation

Year Level School Fees

SELECT SUM(df.AnnualAmount)        FROM finance.vDebtorFees df        WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error          AND df.AutoTuitionCode <> ''           AND df.TuitionVariationType = ''           AND df.FeeCategorySynergyMeaningCategory = '110'          AND df.FamilyPosition = 0          AND df.ActiveFlag = 1 --SL7934          AND df.SiblingCount = 0 --SL 22/02/11 (was 0,1 but if 1 then should be family fee)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '110' AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount = 0 --SL 22/02/11 (was 0,1 but if 1 then should be family fee)

Family School Fees

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE -- 12 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error  df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '110' AND df.ActiveFlag = 1 --SL7934 AND (df.FamilyPosition = 1 OR df.SiblingCount = 1) -- use 1st child for family fees

SELECT * FROM finance.vDebtorFees df WHERE -- 12 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error  df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '110' AND df.ActiveFlag = 1 --SL7934 AND (df.FamilyPosition = 1 OR df.SiblingCount = 1) -- use 1st child for family fees

Capital Fees

Year Level Capital Fees

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '120' AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '120' AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Family Capital Fees

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE -- ISNULL(NULLIF(df.YearLevel, ''), -1) = -1 AND ISNULL(NULLIF(df.YearLevelTo, ''), -1) = -1 -- RA28128 df.AutoTuitionCode <> '' -- RA28128 AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '120' AND df.FamilyPosition = 1 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE -- ISNULL(NULLIF(df.YearLevel, ''), -1) = -1 AND ISNULL(NULLIF(df.YearLevelTo, ''), -1) = -1 -- RA28128 df.AutoTuitionCode <> '' -- RA28128 AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '120' AND df.FamilyPosition = 1 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Fees

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '210' AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory = '210' AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Excursion Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310', '320', '330','340','350','') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310', '320', '330','340','350','') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Swimming Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('320') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('320') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Camp Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('330') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('330') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Excursion Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SAC Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('340') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('340') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Other Levy

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('350','') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('350','') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

Registration Fee

Enrollment Fee

SELECT TOP 1 ed.DepositAmount FROM finance.EnrolmentDepositDefinitions ed WHERE ed.DepositType = 'A'

SELECT TOP 1 ed.DepositAmount FROM finance.EnrolmentDepositDefinitions ed WHERE ed.DepositType = 'E' AND ed.RefundableFlag = 0

Enrollment Fee Refundable

SELECT TOP 1 ed.DepositAmount FROM finance.EnrolmentDepositDefinitions ed WHERE ed.DepositType = 'E' AND ed.RefundableFlag <> 0

Interest Free Loan

Interest Free Loan Comment

SELECT TOP 1 ed.DepositAmount FROM finance.EnrolmentDepositDefinitions ed WHERE ed.DepositType = 'I' AND ed.RefundableFlag <> 0

SELECT TOP 1 ed.Comment FROM finance.EnrolmentDepositDefinitions ed WHERE ed.DepositType = 'I' AND ed.RefundableFlag <> 0

Number of Families

List of Families

SELECT COUNT(DISTINCT sc.LinkedID) FROM finance.pvStudentYears sy INNER JOIN finance.pvStudentContacts sc ON ( sc.ID = sy.ID AND sc.ContactType = 'SC1') WHERE sy.FileYear = @FileYear)

SELECT DISTINCT sc.LinkedID FROM finance.pvStudentYears sy INNER JOIN finance.pvStudentContacts sc ON ( sc.ID = sy.ID AND sc.ContactType = 'SC1') WHERE sy.FileYear = 2020

Excursion Levy Total

SELECT SUM(df.AnnualAmount) FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310', '320', '330') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)

SELECT * FROM finance.vDebtorFees df WHERE 0 BETWEEN ISNULL(df.YearLevel, -999) AND ISNULL(df.YearLevelTo, 999) -- 19/02/2007 AR, fix YearLevelTo error AND df.AutoTuitionCode <> ''  AND df.TuitionVariationType = ''  AND df.FeeCategorySynergyMeaningCategory IN ('310', '320', '330') AND df.FamilyPosition = 0 AND df.ActiveFlag = 1 --SL7934 AND df.SiblingCount IN (0, 1)