[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT closed_ind
FROM IGS_EN_ENCMB_EFCTTYP_V
WHERE s_encmb_effect_type = cp_s_encmb_effect_type;
SELECT eft.*,
seft.description encmb_meaning
FROM IGS_FI_ENC_DFLT_EFT eft,
IGS_EN_ENCMB_EFCTTYP_V seft
WHERE eft.encumbrance_type = p_encumbrance_type AND
eft.s_encmb_effect_type = seft.s_encmb_effect_type;
SELECT apply_to_course_ind
FROM IGS_EN_ENCMB_EFCTTYP_V
WHERE s_encmb_effect_type = cp_s_encmb_effect_type;
SELECT course_cd
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_attempt_status NOT IN ('DISCONTIN', 'COMPLETED', 'DELETED', 'LAPSED',
'UNCONFIRM');
SELECT IGS_PE_PERSENC_EFFCT_SEQ_NUM_S.NEXTVAL
INTO v_pee_seq_num
FROM DUAL;
IGS_PE_PERSENC_EFFCT_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => p_person_id,
x_encumbrance_type => p_encumbrance_type,
x_pen_start_dt => p_start_dt,
x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
x_pee_start_dt => p_start_dt,
x_sequence_number => v_pee_seq_num,
x_expiry_dt => p_expiry_dt,
x_course_cd => v_course_cd,
X_RESTRICTED_ENROLMENT_CP => NULL,
X_RESTRICTED_ATTENDANCE_TYPE => NULL,
X_MODE => 'R'
);
IGS_PE_COURSE_EXCL_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_encumbrance_type => p_encumbrance_type,
x_pen_start_dt => p_start_dt,
x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
x_pee_start_dt => p_start_dt,
x_pee_sequence_number => v_pee_seq_num,
x_course_cd => p_course_cd,
x_pce_start_dt => p_start_dt,
x_expiry_dt => p_expiry_dt,
x_mode => 'R'
);
IGS_PE_COURSE_EXCL_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_encumbrance_type => p_encumbrance_type,
x_pen_start_dt => p_start_dt,
x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
x_pee_start_dt => p_start_dt,
x_pee_sequence_number => v_pee_seq_num,
x_course_cd => p_course_cd,
x_pce_start_dt => p_start_dt,
x_expiry_dt => p_expiry_dt,
x_mode => 'R'
);
|| Added a check saying if in insert row of student program attempt is called
|| it insert the awards else if called in udpaterow it updates the date or inserts the new awards which is not
|| already inserted.
|| (reverse chronological order - newest change first) igs_en_spa_awd_aim
*/
CURSOR cur_caw IS
SELECT caw.award_cd, awd.grading_schema_cd, awd.gs_version_number
FROM igs_ps_award caw, igs_ps_awd awd
WHERE caw.course_cd = p_course_cd AND
caw.version_number = p_version_number AND
caw.default_ind = 'Y' AND
caw.closed_ind = 'N' AND
caw.award_cd = awd.award_cd;
SELECT count(*)
FROM igs_en_spa_awd_aim awd
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT awd.*, awd.rowid
FROM igs_en_spa_awd_aim awd
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT commencement_dt, course_rqrmnt_complete_ind
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
version_number = p_version_number;
igs_en_spa_awd_aim_pkg.insert_row (
x_rowid => lv_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_award_cd => v_cur_caw.award_cd,
x_start_dt => p_start_dt,
x_end_dt => l_end_date,
x_complete_ind => 'N',
x_conferral_date => NULL,
x_award_mark => NULL,
x_award_grade => NULL,
x_grading_schema_cd => v_cur_caw.grading_schema_cd,
x_gs_version_number => v_cur_caw.gs_version_number,
x_mode => 'R'
);
igs_en_spa_awd_aim_pkg.update_row (
x_rowid => l_spaa_awd_rec.rowid,
x_person_id => l_spaa_awd_rec.person_id,
x_course_cd => l_spaa_awd_rec.course_cd,
x_award_cd => l_spaa_awd_rec.award_cd,
x_start_dt => p_start_dt,
x_end_dt => l_spaa_awd_rec.end_dt,
x_complete_ind => l_spaa_awd_rec.complete_ind,
x_conferral_date => l_spaa_awd_rec.conferral_date,
x_award_mark => l_spaa_awd_rec.award_mark,
x_award_grade => l_spaa_awd_rec.award_grade,
x_grading_schema_cd => l_spaa_awd_rec.grading_schema_cd,
x_gs_version_number => l_spaa_awd_rec.gs_version_number,
x_mode => 'R');
SELECT smir.obsolete_person_id,
smir.obsolete_id_row_info,
smir.current_person_id,
smir.current_id_row_info,
smir.table_alias,
smiac.action_id,
smiac.perform_action_ind
FROM IGS_EN_MERGE_ID_ROWS smir, IGS_EN_MRG_ID_ACT_CH smiac
WHERE smir.smir_id = p_smir_id AND
smir.smir_id = smiac.smir_id;
IGS_EN_MERGE_ID_LOG_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_obsolete_person_id => v_merge_record.obsolete_person_id,
x_obsolete_id_row_info => v_merge_record.obsolete_id_row_info,
x_current_person_id => v_merge_record.current_person_id,
x_current_id_row_info => v_merge_record.current_id_row_info,
x_table_alias => v_merge_record.table_alias,
x_action_id => v_merge_record.action_id,
x_perform_action_ind => v_merge_record.perform_action_ind,
x_SMIL_ID => l_SMIL_id );
SELECT aci.cal_type,
aci.sequence_number,
aci.start_dt,
aci.end_dt
FROM IGS_CA_INST aci
WHERE aci.cal_type = p_acad_cal_type AND
aci.sequence_number = p_acad_sequence_number;
SELECT pos.cal_type,
pos.sequence_number,
pos.always_pre_enrol_ind,
pos.number_of_periods,
pos.aprvd_ci_sequence_number ,
pos.acad_perd_unit_set
FROM IGS_PS_PAT_OF_STUDY pos
WHERE pos.course_cd = p_course_cd AND
pos.version_number = p_version_number AND
pos.cal_type = p_acad_cal_type AND
((pos.location_cd IS NULL AND
pos.attendance_mode IS NULL AND
pos.attendance_type IS NULL AND
pos.unit_set_cd IS NULL AND
pos.admission_cal_type IS NULL AND
pos.admission_cat IS NULL) OR
IGS_EN_GEN_005.enrp_get_pos_links(
p_location_cd,
p_attendance_mode,
p_attendance_type,
cp_unit_set_cd,
p_adm_cal_type,
p_admission_cat,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) > 0)
ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
p_location_cd,
p_attendance_mode,
p_attendance_type,
cp_unit_set_cd,
p_adm_cal_type,
p_admission_cat,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) DESC;
SELECT usm.stream_unit_set_Cd
FROM igs_en_unit_set_map usm,
igs_ps_us_prenr_cfg upc
WHERE upc.unit_set_cd = cp_admin_unit_set_cd
AND usm.mapping_set_cd = upc.mapping_set_cd
AND usm.sequence_no = upc.sequence_no;
SELECT 'X'
FROM igs_as_su_setatmpt susa
WHERE susa.unit_set_cd = cp_stream_unit_set_cd
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.end_dt IS NULL
AND susa.rqrmnts_complete_dt IS NULL;
SELECT am.govt_attendance_mode
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_EN_ATD_MODE am
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
am.attendance_mode = sca.attendance_mode;
SELECT posp.acad_period_num,
posp.teach_cal_type,
posp.sequence_number
FROM IGS_PS_PAT_STUDY_PRD posp
WHERE posp.pos_sequence_number = cp_sequence_number AND
posp.acad_period_num >= cp_period_number AND
posp.acad_period_num < (cp_period_number
+ cp_number_of_periods) AND
EXISTS (SELECT 'x'
FROM IGS_PS_PAT_STUDY_UNT posu
WHERE posp.sequence_number = posu.posp_sequence_number AND
posu.unit_cd IS NOT NULL)
ORDER BY posp.acad_period_num;
SELECT aci.cal_type,
aci.sequence_number,
aci.start_dt,
aci.end_dt
FROM IGS_CA_INST aci,
IGS_CA_STAT cs
WHERE aci.cal_type = p_acad_cal_type AND
aci.start_dt > cp_start_dt AND
cs.cal_status = aci.cal_status AND
cs.s_cal_status = cst_active
ORDER BY aci.start_dt;
SELECT ci.start_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = cp_cal_type AND
ci.sequence_number = cp_sequence_number;
SELECT tci.cal_type,
tci.sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST tci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = cp_acad_cal_type AND
sup_ci_sequence_number = cp_acad_sequence_number AND
sub_cal_type = cp_teach_cal_type AND
tci.cal_type = cir.sub_cal_type AND
sequence_number = cir.sub_ci_sequence_number AND
cat.cal_type = tci.cal_type AND
cat.s_cal_cat = 'TEACHING' AND
cs.cal_status = tci.cal_status AND
cs.s_cal_status = cst_active
ORDER BY tci.start_dt DESC;
SELECT posu.unit_cd,
posu.unit_location_cd,
posu.unit_class,
posu.core_ind
FROM IGS_PS_PAT_STUDY_UNT posu
WHERE posu.posp_sequence_number = cp_sequence_number AND
unit_cd IS NOT NULL
AND (
( NVL (core_ind,'N') = cp_core_only
AND cp_core_only = 'Y'
)
OR
cp_core_only = 'N'
);
SELECT DISTINCT acad_perd
FROM igs_en_susa_year_v
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_set_cd = p_unit_set_cd ;
SELECT DECODE(sua.unit_attempt_status, 'UNCONFIRM', 'N', 'WAITLISTED', 'Y' , NULL)
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
SELECT relation_type
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = p_uoo_id;
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
uoo_id = cp_uoo_id ;
SELECT cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT 'x'
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd AND
scae.cal_type = p_cal_type AND
scae.ci_sequence_number = p_ci_sequence_number;
IGS_AS_SC_ATMPT_ENR_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_enrolment_cat => p_enrolment_cat,
X_ENROLLED_DT => NULL,
X_ENR_FORM_DUE_DT => NULL,
X_ENR_PCKG_PROD_DT => NULL,
X_ENR_FORM_RECEIVED_DT => NULL );
SELECT ca.sequence_number,
ca.attendance_percentage
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sca_course_cd = p_course_cd;
SELECT cah.hist_end_dt
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.ca_sequence_number = cp_ca_sequence_number
ORDER BY cah.hist_end_dt DESC;
SELECT NVL(MAX(cah2.sequence_number),0)+1
FROM IGS_RE_CDT_ATT_HIST cah2
WHERE cah2.person_id = p_person_id AND
cah2.ca_sequence_number = cp_sequence_number;
p_message_name := 'IGS_RE_FIRST_HIST_CANT_INSERT';
IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_sequence_number => v_cah_sequence_number,
x_ca_sequence_number => v_sequence_number,
x_hist_start_dt => v_hist_start_dt,
x_hist_end_dt => TRUNC(SYSDATE) - 1,
x_attendance_type => p_old_attendance_type,
x_attendance_percentage => v_attendance_percentage_new,
x_mode => 'R',
x_org_id => l_org_id);
p_new_update_who IN IGS_EN_STDNT_PS_ATT_ALL.last_updated_by%TYPE ,
p_old_update_who IN IGS_EN_STDNT_PS_ATT_ALL.last_updated_by%TYPE ,
p_new_update_on IN IGS_EN_STDNT_PS_ATT_ALL.last_update_date%TYPE ,
p_old_update_on IN IGS_EN_STDNT_PS_ATT_ALL.last_update_date%TYPE ,
p_new_last_date_of_attendance IN IGS_EN_STDNT_PS_ATT_ALL.last_date_of_attendance%TYPE ,
p_old_last_date_of_attendance IN IGS_EN_STDNT_PS_ATT_ALL.last_date_of_attendance%TYPE ,
p_new_dropped_by IN IGS_EN_STDNT_PS_ATT_ALL.dropped_by%TYPE ,
p_old_dropped_by IN IGS_EN_STDNT_PS_ATT_ALL.dropped_by%TYPE ,
p_new_primary_program_type IN IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE ,
p_old_primary_program_type IN IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE ,
p_new_primary_prog_type_source IN IGS_EN_STDNT_PS_ATT_ALL.primary_prog_type_source%TYPE,
p_old_primary_prog_type_source IN IGS_EN_STDNT_PS_ATT_ALl.primary_prog_type_source%TYPE ,
p_new_catalog_cal_type IN IGS_EN_STDNT_PS_ATT_ALl.catalog_cal_type%TYPE ,
p_old_catalog_cal_type IN IGS_EN_STDNT_PS_ATT_ALl.catalog_cal_type%TYPE ,
p_new_catalog_seq_num IN IGS_EN_STDNT_PS_ATT_ALl.catalog_seq_num%TYPE,
p_old_catalog_seq_num IN IGS_EN_STDNT_PS_ATT_ALl.catalog_seq_num%TYPE ,
p_new_key_program IN IGS_EN_STDNT_PS_ATT_ALl.key_program%TYPE ,
p_old_key_program IN IGS_EN_STDNT_PS_ATT_ALl.key_program%TYPE ,
p_new_override_cmpl_dt IN IGS_EN_STDNT_PS_ATT_ALL.override_cmpl_dt%TYPE ,
p_old_override_cmpl_dt IN IGS_EN_STDNT_PS_ATT_ALL.override_cmpl_dt%TYPE ,
p_new_manual_ovr_cmpl_dt_ind IN IGS_EN_STDNT_PS_ATT_ALL.manual_ovr_cmpl_dt_ind%TYPE ,
p_old_manual_ovr_cmpl_dt_ind IN IGS_EN_STDNT_PS_ATT_ALL.manual_ovr_cmpl_dt_ind%TYPE,
p_new_coo_id IN IGS_EN_STDNT_PS_ATT_ALL.coo_id%TYPE,
p_old_coo_id IN IGS_EN_STDNT_PS_ATT_ALL.coo_id%TYPE,
p_new_igs_pr_class_std_id IGS_EN_STDNT_PS_ATT_ALL.igs_pr_class_std_id%TYPE,
p_old_igs_pr_class_std_id IGS_EN_STDNT_PS_ATT_ALL.igs_pr_class_std_id%TYPE
)
AS
BEGIN
DECLARE
r_scah IGS_AS_SC_ATTEMPT_H%ROWTYPE;
SELECT description
FROM IGS_FI_FUND_SRC
WHERE funding_source = r_scah.funding_source;
SELECT description
FROM IGS_AD_LOCATION
WHERE location_cd = r_scah.exam_location_cd;
SELECT description
FROM IGS_FI_FEE_CAT
WHERE fee_cat = r_scah.fee_cat;
SELECT description
FROM IGS_CO_CAT
WHERE correspondence_cat = r_scah.correspondence_cat;
r_scah.hist_start_dt := p_old_update_on;
r_scah.hist_end_dt := p_new_update_on;
r_scah.hist_who := p_old_update_who;
v_update_existing BOOLEAN;
SELECT hpo.govt_hecs_payment_option,
hpo.expire_aftr_acdmc_perd_ind
FROM IGS_FI_HECS_PAY_OPTN hpo
WHERE hpo.hecs_payment_option = p_hecs_payment_option;
SELECT 'x'
FROM IGS_FI_GOV_HEC_PA_OP ghpo
WHERE ghpo.govt_hecs_payment_option = cp_govt_hecs_payment_option AND
ghpo.s_hecs_payment_type = 'EXEMPT';
SELECT ROWID,
IGS_EN_STDNTPSHECSOP.*
FROM IGS_EN_STDNTPSHECSOP
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
end_dt IS NULL
FOR UPDATE OF end_dt NOWAIT;
SELECT scho.end_dt,
scho.start_dt,
scho.hecs_payment_option
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = p_person_id AND
scho.course_cd = p_course_cd
ORDER BY scho.end_dt DESC;
CURSOR c_scho_update IS
SELECT ROWID, IGS_EN_STDNTPSHECSOP.*
FROM IGS_EN_STDNTPSHECSOP
WHERE person_id = p_person_id AND
course_cd = p_course_cd
FOR UPDATE OF hecs_payment_option NOWAIT;
c_scho_update_rec c_scho_update%ROWTYPE;
SELECT ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_acad_cal_type AND
ci.sequence_number = p_acad_sequence_number;
v_scho_rec.diff_hecs_ind_update_who := NULL;
v_scho_rec.diff_hecs_ind_update_on := NULL;
v_scho_rec.diff_hecs_ind_update_comments := NULL;
v_update_existing := FALSE;
v_update_existing := TRUE;
IGS_EN_STDNTPSHECSOP_PKG.UPDATE_ROW(
X_ROWID => c_scho_open_end_rec.ROWID,
X_PERSON_ID => c_scho_open_end_rec.PERSON_ID,
X_COURSE_CD => c_scho_open_end_rec.COURSE_CD,
X_START_DT => c_scho_open_end_rec.START_DT,
X_END_DT => TRUNC(SYSDATE)-1,
X_HECS_PAYMENT_OPTION => c_scho_open_end_rec.HECS_PAYMENT_OPTION,
X_DIFFERENTIAL_HECS_IND => c_scho_open_end_rec.DIFFERENTIAL_HECS_IND,
X_DIFF_HECS_IND_UPDATE_WHO => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_WHO,
X_DIFF_HECS_IND_UPDATE_ON => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_ON ,
X_OUTSIDE_AUS_RES_IND => c_scho_open_end_rec.OUTSIDE_AUS_RES_IND,
X_NZ_CITIZEN_IND => c_scho_open_end_rec.NZ_CITIZEN_IND,
X_NZ_CITIZEN_LESS2YR_IND => c_scho_open_end_rec.NZ_CITIZEN_LESS2YR_IND,
X_NZ_CITIZEN_NOT_RES_IND => c_scho_open_end_rec.NZ_CITIZEN_NOT_RES_IND,
X_SAFETY_NET_IND => c_scho_open_end_rec.SAFETY_NET_IND,
X_TAX_FILE_NUMBER => c_scho_open_end_rec.TAX_FILE_NUMBER ,
X_TAX_FILE_NUMBER_COLLECTED_DT => c_scho_open_end_rec.TAX_FILE_NUMBER_COLLECTED_DT,
X_TAX_FILE_INVALID_DT => c_scho_open_end_rec.TAX_FILE_INVALID_DT,
X_TAX_FILE_CERTIFICATE_NUMBER => c_scho_open_end_rec.TAX_FILE_CERTIFICATE_NUMBER,
X_DIFF_HECS_IND_UPDATE_COMMENT => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_COMMENTS,
X_MODE => 'R'
);
v_scho_rec.diff_hecs_ind_update_who,
v_scho_rec.diff_hecs_ind_update_on,
v_scho_rec.diff_hecs_ind_update_comments,
v_scho_rec.outside_aus_res_ind,
v_scho_rec.nz_citizen_ind,
v_scho_rec.nz_citizen_less2yr_ind,
v_scho_rec.nz_citizen_not_res_ind,
v_scho_rec.safety_net_ind,
v_scho_rec.tax_file_number,
v_scho_rec.tax_file_number_collected_dt,
v_scho_rec.tax_file_invalid_dt,
v_scho_rec.tax_file_certificate_number,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_update_existing = FALSE THEN
DECLARE
l_rowid VARCHAR2(25);
IGS_EN_STDNTPSHECSOP_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_start_dt,
x_end_dt => v_scho_rec.end_dt,
x_hecs_payment_option => v_scho_rec.hecs_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => v_scho_rec.safety_net_ind,
x_tax_file_number => v_scho_rec.tax_file_number,
x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number);
OPEN c_scho_update;
FETCH c_scho_update INTO c_scho_update_rec;
IF c_scho_update%FOUND THEN
IGS_EN_STDNTPSHECSOP_PKG.UPDATE_ROW(
X_ROWID => c_scho_update_rec.ROWID,
X_PERSON_ID => c_scho_update_rec.PERSON_ID,
X_COURSE_CD => c_scho_update_rec.COURSE_CD,
X_START_DT => c_scho_update_rec.START_DT,
X_END_DT => v_scho_rec.end_dt,
X_HECS_PAYMENT_OPTION => p_hecs_payment_option,
X_DIFFERENTIAL_HECS_IND => v_scho_rec.differential_hecs_ind,
X_DIFF_HECS_IND_UPDATE_WHO => v_scho_rec.DIFF_HECS_IND_UPDATE_WHO,
X_DIFF_HECS_IND_UPDATE_ON => v_scho_rec.DIFF_HECS_IND_UPDATE_ON ,
X_OUTSIDE_AUS_RES_IND => v_scho_rec.OUTSIDE_AUS_RES_IND,
X_NZ_CITIZEN_IND => v_scho_rec.NZ_CITIZEN_IND,
X_NZ_CITIZEN_LESS2YR_IND => v_scho_rec.NZ_CITIZEN_LESS2YR_IND,
X_NZ_CITIZEN_NOT_RES_IND => v_scho_rec.NZ_CITIZEN_NOT_RES_IND,
X_SAFETY_NET_IND => v_scho_rec.SAFETY_NET_IND,
X_TAX_FILE_NUMBER => v_scho_rec.TAX_FILE_NUMBER ,
X_TAX_FILE_NUMBER_COLLECTED_DT => v_scho_rec.TAX_FILE_NUMBER_COLLECTED_DT,
X_TAX_FILE_INVALID_DT => v_scho_rec.TAX_FILE_INVALID_DT,
X_TAX_FILE_CERTIFICATE_NUMBER => v_scho_rec.TAX_FILE_CERTIFICATE_NUMBER,
X_DIFF_HECS_IND_UPDATE_COMMENT => v_scho_rec.DIFF_HECS_IND_UPDATE_COMMENTS,
X_MODE => 'R'
);
CLOSE c_scho_update;
IF c_scho_update%ISOPEN THEN
CLOSE c_scho_update;
IF c_scho_update%ISOPEN THEN
CLOSE c_scho_update;
IGS_PS_STDNT_TRN_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_transfer_course_cd => p_transfer_course_cd,
x_TRANSFER_DT => NVL(p_transfer_dt,SYSDATE),
x_COMMENTS => NULL,
X_APPROVED_DATE => p_trans_approved_dt,
X_EFFECTIVE_TERM_CAL_TYPE => p_term_cal_type,
X_EFFECTIVE_TERM_SEQUENCE_NUM => p_term_seq_num,
X_DISCONTINUE_SOURCE_FLAG => p_discontinue_src_flag,
X_UOOIDS_TO_TRANSFER => p_uooids_to_transfer,
X_SUSA_TO_TRANSFER => p_susa_to_transfer,
X_TRANSFER_ADV_STAND_FLAG => p_transfer_adv_stand_flag,
X_STATUS_DATE => p_status_date,
X_STATUS_FLAG => p_status_flag
);
SELECT location_cd,
attendance_mode,
attendance_type,
version_number
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT pos.cal_type,
pos.sequence_number,
pos.always_pre_enrol_ind,
pos.number_of_periods,
pos.aprvd_ci_sequence_number ,
pos.acad_perd_unit_set
FROM IGS_PS_PAT_OF_STUDY pos
WHERE pos.course_cd = p_program_cd AND
pos.version_number = l_ps_att_dtls_rec.version_number AND
pos.cal_type = l_acad_cal_type AND
((pos.location_cd IS NULL AND
pos.attendance_mode IS NULL AND
pos.attendance_type IS NULL AND
pos.unit_set_cd IS NULL AND
pos.admission_cal_type IS NULL AND
pos.admission_cat IS NULL) OR
IGS_EN_GEN_005.enrp_get_pos_links(
l_ps_att_dtls_rec.location_cd,
l_ps_att_dtls_rec.attendance_mode,
l_ps_att_dtls_rec.attendance_type,
cp_unit_set_cd,
NULL,
NULL,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) > 0)
ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
l_ps_att_dtls_rec.location_cd,
l_ps_att_dtls_rec.attendance_mode,
l_ps_att_dtls_rec.attendance_type,
cp_unit_set_cd,
NULL,
NULL,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) DESC;
SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa ,
igs_en_unit_set us ,
igs_en_unit_set_cat usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_program_cd AND
susa.rqrmnts_complete_dt IS NULL AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR';
SELECT unit_set_cd
FROM igs_as_su_setatmpt
WHERE (
(unit_set_cd = cp_unit_set_cd) OR
(unit_set_cd IN (SELECT stream_unit_set_cd
FROM igs_en_unit_set_map
WHERE (mapping_set_cd,sequence_no) IN (SELECT mapping_set_cd,sequence_no
FROM igs_ps_us_prenr_cfg
WHERE unit_set_cd = cp_unit_set_cd)))
)
AND person_id = cp_person_id
AND course_cd = cp_course_cd;
SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_program_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.rqrmnts_complete_dt IS NULL AND
susa.end_dt IS NULL;
SELECT unit_set_cd
FROM igs_ps_pat_of_study pos
WHERE course_cd = p_program_cd AND
version_number = cp_version_number AND
cal_type = l_acad_cal_type AND
unit_set_cd IN
( SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = pos.course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL);
SELECT unit_cd,
cal_type
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT aci.cal_type,
aci.sequence_number,
aci.start_dt,
aci.end_dt
FROM igs_ca_inst aci
WHERE aci.cal_type = l_acad_cal_type AND
aci.sequence_number = l_acad_ci_sequence_number;
SELECT usm.stream_unit_set_Cd
FROM igs_en_unit_set_map usm,
igs_ps_us_prenr_cfg upc
WHERE upc.unit_set_cd = cp_admin_unit_set_cd
AND usm.mapping_set_cd = upc.mapping_set_cd
AND usm.sequence_no = upc.sequence_no;
SELECT 'X'
FROM igs_as_su_setatmpt susa
WHERE susa.unit_set_cd = cp_stream_unit_set_cd
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.end_dt IS NULL
AND susa.rqrmnts_complete_dt IS NULL;
SELECT DISTINCT acad_perd
FROM igs_en_susa_year_v
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND unit_set_cd = p_unit_set_cd ;
SELECT posp.acad_period_num,
posp.teach_cal_type,
posp.sequence_number
FROM igs_ps_pat_study_prd posp
WHERE posp.pos_sequence_number = cp_sequence_number AND
posp.acad_period_num >= cp_period_number AND
posp.acad_period_num < (cp_period_number
+ cp_number_of_periods) AND
EXISTS (SELECT 'x'
FROM igs_ps_pat_study_unt posu
WHERE posp.sequence_number = posu.posp_sequence_number AND
posu.unit_cd IS NOT NULL)
ORDER BY posp.acad_period_num;
SELECT core_ind
FROM IGS_PS_PAT_STUDY_UNT posu
WHERE posu.posp_sequence_number = cp_sequence_number
AND posu.unit_cd = cp_unit_cd;
SELECT core_indicator_code
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT location_cd,
attendance_mode,
attendance_type,
version_number
FROM IGS_PS_OFR_OPT
WHERE coo_id = p_coo_id;
SELECT pos.cal_type,
pos.sequence_number,
pos.always_pre_enrol_ind,
pos.number_of_periods,
pos.aprvd_ci_sequence_number ,
pos.acad_perd_unit_set
FROM IGS_PS_PAT_OF_STUDY pos
WHERE pos.course_cd = p_dest_program_cd AND
pos.version_number = l_ps_att_dtls_rec.version_number AND
pos.cal_type = l_acad_cal_type AND
((pos.location_cd IS NULL AND
pos.attendance_mode IS NULL AND
pos.attendance_type IS NULL AND
pos.unit_set_cd IS NULL AND
pos.admission_cal_type IS NULL AND
pos.admission_cat IS NULL) OR
IGS_EN_GEN_005.enrp_get_pos_links(
l_ps_att_dtls_rec.location_cd,
l_ps_att_dtls_rec.attendance_mode,
l_ps_att_dtls_rec.attendance_type,
cp_unit_set_cd,
NULL,
NULL,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) > 0)
ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
l_ps_att_dtls_rec.location_cd,
l_ps_att_dtls_rec.attendance_mode,
l_ps_att_dtls_rec.attendance_type,
cp_unit_set_cd,
NULL,
NULL,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat) DESC;
SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa ,
igs_en_unit_set us ,
igs_en_unit_set_cat usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_src_program_cd AND
susa.rqrmnts_complete_dt IS NULL AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR';
SELECT unit_set_cd
FROM igs_as_su_setatmpt
WHERE (
(unit_set_cd = cp_unit_set_cd) OR
(unit_set_cd IN (SELECT stream_unit_set_cd
FROM igs_en_unit_set_map
WHERE (mapping_set_cd,sequence_no) IN (SELECT mapping_set_cd,sequence_no
FROM igs_ps_us_prenr_cfg
WHERE unit_set_cd = cp_unit_set_cd)))
)
AND person_id = cp_person_id
AND course_cd = cp_course_cd;
SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_src_program_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.rqrmnts_complete_dt IS NULL AND
susa.end_dt IS NULL;
SELECT unit_set_cd
FROM igs_ps_pat_of_study pos
WHERE course_cd = p_dest_program_cd AND
version_number = cp_version_number AND
cal_type = l_acad_cal_type AND
unit_set_cd IN
( SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_src_program_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL);
SELECT unit_cd,
cal_type
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT aci.cal_type,
aci.sequence_number,
aci.start_dt,
aci.end_dt
FROM igs_ca_inst aci
WHERE aci.cal_type = l_acad_cal_type AND
aci.sequence_number = l_acad_ci_sequence_number;
SELECT usm.stream_unit_set_Cd
FROM igs_en_unit_set_map usm,
igs_ps_us_prenr_cfg upc
WHERE upc.unit_set_cd = cp_admin_unit_set_cd
AND usm.mapping_set_cd = upc.mapping_set_cd
AND usm.sequence_no = upc.sequence_no;
SELECT DISTINCT acad_perd
FROM igs_en_susa_year_v
WHERE person_id = p_person_id
AND course_cd = p_src_program_cd
AND unit_set_cd = p_unit_set_cd ;
SELECT posp.acad_period_num,
posp.teach_cal_type,
posp.sequence_number
FROM igs_ps_pat_study_prd posp
WHERE posp.pos_sequence_number = cp_sequence_number AND
posp.acad_period_num >= cp_period_number AND
posp.acad_period_num < (cp_period_number
+ cp_number_of_periods) AND
EXISTS (SELECT 'x'
FROM igs_ps_pat_study_unt posu
WHERE posp.sequence_number = posu.posp_sequence_number AND
posu.unit_cd IS NOT NULL)
ORDER BY posp.acad_period_num;
SELECT core_ind
FROM IGS_PS_PAT_STUDY_UNT posu
WHERE posu.posp_sequence_number = cp_sequence_number
AND posu.unit_cd = cp_unit_cd;