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