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