1 PACKAGE BODY IGS_AS_GEN_006 AS
2 /* $Header: IGSAS06B.pls 120.1 2006/01/18 22:52:59 swaghmar noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 -- lkaki 19-Nov-2004 Added one more parameter to the procedure 'assp_ins_admin_grds'
7 -- to assign the grades for audited unit attempts separately.
8 -- For this purpose,added one more cursor to handle audited grades
9 -- 'c_grading_schema_audit_grade' and changed the existing cursor definition 'c_grading_schema_grade'
10 -- to exclude audited grades and retrieve only non-audited ones.
11 -- kdande 23-Jan-2004 Removed app_exception.raise_exception since the message
12 -- being logged is a proper message and not an exception.
13 -- ijeddy, Dec 3, 2003 Grade Book Enh build, bug no 3201661
14 -- gmaheswa 13-nov-2003 Bug No. 3227107 . Address changes. Modified address related cursor to select active records only.
15 -- smadathi 28-AUG-2001 Bug No. 1956374 .The call to igs_as_val_esvs.genp_val_staff_prsn
16 -- is changed to igs_ad_val_acai.genp_val_staff_prsn
17 -- bayadav 28-DEC-2001 added code to include newly added columns in IGS_AS_GRD_GRADE as a part of bug 2162831
18 -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
19 -- to table handler procedure calls as part of CCR - ENCR022.
20 -- Aiyer 08-APR-2002 Bug No. 2124034. The parameter p_reproduce was also added as a hidden parameter in the
21 -- concurrent job IGSASJ05 Produce Student Assignment Cover Sheet with a default value as 'NO'.
22 -- In the package body of porocedure too it was made to have a default value of 'NO'.
23 --swaghmar 16-Jan-2006 Bug# 4951054 Added check for disabling UI's
24 -------------------------------------------------------------------------------------------------------------------------
25 -- As part of the bug# 1956374 prcodure assp_val_actv_stdnt is changed
26 x_rowid VARCHAR2(25);
27 l_AT_ID IGS_AS_DUE_DT_SUMRY.AT_ID%type;
28 g_module_head CONSTANT VARCHAR2(40) := 'igs.plsql.igs_as_gen_006.';
29
30 PROCEDURE assp_get_ese_key(
31 p_exam_cal_type IN OUT NOCOPY VARCHAR2 ,
32 p_exam_ci_sequence_number IN OUT NOCOPY NUMBER ,
33 p_dt_alias IN OUT NOCOPY VARCHAR2 ,
34 p_dai_sequence_number IN OUT NOCOPY NUMBER ,
35 p_start_time IN OUT NOCOPY DATE ,
36 p_end_time IN OUT NOCOPY DATE ,
37 p_ese_id IN OUT NOCOPY NUMBER )
38 IS
39 BEGIN --assp_get_ese_key
40 --This module retrieves one of IGS_AS_EXAM_SESSION unique identifiers:
41 --1. exam_cal_type, exam_ci_sequence_number, dt_alias, dai_sequence_number,
42 -- start_time, end_time
43 --2. ese_id
44 DECLARE
45 v_exam_cal_type IGS_AS_EXAM_SESSION.exam_cal_type%TYPE;
46 v_exam_ci_sequence_number IGS_AS_EXAM_SESSION.exam_ci_sequence_number%TYPE;
47 v_dt_alias IGS_AS_EXAM_SESSION.dt_alias%TYPE;
48 v_dai_sequence_number IGS_AS_EXAM_SESSION.dai_sequence_number%TYPE;
49 v_start_time IGS_AS_EXAM_SESSION.start_time%TYPE;
50 v_end_time IGS_AS_EXAM_SESSION.end_time%TYPE;
51 v_ese_id IGS_AS_EXAM_SESSION.ese_id%TYPE;
52 CURSOR c_ese IS
53 SELECT ese.ese_id
54 FROM IGS_AS_EXAM_SESSION ese
55 WHERE exam_cal_type = p_exam_cal_type AND
56 exam_ci_sequence_number = p_exam_ci_sequence_number AND
57 dt_alias = p_dt_alias AND
58 dai_sequence_number = p_dai_sequence_number AND
59 start_time = p_start_time AND
60 end_time = p_end_time;
61 CURSOR c_ese2 IS
62 SELECT exam_cal_type,
63 exam_ci_sequence_number,
64 dt_alias,
65 dai_sequence_number,
66 start_time,
67 end_time
68 FROM IGS_AS_EXAM_SESSION ese
69 WHERE ese_id = p_ese_id;
70 BEGIN
71 --Check if p_exam_cal_type has been passed
72 IF (p_exam_cal_type IS NOT NULL) THEN
73 OPEN c_ese;
74 FETCH c_ese INTO v_ese_id;
75 IF (c_ese%FOUND) THEN
76 p_ese_id := v_ese_id;
77 END IF;
78 CLOSE c_ese;
79 --Check if p_ese_id has been passed
80 ELSIF (p_ese_id IS NOT NULL) THEN
81 OPEN c_ese2;
82 FETCH c_ese2 INTO v_exam_cal_type,
83 v_exam_ci_sequence_number,
84 v_dt_alias,
85 v_dai_sequence_number,
86 v_start_time,
87 v_end_time;
88 IF (c_ese2%FOUND) THEN
89 p_exam_cal_type := v_exam_cal_type;
90 p_exam_ci_sequence_number := v_exam_ci_sequence_number;
91 p_dt_alias := v_dt_alias;
92 p_dai_sequence_number := v_dai_sequence_number;
93 p_start_time := v_start_time;
94 p_end_time := v_end_time;
95 END IF;
96 CLOSE c_ese2;
97 ELSE
98 p_exam_cal_type := NULL;
99 p_exam_ci_sequence_number := NULL;
100 p_dt_alias := NULL;
101 p_dai_sequence_number := NULL;
102 p_start_time := NULL;
103 p_end_time := NULL;
104 p_ese_id := NULL;
105 END IF;
106 END;
107 EXCEPTION
108 WHEN OTHERS THEN
109 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
110 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_get_ese_key');
111 IGS_GE_MSG_STACK.ADD;
112 App_Exception.Raise_Exception;
113 END assp_get_ese_key;
114 --
115 -- Process to insert administrative grades against student unit attempts which
116 -- have no grade recorded.
117 -- This process can be used to either default grades for non-assessable unit
118 -- attempts, or to insert 'holding' grades against unit attempts for which
119 -- grades simply haven't yet been supplied. This may be done prior to result
120 -- release to prevent the students being shown blank grades.
121 --
122 PROCEDURE assp_ins_admin_grds (
123 errbuf OUT NOCOPY VARCHAR2,
124 retcode OUT NOCOPY NUMBER,
125 p_assess_calendar IN VARCHAR2,
126 p_teaching_calendar IN VARCHAR2,
127 p_org_unt_cd IN VARCHAR2,
128 p_unt_cd IN VARCHAR2,
129 p_lctn_cd IN VARCHAR2,
130 p_unt_md IN VARCHAR2,
131 p_unt_cls IN VARCHAR2,
132 p_insert_default_ind IN VARCHAR2,
133 p_grade IN VARCHAR2,
134 p_finalised_ind IN VARCHAR2,
135 p_assble_type IN VARCHAR2,
136 p_no_assmnt_type IN VARCHAR2,
137 p_org_id IN NUMBER,
138 --added by lkaki--
139 p_audit_grade IN VARCHAR2 DEFAULT NULL
140 ) IS
141 --
142 p_assess_cal_type igs_ca_inst.cal_type%TYPE;
143 p_assess_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
144 p_teach_cal_type igs_ca_inst.cal_type%TYPE;
145 p_teach_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
146 p_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
147 p_unit_cd igs_ps_unit.unit_cd%TYPE;
148 p_location_cd igs_ad_location.location_cd%TYPE;
149 p_unit_mode igs_as_unit_mode.unit_mode%TYPE;
150 p_unit_class igs_as_unit_class.unit_class%TYPE;
151 p_assessable_type igs_lookups_view.lookup_code%TYPE;
152 p_no_assessment_type igs_lookups_view.lookup_code%TYPE;
153 --
154 BEGIN -- assp_ins_admin_grds
155 --
156 IGS_GE_GEN_003.set_org_id(); -- swaghmar, bug# 4951054
157
158 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
159 fnd_log.string (
160 fnd_log.level_procedure,
161 g_module_head || 'assp_ins_admin_grds.begin',
162 'In Params : p_assess_calendar => ' || p_assess_calendar || ';' ||
163 'p_teaching_calendar => ' || p_teaching_calendar || ';' ||
164 'p_org_unt_cd => ' || p_org_unt_cd || ';' ||
165 'p_unt_cd => ' || p_unt_cd || ';' ||
166 'p_lctn_cd => ' || p_lctn_cd || ';' ||
167 'p_unt_md => ' || p_unt_md || ';' ||
168 'p_unt_cls => ' || p_unt_cls || ';' ||
169 'p_insert_default_ind => ' || p_insert_default_ind || ';' ||
170 'p_grade => ' || p_grade || ';' ||
171 'p_finalised_ind => ' || p_finalised_ind || ';' ||
172 'p_assble_type => ' || p_assble_type || ';' ||
173 'p_no_assmnt_type => ' || p_no_assmnt_type || ';' ||
174 'p_org_id => ' || p_org_id || ';' ||
175 'p_audit_grade => ' || p_audit_grade
176 );
177 END IF;
178 --
179 igs_ge_gen_003.set_org_id (p_org_id);
180 --
181 retcode := 0;
182 p_org_unit_cd := NVL (p_org_unt_cd, '%');
183 p_unit_cd := NVL (p_unt_cd, '%');
184 p_location_cd := NVL (p_lctn_cd, '%');
185 p_unit_mode := NVL (p_unt_md, '%');
186 p_unit_class := NVL (p_unt_cls, '%');
187 p_assessable_type := NVL (p_assble_type, '%');
188 p_no_assessment_type := NVL (p_no_assmnt_type, '%');
189 --
190 DECLARE
191 invalid_parameter EXCEPTION;
192 BEGIN
193 /*changed by lkaki*/
194 --
195 IF (p_insert_default_ind = 'N'
196 AND (p_grade IS NULL
197 AND p_audit_grade IS NULL)) THEN
198 errbuf := fnd_message.get_string ('IGS', 'IGS_AS_GRD_SPECIFIED');
199 RAISE invalid_parameter;
200 END IF;
201 --
202 IF p_assess_calendar IS NULL THEN
203 p_assess_cal_type := NULL;
204 p_assess_ci_sequence_number := NULL;
205 ELSE
206 p_assess_cal_type := RTRIM (SUBSTR (p_assess_calendar, 101, 10));
207 p_assess_ci_sequence_number := TO_NUMBER (RTRIM (SUBSTR (p_assess_calendar, 112, 6)));
208 END IF;
209 --
210 IF p_teaching_calendar IS NULL THEN
211 p_teach_cal_type := NULL;
212 p_teach_ci_sequence_number := NULL;
213 ELSE
214 p_teach_cal_type := RTRIM (SUBSTR (p_teaching_calendar, 101, 10));
215 p_teach_ci_sequence_number := TO_NUMBER (RTRIM (SUBSTR (p_teaching_calendar, 112, 6)));
216 END IF;
217 --
218 IF ((p_assess_calendar IS NOT NULL) AND
219 (p_teaching_calendar IS NOT NULL)) THEN
220 IF (igs_en_gen_014.enrs_get_within_ci (
221 p_assess_cal_type,
222 p_assess_ci_sequence_number,
223 p_teach_cal_type,
224 p_teach_ci_sequence_number,
225 'N'
226 ) <> 'Y'
227 ) THEN
228 errbuf := fnd_message.get_string ('IGS', 'IGS_AS_TEACHCAL_NOT_EXIST');
229 RAISE invalid_parameter;
230 END IF;
231 END IF;
232 --
233 IF (p_unit_mode <> '%'
234 AND p_unit_class <> '%') THEN
235 errbuf := fnd_message.get_string ('IGS', 'IGS_AS_UNITMODE_OR_UNITCLASS');
236 RAISE invalid_parameter;
237 END IF;
238 EXCEPTION
239 WHEN invalid_parameter THEN
240 retcode := 2;
241 RETURN;
242 END;
243 --
244 --
245 --
246 DECLARE
247 i BINARY_INTEGER DEFAULT 0;
248 n BINARY_INTEGER DEFAULT 0;
249 v_record_found BOOLEAN DEFAULT FALSE;
250 v_grade igs_as_grd_sch_grade.grade%TYPE;
251 --added another variable to handle audit grades for audited attempts--
252 v_audit_grade igs_as_grd_sch_grade.grade%TYPE;
253 v_grading_schema igs_as_grd_schema.grading_schema_cd%TYPE;
254 v_gs_version_number igs_as_grd_schema.version_number%TYPE;
255 v_insert_grade igs_as_grd_sch_grade.grade%TYPE;
256 --
257 -- Get all the Student Unit Attempts that do not have any Unit Attempt Outcome
258 -- and match the criteria passed thru parameters
259 --
260 CURSOR c_stu_unit_atmpt (
261 cp_assess_cal_type igs_ca_inst.cal_type%TYPE,
262 cp_assess_sequence_number igs_ca_inst.sequence_number%TYPE,
263 cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
264 cp_teach_sequence_number igs_ca_inst.sequence_number%TYPE,
265 cp_org_unit_cd igs_or_unit.org_unit_cd%TYPE,
266 cp_unit_cd igs_ps_unit.unit_cd%TYPE,
267 cp_location_cd igs_ad_location.location_cd%TYPE,
268 cp_unit_mode igs_as_unit_class.unit_mode%TYPE,
269 cp_unit_class igs_as_unit_class.unit_class%TYPE,
270 cp_assessable_type igs_as_assessmnt_typ.assessment_type%TYPE,
271 cp_no_assessment_type igs_en_su_attempt.no_assessment_ind%TYPE
272 ) IS
273 SELECT sua.person_id,
274 sua.course_cd,
275 sua.unit_cd,
276 sua.version_number,
277 sua.cal_type,
278 sua.ci_sequence_number,
279 sua.location_cd,
280 sua.unit_class,
281 sua.ci_start_dt,
282 sua.ci_end_dt,
283 sua.uoo_id,
284 sua.no_assessment_ind
285 FROM igs_en_su_attempt_all sua,
286 igs_ps_unit_ver_all uv,
287 igs_as_unit_class_all uc
288 WHERE ((cp_assess_cal_type IS NOT NULL
289 AND EXISTS (
290 SELECT 'x'
291 FROM igs_ca_inst_rel
292 WHERE sub_cal_type = sua.cal_type
293 AND sub_ci_sequence_number = sua.ci_sequence_number
294 AND sup_cal_type = cp_assess_cal_type
295 AND sup_ci_sequence_number = cp_assess_sequence_number)
296 )
297 OR (cp_assess_cal_type IS NULL)
298 )
299 AND ((cp_teach_cal_type IS NOT NULL
300 AND cp_teach_sequence_number IS NOT NULL
301 AND sua.cal_type = cp_teach_cal_type
302 AND sua.ci_sequence_number = cp_teach_sequence_number
303 )
304 OR (cp_teach_cal_type IS NULL)
305 )
306 AND sua.unit_attempt_status = 'ENROLLED'
307 AND (((cp_no_assessment_type <> '%')
308 AND ((cp_no_assessment_type = 'A'
309 AND sua.no_assessment_ind <> 'Y')
310 OR (cp_no_assessment_type = 'N'
311 AND sua.no_assessment_ind <> 'N')
312 )
313 )
314 OR (cp_no_assessment_type = '%')
315 )
316 AND sua.unit_cd LIKE cp_unit_cd
317 AND sua.location_cd LIKE cp_location_cd
318 AND sua.unit_class LIKE cp_unit_class
319 AND NOT EXISTS (
320 SELECT 1
321 FROM igs_as_su_stmptout_all suao
322 WHERE suao.person_id = sua.person_id
323 AND suao.course_cd = sua.course_cd
324 AND suao.uoo_id = sua.uoo_id)
325 AND uv.unit_cd = sua.unit_cd
326 AND uv.version_number = sua.version_number
327 AND uv.owner_org_unit_cd LIKE cp_org_unit_cd
328 AND (((cp_assessable_type <> '%')
329 AND ((cp_assessable_type = 'A'
330 AND uv.assessable_ind = 'Y')
331 OR (cp_assessable_type = 'N'
332 AND uv.assessable_ind = 'N')
333 )
334 )
335 OR (cp_assessable_type = '%')
336 )
337 AND uc.unit_class = sua.unit_class
338 AND uc.unit_mode LIKE cp_unit_mode;
339 --
340 -- Changed the defn of this cursor to exclude audited grades
341 --
342 CURSOR c_grading_schema_grade (
343 cp_grading_schema igs_as_grd_schema.grading_schema_cd%TYPE,
344 cp_gs_version_number igs_as_grd_schema.version_number%TYPE
345 ) IS
346 SELECT gsg.grade
347 FROM igs_as_grd_sch_grade gsg
348 WHERE gsg.grading_schema_cd = cp_grading_schema
349 AND gsg.version_number = cp_gs_version_number
350 AND gsg.dflt_outstanding_ind = 'Y'
351 AND gsg.s_result_type <> 'AUDIT'
352 AND gsg.closed_ind = 'N';
353 --
354 v_grading_schema_grade_rec c_grading_schema_grade%ROWTYPE;
355 --
356 -- Added one more cursor to handle audited grades
357 --
358 CURSOR c_grading_schema_audit_grade (
359 cp_grading_schema igs_as_grd_schema.grading_schema_cd%TYPE,
360 cp_gs_version_number igs_as_grd_schema.version_number%TYPE
361 ) IS
362 SELECT gsg.grade
363 FROM igs_as_grd_sch_grade gsg
364 WHERE gsg.grading_schema_cd = cp_grading_schema
365 AND gsg.version_number = cp_gs_version_number
366 AND gsg.dflt_outstanding_ind = 'Y'
367 AND gsg.s_result_type = 'AUDIT'
368 AND gsg.closed_ind = 'N';
369 --
370 --
371 --
372 PROCEDURE assp_insertgrade (
373 p_person_id igs_en_su_attempt.person_id%TYPE,
374 p_course_cd igs_en_su_attempt.course_cd%TYPE,
375 p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
376 p_cal_type igs_en_su_attempt.cal_type%TYPE,
377 p_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
378 p_ci_start_dt igs_en_su_attempt.ci_start_dt%TYPE,
379 p_ci_end_dt igs_en_su_attempt.ci_end_dt%TYPE,
380 p_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE,
381 p_s_grade_creation_method_type igs_as_su_stmptout.s_grade_creation_method_type%TYPE,
382 p_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
383 p_version_number igs_as_su_stmptout.version_number%TYPE,
384 p_grade igs_as_su_stmptout.grade%TYPE,
385 p_finalised_outcome_ind igs_as_su_stmptout.finalised_outcome_ind%TYPE,
386 p_uoo_id igs_en_su_attempt.uoo_id%TYPE
387 ) IS
388 --
389 CURSOR cur_person_detail (cp_person_id NUMBER) IS
390 SELECT party_number
391 FROM hz_parties
392 WHERE party_id = cp_person_id;
393 rec_person_detail cur_person_detail%ROWTYPE;
394 --
395 BEGIN
396 --
397 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
398 fnd_log.string (
399 fnd_log.level_procedure,
400 g_module_head || 'assp_ins_admin_grds.assp_insertgrade.begin',
401 'In Params : p_person_id => ' || p_person_id || ';' ||
402 'p_course_cd => ' || p_course_cd || ';' ||
403 'p_unit_cd => ' || p_unit_cd || ';' ||
404 'p_cal_type => ' || p_cal_type || ';' ||
405 'p_ci_sequence_number => ' || p_ci_sequence_number || ';' ||
406 'p_ci_start_dt => ' || p_ci_start_dt || ';' ||
407 'p_ci_end_dt => ' || p_ci_end_dt || ';' ||
408 'p_outcome_dt => ' || p_outcome_dt || ';' ||
409 'p_s_grade_creation_method_type => ' || p_s_grade_creation_method_type || ';' ||
410 'p_grading_schema_cd => ' || p_grading_schema_cd || ';' ||
411 'p_version_number => ' || p_version_number || ';' ||
412 'p_grade => ' || p_grade || ';' ||
413 'p_finalised_outcome_ind => ' || p_finalised_outcome_ind || ';' ||
414 'p_uoo_id => ' || p_uoo_id
415 );
416 END IF;
417 --
418 x_rowid := NULL;
419 BEGIN
420 --
421 OPEN cur_person_detail (p_person_id);
422 FETCH cur_person_detail INTO rec_person_detail;
423 CLOSE cur_person_detail;
424 --
425 fnd_file.put_line (fnd_file.log, rec_person_detail.party_number || '; ' || p_course_cd || '; ' || p_uoo_id || '; ' || p_unit_cd || '; ' || p_cal_type || '; ' || p_ci_sequence_number);
426 --
427 SAVEPOINT s_before_suao_creation;
428 igs_as_su_stmptout_pkg.insert_row (
429 x_rowid => x_rowid,
430 x_org_id => p_org_id,
431 x_person_id => p_person_id,
432 x_course_cd => p_course_cd,
433 x_unit_cd => p_unit_cd,
434 x_cal_type => p_cal_type,
435 x_ci_sequence_number => p_ci_sequence_number,
436 x_outcome_dt => p_outcome_dt,
437 x_ci_start_dt => p_ci_start_dt,
438 x_ci_end_dt => p_ci_end_dt,
439 x_grading_schema_cd => p_grading_schema_cd,
440 x_version_number => p_version_number,
441 x_grade => p_grade,
442 x_s_grade_creation_method_type => p_s_grade_creation_method_type,
443 x_finalised_outcome_ind => p_finalised_outcome_ind,
444 x_mark => NULL,
445 x_number_times_keyed => NULL,
446 x_translated_grading_schema_cd => NULL,
447 x_translated_version_number => NULL,
448 x_translated_grade => NULL,
449 x_translated_dt => NULL,
450 x_mode => 'R',
451 x_attribute_category => NULL,
452 x_attribute1 => NULL,
453 x_attribute2 => NULL,
454 x_attribute3 => NULL,
455 x_attribute4 => NULL,
456 x_attribute5 => NULL,
457 x_attribute6 => NULL,
458 x_attribute7 => NULL,
459 x_attribute8 => NULL,
460 x_attribute9 => NULL,
461 x_attribute10 => NULL,
462 x_attribute11 => NULL,
463 x_attribute12 => NULL,
464 x_attribute13 => NULL,
465 x_attribute14 => NULL,
466 x_attribute15 => NULL,
467 x_attribute16 => NULL,
468 x_attribute17 => NULL,
469 x_attribute18 => NULL,
470 x_attribute19 => NULL,
471 x_attribute20 => NULL,
472 x_uoo_id => p_uoo_id,
473 x_mark_capped_flag => 'N',
474 x_show_on_academic_histry_flag => 'Y',
475 x_release_date => NULL,
476 x_manual_override_flag => 'N',
477 x_incomp_deadline_date => NULL,
478 x_incomp_grading_schema_cd => NULL,
479 x_incomp_version_number => NULL,
480 x_incomp_default_grade => NULL,
481 x_incomp_default_mark => NULL,
482 x_comments => NULL,
483 x_grading_period_cd => 'FINAL'
484 );
485 --
486 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
487 fnd_log.string (
488 fnd_log.level_statement,
489 g_module_head || 'assp_ins_admin_grds.assp_insertgrade.created_outcome',
490 'Created Outcome for ' || p_person_id || ';' || p_course_cd || ';' || p_uoo_id
491 );
492 END IF;
493 --
494 EXCEPTION
495 WHEN OTHERS THEN
496 ROLLBACK TO s_before_suao_creation;
497 fnd_file.put_line (fnd_file.log, ' -> ' || SQLERRM);
498 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
499 fnd_log.string (
500 fnd_log.level_exception,
501 g_module_head || 'assp_ins_admin_grds.assp_insertgrade.insert_exception',
502 'SQLERRM => ' || SQLERRM
503 );
504 END IF;
505 END;
506 EXCEPTION
507 WHEN OTHERS THEN
508 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
509 fnd_log.string (
510 fnd_log.level_exception,
511 g_module_head || 'assp_ins_admin_grds.assp_insertgrade.exception',
512 'SQLERRM => ' || SQLERRM
513 );
514 END IF;
515 END assp_insertgrade;
516 --
517 --
518 --
519 PROCEDURE assp_findgrade (
520 p_person_id igs_en_su_attempt.person_id%TYPE,
521 p_course_cd igs_en_su_attempt.course_cd%TYPE,
522 p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
523 p_cal_type igs_en_su_attempt.cal_type%TYPE,
524 p_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
525 p_ci_start_dt igs_en_su_attempt.ci_start_dt%TYPE,
526 p_ci_end_dt igs_en_su_attempt.ci_end_dt%TYPE,
527 p_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE,
528 p_s_grade_creation_method_type igs_as_su_stmptout.s_grade_creation_method_type%TYPE,
529 p_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
530 p_version_number igs_as_su_stmptout.version_number%TYPE,
531 p_grade igs_as_su_stmptout.grade%TYPE,
532 p_finalised_outcome_ind igs_as_su_stmptout.finalised_outcome_ind%TYPE,
533 j BINARY_INTEGER,
534 p_uoo_id igs_en_su_attempt.uoo_id%TYPE
535 ) IS
536 --
537 CURSOR c_grading_schema_grade (
538 cp_grading_schema igs_as_grd_sch_grade.grading_schema_cd%TYPE,
539 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE,
540 cp_grade igs_as_grd_sch_grade.grade%TYPE
541 ) IS
542 SELECT gsg.grade
543 FROM igs_as_grd_sch_grade gsg
544 WHERE gsg.grading_schema_cd = cp_grading_schema
545 AND gsg.version_number = cp_gs_version_number
546 AND gsg.grade = cp_grade;
547 --
548 BEGIN
549 --
550 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
551 fnd_log.string (
552 fnd_log.level_procedure,
553 g_module_head || 'assp_ins_admin_grds.assp_findgrade.begin',
554 'In Params : p_person_id => ' || p_person_id || ';' ||
555 'p_course_cd => ' || p_course_cd || ';' ||
556 'p_unit_cd => ' || p_unit_cd || ';' ||
557 'p_cal_type => ' || p_cal_type || ';' ||
558 'p_ci_sequence_number => ' || p_ci_sequence_number || ';' ||
559 'p_ci_start_dt => ' || p_ci_start_dt || ';' ||
560 'p_ci_end_dt => ' || p_ci_end_dt || ';' ||
561 'p_outcome_dt => ' || p_outcome_dt || ';' ||
562 'p_s_grade_creation_method_type => ' || p_s_grade_creation_method_type || ';' ||
563 'p_grading_schema_cd => ' || p_grading_schema_cd || ';' ||
564 'p_version_number => ' || p_version_number || ';' ||
565 'p_grade => ' || p_grade || ';' ||
566 'p_finalised_outcome_ind => ' || p_finalised_outcome_ind || ';' ||
567 'p_uoo_id => ' || p_uoo_id
568 );
569 END IF;
570 --
571 IF (p_grade IS NOT NULL) THEN
572 OPEN c_grading_schema_grade (p_grading_schema_cd, p_version_number, p_grade);
573 FETCH c_grading_schema_grade INTO v_grade;
574 IF (c_grading_schema_grade%NOTFOUND) THEN
575 CLOSE c_grading_schema_grade;
576 --
577 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
578 fnd_log.string (
579 fnd_log.level_statement,
580 g_module_head || 'assp_ins_admin_grds.assp_findgrade.no_processing',
581 'Grade to be inserted does not belong to the Grading Schema so skipping the outcome creation'
582 );
583 END IF;
584 --
585 ELSE
586 CLOSE c_grading_schema_grade;
587 --
588 assp_insertgrade (
589 p_person_id,
590 p_course_cd,
591 p_unit_cd,
592 p_cal_type,
593 p_ci_sequence_number,
594 p_ci_start_dt,
595 p_ci_end_dt,
596 p_outcome_dt,
597 p_s_grade_creation_method_type,
598 p_grading_schema_cd,
599 p_version_number,
600 v_grade,
601 p_finalised_outcome_ind,
602 p_uoo_id
603 );
604 END IF;
605 END IF;
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
609 fnd_log.string (
610 fnd_log.level_exception,
611 g_module_head || 'assp_ins_admin_grds.assp_findgrade.exception',
612 'SQLERRM => ' || SQLERRM
613 );
614 END IF;
615 END assp_findgrade;
616 --
617 BEGIN -- Main procedure
618 --
619 SAVEPOINT s_before_insert;
620 --
621 FOR v_stu_unit_atmpt_rec IN c_stu_unit_atmpt (
622 p_assess_cal_type,
623 p_assess_ci_sequence_number,
624 p_teach_cal_type,
625 p_teach_ci_sequence_number,
626 p_org_unit_cd,
627 p_unit_cd,
628 p_location_cd,
629 p_unit_mode,
630 p_unit_class,
631 p_assessable_type,
632 p_no_assessment_type
633 ) LOOP
634 BEGIN
635 --
636 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
637 fnd_log.string (
638 fnd_log.level_statement,
639 g_module_head || 'assp_ins_admin_grds.c_stu_unit_atmpt',
640 v_stu_unit_atmpt_rec.person_id || ';' || v_stu_unit_atmpt_rec.course_cd || ';' ||
641 v_stu_unit_atmpt_rec.unit_cd || ';' || v_stu_unit_atmpt_rec.version_number || ';' ||
642 v_stu_unit_atmpt_rec.cal_type || ';' || v_stu_unit_atmpt_rec.ci_sequence_number || ';' ||
643 v_stu_unit_atmpt_rec.location_cd || ';' || v_stu_unit_atmpt_rec.unit_class || ';' ||
644 v_stu_unit_atmpt_rec.ci_start_dt || ';' || v_stu_unit_atmpt_rec.ci_end_dt || ';' ||
645 v_stu_unit_atmpt_rec.uoo_id || ';' || v_stu_unit_atmpt_rec.no_assessment_ind
646 );
647 END IF;
648 --
649 -- Determine the relevant grading schema version for the student unit attempt
650 --
651 IF (igs_as_gen_003.assp_get_sua_gs (
652 v_stu_unit_atmpt_rec.person_id,
653 v_stu_unit_atmpt_rec.course_cd,
654 v_stu_unit_atmpt_rec.unit_cd,
655 v_stu_unit_atmpt_rec.version_number,
656 v_stu_unit_atmpt_rec.cal_type,
657 v_stu_unit_atmpt_rec.ci_sequence_number,
658 v_stu_unit_atmpt_rec.location_cd,
659 v_stu_unit_atmpt_rec.unit_class,
660 v_grading_schema,
661 v_gs_version_number
662 )) THEN
663 --
664 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
665 fnd_log.string (
666 fnd_log.level_statement,
667 g_module_head || 'assp_ins_admin_grds.grading_schema_derivation',
668 v_stu_unit_atmpt_rec.person_id || ';' || v_stu_unit_atmpt_rec.course_cd || ';' ||
669 v_stu_unit_atmpt_rec.uoo_id || ';' || v_grading_schema || ';' || v_gs_version_number
670 );
671 END IF;
672 --
673 IF (p_insert_default_ind = 'Y') THEN
674 IF (v_stu_unit_atmpt_rec.no_assessment_ind = 'N') THEN -- Non-Audit Attempt
675 v_record_found := FALSE;
676 --
677 -- Attempt to locate the default grade within the derived grading schema version
678 --
679 OPEN c_grading_schema_grade (v_grading_schema, v_gs_version_number);
680 FETCH c_grading_schema_grade INTO v_grading_schema_grade_rec;
681 IF (c_grading_schema_grade%FOUND) THEN
682 v_record_found := TRUE;
683 v_grade := v_grading_schema_grade_rec.grade;
684 --
685 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
686 fnd_log.string (
687 fnd_log.level_statement,
688 g_module_head || 'assp_ins_admin_grds.derived_non_audit_grade',
689 v_stu_unit_atmpt_rec.person_id || ';' || v_stu_unit_atmpt_rec.course_cd || ';' ||
690 v_stu_unit_atmpt_rec.uoo_id || ';' || v_grading_schema || ';' ||
691 v_gs_version_number || ';' || v_grade
692 );
693 END IF;
694 --
695 END IF;
696 CLOSE c_grading_schema_grade;
697 --
698 IF (v_record_found = FALSE) THEN
699 IF (p_grade IS NOT NULL) THEN
700 assp_findgrade (
701 v_stu_unit_atmpt_rec.person_id,
702 v_stu_unit_atmpt_rec.course_cd,
703 v_stu_unit_atmpt_rec.unit_cd,
704 v_stu_unit_atmpt_rec.cal_type,
705 v_stu_unit_atmpt_rec.ci_sequence_number,
706 v_stu_unit_atmpt_rec.ci_start_dt,
707 v_stu_unit_atmpt_rec.ci_end_dt,
708 SYSDATE,
709 'SYSTEM',
710 v_grading_schema,
711 v_gs_version_number,
712 p_grade,
713 p_finalised_ind,
714 i,
715 v_stu_unit_atmpt_rec.uoo_id
716 );
717 END IF;
718 ELSIF (v_record_found = TRUE) THEN
719 v_insert_grade := v_grade;
720 assp_insertgrade (
721 v_stu_unit_atmpt_rec.person_id,
722 v_stu_unit_atmpt_rec.course_cd,
723 v_stu_unit_atmpt_rec.unit_cd,
724 v_stu_unit_atmpt_rec.cal_type,
725 v_stu_unit_atmpt_rec.ci_sequence_number,
726 v_stu_unit_atmpt_rec.ci_start_dt,
727 v_stu_unit_atmpt_rec.ci_end_dt,
728 SYSDATE,
729 'SYSTEM',
730 v_grading_schema,
731 v_gs_version_number,
732 v_insert_grade,
733 p_finalised_ind,
734 v_stu_unit_atmpt_rec.uoo_id
735 );
736 END IF;
737 -- added the else condition to handle audit grades for audited attempts--
738 ELSE -- Audit Attempt
739 v_record_found := FALSE;
740 OPEN c_grading_schema_audit_grade (v_grading_schema, v_gs_version_number);
741 FETCH c_grading_schema_audit_grade INTO v_grading_schema_grade_rec;
742 IF (c_grading_schema_audit_grade%FOUND) THEN
743 v_record_found := TRUE;
744 v_audit_grade := v_grading_schema_grade_rec.grade;
745 --
746 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
747 fnd_log.string (
748 fnd_log.level_statement,
749 g_module_head || 'assp_ins_admin_grds.derived_audit_grade',
750 v_stu_unit_atmpt_rec.person_id || ';' || v_stu_unit_atmpt_rec.course_cd || ';' ||
751 v_stu_unit_atmpt_rec.uoo_id || ';' || v_grading_schema || ';' ||
752 v_gs_version_number || ';' || v_audit_grade
753 );
754 END IF;
755 --
756 END IF;
757 CLOSE c_grading_schema_audit_grade;
758 --
759 IF (v_record_found = FALSE) THEN
760 IF (p_audit_grade IS NOT NULL) THEN
761 assp_findgrade (
762 v_stu_unit_atmpt_rec.person_id,
763 v_stu_unit_atmpt_rec.course_cd,
764 v_stu_unit_atmpt_rec.unit_cd,
765 v_stu_unit_atmpt_rec.cal_type,
766 v_stu_unit_atmpt_rec.ci_sequence_number,
767 v_stu_unit_atmpt_rec.ci_start_dt,
768 v_stu_unit_atmpt_rec.ci_end_dt,
769 SYSDATE,
770 'SYSTEM',
771 v_grading_schema,
772 v_gs_version_number,
773 p_audit_grade,
774 p_finalised_ind,
775 i,
776 v_stu_unit_atmpt_rec.uoo_id
777 );
778 END IF;
779 ELSIF (v_record_found = TRUE) THEN
780 v_insert_grade := v_audit_grade;
781 assp_insertgrade (
782 v_stu_unit_atmpt_rec.person_id,
783 v_stu_unit_atmpt_rec.course_cd,
784 v_stu_unit_atmpt_rec.unit_cd,
785 v_stu_unit_atmpt_rec.cal_type,
786 v_stu_unit_atmpt_rec.ci_sequence_number,
787 v_stu_unit_atmpt_rec.ci_start_dt,
788 v_stu_unit_atmpt_rec.ci_end_dt,
789 SYSDATE,
790 'SYSTEM',
791 v_grading_schema,
792 v_gs_version_number,
793 v_insert_grade,
794 p_finalised_ind,
795 v_stu_unit_atmpt_rec.uoo_id
796 );
797 END IF;
798 END IF;
799 -- added by lkaki to check whether to get the grade from audit attempt or non-audit attempt---
800 ELSIF (p_insert_default_ind = 'N') THEN
801 IF (v_stu_unit_atmpt_rec.no_assessment_ind = 'N') THEN -- Non-Audit Attempt
802 assp_findgrade (
803 v_stu_unit_atmpt_rec.person_id,
804 v_stu_unit_atmpt_rec.course_cd,
805 v_stu_unit_atmpt_rec.unit_cd,
806 v_stu_unit_atmpt_rec.cal_type,
807 v_stu_unit_atmpt_rec.ci_sequence_number,
808 v_stu_unit_atmpt_rec.ci_start_dt,
809 v_stu_unit_atmpt_rec.ci_end_dt,
810 SYSDATE,
811 'SYSTEM',
812 v_grading_schema,
813 v_gs_version_number,
814 p_grade,
815 p_finalised_ind,
816 i,
817 v_stu_unit_atmpt_rec.uoo_id
818 );
819 ELSE -- Audit Attempt
820 assp_findgrade (
821 v_stu_unit_atmpt_rec.person_id,
822 v_stu_unit_atmpt_rec.course_cd,
823 v_stu_unit_atmpt_rec.unit_cd,
824 v_stu_unit_atmpt_rec.cal_type,
825 v_stu_unit_atmpt_rec.ci_sequence_number,
826 v_stu_unit_atmpt_rec.ci_start_dt,
827 v_stu_unit_atmpt_rec.ci_end_dt,
828 SYSDATE,
829 'SYSTEM',
830 v_grading_schema,
831 v_gs_version_number,
832 p_audit_grade,
833 p_finalised_ind,
834 i,
835 v_stu_unit_atmpt_rec.uoo_id
836 );
837 END IF;
838 END IF;
839 END IF;
840 EXCEPTION
841 WHEN OTHERS THEN
842 --
843 -- Log the error and skip to the next record
844 --
845 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
846 fnd_log.string (
847 fnd_log.level_exception,
848 g_module_head || 'assp_ins_admin_grds.assp_findgrade.exception',
849 'Skipping to next record due to error => ' || SQLERRM
850 );
851 END IF;
852 END;
853 END LOOP;
854 --
855 COMMIT;
856 --
857 EXCEPTION
858 WHEN OTHERS THEN
859 ROLLBACK TO s_before_insert;
860 retcode := 2;
861 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
862 fnd_log.string (
863 fnd_log.level_exception,
864 g_module_head || 'assp_ins_admin_grds.exception',
865 'SQLERRM => ' || SQLERRM
866 );
867 END IF;
868 END;
869 END assp_ins_admin_grds;
870
871 PROCEDURE assp_ins_aia(
872 p_ass_id IN IGS_AS_ITEM_ASSESSOR.ass_id%TYPE ,
873 p_person_id IN IGS_AS_ITEM_ASSESSOR.person_id%TYPE ,
874 p_ass_assessor_type IN IGS_AS_ITEM_ASSESSOR.ASS_ASSESSOR_TYPE%TYPE ,
875 p_primary_assessor_ind IN IGS_AS_ITEM_ASSESSOR.primary_assessor_ind%TYPE ,
876 p_item_limit IN IGS_AS_ITEM_ASSESSOR.item_limit%TYPE ,
877 p_location_cd IN IGS_AS_ITEM_ASSESSOR.location_cd%TYPE ,
878 p_unit_mode IN IGS_AS_ITEM_ASSESSOR.UNIT_MODE%TYPE ,
879 p_unit_class IN IGS_AS_ITEM_ASSESSOR.UNIT_CLASS%TYPE ,
880 p_comments IN IGS_AS_ITEM_ASSESSOR.comments%TYPE )
881 IS
882 V_SEQUeNCE_NUMBER number;
883 BEGIN -- assp_ins_aia
884 -- Insert a record into the IGS_AS_ITEM_ASSESSOR table
885 --DECLARE
886 --BEGIN
887 select IGS_AS_ITEM_ASSESSOR_SEQ_NUM_S.NEXTVAL into V_SEQUENCE_NUMBER FROM DUAL;
888 x_rowid := NULL ;
889 IGS_AS_ITEM_ASSESSOR_PKG.INSERT_ROW(
890 X_ROWID => x_rowid,
891 X_ASS_ID => p_ass_id,
892 X_PERSON_ID => p_person_id,
893 X_SEQUENCE_NUMBER => V_SEQUENCE_NUMBER,
894 X_ASS_ASSESSOR_TYPE => p_ass_assessor_type,
895 X_PRIMARY_ASSESSOR_IND => p_primary_assessor_ind,
896 X_ITEM_LIMIT => p_item_limit,
897 X_LOCATION_CD => p_location_cd,
898 X_UNIT_MODE => p_unit_mode,
899 X_UNIT_CLASS => p_unit_class,
900 X_COMMENTS => p_comments,
901 X_MODE => 'R'
902 );
903 --END;
904 EXCEPTION
905 WHEN OTHERS THEN
906 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
907 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_ins_aia');
908 IGS_GE_MSG_STACK.ADD;
909 App_Exception.Raise_Exception;
910 END assp_ins_aia;
911 PROCEDURE assp_ins_aia_default(
912 p_ass_id IN IGS_AS_ITEM_ASSESSOR.ass_id%TYPE ,
913 p_unit_cd IN IGS_PS_UNIT_VER_ALL.unit_cd%TYPE ,
914 p_version_number IN IGS_PS_UNIT_VER_ALL.version_number%TYPE )
915 IS
916 BEGIN -- assp_ins_aia_default
917 -- Insert a default record into the IGS_AS_ITEM_ASSESSOR table
918 DECLARE
919 cst_yes CONSTANT CHAR := 'Y';
920 cst_no CONSTANT CHAR := 'N';
921 cst_unit_coord CONSTANT VARCHAR2(20) := 'unit CO-ORDINATOR.';
922 CURSOR c_aia(
923 cp_ass_id IGS_AS_ITEM_ASSESSOR.ass_id%TYPE) IS
924 SELECT COUNT(*)
925 FROM IGS_AS_ITEM_ASSESSOR
926 WHERE ass_id = cp_ass_id;
927 CURSOR c_uv(
928 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
929 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
930 SELECT coord_person_id
931 FROM IGS_PS_UNIT_VER
932 WHERE unit_cd = cp_unit_cd AND
933 version_number = cp_version_number;
934 CURSOR c_asst IS
935 SELECT ASS_ASSESSOR_TYPE
936 FROM IGS_AS_ASSESSOR_TYPE
937 WHERE dflt_ind = cst_yes and
938 closed_ind = cst_no;
939 v_aia_count NUMBER;
940 v_uv_rec c_uv%ROWTYPE;
941 v_asst_rec c_asst%ROWTYPE;
942 v_ass_id IGS_AS_ITEM_ASSESSOR.ass_id%TYPE;
943 v_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE;
944 v_version_number IGS_PS_UNIT_VER.version_number%TYPE;
945 BEGIN
946 -- Initialise all the variables
947 v_ass_id := p_ass_id;
948 v_unit_cd := p_unit_cd;
949 v_version_number := p_version_number;
950 -- Check that an assessor does not already exist for the item
951 OPEN c_aia(
952 v_ass_id);
953 FETCH c_aia INTO v_aia_count;
954 CLOSE c_aia;
955 IF (v_aia_count > 0) THEN
956 RETURN;
957 END IF;
958 -- Fetch the unit coordinator
959 OPEN c_uv(
960 v_unit_cd,
961 v_version_number);
962 FETCH c_uv INTO v_uv_rec;
963 CLOSE c_uv;
964 -- Fetch the default assessor type
965 OPEN c_asst;
966 FETCH c_asst INTO v_asst_rec;
967 CLOSE c_asst;
968 -- Call the generic IGS_AS_GEN_006.assp_ins_aia routine
969 IGS_AS_GEN_006.assp_ins_aia (
970 v_ass_id,
971 v_uv_rec.coord_person_id,
972 v_asst_rec.ASS_ASSESSOR_TYPE,
973 cst_yes,
974 NULL,
975 NULL,
976 NULL,
977 NULL,
978 cst_unit_coord);
979 END;
980 EXCEPTION
981 WHEN OTHERS THEN
982 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
983 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_ins_aia_default');
984 IGS_GE_MSG_STACK.ADD;
985 App_Exception.Raise_Exception;
986 END assp_ins_aia_default;
987
988 PROCEDURE assp_ins_ai_cvr_sht(
989 errbuf OUT NOCOPY VARCHAR2,
990 retcode OUT NOCOPY NUMBER,
991 p_acad_calendar IN VARCHAR2,
992 p_teach_calendar IN VARCHAR2,
993 p_crs_cd IN VARCHAR2 ,
994 p_unt_cd IN VARCHAR2 ,
995 p_lctn_cd IN VARCHAR2 ,
996 p_unt_cls IN VARCHAR2 ,
997 p_unt_md IN VARCHAR2 ,
998 p_person_id IN NUMBER ,
999 p_ass_id IN NUMBER ,
1000 p_reprdc IN VARCHAR2 DEFAULT 'N',
1001 p_org_id IN NUMBER
1002 )
1003 IS
1004 p_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1005 p_acad_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1006 p_teach_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1007 p_teach_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1008 p_course_cd IGS_PS_COURSE.COURSE_CD%TYPE;
1009 p_unit_cd IGS_PS_UNIT.UNIT_CD%TYPE;
1010 p_location_cd IGS_AD_LOCATION.LOCATION_CD%TYPE;
1011 p_unit_class IGS_AS_UNIT_CLASS.UNIT_CLASS%TYPE;
1012 p_unit_mode IGS_AS_UNIT_MODE.UNIT_MODE%TYPE;
1013 p_reproduce IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE ;
1014 --------------------------------------------------------------------------------------------------------------------------
1015 --Change History:
1016 --Who When What
1017 --Aiyer 08-APR-2002 Bug No. 2124034. The parameter p_reproduce was also added as a hidden parameter in the
1018 -- concurrent job IGSASJ05 Produce Student Assignment Cover Sheet with a default value as 'NO'.
1019 -- In the package body of porocedure too it was made to have a default value of 'NO'.
1020 -------------------------------------------------------------------------------------------------------------------------
1021
1022 BEGIN
1023 -- assp_ins_ai_cvr_sht
1024 -- This module will create extraction records that will be used for the
1025 -- production of Assignment Coversheets (Attachments).
1026 -- Coversheet details will be produced for all students with the following
1027 -- criteria:
1028 -- * A tracking item assigned to their IGS_AS_SU_ATMPT_ITM record.
1029 -- * The assignment must be valid for the student.
1030 -- * The student is ENROLLED in the unit.
1031 -- * If not re-producing, then student cannot have had a coversheet produced
1032 -- previously for the assignment in this unit and teaching period.
1033 -- * If re-producing, student must already have a tracking item assigned for
1034 -- the assignment.
1035 -- This module will have a mandatory relationship with ASSR3610
1036 -- (assp_ins_suaai_tri), which assign tracking items to assignments. It
1037 -- will require the re-production parameter to be set to 'N'.
1038 -- A second definition in the Job Scheduling facility will allow this process
1039 -- to be called independently with the re-production parameter set to 'Y'.
1040 -- set org id
1041
1042 igs_ge_gen_003.set_org_id(p_org_id);
1043
1044 p_course_cd := NVL(p_crs_cd, '%');
1045 p_unit_cd := NVL(p_unt_cd,'%');
1046 p_location_cd := NVL(p_lctn_cd,'%');
1047 p_unit_class := NVL(p_unt_cls,'%');
1048 p_unit_mode := NVL(p_unt_md,'%');
1049 p_reproduce := p_reprdc;
1050
1051
1052 --Block for Parameter Validation/Splitting of Parameters
1053
1054 retcode:=0;
1055 DECLARE
1056 invalid_parameter EXCEPTION;
1057
1058 BEGIN
1059
1060 /************************* Validation 1 ***************************************************************************/
1061
1062 IF p_acad_calendar IS NULL THEN
1063 p_acad_cal_type := NULL;
1064 p_acad_ci_sequence_number := NULL;
1065 ELSE
1066 p_acad_cal_type := RTRIM(SUBSTR(p_acad_calendar, 101, 10));
1067 p_acad_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_acad_calendar, 112,6)));
1068 END IF;
1069
1070
1071 /************************* Validation 2 ***************************************************************************/
1072
1073 IF p_teach_calendar IS NULL THEN
1074 p_teach_cal_type := NULL;
1075 p_teach_ci_sequence_number := NULL;
1076 ELSE
1077 p_teach_cal_type := RTRIM(SUBSTR(p_teach_calendar, 101, 10));
1078 p_teach_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_teach_calendar, 112,6)));
1079 END IF;
1080
1081 /************************* Validation 3 ****************************************************************************/
1082
1083 -- Validate that the Teaching Calendar parameter passed is subordinate to the Academic Calendar passed
1084
1085 IF ( IGS_EN_GEN_014.ENRS_GET_WITHIN_CI( p_acad_cal_type,
1086 p_acad_ci_sequence_number,
1087 p_teach_cal_type,
1088 p_teach_ci_sequence_number,
1089 'N'
1090 ) <> 'Y'
1091 ) THEN
1092 ERRBUF:=FND_MESSAGE.GET_STRING('IGS', 'IGS_AS_TEACHCAL_NOT_EXIST');
1093 RAISE invalid_parameter;
1094 END IF;
1095
1096 EXCEPTION
1097 WHEN INVALID_PARAMETER THEN
1098 retcode:=2;
1099 RETURN;
1100 END;
1101
1102 --End of Block for Parameter Validation/Splitting of Parameters
1103
1104 DECLARE
1105 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1106 cst_assignment CONSTANT VARCHAR2(15) := 'ASSIGNMENT';
1107 cst_ass_cover CONSTANT VARCHAR2(9) := 'ASS_COVER';
1108 cst_coversheet CONSTANT VARCHAR2(10) := 'COVERSHEET';
1109 cst_sysdate DATE := SYSDATE;
1110
1111
1112 -- Fetch those records which satisfy the foloowing conditions
1113 -- 1. Consider Student Unit Attempt Assessment records for only the latest attempt of a student
1114 -- 2. Student is enrolled
1115 -- 3. Parameters passed are either null or records exist for the passed parameters ( if not null )
1116 -- 4. If Assessment Item Id is passed then the system assessment item type (s_assessment_type)
1117 -- should be equal to ASSIGNMENT .
1118 -- 5. Correspondence Outcome References of 'COVERSHEET' should not exist for the person , unit ,course
1119 -- and assessment
1120
1121 CURSOR c_enrolled_students (
1122 cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1123 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
1124 cp_teach_cal_type IGS_EN_SU_ATTEMPT.CAL_TYPE%TYPE,
1125 cp_teach_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1126 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1127 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1128 cp_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE,
1129 cp_unit_class IGS_EN_SU_ATTEMPT.UNIT_CLASS%TYPE,
1130 cp_unit_mode IGS_AS_UNIT_CLASS.UNIT_MODE%TYPE,
1131 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1132 cp_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE,
1133 cp_reproduce VARCHAR2
1134 )
1135 IS
1136 SELECT
1137 suaai.person_id,
1138 suaai.course_cd,
1139 suaai.unit_cd,
1140 suaai.cal_type,
1141 suaai.ci_sequence_number,
1142 suaai.ass_id,
1143 ai.description,
1144 suaai.creation_dt,
1145 suaai.override_due_dt,
1146 sua.version_number,
1147 sua.location_cd,
1148 sua.unit_class,
1149 uc.unit_mode,
1150 suaai.tracking_id,
1151 sua.uoo_id
1152 FROM
1153 igs_as_su_atmpt_itm suaai,
1154 igs_en_su_attempt sua,
1155 igs_as_assessmnt_itm ai,
1156 igs_as_unit_class uc
1157 WHERE
1158 (cp_person_id IS NULL OR suaai.person_id = cp_person_id)
1159 AND
1160 suaai.logical_delete_dt IS NULL
1161 AND
1162 suaai.attempt_number
1163 =
1164 ( SELECT
1165 MAX(attempt_number)
1166 FROM
1167 IGS_AS_SU_ATMPT_ITM suaai2
1168 WHERE
1169 suaai2.person_id = suaai.person_id AND
1170 suaai2.course_cd = suaai.course_cd AND
1171 suaai2.uoo_id = suaai.uoo_id AND
1172 suaai2.ass_id = suaai.ass_id
1173 ) AND
1174 suaai.person_id = sua.person_id AND
1175 suaai.course_cd = sua.course_cd AND
1176 suaai.uoo_id = sua.uoo_id AND
1177 sua.course_cd LIKE cp_course_cd AND
1178 sua.unit_cd LIKE cp_unit_cd AND
1179 sua.location_cd LIKE cp_location_cd AND
1180 sua.unit_class LIKE cp_unit_class AND
1181 sua.unit_class = uc.unit_class AND
1182 uc.unit_mode LIKE cp_unit_mode AND
1183 sua.unit_attempt_status = cst_enrolled AND
1184 (
1185 cp_teach_cal_type IS NULL
1186 OR
1187 suaai.cal_type = cp_teach_cal_type
1188 ) AND
1189 (
1190 cp_ass_id IS NULL
1191 OR
1192 suaai.ass_id = cp_ass_id
1193 ) AND
1194 (
1195 cp_teach_ci_sequence_number IS NULL
1196 OR
1197 suaai.ci_sequence_number = cp_teach_ci_sequence_number
1198 ) AND
1199 -- check for teaching calendar being passed is subordinate to the academic calendar passed
1200 igs_en_gen_014.enrs_get_within_ci ( cp_acad_cal_type,
1201 cp_acad_ci_sequence_number,
1202 sua.cal_type,
1203 sua.ci_sequence_number,
1204 'Y'
1205 ) = 'Y' AND
1206 suaai.ass_id = ai.ass_id AND
1207 /* If Assessment Item Id is passed then the system assessment item type (s_assessment_type) should be ASSIGNMENT */
1208 igs_as_gen_002.assp_get_ai_s_type(ai.ass_id) = cst_assignment AND
1209 (
1210 (
1211 (cp_reproduce = 'Y' )
1212 AND
1213 (suaai.tracking_id IS NOT NULL)
1214 )
1215 OR
1216 (
1217 (cp_reproduce = 'N')
1218 AND
1219 (suaai.tracking_id IS NOT NULL)
1220 )
1221 AND
1222 (
1223 NOT EXISTS ( SELECT
1224 'X'
1225 FROM
1226 IGS_CO_OU_CO_REF ocr
1227 WHERE
1228 ocr.person_id = suaai.person_id AND
1229 ocr.correspondence_type = cst_coversheet AND
1230 ocr.cal_type = suaai.cal_type AND
1231 ocr.ci_sequence_number = suaai.ci_sequence_number AND
1232 ocr.course_cd = suaai.course_cd AND
1233 ocr.unit_cd = suaai.unit_cd AND
1234 ocr.other_reference = TO_CHAR(suaai.ass_id) || '|' || IGS_GE_DATE.IGSCHARDT(suaai.creation_dt)
1235 )
1236 )
1237 )
1238 ORDER BY suaai.person_id, suaai.unit_cd;
1239
1240 -- Fetch all those records
1241 CURSOR c_suv (
1242 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1243 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1244 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1245 cp_cal_type IGS_AS_SU_ATMPT_ITM.CAL_TYPE%TYPE,
1246 cp_ci_sequence_number IGS_AS_SU_ATMPT_ITM.ci_sequence_number%TYPE,
1247 cp_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE,
1248 cp_uoo_id IGS_AS_SU_ATMPT_ITM.uoo_id%TYPE)
1249 IS
1250 SELECT
1251 uai_due_dt,
1252 uai_reference
1253 FROM
1254 igs_as_uai_sua_v
1255 WHERE
1256 person_id = cp_person_id AND
1257 course_cd = cp_course_cd AND
1258 uoo_id = cp_uoo_id AND
1259 ass_id = cp_ass_id AND
1260 uai_logical_delete_dt IS NULL;
1261
1262 CURSOR c_pe (cp_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE)
1263 IS
1264 SELECT
1265 title,
1266 surname,
1267 given_names,
1268 preferred_given_name
1269 FROM
1270 igs_pe_person
1271 WHERE
1272 person_id = cp_person_id;
1273
1274 -- Retrieve the record only if the person has a valid person address (exists in table and igs_pe_person_addr)
1275 -- and current date (sysdate) is :-
1276 -- Greater than start date and end date is null
1277 -- OR
1278 -- Between the start date and end date
1279
1280 CURSOR c_pa (cp_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE)
1281 IS
1282 SELECT
1283 pa.addr_line_1,
1284 pa.addr_line_2,
1285 pa.addr_line_3,
1286 pa.addr_line_4,
1287 pa.postal_code,
1288 pa.correspondence_ind
1289 FROM
1290 igs_pe_person_addr pa
1291 WHERE
1292 pa.person_id = cp_person_id
1293 AND
1294 ( pa.status = 'A'
1295 AND
1296 (
1297 SYSDATE BETWEEN NVL(pa.start_dt,SYSDATE) AND NVL(pa.end_dt,SYSDATE+1)
1298 )
1299 )
1300 ORDER BY
1301 pa.correspondence_ind DESC;
1302
1303 -- Ordering by correspondence indicator (desc) means that if a
1304 -- correspondence type address exists, then it will be selected first.)
1305
1306 CURSOR c_crv (
1307 cp_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE,
1308 cp_course_cd IGS_AS_SU_ATMPT_ITM.course_cd%TYPE
1309 )
1310 IS
1311 SELECT
1312 crv.version_number,
1313 crv.short_title
1314 FROM
1315 IGS_EN_STDNT_PS_ATT sca,
1316 IGS_PS_VER crv
1317 WHERE
1318 sca.person_id = cp_person_id AND
1319 sca.course_cd = cp_course_cd AND
1320 sca.course_cd = crv.course_cd AND
1321 sca.version_number = crv.version_number;
1322
1323 CURSOR c_uv (
1324 cp_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE,
1325 cp_course_cd IGS_AS_SU_ATMPT_ITM.course_cd%TYPE,
1326 cp_unit_cd IGS_AS_SU_ATMPT_ITM.unit_cd%TYPE,
1327 cp_cal_type IGS_AS_SU_ATMPT_ITM.CAL_TYPE%TYPE,
1328 cp_ci_sequence_number IGS_AS_SU_ATMPT_ITM.ci_sequence_number%TYPE,
1329 cp_uoo_id IGS_AS_SU_ATMPT_ITM.uoo_id%TYPE
1330 )
1331 IS
1332 SELECT
1333 uv.version_number,
1334 uv.short_title
1335 FROM
1336 IGS_EN_SU_ATTEMPT sua,
1337 IGS_PS_UNIT_VER uv
1338 WHERE
1339 sua.person_id = cp_person_id AND
1340 sua.course_cd = cp_course_cd AND
1341 sua.uoo_id = cp_uoo_id AND
1342 uv.unit_cd = sua.unit_cd AND
1343 uv.version_number = sua.version_number;
1344 CURSOR c_um(
1345 cp_unit_mode IGS_AS_UNIT_CLASS.UNIT_MODE%TYPE) IS
1346 SELECT s_unit_mode
1347 FROM IGS_AS_UNIT_MODE
1348 WHERE UNIT_MODE = cp_unit_mode;
1349
1350 CURSOR c_suaav(
1351 cp_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE,
1352 cp_course_cd IGS_AS_SU_ATMPT_ITM.course_cd%TYPE,
1353 cp_unit_cd IGS_AS_SU_ATMPT_ITM.unit_cd%TYPE,
1354 cp_cal_type IGS_AS_SU_ATMPT_ITM.CAL_TYPE%TYPE,
1355 cp_ci_sequence_number IGS_AS_SU_ATMPT_ITM.ci_sequence_number%TYPE,
1356 cp_uoo_id IGS_AS_SU_ATMPT_ITM.uoo_id%TYPE)
1357 IS
1358 SELECT
1359 acad_alternate_code, -- Year
1360 teach_alternate_code -- Semester
1361 FROM
1362 igs_as_sua_ass_v
1363 WHERE
1364 person_id = cp_person_id AND
1365 course_cd = cp_course_cd AND
1366 uoo_id = cp_uoo_id;
1367
1368 CURSOR c_ai(
1369 cp_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE) IS
1370 SELECT description
1371 FROM IGS_AS_ASSESSMNT_ITM
1372 WHERE ass_id = cp_ass_id;
1373
1374 v_last_person_id IGS_EN_SU_ATTEMPT.PERSON_ID%TYPE := 0;
1375 v_last_course_cd IGS_EN_SU_ATTEMPT.COURSE_CD%TYPE := NULL;
1376 v_last_unit_cd IGS_EN_SU_ATTEMPT.UNIT_CD%TYPE := NULL;
1377 v_first_record BOOLEAN := TRUE;
1378 v_ignore_rec BOOLEAN DEFAULT TRUE;
1379 v_log_dt DATE := NULL;
1380 v_suv_rec c_suv%ROWTYPE;
1381 v_suaav_rec c_suaav%ROWTYPE;
1382 v_pe_rec c_pe%ROWTYPE;
1383 v_pa_rec c_pa%ROWTYPE;
1384 v_crv_rec c_crv%ROWTYPE;
1385 v_uv_rec c_uv%ROWTYPE;
1386 v_um_rec c_um%ROWTYPE;
1387 v_ai_rec c_ai%ROWTYPE;
1388 v_record VARCHAR2(2000);
1389 v_message_name VARCHAR2(30);
1390 v_cori_sequence_number IGS_CO_ITM.reference_number%TYPE;
1391 v_ocr_sequence_number IGS_CO_OU_CO_REF.sequence_number%TYPE;
1392 BEGIN
1393 -- Select students who are enrolled and have an assignment assessment type.
1394 -- If p_reproduce = 'Y' then are interested only in the records that have had
1395 -- a sheet produced previously, otherwise if 'N', then only want records where
1396 -- sheet not produced yet.
1397
1398 /*********************************** Validation 4 ***********************************************************************/
1399
1400 -- Pass the parameters to the current procedure as parameters to this cursor
1401 FOR v_enrolled_rec IN c_enrolled_students( p_acad_cal_type,
1402 p_acad_ci_sequence_number,
1403 p_teach_cal_type,
1404 p_teach_ci_sequence_number,
1405 p_course_cd,
1406 p_unit_cd,
1407 p_location_cd,
1408 p_unit_class,
1409 p_unit_mode,
1410 p_person_id,
1411 p_ass_id,
1412 p_reproduce
1413 )
1414 LOOP
1415 v_ignore_rec := FALSE;
1416
1417 /*************************************************** Validation 4.1 *******************************************************/
1418
1419 -- Validate that the assessment item is still valid for the student and get the due date for the item.
1420 -- Select from IGS_AS_UAI_SUA_V.
1421 OPEN c_suv(
1422 v_enrolled_rec.course_cd,
1423 v_enrolled_rec.unit_cd,
1424 v_enrolled_rec.person_id,
1425 v_enrolled_rec.cal_type,
1426 v_enrolled_rec.ci_sequence_number,
1427 v_enrolled_rec.ass_id,
1428 v_enrolled_rec.uoo_id
1429 );
1430
1431 FETCH c_suv INTO v_suv_rec;
1432
1433 -- Only produce an attachment if the assessment item is still valid for the IGS_PE_PERSON.
1434 IF c_suv%NOTFOUND THEN -- Start of if 1
1435
1436 /************************************* Validation 4.1.1 **************************************************************/
1437
1438 CLOSE c_suv;
1439
1440 ELSE
1441
1442 /************************************* Validation 4.2 **************************************************************/
1443
1444 CLOSE c_suv;
1445
1446 -- If the record is the first record then insert an entry of 'ASS_COVER' into the system Log
1447 IF v_first_record THEN -- Start of if 2
1448 -- Create log entry and returns the creation date, creation date is the system date
1449 IGS_GE_GEN_003.GENP_INS_LOG(cst_ass_cover,FND_GLOBAL.CONC_REQUEST_ID,v_log_dt);
1450 v_first_record := FALSE;
1451 END IF; -- End of if 2
1452
1453
1454
1455
1456 /************************************* Validation 4.3 **************************************************************/
1457
1458 -- If the person id is not the last person id then get the details of the person such as title ,surname given_names,preferred_given_name
1459 -- Get the IGS_PE_PERSON name and address details if processing a new id.
1460 IF v_last_person_id <> v_enrolled_rec.person_id THEN --Start of if 3
1461
1462 OPEN c_pe(v_enrolled_rec.person_id);
1463 FETCH c_pe INTO v_pe_rec;
1464 IF c_pe%NOTFOUND THEN -- Start of if 4
1465 CLOSE c_pe;
1466 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1467 IGS_GE_MSG_STACK.ADD;
1468 App_Exception.Raise_Exception;
1469 END IF; -- End of if 4
1470
1471
1472 CLOSE c_pe;
1473
1474 -- For the current person retrieve the person address details only if
1475 -- current date (sysdate) is :-
1476 -- Greater than start date and end date is null
1477 -- OR
1478 -- Between the start date and end date
1479 OPEN c_pa(v_enrolled_rec.person_id);
1480 FETCH c_pa INTO v_pa_rec;
1481
1482 -- If Person Address details are not found then do not produce any Assignment cover sheet data
1483 -- Set the v_ignore_rec flag to TRUE
1484 -- If this flag is set to true then the current would get ignored and no cover sheet would be produced
1485 -- for the current record.
1486
1487 IF c_pa%NOTFOUND THEN -- Start of if 5
1488
1489 CLOSE c_pa;
1490 v_ignore_rec := TRUE;
1491 ELSE
1492 CLOSE c_pa;
1493 v_last_person_id := v_enrolled_rec.person_id;
1494 -- Reset the last IGS_PS_COURSE and IGS_PS_UNIT codes so that if it is a new IGS_PE_PERSON,
1495 -- then the appropriate IGS_PS_COURSE and unit version is retrieved.
1496 v_last_course_cd := NULL;
1497 v_last_unit_cd := NULL;
1498
1499 END IF; -- End of if 5
1500 END IF; -- End of if 3
1501
1502 /************************************* Validation 4.4 **************************************************************/
1503
1504 -- v_last_person_id <> v_enrolled_rec.person_id
1505
1506 IF v_ignore_rec = FALSE THEN -- Start of if 6
1507
1508 v_record := TO_CHAR(v_enrolled_rec.person_id) || '|' || v_pe_rec.TITLE || '|' || v_pe_rec.surname || '|';
1509
1510 IF v_pe_rec.preferred_given_name IS NULL THEN -- Start of if 7
1511 v_record := v_record || v_pe_rec.given_names || '|';
1512 ELSE
1513 v_record := v_record || v_pe_rec.preferred_given_name || '|';
1514 END IF; -- End of if 7
1515
1516 v_record := v_record ||
1517 v_pa_rec.addr_line_1 || '|' ||
1518 v_pa_rec.addr_line_2 || '|' ||
1519 v_pa_rec.addr_line_3 || '|' ||
1520 v_pa_rec.addr_line_4 || '|' ||
1521 v_pa_rec.postal_code || '|' ||
1522 v_pa_rec.correspondence_ind || '|';
1523
1524
1525 -- Get the IGS_PS_COURSE version and description (Short IGS_PE_TITLE)
1526 -- Only if different to the previous IGS_PS_COURSE cd processed.
1527
1528 IF NVL(v_last_course_cd, 'NULL') <> v_enrolled_rec.course_cd THEN -- Start of if 8
1529 OPEN c_crv(
1530 v_enrolled_rec.person_id,
1531 v_enrolled_rec.course_cd);
1532 FETCH c_crv INTO v_crv_rec;
1533
1534 IF c_crv%NOTFOUND THEN -- Start of if 9
1535 CLOSE c_crv;
1536 RAISE NO_DATA_FOUND;
1537 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1538 IGS_GE_MSG_STACK.ADD;
1539 App_Exception.Raise_Exception;
1540 END IF; -- End of if 9
1541
1542 CLOSE c_crv;
1543
1544 END IF; -- End of if 8
1545
1546
1547 -- Get unit description (Short IGS_PE_TITLE)
1548 -- Only if different to the previous unit processed.
1549 IF NVL(v_last_unit_cd, 'NULL') <> v_enrolled_rec.unit_cd THEN -- Start Of if 10
1550 OPEN c_uv(
1551 v_enrolled_rec.person_id,
1552 v_enrolled_rec.course_cd,
1553 v_enrolled_rec.unit_cd,
1554 v_enrolled_rec.CAL_TYPE,
1555 v_enrolled_rec.ci_sequence_number,
1556 v_enrolled_rec.uoo_id
1557 );
1558
1559 FETCH c_uv INTO v_uv_rec;
1560
1561 IF c_uv%NOTFOUND THEN -- Start Of if 11
1562 CLOSE c_uv;
1563 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1564 IGS_GE_MSG_STACK.ADD;
1565 App_Exception.Raise_Exception;
1566 END IF; -- End Of if 11
1567
1568 CLOSE c_uv;
1569 END IF; -- End Of if 10
1570
1571 -- Determine the system unit mode
1572 OPEN c_um(v_enrolled_rec.UNIT_MODE);
1573 FETCH c_um INTO v_um_rec;
1574
1575 IF c_um%NOTFOUND THEN -- Start Of if 12
1576 CLOSE c_um;
1577 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1578 IGS_GE_MSG_STACK.ADD;
1579 App_Exception.Raise_Exception;
1580 END IF; -- End Of if 12
1581
1582 CLOSE c_um;
1583 v_record := v_record ||
1584 v_enrolled_rec.course_cd || '|' ||
1585 TO_CHAR(v_crv_rec.version_number) || '|' ||
1586 v_crv_rec.short_title || '|' ||
1587 v_enrolled_rec.unit_cd || '|' ||
1588 TO_CHAR(v_uv_rec.version_number) || '|' ||
1589 v_uv_rec.short_title || '|' ||
1590 v_enrolled_rec.CAL_TYPE || '|' ||
1591 v_enrolled_rec.ci_sequence_number || '|' ||
1592 v_enrolled_rec.location_cd || '|' ||
1593 v_enrolled_rec.UNIT_CLASS || '|' ||
1594 v_enrolled_rec.UNIT_MODE || '|' ||
1595 v_um_rec.s_unit_mode || '|';
1596
1597 -- Determine the year and semester.
1598 OPEN c_suaav(
1599 v_enrolled_rec.person_id,
1600 v_enrolled_rec.course_cd,
1601 v_enrolled_rec.unit_cd,
1602 v_enrolled_rec.CAL_TYPE,
1603 v_enrolled_rec.ci_sequence_number,
1604 v_enrolled_rec.uoo_id );
1605
1606 FETCH c_suaav INTO v_suaav_rec;
1607
1608
1609 IF c_suaav%NOTFOUND THEN -- Start Of if 13
1610 CLOSE c_suaav;
1611 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1612 IGS_GE_MSG_STACK.ADD;
1613 App_Exception.Raise_Exception;
1614 END IF; -- End Of if 13
1615
1616 CLOSE c_suaav;
1617 v_record := v_record ||
1618 v_suaav_rec.acad_alternate_code || '|' ||
1619 v_suaav_rec.teach_alternate_code || '|';
1620
1621 -- Get the assignment details.
1622 OPEN c_ai(v_enrolled_rec.ass_id);
1623 FETCH c_ai INTO v_ai_rec;
1624
1625 IF c_ai%NOTFOUND THEN --Start Of if 14
1626 CLOSE c_ai;
1627 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
1628 IGS_GE_MSG_STACK.ADD;
1629 App_Exception.Raise_Exception;
1630 END IF; -- End Of if 14
1631
1632 CLOSE c_ai;
1633 v_record := v_record ||
1634 TO_CHAR(v_enrolled_rec.ass_id) || '|' ||
1635 IGS_GE_DATE.IGSCHARDT(v_enrolled_rec.creation_dt) || '|' ||
1636 v_suv_rec.uai_reference || '|' ||
1637 v_ai_rec.description || '|';
1638
1639
1640 -- If Student Assessment Due date is less than the override_due_dt then take the override_due_dt
1641 -- else take the uai_due_dt
1642 IF v_suv_rec.uai_due_dt < NVL(v_enrolled_rec.override_due_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) THEN -- Start Of if 15
1643
1644 v_record := v_record || IGS_GE_DATE.IGSCHAR(v_enrolled_rec.override_due_dt) || '|';
1645 ELSE
1646 v_record := v_record || IGS_GE_DATE.IGSCHAR(v_suv_rec.uai_due_dt) || '|';
1647 END IF; -- End Of if 15
1648
1649 v_record := v_record || TO_CHAR(v_enrolled_rec.tracking_id);
1650
1651 -- Create the log entry for the assignment cover sheet.
1652 IGS_GE_GEN_003.genp_ins_log_entry( cst_ass_cover,
1653 v_log_dt,
1654 FND_GLOBAL.CONC_REQUEST_ID,
1655 NULL,
1656 v_record
1657 );
1658 END IF; -- End Of if 6
1659 END IF; -- End Of if 1
1660
1661 END LOOP;
1662
1663 -- v_enrolled_rec IN c_enrolled_students
1664 -- Commit only after processing all records.
1665 COMMIT;
1666
1667 IF NOT IGS_EN_GEN_002.ENRP_EXT_ENRL_FORM(FND_GLOBAL.CONC_REQUEST_ID,'ASS_COVER',v_message_name) THEN
1668 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS',v_message_name));
1669 END IF;
1670 END;
1671
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 Retcode := 2;
1675 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1676 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1677 END assp_ins_ai_cvr_sht;
1678
1679 PROCEDURE assp_ins_asr1020_tmp(
1680 p_ass_perd_cal_type IN VARCHAR2 ,
1681 p_ass_perd_sequence_number IN NUMBER ,
1682 p_owner_org_unit_cd IN VARCHAR2 ,
1683 p_owner_ou_start_dt IN DATE ,
1684 p_unit_mode IN VARCHAR2 )
1685 IS
1686 BEGIN -- assp_ins_asr1020_tmp
1687 -- This routine will process all unit offering options for an
1688 -- assessment period and determine the counts of assessment items
1689 -- due and recieved from student. It will insert the information
1690 -- into a temporary table that will be used by the report ASSR1020
1691 -- Assignment Due Date Summary Report.
1692 DECLARE
1693 cst_teaching CONSTANT VARCHAR2 (8) := 'TEACHING';
1694 cst_assessment CONSTANT VARCHAR2 (10) := 'ASSESSMENT';
1695 cst_assignment CONSTANT VARCHAR2 (10) := 'ASSIGNMENT';
1696 cst_assign_due CONSTANT VARCHAR2 (10) := 'ASSIGN-DUE';
1697 cst_true CONSTANT VARCHAR2 (4) := 'TRUE';
1698 cst_none CONSTANT VARCHAR2 (4) := 'NONE';
1699 cst_yes CONSTANT CHAR := 'Y';
1700 cst_no CONSTANT CHAR := 'N';
1701 v_session_id NUMBER;
1702 CURSOR c_sess_id IS
1703 SELECT userenv('SESSIONID')
1704 FROM dual;
1705 -- Cursor to select teaching periods under the assessment period.
1706 CURSOR c_teach_perd (
1707 cp_ass_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1708 cp_ass_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1709 SELECT ci2.CAL_TYPE,
1710 ci2.sequence_number
1711 FROM IGS_CA_INST ci,
1712 IGS_CA_INST ci2,
1713 IGS_CA_TYPE ct,
1714 IGS_CA_TYPE ct2
1715 WHERE ci.CAL_TYPE = cp_ass_cal_type AND
1716 ci.sequence_number = cp_ass_sequence_number AND
1717 ci.CAL_TYPE = ct.CAL_TYPE AND
1718 ct.S_CAL_CAT = cst_assessment AND
1719 ci2.CAL_TYPE = ct2.CAL_TYPE AND
1720 ct2.S_CAL_CAT = cst_teaching AND
1721 IGS_EN_GEN_014.enrs_get_within_ci(
1722 cp_ass_cal_type,
1723 cp_ass_sequence_number,
1724 ci2.CAL_TYPE,
1725 ci2.sequence_number,
1726 cst_no) = cst_yes;
1727 -- Cursor to get the relevant unit offering option and assessment item
1728 -- within the nominated teaching period, org unit and mode.
1729 CURSOR c_uoo(
1730 cp_teach_cal_type IGS_PS_UNIT_OFR_OPT.CAL_TYPE%TYPE,
1731 cp_teach_ci_sequence_number IGS_PS_UNIT_OFR_OPT.ci_sequence_number%TYPE,
1732 cp_owner_org_unit_cd IGS_PS_UNIT_VER.owner_org_unit_cd%TYPE,
1733 cp_owner_ou_start_dt IGS_PS_UNIT_VER.owner_ou_start_dt%TYPE,
1734 cp_unit_mode IGS_AS_UNIT_CLASS.UNIT_MODE%TYPE) IS
1735 SELECT uv.owner_org_unit_cd,
1736 uv.owner_ou_start_dt,
1737 uc.UNIT_MODE,
1738 uv.unit_cd,
1739 uv.version_number,
1740 uoo.uoo_id
1741 FROM IGS_PS_UNIT_OFR_OPT uoo,
1742 IGS_PS_UNIT_VER uv,
1743 IGS_AS_UNIT_CLASS uc
1744 WHERE uoo.unit_cd = uv.unit_cd AND
1745 uoo.version_number = uv.version_number AND
1746 uoo.UNIT_CLASS = uc.UNIT_CLASS AND
1747 uoo.CAL_TYPE = cp_teach_cal_type AND
1748 uoo.ci_sequence_number = cp_teach_ci_sequence_number AND
1749 uv.owner_org_unit_cd LIKE cp_owner_org_unit_cd AND
1750 uv.owner_ou_start_dt = NVL(cp_owner_ou_start_dt, uv.owner_ou_start_dt)AND
1751 uc.UNIT_MODE LIKE cp_unit_mode;
1752 -- Cursor to get the relevant assessment item
1753 -- within the nominated unit offering option,
1754 -- teaching period, org unit and mode.
1755 CURSOR c_uai(
1756 cp_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
1757 SELECT uai.ass_id,
1758 uai.due_dt,
1759 IGS_GE_GEN_001.genp_clc_week_end_dt(uai.due_dt) uai_week_ending_dt
1760 FROM IGS_PS_UNIT_OFR_OPT uoo,
1761 -- IGS_AS_UNIT_CLASS uc,
1762 IGS_AS_UNITASS_ITEM uai,
1763 IGS_AS_ASSESSMNT_ITM ai,
1764 IGS_AS_ASSESSMNT_TYP atyp
1765 WHERE uoo.uoo_id = cp_uoo_id AND
1766 -- uoo.UNIT_CLASS = uc.UNIT_CLASS AND
1767 uoo.unit_cd = uai.unit_cd AND
1768 uoo.version_number = uai.version_number AND
1769 uoo.CAL_TYPE = uai.CAL_TYPE AND
1770 uoo.ci_sequence_number = uai.ci_sequence_number AND
1771 -- uoo.UNIT_CLASS = uc.UNIT_CLASS AND
1772 -- IGS_AS_VAL_UAI.assp_val_sua_uai(
1773 -- uoo.location_cd,
1774 -- uoo.UNIT_CLASS,
1775 -- uc.UNIT_MODE,
1776 -- uai.location_cd,
1777 -- uai.UNIT_CLASS,
1778 -- uai.UNIT_MODE) = cst_true AND
1779 uai.ass_id = ai.ass_id AND
1780 uai.logical_delete_dt IS NULL AND
1781 atyp.ASSESSMENT_TYPE = ai.ASSESSMENT_TYPE AND
1782 NVL(atyp.s_assessment_type, cst_none) = cst_assignment;
1783 CURSOR c_suaai(
1784 cp_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE,
1785 cp_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE) IS
1786 SELECT suaai.override_due_dt,
1787 trst.completion_dt
1788 FROM IGS_AS_SU_ATMPT_ITM suaai,
1789 IGS_TR_STEP trst,
1790 IGS_EN_SU_ATTEMPT sua
1791 WHERE sua.uoo_id = cp_uoo_id AND
1792 sua.person_id = suaai.person_id AND
1793 sua.course_cd = suaai.course_cd AND
1794 sua.uoo_id = suaai.uoo_id AND
1795 suaai.ass_id = cp_ass_id AND
1796 suaai.logical_delete_dt IS NULL AND
1797 suaai.tracking_id IS NOT NULL AND
1798 suaai.tracking_id = trst.tracking_id AND
1799 trst.s_tracking_step_type = cst_assign_due AND
1800 IGS_AS_VAL_SUAAI.assp_val_ass_count(sua.unit_attempt_status,
1801 suaai.tracking_id) = cst_yes;
1802 v_base_count NUMBER;
1803 v_one_week_extension_count NUMBER;
1804 v_two_week_extension_count NUMBER;
1805 v_three_week_plus_extnsn_count NUMBER;
1806 v_received_count NUMBER;
1807 v_completion_week_ending_dt DATE;
1808 v_override_week_ending_dt DATE;
1809 BEGIN
1810 -- Determine the session id.
1811 OPEN c_sess_id;
1812 FETCH c_sess_id INTO v_session_id;
1813 IF c_sess_id%NOTFOUND THEN
1814 CLOSE c_sess_id;
1815 RAISE NO_DATA_FOUND;
1816 END IF;
1817 CLOSE c_sess_id;
1818 -- Get the teaching periods for the assessment period.
1819 FOR c_teach_perd_rec IN c_teach_perd(
1820 p_ass_perd_cal_type,
1821 p_ass_perd_sequence_number) LOOP
1822 -- Determine the unit offering options within the teaching periods.
1823 FOR c_uoo_rec IN c_uoo(
1824 c_teach_perd_rec.CAL_TYPE,
1825 c_teach_perd_rec.sequence_number,
1826 p_owner_org_unit_cd,
1827 p_owner_ou_start_dt,
1828 p_unit_mode) LOOP
1829 -- Determine the assessment items within unit offering options within
1830 -- the teaching periods.
1831 FOR c_uai_rec IN c_uai(
1832 c_uoo_rec.uoo_id) LOOP
1833 -- Initialise counters for the unit offering option.
1834 v_base_count := 0;
1835 v_one_week_extension_count := 0;
1836 v_two_week_extension_count := 0;
1837 v_three_week_plus_extnsn_count := 0;
1838 v_received_count := 0;
1839 -- Process the students within the assessment items,
1840 -- unit offering options and the teaching periods.
1841 FOR c_suaai_rec IN c_suaai(
1842 c_uoo_rec.uoo_id,
1843 c_uai_rec.ass_id) LOOP
1844 -- For each record found, increment the base count.
1845 v_base_count := v_base_count + 1;
1846 -- Determine if any extensions apply and the length of the extension.
1847 -- Increment the appropriate counter.
1848 IF c_suaai_rec.override_due_dt IS NOT NULL THEN
1849 v_override_week_ending_dt :=
1850 IGS_GE_GEN_001.genp_clc_week_end_dt(c_suaai_rec.override_due_dt);
1851 IF (c_suaai_rec.override_due_dt > c_uai_rec.uai_week_ending_dt) THEN
1852 IF IGS_AS_GEN_001.assp_clc_week_extnsn(c_uai_rec.uai_week_ending_dt,
1853 c_suaai_rec.override_due_dt, 1) > 0 THEN
1854 v_one_week_extension_count := v_one_week_extension_count + 1;
1855 ELSIF IGS_AS_GEN_001.assp_clc_week_extnsn(c_uai_rec.uai_week_ending_dt,
1856 c_suaai_rec.override_due_dt, 2) > 0 THEN
1857 v_two_week_extension_count := v_two_week_extension_count + 1;
1858 ELSIF IGS_AS_GEN_001.assp_clc_week_extnsn(c_uai_rec.uai_week_ending_dt,
1859 c_suaai_rec.override_due_dt, 3) > 0 THEN
1860 v_three_week_plus_extnsn_count := v_three_week_plus_extnsn_count + 1;
1861 END IF;
1862 -- Insert into SI_AS_ASSR1020 table for the actual week ending date
1863 -- that the item is now due (override due date).
1864 x_rowid := NULL;
1865 IGS_AS_DUE_DT_SUMRY_PKG.INSERT_ROW(
1866 X_ROWID => x_rowid,
1867 X_SESSION_ID => v_session_id,
1868 X_AT_ID => l_AT_ID,
1869 X_UNIT_CD => c_uoo_rec.unit_cd,
1870 X_VERSION_NUMBER => c_uoo_rec.version_number,
1871 X_CAL_TYPE => c_teach_perd_rec.CAL_TYPE,
1872 X_CI_SEQUENCE_NUMBER => c_teach_perd_rec.sequence_number,
1873 X_OWNER_ORG_UNIT_CD => c_uoo_rec.owner_org_unit_cd,
1874 X_OWNER_OU_START_DT => c_uoo_rec.owner_ou_start_dt,
1875 X_UNIT_MODE => c_uoo_rec.UNIT_MODE,
1876 X_ASS_ID => c_uai_rec.ass_id,
1877 X_WEEK_ENDING_DT => v_override_week_ending_dt,
1878 X_BASE_COUNT => NULL,
1879 X_EXPECTED_OVERDUE_COUNT => 1,
1880 X_ONE_WEEK_EXTENSION_COUNT => NULL,
1881 X_TWO_WEEK_EXTENSION_COUNT => NULL,
1882 X_THREE_WEEK_PLUS_EXTNSN_COUNT => NULL,
1883 X_RECEIVED_COUNT => NULL,
1884 X_MODE => 'R'
1885 );
1886 END IF;
1887 END IF;
1888 -- Determine if the item has been received from the student.
1889 IF c_suaai_rec.completion_dt IS NOT NULL THEN
1890 v_completion_week_ending_dt
1891 := IGS_GE_GEN_001.genp_clc_week_end_dt(c_suaai_rec.completion_dt);
1892 -- If the date received is the same week due then increment the counter.
1893 IF c_uai_rec.uai_week_ending_dt = v_completion_week_ending_dt THEN
1894 v_received_count := v_received_count + 1;
1895 ELSE
1896 -- Insert into SI_AS_ASSR1020 table for the actual week ending date
1897 -- that the item was received (completion date of the tracking step.).
1898 x_rowid := NULL;
1899 IGS_AS_DUE_DT_SUMRY_PKG.INSERT_ROW(
1900 X_ROWID => x_rowid,
1901 X_SESSION_ID => v_session_id,
1902 X_AT_ID => l_AT_ID,
1903 X_UNIT_CD => c_uoo_rec.unit_cd,
1904 X_VERSION_NUMBER => c_uoo_rec.version_number,
1905 X_CAL_TYPE => c_teach_perd_rec.CAL_TYPE,
1906 X_CI_SEQUENCE_NUMBER => c_teach_perd_rec.sequence_number,
1907 X_OWNER_ORG_UNIT_CD => c_uoo_rec.owner_org_unit_cd,
1908 X_OWNER_OU_START_DT => c_uoo_rec.owner_ou_start_dt,
1909 X_UNIT_MODE => c_uoo_rec.UNIT_MODE,
1910 X_ASS_ID => c_uai_rec.ass_id,
1911 X_WEEK_ENDING_DT => v_completion_week_ending_dt,
1912 X_BASE_COUNT => NULL,
1913 X_EXPECTED_OVERDUE_COUNT => 1,
1914 X_ONE_WEEK_EXTENSION_COUNT => NULL,
1915 X_TWO_WEEK_EXTENSION_COUNT => NULL,
1916 X_THREE_WEEK_PLUS_EXTNSN_COUNT => NULL,
1917 X_RECEIVED_COUNT => NULL,
1918 X_MODE => 'R'
1919 );
1920 END IF;
1921 END IF;
1922 END LOOP;
1923 IF v_base_count > 0 THEN
1924 -- If assessment item records exist, then insert counts
1925 -- for the unit and assessment items processed.
1926 x_rowid := NULL;
1927 END IF;
1928 END LOOP;
1929 END LOOP;
1930 END LOOP;
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 IF (c_sess_id%ISOPEN) THEN
1934 CLOSE c_sess_id;
1935 END IF;
1936 IF (c_teach_perd%ISOPEN) THEN
1937 CLOSE c_teach_perd;
1938 END IF;
1939 IF (c_uoo%ISOPEN) THEN
1940 CLOSE c_uoo;
1941 END IF;
1942 IF (c_uai%ISOPEN) THEN
1943 CLOSE c_uai;
1944 END IF;
1945 IF (c_suaai%ISOPEN) THEN
1946 CLOSE c_suaai;
1947 END IF;
1948 RAISE;
1949 END;
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1953 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_ins_asr1020_tmp');
1954 IGS_GE_MSG_STACK.ADD;
1955 App_Exception.Raise_Exception;
1956 END assp_ins_asr1020_tmp;
1957 PROCEDURE assp_ins_dflt_evsa_a(
1958 errbuf out NOCOPY varchar2,
1959 retcode out NOCOPY number,
1960 p_exam_cal in VARCHAR2,
1961 p_org_id in NUMBER
1962 )
1963 IS
1964 p_exam_cal_type igs_ca_inst.cal_type%type;
1965 p_exam_ci_sequence_number igs_ca_inst.sequence_number%type;
1966
1967 BEGIN -- assp_ins_dflt_evsa_a
1968 -- Default the IGS_AS_EXMVNU_SESAVL records for all venues within an
1969 -- examination period.
1970 -- It will set all open venues to be available for all sessions.
1971 --set the org id
1972 igs_ge_gen_003.set_org_id(p_org_id);
1973 --Block for Parameter Validation/Splitting of Parameters
1974 retcode:=0;
1975 BEGIN
1976 IF p_exam_cal IS NULL THEN
1977 p_exam_cal_type:=NULL;
1978 p_exam_ci_sequence_number:=NULL;
1979 ELSE
1980 p_exam_cal_type := RTRIM(SUBSTR(p_exam_cal, 101, 10));
1981 p_exam_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_exam_cal, 112,6)));
1982 END IF;
1983 END;
1984 --End of Block for Parameter Validation/Splitting of Parameters
1985
1986
1987 DECLARE
1988 CURSOR c_ve IS
1989 SELECT venue_cd
1990 FROM IGS_GR_VENUE
1991 WHERE closed_ind = 'N';
1992 CURSOR c_es (
1993 cp_venue_cd IGS_GR_VENUE.venue_cd%TYPE) IS
1994 SELECT exam_cal_type,
1995 exam_ci_sequence_number,
1996 dt_alias,
1997 dai_sequence_number,
1998 ci_start_dt,
1999 ci_end_dt,
2000 start_time,
2001 end_time,
2002 ese_id
2003 FROM IGS_AS_EXAM_SESSION es
2004 WHERE exam_cal_type = p_exam_cal_type AND
2005 exam_ci_sequence_number = p_exam_ci_sequence_number AND
2006 NOT EXISTS (
2007 SELECT 'x'
2008 FROM IGS_AS_EXMVNU_SESAVL evsa
2009 WHERE evsa.ese_id = es.ese_id AND
2010 evsa.venue_cd = cp_venue_cd
2011 );
2012 BEGIN
2013 FOR v_ve_rec IN c_ve LOOP
2014 FOR v_es_rec IN c_es(
2015 v_ve_rec.venue_cd) LOOP
2016 x_rowid := NULL;
2017 IGS_AS_EXMVNU_SESAVL_PKG.INSERT_ROW(
2018 X_ROWID => x_rowid,
2019 X_ORG_ID => p_org_id,
2020 X_VENUE_CD => v_ve_rec.venue_cd,
2021 X_EXAM_CAL_TYPE => v_es_rec.exam_cal_type,
2022 X_EXAM_CI_SEQUENCE_NUMBER => v_es_rec.exam_ci_sequence_number,
2023 X_DT_ALIAS => v_es_rec.dt_alias,
2024 X_DAI_SEQUENCE_NUMBER => v_es_rec.dai_sequence_number,
2025 X_START_TIME => v_es_rec.start_time,
2026 X_END_TIME => v_es_rec.end_time,
2027 X_ESE_ID => v_es_rec.ese_id,
2028 X_COMMENTS => NULL,
2029 X_MODE => 'R'
2030 );
2031 END LOOP;
2032 END LOOP;
2033 COMMIT;
2034 END;
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 Retcode := 2;
2038 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2039 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2040 END assp_ins_dflt_evsa_a;
2041 PROCEDURE assp_ins_ese_sprvsr(
2042 p_exam_cal_type IN VARCHAR2 ,
2043 p_exam_ci_sequence_number IN NUMBER ,
2044 p_person_id IN NUMBER ,
2045 p_exam_supervisor_type IN VARCHAR2 ,
2046 p_venue_cd IN VARCHAR2 ,
2047 p_session_venue_ind IN VARCHAR2 DEFAULT 'N',
2048 p_ignore_warnings_ind IN VARCHAR2 DEFAULT 'N',
2049 p_message_name OUT NOCOPY VARCHAR2 )
2050 IS
2051 BEGIN --assp_ins_ese_sprvsr
2052 --This module will insert a supervisor to exam sessions at a
2053 --IGS_GR_VENUE within a calendar period.
2054 --The module will be called from ASSF4630 and with two scenarios:
2055 --1. Default a supervisor to sessions within a IGS_GR_VENUE. (p_person_id not null)
2056 --2. Default supervisors to Off-campus venues. (p_person_id is null)
2057 --It will provide the ability to enforce that if warnings exist
2058 --when validating the IGS_PE_PERSON, then either allocate (p_ignore_warnings = 'Y')
2059 --or do not allocate (p_ignore_warnings = 'N') the supervisor.
2060 --This module will return a general message indicating that warnings
2061 --or errors where encountered during the processing if that is the case.
2062 DECLARE
2063 v_message_name VARCHAR2(30);
2064 v_tmp_message_name VARCHAR2(30);
2065
2066 v_lr_exists VARCHAR2(1);
2067 CURSOR c_esvv IS
2068 SELECT esvv.ese_id,
2069 esvv.venue_cd,
2070 ve.exam_location_cd
2071 FROM IGS_AS_ESE_VENUE_V esvv,
2072 IGS_GR_VENUE ve
2073 WHERE esvv.exam_cal_type = p_exam_cal_type AND
2074 esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
2075 esvv.venue_cd = ve.venue_cd;
2076 CURSOR c_lr(
2077 cp_exam_location_cd IGS_GR_VENUE.exam_location_cd%TYPE) IS
2078 SELECT 'X'
2079 FROM IGS_AD_LOCATION_REL lr,
2080 IGS_AD_LOCATION loc,
2081 IGS_AD_LOCATION_TYPE lot
2082 WHERE lr.sub_location_cd = cp_exam_location_cd AND
2083 lr.location_cd = loc.location_cd AND
2084 loc.LOCATION_TYPE = lot.LOCATION_TYPE AND
2085 lot.s_location_type = 'CAMPUS';
2086 CURSOR c_els (
2087 cp_venue_cd IGS_AS_ESE_VENUE_V.venue_cd%TYPE) IS
2088 SELECT els.person_id
2089 FROM IGS_AS_EXM_LOC_SPVSR els,
2090 IGS_GR_VENUE ve
2091 WHERE els.exam_location_cd = ve.exam_location_cd AND
2092 ve.venue_cd = cp_venue_cd;
2093 CURSOR c_ve IS
2094 SELECT esvv.ese_id,
2095 esvv.venue_cd
2096 FROM IGS_AS_ESE_VENUE_V esvv
2097 WHERE esvv.exam_cal_type = p_exam_cal_type AND
2098 esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
2099 esvv.venue_cd = p_venue_cd;
2100 FUNCTION asspl_val_sprvsr(
2101 p_person_id IGS_AS_EXM_INS_SPVSR.person_id%TYPE,
2102 p_ese_id IN OUT NOCOPY IGS_AS_ESE_VENUE_V.ese_id%TYPE,
2103 p_venue_cd IGS_AS_EXM_INS_SPVSR.venue_cd%TYPE,
2104 p_exam_supervisor_type IGS_AS_EXM_INS_SPVSR.EXAM_SUPERVISOR_TYPE%TYPE,
2105 p_ignore_warnings VARCHAR2,
2106 p_message_name OUT NOCOPY VARCHAR2)
2107 RETURN BOOLEAN
2108 IS
2109 BEGIN --asspl_val_sprvsr
2110 --Local function to validate the supervisor
2111 DECLARE
2112 v_exam_cal_type IGS_AS_EXM_INS_SPVSR.exam_cal_type%TYPE;
2113 v_exam_ci_sequence_number IGS_AS_EXM_INS_SPVSR.exam_ci_sequence_number%TYPE;
2114 v_dt_alias IGS_AS_EXM_INS_SPVSR.dt_alias%TYPE;
2115 v_dai_sequence_number IGS_AS_EXM_INS_SPVSR.dai_sequence_number%TYPE;
2116 v_start_time IGS_AS_EXM_INS_SPVSR.start_time%TYPE;
2117 v_end_time IGS_AS_EXM_INS_SPVSR.end_time%TYPE;
2118 v_local_message_name VARCHAR2(30);
2119 v_exam_supervisor_type IGS_AS_EXM_SUPRVISOR.EXAM_SUPERVISOR_TYPE%TYPE;
2120 CURSOR c_esu IS
2121 SELECT esu.EXAM_SUPERVISOR_TYPE
2122 FROM IGS_AS_EXM_SUPRVISOR esu
2123 WHERE esu.person_id = p_person_id;
2124 BEGIN
2125 v_local_message_name := NULL;
2126 p_message_name := NULL;
2127 -- Validate that the supervisor type is not closed.
2128 IF (p_exam_supervisor_type IS NULL) THEN
2129 OPEN c_esu;
2130 FETCH c_esu INTO v_exam_supervisor_type;
2131 CLOSE c_esu;
2132 ELSE
2133 v_exam_supervisor_type := p_exam_supervisor_type;
2134 END IF;
2135 IF (IGS_AS_VAL_ESU.assp_val_est_closed(
2136 v_exam_supervisor_type,
2137 v_local_message_name) = FALSE) THEN
2138 p_message_name := v_local_message_name;
2139 RETURN FALSE;
2140 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2141 END IF;
2142 --Check the following warnings:
2143 -- Validate if the IGS_PE_PERSON is not an active student.
2144 -- Warning only.
2145 IF (IGS_AS_VAL_EIS.assp_val_actv_stdnt(
2146 p_person_id,
2147 v_local_message_name) = FALSE) THEN
2148 p_message_name := v_local_message_name;
2149 IF (p_ignore_warnings = 'N') THEN
2150 RETURN FALSE;
2151 END IF;
2152 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2153 END IF;
2154 -- Validate the IGS_PE_PERSON is a staff member.
2155 -- Warning only.
2156 IF (igs_ad_val_acai.genp_val_staff_prsn(
2157 p_person_id,
2158 v_local_message_name) = FALSE) THEN
2159 p_message_name := v_local_message_name;
2160 IF (p_ignore_warnings = 'N') THEN
2161 RETURN FALSE;
2162 END IF;
2163 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2164 END IF;
2165 -- Get the exam session key fields.
2166 assp_get_ese_key(
2167 v_exam_cal_type,
2168 v_exam_ci_sequence_number,
2169 v_dt_alias,
2170 v_dai_sequence_number,
2171 v_start_time,
2172 v_end_time,
2173 p_ese_id);
2174 -- Validate if the IGS_PE_PERSON is allocated to different exam locations for the
2175 -- same day.
2176 -- Warning only.
2177 IF (IGS_AS_VAL_ESVS.assp_val_esu_ese_el(
2178 p_person_id,
2179 v_exam_cal_type,
2180 v_exam_ci_sequence_number,
2181 v_dt_alias,
2182 v_dai_sequence_number,
2183 v_start_time,
2184 v_end_time,
2185 p_venue_cd,
2186 v_local_message_name) = FALSE) THEN
2187 p_message_name := v_local_message_name;
2188 IF (p_ignore_warnings = 'N') THEN
2189 RETURN FALSE;
2190 END IF;
2191 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2192 END IF;
2193 -- Validate if the limit exceeded for the session and IGS_GR_VENUE.
2194 -- Warning only.
2195 --w.r.t BUG #1956374 , Procedure assp_val_esu_ese_lmt reference is changed
2196 IF (IGS_AS_VAL_EIS.assp_val_esu_ese_lmt(
2197 p_person_id,
2198 v_exam_cal_type,
2199 v_exam_ci_sequence_number,
2200 v_dt_alias,
2201 v_dai_sequence_number,
2202 v_start_time,
2203 v_end_time,
2204 p_venue_cd,
2205 v_local_message_name) = FALSE) THEN
2206 p_message_name := v_local_message_name;
2207 IF (p_ignore_warnings = 'N') THEN
2208 RETURN FALSE;
2209 END IF;
2210 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2211 END IF;
2212 -- Validate IGS_PE_PERSON cannot be allocated concurrent sessions at different
2213 -- venues.
2214 -- Warning only.
2215 IF (IGS_AS_VAL_ESVS.assp_val_esu_ese_ve(
2216 p_person_id,
2217 v_exam_cal_type,
2218 v_exam_ci_sequence_number,
2219 v_dt_alias,
2220 v_dai_sequence_number,
2221 v_start_time,
2222 v_end_time,
2223 NULL,
2224 NULL,
2225 p_venue_cd,
2226 v_local_message_name) = FALSE) THEN
2227 p_message_name := v_local_message_name;
2228 IF (p_ignore_warnings = 'N') THEN
2229 RETURN FALSE;
2230 END IF;
2231 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2232 END IF;
2233 -- Validate IGS_GR_VENUE is within supervisor's exam locations.
2234 -- Warning only.
2235 IF IGS_AS_VAL_ESVS.assp_val_els_venue(
2236 p_person_id,
2237 p_venue_cd,
2238 v_local_message_name) = FALSE THEN
2239 p_message_name := v_local_message_name;
2240 IF (p_ignore_warnings = 'N') THEN
2241 RETURN FALSE;
2242 END IF;
2243 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2244 END IF;
2245 -- Validate if more than one IGS_PE_PERSON in-charge at a session and IGS_GR_VENUE.
2246 -- Warning only.
2247 IF IGS_AS_VAL_EIS.assp_val_ese_inchrg(
2248 p_person_id,
2249 v_exam_cal_type,
2250 v_exam_ci_sequence_number,
2251 v_dt_alias,
2252 v_dai_sequence_number,
2253 v_start_time,
2254 v_end_time,
2255 p_venue_cd,
2256 v_exam_supervisor_type,
2257 v_local_message_name) = FALSE THEN
2258 p_message_name := v_local_message_name;
2259 IF (p_ignore_warnings = 'N') THEN
2260 RETURN FALSE;
2261 END IF;
2262 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2263 END IF;
2264 -- Validate if IGS_PE_PERSON is allocated as in-charge when not normally.
2265 -- Warning only.
2266 IF IGS_AS_VAL_EIS.assp_val_est_inchrg(
2267 p_person_id,
2268 v_exam_supervisor_type,
2269 v_local_message_name) = FALSE THEN
2270 p_message_name := v_local_message_name;
2271 IF (p_ignore_warnings = 'N') THEN
2272 RETURN FALSE;
2273 END IF;
2274 -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2275 END IF;
2276 RETURN TRUE;
2277 END;
2278 END asspl_val_sprvsr;
2279 PROCEDURE asspl_ins_ve_sprvsr(
2280 p_person_id IGS_AS_EXM_INS_SPVSR.person_id%TYPE,
2281 p_ese_id IGS_AS_ESE_VENUE_V.ese_id%TYPE,
2282 p_venue_cd IGS_AS_EXM_INS_SPVSR.venue_cd%TYPE,
2283 p_exam_supervisor_type IGS_AS_EXM_INS_SPVSR.EXAM_SUPERVISOR_TYPE%TYPE,
2284 p_session_venue_ind VARCHAR2)
2285 IS
2286 BEGIN --asspl_ins_ve_sprvsr
2287 --Local procedure to insert the supervisor
2288 DECLARE
2289 v_exam_supervisor_type IGS_AS_EXM_SUPRVISOR.EXAM_SUPERVISOR_TYPE%TYPE;
2290 CURSOR c_esu IS
2291 SELECT esu.EXAM_SUPERVISOR_TYPE
2292 FROM IGS_AS_EXM_SUPRVISOR esu
2293 WHERE esu.person_id = p_person_id;
2294 CURSOR c_ei IS
2295 SELECT ei.ass_id
2296 FROM IGS_AS_EXAM_INSTANCE ei
2297 WHERE ei.ese_id = p_ese_id AND
2298 ei.venue_cd = p_venue_cd;
2299 BEGIN
2300 --Determine the supervisor type to use
2301 IF (p_exam_supervisor_type IS NULL) THEN
2302 OPEN c_esu;
2303 FETCH c_esu INTO v_exam_supervisor_type;
2304 CLOSE c_esu;
2305 ELSE
2306 v_exam_supervisor_type := p_exam_supervisor_type;
2307 END IF;
2308 IF (p_session_venue_ind = 'Y') THEN
2309 -- Insert IGS_PE_PERSON into the IGS_AS_EXM_SES_VN_SP table.
2310 x_rowid := NULL;
2311 IGS_AS_EXM_SES_VN_SP_PKG.INSERT_ROW(
2312 X_ROWID => x_rowid,
2313 X_PERSON_ID => p_person_id,
2314 X_EXAM_CAL_TYPE => NULL,
2315 X_EXAM_CI_SEQUENCE_NUMBER => NULL,
2316 X_DT_ALIAS => NULL,
2317 X_DAI_SEQUENCE_NUMBER => NULL,
2318 X_START_TIME => NULL,
2319 X_END_TIME => NULL,
2320 X_VENUE_CD => p_venue_cd,
2321 X_ESE_ID => p_ese_id,
2322 X_EXAM_SUPERVISOR_TYPE => v_exam_supervisor_type,
2323 X_OVERRIDE_START_TIME => NULL,
2324 X_OVERRIDE_END_TIME => NULL,
2325 X_MODE => 'R'
2326 );
2327 -- At a later date, a IGS_GE_S_LOG_ENTRY may be created to indicate successful
2328 -- insertion.
2329 ELSE
2330 -- Insert IGS_PE_PERSON into the IGS_AS_EXM_INS_SPVSR table. Put them into all exams
2331 -- for the IGS_GR_VENUE and session.
2332 FOR v_ei_rec IN c_ei LOOP
2333 BEGIN
2334 IGS_AS_EXM_INS_SPVSR_PKG.INSERT_ROW(
2335 X_ROWID => x_rowid,
2336 X_PERSON_ID => p_person_id,
2337 X_ASS_ID => v_ei_rec.ass_id,
2338 X_EXAM_CAL_TYPE => NULL,
2339 X_EXAM_CI_SEQUENCE_NUMBER => NULL,
2340 X_DT_ALIAS => NULL,
2341 X_DAI_SEQUENCE_NUMBER => NULL,
2342 X_START_TIME => NULL,
2343 X_END_TIME => NULL,
2344 X_VENUE_CD => p_venue_cd,
2345 X_ESE_ID => p_ese_id,
2346 X_EXAM_SUPERVISOR_TYPE => v_exam_supervisor_type,
2347 X_OVERRIDE_START_TIME => NULL,
2348 X_OVERRIDE_END_TIME => NULL,
2349 X_MODE => 'R'
2350 );
2351 -- At a later date, an IGS_GE_S_LOG_ENTRY may be created to indicate
2352 --successful insertion.
2353 -- Ignore IGS_GE_EXCEPTIONS indicating that the record has already been created
2354 -- and remain inside the loop
2355 EXCEPTION
2356 WHEN DUP_VAL_ON_INDEX THEN
2357 NULL;
2358 WHEN OTHERS THEN
2359 RAISE;
2360 END;
2361 END LOOP;
2362 END IF;
2363 END;
2364 -- Ignore IGS_GE_EXCEPTIONS indicating that the record has already been created.
2365 EXCEPTION
2366 WHEN DUP_VAL_ON_INDEX THEN
2367 NULL;
2368 WHEN OTHERS THEN
2369 RAISE;
2370 END asspl_ins_ve_sprvsr;
2371 BEGIN
2372 --Set default message number
2373 p_message_name := NULL;
2374 v_message_name := NULL;
2375 v_tmp_message_name := NULL;
2376 IF (p_person_id IS NULL) THEN
2377 --Perform processing to default the supervisor's
2378 --to OFF-CAMPUS exam venues at sessions within the exam
2379 --period (p_exam_cal_type/p_exam_ci_sequence_number).
2380 --Select all exam session venues for the exam period
2381 --(IGS_AS_ESE_VENUE_V) where the IGS_GR_VENUE is not linked to a campus.
2382 FOR v_esvv_rec IN c_esvv LOOP
2383 OPEN c_lr(
2384 v_esvv_rec.exam_location_cd);
2385 FETCH c_lr INTO v_lr_exists;
2386 IF (c_lr%NOTFOUND) THEN
2387 CLOSE c_lr;
2388 --The exam IGS_AD_LOCATION is an off campus IGS_AD_LOCATION
2389 --For each IGS_GR_VENUE, select the supervisors for which
2390 -- the exam venues are within their nominated exam locations.
2391 FOR v_els_rec IN c_els(
2392 v_esvv_rec.venue_cd) LOOP
2393 --Call a local function to validate the IGS_PE_PERSON being allocated
2394 IF (asspl_val_sprvsr(
2395 v_els_rec.person_id,
2396 v_esvv_rec.ese_id,
2397 v_esvv_rec.venue_cd,
2398 p_exam_supervisor_type,
2399 p_ignore_warnings_ind,
2400 v_message_name) = TRUE) THEN
2401 --Insert the supervisor
2402 asspl_ins_ve_sprvsr(
2403 v_els_rec.person_id,
2404 v_esvv_rec.ese_id,
2405 v_esvv_rec.venue_cd,
2406 p_exam_supervisor_type,
2407 p_session_venue_ind);
2408 END IF;
2409 IF v_message_name IS NOT NULL THEN
2410 v_tmp_message_name := v_message_name;
2411 END IF;
2412 END LOOP;
2413 END IF;
2414 IF(c_lr%ISOPEN) THEN
2415 CLOSE c_lr;
2416 END IF;
2417 END LOOP;
2418 ELSE
2419 --p_person_id is not null so default the IGS_PE_PERSON for all sessions at the IGS_GR_VENUE.
2420 --Select all exam sessions for p_venue_cd within the exam period.
2421 FOR v_ve_rec IN c_ve LOOP
2422 --Call a local function to validate the IGS_PE_PERSON being allocated.
2423 IF (asspl_val_sprvsr(
2424 p_person_id,
2425 v_ve_rec.ese_id,
2426 v_ve_rec.venue_cd,
2427 p_exam_supervisor_type,
2428 p_ignore_warnings_ind,
2429 v_message_name) = TRUE) THEN
2430 -- Insert the supervisor.
2431 asspl_ins_ve_sprvsr(
2432 p_person_id,
2433 v_ve_rec.ese_id,
2434 p_venue_cd,
2435 p_exam_supervisor_type,
2436 p_session_venue_ind);
2437 END IF;
2438 IF v_message_name IS NOT NULL THEN
2439 v_tmp_message_name := v_message_name;
2440 END IF;
2441 END LOOP;
2442 END IF;
2443 --If errors or warnings were encountered during processing,
2444 --return a message to indicate this.
2445 IF (v_tmp_message_name IS NOT NULL) THEN
2446 p_message_name := 'IGS_AS_ERROR_DFLT_SUPERVISOR';
2447 END IF;
2448 END;
2449 EXCEPTION
2450 WHEN OTHERS THEN
2451 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2452 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_ins_ese_sprvsr');
2453 IGS_GE_MSG_STACK.ADD;
2454 App_Exception.Raise_Exception;
2455 END assp_ins_ese_sprvsr;
2456 PROCEDURE assp_ins_gs_duprec(
2457 p_old_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE ,
2458 p_old_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE ,
2459 p_new_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE ,
2460 p_new_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE ,
2461 p_message_name OUT NOCOPY VARCHAR2 )
2462 IS
2463 BEGIN -- assp_ins_gs_duprec
2464 -- This procedure is responsible for transferring all of the details for a
2465 -- nominated
2466 -- grading schema over into another grading schema.
2467 -- Get a record from IGS_AS_GRD_SCH_GRADE and make duplicates under the new
2468 -- IGS_AS_GRD_SCHEMA.
2469 DECLARE
2470 cst_exist_ind CONSTANT CHAR := 'x';
2471 CURSOR c_gs_new (
2472 cp_new_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE,
2473 cp_new_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE) IS
2474 SELECT cst_exist_ind
2475 FROM IGS_AS_GRD_SCHEMA
2476 WHERE grading_schema_cd = cp_new_grading_schema_cd AND
2477 version_number = cp_new_version_number;
2478 CURSOR c_gs_old (
2479 cp_old_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE,
2480 cp_old_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE) IS
2481 SELECT cst_exist_ind
2482 FROM IGS_AS_GRD_SCHEMA
2483 WHERE grading_schema_cd = cp_old_grading_schema_cd AND
2484 version_number = cp_old_version_number;
2485 CURSOR c_gsg (
2486 cp_old_grading_schema_cd IN IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE,
2487 cp_old_version_number IN IGS_AS_GRD_SCH_GRADE.version_number%TYPE) IS
2488 SELECT *
2489 FROM IGS_AS_GRD_SCH_GRADE
2490 WHERE grading_schema_cd = cp_old_grading_schema_cd AND
2491 version_number = cp_old_version_number AND
2492 closed_ind = 'N';
2493 v_gs_old_rec c_gs_old%ROWTYPE;
2494 v_gs_new_rec c_gs_new%ROWTYPE;
2495 v_gsg_rec c_gsg%ROWTYPE;
2496 v_copy_flag BOOLEAN;
2497 v_grading_schema_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE;
2498 v_version_number IGS_AS_GRD_SCHEMA.version_number%TYPE;
2499 BEGIN
2500 -- Set the default message number
2501 p_message_name := NULL;
2502 OPEN c_gs_new(
2503 p_new_grading_schema_cd,
2504 p_new_version_number);
2505 FETCH c_gs_new INTO v_gs_new_rec;
2506 IF c_gs_new%NOTFOUND THEN
2507 CLOSE c_gs_new;
2508 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2509 RETURN;
2510 END IF;
2511 CLOSE c_gs_new;
2512 OPEN c_gs_old(
2513 p_old_grading_schema_cd,
2514 p_old_version_number);
2515 FETCH c_gs_old INTO v_gs_old_rec;
2516 IF c_gs_old%NOTFOUND THEN
2517 CLOSE c_gs_old;
2518 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2519 RETURN;
2520 END IF;
2521 CLOSE c_gs_old;
2522 v_copy_flag := TRUE;
2523 v_grading_schema_cd := p_new_grading_schema_cd;
2524 v_version_number := p_new_version_number;
2525 FOR v_gsg_rec IN c_gsg(
2526 p_old_grading_schema_cd,
2527 p_old_version_number) LOOP
2528 BEGIN
2529 x_rowid := NULL;
2530 IGS_AS_GRD_SCH_GRADE_PKG.INSERT_ROW(
2531 X_ROWID => x_rowid,
2532 X_GRADING_SCHEMA_CD => p_new_grading_schema_cd,
2533 X_VERSION_NUMBER => p_new_version_number,
2534 X_GRADE => v_gsg_rec.grade,
2535 X_FULL_GRADE_NAME => v_gsg_rec.full_grade_name,
2536 X_S_RESULT_TYPE => v_gsg_rec.s_result_type,
2537 X_SHOW_ON_NOTICEBOARD_IND => v_gsg_rec.show_on_noticeboard_ind,
2538 X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_gsg_rec.show_on_official_ntfctn_ind,
2539 X_S_SPECIAL_GRADE_TYPE => NULL,
2540 X_SHOW_IN_NEWSPAPER_IND => v_gsg_rec.show_in_newspaper_ind,
2541 X_SHOW_INTERNALLY_IND => v_gsg_rec.show_internally_ind,
2542 X_SYSTEM_ONLY_IND => v_gsg_rec.system_only_ind,
2543 X_DFLT_OUTSTANDING_IND => v_gsg_rec.dflt_outstanding_ind,
2544 X_EXTERNAL_GRADE => v_gsg_rec.external_grade,
2545 X_LOWER_MARK_RANGE => v_gsg_rec.lower_mark_range,
2546 X_UPPER_MARK_RANGE => v_gsg_rec.upper_mark_range,
2547 X_MIN_PERCENTAGE => v_gsg_rec.min_percentage,
2548 X_MAX_PERCENTAGE => v_gsg_rec.max_percentage,
2549 X_GPA_VAL => v_gsg_rec.gpa_val,
2550 X_RANK => v_gsg_rec.rank,
2551 X_SHOW_IN_EARNED_CRDT_IND => v_gsg_rec.show_in_earned_crdt_ind,
2552 X_INCL_IN_REPEAT_PROCESS_IND => v_gsg_rec.incl_in_repeat_process_ind,
2553 X_ADMIN_ONLY_IND => v_gsg_rec.admin_only_ind,
2554 X_GRADING_PERIOD_CD => v_gsg_rec.grading_period_cd,
2555 X_REPEAT_GRADE => v_gsg_rec.repeat_grade,
2556 X_MODE => 'R',
2557 X_Attribute_Category => v_gsg_rec.Attribute_Category,
2558 X_Attribute1 => v_gsg_rec.Attribute1,
2559 X_Attribute2 => v_gsg_rec.Attribute2,
2560 X_Attribute3 => v_gsg_rec.Attribute3,
2561 X_Attribute4 => v_gsg_rec.Attribute4,
2562 X_Attribute5 => v_gsg_rec.Attribute5,
2563 X_Attribute6 => v_gsg_rec.Attribute6,
2564 X_Attribute7 => v_gsg_rec.Attribute7,
2565 X_Attribute8 => v_gsg_rec.Attribute8,
2566 X_Attribute9 => v_gsg_rec.Attribute9,
2567 X_Attribute10 => v_gsg_rec.Attribute10,
2568 X_Attribute11 => v_gsg_rec.Attribute11,
2569 X_Attribute12 => v_gsg_rec.Attribute12,
2570 X_Attribute13 => v_gsg_rec.Attribute13,
2571 X_Attribute14 => v_gsg_rec.Attribute14,
2572 X_Attribute15 => v_gsg_rec.Attribute15,
2573 X_Attribute16 => v_gsg_rec.Attribute16,
2574 X_Attribute17 => v_gsg_rec.Attribute17,
2575 X_Attribute18 => v_gsg_rec.Attribute18,
2576 X_Attribute19 => v_gsg_rec.Attribute19,
2577 X_Attribute20 => v_gsg_rec.Attribute20,
2578 X_CLOSED_IND => 'N');
2579 EXCEPTION
2580 WHEN OTHERS THEN
2581 v_copy_flag := FALSE;
2582 END;
2583 END LOOP;
2584 IF (v_copy_flag = FALSE) THEN
2585 p_message_name := 'IGS_AS_FAIL_COPY_ALL_GRDSCH';
2586 ELSE
2587 p_message_name := 'IGS_AS_SUCCESS_COPY_ALL_GRDSC';
2588 END IF;
2589 END;
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2593 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_006.assp_ins_gs_duprec');
2594 IGS_GE_MSG_STACK.ADD;
2595 App_Exception.Raise_Exception;
2596 END assp_ins_gs_duprec;
2597
2598 PROCEDURE assp_upd_suao_trans(
2599 errbuf out NOCOPY varchar2,
2600 retcode out NOCOPY number,
2601 p_assess_calendar IN VARCHAR2 ,
2602 p_teaching_calendar IN VARCHAR2,
2603 p_crs_grp_cd IN VARCHAR2 ,
2604 p_crs_cd IN VARCHAR2 ,
2605 p_crs_org_unt_cd IN VARCHAR2 ,
2606 p_crs_lctn_cd IN VARCHAR2 ,
2607 p_crs_attd_md IN VARCHAR2 ,
2608 p_unt_cd IN VARCHAR2 ,
2609 p_unt_org_unt_cd IN VARCHAR2 ,
2610 p_unt_lctn_cd IN VARCHAR2 ,
2611 p_u_mode IN VARCHAR2 ,
2612 p_u_class IN VARCHAR2 ,
2613 p_allow_invalid_ind IN VARCHAR2 ,
2614 p_org_id IN NUMBER)
2615 IS
2616 BEGIN
2617 --
2618 retcode:=0;
2619 --
2620 -- As per 2239087, this concurrent program is obsolete and if the user
2621 -- tries to run this program then an error message should be logged into the log
2622 -- file that the concurrent program is obsolete and should not be run.
2623 --
2624 fnd_message.set_name ('IGS', 'IGS_GE_OBSOLETE_JOB');
2625 fnd_file.put_line (fnd_file.log, fnd_message.get);
2626 --
2627 EXCEPTION
2628 WHEN OTHERS THEN
2629 retcode:=2;
2630 errbuf:=fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2631 igs_ge_msg_stack.conc_exception_hndl;
2632 END assp_upd_suao_trans;
2633 END IGS_AS_GEN_006 ;
[Home] [Help]