DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PS_UNITASS_ITEM_PKG

Source


1 PACKAGE BODY igs_ps_unitass_item_pkg AS
2 /* $Header: IGSPI0KB.pls 120.1 2006/09/20 07:27:13 sepalani noship $ */
3   -- Bug# 1956374 , procedure assp_val_ai_exists,assp_val_uai_uapi,assp_val_uai_uniqref reference is changed
4   l_rowid               VARCHAR2 (25);
5   old_references        igs_ps_unitass_item%ROWTYPE;
6   new_references        igs_ps_unitass_item%ROWTYPE;
7   lv_cal_type           VARCHAR2 (10);
8   lv_unit_cd            VARCHAR2 (10);
9   lv_version_number     NUMBER;
10   lv_ci_sequence_number NUMBER;
11   PROCEDURE set_column_values (
12     p_action                       IN     VARCHAR2,
13     x_rowid                        IN     VARCHAR2 DEFAULT NULL,
14     x_unit_section_ass_item_id     IN     NUMBER DEFAULT NULL,
15     x_uoo_id                       IN     NUMBER DEFAULT NULL,
16     x_ass_id                       IN     NUMBER DEFAULT NULL,
17     x_sequence_number              IN     NUMBER DEFAULT NULL,
18     x_ci_start_dt                  IN     DATE DEFAULT NULL,
19     x_ci_end_dt                    IN     DATE DEFAULT NULL,
20     x_due_dt                       IN     DATE DEFAULT NULL,
21     x_reference                    IN     VARCHAR2 DEFAULT NULL,
22     x_dflt_item_ind                IN     VARCHAR2 DEFAULT NULL,
23     x_logical_delete_dt            IN     DATE DEFAULT NULL,
24     x_action_dt                    IN     DATE DEFAULT NULL,
25     x_exam_cal_type                IN     VARCHAR2 DEFAULT NULL,
26     x_exam_ci_sequence_number      IN     NUMBER DEFAULT NULL,
27     x_creation_date                IN     DATE DEFAULT NULL,
28     x_created_by                   IN     NUMBER DEFAULT NULL,
29     x_last_update_date             IN     DATE DEFAULT NULL,
30     x_last_updated_by              IN     NUMBER DEFAULT NULL,
31     x_last_update_login            IN     NUMBER DEFAULT NULL,
32     x_grading_schema_cd            IN     VARCHAR2 DEFAULT NULL,
33     x_gs_version_number            IN     NUMBER DEFAULT NULL,
34     x_release_date                 IN     DATE DEFAULT NULL,
35     x_description                  IN     VARCHAR2 DEFAULT NULL,
36     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
37     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
38     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
39     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
40     x_final_weight_qty             IN     NUMBER DEFAULT NULL
41   ) AS
42   /*************************************************************
43   Created By      : jdeekoll
44   Date Created By : 11-May-2000
45   Purpose :
46   Know limitations, enhancements or remarks
47   Change History
48   Who             When            What
49   (reverse chronological order - newest change first)
50   ***************************************************************/
51     CURSOR cur_old_ref_values IS
52       SELECT *
53       FROM   igs_ps_unitass_item
54       WHERE  ROWID = x_rowid;
55   BEGIN
56     l_rowid := x_rowid;
57     -- Code for setting the Old and New Reference Values.
58     -- Populate Old Values.
59     OPEN cur_old_ref_values;
60     FETCH cur_old_ref_values INTO old_references;
61     IF  (cur_old_ref_values%NOTFOUND)
62         AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
63       CLOSE cur_old_ref_values;
64       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
65       igs_ge_msg_stack.ADD;
66       app_exception.raise_exception;
67       RETURN;
68     END IF;
69     CLOSE cur_old_ref_values;
70     -- Populate New Values.
71     new_references.unit_section_ass_item_id := x_unit_section_ass_item_id;
72     new_references.uoo_id := x_uoo_id;
73     new_references.ass_id := x_ass_id;
74     new_references.sequence_number := x_sequence_number;
75     new_references.ci_start_dt := x_ci_start_dt;
76     new_references.ci_end_dt := x_ci_end_dt;
77     new_references.due_dt := x_due_dt;
78     new_references.REFERENCE := x_reference;
79     new_references.dflt_item_ind := x_dflt_item_ind;
80     new_references.logical_delete_dt := x_logical_delete_dt;
81     new_references.action_dt := x_action_dt;
82     new_references.exam_cal_type := x_exam_cal_type;
83     new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
84     new_references.grading_schema_cd := x_grading_schema_cd;
85     new_references.gs_version_number := x_gs_version_number;
86     new_references.release_date := x_release_date;
87     new_references.description := x_description;
88     new_references.us_ass_item_group_id := x_us_ass_item_group_id;
89     new_references.midterm_mandatory_type_code := x_midterm_mandatory_type_code;
90     new_references.midterm_weight_qty := x_midterm_weight_qty;
91     new_references.final_mandatory_type_code := x_final_mandatory_type_code;
92     new_references.final_weight_qty := x_final_weight_qty;
93     IF (p_action = 'UPDATE') THEN
94       new_references.creation_date := old_references.creation_date;
95       new_references.created_by := old_references.created_by;
96     ELSE
97       new_references.creation_date := x_creation_date;
98       new_references.created_by := x_created_by;
99     END IF;
100     new_references.last_update_date := x_last_update_date;
101     new_references.last_updated_by := x_last_updated_by;
102     new_references.last_update_login := x_last_update_login;
103   END set_column_values;
104 
105   PROCEDURE beforerowinsertupdate1 (
106     p_inserting                    IN     BOOLEAN DEFAULT FALSE,
107     p_updating                     IN     BOOLEAN DEFAULT FALSE,
108     p_deleting                     IN     BOOLEAN DEFAULT FALSE
109   ) AS
110     v_message_name VARCHAR2 (30);
111   BEGIN
112     -- Validate that inserts/updates are allowed
113     IF p_inserting
114        OR p_updating THEN
115       --<Start uai1>
116       -- Validate assessment item exists
117       -- w.r.t Bug  # 1956374 procedure assp_val_ai_exists reference is changed
118       IF igs_as_val_uai.assp_val_ai_exists (new_references.ass_id, v_message_name) = FALSE THEN
119         fnd_message.set_name ('IGS', v_message_name);
120         igs_ge_msg_stack.ADD;
121         app_exception.raise_exception;
122       END IF;
123       --<<End uai1>>
124       --<Start uai2>
125       -- Validate IGS_AD_LOCATION closed indicator
126       -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UAI.crsp_val_loc_cd
127       IF igs_ps_val_uoo.crsp_val_loc_cd (new_references.location_cd, v_message_name) = FALSE THEN
128         fnd_message.set_name ('IGS', v_message_name);
129         igs_ge_msg_stack.ADD;
130         app_exception.raise_exception;
131       END IF;
132       --<<End uai2>>
133       --<Start uai3>
134       -- Validate IGS_PS_UNIT mode closed indicator
135       -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UAI.crsp_val_um_closed
136       --<<End uai4>>
137       --<Start uai10>
138       -- If the IGS_PS_UNIT version status is inactive then prevent inserts, updates and
139       -- deletes. As deletes are logical, they are equiv to updates and delete
140       -- trigger is not required.
141       IF igs_ps_val_unit.crsp_val_iud_uv_dtl (lv_unit_cd, lv_version_number, v_message_name) = FALSE THEN
142         fnd_message.set_name ('IGS', v_message_name);
143         igs_ge_msg_stack.ADD;
144         app_exception.raise_exception;
145       END IF;
146       --<<End uai10>>
147       --<Start uai11>
148       -- If calendar instance is inactive, then prevent inserts, updates and
149       -- deletes. As deletes are logical, they are equiv to updates and delete
150       -- trigger is not required.
151       IF igs_ps_val_uai.crsp_val_crs_ci (lv_cal_type, lv_ci_sequence_number, v_message_name) = FALSE THEN
152         fnd_message.set_name ('IGS', v_message_name);
153         igs_ge_msg_stack.ADD;
154         app_exception.raise_exception;
155       END IF;
156       --<<End uai11>>
157       --<Start uai15>
158       -- If item is examinable then validate that reference is set.
159       -- w.r.t Bug  # 1956374 procedure assp_val_ai_exmnbl reference is changed
160       IF  NVL (new_references.REFERENCE, 'NULL666') = 'NULL666'
161           AND (igs_as_val_aiem.assp_val_ai_exmnbl (new_references.ass_id, v_message_name)
162                OR igs_as_gen_002.assp_get_ai_s_type (new_references.ass_id) = 'ASSIGNMENT'
163               ) THEN
164         fnd_message.set_name ('IGS', 'IGS_AS_REF_ASSITEM_EXAM');
165         igs_ge_msg_stack.ADD;
166         app_exception.raise_exception;
167       END IF;
168       --<<End uai15>>
169       --<Start uai16>
170       -- Validate the examination calendar type/sequence number if they have
171       -- been specified.
172       IF new_references.exam_cal_type IS NOT NULL THEN
173         IF igs_as_val_uai.assp_val_uai_cal (
174              new_references.exam_cal_type,
175              new_references.exam_ci_sequence_number,
176              lv_cal_type,
177              lv_ci_sequence_number,
178              v_message_name
179            ) = FALSE THEN
180           fnd_message.set_name ('IGS', v_message_name);
181           igs_ge_msg_stack.ADD;
182           app_exception.raise_exception;
183         END IF;
184       END IF;
185       --<<End uai16>>
186       IF p_inserting THEN
187           --<Start uai12>
188         -- If calendar type is closed, then prevent inserts.
189         -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UAI.crsp_val_uo_cal_type
190         IF igs_as_val_uai.crsp_val_uo_cal_type (lv_cal_type, v_message_name) = FALSE THEN
191           fnd_message.set_name ('IGS', v_message_name);
192           igs_ge_msg_stack.ADD;
193           app_exception.raise_exception;
194         END IF;
195       --<<End uai12>>
196       END IF;
197       -- Commented out NOCOPY  by DDEY as a part of Bug # 2162831
198 /*      IF p_updating AND
199          ((NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
200     NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')))
201           (NVL(new_references.location_cd, 'NULL') <> NVL(old_references.location_cd, 'NULL')) OR
202           (NVL(new_references.unit_class, 'NULL') <> NVL(old_references.unit_class, 'NULL')) OR
203           (NVL(new_references.unit_mode,'NULL') <> NVL(old_references.unit_mode, 'NULL')))
204      THEN
205     -- Only perform this validation if not in the process of performing a
206     -- cascading update from altering the IGS_AS_UNTAS_PATTERN
207     -- fields of IGS_AD_LOCATION, class and mode. (Refer to IGS_AS_GEN_005.ASSP_UPD_UAP_UOO)
208     IF igs_as_val_suaap.genp_val_sdtt_sess('UNIT_ASSESSMENT_PATTERN_UPD') THEN
209       IF IGS_AS_VAL_UAI.assp_val_uai_uapi(lv_unit_cd,
210           lv_version_number,
211           lv_cal_type,
212           lv_ci_sequence_number,
213           new_references.ass_id,
214           new_references.sequence_number,
215           old_references.location_cd,
216           old_references.unit_class,
217           old_references.unit_mode,
218           old_references.logical_delete_dt,
219           new_references.location_cd,
220           new_references.unit_class,
221           new_references.unit_mode,
222           new_references.logical_delete_dt,
223           v_message_name) = FALSE THEN
224                      Fnd_Message.Set_Name('IGS', v_message_name);
225                      IGS_GE_MSG_STACK.ADD;
226                      App_Exception.Raise_Exception;
227       END IF;
228     END IF;
229       END IF;   */
230     END IF;
231   END beforerowinsertupdate1;
232   -- Trigger description :-
233   -- "OSS_TST".trg_uai_br_iu_upd
234   -- BEFORE INSERT OR UPDATE
235   -- ON IGS_AS_UNITASS_ITEM
236   -- FOR EACH ROW
237   PROCEDURE beforerowinsertupdate2 (
238     p_inserting                    IN     BOOLEAN DEFAULT FALSE,
239     p_updating                     IN     BOOLEAN DEFAULT FALSE,
240     p_deleting                     IN     BOOLEAN DEFAULT FALSE
241   ) AS
242     CURSOR c_ci (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE) IS
243       SELECT start_dt,
244              end_dt
245       FROM   igs_ca_inst
246       WHERE  cal_type = cp_cal_type
247       AND    sequence_number = cp_seq_number;
248   BEGIN
249     IF p_inserting THEN
250       -- Temporary code to set the start/end date - to be replaced
251       -- with a database routine rather than an embedded cursor
252       -- Start and end date are carried down from UOP for sorting
253       -- purposes.
254       FOR v_ci_rec IN c_ci (lv_cal_type, lv_ci_sequence_number) LOOP
255         new_references.ci_start_dt := v_ci_rec.start_dt;
256         new_references.ci_end_dt := v_ci_rec.end_dt;
257       END LOOP;
258     END IF;
259     IF p_inserting
260        OR p_updating THEN
261       -- Always update the action date when p_inserting/p_updating/p_deleting
262       -- a UAI. This enable mechanism for knowing when an item needs to
263       -- be added to a student.
264       -- Updates only pertain to changes for locn, um and ucl.
265       -- Please IGS_GE_NOTE that p_deleting a UAI is only a logical delete and
266       -- therefore really an update.
267       IF ( /* NVL(new_references.location_cd, 'x')   <> NVL(old_references.location_cd, 'x') OR
268      NVL(new_references.unit_mode,'x') <> NVL(old_references.unit_mode,'x') OR
269      NVL(new_references.unit_class,'x')  <> NVL(old_references.unit_class,'x') OR  */  -- Commented out NOCOPY  by DDEY as a part of Bug # 2162831
270           NVL (new_references.dflt_item_ind, 'x') <> NVL (old_references.dflt_item_ind, 'x')
271           OR NVL (new_references.logical_delete_dt, igs_ge_date.igsdate ('1900/01/01')) <>
272              NVL (old_references.logical_delete_dt, igs_ge_date.igsdate ('1900/01/01'))
273          ) THEN
274         IF NVL (old_references.action_dt, igs_ge_date.igsdate ('1900/01/01')) = igs_ge_date.igsdate ('1900/01/01') THEN
275           new_references.action_dt := TRUNC (SYSDATE);
276         END IF;
277       END IF;
278     END IF;
279   END beforerowinsertupdate2;
280   -- Trigger description :-
281   -- "OSS_TST".trg_uai_ar_iu
282   -- AFTER INSERT OR UPDATE
283   -- ON IGS_AS_UNITASS_ITEM
284   -- FOR EACH ROW
285   PROCEDURE afterrowinsertupdate3 (
286     p_inserting                    IN     BOOLEAN DEFAULT FALSE,
287     p_updating                     IN     BOOLEAN DEFAULT FALSE,
288     p_deleting                     IN     BOOLEAN DEFAULT FALSE
289   ) AS
290     v_message_name VARCHAR2 (30);
291   BEGIN
292     IF p_inserting
293        OR p_updating THEN
294       -- w.r.t Bug  # 1956374 procedure assp_val_ai_exmnbl reference is changed
295       IF igs_as_val_aiem.assp_val_ai_exmnbl (new_references.ass_id, v_message_name) = TRUE THEN
296         --<uai13>
297         -- Validate that the reference number id unique within a UOP
298         IF igs_as_val_uai.assp_val_uai_uniqref (
299              lv_unit_cd,
300              lv_version_number,
301              lv_cal_type,
302              lv_ci_sequence_number,
303              new_references.sequence_number,
304              new_references.REFERENCE,
305              new_references.ass_id,
306              v_message_name
307            ) = FALSE THEN
308           fnd_message.set_name ('IGS', v_message_name);
309           igs_ge_msg_stack.ADD;
310           app_exception.raise_exception;
311         END IF;
312       ELSE
313         --<uai14>
314         -- if record has not been deleted
315         IF NVL (new_references.logical_delete_dt, igs_ge_date.igsdate ('1900/01/01')) =
316            igs_ge_date.igsdate ('1900/01/01') THEN
317           -- Validate that the reference number id unique within a UOP
318           IF igs_ps_val_uai.assp_val_uai_opt_ref (
319                lv_unit_cd,
320                lv_version_number,
321                lv_cal_type,
322                lv_ci_sequence_number,
323                new_references.sequence_number,
324                new_references.REFERENCE,
325                new_references.ass_id,
326                igs_as_gen_001.assp_get_ai_a_type (new_references.ass_id),
327                v_message_name
328              ) = FALSE THEN
329             fnd_message.set_name ('IGS', v_message_name);
330             igs_ge_msg_stack.ADD;
331             app_exception.raise_exception;
332           END IF;
333         END IF;
334       END IF;
335     -- Save the rowid of the current row.
336     END IF;
337   END afterrowinsertupdate3;
338 
339   PROCEDURE check_constraints (column_name IN VARCHAR2 DEFAULT NULL, column_value IN VARCHAR2 DEFAULT NULL) AS
340   /*************************************************************
341   Created By      : jdeekoll
342   Date Created By : 11-May-2000
343   Purpose :
344   Know limitations, enhancements or remarks
345   Change History
346   Who             When            What
347   (reverse chronological order - newest change first)
348   ***************************************************************/
349   BEGIN
350     NULL;
351   END check_constraints;
352 
353   PROCEDURE check_uniqueness AS
354   /*************************************************************
355   Created By      : jdeekoll
356   Date Created By : 11-May-2000
357   Purpose :
358   Know limitations, enhancements or remarks
359   Change History
360   Who             When            What
361   (reverse chronological order - newest change first)
362   ***************************************************************/
363   BEGIN
364     IF get_uk_for_validation (new_references.ass_id, new_references.sequence_number, new_references.uoo_id) THEN
365       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
366       igs_ge_msg_stack.ADD;
367       app_exception.raise_exception;
368     END IF;
369   END check_uniqueness;
370 
371   PROCEDURE check_parent_existance AS
372   /*************************************************************
373   Created By      : jdeekoll
374   Date Created By : 11-May-2000
375   Purpose :
376   Know limitations, enhancements or remarks
377   Change History
378   Who             When            What
379   (reverse chronological order - newest change first)
380   ***************************************************************/
381   BEGIN
382     IF (((old_references.us_ass_item_group_id = new_references.us_ass_item_group_id))
383         OR ((new_references.us_ass_item_group_id IS NULL))
384        ) THEN
385       NULL;
386     ELSIF NOT igs_as_us_ai_group_pkg.get_pk_for_validation (
387                 new_references.us_ass_item_group_id
388               ) THEN
389       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
390       igs_ge_msg_stack.ADD;
391       app_exception.raise_exception;
392     END IF;
393   END check_parent_existance;
394 
395   -- ADDED BY DDEY FOR BUG # 2162831
396   PROCEDURE get_fk_igs_as_grd_schema (x_grading_schema_cd IN VARCHAR2, x_version_number IN NUMBER) AS
397     CURSOR cur_rowid IS
398       SELECT ROWID
399       FROM   igs_ps_unitass_item
400       WHERE  grading_schema_cd = x_grading_schema_cd
401       AND    gs_version_number = x_version_number;
402     lv_rowid cur_rowid%ROWTYPE;
403   BEGIN
404     OPEN cur_rowid;
405     FETCH cur_rowid INTO lv_rowid;
406     IF (cur_rowid%FOUND) THEN
407       fnd_message.set_name ('IGS', 'IGS_AS_UNAS_GS_FK');
408       igs_ge_msg_stack.ADD;
409       CLOSE cur_rowid;
410       app_exception.raise_exception;
411       RETURN;
412     END IF;
413     CLOSE cur_rowid;
414   END get_fk_igs_as_grd_schema;
415 
416   PROCEDURE get_fk_igs_as_us_ai_group (x_us_ass_item_group_id IN NUMBER) AS
417     CURSOR cur_rowid IS
418       SELECT ROWID
419       FROM   igs_ps_unitass_item
420       WHERE  us_ass_item_group_id = x_us_ass_item_group_id;
421     lv_rowid cur_rowid%ROWTYPE;
422   BEGIN
423     OPEN cur_rowid;
424     FETCH cur_rowid INTO lv_rowid;
425     IF (cur_rowid%FOUND) THEN
426       fnd_message.set_name ('IGS', 'IGS_AS_UNAS_USAI_FK');
427       igs_ge_msg_stack.ADD;
428       CLOSE cur_rowid;
429       app_exception.raise_exception;
430       RETURN;
431     END IF;
432     CLOSE cur_rowid;
433   END get_fk_igs_as_us_ai_group;
434 
435   FUNCTION get_pk_for_validation (x_unit_section_ass_item_id IN NUMBER)
436     RETURN BOOLEAN AS
437   /*************************************************************
438   Created By      : jdeekoll
439   Date Created By : 11-May-2000
440   Purpose :
441   Know limitations, enhancements or remarks
442   Change History
443   Who             When            What
444   (reverse chronological order - newest change first)
445   ***************************************************************/
446     CURSOR cur_rowid IS
447       SELECT     ROWID
448       FROM       igs_ps_unitass_item
449       WHERE      unit_section_ass_item_id = x_unit_section_ass_item_id
450       FOR UPDATE NOWAIT;
451     lv_rowid cur_rowid%ROWTYPE;
452   BEGIN
453     OPEN cur_rowid;
454     FETCH cur_rowid INTO lv_rowid;
455     IF (cur_rowid%FOUND) THEN
456       CLOSE cur_rowid;
457       RETURN (TRUE);
458     ELSE
459       CLOSE cur_rowid;
460       RETURN (FALSE);
461     END IF;
462   END get_pk_for_validation;
463 
464   FUNCTION get_uk_for_validation (x_ass_id IN NUMBER, x_sequence_number IN NUMBER, x_uoo_id IN NUMBER)
465     RETURN BOOLEAN AS
466   /*************************************************************
467   Created By      : jdeekoll
468   Date Created By : 11-May-2000
469   Purpose :
470   Know limitations, enhancements or remarks
471   Change History
472   Who             When            What
473   DDEY          8-JAN-2002       The changes done as per DLD Calculation
474                                  of Results Part 1.
475   jdeekoll      27-MAR-2002      Removed the changes implemented by DDEY as
476                                  a part of DLD calculation Results Part 1
477                                  Confirmed with DDEY/PRGOYAL. Copied the
478                                  original get_fk from 115.6 version
479   (reverse chronological order - newest change first)
480   ***************************************************************/
481     CURSOR cur_rowid IS
482       SELECT ROWID
483       FROM   igs_ps_unitass_item
484       WHERE  ass_id = x_ass_id
485       AND    sequence_number = x_sequence_number
486       AND    uoo_id = x_uoo_id
487       AND    ((l_rowid IS NULL)
488               OR (ROWID <> l_rowid)
489              );
490     lv_rowid cur_rowid%ROWTYPE;
491   BEGIN
492     OPEN cur_rowid;
493     FETCH cur_rowid INTO lv_rowid;
494     IF (cur_rowid%FOUND) THEN
495       CLOSE cur_rowid;
496       RETURN (TRUE);
497     ELSE
498       CLOSE cur_rowid;
499       RETURN (FALSE);
500     END IF;
501   END get_uk_for_validation;
502 
503   PROCEDURE before_dml (
504     p_action                       IN     VARCHAR2,
505     x_rowid                        IN     VARCHAR2 DEFAULT NULL,
506     x_unit_section_ass_item_id     IN     NUMBER DEFAULT NULL,
507     x_uoo_id                       IN     NUMBER DEFAULT NULL,
508     x_ass_id                       IN     NUMBER DEFAULT NULL,
509     x_sequence_number              IN     NUMBER DEFAULT NULL,
510     x_ci_start_dt                  IN     DATE DEFAULT NULL,
511     x_ci_end_dt                    IN     DATE DEFAULT NULL,
512     x_due_dt                       IN     DATE DEFAULT NULL,
513     x_reference                    IN     VARCHAR2 DEFAULT NULL,
514     x_dflt_item_ind                IN     VARCHAR2 DEFAULT NULL,
515     x_logical_delete_dt            IN     DATE DEFAULT NULL,
516     x_action_dt                    IN     DATE DEFAULT NULL,
517     x_exam_cal_type                IN     VARCHAR2 DEFAULT NULL,
518     x_exam_ci_sequence_number      IN     NUMBER DEFAULT NULL,
519     x_creation_date                IN     DATE DEFAULT NULL,
520     x_created_by                   IN     NUMBER DEFAULT NULL,
521     x_last_update_date             IN     DATE DEFAULT NULL,
522     x_last_updated_by              IN     NUMBER DEFAULT NULL,
523     x_last_update_login            IN     NUMBER DEFAULT NULL,
524     x_grading_schema_cd            IN     VARCHAR2 DEFAULT NULL,
525     x_gs_version_number            IN     NUMBER DEFAULT NULL,
526     x_release_date                 IN     DATE DEFAULT NULL,
527     x_description                  IN     VARCHAR2 DEFAULT NULL,
528     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
529     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
530     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
531     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
532     x_final_weight_qty             IN     NUMBER DEFAULT NULL
533   ) AS
534   /*************************************************************
535   Created By      : jdeekoll
536   Date Created By : 11-May-2000
537   Purpose :
538   Know limitations, enhancements or remarks
539   Change History
540   Who             When            What
541   (reverse chronological order - newest change first)
542   ***************************************************************/
543     CURSOR c1 IS
544       SELECT unit_cd,
545              cal_type,
546              version_number,
547              ci_sequence_number
548       FROM   igs_ps_unit_ofr_opt
549       WHERE  uoo_id = new_references.uoo_id;
550   BEGIN
551     set_column_values (
552       p_action,
553       x_rowid,
554       x_unit_section_ass_item_id,
555       x_uoo_id,
556       x_ass_id,
557       x_sequence_number,
558       x_ci_start_dt,
559       x_ci_end_dt,
560       x_due_dt,
561       x_reference,
562       x_dflt_item_ind,
563       x_logical_delete_dt,
564       x_action_dt,
565       x_exam_cal_type,
566       x_exam_ci_sequence_number,
567       x_creation_date,
568       x_created_by,
569       x_last_update_date,
570       x_last_updated_by,
571       x_last_update_login,
572       x_grading_schema_cd,
573       x_gs_version_number,
574       x_release_date,
575       x_description,
576       x_us_ass_item_group_id,
577       x_midterm_mandatory_type_code,
578       x_midterm_weight_qty,
579       x_final_mandatory_type_code,
580       x_final_weight_qty
581     );
582     /* Code written because cal_type and lv_unit_cd are not available in this field and being fetched from
583        IGS_PS_UNIT_OFR_OPT table thru uoo_id */
584     FOR i IN c1 LOOP
585       lv_unit_cd := i.unit_cd;
586       lv_cal_type := i.cal_type;
587       lv_version_number := i.version_number;
588       lv_ci_sequence_number := i.ci_sequence_number;
589     END LOOP;
590     IF (p_action = 'INSERT') THEN
591       -- Call all the procedures related to Before Insert.
592       -- BeforeRowInsertUpdate1 ( p_inserting => TRUE );
593       beforerowinsertupdate2 (p_inserting => TRUE);
594       IF get_pk_for_validation (new_references.unit_section_ass_item_id) THEN
595         fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
596         igs_ge_msg_stack.ADD;
597         app_exception.raise_exception;
598       END IF;
599       check_uniqueness;
600       check_parent_existance;
601     ELSIF (p_action = 'UPDATE') THEN
602       -- Call all the procedures related to Before Update.
603       --BeforeRowInsertUpdate1 ( p_inserting => TRUE );
604       beforerowinsertupdate2 (p_inserting => TRUE);
605       check_uniqueness;
606       check_parent_existance;
607     ELSIF (p_action = 'DELETE') THEN
608       -- Call all the procedures related to Before Delete.
609       NULL;
610     ELSIF (p_action = 'VALIDATE_INSERT') THEN
611       -- Call all the procedures related to Before Insert.
612       IF get_pk_for_validation (new_references.unit_section_ass_item_id) THEN
613         fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
614         igs_ge_msg_stack.ADD;
615         app_exception.raise_exception;
616       END IF;
617       check_uniqueness;
618     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
619       check_uniqueness;
620     ELSIF (p_action = 'VALIDATE_DELETE') THEN
621       NULL;
622     END IF;
623   END before_dml;
624 
625   PROCEDURE after_dml (p_action IN VARCHAR2, x_rowid IN VARCHAR2) AS
626   /*************************************************************
627   Created By      : jdeekoll
628   Date Created By : 11-May-2000
629   Purpose :
630   Know limitations, enhancements or remarks
631   Change History
632   Who             When            What
633   (reverse chronological order - newest change first)
634   ***************************************************************/
635   BEGIN
636     l_rowid := x_rowid;
637     IF (p_action = 'INSERT') THEN
638       -- Call all the procedures related to After Insert.
639       --   AfterRowInsertUpdate3 ( p_inserting => TRUE );
640       NULL;
641     ELSIF (p_action = 'UPDATE') THEN
642       -- Call all the procedures related to After Update.
643       afterrowinsertupdate3 (p_inserting => TRUE);
644     ELSIF (p_action = 'DELETE') THEN
645       -- Call all the procedures related to After Delete.
646       NULL;
647     END IF;
648     l_rowid := NULL;
649   END after_dml;
650 
651   PROCEDURE insert_row (
652     x_rowid                        IN OUT NOCOPY VARCHAR2,
653     x_unit_section_ass_item_id     IN OUT NOCOPY NUMBER,
654     x_uoo_id                       IN     NUMBER,
655     x_ass_id                       IN     NUMBER,
656     x_sequence_number              IN OUT NOCOPY NUMBER,
657     x_ci_start_dt                  IN OUT NOCOPY DATE,
658     x_ci_end_dt                    IN OUT NOCOPY DATE,
659     x_due_dt                       IN     DATE,
660     x_reference                    IN     VARCHAR2,
661     x_dflt_item_ind                IN     VARCHAR2,
662     x_logical_delete_dt            IN     DATE,
663     x_action_dt                    IN     DATE,
664     x_exam_cal_type                IN     VARCHAR2,
665     x_exam_ci_sequence_number      IN     NUMBER,
666     x_mode                         IN     VARCHAR2 DEFAULT 'R',
667     x_grading_schema_cd            IN     VARCHAR2,
668     x_gs_version_number            IN     NUMBER,
669     x_release_date                 IN     DATE,
670     x_description                  IN     VARCHAR2 DEFAULT NULL,
671     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
672     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
673     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
674     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
675     x_final_weight_qty             IN     NUMBER DEFAULT NULL
676   ) AS
677   /*************************************************************
678   Created By      : jdeekoll
679   Date Created By : 11-May-2000
680   Purpose :
681   Know limitations, enhancements or remarks
682   Change History
683   Who             When            What
684   (reverse chronological order - newest change first)
685   ***************************************************************/
686     CURSOR c IS
687       SELECT ROWID
688       FROM   igs_ps_unitass_item
689       WHERE  unit_section_ass_item_id = x_unit_section_ass_item_id;
690     x_last_update_date       DATE;
691     x_last_updated_by        NUMBER;
692     x_last_update_login      NUMBER;
693     x_request_id             NUMBER;
694     x_program_id             NUMBER;
695     x_program_application_id NUMBER;
696     x_program_update_date    DATE;
697   BEGIN
698     x_last_update_date := SYSDATE;
699     IF (x_mode = 'I') THEN
700       x_last_updated_by := 1;
701       x_last_update_login := 0;
702     ELSIF (x_mode = 'R') THEN
703       x_last_updated_by := fnd_global.user_id;
704       IF x_last_updated_by IS NULL THEN
705         x_last_updated_by := -1;
706       END IF;
707       x_last_update_login := fnd_global.login_id;
708       IF x_last_update_login IS NULL THEN
709         x_last_update_login := -1;
710       END IF;
711       x_request_id := fnd_global.conc_request_id;
712       x_program_id := fnd_global.conc_program_id;
713       x_program_application_id := fnd_global.prog_appl_id;
714       IF (x_request_id = -1) THEN
715         x_request_id := NULL;
716         x_program_id := NULL;
717         x_program_application_id := NULL;
718         x_program_update_date := NULL;
719       ELSE
720         x_program_update_date := SYSDATE;
721       END IF;
722     ELSE
723       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
724       igs_ge_msg_stack.ADD;
725       app_exception.raise_exception;
726     END IF;
727     SELECT igs_ps_unitass_item_s.NEXTVAL
728     INTO   x_unit_section_ass_item_id
729     FROM   DUAL;
730     SELECT igs_ps_unitass_item_seq_num_s.NEXTVAL
731     INTO   x_sequence_number
732     FROM   DUAL;
733     before_dml (
734       p_action                       => 'INSERT',
735       x_rowid                        => x_rowid,
736       x_unit_section_ass_item_id     => x_unit_section_ass_item_id,
737       x_uoo_id                       => x_uoo_id,
738       x_ass_id                       => x_ass_id,
739       x_sequence_number              => x_sequence_number,
740       x_ci_start_dt                  => x_ci_start_dt,
741       x_ci_end_dt                    => x_ci_end_dt,
742       x_due_dt                       => x_due_dt,
743       x_reference                    => x_reference,
744       x_dflt_item_ind                => x_dflt_item_ind,
745       x_logical_delete_dt            => x_logical_delete_dt,
746       x_action_dt                    => x_action_dt,
747       x_exam_cal_type                => x_exam_cal_type,
748       x_exam_ci_sequence_number      => x_exam_ci_sequence_number,
749       x_creation_date                => x_last_update_date,
750       x_created_by                   => x_last_updated_by,
751       x_last_update_date             => x_last_update_date,
752       x_last_updated_by              => x_last_updated_by,
753       x_last_update_login            => x_last_update_login,
754       x_grading_schema_cd            => x_grading_schema_cd,
755       x_gs_version_number            => x_gs_version_number,
756       x_release_date                 => x_release_date,
757       x_description                  => x_description,
758       x_us_ass_item_group_id         => x_us_ass_item_group_id,
759       x_midterm_mandatory_type_code  => x_midterm_mandatory_type_code,
760       x_midterm_weight_qty           => x_midterm_weight_qty,
761       x_final_mandatory_type_code    => x_final_mandatory_type_code,
762       x_final_weight_qty             => x_final_weight_qty
763     );
764     INSERT INTO igs_ps_unitass_item
765                 (unit_section_ass_item_id, uoo_id, ass_id,
766                  sequence_number, ci_start_dt, ci_end_dt,
767                  due_dt, REFERENCE, dflt_item_ind,
768                  logical_delete_dt, action_dt, exam_cal_type,
769                  exam_ci_sequence_number, grading_schema_cd,
770                  gs_version_number, release_date, creation_date, created_by,
771                  last_update_date, last_updated_by, last_update_login, request_id, program_id,
772                  program_application_id, program_update_date, description,
773                  us_ass_item_group_id,
774                  midterm_mandatory_type_code,
775                  midterm_weight_qty,
776                  final_mandatory_type_code,
777                  final_weight_qty)
778          VALUES (new_references.unit_section_ass_item_id, new_references.uoo_id, new_references.ass_id,
779                  new_references.sequence_number, new_references.ci_start_dt, new_references.ci_end_dt,
780                  new_references.due_dt, new_references.REFERENCE, new_references.dflt_item_ind,
781                  new_references.logical_delete_dt, new_references.action_dt, new_references.exam_cal_type,
782                  new_references.exam_ci_sequence_number, new_references.grading_schema_cd,
783                  new_references.gs_version_number, new_references.release_date, x_last_update_date, x_last_updated_by,
784                  x_last_update_date, x_last_updated_by, x_last_update_login, x_request_id, x_program_id,
785                  x_program_application_id, x_program_update_date, new_references.description,
786                  new_references.us_ass_item_group_id,
787                  new_references.midterm_mandatory_type_code,
788                  new_references.midterm_weight_qty,
789                  new_references.final_mandatory_type_code,
790                  new_references.final_weight_qty);
791     OPEN c;
792     FETCH c INTO x_rowid;
793     IF (c%NOTFOUND) THEN
794       CLOSE c;
795       RAISE NO_DATA_FOUND;
796     END IF;
797     CLOSE c;
798     after_dml (p_action => 'INSERT', x_rowid => x_rowid);
799     x_ci_start_dt := new_references.ci_start_dt;
800     x_ci_end_dt := new_references.ci_end_dt;
801   END insert_row;
802 
803   PROCEDURE lock_row (
804     x_rowid                        IN     VARCHAR2,
805     x_unit_section_ass_item_id     IN     NUMBER,
806     x_uoo_id                       IN     NUMBER,
807     x_ass_id                       IN     NUMBER,
808     x_sequence_number              IN     NUMBER,
809     x_ci_start_dt                  IN     DATE,
810     x_ci_end_dt                    IN     DATE,
811     x_due_dt                       IN     DATE,
812     x_reference                    IN     VARCHAR2,
813     x_dflt_item_ind                IN     VARCHAR2,
814     x_logical_delete_dt            IN     DATE,
815     x_action_dt                    IN     DATE,
816     x_exam_cal_type                IN     VARCHAR2,
817     x_exam_ci_sequence_number      IN     NUMBER,
818     x_grading_schema_cd            IN     VARCHAR2,
819     x_gs_version_number            IN     NUMBER,
820     x_release_date                 IN     DATE,
821     x_description                  IN     VARCHAR2 DEFAULT NULL,
822     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
823     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
824     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
825     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
826     x_final_weight_qty             IN     NUMBER DEFAULT NULL
827   ) AS
828   /*************************************************************
829   Created By      : jdeekoll
830   Date Created By : 11-May-2000
831   Purpose :
832   Know limitations, enhancements or remarks
833   Change History
834   Who             When            What
835   (reverse chronological order - newest change first)
836   ***************************************************************/
837     CURSOR c1 IS
838       SELECT     uoo_id,
839                  ass_id,
840                  sequence_number,
841                  ci_start_dt,
842                  ci_end_dt,
843                  due_dt,
844                  REFERENCE,
845                  dflt_item_ind,
846                  logical_delete_dt,
847                  action_dt,
848                  exam_cal_type,
849                  exam_ci_sequence_number,
850                  grading_schema_cd,
851                  gs_version_number,
852                  release_date,
853                  description,
854                  us_ass_item_group_id,
855                  midterm_mandatory_type_code,
856                  midterm_weight_qty,
857                  final_mandatory_type_code,
858                  final_weight_qty
859       FROM       igs_ps_unitass_item
860       WHERE      ROWID = x_rowid
861       FOR UPDATE NOWAIT;
862     tlinfo c1%ROWTYPE;
863   BEGIN
864     OPEN c1;
865     FETCH c1 INTO tlinfo;
866     IF (c1%NOTFOUND) THEN
867       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
868       igs_ge_msg_stack.ADD;
869       CLOSE c1;
870       app_exception.raise_exception;
871       RETURN;
872     END IF;
873     CLOSE c1;
874     IF ((tlinfo.uoo_id = x_uoo_id)
875         AND (tlinfo.ass_id = x_ass_id)
876         AND (tlinfo.sequence_number = x_sequence_number)
877         AND (tlinfo.ci_start_dt = x_ci_start_dt)
878         AND (tlinfo.ci_end_dt = x_ci_end_dt)
879         AND ((tlinfo.due_dt = x_due_dt)
880              OR ((tlinfo.due_dt IS NULL)
881                  AND (x_due_dt IS NULL)
882                 )
883             )
884         AND ((RTRIM(tlinfo.REFERENCE) = RTRIM(x_reference))
885              OR ((tlinfo.REFERENCE IS NULL)
886                  AND (x_reference IS NULL)
887                 )
888             )
889         AND (tlinfo.dflt_item_ind = x_dflt_item_ind)
890         AND ((tlinfo.logical_delete_dt = x_logical_delete_dt)
891              OR ((tlinfo.logical_delete_dt IS NULL)
892                  AND (x_logical_delete_dt IS NULL)
893                 )
894             )
895         AND ((tlinfo.action_dt = x_action_dt)
896              OR ((tlinfo.action_dt IS NULL)
897                  AND (x_action_dt IS NULL)
898                 )
899             )
900         AND ((tlinfo.exam_cal_type = x_exam_cal_type)
901              OR ((tlinfo.exam_cal_type IS NULL)
902                  AND (x_exam_cal_type IS NULL)
903                 )
904             )
905         AND ((tlinfo.exam_ci_sequence_number = x_exam_ci_sequence_number)
906              OR ((tlinfo.exam_ci_sequence_number IS NULL)
907                  AND (x_exam_ci_sequence_number IS NULL)
908                 )
909             )
910         AND ((tlinfo.grading_schema_cd = x_grading_schema_cd)
911              OR ((tlinfo.grading_schema_cd IS NULL)
912                  AND (x_grading_schema_cd IS NULL)
913                 )
914             )
915         AND ((tlinfo.gs_version_number = x_gs_version_number)
916              OR ((tlinfo.gs_version_number IS NULL)
917                  AND (x_gs_version_number IS NULL)
918                 )
919             )
920         AND ((tlinfo.description = x_description)
921              OR ((tlinfo.description IS NULL)
922                  AND (x_description IS NULL)
923                 )
924             )
925         AND ((tlinfo.release_date = x_release_date)
926              OR ((tlinfo.release_date IS NULL)
927                  AND (x_release_date IS NULL)
928                 )
929             )
930         AND ((tlinfo.us_ass_item_group_id = x_us_ass_item_group_id)
931              OR ((tlinfo.us_ass_item_group_id IS NULL)
932                  AND (x_us_ass_item_group_id IS NULL)
933                 )
934             )
935         AND ((tlinfo.midterm_mandatory_type_code = x_midterm_mandatory_type_code)
936              OR ((tlinfo.midterm_mandatory_type_code IS NULL)
937                  AND (x_midterm_mandatory_type_code IS NULL)
938                 )
939             )
940         AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty)
941              OR ((tlinfo.midterm_weight_qty IS NULL)
942                  AND (x_midterm_weight_qty IS NULL)
943                 )
944             )
945         AND ((tlinfo.final_mandatory_type_code = x_final_mandatory_type_code)
946              OR ((tlinfo.final_mandatory_type_code IS NULL)
947                  AND (x_final_mandatory_type_code IS NULL)
948                 )
949             )
950         AND ((tlinfo.final_weight_qty = x_final_weight_qty)
951              OR ((tlinfo.final_weight_qty IS NULL)
952                  AND (x_final_weight_qty IS NULL)
953                 )
954             )
955        ) THEN
956       NULL;
957     ELSE
958       fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
959       igs_ge_msg_stack.ADD;
960       app_exception.raise_exception;
961     END IF;
962     RETURN;
963   END lock_row;
964 
965   PROCEDURE update_row (
966     x_rowid                        IN     VARCHAR2,
967     x_unit_section_ass_item_id     IN     NUMBER,
968     x_uoo_id                       IN     NUMBER,
969     x_ass_id                       IN     NUMBER,
970     x_sequence_number              IN     NUMBER,
971     x_ci_start_dt                  IN     DATE,
972     x_ci_end_dt                    IN     DATE,
973     x_due_dt                       IN     DATE,
974     x_reference                    IN     VARCHAR2,
975     x_dflt_item_ind                IN     VARCHAR2,
976     x_logical_delete_dt            IN     DATE,
977     x_action_dt                    IN     DATE,
978     x_exam_cal_type                IN     VARCHAR2,
979     x_exam_ci_sequence_number      IN     NUMBER,
980     x_mode                         IN     VARCHAR2 DEFAULT 'R',
981     x_grading_schema_cd            IN     VARCHAR2,
982     x_gs_version_number            IN     NUMBER,
983     x_release_date                 IN     DATE,
984     x_description                  IN     VARCHAR2,
985     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
986     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
987     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
988     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
989     x_final_weight_qty             IN     NUMBER DEFAULT NULL
990   ) AS
991   /*************************************************************
992   Created By      : jdeekoll
993   Date Created By : 11-May-2000
994   Purpose :
995   Know limitations, enhancements or remarks
996   Change History
997   Who             When            What
998   (reverse chronological order - newest change first)
999   ***************************************************************/
1000     x_last_update_date       DATE;
1001     x_last_updated_by        NUMBER;
1002     x_last_update_login      NUMBER;
1003     x_request_id             NUMBER;
1004     x_program_id             NUMBER;
1005     x_program_application_id NUMBER;
1006     x_program_update_date    DATE;
1007   BEGIN
1008     x_last_update_date := SYSDATE;
1009     IF (x_mode = 'I') THEN
1010       x_last_updated_by := 1;
1011       x_last_update_login := 0;
1012     ELSIF (x_mode = 'R') THEN
1013       x_last_updated_by := fnd_global.user_id;
1014       IF x_last_updated_by IS NULL THEN
1015         x_last_updated_by := -1;
1016       END IF;
1017       x_last_update_login := fnd_global.login_id;
1018       IF x_last_update_login IS NULL THEN
1019         x_last_update_login := -1;
1020       END IF;
1021       x_request_id := fnd_global.conc_request_id;
1022       x_program_id := fnd_global.conc_program_id;
1023       x_program_application_id := fnd_global.prog_appl_id;
1024       IF (x_request_id = -1) THEN
1025         x_request_id := NULL;
1026         x_program_id := NULL;
1027         x_program_application_id := NULL;
1028         x_program_update_date := NULL;
1029       ELSE
1030         x_program_update_date := SYSDATE;
1031       END IF;
1032     ELSE
1033       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1034       igs_ge_msg_stack.ADD;
1035       app_exception.raise_exception;
1036     END IF;
1037     before_dml (
1038       p_action                       => 'UPDATE',
1039       x_rowid                        => x_rowid,
1040       x_unit_section_ass_item_id     => x_unit_section_ass_item_id,
1041       x_uoo_id                       => x_uoo_id,
1042       x_ass_id                       => x_ass_id,
1043       x_sequence_number              => x_sequence_number,
1044       x_ci_start_dt                  => x_ci_start_dt,
1045       x_ci_end_dt                    => x_ci_end_dt,
1046       x_due_dt                       => x_due_dt,
1047       x_reference                    => x_reference,
1048       x_dflt_item_ind                => x_dflt_item_ind,
1049       x_logical_delete_dt            => x_logical_delete_dt,
1050       x_action_dt                    => x_action_dt,
1051       x_exam_cal_type                => x_exam_cal_type,
1052       x_exam_ci_sequence_number      => x_exam_ci_sequence_number,
1053       x_creation_date                => x_last_update_date,
1054       x_created_by                   => x_last_updated_by,
1055       x_last_update_date             => x_last_update_date,
1056       x_last_updated_by              => x_last_updated_by,
1057       x_last_update_login            => x_last_update_login,
1058       x_grading_schema_cd            => x_grading_schema_cd,
1059       x_gs_version_number            => x_gs_version_number,
1060       x_release_date                 => x_release_date,
1061       x_us_ass_item_group_id         => x_us_ass_item_group_id,
1062       x_midterm_mandatory_type_code  => x_midterm_mandatory_type_code,
1063       x_midterm_weight_qty           => x_midterm_weight_qty,
1064       x_final_mandatory_type_code    => x_final_mandatory_type_code,
1065       x_final_weight_qty             => x_final_weight_qty
1066     );
1067     UPDATE igs_ps_unitass_item
1068        SET uoo_id = new_references.uoo_id,
1069            ass_id = new_references.ass_id,
1070            sequence_number = new_references.sequence_number,
1071            ci_start_dt = new_references.ci_start_dt,
1072            ci_end_dt = new_references.ci_end_dt,
1073            due_dt = new_references.due_dt,
1074            REFERENCE = new_references.REFERENCE,
1075            dflt_item_ind = new_references.dflt_item_ind,
1076            logical_delete_dt = new_references.logical_delete_dt,
1077            action_dt = new_references.action_dt,
1078            exam_cal_type = new_references.exam_cal_type,
1079            exam_ci_sequence_number = new_references.exam_ci_sequence_number,
1080            grading_schema_cd = new_references.grading_schema_cd,
1081            gs_version_number = new_references.gs_version_number,
1082            release_date = new_references.release_date,
1083            last_update_date = x_last_update_date,
1084            last_updated_by = x_last_updated_by,
1085            last_update_login = x_last_update_login,
1086            request_id = x_request_id,
1087            program_id = x_program_id,
1088            program_application_id = x_program_application_id,
1089            program_update_date = x_program_update_date,
1090            description = x_description,
1091            us_ass_item_group_id = x_us_ass_item_group_id,
1092            midterm_mandatory_type_code = x_midterm_mandatory_type_code,
1093            midterm_weight_qty = x_midterm_weight_qty,
1094            final_mandatory_type_code = x_final_mandatory_type_code,
1095            final_weight_qty = x_final_weight_qty
1096      WHERE ROWID = x_rowid;
1097     IF (SQL%NOTFOUND) THEN
1098       RAISE NO_DATA_FOUND;
1099     END IF;
1100     after_dml (p_action => 'UPDATE', x_rowid => x_rowid);
1101   END update_row;
1102 
1103   PROCEDURE add_row (
1104     x_rowid                        IN OUT NOCOPY VARCHAR2,
1105     x_unit_section_ass_item_id     IN OUT NOCOPY NUMBER,
1106     x_uoo_id                       IN     NUMBER,
1107     x_ass_id                       IN     NUMBER,
1108     x_sequence_number              IN OUT NOCOPY NUMBER,
1109     x_ci_start_dt                  IN OUT NOCOPY DATE,
1110     x_ci_end_dt                    IN OUT NOCOPY DATE,
1111     x_due_dt                       IN     DATE,
1112     x_reference                    IN     VARCHAR2,
1113     x_dflt_item_ind                IN     VARCHAR2,
1114     x_logical_delete_dt            IN     DATE,
1115     x_action_dt                    IN     DATE,
1116     x_exam_cal_type                IN     VARCHAR2,
1117     x_exam_ci_sequence_number      IN     NUMBER,
1118     x_mode                         IN     VARCHAR2 DEFAULT 'R',
1119     x_grading_schema_cd            IN     VARCHAR2,
1120     x_gs_version_number            IN     NUMBER,
1121     x_release_date                 IN     DATE,
1122     x_description                  IN     VARCHAR2,
1123     x_us_ass_item_group_id         IN     VARCHAR2 DEFAULT NULL,
1124     x_midterm_mandatory_type_code  IN     VARCHAR2 DEFAULT NULL,
1125     x_midterm_weight_qty           IN     NUMBER DEFAULT NULL,
1126     x_final_mandatory_type_code    IN     VARCHAR2 DEFAULT NULL,
1127     x_final_weight_qty             IN     NUMBER DEFAULT NULL
1128   ) AS
1129   /*************************************************************
1130   Created By      : jdeekoll
1131   Date Created By : 11-May-2000
1132   Purpose :
1133   Know limitations, enhancements or remarks
1134   Change History
1135   Who             When            What
1136   (reverse chronological order - newest change first)
1137   ***************************************************************/
1138     CURSOR c1 IS
1139       SELECT ROWID
1140       FROM   igs_ps_unitass_item
1141       WHERE  unit_section_ass_item_id = x_unit_section_ass_item_id;
1142   BEGIN
1143     OPEN c1;
1144     FETCH c1 INTO x_rowid;
1145     IF (c1%NOTFOUND) THEN
1146       CLOSE c1;
1147       insert_row (
1148         x_rowid,
1149         x_unit_section_ass_item_id,
1150         x_uoo_id,
1151         x_ass_id,
1152         x_sequence_number,
1153         x_ci_start_dt,
1154         x_ci_end_dt,
1155         x_due_dt,
1156         x_reference,
1157         x_dflt_item_ind,
1158         x_logical_delete_dt,
1159         x_action_dt,
1160         x_exam_cal_type,
1161         x_exam_ci_sequence_number,
1162         x_mode,
1163         x_grading_schema_cd,
1164         x_gs_version_number,
1165         x_release_date,
1166         x_description,
1167         x_us_ass_item_group_id,
1168         x_midterm_mandatory_type_code,
1169         x_midterm_weight_qty,
1170         x_final_mandatory_type_code,
1171         x_final_weight_qty
1172       );
1173       RETURN;
1174     END IF;
1175     CLOSE c1;
1176     update_row (
1177       x_rowid,
1178       x_unit_section_ass_item_id,
1179       x_uoo_id,
1180       x_ass_id,
1181       x_sequence_number,
1182       x_ci_start_dt,
1183       x_ci_end_dt,
1184       x_due_dt,
1185       x_reference,
1186       x_dflt_item_ind,
1187       x_logical_delete_dt,
1188       x_action_dt,
1189       x_exam_cal_type,
1190       x_exam_ci_sequence_number,
1191       x_mode,
1192       x_grading_schema_cd,
1193       x_gs_version_number,
1194       x_release_date,
1195       x_description,
1196       x_us_ass_item_group_id,
1197       x_midterm_mandatory_type_code,
1198       x_midterm_weight_qty,
1199       x_final_mandatory_type_code,
1200       x_final_weight_qty
1201     );
1202   END add_row;
1203 
1204   PROCEDURE delete_row (x_rowid IN VARCHAR2) AS
1205   /*************************************************************
1206   Created By      : jdeekoll
1207   Date Created By : 11-May-2000
1208   Purpose :
1209   Know limitations, enhancements or remarks
1210   Change History
1211   Who             When            What
1212   (reverse chronological order - newest change first)
1213   ***************************************************************/
1214   BEGIN
1215     before_dml (p_action => 'DELETE', x_rowid => x_rowid);
1216     DELETE FROM igs_ps_unitass_item
1217           WHERE ROWID = x_rowid;
1218     IF (SQL%NOTFOUND) THEN
1219       RAISE NO_DATA_FOUND;
1220     END IF;
1221     after_dml (p_action => 'DELETE', x_rowid => x_rowid);
1222   END delete_row;
1223 END igs_ps_unitass_item_pkg;