[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_AWD_CRMN_PKG
Source
1 package body IGS_GR_AWD_CRMN_PKG as
2 /* $Header: IGSGI03B.pls 115.7 2002/11/29 00:34:09 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_AWD_CRMN%RowType;
5 new_references IGS_GR_AWD_CRMN%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_GAC_ID in NUMBER ,
11 x_person_id IN NUMBER ,
12 x_create_dt IN DATE ,
13 x_grd_cal_type IN VARCHAR2 ,
14 x_grd_ci_sequence_number IN NUMBER ,
15 x_ceremony_number IN NUMBER ,
16 x_award_course_cd IN VARCHAR2 ,
17 x_award_crs_version_number IN NUMBER ,
18 x_award_cd IN VARCHAR2 ,
19 x_us_group_number IN NUMBER ,
20 x_order_in_presentation IN NUMBER ,
21 x_graduand_seat_number IN VARCHAR2 ,
22 x_name_pronunciation IN VARCHAR2 ,
23 x_name_announced IN VARCHAR2 ,
24 x_academic_dress_rqrd_ind IN VARCHAR2 ,
25 x_academic_gown_size IN VARCHAR2 ,
26 x_academic_hat_size IN VARCHAR2 ,
27 x_guest_tickets_requested IN NUMBER ,
28 x_guest_tickets_allocated IN NUMBER ,
29 x_guest_seats IN VARCHAR2 ,
30 x_fees_paid_ind IN VARCHAR2 ,
31 x_special_requirements IN VARCHAR2 ,
32 x_comments IN VARCHAR2 ,
33 x_creation_date IN DATE ,
34 x_created_by IN NUMBER ,
35 x_last_update_date IN DATE ,
36 x_last_updated_by IN NUMBER ,
37 x_last_update_login IN NUMBER
38 ) AS
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGS_GR_AWD_CRMN
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 Open cur_old_ref_values;
52 Fetch cur_old_ref_values INTO old_references;
53 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
54 Close cur_old_ref_values;
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 App_Exception.Raise_Exception;
58 Return;
59 END IF;
60 Close cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.GAC_ID := x_GAC_ID;
64 new_references.person_id := x_person_id;
65 new_references.create_dt := x_create_dt;
66 new_references.grd_cal_type := x_grd_cal_type;
67 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
68 new_references.ceremony_number := x_ceremony_number;
69 new_references.award_course_cd := x_award_course_cd;
70 new_references.award_crs_version_number := x_award_crs_version_number;
71 new_references.award_cd := x_award_cd;
72 new_references.us_group_number := x_us_group_number;
73 new_references.order_in_presentation := x_order_in_presentation;
74 new_references.graduand_seat_number := x_graduand_seat_number;
75 new_references.name_pronunciation := x_name_pronunciation;
76 new_references.name_announced := x_name_announced;
77 new_references.academic_dress_rqrd_ind := x_academic_dress_rqrd_ind;
78 new_references.academic_gown_size := x_academic_gown_size;
79 new_references.academic_hat_size := x_academic_hat_size;
80 new_references.guest_tickets_requested := x_guest_tickets_requested;
81 new_references.guest_tickets_allocated := x_guest_tickets_allocated;
82 new_references.guest_seats := x_guest_seats;
83 new_references.fees_paid_ind := x_fees_paid_ind;
84 new_references.special_requirements := x_special_requirements;
85 new_references.comments := x_comments;
86 IF (p_action = 'UPDATE') THEN
87 new_references.creation_date := old_references.creation_date;
88 new_references.created_by := old_references.created_by;
89 ELSE
90 new_references.creation_date := x_creation_date;
91 new_references.created_by := x_created_by;
92 END IF;
93 new_references.last_update_date := x_last_update_date;
94 new_references.last_updated_by := x_last_updated_by;
95 new_references.last_update_login := x_last_update_login;
96
97 END Set_Column_Values;
98
99 -- Trigger description :-
100 -- "OSS_TST".trg_gac_br_iu
101 -- BEFORE INSERT OR UPDATE
102 -- ON IGS_GR_AWD_CRMN
103 -- FOR EACH ROW
104
105 PROCEDURE BeforeRowInsertUpdate1(
106 p_inserting IN BOOLEAN,
107 p_updating IN BOOLEAN,
108 p_deleting IN BOOLEAN
109 ) AS
110
111 v_message_name VARCHAR2(30);
112
113 BEGIN
114 IF p_inserting THEN
115 IF IGS_GR_VAL_GAC.grdp_val_gac_insert(
116 new_references.person_id,
117 new_references.create_dt,
118 v_message_name) = FALSE THEN
119 Fnd_Message.Set_Name('IGS', v_message_name);
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124
125 IF p_inserting OR p_updating THEN
126 -- Validate the graduand award ceremony record may be inserted or updated
127 IF IGS_GR_VAL_GAC.grdp_val_gac_iu(
128 new_references.grd_cal_type,
129 new_references.grd_ci_sequence_number,
130 new_references.ceremony_number,
131 v_message_name) = FALSE THEN
132 Fnd_Message.Set_Name('IGS', v_message_name);
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception;
135 END IF;
136 -- validate required details have been specified
137 IF IGS_GR_VAL_GAC.grdp_val_gac_rqrd(
138 new_references.award_course_cd,
139 new_references.award_crs_version_number,
140 new_references.award_cd,
141 new_references.us_group_number,
142 new_references.academic_dress_rqrd_ind,
143 new_references.academic_gown_size,
144 new_references.academic_hat_size,
145 v_message_name) = FALSE THEN
146 Fnd_Message.Set_Name('IGS', v_message_name);
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 -- validate unit set group
151 IF (NVL(new_references.us_group_number, 0) <>
152 NVL(old_references.us_group_number, 0)) THEN
153 IF (new_references.us_group_number IS NOT NULL) THEN
154 IF IGS_GR_VAL_GAC.grdp_val_gac_susa(
155 new_references.person_id,
156 new_references.create_dt,
157 new_references.grd_cal_type,
158 new_references.grd_ci_sequence_number,
159 NULL,
160 NULL,
161 new_references.ceremony_number,
162 new_references.award_course_cd,
163 new_references.award_crs_version_number,
164 new_references.award_cd,
165 new_references.us_group_number,
166 v_message_name) = FALSE THEN
167 Fnd_Message.Set_Name('IGS', v_message_name);
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 END IF;
172 END IF;
173 -- validate measurement codes
174 IF (NVL(new_references.academic_gown_size, 'NULL') <>
175 NVL(old_references.academic_gown_size, 'NULL')) THEN
176 IF (new_references.academic_gown_size IS NOT NULL) THEN
177 IF IGS_GR_VAL_GAC.grdp_val_msr_closed(
178 new_references.academic_gown_size,
179 v_message_name) = FALSE THEN
180 Fnd_Message.Set_Name('IGS', v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185 END IF;
186 IF (NVL(new_references.academic_hat_size, 'NULL') <>
187 NVL(old_references.academic_hat_size, 'NULL')) THEN
188 IF (new_references.academic_hat_size IS NOT NULL) THEN
189 IF IGS_GR_VAL_GAC.grdp_val_msr_closed(
190 new_references.academic_hat_size,
191 v_message_name) = FALSE THEN
192 Fnd_Message.Set_Name('IGS', v_message_name);
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197 END IF;
198 END IF;
199
200 IF p_inserting OR (p_updating AND
201 new_references.grd_cal_type <> old_references.grd_cal_type OR
202 new_references.grd_ci_sequence_number <> old_references.grd_ci_sequence_number) THEN
203 -- validate the graduation calendar instance
204 IF IGS_GR_VAL_GAC.grdp_val_gac_grd_ci(
205 new_references.grd_cal_type,
206 new_references.grd_ci_sequence_number,
207 v_message_name) = FALSE THEN
208 Fnd_Message.Set_Name('IGS', v_message_name);
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214 IF p_inserting OR (p_updating AND
215 new_references.grd_cal_type <> old_references.grd_cal_type OR
216 new_references.grd_ci_sequence_number <> old_references.grd_ci_sequence_number OR
217 new_references.ceremony_number <> old_references.ceremony_number OR
218 new_references.award_course_cd <> old_references.award_course_cd OR
219 new_references.award_crs_version_number <> old_references.award_crs_version_number OR
220 new_references.award_cd <> old_references.award_cd) THEN
221 -- Validate the award ceremony record is not closed
222 IF igs_gr_val_acus.grdp_val_awc_closed(
223 new_references.grd_cal_type,
224 new_references.grd_ci_sequence_number,
225 new_references.ceremony_number,
226 new_references.award_course_cd,
227 new_references.award_crs_version_number,
228 new_references.award_cd,
229 v_message_name) = FALSE THEN
230 Fnd_Message.Set_Name('IGS', v_message_name);
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234 END IF;
235
236 IF p_inserting OR (p_updating AND
237 new_references.grd_cal_type <> old_references.grd_cal_type OR
238 new_references.grd_ci_sequence_number <> old_references.grd_ci_sequence_number OR
239 new_references.ceremony_number <> old_references.ceremony_number OR
240 new_references.award_course_cd <> old_references.award_course_cd OR
241 new_references.award_crs_version_number <> old_references.award_crs_version_number OR
242 new_references.award_cd <> old_references.award_cd OR
243 NVL(new_references.us_group_number, 0) <> NVL(old_references.us_group_number, 0)) THEN
244 -- Validate the award ceremony unit set group record is not closed
245 IF new_references.us_group_number IS NOT NULL THEN
246 IF IGS_GR_VAL_ACUS.GRDP_VAL_ACUSG_CLOSE(
247 new_references.grd_cal_type,
248 new_references.grd_ci_sequence_number,
249 new_references.ceremony_number,
250 new_references.award_course_cd,
251 new_references.award_crs_version_number,
252 new_references.award_cd,
253 new_references.us_group_number,
254 v_message_name) = FALSE THEN
255 Fnd_Message.Set_Name('IGS', v_message_name);
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END IF;
259 END IF;
260 END IF;
261
262 END BeforeRowInsertUpdate1;
263
264 -- Trigger description :-
265 -- "OSS_TST".trg_gac_ar_d_hist
266 -- AFTER DELETE
267 -- ON IGS_GR_AWD_CRMN
268 -- FOR EACH ROW
269
270 PROCEDURE AfterRowDelete2(
271 p_inserting IN BOOLEAN,
272 p_updating IN BOOLEAN,
273 p_deleting IN BOOLEAN
274 ) AS
275 v_message_name VARCHAR2(30);
276 BEGIN
277 -- Delete the history records.
278 IF IGS_GR_GEN_001.GRDP_DEL_GAC_HIST (
279 old_references.person_id,
280 old_references.create_dt,
281 old_references.grd_cal_type,
282 old_references.grd_ci_sequence_number,
283 old_references.ceremony_number,
284 old_references.award_course_cd,
285 old_references.award_crs_version_number,
286 old_references.award_cd,
287 v_message_name) = FALSE THEN
288 Fnd_Message.Set_Name('IGS', v_message_name);
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292
293
294 END AfterRowDelete2;
295
296 -- Trigger description :-
297 -- "OSS_TST".trg_gac_ar_iu
298 -- AFTER INSERT OR UPDATE
299 -- ON IGS_GR_AWD_CRMN
300 -- FOR EACH ROW
301
302 PROCEDURE AfterRowInsertUpdate3(
303 p_inserting IN BOOLEAN,
304 p_updating IN BOOLEAN,
305 p_deleting IN BOOLEAN
306 ) AS
307 v_message_name VARCHAR2(30);
308 v_rowid_saved BOOLEAN := FALSE;
309 BEGIN
310 IF p_inserting OR p_updating THEN
311 -- validate graduand seat number
312 IF (NVL(new_references.graduand_seat_number, 'NULL') <>
313 NVL(old_references.graduand_seat_number, 'NULL')) THEN
314 IF (new_references.graduand_seat_number IS NOT NULL) THEN
315 -- validate graduand seat number
316 IF (NEW_REFERENCES.graduand_seat_number IS NOT NULL) THEN
317 IF IGS_GR_VAL_GAC.grdp_val_gac_seat(
318 NEW_REFERENCES.person_id,
319 NEW_REFERENCES.grd_cal_type,
320 NEW_REFERENCES.grd_ci_sequence_number,
321 NEW_REFERENCES.ceremony_number,
322 NEW_REFERENCES.graduand_seat_number,
323 v_message_name) = FALSE THEN
324 Fnd_Message.Set_Name('IGS', v_message_name);
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 END IF;
328 END IF;
329 -- validate order in presentation
330 IF (NEW_REFERENCES.order_in_presentation IS NOT NULL) THEN
331 IF IGS_GR_VAL_GAC.grdp_val_gac_order(
332 NEW_REFERENCES.person_id,
333 NEW_REFERENCES.grd_cal_type,
334 NEW_REFERENCES.grd_ci_sequence_number,
335 NEW_REFERENCES.ceremony_number,
336 NEW_REFERENCES.order_in_presentation,
337 v_message_name) = FALSE THEN
338 Fnd_Message.Set_Name('IGS', v_message_name);
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END IF;
342 END IF;
343 v_rowid_saved := TRUE;
344 END IF;
345 END IF;
346 -- validate order in presentation
347 IF (NVL(new_references.order_in_presentation, 0) <>
348 NVL(old_references.order_in_presentation, 0)) THEN
349 IF (new_references.order_in_presentation IS NOT NULL) THEN
350 -- Save the rowid of the current row.
351 -- Cannot call grdp_val_gac_order because trigger
352 -- will be mutating.
353 IF v_rowid_saved = FALSE THEN
354
355 -- validate graduand seat number
356 IF (NEW_REFERENCES.graduand_seat_number IS NOT NULL) THEN
357 IF IGS_GR_VAL_GAC.grdp_val_gac_seat(
358 NEW_REFERENCES.person_id,
359 NEW_REFERENCES.grd_cal_type,
360 NEW_REFERENCES.grd_ci_sequence_number,
361 NEW_REFERENCES.ceremony_number,
362 NEW_REFERENCES.graduand_seat_number,
363 v_message_name) = FALSE THEN
364 Fnd_Message.Set_Name('IGS', v_message_name);
365 IGS_GE_MSG_STACK.ADD;
366 App_Exception.Raise_Exception;
367 END IF;
368 END IF;
369
370 v_rowid_saved := TRUE;
371 END IF;
372 END IF;
373 END IF;
374 END IF;
375
376
377 END AfterRowInsertUpdate3;
378
379 -- Trigger description :-
380 -- "OSS_TST".trg_gac_ar_u_hist
381 -- AFTER UPDATE
382 -- ON IGS_GR_AWD_CRMN
383 -- FOR EACH ROW
384
385 PROCEDURE AfterRowUpdate4(
386 p_inserting IN BOOLEAN,
387 p_updating IN BOOLEAN,
388 p_deleting IN BOOLEAN
389 ) AS
390 v_message_name NUMBER;
391 BEGIN
392 -- If the Ceremony Number has not been updated then create a history record.
393 -- If the Ceremony Number has been updated then remove the history records.
394 -- p_updating the Ceremony Number is the equivalent to creating a new record.
395 -- For this reason all existing history records are deleted.
396 IF old_references.ceremony_number = new_references.ceremony_number THEN
397 IGS_GR_GEN_001.GRDP_INS_GAC_HIST (
398 old_references.person_id,
399 old_references.create_dt,
400 old_references.grd_cal_type,
401 old_references.grd_ci_sequence_number,
402 old_references.ceremony_number,
403 old_references.award_course_cd,
404 old_references.award_crs_version_number,
405 old_references.award_cd,
406 old_references.us_group_number,
407 new_references.us_group_number,
408 old_references.order_in_presentation,
409 new_references.order_in_presentation,
410 old_references.graduand_seat_number,
411 new_references.graduand_seat_number,
412 old_references.name_pronunciation,
413 new_references.name_pronunciation,
414 old_references.name_announced,
415 new_references.name_announced,
416 old_references.academic_dress_rqrd_ind,
417 new_references.academic_dress_rqrd_ind,
418 old_references.academic_gown_size,
419 new_references.academic_gown_size,
420 old_references.academic_hat_size,
421 new_references.academic_hat_size,
422 old_references.guest_tickets_requested,
423 new_references.guest_tickets_requested,
424 old_references.guest_tickets_allocated,
425 new_references.guest_tickets_allocated,
426 old_references.guest_seats,
427 new_references.guest_seats,
428 old_references.fees_paid_ind,
429 new_references.fees_paid_ind,
430 old_references.last_updated_by,
431 new_references.last_updated_by,
432 old_references.last_update_date,
433 new_references.last_update_date,
434 old_references.special_requirements,
435 new_references.special_requirements,
436 old_references.comments,
437 new_references.comments);
438 ELSE
439 IF IGS_GR_GEN_001.GRDP_DEL_GAC_HIST (
440 old_references.person_id,
441 old_references.create_dt,
442 old_references.grd_cal_type,
443 old_references.grd_ci_sequence_number,
444 old_references.ceremony_number,
445 old_references.award_course_cd,
446 old_references.award_crs_version_number,
447 old_references.award_cd,
448 v_message_name) = FALSE THEN
449 Fnd_Message.Set_Name('IGS', v_message_name);
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END IF;
453 END IF;
454
455
456 END AfterRowUpdate4;
457
458 PROCEDURE Check_Parent_Existance AS
459 BEGIN
460
461 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
462 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
463 ((new_references.grd_cal_type IS NULL) OR
464 (new_references.grd_ci_sequence_number IS NULL))) THEN
465 NULL;
466 ELSE
467 IF NOT IGS_GR_CRMN_ROUND_PKG.Get_PK_For_Validation (
468 new_references.grd_cal_type,
469 new_references.grd_ci_sequence_number
470 ) THEN
471 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
472 APP_EXCEPTION.RAISE_EXCEPTION;
473 END IF;
474 END IF;
475
476 IF (((old_references.academic_gown_size = new_references.academic_gown_size)) OR
477 ((new_references.academic_gown_size IS NULL))) THEN
478 NULL;
479 ELSE
480 IF NOT IGS_GE_MEASUREMENT_PKG.Get_PK_For_Validation (
481 new_references.academic_gown_size
482 ) THEN
483 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
484 IGS_GE_MSG_STACK.ADD;
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487 END IF;
488
489 IF (((old_references.person_id = new_references.person_id) AND
490 (old_references.create_dt = new_references.create_dt) AND
491 (old_references.award_course_cd = new_references.award_course_cd) AND
492 (old_references.award_crs_version_number = new_references.award_crs_version_number) AND
493 (old_references.award_cd = new_references.award_cd)) OR
494 ((new_references.person_id IS NULL) OR
495 (new_references.create_dt IS NULL) OR
496 (new_references.award_course_cd IS NULL) OR
497 (new_references.award_crs_version_number IS NULL) OR
498 (new_references.award_cd IS NULL))) THEN
499 NULL;
500 ELSE
501 IF NOT IGS_GR_GRADUAND_PKG.Get_UK_For_Validation (
502 new_references.person_id,
503 new_references.create_dt,
504 new_references.award_course_cd,
505 new_references.award_crs_version_number,
506 new_references.award_cd
507 ) THEN
508 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
509 IGS_GE_MSG_STACK.ADD;
510 APP_EXCEPTION.RAISE_EXCEPTION;
511 END IF;
512 END IF;
513
514 IF (((old_references.academic_hat_size = new_references.academic_hat_size)) OR
515 ((new_references.academic_hat_size IS NULL))) THEN
516 NULL;
517 ELSE
518 IF NOT IGS_GE_MEASUREMENT_PKG.Get_PK_For_Validation (
519 new_references.academic_hat_size
520 ) THEN
521 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
522 APP_EXCEPTION.RAISE_EXCEPTION;
523 END IF;
524 END IF;
525
526 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
527 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number) AND
528 (old_references.ceremony_number = new_references.ceremony_number) AND
529 (old_references.award_course_cd = new_references.award_course_cd) AND
530 (old_references.award_crs_version_number = new_references.award_crs_version_number) AND
531 (old_references.award_cd = new_references.award_cd) AND
532 (old_references.us_group_number = new_references.us_group_number)) OR
533 ((new_references.grd_cal_type IS NULL) OR
534 (new_references.grd_ci_sequence_number IS NULL) OR
535 (new_references.ceremony_number IS NULL) OR
536 (new_references.award_course_cd IS NULL) OR
537 (new_references.award_crs_version_number IS NULL) OR
538 (new_references.award_cd IS NULL) OR
539 (new_references.us_group_number IS NULL))) THEN
540 NULL;
541 ELSE
542 IF NOT IGS_GR_AWD_CRM_US_GP_PKG.Get_PK_For_Validation (
543 new_references.grd_cal_type,
544 new_references.grd_ci_sequence_number,
545 new_references.ceremony_number,
546 new_references.award_course_cd,
547 new_references.award_crs_version_number,
548 new_references.award_cd,
549 new_references.us_group_number
550 ) THEN
551 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
552 APP_EXCEPTION.RAISE_EXCEPTION;
553 END IF;
554 END IF;
555
556 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
557 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number) AND
558 (old_references.ceremony_number = new_references.ceremony_number) AND
559 (old_references.award_course_cd = new_references.award_course_cd) AND
560 (old_references.award_crs_version_number = new_references.award_crs_version_number) AND
561 (old_references.award_cd = new_references.award_cd)) OR
562 ((new_references.grd_cal_type IS NULL) OR
563 (new_references.grd_ci_sequence_number IS NULL) OR
564 (new_references.ceremony_number IS NULL) OR
565 (new_references.award_course_cd IS NULL) OR
566 (new_references.award_crs_version_number IS NULL) OR
567 (new_references.award_cd IS NULL))) THEN
568 NULL;
569 ELSE
570 IF NOT IGS_GR_AWD_CEREMONY_PKG.Get_UK_For_Validation (
571 new_references.grd_cal_type,
572 new_references.grd_ci_sequence_number,
573 new_references.ceremony_number,
574 new_references.award_course_cd,
575 new_references.award_crs_version_number,
576 new_references.award_cd
577 ) THEN
578 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
579 APP_EXCEPTION.RAISE_EXCEPTION;
580 END IF;
581 END IF;
582
583 END Check_Parent_Existance;
584
585 PROCEDURE Check_Uniqueness AS
586 BEGIN
587 IF Get_UK_For_Validation (
588 NEW_REFERENCES.person_id,
589 NEW_REFERENCES.create_dt,
590 NEW_REFERENCES.grd_cal_type,
591 NEW_REFERENCES.grd_ci_sequence_number,
592 NEW_REFERENCES.ceremony_number,
593 NEW_REFERENCES.award_course_cd,
594 NEW_REFERENCES.award_crs_version_number,
595 NEW_REFERENCES.award_cd) THEN
596 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
597 APP_EXCEPTION.RAISE_EXCEPTION;
598 END IF;
599 END;
600
601 PROCEDURE Check_Constraints(
602 Column_Name IN VARCHAR2 ,
603 Column_Value IN VARCHAR2
604 ) AS
605 BEGIN
606 IF column_name is null then
607 NULL;
608 ELSIF upper(column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
609 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(column_value);
610 ELSIF upper(column_name) = 'US_GROUP_NUMBER' THEN
611 NEW_REFERENCES.US_GROUP_NUMBER := IGS_GE_NUMBER.to_num(column_value) ;
612 ELSIF upper(column_name) = 'ORDER_IN_PRESENTATION' THEN
613 NEW_REFERENCES.ORDER_IN_PRESENTATION := IGS_GE_NUMBER.to_num(column_value);
614 ELSIF upper(column_name) = 'ACADEMIC_DRESS_RQRD_IND' THEN
615 NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND := column_value;
616 ELSIF upper(column_name) = 'GUEST_TICKETS_REQUESTED' THEN
617 NEW_REFERENCES.GUEST_TICKETS_REQUESTED := IGS_GE_NUMBER.to_num(column_value);
618 ELSIF upper(column_name) = 'GUEST_TICKETS_ALLOCATED' THEN
619 NEW_REFERENCES.GUEST_TICKETS_ALLOCATED := IGS_GE_NUMBER.to_num(column_value);
620 ELSIF upper(column_name) = 'ACADEMIC_GOWN_SIZE' THEN
621 NEW_REFERENCES.ACADEMIC_GOWN_SIZE := column_value;
622 ELSIF upper(column_name) = 'ACADEMIC_HAT_SIZE' THEN
623 NEW_REFERENCES.ACADEMIC_HAT_SIZE := column_value;
624 ELSIF upper(column_name) = 'AWARD_CD' THEN
625 NEW_REFERENCES.AWARD_CD := column_value;
626 ELSIF upper(column_name) = 'AWARD_COURSE_CD' THEN
627 NEW_REFERENCES.AWARD_COURSE_CD := column_value;
628 ELSIF upper(column_name) = 'FEES_PAID_IND' THEN
629 NEW_REFERENCES.FEES_PAID_IND := column_value;
630 ELSIF upper(column_name) = 'GRADUAND_SEAT_NUMBER' THEN
631 NEW_REFERENCES.GRADUAND_SEAT_NUMBER := column_value;
632 ELSIF upper(column_name) = 'GRD_CAL_TYPE' THEN
633 NEW_REFERENCES.GRD_CAL_TYPE := column_value;
634 ELSIF upper(column_name) = 'GUEST_SEATS' THEN
635 NEW_REFERENCES.GUEST_SEATS := column_value;
636 ELSIF upper(column_name) = 'NAME_ANNOUNCED' THEN
637 NEW_REFERENCES.NAME_ANNOUNCED := column_value;
638 END IF;
639
640 IF upper(column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR column_name is null then
641 IF NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER < 1 OR NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER > 999999 THEN
642 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
643 APP_EXCEPTION.RAISE_EXCEPTION;
644 END IF;
645 END IF;
646 IF upper(column_name) = 'US_GROUP_NUMBER' OR column_name is null then
647 IF NEW_REFERENCES.US_GROUP_NUMBER < 0 OR NEW_REFERENCES.US_GROUP_NUMBER > 999999 THEN
648 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
649 APP_EXCEPTION.RAISE_EXCEPTION;
650 END IF ;
651 END IF;
652 IF upper(column_name) = 'ORDER_IN_PRESENTATION' OR column_name is null then
653 IF NEW_REFERENCES.ORDER_IN_PRESENTATION < 1 OR NEW_REFERENCES.ORDER_IN_PRESENTATION > 9999 THEN
654 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
655 APP_EXCEPTION.RAISE_EXCEPTION;
656 END IF;
657 END IF;
658 IF upper(column_name) = 'ACADEMIC_DRESS_RQRD_IND' OR column_name is null then
659 IF NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND NOT IN ( 'Y' , 'N' ) THEN
660 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
661 APP_EXCEPTION.RAISE_EXCEPTION;
662 END IF;
663 END IF;
664 IF upper(column_name) = 'GUEST_TICKETS_REQUESTED' OR column_name is null then
665 IF NEW_REFERENCES.GUEST_TICKETS_REQUESTED < 0 OR NEW_REFERENCES.GUEST_TICKETS_REQUESTED > 999 THEN
666 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
667 APP_EXCEPTION.RAISE_EXCEPTION;
668 END IF;
669 END IF;
670 IF upper(column_name) = 'GUEST_TICKETS_ALLOCATED' OR column_name is null then
671 IF NEW_REFERENCES.GUEST_TICKETS_ALLOCATED < 0 OR NEW_REFERENCES.GUEST_TICKETS_ALLOCATED > 999 THEN
672 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
673 APP_EXCEPTION.RAISE_EXCEPTION;
674 END IF;
675 END IF;
676 IF ((UPPER (column_name) = 'ACADEMIC_GOWN_SIZE') OR (column_name IS NULL)) THEN
677 IF (new_references.ACADEMIC_GOWN_SIZE <> UPPER (new_references.ACADEMIC_GOWN_SIZE)) THEN
678 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
679 IGS_GE_MSG_STACK.ADD;
680 App_Exception.Raise_Exception;
681 END IF;
682 END IF;
683 IF ((UPPER (column_name) = 'ACADEMIC_HAT_SIZE') OR (column_name IS NULL)) THEN
684 IF (new_references.ACADEMIC_HAT_SIZE <> UPPER (new_references.ACADEMIC_HAT_SIZE)) THEN
685 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
686 IGS_GE_MSG_STACK.ADD;
687 App_Exception.Raise_Exception;
688 END IF;
689 END IF;
690 IF ((UPPER (column_name) = 'AWARD_CD') OR (column_name IS NULL)) THEN
691 IF (new_references.AWARD_CD <> UPPER (new_references.AWARD_CD)) THEN
692 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
693 IGS_GE_MSG_STACK.ADD;
694 App_Exception.Raise_Exception;
695 END IF;
696 END IF;
697 IF ((UPPER (column_name) = 'AWARD_COURSE_CD') OR (column_name IS NULL)) THEN
698 IF (new_references.AWARD_COURSE_CD <> UPPER (new_references.AWARD_COURSE_CD)) THEN
699 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
700 IGS_GE_MSG_STACK.ADD;
701 App_Exception.Raise_Exception;
702 END IF;
703 END IF;
704 IF upper(column_name) = 'FEES_PAID_IND' OR column_name is null then
705 IF NEW_REFERENCES.FEES_PAID_IND NOT IN ('Y', 'N') THEN
706 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_VALUE');
707 APP_EXCEPTION.RAISE_EXCEPTION;
708 END IF;
709 END IF;
710 IF ((UPPER (column_name) = 'GRADUAND_SEAT_NUMBER') OR (column_name IS NULL)) THEN
711 IF (new_references.GRADUAND_SEAT_NUMBER <> UPPER (new_references.GRADUAND_SEAT_NUMBER)) THEN
712 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
713 IGS_GE_MSG_STACK.ADD;
714 App_Exception.Raise_Exception;
715 END IF;
716 END IF;
717 IF ((UPPER (column_name) = 'GRD_CAL_TYPE') OR (column_name IS NULL)) THEN
718 IF (new_references.GRD_CAL_TYPE <> UPPER (new_references.GRD_CAL_TYPE)) THEN
719 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
720 IGS_GE_MSG_STACK.ADD;
721 App_Exception.Raise_Exception;
722 END IF;
723 END IF;
724 IF ((UPPER (column_name) = 'GUEST_SEATS') OR (column_name IS NULL)) THEN
725 IF (new_references.GUEST_SEATS <> UPPER (new_references.GUEST_SEATS)) THEN
726 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
727 IGS_GE_MSG_STACK.ADD;
728 App_Exception.Raise_Exception;
729 END IF;
730 END IF;
731 IF ((UPPER (column_name) = 'NAME_ANNOUNCED') OR (column_name IS NULL)) THEN
732 IF (new_references.NAME_ANNOUNCED <> UPPER (new_references.NAME_ANNOUNCED)) THEN
733 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
734 IGS_GE_MSG_STACK.ADD;
735 App_Exception.Raise_Exception;
736 END IF;
737 END IF;
738 END Check_Constraints;
739
740
741 FUNCTION Get_PK_For_Validation (
742 X_GAC_ID IN NUMBER
743 ) RETURN BOOLEAN AS
744
745 CURSOR cur_rowid IS
746 SELECT rowid
747 FROM IGS_GR_AWD_CRMN
748 WHERE GAC_ID = X_GAC_ID
749 FOR UPDATE NOWAIT;
750
751 lv_rowid cur_rowid%RowType;
752
753 BEGIN
754
755 Open cur_rowid;
756 Fetch cur_rowid INTO lv_rowid;
757 IF (cur_rowid%FOUND) THEN
758 Close cur_rowid;
759 Return (TRUE);
760 ELSE
761 Close cur_rowid;
762 Return (FALSE);
763 END IF;
764
765 END Get_PK_For_Validation;
766
767 FUNCTION Get_UK_For_Validation (
768 x_person_id IN NUMBER,
769 x_create_dt IN DATE,
770 x_grd_cal_type IN VARCHAR2,
771 x_grd_ci_sequence_number IN NUMBER,
772 x_ceremony_number IN NUMBER,
773 x_award_course_cd IN VARCHAR2,
774 x_award_crs_version_number IN NUMBER,
775 x_award_cd IN VARCHAR2
776 ) RETURN BOOLEAN AS
777 CURSOR cur_rowid IS
778 SELECT rowid
779 FROM IGS_GR_AWD_CRMN
780 WHERE person_id = x_person_id
781 AND create_dt = x_create_dt
782 AND grd_cal_type = x_grd_cal_type
783 AND grd_ci_sequence_number = x_grd_ci_sequence_number
784 AND ceremony_number = x_ceremony_number
785 AND NVL(award_course_cd,0) = NVL(x_award_course_cd,0)
786 AND NVL(award_crs_version_number,0) = NVL(x_award_crs_version_number,0)
787 AND award_cd = x_award_cd
788 AND (l_rowid is null or rowid <> l_rowid)
789 FOR UPDATE NOWAIT;
790
791 lv_rowid cur_rowid%RowType;
792
793 BEGIN
794
795 Open cur_rowid;
796 Fetch cur_rowid INTO lv_rowid;
797 IF (cur_rowid%FOUND) THEN
798 Close cur_rowid;
799 Return (TRUE);
800 ELSE
801 Close cur_rowid;
802 Return (FALSE);
803 END IF;
804
805 END Get_UK_For_Validation;
806
807
808 PROCEDURE GET_FK_IGS_GR_CRMN_ROUND (
809 x_grd_cal_type IN VARCHAR2,
810 x_grd_ci_sequence_number IN NUMBER
811 ) AS
812
813 CURSOR cur_rowid IS
814 SELECT rowid
815 FROM IGS_GR_AWD_CRMN
816 WHERE grd_cal_type = x_grd_cal_type
817 AND grd_ci_sequence_number = x_grd_ci_sequence_number ;
818
819 lv_rowid cur_rowid%RowType;
820
821 BEGIN
822
823 Open cur_rowid;
824 Fetch cur_rowid INTO lv_rowid;
825 IF (cur_rowid%FOUND) THEN
826 Close cur_rowid;
827 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GAC_CRD_FK');
828 IGS_GE_MSG_STACK.ADD;
829 App_Exception.Raise_Exception;
830 Return;
831 END IF;
832 Close cur_rowid;
833
834 END GET_FK_IGS_GR_CRMN_ROUND;
835
836 PROCEDURE GET_FK_IGS_GE_MEASUREMENT (
837 x_measurement_cd IN VARCHAR2
838 ) AS
839
840 CURSOR cur_rowid IS
841 SELECT rowid
842 FROM IGS_GR_AWD_CRMN
843 WHERE academic_gown_size = x_measurement_cd
844 OR academic_hat_size = x_measurement_cd;
845
846 lv_rowid cur_rowid%RowType;
847
848 BEGIN
849
850 Open cur_rowid;
851 Fetch cur_rowid INTO lv_rowid;
852 IF (cur_rowid%FOUND) THEN
853 Close cur_rowid;
854 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GAC_GOWN_MSR_FK');
855 IGS_GE_MSG_STACK.ADD;
856 App_Exception.Raise_Exception;
857 Return;
858 END IF;
859 Close cur_rowid;
860
861 END GET_FK_IGS_GE_MEASUREMENT;
862
863 PROCEDURE GET_UFK_IGS_GR_GRADUAND (
864 x_person_id IN NUMBER,
865 x_create_dt IN DATE,
866 x_award_course_cd IN VARCHAR2,
867 x_award_crs_version_number IN NUMBER,
868 x_award_cd IN VARCHAR2
869 ) AS
870
871 CURSOR cur_rowid IS
872 SELECT rowid
873 FROM IGS_GR_AWD_CRMN
874 WHERE person_id = x_person_id
875 AND create_dt = x_create_dt
876 AND award_course_cd = x_award_course_cd
877 AND award_crs_version_number = x_award_crs_version_number
878 AND award_cd = x_award_cd ;
879
880 lv_rowid cur_rowid%RowType;
881
882 BEGIN
883
884 Open cur_rowid;
885 Fetch cur_rowid INTO lv_rowid;
886 IF (cur_rowid%FOUND) THEN
887 Close cur_rowid;
888 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GAC_GR_FK');
889 IGS_GE_MSG_STACK.ADD;
890 App_Exception.Raise_Exception;
891 Return;
892 END IF;
893 Close cur_rowid;
894
895 END GET_UFK_IGS_GR_GRADUAND;
896
897 PROCEDURE GET_FK_IGS_GR_AWD_CRM_US_GP (
898 x_grd_cal_type IN VARCHAR2,
899 x_grd_ci_sequence_number IN NUMBER,
900 x_ceremony_number IN NUMBER,
901 x_award_course_cd IN VARCHAR2,
902 x_award_crs_version_number IN NUMBER,
903 x_award_cd IN VARCHAR2,
904 x_us_group_number IN NUMBER
905 ) AS
906
907 CURSOR cur_rowid IS
908 SELECT rowid
909 FROM IGS_GR_AWD_CRMN
910 WHERE grd_cal_type = x_grd_cal_type
911 AND grd_ci_sequence_number = x_grd_ci_sequence_number
912 AND ceremony_number = x_ceremony_number
913 AND award_course_cd = x_award_course_cd
914 AND award_crs_version_number = x_award_crs_version_number
915 AND award_cd = x_award_cd
916 AND us_group_number = x_us_group_number ;
917
918 lv_rowid cur_rowid%RowType;
919
920 BEGIN
921
922 Open cur_rowid;
923 Fetch cur_rowid INTO lv_rowid;
924 IF (cur_rowid%FOUND) THEN
925 Close cur_rowid;
926 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GAC_ACUSG_FK');
927 IGS_GE_MSG_STACK.ADD;
928 App_Exception.Raise_Exception;
929 Return;
930 END IF;
931 Close cur_rowid;
932
933 END GET_FK_IGS_GR_AWD_CRM_US_GP;
934
935 PROCEDURE GET_UFK_IGS_GR_AWD_CEREMONY (
936 x_grd_cal_type IN VARCHAR2,
937 x_grd_ci_sequence_number IN NUMBER,
938 x_ceremony_number IN NUMBER,
939 x_award_course_cd IN VARCHAR2,
940 x_award_crs_version_number IN NUMBER,
941 x_award_cd IN VARCHAR2
942 ) AS
943
944 CURSOR cur_rowid IS
945 SELECT rowid
946 FROM IGS_GR_AWD_CRMN
947 WHERE grd_cal_type = x_grd_cal_type
948 AND grd_ci_sequence_number = x_grd_ci_sequence_number
949 AND ceremony_number = x_ceremony_number
950 AND award_course_cd = x_award_course_cd
951 AND award_crs_version_number = x_award_crs_version_number
952 AND award_cd = x_award_cd ;
953
954 lv_rowid cur_rowid%RowType;
955
956 BEGIN
957
958 Open cur_rowid;
959 Fetch cur_rowid INTO lv_rowid;
960 IF (cur_rowid%FOUND) THEN
961 Close cur_rowid;
962 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GAC_AWC_FK');
963 IGS_GE_MSG_STACK.ADD;
964 App_Exception.Raise_Exception;
965 Return;
966 END IF;
967 Close cur_rowid;
968
969 END GET_UFK_IGS_GR_AWD_CEREMONY;
970
971 PROCEDURE Before_DML (
972 p_action IN VARCHAR2,
973 x_rowid IN VARCHAR2 ,
974 x_GAC_ID in NUMBER ,
975 x_person_id IN NUMBER ,
976 x_create_dt IN DATE ,
977 x_grd_cal_type IN VARCHAR2 ,
978 x_grd_ci_sequence_number IN NUMBER ,
979 x_ceremony_number IN NUMBER ,
980 x_award_course_cd IN VARCHAR2 ,
981 x_award_crs_version_number IN NUMBER ,
982 x_award_cd IN VARCHAR2 ,
983 x_us_group_number IN NUMBER ,
984 x_order_in_presentation IN NUMBER ,
985 x_graduand_seat_number IN VARCHAR2 ,
986 x_name_pronunciation IN VARCHAR2 ,
987 x_name_announced IN VARCHAR2 ,
988 x_academic_dress_rqrd_ind IN VARCHAR2 ,
989 x_academic_gown_size IN VARCHAR2 ,
990 x_academic_hat_size IN VARCHAR2 ,
991 x_guest_tickets_requested IN NUMBER ,
992 x_guest_tickets_allocated IN NUMBER ,
993 x_guest_seats IN VARCHAR2 ,
994 x_fees_paid_ind IN VARCHAR2 ,
995 x_special_requirements IN VARCHAR2 ,
996 x_comments IN VARCHAR2 ,
997 x_creation_date IN DATE ,
998 x_created_by IN NUMBER ,
999 x_last_update_date IN DATE ,
1000 x_last_updated_by IN NUMBER ,
1001 x_last_update_login IN NUMBER
1002 ) AS
1003
1004 BEGIN
1005
1006 Set_Column_Values (
1007 p_action,
1008 x_rowid,
1009 x_GAC_ID,
1010 x_person_id,
1011 x_create_dt,
1012 x_grd_cal_type,
1013 x_grd_ci_sequence_number,
1014 x_ceremony_number,
1015 x_award_course_cd,
1016 x_award_crs_version_number,
1017 x_award_cd,
1018 x_us_group_number,
1019 x_order_in_presentation,
1020 x_graduand_seat_number,
1021 x_name_pronunciation,
1022 x_name_announced,
1023 x_academic_dress_rqrd_ind,
1024 x_academic_gown_size,
1025 x_academic_hat_size,
1026 x_guest_tickets_requested,
1027 x_guest_tickets_allocated,
1028 x_guest_seats,
1029 x_fees_paid_ind,
1030 x_special_requirements,
1031 x_comments,
1032 x_creation_date,
1033 x_created_by,
1034 x_last_update_date,
1035 x_last_updated_by,
1036 x_last_update_login
1037 );
1038
1039 IF (p_action = 'INSERT') THEN
1040 -- Call all the procedures related to Before Insert.
1041 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
1042 p_updating => FALSE,
1043 p_deleting => FALSE);
1044 IF Get_PK_For_Validation (
1045 NEW_REFERENCES.gac_id) THEN
1046 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1047 IGS_GE_MSG_STACK.ADD;
1048 App_Exception.Raise_Exception;
1049 END IF;
1050 Check_Uniqueness;
1051 Check_Constraints;
1052 Check_Parent_Existance;
1053 ELSIF (p_action = 'UPDATE') THEN
1054 -- Call all the procedures related to Before Update.
1055 BeforeRowInsertUpdate1 ( p_inserting => FALSE,
1056 p_updating => TRUE,
1057 p_deleting => FALSE);
1058 Check_Uniqueness;
1059 Check_Constraints;
1060 Check_Parent_Existance;
1061 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1062 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.GAC_ID) THEN
1063 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1064 IGS_GE_MSG_STACK.ADD;
1065 App_Exception.Raise_Exception;
1066 END IF;
1067 check_uniqueness;
1068 check_constraints;
1069 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1070 check_uniqueness;
1071 check_constraints;
1072 END IF;
1073 END Before_DML;
1074
1075 PROCEDURE After_DML (
1076 p_action IN VARCHAR2,
1077 x_rowid IN VARCHAR2
1078 ) AS
1079 BEGIN
1080
1081 l_rowid := x_rowid;
1082
1083 IF (p_action = 'INSERT') THEN
1084 -- Call all the procedures related to After Insert.
1085 AfterRowInsertUpdate3 ( p_inserting => TRUE,
1086 p_updating => FALSE,
1087 p_deleting => FALSE);
1088 ELSIF (p_action = 'UPDATE') THEN
1089 -- Call all the procedures related to After Update.
1090 AfterRowInsertUpdate3 ( p_inserting => FALSE,
1091 p_updating => TRUE,
1092 p_deleting => FALSE);
1093 AfterRowUpdate4(p_inserting => FALSE,
1094 p_updating => TRUE,
1095 p_deleting => FALSE);
1096 ELSIF (p_action = 'DELETE') THEN
1097 -- Call all the procedures related to After Delete.
1098 AfterRowDelete2(p_inserting => FALSE,
1099 p_updating => FALSE,
1100 p_deleting => TRUE);
1101 END IF;
1102
1103 END After_DML;
1104
1105 procedure INSERT_ROW (
1106 X_ROWID in out NOCOPY VARCHAR2,
1107 X_GAC_ID in out NOCOPY NUMBER,
1108 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
1109 X_NAME_PRONUNCIATION in VARCHAR2,
1110 X_NAME_ANNOUNCED in VARCHAR2,
1111 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
1112 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
1113 X_ACADEMIC_HAT_SIZE in VARCHAR2,
1114 X_GUEST_TICKETS_REQUESTED in NUMBER,
1115 X_GUEST_TICKETS_ALLOCATED in NUMBER,
1116 X_GUEST_SEATS in VARCHAR2,
1117 X_FEES_PAID_IND in VARCHAR2,
1118 X_SPECIAL_REQUIREMENTS in VARCHAR2,
1119 X_COMMENTS in VARCHAR2,
1120 X_PERSON_ID in NUMBER,
1121 X_CREATE_DT in DATE,
1122 X_GRD_CAL_TYPE in VARCHAR2,
1123 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1124 X_CEREMONY_NUMBER in NUMBER,
1125 X_AWARD_COURSE_CD in VARCHAR2,
1126 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1127 X_AWARD_CD in VARCHAR2,
1128 X_US_GROUP_NUMBER in NUMBER,
1129 X_ORDER_IN_PRESENTATION in NUMBER,
1130 X_MODE in VARCHAR2
1131 ) AS
1132 cursor C is select ROWID from IGS_GR_AWD_CRMN
1133 where GAC_ID = X_GAC_ID;
1134 X_LAST_UPDATE_DATE DATE;
1135 X_LAST_UPDATED_BY NUMBER;
1136 X_LAST_UPDATE_LOGIN NUMBER;
1137 X_REQUEST_ID NUMBER;
1138 X_PROGRAM_ID NUMBER;
1139 X_PROGRAM_APPLICATION_ID NUMBER;
1140 X_PROGRAM_UPDATE_DATE DATE;
1141 begin
1142
1143 SELECT IGS_GR_AWD_CRMN_GAC_ID_S.NEXTVAL INTO X_GAC_ID FROM DUAL;
1144
1145 X_LAST_UPDATE_DATE := SYSDATE;
1146 if(X_MODE = 'I') then
1147 X_LAST_UPDATED_BY := 1;
1148 X_LAST_UPDATE_LOGIN := 0;
1149 elsif (X_MODE = 'R') then
1150 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1151 if X_LAST_UPDATED_BY is NULL then
1152 X_LAST_UPDATED_BY := -1;
1153 end if;
1154 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1155 if X_LAST_UPDATE_LOGIN is NULL then
1156 X_LAST_UPDATE_LOGIN := -1;
1157 end if;
1158 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1159 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1160 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1161 if (X_REQUEST_ID = -1) then
1162 X_REQUEST_ID := NULL;
1163 X_PROGRAM_ID := NULL;
1164 X_PROGRAM_APPLICATION_ID := NULL;
1165 X_PROGRAM_UPDATE_DATE := NULL;
1166 else
1167 X_PROGRAM_UPDATE_DATE := SYSDATE;
1168 end if;
1169 else
1170 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1171 app_exception.raise_exception;
1172 end if;
1173
1174 Before_DML (
1175 p_action => 'INSERT',
1176 x_rowid => X_ROWID,
1177 x_GAC_ID => X_GAC_ID,
1178 x_person_id => X_PERSON_ID,
1179 x_create_dt => X_CREATE_DT,
1180 x_grd_cal_type => X_GRD_CAL_TYPE,
1181 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
1182 x_ceremony_number => X_CEREMONY_NUMBER,
1183 x_award_course_cd => X_AWARD_COURSE_CD,
1184 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
1185 x_award_cd => X_AWARD_CD,
1186 x_us_group_number => X_US_GROUP_NUMBER,
1187 x_order_in_presentation => X_ORDER_IN_PRESENTATION,
1188 x_graduand_seat_number => X_GRADUAND_SEAT_NUMBER,
1189 x_name_pronunciation => X_NAME_PRONUNCIATION,
1190 x_name_announced => X_NAME_ANNOUNCED,
1191 x_academic_dress_rqrd_ind => NVL(X_ACADEMIC_DRESS_RQRD_IND, 'N'),
1192 x_academic_gown_size => X_ACADEMIC_GOWN_SIZE,
1193 x_academic_hat_size => X_ACADEMIC_HAT_SIZE,
1194 x_guest_tickets_requested => X_GUEST_TICKETS_REQUESTED,
1195 x_guest_tickets_allocated => X_GUEST_TICKETS_ALLOCATED,
1196 x_guest_seats => X_GUEST_SEATS,
1197 x_fees_paid_ind => NVL(X_FEES_PAID_IND, 'N'),
1198 x_special_requirements => X_SPECIAL_REQUIREMENTS,
1199 x_comments => X_COMMENTS,
1200 x_creation_date => X_LAST_UPDATE_DATE,
1201 x_created_by => X_LAST_UPDATED_BY,
1202 x_last_update_date => X_LAST_UPDATE_DATE,
1203 x_last_updated_by => X_LAST_UPDATED_BY,
1204 x_last_update_login => X_LAST_UPDATE_LOGIN
1205 );
1206
1207 insert into IGS_GR_AWD_CRMN (
1208 GRADUAND_SEAT_NUMBER,
1209 NAME_PRONUNCIATION,
1210 NAME_ANNOUNCED,
1211 ACADEMIC_DRESS_RQRD_IND,
1212 ACADEMIC_GOWN_SIZE,
1213 ACADEMIC_HAT_SIZE,
1214 GUEST_TICKETS_REQUESTED,
1215 GUEST_TICKETS_ALLOCATED,
1216 GUEST_SEATS,
1217 FEES_PAID_IND,
1218 SPECIAL_REQUIREMENTS,
1219 COMMENTS,
1220 GAC_ID,
1221 PERSON_ID,
1222 CREATE_DT,
1223 GRD_CAL_TYPE,
1224 GRD_CI_SEQUENCE_NUMBER,
1225 CEREMONY_NUMBER,
1226 AWARD_COURSE_CD,
1227 AWARD_CRS_VERSION_NUMBER,
1228 AWARD_CD,
1229 US_GROUP_NUMBER,
1230 ORDER_IN_PRESENTATION,
1231 CREATION_DATE,
1232 CREATED_BY,
1233 LAST_UPDATE_DATE,
1234 LAST_UPDATED_BY,
1235 LAST_UPDATE_LOGIN,
1236 REQUEST_ID,
1237 PROGRAM_ID,
1238 PROGRAM_APPLICATION_ID,
1239 PROGRAM_UPDATE_DATE
1240 ) values (
1241 NEW_REFERENCES.GRADUAND_SEAT_NUMBER,
1242 NEW_REFERENCES.NAME_PRONUNCIATION,
1243 NEW_REFERENCES.NAME_ANNOUNCED,
1244 NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND,
1245 NEW_REFERENCES.ACADEMIC_GOWN_SIZE,
1246 NEW_REFERENCES.ACADEMIC_HAT_SIZE,
1247 NEW_REFERENCES.GUEST_TICKETS_REQUESTED,
1248 NEW_REFERENCES.GUEST_TICKETS_ALLOCATED,
1249 NEW_REFERENCES.GUEST_SEATS,
1250 NEW_REFERENCES.FEES_PAID_IND,
1251 NEW_REFERENCES.SPECIAL_REQUIREMENTS,
1252 NEW_REFERENCES.COMMENTS,
1253 NEW_REFERENCES.GAC_ID,
1254 NEW_REFERENCES.PERSON_ID,
1255 NEW_REFERENCES.CREATE_DT,
1256 NEW_REFERENCES.GRD_CAL_TYPE,
1257 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
1258 NEW_REFERENCES.CEREMONY_NUMBER,
1259 NEW_REFERENCES.AWARD_COURSE_CD,
1260 NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
1261 NEW_REFERENCES.AWARD_CD,
1262 NEW_REFERENCES.US_GROUP_NUMBER,
1263 NEW_REFERENCES.ORDER_IN_PRESENTATION,
1264 X_LAST_UPDATE_DATE,
1265 X_LAST_UPDATED_BY,
1266 X_LAST_UPDATE_DATE,
1267 X_LAST_UPDATED_BY,
1268 X_LAST_UPDATE_LOGIN,
1269 X_REQUEST_ID,
1270 X_PROGRAM_ID,
1271 X_PROGRAM_APPLICATION_ID,
1272 X_PROGRAM_UPDATE_DATE
1273 );
1274
1275 open c;
1276 fetch c into X_ROWID;
1277 if (c%notfound) then
1278 close c;
1279 raise no_data_found;
1280 end if;
1281 close c;
1282
1283 After_DML (
1284 p_action => 'INSERT',
1285 x_rowid => X_ROWID
1286 );
1287
1288 end INSERT_ROW;
1289
1290 procedure LOCK_ROW (
1291 X_ROWID in VARCHAR2,
1292 X_GAC_ID in NUMBER,
1293 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
1294 X_NAME_PRONUNCIATION in VARCHAR2,
1295 X_NAME_ANNOUNCED in VARCHAR2,
1296 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
1297 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
1298 X_ACADEMIC_HAT_SIZE in VARCHAR2,
1299 X_GUEST_TICKETS_REQUESTED in NUMBER,
1300 X_GUEST_TICKETS_ALLOCATED in NUMBER,
1301 X_GUEST_SEATS in VARCHAR2,
1302 X_FEES_PAID_IND in VARCHAR2,
1303 X_SPECIAL_REQUIREMENTS in VARCHAR2,
1304 X_COMMENTS in VARCHAR2,
1305 X_PERSON_ID in NUMBER,
1306 X_CREATE_DT in DATE,
1307 X_GRD_CAL_TYPE in VARCHAR2,
1308 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1309 X_CEREMONY_NUMBER in NUMBER,
1310 X_AWARD_COURSE_CD in VARCHAR2,
1311 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1312 X_AWARD_CD in VARCHAR2,
1313 X_US_GROUP_NUMBER in NUMBER,
1314 X_ORDER_IN_PRESENTATION in NUMBER
1315 ) AS
1316 cursor c1 is select
1317 GRADUAND_SEAT_NUMBER,
1318 NAME_PRONUNCIATION,
1319 NAME_ANNOUNCED,
1320 ACADEMIC_DRESS_RQRD_IND,
1321 ACADEMIC_GOWN_SIZE,
1322 ACADEMIC_HAT_SIZE,
1323 GUEST_TICKETS_REQUESTED,
1324 GUEST_TICKETS_ALLOCATED,
1325 GUEST_SEATS,
1326 FEES_PAID_IND,
1327 SPECIAL_REQUIREMENTS,
1328 COMMENTS,
1329 PERSON_ID,
1330 CREATE_DT,
1331 GRD_CAL_TYPE,
1332 GRD_CI_SEQUENCE_NUMBER,
1333 CEREMONY_NUMBER,
1334 AWARD_COURSE_CD,
1335 AWARD_CRS_VERSION_NUMBER,
1336 AWARD_CD,
1337 US_GROUP_NUMBER,
1338 ORDER_IN_PRESENTATION
1339 from IGS_GR_AWD_CRMN
1340 where ROWID = X_ROWID for update nowait;
1341 tlinfo c1%rowtype;
1342
1343 begin
1344 open c1;
1345 fetch c1 into tlinfo;
1346 if (c1%notfound) then
1347 close c1;
1348 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1349 app_exception.raise_exception;
1350 return;
1351 end if;
1352 close c1;
1353
1354 if ( ((tlinfo.GRADUAND_SEAT_NUMBER = X_GRADUAND_SEAT_NUMBER)
1355 OR ((tlinfo.GRADUAND_SEAT_NUMBER is null)
1356 AND (X_GRADUAND_SEAT_NUMBER is null)))
1357 AND ((tlinfo.NAME_PRONUNCIATION = X_NAME_PRONUNCIATION)
1358 OR ((tlinfo.NAME_PRONUNCIATION is null)
1359 AND (X_NAME_PRONUNCIATION is null)))
1360 AND ((tlinfo.NAME_ANNOUNCED = X_NAME_ANNOUNCED)
1361 OR ((tlinfo.NAME_ANNOUNCED is null)
1362 AND (X_NAME_ANNOUNCED is null)))
1363 AND (tlinfo.ACADEMIC_DRESS_RQRD_IND = X_ACADEMIC_DRESS_RQRD_IND)
1364 AND ((tlinfo.ACADEMIC_GOWN_SIZE = X_ACADEMIC_GOWN_SIZE)
1365 OR ((tlinfo.ACADEMIC_GOWN_SIZE is null)
1366 AND (X_ACADEMIC_GOWN_SIZE is null)))
1367 AND ((tlinfo.ACADEMIC_HAT_SIZE = X_ACADEMIC_HAT_SIZE)
1368 OR ((tlinfo.ACADEMIC_HAT_SIZE is null)
1369 AND (X_ACADEMIC_HAT_SIZE is null)))
1370 AND ((tlinfo.GUEST_TICKETS_REQUESTED = X_GUEST_TICKETS_REQUESTED)
1371 OR ((tlinfo.GUEST_TICKETS_REQUESTED is null)
1372 AND (X_GUEST_TICKETS_REQUESTED is null)))
1373 AND ((tlinfo.GUEST_TICKETS_ALLOCATED = X_GUEST_TICKETS_ALLOCATED)
1374 OR ((tlinfo.GUEST_TICKETS_ALLOCATED is null)
1375 AND (X_GUEST_TICKETS_ALLOCATED is null)))
1376 AND ((tlinfo.GUEST_SEATS = X_GUEST_SEATS)
1377 OR ((tlinfo.GUEST_SEATS is null)
1378 AND (X_GUEST_SEATS is null)))
1379 AND (tlinfo.FEES_PAID_IND = X_FEES_PAID_IND)
1380 AND ((tlinfo.SPECIAL_REQUIREMENTS = X_SPECIAL_REQUIREMENTS)
1381 OR ((tlinfo.SPECIAL_REQUIREMENTS is null)
1382 AND (X_SPECIAL_REQUIREMENTS is null)))
1383 AND ((tlinfo.COMMENTS = X_COMMENTS)
1384 OR ((tlinfo.COMMENTS is null)
1385 AND (X_COMMENTS is null)))
1386 AND (tlinfo.PERSON_ID = X_PERSON_ID)
1387 AND (tlinfo.CREATE_DT = X_CREATE_DT)
1388 AND (tlinfo.GRD_CAL_TYPE = X_GRD_CAL_TYPE)
1389 AND (tlinfo.GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER)
1390 AND (tlinfo.CEREMONY_NUMBER = X_CEREMONY_NUMBER)
1391 AND ((tlinfo.AWARD_COURSE_CD = X_AWARD_COURSE_CD)
1392 OR ((tlinfo.AWARD_COURSE_CD is null)
1393 AND (X_AWARD_COURSE_CD is null)))
1394 AND ((tlinfo.AWARD_CRS_VERSION_NUMBER = X_AWARD_CRS_VERSION_NUMBER)
1395 OR ((tlinfo.AWARD_CRS_VERSION_NUMBER is null)
1396 AND (X_AWARD_CRS_VERSION_NUMBER is null)))
1397 AND (tlinfo.AWARD_CD = X_AWARD_CD)
1398 AND ((tlinfo.US_GROUP_NUMBER = X_US_GROUP_NUMBER)
1399 OR ((tlinfo.US_GROUP_NUMBER is null)
1400 AND (X_US_GROUP_NUMBER is null)))
1401 AND ((tlinfo.ORDER_IN_PRESENTATION = X_ORDER_IN_PRESENTATION)
1402 OR ((tlinfo.ORDER_IN_PRESENTATION is null)
1403 AND (X_ORDER_IN_PRESENTATION is null)))
1404 ) then
1405 null;
1406 else
1407 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1408 app_exception.raise_exception;
1409 end if;
1410 return;
1411 end LOCK_ROW;
1412
1413 procedure UPDATE_ROW (
1414 X_ROWID in VARCHAR2,
1415 X_GAC_ID in NUMBER,
1416 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
1417 X_NAME_PRONUNCIATION in VARCHAR2,
1418 X_NAME_ANNOUNCED in VARCHAR2,
1419 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
1420 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
1421 X_ACADEMIC_HAT_SIZE in VARCHAR2,
1422 X_GUEST_TICKETS_REQUESTED in NUMBER,
1423 X_GUEST_TICKETS_ALLOCATED in NUMBER,
1424 X_GUEST_SEATS in VARCHAR2,
1425 X_FEES_PAID_IND in VARCHAR2,
1426 X_SPECIAL_REQUIREMENTS in VARCHAR2,
1427 X_COMMENTS in VARCHAR2,
1428 X_PERSON_ID in NUMBER,
1429 X_CREATE_DT in DATE,
1430 X_GRD_CAL_TYPE in VARCHAR2,
1431 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1432 X_CEREMONY_NUMBER in NUMBER,
1433 X_AWARD_COURSE_CD in VARCHAR2,
1434 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1435 X_AWARD_CD in VARCHAR2,
1436 X_US_GROUP_NUMBER in NUMBER,
1437 X_ORDER_IN_PRESENTATION in NUMBER,
1438 X_MODE in VARCHAR2
1439 ) AS
1440 X_LAST_UPDATE_DATE DATE;
1441 X_LAST_UPDATED_BY NUMBER;
1442 X_LAST_UPDATE_LOGIN NUMBER;
1443 X_REQUEST_ID NUMBER;
1444 X_PROGRAM_ID NUMBER;
1445 X_PROGRAM_APPLICATION_ID NUMBER;
1446 X_PROGRAM_UPDATE_DATE DATE;
1447 begin
1448 X_LAST_UPDATE_DATE := SYSDATE;
1449 if(X_MODE = 'I') then
1450 X_LAST_UPDATED_BY := 1;
1451 X_LAST_UPDATE_LOGIN := 0;
1452 elsif (X_MODE = 'R') then
1453 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1454 if X_LAST_UPDATED_BY is NULL then
1455 X_LAST_UPDATED_BY := -1;
1456 end if;
1457 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1458 if X_LAST_UPDATE_LOGIN is NULL then
1459 X_LAST_UPDATE_LOGIN := -1;
1460 end if;
1461 else
1462 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1463 app_exception.raise_exception;
1464 end if;
1465
1466 Before_DML (
1467 p_action => 'UPDATE',
1468 x_rowid => X_ROWID,
1469 x_GAC_ID => X_GAC_ID,
1470 x_person_id => X_PERSON_ID,
1471 x_create_dt => X_CREATE_DT,
1472 x_grd_cal_type => X_GRD_CAL_TYPE,
1473 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
1474 x_ceremony_number => X_CEREMONY_NUMBER,
1475 x_award_course_cd => X_AWARD_COURSE_CD,
1476 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
1477 x_award_cd => X_AWARD_CD,
1478 x_us_group_number => X_US_GROUP_NUMBER,
1479 x_order_in_presentation => X_ORDER_IN_PRESENTATION,
1480 x_graduand_seat_number => X_GRADUAND_SEAT_NUMBER,
1481 x_name_pronunciation => X_NAME_PRONUNCIATION,
1482 x_name_announced => X_NAME_ANNOUNCED,
1483 x_academic_dress_rqrd_ind => X_ACADEMIC_DRESS_RQRD_IND,
1484 x_academic_gown_size => X_ACADEMIC_GOWN_SIZE,
1485 x_academic_hat_size => X_ACADEMIC_HAT_SIZE,
1486 x_guest_tickets_requested => X_GUEST_TICKETS_REQUESTED,
1487 x_guest_tickets_allocated => X_GUEST_TICKETS_ALLOCATED,
1488 x_guest_seats => X_GUEST_SEATS,
1489 x_fees_paid_ind => X_FEES_PAID_IND,
1490 x_special_requirements => X_SPECIAL_REQUIREMENTS,
1491 x_comments => X_COMMENTS,
1492 x_creation_date => X_LAST_UPDATE_DATE,
1493 x_created_by => X_LAST_UPDATED_BY,
1494 x_last_update_date => X_LAST_UPDATE_DATE,
1495 x_last_updated_by => X_LAST_UPDATED_BY,
1496 x_last_update_login => X_LAST_UPDATE_LOGIN
1497 );
1498
1499 if (X_MODE = 'R') then
1500 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1501 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1502 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1503 if (X_REQUEST_ID = -1) then
1504 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1505 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1506 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1507 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1508 else
1509 X_PROGRAM_UPDATE_DATE := SYSDATE;
1510 end if;
1511 end if;
1512
1513 update IGS_GR_AWD_CRMN set
1514 GRADUAND_SEAT_NUMBER = NEW_REFERENCES.GRADUAND_SEAT_NUMBER,
1515 NAME_PRONUNCIATION = NEW_REFERENCES.NAME_PRONUNCIATION,
1516 NAME_ANNOUNCED = NEW_REFERENCES.NAME_ANNOUNCED,
1517 ACADEMIC_DRESS_RQRD_IND = NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND,
1518 ACADEMIC_GOWN_SIZE = NEW_REFERENCES.ACADEMIC_GOWN_SIZE,
1519 ACADEMIC_HAT_SIZE = NEW_REFERENCES.ACADEMIC_HAT_SIZE,
1520 GUEST_TICKETS_REQUESTED = NEW_REFERENCES.GUEST_TICKETS_REQUESTED,
1521 GUEST_TICKETS_ALLOCATED = NEW_REFERENCES.GUEST_TICKETS_ALLOCATED,
1522 GUEST_SEATS = NEW_REFERENCES.GUEST_SEATS,
1523 FEES_PAID_IND = NEW_REFERENCES.FEES_PAID_IND,
1524 SPECIAL_REQUIREMENTS = NEW_REFERENCES.SPECIAL_REQUIREMENTS,
1525 COMMENTS = NEW_REFERENCES.COMMENTS,
1526 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1527 CREATE_DT = NEW_REFERENCES.CREATE_DT,
1528 GRD_CAL_TYPE = NEW_REFERENCES.GRD_CAL_TYPE,
1529 GRD_CI_SEQUENCE_NUMBER = NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
1530 CEREMONY_NUMBER = NEW_REFERENCES.CEREMONY_NUMBER,
1531 AWARD_COURSE_CD = NEW_REFERENCES.AWARD_COURSE_CD,
1532 AWARD_CRS_VERSION_NUMBER = NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
1533 AWARD_CD = NEW_REFERENCES.AWARD_CD,
1534 US_GROUP_NUMBER = NEW_REFERENCES.US_GROUP_NUMBER,
1535 ORDER_IN_PRESENTATION = NEW_REFERENCES.ORDER_IN_PRESENTATION,
1536 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1537 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1538 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1539 REQUEST_ID = X_REQUEST_ID,
1540 PROGRAM_ID = X_PROGRAM_ID,
1541 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1542 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1543 where ROWID = X_ROWID
1544 ;
1545 if (sql%notfound) then
1546 raise no_data_found;
1547 end if;
1548
1549 After_DML (
1550 p_action => 'UPDATE',
1551 x_rowid => X_ROWID
1552 );
1553
1554 end UPDATE_ROW;
1555
1556 procedure ADD_ROW (
1557 X_ROWID in out NOCOPY VARCHAR2,
1558 X_GAC_ID in out NOCOPY NUMBER,
1559 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
1560 X_NAME_PRONUNCIATION in VARCHAR2,
1561 X_NAME_ANNOUNCED in VARCHAR2,
1562 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
1563 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
1564 X_ACADEMIC_HAT_SIZE in VARCHAR2,
1565 X_GUEST_TICKETS_REQUESTED in NUMBER,
1566 X_GUEST_TICKETS_ALLOCATED in NUMBER,
1567 X_GUEST_SEATS in VARCHAR2,
1568 X_FEES_PAID_IND in VARCHAR2,
1569 X_SPECIAL_REQUIREMENTS in VARCHAR2,
1570 X_COMMENTS in VARCHAR2,
1571 X_PERSON_ID in NUMBER,
1572 X_CREATE_DT in DATE,
1573 X_GRD_CAL_TYPE in VARCHAR2,
1574 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1575 X_CEREMONY_NUMBER in NUMBER,
1576 X_AWARD_COURSE_CD in VARCHAR2,
1577 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1578 X_AWARD_CD in VARCHAR2,
1579 X_US_GROUP_NUMBER in NUMBER,
1580 X_ORDER_IN_PRESENTATION in NUMBER,
1581 X_MODE in VARCHAR2
1582 ) AS
1583 cursor c1 is select rowid from IGS_GR_AWD_CRMN
1584 where GAC_ID = X_GAC_ID
1585 ;
1586
1587 begin
1588 open c1;
1589 fetch c1 into X_ROWID;
1590 if (c1%notfound) then
1591 close c1;
1592 INSERT_ROW (
1593 X_ROWID,
1594 X_GAC_ID,
1595 X_GRADUAND_SEAT_NUMBER,
1596 X_NAME_PRONUNCIATION,
1597 X_NAME_ANNOUNCED,
1598 X_ACADEMIC_DRESS_RQRD_IND,
1599 X_ACADEMIC_GOWN_SIZE,
1600 X_ACADEMIC_HAT_SIZE,
1601 X_GUEST_TICKETS_REQUESTED,
1602 X_GUEST_TICKETS_ALLOCATED,
1603 X_GUEST_SEATS,
1604 X_FEES_PAID_IND,
1605 X_SPECIAL_REQUIREMENTS,
1606 X_COMMENTS,
1607 X_PERSON_ID,
1608 X_CREATE_DT,
1609 X_GRD_CAL_TYPE,
1610 X_GRD_CI_SEQUENCE_NUMBER,
1611 X_CEREMONY_NUMBER,
1612 X_AWARD_COURSE_CD,
1613 X_AWARD_CRS_VERSION_NUMBER,
1614 X_AWARD_CD,
1615 X_US_GROUP_NUMBER,
1616 X_ORDER_IN_PRESENTATION,
1617 X_MODE);
1618 return;
1619 end if;
1620 close c1;
1621 UPDATE_ROW (
1622 X_ROWID,
1623 X_GAC_ID,
1624 X_GRADUAND_SEAT_NUMBER,
1625 X_NAME_PRONUNCIATION,
1626 X_NAME_ANNOUNCED,
1627 X_ACADEMIC_DRESS_RQRD_IND,
1628 X_ACADEMIC_GOWN_SIZE,
1629 X_ACADEMIC_HAT_SIZE,
1630 X_GUEST_TICKETS_REQUESTED,
1631 X_GUEST_TICKETS_ALLOCATED,
1632 X_GUEST_SEATS,
1633 X_FEES_PAID_IND,
1634 X_SPECIAL_REQUIREMENTS,
1635 X_COMMENTS,
1636 X_PERSON_ID,
1637 X_CREATE_DT,
1638 X_GRD_CAL_TYPE,
1639 X_GRD_CI_SEQUENCE_NUMBER,
1640 X_CEREMONY_NUMBER,
1641 X_AWARD_COURSE_CD,
1642 X_AWARD_CRS_VERSION_NUMBER,
1643 X_AWARD_CD,
1644 X_US_GROUP_NUMBER,
1645 X_ORDER_IN_PRESENTATION,
1646 X_MODE);
1647 end ADD_ROW;
1648
1649 procedure DELETE_ROW (
1650 X_ROWID in VARCHAR2
1651 ) AS
1652 begin
1653
1654 delete from IGS_GR_AWD_CRMN
1655 where ROWID = X_ROWID;
1656 if (sql%notfound) then
1657 raise no_data_found;
1658 end if;
1659
1660 After_DML (
1661 p_action => 'DELETE',
1662 x_rowid => X_ROWID
1663 );
1664
1665 end DELETE_ROW;
1666
1667 end IGS_GR_AWD_CRMN_PKG;