Purpose

To query on a single Sponsor code

SubSystem

Finance

This function enables users to query on a single Sponsor Code and check details connected with the sponsor’s sponsorships, including debts, credits, payments, refunds, etc. The information is broken down by Student and Fee Period. The user can view a complete list of all students that the sponsor is sponsoring, or restrict this list by Fee Period.

The sponsored (debt) amounts will be the amounts the sponsor is expected to pay rather than the gross sponsored amounts of each debt. So where discounts are involved they are expected to pay the discounted amount unless the debt is not paid and the due date has passed or the debt is paid late.

The data displayed can be downloaded to a csv file.

This form is accessed from the main menu.

The Sponsor Inquiry block contains:

• Sponsor Code (LOV)
• Sponsor Status
• Payment Advice Number
• Total Paid
• Total Liability
• Total Allocated Amount
• Balance
• Total Refund
• Unallocated Amount

Tabs (disabled when the above block is in Query Mode)

The Student Details tab contains:

Financial and Fee Calendars (Title)

• Financial Period (LOV)
• Fee Period (LOV)

Sponsored Students (Title)

• Display Owing Students Only check box
• Financial Period
• Fee Period
• Person ID
• Course Code
• Fee Category
• Fee Type
• Sponsored Amount
• Payment Received
• Amount Owed
• Total (Sponsored Amount)
• Total (Payment Received)
• Total (Amount Owed)
• Person Name
• Total Sponsorship Limit
• Course Title
• Version Number

Buttons

• Retrieve List (fills in fields of Sponsored Students)
• Units (overlay to Unit Sponsorship )
  • Person ID
  • Financial Period
  • Dee Period
  • Unit Code
  • Version
  • Title
  • Sponsored Amount
  • Student Status
  • Download Records Button
  • Back button
• Download Records

The Payment Details tab contains:

• Receipt Date
• Receipt Number
• Currency
• Transaction Amount
• Applied amount
• Unallocated
• Payment Type
• Pay Sort Key
• Total

Button

• Download Records

The Sponsor Details tab contains:

• Financial Period
• Fee Period
• Total Liability
• Total Paid
• Balance
• Total

Button

• Download Records

Rule/Notes:

The Sponsor Inquiry block

The Sponsor Code can only be queried.

A lamp will display either OWING, BALANCED or CREDIT.

The amounts displayed here are derived for each sponsor queried in the top block (Sponsor Code).

The Total Paid field contains the sum of all payments made by the sponsor.

The Unallocated Amount field is derived as the Total Paid – (Total Allocated Amount + Total Refund).

The Total Refund field contains the sum of all refunds issued to the sponsor.

The Student Details Tab

The Financial Period field is Mandatory.

The Fee Period is restricted to only when a student is sponsored. If the Fee Period is not specified, then ALL Fee Periods applicable for the Financial Period apply.

The Display Owing Students Only check box is checked when user only wants records on sponsors who owe money to be queried.

The Retrieve List button will be enabled if % is selected for the Financial and Fee Periods. In some cases the Retrieve List may return no records.

The Units button is only displayed when a sponsor is sponsoring the particular units for the Student Course Attempt.

Receipt Date

SELECT SUM(sbv.debt_amount)
FROM sponsor_balance_v sbv
WHERE sbv.sponsor_cd = sfsv.sponsor_cd
GROUP BY sbv.sponsor_cd

SELECT SUM(sbv.paid_amount)
FROM sponsor_balance_v sbv
WHERE sbv.sponsor_cd = sfsv.sponsor_cd
GROUP BY sbv.sponsor_cd

SELECT SUM(sbv.balance_amount)
FROM sponsor_balance_v sbv
WHERE sbv.sponsor_cd = sfsv.sponsor_cd
GROUP BY sbv.sponsor_cd

If Total Paid – Total Liability > 0 then Total Refund = Total Paid – Total Liability
Else
Total Refund = 0

The LOV and field definitions will make this field mandatory.
LOV restricted to % and financial periods for which students have been sponsored. This is achieved by querying the sponsor_debt_v for records with the same sponsor_cd as in the Sponsor Inquiry record

LOV restricted to % and fee periods for which students have been sponsored. This is achieved by querying the sponsor_debt_v for records with the same sponsor_cd as in the Sponsor Inquiry record and fee period as specified above.
If the Fee Period is not specified then ALL Fee Periods appicable to the Financial Period apply

Checkbox can only be selected in query mode (i.e., before the query and not after).
If records have already been queried then disable the check box.
If selected, only records with an ‘Amount Owed’ that is greater than 0 will be selected and included in the totals

This button will be enabled only when Sponsored Fee has a management level of UNIT

The lamp will display one of ‘OWING’, ‘BALANCED’ or CREDIT. dependent on balance_amount

0 – BALANCED
<0 CREDIT
>0 OWING