[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_PNOTE_P_P_PKG
Source
1 PACKAGE BODY igf_sl_dl_pnote_p_p_pkg AS
2 /* $Header: IGFLI30B.pls 115.4 2002/11/28 14:28:20 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_dl_pnote_p_p_all%ROWTYPE;
6 new_references igf_sl_dl_pnote_p_p_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_pnpp_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_loan_amt_offered IN NUMBER DEFAULT NULL,
16 x_loan_amt_accepted IN NUMBER DEFAULT NULL,
17 x_loan_per_begin_date IN DATE DEFAULT NULL,
18 x_loan_per_end_date IN DATE DEFAULT NULL,
19 x_person_id IN NUMBER DEFAULT NULL,
20 x_s_ssn IN VARCHAR2 DEFAULT NULL,
21 x_s_first_name IN VARCHAR2 DEFAULT NULL,
22 x_s_last_name IN VARCHAR2 DEFAULT NULL,
23 x_s_middle_name IN VARCHAR2 DEFAULT NULL,
24 x_s_date_of_birth IN DATE DEFAULT NULL,
25 x_s_citizenship_status IN VARCHAR2 DEFAULT NULL,
26 x_s_alien_reg_number IN VARCHAR2 DEFAULT NULL,
27 x_s_license_num IN VARCHAR2 DEFAULT NULL,
28 x_s_license_state IN VARCHAR2 DEFAULT NULL,
29 x_s_permt_addr1 IN VARCHAR2 DEFAULT NULL,
30 x_s_permt_addr2 IN VARCHAR2 DEFAULT NULL,
31 x_s_permt_city IN VARCHAR2 DEFAULT NULL,
32 x_s_permt_state IN VARCHAR2 DEFAULT NULL,
33 x_s_permt_province IN VARCHAR2 DEFAULT NULL,
34 x_s_permt_county IN VARCHAR2 DEFAULT NULL,
35 x_s_permt_country IN VARCHAR2 DEFAULT NULL,
36 x_s_permt_zip IN VARCHAR2 DEFAULT NULL,
37 x_s_email_addr IN VARCHAR2 DEFAULT NULL,
38 x_s_phone IN VARCHAR2 DEFAULT NULL,
39 x_p_person_id IN NUMBER DEFAULT NULL,
40 x_p_ssn IN VARCHAR2 DEFAULT NULL,
41 x_p_last_name IN VARCHAR2 DEFAULT NULL,
42 x_p_first_name IN VARCHAR2 DEFAULT NULL,
43 x_p_middle_name IN VARCHAR2 DEFAULT NULL,
44 x_p_date_of_birth IN DATE DEFAULT NULL,
45 x_p_citizenship_status IN VARCHAR2 DEFAULT NULL,
46 x_p_alien_reg_num IN VARCHAR2 DEFAULT NULL,
47 x_p_license_num IN VARCHAR2 DEFAULT NULL,
48 x_p_license_state IN VARCHAR2 DEFAULT NULL,
49 x_p_permt_addr1 IN VARCHAR2 DEFAULT NULL,
50 x_p_permt_addr2 IN VARCHAR2 DEFAULT NULL,
51 x_p_permt_city IN VARCHAR2 DEFAULT NULL,
52 x_p_permt_state IN VARCHAR2 DEFAULT NULL,
53 x_p_permt_province IN VARCHAR2 DEFAULT NULL,
54 x_p_permt_county IN VARCHAR2 DEFAULT NULL,
55 x_p_permt_country IN VARCHAR2 DEFAULT NULL,
56 x_p_permt_zip IN VARCHAR2 DEFAULT NULL,
57 x_p_email_addr IN VARCHAR2 DEFAULT NULL,
58 x_p_phone IN VARCHAR2 DEFAULT NULL,
59 x_status IN VARCHAR2 DEFAULT NULL,
60 x_creation_date IN DATE DEFAULT NULL,
61 x_created_by IN NUMBER DEFAULT NULL,
62 x_last_update_date IN DATE DEFAULT NULL,
63 x_last_updated_by IN NUMBER DEFAULT NULL,
64 x_last_update_login IN NUMBER DEFAULT NULL
65 ) AS
66 /*
67 || Created By : viramali
68 || Created On : 10-MAY-2001
69 || Purpose : Initialises the Old and New references for the columns of the table.
70 || Known limitations, enhancements or remarks :
71 || Change History :
72 || Who When What
73 || (reverse chronological order - newest change first)
74 */
75
76 CURSOR cur_old_ref_values IS
77 SELECT *
78 FROM IGF_SL_DL_PNOTE_P_P_ALL
79 WHERE rowid = x_rowid;
80
81 BEGIN
82
83 l_rowid := x_rowid;
84
85 -- Code for setting the Old and New Reference Values.
86 -- Populate Old Values.
87 OPEN cur_old_ref_values;
88 FETCH cur_old_ref_values INTO old_references;
89 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
90 CLOSE cur_old_ref_values;
91 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 RETURN;
95 END IF;
96 CLOSE cur_old_ref_values;
97
98 -- Populate New Values.
99 new_references.pnpp_id := x_pnpp_id;
100 new_references.batch_seq_num := x_batch_seq_num;
101 new_references.loan_id := x_loan_id;
102 new_references.loan_number := x_loan_number;
103 new_references.loan_amt_offered := x_loan_amt_offered;
104 new_references.loan_amt_accepted := x_loan_amt_accepted;
105 new_references.loan_per_begin_date := x_loan_per_begin_date;
106 new_references.loan_per_end_date := x_loan_per_end_date;
107 new_references.person_id := x_person_id;
108 new_references.s_ssn := x_s_ssn;
109 new_references.s_first_name := x_s_first_name;
110 new_references.s_last_name := x_s_last_name;
111 new_references.s_middle_name := x_s_middle_name;
112 new_references.s_date_of_birth := x_s_date_of_birth;
113 new_references.s_citizenship_status := x_s_citizenship_status;
114 new_references.s_alien_reg_number := x_s_alien_reg_number;
115 new_references.s_license_num := x_s_license_num;
116 new_references.s_license_state := x_s_license_state;
117 new_references.s_permt_addr1 := x_s_permt_addr1;
118 new_references.s_permt_addr2 := x_s_permt_addr2;
119 new_references.s_permt_city := x_s_permt_city;
120 new_references.s_permt_state := x_s_permt_state;
121 new_references.s_permt_province := x_s_permt_province;
122 new_references.s_permt_county := x_s_permt_county;
123 new_references.s_permt_country := x_s_permt_country;
124 new_references.s_permt_zip := x_s_permt_zip;
125 new_references.s_email_addr := x_s_email_addr;
126 new_references.s_phone := x_s_phone;
127 new_references.p_person_id := x_p_person_id;
128 new_references.p_ssn := x_p_ssn;
129 new_references.p_last_name := x_p_last_name;
130 new_references.p_first_name := x_p_first_name;
131 new_references.p_middle_name := x_p_middle_name;
132 new_references.p_date_of_birth := x_p_date_of_birth;
133 new_references.p_citizenship_status := x_p_citizenship_status;
134 new_references.p_alien_reg_num := x_p_alien_reg_num;
135 new_references.p_license_num := x_p_license_num;
136 new_references.p_license_state := x_p_license_state;
137 new_references.p_permt_addr1 := x_p_permt_addr1;
138 new_references.p_permt_addr2 := x_p_permt_addr2;
139 new_references.p_permt_city := x_p_permt_city;
140 new_references.p_permt_state := x_p_permt_state;
141 new_references.p_permt_province := x_p_permt_province;
142 new_references.p_permt_county := x_p_permt_county;
143 new_references.p_permt_country := x_p_permt_country;
144 new_references.p_permt_zip := x_p_permt_zip;
145 new_references.p_email_addr := x_p_email_addr;
146 new_references.p_phone := x_p_phone;
147 new_references.status := x_status;
148
149 IF (p_action = 'UPDATE') THEN
150 new_references.creation_date := old_references.creation_date;
151 new_references.created_by := old_references.created_by;
152 ELSE
153 new_references.creation_date := x_creation_date;
154 new_references.created_by := x_created_by;
155 END IF;
156
157 new_references.last_update_date := x_last_update_date;
158 new_references.last_updated_by := x_last_updated_by;
159 new_references.last_update_login := x_last_update_login;
160
161 END set_column_values;
162
163
164 PROCEDURE check_parent_existance AS
165 /*
166 || Created By : viramali
167 || Created On : 10-MAY-2001
168 || Purpose : Checks for the existance of Parent records.
169 || Known limitations, enhancements or remarks :
170 || Change History :
171 || Who When What
172 || (reverse chronological order - newest change first)
173 */
174 BEGIN
175
176 IF (((old_references.loan_id = new_references.loan_id)) OR
177 ((new_references.loan_id IS NULL))) THEN
178 NULL;
179 ELSIF NOT igf_sl_loans_pkg.get_pk_for_validation (
180 new_references.loan_id
181 ) THEN
182 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
183 igs_ge_msg_stack.add;
184 app_exception.raise_exception;
185 END IF;
186
187 END check_parent_existance;
188
189
190 FUNCTION get_pk_for_validation (
191 x_pnpp_id IN NUMBER
192 ) RETURN BOOLEAN AS
193 /*
194 || Created By : viramali
195 || Created On : 10-MAY-2001
196 || Purpose : Validates the Primary Key of the table.
197 || Known limitations, enhancements or remarks :
198 || Change History :
199 || Who When What
200 || (reverse chronological order - newest change first)
201 */
202 CURSOR cur_rowid IS
203 SELECT rowid
204 FROM igf_sl_dl_pnote_p_p_all
205 WHERE pnpp_id = x_pnpp_id
206 FOR UPDATE NOWAIT;
207
208 lv_rowid cur_rowid%RowType;
209
210 BEGIN
211
212 OPEN cur_rowid;
213 FETCH cur_rowid INTO lv_rowid;
214 IF (cur_rowid%FOUND) THEN
215 CLOSE cur_rowid;
216 RETURN(TRUE);
217 ELSE
218 CLOSE cur_rowid;
219 RETURN(FALSE);
220 END IF;
221
222 END get_pk_for_validation;
223
224
225 PROCEDURE get_fk_igf_sl_loans (
226 x_loan_id IN NUMBER
227 ) AS
228 /*
229 || Created By : viramali
230 || Created On : 10-MAY-2001
231 || Purpose : Validates the Foreign Keys for the table.
232 || Known limitations, enhancements or remarks :
233 || Change History :
234 || Who When What
235 || (reverse chronological order - newest change first)
236 */
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igf_sl_dl_pnote_p_p_all
240 WHERE ((loan_id = x_loan_id));
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 OPEN cur_rowid;
247 FETCH cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 CLOSE cur_rowid;
250 fnd_message.set_name ('IGF', 'IGF_SL_PNPP_LAR_FK');
251 igs_ge_msg_stack.add;
252 app_exception.raise_exception;
253 RETURN;
254 END IF;
255 CLOSE cur_rowid;
256
257 END get_fk_igf_sl_loans;
258
259
260 PROCEDURE before_dml (
261 p_action IN VARCHAR2,
262 x_rowid IN VARCHAR2 DEFAULT NULL,
263 x_pnpp_id IN NUMBER DEFAULT NULL,
264 x_batch_seq_num IN NUMBER DEFAULT NULL,
265 x_loan_id IN NUMBER DEFAULT NULL,
266 x_loan_number IN VARCHAR2 DEFAULT NULL,
267 x_loan_amt_offered IN NUMBER DEFAULT NULL,
268 x_loan_amt_accepted IN NUMBER DEFAULT NULL,
269 x_loan_per_begin_date IN DATE DEFAULT NULL,
270 x_loan_per_end_date IN DATE DEFAULT NULL,
271 x_person_id IN NUMBER DEFAULT NULL,
272 x_s_ssn IN VARCHAR2 DEFAULT NULL,
273 x_s_first_name IN VARCHAR2 DEFAULT NULL,
274 x_s_last_name IN VARCHAR2 DEFAULT NULL,
278 x_s_alien_reg_number IN VARCHAR2 DEFAULT NULL,
275 x_s_middle_name IN VARCHAR2 DEFAULT NULL,
276 x_s_date_of_birth IN DATE DEFAULT NULL,
277 x_s_citizenship_status IN VARCHAR2 DEFAULT NULL,
279 x_s_license_num IN VARCHAR2 DEFAULT NULL,
280 x_s_license_state IN VARCHAR2 DEFAULT NULL,
281 x_s_permt_addr1 IN VARCHAR2 DEFAULT NULL,
282 x_s_permt_addr2 IN VARCHAR2 DEFAULT NULL,
283 x_s_permt_city IN VARCHAR2 DEFAULT NULL,
284 x_s_permt_state IN VARCHAR2 DEFAULT NULL,
285 x_s_permt_province IN VARCHAR2 DEFAULT NULL,
286 x_s_permt_county IN VARCHAR2 DEFAULT NULL,
287 x_s_permt_country IN VARCHAR2 DEFAULT NULL,
288 x_s_permt_zip IN VARCHAR2 DEFAULT NULL,
289 x_s_email_addr IN VARCHAR2 DEFAULT NULL,
290 x_s_phone IN VARCHAR2 DEFAULT NULL,
291 x_p_person_id IN NUMBER DEFAULT NULL,
292 x_p_ssn IN VARCHAR2 DEFAULT NULL,
293 x_p_last_name IN VARCHAR2 DEFAULT NULL,
294 x_p_first_name IN VARCHAR2 DEFAULT NULL,
295 x_p_middle_name IN VARCHAR2 DEFAULT NULL,
296 x_p_date_of_birth IN DATE DEFAULT NULL,
297 x_p_citizenship_status IN VARCHAR2 DEFAULT NULL,
298 x_p_alien_reg_num IN VARCHAR2 DEFAULT NULL,
299 x_p_license_num IN VARCHAR2 DEFAULT NULL,
300 x_p_license_state IN VARCHAR2 DEFAULT NULL,
301 x_p_permt_addr1 IN VARCHAR2 DEFAULT NULL,
302 x_p_permt_addr2 IN VARCHAR2 DEFAULT NULL,
303 x_p_permt_city IN VARCHAR2 DEFAULT NULL,
304 x_p_permt_state IN VARCHAR2 DEFAULT NULL,
305 x_p_permt_province IN VARCHAR2 DEFAULT NULL,
306 x_p_permt_county IN VARCHAR2 DEFAULT NULL,
307 x_p_permt_country IN VARCHAR2 DEFAULT NULL,
308 x_p_permt_zip IN VARCHAR2 DEFAULT NULL,
309 x_p_email_addr IN VARCHAR2 DEFAULT NULL,
310 x_p_phone IN VARCHAR2 DEFAULT NULL,
311 x_status IN VARCHAR2 DEFAULT NULL,
312 x_creation_date IN DATE DEFAULT NULL,
313 x_created_by IN NUMBER DEFAULT NULL,
314 x_last_update_date IN DATE DEFAULT NULL,
315 x_last_updated_by IN NUMBER DEFAULT NULL,
316 x_last_update_login IN NUMBER DEFAULT NULL
317 ) AS
318 /*
319 || Created By : viramali
320 || Created On : 10-MAY-2001
321 || Purpose : Initialises the columns, Checks Constraints, Calls the
322 || Trigger Handlers for the table, before any DML operation.
323 || Known limitations, enhancements or remarks :
324 || Change History :
325 || Who When What
326 || (reverse chronological order - newest change first)
327 */
328 BEGIN
329
330 set_column_values (
331 p_action,
332 x_rowid,
333 x_pnpp_id,
334 x_batch_seq_num,
335 x_loan_id,
336 x_loan_number,
337 x_loan_amt_offered,
338 x_loan_amt_accepted,
339 x_loan_per_begin_date,
340 x_loan_per_end_date,
341 x_person_id,
342 x_s_ssn,
343 x_s_first_name,
344 x_s_last_name,
345 x_s_middle_name,
346 x_s_date_of_birth,
347 x_s_citizenship_status,
348 x_s_alien_reg_number,
349 x_s_license_num,
350 x_s_license_state,
351 x_s_permt_addr1,
352 x_s_permt_addr2,
353 x_s_permt_city,
354 x_s_permt_state,
355 x_s_permt_province,
356 x_s_permt_county,
357 x_s_permt_country,
358 x_s_permt_zip,
359 x_s_email_addr,
360 x_s_phone,
361 x_p_person_id,
362 x_p_ssn,
363 x_p_last_name,
364 x_p_first_name,
365 x_p_middle_name,
366 x_p_date_of_birth,
367 x_p_citizenship_status,
368 x_p_alien_reg_num,
369 x_p_license_num,
370 x_p_license_state,
371 x_p_permt_addr1,
372 x_p_permt_addr2,
373 x_p_permt_city,
374 x_p_permt_state,
375 x_p_permt_province,
376 x_p_permt_county,
377 x_p_permt_country,
378 x_p_permt_zip,
379 x_p_email_addr,
380 x_p_phone,
381 x_status,
382 x_creation_date,
383 x_created_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login
387 );
388
389 IF (p_action = 'INSERT') THEN
390 -- Call all the procedures related to Before Insert.
391 IF ( get_pk_for_validation(
392 new_references.pnpp_id
393 )
394 ) THEN
398 END IF;
395 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
396 igs_ge_msg_stack.add;
397 app_exception.raise_exception;
399 check_parent_existance;
400 ELSIF (p_action = 'UPDATE') THEN
401 -- Call all the procedures related to Before Update.
402 check_parent_existance;
403 ELSIF (p_action = 'VALIDATE_INSERT') THEN
404 -- Call all the procedures related to Before Insert.
405 IF ( get_pk_for_validation (
406 new_references.pnpp_id
407 )
408 ) THEN
409 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
410 igs_ge_msg_stack.add;
411 app_exception.raise_exception;
412 END IF;
413 END IF;
414
415 END before_dml;
416
417
418 PROCEDURE insert_row (
419 x_rowid IN OUT NOCOPY VARCHAR2,
420 x_pnpp_id IN OUT NOCOPY NUMBER,
421 x_batch_seq_num IN NUMBER,
422 x_loan_id IN NUMBER,
423 x_loan_number IN VARCHAR2,
424 x_loan_amt_offered IN NUMBER,
425 x_loan_amt_accepted IN NUMBER,
426 x_loan_per_begin_date IN DATE,
427 x_loan_per_end_date IN DATE,
428 x_person_id IN NUMBER,
429 x_s_ssn IN VARCHAR2,
430 x_s_first_name IN VARCHAR2,
431 x_s_last_name IN VARCHAR2,
432 x_s_middle_name IN VARCHAR2,
433 x_s_date_of_birth IN DATE,
434 x_s_citizenship_status IN VARCHAR2,
435 x_s_alien_reg_number IN VARCHAR2,
436 x_s_license_num IN VARCHAR2,
437 x_s_license_state IN VARCHAR2,
438 x_s_permt_addr1 IN VARCHAR2,
439 x_s_permt_addr2 IN VARCHAR2,
440 x_s_permt_city IN VARCHAR2,
441 x_s_permt_state IN VARCHAR2,
442 x_s_permt_province IN VARCHAR2,
443 x_s_permt_county IN VARCHAR2,
444 x_s_permt_country IN VARCHAR2,
445 x_s_permt_zip IN VARCHAR2,
446 x_s_email_addr IN VARCHAR2,
447 x_s_phone IN VARCHAR2,
448 x_p_person_id IN NUMBER,
449 x_p_ssn IN VARCHAR2,
450 x_p_last_name IN VARCHAR2,
451 x_p_first_name IN VARCHAR2,
452 x_p_middle_name IN VARCHAR2,
453 x_p_date_of_birth IN DATE,
454 x_p_citizenship_status IN VARCHAR2,
455 x_p_alien_reg_num IN VARCHAR2,
456 x_p_license_num IN VARCHAR2,
457 x_p_license_state IN VARCHAR2,
458 x_p_permt_addr1 IN VARCHAR2,
459 x_p_permt_addr2 IN VARCHAR2,
460 x_p_permt_city IN VARCHAR2,
461 x_p_permt_state IN VARCHAR2,
462 x_p_permt_province IN VARCHAR2,
463 x_p_permt_county IN VARCHAR2,
464 x_p_permt_country IN VARCHAR2,
465 x_p_permt_zip IN VARCHAR2,
466 x_p_email_addr IN VARCHAR2,
467 x_p_phone IN VARCHAR2,
468 x_status IN VARCHAR2,
469 x_mode IN VARCHAR2 DEFAULT 'R'
470 ) AS
471 /*
472 || Created By : viramali
473 || Created On : 10-MAY-2001
474 || Purpose : Handles the INSERT DML logic for the table.
475 || Known limitations, enhancements or remarks :
476 || Change History :
477 || Who When What
478 || (reverse chronological order - newest change first)
479 */
480 CURSOR c IS
481 SELECT rowid
482 FROM igf_sl_dl_pnote_p_p_all
483 WHERE pnpp_id = x_pnpp_id;
484
485 x_last_update_date DATE;
486 x_last_updated_by NUMBER;
487 x_last_update_login NUMBER;
488 x_request_id NUMBER;
489 x_program_id NUMBER;
490 x_program_application_id NUMBER;
491 x_program_update_date DATE;
492
493 BEGIN
494
495 x_last_update_date := SYSDATE;
496 IF (x_mode = 'I') THEN
497 x_last_updated_by := 1;
498 x_last_update_login := 0;
499 ELSIF (x_mode = 'R') THEN
500 x_last_updated_by := fnd_global.user_id;
501 IF (x_last_updated_by IS NULL) THEN
502 x_last_updated_by := -1;
503 END IF;
504 x_last_update_login := fnd_global.login_id;
505 IF (x_last_update_login IS NULL) THEN
506 x_last_update_login := -1;
507 END IF;
508 x_request_id := fnd_global.conc_request_id;
509 x_program_id := fnd_global.conc_program_id;
510 x_program_application_id := fnd_global.prog_appl_id;
511
512 IF (x_request_id = -1) THEN
513 x_request_id := NULL;
514 x_program_id := NULL;
515 x_program_application_id := NULL;
519 END IF;
516 x_program_update_date := NULL;
517 ELSE
518 x_program_update_date := SYSDATE;
520 ELSE
521 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
522 igs_ge_msg_stack.add;
523 app_exception.raise_exception;
524 END IF;
525
526 SELECT igf_sl_dl_pnote_p_p_all_s.NEXTVAL
527 INTO x_pnpp_id
528 FROM dual;
529
530 new_references.org_id := igs_ge_gen_003.get_org_id;
531
532 before_dml(
533 p_action => 'INSERT',
534 x_rowid => x_rowid,
535 x_pnpp_id => x_pnpp_id,
536 x_batch_seq_num => x_batch_seq_num,
537 x_loan_id => x_loan_id,
538 x_loan_number => x_loan_number,
539 x_loan_amt_offered => x_loan_amt_offered,
540 x_loan_amt_accepted => x_loan_amt_accepted,
541 x_loan_per_begin_date => x_loan_per_begin_date,
542 x_loan_per_end_date => x_loan_per_end_date,
543 x_person_id => x_person_id,
544 x_s_ssn => x_s_ssn,
545 x_s_first_name => x_s_first_name,
546 x_s_last_name => x_s_last_name,
547 x_s_middle_name => x_s_middle_name,
548 x_s_date_of_birth => x_s_date_of_birth,
549 x_s_citizenship_status => x_s_citizenship_status,
550 x_s_alien_reg_number => x_s_alien_reg_number,
551 x_s_license_num => x_s_license_num,
552 x_s_license_state => x_s_license_state,
553 x_s_permt_addr1 => x_s_permt_addr1,
554 x_s_permt_addr2 => x_s_permt_addr2,
555 x_s_permt_city => x_s_permt_city,
556 x_s_permt_state => x_s_permt_state,
557 x_s_permt_province => x_s_permt_province,
558 x_s_permt_county => x_s_permt_county,
559 x_s_permt_country => x_s_permt_country,
560 x_s_permt_zip => x_s_permt_zip,
561 x_s_email_addr => x_s_email_addr,
562 x_s_phone => x_s_phone,
563 x_p_person_id => x_p_person_id,
564 x_p_ssn => x_p_ssn,
565 x_p_last_name => x_p_last_name,
566 x_p_first_name => x_p_first_name,
567 x_p_middle_name => x_p_middle_name,
568 x_p_date_of_birth => x_p_date_of_birth,
569 x_p_citizenship_status => x_p_citizenship_status,
570 x_p_alien_reg_num => x_p_alien_reg_num,
571 x_p_license_num => x_p_license_num,
572 x_p_license_state => x_p_license_state,
573 x_p_permt_addr1 => x_p_permt_addr1,
574 x_p_permt_addr2 => x_p_permt_addr2,
575 x_p_permt_city => x_p_permt_city,
576 x_p_permt_state => x_p_permt_state,
577 x_p_permt_province => x_p_permt_province,
578 x_p_permt_county => x_p_permt_county,
579 x_p_permt_country => x_p_permt_country,
580 x_p_permt_zip => x_p_permt_zip,
581 x_p_email_addr => x_p_email_addr,
582 x_p_phone => x_p_phone,
583 x_status => x_status,
584 x_creation_date => x_last_update_date,
585 x_created_by => x_last_updated_by,
586 x_last_update_date => x_last_update_date,
587 x_last_updated_by => x_last_updated_by,
588 x_last_update_login => x_last_update_login
589 );
590
591 INSERT INTO igf_sl_dl_pnote_p_p_all (
592 pnpp_id,
593 batch_seq_num,
594 loan_id,
595 loan_number,
596 loan_amt_offered,
597 loan_amt_accepted,
598 loan_per_begin_date,
599 loan_per_end_date,
600 person_id,
601 s_ssn,
602 s_first_name,
603 s_last_name,
604 s_middle_name,
605 s_date_of_birth,
606 s_citizenship_status,
607 s_alien_reg_number,
608 s_license_num,
609 s_license_state,
610 s_permt_addr1,
611 s_permt_addr2,
612 s_permt_city,
613 s_permt_state,
614 s_permt_province,
615 s_permt_county,
616 s_permt_country,
617 s_permt_zip,
618 s_email_addr,
619 s_phone,
620 p_person_id,
621 p_ssn,
622 p_last_name,
623 p_first_name,
624 p_middle_name,
625 p_date_of_birth,
626 p_citizenship_status,
627 p_alien_reg_num,
628 p_license_num,
629 p_license_state,
630 p_permt_addr1,
631 p_permt_addr2,
632 p_permt_city,
633 p_permt_state,
634 p_permt_province,
635 p_permt_county,
636 p_permt_country,
640 status,
637 p_permt_zip,
638 p_email_addr,
639 p_phone,
641 org_id,
642 creation_date,
643 created_by,
644 last_update_date,
645 last_updated_by,
646 last_update_login,
647 request_id,
648 program_id,
649 program_application_id,
650 program_update_date
651 ) VALUES (
652 new_references.pnpp_id,
653 new_references.batch_seq_num,
654 new_references.loan_id,
655 new_references.loan_number,
656 new_references.loan_amt_offered,
657 new_references.loan_amt_accepted,
658 new_references.loan_per_begin_date,
659 new_references.loan_per_end_date,
660 new_references.person_id,
661 new_references.s_ssn,
662 new_references.s_first_name,
663 new_references.s_last_name,
664 new_references.s_middle_name,
665 new_references.s_date_of_birth,
666 new_references.s_citizenship_status,
667 new_references.s_alien_reg_number,
668 new_references.s_license_num,
669 new_references.s_license_state,
670 new_references.s_permt_addr1,
671 new_references.s_permt_addr2,
672 new_references.s_permt_city,
673 new_references.s_permt_state,
674 new_references.s_permt_province,
675 new_references.s_permt_county,
676 new_references.s_permt_country,
677 new_references.s_permt_zip,
678 new_references.s_email_addr,
679 new_references.s_phone,
680 new_references.p_person_id,
681 new_references.p_ssn,
682 new_references.p_last_name,
683 new_references.p_first_name,
684 new_references.p_middle_name,
685 new_references.p_date_of_birth,
686 new_references.p_citizenship_status,
687 new_references.p_alien_reg_num,
688 new_references.p_license_num,
689 new_references.p_license_state,
690 new_references.p_permt_addr1,
691 new_references.p_permt_addr2,
692 new_references.p_permt_city,
693 new_references.p_permt_state,
694 new_references.p_permt_province,
695 new_references.p_permt_county,
696 new_references.p_permt_country,
697 new_references.p_permt_zip,
698 new_references.p_email_addr,
699 new_references.p_phone,
700 new_references.status,
701 new_references.org_id,
702 x_last_update_date,
703 x_last_updated_by,
704 x_last_update_date,
705 x_last_updated_by,
706 x_last_update_login ,
707 x_request_id,
708 x_program_id,
709 x_program_application_id,
710 x_program_update_date
711 );
712
713 OPEN c;
714 FETCH c INTO x_rowid;
715 IF (c%NOTFOUND) THEN
716 CLOSE c;
717 RAISE NO_DATA_FOUND;
718 END IF;
719 CLOSE c;
720
721 END insert_row;
722
723
724 PROCEDURE lock_row (
725 x_rowid IN VARCHAR2,
726 x_pnpp_id IN NUMBER,
727 x_batch_seq_num IN NUMBER,
728 x_loan_id IN NUMBER,
729 x_loan_number IN VARCHAR2,
730 x_loan_amt_offered IN NUMBER,
731 x_loan_amt_accepted IN NUMBER,
732 x_loan_per_begin_date IN DATE,
733 x_loan_per_end_date IN DATE,
734 x_person_id IN NUMBER,
735 x_s_ssn IN VARCHAR2,
736 x_s_first_name IN VARCHAR2,
737 x_s_last_name IN VARCHAR2,
738 x_s_middle_name IN VARCHAR2,
739 x_s_date_of_birth IN DATE,
740 x_s_citizenship_status IN VARCHAR2,
741 x_s_alien_reg_number IN VARCHAR2,
742 x_s_license_num IN VARCHAR2,
743 x_s_license_state IN VARCHAR2,
744 x_s_permt_addr1 IN VARCHAR2,
745 x_s_permt_addr2 IN VARCHAR2,
746 x_s_permt_city IN VARCHAR2,
747 x_s_permt_state IN VARCHAR2,
748 x_s_permt_province IN VARCHAR2,
749 x_s_permt_county IN VARCHAR2,
750 x_s_permt_country IN VARCHAR2,
751 x_s_permt_zip IN VARCHAR2,
752 x_s_email_addr IN VARCHAR2,
753 x_s_phone IN VARCHAR2,
754 x_p_person_id IN NUMBER,
755 x_p_ssn IN VARCHAR2,
756 x_p_last_name IN VARCHAR2,
757 x_p_first_name IN VARCHAR2,
758 x_p_middle_name IN VARCHAR2,
759 x_p_date_of_birth IN DATE,
760 x_p_citizenship_status IN VARCHAR2,
761 x_p_alien_reg_num IN VARCHAR2,
762 x_p_license_num IN VARCHAR2,
763 x_p_license_state IN VARCHAR2,
764 x_p_permt_addr1 IN VARCHAR2,
768 x_p_permt_province IN VARCHAR2,
765 x_p_permt_addr2 IN VARCHAR2,
766 x_p_permt_city IN VARCHAR2,
767 x_p_permt_state IN VARCHAR2,
769 x_p_permt_county IN VARCHAR2,
770 x_p_permt_country IN VARCHAR2,
771 x_p_permt_zip IN VARCHAR2,
772 x_p_email_addr IN VARCHAR2,
773 x_p_phone IN VARCHAR2,
774 x_status IN VARCHAR2
775 ) AS
776 /*
777 || Created By : viramali
778 || Created On : 10-MAY-2001
779 || Purpose : Handles the LOCK mechanism for the table.
780 || Known limitations, enhancements or remarks :
781 || Change History :
782 || Who When What
783 || (reverse chronological order - newest change first)
784 */
785 CURSOR c1 IS
786 SELECT
787 batch_seq_num,
788 loan_id,
789 loan_number,
790 loan_amt_offered,
791 loan_amt_accepted,
792 loan_per_begin_date,
793 loan_per_end_date,
794 person_id,
795 s_ssn,
796 s_first_name,
797 s_last_name,
798 s_middle_name,
799 s_date_of_birth,
800 s_citizenship_status,
801 s_alien_reg_number,
802 s_license_num,
803 s_license_state,
804 s_permt_addr1,
805 s_permt_addr2,
806 s_permt_city,
807 s_permt_state,
808 s_permt_province,
809 s_permt_county,
810 s_permt_country,
811 s_permt_zip,
812 s_email_addr,
813 s_phone,
814 p_person_id,
815 p_ssn,
816 p_last_name,
817 p_first_name,
818 p_middle_name,
819 p_date_of_birth,
820 p_citizenship_status,
821 p_alien_reg_num,
822 p_license_num,
823 p_license_state,
824 p_permt_addr1,
825 p_permt_addr2,
826 p_permt_city,
827 p_permt_state,
828 p_permt_province,
829 p_permt_county,
830 p_permt_country,
831 p_permt_zip,
832 p_email_addr,
833 p_phone,
834 status
835 FROM igf_sl_dl_pnote_p_p_all
836 WHERE rowid = x_rowid
837 FOR UPDATE NOWAIT;
838
839 tlinfo c1%ROWTYPE;
840
841 BEGIN
842
843 OPEN c1;
844 FETCH c1 INTO tlinfo;
845 IF (c1%notfound) THEN
846 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
847 igs_ge_msg_stack.add;
848 CLOSE c1;
849 app_exception.raise_exception;
850 RETURN;
851 END IF;
852 CLOSE c1;
853
854 IF (
855 (tlinfo.batch_seq_num = x_batch_seq_num)
856 AND (tlinfo.loan_id = x_loan_id)
857 AND (tlinfo.loan_number = x_loan_number)
858 AND ((tlinfo.loan_amt_offered = x_loan_amt_offered) OR ((tlinfo.loan_amt_offered IS NULL) AND (X_loan_amt_offered IS NULL)))
859 AND ((tlinfo.loan_amt_accepted = x_loan_amt_accepted) OR ((tlinfo.loan_amt_accepted IS NULL) AND (X_loan_amt_accepted IS NULL)))
860 AND ((tlinfo.loan_per_begin_date = x_loan_per_begin_date) OR ((tlinfo.loan_per_begin_date IS NULL) AND (X_loan_per_begin_date IS NULL)))
861 AND ((tlinfo.loan_per_end_date = x_loan_per_end_date) OR ((tlinfo.loan_per_end_date IS NULL) AND (X_loan_per_end_date IS NULL)))
862 AND ((tlinfo.person_id = x_person_id) OR ((tlinfo.person_id IS NULL) AND (X_person_id IS NULL)))
863 AND ((tlinfo.s_ssn = x_s_ssn) OR ((tlinfo.s_ssn IS NULL) AND (X_s_ssn IS NULL)))
864 AND ((tlinfo.s_first_name = x_s_first_name) OR ((tlinfo.s_first_name IS NULL) AND (X_s_first_name IS NULL)))
865 AND ((tlinfo.s_last_name = x_s_last_name) OR ((tlinfo.s_last_name IS NULL) AND (X_s_last_name IS NULL)))
866 AND ((tlinfo.s_middle_name = x_s_middle_name) OR ((tlinfo.s_middle_name IS NULL) AND (X_s_middle_name IS NULL)))
867 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)))
868 AND ((tlinfo.s_citizenship_status = x_s_citizenship_status) OR ((tlinfo.s_citizenship_status IS NULL) AND (X_s_citizenship_status IS NULL)))
869 AND ((tlinfo.s_alien_reg_number = x_s_alien_reg_number) OR ((tlinfo.s_alien_reg_number IS NULL) AND (X_s_alien_reg_number IS NULL)))
870 AND ((tlinfo.s_license_num = x_s_license_num) OR ((tlinfo.s_license_num IS NULL) AND (X_s_license_num IS NULL)))
871 AND ((tlinfo.s_license_state = x_s_license_state) OR ((tlinfo.s_license_state IS NULL) AND (X_s_license_state IS NULL)))
872 AND ((tlinfo.s_permt_addr1 = x_s_permt_addr1) OR ((tlinfo.s_permt_addr1 IS NULL) AND (X_s_permt_addr1 IS NULL)))
873 AND ((tlinfo.s_permt_addr2 = x_s_permt_addr2) OR ((tlinfo.s_permt_addr2 IS NULL) AND (X_s_permt_addr2 IS NULL)))
874 AND ((tlinfo.s_permt_city = x_s_permt_city) OR ((tlinfo.s_permt_city IS NULL) AND (X_s_permt_city IS NULL)))
875 AND ((tlinfo.s_permt_state = x_s_permt_state) OR ((tlinfo.s_permt_state IS NULL) AND (X_s_permt_state IS NULL)))
876 AND ((tlinfo.s_permt_province = x_s_permt_province) OR ((tlinfo.s_permt_province IS NULL) AND (X_s_permt_province IS NULL)))
877 AND ((tlinfo.s_permt_county = x_s_permt_county) OR ((tlinfo.s_permt_county IS NULL) AND (X_s_permt_county IS NULL)))
881 AND ((tlinfo.s_phone = x_s_phone) OR ((tlinfo.s_phone IS NULL) AND (X_s_phone IS NULL)))
878 AND ((tlinfo.s_permt_country = x_s_permt_country) OR ((tlinfo.s_permt_country IS NULL) AND (X_s_permt_country IS NULL)))
879 AND ((tlinfo.s_permt_zip = x_s_permt_zip) OR ((tlinfo.s_permt_zip IS NULL) AND (X_s_permt_zip IS NULL)))
880 AND ((tlinfo.s_email_addr = x_s_email_addr) OR ((tlinfo.s_email_addr IS NULL) AND (X_s_email_addr IS NULL)))
882 AND ((tlinfo.p_person_id = x_p_person_id) OR ((tlinfo.p_person_id IS NULL) AND (X_p_person_id IS NULL)))
883 AND ((tlinfo.p_ssn = x_p_ssn) OR ((tlinfo.p_ssn IS NULL) AND (X_p_ssn IS NULL)))
884 AND ((tlinfo.p_last_name = x_p_last_name) OR ((tlinfo.p_last_name IS NULL) AND (X_p_last_name IS NULL)))
885 AND ((tlinfo.p_first_name = x_p_first_name) OR ((tlinfo.p_first_name IS NULL) AND (X_p_first_name IS NULL)))
886 AND ((tlinfo.p_middle_name = x_p_middle_name) OR ((tlinfo.p_middle_name IS NULL) AND (X_p_middle_name IS NULL)))
887 AND ((tlinfo.p_date_of_birth = x_p_date_of_birth) OR ((tlinfo.p_date_of_birth IS NULL) AND (X_p_date_of_birth IS NULL)))
888 AND ((tlinfo.p_citizenship_status = x_p_citizenship_status) OR ((tlinfo.p_citizenship_status IS NULL) AND (X_p_citizenship_status IS NULL)))
889 AND ((tlinfo.p_alien_reg_num = x_p_alien_reg_num) OR ((tlinfo.p_alien_reg_num IS NULL) AND (X_p_alien_reg_num IS NULL)))
890 AND ((tlinfo.p_license_num = x_p_license_num) OR ((tlinfo.p_license_num IS NULL) AND (X_p_license_num IS NULL)))
891 AND ((tlinfo.p_license_state = x_p_license_state) OR ((tlinfo.p_license_state IS NULL) AND (X_p_license_state IS NULL)))
892 AND ((tlinfo.p_permt_addr1 = x_p_permt_addr1) OR ((tlinfo.p_permt_addr1 IS NULL) AND (X_p_permt_addr1 IS NULL)))
893 AND ((tlinfo.p_permt_addr2 = x_p_permt_addr2) OR ((tlinfo.p_permt_addr2 IS NULL) AND (X_p_permt_addr2 IS NULL)))
894 AND ((tlinfo.p_permt_city = x_p_permt_city) OR ((tlinfo.p_permt_city IS NULL) AND (X_p_permt_city IS NULL)))
895 AND ((tlinfo.p_permt_state = x_p_permt_state) OR ((tlinfo.p_permt_state IS NULL) AND (X_p_permt_state IS NULL)))
896 AND ((tlinfo.p_permt_province = x_p_permt_province) OR ((tlinfo.p_permt_province IS NULL) AND (X_p_permt_province IS NULL)))
897 AND ((tlinfo.p_permt_county = x_p_permt_county) OR ((tlinfo.p_permt_county IS NULL) AND (X_p_permt_county IS NULL)))
898 AND ((tlinfo.p_permt_country = x_p_permt_country) OR ((tlinfo.p_permt_country IS NULL) AND (X_p_permt_country IS NULL)))
899 AND ((tlinfo.p_permt_zip = x_p_permt_zip) OR ((tlinfo.p_permt_zip IS NULL) AND (X_p_permt_zip IS NULL)))
900 AND ((tlinfo.p_email_addr = x_p_email_addr) OR ((tlinfo.p_email_addr IS NULL) AND (X_p_email_addr IS NULL)))
901 AND ((tlinfo.p_phone = x_p_phone) OR ((tlinfo.p_phone IS NULL) AND (X_p_phone IS NULL)))
902 AND ((tlinfo.status = x_status) OR ((tlinfo.status IS NULL) AND (X_status IS NULL)))
903 ) THEN
904 NULL;
905 ELSE
906 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
907 igs_ge_msg_stack.add;
908 app_exception.raise_exception;
909 END IF;
910
911 RETURN;
912
913 END lock_row;
914
915
916 PROCEDURE update_row (
917 x_rowid IN VARCHAR2,
918 x_pnpp_id IN NUMBER,
919 x_batch_seq_num IN NUMBER,
920 x_loan_id IN NUMBER,
921 x_loan_number IN VARCHAR2,
922 x_loan_amt_offered IN NUMBER,
923 x_loan_amt_accepted IN NUMBER,
924 x_loan_per_begin_date IN DATE,
925 x_loan_per_end_date IN DATE,
926 x_person_id IN NUMBER,
927 x_s_ssn IN VARCHAR2,
928 x_s_first_name IN VARCHAR2,
929 x_s_last_name IN VARCHAR2,
930 x_s_middle_name IN VARCHAR2,
931 x_s_date_of_birth IN DATE,
932 x_s_citizenship_status IN VARCHAR2,
933 x_s_alien_reg_number IN VARCHAR2,
934 x_s_license_num IN VARCHAR2,
935 x_s_license_state IN VARCHAR2,
936 x_s_permt_addr1 IN VARCHAR2,
937 x_s_permt_addr2 IN VARCHAR2,
938 x_s_permt_city IN VARCHAR2,
939 x_s_permt_state IN VARCHAR2,
940 x_s_permt_province IN VARCHAR2,
941 x_s_permt_county IN VARCHAR2,
942 x_s_permt_country IN VARCHAR2,
943 x_s_permt_zip IN VARCHAR2,
944 x_s_email_addr IN VARCHAR2,
945 x_s_phone IN VARCHAR2,
946 x_p_person_id IN NUMBER,
947 x_p_ssn IN VARCHAR2,
948 x_p_last_name IN VARCHAR2,
949 x_p_first_name IN VARCHAR2,
950 x_p_middle_name IN VARCHAR2,
951 x_p_date_of_birth IN DATE,
952 x_p_citizenship_status IN VARCHAR2,
953 x_p_alien_reg_num IN VARCHAR2,
954 x_p_license_num IN VARCHAR2,
955 x_p_license_state IN VARCHAR2,
956 x_p_permt_addr1 IN VARCHAR2,
957 x_p_permt_addr2 IN VARCHAR2,
961 x_p_permt_county IN VARCHAR2,
958 x_p_permt_city IN VARCHAR2,
959 x_p_permt_state IN VARCHAR2,
960 x_p_permt_province IN VARCHAR2,
962 x_p_permt_country IN VARCHAR2,
963 x_p_permt_zip IN VARCHAR2,
964 x_p_email_addr IN VARCHAR2,
965 x_p_phone IN VARCHAR2,
966 x_status IN VARCHAR2,
967 x_mode IN VARCHAR2 DEFAULT 'R'
968 ) AS
969 /*
970 || Created By : viramali
971 || Created On : 10-MAY-2001
972 || Purpose : Handles the UPDATE DML logic for the table.
973 || Known limitations, enhancements or remarks :
974 || Change History :
975 || Who When What
976 || (reverse chronological order - newest change first)
977 */
978 x_last_update_date DATE ;
979 x_last_updated_by NUMBER;
980 x_last_update_login NUMBER;
981 x_request_id NUMBER;
982 x_program_id NUMBER;
983 x_program_application_id NUMBER;
984 x_program_update_date DATE;
985
986 BEGIN
987
988 x_last_update_date := SYSDATE;
989 IF (X_MODE = 'I') THEN
990 x_last_updated_by := 1;
991 x_last_update_login := 0;
992 ELSIF (x_mode = 'R') THEN
993 x_last_updated_by := fnd_global.user_id;
994 IF x_last_updated_by IS NULL THEN
995 x_last_updated_by := -1;
996 END IF;
997 x_last_update_login := fnd_global.login_id;
998 IF (x_last_update_login IS NULL) THEN
999 x_last_update_login := -1;
1000 END IF;
1001 ELSE
1002 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1003 igs_ge_msg_stack.add;
1004 app_exception.raise_exception;
1005 END IF;
1006
1007 before_dml(
1008 p_action => 'UPDATE',
1009 x_rowid => x_rowid,
1010 x_pnpp_id => x_pnpp_id,
1011 x_batch_seq_num => x_batch_seq_num,
1012 x_loan_id => x_loan_id,
1013 x_loan_number => x_loan_number,
1014 x_loan_amt_offered => x_loan_amt_offered,
1015 x_loan_amt_accepted => x_loan_amt_accepted,
1016 x_loan_per_begin_date => x_loan_per_begin_date,
1017 x_loan_per_end_date => x_loan_per_end_date,
1018 x_person_id => x_person_id,
1019 x_s_ssn => x_s_ssn,
1020 x_s_first_name => x_s_first_name,
1021 x_s_last_name => x_s_last_name,
1022 x_s_middle_name => x_s_middle_name,
1023 x_s_date_of_birth => x_s_date_of_birth,
1024 x_s_citizenship_status => x_s_citizenship_status,
1025 x_s_alien_reg_number => x_s_alien_reg_number,
1026 x_s_license_num => x_s_license_num,
1027 x_s_license_state => x_s_license_state,
1028 x_s_permt_addr1 => x_s_permt_addr1,
1029 x_s_permt_addr2 => x_s_permt_addr2,
1030 x_s_permt_city => x_s_permt_city,
1031 x_s_permt_state => x_s_permt_state,
1032 x_s_permt_province => x_s_permt_province,
1033 x_s_permt_county => x_s_permt_county,
1034 x_s_permt_country => x_s_permt_country,
1035 x_s_permt_zip => x_s_permt_zip,
1036 x_s_email_addr => x_s_email_addr,
1037 x_s_phone => x_s_phone,
1038 x_p_person_id => x_p_person_id,
1039 x_p_ssn => x_p_ssn,
1040 x_p_last_name => x_p_last_name,
1041 x_p_first_name => x_p_first_name,
1042 x_p_middle_name => x_p_middle_name,
1043 x_p_date_of_birth => x_p_date_of_birth,
1044 x_p_citizenship_status => x_p_citizenship_status,
1045 x_p_alien_reg_num => x_p_alien_reg_num,
1046 x_p_license_num => x_p_license_num,
1047 x_p_license_state => x_p_license_state,
1048 x_p_permt_addr1 => x_p_permt_addr1,
1049 x_p_permt_addr2 => x_p_permt_addr2,
1050 x_p_permt_city => x_p_permt_city,
1051 x_p_permt_state => x_p_permt_state,
1052 x_p_permt_province => x_p_permt_province,
1053 x_p_permt_county => x_p_permt_county,
1054 x_p_permt_country => x_p_permt_country,
1055 x_p_permt_zip => x_p_permt_zip,
1056 x_p_email_addr => x_p_email_addr,
1057 x_p_phone => x_p_phone,
1058 x_status => x_status,
1059 x_creation_date => x_last_update_date,
1060 x_created_by => x_last_updated_by,
1061 x_last_update_date => x_last_update_date,
1065
1062 x_last_updated_by => x_last_updated_by,
1063 x_last_update_login => x_last_update_login
1064 );
1066 IF (x_mode = 'R') THEN
1067 x_request_id := fnd_global.conc_request_id;
1068 x_program_id := fnd_global.conc_program_id;
1069 x_program_application_id := fnd_global.prog_appl_id;
1070 IF (x_request_id = -1) THEN
1071 x_request_id := old_references.request_id;
1072 x_program_id := old_references.program_id;
1073 x_program_application_id := old_references.program_application_id;
1074 x_program_update_date := old_references.program_update_date;
1075 ELSE
1076 x_program_update_date := SYSDATE;
1077 END IF;
1078 END IF;
1079
1080 UPDATE igf_sl_dl_pnote_p_p_all
1081 SET
1082 batch_seq_num = new_references.batch_seq_num,
1083 loan_id = new_references.loan_id,
1084 loan_number = new_references.loan_number,
1085 loan_amt_offered = new_references.loan_amt_offered,
1086 loan_amt_accepted = new_references.loan_amt_accepted,
1087 loan_per_begin_date = new_references.loan_per_begin_date,
1088 loan_per_end_date = new_references.loan_per_end_date,
1089 person_id = new_references.person_id,
1090 s_ssn = new_references.s_ssn,
1091 s_first_name = new_references.s_first_name,
1092 s_last_name = new_references.s_last_name,
1093 s_middle_name = new_references.s_middle_name,
1094 s_date_of_birth = new_references.s_date_of_birth,
1095 s_citizenship_status = new_references.s_citizenship_status,
1096 s_alien_reg_number = new_references.s_alien_reg_number,
1097 s_license_num = new_references.s_license_num,
1098 s_license_state = new_references.s_license_state,
1099 s_permt_addr1 = new_references.s_permt_addr1,
1100 s_permt_addr2 = new_references.s_permt_addr2,
1101 s_permt_city = new_references.s_permt_city,
1102 s_permt_state = new_references.s_permt_state,
1103 s_permt_province = new_references.s_permt_province,
1104 s_permt_county = new_references.s_permt_county,
1105 s_permt_country = new_references.s_permt_country,
1106 s_permt_zip = new_references.s_permt_zip,
1107 s_email_addr = new_references.s_email_addr,
1108 s_phone = new_references.s_phone,
1109 p_person_id = new_references.p_person_id,
1110 p_ssn = new_references.p_ssn,
1111 p_last_name = new_references.p_last_name,
1112 p_first_name = new_references.p_first_name,
1113 p_middle_name = new_references.p_middle_name,
1114 p_date_of_birth = new_references.p_date_of_birth,
1115 p_citizenship_status = new_references.p_citizenship_status,
1116 p_alien_reg_num = new_references.p_alien_reg_num,
1117 p_license_num = new_references.p_license_num,
1118 p_license_state = new_references.p_license_state,
1119 p_permt_addr1 = new_references.p_permt_addr1,
1120 p_permt_addr2 = new_references.p_permt_addr2,
1121 p_permt_city = new_references.p_permt_city,
1122 p_permt_state = new_references.p_permt_state,
1123 p_permt_province = new_references.p_permt_province,
1124 p_permt_county = new_references.p_permt_county,
1125 p_permt_country = new_references.p_permt_country,
1126 p_permt_zip = new_references.p_permt_zip,
1127 p_email_addr = new_references.p_email_addr,
1128 p_phone = new_references.p_phone,
1129 status = new_references.status,
1130 last_update_date = x_last_update_date,
1131 last_updated_by = x_last_updated_by,
1132 last_update_login = x_last_update_login ,
1133 request_id = x_request_id,
1134 program_id = x_program_id,
1135 program_application_id = x_program_application_id,
1136 program_update_date = x_program_update_date
1137 WHERE rowid = x_rowid;
1138
1139 IF (SQL%NOTFOUND) THEN
1140 RAISE NO_DATA_FOUND;
1141 END IF;
1142
1143 END update_row;
1144
1145
1146 PROCEDURE add_row (
1147 x_rowid IN OUT NOCOPY VARCHAR2,
1148 x_pnpp_id IN OUT NOCOPY NUMBER,
1149 x_batch_seq_num IN NUMBER,
1150 x_loan_id IN NUMBER,
1151 x_loan_number IN VARCHAR2,
1152 x_loan_amt_offered IN NUMBER,
1153 x_loan_amt_accepted IN NUMBER,
1154 x_loan_per_begin_date IN DATE,
1155 x_loan_per_end_date IN DATE,
1156 x_person_id IN NUMBER,
1160 x_s_middle_name IN VARCHAR2,
1157 x_s_ssn IN VARCHAR2,
1158 x_s_first_name IN VARCHAR2,
1159 x_s_last_name IN VARCHAR2,
1161 x_s_date_of_birth IN DATE,
1162 x_s_citizenship_status IN VARCHAR2,
1163 x_s_alien_reg_number IN VARCHAR2,
1164 x_s_license_num IN VARCHAR2,
1165 x_s_license_state IN VARCHAR2,
1166 x_s_permt_addr1 IN VARCHAR2,
1167 x_s_permt_addr2 IN VARCHAR2,
1168 x_s_permt_city IN VARCHAR2,
1169 x_s_permt_state IN VARCHAR2,
1170 x_s_permt_province IN VARCHAR2,
1171 x_s_permt_county IN VARCHAR2,
1172 x_s_permt_country IN VARCHAR2,
1173 x_s_permt_zip IN VARCHAR2,
1174 x_s_email_addr IN VARCHAR2,
1175 x_s_phone IN VARCHAR2,
1176 x_p_person_id IN NUMBER,
1177 x_p_ssn IN VARCHAR2,
1178 x_p_last_name IN VARCHAR2,
1179 x_p_first_name IN VARCHAR2,
1180 x_p_middle_name IN VARCHAR2,
1181 x_p_date_of_birth IN DATE,
1182 x_p_citizenship_status IN VARCHAR2,
1183 x_p_alien_reg_num IN VARCHAR2,
1184 x_p_license_num IN VARCHAR2,
1185 x_p_license_state IN VARCHAR2,
1186 x_p_permt_addr1 IN VARCHAR2,
1187 x_p_permt_addr2 IN VARCHAR2,
1188 x_p_permt_city IN VARCHAR2,
1189 x_p_permt_state IN VARCHAR2,
1190 x_p_permt_province IN VARCHAR2,
1191 x_p_permt_county IN VARCHAR2,
1192 x_p_permt_country IN VARCHAR2,
1193 x_p_permt_zip IN VARCHAR2,
1194 x_p_email_addr IN VARCHAR2,
1195 x_p_phone IN VARCHAR2,
1196 x_status IN VARCHAR2,
1197 x_mode IN VARCHAR2 DEFAULT 'R'
1198 ) AS
1199 /*
1200 || Created By : viramali
1201 || Created On : 10-MAY-2001
1202 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1203 || Known limitations, enhancements or remarks :
1204 || Change History :
1205 || Who When What
1206 || (reverse chronological order - newest change first)
1207 */
1208 CURSOR c1 IS
1209 SELECT rowid
1210 FROM igf_sl_dl_pnote_p_p_all
1211 WHERE pnpp_id = x_pnpp_id;
1212
1213 BEGIN
1214
1215 OPEN c1;
1216 FETCH c1 INTO x_rowid;
1217 IF (c1%NOTFOUND) THEN
1218 CLOSE c1;
1219
1220 insert_row (
1221 x_rowid,
1222 x_pnpp_id,
1223 x_batch_seq_num,
1224 x_loan_id,
1225 x_loan_number,
1226 x_loan_amt_offered,
1227 x_loan_amt_accepted,
1228 x_loan_per_begin_date,
1229 x_loan_per_end_date,
1230 x_person_id,
1231 x_s_ssn,
1232 x_s_first_name,
1233 x_s_last_name,
1234 x_s_middle_name,
1235 x_s_date_of_birth,
1236 x_s_citizenship_status,
1237 x_s_alien_reg_number,
1238 x_s_license_num,
1239 x_s_license_state,
1240 x_s_permt_addr1,
1241 x_s_permt_addr2,
1242 x_s_permt_city,
1243 x_s_permt_state,
1244 x_s_permt_province,
1245 x_s_permt_county,
1246 x_s_permt_country,
1247 x_s_permt_zip,
1248 x_s_email_addr,
1249 x_s_phone,
1250 x_p_person_id,
1251 x_p_ssn,
1252 x_p_last_name,
1253 x_p_first_name,
1254 x_p_middle_name,
1255 x_p_date_of_birth,
1256 x_p_citizenship_status,
1257 x_p_alien_reg_num,
1258 x_p_license_num,
1259 x_p_license_state,
1260 x_p_permt_addr1,
1261 x_p_permt_addr2,
1262 x_p_permt_city,
1263 x_p_permt_state,
1264 x_p_permt_province,
1265 x_p_permt_county,
1266 x_p_permt_country,
1267 x_p_permt_zip,
1268 x_p_email_addr,
1269 x_p_phone,
1270 x_status,
1271 x_mode
1272 );
1273 RETURN;
1274 END IF;
1275 CLOSE c1;
1276
1277 update_row (
1278 x_rowid,
1279 x_pnpp_id,
1280 x_batch_seq_num,
1281 x_loan_id,
1282 x_loan_number,
1283 x_loan_amt_offered,
1284 x_loan_amt_accepted,
1285 x_loan_per_begin_date,
1286 x_loan_per_end_date,
1287 x_person_id,
1288 x_s_ssn,
1289 x_s_first_name,
1290 x_s_last_name,
1291 x_s_middle_name,
1292 x_s_date_of_birth,
1293 x_s_citizenship_status,
1294 x_s_alien_reg_number,
1295 x_s_license_num,
1296 x_s_license_state,
1297 x_s_permt_addr1,
1298 x_s_permt_addr2,
1299 x_s_permt_city,
1300 x_s_permt_state,
1301 x_s_permt_province,
1302 x_s_permt_county,
1303 x_s_permt_country,
1307 x_p_person_id,
1304 x_s_permt_zip,
1305 x_s_email_addr,
1306 x_s_phone,
1308 x_p_ssn,
1309 x_p_last_name,
1310 x_p_first_name,
1311 x_p_middle_name,
1312 x_p_date_of_birth,
1313 x_p_citizenship_status,
1314 x_p_alien_reg_num,
1315 x_p_license_num,
1316 x_p_license_state,
1317 x_p_permt_addr1,
1318 x_p_permt_addr2,
1319 x_p_permt_city,
1320 x_p_permt_state,
1321 x_p_permt_province,
1322 x_p_permt_county,
1323 x_p_permt_country,
1324 x_p_permt_zip,
1325 x_p_email_addr,
1326 x_p_phone,
1327 x_status,
1328 x_mode
1329 );
1330
1331 END add_row;
1332
1333
1334 PROCEDURE delete_row (
1335 x_rowid IN VARCHAR2
1336 ) AS
1337 /*
1338 || Created By : viramali
1339 || Created On : 10-MAY-2001
1340 || Purpose : Handles the DELETE DML logic for the table.
1341 || Known limitations, enhancements or remarks :
1342 || Change History :
1343 || Who When What
1344 || (reverse chronological order - newest change first)
1345 */
1346 BEGIN
1347
1348 before_dml (
1349 p_action => 'DELETE',
1350 x_rowid => x_rowid
1351 );
1352
1353 DELETE FROM igf_sl_dl_pnote_p_p_all
1354 WHERE rowid = x_rowid;
1355
1356 IF (SQL%NOTFOUND) THEN
1357 RAISE NO_DATA_FOUND;
1358 END IF;
1359
1360 END delete_row;
1361
1362
1363 END igf_sl_dl_pnote_p_p_pkg;