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