1 PACKAGE BODY igf_sl_lender_brc_pkg AS
2 /* $Header: IGFLI02B.pls 115.6 2003/09/10 05:07:51 veramach ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_lender_brc%ROWTYPE;
6 new_references igf_sl_lender_brc%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_lender_id IN VARCHAR2 DEFAULT NULL,
12 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_party_id IN NUMBER DEFAULT NULL,
15 x_enabled IN VARCHAR2 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 : prchandr
24 || Created On : 07-NOV-2000
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_SL_LENDER_BRC
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.lender_id := x_lender_id;
56 new_references.lend_non_ed_brc_id := x_lend_non_ed_brc_id;
57 new_references.description := x_description;
58 new_references.party_id := x_party_id;
59 new_references.enabled := x_enabled;
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_parent_existance AS
77 /*
78 || Created By : prchandr
79 || Created On : 07-NOV-2000
80 || Purpose : Checks for the existance of Parent records.
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 (((old_references.lender_id = new_references.lender_id)) OR
89 ((new_references.lender_id IS NULL))) THEN
90 NULL;
91 ELSIF NOT igf_sl_lender_pkg.get_pk_for_validation (
92 new_references.lender_id
93 ) THEN
94 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95 igs_ge_msg_stack.add;
96 app_exception.raise_exception;
97 END IF;
98
99 END check_parent_existance;
100
101
102 PROCEDURE check_child_existance IS
103 /*
104 || Created By : prchandr
105 || Created On : 07-NOV-2000
106 || Purpose : Checks for the existance of Child records.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 BEGIN
113
114 igf_sl_cl_recipient_pkg.get_fk_igf_sl_lender_brc (
115 old_references.lender_id,
116 old_references.lend_non_ed_brc_id
117 );
118
119 END check_child_existance;
120
121 PROCEDURE check_uniqueness AS
122 ------------------------------------------------------------------
123 --Created by : veramach, Oracle India
124 --Date created: 3-SEP-2003
125 --
126 --Purpose:
127 -- Check uniqueness of all unique key fields
128 --
129 --Known limitations/enhancements and/or remarks:
130 --
131 --Change History:
132 --Who When What
133 -------------------------------------------------------------------
134
135 BEGIN
136
137 IF ( get_uk_for_validation (
138 new_references.party_id
139 )
140 ) THEN
141 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
142 igs_ge_msg_stack.add;
143 app_exception.raise_exception;
144 END IF;
145
146 END check_uniqueness;
147
148 FUNCTION get_pk_for_validation (
149 x_lender_id IN VARCHAR2,
150 x_lend_non_ed_brc_id IN VARCHAR2
151 ) RETURN BOOLEAN AS
152 /*
153 || Created By : prchandr
154 || Created On : 07-NOV-2000
155 || Purpose : Validates the Primary Key of the table.
156 || Known limitations, enhancements or remarks :
157 || Change History :
158 || Who When What
159 || (reverse chronological order - newest change first)
160 */
161 CURSOR cur_rowid IS
162 SELECT rowid
163 FROM igf_sl_lender_brc
164 WHERE lender_id = x_lender_id
165 AND lend_non_ed_brc_id = x_lend_non_ed_brc_id
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN(TRUE);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_pk_for_validation;
183
184
185 PROCEDURE get_fk_igf_sl_lender (
186 x_lender_id IN VARCHAR2
187 ) AS
188 /*
189 || Created By : prchandr
190 || Created On : 07-NOV-2000
191 || Purpose : Validates the Foreign Keys for the table.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM igf_sl_lender_brc
200 WHERE ((lender_id = x_lender_id));
201
202 lv_rowid cur_rowid%RowType;
203
204 BEGIN
205
206 OPEN cur_rowid;
207 FETCH cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 CLOSE cur_rowid;
210 fnd_message.set_name ('IGF', 'IGF_SL_LNDB_LND_FK');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 RETURN;
214 END IF;
215 CLOSE cur_rowid;
216
217 END get_fk_igf_sl_lender;
218
219 FUNCTION get_uk_for_validation (
220 x_party_id IN NUMBER
221 ) RETURN BOOLEAN AS
222 /*
223 || Created By : veramach
224 || Created On : 03-SEP-2003
225 || Purpose : Validates the Unique of the table.
226 || Known limitations, enhancements or remarks :
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM igf_sl_lender_brc
235 WHERE party_id = x_party_id
236 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 OPEN cur_rowid;
243 FETCH cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 CLOSE cur_rowid;
246 RETURN (TRUE);
247 ELSE
248 CLOSE cur_rowid;
249 RETURN(FALSE);
250 END IF;
251 END get_uk_for_validation;
252
253 PROCEDURE before_dml (
254 p_action IN VARCHAR2,
255 x_rowid IN VARCHAR2 DEFAULT NULL,
256 x_lender_id IN VARCHAR2 DEFAULT NULL,
257 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
258 x_description IN VARCHAR2 DEFAULT NULL,
259 x_party_id IN NUMBER DEFAULT NULL,
260 x_enabled IN VARCHAR2 DEFAULT NULL,
261 x_creation_date IN DATE DEFAULT NULL,
262 x_created_by IN NUMBER DEFAULT NULL,
263 x_last_update_date IN DATE DEFAULT NULL,
264 x_last_updated_by IN NUMBER DEFAULT NULL,
265 x_last_update_login IN NUMBER DEFAULT NULL
266 ) AS
267 /*
268 || Created By : prchandr
269 || Created On : 07-NOV-2000
270 || Purpose : Initialises the columns, Checks Constraints, Calls the
271 || Trigger Handlers for the table, before any DML operation.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 BEGIN
278
279 set_column_values (
280 p_action,
281 x_rowid,
282 x_lender_id,
283 x_lend_non_ed_brc_id,
284 x_description,
285 x_party_id,
286 x_enabled,
287 x_creation_date,
288 x_created_by,
289 x_last_update_date,
290 x_last_updated_by,
291 x_last_update_login
292 );
293
294 IF (p_action = 'INSERT') THEN
295 -- Call all the procedures related to Before Insert.
296 IF ( get_pk_for_validation(
297 new_references.lender_id,
298 new_references.lend_non_ed_brc_id
299 )
300 ) THEN
301 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
302 igs_ge_msg_stack.add;
303 app_exception.raise_exception;
304 END IF;
305 check_parent_existance;
306 check_uniqueness;
307 ELSIF (p_action = 'UPDATE') THEN
308 -- Call all the procedures related to Before Update.
309 check_parent_existance;
310 check_uniqueness;
311 ELSIF (p_action = 'DELETE') THEN
312 -- Call all the procedures related to Before Delete.
313 check_child_existance;
314 ELSIF (p_action = 'VALIDATE_INSERT') THEN
315 -- Call all the procedures related to Before Insert.
316 IF ( get_pk_for_validation (
317 new_references.lender_id,
318 new_references.lend_non_ed_brc_id
319 )
320 ) THEN
321 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 END IF;
325 check_uniqueness;
326 ELSIF (p_action = 'VALIDATE_DELETE') THEN
327 check_child_existance;
328 END IF;
329
330 END before_dml;
331
332
333 PROCEDURE insert_row (
334 x_rowid IN OUT NOCOPY VARCHAR2,
335 x_lender_id IN VARCHAR2,
336 x_lend_non_ed_brc_id IN VARCHAR2,
337 x_description IN VARCHAR2,
338 x_party_id IN NUMBER,
339 x_enabled IN VARCHAR2,
340 x_mode IN VARCHAR2 DEFAULT 'R'
341 ) AS
342 /*
343 || Created By : prchandr
344 || Created On : 07-NOV-2000
345 || Purpose : Handles the INSERT DML logic for the table.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 */
351 CURSOR c IS
352 SELECT rowid
353 FROM igf_sl_lender_brc
354 WHERE lender_id = x_lender_id
355 AND lend_non_ed_brc_id = x_lend_non_ed_brc_id;
356
357 x_last_update_date DATE;
358 x_last_updated_by NUMBER;
359 x_last_update_login NUMBER;
360
361 BEGIN
362
363 x_last_update_date := SYSDATE;
364 IF (x_mode = 'I') THEN
365 x_last_updated_by := 1;
366 x_last_update_login := 0;
367 ELSIF (x_mode = 'R') THEN
368 x_last_updated_by := fnd_global.user_id;
369 IF (x_last_updated_by IS NULL) THEN
370 x_last_updated_by := -1;
371 END IF;
372 x_last_update_login := fnd_global.login_id;
373 IF (x_last_update_login IS NULL) THEN
374 x_last_update_login := -1;
375 END IF;
376 ELSE
377 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
378 igs_ge_msg_stack.add;
379 app_exception.raise_exception;
380 END IF;
381
382 before_dml(
383 p_action => 'INSERT',
384 x_rowid => x_rowid,
385 x_lender_id => x_lender_id,
386 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
387 x_description => x_description,
388 x_party_id => x_party_id,
389 x_enabled => x_enabled,
390 x_creation_date => x_last_update_date,
391 x_created_by => x_last_updated_by,
392 x_last_update_date => x_last_update_date,
393 x_last_updated_by => x_last_updated_by,
394 x_last_update_login => x_last_update_login
395 );
396
397 INSERT INTO igf_sl_lender_brc (
398 lender_id,
399 lend_non_ed_brc_id,
400 description,
401 party_id,
402 enabled,
403 creation_date,
404 created_by,
405 last_update_date,
406 last_updated_by,
407 last_update_login
408 ) VALUES (
409 new_references.lender_id,
410 new_references.lend_non_ed_brc_id,
411 new_references.description,
412 new_references.party_id,
413 new_references.enabled,
414 x_last_update_date,
415 x_last_updated_by,
416 x_last_update_date,
417 x_last_updated_by,
418 x_last_update_login
419 );
420
421 OPEN c;
422 FETCH c INTO x_rowid;
423 IF (c%NOTFOUND) THEN
424 CLOSE c;
425 RAISE NO_DATA_FOUND;
426 END IF;
427 CLOSE c;
428
429 END insert_row;
430
431
432 PROCEDURE lock_row (
433 x_rowid IN VARCHAR2,
434 x_lender_id IN VARCHAR2,
435 x_lend_non_ed_brc_id IN VARCHAR2,
436 x_description IN VARCHAR2,
437 x_party_id IN NUMBER,
438 x_enabled IN VARCHAR2
439 ) AS
440 /*
441 || Created By : prchandr
442 || Created On : 07-NOV-2000
443 || Purpose : Handles the LOCK mechanism for the table.
444 || Known limitations, enhancements or remarks :
445 || Change History :
446 || Who When What
447 || (reverse chronological order - newest change first)
448 */
449 CURSOR c1 IS
450 SELECT
451 description,
452 party_id,
453 enabled
454 FROM igf_sl_lender_brc
455 WHERE rowid = x_rowid
456 FOR UPDATE NOWAIT;
457
458 tlinfo c1%ROWTYPE;
459
460 BEGIN
461
462 OPEN c1;
463 FETCH c1 INTO tlinfo;
464 IF (c1%notfound) THEN
465 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
466 igs_ge_msg_stack.add;
467 CLOSE c1;
468 app_exception.raise_exception;
469 RETURN;
470 END IF;
471 CLOSE c1;
472
473 IF (
474 (tlinfo.description = x_description)
475 AND ((tlinfo.party_id = x_party_id) OR ((tlinfo.party_id IS NULL) AND (X_party_id IS NULL)))
476 AND (tlinfo.enabled = x_enabled)
477 ) THEN
478 NULL;
479 ELSE
480 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
481 igs_ge_msg_stack.add;
482 app_exception.raise_exception;
483 END IF;
484
485 RETURN;
486
487 END lock_row;
488
489
490 PROCEDURE update_row (
491 x_rowid IN VARCHAR2,
492 x_lender_id IN VARCHAR2,
493 x_lend_non_ed_brc_id IN VARCHAR2,
494 x_description IN VARCHAR2,
495 x_party_id IN NUMBER,
496 x_enabled IN VARCHAR2,
497 x_mode IN VARCHAR2 DEFAULT 'R'
498 ) AS
499 /*
500 || Created By : prchandr
501 || Created On : 07-NOV-2000
502 || Purpose : Handles the UPDATE DML logic for the table.
503 || Known limitations, enhancements or remarks :
504 || Change History :
505 || Who When What
506 || (reverse chronological order - newest change first)
507 */
508 x_last_update_date DATE ;
509 x_last_updated_by NUMBER;
510 x_last_update_login NUMBER;
511
512 BEGIN
513
514 x_last_update_date := SYSDATE;
515 IF (X_MODE = 'I') THEN
516 x_last_updated_by := 1;
517 x_last_update_login := 0;
518 ELSIF (x_mode = 'R') THEN
519 x_last_updated_by := fnd_global.user_id;
520 IF x_last_updated_by IS NULL THEN
521 x_last_updated_by := -1;
522 END IF;
523 x_last_update_login := fnd_global.login_id;
524 IF (x_last_update_login IS NULL) THEN
525 x_last_update_login := -1;
526 END IF;
527 ELSE
528 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
529 igs_ge_msg_stack.add;
530 app_exception.raise_exception;
531 END IF;
532
533 before_dml(
534 p_action => 'UPDATE',
535 x_rowid => x_rowid,
536 x_lender_id => x_lender_id,
537 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
538 x_description => x_description,
539 x_party_id => x_party_id,
540 x_enabled => x_enabled,
541 x_creation_date => x_last_update_date,
542 x_created_by => x_last_updated_by,
543 x_last_update_date => x_last_update_date,
544 x_last_updated_by => x_last_updated_by,
545 x_last_update_login => x_last_update_login
546 );
547
548 UPDATE igf_sl_lender_brc
549 SET
550 description = new_references.description,
551 party_id = new_references.party_id,
552 enabled = new_references.enabled,
553 last_update_date = x_last_update_date,
554 last_updated_by = x_last_updated_by,
555 last_update_login = x_last_update_login
556 WHERE rowid = x_rowid;
557
558 IF (SQL%NOTFOUND) THEN
559 RAISE NO_DATA_FOUND;
560 END IF;
561
562 END update_row;
563
564
565 PROCEDURE add_row (
566 x_rowid IN OUT NOCOPY VARCHAR2,
567 x_lender_id IN VARCHAR2,
568 x_lend_non_ed_brc_id IN VARCHAR2,
569 x_description IN VARCHAR2,
570 x_party_id IN NUMBER,
571 x_enabled IN VARCHAR2,
572 x_mode IN VARCHAR2 DEFAULT 'R'
573 ) AS
574 /*
575 || Created By : prchandr
576 || Created On : 07-NOV-2000
577 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
578 || Known limitations, enhancements or remarks :
579 || Change History :
580 || Who When What
581 || (reverse chronological order - newest change first)
582 */
583 CURSOR c1 IS
584 SELECT rowid
585 FROM igf_sl_lender_brc
586 WHERE lender_id = x_lender_id
587 AND lend_non_ed_brc_id = x_lend_non_ed_brc_id;
588
589 BEGIN
590
591 OPEN c1;
592 FETCH c1 INTO x_rowid;
593 IF (c1%NOTFOUND) THEN
594 CLOSE c1;
595
596 insert_row (
597 x_rowid,
598 x_lender_id,
599 x_lend_non_ed_brc_id,
600 x_description,
601 x_party_id,
602 x_enabled,
603 x_mode
604 );
605 RETURN;
606 END IF;
607 CLOSE c1;
608
609 update_row (
610 x_rowid,
611 x_lender_id,
612 x_lend_non_ed_brc_id,
613 x_description,
614 x_party_id,
615 x_enabled,
616 x_mode
617 );
618
619 END add_row;
620
621
622 PROCEDURE delete_row (
623 x_rowid IN VARCHAR2
624 ) AS
625 /*
626 || Created By : prchandr
627 || Created On : 07-NOV-2000
628 || Purpose : Handles the DELETE DML logic for the table.
629 || Known limitations, enhancements or remarks :
630 || Change History :
631 || Who When What
632 || (reverse chronological order - newest change first)
633 */
634 BEGIN
635
636 before_dml (
637 p_action => 'DELETE',
638 x_rowid => x_rowid
639 );
640
641 DELETE FROM igf_sl_lender_brc
642 WHERE rowid = x_rowid;
643
644 IF (SQL%NOTFOUND) THEN
645 RAISE NO_DATA_FOUND;
646 END IF;
647
648 END delete_row;
649
650
651 END igf_sl_lender_brc_pkg;
[Home] [Help]