[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_CHG_RESP_PKG
Source
1 PACKAGE BODY igf_sl_dl_chg_resp_pkg AS
2 /* $Header: IGFLI18B.pls 115.9 2002/11/28 14:25:33 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_dl_chg_resp_all%ROWTYPE;
6 new_references igf_sl_dl_chg_resp_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_resp_num IN NUMBER DEFAULT NULL,
12 x_dbth_id IN NUMBER DEFAULT NULL,
13 x_batch_id IN VARCHAR2 DEFAULT NULL,
14 x_loan_number IN VARCHAR2 DEFAULT NULL,
15 x_chg_code IN VARCHAR2 DEFAULT NULL,
16 x_reject_code IN VARCHAR2 DEFAULT NULL,
17 x_new_value IN VARCHAR2 DEFAULT NULL,
18 x_loan_ident_err_code 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 : sjadhav
28 || Created On : 02-NOV-2000
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_chg_resp_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.resp_num := x_resp_num;
60 new_references.dbth_id := x_dbth_id;
61 new_references.batch_id := x_batch_id;
62 new_references.loan_number := x_loan_number;
63 new_references.chg_code := x_chg_code;
64 new_references.reject_code := x_reject_code;
65 new_references.new_value := x_new_value;
66 new_references.loan_ident_err_code := x_loan_ident_err_code;
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 : sjadhav
87 || Created On : 02-NOV-2000
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.dbth_id = new_references.dbth_id)) OR
97 ((new_references.dbth_id IS NULL))) THEN
98 NULL;
99 ELSIF NOT igf_sl_dl_batch_pkg.get_pk_for_validation (
100 new_references.dbth_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_resp_num IN NUMBER
112 ) RETURN BOOLEAN AS
113 /*
114 || Created By : sjadhav
115 || Created On : 02-NOV-2000
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_chg_resp_all
125 WHERE resp_num = x_resp_num
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_dl_batch (
146 x_dbth_id IN NUMBER
147 ) AS
148 /*
149 || Created By : sjadhav
150 || Created On : 02-NOV-2000
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_chg_resp_all
160 WHERE ((dbth_id = x_dbth_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_RCHG_DBTH_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_dl_batch;
178
179
180 PROCEDURE before_dml (
181 p_action IN VARCHAR2,
182 x_rowid IN VARCHAR2 DEFAULT NULL,
183 x_resp_num IN NUMBER DEFAULT NULL,
184 x_dbth_id IN NUMBER DEFAULT NULL,
185 x_batch_id IN VARCHAR2 DEFAULT NULL,
186 x_loan_number IN VARCHAR2 DEFAULT NULL,
187 x_chg_code IN VARCHAR2 DEFAULT NULL,
188 x_reject_code IN VARCHAR2 DEFAULT NULL,
189 x_new_value IN VARCHAR2 DEFAULT NULL,
190 x_loan_ident_err_code 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 : sjadhav
200 || Created On : 02-NOV-2000
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_resp_num,
214 x_dbth_id,
215 x_batch_id,
216 x_loan_number,
217 x_chg_code,
218 x_reject_code,
219 x_new_value,
220 x_loan_ident_err_code,
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.resp_num
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.resp_num
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_resp_num IN OUT NOCOPY NUMBER,
261 x_dbth_id IN NUMBER,
262 x_batch_id IN VARCHAR2,
263 x_loan_number IN VARCHAR2,
264 x_chg_code IN VARCHAR2,
265 x_reject_code IN VARCHAR2,
266 x_new_value IN VARCHAR2,
267 x_loan_ident_err_code IN VARCHAR2,
268 x_status IN VARCHAR2,
269 x_mode IN VARCHAR2 DEFAULT 'R'
270 ) AS
271 /*
272 || Created By : sjadhav
273 || Created On : 02-NOV-2000
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_chg_resp_all
283 WHERE resp_num = x_resp_num;
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 l_org_id igf_sl_dl_chg_resp_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
293
294 BEGIN
295
296 x_last_update_date := SYSDATE;
297 IF (x_mode = 'I') THEN
298 x_last_updated_by := 1;
299 x_last_update_login := 0;
300 ELSIF (x_mode = 'R') THEN
301 x_last_updated_by := fnd_global.user_id;
302 IF (x_last_updated_by IS NULL) THEN
303 x_last_updated_by := -1;
304 END IF;
305 x_last_update_login := fnd_global.login_id;
306 IF (x_last_update_login IS NULL) THEN
307 x_last_update_login := -1;
308 END IF;
309 x_request_id := fnd_global.conc_request_id;
310 x_program_id := fnd_global.conc_program_id;
311 x_program_application_id := fnd_global.prog_appl_id;
312
313 IF (x_request_id = -1) THEN
314 x_request_id := NULL;
315 x_program_id := NULL;
316 x_program_application_id := NULL;
317 x_program_update_date := NULL;
318 ELSE
319 x_program_update_date := SYSDATE;
320 END IF;
321 ELSE
322 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326
327 SELECT igf_sl_dl_chg_resp_s.nextval INTO
328 x_resp_num FROM dual;
332 p_action => 'INSERT',
329
330
331 before_dml(
333 x_rowid => x_rowid,
334 x_resp_num => x_resp_num,
335 x_dbth_id => x_dbth_id,
336 x_batch_id => x_batch_id,
337 x_loan_number => x_loan_number,
338 x_chg_code => x_chg_code,
339 x_reject_code => x_reject_code,
340 x_new_value => x_new_value,
341 x_loan_ident_err_code => x_loan_ident_err_code,
342 x_status => x_status,
343 x_creation_date => x_last_update_date,
344 x_created_by => x_last_updated_by,
345 x_last_update_date => x_last_update_date,
346 x_last_updated_by => x_last_updated_by,
347 x_last_update_login => x_last_update_login
348 );
349
350 INSERT INTO igf_sl_dl_chg_resp_all (
351 resp_num,
352 dbth_id,
353 batch_id,
354 loan_number,
355 chg_code,
356 reject_code,
357 new_value,
358 loan_ident_err_code,
359 status,
360 creation_date,
361 created_by,
362 last_update_date,
363 last_updated_by,
364 last_update_login,
365 request_id,
366 program_id,
367 program_application_id,
368 program_update_date,
369 org_id
370 ) VALUES (
371 new_references.resp_num,
372 new_references.dbth_id,
373 new_references.batch_id,
374 new_references.loan_number,
375 new_references.chg_code,
376 new_references.reject_code,
377 new_references.new_value,
378 new_references.loan_ident_err_code,
379 new_references.status,
380 x_last_update_date,
381 x_last_updated_by,
382 x_last_update_date,
383 x_last_updated_by,
384 x_last_update_login ,
385 x_request_id,
386 x_program_id,
387 x_program_application_id,
388 x_program_update_date,
389 l_org_id
390 );
391
392 OPEN c;
393 FETCH c INTO x_rowid;
394 IF (c%NOTFOUND) THEN
395 CLOSE c;
396 RAISE NO_DATA_FOUND;
397 END IF;
398 CLOSE c;
399
400 END insert_row;
401
402
403 PROCEDURE lock_row (
404 x_rowid IN VARCHAR2,
405 x_resp_num IN NUMBER,
406 x_dbth_id IN NUMBER,
407 x_batch_id IN VARCHAR2,
408 x_loan_number IN VARCHAR2,
409 x_chg_code IN VARCHAR2,
410 x_reject_code IN VARCHAR2,
411 x_new_value IN VARCHAR2,
412 x_loan_ident_err_code IN VARCHAR2,
413 x_status IN VARCHAR2
414 ) AS
415 /*
416 || Created By : sjadhav
417 || Created On : 02-NOV-2000
418 || Purpose : Handles the LOCK mechanism for the table.
419 || Known limitations, enhancements or remarks :
420 || Change History :
421 || Who When What
422 || (reverse chronological order - newest change first)
423 */
424 CURSOR c1 IS
425 SELECT
426 dbth_id,
427 batch_id,
428 loan_number,
429 chg_code,
430 reject_code,
431 new_value,
432 loan_ident_err_code,
433 status,
434 org_id
435 FROM igf_sl_dl_chg_resp_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.dbth_id = x_dbth_id)
456 AND (tlinfo.batch_id = x_batch_id)
457 AND (tlinfo.loan_number = x_loan_number)
458 AND (tlinfo.chg_code = x_chg_code)
459 AND ((tlinfo.reject_code = x_reject_code) OR ((tlinfo.reject_code IS NULL) AND (X_reject_code IS NULL)))
460 AND ((tlinfo.new_value = x_new_value) OR ((tlinfo.new_value IS NULL) AND (X_new_value IS NULL)))
461 AND ((tlinfo.loan_ident_err_code = x_loan_ident_err_code) OR ((tlinfo.loan_ident_err_code IS NULL) AND (X_loan_ident_err_code IS NULL)))
462 AND (tlinfo.status = x_status)
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 (
480 x_batch_id IN VARCHAR2,
477 x_rowid IN VARCHAR2,
478 x_resp_num IN NUMBER,
479 x_dbth_id IN NUMBER,
481 x_loan_number IN VARCHAR2,
482 x_chg_code IN VARCHAR2,
483 x_reject_code IN VARCHAR2,
484 x_new_value IN VARCHAR2,
485 x_loan_ident_err_code IN VARCHAR2,
486 x_status IN VARCHAR2,
487 x_mode IN VARCHAR2 DEFAULT 'R'
488 ) AS
489 /*
490 || Created By : sjadhav
491 || Created On : 02-NOV-2000
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_resp_num => x_resp_num,
531 x_dbth_id => x_dbth_id,
532 x_batch_id => x_batch_id,
533 x_loan_number => x_loan_number,
534 x_chg_code => x_chg_code,
535 x_reject_code => x_reject_code,
536 x_new_value => x_new_value,
537 x_loan_ident_err_code => x_loan_ident_err_code,
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_chg_resp_all
561 SET
562 dbth_id = new_references.dbth_id,
563 batch_id = new_references.batch_id,
564 loan_number = new_references.loan_number,
565 chg_code = new_references.chg_code,
566 reject_code = new_references.reject_code,
567 new_value = new_references.new_value,
568 loan_ident_err_code = new_references.loan_ident_err_code,
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 ,
573 request_id = x_request_id,
574 program_id = x_program_id,
575 program_application_id = x_program_application_id,
576 program_update_date = x_program_update_date
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_resp_num IN OUT NOCOPY NUMBER,
589 x_dbth_id IN NUMBER,
590 x_batch_id IN VARCHAR2,
591 x_loan_number IN VARCHAR2,
592 x_chg_code IN VARCHAR2,
593 x_reject_code IN VARCHAR2,
594 x_new_value IN VARCHAR2,
595 x_loan_ident_err_code IN VARCHAR2,
596 x_status IN VARCHAR2,
597 x_mode IN VARCHAR2 DEFAULT 'R'
598 ) AS
599 /*
600 || Created By : sjadhav
601 || Created On : 02-NOV-2000
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_chg_resp_all
611 WHERE resp_num = x_resp_num;
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_resp_num,
623 x_dbth_id,
624 x_batch_id,
625 x_loan_number,
626 x_chg_code,
627 x_reject_code,
628 x_new_value,
629 x_loan_ident_err_code,
630 x_status,
631 x_mode
632 );
633 RETURN;
634 END IF;
635 CLOSE c1;
636
637 update_row (
638 x_rowid,
639 x_resp_num,
640 x_dbth_id,
641 x_batch_id,
642 x_loan_number,
643 x_chg_code,
644 x_reject_code,
645 x_new_value,
646 x_loan_ident_err_code,
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 : sjadhav
659 || Created On : 02-NOV-2000
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_chg_resp_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_chg_resp_pkg;