[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_STD_EXM_INSTN_PKG
Source
1 package body IGS_AS_STD_EXM_INSTN_PKG AS
2 /* $Header: IGSDI09B.pls 120.0 2005/07/05 12:08:58 appldev noship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_AS_STD_EXM_INSTN_ALL%RowType;
7 new_references IGS_AS_STD_EXM_INSTN_ALL%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_org_id IN NUMBER DEFAULT NULL,
13 x_seat_number IN NUMBER DEFAULT NULL,
14 x_timeslot IN DATE DEFAULT NULL,
15 x_timeslot_duration IN DATE DEFAULT NULL,
16 x_ass_id IN NUMBER DEFAULT NULL,
17 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
18 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
19 x_dt_alias IN VARCHAR2 DEFAULT NULL,
20 x_dai_sequence_number IN NUMBER DEFAULT NULL,
21 x_start_time IN DATE DEFAULT NULL,
22 x_end_time IN DATE DEFAULT NULL,
23 x_ese_id IN NUMBER DEFAULT NULL,
24 x_venue_cd IN VARCHAR2 DEFAULT NULL,
25 x_person_id IN NUMBER DEFAULT NULL,
26 x_course_cd IN VARCHAR2 DEFAULT NULL,
27 x_unit_cd IN VARCHAR2 DEFAULT NULL,
28 x_cal_type IN VARCHAR2 DEFAULT NULL,
29 x_ci_sequence_number IN NUMBER DEFAULT NULL,
30 x_creation_dt IN DATE DEFAULT NULL,
31 x_attendance_ind IN VARCHAR2 DEFAULT NULL,
32 x_creation_date IN DATE DEFAULT NULL,
33 x_created_by IN NUMBER DEFAULT NULL,
34 x_last_update_date IN DATE DEFAULT NULL,
35 x_last_updated_by IN NUMBER DEFAULT NULL,
36 x_last_update_login IN NUMBER DEFAULT NULL,
37 x_uoo_id IN NUMBER DEFAULT NULL,
38 x_std_exm_instn_id IN NUMBER DEFAULT NULL
39 ) AS
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_AS_STD_EXM_INSTN_ALL
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 Open cur_old_ref_values;
53 Fetch cur_old_ref_values INTO old_references;
54 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 Close cur_old_ref_values;
58 APP_EXCEPTION.RAISE_EXCEPTION;
59
60 Return;
61 END IF;
62 Close cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.org_id := x_org_id;
66 new_references.seat_number := x_seat_number;
67 new_references.timeslot := x_timeslot;
68 new_references.timeslot_duration := x_timeslot_duration;
69 new_references.ass_id := x_ass_id;
70 new_references.exam_cal_type := x_exam_cal_type;
71 new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
72 new_references.dt_alias:= x_dt_alias;
73 new_references.dai_sequence_number := x_dai_sequence_number;
74 new_references.start_time := x_start_time;
75 new_references.end_time := x_end_time;
76 new_references.ese_id := x_ese_id;
77 new_references.venue_cd := x_venue_cd;
78 new_references.person_id := x_person_id;
79 new_references.course_cd := x_course_cd;
80 new_references.unit_cd := x_unit_cd;
81 new_references.cal_type := x_cal_type;
82 new_references.ci_sequence_number := x_ci_sequence_number;
83 new_references.creation_dt := x_creation_dt;
84 new_references.attendance_ind := x_attendance_ind;
85 new_references.uoo_id := x_uoo_id;
86 new_references.std_exm_instn_id := x_std_exm_instn_id;
87 IF (p_action = 'UPDATE') THEN
88 new_references.creation_date := old_references.creation_date;
89 new_references.created_by := old_references.created_by;
90 ELSE
91 new_references.creation_date := x_creation_date;
92 new_references.created_by := x_created_by;
93 END IF;
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END Set_Column_Values;
99
100 -- Trigger description :-
101 -- "OSS_TST".trg_sei_br_i
102 -- BEFORE INSERT
103 -- ON IGS_AS_STD_EXM_INSTN
104 -- FOR EACH ROW
105
106 PROCEDURE BeforeRowInsert1(
107 p_inserting IN BOOLEAN DEFAULT FALSE,
108 p_updating IN BOOLEAN DEFAULT FALSE,
109 p_deleting IN BOOLEAN DEFAULT FALSE
110 ) AS
111
112 v_message_name VARCHAR2(30);
113 BEGIN
114 -- Call routine to fill in exam session key.
115 IGS_AS_GEN_006.ASSP_GET_ESE_KEY(
116 new_references.exam_cal_type,
117 new_references.exam_ci_sequence_number,
118 new_references.dt_alias,
119 new_references.dai_sequence_number,
120 new_references.start_time,
121 new_references.end_time,
122 new_references.ese_id);
123 -- Validate the teaching calendar instance against the examination calendar
124 -- instance.
125 IF IGS_AS_VAL_SEI.assp_val_sei_ci( new_references.cal_type,
126 new_references.ci_sequence_number,
127 new_references.exam_cal_type,
128 new_references.exam_ci_sequence_number,
129 v_message_name) = FALSE THEN
130 FND_MESSAGE.SET_NAME('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END IF;
134
135
136 END BeforeRowInsert1;
137
138 -- Trigger description :-
139 -- "OSS_TST".trg_sei_ar_i
140 -- AFTER INSERT
141 -- ON IGS_AS_STD_EXM_INSTN
142 -- FOR EACH ROW
143
144 PROCEDURE AfterRowInsert2(
145 p_inserting IN BOOLEAN DEFAULT FALSE,
146 p_updating IN BOOLEAN DEFAULT FALSE,
147 p_deleting IN BOOLEAN DEFAULT FALSE
148 ) AS
149 v_message_name VARCHAR2(30);
150 v_rowid_saved BOOLEAN := FALSE;
151 BEGIN
152 IF v_rowid_saved = FALSE
153 THEN
154 IF IGS_AS_VAL_SEI.assp_val_sei_dplct (
155 new_references.person_id,
156 new_references.course_cd,
157 new_references.unit_cd,
158 new_references.cal_type,
159 new_references.ci_sequence_number,
160 new_references.exam_cal_type,
161 new_references.exam_ci_sequence_number,
162 new_references.dt_alias,
163 new_references.dai_sequence_number,
164 new_references.start_time,
165 new_references.end_time,
166 new_references.ass_id,
167 new_references.venue_cd,
168 v_message_name,
169 new_references.uoo_id) = FALSE THEN
170 FND_MESSAGE.SET_NAME('IGS',v_message_name);
171 IGS_GE_MSG_STACK.ADD;
172 END IF;
173 v_rowid_saved := TRUE;
174 END IF;
175
176
177 END AfterRowInsert2;
178
179 -- Trigger description :-
180 -- "OSS_TST".trg_sei_as_i
181 -- AFTER INSERT
182 -- ON IGS_AS_STD_EXM_INSTN
183 PROCEDURE Check_Uniqueness AS
184
185 BEGIN
186 IF Get_Uk_For_Validation(
187 x_ass_id =>new_references.ass_id,
188 x_exam_cal_type =>new_references.exam_cal_type,
189 x_exam_ci_sequence_number => new_references.exam_ci_sequence_number,
190 x_dt_alias => new_references.dt_alias,
191 x_dai_sequence_number =>new_references.dai_sequence_number,
192 x_start_time =>new_references.start_time,
193 x_end_time =>new_references.end_time,
194 x_venue_cd =>new_references.venue_cd,
195 x_person_id =>new_references.person_id,
196 x_course_cd =>new_references.course_cd,
197 x_creation_dt =>new_references.creation_dt ,
198 x_uoo_id =>new_references.uoo_id) THEN
199
200 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
201 IGS_GE_MSG_STACK.ADD;
202 APP_EXCEPTION.RAISE_EXCEPTION;
203 END IF;
204
205 END Check_Uniqueness;
206
207 PROCEDURE Check_Parent_Existance AS
208 BEGIN
209
210 IF (((old_references.ass_id = new_references.ass_id) AND
211 (old_references.exam_cal_type = new_references.exam_cal_type) AND
212 (old_references.exam_ci_sequence_number = new_references.exam_ci_sequence_number) AND
213 (old_references.dt_alias= new_references.dt_alias) AND
214 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
215 (old_references.start_time = new_references.start_time) AND
216 (old_references.end_time = new_references.end_time) AND
217 (old_references.venue_cd = new_references.venue_cd)) OR
218 ((new_references.ass_id IS NULL) OR
219 (new_references.exam_cal_type IS NULL) OR
220 (new_references.exam_ci_sequence_number IS NULL) OR
221 (new_references.dt_alias IS NULL) OR
222 (new_references.dai_sequence_number IS NULL) OR
223 (new_references.start_time IS NULL) OR
224 (new_references.end_time IS NULL) OR
225 (new_references.venue_cd IS NULL))) THEN
226 NULL;
227 ELSIF NOT IGS_AS_EXAM_INSTANCE_PKG.Get_PK_For_Validation (
228 new_references.ass_id,
229 new_references.exam_cal_type,
230 new_references.exam_ci_sequence_number,
231 new_references.dt_alias,
232 new_references.dai_sequence_number,
233 new_references.start_time,
234 new_references.end_time,
235 new_references.venue_cd
236 ) THEN
237 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
238 IGS_GE_MSG_STACK.ADD;
239 APP_EXCEPTION.RAISE_EXCEPTION;
240
241 END IF;
242
243 IF (((old_references.ese_id = new_references.ese_id)) OR
244 ((new_references.ese_id IS NULL))) THEN
245 NULL;
246 ELSIF NOT IGS_AS_EXAM_SESSION_PKG.Get_UK_For_Validation (
247 new_references.ese_id
248 ) THEN
249 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
250 IGS_GE_MSG_STACK.ADD;
251 APP_EXCEPTION.RAISE_EXCEPTION;
252
253 END IF;
254
255 IF (((old_references.course_cd = new_references.course_cd) OR
256 (old_references.person_id = new_references.person_id) OR
257 (old_references.ass_id = new_references.ass_id) OR
258 (old_references.creation_dt = new_references.creation_dt) OR
259 (old_references.uoo_id = new_references.uoo_id)) OR
260 ((new_references.course_cd IS NULL) OR
261 (new_references.person_id IS NULL) OR
262 (new_references.ass_id IS NULL) OR
263 (new_references.creation_dt IS NULL) OR
264 (new_references.uoo_id IS NULL))) THEN
265 NULL;
266 ELSIF NOT IGS_AS_SU_ATMPT_ITM_PKG.Get_PK_For_Validation (
267 new_references.course_cd,
268 new_references.person_id,
269 new_references.ass_id,
270 new_references.creation_dt,
271 new_references.uoo_id
272 ) THEN
273 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
274 IGS_GE_MSG_STACK.ADD;
275 APP_EXCEPTION.RAISE_EXCEPTION;
276
277 END IF;
278
279 END Check_Parent_Existance;
280
281 -------------------------------------------------------------------------------------------
282 --Change History:
283 --Who When What
284 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
285 -------------------------------------------------------------------------------------------
286 FUNCTION Get_PK_For_Validation (
287 x_std_exm_instn_id in NUMBER
288 ) RETURN BOOLEAN AS
289
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM IGS_AS_STD_EXM_INSTN_ALL
293 WHERE std_exm_instn_id = x_std_exm_instn_id
294 FOR UPDATE NOWAIT;
295
296 lv_rowid cur_rowid%RowType;
297
298 BEGIN
299
300 Open cur_rowid;
301 Fetch cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 Close cur_rowid;
304 Return (TRUE);
305 ELSE
306 Close cur_rowid;
307 Return (FALSE);
308 END IF;
309
310 END Get_PK_For_Validation;
311
312 FUNCTION Get_Uk_For_Validation (
313 x_ass_id IN NUMBER,
314 x_exam_cal_type IN VARCHAR2,
315 x_exam_ci_sequence_number IN NUMBER,
316 x_dt_alias IN VARCHAR2,
317 x_dai_sequence_number IN NUMBER,
318 x_start_time IN DATE,
319 x_end_time IN DATE,
320 x_venue_cd IN VARCHAR2,
321 x_person_id IN NUMBER,
322 x_course_cd IN VARCHAR2,
323 x_creation_dt IN DATE,
324 x_uoo_id in NUMBER
325 ) RETURN BOOLEAN AS
326 CURSOR cur_sua IS
327 SELECT ROWID
328 FROM IGS_AS_STD_EXM_INSTN_ALL
329 WHERE ass_id = x_ass_id
330 AND exam_cal_type = x_exam_cal_type
331 AND exam_ci_sequence_number = x_exam_ci_sequence_number
332 AND start_time = x_start_time
333 AND end_time = x_end_time
334 AND venue_cd =x_venue_cd
335 AND person_id =x_person_id
336 AND course_cd = x_course_cd
337 AND creation_dt =x_creation_dt
338 AND uoo_id =x_uoo_id
339 AND dt_alias = x_dt_alias
340 AND dai_sequence_number = x_dai_sequence_number
341 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
342 lv_row_id cur_sua%ROWTYPE;
343 BEGIN
344
345 OPEN cur_sua;
346 FETCH cur_sua INTO lv_row_id;
347 IF cur_sua%FOUND THEN
348 CLOSE cur_sua;
349 RETURN(TRUE);
350 ELSE
351 CLOSE cur_sua;
352 RETURN(FALSE);
353 END IF;
354
355 END Get_Uk_For_Validation;
356
357 PROCEDURE GET_FK_IGS_AS_EXAM_INSTANCE (
358 x_ass_id IN NUMBER,
359 x_exam_cal_type IN VARCHAR2,
360 x_exam_ci_sequence_number IN NUMBER,
361 x_dt_alias IN VARCHAR2,
362 x_dai_sequence_number IN NUMBER,
363 x_start_time IN DATE,
364 x_end_time IN DATE,
365 x_venue_cd IN VARCHAR2
366 ) AS
367
368 CURSOR cur_rowid IS
369 SELECT rowid
370 FROM IGS_AS_STD_EXM_INSTN_ALL
371 WHERE ass_id = x_ass_id
372 AND exam_cal_type = x_exam_cal_type
373 AND exam_ci_sequence_number = x_exam_ci_sequence_number
374 AND dt_alias = x_dt_alias
375 AND dai_sequence_number = x_dai_sequence_number
376 AND start_time = x_start_time
377 AND end_time = x_end_time
378 AND venue_cd = x_venue_cd ;
379
380 lv_rowid cur_rowid%RowType;
381
382 BEGIN
383
384 Open cur_rowid;
385 Fetch cur_rowid INTO lv_rowid;
386 IF (cur_rowid%FOUND) THEN
387 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SEI_EI_FK');
388 IGS_GE_MSG_STACK.ADD;
389 Close cur_rowid;
390 APP_EXCEPTION.RAISE_EXCEPTION;
391
392 Return;
393 END IF;
394 Close cur_rowid;
395
396 END GET_FK_IGS_AS_EXAM_INSTANCE;
397
398 PROCEDURE GET_UFK_IGS_AS_EXAM_SESSION (
399 x_ese_id IN NUMBER
400 ) AS
401
402 CURSOR cur_rowid IS
403 SELECT rowid
404 FROM IGS_AS_STD_EXM_INSTN_ALL
405 WHERE ese_id = x_ese_id ;
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 Open cur_rowid;
412 Fetch cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SEI_ESE_UFK');
415 IGS_GE_MSG_STACK.ADD;
416 Close cur_rowid;
417 APP_EXCEPTION.RAISE_EXCEPTION;
418
419 Return;
420 END IF;
421 Close cur_rowid;
422
423 END GET_UFK_IGS_AS_EXAM_SESSION;
424
425 PROCEDURE GET_FK_IGS_AS_SU_ATMPT_ITM (
426 x_course_cd IN VARCHAR2,
427 x_person_id IN NUMBER,
428 x_ass_id IN NUMBER,
429 x_creation_dt IN DATE,
430 x_uoo_id IN NUMBER
431 ) AS
432
433 CURSOR cur_rowid IS
434 SELECT rowid
435 FROM IGS_AS_STD_EXM_INSTN_ALL
436 WHERE person_id = x_person_id
437 AND course_cd = x_course_cd
438 AND ass_id = x_ass_id
439 AND creation_dt = x_creation_dt
440 AND uoo_id = x_uoo_id;
441
442 lv_rowid cur_rowid%RowType;
443
444 BEGIN
445
446 Open cur_rowid;
447 Fetch cur_rowid INTO lv_rowid;
448 IF (cur_rowid%FOUND) THEN
449 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SEI_SUAAI_FK');
450 IGS_GE_MSG_STACK.ADD;
451 Close cur_rowid;
452 APP_EXCEPTION.RAISE_EXCEPTION;
453
454 Return;
455 END IF;
456 Close cur_rowid;
457
458 END GET_FK_IGS_AS_SU_ATMPT_ITM;
459
460 PROCEDURE Before_DML (
461 p_action IN VARCHAR2,
462 x_rowid IN VARCHAR2 DEFAULT NULL,
463 x_org_id IN NUMBER DEFAULT NULL,
464 x_seat_number IN NUMBER DEFAULT NULL,
465 x_timeslot IN DATE DEFAULT NULL,
466 x_timeslot_duration IN DATE DEFAULT NULL,
467 x_ass_id IN NUMBER DEFAULT NULL,
468 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
469 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
470 x_dt_alias IN VARCHAR2 DEFAULT NULL,
471 x_dai_sequence_number IN NUMBER DEFAULT NULL,
472 x_start_time IN DATE DEFAULT NULL,
473 x_end_time IN DATE DEFAULT NULL,
474 x_ese_id IN NUMBER DEFAULT NULL,
475 x_venue_cd IN VARCHAR2 DEFAULT NULL,
476 x_person_id IN NUMBER DEFAULT NULL,
477 x_course_cd IN VARCHAR2 DEFAULT NULL,
478 x_unit_cd IN VARCHAR2 DEFAULT NULL,
479 x_cal_type IN VARCHAR2 DEFAULT NULL,
480 x_ci_sequence_number IN NUMBER DEFAULT NULL,
481 x_creation_dt IN DATE DEFAULT NULL,
482 x_attendance_ind IN VARCHAR2 DEFAULT NULL,
483 x_creation_date IN DATE DEFAULT NULL,
484 x_created_by IN NUMBER DEFAULT NULL,
485 x_last_update_date IN DATE DEFAULT NULL,
486 x_last_updated_by IN NUMBER DEFAULT NULL,
487 x_last_update_login IN NUMBER DEFAULT NULL,
488 x_uoo_id in NUMBER DEFAULT NULL,
489 x_std_exm_instn_id in NUMBER DEFAULT NULL
490 ) AS
491 BEGIN
492
493 Set_Column_Values (
494 p_action,
495 x_rowid,
496 x_org_id,
497 x_seat_number,
498 x_timeslot,
499 x_timeslot_duration,
500 x_ass_id,
501 x_exam_cal_type,
502 x_exam_ci_sequence_number,
503 x_dt_alias,
504 x_dai_sequence_number,
505 x_start_time,
506 x_end_time,
507 x_ese_id,
508 x_venue_cd,
509 x_person_id,
510 x_course_cd,
511 x_unit_cd,
512 x_cal_type,
513 x_ci_sequence_number,
514 x_creation_dt,
515 x_attendance_ind,
516 x_creation_date,
517 x_created_by,
518 x_last_update_date,
519 x_last_updated_by,
520 x_last_update_login,
521 x_uoo_id,
522 x_std_exm_instn_id
523 );
524
525 IF (p_action = 'INSERT') THEN
526 -- Call all the procedures related to Before Insert.
527 BeforeRowInsert1 ( p_inserting => TRUE );
528 IF Get_PK_For_Validation (
529 NEW_REFERENCES.std_exm_instn_id
530 ) THEN
531 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
532 IGS_GE_MSG_STACK.ADD;
533 APP_EXCEPTION.RAISE_EXCEPTION;
534 END IF;
535
536 Check_Constraints;
537
538 Check_Parent_Existance;
539 Check_Uniqueness;
540 ELSIF (p_action = 'UPDATE') THEN
541 -- Call all the procedures related to Before Update.
542
543 Check_Constraints;
544
545 Check_Parent_Existance;
546 Check_Uniqueness;
547
548 ELSIF (p_action = 'VALIDATE_INSERT') THEN
549 IF Get_PK_For_Validation (
550 NEW_REFERENCES.std_exm_instn_id) THEN
551 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
552 IGS_GE_MSG_STACK.ADD;
553 APP_EXCEPTION.RAISE_EXCEPTION;
554 END IF;
555
556 Check_Constraints;
557 Check_Uniqueness;
558 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
559
560 Check_Constraints;
561 Check_Uniqueness;
562
563
564 END IF;
565
566 END Before_DML;
567
568 PROCEDURE After_DML (
569 p_action IN VARCHAR2,
570 x_rowid IN VARCHAR2
571 ) AS
572 BEGIN
573
574 l_rowid := x_rowid;
575
576 IF (p_action = 'INSERT') THEN
577 -- Call all the procedures related to After Insert.
578 AfterRowInsert2 ( p_inserting => TRUE );
579
580
581
582 END IF;
583
584 END After_DML;
585
586 procedure INSERT_ROW (
587 X_ROWID in out NOCOPY VARCHAR2,
588 X_ORG_ID in NUMBER,
589 X_ASS_ID in NUMBER,
590 X_EXAM_CAL_TYPE in VARCHAR2,
591 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
592 X_DT_ALIAS in VARCHAR2,
593 X_DAI_SEQUENCE_NUMBER in NUMBER,
594 X_START_TIME in DATE,
595 X_END_TIME in DATE,
596 X_VENUE_CD in VARCHAR2,
597 X_PERSON_ID in NUMBER,
598 X_COURSE_CD in VARCHAR2,
599 X_UNIT_CD in VARCHAR2,
600 X_CAL_TYPE in VARCHAR2,
601 X_CI_SEQUENCE_NUMBER in NUMBER,
602 X_CREATION_DT in DATE,
603 X_SEAT_NUMBER in NUMBER,
604 X_TIMESLOT in DATE,
605 X_TIMESLOT_DURATION in DATE,
606 X_ESE_ID in NUMBER,
607 X_ATTENDANCE_IND in VARCHAR2,
608 X_MODE in VARCHAR2 default 'R',
609 X_UOO_ID in NUMBER,
610 X_STD_EXM_INSTN_ID in out NOCOPY NUMBER
611 ) AS
612 /*---------------------------------------------------------------------------------------
613 --Change History
614 --Who when What
615 --sbaliga 12-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
616 -- in call to before_dml as part of SWCR006 build.
617 --svanukur 29-apr-03 changed the where clause to reflect the new PK
618 -- as part of MUS build, # 2829262
619 ---------------------------------------------------------------------------------------*/
620 cursor C is select ROWID from IGS_AS_STD_EXM_INSTN_ALL
621 where STD_EXM_INSTN_ID = X_STD_EXM_INSTN_ID;
622 X_LAST_UPDATE_DATE DATE;
623 X_LAST_UPDATED_BY NUMBER;
624 X_LAST_UPDATE_LOGIN NUMBER;
625 begin
626 X_LAST_UPDATE_DATE := SYSDATE;
627 if(X_MODE = 'I') then
628 X_LAST_UPDATED_BY := 1;
629 X_LAST_UPDATE_LOGIN := 0;
630 elsif (X_MODE IN ('R', 'S')) then
631 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
632 if X_LAST_UPDATED_BY is NULL then
633 X_LAST_UPDATED_BY := -1;
634 end if;
635 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
636 if X_LAST_UPDATE_LOGIN is NULL then
637 X_LAST_UPDATE_LOGIN := -1;
638 end if;
639 else
640 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
641 IGS_GE_MSG_STACK.ADD;
642 APP_EXCEPTION.RAISE_EXCEPTION;
643 end if;
644
645 SELECT IGS_AS_STD_EXM_INSTN_ALL_S.nextval
646 INTO X_STD_EXM_INSTN_ID
647 FROM DUAL;
648
649 Before_DML(
650 p_action=>'INSERT',
651 x_rowid=>X_ROWID,
652 x_org_id=>igs_ge_gen_003.get_org_id,
653 x_ass_id=>X_ASS_ID,
654 x_attendance_ind=>X_ATTENDANCE_IND,
655 x_cal_type=>X_CAL_TYPE,
656 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
657 x_course_cd=>X_COURSE_CD,
658 x_creation_dt=>X_CREATION_DT,
659 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
660 x_dt_alias=>X_DT_ALIAS,
661 x_end_time=>X_END_TIME,
662 x_ese_id=>X_ESE_ID,
663 x_exam_cal_type=>X_EXAM_CAL_TYPE,
664 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
665 x_person_id=>X_PERSON_ID,
666 x_seat_number=>X_SEAT_NUMBER,
667 x_start_time=>X_START_TIME,
668 x_timeslot=>X_TIMESLOT,
669 x_timeslot_duration=>X_TIMESLOT_DURATION,
670 x_unit_cd=>X_UNIT_CD,
671 x_venue_cd=>X_VENUE_CD,
672 x_creation_date=>X_LAST_UPDATE_DATE,
673 x_created_by=>X_LAST_UPDATED_BY,
674 x_last_update_date=>X_LAST_UPDATE_DATE,
675 x_last_updated_by=>X_LAST_UPDATED_BY,
676 x_last_update_login=>X_LAST_UPDATE_LOGIN,
677 x_uoo_id => X_UOO_ID,
678 x_std_exm_instn_id =>X_STD_EXM_INSTN_ID
679 );
680
681 IF (x_mode = 'S') THEN
682 igs_sc_gen_001.set_ctx('R');
683 END IF;
684 insert into IGS_AS_STD_EXM_INSTN_ALL (
685 ORG_ID,
686 SEAT_NUMBER,
687
688 TIMESLOT,
689 TIMESLOT_DURATION,
690 ASS_ID,
691 EXAM_CAL_TYPE,
692 EXAM_CI_SEQUENCE_NUMBER,
693 DT_ALIAS,
694 DAI_SEQUENCE_NUMBER,
695 START_TIME,
696 END_TIME,
697 ESE_ID,
698 VENUE_CD,
699 PERSON_ID,
700 COURSE_CD,
701 UNIT_CD,
702 CAL_TYPE,
703 CI_SEQUENCE_NUMBER,
704 CREATION_DT,
705 ATTENDANCE_IND,
706 CREATION_DATE,
707 CREATED_BY,
708 LAST_UPDATE_DATE,
709 LAST_UPDATED_BY,
710 LAST_UPDATE_LOGIN,
711 UOO_ID,
712 STD_EXM_INSTN_ID
713 ) values (
714 new_references.ORG_ID,
715 new_references.SEAT_NUMBER,
716 new_references.TIMESLOT,
717 new_references.TIMESLOT_DURATION,
718 new_references.ASS_ID,
719 new_references.EXAM_CAL_TYPE,
720 new_references.EXAM_CI_SEQUENCE_NUMBER,
721 new_references.DT_ALIAS,
722 new_references.DAI_SEQUENCE_NUMBER,
723 new_references.START_TIME,
724 new_references.END_TIME,
725 new_references.ESE_ID,
726 new_references.VENUE_CD,
727 new_references.PERSON_ID,
728 new_references.COURSE_CD,
729 new_references.UNIT_CD,
730 new_references.CAL_TYPE,
731 new_references.CI_SEQUENCE_NUMBER,
732 new_references.CREATION_DT,
733 new_references.ATTENDANCE_IND,
734 X_LAST_UPDATE_DATE,
735 X_LAST_UPDATED_BY,
736 X_LAST_UPDATE_DATE,
737 X_LAST_UPDATED_BY,
738 X_LAST_UPDATE_LOGIN,
739 new_references.uoo_id,
740 new_references.STD_EXM_INSTN_ID
741 );
742 IF (x_mode = 'S') THEN
743 igs_sc_gen_001.unset_ctx('R');
744 END IF;
745
746
747 open c;
748 fetch c into X_ROWID;
749 if (c%notfound) then
750 close c;
751 raise no_data_found;
752 end if;
753 close c;
754 After_DML(
755 p_action => 'INSERT',
756 x_rowid => X_ROWID
757 );
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
762 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
763 fnd_message.set_token ('ERR_CD', SQLCODE);
764 igs_ge_msg_stack.add;
765 igs_sc_gen_001.unset_ctx('R');
766 app_exception.raise_exception;
767 ELSE
768 igs_sc_gen_001.unset_ctx('R');
769 RAISE;
770 END IF;
771
772 end INSERT_ROW;
773
774 procedure LOCK_ROW (
775 X_ROWID in VARCHAR2,
776 X_ASS_ID in NUMBER,
777 X_EXAM_CAL_TYPE in VARCHAR2,
778 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
779 X_DT_ALIAS in VARCHAR2,
780 X_DAI_SEQUENCE_NUMBER in NUMBER,
781 X_START_TIME in DATE,
782 X_END_TIME in DATE,
783 X_VENUE_CD in VARCHAR2,
784 X_PERSON_ID in NUMBER,
785 X_COURSE_CD in VARCHAR2,
786 X_UNIT_CD in VARCHAR2,
787 X_CAL_TYPE in VARCHAR2,
788 X_CI_SEQUENCE_NUMBER in NUMBER,
789 X_CREATION_DT in DATE,
790 X_SEAT_NUMBER in NUMBER,
791 X_TIMESLOT in DATE,
792 X_TIMESLOT_DURATION in DATE,
793 X_ESE_ID in NUMBER,
794 X_ATTENDANCE_IND in VARCHAR2,
795 X_UOO_ID in NUMBER,
796 X_STD_EXM_INSTN_ID in NUMBER
797 ) AS
798 cursor c1 is select
799 SEAT_NUMBER,
800 TIMESLOT,
801 TIMESLOT_DURATION,
802 ESE_ID,
803 ATTENDANCE_IND
804 from IGS_AS_STD_EXM_INSTN_ALL
805 where ROWID = X_ROWID for update nowait;
806 tlinfo c1%rowtype;
807
808 begin
809 open c1;
810 fetch c1 into tlinfo;
811 if (c1%notfound) then
812 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
813 IGS_GE_MSG_STACK.ADD;
814 APP_EXCEPTION.RAISE_EXCEPTION;
815 close c1;
816 return;
817 end if;
818 close c1;
819
820 if ( ((tlinfo.SEAT_NUMBER = X_SEAT_NUMBER)
821 OR ((tlinfo.SEAT_NUMBER is null)
822 AND (X_SEAT_NUMBER is null)))
823
824 AND ((tlinfo.TIMESLOT = X_TIMESLOT)
825 OR ((tlinfo.TIMESLOT is null)
826 AND (X_TIMESLOT is null)))
827 AND ((tlinfo.TIMESLOT_DURATION = X_TIMESLOT_DURATION)
828 OR ((tlinfo.TIMESLOT_DURATION is null)
829 AND (X_TIMESLOT_DURATION is null)))
830 AND (tlinfo.ESE_ID = X_ESE_ID)
831 AND (tlinfo.ATTENDANCE_IND = X_ATTENDANCE_IND)
832 ) then
833 null;
834 else
835 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
836 IGS_GE_MSG_STACK.ADD;
837 APP_EXCEPTION.RAISE_EXCEPTION;
838 end if;
839 return;
840 end LOCK_ROW;
841
842 procedure UPDATE_ROW (
843 X_ROWID in VARCHAR2,
844 X_ASS_ID in NUMBER,
845 X_EXAM_CAL_TYPE in VARCHAR2,
846 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
847 X_DT_ALIAS in VARCHAR2,
848 X_DAI_SEQUENCE_NUMBER in NUMBER,
849 X_START_TIME in DATE,
850 X_END_TIME in DATE,
851 X_VENUE_CD in VARCHAR2,
852 X_PERSON_ID in NUMBER,
853 X_COURSE_CD in VARCHAR2,
854 X_UNIT_CD in VARCHAR2,
855 X_CAL_TYPE in VARCHAR2,
856 X_CI_SEQUENCE_NUMBER in NUMBER,
857 X_CREATION_DT in DATE,
858 X_SEAT_NUMBER in NUMBER,
859 X_TIMESLOT in DATE,
860 X_TIMESLOT_DURATION in DATE,
861 X_ESE_ID in NUMBER,
862 X_ATTENDANCE_IND in VARCHAR2,
863 X_MODE in VARCHAR2 default 'R',
864 X_UOO_ID in NUMBER,
865 X_STD_EXM_INSTN_ID in NUMBER
866 ) AS
867 X_LAST_UPDATE_DATE DATE;
868 X_LAST_UPDATED_BY NUMBER;
869 X_LAST_UPDATE_LOGIN NUMBER;
870 begin
871 X_LAST_UPDATE_DATE := SYSDATE;
872 if(X_MODE = 'I') then
873 X_LAST_UPDATED_BY := 1;
874 X_LAST_UPDATE_LOGIN := 0;
875 elsif (X_MODE IN ('R', 'S')) then
876 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
877 if X_LAST_UPDATED_BY is NULL then
878 X_LAST_UPDATED_BY := -1;
879 end if;
880 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
881 if X_LAST_UPDATE_LOGIN is NULL then
882 X_LAST_UPDATE_LOGIN := -1;
883 end if;
884 else
885 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
886 IGS_GE_MSG_STACK.ADD;
887 APP_EXCEPTION.RAISE_EXCEPTION;
888 end if;
889 Before_DML(
890 p_action=>'UPDATE',
891 x_rowid=>X_ROWID,
892 x_ass_id=>X_ASS_ID,
893 x_attendance_ind=>X_ATTENDANCE_IND,
894 x_cal_type=>X_CAL_TYPE,
895 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
896 x_course_cd=>X_COURSE_CD,
897 x_creation_dt=>X_CREATION_DT,
898 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
899 x_dt_alias=>X_DT_ALIAS,
900 x_end_time=>X_END_TIME,
901 x_ese_id=>X_ESE_ID,
902 x_exam_cal_type=>X_EXAM_CAL_TYPE,
903 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
904 x_person_id=>X_PERSON_ID,
905 x_seat_number=>X_SEAT_NUMBER,
906 x_start_time=>X_START_TIME,
907 x_timeslot=>X_TIMESLOT,
908 x_timeslot_duration=>X_TIMESLOT_DURATION,
909 x_unit_cd=>X_UNIT_CD,
910 x_venue_cd=>X_VENUE_CD,
911 x_creation_date=>X_LAST_UPDATE_DATE,
912 x_created_by=>X_LAST_UPDATED_BY,
913 x_last_update_date=>X_LAST_UPDATE_DATE,
914 x_last_updated_by=>X_LAST_UPDATED_BY,
915 x_last_update_login=>X_LAST_UPDATE_LOGIN,
916 x_uoo_id => X_UOO_ID,
917 x_std_exm_instn_id => X_STD_EXM_INSTN_ID
918 );
919 IF (x_mode = 'S') THEN
920 igs_sc_gen_001.set_ctx('R');
921 END IF;
922 update IGS_AS_STD_EXM_INSTN_ALL set
923 SEAT_NUMBER = new_references.SEAT_NUMBER,
924 TIMESLOT = new_references.TIMESLOT,
925 TIMESLOT_DURATION = new_references.TIMESLOT_DURATION,
926 ESE_ID = new_references.ESE_ID,
927 ATTENDANCE_IND = new_references.ATTENDANCE_IND,
928 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
929 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
930 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
931 where ROWID = X_ROWID
932 ;
933 if (sql%notfound) then
934 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
935 igs_ge_msg_stack.add;
936 igs_sc_gen_001.unset_ctx('R');
937 app_exception.raise_exception;
938 end if;
939 IF (x_mode = 'S') THEN
940 igs_sc_gen_001.unset_ctx('R');
941 END IF;
942
943 After_DML(
944 p_action => 'UPDATE',
945 x_rowid => X_ROWID
946 );
947 EXCEPTION
948 WHEN OTHERS THEN
949 IF (SQLCODE = (-28115)) THEN
950 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
951 fnd_message.set_token ('ERR_CD', SQLCODE);
952 igs_ge_msg_stack.add;
953 igs_sc_gen_001.unset_ctx('R');
954 app_exception.raise_exception;
955 ELSE
956 igs_sc_gen_001.unset_ctx('R');
957 RAISE;
958 END IF;
959
960 end UPDATE_ROW;
961
962 procedure ADD_ROW (
963 X_ROWID in out NOCOPY VARCHAR2,
964 X_ORG_ID in NUMBER,
965 X_ASS_ID in NUMBER,
966 X_EXAM_CAL_TYPE in VARCHAR2,
967 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
968 X_DT_ALIAS in VARCHAR2,
969 X_DAI_SEQUENCE_NUMBER in NUMBER,
970 X_START_TIME in DATE,
971 X_END_TIME in DATE,
972 X_VENUE_CD in VARCHAR2,
973 X_PERSON_ID in NUMBER,
974 X_COURSE_CD in VARCHAR2,
975 X_UNIT_CD in VARCHAR2,
976 X_CAL_TYPE in VARCHAR2,
977 X_CI_SEQUENCE_NUMBER in NUMBER,
978 X_CREATION_DT in DATE,
979 X_SEAT_NUMBER in NUMBER,
980 X_TIMESLOT in DATE,
981 X_TIMESLOT_DURATION in DATE,
982 X_ESE_ID in NUMBER,
983 X_ATTENDANCE_IND in VARCHAR2,
984 X_MODE in VARCHAR2 default 'R',
985 x_uoo_id in NUMBER,
986 x_std_exm_instn_id in out NOCOPY NUMBER
987 ) AS
988 cursor c1 is select rowid from IGS_AS_STD_EXM_INSTN_ALL
989 where std_exm_instn_id = X_STD_EXM_INSTN_ID;
990
991
992
993 begin
994 open c1;
995 fetch c1 into X_ROWID;
996 if (c1%notfound) then
997 close c1;
998 INSERT_ROW (
999 X_ROWID,
1000 X_ORG_ID,
1001 X_ASS_ID,
1002 X_EXAM_CAL_TYPE,
1003 X_EXAM_CI_SEQUENCE_NUMBER,
1004 X_DT_ALIAS,
1005 X_DAI_SEQUENCE_NUMBER,
1006 X_START_TIME,
1007 X_END_TIME,
1008 X_VENUE_CD,
1009 X_PERSON_ID,
1010 X_COURSE_CD,
1011 X_UNIT_CD,
1012 X_CAL_TYPE,
1013 X_CI_SEQUENCE_NUMBER,
1014 X_CREATION_DT,
1015 X_SEAT_NUMBER,
1016 X_TIMESLOT,
1017 X_TIMESLOT_DURATION,
1018 X_ESE_ID,
1019 X_ATTENDANCE_IND,
1020 X_MODE,
1021 X_UOO_ID,
1022 X_STD_EXM_INSTN_ID);
1023 return;
1024 end if;
1025 close c1;
1026 UPDATE_ROW (
1027 X_ROWID,
1028 X_ASS_ID,
1029 X_EXAM_CAL_TYPE,
1030 X_EXAM_CI_SEQUENCE_NUMBER,
1031 X_DT_ALIAS,
1032 X_DAI_SEQUENCE_NUMBER,
1033 X_START_TIME,
1034 X_END_TIME,
1035 X_VENUE_CD,
1036 X_PERSON_ID,
1037 X_COURSE_CD,
1038 X_UNIT_CD,
1039 X_CAL_TYPE,
1040 X_CI_SEQUENCE_NUMBER,
1041 X_CREATION_DT,
1042 X_SEAT_NUMBER,
1043 X_TIMESLOT,
1044 X_TIMESLOT_DURATION,
1045 X_ESE_ID,
1046 X_ATTENDANCE_IND,
1047 X_MODE,
1048 X_UOO_ID,
1049 X_STD_EXM_INSTN_ID);
1050 end ADD_ROW;
1051
1052 procedure DELETE_ROW (
1053 X_ROWID in VARCHAR2,
1054 x_mode IN VARCHAR2
1055 ) AS
1056 begin
1057 Before_DML(
1058 p_action => 'DELETE',
1059 x_rowid => X_ROWID
1060 );
1061
1062 IF (x_mode = 'S') THEN
1063 igs_sc_gen_001.set_ctx('R');
1064 END IF;
1065 delete from IGS_AS_STD_EXM_INSTN_ALL
1066 where ROWID = X_ROWID;
1067 if (sql%notfound) then
1068 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1069 igs_ge_msg_stack.add;
1070 igs_sc_gen_001.unset_ctx('R');
1071 app_exception.raise_exception;
1072 end if;
1073 IF (x_mode = 'S') THEN
1074 igs_sc_gen_001.unset_ctx('R');
1075 END IF;
1076
1077 After_DML(
1078 p_action => 'DELETE',
1079 x_rowid => X_ROWID
1080 );
1081 end DELETE_ROW;
1082
1083 PROCEDURE Check_Constraints (
1084 Column_Name IN VARCHAR2 DEFAULT NULL,
1085 Column_Value IN VARCHAR2 DEFAULT NULL
1086 )
1087 AS
1088 BEGIN
1089 IF column_name is null then
1090 NULL;
1091 ELSIF upper(Column_name) = 'ATTENDANCE_IND' then
1092 new_references.ATTENDANCE_IND := column_value;
1093 ELSIF upper(Column_name) = 'ESE_ID' then
1094 new_references.ESE_ID := IGS_GE_NUMBER.TO_NUM(column_value);
1095 ELSIF upper(Column_name) = 'DAI_SEQUENCE_NUMBER' then
1096 new_references.DAI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
1097 ELSIF upper(Column_name) = 'EXAM_CI_SEQUENCE_NUMBER' then
1098 new_references.EXAM_CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
1099 ELSIF upper(Column_name) = 'ATTENDANCE_IND' then
1100 new_references.ATTENDANCE_IND := column_value;
1101 ELSIF upper(Column_name) = 'CAL_TYPE' then
1102 new_references.CAL_TYPE := column_value;
1103 ELSIF upper(Column_name) = 'COURSE_CD' then
1104 new_references.COURSE_CD := column_value;
1105 ELSIF upper(Column_name) = 'DT_ALIAS' then
1106 new_references.DT_ALIAS := column_value;
1107 ELSIF upper(Column_name) = 'EXAM_CAL_TYPE' then
1108 new_references.EXAM_CAL_TYPE := column_value;
1109 ELSIF upper(Column_name) = 'UNIT_CD' then
1110 new_references.UNIT_CD := column_value;
1111 ELSIF upper(Column_name) = 'VENUE_CD' then
1112 new_references.VENUE_CD := column_value;
1113 ELSIF upper(Column_name) = 'SEAT_NUMBER' then
1114 new_references.SEAT_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
1115 end if ;
1116
1117 IF upper(column_name) = 'ATTENDANCE_IND' OR
1118 column_name is null Then
1119 IF new_references.ATTENDANCE_IND NOT IN ('Y','N') Then
1120 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1121 IGS_GE_MSG_STACK.ADD;
1122 APP_EXCEPTION.RAISE_EXCEPTION;
1123 END IF;
1124 END IF;
1125
1126 IF upper(column_name) = 'ESE_ID' OR
1127 column_name is null Then
1128 IF new_references.ESE_ID < 1 OR new_references.ESE_ID > 999999 Then
1129 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1130 IGS_GE_MSG_STACK.ADD;
1131 APP_EXCEPTION.RAISE_EXCEPTION;
1132 END IF;
1133 END IF;
1134
1135
1136 IF upper(column_name) = 'DAI_SEQUENCE_NUMBER' OR
1137 column_name is null Then
1138 IF new_references.DAI_SEQUENCE_NUMBER < 1 OR new_references.DAI_SEQUENCE_NUMBER > 999999 Then
1139 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1140 IGS_GE_MSG_STACK.ADD;
1141 APP_EXCEPTION.RAISE_EXCEPTION;
1142 END IF;
1143 END IF;
1144
1145
1146 IF upper(column_name) = 'EXAM_CI_SEQUENCE_NUMBER' OR
1147 column_name is null Then
1148 IF new_references.EXAM_CI_SEQUENCE_NUMBER < 1 OR new_references.EXAM_CI_SEQUENCE_NUMBER> 999999 Then
1149 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1150 IGS_GE_MSG_STACK.ADD;
1151 APP_EXCEPTION.RAISE_EXCEPTION;
1152 END IF;
1153 END IF;
1154
1155 IF upper(column_name) = 'ATTENDANCE_IND' OR
1156 column_name is null Then
1157 IF new_references.ATTENDANCE_IND <> UPPER(new_references.ATTENDANCE_IND) Then
1158 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1159 IGS_GE_MSG_STACK.ADD;
1160 APP_EXCEPTION.RAISE_EXCEPTION;
1161 END IF;
1162 END IF;
1163 IF upper(column_name) = 'CAL_TYPE' OR
1164 column_name is null Then
1165 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
1166 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1167 IGS_GE_MSG_STACK.ADD;
1168 APP_EXCEPTION.RAISE_EXCEPTION;
1169 END IF;
1170 END IF;
1171 IF upper(column_name) = 'COURSE_CD' OR
1172 column_name is null Then
1173 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
1174 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1175 IGS_GE_MSG_STACK.ADD;
1176 APP_EXCEPTION.RAISE_EXCEPTION;
1177 END IF;
1178 END IF;
1179 IF upper(column_name) = 'DT_ALIAS' OR
1180 column_name is null Then
1181 IF new_references.DT_ALIAS <> UPPER(new_references.DT_ALIAS) Then
1182 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1183 IGS_GE_MSG_STACK.ADD;
1184 APP_EXCEPTION.RAISE_EXCEPTION;
1185 END IF;
1186 END IF;
1187 IF upper(column_name) = 'EXAM_CAL_TYPE' OR
1188 column_name is null Then
1189 IF new_references.EXAM_CAL_TYPE <> UPPER(new_references.EXAM_CAL_TYPE) Then
1190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1191 IGS_GE_MSG_STACK.ADD;
1192 APP_EXCEPTION.RAISE_EXCEPTION;
1193 END IF;
1194 END IF;
1195 IF upper(column_name) = 'UNIT_CD' OR
1196 column_name is null Then
1197 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
1198 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1199 IGS_GE_MSG_STACK.ADD;
1200 APP_EXCEPTION.RAISE_EXCEPTION;
1201 END IF;
1202 END IF;
1203 IF upper(column_name) = 'VENUE_CD' OR
1204 column_name is null Then
1205 IF new_references.VENUE_CD <> UPPER(new_references.VENUE_CD) Then
1206 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1207 IGS_GE_MSG_STACK.ADD;
1208 APP_EXCEPTION.RAISE_EXCEPTION;
1209 END IF;
1210 END IF;
1211
1212 IF upper(column_name) = 'SEAT_NUMBER' OR
1213 column_name is null Then
1214 IF new_references.SEAT_NUMBER < 0 OR new_references.SEAT_NUMBER > 9999 Then
1215 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1216 IGS_GE_MSG_STACK.ADD;
1217 APP_EXCEPTION.RAISE_EXCEPTION;
1218 END IF;
1219 END IF;
1220
1221 END Check_Constraints;
1222
1223
1224 end IGS_AS_STD_EXM_INSTN_PKG;