[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_UNITASS_ITEM_PKG
Source
1 PACKAGE BODY igs_as_unitass_item_pkg AS
2 /* $Header: IGSDI31B.pls 120.1 2006/09/20 07:29:23 sepalani noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to IGS_AS_VAL_UAI.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2 (25);
10 old_references igs_as_unitass_item_all%ROWTYPE;
11 new_references igs_as_unitass_item_all%ROWTYPE;
12 PROCEDURE set_column_values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 DEFAULT NULL,
15 x_unit_ass_item_id IN NUMBER DEFAULT NULL,
16 x_unit_cd IN VARCHAR2 DEFAULT NULL,
17 x_version_number IN NUMBER DEFAULT NULL,
18 x_cal_type IN VARCHAR2 DEFAULT NULL,
19 x_ci_sequence_number IN NUMBER DEFAULT NULL,
20 x_ass_id IN NUMBER DEFAULT NULL,
21 x_sequence_number IN NUMBER DEFAULT NULL,
22 x_ci_start_dt IN DATE DEFAULT NULL,
23 x_ci_end_dt IN DATE DEFAULT NULL,
24 x_unit_class IN VARCHAR2 DEFAULT NULL,
25 x_unit_mode IN VARCHAR2 DEFAULT NULL,
26 x_location_cd IN VARCHAR2 DEFAULT NULL,
27 x_due_dt IN DATE DEFAULT NULL,
28 x_reference IN VARCHAR2 DEFAULT NULL,
29 x_dflt_item_ind IN VARCHAR2 DEFAULT NULL,
30 x_logical_delete_dt IN DATE DEFAULT NULL,
31 x_action_dt IN DATE DEFAULT NULL,
32 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
33 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
34 x_creation_date IN DATE DEFAULT NULL,
35 x_created_by IN NUMBER DEFAULT NULL,
36 x_last_update_date IN DATE DEFAULT NULL,
37 x_last_updated_by IN NUMBER DEFAULT NULL,
38 x_last_update_login IN NUMBER DEFAULT NULL,
39 x_org_id IN NUMBER DEFAULT NULL,
40 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
41 x_gs_version_number IN NUMBER DEFAULT NULL,
42 x_release_date IN DATE DEFAULT NULL,
43 x_description IN VARCHAR2 DEFAULT NULL,
44 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
45 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
46 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
47 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
48 x_final_weight_qty IN NUMBER DEFAULT NULL
49 ) AS
50 CURSOR cur_old_ref_values IS
51 SELECT *
52 FROM igs_as_unitass_item_all
53 WHERE ROWID = x_rowid;
54 BEGIN
55 l_rowid := x_rowid;
56 -- Code for setting the Old and New Reference Values.
57 -- Populate Old Values.
58 OPEN cur_old_ref_values;
59 FETCH cur_old_ref_values INTO old_references;
60 IF (cur_old_ref_values%NOTFOUND)
61 AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
62 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
63 igs_ge_msg_stack.ADD;
64 CLOSE cur_old_ref_values;
65 app_exception.raise_exception;
66 RETURN;
67 END IF;
68 CLOSE cur_old_ref_values;
69 -- Populate New Values.
70 new_references.unit_cd := x_unit_cd;
71 new_references.unit_ass_item_id := x_unit_ass_item_id;
72 new_references.version_number := x_version_number;
73 new_references.cal_type := x_cal_type;
74 new_references.ci_sequence_number := x_ci_sequence_number;
75 new_references.ass_id := x_ass_id;
76 new_references.sequence_number := x_sequence_number;
77 new_references.ci_start_dt := x_ci_start_dt;
78 new_references.ci_end_dt := x_ci_end_dt;
79 new_references.unit_class := x_unit_class;
80 new_references.unit_mode := x_unit_mode;
81 new_references.location_cd := x_location_cd;
82 new_references.due_dt := x_due_dt;
83 new_references.REFERENCE := x_reference;
84 new_references.dflt_item_ind := x_dflt_item_ind;
85 new_references.logical_delete_dt := x_logical_delete_dt;
86 new_references.action_dt := x_action_dt;
87 new_references.exam_cal_type := x_exam_cal_type;
88 new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
89 new_references.org_id := x_org_id;
90 new_references.grading_schema_cd := x_grading_schema_cd;
91 new_references.gs_version_number := x_gs_version_number;
92 new_references.release_date := x_release_date;
93 new_references.description := x_description;
94 new_references.unit_ass_item_group_id := x_unit_ass_item_group_id;
95 new_references.midterm_mandatory_type_code := x_midterm_mandatory_type_code;
96 new_references.midterm_weight_qty := x_midterm_weight_qty;
97 new_references.final_mandatory_type_code := x_final_mandatory_type_code;
98 new_references.final_weight_qty := x_final_weight_qty;
99 IF (p_action = 'UPDATE') THEN
100 new_references.creation_date := old_references.creation_date;
101 new_references.created_by := old_references.created_by;
102 ELSE
103 new_references.creation_date := x_creation_date;
104 new_references.created_by := x_created_by;
105 END IF;
106 new_references.last_update_date := x_last_update_date;
107 new_references.last_updated_by := x_last_updated_by;
108 new_references.last_update_login := x_last_update_login;
109 END set_column_values;
110 -- Trigger description :-
111 -- "OSS_TST".trg_uai_br_iu
112 -- BEFORE INSERT OR UPDATE
113 -- ON IGS_AS_UNITASS_ITEM
114 -- FOR EACH ROW
115 PROCEDURE beforerowinsertupdate1 (
116 p_inserting IN BOOLEAN DEFAULT FALSE,
117 p_updating IN BOOLEAN DEFAULT FALSE,
118 p_deleting IN BOOLEAN DEFAULT FALSE
119 ) AS
120 v_message_name VARCHAR2 (30);
121 BEGIN
122 -- Validate that inserts/updates are allowed
123 IF p_inserting
124 OR p_updating THEN
125 --<Start uai1>
126 -- Validate assessment item exists
127 IF igs_as_val_uai.assp_val_ai_exists (
128 new_references.ass_id,
129 v_message_name
130 ) = FALSE THEN
131 fnd_message.set_name ('IGS', v_message_name);
132 igs_ge_msg_stack.ADD;
133 app_exception.raise_exception;
134 END IF;
135 --<<End uai1>>
136 --<Start uai10>
137 -- If the IGS_PS_UNIT version status is inactive then prevent inserts, updates and
138 -- deletes. As deletes are logical, they are equiv to updates and delete
139 -- trigger is not required.
140 IF igs_ps_val_unit.crsp_val_iud_uv_dtl (
141 new_references.unit_cd,
142 new_references.version_number,
143 v_message_name
144 ) = FALSE THEN
145 fnd_message.set_name ('IGS', v_message_name);
146 igs_ge_msg_stack.ADD;
147 app_exception.raise_exception;
148 END IF;
149 --<<End uai10>>
150 --<Start uai11>
151 -- If calendar instance is inactive, then prevent inserts, updates and
152 -- deletes. As deletes are logical, they are equiv to updates and delete
153 -- trigger is not required.
154 IF igs_as_val_uai.crsp_val_crs_ci (
155 new_references.cal_type,
156 new_references.ci_sequence_number,
157 v_message_name
158 ) = FALSE THEN
159 fnd_message.set_name ('IGS', v_message_name);
160 igs_ge_msg_stack.ADD;
161 app_exception.raise_exception;
162 END IF;
163 --<<End uai11>>
164 --<Start uai15>
165 -- If item is examinable then validate that reference is set.
166 -- w.r.t Bug # 1956374 procedure assp_val_ai_exmnbl reference is changed
167 IF NVL (new_references.REFERENCE, 'NULL666') = 'NULL666'
168 AND (igs_as_val_aiem.assp_val_ai_exmnbl (
169 new_references.ass_id,
170 v_message_name
171 )
172 OR igs_as_gen_002.assp_get_ai_s_type (new_references.ass_id) =
173 'ASSIGNMENT'
174 ) THEN
175 fnd_message.set_name ('IGS', 'IGS_AS_REF_ASSITEM_EXAM');
176 igs_ge_msg_stack.ADD;
177 app_exception.raise_exception;
178 END IF;
179 --<<End uai15>>
180 --<Start uai16>
181 -- Validate the examination calendar type/sequence number if they have
182 -- been specified.
183 IF new_references.exam_cal_type IS NOT NULL THEN
184 IF igs_as_val_uai.assp_val_uai_cal (
185 new_references.exam_cal_type,
186 new_references.exam_ci_sequence_number,
187 new_references.cal_type,
188 new_references.ci_sequence_number,
189 v_message_name
190 ) = 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 IF;
196 --<<End uai16>>
197 IF p_inserting THEN
198 --<Start uai12>
199 -- If calendar type is closed, then prevent inserts.
200 IF igs_as_val_uai.crsp_val_uo_cal_type (
201 new_references.cal_type,
202 v_message_name
203 ) = FALSE THEN
204 fnd_message.set_name ('IGS', v_message_name);
205 igs_ge_msg_stack.ADD;
206 app_exception.raise_exception;
207 END IF;
208 --<<End uai12>>
209 END IF;
210 END IF;
211 END beforerowinsertupdate1;
212 -- Trigger description :-
213 -- "OSS_TST".trg_uai_br_iu_upd
214 -- BEFORE INSERT OR UPDATE
215 -- ON IGS_AS_UNITASS_ITEM
216 -- FOR EACH ROW
217 PROCEDURE beforerowinsertupdate2 (
218 p_inserting IN BOOLEAN DEFAULT FALSE,
219 p_updating IN BOOLEAN DEFAULT FALSE,
220 p_deleting IN BOOLEAN DEFAULT FALSE
221 ) AS
222 CURSOR c_ci (
223 cp_cal_type igs_ca_inst.cal_type%TYPE,
224 cp_seq_number igs_ca_inst.sequence_number%TYPE
225 ) IS
226 SELECT start_dt,
227 end_dt
228 FROM igs_ca_inst
229 WHERE cal_type = cp_cal_type
230 AND sequence_number = cp_seq_number;
231 BEGIN
232 IF p_inserting THEN
233 -- Temporary code to set the start/end date - to be replaced
234 -- with a database routine rather than an embedded cursor
235 -- Start and end date are carried down from UOP for sorting
236 -- purposes.
237 FOR v_ci_rec IN c_ci (
238 new_references.cal_type,
239 new_references.ci_sequence_number
240 ) LOOP
241 new_references.ci_start_dt := v_ci_rec.start_dt;
242 new_references.ci_end_dt := v_ci_rec.end_dt;
243 END LOOP;
244 END IF;
245 IF p_inserting
246 OR p_updating THEN
247 -- Always update the action date when p_inserting/p_updating/p_deleting
248 -- a UAI. This enable mechanism for knowing when an item needs to
249 -- be added to a student.
250 -- Updates only pertain to changes for locn, um and ucl.
251 -- Please IGS_GE_NOTE that p_deleting a UAI is only a logical delete and
252 -- therefore really an update.
253 IF (NVL (new_references.dflt_item_ind, 'x') <>
254 NVL (old_references.dflt_item_ind, 'x')
255 OR NVL (
256 new_references.logical_delete_dt,
257 igs_ge_date.igsdate ('1900/01/01')
258 ) <> NVL (
259 old_references.logical_delete_dt,
260 igs_ge_date.igsdate ('1900/01/01')
261 )
262 ) THEN
263 IF NVL (old_references.action_dt, igs_ge_date.igsdate ('1900/01/01')) =
264 igs_ge_date.igsdate ('1900/01/01') THEN
265 new_references.action_dt := SYSDATE;
266 END IF;
267 END IF;
268 END IF;
269 END beforerowinsertupdate2;
270 -- Trigger description :-
271 -- "OSS_TST".trg_uai_ar_iu
272 -- AFTER INSERT OR UPDATE
273 -- ON IGS_AS_UNITASS_ITEM
274 -- FOR EACH ROW
275 PROCEDURE afterrowinsertupdate3 (
276 p_inserting IN BOOLEAN DEFAULT FALSE,
277 p_updating IN BOOLEAN DEFAULT FALSE,
278 p_deleting IN BOOLEAN DEFAULT FALSE
279 ) AS
280 v_message_name VARCHAR2 (30);
281 BEGIN
282 IF p_inserting
283 OR p_updating THEN
284 -- w.r.t Bug # 1956374 procedure assp_val_ai_exmnbl reference is changed
285 IF igs_as_val_aiem.assp_val_ai_exmnbl (
286 new_references.ass_id,
287 v_message_name
288 ) = TRUE THEN
289 --<uai13>
290 -- Validate that the reference number id unique within a UOP
291 IF igs_as_val_uai.assp_val_uai_uniqref (
292 new_references.unit_cd,
293 new_references.version_number,
294 new_references.cal_type,
295 new_references.ci_sequence_number,
296 new_references.sequence_number,
297 new_references.REFERENCE,
298 new_references.ass_id,
299 v_message_name
300 ) = FALSE THEN
301 fnd_message.set_name ('IGS', v_message_name);
302 igs_ge_msg_stack.ADD;
303 app_exception.raise_exception;
304 END IF;
305 ELSE
306 --<uai14>
307 -- if record has not been deleted
308 IF NVL (
309 new_references.logical_delete_dt,
310 igs_ge_date.igsdate ('1900/01/01')
311 ) = igs_ge_date.igsdate ('1900/01/01') THEN
312 -- Validate that the reference number id unique within a UOP
313 IF igs_as_val_uai.assp_val_uai_opt_ref (
314 new_references.unit_cd,
315 new_references.version_number,
316 new_references.cal_type,
317 new_references.ci_sequence_number,
318 new_references.sequence_number,
319 new_references.REFERENCE,
320 new_references.ass_id,
321 igs_as_gen_001.assp_get_ai_a_type (new_references.ass_id),
322 v_message_name
323 ) = FALSE THEN
324 fnd_message.set_name ('IGS', v_message_name);
325 igs_ge_msg_stack.ADD;
326 app_exception.raise_exception;
327 END IF;
328 END IF;
329 END IF;
330 -- Save the rowid of the current row.
331 END IF;
332 END afterrowinsertupdate3;
333
334 PROCEDURE check_parent_existance AS
335 BEGIN
336 IF (((old_references.ass_id = new_references.ass_id))
337 OR ((new_references.ass_id IS NULL))
338 ) THEN
339 NULL;
340 ELSE
341 IF NOT (igs_as_assessmnt_itm_pkg.get_pk_for_validation (
342 new_references.ass_id
343 )
344 ) THEN
345 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
346 igs_ge_msg_stack.ADD;
347 app_exception.raise_exception;
348 END IF;
349 END IF;
350 IF (((old_references.exam_cal_type = new_references.exam_cal_type))
351 OR ((new_references.exam_cal_type IS NULL))
352 ) THEN
353 NULL;
354 ELSE
355 IF NOT (igs_ca_type_pkg.get_pk_for_validation (
356 new_references.exam_cal_type
357 )
358 ) THEN
359 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
360 igs_ge_msg_stack.ADD;
361 app_exception.raise_exception;
362 END IF;
363 END IF;
364 IF (((old_references.exam_cal_type = new_references.exam_cal_type)
365 AND (old_references.exam_ci_sequence_number =
366 new_references.exam_ci_sequence_number
367 )
368 )
369 OR ((new_references.exam_cal_type IS NULL)
370 OR (new_references.exam_ci_sequence_number IS NULL)
371 )
372 ) THEN
373 NULL;
374 ELSE
375 IF NOT (igs_ca_inst_pkg.get_pk_for_validation (
376 new_references.exam_cal_type,
377 new_references.exam_ci_sequence_number
378 )
379 ) THEN
380 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
381 igs_ge_msg_stack.ADD;
382 app_exception.raise_exception;
383 END IF;
384 END IF;
385 IF (((old_references.unit_cd = new_references.unit_cd)
386 OR (old_references.version_number = new_references.version_number)
387 OR (old_references.cal_type = new_references.cal_type)
388 OR (old_references.ci_sequence_number =
389 new_references.ci_sequence_number
390 )
391 )
392 OR ((new_references.unit_cd IS NULL)
393 OR (new_references.version_number IS NULL)
394 OR (new_references.cal_type IS NULL)
395 OR (new_references.ci_sequence_number IS NULL)
396 )
397 ) THEN
398 NULL;
399 ELSE
400 IF NOT (igs_as_unit_ai_grp_pkg.get_pk_for_validation (
401 new_references.unit_ass_item_group_id
402 )
403 ) THEN
404 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
405 igs_ge_msg_stack.ADD;
406 app_exception.raise_exception;
407 END IF;
408 END IF;
409 END check_parent_existance;
410
411 PROCEDURE check_constraints (
412 column_name IN VARCHAR2 DEFAULT NULL,
413 column_value IN VARCHAR2 DEFAULT NULL
414 ) AS
415 BEGIN
416 IF column_name IS NULL THEN
417 NULL;
418 ELSIF UPPER (column_name) = 'CI_SEQUENCE_NUMBER' THEN
419 new_references.ci_sequence_number := igs_ge_number.to_num (column_value);
420 ELSIF UPPER (column_name) = 'SEQUENCE_NUMBER' THEN
421 new_references.sequence_number := igs_ge_number.to_num (column_value);
422 ELSIF UPPER (column_name) = 'EXAM_CI_SEQUENCE_NUMBER' THEN
423 new_references.exam_ci_sequence_number :=
424 igs_ge_number.to_num (column_value);
425 ELSIF UPPER (column_name) = 'DFLT_ITEM_IND' THEN
426 new_references.dflt_item_ind := column_value;
427 ELSIF UPPER (column_name) = 'CAL_TYPE' THEN
428 new_references.cal_type := column_value;
429 ELSIF UPPER (column_name) = 'DFLT_ITEM_IND' THEN
430 new_references.dflt_item_ind := column_value;
431 ELSIF UPPER (column_name) = 'EXAM_CAL_TYPE' THEN
432 new_references.exam_cal_type := column_value;
433 ELSIF UPPER (column_name) = 'REFERENCE' THEN
434 new_references.REFERENCE := column_value;
435 ELSIF UPPER (column_name) = 'UNIT_CD' THEN
436 new_references.unit_cd := column_value;
437 END IF;
438 IF UPPER (column_name) = 'CI_SEQUENCE_NUMBER'
439 OR column_name IS NULL THEN
440 IF new_references.ci_sequence_number < 1
441 AND new_references.ci_sequence_number > 999999 THEN
442 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
443 igs_ge_msg_stack.ADD;
444 app_exception.raise_exception;
445 END IF;
446 END IF;
447 IF UPPER (column_name) = 'SEQUENCE_NUMBER'
448 OR column_name IS NULL THEN
449 IF new_references.sequence_number < 1
450 AND new_references.sequence_number > 999999 THEN
451 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
452 igs_ge_msg_stack.ADD;
453 app_exception.raise_exception;
454 END IF;
455 END IF;
456 IF UPPER (column_name) = 'EXAM_CI_SEQUENCE_NUMBER'
457 OR column_name IS NULL THEN
458 IF new_references.exam_ci_sequence_number < 1
459 AND new_references.exam_ci_sequence_number > 999999 THEN
460 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
461 igs_ge_msg_stack.ADD;
462 app_exception.raise_exception;
463 END IF;
464 END IF;
465 IF UPPER (column_name) = 'DFLT_ITEM_IND'
466 OR column_name IS NULL THEN
467 IF new_references.dflt_item_ind <> UPPER (new_references.dflt_item_ind)
468 OR new_references.dflt_item_ind NOT IN ('Y', 'N') THEN
469 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
470 igs_ge_msg_stack.ADD;
471 app_exception.raise_exception;
472 END IF;
473 END IF;
474 IF UPPER (column_name) = 'CAL_TYPE'
475 OR column_name IS NULL THEN
476 IF new_references.cal_type <> UPPER (new_references.cal_type) THEN
477 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
478 igs_ge_msg_stack.ADD;
479 app_exception.raise_exception;
480 END IF;
481 END IF;
482 IF UPPER (column_name) = 'DFLT_ITEM_IND'
483 OR column_name IS NULL THEN
484 IF new_references.dflt_item_ind <> UPPER (new_references.dflt_item_ind) THEN
485 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
486 igs_ge_msg_stack.ADD;
487 app_exception.raise_exception;
488 END IF;
489 END IF;
490 IF UPPER (column_name) = 'EXAM_CAL_TYPE'
491 OR column_name IS NULL THEN
492 IF new_references.exam_cal_type <> UPPER (new_references.exam_cal_type) THEN
493 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
494 igs_ge_msg_stack.ADD;
495 app_exception.raise_exception;
496 END IF;
497 END IF;
498 IF UPPER (column_name) = 'REFERENCE'
499 OR column_name IS NULL THEN
500 IF new_references.REFERENCE <> UPPER (new_references.REFERENCE) THEN
501 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
502 igs_ge_msg_stack.ADD;
503 app_exception.raise_exception;
504 END IF;
505 END IF;
506 IF UPPER (column_name) = 'UNIT_CD'
507 OR column_name IS NULL THEN
508 IF new_references.unit_cd <> UPPER (new_references.unit_cd) THEN
509 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
510 igs_ge_msg_stack.ADD;
511 app_exception.raise_exception;
512 END IF;
513 END IF;
514 END check_constraints;
515
516 FUNCTION get_pk_for_validation (x_unit_ass_item_id IN NUMBER)
517 RETURN BOOLEAN AS
518 CURSOR cur_rowid IS
519 SELECT ROWID
520 FROM igs_as_unitass_item_all
521 WHERE unit_ass_item_id = x_unit_ass_item_id
522 FOR UPDATE NOWAIT;
523 l_rowid cur_rowid%ROWTYPE;
524 BEGIN
525 OPEN cur_rowid;
526 FETCH cur_rowid INTO l_rowid;
527 IF (cur_rowid%FOUND) THEN
528 CLOSE cur_rowid;
529 RETURN (TRUE);
530 ELSE
531 CLOSE cur_rowid;
532 RETURN (FALSE);
533 END IF;
534 END get_pk_for_validation;
535
536 FUNCTION get_uk_for_validation (
537 x_unit_cd IN VARCHAR2,
538 x_version_number IN NUMBER,
539 x_cal_type IN VARCHAR2,
540 x_ci_sequence_number IN NUMBER,
541 x_ass_id IN NUMBER,
542 x_sequence_number IN NUMBER
543 )
544 RETURN BOOLEAN AS
545 CURSOR cur_rowid IS
546 SELECT ROWID
547 FROM igs_as_unitass_item_all
548 WHERE unit_cd = x_unit_cd
549 AND version_number = x_version_number
550 AND cal_type = x_cal_type
551 AND ci_sequence_number = x_ci_sequence_number
552 AND ass_id = x_ass_id
553 AND sequence_number = x_sequence_number
554 AND ((l_rowid IS NULL)
555 OR (ROWID <> l_rowid)
556 );
557 lv_rowid cur_rowid%ROWTYPE;
558 BEGIN
559 OPEN cur_rowid;
560 FETCH cur_rowid INTO lv_rowid;
561 IF (cur_rowid%FOUND) THEN
562 CLOSE cur_rowid;
563 RETURN (TRUE);
564 ELSE
565 CLOSE cur_rowid;
566 RETURN (FALSE);
567 END IF;
568 END get_uk_for_validation;
569
570 PROCEDURE check_uniqueness AS
571 BEGIN
572 IF get_uk_for_validation (
573 x_unit_cd => new_references.unit_cd,
574 x_version_number => new_references.version_number,
575 x_cal_type => new_references.cal_type,
576 x_ci_sequence_number => new_references.ci_sequence_number,
577 x_ass_id => new_references.ass_id,
578 x_sequence_number => new_references.sequence_number
579 ) THEN
580 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
581 igs_ge_msg_stack.ADD;
582 app_exception.raise_exception;
583 END IF;
584 END check_uniqueness;
585
586 PROCEDURE get_fk_igs_as_assessmnt_itm (x_ass_id IN NUMBER) AS
587 CURSOR cur_rowid IS
588 SELECT ROWID
589 FROM igs_as_unitass_item_all
590 WHERE ass_id = x_ass_id;
591 lv_rowid cur_rowid%ROWTYPE;
592 BEGIN
593 OPEN cur_rowid;
594 FETCH cur_rowid INTO lv_rowid;
595 IF (cur_rowid%FOUND) THEN
596 fnd_message.set_name ('IGS', 'IGS_AS_UAI_AI_FK');
597 igs_ge_msg_stack.ADD;
598 CLOSE cur_rowid;
599 app_exception.raise_exception;
600 RETURN;
601 END IF;
602 CLOSE cur_rowid;
603 END get_fk_igs_as_assessmnt_itm;
604
605 PROCEDURE get_fk_igs_ca_type (x_cal_type IN VARCHAR2) AS
606 CURSOR cur_rowid IS
607 SELECT ROWID
608 FROM igs_as_unitass_item_all
609 WHERE exam_cal_type = x_cal_type;
610 lv_rowid cur_rowid%ROWTYPE;
611 BEGIN
612 OPEN cur_rowid;
613 FETCH cur_rowid INTO lv_rowid;
614 IF (cur_rowid%FOUND) THEN
615 fnd_message.set_name ('IGS', 'IGS_AS_UAI_CAT_FK');
616 igs_ge_msg_stack.ADD;
617 CLOSE cur_rowid;
618 app_exception.raise_exception;
619 RETURN;
620 END IF;
621 CLOSE cur_rowid;
622 END get_fk_igs_ca_type;
623
624 -- ADDED BY DDEY FOR BUG # 2162831
625 PROCEDURE get_fk_igs_as_grd_schema (
626 x_grading_schema_cd IN VARCHAR2,
627 x_version_number IN NUMBER
628 ) AS
629 CURSOR cur_rowid IS
630 SELECT ROWID
631 FROM igs_as_unitass_item_all
632 WHERE grading_schema_cd = x_grading_schema_cd
633 AND version_number = x_version_number;
634 lv_rowid cur_rowid%ROWTYPE;
635 BEGIN
636 OPEN cur_rowid;
637 FETCH cur_rowid INTO lv_rowid;
638 IF (cur_rowid%FOUND) THEN
639 fnd_message.set_name ('IGS', 'IGS_AS_UAI_GS_FK');
640 igs_ge_msg_stack.ADD;
641 CLOSE cur_rowid;
642 app_exception.raise_exception;
643 RETURN;
644 END IF;
645 CLOSE cur_rowid;
646 END get_fk_igs_as_grd_schema;
647
648 PROCEDURE get_fk_igs_ca_inst (
649 x_cal_type IN VARCHAR2,
650 x_sequence_number IN NUMBER
651 ) AS
652 CURSOR cur_rowid IS
653 SELECT ROWID
654 FROM igs_as_unitass_item_all
655 WHERE exam_cal_type = x_cal_type
656 AND exam_ci_sequence_number = x_sequence_number;
657 lv_rowid cur_rowid%ROWTYPE;
658 BEGIN
659 OPEN cur_rowid;
660 FETCH cur_rowid INTO lv_rowid;
661 IF (cur_rowid%FOUND) THEN
662 fnd_message.set_name ('IGS', 'IGS_AS_UAI_CI_FK');
663 igs_ge_msg_stack.ADD;
664 CLOSE cur_rowid;
665 app_exception.raise_exception;
666 RETURN;
667 END IF;
668 CLOSE cur_rowid;
669 END get_fk_igs_ca_inst;
670 --
671 -- Obsoleted as part of Grade Book build
672 --
673 PROCEDURE get_fk_igs_ad_location (x_location_cd IN VARCHAR2) AS
674 BEGIN
675 RETURN;
676 END get_fk_igs_ad_location;
677 --
678 -- Obsoleted as part of Grade Book build
679 --
680 PROCEDURE get_fk_igs_as_unit_class (x_unit_class IN VARCHAR2) AS
681 BEGIN
682 RETURN;
683 END get_fk_igs_as_unit_class;
684 --
685 -- Obsoleted as part of Grade Book build
686 --
687 PROCEDURE get_fk_igs_as_unit_mode (x_unit_mode IN VARCHAR2) AS
688 BEGIN
689 RETURN;
690 END get_fk_igs_as_unit_mode;
691
692 PROCEDURE get_fk_igs_ps_unit_ofr_pat (
693 x_unit_cd IN VARCHAR2,
694 x_version_number IN NUMBER,
695 x_cal_type IN VARCHAR2,
696 x_ci_sequence_number IN NUMBER
697 ) AS
698 CURSOR cur_rowid IS
699 SELECT ROWID
700 FROM igs_as_unitass_item_all
701 WHERE unit_cd = x_unit_cd
702 AND version_number = x_version_number
703 AND cal_type = x_cal_type
704 AND ci_sequence_number = x_ci_sequence_number;
705 lv_rowid cur_rowid%ROWTYPE;
706 BEGIN
707 OPEN cur_rowid;
708 FETCH cur_rowid INTO lv_rowid;
709 IF (cur_rowid%FOUND) THEN
710 fnd_message.set_name ('IGS', 'IGS_AS_UAI_UOP_FK');
711 igs_ge_msg_stack.ADD;
712 CLOSE cur_rowid;
713 app_exception.raise_exception;
714 RETURN;
715 END IF;
716 CLOSE cur_rowid;
717 END get_fk_igs_ps_unit_ofr_pat;
718
719 PROCEDURE get_fk_igs_as_unit_ai_grp (
720 x_unit_ass_item_group_id IN NUMBER
721 ) AS
722 CURSOR cur_rowid IS
723 SELECT ROWID
724 FROM igs_as_unitass_item_all
725 WHERE unit_ass_item_group_id = x_unit_ass_item_group_id;
726 lv_rowid cur_rowid%ROWTYPE;
727 BEGIN
728 OPEN cur_rowid;
729 FETCH cur_rowid INTO lv_rowid;
730 IF (cur_rowid%FOUND) THEN
731 fnd_message.set_name ('IGS', 'IGS_AS_UAI_UAIG_FK');
732 igs_ge_msg_stack.ADD;
733 CLOSE cur_rowid;
734 app_exception.raise_exception;
735 RETURN;
736 END IF;
737 CLOSE cur_rowid;
738 END get_fk_igs_as_unit_ai_grp;
739
740 PROCEDURE before_dml (
741 p_action IN VARCHAR2,
742 x_rowid IN VARCHAR2 DEFAULT NULL,
743 x_unit_ass_item_id IN NUMBER DEFAULT NULL,
744 x_unit_cd IN VARCHAR2 DEFAULT NULL,
745 x_version_number IN NUMBER DEFAULT NULL,
746 x_cal_type IN VARCHAR2 DEFAULT NULL,
747 x_ci_sequence_number IN NUMBER DEFAULT NULL,
748 x_ass_id IN NUMBER DEFAULT NULL,
749 x_sequence_number IN NUMBER DEFAULT NULL,
750 x_ci_start_dt IN DATE DEFAULT NULL,
751 x_ci_end_dt IN DATE DEFAULT NULL,
752 x_unit_class IN VARCHAR2 DEFAULT NULL,
753 x_unit_mode IN VARCHAR2 DEFAULT NULL,
754 x_location_cd IN VARCHAR2 DEFAULT NULL,
755 x_due_dt IN DATE DEFAULT NULL,
756 x_reference IN VARCHAR2 DEFAULT NULL,
757 x_dflt_item_ind IN VARCHAR2 DEFAULT NULL,
758 x_logical_delete_dt IN DATE DEFAULT NULL,
759 x_action_dt IN DATE DEFAULT NULL,
760 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
761 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
762 x_creation_date IN DATE DEFAULT NULL,
763 x_created_by IN NUMBER DEFAULT NULL,
764 x_last_update_date IN DATE DEFAULT NULL,
765 x_last_updated_by IN NUMBER DEFAULT NULL,
766 x_last_update_login IN NUMBER DEFAULT NULL,
767 x_org_id IN NUMBER DEFAULT NULL,
768 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
769 x_gs_version_number IN NUMBER DEFAULT NULL,
770 x_release_date IN DATE DEFAULT NULL,
771 x_description IN VARCHAR2 DEFAULT NULL,
772 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
773 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
774 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
775 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
776 x_final_weight_qty IN NUMBER DEFAULT NULL
777 ) AS
778 BEGIN
779 set_column_values (
780 p_action,
781 x_rowid,
782 x_unit_ass_item_id,
783 x_unit_cd,
784 x_version_number,
785 x_cal_type,
786 x_ci_sequence_number,
787 x_ass_id,
788 x_sequence_number,
789 x_ci_start_dt,
790 x_ci_end_dt,
791 x_unit_class,
792 x_unit_mode,
793 x_location_cd,
794 x_due_dt,
795 x_reference,
796 x_dflt_item_ind,
797 x_logical_delete_dt,
798 x_action_dt,
799 x_exam_cal_type,
800 x_exam_ci_sequence_number,
801 x_creation_date,
802 x_created_by,
803 x_last_update_date,
804 x_last_updated_by,
805 x_last_update_login,
806 x_org_id,
807 x_grading_schema_cd,
808 x_gs_version_number,
809 x_release_date,
810 x_description,
811 x_unit_ass_item_group_id,
812 x_midterm_mandatory_type_code,
813 x_midterm_weight_qty,
814 x_final_mandatory_type_code,
815 x_final_weight_qty
816 );
817 IF (p_action = 'INSERT') THEN
818 -- Call all the procedures related to Before Insert.
819 beforerowinsertupdate1 (p_inserting => TRUE);
820 beforerowinsertupdate2 (p_inserting => TRUE);
821 IF get_pk_for_validation (new_references.unit_ass_item_id) THEN
822 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
823 igs_ge_msg_stack.ADD;
824 app_exception.raise_exception;
825 END IF;
826 check_uniqueness;
827 check_constraints;
828 check_parent_existance;
829 ELSIF (p_action = 'UPDATE') THEN
830 -- Call all the procedures related to Before Update.
831 beforerowinsertupdate1 (p_updating => TRUE);
832 beforerowinsertupdate2 (p_updating => TRUE);
833 check_uniqueness;
834 check_constraints;
835 check_parent_existance;
836 ELSIF (p_action = 'DELETE') THEN
837 -- Call all the procedures related to Before Delete.
838 NULL;
839 ELSIF (p_action = 'VALIDATE_INSERT') THEN
840 IF get_pk_for_validation (new_references.unit_ass_item_id) THEN
841 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
842 igs_ge_msg_stack.ADD;
843 app_exception.raise_exception;
844 END IF;
845 check_constraints;
846 check_uniqueness;
847 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
848 check_constraints;
849 check_uniqueness;
850 ELSIF (p_action = 'VALIDATE_DELETE') THEN
851 NULL;
852 END IF;
853 END before_dml;
854
855 PROCEDURE after_dml (p_action IN VARCHAR2, x_rowid IN VARCHAR2) AS
856 BEGIN
857 l_rowid := x_rowid;
858 IF (p_action = 'INSERT') THEN
859 -- Call all the procedures related to After Insert.
860 afterrowinsertupdate3 (p_inserting => TRUE);
861 ELSIF (p_action = 'UPDATE') THEN
862 -- Call all the procedures related to After Update.
863 afterrowinsertupdate3 (p_updating => TRUE);
864 END IF;
865 END after_dml;
866
867 PROCEDURE insert_row (
868 x_rowid IN OUT NOCOPY VARCHAR2,
869 x_unit_ass_item_id IN OUT NOCOPY NUMBER,
870 x_unit_cd IN VARCHAR2,
871 x_version_number IN NUMBER,
872 x_cal_type IN VARCHAR2,
873 x_ci_sequence_number IN NUMBER,
874 x_ass_id IN NUMBER,
875 x_sequence_number IN NUMBER,
876 x_ci_start_dt IN DATE,
877 x_ci_end_dt IN DATE,
878 x_unit_class IN VARCHAR2,
879 x_unit_mode IN VARCHAR2,
880 x_location_cd IN VARCHAR2,
881 x_due_dt IN DATE,
882 x_reference IN VARCHAR2,
883 x_dflt_item_ind IN VARCHAR2,
884 x_logical_delete_dt IN DATE,
885 x_action_dt IN DATE,
886 x_exam_cal_type IN VARCHAR2,
887 x_exam_ci_sequence_number IN NUMBER,
888 x_mode IN VARCHAR2 DEFAULT 'R',
889 x_org_id IN NUMBER,
890 x_grading_schema_cd IN VARCHAR2,
891 x_gs_version_number IN NUMBER,
892 x_release_date IN DATE,
893 x_description IN VARCHAR2,
894 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
895 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
896 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
897 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
898 x_final_weight_qty IN NUMBER DEFAULT NULL
899 ) AS
900 CURSOR c IS
901 SELECT ROWID
902 FROM igs_as_unitass_item_all
903 WHERE unit_cd = x_unit_cd
904 AND version_number = x_version_number
905 AND cal_type = x_cal_type
906 AND ci_sequence_number = x_ci_sequence_number
907 AND ass_id = x_ass_id
908 AND sequence_number = x_sequence_number;
909 x_last_update_date DATE;
910 x_last_updated_by NUMBER;
911 x_last_update_login NUMBER;
912 x_request_id NUMBER;
913 x_program_id NUMBER;
914 x_program_application_id NUMBER;
915 x_program_update_date DATE;
916 BEGIN
917 x_last_update_date := SYSDATE;
918 IF (x_mode = 'I') THEN
919 x_last_updated_by := 1;
920 x_last_update_login := 0;
921 ELSIF (x_mode = 'R') THEN
922 x_last_updated_by := fnd_global.user_id;
923 IF x_last_updated_by IS NULL THEN
924 x_last_updated_by := -1;
925 END IF;
926 x_last_update_login := fnd_global.login_id;
927 IF x_last_update_login IS NULL THEN
928 x_last_update_login := -1;
929 END IF;
930 x_request_id := fnd_global.conc_request_id;
931 x_program_id := fnd_global.conc_program_id;
932 x_program_application_id := fnd_global.prog_appl_id;
933 IF (x_request_id = -1) THEN
934 x_request_id := NULL;
935 x_program_id := NULL;
936 x_program_application_id := NULL;
937 x_program_update_date := NULL;
938 ELSE
939 x_program_update_date := SYSDATE;
940 END IF;
941 ELSE
942 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
943 igs_ge_msg_stack.ADD;
944 app_exception.raise_exception;
945 END IF;
946 before_dml (
947 p_action => 'INSERT',
948 x_rowid => x_rowid,
949 x_action_dt => x_action_dt,
950 x_unit_ass_item_id => x_unit_ass_item_id,
951 x_ass_id => x_ass_id,
952 x_cal_type => x_cal_type,
953 x_ci_end_dt => x_ci_end_dt,
954 x_ci_sequence_number => x_ci_sequence_number,
955 x_ci_start_dt => x_ci_start_dt,
956 x_dflt_item_ind => NVL (x_dflt_item_ind, 'Y'),
957 x_due_dt => x_due_dt,
958 x_exam_cal_type => x_exam_cal_type,
959 x_exam_ci_sequence_number => x_exam_ci_sequence_number,
960 x_location_cd => x_location_cd,
961 x_logical_delete_dt => x_logical_delete_dt,
962 x_reference => x_reference,
963 x_sequence_number => x_sequence_number,
964 x_unit_cd => x_unit_cd,
965 x_unit_class => x_unit_class,
966 x_unit_mode => x_unit_mode,
967 x_version_number => x_version_number,
968 x_creation_date => x_last_update_date,
969 x_created_by => x_last_updated_by,
970 x_last_update_date => x_last_update_date,
971 x_last_updated_by => x_last_updated_by,
972 x_last_update_login => x_last_update_login,
973 x_org_id => igs_ge_gen_003.get_org_id,
974 x_grading_schema_cd => x_grading_schema_cd,
975 x_gs_version_number => x_gs_version_number,
976 x_release_date => x_release_date,
977 x_description => x_description,
978 x_unit_ass_item_group_id => x_unit_ass_item_group_id,
979 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
980 x_midterm_weight_qty => x_midterm_weight_qty,
981 x_final_mandatory_type_code => x_final_mandatory_type_code,
982 x_final_weight_qty => x_final_weight_qty
983 );
984 SELECT igs_as_unitass_item_s.NEXTVAL
985 INTO x_unit_ass_item_id
986 FROM DUAL;
987 INSERT INTO igs_as_unitass_item_all
988 (unit_ass_item_id, unit_cd,
989 version_number, cal_type,
990 ci_sequence_number, ass_id,
991 sequence_number, ci_start_dt,
992 ci_end_dt, unit_class,
993 unit_mode, location_cd,
994 due_dt, REFERENCE,
995 dflt_item_ind, logical_delete_dt,
996 action_dt, exam_cal_type,
997 exam_ci_sequence_number, org_id,
998 grading_schema_cd,
999 gs_version_number, release_date,
1000 creation_date, created_by, last_update_date,
1001 last_updated_by, last_update_login, request_id,
1002 program_id, program_application_id, program_update_date,
1003 description,
1004 unit_ass_item_group_id,
1005 midterm_mandatory_type_code,
1006 midterm_weight_qty,
1007 final_mandatory_type_code,
1008 final_weight_qty)
1009 VALUES (x_unit_ass_item_id, new_references.unit_cd,
1010 new_references.version_number, new_references.cal_type,
1011 new_references.ci_sequence_number, new_references.ass_id,
1012 new_references.sequence_number, new_references.ci_start_dt,
1013 new_references.ci_end_dt, new_references.unit_class,
1014 new_references.unit_mode, new_references.location_cd,
1015 new_references.due_dt, new_references.REFERENCE,
1016 new_references.dflt_item_ind, new_references.logical_delete_dt,
1017 new_references.action_dt, new_references.exam_cal_type,
1018 new_references.exam_ci_sequence_number, new_references.org_id,
1019 new_references.grading_schema_cd,
1020 new_references.gs_version_number, new_references.release_date,
1021 x_last_update_date, x_last_updated_by, x_last_update_date,
1022 x_last_updated_by, x_last_update_login, x_request_id,
1023 x_program_id, x_program_application_id, x_program_update_date,
1024 new_references.description,
1025 new_references.unit_ass_item_group_id,
1026 new_references.midterm_mandatory_type_code,
1027 new_references.midterm_weight_qty,
1028 new_references.final_mandatory_type_code,
1029 new_references.final_weight_qty
1030 );
1031 OPEN c;
1032 FETCH c INTO x_rowid;
1033 IF (c%NOTFOUND) THEN
1034 CLOSE c;
1035 RAISE NO_DATA_FOUND;
1036 END IF;
1037 CLOSE c;
1038 after_dml (p_action => 'INSERT', x_rowid => x_rowid);
1039 END insert_row;
1040
1041 PROCEDURE lock_row (
1042 x_rowid IN VARCHAR2,
1043 x_unit_ass_item_id IN NUMBER,
1044 x_unit_cd IN VARCHAR2,
1045 x_version_number IN NUMBER,
1046 x_cal_type IN VARCHAR2,
1047 x_ci_sequence_number IN NUMBER,
1048 x_ass_id IN NUMBER,
1049 x_sequence_number IN NUMBER,
1050 x_ci_start_dt IN DATE,
1051 x_ci_end_dt IN DATE,
1052 x_unit_class IN VARCHAR2,
1053 x_unit_mode IN VARCHAR2,
1054 x_location_cd IN VARCHAR2,
1055 x_due_dt IN DATE,
1056 x_reference IN VARCHAR2,
1057 x_dflt_item_ind IN VARCHAR2,
1058 x_logical_delete_dt IN DATE,
1059 x_action_dt IN DATE,
1060 x_exam_cal_type IN VARCHAR2,
1061 x_exam_ci_sequence_number IN NUMBER,
1062 x_grading_schema_cd IN VARCHAR2,
1063 x_gs_version_number IN NUMBER,
1064 x_release_date IN DATE,
1065 x_description IN VARCHAR2,
1066 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
1067 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1068 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
1069 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1070 x_final_weight_qty IN NUMBER DEFAULT NULL
1071 ) AS
1072 CURSOR c1 IS
1073 SELECT unit_ass_item_id,
1074 ci_start_dt,
1075 ci_end_dt,
1076 due_dt,
1077 REFERENCE,
1078 dflt_item_ind,
1079 logical_delete_dt,
1080 action_dt,
1081 exam_cal_type,
1082 exam_ci_sequence_number,
1083 grading_schema_cd,
1084 gs_version_number,
1085 release_date,
1086 description,
1087 unit_ass_item_group_id,
1088 midterm_mandatory_type_code,
1089 midterm_weight_qty,
1090 final_mandatory_type_code,
1091 final_weight_qty
1092 FROM igs_as_unitass_item_all
1093 WHERE ROWID = x_rowid
1094 FOR UPDATE NOWAIT;
1095 tlinfo c1%ROWTYPE;
1096 BEGIN
1097 OPEN c1;
1098 FETCH c1 INTO tlinfo;
1099 IF (c1%NOTFOUND) THEN
1100 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1101 igs_ge_msg_stack.ADD;
1102 CLOSE c1;
1103 app_exception.raise_exception;
1104 RETURN;
1105 END IF;
1106 CLOSE c1;
1107 IF ((TRUNC (tlinfo.ci_start_dt) = TRUNC (x_ci_start_dt))
1108 AND (TRUNC (tlinfo.ci_end_dt) = TRUNC (x_ci_end_dt))
1109 AND ((TRUNC (tlinfo.due_dt) = TRUNC (x_due_dt))
1110 OR ((tlinfo.due_dt) IS NULL)
1111 AND (x_due_dt IS NULL)
1112 )
1113 AND ((RTRIM(tlinfo.REFERENCE) = RTRIM(x_reference))
1114 OR ((tlinfo.REFERENCE IS NULL)
1115 AND (x_reference IS NULL)
1116 )
1117 )
1118 AND (tlinfo.dflt_item_ind = x_dflt_item_ind)
1119 AND ((TRUNC (tlinfo.logical_delete_dt) = TRUNC (x_logical_delete_dt))
1120 OR ((tlinfo.logical_delete_dt IS NULL)
1121 AND (x_logical_delete_dt IS NULL)
1122 )
1123 )
1124 AND ((TRUNC (tlinfo.action_dt) = TRUNC (x_action_dt))
1125 OR ((tlinfo.action_dt IS NULL)
1126 AND (x_action_dt IS NULL)
1127 )
1128 )
1129 AND ((tlinfo.exam_cal_type = x_exam_cal_type)
1130 OR ((tlinfo.exam_cal_type IS NULL)
1131 AND (x_exam_cal_type IS NULL)
1132 )
1133 )
1134 AND ((tlinfo.exam_ci_sequence_number = x_exam_ci_sequence_number)
1135 OR ((tlinfo.exam_ci_sequence_number IS NULL)
1136 AND (x_exam_ci_sequence_number IS NULL)
1137 )
1138 )
1139 AND ((tlinfo.grading_schema_cd = x_grading_schema_cd)
1140 OR ((tlinfo.grading_schema_cd IS NULL)
1141 AND (x_grading_schema_cd IS NULL)
1142 )
1143 )
1144 AND ((tlinfo.gs_version_number = x_gs_version_number)
1145 OR ((tlinfo.gs_version_number IS NULL)
1146 AND (x_gs_version_number IS NULL)
1147 )
1148 )
1149 AND ((TRUNC(tlinfo.release_date) = TRUNC(x_release_date))
1150 OR ((tlinfo.release_date IS NULL)
1151 AND (x_release_date IS NULL)
1152 )
1153 )
1154 /* AND ((tlinfo.description = x_description)
1155 OR ((tlinfo.description IS NULL)
1156 AND (x_description IS NULL)
1157 )
1158 )*/
1159 AND ((tlinfo.unit_ass_item_group_id = x_unit_ass_item_group_id)
1160 OR ((tlinfo.unit_ass_item_group_id IS NULL)
1161 AND (x_unit_ass_item_group_id IS NULL)
1162 )
1163 )
1164 AND ((tlinfo.midterm_mandatory_type_code = x_midterm_mandatory_type_code)
1165 OR ((tlinfo.midterm_mandatory_type_code IS NULL)
1166 AND (x_midterm_mandatory_type_code IS NULL)
1167 )
1168 )
1169 AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty)
1170 OR ((tlinfo.midterm_weight_qty IS NULL)
1171 AND (x_midterm_weight_qty IS NULL)
1172 )
1173 )
1174 AND ((tlinfo.final_mandatory_type_code = x_final_mandatory_type_code)
1175 OR ((tlinfo.final_mandatory_type_code IS NULL)
1176 AND (x_final_mandatory_type_code IS NULL)
1177 )
1178 )
1179 AND ((tlinfo.final_weight_qty = x_final_weight_qty)
1180 OR ((tlinfo.final_weight_qty IS NULL)
1181 AND (x_final_weight_qty IS NULL)
1182 )
1183 )
1184 ) THEN
1185 NULL;
1186 ELSE
1187 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
1188 igs_ge_msg_stack.ADD;
1189 app_exception.raise_exception;
1190 END IF;
1191
1192 RETURN;
1193 END lock_row;
1194
1195 PROCEDURE update_row (
1196 x_rowid IN VARCHAR2,
1197 x_unit_ass_item_id IN NUMBER,
1198 x_unit_cd IN VARCHAR2,
1199 x_version_number IN NUMBER,
1200 x_cal_type IN VARCHAR2,
1201 x_ci_sequence_number IN NUMBER,
1202 x_ass_id IN NUMBER,
1203 x_sequence_number IN NUMBER,
1204 x_ci_start_dt IN DATE,
1205 x_ci_end_dt IN DATE,
1206 x_unit_class IN VARCHAR2,
1207 x_unit_mode IN VARCHAR2,
1208 x_location_cd IN VARCHAR2,
1209 x_due_dt IN DATE,
1210 x_reference IN VARCHAR2,
1211 x_dflt_item_ind IN VARCHAR2,
1212 x_logical_delete_dt IN DATE,
1213 x_action_dt IN DATE,
1214 x_exam_cal_type IN VARCHAR2,
1215 x_exam_ci_sequence_number IN NUMBER,
1216 x_mode IN VARCHAR2 DEFAULT 'R',
1217 x_grading_schema_cd IN VARCHAR2,
1218 x_gs_version_number IN NUMBER,
1219 x_release_date IN DATE,
1220 x_description IN VARCHAR2,
1221 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
1222 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1223 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
1224 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1225 x_final_weight_qty IN NUMBER DEFAULT NULL
1226 ) AS
1227 x_last_update_date DATE;
1228 x_last_updated_by NUMBER;
1229 x_last_update_login NUMBER;
1230 x_request_id NUMBER;
1231 x_program_id NUMBER;
1232 x_program_application_id NUMBER;
1233 x_program_update_date DATE;
1234 BEGIN
1235 x_last_update_date := SYSDATE;
1236 IF (x_mode = 'I') THEN
1237 x_last_updated_by := 1;
1238 x_last_update_login := 0;
1239 ELSIF (x_mode = 'R') THEN
1240 x_last_updated_by := fnd_global.user_id;
1241 IF x_last_updated_by IS NULL THEN
1242 x_last_updated_by := -1;
1243 END IF;
1244 x_last_update_login := fnd_global.login_id;
1245 IF x_last_update_login IS NULL THEN
1246 x_last_update_login := -1;
1247 END IF;
1248 ELSE
1249 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1250 igs_ge_msg_stack.ADD;
1251 app_exception.raise_exception;
1252 END IF;
1253 before_dml (
1254 p_action => 'UPDATE',
1255 x_rowid => x_rowid,
1256 x_action_dt => x_action_dt,
1257 x_ass_id => x_ass_id,
1258 x_cal_type => x_cal_type,
1259 x_ci_end_dt => x_ci_end_dt,
1260 x_ci_sequence_number => x_ci_sequence_number,
1261 x_ci_start_dt => x_ci_start_dt,
1262 x_dflt_item_ind => x_dflt_item_ind,
1263 x_due_dt => x_due_dt,
1264 x_exam_cal_type => x_exam_cal_type,
1265 x_exam_ci_sequence_number => x_exam_ci_sequence_number,
1266 x_location_cd => x_location_cd,
1267 x_logical_delete_dt => x_logical_delete_dt,
1268 x_reference => x_reference,
1269 x_sequence_number => x_sequence_number,
1270 x_unit_cd => x_unit_cd,
1271 x_unit_class => x_unit_class,
1272 x_unit_mode => x_unit_mode,
1273 x_version_number => x_version_number,
1274 x_grading_schema_cd => x_grading_schema_cd,
1275 x_gs_version_number => x_gs_version_number,
1276 x_release_date => x_release_date,
1277 x_creation_date => x_last_update_date,
1278 x_created_by => x_last_updated_by,
1279 x_last_update_date => x_last_update_date,
1280 x_last_updated_by => x_last_updated_by,
1281 x_last_update_login => x_last_update_login,
1282 x_description => x_description,
1283 x_unit_ass_item_group_id => x_unit_ass_item_group_id,
1284 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
1285 x_midterm_weight_qty => x_midterm_weight_qty,
1286 x_final_mandatory_type_code => x_final_mandatory_type_code,
1287 x_final_weight_qty => x_final_weight_qty
1288 );
1289 IF (x_mode = 'R') THEN
1290 x_request_id := fnd_global.conc_request_id;
1291 x_program_id := fnd_global.conc_program_id;
1292 x_program_application_id := fnd_global.prog_appl_id;
1293 IF (x_request_id = -1) THEN
1294 x_request_id := old_references.request_id;
1295 x_program_id := old_references.program_id;
1296 x_program_application_id := old_references.program_application_id;
1297 x_program_update_date := old_references.program_update_date;
1298 ELSE
1299 x_program_update_date := SYSDATE;
1300 END IF;
1301 END IF;
1302 UPDATE igs_as_unitass_item_all
1303 SET ci_start_dt = new_references.ci_start_dt,
1304 ci_end_dt = new_references.ci_end_dt,
1305 unit_class = new_references.unit_class,
1306 unit_mode = new_references.unit_mode,
1307 location_cd = new_references.location_cd,
1308 due_dt = new_references.due_dt,
1309 REFERENCE = new_references.REFERENCE,
1310 dflt_item_ind = new_references.dflt_item_ind,
1311 logical_delete_dt = new_references.logical_delete_dt,
1312 action_dt = new_references.action_dt,
1313 exam_cal_type = new_references.exam_cal_type,
1314 exam_ci_sequence_number = new_references.exam_ci_sequence_number,
1315 grading_schema_cd = new_references.grading_schema_cd,
1316 gs_version_number = new_references.gs_version_number,
1317 release_date = new_references.release_date,
1318 last_update_date = x_last_update_date,
1319 last_updated_by = x_last_updated_by,
1320 last_update_login = x_last_update_login,
1321 request_id = x_request_id,
1322 program_id = x_program_id,
1323 program_application_id = x_program_application_id,
1324 program_update_date = x_program_update_date,
1325 description = x_description,
1326 unit_ass_item_group_id = x_unit_ass_item_group_id,
1327 midterm_mandatory_type_code = x_midterm_mandatory_type_code,
1328 midterm_weight_qty = x_midterm_weight_qty,
1329 final_mandatory_type_code = x_final_mandatory_type_code,
1330 final_weight_qty = x_final_weight_qty
1331 WHERE ROWID = x_rowid;
1332 IF (SQL%NOTFOUND) THEN
1333 RAISE NO_DATA_FOUND;
1334 END IF;
1335 after_dml (p_action => 'UPDATE', x_rowid => x_rowid);
1336 END update_row;
1337
1338 PROCEDURE add_row (
1339 x_rowid IN OUT NOCOPY VARCHAR2,
1340 x_unit_ass_item_id IN OUT NOCOPY NUMBER,
1341 x_unit_cd IN VARCHAR2,
1342 x_version_number IN NUMBER,
1343 x_cal_type IN VARCHAR2,
1344 x_ci_sequence_number IN NUMBER,
1345 x_ass_id IN NUMBER,
1346 x_sequence_number IN NUMBER,
1347 x_ci_start_dt IN DATE,
1348 x_ci_end_dt IN DATE,
1349 x_unit_class IN VARCHAR2,
1350 x_unit_mode IN VARCHAR2,
1351 x_location_cd IN VARCHAR2,
1352 x_due_dt IN DATE,
1353 x_reference IN VARCHAR2,
1354 x_dflt_item_ind IN VARCHAR2,
1355 x_logical_delete_dt IN DATE,
1356 x_action_dt IN DATE,
1357 x_exam_cal_type IN VARCHAR2,
1358 x_exam_ci_sequence_number IN NUMBER,
1359 x_mode IN VARCHAR2 DEFAULT 'R',
1360 x_org_id IN NUMBER,
1361 x_grading_schema_cd IN VARCHAR2,
1362 x_gs_version_number IN NUMBER,
1363 x_release_date IN DATE,
1364 x_description IN VARCHAR2,
1365 x_unit_ass_item_group_id IN VARCHAR2 DEFAULT NULL,
1366 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1367 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
1368 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
1369 x_final_weight_qty IN NUMBER DEFAULT NULL
1370 ) AS
1371 CURSOR c1 IS
1372 SELECT ROWID
1373 FROM igs_as_unitass_item_all
1374 WHERE unit_cd = x_unit_cd
1375 AND version_number = x_version_number
1376 AND cal_type = x_cal_type
1377 AND ci_sequence_number = x_ci_sequence_number
1378 AND ass_id = x_ass_id
1379 AND sequence_number = x_sequence_number;
1380 BEGIN
1381 OPEN c1;
1382 FETCH c1 INTO x_rowid;
1383 IF (c1%NOTFOUND) THEN
1384 CLOSE c1;
1385 insert_row (
1386 x_rowid,
1387 x_unit_ass_item_id,
1388 x_unit_cd,
1389 x_version_number,
1390 x_cal_type,
1391 x_ci_sequence_number,
1392 x_ass_id,
1393 x_sequence_number,
1394 x_ci_start_dt,
1395 x_ci_end_dt,
1396 x_unit_class,
1397 x_unit_mode,
1398 x_location_cd,
1399 x_due_dt,
1400 x_reference,
1401 x_dflt_item_ind,
1402 x_logical_delete_dt,
1403 x_action_dt,
1404 x_exam_cal_type,
1405 x_exam_ci_sequence_number,
1406 x_mode,
1407 x_org_id,
1408 x_grading_schema_cd,
1409 x_gs_version_number,
1410 x_release_date,
1411 x_description,
1412 x_unit_ass_item_group_id,
1413 x_midterm_mandatory_type_code,
1414 x_midterm_weight_qty,
1415 x_final_mandatory_type_code,
1416 x_final_weight_qty
1417 );
1418 RETURN;
1419 END IF;
1420 CLOSE c1;
1421 update_row (
1422 x_rowid,
1423 x_unit_ass_item_id,
1424 x_unit_cd,
1425 x_version_number,
1426 x_cal_type,
1427 x_ci_sequence_number,
1428 x_ass_id,
1429 x_sequence_number,
1430 x_ci_start_dt,
1431 x_ci_end_dt,
1432 x_unit_class,
1433 x_unit_mode,
1434 x_location_cd,
1435 x_due_dt,
1436 x_reference,
1437 x_dflt_item_ind,
1438 x_logical_delete_dt,
1439 x_action_dt,
1440 x_exam_cal_type,
1441 x_exam_ci_sequence_number,
1442 x_mode,
1443 x_grading_schema_cd,
1444 x_gs_version_number,
1445 x_release_date,
1446 x_description,
1447 x_unit_ass_item_group_id,
1448 x_midterm_mandatory_type_code,
1449 x_midterm_weight_qty,
1450 x_final_mandatory_type_code,
1451 x_final_weight_qty
1452 );
1453 END add_row;
1454
1455 PROCEDURE delete_row (x_rowid IN VARCHAR2) AS
1456 BEGIN
1457 before_dml (p_action => 'DELETE', x_rowid => x_rowid);
1458 DELETE FROM igs_as_unitass_item_all
1459 WHERE ROWID = x_rowid;
1460 IF (SQL%NOTFOUND) THEN
1461 RAISE NO_DATA_FOUND;
1462 END IF;
1463 after_dml (p_action => 'DELETE', x_rowid => x_rowid);
1464 END delete_row;
1465 END igs_as_unitass_item_pkg;