1 PACKAGE BODY IGS_PR_CLASS_RANK AS
2 /* $Header: IGSPR37B.pls 120.2 2006/01/18 23:08:00 swaghmar ship $ */
3 /****************************************************************************************************************
4 || Created By : DDEY
5 || Created On : 28-OCT-2002
6 || Purpose : This Job Rankes the students in a cohert or in an orginization
7 || This process can be called from the concurrent manager or the from "Class Rank Cohort".
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 ****************************************************************************************************************/
13
14
15 PROCEDURE ranking_process (
16 p_cohort_name IN VARCHAR2,
17 p_cal_type IN VARCHAR2,
18 p_ci_sequence_number IN NUMBER ,
19 p_count IN OUT NOCOPY NUMBER
20 );
21
22
23
24 PROCEDURE run_ranking_process (
25 errbuf OUT NOCOPY VARCHAR2, -- Standard Error Buffer Variable
26 retcode OUT NOCOPY NUMBER, -- Standard Concurrent Return code
27 p_cohort_name IN VARCHAR2, -- The Cohart or the Student Group Name
28 p_cal_period IN VARCHAR2, -- The Calendar Period ie the concation of term Calendar Type and the sequence Number
29 p_org_unit_cd IN VARCHAR2 -- Org Unit Code
30 ) IS
31 /****************************************************************************************************************
32 || Created By : DDEY
33 || Created On : 28-OCT-2002
34 || Purpose : This Job Rankes the students in a cohert or in an orginization
35 || This process can be called from the concurrent manager or the from "Class Rank Cohort".
36 || Known limitations, enhancements or remarks :
37 || Change History :
38 || Who When What
39 || anilk 31-Dec-2002 Added parameter cp_cohort_name to cursor cur_cohort_org. Bug#2719698
40 || swaghmar 16-Jan-2006 Bug# 4951054 - Added check for disabling UI's
41 || (reverse chronological order - newest change first)
42 ****************************************************************************************************************/
43
44 p_cal_type igs_ca_inst.cal_type%TYPE;
45 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
46 l_cumulative_ind VARCHAR2(1);
47 l_count1 NUMBER ;
48 l_count NUMBER;
49 l_rowid VARCHAR2(4000) DEFAULT NULL;
50 l_student_count NUMBER;
51
52
53 invalid_parameter_combination EXCEPTION;
54
55 -- This cursor fetches the Cohart Instances
56
57 CURSOR cur_inst_query (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE,
58 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
59 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
60 SELECT cohiv.*
61 FROM igs_pr_cohort_inst_v cohiv
62 WHERE cohiv.cohort_name = cp_cohort_name
63 AND cohiv.load_cal_type = cp_cal_type
64 AND cohiv.load_ci_sequence_number = cp_ci_sequence_number ;
65
66
67
68 -- This Cursor is used to get all the cohort name in the organization, whose instance are not present.
69
70 CURSOR cur_cohort_old (cp_org_unit_cd igs_pr_cohort_inst.cohort_name%TYPE,
71 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
72 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
73 SELECT cohr.cohort_name
74 FROM igs_pr_cohort cohr
75 WHERE cohr.org_unit_cd = cp_org_unit_cd
76
77 MINUS
78
79 SELECT cohi.cohort_name
80 FROM igs_pr_cohort coh,
81 igs_pr_cohort_inst cohi
82 WHERE coh.cohort_name = cohi.cohort_name
83 AND coh.org_unit_cd = cp_org_unit_cd
84 AND cohi.load_cal_type = cp_cal_type
85 AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
86
87
88 -- This Cursor is used to get all the cohort name in the organization, whose instance are not present.
89
90 CURSOR cur_cohort_inst_exist (cp_org_unit_cd igs_pr_cohort_inst.cohort_name%TYPE,
91 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
92 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
93 SELECT cohi.*
94 FROM igs_pr_cohort coh,
95 igs_pr_cohort_inst cohi
96 WHERE coh.cohort_name = cohi.cohort_name
97 AND coh.org_unit_cd = cp_org_unit_cd
98 AND cohi.load_cal_type = cp_cal_type
99 AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
100
101 -- This Cursor is used to get all the cohort instance when in the particular Orginanization
102
103 CURSOR cur_cohort_org_inst (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE,
104 cp_org_unit_cd igs_pr_cohort_inst.cohort_name%TYPE,
105 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
106 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
107 SELECT cohi.cohort_name
108 FROM igs_pr_cohort coh,
109 igs_pr_cohort_inst cohi
110 WHERE coh.cohort_name = cohi.cohort_name
111 AND coh.cohort_name = cp_cohort_name
112 AND coh.org_unit_cd = cp_org_unit_cd
113 AND cohi.load_cal_type = cp_cal_type
114 AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
115
116 -- Cursor Finds if cogert exists for the organization
117
118 CURSOR cur_cohort_org (
119 cp_org_unit_cd igs_pr_cohort.org_unit_cd%TYPE,
120 cp_cohort_name igs_pr_cohort.cohort_name%TYPE) IS
121 SELECT coh.cohort_name
122 FROM igs_pr_cohort coh
123 WHERE coh.org_unit_cd = cp_org_unit_cd
124 AND coh.cohort_name = cp_cohort_name;
125
126
127 inst_query_rec cur_inst_query%ROWTYPE;
128 cohort_old_rec cur_cohort_old%ROWTYPE ;
129 cohort_org_inst_rec cur_cohort_org_inst%ROWTYPE;
130 cohort_org_rec cur_cohort_org%ROWTYPE;
131 cohort_inst_exist_rec cur_cohort_inst_exist%ROWTYPE;
132
133 BEGIN
134 retcode:=0;
135
136 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
137
138 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cal_period_old : ' || p_cal_period);
139 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cohort_name_old : ' || p_cohort_name);
140 l_student_count := 1;
141
142 -- Determining the Calander Type from the Calender Period Passed
143 p_cal_type := RTRIM(LTRIM(SUBSTR(p_cal_period,1,10))) ;
144
145 -- Determining the Calander Sequence Number from the Calender Period Passed
146 p_ci_sequence_number := SUBSTR(p_cal_period,76,6) ;
147
148 --
149 -- Creating or Fetching the cohort instances
150 --
151
152 -- Condition # 1 when the parameters p_cohort_name,p_cal_type and p_ci_sequence_number are passed
153 IF (p_cohort_name IS NOT NULL AND p_cal_type IS NOT NULL AND p_ci_sequence_number IS NOT NULL AND p_org_unit_cd IS NULL) THEN
154 OPEN cur_inst_query (p_cohort_name,p_cal_type,p_ci_sequence_number);
155 FETCH cur_inst_query INTO inst_query_rec;
156 IF cur_inst_query%NOTFOUND THEN
157 CLOSE cur_inst_query;
158
159 l_rowid := NULL;
160
161 igs_pr_cohort_inst_pkg.INSERT_ROW(
162 x_rowid => l_rowid ,
163 x_cohort_name => p_cohort_name,
164 x_load_cal_type => p_cal_type ,
165 x_load_ci_sequence_number => p_ci_sequence_number ,
166 x_cohort_status => 'WORKING' ,
167 x_rank_status => 'WORKING' ,
168 x_run_date => SYSDATE
169 );
170
171 l_rowid := NULL;
172
173 END IF;
174
175 IF cur_inst_query%ISOPEN THEN
176 CLOSE cur_inst_query;
177 END IF;
178
179 -- Procedure 'ranking_process' is Called for both the cases
180 -- Case 1 : When the Cohort Instance already Exists in the System
181 -- Case 2 : When the Cohort Instance is newly created in the System
182 -- In both the cases the Cohort Name , Cal Type and the Sequence Number are same ie the parameter passed
183
184 ranking_process (
185 p_cohort_name => p_cohort_name,
186 p_cal_type => p_cal_type,
187 p_ci_sequence_number => p_ci_sequence_number,
188 p_count => l_student_count
189 );
190
191 -- Call to raise a Business Event
192 raise_clsrank_be_cr003 (p_cohort_name => p_cohort_name ,
193 p_cohort_instance => p_cal_period ,
194 p_run_date => SYSDATE ,
195 p_cohort_total_students => l_student_count ) ;
196
197 -- Condition # 2 when the parameters p_org_unit_cd,p_cal_type and p_ci_sequence_number are passed
198
199 ELSIF (p_org_unit_cd IS NOT NULL AND p_cal_type IS NOT NULL AND p_ci_sequence_number IS NOT NULL AND p_cohort_name IS NULL ) THEN
200 FOR cohort_inst_exist_rec IN cur_cohort_inst_exist(p_org_unit_cd,p_cal_type,p_ci_sequence_number) LOOP
201
202 -- The Ranking procedure fo all the Cohort Instances which already existes in the System for the Organization.
203
204 ranking_process (
205 p_cohort_name => cohort_inst_exist_rec.cohort_name,
206 p_cal_type => cohort_inst_exist_rec.load_cal_type,
207 p_ci_sequence_number => cohort_inst_exist_rec.load_ci_sequence_number ,
208 p_count => l_student_count
209 );
210
211 -- Call to raise a Business Event
212
213 raise_clsrank_be_cr003 (p_cohort_name => p_cohort_name ,
214 p_cohort_instance => p_cal_period ,
215 p_run_date => SYSDATE ,
216 p_cohort_total_students => l_student_count ) ;
217
218 END LOOP;
219
220
221 --
222 -- A cohort instance needs to be created for each of the cohorts attached to the org unit which do not have instances in the given calendar period.
223 --
224 FOR cohort_old_rec IN cur_cohort_old (p_org_unit_cd,p_cal_type,p_ci_sequence_number) LOOP
225
226 -- The Cohort Instances are created for the Cohort Name which are in the Organization but does not have the Instance
227
228 igs_pr_cohort_inst_pkg.insert_row(
229 x_rowid => l_rowid ,
230 x_cohort_name => cohort_old_rec.cohort_name,
231 x_load_cal_type => p_cal_type ,
232 x_load_ci_sequence_number => p_ci_sequence_number ,
233 x_cohort_status => 'WORKING' ,
234 x_rank_status => 'WORKING' ,
235 x_run_date => SYSDATE
236 );
237 l_rowid := NULL;
238
239 -- The Ranking procedure fo all the Cohort Instances which are newly created.
240
241 ranking_process (
242 p_cohort_name => cohort_old_rec.cohort_name,
243 p_cal_type => p_cal_type,
244 p_ci_sequence_number => p_ci_sequence_number ,
245 p_count => l_student_count
246 );
247
248 -- Call to raise a Business Event
249 raise_clsrank_be_cr003 (p_cohort_name => p_cohort_name ,
250 p_cohort_instance => p_cal_period ,
251 p_run_date => SYSDATE ,
252 p_cohort_total_students => l_student_count ) ;
253 END LOOP;
254
255
256
257 -- Condition # 3 when the parameters p_cohort_name,p_org_unit_cd,p_cal_type and p_ci_sequence_number are passed
258
259 ELSIF (p_cohort_name IS NOT NULL AND p_org_unit_cd IS NOT NULL AND p_cal_type IS NOT NULL AND p_ci_sequence_number IS NOT NULL) THEN
260
261 OPEN cur_cohort_org_inst(p_cohort_name,p_org_unit_cd,p_cal_type,p_ci_sequence_number);
262 FETCH cur_cohort_org_inst INTO cohort_org_inst_rec;
263
264 IF cur_cohort_org_inst%FOUND THEN
265
266 -- The Ranking procedure fo all the Cohort Instances which already existes in the System for the corresponding cohort name,org unit cd,cal type and ci_sequence_number .
267
268 ranking_process (
269 p_cohort_name => p_cohort_name,
270 p_cal_type => p_cal_type,
271 p_ci_sequence_number => p_ci_sequence_number,
272 p_count => l_student_count
273 );
274
275 -- Call to raise a Business Event
276
277 raise_clsrank_be_cr003 (p_cohort_name => p_cohort_name ,
278 p_cohort_instance => p_cal_period ,
279 p_run_date => SYSDATE ,
280 p_cohort_total_students => l_student_count ) ;
281
282
283 ELSE
284
285 l_count := 0;
286
287 -- If no cohort instance exists for the combination of the parameters p_cohort_name,p_org_unit_cd,p_cal_type and p_ci_sequence_number
288 -- are found, check if the org is attached to the cohort
289
290 FOR cohort_org_rec IN cur_cohort_org (p_org_unit_cd, p_cohort_name) LOOP
291
292 l_count := l_count + 1;
293
294 -- Instance for this cohort is created and then the ranking process for this new instance is run.
295
296 igs_pr_cohort_inst_pkg.insert_row(
297 x_rowid => l_rowid ,
298 x_cohort_name => cohort_org_rec.cohort_name,
299 x_load_cal_type => p_cal_type ,
300 x_load_ci_sequence_number => p_ci_sequence_number ,
301 x_cohort_status => 'WORKING' ,
302 x_rank_status => 'WORKING' ,
303 x_run_date => SYSDATE
304 );
305 l_rowid := NULL;
306
307 -- Ranking process for this new instance created .
308
309 ranking_process (
310 p_cohort_name => cohort_org_rec.cohort_name,
311 p_cal_type => p_cal_type,
312 p_ci_sequence_number => p_ci_sequence_number,
313 p_count => l_student_count
314 );
315
316 -- Call to raise a Business Event
317
318
319 raise_clsrank_be_cr003 (p_cohort_name => p_cohort_name ,
320 p_cohort_instance => p_cal_period ,
321 p_run_date => SYSDATE ,
322 p_cohort_total_students => l_student_count ) ;
323
324
325 END LOOP;
326
327 END IF;
328
329 -- If the org unit is not attached to the cohort, message 'IGS_PR_RNK_NO_POP' is printed in the log file
330
331 IF l_count = 0 THEN
332
333 FND_MESSAGE.SET_NAME('IGS','IGS_PR_RNK_NO_POP');
334 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
335
336 END IF;
337
338
339 -- Condition # 4 when both the parameters p_cohort_name and p_org_unit_cd are not passed
340
341 ELSIF (p_cohort_name IS NULL AND p_org_unit_cd IS NULL ) THEN
342
343 -- When both the Cohort Name and the Org Unit code is passed as NULL, an error message would be raised and the
344 -- process would error out.
345
346
347 RAISE invalid_parameter_combination;
348
349 END IF;
350
351 FND_MESSAGE.SET_NAME('IGS','IGS_PR_RNK_COMP');
352 FND_MESSAGE.SET_TOKEN('RANKCOUNT',l_student_count);
353 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
354 EXCEPTION
355 WHEN invalid_parameter_combination THEN
356 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Error Message :' || SQLERRM);
357 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
358 retcode := 2;
359 errbuf := fnd_message.get_string('IGS','IGS_PR_RNK_INV_PRM');
360 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
361 WHEN OTHERS THEN
362 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Error Message :' || SQLERRM);
363 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
364 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_CLASS_RANK.RUN_RANKING_PROCESS');
365 retcode := 2;
366 errbuf := fnd_message.get;
367 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
368 END run_ranking_process ;
369
370 PROCEDURE ranking_process (
371 p_cohort_name IN VARCHAR2,
372 p_cal_type IN VARCHAR2,
373 p_ci_sequence_number IN NUMBER,
374 p_count IN OUT NOCOPY NUMBER
375 ) IS
376
377 /****************************************************************************************************************
378 || Created By : DDEY
379 || Created On : 28-OCT-2002
380 || Purpose : This Procedure is called from the procedure run_making_process . This procedure does the ranking
381 || of the students in particular cohart instance
382 || Known limitations, enhancements or remarks :
383 || Change History :
384 || Who When What
385 || (reverse chronological order - newest change first)
386 ****************************************************************************************************************/
387
388 l_new_count NUMBER ;
389 l_old_count NUMBER ;
390 l_acad_cal VARCHAR2(4000) DEFAULT NULL ; -- This stores the concatinated value of Academic Calander
391 l_cumulative_ind VARCHAR2(1);
392 l_rowid VARCHAR2(2000);
393 l_old_flag VARCHAR2(1) DEFAULT 'Y';
394 p_id number;
395
396 -- This Cursor fetches the Rank status for the corrosponding Cohort Name
397
398 CURSOR cur_rank_status (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE,
399 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
400 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE ) IS
401 SELECT *
402 FROM igs_pr_cohort_inst_v
403 WHERE cohort_name = cp_cohort_name
404 AND load_cal_type = cp_cal_type
405 AND load_ci_sequence_number = cp_ci_sequence_number;
406
407
408 -- This Cursor fetches the Stat type,TimeFrame for the corrosponding Cohort Name
409
410 CURSOR cur_stat_type (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE) IS
411 SELECT *
412 FROM igs_pr_cohort
413 WHERE cohort_name = cp_cohort_name ;
414
415 -- The cursor is used when the Cohort Status is FROZEN and the rank status is Not Final. This cursor is used when,
416 -- the Dense Rank Indicator is set as 'N' in the table igs_pr_cohort
417
418 TYPE cur_frozen_rank IS REF CURSOR;
419
420 -- The cursor is used when the Cohort Status is FROZEN and the rank status is Not Final. This cursor is used when,
421 -- the Dense Rank Indicator is set as 'Y' in the table igs_pr_cohort
422
423 TYPE cur_frozen_denserank IS REF CURSOR;
424
425
426 -- Cursor to determine the Calander Category for a particular calander type
427
428 CURSOR cur_cal_cat (cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE ) IS
429 SELECT s_cal_cat
430 FROM igs_ca_type
431 WHERE cal_type = cp_cal_type ;
432
433 -- Cursor to determine the Academic Calander for the corrosponding load calander
434
435 CURSOR cur_acad_cal (cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
436 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
437 SELECT sup_cal_type, sup_ci_sequence_number
438 FROM igs_ca_inst_rel
439 WHERE
440 sub_cal_type = cp_cal_type
441 AND sub_ci_sequence_number = cp_ci_sequence_number
442 AND sup_cal_type IN (SELECT CAL_TYPE FROM IGS_CA_TYPE WHERE S_CAL_CAT = 'ACADEMIC') ;
443
444 --
445 -- Cursor used to rank the student based on the Rule. This is done when the rank status is not FINAL and the cohort status is WORKING
446 -- This cursor is used when,the Dense Rank Indicator is set as 'N' in the table igs_pr_cohort
447 --
448
449 TYPE cur_student_ranked_query IS REF CURSOR;
450
451
452 --
453 -- Cursor used to rank the student based on the Rule. This is done when the rank status is not FINAL and the cohort status is WORKING
454 -- This cursor is used when,the Dense Rank Indicator is set as 'Y' in the table igs_pr_cohort
455 --
456
457 TYPE cur_student_denseranked_query IS REF CURSOR;
458
459
460 -- Cursor get the old data when the cohort status is 'WORKING'
461
462 CURSOR cur_old_rank (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE,
463 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
464 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
465 SELECT cohi.*
466 FROM igs_pr_cohort_inst_rank_v cohi
467 WHERE cohi.cohort_name = cp_cohort_name
468 AND cohi.load_cal_type = cp_cal_type
469 AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
470
471
472 -- Cursor get the cohort Institution Name Corrosponding to the cp_cohort_name,cp_cal_type,cp_ci_sequence_number
473
474 CURSOR cur_cohort_inst_person (cp_cohort_name igs_pr_cohort_inst.cohort_name%TYPE,
475 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
476 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE,
477 cp_person_id igs_en_sca_v.person_id%TYPE,
478 cp_course_cd igs_en_sca_v.course_cd%TYPE) IS
479 SELECT cohi.*
480 FROM igs_pr_cohort_inst_rank_v cohi
481 WHERE cohi.cohort_name = cp_cohort_name
482 AND cohi.load_cal_type = cp_cal_type
483 AND cohi.load_ci_sequence_number = cp_ci_sequence_number
484 AND cohi.person_id = cp_person_id
485 AND cohi.course_cd = cp_course_cd;
486
487
488
489 rank_status_rec cur_rank_status%ROWTYPE;
490 stat_type_rec cur_stat_type%ROWTYPE;
491 frozen_rank_type_rec cur_frozen_rank;
492 frozen_denserank_type_rec cur_frozen_denserank;
493 cohort_inst_person_rec cur_cohort_inst_person%ROWTYPE;
494 student_denrank_query_type_rec cur_student_denseranked_query;
495 student_ranked_query_type_rec cur_student_ranked_query;
496 cal_cat_rec cur_cal_cat%ROWTYPE;
497
498
499 TYPE frozen_rank_rec_type IS RECORD (
500
501 row_id VARCHAR2(2000),
502 cohort_name VARCHAR2(30),
503 load_cal_type VARCHAR2(10),
504 load_ci_sequence_number NUMBER(6),
505 person_id NUMBER(15),
506 person_number VARCHAR2(30),
507 person_name VARCHAR2(360),
508 course_cd VARCHAR2(6),
509 course_title VARCHAR2(90),
510 as_of_rank_gpa NUMBER,
511 cohort_rank NUMBER(15),
512 cohort_override_rank NUMBER(15),
513 comments VARCHAR2(240),
514 created_by NUMBER(15),
515 creation_date DATE,
516 last_updated_by NUMBER(15),
517 last_update_date DATE,
518 last_update_login NUMBER(15),
519 request_id NUMBER(15),
520 program_application_id NUMBER(15),
521 program_id NUMBER(15),
522 program_update_date DATE,
523 cum_gpa NUMBER,
524 new_rank NUMBER
525
526 ) ;
527
528 frozen_rank_rec frozen_rank_rec_type ;
529
530 TYPE frozen_denserank_rec_type IS RECORD (
531
532 row_id VARCHAR2(2000),
533 cohort_name VARCHAR2(30),
534 load_cal_type VARCHAR2(10),
535 load_ci_sequence_number NUMBER(6),
536 person_id NUMBER(15),
537 person_number VARCHAR2(30),
538 person_name VARCHAR2(360),
539 course_cd VARCHAR2(6),
540 course_title VARCHAR2(90),
541 as_of_rank_gpa NUMBER,
542 cohort_rank NUMBER(15),
543 cohort_override_rank NUMBER(15),
544 comments VARCHAR2(240),
545 created_by NUMBER(15),
546 creation_date DATE,
547 last_updated_by NUMBER(15),
548 last_update_date DATE,
549 last_update_login NUMBER(15),
550 request_id NUMBER(15),
551 program_application_id NUMBER(15),
552 program_id NUMBER(15),
553 program_update_date DATE,
554 cum_gpa NUMBER,
555 new_rank NUMBER
556
557 ) ;
558
559 frozen_denserank_rec frozen_denserank_rec_type ;
560
561
562 TYPE student_ranked_query_type IS RECORD (
563
564 person_id NUMBER(15),
565 course_cd VARCHAR2(6),
566 cum_gpa NUMBER,
567 new_rank NUMBER
568
569 ) ;
570
571 student_ranked_query_rec student_ranked_query_type ;
572
573
574 TYPE student_denseranked_query_type IS RECORD (
575
576 person_id NUMBER(15),
577 course_cd VARCHAR2(6),
578 cum_gpa NUMBER,
579 new_rank NUMBER
580 ) ;
581
582 student_denseranked_query_rec student_denseranked_query_type;
583 BEGIN
584
585
586 --
587 -- The Rank Status, Cohort Status for the Cohort Instance is determined
588 --
589
590 OPEN cur_rank_status(p_cohort_name,p_cal_type,p_ci_sequence_number);
591 FETCH cur_rank_status INTO rank_status_rec;
592 CLOSE cur_rank_status ;
593
594
595 IF rank_status_rec.rank_status = 'FINAL' THEN -- 1
596
597 --
598 -- If the Rank Status is FINAL then no ranking is done and message 'IGS_PR_RNK_FINAL' should be displayed on the log file
599 --
600 FND_MESSAGE.SET_NAME('IGS','IGS_PR_RNK_FINAL');
601 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
602
603 -- If the ranking status is not FINAL ie it is WORKING then the following needa to be done
604
605 ELSE --1
606
607 -- Determining the Stat Type , Time frame and the Dense Rank Indicator
608
609 OPEN cur_stat_type(p_cohort_name);
610 FETCH cur_stat_type INTO stat_type_rec ;
611 CLOSE cur_stat_type ;
612
613
614 --
615 -- IF timeframe for the corrosponding Cohort Name is 'CUMULATIVE', 'Y' is assign to l_cumulative_ind, else if timeframe is TERM, 'N' is assigned to l_cumulative_ind
616 --
617
618 IF stat_type_rec.timeframe = 'CUMULATIVE' THEN --3
619
620 l_cumulative_ind := 'Y' ;
621
622 ELSIF stat_type_rec.timeframe = 'TERM' THEN --3
623
624 l_cumulative_ind := 'N' ;
625
626 END IF; --3
627
628 -- Ranking for the student are done for the students where the rank status is working and the cohort status is 'FROZEN'
629 -- This means that the student population is frozen. The student population is chosen from IGS_PR_COHORT_INST_RANK_V. This is the population that needs to be ranked.
630
631 IF rank_status_rec.cohort_status = 'FROZEN' THEN --2
632 p_count := 0;
633
634 IF stat_type_rec.dense_rank_ind = 'N' THEN --4
635
636 --
637 -- Ranking the student in the Cohert in a particular Calender Instance when the Rank Status is Not final and the Cohort Status Is Froozen.
638 -- The Dense Rank Indicator should be set as 'N' in the table igs_pr_cohort
639 --(p_cohort_name,p_cal_type,p_ci_sequence_number,stat_type_rec.stat_type,l_cumulative_ind)
640
641 OPEN frozen_rank_type_rec FOR
642 'SELECT res.*,
643 RANK() OVER (ORDER BY res.cum_gpa DESC) new_rank
644 FROM (SELECT cohiv.*,
645 igs_pr_class_rank.get_cum_gpa (
646 cohiv.person_id,
647 cohiv.course_cd,
648 cohiv.cohort_name,
649 cohiv.load_cal_type,
650 cohiv.load_ci_sequence_number,
651 ''' || stat_type_rec.stat_type || ''',
652 '''|| l_cumulative_ind || '''
653 ) cum_gpa
654 FROM igs_pr_cohort_inst_rank_v cohiv
655 WHERE cohiv.cohort_name = ''' || p_cohort_name || '''
656 AND cohiv.load_cal_type = ''' || p_cal_type || '''
657 AND cohiv.load_ci_sequence_number = ' || p_ci_sequence_number || ') res' ;
658
659 LOOP -- Loop 1
660
661 FETCH frozen_rank_type_rec INTO frozen_rank_rec;
662 EXIT WHEN frozen_rank_type_rec%NOTFOUND;
663
664 p_count := p_count + 1;
665
666
667 -- This FOR loop is to determine the studnet rank already present in the system for the specific Cohort Instance.
668 -- These records need to updated with the recent Ranking anf the GPA.
669
670
671 FOR cohort_inst_person_rec IN cur_cohort_inst_person(p_cohort_name,p_cal_type,p_ci_sequence_number,frozen_rank_rec.person_id,frozen_rank_rec.course_cd) LOOP -- Loop2
672
673 --
674 -- Updating the Cohert Instance rank table with the recent ranking and the GPA value as of now. Rest of the values are retained.
675 --
676 igs_pr_cohinst_rank_pkg.update_row (
677 x_rowid => cohort_inst_person_rec.row_id ,
678 x_cohort_name => cohort_inst_person_rec.cohort_name,
679 x_load_cal_type => cohort_inst_person_rec.load_cal_type ,
680 x_load_ci_sequence_number => cohort_inst_person_rec.load_ci_sequence_number ,
681 x_person_id => cohort_inst_person_rec.person_id ,
682 x_course_cd => cohort_inst_person_rec.course_cd ,
683 x_as_of_rank_gpa => frozen_rank_rec.cum_gpa ,
684 x_cohort_rank => frozen_rank_rec.new_rank ,
685 x_cohort_override_rank => cohort_inst_person_rec.cohort_override_rank ,
686 x_comments => cohort_inst_person_rec.comments
687 );
688
689 END LOOP; -- Loop2
690 END LOOP; --Loop 1
691
692
693
694 ELSIF stat_type_rec.dense_rank_ind = 'Y' THEN --4
695
696 --
697 -- Ranking the student in the Cohert in a particular Calender Instance when the Rank Status is Not final and the Cohort Status Is Froozen
698 -- The Dense Rank Indicator should be set as 'Y' in the table igs_pr_cohort
699 --
700
701 OPEN frozen_denserank_type_rec FOR
702 'SELECT res.*,
703 DENSE_RANK() OVER (ORDER BY res.cum_gpa DESC) new_rank
704 FROM (SELECT cohiv.*,
705 igs_pr_class_rank.get_cum_gpa (
706 cohiv.person_id,
707 cohiv.course_cd,
708 cohiv.cohort_name,
709 cohiv.load_cal_type,
710 cohiv.load_ci_sequence_number,
711 ''' || stat_type_rec.stat_type || ''',
712 '''|| l_cumulative_ind || '''
713 ) cum_gpa
714 FROM igs_pr_cohort_inst_rank_v cohiv
715 WHERE cohiv.cohort_name = ''' || p_cohort_name || '''
716 AND cohiv.load_cal_type = ''' || p_cal_type || '''
717 AND cohiv.load_ci_sequence_number = ' || p_ci_sequence_number || ') res' ;
718
719
720
721
722 LOOP -- Loop 1
723
724 FETCH frozen_denserank_type_rec INTO frozen_denserank_rec;
725 EXIT WHEN frozen_denserank_type_rec%NOTFOUND;
726
727 p_count := p_count + 1;
728
729
730 -- This FOR loop is to determine the studnet rank already present in the system for the specific Cohort Instance.
731 -- These records need to updated with the recent Ranking anf the GPA.
732
733 FOR cohort_inst_person_rec IN cur_cohort_inst_person(p_cohort_name,p_cal_type,p_ci_sequence_number,frozen_denserank_rec.person_id,frozen_denserank_rec.course_cd) LOOP
734
735
736 --
737 -- Updating the Cohert Instance rank table with the recent ranking and the GPA value as of now. Rest of the values are retained.
738 --
739
740 igs_pr_cohinst_rank_pkg.update_row (
741 x_rowid => cohort_inst_person_rec.row_id ,
742 x_cohort_name => cohort_inst_person_rec.cohort_name,
743 x_load_cal_type => cohort_inst_person_rec.load_cal_type ,
744 x_load_ci_sequence_number => cohort_inst_person_rec.load_ci_sequence_number ,
745 x_person_id => cohort_inst_person_rec.person_id ,
746 x_course_cd => cohort_inst_person_rec.course_cd ,
747 x_as_of_rank_gpa => frozen_denserank_rec.cum_gpa ,
748 x_cohort_rank => frozen_denserank_rec.new_rank ,
749 x_cohort_override_rank => cohort_inst_person_rec.cohort_override_rank ,
750 x_comments => cohort_inst_person_rec.comments
751 );
752
753 END LOOP;
754
755 END LOOP;
756
757 END IF; -- 4
758
759 --
760 -- Updating the Cohert Instance table with the rundate as the System Date. Rest of the values are retained.
761 --
762
763 igs_pr_cohort_inst_pkg.update_row(
764 x_rowid => rank_status_rec.row_id,
765 x_cohort_name => rank_status_rec.cohort_name,
766 x_load_cal_type => rank_status_rec.load_cal_type,
767 x_load_ci_sequence_number => rank_status_rec.load_ci_sequence_number,
768 x_cohort_status => rank_status_rec.cohort_status,
769 x_rank_status => rank_status_rec.rank_status,
770 x_run_date => SYSDATE
771 );
772
773
774
775
776 -- Ranking for the student are done for the students where the rank status is working and the cohort status is 'WORKING'
777 -- Cohort status is 'WORKING' means that the number of students in the Cohort Instance is not fixed, the number of students can increase or decrease accourdingly.
778
779 ELSIF rank_status_rec.cohort_Status = 'WORKING' THEN -- 2
780
781
782 p_count := 0;
783
784
785 OPEN cur_cal_cat(p_cal_type);
786 FETCH cur_cal_cat INTO cal_cat_rec ;
787 CLOSE cur_cal_cat ;
788
789
790 -- Tha Calender Category passed is 'LOAD'
791
792 IF cal_cat_rec.s_cal_cat = 'LOAD' THEN
793
794 -- Finding out all the Academic Calanders for the corresponding Term Calanders
795 -- Concating all of them and putting them in the format ( 'ACAD-1','ACAD-2')
796
797 FOR acad_cal_rec IN cur_acad_cal (p_cal_type,p_ci_sequence_number) LOOP
798
799 IF l_acad_cal IS NULL THEN
800
801 l_acad_cal := '(' || '''' || acad_cal_rec.sup_cal_type || '''' ;
802
803 ELSE
804
805 l_acad_cal := l_acad_cal || ',' || '''' || acad_cal_rec.sup_cal_type ||'''' ;
806
807 END IF;
808
809 END LOOP;
810
811 --
812 -- L_ACAD_CAL contains all the concatenated values for all the Academic Calanders in the format ( 'ACAD-1','ACAD-2')
813 --
814 IF l_acad_cal IS NOT NULL THEN
815 l_acad_cal := l_acad_cal || ')' ;
816 ELSE
817 l_acad_cal := '('''')';
818 END IF;
819
820 -- Tha Calender Category passed is 'ACADEMIC'
821
822 ELSE
823
824 l_acad_cal := '(' || '''' || p_cal_type || '''' || ')' ;
825
826 END IF;
827 IF stat_type_rec.dense_rank_ind = 'N' THEN -- The Dense Rank Indicator should be set as 'N' in the table igs_pr_cohort
828 l_old_count := 0;
829
830 -- The existing list of students whose ranking is done for the Cohort Instance
831
832 FOR old_rank_rec IN cur_old_rank(p_cohort_name,p_cal_type,p_ci_sequence_number) LOOP
833 l_old_count := l_old_count + 1;
834 l_old_population_table_rec(l_old_count).p_rowid := old_rank_rec.row_id ;
835 l_old_population_table_rec(l_old_count).p_person_id := old_rank_rec.person_id ;
836 l_old_population_table_rec(l_old_count).p_course_cd := old_rank_rec.course_cd ;
837 l_old_population_table_rec(l_old_count).p_cohort_name := old_rank_rec.cohort_name ;
838 l_old_population_table_rec(l_old_count).p_load_cal_type := old_rank_rec.load_cal_type ;
839 l_old_population_table_rec(l_old_count).p_load_ci_sequence_number := old_rank_rec.load_ci_sequence_number ;
840 l_old_population_table_rec(l_old_count).p_as_of_rank_gpa := old_rank_rec.as_of_rank_gpa ;
841 l_old_population_table_rec(l_old_count).p_cohort_rank := old_rank_rec.cohort_rank ;
842 l_old_population_table_rec(l_old_count).p_cohort_override_rank := old_rank_rec.cohort_override_rank ;
843 l_old_population_table_rec(l_old_count).p_comments := old_rank_rec.comments ;
844 l_old_population_table_rec(l_old_count).p_deletion_indicator := 'Y' ;
845
846
847 END LOOP;
848
849 l_new_count := 0;
850
851 -- The output is a record group consisting of the all the students fitting the cohort rule and correspondingly ranked.
852 -- Store this in a PL/SQL table called table l_new_population_table
853
854
855 OPEN student_ranked_query_type_rec FOR
856 ' SELECT res.* , RANK () OVER (order by res.cum_gpa desc) AS new_rank
857 FROM
858 (SELECT person_id, course_cd, igs_pr_class_rank.get_cum_gpa ( sca.person_id,sca.course_cd,''' || p_cohort_name || ''',
859 ''' || p_cal_type || ''',' || p_ci_sequence_number || ',
860 ''' || stat_type_rec.stat_type ||''',
861 ''' || l_cumulative_ind || ''' ) cum_gpa
862 FROM igs_en_sca_v sca
863 WHERE
864 sca.cal_type IN ' || l_acad_cal || ' AND
865 (sca.person_id, sca.course_cd) IN
866 (
867 SELECT sca.person_id, sca.course_cd
868 FROM igs_en_su_attempt sua, igs_en_sca_v sca
869 WHERE sua.person_id = sca.person_id
870 AND sua.course_cd = sca.course_cd
871 AND unit_attempt_status = ''COMPLETED''
872 AND ( sua.cal_type , sua.ci_sequence_number ) IN
873 (SELECT teach_cal_type, teach_ci_sequence_number
874 FROM igs_ca_load_to_teach_v
875 WHERE load_cal_type= ''' || p_cal_type || '''
876 AND load_ci_sequence_number = ' || p_ci_sequence_number || '
877 )
878 )
879 AND
880 igs_pr_class_rank.rulp_val_senna_res (
881 sca.person_id,
882 sca.course_cd,
883 sca.version_number,
884 NULL,
885 NULL,
886 ''' || p_cal_type || ''',
887 ' || p_ci_sequence_number || ','
888 || stat_type_rec.rule_sequence_number || ') = ''true'' ) res';
889
890
891
892
893 LOOP
894
895 FETCH student_ranked_query_type_rec INTO student_ranked_query_rec;
896
897 EXIT WHEN student_ranked_query_type_rec%NOTFOUND;
898
899 p_count := p_count + 1;
900
901 l_old_flag := 'Y';
902
903
904 -- FOR student_ranked_query_rec IN cur_student_ranked_query (p_cohort_name,p_cal_type,p_ci_sequence_number,stat_type_rec.stat_type,l_cumulative_ind,l_acad_cal) LOOP
905
906 l_new_count := l_new_count + 1;
907
908 l_new_population_table_rec(l_new_count).p_person_id := student_ranked_query_rec.person_id ;
909 l_new_population_table_rec(l_new_count).p_course_cd := student_ranked_query_rec.course_cd ;
910 l_new_population_table_rec(l_new_count).p_cohort_name := p_cohort_name ;
911 l_new_population_table_rec(l_new_count).p_load_cal_type := p_cal_type ;
912 l_new_population_table_rec(l_new_count).p_load_ci_sequence_number := p_ci_sequence_number ;
913 l_new_population_table_rec(l_new_count).p_as_of_rank_gpa := student_ranked_query_rec.cum_gpa ;
914 l_new_population_table_rec(l_new_count).p_cohort_rank := student_ranked_query_rec.new_rank ;
915 l_new_population_table_rec(l_new_count).p_cohort_override_rank := NULL ;
916 l_new_population_table_rec(l_new_count).p_comments := NULL ;
917 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'Y' ;
918
919 FOR i IN 1..l_old_count LOOP
920
921
922 IF( l_new_population_table_rec(l_new_count).p_person_id = l_old_population_table_rec(i).p_person_id AND
923 l_new_population_table_rec(l_new_count).p_course_cd = l_old_population_table_rec(i).p_course_cd AND
924 l_new_population_table_rec(l_new_count).p_cohort_name = l_old_population_table_rec(i).p_cohort_name AND
925 l_new_population_table_rec(l_new_count).p_load_cal_type = l_old_population_table_rec(i).p_load_cal_type AND
926 l_new_population_table_rec(l_new_count).p_load_ci_sequence_number = l_old_population_table_rec(i).p_load_ci_sequence_number ) THEN
927
928 -- If the students existinf in the old cohort instance list and the new cohort instance then the old rank and the GPA for the student is updated in the
929 -- Cohort Instance Rank table.
930
931 l_old_flag := 'N';
932
933
934 igs_pr_cohinst_rank_pkg.update_row (
935 x_rowid => l_old_population_table_rec(i).p_rowid ,
936 x_cohort_name => l_old_population_table_rec(i).p_cohort_name,
937 x_load_cal_type => l_old_population_table_rec(i).p_load_cal_type ,
938 x_load_ci_sequence_number => l_old_population_table_rec(i).p_load_ci_sequence_number ,
939 x_person_id => l_old_population_table_rec(i).p_person_id ,
940 x_course_cd => l_old_population_table_rec(i).p_course_cd ,
941 x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
942 x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
943 x_cohort_override_rank => l_old_population_table_rec(i).p_cohort_override_rank ,
944 x_comments => l_old_population_table_rec(i).p_comments
945 );
946
947
948 -- Marking the Records in the New List and the Old list, for the records which already exist in the
949 -- Cohort Instance Rank table as of this step. This indicator would be used later, when the
950 -- records for the Student Cohort Instance rank has to be deleted from the table Cohort Instance Rank,
951 -- which are not avaliable in the new list.
952
953 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'N' ;
954 l_old_population_table_rec(i).p_deletion_indicator := 'N' ;
955
956
957 END IF;
958
959 END LOOP;
960 -- Records existing in the new list and not in the old list . Those records are inserted in the Cohort Instance Rank table
961
962
963 IF l_old_flag = 'Y' THEN
964
965 igs_pr_cohinst_rank_pkg.insert_row (
966 x_rowid => l_rowid ,
967 x_cohort_name => l_new_population_table_rec(l_new_count).p_cohort_name ,
968 x_load_cal_type => l_new_population_table_rec(l_new_count).p_load_cal_type ,
969 x_load_ci_sequence_number => l_new_population_table_rec(l_new_count).p_load_ci_sequence_number,
970 x_person_id => l_new_population_table_rec(l_new_count).p_person_id ,
971 x_course_cd => l_new_population_table_rec(l_new_count).p_course_cd ,
972 x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
973 x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
974 x_cohort_override_rank => l_new_population_table_rec(l_new_count).p_cohort_override_rank ,
975 x_comments => l_new_population_table_rec(l_new_count).p_comments
976 );
977
978 l_old_flag := 'N' ;
979
980 END IF;
981
982 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'N' ;
983 l_rowid := NULL ;
984 END LOOP;
985
986 -- If Cohort status is "WORKING" and the rule returned no student population to be ranked,
987 -- then write the message IGS_PR_RNK_NO_POP "No population returned" to the log file.
988
989 IF l_new_count = 0 THEN
990
991 FND_MESSAGE.SET_NAME('IGS','IGS_PR_RNK_NO_POP');
992 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
993
994 END IF;
995
996 -- Deleting Records which from the Cohort Instance Rank table, which do not fit the rule criterian any more.
997
998 FOR j IN 1..l_old_count LOOP
999
1000 IF l_old_population_table_rec(j).p_deletion_indicator = 'Y' THEN
1001
1002 igs_pr_cohinst_rank_pkg.delete_row (
1003 x_rowid => l_old_population_table_rec(j).p_rowid
1004 );
1005
1006 END IF;
1007
1008 END LOOP;
1009
1010 ELSIF stat_type_rec.dense_rank_ind = 'Y' THEN -- The Dense Rank Indicator should be set as 'Y' in the table igs_pr_cohort
1011
1012 l_old_count := 0;
1013
1014 -- The existing list of students whose ranking is done for the Cohort Instance
1015
1016 FOR old_rank_rec IN cur_old_rank (p_cohort_name,p_cal_type,p_ci_sequence_number) LOOP
1017
1018 l_old_count := l_old_count + 1;
1019
1020 l_old_population_table_rec(l_old_count).p_rowid := old_rank_rec.row_id ;
1021 l_old_population_table_rec(l_old_count).p_person_id := old_rank_rec.person_id ;
1022 l_old_population_table_rec(l_old_count).p_course_cd := old_rank_rec.course_cd ;
1023 l_old_population_table_rec(l_old_count).p_cohort_name := old_rank_rec.cohort_name ;
1024 l_old_population_table_rec(l_old_count).p_load_cal_type := old_rank_rec.load_cal_type ;
1025 l_old_population_table_rec(l_old_count).p_load_ci_sequence_number := old_rank_rec.load_ci_sequence_number ;
1026 l_old_population_table_rec(l_old_count).p_as_of_rank_gpa := old_rank_rec.as_of_rank_gpa ;
1027 l_old_population_table_rec(l_old_count).p_cohort_rank := old_rank_rec.cohort_rank ;
1028 l_old_population_table_rec(l_old_count).p_cohort_override_rank := old_rank_rec.cohort_override_rank ;
1029 l_old_population_table_rec(l_old_count).p_comments := old_rank_rec.comments ;
1030 l_old_population_table_rec(l_old_count).p_deletion_indicator := 'Y' ;
1031
1032
1033 END LOOP;
1034
1035 -- The output is a record group consisting of the all the students fitting the cohort rule and correspondingly ranked.
1036 -- Store this in a PL/SQL table called table l_new_population_table
1037
1038 l_new_count := 0;
1039
1040
1041 OPEN student_denrank_query_type_rec FOR
1042 ' SELECT res.* , DENSE_RANK () OVER (order by res.cum_gpa desc) AS new_rank
1043 FROM
1044 (SELECT person_id, course_cd, igs_pr_class_rank.get_cum_gpa ( sca.person_id,sca.course_cd,''' || p_cohort_name || ''',
1045 ''' || p_cal_type || ''',' || p_ci_sequence_number || ',
1046 ''' || stat_type_rec.stat_type ||''',
1047 ''' || l_cumulative_ind || ''' ) cum_gpa
1048 FROM igs_en_sca_v sca
1049 WHERE sca.cal_type IN ' || l_acad_cal || '
1050 AND
1051 (sca.person_id, sca.course_cd) IN
1052 (
1053 SELECT sca.person_id, sca.course_cd
1054 FROM igs_en_su_attempt sua, igs_en_sca_v sca
1055 WHERE sua.person_id = sca.person_id
1056 AND sua.course_cd = sca.course_cd
1057 AND unit_attempt_status = ''COMPLETED''
1058 AND ( sua.cal_type , sua.ci_sequence_number ) IN
1059 (SELECT teach_cal_type, teach_ci_sequence_number
1060 FROM igs_ca_load_to_teach_v
1061 WHERE load_cal_type= ''' || p_cal_type || '''
1062 AND load_ci_sequence_number = ' || p_ci_sequence_number || '
1063 )
1064 )
1065 AND
1066 igs_pr_class_rank.rulp_val_senna_res (
1067 sca.person_id,
1068 sca.course_cd,
1069 sca.version_number,
1070 NULL,
1071 NULL,
1072 ''' || p_cal_type || ''',
1073 ' || p_ci_sequence_number || ','
1074 || stat_type_rec.rule_sequence_number || ') = ''true'' ) res';
1075
1076 LOOP
1077 FETCH student_denrank_query_type_rec INTO student_denseranked_query_rec;
1078 EXIT WHEN student_denrank_query_type_rec%NOTFOUND;
1079
1080 p_count := p_count + 1;
1081
1082 l_old_flag := 'Y';
1083
1084
1085 l_new_count := l_new_count + 1;
1086
1087 l_new_population_table_rec(l_new_count).p_person_id := student_denseranked_query_rec.person_id ;
1088 l_new_population_table_rec(l_new_count).p_course_cd := student_denseranked_query_rec.course_cd ;
1089 l_new_population_table_rec(l_new_count).p_cohort_name := p_cohort_name ;
1090 l_new_population_table_rec(l_new_count).p_load_cal_type := p_cal_type ;
1091 l_new_population_table_rec(l_new_count).p_load_ci_sequence_number := p_ci_sequence_number ;
1092 l_new_population_table_rec(l_new_count).p_as_of_rank_gpa := student_denseranked_query_rec.cum_gpa ;
1093 l_new_population_table_rec(l_new_count).p_cohort_rank := student_denseranked_query_rec.new_rank ;
1094 l_new_population_table_rec(l_new_count).p_cohort_override_rank := NULL ;
1095 l_new_population_table_rec(l_new_count).p_comments := NULL ;
1096 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'Y' ;
1097
1098
1099 FOR i IN 1..l_old_count LOOP
1100
1101
1102 IF( l_new_population_table_rec(l_new_count).p_person_id = l_old_population_table_rec(i).p_person_id AND
1103 l_new_population_table_rec(l_new_count).p_course_cd = l_old_population_table_rec(i).p_course_cd AND
1104 l_new_population_table_rec(l_new_count).p_cohort_name = l_old_population_table_rec(i).p_cohort_name AND
1105 l_new_population_table_rec(l_new_count).p_load_cal_type = l_old_population_table_rec(i).p_load_cal_type AND
1106 l_new_population_table_rec(l_new_count).p_load_ci_sequence_number = l_old_population_table_rec(i).p_load_ci_sequence_number ) THEN
1107
1108 -- If the students existinf in the old cohort instance list and the new cohort instance then the old rank and the GPA for the student is updated in the
1109 -- Cohort Instance Rank table.
1110
1111
1112 l_old_flag := 'N';
1113
1114 igs_pr_cohinst_rank_pkg.update_row (
1115 x_rowid => l_old_population_table_rec(i).p_rowid ,
1116 x_cohort_name => l_old_population_table_rec(i).p_cohort_name,
1117 x_load_cal_type => l_old_population_table_rec(i).p_load_cal_type ,
1118 x_load_ci_sequence_number => l_old_population_table_rec(i).p_load_ci_sequence_number ,
1119 x_person_id => l_old_population_table_rec(i).p_person_id ,
1120 x_course_cd => l_old_population_table_rec(i).p_course_cd ,
1121 x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
1122 x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
1123 x_cohort_override_rank => l_old_population_table_rec(i).p_cohort_override_rank ,
1124 x_comments => l_old_population_table_rec(i).p_comments
1125 );
1126
1127 -- Marking the Records in the New List and the Old list, for the records which already exist in the
1128 -- Cohort Instance Rank table as of this step. This indicator would be used later, when the
1129 -- records for the Student Cohort Instance rank has to be deleted from the table Cohort Instance Rank,
1130 -- which are not avaliable in the new list.
1131
1132 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'N' ;
1133 l_old_population_table_rec(i).p_deletion_indicator := 'N' ;
1134
1135 END IF;
1136
1137 -- Records existing in the new list and not in the old list . Those records are inserted in the Cohort Instance Rank table
1138
1139 END LOOP;
1140
1141 IF l_old_flag = 'Y' THEN
1142 igs_pr_cohinst_rank_pkg.insert_row (
1143 x_rowid => l_rowid ,
1144 x_cohort_name => l_new_population_table_rec(l_new_count).p_cohort_name ,
1145 x_load_cal_type => l_new_population_table_rec(l_new_count).p_load_cal_type ,
1146 x_load_ci_sequence_number => l_new_population_table_rec(l_new_count).p_load_ci_sequence_number,
1147 x_person_id => l_new_population_table_rec(l_new_count).p_person_id ,
1148 x_course_cd => l_new_population_table_rec(l_new_count).p_course_cd ,
1149 x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
1150 x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
1151 x_cohort_override_rank => l_new_population_table_rec(l_new_count).p_cohort_override_rank ,
1152 x_comments => l_new_population_table_rec(l_new_count).p_comments
1153 );
1154
1155 l_old_flag := 'N';
1156
1157 END IF;
1158
1159 l_new_population_table_rec(l_new_count).p_deletion_indicator := 'Y' ;
1160 l_rowid := NULL ;
1161
1162
1163 END LOOP;
1164
1165 -- If Cohort status is "WORKING" and the rule returned no student population to be ranked,
1166 -- then write the message IGS_PR_RNK_NO_POP "No population returned" to the log file.
1167
1168 IF l_new_count = 0 THEN
1169
1170 FND_MESSAGE.SET_NAME('IGS','IGS_PR_RNK_NO_POP');
1171 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1172
1173 END IF;
1174
1175 -- Deleting Records which from the Cohort Instance Rank table, which do not fit the rule criterian any more.
1176
1177
1178 FOR j IN 1..l_old_count LOOP
1179
1180 IF l_old_population_table_rec(j).p_deletion_indicator = 'Y' THEN
1181
1182 igs_pr_cohinst_rank_pkg.delete_row (
1183 x_rowid => l_old_population_table_rec(j).p_rowid
1184 );
1185
1186 END IF;
1187
1188 END LOOP;
1189
1190
1191 END IF;
1192
1193 --
1194 -- Updating the Cohert Instance table with the rundate as the System Date. Rest of the values are retained.
1195 --
1196
1197 igs_pr_cohort_inst_pkg.update_row(
1198 x_rowid => rank_status_rec.row_id,
1199 x_cohort_name => rank_status_rec.cohort_name,
1200 x_load_cal_type => rank_status_rec.load_cal_type,
1201 x_load_ci_sequence_number => rank_status_rec.load_ci_sequence_number,
1202 x_cohort_status => rank_status_rec.cohort_status,
1203 x_rank_status => rank_status_rec.rank_status,
1204 x_run_date => SYSDATE
1205 );
1206
1207 END IF ; -- 2
1208
1209 END IF; --1
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 Fnd_Message.Set_Name('IGS', 'Ranking Process => ' || SQLERRM);
1214 IGS_GE_MSG_STACK.ADD;
1215 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1216 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_CLASS_RANK.RANKING_PROCESS');
1217 IGS_GE_MSG_STACK.ADD;
1218 App_Exception.Raise_Exception;
1219 END ranking_process ;
1220
1221
1222 FUNCTION get_cum_gpa
1223 ( p_person_id IN igs_en_sca_v.person_id%TYPE,
1224 p_course_cd IN igs_en_sca_v.course_cd%TYPE,
1225 p_cohort_name IN igs_pr_cohort.cohort_name%TYPE,
1226 p_cal_type IN igs_ca_inst.cal_type%TYPE,
1227 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1228 p_stat_type IN VARCHAR2,
1229 p_cumulative_ind IN VARCHAR2
1230 ) RETURN NUMBER IS
1231 /****************************************************************************************************************
1232 || Created By : DDEY
1233 || Created On : 28-OCT-2002
1234 || Purpose : A new function called GET_CUM_GPA is required for fetching the GPA, which is used in calculating the rank for each of these students .
1235 || Known limitations, enhancements or remarks :
1236 || Change History :
1237 || Who When What
1238 || swaghmar 15-Sep-2005 Bug# 4491456
1239 || (reverse chronological order - newest change first)
1240 ****************************************************************************************************************/
1241
1242 l_gpa_val NUMBER;
1243 l_gpa_cp NUMBER;
1244 l_gpa_quality_points NUMBER;
1245 l_return_status VARCHAR2(30);
1246 l_msg_count NUMBER ;
1247 l_msg_data VARCHAR2(2000);
1248
1249 BEGIN
1250
1251 igs_pr_cp_gpa.get_gpa_stats( p_person_id => p_person_id ,
1252 p_course_cd => p_course_cd ,
1253 p_stat_type => p_stat_type ,
1254 p_load_cal_type => p_cal_type ,
1255 p_load_ci_sequence_number => p_ci_sequence_number ,
1256 p_system_stat => NULL ,
1257 p_cumulative_ind => p_cumulative_ind ,
1258 p_gpa_value => l_gpa_val ,
1259 p_gpa_cp => l_gpa_cp ,
1260 p_gpa_quality_points => l_gpa_quality_points ,
1261 p_init_msg_list => NULL ,
1262 p_return_status => l_return_status ,
1263 p_msg_count => l_msg_count ,
1264 p_msg_data => l_msg_data ) ;
1265
1266
1267
1268 IF l_gpa_val IS NULL THEN
1269 return 0 ;
1270 ELSE
1271 return l_gpa_val ;
1272 END IF;
1273
1274
1275 END get_cum_gpa ;
1276
1277 FUNCTION rulp_val_senna_res (
1278 p_person_id IN igs_en_sca_v.person_id%TYPE,
1279 p_course_cd IN igs_en_sca_v.course_cd%TYPE ,
1280 p_course_version IN igs_en_sca_v.version_number%TYPE,
1281 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
1282 p_unit_version IN igs_en_su_attempt.version_number%TYPE,
1283 p_cal_type IN igs_en_su_attempt.cal_type%TYPE,
1284 p_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
1285 p_rule_number IN igs_ru_call_v.rul_sequence_number%TYPE) RETURN VARCHAR2 IS
1286
1287 /****************************************************************************************************************
1288 || Created By : DDEY
1289 || Created On : 28-OCT-2002
1290 || Purpose : A new function which call IGS_RU_GEN_001.RULP_VAL_SENNA. This function is created
1291 || Known limitations, enhancements or remarks :
1292 || Change History :
1293 || Who When What
1294 || (reverse chronological order - newest change first)
1295 ****************************************************************************************************************/
1296
1297 l_message VARCHAR2(2000);
1298 l_status VARCHAR2(2000);
1299
1300 BEGIN
1301
1302 l_status := igs_ru_gen_001.rulp_val_senna (
1303 p_person_id => p_person_id,
1304 p_course_cd => p_course_cd,
1305 p_course_version => p_course_version,
1306 p_unit_cd => NULL,
1307 p_unit_version => NULL,
1308 p_cal_type => p_cal_type,
1309 p_ci_sequence_number => p_ci_sequence_number,
1310 p_message => l_message ,
1311 p_rule_number => p_rule_number,
1312 p_param_1 => p_course_cd) ;
1313
1314
1315 RETURN l_status ;
1316
1317 END rulp_val_senna_res ;
1318
1319
1320
1321 PROCEDURE raise_clsrank_be_cr001 (p_cohort_name IN VARCHAR2,
1322 p_cohort_instance IN VARCHAR2,
1323 p_new_cohort_status IN VARCHAR2,
1324 p_new_rank_status IN VARCHAR2) IS
1325
1326 l_wf_event_t WF_EVENT_T;
1327 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1328
1329 BEGIN
1330 --
1331 -- initialize the wf_event_t object
1332 --
1333 WF_EVENT_T.Initialize(l_wf_event_t);
1334 --
1335 -- set the event name
1336 --
1337 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.pr.clsrank_be_cr001');
1338 --
1339 -- set the event key but before the select a number from sequenec
1340 --
1341 l_wf_event_t.setEventKey ( pEventKey => 'be_cr001'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') );
1342 --
1343 -- set the parameter list
1344 --
1345 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
1346 --
1347 -- now add the parameters to the parameter list
1348 --
1349 wf_event.addparametertolist(
1350 P_NAME => 'COHORT_NAME' ,
1351 P_VALUE => p_cohort_name ,
1352 P_PARAMETERLIST => l_wf_parameter_list_t);
1353 wf_event.addparametertolist(
1354 P_NAME => 'COHORT_INSTANCE' ,
1355 P_VALUE => p_cohort_instance,
1356 P_PARAMETERLIST => l_wf_parameter_list_t);
1357 wf_event.addparametertolist(
1358 P_NAME => 'NEW_COHORT_STATUS' ,
1359 P_VALUE => p_new_cohort_status ,
1360 P_PARAMETERLIST => l_wf_parameter_list_t);
1361 wf_event.addparametertolist(
1362 P_NAME => 'NEW_RANK_STATUS' ,
1363 P_VALUE => p_new_rank_status ,
1364 P_PARAMETERLIST => l_wf_parameter_list_t);
1365
1366 --
1367 -- raise the event
1368 --
1369 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pr.clsrank_be_cr001',
1370 p_event_key => 'be_cr001'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
1371 p_event_data => NULL,
1372 p_parameters => l_wf_parameter_list_t);
1373
1374 END raise_clsrank_be_cr001;
1375
1376
1377 PROCEDURE raise_clsrank_be_cr002 (p_person_id IN NUMBER,
1378 p_person_number IN VARCHAR2,
1379 p_person_name IN VARCHAR2,
1380 p_current_rank IN NUMBER,
1381 p_override_rank IN NUMBER,
1382 p_ovrby_person_id IN NUMBER,
1383 p_ovrby_person_number IN VARCHAR2,
1384 p_ovrby_person_name IN VARCHAR2) IS
1385
1386 l_wf_event_t WF_EVENT_T;
1387 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1388
1389 BEGIN
1390 --
1391 -- initialize the wf_event_t object
1392 --
1393 WF_EVENT_T.Initialize(l_wf_event_t);
1394 --
1395 -- set the event name
1396 --
1397 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.pr.clsrank_be_cr002');
1398 --
1399 -- set the event key but before the select a number from sequenec
1400 --
1401 l_wf_event_t.setEventKey ( pEventKey => 'be_cr002'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') );
1402 --
1403 -- set the parameter list
1404 --
1405 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
1406 --
1407 -- now add the parameters to the parameter list
1408 --
1409 wf_event.addparametertolist(
1410 P_NAME => 'PERSON_ID' ,
1411 P_VALUE => p_person_id ,
1412 P_PARAMETERLIST => l_wf_parameter_list_t);
1413 wf_event.addparametertolist(
1414 P_NAME => 'PERSON_NUMBER' ,
1415 P_VALUE => p_person_number,
1416 P_PARAMETERLIST => l_wf_parameter_list_t);
1417 wf_event.addparametertolist(
1418 P_NAME => 'PERSON_NAME' ,
1419 P_VALUE => p_person_name ,
1420 P_PARAMETERLIST => l_wf_parameter_list_t);
1421 wf_event.addparametertolist(
1422 P_NAME => 'CURRENT_RANK' ,
1423 P_VALUE => p_current_rank ,
1424 P_PARAMETERLIST => l_wf_parameter_list_t);
1425 wf_event.addparametertolist(
1426 P_NAME => 'OVERRIDE_RANK' ,
1427 P_VALUE => p_override_rank ,
1428 P_PARAMETERLIST => l_wf_parameter_list_t);
1429 wf_event.addparametertolist(
1430 P_NAME => 'OVRBY_PERSON_ID' ,
1431 P_VALUE => p_ovrby_person_id ,
1432 P_PARAMETERLIST => l_wf_parameter_list_t);
1433 wf_event.addparametertolist(
1434 P_NAME => 'OVRBY_PERSON_NUMBER' ,
1435 P_VALUE => p_ovrby_person_number ,
1436 P_PARAMETERLIST => l_wf_parameter_list_t);
1437 wf_event.addparametertolist(
1438 P_NAME => 'OVRBY_PERSON_NAME' ,
1439 P_VALUE => p_ovrby_person_name ,
1440 P_PARAMETERLIST => l_wf_parameter_list_t);
1441 --
1442 -- raise the event
1443 --
1444 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pr.clsrank_be_cr002',
1445 p_event_key => 'be_cr002'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
1446 p_event_data => NULL,
1447 p_parameters => l_wf_parameter_list_t);
1448
1449 END raise_clsrank_be_cr002;
1450
1451
1452
1453 PROCEDURE raise_clsrank_be_cr003 (p_cohort_name IN VARCHAR2,
1454 p_cohort_instance IN VARCHAR2,
1455 p_run_date IN VARCHAR2,
1456 p_cohort_total_students IN VARCHAR2) IS
1457
1458 l_wf_event_t WF_EVENT_T;
1459 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1460
1461 BEGIN
1462 --
1463 -- initialize the wf_event_t object
1464 --
1465 WF_EVENT_T.Initialize(l_wf_event_t);
1466 --
1467 -- set the event name
1468 --
1469 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.pr.clsrank_be_cr003');
1470 --
1471 -- set the event key but before the select a number from sequenec
1472 --
1473 l_wf_event_t.setEventKey ( pEventKey => 'be_cr003'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') );
1474 --
1475 -- set the parameter list
1476 --
1477 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
1478 --
1479 -- now add the parameters to the parameter list
1480 --
1481 wf_event.addparametertolist(
1482 P_NAME => 'COHORT_NAME' ,
1483 P_VALUE => p_cohort_name ,
1484 P_PARAMETERLIST => l_wf_parameter_list_t);
1485 wf_event.addparametertolist(
1486 P_NAME => 'COHORT_INSTANCE' ,
1487 P_VALUE => p_cohort_instance,
1488 P_PARAMETERLIST => l_wf_parameter_list_t);
1489 wf_event.addparametertolist(
1490 P_NAME => 'NEW_RUN_DATE' ,
1491 P_VALUE => p_run_date ,
1492 P_PARAMETERLIST => l_wf_parameter_list_t);
1493 wf_event.addparametertolist(
1494 P_NAME => 'NEW_COHORT_TOTAL_STUDENTS' ,
1495 P_VALUE => p_cohort_total_students ,
1496 P_PARAMETERLIST => l_wf_parameter_list_t);
1497
1498 --
1499 -- raise the event
1500 --
1501 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pr.clsrank_be_cr003',
1502 p_event_key => 'be_cr003'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
1503 p_event_data => NULL,
1504 p_parameters => l_wf_parameter_list_t);
1505
1506 END raise_clsrank_be_cr003;
1507
1508
1509
1510 PROCEDURE get_formatted_rank (p_cohort_name IN VARCHAR2,
1511 p_cal_type IN VARCHAR2,
1512 p_ci_sequence_number IN NUMBER,
1513 p_person_id IN NUMBER,
1514 p_disp_type IN VARCHAR2, /* pass lookup code */
1515 p_program_cd IN VARCHAR2,
1516 x_formatted_rank OUT NOCOPY VARCHAR2,
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_msg_count OUT NOCOPY NUMBER,
1519 x_msg_data OUT NOCOPY VARCHAR2)
1520 AS
1521 -- cursor to get default display type
1522 CURSOR c_dflt_disp_type (cp_cohort_name igs_pr_cohort.cohort_name%TYPE) IS
1523 SELECT dflt_display_type
1524 FROM igs_pr_cohort
1525 WHERE cohort_name = cp_cohort_name;
1526 l_disp_type igs_pr_cohort.dflt_display_type%TYPE;
1527
1528 -- cursor to select the rank
1529 CURSOR c_rank (cp_cohort_name igs_pr_cohort.cohort_name%TYPE,
1530 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
1531 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE,
1532 cp_person_id igs_pr_cohinst_rank.person_id%TYPE,
1533 cp_program_cd igs_pr_cohinst_rank.course_cd%TYPE) IS
1534 SELECT NVL(cohort_override_rank, cohort_rank)
1535 FROM igs_pr_cohort_inst_rank_v cohirv
1536 WHERE cohirv.cohort_name = cp_cohort_name
1537 AND cohirv.load_cal_type = cp_cal_type
1538 AND cohirv.load_ci_sequence_number = cp_ci_sequence_number
1539 AND cohirv.person_id = cp_person_id
1540 AND cohirv.course_cd = cp_program_cd;
1541 l_rank igs_pr_cohinst_rank.cohort_rank%TYPE;
1542
1543 -- cursor to get the cohort population
1544 CURSOR c_cohort_population (cp_cohort_name igs_pr_cohort.cohort_name%TYPE,
1545 cp_cal_type igs_pr_cohort_inst.load_cal_type%TYPE,
1546 cp_ci_sequence_number igs_pr_cohort_inst.load_ci_sequence_number%TYPE) IS
1547 SELECT COUNT (*)
1548 FROM igs_pr_cohort_inst_rank_v cohirv
1549 WHERE cohirv.cohort_name = cp_cohort_name
1550 AND cohirv.load_cal_type = cp_cal_type
1551 AND cohirv.load_ci_sequence_number = cp_ci_sequence_number;
1552
1553 l_cohort_population NUMBER;
1554
1555 l_formatted_rank VARCHAR2(4000);
1556
1557 -- cursor to get nth for a number like 84th
1558 CURSOR c_nth (cp_rank VARCHAR2) IS
1559 SELECT LTRIM (TO_CHAR (TO_DATE (cp_rank,'J'),'Jth'),'0') FROM DUAL;
1560 l_nth VARCHAR2(4000);
1561
1562 BEGIN
1563 Fnd_Msg_Pub.Initialize;
1564 l_formatted_rank := NULL;
1565 -- validate parameters
1566 IF p_cohort_name IS NULL OR
1567 p_cal_type IS NULL OR
1568 p_ci_sequence_number IS NULL OR
1569 p_person_id IS NULL OR
1570 p_program_cd IS NULL THEN
1571 l_formatted_rank := NULL;
1572 FND_MESSAGE.SET_NAME ('IGS','IGS_AD_INVALID_PARAM_COMB');
1573 FND_MSG_PUB.ADD;
1574 RAISE FND_API.G_EXC_ERROR;
1575 END IF;
1576
1577 -- select the default display type if p_disp_type is null
1578 IF p_disp_type IS NULL THEN
1579 OPEN c_dflt_disp_type (p_cohort_name);
1580 FETCH c_dflt_disp_type INTO l_disp_type;
1581 CLOSE c_dflt_disp_type;
1582 ELSE
1583 l_disp_type := p_disp_type;
1584 END IF;
1585
1586 -- select the rank
1587 OPEN c_rank (p_cohort_name ,
1588 p_cal_type ,
1589 p_ci_sequence_number,
1590 p_person_id ,
1591 p_program_cd );
1592 FETCH c_rank INTO l_rank;
1593 IF c_rank%NOTFOUND THEN
1594 CLOSE c_rank;
1595 FND_MESSAGE.SET_NAME ('IGS','IGS_PR_NO_RANK_AVL');
1596 FND_MSG_PUB.ADD;
1597 RAISE FND_API.G_EXC_ERROR;
1598 END IF;
1599 CLOSE c_rank;
1600
1601 -- select the cohort population
1602 OPEN c_cohort_population (p_cohort_name ,
1603 p_cal_type ,
1604 p_ci_sequence_number);
1605 FETCH c_cohort_population INTO l_cohort_population;
1606 CLOSE c_cohort_population;
1607
1608 -- decide number
1609 IF l_disp_type = 'N_OF_N' THEN
1610 l_formatted_rank := l_rank||' '||FND_MESSAGE.GET_STRING('IGS','IGS_PR_MSG_OF')||' '||l_cohort_population ;
1611 ELSIF l_disp_type = 'PERCENTILE' THEN
1612 l_formatted_rank := CEIL ((l_rank * 100)/(l_cohort_population + 1)) ;
1613 ELSIF l_disp_type = 'VIGINTILE' THEN
1614 l_formatted_rank := CEIL ((l_rank * 20)/(l_cohort_population + 1))*100/20 ;
1615 ELSIF l_disp_type = 'DECILE' THEN
1616 l_formatted_rank := CEIL ((l_rank * 10)/(l_cohort_population + 1))*100/10 ;
1617 ELSIF l_disp_type = 'QUINTILE' THEN
1618 l_formatted_rank := CEIL ((l_rank * 5)/(l_cohort_population + 1))*100/5 ;
1619 ELSIF l_disp_type = 'QUARTILE' THEN
1620 l_formatted_rank := CEIL ((l_rank * 4)/(l_cohort_population + 1))*100/4 ;
1621 ELSIF l_disp_type = 'TOP_THIRD' THEN
1622 IF ((l_cohort_population-l_rank)/l_cohort_population)*100 > 67 THEN
1623 l_formatted_rank := FND_MESSAGE.GET_STRING('IGS','IGS_PR_RNK_TP_TRD');
1624 ELSE
1625 l_formatted_rank := NULL;
1626 END IF;
1627 END IF;
1628 IF l_formatted_rank IS NOT NULL THEN
1629 -- check that l_formatted_rank carries a number
1630 DECLARE
1631 n NUMBER;
1632 BEGIN
1633 n := to_number(l_formatted_rank);
1634 OPEN c_nth (l_formatted_rank);
1635 FETCH c_nth INTO l_nth;
1636 CLOSE c_nth;
1637 l_formatted_rank := l_nth||' '||FND_MESSAGE.GET_STRING('IGS','IGS_PR_RNK_PRCNTL');
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 NULL;
1641 END;
1642 END IF;
1643
1644
1645 x_formatted_rank := l_formatted_rank;
1646 --Initialize API return status to success.
1647 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1648
1649 --Standard call to get message count and if count is 1, get message
1650 --info.
1651 Fnd_Msg_Pub.Count_And_Get(
1652 p_encoded => Fnd_Api.G_FALSE,
1653 p_count => x_msg_count,
1654 p_data => x_msg_data);
1655
1656 EXCEPTION
1657 WHEN FND_API.G_EXC_ERROR THEN
1658 x_formatted_rank := l_formatted_rank;
1659 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1660 FND_MSG_PUB.Count_And_Get(
1661 p_encoded => FND_API.G_FALSE,
1662 p_count => x_MSG_COUNT,
1663 p_data => X_MSG_DATA);
1664 RETURN;
1665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1666 x_formatted_rank := l_formatted_rank;
1667 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1668 FND_MSG_PUB.Count_And_Get(
1669 p_encoded => FND_API.G_FALSE,
1670 p_count => x_MSG_COUNT,
1671 p_data => X_MSG_DATA);
1672 RETURN;
1673 WHEN OTHERS THEN
1674 x_formatted_rank := l_formatted_rank;
1675 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1676 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1677 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
1678 FND_MSG_PUB.ADD;
1679 FND_MSG_PUB.Count_And_Get(
1680 p_encoded => FND_API.G_FALSE,
1681 p_count => x_MSG_COUNT,
1682 p_data => X_MSG_DATA);
1683 RETURN;
1684 END get_formatted_rank;
1685
1686 FUNCTION get_formatted_rank (p_cohort_name IN VARCHAR2,
1687 p_cal_type IN VARCHAR2,
1688 p_ci_sequence_number IN NUMBER,
1689 p_person_id IN NUMBER,
1690 p_disp_type IN VARCHAR2,
1691 p_program_cd IN VARCHAR2)
1692 RETURN VARCHAR2
1693 /****************************************************************************************************************
1694 || Created By : SMANGLM
1695 || Created On : 28-OCT-2002
1696 || Purpose : This is the function returning rank
1697 || Known limitations, enhancements or remarks :
1698 || Change History :
1699 || Who When What
1700 || (reverse chronological order - newest change first)
1701 ****************************************************************************************************************/
1702 AS
1703 l_formatted_rank VARCHAR2(2000) := null;
1704 l_return_status VARCHAR2(10):=null;
1705 l_msg_count NUMBER :=null;
1706 l_msg_data VARCHAR2(2000):=null;
1707 BEGIN
1708 IGS_PR_CLASS_RANK.GET_FORMATTED_RANK (
1709 P_COHORT_NAME => p_cohort_name,
1710 P_CAL_TYPE => p_cal_type,
1711 P_CI_SEQUENCE_NUMBER => p_ci_sequence_number,
1712 P_PERSON_ID => p_person_id,
1713 P_DISP_TYPE => p_disp_type,
1714 P_PROGRAM_CD => p_program_cd,
1715 X_FORMATTED_RANK => l_formatted_rank,
1716 X_RETURN_STATUS => l_return_status ,
1717 X_MSG_COUNT => l_msg_count ,
1718 X_MSG_DATA => l_msg_data );
1719 IF l_formatted_rank IS NOT NULL THEN
1720 RETURN l_formatted_rank;
1721 ELSE
1722 RETURN NULL;
1723 END IF;
1724 END get_formatted_rank;
1725
1726
1727 END igs_pr_class_rank;
[Home] [Help]