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