1 PACKAGE BODY igs_he_ucas_tariff_pkg AS
2 /* $Header: IGSHE20B.pls 120.10 2006/05/04 20:58:36 jchakrab noship $*/
3
4 l_debug_level NUMBER:= fnd_log.g_current_runtime_level;
5 l_proc_level NUMBER:= fnd_log.level_unexpected;
6
7
8 -----------------------------------------------------------------------------------
9 -- ========================== EXTERNAL TARIFF CALCULATION =========================
10 -----------------------------------------------------------------------------------
11 PROCEDURE External_tariff_calc (
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER,
14 p_person_identifier IN NUMBER,
15 p_course_code IN VARCHAR2,
16 p_start_date IN VARCHAR2,
17 p_end_date IN VARCHAR2,
18 p_tariff_calc_type IN VARCHAR2,
19 p_calculate_tariff IN VARCHAR2,
20 P_recalculate IN VARCHAR2,
21 p_person_id_grp IN NUMBER,
22 p_program_group IN VARCHAR2,
23 p_program_type IN VARCHAR2,
24 p_report_all_hierarchy_flag IN VARCHAR2
25 ) IS
26
27 /*------------------------------------------------------------------
28 --Created by : rgangara, Oracle IDC
29 --Date created: 3-Aug-03
30 --
31 --Purpose: This is to calculate External UCAS tariff into HESA SPA table
32 --Known limitations/enhancements and/or remarks:
33 --
34 -- Change History:
35 -- WHO WHEN WHAT
36 -- smaddali 21-jan-04 Removed 2 cursors c_prgawd and c_spawd and added call
37 -- to get_gen_qual_aim, bug#3360646
38 -- ayedubat 16-MAR-04 Added a new parameter, p_report_all_hierarchy_flag and
39 -- the logic based on this parameter for Bug, 2956444
40 -- jbaber 29-NOV-04 Modified procedure, external_tariff_calc to exclude
41 -- students with no qualifications for Bug# 4035243
42 -- anwest 24-Jan-05 Bug# 4035243 Variable declaration and logging altered
43 -- for unhandled exception noted in peer review
44 -- jbaber 19-Jan-06 Included igs_he_st_spa_all.exclude flag for HE305
45 -- jchin 27-Jan-06 Bug 3484372 output full grade name instead of grade val
46 -- jchakrab 21-Feb-06 Modified for R12 performance enhs - replaced literal SQL with fnd_dsql
47 -- anwest 24-Mar-06 Bug# 5121695 - Modified for impact from R12 SWS changes.
48 -- Replaced IGS_GET_DYNAMIC_SQL(p_pid_group,l_status) with
49 -- GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status, l_group_type)
50 -- and implemented new associated logic.
51 -- jchakrab 04-May-06 Modified for 5203018 - closed cursor created using DBMS_SQL
52 --------------------------------------------------------------------------*/
53
54 -- get the person number for the person id
55 CURSOR c_person_number (cp_person_id igs_pe_person.person_id%TYPE ) IS
56 SELECT person_number
57 FROM igs_pe_person_base_v
58 WHERE person_id = cp_person_id ;
59 l_person_number igs_pe_person.person_number%TYPE ;
60
61 -- cursor to validate that the course is attached to the program group
62 CURSOR prg_group_mem_cur (cp_course_cd igs_ps_grp_mbr.course_cd%TYPE,
63 cp_version igs_ps_grp_mbr.version_number%TYPE) IS
64 SELECT 'X'
65 FROM igs_ps_grp_mbr
66 WHERE course_cd = cp_course_cd
67 AND version_number = cp_version
68 AND course_group_cd = p_program_group;
69
70 -- get the HESA code for oss qualification aim
71 CURSOR Cur_qual_aim (cp_student_qual_aim Igs_he_st_spa.Student_qual_aim%TYPE) IS
72 SELECT map1
73 FROM igs_he_code_map_val
74 WHERE Association_code ='OSS_HESA_AWD_ASSOC'
75 AND Map2 = cp_Student_qual_aim ;
76
77 -- get all the records for passed exam level of the student where year of qualification is null or
78 -- less than spa commencement date
79 -- smaddali added nvl to Year to get field ucas_tariff ,for bug 2415679
80 -- smaddali modified this cursor for bug 2473397 ,
81 -- inorder to calculate ucas_tariff on the fly added the join with igs_as_grd_sch_grade
82 -- Modified the cursor to exclude excluded subjects and subjects that are excluded specifically for this award.
83 -- Modified the cursor to add subject_code IS NULL for 3224610
84 CURSOR Cur_qual_dets (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
85 cp_commencement_date Igs_en_stdnt_ps_att.Commencement_dt%TYPE,
86 cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
87 cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
88 SELECT uqd.Exam_level,
89 uqd.Subject_code,
90 MAX(grd.rank) ucas_tariff
91 FROM igs_uc_qual_dets uqd,
92 igs_as_grd_sch_grade grd
93 WHERE uqd.Person_id = cp_person_id
94 AND uqd.Exam_level = cp_exam_level
95 AND grd.grade = uqd.approved_result
96 AND grd.grading_schema_cd = uqd.grading_schema_cd
97 AND grd.version_number = uqd.version_number
98 AND ( uqd.Year IS NULL OR uqd.Year <= TO_CHAR (cp_commencement_date, 'YYYY'))
99 AND ( subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
100 FROM igs_he_ut_excl_qals
101 WHERE tariff_calc_type_cd = cp_tariff_calc_type
102 AND (award_cd IS NULL OR award_cd = cp_exam_level)))
103 GROUP BY Exam_level, Subject_code ;
104
105 -- select the distinct exam levels for the passed person
106 -- smaddali added nvl to Year for bug 2421505
107 -- Added the subqueries as part of HE202_2 enh - Bug# 2717747.
108 -- Subquery1 is to include only those exam level records which are setup for inclusion for the given calculation type.
109 -- Subquery2 is to filter out exam levels records which are setup for exclusion (Only award without subject)
110 -- Subquery3 is to filter out subject records which are setup for exclusion (Only subject without award)
111 -- Subquery4 is to filter out Award + subject combination records which are setup for exclusion i.e.
112 -- (both Award is not null and subject is not null in exclude qualifications table).
113 -- Modified the cursor to add subject_code IS NULL for 3224610
114 CURSOR Cur_exam_level (l_person_id igs_uc_qual_dets.person_id%TYPE,
115 l_commencement_date Igs_en_stdnt_ps_att. Commencement_dt%TYPE,
116 cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
117 SELECT DISTINCT Exam_level
118 FROM Igs_uc_qual_dets
119 WHERE Person_id = l_person_id
120 AND exam_level IN (SELECT hula.award_cd
121 FROM igs_he_ut_lvl_award hula
122 WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
123 AND hula.closed_ind = 'N')
124 AND exam_level NOT IN (SELECT hueq.award_cd
125 FROM igs_he_ut_excl_qals hueq
126 WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
127 AND hueq.field_of_study IS NULL)
128 AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
129 FROM igs_he_ut_excl_qals
130 WHERE tariff_calc_type_cd = cp_tariff_calc_type
131 AND award_cd IS NULL))
132 AND (exam_level, subject_code) NOT IN (SELECT eqas.award_cd, eqas.field_of_study
133 FROM igs_he_ut_excl_qals eqas
134 WHERE eqas.tariff_calc_type_cd = cp_tariff_calc_type
135 AND eqas.award_cd IS NOT NULL
136 AND eqas.field_of_study IS NOT NULL)
137 AND (Year IS NULL OR Year <= TO_CHAR (l_commencement_date, 'YYYY') ) ;
138
139
140 -- get the spa record to be updated for fields highest_qual_on_entry , date_qual_on_entry_calc,
141 -- total_ucas_tariff
142 CURSOR Cur_st_spa_for_update (l_person_id igs_he_st_spa.person_id%TYPE,
143 l_course_cd igs_he_st_spa.course_cd%TYPE) IS
144 SELECT ihss.row_id row_id,
145 ihss.hesa_st_spa_id hesa_st_spa_id,
146 ihss.course_cd course_cd,
147 ihss.version_number version_number,
148 ihss.person_id person_id,
149 ihss.fe_student_marker fe_student_marker,
150 ihss.domicile_cd domicile_cd,
151 ihss.inst_last_attended inst_last_attended,
152 ihss.year_left_last_inst year_left_last_inst,
153 ihss.highest_qual_on_entry highest_qual_on_entry,
154 ihss.date_qual_on_entry_calc date_qual_on_entry_calc,
155 ihss.a_level_point_score a_level_point_score,
156 ihss.highers_points_scores highers_points_scores,
157 ihss.occupation_code occupation_code,
158 ihss.commencement_dt commencement_dt,
159 ihss.special_student special_student,
160 ihss.student_qual_aim student_qual_aim,
161 ihss.student_fe_qual_aim student_fe_qual_aim,
162 ihss.teacher_train_prog_id teacher_train_prog_id,
163 ihss.itt_phase itt_phase,
164 ihss.bilingual_itt_marker bilingual_itt_marker,
165 ihss.teaching_qual_gain_sector teaching_qual_gain_sector,
166 ihss.teaching_qual_gain_subj1 teaching_qual_gain_subj1,
167 ihss.teaching_qual_gain_subj2 teaching_qual_gain_subj2,
168 ihss.teaching_qual_gain_subj3 teaching_qual_gain_subj3,
169 ihss.student_inst_number student_inst_number,
170 ihss.hesa_return_name hesa_return_name,
171 ihss.hesa_return_id hesa_return_id,
172 ihss.hesa_submission_name hesa_submission_name,
173 ihss.associate_ucas_number associate_ucas_number,
174 ihss.associate_scott_cand associate_scott_cand,
175 ihss.associate_teach_ref_num associate_teach_ref_num,
176 ihss.associate_nhs_reg_num associate_nhs_reg_num,
177 ihss.itt_prog_outcome itt_prog_outcome,
178 ihss.nhs_funding_source nhs_funding_source,
179 ihss.ufi_place ufi_place,
180 ihss.postcode postcode,
181 ihss.social_class_ind social_class_ind,
182 ihss.destination destination,
183 ihss.occcode occcode,
184 ihss.total_ucas_tariff total_ucas_tariff,
185 ihss.nhs_employer nhs_employer,
186 ihss.return_type,
187 ihss.qual_aim_subj1,
188 ihss.qual_aim_subj2,
189 ihss.qual_aim_subj3,
190 ihss.qual_aim_proportion,
191 ihss.dependants_cd,
192 ihss.implied_fund_rate,
193 ihss.gov_initiatives_cd,
194 ihss.units_for_qual,
195 ihss.disadv_uplift_elig_cd,
196 ihss.franch_partner_cd,
197 ihss.units_completed,
198 ihss.franch_out_arr_cd,
199 ihss.employer_role_cd,
200 ihss.disadv_uplift_factor,
201 ihss.enh_fund_elig_cd,
202 ihss.exclude_flag
203 FROM igs_he_st_spa ihss
204 WHERE ihss.person_id = l_person_id
205 AND ihss.course_cd = l_course_cd;
206
207 -- get the rowid for Student attempt
208 CURSOR Cur_st_spa_ut (l_person_id igs_he_st_spa.person_id%TYPE,
209 l_course_cd igs_he_st_spa.course_cd%TYPE ) IS
210 SELECT rowid
211 FROM Igs_he_st_spa_ut
212 WHERE Person_id = l_person_id
213 AND course_cd = l_course_cd;
214
215 --get the Highest qualification on entry as the highest ranked qualification
216 --smaddali modified cursor to select only open code_values from igs_he_code_values ,bug 2730388
217 --Correct the comparision done in this cursor to compare Iagsta.grading_schema_cd with the Iuqd.grading_schema_cd
218 --Not with the Exam Level as mentioned in bug 2782618
219 CURSOR Cur_highest_grade (l_person_id igs_he_st_spa.person_id%TYPE ,
220 p_commencement_dt igs_he_st_spa.commencement_dt%TYPE) IS
221 SELECT Iagsta.grade,
222 Iagsta.To_grading_schema_cd,
223 Iagsta.to_version_number,
224 Iagsta.to_grade,
225 Iagsgv.full_grade_name, -- jchin 3484372 Added full grade name to output to log file
226 Iagsgv.rank
227 FROM Igs_uc_qual_dets Iuqd,
228 Igs_as_grd_sch_trn_all Iagsta,
229 Igs_as_grd_sch_grade_v Iagsgv,
230 Igs_as_grd_sch_grade grd
231 WHERE Iuqd.person_id = l_person_id
232 AND Iagsta.grading_schema_cd = Iuqd.grading_schema_cd
233 AND Iagsta.version_number = Iuqd.version_number
234 AND grd.grading_schema_cd = iuqd.grading_schema_cd
235 AND grd.version_number = Iuqd.version_number
236 AND ( iuqd.approved_result IS NULL
237 OR
238 (grd.grade = iuqd.approved_result AND grd.s_result_type = 'PASS' )
239 )
240 AND Iagsgv.grading_schema_cd = Iagsta.to_grading_schema_cd
241 AND Iagsgv.version_number = Iagsta.to_version_number
242 AND Iagsgv.grade = Iagsta.to_grade
243 AND (Iuqd.Year IS NULL OR Iuqd.Year <= TO_CHAR(p_commencement_dt,'YYYY'))
244 AND EXISTS (SELECT 'X'
245 FROM Igs_he_code_values
246 WHERE Code_type = 'HESA_HIGH_QUAL_ON_ENT'
247 AND Value = Iagsta.to_grading_schema_cd
248 AND NVL(closed_ind,'N') = 'N' )
249 ORDER BY Iagsgv.rank ASC ;
250
251 -- Returns the other awards with the same subject for the current person, exam level, subject being processed.
252 -- modified the cursor to include and exclude exam levels that are setup for the calculation type.
253 CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
254 cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
255 cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
256 cp_commencement_date igs_en_stdnt_ps_att.commencement_dt%TYPE,
257 cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
258 SELECT DISTINCT exam_level
259 FROM igs_uc_qual_dets
260 WHERE person_id = cp_person_id
261 AND exam_level <> cp_exam_level
262 AND subject_code = cp_subject_cd
263 AND exam_level IN (SELECT hula.award_cd
264 FROM igs_he_ut_lvl_award hula
265 WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
266 AND hula.closed_ind = 'N')
267 AND exam_level NOT IN (SELECT hueq.award_cd
268 FROM igs_he_ut_excl_qals hueq
269 WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
270 AND hueq.field_of_study IS NULL)
271 AND (year IS NULL OR Year <= TO_CHAR(cp_commencement_date, 'YYYY'));
272
273 -- For checking whether parent award exists for the passed in award.
274 CURSOR cur_check_parent (cp_award_cd igs_he_ut_prt_award.award_cd%TYPE,
275 cp_tariff_calc_type igs_he_ut_prt_award.tariff_calc_type_cd%TYPE) IS
276 SELECT parent_award_cd
277 FROM (SELECT *
278 FROM igs_he_ut_prt_award
279 WHERE tariff_calc_type_cd = cp_tariff_calc_type)
280 START WITH award_cd = cp_award_cd
281 CONNECT BY PRIOR parent_award_cd = award_cd
282 AND tariff_calc_type_cd = cp_tariff_calc_type;
283
284 -- anwest Bug#4035243 The original declaration:
285 -- l_tariff_score igs_uc_qual_dets.ucas_tariff%TYPE := 0;
286 -- was causing an Unhandled Exception when value > 999
287 -- because igs_uc_qual_dets.ucas_tariff%TYPE is defined as
288 -- NUMBER(3)
289 l_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE := 0;
290 l_qual_count Igs_he_st_spa_ut_all.Number_of_qual%TYPE := 0;
291 l_total_tariff_score Igs_he_st_spa_all.total_ucas_tariff%TYPE := 0;
292 l_last_update_date Igs_uc_qual_dets.Last_update_date%TYPE ;
293 l_record_inserted NUMBER := 0;
294 l_record_updated NUMBER := 0;
295 l_hesa_st_spau_id Igs_he_st_spa_ut_all.hesa_st_spau_id%TYPE := 0;
296 l_Qual_aim igs_he_code_map_val.map1%TYPE ;
297 l_rowid VARCHAR2(26) ;
298 C_st_spa_for_update cur_st_spa_for_update%ROWTYPE;
299 l_grade cur_highest_grade%ROWTYPE;
300 l_parent_awd_found BOOLEAN;
301 l_recs_for_insert NUMBER; -- to bypass inserting into SPA_UT table if no qual recs found
302 l_prog_grp_exists VARCHAR2(1); -- to hold that program group and course combination is valid.
303 l_calc_tariff_flag VARCHAR2(1); -- to indicate whether processing should happen based on program group filtering
304 l_tariff_exists VARCHAR2(1); -- to hold that existing tariff records exist.
305 l_Int_calc_sql VARCHAR2(32767);
306 l_prs_grp_sql VARCHAR2(32767);
307 l_prs_grp_status VARCHAR2(10);
308 l_recs_processed NUMBER := 0;
309 l_start_date DATE;
310 l_end_date DATE;
311
312 --jchakrab added for R12 Performance Enhs (4950293)
313 l_num_rows NUMBER;
314 l_cursor_id NUMBER;
315
316 -- anwest added for Bug #5121695
317 l_group_type VARCHAR2(10);
318
319 TYPE st_spa_rec is record (
320 student_qual_aim igs_he_st_spa.student_qual_aim%TYPE
321 ,commencement_dt igs_en_stdnt_ps_att.commencement_dt%TYPE
322 ,date_qual_on_entry_calc igs_he_st_spa.date_qual_on_entry_calc%TYPE
323 ,person_id igs_he_st_spa.person_id%TYPE
324 ,course_cd igs_he_st_spa.course_cd%TYPE
325 ,version_number igs_he_st_spa.version_number%TYPE);
326
327 c_st_spa st_spa_rec;
328
329 BEGIN
330
331 -- get date values from varchar
332 IF p_start_date IS NOT NULL THEN
333 l_start_date := TO_DATE(SUBSTR(p_start_date,1,11),'YYYY/MM/DD');
334 END IF;
335
336 IF p_end_date IS NOT NULL THEN
337 l_end_date := TO_DATE(SUBSTR(p_end_date,1,11),'YYYY/MM/DD');
338 END IF;
339
340 --initialize fnd_dsql data-structures
341 fnd_dsql.init;
342
343 -- basic SQL statement for selecting records to be processed
344 -- modified for bug 4035243
345 fnd_dsql.add_text('SELECT ihss.student_qual_aim , iespa.commencement_dt, ihss.date_qual_on_entry_calc,');
346 fnd_dsql.add_text('ihss.person_id, ihss.course_cd, ihss.version_number ');
347 fnd_dsql.add_text('FROM igs_he_st_spa ihss, igs_en_stdnt_ps_att iespa ');
348 fnd_dsql.add_text('WHERE ihss.person_id = iespa.person_id AND ihss.course_cd = iespa.course_cd ');
349 fnd_dsql.add_text('AND hesa_return_id IS NULL AND hesa_submission_name IS NULL AND hesa_return_name IS NULL ');
350 fnd_dsql.add_text('AND EXISTS (SELECT person_id from igs_uc_qual_dets where person_id = ihss.person_id) ');
351
352 -- if person id is not null append the following filtering criteria
353 IF p_person_identifier IS NOT NULL THEN
354 fnd_dsql.add_text(' AND ihss.person_id = ');
355 fnd_dsql.add_bind(p_person_identifier);
356 END IF;
357
358 -- if Course is not null append the following filtering criteria
359 IF p_course_code IS NOT NULL THEN
360 fnd_dsql.add_text(' AND ihss.course_cd = ');
361 fnd_dsql.add_bind(p_course_code);
362 END IF;
363
364 -- if Program Type is not null append the following filtering criteria
365 IF p_program_type IS NOT NULL THEN
366 fnd_dsql.add_text(' AND (ihss.course_cd, ihss.version_number) IN ');
367 fnd_dsql.add_text(' (SELECT psv.course_cd, psv.version_number FROM igs_ps_ver psv WHERE psv.course_type = ');
368 fnd_dsql.add_bind(p_program_type);
369 fnd_dsql.add_text(')');
370 END IF;
371
372 -- if End date is not null append the following filtering criteria
373 IF l_end_date IS NOT NULL THEN
374 fnd_dsql.add_text(' AND (iespa.commencement_dt IS NULL OR iespa.commencement_dt <= ');
375 fnd_dsql.add_bind(l_end_date);
376 fnd_dsql.add_text(')');
377 END IF;
378
379 -- if start date is not null append the following filtering criteria
380 IF l_start_date IS NOT NULL THEN
381 fnd_dsql.add_text(' AND (iespa.discontinued_dt IS NULL OR iespa.discontinued_dt >= ');
382 fnd_dsql.add_bind(l_start_date);
383 fnd_dsql.add_text(')');
384 fnd_dsql.add_text(' AND (iespa.course_rqrmnts_complete_dt IS NULL OR iespa.course_rqrmnts_complete_dt >= ');
385 fnd_dsql.add_bind(l_start_date);
386 fnd_dsql.add_text(')');
387 END IF;
388
389 -- Person ID Group filtering. If person ID group is not NULL then append the sql returned to the above sql stmnt.
390 -- ANWEST Bug #5121695 Changed IGS_GET_DYNAMIC_SQL(p_pid_group,l_status) to
391 -- GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status, l_group_type)
392 -- and implemented new associated logic.
393
394 IF p_person_id_grp IS NOT NULL THEN
395
396 l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status, l_group_type);
397
398 IF l_prs_grp_status <> 'S' THEN
399 fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
400 fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
401 errbuf := fnd_message.get();
402 fnd_file.put_line(fnd_file.log, errbuf); -- as this info is also important to end user.
403 retcode := '2';
404 RETURN;
405 END IF;
406
407 IF l_group_type = 'STATIC' THEN
408 l_prs_grp_sql := SUBSTR(l_prs_grp_sql, 1, INSTR(UPPER(l_prs_grp_sql), ':P_GROUPID') - 1);
409 END IF;
410
411 -- concatenate the incoming sql stmt to the basic sql to get the complete SQL stmt
412 fnd_dsql.add_text(' AND ihss.person_id IN (');
413 fnd_dsql.add_text(l_prs_grp_sql);
414 IF l_group_type = 'STATIC' THEN
415 fnd_dsql.add_bind(p_person_id_grp);
416 END IF;
417 fnd_dsql.add_text(')');
418 l_prs_grp_sql := NULL; -- initializing to NULL as this variable no more required for processing.
419
420 END IF;
421
422 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
423 fnd_dsql.set_cursor(l_cursor_id);
424
425 l_Int_calc_sql := fnd_dsql.get_text(FALSE);
426 DBMS_SQL.PARSE(l_cursor_id, l_Int_calc_sql, DBMS_SQL.NATIVE);
427 fnd_dsql.do_binds;
428
429 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1,c_st_spa.student_qual_aim,30);
430 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2,c_st_spa.commencement_dt);
431 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 3,c_st_spa.date_qual_on_entry_calc);
432 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 4,c_st_spa.person_id);
433 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 5,c_st_spa.course_cd,6);
434 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 6,c_st_spa.version_number);
435
436 l_num_rows := DBMS_SQL.EXECUTE(l_cursor_id);
437
438 -- If no student program attempt records exist for which to calculate UCAS Tariff then log error message
439 LOOP
440
441 c_st_spa := NULL; -- initialize record variable
442 l_recs_processed := 1; -- to indicate that atleast one record is processed.
443 l_calc_tariff_flag := 'Y';
444 l_person_number := NULL ;
445
446 -- fetch a row
447 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
448 EXIT;
449 END IF;
450
451 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1,c_st_spa.student_qual_aim);
452 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2,c_st_spa.commencement_dt);
453 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 3,c_st_spa.date_qual_on_entry_calc);
454 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 4,c_st_spa.person_id);
455 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 5,c_st_spa.course_cd);
456 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 6,c_st_spa.version_number);
457
458 -- get the Person Number for the person for logging
459 OPEN c_person_number(C_st_spa.person_id) ;
460 FETCH c_person_number INTO l_person_number ;
461 CLOSE c_person_number ;
462
463 -- Program Group Filtering if program group is entered
464 IF p_program_group IS NOT NULL THEN
465
466 -- Since the program group is entered, check that the record being processed is valid for the program group
467 -- Process the record only if the course is attached to the entered group.
468 OPEN prg_group_mem_cur (C_st_spa.course_cd, C_st_spa.version_number);
469 FETCH prg_group_mem_cur INTO l_prog_grp_exists;
470
471 -- if found then process else skip this record
472 IF prg_group_mem_cur%FOUND THEN
473 l_calc_tariff_flag := 'Y';
474 ELSE
475 l_calc_tariff_flag := 'N';
476 END IF;
477 CLOSE prg_group_mem_cur;
478
479 END IF;
480
481 -- Tariff is calculated for existing (already calculated earlier) tariff records only if
482 -- the Recalculate parameter is YES. Else existing tariff records are not modified and
483 -- tariff calculation is to be bypassed.
484 IF (p_recalculate = 'N' AND l_calc_tariff_flag = 'Y' AND c_st_spa.Date_qual_on_entry_calc IS NOT NULL) THEN
485
486 -- set the flag so that no further processing is required for the record
487 l_calc_tariff_flag := 'N';
488
489 fnd_message.set_name('IGS','IGS_HE_UT_EXT_NOT_RECALC');
490 fnd_message.set_token('PERSON', l_person_number);
491 fnd_message.set_token('PROGRAM', c_st_spa.course_cd);
492 fnd_file.put_line(fnd_file.log, fnd_message.get());
493
494 END IF;
495
496 -- Further processing is done only if the flag is set to Y
497 IF l_calc_tariff_flag = 'Y' THEN
498
499 l_qual_aim := NULL ; /* Initializing the counter */ -- added by smaddali for bug 2353094
500 l_total_tariff_score := 0; /* To hold the UCAS tariff grand total for an applicant. */
501
502 -- get the hesa value of general qualification aim for the student program attempt
503 -- smaddali 21-jan-04 for bug#3360646, calling field derivation instead of deriving value again
504 igs_he_extract_fields_pkg.get_gen_qual_aim
505 (p_person_id => c_st_spa.person_id,
506 p_course_cd => c_st_spa.course_cd,
507 p_version_number => c_st_spa.version_number,
508 p_spa_gen_qaim => c_st_spa.student_qual_aim,
509 p_hesa_gen_qaim => l_Qual_aim ,
510 p_enrl_start_dt => c_st_spa.commencement_dt,
511 p_enrl_end_dt => NULL,
512 p_awd_conf_start_dt => NULL);
513
514 -- smaddali seperated this condition so that it apples only for UCAS tariff and not for calculating Highest qualification
515 -- for bug # 2394366
516 -- UCAS tariff is calculated only for qualification aim between 19-52 or 61 or 97
517
518 -- Before calculating the ucas scoress, Delete the all ucas tariff scores for the student attempt
519 FOR Cur_st_spa_ut_rec IN Cur_st_spa_ut(c_st_spa.person_id, c_st_spa.course_cd) LOOP
520 Igs_he_st_spa_ut_all_pkg.delete_row(x_rowid => Cur_st_spa_ut_rec.rowid );
521 END LOOP;
522
523 IF ((l_qual_aim BETWEEN 18 AND 52) OR (l_qual_aim = 61 OR l_qual_aim = 97)) THEN
524
525 /* This loop will have the list of exam level for an applicant */
526 FOR C_exam_level IN Cur_exam_level (c_st_spa.person_id, c_st_spa.commencement_dt, p_tariff_calc_type)
527 LOOP
528
529 /* Initializing the counter */
530 l_tariff_score := 0; /* To hold the UCAS Tariff for each Exam level of an applicant. */
531 l_qual_count := 0; /* To hold the count of subjects under each exam level. */
532
533 l_recs_for_insert := 0; -- initialize to zero
534 FOR C_qual_dets IN Cur_qual_dets (c_st_spa.person_id,
535 c_st_spa.commencement_dt,
536 c_exam_level.exam_level,
537 p_tariff_calc_type) LOOP
538
539 -- increment the count. This indicates that cur_qual_dets cursor returns atleast
540 -- one record for processing.
541 l_recs_for_insert := l_recs_for_insert + 1;
542
543 -- Added Logic for Filtering to prevent double counting as part of HEFD202.1 Build Bug 2717744.
544 l_parent_awd_found := FALSE;
545 FOR dup_award_rec IN cur_check_dup_awards(c_st_spa.person_id,
546 c_qual_dets.exam_level,
547 c_qual_dets.subject_code,
548 c_st_spa.commencement_dt,
549 p_tariff_calc_type) LOOP
550
551 FOR parent_awd_rec IN cur_check_parent(c_qual_dets.exam_level, p_tariff_calc_type)
552 LOOP
553 IF parent_awd_rec.parent_award_cd = dup_award_rec.exam_level THEN
554 l_parent_awd_found := TRUE;
555 EXIT;
556 END IF;
557 END LOOP;
558
559 EXIT WHEN l_parent_awd_found = TRUE;
560
561 END LOOP;
562
563 IF l_parent_awd_found = FALSE THEN
564
565 -- This will get the total of UCAS Tariff and the number of subjects for an Exam level
566 l_qual_count := l_qual_count + 1;
567 l_tariff_score := l_tariff_score + NVL(c_qual_dets.ucas_tariff,0);
568
569 /* Getting the total of UCAS Tariff for a person Grand total of UCAS Tariff for an applicant*/
570 l_total_tariff_score:= l_total_tariff_score + NVL(c_qual_dets.ucas_tariff,0);
571
572 ELSIF ( p_report_all_hierarchy_flag = 'Y' ) THEN
573
574 -- If Qualification has a parent award in the same subject ( Double counting scenario) and
575 -- Report all awards in tariff breakdown is checked then report the Tariff only at Exam Level,
576 -- but should not be reported in the Grand Total Tariff for an applicant
577 -- This logic is added as part of HE311FD - JUly 2004 Enhancement Bug, 2956444
578 l_tariff_score := l_tariff_score + NVL(c_qual_dets.ucas_tariff,0);
579 l_qual_count := l_qual_count + 1;
580
581 END IF;
582
583 END LOOP;
584
585 IF l_recs_for_insert > 0 THEN
586
587 -- Insert the Tariff scores for each Exam level of an applicant and
588 -- the number of subject of each Exam level
589
590 Igs_he_st_spa_ut_all_pkg.Insert_row (
591 x_mode => 'R',
592 x_rowid => l_rowid,
593 x_hesa_st_spau_id => l_hesa_st_spau_id,
594 x_Person_id => c_st_spa.person_id,
595 x_Course_cd => c_st_spa.course_cd,
596 x_Version_number => c_st_spa.version_number,
597 x_Qualification_level => c_exam_level.exam_level,
598 x_Number_of_qual => l_qual_count,
599 x_tariff_score => l_tariff_score,
600 x_org_id => igs_ge_gen_003.get_org_id );
601
602 l_record_inserted := l_record_inserted + 1 ;
603
604 Fnd_Message.Set_Name('IGS','IGS_HE_EXAM_LEVEL_TARIFF');
605 Fnd_Message.Set_Token('PERSON_NUMBER',l_person_number);
606 Fnd_Message.Set_Token('COURSE_CODE',c_st_spa.course_cd);
607 Fnd_Message.Set_Token('EXAM_LEVEL',c_exam_level.Exam_level);
608 Fnd_Message.Set_Token('SUBJECT_COUNT',l_qual_count);
609 Fnd_Message.Set_Token('TARIFF_SCORE',l_tariff_score);
610 fnd_file.put_line(fnd_file.log, fnd_message.get());
611
612 END IF;
613
614 END LOOP; --end for Cur_exam_level
615
616 END IF ; -- if qualification aim in the set of values
617
618
619 /* picking up records to update the spa table */
620 OPEN Cur_st_spa_for_update (c_st_spa.person_id, c_st_spa.course_cd);
621 FETCH Cur_st_spa_for_update INTO c_st_spa_for_update;
622 CLOSE Cur_st_spa_for_update;
623
624 /* Getting the highest grade for the applicant to update the spa table */
625 l_grade := NULL ; --added by smaddali for bug2353094
626
627 OPEN Cur_highest_grade (C_ST_SPA.person_id, c_st_spa.commencement_dt);
628 FETCH Cur_highest_grade INTO l_grade ;
629 CLOSE Cur_highest_grade;
630
631 Igs_he_st_spa_all_pkg.Update_row (
632 x_mode => 'R',
633 x_rowid => c_st_spa_for_update.row_id,
634 x_hesa_st_spa_id => c_st_spa_for_update.hesa_st_spa_id,
635 x_course_cd => c_st_spa_for_update.course_cd,
636 x_version_number => c_st_spa_for_update.version_number,
637 x_person_id => c_st_spa_for_update.person_id,
638 x_fe_student_marker => c_st_spa_for_update.fe_student_marker,
639 x_domicile_cd => c_st_spa_for_update.domicile_cd,
640 x_inst_last_attended => c_st_spa_for_update.inst_last_attended,
641 x_year_left_last_inst => c_st_spa_for_update.year_left_last_inst,
642 x_highest_qual_on_entry => l_grade.to_grade,
643 x_date_qual_on_entry_calc => SYSDATE,
644 x_a_level_point_score => c_st_spa_for_update.a_level_point_score,
645 x_highers_points_scores => c_st_spa_for_update.highers_points_scores,
646 x_occupation_code => c_st_spa_for_update.occupation_code,
647 x_commencement_dt => c_st_spa_for_update.commencement_dt,
648 x_special_student => c_st_spa_for_update.special_student,
649 x_student_qual_aim => c_st_spa_for_update.student_qual_aim,
650 x_student_fe_qual_aim => c_st_spa_for_update.student_fe_qual_aim,
651 x_teacher_train_prog_id => c_st_spa_for_update.teacher_train_prog_id,
652 x_itt_phase => c_st_spa_for_update.itt_phase,
653 x_bilingual_itt_marker => c_st_spa_for_update.bilingual_itt_marker,
654 x_teaching_qual_gain_sector => c_st_spa_for_update.teaching_qual_gain_sector,
655 x_teaching_qual_gain_subj1 => c_st_spa_for_update.teaching_qual_gain_subj1,
656 x_teaching_qual_gain_subj2 => c_st_spa_for_update.teaching_qual_gain_subj2,
657 x_teaching_qual_gain_subj3 => c_st_spa_for_update.teaching_qual_gain_subj3,
658 x_student_inst_number => c_st_spa_for_update.student_inst_number,
659 x_hesa_return_name => c_st_spa_for_update.hesa_return_name,
660 x_hesa_return_id => c_st_spa_for_update.hesa_return_id,
661 x_hesa_submission_name => c_st_spa_for_update.hesa_submission_name,
662 x_associate_ucas_number => c_st_spa_for_update.associate_ucas_number,
663 x_associate_scott_cand => c_st_spa_for_update.associate_scott_cand,
664 x_associate_teach_ref_num => c_st_spa_for_update.associate_teach_ref_num,
665 x_associate_nhs_reg_num => c_st_spa_for_update.associate_nhs_reg_num,
666 x_itt_prog_outcome => c_st_spa_for_update.itt_prog_outcome,
667 x_nhs_funding_source => c_st_spa_for_update.nhs_funding_source,
668 x_ufi_place => c_st_spa_for_update.ufi_place,
669 x_postcode => c_st_spa_for_update.postcode,
670 x_social_class_ind => c_st_spa_for_update.social_class_ind,
671 x_destination => c_st_spa_for_update.destination,
672 x_occcode => c_st_spa_for_update.occcode,
673 x_total_ucas_tariff => l_total_tariff_score,
674 x_nhs_employer => c_st_spa_for_update.nhs_employer,
675 x_return_type => c_st_spa_for_update.return_type,
676 x_qual_aim_subj1 => c_st_spa_for_update.qual_aim_subj1,
677 x_qual_aim_subj2 => c_st_spa_for_update.qual_aim_subj2,
678 x_qual_aim_subj3 => c_st_spa_for_update.qual_aim_subj3,
679 x_qual_aim_proportion => c_st_spa_for_update.qual_aim_proportion ,
680 x_org_id => igs_ge_gen_003.get_org_id,
681 x_dependants_cd => c_st_spa_for_update.dependants_cd ,
682 x_implied_fund_rate => c_st_spa_for_update.implied_fund_rate ,
683 x_gov_initiatives_cd => c_st_spa_for_update.gov_initiatives_cd ,
684 x_units_for_qual => c_st_spa_for_update.units_for_qual ,
685 x_disadv_uplift_elig_cd => c_st_spa_for_update.disadv_uplift_elig_cd ,
686 x_franch_partner_cd => c_st_spa_for_update.franch_partner_cd ,
687 x_units_completed => c_st_spa_for_update.units_completed ,
688 x_franch_out_arr_cd => c_st_spa_for_update.franch_out_arr_cd ,
689 x_employer_role_cd => c_st_spa_for_update.employer_role_cd ,
690 x_disadv_uplift_factor => c_st_spa_for_update.disadv_uplift_factor ,
691 x_enh_fund_elig_cd => c_st_spa_for_update.enh_fund_elig_cd,
692 x_exclude_flag => c_st_spa_for_update.exclude_flag);
693
694 fnd_message.set_name('IGS','IGS_HE_TOTAL_TARIFF');
695 fnd_message.set_token('PERSON_NUMBER',l_person_number);
696 fnd_message.set_token('COURSE_CODE',c_st_spa.course_cd);
697 IF l_grade.to_grade IS NULL THEN -- jchin added 3484372
698 fnd_message.set_token('HIGH_QUAL', 'NULL');
699 ELSE
700 fnd_message.set_token('HIGH_QUAL', l_grade.full_grade_name);
701 END IF;
702 fnd_message.set_token('TOTAL_TARIFF',l_total_tariff_score);
703 fnd_file.put_line(fnd_file.log, fnd_message.get());
704
705 l_record_updated := l_record_updated + 1 ;
706
707 COMMIT;
708
709 END IF; -- bypass processing if Program group is given and Course is not associated to the group
710
711 END LOOP; -- Loop of Cursor Cur_st_spa
712
713 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
714
715 fnd_message.set_name('IGS','IGS_UC_UPD_REC_COUNT');
716 fnd_message.set_token('REC_CNT',l_record_updated);
717 fnd_file.put_line(fnd_file.log, fnd_message.get());
718
719 fnd_message.set_name('IGS','IGS_UC_INS_REC_COUNT');
720 fnd_message.set_token('REC_CNT',l_record_inserted);
721 fnd_file.put_line(fnd_file.log, fnd_message.get());
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 IF l_cursor_id IS NOT NULL THEN
726 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
727 END IF;
728
729 ROLLBACK;
730 IF SQLCODE=-1436 THEN
731 fnd_message.set_name('IGS','IGS_HE_UT_AWD_CYCLIC_REL');
732 fnd_message.set_token('AWARD',NULL);
733 -- anwest Bug# 4035243 Peer review noted no actual logging
734 fnd_file.put_line(fnd_file.log, fnd_message.get());
735 ELSE
736 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
737 fnd_message.set_token('NAME','IGS_HE_UCAS_TARIFF_PKG.External_tariff_calc - '||SQLERRM);
738 -- anwest Bug# 4035243 Peer review noted no actual logging
739 fnd_file.put_line(fnd_file.log, fnd_message.get());
740 END IF;
741
742 IF ( fnd_log.level_unexpected >= l_debug_level ) THEN
743 fnd_log.message(fnd_log.level_unexpected, 'igs.plsql.hesa.ucas_tariff_calc.exception', FALSE);
744 END IF;
745
746 fnd_message.retrieve (Errbuf);
747 Retcode := 2 ;
748 RETURN;
749
750 END External_tariff_calc;
751 ----------------------------------------------------------------------------------------------------
752 -- ============ END OF EXTERNAL TARIFF CALCULATION ===========
753 ----------------------------------------------------------------------------------------------------
754
755
756 ----------------------------------------------------------------------------------------------------
757 -- ============ BEGIN OF INTERNAL TARIFF CALCULATION ==============
758 ----------------------------------------------------------------------------------------------------
759 PROCEDURE Internal_tariff_calc (
760 errbuf OUT NOCOPY VARCHAR2,
761 retcode OUT NOCOPY NUMBER,
762 p_person_identifier IN NUMBER,
763 p_tariff_calc_type IN VARCHAR2,
764 p_calculate_tariff IN VARCHAR2,
765 P_recalculate IN VARCHAR2,
766 p_person_id_grp IN NUMBER,
767 p_report_all_hierarchy_flag IN VARCHAR2
768 ) IS
769
770 /*------------------------------------------------------------------
771 --Created by : rgangara, Oracle IDC
772 --Date created: 30-Aug-03
773 --
774 --Purpose: This is to calculate Internal UCAS tariff into Person Summary and
775 -- person details table
776 --Known limitations/enhancements and/or remarks:
777 --
778 -- Change History:
779 WHO WHEN WHAT
780 ayedubat 16-MAR-04 Added a new parameter, p_report_all_hierarchy_flag and
781 the logic based on this parameter for Bug, 2956444
782 jchin 27-Jan-06 Bug 3678414 - Output total tariff score to log
783 jchakrab 21-Feb-06 Modified for R12 Performance Enhs (4950293) - removed literal SQL
784 anwest 24-Mar-06 Bug# 5121695 - Modified for impact from R12 SWS changes.
785 Replaced IGS_GET_DYNAMIC_SQL(p_pid_group,l_status) with
786 GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status, l_group_type)
787 and implemented new associated logic.
788 jchakrab 04-May-06 Modified for 5203018 - closed cursor created using DBMS_SQL
789 --------------------------------------------------------------------------*/
790
791 -- Cursor to get only valid exam level records for a person to be processed for tariff calculation.
792 -- Subquery1 is to include only those exam level records which are setup for inclusion for the given calculation type.
793 -- Subquery2 is to filter out exam levels records which are setup for exclusion (Only award without subject)
794 -- Subquery3 is to filter out subject records which are setup for exclusion (Only subject without award)
795 -- Subquery4 is to filter out Award + subject combination records which are setup for exclusion i.e.
796 -- (both Award is not null and subject is not null in exclude qualifications table).
797 -- Modified the cursor to add subject_code IS NULL for 3224610
798 CURSOR prsn_exam_level_cur (cp_person_id igs_uc_qual_dets.person_id%TYPE,
799 cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE)
800 IS
801 SELECT DISTINCT exam_level
802 FROM igs_uc_qual_dets
803 WHERE person_id = cp_person_id
804 AND exam_level IN (SELECT hula.award_cd
805 FROM igs_he_ut_lvl_award hula
806 WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
807 AND hula.closed_ind = 'N')
808 AND exam_level NOT IN (SELECT hueq.award_cd
809 FROM igs_he_ut_excl_qals hueq
810 WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
811 AND hueq.field_of_study IS NULL)
812 AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
813 FROM igs_he_ut_excl_qals
814 WHERE tariff_calc_type_cd = cp_tariff_calc_type
815 AND award_cd IS NULL))
816 AND (exam_level, subject_code) NOT IN (SELECT eqas.award_cd, eqas.field_of_study
817 FROM igs_he_ut_excl_qals eqas
818 WHERE eqas.tariff_calc_type_cd = cp_tariff_calc_type
819 AND eqas.award_cd IS NOT NULL
820 AND eqas.field_of_study IS NOT NULL);
821
822
823 -- get all the valid subject records as per setup for passed exam level of the student and person.
824 -- igs_as_grd_sch_grade is joined as UCAS Tariff is obtained/derived on the fly.
825 -- The subquery is used to exclude excluded subjects and subjects that are excluded specifically for this award.
826 -- Modified the cursor to add subject_code IS NULL for 3224610
827 CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
828 cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
829 cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
830 SELECT uqd.Exam_level,
831 uqd.Subject_code,
832 MAX(grd.rank) ucas_tariff
833 FROM igs_uc_qual_dets uqd,
834 igs_as_grd_sch_grade grd
835 WHERE uqd.Person_id = cp_person_id
836 AND uqd.Exam_level = cp_exam_level
837 AND grd.grade = uqd.approved_result
838 AND grd.grading_schema_cd = uqd.grading_schema_cd
839 AND grd.version_number = uqd.version_number
840 AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
841 FROM igs_he_ut_excl_qals
842 WHERE tariff_calc_type_cd = cp_tariff_calc_type
843 AND (award_cd IS NULL OR award_cd = cp_exam_level)))
844 GROUP BY uqd.Exam_level, uqd.Subject_code ;
845
846
847 -- cursor to get records for the passed subject with other exam levels
848 CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
849 cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
850 cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
851 cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
852 SELECT DISTINCT exam_level
853 FROM igs_uc_qual_dets
854 WHERE person_id = cp_person_id
855 AND exam_level <> cp_exam_level
856 AND subject_code = cp_subject_cd
857 AND exam_level IN (SELECT hula.award_cd
858 FROM igs_he_ut_lvl_award hula
859 WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
860 AND hula.closed_ind = 'N')
861 AND exam_level NOT IN (SELECT hueq.award_cd
862 FROM igs_he_ut_excl_qals hueq
863 WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
864 AND hueq.field_of_study IS NULL);
865
866 -- Cursor for checking whether parent award exists for the passed in award.
867 CURSOR cur_check_parent (cp_award_cd igs_he_ut_prt_award.award_cd%TYPE,
868 cp_tariff_calc_type igs_he_ut_prt_award.tariff_calc_type_cd%TYPE) IS
869 SELECT parent_award_cd
870 FROM ( SELECT *
871 FROM igs_he_ut_prt_award
872 WHERE tariff_calc_type_cd = cp_tariff_calc_type)
873 START WITH award_cd = cp_award_cd
874 CONNECT BY PRIOR parent_award_cd=award_cd
875 AND tariff_calc_type_cd = cp_tariff_calc_type;
876
877 -- get the person number for the person id for logging user message
878 CURSOR person_number_cur (cp_person_id igs_pe_person.person_id%TYPE ) IS
879 SELECT person_number
880 FROM igs_pe_person_base_v
881 WHERE person_id = cp_person_id;
882
883 l_person_number igs_pe_person.person_number%TYPE ;
884
885 -- check whether person + calculation type already exists.
886 CURSOR check_prsn_tariff_exists_cur (cp_person_id igs_he_ut_prs_calcs.person_id%TYPE,
887 cp_calc_type igs_he_ut_prs_calcs.tariff_calc_type_cd%TYPE) IS
888 SELECT rowid
889 FROM igs_he_ut_prs_calcs
890 WHERE person_id = cp_person_id
891 AND tariff_calc_type_cd = cp_calc_type;
892
893 check_prsn_tariff_exists_rec check_prsn_tariff_exists_cur%ROWTYPE;
894
895 -- cursor to get the person tariff details records for deleting before calculation
896 CURSOR get_prsn_tariff_dtls_cur(cp_person_id igs_he_ut_prs_calcs.person_id%TYPE,
897 cp_calc_type igs_he_ut_prs_calcs.tariff_calc_type_cd%TYPE) IS
898 SELECT upd.rowid
899 FROM igs_he_ut_prs_dtls upd
900 WHERE person_id = cp_person_id
901 AND tariff_calc_type_cd = cp_calc_type;
902
903 l_Int_calc_sql VARCHAR2(32767);
904 l_prs_grp_sql VARCHAR2(32767) := NULL;
905 l_prs_grp_status VARCHAR2(1) := NULL;
906 l_parent_awd_found BOOLEAN;
907 l_tariff_score NUMBER ;
908 l_total_tariff_score NUMBER; -- jchin bug 3678414 Hold the total tariff
909 l_qual_count NUMBER ;
910 l_record_inserted NUMBER := 0;
911 l_person_id igs_uc_qual_dets.person_id%TYPE;
912 l_calc_prsn_tariff VARCHAR2(1);
913 l_rowid VARCHAR2(26);
914 l_prnt_awd_closed VARCHAR2(1);
915
916 --jchakrab added for R12 Performance Enhs (4950293)
917 l_num_rows NUMBER;
918 l_cursor_id NUMBER;
919
920 -- anwest added for Bug #5121695
921 l_group_type VARCHAR2(10);
922
923 BEGIN
924 fnd_dsql.init;
925
926 -- basic sql stmt to get list of persons to be processed.
927 -- l_Int_calc_sql := 'SELECT DISTINCT person_id FROM igs_uc_qual_dets WHERE person_id = NVL(' || p_person_identifier || ', person_id) ';
928 fnd_dsql.add_text('SELECT DISTINCT person_id FROM igs_uc_qual_dets ');
929
930 IF p_person_identifier IS NOT NULL OR p_person_id_grp IS NOT NULL THEN
931
932 fnd_dsql.add_text(' WHERE ');
933
934 IF p_person_identifier IS NOT NULL THEN
935 fnd_dsql.add_text('person_id = ');
936 fnd_dsql.add_bind(p_person_identifier);
937 ELSE
938 fnd_dsql.add_text('1 = 1 ');
939 END IF;
940
941 -- Person ID Group filtering
942 IF p_person_id_grp IS NOT NULL THEN
943 l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status, l_group_type);
944
945 IF l_prs_grp_status <> 'S' THEN
946 fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
947 fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
948 errbuf := fnd_message.get();
949 fnd_file.put_line(fnd_file.log, errbuf); -- this message need to be displayed to user.
950 retcode := '2';
951 RETURN;
952 END IF;
953
954 IF l_group_type = 'STATIC' THEN
955 l_prs_grp_sql := SUBSTR(l_prs_grp_sql, 1, INSTR(UPPER(l_prs_grp_sql), ':P_GROUPID') - 1);
956 END IF;
957
958 -- concatenate the incoming sql stmt to the basic sql to get the complete SQL stmt
959 fnd_dsql.add_text(' AND person_id IN (');
960 fnd_dsql.add_text(l_prs_grp_sql);
961 IF l_group_type = 'STATIC' THEN
962 fnd_dsql.add_bind(p_person_id_grp);
963 END IF;
964 fnd_dsql.add_text(')');
965 l_prs_grp_sql := NULL; -- initializing to NULL as this variable no more required for processing.
966 END IF;
967
968 END IF; -- check for person id or person id group parameters
969
970 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
971 fnd_dsql.set_cursor(l_cursor_id);
972
973 l_Int_calc_sql := fnd_dsql.get_text(FALSE);
974 DBMS_SQL.PARSE(l_cursor_id, l_Int_calc_sql, DBMS_SQL.NATIVE);
975 fnd_dsql.do_binds;
976
977 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_person_id);
978
979 l_num_rows := DBMS_SQL.EXECUTE(l_cursor_id);
980
981 -- Get the list of persons to be processed for the given criteria
982 LOOP
983
984 -- fetch a row
985 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
986 EXIT;
987 END IF;
988
989 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_person_id);
990
991 -- variable initialization
992 l_person_number := NULL;
993 check_prsn_tariff_exists_rec := NULL;
994 l_calc_prsn_tariff := 'Y';
995
996 -- get the person number for message logging
997 OPEN person_number_cur (l_person_id);
998 FETCH person_number_cur INTO l_person_number;
999 CLOSE person_number_cur ;
1000
1001 -- check whether the person + Tariff Calculation type record already exists in the
1002 -- igs_he_ut_prs_calcs table. If no, then insert a new record irrespective of the
1003 -- recalculate parameter as in either case, a new record has to be created.
1004 OPEN check_prsn_tariff_exists_cur(l_person_id, p_tariff_calc_type);
1005 FETCH check_prsn_tariff_exists_cur INTO check_prsn_tariff_exists_rec;
1006 CLOSE check_prsn_tariff_exists_cur ;
1007
1008 IF check_prsn_tariff_exists_rec.rowid IS NULL THEN
1009
1010 -- insert a new record
1011 igs_he_ut_prs_calcs_pkg.insert_row(
1012 x_rowid => check_prsn_tariff_exists_rec.rowid
1013 ,x_tariff_calc_type_cd => p_tariff_calc_type
1014 ,x_person_id => l_person_id
1015 ,x_calc_date => TRUNC(SYSDATE)
1016 ,x_mode => 'R');
1017
1018 -- set the flag to indicate that tariff calculation has to be run
1019 l_calc_prsn_tariff := 'Y';
1020
1021 ELSE
1022 IF p_recalculate = 'Y' THEN
1023
1024 -- update the corresponding record's calc date
1025 igs_he_ut_prs_calcs_pkg.update_row(
1026 x_rowid => check_prsn_tariff_exists_rec.rowid
1027 ,x_tariff_calc_type_cd => p_tariff_calc_type
1028 ,x_person_id => l_person_id
1029 ,x_calc_date => TRUNC(SYSDATE)
1030 ,x_mode => 'R');
1031
1032 -- delete all existing tariff records for the person and insert afresh
1033 FOR del_dtls_rec IN get_prsn_tariff_dtls_cur (l_person_id, p_tariff_calc_type)
1034 LOOP
1035 igs_he_ut_prs_dtls_pkg.delete_row(del_dtls_rec.rowid);
1036 END LOOP;
1037
1038 -- set the flag to allow tariff calculation
1039 l_calc_prsn_tariff := 'Y';
1040
1041 ELSE
1042 -- existing tariff calculated values to be retained and hence not tariff need
1043 -- to be calculated. Only log a message in the log
1044 l_calc_prsn_tariff := 'N';
1045
1046 fnd_message.set_name('IGS','IGS_HE_UT_NOT_RECALC');
1047 fnd_message.set_token('PERSON', l_person_number);
1048 fnd_message.set_token('CALCTYPE', p_tariff_calc_type);
1049 fnd_file.put_line(fnd_file.log, fnd_message.get());
1050
1051 END IF;
1052
1053 END IF; -- check for existence of parent record
1054
1055 --------------------------------------------------------------------
1056 --- MAIN PROCESSING FOR TARIFF CALCULATION
1057 --------------------------------------------------------------------
1058
1059 IF l_calc_prsn_tariff = 'Y' THEN
1060
1061 l_total_tariff_score := 0; --jchin bug 3678414 initialize total tariff score
1062
1063 -- get the distinct exam levels for the person based on the given criteria and setup
1064 FOR prsn_exam_level_rec IN prsn_exam_level_cur(l_person_id, p_tariff_calc_type)
1065 LOOP
1066
1067 -- variable initialization
1068 l_tariff_score := 0;
1069 l_qual_count := 0;
1070
1071 -- get tariff
1072 FOR get_tariff_rec IN get_tariff_cur (l_person_id, prsn_exam_level_rec.exam_level, p_tariff_calc_type)
1073 LOOP
1074
1075 -- variable initialization
1076 l_parent_awd_found := FALSE;
1077
1078 -- Logic for Filtering to prevent double counting
1079 FOR dup_award_rec IN cur_check_dup_awards(l_person_id,
1080 get_tariff_rec.exam_level,
1081 get_tariff_rec.subject_code,
1082 p_tariff_calc_type) LOOP
1083
1084 FOR parent_awd_rec IN cur_check_parent(prsn_exam_level_rec.exam_level, p_tariff_calc_type)
1085 LOOP
1086 IF parent_awd_rec.parent_award_cd = dup_award_rec.exam_level THEN
1087 l_parent_awd_found := TRUE;
1088 EXIT;
1089 END IF;
1090 END LOOP;
1091
1092 EXIT WHEN l_parent_awd_found = TRUE;
1093
1094 END LOOP;
1095
1096 -- jchin bug 3678414 generating total tariff score
1097 IF l_parent_awd_found = FALSE THEN
1098
1099 l_qual_count := l_qual_count + 1;
1100 l_tariff_score := l_tariff_score + NVL(get_tariff_rec.ucas_tariff,0);
1101 l_total_tariff_score := l_total_tariff_score + NVL(get_tariff_rec.ucas_tariff,0);
1102
1103 ELSIF p_report_all_hierarchy_flag = 'Y' THEN
1104
1105 -- Logic for Condition, p_report_all_hierarchy_flag = 'Y'
1106 -- If Qualification has a parent award in the same subject(Double counting scenario) and
1107 -- Report all awards in tariff breakdown is checked then report the Tariff at Exam Level
1108 -- This logic is added as part of HE311FD - JUly 2004 Enhancement Bug, 2956444
1109
1110 -- Update the count and Tariff score. This will get the total of UCAS Tariff and the
1111 -- number of subjects for an Exam level
1112
1113 l_tariff_score := l_tariff_score + NVL(get_tariff_rec.ucas_tariff,0);
1114 l_qual_count := l_qual_count + 1;
1115
1116 END IF;
1117
1118 END LOOP;
1119
1120 -- create tariff details for the person
1121 l_rowid := NULL;
1122 igs_he_ut_prs_dtls_pkg.insert_row(
1123 x_rowid => l_rowid
1124 ,x_tariff_calc_type_cd => p_tariff_calc_type
1125 ,x_person_id => l_person_id
1126 ,x_award_cd => prsn_exam_level_rec.exam_level
1127 ,x_number_of_qual => l_qual_count
1128 ,x_tariff_score => l_tariff_score
1129 ,x_mode => 'R' );
1130
1131 l_record_inserted := l_record_inserted + 1 ;
1132
1133 Fnd_Message.Set_Name('IGS','IGS_HE_EXAM_LEVEL_TARIFF');
1134 Fnd_Message.Set_Token('PERSON_NUMBER',l_person_number);
1135 Fnd_Message.Set_Token('COURSE_CODE', '---');
1136 Fnd_Message.Set_Token('EXAM_LEVEL',prsn_exam_level_rec.Exam_level);
1137 Fnd_Message.Set_Token('SUBJECT_COUNT',l_qual_count);
1138 Fnd_Message.Set_Token('TARIFF_SCORE',l_tariff_score);
1139 fnd_file.put_line(fnd_file.log, fnd_message.get());
1140
1141 END LOOP; -- for distinct exam levels for a person
1142
1143 -- jchin bug 3678414 - display total tariff score
1144 Fnd_Message.Set_Name('IGS','IGS_HE_TOTAL_TARIFF');
1145 Fnd_Message.Set_Token('PERSON_NUMBER',l_person_number);
1146 Fnd_Message.Set_Token('COURSE_CODE', '---');
1147 Fnd_Message.Set_Token('HIGH_QUAL', '---');
1148 Fnd_Message.Set_Token('TOTAL_TARIFF',l_total_tariff_score);
1149 fnd_file.put_line(fnd_file.log, fnd_message.get());
1150
1151 END IF; -- l_calc_prsn_tariff = Y check. If no then bypass the above processing
1152
1153 COMMIT;
1154
1155 END LOOP; -- Loop of Cursor prsn_grp_cur i.e. list of all persons to be processed
1156
1157 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1158
1159 fnd_message.set_name('IGS','IGS_UC_INS_REC_COUNT');
1160 fnd_message.set_token('REC_CNT',l_record_inserted);
1161 fnd_file.put_line(fnd_file.log, fnd_message.get());
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 IF l_cursor_id IS NOT NULL THEN
1166 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1167 END IF;
1168
1169 ROLLBACK;
1170
1171 IF SQLCODE=-1436 THEN
1172 fnd_message.set_name('IGS','IGS_HE_UT_AWD_CYCLIC_REL');
1173 fnd_message.set_token('AWARD',NULL);
1174 ELSE
1175 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1176 fnd_message.set_token('NAME','IGS_HE_UCAS_TARIFF_PKG.internal_tariff_calc - '||SQLERRM);
1177 END IF;
1178
1179 IF ( fnd_log.level_unexpected >= l_debug_level ) THEN
1180 fnd_log.message(fnd_log.level_unexpected, 'igs.plsql.hesa.ucas_tariff_calc.exception', FALSE);
1181 END IF;
1182
1183 fnd_message.retrieve (Errbuf);
1184 Retcode := 2 ;
1185 RETURN;
1186
1187 END internal_tariff_calc;
1188
1189 FUNCTION total_internal_tariff (
1190 p_tariff_calc_type_cd IN igs_he_ut_prs_calcs.tariff_calc_type_cd%TYPE,
1191 p_person_id IN igs_he_ut_prs_calcs.person_id%TYPE)
1192 RETURN NUMBER AS
1193 /*------------------------------------------------------------------
1194 --Created by : AYEDUBAT, Oracle IDC
1195 --Date created: 05-05-2004
1196 --
1197 --Purpose: To calculate the Total Tariff for a person for an internal tariff calculation type
1198 -- Used in the view definition, IGS_HE_UT_PRS_CALCS_V
1199 --Known limitations/enhancements and/or remarks:
1200 --
1201 -- Change History:
1202 WHO WHEN WHAT
1203 AYEDUBAT 05-05-04 Created as part of the bug # 3589897
1204 --------------------------------------------------------------------------*/
1205
1206 -- cursor to get the person tariff details records
1207 CURSOR prsn_tariff_dtls_cur(cp_person_id igs_he_ut_prs_calcs.person_id%TYPE,
1208 cp_calc_type igs_he_ut_prs_calcs.tariff_calc_type_cd%TYPE) IS
1209 SELECT upd.award_cd
1210 FROM igs_he_ut_prs_dtls upd
1211 WHERE person_id = cp_person_id
1212 AND tariff_calc_type_cd = cp_calc_type;
1213
1214 CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
1215 cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
1216 cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
1217 SELECT uqd.Exam_level,
1218 uqd.Subject_code,
1219 MAX(grd.rank) ucas_tariff
1220 FROM igs_uc_qual_dets uqd,
1221 igs_as_grd_sch_grade grd
1222 WHERE uqd.Person_id = cp_person_id
1223 AND uqd.Exam_level = cp_exam_level
1224 AND grd.grade = uqd.approved_result
1225 AND grd.grading_schema_cd = uqd.grading_schema_cd
1226 AND grd.version_number = uqd.version_number
1227 AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
1228 FROM igs_he_ut_excl_qals
1229 WHERE tariff_calc_type_cd = cp_tariff_calc_type
1230 AND (award_cd IS NULL OR award_cd = cp_exam_level)))
1231 GROUP BY uqd.Exam_level, uqd.Subject_code ;
1232
1233 -- cursor to get records for the passed subject with other exam levels
1234 CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
1235 cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
1236 cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
1237 cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
1238 SELECT DISTINCT exam_level
1239 FROM igs_uc_qual_dets
1240 WHERE person_id = cp_person_id
1241 AND exam_level <> cp_exam_level
1242 AND subject_code = cp_subject_cd
1243 AND exam_level IN (SELECT hula.award_cd
1244 FROM igs_he_ut_lvl_award hula
1245 WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
1246 AND hula.closed_ind = 'N')
1247 AND exam_level NOT IN (SELECT hueq.award_cd
1248 FROM igs_he_ut_excl_qals hueq
1249 WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
1250 AND hueq.field_of_study IS NULL);
1251
1252 -- Cursor for checking whether parent award exists for the passed in award.
1253 CURSOR cur_check_parent (cp_award_cd igs_he_ut_prt_award.award_cd%TYPE,
1254 cp_tariff_calc_type igs_he_ut_prt_award.tariff_calc_type_cd%TYPE) IS
1255 SELECT parent_award_cd
1256 FROM ( SELECT *
1257 FROM igs_he_ut_prt_award
1258 WHERE tariff_calc_type_cd = cp_tariff_calc_type)
1259 START WITH award_cd = cp_award_cd
1260 CONNECT BY PRIOR parent_award_cd=award_cd
1261 AND tariff_calc_type_cd = cp_tariff_calc_type;
1262
1263 l_parent_awd_found BOOLEAN;
1264 l_tariff_score NUMBER(15);
1265
1266 BEGIN
1267
1268 l_tariff_score := 0;
1269
1270 -- Get the Exam levels for the person from the Person UCAS Tariff Details table
1271 FOR prsn_tariff_dtls_rec IN prsn_tariff_dtls_cur(p_person_id, p_tariff_calc_type_cd) LOOP
1272
1273 -- Get the UCAS Tariff
1274 FOR get_tariff_rec IN get_tariff_cur (p_person_id, prsn_tariff_dtls_rec.award_cd, p_tariff_calc_type_cd) LOOP
1275
1276 -- variable initialization
1277 l_parent_awd_found := FALSE;
1278
1279 -- Logic for Filtering to prevent double counting
1280 FOR dup_award_rec IN cur_check_dup_awards(p_person_id,
1281 get_tariff_rec.exam_level,
1282 get_tariff_rec.subject_code,
1283 p_tariff_calc_type_cd) LOOP
1284
1285 FOR parent_awd_rec IN cur_check_parent(prsn_tariff_dtls_rec.award_cd, p_tariff_calc_type_cd) LOOP
1286
1287 IF parent_awd_rec.parent_award_cd = dup_award_rec.exam_level THEN
1288 l_parent_awd_found := TRUE;
1289 EXIT;
1290 END IF;
1291
1292 END LOOP;
1293
1294 EXIT WHEN l_parent_awd_found = TRUE;
1295
1296 END LOOP;
1297
1298 IF l_parent_awd_found = FALSE THEN
1299
1300 l_tariff_score := l_tariff_score + NVL(get_tariff_rec.ucas_tariff,0);
1301
1302 END IF;
1303
1304 END LOOP;
1305
1306 END LOOP;
1307
1308 RETURN l_tariff_score;
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1313 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT2_PKG.TOTAL_INTERNAL_TARIFF');
1314 IGS_GE_MSG_STACK.ADD;
1315 App_Exception.Raise_Exception;
1316
1317 END total_internal_tariff;
1318
1319
1320 -- ============================== END OF INTERNAL TARIFF CALCULATION ===========================
1321
1322 PROCEDURE ucas_tariff_calc (
1323 errbuf OUT NOCOPY VARCHAR2,
1324 retcode OUT NOCOPY NUMBER,
1325 p_tariff_calc_type IN VARCHAR2,
1326 p_calculate_tariff IN VARCHAR2,
1327 p_person_id_grp IN NUMBER,
1328 p_person_identifier IN NUMBER,
1329 p_program_group IN VARCHAR2,
1330 p_program_type IN VARCHAR2,
1331 p_course_code IN VARCHAR2,
1332 p_start_date IN VARCHAR2,
1333 p_end_date IN VARCHAR2,
1334 P_recalculate IN VARCHAR2
1335 ) IS
1336
1337 /*------------------------------------------------------------------
1338 --Created by : Bayadav, Oracle IDC
1339 --Date created: Sekhar Kappaganti
1340 --
1341 --Purpose: This is to import UCAS tariff data from Sec/ter table in to SPA table
1342 --Known limitations/enhancements and/or remarks:
1343 --
1344 --Change History:
1345 WHO WHEN WHAT
1346 smvk 03-Jun-2003 Bug # 2858436.Modified the cursor c_prgawd to select open program awards only.
1347 rbezawad 13-Feb-03 Modified w.r.t. HEFD202.1 build, Bug 2717744.
1348 Introduced logic to aviod double counting of qualifications
1349 pmarada 24-jul-2003 Before creating the ucas tariff scores for a student deleting old
1350 ucas tariff score details. so removed the Igs_he_st_spa_ut_all_pkg update row call
1351 and added delete row call. as per the bug 3064689
1352 rgangara 29-Aug-03 Added 4 new parameters and created this as a separate procedure
1353 for ease of understanding and maintenance
1354 ayedubat 16-MAR-04 Added a new parameter, p_report_all_hierarchy_flag to the internal and
1355 external tariff calculation procedure calls for Bug, 2956444
1356 anwest 18-JAN-20 Bug# 4950285 R12 Disable OSS Mandate
1357 -----------------------------------------------------------------------*/
1358
1359 CURSOR get_calc_type_cur (p_tariff_calc_type igs_he_ut_calc_type.tariff_calc_type_cd%TYPE) IS
1360 SELECT tariff_calc_type_cd,
1361 external_calc_ind,
1362 report_all_hierarchy_flag
1363 FROM igs_he_ut_calc_type
1364 WHERE tariff_calc_type_cd = p_tariff_calc_type;
1365
1366 get_calc_type_rec get_calc_type_cur%ROWTYPE;
1367
1368 BEGIN
1369
1370 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
1371 IGS_GE_GEN_003.SET_ORG_ID;
1372
1373 fnd_file.put_line(fnd_file.log, '-------------------------------------------------------');
1374 fnd_file.put_line(fnd_file.log, 'P_TARIFF_CALC_TYPE = ' || p_tariff_calc_type);
1375 fnd_file.put_line(fnd_file.log, 'P_CALCULATE_TARIFF = ' || p_calculate_tariff);
1376 fnd_file.put_line(fnd_file.log, 'P_PERSON_ID_GRP = ' || p_person_id_grp);
1377 fnd_file.put_line(fnd_file.log, 'P_PERSON_IDENTIFIER = ' || TO_CHAR(p_person_identifier));
1378 fnd_file.put_line(fnd_file.log, 'P_PROGRAM_GROUP = ' || p_program_group);
1379 fnd_file.put_line(fnd_file.log, 'P_PROGRAM_TYPE = ' || p_program_type);
1380 fnd_file.put_line(fnd_file.log, 'P_COURSE_CODE = ' || p_course_code);
1381 fnd_file.put_line(fnd_file.log, 'P_START_DATE = ' || p_start_date);
1382 fnd_file.put_line(fnd_file.log, 'P_END_DATE = ' || p_end_date);
1383 fnd_file.put_line(fnd_file.log, 'P_RECALCULATE = ' || p_recalculate);
1384 fnd_file.put_line(fnd_file.log, '-------------------------------------------------------');
1385
1386
1387 /* Checking whether the UK profile is enabled */
1388 IF NOT (igs_uc_utils.is_ucas_hesa_enabled) THEN
1389
1390 fnd_message.set_name('IGS','IGS_UC_HE_NOT_ENABLED');
1391 fnd_file.put_line(fnd_file.log, fnd_message.get()); -- display to user also
1392 -- also log using the new logging framework
1393 IF (fnd_log.level_statement >= l_debug_level ) THEN
1394 fnd_log.string( fnd_log.level_statement, 'igs.plsql.hesa.ucas_tariff_calc.validation', fnd_message.get());
1395 END IF;
1396 retcode := 3 ;
1397 RETURN ;
1398
1399 END IF;
1400
1401 -- Fetch the details of the Tariff Calculation Type
1402 get_calc_type_rec := NULL;
1403 OPEN get_calc_type_cur (p_tariff_calc_type);
1404 FETCH get_calc_type_cur INTO get_calc_type_rec;
1405 CLOSE get_calc_type_cur;
1406
1407 IF p_calculate_tariff = 'Y' THEN
1408
1409 IF get_calc_type_rec.external_calc_ind <> 'Y' THEN
1410 --Display log message that HESA Tariff calculation can only be done for External Flagged Calculation type.
1411 fnd_message.set_name('IGS','IGS_HE_UT_CALC_NOT_EXTERNAL');
1412 fnd_file.put_line(fnd_file.log, fnd_message.get());
1413 retcode := 3;
1414 RETURN;
1415 END IF;
1416
1417 END IF;
1418
1419 -- If internal tariff calculation and start or end dates are not null then log an appropriate message and continue
1420 IF p_calculate_tariff = 'N' AND (p_start_date IS NOT NULL OR p_end_date IS NOT NULL) THEN
1421 fnd_message.set_name('IGS','IGS_HE_UT_DATE_NOT_RELEVANT');
1422 fnd_file.put_line(fnd_file.log, fnd_message.get());
1423 END IF;
1424
1425 -- for Internal tariff calculation, parameters
1426 -- p_course_cd, p_start_date, p_end_date, p_program_group and p_program_type
1427 -- have got no relevance.
1428 -- Based on the HESA Tariff Calculation parameter, call either
1429 -- internal or external tariff calculation
1430 IF p_calculate_tariff = 'Y' THEN
1431
1432 -- external tariff calculation
1433 External_tariff_calc (
1434 errbuf => errbuf ,
1435 retcode => retcode ,
1436 p_person_identifier => p_person_identifier,
1437 p_course_code => p_course_code ,
1438 p_start_date => p_start_date ,
1439 p_end_date => p_end_date ,
1440 p_tariff_calc_type => p_tariff_calc_type ,
1441 p_calculate_tariff => p_calculate_tariff ,
1442 P_recalculate => p_recalculate ,
1443 p_person_id_grp => p_person_id_grp ,
1444 p_program_group => p_program_group ,
1445 p_program_type => p_program_type ,
1446 p_report_all_hierarchy_flag => get_calc_type_rec.report_all_hierarchy_flag );
1447
1448 ELSE
1449
1450 -- Internal tariff calculation
1451 Internal_tariff_calc (
1452 errbuf => errbuf ,
1453 retcode => retcode ,
1454 p_person_identifier => p_person_identifier,
1455 p_tariff_calc_type => p_tariff_calc_type ,
1456 p_calculate_tariff => p_calculate_tariff ,
1457 p_recalculate => p_recalculate ,
1458 p_person_id_grp => p_person_id_grp ,
1459 p_report_all_hierarchy_flag => get_calc_type_rec.report_all_hierarchy_flag );
1460
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 ROLLBACK;
1466
1467 IF SQLCODE=-1436 THEN
1468 fnd_message.set_name('IGS','IGS_HE_UT_AWD_CYCLIC_REL');
1469 fnd_message.set_token('AWARD',NULL);
1470 ELSE
1471 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1472 fnd_message.set_token('NAME','IGS_HE_UCAS_TARIFF_PKG.UCAS_TARIFF_CALC - '||SQLERRM);
1473 END IF;
1474
1475 IF (fnd_log.level_unexpected >= l_debug_level) THEN
1476 fnd_log.message(fnd_log.level_unexpected, 'igs.plsql.hesa.ucas_tariff_calc.exception', FALSE);
1477 END IF;
1478
1479 fnd_message.retrieve (Errbuf);
1480 Retcode := 2 ;
1481 igs_ge_msg_stack.conc_exception_hndl;
1482
1483 END ucas_tariff_calc;
1484
1485 END igs_he_ucas_tariff_pkg;
[Home] [Help]