[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_DB_CHG_DTLS_PKG
Source
1 PACKAGE BODY igf_aw_db_chg_dtls_pkg AS
2 /* $Header: IGFWI64B.pls 120.1 2005/09/28 05:31:35 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_db_chg_dtls%ROWTYPE;
6 new_references igf_aw_db_chg_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_award_id IN NUMBER,
12 x_disb_num IN NUMBER,
13 x_disb_seq_num IN NUMBER,
14 x_disb_accepted_amt IN NUMBER,
15 x_orig_fee_amt IN NUMBER,
16 x_disb_net_amt IN NUMBER,
17 x_disb_date IN DATE,
18 x_disb_activity IN VARCHAR2,
19 x_disb_status IN VARCHAR2,
20 x_disb_status_date IN DATE,
21 x_disb_rel_flag IN VARCHAR2,
22 x_first_disb_flag IN VARCHAR2,
23 x_interest_rebate_amt IN NUMBER,
24 x_disb_conf_flag IN VARCHAR2,
25 x_pymnt_prd_start_date IN DATE,
26 x_note_message IN VARCHAR2,
27 x_batch_id_txt IN VARCHAR2,
28 x_ack_date IN DATE,
29 x_booking_id_txt IN VARCHAR2,
30 x_booking_date IN DATE,
31 x_creation_date IN DATE,
32 x_created_by IN NUMBER,
33 x_last_update_date IN DATE,
34 x_last_updated_by IN NUMBER,
35 x_last_update_login IN NUMBER
36 ) AS
37 /*
38 || Created By : [email protected]
39 || Created On : 29-SEP-2004
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_aw_db_chg_dtls
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.award_id := x_award_id;
71 new_references.disb_num := x_disb_num;
72 new_references.disb_seq_num := x_disb_seq_num;
73 new_references.disb_accepted_amt := x_disb_accepted_amt;
74 new_references.orig_fee_amt := x_orig_fee_amt;
75 new_references.disb_net_amt := x_disb_net_amt;
76 new_references.disb_date := x_disb_date;
77 new_references.disb_activity := x_disb_activity;
78 new_references.disb_status := x_disb_status;
79 new_references.disb_status_date := x_disb_status_date;
80 new_references.disb_rel_flag := x_disb_rel_flag;
81 new_references.first_disb_flag := x_first_disb_flag;
82 new_references.interest_rebate_amt := x_interest_rebate_amt;
83 new_references.disb_conf_flag := x_disb_conf_flag;
84 new_references.pymnt_prd_start_date := x_pymnt_prd_start_date;
85 new_references.note_message := x_note_message;
86 new_references.batch_id_txt := x_batch_id_txt;
87 new_references.ack_date := x_ack_date;
88 new_references.booking_id_txt := x_booking_id_txt;
89 new_references.booking_date := x_booking_date;
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 BEGIN
109 IF (((old_references.award_id = new_references.award_id)) OR
110 ((new_references.award_id IS NULL))) THEN
111 NULL;
112 ELSIF NOT igf_aw_award_pkg.get_pk_for_validation (
113 new_references.award_id
114 ) THEN
115 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119 END check_parent_existance;
120
121
122 FUNCTION get_pk_for_validation (
123 x_award_id IN NUMBER,
124 x_disb_num IN NUMBER,
125 x_disb_seq_num IN NUMBER
126 ) RETURN BOOLEAN AS
127 /*
128 || Created By : [email protected]
129 || Created On : 29-SEP-2004
130 || Purpose : Validates the Primary Key of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || (reverse chronological order - newest change first)
135 */
136 CURSOR cur_rowid IS
137 SELECT rowid
138 FROM igf_aw_db_chg_dtls
139 WHERE award_id = x_award_id
140 AND disb_num = x_disb_num
141 AND disb_seq_num = x_disb_seq_num
142 FOR UPDATE NOWAIT;
143
144 lv_rowid cur_rowid%RowType;
145
146 BEGIN
147
148 OPEN cur_rowid;
149 FETCH cur_rowid INTO lv_rowid;
150 IF (cur_rowid%FOUND) THEN
151 CLOSE cur_rowid;
152 RETURN(TRUE);
153 ELSE
154 CLOSE cur_rowid;
155 RETURN(FALSE);
156 END IF;
157
158 END get_pk_for_validation;
159
160 PROCEDURE get_fk_igf_aw_award (
161 x_award_id IN NUMBER
162 ) AS
163 /*
164 || Created By : [email protected]
165 || Created On : 29-SEP-2004
166 || Purpose : Validates the Foreign Keys for the table.
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || Who When What
170 || (reverse chronological order - newest change first)
171 */
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM igf_aw_db_chg_dtls
175 WHERE ((award_id = x_award_id));
176
177 lv_rowid cur_rowid%RowType;
178
179 BEGIN
180
181 OPEN cur_rowid;
182 FETCH cur_rowid INTO lv_rowid;
183 IF (cur_rowid%FOUND) THEN
184 CLOSE cur_rowid;
185 fnd_message.set_name ('IGF', 'IGF_AW_AWD_DBCHG_FK');
186 igs_ge_msg_stack.add;
187 app_exception.raise_exception;
188 RETURN;
189 END IF;
190 CLOSE cur_rowid;
191
192 END get_fk_igf_aw_award;
193
194
195 PROCEDURE get_fk_igf_aw_awd_disb (
196 x_award_id IN NUMBER,
197 x_disb_num IN NUMBER
198 ) AS
199 /*
200 || Created By : [email protected]
201 || Created On : 29-SEP-2004
202 || Purpose : Validates the Foreign Keys for the table.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || (reverse chronological order - newest change first)
207 */
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM igf_aw_db_chg_dtls
211 WHERE award_id = x_award_id
212 AND disb_num = x_disb_num;
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222 fnd_message.set_name ('IGF', 'IGF_DB_DISB_DBCHG_FK');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 RETURN;
226 END IF;
227 CLOSE cur_rowid;
228
229 END get_fk_igf_aw_awd_disb;
230
231 PROCEDURE before_dml (
232 p_action IN VARCHAR2,
233 x_rowid IN VARCHAR2,
234 x_award_id IN NUMBER,
235 x_disb_num IN NUMBER,
236 x_disb_seq_num IN NUMBER,
237 x_disb_accepted_amt IN NUMBER,
238 x_orig_fee_amt IN NUMBER,
239 x_disb_net_amt IN NUMBER,
240 x_disb_date IN DATE,
241 x_disb_activity IN VARCHAR2,
242 x_disb_status IN VARCHAR2,
243 x_disb_status_date IN DATE,
244 x_disb_rel_flag IN VARCHAR2,
245 x_first_disb_flag IN VARCHAR2,
246 x_interest_rebate_amt IN NUMBER,
247 x_disb_conf_flag IN VARCHAR2,
248 x_pymnt_prd_start_date IN DATE,
249 x_note_message IN VARCHAR2,
250 x_batch_id_txt IN VARCHAR2,
251 x_ack_date IN DATE,
252 x_booking_id_txt IN VARCHAR2,
253 x_booking_date IN DATE,
254 x_creation_date IN DATE,
255 x_created_by IN NUMBER,
256 x_last_update_date IN DATE,
257 x_last_updated_by IN NUMBER,
258 x_last_update_login IN NUMBER
259 ) AS
260 /*
261 || Created By : [email protected]
262 || Created On : 29-SEP-2004
263 || Purpose : Initialises the columns, Checks Constraints, Calls the
264 || Trigger Handlers for the table, before any DML operation.
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || (reverse chronological order - newest change first)
269 */
270 BEGIN
271
272 set_column_values (
273 p_action,
274 x_rowid,
275 x_award_id,
276 x_disb_num,
277 x_disb_seq_num,
278 x_disb_accepted_amt,
279 x_orig_fee_amt,
280 x_disb_net_amt,
281 x_disb_date,
282 x_disb_activity,
283 x_disb_status,
284 x_disb_status_date,
285 x_disb_rel_flag,
286 x_first_disb_flag,
287 x_interest_rebate_amt,
288 x_disb_conf_flag,
289 x_pymnt_prd_start_date,
290 x_note_message,
291 x_batch_id_txt,
292 x_ack_date,
293 x_booking_id_txt,
294 x_booking_date,
295 x_creation_date,
296 x_created_by,
297 x_last_update_date,
298 x_last_updated_by,
299 x_last_update_login
300 );
301
302 IF (p_action = 'INSERT') THEN
303 -- Call all the procedures related to Before Insert.
304 IF ( get_pk_for_validation(
305 new_references.award_id,
306 new_references.disb_num,
307 new_references.disb_seq_num
308 )
309 ) THEN
310 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313 END IF;
314 check_parent_existance;
315
316 ELSIF (p_action = 'UPDATE') THEN
317 -- Call all the procedures related to Before Update.
318 check_parent_existance;
319
320 ELSIF (p_action = 'VALIDATE_INSERT') THEN
321 -- Call all the procedures related to Before Insert.
322 IF ( get_pk_for_validation (
323 new_references.award_id,
324 new_references.disb_num,
325 new_references.disb_seq_num
326 )
327 ) THEN
328 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 END IF;
332 END IF;
333
334 END before_dml;
335
336
337 PROCEDURE insert_row (
338 x_rowid IN OUT NOCOPY VARCHAR2,
339 x_award_id IN NUMBER,
340 x_disb_num IN NUMBER,
341 x_disb_seq_num IN NUMBER,
342 x_disb_accepted_amt IN NUMBER,
343 x_orig_fee_amt IN NUMBER,
344 x_disb_net_amt IN NUMBER,
345 x_disb_date IN DATE,
346 x_disb_activity IN VARCHAR2,
347 x_disb_status IN VARCHAR2,
348 x_disb_status_date IN DATE,
349 x_disb_rel_flag IN VARCHAR2,
350 x_first_disb_flag IN VARCHAR2,
351 x_interest_rebate_amt IN NUMBER,
352 x_disb_conf_flag IN VARCHAR2,
353 x_pymnt_prd_start_date IN DATE,
354 x_note_message IN VARCHAR2,
355 x_batch_id_txt IN VARCHAR2,
356 x_ack_date IN DATE,
357 x_booking_id_txt IN VARCHAR2,
358 x_booking_date IN DATE,
359 x_mode IN VARCHAR2
360 ) AS
361 /*
362 || Created By : [email protected]
363 || Created On : 29-SEP-2004
364 || Purpose : Handles the INSERT DML logic for the table.
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || (reverse chronological order - newest change first)
369 */
370
371 CURSOR c IS
372 SELECT rowid
373 FROM igf_aw_db_chg_dtls
374 WHERE award_id = x_award_id
375 AND disb_num = x_disb_num
376 AND disb_seq_num = x_disb_seq_num;
377
378 x_last_update_date DATE;
379 x_last_updated_by NUMBER;
380 x_last_update_login NUMBER;
381 x_request_id NUMBER;
382 x_program_id NUMBER;
383 x_program_application_id NUMBER;
384 x_program_update_date DATE;
385
386 BEGIN
387
388 x_last_update_date := SYSDATE;
389 IF (x_mode = 'I') THEN
390 x_last_updated_by := 1;
391 x_last_update_login := 0;
392 ELSIF (x_mode = 'R') THEN
393 x_last_updated_by := fnd_global.user_id;
394 IF (x_last_updated_by IS NULL) THEN
395 x_last_updated_by := -1;
396 END IF;
397 x_last_update_login := fnd_global.login_id;
398 IF (x_last_update_login IS NULL) THEN
399 x_last_update_login := -1;
400 END IF;
401 x_request_id := fnd_global.conc_request_id;
402 x_program_id := fnd_global.conc_program_id;
403 x_program_application_id := fnd_global.prog_appl_id;
404
405 IF (x_request_id = -1) THEN
406 x_request_id := NULL;
407 x_program_id := NULL;
408 x_program_application_id := NULL;
409 x_program_update_date := NULL;
410 ELSE
411 x_program_update_date := SYSDATE;
412 END IF;
413 ELSE
414 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
415 fnd_message.set_token ('ROUTINE', 'IGF_AW_DB_CHG_DTLS_PKG.INSERT_ROW');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419
420 before_dml(
421 p_action => 'INSERT',
422 x_rowid => x_rowid,
423 x_award_id => x_award_id,
424 x_disb_num => x_disb_num,
425 x_disb_seq_num => x_disb_seq_num,
426 x_disb_accepted_amt => x_disb_accepted_amt,
427 x_orig_fee_amt => x_orig_fee_amt,
428 x_disb_net_amt => x_disb_net_amt,
429 x_disb_date => x_disb_date,
430 x_disb_activity => x_disb_activity,
431 x_disb_status => x_disb_status,
432 x_disb_status_date => x_disb_status_date,
433 x_disb_rel_flag => x_disb_rel_flag,
434 x_first_disb_flag => x_first_disb_flag,
435 x_interest_rebate_amt => x_interest_rebate_amt,
436 x_disb_conf_flag => x_disb_conf_flag,
437 x_pymnt_prd_start_date => x_pymnt_prd_start_date,
438 x_note_message => x_note_message,
439 x_batch_id_txt => x_batch_id_txt,
440 x_ack_date => x_ack_date,
441 x_booking_id_txt => x_booking_id_txt,
442 x_booking_date => x_booking_date,
443 x_creation_date => x_last_update_date,
444 x_created_by => x_last_updated_by,
445 x_last_update_date => x_last_update_date,
446 x_last_updated_by => x_last_updated_by,
447 x_last_update_login => x_last_update_login
448 );
449
450 INSERT INTO igf_aw_db_chg_dtls (
451 award_id,
452 disb_num,
453 disb_seq_num,
454 disb_accepted_amt,
455 orig_fee_amt,
456 disb_net_amt,
457 disb_date,
458 disb_activity,
459 disb_status,
460 disb_status_date,
461 disb_rel_flag,
462 first_disb_flag,
463 interest_rebate_amt,
464 disb_conf_flag,
465 pymnt_prd_start_date,
466 note_message,
467 batch_id_txt,
468 ack_date,
469 booking_id_txt,
470 booking_date,
471 creation_date,
472 created_by,
473 last_update_date,
474 last_updated_by,
475 last_update_login,
476 request_id,
477 program_id,
478 program_application_id,
479 program_update_date
480 ) VALUES (
481 new_references.award_id,
482 new_references.disb_num,
483 new_references.disb_seq_num,
484 new_references.disb_accepted_amt,
485 new_references.orig_fee_amt,
486 new_references.disb_net_amt,
487 new_references.disb_date,
488 new_references.disb_activity,
489 new_references.disb_status,
490 new_references.disb_status_date,
491 NVL(new_references.disb_rel_flag, 'FALSE'),
492 new_references.first_disb_flag,
493 new_references.interest_rebate_amt,
494 new_references.disb_conf_flag,
495 new_references.pymnt_prd_start_date,
496 new_references.note_message,
497 new_references.batch_id_txt,
498 new_references.ack_date,
499 new_references.booking_id_txt,
500 new_references.booking_date,
501 x_last_update_date,
502 x_last_updated_by,
503 x_last_update_date,
504 x_last_updated_by,
505 x_last_update_login ,
506 x_request_id,
507 x_program_id,
508 x_program_application_id,
509 x_program_update_date
510 ) RETURNING ROWID INTO x_rowid;
511
512
513 OPEN c;
514 FETCH c INTO x_rowid;
515 IF (c%NOTFOUND) THEN
516 CLOSE c;
517 RAISE NO_DATA_FOUND;
518 END IF;
519 CLOSE c;
520
521
522 END insert_row;
523
524
525 PROCEDURE lock_row (
526 x_rowid IN VARCHAR2,
527 x_award_id IN NUMBER,
528 x_disb_num IN NUMBER,
529 x_disb_seq_num IN NUMBER,
530 x_disb_accepted_amt IN NUMBER,
531 x_orig_fee_amt IN NUMBER,
532 x_disb_net_amt IN NUMBER,
533 x_disb_date IN DATE,
534 x_disb_activity IN VARCHAR2,
535 x_disb_status IN VARCHAR2,
536 x_disb_status_date IN DATE,
537 x_disb_rel_flag IN VARCHAR2,
538 x_first_disb_flag IN VARCHAR2,
539 x_interest_rebate_amt IN NUMBER,
540 x_disb_conf_flag IN VARCHAR2,
541 x_pymnt_prd_start_date IN DATE,
542 x_note_message IN VARCHAR2,
543 x_batch_id_txt IN VARCHAR2,
544 x_ack_date IN DATE,
545 x_booking_id_txt IN VARCHAR2,
546 x_booking_date IN DATE
547 ) AS
548 /*
549 || Created By : [email protected]
550 || Created On : 29-SEP-2004
551 || Purpose : Handles the LOCK mechanism for the table.
552 || Known limitations, enhancements or remarks :
553 || Change History :
554 || Who When What
555 || (reverse chronological order - newest change first)
556 */
557 CURSOR c1 IS
558 SELECT
559 disb_accepted_amt,
560 orig_fee_amt,
561 disb_net_amt,
562 disb_date,
563 disb_activity,
564 disb_status,
565 disb_status_date,
566 disb_rel_flag,
567 first_disb_flag,
568 interest_rebate_amt,
569 disb_conf_flag,
570 pymnt_prd_start_date,
571 note_message,
572 batch_id_txt,
573 ack_date,
574 booking_id_txt,
575 booking_date
576 FROM igf_aw_db_chg_dtls
577 WHERE rowid = x_rowid
578 FOR UPDATE NOWAIT;
579
580 tlinfo c1%ROWTYPE;
581
582 BEGIN
583
584 OPEN c1;
585 FETCH c1 INTO tlinfo;
586 IF (c1%notfound) THEN
587 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
588 igs_ge_msg_stack.add;
589 CLOSE c1;
590 app_exception.raise_exception;
591 RETURN;
592 END IF;
593 CLOSE c1;
594
595 IF (
596 (tlinfo.disb_accepted_amt = x_disb_accepted_amt)
597 AND ((tlinfo.orig_fee_amt = x_orig_fee_amt) OR ((tlinfo.orig_fee_amt IS NULL) AND (X_orig_fee_amt IS NULL)))
598 AND ((tlinfo.disb_net_amt = x_disb_net_amt) OR ((tlinfo.disb_net_amt IS NULL) AND (X_disb_net_amt IS NULL)))
599 AND ((tlinfo.disb_date = x_disb_date) OR ((tlinfo.disb_date IS NULL) AND (X_disb_date IS NULL)))
600 AND ((tlinfo.disb_activity = x_disb_activity) OR ((tlinfo.disb_activity IS NULL) AND (X_disb_activity IS NULL)))
601 AND ((tlinfo.disb_status = x_disb_status) OR ((tlinfo.disb_status IS NULL) AND (X_disb_status IS NULL)))
602 AND ((tlinfo.disb_status_date = x_disb_status_date) OR ((tlinfo.disb_status_date IS NULL) AND (X_disb_status_date IS NULL)))
603 AND ((tlinfo.disb_rel_flag = x_disb_rel_flag) OR ((tlinfo.disb_rel_flag IS NULL) AND (X_disb_rel_flag IS NULL)))
604 AND ((tlinfo.first_disb_flag = x_first_disb_flag) OR ((tlinfo.first_disb_flag IS NULL) AND (X_first_disb_flag IS NULL)))
605 AND ((tlinfo.interest_rebate_amt = x_interest_rebate_amt) OR ((tlinfo.interest_rebate_amt IS NULL) AND (X_interest_rebate_amt IS NULL)))
606 AND ((tlinfo.disb_conf_flag = x_disb_conf_flag) OR ((tlinfo.disb_conf_flag IS NULL) AND (X_disb_conf_flag IS NULL)))
607 AND ((tlinfo.pymnt_prd_start_date = x_pymnt_prd_start_date) OR ((tlinfo.pymnt_prd_start_date IS NULL) AND (X_pymnt_prd_start_date IS NULL)))
608 AND ((tlinfo.note_message = x_note_message) OR ((tlinfo.note_message IS NULL) AND (X_note_message IS NULL)))
609 AND ((tlinfo.batch_id_txt = x_batch_id_txt) OR ((tlinfo.batch_id_txt IS NULL) AND (X_batch_id_txt IS NULL)))
610 AND ((tlinfo.ack_date = x_ack_date) OR ((tlinfo.ack_date IS NULL) AND (X_ack_date IS NULL)))
611 AND ((tlinfo.booking_id_txt = x_booking_id_txt) OR ((tlinfo.booking_id_txt IS NULL) AND (X_booking_id_txt IS NULL)))
612 AND ((tlinfo.booking_date = x_booking_date) OR ((tlinfo.booking_date IS NULL) AND (X_booking_date IS NULL)))
613 ) THEN
614 NULL;
615 ELSE
616 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
617 igs_ge_msg_stack.add;
618 app_exception.raise_exception;
619 END IF;
620
621 RETURN;
622
623 END lock_row;
624
625
626 PROCEDURE update_row (
627 x_rowid IN VARCHAR2,
628 x_award_id IN NUMBER,
629 x_disb_num IN NUMBER,
630 x_disb_seq_num IN NUMBER,
631 x_disb_accepted_amt IN NUMBER,
632 x_orig_fee_amt IN NUMBER,
633 x_disb_net_amt IN NUMBER,
634 x_disb_date IN DATE,
635 x_disb_activity IN VARCHAR2,
636 x_disb_status IN VARCHAR2,
637 x_disb_status_date IN DATE,
638 x_disb_rel_flag IN VARCHAR2,
639 x_first_disb_flag IN VARCHAR2,
640 x_interest_rebate_amt IN NUMBER,
641 x_disb_conf_flag IN VARCHAR2,
642 x_pymnt_prd_start_date IN DATE,
643 x_note_message IN VARCHAR2,
644 x_batch_id_txt IN VARCHAR2,
645 x_ack_date IN DATE,
646 x_booking_id_txt IN VARCHAR2,
647 x_booking_date IN DATE,
648 x_mode IN VARCHAR2
649 ) AS
650 /*
651 || Created By : [email protected]
652 || Created On : 29-SEP-2004
653 || Purpose : Handles the UPDATE DML logic for the table.
654 || Known limitations, enhancements or remarks :
655 || Change History :
656 || Who When What
657 || (reverse chronological order - newest change first)
658 */
659 x_last_update_date DATE ;
660 x_last_updated_by NUMBER;
661 x_last_update_login NUMBER;
662 x_request_id NUMBER;
663 x_program_id NUMBER;
664 x_program_application_id NUMBER;
665 x_program_update_date DATE;
666
667 BEGIN
668
669 x_last_update_date := SYSDATE;
670 IF (X_MODE = 'I') THEN
671 x_last_updated_by := 1;
672 x_last_update_login := 0;
673 ELSIF (x_mode = 'R') THEN
674 x_last_updated_by := fnd_global.user_id;
675 IF x_last_updated_by IS NULL THEN
676 x_last_updated_by := -1;
677 END IF;
678 x_last_update_login := fnd_global.login_id;
679 IF (x_last_update_login IS NULL) THEN
680 x_last_update_login := -1;
681 END IF;
682 ELSE
683 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
684 fnd_message.set_token ('ROUTINE', 'IGF_AW_DB_CHG_DTLS_PKG.UPDATE_ROW');
685 igs_ge_msg_stack.add;
686 app_exception.raise_exception;
687 END IF;
688
689 before_dml(
690 p_action => 'UPDATE',
691 x_rowid => x_rowid,
692 x_award_id => x_award_id,
693 x_disb_num => x_disb_num,
694 x_disb_seq_num => x_disb_seq_num,
695 x_disb_accepted_amt => x_disb_accepted_amt,
696 x_orig_fee_amt => x_orig_fee_amt,
697 x_disb_net_amt => x_disb_net_amt,
698 x_disb_date => x_disb_date,
699 x_disb_activity => x_disb_activity,
700 x_disb_status => x_disb_status,
701 x_disb_status_date => x_disb_status_date,
702 x_disb_rel_flag => x_disb_rel_flag,
703 x_first_disb_flag => x_first_disb_flag,
704 x_interest_rebate_amt => x_interest_rebate_amt,
705 x_disb_conf_flag => x_disb_conf_flag,
706 x_pymnt_prd_start_date => x_pymnt_prd_start_date,
707 x_note_message => x_note_message,
708 x_batch_id_txt => x_batch_id_txt,
709 x_ack_date => x_ack_date,
710 x_booking_id_txt => x_booking_id_txt,
711 x_booking_date => x_booking_date,
712 x_creation_date => x_last_update_date,
713 x_created_by => x_last_updated_by,
714 x_last_update_date => x_last_update_date,
715 x_last_updated_by => x_last_updated_by,
716 x_last_update_login => x_last_update_login
717 );
718
719 IF (x_mode = 'R') THEN
720 x_request_id := fnd_global.conc_request_id;
721 x_program_id := fnd_global.conc_program_id;
722 x_program_application_id := fnd_global.prog_appl_id;
723 IF (x_request_id = -1) THEN
724 x_request_id := old_references.request_id;
725 x_program_id := old_references.program_id;
726 x_program_application_id := old_references.program_application_id;
727 x_program_update_date := old_references.program_update_date;
728 ELSE
729 x_program_update_date := SYSDATE;
730 END IF;
731 END IF;
732
733 UPDATE igf_aw_db_chg_dtls
734 SET
735 disb_accepted_amt = new_references.disb_accepted_amt,
736 orig_fee_amt = new_references.orig_fee_amt,
737 disb_net_amt = new_references.disb_net_amt,
738 disb_date = new_references.disb_date,
739 disb_activity = new_references.disb_activity,
740 disb_status = new_references.disb_status,
741 disb_status_date = new_references.disb_status_date,
742 disb_rel_flag = NVL(new_references.disb_rel_flag, 'FALSE'),
743 first_disb_flag = new_references.first_disb_flag,
744 interest_rebate_amt = new_references.interest_rebate_amt,
745 disb_conf_flag = new_references.disb_conf_flag,
746 pymnt_prd_start_date = new_references.pymnt_prd_start_date,
747 note_message = new_references.note_message,
748 batch_id_txt = new_references.batch_id_txt,
749 ack_date = new_references.ack_date,
750 booking_id_txt = new_references.booking_id_txt,
751 booking_date = new_references.booking_date,
752 last_update_date = x_last_update_date,
753 last_updated_by = x_last_updated_by,
754 last_update_login = x_last_update_login ,
755 request_id = x_request_id,
756 program_id = x_program_id,
757 program_application_id = x_program_application_id,
758 program_update_date = x_program_update_date
759 WHERE rowid = x_rowid;
760
761 IF (SQL%NOTFOUND) THEN
762 RAISE NO_DATA_FOUND;
763 END IF;
764
765 END update_row;
766
767
768 PROCEDURE add_row (
769 x_rowid IN OUT NOCOPY VARCHAR2,
770 x_award_id IN NUMBER,
771 x_disb_num IN NUMBER,
772 x_disb_seq_num IN NUMBER,
773 x_disb_accepted_amt IN NUMBER,
774 x_orig_fee_amt IN NUMBER,
775 x_disb_net_amt IN NUMBER,
776 x_disb_date IN DATE,
777 x_disb_activity IN VARCHAR2,
778 x_disb_status IN VARCHAR2,
779 x_disb_status_date IN DATE,
780 x_disb_rel_flag IN VARCHAR2,
781 x_first_disb_flag IN VARCHAR2,
782 x_interest_rebate_amt IN NUMBER,
783 x_disb_conf_flag IN VARCHAR2,
784 x_pymnt_prd_start_date IN DATE,
785 x_note_message IN VARCHAR2,
786 x_batch_id_txt IN VARCHAR2,
787 x_ack_date IN DATE,
788 x_booking_id_txt IN VARCHAR2,
789 x_booking_date IN DATE,
790 x_mode IN VARCHAR2
791 ) AS
792 /*
793 || Created By : [email protected]
794 || Created On : 29-SEP-2004
795 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
796 || Known limitations, enhancements or remarks :
797 || Change History :
798 || Who When What
799 || (reverse chronological order - newest change first)
800 */
801 CURSOR c1 IS
802 SELECT rowid
803 FROM igf_aw_db_chg_dtls
804 WHERE award_id = x_award_id
805 AND disb_num = x_disb_num
806 AND disb_seq_num = x_disb_seq_num;
807
808 BEGIN
809
810 OPEN c1;
811 FETCH c1 INTO x_rowid;
812 IF (c1%NOTFOUND) THEN
813 CLOSE c1;
814
815 insert_row (
816 x_rowid,
817 x_award_id,
818 x_disb_num,
819 x_disb_seq_num,
820 x_disb_accepted_amt,
821 x_orig_fee_amt,
822 x_disb_net_amt,
823 x_disb_date,
824 x_disb_activity,
825 x_disb_status,
826 x_disb_status_date,
827 x_disb_rel_flag,
828 x_first_disb_flag,
829 x_interest_rebate_amt,
830 x_disb_conf_flag,
831 x_pymnt_prd_start_date,
832 x_note_message,
833 x_batch_id_txt,
834 x_ack_date,
835 x_booking_id_txt,
836 x_booking_date,
837 x_mode
838 );
839 RETURN;
840 END IF;
841 CLOSE c1;
842
843 update_row (
844 x_rowid,
845 x_award_id,
846 x_disb_num,
847 x_disb_seq_num,
848 x_disb_accepted_amt,
849 x_orig_fee_amt,
850 x_disb_net_amt,
851 x_disb_date,
852 x_disb_activity,
853 x_disb_status,
854 x_disb_status_date,
855 x_disb_rel_flag,
856 x_first_disb_flag,
857 x_interest_rebate_amt,
858 x_disb_conf_flag,
859 x_pymnt_prd_start_date,
860 x_note_message,
861 x_batch_id_txt,
862 x_ack_date,
863 x_booking_id_txt,
864 x_booking_date,
865 x_mode
866 );
867
868 END add_row;
869
870
871 PROCEDURE delete_row (
872 x_rowid IN VARCHAR2
873 ) AS
874 /*
875 || Created By : [email protected]
876 || Created On : 29-SEP-2004
877 || Purpose : Handles the DELETE DML logic for the table.
878 || Known limitations, enhancements or remarks :
879 || Change History :
880 || Who When What
881 || (reverse chronological order - newest change first)
882 */
883 BEGIN
884
885 before_dml (
886 p_action => 'DELETE',
887 x_rowid => x_rowid
888 );
889
890 DELETE FROM igf_aw_db_chg_dtls
891 WHERE rowid = x_rowid;
892
893 IF (SQL%NOTFOUND) THEN
894 RAISE NO_DATA_FOUND;
895 END IF;
896
897 END delete_row;
898
899
900 END igf_aw_db_chg_dtls_pkg;