[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ADI_UPLD_PR_PKG
Source
1 PACKAGE BODY igs_as_adi_upld_pr_pkg AS
2 /* $Header: IGSPR33B.pls 120.3 2006/05/29 06:27:30 ijeddy noship $ */
3 --
4 -- API to upload the Progression and Unit Outcomes from Web ADI that is used
5 -- to upload multiple outcomes for Progression and Unit together from a
6 -- single spreadsheet.
7 --
8 -- This routine calls the existing routines for Progression and Unit Grading
9 -- that validate and upload the data from Web ADI to corresponding OSS tables.
10 --
11 PROCEDURE prog_ug_process (
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER,
14 p_user_id IN NUMBER,
15 p_batch_datetime IN VARCHAR2,
16 p_grade_creation_method_type IN VARCHAR2,
17 p_delete_rows IN VARCHAR2 DEFAULT 'Y'
18 ) IS
19 --
20 p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
21 --
22 CURSOR error_pr IS
23 SELECT *
24 FROM igs_pr_spo_interface
25 WHERE user_id = p_user_id
26 AND trunc(batch_date) = trunc(p_batch_date)
27 AND error_code IS NOT NULL
28 AND NVL (progression_outcome_type, '--') <> '-';
29 --
30 BEGIN
31 --
32 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
33 fnd_log.string (
34 fnd_log.level_procedure,
35 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_process',
36 'Entered the prog_ug_process procedure with values:' ||
37 'p_user_id => ' || p_user_id || ';' ||
38 'p_batch_datetime => ' || p_batch_datetime || ';' ||
39 'p_grade_creation_method_type => ' || p_grade_creation_method_type || ';' ||
40 'p_delete_rows => ' || p_delete_rows
41 );
42 END IF;
43 --
44 -- Invoke Progression Outcome Upload API
45 --
46 progression_outcome_process (
47 errbuf,
48 retcode,
49 p_user_id,
50 p_batch_datetime,
51 p_grade_creation_method_type,
52 p_delete_rows
53 );
54 --
55 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
56 fnd_log.string (
57 fnd_log.level_statement,
58 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_process',
59 'Completed progression_outcome_process'
60 );
61 END IF;
62 --
63 -- Invoke Unit Grading Upload API
64 --
65 igs_as_adi_upld_ug_pkg.grading_period_grade_process (
66 errbuf,
67 retcode,
68 p_user_id,
69 p_batch_datetime,
70 p_grade_creation_method_type,
71 p_delete_rows
72 );
73 --
74 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
75 fnd_log.string (
76 fnd_log.level_statement,
77 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_process',
78 'Completed igs_as_adi_upld_ug_pkg.grading_period_grade_process'
79 );
80 END IF;
81 --
82 -- Transfer the errors from Progression Interface table to Unit Grading
83 -- Interface table as Unit Grading Interface table is used to publish the
84 -- errors back in the Web ADI spread sheet
85 --
86 FOR pr_error_rows IN error_pr LOOP
87 --
88 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
89 fnd_log.string (
90 fnd_log.level_statement,
91 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_process',
92 'There were errors in the progression upload, error are being transferred to the IGS_AS_UG_INTERFACE table'
93 );
94 END IF;
95 --
96 UPDATE igs_as_ug_interface
97 SET error_code = pr_error_rows.error_code
98 WHERE user_id = p_user_id
99 AND trunc(batch_date) = trunc(p_batch_date)
100 AND (alternate_code = pr_error_rows.progression_outcome_type
101 OR (alternate_code IS NULL
102 AND pr_error_rows.progression_outcome_type IS NULL)
103 )
104 AND (person_number = pr_error_rows.person_number
105 OR anonymous_id = pr_error_rows.anonymous_id)
106 AND course_cd = pr_error_rows.course_cd;
107 --
108 END LOOP;
109 --
110 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
111 fnd_log.string (
112 fnd_log.level_procedure,
113 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_process',
114 'Exiting the prog_ug_process procedure'
115 );
116 END IF;
117 --
118 END prog_ug_process;
119 --
120 -- API to upload the Progression, Unit and Assessment Item Outcomes from
121 -- Web ADI that is used to upload multiple outcomes for Progression, Unit
122 -- and Assessment Items together from a single spreadsheet.
123 --
124 -- This routine calls the existing routines for Progression, Unit Grading
125 -- and Assessment Item that validate and upload the data from Web ADI to
126 -- corresponding OSS tables.
127 --
128 PROCEDURE prog_ug_aio_process (
129 errbuf OUT NOCOPY VARCHAR2,
130 retcode OUT NOCOPY NUMBER,
131 p_user_id IN NUMBER,
132 p_batch_datetime IN VARCHAR2,
133 p_grade_creation_method_type IN VARCHAR2,
134 p_delete_rows IN VARCHAR2 DEFAULT 'Y'
135 ) IS
136 --
137 p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
138 --
139 CURSOR error_pr IS
140 SELECT *
141 FROM igs_pr_spo_interface
142 WHERE user_id = p_user_id
143 AND TRUNC (batch_date) = TRUNC (p_batch_date)
144 AND ERROR_CODE IS NOT NULL
145 AND NVL (progression_outcome_type, '--') <> '-';
146 --
147 CURSOR error_aio IS
148 SELECT *
149 FROM igs_as_aio_interface
150 WHERE user_id = p_user_id
151 AND trunc(batch_date) = trunc(p_batch_date)
152 AND ERROR_CODE IS NOT NULL
153 AND ass_id IS NOT NULL;
154 --
155 BEGIN
156 --
157 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
158 fnd_log.string (
159 fnd_log.level_procedure,
160 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
161 'Entered the prog_ug_aio_process procedure with values:' ||
162 'p_user_id => ' || p_user_id || ';' ||
163 'p_batch_datetime => ' || p_batch_datetime || ';' ||
164 'p_grade_creation_method_type => ' || p_grade_creation_method_type || ';' ||
165 'p_delete_rows => ' || p_delete_rows
166 );
167 END IF;
168 --
169 -- Invoke Progression Outcome Upload API
170 --
171 progression_outcome_process (
172 errbuf,
173 retcode,
174 p_user_id,
175 p_batch_datetime,
176 p_grade_creation_method_type,
177 p_delete_rows
178 );
179 --
180 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
181 fnd_log.string (
182 fnd_log.level_statement,
183 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
184 'Completed progression_outcome_process'
185 );
186 END IF;
187 --
188 -- Invoke Assessment Item Outcome Upload API
189 --
190 igs_as_adi_upld_aio_pkg.assessment_item_grade_process (
191 errbuf,
192 retcode,
193 p_user_id,
194 p_batch_datetime,
195 p_grade_creation_method_type,
196 p_delete_rows
197 );
198 --
199 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
200 fnd_log.string (
201 fnd_log.level_statement,
202 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
203 'Completed igs_as_adi_upld_aio_pkg.assessment_item_grade_process'
204 );
205 END IF;
206 --
207 -- Invoke Unit Grading Upload API
208 --
209 igs_as_adi_upld_ug_pkg.grading_period_grade_process (
210 errbuf,
211 retcode,
212 p_user_id,
213 p_batch_datetime,
214 p_grade_creation_method_type,
215 p_delete_rows
216 );
217 --
218 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
219 fnd_log.string (
220 fnd_log.level_statement,
221 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
222 'Completed igs_as_adi_upld_ug_pkg.grading_period_grade_process'
223 );
224 END IF;
225 --
226 -- Transfer the errors from Progression Interface table to Unit Grading
227 -- Interface table as Unit Grading Interface table is used to publish the
228 -- errors back in the Web ADI spread sheet
229 --
230 FOR pr_error_rows IN error_pr LOOP
231 --
232 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
233 fnd_log.string (
234 fnd_log.level_statement,
235 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
236 'There were errors in the progression upload, error are being transferred to the IGS_AS_UG_INTERFACE table'
237 );
238 END IF;
239 --
240 UPDATE igs_as_ug_interface
241 SET error_code = pr_error_rows.error_code
242 WHERE user_id = p_user_id
243 AND trunc(batch_date) = trunc(p_batch_date)
244 AND (alternate_code = pr_error_rows.progression_outcome_type
245 OR (alternate_code IS NULL
246 AND pr_error_rows.progression_outcome_type IS NULL)
247 )
248 AND (person_number = pr_error_rows.person_number
249 OR anonymous_id = pr_error_rows.anonymous_id)
250 AND course_cd = pr_error_rows.course_cd
251 AND grading_period_cd = '-';
252 --
253 END LOOP;
254 --
255 -- Transfer the errors from Assessment Item Interface table to Unit Grading
256 -- Interface table as Unit Grading Interface table is used to publish the
257 -- errors back in the Web ADI spread sheet
258 --
259 FOR aio_error_rows IN error_aio LOOP
260 --
261 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
262 fnd_log.string (
263 fnd_log.level_statement,
264 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
265 'There were errors in the assessment item upload, error are being transferred to the IGS_AS_UG_INTERFACE table'
266 );
267 END IF;
268 --
269 UPDATE igs_as_ug_interface
270 SET error_code = aio_error_rows.error_code
271 WHERE user_id = p_user_id
272 AND trunc(batch_date) = trunc(p_batch_date)
273 AND (person_number = aio_error_rows.person_number
274 OR anonymous_id = aio_error_rows.anonymous_id)
275 AND course_cd = aio_error_rows.course_cd
276 AND uoo_id = aio_error_rows.uoo_id
277 AND cal_type = aio_error_rows.cal_type
278 AND ci_sequence_number = aio_error_rows.ci_sequence_number
279 AND unit_class = aio_error_rows.unit_class
280 AND location_cd = aio_error_rows.location_cd
281 AND incomp_default_mark = aio_error_rows.ass_id
282 AND grading_period_cd = '-';
283 --
284 END LOOP;
285 --
286 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
287 fnd_log.string (
288 fnd_log.level_procedure,
289 'igs.plsql.igs_as_adi_upld_pr_pkg.prog_ug_aio_process',
290 'Exiting the prog_ug_aio_process procedure'
291 );
292 END IF;
293 --
294 END prog_ug_aio_process;
295 --
296 -- Validate the records before inserting into base table and call the table handlers
297 --
298 PROCEDURE progression_outcome_process (
299 errbuf OUT NOCOPY VARCHAR2,
300 retcode OUT NOCOPY NUMBER,
301 p_user_id IN NUMBER,
302 p_batch_datetime IN VARCHAR2,
303 p_grade_creation_method_type IN VARCHAR2,
304 p_delete_rows IN VARCHAR2 DEFAULT 'Y'
305 ) IS
306 --
307 v_person_id NUMBER (15);
308 v_prg_cal_type VARCHAR2 (10);
309 v_prg_ci_sequence_number NUMBER (6);
310 v_spo_sequence_number NUMBER (10);
311 v_error_code VARCHAR2 (30);
312 v_load_file_flag VARCHAR2 (1);
313 v_load_file_master VARCHAR2 (1) := 'Y';
314 v_load_record_flag VARCHAR2 (1);
315 v_rowid VARCHAR2 (25);
316 v_org_id NUMBER (15);
317 v_request_id NUMBER;
318 v_he_rowid VARCHAR2 (25);
319 v_hesa_en_susa_id NUMBER (6, 0);
320 --
321 CURSOR c_susa (
322 cp_person_id igs_he_en_susa.person_id%TYPE,
323 cp_course_cd igs_he_en_susa.course_cd%TYPE,
324 cp_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE,
325 cp_sequence_number igs_he_en_susa.sequence_number%TYPE,
326 cp_us_version_number igs_he_en_susa.us_version_number%TYPE
327 ) IS
328 SELECT ROWID,
329 hesa_en_susa_id,
330 person_id,
331 course_cd,
332 unit_set_cd,
333 us_version_number,
334 sequence_number,
335 new_he_entrant_cd,
336 term_time_accom,
337 disability_allow,
338 additional_sup_band,
339 sldd_discrete_prov,
340 study_mode,
341 study_location,
342 fte_perc_override,
343 franchising_activity,
344 completion_status,
345 good_stand_marker,
346 complete_pyr_study_cd,
347 credit_value_yop1,
348 credit_value_yop2,
349 credit_level_achieved1,
350 credit_level_achieved2,
351 credit_pt_achieved1,
352 credit_pt_achieved2,
353 credit_level1,
354 credit_level2,
355 grad_sch_grade,
356 mark,
357 teaching_inst1,
358 teaching_inst2,
359 pro_not_taught,
360 fundability_code,
361 fee_eligibility,
362 fee_band,
363 non_payment_reason,
364 student_fee,
365 fte_intensity,
366 calculated_fte,
367 fte_calc_type,
368 type_of_year,
369 credit_value_yop3,
370 credit_value_yop4,
371 credit_level_achieved3,
372 credit_level_achieved4,
373 credit_pt_achieved3,
374 credit_pt_achieved4,
375 credit_level3,
376 credit_level4,
377 additional_sup_cost,
378 enh_fund_elig_cd,
379 disadv_uplift_factor,
380 year_stu
381 FROM igs_he_en_susa
382 WHERE person_id = cp_person_id
383 AND course_cd = cp_course_cd
384 AND unit_set_cd = cp_unit_set_cd
385 AND sequence_number = cp_sequence_number
386 AND us_version_number = cp_us_version_number;
387 --
388 v_susa c_susa%ROWTYPE;
389 p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
390 v_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE;
391 v_us_version_number igs_he_en_susa.us_version_number%TYPE;
392 v_sequence_number igs_he_en_susa.sequence_number%TYPE;
393 --
394 CURSOR c_upload_outcome_spoi IS
395 SELECT spoi.user_id,
396 spoi.batch_date,
397 decode(spoi.person_number,'-',null,spoi.person_number) person_number,
398 decode(spoi.anonymous_id,'-',null,spoi.anonymous_id) anonymous_id,
399 spoi.course_cd,
400 spoi.progression_outcome_type,
401 spoi.comments,
402 spoi.error_code,
403 spoi.ROWID,
404 spoi.yop_grade,
405 spoi.yop_mark
406 FROM igs_pr_spo_interface spoi
407 WHERE spoi.user_id = p_user_id
408 AND trunc(spoi.batch_date) = trunc(p_batch_date)
409 AND NVL (spoi.progression_outcome_type, '--') <> '-';
410 --
411 CURSOR spo_exists (
412 cp_person_id NUMBER,
413 cp_course_cd VARCHAR2,
414 cp_prg_cal_type VARCHAR2,
415 cp_prg_ci_sequence_number NUMBER,
416 cp_progression_outcome_type VARCHAR2
417 ) IS
418 SELECT 'X' spo_exists
419 FROM igs_pr_stdnt_pr_ou_all
420 WHERE person_id = cp_person_id
421 AND course_cd = cp_course_cd
422 AND prg_cal_type = cp_prg_cal_type
423 AND prg_ci_sequence_number = cp_prg_ci_sequence_number
424 AND progression_outcome_type = cp_progression_outcome_type;
425 --
426 lspo_exists VARCHAR2 (1);
427 l_validuser VARCHAR2(1);
428 --
429 BEGIN
430 --
431
432 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
433
434 FOR v_spoi_rec IN c_upload_outcome_spoi LOOP
435 --
436 -- Check if the user is authorised to upload data.
437 -- Only Admin/valid Faculty can upload progression data to OSS.
438 --
439 l_validuser := igs_as_adi_upld_aio_pkg.isvaliduser (
440 v_spoi_rec.user_id
441 );
442 --
443 IF (l_validuser <> 'Y') THEN
444 UPDATE igs_pr_spo_interface
445 SET error_code = 'IGS_EN_PERSON_NO_RESP'
446 WHERE ROWID = v_spoi_rec.ROWID;
447 ELSE
448 v_error_code := NULL;
449 --
450 -- Call routine to upload for validate the particular row
451 --
452 igs_as_pr_val_upld (
453 v_spoi_rec.person_number,
454 v_spoi_rec.anonymous_id,
455 v_spoi_rec.course_cd,
456 v_spoi_rec.progression_outcome_type,
457 v_person_id,
458 v_prg_cal_type,
459 v_prg_ci_sequence_number,
460 v_error_code,
461 v_load_file_flag,
462 v_load_record_flag,
463 v_unit_set_cd,
464 v_us_version_number,
465 v_sequence_number,
466 v_spoi_rec.yop_mark,
467 v_spoi_rec.yop_grade
468 );
469 --
470 IF v_load_file_flag = 'N' THEN
471 v_load_file_master := 'N';
472 END IF;
473 --
474 IF (v_error_code IS NOT NULL) THEN
475 --
476 -- Update the interface record with the error code
477 --
478 UPDATE igs_pr_spo_interface
479 SET error_code = v_error_code
480 WHERE ROWID = v_spoi_rec.ROWID;
481 END IF;
482 END IF;
483 END LOOP;
484 --
485 COMMIT;
486 --
487 -- If any of the records set the
488 --
489 IF v_load_file_master = 'Y' THEN
490 FOR v_spoi_rec IN c_upload_outcome_spoi LOOP
491 igs_as_pr_val_upld (
492 v_spoi_rec.person_number,
493 v_spoi_rec.anonymous_id,
494 v_spoi_rec.course_cd,
495 v_spoi_rec.progression_outcome_type,
496 v_person_id,
497 v_prg_cal_type,
498 v_prg_ci_sequence_number,
499 v_error_code,
500 v_load_file_flag,
501 v_load_record_flag,
502 v_unit_set_cd,
503 v_us_version_number,
504 v_sequence_number,
505 v_spoi_rec.yop_mark,
506 v_spoi_rec.yop_grade
507 );
508 OPEN spo_exists (
509 v_person_id,
510 v_spoi_rec.course_cd,
511 v_prg_cal_type,
512 v_prg_ci_sequence_number,
513 v_spoi_rec.progression_outcome_type
514 );
515 FETCH spo_exists INTO lspo_exists;
516 CLOSE spo_exists;
517 IF ((v_load_record_flag = 'Y' OR v_load_record_flag = 'W')
518 AND lspo_exists IS NULL )
519 THEN
520 BEGIN
521 IF v_spoi_rec.progression_outcome_type IS NOT NULL THEN
522 SELECT igs_pr_spo_seq_num_s.NEXTVAL
523 INTO v_spo_sequence_number
524 FROM DUAL;
525 --
526 igs_pr_stdnt_pr_ou_pkg.insert_row (
527 x_rowid => v_rowid,
528 x_person_id => v_person_id,
529 x_course_cd => v_spoi_rec.course_cd,
530 x_sequence_number => v_spo_sequence_number,
531 x_prg_cal_type => v_prg_cal_type,
532 x_prg_ci_sequence_number => v_prg_ci_sequence_number,
533 x_rule_check_dt => NULL,
534 x_progression_rule_cat => NULL,
535 x_pra_sequence_number => NULL,
536 x_pro_sequence_number => NULL,
537 x_progression_outcome_type => v_spoi_rec.progression_outcome_type,
538 x_duration => NULL,
539 x_duration_type => NULL,
540 x_decision_status => 'PENDING',
541 x_decision_dt => NULL,
542 x_decision_org_unit_cd => NULL,
543 x_decision_ou_start_dt => NULL,
544 x_applied_dt => NULL,
545 x_show_cause_expiry_dt => NULL,
546 x_show_cause_dt => NULL,
547 x_show_cause_outcome_dt => NULL,
548 x_show_cause_outcome_type => NULL,
549 x_appeal_expiry_dt => NULL,
550 x_appeal_dt => NULL,
551 x_appeal_outcome_dt => NULL,
552 x_appeal_outcome_type => NULL,
553 x_encmb_course_group_cd => NULL,
554 x_restricted_enrolment_cp => NULL,
555 x_restricted_attendance_type => NULL,
556 x_comments => v_spoi_rec.comments,
557 x_show_cause_comments => NULL,
558 x_appeal_comments => NULL,
559 x_expiry_dt => NULL,
560 x_pro_pra_sequence_number => NULL,
561 x_mode => 'S',
562 x_org_id => v_org_id
563 );
564 END IF; --IF v_spoi_rec.progression_outcome_type IS NOT NULL THEN
565 EXCEPTION
566 WHEN OTHERS THEN
567 DECLARE
568 app_short_name VARCHAR2 (10);
569 message_name VARCHAR2 (100);
570 BEGIN
571 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
572 fnd_message.parse_encoded (
573 fnd_message.get_encoded,
574 app_short_name,
575 message_name
576 );
577 retcode := 2;
578 errbuf := message_name;
579 IF (errbuf IS NOT NULL) THEN
580 UPDATE igs_pr_spo_interface
581 SET error_code = errbuf
582 WHERE rowid = v_spoi_rec.rowid;
583 END IF;
584 END;
585 END;
586 --
587 IF (fnd_profile.VALUE ('CAREER_MODEL_ENABLED') = 'N'
588 AND fnd_profile.VALUE ('IGS_PS_PRENRL_YEAR_IND') = 'Y'
589 AND v_load_record_flag = 'Y'
590 AND (v_spoi_rec.yop_grade IS NOT NULL OR v_spoi_rec.yop_mark IS NOT NULL)
591 ) THEN
592 BEGIN
593 igs_he_en_susa_pkg.insert_row (
594 x_mode => 'S',
595 x_rowid => v_he_rowid,
596 x_hesa_en_susa_id => v_hesa_en_susa_id,
597 x_person_id => v_person_id,
598 x_course_cd => v_spoi_rec.course_cd,
599 x_unit_set_cd => v_unit_set_cd,
600 x_us_version_number => v_us_version_number,
601 x_sequence_number => v_sequence_number,
602 x_new_he_entrant_cd => NULL,
603 x_term_time_accom => NULL,
604 x_disability_allow => NULL,
605 x_additional_sup_band => NULL,
606 x_sldd_discrete_prov => NULL,
607 x_study_mode => NULL,
608 x_study_location => NULL,
609 x_fte_perc_override => NULL,
610 x_franchising_activity => NULL,
611 x_completion_status => NULL,
612 x_good_stand_marker => NULL,
613 x_complete_pyr_study_cd => NULL,
614 x_credit_value_yop1 => NULL,
615 x_credit_value_yop2 => NULL,
616 x_credit_level_achieved1 => NULL,
617 x_credit_level_achieved2 => NULL,
618 x_credit_pt_achieved1 => NULL,
619 x_credit_pt_achieved2 => NULL,
620 x_credit_level1 => NULL,
621 x_credit_level2 => NULL,
622 x_grad_sch_grade => v_spoi_rec.yop_grade,
623 x_mark => TO_NUMBER (v_spoi_rec.yop_mark),
624 x_teaching_inst1 => NULL,
625 x_teaching_inst2 => NULL,
626 x_pro_not_taught => NULL,
627 x_fundability_code => NULL,
628 x_fee_eligibility => NULL,
629 x_fee_band => NULL,
630 x_non_payment_reason => NULL,
631 x_student_fee => NULL,
632 x_fte_intensity => NULL,
633 x_calculated_fte => NULL,
634 x_fte_calc_type => NULL,
635 x_type_of_year => NULL,
636 x_credit_value_yop3 => NULL,
637 x_credit_value_yop4 => NULL,
638 x_credit_level_achieved3 => NULL,
639 x_credit_level_achieved4 => NULL,
640 x_credit_pt_achieved3 => NULL,
641 x_credit_pt_achieved4 => NULL,
642 x_credit_level3 => NULL,
643 x_credit_level4 => NULL,
644 x_additional_sup_cost => NULL,
645 x_enh_fund_elig_cd => NULL,
646 x_disadv_uplift_factor => NULL,
647 x_year_stu => NULL
648 );
649 EXCEPTION
650 WHEN OTHERS THEN
651 DECLARE
652 app_short_name VARCHAR2 (10);
653 message_name VARCHAR2 (100);
654 BEGIN
655 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
656 fnd_message.parse_encoded (
657 fnd_message.get_encoded,
658 app_short_name,
659 message_name
660 );
661 retcode := 2;
662 errbuf := message_name;
663 IF (errbuf IS NOT NULL) THEN
664 UPDATE igs_pr_spo_interface
665 SET error_code = errbuf
666 WHERE rowid = v_spoi_rec.rowid;
667 END IF;
668 END;
669 END;
670 END IF;
671 END IF; -- load record
672 --
673 IF v_load_record_flag = 'W' THEN
674 OPEN c_susa (v_person_id, v_spoi_rec.course_cd, v_unit_set_cd, v_sequence_number, v_us_version_number);
675 FETCH c_susa INTO v_susa;
676 BEGIN
677 igs_he_en_susa_pkg.update_row (
678 x_mode => 'S',
679 x_rowid => v_susa.ROWID,
680 x_hesa_en_susa_id => v_susa.hesa_en_susa_id,
681 x_person_id => v_susa.person_id,
682 x_course_cd => v_susa.course_cd,
683 x_unit_set_cd => v_susa.unit_set_cd,
684 x_us_version_number => v_susa.us_version_number,
685 x_sequence_number => v_susa.sequence_number,
686 x_new_he_entrant_cd => v_susa.new_he_entrant_cd,
687 x_term_time_accom => v_susa.term_time_accom,
688 x_disability_allow => v_susa.disability_allow,
689 x_additional_sup_band => v_susa.additional_sup_band,
690 x_sldd_discrete_prov => v_susa.sldd_discrete_prov,
691 x_study_mode => v_susa.study_mode,
692 x_study_location => v_susa.study_location,
693 x_fte_perc_override => v_susa.fte_perc_override,
694 x_franchising_activity => v_susa.franchising_activity,
695 x_completion_status => v_susa.completion_status,
696 x_good_stand_marker => v_susa.good_stand_marker,
697 x_complete_pyr_study_cd => v_susa.complete_pyr_study_cd,
698 x_credit_value_yop1 => v_susa.credit_value_yop1,
699 x_credit_value_yop2 => v_susa.credit_value_yop2,
700 x_credit_level_achieved1 => v_susa.credit_level_achieved1,
701 x_credit_level_achieved2 => v_susa.credit_level_achieved2,
702 x_credit_pt_achieved1 => v_susa.credit_pt_achieved1,
703 x_credit_pt_achieved2 => v_susa.credit_pt_achieved2,
704 x_credit_level1 => v_susa.credit_level1,
705 x_credit_level2 => v_susa.credit_level2,
706 x_grad_sch_grade => v_spoi_rec.yop_grade,
707 x_mark => TO_NUMBER (v_spoi_rec.yop_mark),
708 x_teaching_inst1 => v_susa.teaching_inst1,
709 x_teaching_inst2 => v_susa.teaching_inst2,
710 x_pro_not_taught => v_susa.pro_not_taught,
711 x_fundability_code => v_susa.fundability_code,
712 x_fee_eligibility => v_susa.fee_eligibility,
713 x_fee_band => v_susa.fee_band,
714 x_non_payment_reason => v_susa.non_payment_reason,
715 x_student_fee => v_susa.student_fee,
716 x_fte_intensity => v_susa.fte_intensity,
717 x_calculated_fte => v_susa.calculated_fte,
718 x_fte_calc_type => v_susa.fte_calc_type,
719 x_type_of_year => v_susa.type_of_year,
720 x_credit_value_yop3 => v_susa.credit_value_yop3,
721 x_credit_value_yop4 => v_susa.credit_value_yop4,
722 x_credit_level_achieved3 => v_susa.credit_level_achieved3,
723 x_credit_level_achieved4 => v_susa.credit_level_achieved4,
724 x_credit_pt_achieved3 => v_susa.credit_pt_achieved3,
725 x_credit_pt_achieved4 => v_susa.credit_pt_achieved4,
726 x_credit_level3 => v_susa.credit_level3,
727 x_credit_level4 => v_susa.credit_level4,
728 x_additional_sup_cost => v_susa.additional_sup_cost,
729 x_enh_fund_elig_cd => v_susa.enh_fund_elig_cd,
730 x_disadv_uplift_factor => v_susa.disadv_uplift_factor,
731 x_year_stu => v_susa.year_stu
732 );
733 EXCEPTION
734 WHEN OTHERS THEN
735 DECLARE
736 app_short_name VARCHAR2 (10);
737 message_name VARCHAR2 (100);
738 BEGIN
739 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
740 fnd_message.parse_encoded (
741 fnd_message.get_encoded,
742 app_short_name,
743 message_name
744 );
745 retcode := 2;
746 errbuf := message_name;
747 IF (errbuf IS NOT NULL) THEN
748 UPDATE igs_pr_spo_interface
749 SET error_code = errbuf
750 WHERE rowid = v_spoi_rec.rowid;
751 END IF;
752 END;
753 END;
754 CLOSE c_susa;
755 END IF;
756 END LOOP;
757 END IF; -- Load File
758 --
759 -- Call Reports to generate the error report with parameters
760 -- then delete the records from by calling after report trigger.
761 --
762 /* Extracting WebADI from Concurrent Program LOV */
763 IF p_grade_creation_method_type <> 'WEBADI' THEN
764 v_request_id :=
765 fnd_request.submit_request ('IGS', 'IGSPRS04', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
766 END IF;
767 IF v_request_id = 0 THEN
768 RAISE fnd_api.g_exc_unexpected_error;
769 END IF;
770 COMMIT;
771 --
772 END progression_outcome_process;
773 --
774 -- Validate single Grading Period record from the interface table
775 -- before being uploaded.
776 -- This validation is called from the interface table import routine,
777 -- and also the ADI pre-validation functionality.
778 --
779 PROCEDURE igs_as_pr_val_upld (
780 p_person_number IN VARCHAR2,
781 p_anonymous_id IN VARCHAR2,
782 p_course_cd IN VARCHAR2,
783 p_progression_outcome_type IN VARCHAR2,
784 p_person_id OUT NOCOPY NUMBER,
785 p_prg_cal_type OUT NOCOPY VARCHAR2,
786 p_prg_ci_sequence_number OUT NOCOPY NUMBER,
787 p_error_code OUT NOCOPY VARCHAR2,
788 p_load_file_flag OUT NOCOPY VARCHAR2,
789 p_load_record_flag OUT NOCOPY VARCHAR2,
790 p_unit_set_cd OUT NOCOPY igs_as_su_setatmpt.unit_set_cd%TYPE,
791 p_us_version_number OUT NOCOPY igs_as_su_setatmpt.us_version_number%TYPE,
792 p_sequence_number OUT NOCOPY igs_he_en_susa.sequence_number%TYPE,
793 p_mark IN NUMBER,
794 p_grade IN OUT NOCOPY VARCHAR2
795 ) IS
796 --
797 v_course_attempt_status VARCHAR2 (30);
798 v_version_number NUMBER (3);
799 v_s_progression_outcome_type VARCHAR2 (30);
800 v_upld_person_no_exist VARCHAR2 (1);
801 v_upld_crs_not_enrolled VARCHAR2 (1);
802 v_dummy VARCHAR2 (1);
803 v_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
804 v_gs_version_number igs_as_grd_sch_grade.version_number%TYPE;
805 --
806 -- Get Grading Entry Configuration
807 --
808 CURSOR c_ec IS
809 SELECT ec.upld_person_no_exist,
810 ec.upld_crs_not_enrolled
811 FROM igs_as_entry_conf ec
812 WHERE s_control_num = 1;
813 --
814 -- Get Person ID based on Anonymous ID - Assumes UK program based ID's
815 --
816 CURSOR c_aip IS
817 SELECT aip.person_id
818 FROM igs_as_anon_id_ps aip
819 WHERE aip.anonymous_id = p_anonymous_id
820 AND aip.course_cd = p_course_cd;
821 --
822 -- Get Person ID based on Person Number
823 --
824 CURSOR c_p IS
825 SELECT p.party_id
826 FROM hz_parties p
827 WHERE p.party_number = p_person_number;
828 --
829 -- Get Student Program Attempt details
830 --
831 CURSOR c_spa (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE) IS
832 SELECT spa.course_attempt_status,
833 spa.version_number
834 FROM igs_en_stdnt_ps_att_all spa
835 WHERE spa.person_id = cp_person_id
836 AND spa.course_cd = p_course_cd;
837 --
838 -- Get the System Progession Outcome Type
839 --
840 CURSOR c_pot IS
841 SELECT pot.s_progression_outcome_type
842 FROM igs_pr_ou_type pot
843 WHERE pot.progression_outcome_type = p_progression_outcome_type;
844 --
845 -- Check Student Progression Outcome doesn't already exist
846 --
847 CURSOR c_spo (
848 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
849 cp_prg_cal_type igs_ca_inst.cal_type%TYPE,
850 cp_prg_ci_sequence_number igs_ca_inst.sequence_number%TYPE
851 ) IS
852 SELECT 'X'
853 FROM igs_pr_stdnt_pr_ou spo
854 WHERE spo.person_id = cp_person_id
855 AND spo.course_cd = p_course_cd
856 AND spo.progression_outcome_type = p_progression_outcome_type
857 AND spo.decision_status IN ('PENDING', 'APPROVED')
858 AND spo.prg_cal_type = cp_prg_cal_type
859 AND spo.prg_ci_sequence_number = cp_prg_ci_sequence_number;
860 --
861 -- Determine the current progression period
862 --
863 CURSOR c_scpc (cp_version_number igs_ps_ver.version_number%TYPE) IS
864 SELECT 'X'
865 FROM igs_pr_s_crv_prg_cal scpc
866 WHERE scpc.course_cd = p_course_cd
867 AND scpc.version_number = cp_version_number;
868
869 CURSOR c_scpc_ci (
870 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
871 cp_version_number igs_ps_ver.version_number%TYPE
872 ) IS
873 SELECT ci.cal_type,
874 ci.sequence_number
875 FROM igs_pr_s_crv_prg_cal scpc,
876 igs_ca_inst ci,
877 igs_ca_stat cs
878 WHERE scpc.course_cd = p_course_cd
879 AND scpc.version_number = cp_version_number
880 AND ci.cal_type = scpc.prg_cal_type
881 AND ci.cal_status = cs.cal_status
882 AND cs.s_cal_status = 'ACTIVE'
883 AND ci.start_dt < SYSDATE
884 --AND ci.end_dt = (SELECT MAX(ci.end_dt)
885 AND EXISTS ( SELECT 'X'
886 FROM igs_ca_inst_rel cir,
887 igs_en_su_attempt sua
888 WHERE cir.sup_cal_type = ci.cal_type
889 AND cir.sup_ci_sequence_number = ci.sequence_number
890 AND cir.sub_cal_type = sua.cal_type
891 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
892 AND sua.person_id = cp_person_id
893 AND sua.course_cd = p_course_cd
894 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
895 ORDER BY ci.end_dt DESC;
896 --
897 --
898 --
899 CURSOR c_sopc (cp_version_number igs_ps_ver.version_number%TYPE) IS
900 SELECT 'X'
901 FROM igs_pr_s_ou_prg_cal sopc
902 WHERE igs_pr_gen_001.prgp_get_crv_cmt (p_course_cd, cp_version_number, sopc.org_unit_cd, sopc.ou_start_dt) = 'Y';
903 --
904 --
905 --
906 CURSOR c_sopc_ci (
907 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
908 cp_version_number igs_ps_ver.version_number%TYPE
909 ) IS
910 SELECT ci.cal_type,
911 ci.sequence_number
912 FROM igs_pr_s_ou_prg_cal sopc,
913 igs_ca_inst ci,
914 igs_ca_stat cs
915 WHERE igs_pr_gen_001.prgp_get_crv_cmt (p_course_cd, cp_version_number, sopc.org_unit_cd, sopc.ou_start_dt) = 'Y'
916 AND ci.cal_type = sopc.prg_cal_type
917 AND ci.cal_status = cs.cal_status
918 AND cs.s_cal_status = 'ACTIVE'
919 AND ci.start_dt < SYSDATE
920 --AND ci.end_dt = (SELECT MAX(ci.end_dt)
921 AND EXISTS ( SELECT 'X'
922 FROM igs_ca_inst_rel cir,
923 igs_en_su_attempt sua
924 WHERE cir.sup_cal_type = ci.cal_type
925 AND cir.sup_ci_sequence_number = ci.sequence_number
926 AND cir.sub_cal_type = sua.cal_type
927 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
928 AND sua.person_id = cp_person_id
929 AND sua.course_cd = p_course_cd
930 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
931 ORDER BY ci.end_dt DESC;
932 --
933 --
934 --
935 CURSOR c_spc_ci (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE) IS
936 SELECT ci.cal_type,
937 ci.sequence_number
938 FROM igs_pr_s_prg_cal spc,
939 igs_ca_inst ci,
940 igs_ca_stat cs
941 WHERE spc.s_control_num = 1
942 AND ci.cal_type = spc.prg_cal_type
943 AND ci.cal_status = cs.cal_status
944 AND cs.s_cal_status = 'ACTIVE'
945 AND ci.start_dt < SYSDATE
946 --AND ci.end_dt = (SELECT MAX(ci.end_dt)
947 AND EXISTS ( SELECT 'X'
948 FROM igs_ca_inst_rel cir,
949 igs_en_su_attempt sua
950 WHERE cir.sup_cal_type = ci.cal_type
951 AND cir.sup_ci_sequence_number = ci.sequence_number
952 AND cir.sub_cal_type = sua.cal_type
953 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
954 AND sua.person_id = cp_person_id
955 AND sua.course_cd = p_course_cd
956 AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
957 ORDER BY ci.end_dt DESC;
958 --
959 --
960 --
961 CURSOR c_grd_sch (
962 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
963 cp_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE
964 ) IS
965 SELECT hpoous.grading_schema_cd,
966 hpoous.gs_version_number,
967 yop.unit_set_cd,
968 yop.us_version_number,
969 yop.sequence_number
970 FROM igs_en_susa_year_v yop,
971 igs_en_stdnt_ps_att_all spa,
972 igs_ps_ofr_opt_all coo,
973 igs_he_poous_all hpoous
974 WHERE yop.person_id = cp_person_id
975 AND yop.course_cd = cp_course_cd
976 AND yop.completion_dt IS NULL
977 AND yop.end_dt IS NULL
978 AND yop.person_id = spa.person_id
979 AND yop.course_cd = spa.course_cd
980 AND spa.coo_id = coo.coo_id
981 AND hpoous.unit_set_cd = yop.unit_set_cd
982 AND hpoous.us_version_number = yop.us_version_number
983 AND hpoous.course_cd = coo.course_cd
984 AND hpoous.crv_version_number = coo.version_number
985 AND hpoous.cal_type = coo.cal_type
986 AND hpoous.location_cd = coo.location_cd
987 AND hpoous.attendance_type = coo.attendance_type
988 AND hpoous.attendance_mode = coo.attendance_mode;
989 --
990 -- Cursor to check if the grade entered in part of the grading schema
991 --
992 CURSOR cur_grade_exists (
993 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
994 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE,
995 cp_grade igs_as_grd_sch_grade.grade%TYPE
996 ) IS
997 SELECT 'Y' grade_found
998 FROM igs_as_grd_sch_grade gsg
999 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
1000 AND gsg.version_number = cp_gs_version_number
1001 AND gsg.grade = cp_grade;
1002 --
1003 rec_grade_exists cur_grade_exists%ROWTYPE;
1004 --
1005 --
1006 --
1007 CURSOR c_calc_grade (
1008 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
1009 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE,
1010 cp_marks igs_as_grd_sch_grade.lower_mark_range%TYPE
1011 ) IS
1012 SELECT grade
1013 FROM igs_as_grd_sch_grade gsg
1014 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
1015 AND gsg.version_number = cp_gs_version_number
1016 AND system_only_ind = 'N'
1017 AND cp_marks BETWEEN gsg.lower_mark_range AND gsg.upper_mark_range;
1018 --
1019 rec_calc_grade c_calc_grade%ROWTYPE;
1020 --
1021 -- Cursor to fix the issue progression outcome uploading incorrect marks and grades
1022 --
1023 CURSOR c_gsg_min_max (
1024 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
1025 cp_gs_version_number igs_as_grd_sch_grade.version_number%TYPE
1026 ) IS
1027 SELECT MIN (gsg.lower_mark_range) min_lower_mark_range,
1028 MAX (gsg.upper_mark_range) max_upper_mark_range
1029 FROM igs_as_grd_sch_grade gsg
1030 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
1031 AND gsg.version_number = cp_gs_version_number;
1032 rec_gsg_min_max c_gsg_min_max%ROWTYPE;
1033 --
1034 BEGIN
1035 -- Initialise flags
1036 p_load_file_flag := 'Y';
1037 p_load_record_flag := 'Y';
1038 --
1039 -- Get Grade Entry Configuration
1040 --
1041 OPEN c_ec;
1042 FETCH c_ec INTO v_upld_person_no_exist,
1043 v_upld_crs_not_enrolled;
1044 CLOSE c_ec;
1045 --
1046 -- Get Person ID from Person Number and Anonymous ID
1047 --
1048 IF p_person_number IS NULL
1049 AND p_anonymous_id IS NULL THEN
1050 IF v_upld_person_no_exist = 'D' THEN
1051 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
1052 p_load_record_flag := 'N';
1053 RETURN;
1054 ELSIF v_upld_person_no_exist = 'A' THEN
1055 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
1056 p_load_file_flag := 'N';
1057 RETURN;
1058 END IF;
1059 ELSIF p_person_number IS NOT NULL
1060 AND p_anonymous_id IS NOT NULL THEN
1061 p_error_code := 'IGS_AS_ASD_PER_ANON_BOTH_EXIST';
1062 p_load_record_flag := 'N';
1063 RETURN;
1064 ELSIF p_person_number IS NULL
1065 AND p_anonymous_id IS NOT NULL THEN
1066 -- Get the Person ID based on the Anonymous ID
1067 OPEN c_aip;
1068 FETCH c_aip INTO p_person_id;
1069 IF c_aip%NOTFOUND THEN
1070 IF v_upld_person_no_exist = 'D' THEN
1071 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
1072 p_load_record_flag := 'N';
1073 RETURN;
1074 ELSIF v_upld_person_no_exist = 'A' THEN
1075 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
1076 p_load_file_flag := 'N';
1077 RETURN;
1078 END IF;
1079 END IF;
1080 CLOSE c_aip;
1081 ELSIF p_person_number IS NOT NULL
1082 AND p_anonymous_id IS NULL THEN
1083 --
1084 -- Get the Person ID based on the Person Number
1085 --
1086 OPEN c_p;
1087 FETCH c_p INTO p_person_id;
1088 IF c_p%NOTFOUND THEN
1089 IF v_upld_person_no_exist = 'D' THEN
1090 p_error_code := 'IGS_AS_ASD_AN_NO_PERSON_EXIST';
1091 p_load_record_flag := 'N';
1092 RETURN;
1093 ELSIF v_upld_person_no_exist = 'A' THEN
1094 p_error_code := 'IGS_AS_ASA_AN_NO_PERSON_EXIST';
1095 p_load_file_flag := 'N';
1096 RETURN;
1097 END IF;
1098 END IF;
1099 CLOSE c_p;
1100 END IF;
1101 --
1102 -- Check for a valid Student Program Attempt
1103 --
1104 OPEN c_spa (p_person_id);
1105 FETCH c_spa INTO v_course_attempt_status,
1106 v_version_number;
1107 IF c_spa%NOTFOUND THEN
1108 p_error_code := 'IGS_AS_ASA_PR_NO_PRGRM_ATTEMPT';
1109 p_load_file_flag := 'N';
1110 RETURN;
1111 END IF;
1112 CLOSE c_spa;
1113 IF v_course_attempt_status NOT IN ('ENROLLED', 'INACTIVE') THEN
1114 IF v_upld_crs_not_enrolled = 'D' THEN
1115 p_error_code := 'IGS_AS_ASD_COURSE_NOT_ENROLLED';
1116 p_load_record_flag := 'N';
1117 RETURN;
1118 ELSIF v_upld_crs_not_enrolled = 'A' THEN
1119 p_error_code := 'IGS_AS_ASA_COURSE_NOT_ENROLLED';
1120 p_load_file_flag := 'N';
1121 RETURN;
1122 END IF;
1123 END IF;
1124 --
1125 -- Validate that progression outcome type is valid.
1126 --
1127 IF p_progression_outcome_type IS NOT NULL THEN
1128 OPEN c_pot;
1129 FETCH c_pot INTO v_s_progression_outcome_type;
1130 IF c_pot%NOTFOUND THEN
1131 p_error_code := 'IGS_AS_ASA_PR_NOT_VALID';
1132 p_load_file_flag := 'N';
1133 RETURN;
1134 ELSIF v_s_progression_outcome_type NOT IN ('NOPENALTY', 'ADVANCE', 'REPEATYR',
1135 'MANUAL', 'EXCLUSION', 'EXPULSION') THEN
1136 p_error_code := 'IGS_AS_ASA_PR_TYPE_INVALID';
1137 p_load_file_flag := 'N';
1138 RETURN;
1139 END IF;
1140 CLOSE c_pot;
1141 --
1142 -- Check for Progression Calendar Stream configuration at Program
1143 -- Version level
1144 --
1145 OPEN c_scpc (v_version_number);
1146 FETCH c_scpc INTO v_dummy;
1147 IF c_scpc%FOUND THEN
1148 CLOSE c_scpc;
1149 -- Get Matching Progression Calendar at Program Version level
1150 OPEN c_scpc_ci (p_person_id, v_version_number);
1151 FETCH c_scpc_ci INTO p_prg_cal_type,
1152 p_prg_ci_sequence_number;
1153 IF c_scpc_ci%NOTFOUND THEN
1154 p_error_code := 'IGS_AS_ASA_PR_CLNDR_NOT_FOUND';
1155 p_load_record_flag := 'N';
1156 RETURN;
1157 END IF;
1158 CLOSE c_scpc_ci;
1159 ELSE
1160 --
1161 -- Check for Progression Calendar Stream configuration at Org
1162 -- Unit level
1163 --
1164 OPEN c_sopc (v_version_number);
1165 FETCH c_sopc INTO v_dummy;
1166 IF c_sopc%FOUND THEN
1167 CLOSE c_sopc;
1168 -- Get Matching Progression Calendar at Org Unit level
1169 OPEN c_sopc_ci (p_person_id, v_version_number);
1170 FETCH c_sopc_ci INTO p_prg_cal_type,
1171 p_prg_ci_sequence_number;
1172 IF c_sopc_ci%NOTFOUND THEN
1173 p_error_code := 'IGS_AS_ASA_PR_CLNDR_NOT_FOUND';
1174 p_load_record_flag := 'N';
1175 RETURN;
1176 END IF;
1177 CLOSE c_sopc_ci;
1178 ELSE
1179 -- Get matching Progression Calendar at Institution level
1180 OPEN c_spc_ci (p_person_id);
1181 FETCH c_spc_ci INTO p_prg_cal_type,
1182 p_prg_ci_sequence_number;
1183 IF c_spc_ci%NOTFOUND THEN
1184 p_error_code := 'IGS_AS_ASA_PR_CLNDR_NOT_FOUND';
1185 p_load_record_flag := 'N';
1186 RETURN;
1187 END IF;
1188 CLOSE c_spc_ci;
1189 END IF;
1190 END IF;
1191 --
1192 --Check that person doesn't already have an outcome of this type.
1193 --
1194 IF (fnd_profile.VALUE ('CAREER_MODEL_ENABLED') = 'Y') THEN
1195 OPEN c_spo (p_person_id, p_prg_cal_type, p_prg_ci_sequence_number);
1196 FETCH c_spo INTO v_dummy;
1197 IF c_spo%FOUND THEN
1198 CLOSE c_spo;
1199 p_error_code := 'IGS_AS_ASA_PR_OUTCOME_EXIST';
1200 p_load_record_flag := 'N';
1201 RETURN;
1202 END IF;
1203 END IF;
1204 END IF;
1205 --
1206 -- Get the Grading schema Grades
1207 --
1208 OPEN c_grd_sch (p_person_id, p_course_cd);
1209 FETCH c_grd_sch INTO v_grading_schema_cd,
1210 v_gs_version_number,
1211 p_unit_set_cd,
1212 p_us_version_number,
1213 p_sequence_number;
1214 CLOSE c_grd_sch;
1215 --
1216 --
1217 -- Validate that the grade entered by the user is part of the grading schema
1218 --
1219 IF p_grade IS NOT NULL THEN
1220 OPEN cur_grade_exists (v_grading_schema_cd, v_gs_version_number, p_grade);
1221 FETCH cur_grade_exists INTO rec_grade_exists;
1222 IF (cur_grade_exists%NOTFOUND) THEN
1223 p_error_code := 'IGS_AS_GRADE_INVALID';
1224 END IF;
1225 CLOSE cur_grade_exists;
1226
1227 END IF;
1228
1229 --
1230 -- Determine action if record already exists
1231 --
1232 BEGIN
1233 IF igs_he_en_susa_pkg.get_uk_for_validation (
1234 x_person_id => p_person_id,
1235 x_course_cd => p_course_cd,
1236 x_unit_set_cd => p_unit_set_cd,
1237 x_sequence_number => p_sequence_number
1238 ) THEN
1239 p_load_record_flag := 'W';
1240 END IF;
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 NULL;
1244 END;
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 p_load_file_flag := 'N';
1248 p_load_record_flag := 'N';
1249 p_error_code := 'No Data Found - Others';
1250 END igs_as_pr_val_upld;
1251 END igs_as_adi_upld_pr_pkg;