[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ADI_UPLD_UG_PKG
Source
1 PACKAGE BODY igs_as_adi_upld_ug_pkg AS
2 /* $Header: IGSAS43B.pls 120.4 2006/06/20 13:16:33 sepalani noship $ */
3 PROCEDURE grading_period_grade_process (
4 errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_user_id IN NUMBER,
7 p_batch_datetime IN VARCHAR2,
8 p_grade_creation_method_type IN VARCHAR2,
9 p_delete_rows IN VARCHAR2 DEFAULT 'Y'
10 ) IS
11 --
12 p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
13 --
14 --
15 --
16 --
17 --
18 --
19 CURSOR c_upload_outcome_ugi IS
20 SELECT user_id,
21 batch_date,
22 decode(person_number,'-',null,person_number) person_number,
23 decode(anonymous_id,'-',null,anonymous_id) anonymous_id,
24 course_cd,
25 unit_cd,
26 cal_type,
27 ci_sequence_number,
28 alternate_code,
29 grade,
30 mark,
31 grading_period_cd,
32 incomp_default_mark,
33 incomp_default_grade,
34 incomp_deadline_date,
35 comments,
36 error_code,
37 rowid,
38 unit_class,
39 location_cd,
40 uoo_id,
41 manual_override_flag,
42 mark_capped_flag,
43 release_date
44 FROM igs_as_ug_interface ugi
45 WHERE ugi.user_id = p_user_id
46 AND trunc(ugi.batch_date) = trunc(p_batch_date)
47 AND NVL(grading_period_cd , '-') <> '-';
48
49 --
50 --
51 --
52 CURSOR c_upload_outcome_ugi_upd IS
53 SELECT user_id,
54 batch_date,
55 decode(person_number,'-',null,person_number) person_number,
56 decode(anonymous_id,'-',null,anonymous_id) anonymous_id,
57 course_cd,
58 unit_cd,
59 cal_type,
60 ci_sequence_number ,
61 alternate_code,
62 grade,
63 mark,
64 grading_period_cd ,
65 incomp_default_mark,
66 incomp_default_grade,
67 incomp_deadline_date,
68 comments,
69 error_code,
70 rowid,
71 unit_class,
72 location_cd,
73 uoo_id,
74 manual_override_flag,
75 mark_capped_flag,
76 release_date
77 FROM igs_as_ug_interface ugi
78 WHERE ugi.user_id=p_user_id
79 AND trunc(ugi.batch_date) = trunc(p_batch_date)
80 AND (error_code IS NULL
81 OR error_code IN ('IGS_AS_ASW_GP_GRADE_EXIST', 'IGS_AS_ASW_MARK_GRADE_INVALID'))
82 AND NVL(grading_period_cd, '-') <> '-';
83 --
84 -- Cursor added to get the Student Unit Attempt uoo_id
85 --
86 CURSOR c_sua_uoo (
87 cp_person_id igs_en_su_attempt.person_id%TYPE,
88 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
89 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
90 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
91 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
92 cp_location_cd igs_en_su_attempt.location_cd%TYPE,
93 cp_unit_class igs_en_su_attempt.unit_class%TYPE
94 ) IS
95 SELECT sua.uoo_id
96 FROM igs_en_su_attempt sua
97 WHERE sua.person_id = cp_person_id
98 AND sua.course_cd = cp_course_cd
99 AND sua.unit_cd = cp_unit_cd
100 AND sua.cal_type = cp_cal_type
101 AND sua.ci_sequence_number = cp_ci_sequence_number
102 AND sua.location_cd = cp_location_cd
103 AND sua.unit_class = cp_unit_class;
104 --
105 --
106 --
107 CURSOR c_suao (
108 cp_person_id igs_as_su_atmpt_itm.person_id%TYPE,
109 cp_course_cd igs_as_su_atmpt_itm.course_cd%TYPE,
110 cp_unit_cd igs_as_su_atmpt_itm.unit_cd%TYPE,
111 cp_cal_type igs_as_su_atmpt_itm.cal_type%TYPE,
112 cp_ci_sequence_number igs_as_su_atmpt_itm.ci_sequence_number%TYPE,
113 cp_grading_period_cd igs_as_gpc_programs.grading_period_cd%TYPE,
114 -- anilk, 22-Apr-2003, Bug# 2829262
115 cp_uoo_id igs_as_su_atmpt_itm.uoo_id%TYPE
116 ) IS
117 SELECT ROWID
118 FROM igs_as_su_stmptout_all suao
119 WHERE suao.person_id = cp_person_id
120 AND suao.course_cd = cp_course_cd
121 AND suao.uoo_id = cp_uoo_id
122 AND suao.grading_period_cd = cp_grading_period_cd
123 AND suao.finalised_outcome_ind = 'N';
124 --
125 --
126 --
127 CURSOR c_suao_record (
128 cp_rowid IN VARCHAR2
129 ) IS
130 SELECT suao.*
131 FROM igs_as_su_stmptout_all suao
132 WHERE ROWID = cp_rowid;
133 -- Declare local variables
134 v_person_id NUMBER (15);
135 v_cal_type VARCHAR2 (10);
136 v_ci_sequence_number NUMBER (6);
137 v_ci_start_dt DATE;
138 v_ci_end_dt DATE;
139 v_error_code VARCHAR2 (30);
140 v_grade VARCHAR2 (5);
141 v_request_id NUMBER;
142 v_uoo_id NUMBER (7);
143 v_load_file_flag VARCHAR2 (1);
144 v_load_file_master VARCHAR2 (1) := 'Y';
145 v_load_record_flag VARCHAR2 (1);
146 v_grading_schema_cd VARCHAR2 (10);
147 v_gs_version_number NUMBER (3);
148 v_rowid VARCHAR2 (25);
149 v_outcome_dt DATE DEFAULT SYSDATE;
150 v_s_grade_creation_method_type VARCHAR2 (30) DEFAULT 'WEBADI';
151 v_finalised_outcome_ind VARCHAR2 (1) DEFAULT 'N';
152 v_number_times_keyed NUMBER (2) DEFAULT 1;
153 l_uoo_id NUMBER;
154 rec_suao_record c_suao_record%ROWTYPE;
155 --
156 l_validuser varchar2(1);
157 BEGIN
158
159 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
160 --
161 -- FND_LOGGING
162 --
163 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
164 fnd_log.string ( fnd_log.level_procedure,
165 'igs.plsql.igs_as_adi_upld_ug_pkg.grading_period_grade_process.begin',
166 'Params: p_user_id => '||p_user_id|| ';' ||
167 ' p_batch_datetime => '||p_batch_datetime|| ';' ||
168 ' p_grade_creation_method_type => '||p_grade_creation_method_type|| ';' ||
169 ' p_delete_rows => '||p_delete_rows|| ';'
170 );
171 END IF;
172
173 FOR v_ugi_rec IN c_upload_outcome_ugi LOOP
174 -- Initialize variables here.
175 v_cal_type := v_ugi_rec.cal_type;
176 v_ci_sequence_number := v_ugi_rec.ci_sequence_number;
177 v_grade := v_ugi_rec.grade;
178 --Call routine to upload for validate the particular row
179
180 --Check if the user is authorised to upload data .
181 --Only admin and faculty for the unitsection can upload data to OSS.
182
183 l_validuser:=IGS_AS_ADI_UPLD_AIO_PKG.isvaliduser (
184 v_ugi_rec.user_id ,
185 v_ugi_rec.uoo_id
186 );
187
188 if(l_validuser<>'Y') then
189 UPDATE igs_as_ug_interface
190 SET error_code = 'IGS_EN_PERSON_NO_RESP'
191 WHERE ROWID = v_ugi_rec.ROWID;
192 else
193 --
194 -- FND_LOGGING
195 --
196 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
197 fnd_log.string (fnd_log.level_statement,
198 'igs.plsql.igs_as_adi_upld_ug_pkg.grading_period_grade_process.c_upload_outcome_ugi',
199 'v_ugi_rec.person_number => '||v_ugi_rec.person_number||';'||
200 'v_ugi_rec.anonymous_id => '||v_ugi_rec.anonymous_id||';'||
201 'v_cal_type => '||v_cal_type||';'||
202 'v_ci_sequence_number =>'||v_ci_sequence_number||';'||
203 'v_grade =>'||v_grade||';'
204 );
205 END IF;
206
207 igs_as_ug_val_upld (
208 v_ugi_rec.person_number,
209 v_ugi_rec.anonymous_id,
210 v_ugi_rec.alternate_code,
211 v_ugi_rec.course_cd,
212 v_ugi_rec.unit_cd,
213 v_ugi_rec.grading_period_cd,
214 v_ugi_rec.mark,
215 v_grade,
216 v_person_id,
217 v_cal_type,
218 v_ci_sequence_number,
219 v_ci_start_dt,
220 v_ci_end_dt,
221 v_grading_schema_cd,
222 v_gs_version_number,
223 v_error_code,
224 v_load_file_flag,
225 v_load_record_flag,
226 v_ugi_rec.unit_class,
227 v_ugi_rec.location_cd,
228 v_ugi_rec.manual_override_flag,
229 v_ugi_rec.mark_capped_flag,
230 v_ugi_rec.release_date,
231 v_ugi_rec.uoo_id
232 );
233 IF v_load_file_flag = 'N' THEN
234 v_load_file_master := 'N';
235 END IF;
236 UPDATE igs_as_ug_interface
237 SET error_code = v_error_code,
238 grade = v_grade
239 WHERE ROWID = v_ugi_rec.ROWID;
240 end if ;
241 END LOOP;
242 COMMIT;
243 FOR v_ugi_rec IN c_upload_outcome_ugi_upd LOOP
244 -- Initialize variables here.
245 v_cal_type := v_ugi_rec.cal_type;
246 v_ci_sequence_number := v_ugi_rec.ci_sequence_number;
247 v_grade := v_ugi_rec.grade;
248 --
249
250 IF (UPPER (NVL (v_ugi_rec.manual_override_flag, 'N')) <> 'Y') THEN
251 v_ugi_rec.manual_override_flag := 'N';
252 ELSE
253 v_ugi_rec.manual_override_flag := 'Y';
254 END IF;
255 --
256 IF (UPPER (NVL (v_ugi_rec.mark_capped_flag, 'N')) <> 'Y') THEN
257 v_ugi_rec.mark_capped_flag := 'N';
258 ELSE
259 v_ugi_rec.mark_capped_flag := 'Y';
260 END IF;
261
262 --
263 -- FND_LOGGING
264 --
265 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
266 fnd_log.string (fnd_log.level_statement,
267 'igs.plsql.igs_as_adi_upld_ug_pkg.grading_period_grade_process.c_upload_outcome_ugi_upd',
268 'v_ugi_rec.person_number => '||v_ugi_rec.person_number||';'||
269 'v_ugi_rec.anonymous_id => '||v_ugi_rec.anonymous_id||';'||
270 'v_cal_type => '||v_cal_type||';'||
271 'v_ci_sequence_number =>'||v_ci_sequence_number||';'||
272 'v_grade =>'||v_grade||';'
273 );
274 END IF;
275
276 igs_as_ug_val_upld (
277 v_ugi_rec.person_number,
278 v_ugi_rec.anonymous_id,
279 v_ugi_rec.alternate_code,
280 v_ugi_rec.course_cd,
281 v_ugi_rec.unit_cd,
282 v_ugi_rec.grading_period_cd,
283 v_ugi_rec.mark,
284 v_grade,
285 v_person_id,
286 v_cal_type,
287 v_ci_sequence_number,
288 v_ci_start_dt,
289 v_ci_end_dt,
290 v_grading_schema_cd,
291 v_gs_version_number,
292 v_error_code,
293 v_load_file_flag,
294 v_load_record_flag,
295 v_ugi_rec.unit_class,
296 v_ugi_rec.location_cd,
297 v_ugi_rec.manual_override_flag,
298 v_ugi_rec.mark_capped_flag,
299 v_ugi_rec.release_date,
300 v_ugi_rec.uoo_id
301 );
302 IF v_load_record_flag = 'Y' THEN
303 -- anilk, 22-Apr-2003, Bug# 2829262
304
305 -- BUG # 2735673 UOO_ID is added to the interface table. So no need to quary for UOO_ID from the tables.
306 -- Still keeping this cursor if user does not provide the uoo_id in the interface table
307 -- and wants to upload
308 IF v_ugi_rec.uoo_id IS NULL THEN
309 OPEN c_sua_uoo(
310 v_person_id,
311 v_ugi_rec.course_cd,
312 v_ugi_rec.unit_cd,
313 v_cal_type,
314 v_ci_sequence_number,
315 v_ugi_rec.location_cd,
316 v_ugi_rec.unit_class
317 );
318 FETCH c_sua_uoo INTO l_uoo_id;
319 CLOSE c_sua_uoo;
320 v_ugi_rec.uoo_id := l_uoo_id;
321 END IF;
322
323 OPEN c_suao (
324 v_person_id,
325 v_ugi_rec.course_cd,
326 v_ugi_rec.unit_cd,
327 v_cal_type,
328 v_ci_sequence_number,
329 v_ugi_rec.grading_period_cd,
330 v_ugi_rec.uoo_id
331 );
332
333 FETCH c_suao INTO v_rowid;
334 IF c_suao%FOUND THEN
335 BEGIN
336 OPEN c_suao_record (v_rowid);
337 FETCH c_suao_record INTO rec_suao_record;
338 CLOSE c_suao_record;
339 -- that means record is already exists in base table
340
341 IF v_ugi_rec.grading_period_cd = 'EARLY_FINAL' THEN
342 v_ugi_rec.grading_period_cd := 'FINAL';
343 END IF;
344
345 igs_as_su_stmptout_pkg.update_row (
346 x_rowid => v_rowid,
347 x_person_id => v_person_id,
348 x_course_cd => v_ugi_rec.course_cd,
349 x_unit_cd => v_ugi_rec.unit_cd,
350 x_cal_type => v_cal_type,
351 x_ci_sequence_number => v_ci_sequence_number,
352 x_outcome_dt => v_outcome_dt,
353 x_ci_start_dt => v_ci_start_dt,
354 x_ci_end_dt => v_ci_end_dt,
355 x_grading_schema_cd => v_grading_schema_cd,
356 x_version_number => v_gs_version_number,
357 x_grade => v_ugi_rec.grade,
358 x_s_grade_creation_method_type => v_s_grade_creation_method_type,
359 x_finalised_outcome_ind => v_finalised_outcome_ind,
360 x_mark => v_ugi_rec.mark,
361 x_number_times_keyed => v_number_times_keyed,
362 x_translated_grading_schema_cd => rec_suao_record.translated_grading_schema_cd,
363 x_translated_version_number => rec_suao_record.translated_version_number,
364 x_translated_grade => rec_suao_record.translated_grade,
365 x_translated_dt => rec_suao_record.translated_dt,
366 x_mode => 'S',
367 x_grading_period_cd => v_ugi_rec.grading_period_cd,
368 x_attribute_category => rec_suao_record.attribute_category,
369 x_attribute1 => rec_suao_record.attribute1,
370 x_attribute2 => rec_suao_record.attribute2,
371 x_attribute3 => rec_suao_record.attribute3,
372 x_attribute4 => rec_suao_record.attribute4,
373 x_attribute5 => rec_suao_record.attribute5,
374 x_attribute6 => rec_suao_record.attribute6,
375 x_attribute7 => rec_suao_record.attribute7,
376 x_attribute8 => rec_suao_record.attribute8,
377 x_attribute9 => rec_suao_record.attribute9,
378 x_attribute10 => rec_suao_record.attribute10,
379 x_attribute11 => rec_suao_record.attribute11,
380 x_attribute12 => rec_suao_record.attribute12,
381 x_attribute13 => rec_suao_record.attribute13,
382 x_attribute14 => rec_suao_record.attribute14,
383 x_attribute15 => rec_suao_record.attribute15,
384 x_attribute16 => rec_suao_record.attribute16,
385 x_attribute17 => rec_suao_record.attribute17,
386 x_attribute18 => rec_suao_record.attribute18,
387 x_attribute19 => rec_suao_record.attribute19,
388 x_attribute20 => rec_suao_record.attribute20,
389 x_incomp_deadline_date => v_ugi_rec.incomp_deadline_date,
390 x_incomp_grading_schema_cd => rec_suao_record.incomp_grading_schema_cd,
391 x_incomp_version_number => rec_suao_record.incomp_version_number,
392 x_incomp_default_grade => v_ugi_rec.incomp_default_grade,
393 x_incomp_default_mark => v_ugi_rec.incomp_default_mark,
394 x_comments => v_ugi_rec.comments,
395 x_uoo_id => v_ugi_rec.uoo_id,
396 x_mark_capped_flag => v_ugi_rec.mark_capped_flag,
397 x_release_date => v_ugi_rec.release_date,
398 x_manual_override_flag => v_ugi_rec.manual_override_flag,
399 x_show_on_academic_histry_flag => rec_suao_record.show_on_academic_histry_flag
400 );
401 EXCEPTION
402 WHEN OTHERS THEN
403 DECLARE
404 app_short_name VARCHAR2 (10);
405 message_name VARCHAR2 (100);
406 BEGIN
407 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
408 fnd_message.parse_encoded (
409 fnd_message.get_encoded,
410 app_short_name,
411 message_name
412 );
413 retcode := 2;
414 errbuf := message_name;
415 IF (errbuf IS NOT NULL) THEN
416 UPDATE igs_as_ug_interface
417 SET error_code = errbuf
418 WHERE rowid = v_ugi_rec.rowid;
419 END IF;
420 END;
421 END;
422 ELSE -- records is not exist in base table
423 BEGIN
424 IF v_ugi_rec.grading_period_cd = 'EARLY_FINAL' THEN
425 v_ugi_rec.grading_period_cd := 'FINAL';
426 END IF;
427 igs_as_su_stmptout_pkg.insert_row (
428 x_rowid => v_rowid,
429 x_org_id => NULL,
430 x_person_id => v_person_id,
431 x_course_cd => v_ugi_rec.course_cd,
432 x_unit_cd => v_ugi_rec.unit_cd,
433 x_cal_type => v_cal_type,
434 x_ci_sequence_number => v_ci_sequence_number,
435 x_outcome_dt => v_outcome_dt,
436 x_ci_start_dt => v_ci_start_dt,
437 x_ci_end_dt => v_ci_end_dt,
438 x_grading_schema_cd => v_grading_schema_cd,
439 x_version_number => v_gs_version_number,
440 x_grade => v_ugi_rec.grade,
441 x_s_grade_creation_method_type => v_s_grade_creation_method_type,
442 x_finalised_outcome_ind => v_finalised_outcome_ind,
443 x_mark => v_ugi_rec.mark,
444 x_number_times_keyed => v_number_times_keyed,
445 x_translated_grading_schema_cd => NULL,
446 x_translated_version_number => NULL,
447 x_translated_grade => NULL,
448 x_translated_dt => NULL,
449 x_mode => 'S',
450 x_grading_period_cd => v_ugi_rec.grading_period_cd,
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_incomp_deadline_date => v_ugi_rec.incomp_deadline_date,
473 x_incomp_grading_schema_cd => NULL,
474 x_incomp_version_number => NULL,
475 x_incomp_default_grade => v_ugi_rec.incomp_default_grade,
476 x_incomp_default_mark => v_ugi_rec.incomp_default_mark,
477 x_comments => v_ugi_rec.comments,
478 x_uoo_id => v_ugi_rec.uoo_id,
479 x_mark_capped_flag => v_ugi_rec.mark_capped_flag,
480 x_release_date => v_ugi_rec.release_date,
481 x_manual_override_flag => v_ugi_rec.manual_override_flag,
482 x_show_on_academic_histry_flag => 'Y'
483 );
484 EXCEPTION
485 WHEN OTHERS THEN
486 DECLARE
487 app_short_name VARCHAR2 (10);
488 message_name VARCHAR2 (100);
489 BEGIN
490 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
491 fnd_message.parse_encoded (
492 fnd_message.get_encoded,
493 app_short_name,
494 message_name
495 );
496 retcode := 2;
497 errbuf := message_name;
498 IF (errbuf IS NOT NULL) THEN
499 UPDATE igs_as_ug_interface
500 SET error_code = errbuf
501 WHERE rowid = v_ugi_rec.rowid;
502 END IF;
503 END;
504 END;
505 END IF;
506 CLOSE c_suao;
507 END IF;
508 END LOOP;
509 /* Extracting WebADI from Concurrent Program LOV */
510 IF p_grade_creation_method_type <> 'WEBADI' THEN
511 v_request_id := fnd_request.submit_request ('IGS', 'IGSASS24', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
512 IF v_request_id = 0 THEN
513 RAISE fnd_api.g_exc_unexpected_error;
514 END IF;
515 END IF;
516 --
517 -- FND_LOGGING
518 --
519 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
520 fnd_log.string ( fnd_log.level_procedure,
521 'igs.plsql.igs_as_adi_upld_ug_pkg.grading_period_grade_process.end',
522 'Exiting IGS_AS_ADI_UPLD_UG_PKG.grading_period_grade_process'
523 );
524 END IF;
525
526 COMMIT;
527 END grading_period_grade_process;
528 --
529 -- Validate single Grading Period record from the interface table
530 -- before being uploaded. This validation is called from the interface
531 -- table import routine and also the ADI pre-validation functionality.
532 --
533 PROCEDURE igs_as_ug_val_upld (
534 p_person_number IN VARCHAR2,
535 p_anonymous_id IN VARCHAR2,
536 p_alternate_code IN VARCHAR2,
537 p_course_cd IN VARCHAR2,
538 p_unit_cd IN VARCHAR2,
539 p_grading_period_cd IN VARCHAR2,
540 p_mark IN NUMBER,
541 p_grade IN OUT NOCOPY VARCHAR2,
542 p_person_id OUT NOCOPY NUMBER,
543 p_cal_type IN OUT NOCOPY VARCHAR2,
544 p_ci_sequence_number IN OUT NOCOPY NUMBER,
545 p_ci_start_dt OUT NOCOPY DATE,
546 p_ci_end_dt OUT NOCOPY DATE,
547 p_grading_schema_cd OUT NOCOPY VARCHAR2,
548 p_gs_version_number OUT NOCOPY NUMBER,
549 p_error_code OUT NOCOPY VARCHAR2,
550 p_load_file_flag OUT NOCOPY VARCHAR2,
551 p_load_record_flag OUT NOCOPY VARCHAR2,
552 p_unit_class IN VARCHAR2,
553 p_location_cd IN VARCHAR2,
554 manual_override_flag IN VARCHAR2,
555 mark_capped_flag IN VARCHAR2,
556 release_date IN DATE,
557 p_uoo_id IN NUMBER
558 ) IS
559 --
560 v_key_mark_mndtry_ind VARCHAR2 (1);
561 v_key_grade_derive_ind VARCHAR2 (1);
562 v_key_allow_invalid_ind VARCHAR2 (1);
563 v_upld_person_no_exist VARCHAR2 (1);
564 v_upld_crs_not_enrolled VARCHAR2 (1);
565 v_upld_unit_not_enrolled VARCHAR2 (1);
566 v_upld_unit_discont VARCHAR2 (1);
567 v_upld_grade_invalid VARCHAR2 (1);
568 v_upld_mark_grade_invalid VARCHAR2 (1);
569 v_upld_ug_sbmtd_grade_exist VARCHAR2 (1);
570 v_upld_ug_saved_grade_exist VARCHAR2 (1);
571 v_key_mark_entry_dec_points NUMBER;
572 v_course_attempt_status VARCHAR2 (30);
573 v_unit_attempt_status VARCHAR2 (30);
574 v_uoo_id NUMBER;
575 v_finalized_outcome_ind VARCHAR2 (1);
576 v_mark NUMBER (6, 3);
577 v_grade VARCHAR2 (5);
578 v_lower_mark_range NUMBER (3);
579 v_upper_mark_range NUMBER (3);
580 v_grading_schema_cd VARCHAR2 (10);
581 v_gs_version_number NUMBER (3);
582 v_submission_status VARCHAR2 (20);
583 v_dummy VARCHAR2 (1);
584 v_result_type VARCHAR2 (30);
585 --
586 --
587 --
588 CURSOR cur_uoo_id IS
589 SELECT uoo_id
590 FROM igs_ps_unit_ofr_opt
591 WHERE unit_cd = p_unit_cd
592 AND cal_type = p_cal_type
593 AND ci_sequence_number = p_ci_sequence_number
594 AND location_cd = p_location_cd
595 AND unit_class = p_unit_class;
596 rec_uoo_id cur_uoo_id%ROWTYPE;
597 --
598 -- Grade Period Start Date
599 --
600 CURSOR c_grd_st IS
601 SELECT 'X'
602 FROM igs_ca_da_inst_v
603 WHERE cal_type = p_cal_type
604 AND ci_sequence_number = p_ci_sequence_number
605 AND dt_alias = (SELECT DECODE (
606 p_grading_period_cd,
607 'FINAL', final_mgs_start_dt_alias,
608 'MIDTERM', mid_mgs_start_dt_alias,
609 'EARLY_FINAL', efinal_mgs_start_dt_alias,
610 NULL
611 )
612 FROM igs_as_cal_conf
613 WHERE s_control_num = 1)
614 AND alias_val <= SYSDATE;
615 --
616 -- Get Grade Entry Configuration
617 --
618 CURSOR c_ec IS
619 SELECT key_grade_derive_ind,
620 key_allow_invalid_ind,
621 upld_person_no_exist,
622 upld_crs_not_enrolled,
623 upld_unit_not_enrolled,
624 upld_unit_discont,
625 upld_grade_invalid,
626 upld_mark_grade_invalid,
627 upld_ug_sbmtd_grade_exist,
628 upld_ug_saved_grade_exist,
629 key_mark_mndtry_ind,
630 key_mark_entry_dec_points
631 FROM igs_as_entry_conf ec
632 WHERE ec.s_control_num = 1;
633 --
634 --
635 --
636 CURSOR c_ci IS
637 SELECT ci.cal_type,
638 ci.sequence_number,
639 ci.start_dt,
640 ci.end_dt
641 FROM igs_ca_inst_all ci,
642 igs_ca_type ct,
643 igs_ca_stat cs
644 WHERE /* (ci.alternate_code = p_alternate_code
645 OR p_alternate_code IS NULL
646 )
647 AND */ ((ci.cal_type = p_cal_type
648 AND ci.sequence_number = p_ci_sequence_number
649 )
650 OR p_cal_type IS NULL
651 )
652 AND ct.cal_type = ci.cal_type
653 AND ct.s_cal_cat = 'TEACHING'
654 AND cs.cal_status = ci.cal_status
655 AND cs.s_cal_status = 'ACTIVE';
656 --
657 -- Get Person ID
658 --
659 CURSOR c_p IS
660 SELECT p.party_id
661 FROM hz_parties p
662 WHERE p.party_number = p_person_number;
663 --
664 -- Get Student Program Attempt status
665 --
666 CURSOR c_spa IS
667 SELECT spa.course_attempt_status
668 FROM igs_en_stdnt_ps_att spa
669 WHERE spa.person_id = p_person_id
670 AND spa.course_cd = p_course_cd;
671 --
672 -- Get Student Unit Attempt details
673 --
674 CURSOR c_sua (
675 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE
676 ) IS
677 SELECT sua.unit_attempt_status,
678 NVL (ugs.grading_schema_code, ungs.GRADING_SCHEMA_CODE),
679 NVL (ugs.grd_schm_version_number, ungs.GRD_SCHM_VERSION_NUMBER)
680 FROM igs_en_su_attempt sua,
681 --igs_ps_unit_ofr_opt uoo,
682 IGS_PS_UNIT_GRD_SCHM ungs,
683 igs_ps_usec_grd_schm ugs
684 WHERE sua.person_id = p_person_id
685 AND sua.course_cd = p_course_cd
686 AND sua.unit_cd = p_unit_cd
687 AND sua.uoo_id = cp_uoo_id
688 AND ungs.unit_code = sua.unit_cd
689 AND ungs.unit_version_number = sua.version_number
690 AND ungs.default_flag = 'Y'
691 AND sua.uoo_id = ugs.uoo_id(+)
692 AND ugs.default_flag(+) = 'Y';
693 --
694 -- Get saved or submitted grades
695 --
696 CURSOR c_suao (
697 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
698 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
699 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE
700 ) IS
701 SELECT suao.mark,
702 suao.grade,
703 suao.finalised_outcome_ind
704 FROM igs_as_su_stmptout_all suao
705 WHERE suao.person_id = p_person_id
706 AND suao.course_cd = p_course_cd
707 -- anilk, 22-Apr-2003, Bug# 2829262
708 AND suao.uoo_id = cp_uoo_id
709 AND suao.grading_period_cd = p_grading_period_cd;
710 --
711 -- Get Grading Schema details
712 --
713 CURSOR c_gsg (
714 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
715 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE
716 ) IS
717 SELECT gsg.lower_mark_range,
718 gsg.upper_mark_range,
719 gsg.s_result_type
720 FROM igs_as_grd_sch_grade gsg
721 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
722 AND gsg.version_number = cp_gs_version_number
723 AND gsg.grade = p_grade
724 AND gsg.system_only_ind = 'N';
725 --
726 -- Derive Grade
727 --
728 CURSOR c_gsg_derive (
729 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
730 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE
731 ) IS
732 SELECT gsg.grade
733 FROM igs_as_grd_sch_grade gsg
734 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
735 AND gsg.version_number = cp_gs_version_number
736 AND gsg.system_only_ind = 'N'
737 AND p_mark BETWEEN lower_mark_range AND upper_mark_range + 0.999;
738 --
739 -- Get Grading Submission History
740 --
741 CURSOR c_gsh (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
742 SELECT gsh.submission_status
743 FROM igs_as_gaa_sub_hist gsh
744 WHERE gsh.uoo_id = cp_uoo_id
745 AND gsh.grading_period_cd = p_grading_period_cd;
746 --
747 -- Check Grading Period Cohorts
748 --
749 CURSOR c_gpc IS
750 SELECT 'X'
751 FROM DUAL
752 WHERE EXISTS ( SELECT 'X'
753 FROM igs_as_gpc_programs gpr
754 WHERE gpr.course_cd = p_course_cd
755 AND gpr.grading_period_cd = p_grading_period_cd)
756 OR EXISTS ( SELECT 'X'
757 FROM igs_en_stdnt_ps_att spa,
758 igs_as_gpc_aca_stndg gas
759 WHERE spa.person_id = p_person_id
760 AND spa.course_cd = p_course_cd
761 AND spa.progression_status = gas.progression_status
762 AND gas.grading_period_cd = p_grading_period_cd)
763 OR EXISTS ( SELECT 'X'
764 FROM igs_pe_prsid_grp_mem pigm,
765 igs_as_gpc_pe_id_grp gpg
766 WHERE p_person_id = pigm.person_id
767 AND pigm.GROUP_ID = gpg.GROUP_ID
768 AND gpg.grading_period_cd = p_grading_period_cd)
769 OR EXISTS ( SELECT 'X'
770 FROM igs_as_gpc_cls_stndg gcs
771 WHERE gcs.class_standing = igs_pr_get_class_std.get_class_standing (
772 p_person_id,
773 p_course_cd,
774 'N',
775 SYSDATE,
776 NULL,
777 NULL
778 )
779 AND gcs.grading_period_cd = p_grading_period_cd)
780 OR EXISTS ( SELECT 'X'
781 FROM igs_as_su_setatmpt susa,
782 igs_as_gpc_unit_sets gus
783 WHERE susa.person_id = p_person_id
784 AND susa.course_cd = p_course_cd
785 AND susa.selection_dt IS NOT NULL
786 AND (susa.end_dt IS NULL
787 OR susa.rqrmnts_complete_ind = 'Y'
788 )
789 AND susa.unit_set_cd = gus.unit_set_cd
790 AND gus.grading_period_cd = p_grading_period_cd);
791 --
792 -- Get the Minimum of the Lower and Maximum of the Upper Mark limits
793 -- as setup in the Grading Schema
794 --
795 CURSOR c_gsg_min_max (
796 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
797 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE
798 ) IS
799 SELECT NVL (MIN (gsg.lower_mark_range), 0) min_lower_mark_range,
800 NVL (MAX (gsg.upper_mark_range), 1000) max_upper_mark_range
801 FROM igs_as_grd_sch_grade gsg
802 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
803 AND gsg.version_number = cp_gs_version_number;
804 rec_gsg_min_max c_gsg_min_max%ROWTYPE;
805 --
806 BEGIN
807 --
808 -- Initialise variables
809 --
810 p_load_file_flag := 'Y';
811 p_load_record_flag := 'Y';
812 -- anilk, 22-Apr-2003, Bug# 2829262
813
814 -- BUG # 2735673 UOO_ID is added to the interface table. So no need to quary for UOO_ID from the tables.
815 -- Still keeping this cursor if user does not provide the uoo_id in the interface table
816 -- and wants to upload
817
818
819 IF p_uoo_id IS NULL THEN
820 OPEN cur_uoo_id;
821 FETCH cur_uoo_id INTO rec_uoo_id;
822 v_uoo_id := rec_uoo_id.uoo_id;
823 CLOSE cur_uoo_id;
824 ELSE
825 v_uoo_id := p_uoo_id;
826 END IF;
827
828 --
829 -- Get the Calendar Instance details
830 --
831 OPEN c_ci;
832
833 FETCH c_ci INTO p_cal_type,
834 p_ci_sequence_number,
835 p_ci_start_dt,
836 p_ci_end_dt;
837 IF c_ci%NOTFOUND THEN
838 CLOSE c_ci;
839 p_error_code := 'IGS_AS_MISSING_ALTNTE_CODE';
840 p_load_file_flag := 'N';
841 RETURN;
842 END IF;
843 CLOSE c_ci;
844 --
845 -- Get Grade Entry Configuration
846 --
847 OPEN c_ec;
848 FETCH c_ec INTO v_key_grade_derive_ind,
849 v_key_allow_invalid_ind,
850 v_upld_person_no_exist,
851 v_upld_crs_not_enrolled,
852 v_upld_unit_not_enrolled,
853 v_upld_unit_discont,
854 v_upld_grade_invalid,
855 v_upld_mark_grade_invalid,
856 v_upld_ug_sbmtd_grade_exist,
857 v_upld_ug_saved_grade_exist,
858 v_key_mark_mndtry_ind,
859 v_key_mark_entry_dec_points;
860 CLOSE c_ec;
861 --
862 -- Check number of decimal places in marks
863 --
864 IF (LENGTH (p_mark) - LENGTH (FLOOR (p_mark)) - 1) > v_key_mark_entry_dec_points THEN
865 p_error_code := 'IGS_AS_MORE_DECIMAL_PLACES';
866 p_load_record_flag := 'N';
867 RETURN;
868 END IF;
869 --
870 -- Get Person id AND Person Does Not Exist --
871 --
872 IF p_person_number IS NULL
873 AND p_anonymous_id IS NULL THEN
874 IF v_upld_person_no_exist = 'D' THEN
875 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
876 p_load_record_flag := 'N';
877 RETURN;
878 ELSIF v_upld_person_no_exist = 'A' THEN
879 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
880 p_load_file_flag := 'N';
881 RETURN;
882 END IF;
883 ELSIF p_person_number IS NOT NULL
884 AND p_anonymous_id IS NOT NULL THEN
885 p_error_code := 'IGS_AS_ASD_PER_ANON_BOTH_EXIST';
886 p_load_record_flag := 'N';
887 RETURN;
888 ELSIF p_person_number IS NULL
889 AND p_anonymous_id IS NOT NULL THEN
890 --
891 -- Call function to get Person ID based on Anonymous ID
892 --
893 p_person_id := igs_as_anon_grd_pkg.get_person_id (p_anonymous_id, p_cal_type, p_ci_sequence_number);
894 IF p_person_id IS NULL THEN
895 IF v_upld_person_no_exist = 'D' THEN
896 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
897 p_load_record_flag := 'N';
898 RETURN;
899 ELSIF v_upld_person_no_exist = 'A' THEN
900 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
901 p_load_file_flag := 'N';
902 RETURN;
903 END IF;
904 END IF;
905 ELSIF p_person_number IS NOT NULL
906 AND p_anonymous_id IS NULL THEN
907 OPEN c_p;
908 FETCH c_p INTO p_person_id;
909 IF c_p%NOTFOUND THEN
910 IF v_upld_person_no_exist = 'D' THEN
911 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
912 p_load_record_flag := 'N';
913 RETURN;
914 ELSIF v_upld_person_no_exist = 'A' THEN
915 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
916 p_load_file_flag := 'N';
917 RETURN;
918 END IF;
919 END IF;
920 CLOSE c_p;
921 END IF;
922 --
923 -- Check Grading Period Start Date
924 --
925 OPEN c_grd_st;
926 FETCH c_grd_st INTO v_dummy;
927 IF c_grd_st%NOTFOUND THEN
928 p_error_code := 'IGS_SS_AS_GRD_START_DT_VIOLATN';
929 p_load_record_flag := 'N';
930 RETURN;
931 END IF;
932 CLOSE c_grd_st;
933 --
934 -- Check Student Program Attempt status
935 --
936 OPEN c_spa;
937 FETCH c_spa INTO v_course_attempt_status;
938 IF c_spa%NOTFOUND
939 OR v_course_attempt_status NOT IN ('ENROLLED', 'INACTIVE') THEN
940 IF v_upld_crs_not_enrolled = 'D' THEN
941 p_error_code := 'IGS_AS_ASD_COURSE_NOT_ENROLLED';
942 p_load_record_flag := 'N';
943 RETURN;
944 ELSIF v_upld_crs_not_enrolled = 'A' THEN
945 p_error_code := 'IGS_AS_ASA_COURSE_NOT_ENROLLED';
946 p_load_file_flag := 'N';
947 RETURN;
948 END IF;
949 END IF;
950 --
951 -- Check Student Unit Attempt status
952 --
953 OPEN c_sua (
954 v_uoo_id
955 );
956 FETCH c_sua INTO v_unit_attempt_status,
957 v_grading_schema_cd,
958 v_gs_version_number;
959 IF c_sua%NOTFOUND THEN
960 IF v_upld_unit_not_enrolled = 'D' THEN
961 p_error_code := 'IGS_AS_ASD_UNIT_NOT_ENROLLED';
962 p_load_record_flag := 'N';
963 RETURN;
964 ELSIF v_upld_unit_not_enrolled = 'A' THEN
965 p_error_code := 'IGS_AS_ASA_UNIT_NOT_ENROLLED';
966 p_load_file_flag := 'N';
967 RETURN;
968 END IF;
969 END IF;
970 IF v_unit_attempt_status = 'DISCONTIN' THEN
971 IF v_upld_unit_discont = 'D' THEN
972 p_error_code := 'IGS_AS_ASD_UNIT_DISCONTINUED';
973 p_load_record_flag := 'N';
974 RETURN;
975 ELSIF v_upld_unit_discont = 'A' THEN
976 p_error_code := 'IGS_AS_ASA_UNIT_DISCONTINUED';
977 p_load_file_flag := 'N';
978 RETURN;
979 END IF;
980 ELSIF v_unit_attempt_status <> 'ENROLLED' THEN
981 IF v_upld_unit_not_enrolled = 'D' THEN
982 p_error_code := 'IGS_AS_ASD_UNIT_NOT_ENROLLED';
983 p_load_record_flag := 'N';
984 RETURN;
985 ELSIF v_upld_unit_not_enrolled = 'A' THEN
986 p_error_code := 'IGS_AS_ASA_UNIT_NOT_ENROLLED';
987 p_load_file_flag := 'N';
988 RETURN;
989 END IF;
990 END IF;
991 --
992 OPEN c_gsg_min_max (
993 v_grading_schema_cd,
994 v_gs_version_number
995 );
996 FETCH c_gsg_min_max INTO rec_gsg_min_max;
997 CLOSE c_gsg_min_max;
998 IF ((p_mark < rec_gsg_min_max.min_lower_mark_range) OR
999 (p_mark > rec_gsg_min_max.max_upper_mark_range)) THEN
1000 p_error_code := 'IGS_AS_MARK_INVALID';
1001 p_load_record_flag := 'N';
1002 RETURN;
1003 END IF;
1004 --
1005 -- Check if Submitted or Saved grade exists
1006 --
1007 OPEN c_suao (
1008 p_cal_type,
1009 p_ci_sequence_number,
1010 v_uoo_id
1011 );
1012 FETCH c_suao INTO v_mark,
1013 v_grade,
1014 v_finalized_outcome_ind;
1015 IF c_suao%FOUND THEN
1016 IF v_mark = p_mark
1017 AND v_grade = p_grade THEN
1018 p_error_code := 'IGS_AS_ASD_DUPLICATE_RECORD';
1019 p_load_record_flag := 'N';
1020 RETURN;
1021 ELSIF v_finalized_outcome_ind = 'Y' THEN
1022 IF v_upld_ug_sbmtd_grade_exist = 'D' THEN
1023 p_error_code := 'IGS_AS_ASD_GP_GRADE_SUBMITTED';
1024 p_load_record_flag := 'N';
1025 RETURN;
1026 ELSIF v_upld_ug_sbmtd_grade_exist = 'A' THEN
1027 p_error_code := 'IGS_AS_ASA_GP_GRADE_SUBMITTED';
1028 p_load_file_flag := 'N';
1029 RETURN;
1030 END IF;
1031 ELSIF v_finalized_outcome_ind = 'N' THEN
1032 IF v_upld_ug_saved_grade_exist = 'D' THEN
1033 p_error_code := 'IGS_AS_ASD_GP_GRADE_EXIST';
1034 p_load_record_flag := 'N';
1035 RETURN;
1036 ELSIF v_upld_ug_saved_grade_exist = 'A' THEN
1037 p_error_code := 'IGS_AS_ASA_GP_GRADE_EXIST';
1038 p_load_file_flag := 'N';
1039 RETURN;
1040 ELSIF v_upld_ug_saved_grade_exist = 'W' THEN
1041 p_error_code := 'IGS_AS_ASW_GP_GRADE_EXIST';
1042 END IF;
1043 END IF;
1044 CLOSE c_suao;
1045 END IF;
1046 --
1047 -- Grade Invalid
1048 --
1049 OPEN c_gsg (v_grading_schema_cd, v_gs_version_number);
1050 FETCH c_gsg INTO v_lower_mark_range,
1051 v_upper_mark_range,
1052 v_result_type;
1053 IF (c_gsg%NOTFOUND
1054 AND p_grade IS NOT NULL
1055 )
1056 OR (v_key_grade_derive_ind = 'N'
1057 AND p_grade IS NULL
1058 )
1059 OR (p_mark IS NULL
1060 AND p_grade IS NULL
1061 ) THEN
1062 IF v_upld_grade_invalid = 'D' THEN
1063 p_error_code := 'IGS_AS_ASD_GRADE_INVALID';
1064 p_load_record_flag := 'N';
1065 RETURN;
1066 ELSIF v_upld_grade_invalid = 'A' THEN
1067 p_error_code := 'IGS_AS_ASA_GRADE_INVALID';
1068 p_load_file_flag := 'N';
1069 RETURN;
1070 END IF;
1071 ELSE
1072 -- Mark Grade Combination Invalid
1073 IF v_key_allow_invalid_ind = 'N'
1074 AND p_mark IS NOT NULL THEN
1075 IF p_mark NOT BETWEEN v_lower_mark_range AND v_upper_mark_range THEN
1076 IF v_upld_mark_grade_invalid = 'D' THEN
1077 p_error_code := 'IGS_AS_ASD_MARK_GRADE_INVALID';
1078 p_load_record_flag := 'N';
1079 RETURN;
1080 ELSIF v_upld_mark_grade_invalid = 'A' THEN
1081 p_error_code := 'IGS_AS_ASA_MARK_GRADE_INVALID';
1082 p_load_file_flag := 'N';
1083 RETURN;
1084 ELSIF v_upld_mark_grade_invalid = 'W' THEN
1085 p_error_code := 'IGS_AS_ASW_MARK_GRADE_INVALID';
1086 END IF; --setup combination invalid
1087 END IF; --mark not between range
1088 END IF;
1089 END IF;
1090 --
1091 -- Derive Grade
1092 --
1093 IF v_key_grade_derive_ind = 'Y'
1094 AND p_mark IS NOT NULL
1095 AND p_grade IS NULL THEN
1096 OPEN c_gsg_derive (v_grading_schema_cd, v_gs_version_number);
1097 FETCH c_gsg_derive INTO p_grade;
1098 IF c_gsg_derive%NOTFOUND THEN
1099 p_error_code := 'IGS_AS_ASD_MARK_GRADE_INVALID';
1100 p_load_record_flag := 'N';
1101 RETURN;
1102 END IF;
1103 CLOSE c_gsg_derive;
1104 END IF;
1105 p_grading_schema_cd := v_grading_schema_cd;
1106 p_gs_version_number := v_gs_version_number;
1107 --
1108 -- Check Mark Entry Mandatory
1109 --
1110 IF v_key_mark_mndtry_ind = 'Y'
1111 AND p_mark IS NULL
1112 AND v_result_type <> 'INCOMP' THEN
1113 p_error_code := 'IGS_SS_AS_MARK_MANDATORY';
1114 p_load_record_flag := 'N';
1115 RETURN;
1116 END IF;
1117 --
1118 -- Check Grading Period Cohorts
1119 -- Cohorts are only considerd for Mid Term and Early Final
1120 --
1121 IF p_grading_period_cd <> 'FINAL' THEN
1122 OPEN c_gsh (v_uoo_id);
1123 FETCH c_gsh INTO v_submission_status;
1124 IF c_gsh%NOTFOUND
1125 OR v_submission_status = 'Incomplete' THEN
1126 OPEN c_gpc;
1127 FETCH c_gpc INTO v_dummy;
1128 IF c_gpc%NOTFOUND THEN
1129 p_error_code := 'IGS_AS_ASD_NO_COHORT_SETUP';
1130 p_load_record_flag := 'N';
1131 RETURN;
1132 END IF;
1133 CLOSE c_gpc;
1134 END IF;
1135 CLOSE c_gsh;
1136 END IF;
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 p_load_file_flag := 'N';
1140 p_load_record_flag := 'N';
1141 p_error_code := 'No Data Found - Others';
1142 END igs_as_ug_val_upld;
1143 END igs_as_adi_upld_ug_pkg;