1 PACKAGE BODY igf_sp_fc_pkg AS
2 /* $Header: IGFPI01B.pls 115.2 2003/03/19 08:49:40 smadathi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sp_fc_all%ROWTYPE;
6 new_references igf_sp_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_fund_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 : Rakesh Singh
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_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.fund_id := x_fund_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 : Rakesh Singh
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.fund_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 : Rakesh Singh
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.fund_id = new_references.fund_id)) OR
114 ((new_references.fund_id IS NULL))) THEN
115 NULL;
116 ELSIF NOT igf_aw_fund_mast_pkg.get_pk_for_validation (
117 new_references.fund_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 : Rakesh Singh
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_prg_pkg.get_fk_igf_sp_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 : Rakesh Singh
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_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_fund_id IN NUMBER,
195 x_fee_class IN VARCHAR2
196 ) RETURN BOOLEAN AS
197 /*
198 || Created By : Rakesh Singh
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_fc_all
209 WHERE fund_id = x_fund_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_aw_fund_mast (
231 x_fund_id IN NUMBER
232 ) AS
233 /*
234 || Created By : Rakesh Singh
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_fc_all
245 WHERE ((fund_id = x_fund_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_AW_SFCL_FMAST_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_aw_fund_mast;
263
264
265 PROCEDURE get_fk_igs_lookups_view (
266 x_fee_class IN VARCHAR2
267 ) AS
268 /*
269 || Created By : Rakesh Singh
270 || Created On : 28-DEC-2001
271 || Purpose : Validates the Foreign Keys for the table.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 CURSOR cur_rowid IS
278 SELECT rowid
279 FROM igf_sp_fc_all
280 WHERE ((fee_class = x_fee_class));
281
282 lv_rowid cur_rowid%RowType;
283
284 BEGIN
285
286 OPEN cur_rowid;
287 FETCH cur_rowid INTO lv_rowid;
288 IF (cur_rowid%FOUND) THEN
289 CLOSE cur_rowid;
290 fnd_message.set_name ('IGF', 'IGF_SP_SFCL_LKUP_FK');
291 igs_ge_msg_stack.add;
292 app_exception.raise_exception;
293 RETURN;
294 END IF;
295 CLOSE cur_rowid;
296
297 END get_fk_igs_lookups_view;
298
299
300 PROCEDURE before_dml (
301 p_action IN VARCHAR2,
302 x_rowid IN VARCHAR2 DEFAULT NULL,
303 x_fee_cls_id IN NUMBER DEFAULT NULL,
304 x_fund_id IN NUMBER DEFAULT NULL,
305 x_fee_class IN VARCHAR2 DEFAULT NULL,
306 x_fee_percent IN NUMBER DEFAULT NULL,
307 x_max_amount IN NUMBER DEFAULT NULL,
308 x_creation_date IN DATE DEFAULT NULL,
309 x_created_by IN NUMBER DEFAULT NULL,
310 x_last_update_date IN DATE DEFAULT NULL,
311 x_last_updated_by IN NUMBER DEFAULT NULL,
312 x_last_update_login IN NUMBER DEFAULT NULL
313 ) AS
314 /*
315 || Created By : Rakesh Singh
316 || Created On : 28-DEC-2001
317 || Purpose : Initialises the columns, Checks Constraints, Calls the
318 || Trigger Handlers for the table, before any DML operation.
319 || Known limitations, enhancements or remarks :
320 || Change History :
321 || Who When What
322 || (reverse chronological order - newest change first)
323 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null
324 */
325 BEGIN
326
327 set_column_values (
328 p_action,
329 x_rowid,
330 x_fee_cls_id,
331 x_fund_id,
332 x_fee_class,
333 x_fee_percent,
334 x_max_amount,
335 x_creation_date,
336 x_created_by,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_login
340 );
341
342 IF (p_action = 'INSERT') THEN
343 -- Call all the procedures related to Before Insert.
344 IF ( get_pk_for_validation(
345 new_references.fee_cls_id
346 )
347 ) THEN
348 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 check_uniqueness;
353 check_parent_existance;
354 ELSIF (p_action = 'UPDATE') THEN
355 -- Call all the procedures related to Before Update.
356 check_uniqueness;
357 check_parent_existance;
358 ELSIF (p_action = 'DELETE') THEN
359 -- Call all the procedures related to Before Delete.
360 check_child_existance;
361 ELSIF (p_action = 'VALIDATE_INSERT') THEN
362 -- Call all the procedures related to Before Insert.
363 IF ( get_pk_for_validation (
364 new_references.fee_cls_id
365 )
366 ) THEN
367 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
368 igs_ge_msg_stack.add;
369 app_exception.raise_exception;
370 END IF;
371 check_uniqueness;
372 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
373 check_uniqueness;
374 ELSIF (p_action = 'VALIDATE_DELETE') THEN
375 check_child_existance;
376 END IF;
377 l_rowid := NULL;
378 END before_dml;
379
380
381 PROCEDURE insert_row (
382 x_rowid IN OUT NOCOPY VARCHAR2,
383 x_fee_cls_id IN OUT NOCOPY NUMBER,
384 x_fund_id IN NUMBER,
385 x_fee_class IN VARCHAR2,
386 x_fee_percent IN NUMBER,
387 x_max_amount IN NUMBER,
388 x_mode IN VARCHAR2 DEFAULT 'R'
389 ) AS
390 /*
391 || Created By : Rakesh Singh
392 || Created On : 28-DEC-2001
393 || Purpose : Handles the INSERT DML logic for the table.
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 || (reverse chronological order - newest change first)
398 */
399 CURSOR c IS
400 SELECT rowid
401 FROM igf_sp_fc_all
402 WHERE fee_cls_id = x_fee_cls_id;
403
404 x_last_update_date DATE;
405 x_last_updated_by NUMBER;
406 x_last_update_login NUMBER;
407
408 BEGIN
409
410 x_last_update_date := SYSDATE;
411 IF (x_mode = 'I') THEN
412 x_last_updated_by := 1;
413 x_last_update_login := 0;
414 ELSIF (x_mode = 'R') THEN
415 x_last_updated_by := fnd_global.user_id;
416 IF (x_last_updated_by IS NULL) THEN
417 x_last_updated_by := -1;
418 END IF;
419 x_last_update_login := fnd_global.login_id;
420 IF (x_last_update_login IS NULL) THEN
421 x_last_update_login := -1;
422 END IF;
423 ELSE
424 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
425 igs_ge_msg_stack.add;
426 app_exception.raise_exception;
427 END IF;
428
429 SELECT igf_sp_fc_s.NEXTVAL
430 INTO x_fee_cls_id
431 FROM dual;
435 before_dml(
432
433 new_references.org_id := igs_ge_gen_003.get_org_id;
434
436 p_action => 'INSERT',
437 x_rowid => x_rowid,
438 x_fee_cls_id => x_fee_cls_id,
439 x_fund_id => x_fund_id,
440 x_fee_class => x_fee_class,
441 x_fee_percent => x_fee_percent,
442 x_max_amount => x_max_amount,
443 x_creation_date => x_last_update_date,
444 x_created_by => x_last_updated_by,
445 x_last_update_date => x_last_update_date,
446 x_last_updated_by => x_last_updated_by,
447 x_last_update_login => x_last_update_login
448 );
449
450 INSERT INTO igf_sp_fc_all (
451 fee_cls_id,
452 fund_id,
453 fee_class,
454 fee_percent,
455 max_amount,
456 org_id,
457 creation_date,
458 created_by,
459 last_update_date,
460 last_updated_by,
461 last_update_login
462 ) VALUES (
463 new_references.fee_cls_id,
464 new_references.fund_id,
465 new_references.fee_class,
466 new_references.fee_percent,
467 new_references.max_amount,
468 new_references.org_id,
469 x_last_update_date,
470 x_last_updated_by,
471 x_last_update_date,
472 x_last_updated_by,
473 x_last_update_login
474 );
475
476 OPEN c;
477 FETCH c INTO x_rowid;
478 IF (c%NOTFOUND) THEN
479 CLOSE c;
480 RAISE NO_DATA_FOUND;
481 END IF;
482 CLOSE c;
483
484 END insert_row;
485
486
487 PROCEDURE lock_row (
488 x_rowid IN VARCHAR2,
489 x_fee_cls_id IN NUMBER,
490 x_fund_id IN NUMBER,
491 x_fee_class IN VARCHAR2,
492 x_fee_percent IN NUMBER,
493 x_max_amount IN NUMBER
494 ) AS
495 /*
496 || Created By : Rakesh Singh
497 || Created On : 28-DEC-2001
498 || Purpose : Handles the LOCK mechanism for the table.
499 || Known limitations, enhancements or remarks :
500 || Change History :
501 || Who When What
502 || (reverse chronological order - newest change first)
503 */
504 CURSOR c1 IS
505 SELECT
506 fund_id,
507 fee_class,
508 fee_percent,
509 max_amount
510 FROM igf_sp_fc_all
511 WHERE rowid = x_rowid
512 FOR UPDATE NOWAIT;
513
514 tlinfo c1%ROWTYPE;
515
516 BEGIN
517
518 OPEN c1;
519 FETCH c1 INTO tlinfo;
520 IF (c1%notfound) THEN
521 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
522 igs_ge_msg_stack.add;
523 CLOSE c1;
524 app_exception.raise_exception;
525 RETURN;
526 END IF;
527 CLOSE c1;
528
529 IF (
530 (tlinfo.fund_id = x_fund_id)
531 AND (tlinfo.fee_class = x_fee_class)
532 AND ((tlinfo.fee_percent = x_fee_percent) OR ((tlinfo.fee_percent IS NULL) AND (X_fee_percent IS NULL)))
533 AND ((tlinfo.max_amount = x_max_amount) OR ((tlinfo.max_amount IS NULL) AND (X_max_amount IS NULL)))
534 ) THEN
535 NULL;
536 ELSE
537 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
538 igs_ge_msg_stack.add;
539 app_exception.raise_exception;
540 END IF;
541
542 RETURN;
543
544 END lock_row;
545
546
547 PROCEDURE update_row (
548 x_rowid IN VARCHAR2,
549 x_fee_cls_id IN NUMBER,
550 x_fund_id IN NUMBER,
551 x_fee_class IN VARCHAR2,
552 x_fee_percent IN NUMBER,
553 x_max_amount IN NUMBER,
554 x_mode IN VARCHAR2 DEFAULT 'R'
555 ) AS
556 /*
557 || Created By : Rakesh Singh
558 || Created On : 28-DEC-2001
559 || Purpose : Handles the UPDATE DML logic for the table.
560 || Known limitations, enhancements or remarks :
561 || Change History :
562 || Who When What
563 || (reverse chronological order - newest change first)
564 */
565 x_last_update_date DATE ;
566 x_last_updated_by NUMBER;
567 x_last_update_login NUMBER;
568
569 BEGIN
570
571 x_last_update_date := SYSDATE;
572 IF (X_MODE = 'I') THEN
573 x_last_updated_by := 1;
574 x_last_update_login := 0;
575 ELSIF (x_mode = 'R') THEN
576 x_last_updated_by := fnd_global.user_id;
577 IF x_last_updated_by IS NULL THEN
578 x_last_updated_by := -1;
579 END IF;
580 x_last_update_login := fnd_global.login_id;
581 IF (x_last_update_login IS NULL) THEN
585 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
582 x_last_update_login := -1;
583 END IF;
584 ELSE
586 igs_ge_msg_stack.add;
587 app_exception.raise_exception;
588 END IF;
589
590 before_dml(
591 p_action => 'UPDATE',
592 x_rowid => x_rowid,
593 x_fee_cls_id => x_fee_cls_id,
594 x_fund_id => x_fund_id,
595 x_fee_class => x_fee_class,
596 x_fee_percent => x_fee_percent,
597 x_max_amount => x_max_amount,
598 x_creation_date => x_last_update_date,
599 x_created_by => x_last_updated_by,
600 x_last_update_date => x_last_update_date,
601 x_last_updated_by => x_last_updated_by,
602 x_last_update_login => x_last_update_login
603 );
604
605 UPDATE igf_sp_fc_all
606 SET
607 fund_id = new_references.fund_id,
608 fee_class = new_references.fee_class,
609 fee_percent = new_references.fee_percent,
610 max_amount = new_references.max_amount,
611 last_update_date = x_last_update_date,
612 last_updated_by = x_last_updated_by,
613 last_update_login = x_last_update_login
614 WHERE rowid = x_rowid;
615
616 IF (SQL%NOTFOUND) THEN
617 RAISE NO_DATA_FOUND;
618 END IF;
619
620 END update_row;
621
622
623 PROCEDURE add_row (
624 x_rowid IN OUT NOCOPY VARCHAR2,
625 x_fee_cls_id IN OUT NOCOPY NUMBER,
626 x_fund_id IN NUMBER,
627 x_fee_class IN VARCHAR2,
628 x_fee_percent IN NUMBER,
629 x_max_amount IN NUMBER,
630 x_mode IN VARCHAR2 DEFAULT 'R'
631 ) AS
632 /*
633 || Created By : Rakesh Singh
634 || Created On : 28-DEC-2001
635 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
636 || Known limitations, enhancements or remarks :
637 || Change History :
638 || Who When What
639 || (reverse chronological order - newest change first)
640 */
641 CURSOR c1 IS
642 SELECT rowid
643 FROM igf_sp_fc_all
644 WHERE fee_cls_id = x_fee_cls_id;
645
646 BEGIN
647
648 OPEN c1;
649 FETCH c1 INTO x_rowid;
650 IF (c1%NOTFOUND) THEN
651 CLOSE c1;
652
653 insert_row (
654 x_rowid,
655 x_fee_cls_id,
656 x_fund_id,
657 x_fee_class,
658 x_fee_percent,
659 x_max_amount,
660 x_mode
661 );
662 RETURN;
663 END IF;
664 CLOSE c1;
665
666 update_row (
667 x_rowid,
668 x_fee_cls_id,
669 x_fund_id,
673 x_mode
670 x_fee_class,
671 x_fee_percent,
672 x_max_amount,
674 );
675
676 END add_row;
677
678
679 PROCEDURE delete_row (
680 x_rowid IN VARCHAR2
681 ) AS
682 /*
683 || Created By : Rakesh Singh
684 || Created On : 28-DEC-2001
685 || Purpose : Handles the DELETE DML logic for the table.
686 || Known limitations, enhancements or remarks :
687 || Change History :
688 || Who When What
689 || (reverse chronological order - newest change first)
690 */
691 BEGIN
692
693 before_dml (
694 p_action => 'DELETE',
695 x_rowid => x_rowid
696 );
697
698 DELETE FROM igf_sp_fc_all
699 WHERE rowid = x_rowid;
700
701 IF (SQL%NOTFOUND) THEN
702 RAISE NO_DATA_FOUND;
703 END IF;
704
705 END delete_row;
706
707
708 END igf_sp_fc_pkg;
[Home] [Help]