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_PS_NSUS_RTN_DTL_PKG

Source


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