1 PACKAGE BODY igf_aw_fund_cat_pkg AS
2 /* $Header: IGFWI04B.pls 120.0 2005/06/01 13:41:03 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_fund_cat_all%ROWTYPE;
6 new_references igf_aw_fund_cat_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_fund_code IN VARCHAR2,
12 x_description IN VARCHAR2,
13 x_fund_type IN VARCHAR2,
14 x_fund_source IN VARCHAR2,
15 x_fed_fund_code IN VARCHAR2,
16 x_sys_fund_type IN VARCHAR2,
17 x_active IN VARCHAR2,
18 x_fcat_id IN NUMBER ,
19 x_creation_date IN DATE ,
20 x_created_by IN NUMBER ,
21 x_last_update_date IN DATE ,
22 x_last_updated_by IN NUMBER ,
23 x_last_update_login IN NUMBER ,
24 x_alt_loan_code IN VARCHAR2,
25 x_alt_rel_code IN VARCHAR2
26 ) AS
27 /*
28 || Created By : venagara
29 || Created On : 29-MAR-2001
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGF_AW_FUND_CAT_ALL
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.fund_code := x_fund_code;
61 new_references.description := x_description;
62 new_references.fund_type := x_fund_type;
63 new_references.fund_source := x_fund_source;
64 new_references.fed_fund_code := x_fed_fund_code;
65 new_references.sys_fund_type := x_sys_fund_type;
66 new_references.active := x_active;
67 new_references.fcat_id := x_fcat_id;
68 new_references.alt_loan_code := x_alt_loan_code;
69 new_references.alt_rel_code := x_alt_rel_code;
70
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82
83 END set_column_values;
84
85
86 PROCEDURE check_uniqueness AS
87 /*
88 || Created By : venagara
89 || Created On : 29-MAR-2001
90 || Purpose : Handles the Unique Constraint logic defined for the columns.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 BEGIN
97
98 IF ((get_uk_for_validation(new_references.fund_code)) OR (get_uk1_for_validation(new_references.alt_loan_code))) THEN
99 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
100 igs_ge_msg_stack.add;
101 app_exception.raise_exception;
102 END IF;
103
104 END check_uniqueness;
105
106
107 PROCEDURE check_parent_existance AS
108 /*
109 || Created By : venagara
110 || Created On : 29-MAR-2001
111 || Purpose : Checks for the existance of Parent records.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || (reverse chronological order - newest change first)
116 */
117 BEGIN
118
119 IF (((old_references.fund_type = new_references.fund_type)) OR
120 ((new_references.fund_type IS NULL))) THEN
121 NULL;
122 ELSIF NOT igf_aw_fund_type_pkg.get_uk_For_validation (
123 new_references.fund_type
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 PROCEDURE check_child_existance IS
133 /*
134 || Created By : prchandr
135 || Created On : 04-APR-2001
136 || Purpose : Checks for the existance of Child records.
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || pkpatel 04-OCT-2002 Bug NO: 2600842
141 || Added the call igs_pe_fund_excl_pkg.get_ufk_igf_aw_fund_cat
142 || nalkumar 14-NOV-2002 Bug NO: 2658550
143 || Added the call igs_pr_ou_fnd_pkg.get_fk_igf_aw_fund_cat and
144 || igs_pr_stdnt_pr_fnd_pkg.get_fk_igf_aw_fund_cat. As per FA110 PR Enh.
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149 igf_aw_fund_excl_pkg.get_ufk_igf_aw_fund_cat (
150 old_references.fund_code
151 );
152
153 igf_aw_fund_incl_pkg.get_ufk_igf_aw_fund_cat (
154 old_references.fund_code
155 );
156
157 igf_aw_fund_mast_pkg.get_ufk_igf_aw_fund_cat (
158 old_references.fund_code ,
159 old_references.org_id
160 );
161
162 igs_pe_fund_excl_pkg.get_ufk_igf_aw_fund_cat (
163 old_references.fund_code
164 );
165
166 igs_pr_ou_fnd_pkg.get_fk_igf_aw_fund_cat (
167 old_references.fund_code
168 );
169 igs_pr_stdnt_pr_fnd_pkg.get_fk_igf_aw_fund_cat (
170 old_references.fund_code
171 );
172 END check_child_existance;
173
174 PROCEDURE check_uk_child_existance IS
175 /*
176 || Created By : venagara
177 || Created On : 29-MAR-2001
178 || Purpose : Checks for the existance of Child records based on Unique Keys of this table.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 BEGIN
185
186
187 IF (((old_references.fund_code = new_references.fund_code)) OR
188 ((old_references.fund_code IS NULL))) THEN
189 NULL;
190 ELSE igf_aw_fund_excl_pkg.get_ufk_igf_aw_fund_cat (
191 old_references.fund_code
192 );
193 END IF;
194
195 IF (((old_references.fund_code = new_references.fund_code)) OR
196 ((old_references.fund_code IS NULL))) THEN
197 NULL;
198 ELSE igf_aw_fund_incl_pkg.get_ufk_igf_aw_fund_cat (
199 old_references.fund_code
200 );
201 END IF;
202
203 IF (((old_references.fund_code = new_references.fund_code)) OR
204 ((old_references.fund_code IS NULL))) THEN
205 NULL;
206 ELSE igf_aw_fund_mast_pkg.get_ufk_igf_aw_fund_cat (
207 old_references.fund_code ,
208 old_references.org_id
209 );
210 END IF;
211
212 END check_uk_child_existance;
213
214
215 FUNCTION get_pk_for_validation (
216 x_fcat_id IN NUMBER
217 ) RETURN BOOLEAN AS
218 /*
219 || Created By : venagara
220 || Created On : 29-MAR-2001
221 || Purpose : Validates the Primary Key of the table.
222 || Known limitations, enhancements or remarks :
223 || Change History :
224 || Who When What
225 || (reverse chronological order - newest change first)
226 */
227 CURSOR cur_rowid IS
228 SELECT rowid
229 FROM igf_aw_fund_cat_all
230 WHERE fcat_id = x_fcat_id
231 FOR UPDATE NOWAIT;
232
233 lv_rowid cur_rowid%RowType;
234
235 BEGIN
236
237 OPEN cur_rowid;
238 FETCH cur_rowid INTO lv_rowid;
239 IF (cur_rowid%FOUND) THEN
240 CLOSE cur_rowid;
241 RETURN(TRUE);
242 ELSE
243 CLOSE cur_rowid;
244 RETURN(FALSE);
245 END IF;
246
247 END get_pk_for_validation;
248
249
250 FUNCTION get_uk_for_validation (
251 x_fund_code IN VARCHAR2
252 ) RETURN BOOLEAN AS
253 /*
254 || Created By : venagara
255 || Created On : 29-MAR-2001
256 || Purpose : Validates the Unique Keys of the table.
257 || Known limitations, enhancements or remarks :
258 || Change History :
259 || Who When What
260 || (reverse chronological order - newest change first)
261 */
262
263 l_org_id igf_aw_fund_cat_all.org_id%TYPE := igf_aw_gen.get_org_id;
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM igf_aw_fund_cat_all
267 WHERE fund_code = x_fund_code
268 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
269 AND NVL(org_id,NVL(l_org_id,-99))=NVL(l_org_id,-99)
270 FOR UPDATE NOWAIT;
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 OPEN cur_rowid;
277 FETCH cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 CLOSE cur_rowid;
280 RETURN (TRUE);
281 ELSE
282 CLOSE cur_rowid;
283 RETURN(FALSE);
284 END IF;
285
286 END get_uk_for_validation ;
287
288 FUNCTION get_uk1_for_validation (
289 x_alt_loan_code IN VARCHAR2
290 ) RETURN BOOLEAN AS
291 /*
292 || Created By : venagara
293 || Created On : 29-MAR-2001
294 || Purpose : Validates the Unique Keys of the table.
295 || Known limitations, enhancements or remarks :
296 || Change History :
297 || Who When What
298 || bkkumar 02-apr-04 FACR116 - Added the validation for checking the
299 || uniqueness of the alt_loan_code column.
300 || (reverse chronological order - newest change first)
301 */
302
303 l_org_id igf_aw_fund_cat_all.org_id%TYPE := igf_aw_gen.get_org_id;
304 CURSOR cur_rowid IS
305 SELECT rowid
306 FROM igf_aw_fund_cat_all
307 WHERE (NVL(alt_loan_code,'*') = NVL(x_alt_loan_code,'**')
308 AND ((l_rowid IS NULL) OR (rowid <> l_rowid)))
309 FOR UPDATE NOWAIT;
310
311 lv_rowid cur_rowid%RowType;
312
313 BEGIN
314
315 OPEN cur_rowid;
316 FETCH cur_rowid INTO lv_rowid;
317 IF (cur_rowid%FOUND) THEN
318 CLOSE cur_rowid;
319 RETURN (TRUE);
320 ELSE
321 CLOSE cur_rowid;
322 RETURN(FALSE);
323 END IF;
324
325 END get_uk1_for_validation ;
326
327 PROCEDURE get_fk_igf_sl_cl_recipient (
328 x_relationship_cd IN VARCHAR2
329 ) AS
330 /*
331 || Created By : bkkumar
332 || Created On : 10-APR-2004
333 || Purpose : Validates the Foreign Keys for the table.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 CURSOR cur_rowid IS
340 SELECT rowid
341 FROM igf_aw_fund_cat_all
342 WHERE NVL(alt_rel_code,'*') = x_relationship_cd;
343
344 lv_rowid cur_rowid%RowType;
345
346 BEGIN
347
348 OPEN cur_rowid;
349 FETCH cur_rowid INTO lv_rowid;
350 IF (cur_rowid%FOUND) THEN
351 CLOSE cur_rowid;
352 fnd_message.set_name ('IGF','IGF_AW_FUND_CAT_FK');
353 igs_ge_msg_stack.add;
354 app_exception.raise_exception;
355 RETURN;
356 END IF;
357 CLOSE cur_rowid;
358
359 END get_fk_igf_sl_cl_recipient;
360
361 PROCEDURE get_ufk_igf_aw_fund_type (
362 x_fund_type IN VARCHAR2
363 ) AS
364 /*
365 || Created By : venagara
366 || Created On : 29-MAR-2001
367 || Purpose : Validates the Foreign Keys for the table.
368 || Known limitations, enhancements or remarks :
369 || Change History :
370 || Who When What
371 || (reverse chronological order - newest change first)
372 */
373
374 l_org_id igf_aw_fund_cat_all.org_id%TYPE := igf_aw_gen.get_org_id;
375 CURSOR cur_rowid IS
376 SELECT rowid
377 FROM igf_aw_fund_cat_all
378 WHERE ((fund_type = x_fund_type))
379 AND NVL(org_id,NVL(l_org_id,-99))=NVL(l_org_id,-99);
380
381 lv_rowid cur_rowid%RowType;
382
383 BEGIN
384
385 OPEN cur_rowid;
386 FETCH cur_rowid INTO lv_rowid;
387 IF (cur_rowid%FOUND) THEN
388 CLOSE cur_rowid;
392 RETURN;
389 fnd_message.set_name ('IGF', 'IGF_AW_FCAT_FT_FK');
390 igs_ge_msg_stack.add;
391 app_exception.raise_exception;
393 END IF;
394 CLOSE cur_rowid;
395
396 END get_ufk_igf_aw_fund_type;
397
398
399 PROCEDURE before_dml (
400 p_action IN VARCHAR2,
401 x_rowid IN VARCHAR2,
402 x_fund_code IN VARCHAR2,
403 x_description IN VARCHAR2,
404 x_fund_type IN VARCHAR2,
405 x_fund_source IN VARCHAR2,
406 x_fed_fund_code IN VARCHAR2,
407 x_sys_fund_type IN VARCHAR2,
408 x_active IN VARCHAR2,
409 x_fcat_id IN NUMBER ,
410 x_creation_date IN DATE ,
411 x_created_by IN NUMBER ,
412 x_last_update_date IN DATE ,
413 x_last_updated_by IN NUMBER ,
414 x_last_update_login IN NUMBER ,
415 x_alt_loan_code IN VARCHAR2,
416 x_alt_rel_code IN VARCHAR2
417 ) AS
418 /*
419 || Created By : venagara
420 || Created On : 29-MAR-2001
421 || Purpose : Initialises the columns, Checks Constraints, Calls the
422 || Trigger Handlers for the table, before any DML operation.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || (reverse chronological order - newest change first)
427 */
428 BEGIN
429
430 set_column_values (
431 p_action,
432 x_rowid,
433 x_fund_code,
434 x_description,
435 x_fund_type,
436 x_fund_source,
437 x_fed_fund_code,
438 x_sys_fund_type,
439 x_active,
440 x_fcat_id,
441 x_creation_date,
442 x_created_by,
443 x_last_update_date,
444 x_last_updated_by,
445 x_last_update_login ,
446 x_alt_loan_code,
447 x_alt_rel_code
448 );
449
450 IF (p_action = 'INSERT') THEN
451 -- Call all the procedures related to Before Insert.
452 IF ( get_pk_for_validation(
453 new_references.fcat_id
454 )
455 ) THEN
456 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460 check_uniqueness;
461 check_parent_existance;
462 ELSIF (p_action = 'UPDATE') THEN
463 -- Call all the procedures related to Before Update.
464 check_uniqueness;
465 check_parent_existance;
466 check_uk_child_existance;
467 ELSIF (p_action = 'VALIDATE_INSERT') THEN
468 -- Call all the procedures related to Before Insert.
469 IF ( get_pk_for_validation (
470 new_references.fcat_id
471 )
472 ) THEN
473 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
474 igs_ge_msg_stack.add;
475 app_exception.raise_exception;
476 END IF;
477 check_uniqueness;
478 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
479 check_uniqueness;
480 check_uk_child_existance;
481
482 ELSIF (p_action = 'DELETE') THEN
483 check_child_existance;
484
485 ELSIF (p_action = 'VALIDATE_DELETE') THEN
486 check_child_existance;
487 END IF;
488
489 END before_dml;
490
491
492 PROCEDURE insert_row (
493 x_rowid IN OUT NOCOPY VARCHAR2,
494 x_fund_code IN VARCHAR2,
495 x_description IN VARCHAR2,
496 x_fund_type IN VARCHAR2,
497 x_fund_source IN VARCHAR2,
498 x_fed_fund_code IN VARCHAR2,
499 x_sys_fund_type IN VARCHAR2,
500 x_active IN VARCHAR2,
501 x_fcat_id IN OUT NOCOPY NUMBER,
502 x_mode IN VARCHAR2,
503 x_alt_loan_code IN VARCHAR2,
504 x_alt_rel_code IN VARCHAR2
505 ) AS
506 /*
507 || Created By : venagara
508 || Created On : 29-MAR-2001
509 || Purpose : Handles the INSERT DML logic for the table.
510 || Known limitations, enhancements or remarks :
511 || Change History :
512 || Who When What
513 || (reverse chronological order - newest change first)
514 */
515 CURSOR c IS
516 SELECT rowid
517 FROM igf_aw_fund_cat_all
518 WHERE fcat_id = x_fcat_id;
519
520 x_last_update_date DATE;
521 x_last_updated_by NUMBER;
522 x_last_update_login NUMBER;
523
524 l_org_id igf_aw_fund_cat_all.org_id%TYPE := igf_aw_gen.get_org_id;
525
526 BEGIN
530 x_last_updated_by := 1;
527
528 x_last_update_date := SYSDATE;
529 IF (x_mode = 'I') THEN
531 x_last_update_login := 0;
532 ELSIF (x_mode = 'R') THEN
533 x_last_updated_by := fnd_global.user_id;
534 IF (x_last_updated_by IS NULL) THEN
535 x_last_updated_by := -1;
536 END IF;
537 x_last_update_login := fnd_global.login_id;
538 IF (x_last_update_login IS NULL) THEN
539 x_last_update_login := -1;
540 END IF;
541 ELSE
542 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
543 igs_ge_msg_stack.add;
544 app_exception.raise_exception;
545 END IF;
546
547 SELECT igf_aw_fund_cat_all_s.nextval INTO x_fcat_id FROM dual;
548
549 before_dml(
550 p_action => 'INSERT',
551 x_rowid => x_rowid,
552 x_fund_code => x_fund_code,
553 x_description => x_description,
554 x_fund_type => x_fund_type,
555 x_fund_source => x_fund_source,
556 x_fed_fund_code => x_fed_fund_code,
557 x_sys_fund_type => x_sys_fund_type,
558 x_active => x_active,
559 x_fcat_id => x_fcat_id,
560 x_creation_date => x_last_update_date,
561 x_created_by => x_last_updated_by,
562 x_last_update_date => x_last_update_date,
563 x_last_updated_by => x_last_updated_by,
564 x_last_update_login => x_last_update_login,
565 x_alt_loan_code => x_alt_loan_code ,
566 x_alt_rel_code => x_alt_rel_code
567 );
568 INSERT INTO igf_aw_fund_cat_all (
569 fund_code,
570 description,
571 fund_type,
572 fund_source,
573 fed_fund_code,
574 sys_fund_type,
575 active,
576 fcat_id,
577 creation_date,
578 created_by,
579 last_update_date,
580 last_updated_by,
581 last_update_login,
582 org_id,
583 alt_loan_code,
584 alt_rel_code
585 ) VALUES (
586 new_references.fund_code,
587 new_references.description,
588 new_references.fund_type,
589 new_references.fund_source,
590 new_references.fed_fund_code,
591 new_references.sys_fund_type,
592 new_references.active,
593 new_references.fcat_id,
594 x_last_update_date,
595 x_last_updated_by,
596 x_last_update_date,
597 x_last_updated_by,
598 x_last_update_login,
599 l_org_id ,
600 new_references.alt_loan_code,
601 new_references.alt_rel_code
602 );
603
604 OPEN c;
605 FETCH c INTO x_rowid;
606 IF (c%NOTFOUND) THEN
607 CLOSE c;
608 RAISE NO_DATA_FOUND;
609 END IF;
610 CLOSE c;
611
612 END insert_row;
613
614
615 PROCEDURE lock_row (
616 x_rowid IN VARCHAR2,
617 x_fund_code IN VARCHAR2,
618 x_description IN VARCHAR2,
619 x_fund_type IN VARCHAR2,
620 x_fund_source IN VARCHAR2,
621 x_fed_fund_code IN VARCHAR2,
622 x_sys_fund_type IN VARCHAR2,
623 x_active IN VARCHAR2,
624 x_fcat_id IN NUMBER,
625 x_alt_loan_code IN VARCHAR2,
626 x_alt_rel_code IN VARCHAR2
627 ) AS
628 /*
629 || Created By : venagara
630 || Created On : 29-MAR-2001
631 || Purpose : Handles the LOCK mechanism for the table.
632 || Known limitations, enhancements or remarks :
633 || Change History :
634 || Who When What
635 || (reverse chronological order - newest change first)
636 */
637 CURSOR c1 IS
638 SELECT
639 fund_code,
640 description,
641 fund_type,
642 fund_source,
643 fed_fund_code,
644 sys_fund_type,
645 active,
646 alt_loan_code,
647 alt_rel_code
648 FROM igf_aw_fund_cat
649 WHERE rowid = x_rowid
650 FOR UPDATE NOWAIT;
651
652 tlinfo c1%ROWTYPE;
653
654 BEGIN
655
656 OPEN c1;
657 FETCH c1 INTO tlinfo;
658 IF (c1%notfound) THEN
659 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
660 igs_ge_msg_stack.add;
661 CLOSE c1;
662 app_exception.raise_exception;
663 RETURN;
664 END IF;
665 CLOSE c1;
666
667 IF (
668 (tlinfo.fund_code = x_fund_code)
669 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
670 AND (tlinfo.fund_type = x_fund_type)
674 AND (tlinfo.active = x_active)
671 AND (tlinfo.fund_source = x_fund_source)
672 AND (tlinfo.fed_fund_code = x_fed_fund_code)
673 AND (tlinfo.sys_fund_type = x_sys_fund_type)
675 AND ((tlinfo.alt_loan_code = x_alt_loan_code) OR ((tlinfo.alt_loan_code IS NULL) AND (x_alt_loan_code IS NULL)))
676 AND ((tlinfo.alt_rel_code = x_alt_rel_code) OR ((tlinfo.alt_rel_code IS NULL) AND (x_alt_rel_code IS NULL)))
677 ) THEN
678 NULL;
679 ELSE
680 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
681 igs_ge_msg_stack.add;
682 app_exception.raise_exception;
683 END IF;
684
685 RETURN;
686
687 END lock_row;
688
689
690 PROCEDURE update_row (
691 x_rowid IN VARCHAR2,
692 x_fund_code IN VARCHAR2,
693 x_description IN VARCHAR2,
694 x_fund_type IN VARCHAR2,
695 x_fund_source IN VARCHAR2,
696 x_fed_fund_code IN VARCHAR2,
697 x_sys_fund_type IN VARCHAR2,
698 x_active IN VARCHAR2,
699 x_fcat_id IN NUMBER,
700 x_mode IN VARCHAR2,
701 x_alt_loan_code IN VARCHAR2,
702 x_alt_rel_code IN VARCHAR2
703 ) AS
704 /*
705 || Created By : venagara
706 || Created On : 29-MAR-2001
707 || Purpose : Handles the UPDATE DML logic for the table.
708 || Known limitations, enhancements or remarks :
709 || Change History :
710 || Who When What
711 || (reverse chronological order - newest change first)
712 */
713 x_last_update_date DATE ;
714 x_last_updated_by NUMBER;
715 x_last_update_login NUMBER;
716
717 BEGIN
718
719 x_last_update_date := SYSDATE;
720 IF (X_MODE = 'I') THEN
721 x_last_updated_by := 1;
722 x_last_update_login := 0;
723 ELSIF (x_mode = 'R') THEN
724 x_last_updated_by := fnd_global.user_id;
725 IF x_last_updated_by IS NULL THEN
726 x_last_updated_by := -1;
727 END IF;
728 x_last_update_login := fnd_global.login_id;
729 IF (x_last_update_login IS NULL) THEN
730 x_last_update_login := -1;
731 END IF;
732 ELSE
733 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
734 igs_ge_msg_stack.add;
735 app_exception.raise_exception;
736 END IF;
737
738 before_dml(
739 p_action => 'UPDATE',
740 x_rowid => x_rowid,
741 x_fund_code => x_fund_code,
745 x_fed_fund_code => x_fed_fund_code,
742 x_description => x_description,
743 x_fund_type => x_fund_type,
744 x_fund_source => x_fund_source,
746 x_sys_fund_type => x_sys_fund_type,
747 x_active => x_active,
748 x_fcat_id => x_fcat_id,
749 x_creation_date => x_last_update_date,
750 x_created_by => x_last_updated_by,
751 x_last_update_date => x_last_update_date,
752 x_last_updated_by => x_last_updated_by,
753 x_last_update_login => x_last_update_login,
754 x_alt_loan_code => x_alt_loan_code ,
755 x_alt_rel_code => x_alt_rel_code
756 );
757
758 UPDATE igf_aw_fund_cat_all
759 SET
760 fund_code = new_references.fund_code,
761 description = new_references.description,
762 fund_type = new_references.fund_type,
763 fund_source = new_references.fund_source,
764 fed_fund_code = new_references.fed_fund_code,
765 sys_fund_type = new_references.sys_fund_type,
766 active = new_references.active,
767 last_update_date = x_last_update_date,
768 last_updated_by = x_last_updated_by,
769 last_update_login = x_last_update_login ,
770 alt_loan_code = new_references.alt_loan_code,
771 alt_rel_code = new_references.alt_rel_code
772 WHERE rowid = x_rowid;
773
774 IF (SQL%NOTFOUND) THEN
775 RAISE NO_DATA_FOUND;
776 END IF;
777
778 END update_row;
779
780
781 PROCEDURE add_row (
782 x_rowid IN OUT NOCOPY VARCHAR2,
783 x_fund_code IN VARCHAR2,
784 x_description IN VARCHAR2,
785 x_fund_type IN VARCHAR2,
786 x_fund_source IN VARCHAR2,
787 x_fed_fund_code IN VARCHAR2,
788 x_sys_fund_type IN VARCHAR2,
789 x_active IN VARCHAR2,
790 x_fcat_id IN OUT NOCOPY NUMBER,
791 x_mode IN VARCHAR2,
792 x_alt_loan_code IN VARCHAR2,
793 x_alt_rel_code IN VARCHAR2
794 ) AS
795 /*
796 || Created By : venagara
797 || Created On : 29-MAR-2001
798 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
799 || Known limitations, enhancements or remarks :
800 || Change History :
801 || Who When What
802 || (reverse chronological order - newest change first)
803 */
804 CURSOR c1 IS
805 SELECT rowid
806 FROM igf_aw_fund_cat_all
807 WHERE fcat_id = x_fcat_id;
808
809 BEGIN
810
811 OPEN c1;
812 FETCH c1 INTO x_rowid;
813 IF (c1%NOTFOUND) THEN
814 CLOSE c1;
815
816 insert_row (
817 x_rowid,
818 x_fund_code,
819 x_description,
820 x_fund_type,
821 x_fund_source,
822 x_fed_fund_code,
823 x_sys_fund_type,
824 x_active,
825 x_fcat_id,
826 x_mode ,
827 x_alt_loan_code,
828 x_alt_rel_code
829 );
830 RETURN;
831 END IF;
832 CLOSE c1;
833
834 update_row (
835 x_rowid,
836 x_fund_code,
837 x_description,
838 x_fund_type,
839 x_fund_source,
840 x_fed_fund_code,
841 x_sys_fund_type,
842 x_active,
843 x_fcat_id,
844 x_mode ,
845 x_alt_loan_code,
846 x_alt_rel_code
847 );
848
849 END add_row;
850
851
852 PROCEDURE delete_row (
853 x_rowid IN VARCHAR2
854 ) AS
855 /*
856 || Created By : venagara
857 || Created On : 29-MAR-2001
858 || Purpose : Handles the DELETE DML logic for the table.
859 || Known limitations, enhancements or remarks :
860 || Change History :
861 || Who When What
862 || (reverse chronological order - newest change first)
863 */
864 BEGIN
865
866 before_dml (
867 p_action => 'DELETE',
868 x_rowid => x_rowid
869 );
870
871 DELETE FROM igf_aw_fund_cat_all
872 WHERE rowid = x_rowid;
873
877
874 IF (SQL%NOTFOUND) THEN
875 RAISE NO_DATA_FOUND;
876 END IF;
878 END delete_row;
879
880
881 END igf_aw_fund_cat_pkg;
[Home] [Help]