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_AS_US_AI_GROUP_PKG

Source


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