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;
[Home] [Help]