1 PACKAGE BODY Igs_Pr_Get_Class_Std AS
2 /* $Header: IGSPR28B.pls 120.2 2006/04/29 02:27:11 swaghmar ship $ */
3
4 ----------------------------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 -- ddey 27-Oct-2003 Changes are done, so that the message stack is not initilized.(Bug # 3163305)
8 -- In the function Get_Class_Standing an extra parameter 'p_init_msg_list' is added.
9 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
10 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
11 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
12 --ptandon 20-Nov-03 Modified cursor c_spa in Function Get_Class_Standing to consider
13 -- class standing in term records table as per Term Based Fee Calc build.
14 -- Enh. Bug# 2829263.
15 --swaghmar 15-Sep-2005 Bug# 4491456 Changed datatypes for l_gpa_value, l_gpa_cp, l_gpa_quality_points
16 --swaghmar 25-Apr-2006 Bug# 5171158 Enabled FND Logging and added call to user hook procedure
17 -- for the customers to customize the derivation of class standing
18 ----------------------------------------------------------------------------------------------------------------
19 FUNCTION Get_Class_Standing(
20 p_PERSON_ID IN NUMBER,
21 p_COURSE_CD IN VARCHAR2,
22 p_Predictive_ind IN VARCHAR2 DEFAULT 'N',
23 p_Effective_dt IN DATE,
24 p_Load_Cal_type IN VARCHAR2,
25 p_Load_Ci_Sequence_Number IN NUMBER,
26 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
27 )
28 RETURN VARCHAR2 IS
29 /* Define the required local variables */
30 l_igs_pr_class_std_id igs_pr_class_std.igs_pr_class_std_id%TYPE;
31 l_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE;
32 l_consider_changes igs_pr_cs_schdl.consider_changes%TYPE;
33 l_class_standing_override igs_pr_class_std.class_standing%TYPE;
34 l_min_cp_count igs_pr_css_class_std.min_cp%TYPE;
35 l_acad_year_count igs_pr_css_class_std.acad_year%TYPE;
36 l_acad_year igs_pr_css_class_std.acad_year%TYPE;
37 l_class_standing igs_pr_class_std.class_standing%TYPE;
38 l_cp igs_pr_css_class_std.min_cp%TYPE;
39 l_dummy igs_pr_css_class_std.min_cp%TYPE;
40 l_enrolled_cp igs_pr_css_class_std.min_cp%TYPE;
41 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
42 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
43 l_Load_Cal_type igs_ca_inst.cal_type%TYPE;
44 l_Load_Ci_Sequence_Number igs_ca_inst.sequence_number%TYPE;
45 l_effective_dt igs_pr_cs_schdl.start_dt%TYPE;
46 l_earned_cp NUMBER;
47 l_attempted_cp NUMBER;
48 l_return_status VARCHAR2(100);
49 l_msg_count NUMBER(6);
50 l_msg_data VARCHAR2(1000);
51
52 /* added jhanda bug 3843525 */
53 l_gpa_value NUMBER;
54 l_gpa_cp NUMBER;
55 l_gpa_quality_points NUMBER;
56
57 l_flag VARCHAR2(1);
58
59 /* Cursor to get the Student Program Attempt and Class Standing Details */
60 CURSOR c_spa IS
61 SELECT NVL(spat.class_standing_id,spa.igs_pr_class_std_id) igs_pr_class_std_id,
62 css.igs_pr_cs_schdl_id,
63 css.consider_changes
64 FROM IGS_EN_STDNT_PS_ATT spa,
65 IGS_PS_VER pv,
66 IGS_PS_TYPE pt,
67 IGS_PR_CS_SCHDL css,
68 IGS_EN_SPA_TERMS spat
69 WHERE spa.person_id = p_person_id
70 AND spa.course_cd = p_course_cd
71 AND spa.course_cd = pv.course_cd
72 AND spa.version_number = pv.version_number
73 AND spa.course_attempt_status IN
74 ('ENROLLED','COMPLETED','INTERMIT','INACTIVE')
75 AND pv.course_type = pt.course_type
76 AND pt.course_type = css.course_type (+)
77 AND TRUNC(css.start_dt (+) ) <= TRUNC(SYSDATE)
78 AND TRUNC(NVL(css.end_dt (+),SYSDATE) ) >= TRUNC(SYSDATE)
79 AND spat.person_id(+) = spa.person_id
80 AND spat.program_cd(+) = spa.course_cd
81 AND spat.term_cal_type(+) = p_load_cal_type
82 AND spat.term_sequence_number(+) = p_load_ci_sequence_number;
83
84 /* Cursor to get the class standing overriden value */
85
86 CURSOR C_cs_override(cp_igs_pr_class_std_id igs_pr_class_std.igs_pr_class_std_id%TYPE ) IS
87 SELECT class_standing
88 FROM igs_pr_class_std
89 WHERE igs_pr_class_std_id = cp_igs_pr_class_std_id;
90
91
92 /* Cursors to get the class standing calculation method */
93 CURSOR C_cs_min_cp(cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE ) IS
94 SELECT COUNT(*)
95 FROM igs_pr_css_class_std
96 WHERE igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
97 AND min_cp IS NOT NULL;
98 CURSOR C_cs_acad_yr(cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE ) IS
99 SELECT COUNT(*)
100 FROM igs_pr_css_class_std
101 WHERE igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
102 AND acad_year IS NOT NULL;
103
104 /* Cursor to get the academic year method details with load deatils */
105 CURSOR C_acad_year (cp_load_cal_type igs_ca_inst.cal_type%TYPE,
106 cp_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE ) IS
107 SELECT COUNT(*)
108 FROM igs_ca_type ct1,
109 igs_ca_inst ci1
110 WHERE ct1.s_cal_cat = 'ACADEMIC'
111 AND ct1.cal_type = ci1.cal_type
112 AND 0 < (SELECT COUNT(*)
113 FROM igs_ca_inst ci2a,
114 igs_ca_inst ci2b,
115 igs_ca_type ct2,
116 igs_ca_inst_rel cir2
117 WHERE ci2a.cal_type = cp_load_cal_type
118 AND ci2a.sequence_number = cp_load_ci_sequence_number
119 AND TRUNC(ci2a.end_dt) >= TRUNC(ci2b.end_dt)
120 AND cir2.sub_cal_type = ci2b.cal_type
121 AND cir2.sub_ci_sequence_number = ci2b.sequence_number
122 AND cir2.sup_cal_type = ci1.cal_type
123 AND cir2.sup_ci_sequence_number = ci1.sequence_number
124 AND ci2b.cal_type = ct2.cal_type
125 AND ct2.s_cal_cat = 'LOAD')
126 AND 0 < (SELECT COUNT(*)
127 FROM igs_en_su_attempt sua3,
128 igs_ca_inst_rel cir3
129 WHERE sua3.person_id = p_person_id
130 AND sua3.course_cd = p_course_cd
131 AND sua3.unit_attempt_status NOT IN
132 ('UNCONFIRM', 'DROPPED','DISCONTIN', 'WAITLISTED')
133 AND sua3.cal_type = cir3.sub_cal_type
134 AND sua3.ci_sequence_number = cir3.sub_ci_sequence_number
135 AND cir3.sup_cal_type = ci1.cal_type
136 AND cir3.sup_ci_sequence_number = ci1.sequence_number);
137
138 /* Cursor to Select Class Standing matching the Period Range */
139 CURSOR C_Get_Cs_for_acad_yr(cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE,
140 cp_acad_year igs_pr_css_class_std.acad_year%TYPE ) IS
141 SELECT ipcs.class_standing
142 FROM igs_pr_css_class_std ipccs,
143 igs_pr_class_std ipcs
144 WHERE ipccs.igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
145 AND ipccs.acad_year = cp_acad_year
146 AND ipccs.igs_pr_class_std_id = ipcs.igs_pr_class_std_id;
147
148 /* Cursor to Get the details of the most recent load calendar attempted */
149 CURSOR c_load_ci (cp_consider_changes IGS_PR_CS_SCHDL.consider_changes%TYPE) IS
150 SELECT ci.cal_type,
151 ci.sequence_number
152 FROM igs_ca_type ct,
153 igs_ca_inst ci
154 WHERE ct.s_cal_cat = 'LOAD'
155 AND ct.cal_type = ci.cal_type
156 AND ((cp_consider_changes = 'IMMEDIATELY'
157 AND ((TRUNC(ci.start_dt) <= TRUNC(NVL(p_effective_dt, SYSDATE))
158 AND p_load_cal_type IS NULL)
159 OR (ci.cal_type = p_load_cal_type
160 AND ci.sequence_number = p_load_ci_sequence_number
161 AND p_load_cal_type IS NOT NULL)))
162 OR (cp_consider_changes = 'BYPERIOD'
163 AND (TRUNC(ci.end_dt) <= TRUNC(NVL(p_effective_dt, SYSDATE))
164 OR (p_load_cal_type IS NOT NULL
165 AND ci.cal_type = p_load_cal_type
166 AND ci.sequence_number = p_load_ci_sequence_number
167 AND TRUNC(ci.end_dt) <= TRUNC(SYSDATE)))))
168 ORDER BY ci.end_dt DESC;
169
170 /* Cursor to Get the academic calendar superior to the load calendar provided */
171 CURSOR C_Get_acad_cal(cp_load_cal_type igs_ca_inst.cal_type%TYPE,
172 cp_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE ) IS
173 SELECT sup_cal_type,
174 sup_ci_sequence_number
175 FROM igs_ca_type ct,
176 igs_ca_inst_rel cir
177 WHERE cir.sub_cal_type = cp_load_cal_type
178 AND cir.sub_ci_sequence_number = cp_load_ci_sequence_number
179 AND cir.sup_cal_type = ct.cal_type
180 AND ct.s_cal_cat = 'ACADEMIC';
181
182 /*Cursor to SELECT Class Standing matching the CP RANGE */
183 CURSOR C_Get_cs_cp_range( cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE,
184 cp_cp igs_pr_css_class_std.min_cp%TYPE ) IS
185 SELECT ipcs.class_standing
186 FROM igs_pr_css_class_std ipccs,
187 igs_pr_class_std ipcs
188 WHERE ipccs.igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
189 AND ipccs.min_cp <= cp_cp
190 AND ipccs.max_cp >= cp_cp
191 AND ipccs.igs_pr_class_std_id = ipcs.igs_pr_class_std_id;
192
193 BEGIN
194
195 l_flag := IGS_PR_USER_CLASS_STD.customized_class_standing_flag;
196
197 /**
198 * Logging all params
199 */
200 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
201 fnd_log.string ( fnd_log.level_procedure,
202 'igs.plsql.igs_pr_get_class_std.get_class_standing' || '.begin',
203 'Params: p_person_id => '||p_person_id|| ';' ||
204 ' p_course_cd => '||p_course_cd|| ';' ||
205 ' p_predictive_ind => '||p_predictive_ind|| ';' ||
206 ' p_effective_dt => '||p_effective_dt|| ';' ||
207 ' p_load_cal_type => '||p_load_cal_type|| ';' ||
208 ' p_load_ci_sequence_number => '||p_load_ci_sequence_number|| ';' ||
209 ' p_init_msg_list => '||p_init_msg_list|| ';'
210 );
211 END IF;
212
213 /**
214 * Logging for validating whether or not class standing calculation is
215 * customized
216 */
217 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
218 fnd_log.string (fnd_log.level_statement,
219 'igs.plsql.igs_pr_get_class_std.get_class_standing',
220 'Customization Flag => '||l_flag
221 );
222 END IF;
223
224 IF (l_flag = 'Y')
225 THEN -- For Customized Class Standing
226 /* For customized class standing, calling the customized procedure*/
227 l_class_standing :=
228 IGS_PR_USER_CLASS_STD.get_customized_class_std
229 (p_person_id,
230 p_course_cd,
231 p_predictive_ind,
232 p_effective_dt,
233 p_load_cal_type,
234 p_load_ci_sequence_number,
235 p_init_msg_list
236 );
237 /**
238 * Customized Class Standing outcome
239 */
240 RETURN l_class_standing;
241
242 ELSE -- For Non Customized Class Standing
243 /* Validate Parameters*/
244 IF p_person_id IS NULL OR p_course_cd IS NULL THEN
245 RETURN NULL;
246 END IF;
247 /* both load calendar attributes should be specified or both should be null */
248 IF ( p_load_cal_type IS NOT NULL AND p_load_ci_sequence_number IS NULL ) OR
249 ( p_load_cal_type IS NULL AND p_load_ci_sequence_number IS NOT NULL ) OR
250 ( p_predictive_ind NOT IN ('Y','N' ) ) THEN
251 /**
252 * Logging Load Calendar Set-Up
253 *
254 */
255 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
256 fnd_log.string (fnd_log.level_statement,
257 'igs.plsql.igs_pr_get_class_std.get_class_standing',
258 'Load Calendar attributes not set up properly'
259 );
260 END IF;
261 RETURN NULL;
262 END IF;
263 /*if load calendar attributes and effective dates are passed then return null */
264 IF ( p_load_cal_type IS NOT NULL AND p_load_ci_sequence_number IS NOT NULL AND
265 p_effective_dt IS NOT NULL ) THEN
266 /**
267 * Logging -
268 *
269 */
270 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
271 fnd_log.string (fnd_log.level_statement,
272 'igs.plsql.igs_pr_get_class_std.get_class_standing',
273 'Both load Calendar attributes and effective dates are passed, hence exiting'
274 );
275 END IF;
276 RETURN NULL;
277 END IF;
278 /* Get Student Program Attempt and Program Type details*/
279 OPEN c_spa;
280 FETCH c_spa INTO l_igs_pr_class_std_id,
281 l_igs_pr_cs_schdl_id,
282 l_consider_changes;
283 /**
284 * Logging - l_igs_pr_class_std_id, l_igs_pr_cs_schdl_id, l_consider_changes
285 *
286 */
287 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
288 fnd_log.string (fnd_log.level_statement,
289 'igs.plsql.igs_pr_get_class_std.get_class_standing',
290 'Student Program Attempt and Program Type details - '||
291 'l_igs_pr_class_std_id => '||l_igs_pr_class_std_id||';'||
292 'l_igs_pr_cs_schdl_id => '||l_igs_pr_cs_schdl_id||';'||
293 'l_onsider_changes => '||l_consider_changes||';'
294 );
295 END IF;
296 CLOSE c_spa;
297 /* Is the Class Standing Override values set*/
298 IF l_igs_pr_class_std_id IS NOT NULL THEN
299 OPEN C_cs_override(l_igs_pr_class_std_id);
300 FETCH C_cs_override INTO l_class_standing_override;
301 CLOSE C_cs_override;
302 /**
303 * Logging - l_class_standing_override
304 *
305 */
306 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
307 fnd_log.string (fnd_log.level_statement,
308 'igs.plsql.igs_pr_get_class_std.get_class_standing',
309 'Class Standing Override values set => '||l_class_standing_override
310 );
311 END IF;
312 RETURN l_class_standing_override;
313 ELSIF l_igs_pr_cs_schdl_id IS NULL OR l_consider_changes IS NULL THEN
314 /* Return Null if the class standing setup is not done */
315 /**
316 * Logging -
317 *
318 */
319 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
320 fnd_log.string (fnd_log.level_statement,
321 'igs.plsql.igs_pr_get_class_std.get_class_standing',
322 'Class Standing setup is not done, hence exiting'
323 );
324 END IF;
325 RETURN NULL;
326 END IF;
327 /* Determine the correct Load Calendar */
328 OPEN c_load_ci (l_consider_changes);
329 FETCH c_load_ci
330 INTO l_load_cal_type,
331 l_load_ci_sequence_number;
332 IF c_load_ci%NOTFOUND THEN
333 /* If no Load Calendars are found assume student is a new enrolment */
334 l_acad_year := 1;
335 l_cp := 0;
336 END IF;
337 CLOSE c_load_ci;
338 /* Determine the Class Standing calculation method
339 A Program Type Class Standing Schedule cannot have child records
340 with both start and end cp values and academic year values set. */
341 OPEN C_cs_min_cp(l_igs_pr_cs_schdl_id);
342 FETCH C_cs_min_cp INTO l_min_cp_count;
343 CLOSE C_cs_min_cp;
344 OPEN C_cs_acad_yr(l_igs_pr_cs_schdl_id);
345 FETCH C_cs_acad_yr INTO l_acad_year_count;
346 CLOSE C_cs_acad_yr;
347 IF NVL(l_min_cp_count, 0) = 0 AND NVL(l_acad_year_count, 0) = 0 THEN
348 /**
349 * Logging -
350 *
351 */
352 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
353 fnd_log.string (fnd_log.level_statement,
354 'igs.plsql.igs_pr_get_class_std.get_class_standing',
355 'l_min_cp_count and l_acad_year_count both are NULL or 0, hence exiting'
356 );
357 END IF;
358 RETURN NULL;
359 END IF;
360 IF NVL(l_min_cp_count, 0) > 0 AND NVL(l_acad_year_count, 0) > 0 THEN
361 /**
362 * Logging -
363 *
364 */
365 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
366 fnd_log.string (fnd_log.level_statement,
367 'igs.plsql.igs_pr_get_class_std.get_class_standing',
368 'l_min_cp_count => '|| l_min_cp_count||';'||
369 'l_acad_year_count => '||l_acad_year_count||';'
370 );
371 END IF;
372 RETURN NULL;
373 END IF;
374 /* Academic Year Method*/
375 IF NVL(l_acad_year_count, 0) > 0 THEN
376 IF l_acad_year IS NULL THEN
377 OPEN c_acad_year(p_load_cal_type,p_load_ci_sequence_number);
378 FETCH c_acad_year INTO l_acad_year;
379 CLOSE c_acad_year;
380 END IF;
381 /* Select Class Standing matching the Period Range */
382 OPEN C_Get_Cs_for_acad_yr(l_igs_pr_cs_schdl_id, l_acad_year) ;
383 FETCH C_Get_Cs_for_acad_yr INTO l_class_standing;
384 CLOSE C_Get_Cs_for_acad_yr;
385 /**
386 * Logging - l_class_standing
387 *
388 */
389 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
390 fnd_log.string (fnd_log.level_statement,
391 'igs.plsql.igs_pr_get_class_std.get_class_standing',
392 'l_class_standing => '|| l_class_standing||';'
393 );
394 END IF;
395 RETURN l_class_standing;
396 ELSIF NVL(l_min_cp_count, 0) > 0 THEN /* CP Range */
397 IF l_cp IS NULL THEN
398 /* Get the earned cp total */
399 igs_pr_cp_gpa.get_cp_stats(
400 p_person_id,
401 p_course_cd,
402 NULL,
403 l_load_cal_type,
404 l_load_ci_sequence_number,
405 NULL,
406 'Y',
407 l_earned_cp,
408 l_attempted_cp,
409 p_init_msg_list,
410 l_return_status,
411 l_msg_count,
412 l_msg_data);
413 /**
414 * Logging - l_earned_cp
415 *
416 */
417 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
418 fnd_log.string (fnd_log.level_statement,
419 'igs.plsql.igs_pr_get_class_std.get_class_standing',
420 'l_earned_cp => '|| l_earned_cp||';'
421 );
422 END IF;
423 l_cp := NVL(l_earned_cp, 0);
424 END IF;
425 IF p_predictive_ind = 'Y' THEN
426 /* Get the academic calendar superior to the load calendar provided*/
427 OPEN C_Get_acad_cal( l_load_cal_type, l_load_ci_sequence_number);
428 FETCH C_Get_acad_cal INTO l_acad_cal_type,l_acad_ci_sequence_number;
429 CLOSE C_Get_acad_cal;
430 /* Get the enrolled cp total */
431
432 /* Removed call to Igs_En_Prc_Load.ENRP_CLC_EFTSU_TOTAL
433 for calculating the l_enrolled_cp value .
434 Jhanda
435 */
436 igs_pr_cp_gpa.get_all_stats_new(
437 p_person_id ,
438 p_course_cd ,
439 NULL ,
440 l_load_cal_type ,
441 l_load_ci_sequence_number ,
442 NULL ,
443 'Y' ,
444 l_earned_cp ,
445 l_attempted_cp ,
446 l_gpa_value ,
447 l_gpa_cp ,
448 l_gpa_quality_points ,
449 p_init_msg_list ,
450 l_return_status ,
451 l_msg_count ,
452 l_msg_data ,
453 'N' ,
454 l_enrolled_cp );
455
456 /**
457 * Logging - l_enrolled_cp
458 *
459 */
460 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
461 fnd_log.string (fnd_log.level_statement,
462 'igs.plsql.igs_pr_get_class_std.get_class_standing',
463 'l_enrolled_cp => '|| l_enrolled_cp||';'
464 );
465 END IF;
466 /* For predictive the cp total is earned cp plus enrolled cp */
467 l_cp := l_cp + NVL(l_enrolled_cp, 0);
468 END IF;
469 /* SELECT Class Standing matching the CP RANGE */
470 OPEN C_Get_cs_cp_range( l_igs_pr_cs_schdl_id, l_cp);
471 FETCH C_Get_cs_cp_range INTO l_class_standing;
472 CLOSE C_Get_cs_cp_range;
473 /**
474 * Logging - l_class_standing
475 *
476 */
477 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
478 fnd_log.string (fnd_log.level_statement,
479 'igs.plsql.igs_pr_get_class_std.get_class_standing',
480 'l_class_standing => '|| l_class_standing||';'
481 );
482 END IF;
483 RETURN l_class_standing;
484 END IF; -- For Non Customized Class Standing
485 END IF;
486 EXCEPTION
487 WHEN OTHERS THEN
488 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
489 Fnd_Message.SET_TOKEN('NAME', 'Igs_Pr_Get_Class_Std.get_class_standing');
490 Igs_Ge_Msg_Stack.ADD;
491 App_Exception.Raise_Exception;
492 END get_class_standing;
493
494 END Igs_Pr_Get_Class_Std;
[Home] [Help]