Sponsor Summary Statement Extract

Sponsor Summary Statement Extract - View Details

Sponsor Summary Extract View Details - This view presents information about each individual instance on which the Statement of Account Extract creation process (FINJ6120) and the Reminder Notice Extract creation process (FINJ6121) is run. Student and student finance records relating to an individual run instance are presented in the Statement of Account Record view.
Sponsor Summary Record View Details - This view presents statement of account records. Each record is for a particular student course attempt.

Sponsor Summary Extract View Details

The table shows details of the fields presented by this view.

SPNSR_SUM_EXTRACT_V
Field Name	Field Description	Data Source
CREATION_DT	Extract Creation Date	System
REQUEST_PERSON_ID	ID number of person who requested this job	Job run parameter (numbers - 10)
CORRESPONDENCE_TYPE	The type of Correspondence created by the job run	Job run parameter (characters - 10)
FIN_CAL_TYPE	The Calendar Type of the Financial Period for which the job was run	(characters - 10) Job run parameter (numbers - 6)
FIN_CI_SEQUENCE_NUMBER	The Calendar Instance Sequence Number of the Financial Period for which the job was run	(characters - 10) Job run parameter (numbers - 6)
FEE_CAL_TYPE	The Calendar Type of the Fee Period for which the job was run	(characters - 10) Job run parameter (numbers - 6)
FEE_CI_SEQUENCE_NUMBER	the Calendar Instance Sequence Number of the Fee Period for which the job was run	(characters - 10) Job run parameter (numbers - 6)
FEE_TYPE	Fee Type	Job run parameter (characters - 10)
FEE_CAT	Fee Category	Job run parameter (characters - 10)
COURSE_CD	Course Code	Job run parameter (characters - 6)
SPONSOR_CD	Sponsor Code	Job run parameter (characters - 10)
INSTITUTION_CD	Institution Code	Job run parameter (characters - 10)
ADDR_TYPE	Institution Address Type	Job run parameter (characters - 10)
DATE_OF_ISSUE	Planned Date of Issue of Sponsor Statements	Job run parameter (dd/mm/yyyy)
EXTRACT_Comment	Comments for inclusion on all statements of account	Job run parameter (up to 60 characters)
TEST_EXTRACTION	Indicates whether or not FINJ6130 was run as a Test Extraction only, i.e. no Correspondence Items recorded.	Job run parameter (Y or N)
REQUEST_JOB_RUN_ID	System generated ID number of the request job run	passed by job scheduler (numbers - 10)
REQUEST_NUM	System generated number of the request in which the job was run	derived from request job run ID (numbers - 9)
UPDATE_WHO		System (up to 30 characters)
UPDATE_ON		System (dd/mm/yyyy hh24:mi:ss)

Sponsor Summary Record View Details

The table provides details of the different Record Types presented by this view and the fields in each Record Type.

1. Header
2. Sponsor
3. Addresses
4. Sponsored Person
5. Sponsored Course Attempts
6. Transaction Details
7. External References
8. Sponsored Totals
9. Footer

