1 PACKAGE BODY igs_ps_gen_002 AS
2 /* $Header: IGSPS02B.pls 120.3 2006/06/14 07:03:37 ckasu noship $ */
3 /* CAHNGE HISTORY
4 WHO WHEN WHAT
5 ckasu 14-JUN-2006 modified as a part of bug#5299752 inorder to include attendance type
6 as parameter in crsp_get_crv_eftd PROCEDURE
7 ckasu 07-MAR-2006 modified as a part of bug#5070746 inorder to include location_cd , attendance mode
8 as parameters in crsp_get_crv_eftd PROCEDURE
9 ijeddy 05-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
10 smvk 10-Oct-2003 Enh # 3052445. Added p_n_max_wlst_per_stud to the signature of crsp_ins_cv_hist.
11 Nishikant 11DEC2002 ENCR027 Build (Program Length Integration). The function crsp_get_crv_eftd revamped.
12 vvutukur 19-Oct-2002 Enh#2608227.Modified crsp_get_crv_eftd,crsp_ins_cv_hist.
13 ayedubat 25-MAY-2001 midified the procedure,crsp_ins_cv_hist to add the new columns.
14 avenkatr 29-AUG-2001 removed procedure "crsp_val_crv_quality"
15 knaraset 8-Jan-2003 Modified the code to fetch alias value instead of absolete value
16 while getting the load effective date bug 2739128
17 Rvivekan 26-6-2003 Bug#2931318. The logic of crsp_get_crv_eftd has been completed revamped. The function now calculates
18 attendance type byt chosing the attendance type with the greatest research percentage.
19
20 */
21
22 FUNCTION crsp_get_course_ttl(
23 p_course_cd IN igs_ps_course.course_cd%TYPE )
24 RETURN VARCHAR2 AS
25 BEGIN -- crsp_get_course_ttl
26 -- This module returns the IGS_PS_COURSE version IGS_PE_TITLE for a IGS_PS_COURSE code.
27 -- If no IGS_PS_COURSE version is found then NULL is returned.
28 DECLARE
29
30 cst_active CONSTANT igs_ps_stat.s_course_status%TYPE := 'ACTIVE';
31 cst_planned CONSTANT igs_ps_stat.s_course_status%TYPE := 'PLANNED';
32
33 v_title igs_ps_ver.title%TYPE;
34
35 CURSOR c_crv_crst IS
36 SELECT crv.title
37 FROM igs_ps_ver crv,
38 igs_ps_stat crst
39 WHERE crv.course_cd = p_course_cd AND
40 crst.course_status = crv.course_status
41 ORDER BY decode( crst.s_course_status,
42 cst_active, 1,
43 cst_planned, 2, 3) ASC,
44 crv.expiry_dt DESC,
45 crv.version_number DESC;
46 BEGIN
47 OPEN c_crv_crst;
48 FETCH c_crv_crst INTO v_title;
49 IF c_crv_crst%NOTFOUND THEN
50 CLOSE c_crv_crst;
51 RETURN NULL;
52 END IF;
53 CLOSE c_crv_crst;
54 RETURN v_title;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF (c_crv_crst%isopen) THEN
58 CLOSE c_crv_crst;
59 END IF;
60 app_exception.raise_exception;
61 END;
62 EXCEPTION
63 WHEN OTHERS THEN
64 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
65 fnd_message.set_token('NAME','IGS_PS_GEN_002.crsp_get_course_ttl');
66 igs_ge_msg_stack.add;
67 app_exception.raise_exception;
68 END crsp_get_course_ttl;
69
70 FUNCTION crsp_get_crv_eftd( p_person_id IN NUMBER ,
71 p_course_cd IN VARCHAR2)
72 RETURN NUMBER AS
73 /*----------------------------------------------------------------------------
74 || Created By :
75 || Created On :
76 || Purpose : Get the program length, program length measurement for a course
77 || offering option. This is derived from program_length,program_length_measurement
78 || fields.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 || ckasu 07-MAR-2006 modified as a part of bug#5070746 inorder to include location_cd , attendance mode
84 || as parameters.
85 || Rvivekan 26-6-2003 Bug#2931318. The logic of this function has bee completed revamped. The function now calculates
86 || attendance type byt chosing the attendance type with the greatest research percentage.
87 || Nishikant 12MAR2003 Bug#2843854. The Cursor c_check_att_type was retrieving the Program Length and Measurement from
88 || the Uoo_id passed as a parameter. Now its modified to be based upon the parameters course_cd,
89 || version_number, cal_type, attendance_type. So that if a student has been attempted a Half Time
90 || Program Offering Option, and if the Program Length and Measurement are specified for a Full time
91 || offering option, then these will be considered. And the cursor c_coo_id removed.
92 || Nishikant 11DEC2002 ENCR027 Build (Program Length Integration) . The Function revamped fully to calculate Total
93 || EFTD for research students based on the FT offering for the Program with the highest FTE value
94 || and not based on the offering the student is attempting.
95 || vvutukur 19-Oct-2002 Enh#2608227.Modified cursor c_get and its usage in the code
96 || to fetch program length and program length measurement by removing
97 || references to std_ft_completion_time.
98 ----------------------------------------------------------------------------*/
99
100 CURSOR c_stu_crs_atmpt (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
101 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
102 SELECT sca.cal_type, sca.version_number,sca.location_cd,sca.attendance_mode,sca.attendance_type
103 FROM igs_en_stdnt_ps_att sca
104 WHERE sca.person_id = cp_person_id
105 AND sca.course_cd = cp_course_cd;
106 --
107
108 CURSOR c_stu_crs_atmpt_from_appl (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
109 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
110 SELECT aav.acad_cal_type, acai.crv_version_number , acai.location_cd,acai.attendance_mode,acai.attendance_type
111 FROM igs_ad_appl aav,
112 igs_ad_ps_appl_inst acai
113 WHERE acai.person_id=cp_person_id AND acai.course_cd=cp_course_cd AND
114 aav.person_id = acai.person_id AND aav.admission_appl_number = acai.admission_appl_number;
115
116
117 CURSOR c_check_att_type (cp_course_cd igs_ps_ofr_opt_all.course_cd%TYPE,
118 cp_version_number igs_ps_ofr_opt_all.version_number%TYPE,
119 cp_cal_type igs_ps_ofr_opt_all.cal_type%TYPE,
120 cp_attendance_type igs_ps_ofr_opt_all.attendance_type%TYPE,
121 cp_location igs_ps_ofr_opt_all.location_cd%TYPE,
122 cp_attendance_mode igs_ps_ofr_opt_all.attendance_mode%TYPE) IS
123 SELECT program_length, program_length_measurement
124 FROM igs_ps_ofr_opt_all
125 WHERE course_cd = cp_course_cd AND
126 version_number = cp_version_number AND
127 cal_type = cp_cal_type AND
128 attendance_type = cp_attendance_type AND
129 location_cd = cp_location AND
130 attendance_mode = cp_attendance_mode AND
131 program_length IS NOT NULL AND
132 program_length_measurement IS NOT NULL;
133 l_program_length igs_ps_ofr_opt_all.program_length%TYPE;
134 l_program_length_measurement igs_ps_ofr_opt_all.program_length_measurement%TYPE;
135 l_version_number igs_en_stdnt_ps_att.version_number%TYPE;
136 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
137 l_message_name fnd_new_messages.message_name%TYPE;
138 l_ret_value NUMBER := 0;
139 l_total_eftd NUMBER := 0;
140 l_proglength_found BOOLEAN :=FALSE;
141 l_location_cd igs_ps_ofr_opt_all.location_cd%TYPE;
142 l_attendance_mode igs_ps_ofr_opt_all.attendance_mode%TYPE;
143 l_attendance_type igs_ps_ofr_opt_all.attendance_type%TYPE;
144 BEGIN
145 l_message_name := NULL;
146 l_total_eftd := l_ret_value; --If returns without calculating EFTD, then p_total_eftd will be zero
147
148 OPEN c_stu_crs_atmpt (p_person_id,
149 p_course_cd);
150 FETCH c_stu_crs_atmpt INTO l_acad_cal_type, l_version_number,l_location_cd,l_attendance_mode,l_attendance_type;
151 IF (c_stu_crs_atmpt%NOTFOUND) THEN
152 --
153 -- if not data found return from the program unit
154 --
155 CLOSE c_stu_crs_atmpt;
156 OPEN c_stu_crs_atmpt_from_appl (p_person_id,p_course_cd);
157 FETCH c_stu_crs_atmpt_from_appl INTO l_acad_cal_type, l_version_number,l_location_cd,l_attendance_mode,l_attendance_type;
158 IF (c_stu_crs_atmpt_from_appl%NOTFOUND) THEN
159 CLOSE c_stu_crs_atmpt_from_appl;
160 l_message_name := 'IGS_EN_NO_CRS_ATMPT';
161 l_total_eftd := -2;
162 RETURN l_total_eftd;
163 END IF;
164 CLOSE c_stu_crs_atmpt_from_appl;
165 ELSE
166 CLOSE c_stu_crs_atmpt;
167 END IF;
168
169 --Get the Program Length and Program Length Measurement for the Full Time Attendance Type
170 --in the same Program offering, the student is attempting
171 OPEN c_check_att_type ( p_course_cd, l_version_number, l_acad_cal_type,l_attendance_type,l_location_cd,l_attendance_mode);
172 FETCH c_check_att_type INTO l_program_length, l_program_length_measurement;
173
174 IF c_check_att_type%FOUND AND l_program_length IS NOT NULL AND l_program_length_measurement IS NOT NULL THEN
175 l_proglength_found:=TRUE;
176 CLOSE c_check_att_type;
177 ELSE
178 CLOSE c_check_att_type;
179 END IF;
180
181
182 IF l_proglength_found=FALSE THEN
183 l_message_name:='IGS_EN_FT_OFR_INCOMPL';
184 l_total_eftd := 0;
185 RETURN l_total_eftd;
186 END IF;
187
188
189 --If FT Attendance Type has been found at the Program Offering option then
190 --calculate the total EFTD according to the value of Program Length and
191 --Program Length Measurement return the calculated EFTD.
192
193 IF l_program_length_measurement = 'YEAR' THEN
194 l_ret_value := l_program_length*365;
195 ELSIF l_program_length_measurement = 'MONTHS' THEN
196 l_ret_value := l_program_length*365/12;
197 ELSIF l_program_length_measurement = 'DAYS' THEN
198 l_ret_value := l_program_length;
199 ELSIF l_program_length_measurement = 'WEEKS' THEN
200 l_ret_value := l_program_length*7;
201 ELSIF l_program_length_measurement = 'HOURS' THEN
202 l_ret_value := l_program_length/24;
203 ELSIF l_program_length_measurement = '10TH OF A YEAR' THEN
204 l_ret_value := l_program_length*365/10;
205 ELSIF l_program_length_measurement = 'MINUTES' THEN
206 l_ret_value := l_program_length/(24*60);
207 END IF;
208
209 l_total_eftd := l_ret_value;
210 RETURN l_total_eftd;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 RAISE;
215 END crsp_get_crv_eftd;
216
217 FUNCTION crsp_get_un_lvl(
218 p_unit_cd IN VARCHAR2 ,
219 p_unit_version_number IN NUMBER ,
220 p_course_cd IN VARCHAR2 ,
221 p_course_version_number IN NUMBER )
222 RETURN VARCHAR2 AS
223 BEGIN
224 DECLARE
225 v_unit_level igs_ps_unit_lvl.unit_level%TYPE;
226 CURSOR c_cul_crv IS
227 SELECT cul.unit_level
228 FROM igs_ps_unit_lvl cul
229 WHERE cul.course_cd = p_course_cd AND
230 cul.version_number = p_course_version_number AND
231 cul.unit_cd = p_unit_cd AND
232 cul.version_number = p_unit_version_number;
233 CURSOR c_uv IS
234 SELECT uv.unit_level
235 FROM igs_ps_unit_ver uv
236 WHERE uv.unit_cd = p_unit_cd AND
237 uv.version_number = p_unit_version_number;
238 BEGIN
239 -- Get the IGS_PS_UNIT level of a IGS_PS_UNIT attempt within a nominated IGS_PS_COURSE.
240 -- Searches for the existence of a IGS_PS_UNIT_LVL record using the
241 -- IGS_PS_UNIT.level
242 -- 1. Search for a IGS_PS_UNIT_LVL record matching the IGS_PS_UNIT version and the
243 -- IGS_PS_COURSE type of the nominated IGS_PS_COURSE.
244 OPEN c_cul_crv;
245 FETCH c_cul_crv INTO v_unit_level;
246 IF (c_cul_crv%FOUND) THEN
247 CLOSE c_cul_crv;
248 RETURN v_unit_level;
249 END IF;
250 CLOSE c_cul_crv;
251 OPEN c_uv;
252 FETCH c_uv INTO v_unit_level;
253 IF (c_uv%NOTFOUND) THEN
254 CLOSE c_uv;
255 RETURN NULL;
256 END IF;
257 CLOSE c_uv;
258 RETURN v_unit_level;
259 END;
260 EXCEPTION
261 WHEN OTHERS THEN
262 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
263 fnd_message.set_token('NAME','IGS_PS_GEN_002.crsp_get_un_lvl');
264 igs_ge_msg_stack.add;
265 app_exception.raise_exception;
266 END crsp_get_un_lvl;
267
268 PROCEDURE crsp_ins_cfos_hist(
269 p_course_cd IN VARCHAR2 ,
270 p_version_number IN NUMBER ,
271 p_field_of_study IN VARCHAR2 ,
272 p_last_update_on IN DATE ,
273 p_update_on IN DATE ,
274 p_last_update_who IN VARCHAR2 ,
275 p_percentage IN NUMBER ,
276 p_major_field_ind IN VARCHAR2)
277 AS
278 v_s_course_status igs_ps_stat.s_course_status%TYPE;
279 x_rowid VARCHAR2(25);
280 l_org_id NUMBER(15);
281
282 CURSOR c_get_course_status IS
283 SELECT s_course_status
284 FROM igs_ps_stat, igs_ps_ver
285 WHERE igs_ps_ver.course_cd = p_course_cd AND
286 igs_ps_ver.version_number = p_version_number AND
287 igs_ps_stat.course_status = igs_ps_ver.course_status;
288 BEGIN
289 OPEN c_get_course_status;
290 FETCH c_get_course_status INTO v_s_course_status;
291 CLOSE c_get_course_status;
292 l_org_id := igs_ge_gen_003.get_org_id;
293 IF v_s_course_status = 'ACTIVE' THEN
294 igs_ps_fld_std_hist_pkg.insert_row(
295 x_rowid => x_rowid,
296 x_course_cd => p_course_cd,
297 x_field_of_study => p_field_of_study,
298 x_hist_start_dt => p_last_update_on,
299 x_version_number => p_version_number,
300 x_hist_end_dt => p_update_on,
301 x_hist_who => p_last_update_who,
302 x_percentage => p_percentage,
303 x_major_field_ind => p_major_field_ind,
304 x_mode => 'R',
305 x_org_id => l_org_id);
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
310 fnd_message.set_token('NAME','IGS_PS_GEN_002.crsp_ins_cfos_hist');
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313 END crsp_ins_cfos_hist;
314
315 PROCEDURE crsp_ins_cv_hist(
316 p_course_cd IN VARCHAR2 ,
317 p_version_number IN NUMBER ,
318 p_last_update_on IN DATE ,
319 p_update_on IN DATE ,
320 p_last_update_who IN VARCHAR2 ,
321 p_start_dt IN DATE ,
322 p_review_dt IN DATE ,
323 p_expiry_dt IN DATE ,
324 p_end_dt IN DATE ,
325 p_course_status IN VARCHAR2 ,
326 p_title IN VARCHAR2 ,
327 p_short_title IN VARCHAR2 ,
328 p_abbreviation IN VARCHAR2 ,
329 p_supp_exam_permitted_ind IN VARCHAR2 ,
330 p_generic_course_ind IN VARCHAR2 ,
331 p_graduate_students_ind IN VARCHAR2 ,
332 p_count_intrmsn_in_time_ind IN VARCHAR2 ,
333 p_intrmsn_allowed_ind IN VARCHAR2 ,
334 p_course_type IN VARCHAR2 ,
335 p_responsible_org_unit_cd IN VARCHAR2 ,
336 p_responsible_ou_start_dt IN DATE ,
337 p_govt_special_course_type IN VARCHAR2 ,
338 p_qualification_recency IN NUMBER ,
339 p_external_adv_stnd_limit IN NUMBER ,
340 p_internal_adv_stnd_limit IN NUMBER ,
341 p_contact_hours IN NUMBER ,
342 p_credit_points_required IN NUMBER ,
343 p_govt_course_load IN NUMBER ,
344 p_std_annual_load IN NUMBER ,
345 p_course_total_eftsu IN NUMBER ,
346 p_max_intrmsn_duration IN NUMBER ,
347 p_num_of_units_before_intrmsn IN NUMBER ,
348 p_min_sbmsn_percentage IN NUMBER,
349 p_min_cp_per_calendar IN NUMBER,
350 p_approval_date IN DATE,
351 p_external_approval_date IN DATE,
352 p_federal_financial_aid IN VARCHAR2,
353 p_institutional_financial_aid IN VARCHAR2,
354 p_max_cp_per_teaching_period IN NUMBER,
355 p_residency_cp_required IN NUMBER,
356 p_state_financial_aid IN VARCHAR2,
357 p_primary_program_rank IN NUMBER,
358 p_n_max_wlst_per_stud IN NUMBER,
359 p_n_annual_instruction_time IN NUMBER)
360 AS
361 v_ct_description igs_ps_type.description%TYPE;
362 v_ou_description igs_or_unit.description%TYPE;
363 v_gsct_description igs_ps_govt_spl_type.description%TYPE;
364 x_rowid VARCHAR(25);
365 l_org_id NUMBER(15);
366 CURSOR c_find_ct_desc IS
367 SELECT description
368 FROM igs_ps_type
369 WHERE course_type = p_course_type;
370 CURSOR c_find_ou_desc IS
371 SELECT party_name description
372 FROM igs_or_inst_org_base_v
373 WHERE party_number = p_responsible_org_unit_cd AND
374 start_dt = p_responsible_ou_start_dt;
375 CURSOR c_find_gsct_desc IS
376 SELECT description
377 FROM igs_ps_govt_spl_type
378 WHERE govt_special_course_type = p_govt_special_course_type;
379 /*************************************************************
380 Created By :
381 Date Created By :
382 Purpose :
383 Know limitations, enhancements or remarks
384 Change History
385 Who When What
386 --sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_find_ou_desc
387 sarakshi 23-Jan-2004 Enh#3345205, added column annual_instruction_time in the TBH call
388 vvutukur 19-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time as these
389 columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
390 ayedubat 25-MAY-2001 Added the new columns
391 (reverse chronological order - newest change first)
392 ***************************************************************/
393 BEGIN
394 IF p_course_type IS NULL THEN
395 v_ct_description := NULL;
396 ELSE
397 OPEN c_find_ct_desc;
398 FETCH c_find_ct_desc INTO v_ct_description;
399 CLOSE c_find_ct_desc;
400 END IF;
401 IF p_responsible_org_unit_cd IS NULL THEN
402 v_ou_description := NULL;
403 ELSE
404 OPEN c_find_ou_desc;
405 FETCH c_find_ou_desc INTO v_ou_description;
406 CLOSE c_find_ou_desc;
407 END IF;
408 IF p_govt_special_course_type IS NULL THEN
409 v_gsct_description := NULL;
410 ELSE
411 OPEN c_find_gsct_desc;
412 FETCH c_find_gsct_desc INTO v_gsct_description;
413 CLOSE c_find_gsct_desc;
414 END IF;
415 l_org_id := igs_ge_gen_003.get_org_id;
416
417 igs_ps_ver_hist_pkg.insert_row(
418 x_rowid => x_rowid,
419 x_course_cd => p_course_cd,
420 x_version_number => p_version_number,
421 x_hist_start_dt => p_last_update_on,
422 x_hist_end_dt => p_update_on,
423 x_hist_who => p_last_update_who,
424 x_start_dt => p_start_dt,
425 x_review_dt => p_review_dt,
426 x_expiry_dt => p_expiry_dt,
427 x_end_dt => p_end_dt,
428 x_course_status => p_course_status,
429 x_title => p_title,
430 x_short_title => p_short_title,
431 x_abbreviation => p_abbreviation,
432 x_supp_exam_permitted_ind => p_supp_exam_permitted_ind,
433 x_generic_course_ind => p_generic_course_ind,
434 x_graduate_students_ind => p_graduate_students_ind,
435 x_count_intrmsn_in_time_ind => p_count_intrmsn_in_time_ind,
436 x_intrmsn_allowed_ind => p_intrmsn_allowed_ind,
437 x_course_type => p_course_type,
438 x_ct_description => v_ct_description,
439 x_responsible_org_unit_cd => p_responsible_org_unit_cd,
440 x_responsible_ou_start_dt => p_responsible_ou_start_dt,
441 x_ou_description => v_ou_description,
442 x_govt_special_course_type => p_govt_special_course_type,
443 x_gsct_description => v_gsct_description,
444 x_qualification_recency => p_qualification_recency,
445 x_external_adv_stnd_limit => p_external_adv_stnd_limit,
446 x_internal_adv_stnd_limit => p_internal_adv_stnd_limit,
447 x_contact_hours => p_contact_hours,
448 x_credit_points_required => p_credit_points_required,
449 x_govt_course_load => p_govt_course_load,
450 x_std_annual_load => p_std_annual_load,
451 x_course_total_eftsu => p_course_total_eftsu,
452 x_max_intrmsn_duration => p_max_intrmsn_duration,
453 x_num_of_units_before_intrmsn=> p_num_of_units_before_intrmsn,
454 x_min_sbmsn_percentage => p_min_sbmsn_percentage,
455 x_min_cp_per_calendar => p_min_cp_per_calendar,
456 x_approval_date => p_approval_date,
457 x_external_approval_date => p_external_approval_date,
458 x_federal_financial_aid => p_federal_financial_aid ,
459 x_institutional_financial_aid=> p_institutional_financial_aid ,
460 x_max_cp_per_teaching_period => p_max_cp_per_teaching_period ,
461 x_residency_cp_required => p_residency_cp_required,
462 x_state_financial_aid => p_state_financial_aid,
463 x_primary_program_rank => p_primary_program_rank,
464 x_max_wlst_per_stud => p_n_max_wlst_per_stud,
465 x_mode => 'R',
466 x_org_id => l_org_id,
467 x_annual_instruction_time => p_n_annual_instruction_time);
468 EXCEPTION
469 WHEN OTHERS THEN
470 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
471 fnd_message.set_token('NAME','IGS_PS_GEN_002.crsp_ins_cv_hist');
472 igs_ge_msg_stack.add;
473 app_exception.raise_exception;
474 END crsp_ins_cv_hist;
475
476
477 END igs_ps_gen_002;
478
479
[Home] [Help]