DBA Data[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;