[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_GEN_004
Source
1 PACKAGE BODY IGS_AS_GEN_004 AS
2 /* $Header: IGSAS04B.pls 120.1 2005/11/28 03:38:11 appldev ship $ */
3 /*======================================================================+
4 | |
5 | DESCRIPTION |
6 | PL/SQL boby for package: igs_as_gen_001 |
7 | |
8 | NOTES |
9 | |
10 | CHANGE HISTORY |
11 +======================================================================+
12 | WHO WHEN WHAT |
13 +======================================================================+
14 | nalkumar 24-May-2003 Modified the call to the igs_as_su_atmpt_itm_pkg|
15 | Added two new parameters |
16 | x_unit_section_ass_item_id and |
17 | x_unit_ass_item_id in the call; |
18 | This is as per 'Assessment Item description |
19 | Build'; Bug# 2829291 |
20 | ijeddy 19-Jun-2003 Bug 2884615, addition of notified_date in |
21 | assp_ins_scap_lovall. |
22 | kdande 18-Aug-2003 Bug# 2895945. Changed the cursors to use |
23 | hz_parties table instead of igs_pe_person view. |
24 | smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_id_no_ind,|
25 | c_id_with_ind, c_surname_no_ind, c_sua_person_id,
26 | c_surname_with_ind, c_uop and c_sua_surname to |
27 | select active (not closed) unit classes. |
28 | ijeddy 11/28/2005 Bug 4763207, modified c_suaai_an. |
29 +======================================================================+*/
30 --
31 -- Bug No. 1956374 Procedure assp_val_suaai_ins reference is changed
32 --
33 PROCEDURE asss_ins_transcript (
34 errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY NUMBER,
36 p_course_org_unit_cd IN VARCHAR2,
37 p_course_group_cd IN VARCHAR2,
38 p_course_cd IN VARCHAR2,
39 p_course_location_cd IN VARCHAR2,
40 p_course_attendance_mode IN VARCHAR2,
41 p_course_award IN VARCHAR2 DEFAULT 'BOTH',
42 p_course_attempt_status IN VARCHAR2,
43 p_progression_status IN VARCHAR2,
44 p_graduand_status IN VARCHAR2,
45 p_person_id_group IN NUMBER,
46 p_person_id IN NUMBER,
47 p_transcript_type IN VARCHAR2,
48 p_include_fail_grades_ind IN VARCHAR2 DEFAULT 'N',
49 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
50 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
51 p_exclude_unit_category IN VARCHAR2,
52 p_extract_course_cd IN VARCHAR2,
53 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
54 p_order_by IN VARCHAR2 DEFAULT 'YEAR',
55 p_external_order_by IN VARCHAR2 DEFAULT 'SURNAME',
56 p_correspondence_ind IN VARCHAR2 DEFAULT 'N',
57 p_org_id IN NUMBER
58 ) IS
59 BEGIN
60 --
61 retcode := 0;
62 --
63 -- As per 2239087, this concurrent program is obsolete and if the user
64 -- tries to run this program then an error message should be logged into the log
65 -- file that the concurrent program is obsolete and should not be run.
66 --
67 fnd_message.set_name ('IGS', 'IGS_GE_OBSOLETE_JOB');
68 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
69 --
70 EXCEPTION
71 WHEN OTHERS THEN
72 retcode := 2;
73 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
74 igs_ge_msg_stack.conc_exception_hndl;
75 END asss_ins_transcript;
76 --
77 -- This function is obsolete as the Grade Book Enhancement obsoleted the
78 -- Assessment Patterns functionality
79 --
80 FUNCTION assp_get_uap_cd (
81 p_ass_pattern_id IN NUMBER
82 ) RETURN VARCHAR2 IS
83 BEGIN
84 RETURN NULL;
85 END assp_get_uap_cd;
86 --
87 --
88 --
89 FUNCTION assp_ins_dflt_evsa (
90 p_venue_cd IN VARCHAR2,
91 p_exam_cal_type IN VARCHAR2,
92 p_exam_ci_sequence_number IN NUMBER,
93 p_message_name OUT NOCOPY VARCHAR2
94 ) RETURN BOOLEAN IS
95 gv_other_detail VARCHAR2 (255);
96 l_org_id NUMBER (15);
97 BEGIN -- assp_ins_dflt_evsa
98 -- Routine to insert default session details for a nominated examination
99 -- IGS_GR_VENUE and period.
100 -- The routine will only insert if there are no existing record for the
101 -- IGS_GR_VENUE under the nominated examination period.
102 DECLARE
103 v_inserted_flag BOOLEAN DEFAULT FALSE;
104 l_rowid VARCHAR2 (25);
105 v_evsa_exists VARCHAR2 (1);
106 CURSOR c_evsa IS
107 SELECT 'x'
108 FROM igs_as_exmvnu_sesavl
109 WHERE venue_cd = p_venue_cd
110 AND exam_cal_type = p_exam_cal_type
111 AND exam_ci_sequence_number = p_exam_ci_sequence_number;
112 CURSOR c_es IS
113 SELECT exam_cal_type,
114 exam_ci_sequence_number,
115 dt_alias,
116 dai_sequence_number,
117 start_time,
118 end_time,
119 ese_id,
120 comments
121 FROM igs_as_exam_session
122 WHERE exam_cal_type = p_exam_cal_type
123 AND exam_ci_sequence_number = p_exam_ci_sequence_number;
124 BEGIN
125 -- Set the default message number
126 p_message_name := NULL;
127 -- 1. Check that there are no existing details under the nominated
128 -- period/IGS_GR_VENUE combination.
129 OPEN c_evsa;
130 FETCH c_evsa INTO v_evsa_exists;
131 IF (c_evsa%FOUND) THEN
132 p_message_name := 'IGS_AS_CANNOT_DFLT_RECORDS';
133 RETURN FALSE;
134 END IF;
135 -- 2. Default the session availability for all sessions within the
136 -- nominated examination calendar.
137 FOR v_es_rec IN c_es LOOP
138 v_inserted_flag := TRUE;
139 --get org id
140 l_org_id := igs_ge_gen_003.get_org_id;
141 igs_as_exmvnu_sesavl_pkg.insert_row (
142 x_mode => 'R',
143 x_rowid => l_rowid,
144 x_org_id => l_org_id,
145 x_venue_cd => p_venue_cd,
146 x_exam_cal_type => v_es_rec.exam_cal_type,
147 x_exam_ci_sequence_number => v_es_rec.exam_ci_sequence_number,
148 x_dt_alias => v_es_rec.dt_alias,
149 x_dai_sequence_number => v_es_rec.dai_sequence_number,
150 x_start_time => v_es_rec.start_time,
151 x_end_time => v_es_rec.end_time,
152 x_ese_id => v_es_rec.ese_id,
153 x_comments => v_es_rec.comments
154 );
155 END LOOP;
156 IF (v_inserted_flag = FALSE) THEN
157 -- no sessions found
158 p_message_name := 'IGS_AS_NOEXAM_SESSIONS_FOUND';
159 RETURN FALSE;
160 END IF;
161 COMMIT WORK;
162 RETURN TRUE;
163 END;
164 EXCEPTION
165 WHEN OTHERS THEN
166 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
167 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_dflt_evsa');
168 igs_ge_msg_stack.ADD;
169 app_exception.raise_exception;
170 END assp_ins_dflt_evsa;
171 --
172 --
173 --
174 FUNCTION assp_ins_get (
175 p_keying_who IN VARCHAR2,
176 p_sheet_number IN NUMBER,
177 p_cal_type IN VARCHAR2,
178 p_sequence_number IN NUMBER,
179 p_unit_cd IN VARCHAR2,
180 p_location_cd IN VARCHAR2,
181 p_unit_mode IN VARCHAR2,
182 p_unit_class IN VARCHAR2,
183 p_include_discont_ind IN VARCHAR2,
184 p_sort_by IN VARCHAR2,
185 p_keying_time OUT NOCOPY DATE
186 ) RETURN BOOLEAN IS
187 gv_other_detail VARCHAR2 (255);
188 BEGIN
189 DECLARE
190 cst_enrolled igs_en_su_attempt.unit_attempt_status%TYPE := 'ENROLLED';
191 cst_discontin igs_en_su_attempt.unit_attempt_status%TYPE := 'DISCONTIN';
192 cst_completed igs_en_su_attempt.unit_attempt_status%TYPE := 'COMPLETED';
193 cst_yes VARCHAR2 (1) := 'Y';
194 cst_no VARCHAR2 (1) := 'N';
195 cst_id VARCHAR2 (10) := 'ID';
196 cst_surname VARCHAR2 (10) := 'SURNAME';
197 cst_v7 INTEGER := 2;
198 v_keying_time DATE;
199 v_exists_flag CHAR;
200 v_exit_loop_flag BOOLEAN DEFAULT FALSE;
201 v_sort_parse VARCHAR2 (30);
202 v_select_statuses VARCHAR2 (50);
203 v_return INTEGER;
204 v_cursor_handle INTEGER;
205 v_student_seq NUMBER DEFAULT 1;
206 v_parse_command VARCHAR2 (1000);
207 v_get_record igs_as_ins_grd_entry%ROWTYPE;
208 v_initials igs_pe_person.given_names%TYPE;
209 v_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE;
210 v_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE;
211 v_unit_mode igs_as_unit_class.unit_mode%TYPE;
212 CURSOR c_chk_keying_time (cp_keying_time DATE, cp_keying_who igs_as_ins_grd_entry.keying_who%TYPE) IS
213 SELECT 'x'
214 FROM DUAL
215 WHERE EXISTS ( SELECT *
216 FROM igs_as_ins_grd_entry iaige
217 WHERE iaige.keying_time = cp_keying_time
218 AND iaige.keying_who = cp_keying_who);
219 CURSOR c_chk_mark_sheets (cp_mark_sheet igs_as_mark_sheet.sheet_number%TYPE) IS
220 SELECT 'x'
221 FROM DUAL
222 WHERE EXISTS ( SELECT *
223 FROM igs_as_msht_su_atmpt iamsa
224 WHERE iamsa.sheet_number = cp_mark_sheet);
225 CURSOR c_grd_entry_tmp IS
226 SELECT p_keying_who,
227 v_keying_time,
228 mssua.student_sequence,
229 mssua.person_id,
230 pe.person_last_name surname,
231 mssua.course_cd,
232 mssua.unit_cd,
233 sua.version_number,
234 mssua.cal_type,
235 mssua.ci_sequence_number,
236 sua.location_cd,
237 sua.unit_class,
238 sua.unit_attempt_status,
239 NULL n1,
240 NULL n2,
241 NULL n3,
242 NULL n4,
243 'N'
244 FROM igs_as_msht_su_atmpt mssua,
245 hz_parties pe,
246 igs_en_su_attempt sua
247 WHERE mssua.sheet_number = p_sheet_number
248 AND pe.party_id = mssua.person_id
249 AND sua.person_id(+) = mssua.person_id
250 AND sua.course_cd(+) = mssua.course_cd
251 AND sua.uoo_id(+) = mssua.uoo_id;
252 --
253 c_grd_entry_tmp_rec c_grd_entry_tmp%ROWTYPE;
254 l_rowid VARCHAR2 (25);
255 BEGIN
256 -- Generate the keying time
257 v_keying_time := SYSDATE;
258 LOOP
259 -- If any records exist matching the p_keying_who with the same date/time
260 -- then increment the time by 5 seconds and try again
261 EXIT WHEN v_exit_loop_flag;
262 OPEN c_chk_keying_time (v_keying_time, p_keying_who);
263 FETCH c_chk_keying_time INTO v_exists_flag;
264 IF c_chk_keying_time%FOUND THEN
265 v_keying_time := v_keying_time + 1 / 17280;
266 ELSE
267 v_exit_loop_flag := TRUE;
268 END IF;
269 CLOSE c_chk_keying_time;
270 END LOOP;
271 IF p_sheet_number IS NOT NULL THEN
272 -- The routine should copy the records from the nominated mark sheet
273 -- Copy records from IGS_AS_MSHT_SU_ATMPT to the IGS_AS_INS_GRD_ENTRY table
274 -- where the sheet_number matches p_sheet_number
275 FOR c_grd_entry_tmp_rec IN c_grd_entry_tmp LOOP
276 igs_as_ins_grd_entry_pkg.insert_row (
277 x_mode => 'R',
278 x_rowid => l_rowid,
279 x_keying_who => c_grd_entry_tmp_rec.p_keying_who,
280 x_keying_time => c_grd_entry_tmp_rec.v_keying_time,
281 x_student_sequence => c_grd_entry_tmp_rec.student_sequence,
282 x_person_id => c_grd_entry_tmp_rec.person_id,
283 x_name => c_grd_entry_tmp_rec.surname,
284 x_course_cd => c_grd_entry_tmp_rec.course_cd,
285 x_unit_cd => c_grd_entry_tmp_rec.unit_cd,
286 x_version_number => c_grd_entry_tmp_rec.version_number,
287 x_cal_type => c_grd_entry_tmp_rec.cal_type,
288 x_ci_sequence_number => c_grd_entry_tmp_rec.ci_sequence_number,
289 x_location_cd => c_grd_entry_tmp_rec.location_cd,
290 x_unit_class => c_grd_entry_tmp_rec.unit_class,
291 x_unit_attempt_status => c_grd_entry_tmp_rec.unit_attempt_status,
292 x_mark => NULL,
293 x_grading_schema_cd => NULL,
294 x_gs_version_number => NULL,
295 x_grade => NULL,
296 x_specified_grade_ind => NULL
297 );
298 END LOOP;
299 OPEN c_chk_mark_sheets (p_sheet_number);
300 FETCH c_chk_mark_sheets INTO v_exists_flag;
301 IF c_chk_mark_sheets%NOTFOUND THEN
302 CLOSE c_chk_mark_sheets;
303 p_keying_time := NULL;
304 RETURN FALSE;
305 ELSE
306 CLOSE c_chk_mark_sheets;
307 p_keying_time := v_keying_time;
308 COMMIT;
309 RETURN TRUE;
310 END IF;
311 ELSE
312 -- The routine must query the students and create the temporary structure
313 -- v_sort_parse is set to pe.person_number though the incoming p_sort_by remains as ID
314 -- this is done to make the sort happen by person_number instead of person_id
315 -- without affecting the call to this procedure
316 -- so the call to this procedure will still pass ID if the sort is to be done by person_number
317 --
318 IF p_sort_by = cst_id THEN
319 v_sort_parse := 'pe.person_number';
320 ELSIF p_sort_by = cst_surname THEN
321 v_sort_parse := 'pe.surname';
322 END IF;
323 IF p_include_discont_ind = cst_no THEN
324 v_select_statuses := '''' || cst_enrolled || ''', ''' || cst_completed || '''';
325 ELSE
326 v_select_statuses := '''' || cst_enrolled || ''', ''' || cst_discontin || ''', ''' || cst_completed || '''';
327 END IF;
328 IF p_unit_mode IS NOT NULL THEN
329 v_unit_mode := p_unit_mode;
330 v_unit_class := '%';
331 ELSE
332 v_unit_mode := '%';
333 IF p_unit_class IS NOT NULL THEN
334 v_unit_class := p_unit_class;
335 ELSE
336 v_unit_class := '%';
337 END IF;
338 END IF;
339 IF p_location_cd IS NOT NULL THEN
340 v_location_cd := p_location_cd;
341 ELSE
342 v_location_cd := '%';
343 END IF;
344 --
345 -- Code to replace the earlier existing Dynamic SQL
346 --
347 DECLARE
348 CURSOR c_id_no_ind IS
349 SELECT sua.location_cd,
350 sua.unit_class,
351 sua.person_id,
352 sua.course_cd,
353 sua.unit_attempt_status,
354 pe.person_last_name surname,
355 igs_ge_gen_002.genp_get_initials (pe.person_first_name),
356 sua.version_number
357 FROM igs_ps_unit_ofr_opt uoo,
358 igs_en_su_attempt sua,
359 hz_parties pe,
360 igs_as_unit_class ucl
361 WHERE uoo.unit_cd = p_unit_cd
362 AND uoo.cal_type = p_cal_type
363 AND uoo.ci_sequence_number = TO_CHAR (p_sequence_number)
364 AND uoo.location_cd LIKE v_location_cd
365 AND uoo.unit_class LIKE v_unit_class
366 AND ucl.unit_class = uoo.unit_class
367 AND ucl.unit_mode LIKE v_unit_mode
368 AND sua.uoo_id = uoo.uoo_id
369 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED')
370 AND pe.party_id = sua.person_id
371 AND ucl.closed_ind = 'N'
372 ORDER BY pe.party_number;
373 --
374 -- Including Discontin Status alongwith ENROLLED,COMPLETED,DISCONTIN
375 --
376 CURSOR c_id_with_ind IS
377 SELECT sua.location_cd,
378 sua.unit_class,
379 sua.person_id,
380 sua.course_cd,
381 sua.unit_attempt_status,
382 pe.person_last_name surname,
383 igs_ge_gen_002.genp_get_initials (pe.person_first_name),
384 sua.version_number
385 FROM igs_ps_unit_ofr_opt uoo,
386 igs_en_su_attempt sua,
387 hz_parties pe,
388 igs_as_unit_class ucl
389 WHERE uoo.unit_cd = p_unit_cd
390 AND uoo.cal_type = p_cal_type
391 AND uoo.ci_sequence_number = TO_CHAR (p_sequence_number)
392 AND uoo.location_cd LIKE v_location_cd
393 AND uoo.unit_class LIKE v_unit_class
394 AND ucl.unit_class = uoo.unit_class
395 AND ucl.unit_mode LIKE v_unit_mode
396 AND sua.uoo_id = uoo.uoo_id
397 AND sua.unit_attempt_status IN ('ENROLLED', 'DISCONTIN', 'COMPLETED')
398 AND pe.party_id = sua.person_id
399 AND ucl.closed_ind = 'N'
400 ORDER BY pe.party_number;
401 --
402 -- Excluding Discontin Status that is JUST with ENROLLED,COMPLETED
403 --
404 CURSOR c_surname_no_ind (
405 cp_unit_cd VARCHAR2,
406 cp_cal_type VARCHAR2,
407 cp_sequence_number NUMBER,
408 cp_location_cd VARCHAR2,
409 cp_unit_class VARCHAR2,
410 cp_unit_mode VARCHAR2
411 ) IS
412 SELECT sua.location_cd,
413 sua.unit_class,
414 sua.person_id,
415 sua.course_cd,
416 sua.unit_attempt_status,
417 pe.person_last_name surname,
418 igs_ge_gen_002.genp_get_initials (pe.person_first_name),
419 sua.version_number
420 FROM igs_ps_unit_ofr_opt uoo,
421 igs_en_su_attempt sua,
422 hz_parties pe,
423 igs_as_unit_class ucl
424 WHERE uoo.unit_cd = cp_unit_cd
425 AND uoo.cal_type = cp_cal_type
426 AND uoo.ci_sequence_number = TO_CHAR (cp_sequence_number)
427 AND uoo.location_cd LIKE cp_location_cd
428 AND uoo.unit_class LIKE cp_unit_class
429 AND ucl.unit_class = uoo.unit_class
430 AND ucl.unit_mode LIKE cp_unit_mode
431 AND sua.uoo_id = uoo.uoo_id
432 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED')
433 AND pe.party_id = sua.person_id
434 AND ucl.closed_ind = 'N'
435 ORDER BY pe.person_last_name;
436 --
437 CURSOR c_surname_with_ind (
438 cp_unit_cd VARCHAR2,
439 cp_cal_type VARCHAR2,
440 cp_sequence_number NUMBER,
441 cp_location_cd VARCHAR2,
442 cp_unit_class VARCHAR2,
443 cp_unit_mode VARCHAR2
444 ) IS
445 SELECT sua.location_cd,
446 sua.unit_class,
447 sua.person_id,
448 sua.course_cd,
449 sua.unit_attempt_status,
450 pe.person_last_name surname,
451 igs_ge_gen_002.genp_get_initials (pe.person_first_name),
452 sua.version_number
453 FROM igs_ps_unit_ofr_opt uoo,
454 igs_en_su_attempt sua,
455 hz_parties pe,
456 igs_as_unit_class ucl
457 WHERE uoo.unit_cd = cp_unit_cd
458 AND uoo.cal_type = cp_cal_type
459 AND uoo.ci_sequence_number = TO_CHAR (cp_sequence_number)
460 AND uoo.location_cd LIKE cp_location_cd
461 AND uoo.unit_class LIKE cp_unit_class
462 AND ucl.unit_class = uoo.unit_class
463 AND ucl.unit_mode LIKE cp_unit_mode
464 AND sua.uoo_id = uoo.uoo_id
465 AND sua.unit_attempt_status IN ('ENROLLED', 'DISCONTIN', 'COMPLETED')
466 AND pe.party_id = sua.person_id
467 AND ucl.closed_ind = 'N'
468 ORDER BY pe.person_last_name;
469 --
470 l_location_cd VARCHAR2 (10);
471 l_unit_class VARCHAR2 (10);
472 l_person_id NUMBER;
473 l_course_cd VARCHAR2 (10);
474 l_unit_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE;
475 l_name VARCHAR2 (30);
476 l_initials VARCHAR2 (10);
477 l_version_number NUMBER;
478 l_rowid VARCHAR2 (25);
479 BEGIN
480 IF (v_sort_parse = 'pe.person_number') THEN
481 IF p_include_discont_ind = cst_no THEN
482 OPEN c_id_no_ind;
483 LOOP
484 FETCH c_id_no_ind INTO l_location_cd,
485 l_unit_class,
486 l_person_id,
487 l_course_cd,
488 l_unit_attempt_status,
489 l_name,
490 l_initials,
491 l_version_number;
492 IF c_id_no_ind%NOTFOUND THEN
493 EXIT;
494 END IF;
495 igs_as_ins_grd_entry_pkg.insert_row (
496 x_mode => 'R',
497 x_rowid => l_rowid,
498 x_keying_who => p_keying_who,
499 x_keying_time => v_keying_time,
500 x_student_sequence => v_student_seq,
501 x_person_id => l_person_id,
502 x_name => l_name || ', ' || l_initials,
503 x_course_cd => l_course_cd,
504 x_unit_cd => p_unit_cd,
505 x_version_number => l_version_number,
506 x_cal_type => p_cal_type,
507 x_ci_sequence_number => p_sequence_number,
508 x_location_cd => l_location_cd,
509 x_unit_class => l_unit_class,
510 x_unit_attempt_status => l_unit_attempt_status,
511 x_mark => NULL,
512 x_grading_schema_cd => NULL,
513 x_gs_version_number => NULL,
514 x_grade => NULL,
515 x_specified_grade_ind => 'N'
516 );
517 v_student_seq := v_student_seq + 1;
518 END LOOP;
519 CLOSE c_id_no_ind;
520 ELSE
521 OPEN c_id_with_ind;
522 LOOP
523 FETCH c_id_with_ind INTO l_location_cd,
524 l_unit_class,
525 l_person_id,
526 l_course_cd,
527 l_unit_attempt_status,
528 l_name,
529 l_initials,
530 l_version_number;
531 IF c_id_with_ind%NOTFOUND THEN
532 EXIT;
533 END IF;
534 igs_as_ins_grd_entry_pkg.insert_row (
535 x_mode => 'R',
536 x_rowid => l_rowid,
537 x_keying_who => p_keying_who,
538 x_keying_time => v_keying_time,
539 x_student_sequence => v_student_seq,
540 x_person_id => l_person_id,
541 x_name => l_name || ', ' || l_initials,
542 x_course_cd => l_course_cd,
543 x_unit_cd => p_unit_cd,
544 x_version_number => l_version_number,
545 x_cal_type => p_cal_type,
546 x_ci_sequence_number => p_sequence_number,
547 x_location_cd => l_location_cd,
548 x_unit_class => l_unit_class,
549 x_unit_attempt_status => l_unit_attempt_status,
550 x_mark => NULL,
551 x_grading_schema_cd => NULL,
552 x_gs_version_number => NULL,
553 x_grade => NULL,
554 x_specified_grade_ind => 'N'
555 );
556 v_student_seq := v_student_seq + 1;
557 END LOOP;
558 CLOSE c_id_with_ind;
559 END IF;
560 ELSIF v_sort_parse = 'pe.surname' THEN
561 IF p_include_discont_ind = cst_no THEN
562 OPEN c_surname_no_ind (
563 p_unit_cd,
564 p_cal_type,
565 p_sequence_number,
566 v_location_cd,
567 v_unit_class,
568 v_unit_mode
569 );
570 LOOP
571 FETCH c_surname_no_ind INTO l_location_cd,
572 l_unit_class,
573 l_person_id,
574 l_course_cd,
575 l_unit_attempt_status,
576 l_name,
577 l_initials,
578 l_version_number;
579 IF c_surname_no_ind%NOTFOUND THEN
580 EXIT;
581 END IF;
582 igs_as_ins_grd_entry_pkg.insert_row (
583 x_mode => 'R',
584 x_rowid => l_rowid,
585 x_keying_who => p_keying_who,
586 x_keying_time => v_keying_time,
587 x_student_sequence => v_student_seq,
588 x_person_id => l_person_id,
589 x_name => l_name || ', ' || l_initials,
590 x_course_cd => l_course_cd,
591 x_unit_cd => p_unit_cd,
592 x_version_number => l_version_number,
593 x_cal_type => p_cal_type,
594 x_ci_sequence_number => p_sequence_number,
595 x_location_cd => l_location_cd,
596 x_unit_class => l_unit_class,
597 x_unit_attempt_status => l_unit_attempt_status,
598 x_mark => NULL,
599 x_grading_schema_cd => NULL,
600 x_gs_version_number => NULL,
601 x_grade => NULL,
602 x_specified_grade_ind => 'N'
603 );
604 v_student_seq := v_student_seq + 1;
605 END LOOP;
606 CLOSE c_surname_no_ind;
607 ELSE
608 OPEN c_surname_with_ind (
609 p_unit_cd,
610 p_cal_type,
611 p_sequence_number,
612 v_location_cd,
613 v_unit_class,
614 v_unit_mode
615 );
616 LOOP
617 FETCH c_surname_with_ind INTO l_location_cd,
618 l_unit_class,
619 l_person_id,
620 l_course_cd,
621 l_unit_attempt_status,
622 l_name,
623 l_initials,
624 l_version_number;
625 IF c_surname_with_ind%NOTFOUND THEN
626 EXIT;
627 END IF;
628 igs_as_ins_grd_entry_pkg.insert_row (
629 x_mode => 'R',
630 x_rowid => l_rowid,
631 x_keying_who => p_keying_who,
632 x_keying_time => v_keying_time,
633 x_student_sequence => v_student_seq,
634 x_person_id => l_person_id,
635 x_name => l_name || ', ' || l_initials,
636 x_course_cd => l_course_cd,
637 x_unit_cd => p_unit_cd,
638 x_version_number => l_version_number,
639 x_cal_type => p_cal_type,
640 x_ci_sequence_number => p_sequence_number,
641 x_location_cd => l_location_cd,
642 x_unit_class => l_unit_class,
643 x_unit_attempt_status => l_unit_attempt_status,
644 x_mark => NULL,
645 x_grading_schema_cd => NULL,
646 x_gs_version_number => NULL,
647 x_grade => NULL,
648 x_specified_grade_ind => 'N'
649 );
650 v_student_seq := v_student_seq + 1;
651 END LOOP;
652 CLOSE c_surname_with_ind;
653 END IF;
654 END IF;
655 END;
656 OPEN c_chk_keying_time (v_keying_time, p_keying_who);
657 FETCH c_chk_keying_time INTO v_exists_flag;
658 IF c_chk_keying_time%NOTFOUND THEN
659 CLOSE c_chk_keying_time;
660 p_keying_time := NULL;
661 RETURN FALSE;
662 ELSE
663 CLOSE c_chk_keying_time;
664 p_keying_time := v_keying_time;
665 COMMIT;
666 RETURN TRUE;
667 END IF;
668 END IF;
669 END;
670 EXCEPTION
671 WHEN OTHERS THEN
672 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
673 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_get');
674 igs_ge_msg_stack.ADD;
675 app_exception.raise_exception;
676 END assp_ins_get;
677 --
678 --
679 --
680 FUNCTION assp_ins_mark_sheet (
681 p_assess_cal_type IN VARCHAR2,
682 p_assess_sequence_number IN NUMBER,
683 p_teach_cal_type IN VARCHAR2,
684 p_teach_sequence_number IN NUMBER,
685 p_unit_org_unit_cd IN VARCHAR2,
686 p_unit_cd IN VARCHAR2,
687 p_location_cd IN VARCHAR2,
688 p_unit_mode IN VARCHAR2,
689 p_include_discont_ind IN VARCHAR2,
690 p_sort_by IN VARCHAR2,
691 p_group_sequence_number OUT NOCOPY NUMBER,
692 p_grading_period_cd IN VARCHAR2,
693 p_unit_class IN VARCHAR2,
694 p_call_number IN NUMBER
695 ) RETURN BOOLEAN IS
696 gv_other_detail VARCHAR2 (255);
697 BEGIN -- assp_ins_mark_sheet
698 -- Inserts mark sheet records (ms and mssua)for students matching the passed
699 -- parameters
700 DECLARE
701 -- select the mark sheets to produce based on parameters
702 e_resource_busy EXCEPTION;
703 --PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
704 v_mss_mark_sheet igs_as_mark_sheet.sheet_number%TYPE;
705 v_ms_sequence_number igs_as_mark_sheet.sheet_number%TYPE;
706 v_group_sequence_number igs_as_mark_sheet.group_sequence_number%TYPE;
707 cst_no CONSTANT CHAR := 'N';
708 v_duplicate_sheet BOOLEAN DEFAULT FALSE;
709 v_not_duplicate BOOLEAN;
710 v_duplicate_ind VARCHAR2 (1) := 'N';
711 v_ins_ms BOOLEAN DEFAULT FALSE;
712 v_sheet_number igs_as_mark_sheet.sheet_number%TYPE;
713 v_person_id igs_as_msht_su_atmpt.person_id%TYPE;
714 v_course_cd igs_as_msht_su_atmpt.course_cd%TYPE;
715 v_unit_cd igs_as_msht_su_atmpt.unit_cd%TYPE;
716 v_cal_type igs_as_msht_su_atmpt.cal_type%TYPE;
717 v_ci_sequence_number igs_as_msht_su_atmpt.ci_sequence_number%TYPE;
718 v_unit_class igs_as_msht_su_atmpt.unit_class%TYPE;
719 v_student_sequence igs_as_msht_su_atmpt.student_sequence%TYPE;
720 v_update_on igs_as_msht_su_atmpt.last_update_date%TYPE;
721 v_update_who igs_as_msht_su_atmpt.last_updated_by%TYPE;
722 CURSOR c_uop (
723 cp_assess_cal_type IN igs_ca_inst.cal_type%TYPE,
724 cp_assess_sequence_number IN igs_ca_inst.sequence_number%TYPE,
725 cp_teach_cal_type IN igs_ps_unit_ofr_opt.cal_type%TYPE,
726 cp_teach_sequence_number IN igs_ps_unit_ofr_opt.ci_sequence_number%TYPE
727 ) IS
728 SELECT DISTINCT uop.unit_cd,
729 uop.version_number,
730 uop.cal_type,
731 uop.ci_sequence_number,
732 uop.location_cd,
733 ucl.unit_mode,
734 uop.uoo_id
735 FROM igs_ps_unit_ofr_opt uop,
736 igs_as_unit_class ucl,
737 igs_ca_inst ci,
738 igs_ps_unit_ver uv,
739 igs_ca_stat cs,
740 igs_ps_unit_stat us
741 WHERE uop.unit_cd LIKE p_unit_cd
742 AND uop.unit_cd = uv.unit_cd
743 AND uop.version_number = uv.version_number
744 AND uv.unit_status = us.unit_status
745 AND uv.owner_org_unit_cd LIKE NVL (p_unit_org_unit_cd, uv.owner_org_unit_cd)
746 AND us.s_unit_status = 'ACTIVE'
747 AND uop.location_cd LIKE NVL (p_location_cd, uop.location_cd)
748 AND ucl.unit_class = uop.unit_class
749 AND ucl.unit_mode LIKE NVL (p_unit_mode, ucl.unit_mode)
750 AND ucl.closed_ind = 'N'
751 AND uop.cal_type = ci.cal_type
752 AND uop.ci_sequence_number = ci.sequence_number
753 AND ci.cal_status = cs.cal_status
754 AND cs.s_cal_status = 'ACTIVE'
755 AND ((cp_assess_cal_type IS NULL
756 OR (cp_assess_cal_type IS NOT NULL
757 AND igs_en_gen_014.enrs_get_within_ci (
758 cp_assess_cal_type,
759 cp_assess_sequence_number,
760 uop.cal_type,
761 uop.ci_sequence_number,
762 'N'
763 ) = 'Y'
764 )
765 )
766 )
767 AND (cp_teach_cal_type IS NULL
768 OR (cp_teach_cal_type IS NOT NULL
769 AND uop.cal_type = cp_teach_cal_type
770 AND uop.ci_sequence_number = cp_teach_sequence_number
771 AND uop.call_number LIKE NVL (p_call_number, uop.call_number)
772 AND ucl.unit_class LIKE NVL (p_unit_class, ucl.unit_class)
773 )
774 );
775 CURSOR c_ms1 (
776 cp_uop_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
777 cp_uop_version_number IN igs_ps_unit_ofr_pat.version_number%TYPE,
778 cp_uop_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
779 cp_uop_ci_sequence_number IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE,
780 cp_ms_sequence_number IN igs_as_mark_sheet.sheet_number%TYPE
781 ) IS
782 SELECT sheet_number
783 FROM igs_as_mark_sheet ms
784 WHERE ms.unit_cd = cp_uop_unit_cd
785 AND ms.version_number = cp_uop_version_number
786 AND ms.cal_type = cp_uop_cal_type
787 AND ms.ci_sequence_number = cp_uop_ci_sequence_number
788 AND ms.sheet_number <> cp_ms_sequence_number
789 AND ms.grading_period_cd = p_grading_period_cd;
790 CURSOR c_mssua1 (cp_ms_sequence_number IN igs_as_mark_sheet.sheet_number%TYPE) IS
791 SELECT mssua.person_id
792 FROM igs_as_msht_su_atmpt mssua
793 WHERE mssua.sheet_number = cp_ms_sequence_number;
794 CURSOR c_mss (cp_ms_sequence_number IN igs_as_mark_sheet.sheet_number%TYPE) IS
795 SELECT mss.sheet_number
796 FROM igs_as_mark_sheet mss
797 WHERE mss.sheet_number = cp_ms_sequence_number
798 FOR UPDATE OF duplicate_ind NOWAIT;
799 CURSOR c_mssua2 (
800 cp_ms_mark_sheet IN igs_as_mark_sheet.sheet_number%TYPE,
801 cp_mssua1_person_id IN igs_as_msht_su_atmpt.person_id%TYPE
802 ) IS
803 SELECT sheet_number,
804 person_id,
805 course_cd,
806 unit_cd,
807 cal_type,
808 ci_sequence_number,
809 student_sequence,
810 unit_class,
811 last_update_date,
812 last_updated_by
813 FROM igs_as_msht_su_atmpt mssua2
814 WHERE mssua2.sheet_number = cp_ms_mark_sheet
815 AND mssua2.person_id = cp_mssua1_person_id;
816 CURSOR c_get_nxt_seq_no IS
817 SELECT igs_as_mark_sheet_grpseqnum_s.NEXTVAL
818 FROM DUAL;
819 CURSOR c_get_nxt_grp_seq_no IS
820 SELECT igs_as_mark_sheet_grpseqnum_s.NEXTVAL
821 FROM DUAL;
822 ----------------------------------------ASSPL_INS_MSSUA_SORT--------------------
823 -- procedure used to mainly insert student records (orderd by
824 -- PERSON_ID/SURNAME) into MSSUA
825 PROCEDURE asspl_ins_mssua_sort (
826 lp_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
827 lp_unit_mode IN igs_as_unit_class.unit_mode%TYPE,
828 lp_sort_by IN VARCHAR2,
829 lp_uop_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
830 lp_uop_version_number IN igs_ps_unit_ofr_pat.version_number%TYPE,
831 lp_uop_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
832 lp_uop_ci_sequence_number IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE,
833 lp_ms_sequence_number IN igs_as_mark_sheet.sheet_number%TYPE,
834 lp_group_sequence_number IN igs_as_mark_sheet.group_sequence_number%TYPE,
835 lp_uoo_id IN NUMBER
836 ) IS
837 -- select the students to be included (where p_assess_cal_type is specified)
838 -- in mark sheets and ORDER BY person_id
839 CURSOR c_sua_person_id (
840 cp_uop_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
841 cp_uop_version_number IN igs_ps_unit_ofr_pat.version_number%TYPE,
842 cp_uop_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
843 cp_uop_ci_sequence_number IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE,
844 cp_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
845 cp_unit_mode IN igs_as_unit_class.unit_mode%TYPE,
846 cp_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
847 ) IS
848 SELECT sua.person_id,
849 sua.course_cd,
850 sua.unit_cd,
851 sua.cal_type,
852 sua.ci_sequence_number,
853 sua.location_cd,
854 ucl.unit_mode,
855 sua.unit_class,
856 unit_attempt_status
857 FROM igs_en_su_attempt sua,
858 igs_as_unit_class ucl
859 WHERE ucl.unit_class = sua.unit_class
860 AND sua.uoo_id = cp_uoo_id
861 AND sua.location_cd = cp_location_cd
862 AND ucl.unit_mode = cp_unit_mode
863 AND ucl.closed_ind = 'N'
864 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DISCONTIN')
865 AND (p_grading_period_cd = 'FINAL'
866 OR (EXISTS ( SELECT 'x'
867 FROM igs_as_gpc_programs gpr
868 WHERE gpr.course_cd = sua.course_cd)
869 OR EXISTS ( SELECT 'x'
870 FROM igs_en_stdnt_ps_att sca,
871 igs_as_gpc_aca_stndg gas
872 WHERE sca.person_id = sua.person_id
873 AND sca.course_cd = sua.course_cd
874 AND sca.progression_status = gas.progression_status)
875 OR EXISTS ( SELECT 'x'
876 FROM igs_pe_prsid_grp_mem pigm,
877 igs_as_gpc_pe_id_grp gpg
878 WHERE sua.person_id = pigm.person_id
879 AND pigm.GROUP_ID = gpg.GROUP_ID)
880 OR EXISTS ( SELECT 'x'
881 FROM igs_as_gpc_cls_stndg gcs
882 WHERE gcs.class_standing =
883 igs_pr_get_class_std.get_class_standing (
884 sua.person_id,
885 sua.course_cd,
886 'N',
887 SYSDATE,
888 sua.cal_type,
889 sua.ci_sequence_number
890 ))
891 OR EXISTS ( SELECT 'x'
892 FROM igs_as_su_setatmpt iass,
893 igs_as_gpc_unit_sets gus
894 WHERE iass.person_id = sua.person_id
895 AND iass.course_cd = sua.course_cd
896 AND iass.unit_set_cd = gus.unit_set_cd)
897 )
898 )
899 ORDER BY sua.person_id;
900 --
901 -- select the students to be included (where p_assess_cal_type is specified)
902 -- in mark sheets and ORDER BY pe.person_name, person_id
903 --
904 CURSOR c_sua_surname (
905 cp_uop_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
906 cp_uop_version_number IN igs_ps_unit_ofr_pat.version_number%TYPE,
907 cp_uop_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
908 cp_uop_ci_sequence_number IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE,
909 cp_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
910 cp_unit_mode IN igs_as_unit_class.unit_mode%TYPE,
911 cp_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
912 ) IS
913 SELECT pe.person_last_name surname,
914 sua.person_id,
915 sua.course_cd,
916 sua.unit_cd,
917 sua.cal_type,
918 sua.ci_sequence_number,
919 sua.location_cd,
920 ucl.unit_mode,
921 sua.unit_class,
922 unit_attempt_status
923 FROM igs_en_su_attempt sua,
924 igs_as_unit_class ucl,
925 hz_parties pe
926 WHERE sua.person_id = pe.party_id
927 AND ucl.unit_class = sua.unit_class
928 AND ucl.closed_ind = 'N'
929 AND sua.uoo_id = cp_uoo_id
930 AND sua.location_cd = cp_location_cd
931 AND ucl.unit_mode = cp_unit_mode
932 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DISCONTIN')
933 AND (p_grading_period_cd = 'FINAL'
934 OR (EXISTS ( SELECT 'x'
935 FROM igs_as_gpc_programs gpr
936 WHERE gpr.course_cd = sua.course_cd)
937 OR EXISTS ( SELECT 'x'
938 FROM igs_en_stdnt_ps_att sca,
939 igs_as_gpc_aca_stndg gas
940 WHERE sca.person_id = sua.person_id
941 AND sca.course_cd = sua.course_cd
942 AND sca.progression_status = gas.progression_status)
943 OR EXISTS ( SELECT 'x'
944 FROM igs_pe_prsid_grp_mem pigm,
945 igs_as_gpc_pe_id_grp gpg
946 WHERE sua.person_id = pigm.person_id
947 AND pigm.GROUP_ID = gpg.GROUP_ID)
948 OR EXISTS ( SELECT 'x'
949 FROM igs_as_gpc_cls_stndg gcs
950 WHERE gcs.class_standing =
951 igs_pr_get_class_std.get_class_standing (
952 sua.person_id,
953 sua.course_cd,
954 'N',
955 SYSDATE,
956 sua.cal_type,
957 sua.ci_sequence_number
958 ))
959 OR EXISTS ( SELECT 'x'
960 FROM igs_as_su_setatmpt iass,
961 igs_as_gpc_unit_sets gus
962 WHERE iass.person_id = sua.person_id
963 AND iass.course_cd = sua.course_cd
964 AND iass.unit_set_cd = gus.unit_set_cd)
965 )
966 )
967 ORDER BY pe.person_last_name,
968 sua.person_id;
969 --
970 v_stdnt_seq NUMBER (10) := 0;
971 BEGIN
972 IF p_sort_by = 'ID' THEN
973 FOR v_sua_person_id_rec IN c_sua_person_id (
974 lp_uop_unit_cd,
975 lp_uop_version_number,
976 lp_uop_cal_type,
977 lp_uop_ci_sequence_number,
978 lp_location_cd,
979 lp_unit_mode,
980 lp_uoo_id
981 ) LOOP
982 -- If parameter says not to include discontinued students, then exclude and
983 -- go to the next student and insert records into mark_sheet_stnd_unit_atmpt
984 IF NOT ((p_include_discont_ind = 'N')
985 AND (v_sua_person_id_rec.unit_attempt_status = 'DISCONTIN')
986 ) THEN
987 v_stdnt_seq := v_stdnt_seq + 1;
988 DECLARE
989 l_rowid1 VARCHAR2 (25);
990 BEGIN
991 igs_as_msht_su_atmpt_pkg.insert_row (
992 x_mode => 'R',
993 x_rowid => l_rowid1,
994 x_sheet_number => v_ms_sequence_number,
995 x_person_id => v_sua_person_id_rec.person_id,
996 x_course_cd => v_sua_person_id_rec.course_cd,
997 x_unit_cd => v_sua_person_id_rec.unit_cd,
998 x_cal_type => v_sua_person_id_rec.cal_type,
999 x_ci_sequence_number => v_sua_person_id_rec.ci_sequence_number,
1000 x_location_cd => v_sua_person_id_rec.location_cd,
1001 x_unit_mode => v_sua_person_id_rec.unit_mode,
1002 x_unit_class => v_sua_person_id_rec.unit_class,
1003 x_student_sequence => v_stdnt_seq,
1004 x_uoo_id => lp_uoo_id
1005 );
1006 END;
1007 END IF;
1008 END LOOP;
1009 ELSIF p_sort_by = 'SURNAME' THEN
1010 FOR v_sua_surname_rec IN c_sua_surname (
1011 lp_uop_unit_cd,
1012 lp_uop_version_number,
1013 lp_uop_cal_type,
1014 lp_uop_ci_sequence_number,
1015 lp_location_cd,
1016 lp_unit_mode,
1017 lp_uoo_id
1018 ) LOOP
1019 IF NOT (p_include_discont_ind = 'N'
1020 AND v_sua_surname_rec.unit_attempt_status = 'DISCONTIN'
1021 ) THEN
1022 v_stdnt_seq := v_stdnt_seq + 1;
1023 DECLARE
1024 l_rowid4 VARCHAR2 (25);
1025 BEGIN
1026 igs_as_msht_su_atmpt_pkg.insert_row (
1027 x_mode => 'R',
1028 x_rowid => l_rowid4,
1029 x_sheet_number => v_ms_sequence_number,
1030 x_person_id => v_sua_surname_rec.person_id,
1031 x_course_cd => v_sua_surname_rec.course_cd,
1032 x_unit_cd => v_sua_surname_rec.unit_cd,
1033 x_cal_type => v_sua_surname_rec.cal_type,
1034 x_ci_sequence_number => v_sua_surname_rec.ci_sequence_number,
1035 x_location_cd => v_sua_surname_rec.location_cd,
1036 x_unit_mode => v_sua_surname_rec.unit_mode,
1037 x_unit_class => v_sua_surname_rec.unit_class,
1038 x_student_sequence => v_stdnt_seq,
1039 x_uoo_id => lp_uoo_id
1040 );
1041 END;
1042 END IF;
1043 END LOOP; -- v_sua_surname_rec
1044 END IF;
1045 END asspl_ins_mssua_sort;
1046 ----------------------------------------------- MAIN ---------------------------
1047 BEGIN
1048 IF p_assess_cal_type IS NOT NULL THEN
1049 IF p_assess_sequence_number IS NULL THEN
1050 p_group_sequence_number := NULL;
1051 RETURN FALSE;
1052 END IF;
1053 ELSIF p_assess_sequence_number IS NOT NULL THEN
1054 p_group_sequence_number := NULL;
1055 RETURN FALSE;
1056 END IF;
1057 IF p_teach_cal_type IS NOT NULL THEN
1058 IF p_teach_sequence_number IS NULL THEN
1059 p_group_sequence_number := NULL;
1060 RETURN FALSE;
1061 END IF;
1062 ELSIF p_teach_sequence_number IS NOT NULL THEN
1063 p_group_sequence_number := NULL;
1064 RETURN FALSE;
1065 END IF;
1066 IF (p_unit_cd IS NULL)
1067 OR (p_include_discont_ind NOT IN ('Y', 'N'))
1068 OR (p_sort_by NOT IN ('ID', 'SURNAME')) THEN
1069 p_group_sequence_number := NULL;
1070 RETURN FALSE;
1071 END IF;
1072 -- get the next IGS_AS_MARK_SHEET.group_sequence_number for the new mark sheet
1073 OPEN c_get_nxt_grp_seq_no;
1074 FETCH c_get_nxt_grp_seq_no INTO v_group_sequence_number;
1075 CLOSE c_get_nxt_grp_seq_no;
1076 -- select the mark sheets to produce based on the input parameters
1077 FOR v_uop_rec IN c_uop (p_assess_cal_type, p_assess_sequence_number, p_teach_cal_type, p_teach_sequence_number) LOOP
1078 -- store IGS_AS_MARK_SHEET.sheet_number and create mark sheet record
1079 OPEN c_get_nxt_seq_no;
1080 FETCH c_get_nxt_seq_no INTO v_ms_sequence_number;
1081 CLOSE c_get_nxt_seq_no;
1082 DECLARE
1083 l_rowid6 VARCHAR2 (25);
1084 l_org_id NUMBER (15);
1085 BEGIN
1086 -- get org_id
1087 l_org_id := igs_ge_gen_003.get_org_id;
1088 igs_as_mark_sheet_pkg.insert_row (
1089 x_mode => 'R',
1090 x_rowid => l_rowid6,
1091 x_org_id => l_org_id,
1092 x_sheet_number => v_ms_sequence_number,
1093 x_group_sequence_number => v_group_sequence_number,
1094 x_unit_cd => v_uop_rec.unit_cd,
1095 x_version_number => v_uop_rec.version_number,
1096 x_cal_type => v_uop_rec.cal_type,
1097 x_ci_sequence_number => v_uop_rec.ci_sequence_number,
1098 x_location_cd => v_uop_rec.location_cd,
1099 x_unit_mode => v_uop_rec.unit_mode,
1100 x_production_dt => SYSDATE,
1101 x_duplicate_ind => NULL,
1102 x_grading_period_cd => p_grading_period_cd
1103 );
1104 END;
1105 v_ins_ms := TRUE;
1106 -- select students to be included in mark sheets and insert student details in
1107 -- to MSSUA
1108 asspl_ins_mssua_sort (
1109 v_uop_rec.location_cd,
1110 v_uop_rec.unit_mode,
1111 p_sort_by,
1112 v_uop_rec.unit_cd,
1113 v_uop_rec.version_number,
1114 v_uop_rec.cal_type,
1115 v_uop_rec.ci_sequence_number,
1116 v_ms_sequence_number,
1117 v_group_sequence_number,
1118 v_uop_rec.uoo_id
1119 );
1120 -- Check if the sheet is not a duplicate of another mark sheet
1121 v_duplicate_sheet := FALSE;
1122 FOR v_ms_rec IN c_ms1 (
1123 v_uop_rec.unit_cd,
1124 v_uop_rec.version_number,
1125 v_uop_rec.cal_type,
1126 v_uop_rec.ci_sequence_number,
1127 v_ms_sequence_number
1128 ) LOOP
1129 FOR v_mssua1_rec IN c_mssua1 (v_ms_sequence_number) LOOP
1130 v_not_duplicate := FALSE;
1131 OPEN c_mssua2 (v_ms_rec.sheet_number, v_mssua1_rec.person_id);
1132 FETCH c_mssua2 INTO v_sheet_number,
1133 v_person_id,
1134 v_course_cd,
1135 v_unit_cd,
1136 v_cal_type,
1137 v_ci_sequence_number,
1138 v_student_sequence,
1139 v_unit_class,
1140 v_update_on,
1141 v_update_who;
1142
1143 IF (c_mssua2%NOTFOUND) THEN
1144 v_not_duplicate := TRUE;
1145 END IF;
1146 CLOSE c_mssua2;
1147 EXIT;
1148 END LOOP;
1149 IF v_not_duplicate = FALSE THEN
1150 v_duplicate_sheet := TRUE;
1151 -- open cursor in which the update of IGS_AS_MARK_SHEET.duplicate_ind is based on
1152 -- if table is busy, update will be abandoned without waiting (NO_WAIT)
1153 OPEN c_mss (v_ms_sequence_number);
1154 FETCH c_mss INTO v_mss_mark_sheet;
1155 UPDATE igs_as_mark_sheet_all
1156 SET duplicate_ind = 'Y'
1157 WHERE CURRENT OF c_mss;
1158 CLOSE c_mss;
1159 EXIT;
1160 END IF;
1161 END LOOP;
1162 END LOOP;
1163 IF v_ins_ms = TRUE THEN
1164 -- Delete sheets which were created but have no students.
1165 DELETE igs_as_mark_sheet_all
1166 WHERE group_sequence_number = v_group_sequence_number
1167 AND NOT EXISTS ( SELECT sheet_number
1168 FROM igs_as_msht_su_atmpt
1169 WHERE sheet_number = igs_as_mark_sheet_all.sheet_number);
1170 p_group_sequence_number := v_group_sequence_number;
1171 RETURN TRUE;
1172 ELSE
1173 p_group_sequence_number := NULL;
1174 RETURN FALSE;
1175 END IF;
1176 EXCEPTION
1177 WHEN e_resource_busy THEN
1178 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_LOCKED');
1179 igs_ge_msg_stack.ADD;
1180 app_exception.raise_exception;
1181 RETURN FALSE;
1182 WHEN OTHERS THEN
1183 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1184 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_mark_sheet_INNER');
1185 igs_ge_msg_stack.ADD;
1186 app_exception.raise_exception;
1187 END;
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1191 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_mark_sheet');
1192 igs_ge_msg_stack.ADD;
1193 app_exception.raise_exception;
1194 END assp_ins_mark_sheet;
1195 --
1196 --
1197 --
1198 FUNCTION assp_ins_scap_lovall (
1199 p_person_id IN igs_as_spl_cons_appl.person_id%TYPE,
1200 p_course_cd IN igs_as_spl_cons_appl.course_cd%TYPE,
1201 p_unit_cd IN igs_as_spl_cons_appl.unit_cd%TYPE,
1202 p_cal_type IN igs_as_spl_cons_appl.cal_type%TYPE,
1203 p_ci_sequence_number IN NUMBER,
1204 p_received_dt IN DATE,
1205 p_spcl_consideration_cat IN VARCHAR2,
1206 p_estimated_processing_days IN NUMBER,
1207 p_sought_outcome IN VARCHAR2,
1208 p_message_name OUT NOCOPY VARCHAR2,
1209 p_uoo_id IN NUMBER,
1210 p_notified_date IN DATE
1211 ) RETURN BOOLEAN IS
1212 gv_other_detail VARCHAR2 (255);
1213 BEGIN -- assp_ins_scap_lovall
1214 -- This procedure is responsible for copying all of the assessment
1215 -- items for a Student IGS_PS_UNIT Attempt to the Special Consideration
1216 -- Application table.
1217 DECLARE
1218 v_ass_id igs_as_su_atmpt_itm.ass_id%TYPE;
1219 v_creation_dt igs_as_su_atmpt_itm.creation_dt%TYPE;
1220 v_spcl_consideration_cat igs_as_spcl_cons_cat.spcl_consideration_cat%TYPE;
1221 v_static_sysdate igs_as_spl_cons_appl.received_dt%TYPE;
1222 v_ins_fail_flag BOOLEAN DEFAULT FALSE;
1223 v_scap_exists_flag BOOLEAN DEFAULT FALSE;
1224 v_scap_error_flag BOOLEAN DEFAULT FALSE;
1225 v_suaai_error_flag BOOLEAN DEFAULT FALSE;
1226 v_select_ktr NUMBER DEFAULT 0;
1227 v_insert_ktr NUMBER DEFAULT 0;
1228 v_message_name VARCHAR2 (30) DEFAULT NULL;
1229 CURSOR c_suaai IS
1230 SELECT suaai.ass_id,
1231 suaai.creation_dt
1232 FROM igs_as_su_atmpt_itm suaai
1233 WHERE suaai.person_id = p_person_id
1234 AND suaai.course_cd = p_course_cd
1235 AND suaai.uoo_id = p_uoo_id
1236 AND suaai.logical_delete_dt IS NULL
1237 AND suaai.creation_dt = (SELECT MAX (suaai2.creation_dt)
1238 FROM igs_as_su_atmpt_itm suaai2
1239 WHERE suaai2.person_id = suaai.person_id
1240 AND suaai2.course_cd = suaai.course_cd
1241 AND suaai2.uoo_id = suaai.uoo_id
1242 AND suaai2.logical_delete_dt IS NULL
1243 AND suaai2.ass_id = suaai.ass_id);
1244 CURSOR c_scap (cp_ass_id igs_as_spl_cons_appl.ass_id%TYPE) IS
1245 SELECT ass_id
1246 FROM igs_as_spl_cons_appl
1247 WHERE person_id = p_person_id
1248 AND course_cd = p_course_cd
1249 AND uoo_id = p_uoo_id
1250 AND ass_id = cp_ass_id;
1251 BEGIN
1252 -- Initialise message number and variable
1253 p_message_name := NULL;
1254 v_static_sysdate := SYSDATE;
1255 OPEN c_suaai;
1256 FETCH c_suaai INTO v_ass_id,
1257 v_creation_dt;
1258 IF (c_suaai%NOTFOUND) THEN
1259 -- SUAAI do not exist
1260 CLOSE c_suaai;
1261 p_message_name := 'IGS_AS_ASSITEM_DOESNOT_EXISTS';
1262 RETURN FALSE;
1263 END IF;
1264 CLOSE c_suaai;
1265 FOR v_suaai_rec IN c_suaai LOOP
1266 v_select_ktr := v_select_ktr + 1;
1267 -- Check to see if ass item already has a special consid applic
1268 OPEN c_scap (v_suaai_rec.ass_id);
1269 FETCH c_scap INTO v_ass_id;
1270 IF (c_scap%FOUND) THEN
1271 v_scap_exists_flag := TRUE;
1272 ELSE
1273 v_scap_exists_flag := FALSE;
1274 END IF;
1275 CLOSE c_scap;
1276 IF (igs_as_val_scap.assp_val_suaai_ins (
1277 p_person_id,
1278 p_course_cd,
1279 p_unit_cd,
1280 p_cal_type,
1281 p_ci_sequence_number,
1282 v_suaai_rec.ass_id,
1283 v_message_name,
1284 p_uoo_id
1285 ) = FALSE
1286 AND v_message_name <> 'IGS_AS_SUA_STATUS_INVALID_COM'
1287 ) THEN
1288 -- Do not perform insert
1289 IF v_message_name = 'IGS_AS_SUA_STATUS_INVALID' THEN
1290 -- SUA IGS_PS_UNIT status is invalid
1291 p_message_name := 'IGS_AS_SPLAPPL_NC_ASSITEM_SUA';
1292 RETURN FALSE;
1293 ELSE
1294 -- SUA ass item is invalid
1295 v_suaai_error_flag := TRUE;
1296 END IF;
1297 ELSE
1298 IF v_scap_exists_flag = TRUE THEN
1299 -- Do not perform insert
1300 -- SCAP already exist for ass item
1301 v_scap_error_flag := TRUE;
1302 ELSE
1303 DECLARE
1304 l_rowid7 VARCHAR (25);
1305 BEGIN
1306 igs_as_spl_cons_appl_pkg.insert_row (
1307 x_mode => 'R',
1308 x_rowid => l_rowid7,
1309 x_person_id => p_person_id,
1310 x_course_cd => p_course_cd,
1311 x_unit_cd => p_unit_cd,
1312 x_cal_type => p_cal_type,
1313 x_ci_sequence_number => p_ci_sequence_number,
1314 x_ass_id => v_suaai_rec.ass_id,
1315 x_creation_dt => v_suaai_rec.creation_dt,
1316 x_received_dt => p_received_dt,
1317 x_spcl_consideration_cat => p_spcl_consideration_cat,
1318 x_sought_outcome => p_sought_outcome,
1319 x_spcl_consideration_outcome => NULL,
1320 x_tracking_id => NULL,
1321 x_estimated_processing_days => p_estimated_processing_days,
1322 x_comments => 'Special consideration application for'
1323 || ' all assessment items for the unit attempt.',
1324 x_uoo_id => p_uoo_id,
1325 x_notified_date => p_notified_date
1326 );
1327 v_insert_ktr := v_insert_ktr + 1;
1328 END;
1329 END IF;
1330 END IF;
1331 END LOOP;
1332 IF v_insert_ktr = 0 THEN
1333 IF v_scap_error_flag THEN
1334 IF v_suaai_error_flag THEN
1335 p_message_name := 'IGS_AS_SPLAPPL_NC_ASSITEM_INV';
1336 RETURN FALSE;
1337 ELSE
1338 p_message_name := 'IGS_AS_SPLAPPL_NC_ASSITEM_AI';
1339 RETURN FALSE;
1340 END IF;
1341 ELSE
1342 IF v_suaai_error_flag THEN
1343 p_message_name := 'IGS_AS_SPLAPPL_NC_AI_INVALID';
1344 RETURN FALSE;
1345 ELSE
1346 p_message_name := 'IGS_AS_SPLAPPL_NC_INVESTIGATI';
1347 RETURN FALSE;
1348 END IF;
1349 END IF;
1350 END IF;
1351 IF v_insert_ktr < v_select_ktr THEN
1352 IF v_scap_error_flag THEN
1353 IF v_suaai_error_flag THEN
1354 p_message_name := 'IGS_AS_SPLAPPL_NC_APPL_EXISTS';
1355 RETURN FALSE;
1356 ELSE
1357 p_message_name := 'IGS_AS_SPLAPPL_NC_APPL_EXIST';
1358 RETURN FALSE;
1359 END IF;
1360 ELSE
1361 IF v_suaai_error_flag THEN
1362 p_message_name := 'IGS_AS_SPLAPPL_NC_ASI_INVALID';
1363 RETURN FALSE;
1364 ELSE
1365 p_message_name := 'IGS_AS_SPLCONS_APPL_NOT_CREAT';
1366 RETURN FALSE;
1367 END IF;
1368 END IF;
1369 END IF;
1370 RETURN TRUE;
1371 END;
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1375 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_scap_lovall');
1376 igs_ge_msg_stack.ADD;
1377 app_exception.raise_exception;
1378 END assp_ins_scap_lovall;
1379 --
1380 -- This routine will insert default Assessment Items for the student or update
1381 -- the changed setup for Assessment Items
1382 --
1383 FUNCTION assp_ins_suaai_dflt (
1384 p_person_id IN NUMBER,
1385 p_course_cd IN VARCHAR2,
1386 p_unit_cd IN VARCHAR2,
1387 p_version_number IN NUMBER,
1388 p_cal_type IN VARCHAR2,
1389 p_ci_sequence_number IN NUMBER,
1390 p_location_cd IN VARCHAR2,
1391 p_unit_class IN VARCHAR2,
1392 p_ass_id IN NUMBER,
1393 p_ass_pattern_id IN NUMBER,
1394 p_ass_id_usec_unit_ind IN VARCHAR2 DEFAULT 'UNIT', -- Added by DDEY as a part of enhancement Bug # 2162831
1395 p_creation_dt IN DATE,
1396 p_s_log_type IN VARCHAR2,
1397 p_key IN VARCHAR2,
1398 p_sle_key IN VARCHAR2,
1399 p_error_count IN OUT NOCOPY NUMBER,
1400 p_warning_count IN OUT NOCOPY NUMBER,
1401 p_message_name OUT NOCOPY VARCHAR2,
1402 p_ass_item_id IN NUMBER ,
1403 p_group_id IN NUMBER,
1404 p_midterm_mandatory_type_code IN VARCHAR2,
1405 p_midterm_weight_qty IN NUMBER ,
1406 p_final_mandatory_type_code IN VARCHAR2,
1407 p_final_weight_qty IN NUMBER,
1408 p_grading_schema_cd IN VARCHAR2,
1409 p_gs_version_number IN NUMBER,
1410 p_uoo_id IN NUMBER
1411 ) RETURN BOOLEAN IS
1412 gv_other_detail VARCHAR2 (255);
1413 BEGIN
1414 --
1415 DECLARE
1416 cst_yes CONSTANT CHAR := 'Y';
1417 cst_enrolled CONSTANT VARCHAR2 (10) := 'ENROLLED';
1418 cst_pattern CONSTANT VARCHAR2 (10) := 'PATTERN';
1419 cst_item CONSTANT VARCHAR2 (10) := 'ITEM';
1420 e_resource_busy EXCEPTION;
1421 v_dummy VARCHAR2 (1);
1422 v_level VARCHAR2 (10);
1423 v_message_name VARCHAR2 (30);
1424 v_attempt_number igs_as_su_atmpt_itm.attempt_number%TYPE;
1425 v_course_type igs_ps_ver.course_type%TYPE;
1426 v_creation_dt DATE;
1427 l_update_flag VARCHAR2 (20) := 'FALSE';
1428 -- l_grading_schema_cd igs_ps_unitass_item.grading_schema_cd%TYPE;
1429 -- l_gs_version_number igs_ps_unitass_item.gs_version_number%TYPE;
1430 --
1431 -- Get the Unit Section ID
1432 --
1433 /* CURSOR cur_uoo_id IS
1434 SELECT uoo_id
1435 FROM igs_ps_unit_ofr_opt
1436 WHERE unit_cd = p_unit_cd
1437 AND version_number = p_version_number
1438 AND cal_type = p_cal_type
1439 AND ci_sequence_number = p_ci_sequence_number
1440 AND location_cd = p_location_cd
1441 AND unit_class = p_unit_class;*/
1442 --
1443 -- rec_uoo_id cur_uoo_id%ROWTYPE;
1444 --
1445 -- For checking the unit status i.e., it should be ENROLLED.
1446 --
1447 CURSOR c_sua_status (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1448 SELECT 'X'
1449 FROM igs_en_su_attempt sua
1450 WHERE sua.person_id = p_person_id
1451 AND sua.course_cd = p_course_cd
1452 AND sua.uoo_id = cp_uoo_id
1453 AND sua.unit_attempt_status = cst_enrolled;
1454 --
1455 sua_status_rec c_sua_status%ROWTYPE;
1456 --
1457 -- For checking if the unit assessment item is MANUALLY delete or not.
1458 --
1459 CURSOR c_suaai_deleted (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1460 SELECT 'x'
1461 FROM igs_as_su_atmpt_itm suaai
1462 WHERE suaai.person_id = p_person_id
1463 AND suaai.course_cd = p_course_cd
1464 AND suaai.uoo_id = cp_uoo_id
1465 AND suaai.ass_id = p_ass_id
1466 AND suaai.logical_delete_dt IS NOT NULL
1467 AND suaai.s_default_ind = 'N'
1468 AND NOT EXISTS ( SELECT 'x'
1469 FROM igs_as_su_atmpt_itm suaai
1470 WHERE suaai.person_id = p_person_id
1471 AND suaai.course_cd = p_course_cd
1472 AND suaai.uoo_id = cp_uoo_id
1473 AND suaai.ass_id = p_ass_id
1474 AND suaai.logical_delete_dt IS NULL);
1475 --
1476 CURSOR c_crv IS
1477 SELECT crv.course_type
1478 FROM igs_en_stdnt_ps_att sca,
1479 igs_ps_ver crv
1480 WHERE sca.person_id = p_person_id
1481 AND sca.course_cd = p_course_cd
1482 AND sca.course_cd = crv.course_cd
1483 AND sca.version_number = crv.version_number;
1484 --
1485 --
1486 --
1487 CURSOR c_suaai_an (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1488 SELECT NVL (MAX (suaai.attempt_number), 0) + 1
1489 FROM igs_as_su_atmpt_itm suaai
1490 WHERE suaai.person_id = p_person_id
1491 AND suaai.course_cd = p_course_cd
1492 AND suaai.uoo_id = cp_uoo_id
1493 AND suaai.ass_id = p_ass_id
1494 AND suaai.logical_delete_dt IS NULL;
1495 --
1496 -- Added by DDEY as a part of enhancement Bug # 2162831
1497 -- This cursor is declared to fetch the Assessment Items which are are attached to the
1498 -- Assessment Pattern , but already logically deleted. This would enable the logical deleted
1499 -- record to be back in the system.
1500 --
1501 CURSOR c_suaai_upd (cp_ass_id igs_ps_unitass_item.ass_id%TYPE, cp_uoo_id igs_en_su_attempt.uoo_id%TYPE , cp_ass_item_id igs_as_su_atmpt_itm.unit_section_ass_item_id%TYPE ) IS
1502 SELECT suaai.ROWID,
1503 suaai.*,
1504 sag.unit_ass_item_group_id,
1505 sag.us_ass_item_group_id
1506 FROM igs_as_su_atmpt_itm suaai,
1507 igs_as_sua_ai_group sag
1508 WHERE suaai.person_id = p_person_id
1509 AND suaai.course_cd = p_course_cd
1510 AND suaai.uoo_id = cp_uoo_id
1511 AND suaai.ass_id = cp_ass_id
1512 AND (suaai.unit_section_ass_item_id = cp_ass_item_id OR suaai.UNIT_ASS_ITEM_ID = cp_ass_item_id)
1513 AND suaai.sua_ass_item_group_id = sag.sua_ass_item_group_id
1514 /* AND NOT EXISTS (
1515 SELECT 'X'
1516 FROM igs_as_su_atmpt_itm suaai1
1517 WHERE suaai1.person_id = suaai.person_id
1518 AND suaai1.course_cd = suaai.course_cd
1519 AND suaai1.uoo_id = suaai.uoo_id
1520 AND suaai1.unit_section_ass_item_id IS NOT NULL
1521 AND p_ass_id_usec_unit_ind = 'UNIT'
1522 ) */;
1523 --
1524 suaai_upd_rec c_suaai_upd%ROWTYPE;
1525 --
1526 BEGIN
1527 --
1528 -- Set the default values
1529 --
1530 p_message_name := NULL;
1531 v_level := cst_item;
1532 p_error_count := 0;
1533 p_warning_count := 0;
1534 /* OPEN cur_uoo_id;
1535 FETCH cur_uoo_id INTO rec_uoo_id;
1536 CLOSE cur_uoo_id;
1537 */ --
1538 -- Check the status of the student unit attempt. Assessment items will only be
1539 -- assigned if the status is 'ENROLLED'. The status may have been updated
1540 -- since the triggering action for this process was created (IGS_PE_STD_TODO).
1541 --
1542 OPEN c_sua_status (p_uoo_id);
1543 FETCH c_sua_status INTO sua_status_rec;
1544 IF c_sua_status%NOTFOUND THEN
1545 CLOSE c_sua_status;
1546 p_message_name := 'IGS_AS_SUA_STATUS_INVALID_ENR';
1547 RETURN FALSE;
1548 END IF;
1549 CLOSE c_sua_status;
1550 --
1551 -- Validate that the Assessment Item has not been manually deleted previously
1552 -- (ie. s_default_ind = 'N'). If such occurance has happened, then do not add
1553 -- the item to the student again.
1554 --
1555 OPEN c_suaai_deleted (p_uoo_id);
1556 FETCH c_suaai_deleted INTO v_dummy;
1557 IF c_suaai_deleted%FOUND THEN
1558 CLOSE c_suaai_deleted;
1559 --
1560 -- Log warning that the item has not been added as has been previously deleted
1561 --
1562 igs_ge_ins_sle.genp_set_log_entry (
1563 p_s_log_type,
1564 p_key,
1565 p_sle_key,
1566 'IGS_AS_CANNOT_CREATE_DFLT_AI', -- Cannot create item as it has previously been deleted
1567 'WARNING|' || v_level || '|' || TO_CHAR (NULL) || '|' || TO_CHAR (p_ass_id)
1568 );
1569 p_warning_count := p_warning_count + 1;
1570 p_message_name := 'IGS_AS_CANNOT_CREATE_DFLT_AI';
1571 RETURN FALSE;
1572 END IF;
1573 CLOSE c_suaai_deleted;
1574 --
1575 -- If the assessment item is valid then check if any course type restrictions.
1576 --
1577 OPEN c_crv;
1578 FETCH c_crv INTO v_course_type;
1579 CLOSE c_crv;
1580 --
1581 IF igs_as_val_suaai.assp_val_ai_acot (
1582 p_ass_id,
1583 v_course_type,
1584 v_message_name
1585 ) = FALSE THEN
1586 -- Log warning that there exists a course restriction
1587 --
1588 igs_ge_ins_sle.genp_set_log_entry (
1589 p_s_log_type,
1590 p_key,
1591 p_sle_key,
1592 v_message_name, -- Warn item cannot be added due to course restriction
1593 -- against the item
1594 'WARNING|' || v_level || '|' || TO_CHAR (NULL) || '|' || TO_CHAR (p_ass_id)
1595 );
1596 p_warning_count := p_warning_count + 1;
1597 p_message_name := v_message_name;
1598 RETURN FALSE;
1599 END IF;
1600 --
1601 IF p_creation_dt IS NULL THEN
1602 v_creation_dt := SYSDATE;
1603 ELSE
1604 v_creation_dt := p_creation_dt;
1605 END IF;
1606 --
1607 -- Insert/Update the Assessment Item for the Student Unit Attempt
1608 --
1609 DECLARE
1610 l_rowid8 VARCHAR2 (25);
1611
1612 -- Bug # 3749413
1613 --
1614 -- Start of new code added as per Bug# 2829291;
1615 -- Check if the Assessment Item is attached from the Unit Section Level.
1616 --
1617 /* CURSOR cur_c1 (
1618 cp_person_id NUMBER,
1619 cp_ass_id NUMBER,
1620 cp_course_cd VARCHAR2,
1621 cp_uoo_id NUMBER,
1622 cp_group_id NUMBER
1623 ) IS
1624 SELECT suv.unit_section_ass_item_id,
1625 suv.us_ass_item_group_id,
1626 suv.midterm_mandatory_type_code,
1627 suv.midterm_weight_qty,
1628 suv.final_mandatory_type_code,
1629 suv.final_weight_qty,
1630 suv.grading_schema_cd,
1631 suv.gs_version_number,
1632 usaig.group_name
1633 FROM igs_as_usecai_sua_v suv,
1634 igs_as_us_ai_group usaig
1635 WHERE suv.person_id = cp_person_id
1636 AND suv.ass_id = cp_ass_id
1637 AND suv.course_cd = cp_course_cd
1638 AND suv.uoo_id = cp_uoo_id
1639 AND usaig.us_ass_item_group_id = cp_group_id
1640 AND suv.us_ass_item_group_id = usaig.us_ass_item_group_id;
1641 */
1642
1643
1644 -- rec_c1 cur_c1%ROWTYPE;
1645
1646 --
1647 -- Check if the Assessment Item is attached from the Unit Level.
1648 --
1649 /*
1650 CURSOR cur_c2 (
1651 cp_person_id NUMBER,
1652 cp_ass_id NUMBER,
1653 cp_course_cd VARCHAR2,
1654 cp_uoo_id NUMBER,
1655 cp_group_id NUMBER
1656 ) IS
1657 SELECT suv.unit_ass_item_id,
1658 suv.unit_ass_item_group_id,
1659 suv.midterm_mandatory_type_code,
1660 suv.midterm_weight_qty,
1661 suv.final_mandatory_type_code,
1662 suv.final_weight_qty,
1663 suv.grading_schema_cd,
1664 suv.gs_version_number,
1665 uaig.group_name
1666 FROM igs_as_uai_sua_v suv,
1667 igs_as_unit_ai_grp uaig
1668 WHERE suv.person_id = cp_person_id
1669 AND suv.ass_id = cp_ass_id
1670 AND suv.course_cd = cp_course_cd
1671 AND suv.uoo_id = cp_uoo_id
1672 AND uaig.unit_ass_item_group_id = cp_group_id
1673 AND suv.unit_ass_item_group_id = uaig.unit_ass_item_group_id; */
1674 --
1675 -- rec_c2 cur_c2%ROWTYPE;
1676
1677
1678 -- Bug # 3749413
1679
1680 --
1681 -- Check if there are any Unit Section Assessment Items which are active
1682 --
1683 CURSOR cur_usec_ass_items_exist (
1684 cp_uoo_id NUMBER
1685 ) IS
1686 SELECT 'Y' ass_item_exists
1687 FROM igs_ps_unitass_item
1688 WHERE uoo_id = cp_uoo_id
1689 AND logical_delete_dt IS NULL;
1690 --
1691 rec_usec_ass_items_exist cur_usec_ass_items_exist%ROWTYPE;
1692 --
1693 -- Get all the Student Unit Attempt Assessment Items that are attached
1694 -- from Unit level
1695 --
1696 CURSOR cur_suaai_from_unit (
1697 cp_person_id NUMBER,
1698 cp_course_cd VARCHAR2,
1699 cp_uoo_id NUMBER
1700 ) IS
1701 SELECT suaai.ROWID,
1702 suaai.*
1703 FROM igs_as_su_atmpt_itm suaai
1704 WHERE person_id = cp_person_id
1705 AND course_cd = cp_course_cd
1706 AND uoo_id = cp_uoo_id
1707 AND unit_ass_item_id IS NOT NULL
1708 AND logical_delete_dt IS NULL;
1709 --
1710 -- Unit Section Assessment Item Group Details
1711 --
1712 CURSOR cur_usec_aig (
1713 cp_us_ass_item_group_id IN NUMBER
1714 ) IS
1715 SELECT usaig.*
1716 FROM igs_as_us_ai_group usaig
1717 WHERE usaig.us_ass_item_group_id = cp_us_ass_item_group_id;
1718 --
1719 rec_usec_aig cur_usec_aig%ROWTYPE;
1720 --
1721 -- Unit Assessment Item Group Details
1722 --
1723 CURSOR cur_unit_aig (
1724 cp_unit_ass_item_group_id IN NUMBER
1725 ) IS
1726 SELECT uaig.*
1727 FROM igs_as_unit_ai_grp uaig
1728 WHERE uaig.unit_ass_item_group_id = cp_unit_ass_item_group_id;
1729 --
1730 rec_unit_aig cur_unit_aig%ROWTYPE;
1731 --
1732 -- Check if the Student Unit Attempt Assessment Item Group exists for
1733 -- the items copied from Unit Assessment Items
1734 --
1735 CURSOR cur_unit_suaig_exists (
1736 cp_group_id IN NUMBER,
1737 cp_person_id IN NUMBER,
1738 cp_course_cd IN VARCHAR2,
1739 cp_uoo_id IN VARCHAR2
1740 ) IS
1741 SELECT sua_ass_item_group_id,
1742 us_ass_item_group_id,
1743 unit_ass_item_group_id,
1744 group_name,
1745 rowid
1746 FROM igs_as_sua_ai_group suaaig
1747 WHERE suaaig.unit_ass_item_group_id = cp_group_id
1748 AND suaaig.person_id = cp_person_id
1749 AND suaaig.course_cd = cp_course_cd
1750 AND suaaig.uoo_id = cp_uoo_id
1751 ORDER BY unit_ass_item_group_id, us_ass_item_group_id;
1752 --
1753 -- Check if the Student Unit Attempt Assessment Item Group exists for
1754 -- the items copied from Unit Section Assessment Items
1755 --
1756 CURSOR cur_usec_suaig_exists (
1757 cp_group_id IN NUMBER,
1758 cp_person_id IN NUMBER,
1759 cp_course_cd IN VARCHAR2,
1760 cp_uoo_id IN VARCHAR2
1761 ) IS
1762 SELECT sua_ass_item_group_id,
1763 us_ass_item_group_id,
1764 unit_ass_item_group_id,
1765 group_name,
1766 rowid
1767 FROM igs_as_sua_ai_group suaaig
1768 WHERE suaaig.us_ass_item_group_id = cp_group_id
1769 AND suaaig.person_id = cp_person_id
1770 AND suaaig.course_cd = cp_course_cd
1771 AND suaaig.uoo_id = cp_uoo_id
1772 FOR UPDATE OF logical_delete_date NOWAIT;
1773 --
1774 rec_suaig cur_unit_suaig_exists%ROWTYPE;
1775 l_group_name VARCHAR2(30);
1776 -- Bug # 3749413
1777
1778 /* l_unit_section_ass_item_id igs_ps_unitass_item.unit_section_ass_item_id%TYPE;
1779 l_unit_ass_item_id igs_as_unitass_item_all.unit_ass_item_id%TYPE;
1780 l_midterm_mandatory_type_code VARCHAR2(30);
1781 l_midterm_weight_qty NUMBER(6,3);
1782 l_final_mandatory_type_code VARCHAR2(30);
1783 l_final_weight_qty NUMBER(6,3);
1784 l_grading_schema_cd VARCHAR2(30);
1785 l_gs_version_number NUMBER; */
1786
1787 -- Bug # 3749413
1788
1789 l_return_pk_id NUMBER;
1790 l_unit_assessment_id igs_ps_unitass_item.unit_section_ass_item_id%TYPE;
1791 l_us_assessment_id igs_ps_unitass_item.unit_section_ass_item_id%TYPE;
1792
1793 -- End of new code added as per Bug# 2829291;
1794 BEGIN
1795 --
1796 -- Start of new code added as per Bug# 2829291;
1797 -- Initialise local variables.
1798 --
1799
1800 /* l_unit_section_ass_item_id := NULL;
1801 l_unit_ass_item_id := NULL; */
1802
1803 --
1804 -- Check if the Assessment Item is attached from the Unit Section Level.
1805 --
1806
1807 -- Bug # 3749413
1808
1809 /*
1810 OPEN cur_c1 (
1811 p_person_id,
1812 p_ass_id,
1813 p_course_cd,
1814 rec_uoo_id.uoo_id,
1815 p_group_id
1816 );
1817 FETCH cur_c1 INTO rec_c1;
1818 IF cur_c1%FOUND THEN
1819 CLOSE cur_c1;
1820 l_unit_section_ass_item_id := rec_c1.unit_section_ass_item_id;
1821 l_midterm_mandatory_type_code := rec_c1.midterm_mandatory_type_code;
1822 l_midterm_weight_qty := rec_c1.midterm_weight_qty;
1823 l_final_mandatory_type_code := rec_c1.final_mandatory_type_code;
1824 l_final_weight_qty := rec_c1.final_weight_qty;
1825 l_grading_schema_cd := rec_c1.grading_schema_cd;
1826 l_gs_version_number := rec_c1.gs_version_number;
1827 ELSE
1828 CLOSE cur_c1;
1829 --
1830 -- Check if the Assessment Item is attached from the Unit Level.
1831 --
1832 OPEN cur_c2 (
1833 p_person_id,
1834 p_ass_id,
1835 p_course_cd,
1836 rec_uoo_id.uoo_id,
1837 p_group_id
1838 );
1839 FETCH cur_c2 INTO rec_c2;
1840 IF cur_c2%FOUND THEN
1841 l_unit_ass_item_id := rec_c2.unit_ass_item_id;
1842 l_midterm_mandatory_type_code := rec_c2.midterm_mandatory_type_code;
1843 l_midterm_weight_qty := rec_c2.midterm_weight_qty;
1844 l_final_mandatory_type_code := rec_c2.final_mandatory_type_code;
1845 l_final_weight_qty := rec_c2.final_weight_qty;
1846 l_grading_schema_cd := rec_c2.grading_schema_cd;
1847 l_gs_version_number := rec_c2.gs_version_number;
1848 END IF;
1849 CLOSE cur_c2;
1850 END IF;
1851 */
1852 -- Bug # 3749413
1853 --
1854 -- End of new code added as per Bug# 2829291;
1855 -- Added by DDEY as a part of enhancement Bug # 2162831
1856 --
1857 -- Check if the Assessment Item Group is already created for the
1858 -- Assessment Item being added. If not created then create the
1859 -- Assessment Item Group and then the Assessment Items under that Group
1860 -- Get the details of the Assessment Item Group either from Unit Section
1861 -- or Unit level based on from where the item is being attached
1862 --
1863 IF (p_ass_id_usec_unit_ind = 'USEC') THEN
1864 --
1865 -- Check if there exists any assessment item setup for the student's
1866 -- unit section. If the setup exists at unit section level and the
1867 -- student carries assessment items from unit level, then logically
1868 -- delete all the student unit assessment items and attach to the
1869 -- student all default active assessment items from unit section level.
1870 --
1871 OPEN cur_usec_ass_items_exist (p_uoo_id);
1872 FETCH cur_usec_ass_items_exist INTO rec_usec_ass_items_exist;
1873 CLOSE cur_usec_ass_items_exist;
1874 --
1875 IF (rec_usec_ass_items_exist.ass_item_exists = 'Y') THEN
1876 --
1877 UPDATE igs_as_su_atmpt_itm suaai
1878 SET suaai.logical_delete_dt = SYSDATE,
1879 suaai.last_update_date = SYSDATE,
1880 suaai.last_updated_by = fnd_global.user_id,
1881 suaai.last_update_login = fnd_global.login_id,
1882 suaai.request_id = fnd_global.conc_request_id,
1883 suaai.program_id = fnd_global.conc_program_id,
1884 suaai.program_application_id = fnd_global.prog_appl_id,
1885 suaai.program_update_date = SYSDATE
1886 WHERE suaai.person_id = p_person_id
1887 AND suaai.course_cd = p_course_cd
1888 AND suaai.uoo_id = p_uoo_id
1889 AND suaai.unit_ass_item_id IS NOT NULL
1890 AND suaai.logical_delete_dt IS NULL;
1891 --
1892 UPDATE igs_as_sua_ai_group suaaig
1893 SET suaaig.logical_delete_date = SYSDATE,
1894 suaaig.last_update_date = SYSDATE,
1895 suaaig.last_updated_by = fnd_global.user_id,
1896 suaaig.last_update_login = fnd_global.login_id
1897 WHERE suaaig.person_id = p_person_id
1898 AND suaaig.course_cd = p_course_cd
1899 AND suaaig.uoo_id = p_uoo_id
1900 AND suaaig.unit_ass_item_group_id IS NOT NULL
1901 AND suaaig.logical_delete_date IS NULL;
1902 --
1903 OPEN cur_usec_suaig_exists (
1904 p_group_id,
1905 p_person_id,
1906 p_course_cd,
1907 p_uoo_id
1908 );
1909 FETCH cur_usec_suaig_exists INTO rec_suaig;
1910 --
1911 IF (cur_usec_suaig_exists%NOTFOUND) THEN
1912 CLOSE cur_usec_suaig_exists;
1913 OPEN cur_usec_aig (p_group_id);
1914 FETCH cur_usec_aig INTO rec_usec_aig;
1915 CLOSE cur_usec_aig;
1916 l_rowid8 := NULL;
1917 igs_as_sua_ai_group_pkg.insert_row (
1918 x_rowid => l_rowid8,
1919 x_sua_ass_item_group_id => l_return_pk_id,
1920 x_person_id => p_person_id,
1921 x_course_cd => p_course_cd,
1922 x_uoo_id => p_uoo_id,
1923 x_group_name => rec_usec_aig.group_name,
1924 x_midterm_formula_code => rec_usec_aig.midterm_formula_code,
1925 x_midterm_formula_qty => rec_usec_aig.midterm_formula_qty,
1926 x_midterm_weight_qty => rec_usec_aig.midterm_weight_qty,
1927 x_final_formula_code => rec_usec_aig.final_formula_code,
1928 x_final_formula_qty => rec_usec_aig.final_formula_qty,
1929 x_final_weight_qty => rec_usec_aig.final_weight_qty,
1930 x_unit_ass_item_group_id => NULL,
1931 x_us_ass_item_group_id => rec_usec_aig.us_ass_item_group_id,
1932 x_logical_delete_date => NULL,
1933 x_mode => 'R'
1934 );
1935 ELSE
1936 --
1937 -- Update the SUAI Group definition from latest USAIG definition
1938 --
1939 OPEN cur_usec_aig (p_group_id);
1940 FETCH cur_usec_aig INTO rec_usec_aig;
1941 CLOSE cur_usec_aig;
1942 igs_as_sua_ai_group_pkg.update_row (
1943 x_rowid => rec_suaig.rowid,
1944 x_sua_ass_item_group_id => rec_suaig.sua_ass_item_group_id,
1945 x_person_id => p_person_id,
1946 x_course_cd => p_course_cd,
1947 x_uoo_id => p_uoo_id,
1948 x_group_name => rec_suaig.group_name,
1949 x_midterm_formula_code => rec_usec_aig.midterm_formula_code,
1950 x_midterm_formula_qty => rec_usec_aig.midterm_formula_qty,
1951 x_midterm_weight_qty => rec_usec_aig.midterm_weight_qty,
1952 x_final_formula_code => rec_usec_aig.final_formula_code,
1953 x_final_formula_qty => rec_usec_aig.final_formula_qty,
1954 x_final_weight_qty => rec_usec_aig.final_weight_qty,
1955 x_unit_ass_item_group_id => NULL,
1956 x_us_ass_item_group_id => rec_usec_aig.us_ass_item_group_id,
1957 x_logical_delete_date => NULL,
1958 x_mode => 'R'
1959 );
1960 l_return_pk_id := rec_suaig.sua_ass_item_group_id;
1961 END IF;
1962 END IF;
1963 ELSIF (p_ass_id_usec_unit_ind = 'UNIT') THEN
1964 OPEN cur_unit_suaig_exists (
1965 p_group_id,
1966 p_person_id,
1967 p_course_cd,
1968 p_uoo_id
1969 );
1970 FETCH cur_unit_suaig_exists INTO rec_suaig;
1971 IF (cur_unit_suaig_exists%NOTFOUND) THEN
1972 CLOSE cur_unit_suaig_exists;
1973 OPEN cur_unit_aig (p_group_id);
1974 FETCH cur_unit_aig INTO rec_unit_aig;
1975 CLOSE cur_unit_aig;
1976 l_rowid8 := NULL;
1977 igs_as_sua_ai_group_pkg.insert_row (
1978 x_rowid => l_rowid8,
1979 x_sua_ass_item_group_id => l_return_pk_id,
1980 x_person_id => p_person_id,
1981 x_course_cd => p_course_cd,
1982 x_uoo_id => p_uoo_id,
1983 x_group_name => rec_unit_aig.group_name,
1984 x_midterm_formula_code => rec_unit_aig.midterm_formula_code,
1985 x_midterm_formula_qty => rec_unit_aig.midterm_formula_qty,
1986 x_midterm_weight_qty => rec_unit_aig.midterm_weight_qty,
1987 x_final_formula_code => rec_unit_aig.final_formula_code,
1988 x_final_formula_qty => rec_unit_aig.final_formula_qty,
1989 x_final_weight_qty => rec_unit_aig.final_weight_qty,
1990 x_unit_ass_item_group_id => rec_unit_aig.unit_ass_item_group_id,
1991 x_us_ass_item_group_id => NULL,
1992 x_logical_delete_date => NULL,
1993 x_mode => 'R'
1994 );
1995 ELSE
1996 CLOSE cur_unit_suaig_exists;
1997 OPEN cur_unit_aig (p_group_id);
1998 FETCH cur_unit_aig INTO rec_unit_aig;
1999 CLOSE cur_unit_aig;
2000 --
2001 -- Update the SUAI Group definition from latest UAIG definition
2002 --
2003 igs_as_sua_ai_group_pkg.update_row (
2004 x_rowid => rec_suaig.rowid,
2005 x_sua_ass_item_group_id => rec_suaig.sua_ass_item_group_id,
2006 x_person_id => p_person_id,
2007 x_course_cd => p_course_cd,
2008 x_uoo_id => p_uoo_id,
2009 x_group_name => rec_unit_aig.group_name,
2010 x_midterm_formula_code => rec_unit_aig.midterm_formula_code,
2011 x_midterm_formula_qty => rec_unit_aig.midterm_formula_qty,
2012 x_midterm_weight_qty => rec_unit_aig.midterm_weight_qty,
2013 x_final_formula_code => rec_unit_aig.final_formula_code,
2014 x_final_formula_qty => rec_unit_aig.final_formula_qty,
2015 x_final_weight_qty => rec_unit_aig.final_weight_qty,
2016 x_unit_ass_item_group_id => rec_unit_aig.unit_ass_item_group_id,
2017 x_us_ass_item_group_id => NULL,
2018 x_logical_delete_date => NULL,
2019 x_mode => 'R'
2020 );
2021 l_return_pk_id := rec_suaig.sua_ass_item_group_id;
2022 END IF;
2023 END IF;
2024 --
2025 -- Added by DDEY as a part of enhancement Bug # 2162831
2026 -- Assessment Item already available in the system but is Logically Deleted
2027 -- So update the Logically Deleted Date to NULL to bring it back into the system
2028 --
2029 OPEN c_suaai_upd (p_ass_id, p_uoo_id,p_ass_item_id);
2030 FETCH c_suaai_upd INTO suaai_upd_rec;
2031 --
2032 IF ((c_suaai_upd%FOUND) AND
2033 (((p_ass_id_usec_unit_ind = 'UNIT') AND (suaai_upd_rec.unit_ass_item_id IS NOT NULL) AND suaai_upd_rec.unit_ass_item_group_id = p_group_id) OR
2034 ((p_ass_id_usec_unit_ind = 'USEC') AND (suaai_upd_rec.unit_section_ass_item_id IS NOT NULL) AND suaai_upd_rec.us_ass_item_group_id = p_group_id )) AND
2035 (suaai_upd_rec.logical_delete_dt IS NOT NULL)) THEN
2036 CLOSE c_suaai_upd;
2037 --
2038 UPDATE igs_as_su_atmpt_itm suaai
2039 SET suaai.logical_delete_dt = NULL,
2040 suaai.last_update_date = SYSDATE,
2041 suaai.last_updated_by = fnd_global.user_id,
2042 suaai.last_update_login = fnd_global.login_id,
2043 suaai.request_id = fnd_global.conc_request_id,
2044 suaai.program_id = fnd_global.conc_program_id,
2045 suaai.program_application_id = fnd_global.prog_appl_id,
2046 suaai.program_update_date = SYSDATE ,
2047 suaai.midterm_mandatory_type_code = p_midterm_mandatory_type_code,
2048 suaai.midterm_weight_qty = p_midterm_weight_qty,
2049 suaai.final_mandatory_type_code = p_final_mandatory_type_code,
2050 suaai.final_weight_qty = p_final_weight_qty,
2051 suaai.grading_schema_cd = p_grading_schema_cd,
2052 suaai.gs_version_number = p_gs_version_number
2053 WHERE suaai.rowid = suaai_upd_rec.ROWID;
2054
2055 /* igs_as_su_atmpt_itm_pkg.update_row (
2056 x_mode => 'R',
2057 x_rowid => suaai_upd_rec.ROWID,
2058 x_person_id => suaai_upd_rec.person_id,
2059 x_course_cd => suaai_upd_rec.course_cd,
2060 x_unit_cd => suaai_upd_rec.unit_cd,
2061 x_cal_type => suaai_upd_rec.cal_type,
2062 x_ci_sequence_number => suaai_upd_rec.ci_sequence_number,
2063 x_ass_id => suaai_upd_rec.ass_id,
2064 x_creation_dt => suaai_upd_rec.creation_dt,
2065 x_attempt_number => suaai_upd_rec.attempt_number,
2066 x_outcome_dt => suaai_upd_rec.outcome_dt,
2067 x_override_due_dt => suaai_upd_rec.override_due_dt,
2068 x_tracking_id => suaai_upd_rec.tracking_id,
2069 x_logical_delete_dt => NULL,
2070 x_s_default_ind => suaai_upd_rec.s_default_ind,
2071 x_ass_pattern_id => suaai_upd_rec.ass_pattern_id,
2072 x_grading_schema_cd => suaai_upd_rec.grading_schema_cd,
2073 x_gs_version_number => suaai_upd_rec.gs_version_number,
2074 x_grade => suaai_upd_rec.grade,
2075 x_outcome_comment_code => suaai_upd_rec.outcome_comment_code,
2076 x_mark => suaai_upd_rec.mark,
2077 x_attribute_category => suaai_upd_rec.attribute_category,
2078 x_attribute1 => suaai_upd_rec.attribute1,
2079 x_attribute2 => suaai_upd_rec.attribute2,
2080 x_attribute3 => suaai_upd_rec.attribute3,
2081 x_attribute4 => suaai_upd_rec.attribute4,
2082 x_attribute5 => suaai_upd_rec.attribute5,
2083 x_attribute6 => suaai_upd_rec.attribute6,
2084 x_attribute7 => suaai_upd_rec.attribute7,
2085 x_attribute8 => suaai_upd_rec.attribute8,
2086 x_attribute9 => suaai_upd_rec.attribute9,
2087 x_attribute10 => suaai_upd_rec.attribute10,
2088 x_attribute11 => suaai_upd_rec.attribute11,
2089 x_attribute12 => suaai_upd_rec.attribute12,
2090 x_attribute13 => suaai_upd_rec.attribute13,
2091 x_attribute14 => suaai_upd_rec.attribute14,
2092 x_attribute15 => suaai_upd_rec.attribute15,
2093 x_attribute16 => suaai_upd_rec.attribute16,
2094 x_attribute17 => suaai_upd_rec.attribute17,
2095 x_attribute18 => suaai_upd_rec.attribute18,
2096 x_attribute19 => suaai_upd_rec.attribute19,
2097 x_attribute20 => suaai_upd_rec.attribute20,
2098 x_uoo_id => suaai_upd_rec.uoo_id,
2099 x_unit_section_ass_item_id => suaai_upd_rec.unit_section_ass_item_id,
2100 x_unit_ass_item_id => suaai_upd_rec.unit_ass_item_id,
2101 x_sua_ass_item_group_id => suaai_upd_rec.sua_ass_item_group_id,
2102 x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
2103 x_midterm_weight_qty => p_midterm_weight_qty,
2104 x_final_mandatory_type_code => p_final_mandatory_type_code,
2105 x_final_weight_qty => p_final_weight_qty,
2106 x_submitted_date => suaai_upd_rec.submitted_date,
2107 x_waived_flag => suaai_upd_rec.waived_flag,
2108 x_penalty_applied_flag => suaai_upd_rec.penalty_applied_flag
2109 );*/
2110 ELSIF ((c_suaai_upd%FOUND) AND
2111 (((p_ass_id_usec_unit_ind = 'UNIT') AND (suaai_upd_rec.unit_ass_item_id IS NOT NULL) AND suaai_upd_rec.unit_ass_item_group_id = p_group_id ) OR
2112 ((p_ass_id_usec_unit_ind = 'USEC') AND (suaai_upd_rec.unit_section_ass_item_id IS NOT NULL) AND suaai_upd_rec.us_ass_item_group_id = p_group_id)) AND
2113 (suaai_upd_rec.logical_delete_dt IS NULL)) THEN
2114 CLOSE c_suaai_upd;
2115 --
2116 -- Item already exists; so apply the changed assessment item definition if any
2117 --
2118 UPDATE igs_as_su_atmpt_itm suaai
2119 SET suaai.last_update_date = SYSDATE,
2120 suaai.last_updated_by = fnd_global.user_id,
2121 suaai.last_update_login = fnd_global.login_id,
2122 suaai.request_id = fnd_global.conc_request_id,
2123 suaai.program_id = fnd_global.conc_program_id,
2124 suaai.program_application_id = fnd_global.prog_appl_id,
2125 suaai.program_update_date = SYSDATE,
2126 suaai.midterm_mandatory_type_code = p_midterm_mandatory_type_code,
2127 suaai.midterm_weight_qty = p_midterm_weight_qty,
2128 suaai.final_mandatory_type_code = p_final_mandatory_type_code,
2129 suaai.final_weight_qty = p_final_weight_qty,
2130 suaai.grading_schema_cd = p_grading_schema_cd,
2131 suaai.gs_version_number = p_gs_version_number
2132 WHERE suaai.rowid = suaai_upd_rec.ROWID;
2133 /* igs_as_su_atmpt_itm_pkg.update_row (
2134 x_mode => 'R',
2135 x_rowid => suaai_upd_rec.ROWID,
2136 x_person_id => suaai_upd_rec.person_id,
2137 x_course_cd => suaai_upd_rec.course_cd,
2138 x_unit_cd => suaai_upd_rec.unit_cd,
2139 x_cal_type => suaai_upd_rec.cal_type,
2140 x_ci_sequence_number => suaai_upd_rec.ci_sequence_number,
2141 x_ass_id => suaai_upd_rec.ass_id,
2142 x_creation_dt => suaai_upd_rec.creation_dt,
2143 x_attempt_number => suaai_upd_rec.attempt_number,
2144 x_outcome_dt => suaai_upd_rec.outcome_dt,
2145 x_override_due_dt => suaai_upd_rec.override_due_dt,
2146 x_tracking_id => suaai_upd_rec.tracking_id,
2147 x_logical_delete_dt => suaai_upd_rec.logical_delete_dt,
2148 x_s_default_ind => suaai_upd_rec.s_default_ind,
2149 x_ass_pattern_id => suaai_upd_rec.ass_pattern_id,
2150 x_grading_schema_cd => p_grading_schema_cd,
2151 x_gs_version_number => p_gs_version_number ,
2152 x_grade => suaai_upd_rec.grade,
2153 x_outcome_comment_code => suaai_upd_rec.outcome_comment_code,
2154 x_mark => suaai_upd_rec.mark,
2155 x_attribute_category => suaai_upd_rec.attribute_category,
2156 x_attribute1 => suaai_upd_rec.attribute1,
2157 x_attribute2 => suaai_upd_rec.attribute2,
2158 x_attribute3 => suaai_upd_rec.attribute3,
2159 x_attribute4 => suaai_upd_rec.attribute4,
2160 x_attribute5 => suaai_upd_rec.attribute5,
2161 x_attribute6 => suaai_upd_rec.attribute6,
2162 x_attribute7 => suaai_upd_rec.attribute7,
2163 x_attribute8 => suaai_upd_rec.attribute8,
2164 x_attribute9 => suaai_upd_rec.attribute9,
2165 x_attribute10 => suaai_upd_rec.attribute10,
2166 x_attribute11 => suaai_upd_rec.attribute11,
2167 x_attribute12 => suaai_upd_rec.attribute12,
2168 x_attribute13 => suaai_upd_rec.attribute13,
2169 x_attribute14 => suaai_upd_rec.attribute14,
2170 x_attribute15 => suaai_upd_rec.attribute15,
2171 x_attribute16 => suaai_upd_rec.attribute16,
2172 x_attribute17 => suaai_upd_rec.attribute17,
2173 x_attribute18 => suaai_upd_rec.attribute18,
2174 x_attribute19 => suaai_upd_rec.attribute19,
2175 x_attribute20 => suaai_upd_rec.attribute20,
2176 x_uoo_id => suaai_upd_rec.uoo_id,
2177 x_unit_section_ass_item_id => suaai_upd_rec.unit_section_ass_item_id,
2178 x_unit_ass_item_id => suaai_upd_rec.unit_ass_item_id,
2179 x_sua_ass_item_group_id => suaai_upd_rec.sua_ass_item_group_id,
2180 x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
2181 x_midterm_weight_qty => p_midterm_weight_qty,
2182 x_final_mandatory_type_code => p_final_mandatory_type_code,
2183 x_final_weight_qty => p_final_weight_qty,
2184 x_submitted_date => suaai_upd_rec.submitted_date,
2185 x_waived_flag => suaai_upd_rec.waived_flag,
2186 x_penalty_applied_flag => suaai_upd_rec.penalty_applied_flag
2187 );*/
2188 ELSE
2189 CLOSE c_suaai_upd;
2190 l_rowid8 := NULL;
2191 --
2192 -- Create the Assessment Item under the Student Unit Attempt
2193 -- Assessment Item Group
2194 --
2195 IF( p_ass_id_usec_unit_ind = 'USEC' ) THEN
2196 l_unit_assessment_id := NULL;
2197 l_us_assessment_id := p_ass_item_id;
2198 ELSE
2199 l_unit_assessment_id := p_ass_item_id;
2200 l_us_assessment_id := NULL;
2201 END IF;
2202 OPEN c_suaai_an (p_uoo_id);
2203 FETCH c_suaai_an INTO v_attempt_number;
2204 CLOSE c_suaai_an;
2205 igs_as_su_atmpt_itm_pkg.insert_row (
2206 x_mode => 'R',
2207 x_rowid => l_rowid8,
2208 x_person_id => p_person_id,
2209 x_course_cd => p_course_cd,
2210 x_unit_cd => p_unit_cd,
2211 x_cal_type => p_cal_type,
2212 x_ci_sequence_number => p_ci_sequence_number,
2213 x_ass_id => p_ass_id,
2214 x_creation_dt => v_creation_dt,
2215 x_attempt_number => v_attempt_number,
2216 x_outcome_dt => NULL,
2217 x_override_due_dt => NULL,
2218 x_tracking_id => NULL,
2219 x_logical_delete_dt => NULL,
2220 x_s_default_ind => cst_yes,
2221 x_ass_pattern_id => NULL,
2222 x_grading_schema_cd => p_grading_schema_cd,
2223 x_gs_version_number => p_gs_version_number,
2224 x_grade => NULL,
2225 x_outcome_comment_code => NULL,
2226 x_mark => NULL,
2227 x_attribute_category => NULL,
2228 x_attribute1 => NULL,
2229 x_attribute2 => NULL,
2230 x_attribute3 => NULL,
2231 x_attribute4 => NULL,
2232 x_attribute5 => NULL,
2233 x_attribute6 => NULL,
2234 x_attribute7 => NULL,
2235 x_attribute8 => NULL,
2236 x_attribute9 => NULL,
2237 x_attribute10 => NULL,
2238 x_attribute11 => NULL,
2239 x_attribute12 => NULL,
2240 x_attribute13 => NULL,
2241 x_attribute14 => NULL,
2242 x_attribute15 => NULL,
2243 x_attribute16 => NULL,
2244 x_attribute17 => NULL,
2245 x_attribute18 => NULL,
2246 x_attribute19 => NULL,
2247 x_attribute20 => NULL,
2248 x_uoo_id => p_uoo_id,
2249 x_unit_section_ass_item_id => l_us_assessment_id,
2250 x_unit_ass_item_id => l_unit_assessment_id,
2251 x_sua_ass_item_group_id => l_return_pk_id,
2252 x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
2253 x_midterm_weight_qty => p_midterm_weight_qty,
2254 x_final_mandatory_type_code => p_final_mandatory_type_code,
2255 x_final_weight_qty => p_final_weight_qty,
2256 x_submitted_date => NULL,
2257 x_waived_flag => 'N',
2258 x_penalty_applied_flag => 'N'
2259 );
2260 END IF;
2261 END;
2262 RETURN TRUE;
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 IF c_sua_status%ISOPEN THEN
2266 CLOSE c_sua_status;
2267 END IF;
2268 IF c_suaai_deleted%ISOPEN THEN
2269 CLOSE c_suaai_deleted;
2270 END IF;
2271 IF c_crv%ISOPEN THEN
2272 CLOSE c_crv;
2273 END IF;
2274 IF c_suaai_an%ISOPEN THEN
2275 CLOSE c_suaai_an;
2276 END IF;
2277 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2278 fnd_log.string (
2279 fnd_log.level_exception, 'igs_as_gen_004.assp_ins_suaai_dflt.exception_while_insert_update',
2280 'SQLERRM:' || SQLERRM
2281 );
2282 END IF;
2283 RETURN FALSE;
2284 END;
2285
2286 RETURN TRUE;
2287 EXCEPTION
2288 WHEN OTHERS THEN
2289 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2290 fnd_log.string (
2291 fnd_log.level_exception, 'igs_as_gen_004.assp_ins_suaai_dflt.final_exception_while_insert_update',
2292 'SQLERRM:' || SQLERRM
2293 );
2294 END IF;
2295 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
2296 fnd_message.set_token ('NAME', 'IGS_AS_GEN_004.assp_ins_suaai_dflt');
2297 igs_ge_msg_stack.ADD;
2298 RETURN FALSE;
2299 END assp_ins_suaai_dflt;
2300 --
2301 -- This function is obsolete as the Grade Book Enhancement obsoleted the
2302 -- Assessment Patterns functionality
2303 --
2304 FUNCTION assp_ins_suaap_dflt (
2305 p_person_id IN NUMBER,
2306 p_course_cd IN VARCHAR2,
2307 p_unit_cd IN VARCHAR2,
2308 p_version_number IN NUMBER,
2309 p_cal_type IN VARCHAR2,
2310 p_ci_sequence_number IN NUMBER,
2311 p_location_cd IN VARCHAR2,
2312 p_unit_class IN VARCHAR2,
2313 p_ass_pattern_id IN NUMBER,
2314 p_s_log_type IN VARCHAR2,
2315 p_key IN VARCHAR2,
2316 p_sle_key IN VARCHAR2,
2317 p_error_count IN OUT NOCOPY NUMBER,
2318 p_warning_count IN OUT NOCOPY NUMBER,
2319 p_message_name OUT NOCOPY VARCHAR2
2320 ) RETURN BOOLEAN IS
2321 BEGIN
2322 RETURN FALSE;
2323 END assp_ins_suaap_dflt;
2324 --
2325 -- This function is obsolete as the Grade Book Enhancement obsoleted the
2326 -- Assessment Patterns functionality
2327 --
2328 FUNCTION assp_ins_suaap_suaai (
2329 p_person_id IN NUMBER,
2330 p_course_cd IN VARCHAR2,
2331 p_unit_cd IN VARCHAR2,
2332 p_version_number IN NUMBER,
2333 p_cal_type IN VARCHAR2,
2334 p_ci_sequence_number IN NUMBER,
2335 p_ass_pattern_id IN NUMBER,
2336 p_creation_dt IN DATE,
2337 p_s_default_ind IN VARCHAR2 DEFAULT 'N',
2338 p_call_from_db_trg IN VARCHAR2 DEFAULT 'N',
2339 p_message_name OUT NOCOPY VARCHAR2,
2340 p_uoo_id IN NUMBER
2341 ) RETURN BOOLEAN IS
2342 BEGIN
2343 RETURN FALSE;
2344 END assp_ins_suaap_suaai;
2345 --
2346 --
2347 --
2348 FUNCTION assp_ins_transcript (
2349 p_course_org_unit_cd IN VARCHAR2,
2350 p_course_group_cd IN VARCHAR2,
2351 p_course_cd IN VARCHAR2,
2352 p_course_location_cd IN VARCHAR2,
2353 p_course_attendance_mode IN VARCHAR2,
2354 p_course_award IN VARCHAR2 DEFAULT 'BOTH',
2355 p_course_attempt_status IN VARCHAR2,
2356 p_progression_status IN VARCHAR2,
2357 p_graduand_status IN VARCHAR2,
2358 p_person_id_group IN NUMBER,
2359 p_person_id IN NUMBER,
2360 p_transcript_type IN VARCHAR2,
2361 p_include_fail_grades_ind IN VARCHAR2 DEFAULT 'N',
2362 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
2363 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
2364 p_exclude_unit_category IN VARCHAR2,
2365 p_extract_course_cd IN VARCHAR2,
2366 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
2367 p_order_by IN VARCHAR2 DEFAULT 'YEAR',
2368 p_external_order_by IN VARCHAR2 DEFAULT 'SURNAME',
2369 p_correspondence_ind IN VARCHAR2 DEFAULT 'N',
2370 p_message_name OUT NOCOPY VARCHAR2,
2371 p_reference_number OUT NOCOPY NUMBER
2372 ) RETURN BOOLEAN IS
2373 BEGIN
2374 --
2375 -- As per 2239087, this concurrent program is obsolete and if the user
2376 -- tries to run this program then an error message should be logged into the log
2377 -- file that the concurrent program is obsolete and should not be run.
2378 --
2379 fnd_message.set_name ('IGS', 'IGS_GE_OBSOLETE_JOB');
2380 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
2381 --
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 igs_ge_msg_stack.conc_exception_hndl;
2385 END assp_ins_transcript;
2386 --
2387 -- This function is obsolete as the Grade Book Enhancement obsoleted the
2388 -- Assessment Patterns functionality
2389 --
2390 FUNCTION assp_get_uapi_ap (
2391 p_ass_pattern_id IN NUMBER,
2392 p_ass_id IN NUMBER
2393 ) RETURN NUMBER IS
2394 BEGIN
2395 RETURN 0;
2396 END assp_get_uapi_ap;
2397 END igs_as_gen_004;