The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
DECODE (
p_column_name,
cst_appl_dt, IGS_GE_DATE.igschar(aah.appl_dt),
cst_acad_cal_type, aah.acad_cal_type,
cst_acad_ci_sequence_number, TO_CHAR(aah.acad_ci_sequence_number),
cst_adm_cal_type, aah.adm_cal_type,
cst_adm_ci_sequence_number, TO_CHAR(aah.adm_ci_sequence_number),
cst_admission_cat, aah.ADMISSION_CAT,
cst_s_admission_process_type, aah.s_admission_process_type,
cst_adm_appl_status, aah.ADM_APPL_STATUS,
cst_adm_fee_status, aah.ADM_FEE_STATUS,
cst_tac_appl_ind, aah.tac_appl_ind)
FROM IGS_AD_APPL_HIST aah
WHERE aah.person_id = p_person_id AND
aah.admission_appl_number = p_admission_appl_number AND
aah.hist_start_dt >= p_hist_end_dt AND
DECODE (
p_column_name,
cst_appl_dt, IGS_GE_DATE.igschar(aah.appl_dt),
cst_acad_cal_type, aah.acad_cal_type,
cst_acad_ci_sequence_number, TO_CHAR(aah.acad_ci_sequence_number),
cst_adm_cal_type, aah.adm_cal_type,
cst_adm_ci_sequence_number, TO_CHAR(aah.adm_ci_sequence_number),
cst_admission_cat, aah.ADMISSION_CAT,
cst_s_admission_process_type, aah.s_admission_process_type,
cst_adm_appl_status, aah.ADM_APPL_STATUS,
cst_adm_fee_status, aah.ADM_FEE_STATUS,
cst_tac_appl_ind, aah.tac_appl_ind) IS NOT NULL
ORDER BY
aah.hist_start_dt;
SELECT
DECODE (
p_column_name,
cst_transfer_course_cd, acah.transfer_course_cd,
cst_basis_for_admission_type, acah.basis_for_admission_type,
cst_admission_cd, acah.ADMISSION_CD,
cst_course_rank_set, acah.course_rank_set,
cst_course_rank_schedule, acah.course_rank_schedule,
cst_req_for_reconsideratn_ind, acah.req_for_reconsideration_ind,
cst_req_for_adv_standing_ind, acah.req_for_adv_standing_ind)
FROM IGS_AD_PS_APPL_HIST acah
WHERE acah.person_id = p_person_id AND
acah.admission_appl_number = p_admission_appl_number AND
acah.nominated_course_cd = p_nominated_course_cd AND
acah.hist_start_dt >= p_hist_end_dt AND
DECODE (
p_column_name,
cst_transfer_course_cd, acah.transfer_course_cd,
cst_basis_for_admission_type, acah.basis_for_admission_type,
cst_admission_cd, acah.ADMISSION_CD,
cst_course_rank_set, acah.course_rank_set,
cst_course_rank_schedule, acah.course_rank_schedule,
cst_req_for_reconsideratn_ind, acah.req_for_reconsideration_ind,
cst_req_for_adv_standing_ind, acah.req_for_adv_standing_ind) IS NOT NULL
ORDER BY
acah.hist_start_dt;
SELECT
DECODE (
p_column_name,
cst_hist_offer_round_number, TO_CHAR(acaih.hist_offer_round_number),
cst_adm_cal_type, acaih.adm_cal_type,
cst_adm_ci_sequence_number, TO_CHAR(acaih.adm_ci_sequence_number),
cst_course_cd, acaih.course_cd,
cst_crv_version_number, TO_CHAR(acaih.crv_version_number),
cst_location_cd, acaih.location_cd,
cst_attendance_mode, acaih.ATTENDANCE_MODE,
cst_attendance_type, acaih.ATTENDANCE_TYPE,
cst_unit_set_cd, acaih.unit_set_cd,
cst_us_version_number, TO_CHAR(acaih.us_version_number),
cst_preference_number, TO_CHAR(acaih.preference_number),
cst_adm_doc_status, acaih.ADM_DOC_STATUS,
cst_adm_entry_qual_status, acaih.ADM_ENTRY_QUAL_STATUS,
cst_late_adm_fee_status, acaih.late_adm_fee_status,
cst_adm_outcome_status, acaih.ADM_OUTCOME_STATUS,
cst_adm_otcm_stus_auth_prsn_id,
TO_CHAR(acaih.adm_otcm_status_auth_person_id),
cst_adm_outcome_status_auth_dt,
IGS_GE_DATE.igschar(acaih.adm_outcome_status_auth_dt),
cst_adm_outcome_status_reason, acaih.adm_outcome_status_reason,
cst_offer_dt, IGS_GE_DATE.igschar(acaih.offer_dt),
cst_offer_response_dt, IGS_GE_DATE.igschar(acaih.offer_response_dt),
cst_prpsd_commencement_dt,
IGS_GE_DATE.igschar(acaih.prpsd_commencement_dt),
cst_adm_cndtnl_offer_status, acaih.ADM_CNDTNL_OFFER_STATUS,
cst_cndtnl_offer_satisfied_dt,
IGS_GE_DATE.igschar(acaih.cndtnl_offer_satisfied_dt),
cst_cndtnl_offer_must_be_stsfd, acaih.cndtnl_offer_must_be_stsfd_ind,
cst_adm_offer_resp_status, acaih.ADM_OFFER_RESP_STATUS,
cst_actual_response_dt, IGS_GE_DATE.igschar(acaih.actual_response_dt),
cst_adm_offer_dfrmnt_status, acaih.ADM_OFFER_DFRMNT_STATUS,
cst_deferred_adm_cal_type, acaih.deferred_adm_cal_type,
cst_defer_adm_ci_sequnc_num, TO_CHAR(acaih.deferred_adm_ci_sequence_num),
cst_deferred_tracking_id, TO_CHAR(acaih.deferred_tracking_id),
cst_ass_rank, TO_CHAR(acaih.ass_rank),
cst_secondary_ass_rank, TO_CHAR(acaih.secondary_ass_rank),
cst_intrnnl_accptnce_adv_num,
TO_CHAR(acaih.intrntnl_acceptance_advice_num),
cst_ass_tracking_id, TO_CHAR(acaih.ass_tracking_id),
cst_fee_cat, acaih.FEE_CAT,
cst_hecs_payment_option, acaih.HECS_PAYMENT_OPTION,
cst_expected_completion_yr, TO_CHAR(acaih.expected_completion_yr),
cst_expected_completion_perd, acaih.expected_completion_perd,
cst_correspondence_cat, acaih.CORRESPONDENCE_CAT,
cst_enrolment_cat, acaih.ENROLMENT_CAT,
cst_funding_source, acaih.FUNDING_SOURCE,
cst_applicant_acptnce_cndtn, acaih.applicant_acptnce_cndtn,
cst_cndtnl_offer_cndtn, acaih.cndtnl_offer_cndtn)
FROM IGS_AD_PS_APLINSTHST acaih
WHERE acaih.person_id = p_person_id AND
acaih.admission_appl_number = p_admission_appl_number AND
acaih.nominated_course_cd = p_nominated_course_cd AND
acaih.sequence_number = p_sequence_number AND
acaih.hist_start_dt >= p_hist_end_dt AND
DECODE (
p_column_name,
cst_hist_offer_round_number, TO_CHAR(acaih.hist_offer_round_number),
cst_adm_cal_type, acaih.adm_cal_type,
cst_adm_ci_sequence_number, TO_CHAR(acaih.adm_ci_sequence_number),
cst_course_cd, acaih.course_cd,
cst_crv_version_number, TO_CHAR(acaih.crv_version_number),
cst_location_cd, acaih.location_cd,
cst_attendance_mode, acaih.ATTENDANCE_MODE,
cst_attendance_type, acaih.ATTENDANCE_TYPE,
cst_unit_set_cd, acaih.unit_set_cd,
cst_us_version_number, TO_CHAR(acaih.us_version_number),
cst_preference_number, TO_CHAR(acaih.preference_number),
cst_adm_doc_status, acaih.ADM_DOC_STATUS,
cst_adm_entry_qual_status, acaih.ADM_ENTRY_QUAL_STATUS,
cst_late_adm_fee_status, acaih.late_adm_fee_status,
cst_adm_outcome_status, acaih.ADM_OUTCOME_STATUS,
cst_adm_otcm_stus_auth_prsn_id,
TO_CHAR(acaih.adm_otcm_status_auth_person_id),
cst_adm_outcome_status_auth_dt,
IGS_GE_DATE.igschar(acaih.adm_outcome_status_auth_dt),
cst_adm_outcome_status_reason, acaih.adm_outcome_status_reason,
cst_offer_dt, IGS_GE_DATE.igschar(acaih.offer_dt),
cst_offer_response_dt, IGS_GE_DATE.igschar(acaih.offer_response_dt),
cst_prpsd_commencement_dt,
IGS_GE_DATE.igschar(acaih.prpsd_commencement_dt),
cst_adm_cndtnl_offer_status, acaih.ADM_CNDTNL_OFFER_STATUS,
cst_cndtnl_offer_satisfied_dt,
IGS_GE_DATE.igschar(acaih.cndtnl_offer_satisfied_dt),
cst_cndtnl_offer_must_be_stsfd, acaih.cndtnl_offer_must_be_stsfd_ind,
cst_adm_offer_resp_status, acaih.ADM_OFFER_RESP_STATUS,
cst_actual_response_dt, IGS_GE_DATE.igschar(acaih.actual_response_dt),
cst_adm_offer_dfrmnt_status, acaih.ADM_OFFER_DFRMNT_STATUS,
cst_deferred_adm_cal_type, acaih.deferred_adm_cal_type,
cst_defer_adm_ci_sequnc_num, TO_CHAR(acaih.deferred_adm_ci_sequence_num),
cst_deferred_tracking_id, TO_CHAR(acaih.deferred_tracking_id),
cst_ass_rank, TO_CHAR(acaih.ass_rank),
cst_secondary_ass_rank, TO_CHAR(acaih.secondary_ass_rank),
cst_intrnnl_accptnce_adv_num, TO_CHAR(acaih.intrntnl_acceptance_advice_num),
cst_ass_tracking_id, TO_CHAR(acaih.ass_tracking_id),
cst_fee_cat, acaih.FEE_CAT,
cst_hecs_payment_option, acaih.HECS_PAYMENT_OPTION,
cst_expected_completion_yr, TO_CHAR(acaih.expected_completion_yr),
cst_expected_completion_perd, acaih.expected_completion_perd,
cst_correspondence_cat, acaih.CORRESPONDENCE_CAT,
cst_enrolment_cat, acaih.ENROLMENT_CAT,
cst_funding_source, acaih.FUNDING_SOURCE,
cst_applicant_acptnce_cndtn, acaih.applicant_acptnce_cndtn,
cst_cndtnl_offer_cndtn, acaih.cndtnl_offer_cndtn) IS NOT NULL
ORDER BY
acaih.hist_start_dt;
SELECT DECODE(p_column_name,
cst_uv_version_number, TO_CHAR(acaiuh.uv_version_number),
cst_cal_type, acaiuh.CAL_TYPE,
cst_ci_sequence_number, TO_CHAR(acaiuh.ci_sequence_number),
cst_location_cd, acaiuh.location_cd,
cst_unit_class, acaiuh.UNIT_CLASS,
cst_unit_mode, acaiuh.UNIT_MODE,
cst_adm_unit_outcome_status, acaiuh.ADM_UNIT_OUTCOME_STATUS,
cst_ass_tracking_id, TO_CHAR(acaiuh.ass_tracking_id),
cst_rule_waived_dt,
IGS_GE_DATE.igscharDT(acaiuh.rule_waived_dt),
cst_rule_waived_person_id, TO_CHAR(acaiuh.rule_waived_person_id),
cst_sup_unit_cd, acaiuh.sup_unit_cd,
cst_sup_uv_version_number, TO_CHAR(acaiuh.sup_uv_version_number))
FROM IGS_AD_PS_APINTUNTHS acaiuh
WHERE acaiuh.adm_ps_appl_inst_unit_id = p_adm_ps_appl_inst_unit_id AND
/*********************** 3083148 ***********************
acaiuh.person_id = p_person_id AND
acaiuh.admission_appl_number = p_admission_appl_number AND
acaiuh.nominated_course_cd = p_nominated_course_cd AND
acaiuh.acai_sequence_number = p_acai_sequence_number AND
acaiuh.unit_cd = p_unit_cd AND
*********************** 3083148 ***********************/
acaiuh.hist_start_dt >= p_hist_end_dt AND
DECODE(p_column_name,
cst_uv_version_number, TO_CHAR(acaiuh.uv_version_number),
cst_cal_type, acaiuh.CAL_TYPE,
cst_ci_sequence_number, TO_CHAR(acaiuh.ci_sequence_number),
cst_location_cd, acaiuh.location_cd,
cst_unit_class, acaiuh.UNIT_CLASS,
cst_unit_mode, acaiuh.UNIT_MODE,
cst_adm_unit_outcome_status, acaiuh.ADM_UNIT_OUTCOME_STATUS,
cst_ass_tracking_id, TO_CHAR(acaiuh.ass_tracking_id),
cst_rule_waived_dt,
IGS_GE_DATE.igscharDT(acaiuh.rule_waived_dt),
cst_rule_waived_person_id, TO_CHAR(acaiuh.rule_waived_person_id),
cst_sup_unit_cd, acaiuh.sup_unit_cd,
cst_sup_uv_version_number, TO_CHAR(acaiuh.sup_uv_version_number))
IS NOT NULL
ORDER BY acaiuh.hist_start_dt;
SELECT DECODE (p_column_name,
cst_percentage, TO_CHAR(cfosh.percentage),
cst_major_field_ind, cfosh.major_field_ind)
FROM IGS_PS_FLD_STD_HIST cfosh
WHERE cfosh.course_cd = p_course_cd AND
cfosh.version_number = p_version_number AND
cfosh.field_of_study = p_field_of_study AND
cfosh.hist_start_dt >= p_hist_date AND
DECODE (p_column_name,
cst_percentage, TO_CHAR(cfosh.percentage),
cst_major_field_ind, cfosh.major_field_ind) IS NOT NULL
ORDER BY cfosh.hist_start_dt;
SELECT coh.percentage
FROM IGS_PS_OWN_HIST coh
WHERE coh.course_cd = p_course_cd AND
coh.version_number = p_version_number AND
coh.org_unit_cd = p_org_unit_cd AND
coh.ou_start_dt = p_ou_start_dt AND
coh.hist_start_dt >= p_hist_date AND
coh.percentage IS NOT NULL
ORDER BY coh.hist_start_dt;
SELECT crch.description
FROM IGS_PS_REF_CD_HIST crch
WHERE crch.course_cd = p_course_cd AND
crch.version_number = p_version_number AND
crch.reference_cd_type = p_reference_cd_type AND
crch.reference_cd = p_reference_cd AND
crch.hist_start_dt >= p_hist_date AND
crch.description IS NOT NULL
ORDER BY crch.hist_start_dt;
-- added the primary_auto_select column as part of Career impact dld, bug 2027984,pmarada
DECLARE
CURSOR c_cth IS
SELECT
DECODE (p_column_name,
'DESCRIPTION', cth.description,
'GOVT_COURSE_TYPE', TO_CHAR(cth.GOVT_COURSE_TYPE),
'COURSE_TYPE_GROUP_CD', cth.course_type_group_cd,
'TAC_COURSE_LEVEL', cth.tac_course_level,
'CLOSED_IND', cth.closed_ind,
'AWARD_COURSE_IND', cth.award_course_ind,
'RESEARCH_TYPE_IND', cth.research_type_ind,
'PRIMARY_AUTO_SELECT', cth.primary_auto_select,
'FIN_AID_PROGRAM_TYPE', cth.fin_aid_program_type)
FROM IGS_PS_TYPE_HIST cth
WHERE cth.course_type = p_course_type AND
cth.hist_start_dt >= p_hist_end_dt AND
DECODE (p_column_name,
'DESCRIPTION', cth.description,
'GOVT_COURSE_TYPE', TO_CHAR(cth.GOVT_COURSE_TYPE),
'COURSE_TYPE_GROUP_CD', cth.course_type_group_cd,
'TAC_COURSE_LEVEL', cth.tac_course_level,
'CLOSED_IND', cth.closed_ind,
'AWARD_COURSE_IND', cth.award_course_ind,
'RESEARCH_TYPE_IND', cth.research_type_ind,
'PRIMARY_AUTO_SELECT', cth.primary_auto_select,
'FIN_AID_PROGRAM_TYPE', cth.fin_aid_program_type) IS NOT NULL
ORDER BY
cth.hist_start_dt;