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