1 PACKAGE BODY IGS_AD_GEN_001 AS
2 /* $Header: IGSAD01B.pls 120.3 2005/09/22 05:26:17 appldev ship $ */
3 Function Admp_Del_Aa_Hist(
4 p_person_id IN NUMBER ,
5 p_admission_appl_number IN NUMBER ,
6 p_message_name OUT NOCOPY VARCHAR2 )
7 RETURN BOOLEAN IS
8 BEGIN -- admp_del_aa_hist
9 -- Removes the history record/s from IGS_AD_APPL by calling another a
10 -- sub-function.
11 -- If true is returned then we will know that the record either not there or
12 -- has been deleted.
13 -- If false is returned then we know that the record or table is locked.
14 -- Another check needs
15 -- to be made to see whether the values that have been passed actually exist in
16 -- the db. If they don't,
17 -- then return true, otherwise return false with the knowledge that the table
18 -- or record is locked.
19 DECLARE
20 CURSOR c_aah_sel (
21 cp_person_id IGS_AD_APPL_HIST.person_id%TYPE,
22 cp_admission_appl_number IGS_AD_APPL_HIST.admission_appl_number%TYPE) IS
23 SELECT person_id
24 FROM IGS_AD_APPL_HIST
25 WHERE person_id = cp_person_id
26 AND admission_appl_number = cp_admission_appl_number;
27 v_aah_sel_rec c_aah_sel%ROWTYPE;
28 FUNCTION admpl_del_if_not_locked(
29 p_adinl_person_id IN IGS_AD_APPL_HIST.person_id%TYPE,
30 p_adinl_admission_appl_number IN IGS_AD_APPL_HIST.admission_appl_number%TYPE)
31 RETURN
32 BOOLEAN
33 IS
34 e_resource_busy_exception EXCEPTION;
35 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
36 BEGIN -- admpl_del_if_not_locked
37 -- This function will simply return false if the IGS_AD_APPL_HIST table or
38 -- rows are locked. Otherwise, it will delete the appropriate records from the
39 -- table and return true.
40 DECLARE
41 CURSOR c_aah (
42 cp_adinl_person_id IGS_AD_APPL_HIST.person_id%TYPE,
43 cp_adinl_admission_appl_number
44 IGS_AD_APPL_HIST.admission_appl_number%TYPE) IS
45 SELECT rowid, aah.*
46 FROM IGS_AD_APPL_HIST aah
47 WHERE person_id = cp_adinl_person_id AND
48 admission_appl_number = cp_adinl_admission_appl_number
49 FOR UPDATE OF person_id NOWAIT;
50 BEGIN
51 FOR v_aah_rec IN c_aah (
52 p_adinl_person_id,
53 p_adinl_admission_appl_number)
54 LOOP
55 IGS_AD_APPL_HIST_PKG.DELETE_ROW (
56 X_ROWID => v_aah_rec.rowid );
57 END LOOP;
58 RETURN TRUE;
59 END;
60 EXCEPTION
61 WHEN e_resource_busy_exception THEN
62 RETURN FALSE;
63 WHEN OTHERS THEN
64 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
65 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_if_not_locked');
66 IGS_GE_MSG_STACK.ADD;
67 App_Exception.Raise_Exception;
68 END admpl_del_if_not_locked;
69 BEGIN
70 p_message_name := null;
71 IF admpl_del_if_not_locked (
72 p_person_id,
73 p_admission_appl_number)= FALSE THEN
74 OPEN c_aah_sel(
75 p_person_id,
76 p_admission_appl_number);
77 FETCH c_aah_sel INTO v_aah_sel_rec;
78 IF c_aah_sel%FOUND THEN
79 CLOSE c_aah_sel;
80 p_message_name := 'IGS_AD_NODEL_ADMAPPL_RECORD';
81 RETURN FALSE;
82 END IF;
83 CLOSE c_aah_sel;
84 END IF;
85 RETURN TRUE;
86 END;
87 EXCEPTION
88 WHEN OTHERS THEN
89 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
90 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_aa_hist');
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END admp_del_aa_hist;
94
95 Function Admp_Del_Acaiu_Hist(
96 p_person_id IN NUMBER ,
97 p_admission_appl_number IN NUMBER ,
98 p_nominated_course_cd IN VARCHAR2 ,
99 p_acai_sequence_number IN NUMBER ,
100 p_unit_cd IN VARCHAR2 ,
101 p_message_name OUT NOCOPY VARCHAR2 )
102 RETURN BOOLEAN IS
103 BEGIN -- admp_del_acaiu_hist
104 -- Routine to remove the history for an IGS_AD_PS_APLINSTUNT.
105 DECLARE
106 e_resource_busy_exception EXCEPTION;
107 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
108 CURSOR c_acaiuh IS
109 SELECT rowid,acaiuh.*
110 FROM IGS_AD_PS_APINTUNTHS acaiuh
111 WHERE acaiuh.person_id = p_person_id AND
112 acaiuh.admission_appl_number = p_admission_appl_number AND
113 acaiuh.nominated_course_cd = p_nominated_course_cd AND
114 acaiuh.acai_sequence_number = p_acai_sequence_number AND
115 acaiuh.unit_cd = p_unit_cd
116 FOR UPDATE OF acaiuh.person_id NOWAIT;
117 BEGIN
118 -- Set default value
119 p_message_name := null;
120 FOR v_acaiuh_rec IN c_acaiuh LOOP
121
122 IGS_AD_PS_APINTUNTHS_PKG.DELETE_ROW (
123 X_ROWID => v_acaiuh_rec.rowid );
124
125 END LOOP;
126 RETURN TRUE;
127 EXCEPTION
128 WHEN e_resource_busy_exception THEN
129 -- Close unlclosed local cursor
130 IF c_acaiuh%ISOPEN THEN
131 CLOSE c_acaiuh;
132 END IF;
133 -- Set error message number
134 p_message_name := 'IGS_AD_UNABLE_TO_DELETE';
135 RETURN FALSE;
136 WHEN OTHERS THEN
137 IF c_acaiuh%ISOPEN THEN
138 CLOSE c_acaiuh;
139 END IF;
140 App_Exception.Raise_Exception;
141 END;
142 EXCEPTION
143 WHEN OTHERS THEN
144 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
145 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_acaiu_hist');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END admp_del_acaiu_hist;
149
150 Function Admp_Del_Acai_Hist(
151 p_person_id IN NUMBER ,
152 p_admission_appl_number IN NUMBER ,
153 p_nominated_course_cd IN VARCHAR2 ,
154 p_sequence_number IN NUMBER ,
155 p_message_name OUT NOCOPY VARCHAR2 )
156 RETURN BOOLEAN IS
157 BEGIN -- admp_del_acai_hist
158 -- Deletes records from IGS_AD_PS_APLINSTHST table
159 DECLARE
160 FUNCTION admp_del_if_not_locked(
161 p_person_id IGS_AD_PS_APLINSTHST.person_id%TYPE,
162 p_admission_appl_number
163 IGS_AD_PS_APLINSTHST.admission_appl_number%TYPE,
164 p_nominated_course_cd IGS_AD_PS_APLINSTHST.nominated_course_cd%TYPE,
165 p_sequence_number IGS_AD_PS_APLINSTHST.sequence_number%TYPE)
166 RETURN BOOLEAN IS
167 e_resource_busy_exception EXCEPTION;
168 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
169 BEGIN -- admp_del_if_not_locked
170 -- This function will return false if the IGS_AD_PS_APLINSTHST table
171 -- rows are locked. Otherwise, it will delete the appropriate records from the
172 -- table and return true.
173 DECLARE
174 CURSOR c_acaih (
175 cp_person_id IGS_AD_PS_APLINSTHST.person_id%TYPE,
176 cp_admission_appl_number
177 IGS_AD_PS_APLINSTHST.admission_appl_number%TYPE,
178 cp_nominated_course_cd
179 IGS_AD_PS_APLINSTHST.nominated_course_cd%TYPE,
180 cp_sequence_number IGS_AD_PS_APLINSTHST.sequence_number%TYPE) IS
181
182 SELECT rowid, acaih.*
183 FROM IGS_AD_PS_APLINSTHST acaih
184 WHERE acaih.person_id = cp_person_id AND
185 acaih.admission_appl_number = cp_admission_appl_number AND
186 acaih.nominated_course_cd = cp_nominated_course_cd AND
187 acaih.sequence_number = cp_sequence_number
188 FOR UPDATE OF acaih.person_id NOWAIT;
189 BEGIN
190 FOR v_del_acaih_rec IN c_acaih (
191 p_person_id,
192 p_admission_appl_number,
193 p_nominated_course_cd,
194 p_sequence_number) LOOP
195
196 IGS_AD_PS_APLINSTHST_PKG.DELETE_ROW (
197 X_ROWID => v_del_acaih_rec.rowid );
198
199 END LOOP;
200 RETURN TRUE;
201 END;
202 EXCEPTION
203 WHEN e_resource_busy_exception THEN
204 RETURN FALSE;
205 WHEN OTHERS THEN
206 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
207 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_if_not_locked');
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 END admp_del_if_not_locked;
211 BEGIN
212 p_message_name := null;
213 IF(admp_del_if_not_locked (
214 p_person_id,
215 p_admission_appl_number,
216 p_nominated_course_cd,
217 p_sequence_number)= FALSE) THEN
218 p_message_name := 'IGS_AD_UNABLEDEL_ADMPRG_APPL';
219 RETURN FALSE;
220 END IF;
221 RETURN TRUE;
222 END;
223 EXCEPTION
224 WHEN OTHERS THEN
225 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
226 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_acai_hist');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END admp_del_acai_hist;
230
231 Function Admp_Del_Aca_Hist(
232 p_person_id IN NUMBER ,
233 p_admission_appl_number IN NUMBER ,
234 p_nominated_course_cd IN VARCHAR2 ,
235 p_message_name OUT NOCOPY VARCHAR2 )
236 RETURN BOOLEAN IS
237 BEGIN -- admp_del_aca_hist
238 -- Deletes records from IGS_AD_PS_APPL_HIST table
239 DECLARE
240 FUNCTION admp_del_if_not_locked(
241 p_person_id IGS_AD_PS_APPL_HIST.person_id%TYPE,
242 p_admission_appl_number IGS_AD_PS_APPL_HIST.admission_appl_number%TYPE,
243 p_nominated_course_cd IGS_AD_PS_APPL_HIST.nominated_course_cd%TYPE)
244 RETURN BOOLEAN IS
245 e_resource_busy_exception EXCEPTION;
246 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
247 BEGIN
248 -- admp_del_if_not_locked
249 -- This function will return false if the IGS_AD_PS_APPL_HIST table
250 -- rows are locked. Otherwise, it will delete the appropriate records from the
251 -- table and return true.
252 DECLARE
253 CURSOR c_acah (
254 cp_person_id IGS_AD_PS_APPL_HIST.person_id%TYPE,
255 cp_admission_appl_number IGS_AD_PS_APPL_HIST.admission_appl_number%TYPE,
256 cp_nominated_course_cd IGS_AD_PS_APPL_HIST.nominated_course_cd%TYPE) IS
257 SELECT ROWID, acah.*
258 FROM IGS_AD_PS_APPL_HIST acah
259 WHERE acah.person_id = cp_person_id AND
260 acah.admission_appl_number = cp_admission_appl_number AND
261 acah.nominated_course_cd = cp_nominated_course_cd
262 FOR UPDATE OF acah.person_id NOWAIT;
263 BEGIN
264 FOR v_del_acah_rec IN c_acah (
265 p_person_id,
266 p_admission_appl_number,
267 p_nominated_course_cd) LOOP
268
269 IGS_AD_PS_APPL_HIST_PKG.DELETE_ROW (
270 X_ROWID => v_del_acah_rec.rowid );
271
272 END LOOP;
273 RETURN TRUE;
274 END;
275 EXCEPTION
276 WHEN e_resource_busy_exception THEN
277 RETURN FALSE;
278 WHEN OTHERS THEN
279 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
280 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_if_not_locked');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END admp_del_if_not_locked;
284 BEGIN
285 p_message_name := null;
286 IF(admp_del_if_not_locked (
287 p_person_id,
288 p_admission_appl_number,
289 p_nominated_course_cd)= FALSE) THEN
290 p_message_name := 'IGS_AD_UNABLE_DEL_ADMPRG_APPL';
291 RETURN FALSE;
292 END IF;
293 RETURN TRUE;
294 END;
295 EXCEPTION
296 WHEN OTHERS THEN
297 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
298 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_aca_hist');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END admp_del_aca_hist;
302
303 --removed the Function Admp_Del_Eap_Cepi (bug 2664699) rghosh
304
305 --removed Function Admp_Del_Eap_Eitpi for IGR Migration (bug 4114493) sjlaport
306
307 --removed the function Admp_Del_Eap_Eltpi (bug 2664699) rghosh
308
309 --------------------------------------------------------------------------------
310 -- ADMPL_DEL_INSERT_LOG_ENTRY is called from ADMP_DEL_SCA_UNCONF ---------------
311 --------------------------------------------------------------------------------
312 PROCEDURE admpl_del_ins_log_entry (
313 p_message_name VARCHAR2 ,
314 p_default_msg_txt VARCHAR2 ,
315 p_sca_deleted_ind VARCHAR2,
316 p_log_creation_dt DATE,
317 p_key VARCHAR2,
318 p_s_log_type VARCHAR2)
319 IS
320 PRAGMA AUTONOMOUS_TRANSACTION;
321 BEGIN -- admpl_del_ins_log_entry
322 -- Create a log entry
323 BEGIN
324 IGS_GE_GEN_003.genp_ins_log_entry(
325 p_s_log_type,
326 p_log_creation_dt,
327 p_sca_deleted_ind || '|' || p_key,
328 p_message_name,
329 p_default_msg_txt);
330 END;
331 COMMIT;
332 EXCEPTION
333 WHEN OTHERS THEN
334 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
335 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_ins_log_entry');
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END admpl_del_ins_log_entry;
339
340 Procedure Admp_Del_Sca_Unconf(
341 p_log_creation_dt OUT NOCOPY DATE )
342 IS
343 BEGIN -- admp_del_sca_unconf
344 -- This module deletes unconfirmed student course attempts that
345 -- were created as a result of an admission course application
346 -- offer that was never accepted. This process will be run
347 -- nightly by the Job Scheduler.
348 -- Records are deleted from the following IGS_EN_STDNT_PS_ATT
349 -- child tables
350 -- Student IGS_PS_UNIT Attempt (and histories)
351 -- Student IGS_PS_UNIT Set Attempt
352 -- Student IGS_PS_COURSE HECS Option
353 -- Student IGS_PS_COURSE Attempt Enrolment
354 -- Student IGS_PS_COURSE Attempt Notes
355 -- Advanced Standing (and child tables
356 -- IGS_RE_CANDIDATURE (by breaking the SCA parent link
357 -- Fee Assessment (by reversing the fee assessment)
358 -- Contract Fee Assessment Rates
359 -- Records on all other student IGS_PS_COURSE child tables are to be
360 -- processed as IGS_GE_EXCEPTIONS
361 DECLARE
362 e_resource_busy EXCEPTION;
363 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
364 e_savepoint_lost EXCEPTION;
365 PRAGMA EXCEPTION_INIT(e_savepoint_lost, -1086);
366
367 cst_enrolment CONSTANT VARCHAR2(10) := 'ENROLMENT';
368 cst_admission CONSTANT VARCHAR2(10) := 'ADMISSION';
369 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
370 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
371 cst_voided CONSTANT VARCHAR2(10) := 'VOIDED';
372 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
373 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
374 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
375 cst_del_un_sca CONSTANT VARCHAR2(10) := 'DEL-UN-SCA';
376 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
377 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
378
379 v_process_next BOOLEAN DEFAULT FALSE;
380 v_error_number NUMBER DEFAULT NULL;
381 v_error_flag BOOLEAN DEFAULT FALSE;
382 v_constraint VARCHAR2(40) DEFAULT NULL;
383 v_default_msg VARCHAR2(300) DEFAULT NULL;
384 v_message_name VARCHAR2(30);
385 v_message_num NUMBER;
386 v_key VARCHAR2(255) DEFAULT NULL;
387 v_log_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
388 v_delete_sca_ind VARCHAR2(1);
389 v_record_locked BOOLEAN;
390 v_hist_record_locked BOOLEAN;
391 v_fee_ass_log_creation_dt IGS_GE_S_LOG.creation_dt%TYPE DEFAULT NULL;
392 l_msg_at_index NUMBER;
393 l_entity_name VARCHAR2(30);
394
395 CURSOR c_cir IS
396 SELECT cir.sup_cal_type,
397 cir.sup_ci_sequence_number,
398 daiv.cal_type,
399 daiv.ci_sequence_number
400 FROM IGS_CA_INST_REL cir,
401 IGS_CA_DA_INST_V daiv,
402 IGS_CA_TYPE ct,
403 IGS_CA_TYPE ct2,
404 IGS_EN_CAL_CONF secc
405 WHERE secc.s_control_num = 1 AND
406 TRUNC(daiv.alias_val) = TRUNC(SYSDATE) AND
407 daiv.dt_alias = secc.enr_cleanup_dt_alias AND
408 ct.cal_type = daiv.cal_type AND
409 ct.s_cal_cat = cst_enrolment AND
410 cir.sub_cal_type = daiv.cal_type AND
411 cir.sub_ci_sequence_number = daiv.ci_sequence_number AND
412 ct2.cal_type = cir.sup_cal_type AND
413 ct2.s_cal_cat = cst_admission;
414
415 CURSOR c_sca (
416 cp_cal_type IGS_CA_INST.cal_type%TYPE,
417 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
418 SELECT sca.person_id,
419 sca.course_cd,
420 sca.course_attempt_status,
421 sca.fee_cat,
422 sca.adm_admission_appl_number,
423 sca.adm_nominated_course_cd,
424 sca.adm_sequence_number,
425 acaiv.admission_appl_number,
426 acaiv.nominated_course_cd,
427 acaiv.sequence_number,
428 aa.acad_cal_type,
429 aa.acad_ci_sequence_number,
430 acaiv.adm_cal_type,
431 acaiv.adm_ci_sequence_number,
432 aa.admission_cat,
433 aa.s_admission_process_type,
434 aors.s_adm_offer_resp_status,
435 aods.s_adm_offer_dfrmnt_status
436 FROM igs_en_stdnt_ps_att sca,
437 igs_ad_ps_appl_inst acaiv,
438 igs_ad_appl aa,
439 igs_ad_ou_stat aos,
440 igs_ad_ofr_resp_stat aors,
441 igs_ad_ofrdfrmt_stat aods
442 WHERE sca.course_attempt_status = cst_unconfirm AND
443 sca.person_id = acaiv.person_id AND
444 sca.adm_admission_appl_number = acaiv.admission_appl_number AND
445 sca.adm_nominated_course_cd = acaiv.nominated_course_cd AND
446 sca.adm_sequence_number = acaiv.sequence_number AND
447 acaiv.adm_cal_type = cp_cal_type AND
448 acaiv.adm_ci_sequence_number = cp_sequence_number AND
449 aa.person_id = acaiv.person_id AND
450 aa.admission_appl_number = acaiv.admission_appl_number AND
451 aos.adm_outcome_status = acaiv.adm_outcome_status AND
452 aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
453 aods.adm_offer_dfrmnt_status = acaiv.adm_offer_dfrmnt_status AND
454 (aos.s_adm_outcome_status IN (
455 cst_withdrawn,
456 cst_voided,
457 cst_rejected) OR
458 aors.s_adm_offer_resp_status IN (
459 cst_rejected,
460 cst_lapsed,
461 cst_deferral));
462
463 CURSOR c_term (cp_person_id igs_en_spa_terms.person_id%TYPE,
464 cp_course_cd igs_en_spa_terms.program_cd%TYPE) IS
465 SELECT sterm.person_id,sterm.program_cd
466 FROM IGS_EN_SPA_TERMS sterm
467 WHERE sterm.person_id = cp_person_id
468 AND sterm.program_cd = cp_course_cd;
469
470 l_term c_term%ROWTYPE;
471
472 CURSOR c_sca_upd(
473 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
474 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
475 SELECT ROWID, sca.*
476 FROM IGS_EN_STDNT_PS_ATT sca
477 WHERE sca.person_id = cp_person_id AND
478 sca.course_cd = cp_course_cd
479 FOR UPDATE OF
480 sca.LAST_UPDATE_DATE NOWAIT;
481 v_sca_upd_exists c_sca_upd%ROWTYPE;
482
483 --------------------------------------------------------------------------------
484 -- EXTRACT_MSG_FROM_STACK ---------------------------------------------------------------
485 --Who When What
486 --------------------------------------------------------------------------------
487 PROCEDURE extract_msg_from_stack (p_msg_at_index NUMBER)
488 IS
489 l_old_msg_count NUMBER;
490 l_new_msg_count NUMBER;
491 l_msg_inc_factr NUMBER := 1;
492 l_msg_idx_start NUMBER;
493 l_msg_txt fnd_new_messages.message_text%TYPE;
494 l_app_nme varchar2(1000);
495 l_msg_nme varchar2(2000);
496 BEGIN
497 l_old_msg_count := p_msg_at_index;
498 l_new_msg_count := igs_ge_msg_stack.count_msg;
499
500 WHILE (l_new_msg_count - l_old_msg_count) > 0
501 LOOP
502 igs_ge_msg_stack.get(l_old_msg_count+l_msg_inc_factr,'T',l_msg_txt,l_msg_idx_start);
503
504 igs_ge_msg_stack.delete_msg(l_msg_idx_start);
505 l_new_msg_count := l_new_msg_count -1;
506
507 fnd_message.parse_encoded (l_msg_txt, l_app_nme, l_msg_nme);
508 fnd_message.set_encoded (l_msg_txt);
509 l_msg_txt := fnd_message.get;
510
511 IF l_msg_txt IS NOT NULL THEN
512 admpl_del_ins_log_entry (
513 p_message_name => l_msg_nme,
514 p_default_msg_txt => l_msg_txt,
515 p_sca_deleted_ind => 'N',
516 p_log_creation_dt => v_log_creation_dt,
517 p_key => v_key,
518 p_s_log_type => cst_del_un_sca);
519 END IF;
520 END LOOP;
521
522 IF l_msg_txt IS NULL AND SQLCODE <> 0 THEN
523 l_msg_txt := SQLERRM;
524 admpl_del_ins_log_entry (
525 p_message_name => l_msg_nme,
526 p_default_msg_txt => l_msg_txt,
527 p_sca_deleted_ind => 'N',
528 p_log_creation_dt => v_log_creation_dt,
529 p_key => v_key,
530 p_s_log_type => cst_del_un_sca);
531 END IF;
532 END extract_msg_from_stack;
533 --------------------------------------------------------------------------------
534 -- ADMPL_DEL_SUA ---------------------------------------------------------------
535 --Who When What
536 --knaraset 29-Apr-03 Modified cursors to have uoo_id reference as part of MUS build bug 2829262
537 --------------------------------------------------------------------------------
538 FUNCTION admpl_del_sua(
539 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
540 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
541 RETURN BOOLEAN
542 IS
543 BEGIN -- admpl_del_sua
544 -- (1) Delete IGS_EN_SU_ATTEMPT records
545 DECLARE
546 CURSOR c_sua IS
547 SELECT uoo_id
548 FROM IGS_EN_SU_ATTEMPT sua
549 WHERE sua.person_id = p_person_id AND
550 sua.course_cd = p_course_cd;
551 CURSOR c_sua_del (
552 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
553 SELECT row_id
554 FROM IGS_EN_SU_ATTEMPT sua
555 WHERE sua.person_id = p_person_id AND
556 sua.course_cd = p_course_cd AND
557 sua.uoo_id = cp_uoo_id
558 FOR UPDATE OF
559 sua.LAST_UPDATE_DATE NOWAIT;
560 v_sua_del_exists c_sua_del%ROWTYPE;
561
562
563 BEGIN
564 v_error_flag := FALSE;
565 FOR v_sua_rec IN c_sua LOOP
566
567 -- Delete unconfirmed IGS_EN_SU_ATTEMPT
568 FOR v_sua_del_exists IN c_sua_del(v_sua_rec.uoo_id) LOOP
569 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW(
570 X_ROWID => v_sua_del_exists.row_id );
571 END LOOP;
572
573 END LOOP;
574 IF v_error_flag THEN
575 RETURN FALSE;
576 END IF;
577
578 RETURN TRUE;
579 EXCEPTION
580 WHEN e_resource_busy THEN
581 IF c_sua%ISOPEN THEN
582 CLOSE c_sua;
583 END IF;
584 IF c_sua_del%ISOPEN THEN
585 CLOSE c_sua_del;
586 END IF;
587 l_entity_name := 'IGS_EN_SU_ATTEMPT_ALL';
588 RETURN FALSE;
589 WHEN OTHERS THEN
590 IF c_sua%ISOPEN THEN
591 CLOSE c_sua;
592 END IF;
593 IF c_sua_del%ISOPEN THEN
594 CLOSE c_sua_del;
595 END IF;
596 App_Exception.Raise_Exception;
597 END;
598 EXCEPTION
599 WHEN OTHERS THEN
600 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
601 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_sua');
602 IGS_GE_MSG_STACK.ADD;
603 App_Exception.Raise_Exception;
604 END admpl_del_sua;
605
606 --------------------------------------------------------------------------------
607 -- ADMPL_DEL_SUAH ---------------------------------------------------------------
608 --Who When What
609 --------------------------------------------------------------------------------
610 FUNCTION admpl_del_suah(
611 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
612 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
613 RETURN BOOLEAN
614 IS
615 BEGIN -- admpl_del_suah
616 -- Delete IGS_EN_SU_ATTEMPT_H records
617 DECLARE
618 CURSOR c_suah IS
619 SELECT suah.uoo_id,
620 suah.hist_start_dt
621 FROM IGS_EN_SU_ATTEMPT_H suah
622 WHERE suah.person_id = p_person_id AND
623 suah.course_cd = p_course_cd ;
624
625
626 CURSOR c_suah_del (
627 cp_uoo_id IGS_EN_SU_ATTEMPT_H.uoo_id%TYPE,
628 cp_hist_start_dt IGS_EN_SU_ATTEMPT_H.hist_start_dt%TYPE) IS
629 SELECT row_id
630 FROM IGS_EN_SU_ATTEMPT_H suah
631 WHERE suah.person_id = p_person_id AND
632 suah.course_cd = p_course_cd AND
633 suah.uoo_id = cp_uoo_id AND
634 suah.hist_start_dt = cp_hist_start_dt
635 FOR UPDATE OF
636 suah.LAST_UPDATE_DATE NOWAIT;
637 v_suah_del_exists c_suah_del%ROWTYPE;
638
639
640 BEGIN
641
642 v_error_flag := FALSE;
643
644 FOR v_suah_rec IN c_suah LOOP
645 -- Delete IGS_EN_SU_ATTEMPT_H records
646 FOR v_suah_del_exists IN c_suah_del(v_suah_rec.uoo_id, v_suah_rec.hist_start_dt) LOOP
647
648 IGS_EN_SU_ATTEMPT_H_PKG.DELETE_ROW(
649 X_ROWID => v_suah_del_exists.ROW_ID );
650 END LOOP;
651 END LOOP;
652
653 -- Return the default value
654 RETURN TRUE;
655 EXCEPTION
656 WHEN e_resource_busy THEN
657 IF c_suah%ISOPEN THEN
658 CLOSE c_suah;
659 END IF;
660 IF c_suah_del%ISOPEN THEN
661 CLOSE c_suah_del;
662 END IF;
663 l_entity_name := 'IGS_EN_SU_ATTEMPT_H';
664 RETURN FALSE;
665 WHEN OTHERS THEN
666 IF c_suah%ISOPEN THEN
667 CLOSE c_suah;
668 END IF;
669 IF c_suah_del%ISOPEN THEN
670 CLOSE c_suah_del;
671 END IF;
672 App_Exception.Raise_Exception;
673 END;
674 EXCEPTION
675 WHEN OTHERS THEN
676 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
677 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_suah');
678 IGS_GE_MSG_STACK.ADD;
679 App_Exception.Raise_Exception;
680 END admpl_del_suah;
681
682 --------------------------------------------------------------------------------
683 -- ADMPL_DEL_SUSA --------------------------------------------------------------
684 --------------------------------------------------------------------------------
685 FUNCTION admpl_del_susa(
686 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
687 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
688 RETURN BOOLEAN
689 IS
690 BEGIN -- admpl_del_susa
691 -- (2) Delete IGS_AS_SU_SETATMPT records
692 DECLARE
693 CURSOR c_susa IS
694 SELECT
695 susa.unit_set_cd,
696 susa.sequence_number
697 FROM IGS_AS_SU_SETATMPT susa
698 WHERE susa.person_id = p_person_id AND
699 susa.course_cd = p_course_cd
700 START WITH
701 susa.person_id = p_person_id AND
702 susa.course_cd = p_course_cd AND
703 susa.parent_unit_set_cd IS NULL
704 CONNECT BY
705 PRIOR susa.person_id = p_person_id AND
706 PRIOR susa.course_cd = p_course_cd AND
707 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
708 PRIOR susa.sequence_number = susa.parent_sequence_number
709 ORDER BY LEVEL DESC;
710 CURSOR c_susa_del (
711 cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
712 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
713 SELECT ROWID, susa.*
714 FROM IGS_AS_SU_SETATMPT susa
715 WHERE susa.person_id = p_person_id AND
716 susa.course_cd = p_course_cd AND
717 susa.unit_set_cd = cp_unit_set_cd AND
718 susa.sequence_number = cp_sequence_number
719 FOR UPDATE OF
720 susa.LAST_UPDATE_DATE NOWAIT;
721 v_susa_del_exists c_susa_del%ROWTYPE;
722
723
724 CURSOR c_hes ( cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
725 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
726 SELECT hesa_en_susa_id
727 FROM IGS_HE_EN_SUSA hes
728 WHERE hes.person_id = p_person_id AND
729 hes.course_cd = p_course_cd AND
730 hes.unit_set_cd = cp_unit_set_cd AND
731 hes.sequence_number = cp_sequence_number;
732
733 CURSOR c_hes_del (
734 cp_hesa_en_susa_id IGS_HE_EN_SUSA.hesa_en_susa_id%TYPE) IS
735 SELECT rowid
736 FROM IGS_HE_EN_SUSA hes
737 WHERE hes.hesa_en_susa_id = cp_hesa_en_susa_id
738 FOR UPDATE OF hes.LAST_UPDATE_DATE NOWAIT ;
739
740 CURSOR c_hesc ( cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
741 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
742 SELECT he_susa_cc_id
743 FROM IGS_HE_EN_SUSA_CC hesc
744 WHERE hesc.person_id = p_person_id AND
745 hesc.course_cd = p_course_cd AND
746 hesc.unit_set_cd = cp_unit_set_cd AND
747 hesc.sequence_number = cp_sequence_number;
748
749 CURSOR c_hesc_del (
750 cp_he_susa_cc_id IGS_HE_EN_SUSA_CC.he_susa_cc_id%TYPE) IS
751 SELECT rowid
752 FROM IGS_HE_EN_SUSA_CC hesc
753 WHERE hesc.he_susa_cc_id = cp_he_susa_cc_id
754 FOR UPDATE OF hesc.LAST_UPDATE_DATE NOWAIT ;
755
756 L_ROWID VARCHAR2(25);
757 BEGIN
758 v_error_flag := FALSE;
759
760 -- Prevent admission application validation in database trigger
761 -- Inserts a record into the s_disable_table_trigger
762 -- database table.
763 IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
764 X_ROWID => L_ROWID ,
765 X_TABLE_NAME =>'ADMP_DEL_SCA_UNCONF',
766 X_SESSION_ID => userenv('SESSIONID'),
767 x_mode => 'R'
768 );
769
770 FOR v_susa_rec IN c_susa LOOP
771
772 FOR v_hes_rec IN c_hes (v_susa_rec.unit_set_cd, v_susa_rec.sequence_number )
773 LOOP
774 BEGIN
775 -- Delete unconfirmed IGS_HE_EN_SUSA records
776 FOR v_hes_del_rec in c_hes_del(
777 v_hes_rec.hesa_en_susa_id) LOOP
778
779 IGS_HE_EN_SUSA_PKG.DELETE_ROW(
780 X_ROWID => v_hes_del_rec.ROWID );
781
782 END LOOP;
783 EXCEPTION
784 WHEN e_resource_busy THEN
785 IF c_hes_del%ISOPEN THEN
786 CLOSE c_hes_del;
787 END IF;
788 l_entity_name := 'IGS_HE_EN_SUSA';
789 EXIT;
790 END;
791
792 END LOOP;
793
794 FOR v_hesc_rec IN c_hesc (v_susa_rec.unit_set_cd, v_susa_rec.sequence_number )
795 LOOP
796 BEGIN
797 -- Delete unconfirmed IGS_HE_EN_SUSA_CC records
798 FOR v_hesc_del_rec in c_hesc_del(
799 v_hesc_rec.he_susa_cc_id) LOOP
800
801 IGS_HE_EN_SUSA_CC_PKG.DELETE_ROW(
802 X_ROWID => v_hesc_del_rec.ROWID );
803
804 END LOOP;
805 EXCEPTION
806 WHEN e_resource_busy THEN
807 IF c_hesc_del%ISOPEN THEN
808 CLOSE c_hesc_del;
809 END IF;
810 l_entity_name := 'IGS_HE_EN_SUSA_CC';
811 EXIT;
812 END;
813
814 END LOOP;
815
816 IF l_entity_name IS NOT NULL THEN
817 EXIT;
818 END IF;
819
820 -- Delete unconfirmed IGS_AS_SU_SETATMPT
821 FOR v_susa_del_exists IN c_susa_del(
822 v_susa_rec.unit_set_cd,
823 v_susa_rec.sequence_number) LOOP
824 IGS_AS_SU_SETATMPT_PKG.DELETE_ROW (
825 X_ROWID => V_SUSA_DEL_EXISTS.ROWID );
826 END LOOP;
827
828 END LOOP;
829 IF v_error_flag THEN
830 -- Must reset database trigger validation if been turned off
831 IGS_GE_MNT_SDTT.genp_del_sdtt(
832 'ADMP_DEL_SCA_UNCONF');
833 RETURN FALSE;
834 END IF;
835 -- Must reset database trigger validation if been turned off
836 IGS_GE_MNT_SDTT.genp_del_sdtt(
837 'ADMP_DEL_SCA_UNCONF');
838
839 IF l_entity_name IS NOT NULL THEN
840 RETURN FALSE;
841 END IF;
842
843 RETURN TRUE;
844 EXCEPTION
845 WHEN e_resource_busy THEN
846 IF c_susa%ISOPEN THEN
847 CLOSE c_susa;
848 END IF;
849 IF c_susa_del%ISOPEN THEN
850 CLOSE c_susa_del;
851 END IF;
852 IF c_hes%ISOPEN THEN
853 CLOSE c_hes;
854 END IF;
855 IF c_hes_del%ISOPEN THEN
856 CLOSE c_hes_del;
857 END IF;
858 IF c_hesc%ISOPEN THEN
859 CLOSE c_hesc;
860 END IF;
861 IF c_hesc_del%ISOPEN THEN
862 CLOSE c_hesc_del;
863 END IF;
864 -- Must reset database trigger validation if been turned off
865 IGS_GE_MNT_SDTT.genp_del_sdtt(
866 'ADMP_DEL_SCA_UNCONF');
867 l_entity_name := 'IGS_AS_SU_SETATMPT';
868 RETURN FALSE;
869 WHEN OTHERS THEN
870 IF c_susa%ISOPEN THEN
871 CLOSE c_susa;
872 END IF;
873 IF c_susa_del%ISOPEN THEN
874 CLOSE c_susa_del;
875 END IF;
876 IF c_hes%ISOPEN THEN
877 CLOSE c_hes;
878 END IF;
879 IF c_hes_del%ISOPEN THEN
880 CLOSE c_hes_del;
881 END IF;
882 IF c_hesc%ISOPEN THEN
883 CLOSE c_hesc;
884 END IF;
885 IF c_hesc_del%ISOPEN THEN
886 CLOSE c_hesc_del;
887 END IF;
888 -- Must reset database trigger validation if been turned off
889 IGS_GE_MNT_SDTT.genp_del_sdtt(
890 'ADMP_DEL_SCA_UNCONF');
891 App_Exception.Raise_Exception;
892 END;
893 EXCEPTION
894 WHEN OTHERS THEN
895 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
896 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_susa');
897 IGS_GE_MSG_STACK.ADD;
898 App_Exception.Raise_Exception;
899 END admpl_del_susa;
900 --------------------------------------------------------------------------------
901 -- ADMPL_DEL_SCHO --------------------------------------------------------------
902 --------------------------------------------------------------------------------
903 FUNCTION admpl_del_scho(
904 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
905 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
906 RETURN BOOLEAN
907 IS
908 BEGIN -- admpl_del_scho
909 -- (3) Delete IGS_EN_STDNTPSHECSOP records
910 DECLARE
911 CURSOR c_scho IS
912 SELECT scho.start_dt
913 FROM IGS_EN_STDNTPSHECSOP scho
914 WHERE scho.person_id = p_person_id AND
915 scho.course_cd = p_course_cd;
916 CURSOR c_scho_del (
917 cp_start_dt IGS_EN_STDNTPSHECSOP.start_dt%TYPE) IS
918 SELECT ROWID, scho.*
919 FROM IGS_EN_STDNTPSHECSOP scho
920 WHERE scho.person_id = p_person_id AND
921 scho.course_cd = p_course_cd AND
922 scho.start_dt = cp_start_dt
923 FOR UPDATE OF
924 scho.LAST_UPDATE_DATE NOWAIT;
925 v_scho_del_exists c_scho_del%ROWTYPE;
926 BEGIN
927 v_error_flag := FALSE;
928 FOR v_scho_rec IN c_scho LOOP
929
930 -- Delete unconfirmed IGS_EN_STDNTPSHECSOP
931 FOR v_scho_del_exists IN c_scho_del(v_scho_rec.start_dt) LOOP
932 IGS_EN_STDNTPSHECSOP_PKG.DELETE_ROW (
933 X_ROWID => V_SCHO_DEL_EXISTS.ROWID );
934 END LOOP;
935
936 END LOOP;
937 IF v_error_flag THEN
938 RETURN FALSE;
939 END IF;
940
941 RETURN TRUE;
942 EXCEPTION
943 WHEN e_resource_busy THEN
944 IF c_scho%ISOPEN THEN
945 CLOSE c_scho;
946 END IF;
947 IF c_scho_del%ISOPEN THEN
948 CLOSE c_scho_del;
949 END IF;
950 l_entity_name := 'IGS_EN_STDNTPSHECSOP';
951 RETURN FALSE;
952 WHEN OTHERS THEN
953 IF c_scho%ISOPEN THEN
954 CLOSE c_scho;
955 END IF;
956 IF c_scho_del%ISOPEN THEN
957 CLOSE c_scho_del;
958 END IF;
959 App_Exception.Raise_Exception;
960 END;
961 EXCEPTION
962 WHEN OTHERS THEN
963 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
964 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_scho');
965 IGS_GE_MSG_STACK.ADD;
966 App_Exception.Raise_Exception;
967 END admpl_del_scho;
968 --------------------------------------------------------------------------------
969 -- ADMPL_DEL_SCAE --------------------------------------------------------------
970 --------------------------------------------------------------------------------
971 FUNCTION admpl_del_scae(
972 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
973 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
974 RETURN BOOLEAN IS
975 BEGIN -- admpl_del_scae
976 -- (4) Delete IGS_AS_SC_ATMPT_ENR scae
977 DECLARE
978 CURSOR c_scae IS
979 SELECT ROWID, scae.*
980 FROM IGS_AS_SC_ATMPT_ENR scae
981 WHERE scae.person_id = p_person_id AND
982 scae.course_cd = p_course_cd
983 FOR UPDATE OF scae.LAST_UPDATE_DATE NOWAIT;
984 BEGIN
985 FOR v_scae_rec IN c_scae
986 LOOP
987 IGS_AS_SC_ATMPT_ENR_PKG.DELETE_ROW(v_scae_rec.rowid);
988 END LOOP;
989
990 RETURN TRUE;
991 EXCEPTION
992 WHEN e_resource_busy THEN
993 IF c_scae%ISOPEN THEN
994 CLOSE c_scae;
995 END IF;
996 l_entity_name := 'IGS_AS_SC_ATMPT_ENR';
997 RETURN FALSE;
998 WHEN OTHERS THEN
999 IF c_scae%ISOPEN THEN
1000 CLOSE c_scae;
1001 END IF;
1002 App_Exception.Raise_Exception;
1003 END;
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1007 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_scae');
1008 IGS_GE_MSG_STACK.ADD;
1009 App_Exception.Raise_Exception;
1010 END admpl_del_scae;
1011 --------------------------------------------------------------------------------
1012 -- ADMPL_DEL_SCAN --------------------------------------------------------------
1013 --------------------------------------------------------------------------------
1014 FUNCTION admpl_del_scan(
1015 p_person_id IGS_AS_SC_ATMPT_NOTE.person_id%TYPE,
1016 p_course_cd IGS_AS_SC_ATMPT_NOTE.course_cd%TYPE)
1017 RETURN BOOLEAN
1018 IS
1019 BEGIN -- admpl_del_scan
1020 -- Delete student IGS_PS_COURSE attempt notes (5)
1021 DECLARE
1022 CURSOR c_scan IS
1023 SELECT ROWID, scan.*
1024 FROM IGS_AS_SC_ATMPT_NOTE scan
1025 WHERE scan.person_id = p_person_id AND
1026 scan.course_cd = p_course_cd
1027 FOR UPDATE OF scan.reference_number NOWAIT;
1028 BEGIN
1029 FOR v_scan_rec IN c_scan LOOP
1030 -- Call RI check routine for the IGS_AS_SC_ATMPT_NOTE table
1031 IGS_AS_SC_ATMPT_NOTE_PKG.DELETE_ROW(v_scan_rec.rowid);
1032 END LOOP;
1033
1034 RETURN TRUE;
1035 EXCEPTION
1036 WHEN e_resource_busy THEN
1037 IF c_scan%ISOPEN THEN
1038 CLOSE c_scan;
1039 END IF;
1040 l_entity_name := 'IGS_AS_SC_ATMPT_NOTE';
1041 RETURN FALSE;
1042 WHEN OTHERS THEN
1043 IF c_scan%ISOPEN THEN
1044 CLOSE c_scan;
1045 END IF;
1046 App_Exception.Raise_Exception;
1047 END;
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1051 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_scan');
1052 IGS_GE_MSG_STACK.ADD;
1053 App_Exception.Raise_Exception;
1054 END admpl_del_scan;
1055 --------------------------------------------------------------------------------
1056 -- ADMPL_CHK_AS ----------------------------------------------------------------
1057 --------------------------------------------------------------------------------
1058 FUNCTION admpl_chk_as(
1059 p_person_id IGS_AV_ADV_STANDING.person_id%TYPE,
1060 p_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE,
1061 p_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE,
1062 p_s_adm_offer_dfrmnt_status
1063 IGS_AD_OFRDFRMT_STAT.s_adm_offer_dfrmnt_status%TYPE)
1064 RETURN BOOLEAN
1065 IS
1066 BEGIN -- admpl_chk_as
1067 -- Advanced Standing record is NOT to be deleted since
1068 -- its parents are person and program-version and
1069 -- NOT program attempt
1070 DECLARE
1071 BEGIN
1072 v_message_name := NULL;
1073 v_default_msg := NULL;
1074 -- Do not clean up program attempt record for admission program application
1075 -- instances that have an approved advanced standing.
1076 IF NOT (p_s_adm_offer_resp_status = cst_deferral AND
1077 p_s_adm_offer_dfrmnt_status = cst_approved) THEN
1078 IF NOT IGS_AV_GEN_001.advp_del_adv_stnd(
1079 p_person_id,
1080 p_course_cd,
1081 v_message_name,
1082 v_default_msg) THEN
1083 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1084 IGS_GE_MSG_STACK.ADD;
1085 RETURN FALSE;
1086 END IF;
1087 END IF;
1088 RETURN TRUE;
1089 END;
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1093 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_chk_as');
1094 IGS_GE_MSG_STACK.ADD;
1095 App_Exception.Raise_Exception;
1096 END admpl_chk_as;
1097 --------------------------------------------------------------------------------
1098 -- ADMPL_UPD_RE_CANDIDATURE ------------------------------------------------------------
1099 ----
1100 --------------------------------------------------------------------------------
1101 FUNCTION admpl_upd_re_candidature(
1102 p_person_id IGS_AV_ADV_STANDING.person_id%TYPE,
1103 p_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE,
1104 p_adm_admission_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE,
1105 p_adm_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE,
1106 p_adm_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE)
1107 RETURN BOOLEAN
1108 IS
1109 BEGIN -- admpl_upd_re_candidature
1110 -- Process IGS_RE_CANDIDATURE
1111 DECLARE
1112 CURSOR c_ca IS
1113 SELECT rowid, ca.*
1114 FROM IGS_RE_CANDIDATURE ca
1115 WHERE ca.person_id = p_person_id AND
1116 ca.sca_course_cd = p_course_cd AND
1117 ca.acai_admission_appl_number = p_adm_admission_appl_number AND
1118 ca.acai_nominated_course_cd = p_adm_nominated_course_cd AND
1119 ca.acai_sequence_number = p_adm_sequence_number
1120 FOR UPDATE OF ca.sca_course_cd NOWAIT;
1121 BEGIN
1122 FOR v_ca_rec IN c_ca LOOP
1123
1124 IGS_RE_CANDIDATURE_PKG.UPDATE_ROW(
1125 X_ROWID => V_CA_REC.ROWID,
1126 X_PERSON_ID => V_CA_REC.PERSON_ID,
1127 X_SEQUENCE_NUMBER => V_CA_REC.SEQUENCE_NUMBER,
1128 X_SCA_COURSE_CD => NULL,
1129 X_ACAI_ADMISSION_APPL_NUMBER => V_CA_REC.ACAI_ADMISSION_APPL_NUMBER,
1130 X_ACAI_NOMINATED_COURSE_CD => V_CA_REC.ACAI_NOMINATED_COURSE_CD,
1131 X_ACAI_SEQUENCE_NUMBER => V_CA_REC.ACAI_SEQUENCE_NUMBER,
1132 X_ATTENDANCE_PERCENTAGE => V_CA_REC.ATTENDANCE_PERCENTAGE,
1133 X_GOVT_TYPE_OF_ACTIVITY_CD => V_CA_REC.GOVT_TYPE_OF_ACTIVITY_CD,
1134 X_MAX_SUBMISSION_DT => V_CA_REC.MAX_SUBMISSION_DT,
1135 X_MIN_SUBMISSION_DT => V_CA_REC.MIN_SUBMISSION_DT,
1136 X_RESEARCH_TOPIC => V_CA_REC.RESEARCH_TOPIC,
1137 X_INDUSTRY_LINKS => V_CA_REC.INDUSTRY_LINKS );
1138
1139 END LOOP;
1140 RETURN TRUE;
1141 EXCEPTION
1142 WHEN e_resource_busy THEN
1143 IF c_ca%ISOPEN THEN
1144 CLOSE c_ca;
1145 END IF;
1146 l_entity_name := 'IGS_RE_CANDIDATURE_ALL';
1147 RETURN FALSE;
1148 WHEN OTHERS THEN
1149 IF c_ca%ISOPEN THEN
1150 CLOSE c_ca;
1151 END IF;
1152 App_Exception.Raise_Exception;
1153 END;
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1157 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_upd_re_candidature');
1158 IGS_GE_MSG_STACK.ADD;
1159 App_Exception.Raise_Exception;
1160 END admpl_upd_re_candidature;
1161
1162 --------------------------------------------------------------------------------
1163 -- ADMPL_DEL_GUA ---------------------------------------------------------------
1164 --Who When What
1165 --------------------------------------------------------------------------------
1166 FUNCTION admpl_del_gua(
1167 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1168 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1169 RETURN BOOLEAN
1170 IS
1171 BEGIN -- admpl_del_gua
1172 -- Delete IGS_GR_GRADUAND_PKG records
1173 DECLARE
1174 CURSOR c_gua IS
1175 SELECT create_dt
1176 FROM IGS_GR_GRADUAND_ALL gua
1177 WHERE gua.person_id = p_person_id AND
1178 gua.course_cd = p_course_cd;
1179 CURSOR c_gua_del (
1180 cp_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE) IS
1181 SELECT rowid
1182 FROM IGS_GR_GRADUAND_ALL gua
1183 WHERE gua.person_id = p_person_id AND
1184 gua.create_dt = cp_create_dt
1185 FOR UPDATE OF gua.LAST_UPDATE_DATE NOWAIT ;
1186
1187 v_gua_del_exists c_gua_del%ROWTYPE;
1188
1189 CURSOR c_gach (
1190 cp_create_dt IGS_GR_AWD_CRMN_HIST.create_dt%TYPE) IS
1191 SELECT gach.gach_id
1192 FROM IGS_GR_AWD_CRMN_HIST gach
1193 WHERE gach.person_id = p_person_id AND
1194 gach.create_dt = cp_create_dt;
1195 CURSOR c_gach_del (
1196 cp_gach_id IGS_GR_AWD_CRMN_HIST.gach_id%TYPE) IS
1197 SELECT rowid
1198 FROM IGS_GR_AWD_CRMN_HIST gach
1199 WHERE gach.gach_id = cp_gach_id
1200 FOR UPDATE OF gach.LAST_UPDATE_DATE NOWAIT ;
1201
1202
1203
1204 CURSOR c_gac (
1205 cp_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE) IS
1206 SELECT gac.gac_id
1207 FROM IGS_GR_AWD_CRMN gac
1208 WHERE gac.person_id = p_person_id AND
1209 gac.create_dt = cp_create_dt;
1210 CURSOR c_gac_del (
1211 cp_gac_id IGS_GR_AWD_CRMN.gac_id%TYPE) IS
1212 SELECT rowid
1213 FROM IGS_GR_AWD_CRMN gac
1214 WHERE gac.gac_id = cp_gac_id
1215 FOR UPDATE OF gac.LAST_UPDATE_DATE NOWAIT ;
1216
1217
1218 BEGIN
1219 v_error_flag := FALSE;
1220
1221 FOR v_gua_rec IN c_gua LOOP
1222 FOR v_gach_rec IN c_gach (v_gua_rec.create_dt) LOOP
1223 BEGIN
1224 -- Delete unconfirmed IGS_GR_AWD_CRMN_HIST records
1225 FOR c_gach_del_rec in c_gach_del(
1226 v_gach_rec.gach_id) LOOP
1227
1228 IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW(
1229 X_ROWID => c_gach_del_rec.ROWID );
1230
1231 END LOOP;
1232 EXCEPTION
1233 WHEN e_resource_busy THEN
1234 IF c_gach_del%ISOPEN THEN
1235 CLOSE c_gach_del;
1236 END IF;
1237 l_entity_name := 'IGS_GR_AWD_CRMN_HIST';
1238 EXIT;
1239 END;
1240
1241 END LOOP;
1242 IF l_entity_name IS NOT NULL THEN
1243 EXIT;
1244 END IF;
1245
1246 FOR v_gca_rec IN c_gac(v_gua_rec.create_dt) LOOP
1247 BEGIN
1248 -- Delete unconfirmed IGS_GR_AWD_CRMN records
1249 FOR c_gac_del_rec IN c_gac_del(
1250 v_gca_rec.gac_id) LOOP
1251
1252 IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
1253 X_ROWID => c_gac_del_rec.ROWID );
1254
1255 END LOOP;
1256 EXCEPTION
1257 WHEN e_resource_busy THEN
1258 IF c_gac_del%ISOPEN THEN
1259 CLOSE c_gac_del;
1260 END IF;
1261 l_entity_name := 'IGS_GR_AWD_CRMN';
1262 EXIT;
1263 END;
1264 END LOOP;
1265
1266 IF l_entity_name IS NOT NULL THEN
1267 EXIT;
1268 END IF;
1269 -- Delete unconfirmed IGS_GR_GRADUAND records
1270 FOR v_gua_del_exists IN c_gua_del(v_gua_rec.create_dt) LOOP
1271 IGS_GR_GRADUAND_PKG.DELETE_ROW(
1272 X_ROWID => v_gua_del_exists.rowid );
1273 END LOOP;
1274
1275 END LOOP;
1276 IF l_entity_name IS NOT NULL THEN
1277 RETURN FALSE;
1278 END IF;
1279 -- Return the default value
1280 RETURN TRUE;
1281 EXCEPTION
1282 WHEN e_resource_busy THEN
1283 IF c_gua%ISOPEN THEN
1284 CLOSE c_gua;
1285 END IF;
1286 IF c_gua_del%ISOPEN THEN
1287 CLOSE c_gua_del;
1288 END IF;
1289 IF c_gach%ISOPEN THEN
1290 CLOSE c_gach;
1291 END IF;
1292 IF c_gach_del%ISOPEN THEN
1293 CLOSE c_gach_del;
1294 END IF;
1295 IF c_gac%ISOPEN THEN
1296 CLOSE c_gac;
1297 END IF;
1298 IF c_gac_del%ISOPEN THEN
1299 CLOSE c_gac_del;
1300 END IF;
1301 l_entity_name := 'IGS_GR_GRADUAND_ALL';
1302 RETURN FALSE;
1303 WHEN OTHERS THEN
1304 IF c_gua%ISOPEN THEN
1305 CLOSE c_gua;
1306 END IF;
1307 IF c_gua_del%ISOPEN THEN
1308 CLOSE c_gua_del;
1309 END IF;
1310 IF c_gach%ISOPEN THEN
1311 CLOSE c_gach;
1312 END IF;
1313 IF c_gach_del%ISOPEN THEN
1314 CLOSE c_gach_del;
1315 END IF;
1316 IF c_gac%ISOPEN THEN
1317 CLOSE c_gac;
1318 END IF;
1319 IF c_gac_del%ISOPEN THEN
1320 CLOSE c_gac_del;
1321 END IF;
1322 App_Exception.Raise_Exception;
1323 END;
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1327 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_gua');
1328 IGS_GE_MSG_STACK.ADD;
1329 App_Exception.Raise_Exception;
1330 END admpl_del_gua;
1331 --------------------------------------------------------------------------------
1332 -- ADMPL_DEL_GSA ---------------------------------------------------------------
1333 --Who When What
1334 --------------------------------------------------------------------------------
1335 FUNCTION admpl_del_gsa(
1336 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1337 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1338 RETURN BOOLEAN
1339 IS
1340 BEGIN -- admpl_del_gsa
1341 -- (1) Delete IGS_GR_SPECIAL_AWARD records
1342 DECLARE
1343 CURSOR c_gsa IS
1344 SELECT award_cd,
1345 award_dt
1346 FROM IGS_GR_SPECIAL_AWARD_ALL gsa
1347 WHERE gsa.person_id = p_person_id AND
1348 gsa.course_cd = p_course_cd;
1349
1350 CURSOR c_gsa_del (
1351 cp_award_cd IGS_GR_SPECIAL_AWARD_ALL.award_cd%TYPE,
1352 cp_award_dt IGS_GR_SPECIAL_AWARD_ALL.award_dt%TYPE) IS
1353 SELECT rowid
1354 FROM IGS_GR_SPECIAL_AWARD_ALL gsa
1355 WHERE gsa.person_id = p_person_id AND
1356 gsa.course_cd = p_course_cd AND
1357 gsa.award_cd = cp_award_cd AND
1358 gsa.award_dt = cp_award_dt
1359 FOR UPDATE OF gsa.LAST_UPDATE_DATE NOWAIT ;
1360
1361 v_gsa_del_exists c_gsa_del%ROWTYPE;
1362
1363
1364 BEGIN
1365 v_error_flag := FALSE;
1366
1367 FOR v_gsa_rec IN c_gsa LOOP
1368
1369 FOR v_gsa_del_exists IN c_gsa_del(v_gsa_rec.award_cd,
1370 v_gsa_rec.award_dt ) LOOP
1371 IGS_GR_SPECIAL_AWARD_PKG.DELETE_ROW(
1372 X_ROWID => v_gsa_del_exists.rowid );
1373 END LOOP;
1374 END LOOP;
1375
1376 -- Return the default value
1377 RETURN TRUE;
1378 EXCEPTION
1379 WHEN e_resource_busy THEN
1380 IF c_gsa%ISOPEN THEN
1381 CLOSE c_gsa;
1382 END IF;
1383 IF c_gsa_del%ISOPEN THEN
1384 CLOSE c_gsa_del;
1385 END IF;
1386 l_entity_name := 'IGS_GR_SPECIAL_AWARD_ALL';
1387 RETURN FALSE;
1388 WHEN OTHERS THEN
1389 IF c_gsa%ISOPEN THEN
1390 CLOSE c_gsa;
1391 END IF;
1392 IF c_gsa_del%ISOPEN THEN
1393 CLOSE c_gsa_del;
1394 END IF;
1395 App_Exception.Raise_Exception;
1396 END;
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1400 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_gsa');
1401 IGS_GE_MSG_STACK.ADD;
1402 App_Exception.Raise_Exception;
1403 END admpl_del_gsa;
1404
1405 FUNCTION admpl_del_psaa(
1406 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1407 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1408 RETURN BOOLEAN
1409 IS
1410 BEGIN -- admpl_del_psaa
1411 -- Delete IGS_PS_STDNT_APV_ALT records
1412 DECLARE
1413 CURSOR c_psaa IS
1414 SELECT exit_course_cd,
1415 exit_version_number
1416 FROM IGS_PS_STDNT_APV_ALT psaa
1417 WHERE psaa.person_id = p_person_id AND
1418 psaa.course_cd = p_course_cd;
1419
1420 CURSOR c_psaa_del (
1421 cp_exit_course_cd IGS_PS_STDNT_APV_ALT.exit_course_cd%TYPE,
1422 cp_exit_version_number IGS_PS_STDNT_APV_ALT.exit_version_number%TYPE) IS
1423 SELECT rowid
1424 FROM IGS_PS_STDNT_APV_ALT psaa
1425 WHERE psaa.person_id = p_person_id AND
1426 psaa.course_cd = p_course_cd AND
1427 psaa.exit_course_cd = cp_exit_course_cd AND
1428 psaa.exit_version_number = cp_exit_version_number
1429 FOR UPDATE OF psaa.LAST_UPDATE_DATE NOWAIT ;
1430
1431 v_psaa_del_exists c_psaa_del%ROWTYPE;
1432
1433
1434 BEGIN
1435 v_error_flag := FALSE;
1436
1437 FOR v_psaa_rec IN c_psaa LOOP
1438 -- Delete IGS_PS_STDNT_APV_ALT records
1439 FOR v_psaa_del_exists IN c_psaa_del(v_psaa_rec.exit_course_cd,
1440 v_psaa_rec.exit_version_number ) LOOP
1441 IGS_PS_STDNT_APV_ALT_PKG.DELETE_ROW(
1442 X_ROWID => v_psaa_del_exists.rowid );
1443 END LOOP;
1444 END LOOP;
1445
1446 -- Return the default value
1447 RETURN TRUE;
1448 EXCEPTION
1449 WHEN e_resource_busy THEN
1450 IF c_psaa%ISOPEN THEN
1451 CLOSE c_psaa;
1452 END IF;
1453 IF c_psaa_del%ISOPEN THEN
1454 CLOSE c_psaa_del;
1455 END IF;
1456 l_entity_name := 'IGS_PS_STDNT_APV_ALT';
1457 RETURN FALSE;
1458 WHEN OTHERS THEN
1459 IF c_psaa%ISOPEN THEN
1460 CLOSE c_psaa;
1461 END IF;
1462 IF c_psaa_del%ISOPEN THEN
1463 CLOSE c_psaa_del;
1464 END IF;
1465 App_Exception.Raise_Exception;
1466 END;
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1470 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_psaa');
1471 IGS_GE_MSG_STACK.ADD;
1472 App_Exception.Raise_Exception;
1473 END admpl_del_psaa;
1474 --------------------------------------------------------------------------------
1475 -- ADMPL_DEL_PSSR ---------------------------------------------------------------
1476 --Who When What
1477 --------------------------------------------------------------------------------
1478 FUNCTION admpl_del_pssr(
1479 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1480 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1481 RETURN BOOLEAN
1482 IS
1483 BEGIN -- admpl_del_pssr
1484 -- Delete IGS_PS_STDNT_SPL_REQ records
1485 DECLARE
1486 CURSOR c_pssr IS
1487 SELECT special_requirement_cd,
1488 completed_dt
1489 FROM IGS_PS_STDNT_SPL_REQ pssr
1490 WHERE pssr.person_id = p_person_id AND
1491 pssr.course_cd = p_course_cd;
1492
1493 CURSOR c_pssr_del (
1494 cp_special_requirement_cd IGS_PS_STDNT_SPL_REQ.special_requirement_cd%TYPE,
1495 cp_completed_dt IGS_PS_STDNT_SPL_REQ.completed_dt%TYPE) IS
1496 SELECT rowid
1497 FROM IGS_PS_STDNT_SPL_REQ pssr
1498 WHERE pssr.person_id = p_person_id AND
1499 pssr.course_cd = p_course_cd AND
1500 pssr.special_requirement_cd = cp_special_requirement_cd AND
1501 pssr.completed_dt = cp_completed_dt
1502 FOR UPDATE OF pssr.LAST_UPDATE_DATE NOWAIT ;
1503
1504 v_pssr_del_exists c_pssr_del%ROWTYPE;
1505
1506
1507 BEGIN
1508 v_error_flag := FALSE;
1509
1510 FOR v_pssr_rec IN c_pssr LOOP
1511 -- Delete IGS_PS_STDNT_SPL_REQ records
1512 FOR v_pssr_del_exists IN c_pssr_del(v_pssr_rec.special_requirement_cd,
1513 v_pssr_rec.completed_dt ) LOOP
1514 IGS_PS_STDNT_SPL_REQ_PKG.DELETE_ROW(
1515 X_ROWID => v_pssr_del_exists.rowid );
1516 END LOOP;
1517 END LOOP;
1518
1519 -- Return the default value
1520 RETURN TRUE;
1521 EXCEPTION
1522 WHEN e_resource_busy THEN
1523 IF c_pssr%ISOPEN THEN
1524 CLOSE c_pssr;
1525 END IF;
1526 IF c_pssr_del%ISOPEN THEN
1527 CLOSE c_pssr_del;
1528 END IF;
1529 l_entity_name := 'IGS_PS_STDNT_SPL_REQ';
1530 RETURN FALSE;
1531 WHEN OTHERS THEN
1532 IF c_pssr%ISOPEN THEN
1533 CLOSE c_pssr;
1534 END IF;
1535 IF c_pssr_del%ISOPEN THEN
1536 CLOSE c_pssr_del;
1537 END IF;
1538 App_Exception.Raise_Exception;
1539 END;
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1543 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_pssr');
1544 IGS_GE_MSG_STACK.ADD;
1545 App_Exception.Raise_Exception;
1546 END admpl_del_pssr;
1547
1548 --------------------------------------------------------------------------------
1549 -- ADMPL_DEL_ESAA ---------------------------------------------------------------
1550 --Who When What
1551 --------------------------------------------------------------------------------
1552 FUNCTION admpl_del_esaa(
1553 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1554 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1555 RETURN BOOLEAN
1556 IS
1557 BEGIN -- admpl_del_esaa
1558 -- Delete IGS_EN_SPA_AWD_AIM records
1559 DECLARE
1560 CURSOR c_esaa IS
1561 SELECT award_cd
1562 FROM IGS_EN_SPA_AWD_AIM esaa
1563 WHERE esaa.person_id = p_person_id AND
1564 esaa.course_cd = p_course_cd;
1565
1566 CURSOR c_esaa_del (
1567 cp_award_cd IGS_EN_SPA_AWD_AIM.award_cd%TYPE) IS
1568 SELECT rowid
1569 FROM IGS_EN_SPA_AWD_AIM esaa
1570 WHERE esaa.person_id = p_person_id AND
1571 esaa.course_cd = p_course_cd AND
1572 esaa.award_cd = cp_award_cd
1573 FOR UPDATE OF esaa.LAST_UPDATE_DATE NOWAIT ;
1574
1575 v_esaa_del_exists c_esaa_del%ROWTYPE;
1576
1577
1578 BEGIN
1579 v_error_flag := FALSE;
1580
1581 FOR v_esaa_rec IN c_esaa LOOP
1582 -- Delete IGS_EN_SPA_AWD_AIM records
1583 FOR v_esaa_del_exists IN c_esaa_del(v_esaa_rec.award_cd) LOOP
1584 IGS_EN_SPA_AWD_AIM_PKG.DELETE_ROW(
1585 X_ROWID => v_esaa_del_exists.rowid );
1586 END LOOP;
1587 END LOOP;
1588
1589 -- Return the default value
1590 RETURN TRUE;
1591 EXCEPTION
1592 WHEN e_resource_busy THEN
1593 IF c_esaa%ISOPEN THEN
1594 CLOSE c_esaa;
1595 END IF;
1596 IF c_esaa_del%ISOPEN THEN
1597 CLOSE c_esaa_del;
1598 END IF;
1599 l_entity_name := 'IGS_EN_SPA_AWD_AIM';
1600 RETURN FALSE;
1601 WHEN OTHERS THEN
1602 IF c_esaa%ISOPEN THEN
1603 CLOSE c_esaa;
1604 END IF;
1605 IF c_esaa_del%ISOPEN THEN
1606 CLOSE c_esaa_del;
1607 END IF;
1608 App_Exception.Raise_Exception;
1609 END;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1613 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_esaa');
1614 IGS_GE_MSG_STACK.ADD;
1615 App_Exception.Raise_Exception;
1616 END admpl_del_esaa;
1617
1618 --------------------------------------------------------------------------------
1619 -- ADMPL_DEL_HSSA ---------------------------------------------------------------
1620 --Who When What
1621 --------------------------------------------------------------------------------
1622 FUNCTION admpl_del_hssa(
1623 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1624 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1625 RETURN BOOLEAN
1626 IS
1627 BEGIN -- admpl_del_hssa
1628 -- Delete IGS_HE_ST_SPA_ALL records
1629 DECLARE
1630 CURSOR c_hssa IS
1631 SELECT hesa_st_spa_id,
1632 person_id,
1633 course_cd
1634 FROM IGS_HE_ST_SPA_ALL hssa
1635 WHERE hssa.person_id = p_person_id AND
1636 hssa.course_cd = p_course_cd;
1637
1638 CURSOR c_hssa_del (
1639 cp_hesa_st_spa_id IGS_HE_ST_SPA_ALL.hesa_st_spa_id%TYPE) IS
1640 SELECT rowid
1641 FROM IGS_HE_ST_SPA_ALL hssa
1642 WHERE hssa.hesa_st_spa_id = cp_hesa_st_spa_id
1643 FOR UPDATE OF hssa.LAST_UPDATE_DATE NOWAIT ;
1644
1645 v_hssa_del_exists c_hssa_del%ROWTYPE;
1646
1647 CURSOR c_hssua (
1648 cp_person_id IGS_HE_ST_SPA_UT_ALL.person_id%TYPE,
1649 cp_course_cd IGS_HE_ST_SPA_ALL.course_cd%TYPE) IS
1650 SELECT hesa_st_spau_id
1651 FROM IGS_HE_ST_SPA_UT_ALL hssua
1652 WHERE hssua.person_id = cp_person_id AND
1653 hssua.course_cd = cp_course_cd ;
1654
1655 CURSOR c_hssua_del (
1656 cp_hesa_st_spau_id IGS_HE_ST_SPA_UT_ALL.hesa_st_spau_id%TYPE) IS
1657 SELECT rowid
1658 FROM IGS_HE_ST_SPA_UT_ALL hssua
1659 WHERE hssua.hesa_st_spau_id = cp_hesa_st_spau_id
1660 FOR UPDATE OF hssua.LAST_UPDATE_DATE NOWAIT ;
1661
1662
1663 BEGIN
1664 v_error_flag := FALSE;
1665
1666 FOR v_hssa_rec IN c_hssa LOOP
1667
1668 FOR v_hssua_rec IN c_hssua (v_hssa_rec.person_id,
1669 v_hssa_rec.course_cd ) LOOP
1670 BEGIN
1671 -- Delete unconfirmed IGS_HE_ST_SPA_UT_ALL records
1672 FOR v_hssua_del_rec in c_hssua_del(
1673 v_hssua_rec.hesa_st_spau_id) LOOP
1674 IGS_HE_ST_SPA_UT_ALL_PKG.DELETE_ROW(
1675 X_ROWID => v_hssua_del_rec.ROWID );
1676
1677 END LOOP;
1678 EXCEPTION
1679 WHEN e_resource_busy THEN
1680 IF c_hssua_del%ISOPEN THEN
1681 CLOSE c_hssua_del;
1682 END IF;
1683 l_entity_name := 'IGS_HE_ST_SPA_UT_ALL';
1684 EXIT;
1685 END;
1686
1687 END LOOP;
1688 IF l_entity_name IS NOT NULL THEN
1689 EXIT;
1690 END IF;
1691
1692 -- Delete IGS_HE_ST_SPA_ALL records
1693 FOR v_hssa_del_exists IN c_hssa_del(v_hssa_rec.hesa_st_spa_id) LOOP
1694
1695 IGS_HE_ST_SPA_ALL_PKG.DELETE_ROW(
1696 X_ROWID => v_hssa_del_exists.rowid );
1697 END LOOP;
1698
1699 END LOOP;
1700
1701 IF l_entity_name IS NOT NULL THEN
1702 RETURN FALSE;
1703 END IF;
1704 -- Return the default value
1705 RETURN TRUE;
1706 EXCEPTION
1707 WHEN e_resource_busy THEN
1708 IF c_hssa%ISOPEN THEN
1709 CLOSE c_hssa;
1710 END IF;
1711 IF c_hssa_del%ISOPEN THEN
1712 CLOSE c_hssa_del;
1713 END IF;
1714 IF c_hssua%ISOPEN THEN
1715 CLOSE c_hssua;
1716 END IF;
1717 IF c_hssua_del%ISOPEN THEN
1718 CLOSE c_hssua_del;
1719 END IF;
1720 l_entity_name := 'IGS_HE_ST_SPA_ALL';
1721 RETURN FALSE;
1722 WHEN OTHERS THEN
1723 IF c_hssa%ISOPEN THEN
1724 CLOSE c_hssa;
1725 END IF;
1726 IF c_hssa_del%ISOPEN THEN
1727 CLOSE c_hssa_del;
1728 END IF;
1729 IF c_hssua%ISOPEN THEN
1730 CLOSE c_hssua;
1731 END IF;
1732 IF c_hssua_del%ISOPEN THEN
1733 CLOSE c_hssua_del;
1734 END IF;
1735 App_Exception.Raise_Exception;
1736 END;
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1740 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_hssa');
1741 IGS_GE_MSG_STACK.ADD;
1742 App_Exception.Raise_Exception;
1743 END admpl_del_hssa;
1744
1745 --------------------------------------------------------------------------------
1746 -- ADMPL_UPD_PR_RULE_APPL ---------------------------------------------------------------
1747 --Who When What
1748 --------------------------------------------------------------------------------
1749 FUNCTION admpl_upd_pr_rule_appl(
1750 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1751 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1752 RETURN BOOLEAN
1753 IS
1754 BEGIN -- admpl_upd_pr_rule_appl
1755 -- Delete IGS_PR_RU_APPL_ALL records
1756 DECLARE
1757 CURSOR c_pra IS
1758 SELECT progression_rule_cat,
1759 sequence_number
1760 FROM IGS_PR_RU_APPL_ALL pra
1761 WHERE pra.sca_person_id = p_person_id AND
1762 pra.sca_course_cd = p_course_cd;
1763
1764 CURSOR c_pra_del (
1765 cp_progression_rule_cat IGS_PR_RU_APPL_ALL.progression_rule_cat%TYPE,
1766 cp_sequence_number IGS_PR_RU_APPL_ALL.sequence_number%TYPE) IS
1767 SELECT rowid,pra.*
1768 FROM IGS_PR_RU_APPL_ALL pra
1769 WHERE pra.progression_rule_cat = cp_progression_rule_cat AND
1770 pra.sequence_number = cp_sequence_number
1771 FOR UPDATE OF pra.LAST_UPDATE_DATE NOWAIT ;
1772
1773 v_pra_upd_exists c_pra_del%ROWTYPE;
1774
1775
1776 BEGIN
1777 v_error_flag := FALSE;
1778
1779 FOR v_pra_rec IN c_pra LOOP
1780 -- Delete IGS_PR_RU_APPL_ALL records
1781 FOR v_pra_upd_exists IN c_pra_del(v_pra_rec.progression_rule_cat,
1782 v_pra_rec.sequence_number ) LOOP
1783 IGS_PR_RU_APPL_PKG.UPDATE_ROW (
1784 X_ROWID => v_pra_upd_exists.rowid,
1785 X_PROGRESSION_RULE_CAT => v_pra_upd_exists.progression_rule_cat ,
1786 X_SEQUENCE_NUMBER => v_pra_upd_exists.sequence_number,
1787 X_S_RELATION_TYPE => v_pra_upd_exists.s_relation_type,
1788 X_PROGRESSION_RULE_CD => v_pra_upd_exists.progression_rule_cd,
1789 X_REFERENCE_CD => v_pra_upd_exists.reference_cd,
1790 X_RUL_SEQUENCE_NUMBER => v_pra_upd_exists.rul_sequence_number,
1791 X_ATTENDANCE_TYPE => v_pra_upd_exists.attendance_type,
1792 X_OU_ORG_UNIT_CD => v_pra_upd_exists.ou_org_unit_cd,
1793 X_OU_START_DT => v_pra_upd_exists.ou_start_dt,
1794 X_COURSE_TYPE => v_pra_upd_exists.course_type,
1795 X_CRV_COURSE_CD => v_pra_upd_exists.crv_course_cd,
1796 X_CRV_VERSION_NUMBER => v_pra_upd_exists.crv_version_number,
1797 X_SCA_PERSON_ID => v_pra_upd_exists.sca_person_id,
1798 X_SCA_COURSE_CD => v_pra_upd_exists.sca_course_cd,
1799 X_PRO_PROGRESSION_RULE_CAT => v_pra_upd_exists.pro_progression_rule_cat,
1800 X_PRO_PRA_SEQUENCE_NUMBER => v_pra_upd_exists.pro_pra_sequence_number,
1801 X_PRO_SEQUENCE_NUMBER => v_pra_upd_exists.pro_sequence_number,
1802 X_SPO_PERSON_ID => v_pra_upd_exists.spo_person_id,
1803 X_SPO_COURSE_CD => v_pra_upd_exists.spo_course_cd,
1804 X_SPO_SEQUENCE_NUMBER => v_pra_upd_exists.spo_sequence_number,
1805 X_LOGICAL_DELETE_DT => TRUNC(SYSDATE),
1806 X_MESSAGE => v_pra_upd_exists.message,
1807 X_MODE => 'R',
1808 X_MIN_CP => v_pra_upd_exists.min_cp,
1809 X_MAX_CP => v_pra_upd_exists.max_cp,
1810 X_IGS_PR_CLASS_STD_ID => v_pra_upd_exists.igs_pr_class_std_id
1811 ) ;
1812 END LOOP;
1813 END LOOP;
1814
1815 -- Return the default value
1816 RETURN TRUE;
1817 EXCEPTION
1818 WHEN e_resource_busy THEN
1819 IF c_pra%ISOPEN THEN
1820 CLOSE c_pra;
1821 END IF;
1822 IF c_pra_del%ISOPEN THEN
1823 CLOSE c_pra_del;
1824 END IF;
1825 l_entity_name := 'IGS_PR_RU_APPL_ALL';
1826 RETURN FALSE;
1827 WHEN OTHERS THEN
1828 IF c_pra%ISOPEN THEN
1829 CLOSE c_pra;
1830 END IF;
1831 IF c_pra_del%ISOPEN THEN
1832 CLOSE c_pra_del;
1833 END IF;
1834 App_Exception.Raise_Exception;
1835 END;
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1839 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_upd_pr_rule_appl');
1840 IGS_GE_MSG_STACK.ADD;
1841 App_Exception.Raise_Exception;
1842 END admpl_upd_pr_rule_appl;
1843
1844 --------------------------------------------------------------------------------
1845 -- ADMPL_DEL_HSSC ---------------------------------------------------------------
1846 --Who When What
1847 --------------------------------------------------------------------------------
1848 FUNCTION admpl_del_hssc(
1849 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1850 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1851 RETURN BOOLEAN
1852 IS
1853 BEGIN -- admpl_del_hssc
1854 -- Delete IGS_HE_ST_SPA_CC records
1855 DECLARE
1856 CURSOR c_hssc IS
1857 SELECT he_spa_cc_id
1858 FROM IGS_HE_ST_SPA_CC hssc
1859 WHERE hssc.person_id = p_person_id AND
1860 hssc.course_cd = p_course_cd;
1861
1862 CURSOR c_hssc_del (cp_he_spa_cc_id IGS_HE_ST_SPA_CC.he_spa_cc_id%TYPE) IS
1863 SELECT rowid
1864 FROM IGS_HE_ST_SPA_CC hssc
1865 WHERE hssc.he_spa_cc_id = cp_he_spa_cc_id
1866 FOR UPDATE OF hssc.LAST_UPDATE_DATE NOWAIT ;
1867
1868 v_hssc_del_exists c_hssc_del%ROWTYPE;
1869
1870
1871 BEGIN
1872
1873 v_error_flag := FALSE;
1874
1875 FOR v_hssc_rec IN c_hssc LOOP
1876 -- Delete IGS_HE_ST_SPA_CC records
1877 FOR v_hssc_del_exists IN c_hssc_del(v_hssc_rec.he_spa_cc_id) LOOP
1878
1879 IGS_HE_ST_SPA_CC_PKG.DELETE_ROW(
1880 X_ROWID => v_hssc_del_exists.ROWID );
1881 END LOOP;
1882 END LOOP;
1883
1884 -- Return the default value
1885 RETURN TRUE;
1886 EXCEPTION
1887 WHEN e_resource_busy THEN
1888 IF c_hssc%ISOPEN THEN
1889 CLOSE c_hssc;
1890 END IF;
1891 IF c_hssc_del%ISOPEN THEN
1892 CLOSE c_hssc_del;
1893 END IF;
1894 l_entity_name := 'IGS_HE_ST_SPA_CC';
1895 RETURN FALSE;
1896 WHEN OTHERS THEN
1897 IF c_hssc%ISOPEN THEN
1898 CLOSE c_hssc;
1899 END IF;
1900 IF c_hssc_del%ISOPEN THEN
1901 CLOSE c_hssc_del;
1902 END IF;
1903 App_Exception.Raise_Exception;
1904 END;
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1908 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admpl_del_hssc');
1909 IGS_GE_MSG_STACK.ADD;
1910 App_Exception.Raise_Exception;
1911 END admpl_del_hssc;
1912
1913 BEGIN
1914 -- Create log
1915 IGS_GE_GEN_003.genp_ins_log (
1916 cst_del_un_sca,
1917 NULL, -- Key
1918 v_log_creation_dt);
1919 -- Issue commit since the the child IGS_GE_S_LOG_ENTRY is populated
1920 -- in autonomous transaction mode and would need to reference the
1921 -- parent record in check child existance
1922 COMMIT;
1923
1924 -- Determine the enrolment period cleanup dates that are on this date.
1925 -- Determine admission periods linked as superiors to the enrolment period
1926 FOR v_cir_rec IN c_cir
1927 LOOP
1928 -- Delete unconfirmed student IGS_PS_COURSE attempts that have been withdrawn
1929 -- or revoked, or have been made offers and rejected or lapsed.
1930 FOR v_sca_rec IN c_sca(
1931 v_cir_rec.sup_cal_type,
1932 v_cir_rec.sup_ci_sequence_number)
1933 LOOP
1934
1935 OPEN c_term (v_sca_rec.person_id, v_sca_rec.course_cd);
1936 FETCH c_term INTO l_term;
1937
1938 IF c_term%NOTFOUND THEN
1939
1940 -- Initialise variables before processing next student IGS_PS_COURSE attempt
1941 l_entity_name := NULL;
1942 v_process_next := FALSE;
1943 v_delete_sca_ind := 'Y';
1944 v_key :=
1945 v_sca_rec.acad_cal_type || '|' ||
1946 IGS_GE_NUMBER.TO_CANN(v_sca_rec.acad_ci_sequence_number) || '|' ||
1947 v_sca_rec.adm_cal_type || '|' ||
1948 IGS_GE_NUMBER.TO_CANN(v_sca_rec.adm_ci_sequence_number) || '|' ||
1949 v_sca_rec.admission_cat || '|' ||
1950 v_sca_rec.s_admission_process_type || '|' ||
1951 IGS_GE_NUMBER.TO_CANN(v_sca_rec.person_id) || '|' ||
1952 v_sca_rec.course_cd;
1953 igs_ge_msg_stack.initialize;
1954 l_msg_at_index := igs_ge_msg_stack.count_msg;
1955
1956 BEGIN -- c_sca_upd_block
1957 OPEN c_sca_upd(
1958 v_sca_rec.person_id,
1959 v_sca_rec.course_cd);
1960 FETCH c_sca_upd INTO v_sca_upd_exists;
1961
1962 SAVEPOINT sp_sca_del;
1963 -- Delete child records
1964
1965 -- Delete IGS_GR_GRADUAND Records (1)
1966 IF NOT v_process_next THEN
1967 IF NOT admpl_del_gua(
1968 v_sca_rec.person_id,
1969 v_sca_rec.course_cd) THEN
1970 ROLLBACK TO sp_sca_del;
1971 -- Process next entity for the current program attempt
1972 v_process_next := TRUE;
1973 END IF;
1974 END IF;
1975
1976 -- Delete IGS_EN_SPA_AWD_AIM Records (2)
1977 IF NOT v_process_next THEN
1978 IF NOT admpl_del_esaa(
1979 v_sca_rec.person_id,
1980 v_sca_rec.course_cd) THEN
1981 ROLLBACK TO sp_sca_del;
1982 -- Process next entity for the current program attempt
1983 v_process_next := TRUE;
1984 END IF;
1985 END IF;
1986
1987 -- Delete IGS_GR_SPECIAL_AWARD Records (3)
1988 IF NOT v_process_next THEN
1989 IF NOT admpl_del_gsa(
1990 v_sca_rec.person_id,
1991 v_sca_rec.course_cd) THEN
1992 ROLLBACK TO sp_sca_del;
1993 -- Process next entity for the current program attempt
1994 v_process_next := TRUE;
1995 END IF;
1996 END IF;
1997
1998 -- Delete IGS_HE_ST_SPA_CC Records (4)
1999 IF NOT v_process_next THEN
2000 IF NOT admpl_del_hssc(
2001 v_sca_rec.person_id,
2002 v_sca_rec.course_cd) THEN
2003 ROLLBACK TO sp_sca_del;
2004 -- Process next entity for the current program attempt
2005 v_process_next := TRUE;
2006 END IF;
2007 END IF;
2008
2009 -- Delete IGS_HE_ST_SPA_ALL Records (5)
2010 IF NOT v_process_next THEN
2011 IF NOT admpl_del_hssa(
2012 v_sca_rec.person_id,
2013 v_sca_rec.course_cd) THEN
2014 ROLLBACK TO sp_sca_del;
2015 -- Process next entity for the current program attempt
2016 v_process_next := TRUE;
2017 END IF;
2018 END IF;
2019
2020 -- Update setup of progression rule application
2021 -- record's logical_delete_date to TRUNC(current system date) (6)
2022 IF NOT v_process_next THEN
2023 IF NOT admpl_upd_pr_rule_appl(
2024 v_sca_rec.person_id,
2025 v_sca_rec.course_cd) THEN
2026 ROLLBACK TO sp_sca_del;
2027 -- Process next entity for the current program attempt
2028 v_process_next := TRUE;
2029 END IF;
2030 END IF;
2031
2032 -- Delete IGS_PS_STDNT_APV_ALT Records (7)
2033 IF NOT v_process_next THEN
2034 IF NOT admpl_del_psaa(
2035 v_sca_rec.person_id,
2036 v_sca_rec.course_cd) THEN
2037 ROLLBACK TO sp_sca_del;
2038 -- Process next entity for the current program attempt
2039 v_process_next := TRUE;
2040 END IF;
2041 END IF;
2042
2043 -- Delete IGS_PS_STDNT_SPL_REQ Records (8)
2044 -- Do not need to handle as record cannot be created
2045 -- if program attempt is unconfirmed
2046 /*
2047 IF NOT v_process_next THEN
2048 IF NOT admpl_del_pssr(
2049 v_sca_rec.person_id,
2050 v_sca_rec.course_cd) THEN
2051 ROLLBACK TO sp_sca_del;
2052 -- Process next entity for the current program attempt
2053 v_process_next := TRUE;
2054 END IF;
2055 END IF;
2056 */
2057
2058 -- Delete IGS_EN_SU_ATTEMPT_H (9)
2059 IF NOT v_process_next THEN
2060 IF NOT admpl_del_suah(
2061 v_sca_rec.person_id,
2062 v_sca_rec.course_cd) THEN
2063 ROLLBACK TO sp_sca_del;
2064 -- Process next entity for the current program attempt
2065 v_process_next := TRUE;
2066 END IF;
2067 END IF;
2068
2069 -- Delete IGS_EN_SU_ATTEMPT (10)
2070 IF NOT v_process_next THEN
2071 IF NOT admpl_del_sua(
2072 v_sca_rec.person_id,
2073 v_sca_rec.course_cd) THEN
2074 ROLLBACK TO sp_sca_del;
2075 -- Process next entity for the current program attempt
2076 v_process_next := TRUE;
2077 END IF;
2078 END IF;
2079
2080 -- Delete IGS_AS_SU_SETATMPT (11)
2081 IF NOT v_process_next THEN
2082 IF NOT admpl_del_susa(
2083 v_sca_rec.person_id,
2084 v_sca_rec.course_cd) THEN
2085 ROLLBACK TO sp_sca_del;
2086 -- Process next entity for the current program attempt
2087 v_process_next := TRUE;
2088 END IF;
2089 END IF;
2090
2091 -- Delete IGS_EN_STDNTPSHECSOP (12)
2092 IF NOT v_process_next THEN
2093 IF NOT admpl_del_scho(
2094 v_sca_rec.person_id,
2095 v_sca_rec.course_cd) THEN
2096 ROLLBACK TO sp_sca_del;
2097 -- Process next entity for the current program attempt
2098 v_process_next := TRUE;
2099 END IF;
2100 END IF;
2101
2102 -- Delete IGS_AS_SC_ATMPT_ENR (13)
2103 IF NOT v_process_next THEN
2104 IF NOT admpl_del_scae(
2105 v_sca_rec.person_id,
2106 v_sca_rec.course_cd) THEN
2107 ROLLBACK TO sp_sca_del;
2108 -- Process next entity for the current program attempt
2109 v_process_next := TRUE;
2110 END IF;
2111 END IF;
2112
2113 -- Delete from student_course_attempt_notes (14)
2114 IF NOT v_process_next THEN
2115 IF NOT admpl_del_scan (
2116 v_sca_rec.person_id,
2117 v_sca_rec.course_cd) THEN
2118 ROLLBACK TO sp_sca_del;
2119 -- Process next entity for the current program attempt
2120 v_process_next := TRUE;
2121 END IF;
2122 END IF;
2123
2124 -- Update research candidature (15)
2125 IF NOT v_process_next THEN
2126 IF NOT admpl_upd_re_candidature (
2127 v_sca_rec.person_id,
2128 v_sca_rec.course_cd,
2129 v_sca_rec.adm_admission_appl_number,
2130 v_sca_rec.adm_nominated_course_cd,
2131 v_sca_rec.adm_sequence_number) THEN
2132 ROLLBACK TO sp_sca_del;
2133 -- Process next entity for the current program attempt
2134 v_process_next := TRUE;
2135 END IF;
2136 END IF;
2137
2138 -- Check for approved advanced standing (16)
2139 IF NOT v_process_next THEN
2140 IF NOT admpl_chk_as (
2141 v_sca_rec.person_id,
2142 v_sca_rec.course_cd,
2143 v_sca_rec.s_adm_offer_resp_status,
2144 v_sca_rec.s_adm_offer_dfrmnt_status) THEN
2145 ROLLBACK TO sp_sca_del;
2146 -- Process next entity for the current program attempt
2147 v_process_next := TRUE;
2148 END IF;
2149 END IF;
2150
2151 -- Process fees, routine performs its own logging (17)
2152 IF NOT v_process_next THEN
2153 IGS_FI_GEN_004.finp_prc_sca_unconf (
2154 v_sca_rec.person_id,
2155 v_sca_rec.course_cd,
2156 v_sca_rec.course_attempt_status,
2157 v_sca_rec.fee_cat,
2158 v_log_creation_dt,
2159 v_key,
2160 v_sca_rec.admission_appl_number,
2161 v_sca_rec.nominated_course_cd,
2162 v_sca_rec.sequence_number,
2163 v_fee_ass_log_creation_dt,
2164 v_delete_sca_ind);
2165 END IF;
2166
2167 -- Validate if student program attempt can be deleted
2168 IF NOT v_process_next AND v_delete_sca_ind = 'Y' THEN
2169 IGS_EN_STDNT_PS_ATT_PKG.DELETE_ROW(v_sca_upd_exists.rowid);
2170 admpl_del_ins_log_entry (
2171 p_message_name => NULL,
2172 p_default_msg_txt => NULL,
2173 p_sca_deleted_ind => 'Y',
2174 p_log_creation_dt => v_log_creation_dt,
2175 p_key => v_key,
2176 p_s_log_type => cst_del_un_sca);
2177 ELSE
2178 IF l_entity_name IS NOT NULL THEN
2179 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UNCONF_SCA_REC_LOCKED');
2180 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity_name);
2181 IGS_GE_MSG_STACK.ADD;
2182 END IF;
2183 extract_msg_from_stack(l_msg_at_index);
2184 END IF;
2185
2186 CLOSE c_sca_upd;
2187 EXCEPTION
2188 WHEN e_resource_busy THEN
2189 l_entity_name := 'IGS_EN_STDNT_PS_ATT_ALL';
2190 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UNCONF_SCA_REC_LOCKED');
2191 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity_name);
2192 IGS_GE_MSG_STACK.ADD;
2193 extract_msg_from_stack(l_msg_at_index);
2194 ROLLBACK TO sp_sca_del;
2195 WHEN e_savepoint_lost THEN
2196 IF c_sca_upd%ISOPEN THEN
2197 CLOSE c_sca_upd;
2198 END IF;
2199 extract_msg_from_stack(l_msg_at_index);
2200 ROLLBACK TO sp_sca_del;
2201 WHEN OTHERS THEN
2202 IF c_sca_upd%ISOPEN THEN
2203 CLOSE c_sca_upd;
2204 END IF;
2205 extract_msg_from_stack(l_msg_at_index);
2206 ROLLBACK TO sp_sca_del;
2207 END; -- c_sca_upd_block
2208
2209 ELSE
2210 fnd_file.put_line(fnd_file.log, 'Cannot delete unconfirm Student Program Attempt for Person ID:'|| l_term.person_id||
2211 ' and Course Code: '|| l_term.program_cd);
2212 END IF;
2213 CLOSE c_term;
2214
2215 END LOOP; -- c_sca
2216 END LOOP; -- c_cir
2217 p_log_creation_dt := v_log_creation_dt;
2218 COMMIT;
2219 RETURN;
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 IF c_sca%ISOPEN THEN
2223 CLOSE c_sca;
2224 END IF;
2225 IF c_sca_upd%ISOPEN THEN
2226 CLOSE c_sca_upd;
2227 END IF;
2228 IF c_cir%ISOPEN THEN
2229 CLOSE c_cir;
2230 END IF;
2231 App_Exception.Raise_Exception;
2232 END;
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2236 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_001.admp_del_sca_unconf');
2237 IGS_GE_MSG_STACK.ADD;
2238 App_Exception.Raise_Exception;
2239 END admp_del_sca_unconf;
2240
2241 PROCEDURE Set_Token(Token Varchar2)
2242
2243 IS
2244
2245 BEGIN
2246
2247 FND_MESSAGE.SET_TOKEN('ADM',Token);
2248
2249 END Set_Token;
2250
2251 PROCEDURE Check_Mand_Person_Type
2252 (
2253 p_person_id IN HZ_PARTIES.PARTY_ID%TYPE,
2254 p_data_element IN IGS_PE_STUP_DATA_EMT_ALL.data_element%TYPE,
2255 p_required_ind OUT NOCOPY IGS_PE_STUP_DATA_EMT_ALL.required_ind%TYPE
2256 )
2257 IS
2258 Cursor per_type IS
2259 SELECT person_type_code
2260 FROM igs_pe_typ_instances
2261 WHERE person_id = p_person_id
2262 AND sysdate BETWEEN start_date AND NVL(end_date, sysdate);
2263
2264 CURSOR per_type_req_man_upd (cp_person_type_code IGS_PE_STUP_DATA_EMT.PERSON_TYPE_CODE%TYPE) IS
2265 SELECT 'x'
2266 FROM igs_pe_stup_data_emt
2267 WHERE person_type_code = cp_person_type_code
2268 AND UPPER(data_element) = UPPER(p_data_element)
2269 AND required_ind = 'M';
2270
2271 CURSOR per_type_req_pre_upd (cp_person_type_code IGS_PE_STUP_DATA_EMT.PERSON_TYPE_CODE%TYPE) IS
2272 SELECT 'x'
2273 FROM igs_pe_stup_data_emt
2274 WHERE person_type_code = cp_person_type_code
2275 AND UPPER(data_element) = UPPER(p_data_element)
2276 AND required_ind = 'P';
2277
2278 CURSOR per_type_req_man_ins IS
2279 SELECT 'x'
2280 FROM igs_pe_stup_data_emt sdt, igs_pe_person_types pt
2281 WHERE sdt.person_type_code = pt.person_type_code
2282 AND pt.system_type = 'OTHER'
2283 AND UPPER(sdt.data_element) = UPPER(p_data_element)
2284 AND sdt.required_ind = 'M';
2285
2286 CURSOR per_type_req_pre_ins IS
2287 SELECT 'x'
2288 FROM igs_pe_stup_data_emt sdt, igs_pe_person_types pt
2289 WHERE sdt.person_type_code = pt.person_type_code
2290 AND pt.system_type = 'OTHER'
2291 AND UPPER(sdt.data_element) = UPPER(p_data_element)
2292 AND sdt.required_ind = 'P';
2293
2294 BEGIN
2295 IF p_person_id IS NOT NULL THEN
2296 FOR c_per_type IN per_type LOOP
2297
2298 FOR c_per_type_req_man_upd IN per_type_req_man_upd (c_per_type.person_type_code) LOOP
2299 p_required_ind := 'M';
2300 RETURN;
2301 END LOOP;
2302
2303 FOR c_per_type_req_man_upd IN per_type_req_man_upd (c_per_type.person_type_code) LOOP
2304 p_required_ind := 'P';
2305 RETURN;
2306 END LOOP;
2307 END LOOP;
2308
2309 ELSE
2310
2311 FOR c_per_type_req_man_ins IN per_type_req_man_ins LOOP
2312 p_required_ind := 'M';
2313 RETURN;
2314 END LOOP;
2315
2316 FOR c_per_type_req_pre_ins IN per_type_req_pre_ins LOOP
2317 p_required_ind := 'P';
2318 RETURN;
2319 END LOOP;
2320
2321 END IF;
2322 END Check_Mand_Person_Type;
2323
2324 FUNCTION get_user_form_name (p_function_name VARCHAR2) RETURN VARCHAR2 IS
2325 ------------------------------------------------------------------
2326 --Created by : Ramesh.Rengarajan Oracle IDC (nsinha)
2327 --Date created: 23-Jan-2002
2328 --
2329 --Purpose: Procedure to get the User form name for the passed form Name
2330 --
2331 --
2332 --Known limitations/enhancements and/or remarks:
2333 --
2334 --Change History:
2335 --Who When What
2336 -------------------------------------------------------------------
2337 --Cursor to get the user function name for the child form.
2338 CURSOR c_user_form_name IS
2339 SELECT tl.user_form_name
2340 FROM fnd_form_tl tl,
2341 fnd_form_functions_vl vl
2342 WHERE tl.form_id = vl.form_id
2343 AND tl.language = USERENV ('LANG')
2344 AND tl.application_id = 8405
2345 AND vl.application_id = 8405
2346 AND vl.function_name = p_function_name;
2347
2348 c_user_form_name_rec c_user_form_name%ROWTYPE;
2349 BEGIN
2350 IF p_function_name IS NOT NULL THEN
2351 -- Get the user function name for the child form.
2352 OPEN c_user_form_name;
2353 FETCH c_user_form_name INTO c_user_form_name_rec;
2354 IF c_user_form_name%NOTFOUND THEN
2355 CLOSE c_user_form_name;
2356 RETURN NULL;
2357 END IF;
2358 CLOSE c_user_form_name;
2359 RETURN (c_user_form_name_rec.user_form_name);
2360 ELSE
2361 RETURN NULL;
2362 END IF;
2363 END get_user_form_name;
2364
2365
2366 END IGS_AD_GEN_001;
[Home] [Help]