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

Last Modified on 4 March, 2004