[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_AWD_CRMN_HIST_PKG
Source
1 package body IGS_GR_AWD_CRMN_HIST_PKG as
2 /* $Header: IGSGI04B.pls 115.4 2002/11/29 00:34:27 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_AWD_CRMN_HIST%RowType;
5 new_references IGS_GR_AWD_CRMN_HIST%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_GACH_ID in NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_create_dt IN DATE DEFAULT NULL,
13 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_ceremony_number IN NUMBER DEFAULT NULL,
16 x_award_course_cd IN VARCHAR2 DEFAULT NULL,
17 x_award_crs_version_number IN NUMBER DEFAULT NULL,
18 x_award_cd IN VARCHAR2 DEFAULT NULL,
19 x_hist_start_dt IN DATE DEFAULT NULL,
20 x_hist_end_dt IN DATE DEFAULT NULL,
21 x_hist_who IN NUMBER DEFAULT NULL,
22 x_us_group_number IN NUMBER DEFAULT NULL,
23 x_order_in_presentation IN NUMBER DEFAULT NULL,
24 x_graduand_seat_number IN VARCHAR2 DEFAULT NULL,
25 x_name_pronunciation IN VARCHAR2 DEFAULT NULL,
26 x_name_announced IN VARCHAR2 DEFAULT NULL,
27 x_academic_dress_rqrd_ind IN VARCHAR2 DEFAULT NULL,
28 x_academic_gown_size IN VARCHAR2 DEFAULT NULL,
29 x_academic_hat_size IN VARCHAR2 DEFAULT NULL,
30 x_guest_tickets_requested IN NUMBER DEFAULT NULL,
31 x_guest_tickets_allocated IN NUMBER DEFAULT NULL,
32 x_guest_seats IN VARCHAR2 DEFAULT NULL,
33 x_fees_paid_ind IN VARCHAR2 DEFAULT NULL,
34 x_special_requirements IN VARCHAR2 DEFAULT NULL,
35 x_comments IN VARCHAR2 DEFAULT NULL,
36 x_creation_date IN DATE DEFAULT NULL,
37 x_created_by IN NUMBER DEFAULT NULL,
38 x_last_update_date IN DATE DEFAULT NULL,
39 x_last_updated_by IN NUMBER DEFAULT NULL,
40 x_last_update_login IN NUMBER DEFAULT NULL
41 ) AS
42
43 CURSOR cur_old_ref_values IS
44 SELECT *
45 FROM IGS_GR_AWD_CRMN_HIST
46 WHERE rowid = x_rowid;
47
48 BEGIN
49
50 l_rowid := x_rowid;
51
52 -- Code for setting the Old and New Reference Values.
53 -- Populate Old Values.
54 Open cur_old_ref_values;
55 Fetch cur_old_ref_values INTO old_references;
56 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
57 Close cur_old_ref_values;
58 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 Return;
62 END IF;
63 Close cur_old_ref_values;
64
65 -- Populate New Values.
66 new_references.GACH_ID := x_GACH_ID;
67 new_references.person_id := x_person_id;
68 new_references.create_dt := x_create_dt;
69 new_references.grd_cal_type := x_grd_cal_type;
70 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
71 new_references.ceremony_number := x_ceremony_number;
72 new_references.award_course_cd := x_award_course_cd;
73 new_references.award_crs_version_number := x_award_crs_version_number;
74 new_references.award_cd := x_award_cd;
75 new_references.hist_start_dt := x_hist_start_dt;
76 new_references.hist_end_dt := x_hist_end_dt;
77 new_references.hist_who := x_hist_who;
78 new_references.us_group_number := x_us_group_number;
79 new_references.order_in_presentation := x_order_in_presentation;
80 new_references.graduand_seat_number := x_graduand_seat_number;
81 new_references.name_pronunciation := x_name_pronunciation;
82 new_references.name_announced := x_name_announced;
83 new_references.academic_dress_rqrd_ind := x_academic_dress_rqrd_ind;
84 new_references.academic_gown_size := x_academic_gown_size;
85 new_references.academic_hat_size := x_academic_hat_size;
86 new_references.guest_tickets_requested := x_guest_tickets_requested;
87 new_references.guest_tickets_allocated := x_guest_tickets_allocated;
88 new_references.guest_seats := x_guest_seats;
89 new_references.fees_paid_ind := x_fees_paid_ind;
90 new_references.special_requirements := x_special_requirements;
91 new_references.comments := x_comments;
92 IF (p_action = 'UPDATE') THEN
93 new_references.creation_date := old_references.creation_date;
94 new_references.created_by := old_references.created_by;
95 ELSE
96 new_references.creation_date := x_creation_date;
97 new_references.created_by := x_created_by;
98 END IF;
99 new_references.last_update_date := x_last_update_date;
100 new_references.last_updated_by := x_last_updated_by;
101 new_references.last_update_login := x_last_update_login;
102
103 END Set_Column_Values;
104
105 PROCEDURE Check_Parent_Existance AS
106 BEGIN
107
108 IF (((old_references.person_id = new_references.person_id) AND
109 (old_references.create_dt = new_references.create_dt)) OR
110 ((new_references.person_id IS NULL) OR
111 (new_references.create_dt IS NULL))) THEN
112 NULL;
113 ELSE
114 IF NOT IGS_GR_GRADUAND_PKG.Get_PK_For_Validation (
115 new_references.person_id,
116 new_references.create_dt
117 ) THEN
118 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
119 APP_EXCEPTION.RAISE_EXCEPTION;
120 END IF;
121 END IF;
122
123 END Check_Parent_Existance;
124
125 PROCEDURE Check_Uniqueness AS
126 BEGIN
127 IF Get_UK_For_Validation (
128 new_references.person_id,
129 new_references.create_dt,
130 new_references.grd_cal_type,
131 new_references.grd_ci_sequence_number,
132 new_references.ceremony_number,
133 new_references.award_course_cd,
134 new_references.award_crs_version_number,
135 new_references.award_cd,
136 new_references.hist_start_dt
137 ) THEN
138 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
139 APP_EXCEPTION.RAISE_EXCEPTION;
140 END IF;
141 END Check_Uniqueness;
142
143 PROCEDURE Check_Constraints(
144 Column_Name IN VARCHAR2 DEFAULT NULL,
145 Column_Value IN VARCHAR2 DEFAULT NULL
146 ) AS
147 BEGIN
148 IF Column_Name is null THEN
149 NULL;
150
151 ELSIF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
152 new_references.GRD_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
153
154 ELSIF upper(Column_name) = 'ORDER_IN_PRESENTATION' THEN
155 new_references.ORDER_IN_PRESENTATION:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
156
157 ELSIF upper(Column_name) = 'CEREMONY_NUMBER' THEN
158 new_references.CEREMONY_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
159
160 ELSIF upper(Column_name) = 'GUEST_TICKETS_ALLOCATED' THEN
161 new_references.GUEST_TICKETS_ALLOCATED:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
162
163 ELSIF upper(Column_name) = 'GUEST_TICKETS_REQUESTED' THEN
164 new_references.GUEST_TICKETS_REQUESTED := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
165
166 ELSIF upper(Column_name) = 'FEES_PAID_IND' THEN
167 new_references.FEES_PAID_IND:= COLUMN_VALUE ;
168
169 ELSIF upper(Column_name) = 'ACADEMIC_DRESS_RQRD_IND' THEN
170 new_references.ACADEMIC_DRESS_RQRD_IND:= COLUMN_VALUE ;
171
172 ELSIF upper(Column_name) = 'ACADEMIC_DRESS_RQRD_IND' THEN
173 new_references.ACADEMIC_DRESS_RQRD_IND:= COLUMN_VALUE ;
174
175 ELSIF upper(Column_name) = 'ACADEMIC_GOWN_SIZE' THEN
176 new_references.ACADEMIC_GOWN_SIZE:= COLUMN_VALUE ;
177
178 ELSIF upper(Column_name) = 'ACADEMIC_HAT_SIZE' THEN
179 new_references.ACADEMIC_HAT_SIZE:= COLUMN_VALUE ;
180
181 ELSIF upper(Column_name) = 'AWARD_CD' THEN
182 new_references.AWARD_CD:= COLUMN_VALUE ;
183
184 ELSIF upper(Column_name) = 'AWARD_COURSE_CD' THEN
185 new_references.AWARD_COURSE_CD:= COLUMN_VALUE ;
186
187 ELSIF upper(Column_name) = 'GRADUAND_SEAT_NUMBER' THEN
188 new_references.GRADUAND_SEAT_NUMBER:= COLUMN_VALUE ;
189
190 ELSIF upper(Column_name) = 'GRD_CAL_TYPE' THEN
191 new_references.GRD_CAL_TYPE:= COLUMN_VALUE ;
192
193 ELSIF upper(Column_name) = 'GUEST_SEATS' THEN
194 new_references.GUEST_SEATS:= COLUMN_VALUE ;
195
196 ELSIF upper(Column_name) = 'HIST_WHO' THEN
197 new_references.HIST_WHO:= COLUMN_VALUE ;
198
199 ELSIF upper(Column_name) = 'NAME_ANNOUNCED' THEN
200 new_references.NAME_ANNOUNCED:= COLUMN_VALUE ;
201
202 ELSIF upper(Column_name) = 'NAME_PRONUNCIATION' THEN
203 new_references.NAME_PRONUNCIATION:= COLUMN_VALUE ;
204
205 ELSIF upper(Column_name) = 'SPECIAL_REQUIREMENTS' THEN
206 new_references.SPECIAL_REQUIREMENTS:= COLUMN_VALUE ;
207
208 END IF ;
209
210 IF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
211 IF new_references.GRD_CI_SEQUENCE_NUMBER < 0 OR new_references.GRD_CI_SEQUENCE_NUMBER > 999999 THEN
212 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception ;
215 END IF;
216 END IF;
217
218 IF upper(Column_name) = 'ORDER_IN_PRESENTATION' OR COLUMN_NAME IS NULL THEN
219 IF new_references.ORDER_IN_PRESENTATION < 0 OR new_references.ORDER_IN_PRESENTATION > 9999 THEN
220 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception ;
223 END IF;
224 END IF;
225
226 IF upper(Column_name) = 'CEREMONY_NUMBER' OR COLUMN_NAME IS NULL THEN
227 IF new_references.CEREMONY_NUMBER < 0 OR new_references.CEREMONY_NUMBER > 999999 THEN
228 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception ;
231 END IF;
232 END IF;
233
234 IF upper(Column_name) = 'GUEST_TICKETS_ALLOCATED' OR COLUMN_NAME IS NULL THEN
235 IF new_references.GUEST_TICKETS_ALLOCATED < 0 OR new_references.GUEST_TICKETS_ALLOCATED > 999 THEN
236 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
237 IGS_GE_MSG_STACK.ADD;
238 App_Exception.Raise_Exception ;
239 END IF;
240 END IF;
241
242 IF upper(Column_name) = 'GUEST_TICKETS_REQUESTED' OR COLUMN_NAME IS NULL THEN
243 IF new_references.GUEST_TICKETS_REQUESTED < 0 OR new_references.GUEST_TICKETS_REQUESTED > 999 THEN
244 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception ;
247 END IF;
248 END IF;
249
250 IF upper(Column_name) = 'FEES_PAID_IND' OR COLUMN_NAME IS NULL THEN
251 IF new_references.FEES_PAID_IND not in ('Y','N') then
252 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception ;
255 END IF;
256 END IF ;
257
258 IF upper(Column_name) = 'ACADEMIC_DRESS_RQRD_IND' OR COLUMN_NAME IS NULL THEN
259 IF new_references.ACADEMIC_DRESS_RQRD_IND not in ('Y','N') then
260 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception ;
263 END IF;
264 END IF ;
265
266 IF upper(Column_name) = 'ACADEMIC_GOWN_SIZE' OR COLUMN_NAME IS NULL THEN
267 IF new_references.ACADEMIC_GOWN_SIZE<> upper(NEW_REFERENCES.ACADEMIC_GOWN_SIZE) then
268 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception ;
271 END IF;
272
273 END IF ;
274
275 IF upper(Column_name) = 'ACADEMIC_HAT_SIZE' OR COLUMN_NAME IS NULL THEN
276 IF new_references.ACADEMIC_HAT_SIZE<> upper(NEW_REFERENCES.ACADEMIC_HAT_SIZE) then
277 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception ;
280 END IF;
281
282 END IF ;
283
284 IF upper(Column_name) = 'AWARD_CD' OR COLUMN_NAME IS NULL THEN
285 IF new_references.AWARD_CD<> upper(NEW_REFERENCES.AWARD_CD) then
286 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception ;
289 END IF;
290
291 END IF ;
292
293 IF upper(Column_name) = 'AWARD_COURSE_CD' OR COLUMN_NAME IS NULL THEN
294 IF new_references.AWARD_COURSE_CD<> upper(NEW_REFERENCES.AWARD_COURSE_CD) then
295 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
296 IGS_GE_MSG_STACK.ADD;
297 App_Exception.Raise_Exception ;
298 END IF;
299
300 END IF ;
301
302 IF upper(Column_name) = 'GRADUAND_SEAT_NUMBER' OR COLUMN_NAME IS NULL THEN
303 IF new_references.GRADUAND_SEAT_NUMBER<> upper(NEW_REFERENCES.GRADUAND_SEAT_NUMBER) then
304 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception ;
307 END IF;
308
309 END IF ;
310
311 IF upper(Column_name) = 'GRD_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
312 IF new_references.GRD_CAL_TYPE<> upper(NEW_REFERENCES.GRD_CAL_TYPE) then
313 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
314 IGS_GE_MSG_STACK.ADD;
315 App_Exception.Raise_Exception ;
316 END IF;
317
318 END IF ;
319
320 IF upper(Column_name) = 'GUEST_SEATS' OR COLUMN_NAME IS NULL THEN
321 IF new_references.GUEST_SEATS<> upper(NEW_REFERENCES.GUEST_SEATS) then
322 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
323 IGS_GE_MSG_STACK.ADD;
324 App_Exception.Raise_Exception ;
325 END IF;
326
327 END IF ;
328
329 IF upper(Column_name) = 'HIST_WHO' OR COLUMN_NAME IS NULL THEN
330 IF new_references.HIST_WHO<> upper(NEW_REFERENCES.HIST_WHO) then
331 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
332 IGS_GE_MSG_STACK.ADD;
333 App_Exception.Raise_Exception ;
334 END IF;
335
336 END IF ;
337
338 IF upper(Column_name) = 'NAME_ANNOUNCED' OR COLUMN_NAME IS NULL THEN
339 IF new_references.NAME_ANNOUNCED<> upper(NEW_REFERENCES.NAME_ANNOUNCED) then
340 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
341 IGS_GE_MSG_STACK.ADD;
342 App_Exception.Raise_Exception ;
343 END IF;
344
345 END IF ;
346
347 IF upper(Column_name) = 'NAME_PRONUNCIATION' OR COLUMN_NAME IS NULL THEN
348 IF new_references.NAME_PRONUNCIATION<> upper(NEW_REFERENCES.NAME_PRONUNCIATION) then
349 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception ;
352 END IF;
353
354 END IF ;
355
356 IF upper(Column_name) = 'SPECIAL_REQUIREMENTS' OR COLUMN_NAME IS NULL THEN
357 IF new_references.SPECIAL_REQUIREMENTS<> upper(NEW_REFERENCES.SPECIAL_REQUIREMENTS) then
358 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
359 IGS_GE_MSG_STACK.ADD;
360 App_Exception.Raise_Exception ;
361 END IF;
362
363 END IF ;
364 END Check_Constraints;
365
366 FUNCTION Get_PK_For_Validation (
367 x_GACH_ID IN NUMBER
368 ) RETURN BOOLEAN AS
369
370 CURSOR cur_rowid IS
371 SELECT rowid
372 FROM IGS_GR_AWD_CRMN_HIST
373 WHERE GACH_ID = x_GACH_ID
374 FOR UPDATE NOWAIT;
375
376 lv_rowid cur_rowid%RowType;
377
378 BEGIN
379
380 Open cur_rowid;
381 Fetch cur_rowid INTO lv_rowid;
382 IF (cur_rowid%FOUND) THEN
383 Close cur_rowid;
384 Return (TRUE);
385 ELSE
386 Close cur_rowid;
387 Return (FALSE);
388 END IF;
389
390 END Get_PK_For_Validation;
391
392 FUNCTION Get_UK_For_Validation (
393 x_person_id IN NUMBER,
394 x_create_dt IN DATE,
395 x_grd_cal_type IN VARCHAR2,
396 x_grd_ci_sequence_number IN NUMBER,
397 x_ceremony_number IN NUMBER,
398 x_award_course_cd IN VARCHAR2,
399 x_award_crs_version_number IN NUMBER,
400 x_award_cd IN VARCHAR2,
401 x_hist_start_dt IN DATE
402 ) RETURN BOOLEAN AS
403 CURSOR cur_rowid IS
404 SELECT rowid
405 FROM IGS_GR_AWD_CRMN_HIST
406 WHERE person_id = x_person_id
407 AND create_dt = x_create_dt
408 AND grd_cal_type = x_grd_cal_type
409 AND grd_ci_sequence_number = x_grd_ci_sequence_number
410 AND ceremony_number = x_ceremony_number
411 AND award_course_cd = x_award_course_cd
412 AND award_crs_version_number = x_award_crs_version_number
413 AND award_cd = x_award_cd
414 AND hist_start_dt = x_hist_start_dt
415 AND (l_rowid is null or rowid <> l_rowid)
416 FOR UPDATE NOWAIT;
417
418 lv_rowid cur_rowid%RowType;
419
420 BEGIN
421
422 Open cur_rowid;
423 Fetch cur_rowid INTO lv_rowid;
424 IF (cur_rowid%FOUND) THEN
425 Close cur_rowid;
426 Return (TRUE);
427 ELSE
428 Close cur_rowid;
429 Return (FALSE);
430 END IF;
431
432 END Get_UK_For_Validation;
433
434 PROCEDURE GET_FK_IGS_GR_GRADUAND (
435 x_person_id IN NUMBER,
436 x_create_dt IN DATE
437 ) AS
438
439 CURSOR cur_rowid IS
440 SELECT rowid
441 FROM IGS_GR_AWD_CRMN_HIST
442 WHERE person_id = x_person_id
443 AND create_dt = x_create_dt ;
444
445 lv_rowid cur_rowid%RowType;
446
447 BEGIN
448
449 Open cur_rowid;
450 Fetch cur_rowid INTO lv_rowid;
451 IF (cur_rowid%FOUND) THEN
452 Close cur_rowid;
453 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GACH_GR_FK');
454 IGS_GE_MSG_STACK.ADD;
455 App_Exception.Raise_Exception;
456 Return;
457 END IF;
458 Close cur_rowid;
459
460 END GET_FK_IGS_GR_GRADUAND;
461
462 PROCEDURE Before_DML (
463 p_action IN VARCHAR2,
464 x_rowid IN VARCHAR2 DEFAULT NULL,
465 x_GACH_ID in NUMBER DEFAULT NULL,
466 x_person_id IN NUMBER DEFAULT NULL,
467 x_create_dt IN DATE DEFAULT NULL,
468 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
469 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
470 x_ceremony_number IN NUMBER DEFAULT NULL,
471 x_award_course_cd IN VARCHAR2 DEFAULT NULL,
472 x_award_crs_version_number IN NUMBER DEFAULT NULL,
473 x_award_cd IN VARCHAR2 DEFAULT NULL,
474 x_hist_start_dt IN DATE DEFAULT NULL,
475 x_hist_end_dt IN DATE DEFAULT NULL,
476 x_hist_who IN NUMBER DEFAULT NULL,
477 x_us_group_number IN NUMBER DEFAULT NULL,
478 x_order_in_presentation IN NUMBER DEFAULT NULL,
479 x_graduand_seat_number IN VARCHAR2 DEFAULT NULL,
480 x_name_pronunciation IN VARCHAR2 DEFAULT NULL,
481 x_name_announced IN VARCHAR2 DEFAULT NULL,
482 x_academic_dress_rqrd_ind IN VARCHAR2 DEFAULT NULL,
483 x_academic_gown_size IN VARCHAR2 DEFAULT NULL,
484 x_academic_hat_size IN VARCHAR2 DEFAULT NULL,
485 x_guest_tickets_requested IN NUMBER DEFAULT NULL,
486 x_guest_tickets_allocated IN NUMBER DEFAULT NULL,
487 x_guest_seats IN VARCHAR2 DEFAULT NULL,
488 x_fees_paid_ind IN VARCHAR2 DEFAULT NULL,
489 x_special_requirements IN VARCHAR2 DEFAULT NULL,
490 x_comments IN VARCHAR2 DEFAULT NULL,
491 x_creation_date IN DATE DEFAULT NULL,
492 x_created_by IN NUMBER DEFAULT NULL,
493 x_last_update_date IN DATE DEFAULT NULL,
494 x_last_updated_by IN NUMBER DEFAULT NULL,
495 x_last_update_login IN NUMBER DEFAULT NULL
496 ) AS
497 BEGIN
498
499 Set_Column_Values (
500 p_action,
501 x_rowid,
502 x_GACH_ID,
503 x_person_id,
504 x_create_dt,
505 x_grd_cal_type,
506 x_grd_ci_sequence_number,
507 x_ceremony_number,
508 x_award_course_cd,
509 x_award_crs_version_number,
510 x_award_cd,
511 x_hist_start_dt,
512 x_hist_end_dt,
513 x_hist_who,
514 x_us_group_number,
515 x_order_in_presentation,
516 x_graduand_seat_number,
517 x_name_pronunciation,
518 x_name_announced,
519 x_academic_dress_rqrd_ind,
520 x_academic_gown_size,
521 x_academic_hat_size,
522 x_guest_tickets_requested,
523 x_guest_tickets_allocated,
524 x_guest_seats,
525 x_fees_paid_ind,
526 x_special_requirements,
527 x_comments,
528 x_creation_date,
529 x_created_by,
530 x_last_update_date,
531 x_last_updated_by,
532 x_last_update_login
533 );
534
535 IF (p_action = 'INSERT') THEN
536 -- Call all the procedures related to Before Insert.
537 IF Get_PK_For_Validation (
538 NEW_REFERENCES.gach_id) THEN
539 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
540 IGS_GE_MSG_STACK.ADD;
541 App_Exception.Raise_Exception;
542 END IF;
543 Check_Uniqueness;
544 Check_Constraints;
545 Check_Parent_Existance;
546 ELSIF (p_action = 'UPDATE') THEN
547 -- Call all the procedures related to Before Update.
548 Check_Uniqueness;
549 Check_Constraints;
550 Check_Parent_Existance;
551 ELSIF (p_action = 'VALIDATE_INSERT') THEN
552 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.GACH_ID) THEN
553 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
554 IGS_GE_MSG_STACK.ADD;
555 App_Exception.Raise_Exception;
556 END IF;
557 check_uniqueness;
558 check_constraints;
559 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
560 check_uniqueness;
561 check_constraints;
562 END IF;
563
564 END Before_DML;
565
566 procedure INSERT_ROW (
567 X_ROWID in out NOCOPY VARCHAR2,
568 X_GACH_ID in out NOCOPY NUMBER,
569 X_NAME_PRONUNCIATION in VARCHAR2,
570 X_NAME_ANNOUNCED in VARCHAR2,
571 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
572 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
573 X_ACADEMIC_HAT_SIZE in VARCHAR2,
574 X_GUEST_TICKETS_REQUESTED in NUMBER,
575 X_GUEST_TICKETS_ALLOCATED in NUMBER,
576 X_GUEST_SEATS in VARCHAR2,
577 X_FEES_PAID_IND in VARCHAR2,
578 X_SPECIAL_REQUIREMENTS in VARCHAR2,
579 X_COMMENTS in VARCHAR2,
580 X_PERSON_ID in NUMBER,
581 X_CREATE_DT in DATE,
582 X_GRD_CAL_TYPE in VARCHAR2,
583 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
584 X_HIST_WHO in NUMBER,
585 X_US_GROUP_NUMBER in NUMBER,
586 X_ORDER_IN_PRESENTATION in NUMBER,
587 X_HIST_END_DT in DATE,
588 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
589 X_CEREMONY_NUMBER in NUMBER,
590 X_AWARD_COURSE_CD in VARCHAR2,
591 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
592 X_AWARD_CD in VARCHAR2,
593 X_HIST_START_DT in DATE,
594 X_MODE in VARCHAR2 default 'R'
595 ) AS
596 cursor C is select ROWID from IGS_GR_AWD_CRMN_HIST
597 where GACH_ID = X_GACH_ID;
598 X_LAST_UPDATE_DATE DATE;
599 X_LAST_UPDATED_BY NUMBER;
600 X_LAST_UPDATE_LOGIN NUMBER;
601 begin
602
603 SELECT IGS_GR_AWD_CRMN_HIST_GACH_ID_S.NEXTVAL INTO X_GACH_ID FROM DUAL;
604
605 X_LAST_UPDATE_DATE := SYSDATE;
606 if(X_MODE = 'I') then
607 X_LAST_UPDATED_BY := 1;
608 X_LAST_UPDATE_LOGIN := 0;
609 elsif (X_MODE = 'R') then
610 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
611 if X_LAST_UPDATED_BY is NULL then
612 X_LAST_UPDATED_BY := -1;
613 end if;
614 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
615 if X_LAST_UPDATE_LOGIN is NULL then
616 X_LAST_UPDATE_LOGIN := -1;
617 end if;
618 else
619 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
620 IGS_GE_MSG_STACK.ADD;
621 app_exception.raise_exception;
622 end if;
623
624 Before_DML (
625 p_action => 'INSERT',
626 x_rowid => X_ROWID,
627 x_GACH_ID => X_GACH_ID,
628 x_person_id => X_PERSON_ID,
629 x_create_dt => X_CREATE_DT,
630 x_grd_cal_type => X_GRD_CAL_TYPE,
631 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
632 x_ceremony_number => X_CEREMONY_NUMBER,
633 x_award_course_cd => X_AWARD_COURSE_CD,
634 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
635 x_award_cd => X_AWARD_CD,
636 x_hist_start_dt => X_HIST_START_DT,
637 x_hist_end_dt => X_HIST_END_DT,
638 x_hist_who => X_HIST_WHO,
639 x_us_group_number => X_US_GROUP_NUMBER,
640 x_order_in_presentation => X_ORDER_IN_PRESENTATION,
641 x_graduand_seat_number => X_GRADUAND_SEAT_NUMBER,
642 x_name_pronunciation => X_NAME_PRONUNCIATION,
643 x_name_announced => X_NAME_ANNOUNCED,
644 x_academic_dress_rqrd_ind => X_ACADEMIC_DRESS_RQRD_IND,
645 x_academic_gown_size => X_ACADEMIC_GOWN_SIZE,
646 x_academic_hat_size => X_ACADEMIC_HAT_SIZE,
647 x_guest_tickets_requested => X_GUEST_TICKETS_REQUESTED,
648 x_guest_tickets_allocated => X_GUEST_TICKETS_ALLOCATED,
649 x_guest_seats => X_GUEST_SEATS,
650 x_fees_paid_ind => X_FEES_PAID_IND,
651 x_special_requirements => X_SPECIAL_REQUIREMENTS,
652 x_comments => X_COMMENTS,
653 x_creation_date => X_LAST_UPDATE_DATE,
654 x_created_by => X_LAST_UPDATED_BY,
655 x_last_update_date => X_LAST_UPDATE_DATE,
656 x_last_updated_by => X_LAST_UPDATED_BY,
657 x_last_update_login => X_LAST_UPDATE_LOGIN
658 );
659
660 insert into IGS_GR_AWD_CRMN_HIST (
661 NAME_PRONUNCIATION,
662 NAME_ANNOUNCED,
663 ACADEMIC_DRESS_RQRD_IND,
664 ACADEMIC_GOWN_SIZE,
665 ACADEMIC_HAT_SIZE,
666 GUEST_TICKETS_REQUESTED,
667 GUEST_TICKETS_ALLOCATED,
668 GUEST_SEATS,
669 FEES_PAID_IND,
670 SPECIAL_REQUIREMENTS,
671 COMMENTS,
672 GACH_ID,
673 PERSON_ID,
674 CREATE_DT,
675 GRD_CAL_TYPE,
676 GRADUAND_SEAT_NUMBER,
677 HIST_WHO,
678 US_GROUP_NUMBER,
679 ORDER_IN_PRESENTATION,
680 HIST_END_DT,
681 GRD_CI_SEQUENCE_NUMBER,
682 CEREMONY_NUMBER,
683 AWARD_COURSE_CD,
684 AWARD_CRS_VERSION_NUMBER,
685 AWARD_CD,
686 HIST_START_DT,
687 CREATION_DATE,
688 CREATED_BY,
689 LAST_UPDATE_DATE,
690 LAST_UPDATED_BY,
691 LAST_UPDATE_LOGIN
692 ) values (
693 NEW_REFERENCES.NAME_PRONUNCIATION,
694 NEW_REFERENCES.NAME_ANNOUNCED,
695 NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND,
696 NEW_REFERENCES.ACADEMIC_GOWN_SIZE,
697 NEW_REFERENCES.ACADEMIC_HAT_SIZE,
698 NEW_REFERENCES.GUEST_TICKETS_REQUESTED,
699 NEW_REFERENCES.GUEST_TICKETS_ALLOCATED,
700 NEW_REFERENCES.GUEST_SEATS,
701 NEW_REFERENCES.FEES_PAID_IND,
702 NEW_REFERENCES.SPECIAL_REQUIREMENTS,
703 NEW_REFERENCES.COMMENTS,
704 NEW_REFERENCES.GACH_ID,
705 NEW_REFERENCES.PERSON_ID,
706 NEW_REFERENCES.CREATE_DT,
707 NEW_REFERENCES.GRD_CAL_TYPE,
708 NEW_REFERENCES.GRADUAND_SEAT_NUMBER,
709 NEW_REFERENCES.HIST_WHO,
710 NEW_REFERENCES.US_GROUP_NUMBER,
711 NEW_REFERENCES.ORDER_IN_PRESENTATION,
712 NEW_REFERENCES.HIST_END_DT,
713 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
714 NEW_REFERENCES.CEREMONY_NUMBER,
715 NEW_REFERENCES.AWARD_COURSE_CD,
716 NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
717 NEW_REFERENCES.AWARD_CD,
718 NEW_REFERENCES.HIST_START_DT,
719 X_LAST_UPDATE_DATE,
720 X_LAST_UPDATED_BY,
721 X_LAST_UPDATE_DATE,
722 X_LAST_UPDATED_BY,
723 X_LAST_UPDATE_LOGIN
724 );
725
726 open c;
727 fetch c into X_ROWID;
728 if (c%notfound) then
729 close c;
730 raise no_data_found;
731 end if;
732 close c;
733
734 end INSERT_ROW;
735
736 procedure LOCK_ROW (
737 X_ROWID in VARCHAR2,
738 X_GACH_ID in NUMBER,
739 X_NAME_PRONUNCIATION in VARCHAR2,
740 X_NAME_ANNOUNCED in VARCHAR2,
741 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
742 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
743 X_ACADEMIC_HAT_SIZE in VARCHAR2,
744 X_GUEST_TICKETS_REQUESTED in NUMBER,
745 X_GUEST_TICKETS_ALLOCATED in NUMBER,
746 X_GUEST_SEATS in VARCHAR2,
747 X_FEES_PAID_IND in VARCHAR2,
748 X_SPECIAL_REQUIREMENTS in VARCHAR2,
749 X_COMMENTS in VARCHAR2,
750 X_PERSON_ID in NUMBER,
751 X_CREATE_DT in DATE,
752 X_GRD_CAL_TYPE in VARCHAR2,
753 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
754 X_HIST_WHO in NUMBER,
755 X_US_GROUP_NUMBER in NUMBER,
756 X_ORDER_IN_PRESENTATION in NUMBER,
757 X_HIST_END_DT in DATE,
758 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
759 X_CEREMONY_NUMBER in NUMBER,
760 X_AWARD_COURSE_CD in VARCHAR2,
761 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
762 X_AWARD_CD in VARCHAR2,
763 X_HIST_START_DT in DATE
764 ) AS
765 cursor c1 is select
766 NAME_PRONUNCIATION,
767 NAME_ANNOUNCED,
768 ACADEMIC_DRESS_RQRD_IND,
769 ACADEMIC_GOWN_SIZE,
770 ACADEMIC_HAT_SIZE,
771 GUEST_TICKETS_REQUESTED,
772 GUEST_TICKETS_ALLOCATED,
773 GUEST_SEATS,
774 FEES_PAID_IND,
775 SPECIAL_REQUIREMENTS,
776 COMMENTS,
777 PERSON_ID,
778 CREATE_DT,
779 GRD_CAL_TYPE,
780 GRADUAND_SEAT_NUMBER,
781 HIST_WHO,
782 US_GROUP_NUMBER,
783 ORDER_IN_PRESENTATION,
784 HIST_END_DT,
785 GRD_CI_SEQUENCE_NUMBER,
786 CEREMONY_NUMBER,
787 AWARD_COURSE_CD,
788 AWARD_CRS_VERSION_NUMBER,
789 AWARD_CD,
790 HIST_START_DT
791 from IGS_GR_AWD_CRMN_HIST
792 where ROWID = X_ROWID for update nowait;
793 tlinfo c1%rowtype;
794
795 begin
796 open c1;
797 fetch c1 into tlinfo;
798 if (c1%notfound) then
799 close c1;
800 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
801 app_exception.raise_exception;
802 return;
803 end if;
804 close c1;
805
806 if ( ((tlinfo.NAME_PRONUNCIATION = X_NAME_PRONUNCIATION)
807 OR ((tlinfo.NAME_PRONUNCIATION is null)
808 AND (X_NAME_PRONUNCIATION is null)))
809 AND ((tlinfo.NAME_ANNOUNCED = X_NAME_ANNOUNCED)
810 OR ((tlinfo.NAME_ANNOUNCED is null)
811 AND (X_NAME_ANNOUNCED is null)))
812 AND ((tlinfo.ACADEMIC_DRESS_RQRD_IND = X_ACADEMIC_DRESS_RQRD_IND)
813 OR ((tlinfo.ACADEMIC_DRESS_RQRD_IND is null)
814 AND (X_ACADEMIC_DRESS_RQRD_IND is null)))
815 AND ((tlinfo.ACADEMIC_GOWN_SIZE = X_ACADEMIC_GOWN_SIZE)
816 OR ((tlinfo.ACADEMIC_GOWN_SIZE is null)
817 AND (X_ACADEMIC_GOWN_SIZE is null)))
818 AND ((tlinfo.ACADEMIC_HAT_SIZE = X_ACADEMIC_HAT_SIZE)
819 OR ((tlinfo.ACADEMIC_HAT_SIZE is null)
820 AND (X_ACADEMIC_HAT_SIZE is null)))
821 AND ((tlinfo.GUEST_TICKETS_REQUESTED = X_GUEST_TICKETS_REQUESTED)
822 OR ((tlinfo.GUEST_TICKETS_REQUESTED is null)
823 AND (X_GUEST_TICKETS_REQUESTED is null)))
824 AND ((tlinfo.GUEST_TICKETS_ALLOCATED = X_GUEST_TICKETS_ALLOCATED)
825 OR ((tlinfo.GUEST_TICKETS_ALLOCATED is null)
826 AND (X_GUEST_TICKETS_ALLOCATED is null)))
827 AND ((tlinfo.GUEST_SEATS = X_GUEST_SEATS)
828 OR ((tlinfo.GUEST_SEATS is null)
829 AND (X_GUEST_SEATS is null)))
830 AND ((tlinfo.FEES_PAID_IND = X_FEES_PAID_IND)
831 OR ((tlinfo.FEES_PAID_IND is null)
832 AND (X_FEES_PAID_IND is null)))
833 AND ((tlinfo.SPECIAL_REQUIREMENTS = X_SPECIAL_REQUIREMENTS)
834 OR ((tlinfo.SPECIAL_REQUIREMENTS is null)
835 AND (X_SPECIAL_REQUIREMENTS is null)))
836 AND ((tlinfo.COMMENTS = X_COMMENTS)
837 OR ((tlinfo.COMMENTS is null)
838 AND (X_COMMENTS is null)))
839 AND (tlinfo.PERSON_ID = X_PERSON_ID)
840 AND (tlinfo.CREATE_DT = X_CREATE_DT)
841 AND (tlinfo.GRD_CAL_TYPE = X_GRD_CAL_TYPE)
842 AND ((tlinfo.GRADUAND_SEAT_NUMBER = X_GRADUAND_SEAT_NUMBER)
843 OR ((tlinfo.GRADUAND_SEAT_NUMBER is null)
844 AND (X_GRADUAND_SEAT_NUMBER is null)))
845 AND (tlinfo.HIST_WHO = X_HIST_WHO)
846 AND ((tlinfo.US_GROUP_NUMBER = X_US_GROUP_NUMBER)
847 OR ((tlinfo.US_GROUP_NUMBER is null)
848 AND (X_US_GROUP_NUMBER is null)))
849 AND ((tlinfo.ORDER_IN_PRESENTATION = X_ORDER_IN_PRESENTATION)
850 OR ((tlinfo.ORDER_IN_PRESENTATION is null)
851 AND (X_ORDER_IN_PRESENTATION is null)))
852 AND (tlinfo.HIST_END_DT = X_HIST_END_DT)
853 AND (tlinfo.GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER)
854 AND (tlinfo.CEREMONY_NUMBER = X_CEREMONY_NUMBER)
855 AND ((tlinfo.AWARD_COURSE_CD = X_AWARD_COURSE_CD)
856 OR ((tlinfo.AWARD_COURSE_CD is null)
857 AND (X_AWARD_COURSE_CD is null)))
858 AND ((tlinfo.AWARD_CRS_VERSION_NUMBER = X_AWARD_CRS_VERSION_NUMBER)
859 OR ((tlinfo.AWARD_CRS_VERSION_NUMBER is null)
860 AND (X_AWARD_CRS_VERSION_NUMBER is null)))
861 AND (tlinfo.AWARD_CD = X_AWARD_CD)
862 AND (tlinfo.HIST_START_DT = X_HIST_START_DT)
863 ) then
864 null;
865 else
866 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
867 app_exception.raise_exception;
868 end if;
869 return;
870 end LOCK_ROW;
871
872 procedure UPDATE_ROW (
873 X_ROWID in VARCHAR2,
874 X_GACH_ID in NUMBER,
875 X_NAME_PRONUNCIATION in VARCHAR2,
876 X_NAME_ANNOUNCED in VARCHAR2,
877 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
878 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
879 X_ACADEMIC_HAT_SIZE in VARCHAR2,
880 X_GUEST_TICKETS_REQUESTED in NUMBER,
881 X_GUEST_TICKETS_ALLOCATED in NUMBER,
882 X_GUEST_SEATS in VARCHAR2,
883 X_FEES_PAID_IND in VARCHAR2,
884 X_SPECIAL_REQUIREMENTS in VARCHAR2,
885 X_COMMENTS in VARCHAR2,
886 X_PERSON_ID in NUMBER,
887 X_CREATE_DT in DATE,
888 X_GRD_CAL_TYPE in VARCHAR2,
889 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
890 X_HIST_WHO in NUMBER,
891 X_US_GROUP_NUMBER in NUMBER,
892 X_ORDER_IN_PRESENTATION in NUMBER,
893 X_HIST_END_DT in DATE,
894 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
895 X_CEREMONY_NUMBER in NUMBER,
896 X_AWARD_COURSE_CD in VARCHAR2,
897 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
898 X_AWARD_CD in VARCHAR2,
899 X_HIST_START_DT in DATE,
900 X_MODE in VARCHAR2 default 'R'
901 ) AS
902 X_LAST_UPDATE_DATE DATE;
903 X_LAST_UPDATED_BY NUMBER;
904 X_LAST_UPDATE_LOGIN NUMBER;
905 begin
906 X_LAST_UPDATE_DATE := SYSDATE;
907 if(X_MODE = 'I') then
908 X_LAST_UPDATED_BY := 1;
909 X_LAST_UPDATE_LOGIN := 0;
910 elsif (X_MODE = 'R') then
911 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
912 if X_LAST_UPDATED_BY is NULL then
913 X_LAST_UPDATED_BY := -1;
914 end if;
915 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
916 if X_LAST_UPDATE_LOGIN is NULL then
917 X_LAST_UPDATE_LOGIN := -1;
918 end if;
919 else
920 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
921 IGS_GE_MSG_STACK.ADD;
922 app_exception.raise_exception;
923 end if;
924
925 Before_DML (
926 p_action => 'UPDATE',
927 x_rowid => X_ROWID,
928 x_GACH_ID => X_GACH_ID,
929 x_person_id => X_PERSON_ID,
930 x_create_dt => X_CREATE_DT,
931 x_grd_cal_type => X_GRD_CAL_TYPE,
932 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
933 x_ceremony_number => X_CEREMONY_NUMBER,
934 x_award_course_cd => X_AWARD_COURSE_CD,
935 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
936 x_award_cd => X_AWARD_CD,
937 x_hist_start_dt => X_HIST_START_DT,
938 x_hist_end_dt => X_HIST_END_DT,
939 x_hist_who => X_HIST_WHO,
940 x_us_group_number => X_US_GROUP_NUMBER,
941 x_order_in_presentation => X_ORDER_IN_PRESENTATION,
942 x_graduand_seat_number => X_GRADUAND_SEAT_NUMBER,
943 x_name_pronunciation => X_NAME_PRONUNCIATION,
944 x_name_announced => X_NAME_ANNOUNCED,
945 x_academic_dress_rqrd_ind => X_ACADEMIC_DRESS_RQRD_IND,
946 x_academic_gown_size => X_ACADEMIC_GOWN_SIZE,
947 x_academic_hat_size => X_ACADEMIC_HAT_SIZE,
948 x_guest_tickets_requested => X_GUEST_TICKETS_REQUESTED,
949 x_guest_tickets_allocated => X_GUEST_TICKETS_ALLOCATED,
950 x_guest_seats => X_GUEST_SEATS,
951 x_fees_paid_ind => X_FEES_PAID_IND,
952 x_special_requirements => X_SPECIAL_REQUIREMENTS,
953 x_comments => X_COMMENTS,
954 x_creation_date => X_LAST_UPDATE_DATE,
955 x_created_by => X_LAST_UPDATED_BY,
956 x_last_update_date => X_LAST_UPDATE_DATE,
957 x_last_updated_by => X_LAST_UPDATED_BY,
958 x_last_update_login => X_LAST_UPDATE_LOGIN
959 );
960
961 update IGS_GR_AWD_CRMN_HIST set
962 NAME_PRONUNCIATION = NEW_REFERENCES.NAME_PRONUNCIATION,
963 NAME_ANNOUNCED = NEW_REFERENCES.NAME_ANNOUNCED,
964 ACADEMIC_DRESS_RQRD_IND = NEW_REFERENCES.ACADEMIC_DRESS_RQRD_IND,
965 ACADEMIC_GOWN_SIZE = NEW_REFERENCES.ACADEMIC_GOWN_SIZE,
966 ACADEMIC_HAT_SIZE = NEW_REFERENCES.ACADEMIC_HAT_SIZE,
967 GUEST_TICKETS_REQUESTED = NEW_REFERENCES.GUEST_TICKETS_REQUESTED,
968 GUEST_TICKETS_ALLOCATED = NEW_REFERENCES.GUEST_TICKETS_ALLOCATED,
969 GUEST_SEATS = NEW_REFERENCES.GUEST_SEATS,
970 FEES_PAID_IND = NEW_REFERENCES.FEES_PAID_IND,
971 SPECIAL_REQUIREMENTS = NEW_REFERENCES.SPECIAL_REQUIREMENTS,
972 COMMENTS = NEW_REFERENCES.COMMENTS,
973 PERSON_ID = NEW_REFERENCES.PERSON_ID,
974 CREATE_DT = NEW_REFERENCES.CREATE_DT,
975 GRD_CAL_TYPE = NEW_REFERENCES.GRD_CAL_TYPE,
976 GRADUAND_SEAT_NUMBER = NEW_REFERENCES.GRADUAND_SEAT_NUMBER,
977 HIST_WHO = NEW_REFERENCES.HIST_WHO,
978 US_GROUP_NUMBER = NEW_REFERENCES.US_GROUP_NUMBER,
979 ORDER_IN_PRESENTATION = NEW_REFERENCES.ORDER_IN_PRESENTATION,
980 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
981 GRD_CI_SEQUENCE_NUMBER = NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
982 CEREMONY_NUMBER = NEW_REFERENCES.CEREMONY_NUMBER,
983 AWARD_COURSE_CD = NEW_REFERENCES.AWARD_COURSE_CD,
984 AWARD_CRS_VERSION_NUMBER = NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
985 AWARD_CD = NEW_REFERENCES.AWARD_CD,
986 HIST_START_DT = NEW_REFERENCES.HIST_START_DT,
987 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
988 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
989 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
990 where ROWID = X_ROWID
991 ;
992 if (sql%notfound) then
993 raise no_data_found;
994 end if;
995
996 end UPDATE_ROW;
997
998 procedure ADD_ROW (
999 X_ROWID in out NOCOPY VARCHAR2,
1000 X_GACH_ID in out NOCOPY NUMBER,
1001 X_NAME_PRONUNCIATION in VARCHAR2,
1002 X_NAME_ANNOUNCED in VARCHAR2,
1003 X_ACADEMIC_DRESS_RQRD_IND in VARCHAR2,
1004 X_ACADEMIC_GOWN_SIZE in VARCHAR2,
1005 X_ACADEMIC_HAT_SIZE in VARCHAR2,
1006 X_GUEST_TICKETS_REQUESTED in NUMBER,
1007 X_GUEST_TICKETS_ALLOCATED in NUMBER,
1008 X_GUEST_SEATS in VARCHAR2,
1009 X_FEES_PAID_IND in VARCHAR2,
1010 X_SPECIAL_REQUIREMENTS in VARCHAR2,
1011 X_COMMENTS in VARCHAR2,
1012 X_PERSON_ID in NUMBER,
1013 X_CREATE_DT in DATE,
1014 X_GRD_CAL_TYPE in VARCHAR2,
1015 X_GRADUAND_SEAT_NUMBER in VARCHAR2,
1016 X_HIST_WHO in NUMBER,
1017 X_US_GROUP_NUMBER in NUMBER,
1018 X_ORDER_IN_PRESENTATION in NUMBER,
1019 X_HIST_END_DT in DATE,
1020 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1021 X_CEREMONY_NUMBER in NUMBER,
1022 X_AWARD_COURSE_CD in VARCHAR2,
1023 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1024 X_AWARD_CD in VARCHAR2,
1025 X_HIST_START_DT in DATE,
1026 X_MODE in VARCHAR2 default 'R'
1027 ) AS
1028 cursor c1 is select rowid from IGS_GR_AWD_CRMN_HIST
1029 where GACH_ID = X_GACH_ID
1030 ;
1031
1032 begin
1033 open c1;
1034 fetch c1 into X_ROWID;
1035 if (c1%notfound) then
1036 close c1;
1037 INSERT_ROW (
1038 X_ROWID,
1039 X_GACH_ID,
1040 X_NAME_PRONUNCIATION,
1041 X_NAME_ANNOUNCED,
1042 X_ACADEMIC_DRESS_RQRD_IND,
1043 X_ACADEMIC_GOWN_SIZE,
1044 X_ACADEMIC_HAT_SIZE,
1045 X_GUEST_TICKETS_REQUESTED,
1046 X_GUEST_TICKETS_ALLOCATED,
1047 X_GUEST_SEATS,
1048 X_FEES_PAID_IND,
1049 X_SPECIAL_REQUIREMENTS,
1050 X_COMMENTS,
1051 X_PERSON_ID,
1052 X_CREATE_DT,
1053 X_GRD_CAL_TYPE,
1054 X_GRADUAND_SEAT_NUMBER,
1055 X_HIST_WHO,
1056 X_US_GROUP_NUMBER,
1057 X_ORDER_IN_PRESENTATION,
1058 X_HIST_END_DT,
1059 X_GRD_CI_SEQUENCE_NUMBER,
1060 X_CEREMONY_NUMBER,
1061 X_AWARD_COURSE_CD,
1062 X_AWARD_CRS_VERSION_NUMBER,
1063 X_AWARD_CD,
1064 X_HIST_START_DT,
1065 X_MODE);
1066 return;
1067 end if;
1068 close c1;
1069 UPDATE_ROW (
1070 X_ROWID,
1071 X_GACH_ID,
1072 X_NAME_PRONUNCIATION,
1073 X_NAME_ANNOUNCED,
1074 X_ACADEMIC_DRESS_RQRD_IND,
1075 X_ACADEMIC_GOWN_SIZE,
1076 X_ACADEMIC_HAT_SIZE,
1077 X_GUEST_TICKETS_REQUESTED,
1078 X_GUEST_TICKETS_ALLOCATED,
1079 X_GUEST_SEATS,
1080 X_FEES_PAID_IND,
1081 X_SPECIAL_REQUIREMENTS,
1082 X_COMMENTS,
1083 X_PERSON_ID,
1084 X_CREATE_DT,
1085 X_GRD_CAL_TYPE,
1086 X_GRADUAND_SEAT_NUMBER,
1087 X_HIST_WHO,
1088 X_US_GROUP_NUMBER,
1089 X_ORDER_IN_PRESENTATION,
1090 X_HIST_END_DT,
1091 X_GRD_CI_SEQUENCE_NUMBER,
1092 X_CEREMONY_NUMBER,
1093 X_AWARD_COURSE_CD,
1094 X_AWARD_CRS_VERSION_NUMBER,
1095 X_AWARD_CD,
1096 X_HIST_START_DT,
1097 X_MODE);
1098 end ADD_ROW;
1099
1100 procedure DELETE_ROW (
1101 X_ROWID in VARCHAR2
1102 ) AS
1103 begin
1104
1105 delete from IGS_GR_AWD_CRMN_HIST
1106 where ROWID = X_ROWID;
1107 if (sql%notfound) then
1108 raise no_data_found;
1109 end if;
1110
1111 end DELETE_ROW;
1112
1113 end IGS_GR_AWD_CRMN_HIST_PKG;