eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_FI_HOLD_PLAN_PKG

Source


1 PACKAGE BODY igs_fi_hold_plan_pkg AS
2 /* $Header: IGSSIA6B.pls 115.18 2003/09/12 10:24:46 vvutukur ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_hold_plan%ROWTYPE;
6   new_references igs_fi_hold_plan%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_hold_plan_name                    IN     VARCHAR2    ,
12     x_hold_plan_desc                    IN     VARCHAR2    ,
13     x_hold_plan_level                   IN     VARCHAR2    ,
14     x_hold_type                         IN     VARCHAR2    ,
15     x_threshold_amount                  IN     NUMBER      ,
16     x_threshold_percent                 IN     NUMBER      ,
17     x_closed_ind                        IN     VARCHAR2    ,
18     x_creation_date                     IN     DATE        ,
19     x_created_by                        IN     NUMBER      ,
20     x_last_update_date                  IN     DATE        ,
21     x_last_updated_by                   IN     NUMBER      ,
22     x_last_update_login                 IN     NUMBER      ,
23     x_fee_type                          IN     VARCHAR2    ,
24     x_offset_days                       IN     NUMBER      ,
25     x_payment_plan_threshold_amt        IN     NUMBER      ,
26     x_payment_plan_threshold_pcent      IN     NUMBER
27   ) AS
28   /*
29   ||  Created By : [email protected]
30   ||  Created On : 29-NOV-2001
31   ||  Purpose : Initialises the Old and New references for the columns of the table.
32   ||  Known limitations, enhancements or remarks :
33   ||  Change History :
34   ||  Who             When            What
35   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
36   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
37   ||  (reverse chronological order - newest change first)
38   */
39 
40     CURSOR cur_old_ref_values IS
41       SELECT   *
42       FROM     igs_fi_hold_plan
43       WHERE    rowid = x_rowid;
44 
45   BEGIN
46 
47     l_rowid := x_rowid;
48 
49     -- Code for setting the Old and New Reference Values.
50     -- Populate Old Values.
51     OPEN cur_old_ref_values;
52     FETCH cur_old_ref_values INTO old_references;
53     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
54       CLOSE cur_old_ref_values;
55       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
56       igs_ge_msg_stack.add;
57       app_exception.raise_exception;
58       RETURN;
59     END IF;
60     CLOSE cur_old_ref_values;
61 
62     -- Populate New Values.
63     new_references.hold_plan_name                    := x_hold_plan_name;
64     new_references.hold_plan_desc                    := x_hold_plan_desc;
65     new_references.hold_plan_level                   := x_hold_plan_level;
66     new_references.hold_type                         := x_hold_type;
67     new_references.threshold_amount                  := x_threshold_amount;
68     new_references.threshold_percent                 := x_threshold_percent;
69     new_references.closed_ind                        := x_closed_ind;
70     new_references.fee_type                          := x_fee_type;
71     new_references.offset_days                       := x_offset_days;
72     new_references.payment_plan_threshold_amt        := x_payment_plan_threshold_amt;
73     new_references.payment_plan_threshold_pcent      := x_payment_plan_threshold_pcent;
74 
75     IF (p_action = 'UPDATE') THEN
76       new_references.creation_date                   := old_references.creation_date;
77       new_references.created_by                      := old_references.created_by;
78     ELSE
79       new_references.creation_date                   := x_creation_date;
80       new_references.created_by                      := x_created_by;
81     END IF;
82 
83     new_references.last_update_date                  := x_last_update_date;
84     new_references.last_updated_by                   := x_last_updated_by;
85     new_references.last_update_login                 := x_last_update_login;
86 
87   END set_column_values;
88 
89 
90   PROCEDURE check_parent_existance AS
91   /*
92   ||  Created By : [email protected]
93   ||  Created On : 29-NOV-2001
94   ||  Purpose : Checks for the existance of Parent records.
95   ||  Known limitations, enhancements or remarks :
96   ||  Change History :
97   ||  Who             When            What
98   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added igs_fi_fee_type_pkg.get_pk_for_validation
99   ||  (reverse chronological order - newest change first)
100   */
101   BEGIN
102 
103     IF (((old_references.hold_type = new_references.hold_type)) OR
104         ((new_references.hold_type IS NULL))) THEN
105       NULL;
106     ELSIF NOT igs_fi_encmb_type_pkg.get_pk_for_validation (
107                 new_references.hold_type
108               ) THEN
109       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
110       igs_ge_msg_stack.add;
111       app_exception.raise_exception;
112     END IF;
113 
114     IF (((old_references.fee_type = new_references.fee_type)) OR
115         ((new_references.fee_type IS NULL))) THEN
116       NULL;
117     ELSIF NOT igs_fi_fee_type_pkg.get_pk_for_validation (
118                 new_references.fee_type
119               ) THEN
120       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
121       igs_ge_msg_stack.add;
122       app_exception.raise_exception;
123     END IF;
124 
125   END check_parent_existance;
126 
127 
128   PROCEDURE check_child_existance IS
129   /*
130   ||  Created By : [email protected]
131   ||  Created On : 29-NOV-2001
132   ||  Purpose : Checks for the existance of Child records.
133   ||  Known limitations, enhancements or remarks :
134   ||  Change History :
135   ||  Who             When            What
136   ||  smadathi       18-dec-2002      Enh Bug 2566615. Removed
137   ||                                  igs_fi_hold_plns_pkg.get_fk_igs_fi_hold_plan
138   ||                                   call
139   ||  (reverse chronological order - newest change first)
140   */
141   BEGIN
142 
143 
144     igs_fi_person_holds_pkg.get_fk_igs_fi_hold_plan (
145       old_references.hold_plan_name
146     );
147 
148   END check_child_existance;
149 
150 
151   FUNCTION get_pk_for_validation (
152     x_hold_plan_name                    IN     VARCHAR2
153   ) RETURN BOOLEAN AS
154   /*
155   ||  Created By : [email protected]
156   ||  Created On : 29-NOV-2001
157   ||  Purpose : Validates the Primary Key of the table.
158   ||  Known limitations, enhancements or remarks :
159   ||  Change History :
160   ||  Who             When            What
161   ||  (reverse chronological order - newest change first)
162   */
163     CURSOR cur_rowid IS
164       SELECT   rowid
165       FROM     igs_fi_hold_plan
166       WHERE    hold_plan_name = x_hold_plan_name
167       FOR UPDATE NOWAIT;
168 
169     lv_rowid cur_rowid%RowType;
170 
171   BEGIN
172 
173     OPEN cur_rowid;
174     FETCH cur_rowid INTO lv_rowid;
175     IF (cur_rowid%FOUND) THEN
176       CLOSE cur_rowid;
177       RETURN(TRUE);
178     ELSE
179       CLOSE cur_rowid;
180       RETURN(FALSE);
181     END IF;
182 
183   END get_pk_for_validation;
184 
185   PROCEDURE get_fk_igs_fi_encmb_type (
186     x_encumbrance_type                  IN     VARCHAR2
187   ) AS
188   /*
189   ||  Created By : [email protected]
190   ||  Created On : 29-NOV-2001
191   ||  Purpose : Validates the Foreign Keys for the table.
192   ||  Known limitations, enhancements or remarks :
193   ||  Change History :
194   ||  Who             When            What
195   ||  (reverse chronological order - newest change first)
196   */
197     CURSOR cur_rowid IS
198       SELECT   rowid
199       FROM     igs_fi_hold_plan
200       WHERE   ((hold_type = x_encumbrance_type));
201 
202     lv_rowid cur_rowid%RowType;
203 
204   BEGIN
205 
206     OPEN cur_rowid;
207     FETCH cur_rowid INTO lv_rowid;
208     IF (cur_rowid%FOUND) THEN
209       CLOSE cur_rowid;
210       fnd_message.set_name ('IGS', 'IGS_FI_FIHP_ET_FK');
211       igs_ge_msg_stack.add;
212       app_exception.raise_exception;
213       RETURN;
214     END IF;
215     CLOSE cur_rowid;
216 
217   END get_fk_igs_fi_encmb_type;
218 
219  PROCEDURE BeforeInsertUpdate(p_inserting BOOLEAN , p_updating BOOLEAN) AS
220   /*
221   ||  Created By : [email protected]
222   ||  Created On : 29-NOV-2001
223   ||  Purpose : Validates the Primary Key of the table.
224   ||  Known limitations, enhancements or remarks :
225   ||  Change History :
226   ||  Who             When            What
227   ||  (reverse chronological order - newest change first)
228   */
229   p_message_name VARCHAR2(30);
230   BEGIN
231    IF ( p_inserting = TRUE OR (p_updating = TRUE AND new_references.hold_type <> old_references.hold_type) ) THEN
232      IF  NOT igs_en_val_etde.enrp_val_et_closed(new_references.hold_type,p_message_name) THEN
233         Fnd_Message.Set_Name('IGS', p_message_name);
234         IGS_GE_MSG_STACK.ADD;
235         App_Exception.Raise_Exception;
236      END IF;
237    END IF;
238   END BeforeInsertUpdate;
239 
240 
241   PROCEDURE before_dml (
242     p_action                            IN     VARCHAR2,
243     x_rowid                             IN     VARCHAR2    ,
244     x_hold_plan_name                    IN     VARCHAR2    ,
245     x_hold_plan_desc                    IN     VARCHAR2    ,
246     x_hold_plan_level                   IN     VARCHAR2    ,
247     x_hold_type                         IN     VARCHAR2    ,
248     x_threshold_amount                  IN     NUMBER      ,
249     x_threshold_percent                 IN     NUMBER      ,
250     x_closed_ind                        IN     VARCHAR2    ,
251     x_creation_date                     IN     DATE        ,
252     x_created_by                        IN     NUMBER      ,
253     x_last_update_date                  IN     DATE        ,
254     x_last_updated_by                   IN     NUMBER      ,
255     x_last_update_login                 IN     NUMBER      ,
256     x_fee_type                          IN     VARCHAR2    ,
257     x_offset_days                       IN     NUMBER      ,
258     x_payment_plan_threshold_amt        IN     NUMBER      ,
259     x_payment_plan_threshold_pcent      IN     NUMBER
260   ) AS
261   /*
262   ||  Created By : [email protected]
263   ||  Created On : 29-NOV-2001
264   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
265   ||            Trigger Handlers for the table, before any DML operation.
266   ||  Known limitations, enhancements or remarks :
267   ||  Change History :
268   ||  Who             When            What
269   ||  (reverse chronological order - newest change first)
270   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
271   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
272   ||  vvutukur    12-May-2002     removed calls to check_constraints procedure
273   ||                              as it is removed.bug#2344826.
274   */
275   BEGIN
276 
277     set_column_values (
278       p_action,
279       x_rowid,
280       x_hold_plan_name,
281       x_hold_plan_desc,
282       x_hold_plan_level,
283       x_hold_type,
284       x_threshold_amount,
285       x_threshold_percent,
286       x_closed_ind,
287       x_creation_date,
288       x_created_by,
289       x_last_update_date,
290       x_last_updated_by,
291       x_last_update_login ,
292       x_fee_type,
293       x_offset_days,
294       x_payment_plan_threshold_amt,
295       x_payment_plan_threshold_pcent
296     );
297 
298     IF (p_action = 'INSERT') THEN
299      BeforeInsertUpdate(TRUE,FALSE);
300       -- Call all the procedures related to Before Insert.
301       IF ( get_pk_for_validation(
302              new_references.hold_plan_name
303            )
304          ) THEN
305         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
306         igs_ge_msg_stack.add;
307         app_exception.raise_exception;
308       END IF;
309       check_parent_existance;
310     ELSIF (p_action = 'UPDATE') THEN
311     BeforeInsertUpdate(FALSE,TRUE);
312       -- Call all the procedures related to Before Update.
313       check_parent_existance;
314     ELSIF (p_action = 'DELETE') THEN
315       -- Call all the procedures related to Before Delete.
316       check_child_existance;
317     ELSIF (p_action = 'VALIDATE_INSERT') THEN
318       -- Call all the procedures related to Before Insert.
319       IF ( get_pk_for_validation (
320              new_references.hold_plan_name
321            )
322          ) THEN
323         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
324         igs_ge_msg_stack.add;
325         app_exception.raise_exception;
326       END IF;
327     ELSIF (p_action = 'VALIDATE_DELETE') THEN
328       check_child_existance;
329     END IF;
330 
331   END before_dml;
332 
333 
334   PROCEDURE insert_row (
335     x_rowid                             IN OUT NOCOPY VARCHAR2,
336     x_hold_plan_name                    IN     VARCHAR2,
337     x_hold_plan_desc                    IN     VARCHAR2,
338     x_hold_plan_level                   IN     VARCHAR2,
339     x_hold_type                         IN     VARCHAR2,
340     x_threshold_amount                  IN     NUMBER,
341     x_threshold_percent                 IN     NUMBER,
342     x_closed_ind                        IN     VARCHAR2,
343     x_mode                              IN     VARCHAR2,
344     x_fee_type                          IN     VARCHAR2,
345     x_offset_days                       IN     NUMBER,
346     x_payment_plan_threshold_amt        IN     NUMBER,
347     x_payment_plan_threshold_pcent      IN     NUMBER
348   ) AS
349   /*
350   ||  Created By : [email protected]
351   ||  Created On : 29-NOV-2001
352   ||  Purpose : Handles the INSERT DML logic for the table.
353   ||  Known limitations, enhancements or remarks :
354   ||  Change History :
355   ||  Who             When            What
356   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
357   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
358   ||  (reverse chronological order - newest change first)
359   */
360     CURSOR c IS
361       SELECT   rowid
362       FROM     igs_fi_hold_plan
363       WHERE    hold_plan_name                    = x_hold_plan_name;
364 
365     x_last_update_date           DATE;
366     x_last_updated_by            NUMBER;
367     x_last_update_login          NUMBER;
368 
369   BEGIN
370 
371     x_last_update_date := SYSDATE;
372     IF (x_mode = 'I') THEN
373       x_last_updated_by := 1;
374       x_last_update_login := 0;
375     ELSIF (x_mode = 'R') THEN
376       x_last_updated_by := fnd_global.user_id;
377       IF (x_last_updated_by IS NULL) THEN
378         x_last_updated_by := -1;
379       END IF;
380       x_last_update_login := fnd_global.login_id;
381       IF (x_last_update_login IS NULL) THEN
382         x_last_update_login := -1;
383       END IF;
384     ELSE
385       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
386       igs_ge_msg_stack.add;
387       app_exception.raise_exception;
388     END IF;
389 
390     before_dml(
391       p_action                            => 'INSERT',
392       x_rowid                             => x_rowid,
393       x_hold_plan_name                    => x_hold_plan_name,
394       x_hold_plan_desc                    => x_hold_plan_desc,
395       x_hold_plan_level                   => x_hold_plan_level,
396       x_hold_type                         => x_hold_type,
397       x_threshold_amount                  => x_threshold_amount,
398       x_threshold_percent                 => x_threshold_percent,
399       x_closed_ind                        => x_closed_ind,
400       x_creation_date                     => x_last_update_date,
401       x_created_by                        => x_last_updated_by,
402       x_last_update_date                  => x_last_update_date,
403       x_last_updated_by                   => x_last_updated_by,
404       x_last_update_login                 => x_last_update_login ,
405       x_fee_type                          => x_fee_type,
406       x_offset_days                       => x_offset_days,
407       x_payment_plan_threshold_amt        => x_payment_plan_threshold_amt,
408       x_payment_plan_threshold_pcent      => x_payment_plan_threshold_pcent
409     );
410 
411     INSERT INTO igs_fi_hold_plan (
412       hold_plan_name,
413       hold_plan_desc,
414       hold_plan_level,
415       hold_type,
416       threshold_amount,
417       threshold_percent,
418       closed_ind,
419       creation_date,
420       created_by,
421       last_update_date,
422       last_updated_by,
423       last_update_login,
424       fee_type,
425       offset_days,
426       payment_plan_threshold_amt,
427       payment_plan_threshold_pcent
428     ) VALUES (
429       new_references.hold_plan_name,
430       new_references.hold_plan_desc,
431       new_references.hold_plan_level,
432       new_references.hold_type,
433       new_references.threshold_amount,
434       new_references.threshold_percent,
435       new_references.closed_ind,
436       x_last_update_date,
437       x_last_updated_by,
438       x_last_update_date,
439       x_last_updated_by,
440       x_last_update_login ,
441       new_references.fee_type,
442       new_references.offset_days,
443       new_references.payment_plan_threshold_amt,
444       new_references.payment_plan_threshold_pcent
445     );
446 
447     OPEN c;
448     FETCH c INTO x_rowid;
449     IF (c%NOTFOUND) THEN
450       CLOSE c;
451       RAISE NO_DATA_FOUND;
452     END IF;
453     CLOSE c;
454 
455   END insert_row;
456 
457 
458   PROCEDURE lock_row (
459     x_rowid                             IN     VARCHAR2,
460     x_hold_plan_name                    IN     VARCHAR2,
461     x_hold_plan_desc                    IN     VARCHAR2,
462     x_hold_plan_level                   IN     VARCHAR2,
463     x_hold_type                         IN     VARCHAR2,
464     x_threshold_amount                  IN     NUMBER,
465     x_threshold_percent                 IN     NUMBER,
466     x_closed_ind                        IN     VARCHAR2,
467     x_fee_type                          IN     VARCHAR2,
468     x_offset_days                       IN     NUMBER,
469     x_payment_plan_threshold_amt        IN     NUMBER,
470     x_payment_plan_threshold_pcent      IN     NUMBER
471   ) AS
472   /*
473   ||  Created By : [email protected]
474   ||  Created On : 29-NOV-2001
475   ||  Purpose : Handles the LOCK mechanism for the table.
476   ||  Known limitations, enhancements or remarks :
477   ||  Change History :
478   ||  Who             When            What
479   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
480   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
481   ||  (reverse chronological order - newest change first)
482   */
483     CURSOR c1 IS
484       SELECT
485         hold_plan_desc,
486         hold_plan_level,
487         hold_type,
488         threshold_amount,
489         threshold_percent,
490         closed_ind,
491         fee_type,
492         offset_days,
493         payment_plan_threshold_amt,
494         payment_plan_threshold_pcent
495       FROM  igs_fi_hold_plan
496       WHERE rowid = x_rowid
497       FOR UPDATE NOWAIT;
498 
499     tlinfo c1%ROWTYPE;
500 
501   BEGIN
502 
503     OPEN c1;
504     FETCH c1 INTO tlinfo;
505     IF (c1%notfound) THEN
506       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
507       igs_ge_msg_stack.add;
508       CLOSE c1;
509       app_exception.raise_exception;
510       RETURN;
511     END IF;
512     CLOSE c1;
513 
514     IF (
515         (tlinfo.hold_plan_desc = x_hold_plan_desc)
516         AND (tlinfo.hold_plan_level = x_hold_plan_level)
517         AND (tlinfo.hold_type = x_hold_type)
518         AND ((tlinfo.threshold_amount = x_threshold_amount) OR ((tlinfo.threshold_amount IS NULL) AND (X_threshold_amount IS NULL)))
519         AND ((tlinfo.threshold_percent = x_threshold_percent) OR ((tlinfo.threshold_percent IS NULL) AND (X_threshold_percent IS NULL)))
520         AND (tlinfo.closed_ind = x_closed_ind)
521         AND ((tlinfo.fee_type = x_fee_type) OR ((tlinfo.fee_type IS NULL) AND (X_fee_type IS NULL)))
522         AND ((tlinfo.offset_days = x_offset_days) OR ((tlinfo.offset_days IS NULL) AND (x_offset_days IS NULL)))
523         AND ((tlinfo.payment_plan_threshold_amt = x_payment_plan_threshold_amt) OR ((tlinfo.payment_plan_threshold_amt IS NULL) AND (x_payment_plan_threshold_amt IS NULL)))
524         AND ((tlinfo.payment_plan_threshold_pcent = x_payment_plan_threshold_pcent) OR ((tlinfo.payment_plan_threshold_pcent IS NULL) AND (x_payment_plan_threshold_pcent IS NULL)))
525        ) THEN
526       NULL;
527     ELSE
528       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
529       igs_ge_msg_stack.add;
530       app_exception.raise_exception;
531     END IF;
532 
533     RETURN;
534 
535   END lock_row;
536 
537 
538   PROCEDURE update_row (
539     x_rowid                             IN     VARCHAR2,
540     x_hold_plan_name                    IN     VARCHAR2,
541     x_hold_plan_desc                    IN     VARCHAR2,
542     x_hold_plan_level                   IN     VARCHAR2,
543     x_hold_type                         IN     VARCHAR2,
544     x_threshold_amount                  IN     NUMBER,
545     x_threshold_percent                 IN     NUMBER,
546     x_closed_ind                        IN     VARCHAR2,
547     x_mode                              IN     VARCHAR2 ,
548     x_fee_type                          IN     VARCHAR2,
549     x_offset_days                       IN     NUMBER,
550     x_payment_plan_threshold_amt        IN     NUMBER,
551     x_payment_plan_threshold_pcent      IN     NUMBER
552   ) AS
553   /*
554   ||  Created By : [email protected]
555   ||  Created On : 29-NOV-2001
556   ||  Purpose : Handles the UPDATE DML logic for the table.
557   ||  Known limitations, enhancements or remarks :
558   ||  Change History :
559   ||  Who             When            What
560   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
561   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
562   ||  (reverse chronological order - newest change first)
563   */
564     x_last_update_date           DATE ;
565     x_last_updated_by            NUMBER;
566     x_last_update_login          NUMBER;
567 
568   BEGIN
569 
570     x_last_update_date := SYSDATE;
571     IF (X_MODE = 'I') THEN
572       x_last_updated_by := 1;
573       x_last_update_login := 0;
574     ELSIF (x_mode = 'R') THEN
575       x_last_updated_by := fnd_global.user_id;
576       IF x_last_updated_by IS NULL THEN
577         x_last_updated_by := -1;
578       END IF;
579       x_last_update_login := fnd_global.login_id;
580       IF (x_last_update_login IS NULL) THEN
581         x_last_update_login := -1;
582       END IF;
583     ELSE
584       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
585       igs_ge_msg_stack.add;
586       app_exception.raise_exception;
587     END IF;
588 
589     before_dml(
590       p_action                            => 'UPDATE',
591       x_rowid                             => x_rowid,
592       x_hold_plan_name                    => x_hold_plan_name,
593       x_hold_plan_desc                    => x_hold_plan_desc,
594       x_hold_plan_level                   => x_hold_plan_level,
595       x_hold_type                         => x_hold_type,
596       x_threshold_amount                  => x_threshold_amount,
597       x_threshold_percent                 => x_threshold_percent,
598       x_closed_ind                        => x_closed_ind,
599       x_creation_date                     => x_last_update_date,
600       x_created_by                        => x_last_updated_by,
601       x_last_update_date                  => x_last_update_date,
602       x_last_updated_by                   => x_last_updated_by,
603       x_last_update_login                 => x_last_update_login ,
604       x_fee_type                          => x_fee_type,
605       x_offset_days                       => x_offset_days,
606       x_payment_plan_threshold_amt        => x_payment_plan_threshold_amt,
607       x_payment_plan_threshold_pcent      => x_payment_plan_threshold_pcent
608     );
609 
610     UPDATE igs_fi_hold_plan
611       SET
612         hold_plan_desc                    = new_references.hold_plan_desc,
613         hold_plan_level                   = new_references.hold_plan_level,
614         hold_type                         = new_references.hold_type,
615         threshold_amount                  = new_references.threshold_amount,
616         threshold_percent                 = new_references.threshold_percent,
617         closed_ind                        = new_references.closed_ind,
618         last_update_date                  = x_last_update_date,
619         last_updated_by                   = x_last_updated_by,
620         last_update_login                 = x_last_update_login ,
621         fee_type                          = new_references.fee_type,
622         offset_days                       = new_references.offset_days,
623         payment_plan_threshold_amt        = new_references.payment_plan_threshold_amt,
624         payment_plan_threshold_pcent      = new_references.payment_plan_threshold_pcent
625       WHERE rowid = x_rowid;
626 
627     IF (SQL%NOTFOUND) THEN
628       RAISE NO_DATA_FOUND;
629     END IF;
630 
631   END update_row;
632 
633 
634   PROCEDURE add_row (
635     x_rowid                             IN OUT NOCOPY VARCHAR2,
636     x_hold_plan_name                    IN     VARCHAR2,
637     x_hold_plan_desc                    IN     VARCHAR2,
638     x_hold_plan_level                   IN     VARCHAR2,
639     x_hold_type                         IN     VARCHAR2,
640     x_threshold_amount                  IN     NUMBER,
641     x_threshold_percent                 IN     NUMBER,
642     x_closed_ind                        IN     VARCHAR2,
643     x_mode                              IN     VARCHAR2,
644     x_fee_type                          IN     VARCHAR2,
645     x_offset_days                       IN     NUMBER,
646     x_payment_plan_threshold_amt        IN     NUMBER,
647     x_payment_plan_threshold_pcent      IN     NUMBER
648   ) AS
649   /*
650   ||  Created By : [email protected]
651   ||  Created On : 29-NOV-2001
652   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
653   ||  Known limitations, enhancements or remarks :
654   ||  Change History :
655   ||  Who             When            What
656   ||  vvutukur       12-Sep-2003      Enh#3045007.Payment Plans Build. Changes as specified in TD.
657   ||  smadathi       18-dec-2002      Enh Bug 2566615. Added new column - fee type
658   ||  (reverse chronological order - newest change first)
659   */
660     CURSOR c1 IS
661       SELECT   rowid
662       FROM     igs_fi_hold_plan
663       WHERE    hold_plan_name                    = x_hold_plan_name;
664 
665   BEGIN
666 
667     OPEN c1;
668     FETCH c1 INTO x_rowid;
669     IF (c1%NOTFOUND) THEN
670       CLOSE c1;
671 
672       insert_row (
673         x_rowid,
674         x_hold_plan_name,
675         x_hold_plan_desc,
676         x_hold_plan_level,
677         x_hold_type,
678         x_threshold_amount,
679         x_threshold_percent,
680         x_closed_ind,
681         x_mode ,
682         x_fee_type,
683         x_offset_days,
684         x_payment_plan_threshold_amt,
685         x_payment_plan_threshold_pcent
686       );
687       RETURN;
688     END IF;
689     CLOSE c1;
690 
691     update_row (
692       x_rowid,
693       x_hold_plan_name,
694       x_hold_plan_desc,
695       x_hold_plan_level,
696       x_hold_type,
697       x_threshold_amount,
698       x_threshold_percent,
699       x_closed_ind,
700       x_mode,
701       x_fee_type,
702       x_offset_days,
703       x_payment_plan_threshold_amt,
704       x_payment_plan_threshold_pcent
705     );
706 
707   END add_row;
708 
709 
710   PROCEDURE delete_row (
711     x_rowid IN VARCHAR2
712   ) AS
713   /*
714   ||  Created By : [email protected]
715   ||  Created On : 29-NOV-2001
716   ||  Purpose : Handles the DELETE DML logic for the table.
717   ||  Known limitations, enhancements or remarks :
718   ||  Change History :
719   ||  Who             When            What
720   ||  (reverse chronological order - newest change first)
721   */
722   BEGIN
723 
724     before_dml (
725       p_action => 'DELETE',
726       x_rowid => x_rowid
727     );
728 
729     DELETE FROM igs_fi_hold_plan
730     WHERE rowid = x_rowid;
731 
732     IF (SQL%NOTFOUND) THEN
733       RAISE NO_DATA_FOUND;
734     END IF;
735 
736   END delete_row;
737 
738 END igs_fi_hold_plan_pkg;