[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_ucas_interface_rec( p_app_no IN NUMBER) IS
/*************************************************************
Created By : rbezawad
Date Created By : 11-NOV-2002
Purpose : To delete Wrong Applicant records from UCAS Interface tables.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
rbezawad 16-Oct-03 Added logic to delete from interface (_INTS) tables w.r.t. ucfd209 build, bug: 2669228.
***************************************************************/
CURSOR cur_ucap IS
SELECT ROWID
FROM igs_uc_applicants
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_clearing
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_clr_rnd
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_results
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_stats
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_addreses
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_names
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_app_referees
WHERE app_no = p_app_no;
SELECT ROWID
FROM igs_uc_form_quals
WHERE app_no = p_app_no;
igs_uc_app_stats_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_results_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_clr_rnd_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_clearing_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_addreses_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_names_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_referees_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_form_quals_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_applicants_pkg.delete_row ( x_rowid => x.ROWID );
DELETE igs_uc_ifrmqul_ints WHERE appno = p_app_no;
DELETE igs_uc_iqual_ints WHERE appno = p_app_no;
DELETE igs_uc_irefrnc_ints WHERE appno = p_app_no;
DELETE igs_uc_istara_ints WHERE appno = p_app_no;
DELETE igs_uc_istarg_ints WHERE appno = p_app_no;
DELETE igs_uc_istarh_ints WHERE appno = p_app_no;
DELETE igs_uc_istarj_ints WHERE appno = p_app_no;
DELETE igs_uc_istark_ints WHERE appno = p_app_no;
DELETE igs_uc_istarn_ints WHERE appno = p_app_no;
DELETE igs_uc_istart_ints WHERE appno = p_app_no;
DELETE igs_uc_istarw_ints WHERE appno = p_app_no;
DELETE igs_uc_istarx_ints WHERE appno = p_app_no;
DELETE igs_uc_istarz1_ints WHERE appno = p_app_no;
DELETE igs_uc_istarz2_ints WHERE appno = p_app_no;
DELETE igs_uc_istmnt_ints WHERE appno = p_app_no;
DELETE igs_uc_istrpqr_ints WHERE appno = p_app_no;
fnd_message.set_token('NAME','igs_uc_expunge_app.delete_ucas_interface_rec'||' - '||SQLERRM);
END delete_ucas_interface_rec;
3) Delete the Wrong Application related data from UCAS interface tables.
4) If all wrong application data is successfully deleted from UCAS Interface tables then mark the applicants as
expunged by setting the flag in IGS_UC_WRONG_APP.EXPUNGED to 'Y'.
5) log the message in the log file for the each step whether the processing is succussful or not.
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 2-DEC-2002 Bug No: 2599109
Modified igs_pe_alt_pers_id_pkg signature to include REGION_CD
rbezawad 16-Oct-03 Modified logic to expunge at Application Choice level rather only at Application
level w.r.t. ucfd209 build, bug: 2669228.
ssaleem 09-FEB-05 Bug 3882788 - DELETION OF INVALID ALTERNATE PERSON IDS
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
(reverse chronological order - newest change first)
***************************************************************/
--To check whether the setup record is available FOR each System, which has an application to be expunged.
CURSOR cur_system_setup IS
SELECT 'X'
FROM igs_uc_defaults def,
igs_uc_applicants ucap
WHERE def.system_code (+) = ucap.system_code
AND def.system_code IS NULL
AND ucap.app_no IN ( SELECT app_no
FROM igs_uc_wrong_app wap
WHERE wap.expunge = 'Y'
AND wap.expunged <> 'Y'
AND wap.app_no = NVL(p_app_no, wap.app_no) );
SELECT 'X'
FROM igs_uc_defaults def
WHERE system_code IN ( SELECT DISTINCT system_code
FROM igs_uc_applicants ucap,
igs_uc_wrong_app wap
WHERE ucap.app_no = wap.app_no
AND wap.expunge = 'Y'
AND wap.expunged <> 'Y'
AND wap.app_no = NVL(p_app_no, wap.app_no) )
AND ( def.obsolete_outcome_status IS NULL OR def.decision_make_id IS NULL OR def.decision_reason_id IS NULL );
SELECT wap.ROWID row_id, wap.*
FROM igs_uc_wrong_app wap
WHERE wap.app_no = NVL(p_app_no, wap.app_no)
AND wap.expunge = 'Y'
AND wap.expunged <> 'Y'
ORDER BY wap.app_no;
SELECT obsolete_outcome_status,
decision_make_id,
decision_reason_id
FROM igs_uc_defaults def,
igs_uc_applicants ucap
WHERE def.system_code = ucap.system_code
AND ucap.app_no = cp_app_no;
SELECT aap.person_id,
aap.admission_appl_number,
aap.alt_appl_id,
aap.choice_number,
aap.acad_cal_type,
aap.acad_ci_sequence_number,
aap.adm_cal_type,
aap.adm_ci_sequence_number,
aap.admission_cat,
aap.s_admission_process_type,
apai.nominated_course_cd,
apai.crv_version_number,
apai.location_cd,
apai.attendance_mode,
apai.attendance_type,
apai.sequence_number,
apai.adm_outcome_status
FROM igs_ad_appl_all aap,
igs_ad_ps_appl_inst_all apai,
igs_ad_ou_stat aous
WHERE aap.alt_appl_id = TO_CHAR(cp_app_no)
AND aap.choice_number = NVL(cp_choice_no,aap.choice_number)
AND aap.person_id = apai.person_id
AND aap.admission_appl_number = apai. admission_appl_number
AND apai.adm_outcome_status = aous.adm_outcome_status
AND aous.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED')
ORDER BY aap.choice_number, aap.admission_appl_number, apai.preference_number;
SELECT igs_ad_interface_ctl_s.NEXTVAL
FROM dual ;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT api.ROWID row_id, api.*
FROM igs_pe_alt_pers_id api,
igs_uc_applicants ucap
WHERE api.pe_person_id = ucap.oss_person_id
AND api.api_person_id = TO_CHAR(ucap.app_no)
AND ucap.app_no = cp_app_no
AND api.person_id_type= DECODE(ucap.system_code, 'U', 'UCASID', 'G', 'GTTRID', 'S', 'SWASID', 'N', 'NMASID')
AND (api.end_dt IS NULL OR (api.end_dt > SYSDATE AND api.end_dt <> api.start_dt));
SELECT ROWID
FROM igs_uc_app_cho_cnds
WHERE app_no = cp_app_no
AND choice_no = cp_choice_no;
SELECT ROWID, choice_no
FROM igs_uc_app_choices
WHERE app_no = cp_app_no
AND choice_no = NVL(cp_choice_no, choice_no);
SELECT ROWID
FROM igs_uc_transactions
WHERE app_no = cp_app_no
AND choice_no = cp_choice_no;
l_ucas_app_recs_deleted VARCHAR2(1);
l_expunge_choice_det.DELETE;
igs_ad_batc_def_det_pkg.insert_row ( x_rowid => l_rowid,
x_batch_id => l_dec_batch_id,
x_description => fnd_message.get_string('IGS','IGS_UC_XPG_DEC_BATCH'),
x_acad_cal_type => l_oss_ad_appl_inst_rec.acad_cal_type,
x_acad_ci_sequence_number => l_oss_ad_appl_inst_rec.acad_ci_sequence_number,
x_adm_cal_type => l_oss_ad_appl_inst_rec.adm_cal_type,
x_adm_ci_sequence_number => l_oss_ad_appl_inst_rec.adm_ci_sequence_number,
x_admission_cat => l_oss_ad_appl_inst_rec.admission_cat,
x_s_admission_process_type => l_oss_ad_appl_inst_rec.s_admission_process_type,
x_decision_make_id => NULL,
x_decision_date => NULL,
x_decision_reason_id => NULL,
x_pending_reason_id => NULL,
x_offer_dt => NULL,
x_offer_response_dt => NULL,
x_mode => 'R' );
igs_ad_admde_int_pkg.insert_row ( x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id ,
x_batch_id => l_dec_batch_id,
x_person_id => l_oss_ad_appl_inst_rec.person_id,
x_admission_appl_number => l_oss_ad_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => l_oss_ad_appl_inst_rec.nominated_course_cd,
x_sequence_number => l_oss_ad_appl_inst_rec.sequence_number,
x_adm_outcome_status => l_defaults_rec.obsolete_outcome_status,
x_decision_make_id => l_defaults_rec.decision_make_id,
x_decision_date => SYSDATE,
x_decision_reason_id => l_defaults_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => NULL,
x_offer_response_dt => NULL,
x_status => '2', -- pending status
x_error_code => NULL,
x_mode => 'R' );
igs_uc_app_cho_cnds_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_app_choices_pkg.delete_row ( x_rowid => x.ROWID );
igs_uc_transactions_pkg.delete_row ( x_rowid => x.ROWID );
DELETE igs_uc_istarc_ints
WHERE appno = l_wrong_app_rec.app_no
AND choiceno = l_expunge_choice_det(l_loc);
DELETE igs_uc_ioffer_ints
WHERE appno = l_wrong_app_rec.app_no
AND choiceno = l_expunge_choice_det(l_loc);
igs_pe_alt_pers_id_pkg.Update_Row ( x_mode => 'R',
x_rowid => l_alt_pers_id_rec.row_id,
x_pe_person_id => l_alt_pers_id_rec.pe_person_id,
x_api_person_id => l_alt_pers_id_rec.api_person_id,
x_api_person_id_uf => l_alt_pers_id_rec.api_person_id_uf,
x_person_id_type => l_alt_pers_id_rec.person_id_type,
x_start_dt => l_alt_pers_id_rec.start_dt,
x_end_dt => SYSDATE ,
x_attribute_category => l_alt_pers_id_rec.attribute_category,
x_attribute1 => l_alt_pers_id_rec.attribute1,
x_attribute2 => l_alt_pers_id_rec.attribute2,
x_attribute3 => l_alt_pers_id_rec.attribute3,
x_attribute4 => l_alt_pers_id_rec.attribute4,
x_attribute5 => l_alt_pers_id_rec.attribute5,
x_attribute6 => l_alt_pers_id_rec.attribute6,
x_attribute7 => l_alt_pers_id_rec.attribute7,
x_attribute8 => l_alt_pers_id_rec.attribute8,
x_attribute9 => l_alt_pers_id_rec.attribute9,
x_attribute10 => l_alt_pers_id_rec.attribute10,
x_attribute11 => l_alt_pers_id_rec.attribute11,
x_attribute12 => l_alt_pers_id_rec.attribute12,
x_attribute13 => l_alt_pers_id_rec.attribute13,
x_attribute14 => l_alt_pers_id_rec.attribute14,
x_attribute15 => l_alt_pers_id_rec.attribute15,
x_attribute16 => l_alt_pers_id_rec.attribute16,
x_attribute17 => l_alt_pers_id_rec.attribute17,
x_attribute18 => l_alt_pers_id_rec.attribute18,
x_attribute19 => l_alt_pers_id_rec.attribute19,
x_attribute20 => l_alt_pers_id_rec.attribute20,
x_region_cd => l_alt_pers_id_rec.region_cd);
l_ucas_app_recs_deleted := 'N';
l_ucas_app_recs_deleted := 'Y';
delete_ucas_interface_rec(l_wrong_app_rec.app_no);
l_ucas_app_recs_deleted := 'N';
IF ( l_ucas_app_expunged = 'Y' AND l_pe_alt_pers_id_closed = 'Y' AND l_ucas_app_recs_deleted = 'Y' ) OR
( l_ucas_app_expunged = 'N' AND l_all_makred_app_inst_expunged = 'Y' ) THEN
BEGIN
igs_uc_wrong_app_pkg.update_row ( x_mode => 'R',
x_rowid => l_wrong_app_rec.row_id,
x_wrong_app_id => l_wrong_app_rec.wrong_app_id,
x_app_no => l_wrong_app_rec.app_no,
x_miscoded => l_wrong_app_rec.miscoded,
x_cancelled => l_wrong_app_rec.cancelled,
x_cancel_date => l_wrong_app_rec.cancel_date,
x_remark => l_wrong_app_rec.remark,
x_expunge => l_wrong_app_rec.expunge,
x_batch_id => l_wrong_app_rec.batch_id,
x_expunged => 'Y',
x_joint_admission_ind => l_wrong_app_rec.joint_admission_ind,
x_choice1_lost => l_wrong_app_rec.choice1_lost,
x_choice2_lost => l_wrong_app_rec.choice2_lost,
x_choice3_lost => l_wrong_app_rec.choice3_lost,
x_choice4_lost => l_wrong_app_rec.choice4_lost,
x_choice5_lost => l_wrong_app_rec.choice5_lost,
x_choice6_lost => l_wrong_app_rec.choice6_lost,
x_choice7_lost => l_wrong_app_rec.choice7_lost);