1 PACKAGE BODY igs_ps_us_exam_meet_pkg AS
2 /* $Header: IGSPI2RB.pls 115.3 2002/11/29 02:19:21 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_us_exam_meet%ROWTYPE;
6 new_references igs_ps_us_exam_meet%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_exam_meet_id IN NUMBER DEFAULT NULL,
12 x_exam_meet_group_id IN NUMBER DEFAULT NULL,
13 x_uoo_id IN NUMBER DEFAULT NULL,
14 x_host IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 25-MAY-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_PS_US_EXAM_MEET
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.exam_meet_id := x_exam_meet_id;
55 new_references.exam_meet_group_id := x_exam_meet_group_id;
56 new_references.uoo_id := x_uoo_id;
57 new_references.host := x_host;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : [email protected]
77 || Created On : 25-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.uoo_id
88 )
89 ) THEN
90 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
91 igs_ge_msg_stack.add;
92 app_exception.raise_exception;
93 END IF;
94
95 END check_uniqueness;
96
97
98 PROCEDURE check_parent_existance AS
99 /*
100 || Created By : [email protected]
101 || Created On : 25-MAY-2001
102 || Purpose : Checks for the existance of Parent records.
103 || Known limitations, enhancements or remarks :
104 || Change History :
105 || Who When What
106 || (reverse chronological order - newest change first)
107 */
108 BEGIN
109
110 IF (((old_references.exam_meet_group_id = new_references.exam_meet_group_id)) OR
111 ((new_references.exam_meet_group_id IS NULL))) THEN
112 NULL;
113 ELSIF NOT igs_ps_us_em_grp_pkg.get_pk_for_validation (
114 new_references.exam_meet_group_id
115 ) THEN
116 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
117 igs_ge_msg_stack.add;
118 app_exception.raise_exception;
119 END IF;
120
121 IF (((old_references.uoo_id = new_references.uoo_id)) OR
122 ((new_references.uoo_id IS NULL))) THEN
123 NULL;
124 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
125 new_references.uoo_id
126 ) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 END IF;
131
132 END check_parent_existance;
133
134
135 FUNCTION get_pk_for_validation (
136 x_exam_meet_id IN NUMBER
137 ) RETURN BOOLEAN AS
138 /*
139 || Created By : [email protected]
140 || Created On : 25-MAY-2001
141 || Purpose : Validates the Primary Key of the table.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 CURSOR cur_rowid IS
148 SELECT rowid
149 FROM igs_ps_us_exam_meet
150 WHERE exam_meet_id = x_exam_meet_id
151 FOR UPDATE NOWAIT;
152
153 lv_rowid cur_rowid%RowType;
154
155 BEGIN
156
157 OPEN cur_rowid;
158 FETCH cur_rowid INTO lv_rowid;
159 IF (cur_rowid%FOUND) THEN
160 CLOSE cur_rowid;
161 RETURN(TRUE);
162 ELSE
163 CLOSE cur_rowid;
164 RETURN(FALSE);
165 END IF;
166
167 END get_pk_for_validation;
168
169
170 FUNCTION get_uk_for_validation (
171 x_uoo_id IN NUMBER
172 ) RETURN BOOLEAN AS
173 /*
174 || Created By : [email protected]
175 || Created On : 25-MAY-2001
176 || Purpose : Validates the Unique Keys of the table.
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || (reverse chronological order - newest change first)
181 */
182 CURSOR cur_rowid IS
183 SELECT rowid
184 FROM igs_ps_us_exam_meet
185 WHERE uoo_id = x_uoo_id
186 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
187
188 lv_rowid cur_rowid%RowType;
189
190 BEGIN
191
192 OPEN cur_rowid;
193 FETCH cur_rowid INTO lv_rowid;
194 IF (cur_rowid%FOUND) THEN
195 CLOSE cur_rowid;
196 RETURN (true);
197 ELSE
198 CLOSE cur_rowid;
199 RETURN(FALSE);
200 END IF;
201
202 END get_uk_for_validation ;
203
204
205 PROCEDURE get_fk_igs_ps_us_em_grp (
206 x_exam_meet_group_id IN NUMBER
207 ) AS
208 /*
209 || Created By : [email protected]
210 || Created On : 25-MAY-2001
211 || Purpose : Validates the Foreign Keys for the table.
212 || Known limitations, enhancements or remarks :
213 || Change History :
214 || Who When What
215 || (reverse chronological order - newest change first)
216 */
217 CURSOR cur_rowid IS
218 SELECT rowid
219 FROM igs_ps_us_exam_meet
220 WHERE ((exam_meet_group_id = x_exam_meet_group_id));
221
222 lv_rowid cur_rowid%RowType;
223
224 BEGIN
225
226 OPEN cur_rowid;
227 FETCH cur_rowid INTO lv_rowid;
228 IF (cur_rowid%FOUND) THEN
229 CLOSE cur_rowid;
230 fnd_message.set_name ('IGS', 'IGS_PS_EMG_EM_FK2');
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 RETURN;
234 END IF;
235 CLOSE cur_rowid;
236
237 END get_fk_igs_ps_us_em_grp;
238
239
240 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
241 x_uoo_id IN NUMBER
242 ) AS
243 /*
244 || Created By : [email protected]
245 || Created On : 25-MAY-2001
246 || Purpose : Validates the Foreign Keys for the table.
247 || Known limitations, enhancements or remarks :
248 || Change History :
249 || Who When What
250 || (reverse chronological order - newest change first)
251 */
252 CURSOR cur_rowid IS
253 SELECT rowid
254 FROM igs_ps_us_exam_meet
255 WHERE ((uoo_id = x_uoo_id));
256
257 lv_rowid cur_rowid%RowType;
258
259 BEGIN
260
261 OPEN cur_rowid;
262 FETCH cur_rowid INTO lv_rowid;
263 IF (cur_rowid%FOUND) THEN
264 CLOSE cur_rowid;
265 fnd_message.set_name ('IGS', 'IGS_PS_UOO_EM_FK1');
266 igs_ge_msg_stack.add;
267 app_exception.raise_exception;
268 RETURN;
269 END IF;
270 CLOSE cur_rowid;
271
272 END get_ufk_igs_ps_unit_ofr_opt;
273
274
275 PROCEDURE before_dml (
276 p_action IN VARCHAR2,
277 x_rowid IN VARCHAR2 DEFAULT NULL,
278 x_exam_meet_id IN NUMBER DEFAULT NULL,
279 x_exam_meet_group_id IN NUMBER DEFAULT NULL,
280 x_uoo_id IN NUMBER DEFAULT NULL,
281 x_host IN VARCHAR2 DEFAULT NULL,
282 x_creation_date IN DATE DEFAULT NULL,
283 x_created_by IN NUMBER DEFAULT NULL,
284 x_last_update_date IN DATE DEFAULT NULL,
285 x_last_updated_by IN NUMBER DEFAULT NULL,
286 x_last_update_login IN NUMBER DEFAULT NULL
287 ) AS
288 /*
289 || Created By : [email protected]
290 || Created On : 25-MAY-2001
291 || Purpose : Initialises the columns, Checks Constraints, Calls the
292 || Trigger Handlers for the table, before any DML operation.
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 */
298 BEGIN
299
300 set_column_values (
301 p_action,
302 x_rowid,
303 x_exam_meet_id,
304 x_exam_meet_group_id,
305 x_uoo_id,
306 x_host,
307 x_creation_date,
308 x_created_by,
309 x_last_update_date,
310 x_last_updated_by,
311 x_last_update_login
312 );
313
314 IF (p_action = 'INSERT') THEN
315 -- Call all the procedures related to Before Insert.
316 IF ( get_pk_for_validation(
317 new_references.exam_meet_id
318 )
319 ) THEN
320 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
321 igs_ge_msg_stack.add;
322 app_exception.raise_exception;
323 END IF;
324 check_uniqueness;
325 check_parent_existance;
326 ELSIF (p_action = 'UPDATE') THEN
327 -- Call all the procedures related to Before Update.
328 check_uniqueness;
329 check_parent_existance;
330 ELSIF (p_action = 'VALIDATE_INSERT') THEN
331 -- Call all the procedures related to Before Insert.
332 IF ( get_pk_for_validation (
333 new_references.exam_meet_id
334 )
335 ) THEN
336 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
337 igs_ge_msg_stack.add;
338 app_exception.raise_exception;
339 END IF;
340 check_uniqueness;
341 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
342 check_uniqueness;
343 END IF;
344
345 END before_dml;
346
347
348 PROCEDURE insert_row (
349 x_rowid IN OUT NOCOPY VARCHAR2,
350 x_exam_meet_id IN OUT NOCOPY NUMBER,
351 x_exam_meet_group_id IN NUMBER,
352 x_uoo_id IN NUMBER,
353 x_host IN VARCHAR2,
354 x_mode IN VARCHAR2 DEFAULT 'R'
355 ) AS
356 /*
357 || Created By : [email protected]
358 || Created On : 25-MAY-2001
359 || Purpose : Handles the INSERT DML logic for the table.
360 || Known limitations, enhancements or remarks :
361 || Change History :
362 || Who When What
363 || (reverse chronological order - newest change first)
364 */
365 CURSOR c IS
366 SELECT rowid
367 FROM igs_ps_us_exam_meet
368 WHERE exam_meet_id = x_exam_meet_id;
369
370 x_last_update_date DATE;
371 x_last_updated_by NUMBER;
372 x_last_update_login NUMBER;
373
374 BEGIN
375
376 x_last_update_date := SYSDATE;
377 IF (x_mode = 'I') THEN
378 x_last_updated_by := 1;
379 x_last_update_login := 0;
380 ELSIF (x_mode = 'R') THEN
381 x_last_updated_by := fnd_global.user_id;
382 IF (x_last_updated_by IS NULL) THEN
383 x_last_updated_by := -1;
384 END IF;
385 x_last_update_login := fnd_global.login_id;
386 IF (x_last_update_login IS NULL) THEN
387 x_last_update_login := -1;
388 END IF;
389 ELSE
390 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
391 igs_ge_msg_stack.add;
392 app_exception.raise_exception;
393 END IF;
394
395 SELECT igs_ps_us_exam_meet_s.NEXTVAL
396 INTO x_exam_meet_id
397 FROM dual;
398
399 before_dml(
400 p_action => 'INSERT',
401 x_rowid => x_rowid,
402 x_exam_meet_id => x_exam_meet_id,
403 x_exam_meet_group_id => x_exam_meet_group_id,
404 x_uoo_id => x_uoo_id,
405 x_host => x_host,
406 x_creation_date => x_last_update_date,
407 x_created_by => x_last_updated_by,
408 x_last_update_date => x_last_update_date,
409 x_last_updated_by => x_last_updated_by,
410 x_last_update_login => x_last_update_login
411 );
412
413 INSERT INTO igs_ps_us_exam_meet (
414 exam_meet_id,
415 exam_meet_group_id,
416 uoo_id,
417 host,
418 creation_date,
419 created_by,
420 last_update_date,
421 last_updated_by,
422 last_update_login
423 ) VALUES (
424 new_references.exam_meet_id,
425 new_references.exam_meet_group_id,
426 new_references.uoo_id,
427 new_references.host,
428 x_last_update_date,
429 x_last_updated_by,
430 x_last_update_date,
431 x_last_updated_by,
432 x_last_update_login
433 );
434
435 OPEN c;
436 FETCH c INTO x_rowid;
437 IF (c%NOTFOUND) THEN
438 CLOSE c;
439 RAISE NO_DATA_FOUND;
440 END IF;
441 CLOSE c;
442
443 END insert_row;
444
445
446 PROCEDURE lock_row (
447 x_rowid IN VARCHAR2,
448 x_exam_meet_id IN NUMBER,
449 x_exam_meet_group_id IN NUMBER,
450 x_uoo_id IN NUMBER,
451 x_host IN VARCHAR2
452 ) AS
453 /*
454 || Created By : [email protected]
455 || Created On : 25-MAY-2001
456 || Purpose : Handles the LOCK mechanism for the table.
457 || Known limitations, enhancements or remarks :
458 || Change History :
459 || Who When What
460 || (reverse chronological order - newest change first)
461 */
462 CURSOR c1 IS
463 SELECT
464 exam_meet_group_id,
465 uoo_id,
466 host
467 FROM igs_ps_us_exam_meet
468 WHERE rowid = x_rowid
469 FOR UPDATE NOWAIT;
470
471 tlinfo c1%ROWTYPE;
472
473 BEGIN
474
475 OPEN c1;
476 FETCH c1 INTO tlinfo;
477 IF (c1%notfound) THEN
478 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
479 igs_ge_msg_stack.add;
480 CLOSE c1;
481 app_exception.raise_exception;
482 RETURN;
483 END IF;
484 CLOSE c1;
485
486 IF (
487 (tlinfo.exam_meet_group_id = x_exam_meet_group_id)
488 AND (tlinfo.uoo_id = x_uoo_id)
489 AND (tlinfo.host = x_host)
490 ) THEN
491 NULL;
492 ELSE
493 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
494 igs_ge_msg_stack.add;
495 app_exception.raise_exception;
496 END IF;
497
498 RETURN;
499
500 END lock_row;
501
502
503 PROCEDURE update_row (
504 x_rowid IN VARCHAR2,
505 x_exam_meet_id IN NUMBER,
506 x_exam_meet_group_id IN NUMBER,
507 x_uoo_id IN NUMBER,
508 x_host IN VARCHAR2,
509 x_mode IN VARCHAR2 DEFAULT 'R'
510 ) AS
511 /*
512 || Created By : [email protected]
513 || Created On : 25-MAY-2001
514 || Purpose : Handles the UPDATE DML logic for the table.
515 || Known limitations, enhancements or remarks :
516 || Change History :
517 || Who When What
518 || (reverse chronological order - newest change first)
519 */
520 x_last_update_date DATE ;
521 x_last_updated_by NUMBER;
522 x_last_update_login NUMBER;
523
524 BEGIN
525
526 x_last_update_date := SYSDATE;
527 IF (X_MODE = 'I') THEN
528 x_last_updated_by := 1;
529 x_last_update_login := 0;
530 ELSIF (x_mode = 'R') THEN
531 x_last_updated_by := fnd_global.user_id;
532 IF x_last_updated_by IS NULL THEN
533 x_last_updated_by := -1;
534 END IF;
535 x_last_update_login := fnd_global.login_id;
536 IF (x_last_update_login IS NULL) THEN
537 x_last_update_login := -1;
538 END IF;
539 ELSE
540 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
541 igs_ge_msg_stack.add;
542 app_exception.raise_exception;
543 END IF;
544
545 before_dml(
546 p_action => 'UPDATE',
547 x_rowid => x_rowid,
548 x_exam_meet_id => x_exam_meet_id,
549 x_exam_meet_group_id => x_exam_meet_group_id,
550 x_uoo_id => x_uoo_id,
551 x_host => x_host,
552 x_creation_date => x_last_update_date,
553 x_created_by => x_last_updated_by,
554 x_last_update_date => x_last_update_date,
555 x_last_updated_by => x_last_updated_by,
556 x_last_update_login => x_last_update_login
557 );
558
559 UPDATE igs_ps_us_exam_meet
560 SET
561 exam_meet_group_id = new_references.exam_meet_group_id,
562 uoo_id = new_references.uoo_id,
563 host = new_references.host,
564 last_update_date = x_last_update_date,
565 last_updated_by = x_last_updated_by,
566 last_update_login = x_last_update_login
567 WHERE rowid = x_rowid;
568
569 IF (SQL%NOTFOUND) THEN
570 RAISE NO_DATA_FOUND;
571 END IF;
572
573 END update_row;
574
575
576 PROCEDURE add_row (
577 x_rowid IN OUT NOCOPY VARCHAR2,
578 x_exam_meet_id IN OUT NOCOPY NUMBER,
579 x_exam_meet_group_id IN NUMBER,
580 x_uoo_id IN NUMBER,
581 x_host IN VARCHAR2,
582 x_mode IN VARCHAR2 DEFAULT 'R'
583 ) AS
584 /*
585 || Created By : [email protected]
586 || Created On : 25-MAY-2001
587 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
588 || Known limitations, enhancements or remarks :
589 || Change History :
590 || Who When What
591 || (reverse chronological order - newest change first)
592 */
593 CURSOR c1 IS
594 SELECT rowid
595 FROM igs_ps_us_exam_meet
596 WHERE exam_meet_id = x_exam_meet_id;
597
598 BEGIN
599
600 OPEN c1;
601 FETCH c1 INTO x_rowid;
602 IF (c1%NOTFOUND) THEN
603 CLOSE c1;
604
605 insert_row (
606 x_rowid,
607 x_exam_meet_id,
608 x_exam_meet_group_id,
609 x_uoo_id,
610 x_host,
611 x_mode
612 );
613 RETURN;
614 END IF;
615 CLOSE c1;
616
617 update_row (
618 x_rowid,
619 x_exam_meet_id,
620 x_exam_meet_group_id,
621 x_uoo_id,
622 x_host,
623 x_mode
624 );
625
626 END add_row;
627
628
629 PROCEDURE delete_row (
630 x_rowid IN VARCHAR2
631 ) AS
632 /*
633 || Created By : [email protected]
634 || Created On : 25-MAY-2001
635 || Purpose : Handles the DELETE DML logic for the table.
636 || Known limitations, enhancements or remarks :
637 || Change History :
638 || Who When What
639 || (reverse chronological order - newest change first)
640 */
641 BEGIN
642
643 before_dml (
644 p_action => 'DELETE',
645 x_rowid => x_rowid
646 );
647
648 DELETE FROM igs_ps_us_exam_meet
649 WHERE rowid = x_rowid;
650
651 IF (SQL%NOTFOUND) THEN
652 RAISE NO_DATA_FOUND;
653 END IF;
654
655 END delete_row;
656
657
658 END igs_ps_us_exam_meet_pkg;
[Home] [Help]