[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_INS_CA_TRNSFR
Source
1 PACKAGE BODY IGS_EN_INS_CA_TRNSFR AS
2 /* $Header: IGSEN17B.pls 115.6 2002/11/28 23:53:54 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_ge_gen_004.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 -- Insert CAFOS as part of course Transfer.
10 FUNCTION ENRP_INS_CAFOSTRNSFR(
11 p_person_id IN NUMBER ,
12 p_ca_sequence_number IN NUMBER ,
13 p_transfer_ca_sequence_number IN NUMBER ,
14 p_message_name OUT NOCOPY VARCHAR2)
15 RETURN BOOLEAN AS
16
17 BEGIN -- enrp_ins_cafostrnsfr
18 -- This module inserts research IGS_RE_CDT_FLD_OF_SY details as a result of
19 --course transfer. All Candidature research supervisor details
20 --(p_person_id/p_transfer_ca_sequence_number) are to be copied to the
21 --new Candidature (p_person_id/p_ca_sequence_number).
22 DECLARE
23 v_cafos_exists VARCHAR2(1);
24 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
25 CURSOR c_cafos IS
26 SELECT 'x'
27 FROM IGS_RE_CDT_FLD_OF_SY cafos
28 WHERE cafos.person_id = p_person_id AND
29 cafos.ca_sequence_number = p_ca_sequence_number;
30 CURSOR c_cafos1 IS
31 SELECT cafos.field_of_study,
32 cafos.percentage
33 FROM IGS_RE_CDT_FLD_OF_SY cafos
34 WHERE cafos.person_id = p_person_id AND
35 cafos.ca_sequence_number = p_transfer_ca_sequence_number;
36 BEGIN
37 -- Set the default message number
38 p_message_name := null;
39 --Check for existence of transfer session details
40 IF igs_as_val_suaap.genp_val_sdtt_sess(
41 cst_enrp_ins_ca_trnsfr) = TRUE THEN
42 --Not processing course transfer Candidature, finish processing
43 RETURN TRUE;
44 END IF;
45 --Validate that Candidature field of study details have not already
46 -- been transferred
47 OPEN c_cafos;
48 FETCH c_cafos INTO v_cafos_exists;
49 IF c_cafos%FOUND THEN
50 CLOSE c_cafos;
51 --Candidature field of study details already exist, cannot transfer
52 p_message_name := 'IGS_RE_CAND_FIELD_STUDY_EXIST';
53 RETURN FALSE;
54 END IF;
55 CLOSE c_cafos;
56 SAVEPOINT sp_cand_field_of_study;
57 --Insert Candidature field of study details
58 FOR v_cafos1_rec IN c_cafos1 LOOP
59 BEGIN
60 DECLARE
61 l_rowid VARCHAR2(25);
62 BEGIN
63 IGS_RE_CDT_FLD_OF_SY_PKG.INSERT_ROW(
64 x_rowid => l_rowid,
65 x_person_id => p_person_id,
66 x_ca_sequence_number => p_ca_sequence_number,
67 x_field_of_study => v_cafos1_rec.field_of_study,
68 x_percentage => v_cafos1_rec.percentage );
69 END;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 ROLLBACK TO sp_cand_field_of_study;
74 p_message_name := 'IGS_RE_CANT_INS_CAND_FLD_STDY';
75 RETURN FALSE;
76 END;
77 END LOOP; --(IGS_RE_CDT_FLD_OF_SY)
78 -- Return the default value
79 RETURN TRUE;
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF c_cafos%ISOPEN THEN
83 CLOSE c_cafos;
84 END IF;
85 IF c_cafos1%ISOPEN THEN
86 CLOSE c_cafos1;
87 END IF;
88 RAISE;
89 END;
90 EXCEPTION
91 WHEN OTHERS THEN
92 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
93 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_cafostrnsfr');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 END enrp_ins_cafostrnsfr;
97 --
98 -- Insert CAH as part of course Transfer.
99 FUNCTION ENRP_INS_CAH_TRNSFR(
100 p_person_id IN NUMBER ,
101 p_ca_sequence_number IN NUMBER ,
102 p_transfer_ca_sequence_number IN NUMBER ,
103 p_message_name OUT NOCOPY VARCHAR2)
104 RETURN BOOLEAN AS
105
106 BEGIN -- enrp_ins_cah_trnsfr
107 --This module inserts research IGS_RE_CDT_ATT_HIST details as a result of
108 --course transfer. All Candidature research supervisor details
109 --(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
110 --Candidature (p_person_id/p_ca_sequence_number).
111 DECLARE
112 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
113 v_cah_exists VARCHAR2(1);
114 CURSOR c_cah IS
115 SELECT 'x'
116 FROM IGS_RE_CDT_ATT_HIST cah
117 WHERE cah.person_id = p_person_id AND
118 cah.sequence_number = p_ca_sequence_number;
119 CURSOR c_cah1 IS
120 SELECT cah.sequence_number,
121 cah.hist_start_dt,
122 cah.hist_end_dt,
123 cah.attendance_type,
124 cah.attendance_percentage
125 FROM IGS_RE_CDT_ATT_HIST cah
126 WHERE cah.person_id = p_person_id AND
127 cah.ca_sequence_number = p_transfer_ca_sequence_number;
128 BEGIN
129 -- Set the default message number
130 p_message_name := null;
131 --Check for existence of transfer session details
132 IF igs_as_val_suaap.genp_val_sdtt_sess(
133 cst_enrp_ins_ca_trnsfr) = TRUE THEN
134 --Not processing course transfer Candidature, finish processing
135 RETURN TRUE;
136 END IF;
137 --Validate that Candidature attendance history details have not already
138 -- been transferred
139 OPEN c_cah;
140 FETCH c_cah INTO v_cah_exists;
141 IF c_cah%FOUND THEN
142 CLOSE c_cah;
143 --Candidature attendance histories already exist, cannot transfer
144 p_message_name := 'IGS_RE_CAND_ATTN_HIST_EXISTS';
145 RETURN FALSE;
146 END IF;
147 CLOSE c_cah;
148 SAVEPOINT sp_candidature_att_hist;
149 --Insert Candidature attendance histories
150 FOR v_cah1_rec IN c_cah1 LOOP
151 BEGIN
152 DECLARE
153 l_rowid VARCHAR2(25);
154 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
155 BEGIN
156 IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW(
157 X_ROWID => l_rowid,
158 X_org_id => l_org_id,
159 X_person_id => p_person_id,
160 X_ca_sequence_number => p_ca_sequence_number,
161 X_sequence_number => v_cah1_rec.sequence_number,
162 X_hist_start_dt => v_cah1_rec.hist_start_dt ,
163 X_hist_end_dt => v_cah1_rec.hist_end_dt,
164 X_attendance_type => v_cah1_rec.attendance_type,
165 X_attendance_percentage => v_cah1_rec.attendance_percentage);
166 END;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 ROLLBACK TO sp_candidature_att_hist;
171 p_message_name := 'IGS_RE_CANT_INS_CAND_ATTN_HIS';
172 RETURN FALSE;
173 END;
174 END LOOP; --(IGS_RE_CDT_ATT_HIST)
175 -- Return the default value
176 RETURN TRUE;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF c_cah%ISOPEN THEN
180 CLOSE c_cah;
181 END IF;
182 IF c_cah1%ISOPEN THEN
183 CLOSE c_cah1;
184 END IF;
185 RAISE;
186 END;
187 EXCEPTION
188 WHEN OTHERS THEN
189 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
190 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_cah_trnsfr');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END enrp_ins_cah_trnsfr;
194 --
195 -- Insert CSC as part of course Transfer.
196 FUNCTION ENRP_INS_CSC_TRNSFR(
197 p_person_id IN NUMBER ,
198 p_ca_sequence_number IN NUMBER ,
199 p_transfer_ca_sequence_number IN NUMBER ,
200 p_message_name OUT NOCOPY VARCHAR2)
201 RETURN BOOLEAN AS
202
203 BEGIN -- enrp_ins_csc_trnsfr
204 --This module inserts research IGS_RE_CAND_SEO_CLS details as a result of IGS_PS_COURSE
205 -- transfer. All Candidature research supervisor details
206 --(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
207 --Candidature (p_person_id/p_ca_sequence_number).
208 DECLARE
209 v_csc_exists VARCHAR2(1);
210 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
211 CURSOR c_csc IS
212 SELECT 'x'
213 FROM IGS_RE_CAND_SEO_CLS csc
214 WHERE csc.person_id = p_person_id AND
215 csc.ca_sequence_number = p_ca_sequence_number;
216 CURSOR c_csc1 IS
217 SELECT csc.seo_class_cd,
218 csc.percentage
219 FROM IGS_RE_CAND_SEO_CLS csc
220 WHERE csc.person_id = p_person_id AND
221 csc.ca_sequence_number = p_transfer_ca_sequence_number;
222 BEGIN
223 -- Set the default message number
224 p_message_name := null;
225 --Check for existence of transfer session details
226 IF igs_as_val_suaap.genp_val_sdtt_sess(
227 cst_enrp_ins_ca_trnsfr) = TRUE THEN
228 --Not processing course transfer Candidature, finish processing
229 RETURN TRUE;
230 END IF;
231 --Validate that Candidature socio-economic classification details have not
232 -- already been transferred
233 OPEN c_csc;
234 FETCH c_csc INTO v_csc_exists;
235 IF c_csc%FOUND THEN
236 CLOSE c_csc;
237 --Candidature socio-economic classification details already exist,
238 -- cannot transfer
239 p_message_name := 'IGS_RE_CAND_ECON_CLASS_EXISTS';
240 RETURN FALSE;
241 END IF;
242 CLOSE c_csc;
243 SAVEPOINT sp_cand_seo_class;
244 --Insert Candidature socio-economic classification details
245 FOR v_csc1_rec IN c_csc1 LOOP
246 BEGIN
247 DECLARE
248 l_rowid VARCHAR2(25);
249 BEGIN
250 IGS_RE_CAND_SEO_CLS_PKG.INSERT_ROW(
251 x_rowid => l_rowid,
252 x_person_id => p_person_id,
253 x_ca_sequence_number => p_ca_sequence_number,
254 x_seo_class_cd => v_csc1_rec.seo_class_cd,
255 x_percentage =>v_csc1_rec.percentage );
256 END;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 ROLLBACK TO sp_cand_seo_class;
261 p_message_name := 'IGS_RE_CANT_INS_CAND_SCO-ECON';
262 RETURN FALSE;
263 END;
264 END LOOP;
265 -- Return the default value
266 RETURN TRUE;
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF c_csc%ISOPEN THEN
270 CLOSE c_csc;
271 END IF;
272 IF c_csc1%ISOPEN THEN
273 CLOSE c_csc1;
274 END IF;
275 RAISE;
276 END;
277 EXCEPTION
278 WHEN OTHERS THEN
279 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
280 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_csc_trnsfr');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END enrp_ins_csc_trnsfr;
284 --
285 -- Insert SCH as part of course Transfer.
286 FUNCTION ENRP_INS_MIL_TRNSFR(
287 p_person_id IN NUMBER ,
288 p_ca_sequence_number IN NUMBER ,
289 p_transfer_ca_sequence_number IN NUMBER ,
290 p_message_name OUT NOCOPY VARCHAR2)
291 RETURN BOOLEAN AS
292
293 BEGIN -- enrp_ins_mil_trnsfr
294 --This module inserts research milestone details as a result of COURSE
295 --transfer. All Candidature milestone details
296 --(p_person_id/p_transfer_ca_sequence_number) are to be copied to the new
297 --Candidature (p_person_id/p_ca_sequence_number).
298 DECLARE
299 v_mil_exists VARCHAR2(1);
300 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
301 CURSOR c_mil IS
302 SELECT 'x'
303 FROM IGS_PR_MILESTONE mil
304 WHERE mil.person_id = p_person_id AND
305 mil.sequence_number = p_ca_sequence_number;
306 CURSOR c_mil1 IS
307 SELECT mil.sequence_number,
308 mil.milestone_type,
309 mil.milestone_status,
310 mil.due_dt,
311 mil.description,
312 mil.actual_reached_dt,
313 mil.preced_sequence_number,
314 mil.ovrd_ntfctn_imminent_days,
315 mil.ovrd_ntfctn_reminder_days,
316 mil.ovrd_ntfctn_re_reminder_days,
317 mil.comments
318 FROM IGS_PR_MILESTONE mil
319 WHERE mil.person_id = p_person_id AND
320 mil.ca_sequence_number = p_transfer_ca_sequence_number
321 ORDER BY mil.due_dt;
322 BEGIN
323 -- Set the default message number
324 p_message_name := null;
325 --Check for existence of transfer session details
326 IF igs_as_val_suaap.genp_val_sdtt_sess(
327 'ENRP_INS_CA_TRNSFR') = TRUE THEN
328 --Not processing course transfer Candidature, finish processing
329 RETURN TRUE;
330 END IF;
331 --Validate that milestone details have not already been transferred
332 OPEN c_mil;
333 FETCH c_mil INTO v_mil_exists;
334 IF c_mil%FOUND THEN
335 CLOSE c_mil;
336 --milestone details already exist, cannot transfer
337 p_message_name := 'IGS_RE_MILESTONE_ALREADY_EXIS';
338 RETURN FALSE;
339 END IF;
340 CLOSE c_mil;
341 SAVEPOINT sp_milestone;
342 --Insert milestone
343 BEGIN --Insert IGS_PR_MILESTONE
344 FOR v_mil1_rec IN c_mil1 LOOP
345 DECLARE
346 l_rowid VARCHAR2(25);
347 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
348 BEGIN
349 IGS_PR_MILESTONE_PKG.INSERT_ROW(
350 x_rowid => l_rowid,
351 X_org_id => l_org_id,
352 x_person_id => p_person_id,
353 x_ca_sequence_number => p_ca_sequence_number,
354 x_sequence_number => v_mil1_rec.sequence_number,
355 x_milestone_type => v_mil1_rec.milestone_type,
356 x_milestone_status => v_mil1_rec.milestone_status,
357 x_due_dt => v_mil1_rec.due_dt,
358 x_description => v_mil1_rec.description,
359 x_actual_reached_dt => v_mil1_rec.actual_reached_dt,
360 x_preced_sequence_number => v_mil1_rec.preced_sequence_number,
361 x_ovrd_ntfctn_imminent_days => v_mil1_rec.ovrd_ntfctn_imminent_days,
362 x_ovrd_ntfctn_reminder_days => v_mil1_rec.ovrd_ntfctn_reminder_days,
363 x_ovrd_ntfctn_re_reminder_days => v_mil1_rec.ovrd_ntfctn_re_reminder_days,
364 x_comments => v_mil1_rec.comments );
365 END;
366
367 END LOOP; --(IGS_PR_MILESTONE)
368 EXCEPTION
369 WHEN OTHERS THEN -- (exception)
370 ROLLBACK TO sp_milestone;
371 p_message_name := 'IGS_RE_CANT_INSERT_MILESTONES';
372 RETURN FALSE;
373 END;
374 -- Return the default value
375 RETURN TRUE;
376 EXCEPTION
377 WHEN OTHERS THEN
378 IF c_mil%ISOPEN THEN
379 CLOSE c_mil;
380 END IF;
381 IF c_mil1%ISOPEN THEN
382 CLOSE c_mil1;
383 END IF;
384 RAISE;
385 END;
386 EXCEPTION
387 WHEN OTHERS THEN
388 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
389 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_mil_trnsfr');
390 IGS_GE_MSG_STACK.ADD;
391 App_Exception.Raise_Exception;
392 END enrp_ins_mil_trnsfr;
393 --
394 -- Insert RSUP as part of course Transfer.
395 FUNCTION ENRP_INS_RSUP_TRNSFR(
396 p_person_id IN NUMBER ,
397 p_ca_sequence_number IN NUMBER ,
398 p_transfer_ca_sequence_number IN NUMBER ,
399 p_message_name OUT NOCOPY VARCHAR2)
400 RETURN BOOLEAN AS
401
402 BEGIN --enrp_ins_rsup_trnsfr
403 DECLARE
404 v_rsup_exists VARCHAR2(1);
405 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
406 CURSOR c_rsup IS
407 SELECT 'x'
408 FROM IGS_RE_SPRVSR rsup
409 WHERE rsup.ca_person_id = p_person_id AND
410 rsup.ca_sequence_number = p_ca_sequence_number;
411 CURSOR c_rsup1 IS
412 SELECT rsup.person_id,
413 rsup.sequence_number,
414 rsup.start_dt,
415 rsup.end_dt,
416 rsup.research_supervisor_type,
417 rsup.supervisor_profession,
418 rsup.supervision_percentage,
419 rsup.funding_percentage,
420 rsup.org_unit_cd,
421 rsup.ou_start_dt,
422 rsup.replaced_person_id,
423 rsup.replaced_sequence_number,
424 rsup.comments
425 FROM IGS_RE_SPRVSR rsup
426 WHERE rsup.ca_person_id = p_person_id AND
427 rsup.ca_sequence_number = p_transfer_ca_sequence_number
428 ORDER BY rsup.start_dt;
429 BEGIN
430 -- Set the default message number
431 p_message_name := null;
432 --Check for existence of transfer session details
433 IF igs_as_val_suaap.genp_val_sdtt_sess(
434 cst_enrp_ins_ca_trnsfr) = TRUE THEN
435 --Not processing course transfer Candidature, finish processing
436 p_message_name := null;
437 RETURN TRUE;
438 END IF;
439 --Validate that IGS_RE_SPRVSR details have not already been transferred
440 OPEN c_rsup;
441 FETCH c_rsup INTO v_rsup_exists;
442 IF c_rsup%FOUND THEN
443 CLOSE c_rsup;
444 --IGS_RE_SPRVSR details already exist, cannot transfer
445 p_message_name := 'IGS_RE_SUPERVISORS_EXISTS';
446 RETURN FALSE;
447 END IF;
448 CLOSE c_rsup;
449 SAVEPOINT sp_research_supervisor;
450 --Insert IGS_RE_SPRVSR details
451 FOR v_rsup1_rec IN c_rsup1 LOOP
452 BEGIN
453 DECLARE
454 l_rowid VARCHAR2(25);
455 BEGIN
456 IGS_RE_SPRVSR_PKG.INSERT_ROW(
457 x_rowid => l_rowid,
458 x_ca_person_id => p_person_id,
459 x_ca_sequence_number =>p_ca_sequence_number ,
460 x_person_id => v_rsup1_rec.person_id,
461 x_sequence_number => v_rsup1_rec.sequence_number,
462 x_start_dt => v_rsup1_rec.start_dt,
463 x_end_dt => v_rsup1_rec.end_dt,
464 x_research_supervisor_type => v_rsup1_rec.research_supervisor_type,
465 x_supervisor_profession => v_rsup1_rec.supervisor_profession,
466 x_supervision_percentage => v_rsup1_rec.supervision_percentage,
467 x_funding_percentage => v_rsup1_rec.funding_percentage,
468 x_org_unit_cd => v_rsup1_rec.org_unit_cd,
469 x_ou_start_dt => v_rsup1_rec.ou_start_dt ,
470 x_replaced_person_id => v_rsup1_rec.replaced_person_id,
471 x_replaced_sequence_number => v_rsup1_rec.replaced_sequence_number,
472 x_comments => v_rsup1_rec.comments);
473
474 END;
475
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 ROLLBACK TO sp_research_supervisor;
480 p_message_name := 'IGS_RE_SUPERVISORS_CANT_INSER';
481 RETURN FALSE;
482 END;
483 END LOOP; --(IGS_RE_SPRVSR)
484 -- Return the default value
485 RETURN TRUE;
486 EXCEPTION
487 WHEN OTHERS THEN
488 IF c_rsup%ISOPEN THEN
489 CLOSE c_rsup;
490 END IF;
491 IF c_rsup1%ISOPEN THEN
492 CLOSE c_rsup1;
493 END IF;
494 RAISE;
495 END;
496 EXCEPTION
497 WHEN OTHERS THEN
498 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
499 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_rsup_trnsfr');
500 IGS_GE_MSG_STACK.ADD;
501 App_Exception.Raise_Exception;
502 END enrp_ins_rsup_trnsfr;
503 --
504 -- Insert SCH as part of course Transfer.
505 FUNCTION ENRP_INS_SCH_TRNSFR(
506 p_person_id IN NUMBER ,
507 p_ca_sequence_number IN NUMBER ,
508 p_transfer_ca_sequence_number IN NUMBER ,
509 p_message_name OUT NOCOPY VARCHAR2)
510 RETURN BOOLEAN AS
511
512 BEGIN -- enrp_ins_sch_trnsfr
513 --This module inserts research IGS_RE_SCHOLARSHIP details as a
514 --result of course transfer.
515 DECLARE
516 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
517 v_sch_exists VARCHAR2(1);
518 v_sch1_scholarship_type IGS_RE_SCHOLARSHIP.scholarship_type%TYPE;
519 v_sch1_start_dt IGS_RE_SCHOLARSHIP.start_dt%TYPE;
520 v_sch1_end_dt IGS_RE_SCHOLARSHIP.end_dt%TYPE;
521 v_sch1_dollar_value IGS_RE_SCHOLARSHIP.dollar_value%TYPE;
522 v_sch1_description IGS_RE_SCHOLARSHIP.description%TYPE;
523 v_sch1_other_benefits IGS_RE_SCHOLARSHIP.other_benefits%TYPE;
524 v_sch1_conditions IGS_RE_SCHOLARSHIP.conditions%TYPE;
525 CURSOR c_sch IS
526 SELECT 'x'
527 FROM IGS_RE_SCHOLARSHIP sch
528 WHERE sch.person_id = p_person_id AND
529 sch.ca_sequence_number = p_ca_sequence_number;
530 CURSOR c_sch1 IS
531 SELECT sch.scholarship_type,
532 sch.start_dt,
533 sch.end_dt,
534 sch.dollar_value,
535 sch.description,
536 sch.other_benefits,
537 sch.conditions
538 FROM IGS_RE_SCHOLARSHIP sch
539 WHERE sch.person_id = p_person_id AND
540 sch.ca_sequence_number = p_transfer_ca_sequence_number
541 ORDER BY sch.start_dt;
542 BEGIN
543 -- Set the default message number
544 p_message_name := null;
545 --Check for existence of transfer session details
546 IF igs_as_val_suaap.genp_val_sdtt_sess(
547 cst_enrp_ins_ca_trnsfr) = TRUE THEN
548 --Not processing course transfer Candidature, finish processing
549 RETURN TRUE;
550 END IF;
551 --Validate that IGS_RE_SCHOLARSHIP details have not already been transferred
552 OPEN c_sch;
553 FETCH c_sch INTO v_sch_exists;
554 IF c_sch%FOUND THEN
555 CLOSE c_sch;
556 --IGS_RE_SCHOLARSHIP details already exist, cannot transfer
557 p_message_name := 'IGS_RE_SCHOLARSHIP_EXISTS';
558 RETURN FALSE;
559 END IF;
560 CLOSE c_sch;
561 SAVEPOINT sp_scholarship;
562 BEGIN --Insert IGS_RE_SCHOLARSHIP details
563 FOR v_sch1_rec IN c_sch1 LOOP
564
565 DECLARE
566 l_rowid VARCHAR2(25);
567 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
568 BEGIN
569 IGS_RE_SCHOLARSHIP_PKG.INSERT_ROW(
570 x_rowid => l_rowid,
571 X_org_id => l_org_id,
572 x_person_id => p_person_id,
573 x_ca_sequence_number => p_ca_sequence_number,
574 x_scholarship_type => v_sch1_rec.scholarship_type,
575 x_start_dt => v_sch1_rec.start_dt,
576 x_end_dt => v_sch1_rec.end_dt,
577 x_dollar_value => v_sch1_rec.dollar_value,
578 x_description => v_sch1_rec.description,
579 x_other_benefits => v_sch1_rec.other_benefits,
580 x_conditions => v_sch1_rec.conditions );
581
582 END;
583
584 END LOOP; --(IGS_RE_SCHOLARSHIP)
585 EXCEPTION
586 WHEN OTHERS THEN -- (exception)
587 ROLLBACK TO sp_scholarship;
588 p_message_name :='IGS_RE_CANT_INSERT_SCHOLARSHI';
589 RETURN FALSE;
590 END;
591 -- Return the default value
592 RETURN TRUE;
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF c_sch%ISOPEN THEN
596 CLOSE c_sch;
597 END IF;
598 IF c_sch1%ISOPEN THEN
599 CLOSE c_sch1;
600 END IF;
601 RAISE;
602 END;
603 EXCEPTION
604 WHEN OTHERS THEN
605 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
606 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_sch_trnsfr');
607 IGS_GE_MSG_STACK.ADD;
608 App_Exception.Raise_Exception;
609 END enrp_ins_sch_trnsfr;
610 --
611 -- Insert THE as part of course Transfer.
612 FUNCTION ENRP_INS_THE_TRNSFR(
613 p_person_id IN NUMBER ,
614 p_ca_sequence_number IN NUMBER ,
615 p_transfer_ca_sequence_number IN NUMBER ,
616 p_message_name OUT NOCOPY VARCHAR2)
617 RETURN BOOLEAN AS
618
619 v_user varchar2(30);
620 BEGIN -- enrp_ins_the_trnsfr
621 --This module inserts research THESIS details as a result of COURSE transfer.
622 --All Candidature THESIS details (p_person_id/p_transfer_ca_sequence_number)
623 --are to be copied to the new Candidature (p_person_id/p_ca_sequence_number).
624 DECLARE
625 v_return_val BOOLEAN;
626 v_the_exists VARCHAR2(1);
627 v_the_sequence_number IGS_RE_THESIS.sequence_number%TYPE;
628 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(20) := 'ENRP_INS_CA_TRNSFR';
629 CURSOR c_next_val IS
630 SELECT IGS_RE_THESIS_SEQ_NUM_S.nextval
631 FROM dual;
632 CURSOR c_the IS
633 SELECT 'x'
634 FROM IGS_RE_THESIS thes
635 WHERE thes.person_id = p_person_id AND
636 thes.ca_sequence_number = p_ca_sequence_number;
637 CURSOR c_the2 IS
638 SELECT thes.sequence_number,
639 thes.title,
640 thes.final_title_ind,
641 thes.short_title,
642 thes.abbreviated_title,
643 thes.thesis_result_cd,
644 thes.expected_submission_dt,
645 thes.library_lodgement_dt,
646 thes.library_catalogue_number,
647 thes.embargo_expiry_dt,
648 thes.thesis_format,
649 thes.embargo_details,
650 thes.thesis_topic,
651 thes.citation,
652 thes.comments
653 FROM IGS_RE_THESIS thes
654 WHERE thes.person_id = p_person_id AND
655 thes.ca_sequence_number = p_transfer_ca_sequence_number AND
656 thes.logical_delete_dt IS NULL;
657 CURSOR c_tex(
658 cp_the_sequence_number IGS_RE_THESIS.sequence_number%TYPE) IS
659 SELECT tex.the_sequence_number,
660 tex.creation_dt,
661 tex.submission_dt,
662 tex.thesis_exam_type,
663 tex.thesis_panel_type,
664 tex.tracking_id,
665 tex.thesis_result_cd
666 FROM IGS_RE_THESIS_EXAM tex
667 WHERE tex.person_id = p_person_id AND
668 tex.ca_sequence_number = p_transfer_ca_sequence_number AND
669 tex.the_sequence_number = cp_the_sequence_number
670 ORDER BY tex.creation_dt ASC;
671 CURSOR c_teh (
672 cp_tex_the_sequence_number IGS_RE_THESIS_EXAM.the_sequence_number%TYPE,
673 cp_tex_creation_dt IGS_RE_THESIS_EXAM.creation_dt%TYPE) IS
674 SELECT teh.hist_start_dt,
675 teh.hist_end_dt,
676 teh.thesis_result_cd
677 FROM IGS_RE_THS_EXAM_HIST teh
678 WHERE teh.person_id = p_person_id AND
679 teh.ca_sequence_number = p_transfer_ca_sequence_number AND
680 teh.the_sequence_number = cp_tex_the_sequence_number AND
681 teh.creation_dt = cp_tex_creation_dt AND
682 teh.thesis_result_cd IS NOT NULL
683 ORDER BY teh.hist_end_dt DESC;
684 CURSOR c_tpm(
685 cp_tex_the_sequence_number IGS_RE_THESIS_EXAM.the_sequence_number%TYPE,
686 cp_tex_creation_dt IGS_RE_THESIS_EXAM.creation_dt%TYPE) IS
687 SELECT tpm.the_sequence_number,
688 tpm.creation_dt,
689 tpm.person_id,
690 tpm.panel_member_type,
691 tpm.confirmed_dt,
692 tpm.declined_dt,
693 tpm.anonymity_ind,
694 tpm.thesis_result_cd,
695 tpm.paid_dt,
696 tpm.tracking_id,
697 tpm.recommendation_summary
698 FROM IGS_RE_THS_PNL_MBR tpm
699 WHERE tpm.ca_person_id = p_person_id AND
700 tpm.ca_sequence_number = p_transfer_ca_sequence_number AND
701 tpm.the_sequence_number = cp_tex_the_sequence_number AND
702 tpm.creation_dt = cp_tex_creation_dt;
703 CURSOR c_tpmh (
704 cp_the_sequence_number IGS_RE_THS_PNL_MR_HS.the_sequence_number%TYPE,
705 cp_tpm_creation_dt IGS_RE_THS_PNL_MR_HS.creation_dt%TYPE,
706 cp_tpm_person_id IGS_RE_THS_PNL_MR_HS.person_id%TYPE) IS
707 SELECT tpmh.hist_start_dt,
708 tpmh.hist_end_dt,
709 tpmh.thesis_result_cd
710 FROM IGS_RE_THS_PNL_MR_HS tpmh
711 WHERE tpmh.ca_person_id = p_person_id AND
712 tpmh.ca_sequence_number = p_transfer_ca_sequence_number AND
713 tpmh.the_sequence_number = cp_the_sequence_number AND
714 tpmh.creation_dt = cp_tpm_creation_dt AND
715 tpmh.person_id = cp_tpm_person_id AND
716 tpmh.thesis_result_cd IS NOT NULL
717 ORDER BY tpmh.hist_end_dt DESC;
718 v_teh1_rec c_teh%ROWTYPE;
719 v_tpmh1_rec c_tpmh%ROWTYPE;
720 BEGIN
721 -- Set the defaults
722 p_message_name := null;
723 v_return_val := TRUE;
724 --Check for existence of transfer session details
725 IF igs_as_val_suaap.genp_val_sdtt_sess(
726 cst_enrp_ins_ca_trnsfr) THEN
727 --Not processing course transfer Candidature, finish processing
728 RETURN TRUE;
729 END IF;
730 --Validate that THESIS details have not already been transferred
731 OPEN c_the;
732 FETCH c_the INTO v_the_exists;
733 IF c_the%FOUND THEN
734 CLOSE c_the;
735 --THESIS details already exist, cannot transfer
736 p_message_name := 'IGS_RE_THESIS_ALREADY_EXISTS';
737 RETURN FALSE;
738 END IF;
739 CLOSE c_the;
740 SAVEPOINT sp_thesis;
741 --Insert IGS_RE_THESIS
742 FOR v_the1_rec IN c_the2 LOOP
743 BEGIN
744 OPEN c_next_val;
745 FETCH c_next_val INTO v_the_sequence_number;
746 CLOSE c_next_val;
747 DECLARE
748 l_rowid VARCHAR2(25);
749 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
750 BEGIN
751 IGS_RE_THESIS_PKG.INSERT_ROW(
752 x_rowid => l_rowid,
753 X_org_id => l_org_id,
754 x_person_id =>p_person_id,
755 x_ca_sequence_number => p_ca_sequence_number,
756 x_sequence_number => v_the_sequence_number,
757 x_title=> v_the1_rec.title,
758 x_final_title_ind => v_the1_rec.final_title_ind,
759 x_short_title => v_the1_rec.short_title,
760 x_abbreviated_title => v_the1_rec.abbreviated_title,
761 x_thesis_result_cd => v_the1_rec.thesis_result_cd,
762 x_expected_submission_dt => v_the1_rec.expected_submission_dt,
763 x_library_lodgement_dt => v_the1_rec.library_lodgement_dt,
764 x_library_catalogue_number => v_the1_rec.library_catalogue_number,
765 x_embargo_expiry_dt => v_the1_rec.embargo_expiry_dt,
766 x_thesis_format => v_the1_rec.thesis_format,
767 x_embargo_details => v_the1_rec.embargo_details,
768 x_thesis_topic => v_the1_rec.thesis_topic,
769 x_citation => v_the1_rec.citation,
770 x_comments => v_the1_rec.comments,
771 x_logical_delete_dt => null);
772 END;
773
774 --Insert IGS_RE_THESIS exam(s)
775 FOR v_tex1_rec IN c_tex(
776 v_the1_rec.sequence_number) LOOP
777
778
779
780 DECLARE
781 l_rowid VARCHAR2(25);
782 BEGIN
783
784 IGS_RE_THESIS_EXAM_PKG.INSERT_ROW(
785 x_rowid => l_rowid,
786 x_person_id => p_person_id,
787 x_ca_sequence_number => p_ca_sequence_number,
788 x_the_sequence_number => v_the_sequence_number,
789 x_creation_dt => v_tex1_rec.creation_dt,
790 x_submission_dt => v_tex1_rec.submission_dt,
791 x_thesis_exam_type => v_tex1_rec.thesis_exam_type,
792 x_thesis_panel_type => v_tex1_rec.thesis_panel_type,
793 x_tracking_id => v_tex1_rec.tracking_id,
794 x_thesis_result_cd => v_tex1_rec.thesis_result_cd);
795
796 END;
797 --Insert result code IGS_RE_THESIS exam history if required
798 --Note: IGS_RE_THESIS result code date(see RESF3700) is derived from audit details
799 --And must be retained with the transfer.
800 IF v_tex1_rec.thesis_result_cd IS NOT NULL THEN
801 OPEN c_teh(
802 v_tex1_rec.the_sequence_number,
803 v_tex1_rec.creation_dt);
804 FETCH c_teh INTO v_teh1_rec;
805 IF c_teh%NOTFOUND THEN
806 CLOSE c_teh;
807 ELSE
808 CLOSE c_teh;
809 v_user := fnd_global.user_name;
810
811 DECLARE
812 l_rowid VARCHAR2(25);
813 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
814 BEGIN
815
816 IGS_RE_THS_EXAM_HIST_PKG.INSERT_ROW (
817 x_rowid => l_rowid,
818 X_org_id => l_org_id,
819 x_person_id => p_person_id ,
820 x_ca_sequence_number => p_ca_sequence_number,
821 x_the_sequence_number => v_the_sequence_number,
822 x_creation_dt => v_tex1_rec.creation_dt,
823 x_hist_start_dt => v_teh1_rec.hist_start_dt,
824 x_hist_end_dt => v_teh1_rec.hist_end_dt,
825 x_hist_who => v_user,
826 x_submission_dt => NULL,
827 x_thesis_exam_type => NULL,
828 x_thesis_panel_type => NULL,
829 x_tracking_id => NULL,
830 x_thesis_result_cd => v_teh1_rec.thesis_result_cd );
831 END;
832
833
834 END IF; -- c_teh1%NOTFOUND
835 END IF; -- if result_cd is not null
836 --Insert IGS_RE_THESIS panel member(s)
837 FOR v_tpm1_rec IN c_tpm(
838 v_tex1_rec.the_sequence_number,
839 v_tex1_rec.creation_dt) LOOP
840 DECLARE
841 l_rowid VARCHAR2(25);
842 BEGIN
843
844 IGS_RE_THS_PNL_MBR_PKG.INSERT_ROW(
845 x_rowid => l_rowid,
846 x_ca_person_id => p_person_id,
847 x_ca_sequence_number => p_ca_sequence_number,
848 x_the_sequence_number => v_the_sequence_number,
849 x_creation_dt => v_tpm1_rec.creation_dt,
850 x_person_id => v_tpm1_rec.person_id,
851 x_panel_member_type => v_tpm1_rec.panel_member_type,
852 x_confirmed_dt => v_tpm1_rec.confirmed_dt,
853 x_declined_dt => v_tpm1_rec.declined_dt,
854 x_anonymity_ind => v_tpm1_rec.anonymity_ind,
855 x_thesis_result_cd => v_tpm1_rec.thesis_result_cd,
856 x_paid_dt => v_tpm1_rec.paid_dt,
857 x_tracking_id => v_tpm1_rec.tracking_id,
858 x_recommendation_summary => v_tpm1_rec.recommendation_summary);
859 END;
860
861
862 --Insert result code IGS_RE_THESIS panel member history if required
863 --Note: IGS_RE_THESIS result code date ( see RESF3700) is derived from audit details
864 --And must be retained with the transfer.
865 IF v_tpm1_rec.thesis_result_cd IS NOT NULL THEN
866 OPEN c_tpmh(
867 v_tpm1_rec.the_sequence_number,
868 v_tpm1_rec.creation_dt,
869 v_tpm1_rec.person_id);
870 FETCH c_tpmh INTO v_tpmh1_rec;
871 IF c_tpmh%NOTFOUND THEN
872 CLOSE c_tpmh;
873 ELSE
874 CLOSE c_tpmh;
875 v_user := fnd_global.user_name;
876 DECLARE
877 l_rowid VARCHAR2(25);
878 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
879 BEGIN
880
881 IGS_RE_THS_PNL_MR_HS_PKG.INSERT_ROW (
882 x_rowid => l_rowid,
883 X_org_id => l_org_id,
884 x_ca_person_id=> p_person_id,
885 x_ca_sequence_number => p_ca_sequence_number,
886 x_the_sequence_number => v_the_sequence_number,
887 x_creation_dt => v_tpm1_rec.creation_dt,
888 x_person_id => v_tpm1_rec.person_id,
889 x_hist_start_dt => v_tpmh1_rec.hist_start_dt,
890 x_hist_end_dt => v_tpmh1_rec.hist_end_dt,
891 x_hist_who => v_user,
892 x_panel_member_type => NULL,
893 x_confirmed_dt => NULL,
894 x_declined_dt => NULL,
895 x_anonymity_ind => 'N',
896 x_thesis_result_cd => v_tpm1_rec.thesis_result_cd,
897 x_paid_dt => NULL,
898 x_tracking_id => NULL,
899 x_recommendation_summary => NULL);
900 END;
901
902 END IF; -- c_tpmh%NOTFOUND
903 END IF; -- if IGS_RE_THESIS result_cd IS NOT NULL
904 END LOOP; --(IGS_RE_THS_PNL_MBR)
905 END LOOP; --(IGS_RE_THESIS_EXAM)
906 EXCEPTION
907 WHEN OTHERS THEN
908 ROLLBACK TO sp_thesis;
909 p_message_name := 'IGS_RE_CANT_INSERT_THESIS_DET';
910 v_return_val := FALSE;
911 EXIT;
912 END;
913 END LOOP; --(IGS_RE_THESIS)
914 RETURN v_return_val;
915 EXCEPTION
916 WHEN OTHERS THEN
917 IF c_the%ISOPEN THEN
918 CLOSE c_the;
919 END IF;
920 IF c_the2%ISOPEN THEN
921 CLOSE c_the2;
922 END IF;
923 IF c_tex%ISOPEN THEN
924 CLOSE c_tex;
925 END IF;
926 IF c_tpm%ISOPEN THEN
927 CLOSE c_tpm;
928 END IF;
929 IF c_teh%ISOPEN THEN
930 CLOSE c_teh;
931 END IF;
932 IF c_tpmh%ISOPEN THEN
933 CLOSE c_tpmh;
934 END IF;
935 IF c_next_val%ISOPEN THEN
936 CLOSE c_next_val;
937 END IF;
938 RAISE;
939 END;
940 EXCEPTION
941 WHEN OTHERS THEN
942 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
943 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_the_trnsfr');
944 IGS_GE_MSG_STACK.ADD;
945 App_Exception.Raise_Exception;
946 END enrp_ins_the_trnsfr;
947 --
948 -- Insert Research Candidature as part of course Transfer.
949 FUNCTION ENRP_INS_CA_TRNSFR(
950 p_person_id IN NUMBER ,
951 p_sca_course_cd IN VARCHAR2 ,
952 p_acai_admission_appl_number IN NUMBER ,
953 p_acai_nominated_course_cd IN VARCHAR2 ,
954 p_acai_sequence_number IN NUMBER ,
955 p_transfer_course_cd IN VARCHAR2 ,
956 p_parent IN VARCHAR2 ,
957 p_message_name OUT NOCOPY VARCHAR2)
958 RETURN BOOLEAN AS
959
960 BEGIN -- enrp_ins_ca_trnsfr
961 DECLARE
962 v_ca_to_exists VARCHAR2(1);
963 cst_ca_to_sca CONSTANT VARCHAR2(3):= 'SCA';
964 cst_ca_to_acai CONSTANT VARCHAR2(4):= 'ACAI';
965 cst_enrp_ins_ca_trnsfr CONSTANT VARCHAR2(18) := 'ENRP_INS_CA_TRNSFR';
966 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
967 v_ca_govt_type_of_activity_cd IGS_RE_CANDIDATURE.govt_type_of_activity_cd%TYPE;
968 v_ca_research_topic IGS_RE_CANDIDATURE.research_topic%TYPE;
969 v_ca_industry_links IGS_RE_CANDIDATURE.industry_links%TYPE;
970 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
971 v_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
972 v_message_name Varchar2(30);
973 v_person_id IGS_RE_CANDIDATURE.person_id%TYPE;
974 CURSOR c_ca IS
975 SELECT ca.attendance_percentage,
976 ca.govt_type_of_activity_cd,
977 ca.research_topic,
978 ca.industry_links,
979 ca.sequence_number
980 FROM IGS_RE_CANDIDATURE ca
981 WHERE ca.person_id = p_person_id AND
982 ca.sca_course_cd = p_transfer_course_cd;
983 CURSOR c_ca_to IS
984 SELECT 'x'
985 FROM IGS_RE_CANDIDATURE ca_to
986 WHERE ca_to.person_id = p_person_id AND
987 (ca_to.sca_course_cd = p_sca_course_cd AND
988 p_parent = cst_ca_to_sca) OR
989 (ca_to.acai_admission_appl_number = p_acai_admission_appl_number AND
990 ca_to.acai_nominated_course_cd = p_acai_nominated_course_cd AND
991 ca_to.acai_sequence_number = p_acai_sequence_number AND
992 p_parent = cst_ca_to_acai);
993 CURSOR c_dual IS
994 SELECT IGS_RE_CANDIDATURE_SEQ_NUM_S.nextval
995 FROM dual;
996 BEGIN
997 -- Set the default message number
998 p_message_name := null;
999 --Validate that Candidature details exist against the transferring 'from'
1000 -- course attempt
1001 OPEN c_ca;
1002 FETCH c_ca INTO v_attendance_percentage,
1003 v_ca_govt_type_of_activity_cd,
1004 v_ca_research_topic,
1005 v_ca_industry_links,
1006 v_sequence_number;
1007 IF c_ca%NOTFOUND THEN
1008 CLOSE c_ca;
1009 --Candidature details do not exist, cannot transfer
1010 RETURN TRUE;
1011 END IF;
1012 CLOSE c_ca;
1013 --Validate that Candidature details do not already exist for the
1014 --transferring 'to' COURSE
1015 OPEN c_ca_to;
1016 FETCH c_ca_to INTO v_ca_to_exists;
1017 IF c_ca_to%FOUND THEN
1018 CLOSE c_ca_to;
1019 --Candidature details already exist, cannot transfer
1020 p_message_name := 'IGS_RE_CAND_DETAILS_EXISTS';
1021 RETURN FALSE;
1022 END IF;
1023 CLOSE c_ca_to;
1024 SAVEPOINT sp_candidature;
1025 --Disable trigger validation
1026 IGS_GE_MNT_SDTT.genp_del_sdtt(cst_enrp_ins_ca_trnsfr);
1027
1028 -- Inserts a record into the s_disable_table_trigger
1029 -- database table.
1030 DECLARE
1031 l_rowid VARCHAR2(25);
1032 BEGIN
1033
1034 IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
1035 X_ROWID => L_ROWID ,
1036 X_TABLE_NAME =>cst_enrp_ins_ca_trnsfr,
1037 X_SESSION_ID => userenv('SESSIONID'),
1038 x_mode => 'R'
1039 );
1040 END;
1041
1042 --Get next sequence number
1043 OPEN c_dual;
1044 FETCH c_dual INTO v_ca_sequence_number;
1045 --Insert Candidature
1046 BEGIN
1047 DECLARE
1048 l_rowid VARCHAR2(25);
1049 l_org_id NUMBER(15) := IGS_GE_GEN_003.GET_ORG_ID;
1050 BEGIN
1051
1052 IGS_RE_CANDIDATURE_PKG.INSERT_ROW(
1053 x_rowid => l_rowid,
1054 X_org_id => l_org_id,
1055 x_person_id => p_person_id,
1056 x_sequence_number => v_ca_sequence_number,
1057 x_sca_course_cd => p_sca_course_cd,
1058 x_acai_admission_appl_number => p_acai_admission_appl_number,
1059 x_acai_nominated_course_cd => p_acai_nominated_course_cd,
1060 x_acai_sequence_number => p_acai_sequence_number,
1061 x_attendance_percentage => v_attendance_percentage,
1062 x_govt_type_of_activity_cd => v_ca_govt_type_of_activity_cd,
1063 x_max_submission_dt => NULL,
1064 x_min_submission_dt => NULL,
1065 x_research_topic => v_ca_research_topic,
1066 x_industry_links => v_ca_industry_links );
1067 END;
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 ROLLBACK TO sp_candidature;
1072 p_message_name := 'IGS_RE_CANT_INS_CAND_DETAILS';
1073 RETURN FALSE;
1074 END;
1075 --Insert Candidature attendance history(s)
1076 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_cah_trnsfr(
1077 p_person_id,
1078 v_ca_sequence_number,
1079 v_sequence_number,
1080 v_message_name) = FALSE THEN
1081 ROLLBACK TO sp_candidature;
1082 p_message_name := v_message_name;
1083 RETURN FALSE;
1084 END IF;
1085 --Insert Candidature field of study(s)
1086 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_cafostrnsfr(
1087 p_person_id,
1088 v_ca_sequence_number,
1089 v_sequence_number,
1090 v_message_name) = FALSE THEN
1091 ROLLBACK TO sp_candidature;
1092 p_message_name := v_message_name;
1093 RETURN FALSE;
1094 END IF;
1095 --Insert Candidature socio-economic classification code(s)
1096 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_csc_trnsfr(
1097 p_person_id,
1098 v_ca_sequence_number,
1099 v_sequence_number,
1100 v_message_name) = FALSE THEN
1101 ROLLBACK TO sp_candidature;
1102 p_message_name := v_message_name;
1103 RETURN FALSE;
1104 END IF;
1105 --Insert Candidature Thesis(s)
1106 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_the_trnsfr(
1107 p_person_id,
1108 v_ca_sequence_number,
1109 v_sequence_number,
1110 v_message_name) = FALSE THEN
1111 ROLLBACK TO sp_candidature;
1112 p_message_name := v_message_name;
1113 RETURN FALSE;
1114 END IF;
1115 --Insert Candidature research supervisor(s)
1116 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_rsup_trnsfr(
1117 p_person_id,
1118 v_ca_sequence_number,
1119 v_sequence_number,
1120 v_message_name) = FALSE THEN
1121 Rollback to sp_candidature;
1122 p_message_name := v_message_name;
1123 RETURN FALSE;
1124 END IF;
1125 --Insert Candidature milestone(s)
1126 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_mil_trnsfr(
1127 p_person_id,
1128 v_ca_sequence_number,
1129 v_sequence_number,
1130 v_message_name) = FALSE THEN
1131 ROLLBACK TO sp_candidature;
1132 p_message_name := v_message_name;
1133 RETURN FALSE;
1134 END IF;
1135 --Insert Candidature research scholarship(s)
1136 IF IGS_EN_INS_CA_TRNSFR.enrp_ins_sch_trnsfr(
1137 p_person_id,
1138 v_ca_sequence_number,
1139 v_sequence_number,
1140 v_message_name) = FALSE THEN
1141 ROLLBACK TO sp_candidature;
1142 p_message_name := v_message_name;
1143 RETURN FALSE;
1144 END IF;
1145 --Enable trigger validation
1146 IGS_GE_MNT_SDTT.genp_del_sdtt(cst_enrp_ins_ca_trnsfr);
1147 -- Return the default value
1148 RETURN TRUE;
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 IF c_ca%ISOPEN THEN
1152 CLOSE c_ca;
1153 END IF;
1154 IF c_ca_to%ISOPEN THEN
1155 CLOSE c_ca_to;
1156 END IF;
1157 IF c_dual%ISOPEN THEN
1158 CLOSE c_dual;
1159 END IF;
1160 RAISE;
1161 END;
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1165 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_INS_CA_TRNSFR.enrp_ins_ca_trnsfr');
1166 IGS_GE_MSG_STACK.ADD;
1167 App_Exception.Raise_Exception;
1168 END enrp_ins_ca_trnsfr;
1169 END igs_en_ins_ca_trnsfr;