[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_EXM_INS_SPVSR_PKG
Source
1 package body IGS_AS_EXM_INS_SPVSR_PKG AS
2 /* $Header: IGSDI11B.pls 115.4 2002/11/28 23:12:54 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_EXM_INS_SPVSR%RowType;
5 new_references IGS_AS_EXM_INS_SPVSR%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_person_id IN NUMBER DEFAULT NULL,
10 x_ass_id IN NUMBER DEFAULT NULL,
11 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_dai_sequence_number IN NUMBER DEFAULT NULL,
15 x_start_time IN DATE DEFAULT NULL,
16 x_end_time IN DATE DEFAULT NULL,
17 x_ese_id IN NUMBER DEFAULT NULL,
18 x_venue_cd IN VARCHAR2 DEFAULT NULL,
19 x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
20 x_override_start_time IN DATE DEFAULT NULL,
21 x_override_end_time IN DATE DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) AS
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM IGS_AS_EXM_INS_SPVSR
31 WHERE rowid = x_rowid;
32 BEGIN
33 l_rowid := x_rowid;
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40
41 IGS_GE_MSG_STACK.ADD;
42 Close cur_old_ref_values;
43 APP_EXCEPTION.RAISE_EXCEPTION;
44
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48 -- Populate New Values.
49 new_references.person_id := x_person_id;
50 new_references.ass_id := x_ass_id;
51 new_references.exam_cal_type := x_exam_cal_type;
52 new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
53 new_references.dt_alias := x_dt_alias;
54 new_references.dai_sequence_number := x_dai_sequence_number;
55 new_references.start_time := x_start_time;
56 new_references.end_time := x_end_time;
57 new_references.ese_id := x_ese_id;
58 new_references.venue_cd := x_venue_cd;
59 new_references.exam_supervisor_type := x_exam_supervisor_type;
60 new_references.override_start_time := x_override_start_time;
61 new_references.override_end_time := x_override_end_time;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72 END Set_Column_Values;
73 PROCEDURE BeforeRowInsertUpdate1(
74 p_inserting IN BOOLEAN DEFAULT FALSE,
75 p_updating IN BOOLEAN DEFAULT FALSE,
76 p_deleting IN BOOLEAN DEFAULT FALSE
77 ) AS
78 v_message_name VARCHAR2(30);
79 BEGIN
80 IF p_inserting THEN
81 -- Call routine to fill in exam session key.
82 IGS_AS_GEN_006.ASSP_GET_ESE_KEY(
83 new_references.exam_cal_type,
84 new_references.exam_ci_sequence_number,
85 new_references.dt_alias,
86 new_references.dai_sequence_number,
87 new_references.start_time,
88 new_references.end_time,
89 new_references.ese_id);
90 END IF;
91 --w.r.t BUG #1956374 , Procedure assp_val_est_closed reference is changed
92 -- Validate that the exam supervisor type is not closed.
93 IF p_inserting OR
94 (p_updating AND
95 (new_references.exam_supervisor_type <> old_references.exam_supervisor_type)) THEN
96 IF IGS_AS_VAL_ESU.assp_val_est_closed(new_references.exam_supervisor_type,
97 v_message_name) = FALSE THEN
98 FND_message.SET_NAME('IGS',v_message_name);
99 IGS_GE_MSG_STACK.ADD;
100 APP_EXCEPTION.RAISE_EXCEPTION;
101 END IF;
102 END IF;
103 END BeforeRowInsertUpdate1;
104
105 PROCEDURE Check_Parent_Existance AS
106 BEGIN
107 IF (((old_references.ass_id = new_references.ass_id) AND
108 (old_references.exam_cal_type = new_references.exam_cal_type) AND
109 (old_references.exam_ci_sequence_number = new_references.exam_ci_sequence_number) AND
110 (old_references.dt_alias = new_references.dt_alias) AND
111 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
112 (old_references.start_time = new_references.start_time) AND
113 (old_references.end_time = new_references.end_time) AND
114 (old_references.venue_cd = new_references.venue_cd)) OR
115 ((new_references.ass_id IS NULL) OR
116 (new_references.exam_cal_type IS NULL) OR
117 (new_references.exam_ci_sequence_number IS NULL) OR
118 (new_references.dt_alias IS NULL) OR
119 (new_references.dai_sequence_number IS NULL) OR
120 (new_references.start_time IS NULL) OR
121 (new_references.end_time IS NULL) OR
122 (new_references.venue_cd IS NULL))) THEN
123 NULL;
124 ELSIF NOT IGS_AS_EXAM_INSTANCE_PKG.Get_PK_For_Validation (
125 new_references.ass_id,
126 new_references.exam_cal_type,
127 new_references.exam_ci_sequence_number,
128 new_references.dt_alias,
129 new_references.dai_sequence_number,
130 new_references.start_time,
131 new_references.end_time,
132 new_references.venue_cd ) THEN
133 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
134 IGS_GE_MSG_STACK.ADD;
135 APP_EXCEPTION.RAISE_EXCEPTION;
136
137
138 END IF;
139 IF (((old_references.ese_id = new_references.ese_id)) OR
140 ((new_references.ese_id IS NULL))) THEN
141 NULL;
142 ELSIF NOT IGS_AS_EXAM_SESSION_PKG.Get_UK_For_Validation (
143 new_references.ese_id )THEN
144 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
145 IGS_GE_MSG_STACK.ADD;
146 APP_EXCEPTION.RAISE_EXCEPTION;
147
148 END IF;
149 IF (((old_references.exam_supervisor_type = new_references.exam_supervisor_type)) OR
150 ((new_references.exam_supervisor_type IS NULL))) THEN
151 NULL;
152 ELSIF NOT IGS_AS_EXM_SPRVSRTYP_PKG.Get_PK_For_Validation (
153 new_references.exam_supervisor_type
154 )THEN
155 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
156 IGS_GE_MSG_STACK.ADD;
157 APP_EXCEPTION.RAISE_EXCEPTION;
158
159 END IF;
160 IF (((old_references.person_id = new_references.person_id)) OR
161 ((new_references.person_id IS NULL))) THEN
162 NULL;
163 ELSIF NOT IGS_AS_EXM_SUPRVISOR_PKG.Get_PK_For_Validation (
164 new_references.person_id )THEN
165 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
166 IGS_GE_MSG_STACK.ADD;
167 APP_EXCEPTION.RAISE_EXCEPTION;
168
169 END IF;
170 END Check_Parent_Existance;
171 function Get_PK_For_Validation (
172 x_person_id IN NUMBER,
173 x_ass_id IN NUMBER,
174 x_exam_cal_type IN VARCHAR2,
175 x_exam_ci_sequence_number IN NUMBER,
176 x_dt_alias IN VARCHAR2,
177 x_dai_sequence_number IN NUMBER,
178 x_start_time IN DATE,
179 x_end_time IN DATE,
180 x_venue_cd IN VARCHAR2
181 )return boolean AS
182 CURSOR cur_rowid IS
183 SELECT rowid
184 FROM IGS_AS_EXM_INS_SPVSR
185 WHERE person_id = x_person_id
186 AND ass_id = x_ass_id
187 AND exam_cal_type = x_exam_cal_type
188 AND exam_ci_sequence_number = x_exam_ci_sequence_number
189 AND dt_alias = x_dt_alias
190 AND dai_sequence_number = x_dai_sequence_number
191 AND start_time = x_start_time
192 AND end_time = x_end_time
193 AND venue_cd = x_venue_cd
194 FOR UPDATE NOWAIT;
195 lv_rowid cur_rowid%RowType;
196 BEGIN
197 Open cur_rowid;
198 Fetch cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 Close cur_rowid;
201 Return (TRUE);
202 ELSE
203 Close cur_rowid;
204 Return (FALSE);
205 END IF;
206
207 END Get_PK_For_Validation;
208 PROCEDURE GET_FK_IGS_AS_EXAM_INSTANCE (
209 x_ass_id IN NUMBER,
210 x_exam_cal_type IN VARCHAR2,
211 x_exam_ci_sequence_number IN NUMBER,
212 x_dt_alias IN VARCHAR2,
213 x_dai_sequence_number IN NUMBER,
214 x_start_time IN DATE,
215 x_end_time IN DATE,
216 x_venue_cd IN VARCHAR2
217 ) AS
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM IGS_AS_EXM_INS_SPVSR
221 WHERE ass_id = x_ass_id
222 AND exam_cal_type = x_exam_cal_type
223 AND exam_ci_sequence_number = x_exam_ci_sequence_number
224 AND dt_alias = x_dt_alias
225 AND dai_sequence_number = x_dai_sequence_number
226 AND start_time = x_start_time
227 AND end_time = x_end_time
228 AND venue_cd = x_venue_cd ;
229 lv_rowid cur_rowid%RowType;
230 BEGIN
231 Open cur_rowid;
232 Fetch cur_rowid INTO lv_rowid;
233 IF (cur_rowid%FOUND) THEN
234 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EIS_EI_FK');
235 IGS_GE_MSG_STACK.ADD;
236 Close cur_rowid;
237 APP_EXCEPTION.RAISE_EXCEPTION;
238
239 Return;
240 END IF;
241 Close cur_rowid;
242 END GET_FK_IGS_AS_EXAM_INSTANCE;
243 PROCEDURE GET_UFK_IGS_AS_EXAM_SESSION (
244 x_ese_id IN NUMBER
245 ) AS
246 CURSOR cur_rowid IS
247 SELECT rowid
248 FROM IGS_AS_EXM_INS_SPVSR
249 WHERE ese_id = x_ese_id ;
250 lv_rowid cur_rowid%RowType;
251 BEGIN
252 Open cur_rowid;
253 Fetch cur_rowid INTO lv_rowid;
254 IF (cur_rowid%FOUND) THEN
255 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EIS_ESE_UFK');
256 IGS_GE_MSG_STACK.ADD;
257 Close cur_rowid;
258 APP_EXCEPTION.RAISE_EXCEPTION;
259
260 Return;
261 END IF;
262 Close cur_rowid;
263 END GET_UFK_IGS_AS_EXAM_SESSION;
264 PROCEDURE GET_FK_IGS_AS_EXM_SPRVSRTYP (
265 x_exam_supervisor_type IN VARCHAR2
266 ) AS
267 CURSOR cur_rowid IS
268 SELECT rowid
269 FROM IGS_AS_EXM_INS_SPVSR
270 WHERE exam_supervisor_type = x_exam_supervisor_type ;
271 lv_rowid cur_rowid%RowType;
272 BEGIN
273 Open cur_rowid;
274 Fetch cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EIS_EST_FK');
277 IGS_GE_MSG_STACK.ADD;
278 Close cur_rowid;
279 APP_EXCEPTION.RAISE_EXCEPTION;
280
281 Return;
282 END IF;
283 Close cur_rowid;
284 END GET_FK_IGS_AS_EXM_SPRVSRTYP;
285 PROCEDURE GET_FK_IGS_AS_EXM_SUPRVISOR (
286 x_person_id IN NUMBER
287 ) AS
288 CURSOR cur_rowid IS
289 SELECT rowid
290 FROM IGS_AS_EXM_INS_SPVSR
291 WHERE person_id = x_person_id ;
292 lv_rowid cur_rowid%RowType;
293 BEGIN
294 Open cur_rowid;
295 Fetch cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EIS_ESU_FK');
298 IGS_GE_MSG_STACK.ADD;
299 Close cur_rowid;
300 APP_EXCEPTION.RAISE_EXCEPTION;
301
302 Return;
303 END IF;
304 Close cur_rowid;
305 END GET_FK_IGS_AS_EXM_SUPRVISOR;
306 PROCEDURE Before_DML (
307 p_action IN VARCHAR2,
308 x_rowid IN VARCHAR2 DEFAULT NULL,
309 x_person_id IN NUMBER DEFAULT NULL,
310 x_ass_id IN NUMBER DEFAULT NULL,
311 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
312 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
313 x_dt_alias IN VARCHAR2 DEFAULT NULL,
314 x_dai_sequence_number IN NUMBER DEFAULT NULL,
315 x_start_time IN DATE DEFAULT NULL,
316 x_end_time IN DATE DEFAULT NULL,
317 x_ese_id IN NUMBER DEFAULT NULL,
318 x_venue_cd IN VARCHAR2 DEFAULT NULL,
319 x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
320 x_override_start_time IN DATE DEFAULT NULL,
321 x_override_end_time IN DATE DEFAULT NULL,
322 x_creation_date IN DATE DEFAULT NULL,
323 x_created_by IN NUMBER DEFAULT NULL,
324 x_last_update_date IN DATE DEFAULT NULL,
325 x_last_updated_by IN NUMBER DEFAULT NULL,
326 x_last_update_login IN NUMBER DEFAULT NULL
327 ) AS
328 BEGIN
329 Set_Column_Values (
330 p_action,
331 x_rowid,
332 x_person_id,
333 x_ass_id,
334 x_exam_cal_type,
335 x_exam_ci_sequence_number,
336 x_dt_alias,
337 x_dai_sequence_number,
338 x_start_time,
339 x_end_time,
340 x_ese_id,
341 x_venue_cd,
342 x_exam_supervisor_type,
343 x_override_start_time,
344 x_override_end_time,
345 x_creation_date,
346 x_created_by,
347 x_last_update_date,
348 x_last_updated_by,
349 x_last_update_login
350 );
351 IF (p_action = 'INSERT') THEN
352 -- Call all the procedures related to Before Insert.
353 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
354
355 IF Get_PK_For_Validation (
356 NEW_REFERENCES.person_id ,
357 NEW_REFERENCES.ass_id ,
358 NEW_REFERENCES.exam_cal_type ,
359 NEW_REFERENCES.exam_ci_sequence_number ,
360 NEW_REFERENCES.dt_alias ,
361 NEW_REFERENCES.dai_sequence_number ,
362 NEW_REFERENCES.start_time ,
363 NEW_REFERENCES.end_time,
364 NEW_REFERENCES.venue_cd ) THEN
365 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
366 IGS_GE_MSG_STACK.ADD;
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 END IF;
369
370 Check_Constraints;
371 Check_Parent_Existance;
372 ELSIF (p_action = 'UPDATE') THEN
373 -- Call all the procedures related to Before Update.
374 BeforeRowInsertUpdate1 ( p_updating => TRUE );
375 Check_Constraints;
376 Check_Parent_Existance;
377
378 ELSIF (p_action = 'VALIDATE_INSERT') THEN
379 IF Get_PK_For_Validation (
380 NEW_REFERENCES.person_id ,
381 NEW_REFERENCES.ass_id ,
382 NEW_REFERENCES.exam_cal_type ,
383 NEW_REFERENCES.exam_ci_sequence_number ,
384 NEW_REFERENCES.dt_alias ,
385 NEW_REFERENCES.dai_sequence_number ,
386 NEW_REFERENCES.start_time ,
387 NEW_REFERENCES.end_time,
388 NEW_REFERENCES.venue_cd ) THEN
389 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
390 IGS_GE_MSG_STACK.ADD;
391 APP_EXCEPTION.RAISE_EXCEPTION;
392 END IF;
393
394 Check_Constraints;
395 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
396 Check_Constraints;
397
398 END IF;
399 END Before_DML;
400
401 procedure INSERT_ROW (
402 X_ROWID in out NOCOPY VARCHAR2,
403 X_PERSON_ID in NUMBER,
404 X_ASS_ID in NUMBER,
405 X_EXAM_CAL_TYPE in VARCHAR2,
406 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
407 X_DT_ALIAS in VARCHAR2,
408 X_DAI_SEQUENCE_NUMBER in NUMBER,
409 X_START_TIME in DATE,
410 X_END_TIME in DATE,
411 X_VENUE_CD in VARCHAR2,
412 X_ESE_ID in NUMBER,
413 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
414 X_OVERRIDE_START_TIME in DATE,
415 X_OVERRIDE_END_TIME in DATE,
416 X_MODE in VARCHAR2 default 'R'
417 ) AS
418 cursor C is select ROWID from IGS_AS_EXM_INS_SPVSR
419 where PERSON_ID = X_PERSON_ID
420 and ASS_ID = X_ASS_ID
421 and EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
422 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
423 and DT_ALIAS = X_DT_ALIAS
424 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
425 and START_TIME = X_START_TIME
426 and END_TIME = X_END_TIME
427 and VENUE_CD = X_VENUE_CD;
428 X_LAST_UPDATE_DATE DATE;
429 X_LAST_UPDATED_BY NUMBER;
430 X_LAST_UPDATE_LOGIN NUMBER;
431 begin
432 X_LAST_UPDATE_DATE := SYSDATE;
433 if(X_MODE = 'I') then
434 X_LAST_UPDATED_BY := 1;
435 X_LAST_UPDATE_LOGIN := 0;
436 elsif (X_MODE = 'R') then
437 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
438 if X_LAST_UPDATED_BY is NULL then
439 X_LAST_UPDATED_BY := -1;
440 end if;
441 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
442 if X_LAST_UPDATE_LOGIN is NULL then
443 X_LAST_UPDATE_LOGIN := -1;
444 end if;
445 else
446 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
447 IGS_GE_MSG_STACK.ADD;
448 APP_EXCEPTION.RAISE_EXCEPTION;
449 end if;
450 Before_DML(
451 p_action=>'INSERT',
452 x_rowid=>X_ROWID,
453 x_ass_id=>X_ASS_ID,
454 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
455 x_dt_alias=>X_DT_ALIAS,
456 x_end_time=>X_END_TIME,
457 x_ese_id=>X_ESE_ID,
458 x_exam_cal_type=>X_EXAM_CAL_TYPE,
459 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
460 x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
461 x_override_end_time=>X_OVERRIDE_END_TIME,
462 x_override_start_time=>X_OVERRIDE_START_TIME,
463 x_person_id=>X_PERSON_ID,
464 x_start_time=>X_START_TIME,
465 x_venue_cd=>X_VENUE_CD,
466 x_creation_date=>X_LAST_UPDATE_DATE,
467 x_created_by=>X_LAST_UPDATED_BY,
468 x_last_update_date=>X_LAST_UPDATE_DATE,
469 x_last_updated_by=>X_LAST_UPDATED_BY,
470 x_last_update_login=>X_LAST_UPDATE_LOGIN
471 );
472 insert into IGS_AS_EXM_INS_SPVSR (
473 PERSON_ID,
474 ASS_ID,
475 EXAM_CAL_TYPE,
476 EXAM_CI_SEQUENCE_NUMBER,
477 DT_ALIAS,
478 DAI_SEQUENCE_NUMBER,
479 START_TIME,
480 END_TIME,
481 ESE_ID,
482 VENUE_CD,
483 EXAM_SUPERVISOR_TYPE,
484 OVERRIDE_START_TIME,
485 OVERRIDE_END_TIME,
486 CREATION_DATE,
487 CREATED_BY,
488 LAST_UPDATE_DATE,
489 LAST_UPDATED_BY,
490 LAST_UPDATE_LOGIN
491 ) values (
492 NEW_REFERENCES.PERSON_ID,
493 NEW_REFERENCES.ASS_ID,
494 NEW_REFERENCES.EXAM_CAL_TYPE,
495 NEW_REFERENCES.EXAM_CI_SEQUENCE_NUMBER,
496 NEW_REFERENCES.DT_ALIAS,
497 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
498 NEW_REFERENCES.START_TIME,
499 NEW_REFERENCES.END_TIME,
500 NEW_REFERENCES.ESE_ID,
501 NEW_REFERENCES.VENUE_CD,
502 NEW_REFERENCES.EXAM_SUPERVISOR_TYPE,
503 NEW_REFERENCES.OVERRIDE_START_TIME,
504 NEW_REFERENCES.OVERRIDE_END_TIME,
505 X_LAST_UPDATE_DATE,
506 X_LAST_UPDATED_BY,
507 X_LAST_UPDATE_DATE,
508 X_LAST_UPDATED_BY,
509 X_LAST_UPDATE_LOGIN
510 );
511 open c;
512 fetch c into X_ROWID;
513 if (c%notfound) then
514 close c;
515 raise no_data_found;
516 end if;
517 close c;
518
519 end INSERT_ROW;
520 procedure LOCK_ROW (
521 X_ROWID in VARCHAR2,
522 X_PERSON_ID in NUMBER,
523 X_ASS_ID in NUMBER,
524 X_EXAM_CAL_TYPE in VARCHAR2,
525 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
526 X_DT_ALIAS in VARCHAR2,
527 X_DAI_SEQUENCE_NUMBER in NUMBER,
528 X_START_TIME in DATE,
529 X_END_TIME in DATE,
530 X_VENUE_CD in VARCHAR2,
531 X_ESE_ID in NUMBER,
532 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
533 X_OVERRIDE_START_TIME in DATE,
534 X_OVERRIDE_END_TIME in DATE
535 ) AS
536 cursor c1 is select
537 ESE_ID,
538 EXAM_SUPERVISOR_TYPE,
539 OVERRIDE_START_TIME,
540 OVERRIDE_END_TIME
541 from IGS_AS_EXM_INS_SPVSR
542 where ROWID = X_ROWID for update nowait;
543 tlinfo c1%rowtype;
544 begin
545 open c1;
546 fetch c1 into tlinfo;
547 if (c1%notfound) then
548 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
549 IGS_GE_MSG_STACK.ADD;
550 APP_EXCEPTION.RAISE_EXCEPTION;
551 close c1;
552 return;
553 end if;
554 close c1;
555 if ( (tlinfo.ESE_ID = X_ESE_ID)
556 AND (tlinfo.EXAM_SUPERVISOR_TYPE = X_EXAM_SUPERVISOR_TYPE)
557 AND ((tlinfo.OVERRIDE_START_TIME = X_OVERRIDE_START_TIME)
558 OR ((tlinfo.OVERRIDE_START_TIME is null)
559 AND (X_OVERRIDE_START_TIME is null)))
560 AND ((tlinfo.OVERRIDE_END_TIME = X_OVERRIDE_END_TIME)
561 OR ((tlinfo.OVERRIDE_END_TIME is null)
562 AND (X_OVERRIDE_END_TIME is null)))
563 ) then
564 null;
565 else
566 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
567 IGS_GE_MSG_STACK.ADD;
568 APP_EXCEPTION.RAISE_EXCEPTION;
569 end if;
570 return;
571 end LOCK_ROW;
572 procedure UPDATE_ROW (
573 X_ROWID in VARCHAR2,
574 X_PERSON_ID in NUMBER,
575 X_ASS_ID in NUMBER,
576 X_EXAM_CAL_TYPE in VARCHAR2,
577 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
578 X_DT_ALIAS in VARCHAR2,
579 X_DAI_SEQUENCE_NUMBER in NUMBER,
580 X_START_TIME in DATE,
581 X_END_TIME in DATE,
582 X_VENUE_CD in VARCHAR2,
583 X_ESE_ID in NUMBER,
584 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
585 X_OVERRIDE_START_TIME in DATE,
586 X_OVERRIDE_END_TIME in DATE,
587 X_MODE in VARCHAR2 default 'R'
588 ) AS
589 X_LAST_UPDATE_DATE DATE;
590 X_LAST_UPDATED_BY NUMBER;
591 X_LAST_UPDATE_LOGIN NUMBER;
592 begin
593 X_LAST_UPDATE_DATE := SYSDATE;
594 if(X_MODE = 'I') then
595 X_LAST_UPDATED_BY := 1;
596 X_LAST_UPDATE_LOGIN := 0;
597 elsif (X_MODE = 'R') then
598 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
599 if X_LAST_UPDATED_BY is NULL then
600 X_LAST_UPDATED_BY := -1;
601 end if;
602 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
603 if X_LAST_UPDATE_LOGIN is NULL then
604 X_LAST_UPDATE_LOGIN := -1;
605 end if;
606 else
607 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
608 IGS_GE_MSG_STACK.ADD;
609 APP_EXCEPTION.RAISE_EXCEPTION;
610 end if;
611 Before_DML(
612 p_action=>'UPDATE',
613 x_rowid=>X_ROWID,
614 x_ass_id=>X_ASS_ID,
615 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
616 x_dt_alias=>X_DT_ALIAS,
617 x_end_time=>X_END_TIME,
618 x_ese_id=>X_ESE_ID,
619 x_exam_cal_type=>X_EXAM_CAL_TYPE,
620 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
621 x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
622 x_override_end_time=>X_OVERRIDE_END_TIME,
623 x_override_start_time=>X_OVERRIDE_START_TIME,
624 x_person_id=>X_PERSON_ID,
625 x_start_time=>X_START_TIME,
626 x_venue_cd=>X_VENUE_CD,
627 x_creation_date=>X_LAST_UPDATE_DATE,
628 x_created_by=>X_LAST_UPDATED_BY,
629 x_last_update_date=>X_LAST_UPDATE_DATE,
630 x_last_updated_by=>X_LAST_UPDATED_BY,
631 x_last_update_login=>X_LAST_UPDATE_LOGIN
632 );
633 update IGS_AS_EXM_INS_SPVSR set
634 ESE_ID = NEW_REFERENCES.ESE_ID,
635 EXAM_SUPERVISOR_TYPE = NEW_REFERENCES.EXAM_SUPERVISOR_TYPE,
636 OVERRIDE_START_TIME = NEW_REFERENCES.OVERRIDE_START_TIME,
637 OVERRIDE_END_TIME = NEW_REFERENCES.OVERRIDE_END_TIME,
638 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
639 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
640 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
641 where ROWID = X_ROWID;
642 if (sql%notfound) then
643 raise no_data_found;
644 end if;
645
646 end UPDATE_ROW;
647 procedure ADD_ROW (
648 X_ROWID in out NOCOPY VARCHAR2,
649 X_PERSON_ID in NUMBER,
650 X_ASS_ID in NUMBER,
651 X_EXAM_CAL_TYPE in VARCHAR2,
652 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
653 X_DT_ALIAS in VARCHAR2,
654 X_DAI_SEQUENCE_NUMBER in NUMBER,
655 X_START_TIME in DATE,
656 X_END_TIME in DATE,
657 X_VENUE_CD in VARCHAR2,
658 X_ESE_ID in NUMBER,
659 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
660 X_OVERRIDE_START_TIME in DATE,
661 X_OVERRIDE_END_TIME in DATE,
662 X_MODE in VARCHAR2 default 'R'
663 ) AS
664 cursor c1 is select rowid from IGS_AS_EXM_INS_SPVSR
665 where PERSON_ID = X_PERSON_ID
666 and ASS_ID = X_ASS_ID
667 and EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
668 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
669 and DT_ALIAS = X_DT_ALIAS
670 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
671 and START_TIME = X_START_TIME
672 and END_TIME = X_END_TIME
673 and VENUE_CD = X_VENUE_CD
674 ;
675 begin
676 open c1;
677 fetch c1 into X_ROWID;
678 if (c1%notfound) then
679 close c1;
680 INSERT_ROW (
681 X_ROWID,
682 X_PERSON_ID,
683 X_ASS_ID,
684 X_EXAM_CAL_TYPE,
685 X_EXAM_CI_SEQUENCE_NUMBER,
686 X_DT_ALIAS,
687 X_DAI_SEQUENCE_NUMBER,
688 X_START_TIME,
689 X_END_TIME,
690 X_VENUE_CD,
691 X_ESE_ID,
692 X_EXAM_SUPERVISOR_TYPE,
693 X_OVERRIDE_START_TIME,
694 X_OVERRIDE_END_TIME,
695 X_MODE);
696 return;
697 end if;
698 close c1;
699 UPDATE_ROW (
700 X_ROWID,
701 X_PERSON_ID,
702 X_ASS_ID,
703 X_EXAM_CAL_TYPE,
704 X_EXAM_CI_SEQUENCE_NUMBER,
705 X_DT_ALIAS,
706 X_DAI_SEQUENCE_NUMBER,
707 X_START_TIME,
708 X_END_TIME,
709 X_VENUE_CD,
710 X_ESE_ID,
711 X_EXAM_SUPERVISOR_TYPE,
712 X_OVERRIDE_START_TIME,
713 X_OVERRIDE_END_TIME,
714 X_MODE);
715 end ADD_ROW;
716 procedure DELETE_ROW (
717 X_ROWID in VARCHAR2) AS
718 begin
719 Before_DML(
720 p_action => 'DELETE',
721 x_rowid => X_ROWID
722 );
723 delete from IGS_AS_EXM_INS_SPVSR
724 where ROWID = X_ROWID;
725 if (sql%notfound) then
726 raise no_data_found;
727 end if;
728
729 end DELETE_ROW;
730
731
732 PROCEDURE Check_Constraints (
733 Column_Name IN VARCHAR2 DEFAULT NULL,
734 Column_Value IN VARCHAR2 DEFAULT NULL
735 )
736 AS
737 BEGIN
738 IF column_name is null then
739 NULL;
740 ELSIF upper(Column_name) = 'DT_ALIAS' then
741 new_references.DT_ALIAS := column_value;
742 ELSIF upper(Column_name) = 'EXAM_CAL_TYPE' then
743 new_references.EXAM_CAL_TYPE := column_value;
744 ELSIF upper(Column_name) = 'EXAM_SUPERVISOR_TYPE' then
745 new_references.EXAM_SUPERVISOR_TYPE := column_value;
746 ELSIF upper(Column_name) = 'VENUE_CD' then
747 new_references.VENUE_CD := column_value;
748 ELSIF upper(Column_name) = 'DAI_SEQUENCE_NUMBER' then
749 new_references.DAI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
750 ELSIF upper(Column_name) = 'EXAM_CI_SEQUENCE_NUMBER' then
751 new_references.EXAM_CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
752 ELSIF upper(Column_name) = 'ESE_ID' then
753 new_references.ESE_ID := IGS_GE_NUMBER.TO_NUM(column_value);
754 end if;
755 IF upper(column_name) = 'DT_ALIAS' OR
756 column_name is null Then
757 IF new_references.DT_ALIAS <> UPPER(new_references.DT_ALIAS) Then
758 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
759 IGS_GE_MSG_STACK.ADD;
760 APP_EXCEPTION.RAISE_EXCEPTION;
761 END IF;
762 END IF;
763 IF upper(column_name) = 'EXAM_CAL_TYPE' OR
764 column_name is null Then
765 IF new_references.EXAM_CAL_TYPE <> UPPER(new_references.EXAM_CAL_TYPE) Then
766 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
767 IGS_GE_MSG_STACK.ADD;
768 APP_EXCEPTION.RAISE_EXCEPTION;
769 END IF;
770 END IF;
771 IF upper(column_name) = 'EXAM_SUPERVISOR_TYPE' OR
772 column_name is null Then
773 IF new_references.EXAM_SUPERVISOR_TYPE <> UPPER(new_references.EXAM_SUPERVISOR_TYPE) Then
774 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
775 IGS_GE_MSG_STACK.ADD;
776 APP_EXCEPTION.RAISE_EXCEPTION;
777 END IF;
778 END IF;
779 IF upper(column_name) = 'VENUE_CD' OR
780 column_name is null Then
781 IF new_references.VENUE_CD <> UPPER(new_references.VENUE_CD) Then
782 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
783 IGS_GE_MSG_STACK.ADD;
784 APP_EXCEPTION.RAISE_EXCEPTION;
785 END IF;
786 END IF;
787
788 IF upper(column_name) = 'DAI_SEQUENCE_NUMBER' OR
789 column_name is null Then
790 IF new_references.DAI_SEQUENCE_NUMBER < 1 OR new_references.DAI_SEQUENCE_NUMBER > 999999 THEN
791 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
792 IGS_GE_MSG_STACK.ADD;
793 APP_EXCEPTION.RAISE_EXCEPTION;
794 END IF;
795 END IF;
796 IF upper(column_name) = 'EXAM_CI_SEQUENCE_NUMBER' OR
797 column_name is null Then
798 IF new_references.EXAM_CI_SEQUENCE_NUMBER < 1 OR new_references.EXAM_CI_SEQUENCE_NUMBER > 999999 Then
799 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
800 IGS_GE_MSG_STACK.ADD;
801 APP_EXCEPTION.RAISE_EXCEPTION;
802 END IF;
803 END IF;
804 IF upper(column_name) = 'ESE_ID' OR
805 column_name is null Then
806 IF new_references.ESE_ID < 1 OR new_references.ESE_ID > 999999 Then
807 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
808 IGS_GE_MSG_STACK.ADD;
809 APP_EXCEPTION.RAISE_EXCEPTION;
810 END IF;
811 END IF;
812 END Check_Constraints;
813 end IGS_AS_EXM_INS_SPVSR_PKG;