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