[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_GEN_001
Source
1 PACKAGE BODY igs_gr_gen_001 AS
2 /* $Header: IGSGR13B.pls 120.3 2006/02/21 01:00:29 sepalani noship $ */
3 --
4 g_module_head CONSTANT VARCHAR2(40) := 'igs.plsql.igs_gr_gen_001.';
5 --
6 -- Deletes the Graduand Award Ceremony History records.
7 --
8 FUNCTION grdp_del_gac_hist (
9 p_person_id IN igs_gr_awd_crmn.person_id%TYPE,
10 p_create_dt IN igs_gr_awd_crmn.create_dt%TYPE,
11 p_grd_cal_type IN igs_gr_awd_crmn.grd_cal_type%TYPE,
12 p_grd_ci_sequence_number IN igs_gr_awd_crmn.grd_ci_sequence_number%TYPE,
13 p_ceremony_number IN igs_gr_awd_crmn.ceremony_number%TYPE,
14 p_award_course_cd IN igs_gr_awd_crmn.award_course_cd%TYPE,
15 p_award_crs_version_number IN igs_gr_awd_crmn.award_crs_version_number%TYPE,
16 p_award_cd IN igs_gr_awd_crmn.award_cd%TYPE,
17 p_message_name OUT NOCOPY VARCHAR2
18 ) RETURN BOOLEAN AS
19 BEGIN -- grdp_del_gac_hist
20 --
21 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
22 fnd_log.string (
23 fnd_log.level_procedure, g_module_head || 'grdp_del_gac_hist.begin',
24 'In Params: p_person_id=>' || p_person_id || ';' ||
25 'p_create_dt=>' || p_create_dt || ';' ||
26 'p_grd_cal_type=>' || p_grd_cal_type || ';' ||
27 'p_grd_ci_sequence_number=>' || p_grd_ci_sequence_number || ';' ||
28 'p_ceremony_number=>' || p_ceremony_number || ';' ||
29 'p_award_course_cd=>' || p_award_course_cd || ';' ||
30 'p_award_crs_version_number=>' || p_award_crs_version_number || ';' ||
31 'p_award_cd=>' || p_award_cd || ';'
32 );
33 END IF;
34 --
35 DECLARE
36 CURSOR c_gach (
37 cp_person_id igs_gr_awd_crmn.person_id%TYPE,
38 cp_create_dt igs_gr_awd_crmn.create_dt%TYPE,
39 cp_grd_cal_type igs_gr_awd_crmn.grd_cal_type%TYPE,
40 cp_grd_ci_sequence_number igs_gr_awd_crmn.grd_ci_sequence_number%TYPE,
41 cp_ceremony_number igs_gr_awd_crmn.ceremony_number%TYPE,
42 cp_award_course_cd igs_gr_awd_crmn.award_course_cd%TYPE,
43 cp_award_crs_version_number igs_gr_awd_crmn.award_crs_version_number%TYPE,
44 cp_award_cd igs_gr_awd_crmn.award_cd%TYPE
45 ) IS
46 SELECT person_id
47 FROM igs_gr_awd_crmn_hist
48 WHERE person_id = cp_person_id
49 AND create_dt = cp_create_dt
50 AND grd_cal_type = cp_grd_cal_type
51 AND grd_ci_sequence_number = cp_grd_ci_sequence_number
52 AND ceremony_number = cp_ceremony_number
53 AND award_course_cd = cp_award_course_cd
54 AND award_crs_version_number = cp_award_crs_version_number
55 AND award_cd = cp_award_cd;
56 --
57 v_gach_rec c_gach%ROWTYPE;
58 --
59 -- This function will simply return false if the Graduand Award Ceremony History
60 -- table or rows are locked. Otherwise, it will delete the appropriate records
61 -- from the table and return true.
62 --
63 FUNCTION grdpl_del_if_not_locked (
64 pl_person_id IN igs_gr_awd_crmn.person_id%TYPE,
65 pl_create_dt IN igs_gr_awd_crmn.create_dt%TYPE,
66 pl_grd_cal_type IN igs_gr_awd_crmn.grd_cal_type%TYPE,
67 pl_grd_ci_sequence_number IN igs_gr_awd_crmn.grd_ci_sequence_number%TYPE,
68 pl_ceremony_number IN igs_gr_awd_crmn.ceremony_number%TYPE,
69 pl_award_course_cd IN igs_gr_awd_crmn.award_course_cd%TYPE,
70 pl_award_crs_version_number IN igs_gr_awd_crmn.award_crs_version_number%TYPE,
71 pl_award_cd IN igs_gr_awd_crmn.award_cd%TYPE
72 ) RETURN BOOLEAN AS
73 --
74 e_resource_busy_exception EXCEPTION;
75 PRAGMA EXCEPTION_INIT (e_resource_busy_exception, -54);
76 --
77 BEGIN -- grdpl_del_if_not_locked
78 --
79 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
80 fnd_log.string (
81 fnd_log.level_procedure, g_module_head || 'grdpl_del_if_not_locked.begin',
82 'In Params: pl_person_id=>' || pl_person_id || ';' ||
83 'pl_create_dt=>' || pl_create_dt || ';' ||
84 'pl_grd_cal_type=>' || pl_grd_cal_type || ';' ||
85 'pl_grd_ci_sequence_number=>' || pl_grd_ci_sequence_number || ';' ||
86 'pl_ceremony_number=>' || pl_ceremony_number || ';' ||
87 'pl_award_course_cd=>' || pl_award_course_cd || ';' ||
88 'pl_award_crs_version_number=>' || pl_award_crs_version_number || ';' ||
89 'pl_award_cd=>' || pl_award_cd || ';'
90 );
91 END IF;
92 --
93 DECLARE
94 CURSOR c_gach (
95 cp_person_id igs_gr_awd_crmn.person_id%TYPE,
96 cp_create_dt igs_gr_awd_crmn.create_dt%TYPE,
97 cp_grd_cal_type igs_gr_awd_crmn.grd_cal_type%TYPE,
98 cp_grd_ci_sequence_number igs_gr_awd_crmn.grd_ci_sequence_number%TYPE,
99 cp_ceremony_number igs_gr_awd_crmn.ceremony_number%TYPE,
100 cp_award_course_cd igs_gr_awd_crmn.award_course_cd%TYPE,
101 cp_award_crs_version_number igs_gr_awd_crmn.award_crs_version_number%TYPE,
102 cp_award_cd igs_gr_awd_crmn.award_cd%TYPE
103 ) IS
104 SELECT ROWID,
105 person_id
106 FROM igs_gr_awd_crmn_hist
107 WHERE person_id = cp_person_id
108 AND create_dt = cp_create_dt
109 AND grd_cal_type = cp_grd_cal_type
110 AND grd_ci_sequence_number = cp_grd_ci_sequence_number
111 AND ceremony_number = cp_ceremony_number
112 AND award_course_cd = cp_award_course_cd
113 AND award_crs_version_number = cp_award_crs_version_number
114 AND award_cd = cp_award_cd
115 FOR UPDATE OF person_id NOWAIT;
116 BEGIN
117 --
118 FOR v_gach_rec IN c_gach (
119 p_person_id,
120 p_create_dt,
121 p_grd_cal_type,
122 p_grd_ci_sequence_number,
123 p_ceremony_number,
124 p_award_course_cd,
125 p_award_crs_version_number,
126 p_award_cd
127 ) LOOP
128 igs_gr_awd_crmn_hist_pkg.delete_row (x_rowid => v_gach_rec.ROWID);
129 END LOOP;
130 --
131 RETURN TRUE;
132 END;
133 EXCEPTION
134 WHEN e_resource_busy_exception THEN
135 RETURN FALSE;
136 WHEN OTHERS THEN
137 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
138 fnd_log.string (
139 fnd_log.level_exception, g_module_head || 'grdpl_del_if_not_locked.e_resource_busy_exception',
140 'Error: ' || SQLERRM
141 );
142 END IF;
143 app_exception.raise_exception;
144 END grdpl_del_if_not_locked;
145 BEGIN
146 --
147 p_message_name := NULL;
148 --
149 IF grdpl_del_if_not_locked (
150 p_person_id,
151 p_create_dt,
152 p_grd_cal_type,
153 p_grd_ci_sequence_number,
154 p_ceremony_number,
155 p_award_course_cd,
156 p_award_crs_version_number,
157 p_award_cd
158 ) = FALSE THEN
159 OPEN c_gach (
160 p_person_id,
161 p_create_dt,
162 p_grd_cal_type,
163 p_grd_ci_sequence_number,
164 p_ceremony_number,
165 p_award_course_cd,
166 p_award_crs_version_number,
167 p_award_cd
168 );
169 FETCH c_gach INTO v_gach_rec;
170 IF c_gach%FOUND THEN
171 CLOSE c_gach;
172 p_message_name := 'IGS_GR_CANNOT_DEL_GRD_AWD_CER';
173 RETURN FALSE;
174 END IF;
175 --
176 CLOSE c_gach;
177 END IF;
178 --
179 RETURN TRUE;
180 --
181 END;
182 EXCEPTION
183 WHEN OTHERS THEN
184 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
185 fnd_log.string (
186 fnd_log.level_exception, g_module_head || 'grdp_del_gac_hist.exit_exception',
187 'Error: ' || SQLERRM
188 );
189 END IF;
190 app_exception.raise_exception;
191 END grdp_del_gac_hist;
192 --
193 -- Clean up the Graduand and Graduand Award Ceremony records for the specfied
194 -- Graduation Ceremony Round which have a Graduand Status of POTENTIAL.
195 -- Block for Parameter Validation/Splitting of Parameters
196 --
197 PROCEDURE grdp_del_gr_gac (
198 errbuf OUT NOCOPY VARCHAR2,
199 retcode OUT NOCOPY NUMBER,
200 p_grd_period IN VARCHAR2,
201 p_org_id IN NUMBER
202 ) AS
203 --
204 p_grd_cal_type igs_gr_crmn_round_all.grd_cal_type%TYPE;
205 p_grd_ci_sequence_number igs_gr_crmn_round_all.grd_ci_sequence_number%TYPE;
206 --
207 BEGIN -- grdp_del_gr_gac
208
209 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
210 --
211 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
212 fnd_log.string (
213 fnd_log.level_procedure, g_module_head || 'grdp_del_gr_gac.begin',
214 'In Params: p_grd_period=>' || p_grd_period || ';' ||
215 'p_org_id=>' || p_org_id || ';'
216 );
217 END IF;
218 --
219 retcode := 0;
220 p_grd_cal_type := RTRIM (SUBSTR (p_grd_period, 1, 10));
221 p_grd_ci_sequence_number := TO_NUMBER (RTRIM (SUBSTR (p_grd_period, 11, 10)));
222 --
223 DECLARE
224 --
225 e_resource_busy EXCEPTION;
226 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
227 v_person_id igs_gr_graduand_all.person_id%TYPE;
228 v_create_dt igs_gr_graduand_all.create_dt%TYPE;
229 v_gac_locked VARCHAR2 (1);
230 --
231 CURSOR c_gr IS
232 SELECT gr.person_id,
233 gr.create_dt
234 FROM igs_gr_graduand_all gr,
235 igs_gr_stat gst
236 WHERE gr.grd_cal_type = p_grd_cal_type
237 AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
238 AND gr.graduand_status = gst.graduand_status
239 AND gst.s_graduand_status = 'POTENTIAL'
240 AND gr.grd_cal_type = p_grd_cal_type
241 AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number;
242 --
243 CURSOR c_gr_del (
244 cp_gr_person_id igs_gr_graduand_all.person_id%TYPE,
245 cp_gr_create_dt igs_gr_graduand_all.create_dt%TYPE
246 ) IS
247 SELECT ROWID
248 FROM igs_gr_graduand_all gr
249 WHERE gr.person_id = cp_gr_person_id
250 AND gr.create_dt = cp_gr_create_dt
251 AND NOT EXISTS (SELECT 'X'
252 FROM igs_gr_awd_crmn gac
253 WHERE gac.person_id = cp_gr_person_id
254 AND gac.create_dt = cp_gr_create_dt)
255 FOR UPDATE OF gr.person_id NOWAIT;
256 --
257 CURSOR c_grh_del (
258 cp_gr_person_id igs_gr_graduand_all.person_id%TYPE,
259 cp_gr_create_dt igs_gr_graduand_all.create_dt%TYPE
260 ) IS
261 SELECT ROWID
262 FROM igs_gr_graduand_hist grh
263 WHERE grh.person_id = cp_gr_person_id
264 AND grh.create_dt = cp_gr_create_dt
265 FOR UPDATE OF grh.person_id NOWAIT;
266 --
267 CURSOR c_gac (
268 cp_gr_person_id igs_gr_graduand_all.person_id%TYPE,
269 cp_gr_create_dt igs_gr_graduand_all.create_dt%TYPE
270 ) IS
271 SELECT ROWID
272 FROM igs_gr_awd_crmn gac
273 WHERE gac.person_id = cp_gr_person_id
274 AND gac.create_dt = cp_gr_create_dt
275 FOR UPDATE OF gac.person_id NOWAIT;
276 --
277 CURSOR c_gach (
278 cp_gr_person_id igs_gr_graduand_all.person_id%TYPE,
279 cp_gr_create_dt igs_gr_graduand_all.create_dt%TYPE
280 ) IS
281 SELECT ROWID
282 FROM igs_gr_awd_crmn_hist gach
283 WHERE gach.person_id = cp_gr_person_id
284 AND gach.create_dt = cp_gr_create_dt
285 AND gach.grd_cal_type = p_grd_cal_type
286 AND gach.grd_ci_sequence_number = p_grd_ci_sequence_number
287 FOR UPDATE OF gach.person_id NOWAIT;
288 --
289 BEGIN
290 --
291 -- 1. Check parameters
292 --
293 IF p_grd_cal_type IS NULL
294 OR p_grd_ci_sequence_number IS NULL THEN
295 fnd_message.set_name ('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
296 app_exception.raise_exception;
297 END IF;
298 --
299 v_gac_locked := 'N';
300 --
301 -- 2. Loop through all of the Graduation Ceremony records which match the specifed parameters.
302 --
303 FOR v_gr_rec IN c_gr LOOP
304 --
305 -- 3. Check if the Graduand has any related Graduand Award Ceremony records and delete them.
306 --
307 v_person_id := v_gr_rec.person_id;
308 v_create_dt := v_gr_rec.create_dt;
309 --
310 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
311 fnd_log.string (
312 fnd_log.level_statement, g_module_head || 'grdp_del_gr_gac.c_gr_values',
313 'person_id=>' || v_gr_rec.person_id || ';' ||
314 'create_dt=>' || v_gr_rec.create_dt || ';'
315 );
316 END IF;
317 --
318 BEGIN
319 --
320 -- 4.1 Delete any Graduand Award Ceremony records found.
321 --
322 FOR v_gac_rec IN c_gac (v_gr_rec.person_id, v_gr_rec.create_dt) LOOP
323 igs_gr_awd_crmn_pkg.delete_row (x_rowid => v_gac_rec.ROWID);
324 END LOOP;
325 --
326 -- 4.2 Delete any Graduand Award Ceremony History records found.
327 --
328 FOR v_gach_rec IN c_gach (v_gr_rec.person_id, v_gr_rec.create_dt) LOOP
329 igs_gr_awd_crmn_hist_pkg.delete_row (x_rowid => v_gach_rec.ROWID);
330 END LOOP;
331 --
332 EXCEPTION
333 WHEN e_resource_busy THEN
334 fnd_file.put_line (fnd_file.LOG, fnd_message.get_string ('IGS', 'IGS_GE_RECORD_LOCKED'));
335 v_gac_locked := 'Y';
336 END;
337 --
338 -- 5. Delete any Graduand records found.
339 --
340 IF v_gac_locked = 'N' THEN
341 BEGIN
342 --
343 -- 5.1 Delete any Graduand records found.
344 --
345 FOR v_gr_del_rec IN c_gr_del (v_gr_rec.person_id, v_gr_rec.create_dt) LOOP
346 igs_gr_graduand_pkg.delete_row (x_rowid => v_gr_del_rec.ROWID);
347 END LOOP;
348 --
349 -- 5.1 Delete any Graduand History records found.
350 --
351 FOR v_grh_del_rec IN c_grh_del (v_gr_rec.person_id, v_gr_rec.create_dt) LOOP
352 igs_gr_graduand_hist_pkg.delete_row (x_rowid => v_grh_del_rec.ROWID);
353 END LOOP;
354 --
355 EXCEPTION
356 WHEN e_resource_busy THEN
357 fnd_file.put_line (fnd_file.LOG, fnd_message.get_string ('IGS', 'IGS_GE_RECORD_LOCKED'));
358 END;
359 ELSE
360 v_gac_locked := 'N';
361 END IF;
362 END LOOP;
363 --
364 COMMIT;
365 RETURN;
366 --
367 EXCEPTION
368 WHEN OTHERS THEN
369 IF c_gr%ISOPEN THEN
370 CLOSE c_gr;
371 END IF;
372 IF c_gr%ISOPEN THEN
373 CLOSE c_gr_del;
374 END IF;
375 IF c_gr%ISOPEN THEN
376 CLOSE c_grh_del;
377 END IF;
378 IF c_gac%ISOPEN THEN
379 CLOSE c_gac;
380 END IF;
381 IF c_gac%ISOPEN THEN
382 CLOSE c_gach;
383 END IF;
384 RAISE;
385 END;
386 --
387 EXCEPTION
388 WHEN OTHERS THEN
389 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
390 fnd_log.string (
391 fnd_log.level_exception, g_module_head || 'grdp_del_gr_gac.exit_exception',
392 'Error: ' || SQLERRM
393 );
394 END IF;
395 app_exception.raise_exception;
396 END grdp_del_gr_gac;
397 --
398 -- This function gets the title for the unit set group if an override title is not set.
399 --
400 FUNCTION grdp_get_acusg_title (
401 p_grd_cal_type IN VARCHAR2,
402 p_grd_ci_sequence_number IN NUMBER,
403 p_ceremony_number IN NUMBER,
404 p_award_course_cd IN CHAR,
405 p_award_crs_version_number IN NUMBER,
406 p_award_cd IN VARCHAR2,
407 p_us_group_number IN NUMBER
408 ) RETURN VARCHAR2 AS
409 BEGIN -- grdp_get_acusg_title
410 --
411 -- Get the Award Ceremony Unit Set Group title
412 --
413 DECLARE
414 v_group_title VARCHAR2 (500);
415 v_us_title VARCHAR2 (100);
416 --
417 CURSOR c_acusg IS
418 SELECT acusg.override_title
419 FROM igs_gr_awd_crm_us_gp acusg
420 WHERE acusg.grd_cal_type = p_grd_cal_type
421 AND acusg.grd_ci_sequence_number = p_grd_ci_sequence_number
422 AND acusg.ceremony_number = p_ceremony_number
423 AND acusg.award_course_cd = p_award_course_cd
424 AND acusg.award_crs_version_number = p_award_crs_version_number
425 AND acusg.award_cd = p_award_cd
426 AND acusg.us_group_number = p_us_group_number
427 AND acusg.override_title IS NOT NULL;
428 --
429 CURSOR c_us IS
430 SELECT us.short_title
431 FROM igs_en_unit_set us,
432 igs_gr_awd_crm_ut_st acus
433 WHERE acus.grd_cal_type = p_grd_cal_type
434 AND acus.grd_ci_sequence_number = p_grd_ci_sequence_number
435 AND acus.ceremony_number = p_ceremony_number
436 AND acus.award_course_cd = p_award_course_cd
437 AND acus.award_crs_version_number = p_award_crs_version_number
438 AND acus.award_cd = p_award_cd
439 AND acus.us_group_number = p_us_group_number
440 AND acus.unit_set_cd = us.unit_set_cd
441 AND acus.us_version_number = us.version_number
442 ORDER BY acus.order_in_group;
443 --
444 BEGIN
445 --
446 OPEN c_acusg;
447 FETCH c_acusg
448 INTO v_us_title;
449 --
450 IF c_acusg%FOUND THEN
451 CLOSE c_acusg;
452 RETURN v_us_title;
453 END IF;
454 CLOSE c_acusg;
455 --
456 OPEN c_us;
457 LOOP
458 FETCH c_us
459 INTO v_us_title;
460 --
461 IF c_us%NOTFOUND THEN
462 CLOSE c_us;
463 EXIT;
464 END IF;
465 --
466 IF v_group_title IS NULL THEN
467 v_group_title := v_us_title;
468 ELSE
469 v_group_title := v_group_title || ' & ' || v_us_title;
470 END IF;
471 --
472 v_group_title := SUBSTR (v_group_title, 1, 255);
473 END LOOP;
474 --
475 RETURN v_group_title;
476 END;
477 EXCEPTION
478 WHEN OTHERS THEN
479 RAISE;
480 END grdp_get_acusg_title;
481 --
482 -- Retrieves and formats the graduation name from a student's person detail.
483 --
484 FUNCTION grdp_get_grad_name (p_person_id IN NUMBER)
485 RETURN VARCHAR2 AS
486 BEGIN
487 --
488 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
489 fnd_log.string (
490 fnd_log.level_procedure, g_module_head || 'grdp_get_grad_name.begin',
491 'In Params: p_person_id=>' || p_person_id || ';'
492 );
493 END IF;
494 --
495 DECLARE
496 cst_person CONSTANT VARCHAR2 (6) := 'PERSON';
497 cst_title CONSTANT VARCHAR2 (5) := 'TITLE';
498 cst_given_names CONSTANT VARCHAR2 (11) := 'GIVEN_NAMES';
499 cst_surname CONSTANT VARCHAR2 (7) := 'SURNAME';
500 v_surname igs_pe_person_base_v.last_name%TYPE DEFAULT NULL;
501 v_given_names igs_pe_person_base_v.first_name%TYPE DEFAULT NULL;
502 v_title igs_pe_person_base_v.title%TYPE DEFAULT NULL;
503 v_graduation_name igs_gr_graduand_all.graduation_name%TYPE DEFAULT '';
504 --
505 CURSOR c_pe IS
506 SELECT pe.last_name,
507 pe.first_name,
508 pe.title
509 FROM igs_pe_person_base_v pe
510 WHERE pe.person_id = p_person_id;
511 --
512 --
513 --
514 FUNCTION grdpl_surname_initcap (surname VARCHAR2)
515 RETURN VARCHAR2 AS
516 name_start VARCHAR2 (4);
517 BEGIN
518 --
519 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
520 fnd_log.string (
521 fnd_log.level_procedure, g_module_head || 'grdpl_surname_initcap.begin',
522 'In Params: surname=>' || surname || ';'
523 );
524 END IF;
525 --
526 name_start := SUBSTR (surname, 1, 3);
527 --
528 IF name_start = 'MAC' THEN
529 RETURN 'Mac' || INITCAP (SUBSTR (surname, 4, LENGTH (surname)));
530 ELSIF name_start LIKE 'MC%' THEN
531 RETURN 'Mc' || INITCAP (SUBSTR (surname, 3, LENGTH (surname)));
532 ELSE
533 RETURN INITCAP (surname);
534 END IF;
535 END grdpl_surname_initcap;
536 BEGIN
537 --
538 OPEN c_pe;
539 FETCH c_pe
540 INTO v_surname,
541 v_given_names,
542 v_title;
543 --
544 IF c_pe%NOTFOUND THEN
545 CLOSE c_pe;
546 RAISE NO_DATA_FOUND;
547 END IF;
548 --
549 CLOSE c_pe;
550 --
551 -- the code commented out below can be uncommented if 'Title' is a required
552 -- component in the Graduand name.
553 --
554 /*IF v_title IS NOT NULL THEN
555 -- IF column is forced uppercase, re-Capitalise.
556 IF IGS_GE_GEN_001.genp_chk_col_upper( cst_title,
557 cst_person) THEN
558 v_graduation_name := RTRIM(INITCAP(v_title));
559 ELSE
560 v_graduation_name := RTRIM(v_title);
561 END IF;
562 END IF;*/
563 --
564 IF v_given_names IS NOT NULL THEN
565 -- If column is forced uppercase, re-Capitalise.
566 IF igs_ge_gen_001.genp_chk_col_upper (cst_given_names, cst_person) THEN
567 v_graduation_name := v_graduation_name || ' ' || RTRIM (INITCAP (v_given_names));
568 ELSE
569 v_graduation_name := v_graduation_name || ' ' || RTRIM (v_given_names);
570 END IF;
571 END IF;
572 --
573 IF v_surname IS NOT NULL THEN
574 --
575 -- If column is forced uppercase, re-Capitalise.
576 -- Allow for mid-name capitals (like McDonald).
577 --
578 IF igs_ge_gen_001.genp_chk_col_upper (cst_surname, cst_person) THEN
579 v_graduation_name := v_graduation_name || ' ' || RTRIM (grdpl_surname_initcap (v_surname));
580 ELSE
581 v_graduation_name := v_graduation_name || ' ' || RTRIM (v_surname);
582 END IF;
583 END IF;
584 --
585 RETURN v_graduation_name;
586 --
587 EXCEPTION
588 WHEN OTHERS THEN
589 IF c_pe%ISOPEN THEN
590 CLOSE c_pe;
591 END IF;
592 --
593 RAISE;
594 END;
595 --
596 EXCEPTION
597 WHEN OTHERS THEN
598 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
599 fnd_log.string (
600 fnd_log.level_exception, g_module_head || 'grdp_get_grad_name.exit_exception',
601 'Error: ' || SQLERRM
602 );
603 END IF;
604 app_exception.raise_exception;
605 END grdp_get_grad_name;
606 --
607 -- Retrieves the government honours level from a student's Graduand detail.
608 --
609 FUNCTION grdp_get_gr_ghl (p_person_id IN NUMBER, p_course_cd IN VARCHAR2)
610 RETURN VARCHAR2 AS
611 BEGIN -- grdp_get_gr_ghl
612 --
613 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
614 fnd_log.string (
615 fnd_log.level_procedure, g_module_head || 'grdp_get_gr_ghl.begin',
616 'In Params: p_person_id=>' || p_person_id || ';' ||
617 'p_course_cd=>' || p_course_cd || ';'
618 );
619 END IF;
620 --
621 DECLARE
622 --
623 v_govt_honours_level igs_gr_honours_level.govt_honours_level%TYPE;
624 --
625 CURSOR c_gr_gst_aw_hl IS
626 SELECT hl.govt_honours_level
627 FROM igs_gr_graduand_all gr,
628 igs_gr_stat gst,
629 igs_ps_awd aw,
630 igs_gr_honours_level hl
631 WHERE gr.person_id = p_person_id
632 AND gr.course_cd = p_course_cd
633 AND gr.award_course_cd = gr.course_cd
634 AND gst.graduand_status = gr.graduand_status
635 AND gst.s_graduand_status = 'GRADUATED'
636 AND aw.award_cd = gr.award_cd
637 AND aw.s_award_type = 'COURSE'
638 AND hl.honours_level = gr.honours_level;
639 --
640 BEGIN
641 --
642 OPEN c_gr_gst_aw_hl;
643 FETCH c_gr_gst_aw_hl
644 INTO v_govt_honours_level;
645 --
646 IF c_gr_gst_aw_hl%NOTFOUND THEN
647 CLOSE c_gr_gst_aw_hl;
648 --
649 RETURN 1; -- Student not granted honours level
650 ELSE
651 CLOSE c_gr_gst_aw_hl;
652 --
653 RETURN v_govt_honours_level;
654 END IF;
655 --
656 EXCEPTION
657 WHEN OTHERS THEN
658 IF c_gr_gst_aw_hl%ISOPEN THEN
659 CLOSE c_gr_gst_aw_hl;
660 END IF;
661 --
662 RAISE;
663 END;
664 --
665 EXCEPTION
666 WHEN OTHERS THEN
667 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
668 fnd_log.string (
669 fnd_log.level_exception, g_module_head || 'grdp_get_gr_ghl.exit_exception',
670 'Error: ' || SQLERRM
671 );
672 END IF;
673 app_exception.raise_exception;
674 END grdp_get_gr_ghl;
675 --
676 -- Insert Graduand Award Ceremony History
677 --
678 PROCEDURE grdp_ins_gac_hist (
679 p_person_id igs_gr_awd_crmn.person_id%TYPE,
680 p_create_dt igs_gr_awd_crmn.create_dt%TYPE,
681 p_grd_cal_type igs_gr_awd_crmn.grd_cal_type%TYPE,
682 p_grd_ci_sequence_number igs_gr_awd_crmn.grd_ci_sequence_number%TYPE,
683 p_ceremony_number igs_gr_awd_crmn.ceremony_number%TYPE,
684 p_award_course_cd igs_gr_awd_crmn.award_course_cd%TYPE,
685 p_award_crs_version_number igs_gr_awd_crmn.award_crs_version_number%TYPE,
686 p_award_cd igs_gr_awd_crmn.award_cd%TYPE,
687 p_old_us_group_number igs_gr_awd_crmn.us_group_number%TYPE,
688 p_new_us_group_number igs_gr_awd_crmn.us_group_number%TYPE,
689 p_old_order_in_presentation igs_gr_awd_crmn.order_in_presentation%TYPE,
690 p_new_order_in_presentation igs_gr_awd_crmn.order_in_presentation%TYPE,
691 p_old_graduand_seat_number igs_gr_awd_crmn.graduand_seat_number%TYPE,
692 p_new_graduand_seat_number igs_gr_awd_crmn.graduand_seat_number%TYPE,
693 p_old_name_pronunciation igs_gr_awd_crmn.name_pronunciation%TYPE,
694 p_new_name_pronunciation igs_gr_awd_crmn.name_pronunciation%TYPE,
695 p_old_name_announced igs_gr_awd_crmn.name_announced%TYPE,
696 p_new_name_announced igs_gr_awd_crmn.name_announced%TYPE,
697 p_old_academic_dress_rqrd_ind igs_gr_awd_crmn.academic_dress_rqrd_ind%TYPE,
698 p_new_academic_dress_rqrd_ind igs_gr_awd_crmn.academic_dress_rqrd_ind%TYPE,
699 p_old_academic_gown_size igs_gr_awd_crmn.academic_gown_size%TYPE,
700 p_new_academic_gown_size igs_gr_awd_crmn.academic_gown_size%TYPE,
701 p_old_academic_hat_size igs_gr_awd_crmn.academic_hat_size%TYPE,
702 p_new_academic_hat_size igs_gr_awd_crmn.academic_hat_size%TYPE,
703 p_old_guest_tickets_requested igs_gr_awd_crmn.guest_tickets_requested%TYPE,
704 p_new_guest_tickets_requested igs_gr_awd_crmn.guest_tickets_requested%TYPE,
705 p_old_guest_tickets_allocated igs_gr_awd_crmn.guest_tickets_allocated%TYPE,
706 p_new_guest_tickets_allocated igs_gr_awd_crmn.guest_tickets_allocated%TYPE,
707 p_old_guest_seats igs_gr_awd_crmn.guest_seats%TYPE,
708 p_new_guest_seats igs_gr_awd_crmn.guest_seats%TYPE,
709 p_old_fees_paid_ind igs_gr_awd_crmn.fees_paid_ind%TYPE,
710 p_new_fees_paid_ind igs_gr_awd_crmn.fees_paid_ind%TYPE,
711 p_old_update_who igs_gr_awd_crmn.last_updated_by%TYPE,
712 p_new_update_who igs_gr_awd_crmn.last_updated_by%TYPE,
713 p_old_update_on igs_gr_awd_crmn.last_update_date%TYPE,
714 p_new_update_on igs_gr_awd_crmn.last_update_date%TYPE,
715 p_old_special_requirements igs_gr_awd_crmn.special_requirements%TYPE,
716 p_new_special_requirements igs_gr_awd_crmn.special_requirements%TYPE,
717 p_old_comments igs_gr_awd_crmn.comments%TYPE,
718 p_new_comments igs_gr_awd_crmn.comments%TYPE
719 ) AS
720 BEGIN -- grdp_ins_gr_hist
721 --
722 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
723 fnd_log.string (
724 fnd_log.level_procedure, g_module_head || 'grdp_ins_gac_hist.begin',
725 'In Params: p_person_id=>' || p_person_id || ';' ||
726 'p_create_dt=>' || p_create_dt || ';' ||
727 'p_grd_cal_type=>' || p_grd_cal_type || ';' ||
728 'p_grd_ci_sequence_number=>' || p_grd_ci_sequence_number || ';' ||
729 'p_ceremony_number=>' || p_ceremony_number || ';' ||
730 'p_award_course_cd=>' || p_award_course_cd || ';' ||
731 'p_award_crs_version_number=>' || p_award_crs_version_number || ';' ||
732 'p_award_cd=>' || p_award_cd || ';' ||
733 'p_old_us_group_number=>' || p_old_us_group_number || ';' ||
734 'p_new_us_group_number=>' || p_new_us_group_number || ';' ||
735 'p_old_order_in_presentation=>' || p_old_order_in_presentation || ';' ||
736 'p_new_order_in_presentation=>' || p_new_order_in_presentation || ';' ||
737 'p_old_graduand_seat_number=>' || p_old_graduand_seat_number || ';' ||
738 'p_new_graduand_seat_number=>' || p_new_graduand_seat_number || ';' ||
739 'p_old_name_pronunciation=>' || p_old_name_pronunciation || ';' ||
740 'p_new_name_pronunciation=>' || p_new_name_pronunciation || ';' ||
741 'p_old_name_announced=>' || p_old_name_announced || ';' ||
742 'p_new_name_announced=>' || p_new_name_announced || ';' ||
743 'p_old_academic_dress_rqrd_ind=>' || p_old_academic_dress_rqrd_ind || ';' ||
744 'p_new_academic_dress_rqrd_ind=>' || p_new_academic_dress_rqrd_ind || ';' ||
745 'p_old_academic_gown_size=>' || p_old_academic_gown_size || ';' ||
746 'p_new_academic_gown_size=>' || p_new_academic_gown_size || ';' ||
747 'p_old_academic_hat_size=>' || p_old_academic_hat_size || ';' ||
748 'p_new_academic_hat_size=>' || p_new_academic_hat_size || ';' ||
749 'p_old_guest_tickets_requested=>' || p_old_guest_tickets_requested || ';' ||
750 'p_new_guest_tickets_requested=>' || p_new_guest_tickets_requested || ';' ||
751 'p_old_guest_tickets_allocated=>' || p_old_guest_tickets_allocated || ';' ||
752 'p_new_guest_tickets_allocated=>' || p_new_guest_tickets_allocated || ';' ||
753 'p_old_guest_seats=>' || p_old_guest_seats || ';' ||
754 'p_new_guest_seats=>' || p_new_guest_seats || ';' ||
755 'p_old_fees_paid_ind=>' || p_old_fees_paid_ind || ';' ||
756 'p_new_fees_paid_ind=>' || p_new_fees_paid_ind || ';' ||
757 'p_old_update_who=>' || p_old_update_who || ';' ||
758 'p_new_update_who=>' || p_new_update_who || ';' ||
759 'p_old_update_on=>' || p_old_update_on || ';' ||
760 'p_new_update_on=>' || p_new_update_on || ';' ||
761 'p_old_special_requirements=>' || p_old_special_requirements || ';' ||
762 'p_new_special_requirements=>' || p_new_special_requirements || ';' ||
763 'p_old_comments=>' || p_old_comments || ';' ||
764 'p_new_comments=>' || p_new_comments || ';'
765 );
766 END IF;
767 --
768 DECLARE
769 v_gac_rec igs_gr_awd_crmn_hist%ROWTYPE;
770 v_create_history BOOLEAN DEFAULT FALSE;
771 BEGIN
772 --
773 -- If any of the old values (p_old_<column_name>) are different from the
774 -- associated new values (p_new_<column_name>) (with the exception of
775 -- the last_update_date and last_updated_by columns) then create a
776 -- Graduand Award Ceremony History record with the old values
777 -- (p_old_<column_name>). Do not set the last_updated_by and last_update_date
778 -- columns when creating the history record.
779 --
780 IF NVL (p_new_us_group_number, 0) <> NVL (p_old_us_group_number, 0) THEN
781 v_gac_rec.us_group_number := p_old_us_group_number;
782 v_create_history := TRUE;
783 END IF;
784 --
785 IF NVL (p_new_order_in_presentation, 0) <> NVL (p_old_order_in_presentation, 0) THEN
786 v_gac_rec.order_in_presentation := p_old_order_in_presentation;
787 v_create_history := TRUE;
788 END IF;
789 --
790 IF NVL (p_new_graduand_seat_number, 'NULL') <> NVL (p_old_graduand_seat_number, 'NULL') THEN
791 v_gac_rec.graduand_seat_number := p_old_graduand_seat_number;
792 v_create_history := TRUE;
793 END IF;
794 --
795 IF NVL (p_new_name_pronunciation, 'NULL') <> NVL (p_old_name_pronunciation, 'NULL') THEN
796 v_gac_rec.name_pronunciation := p_old_name_pronunciation;
797 v_create_history := TRUE;
798 END IF;
799 --
800 IF NVL (p_new_name_announced, 'NULL') <> NVL (p_old_name_announced, 'NULL') THEN
801 v_gac_rec.name_announced := p_old_name_announced;
802 v_create_history := TRUE;
803 END IF;
804 --
805 IF p_new_academic_dress_rqrd_ind <> p_old_academic_dress_rqrd_ind THEN
806 v_gac_rec.academic_dress_rqrd_ind := p_old_academic_dress_rqrd_ind;
807 v_create_history := TRUE;
808 END IF;
809 --
810 IF NVL (p_new_academic_gown_size, 'NULL') <> NVL (p_old_academic_gown_size, 'NULL') THEN
811 v_gac_rec.academic_gown_size := p_old_academic_gown_size;
812 v_create_history := TRUE;
813 END IF;
814 --
815 IF NVL (p_new_academic_hat_size, 'NULL') <> NVL (p_old_academic_hat_size, 'NULL') THEN
816 v_gac_rec.academic_hat_size := p_old_academic_hat_size;
817 v_create_history := TRUE;
818 END IF;
819 --
820 IF NVL (p_new_guest_tickets_requested, 0) <> NVL (p_old_guest_tickets_requested, 0) THEN
821 v_gac_rec.guest_tickets_requested := p_old_guest_tickets_requested;
822 v_create_history := TRUE;
823 END IF;
824 --
825 IF NVL (p_new_guest_tickets_allocated, 0) <> NVL (p_old_guest_tickets_allocated, 0) THEN
826 v_gac_rec.guest_tickets_allocated := p_old_guest_tickets_allocated;
827 v_create_history := TRUE;
828 END IF;
829 --
830 IF NVL (p_new_guest_seats, 'NULL') <> NVL (p_old_guest_seats, 'NULL') THEN
831 v_gac_rec.guest_seats := p_old_guest_seats;
832 v_create_history := TRUE;
833 END IF;
834 --
835 IF p_new_fees_paid_ind <> p_old_fees_paid_ind THEN
836 v_gac_rec.fees_paid_ind := p_old_fees_paid_ind;
837 v_create_history := TRUE;
838 END IF;
839 --
840 IF NVL (p_new_special_requirements, 'NULL') <> NVL (p_old_special_requirements, 'NULL') THEN
841 v_gac_rec.special_requirements := p_old_special_requirements;
842 v_create_history := TRUE;
843 END IF;
844 --
845 IF NVL (p_new_comments, 'NULL') <> NVL (p_old_comments, 'NULL') THEN
846 v_gac_rec.comments := p_old_comments;
847 v_create_history := TRUE;
848 END IF;
849 --
850 -- Insert history record.
851 --
852 IF v_create_history THEN
853 v_gac_rec.person_id := p_person_id;
854 v_gac_rec.create_dt := p_create_dt;
855 v_gac_rec.grd_cal_type := p_grd_cal_type;
856 v_gac_rec.grd_ci_sequence_number := p_grd_ci_sequence_number;
857 v_gac_rec.ceremony_number := p_ceremony_number;
858 v_gac_rec.award_course_cd := p_award_course_cd;
859 v_gac_rec.award_crs_version_number := p_award_crs_version_number;
860 v_gac_rec.award_cd := p_award_cd;
861 v_gac_rec.hist_start_dt := p_old_update_on;
862 v_gac_rec.hist_end_dt := p_new_update_on;
863 v_gac_rec.hist_who := p_old_update_who;
864 --
865 -- Remove one second from the hist_start_dt value when the hist_start_dt
866 -- and hist_end_dt are the same to avoid a primary key constraint from
867 -- occurring when saving the record
868 --
869 IF (v_gac_rec.hist_start_dt = v_gac_rec.hist_end_dt) THEN
870 v_gac_rec.hist_start_dt := v_gac_rec.hist_start_dt - 1 / (60 * 24 * 60);
871 END IF;
872 --
873 DECLARE
874 lv_rowid VARCHAR2 (25);
875 lv_seqnc NUMBER;
876 BEGIN
877 igs_gr_awd_crmn_hist_pkg.insert_row (
878 x_rowid => lv_rowid,
879 x_gach_id => lv_seqnc,
880 x_name_pronunciation => v_gac_rec.name_pronunciation,
881 x_name_announced => v_gac_rec.name_announced,
882 x_academic_dress_rqrd_ind => v_gac_rec.academic_dress_rqrd_ind,
883 x_academic_gown_size => v_gac_rec.academic_gown_size,
884 x_academic_hat_size => v_gac_rec.academic_hat_size,
885 x_guest_tickets_requested => v_gac_rec.guest_tickets_requested,
886 x_guest_tickets_allocated => v_gac_rec.guest_tickets_allocated,
887 x_guest_seats => v_gac_rec.guest_seats,
888 x_fees_paid_ind => v_gac_rec.fees_paid_ind,
889 x_special_requirements => v_gac_rec.special_requirements,
890 x_comments => v_gac_rec.comments,
891 x_person_id => v_gac_rec.person_id,
892 x_create_dt => v_gac_rec.create_dt,
893 x_grd_cal_type => v_gac_rec.grd_cal_type,
894 x_graduand_seat_number => v_gac_rec.graduand_seat_number,
895 x_hist_who => v_gac_rec.hist_who,
896 x_us_group_number => v_gac_rec.us_group_number,
897 x_order_in_presentation => v_gac_rec.order_in_presentation,
898 x_hist_end_dt => v_gac_rec.hist_end_dt,
899 x_grd_ci_sequence_number => v_gac_rec.grd_ci_sequence_number,
900 x_ceremony_number => v_gac_rec.ceremony_number,
901 x_award_course_cd => v_gac_rec.award_course_cd,
902 x_award_crs_version_number => v_gac_rec.award_crs_version_number,
903 x_award_cd => v_gac_rec.award_cd,
904 x_hist_start_dt => v_gac_rec.hist_start_dt,
905 x_mode => 'R'
906 );
907 END;
908 END IF;
909 END;
910 --
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
914 fnd_log.string (
915 fnd_log.level_exception, g_module_head || 'grdp_ins_gac_hist.exit_exception',
916 'Error: ' || SQLERRM
917 );
918 END IF;
919 app_exception.raise_exception;
920 END grdp_ins_gac_hist;
921 END igs_gr_gen_001;