1 package body igs_as_anon_grd_pkg as
2 /* $Header: IGSAS39B.pls 120.3 2006/09/08 09:58:42 amanohar noship $ */
3 /*
4 || Created By : pkpatel
5 || Created On : 28-JAN-2002
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11 || smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_uoo_ai and
12 || c_uoo_ug to select active (not closed) unit classes.
13 */
14
15 FUNCTION chk_anon_graded (
16 p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
17 p_ass_id IN igs_as_assessmnt_itm_all.ass_id%TYPE
18 ) RETURN VARCHAR2 IS
19 /*
20 || Created By : pkpatel
21 || Created On : 28-JAN-2002
22 || Purpose : This function checks if Anonymously Graded. It returns 'Y'/'N'. If P_UOO_ID
23 || is passed as NULL, it returns NULL.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR c_uoo_ug
31 IS
32 SELECT 'X'
33 FROM igs_ps_unit_ofr_opt uoo,
34 igs_as_anon_method anm,
35 igs_ca_teach_to_load_v ttl
36 WHERE uoo.uoo_id = p_uoo_id
37 AND uoo.anon_unit_grading_ind = 'Y'
38 AND uoo.cal_type = ttl.teach_cal_type
39 AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
40 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
41 FROM igs_ca_teach_to_load_v ttl2
42 WHERE uoo.cal_type = ttl2.teach_cal_type
43 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
44 AND anm.load_cal_type = ttl.load_cal_type
45 AND (anm.method <> 'ASSESSMENT'
46 OR (anm.method = 'ASSESSMENT'
47 AND (EXISTS ( SELECT 'X'
48 FROM igs_ps_unitass_item uooai,
49 igs_as_assessmnt_itm ai,
50 igs_as_assessmnt_typ ast
51 WHERE uooai.uoo_id = uoo.uoo_id
52 AND uooai.logical_delete_dt IS NULL
53 AND uooai.ass_id = ai.ass_id
54 AND ai.assessment_type = anm.assessment_type
55 AND ai.assessment_type = ast.assessment_type
56 AND ast.anon_grading_ind = 'Y')
57 OR EXISTS ( SELECT 'X'
58 FROM igs_as_unitass_item uoai,
59 igs_as_assessmnt_itm ai,
60 igs_as_assessmnt_typ ast,
61 igs_as_unit_class uc
62 WHERE uoai.unit_cd = uoo.unit_cd
63 AND uoai.version_number = uoo.version_number
64 AND uoai.cal_type = uoo.cal_type
65 AND uoai.ci_sequence_number = uoo.ci_sequence_number
66 AND uc.closed_ind = 'N'
67 --ijeddy, Bug 3201661, Grade Book.
68 AND uoai.logical_delete_dt IS NULL
69 AND uoai.ass_id = ai.ass_id
70 AND ai.assessment_type = anm.assessment_type
71 AND ai.assessment_type = ast.assessment_type
72 AND ast.anon_grading_ind = 'Y'
73 AND NOT EXISTS (SELECT 'X'
74 FROM igs_ps_unitass_item uooai
75 WHERE uooai.uoo_id = uoo.uoo_id
76 AND uooai.logical_delete_dt IS NOT NULL)))));
77
78
79 CURSOR c_uoo_ai
80 IS
81 SELECT 'X'
82 FROM igs_ps_unit_ofr_opt uoo,
83 igs_as_anon_method anm,
84 igs_ca_teach_to_load_v ttl
85 WHERE uoo.uoo_id = p_uoo_id
86 AND uoo.anon_assess_grading_ind = 'Y'
87 AND uoo.cal_type = ttl.teach_cal_type
88 AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
89 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
90 FROM igs_ca_teach_to_load_v ttl2
91 WHERE uoo.cal_type = ttl2.teach_cal_type
92 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
93 AND anm.load_cal_type = ttl.load_cal_type
94 AND (EXISTS ( SELECT 'X'
95 FROM igs_ps_unitass_item uooai,
96 igs_as_assessmnt_itm ai,
97 igs_as_assessmnt_typ ast
98 WHERE uooai.uoo_id = uoo.uoo_id
99 AND uooai.ass_id = p_ass_id
100 AND uooai.logical_delete_dt IS NULL
101 AND uooai.ass_id = ai.ass_id
102 AND ai.assessment_type = ast.assessment_type
103 AND ast.anon_grading_ind = 'Y')
104 OR EXISTS ( SELECT 'X'
105 FROM igs_as_unitass_item uoai,
106 igs_as_assessmnt_itm ai,
107 igs_as_assessmnt_typ ast,
108 igs_as_unit_class uc
109 WHERE uoai.unit_cd = uoo.unit_cd
110 AND uoai.version_number = uoo.version_number
111 AND uoai.cal_type = uoo.cal_type
112 AND uoai.ci_sequence_number = uoo.ci_sequence_number
113 AND uoai.ass_id = p_ass_id
114 AND uoo.location_cd = NVL(uoai.location_cd, uoo.location_cd)
115 AND uoo.unit_class = NVL(uoai.unit_class, uoo.unit_class)
116 AND uoo.unit_class = uc.unit_class
117 AND uc.closed_ind = 'N'
118 AND uc.unit_mode = NVL(uoai.unit_mode, uc.unit_mode)
119 AND uoai.logical_delete_dt IS NULL
120 AND uoai.ass_id = ai.ass_id
121 AND ai.assessment_type = ast.assessment_type
122 AND ast.anon_grading_ind = 'Y'
123 AND NOT EXISTS (SELECT 'X'
124 FROM igs_ps_unitass_item uooai
125 WHERE uooai.uoo_id = uoo.uoo_id
126 AND uooai.logical_delete_dt IS NOT NULL)));
127
128 c_uoo_ai_rec c_uoo_ai%ROWTYPE;
129 c_uoo_ug_rec c_uoo_ug%ROWTYPE;
130
131 BEGIN
132 IF (p_uoo_id IS NOT NULL AND p_ass_id IS NULL) THEN
133
134 OPEN c_uoo_ug;
135 FETCH c_uoo_ug INTO c_uoo_ug_rec;
136
137 -- Unit Grading is done anonymously
138 IF c_uoo_ug%FOUND THEN
139 CLOSE c_uoo_ug;
140 RETURN 'Y';
141
142 ELSE -- Unit Grading is NOT done anonymously
143 CLOSE c_uoo_ug;
144 RETURN 'N';
145 END IF;
146
147 ELSIF (p_uoo_id IS NOT NULL AND p_ass_id IS NOT NULL ) THEN
148
149 OPEN c_uoo_ai;
150 FETCH c_uoo_ai INTO c_uoo_ai_rec;
151
152 -- Assessment Item grading is done anonymously
153 IF c_uoo_ai%FOUND THEN
154 CLOSE c_uoo_ai;
155 RETURN 'Y';
156
157 ELSE -- Assessment Item grading is NOT done anonymously
158 CLOSE c_uoo_ai;
159 RETURN 'N';
160 END IF;
161
162 ELSE
163 -- Parameters are not passed properly(P_UOO_ID is passed as NULL),
164 -- hence return NULL
165 RETURN NULL;
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
171 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED');
172 IGS_GE_MSG_STACK.ADD;
173 APP_EXCEPTION.RAISE_EXCEPTION;
174
175 END chk_anon_graded;
176
177 PROCEDURE sub_get_insert(
178 p_method IN igs_as_anon_method.method%TYPE,
179 p_person_id IN hz_parties.party_id%TYPE,
180 p_course_cd IN igs_en_su_attempt_all.course_cd%TYPE,
181 p_unit_cd IN igs_en_su_attempt_all.unit_cd%TYPE,
182 p_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
183 p_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
184 p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
185 p_assessment_type IN igs_as_assessmnt_typ.assessment_type%TYPE,
186 p_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
187 p_load_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE )
188 IS
189 /*
190 || Created By : [email protected]
191 || Created On : 28-JAN-2002
192 || Purpose : This is a private procedure to Insert records in the Anonymous ID
193 || tables as per the Method
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199
200 -- Select the first record from the unallocated anonymous number table IGS_AS_ANON_NUMBER
201 -- for the different methods. or PROGRAM method the cal_type and ci_sequence_number would be NULL.
202 CURSOR c_anon_number(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
203 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
204 IS
205 SELECT anonymous_number
206 FROM igs_as_anon_number
207 WHERE ((load_cal_type = cp_load_cal_type) OR
208 (load_cal_type IS NULL AND cp_load_cal_type IS NULL))
209 AND ((load_ci_sequence_number = cp_load_ci_sequence_number) OR
210 (load_ci_sequence_number IS NULL AND cp_load_ci_sequence_number IS NULL));
211
212 CURSOR c_delete_anon_number(cp_anonymous_number igs_as_anon_number.anonymous_number%TYPE,
213 cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
214 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
215 IS
216 SELECT ROWID
217 FROM igs_as_anon_number
218 WHERE anonymous_number = cp_anonymous_number
219 AND ((load_cal_type = cp_load_cal_type) OR
220 (load_cal_type IS NULL AND cp_load_cal_type IS NULL))
221 AND ((load_ci_sequence_number = cp_load_ci_sequence_number) OR
222 (load_ci_sequence_number IS NULL AND cp_load_ci_sequence_number IS NULL));
223
224
225 l_anonymous_number igs_as_anon_number.anonymous_number%TYPE;
226 l_rowid ROWID;
227 l_rowid_delete ROWID;
228 l_anonymous_id igs_as_anon_id_ps.anonymous_id%TYPE;
229 l_system_generated_ind VARCHAR2(1);
230
231 BEGIN
232
233 OPEN c_anon_number(p_load_cal_type, p_load_ci_sequence_number);
234 FETCH c_anon_number INTO l_anonymous_number;
235 CLOSE c_anon_number;
236
237 -- Delete the number so it can only be used once
238 OPEN c_delete_anon_number(l_anonymous_number,p_load_cal_type, p_load_ci_sequence_number);
239 FETCH c_delete_anon_number INTO l_rowid_delete;
240 CLOSE c_delete_anon_number;
241
242 igs_as_anon_number_pkg.delete_row(l_rowid_delete);
243
244
245 -- Call the USER_ANON_ID user hook to return an Anonymous ID in a format specified by the Institution
246 l_anonymous_id := igs_as_anon_grd_pkg.user_anon_id (
247 p_anonymous_number => l_anonymous_number,
248 p_method => p_method,
249 p_person_id => p_person_id,
250 p_course_cd => p_course_cd,
251 p_unit_cd => p_unit_cd,
252 p_teach_cal_type => p_teach_cal_type,
253 p_teach_ci_sequence_number => p_teach_ci_sequence_number,
254 p_uoo_id => p_uoo_id,
255 p_assessment_type => p_assessment_type,
256 p_load_cal_type => p_load_cal_type,
257 p_load_ci_sequence_number => p_load_ci_sequence_number);
258
259
260 -- Use the Anonymous Number if there is no Institution specific Anonymous ID
261 IF l_anonymous_id IS NULL THEN
262
263 l_anonymous_id := l_anonymous_number;
264 l_system_generated_ind := 'Y';
265 ELSE
266
267 l_system_generated_ind := 'N';
268 END IF;
269
270 IF p_method = 'SECTION' THEN
271
272 igs_as_anon_id_us_pkg.insert_row(
273 x_rowid => l_rowid,
274 x_person_id => p_person_id,
275 x_anonymous_id => l_anonymous_id,
276 x_system_generated_ind => l_system_generated_ind,
277 x_course_cd => p_course_cd,
278 x_unit_cd => p_unit_cd,
279 x_teach_cal_type => p_teach_cal_type,
280 x_teach_ci_sequence_number => p_teach_ci_sequence_number,
281 x_uoo_id => p_uoo_id,
282 x_load_cal_type => p_load_cal_type,
283 x_load_ci_sequence_number => p_load_ci_sequence_number,
284 x_mode => 'R');
285
286 ELSIF p_method = 'ASSESSMENT' THEN
287
288 igs_as_anon_id_ass_pkg.insert_row(
289 x_rowid => l_rowid,
290 x_person_id => p_person_id,
291 x_anonymous_id => l_anonymous_id,
292 x_system_generated_ind => l_system_generated_ind,
293 x_assessment_type => p_assessment_type,
294 x_load_cal_type => p_load_cal_type,
295 x_load_ci_sequence_number => p_load_ci_sequence_number,
296 x_mode =>'R');
297
298 ELSE -- p_method = 'PROGRAM'
299
300 igs_as_anon_id_ps_pkg.insert_row(
301 x_rowid => l_rowid,
302 x_person_id => p_person_id,
303 x_anonymous_id => l_anonymous_id,
304 x_system_generated_ind => l_system_generated_ind,
305 x_course_cd => p_course_cd,
306 x_mode => 'R' );
307 END IF;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Error Message :'||SQLERRM);
312 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
313 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.MNT_ANON_ID.SUB_GET_INSERT');
314 IGS_GE_MSG_STACK.ADD;
315 APP_EXCEPTION.RAISE_EXCEPTION;
316
317 END sub_get_insert; -- END sub_get_insert
318
319
320 PROCEDURE mnt_anon_id (
321 errbuf OUT NOCOPY VARCHAR2,
322 retcode OUT NOCOPY NUMBER,
323 p_load_calendar IN VARCHAR2,
324 p_min_number IN NUMBER,
325 p_max_number IN NUMBER,
326 p_reallocate_anon_id IN VARCHAR2
327 )
328 AS
329 /*
330 || Created By : [email protected]
331 || Created On : 28-JAN-2002
332 || Purpose :
333 ||
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 || swaghmar 16-Jan-2006 Bug# 4951054
339 || swaghmar 23-Feb-2006 Bug# 5056679
340 */
341 -- Define a PL/SQL table to hold the Anonymous
342 TYPE t_temp_table IS TABLE OF igs_as_anon_number.anonymous_number%TYPE
343 INDEX BY BINARY_INTEGER;
344
345 temp_table t_temp_table;
346
347 l_random_number binary_integer;
348 l_index binary_integer;
349 l_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
350 l_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
351 l_anon_id_count NUMBER;
355 l_anum_id NUMBER(15);
352 l_anon_id_required NUMBER;
353 l_rowid ROWID;
354 l_method igs_as_anon_method.method%TYPE;
356
357 -- Exception to handle the error in parameters.
358 PARAM_ERROR EXCEPTION;
359
360 -- Find Anonymous Grading Method for the Load Cal Type supplied
361 CURSOR c_method(cp_load_cal_type igs_as_anon_method.load_cal_type%TYPE)
362 IS
363 SELECT anm.method
364 FROM igs_as_anon_method anm
365 WHERE anm.load_cal_type = cp_load_cal_type;
366
367 -- COUNT the NUMBER of Anonymous Numbers available for ASSESSMENT/SECTION methods
368 CURSOR c_count_anon_num(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
369 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
370 IS
371 SELECT COUNT(*)
372 FROM igs_as_anon_number ann
373 WHERE ann.load_cal_type = cp_load_cal_type
374 AND ann.load_ci_sequence_number = cp_load_ci_sequence_number;
375
376 -- COUNT the NUMBER of Anonymous Numbers available for the PROGRAM method
377 CURSOR c_count_anon_num_prog
378 IS
379 SELECT COUNT(*)
380 FROM igs_as_anon_number ann
381 WHERE ann.load_cal_type IS NULL;
382
383
384 -- Count the number of Student Program Attempts
385 CURSOR c_count_program_reqd
386 IS
387 SELECT COUNT(*)
388 FROM igs_en_stdnt_ps_att spa
389 WHERE spa.course_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'INACTIVE')
390 AND NOT EXISTS (SELECT 'X'
391 FROM igs_as_anon_id_ps anip
392 WHERE anip.person_id = spa.person_id
393 AND anip.course_cd = spa.course_cd);
394
395
396 -- Count the Student Unit Attempts for Unit Sections with anonymous assessment item
397 -- OR unit grading
398 CURSOR c_count_section_reqd(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
399 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
400 IS
401 SELECT COUNT(*)
402 FROM igs_en_su_attempt sua,
403 igs_ps_unit_ofr_opt uoo,
404 igs_ca_teach_to_load_v ttl
405 WHERE sua.unit_attempt_status = 'ENROLLED'
406 AND ttl.load_cal_type = cp_load_cal_type
407 AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
408 AND uoo.cal_type = ttl.teach_cal_type
409 AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
410 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
411 FROM igs_ca_teach_to_load_v ttl2
412 WHERE uoo.cal_type = ttl2.teach_cal_type
413 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
414 AND (uoo.anon_assess_grading_ind = 'Y'
415 OR uoo.anon_unit_grading_ind = 'Y')
416 AND uoo.uoo_id = sua.uoo_id
417 AND NOT EXISTS (SELECT 'X'
418 FROM igs_as_anon_id_us aniu
419 WHERE aniu.person_id = sua.person_id
420 AND aniu.course_cd = sua.course_cd
421 AND aniu.unit_cd = sua.unit_cd
422 AND aniu.teach_cal_type = sua.cal_type
423 AND aniu.teach_ci_sequence_number = sua.ci_sequence_number
424 AND aniu.uoo_id = sua.uoo_id);
425
426
427 -- Count the Student Unit Attempt Assessment Items for anonymous Assessment Types
428 -- in anonymous Unit Sections
429 CURSOR c_count_assessment_reqd(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
430 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
431 IS
432 SELECT COUNT(*)
433 FROM(
434 SELECT DISTINCT sua.person_id, ast.assessment_type
435 FROM igs_ca_teach_to_load_v ttl,
436 igs_en_su_attempt sua,
437 igs_ps_unit_ofr_opt uoo,
438 igs_as_assessmnt_typ ast
439 WHERE sua.unit_attempt_status = 'ENROLLED'
440 AND ttl.load_cal_type = cp_load_cal_type
441 AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
442 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
443 FROM igs_ca_teach_to_load_v ttl2
444 WHERE uoo.cal_type = ttl2.teach_cal_type
445 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
446 AND ttl.teach_cal_type = uoo.cal_type
447 AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
448 AND sua.uoo_id = uoo.uoo_id
449 AND ((uoo.anon_assess_grading_ind = 'Y'
450 AND ast.anon_grading_ind = 'Y'
451 AND EXISTS (SELECT 'X'
452 FROM igs_as_su_atmpt_itm suaai,
453 igs_as_assessmnt_itm ai
454 WHERE sua.person_id = suaai.person_id
455 AND sua.course_cd = suaai.course_cd
456 AND sua.unit_cd = suaai.unit_cd
457 AND sua.cal_type = suaai.cal_type
458 AND sua.ci_sequence_number = suaai.ci_sequence_number
459 AND suaai.ass_id = ai.ass_id
460 AND ai.assessment_type = ast.assessment_type))
461 OR (uoo.anon_unit_grading_ind = 'Y'
462 AND EXISTS (SELECT 'X'
463 FROM igs_as_anon_method anm
464 WHERE anm.load_cal_type = cp_load_cal_type
465 AND anm.assessment_type = ast.assessment_type)))
469 AND ania.assessment_type = ast.assessment_type
466 AND NOT EXISTS (SELECT 'X'
467 FROM igs_as_anon_id_ass ania
468 WHERE ania.person_id = sua.person_id
470 AND ania.load_cal_type = cp_load_cal_type
471 AND ania.load_ci_sequence_number = cp_load_ci_sequence_number));
472
473
474 -- Find all Student Program Attempts
475 CURSOR c_spa
476 IS
477 SELECT spa.person_id,
478 spa.course_cd
479 FROM igs_en_stdnt_ps_att spa
480 WHERE spa.course_attempt_status IN ('ENROLLED', 'UNCONFIRM','INACTIVE')
481 AND NOT EXISTS (SELECT 'X'
482 FROM igs_as_anon_id_ps anip
483 WHERE anip.person_id = spa.person_id
484 AND anip.course_cd = spa.course_cd);
485
486 -- Search for all Student Unit Attempts related to the Load Calendar
487 CURSOR c_sua(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
488 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
489 IS
490 SELECT sua.person_id,
491 sua.course_cd,
492 sua.unit_cd,
493 sua.cal_type,
494 sua.ci_sequence_number,
495 sua.uoo_id
496 FROM igs_ca_teach_to_load_v ttl,
497 igs_en_su_attempt sua,
498 igs_ps_unit_ofr_opt uoo
499 WHERE sua.unit_attempt_status = 'ENROLLED'
500 AND ttl.load_cal_type = cp_load_cal_type
501 AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
502 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
503 FROM igs_ca_teach_to_load_v ttl2
504 WHERE uoo.cal_type = ttl2.teach_cal_type
505 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
506 AND ttl.teach_cal_type = uoo.cal_type
507 AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
508 AND (uoo.anon_unit_grading_ind = 'Y'
509 OR uoo.anon_assess_grading_ind = 'Y')
510 AND sua.uoo_id = uoo.uoo_id
511 AND NOT EXISTS (SELECT 'X'
512 FROM igs_as_anon_id_us aniu
513 WHERE aniu.person_id = sua.person_id
514 AND aniu.course_cd = sua.course_cd
515 AND aniu.unit_cd = sua.unit_cd
516 AND aniu.teach_cal_type = sua.cal_type
517 AND aniu.teach_ci_sequence_number = sua.ci_sequence_number
518 AND aniu.uoo_id = sua.uoo_id);
519
520 -- Search for all Student Unit Attempt Assessment Items related to the Load Calendar
521 CURSOR c_suaai(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
522 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
523 IS
524 SELECT DISTINCT sua.person_id,
525 ast.assessment_type
526 FROM igs_ca_teach_to_load_v ttl,
527 igs_en_su_attempt sua,
528 igs_ps_unit_ofr_opt uoo,
529 igs_as_assessmnt_typ ast
530 WHERE sua.unit_attempt_status = 'ENROLLED'
531 AND ttl.load_cal_type = cp_load_cal_type
532 AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
533 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
534 FROM igs_ca_teach_to_load_v ttl2
535 WHERE uoo.cal_type = ttl2.teach_cal_type
536 AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
537 AND ttl.teach_cal_type = uoo.cal_type
538 AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
539 AND sua.uoo_id = uoo.uoo_id
540 AND ((uoo.anon_assess_grading_ind = 'Y'
541 AND ast.anon_grading_ind = 'Y'
542 AND EXISTS (SELECT 'X'
543 FROM igs_as_su_atmpt_itm suaai,
544 igs_as_assessmnt_itm ai
545 WHERE sua.person_id = suaai.person_id
546 AND sua.course_cd = suaai.course_cd
547 AND sua.unit_cd = suaai.unit_cd
548 AND sua.cal_type = suaai.cal_type
549 AND sua.ci_sequence_number = suaai.ci_sequence_number
550 AND suaai.ass_id = ai.ass_id
551 AND ai.assessment_type = ast.assessment_type))
552 OR (uoo.anon_unit_grading_ind = 'Y'
553 AND EXISTS (SELECT 'X'
554 FROM igs_as_anon_method anm
555 WHERE anm.load_cal_type = cp_load_cal_type
556 AND anm.assessment_type = ast.assessment_type)))
557 AND NOT EXISTS (SELECT 'X'
558 FROM igs_as_anon_id_ass ania
559 WHERE ania.person_id = sua.person_id
560 AND ania.assessment_type = ast.assessment_type
561 AND ania.load_cal_type = cp_load_cal_type
562 AND ania.load_ci_sequence_number = cp_load_ci_sequence_number) ;
563
564
565 -- Find all the records in the Context Load Calendar for ASSESSMENT method
566 -- (To delete the records when P_REALLOCATE_ID = 'Y')
567 CURSOR c_ass_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
568 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
569 IS
570 SELECT ROWID
571 FROM igs_as_anon_id_ass ania
572 WHERE ania.load_cal_type = cp_load_cal_type
573 AND ania.load_ci_sequence_number = cp_load_ci_sequence_number;
574
575 -- Find all the records in the Context Load Calendar for SECTION method
576 -- (To delete the records when P_REALLOCATE_ID = 'Y')
577 CURSOR c_section_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
581 FROM igs_as_anon_id_us aniu
578 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
579 IS
580 SELECT ROWID
582 WHERE aniu.load_cal_type = cp_load_cal_type
583 AND aniu.load_ci_sequence_number = cp_load_ci_sequence_number;
584
585 -- Find all the records of PROGRAM method
586 -- (To delete the records when P_REALLOCATE_ID = 'Y')
587 CURSOR c_program_delete
588 IS
589 SELECT ROWID
590 FROM igs_as_anon_id_ps;
591
592 -- Find all records of PROGRAM method
593 -- (To delete the records when P_REALLOCATE_ID = 'Y')
594 CURSOR c_anon_num_delete_prog
595 IS
596 SELECT ROWID
597 FROM igs_as_anon_number ann
598 WHERE ann.load_cal_type IS NULL;
599
600 -- Find all records of SECTION/ASSESSMENT method
601 -- (To delete the records when P_REALLOCATE_ID = 'Y')
602 CURSOR c_anon_num_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
603 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
604 IS
605 SELECT ROWID
606 FROM igs_as_anon_number ann
607 WHERE ann.load_cal_type = cp_load_cal_type
608 AND ann.load_ci_sequence_number = cp_load_ci_sequence_number;
609
610 -- To get the Parameter Value
611 CURSOR c_reallocate
612 IS
613 SELECT meaning
614 FROM FND_LOOKUP_VALUES
615 WHERE lookup_type = 'SYS_YES_NO'
616 AND language = userenv ('LANG')
617 AND VIEW_APPLICATION_ID = 8405 AND SECURITY_GROUP_ID = 0
618 AND lookup_code = p_reallocate_anon_id;
619
620 -- To get the Start and End date of the Calander
621 CURSOR c_cal(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
622 cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
623 IS
624 SELECT alternate_code,start_dt, end_dt
625 FROM igs_ca_inst
626 WHERE cal_type = cp_load_cal_type
627 AND sequence_number = cp_load_ci_sequence_number;
628
629 -- To get the Person Number of the Person processed.
630 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE)
631 IS
632 SELECT party_number
633 FROM hz_parties
634 WHERE party_id = cp_person_id;
635
636 c_method_rec c_method%ROWTYPE;
637 c_reallocate_rec c_reallocate%ROWTYPE;
638 c_cal_rec c_cal%ROWTYPE;
639 c_person_rec c_person%ROWTYPE;
640
641 BEGIN
642
643 --Intialize Error Code
644 retcode := 0;
645
646 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
647
648 -- To fetch the cal_type and sequence_number from the Award Year passed.
649 l_ld_cal_type := LTRIM(RTRIM(SUBSTR(p_load_calendar,1,10)));
650 l_ld_sequence_number := TO_NUMBER(SUBSTR(p_load_calendar,-6));
651
652 OPEN c_reallocate;
653 FETCH c_reallocate INTO c_reallocate_rec;
654 CLOSE c_reallocate;
655
656 OPEN c_cal(l_ld_cal_type,l_ld_sequence_number);
657 FETCH c_cal INTO c_cal_rec;
658 CLOSE c_cal;
659
660 /* Print the Parameters Passed */
661
662 FND_FILE.PUT_LINE(FND_FILE.LOG,'+-------------------------Parameters Passed---------------------------------+');
663 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
664 FND_FILE.PUT_LINE(FND_FILE.LOG,'Load Calendar : ' || l_ld_cal_type);
665 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calendar Start Date : ' || TO_CHAR(c_cal_rec.start_dt,'DD-MON-YYYY'));
666 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calendar End Date : ' || TO_CHAR(c_cal_rec.end_dt,'DD-MON-YYYY')) ;
667 FND_FILE.PUT_LINE(FND_FILE.LOG,'Minimum limit of Range : ' || TO_CHAR(p_min_number));
668 FND_FILE.PUT_LINE(FND_FILE.LOG,'Maximum limit of Range : ' || TO_CHAR(p_max_number));
669 FND_FILE.PUT_LINE(FND_FILE.LOG,'Reallocate Anonymous ID : ' || c_reallocate_rec.meaning );
670
671 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
672
673 -- Find Anonymous Grading Method for the Load Cal Type supplied
674 OPEN c_method(l_ld_cal_type);
675 FETCH c_method INTO c_method_rec;
676 IF c_method%NOTFOUND THEN
677 CLOSE c_method;
678 FND_MESSAGE.SET_NAME('IGS','IGS_AS_NO_ANON_METHOD');
679 IGS_GE_MSG_STACK.ADD;
680 RAISE PARAM_ERROR;
681 END IF;
682 CLOSE c_method;
683
684 l_method := c_method_rec.method;
685
686 -- Check if the anonymous number range supplied is valid
687 IF NVL(p_min_number, 1) > p_max_number THEN
688 FND_MESSAGE.SET_NAME('IGS','IGS_AS_INVALID_ANON_RANGE');
689 IGS_GE_MSG_STACK.ADD;
690 RAISE PARAM_ERROR;
691 END IF;
692
693 -- Reallocate Anonymous Numbers
694 IF NVL(p_reallocate_anon_id, '2') = '1' THEN
695
696 IF l_method = 'ASSESSMENT' THEN
697 -- Delete Allocated Anonymous IDs
698 FOR c_ass_delete_rec IN c_ass_delete(l_ld_cal_type,l_ld_sequence_number) LOOP
699 igs_as_anon_id_ass_pkg.delete_row(c_ass_delete_rec.ROWID);
700 END LOOP;
701
702 ELSIF l_method = 'SECTION' THEN
703 -- Delete Allocated Anonymous IDs
704 FOR c_section_delete_rec IN c_section_delete(l_ld_cal_type,l_ld_sequence_number)
705 LOOP
709 ELSE -- 'PROGRAM'
706 igs_as_anon_id_us_pkg.delete_row(c_section_delete_rec.ROWID);
707 END LOOP;
708
710 -- Delete Allocated Anonymous IDs
711 FOR c_program_delete_rec IN c_program_delete
712 LOOP
713 igs_as_anon_id_ps_pkg.delete_row(c_program_delete_rec.ROWID);
714 END LOOP;
715
716 -- Delete Unallocated Anonymous IDs
717 FOR c_anon_num_delete_prog_rec IN c_anon_num_delete_prog
718 LOOP
719 igs_as_anon_number_pkg.delete_row(c_anon_num_delete_prog_rec.ROWID);
720 END LOOP;
721
722 END IF;
723 -- Delete Unallocated Anonymous IDs
724 FOR c_anon_num_delete_rec IN c_anon_num_delete(l_ld_cal_type,l_ld_sequence_number)
725 LOOP
726 igs_as_anon_number_pkg.delete_row(c_anon_num_delete_rec.ROWID);
727 END LOOP;
728
729 END IF;
730
731 -- Check how many Anonymous Numbers are available for SECTION/ASSESSMENT methods.
732 OPEN c_count_anon_num(l_ld_cal_type,l_ld_sequence_number);
733 FETCH c_count_anon_num INTO l_anon_id_count;
734 CLOSE c_count_anon_num;
735
736 -- Check how many Anonymous Numbers are required not including those with numbers already allocated.
737 IF l_method = 'PROGRAM' THEN
738 -- Check how many Anonymous Numbers are available for PROGRAM method.
739 OPEN c_count_anon_num_prog;
740 FETCH c_count_anon_num_prog INTO l_anon_id_count;
741 CLOSE c_count_anon_num_prog;
742
743 -- Count the number of Student Program Attempts
744 OPEN c_count_program_reqd;
745 FETCH c_count_program_reqd INTO l_anon_id_required;
746 CLOSE c_count_program_reqd;
747
748 ELSIF l_method = 'SECTION' THEN
749 -- Count the Student Unit Attempts for Unit Sections with anonymous assessment item
750 -- OR unit grading
751 OPEN c_count_section_reqd(l_ld_cal_type,l_ld_sequence_number);
752 FETCH c_count_section_reqd INTO l_anon_id_required;
753 CLOSE c_count_section_reqd;
754
755 ELSIF l_method = 'ASSESSMENT' THEN
756 -- Count the Student Unit Attempt Assessment Items for anonymous Assessment Types
757 -- in anonymous Unit Sections
758 OPEN c_count_assessment_reqd(l_ld_cal_type,l_ld_sequence_number);
759 FETCH c_count_assessment_reqd INTO l_anon_id_required;
760 CLOSE c_count_assessment_reqd;
761
762 END IF;
763 -- Check whether Anonymous Numbers are already available for the
764 -- Context Load Calendar. If its present the Range passed will not be considered
765 -- and existing Anonymous Numbers will be used.
766 IF l_anon_id_count > 0 THEN
767
768 FND_MESSAGE.SET_NAME('IGS','IGS_AS_ANON_RANGE_N_USE');
769 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
770 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
771 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
772
773 -- Check if the available anonymous numbers cover the required number
774 -- If enough Anonymous Numbers are not present then show ERROR with proper Message.
775
776 IF l_anon_id_count < l_anon_id_required THEN
777
778 FND_MESSAGE.SET_NAME('IGS','IGS_AS_NOT_ENOUGH_ANON_NUMBER');
779 IGS_GE_MSG_STACK.ADD;
780 RAISE PARAM_ERROR;
781 END IF;
782
783 -- If there are no Anonymous Numbers available generate new ones
784 -- only if the REQUIRED is > 0.
785 ELSIF l_anon_id_required > 0 THEN
786 -- Check if the anonymous number range covers the number required plus 50% to allow for growth
787 -- If the Range is not sufficient as per the ANON ID required then show ERROR with proper Message.
788 IF (p_max_number - NVL(p_min_number, 0)) < (NVL(l_anon_id_required, 1) * 1.5) THEN
789 FND_MESSAGE.SET_NAME('IGS','IGS_AS_ANON_RANGE_TOO_SMALL');
790 IGS_GE_MSG_STACK.ADD;
791 RAISE PARAM_ERROR;
792 END IF;
793
794
795 -- Added by DDEY as a part of Bug # 2280067 .
796 -- Return an error if the Range is to large for a Binary Integer
797 -- The process becomes less efficient, in case the the whole table is searched, so the
798 -- number of records searched are reduced. This acts as temporary solution.
799
800 IF (p_max_number - NVL(p_min_number, 0)) > 1000000000 THEN
801 FND_MESSAGE.SET_NAME('IGS','IGS_AS_OUT_ANON_RANGE');
802 IGS_GE_MSG_STACK.ADD;
803 RAISE PARAM_ERROR;
804 END IF;
805
806 -- Populate a PL/SQL table with an anonymous number and a random number
807 FOR l_anonymous_number IN NVL(p_min_number,1) .. p_max_number LOOP
808
809 -- Added by DDEY as a part of Bug # 2280067 .
810 -- This would avoid the negative values and the non-unique values, to be generated.
811
812 LOOP
813 -- swaghmar 23-Feb-2006 Bug# 5056679
814 l_random_number := FND_CRYPTO.SmallRandomNumber;
815 IF NOT temp_table.EXISTS(l_random_number) THEN
816 temp_table(l_random_number) := l_anonymous_number;
817 EXIT;
818 END IF;
819 END LOOP;
820
821 END LOOP;
822
823 l_index := temp_table.FIRST;
824
825 LOOP
826
827 -- Added by DDEY as a part of Bug # 2280067 .
828 -- The If condition is added
829
833 IF c_method_rec.method = 'PROGRAM' THEN
830 IF temp_table.EXISTS(l_index) THEN
831
832 -- Insert the Randomly generated Anonymous Numbers
834 igs_as_anon_number_pkg.insert_row(
835 x_rowid => l_rowid,
836 x_anum_id => l_anum_id,
837 x_anonymous_number => temp_table(l_index),
838 x_load_cal_type => NULL,
839 x_load_ci_sequence_number => NULL,
840 x_mode => 'R');
841 ELSE
842 igs_as_anon_number_pkg.insert_row(
843 x_rowid => l_rowid,
844 x_anum_id => l_anum_id,
845 x_anonymous_number => temp_table(l_index),
846 x_load_cal_type => l_ld_cal_type,
847 x_load_ci_sequence_number => l_ld_sequence_number,
848 x_mode => 'R');
849 END IF;
850 END IF;
851 EXIT WHEN l_index = temp_table.LAST;
852 l_index := temp_table.NEXT(l_index);
853 END LOOP;
854
855 -- Delete all the Records from the PL/SQL table
856 temp_table.DELETE;
857
858 END IF;
859
860 FND_FILE.PUT_LINE(FND_FILE.LOG,'+-------------------------Persons Processed---------------------------------+');
861
862 IF l_method = 'PROGRAM' THEN
863 FOR c_spa_rec IN c_spa
864 LOOP
865 OPEN c_person(c_spa_rec.person_id);
866 FETCH c_person INTO c_person_rec;
867 CLOSE c_person;
868
869 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
870 FND_FILE.PUT_LINE(FND_FILE.LOG,'Person Number : ' || c_person_rec.party_number);
871 FND_FILE.PUT_LINE(FND_FILE.LOG,'Course Code : ' || c_spa_rec.course_cd);
872 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
873
874 sub_get_insert( p_method => l_method,
875 p_person_id => c_spa_rec.person_id,
876 p_course_cd => c_spa_rec.course_cd,
877 p_unit_cd => NULL,
878 p_teach_cal_type => NULL,
879 p_teach_ci_sequence_number => NULL,
880 p_uoo_id => NULL,
881 p_assessment_type => NULL,
882 p_load_cal_type => NULL,
883 p_load_ci_sequence_number => NULL);
884
885 END LOOP;
886
887 ELSIF l_method = 'SECTION' THEN
888
889 FOR c_sua_rec IN c_sua(l_ld_cal_type,l_ld_sequence_number)
890 LOOP
891
892 OPEN c_person(c_sua_rec.person_id);
893 FETCH c_person INTO c_person_rec;
894 CLOSE c_person;
895
896 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
897 FND_FILE.PUT_LINE(FND_FILE.LOG,'Person Number : ' || c_person_rec.party_number);
898 FND_FILE.PUT_LINE(FND_FILE.LOG,'Course Code : ' || c_sua_rec.course_cd);
899 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unit Code : ' || c_sua_rec.unit_cd);
900 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unit Offering Option ID: ' || c_sua_rec.uoo_id);
901 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
902
903 sub_get_insert( p_method => l_method,
904 p_person_id => c_sua_rec.person_id,
905 p_course_cd => c_sua_rec.course_cd,
906 p_unit_cd => c_sua_rec.unit_cd,
907 p_teach_cal_type => c_sua_rec.cal_type,
908 p_teach_ci_sequence_number => c_sua_rec.ci_sequence_number,
909 p_uoo_id => c_sua_rec.uoo_id,
910 p_assessment_type => NULL,
911 p_load_cal_type => l_ld_cal_type,
912 p_load_ci_sequence_number => l_ld_sequence_number);
913
914 END LOOP;
915
916 ELSIF l_method = 'ASSESSMENT' THEN
917
918 FOR c_suaai_rec IN c_suaai(l_ld_cal_type,l_ld_sequence_number)
919 LOOP
920
921 OPEN c_person(c_suaai_rec.person_id);
922 FETCH c_person INTO c_person_rec;
923 CLOSE c_person;
924
925 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
926 FND_FILE.PUT_LINE(FND_FILE.LOG,'Person Number : ' || c_person_rec.party_number);
927 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assessment Type : ' || c_suaai_rec.assessment_type);
928 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
929
930 sub_get_insert( p_method => l_method,
931 p_person_id => c_suaai_rec.person_id,
932 p_course_cd => NULL,
933 p_unit_cd => NULL,
934 p_teach_cal_type => NULL,
935 p_teach_ci_sequence_number => NULL,
936 p_uoo_id => NULL,
937 p_assessment_type => c_suaai_rec.assessment_type,
938 p_load_cal_type => l_ld_cal_type,
939 p_load_ci_sequence_number => l_ld_sequence_number);
940
941 END LOOP;
942 END IF;
943
944
945 EXCEPTION
946
950
947 WHEN PARAM_ERROR THEN
948 ROLLBACK ;
949 retcode := 1 ;
951 FND_FILE.PUT_LINE(fnd_file.log,' ');
952 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
953 FND_FILE.PUT_LINE(fnd_file.log,' ');
954
955
956 WHEN OTHERS THEN
957 ROLLBACK ;
958 retcode := 2 ;
959 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Error Message :'||SQLERRM);
960 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
961 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.MNT_ANON_ID');
962 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
963 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
964
965 END mnt_anon_id;
966
967
968 FUNCTION get_anon_id (
969 p_person_id IN hz_parties.party_id%TYPE,
970 p_course_cd IN igs_en_su_attempt_all.course_cd%TYPE,
971 p_unit_cd IN igs_en_su_attempt_all.unit_cd%TYPE,
972 p_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
973 p_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
974 p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
975 p_ass_id IN igs_as_assessmnt_itm_all.ass_id%TYPE,
976 p_unit_grading_ind IN VARCHAR2
977 ) RETURN VARCHAR2 IS
978 /*
979 || Created By : pkpatel
980 || Created On : 28-JAN-2002
981 || Purpose : This function returns the Anonymous ID of the Student as Parameters passed.
982 || Known limitations, enhancements or remarks :
983 || Change History :
984 || Who When What
985 || (reverse chronological order - newest change first)
986 */
987
988 CURSOR c_anip
989 IS
990 SELECT anonymous_id
991 FROM igs_as_anon_id_ps anip,
992 igs_en_su_attempt sua,
993 igs_ps_unit_ofr_opt uoo
994 WHERE sua.person_id = p_person_id
995 AND sua.course_cd = p_course_cd
996 AND sua.unit_cd = p_unit_cd
997 AND sua.cal_type = p_teach_cal_type
998 AND sua.ci_sequence_number = p_teach_ci_sequence_number
999 AND sua.uoo_id = p_uoo_id
1000 AND uoo.unit_cd = p_unit_cd
1001 AND uoo.cal_type = p_teach_cal_type
1002 AND uoo.ci_sequence_number = p_teach_ci_sequence_number
1003 AND uoo.version_number = sua.version_number
1004 AND uoo.uoo_id = sua.uoo_id
1005 AND anip.person_id = p_person_id
1006 AND anip.course_cd = p_course_cd
1007 AND ( ( p_unit_grading_ind = 'Y'
1008 AND uoo.anon_unit_grading_ind = 'Y')
1009 OR ( p_unit_grading_ind = 'N'
1010 AND p_ass_id IS NOT NULL
1011 AND uoo.anon_assess_grading_ind = 'Y'
1012 AND 'Y' = ( SELECT ast.anon_grading_ind
1013 FROM igs_as_assessmnt_typ ast,
1014 igs_as_assessmnt_itm ai
1015 WHERE ai.ass_id = p_ass_id
1016 AND ai.assessment_type = ast.assessment_type)));
1017
1018 CURSOR c_aniu
1019 IS
1020 SELECT anonymous_id
1021 FROM igs_as_anon_id_us aniu,
1022 igs_en_su_attempt sua,
1023 igs_ps_unit_ofr_opt uoo
1024 WHERE sua.person_id = p_person_id
1025 AND sua.course_cd = p_course_cd
1026 AND sua.unit_cd = p_unit_cd
1027 AND sua.cal_type = p_teach_cal_type
1028 AND sua.ci_sequence_number = p_teach_ci_sequence_number
1029 AND sua.uoo_id = p_uoo_id
1030 AND uoo.uoo_id = sua.uoo_id
1031 AND uoo.unit_cd = p_unit_cd
1032 AND uoo.cal_type = p_teach_cal_type
1033 AND uoo.ci_sequence_number = p_teach_ci_sequence_number
1034 AND uoo.version_number = sua.version_number
1035 AND aniu.person_id = p_person_id
1036 AND aniu.course_cd = p_course_cd
1037 AND aniu.unit_cd = p_unit_cd
1038 AND aniu.teach_cal_type = p_teach_cal_type
1039 AND aniu.teach_ci_sequence_number = p_teach_ci_sequence_number
1040 AND aniu.uoo_id = p_uoo_id
1041 AND ( ( p_unit_grading_ind = 'Y'
1042 AND uoo.anon_unit_grading_ind = 'Y')
1043 OR ( p_unit_grading_ind = 'N'
1044 AND p_ass_id IS NOT NULL
1045 AND uoo.anon_assess_grading_ind = 'Y'
1046 AND 'Y' = ( SELECT ast.anon_grading_ind
1047 FROM igs_as_assessmnt_typ ast,
1048 igs_as_assessmnt_itm ai
1049 WHERE ai.ass_id = p_ass_id
1050 AND ai.assessment_type = ast.assessment_type)));
1051
1052 CURSOR c_ania
1053 IS
1054 SELECT anonymous_id
1055 FROM igs_as_anon_id_ass ania,
1056 igs_en_su_attempt sua,
1057 igs_ps_unit_ofr_opt uoo,
1058 igs_ca_teach_to_load_v ttl
1059 WHERE sua.person_id = p_person_id
1060 AND sua.course_cd = p_course_cd
1061 AND sua.unit_cd = p_unit_cd
1062 AND sua.cal_type = p_teach_cal_type
1063 AND sua.ci_sequence_number = p_teach_ci_sequence_number
1064 AND sua.uoo_id = p_uoo_id
1065 AND uoo.unit_cd = p_unit_cd
1066 AND uoo.cal_type = p_teach_cal_type
1067 AND uoo.ci_sequence_number = p_teach_ci_sequence_number
1068 AND uoo.version_number = sua.version_number
1069 AND uoo.uoo_id = sua.uoo_id
1070 AND ania.person_id = p_person_id
1071 AND ania.load_cal_type = ttl.load_cal_type
1072 AND ania.load_ci_sequence_number = ttl.load_ci_sequence_number
1073 AND ttl.teach_cal_type = p_teach_cal_type
1074 AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
1078 AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
1075 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
1076 FROM igs_ca_teach_to_load_v ttl2
1077 WHERE ttl2.teach_cal_type = p_teach_cal_type
1079 AND ( ( p_unit_grading_ind = 'Y'
1080 AND uoo.anon_unit_grading_ind = 'Y'
1081 AND ania.assessment_type = (SELECT anm.assessment_type
1082 FROM igs_as_anon_method anm
1083 WHERE anm.method = 'ASSESSMENT'
1084 AND anm.load_cal_type = ttl.load_cal_type))
1085 OR ( p_ass_id IS NOT NULL
1086 AND p_unit_grading_ind = 'N'
1087 AND uoo.anon_assess_grading_ind = 'Y'
1088 AND ania.assessment_type = (SELECT ai.assessment_type
1089 FROM igs_as_assessmnt_typ ast,
1090 igs_as_assessmnt_itm ai
1091 WHERE ai.ass_id = p_ass_id
1092 AND ai.assessment_type = ast.assessment_type
1093 AND ast.anon_grading_ind = 'Y')));
1094
1095 CURSOR c_method
1096 IS
1097 SELECT anm.method
1098 FROM igs_ca_teach_to_load_v ttl,
1099 igs_as_anon_method anm
1100 WHERE ttl.teach_cal_type = p_teach_cal_type
1101 AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
1102 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
1103 FROM igs_ca_teach_to_load_v ttl2
1104 WHERE ttl2.teach_cal_type = p_teach_cal_type
1105 AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
1106 AND ttl.load_cal_type = anm.load_cal_type;
1107
1108 c_method_rec c_method%ROWTYPE;
1109 c_ania_rec c_ania%ROWTYPE;
1110 c_aniu_rec c_aniu%ROWTYPE;
1111 c_anip_rec c_anip%ROWTYPE;
1112
1113 BEGIN
1114
1115 -- Check whether all the Parameters are passed properly. If any of the Parameter is passed INVALID then
1116 -- return NULL.
1117 IF p_person_id IS NULL OR
1118 p_course_cd IS NULL OR
1119 p_unit_cd IS NULL OR
1120 p_teach_cal_type IS NULL OR
1121 p_teach_ci_sequence_number IS NULL OR
1122 p_uoo_id IS NULL OR
1123 (p_unit_grading_ind = 'Y' AND p_ass_id IS NOT NULL) OR
1124 (NVL(p_unit_grading_ind, 'N') = 'N' AND p_ass_id IS NULL) THEN
1125
1126 RETURN NULL;
1127
1128 END IF;
1129
1130
1131 OPEN c_method;
1132 FETCH c_method INTO c_method_rec;
1133
1134 -- Return NULL if no method defined
1135 IF c_method%NOTFOUND THEN
1136 CLOSE c_method;
1137 RETURN NULL;
1138 END IF;
1139 CLOSE c_method;
1140
1141 IF c_method_rec.method = 'PROGRAM' THEN
1142 OPEN c_anip;
1143 FETCH c_anip INTO c_anip_rec;
1144 IF c_anip%FOUND THEN
1145 CLOSE c_anip;
1146 RETURN c_anip_rec.anonymous_id;
1147 END IF;
1148
1149 CLOSE c_anip;
1150
1151 ELSIF c_method_rec.method = 'SECTION' THEN
1152 OPEN c_aniu;
1153 FETCH c_aniu INTO c_aniu_rec;
1154 IF c_aniu%FOUND THEN
1155 CLOSE c_aniu;
1156 RETURN c_aniu_rec.anonymous_id;
1157 END IF;
1158 CLOSE c_aniu;
1159
1160 ELSIF c_method_rec.method = 'ASSESSMENT' THEN
1161 OPEN c_ania;
1162 FETCH c_ania INTO c_ania_rec;
1163 IF c_ania%FOUND THEN
1164 CLOSE c_ania;
1165 RETURN c_ania_rec.anonymous_id;
1166 END IF;
1167 CLOSE c_ania;
1168
1169 END IF;
1170
1171 -- Return NULL if for any of the method the Anonymous ID is not found
1172 RETURN NULL;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1177 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.GET_ANON_ID');
1178 IGS_GE_MSG_STACK.ADD;
1179 APP_EXCEPTION.RAISE_EXCEPTION;
1180
1181 END get_anon_id;
1182
1183
1184
1185 FUNCTION get_person_id (
1186 p_anonymous_id IN igs_as_anon_id_ps.anonymous_id%TYPE,
1187 p_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1188 p_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1189 ) RETURN NUMBER IS
1190
1191 /*
1192 || Created By : pkpatel
1193 || Created On : 28-JAN-2002
1194 || Purpose : This function returns the person ID of the Student as Parameters passed.
1195 || Known limitations, enhancements or remarks :
1196 || Change History :
1197 || Who When What
1198 || (reverse chronological order - newest change first)
1199 */
1200
1201 CURSOR c_anip
1202 IS
1203 SELECT person_id
1204 FROM igs_as_anon_id_ps anip
1205 WHERE anip.anonymous_id = p_anonymous_id;
1206
1207 CURSOR c_aniu
1208 IS
1209 SELECT person_id
1210 FROM igs_as_anon_id_us aniu,
1211 igs_ca_teach_to_load_v ttl
1212 WHERE aniu. anonymous_id = p_anonymous_id
1213 AND aniu.load_cal_type = ttl.load_cal_type
1214 AND aniu.load_ci_sequence_number = ttl.load_ci_sequence_number
1215 AND ttl.teach_cal_type = p_teach_cal_type
1219 WHERE ttl2.teach_cal_type = p_teach_cal_type
1216 AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
1217 AND ttl.load_start_dt = ( SELECT MIN(ttl2.load_start_dt)
1218 FROM igs_ca_teach_to_load_v ttl2
1220 AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number);
1221 CURSOR c_ania
1222 IS
1223 SELECT person_id
1224 FROM igs_as_anon_id_ass ania,
1225 igs_ca_teach_to_load_v ttl
1226 WHERE ania. anonymous_id = p_anonymous_id
1227 AND ania.load_cal_type = ttl.load_cal_type
1228 AND ania.load_ci_sequence_number = ttl.load_ci_sequence_number
1229 AND ttl.teach_cal_type = p_teach_cal_type
1230 AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
1231 AND ttl.load_start_dt = ( SELECT MIN(ttl2.load_start_dt)
1232 FROM igs_ca_teach_to_load_v ttl2
1233 WHERE ttl2.teach_cal_type = p_teach_cal_type
1234 AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number);
1235
1236
1237 -- Get the Anonymous Grading Method for this Load Calendar
1238 CURSOR c_method
1239 IS
1240 SELECT anm.method
1241 FROM igs_ca_teach_to_load_v ttl,
1242 igs_as_anon_method anm
1243 WHERE ttl.teach_cal_type = p_teach_cal_type
1244 AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
1245 AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
1246 FROM igs_ca_teach_to_load_v ttl2
1247 WHERE ttl2.teach_cal_type = p_teach_cal_type
1248 AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
1249 AND ttl.load_cal_type = anm.load_cal_type;
1250
1251 c_method_rec c_method%ROWTYPE;
1252 c_ania_rec c_ania%ROWTYPE;
1253 c_aniu_rec c_aniu%ROWTYPE;
1254 c_anip_rec c_anip%ROWTYPE;
1255
1256 BEGIN
1257
1258 -- Check whether all the Parameters are passed properly. If any of the Parameter is passed INVALID then
1259 -- return NULL.
1260 IF p_anonymous_id IS NULL OR
1261 p_teach_cal_type IS NULL OR
1262 p_teach_ci_sequence_number IS NULL THEN
1263
1264 RETURN NULL;
1265
1266 END IF;
1267
1268 OPEN c_method;
1269 FETCH c_method INTO c_method_rec;
1270
1271 -- Return NULL if no method defined
1272 IF c_method%NOTFOUND THEN
1273 CLOSE c_method;
1274 RETURN NULL;
1275 END IF;
1276 CLOSE c_method;
1277
1278 IF c_method_rec.method = 'PROGRAM' THEN
1279 OPEN c_anip;
1280 FETCH c_anip INTO c_anip_rec;
1281 IF c_anip%FOUND THEN
1282 CLOSE c_anip;
1283 RETURN c_anip_rec.person_id;
1284 END IF;
1285 CLOSE c_anip;
1286
1287 ELSIF c_method_rec.method = 'SECTION' THEN
1288 OPEN c_aniu;
1289 FETCH c_aniu INTO c_aniu_rec;
1290 IF c_aniu%FOUND THEN
1291 CLOSE c_aniu;
1292 RETURN c_aniu_rec.person_id;
1293 END IF;
1294 CLOSE c_aniu;
1295
1296 ELSIF c_method_rec.method = 'ASSESSMENT' THEN
1297 OPEN c_ania;
1298 FETCH c_ania INTO c_ania_rec;
1299 IF c_ania%FOUND THEN
1300 CLOSE c_ania;
1301 RETURN c_ania_rec.person_id;
1302 END IF;
1303 CLOSE c_ania;
1304
1305 END IF;
1306
1307 -- Return NULL if for any of the method the Person ID is not found
1308 RETURN NULL;
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1313 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.GET_PERSON_ID');
1314 IGS_GE_MSG_STACK.ADD;
1315 APP_EXCEPTION.RAISE_EXCEPTION;
1316 END get_person_id;
1317
1318
1319 FUNCTION user_anon_id (
1320 p_anonymous_number IN varchar2,
1321 p_method IN igs_as_anon_method.METHOD%TYPE,
1322 p_person_id IN hz_parties.party_id%TYPE,
1323 p_course_cd IN igs_en_su_attempt_all.course_cd%TYPE,
1324 p_unit_cd IN igs_en_su_attempt_all.unit_cd%TYPE,
1325 p_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1326 p_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1327 p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
1328 p_assessment_type IN igs_as_assessmnt_typ.assessment_type%TYPE,
1329 p_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1330 p_load_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1331 ) RETURN VARCHAR2 IS
1332 /*
1333 || Created By : pkpatel
1334 || Created On : 28-JAN-2002
1335 || Purpose : The logic for this function will be user defined to allow Institutions
1336 || to define their own specific Anonymous IDs
1337 || Known limitations, enhancements or remarks :
1338 || Change History :
1339 || Who When What
1340 || (reverse chronological order - newest change first)
1341 */
1342
1343 BEGIN
1344
1345 -- The logic for this function will be user defined to allow Institutions to define their own specific Anonymous IDs
1346 NULL;
1347 RETURN NULL;
1348
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1352 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.USER_ANON_ID');
1353 IGS_GE_MSG_STACK.ADD;
1354 APP_EXCEPTION.RAISE_EXCEPTION;
1355 END user_anon_id;
1356
1357 END igs_as_anon_grd_pkg;
[Home] [Help]