1 PACKAGE BODY igf_sp_std_prg_pkg AS
2 /* $Header: IGFPI06B.pls 115.2 2003/03/19 08:50:40 smadathi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sp_std_prg_all%ROWTYPE;
6 new_references igf_sp_std_prg_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fee_cls_prg_id IN NUMBER DEFAULT NULL,
12 x_fee_cls_id IN NUMBER DEFAULT NULL,
13 x_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER DEFAULT NULL,
15 x_fee_percent IN NUMBER DEFAULT NULL,
16 x_max_amount IN NUMBER DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23 /*
24 || Created By :
25 || Created On : 28-DEC-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 igf_sp_std_prg_all
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.fee_cls_prg_id := x_fee_cls_prg_id;
57 new_references.fee_cls_id := x_fee_cls_id;
58 new_references.course_cd := x_course_cd;
59 new_references.version_number := x_version_number;
60 new_references.fee_percent := x_fee_percent;
61 new_references.max_amount := x_max_amount;
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 :
81 || Created On : 28-DEC-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.fee_cls_id,
92 new_references.course_cd,
93 new_references.version_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 :
107 || Created On : 28-DEC-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.fee_cls_id = new_references.fee_cls_id)) OR
117 ((new_references.fee_cls_id IS NULL))) THEN
118 NULL;
119 ELSIF NOT igf_sp_std_fc_pkg.get_pk_for_validation (
120 new_references.fee_cls_id
121 ) THEN
122 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
123 igs_ge_msg_stack.add;
124 app_exception.raise_exception;
125 END IF;
126
127 IF (((old_references.course_cd = new_references.course_cd) AND
128 (old_references.version_number = new_references.version_number)) OR
129 ((new_references.course_cd IS NULL) OR
130 (new_references.version_number IS NULL))) THEN
131 NULL;
132 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
133 new_references.course_cd,
134 new_references.version_number
135 ) THEN
136 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
137 igs_ge_msg_stack.add;
138 app_exception.raise_exception;
139 END IF;
140
141 END check_parent_existance;
142
143
144 PROCEDURE check_child_existance IS
145 /*
146 || Created By :
147 || Created On : 28-DEC-2001
148 || Purpose : Checks for the existance of Child records.
149 || Known limitations, enhancements or remarks :
150 || Change History :
151 || Who When What
152 || (reverse chronological order - newest change first)
153 */
154 BEGIN
155
156 igf_sp_std_unit_pkg.get_fk_igf_sp_std_prg (
157 old_references.fee_cls_prg_id
158 );
159
160 END check_child_existance;
161
162
163 FUNCTION get_pk_for_validation (
164 x_fee_cls_prg_id IN NUMBER
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By :
168 || Created On : 28-DEC-2001
169 || Purpose : Validates the Primary Key of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM igf_sp_std_prg_all
178 WHERE fee_cls_prg_id = x_fee_cls_prg_id
179 FOR UPDATE NOWAIT;
180
181 lv_rowid cur_rowid%RowType;
182
183 BEGIN
184
185 OPEN cur_rowid;
186 FETCH cur_rowid INTO lv_rowid;
187 IF (cur_rowid%FOUND) THEN
188 CLOSE cur_rowid;
189 RETURN(TRUE);
190 ELSE
191 CLOSE cur_rowid;
192 RETURN(FALSE);
193 END IF;
194
195 END get_pk_for_validation;
196
197
198 FUNCTION get_uk_for_validation (
199 x_fee_cls_id IN NUMBER,
200 x_course_cd IN VARCHAR2,
201 x_version_number IN NUMBER
202 ) RETURN BOOLEAN AS
203 /*
204 || Created By :
205 || Created On : 28-DEC-2001
206 || Purpose : Validates the Unique Keys of the table.
207 || Known limitations, enhancements or remarks :
208 || Change History :
209 || Who When What
210 || (reverse chronological order - newest change first)
211 */
212 CURSOR cur_rowid IS
213 SELECT rowid
214 FROM igf_sp_std_prg_all
215 WHERE fee_cls_id = x_fee_cls_id
216 AND course_cd = x_course_cd
217 AND version_number = x_version_number
218 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 OPEN cur_rowid;
225 FETCH cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 CLOSE cur_rowid;
228 RETURN (true);
229 ELSE
230 CLOSE cur_rowid;
231 RETURN(FALSE);
232 END IF;
233
234 END get_uk_for_validation ;
235
236
237 PROCEDURE get_fk_igf_sp_std_fc (
238 x_fee_cls_id IN NUMBER
239 ) AS
240 /*
241 || Created By :
242 || Created On : 28-DEC-2001
243 || Purpose : Validates the Foreign Keys for the table.
244 || Known limitations, enhancements or remarks :
245 || Change History :
246 || Who When What
247 || (reverse chronological order - newest change first)
248 */
249 CURSOR cur_rowid IS
250 SELECT rowid
251 FROM igf_sp_std_prg_all
252 WHERE ((fee_cls_id = x_fee_cls_id));
253
254 lv_rowid cur_rowid%RowType;
255
256 BEGIN
257
258 OPEN cur_rowid;
259 FETCH cur_rowid INTO lv_rowid;
260 IF (cur_rowid%FOUND) THEN
261 CLOSE cur_rowid;
262 fnd_message.set_name ('IGF', 'IGF_SP_STDP_SDFC_FK');
263 igs_ge_msg_stack.add;
264 app_exception.raise_exception;
265 RETURN;
266 END IF;
267 CLOSE cur_rowid;
268
269 END get_fk_igf_sp_std_fc;
270
271 PROCEDURE get_fk_igs_ps_ver (
272 x_course_cd IN VARCHAR2,
273 x_version_number IN NUMBER
274 ) AS
275 /*
276 || Created By : Rakesh Singh
277 || Created On : 28-DEC-2001
278 || Purpose : Validates the Foreign Keys for the table.
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM igf_sp_std_prg_all
287 WHERE ( course_cd = x_course_cd
288 AND version_number = x_version_number );
289
290 lv_rowid cur_rowid%RowType;
291
292 BEGIN
293
294 OPEN cur_rowid;
295 FETCH cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 CLOSE cur_rowid;
298 fnd_message.set_name ('IGF', 'IGF_SP_STDP_CRV_FK');
299 igs_ge_msg_stack.add;
300 app_exception.raise_exception;
301 RETURN;
302 END IF;
303 CLOSE cur_rowid;
304
305 END get_fk_igs_ps_ver;
306
307
308 PROCEDURE before_dml (
309 p_action IN VARCHAR2,
310 x_rowid IN VARCHAR2 DEFAULT NULL,
311 x_fee_cls_prg_id IN NUMBER DEFAULT NULL,
312 x_fee_cls_id IN NUMBER DEFAULT NULL,
313 x_course_cd IN VARCHAR2 DEFAULT NULL,
314 x_version_number IN NUMBER DEFAULT NULL,
315 x_fee_percent IN NUMBER DEFAULT NULL,
316 x_max_amount IN NUMBER DEFAULT NULL,
317 x_creation_date IN DATE DEFAULT NULL,
318 x_created_by IN NUMBER DEFAULT NULL,
319 x_last_update_date IN DATE DEFAULT NULL,
320 x_last_updated_by IN NUMBER DEFAULT NULL,
321 x_last_update_login IN NUMBER DEFAULT NULL
322 ) AS
323 /*
324 || Created By :
325 || Created On : 28-DEC-2001
326 || Purpose : Initialises the columns, Checks Constraints, Calls the
327 || Trigger Handlers for the table, before any DML operation.
328 || Known limitations, enhancements or remarks :
329 || Change History :
330 || Who When What
331 || (reverse chronological order - newest change first)
332 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null.
333 */
334 BEGIN
335
336 set_column_values (
337 p_action,
338 x_rowid,
339 x_fee_cls_prg_id,
340 x_fee_cls_id,
341 x_course_cd,
342 x_version_number,
343 x_fee_percent,
344 x_max_amount,
345 x_creation_date,
346 x_created_by,
347 x_last_update_date,
348 x_last_updated_by,
349 x_last_update_login
350 );
351
352 IF (p_action = 'INSERT') THEN
353 -- Call all the procedures related to Before Insert.
354 IF ( get_pk_for_validation(
355 new_references.fee_cls_prg_id
356 )
357 ) THEN
358 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
359 igs_ge_msg_stack.add;
360 app_exception.raise_exception;
361 END IF;
362 check_uniqueness;
363 check_parent_existance;
364 ELSIF (p_action = 'UPDATE') THEN
365 -- Call all the procedures related to Before Update.
366 check_uniqueness;
367 check_parent_existance;
368 ELSIF (p_action = 'DELETE') THEN
369 -- Call all the procedures related to Before Delete.
370 check_child_existance;
371 ELSIF (p_action = 'VALIDATE_INSERT') THEN
372 -- Call all the procedures related to Before Insert.
373 IF ( get_pk_for_validation (
374 new_references.fee_cls_prg_id
375 )
376 ) THEN
377 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
378 igs_ge_msg_stack.add;
379 app_exception.raise_exception;
380 END IF;
381 check_uniqueness;
382 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
383 check_uniqueness;
384 ELSIF (p_action = 'VALIDATE_DELETE') THEN
385 check_child_existance;
386 END IF;
387 l_rowid := NULL;
388 END before_dml;
389
390
391 PROCEDURE insert_row (
392 x_rowid IN OUT NOCOPY VARCHAR2,
393 x_fee_cls_prg_id IN OUT NOCOPY NUMBER,
394 x_fee_cls_id IN NUMBER,
395 x_course_cd IN VARCHAR2,
396 x_version_number IN NUMBER,
397 x_fee_percent IN NUMBER,
398 x_max_amount IN NUMBER,
399 x_mode IN VARCHAR2 DEFAULT 'R'
400 ) AS
401 /*
402 || Created By :
403 || Created On : 28-DEC-2001
404 || Purpose : Handles the INSERT DML logic for the table.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 CURSOR c IS
411 SELECT rowid
412 FROM igf_sp_std_prg_all
413 WHERE fee_cls_prg_id = x_fee_cls_prg_id;
414
415 x_last_update_date DATE;
416 x_last_updated_by NUMBER;
417 x_last_update_login NUMBER;
418
419 BEGIN
420
421 x_last_update_date := SYSDATE;
422 IF (x_mode = 'I') THEN
423 x_last_updated_by := 1;
424 x_last_update_login := 0;
425 ELSIF (x_mode = 'R') THEN
426 x_last_updated_by := fnd_global.user_id;
427 IF (x_last_updated_by IS NULL) THEN
428 x_last_updated_by := -1;
429 END IF;
430 x_last_update_login := fnd_global.login_id;
431 IF (x_last_update_login IS NULL) THEN
432 x_last_update_login := -1;
433 END IF;
434 ELSE
435 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
436 igs_ge_msg_stack.add;
437 app_exception.raise_exception;
438 END IF;
439
440 SELECT igf_sp_std_prg_s.NEXTVAL
441 INTO x_fee_cls_prg_id
442 FROM dual;
443
444 new_references.org_id := igs_ge_gen_003.get_org_id;
445
446 before_dml(
447 p_action => 'INSERT',
448 x_rowid => x_rowid,
449 x_fee_cls_prg_id => x_fee_cls_prg_id,
450 x_fee_cls_id => x_fee_cls_id,
451 x_course_cd => x_course_cd,
452 x_version_number => x_version_number,
453 x_fee_percent => x_fee_percent,
454 x_max_amount => x_max_amount,
455 x_creation_date => x_last_update_date,
456 x_created_by => x_last_updated_by,
457 x_last_update_date => x_last_update_date,
458 x_last_updated_by => x_last_updated_by,
459 x_last_update_login => x_last_update_login
460 );
461
462 INSERT INTO igf_sp_std_prg_all (
463 fee_cls_prg_id,
464 fee_cls_id,
465 course_cd,
466 version_number,
467 fee_percent,
468 max_amount,
469 org_id,
470 creation_date,
471 created_by,
472 last_update_date,
473 last_updated_by,
474 last_update_login
475 ) VALUES (
476 new_references.fee_cls_prg_id,
477 new_references.fee_cls_id,
478 new_references.course_cd,
479 new_references.version_number,
480 new_references.fee_percent,
481 new_references.max_amount,
482 new_references.org_id,
483 x_last_update_date,
484 x_last_updated_by,
485 x_last_update_date,
486 x_last_updated_by,
487 x_last_update_login
488 );
489
490 OPEN c;
491 FETCH c INTO x_rowid;
492 IF (c%NOTFOUND) THEN
493 CLOSE c;
494 RAISE NO_DATA_FOUND;
495 END IF;
496 CLOSE c;
497
498 END insert_row;
499
500
501 PROCEDURE lock_row (
502 x_rowid IN VARCHAR2,
503 x_fee_cls_prg_id IN NUMBER,
504 x_fee_cls_id IN NUMBER,
505 x_course_cd IN VARCHAR2,
506 x_version_number IN NUMBER,
507 x_fee_percent IN NUMBER,
508 x_max_amount IN NUMBER
509 ) AS
510 /*
511 || Created By :
512 || Created On : 28-DEC-2001
513 || Purpose : Handles the LOCK mechanism for the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || (reverse chronological order - newest change first)
518 */
519 CURSOR c1 IS
520 SELECT
521 fee_cls_id,
522 course_cd,
523 version_number,
524 fee_percent,
525 max_amount
526 FROM igf_sp_std_prg_all
527 WHERE rowid = x_rowid
528 FOR UPDATE NOWAIT;
529
530 tlinfo c1%ROWTYPE;
531
532 BEGIN
533
534 OPEN c1;
535 FETCH c1 INTO tlinfo;
536 IF (c1%notfound) THEN
537 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
538 igs_ge_msg_stack.add;
539 CLOSE c1;
540 app_exception.raise_exception;
541 RETURN;
542 END IF;
543 CLOSE c1;
544
545 IF (
546 (tlinfo.fee_cls_id = x_fee_cls_id)
547 AND (tlinfo.course_cd = x_course_cd)
548 AND (tlinfo.version_number = x_version_number)
549 AND ((tlinfo.fee_percent = x_fee_percent) OR ((tlinfo.fee_percent IS NULL) AND (X_fee_percent IS NULL)))
550 AND ((tlinfo.max_amount = x_max_amount) OR ((tlinfo.max_amount IS NULL) AND (X_max_amount IS NULL)))
551 ) THEN
552 NULL;
553 ELSE
554 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
555 igs_ge_msg_stack.add;
556 app_exception.raise_exception;
557 END IF;
558
559 RETURN;
560
561 END lock_row;
562
563
564 PROCEDURE update_row (
565 x_rowid IN VARCHAR2,
566 x_fee_cls_prg_id IN NUMBER,
567 x_fee_cls_id IN NUMBER,
568 x_course_cd IN VARCHAR2,
569 x_version_number IN NUMBER,
570 x_fee_percent IN NUMBER,
571 x_max_amount IN NUMBER,
572 x_mode IN VARCHAR2 DEFAULT 'R'
573 ) AS
574 /*
575 || Created By :
576 || Created On : 28-DEC-2001
577 || Purpose : Handles the UPDATE DML logic for the table.
578 || Known limitations, enhancements or remarks :
579 || Change History :
580 || Who When What
581 || (reverse chronological order - newest change first)
582 */
583 x_last_update_date DATE ;
584 x_last_updated_by NUMBER;
585 x_last_update_login NUMBER;
586
587 BEGIN
588
589 x_last_update_date := SYSDATE;
590 IF (X_MODE = 'I') THEN
591 x_last_updated_by := 1;
592 x_last_update_login := 0;
593 ELSIF (x_mode = 'R') THEN
594 x_last_updated_by := fnd_global.user_id;
595 IF x_last_updated_by IS NULL THEN
596 x_last_updated_by := -1;
597 END IF;
598 x_last_update_login := fnd_global.login_id;
599 IF (x_last_update_login IS NULL) THEN
600 x_last_update_login := -1;
601 END IF;
602 ELSE
603 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
604 igs_ge_msg_stack.add;
605 app_exception.raise_exception;
606 END IF;
607
608 before_dml(
609 p_action => 'UPDATE',
610 x_rowid => x_rowid,
611 x_fee_cls_prg_id => x_fee_cls_prg_id,
612 x_fee_cls_id => x_fee_cls_id,
613 x_course_cd => x_course_cd,
614 x_version_number => x_version_number,
615 x_fee_percent => x_fee_percent,
616 x_max_amount => x_max_amount,
617 x_creation_date => x_last_update_date,
618 x_created_by => x_last_updated_by,
619 x_last_update_date => x_last_update_date,
620 x_last_updated_by => x_last_updated_by,
621 x_last_update_login => x_last_update_login
622 );
623
624 UPDATE igf_sp_std_prg_all
625 SET
626 fee_cls_id = new_references.fee_cls_id,
627 course_cd = new_references.course_cd,
628 version_number = new_references.version_number,
629 fee_percent = new_references.fee_percent,
630 max_amount = new_references.max_amount,
631 last_update_date = x_last_update_date,
632 last_updated_by = x_last_updated_by,
633 last_update_login = x_last_update_login
634 WHERE rowid = x_rowid;
635
636 IF (SQL%NOTFOUND) THEN
637 RAISE NO_DATA_FOUND;
638 END IF;
639
640 END update_row;
641
642
643 PROCEDURE add_row (
644 x_rowid IN OUT NOCOPY VARCHAR2,
645 x_fee_cls_prg_id IN OUT NOCOPY NUMBER,
646 x_fee_cls_id IN NUMBER,
647 x_course_cd IN VARCHAR2,
648 x_version_number IN NUMBER,
649 x_fee_percent IN NUMBER,
650 x_max_amount IN NUMBER,
651 x_mode IN VARCHAR2 DEFAULT 'R'
652 ) AS
653 /*
654 || Created By :
655 || Created On : 28-DEC-2001
656 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
657 || Known limitations, enhancements or remarks :
658 || Change History :
659 || Who When What
660 || (reverse chronological order - newest change first)
661 */
662 CURSOR c1 IS
663 SELECT rowid
664 FROM igf_sp_std_prg_all
665 WHERE fee_cls_prg_id = x_fee_cls_prg_id;
666
667 BEGIN
668
669 OPEN c1;
670 FETCH c1 INTO x_rowid;
671 IF (c1%NOTFOUND) THEN
672 CLOSE c1;
673
674 insert_row (
675 x_rowid,
676 x_fee_cls_prg_id,
677 x_fee_cls_id,
678 x_course_cd,
679 x_version_number,
680 x_fee_percent,
681 x_max_amount,
682 x_mode
683 );
684 RETURN;
685 END IF;
686 CLOSE c1;
687
688 update_row (
689 x_rowid,
690 x_fee_cls_prg_id,
691 x_fee_cls_id,
692 x_course_cd,
693 x_version_number,
694 x_fee_percent,
695 x_max_amount,
696 x_mode
697 );
698
699 END add_row;
700
701
702 PROCEDURE delete_row (
703 x_rowid IN VARCHAR2
704 ) AS
705 /*
706 || Created By :
707 || Created On : 28-DEC-2001
708 || Purpose : Handles the DELETE DML logic for the table.
709 || Known limitations, enhancements or remarks :
710 || Change History :
711 || Who When What
712 || (reverse chronological order - newest change first)
713 */
714 BEGIN
715
716 before_dml (
717 p_action => 'DELETE',
718 x_rowid => x_rowid
719 );
720
721 DELETE FROM igf_sp_std_prg_all
722 WHERE rowid = x_rowid;
723
724 IF (SQL%NOTFOUND) THEN
725 RAISE NO_DATA_FOUND;
726 END IF;
727
728 END delete_row;
729
730
731 END igf_sp_std_prg_pkg;
[Home] [Help]