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_SUA_AI_GROUP_PKG

Source


1 PACKAGE BODY igs_as_sua_ai_group_pkg AS
2 /* $Header: IGSDI83B.pls 120.0 2005/07/05 13:03:38 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_as_sua_ai_group%ROWTYPE;
6   new_references igs_as_sua_ai_group%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_sua_ass_item_group_id             IN     NUMBER,
12     x_person_id                         IN     NUMBER,
13     x_course_cd                         IN     VARCHAR2,
14     x_uoo_id                            IN     NUMBER,
15     x_group_name                        IN     VARCHAR2,
16     x_midterm_formula_code              IN     VARCHAR2,
17     x_midterm_formula_qty               IN     NUMBER,
18     x_midterm_weight_qty                IN     NUMBER,
19     x_final_formula_code                IN     VARCHAR2,
20     x_final_formula_qty                 IN     NUMBER,
21     x_final_weight_qty                  IN     NUMBER,
22     x_unit_ass_item_group_id            IN     NUMBER,
23     x_us_ass_item_group_id              IN     NUMBER,
24     x_logical_delete_date               IN     DATE,
25     x_creation_date                     IN     DATE,
26     x_created_by                        IN     NUMBER,
27     x_last_update_date                  IN     DATE,
28     x_last_updated_by                   IN     NUMBER,
29     x_last_update_login                 IN     NUMBER
30   ) AS
31   /*
32   ||  Created By : [email protected]
33   ||  Created On : 10-OCT-2003
34   ||  Purpose : Initialises the Old and New references for the columns of the table.
35   ||  Known limitations, enhancements or remarks :
36   ||  Change History :
37   ||  Who             When            What
38   ||  (reverse chronological order - newest change first)
39   */
40 
41     CURSOR cur_old_ref_values IS
42       SELECT   *
43       FROM     igs_as_sua_ai_group
44       WHERE    rowid = x_rowid;
45 
46   BEGIN
47 
48     l_rowid := x_rowid;
49 
50     -- Code for setting the Old and New Reference Values.
51     -- Populate Old Values.
52     OPEN cur_old_ref_values;
53     FETCH cur_old_ref_values INTO old_references;
54     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55       CLOSE cur_old_ref_values;
56       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57       igs_ge_msg_stack.add;
58       app_exception.raise_exception;
59       RETURN;
60     END IF;
61     CLOSE cur_old_ref_values;
62 
63     -- Populate New Values.
64     new_references.sua_ass_item_group_id             := x_sua_ass_item_group_id;
65     new_references.person_id                         := x_person_id;
66     new_references.course_cd                         := x_course_cd;
67     new_references.uoo_id                            := x_uoo_id;
68     new_references.group_name                        := x_group_name;
69     new_references.midterm_formula_code              := x_midterm_formula_code;
70     new_references.midterm_formula_qty               := x_midterm_formula_qty;
71     new_references.midterm_weight_qty                := x_midterm_weight_qty;
72     new_references.final_formula_code                := x_final_formula_code;
73     new_references.final_formula_qty                 := x_final_formula_qty;
74     new_references.final_weight_qty                  := x_final_weight_qty;
75     new_references.unit_ass_item_group_id            := x_unit_ass_item_group_id;
76     new_references.us_ass_item_group_id              := x_us_ass_item_group_id;
77     new_references.logical_delete_date               := x_logical_delete_date;
78 
79     IF (p_action = 'UPDATE') THEN
80       new_references.creation_date                   := old_references.creation_date;
81       new_references.created_by                      := old_references.created_by;
82     ELSE
83       new_references.creation_date                   := x_creation_date;
84       new_references.created_by                      := x_created_by;
85     END IF;
86 
87     new_references.last_update_date                  := x_last_update_date;
88     new_references.last_updated_by                   := x_last_updated_by;
89     new_references.last_update_login                 := x_last_update_login;
90 
91   END set_column_values;
92 
93 
94   PROCEDURE check_uniqueness AS
95   /*
96   ||  Created By : [email protected]
97   ||  Created On : 10-OCT-2003
98   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
99   ||  Known limitations, enhancements or remarks :
100   ||  Change History :
101   ||  Who             When            What
102   ||  (reverse chronological order - newest change first)
103   */
104   BEGIN
105 
106     IF ( get_uk_for_validation (
107            new_references.person_id,
108            new_references.course_cd,
109            new_references.uoo_id,
110            new_references.group_name
111          )
112        ) THEN
113       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
114       igs_ge_msg_stack.add;
115       app_exception.raise_exception;
116     END IF;
117 
118   END check_uniqueness;
119 
120   PROCEDURE check_child_existance AS
121   /*
122   ||  Created By : [email protected]
123   ||  Created On : 10-OCT-2003
124   ||  Purpose : Checks for the existance of Parent records.
125   ||  Known limitations, enhancements or remarks :
126   ||  Change History :
127   ||  Who             When            What
128   ||  (reverse chronological order - newest change first)
129   */
130   BEGIN
131     igs_as_su_atmpt_itm_pkg.get_fk_igs_as_sua_ai_group (
132       old_references.sua_ass_item_group_id
133     );
134     igs_as_suaai_ouhist_pkg.get_fk_igs_as_sua_ai_group (
135       old_references.sua_ass_item_group_id
136     );
137   END check_child_existance;
138 
139   PROCEDURE check_parent_existance AS
140   /*
141   ||  Created By : [email protected]
142   ||  Created On : 10-OCT-2003
143   ||  Purpose : Checks for the existance of Parent records.
144   ||  Known limitations, enhancements or remarks :
145   ||  Change History :
146   ||  Who             When            What
147   ||  (reverse chronological order - newest change first)
148   */
149   BEGIN
150 
151     IF (((old_references.person_id = new_references.person_id) AND
152          (old_references.course_cd = new_references.course_cd) AND
153          (old_references.uoo_id = new_references.uoo_id)) OR
154         ((new_references.person_id IS NULL) OR
155          (new_references.course_cd IS NULL) OR
156          (new_references.uoo_id IS NULL))) THEN
157       NULL;
158     ELSIF NOT igs_en_su_attempt_pkg.get_pk_for_validation (
159                 new_references.person_id,
160                 new_references.course_cd,
161                 new_references.uoo_id
162               ) THEN
163       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
164       igs_ge_msg_stack.add;
165       app_exception.raise_exception;
166     END IF;
167 
168     IF (((old_references.unit_ass_item_group_id = new_references.unit_ass_item_group_id)) OR
169         ((new_references.unit_ass_item_group_id IS NULL))) THEN
170       NULL;
171     ELSIF NOT igs_as_unit_ai_grp_pkg.get_pk_for_validation (
172                 new_references.unit_ass_item_group_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     IF (((old_references.us_ass_item_group_id = new_references.us_ass_item_group_id)) OR
180         ((new_references.us_ass_item_group_id IS NULL))) THEN
181       NULL;
182     ELSIF NOT igs_as_us_ai_group_pkg.get_pk_for_validation (
183                 new_references.us_ass_item_group_id
184               ) THEN
185       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
186       igs_ge_msg_stack.add;
187       app_exception.raise_exception;
188     END IF;
189 
190   END check_parent_existance;
191 
192 
193   FUNCTION get_pk_for_validation (
194     x_sua_ass_item_group_id             IN     NUMBER
195   ) RETURN BOOLEAN AS
196   /*
197   ||  Created By : [email protected]
198   ||  Created On : 10-OCT-2003
199   ||  Purpose : Validates the Primary Key of the table.
200   ||  Known limitations, enhancements or remarks :
201   ||  Change History :
202   ||  Who             When            What
203   ||  (reverse chronological order - newest change first)
204   */
205     CURSOR cur_rowid IS
206       SELECT   rowid
207       FROM     igs_as_sua_ai_group
208       WHERE    sua_ass_item_group_id = x_sua_ass_item_group_id
209       FOR UPDATE NOWAIT;
210 
211     lv_rowid cur_rowid%RowType;
212 
213   BEGIN
214 
215     OPEN cur_rowid;
216     FETCH cur_rowid INTO lv_rowid;
217     IF (cur_rowid%FOUND) THEN
218       CLOSE cur_rowid;
219       RETURN(TRUE);
220     ELSE
221       CLOSE cur_rowid;
222       RETURN(FALSE);
223     END IF;
224 
225   END get_pk_for_validation;
226 
227 
228   FUNCTION get_uk_for_validation (
229     x_person_id                         IN     NUMBER,
230     x_course_cd                         IN     VARCHAR2,
231     x_uoo_id                            IN     NUMBER,
232     x_group_name                        IN     VARCHAR2
233   ) RETURN BOOLEAN AS
234   /*
235   ||  Created By : [email protected]
236   ||  Created On : 10-OCT-2003
237   ||  Purpose : Validates the Unique Keys of the table.
238   ||  Known limitations, enhancements or remarks :
239   ||  Change History :
240   ||  Who             When            What
241   ||  (reverse chronological order - newest change first)
242   */
243     CURSOR cur_rowid IS
244       SELECT   rowid
245       FROM     igs_as_sua_ai_group
246       WHERE    person_id = x_person_id
247       AND      course_cd = x_course_cd
248       AND      uoo_id = x_uoo_id
249       AND      group_name = x_group_name
250       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
251 
252     lv_rowid cur_rowid%RowType;
253 
254   BEGIN
255 
256     OPEN cur_rowid;
257     FETCH cur_rowid INTO lv_rowid;
258     IF (cur_rowid%FOUND) THEN
259       CLOSE cur_rowid;
260         RETURN (true);
261         ELSE
262        CLOSE cur_rowid;
263       RETURN(FALSE);
264     END IF;
265 
266   END get_uk_for_validation ;
267 
268 
269   PROCEDURE get_fk_igs_en_su_attempt (
270     x_person_id                         IN     NUMBER,
271     x_course_cd                         IN     VARCHAR2,
272     x_uoo_id                            IN     NUMBER
273   ) AS
274   /*
275   ||  Created By : [email protected]
276   ||  Created On : 10-OCT-2003
277   ||  Purpose : Validates the Foreign Keys for the table.
278   ||  Known limitations, enhancements or remarks :
279   ||  Change History :
280   ||  Who             When            What
281   ||  (reverse chronological order - newest change first)
282   */
283     CURSOR cur_rowid IS
284       SELECT   rowid
285       FROM     igs_as_sua_ai_group
286       WHERE   ((course_cd = x_course_cd) AND
287                (person_id = x_person_id) AND
288                (uoo_id = x_uoo_id));
289 
290     lv_rowid cur_rowid%RowType;
291 
292   BEGIN
293 
294     OPEN cur_rowid;
295     FETCH cur_rowid INTO lv_rowid;
296     IF (cur_rowid%FOUND) THEN
297       CLOSE cur_rowid;
298       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
299       igs_ge_msg_stack.add;
300       app_exception.raise_exception;
301       RETURN;
302     END IF;
303     CLOSE cur_rowid;
304 
305   END get_fk_igs_en_su_attempt;
306 
307 
308   PROCEDURE get_fk_igs_as_unit_ai_grp (
309     x_unit_ass_item_group_id            IN     NUMBER
310   ) AS
311   /*
312   ||  Created By : [email protected]
313   ||  Created On : 10-OCT-2003
314   ||  Purpose : Validates the Foreign Keys for the table.
315   ||  Known limitations, enhancements or remarks :
316   ||  Change History :
317   ||  Who             When            What
318   ||  (reverse chronological order - newest change first)
319   */
320     CURSOR cur_rowid IS
321       SELECT   rowid
322       FROM     igs_as_sua_ai_group
323       WHERE   ((unit_ass_item_group_id = x_unit_ass_item_group_id));
324 
325     lv_rowid cur_rowid%RowType;
326 
327   BEGIN
328 
329     OPEN cur_rowid;
330     FETCH cur_rowid INTO lv_rowid;
331     IF (cur_rowid%FOUND) THEN
332       CLOSE cur_rowid;
333       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
334       igs_ge_msg_stack.add;
335       app_exception.raise_exception;
336       RETURN;
337     END IF;
338     CLOSE cur_rowid;
339 
340   END get_fk_igs_as_unit_ai_grp;
341 
342 
343   PROCEDURE get_fk_igs_as_us_ai_group (
344     x_us_ass_item_group_id              IN     NUMBER
345   ) AS
346   /*
347   ||  Created By : [email protected]
348   ||  Created On : 10-OCT-2003
349   ||  Purpose : Validates the Foreign Keys for the table.
350   ||  Known limitations, enhancements or remarks :
351   ||  Change History :
352   ||  Who             When            What
353   ||  (reverse chronological order - newest change first)
354   */
355     CURSOR cur_rowid IS
356       SELECT   rowid
357       FROM     igs_as_sua_ai_group
358       WHERE   ((us_ass_item_group_id = x_us_ass_item_group_id));
359 
360     lv_rowid cur_rowid%RowType;
361 
362   BEGIN
363 
364     OPEN cur_rowid;
365     FETCH cur_rowid INTO lv_rowid;
366     IF (cur_rowid%FOUND) THEN
367       CLOSE cur_rowid;
368       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
369       igs_ge_msg_stack.add;
370       app_exception.raise_exception;
371       RETURN;
372     END IF;
373     CLOSE cur_rowid;
374 
375   END get_fk_igs_as_us_ai_group;
376 
377 
378   PROCEDURE before_dml (
379     p_action                            IN     VARCHAR2,
380     x_rowid                             IN     VARCHAR2,
381     x_sua_ass_item_group_id             IN     NUMBER,
382     x_person_id                         IN     NUMBER,
383     x_course_cd                         IN     VARCHAR2,
384     x_uoo_id                            IN     NUMBER,
385     x_group_name                        IN     VARCHAR2,
386     x_midterm_formula_code              IN     VARCHAR2,
387     x_midterm_formula_qty               IN     NUMBER,
388     x_midterm_weight_qty                IN     NUMBER,
389     x_final_formula_code                IN     VARCHAR2,
390     x_final_formula_qty                 IN     NUMBER,
391     x_final_weight_qty                  IN     NUMBER,
392     x_unit_ass_item_group_id            IN     NUMBER,
393     x_us_ass_item_group_id              IN     NUMBER,
394     x_logical_delete_date               IN     DATE,
395     x_creation_date                     IN     DATE,
396     x_created_by                        IN     NUMBER,
397     x_last_update_date                  IN     DATE,
398     x_last_updated_by                   IN     NUMBER,
399     x_last_update_login                 IN     NUMBER
400   ) AS
401   /*
402   ||  Created By : [email protected]
403   ||  Created On : 10-OCT-2003
404   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
405   ||            Trigger Handlers for the table, before any DML operation.
406   ||  Known limitations, enhancements or remarks :
407   ||  Change History :
408   ||  Who             When            What
409   ||  (reverse chronological order - newest change first)
410   */
411   BEGIN
412 
413     set_column_values (
414       p_action,
415       x_rowid,
416       x_sua_ass_item_group_id,
417       x_person_id,
418       x_course_cd,
419       x_uoo_id,
420       x_group_name,
421       x_midterm_formula_code,
422       x_midterm_formula_qty,
423       x_midterm_weight_qty,
424       x_final_formula_code,
425       x_final_formula_qty,
426       x_final_weight_qty,
427       x_unit_ass_item_group_id,
428       x_us_ass_item_group_id,
429       x_logical_delete_date,
430       x_creation_date,
431       x_created_by,
432       x_last_update_date,
433       x_last_updated_by,
434       x_last_update_login
435     );
436 
437     IF (p_action = 'INSERT') THEN
438       -- Call all the procedures related to Before Insert.
439       IF ( get_pk_for_validation(
440              new_references.sua_ass_item_group_id
441            )
442          ) THEN
443         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
444         igs_ge_msg_stack.add;
445         app_exception.raise_exception;
446       END IF;
447       check_uniqueness;
448       check_parent_existance;
449     ELSIF (p_action = 'UPDATE') THEN
450       -- Call all the procedures related to Before Update.
451       check_uniqueness;
452       check_parent_existance;
453     ELSIF (p_action = 'VALIDATE_INSERT') THEN
454       -- Call all the procedures related to Before Insert.
455       IF ( get_pk_for_validation (
456              new_references.sua_ass_item_group_id
457            )
458          ) THEN
459         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
460         igs_ge_msg_stack.add;
461         app_exception.raise_exception;
462       END IF;
463       check_uniqueness;
464     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
465       check_uniqueness;
466     ELSIF (p_action = 'VALIDATE_DELETE') THEN
467       Check_Child_Existance;
468     END IF;
469 
470   END before_dml;
471 
472 
473   PROCEDURE insert_row (
474     x_rowid                             IN OUT NOCOPY VARCHAR2,
475     x_sua_ass_item_group_id             IN OUT NOCOPY NUMBER,
476     x_person_id                         IN     NUMBER,
477     x_course_cd                         IN     VARCHAR2,
478     x_uoo_id                            IN     NUMBER,
479     x_group_name                        IN     VARCHAR2,
480     x_midterm_formula_code              IN     VARCHAR2,
481     x_midterm_formula_qty               IN     NUMBER,
482     x_midterm_weight_qty                IN     NUMBER,
483     x_final_formula_code                IN     VARCHAR2,
484     x_final_formula_qty                 IN     NUMBER,
485     x_final_weight_qty                  IN     NUMBER,
486     x_unit_ass_item_group_id            IN     NUMBER,
487     x_us_ass_item_group_id              IN     NUMBER,
488     x_logical_delete_date               IN     DATE,
489     x_mode                              IN     VARCHAR2
490   ) AS
491   /*
492   ||  Created By : [email protected]
493   ||  Created On : 10-OCT-2003
494   ||  Purpose : Handles the INSERT DML logic for the table.
495   ||  Known limitations, enhancements or remarks :
496   ||  Change History :
497   ||  Who             When            What
498   ||  (reverse chronological order - newest change first)
499   */
500 
501     x_last_update_date           DATE;
502     x_last_updated_by            NUMBER;
503     x_last_update_login          NUMBER;
504 
505   BEGIN
506 
507     x_last_update_date := SYSDATE;
508     IF (x_mode = 'I') THEN
509       x_last_updated_by := 1;
510       x_last_update_login := 0;
511     ELSIF (X_MODE IN ('R', 'S')) THEN
512       x_last_updated_by := fnd_global.user_id;
513       IF (x_last_updated_by IS NULL) THEN
514         x_last_updated_by := -1;
515       END IF;
516       x_last_update_login := fnd_global.login_id;
517       IF (x_last_update_login IS NULL) THEN
518         x_last_update_login := -1;
519       END IF;
520     ELSE
521       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
522       fnd_message.set_token ('ROUTINE', 'IGS_AS_SUA_AI_GROUP_PKG.INSERT_ROW');
523       igs_ge_msg_stack.add;
524       app_exception.raise_exception;
525     END IF;
526 
527     x_sua_ass_item_group_id := NULL;
528 
529     before_dml(
530       p_action                            => 'INSERT',
531       x_rowid                             => x_rowid,
532       x_sua_ass_item_group_id             => x_sua_ass_item_group_id,
533       x_person_id                         => x_person_id,
534       x_course_cd                         => x_course_cd,
535       x_uoo_id                            => x_uoo_id,
536       x_group_name                        => x_group_name,
537       x_midterm_formula_code              => x_midterm_formula_code,
538       x_midterm_formula_qty               => x_midterm_formula_qty,
539       x_midterm_weight_qty                => x_midterm_weight_qty,
540       x_final_formula_code                => x_final_formula_code,
541       x_final_formula_qty                 => x_final_formula_qty,
542       x_final_weight_qty                  => x_final_weight_qty,
543       x_unit_ass_item_group_id            => x_unit_ass_item_group_id,
544       x_us_ass_item_group_id              => x_us_ass_item_group_id,
545       x_logical_delete_date               => x_logical_delete_date,
546       x_creation_date                     => x_last_update_date,
547       x_created_by                        => x_last_updated_by,
548       x_last_update_date                  => x_last_update_date,
549       x_last_updated_by                   => x_last_updated_by,
550       x_last_update_login                 => x_last_update_login
551     );
552 
553     IF (x_mode = 'S') THEN
554     igs_sc_gen_001.set_ctx('R');
555   END IF;
556   INSERT INTO igs_as_sua_ai_group (
557       sua_ass_item_group_id,
558       person_id,
559       course_cd,
560       uoo_id,
561       group_name,
562       midterm_formula_code,
563       midterm_formula_qty,
564       midterm_weight_qty,
565       final_formula_code,
566       final_formula_qty,
567       final_weight_qty,
568       unit_ass_item_group_id,
569       us_ass_item_group_id,
570       logical_delete_date,
571       creation_date,
572       created_by,
573       last_update_date,
574       last_updated_by,
575       last_update_login
576     ) VALUES (
577       igs_as_sua_ai_group_s.NEXTVAL,
578       new_references.person_id,
579       new_references.course_cd,
580       new_references.uoo_id,
581       new_references.group_name,
582       new_references.midterm_formula_code,
583       new_references.midterm_formula_qty,
584       new_references.midterm_weight_qty,
585       new_references.final_formula_code,
586       new_references.final_formula_qty,
587       new_references.final_weight_qty,
588       new_references.unit_ass_item_group_id,
589       new_references.us_ass_item_group_id,
590       new_references.logical_delete_date,
591       x_last_update_date,
592       x_last_updated_by,
593       x_last_update_date,
594       x_last_updated_by,
595       x_last_update_login
596     ) RETURNING ROWID, sua_ass_item_group_id INTO x_rowid, x_sua_ass_item_group_id;
597  IF (x_mode = 'S') THEN
598     igs_sc_gen_001.unset_ctx('R');
599   END IF;
600 
601 
602 EXCEPTION
603   WHEN OTHERS THEN
604     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
605       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
606  IF (x_mode = 'S') THEN
607     igs_sc_gen_001.unset_ctx('R');
608   END IF;
609 
610       fnd_message.set_token ('ERR_CD', SQLCODE);
611       igs_ge_msg_stack.add;
612       igs_sc_gen_001.unset_ctx('R');
613       app_exception.raise_exception;
614     ELSE
615       igs_sc_gen_001.unset_ctx('R');
616       RAISE;
617     END IF;
618  END insert_row;
619 
620 
621   PROCEDURE lock_row (
622     x_rowid                             IN     VARCHAR2,
623     x_sua_ass_item_group_id             IN     NUMBER,
624     x_person_id                         IN     NUMBER,
625     x_course_cd                         IN     VARCHAR2,
626     x_uoo_id                            IN     NUMBER,
627     x_group_name                        IN     VARCHAR2,
628     x_midterm_formula_code              IN     VARCHAR2,
629     x_midterm_formula_qty               IN     NUMBER,
630     x_midterm_weight_qty                IN     NUMBER,
631     x_final_formula_code                IN     VARCHAR2,
632     x_final_formula_qty                 IN     NUMBER,
633     x_final_weight_qty                  IN     NUMBER,
634     x_unit_ass_item_group_id            IN     NUMBER,
638   /*
635     x_us_ass_item_group_id              IN     NUMBER,
636     x_logical_delete_date               IN     DATE
637   ) AS
639   ||  Created By : [email protected]
640   ||  Created On : 10-OCT-2003
641   ||  Purpose : Handles the LOCK mechanism for the table.
642   ||  Known limitations, enhancements or remarks :
643   ||  Change History :
644   ||  Who             When            What
645   ||  (reverse chronological order - newest change first)
646   */
647     CURSOR c1 IS
648       SELECT
649         person_id,
650         course_cd,
651         uoo_id,
652         group_name,
653         midterm_formula_code,
654         midterm_formula_qty,
655         midterm_weight_qty,
656         final_formula_code,
657         final_formula_qty,
658         final_weight_qty,
659         unit_ass_item_group_id,
660         us_ass_item_group_id,
661         logical_delete_date
662       FROM  igs_as_sua_ai_group
663       WHERE rowid = x_rowid
664       FOR UPDATE NOWAIT;
665 
666     tlinfo c1%ROWTYPE;
667 
668   BEGIN
669 
670     OPEN c1;
671     FETCH c1 INTO tlinfo;
672     IF (c1%notfound) THEN
673       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
674       igs_ge_msg_stack.add;
675       CLOSE c1;
676       app_exception.raise_exception;
677       RETURN;
678     END IF;
679     CLOSE c1;
680 
681     IF (
682         (tlinfo.person_id = x_person_id)
683         AND (tlinfo.course_cd = x_course_cd)
684         AND (tlinfo.uoo_id = x_uoo_id)
685         AND (tlinfo.group_name = x_group_name)
686         AND ((tlinfo.midterm_formula_code = x_midterm_formula_code) OR ((tlinfo.midterm_formula_code IS NULL) AND (X_midterm_formula_code IS NULL)))
687         AND ((tlinfo.midterm_formula_qty = x_midterm_formula_qty) OR ((tlinfo.midterm_formula_qty IS NULL) AND (X_midterm_formula_qty IS NULL)))
688         AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty) OR ((tlinfo.midterm_weight_qty IS NULL) AND (X_midterm_weight_qty IS NULL)))
689         AND ((tlinfo.final_formula_code = x_final_formula_code) OR ((tlinfo.final_formula_code IS NULL) AND (X_final_formula_code IS NULL)))
690         AND ((tlinfo.final_formula_qty = x_final_formula_qty) OR ((tlinfo.final_formula_qty IS NULL) AND (X_final_formula_qty IS NULL)))
691         AND ((tlinfo.final_weight_qty = x_final_weight_qty) OR ((tlinfo.final_weight_qty IS NULL) AND (X_final_weight_qty IS NULL)))
692         AND ((tlinfo.unit_ass_item_group_id = x_unit_ass_item_group_id) OR ((tlinfo.unit_ass_item_group_id IS NULL) AND (X_unit_ass_item_group_id IS NULL)))
693         AND ((tlinfo.us_ass_item_group_id = x_us_ass_item_group_id) OR ((tlinfo.us_ass_item_group_id IS NULL) AND (X_us_ass_item_group_id IS NULL)))
694         AND ((tlinfo.logical_delete_date = x_logical_delete_date) OR ((tlinfo.logical_delete_date IS NULL) AND (x_logical_delete_date IS NULL)))
695        ) THEN
696       NULL;
697     ELSE
698       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
699       igs_ge_msg_stack.add;
700       app_exception.raise_exception;
701     END IF;
702 
703     RETURN;
704 
705   END lock_row;
706 
707 
708   PROCEDURE update_row (
709     x_rowid                             IN     VARCHAR2,
710     x_sua_ass_item_group_id             IN     NUMBER,
711     x_person_id                         IN     NUMBER,
712     x_course_cd                         IN     VARCHAR2,
713     x_uoo_id                            IN     NUMBER,
714     x_group_name                        IN     VARCHAR2,
715     x_midterm_formula_code              IN     VARCHAR2,
716     x_midterm_formula_qty               IN     NUMBER,
717     x_midterm_weight_qty                IN     NUMBER,
718     x_final_formula_code                IN     VARCHAR2,
719     x_final_formula_qty                 IN     NUMBER,
720     x_final_weight_qty                  IN     NUMBER,
721     x_unit_ass_item_group_id            IN     NUMBER,
722     x_us_ass_item_group_id              IN     NUMBER,
723     x_logical_delete_date               IN     DATE,
724     x_mode                              IN     VARCHAR2
725   ) AS
726   /*
727   ||  Created By : [email protected]
728   ||  Created On : 10-OCT-2003
729   ||  Purpose : Handles the UPDATE DML logic for the table.
730   ||  Known limitations, enhancements or remarks :
731   ||  Change History :
732   ||  Who             When            What
733   ||  (reverse chronological order - newest change first)
734   */
735     x_last_update_date           DATE ;
736     x_last_updated_by            NUMBER;
737     x_last_update_login          NUMBER;
738 
739   BEGIN
740 
741     x_last_update_date := SYSDATE;
742     IF (X_MODE = 'I') THEN
743       x_last_updated_by := 1;
744       x_last_update_login := 0;
745     ELSIF (X_MODE IN ('R', 'S')) THEN
746       x_last_updated_by := fnd_global.user_id;
747       IF x_last_updated_by IS NULL THEN
748         x_last_updated_by := -1;
749       END IF;
750       x_last_update_login := fnd_global.login_id;
751       IF (x_last_update_login IS NULL) THEN
752         x_last_update_login := -1;
753       END IF;
754     ELSE
755       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
756       fnd_message.set_token ('ROUTINE', 'IGS_AS_SUA_AI_GROUP_PKG.UPDATE_ROW');
757       igs_ge_msg_stack.add;
758       app_exception.raise_exception;
759     END IF;
760 
761     before_dml(
762       p_action                            => 'UPDATE',
763       x_rowid                             => x_rowid,
764       x_sua_ass_item_group_id             => x_sua_ass_item_group_id,
765       x_person_id                         => x_person_id,
766       x_course_cd                         => x_course_cd,
767       x_uoo_id                            => x_uoo_id,
768       x_group_name                        => x_group_name,
769       x_midterm_formula_code              => x_midterm_formula_code,
770       x_midterm_formula_qty               => x_midterm_formula_qty,
771       x_midterm_weight_qty                => x_midterm_weight_qty,
772       x_final_formula_code                => x_final_formula_code,
773       x_final_formula_qty                 => x_final_formula_qty,
774       x_final_weight_qty                  => x_final_weight_qty,
775       x_unit_ass_item_group_id            => x_unit_ass_item_group_id,
776       x_us_ass_item_group_id              => x_us_ass_item_group_id,
777       x_logical_delete_date               => x_logical_delete_date,
778       x_creation_date                     => x_last_update_date,
779       x_created_by                        => x_last_updated_by,
780       x_last_update_date                  => x_last_update_date,
781       x_last_updated_by                   => x_last_updated_by,
782       x_last_update_login                 => x_last_update_login
783     );
784 
785     IF (x_mode = 'S') THEN
786     igs_sc_gen_001.set_ctx('R');
787   END IF;
788   UPDATE igs_as_sua_ai_group
789       SET
790         person_id                         = new_references.person_id,
791         course_cd                         = new_references.course_cd,
792         uoo_id                            = new_references.uoo_id,
793         group_name                        = new_references.group_name,
794         midterm_formula_code              = new_references.midterm_formula_code,
795         midterm_formula_qty               = new_references.midterm_formula_qty,
796         midterm_weight_qty                = new_references.midterm_weight_qty,
797         final_formula_code                = new_references.final_formula_code,
798         final_formula_qty                 = new_references.final_formula_qty,
799         final_weight_qty                  = new_references.final_weight_qty,
800         unit_ass_item_group_id            = new_references.unit_ass_item_group_id,
801         us_ass_item_group_id              = new_references.us_ass_item_group_id,
802         logical_delete_date               = new_references.logical_delete_date,
803         last_update_date                  = x_last_update_date,
804         last_updated_by                   = x_last_updated_by,
805         last_update_login                 = x_last_update_login
806       WHERE rowid = x_rowid;
807 
808     IF (SQL%NOTFOUND) THEN
809      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
810      igs_ge_msg_stack.add;
811      igs_sc_gen_001.unset_ctx('R');
812      app_exception.raise_exception;
813  END IF;
814  IF (x_mode = 'S') THEN
815     igs_sc_gen_001.unset_ctx('R');
816   END IF;
817 
818 
819 
820 EXCEPTION
821   WHEN OTHERS THEN
822     IF (SQLCODE = (-28115)) THEN
823       fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
824       fnd_message.set_token ('ERR_CD', SQLCODE);
825       igs_ge_msg_stack.add;
826       igs_sc_gen_001.unset_ctx('R');
827       app_exception.raise_exception;
828     ELSE
829       igs_sc_gen_001.unset_ctx('R');
830       RAISE;
831     END IF;
832  END update_row;
833 
834 
835   PROCEDURE add_row (
836     x_rowid                             IN OUT NOCOPY VARCHAR2,
837     x_sua_ass_item_group_id             IN OUT NOCOPY NUMBER,
838     x_person_id                         IN     NUMBER,
839     x_course_cd                         IN     VARCHAR2,
840     x_uoo_id                            IN     NUMBER,
841     x_group_name                        IN     VARCHAR2,
842     x_midterm_formula_code              IN     VARCHAR2,
843     x_midterm_formula_qty               IN     NUMBER,
844     x_midterm_weight_qty                IN     NUMBER,
845     x_final_formula_code                IN     VARCHAR2,
846     x_final_formula_qty                 IN     NUMBER,
847     x_final_weight_qty                  IN     NUMBER,
848     x_unit_ass_item_group_id            IN     NUMBER,
849     x_us_ass_item_group_id              IN     NUMBER,
850     x_logical_delete_date               IN     DATE,
851     x_mode                              IN     VARCHAR2
852   ) AS
853   /*
854   ||  Created By : [email protected]
855   ||  Created On : 10-OCT-2003
856   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
857   ||  Known limitations, enhancements or remarks :
858   ||  Change History :
859   ||  Who             When            What
860   ||  (reverse chronological order - newest change first)
861   */
862     CURSOR c1 IS
863       SELECT   rowid
864       FROM     igs_as_sua_ai_group
865       WHERE    sua_ass_item_group_id             = x_sua_ass_item_group_id;
866 
867   BEGIN
868 
869     OPEN c1;
870     FETCH c1 INTO x_rowid;
871     IF (c1%NOTFOUND) THEN
872       CLOSE c1;
873 
874       insert_row (
875         x_rowid,
876         x_sua_ass_item_group_id,
877         x_person_id,
878         x_course_cd,
879         x_uoo_id,
880         x_group_name,
881         x_midterm_formula_code,
882         x_midterm_formula_qty,
883         x_midterm_weight_qty,
884         x_final_formula_code,
885         x_final_formula_qty,
886         x_final_weight_qty,
887         x_unit_ass_item_group_id,
888         x_us_ass_item_group_id,
889         x_logical_delete_date,
890         x_mode
891       );
892       RETURN;
893     END IF;
894     CLOSE c1;
895 
896     update_row (
897       x_rowid,
898       x_sua_ass_item_group_id,
899       x_person_id,
900       x_course_cd,
901       x_uoo_id,
902       x_group_name,
903       x_midterm_formula_code,
904       x_midterm_formula_qty,
905       x_midterm_weight_qty,
906       x_final_formula_code,
907       x_final_formula_qty,
908       x_final_weight_qty,
909       x_unit_ass_item_group_id,
910       x_us_ass_item_group_id,
911       x_logical_delete_date,
912       x_mode
913     );
914 
915   END add_row;
916 
917 
918   PROCEDURE delete_row (
919     x_rowid IN VARCHAR2,
920   x_mode IN VARCHAR2
921   ) AS
922   /*
923   ||  Created By : [email protected]
924   ||  Created On : 10-OCT-2003
925   ||  Purpose : Handles the DELETE DML logic for the table.
926   ||  Known limitations, enhancements or remarks :
927   ||  Change History :
928   ||  Who             When            What
929   ||  (reverse chronological order - newest change first)
930   */
931   BEGIN
932 
933     before_dml (
934       p_action => 'DELETE',
935       x_rowid => x_rowid
936     );
937 
938     IF (x_mode = 'S') THEN
939     igs_sc_gen_001.set_ctx('R');
940   END IF;
941   DELETE FROM igs_as_sua_ai_group
942     WHERE rowid = x_rowid;
943 
944     IF (SQL%NOTFOUND) THEN
945      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
946      igs_ge_msg_stack.add;
947      igs_sc_gen_001.unset_ctx('R');
948      app_exception.raise_exception;
949  END IF;
950  IF (x_mode = 'S') THEN
951     igs_sc_gen_001.unset_ctx('R');
952   END IF;
953 
954 
955   END delete_row;
956 
957 
958 END igs_as_sua_ai_group_pkg;