[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| to select active (not closed) unit classes.
| gmaheswa 25-Jan-2005 Bug 3882788 Modified c_get_api to exclude person
| identifiers whose start_dt = end_dt.
|
| ctyagi 15-Apr-2004 Obsolete the procedure Stas_Ins_Ess for bug 4293239
+------------------------------------------------------------------------------*/
Function Stap_Get_Supp_Fos(
p_course_cd IN VARCHAR2 ,
p_version_number IN NUMBER )
RETURN VARCHAR2 AS
gv_other_detail VARCHAR2(255);
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm,
IGS_PS_GRP cgr,
IGS_PS_GRP_TYPE cgt
WHERE cgm.course_cd = p_course_cd AND
cgm.version_number = p_version_number AND
cgr.course_group_cd = cgm.course_group_cd AND
cgt.course_group_type = cgr.course_group_type AND
cgt.s_course_group_type = cst_combined;
SELECT fos.govt_field_of_study
FROM IGS_PS_FIELD_STUDY cfos,
IGS_PS_FLD_OF_STUDY fos
WHERE cfos.course_cd = cp_course_cd AND
cfos.version_number = cp_version_number AND
fos.field_of_study = cfos.field_of_study
ORDER BY
cfos.major_field_ind ASC,
cfos.percentage DESC,
cfos.field_of_study ASC;
SELECT trunc(total_exmptn_perc_grntd)
FROM IGS_AV_ADV_STANDING ast
WHERE ast.person_id = p_person_id and
ast.course_cd = p_course_cd and
ast.version_number = p_version_number;
SELECT unit_attempt_status
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
uoo_id = p_uoo_id;
v_hecs_prexmt_exie_update IGS_ST_GVT_STDNT_LBL.hecs_prexmt_exie%TYPE;
SELECT sgs.*,uoo.uoo_id
FROM IGS_ST_GVT_STDNTLOAD sgs,
igs_ps_unit_ofr_opt uoo
WHERE sgs.submission_yr = p_submission_yr AND
sgs.submission_number = (p_submission_number - 1) AND
sgs.unit_cd = uoo.unit_cd AND
sgs.uv_version_number = uoo.version_number AND
sgs.sua_cal_type = uoo.cal_type AND
sgs.sua_ci_sequence_number = uoo.ci_sequence_number AND
sgs.sua_location_cd = uoo.location_cd AND
sgs.unit_class = uoo.unit_class
ORDER BY Person_id;
SELECT gsc.submission_yr
FROM IGS_ST_GVT_SPSHT_CTL gsc
WHERE gsc.submission_yr = p_submission_yr AND
gsc.submission_number = p_submission_number;
SELECT rowid, gsc.*
FROM IGS_ST_GVT_SPSHT_CTL gsc
WHERE gsc.submission_yr = p_submission_yr AND
gsc.submission_number = p_submission_number
FOR UPDATE OF gsc.ess_snapshot_dt_time NOWAIT;
SELECT gsc.ess_snapshot_dt_time
FROM IGS_ST_GVT_SPSHT_CTL gsc
WHERE gsc.submission_yr = p_submission_yr AND
gsc.submission_number = 2;
SELECT essc.snapshot_dt_time
FROM IGS_EN_ST_SPSHT_CTL essc
ORDER BY essc.snapshot_dt_time DESC;
SELECT rowid, essc.*
FROM IGS_EN_ST_SPSHT_CTL essc
WHERE snapshot_dt_time = cp_ess_snapshot_dt_time
FOR UPDATE OF essc.delete_snapshot_ind NOWAIT;
SELECT aty.attendance_type,
aty.govt_attendance_type
FROM IGS_EN_ATD_TYPE aty
WHERE aty.govt_attendance_type = 2 AND
aty.upper_enr_load_range > 0;
SELECT atm.attendance_mode,
atm.govt_attendance_mode
FROM IGS_EN_ATD_MODE atm
WHERE atm.govt_attendance_mode = '1'
ORDER BY atm.attendance_mode ASC;
SELECT atm.attendance_mode,
atm.govt_attendance_mode
FROM IGS_EN_ATD_MODE atm
WHERE atm.govt_attendance_mode = '2'
ORDER BY atm.attendance_mode ASC;
SELECT atm.attendance_mode,
atm.govt_attendance_mode
FROM IGS_EN_ATD_MODE atm
WHERE atm.govt_attendance_mode = '3'
ORDER BY atm.attendance_mode ASC;
SELECT ess.ci_cal_type,
ess.ci_sequence_number,
ess.person_id,
ess.course_cd,
ess.crv_version_number,
ess.unit_cd,
ess.uv_version_number,
ess.sua_cal_type,
ess.sua_ci_sequence_number,
ess.tr_org_unit_cd,
ess.tr_ou_start_dt,
ess.discipline_group_cd,
ess.govt_discipline_group_cd,
ess.unit_class,
ess.enrolled_dt,
ess.discontinued_dt,
ess.eftsu,
ess.commencing_student_ind,
ct.award_course_ind,
uoo.uoo_id,
ess.sua_location_cd
FROM IGS_EN_ST_SNAPSHOT ess,
IGS_PS_TYPE ct,
igs_ps_unit_ofr_opt uoo
WHERE ess.snapshot_dt_time = cp_dt_time AND
ess.govt_reportable_ind <> 'X' AND
ct.course_type = ess.course_type AND
ess.unit_cd = uoo.unit_cd AND
ess.uv_version_number = uoo.version_number AND
ess.sua_cal_type = uoo.cal_type AND
ess.sua_ci_sequence_number = uoo.ci_sequence_number AND
ess.sua_location_cd = uoo.location_cd AND
ess.unit_class = uoo.unit_class
ORDER BY ess.person_id ASC,
ess.course_cd ASC,
ess.ci_cal_type ASC,
ess.ci_sequence_number ASC,
ess.sua_cal_type ASC,
ess.sua_ci_sequence_number ASC,
ess.unit_cd ASC;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = cp_teach_cal_type AND
daiv.ci_sequence_number = cp_teach_ci_seq_num AND
daiv.dt_alias = sgcc.census_dt_alias
ORDER BY daiv.alias_val ASC;
SELECT ci.start_dt,
ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = cp_teach_cal_type AND
ci.sequence_number = cp_teach_ci_sequence_number;
SELECT api.pe_person_id
FROM IGS_PE_ALT_PERS_ID api,
IGS_PE_PERSON_ID_TYP pit
WHERE api.api_person_id = TO_CHAR(cp_person_id) AND
pit.person_id_type = api.person_id_type AND
pit.s_person_id_type = 'OBSOLETE' AND
api.start_dt IS NOT NULL AND
api.start_dt <= cp_eff_dt AND
(api.end_dt IS NULL OR
api.end_dt >= cp_eff_dt) AND
(api.end_dt IS NULL OR
api.start_dt <> api.end_dt)
ORDER BY api.end_dt ASC;
SELECT DISTINCT
gse.person_id,
gse.course_cd
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number;
SELECT gslo.unit_cd,
gslo.sua_cal_type,
gslo.sua_ci_sequence_number,
gslo.govt_semester,
gslo.tr_org_unit_cd,
gslo.tr_ou_start_dt,
gslo.discipline_group_cd,
gslo.govt_discipline_group_cd,
gslo.sua_location_cd,
gslo.unit_class
FROM IGS_ST_GVT_STDNTLOAD gslo
WHERE gslo.submission_yr = p_submission_yr AND
gslo.submission_number = p_submission_number AND
gslo.person_id = cp_person_id AND
gslo.course_cd = cp_course_cd AND
gslo.eftsu <> 0;
SELECT um.s_unit_mode
FROM IGS_EN_SU_ATTEMPT sua,
IGS_AS_UNIT_CLASS ucl,
IGS_AS_UNIT_MODE um
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.unit_cd = cp_unit_cd AND
sua.cal_type = cp_cal_type AND
sua.ci_sequence_number = cp_ci_seq_num AND
sua.location_cd = cp_location_cd AND
sua.unit_class = cp_unit_class AND
ucl.unit_class = sua.unit_class AND
ucl.closed_ind = 'N' AND
um.unit_mode = ucl.unit_mode;
SELECT att.attendance_type,
att.govt_attendance_type,
att.lower_enr_load_range
FROM IGS_EN_ATD_TYPE att
WHERE att.govt_attendance_type = 1 AND
att.lower_enr_load_range > 0;
SELECT DISTINCT
gse.person_id
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number AND
cp_lower_enr_load_range <=
(SELECT SUM(gsl.eftsu)
FROM IGS_ST_GVT_STDNTLOAD gsl
WHERE gsl.submission_yr = p_submission_yr AND
gsl.submission_number = p_submission_number AND
gsl.person_id = gse.person_id);
SELECT gse.person_id,
gse.course_cd
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number
ORDER BY
gse.person_id;
SELECT gse.person_id,
gse.course_cd,
SUM(gslo.eftsu)
FROM IGS_ST_GOVT_STDNT_EN gse,
IGS_ST_GVT_STDNTLOAD gslo,
IGS_EN_STDNT_PS_ATT sca
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number AND
gse.person_id = cp_person_id AND
1 < (SELECT COUNT(DISTINCT gse2.course_cd)
FROM IGS_ST_GOVT_STDNT_EN gse2
WHERE gse2.submission_yr = p_submission_yr AND
gse2.submission_number = p_submission_number AND
gse2.person_id = cp_person_id) AND
sca.person_id = gse.person_id AND
sca.course_cd = gse.course_cd AND
gslo.submission_yr = gse.submission_yr AND
gslo.submission_number = gse.submission_number AND
gslo.person_id = gse.person_id AND
gslo.course_cd = gse.course_cd
GROUP BY gse.person_id,
gse.course_cd,
sca.commencement_dt
ORDER BY gse.person_id ASC,
SUM(gslo.eftsu) DESC,
sca.commencement_dt ASC;
SELECT rowid, gsli.*
FROM IGS_ST_GVT_STDNT_LBL gsli
WHERE submission_yr = p_submission_yr AND
submission_number = p_submission_number
FOR UPDATE OF gsli.last_updated_by NOWAIT;
SELECT rowid, gsli.*
FROM IGS_ST_GVT_STDNT_LBL gsli
WHERE gsli.submission_yr = p_submission_yr AND
gsli.submission_number = p_submission_number AND
gsli.person_id = cp_person_id AND
gsli.course_cd = cp_course_cd AND
gsli.govt_semester = cp_govt_semester
FOR UPDATE OF gsli.last_updated_by NOWAIT;
SELECT rowid,gslo.*
FROM IGS_ST_GVT_STDNTLOAD gslo
WHERE submission_yr = p_submission_yr AND
submission_number = p_submission_number
FOR UPDATE OF gslo.last_updated_by NOWAIT;
SELECT uv.industrial_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = cp_unit_cd AND
uv.version_number = cp_version_number;
CURSOR c_update_total_eftsu IS
SELECT gslo.person_id,
gslo.course_cd,
gslo.govt_semester,
NVL(SUM(gslo.eftsu), 0) v_upd_total_eftsu
FROM IGS_ST_GVT_STDNTLOAD gslo
WHERE gslo.submission_yr = p_submission_yr AND
gslo.submission_number = p_submission_number
GROUP BY gslo.person_id,
gslo.course_cd,
gslo.govt_semester;
CURSOR c_update_indus_eftsu IS
SELECT gslo.person_id,
gslo.course_cd,
gslo.govt_semester,
NVL(SUM(gslo.eftsu), 0) v_upd_indus_eftsu
FROM IGS_ST_GVT_STDNTLOAD gslo
WHERE gslo.submission_yr = p_submission_yr AND
gslo.submission_number = p_submission_number AND
gslo.industrial_ind = 'Y'
GROUP BY gslo.person_id,
gslo.course_cd,
gslo.govt_semester;
SELECT rowid,gse.*
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE submission_yr = p_submission_yr AND
submission_number = p_submission_number
FOR UPDATE OF gse.last_updated_by NOWAIT;
SELECT rowid, gse.*
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE submission_yr = p_submission_yr AND
submission_number = p_submission_number AND
person_id = cp_person_id
FOR UPDATE OF gse.last_updated_by NOWAIT;
SELECT rowid, gse.*
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number AND
gse.person_id = cp_person_id AND
gse.course_cd = cp_course_cd
FOR UPDATE OF gse.last_updated_by NOWAIT;
SELECT UNIQUE
gsli.person_id,
gsli.course_cd,
gsli.version_number,
gsli.commencement_dt
FROM IGS_ST_GVT_STDNT_LBL gsli
WHERE gsli.submission_yr = p_submission_yr AND
gsli.submission_number = p_submission_number;
SELECT rowid,gsli.*
FROM IGS_ST_GVT_STDNT_LBL gsli
WHERE gsli.submission_yr = p_submission_yr AND
gsli.submission_number = p_submission_number AND
gsli.person_id = cp_person_id AND
gsli.course_cd = cp_course_cd
FOR UPDATE OF gsli.commencement_dt NOWAIT;
SELECT UNIQUE
gse.person_id,
gse.course_cd,
gse.version_number,
gse.commencement_dt
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number;
SELECT rowid,gse.*
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = p_submission_number AND
gse.person_id = cp_person_id AND
gse.course_cd = cp_course_cd
FOR UPDATE OF gse.commencement_dt NOWAIT;
-- insert a record into IGS_ST_GVT_STDNTLOAD
-- to insert row using the insertrow of TBH package
IGS_ST_GVT_STDNTLOAD_PKG.INSERT_ROW(
X_ROWID => v_rowid,
X_SUBMISSION_YR => p_sub_yr,
X_SUBMISSION_NUMBER => p_sub_number,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_CRV_VERSION_NUMBER => p_crv_version_number,
X_GOVT_SEMESTER => p_govt_semester,
X_UNIT_CD => p_unit_cd,
X_UV_VERSION_NUMBER => p_uv_version_number,
X_SUA_CAL_TYPE => p_sua_cal_type,
X_SUA_CI_SEQUENCE_NUMBER => p_sua_ci_sequence_number,
X_TR_ORG_UNIT_CD => p_tr_org_unit_cd,
X_TR_OU_START_DT => p_tr_ou_start_dt,
X_DISCIPLINE_GROUP_CD => p_discipline_group_cd,
X_GOVT_DISCIPLINE_GROUP_CD => p_govt_discipline_group_cd,
X_INDUSTRIAL_IND => p_industrial_ind,
X_EFTSU => p_eftsu,
X_UNIT_COMPLETION_STATUS => p_unit_completion_status,
X_MODE => 'R',
X_SUA_LOCATION_CD => p_sua_location_cd,
X_UNIT_CLASS => p_unit_class);
SELECT pe.birth_dt,
pe.sex
FROM IGS_PE_PERSON pe
WHERE pe.person_id = p_current_person_id;
SELECT ps.citizenship_cd,
ps.perm_resident_cd
FROM IGS_PE_STATISTICS ps
WHERE ps.person_id = p_current_person_id AND
ps.start_dt <= p_effective_dt AND
(ps.end_dt IS NULL OR
ps.end_dt >= p_effective_dt)
ORDER BY ps.end_dt ASC;
SELECT ccd.govt_citizenship_cd
FROM IGS_ST_CITIZENSHP_CD ccd
WHERE ccd.citizenship_cd = p_citizenship_cd;
SELECT prcd.govt_perm_resident_cd
FROM IGS_PE_PERM_RES_CD prcd
WHERE prcd.perm_resident_cd = cp_perm_res_cd;
-- insert liability record
BEGIN
-- to insert row using the insert row of the respective TBH package
IGS_ST_GVT_STDNT_LBL_PKG.INSERT_ROW(
X_ROWID => v_rowid,
X_SUBMISSION_YR => p_sub_yr,
X_SUBMISSION_NUMBER => p_sub_number,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_VERSION_NUMBER => p_version_number,
X_GOVT_SEMESTER => p_govt_semester,
X_HECS_PAYMENT_OPTION => p_hecs_payment_option,
X_GOVT_HECS_PAYMENT_OPTION => p_govt_hpo,
X_TOTAL_EFTSU => 0,
X_INDUSTRIAL_EFTSU => 0,
X_HECS_PREXMT_EXIE => p_hecs_fee,
X_HECS_AMOUNT_PAID => p_hecs_amount_pd,
X_TUITION_FEE => p_tuition_fee,
X_DIFFERENTIAL_HECS_IND => p_differential_hecs_ind,
X_BIRTH_DT => p_birth_dt,
X_SEX => p_sex,
X_CITIZENSHIP_CD => p_citizenship_cd,
X_GOVT_CITIZENSHIP_CD => p_govt_citizenship_cd,
X_PERM_RESIDENT_CD => p_perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => p_govt_perm_resident_cd,
X_COMMENCEMENT_DT => p_commencement_dt,
X_MODE => 'R' );
-- select the first record only
OPEN c_essc;
-- is marked for delete. Update the
-- delete snapshot indicator
BEGIN
FOR v_essc_upd_rec IN c_essc_upd(v_ess_snapshot_dt_time) LOOP
v_essc_upd_rec.delete_snapshot_ind := 'N';
IGS_EN_ST_SPSHT_CTL_PKG.UPDATE_ROW(
X_ROWID => v_essc_upd_rec.rowid,
X_SNAPSHOT_DT_TIME => v_essc_upd_rec.snapshot_dt_time,
X_DELETE_SNAPSHOT_IND => v_essc_upd_rec.delete_snapshot_ind,
X_COMMENTS => v_essc_upd_rec.comments,
X_MODE => 'R');
-- update the enrolment statistics snapshot date time
-- for the government snapshot
BEGIN
FOR v_gsc_upd_rec IN c_gsc_upd LOOP
v_gsc_upd_rec.ess_snapshot_dt_time := v_ess_snapshot_dt_time;
IGS_ST_GVT_SPSHT_CTL_PKG.UPDATE_ROW(
X_ROWID => v_gsc_upd_rec.rowid,
X_SUBMISSION_YR => v_gsc_upd_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gsc_upd_rec.submission_number,
X_ESS_SNAPSHOT_DT_TIME => v_gsc_upd_rec.ess_snapshot_dt_time,
X_COMPLETION_DT => v_gsc_upd_rec.completion_dt,
X_MODE => 'R');
IGS_ST_GVT_SPSHT_CTL_PKG.UPDATE_ROW(
X_ROWID => v_gsc_upd_rec.rowid,
X_SUBMISSION_YR => v_gsc_upd_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gsc_upd_rec.submission_number,
X_ESS_SNAPSHOT_DT_TIME => v_gsc_upd_rec.ess_snapshot_dt_time,
X_COMPLETION_DT => v_gsc_upd_rec.completion_dt,
X_MODE => 'R');
IGS_ST_GVT_STDNT_LBL_PKG.DELETE_ROW(
X_ROWID => v_gsli_upd_rec.rowid);
IGS_ST_GVT_STDNTLOAD_PKG.DELETE_ROW(
X_ROWID => v_gslo_upd_rec.rowid);
IGS_ST_GOVT_STDNT_EN_PKG.DELETE_ROW(
X_ROWID => v_gse_upd_rec.rowid);
-- select (there are three different selects
-- as different details are retrieved depending
-- on the govt_attendance_mode specified)
-- select the first record only
-- internal attendance mode
OPEN c_get_att_mode_1;
-- select the first record only
IF v_teach_cal_type IS NULL OR
v_teach_cal_type <> v_enr_snpsht_rec.sua_cal_type OR
v_teach_ci_sequence_number IS NULL OR
v_teach_ci_sequence_number <> v_enr_snpsht_rec.sua_ci_sequence_number THEN
OPEN c_alias_val(
v_enr_snpsht_rec.sua_cal_type,
v_enr_snpsht_rec.sua_ci_sequence_number);
IGS_ST_GOVT_STDNT_EN_PKG.INSERT_ROW(
X_ROWID => v_rowid,
X_SUBMISSION_YR => p_submission_yr,
X_SUBMISSION_NUMBER => p_submission_number,
X_PERSON_ID => v_enr_snpsht_rec.person_id,
X_COURSE_CD => v_enr_snpsht_rec.course_cd,
X_VERSION_NUMBER => v_enr_snpsht_rec.crv_version_number,
X_BIRTH_DT => v_birth_dt,
X_SEX => v_sex,
X_ABORIG_TORRES_CD => v_aborig_torres_cd,
X_GOVT_ABORIG_TORRES_CD => v_govt_aborig_torres_cd,
X_CITIZENSHIP_CD => v_citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_govt_perm_resident_cd,
X_HOME_LOCATION => v_home_location_cd,
X_GOVT_HOME_LOCATION => v_govt_home_location_cd,
X_TERM_LOCATION => v_term_location_cd,
X_GOVT_TERM_LOCATION => v_govt_term_location_cd,
X_BIRTH_COUNTRY_CD => v_birth_country_cd,
X_GOVT_BIRTH_COUNTRY_CD => v_govt_birth_country_cd,
X_YR_ARRIVAL => v_yr_arrival,
X_HOME_LANGUAGE_CD => v_home_language_cd,
X_GOVT_HOME_LANGUAGE_CD => v_govt_home_language_cd,
X_PRIOR_UG_INST => v_prior_ug_inst,
X_GOVT_PRIOR_UG_INST => v_govt_prior_ug_inst,
X_PRIOR_OTHER_QUAL => v_prior_other_qual,
X_PRIOR_POST_GRAD => v_prior_post_grad,
X_PRIOR_DEGREE => v_prior_degree,
X_PRIOR_SUBDEG_NOTAFE => v_prior_subdeg_notafe,
X_PRIOR_SUBDEG_TAFE => v_prior_subdeg_tafe,
X_PRIOR_SECED_TAFE => v_prior_seced_tafe,
X_PRIOR_SECED_SCHOOL => v_prior_seced_school,
X_PRIOR_TAFE_AWARD => v_prior_tafe_award,
X_PRIOR_STUDIES_EXEMPTION => v_prior_studies_exemption,
X_EXEMPTION_INSTITUTION_CD => v_exempt_institution_cd,
X_GOVT_EXEMPT_INSTITU_CD => v_govt_exempt_institution_cd,
X_ATTENDANCE_MODE => v_attendance_mode_3,
X_GOVT_ATTENDANCE_MODE => v_govt_attendance_mode_3,
X_ATTENDANCE_TYPE => v_attendance_type,
X_GOVT_ATTENDANCE_TYPE => v_govt_attendance_type,
X_COMMENCEMENT_DT => v_commencement_dt,
X_MAJOR_COURSE => 1,
X_TERTIARY_ENTRANCE_SCORE => v_tertiary_entrance_score,
X_BASIS_FOR_ADMISSION_TYPE => v_basis_for_admission_type,
X_GOVT_BASIS_FOR_ADM_TYPE => v_govt_basis_for_adm_type,
X_GOVT_DISABILITY => NVL(v_govt_disability, ' '),
X_MODE => 'R');
-- select the alternate person_id
-- select the first record only, as this
-- will be the end dated record if one exists
OPEN c_get_api(
v_gslot_rec.person_id,
v_unit_effective_dt);
-- Update the Attendance Mode value for inserted records.
FOR v_gse_att_mode IN c_gse_att_mode LOOP
-- get IGS_ST_GVT_STDNTLOAD details
FOR v_gslo IN c_gslo(
v_gse_att_mode.person_id,
v_gse_att_mode.course_cd) LOOP
-- determine the system IGS_PS_UNIT mode of the IGS_PS_UNIT
OPEN c_sua_ucl_um(
v_gse_att_mode.person_id,
v_gse_att_mode.course_cd,
v_gslo.unit_cd,
v_gslo.sua_cal_type,
v_gslo.sua_ci_sequence_number,
v_gslo.sua_location_cd,
v_gslo.unit_class);
-- update IGS_ST_GOVT_STDNT_EN table
BEGIN
FOR v_gse_upd3_rec IN c_gse_upd3(
v_gse_att_mode.person_id,
v_gse_att_mode.course_cd) LOOP
v_gse_upd3_rec.attendance_mode := v_attendance_mode;
IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
X_ROWID => v_gse_upd3_rec.rowid,
X_SUBMISSION_YR => v_gse_upd3_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gse_upd3_rec.submission_number,
X_PERSON_ID => v_gse_upd3_rec.person_id,
X_COURSE_CD => v_gse_upd3_rec.course_cd,
X_VERSION_NUMBER => v_gse_upd3_rec.version_number,
X_BIRTH_DT => v_gse_upd3_rec.birth_dt,
X_SEX => v_gse_upd3_rec.sex,
X_ABORIG_TORRES_CD => v_gse_upd3_rec.aborig_torres_cd,
X_GOVT_ABORIG_TORRES_CD => v_gse_upd3_rec.govt_aborig_torres_cd,
X_CITIZENSHIP_CD => v_gse_upd3_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gse_upd3_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gse_upd3_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gse_upd3_rec.govt_perm_resident_cd,
X_HOME_LOCATION => v_gse_upd3_rec.home_location,
X_GOVT_HOME_LOCATION => v_gse_upd3_rec.govt_home_location,
X_TERM_LOCATION => v_gse_upd3_rec.term_location,
X_GOVT_TERM_LOCATION => v_gse_upd3_rec.govt_term_location,
X_BIRTH_COUNTRY_CD => v_gse_upd3_rec.birth_country_cd,
X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd3_rec.govt_birth_country_cd,
X_YR_ARRIVAL => v_gse_upd3_rec.yr_arrival,
X_HOME_LANGUAGE_CD => v_gse_upd3_rec.home_language_cd,
X_GOVT_HOME_LANGUAGE_CD => v_gse_upd3_rec.govt_home_language_cd,
X_PRIOR_UG_INST => v_gse_upd3_rec.prior_ug_inst,
X_GOVT_PRIOR_UG_INST => v_gse_upd3_rec.govt_prior_ug_inst,
X_PRIOR_OTHER_QUAL => v_gse_upd3_rec.prior_other_qual,
X_PRIOR_POST_GRAD => v_gse_upd3_rec.prior_post_grad,
X_PRIOR_DEGREE => v_gse_upd3_rec.prior_degree,
X_PRIOR_SUBDEG_NOTAFE => v_gse_upd3_rec.prior_subdeg_notafe,
X_PRIOR_SUBDEG_TAFE => v_gse_upd3_rec.prior_subdeg_tafe,
X_PRIOR_SECED_TAFE => v_gse_upd3_rec.prior_seced_tafe,
X_PRIOR_SECED_SCHOOL => v_gse_upd3_rec.prior_seced_school,
X_PRIOR_TAFE_AWARD => v_gse_upd3_rec.prior_tafe_award,
X_PRIOR_STUDIES_EXEMPTION => v_gse_upd3_rec.prior_studies_exemption,
X_EXEMPTION_INSTITUTION_CD => v_gse_upd3_rec.exemption_institution_cd,
X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd3_rec.govt_exemption_institution_cd,
X_ATTENDANCE_MODE => v_gse_upd3_rec.attendance_mode,
X_GOVT_ATTENDANCE_MODE => v_gse_upd3_rec.govt_attendance_mode,
X_ATTENDANCE_TYPE => v_gse_upd3_rec.attendance_type,
X_GOVT_ATTENDANCE_TYPE => v_gse_upd3_rec.govt_attendance_type,
X_COMMENCEMENT_DT => v_gse_upd3_rec.commencement_dt,
X_MAJOR_COURSE => v_gse_upd3_rec.major_course,
X_TERTIARY_ENTRANCE_SCORE => v_gse_upd3_rec.tertiary_entrance_score,
X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd3_rec.basis_for_admission_type,
X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd3_rec.govt_basis_for_admission_type,
X_GOVT_DISABILITY => v_gse_upd3_rec.govt_disability,
X_MODE => 'R');
-- select records from IGS_ST_GOVT_STDNT_EN which
-- the SUM(eftsu) is greater than the lower_enr_load_range
FOR v_gse IN c_gse(v_lower_enr_load_range) LOOP
-- update the IGS_ST_GOVT_STDNT_EN record
BEGIN
FOR v_gse_upd2_rec IN c_gse_upd2(v_gse.person_id) LOOP
v_gse_upd2_rec.attendance_type := v_attendance_type;
IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
X_ROWID => v_gse_upd2_rec.rowid,
X_SUBMISSION_YR => v_gse_upd2_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gse_upd2_rec.submission_number,
X_PERSON_ID => v_gse_upd2_rec.person_id,
X_COURSE_CD => v_gse_upd2_rec.course_cd,
X_VERSION_NUMBER => v_gse_upd2_rec.version_number,
X_BIRTH_DT => v_gse_upd2_rec.birth_dt,
X_SEX => v_gse_upd2_rec.sex,
X_ABORIG_TORRES_CD => v_gse_upd2_rec.aborig_torres_cd,
X_GOVT_ABORIG_TORRES_CD => v_gse_upd2_rec.govt_aborig_torres_cd,
X_CITIZENSHIP_CD => v_gse_upd2_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gse_upd2_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gse_upd2_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gse_upd2_rec.govt_perm_resident_cd,
X_HOME_LOCATION => v_gse_upd2_rec.home_location,
X_GOVT_HOME_LOCATION => v_gse_upd2_rec.govt_home_location,
X_TERM_LOCATION => v_gse_upd2_rec.term_location,
X_GOVT_TERM_LOCATION => v_gse_upd2_rec.govt_term_location,
X_BIRTH_COUNTRY_CD => v_gse_upd2_rec.birth_country_cd,
X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd2_rec.govt_birth_country_cd,
X_YR_ARRIVAL => v_gse_upd2_rec.yr_arrival,
X_HOME_LANGUAGE_CD => v_gse_upd2_rec.home_language_cd,
X_GOVT_HOME_LANGUAGE_CD => v_gse_upd2_rec.govt_home_language_cd,
X_PRIOR_UG_INST => v_gse_upd2_rec.prior_ug_inst,
X_GOVT_PRIOR_UG_INST => v_gse_upd2_rec.govt_prior_ug_inst,
X_PRIOR_OTHER_QUAL => v_gse_upd2_rec.prior_other_qual,
X_PRIOR_POST_GRAD => v_gse_upd2_rec.prior_post_grad,
X_PRIOR_DEGREE => v_gse_upd2_rec.prior_degree,
X_PRIOR_SUBDEG_NOTAFE => v_gse_upd2_rec.prior_subdeg_notafe,
X_PRIOR_SUBDEG_TAFE => v_gse_upd2_rec.prior_subdeg_tafe,
X_PRIOR_SECED_TAFE => v_gse_upd2_rec.prior_seced_tafe,
X_PRIOR_SECED_SCHOOL => v_gse_upd2_rec.prior_seced_school,
X_PRIOR_TAFE_AWARD => v_gse_upd2_rec.prior_tafe_award,
X_PRIOR_STUDIES_EXEMPTION => v_gse_upd2_rec.prior_studies_exemption,
X_EXEMPTION_INSTITUTION_CD => v_gse_upd2_rec.exemption_institution_cd,
X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd2_rec.govt_exemption_institution_cd,
X_ATTENDANCE_MODE => v_gse_upd2_rec.attendance_mode,
X_GOVT_ATTENDANCE_MODE => v_gse_upd2_rec.govt_attendance_mode,
X_ATTENDANCE_TYPE => v_gse_upd2_rec.attendance_type,
X_GOVT_ATTENDANCE_TYPE => v_gse_upd2_rec.govt_attendance_type,
X_COMMENCEMENT_DT => v_gse_upd2_rec.commencement_dt,
X_MAJOR_COURSE => v_gse_upd2_rec.major_course,
X_TERTIARY_ENTRANCE_SCORE => v_gse_upd2_rec.tertiary_entrance_score,
X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd2_rec.basis_for_admission_type,
X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd2_rec.govt_basis_for_admission_type,
X_GOVT_DISABILITY => v_gse_upd2_rec.govt_disability,
X_MODE => 'R');
-- update the IGS_ST_GOVT_STDNT_EN record
BEGIN
FOR v_gse_upd3_rec IN c_gse_upd3(
v_person_id,
v_gse_sca.course_cd) LOOP
v_gse_upd3_rec.major_course := v_major_course;
IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
X_ROWID => v_gse_upd3_rec.rowid,
X_SUBMISSION_YR => v_gse_upd3_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gse_upd3_rec.submission_number,
X_PERSON_ID => v_gse_upd3_rec.person_id,
X_COURSE_CD => v_gse_upd3_rec.course_cd,
X_VERSION_NUMBER => v_gse_upd3_rec.version_number,
X_BIRTH_DT => v_gse_upd3_rec.birth_dt,
X_SEX => v_gse_upd3_rec.sex,
X_ABORIG_TORRES_CD => v_gse_upd3_rec.aborig_torres_cd,
X_GOVT_ABORIG_TORRES_CD => v_gse_upd3_rec.govt_aborig_torres_cd,
X_CITIZENSHIP_CD => v_gse_upd3_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gse_upd3_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gse_upd3_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gse_upd3_rec.govt_perm_resident_cd,
X_HOME_LOCATION => v_gse_upd3_rec.home_location,
X_GOVT_HOME_LOCATION => v_gse_upd3_rec.govt_home_location,
X_TERM_LOCATION => v_gse_upd3_rec.term_location,
X_GOVT_TERM_LOCATION => v_gse_upd3_rec.govt_term_location,
X_BIRTH_COUNTRY_CD => v_gse_upd3_rec.birth_country_cd,
X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd3_rec.govt_birth_country_cd,
X_YR_ARRIVAL => v_gse_upd3_rec.yr_arrival,
X_HOME_LANGUAGE_CD => v_gse_upd3_rec.home_language_cd,
X_GOVT_HOME_LANGUAGE_CD => v_gse_upd3_rec.govt_home_language_cd,
X_PRIOR_UG_INST => v_gse_upd3_rec.prior_ug_inst,
X_GOVT_PRIOR_UG_INST => v_gse_upd3_rec.govt_prior_ug_inst,
X_PRIOR_OTHER_QUAL => v_gse_upd3_rec.prior_other_qual,
X_PRIOR_POST_GRAD => v_gse_upd3_rec.prior_post_grad,
X_PRIOR_DEGREE => v_gse_upd3_rec.prior_degree,
X_PRIOR_SUBDEG_NOTAFE => v_gse_upd3_rec.prior_subdeg_notafe,
X_PRIOR_SUBDEG_TAFE => v_gse_upd3_rec.prior_subdeg_tafe,
X_PRIOR_SECED_TAFE => v_gse_upd3_rec.prior_seced_tafe,
X_PRIOR_SECED_SCHOOL => v_gse_upd3_rec.prior_seced_school,
X_PRIOR_TAFE_AWARD => v_gse_upd3_rec.prior_tafe_award,
X_PRIOR_STUDIES_EXEMPTION => v_gse_upd3_rec.prior_studies_exemption,
X_EXEMPTION_INSTITUTION_CD => v_gse_upd3_rec.exemption_institution_cd,
X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd3_rec.govt_exemption_institution_cd,
X_ATTENDANCE_MODE => v_gse_upd3_rec.attendance_mode,
X_GOVT_ATTENDANCE_MODE => v_gse_upd3_rec.govt_attendance_mode,
X_ATTENDANCE_TYPE => v_gse_upd3_rec.attendance_type,
X_GOVT_ATTENDANCE_TYPE => v_gse_upd3_rec.govt_attendance_type,
X_COMMENCEMENT_DT => v_gse_upd3_rec.commencement_dt,
X_MAJOR_COURSE => v_gse_upd3_rec.major_course,
X_TERTIARY_ENTRANCE_SCORE => v_gse_upd3_rec.tertiary_entrance_score,
X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd3_rec.basis_for_admission_type,
X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd3_rec.govt_basis_for_admission_type,
X_GOVT_DISABILITY => v_gse_upd3_rec.govt_disability,
X_MODE => 'R');
-- student liability commencement date then we want to update the
-- government student liability and government student enrolment
-- commencement dates.
IF v_derived_commencement_dt <> v_gsli_rec.commencement_dt THEN
FOR v_gsli_upd_comm_rec IN c_gsli_upd_commencement(
v_gsli_rec.person_id,
v_gsli_rec.course_cd) LOOP
BEGIN
v_gsli_upd_comm_rec.commencement_dt := v_derived_commencement_dt;
IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
X_ROWID => v_gsli_upd_comm_rec.rowid,
X_SUBMISSION_YR => v_gsli_upd_comm_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gsli_upd_comm_rec.submission_number,
X_PERSON_ID => v_gsli_upd_comm_rec.person_id,
X_COURSE_CD => v_gsli_upd_comm_rec.course_cd,
X_GOVT_SEMESTER => v_gsli_upd_comm_rec.govt_semester,
X_VERSION_NUMBER => v_gsli_upd_comm_rec.version_number,
X_HECS_PAYMENT_OPTION => v_gsli_upd_comm_rec.hecs_payment_option,
X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd_comm_rec.govt_hecs_payment_option,
X_TOTAL_EFTSU => v_gsli_upd_comm_rec.total_eftsu,
X_INDUSTRIAL_EFTSU => v_gsli_upd_comm_rec.industrial_eftsu,
X_HECS_PREXMT_EXIE => v_gsli_upd_comm_rec.hecs_prexmt_exie,
X_HECS_AMOUNT_PAID => v_gsli_upd_comm_rec.hecs_amount_paid,
X_TUITION_FEE => v_gsli_upd_comm_rec.tuition_fee,
X_DIFFERENTIAL_HECS_IND => v_gsli_upd_comm_rec.differential_hecs_ind,
X_BIRTH_DT => v_gsli_upd_comm_rec.birth_dt,
X_SEX => v_gsli_upd_comm_rec.sex,
X_CITIZENSHIP_CD => v_gsli_upd_comm_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gsli_upd_comm_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gsli_upd_comm_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gsli_upd_comm_rec.govt_perm_resident_cd,
X_COMMENCEMENT_DT => v_gsli_upd_comm_rec.commencement_dt,
X_MODE => 'R');
-- student liability commencement date then we want to update the
-- government student enrolment commencement dates.
IF v_derived_commencement_dt <> v_gse_enrolment_rec.commencement_dt THEN
FOR v_gse_upd_comm_rec IN c_gse_upd_commencement(
v_gse_enrolment_rec.person_id,
v_gse_enrolment_rec.course_cd) LOOP
BEGIN
v_gse_upd_comm_rec.commencement_dt := v_derived_commencement_dt;
IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
X_ROWID => v_gse_upd_comm_rec.rowid,
X_SUBMISSION_YR => v_gse_upd_comm_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gse_upd_comm_rec.submission_number,
X_PERSON_ID => v_gse_upd_comm_rec.person_id,
X_COURSE_CD => v_gse_upd_comm_rec.course_cd,
X_VERSION_NUMBER => v_gse_upd_comm_rec.version_number,
X_BIRTH_DT => v_gse_upd_comm_rec.birth_dt,
X_SEX => v_gse_upd_comm_rec.sex,
X_ABORIG_TORRES_CD => v_gse_upd_comm_rec.aborig_torres_cd,
X_GOVT_ABORIG_TORRES_CD => v_gse_upd_comm_rec.govt_aborig_torres_cd,
X_CITIZENSHIP_CD => v_gse_upd_comm_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gse_upd_comm_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gse_upd_comm_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gse_upd_comm_rec.govt_perm_resident_cd,
X_HOME_LOCATION => v_gse_upd_comm_rec.home_location,
X_GOVT_HOME_LOCATION => v_gse_upd_comm_rec.govt_home_location,
X_TERM_LOCATION => v_gse_upd_comm_rec.term_location,
X_GOVT_TERM_LOCATION => v_gse_upd_comm_rec.govt_term_location,
X_BIRTH_COUNTRY_CD => v_gse_upd_comm_rec.birth_country_cd,
X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd_comm_rec.govt_birth_country_cd,
X_YR_ARRIVAL => v_gse_upd_comm_rec.yr_arrival,
X_HOME_LANGUAGE_CD => v_gse_upd_comm_rec.home_language_cd,
X_GOVT_HOME_LANGUAGE_CD => v_gse_upd_comm_rec.govt_home_language_cd,
X_PRIOR_UG_INST => v_gse_upd_comm_rec.prior_ug_inst,
X_GOVT_PRIOR_UG_INST => v_gse_upd_comm_rec.govt_prior_ug_inst,
X_PRIOR_OTHER_QUAL => v_gse_upd_comm_rec.prior_other_qual,
X_PRIOR_POST_GRAD => v_gse_upd_comm_rec.prior_post_grad,
X_PRIOR_DEGREE => v_gse_upd_comm_rec.prior_degree,
X_PRIOR_SUBDEG_NOTAFE => v_gse_upd_comm_rec.prior_subdeg_notafe,
X_PRIOR_SUBDEG_TAFE => v_gse_upd_comm_rec.prior_subdeg_tafe,
X_PRIOR_SECED_TAFE => v_gse_upd_comm_rec.prior_seced_tafe,
X_PRIOR_SECED_SCHOOL => v_gse_upd_comm_rec.prior_seced_school,
X_PRIOR_TAFE_AWARD => v_gse_upd_comm_rec.prior_tafe_award,
X_PRIOR_STUDIES_EXEMPTION => v_gse_upd_comm_rec.prior_studies_exemption,
X_EXEMPTION_INSTITUTION_CD => v_gse_upd_comm_rec.exemption_institution_cd,
X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd_comm_rec.govt_exemption_institution_cd,
X_ATTENDANCE_MODE => v_gse_upd_comm_rec.attendance_mode,
X_GOVT_ATTENDANCE_MODE => v_gse_upd_comm_rec.govt_attendance_mode,
X_ATTENDANCE_TYPE => v_gse_upd_comm_rec.attendance_type,
X_GOVT_ATTENDANCE_TYPE => v_gse_upd_comm_rec.govt_attendance_type,
X_COMMENCEMENT_DT => v_gse_upd_comm_rec.commencement_dt,
X_MAJOR_COURSE => v_gse_upd_comm_rec.major_course,
X_TERTIARY_ENTRANCE_SCORE => v_gse_upd_comm_rec.tertiary_entrance_score,
X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd_comm_rec.basis_for_admission_type,
X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd_comm_rec.govt_basis_for_admission_type,
X_GOVT_DISABILITY => v_gse_upd_comm_rec.govt_disability,
X_MODE => 'R');
FOR v_update_total_eftsu IN c_update_total_eftsu LOOP
BEGIN
FOR v_gsli_upd2_rec IN c_gsli_upd2(
v_update_total_eftsu.person_id,
v_update_total_eftsu.course_cd,
v_update_total_eftsu.govt_semester) LOOP
v_gsli_upd2_rec.total_eftsu := v_update_total_eftsu.v_upd_total_eftsu;
IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
X_ROWID => v_gsli_upd2_rec.rowid,
X_SUBMISSION_YR => v_gsli_upd2_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gsli_upd2_rec.submission_number,
X_PERSON_ID => v_gsli_upd2_rec.person_id,
X_COURSE_CD => v_gsli_upd2_rec.course_cd,
X_GOVT_SEMESTER => v_gsli_upd2_rec.govt_semester,
X_VERSION_NUMBER => v_gsli_upd2_rec.version_number,
X_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.hecs_payment_option,
X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.govt_hecs_payment_option,
X_TOTAL_EFTSU => v_gsli_upd2_rec.total_eftsu,
X_INDUSTRIAL_EFTSU => v_gsli_upd2_rec.industrial_eftsu,
X_HECS_PREXMT_EXIE => v_gsli_upd2_rec.hecs_prexmt_exie,
X_HECS_AMOUNT_PAID => v_gsli_upd2_rec.hecs_amount_paid,
X_TUITION_FEE => v_gsli_upd2_rec.tuition_fee,
X_DIFFERENTIAL_HECS_IND => v_gsli_upd2_rec.differential_hecs_ind,
X_BIRTH_DT => v_gsli_upd2_rec.birth_dt,
X_SEX => v_gsli_upd2_rec.sex,
X_CITIZENSHIP_CD => v_gsli_upd2_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gsli_upd2_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gsli_upd2_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gsli_upd2_rec.govt_perm_resident_cd,
X_COMMENCEMENT_DT => v_gsli_upd2_rec.commencement_dt,
X_MODE => 'R');
IF v_update_total_eftsu.v_upd_total_eftsu > 1 THEN
IF v_logged_ind = FALSE THEN
v_logged_ind := TRUE;
TO_CHAR(v_update_total_eftsu.person_id) || ',' ||
v_update_total_eftsu.course_cd,
4222,
NULL);
FOR v_update_indus_eftsu IN c_update_indus_eftsu LOOP
BEGIN
FOR v_gsli_upd2_rec IN c_gsli_upd2(
v_update_indus_eftsu.person_id,
v_update_indus_eftsu.course_cd,
v_update_indus_eftsu.govt_semester) LOOP
v_gsli_upd2_rec.industrial_eftsu := v_update_indus_eftsu.v_upd_indus_eftsu;
IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
X_ROWID => v_gsli_upd2_rec.rowid,
X_SUBMISSION_YR => v_gsli_upd2_rec.submission_yr,
X_SUBMISSION_NUMBER => v_gsli_upd2_rec.submission_number,
X_PERSON_ID => v_gsli_upd2_rec.person_id,
X_COURSE_CD => v_gsli_upd2_rec.course_cd,
X_GOVT_SEMESTER => v_gsli_upd2_rec.govt_semester,
X_VERSION_NUMBER => v_gsli_upd2_rec.version_number,
X_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.hecs_payment_option,
X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.govt_hecs_payment_option,
X_TOTAL_EFTSU => v_gsli_upd2_rec.total_eftsu,
X_INDUSTRIAL_EFTSU => v_gsli_upd2_rec.industrial_eftsu,
X_HECS_PREXMT_EXIE => v_gsli_upd2_rec.hecs_prexmt_exie,
X_HECS_AMOUNT_PAID => v_gsli_upd2_rec.hecs_amount_paid,
X_TUITION_FEE => v_gsli_upd2_rec.tuition_fee,
X_DIFFERENTIAL_HECS_IND => v_gsli_upd2_rec.differential_hecs_ind,
X_BIRTH_DT => v_gsli_upd2_rec.birth_dt,
X_SEX => v_gsli_upd2_rec.sex,
X_CITIZENSHIP_CD => v_gsli_upd2_rec.citizenship_cd,
X_GOVT_CITIZENSHIP_CD => v_gsli_upd2_rec.govt_citizenship_cd,
X_PERM_RESIDENT_CD => v_gsli_upd2_rec.perm_resident_cd,
X_GOVT_PERM_RESIDENT_CD => v_gsli_upd2_rec.govt_perm_resident_cd,
X_COMMENCEMENT_DT => v_gsli_upd2_rec.commencement_dt,
X_MODE => 'R');
IF v_update_indus_eftsu.v_upd_indus_eftsu > 1 THEN
IF v_logged_ind = FALSE THEN
v_logged_ind := TRUE;
TO_CHAR(v_update_indus_eftsu.person_id) || ',' ||
v_update_indus_eftsu.course_cd,
4223,
NULL);
IF c_update_total_eftsu%ISOPEN THEN
CLOSE c_update_total_eftsu;
IF c_update_indus_eftsu%ISOPEN THEN
CLOSE c_update_indus_eftsu;
SELECT UNIQUE ess.snapshot_dt_time
FROM IGS_EN_ST_SNAPSHOT ess
ORDER BY ess.snapshot_dt_time DESC;
SELECT gsli.person_id,
gsli.course_cd,
gsli.version_number,
gsli.govt_semester,
gsli.hecs_payment_option,
gsli.differential_hecs_ind,
gsli.hecs_prexmt_exie,
gsli.hecs_amount_paid,
gsli.citizenship_cd,
gsli.perm_resident_cd,
gslc.cal_type,
gslc.ci_sequence_number,
ghpo.s_hecs_payment_type
FROM IGS_ST_GVT_STDNT_LBL gsli,
IGS_ST_GVTSEMLOAD_CA gslc,
IGS_FI_HECS_PAY_OPTN hpo,
IGS_FI_GOV_HEC_PA_OP ghpo
WHERE gsli.submission_yr = p_submission_yr AND
gsli.submission_number = p_submission_number AND
gslc.submission_yr = gsli.submission_yr AND
gslc.submission_number = gsli.submission_number AND
gslc.govt_semester = gsli.govt_semester AND
hpo.hecs_payment_option(+) = gsli.hecs_payment_option AND
ghpo.govt_hecs_payment_option(+) = hpo.hecs_payment_option
ORDER BY gsli.person_id,
gsli.course_cd,
gsli.govt_semester;
SELECT scho.differential_hecs_ind,
scho.hecs_payment_option,
hpo.govt_hecs_payment_option,
ghpo.s_hecs_payment_type
FROM IGS_EN_STDNTPSHECSOP scho,
IGS_FI_HECS_PAY_OPTN hpo,
IGS_FI_GOV_HEC_PA_OP ghpo
WHERE scho.person_id = cp_person_id AND
scho.course_cd = cp_course_cd AND
scho.start_dt <= cp_effective_dt AND
(scho.end_dt IS NULL OR
scho.end_dt >= cp_effective_dt) AND
hpo.hecs_payment_option = scho.hecs_payment_option AND
ghpo.govt_hecs_payment_option = hpo.hecs_payment_option
ORDER BY scho.end_dt;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = cp_sua_cal_type AND
daiv.ci_sequence_number = cp_sua_ci_sequence_number AND
daiv.dt_alias = sgcc.census_dt_alias
ORDER BY daiv.alias_val;
SELECT 'x'
FROM IGS_ST_GVT_SPSHT_CHG gsc
WHERE gsc.submission_yr = p_submission_yr AND
gsc.submission_number = p_submission_number AND
gsc.person_id = cp_person_id AND
gsc.course_cd = cp_course_cd AND
gsc.version_number = cp_crv_version_number AND
gsc.govt_semester = cp_govt_semester AND
gsc.old_hecs_prexmt_exie = cp_old_hecs_prexmt_exie AND
gsc.old_hecs_amount_paid = cp_old_hecs_amount_paid AND
gsc.old_hecs_payment_option = cp_old_hecs_payment_option AND
gsc.old_differential_hecs_ind = cp_old_differential_hecs_ind AND
gsc.hecs_amount_paid = cp_hecs_amount_paid AND
gsc.hecs_prexmt_exie = cp_hecs_prexmt_exie AND
gsc.hecs_payment_option = cp_hecs_payment_option AND
gsc.differential_hecs_ind = cp_differential_hecs_ind;
SELECT scho.last_updated_by,
scho.last_update_date
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = cp_person_id AND
scho.course_cd = cp_course_cd AND
scho.end_dt IS NOT NULL AND
scho.hecs_payment_option = cp_old_hecs_payment_option AND
scho.differential_hecs_ind = cp_old_differential_hecs_ind
ORDER BY scho.end_dt DESC;
SELECT scho.last_updated_by,
scho.last_update_date
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = cp_person_id AND
scho.course_cd = cp_course_cd AND
scho.end_dt IS NOT NULL -- AND
-- scho.end_dt > cp_effective_dt
ORDER BY scho.end_dt DESC;
SELECT gslo.unit_cd,
gslo.sua_cal_type,
gslo.sua_ci_sequence_number,
gslo.tr_org_unit_cd,
gslo.tr_ou_start_dt,
gslo.govt_discipline_group_cd,
gslo.eftsu,
gslo.industrial_ind,
uoo.uoo_id
FROM IGS_ST_GVT_STDNTLOAD gslo,
igs_ps_unit_ofr_opt uoo
WHERE gslo.submission_yr = p_submission_yr AND
gslo.submission_number = p_submission_number AND
gslo.govt_semester = cp_govt_semester AND
gslo.person_id = cp_person_id AND
gslo.course_cd = cp_course_cd AND
gslo.unit_cd = uoo.unit_cd AND
gslo.uv_version_number = uoo.version_number AND
gslo.sua_cal_type = uoo.cal_type AND
gslo.sua_ci_sequence_number = uoo.ci_sequence_number AND
gslo.sua_location_cd = uoo.location_cd AND
gslo.unit_class = uoo.unit_class;
SELECT ess.unit_cd,
ess.sua_cal_type,
ess.sua_ci_sequence_number,
ess.tr_org_unit_cd,
ess.tr_ou_start_dt,
ess.govt_discipline_group_cd,
ess.eftsu,
uv.industrial_ind,
ess.crv_version_number,
ess.uv_version_number,
ess.enrolled_dt,
ess.discontinued_dt,
ess.ci_cal_type,
ess.ci_sequence_number,
uoo.uoo_id
FROM IGS_EN_ST_SNAPSHOT ess,
IGS_PS_UNIT_VER uv,
IGS_ST_GVTSEMLOAD_CA gslc,
igs_ps_unit_ofr_opt uoo
WHERE ess.snapshot_dt_time = cp_snapshot_dt_time AND
ess.ci_cal_type = gslc.cal_type AND
ess.ci_sequence_number = gslc.ci_sequence_number AND
gslc.submission_yr = p_submission_yr AND
gslc.submission_number = p_submission_number AND
gslc.govt_semester = cp_govt_semester AND
ess.person_id = cp_person_id AND
ess.course_cd = cp_course_cd AND
ess.govt_reportable_ind <> 'X' AND
uv.unit_cd = ess.unit_cd AND
uv.version_number = ess.uv_version_number AND
ess.unit_cd = uoo.unit_cd AND
ess.uv_version_number = uoo.version_number AND
ess.sua_cal_type = uoo.cal_type AND
ess.sua_ci_sequence_number = uoo.ci_sequence_number AND
ess.sua_location_cd = uoo.location_cd AND
ess.unit_class = uoo.unit_class ;
SELECT suah.last_updated_by,
suah.last_update_date
FROM IGS_EN_SU_ATTEMPT_H suah
WHERE suah.person_id = cp_person_id AND
suah.course_cd = cp_course_cd AND
suah.uoo_id = cp_uoo_id AND
(suah.enrolled_dt IS NOT NULL OR
suah.discontinued_dt IS NOT NULL)
ORDER BY suah.hist_end_dt DESC;
SELECT suah.last_updated_by,
suah.last_update_date
FROM IGS_EN_SU_ATTEMPT_H suah
WHERE suah.person_id = cp_person_id AND
suah.course_cd = cp_course_cd AND
suah.uoo_id = cp_uoo_id
ORDER BY suah.hist_end_dt DESC;
SELECT suah.last_updated_by,
suah.last_update_date
FROM IGS_EN_SU_ATTEMPT_H suah
WHERE suah.person_id = cp_person_id AND
suah.course_cd = cp_course_cd AND
suah.uoo_id = cp_uoo_id AND
(suah.enrolled_dt IS NOT NULL OR
suah.discontinued_dt IS NOT NULL)
ORDER BY suah.hist_end_dt DESC;
SELECT sua.last_updated_by,
sua.last_update_date
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.uoo_id = cp_uoo_id
ORDER BY sua.last_update_date DESC;
SELECT gse.person_id,
gse.course_cd,
gse.version_number,
gse.citizenship_cd,
gse.perm_resident_cd,
gse.prior_degree,
gse.prior_post_grad
FROM IGS_ST_GOVT_STDNT_EN gse
WHERE gse.submission_yr = p_submission_yr AND
gse.submission_number = 1 AND -- Government Enrolment only sub 1
gse.person_id = cp_person_id AND
gse.course_cd = cp_course_cd;
SELECT NVL(ps.citizenship_cd, '9'),
NVL(ps.perm_resident_cd, '9'),
ps.prior_degree,
ps.prior_post_grad
FROM IGS_PE_STATISTICS ps
WHERE ps.person_id = cp_person_id AND
ps.start_dt <= cp_effective_dt AND
(ps.end_dt IS NULL OR
ps.end_dt >= cp_effective_dt)
ORDER BY ps.end_dt;
SELECT 'x'
FROM IGS_ST_GVT_SPSHT_CHG gsc
WHERE gsc.submission_yr = p_submission_yr AND
gsc.submission_number = p_submission_number AND
gsc.person_id = cp_person_id AND
gsc.course_cd = cp_course_cd AND
gsc.version_number = cp_crv_version_number AND
NVL(gsc.old_citizenship_cd, -1) = NVL(cp_old_citizenship_cd, -1) AND
NVL(gsc.old_perm_resident_cd, -1) = NVL(cp_old_perm_resident_cd, -1) AND
NVL(gsc.perm_resident_cd, -1) = NVL(cp_perm_resident_cd, -1) AND
NVL(gsc.citizenship_cd, -1) = NVL(cp_citizenship_cd, -1);
SELECT ps.last_updated_by,
ps.last_update_date
FROM IGS_PE_STATISTICS ps
WHERE ps.person_id = cp_person_id AND
ps.end_dt IS NOT NULL AND
ps.perm_resident_cd = cp_old_perm_resident_cd AND
ps.citizenship_cd = cp_old_citizenship_cd
ORDER BY ps.end_dt DESC;
SELECT ps.last_updated_by,
ps.last_update_date
FROM IGS_PE_STATISTICS ps
WHERE ps.person_id = cp_person_id AND
ps.end_dt IS NOT NULL -- AND
-- ps.end_dt > cp_effective_dt
ORDER BY ps.end_dt DESC;
v_last_updated_by IGS_EN_STDNTPSHECSOP.last_updated_by%TYPE;
v_last_update_date IGS_EN_STDNTPSHECSOP.last_update_date%TYPE;
-- select all govt_student_liability_records for previous submission
FOR v_gsli_gslc IN c_gsli_gslc LOOP
v_person_id := v_gsli_gslc.person_id;
FETCH c_scho1 INTO v_last_updated_by,
v_last_update_date;
FETCH c_scho2 INTO v_last_updated_by,
v_last_update_date;
FETCH c_suah1 INTO v_last_updated_by,
v_last_update_date;
FETCH c_suah2 INTO v_last_updated_by,
v_last_update_date;
FETCH c_suah3 INTO v_last_updated_by,
v_last_update_date;
FETCH c_sua INTO v_last_updated_by,
v_last_update_date;
-- Insert changed details in table
-- to insert row using the insertrow of respective TBH package
IGS_ST_GVT_SPSHT_CHG_PKG.INSERT_ROW(
X_ROWID => v_rowid,
X_SUBMISSION_YR => p_submission_yr,
X_SUBMISSION_NUMBER => p_submission_number,
X_PERSON_ID => v_person_id,
X_COURSE_CD => v_course_cd,
X_VERSION_NUMBER => v_crv_version_number,
X_SEQUENCE_NUMBER => v_seqnum,
X_CHANGED_UPDATE_WHO => v_last_updated_by,
X_CHANGED_UPDATE_ON => v_last_update_date,
X_GOVT_SEMESTER => v_govt_semester,
X_UNIT_CD => v_new_unit_cd,
X_EFTSU => v_new_eftsu,
X_HECS_PREXMT_EXIE => v_hecs_prexmt_exie,
X_HECS_AMOUNT_PAID => v_hecs_amount_paid,
X_HECS_PAYMENT_OPTION => v_hecs_payment_option,
X_DIFFERENTIAL_HECS_IND => v_differential_hecs_ind,
X_CITIZENSHIP_CD => NULL,
X_PERM_RESIDENT_CD => NULL,
X_PRIOR_DEGREE => NULL,
X_PRIOR_POST_GRAD => NULL,
X_OLD_UNIT_CD => NULL,
X_OLD_EFTSU => NULL,
X_OLD_HECS_PREXMT_EXIE => v_old_hecs_prexmt_exie,
X_OLD_HECS_AMOUNT_PAID => v_old_hecs_amount_paid,
X_OLD_HECS_PAYMENT_OPTION => v_old_hecs_payment_option,
X_OLD_DIFFERENTIAL_HECS_IND => v_old_differential_hecs_ind,
X_OLD_CITIZENSHIP_CD => NULL,
X_OLD_PERM_RESIDENT_CD => NULL,
X_OLD_PRIOR_DEGREE => NULL,
X_OLD_PRIOR_POST_GRAD => NULL,
X_REPORTED_IND => 'N',
X_MODE => 'R');
-- reset the changed update who and on fields
v_last_updated_by := NULL;
v_last_update_date := NULL;
FETCH c_ps2 INTO v_last_updated_by,
v_last_update_date;
FETCH c_ps3 INTO v_last_updated_by,
v_last_update_date;
-- Insert changed details in table
-- to insert row using the insertrow of respective TBH package
IGS_ST_GVT_SPSHT_CHG_PKG.INSERT_ROW(
X_ROWID => v_rowid,
X_SUBMISSION_YR => p_submission_yr,
X_SUBMISSION_NUMBER => p_submission_number,
X_PERSON_ID => v_person_id,
X_COURSE_CD => v_course_cd,
X_SEQUENCE_NUMBER => v_seqnum,
X_VERSION_NUMBER => v_crv_version_number,
X_CHANGED_UPDATE_WHO => v_last_updated_by,
X_CHANGED_UPDATE_ON => v_last_update_date,
X_GOVT_SEMESTER => NULL,
X_UNIT_CD => NULL,
X_EFTSU => NULL,
X_HECS_PREXMT_EXIE => NULL,
X_HECS_AMOUNT_PAID => NULL,
X_HECS_PAYMENT_OPTION => NULL,
X_DIFFERENTIAL_HECS_IND => NULL,
X_CITIZENSHIP_CD => v_citizenship_cd,
X_PERM_RESIDENT_CD => v_perm_resident_cd,
X_PRIOR_DEGREE => NULL,
X_PRIOR_POST_GRAD => NULL,
X_OLD_UNIT_CD => NULL,
X_OLD_EFTSU => NULL,
X_OLD_HECS_PREXMT_EXIE => NULL,
X_OLD_HECS_AMOUNT_PAID => NULL,
X_OLD_HECS_PAYMENT_OPTION => NULL,
X_OLD_DIFFERENTIAL_HECS_IND => NULL,
X_OLD_CITIZENSHIP_CD => v_old_citizenship_cd,
X_OLD_PERM_RESIDENT_CD => v_old_perm_resident_cd,
X_OLD_PRIOR_DEGREE => NULL,
X_OLD_PRIOR_POST_GRAD => NULL,
X_REPORTED_IND => 'N',
X_MODE => 'R');
-- reset the changed update who and on fields
v_last_updated_by := NULL;
v_last_update_date := NULL;