1 PACKAGE BODY igs_he_prog_transfer_pkg AS
2 /* $Header: IGSHE17B.pls 120.1 2006/02/07 14:53:19 jbaber noship $ */
3
4 /*---------------------------------------------------------------------
5 This procedure will copy old record details to a new record
6
7 Output : p_message_name - Error messagev_calc
8 p_status - Return code for the procedure.
9 0 - Success
10 1 - Warning
11 2 - Failure
12 --smvk 03-Jun-2003 Bug # 2858436.Modified the cursor c_prg_awd to select open program awards only.
13 -- smaddali (bug#2371477) 16-may-2002 modified this procedure to create the new unit
14 -- set attempt hesa details record even when the old susa hesa details record is not found
15 --Bayadav 22-OCT-2002 Included four new columns qual_aim_subj1,qual_aim_subj2,qual_aim_subj3,qual_aim_proportion
16 -- in IGS_HE_ST_SPA_ALL table as a part of bug 2636897
17 -- Bayadav 05-DEC-2002 Included the check for HESA qualaim instead of award code before copying the old units values back to the new unit
18 -- as a part of bug 2671155
19 -- smaddali modified procedure to copy new_he_entrant_cd and modified validation for fundability_cd ,bug 2730371
20 -- smaddali modified procedure to copy new_he_entrant_cd for continuous programs of study ,bug 2717755
21 -- ayedubat 01-SEP-2003 Changed the procedure to copy the Student Unit Set Attempt Cost Centre details
22 -- for HE207FD bug, 2717753
23 ---------------------------------------------------------------------*/
24
25 PROCEDURE hesa_stud_susa_trans(
26 p_person_id IN NUMBER,
27 p_old_course_cd IN VARCHAR2,
28 p_new_course_cd IN VARCHAR2,
29 p_old_unit_set_cd IN VARCHAR2,
30 p_new_unit_set_cd IN VARCHAR2,
31 p_old_us_version_number IN NUMBER,
32 p_new_us_version_number IN NUMBER,
33 p_status OUT NOCOPY VARCHAR2,
34 p_message_name OUT NOCOPY VARCHAR2 ) IS
35
36
37 -- Variables to hold old and new values from other tables for record.
38 v_old_prg_funding_source igs_fi_fnd_src_rstn.funding_source%TYPE;
39 v_old_hesa_qual_map1 igs_he_code_map_val.map1%TYPE;
40 v_old_prg_fundability_cd igs_he_poous_all.fundability_cd%TYPE;
41
42 v_new_hesa_qual_map1 igs_he_code_map_val.map1%TYPE;
43 v_new_prg_funding_source igs_fi_fnd_src_rstn.funding_source%TYPE;
44 v_new_prg_fundability_cd igs_he_poous_all.fundability_cd%TYPE;
45
46 -- Variables to hold final values for first record
47 v_hesa_en_susa_id igs_he_en_susa.hesa_en_susa_id%TYPE;
48 v_term_time_accom igs_he_en_susa.term_time_accom%TYPE;
49 v_study_mode igs_he_en_susa.study_mode%TYPE;
50 v_student_qual_aim igs_he_st_spa_all.student_qual_aim%TYPE;
51 v_franchising_activity igs_he_en_susa.franchising_activity%TYPE;
52 v_fte_perc_override igs_he_en_susa.fte_perc_override%TYPE;
53 v_fundability_code igs_he_en_susa.fundability_code%TYPE;
54 v_fee_band igs_he_en_susa.fee_band%TYPE;
55 v_completion_status igs_he_en_susa.completion_status%TYPE;
56 v_good_stand_marker igs_he_en_susa.good_stand_marker%TYPE;
57 v_complete_pyr_study_cd igs_he_en_susa.complete_pyr_study_cd%TYPE;
58 v_grad_sch_grade igs_he_en_susa.grad_sch_grade%TYPE;
59 v_mark igs_he_en_susa.mark%TYPE;
60 v_type_of_year igs_he_en_susa.TYPE_OF_YEAR%TYPE;
61 v_fte_intensity igs_he_en_susa.fte_intensity%TYPE;
62 v_fte_calc_type igs_he_en_susa.fte_calc_type%TYPE;
63 v_term_time_map1 igs_he_code_map_val.map1%TYPE;
64 v_rowid VARCHAR2(25);
65 v_new_he_entrant_cd igs_he_en_susa.new_he_entrant_cd%TYPE;
66
67 --Variables to hold old record unique key values
68 v_u_old_version_number igs_en_stdnt_ps_att.version_number%TYPE;
69 v_u_old_cal_type igs_en_stdnt_ps_att.cal_type%TYPE;
70 v_u_old_location_cd igs_en_stdnt_ps_att.location_cd%TYPE;
71 v_u_old_attendance_mode igs_en_stdnt_ps_att.attendance_mode%TYPE;
72 v_u_old_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
73 v_u_old_sequence_number igs_as_su_setatmpt.sequence_number%TYPE;
74 v_u_new_version_number igs_en_stdnt_ps_att.version_number%TYPE;
75 v_u_new_cal_type igs_en_stdnt_ps_att.cal_type%TYPE;
76 v_u_new_location_cd igs_en_stdnt_ps_att.location_cd%TYPE;
77 v_u_new_attendance_mode igs_en_stdnt_ps_att.attendance_mode%TYPE;
78 v_u_new_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
79 v_u_new_sequence_number igs_as_su_setatmpt.sequence_number%TYPE;
80
81 -- Cursor to retrieve unique-key values for passed program_cd which will be used
82 -- to extract single records from other tables.
83 CURSOR c_prg_ukeyrec (cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ) IS
84 SELECT version_number,
85 cal_type,
86 location_cd,
87 attendance_mode,
88 attendance_type
89 FROM igs_en_stdnt_ps_att
90 WHERE person_id = p_person_id AND
91 course_cd = cp_course_cd;
92
93
94 -- Cursor to retrieve unique-key values for passed unit set attempt which will be used
95 -- to extract single records from other tables.
96 CURSOR c_us_ukeyrec(cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
97 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE,
98 cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE) IS
99 SELECT max(sequence_number)
100 FROM igs_as_su_setatmpt
101 WHERE person_id = p_person_id AND
102 course_cd = cp_course_cd AND
103 unit_set_cd = cp_unit_set_cd AND
104 us_version_number = cp_us_version_number;
105
106
107
108 -- Cursor to retrive values that dont need to be validated for record1.
109 -- smaddali merged this cursor with c_oldrec1 for better performance
110 CURSOR c_old_susa IS
111 SELECT disability_allow,
112 additional_sup_band,
113 sldd_discrete_prov,
114 credit_level_achieved1,
115 credit_level_achieved2,
116 credit_pt_achieved1,
117 credit_pt_achieved2,
118 fee_eligibility,
119 non_payment_reason,
120 calculated_fte ,
121 term_time_accom,
122 study_mode,
123 franchising_activity,
124 fte_perc_override,
125 completion_status,
126 good_stand_marker,
127 complete_pyr_study_cd,
128 grad_sch_grade,
129 mark,
130 fundability_code,
131 fee_band ,
132 fte_intensity,
133 fte_calc_type,
134 type_of_year,
135 new_he_entrant_cd,
136 credit_level_achieved3,
137 credit_level_achieved4,
138 credit_pt_achieved3,
139 credit_pt_achieved4,
140 additional_sup_cost,
141 enh_fund_elig_cd,
142 disadv_uplift_factor,
143 year_stu
144 FROM igs_he_en_susa
145 WHERE person_id = p_person_id AND
146 course_cd = p_old_course_cd AND
147 unit_set_cd = p_old_unit_set_cd AND
148 us_version_number = p_old_us_version_number AND
149 sequence_number = v_u_old_sequence_number;
150 c_old_susa_rec c_old_susa%ROWTYPE ;
151
152
153 -- Cursor to retrieve old record values for record 1 from IGS_HE_POOUS_ALL
154 -- that will be validated.
155 -- smaddali selecting fundability_cd ,for bug 2730371
156 -- smaddali added field funding_source as part of hefd208 bug#2717751
157 CURSOR c_old_poous IS
158 SELECT attendance_type,
159 franchising_activity,
160 fte_intensity,
161 fte_calc_type,
162 grading_schema_cd,
163 gs_version_number,
164 fee_band,
165 type_of_year,
166 fundability_cd,
167 funding_source
168 FROM igs_he_poous_all
169 WHERE course_cd = p_old_course_cd AND
170 unit_set_cd = p_old_unit_set_cd AND
171 us_version_number = p_old_us_version_number AND
172 crv_version_number = v_u_old_version_number AND
173 cal_type = v_u_old_cal_type AND
174 location_cd = v_u_old_location_cd AND
175 attendance_mode = v_u_old_attendance_mode AND
176 attendance_type = v_u_old_attendance_type;
177 c_old_poous_rec c_old_poous%ROWTYPE ;
178
179 -- Cursor to retrieve default program award for the passed program from IGS_PS_AWARD
180 -- that will be validated.
181 CURSOR c_prg_awd( cp_course_cd igs_he_st_prog_all.course_cd%TYPE,
182 cp_version_number igs_he_st_prog_all.version_number%TYPE) IS
183 SELECT map1
184 FROM igs_ps_award, igs_he_code_map_val
185 WHERE course_cd = cp_course_cd AND
186 version_number = cp_version_number AND
187 closed_ind = 'N' AND
188 map2 = award_cd AND
189 association_code = 'OSS_HESA_AWD_ASSOC'
190 ORDER BY default_ind DESC, map1 ASC ;
191
192 -- Cursor to retrieve funding source restriction values for passed program from IGS_FI_FND_SRC_RSTN
193 -- that will be validated.
194 CURSOR c_fnd_src_rstn( cp_course_cd igs_he_st_prog_all.course_cd%TYPE,
195 cp_version_number igs_he_st_prog_all.version_number%TYPE) IS
196 SELECT funding_source
197 FROM igs_fi_fnd_src_rstn
198 WHERE course_cd = cp_course_cd AND
199 version_number = cp_version_number AND
200 dflt_ind = 'Y';
201
202 -- Cursor to retrieve values for new record from IGS_HE_POOUS_ALL
203 -- that will be validated against old values.
204 -- smaddali selecting fundability_cd ,for bug 2730371
205 -- smaddali added field funding_source as part of hefd208 bug#2717751
206 CURSOR c_new_poous IS
207 SELECT attendance_type,
208 franchising_activity,
209 fte_intensity,
210 fte_calc_type,
211 grading_schema_cd,
212 gs_version_number,
213 fee_band,type_of_year,
214 fundability_cd,
215 funding_source
216 FROM igs_he_poous_all
217 WHERE course_cd = p_new_course_cd AND
218 crv_version_number = v_u_new_version_number AND
219 cal_type = v_u_new_cal_type AND
220 location_cd = v_u_new_location_cd AND
221 attendance_mode = v_u_new_attendance_mode AND
222 attendance_type = v_u_new_attendance_type AND
223 unit_set_cd = p_new_unit_set_cd AND
224 us_version_number = p_new_us_version_number ;
225 c_new_poous_rec c_new_poous%ROWTYPE ;
226
227
228
229 -- Required for validation against field in first record.
230 CURSOR term_map IS
231 SELECT map1
232 FROM igs_he_code_map_val
233 WHERE association_code = 'OSS_HESA_TTA_ASSOC' AND
234 map2 = (select term_time_accom
235 from igs_he_en_susa
236 where person_id = p_person_id AND
237 course_cd = p_old_course_cd AND
238 unit_set_cd = p_old_unit_set_cd AND
239 us_version_number = p_old_us_version_number);
240
241 -- smaddali added new cursor for bug 2730371
242 -- Cursor to retrieve fundability_cd for passed program record
243 -- that will be validated against old values.
244 CURSOR c_prg_fundability(cp_course_cd igs_he_st_prog_all.course_cd%TYPE,
245 cp_version_number igs_he_st_prog_all.version_number%TYPE) IS
246 SELECT fundability
247 FROM IGS_he_st_prog_all
248 WHERE course_cd = cp_course_cd AND
249 version_number = cp_version_number ;
250
251 -- smaddali added this cursor for Bug# 2717755
252 -- check if the old and new programs belong to the same program group with system group type CONTINUOUS
253 CURSOR c_prg_grp IS
254 SELECT b.course_group_cd
255 FROM igs_ps_grp_type a, igs_ps_grp_all b, igs_ps_grp_mbr c , igs_ps_grp_mbr d
256 WHERE a.course_group_type = b.course_group_type AND
257 a.closed_ind = 'N' AND
258 a.s_course_group_type = 'CONTINUOUS' AND
259 b.course_group_cd = c.course_group_cd AND
260 b.closed_ind = 'N' AND
261 c.course_cd = p_old_course_cd AND
262 c.version_number = v_u_old_version_number AND
263 b.course_group_cd = d.course_group_cd AND
264 d.course_cd = p_new_course_cd AND
265 d.version_number = v_u_new_version_number ;
266
267 c_prg_grp_rec c_prg_grp%ROWTYPE ;
268 l_cont_progs BOOLEAN;
269
270 -- Fetch the Cost Centers of the Old Program Attempt
271 CURSOR old_susa_cc_dtls_cur( cp_person_id igs_he_en_susa_cc.person_id%TYPE,
272 cp_course_cd igs_he_en_susa_cc.course_cd%TYPE,
273 cp_unit_set_cd igs_he_en_susa_cc.unit_set_cd%TYPE,
274 cp_sequence_number igs_he_en_susa_cc.sequence_number%TYPE) IS
275 SELECT susa.*
276 FROM igs_he_en_susa_cc susa
277 WHERE susa.person_id = cp_person_id
278 AND susa.course_cd = cp_course_cd
279 AND susa.unit_set_cd = cp_unit_set_cd
280 AND susa.sequence_number = cp_sequence_number;
281
282 -- Check whether the Cost Center record already exist in the new program attempt
283 CURSOR new_susa_cc_dtls_cur(cp_person_id igs_he_en_susa_cc.person_id%TYPE,
284 cp_course_cd igs_he_en_susa_cc.course_cd%TYPE,
285 cp_unit_set_cd igs_he_en_susa_cc.unit_set_cd%TYPE,
286 cp_sequence_number igs_he_en_susa_cc.sequence_number%TYPE,
287 cp_cost_centre igs_he_en_susa_cc.cost_centre%TYPE,
288 cp_subject igs_he_en_susa_cc.subject%TYPE) IS
289 SELECT 'X'
290 FROM igs_he_en_susa_cc susa
291 WHERE susa.person_id = cp_person_id
292 AND susa.course_cd = cp_course_cd
293 AND susa.unit_set_cd = cp_unit_set_cd
294 AND susa.sequence_number = cp_sequence_number
295 AND susa.cost_centre = cp_cost_centre
296 AND susa.subject = cp_subject;
297
298 l_rowid VARCHAR2(25) := NULL;
299 l_he_susa_cc_id igs_he_en_susa_cc.he_susa_cc_id%TYPE := NULL ;
300 l_dummy VARCHAR2(1);
301
302 BEGIN
303
304 -- Check if Parameter values are passed incorrectly
305 p_status := 0;
306 l_cont_progs := FALSE ;
307
308 IF p_person_id IS NULL OR
309 p_old_course_cd IS NULL OR
310 p_new_course_cd IS NULL OR
311 p_old_unit_set_cd IS NULL OR
312 p_new_unit_set_cd IS NULL OR
313 p_old_us_version_number IS NULL OR
314 p_new_us_version_number IS NULL
315 THEN
316 p_status := 2;
317 p_message_name := 'IGS_HE_INV_PARAMS';
318 RETURN;
319 END IF;
320
321
322 -- fetch Unique Keys for old program_cd
323 OPEN c_prg_ukeyrec(p_old_course_cd);
324 FETCH c_prg_ukeyrec INTO v_u_old_version_number,
325 v_u_old_cal_type,
326 v_u_old_location_cd,
327 v_u_old_attendance_mode,
328 v_u_old_attendance_type;
329 CLOSE c_prg_ukeyrec;
330
331 -- Fetch unique keys for old unit set attempt record
332 OPEN c_us_ukeyrec(p_old_course_cd,p_old_unit_set_cd,p_old_us_version_number);
333 FETCH c_us_ukeyrec INTO v_u_old_sequence_number;
334 CLOSE c_us_ukeyrec;
335
336 --FETCH Unique Keys for new program_cd
337 OPEN c_prg_ukeyrec(p_new_course_cd);
338 FETCH c_prg_ukeyrec INTO v_u_new_version_number,
339 v_u_new_cal_type,
340 v_u_new_location_cd,
341 v_u_new_attendance_mode,
342 v_u_new_attendance_type;
343 CLOSE c_prg_ukeyrec;
344
345 -- Fetch unique keys for new unit set attempt record
346 OPEN c_us_ukeyrec(p_new_course_cd,p_new_unit_set_cd,p_new_us_version_number);
347 FETCH c_us_ukeyrec INTO v_u_new_sequence_number;
348 CLOSE c_us_ukeyrec;
349
350 -- set the flag if the old and new programs are a continuous study
351 -- smaddali added this new cursor code for build HEFD209 bug2717755
352 OPEN c_prg_grp ;
353 FETCH c_prg_grp INTO c_prg_grp_rec;
354 IF c_prg_grp%FOUND THEN
355 l_cont_progs := TRUE ;
356 END IF;
357 CLOSE c_prg_grp;
358
359
360 -- Check If Old unit set attempt hesa record exists
361 OPEN c_old_susa;
362 FETCH c_old_susa INTO c_old_susa_rec;
363 IF c_old_susa%NOTFOUND THEN
364 -- smaddali added this code instead of raising error ,for bug#2371477
365 close c_old_susa;
366 -- create the new susa hesa details record
367 igs_he_en_susa_pkg.insert_row(
368 x_rowid => v_rowid,
369 x_hesa_en_susa_id => v_hesa_en_susa_id,
370 x_person_id => p_person_id,
371 x_course_cd => p_new_course_cd,
372 x_unit_set_cd => p_new_unit_set_cd,
373 x_us_version_number => p_new_us_version_number,
374 x_sequence_number => v_u_new_sequence_number,
375 x_new_he_entrant_cd => NULL,
376 x_term_time_accom => NULL ,
377 x_disability_allow => NULL ,
378 x_additional_sup_band => NULL,
379 x_sldd_discrete_prov => NULL ,
380 x_study_mode => NULL ,
381 x_study_location => NULL,
382 x_fte_perc_override => NULL,
383 x_franchising_activity => NULL,
384 x_completion_status => NULL ,
385 x_good_stand_marker => NULL ,
386 x_complete_pyr_study_cd => NULL ,
387 x_credit_value_yop1 => NULL,
388 x_credit_value_yop2 => NULL,
389 x_credit_level_achieved1 => NULL ,
390 x_credit_level_achieved2 => NULL ,
391 x_credit_pt_achieved1 => NULL ,
392 x_credit_pt_achieved2 => NULL ,
393 x_credit_level1 => NULL,
394 x_credit_level2 => NULL,
395 x_grad_sch_grade => NULL ,
396 x_mark => NULL ,
397 x_teaching_inst1 => NULL,
398 x_teaching_inst2 => NULL,
399 x_pro_not_taught => NULL,
400 x_fundability_code => NULL ,
401 x_fee_eligibility => NULL ,
402 x_fee_band => NULL ,
403 x_non_payment_reason => NULL,
404 x_student_fee => NULL,
405 x_fte_intensity => NULL ,
406 x_fte_calc_type => NULL ,
407 x_calculated_fte => NULL ,
408 x_type_of_year => NULL,
409 x_mode => 'R',
410 x_credit_value_yop3 => NULL,
411 x_credit_value_yop4 => NULL,
412 x_credit_level_achieved3 => NULL,
413 x_credit_level_achieved4 => NULL,
414 x_credit_pt_achieved3 => NULL,
415 x_credit_pt_achieved4 => NULL,
416 x_credit_level3 => NULL,
417 x_credit_level4 => NULL,
418 x_additional_sup_cost => NULL,
419 x_enh_fund_elig_cd => NULL,
420 x_disadv_uplift_factor => NULL,
421 x_year_stu => NULL);
422
423 ELSE
424
425 close c_old_susa;
426
427 --FETCH Values for old records from IGS_HE_POOUS_ALL, IGS_PS_AWARD, IGS_FI_FND_SRC_RSTN
428 OPEN c_old_poous;
429 FETCH c_old_poous INTO c_old_poous_rec;
430 CLOSE c_old_poous;
431
432 -- Fetch the old program default award
433 v_old_hesa_qual_map1 := NULL;
434 OPEN c_prg_awd(p_old_course_cd,v_u_old_version_number);
435 FETCH c_prg_awd INTO v_old_hesa_qual_map1;
436 CLOSE c_prg_awd;
437
438 -- Fetch the old programs default funding source restriction
439 v_old_prg_funding_source := NULL;
440 OPEN c_fnd_src_rstn(p_old_course_cd,v_u_old_version_number);
441 FETCH c_fnd_src_rstn INTO v_old_prg_funding_source;
442 CLOSE c_fnd_src_rstn;
443
444 --FETCH Values for new records from IGS_HE_POOUS_ALL, IGS_PS_AWARD, IGS_FI_FND_SRC_RSTN
445 OPEN c_new_poous;
446 FETCH c_new_poous INTO c_new_poous_rec;
447 CLOSE c_new_poous;
448
449 -- Fetch the new programs default award code
450 v_new_hesa_qual_map1 := NULL;
451 OPEN c_prg_awd(p_new_course_cd,v_u_new_version_number);
452 FETCH c_prg_awd INTO v_new_hesa_qual_map1;
453 CLOSE c_prg_awd;
454
455 -- Fetch the new programs default funding source restriction
456 v_new_prg_funding_source := NULL;
457 OPEN c_fnd_src_rstn(p_new_course_cd,v_u_new_version_number);
458 FETCH c_fnd_src_rstn INTO v_new_prg_funding_source;
459 CLOSE c_fnd_src_rstn;
460
461 --Get Value for term_time field.
462 v_term_time_map1 := NULL;
463 OPEN term_map;
464 FETCH term_map INTO v_term_time_map1;
465 CLOSE term_map;
466
467 --check conditions for term time accomodation
468 IF v_term_time_map1 <> '6' THEN
469 --copy value from old record to new record
470 v_term_time_accom := c_old_susa_rec.term_time_accom;
471 END IF;
472
473 --check conditions for study_mode.
474 IF c_old_poous_rec.attendance_type = c_new_poous_rec.attendance_type AND
475 v_old_hesa_qual_map1 = v_new_hesa_qual_map1 THEN
476 --copy value from old record to new record
477 V_STUDY_MODE := c_old_susa_rec.study_mode;
478 END IF;
479
480 --check conditions for franchising activity field.
481 IF (c_old_poous_rec.franchising_activity IS NULL AND c_new_poous_rec.franchising_activity IS NULL) OR
482 (c_old_poous_rec.franchising_activity = c_new_poous_rec.franchising_activity ) THEN
483 -- copy value from old record to new record
484 v_franchising_activity := c_old_susa_rec.franchising_activity;
485 END IF;
486
487 --check conditions for fte intensity field.
488 IF (c_old_poous_rec.fte_intensity IS NULL AND c_new_poous_rec.fte_intensity IS NULL) OR
489 (c_old_poous_rec.fte_intensity = c_new_poous_rec.fte_intensity ) THEN
490 --copy value from old record to new record
491 v_fte_intensity := c_old_susa_rec.fte_intensity;
492 END IF;
493
494 --check conditions for fte calculation type field.
495 IF (c_old_poous_rec.fte_calc_type IS NULL AND c_new_poous_rec.fte_calc_type IS NULL) OR
496 (c_old_poous_rec.fte_calc_type = c_new_poous_rec.fte_calc_type) THEN
497 --copy value from old record to new record
498 v_fte_calc_type := c_old_susa_rec.fte_calc_type;
499 END IF;
500
501 --check conditions for completion status, good standing marker, completion
502 --of year of program
503 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 THEN
504 --copy value from old record to new record for fields.
505 v_completion_status := c_old_susa_rec.completion_status;
506 v_good_stand_marker := c_old_susa_rec.good_stand_marker;
507 v_complete_pyr_study_cd := c_old_susa_rec.complete_pyr_study_cd;
508 END IF;
509
510 -- smaddali added new check "or both programs are a continuous study" for HEFD209 build , bug#2717755
511 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 OR l_cont_progs THEN
512 -- smaddali added new_entrant_cd field , bug 2730371
513 v_new_he_entrant_cd := c_old_susa_rec.new_he_entrant_cd ;
514 END IF;
515
516 --check condition for grading schema grade and mark fields.
517 IF c_old_poous_rec.grading_schema_cd = c_new_poous_rec.grading_schema_cd AND
518 c_old_poous_rec.gs_version_number = c_new_poous_rec.gs_version_number THEN
519 --copy old record to new record for fields.
520 v_grad_sch_grade := c_old_susa_rec.grad_sch_grade;
521 v_mark := c_old_susa_rec.mark;
522 END IF;
523
524 --check condition for fundability code.
525 -- smaddali modified fundability validation for nug 2730371
526 -- If fundability for both POOUS are equal then copy old susa fundability
527 -- elsif both poous fundability is null then get from program level
528 IF (c_old_poous_rec.fundability_cd IS NULL AND c_new_poous_rec.fundability_cd IS NULL) THEN
529 v_old_prg_fundability_cd := NULL;
530 v_new_prg_fundability_cd := NULL ;
531 OPEN c_prg_fundability(p_old_course_cd,v_u_old_version_number);
532 FETCH c_prg_fundability into v_old_prg_fundability_cd ;
533 CLOSE c_prg_fundability;
534
535 OPEN c_prg_fundability(p_new_course_cd,v_u_new_version_number);
536 FETCH c_prg_fundability into v_new_prg_fundability_cd;
537 CLOSE c_prg_fundability;
538
539 -- If fundability at both programs is equal then copy old susa fundability
540 -- elsif fundability is null for both programs then get funding source at POOUS
541 IF (v_old_prg_fundability_cd IS NULL AND v_new_prg_fundability_cd IS NULL) THEN
542 -- smaddali added check for funding_source at POOUS level for HEFD208 bug#2717751
543 -- If funding source at both POOUS is equal then copy old susa fundability
544 -- elsif funding source is null for both POOUS then get funding source at Proogram level
545 IF (c_old_poous_rec.funding_source IS NULL AND c_new_poous_rec.funding_source IS NULL) THEN
546 -- If funding sources are both null or equal then copy the old susa fundability
547 IF (v_old_prg_funding_source IS NULL AND v_new_prg_funding_source IS NULL) OR
548 (v_old_prg_funding_source = v_new_prg_funding_source) THEN
549 --copy old record to new record for field.
550 v_fundability_code := c_old_susa_rec.fundability_code;
551 END IF;
552 ELSIF c_old_poous_rec.funding_source = c_new_poous_rec.funding_source THEN
553 --copy old record to new record for field.
554 v_fundability_code := c_old_susa_rec.fundability_code;
555 END IF ;
556 ELSIF v_old_prg_fundability_cd = v_new_prg_fundability_cd THEN
557 --copy old record to new record for field.
558 v_fundability_code := c_old_susa_rec.fundability_code;
559 END IF ;
560 ELSIF c_old_poous_rec.fundability_cd = c_new_poous_rec.fundability_cd THEN
561 --copy old record to new record for field.
562 v_fundability_code := c_old_susa_rec.fundability_code;
563 END IF;
564
565 --check condition for fee band field.
566 IF (c_old_poous_rec.fee_band IS NULL AND c_new_poous_rec.fee_band IS NULL ) OR
567 (c_old_poous_rec.fee_band = c_new_poous_rec.fee_band ) THEN
568 --copy old record to new record.
569 v_fee_band := c_old_susa_rec.fee_band;
570 END IF;
571
572
573 --check condition for type of program year
574 IF (c_old_poous_rec.type_of_year IS NULL AND c_new_poous_rec.type_of_year IS NULL) OR
575 (c_old_poous_rec.type_of_year = c_new_poous_rec.type_of_year) THEN
576 --copy old record to new record.
577 v_type_of_year := c_old_susa_rec.type_of_year;
578 END IF;
579
580
581 -- INSERT Values into Record.
582 igs_he_en_susa_pkg.insert_row(
583 x_rowid => v_rowid,
584 x_hesa_en_susa_id => v_hesa_en_susa_id,
585 x_person_id => p_person_id,
586 x_course_cd => p_new_course_cd,
587 x_unit_set_cd => p_new_unit_set_cd,
588 x_us_version_number => p_new_us_version_number,
589 x_sequence_number => v_u_new_sequence_number,
590 x_new_he_entrant_cd => v_new_he_entrant_cd,
591 x_term_time_accom => v_term_time_accom,
592 x_disability_allow => c_old_susa_rec.disability_allow,
593 x_additional_sup_band => c_old_susa_rec.additional_sup_band,
594 x_sldd_discrete_prov => c_old_susa_rec.sldd_discrete_prov,
595 x_study_mode => v_study_mode,
596 x_study_location => NULL,
597 x_fte_perc_override => NULL,
598 x_franchising_activity => v_franchising_activity,
599 x_completion_status => v_completion_status,
600 x_good_stand_marker => v_good_stand_marker,
601 x_complete_pyr_study_cd => v_complete_pyr_study_cd,
602 x_credit_value_yop1 => NULL,
603 x_credit_value_yop2 => NULL,
604 x_credit_level_achieved1 => c_old_susa_rec.credit_level_achieved1,
605 x_credit_level_achieved2 => c_old_susa_rec.credit_level_achieved2,
606 x_credit_pt_achieved1 => c_old_susa_rec.credit_pt_achieved1,
607 x_credit_pt_achieved2 => c_old_susa_rec.credit_pt_achieved2,
608 x_credit_level1 => NULL,
609 x_credit_level2 => NULL,
610 x_grad_sch_grade => v_grad_sch_grade,
611 x_mark => v_mark,
612 x_teaching_inst1 => NULL,
613 x_teaching_inst2 => NULL,
614 x_pro_not_taught => NULL,
615 x_fundability_code => v_fundability_code,
616 x_fee_eligibility => c_old_susa_rec.fee_eligibility,
617 x_fee_band => v_fee_band,
618 x_non_payment_reason => c_old_susa_rec.non_payment_reason,
619 x_student_fee => NULL,
620 x_fte_intensity => v_fte_intensity,
621 x_fte_calc_type => v_fte_calc_type,
622 x_calculated_fte => c_old_susa_rec.calculated_fte,
623 x_type_of_year => v_type_of_year,
624 x_mode => 'R',
625 x_credit_value_yop3 => NULL,
626 x_credit_value_yop4 => NULL,
627 x_credit_level_achieved3 => c_old_susa_rec.credit_level_achieved3,
628 x_credit_level_achieved4 => c_old_susa_rec.credit_level_achieved4,
629 x_credit_pt_achieved3 => c_old_susa_rec.credit_pt_achieved3,
630 x_credit_pt_achieved4 => c_old_susa_rec.credit_pt_achieved4,
631 x_credit_level3 => NULL,
632 x_credit_level4 => NULL,
633 x_additional_sup_cost => NULL,
634 x_enh_fund_elig_cd => NULL,
635 x_disadv_uplift_factor => NULL,
636 x_year_stu => NULL);
637
638 END IF; -- if old susa hesa details record is not found
639
640 -- CREATE THE STUDENT SET ATTEMPT COST CENTRE RECORD
641
642 -- Loop through all the records in igs_he_en_susa_cc table for the old unit set attempt
643 -- and insert if the record does not exist for new Unit Set Attempt
644 FOR old_susa_cc_dtls_rec IN old_susa_cc_dtls_cur( p_person_id, p_old_course_cd,
645 p_old_unit_set_cd, v_u_old_sequence_number ) LOOP
646
647 OPEN new_susa_cc_dtls_cur( p_person_id, p_new_course_cd, p_new_unit_set_cd, v_u_new_sequence_number,
648 old_susa_cc_dtls_rec.cost_centre, old_susa_cc_dtls_rec.subject );
649 FETCH new_susa_cc_dtls_cur INTO l_dummy;
650 IF new_susa_cc_dtls_cur%NOTFOUND THEN
651
652 -- create the new student unit set attempt cost centre record
653 igs_he_en_susa_cc_pkg.insert_row (
654 x_rowid => l_rowid,
655 x_he_susa_cc_id => l_he_susa_cc_id,
656 x_person_id => p_person_id,
657 x_course_cd => p_new_course_cd,
658 x_unit_set_cd => p_new_unit_set_cd,
659 x_sequence_number => v_u_new_sequence_number,
660 x_cost_centre => old_susa_cc_dtls_rec.cost_centre,
661 x_subject => old_susa_cc_dtls_rec.subject,
662 x_proportion => old_susa_cc_dtls_rec.proportion,
663 x_mode => 'R' );
664
665 END IF;
666 CLOSE new_susa_cc_dtls_cur;
667
668 END LOOP;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 ROLLBACK;
673 p_status := 2;
674 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
675 Fnd_message.set_token('NAME', 'IGS_HE_PROG_TRANSFER_PKG.Hesa_Stud_Susa_Trans');
676 igs_ge_msg_stack.add;
677 RETURN;
678
679 END hesa_stud_susa_trans;
680
681
682 /*---------------------------------------------------------------------
683 This procedure will copy old record details to a new record for
684 tables IGS_HE_ST_SPA_ALL and IGS_HE_ST_SPA_UT_ALL.
685
686 Output : p_message_name - Exit error message
687 p_status - Return code for the procedure.
688 0 - Success
689 1 - Warning
690 2 - Failure
691 -- smvk 06-Jun-2003 Bug # 2858436. Modified c_old_awardcode to select un closed award code only.
692 -- smaddali (bug#2371477) 16-may-2002 modified this procedure to create the old and new uk statistics
693 -- record when the old uk statistics record is not found
694 -- Bayadav 05-DEC-2002 Included the check for HESA qualaim for the program code to set the student instance number
695 -- as a part of bug 2671155
696 -- smaddali 30-dec-2002 included transfer of highest_qual_on_entry and ucas_tariff_score for bug 2728756
697 -- smaddali modified procedure to copy some fields for continuous programs of study ,bug 2717755
698 -- pmarada 23-aug-2003 modified as per HECR008 build, deriving student instance number value.
699 -- ayedubat 01-SEP-2003 Changed the procedure to copy the HESA Statitistic Student Program Attempt Cost Centre details
700 -- for HE207FD bug, 2717753
701 ---------------------------------------------------------------------*/
702
703 PROCEDURE hesa_stud_stat_trans(
704 p_person_id IN NUMBER,
705 p_old_course_cd IN VARCHAR2,
706 p_new_course_cd IN VARCHAR2,
707 p_status OUT NOCOPY VARCHAR2,
708 p_message_name OUT NOCOPY VARCHAR2) IS
709
710 -- Variables to hold old values for third record.
711 v_old_qualification_level igs_he_st_spa_ut_all.qualification_level%TYPE;
712 v_old_number_of_qual igs_he_st_spa_ut_all.number_of_qual%TYPE;
713 v_old_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE;
714
715 -- Variables to hold final values for second record
716 v_hesa_st_spa_id igs_he_st_spa_all.hesa_st_spa_id%TYPE;
717 v_student_qual_aim igs_he_st_spa_all.student_qual_aim%TYPE;
718 v_student_inst_number igs_he_st_spa_all.student_inst_number%TYPE;
719 v_commencement_dt igs_he_st_spa_all.commencement_dt%TYPE;
720 v_fe_student_marker igs_he_st_spa_all.fe_student_marker%TYPE;
721 v_domicile_cd igs_he_st_spa_all.domicile_cd%TYPE;
722 v_postcode igs_he_st_spa_all.postcode%TYPE;
723 v_special_student igs_he_st_spa_all.special_student%TYPE;
724 v_social_class_ind igs_he_st_spa_all.social_class_ind%TYPE;
725 v_occupation_code igs_he_st_spa_all.occupation_code%TYPE;
726 v_occcode igs_he_st_spa_all.occcode%TYPE;
727 v_student_fe_qual_aim igs_he_st_spa_all.student_fe_qual_aim%TYPE;
728 v_teacher_train_prog_id igs_he_st_spa_all.teacher_train_prog_id%TYPE;
729 v_nhs_funding_source igs_he_st_spa_all.nhs_funding_source%TYPE;
730 v_qual_aim_subj1 igs_he_st_spa_all.qual_aim_subj1%TYPE;
731 v_qual_aim_subj2 igs_he_st_spa_all.qual_aim_subj2%TYPE;
732 v_qual_aim_subj3 igs_he_st_spa_all.qual_aim_subj3%TYPE;
733 v_qual_aim_proportion igs_he_st_spa_all.qual_aim_proportion%TYPE;
734 v_teach_map1 igs_he_code_map_val.map1%TYPE;
735 v_new_hesa_qual_map1 igs_he_code_map_val.map1%TYPE;
736 v_old_hesa_qual_map1 igs_he_code_map_val.map1%TYPE;
737 v_rowid VARCHAR2(25);
738 v_org_id NUMBER := igs_ge_gen_003.get_org_id;
739 v_highest_qual_on_entry igs_he_st_spa_all.highest_qual_on_entry%TYPE;
740 v_total_ucas_tariff igs_he_st_spa_all.total_ucas_tariff%TYPE;
741
742 -- Variables to hold final values for third record
743 v_hesa_st_spau_id igs_he_st_spa_ut_all.hesa_st_spau_id%TYPE;
744 v_qualification_level igs_he_st_spa_ut_all.qualification_level%TYPE;
745 v_number_of_qual igs_he_st_spa_ut_all.number_of_qual%TYPE;
746 v_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE;
747 v_ut_rowid VARCHAR2(25);
748
749 -- Variables to hold old and new values from other tables for record.
750 v_old_tmp_prgfldstudy igs_ps_field_study.field_of_study%TYPE;
751 v_new_tmp_prgfldstudy igs_ps_field_study.field_of_study%TYPE;
752
753 -- Variables to hold old record unique key values
754 v_u_old_version_number igs_en_stdnt_ps_att.version_number%TYPE;
755 v_u_new_version_number igs_en_stdnt_ps_att.version_number%TYPE;
756 -- variables to hold old record program field of study values
757 v_u_old_program_fld_study igs_ps_field_study.field_of_study%TYPE;
758 v_u_new_program_fld_study igs_ps_field_study.field_of_study%TYPE;
759
760 -- Cursor required to retrieve version_number which is required as unique key for another cursor.
761 CURSOR old_ukeyrec2 IS
762 SELECT version_number
763 FROM igs_en_stdnt_ps_att
764 WHERE person_id = p_person_id AND
765 course_cd = p_old_course_cd;
766
767 -- Cursor required to retrieve version_number which is required as unique key for another cursor.
768 CURSOR new_ukeyrec2 IS
769 SELECT version_number
770 FROM igs_en_stdnt_ps_att
771 WHERE person_id = p_person_id AND
772 course_cd = p_new_course_cd;
773
774 -- Cursor to retrieve record values for award code from IGS_PS_AWARD
775 CURSOR c_awardcode (cp_course_cd igs_ps_award.course_cd%TYPE ,
776 cp_version_number igs_ps_award.version_number%TYPE) IS
777 SELECT map1
778 FROM igs_ps_award , igs_he_code_map_val
779 WHERE course_cd = cp_course_cd AND
780 version_number = cp_version_number AND
781 closed_ind = 'N' AND
782 map2 = award_cd AND
783 association_code = 'OSS_HESA_AWD_ASSOC'
784 ORDER BY default_ind DESC, map1 ASC ;
785
786 -- Cursor to retrieve old record values for program field of study record 2 from IGS_PS_FIELD_STUDY
787 CURSOR c_old_prg_fldstudy IS
788 SELECT field_of_study
789 FROM igs_ps_field_study
790 WHERE course_cd = p_old_course_cd AND
791 version_number = v_u_old_version_number AND
792 major_field_ind = 'Y';
793
794 -- Cursor to retrieve new record values for program field of study record 2 from IGS_PS_FIELD_STUDY
795 CURSOR c_new_prg_fldstudy IS
796 SELECT field_of_study
797 FROM igs_ps_field_study
798 WHERE course_cd = p_new_course_cd AND
799 version_number = v_u_new_version_number AND
800 major_field_ind = 'Y';
801
802
803 -- Cursor to retrive values required for old program attempt hesa record.
804 -- smaddali modified this cursor to select highest_ualification_on_entry,total_ucas_tariff also ,bug 2728756
805 CURSOR c_old_spa IS
806 SELECT student_inst_number,
807 commencement_dt,
808 fe_student_marker,
809 domicile_cd,
810 postcode,
811 special_student,
812 social_class_ind,
813 occupation_code,
814 occcode,
815 student_qual_aim,
816 student_fe_qual_aim,
817 teacher_train_prog_id,
818 nhs_funding_source,
819 ufi_place,
820 nhs_employer,
821 qual_aim_subj1,
822 qual_aim_subj2,
823 qual_aim_subj3,
824 qual_aim_proportion ,
825 highest_qual_on_entry,
826 total_ucas_tariff
827 FROM igs_he_st_spa_all
828 WHERE person_id = p_person_id AND
829 course_cd = p_old_course_cd;
830 c_old_spa_rec c_old_spa%ROWTYPE;
831
832 -- Cursor required for validation against field in second record.
833 -- get the hesa code mapped to the oss tacher code
834 CURSOR teach_map(cp_teacher_train_prog_id igs_he_code_map_val.map2%TYPE) IS
835 SELECT map1
836 FROM igs_he_code_map_val
837 WHERE association_code = 'OSS_HESA_TTCID_ASSOC' AND
838 map2 = cp_teacher_train_prog_id;
839
840 -- Cursor to retrieve values required for ucas tariff record.
841 CURSOR ucas_tariff IS
842 SELECT qualification_level,
843 number_of_qual,
844 tariff_score
845 FROM igs_he_st_spa_ut
846 WHERE person_id = p_person_id AND
847 course_cd = p_old_course_cd;
848
849 CURSOR cur_std_inst_num(cp_person_id igs_he_st_spa_all.person_id%TYPE) IS
850 SELECT student_inst_number
851 FROM igs_he_st_spa_all
852 WHERE person_id = cp_person_id;
853
854 l_std_inst_num NUMBER;
855
856 -- smaddali added this cursor for Bug# 2717755
857 -- check if the old and new programs belong to the same program group with system group type CONTINUOUS
858 CURSOR c_prg_grp IS
859 SELECT b.course_group_cd
860 FROM igs_ps_grp_type a, igs_ps_grp_all b, igs_ps_grp_mbr c , igs_ps_grp_mbr d
861 WHERE a.course_group_type = b.course_group_type AND
862 a.closed_ind = 'N' AND
863 a.s_course_group_type = 'CONTINUOUS' AND
864 b.course_group_cd = c.course_group_cd AND
865 b.closed_ind = 'N' AND
866 c.course_cd = p_old_course_cd AND
867 c.version_number = v_u_old_version_number AND
868 b.course_group_cd = d.course_group_cd AND
869 d.course_cd = p_new_course_cd AND
870 d.version_number = v_u_new_version_number ;
871
872 c_prg_grp_rec c_prg_grp%ROWTYPE ;
873 l_cont_progs BOOLEAN;
874
875 -- Fetch the Cost Centers of the Old Program Attempt
876 CURSOR old_spa_cc_dtls_cur( cp_person_id IGS_HE_ST_SPA_CC.person_id%TYPE,
877 cp_course_cd IGS_HE_ST_SPA_CC.course_cd%TYPE) IS
878 SELECT spa.*
879 FROM IGS_HE_ST_SPA_CC spa
880 WHERE spa.person_id = cp_person_id
881 AND spa.course_cd = cp_course_cd ;
882
883 -- Check whether the Cost Center record already exist in the new program attempt
884 CURSOR new_spa_cc_dtls_cur( cp_person_id IGS_HE_ST_SPA_CC.person_id%TYPE,
885 cp_course_cd IGS_HE_ST_SPA_CC.course_cd%TYPE,
886 cp_cost_centre IGS_HE_ST_SPA_CC.cost_centre%TYPE,
887 cp_subject IGS_HE_ST_SPA_CC.subject%TYPE) IS
888 SELECT 'X'
889 FROM IGS_HE_ST_SPA_CC spa
890 WHERE spa.person_id = cp_person_id
891 AND spa.course_cd = cp_course_cd
892 AND cost_centre = cp_cost_centre
893 AND subject = cp_subject;
894
895 l_rowid VARCHAR2(25) := NULL;
896 l_he_spa_cc_id igs_he_st_spa_cc.he_spa_cc_id%TYPE := NULL ;
897 l_dummy VARCHAR2(1);
898
899 BEGIN
900
901 -- Check Parameter values passed in correctly
902
903 p_status := 0;
904 l_cont_progs := FALSE ;
905
906 IF p_person_id IS NULL OR
907 p_old_course_cd IS NULL OR
908 p_new_course_cd IS NULL
909 THEN
910 p_status := 2;
911 p_message_name := 'IGS_HE_INV_PARAMS';
912 RETURN;
913 END IF;
914
915 -- fetch unique keys for old program_cd from igs_en_stdnt_ps_att
916 OPEN old_ukeyrec2;
917 FETCH old_ukeyrec2 INTO v_u_old_version_number;
918 CLOSE old_ukeyrec2;
919
920 -- fetch unique keys for new program_cd from igs_en_stdnt_ps_att
921 OPEN new_ukeyrec2;
922 FETCH new_ukeyrec2 INTO v_u_new_version_number;
923 CLOSE new_ukeyrec2;
924
925 -- fetch award code for old program_cd
926 OPEN c_awardcode( p_old_course_cd, v_u_old_version_number);
927 FETCH c_awardcode INTO v_old_hesa_qual_map1;
928 CLOSE c_awardcode;
929
930 -- fetch award code for new program_cd
931 OPEN c_awardcode(p_new_course_cd, v_u_new_version_number);
932 FETCH c_awardcode INTO v_new_hesa_qual_map1;
933 CLOSE c_awardcode;
934
935 --Fetch program field of study for old program
936 OPEN c_old_prg_fldstudy ;
937 FETCH c_old_prg_fldstudy INTO v_old_tmp_prgfldstudy;
938 CLOSE c_old_prg_fldstudy ;
939
940
941 --Fetch program field of study for new program
942 OPEN c_new_prg_fldstudy ;
943 FETCH c_new_prg_fldstudy INTO v_new_tmp_prgfldstudy;
944 CLOSE c_new_prg_fldstudy ;
945
946 -- set the flag if the old and new programs are a continuous study
947 -- smaddali added this new cursor code for build HEFD209 bug2717755
948 OPEN c_prg_grp ;
949 FETCH c_prg_grp INTO c_prg_grp_rec;
950 IF c_prg_grp%FOUND THEN
951 l_cont_progs := TRUE ;
952 END IF;
953 CLOSE c_prg_grp;
954
955 -- SECONDLY CREATE A UK STATISTICS - SPA RECORD.
956 -- Check If Old record exists and if so then fetch values into variables.
957 OPEN c_old_spa;
958 FETCH c_old_spa INTO c_old_spa_rec;
959 IF c_old_spa%NOTFOUND THEN
960 CLOSE c_old_spa;
961 -- Derive the student instance number value and use this value while creating
962 -- new record in igs_he_st_spa_all table, added as per
963 -- HECR008-alpha numeric student instance number CR
964 l_std_inst_num := 1;
965 FOR cur_std_inst_num_rec IN cur_std_inst_num(p_person_id) LOOP
966 BEGIN
967 IF NVL(TO_NUMBER(cur_std_inst_num_rec.student_inst_number),0) >= l_std_inst_num THEN
968 l_std_inst_num := TO_NUMBER(cur_std_inst_num_rec.Student_inst_number) + 1;
969 END IF;
970 EXCEPTION
971 WHEN VALUE_ERROR THEN
972 NULL;
973 END ;
974 END LOOP;
975
976 v_student_inst_number := l_std_inst_num ;
977
978 -- create the SPA record for the old program attempt
979 --Insert Values Into Record
980 igs_he_st_spa_all_pkg.insert_row(
981 x_rowid => v_rowid,
982 x_hesa_st_spa_id => v_hesa_st_spa_id,
983 x_org_id => v_org_id,
984 x_person_id => p_person_id,
985 x_course_cd => p_old_course_cd,
986 x_version_number => v_u_old_version_number,
987 x_fe_student_marker => NULL,
988 x_domicile_cd => NULL,
989 x_inst_last_attended => NULL,
990 x_year_left_last_inst => NULL,
991 x_highest_qual_on_entry => NULL,
992 x_date_qual_on_entry_calc => NULL,
993 x_a_level_point_score => NULL,
994 x_highers_points_scores => NULL,
995 x_occupation_code => NULL,
996 x_commencement_dt => NULL,
997 x_special_student => NULL ,
998 x_student_qual_aim => NULL ,
999 x_student_fe_qual_aim => NULL ,
1000 x_teacher_train_prog_id => NULL ,
1001 x_itt_phase => NULL,
1002 x_bilingual_itt_marker => NULL,
1003 x_teaching_qual_gain_sector => NULL,
1004 x_teaching_qual_gain_subj1 => NULL,
1005 x_teaching_qual_gain_subj2 => NULL,
1006 x_teaching_qual_gain_subj3 => NULL,
1007 x_student_inst_number => v_student_inst_number,
1008 x_destination => NULL,
1009 x_itt_prog_outcome => NULL,
1010 x_hesa_return_name => NULL,
1011 x_hesa_return_id => NULL,
1012 x_hesa_submission_name => NULL,
1013 x_associate_ucas_number => NULL,
1014 x_associate_scott_cand => NULL,
1015 x_associate_teach_ref_num => NULL,
1016 x_associate_nhs_reg_num => NULL,
1017 x_nhs_funding_source => NULL ,
1018 x_ufi_place => NULL,
1019 x_postcode => NULL ,
1020 x_social_class_ind => NULL ,
1021 x_occcode => NULL ,
1022 x_total_ucas_tariff => NULL,
1023 x_nhs_employer => NULL ,
1024 x_return_type => NULL,
1025 x_qual_aim_subj1 => NULL,
1026 x_qual_aim_subj2 => NULL,
1027 x_qual_aim_subj3 => NULL,
1028 x_qual_aim_proportion => NULL,
1029 x_exclude_flag => NULL,
1030 x_mode => 'R' );
1031
1032 -- create the SPA record for the new program attempt
1033 -- check condition for Student Instance Number ,
1034 -- if a the HESA qual aim is different or the two programs are not a continuous study then it is considered
1035 -- as a different qualification aim ,clarified by SARA
1036 -- smaddali added the check to copy student instance number when the two programs are a continuous study ,Build HEFD209 bug#2717755
1037 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 OR l_cont_progs THEN
1038 --copy value from old spa record created above to new record.
1039 v_student_inst_number := v_student_inst_number;
1040 ELSE
1041 -- Derive the student instance number value and use this value while creating
1042 -- new record in igs_he_st_spa_all table, added as per
1043 -- HECR008-alpha numeric student instance number CR
1044 l_std_inst_num := 1;
1045 FOR cur_std_inst_num_rec IN cur_std_inst_num(p_person_id) LOOP
1046 BEGIN
1047 IF NVL(TO_NUMBER(cur_std_inst_num_rec.student_inst_number),0) >= l_std_inst_num THEN
1048 l_std_inst_num := TO_NUMBER(cur_std_inst_num_rec.Student_inst_number) + 1;
1049 END IF;
1050 EXCEPTION
1051 WHEN VALUE_ERROR THEN
1052 NULL;
1053 END ;
1054 END LOOP;
1055 v_student_inst_number := l_std_inst_num;
1056 END IF;
1057
1058 v_rowid := NULL ;
1059 v_hesa_st_spa_id := NULL ;
1060
1061 igs_he_st_spa_all_pkg.insert_row(
1062 x_rowid => v_rowid,
1063 x_hesa_st_spa_id => v_hesa_st_spa_id,
1064 x_org_id => v_org_id,
1065 x_person_id => p_person_id,
1066 x_course_cd => p_new_course_cd,
1067 x_version_number => v_u_new_version_number,
1068 x_fe_student_marker => NULL,
1069 x_domicile_cd => NULL,
1070 x_inst_last_attended => NULL,
1071 x_year_left_last_inst => NULL,
1072 x_highest_qual_on_entry => NULL,
1073 x_date_qual_on_entry_calc => NULL,
1074 x_a_level_point_score => NULL,
1075 x_highers_points_scores => NULL,
1076 x_occupation_code => NULL,
1077 x_commencement_dt => NULL,
1078 x_special_student => NULL ,
1079 x_student_qual_aim => NULL ,
1080 x_student_fe_qual_aim => NULL ,
1081 x_teacher_train_prog_id => NULL ,
1082 x_itt_phase => NULL,
1083 x_bilingual_itt_marker => NULL,
1084 x_teaching_qual_gain_sector => NULL,
1085 x_teaching_qual_gain_subj1 => NULL,
1086 x_teaching_qual_gain_subj2 => NULL,
1087 x_teaching_qual_gain_subj3 => NULL,
1088 x_student_inst_number => v_student_inst_number,
1089 x_destination => NULL,
1090 x_itt_prog_outcome => NULL,
1091 x_hesa_return_name => NULL,
1092 x_hesa_return_id => NULL,
1093 x_hesa_submission_name => NULL,
1094 x_associate_ucas_number => NULL,
1095 x_associate_scott_cand => NULL,
1096 x_associate_teach_ref_num => NULL,
1097 x_associate_nhs_reg_num => NULL,
1098 x_nhs_funding_source => NULL ,
1099 x_ufi_place => NULL,
1100 x_postcode => NULL ,
1101 x_social_class_ind => NULL ,
1102 x_occcode => NULL ,
1103 x_total_ucas_tariff => NULL,
1104 x_nhs_employer => NULL ,
1105 x_return_type => NULL,
1106 x_qual_aim_subj1 => NULL,
1107 x_qual_aim_subj2 => NULL,
1108 x_qual_aim_subj3 => NULL,
1109 x_qual_aim_proportion => NULL,
1110 x_exclude_flag => NULL,
1111 x_mode => 'R');
1112
1113 ELSE -- if old spa record exists then copy old uk statistics details to the new spa record
1114
1115 CLOSE c_old_spa;
1116
1117 -- check condition for Student Instance Number
1118 -- smaddali added the check to copy student instance number when the two programs
1119 -- are a continuous study ,Build HEFD209 bug#2717755
1120 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 OR l_cont_progs THEN
1121 --copy value from old record to new record.
1122 v_student_inst_number := c_old_spa_rec.student_inst_number;
1123 ELSE
1124 -- Derive the student instance number value and use this value while creating
1125 -- new record in igs_he_st_spa_all table, added as per
1126 -- HECR008-alpha numeric student instance number CR
1127 l_std_inst_num := 1;
1128 FOR cur_std_inst_num_rec IN cur_std_inst_num(p_person_id) LOOP
1129 BEGIN
1130 IF NVL(TO_NUMBER(cur_std_inst_num_rec.student_inst_number),0) >= l_std_inst_num THEN
1131 l_std_inst_num := TO_NUMBER(cur_std_inst_num_rec.Student_inst_number) + 1;
1132 END IF;
1133 EXCEPTION
1134 WHEN VALUE_ERROR THEN
1135 NULL;
1136 END ;
1137 END LOOP;
1138 v_student_inst_number := l_std_inst_num ;
1139 END IF;
1140
1141 --check condition for commencment date, fe student marker, domicile_cd, postcode,
1142 --special student, social class, occupation code, old structure occupation code,
1143 --NHS funding source, general qualification aim, fe general qualification aim.
1144 -- smaddali added new check "or both programs are a continuous study" for HEFD209 build , bug2717755
1145 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 OR l_cont_progs THEN
1146 --copy value from old fields to new fields.
1147 v_commencement_dt := c_old_spa_rec.commencement_dt;
1148 v_domicile_cd := c_old_spa_rec.domicile_cd;
1149 v_postcode := c_old_spa_rec.postcode;
1150 v_social_class_ind := c_old_spa_rec.social_class_ind;
1151 v_occupation_code := c_old_spa_rec.occupation_code;
1152 v_occcode := c_old_spa_rec.occcode;
1153 --smaddali added code to transfer highest_qual_on_entry,total_ucas_tariff field ,bug 2728756
1154 v_highest_qual_on_entry := c_old_spa_rec.highest_qual_on_entry ;
1155 v_total_ucas_tariff := c_old_spa_rec.total_ucas_tariff ;
1156 END IF ;
1157
1158 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 THEN
1159
1160 v_fe_student_marker := c_old_spa_rec.fe_student_marker;
1161 v_special_student := c_old_spa_rec.special_student;
1162 v_nhs_funding_source := c_old_spa_rec.nhs_funding_source;
1163 v_student_qual_aim := c_old_spa_rec.student_qual_aim;
1164 v_student_fe_qual_aim := c_old_spa_rec.student_fe_qual_aim;
1165
1166 --extra check for teacher_train_prog_id
1167 -- if hesa code of the qualification aim is 12,13or 20 then copy teacher train prog id
1168 -- fetch hesa code mapeed to the oss qualification aim value, required for validating teacher_train_prog_id
1169 v_teach_map1 := NULL ;
1170 OPEN teach_map( c_old_spa_rec.teacher_train_prog_id ) ;
1171 FETCH teach_map INTO v_teach_map1;
1172 CLOSE teach_map;
1173
1174 IF v_teach_map1 IN ('12','13','20') THEN
1175
1176 --copy value from old field to new field
1177 v_teacher_train_prog_id := c_old_spa_rec.teacher_train_prog_id;
1178
1179 END IF;
1180
1181 IF v_old_tmp_prgfldstudy = v_new_tmp_prgfldstudy THEN
1182
1183 v_qual_aim_subj1 := c_old_spa_rec.qual_aim_subj1 ;
1184 v_qual_aim_subj2 := c_old_spa_rec.qual_aim_subj2 ;
1185 v_qual_aim_subj3 := c_old_spa_rec.qual_aim_subj3;
1186 v_qual_aim_proportion := c_old_spa_rec.qual_aim_proportion ;
1187
1188 END IF;
1189
1190
1191 END IF; -- if old program's qualification aim is same as that of new program
1192
1193 --Insert Values Into Record
1194 igs_he_st_spa_all_pkg.insert_row(
1195 x_rowid => v_rowid,
1196 x_hesa_st_spa_id => v_hesa_st_spa_id,
1197 x_org_id => v_org_id,
1198 x_person_id => p_person_id,
1199 x_course_cd => p_new_course_cd,
1200 x_version_number => v_u_new_version_number,
1201 x_fe_student_marker => v_fe_student_marker,
1202 x_domicile_cd => v_domicile_cd,
1203 x_inst_last_attended => NULL,
1204 x_year_left_last_inst => NULL,
1205 x_highest_qual_on_entry => v_highest_qual_on_entry,
1206 x_date_qual_on_entry_calc => NULL,
1207 x_a_level_point_score => NULL,
1208 x_highers_points_scores => NULL,
1209 x_occupation_code => v_occupation_code,
1210 x_commencement_dt => v_commencement_dt,
1211 x_special_student => v_special_student,
1212 x_student_qual_aim => v_student_qual_aim,
1213 x_student_fe_qual_aim => v_student_fe_qual_aim,
1214 x_teacher_train_prog_id => v_teacher_train_prog_id,
1215 x_itt_phase => NULL,
1216 x_bilingual_itt_marker => NULL,
1217 x_teaching_qual_gain_sector => NULL,
1218 x_teaching_qual_gain_subj1 => NULL,
1219 x_teaching_qual_gain_subj2 => NULL,
1220 x_teaching_qual_gain_subj3 => NULL,
1221 x_student_inst_number => v_student_inst_number,
1222 x_destination => NULL,
1223 x_itt_prog_outcome => NULL,
1224 x_hesa_return_name => NULL,
1225 x_hesa_return_id => NULL,
1226 x_hesa_submission_name => NULL,
1227 x_associate_ucas_number => NULL,
1228 x_associate_scott_cand => NULL,
1229 x_associate_teach_ref_num => NULL,
1230 x_associate_nhs_reg_num => NULL,
1231 x_nhs_funding_source => v_nhs_funding_source,
1232 x_ufi_place => c_old_spa_rec.ufi_place,
1233 x_postcode => v_postcode,
1234 x_social_class_ind => v_social_class_ind,
1235 x_occcode => v_occcode,
1236 x_total_ucas_tariff => v_total_ucas_tariff,
1237 x_nhs_employer => c_old_spa_rec.nhs_employer,
1238 x_return_type => NULL,
1239 x_qual_aim_subj1 => v_qual_aim_subj1,
1240 x_qual_aim_subj2 => v_qual_aim_subj2,
1241 x_qual_aim_subj3 => v_qual_aim_subj3,
1242 x_qual_aim_proportion => v_qual_aim_proportion,
1243 x_exclude_flag => NULL,
1244 x_mode => 'R');
1245
1246 -- CREATE UCAS TARIFF RECORD
1247 -- Fetch Old Values into Variables.
1248 -- smaddali added new check "or both programs are a continuous study" for HEFD209 build , bug2717755
1249 IF v_old_hesa_qual_map1 = v_new_hesa_qual_map1 OR l_cont_progs THEN
1250
1251 OPEN ucas_tariff;
1252 LOOP
1253 FETCH ucas_tariff INTO v_qualification_level,
1254 v_number_of_qual,
1255 v_tariff_score;
1256 EXIT WHEN ucas_tariff%NOTFOUND;
1257
1258 --Insert Values into Record
1259 igs_he_st_spa_ut_all_pkg.insert_row(
1260 x_rowid => v_ut_rowid,
1261 x_hesa_st_spau_id => v_hesa_st_spau_id,
1262 x_org_id => v_org_id,
1263 x_person_id => p_person_id,
1264 x_course_cd => p_new_course_cd,
1265 x_version_number => v_u_new_version_number,
1266 x_qualification_level => v_qualification_level,
1267 x_number_of_qual => v_number_of_qual,
1268 x_tariff_score => v_tariff_score,
1269 x_mode => 'R' );
1270 END LOOP;
1271 CLOSE ucas_tariff;
1272 END IF; -- if old qualification aim is equal to the new qualification aim
1273
1274 END IF; -- if old UK statistics record found
1275
1276 -- CREATE THE UK STATISTICS COST CENTRE - SPA RECORD
1277
1278 -- Loop through all the records in IGS_HE_ST_SPA_CC table for the old program attempt
1279 -- and insert if the record does not exist for new Program Attempt
1280 FOR old_spa_cc_dtls_rec IN old_spa_cc_dtls_cur( p_person_id, p_old_course_cd ) LOOP
1281
1282 OPEN new_spa_cc_dtls_cur( p_person_id, p_new_course_cd,
1283 old_spa_cc_dtls_rec.cost_centre, old_spa_cc_dtls_rec.subject );
1284 FETCH new_spa_cc_dtls_cur INTO l_dummy;
1285 IF new_spa_cc_dtls_cur%NOTFOUND THEN
1286
1287 -- create the new student program attempt cost centre record
1288 igs_he_st_spa_cc_pkg.insert_row (
1289 x_rowid => l_rowid,
1290 x_he_spa_cc_id => l_he_spa_cc_id,
1291 x_person_id => p_person_id,
1292 x_course_cd => p_new_course_cd,
1293 x_cost_centre => old_spa_cc_dtls_rec.cost_centre,
1294 x_subject => old_spa_cc_dtls_rec.subject,
1295 x_proportion => old_spa_cc_dtls_rec.proportion,
1296 x_mode => 'R' );
1297
1298 END IF;
1299 CLOSE new_spa_cc_dtls_cur;
1300
1301 END LOOP;
1302
1303 EXCEPTION
1304 WHEN OTHERS THEN
1305 ROLLBACK;
1306 p_status := 2;
1307 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1308 fnd_message.set_token('NAME','IGS_HE_PROG_TRANSFER_PKG.HESA_STUD_STAT_TRANS');
1309 igs_ge_msg_stack.add;
1310 RETURN;
1311
1312 END hesa_stud_stat_trans;
1313
1314 END igs_he_prog_transfer_pkg;
1315
[Home] [Help]