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