1 PACKAGE BODY igs_as_adi_upld_aio_pkg AS
2 /* $Header: IGSAS44B.pls 120.7 2006/05/29 06:26:14 ijeddy ship $ */
3 --
4 FUNCTION get_sua_yop (
5 p_person_id IN igs_en_su_attempt.person_id%TYPE,
6 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
7 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
8 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE
9 ) RETURN VARCHAR2 AS
10 --
11 -- This function returns the Unit Set Code of any YOP specific Student Unit
12 -- Set Attempts which have Selection and Requirements Complete/End Dates
13 -- which span the Census Date of the Teaching Period provided. If more than
14 -- one exists the one with the latest selection date will be returned
15 --
16 v_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE;
17 --
18 --
19 --
20 CURSOR c_susa IS
21 SELECT us.title
22 FROM igs_as_su_setatmpt susa,
23 igs_en_unit_set us,
24 igs_en_unit_set_cat usc
25 WHERE p_person_id = susa.person_id
26 AND p_course_cd = susa.course_cd
27 AND (igs_en_gen_015.get_effective_census_date (
28 NULL,
29 NULL,
30 p_teach_cal_type,
31 p_teach_ci_sequence_number
32 ) BETWEEN susa.selection_dt
33 AND NVL (susa.rqrmnts_complete_dt,
34 NVL (susa.end_dt,
35 fnd_date.canonical_to_date ('9999/12/31'))))
36 AND susa.unit_set_cd = us.unit_set_cd
37 AND us.unit_set_cat = usc.unit_set_cat
38 AND usc.s_unit_set_cat = 'PRENRL_YR'
39 ORDER BY susa.selection_dt DESC;
40 --
41 BEGIN
42 --
43 OPEN c_susa;
44 FETCH c_susa INTO v_unit_set_cd;
45 --
46 IF c_susa%FOUND THEN
47 CLOSE c_susa;
48 RETURN v_unit_set_cd;
49 ELSE
50 CLOSE c_susa;
51 RETURN NULL;
52 END IF;
53 --
54 END get_sua_yop;
55 --
56 -- Validate the Assessment Item Outcome records before inserting them into
57 -- base table and call table handlers
58 --
59 PROCEDURE assessment_item_grade_process (
60 errbuf OUT NOCOPY VARCHAR2,
61 retcode OUT NOCOPY NUMBER,
62 p_user_id IN NUMBER,
63 p_batch_datetime IN VARCHAR2,
64 p_grade_creation_method_type IN VARCHAR2,
65 p_delete_rows IN VARCHAR2 DEFAULT 'Y'
66 ) IS
67 BEGIN
68 DECLARE
69 p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
70 --
71 -- Get the uoo_id based on the pk columns for Unit Section
72 --
73 CURSOR cur_uoo_id (
74 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
75 cp_version_number igs_en_su_attempt.version_number%TYPE,
76 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
77 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
78 cp_unit_class igs_en_su_attempt.unit_class%TYPE,
79 cp_location_cd igs_en_su_attempt.location_cd%TYPE
80 ) IS
81 SELECT uoo_id
82 FROM igs_ps_unit_ofr_opt
83 WHERE unit_cd = cp_unit_cd
84 --AND version_number = cp_version_number
85 AND cal_type = cp_cal_type
86 AND ci_sequence_number = cp_ci_sequence_number
87 AND unit_class = cp_unit_class
88 AND location_cd = cp_location_cd;
89 --
90 rec_uoo_id cur_uoo_id%ROWTYPE;
91 --
92 -- Get the Assessment Item Outcomes from the Assessment Item Outcome
93 -- Interface table. The data from this interface table is the data that is
94 -- being uploaded currently and once the data is uploaded it is deleted
95 -- from the Interface table.
96 --
97 CURSOR c_upload_outcome_ai IS
98 SELECT user_id,
99 batch_date,
100 decode(person_number,'-',null,person_number) person_number,
101 decode(anonymous_id,'-',null,anonymous_id) anonymous_id,
102 course_cd,
103 unit_cd,
104 cal_type,
105 ci_sequence_number,
106 alternate_code,
107 ass_id,
108 assessment_type,
109 reference,
110 grade,
111 outcome_comment_code,
112 mark,
113 error_code,
114 ROWID,
115 unit_class,
116 location_cd,
117 override_due_dt,
118 penalty_applied_flag,
119 waived_flag,
120 submitted_date,
121 uoo_id
122 FROM igs_as_aio_interface
123 WHERE user_id = p_user_id
124 AND trunc(batch_date) = trunc(p_batch_date)
125 AND ass_id IS NOT NULL;
126 --
127 -- Get the Student Unit Attempt Assessment Item details
128 --
129 CURSOR cur_suaai (
130 cp_person_id NUMBER,
131 cp_course_cd VARCHAR2,
132 cp_uoo_id NUMBER,
133 cp_ass_id NUMBER,
134 cp_reference VARCHAR2
135 ) IS
136 SELECT suaai.*, suaai.rowid
137 FROM igs_as_su_atmpt_itm suaai
138 WHERE suaai.person_id = cp_person_id
139 AND suaai.course_cd = cp_course_cd
140 AND suaai.uoo_id = cp_uoo_id
141 AND suaai.ass_id = cp_ass_id
142 AND igs_as_gen_003.assp_get_ai_ref (suaai.unit_section_ass_item_id, suaai.unit_ass_item_id) = cp_reference
143 AND suaai.logical_delete_dt IS NULL;
144 --
145 rec_suaai cur_suaai%ROWTYPE;
146 --
147 -- Declare local variables
148 -- insert flag will update when any of the record got Abort message
149 -- not load flag will update when any of the record got Do Not Load Record message.
150 --
151 v_person_id NUMBER (15);
152 v_cal_type VARCHAR2 (10);
153 v_ci_sequence_number NUMBER (6);
154 v_ass_id NUMBER (10);
155 v_grade VARCHAR2 (5);
156 v_request_id NUMBER;
157 v_uoo_id NUMBER (7);
158 v_error_code VARCHAR2 (30);
159 v_ret_val BOOLEAN;
160 v_insert_flag VARCHAR2 (1);
161 v_insert_batch VARCHAR2 (1);
162 v_load_flag VARCHAR2 (1);
163 v_grading_schema_cd VARCHAR2 (10);
164 v_gs_version_number NUMBER (3);
165 v_rowid VARCHAR2 (25);
166 v_outcome_dt DATE DEFAULT SYSDATE;
167 v_creation_dt DATE DEFAULT SYSDATE;
168 --
169 l_validuser varchar2(1);
170 BEGIN
171
172 --
173 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
174 --
175 -- FND_LOGGING
176 --
177 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
178 fnd_log.string ( fnd_log.level_procedure,
179 'igs.plsql.igs_as_adi_upld_aio_pkg.assessment_item_grade_process.begin',
180 'Params: p_user_id => '||p_user_id|| ';' ||
181 ' p_batch_datetime => '||p_batch_datetime|| ';' ||
182 ' p_grade_creation_method_type => '||p_grade_creation_method_type|| ';' ||
183 ' p_delete_rows => '||p_delete_rows|| ';'
184 );
185 END IF;
186
187 FOR v_aio_upld IN c_upload_outcome_ai LOOP
188 --
189 -- Initialize variables here.
190 --
191 v_cal_type := v_aio_upld.cal_type;
192 v_ci_sequence_number := v_aio_upld.ci_sequence_number;
193 v_ass_id := v_aio_upld.ass_id;
194 v_insert_flag := 'Y';
195 v_insert_batch := 'Y';
196 v_load_flag := 'Y';
197 v_grade := v_aio_upld.grade;
198 -- Check if the user is authorised to upload data .
199 -- Only admin and faculty for the unitsection can upload data to OSS.
200 l_validuser:= isvaliduser (
201 v_aio_upld.user_id ,
202 v_aio_upld.uoo_id
203 );
204 IF (l_validuser <> 'Y') THEN
205 UPDATE igs_as_aio_interface
206 SET error_code = 'IGS_EN_PERSON_NO_RESP'
207 WHERE ROWID = v_aio_upld.ROWID;
208 ELSE
209
210 --
211 -- FND_LOGGING
212 --
213 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
214 fnd_log.string (fnd_log.level_statement,
215 'igs.plsql.IGS_AS_ADI_UPLD_AIO_PKG.assessment_item_grade_process.c_upload_outcome_ai',
216 'v_ugi_rec.person_number => '||v_aio_upld.person_number||';'||
217 'v_ugi_rec.anonymous_id => '||v_aio_upld.anonymous_id||';'||
218 'v_cal_type => '||v_cal_type||';'||
219 'v_ci_sequence_number =>'||v_ci_sequence_number||';'||
220 'v_grade =>'||v_grade||';'
221 );
222 END IF;
223
224 --
225 -- Call routine to upload for validate the particular row
226 --
227 igs_as_aio_val_upld (
228 v_aio_upld.person_number,
229 v_person_id,
230 v_aio_upld.anonymous_id,
231 v_aio_upld.course_cd,
232 v_aio_upld.unit_cd,
233 v_cal_type,
234 v_ci_sequence_number,
235 v_aio_upld.alternate_code,
236 v_ass_id,
237 v_aio_upld.assessment_type,
238 v_aio_upld.REFERENCE,
239 v_grading_schema_cd,
240 v_gs_version_number,
241 v_grade,
242 v_aio_upld.mark,
243 v_error_code,
244 v_ret_val,
245 v_insert_flag,
246 v_load_flag,
247 v_aio_upld.unit_class,
248 v_aio_upld.location_cd,
249 v_aio_upld.override_due_dt,
250 v_aio_upld.penalty_applied_flag,
251 v_aio_upld.waived_flag,
252 v_aio_upld.submitted_date,
253 v_aio_upld.uoo_id
254 );
255 --
256 IF v_insert_flag = 'N' THEN
257 v_insert_batch := 'N';
258 END IF;
259 --
260 UPDATE igs_as_aio_interface
261 SET error_code = v_error_code,
262 grade = v_grade
263 WHERE ROWID = v_aio_upld.ROWID;
264 END IF;
265 END LOOP;
266 --
267 COMMIT; -- commit the records into interface table.
268 --
269 /* Need to call table handlers only if any of the records does not have setup option abort.
270 get the value that is there any record into interface table with Abort Status */
271 --
272 IF v_insert_batch = 'Y' THEN
273 FOR v_aio_upld IN c_upload_outcome_ai LOOP
274 IF (v_aio_upld.ERROR_CODE IS NULL OR
275 (v_aio_upld.ERROR_CODE IS NOT NULL AND
276 v_aio_upld.ERROR_CODE <> 'IGS_EN_PERSON_NO_RESP')) THEN
277 --
278 -- Initialize variables here.
279 --
280 v_cal_type := v_aio_upld.cal_type;
281 v_ci_sequence_number := v_aio_upld.ci_sequence_number;
282 v_ass_id := v_aio_upld.ass_id;
283 v_grade := v_aio_upld.grade;
284 --
285 IF (UPPER (NVL(v_aio_upld.waived_flag, 'N')) NOT IN('N', 'Y')) THEN
286 v_aio_upld.waived_flag := NULL;
287 ELSE
288 v_aio_upld.waived_flag := UPPER(v_aio_upld.waived_flag);
289 END IF;
290 --
291 IF (UPPER (NVL(v_aio_upld.penalty_applied_flag, 'N')) NOT IN('N', 'Y')) THEN
292 v_aio_upld.penalty_applied_flag := NULL;
293 ELSE
294 v_aio_upld.penalty_applied_flag := (v_aio_upld.penalty_applied_flag) ;
295 END IF;
296
297 --
298 -- FND_LOGGING
299 --
300 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
301 fnd_log.string (fnd_log.level_statement,
302 'igs.plsql.IGS_AS_ADI_UPLD_AIO_PKG.assessment_item_grade_process.c_upload_outcome_ai',
303 'v_ugi_rec.person_number => '||v_aio_upld.person_number||';'||
304 'v_ugi_rec.anonymous_id => '||v_aio_upld.anonymous_id||';'||
305 'v_cal_type => '||v_cal_type||';'||
306 'v_ci_sequence_number =>'||v_ci_sequence_number||';'||
307 'v_grade =>'||v_grade||';'
308 );
309 END IF;
310
311 --
312 -- Call the same procedure again for update mode, earlier we call this procedure for validate mode.
313 --
314 igs_as_aio_val_upld (
315 v_aio_upld.person_number,
316 v_person_id,
317 v_aio_upld.anonymous_id,
318 v_aio_upld.course_cd,
319 v_aio_upld.unit_cd,
320 v_cal_type,
321 v_ci_sequence_number,
322 v_aio_upld.alternate_code,
323 v_ass_id,
324 v_aio_upld.assessment_type,
325 v_aio_upld.reference,
326 v_grading_schema_cd,
327 v_gs_version_number,
328 v_grade,
329 v_aio_upld.mark,
330 v_error_code,
331 v_ret_val,
332 v_insert_flag,
333 v_load_flag,
334 v_aio_upld.unit_class,
335 v_aio_upld.location_cd,
336 v_aio_upld.override_due_dt,
337 v_aio_upld.penalty_applied_flag,
338 v_aio_upld.waived_flag,
339 v_aio_upld.submitted_date,
340 v_aio_upld.uoo_id
341 );
342 --
343 -- start process only if load flag is 'Y'
344 --
345 IF v_load_flag = 'Y' THEN
346 -- BUG # 2735673 UOO_ID is added to the interface table. So no need to quary for UOO_ID from the tables.
347 -- Still keeping this cursor if user does not provide the uoo_id in the interface table
348 -- and wants to upload
349 IF v_aio_upld.uoo_id IS NULL THEN
350 OPEN cur_uoo_id (
351 v_aio_upld.unit_cd,
352 v_gs_version_number,
353 v_cal_type,
354 v_ci_sequence_number,
355 v_aio_upld.unit_class,
356 v_aio_upld.location_cd
357 );
358 FETCH cur_uoo_id INTO rec_uoo_id;
359 CLOSE cur_uoo_id;
360 v_aio_upld.uoo_id := rec_uoo_id.uoo_id;
361 END IF;
362 OPEN cur_suaai (
363 v_person_id,
364 v_aio_upld.course_cd,
365 v_aio_upld.uoo_id,
366 v_ass_id,
367 v_aio_upld.reference
368 );
369 FETCH cur_suaai INTO rec_suaai; --l_unit_section_ass_item_id, l_unit_ass_item_id, v_rowid;
370 --
371 IF cur_suaai%FOUND THEN -- that means record is already exist into base table
372 CLOSE cur_suaai;
373 BEGIN
374 igs_as_su_atmpt_itm_pkg.update_row (
375 x_rowid => rec_suaai.rowid,
376 x_person_id => rec_suaai.person_id,
377 x_course_cd => rec_suaai.course_cd,
378 x_unit_cd => rec_suaai.unit_cd,
379 x_cal_type => rec_suaai.cal_type,
380 x_ci_sequence_number => rec_suaai.ci_sequence_number,
381 x_ass_id => rec_suaai.ass_id,
382 x_creation_dt => rec_suaai.creation_dt,
383 x_attempt_number => rec_suaai.attempt_number,
384 x_outcome_dt => SYSDATE,
385 x_override_due_dt => v_aio_upld.override_due_dt,
386 x_tracking_id => rec_suaai.tracking_id,
387 x_logical_delete_dt => rec_suaai.logical_delete_dt,
388 x_s_default_ind => rec_suaai.s_default_ind,
389 x_ass_pattern_id => rec_suaai.ass_pattern_id,
390 x_mode => 'S',
391 x_grading_schema_cd => rec_suaai.grading_schema_cd,
392 x_gs_version_number => rec_suaai.gs_version_number,
393 x_grade => v_aio_upld.grade,
394 x_outcome_comment_code => 'UPLOAD',
395 x_mark => v_aio_upld.mark,
396 x_attribute_category => rec_suaai.attribute_category,
397 x_attribute1 => rec_suaai.attribute1,
398 x_attribute2 => rec_suaai.attribute2,
399 x_attribute3 => rec_suaai.attribute3,
400 x_attribute4 => rec_suaai.attribute4,
401 x_attribute5 => rec_suaai.attribute5,
402 x_attribute6 => rec_suaai.attribute6,
403 x_attribute7 => rec_suaai.attribute7,
404 x_attribute8 => rec_suaai.attribute8,
405 x_attribute9 => rec_suaai.attribute9,
406 x_attribute10 => rec_suaai.attribute10,
407 x_attribute11 => rec_suaai.attribute11,
408 x_attribute12 => rec_suaai.attribute12,
409 x_attribute13 => rec_suaai.attribute13,
410 x_attribute14 => rec_suaai.attribute14,
411 x_attribute15 => rec_suaai.attribute15,
412 x_attribute16 => rec_suaai.attribute16,
413 x_attribute17 => rec_suaai.attribute17,
414 x_attribute18 => rec_suaai.attribute18,
415 x_attribute19 => rec_suaai.attribute19,
416 x_attribute20 => rec_suaai.attribute20,
417 x_uoo_id => rec_suaai.uoo_id,
418 x_unit_section_ass_item_id => rec_suaai.unit_section_ass_item_id,
419 x_unit_ass_item_id => rec_suaai.unit_ass_item_id,
420 x_sua_ass_item_group_id => rec_suaai.sua_ass_item_group_id,
421 x_midterm_mandatory_type_code => rec_suaai.midterm_mandatory_type_code,
422 x_midterm_weight_qty => rec_suaai.midterm_weight_qty,
423 x_final_mandatory_type_code => rec_suaai.final_mandatory_type_code,
424 x_final_weight_qty => rec_suaai.final_weight_qty,
425 x_submitted_date => v_aio_upld.submitted_date,
426 x_waived_flag => v_aio_upld.waived_flag,
427 x_penalty_applied_flag => v_aio_upld.penalty_applied_flag
428 );
429 EXCEPTION
430 WHEN OTHERS THEN
431 DECLARE
432 app_short_name VARCHAR2 (10);
433 message_name VARCHAR2 (100);
434 BEGIN
435 errbuf := NULL;
436 fnd_message.parse_encoded (fnd_message.get_encoded, app_short_name, message_name);
437 retcode := 2;
438 errbuf := message_name;
439 IF (errbuf IS NOT NULL) THEN
440 UPDATE igs_as_aio_interface
441 SET ERROR_CODE = errbuf
442 WHERE ROWID = v_aio_upld.ROWID;
443 END IF;
444 END;
448 END IF; -- rowid is not null
445 END;
446 ELSE
447 CLOSE cur_suaai;
449 END IF; -- the record is good to load
450 END IF;
451 END LOOP;
452 END IF; --insert_batch is 'Y' or not
453 /* Call Reports for generating error report with parameter and after that delete the records from Report only
454 by calling after report trigger.
455 ERR_REPORT (p_user_id,p_batch_date,p_delete_rows,p_header_message)*/
456 /* Extracting WebADI from Concurrent Program LOV */
457 IF p_grade_creation_method_type <> 'WEBADI' THEN
458 v_request_id :=
459 fnd_request.submit_request ('IGS', 'IGSASS25', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
460 END IF;
461 IF v_request_id = 0 THEN
462 RAISE fnd_api.g_exc_unexpected_error;
463 END IF;
464
465 --
466 -- FND_LOGGING
467 --
468 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
469 fnd_log.string ( fnd_log.level_procedure,
470 'igs.plsql.IGS_AS_ADI_UPLD_AIO_PKG.assessment_item_grade_process.end',
471 'Exiting IGS_AS_ADI_UPLD_AIO_PKG.assessment_item_grade_process'
472 );
473 END IF;
474
475 COMMIT;
476 END;
477 END assessment_item_grade_process;
478 --
479 -- Validate the records before inserting into base table and call the table handlers
480 -- This is a wrapper API to the Grade Unit and Grade Assessment Item API's
481 --
482 PROCEDURE assmnt_item_grade_unit_process (
483 errbuf OUT NOCOPY VARCHAR2,
484 retcode OUT NOCOPY NUMBER,
485 p_user_id IN NUMBER,
486 p_batch_datetime IN VARCHAR2,
487 p_grade_creation_method_type IN VARCHAR2,
488 p_delete_rows IN VARCHAR2
489 ) IS
490 p_batch_date DATE := to_date(p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
491 -- Cursors to copy back the error code for UG_INTERFACE to AIO INTERFACD
492 CURSOR cur_ug_err (cp_user_id number, cp_batch_date DATE, cp_person_number VARCHAR2,
493 CP_ANONYMOUS_ID VARCHAR2, cp_uoo_id NUMBER)
494 IS SELECT ugi.error_code from IGS_AS_UG_INTERFACE UGI
495 WHERE ( (PERSON_number = cp_person_number OR ANONYMOUS_ID = CP_ANONYMOUS_ID)
496 AND user_id = cp_user_id and trunc(batch_date) = trunc(cp_batch_date)
497 AND uoo_id = cp_uoo_id and ERROR_CODE IS NOT NULL
498 );
499 CURSOR CUR_AIO_NO_ERR IS SELECT aio.* from IGS_AS_AIO_INTERFACE AIO
500 WHERE user_id = p_user_id and trunc(batch_date) = trunc(p_batch_date)
501 AND ASS_ID IS NULL
502 AND ERROR_CODE IS NULL
503 FOR UPDATE OF ERROR_CODE;
504 v_error_code_toaio VARCHAR2(30);
505 BEGIN
506 assessment_item_grade_process (
507 errbuf,
508 retcode,
509 p_user_id,
510 p_batch_datetime,
511 p_grade_creation_method_type,
512 p_delete_rows
513 );
514 igs_as_adi_upld_ug_pkg.grading_period_grade_process (
515 errbuf,
516 retcode,
517 p_user_id,
518 p_batch_datetime,
519 p_grade_creation_method_type,
520 p_delete_rows
521 );
522 --- Bug# 2735673 Since the SQL Statement to identify the errored rows accept only
523 -- one table as the String_Value in BNE_PARAM_LIST_ITEMS, We need to copy the eror code
524 -- From UG INTERFACE TABLE TO AIO INTERFACE table show that rows can be appropriately identified in the spread sheet
525 FOR AIO_NO_ERR IN CUR_AIO_NO_ERR LOOP
526 -- Initialize the error code to null so that the existing one is not used to replace the next one
527 v_error_code_toaio := NULL;
528 OPEN cur_ug_err (AIO_NO_ERR.USER_ID, AIO_NO_ERR.batch_date, AIO_NO_ERR.person_number,AIO_NO_ERR.anonymous_id, AIO_NO_ERR.uoo_id);
529 FETCH cur_ug_err into v_error_code_toaio;
530 CLOSE cur_ug_err;
531 UPDATE IGS_AS_AIO_INTERFACE SET ERROR_CODE = v_error_code_toaio WHERE CURRENT OF CUR_AIO_NO_ERR;
532 END LOOP;
533 END assmnt_item_grade_unit_process;
534 --
535 -- Validate single Grading Period record from the interface table before
536 -- uploading it. This validation is called from the interface table import
537 -- routine, and also the ADI pre-validation functionality.
538 --
539 PROCEDURE igs_as_aio_val_upld (
540 p_person_number IN VARCHAR2,
541 p_person_id OUT NOCOPY NUMBER,
542 p_anonymous_id IN VARCHAR2,
543 p_course_cd IN VARCHAR2,
544 p_unit_cd IN VARCHAR2,
545 p_cal_type IN OUT NOCOPY VARCHAR2,
546 p_ci_sequence_number IN OUT NOCOPY NUMBER,
547 p_alternate_code IN VARCHAR2,
548 p_ass_id IN OUT NOCOPY NUMBER,
549 p_assessment_type IN VARCHAR2,
550 p_reference IN VARCHAR2,
551 p_grading_schema_cd OUT NOCOPY VARCHAR2,
552 p_gs_version_number OUT NOCOPY NUMBER,
553 p_grade IN OUT NOCOPY VARCHAR2,
554 p_mark IN NUMBER,
555 p_error_code OUT NOCOPY VARCHAR2,
556 p_ret_val OUT NOCOPY BOOLEAN,
557 p_insert_flag OUT NOCOPY VARCHAR2,
558 p_load_flag OUT NOCOPY VARCHAR2,
562 p_penalty_applied_flag IN VARCHAR2 DEFAULT NULL,
559 p_unit_class IN VARCHAR2 DEFAULT NULL,
560 p_location_cd IN VARCHAR2 DEFAULT NULL,
561 p_override_due_dt IN DATE DEFAULT NULL,
563 p_waived_flag IN VARCHAR2 DEFAULT NULL,
564 p_submitted_date IN DATE DEFAULT NULL,
565 p_uoo_id IN NUMBER
566 ) IS
567 --
568 v_no_program_status VARCHAR2 (30);
569 v_unit_enrolled_status VARCHAR2 (30);
570 v_uoo_id NUMBER (7);
571 v_upld_person_no_exist VARCHAR2 (1);
572 v_upld_crs_not_enrolled VARCHAR2 (1);
573 v_upld_unit_discont VARCHAR2 (1);
574 v_upld_unit_not_enrolled VARCHAR2 (1);
575 v_finalized_outcome_ind VARCHAR2 (1);
576 v_lower_mark_range NUMBER (3);
577 v_upper_mark_range NUMBER (3);
578 v_grading_schema_cd VARCHAR2 (10);
579 v_gs_version_number NUMBER (3);
580 v_upld_grade_invalid VARCHAR2 (1);
581 v_invalid_allow VARCHAR2 (1);
582 v_combination_invalid VARCHAR2 (3);
583 v_upld_mark_grade_invalid VARCHAR2 (1);
584 v_valid_record VARCHAR2 (1);
585 v_assessment_item_exist VARCHAR2 (1);
586 v_upld_asmnt_item_not_exist VARCHAR2 (1);
587 v_outcome_dt DATE;
588 v_upld_asmnt_grade_exist VARCHAR2 (1);
589 v_mark_entry_mandatory VARCHAR2 (1);
590 --
591 --
592 --
593 CURSOR c_alternate_code IS
594 SELECT ci.cal_type,
595 ci.sequence_number
596 FROM igs_ca_inst_all ci,
597 igs_ca_type cat,
598 igs_ca_stat cs
599 WHERE (ci.alternate_code = p_alternate_code
600 OR p_alternate_code IS NULL
601 )
602 AND ((ci.cal_type = p_cal_type
603 AND ci.sequence_number = p_ci_sequence_number
604 )
605 OR p_cal_type IS NULL
606 )
607 AND cat.cal_type = ci.cal_type
608 AND cat.s_cal_cat = 'TEACHING'
609 AND cs.cal_status = ci.cal_status
610 AND cs.s_cal_status = 'ACTIVE';
611 --
612 --
613 --
614 CURSOR c_person_id IS
615 SELECT party_id
616 FROM hz_parties hzp
617 WHERE hzp.party_number = p_person_number;
618 --
619 --
620 --
621 CURSOR c_assessment_id (cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
622 SELECT ai.ass_id,
623 gs.grading_schema_cd,
624 gs.version_number
625 FROM igs_as_assessmnt_itm ai,
626 igs_as_grd_schema gs
627 WHERE (ai.assessment_type = p_assessment_type
628 OR p_assessment_type IS NULL
629 )
630 AND (ai.ass_id = p_ass_id
631 OR p_ass_id IS NULL
632 )
633 AND (EXISTS ( SELECT 'X'
634 FROM igs_ps_unitass_item uooai,
635 igs_ps_unit_ofr_opt uoo
636 WHERE uoo.uoo_id = cp_uoo_id
637 AND uooai.uoo_id = uoo.uoo_id
638 AND uooai.ass_id = ai.ass_id
639 AND uooai.logical_delete_dt IS NULL
640 AND (uooai.REFERENCE = p_reference
641 OR p_reference IS NULL
642 )
643 AND uooai.grading_schema_cd = gs.grading_schema_cd
644 AND uooai.gs_version_number = gs.version_number)
645 OR EXISTS ( SELECT 'X'
646 FROM igs_as_unitass_item uoai,
647 igs_ps_unit_ofr_opt uoo
648 WHERE uoo.uoo_id = cp_uoo_id
649 AND uoai.unit_cd = uoo.unit_cd
650 AND uoai.version_number = uoo.version_number
651 AND uoai.cal_type = uoo.cal_type
652 AND uoai.ci_sequence_number = uoo.ci_sequence_number
653 AND uoai.ass_id = ai.ass_id
654 AND uoai.logical_delete_dt IS NULL
655 AND (uoai.REFERENCE = p_reference
656 OR p_reference IS NULL
657 )
658 AND uoai.grading_schema_cd = gs.grading_schema_cd
659 AND uoai.gs_version_number = gs.version_number
660 AND NOT EXISTS ( SELECT 'X'
661 FROM igs_ps_unitass_item uooai
662 WHERE uooai.uoo_id = uoo.uoo_id
663 AND uooai.logical_delete_dt IS NULL
664 AND uooai.ass_id = ai.ass_id)));
665 --
666 --
667 --
668 CURSOR c_uoo_id (
669 cp_cal_type igs_en_su_attempt_all.cal_type%TYPE,
670 cp_ci_sequence_number igs_en_su_attempt_all.ci_sequence_number%TYPE,
671 cp_person_id hz_parties.party_id%TYPE
672 ) IS
673 SELECT uoo_id
674 FROM igs_en_su_attempt_all
675 WHERE unit_cd = p_unit_cd
679 AND course_cd = p_course_cd
676 AND cal_type = cp_cal_type
677 AND ci_sequence_number = cp_ci_sequence_number
678 AND person_id = cp_person_id
680 AND unit_class = p_unit_class
681 AND location_cd = p_location_cd;
682 --
683 --
684 --
685 CURSOR c_course_attempt_status (cp_person_id hz_parties.party_id%TYPE) IS
686 SELECT course_attempt_status
687 FROM igs_en_stdnt_ps_att_all
688 WHERE person_id = cp_person_id
689 AND course_cd = p_course_cd;
690 --
691 --
692 --
693 CURSOR c_unit_enroll_status (
694 cp_person_id igs_en_su_attempt_all.person_id%TYPE,
695 cp_course_cd igs_en_su_attempt_all.course_cd%TYPE,
696 cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE
697 ) IS
698 SELECT unit_attempt_status
699 FROM igs_en_su_attempt_all
700 WHERE person_id = cp_person_id
701 AND course_cd = cp_course_cd
702 AND uoo_id = cp_uoo_id;
703 --
704 --
705 --
706 CURSOR c_ass_item_exist (
707 cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE,
708 cp_ass_id igs_as_assessmnt_itm.ass_id%TYPE,
709 cp_person_id hz_parties.party_id%TYPE,
710 cp_reference VARCHAR2
711 ) IS
712 SELECT 'X'
713 FROM igs_as_su_atmpt_itm sai
714 WHERE sai.person_id = cp_person_id
715 AND sai.course_cd = p_course_cd
716 AND sai.uoo_id = cp_uoo_id
717 AND sai.ass_id = cp_ass_id
718 AND igs_as_gen_003.assp_get_ai_ref (sai.unit_section_ass_item_id, sai.unit_ass_item_id) = cp_reference
719 AND sai.logical_delete_dt IS NULL;
720 --
721 --
722 --
723 CURSOR c_ass_item_grade_exist (
724 cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE,
725 cp_person_id hz_parties.party_id%TYPE,
726 cp_ass_id igs_as_assessmnt_itm.ass_id%TYPE,
727 cp_reference VARCHAR2
728 ) IS
729 SELECT sai.outcome_dt outcome_dt
730 FROM igs_as_su_atmpt_itm sai
731 WHERE sai.person_id = cp_person_id
732 AND sai.course_cd = p_course_cd
733 AND sai.uoo_id = cp_uoo_id
734 AND sai.ass_id = cp_ass_id
735 AND igs_as_gen_003.assp_get_ai_ref (sai.unit_section_ass_item_id, sai.unit_ass_item_id) = cp_reference
736 AND sai.logical_delete_dt IS NULL;
737 --
738 --
739 --
740 CURSOR c_grade_invalid (
741 cp_grading_schema_cd igs_as_grd_schema.grading_schema_cd%TYPE,
742 cp_gs_version_number igs_as_grd_schema.version_number%TYPE
743 ) IS
744 SELECT gsg.lower_mark_range,
745 gsg.upper_mark_range
746 FROM igs_as_grd_sch_grade gsg
747 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
748 AND gsg.version_number = cp_gs_version_number
749 AND system_only_ind = 'N'
750 AND gsg.grade = p_grade;
751 --
752 -- Get the Mark/Grade Entry Configuration Setup
753 --
754 CURSOR cur_ai_mark_grade_conf IS
755 SELECT *
756 FROM igs_as_entry_conf
757 WHERE s_control_num = 1;
758 --
759 rec_ai_mark_grade_conf cur_ai_mark_grade_conf%ROWTYPE;
760 --
761 -- Derive the Grade from the Grading Schema and the entered mark
762 --
763 CURSOR cur_gsg_derive (
764 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
765 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE
766 ) IS
767 SELECT gsg.grade
768 FROM igs_as_grd_sch_grade gsg
769 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
770 AND gsg.version_number = cp_gs_version_number
771 AND gsg.system_only_ind = 'N'
772 AND p_mark BETWEEN lower_mark_range AND upper_mark_range + 0.999;
773 --
774 rec_gsg_derive cur_gsg_derive%ROWTYPE;
775 --
776 BEGIN
777 --
778 p_insert_flag := 'Y';
779 p_load_flag := 'Y';
780 --
781 -- Get the Alternate Code
782 --
783 BEGIN
784 IF p_cal_type IS NULL
785 AND p_ci_sequence_number IS NULL
786 AND p_alternate_code IS NULL THEN
787 p_error_code := 'IGS_AS_MISSING_ALTNTE_CODE';
788 p_ret_val := FALSE;
789 p_insert_flag := 'N';
790 RETURN;
791 ELSIF p_cal_type IS NULL
792 AND p_ci_sequence_number IS NULL
793 AND p_alternate_code IS NOT NULL THEN
794 OPEN c_alternate_code;
795 FETCH c_alternate_code INTO p_cal_type,
796 p_ci_sequence_number;
797 IF c_alternate_code%NOTFOUND THEN
798 CLOSE c_alternate_code;
799 p_error_code := 'IGS_AS_MISSING_ALTNTE_CODE';
800 p_ret_val := FALSE;
801 p_insert_flag := 'N';
802 RETURN;
803 ELSE
804 CLOSE c_alternate_code;
805 END IF;
806 END IF;
807 EXCEPTION
808 WHEN NO_DATA_FOUND THEN
809 p_insert_flag := 'N';
810 END;
811 --
815 FETCH cur_ai_mark_grade_conf INTO rec_ai_mark_grade_conf;
812 -- Get the Assessment Item Mark/Grade Entry Configuration
813 --
814 OPEN cur_ai_mark_grade_conf;
816 CLOSE cur_ai_mark_grade_conf;
817 --
818 -- Get Person ID AND Person Does Not Exist
819 --
820 BEGIN
821 IF p_person_number IS NULL
822 AND p_anonymous_id IS NULL THEN
823 SELECT upld_person_no_exist
824 INTO v_upld_person_no_exist
825 FROM igs_as_entry_conf;
826 IF v_upld_person_no_exist = 'D' THEN
827 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
828 p_ret_val := FALSE;
829 p_load_flag := 'N';
830 RETURN;
831 ELSIF v_upld_person_no_exist = 'A' THEN
832 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
833 p_ret_val := FALSE;
834 p_insert_flag := 'N';
835 RETURN;
836 END IF;
837 ELSIF p_person_number IS NOT NULL
838 AND p_anonymous_id IS NOT NULL THEN
839 p_error_code := 'IGS_AS_ASD_PER_ANON_BOTH_EXIST';
840 p_ret_val := FALSE;
841 p_load_flag := 'N';
842 RETURN;
843 ELSIF p_person_number IS NULL
844 AND p_anonymous_id IS NOT NULL THEN
845 -- call function to get person id based on anonymous number
846 p_person_id := igs_as_anon_grd_pkg.get_person_id (
847 p_anonymous_id,
848 p_cal_type,
849 p_ci_sequence_number
850 );
851 ELSIF p_person_number IS NOT NULL
852 AND p_anonymous_id IS NULL THEN
853 OPEN c_person_id;
854 FETCH c_person_id INTO p_person_id;
855 IF c_person_id%NOTFOUND THEN
856 CLOSE c_person_id;
857 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
858 p_ret_val := FALSE;
859 p_load_flag := 'N';
860 RETURN;
861 ELSE
862 CLOSE c_person_id;
863 END IF;
864 --
865 -- If Person does not exist then show error into exception report based
866 -- on option selected into configuration setup form.
867 --
868 IF p_person_id IS NOT NULL THEN
869 IF v_upld_person_no_exist = 'D' THEN
870 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
871 p_ret_val := FALSE;
872 p_load_flag := 'N';
873 RETURN;
874 ELSIF v_upld_person_no_exist = 'A' THEN
875 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
876 p_ret_val := FALSE;
877 p_insert_flag := 'N';
878 RETURN;
879 END IF;
880 END IF;
881 END IF;
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 p_insert_flag := 'N';
885 END;
886 --
887 -- Get Assessment ID
888 --
889 /* If user enters new records, they are going to key in assessment id, assessment type and reference.
890 If users download the file then they will be having all the information. That means if assessment id
891 is blank and assessment type or reference is blank give error or get the value for assessment id
892 based on assessment type and reference. FIRST GET UOO_ID
893 */
894 -- As per bug# 2735673 The uoo_id column has been added to the interface table. UOO_ID is not
895 -- required to be derived from the tables based on PK of IGS_PS_UNIT_OFR_OPT_ALL.
896 --Still Keeping this query in case user has not entered the UOO_ID in the spreadsheet).
897 IF p_uoo_id IS NULL THEN
898 OPEN c_uoo_id (p_cal_type, p_ci_sequence_number, p_person_id);
899 FETCH c_uoo_id INTO v_uoo_id;
900 IF c_uoo_id%NOTFOUND THEN
901 -- 'student unit attempt not exist
902 p_load_flag := 'N';
903 END IF;
904 CLOSE c_uoo_id;
905 ELSE
906 v_uoo_id := p_uoo_id;
907 END IF; /* End p_uoo_id is null */
908 IF p_ass_id IS NULL
909 AND (p_assessment_type IS NULL
910 OR p_reference IS NULL
911 ) THEN
912 p_error_code := 'IGS_AS_ASD_AI_INFO_MISSING';
913 p_ret_val := FALSE;
914 p_load_flag := 'N';
915 RETURN;
916 ELSE
917 OPEN c_assessment_id (v_uoo_id);
918 FETCH c_assessment_id INTO p_ass_id,
919 p_grading_schema_cd,
920 p_gs_version_number;
921 IF c_assessment_id%NOTFOUND THEN
922 p_error_code := 'IGS_AS_ASD_AI_NOT_FOUND';
923 p_ret_val := FALSE;
924 p_load_flag := 'N';
925 RETURN;
926 -- Could not find unique assessment item for particular assessment type and reference combination)
927 END IF; --ass_id is null
928 CLOSE c_assessment_id;
929 END IF; -- ass_id , assessment type , reference is null
930 -- No Program Attempts
931 OPEN c_course_attempt_status (p_person_id);
932 FETCH c_course_attempt_status INTO v_no_program_status;
933 /* If student dose not enrolled into course then act according to configuration setup. Available options are
934 1) Abort File 2) Do not Load Record. Course attempt other then ?Enrolled? consider as no program attempt.
935 */
939 INTO v_upld_crs_not_enrolled
936 IF c_course_attempt_status%NOTFOUND
937 OR v_no_program_status NOT IN ('ENROLLED', 'INACTIVE') THEN
938 SELECT upld_crs_not_enrolled
940 FROM igs_as_entry_conf;
941 IF v_upld_crs_not_enrolled = 'D' THEN
942 p_error_code := 'IGS_AS_ASD_COURSE_NOT_ENROLLED';
943 p_ret_val := FALSE;
944 p_load_flag := 'N';
945 RETURN;
946 ELSIF v_upld_crs_not_enrolled = 'A' THEN
947 p_error_code := 'IGS_AS_ASA_COURSE_NOT_ENROLLED';
948 p_ret_val := FALSE;
949 p_insert_flag := 'N';
950 RETURN;
951 END IF;
952 END IF;
953 CLOSE c_course_attempt_status;
954 -- Unit Not Enrolled or Unit Discontinued -
955 OPEN c_unit_enroll_status (p_person_id, p_course_cd, v_uoo_id);
956 FETCH c_unit_enroll_status INTO v_unit_enrolled_status;
957 IF c_unit_enroll_status%NOTFOUND THEN
958 SELECT upld_unit_not_enrolled
959 INTO v_upld_unit_not_enrolled
960 FROM igs_as_entry_conf;
961 IF v_upld_unit_not_enrolled = 'D' THEN
962 p_error_code := 'IGS_AS_ASD_UNIT_NOT_ENROLLED';
963 p_ret_val := FALSE;
964 p_load_flag := 'N';
965 RETURN;
966 ELSIF v_upld_unit_not_enrolled = 'A' THEN
967 p_error_code := 'IGS_AS_ASA_UNIT_NOT_ENROLLED';
968 p_ret_val := FALSE;
969 p_insert_flag := 'N';
970 RETURN;
971 END IF;
972 END IF;
973 CLOSE c_unit_enroll_status;
974 /* Student who has been enrolled in the unit for current teaching period but afterward
975 withdrawn from the same then act according to configuration setup. Available options are
976 1) Abort File 2) Do not Load Record.
977 */
978 IF v_unit_enrolled_status = 'DISCONTIN' THEN
979 SELECT upld_unit_discont
980 INTO v_upld_unit_discont
981 FROM igs_as_entry_conf;
982 IF v_upld_unit_discont = 'D' THEN
983 p_error_code := 'IGS_AS_ASD_UNIT_DISCONTINUED';
984 p_ret_val := FALSE;
985 p_load_flag := 'N';
986 RETURN;
987 ELSIF v_upld_unit_discont = 'A' THEN
988 p_error_code := 'IGS_AS_ASA_UNIT_DISCONTINUED';
989 p_ret_val := FALSE;
990 p_insert_flag := 'N';
991 RETURN;
992 END IF;
993 /* Student might enrolled in the institution but not in the Unit for current period which
994 results are uploading then act according to configuration setup. Available options are
995 1) Abort File 2) Do not Load Record.
996 */
997 ELSIF v_unit_enrolled_status <> 'ENROLLED' THEN
998 SELECT upld_unit_not_enrolled
999 INTO v_upld_unit_not_enrolled
1000 FROM igs_as_entry_conf;
1001
1002 IF v_upld_unit_not_enrolled = 'D' THEN
1003 p_error_code := 'IGS_AS_ASD_UNIT_NOT_ENROLLED';
1004 p_ret_val := FALSE;
1005 p_load_flag := 'N';
1006 RETURN;
1007 ELSIF v_upld_unit_not_enrolled = 'A' THEN
1008 p_error_code := 'IGS_AS_ASA_UNIT_NOT_ENROLLED';
1009 p_ret_val := FALSE;
1010 p_insert_flag := 'N';
1011 RETURN;
1012 END IF;
1013 END IF;
1014 -- Assessment Item Not Exists
1015 -- Applies to a record where the student unit attempt does not have a record for that Assessment Item.
1016 OPEN c_ass_item_exist (v_uoo_id, p_ass_id, p_person_id, p_reference);
1017 FETCH c_ass_item_exist INTO v_assessment_item_exist;
1018 IF c_ass_item_exist%NOTFOUND THEN
1019 SELECT upld_asmnt_item_not_exist
1020 INTO v_upld_asmnt_item_not_exist
1021 FROM igs_as_entry_conf;
1022 IF v_upld_asmnt_item_not_exist = 'D' THEN
1023 p_error_code := 'IGS_AS_ASD_AIO_NOT_EXIST';
1024 p_ret_val := FALSE;
1025 p_load_flag := 'N';
1026 RETURN;
1027 ELSIF v_upld_asmnt_item_not_exist = 'A' THEN
1028 p_error_code := 'IGS_AS_ASA_AIO_NOT_EXIST';
1029 p_ret_val := FALSE;
1030 p_insert_flag := 'N';
1031 RETURN;
1032 END IF;
1033 END IF;
1034 --
1035 -- Check if Assessment Item Grade already exists
1036 --
1037 OPEN c_ass_item_grade_exist (v_uoo_id, p_person_id, p_ass_id, p_reference);
1038 FETCH c_ass_item_grade_exist INTO v_outcome_dt;
1039 IF c_ass_item_exist%FOUND
1040 AND v_outcome_dt IS NOT NULL THEN
1041 CLOSE c_ass_item_exist;
1042 /* If a record exist in the table with outcome date then act according to configuration setup.
1043 Available options are 1) Abort File 2) Do not Load file 3) Warning.*/
1044 SELECT upld_asmnt_item_grd_exist
1045 INTO v_upld_asmnt_grade_exist
1046 FROM igs_as_entry_conf;
1047 IF v_upld_asmnt_grade_exist = 'D' THEN
1048 p_error_code := 'IGS_AS_ASD_AIO_GRADE_EXIST';
1049 p_ret_val := FALSE;
1050 p_load_flag := 'N';
1051 RETURN;
1052 ELSIF v_upld_asmnt_grade_exist = 'A' THEN
1053 p_error_code := 'IGS_AS_ASA_AIO_GRADE_EXIST';
1054 p_ret_val := FALSE;
1055 p_insert_flag := 'N';
1056 RETURN;
1057 ELSIF v_upld_asmnt_grade_exist = 'W' THEN
1058 p_error_code := 'IGS_AS_ASW_AIO_GRADE_EXIST';
1059 p_ret_val := FALSE;
1060 END IF;
1061 ELSE
1062 CLOSE c_ass_item_exist;
1063 END IF;
1064 CLOSE c_ass_item_grade_exist;
1068 IF ((p_mark IS NULL) AND
1065 --
1066 -- Check that Marks are entered when Mark Entry is Mandatory
1067 --
1069 (rec_ai_mark_grade_conf.key_ai_mark_mndtry_flag = 'Y') AND
1070 (NVL (p_waived_flag, 'N') = 'N')) THEN
1071 p_error_code := 'IGS_SS_AS_MARK_MANDATORY';
1072 p_ret_val := FALSE;
1073 p_load_flag := 'N';
1074 RETURN;
1075 END IF;
1076 --
1077 -- Check number of decimal places entered in the marks field. If the number
1078 -- of decimals is more than the setup then show an error message
1079 --
1080 IF (((LENGTH (p_mark) - LENGTH (FLOOR (p_mark)) - 1) >
1081 rec_ai_mark_grade_conf.key_ai_mark_entry_dec_points) AND
1082 (NVL (p_waived_flag, 'N') = 'N')) THEN
1083 p_error_code := 'IGS_AS_MORE_DECIMAL_PLACES';
1084 p_ret_val := FALSE;
1085 p_load_flag := 'N';
1086 RETURN;
1087 END IF;
1088 --
1089 -- If Derive Grade From Mark is enabled then derive the Grade from the mark
1090 -- entered using the Assessment Item Grading Schema. If there is no range
1091 -- defined for the mark and Invalid Mark/Grade is not allowed then show error
1092 --
1093 IF ((rec_ai_mark_grade_conf.key_ai_grade_derive_flag = 'Y') AND
1094 (p_mark IS NOT NULL) AND
1095 (p_grade IS NULL) AND
1096 (NVL (p_waived_flag, 'N') = 'N')) THEN
1097 OPEN cur_gsg_derive (p_grading_schema_cd, p_gs_version_number);
1098 FETCH cur_gsg_derive INTO p_grade;
1099 IF ((cur_gsg_derive%NOTFOUND) AND
1100 (rec_ai_mark_grade_conf.key_ai_allow_invalid_flag <> 'Y')) THEN
1101 CLOSE cur_gsg_derive;
1102 p_error_code := 'IGS_AS_ASD_MARK_GRADE_INVALID';
1103 p_ret_val := FALSE;
1104 p_load_flag := 'N';
1105 RETURN;
1106 ELSE
1107 CLOSE cur_gsg_derive;
1108 END IF;
1109 END IF;
1110 --
1111 -- Grade Invalid
1112 --
1113 OPEN c_grade_invalid (p_grading_schema_cd, p_gs_version_number);
1114 FETCH c_grade_invalid INTO v_lower_mark_range,
1115 v_upper_mark_range;
1116 IF (c_grade_invalid%NOTFOUND AND
1117 p_grade IS NOT NULL AND
1118 (NVL (p_waived_flag, 'N') = 'N')) THEN
1119 /* If record contains a grade that is not within the grading schema for student unit attempt than
1120 act according to configuration setup. Available options are 1) Abort File 2) Do not Load Record
1121 */
1122 SELECT upld_grade_invalid
1123 INTO v_upld_grade_invalid
1124 FROM igs_as_entry_conf;
1125 IF v_upld_grade_invalid = 'D' THEN
1126 p_error_code := 'IGS_AS_ASD_GRADE_INVALID';
1127 p_ret_val := FALSE;
1128 p_load_flag := 'N';
1129 RETURN;
1130 ELSIF v_upld_grade_invalid = 'A' THEN
1131 p_error_code := 'IGS_AS_ASA_GRADE_INVALID';
1132 p_ret_val := FALSE;
1133 p_insert_flag := 'N';
1134 RETURN;
1135 END IF;
1136 END IF;
1137 CLOSE c_grade_invalid;
1138 --
1139 -- Mark Grade Combination Invalid
1140 --
1141 IF (p_mark NOT BETWEEN v_lower_mark_range AND v_upper_mark_range
1142 AND p_mark IS NOT NULL
1143 AND (NVL (p_waived_flag, 'N') = 'N')) THEN
1144 /* If a record contains a grade that is within the relevant grading schema for the student unit attempt
1145 but the grade dose not relate to the mark range for the entered mark then act according to
1146 configuration setup. Available options are 1) Abort File 2) Do not Load file 3) Warning.
1147 */
1148 IF (rec_ai_mark_grade_conf.key_ai_allow_invalid_flag <> 'Y') THEN
1149 p_error_code := 'IGS_AS_ASD_MARK_GRADE_INVALID';
1150 p_ret_val := FALSE;
1151 p_load_flag := 'N';
1152 RETURN;
1153 END IF;
1154 SELECT upld_mark_grade_invalid
1155 INTO v_upld_mark_grade_invalid
1156 FROM igs_as_entry_conf;
1157 --
1158 IF v_upld_mark_grade_invalid = 'D' THEN
1159 p_error_code := 'IGS_AS_ASD_MARK_GRADE_INVALID';
1160 p_ret_val := FALSE;
1161 p_load_flag := 'N';
1162 RETURN;
1163 ELSIF v_upld_mark_grade_invalid = 'A' THEN
1164 p_error_code := 'IGS_AS_ASA_MARK_GRADE_INVALID';
1165 p_ret_val := FALSE;
1166 p_insert_flag := 'N';
1167 RETURN;
1168 ELSIF v_upld_mark_grade_invalid = 'W' THEN
1169 p_error_code := 'IGS_AS_ASW_MARK_GRADE_INVALID';
1170 p_ret_val := FALSE;
1171 END IF;
1172 END IF;
1173 EXCEPTION
1174 WHEN NO_DATA_FOUND THEN
1175 p_insert_flag := 'N';
1176 p_load_flag := 'N';
1177 p_error_code := 'No Data Found';
1178 WHEN TOO_MANY_ROWS THEN
1179 p_insert_flag := 'N';
1180 p_load_flag := 'N';
1181 p_error_code := 'More then One row retirve for a select statment';
1182 WHEN OTHERS THEN
1183 p_insert_flag := 'N';
1184 p_load_flag := 'N';
1185 p_error_code := 'No Data Found - Others';
1186 END igs_as_aio_val_upld;
1187 --
1188 -- Validate the user while upload and download of ADI data
1189 --
1190 FUNCTION isvaliduser (
1191 p_userid IN NUMBER,
1192 p_uoo_id IN NUMBER DEFAULT NULL,
1193 p_group_id IN NUMBER DEFAULT NULL
1194 ) RETURN VARCHAR2 IS
1195 CURSOR cur_cusid IS
1196 SELECT person_party_id
1197 FROM fnd_user
1198 WHERE user_id = p_userid;
1199 CURSOR cur_instruct (cp_inst_id NUMBER) IS
1200 SELECT 1
1201 FROM igs_ps_usec_tch_resp
1202 WHERE instructor_id = cp_inst_id AND uoo_id = p_uoo_id;
1203 CURSOR cur_class_list (cp_inst_id NUMBER) IS
1204 SELECT 1
1205 FROM igs_as_x_usec_classlist_v
1206 WHERE instructor_id = cp_inst_id AND GROUP_ID = p_group_id;
1207 customerid NUMBER := NULL;
1208 linstructorid NUMBER;
1209 outval VARCHAR2 (3);
1210 BEGIN
1211 -- Check if the logged in user is a administrator
1212 IF fnd_function.test ('IGS_SS_ADMIN_HOME') OR fnd_function.test ('IGSAS016') OR fnd_function.test ('IGSAS030') THEN
1213 -- If administrator allow download by returning Y
1214 RETURN 'Y';
1215 ELSIF (fnd_function.test ('IGS_SS_FACULTY_HOME')) THEN
1216 -- Get the customer id for customer attached to the logged in user
1217 OPEN cur_cusid;
1218 FETCH cur_cusid INTO customerid;
1219 CLOSE cur_cusid;
1220 -- UOO_ID is present for grading and student list adi sheets
1221 IF p_uoo_id IS NOT NULL THEN
1222 -- check if the logged in user is a faculty for the given unit section identified by the uoo_id
1223 OPEN cur_instruct (customerid);
1224 FETCH cur_instruct INTO linstructorid;
1225 IF cur_instruct%NOTFOUND THEN
1226 -- Not a facluty for the unit section .. disallow download
1227 CLOSE cur_instruct;
1228 RETURN 'N';
1229 ELSE
1230 -- Is a facluty for the unit section .. allow download
1231 CLOSE cur_instruct;
1232 RETURN 'Y';
1233 END IF;
1234 ELSIF p_group_id IS NOT NULL THEN
1235 -- check if the logged in user is a faculty for the given cross listed group
1236 OPEN cur_class_list (p_group_id);
1237 FETCH cur_class_list INTO linstructorid;
1238 IF cur_class_list%NOTFOUND THEN
1239 -- Not a facluty for the crosslisted group .. disallow download
1243 -- Not a facluty for the crosslisted group .. disallow download
1240 CLOSE cur_class_list;
1241 RETURN 'N';
1242 ELSE
1244 CLOSE cur_class_list;
1245 RETURN 'Y';
1246 END IF;
1247 END IF;
1248 ELSE
1249 -- For all other responsibilities except ADMIN and FACULTY return N and disallow download
1250 RETURN 'N';
1251 END IF;
1252 -- For all other combinations return N and disallow download
1253 RETURN 'N';
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 RETURN 'N';
1257 END isvaliduser;
1258 END igs_as_adi_upld_aio_pkg;
[Home] [Help]