1 PACKAGE BODY igs_ad_assign_eval_ai_pkg AS
2 /* $Header: IGSADB4B.pls 120.3 2006/02/02 02:39:15 pfotedar ship $ */
3
4 PROCEDURE Assign_Eval_To_Appl_Inst(
5 Errbuf OUT NOCOPY VARCHAR2,
6 Retcode OUT NOCOPY NUMBER,
7 p_review_profile_id IN NUMBER DEFAULT NULL,
8 p_review_group_code IN NUMBER DEFAULT NULL,
9 p_unassigned_appl IN VARCHAR2 DEFAULT NULL,
10 p_org_id IN NUMBER
11 )
12 AS
13
14 /*************************************************************
15 Created By :nsinha
16 Date : 20-NOV-2001
17 Created By : Navin.Sinha
18 Purpose : This Procedure Assigns Evaluators to Applications
19 that have been assigned Review Groups with no Evaluator.
20 This procedure is created as part of Enh# : 2097333.
21 Know limitations, enhancements or remarks
22 Change History
23 Who When What
24 rghosh 8-may-2003 Modified code so that the evaluation sequence assigned to each evaluator will be the same
25 bug#2871426
26 nsinha 19-May-03 Corrected the cursor parameter value while opening c_appl_rev_profile.
27 ***************************************************************/
28
29 INVALID_PARAMETER EXCEPTION;
30
31 l_rating_exists VARCHAR2(1);
32 l_appl_revprof_revgr_id igs_ad_apl_rprf_rgr.appl_revprof_revgr_id%TYPE;
33
34
35 CURSOR c_common_no_eval(cp_review_profile_id igs_ad_appl_arp.appl_rev_profile_id%TYPE,
36 cp_appl_revprof_revgr_id igs_ad_appl_arp.appl_revprof_revgr_id%TYPE) IS
37 SELECT apl.person_id,
38 apl.admission_appl_number,
39 apl.nominated_course_cd,
40 apl.sequence_number,
41 arp.appl_rev_profile_id,
42 arp.appl_revprof_revgr_id
43 FROM igs_ad_ps_appl_inst_all apl,
44 igs_ad_appl_arp arp
45 WHERE apl.person_id = arp.person_id
46 AND apl.admission_appl_number = arp.admission_appl_number
47 AND apl.nominated_course_cd = arp.nominated_course_cd
48 AND apl.sequence_number = arp.sequence_number
49 AND arp.appl_revprof_revgr_id IS NOT NULL
50 AND EXISTS (SELECT '1'
51 FROM igs_ad_ou_stat ou
52 WHERE ou.s_adm_outcome_status = 'PENDING'
53 AND apl.adm_outcome_status = ou.adm_outcome_status )
54 AND NOT EXISTS (SELECT '1'
55 FROM igs_ad_appl_eval aev
56 WHERE aev.person_id = apl.person_id
57 AND aev.admission_appl_number = apl.admission_appl_number
58 AND aev.nominated_course_cd = apl.nominated_course_cd
59 AND aev.sequence_number = apl.sequence_number );
60
61 CURSOR c_common_no_eval_prf(cp_review_profile_id igs_ad_appl_arp.appl_rev_profile_id%TYPE,
62 cp_appl_revprof_revgr_id igs_ad_appl_arp.appl_revprof_revgr_id%TYPE) IS
63 SELECT apl.person_id,
64 apl.admission_appl_number,
65 apl.nominated_course_cd,
66 apl.sequence_number,
67 arp.appl_rev_profile_id,
68 arp.appl_revprof_revgr_id
69 FROM igs_ad_ps_appl_inst_all apl,
70 igs_ad_appl_arp arp
71 WHERE apl.person_id = arp.person_id
72 AND apl.admission_appl_number = arp.admission_appl_number
73 AND apl.nominated_course_cd = arp.nominated_course_cd
74 AND apl.sequence_number = arp.sequence_number
75 AND arp.appl_revprof_revgr_id IS NOT NULL
76 AND EXISTS (SELECT '1'
77 FROM igs_ad_ou_stat ou
78 WHERE ou.s_adm_outcome_status = 'PENDING'
79 AND apl.adm_outcome_status = ou.adm_outcome_status )
80 AND NOT EXISTS (SELECT '1'
81 FROM igs_ad_appl_eval aev
82 WHERE aev.person_id = apl.person_id
83 AND aev.admission_appl_number = apl.admission_appl_number
84 AND aev.nominated_course_cd = apl.nominated_course_cd
85 AND aev.sequence_number = apl.sequence_number )
86 AND arp.appl_rev_profile_id = cp_review_profile_id;
87
88 CURSOR c_common_no_eval_grp(cp_review_profile_id igs_ad_appl_arp.appl_rev_profile_id%TYPE,
89 cp_appl_revprof_revgr_id igs_ad_appl_arp.appl_revprof_revgr_id%TYPE) IS
90 SELECT apl.person_id,
91 apl.admission_appl_number,
92 apl.nominated_course_cd,
93 apl.sequence_number,
94 arp.appl_rev_profile_id,
95 arp.appl_revprof_revgr_id
96 FROM igs_ad_ps_appl_inst_all apl,
97 igs_ad_appl_arp arp
98 WHERE apl.person_id = arp.person_id
99 AND apl.admission_appl_number = arp.admission_appl_number
100 AND apl.nominated_course_cd = arp.nominated_course_cd
101 AND apl.sequence_number = arp.sequence_number
102 AND arp.appl_revprof_revgr_id IS NOT NULL
103 AND EXISTS (SELECT '1'
104 FROM igs_ad_ou_stat ou
105 WHERE ou.s_adm_outcome_status = 'PENDING'
106 AND apl.adm_outcome_status = ou.adm_outcome_status )
107 AND NOT EXISTS (SELECT '1'
108 FROM igs_ad_appl_eval aev
109 WHERE aev.person_id = apl.person_id
110 AND aev.admission_appl_number = apl.admission_appl_number
111 AND aev.nominated_course_cd = apl.nominated_course_cd
112 AND aev.sequence_number = apl.sequence_number )
113 AND arp.appl_revprof_revgr_id = cp_appl_revprof_revgr_id;
114
115 l_common_no_eval_rec c_common_no_eval%ROWTYPE;
116 l_common_no_eval_prf_rec c_common_no_eval_prf%ROWTYPE;
117 l_common_no_eval_grp_rec c_common_no_eval_grp%ROWTYPE;
118
119
120 CURSOR c_appl_rev_profile (cp_appl_rev_profile_id igs_ad_apl_rev_prf.appl_rev_profile_id%TYPE) IS
121 SELECT review_profile_name
122 FROM igs_ad_apl_rev_prf
123 WHERE appl_rev_profile_id = cp_appl_rev_profile_id;
124
125 l_review_profile_name igs_ad_apl_rev_prf.review_profile_name%TYPE;
126
127 CURSOR c_appl_rev_group (cp_review_group_code igs_ad_apl_rprf_rgr.revprof_revgr_cd%TYPE) IS
128 SELECT revprof_revgr_cd
129 FROM igs_ad_apl_rprf_rgr
130 WHERE appl_revprof_revgr_id = cp_review_group_code;
131
132 l_review_group_code igs_ad_apl_rprf_rgr.revprof_revgr_cd%TYPE;
133
134 l_unassigned_appl VARCHAR2(3);
135
136 BEGIN
137
138 IGS_GE_GEN_003.Set_org_id(p_org_id);
139
140 OPEN c_appl_rev_profile(p_review_profile_id);
141 FETCH c_appl_rev_profile INTO l_review_profile_name;
142 CLOSE c_appl_rev_profile;
143
144 OPEN c_appl_rev_group(p_review_group_code);
145 FETCH c_appl_rev_group INTO l_review_group_code;
146 CLOSE c_appl_rev_group;
147
148 IF p_unassigned_appl = '1' THEN l_unassigned_appl := 'YES';
149 ELSIF p_unassigned_appl = '2' THEN l_unassigned_appl := 'NO';
150 END IF;
151
152 FND_FILE.PUT_LINE(FND_FILE.LOG,'Review Profile Name :' || l_review_profile_name);
153 FND_FILE.PUT_LINE(FND_FILE.LOG,'Review Group Code :' || l_review_group_code);
154 FND_FILE.PUT_LINE(FND_FILE.LOG,'All Unassigned Applications :' || l_unassigned_appl);
155
156 IF p_review_profile_id IS NULL AND
157 p_review_group_code IS NULL AND
158 p_unassigned_appl IS NULL THEN
159 FND_MESSAGE.SET_NAME('IGS','IGS_FI_SELECT_COMBN_NORECORDS');
160 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
161 RAISE INVALID_PARAMETER;
162 END IF;
163
164 IF p_review_profile_id IS NOT NULL AND
165 p_review_group_code IS NOT NULL AND
166 p_unassigned_appl = '1' THEN -- p_unassigned_appl is passed as 'Y' interpreted as '1'
167 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROCESS_PARAM');
168 FND_MESSAGE.SET_TOKEN('PARAM_NAME',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ALL_UNASGN_APL'));
169 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
170
171 ELSIF p_review_profile_id IS NOT NULL AND
172 p_review_group_code IS NOT NULL AND
173 NVL(p_unassigned_appl,'0') <> '1' THEN -- p_unassigned_appl is passed as 'Y' interpreted as '1'
174 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROCESS_PARAM');
175 FND_MESSAGE.SET_TOKEN('PARAM_NAME',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REV_PROF_NAME'));
176 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
177
178 ELSIF p_review_profile_id IS NULL AND
179 p_review_group_code IS NOT NULL AND
180 NVL(p_unassigned_appl,'0') <> '1' THEN -- p_unassigned_appl is passed as 'Y' interpreted as '1'
181 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROCESS_PARAM');
182 FND_MESSAGE.SET_TOKEN('PARAM_NAME',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REV_GR_CD'));
183 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
184 END IF;
185
186
187 -- Initialize l_appl_revprof_revgr_id based on wether user has provided the
188 -- value for Review Group Code or Review Group Name.
189
190 IF p_review_profile_id IS NULL AND p_review_group_code IS NOT NULL THEN
191 l_appl_revprof_revgr_id := p_review_group_code;
192 END IF;
193
194 Retcode := 0;
195
196 IF p_unassigned_appl = '1' THEN -- p_unassigned_appl is passed as 'Y'
197
198 FOR l_common_no_eval_rec IN c_common_no_eval(NULL,NULL)
199 LOOP
200 -- Loop through all the Application Instances for which there has been a
201 -- Review Group Code assigned but no evaluators have been assigned.
202
203 Assign_Eval_To_Ai(
204 Errbuf,
205 Retcode,
206 l_common_no_eval_rec.appl_rev_profile_id,
207 l_common_no_eval_rec.appl_revprof_revgr_id,
208 l_common_no_eval_rec.person_id,
209 l_common_no_eval_rec.admission_appl_number,
210 l_common_no_eval_rec.nominated_course_cd,
211 l_common_no_eval_rec.sequence_number);
212
213 END LOOP;
214
215 ELSIF p_review_profile_id IS NOT NULL THEN -- 2. Else If p_REVIEW_PROFILE_NAME is NOT NULL
216
217 FOR l_common_no_eval_prf_rec IN c_common_no_eval_prf(p_review_profile_id,NULL)
218 LOOP
219 -- Loop through all the Application Instances where Review_Profile_Id is same
220 -- as the parameter passed and for which there has been a Review Group Code
221 -- assigned but no evaluators have been assigned.
222
223 Assign_Eval_To_Ai(
224 Errbuf,
225 Retcode,
226 l_common_no_eval_prf_rec.appl_rev_profile_id,
227 l_common_no_eval_prf_rec.appl_revprof_revgr_id,
228 l_common_no_eval_prf_rec.person_id,
229 l_common_no_eval_prf_rec.admission_appl_number,
230 l_common_no_eval_prf_rec.nominated_course_cd,
231 l_common_no_eval_prf_rec.sequence_number);
232
233 END LOOP;
234
235 ELSIF l_appl_revprof_revgr_id IS NOT NULL THEN -- 3. Else If P_REVIEW_GROUP_CODE IS NOT NULL
236
237 FOR l_common_no_eval_grp_rec IN c_common_no_eval_grp(NULL,l_appl_revprof_revgr_id)
238 LOOP
239 -- Loop through all the Application Instances where Review_Group_Code is same
240 -- as the parameter passed and for which there has been no evaluator assigned.
241 Assign_Eval_To_Ai(
242 Errbuf,
243 Retcode,
244 l_common_no_eval_grp_rec.appl_rev_profile_id,
245 l_appl_revprof_revgr_id,
246 l_common_no_eval_grp_rec.person_id,
247 l_common_no_eval_grp_rec.admission_appl_number,
248 l_common_no_eval_grp_rec.nominated_course_cd,
249 l_common_no_eval_grp_rec.sequence_number);
250 END LOOP;
251
252 END IF;
253
254 EXCEPTION
255 WHEN INVALID_PARAMETER THEN
256 Retcode := 2;
257
258 WHEN OTHERS THEN
259 IF c_common_no_eval%ISOPEN THEN
260 CLOSE c_common_no_eval;
261 END IF;
262 IF c_common_no_eval_prf%ISOPEN THEN
263 CLOSE c_common_no_eval_prf;
264 END IF;
265 IF c_common_no_eval_grp%ISOPEN THEN
266 CLOSE c_common_no_eval_grp;
267 END IF;
268 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm(sqlcode));
269 Retcode := 2;
270 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
271 FND_MESSAGE.SET_TOKEN('NAME','Assign_Eval_To_Appl_Inst');
272 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
273 Igs_Ge_Msg_Stack.conc_exception_hndl;
274
275 END Assign_Eval_To_Appl_Inst;
276
277
278 PROCEDURE Assign_Eval_To_Ai(
279 Errbuf OUT NOCOPY VARCHAR2,
280 Retcode OUT NOCOPY NUMBER,
281 p_appl_rev_profile_id IN NUMBER,
282 p_appl_revprof_revgr_id IN NUMBER,
283 p_person_id IN NUMBER,
284 p_admission_appl_number IN NUMBER,
285 p_nominated_course_cd IN VARCHAR2,
286 p_sequence_number IN NUMBER
287 )
288 AS
289
290 /*************************************************************
291 Created By :nsinha
292 Date : 20-NOV-2001
293 Created By : Navin.Sinha
294 Purpose : API to assign the Evaluators of the p_appl_revprof_revgr_id
295 Review Group Code to the Application Instance.
296 This procedure is created as part of Enh# : 2097333.
297 Know limitations, enhancements or remarks
298 Change History
299 Who When What
300 rghosh 8-may-2003 Modified code so that the evaluation sequence assigned to each evaluator will be the same
301 bug#2871426
302 ***************************************************************/
303
304 invalid_parameter EXCEPTION;
305
306 -- Select all the Evaluators assigned for this Review Profile Code
307 CURSOR c_appl_rev_prof_evaluator(cp_appl_revprof_revgr_id NUMBER) IS
308 SELECT person_id,
309 person_number,
310 evaluation_sequence
311 FROM igs_ad_rvgr_evaltr ev
312 WHERE appl_revprof_revgr_id = cp_appl_revprof_revgr_id
313 ORDER BY evaluation_sequence;
314
315 l_appl_rev_prof_evaluator_rec c_appl_rev_prof_evaluator%ROWTYPE;
316
317 CURSOR c_seq_conc_ind (cp_appl_rev_profile_id igs_ad_apl_rev_prf_all.APPL_REV_PROFILE_ID%TYPE) IS
318 SELECT sequential_concurrent_ind
319 FROM igs_ad_apl_rev_prf_all
320 WHERE APPL_REV_PROFILE_ID = cp_appl_rev_profile_id;
321
322 l_seq_conc_ind igs_ad_apl_rev_prf_all.sequential_concurrent_ind%TYPE;
323
324 CURSOR c_arp_rate_scale(cp_appl_rev_profile_id number) IS
325 SELECT *
326 FROM igs_ad_apl_rvpf_rsl arr
327 WHERE arr.appl_rev_profile_id = cp_appl_rev_profile_id;
328
329 arp_rate_scale_rec c_arp_rate_scale%ROWTYPE;
330
331 CURSOR c_igs_ad_appl_eval_s
332 IS
333 SELECT Igs_ad_appl_eval_s.nextval
334 FROM DUAL;
335
336 -- Cursor fetching the review_profile_name for the corresponding review profile id (rghosh bug#2871426)
337 CURSOR c_appl_rev_profile (cp_appl_rev_profile_id igs_ad_apl_rev_prf.appl_rev_profile_id%TYPE) IS
338 SELECT review_profile_name
339 FROM igs_ad_apl_rev_prf
340 WHERE appl_rev_profile_id = cp_appl_rev_profile_id;
341
342 l_review_profile_name igs_ad_apl_rev_prf.review_profile_name%TYPE;
343
344
345 -- Cursor to get the existing evaluators (rghosh bug #2986802)
346 CURSOR c_existing_evaluators (cp_person_id igs_ad_appl_eval.person_id%TYPE,
347 cp_admission_appl_number igs_ad_appl_eval.admission_appl_number%TYPE,
348 cp_nominated_course_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
349 cp_sequence_number igs_ad_appl_eval.sequence_number%TYPE ) IS
353 AND admission_appl_number = cp_admission_appl_number
350 SELECT *
351 FROM igs_ad_appl_eval_v
352 WHERE person_id = cp_person_id
354 AND nominated_course_cd = cp_nominated_course_cd
355 AND sequence_number = cp_sequence_number;
356
357 l_existing_evaluators_rec c_existing_evaluators%ROWTYPE;
358
359 -- Cursor to fetch the next evaluation sequence that is available for that particular application instance (rghosh bug #2986802)
360 CURSOR c_max_evaluation_sequence(cp_person_id igs_ad_appl_eval.person_id%TYPE,
361 cp_admission_appl_number igs_ad_appl_eval.admission_appl_number%TYPE,
362 cp_nominated_course_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
363 cp_sequence_number igs_ad_appl_eval.sequence_number%TYPE) IS
364 SELECT (max(evaluation_sequence))
365 FROM igs_ad_appl_eval
366 WHERE person_id = cp_person_id
367 AND admission_appl_number = cp_admission_appl_number
368 AND nominated_course_cd = cp_nominated_course_cd
369 AND sequence_number = cp_sequence_number;
370
371 l_max_evaluation_sequence NUMBER;
372
373 TYPE l_evalexist_record IS RECORD (
374 person_number igs_ad_rvgr_evaltr.person_number%TYPE,
375 evaluation_sequence igs_ad_rvgr_evaltr.evaluation_sequence%TYPE);
376
377 TYPE l_evalexist_table IS TABLE OF l_evalexist_record INDEX BY BINARY_INTEGER;
378
379 l_eval_exists l_evalexist_table;
380
381 x NUMBER := 0;
382
383 TYPE revgrp_list IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
384 my_revgrp_list revgrp_list;
385
386 l_rating_exists VARCHAR2(1);
387 l_evaluators_exists VARCHAR2(1);
388 l_matching_ind VARCHAR2(1);
389
390 lv_rowid VARCHAR2(25);
391 l_igs_ad_appl_eval_s NUMBER;
392 l_sequence_number NUMBER:= 0;
393 i NUMBER:= 0;
394 l_exists VARCHAR2(1);
395 l_list_count BINARY_INTEGER := 0 ;
396 j BINARY_INTEGER := 0;
397 k BINARY_INTEGER := 0;
398
399 l_person_id NUMBER;
400 l_person_name VARCHAR2(320);
401 l_full_name VARCHAR2(1000);
402 l_display_name VARCHAR2(360);
403
404
405 BEGIN
406
407 l_exists := 'N' ;
408
409 IF p_appl_rev_profile_id IS NULL OR
410 p_appl_revprof_revgr_id IS NULL OR
411 p_person_id IS NULL OR
412 p_admission_appl_number IS NULL OR
413 p_nominated_course_cd IS NULL OR
414 p_sequence_number IS NULL
415 THEN
416 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_COMBI_OF_PARAMS');
417 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
418 RAISE INVALID_PARAMETER;
419 END IF;
420
421 Retcode := 0;
422
423 OPEN c_seq_conc_ind(p_appl_rev_profile_id);
424 FETCH c_seq_conc_ind INTO l_seq_conc_ind;
425 CLOSE c_seq_conc_ind;
426
427 l_rating_exists := 'N';
428
429 l_evaluators_exists := 'N';
430
431 IF l_seq_conc_ind = 'S' THEN -- Sequential Evaluation.
432
433 FOR l_existing_evaluators_rec IN c_existing_evaluators (p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number)
434 LOOP
435 l_eval_exists(x).person_number := l_existing_evaluators_rec.person_number;
436 l_eval_exists(x).evaluation_sequence := l_existing_evaluators_rec.evaluation_sequence;
437 l_evaluators_exists := 'Y';
438 x := x+1;
439 END LOOP;
440 END IF;
441
442 FOR l_appl_rev_prof_evaluator_rec IN c_appl_rev_prof_evaluator(p_appl_revprof_revgr_id)
443 LOOP
444
445 IF l_seq_conc_ind = 'S' THEN -- Sequential Evaluation.
446
447 l_matching_ind := 'N';
448
449 IF l_evaluators_exists = 'Y' THEN
450 FOR y IN 0..(l_eval_exists.count-1)
451 LOOP
452 IF l_appl_rev_prof_evaluator_rec.person_number = l_eval_exists(y).person_number THEN
453 l_sequence_number := l_eval_exists(y).evaluation_sequence;
454 l_matching_ind := 'Y';
455 EXIT;
456 END IF;
457 END LOOP;
458 END IF;
459
460 IF l_evaluators_exists = 'Y' AND l_matching_ind = 'N' THEN
461 OPEN c_max_evaluation_sequence(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number);
462 FETCH c_max_evaluation_sequence INTO l_max_evaluation_sequence;
463 CLOSE c_max_evaluation_sequence;
464 l_sequence_number := l_max_evaluation_sequence + 1;
465 ELSIF l_evaluators_exists = 'N' THEN
466 l_sequence_number := l_sequence_number + 1;
467 END IF;
468
469 ELSE -- Concurrent Evaluation.
470 l_sequence_number := 1;
471 END IF;
472
473 OPEN c_arp_rate_scale(p_appl_rev_profile_id);
474 LOOP -- Loop through all the Rating Scales of that Application Review Profile.
475 FETCH c_arp_rate_scale INTO arp_rate_scale_rec;
476 EXIT WHEN c_arp_rate_scale%NOTFOUND;
477
478 -- Get the next sequence value for table igs_ad_appl_eval.
479 OPEN c_igs_ad_appl_eval_s;
480 FETCH c_igs_ad_appl_eval_s INTO l_igs_ad_appl_eval_s;
481 CLOSE c_igs_ad_appl_eval_s;
482 l_rating_exists := 'Y';
483 Igs_ad_appl_eval_pkg.insert_row(
484 x_rowid => lv_rowid,
488 x_nominated_course_cd => p_nominated_course_cd,
485 x_appl_eval_id => l_igs_ad_appl_eval_s,
486 x_person_id => p_person_id,
487 x_admission_appl_number => p_admission_appl_number,
489 x_sequence_number=> p_sequence_number,
490 x_evaluator_id => l_appl_rev_prof_evaluator_rec.person_id,
491 x_assign_type => 'M',
492 x_assign_date => SYSDATE,
493 x_evaluation_date => NULL,
494 x_rating_type_id => arp_rate_scale_rec.rating_type_id,
495 x_rating_values_id => NULL,
496 x_rating_notes => NULL,
497 x_evaluation_sequence => l_sequence_number,
498 x_rating_scale_id => arp_rate_scale_rec.rating_scale_id,
499 x_closed_ind => 'N'
500 );
501
502 /*********** New Feature ****** For Sending notification to Evaluators: bug 2864696 *************/
503
504 -- fnd_file.put_line(fnd_file.log,'Value of seq ind is '|| l_seq_conc_ind);
505
506 IF l_seq_conc_ind = 'S' and l_sequence_number = 1 THEN
507
508 IF my_revgrp_list.count = 0 THEN
509 my_revgrp_list(0) := l_appl_rev_prof_evaluator_rec.person_id;
510 END IF;
511
512 ELSIF l_seq_conc_ind = 'C' THEN
513
514 l_exists := 'N';
515
516 FOR j IN 0..(my_revgrp_list.COUNT-1)
517 LOOP
518 IF my_revgrp_list(j) = l_appl_rev_prof_evaluator_rec.person_id THEN
519 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Record already present in table');
520 l_exists := 'Y';
521 EXIT;
522 END IF;
523 END LOOP;
524
525 IF l_exists = 'N' THEN
526 my_revgrp_list(my_revgrp_list.COUNT) := l_appl_rev_prof_evaluator_rec.person_id;
527 END IF;
528
529 END IF;
530 /*****************************************/
531
532 END LOOP; -- End of looping through all the Rating Scales of that Application Review Profile.
533 CLOSE c_arp_rate_scale;
534
535 OPEN c_appl_rev_profile(p_appl_rev_profile_id);
536 FETCH c_appl_rev_profile INTO l_review_profile_name;
537 CLOSE c_appl_rev_profile;
538
539 IF l_rating_exists = 'N' THEN
540 FND_MESSAGE.SET_NAME('IGS','IGS_AD_MISS_RS_SETUP');
541 FND_MESSAGE.SET_TOKEN ('REVIEW_PROFILE', l_review_profile_name);
542 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
543 EXIT;
544 END IF;
545
546 END LOOP; -- End of looping through all the evaluators of that Review Profile Group.
547
548 /**************************************/
549 FOR k IN 0..(my_revgrp_list.COUNT-1)
550
551 LOOP
552 l_person_id := NVL(my_revgrp_list(k),0);
553
554 -- FND_FILE.PUT_LINE (FND_FILE.LOG, 'eval is '|| l_person_id );
555
556 Wf_Directory.GetRoleName('HZ_PARTY', l_person_id, l_person_name, l_full_name);
557
558 IF l_person_name IS NOT NULL THEN
559
560 /* Evaluators are being printed without any application context information.
561 Hence commenting out the following as part of fix for bug# 3224891
562
563 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
564 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
565 FND_MESSAGE.SET_TOKEN ('PNAME', l_full_name);
566 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET()); */
567
568 Wf_Inform_Evaluator_Appl (l_person_id, l_person_name, l_full_name);
569
570 /* Evaluators are being printed without any application context information.
571 Hence commenting out the following as part of fix for bug# 3224891
572
573 ELSE
574 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
575 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET()); */
576
577 END IF ; -- l_person_name
578 END LOOP;
579 /**************************************/
580
581 EXCEPTION
582 WHEN INVALID_PARAMETER THEN
583 Retcode := 2;
584 WHEN OTHERS THEN
585 fnd_file.put_line(fnd_file.log,sqlerrm(sqlcode));
586 Retcode := 2;
587 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
588 FND_MESSAGE.SET_TOKEN('NAME','Assign_Eval_To_Ai');
589 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
590 Igs_Ge_Msg_Stack.add;
591
592 END Assign_Eval_To_Ai;
593
594
595
596 PROCEDURE Wf_Inform_Evaluator_Appl
597 ( p_evaluator_id IN NUMBER,
598 p_evaluator_name IN VARCHAR2,
599 p_evaluator_full_name IN VARCHAR2
600 )
601 IS
602
603 l_event_t wf_event_t;
604 l_parameter_list_t wf_parameter_list_t;
605 l_itemKey varchar2(100);
606 l_incomplt_appl_url varchar2(1000);
607
608
609 CURSOR cur_seq IS
610 SELECT IGS_AD_WF_EVAL_S.NEXTVAL
611 FROM dual;
612
613
614 BEGIN
615
616 -- initialize the wf_event_t object
620 OPEN cur_seq ;
617 --
618 wf_event_t.Initialize(l_event_t);
619
621 FETCH cur_seq INTO l_itemKey ;
622 CLOSE cur_seq ;
623
624
625 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_ID',
626 p_Value => p_evaluator_id,
627 p_parameterlist=>l_parameter_list_t);
628 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_NAME',
629 p_Value => p_evaluator_name,
630 p_parameterlist=>l_parameter_list_t);
631 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_FULL_NAME',
632 p_Value => p_evaluator_full_name,
633 p_parameterlist=>l_parameter_list_t);
634 --
635 -- raise the event
636 --
637 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.appl.eval_appl',
638 p_event_key => l_itemKey,
639 p_parameters => l_parameter_list_t);
640
641 l_parameter_list_t.delete;
642 EXCEPTION
643 WHEN OTHERS THEN
644 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
645
646 END Wf_Inform_Evaluator_Appl;
647
648 /*************************************/
649
650 Function Calc_Ratstat(
651 p_person_id IN igs_ad_ps_appl_inst_all.person_id%TYPE,
652 p_admission_appl_number IN igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
653 p_nominated_course_cd IN igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
654 p_sequence_number IN igs_ad_ps_appl_inst_all.sequence_number%TYPE,
655 p_faculty_id IN igs_ad_appl_eval.evaluator_id%TYPE,
656 p_roletype IN VARCHAR2,
657 p_eval_type IN VARCHAR2,
658 p_eval_seq_number IN NUMBER)
659 RETURN VARCHAR2 IS
660
661 l_no_of_evaluators NUMBER := 0;
662 l_count NUMBER := 0;
663 l_prev_count NUMBER := 0;
664 l_prev_seq_number NUMBER := 0;
665
666 BEGIN
667
668 IF p_roletype = 'ADMIN' THEN
669
670 Select count(rowid)
671 Into l_no_of_evaluators
672 From igs_ad_appl_eval
673 Where person_id = p_person_id
674 And Admission_appl_number = p_admission_appl_number
675 And Nominated_course_cd = p_nominated_course_cd
676 And sequence_number = p_sequence_number;
677
678 Select count(rowid)
679 Into l_count
680 From igs_ad_appl_eval
681 Where person_id = p_person_id
682 And Admission_appl_number = p_admission_appl_number
683 And Nominated_course_cd = p_nominated_course_cd
684 And sequence_number = p_sequence_number
685 And rating_type_id is not null
686 And rating_values_id is not null
687 And evaluation_date is not null
688 And rating_scale_id is not null;
689
690 IF l_count = l_no_of_evaluators AND l_count <> 0 THEN
691 RETURN 'R';
692
693 ELSIF l_count = l_no_of_evaluators and l_count = 0 THEN
694 RETURN 'U';
695
696 ELSE
697 RETURN 'N';
698
699 END IF;
700
701 ELSIF p_roletype = 'FACULTY' THEN
702
703 Select count(rowid)
704 Into l_no_of_evaluators
705 From igs_ad_appl_eval
706 Where person_id = p_person_id
707 And Admission_appl_number = p_admission_appl_number
708 And Nominated_course_cd = p_nominated_course_cd
709 And sequence_number = p_sequence_number
710 AND EVALUATOR_ID = p_faculty_id;
711
712 Select count(rowid)
713 Into l_count
714 From igs_ad_appl_eval
715 Where person_id = p_person_id
716 And Admission_appl_number = p_admission_appl_number
717 And Nominated_course_cd = p_nominated_course_cd
718 And sequence_number = p_sequence_number
719 And rating_type_id is not null
720 And rating_values_id is not null
721 And evaluation_date is not null
722 And rating_scale_id is not null
723 And evaluator_id = p_faculty_id;
724
725 l_prev_seq_number := igs_ad_appl_eval_pkg.find_prev_seq_number(p_person_id,
726 p_admission_appl_number,
727 p_nominated_course_cd,
728 p_sequence_number,
729 p_eval_seq_number);
730 Select count(rowid)
731 Into l_prev_count
732 From igs_ad_appl_eval
733 Where person_id = p_person_id
734 And Admission_appl_number = p_admission_appl_number
735 And Nominated_course_cd = p_nominated_course_cd
736 And sequence_number = p_sequence_number
737 And evaluation_sequence = l_prev_seq_number
738 And rating_values_id is null
739 And evaluation_date is null;
740
741 IF p_eval_type = 'S' THEN
742
743 IF l_no_of_evaluators = 0 THEN
744 RETURN 'U';
745
746 ELSIF l_count = l_no_of_evaluators and l_count <> 0 then
747 RETURN 'R';
748
749 ELSIF l_prev_count <> 0 and p_eval_seq_number <> 1 Then
750 RETURN 'D';
751
752 ELSE
753 RETURN 'N';
754
755 END IF;
756
757 ELSIF (p_eval_type = 'C' OR p_eval_type IS NULL) THEN
758 IF l_no_of_evaluators = 0 THEN
759 RETURN 'U';
760
761 ELSIF l_count = l_no_of_evaluators and l_count <> 0 then
762 RETURN 'R';
763
764 ELSE
765 RETURN 'N';
766
767 END IF;
768
769 END IF;
770
771 END IF;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
776
777 END Calc_Ratstat;
778
779 --------------------------------
783 l_parameter_list wf_parameter_list_t;
780 FUNCTION rule_function (p_subscription in RAW,
781 p_event in out NOCOPY WF_EVENT_T) return varchar2 is
782 l_rule VARCHAR2(20);
784 l_parameter_t wf_parameter_t;
785 i_parameter_name l_parameter_t.name%type;
786 i_parameter_value l_parameter_t.value%type;
787 i pls_integer;
788
789
790 BEGIN
791
792 l_parameter_list := wf_parameter_list_t();
793 l_parameter_t := wf_parameter_t(null, null);
794
795 l_parameter_list := p_event.getParameterList();
796 if l_parameter_list is not null
797 then
798 i := l_parameter_list.FIRST;
799 while ( i <= l_parameter_list.LAST )
800 loop
801 i_parameter_name := null;
802 i_parameter_value := null;
803
804 i_parameter_name := l_parameter_list(i).getName();
805 i_parameter_value := l_parameter_list(i).getValue();
806
807 i := l_parameter_list.NEXT(i);
808 end loop;
809
810 end if;
811
812
813 l_rule := wf_rule.default_rule(p_subscription,p_event);
814
815 return ('SUCCESS');
816
817 END rule_function;
818
819
820 /************************/
821
822 -- this procedure will set the package variable g_dns_ind according to the value of the checkbox do not send notification in ratings forms(IGSAD090)
823 -- rghosh (bug # 2871426 - Evaluator entry and assignment
824 PROCEDURE set_dns_ind (x_do_not_send_notif IN VARCHAR2) IS
825 BEGIN
826 IF NVL(x_do_not_send_notif,'N') = 'N' THEN
827 igs_ad_appl_eval_pkg.g_dns_ind := 'N';
828 ELSE
829 igs_ad_appl_eval_pkg.g_dns_ind := 'Y';
830 END IF;
831 END set_dns_ind;
832
833 --this function will return the value of the next sequence number that has to be assigned to new evaluator who is added manually
834 -- rghosh (bug#2871426 - Evaluator entry and assignment)
835 FUNCTION set_eval_sequence (p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
836 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
837 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
838 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
839 p_evaluator_id igs_ad_appl_eval.evaluator_id%TYPE,
840 p_rating_type_id igs_ad_appl_eval.rating_type_id%TYPE,
841 p_rating_scale_id igs_ad_appl_eval.rating_scale_id%TYPE ) RETURN NUMBER IS
842
843 CURSOR eval_type_cur( cp_person_id igs_ad_appl_arp.person_id%TYPE,
844 cp_admission_appl_number igs_ad_appl_arp.admission_appl_number%TYPE,
845 cp_nominated_course_cd igs_ad_appl_arp.nominated_course_cd%TYPE,
846 cp_sequence_number igs_ad_appl_arp.sequence_number%TYPE) IS
847 SELECT
848 distinct sequential_concurrent_ind
849 FROM
850 igs_ad_apl_rev_prf_all
851 WHERE
852 appl_rev_profile_id = (select appl_rev_profile_id
853 from igs_ad_appl_arp
854 where person_id = cp_person_id
855 and admission_appl_number = cp_admission_appl_number
856 and nominated_course_cd = cp_nominated_course_cd
857 and sequence_number = cp_sequence_number);
858
859 CURSOR c_max_evaluation_sequence(
860 cp_person_id igs_ad_appl_eval.person_id%TYPE,
861 cp_adm_apl_num igs_ad_appl_eval.admission_appl_number%TYPE,
862 cp_nom_crs_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
863 cp_seq_number igs_ad_appl_eval.sequence_number%TYPE) IS
864 SELECT
865 (max(evaluation_sequence)+1)
866 FROM
867 igs_ad_appl_eval
868 WHERE
869 person_id = cp_person_id AND
870 admission_appl_number = cp_adm_apl_num AND
871 nominated_course_cd = cp_nom_crs_cd AND
872 sequence_number = cp_seq_number;
873
874 CURSOR c_next_seq (
875 cp_person_id igs_ad_appl_eval.person_id%TYPE,
876 cp_adm_apl_num igs_ad_appl_eval.admission_appl_number%TYPE,
877 cp_nom_crs_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
878 cp_seq_number igs_ad_appl_eval.sequence_number%TYPE,
879 cp_evaluator_id igs_ad_appl_eval.evaluator_id%TYPE) IS
880 SELECT *
881 FROM igs_ad_appl_eval
882 WHERE person_id = cp_person_id
883 AND admission_appl_number = cp_adm_apl_num
884 AND nominated_course_cd = cp_nom_crs_cd
885 AND sequence_number = cp_seq_number
886 AND evaluator_id = cp_evaluator_id;
887
888 l_next_seq c_next_seq%ROWTYPE;
889 l_exist_eval_type igs_ad_apl_rev_prf_all.sequential_concurrent_ind%TYPE;
890 l_exist_arp_id igs_ad_appl_arp_v.appl_rev_profile_id%TYPE;
891 l_count igs_ad_appl_eval.evaluation_sequence%TYPE;
892 l_max_evaluation_sequence igs_ad_appl_eval.evaluation_sequence%TYPE;
893 l_chk_rating_val_null VARCHAR2(1);
894
895 BEGIN
896 OPEN eval_type_cur(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number);
897 FETCH eval_type_cur INTO l_exist_eval_type;
898 CLOSE eval_type_cur;
899 l_chk_rating_val_null := 'N' ;
900
901 IF l_exist_eval_type = 'S' THEN
902 OPEN c_max_evaluation_sequence(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number);
903 FETCH c_max_evaluation_sequence into l_max_evaluation_sequence;
904 CLOSE c_max_evaluation_sequence;
905
906 FOR l_next_seq IN c_next_seq(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number,
907 p_evaluator_id) LOOP
908 IF l_next_seq.rating_values_id IS NULL AND NVL(l_next_seq.closed_ind,'N') = 'N' THEN
909 IF (p_rating_type_id = l_next_seq.rating_type_id AND p_rating_scale_id = l_next_seq.rating_scale_id ) THEN
910 FND_MESSAGE.SET_NAME('IGS','IGS_AD_MAND_RATING');
911 IGS_GE_MSG_STACK.ADD;
912 APP_EXCEPTION.RAISE_EXCEPTION;
913 END IF;
914 l_chk_rating_val_null := 'Y' ;
915 l_count := l_next_seq.evaluation_sequence;
916 EXIT;
917 END IF;
918 IF l_next_seq.rating_values_id IS NOT NULL AND NVL(l_next_seq.closed_ind,'N') = 'N' THEN
919 IF (p_rating_type_id = l_next_seq.rating_type_id AND p_rating_scale_id = l_next_seq.rating_scale_id ) THEN
920 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACT_EVL_RT_RS');
921 IGS_GE_MSG_STACK.ADD;
922 APP_EXCEPTION.RAISE_EXCEPTION;
923 END IF;
924 END IF;
925 END LOOP;
926 IF l_chk_rating_val_null = 'N' THEN
927 IF l_max_evaluation_sequence IS NOT NULL THEN
928 l_count := l_max_evaluation_sequence;
929 ELSE
930 l_count := 1;
931 END IF;
932 END IF;
933
934 ELSIF l_exist_eval_type = 'C' THEN
935 l_count:=1;
936
937 fnd_message.set_name('IGS','IGS_AD_NO_WF_NOTIF');
938 IGS_GE_MSG_STACK.ADD;
939
940 ELSE
941 l_count:=1;
942 END IF;
943 RETURN l_count;
944 END set_eval_sequence;
945
946 END igs_ad_assign_eval_ai_pkg;
[Home] [Help]