SPNSR_SUM_RECORD_V
Note: All references to source database tables and columns are in italics. Fields in bold type are included for sorting purposes. Field names in brown text are key fields, specifically included for sorting purposes.
Field Name	Field Description		Max Length	Type	Source / Value
1. HEADER
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD			'0000000000'
MAJOR_SORT_SEQUENCE			blank
KEY5			blank
COURSE_CD			blank
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'HEADER'
FIELD1	Comment		parameter
FIELD2	Financial Calendar Type		cal_instance.cal_type
FIELD3	Financial Calendar Description		cal_type.description
FIELD4	Financial Calendar Start Date		cal_instance.start_dt
FIELD5	Financial Calendar End Date		cal_instance.end_dt
FIELD6	Address Type		institution_addr .addr_type
FIELD7	Address Line 1		institution_addr .addr_line_1
FIELD8	Address Line 2		institution_addr .addr_line_2
FIELD9	Address Line 3		institution_addr .addr_line_3
FIELD10	Address Line 4		institution_addr .addr_line_4
FIELD11	Address Line 5		institution_addr .addr_line_5
FIELD12	Australian Postcode		institution_addr .aust_postcode
FIELD13	Overseas Postal code		institution_addr .os_code
FIELD14	phone 1		institution_addr .phone_1
FIELD15	phone 2		institution_addr .phone_2
FIELD16	phone 3		institution_addr .phone_3
FIELD17	Other Details		institution_addr .other_details
FIELD18	Correspondence check box		addr_type .correspondence_ind
FIELD19-40			blank
Field Name	Field Description		Source / Value
2. SPONSOR
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE			blank
KEY5			blank
COURSE_CD			blank
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'SPONSOR'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Sponsor Type		student_fee_sponsor .sponsor_type
FIELD3	Sponsor Status		student_fee_sponsor .sponsor_status
FIELD4	Person ID		student_fee_sponsor .person_id
FIELD5	Organisational Unit Code		student_fee_sponsor .org_unit_cd
FIELD6	Organisational Unit Start Date		student_fee_sponsor .start_dt
FIELD7	Sponsor Name		derived
FIELD8	Comments		student_fee_sponsor .Comments
FIELD9	Payment Advice Number		student_fee_sponsor .payment_advice_number
FIELD10	Date of Issue		Job run parameter or default (System date)
FIELD11	Last Statement		greatest correspondence_item .create_dt for the sponsor
FIELD12-40			blank
Field Name	Field Description		Source / Value
3. ADDRESSES
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor Code of The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'03'
KEY5	Identification of the Sponsor who may be a person or an organisation		student_fee_sponsor .person_id or else student_fee_sponsor .org_unit_cd
COURSE_CD			blank
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'ADDRESSES'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Sponsor ID		org_unit.org_unit_cd /person.person_id
FIELD3	Address Type		org_addr.addr_type / person_addr.addr_type
FIELD4	Address Line 1		org_addr.addr_line_1 / person_addr.addr_line_1
FIELD5	Address Line 2		org_addr.addr_line_2 / person_addr.addr_line_2
FIELD6	Address Line 3		org_addr.addr_line_3 / person_addr.addr_line_3
FIELD7	Address Line 4		org_addr.addr_line_4 / person_addr.addr_line_4
FIELD8	Address Line 5		org_addr.addr_line_5 / person_addr.addr_line_5
FIELD9	Australian Postcode		org_addr.aust_postcode / person_addr .aust_postcode
FIELD10	Overseas Postal code		org_addr.os_code / person_addr.os_code
FIELD11	Phone 1		org_addr.phone_1 / person_addr.phone_1
FIELD12	Phone 2		org_addr.phone_2 / person_addr.phone_2
FIELD13	Phone 3		org_addr.phone_3 / person_addr.phone_3
FIELD14	Other Details		person_addr .other_details
FIELD15	Correspondence check box		addr_type .correspondence_ind
FIELD16-40			blank
Field Name	Field Description		Source / Value
4. SPONSORED PERSON
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE			'04'
KEY5	The ID number of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .person_id
COURSE_CD			blank
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'SPONSORED-PERSON'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Person ID		stdnt_crs_atmpt_ fee_spnsrshp .person_id
FIELD3	Title		person.title
FIELD4	Surname		person.surname
FIELD5	Given Names		person.given_names
FIELD6-40			blank
Field Name	Field Description		Source / Value
5. SPONSORED COURSE ATTEMPTS
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'04'
KEY5	The ID number of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .person_id
COURSE_CD	The Course Code of the Course Attempt of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .course_cd
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'SPONSORED-CRS- ATMPTS'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Person ID		stdnt_crs_atmpt_ fee_spnsrshp .person_id
FIELD3	Course Code		stdnt_crs_atmpt_ fee_spnsrshp .course_cd
FIELD4	Version		student_course_attempt .version_number
FIELD5	Title		course_version.title
FIELD6	Short Title		course_version.short_title
FIELD7	Course Type		course_version .course_type
FIELD8	Location		student_course_attempt .location_cd
FIELD9	Attendance Mode		student_course_attempt .attendance_mode
FIELD10	Attendance Type		student_course_attempt .attendance_type
FIELD11	Derived Attendance Mode		student_course_attempt .derived_att_mode
FIELD12	Derived Attendance Type		student_course_attempt .derived_att_type
FIELD13	Status		student_course_attempt .course_attempt_status
FIELD14	Commencement Date		student_course_attempt .commencement_dt
FIELD15	Discontinued Date		student_course_attempt .discontinued_dt
FIELD16	Fee Category		student_course_attempt .fee_cat
FIELD17	Fee Category Description		fee_cat.description
FIELD18	Correspondence Category		student_course_attempt .correspondence_cat
FIELD19	Fee Period Sponsorship Status		fee_cat_ci_spnsrshp.fee.sponsorship_status
FIELD20	Sponsorship Limit		stdnt_crs_atmpt _fee_spnsrshp .sponsorship_limit
FIELD21	Percentage Contribution		stdnt_crs_atmpt _fee_spnsrshp .percentage_contribution
FIELD22-40			blank
Field Name	Field Description		Source / Value
6. TRANSACTION DETAILS
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'04'
KEY5	The ID number of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .person_id
COURSE_CD	The Course Code of the Course Attempt of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .course_cd
FEE_TYPE	The type of the Fee that the Transaction refers to		fee_ass.fee_type
MINOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'06'
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'TRANSACTION- DETAILS'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Person ID		stdnt_crs_atmpt_ fee_spnsrshp .person_id
FIELD3	Course Code		stdnt_crs_atmpt_ fee_spnsrshp .course_cd
FIELD4	Fee Type		fee_ass.fee_type
FIELD5	Fee Type Description		fee_type.description
FIELD6	Fee Assess-ment Period	Fee Calendar Type	cal_instance.cal_type
FIELD7		Fee Calendar Instance Sequence Number	cal_instance .sequence_number
FIELD8	Fee Calendar Description		cal_type.description
FIELD9	Fee Calendar Start Date		cal_instance.start_dt
FIELD10	Fee Calendar End Date		cal_instance.end_dt
FIELD11	Fee Assessment Period Start		dt_alias_instance_v .alias_val
FIELD12	Fee Assessment Period End		dt_alias_instance_v .alias_val
FIELD13	System Fee Type		fee_type.s_fee_type
FIELD14	System Fee Trigger Category		fee_type .s_fee_trigger_cat
FIELD15	Optional Payment		fee_type .optional_payment_ind
FIELD16	Charge Method		fee_type_cal_instance .s_chg_method_type / fee_cat_fee_liability .s_chg_method_type
FIELD17	Transaction Category		s_transaction_type .transaction_cat
FIELD18	Transaction Type		fee_ass .s_transaction_type
FIELD19	Transaction Date		fee_ass .effective_dt OR person_payment_schedule .payment_due_dt
FIELD20	Transaction Amount		transaction_amount
FIELD21	Tax Amount		If transaction category is PAYMENT, calculated OR if transaction category is DEBT, person_payment_schedule.tax_amount
FIELD22	Transaction ID		fee_ass.transaction_id Note, payment details only
FIELD23	Sponsored Amount		sponsored_amount
FIELD24	Sponsor Tax Amount		person_payment_schedule.sponsored_tax_amount
FIELD25	Discount Percentage		discount_percentage
FIELD26	Discount Amount		discount_amount
FIELD27	Discount Minimum Payment		discount_min_payment
FIELD28	Currency		currency_cd
FIELD29	Exchange Rate		exchange_rate
FIELD 30	Expected Payment		Derived value
FIELD 31	Expected Tax Amount		Derived value
FIELD 32	Discount Available		Derived value
FIELD 33	Tax Discount Available		Derived value
FIELD 34	Tax Reference		fee_cat_cal_instance.tax_reference
FIELD 35	Limited Payment check box		if expected amount for student for student has been limited by the student_crs_hecs_opt_override.maximum_payment_limit =Y otherwise N
FIELD 36	Unit Code		unit_cd
FIELD 37	Unit Offering ID		uoo_id
FIELD 38	Sponsorship Limit		sponsorship_limit
FIELD 39	Percentage Contribution		percentage_contibution
FIELD 40	Sponsorship Level		v_level As determined by get_student_configured_sponsorship_details routine
Field Name	Field Description		Source / Value
7. EXTERNAL REFERENCES
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'04'
KEY5	The ID number of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .person_id
COURSE_CD	The Course Code of the Course Attempt of the student being Sponsored		stdnt_crs_atmpt_ fee_spnsrshp .course_cd
FEE_TYPE	The type of the Fee that the Transaction refers to		fee_ass.fee_type
MINOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'07'
TRANSACTION_ID	Transaction ID		fee_ass .transaction_id
RECORD_TYPE	Record Type		'EXTERNAL- REFERENCES'
FIELD1	Sponsor Code		student_fee_sponsor .sponsor_cd
FIELD2	Person ID		stdnt_crs_atmpt _fee_spnsrshp .person_id
FIELD3	Course Code		stdnt_crs_atmpt _fee_spnsrshp .course_cd
FIELD4	Fee Type		fee_ass.fee_type
FIELD5	Transaction ID		fee_ass.transaction_id
FIELD6	External Reference Code		fee_ass_external_ref .external_reference_cd
FIELD7	External Reference Type		fee_ass_external_ref .external_refrence_type
FIELD8	External Reference Type Description		external_reference_type .description
FIELD9-40			blank
Field Name	Field Description		Source / Value
8. SPONSORED TOTALS
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'04'
KEY5	Sponsored Student		fee_cat_ci.spnsrshp.person_id
COURSE_CD	Sponsored Course Attempt		fee_cat_ci.spnsrshp.course_cd
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'SPONSORED-TOTALS'
FIELD1	Total Sponsorship Limit		Stdnt_crs_atmpt_fee_spnsrshp.total_sponsorship_limit
FIELD2	Total Sponsored amount		Sum of all sponsored_amount from fee_statement_unit_v for a given sponsor, person and course
FIELD3	Total Sponsored Tax amount		Sum of all sponsor_tax_amount from fee_statement_unit_v for a given sponsor, person and course
FIELD4-40			blank
Field Name	Field Description		Source / Value
9. FOOTER
CREATION_DT	Date and Time of Creation of the Extract		System
FIN_CAL_TYPE	The financial Period for which the Extract was run		Job run parameter
FIN_CI_SEQUENCE _NUMBER
SPONSOR_CD	The Sponsor to which the record refers		student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE	Used to Group Records to parent/child relationship of data		'04'
KEY5			blank
COURSE_CD			blank
FEE_TYPE			blank
MINOR_SORT_SEQUENCE			blank
TRANSACTION_ID			blank
RECORD_TYPE	Record Type		'FOOTER'
FIELD1	Count of Record Type SPONSOR		calculated
FIELD2	Count of Record Type ADDRESSES		calculated
FIELD3	Count of Record Type SPONSORED-PERSON		calculated
FIELD4	Count of Record Type SPONSORED-CRS-ATMPTS		calculated
FIELD5	Count of Record Type TRANSACTION-DETAILS		calculated
FIELD6	Count of Record Type EXTERNAL-REFERENCES		calculated
FIELD7	Count of Record Type SPONSORED-TOTAL		calculated
FIELD8-40			blank

Last modified on 30 May, 2012 8:51 AM

History Information

Release Version	Project	Change to Document
12.1	Calipso 28827	Inclusion of details from project 1094