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