Sponsor Summary Statement Extract - View Details

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 (num(10))
CORRESPONDENCE_TYPE the type of correspondence created by the job run job run parameter (char(10))
FIN_CAL_TYPE the calendar type of the financial period for which the job was run

(char(10))

job run parameter

(num(6))

FIN_CI_SEQUENCE_NUMBER the calendar instance sequence number of the financial period for which the job was run
FEE_CAL_TYPE the calendar type of the fee period for which the job was run

(char(10))

job run parameter

(num(6))

FEE_CI_SEQUENCE_NUMBER the calendar instance sequence number of the fee period for which the job was run
FEE_TYPE fee type job run parameter (char(10))
FEE_CAT fee category job run parameter (char(10))
COURSE_CD course code job run parameter (char(6))
SPONSOR_CD sponsor code job run parameter (char(10))
INSTITUTION_CD institution code job run parameter (char(10))
ADDR_TYPE institution address type job run parameter (char(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 char(60))
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 (num(10))
REQUEST_NUM system generated number of the request in which the job was run derived from request job run ID (num(9))
UPDATE_WHO   system (up to char(30))
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.

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 blue 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 6 num
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 addr type 10 char institution_addr .addr_type
FIELD7 addr line 1 40 char institution_addr .addr_line_1
FIELD8 addr line 2 40 char institution_addr .addr_line_2
FIELD9 addr line 3 40 char institution_addr .addr_line_3
FIELD10 addr line 4 40 char institution_addr .addr_line_4
FIELD11 addr line 5 40 char institution_addr .addr_line_5
FIELD12 aust postcode 4 num institution_addr .aust_postcode
FIELD13 os 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 indicator 1 char addr_type .correspondence_ind
FIELD19-34 blank
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 6 num
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
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 6 num
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 according 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 addr type 10 char org_addr.addr_type / person_addr.addr_type
FIELD4 addr line 1 40 char org_addr.addr_line_1 / person_addr.addr_line_1
FIELD5 addr line 2 40 char org_addr.addr_line_2 / person_addr.addr_line_2
FIELD6 addr line 3 40 char org_addr.addr_line_3 / person_addr.addr_line_3
FIELD7 addr line 4 40 char org_addr.addr_line_4 / person_addr.addr_line_4
FIELD8 addr line 5 40 char org_addr.addr_line_5 / person_addr.addr_line_5
FIELD9 aust postcode 4 num org_addr.aust_postcode / person_addr .aust_postcode
FIELD10 os 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 indicator 1 char addr_type .correspondence_ind
FIELD16-34 blank
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 6 num
SPONSOR_CD the sponsor to which the record refers 10 char student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE  used to group records according 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
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 6 num
SPONSOR_CD the sponsor to which the record refers 10 char student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE  used to group records according 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
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 6 num
SPONSOR_CD the sponsor to which the record refers 10 char student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE  used to group records according 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 according 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 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 indicator 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
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 6 num
SPONSOR_CD the sponsor to which the record refers 10 char student_fee_sponsor .sponsor_cd
MAJOR_SORT_SEQUENCE  used to group records according 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 according 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
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 6 num
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 11 March 2002