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