[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- This module inserts research IGS_RE_CDT_FLD_OF_SY details as a result of
--course transfer. All Candidature research supervisor details
--(p_person_id/p_transfer_ca_sequence_number) are to be copied to the
--new Candidature (p_person_id/p_ca_sequence_number).
DECLARE
v_cafos_exists VARCHAR2(1);
SELECT 'x'
FROM IGS_RE_CDT_FLD_OF_SY cafos
WHERE cafos.person_id = p_person_id AND
cafos.ca_sequence_number = p_ca_sequence_number;
SELECT cafos.field_of_study,
cafos.percentage
FROM IGS_RE_CDT_FLD_OF_SY cafos
WHERE cafos.person_id = p_person_id AND
cafos.ca_sequence_number = p_transfer_ca_sequence_number;
--Insert Candidature field of study details
FOR v_cafos1_rec IN c_cafos1 LOOP
BEGIN
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_CDT_FLD_OF_SY_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_field_of_study => v_cafos1_rec.field_of_study,
x_percentage => v_cafos1_rec.percentage );
--This module inserts research IGS_RE_CDT_ATT_HIST details as a result of
--course transfer. All Candidature research supervisor details
--(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
--Candidature (p_person_id/p_ca_sequence_number).
DECLARE
cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
SELECT 'x'
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.sequence_number = p_ca_sequence_number;
SELECT cah.sequence_number,
cah.hist_start_dt,
cah.hist_end_dt,
cah.attendance_type,
cah.attendance_percentage
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.ca_sequence_number = p_transfer_ca_sequence_number;
--Insert Candidature attendance histories
FOR v_cah1_rec IN c_cah1 LOOP
BEGIN
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_org_id => l_org_id,
X_person_id => p_person_id,
X_ca_sequence_number => p_ca_sequence_number,
X_sequence_number => v_cah1_rec.sequence_number,
X_hist_start_dt => v_cah1_rec.hist_start_dt ,
X_hist_end_dt => v_cah1_rec.hist_end_dt,
X_attendance_type => v_cah1_rec.attendance_type,
X_attendance_percentage => v_cah1_rec.attendance_percentage);
--This module inserts research IGS_RE_CAND_SEO_CLS details as a result of IGS_PS_COURSE
-- transfer. All Candidature research supervisor details
--(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
--Candidature (p_person_id/p_ca_sequence_number).
DECLARE
v_csc_exists VARCHAR2(1);
SELECT 'x'
FROM IGS_RE_CAND_SEO_CLS csc
WHERE csc.person_id = p_person_id AND
csc.ca_sequence_number = p_ca_sequence_number;
SELECT csc.seo_class_cd,
csc.percentage
FROM IGS_RE_CAND_SEO_CLS csc
WHERE csc.person_id = p_person_id AND
csc.ca_sequence_number = p_transfer_ca_sequence_number;
--Insert Candidature socio-economic classification details
FOR v_csc1_rec IN c_csc1 LOOP
BEGIN
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_CAND_SEO_CLS_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_seo_class_cd => v_csc1_rec.seo_class_cd,
x_percentage =>v_csc1_rec.percentage );
--This module inserts research milestone details as a result of COURSE
--transfer. All Candidature milestone details
--(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
--Candidature (p_person_id/p_ca_sequence_number).
DECLARE
v_mil_exists VARCHAR2(1);
SELECT 'x'
FROM IGS_PR_MILESTONE mil
WHERE mil.person_id = p_person_id AND
mil.sequence_number = p_ca_sequence_number;
SELECT mil.sequence_number,
mil.milestone_type,
mil.milestone_status,
mil.due_dt,
mil.description,
mil.actual_reached_dt,
mil.preced_sequence_number,
mil.ovrd_ntfctn_imminent_days,
mil.ovrd_ntfctn_reminder_days,
mil.ovrd_ntfctn_re_reminder_days,
mil.comments
FROM IGS_PR_MILESTONE mil
WHERE mil.person_id = p_person_id AND
mil.ca_sequence_number = p_transfer_ca_sequence_number
ORDER BY mil.due_dt;
--Insert milestone
BEGIN --Insert IGS_PR_MILESTONE
FOR v_mil1_rec IN c_mil1 LOOP
DECLARE
l_rowid VARCHAR2(25);
IGS_PR_MILESTONE_PKG.INSERT_ROW(
x_rowid => l_rowid,
X_org_id => l_org_id,
x_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_sequence_number => v_mil1_rec.sequence_number,
x_milestone_type => v_mil1_rec.milestone_type,
x_milestone_status => v_mil1_rec.milestone_status,
x_due_dt => v_mil1_rec.due_dt,
x_description => v_mil1_rec.description,
x_actual_reached_dt => v_mil1_rec.actual_reached_dt,
x_preced_sequence_number => v_mil1_rec.preced_sequence_number,
x_ovrd_ntfctn_imminent_days => v_mil1_rec.ovrd_ntfctn_imminent_days,
x_ovrd_ntfctn_reminder_days => v_mil1_rec.ovrd_ntfctn_reminder_days,
x_ovrd_ntfctn_re_reminder_days => v_mil1_rec.ovrd_ntfctn_re_reminder_days,
x_comments => v_mil1_rec.comments );
p_message_name := 'IGS_RE_CANT_INSERT_MILESTONES';
SELECT 'x'
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = p_ca_sequence_number;
SELECT rsup.person_id,
rsup.sequence_number,
rsup.start_dt,
rsup.end_dt,
rsup.research_supervisor_type,
rsup.supervisor_profession,
rsup.supervision_percentage,
rsup.funding_percentage,
rsup.org_unit_cd,
rsup.ou_start_dt,
rsup.replaced_person_id,
rsup.replaced_sequence_number,
rsup.comments
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = p_transfer_ca_sequence_number
ORDER BY rsup.start_dt;
--Insert IGS_RE_SPRVSR details
FOR v_rsup1_rec IN c_rsup1 LOOP
BEGIN
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_SPRVSR_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_ca_person_id => p_person_id,
x_ca_sequence_number =>p_ca_sequence_number ,
x_person_id => v_rsup1_rec.person_id,
x_sequence_number => v_rsup1_rec.sequence_number,
x_start_dt => v_rsup1_rec.start_dt,
x_end_dt => v_rsup1_rec.end_dt,
x_research_supervisor_type => v_rsup1_rec.research_supervisor_type,
x_supervisor_profession => v_rsup1_rec.supervisor_profession,
x_supervision_percentage => v_rsup1_rec.supervision_percentage,
x_funding_percentage => v_rsup1_rec.funding_percentage,
x_org_unit_cd => v_rsup1_rec.org_unit_cd,
x_ou_start_dt => v_rsup1_rec.ou_start_dt ,
x_replaced_person_id => v_rsup1_rec.replaced_person_id,
x_replaced_sequence_number => v_rsup1_rec.replaced_sequence_number,
x_comments => v_rsup1_rec.comments);
--This module inserts research IGS_RE_SCHOLARSHIP details as a
--result of course transfer.
DECLARE
cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
SELECT 'x'
FROM IGS_RE_SCHOLARSHIP sch
WHERE sch.person_id = p_person_id AND
sch.ca_sequence_number = p_ca_sequence_number;
SELECT sch.scholarship_type,
sch.start_dt,
sch.end_dt,
sch.dollar_value,
sch.description,
sch.other_benefits,
sch.conditions
FROM IGS_RE_SCHOLARSHIP sch
WHERE sch.person_id = p_person_id AND
sch.ca_sequence_number = p_transfer_ca_sequence_number
ORDER BY sch.start_dt;
BEGIN --Insert IGS_RE_SCHOLARSHIP details
FOR v_sch1_rec IN c_sch1 LOOP
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_SCHOLARSHIP_PKG.INSERT_ROW(
x_rowid => l_rowid,
X_org_id => l_org_id,
x_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_scholarship_type => v_sch1_rec.scholarship_type,
x_start_dt => v_sch1_rec.start_dt,
x_end_dt => v_sch1_rec.end_dt,
x_dollar_value => v_sch1_rec.dollar_value,
x_description => v_sch1_rec.description,
x_other_benefits => v_sch1_rec.other_benefits,
x_conditions => v_sch1_rec.conditions );
p_message_name :='IGS_RE_CANT_INSERT_SCHOLARSHI';
--This module inserts research THESIS details as a result of COURSE transfer.
--All Candidature THESIS details (p_person_id/p_transfer_ca_sequence_number)
--are to be copied to the new Candidature (p_person_id/p_ca_sequence_number).
DECLARE
v_return_val BOOLEAN;
SELECT IGS_RE_THESIS_SEQ_NUM_S.nextval
FROM dual;
SELECT 'x'
FROM IGS_RE_THESIS thes
WHERE thes.person_id = p_person_id AND
thes.ca_sequence_number = p_ca_sequence_number;
SELECT thes.sequence_number,
thes.title,
thes.final_title_ind,
thes.short_title,
thes.abbreviated_title,
thes.thesis_result_cd,
thes.expected_submission_dt,
thes.library_lodgement_dt,
thes.library_catalogue_number,
thes.embargo_expiry_dt,
thes.thesis_format,
thes.embargo_details,
thes.thesis_topic,
thes.citation,
thes.comments
FROM IGS_RE_THESIS thes
WHERE thes.person_id = p_person_id AND
thes.ca_sequence_number = p_transfer_ca_sequence_number AND
thes.logical_delete_dt IS NULL;
SELECT tex.the_sequence_number,
tex.creation_dt,
tex.submission_dt,
tex.thesis_exam_type,
tex.thesis_panel_type,
tex.tracking_id,
tex.thesis_result_cd
FROM IGS_RE_THESIS_EXAM tex
WHERE tex.person_id = p_person_id AND
tex.ca_sequence_number = p_transfer_ca_sequence_number AND
tex.the_sequence_number = cp_the_sequence_number
ORDER BY tex.creation_dt ASC;
SELECT teh.hist_start_dt,
teh.hist_end_dt,
teh.thesis_result_cd
FROM IGS_RE_THS_EXAM_HIST teh
WHERE teh.person_id = p_person_id AND
teh.ca_sequence_number = p_transfer_ca_sequence_number AND
teh.the_sequence_number = cp_tex_the_sequence_number AND
teh.creation_dt = cp_tex_creation_dt AND
teh.thesis_result_cd IS NOT NULL
ORDER BY teh.hist_end_dt DESC;
SELECT tpm.the_sequence_number,
tpm.creation_dt,
tpm.person_id,
tpm.panel_member_type,
tpm.confirmed_dt,
tpm.declined_dt,
tpm.anonymity_ind,
tpm.thesis_result_cd,
tpm.paid_dt,
tpm.tracking_id,
tpm.recommendation_summary
FROM IGS_RE_THS_PNL_MBR tpm
WHERE tpm.ca_person_id = p_person_id AND
tpm.ca_sequence_number = p_transfer_ca_sequence_number AND
tpm.the_sequence_number = cp_tex_the_sequence_number AND
tpm.creation_dt = cp_tex_creation_dt;
SELECT tpmh.hist_start_dt,
tpmh.hist_end_dt,
tpmh.thesis_result_cd
FROM IGS_RE_THS_PNL_MR_HS tpmh
WHERE tpmh.ca_person_id = p_person_id AND
tpmh.ca_sequence_number = p_transfer_ca_sequence_number AND
tpmh.the_sequence_number = cp_the_sequence_number AND
tpmh.creation_dt = cp_tpm_creation_dt AND
tpmh.person_id = cp_tpm_person_id AND
tpmh.thesis_result_cd IS NOT NULL
ORDER BY tpmh.hist_end_dt DESC;
--Insert IGS_RE_THESIS
FOR v_the1_rec IN c_the2 LOOP
BEGIN
OPEN c_next_val;
IGS_RE_THESIS_PKG.INSERT_ROW(
x_rowid => l_rowid,
X_org_id => l_org_id,
x_person_id =>p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_sequence_number => v_the_sequence_number,
x_title=> v_the1_rec.title,
x_final_title_ind => v_the1_rec.final_title_ind,
x_short_title => v_the1_rec.short_title,
x_abbreviated_title => v_the1_rec.abbreviated_title,
x_thesis_result_cd => v_the1_rec.thesis_result_cd,
x_expected_submission_dt => v_the1_rec.expected_submission_dt,
x_library_lodgement_dt => v_the1_rec.library_lodgement_dt,
x_library_catalogue_number => v_the1_rec.library_catalogue_number,
x_embargo_expiry_dt => v_the1_rec.embargo_expiry_dt,
x_thesis_format => v_the1_rec.thesis_format,
x_embargo_details => v_the1_rec.embargo_details,
x_thesis_topic => v_the1_rec.thesis_topic,
x_citation => v_the1_rec.citation,
x_comments => v_the1_rec.comments,
x_logical_delete_dt => null);
--Insert IGS_RE_THESIS exam(s)
FOR v_tex1_rec IN c_tex(
v_the1_rec.sequence_number) LOOP
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_THESIS_EXAM_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_the_sequence_number => v_the_sequence_number,
x_creation_dt => v_tex1_rec.creation_dt,
x_submission_dt => v_tex1_rec.submission_dt,
x_thesis_exam_type => v_tex1_rec.thesis_exam_type,
x_thesis_panel_type => v_tex1_rec.thesis_panel_type,
x_tracking_id => v_tex1_rec.tracking_id,
x_thesis_result_cd => v_tex1_rec.thesis_result_cd);
--Insert result code IGS_RE_THESIS exam history if required
--Note: IGS_RE_THESIS result code date(see RESF3700) is derived from audit details
--And must be retained with the transfer.
IF v_tex1_rec.thesis_result_cd IS NOT NULL THEN
OPEN c_teh(
v_tex1_rec.the_sequence_number,
v_tex1_rec.creation_dt);
IGS_RE_THS_EXAM_HIST_PKG.INSERT_ROW (
x_rowid => l_rowid,
X_org_id => l_org_id,
x_person_id => p_person_id ,
x_ca_sequence_number => p_ca_sequence_number,
x_the_sequence_number => v_the_sequence_number,
x_creation_dt => v_tex1_rec.creation_dt,
x_hist_start_dt => v_teh1_rec.hist_start_dt,
x_hist_end_dt => v_teh1_rec.hist_end_dt,
x_hist_who => v_user,
x_submission_dt => NULL,
x_thesis_exam_type => NULL,
x_thesis_panel_type => NULL,
x_tracking_id => NULL,
x_thesis_result_cd => v_teh1_rec.thesis_result_cd );
--Insert IGS_RE_THESIS panel member(s)
FOR v_tpm1_rec IN c_tpm(
v_tex1_rec.the_sequence_number,
v_tex1_rec.creation_dt) LOOP
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_THS_PNL_MBR_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_ca_person_id => p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_the_sequence_number => v_the_sequence_number,
x_creation_dt => v_tpm1_rec.creation_dt,
x_person_id => v_tpm1_rec.person_id,
x_panel_member_type => v_tpm1_rec.panel_member_type,
x_confirmed_dt => v_tpm1_rec.confirmed_dt,
x_declined_dt => v_tpm1_rec.declined_dt,
x_anonymity_ind => v_tpm1_rec.anonymity_ind,
x_thesis_result_cd => v_tpm1_rec.thesis_result_cd,
x_paid_dt => v_tpm1_rec.paid_dt,
x_tracking_id => v_tpm1_rec.tracking_id,
x_recommendation_summary => v_tpm1_rec.recommendation_summary);
--Insert result code IGS_RE_THESIS panel member history if required
--Note: IGS_RE_THESIS result code date ( see RESF3700) is derived from audit details
--And must be retained with the transfer.
IF v_tpm1_rec.thesis_result_cd IS NOT NULL THEN
OPEN c_tpmh(
v_tpm1_rec.the_sequence_number,
v_tpm1_rec.creation_dt,
v_tpm1_rec.person_id);
IGS_RE_THS_PNL_MR_HS_PKG.INSERT_ROW (
x_rowid => l_rowid,
X_org_id => l_org_id,
x_ca_person_id=> p_person_id,
x_ca_sequence_number => p_ca_sequence_number,
x_the_sequence_number => v_the_sequence_number,
x_creation_dt => v_tpm1_rec.creation_dt,
x_person_id => v_tpm1_rec.person_id,
x_hist_start_dt => v_tpmh1_rec.hist_start_dt,
x_hist_end_dt => v_tpmh1_rec.hist_end_dt,
x_hist_who => v_user,
x_panel_member_type => NULL,
x_confirmed_dt => NULL,
x_declined_dt => NULL,
x_anonymity_ind => 'N',
x_thesis_result_cd => v_tpm1_rec.thesis_result_cd,
x_paid_dt => NULL,
x_tracking_id => NULL,
x_recommendation_summary => NULL);
p_message_name := 'IGS_RE_CANT_INSERT_THESIS_DET';
SELECT ca.attendance_percentage,
ca.govt_type_of_activity_cd,
ca.research_topic,
ca.industry_links,
ca.sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sca_course_cd = p_transfer_course_cd;
SELECT 'x'
FROM IGS_RE_CANDIDATURE ca_to
WHERE ca_to.person_id = p_person_id AND
(ca_to.sca_course_cd = p_sca_course_cd AND
p_parent = cst_ca_to_sca) OR
(ca_to.acai_admission_appl_number = p_acai_admission_appl_number AND
ca_to.acai_nominated_course_cd = p_acai_nominated_course_cd AND
ca_to.acai_sequence_number = p_acai_sequence_number AND
p_parent = cst_ca_to_acai);
SELECT IGS_RE_CANDIDATURE_SEQ_NUM_S.nextval
FROM dual;
-- Inserts a record into the s_disable_table_trigger
-- database table.
DECLARE
l_rowid VARCHAR2(25);
IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
X_ROWID => L_ROWID ,
X_TABLE_NAME =>cst_enrp_ins_ca_trnsfr,
X_SESSION_ID => userenv('SESSIONID'),
x_mode => 'R'
);
--Insert Candidature
BEGIN
DECLARE
l_rowid VARCHAR2(25);
IGS_RE_CANDIDATURE_PKG.INSERT_ROW(
x_rowid => l_rowid,
X_org_id => l_org_id,
x_person_id => p_person_id,
x_sequence_number => v_ca_sequence_number,
x_sca_course_cd => p_sca_course_cd,
x_acai_admission_appl_number => p_acai_admission_appl_number,
x_acai_nominated_course_cd => p_acai_nominated_course_cd,
x_acai_sequence_number => p_acai_sequence_number,
x_attendance_percentage => v_attendance_percentage,
x_govt_type_of_activity_cd => v_ca_govt_type_of_activity_cd,
x_max_submission_dt => NULL,
x_min_submission_dt => NULL,
x_research_topic => v_ca_research_topic,
x_industry_links => v_ca_industry_links );
--Insert Candidature attendance history(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_cah_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;
--Insert Candidature field of study(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_cafostrnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;
--Insert Candidature socio-economic classification code(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_csc_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;
--Insert Candidature Thesis(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_the_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;
--Insert Candidature research supervisor(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_rsup_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
Rollback to sp_candidature;
--Insert Candidature milestone(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_mil_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;
--Insert Candidature research scholarship(s)
IF IGS_EN_INS_CA_TRNSFR.enrp_ins_sch_trnsfr(
p_person_id,
v_ca_sequence_number,
v_sequence_number,
v_message_name) = FALSE THEN
ROLLBACK TO sp_candidature;