[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_HESA_PKG
Source
1 PACKAGE BODY igs_en_hesa_pkg AS
2 /* $Header: IGSHE16B.pls 120.1 2006/02/07 14:53:06 jbaber noship $ */
3
4 ---------------------------------------------------------------------
5 -- Change History
6 -- Who When What
7 --rshergil 23-Jan-2002 Created program for pre-enrollment process
8 -- relating to HESA details
9 -- 1. UK Statistics - SPA
10 -- 2. Student Unit Set Attempt HESA Details
11 --sbaliga 16-Apr-2002 Modified HESA_SUSA_ENR procedure
12 -- corresponding to changes in IGS_HE_POOUS_ALL
13 -- and IGS_HE_EN_SUSA table
14 --smaddali 14-may-2002 added new parameter p_old_sequence_number and
15 -- renamed p_sequence_number to p_new_sequence_number in procedure hesa_susa_enr for bug#2350629
16 --Bayadav 22-OCT-2002 Included four new columns qual_aim_subj1,qual_aim_subj2,qual_aim_subj3,qual_aim_proportion
17 -- in IGS_HE_ST_SPA_ALL table as a part of bug 2636897
18 --Bayadav 22-OCT-2002 Included one new column type_of_year and corresponding validation |
19 -- in IGS_HE_EN_SUSA table as a part of bug 2636897 |
20 --knaraset 14-Nov-2002 Added the functions validate_program_aim,val_sub_qual_proportion,
21 -- val_highest_qual_entry,get_unit_set_cat,check_teach_inst,check_grading_sch_grade
22 -- as part of Build TD Legacy SPA Bug 2661533
23 --smaddali modified cursor cur_hqual_grade for bug 2730388
24 --pmarada 13-feb-03 Modified the validate_program_aim procedure
25 -- added the closed_ind condition in the cursor where clause, bug 2801518
26 --pmarada 20-aug-2003 Added code to derive the student instance number field value as per
27 -- HECR008-Alphsnumeric student instance number Bug 2893557
28 --gmahesa 13-Nov-2003 Bug No: 3227107 address changes, Modified gc_addr_rec cursor to select records with active status.
29 --smaddali 08-Jan-2004 Bug#3291399 , modified procedure hesa_susa_enr for modifying logic for copying fields
30 --jbaber 24-Nov-2004 Bug No: 3949136. Modified hesa_stats_enr procedure to prevent enrollment HESA data from being overwritten
31 --jbaber 16-Jan-2006 Updated igs_he_st_spa_all_pkg call to include exclud_flag column for HE305
32 --------------------------------------------------------------------
33
34 PROCEDURE hesa_stats_enr(
35 p_person_id IN NUMBER,
36 p_course_cd IN VARCHAR2,
37 p_crv_version_number IN NUMBER,
38 p_message OUT NOCOPY VARCHAR2,
39 p_status OUT NOCOPY NUMBER)
40
41 AS
42 gv_addr_rec igs_pe_addr_v.postal_code%TYPE;
43
44 CURSOR gc_stats_rec IS
45 SELECT hesa_st_spa_id,
46 course_cd,
47 version_number,
48 person_id,
49 fe_student_marker,
50 domicile_cd,
51 inst_last_attended,
52 year_left_last_inst,
53 highest_qual_on_entry,
54 date_qual_on_entry_calc,
55 a_level_point_score,
56 highers_points_scores,
57 occupation_code,
58 commencement_dt,
59 special_student,
60 student_qual_aim,
61 student_fe_qual_aim,
62 teacher_train_prog_id,
63 itt_phase,
64 bilingual_itt_marker,
65 teaching_qual_gain_sector,
66 teaching_qual_gain_subj1,
67 teaching_qual_gain_subj2,
68 teaching_qual_gain_subj3,
69 student_inst_number,
70 hesa_return_name,
71 hesa_return_id,
72 hesa_submission_name,
73 associate_ucas_number,
74 associate_scott_cand,
75 associate_teach_ref_num,
76 associate_nhs_reg_num,
77 itt_prog_outcome,
78 nhs_funding_source,
79 ufi_place,
80 postcode,
81 social_class_ind,
82 destination,
83 occcode,
84 total_ucas_tariff,
85 nhs_employer,
86 return_type,
87 qual_aim_subj1,
88 qual_aim_subj2,
89 qual_aim_subj3,
90 qual_aim_proportion,
91 dependants_cd,
92 implied_fund_rate,
93 gov_initiatives_cd,
94 units_for_qual,
95 disadv_uplift_elig_cd,
96 franch_partner_cd,
97 units_completed,
98 franch_out_arr_cd,
99 employer_role_cd,
100 disadv_uplift_factor,
101 enh_fund_elig_cd,
102 creation_date,
103 created_by,
104 last_update_date,
105 last_updated_by,
106 last_update_login
107 FROM igs_he_st_spa
108 WHERE person_id = p_person_id
109 AND course_cd = p_course_cd
110 AND version_number = p_crv_version_number;
111
112 gv_stats_rec gc_stats_rec%ROWTYPE;
113
114 CURSOR gc_adm_rec IS
115 SELECT a.hesa_sequence_id hesa_sequence_id,
116 a.person_id person_id,
117 a.admission_appl_number admission_appl_number,
118 a.nominated_course_cd nominated_course_cd,
119 a.sequence_number sequence_number,
120 a.occupation_cd occupation_cd,
121 a.domicile_cd domicile_cd,
122 a.social_class_cd social_class_cd,
123 a.special_student_cd special_student_cd,
124 a.creation_date creation_date,
125 a.created_by created_by,
126 a.last_update_date last_update_date,
127 a.last_updated_by last_updated_by,
128 a.last_update_login last_update_login
129 FROM igs_he_ad_dtl a,
130 igs_ad_ps_appl_inst b,
131 igs_en_stdnt_ps_att c
132 WHERE a.person_id = b.person_id
133 AND a.admission_appl_number = b.admission_appl_number
134 AND a.nominated_course_cd = b.nominated_course_cd
135 AND a.sequence_number = b.sequence_number
136 AND b.admission_appl_number = c.adm_admission_appl_number
137 AND b.person_id = c.person_id
138 AND b.nominated_course_cd = c.adm_nominated_course_cd
139 AND b.sequence_number = c.adm_sequence_number
140 AND c.person_id = p_person_id
141 AND c.course_cd = p_course_cd
142 AND c.version_number = p_crv_version_number;
143
144 gv_adm_rec gc_adm_rec%ROWTYPE;
145
146 CURSOR gc_addr_rec IS
147 SELECT a.postal_code postal_code
148 FROM igs_pe_addr_v a
149 WHERE a.person_id = p_person_id
150 AND (a.status = 'A' AND SYSDATE BETWEEN NVL(a.start_dt,SYSDATE) AND NVL(a.end_dt,SYSDATE))
151 AND (EXISTS(SELECT 'X'
152 FROM igs_pe_partysiteuse_v b
153 WHERE a.party_site_id = b.party_site_id
154 AND b.site_use_type = 'HOME'
155 AND b.active='Y')
156 OR a.correspondence = 'Y');
157
158
159 CURSOR gc_upd_stats_rec IS
160 SELECT ROWID
161 FROM igs_he_st_spa
162 WHERE person_id = p_person_id
163 AND course_cd = p_course_cd
164 AND version_number = p_crv_version_number;
165
166 -- Get the all instance numbers for the student
167 CURSOR cur_std_inst_num(cp_person_id igs_he_st_spa.person_id%TYPE) IS
168 SELECT student_inst_number
169 FROM igs_he_st_spa
170 WHERE person_id = cp_person_id;
171
172 l_std_inst_num NUMBER;
173
174 --Procedure inserts into IGS_HE_ST_SPA_ALL table
175
176 PROCEDURE cr_he_st_spa_rec ( p_person_id igs_he_st_spa.person_id%TYPE,
177 p_course_cd igs_he_st_spa.course_cd%TYPE,
178 p_crv_version_number igs_he_st_spa.version_number%TYPE) IS
179
180 BEGIN
181
182 DECLARE
183
184 v_stat_seq_num igs_he_st_spa.hesa_st_spa_id%TYPE;
185
186 CURSOR c_stat_seq_num IS
187 SELECT igs_he_st_spa_all_s.NEXTVAL
188 FROM dual;
189
190 x_rowid VARCHAR2(250);
191 l_org_id NUMBER(15);
192
193 BEGIN
194
195 OPEN c_stat_seq_num;
196 FETCH c_stat_seq_num INTO v_stat_seq_num;
197 CLOSE c_stat_seq_num;
198
199 l_org_id := igs_ge_gen_003.get_org_id;
200 x_rowid := NULL;
201
202 OPEN gc_adm_rec;
203 FETCH gc_adm_rec INTO gv_adm_rec;
204 CLOSE gc_adm_rec;
205
206 OPEN gc_addr_rec;
207 FETCH gc_addr_rec INTO gv_addr_rec;
208 CLOSE gc_addr_rec;
209
210 -- Derive the student instance number value, added as per
211 -- HECR008-alpha numeric student instance number CR
212 l_std_inst_num := 1;
213 FOR cur_std_inst_num_rec IN cur_std_inst_num(p_person_id) LOOP
214 BEGIN
215
216 IF NVL(TO_NUMBER(cur_std_inst_num_rec.Student_inst_number),0) >= l_std_inst_num THEN
217 l_std_inst_num := TO_NUMBER(cur_std_inst_num_rec.Student_inst_number) + 1;
218 END IF;
219 EXCEPTION
220 WHEN VALUE_ERROR THEN
221 NULL;
222 END ;
223 END LOOP;
224
225 --Create a reocrd in hesa student program attempt table
226 igs_he_st_spa_all_pkg.insert_row(
227 x_rowid =>x_rowid,
228 x_org_id =>l_org_id,
229 x_hesa_st_spa_id =>v_stat_seq_num,
230 x_person_id =>p_person_id,
231 x_course_cd =>p_course_cd,
232 x_version_number =>p_crv_version_number,
233 x_fe_student_marker =>NULL,
234 x_student_inst_number =>l_std_inst_num ,
235 x_domicile_cd =>gv_adm_rec.domicile_cd,
236 x_inst_last_attended =>NULL,
237 x_year_left_last_inst =>NULL,
238 x_highest_qual_on_entry =>NULL,
239 x_date_qual_on_entry_calc =>NULL,
240 x_a_level_point_score =>NULL,
241 x_highers_points_scores =>NULL,
242 x_occupation_code =>gv_adm_rec.occupation_cd,
243 x_commencement_dt =>NULL,
244 x_social_class_ind =>gv_adm_rec.social_class_cd,
245 x_special_student =>gv_adm_rec.special_student_cd,
246 x_student_qual_aim =>NULL,
247 x_student_fe_qual_aim =>NULL,
248 x_teacher_train_prog_id =>NULL,
249 x_itt_phase =>NULL,
250 x_bilingual_itt_marker =>NULL,
251 x_teaching_qual_gain_sector=>NULL,
252 x_teaching_qual_gain_subj1 =>NULL,
253 x_teaching_qual_gain_subj2 =>NULL,
254 x_teaching_qual_gain_subj3 =>NULL,
255 x_destination =>NULL,
256 x_itt_prog_outcome =>NULL,
257 x_hesa_return_name =>NULL,
258 x_hesa_return_id =>NULL,
259 x_hesa_submission_name =>NULL,
260 x_associate_ucas_number =>NULL,
261 x_associate_scott_cand =>NULL,
262 x_associate_teach_ref_num =>NULL,
263 x_associate_nhs_reg_num =>NULL,
264 x_nhs_funding_source =>NULL,
265 x_ufi_place =>NULL,
266 x_postcode =>gv_addr_rec,
267 x_occcode =>NULL,
268 x_total_ucas_tariff =>NULL,
269 x_nhs_employer =>NULL,
270 x_return_type =>NULL,
271 x_qual_aim_subj1 =>NULL,
272 x_qual_aim_subj2 =>NULL,
273 x_qual_aim_subj3 =>NULL,
274 x_qual_aim_proportion =>NULL,
275 x_mode =>'R',
276 x_dependants_cd =>NULL,
277 x_implied_fund_rate =>NULL,
278 x_gov_initiatives_cd =>NULL,
279 x_units_for_qual =>NULL,
280 x_disadv_uplift_elig_cd =>NULL,
281 x_franch_partner_cd =>NULL,
282 x_units_completed =>NULL,
283 x_franch_out_arr_cd =>NULL,
284 x_employer_role_cd =>NULL,
285 x_disadv_uplift_factor =>NULL,
286 x_enh_fund_elig_cd =>NULL,
287 x_exclude_flag =>NULL);
288
289 END;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 p_status :=2;
294 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
295 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_HESA_PKG.cr_he_st_spa_rec');
296 IGS_GE_MSG_STACK.ADD;
297 app_exception.raise_exception;
298
299 END cr_he_st_spa_rec;
300
301
302 BEGIN
303
304 p_status := 0;
305
306 -- Check Parameter values passed in correctly
307
308 IF p_person_id IS NULL or
309 p_course_cd IS NULL or
310 p_crv_version_number IS NULL
311 THEN
312 p_status :=2;
313 p_message := 'IGS_HE_INV_PARAMS';
314 RETURN;
315 END IF;
316
317
318 -- if record not found in table then insert new record else update existing record
319
320 OPEN gc_stats_rec;
321 FETCH gc_stats_rec INTO gv_stats_rec;
322 IF gc_stats_rec%NOTFOUND THEN
323 cr_he_st_spa_rec(p_person_id,p_course_cd,p_crv_version_number);
324 END IF;
325 CLOSE gc_stats_rec;
326
327 EXCEPTION
328
329 WHEN OTHERS THEN
330 P_STATUS :=2;
331 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
332 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_HESA_PKG.HESA_STATS_ENR');
333 IGS_GE_MSG_STACK.ADD;
334 app_exception.raise_exception;
335 END hesa_stats_enr;
336
337
338 PROCEDURE hesa_susa_enr(
339 p_person_id IN NUMBER,
340 p_course_cd IN VARCHAR2,
341 p_crv_version_number IN NUMBER,
342 p_old_unit_set_cd IN VARCHAR2,
343 p_old_us_version_number IN NUMBER,
344 p_old_sequence_number IN NUMBER ,
345 p_new_unit_set_cd IN VARCHAR2,
346 p_new_us_version_number IN NUMBER,
347 p_new_sequence_number IN NUMBER,
348 p_message OUT NOCOPY VARCHAR2,
349 p_status OUT NOCOPY NUMBER)
350 -- smaddali 14-may-2002 added new parameter p_old_sequence_number and
351 --renamed p_sequence_number to p_new_sequence_number for bug#2350629
352 -- smaddali 8-jan-2004 Bug#3291399 , modified logic for copying field values
353 AS
354 -- gv_old_susa_rec IGS_HE_EN_SUSA%ROWTYPE;
355 --gv_new_susa_rec IGS_HE_EN_SUSA%ROWTYPE;
356 gv_old_fte_rec igs_he_poous.fte_intensity%TYPE;
357 gv_new_fte_rec igs_he_poous.fte_intensity%TYPE;
358 gv_old_fte_rec_type igs_he_poous.fte_calc_type%TYPE;
359 gv_new_fte_rec_type igs_he_poous.fte_calc_type%TYPE;
360 gv_old_franchising_rec igs_he_poous.franchising_activity%TYPE;
361 gv_new_franchising_rec igs_he_poous.franchising_activity%TYPE;
362 gv_old_fee_rec igs_he_poous.fee_band%TYPE;
363 gv_new_fee_rec igs_he_poous.fee_band%TYPE;
364
365 CURSOR gc_old_susa_rec IS
366 SELECT hesa_en_susa_id,
367 person_id,
368 course_cd,
369 unit_set_cd,
370 us_version_number,
371 sequence_number,
372 new_he_entrant_cd,
373 term_time_accom,
374 disability_allow,
375 additional_sup_band,
376 sldd_discrete_prov,
377 study_mode,
378 study_location,
379 fte_perc_override,
380 franchising_activity,
381 completion_status,
382 good_stand_marker,
383 complete_pyr_study_cd,
384 credit_value_yop1,
385 credit_value_yop2,
386 credit_level1,
387 credit_level2,
388 grad_sch_grade,
389 mark,
390 teaching_inst1,
391 teaching_inst2,
392 pro_not_taught,
393 fundability_code,
394 fee_eligibility,
395 fee_band,
396 non_payment_reason,
397 student_fee,
398 fte_intensity,
399 fte_calc_type,
400 calculated_fte,
401 creation_date,
402 created_by,
403 last_update_date,
404 last_updated_by,
405 last_update_login ,
406 type_of_year
407 FROM igs_he_en_susa
408 WHERE person_id = p_person_id
409 AND course_cd = p_course_cd
410 AND unit_set_cd = p_old_unit_set_cd
411 AND us_version_number = p_old_us_version_number
412 -- smaddali modified p_sequence_number to p_old_sequence_number for bug#2350629
413 AND sequence_number = p_old_sequence_number;
414
415
416 CURSOR gc_new_susa_rec IS
417 SELECT hesa_en_susa_id,
418 person_id,
419 course_cd,
420 unit_set_cd,
421 us_version_number,
422 sequence_number,
423 new_he_entrant_cd,
424 term_time_accom,
425 disability_allow,
426 additional_sup_band,
427 sldd_discrete_prov,
428 study_mode,
429 study_location,
430 fte_perc_override,
431 franchising_activity,
432 completion_status,
433 good_stand_marker,
434 complete_pyr_study_cd,
435 credit_value_yop1,
436 credit_value_yop2,
437 credit_level1,
438 credit_level2,
439 grad_sch_grade,
440 mark,
441 teaching_inst1,
442 teaching_inst2,
443 pro_not_taught,
444 fundability_code,
445 fee_eligibility,
446 fee_band,
447 non_payment_reason,
448 student_fee,
449 fte_intensity,
450 calculated_fte,
451 fte_calc_type,
452 creation_date,
453 created_by,
454 last_update_date,
455 last_updated_by,
456 last_update_login ,
457 type_of_year
458 FROM igs_he_en_susa
459 WHERE person_id = p_person_id
460 AND course_cd = p_course_cd
461 AND unit_set_cd = p_new_unit_set_cd
462 AND us_version_number = p_new_us_version_number
463 -- smaddali modified p_sequence_number to p_new_sequence_number for bug#2350629
464 AND sequence_number = p_new_sequence_number;
465
466 --smaddali modified the join conditions because they are resulting in a cartesian product (bug#2350629)
467 CURSOR gc_old_poo is
468 SELECT a.fte_intensity fte_intensity,
469 a.fte_calc_type fte_calc_type,
470 a.franchising_activity franchising_activity,
471 a.fee_band fee_band,
472 a.type_of_year,
473 a.fundability_cd
474 FROM igs_he_poous a,
475 igs_en_stdnt_ps_att b
476 WHERE a.course_cd = p_course_cd
477 AND a.crv_version_number = p_crv_version_number
478 AND a.unit_set_cd = p_old_unit_set_cd
479 AND a.us_version_number = p_old_us_version_number
480 AND b.person_id = p_person_id
481 AND b.course_cd = a.course_cd
482 AND b.version_number = a.crv_version_number
483 AND a.cal_type = b.cal_type
484 AND a.location_cd = b.location_cd
485 AND a.attendance_mode = b.attendance_mode
486 AND a.attendance_type = b.attendance_type;
487 gc_old_poo_rec gc_old_poo%ROWTYPE;
488
489 --smaddali modified the join conditions because they are resulting in a cartesian product (bug#2350629)
490 CURSOR gc_new_poo IS
491 SELECT a.fte_intensity fte_intensity,
492 a.fte_calc_type fte_calc_type,
493 a.franchising_activity franchising_activity,
494 a.fee_band fee_band,
495 a.type_of_year,
496 a.fundability_cd
497 FROM igs_he_poous a,
498 igs_en_stdnt_ps_att b
499 WHERE a.course_cd = p_course_cd
500 AND a.crv_version_number = p_crv_version_number
501 AND a.unit_set_cd = p_new_unit_set_cd
502 AND a.us_version_number = p_new_us_version_number
503 AND b.person_id = p_person_id
504 AND b.course_cd = a.course_cd
505 AND b.version_number = a.crv_version_number
506 AND a.cal_type = b.cal_type
507 AND a.location_cd = b.location_cd
508 AND a.attendance_mode = b.attendance_mode
509 AND a.attendance_type = b.attendance_type;
510 gc_new_poo_rec gc_new_poo%ROWTYPE;
511
512 gv_old_susa_rec gc_old_susa_rec%ROWTYPE;
513 gv_new_susa_rec gc_new_susa_rec%ROWTYPE;
514
515 --Procedure to create a new record on first pre-enrolment
516 --smaddali modified p_sequence_number to p_new_sequence_number (bug#2350629)
517 PROCEDURE cr_he_new_susa_rec(p_person_id igs_he_en_susa.person_id%TYPE,
518 p_course_cd igs_he_en_susa.course_cd%TYPE,
519 p_new_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE,
520 p_new_us_version_number igs_he_en_susa.us_version_number%TYPE,
521 p_new_sequence_number igs_he_en_susa.sequence_number%TYPE) IS
522
523 BEGIN
524
525 DECLARE
526
527 v_susa_seq_num igs_he_en_susa.hesa_en_susa_id%TYPE;
528
529 CURSOR c_susa_seq_num IS
530 SELECT igs_he_en_susa_s.NEXTVAL
531 FROM dual;
532
533 v_rowid VARCHAR2(250);
534
535 BEGIN
536
537 OPEN c_susa_seq_num;
538 FETCH c_susa_seq_num INTO v_susa_seq_num;
539 CLOSE c_susa_seq_num;
540
541 v_rowid := NULL;
542
543 igs_he_en_susa_pkg.insert_row(
544 x_rowid => v_rowid,
545 x_hesa_en_susa_id => v_susa_seq_num,
546 x_person_id => p_person_id,
547 x_course_cd => p_course_cd,
548 x_unit_set_cd => p_new_unit_set_cd,
549 x_us_version_number => p_new_us_version_number,
550 x_sequence_number => p_new_sequence_number,
551 x_new_he_entrant_cd => NULL,
552 x_term_time_accom => NULL,
553 x_disability_allow => NULL,
554 x_additional_sup_band => NULL,
555 x_sldd_discrete_prov => NULL,
556 x_study_mode => NULL,
557 x_study_location => NULL,
558 x_fte_perc_override => NULL,
559 x_franchising_activity => NULL,
560 x_completion_status => NULL,
561 x_good_stand_marker => NULL,
562 x_complete_pyr_study_cd => NULL,
563 x_credit_value_yop1 => NULL,
564 x_credit_value_yop2 => NULL,
565 x_credit_level_achieved1 => NULL,
566 x_credit_level_achieved2 => NULL,
567 x_credit_pt_achieved1 => NULL,
568 x_credit_pt_achieved2 => NULL,
569 x_credit_level1 => NULL,
570 x_credit_level2 => NULL,
571 x_grad_sch_grade => NULL,
572 x_mark => NULL,
573 x_teaching_inst1 => NULL,
574 x_teaching_inst2 => NULL,
575 x_pro_not_taught => NULL,
576 x_fundability_code => NULL,
577 x_fee_eligibility => NULL,
578 x_fee_band => NULL,
579 x_non_payment_reason => NULL,
580 x_student_fee => NULL,
581 x_fte_intensity => NULL,
582 x_fte_calc_type => NULL,
583 x_calculated_fte => NULL,
584 x_type_of_year => NULL,
585 x_mode => 'R',
586 x_credit_value_yop3 => NULL,
587 x_credit_value_yop4 => NULL,
588 x_credit_level_achieved3 => NULL,
589 x_credit_level_achieved4 => NULL,
590 x_credit_pt_achieved3 => NULL,
591 x_credit_pt_achieved4 => NULL,
592 x_credit_level3 => NULL,
593 x_credit_level4 => NULL,
594 x_additional_sup_cost => NULL,
595 x_enh_fund_elig_cd => NULL,
596 x_disadv_uplift_factor => NULL,
597 x_year_stu => NULL);
598
599 END;
600
601 EXCEPTION
602
603 WHEN OTHERS THEN
604 P_STATUS := 2;
605 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
606 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_HESA_PKG.CR_HE_NEW_SUSA_REC');
607 IGS_GE_MSG_STACK.ADD;
608 app_exception.raise_exception;
609
610 END cr_he_new_susa_rec;
611
612 --smaddali modified p_sequence_number to p_new_sequence_number (bug#2350629)
613 PROCEDURE cr_he_add_susa_rec(p_person_id igs_he_en_susa.person_id%TYPE,
614 p_course_cd igs_he_en_susa.course_cd%TYPE,
615 p_old_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE,
616 p_old_us_version_number igs_he_en_susa.us_version_number%TYPE,
617 p_new_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE,
618 p_new_us_version_number igs_he_en_susa.us_version_number%TYPE,
619 p_new_sequence_number igs_he_en_susa.sequence_number%TYPE) IS
620
621 BEGIN
622
623 DECLARE
624
625 v_susa_seq_num igs_he_en_susa.hesa_en_susa_id%TYPE;
626
627 CURSOR c_susa_seq_num IS
628 SELECT igs_he_en_susa_s.NEXTVAL
629 FROM dual;
630
631 x_rowid VARCHAR2(250);
632
633 v_old_franchising igs_he_poous.franchising_activity%TYPE;
634 v_new_franchising igs_he_poous.franchising_activity%TYPE;
635 v_franchising igs_he_en_susa.franchising_activity%TYPE;
636
637 v_old_fte igs_he_poous.fte_intensity%TYPE;
638 v_new_fte igs_he_poous.fte_intensity%TYPE;
639 v_fte igs_he_en_susa.fte_intensity%TYPE;
640
641 v_old_fee_band igs_he_poous.fee_band%TYPE;
642 v_new_fee_band igs_he_poous.fee_band%TYPE;
643 v_fee_band igs_he_en_susa.fee_band%TYPE;
644
645 v_old_fte_type igs_he_poous.fte_calc_type%TYPE;
646 v_new_fte_type igs_he_poous.fte_calc_type%TYPE;
647 v_fte_type igs_he_en_susa.fte_calc_type%TYPE;
648 -- smaddali added variables for bug#
649 l_fundability_code igs_he_en_susa.fundability_code%TYPE;
650 l_type_of_year igs_he_en_susa.type_of_year%TYPE ;
651 BEGIN
652
653 OPEN c_susa_seq_num;
654 FETCH c_susa_seq_num INTO v_susa_seq_num;
655 CLOSE c_susa_seq_num;
656
657 gc_old_poo_rec := NULL;
658 OPEN gc_old_poo;
659 FETCH gc_old_poo INTO gc_old_poo_rec;
660 CLOSE gc_old_poo;
661 gc_new_poo_rec := NULL ;
662 OPEN gc_new_poo;
663 FETCH gc_new_poo INTO gc_new_poo_rec ;
664 CLOSE gc_new_poo;
665
666 -- smaddali added the condition to copy field when both old and new poous values are null
667 -- for the fields franchising_activity,fte_intensity and fee_band, bug#3291399
668 IF ( gc_old_poo_rec.franchising_activity IS NULL AND gc_new_poo_rec.franchising_activity IS NULL ) OR
669 (gc_old_poo_rec.franchising_activity = gc_new_poo_rec.franchising_activity) THEN
670 v_franchising := gv_old_susa_rec.franchising_activity;
671 ELSE
672 v_franchising := NULL;
673 END IF;
674
675 IF ( gc_old_poo_rec.fte_intensity IS NULL AND gc_new_poo_rec.fte_intensity IS NULL ) OR
676 (gc_old_poo_rec.fte_intensity = gc_new_poo_rec.fte_intensity) THEN
677 v_fte := gv_old_susa_rec.fte_intensity;
678 ELSE
679 v_fte := NULL;
680 END IF;
681
682 IF ( gc_old_poo_rec.fee_band IS NULL AND gc_new_poo_rec.fee_band IS NULL ) OR
683 (gc_old_poo_rec.fee_band = gc_new_poo_rec.fee_band) THEN
684 v_fee_band := gv_old_susa_rec.fee_band;
685 ELSE
686 v_fee_band := NULL;
687 END IF;
688
689 -- smaddali added code to copy fundability_code and type_of_year conditionally for bug#3291399
690 -- copy fte_calc_type and type_of_year if poous.type_of_year is same or null for both programs
691 IF (gc_old_poo_rec.type_of_year IS NULL AND gc_new_poo_rec.type_of_year IS NULL) OR
692 (gc_old_poo_rec.type_of_year = gc_new_poo_rec.type_of_year) THEN
693 v_fte_type := gv_old_susa_rec.fte_calc_type;
694 l_type_of_year := gv_old_susa_rec.type_of_year;
695 ELSE
696 v_fte_type := NULL;
697 l_type_of_year := NULL ;
698 END IF;
699
700 -- copy fundability_code if poous.fundability_code is same or is null for both poous
701 IF ( gc_old_poo_rec.fundability_cd IS NULL AND gc_new_poo_rec.fundability_cd IS NULL ) OR
702 (gc_old_poo_rec.fundability_cd = gc_new_poo_rec.fundability_cd) THEN
703 l_fundability_code := gv_old_susa_rec.fundability_code;
704 ELSE
705 l_fundability_code := NULL;
706 END IF;
707
708
709 x_rowid := NULL;
710 -- smaddali removed copying of x_credit_level_achieved1,x_credit_level_achieved2,
711 -- x_credit_pt_achieved1 and x_credit_pt_achieved2,fundability_code,calculated_fte
712 -- copying fundability_code and type_of_year conditionally for bug#3291399
713 igs_he_en_susa_pkg.insert_row(
714 x_rowid => x_rowid,
715 x_hesa_en_susa_id => v_susa_seq_num,
716 x_person_id => p_person_id,
717 x_course_cd => p_course_cd,
718 x_unit_set_cd => p_new_unit_set_cd,
719 x_us_version_number => p_new_us_version_number,
720 x_sequence_number => p_new_sequence_number,
721 x_new_he_entrant_cd => NULL,
722 x_term_time_accom => NULL,
723 x_disability_allow => gv_old_susa_rec.disability_allow,
724 x_additional_sup_band => gv_old_susa_rec.additional_sup_band,
725 x_sldd_discrete_prov => gv_old_susa_rec.sldd_discrete_prov,
726 x_study_mode => NULL,
727 x_study_location => NULL,
728 x_fte_perc_override => NULL,
729 x_franchising_activity => v_franchising,
730 x_completion_status => NULL,
731 x_good_stand_marker => NULL,
732 x_complete_pyr_study_cd => NULL,
733 x_grad_sch_grade => NULL,
734 x_mark => NULL,
735 x_credit_value_yop1 => NULL,
736 x_credit_value_yop2 => NULL,
737 x_credit_level_achieved1 => NULL,
738 x_credit_level_achieved2 => NULL,
739 x_credit_pt_achieved1 => NULL,
740 x_credit_pt_achieved2 => NULL,
741 x_credit_level1 => NULL,
742 x_credit_level2 => NULL,
743 x_teaching_inst1 => NULL,
744 x_teaching_inst2 => NULL,
745 x_pro_not_taught => NULL,
746 x_fundability_code => l_fundability_code,
747 x_fee_eligibility => gv_old_susa_rec.fee_eligibility,
748 x_fee_band => v_fee_band,
749 x_non_payment_reason => NULL,
750 x_student_fee => gv_old_susa_rec.student_fee,
751 x_fte_intensity => v_fte,
752 x_calculated_fte => NULL,
753 x_fte_calc_type => v_fte_type,
754 x_type_of_year => l_type_of_year,
755 x_mode => 'R',
756 x_credit_value_yop3 => NULL,
757 x_credit_value_yop4 => NULL,
758 x_credit_level_achieved3 => NULL,
759 x_credit_level_achieved4 => NULL,
760 x_credit_pt_achieved3 => NULL,
761 x_credit_pt_achieved4 => NULL,
762 x_credit_level3 => NULL,
763 x_credit_level4 => NULL,
764 x_additional_sup_cost => NULL,
765 x_enh_fund_elig_cd => NULL,
766 x_disadv_uplift_factor => NULL,
767 x_year_stu => NULL);
768 END;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 P_STATUS :=2;
773 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
774 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_HESA_PKG.CR_HE_ADD_SUSA_REC');
775 IGS_GE_MSG_STACK.ADD;
776 app_exception.raise_exception;
777
778 END cr_he_add_susa_rec;
779
780
781
782 BEGIN
783
784 p_status := 0;
785
786 -- Check Parameter values passed in correctly
787
788 IF p_person_id IS NULL OR
789 p_course_cd IS NULL OR
790 p_new_unit_set_cd IS NULL OR
791 p_new_us_version_number IS NULL OR
792 p_new_sequence_number is NULL
793 THEN
794 p_status := 2;
795 p_message := 'IGS_HE_INV_PARAMS';
796 RETURN;
797 END IF;
798
799
800 -- If no record exists in IGS_HE_ENS_SUSA table then create new record
801 -- else if record exists then add new record and copy some fields
802 -- from previous SUSA record
803
804 OPEN gc_new_susa_rec;
805 FETCH gc_new_susa_rec INTO gv_new_susa_rec;
806 IF gc_new_susa_rec%NOTFOUND THEN
807
808
809 OPEN gc_old_susa_rec;
810 FETCH gc_old_susa_rec INTO gv_old_susa_rec;
811 IF gc_old_susa_rec%NOTFOUND THEN
812 cr_he_new_susa_rec(p_person_id,
813 p_course_cd,
814 p_new_unit_set_cd,
815 p_new_us_version_number,
816 p_new_sequence_number); --smaddali modified p_sequence_number to p_new_sequence_number (bug#2350629)
817
818 ELSE
819
820 IF p_old_unit_set_cd IS NULL or
821 p_old_us_version_number IS NULL THEN
822
823 p_status := 2;
824 p_message := 'IGS_HE_PCPY_INV_PARAMS';
825 app_exception.raise_exception;
826 RETURN;
827 END IF;
828
829
830 cr_he_add_susa_rec(p_person_id,
831 p_course_cd,
832 p_old_unit_set_cd,
833 p_old_us_version_number,
834 p_new_unit_set_cd,
835 p_new_us_version_number,
836 p_new_sequence_number); --smaddali modified p_sequence_number to p_new_sequence_number (bug#2350629)
837
838 END IF;
839
840 CLOSE gc_old_susa_rec;
841
842 END IF;
843
844 CLOSE gc_new_susa_rec;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 p_status := 2;
849 FND_MESSAGE.SET_NAME('IGS','IGS_UNHANDLED_EXCEPTION');
850 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_HESA_PKG.HESA_SUSA_ENR');
851 IGS_GE_MSG_STACK.ADD;
852 app_exception.raise_exception;
853
854 END hesa_susa_enr;
855
856 /*------------------------------------------------------------------
857 --Created by : knaraset, Oracle IDC
858 --Date created: 14-Nov-2002
859 --
860 --Purpose: Function to validate whether the given award code exists
861 -- against system award type COURSE
862 --
863 --Known limitations/enhancements and/or remarks:
864 --
865 --Change History:
866 --Who When What
867 --pmarada 13-feb-03 Modified the cur_award_cd cursor where clause,
868 -- added the closed_ind condition, bug 2801518
869 ------------------------------------------------------------------ */
870 FUNCTION validate_program_aim(
871 p_award_cd IN VARCHAR2)
872 RETURN BOOLEAN AS
873 --
874 -- cursor to check whether the given award code exists against system award type COURSE
875 CURSOR cur_award_cd IS
876 SELECT 'x'
877 FROM igs_ps_awd
878 WHERE s_award_type = 'COURSE' AND
879 award_cd = p_award_cd AND
880 closed_ind = 'N';
881
882 l_dummy varchar2(1);
883
884 BEGIN
885
886 IF p_award_cd IS NOT NULL THEN
887 OPEN cur_award_cd;
888 FETCH cur_award_cd INTO l_dummy;
889 IF cur_award_cd%NOTFOUND THEN
890 CLOSE cur_award_cd;
891 RETURN FALSE;
892 END IF;
893 CLOSE cur_award_cd;
894 END IF;
895
896 RETURN TRUE;
897 END validate_program_aim;
898
899 /*------------------------------------------------------------------
900 --Created by : knaraset, Oracle IDC
901 --Date created: 14-Nov-2002
902 --
903 --Purpose: function to validate whether the specified combination of subj_qualaim's and qualaim_proportion is valid
904 --
905 --Known limitations/enhancements and/or remarks:
906 --
907 --Change History:
908 --Who When What
909 --
910 ------------------------------------------------------------------ */
911
912 FUNCTION val_sub_qual_proportion(
913 p_subj_qualaim1 IN VARCHAR2,
914 p_subj_qualaim2 IN VARCHAR2,
915 p_subj_qualaim3 IN VARCHAR2,
916 p_qualaim_proportion IN VARCHAR2)
917 RETURN BOOLEAN AS
918 l_val_failed BOOLEAN := FALSE;
919 BEGIN
920
921 IF p_subj_qualaim1 IS NULL AND p_subj_qualaim2 IS NOT NULL THEN
922 l_val_failed := TRUE;
923 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ2_IF_SUBQ1');
924 FND_MSG_PUB.ADD;
925 END IF;
926
927 IF p_subj_qualaim2 IS NULL AND p_subj_qualaim3 IS NOT NULL THEN
928 l_val_failed := TRUE;
929 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ3_IF_SUBQ2');
930 FND_MSG_PUB.ADD;
931 END IF;
932
933 IF p_subj_qualaim1 IS NOT NULL AND p_subj_qualaim2 IS NOT NULL AND
934 p_subj_qualaim3 IS NULL AND p_qualaim_proportion IS NULL THEN
935 l_val_failed := TRUE;
936 FND_MESSAGE.SET_NAME('IGS','IGS_HE_QUAL_PROP_MUST_COMP');
937 FND_MSG_PUB.ADD;
938 END IF;
939
940 IF p_subj_qualaim3 IS NOT NULL AND p_qualaim_proportion IS NOT NULL THEN
941 l_val_failed := TRUE;
942 FND_MESSAGE.SET_NAME('IGS','IGS_HE_QUAL_PROP_CANT_SET');
943 FND_MSG_PUB.ADD;
944 END IF;
945
946 IF ((p_subj_qualaim1 IS NULL AND (p_subj_qualaim2 IS NOT NULL OR p_subj_qualaim3 IS NOT NULL OR p_qualaim_proportion IS NOT NULL)) OR
947 (p_subj_qualaim2 IS NULL AND (p_subj_qualaim3 IS NOT NULL OR p_qualaim_proportion IS NOT NULL)) OR
948 (p_subj_qualaim3 IS NULL AND p_qualaim_proportion IS NULL AND (p_subj_qualaim1 IS NOT NULL AND p_subj_qualaim2 IS NOT NULL )) OR
949 (p_subj_qualaim1 IS NOT NULL AND p_subj_qualaim2 IS NOT NULL AND p_subj_qualaim3 IS NOT NULL AND p_qualaim_proportion IS NOT NULL)) THEN
950 l_val_failed := TRUE;
951 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ_INVALID_SET');
952 FND_MSG_PUB.ADD;
953 END IF;
954
955 IF l_val_failed THEN
956 RETURN FALSE;
957 END IF;
958 RETURN TRUE;
959
960 END val_sub_qual_proportion;
961
962
963 /*------------------------------------------------------------------
964 --Created by : knaraset, Oracle IDC
965 --Date created: 14-Nov-2002
966 --
967 --Purpose: function to check whether the given highest qual on entry is exists against the
968 -- grading schema defined for HESA code HESA_HIGH_QUAL_ON_ENT.
969 --
970 --Known limitations/enhancements and/or remarks:
971 --
972 --Change History:
973 --Who When What
974 --
975 ------------------------------------------------------------------ */
976 FUNCTION val_highest_qual_entry(
977 p_highest_qual_on_entry IN VARCHAR2)
978 RETURN BOOLEAN AS
979
980 --
981 -- cursor to check whether the given grade exists against the grading schema defined for HESA code HESA_HIGH_QUAL_ON_ENT.
982 -- smaddali added condition to get only open code values for bug 2730388
983 CURSOR cur_hqual_grade IS
984 SELECT 'x'
985 FROM igs_as_grd_sch_grade gsg,
986 igs_he_code_values hcv
987 WHERE gsg.grading_schema_cd = hcv.value AND
988 hcv.code_type = 'HESA_HIGH_QUAL_ON_ENT' AND
989 gsg.grade = p_highest_qual_on_entry AND
990 NVL(hcv.closed_ind,'N') = 'N' ;
991
992 l_dummy VARCHAR2(1);
993 BEGIN
994 IF p_highest_qual_on_entry IS NOT NULL THEN
995 OPEN cur_hqual_grade;
996 FETCH cur_hqual_grade INTO l_dummy;
997 IF cur_hqual_grade%NOTFOUND THEN
998 CLOSE cur_hqual_grade;
999 RETURN FALSE;
1000 END IF;
1001 CLOSE cur_hqual_grade;
1002 END IF;
1003
1004 RETURN TRUE;
1005
1006 END val_highest_qual_entry;
1007
1008 /*------------------------------------------------------------------
1009 --Created by : knaraset, Oracle IDC
1010 --Date created: 14-Nov-2002
1011 --
1012 --Purpose: function to get the unit set category for the given unit set
1013 --
1014 --Known limitations/enhancements and/or remarks:
1015 --
1016 --Change History:
1017 --Who When What
1018 --
1019 ------------------------------------------------------------------ */
1020 FUNCTION get_unit_set_cat(
1021 p_unit_set_cd IN VARCHAR2,
1022 p_us_version_number IN NUMBER)
1023 RETURN VARCHAR2 AS
1024
1025 --
1026 -- cursor to get the unit set category.
1027 CURSOR cur_us_cat IS
1028 SELECT usc.s_unit_set_cat
1029 FROM igs_en_unit_set us,
1030 igs_en_unit_set_cat usc
1031 WHERE us.unit_set_cd = p_unit_set_cd AND
1032 us.version_number = p_us_version_number AND
1033 us.unit_set_cat = usc.unit_set_cat;
1034
1035 l_us_category igs_en_unit_set_cat.s_unit_set_cat%TYPE;
1036
1037 BEGIN
1038
1039 l_us_category := NULL;
1040 IF p_unit_set_cd IS NOT NULL AND p_us_version_number IS NOT NULL THEN
1041 OPEN cur_us_cat;
1042 FETCH cur_us_cat INTO l_us_category;
1043 CLOSE cur_us_cat;
1044 END IF;
1045 RETURN l_us_category;
1046 END get_unit_set_cat;
1047
1048 /*------------------------------------------------------------------
1049 --Created by : knaraset, Oracle IDC
1050 --Date created: 14-Nov-2002
1051 --
1052 --Purpose: function to check whether the given institution exists with institution type Post-Secondary
1053 --
1054 --Known limitations/enhancements and/or remarks:
1055 --
1056 --Change History:
1057 --Who When What
1058 --
1059 ------------------------------------------------------------------ */
1060 FUNCTION check_teach_inst(
1061 p_teaching_inst IN VARCHAR2)
1062 RETURN BOOLEAN AS
1063 --
1064 -- cursor to check whether the given teaching institution is exists as POST SECONDARY institution type.
1065 CURSOR cur_tech_inst IS
1066 SELECT 'x'
1067 FROM igs_or_inst_outer_v
1068 WHERE institution_cd = p_teaching_inst AND
1069 system_inst_type='POST-SECONDARY';
1070
1071 l_dummy VARCHAR2(1);
1072 BEGIN
1073
1074 IF p_teaching_inst IS NOT NULL THEN
1075 OPEN cur_tech_inst;
1076 FETCH cur_tech_inst INTO l_dummy;
1077 IF cur_tech_inst%NOTFOUND THEN
1078 CLOSE cur_tech_inst;
1079 RETURN FALSE;
1080 END IF;
1081 CLOSE cur_tech_inst;
1082 END IF;
1083 RETURN TRUE;
1084 END check_teach_inst;
1085
1086 /*------------------------------------------------------------------
1087 --Created by : knaraset, Oracle IDC
1088 --Date created: 14-Nov-2002
1089 --
1090 --Purpose: function to check whether the given grade is exists against the grading schema defined in Unit set statistics.
1091 --
1092 --Known limitations/enhancements and/or remarks:
1093 --
1094 --Change History:
1095 --Who When What
1096 --
1097 ------------------------------------------------------------------ */
1098 FUNCTION check_grading_sch_grade(
1099 p_person_id IN NUMBER,
1100 p_program_cd IN VARCHAR2,
1101 p_unit_set_cd IN VARCHAR2,
1102 p_grad_sch_grade IN VARCHAR2)
1103 RETURN BOOLEAN AS
1104 --
1105 -- cursor to check whether the given grade is exists against the grading schema defined in Unit set statistics.
1106 CURSOR cur_grd_sch_grade IS
1107 SELECT 'x'
1108 FROM igs_as_grd_sch_grade gsg,
1109 igs_he_poous_all poous,
1110 igs_en_stdnt_ps_att sca,
1111 igs_as_su_setatmpt susa
1112 WHERE gsg.grading_schema_cd = poous.grading_schema_cd AND
1113 gsg.version_number = poous.gs_version_number AND
1114 gsg.grade = p_grad_sch_grade AND
1115 poous.course_cd = sca.course_cd AND
1116 poous.crv_version_number = sca.version_number AND
1117 poous.cal_type = sca.cal_type AND
1118 poous.location_cd = sca.location_cd AND
1119 poous.attendance_mode = sca.attendance_mode AND
1120 poous.attendance_type = sca.attendance_type AND
1121 poous.unit_set_cd = susa.unit_set_cd AND
1122 poous.us_version_number = susa.us_version_number AND
1123 sca.person_id = p_person_id AND
1124 sca.course_cd = p_program_cd AND
1125 susa.person_id= sca.person_id AND
1126 susa.course_cd = sca.course_cd AND
1127 susa.unit_set_cd = p_unit_set_cd;
1128
1129 l_dummy VARCHAR2(1);
1130 BEGIN
1131
1132 IF p_grad_sch_grade IS NOT NULL THEN
1133 IF p_person_id IS NULL OR p_program_cd IS NULL OR p_unit_set_cd IS NULL THEN
1134 RETURN FALSE;
1135 END IF;
1136 OPEN cur_grd_sch_grade;
1137 FETCH cur_grd_sch_grade INTO l_dummy;
1138 IF cur_grd_sch_grade%NOTFOUND THEN
1139 CLOSE cur_grd_sch_grade;
1140 RETURN FALSE;
1141 END IF;
1142 CLOSE cur_grd_sch_grade;
1143 END IF;
1144 RETURN TRUE;
1145
1146 END check_grading_sch_grade;
1147
1148 END igs_en_hesa_pkg;
1149