[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_MANIFEST_PKG
Source
1 PACKAGE BODY igf_sl_dl_manifest_pkg AS
2 /* $Header: IGFLI31B.pls 115.5 2002/11/28 14:28:35 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_dl_manifest_all%ROWTYPE;
6 new_references igf_sl_dl_manifest_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_pnmn_id IN NUMBER DEFAULT NULL,
12 x_batch_seq_num IN NUMBER DEFAULT NULL,
13 x_loan_id IN NUMBER DEFAULT NULL,
14 x_loan_number IN VARCHAR2 DEFAULT NULL,
15 x_b_ssn IN VARCHAR2 DEFAULT NULL,
16 x_b_first_name IN VARCHAR2 DEFAULT NULL,
17 x_b_last_name IN VARCHAR2 DEFAULT NULL,
18 x_b_middle_name IN VARCHAR2 DEFAULT NULL,
19 x_status IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 /*
27 || Created By : viramali
28 || Created On : 10-MAY-2001
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGF_SL_DL_MANIFEST_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.pnmn_id := x_pnmn_id;
60 new_references.batch_seq_num := x_batch_seq_num;
61 new_references.loan_id := x_loan_id;
62 new_references.loan_number := x_loan_number;
63 new_references.b_ssn := x_b_ssn;
64 new_references.b_first_name := x_b_first_name;
65 new_references.b_last_name := x_b_last_name;
66 new_references.b_middle_name := x_b_middle_name;
67 new_references.status := x_status;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
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 : viramali
87 || Created On : 10-MAY-2001
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.loan_id = new_references.loan_id)) OR
97 ((new_references.loan_id IS NULL))) THEN
98 NULL;
99 ELSIF NOT igf_sl_loans_pkg.get_pk_for_validation (
100 new_references.loan_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 END check_parent_existance;
108
109
110 FUNCTION get_pk_for_validation (
111 x_pnmn_id IN NUMBER
112 ) RETURN BOOLEAN AS
113 /*
114 || Created By : viramali
115 || Created On : 10-MAY-2001
116 || Purpose : Validates the Primary Key of the table.
117 || Known limitations, enhancements or remarks :
118 || Change History :
119 || Who When What
120 || (reverse chronological order - newest change first)
121 */
122 CURSOR cur_rowid IS
123 SELECT rowid
124 FROM igf_sl_dl_manifest_all
125 WHERE pnmn_id = x_pnmn_id
126 FOR UPDATE NOWAIT;
127
128 lv_rowid cur_rowid%RowType;
129
130 BEGIN
131
132 OPEN cur_rowid;
133 FETCH cur_rowid INTO lv_rowid;
134 IF (cur_rowid%FOUND) THEN
135 CLOSE cur_rowid;
136 RETURN(TRUE);
137 ELSE
138 CLOSE cur_rowid;
139 RETURN(FALSE);
140 END IF;
141
142 END get_pk_for_validation;
143
144
145 PROCEDURE get_fk_igf_sl_loans (
146 x_loan_id IN NUMBER
147 ) AS
148 /*
149 || Created By : viramali
150 || Created On : 10-MAY-2001
151 || Purpose : Validates the Foreign Keys for the table.
152 || Known limitations, enhancements or remarks :
153 || Change History :
154 || Who When What
155 || (reverse chronological order - newest change first)
156 */
157 CURSOR cur_rowid IS
158 SELECT rowid
159 FROM igf_sl_dl_manifest_all
160 WHERE ((loan_id = x_loan_id));
161
162 lv_rowid cur_rowid%RowType;
163
164 BEGIN
165
166 OPEN cur_rowid;
167 FETCH cur_rowid INTO lv_rowid;
168 IF (cur_rowid%FOUND) THEN
169 CLOSE cur_rowid;
170 fnd_message.set_name ('IGF', 'IGF_SL_PNMN_LAR_FK');
171 igs_ge_msg_stack.add;
172 app_exception.raise_exception;
173 RETURN;
174 END IF;
175 CLOSE cur_rowid;
176
177 END get_fk_igf_sl_loans;
178
179
180 PROCEDURE before_dml (
181 p_action IN VARCHAR2,
182 x_rowid IN VARCHAR2 DEFAULT NULL,
183 x_pnmn_id IN NUMBER DEFAULT NULL,
184 x_batch_seq_num IN NUMBER DEFAULT NULL,
185 x_loan_id IN NUMBER DEFAULT NULL,
186 x_loan_number IN VARCHAR2 DEFAULT NULL,
187 x_b_ssn IN VARCHAR2 DEFAULT NULL,
188 x_b_first_name IN VARCHAR2 DEFAULT NULL,
189 x_b_last_name IN VARCHAR2 DEFAULT NULL,
190 x_b_middle_name IN VARCHAR2 DEFAULT NULL,
191 x_status IN VARCHAR2 DEFAULT NULL,
192 x_creation_date IN DATE DEFAULT NULL,
193 x_created_by IN NUMBER DEFAULT NULL,
194 x_last_update_date IN DATE DEFAULT NULL,
195 x_last_updated_by IN NUMBER DEFAULT NULL,
196 x_last_update_login IN NUMBER DEFAULT NULL
197 ) AS
198 /*
199 || Created By : viramali
200 || Created On : 10-MAY-2001
201 || Purpose : Initialises the columns, Checks Constraints, Calls the
202 || Trigger Handlers for the table, before any DML operation.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || (reverse chronological order - newest change first)
207 */
208 BEGIN
209
210 set_column_values (
211 p_action,
212 x_rowid,
213 x_pnmn_id,
214 x_batch_seq_num,
215 x_loan_id,
216 x_loan_number,
217 x_b_ssn,
218 x_b_first_name,
219 x_b_last_name,
220 x_b_middle_name,
221 x_status,
222 x_creation_date,
223 x_created_by,
224 x_last_update_date,
225 x_last_updated_by,
226 x_last_update_login
227 );
228
229 IF (p_action = 'INSERT') THEN
230 -- Call all the procedures related to Before Insert.
231 IF ( get_pk_for_validation(
232 new_references.pnmn_id
233 )
234 ) THEN
235 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
236 igs_ge_msg_stack.add;
237 app_exception.raise_exception;
238 END IF;
239 check_parent_existance;
240 ELSIF (p_action = 'UPDATE') THEN
241 -- Call all the procedures related to Before Update.
242 check_parent_existance;
243 ELSIF (p_action = 'VALIDATE_INSERT') THEN
244 -- Call all the procedures related to Before Insert.
245 IF ( get_pk_for_validation (
246 new_references.pnmn_id
247 )
248 ) THEN
249 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
250 igs_ge_msg_stack.add;
251 app_exception.raise_exception;
252 END IF;
253 END IF;
254
255 END before_dml;
256
257
258 PROCEDURE insert_row (
259 x_rowid IN OUT NOCOPY VARCHAR2,
260 x_pnmn_id IN OUT NOCOPY NUMBER,
261 x_batch_seq_num IN NUMBER,
262 x_loan_id IN NUMBER,
263 x_loan_number IN VARCHAR2,
264 x_b_ssn IN VARCHAR2,
265 x_b_first_name IN VARCHAR2,
266 x_b_last_name IN VARCHAR2,
267 x_b_middle_name IN VARCHAR2,
268 x_status IN VARCHAR2,
269 x_mode IN VARCHAR2 DEFAULT 'R'
270 ) AS
271 /*
272 || Created By : viramali
273 || Created On : 10-MAY-2001
274 || Purpose : Handles the INSERT DML logic for the table.
275 || Known limitations, enhancements or remarks :
276 || Change History :
277 || Who When What
278 || (reverse chronological order - newest change first)
279 */
280 CURSOR c IS
281 SELECT rowid
282 FROM igf_sl_dl_manifest_all
283 WHERE pnmn_id = x_pnmn_id;
284
285 x_last_update_date DATE;
286 x_last_updated_by NUMBER;
287 x_last_update_login NUMBER;
288 x_request_id NUMBER;
289 x_program_id NUMBER;
290 x_program_application_id NUMBER;
291 x_program_update_date DATE;
292
293 BEGIN
294
295 x_last_update_date := SYSDATE;
296 IF (x_mode = 'I') THEN
297 x_last_updated_by := 1;
298 x_last_update_login := 0;
299 ELSIF (x_mode = 'R') THEN
300 x_last_updated_by := fnd_global.user_id;
301 IF (x_last_updated_by IS NULL) THEN
302 x_last_updated_by := -1;
303 END IF;
304 x_last_update_login := fnd_global.login_id;
305 IF (x_last_update_login IS NULL) THEN
306 x_last_update_login := -1;
307 END IF;
308 x_request_id := fnd_global.conc_request_id;
309 x_program_id := fnd_global.conc_program_id;
310 x_program_application_id := fnd_global.prog_appl_id;
311
312 IF (x_request_id = -1) THEN
313 x_request_id := NULL;
314 x_program_id := NULL;
315 x_program_application_id := NULL;
316 x_program_update_date := NULL;
317 ELSE
318 x_program_update_date := SYSDATE;
319 END IF;
320 ELSE
321 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 END IF;
325
326 SELECT igf_sl_dl_manifest_all_s.NEXTVAL
327 INTO x_pnmn_id
328 FROM dual;
329
330 new_references.org_id := igs_ge_gen_003.get_org_id;
331
332 before_dml(
333 p_action => 'INSERT',
334 x_rowid => x_rowid,
335 x_pnmn_id => x_pnmn_id,
336 x_batch_seq_num => x_batch_seq_num,
337 x_loan_id => x_loan_id,
338 x_loan_number => x_loan_number,
339 x_b_ssn => x_b_ssn,
340 x_b_first_name => x_b_first_name,
341 x_b_last_name => x_b_last_name,
342 x_b_middle_name => x_b_middle_name,
343 x_status => x_status,
344 x_creation_date => x_last_update_date,
345 x_created_by => x_last_updated_by,
346 x_last_update_date => x_last_update_date,
347 x_last_updated_by => x_last_updated_by,
348 x_last_update_login => x_last_update_login
349 );
350
351 INSERT INTO igf_sl_dl_manifest_all (
352 pnmn_id,
353 batch_seq_num,
354 loan_id,
355 loan_number,
356 b_ssn,
357 b_first_name,
358 b_last_name,
359 b_middle_name,
360 status,
361 org_id,
362 creation_date,
363 created_by,
364 last_update_date,
365 last_updated_by,
366 last_update_login,
367 request_id,
368 program_id,
369 program_application_id,
370 program_update_date
371 ) VALUES (
372 new_references.pnmn_id,
373 new_references.batch_seq_num,
374 new_references.loan_id,
375 new_references.loan_number,
376 new_references.b_ssn,
377 new_references.b_first_name,
378 new_references.b_last_name,
379 new_references.b_middle_name,
380 new_references.status,
381 new_references.org_id,
382 x_last_update_date,
383 x_last_updated_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login ,
387 x_request_id,
388 x_program_id,
389 x_program_application_id,
390 x_program_update_date
391 );
392
393 OPEN c;
394 FETCH c INTO x_rowid;
395 IF (c%NOTFOUND) THEN
396 CLOSE c;
397 RAISE NO_DATA_FOUND;
398 END IF;
399 CLOSE c;
400
401 END insert_row;
402
403
404 PROCEDURE lock_row (
405 x_rowid IN VARCHAR2,
406 x_pnmn_id IN NUMBER,
407 x_batch_seq_num IN NUMBER,
408 x_loan_id IN NUMBER,
409 x_loan_number IN VARCHAR2,
410 x_b_ssn IN VARCHAR2,
414 x_status IN VARCHAR2
411 x_b_first_name IN VARCHAR2,
412 x_b_last_name IN VARCHAR2,
413 x_b_middle_name IN VARCHAR2,
415 ) AS
416 /*
417 || Created By : viramali
418 || Created On : 10-MAY-2001
419 || Purpose : Handles the LOCK mechanism for the table.
420 || Known limitations, enhancements or remarks :
421 || Change History :
422 || Who When What
423 || (reverse chronological order - newest change first)
424 */
425 CURSOR c1 IS
426 SELECT
427 batch_seq_num,
428 loan_id,
429 loan_number,
430 b_ssn,
431 b_first_name,
432 b_last_name,
433 b_middle_name,
434 status
435 FROM igf_sl_dl_manifest_all
436 WHERE rowid = x_rowid
437 FOR UPDATE NOWAIT;
438
439 tlinfo c1%ROWTYPE;
440
441 BEGIN
442
443 OPEN c1;
444 FETCH c1 INTO tlinfo;
445 IF (c1%notfound) THEN
446 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
447 igs_ge_msg_stack.add;
448 CLOSE c1;
449 app_exception.raise_exception;
450 RETURN;
451 END IF;
452 CLOSE c1;
453
454 IF (
455 (tlinfo.batch_seq_num = x_batch_seq_num)
456 AND (tlinfo.loan_id = x_loan_id)
457 AND (tlinfo.loan_number = x_loan_number)
458 AND ((tlinfo.b_ssn = x_b_ssn) OR ((tlinfo.b_ssn IS NULL) AND (X_b_ssn IS NULL)))
459 AND ((tlinfo.b_first_name = x_b_first_name) OR ((tlinfo.b_first_name IS NULL) AND (X_b_first_name IS NULL)))
460 AND ((tlinfo.b_last_name = x_b_last_name) OR ((tlinfo.b_last_name IS NULL) AND (X_b_last_name IS NULL)))
461 AND ((tlinfo.b_middle_name = x_b_middle_name) OR ((tlinfo.b_middle_name IS NULL) AND (X_b_middle_name IS NULL)))
462 AND ((tlinfo.status = x_status) OR ((tlinfo.status IS NULL) AND (X_status IS NULL)))
463 ) THEN
464 NULL;
465 ELSE
466 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
467 igs_ge_msg_stack.add;
468 app_exception.raise_exception;
469 END IF;
470
471 RETURN;
472
473 END lock_row;
474
475
476 PROCEDURE update_row (
477 x_rowid IN VARCHAR2,
478 x_pnmn_id IN NUMBER,
479 x_batch_seq_num IN NUMBER,
480 x_loan_id IN NUMBER,
481 x_loan_number IN VARCHAR2,
482 x_b_ssn IN VARCHAR2,
483 x_b_first_name IN VARCHAR2,
484 x_b_last_name IN VARCHAR2,
485 x_b_middle_name IN VARCHAR2,
486 x_status IN VARCHAR2,
487 x_mode IN VARCHAR2 DEFAULT 'R'
488 ) AS
489 /*
490 || Created By : viramali
491 || Created On : 10-MAY-2001
492 || Purpose : Handles the UPDATE DML logic for the table.
493 || Known limitations, enhancements or remarks :
494 || Change History :
495 || Who When What
496 || (reverse chronological order - newest change first)
497 */
498 x_last_update_date DATE ;
499 x_last_updated_by NUMBER;
500 x_last_update_login NUMBER;
501 x_request_id NUMBER;
502 x_program_id NUMBER;
503 x_program_application_id NUMBER;
504 x_program_update_date DATE;
505
506 BEGIN
507
508 x_last_update_date := SYSDATE;
509 IF (X_MODE = 'I') THEN
510 x_last_updated_by := 1;
511 x_last_update_login := 0;
512 ELSIF (x_mode = 'R') THEN
513 x_last_updated_by := fnd_global.user_id;
514 IF x_last_updated_by IS NULL THEN
515 x_last_updated_by := -1;
516 END IF;
517 x_last_update_login := fnd_global.login_id;
518 IF (x_last_update_login IS NULL) THEN
519 x_last_update_login := -1;
520 END IF;
521 ELSE
522 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
523 igs_ge_msg_stack.add;
524 app_exception.raise_exception;
525 END IF;
526
527 before_dml(
528 p_action => 'UPDATE',
529 x_rowid => x_rowid,
530 x_pnmn_id => x_pnmn_id,
531 x_batch_seq_num => x_batch_seq_num,
532 x_loan_id => x_loan_id,
533 x_loan_number => x_loan_number,
534 x_b_ssn => x_b_ssn,
535 x_b_first_name => x_b_first_name,
536 x_b_last_name => x_b_last_name,
537 x_b_middle_name => x_b_middle_name,
538 x_status => x_status,
539 x_creation_date => x_last_update_date,
540 x_created_by => x_last_updated_by,
541 x_last_update_date => x_last_update_date,
542 x_last_updated_by => x_last_updated_by,
543 x_last_update_login => x_last_update_login
544 );
545
546 IF (x_mode = 'R') THEN
547 x_request_id := fnd_global.conc_request_id;
548 x_program_id := fnd_global.conc_program_id;
549 x_program_application_id := fnd_global.prog_appl_id;
550 IF (x_request_id = -1) THEN
551 x_request_id := old_references.request_id;
552 x_program_id := old_references.program_id;
553 x_program_application_id := old_references.program_application_id;
554 x_program_update_date := old_references.program_update_date;
555 ELSE
556 x_program_update_date := SYSDATE;
557 END IF;
558 END IF;
559
560 UPDATE igf_sl_dl_manifest_all
561 SET
562 batch_seq_num = new_references.batch_seq_num,
563 loan_id = new_references.loan_id,
564 loan_number = new_references.loan_number,
565 b_ssn = new_references.b_ssn,
566 b_first_name = new_references.b_first_name,
567 b_last_name = new_references.b_last_name,
568 b_middle_name = new_references.b_middle_name,
569 status = new_references.status,
570 last_update_date = x_last_update_date,
571 last_updated_by = x_last_updated_by,
572 last_update_login = x_last_update_login ,
576 program_update_date = x_program_update_date
573 request_id = x_request_id,
574 program_id = x_program_id,
575 program_application_id = x_program_application_id,
577 WHERE rowid = x_rowid;
578
579 IF (SQL%NOTFOUND) THEN
580 RAISE NO_DATA_FOUND;
581 END IF;
582
583 END update_row;
584
585
586 PROCEDURE add_row (
587 x_rowid IN OUT NOCOPY VARCHAR2,
588 x_pnmn_id IN OUT NOCOPY NUMBER,
589 x_batch_seq_num IN NUMBER,
590 x_loan_id IN NUMBER,
591 x_loan_number IN VARCHAR2,
592 x_b_ssn IN VARCHAR2,
593 x_b_first_name IN VARCHAR2,
594 x_b_last_name IN VARCHAR2,
595 x_b_middle_name IN VARCHAR2,
596 x_status IN VARCHAR2,
597 x_mode IN VARCHAR2 DEFAULT 'R'
598 ) AS
599 /*
600 || Created By : viramali
601 || Created On : 10-MAY-2001
602 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
603 || Known limitations, enhancements or remarks :
604 || Change History :
605 || Who When What
606 || (reverse chronological order - newest change first)
607 */
608 CURSOR c1 IS
609 SELECT rowid
610 FROM igf_sl_dl_manifest_all
611 WHERE pnmn_id = x_pnmn_id;
612
613 BEGIN
614
615 OPEN c1;
616 FETCH c1 INTO x_rowid;
617 IF (c1%NOTFOUND) THEN
618 CLOSE c1;
619
620 insert_row (
621 x_rowid,
622 x_pnmn_id,
623 x_batch_seq_num,
624 x_loan_id,
625 x_loan_number,
626 x_b_ssn,
627 x_b_first_name,
628 x_b_last_name,
629 x_b_middle_name,
630 x_status,
631 x_mode
632 );
633 RETURN;
634 END IF;
635 CLOSE c1;
636
637 update_row (
638 x_rowid,
639 x_pnmn_id,
640 x_batch_seq_num,
641 x_loan_id,
642 x_loan_number,
643 x_b_ssn,
644 x_b_first_name,
645 x_b_last_name,
646 x_b_middle_name,
647 x_status,
648 x_mode
649 );
650
651 END add_row;
652
653
654 PROCEDURE delete_row (
655 x_rowid IN VARCHAR2
656 ) AS
657 /*
658 || Created By : viramali
659 || Created On : 10-MAY-2001
660 || Purpose : Handles the DELETE DML logic for the table.
661 || Known limitations, enhancements or remarks :
662 || Change History :
663 || Who When What
664 || (reverse chronological order - newest change first)
665 */
666 BEGIN
667
668 before_dml (
669 p_action => 'DELETE',
670 x_rowid => x_rowid
671 );
672
673 DELETE FROM igf_sl_dl_manifest_all
674 WHERE rowid = x_rowid;
675
676 IF (SQL%NOTFOUND) THEN
677 RAISE NO_DATA_FOUND;
678 END IF;
679
680 END delete_row;
681
682
683 END igf_sl_dl_manifest_pkg;