1 PACKAGE BODY igs_ad_appl_eval_pkg AS
2 /* $Header: IGSAIA4B.pls 120.4 2006/07/31 13:28:15 rbezawad ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_appl_eval%RowType;
5 new_references igs_ad_appl_eval%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_appl_eval_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_admission_appl_number IN NUMBER DEFAULT NULL,
13 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_sequence_number IN NUMBER DEFAULT NULL,
15 x_evaluator_id IN NUMBER DEFAULT NULL,
16 x_assign_type IN VARCHAR2 DEFAULT NULL,
17 x_assign_date IN DATE DEFAULT NULL,
18 x_evaluation_date IN DATE DEFAULT NULL,
19 x_rating_type_id IN NUMBER DEFAULT NULL,
20 x_rating_values_id IN NUMBER DEFAULT NULL,
21 x_rating_notes IN VARCHAR2 DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL,
27 x_evaluation_sequence IN NUMBER DEFAULT NULL,
28 x_rating_scale_id IN NUMBER DEFAULT NULL,
29 x_closed_ind IN VARCHAR2
30 ) AS
31
32 /*************************************************************
33 Created By :
34 Date Created By :
35 Purpose :
36 Know limitations, enhancements or remarks
37 Change History
38 Who When What
39
40 (reverse chronological order - newest change first)
41 ***************************************************************/
42
43 CURSOR cur_old_ref_values IS
44 SELECT *
45 FROM IGS_AD_APPL_EVAL
46 WHERE rowid = x_rowid;
47
48 BEGIN
49
50 l_rowid := x_rowid;
51
52 -- Code for setting the Old and New Reference Values.
53 -- Populate Old Values.
54 Open cur_old_ref_values;
55 Fetch cur_old_ref_values INTO old_references;
56 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
57 Close cur_old_ref_values;
58 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 Return;
62 END IF;
63 Close cur_old_ref_values;
64
65 -- Populate New Values.
66 new_references.appl_eval_id := x_appl_eval_id;
67 new_references.person_id := x_person_id;
68 new_references.admission_appl_number := x_admission_appl_number;
69 new_references.nominated_course_cd := x_nominated_course_cd;
70 new_references.sequence_number := x_sequence_number;
71 new_references.evaluator_id := x_evaluator_id;
72 new_references.assign_type := x_assign_type;
73 new_references.assign_date := TRUNC(x_assign_date);
74 new_references.evaluation_date := TRUNC(x_evaluation_date);
75 new_references.rating_type_id := x_rating_type_id;
76 new_references.rating_values_id := x_rating_values_id;
77 new_references.rating_notes := x_rating_notes;
78 new_references.evaluation_sequence := x_evaluation_sequence;
79 new_references.rating_scale_id := x_rating_scale_id;
80 new_references.closed_ind := x_closed_ind;
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88 new_references.last_update_date := x_last_update_date;
89 new_references.last_updated_by := x_last_updated_by;
90 new_references.last_update_login := x_last_update_login;
91
92 END Set_Column_Values;
93
94 PROCEDURE Check_Constraints (
95 Column_Name IN VARCHAR2 DEFAULT NULL,
96 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
97 /*************************************************************
98 Created By :
99 Date Created By :
100 Purpose :
101 Know limitations, enhancements or remarks
102 Change History
103 Who When What
104
105 (reverse chronological order - newest change first)
106 ***************************************************************/
107
108 BEGIN
109
110 IF column_name IS NULL THEN
111 NULL;
112 ELSIF UPPER(column_name) = 'ASSIGN_TYPE' THEN
113 new_references.assign_type := column_value;
114 NULL;
115 END IF;
116
117
118
119 -- The following code checks for check constraints on the Columns.
120 IF Upper(Column_Name) = 'ASSIGN_TYPE' OR
121 Column_Name IS NULL THEN
122 IF NOT (new_references.assign_type IN ('M','A')) THEN
123 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 END IF;
128
129
130 END Check_Constraints;
131
132 PROCEDURE Check_Parent_Existance AS
133 /*************************************************************
134 Created By :
135 Date Created By :
136 Purpose :
137 Know limitations, enhancements or remarks
138 Change History
139 Who When What
140
141 (reverse chronological order - newest change first)
142 ***************************************************************/
143
144 BEGIN
145
146 IF (((old_references.rating_values_id = new_references.rating_values_id)) OR
147 ((new_references.rating_values_id IS NULL))) THEN
148 NULL;
149 ELSIF NOT Igs_Ad_Rs_Values_Pkg.Get_PK_For_Validation (
150 new_references.rating_values_id ,
151 'N' ) THEN
152 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156
157 IF (((old_references.rating_type_id = new_references.rating_type_id)) OR
158 ((new_references.rating_type_id IS NULL))) THEN
159 NULL;
160 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
161 new_references.rating_type_id,
162 'RATING_TYPE',
163 'N' ) THEN
164 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168
169 IF (((old_references.person_id = new_references.person_id) AND
170 (old_references.admission_appl_number = new_references.admission_appl_number) AND
171 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
172 (old_references.sequence_number = new_references.sequence_number)) OR
173 ((new_references.person_id IS NULL) OR
174 (new_references.admission_appl_number IS NULL) OR
175 (new_references.nominated_course_cd IS NULL) OR
176 (new_references.sequence_number IS NULL))) THEN
177 NULL;
178 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PKNolock_For_Validation ( -- changed the function call from Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation to
179 new_references.person_id, -- Igs_Ad_Ps_Appl_Inst_Pkg.Get_PKNolock_For_Validation (For Bug 2760811 - ADCR061
180 new_references.admission_appl_number, -- locking issues -- rghosh )
181 new_references.nominated_course_cd,
182 new_references.sequence_number
183 ) THEN
184 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188
189 IF (((old_references.evaluator_id = new_references.evaluator_id)) OR
190 ((new_references.evaluator_id IS NULL))) THEN
191 NULL;
192 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
193 new_references.evaluator_id
194 ) THEN
195 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198 END IF;
199
200 END Check_Parent_Existance;
201
202 FUNCTION Get_PK_For_Validation (
203 x_appl_eval_id IN NUMBER,
204 x_closed_ind IN VARCHAR2
205 ) RETURN BOOLEAN AS
206
207 /*************************************************************
208 Created By :
209 Date Created By :
210 Purpose :
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214
215 (reverse chronological order - newest change first)
216 ***************************************************************/
217
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_ad_appl_eval
221 WHERE appl_eval_id = x_appl_eval_id AND
222 NVL(closed_ind,'N') = DECODE(closed_ind,NULL,'N',NVL(x_closed_ind,closed_ind))
223 FOR UPDATE NOWAIT;
224
225 lv_rowid cur_rowid%RowType;
226
227 BEGIN
228
229 Open cur_rowid;
230 Fetch cur_rowid INTO lv_rowid;
231 IF (cur_rowid%FOUND) THEN
232 Close cur_rowid;
233 Return(TRUE);
234 ELSE
235 Close cur_rowid;
236 Return(FALSE);
237 END IF;
238 END Get_PK_For_Validation;
239
240 PROCEDURE Get_FK_Igs_Ad_Rs_Values (
241 x_rating_values_id IN NUMBER
242 ) AS
243
244 /*************************************************************
245 Created By :
246 Date Created By :
247 Purpose :
248 Know limitations, enhancements or remarks
249 Change History
250 Who When What
251
252 (reverse chronological order - newest change first)
253 ***************************************************************/
254
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM igs_ad_appl_eval
258 WHERE rating_values_id = x_rating_values_id ;
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 Open cur_rowid;
265 Fetch cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 Close cur_rowid;
268 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAE_ARV_FK');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 Return;
272 END IF;
273 Close cur_rowid;
274
275 END Get_FK_Igs_Ad_Rs_Values;
276
277 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
278 x_code_id IN NUMBER
279 ) AS
280
281 /*************************************************************
282 Created By :
283 Date Created By :
284 Purpose :
285 Know limitations, enhancements or remarks
286 Change History
287 Who When What
288
289 (reverse chronological order - newest change first)
290 ***************************************************************/
291
292 CURSOR cur_rowid IS
293 SELECT rowid
294 FROM igs_ad_appl_eval
295 WHERE rating_type_id = x_code_id ;
296
297 lv_rowid cur_rowid%RowType;
298
299 BEGIN
300
301 Open cur_rowid;
302 Fetch cur_rowid INTO lv_rowid;
303 IF (cur_rowid%FOUND) THEN
304 Close cur_rowid;
305 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAE_ACDC_FK');
306 IGS_GE_MSG_STACK.ADD;
307 App_Exception.Raise_Exception;
308 Return;
309 END IF;
310 Close cur_rowid;
311
312 END Get_FK_Igs_Ad_Code_Classes;
313
314 PROCEDURE Get_FK_Igs_Ad_Ps_Appl_Inst (
315 x_person_id IN NUMBER,
316 x_admission_appl_number IN NUMBER,
317 x_nominated_course_cd IN VARCHAR2,
318 x_sequence_number IN NUMBER
319 ) AS
320
321 /*************************************************************
322 Created By :
323 Date Created By :
324 Purpose :
325 Know limitations, enhancements or remarks
326 Change History
327 Who When What
328
329 (reverse chronological order - newest change first)
330 ***************************************************************/
331
332 CURSOR cur_rowid IS
333 SELECT rowid
334 FROM igs_ad_appl_eval
335 WHERE person_id = x_person_id
336 AND admission_appl_number = x_admission_appl_number
337 AND nominated_course_cd = x_nominated_course_cd
338 AND sequence_number = x_sequence_number ;
339
340 lv_rowid cur_rowid%RowType;
341
342 BEGIN
343
344 Open cur_rowid;
345 Fetch cur_rowid INTO lv_rowid;
346 IF (cur_rowid%FOUND) THEN
347 Close cur_rowid;
348 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAE_ACAI_FK');
349 IGS_GE_MSG_STACK.ADD;
350 App_Exception.Raise_Exception;
351 Return;
352 END IF;
353 Close cur_rowid;
354
355 END Get_FK_Igs_Ad_Ps_Appl_Inst;
356
357 PROCEDURE Get_FK_Igs_Pe_Person (
358 x_person_id IN NUMBER
359 ) AS
360
361 /*************************************************************
362 Created By :
363 Date Created By :
364 Purpose :
365 Know limitations, enhancements or remarks
366 Change History
367 Who When What
368
369 (reverse chronological order - newest change first)
370 ***************************************************************/
371
372 CURSOR cur_rowid IS
373 SELECT rowid
374 FROM igs_ad_appl_eval
375 WHERE evaluator_id = x_person_id ;
376
377 lv_rowid cur_rowid%RowType;
378
379 BEGIN
380
381 Open cur_rowid;
382 Fetch cur_rowid INTO lv_rowid;
383 IF (cur_rowid%FOUND) THEN
384 Close cur_rowid;
385 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAE_PE_FK');
386 IGS_GE_MSG_STACK.ADD;
387 App_Exception.Raise_Exception;
388 Return;
389 END IF;
390 Close cur_rowid;
391
392 END Get_FK_Igs_Pe_Person;
393
394 PROCEDURE Get_FK_Igs_Ad_Rating_Scales (
395 x_rating_scale_id IN NUMBER
396 ) AS
397
398 /*************************************************************
399 Created By : rboddu
400 Date Created By : 16-NOV-2001
401 Purpose :
402 Know limitations, enhancements or remarks
403 Change History
404 Who When What
405
406 (reverse chronological order - newest change first)
407 ***************************************************************/
408
409 CURSOR cur_rowid IS
410 SELECT rowid
411 FROM igs_ad_appl_eval
412 WHERE rating_scale_id = x_rating_scale_id ;
413
414 lv_rowid cur_rowid%RowType;
415
416 BEGIN
417
418 Open cur_rowid;
419 Fetch cur_rowid INTO lv_rowid;
420 IF (cur_rowid%FOUND) THEN
421 Close cur_rowid;
422 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAE_ARS_FK');
423 IGS_GE_MSG_STACK.ADD;
424 App_Exception.Raise_Exception;
425 Return;
426 END IF;
427 Close cur_rowid;
428
429 END Get_FK_Igs_Ad_Rating_Scales;
430
431 PROCEDURE Check_Outcome_Status (
432 x_person_id IN NUMBER,
433 x_admission_appl_number IN NUMBER,
434 x_nominated_course_cd IN VARCHAR2,
435 x_sequence_number IN NUMBER
436 ) IS
437 /*************************************************************
438 Created By : rghosh
439 Date Created By : 20-Feb-2003
440 Purpose : Insert and Update is not allowed if system outcome status is in
441 ('VOIDED','WITHDRAWN','NO-QUOTA','OFFER','OFFER-FUTURE-TERM')
442 or the system outcome status is REJECTED and the
443 req_for_reconsideration_ind is set to 'N'
444 Know limitations, enhancements or remarks
445 Change History
446 Who When What
447
448 (reverse chronological order - newest change first)
449 ***************************************************************/
450 CURSOR c_get_outcome_status ( p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
451 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
452 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
453 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
454 SELECT a.adm_outcome_status, b.req_for_reconsideration_ind
455 FROM igs_ad_ps_appl_inst_all a, igs_ad_ps_appl b
456 WHERE a.person_id = p_person_id
457 AND a.admission_appl_number = p_admission_appl_number
458 AND a.nominated_course_cd = p_nominated_course_cd
459 AND a.sequence_number = p_sequence_number
460 AND a.person_id = b.person_id
461 AND a.admission_appl_number = b.admission_appl_number
462 AND a.nominated_course_cd = b.nominated_course_cd;
463
464 c_get_outcome_status_rec c_get_outcome_status%ROWTYPE;
465
466 BEGIN
467
468 OPEN c_get_outcome_status (
469 x_person_id,
470 x_admission_appl_number,
471 x_nominated_course_cd,
472 x_sequence_number );
473 FETCH c_get_outcome_status INTO c_get_outcome_status_rec;
474 IF IGS_AD_GEN_008.ADMP_GET_SAOS(c_get_outcome_status_rec.adm_outcome_status) IN ('VOIDED','WITHDRAWN','NO-QUOTA','OFFER','OFFER-FUTURE-TERM') THEN
475 Fnd_Message.Set_name('IGS','IGS_AD_NOT_INS_UPD_EVAL_OUT');
476 IGS_GE_MSG_STACK.ADD;
477 CLOSE c_get_outcome_status;
478 App_Exception.Raise_Exception;
479 ELSIF IGS_AD_GEN_008.ADMP_GET_SAOS(c_get_outcome_status_rec.adm_outcome_status) = 'REJECTED' AND c_get_outcome_status_rec.req_for_reconsideration_ind = 'N' THEN
480 Fnd_Message.Set_name('IGS','IGS_AD_NOT_INS_UPD_EVAL_REQ');
481 IGS_GE_MSG_STACK.ADD;
482 CLOSE c_get_outcome_status;
483 App_Exception.Raise_Exception;
484 END IF;
485 CLOSE c_get_outcome_status;
486
487 END Check_Outcome_Status;
488
489
490 PROCEDURE Before_DML (
491 p_action IN VARCHAR2,
492 x_rowid IN VARCHAR2 DEFAULT NULL,
493 x_appl_eval_id IN NUMBER DEFAULT NULL,
494 x_person_id IN NUMBER DEFAULT NULL,
495 x_admission_appl_number IN NUMBER DEFAULT NULL,
496 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
497 x_sequence_number IN NUMBER DEFAULT NULL,
498 x_evaluator_id IN NUMBER DEFAULT NULL,
499 x_assign_type IN VARCHAR2 DEFAULT NULL,
500 x_assign_date IN DATE DEFAULT NULL,
501 x_evaluation_date IN DATE DEFAULT NULL,
502 x_rating_type_id IN NUMBER DEFAULT NULL,
503 x_rating_values_id IN NUMBER DEFAULT NULL,
504 x_rating_notes IN VARCHAR2 DEFAULT NULL,
505 x_creation_date IN DATE DEFAULT NULL,
506 x_created_by IN NUMBER DEFAULT NULL,
507 x_last_update_date IN DATE DEFAULT NULL,
508 x_last_updated_by IN NUMBER DEFAULT NULL,
509 x_last_update_login IN NUMBER DEFAULT NULL,
510 x_evaluation_sequence IN NUMBER DEFAULT NULL,
511 x_rating_scale_id IN NUMBER DEFAULT NULL,
512 x_closed_ind IN VARCHAR2 DEFAULT NULL
513 ) AS
514 /*************************************************************
515 Created By :
516 Date Created By :
517 Purpose :
518 Know limitations, enhancements or remarks
519 Change History
520 Who When What
521
522 (reverse chronological order - newest change first)
523 ***************************************************************/
524
525 BEGIN
526
527 Set_Column_Values (
528 p_action,
529 x_rowid,
530 x_appl_eval_id,
531 x_person_id,
532 x_admission_appl_number,
533 x_nominated_course_cd,
534 x_sequence_number,
535 x_evaluator_id,
536 x_assign_type,
537 x_assign_date,
538 x_evaluation_date,
539 x_rating_type_id,
540 x_rating_values_id,
541 x_rating_notes,
542 x_creation_date,
543 x_created_by,
544 x_last_update_date,
545 x_last_updated_by,
546 x_last_update_login,
547 x_evaluation_sequence,
548 x_rating_scale_id,
549 x_closed_ind
550 );
551
552 igs_ad_gen_002.check_adm_appl_inst_stat(
553 nvl(x_person_id,old_references.person_id),
554 nvl(x_admission_appl_number,old_references.admission_appl_number),
555 nvl(x_nominated_course_cd,old_references.nominated_course_cd),
556 nvl(x_sequence_number,old_references.sequence_number)
557 );
558
559 IF (p_action = 'INSERT') THEN
560 -- Call all the procedures related to Before Insert.
561 Null;
562 IF Get_Pk_For_Validation(
563 new_references.appl_eval_id) THEN
564 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
565 IGS_GE_MSG_STACK.ADD;
566 App_Exception.Raise_Exception;
567 END IF;
568 Check_Constraints;
569 Check_Parent_Existance;
570 Check_Outcome_Status (
571 new_references.person_id,
572 new_references.admission_appl_number,
573 new_references.nominated_course_cd,
574 new_references.sequence_number );
575 ELSIF (p_action = 'UPDATE') THEN
576 -- Call all the procedures related to Before Update.
577 Null;
578 Check_Constraints;
579 Check_Parent_Existance;
580 Check_Outcome_Status (
581 new_references.person_id,
582 new_references.admission_appl_number,
583 new_references.nominated_course_cd,
584 new_references.sequence_number );
585 ELSIF (p_action = 'DELETE') THEN
586 -- Call all the procedures related to Before Delete.
587 Null;
588 ELSIF (p_action = 'VALIDATE_INSERT') THEN
589 -- Call all the procedures related to Before Insert.
590 IF Get_PK_For_Validation (
591 new_references.appl_eval_id) THEN
592 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
593 IGS_GE_MSG_STACK.ADD;
594 App_Exception.Raise_Exception;
595 END IF;
596 Check_Constraints;
597 Check_Outcome_Status (
598 new_references.person_id,
599 new_references.admission_appl_number,
600 new_references.nominated_course_cd,
601 new_references.sequence_number );
602 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
603 Check_Constraints;
604 Check_Outcome_Status (
605 new_references.person_id,
606 new_references.admission_appl_number,
607 new_references.nominated_course_cd,
608 new_references.sequence_number );
609 ELSIF (p_action = 'VALIDATE_DELETE') THEN
610 Null;
611 END IF;
612
613 END Before_DML;
614
615 PROCEDURE After_DML (
616 p_action IN VARCHAR2,
617 x_rowid IN VARCHAR2
618 ) IS
619 /*************************************************************
620 Created By :
621 Date Created By :
622 Purpose :
623 Know limitations, enhancements or remarks
624 Change History
625 Who When What
626
627 (reverse chronological order - newest change first)
628 ***************************************************************/
629
630 BEGIN
631
632 l_rowid := x_rowid;
633
634 IF (p_action = 'INSERT') THEN
635 -- Call all the procedures related to After Insert.
636 Null;
637 ELSIF (p_action = 'UPDATE') THEN
638 -- Call all the procedures related to After Update.
639 Null;
640 ELSIF (p_action = 'DELETE') THEN
641 -- Call all the procedures related to After Delete.
642 Null;
643 END IF;
644
645 l_rowid:=NULL;
646 END After_DML;
647
648 /* New procedure to findout the person id for the next set of evaluators */
649
650 FUNCTION find_next_eval (
651 x_person_id IN NUMBER,
652 x_admission_appl_number IN NUMBER,
653 x_NOMINATED_COURSE_CD IN VARCHAR2,
654 x_SEQUENCE_NUMBER IN NUMBER,
655 x_eval_seq IN NUMBER
656 ) RETURN NUMBER AS
657
658 /* Given an evaluation sequence this function is used to find out the next evaluator person id */
659
660 cursor c_next_eval IS
661 select evaluation_sequence
662 from igs_ad_appl_eval
663 where person_id = x_person_id
664 and admission_appl_number = x_admission_appl_number
665 and nominated_course_cd = x_nominated_course_cd
666 and sequence_number = x_sequence_number
667 and evaluation_sequence > x_eval_seq
668 order by evaluation_sequence;
669
670 cursor c_next_person_id(cp_eval_seq number) is
671 Select
672 distinct EVALUATOR_ID
673 From IGS_AD_APPL_EVAL
674 Where EVALUATION_SEQUENCE = cp_eval_seq
675 and person_id = x_person_id
676 and admission_appl_number = x_admission_appl_number
677 and nominated_course_cd = x_nominated_course_cd
678 and sequence_number = x_sequence_number;
679
680 l_next_eval_seq NUMBER;
681 l_next_person_id NUMBER;
682
683 BEGIN
684 OPEN c_next_eval;
685 FETCH c_next_eval INTO l_next_eval_seq;
686 IF c_next_eval%NOTFOUND THEN
687 CLOSE c_next_eval;
688 RETURN 0;
689 ELSE
690 CLOSE c_next_eval;
691 END IF;
692
693 OPEN c_next_person_id(l_next_eval_seq);
694 FETCH c_next_person_id INTO l_next_person_id;
695 CLOSE c_next_person_id;
696
697 RETURN l_next_person_id;
698 END;
699
700
701
702 /************** New Procedure for sending notification to evaluators: bug 2864696 ******************/
703
704 PROCEDURE wf_evaluator_validation (
705 x_person_id IN NUMBER,
706 x_admission_appl_number IN NUMBER,
707 x_NOMINATED_COURSE_CD IN VARCHAR2,
708 x_SEQUENCE_NUMBER IN NUMBER,
709 x_eval_seq IN NUMBER
710 ) IS
711
712 Cursor c_appl_revprof IS
713 SELECT
714 a.appl_rev_profile_id,
715 a.appl_revprof_revgr_id,
716 r.SEQUENTIAL_CONCURRENT_IND
717 FROM igs_ad_appl_arp a, igs_ad_apl_rev_prf_all r
718 WHERE a.person_id = x_person_id
719 AND a.admission_appl_number = x_admission_appl_number
720 And a.nominated_course_cd = x_nominated_course_cd
721 And a.sequence_number = x_sequence_number
722 AND a.appl_rev_profile_id = r.appl_rev_profile_id;
723
724 Cursor c_eval_num IS
725 SELECT 'X' --bug 3709285 arvsrini
726 FROM IGS_AD_APPL_EVAL
727 WHERE EVALUATION_SEQUENCE = x_eval_seq
728 AND rating_type_ID is NOT NULL
729 AND rating_scale_id IS NOT NULL
730 AND EVALUATION_DATE IS NULL
731 AND rating_values_id IS NULL
732 AND PERSON_ID = x_person_id
733 AND ADMISSION_APPL_NUMBER = x_admission_appl_number
734 AND NOMINATED_COURSE_CD = x_nominated_course_cd
735 AND SEQUENCE_NUMBER = x_sequence_number;
736
737
738 /* SELECT count(rowid)
739 FROM IGS_AD_APPL_EVAL
740 WHERE EVALUATION_SEQUENCE = x_eval_seq
741 AND rating_type_ID is NOT NULL
742 AND rating_scale_id IS NOT NULL
743 AND EVALUATION_DATE IS NOT NULL
744 AND rating_values_id IS NOT NULL;
745 */
746
747
748 l_eval_num NUMBER := 0;
749 l_appl_revprof_id igs_ad_apl_rev_prf_all.appl_rev_profile_id%TYPE;
750 l_appl_revprof_revgr_id igs_ad_appl_arp.APPL_REVPROF_REVGR_ID%TYPE;
751 l_seq_conc_ind igs_ad_apl_rev_prf_all.SEQUENTIAL_CONCURRENT_IND%TYPE;
752
753 l_person_id NUMBER;
754 l_person_name VARCHAR2(320);
755 l_full_name VARCHAR2(1000);
756 l_display_name VARCHAR2(360);
757
758
759 BEGIN
760
761 OPEN c_appl_revprof;
762 FETCH c_appl_revprof INTO l_appl_revprof_id, l_appl_revprof_revgr_id, l_seq_conc_ind;
763 IF c_appl_revprof%NOTFOUND THEN
764 CLOSE c_appl_revprof;
765 Return;
766 ELSE
767 IF l_seq_conc_ind = 'S' THEN
768 IF g_dns_ind = 'N' THEN
769
770 OPEN c_eval_num;
771
772 FETCH c_eval_num INTO l_eval_num;
773 -- IF C_EVAL_NUM%NOTFOUND THEN -- bug 3709285
774 IF C_EVAL_NUM%FOUND THEN -- If there exists any un eveluated record for this sequence number then do not send notification to next evaluator
775 RETURN;
776 ELSE
777
778 /* Added this function call to take care of evaluation sequence gaps caused by delete on this table */
779 l_person_id := find_next_eval(
780 x_person_id,
781 x_admission_appl_number,
782 x_NOMINATED_COURSE_CD,
783 x_SEQUENCE_NUMBER,
784 x_eval_seq );
785
786 /* Select
787 distinct person_id
788 Into l_person_id
789 From IGS_AD_APPL_EVAL
790 Where EVALUATION_SEQUENCE = x_eval_seq + 1; */
791
792 Wf_Directory.GetRoleName('HZ_PARTY', l_person_id, l_person_name, l_full_name);
793
794 IF l_person_name IS NOT NULL THEN
795
796 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
797 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
798 FND_MESSAGE.SET_TOKEN ('PNAME', l_full_name);
799 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
800
801 IGS_AD_ASSIGN_EVAL_AI_PKG.Wf_Inform_Evaluator_Appl (l_person_id, l_person_name,l_full_name);
802
803 ELSE
804 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
805 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
806
807 END IF ; /* l_person_name */
808
809 END IF; /* c_eval_num */
810
811 END IF; /* p_dns_ind */
812
813 END IF; /* seq_conc_ind */
814
815 END IF; /* c_appl_revprof */
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
820
821 IF c_appl_revprof%ISOPEN THEN
822 CLOSE c_appl_revprof;
823 END IF;
824 IF c_eval_num%ISOPEN THEN
825 CLOSE c_eval_num;
826 END IF;
827
828 END wf_evaluator_validation;
829 /***************************/
830
831
832 PROCEDURE insert_row (
833 x_rowid IN OUT NOCOPY VARCHAR2,
834 x_appl_eval_id IN OUT NOCOPY NUMBER,
835 x_person_id IN NUMBER,
836 x_admission_appl_number IN NUMBER,
837 x_nominated_course_cd IN VARCHAR2,
838 x_sequence_number IN NUMBER,
839 x_evaluator_id IN NUMBER,
840 x_assign_type IN VARCHAR2,
841 x_assign_date IN DATE,
842 x_evaluation_date IN DATE,
843 x_rating_type_id IN NUMBER,
844 x_rating_values_id IN NUMBER,
845 x_rating_notes IN VARCHAR2,
846 x_mode IN VARCHAR2,
847 x_evaluation_sequence IN NUMBER DEFAULT NULL,
848 x_rating_scale_id IN NUMBER DEFAULT NULL,
849 x_closed_ind IN VARCHAR2
850 ) AS
851 /*************************************************************
852 Created By :
853 Date Created By :
854 Purpose :
855 Know limitations, enhancements or remarks
856 Change History
857 Who When What
858 ravishar 5/30/2005 Security related changes
859
860 (reverse chronological order - newest change first)
861 ***************************************************************/
862
863 cursor C is select ROWID from IGS_AD_APPL_EVAL
864 where APPL_EVAL_ID= X_APPL_EVAL_ID
865 ;
866 X_LAST_UPDATE_DATE DATE ;
867 X_LAST_UPDATED_BY NUMBER ;
868 X_LAST_UPDATE_LOGIN NUMBER ;
869 X_REQUEST_ID NUMBER;
870 X_PROGRAM_ID NUMBER;
871 X_PROGRAM_APPLICATION_ID NUMBER;
872 X_PROGRAM_UPDATE_DATE DATE;
873 begin
874 X_LAST_UPDATE_DATE := SYSDATE;
875 if(X_MODE = 'I') then
876 X_LAST_UPDATED_BY := 1;
877 X_LAST_UPDATE_LOGIN := 0;
878 elsif (X_MODE IN ('R', 'S')) then
879 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
880 if X_LAST_UPDATED_BY is NULL then
881 X_LAST_UPDATED_BY := -1;
882 end if;
883 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
884 if X_LAST_UPDATE_LOGIN is NULL then
885 X_LAST_UPDATE_LOGIN := -1;
886 end if;
887 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
888 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
889 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
890 if (X_REQUEST_ID = -1) then
891 X_REQUEST_ID := NULL;
892 X_PROGRAM_ID := NULL;
893 X_PROGRAM_APPLICATION_ID := NULL;
894 X_PROGRAM_UPDATE_DATE := NULL;
895 else
896 X_PROGRAM_UPDATE_DATE := SYSDATE;
897 end if;
898 else
899 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
900 IGS_GE_MSG_STACK.ADD;
901 app_exception.raise_exception;
902 end if;
903
904 X_APPL_EVAL_ID := -1;
905 Before_DML(
906 p_action=>'INSERT',
907 x_rowid=>X_ROWID,
908 x_appl_eval_id=>X_APPL_EVAL_ID,
909 x_person_id=>X_PERSON_ID,
910 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
911 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
912 x_sequence_number=>X_SEQUENCE_NUMBER,
913 x_evaluator_id=>X_EVALUATOR_ID,
914 x_assign_type=>X_ASSIGN_TYPE,
915 x_assign_date=>X_ASSIGN_DATE,
916 x_evaluation_date=>X_EVALUATION_DATE,
917 x_rating_type_id=>X_RATING_TYPE_ID,
918 x_rating_values_id=>X_RATING_VALUES_ID,
919 x_rating_notes=>X_RATING_NOTES,
920 x_creation_date=>X_LAST_UPDATE_DATE,
921 x_created_by=>X_LAST_UPDATED_BY,
922 x_last_update_date=>X_LAST_UPDATE_DATE,
923 x_last_updated_by=>X_LAST_UPDATED_BY,
924 x_last_update_login=>X_LAST_UPDATE_LOGIN,
925 x_evaluation_sequence=>X_EVALUATION_SEQUENCE,
926 x_rating_scale_id=>X_RATING_SCALE_ID,
927 x_closed_ind => X_CLOSED_IND);
928 IF (x_mode = 'S') THEN
929 igs_sc_gen_001.set_ctx('R');
930 END IF;
931 INSERT INTO igs_ad_appl_eval (
932 appl_eval_id
933 ,person_id
934 ,admission_appl_number
935 ,nominated_course_cd
936 ,sequence_number
937 ,evaluator_id
938 ,assign_type
939 ,assign_date
940 ,evaluation_date
941 ,rating_type_id
942 ,rating_values_id
943 ,rating_notes
944 ,creation_date
945 ,created_by
946 ,last_update_date
947 ,last_updated_by
948 ,last_update_login
949 ,request_id
950 ,program_id
951 ,program_application_id
952 ,program_update_date
953 ,evaluation_sequence
954 ,rating_scale_id
955 ,closed_ind
956 ) VALUES (
957 igs_ad_appl_eval_s.NEXTVAL
958 ,new_references.person_id
959 ,new_references.admission_appl_number
960 ,new_references.nominated_course_cd
961 ,new_references.sequence_number
962 ,new_references.evaluator_id
963 ,new_references.assign_type
964 ,new_references.assign_date
965 ,new_references.evaluation_date
966 ,new_references.rating_type_id
967 ,new_references.rating_values_id
968 ,new_references.rating_notes
969 ,x_last_update_date
970 ,x_last_updated_by
971 ,x_last_update_date
972 ,x_last_updated_by
973 ,x_last_update_login
974 ,x_request_id
975 ,x_program_id
976 ,x_program_application_id
977 ,x_program_update_date
978 ,x_evaluation_sequence
979 ,x_rating_scale_id
980 ,x_closed_ind
981 )RETURNING appl_eval_id INTO x_appl_eval_id;
982 IF (x_mode = 'S') THEN
983 igs_sc_gen_001.unset_ctx('R');
984 END IF;
985
986 OPEN c;
987 FETCH c INTO x_rowid;
988 IF (c%NOTFOUND) THEN
989 CLOSE c;
990 RAISE no_data_found;
991 END IF;
992 CLOSE c;
993 After_DML (
994 p_action => 'INSERT' ,
995 x_rowid => X_ROWID );
996 EXCEPTION
997 WHEN OTHERS THEN
998 IF (x_mode = 'S') THEN
999 igs_sc_gen_001.unset_ctx('R');
1000 END IF;
1001 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1002 -- Code to handle Security Policy error raised
1003 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1004 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1005 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1006 -- that the ownerof policy function does not have privilege to access.
1007 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1008 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1009 IGS_GE_MSG_STACK.ADD;
1010 app_exception.raise_exception;
1011 ELSE
1012 RAISE;
1013 END IF;
1014 END insert_row;
1015
1016 PROCEDURE lock_row (
1017 x_rowid IN VARCHAR2,
1018 x_appl_eval_id IN NUMBER,
1019 x_person_id IN NUMBER,
1020 x_admission_appl_number IN NUMBER,
1021 x_nominated_course_cd IN VARCHAR2,
1022 x_sequence_number IN NUMBER,
1023 x_evaluator_id IN NUMBER,
1024 x_assign_type IN VARCHAR2,
1025 x_assign_date IN DATE,
1026 x_evaluation_date IN DATE,
1027 x_rating_type_id IN NUMBER,
1028 x_rating_values_id IN NUMBER,
1029 x_rating_notes IN VARCHAR2,
1030 x_evaluation_sequence IN NUMBER,
1031 x_rating_scale_id IN NUMBER,
1032 x_closed_ind IN VARCHAR2
1033 ) AS
1034 /*
1035 || Created By : kamohan
1036 || Created On : 06-FEB-2002
1037 || Purpose : Handles the LOCK mechanism for the table.
1038 || Known limitations, enhancements or remarks :
1039 || Change History :
1040 || Who When What
1041 || (reverse chronological order - newest change first)
1042 */
1043 CURSOR c1 IS
1044 SELECT
1045 person_id,
1046 admission_appl_number,
1047 nominated_course_cd,
1048 sequence_number,
1049 evaluator_id,
1050 assign_type,
1051 assign_date,
1052 evaluation_date,
1053 rating_type_id,
1054 rating_values_id,
1055 rating_notes,
1056 evaluation_sequence,
1057 rating_scale_id,
1058 closed_ind
1059 FROM igs_ad_appl_eval
1060 WHERE rowid = x_rowid
1061 FOR UPDATE NOWAIT;
1062
1063 tlinfo c1%ROWTYPE;
1064
1065 BEGIN
1066
1067 OPEN c1;
1068 FETCH c1 INTO tlinfo;
1069 IF (c1%notfound) THEN
1070 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1071 igs_ge_msg_stack.add;
1072 CLOSE c1;
1073 app_exception.raise_exception;
1074 RETURN;
1075 END IF;
1076 CLOSE c1;
1077
1078 IF (
1079 (tlinfo.person_id = x_person_id)
1080 AND (tlinfo.admission_appl_number = x_admission_appl_number)
1081 AND (tlinfo.nominated_course_cd = x_nominated_course_cd)
1082 AND (tlinfo.sequence_number = x_sequence_number)
1083 AND (tlinfo.evaluator_id = x_evaluator_id)
1084 AND (tlinfo.assign_type = x_assign_type)
1085 AND (trunc(tlinfo.assign_date) = trunc(x_assign_date))
1086 AND ((trunc(tlinfo.evaluation_date) = trunc(x_evaluation_date)) OR ((tlinfo.evaluation_date IS NULL) AND (X_evaluation_date IS NULL)))
1087 AND ((tlinfo.rating_type_id = x_rating_type_id) OR ((tlinfo.rating_type_id IS NULL) AND (X_rating_type_id IS NULL)))
1088 AND ((tlinfo.rating_values_id = x_rating_values_id) OR ((tlinfo.rating_values_id IS NULL) AND (X_rating_values_id IS NULL)))
1089 AND ((tlinfo.rating_notes = x_rating_notes) OR ((tlinfo.rating_notes IS NULL) AND (X_rating_notes IS NULL)))
1090 AND ((tlinfo.evaluation_sequence = x_evaluation_sequence) OR ((tlinfo.evaluation_sequence IS NULL) AND (X_evaluation_sequence IS NULL)))
1091 AND ((tlinfo.rating_scale_id = x_rating_scale_id) OR ((tlinfo.rating_scale_id IS NULL) AND (X_rating_scale_id IS NULL)))
1092 AND ((tlinfo.closed_ind = x_closed_ind) OR ((tlinfo.closed_ind IS NULL) AND (X_closed_ind IS NULL)))
1093 ) THEN
1094 NULL;
1095 ELSE
1096 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1097 igs_ge_msg_stack.add;
1098 app_exception.raise_exception;
1099 END IF;
1100
1101 RETURN;
1102
1103 END lock_row;
1104
1105 PROCEDURE update_row (
1106 x_rowid IN VARCHAR2,
1107 x_appl_eval_id IN NUMBER,
1108 x_person_id IN NUMBER,
1109 x_admission_appl_number IN NUMBER,
1110 x_nominated_course_cd IN VARCHAR2,
1111 x_sequence_number IN NUMBER,
1112 x_evaluator_id IN NUMBER,
1113 x_assign_type IN VARCHAR2,
1114 x_assign_date IN DATE,
1115 x_evaluation_date IN DATE,
1116 x_rating_type_id IN NUMBER,
1117 x_rating_values_id IN NUMBER,
1118 x_rating_notes IN VARCHAR2,
1119 x_mode IN VARCHAR2,
1120 x_evaluation_sequence IN NUMBER DEFAULT NULL,
1121 x_rating_scale_id IN NUMBER DEFAULT NULL,
1122 x_closed_ind IN VARCHAR2
1123 ) AS
1124 /*************************************************************
1125 Created By :
1126 Date Created By :
1127 Purpose :
1128 Know limitations, enhancements or remarks
1129 Change History
1130 Who When What
1131 ravishar 05/27/05 Security related changes
1132
1133 (reverse chronological order - newest change first)
1134 ***************************************************************/
1135
1136 X_LAST_UPDATE_DATE DATE ;
1137 X_LAST_UPDATED_BY NUMBER ;
1138 X_LAST_UPDATE_LOGIN NUMBER ;
1139 X_REQUEST_ID NUMBER;
1140 X_PROGRAM_ID NUMBER;
1141 X_PROGRAM_APPLICATION_ID NUMBER;
1142 X_PROGRAM_UPDATE_DATE DATE;
1143 begin
1144 X_LAST_UPDATE_DATE := SYSDATE;
1145 if(X_MODE = 'I') then
1146 X_LAST_UPDATED_BY := 1;
1147 X_LAST_UPDATE_LOGIN := 0;
1148 elsif (X_MODE IN ('R', 'S')) then
1149 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1150 if X_LAST_UPDATED_BY is NULL then
1151 X_LAST_UPDATED_BY := -1;
1152 end if;
1153 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1154 if X_LAST_UPDATE_LOGIN is NULL then
1155 X_LAST_UPDATE_LOGIN := -1;
1156 end if;
1157 else
1158 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1159 IGS_GE_MSG_STACK.ADD;
1160 app_exception.raise_exception;
1161 end if;
1162 Before_DML(
1163 p_action=>'UPDATE',
1164 x_rowid=>X_ROWID,
1165 x_appl_eval_id=>X_APPL_EVAL_ID,
1166 x_person_id=>X_PERSON_ID,
1167 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
1168 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
1169 x_sequence_number=>X_SEQUENCE_NUMBER,
1170 x_evaluator_id=>X_EVALUATOR_ID,
1171 x_assign_type=>X_ASSIGN_TYPE,
1172 x_assign_date=>X_ASSIGN_DATE,
1173 x_evaluation_date=>X_EVALUATION_DATE,
1174 x_rating_type_id=>X_RATING_TYPE_ID,
1175 x_rating_values_id=>X_RATING_VALUES_ID,
1176 x_rating_notes=>X_RATING_NOTES,
1177 x_creation_date=>X_LAST_UPDATE_DATE,
1178 x_created_by=>X_LAST_UPDATED_BY,
1179 x_last_update_date=>X_LAST_UPDATE_DATE,
1180 x_last_updated_by=>X_LAST_UPDATED_BY,
1181 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1182 x_evaluation_sequence=>X_EVALUATION_SEQUENCE,
1183 x_rating_scale_id=>X_RATING_SCALE_ID,
1184 x_closed_ind => X_CLOSED_IND );
1185
1186 if (X_MODE IN ('R', 'S')) then
1187 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1188 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1189 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1190 if (X_REQUEST_ID = -1) then
1191 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1192 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1193 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1194 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1195 else
1196 X_PROGRAM_UPDATE_DATE := SYSDATE;
1197 end if;
1198 end if;
1199
1200 IF (x_mode = 'S') THEN
1201 igs_sc_gen_001.set_ctx('R');
1202 END IF;
1203 UPDATE igs_ad_appl_eval SET
1204 person_id = new_references.person_id,
1205 admission_appl_number = new_references.admission_appl_number,
1206 nominated_course_cd = new_references.nominated_course_cd,
1207 sequence_number = new_references.sequence_number,
1208 evaluator_id = new_references.evaluator_id,
1209 assign_type = new_references.assign_type,
1210 assign_date = new_references.assign_date,
1211 evaluation_date = new_references.evaluation_date,
1212 rating_type_id = new_references.rating_type_id,
1213 rating_values_id = new_references.rating_values_id,
1214 rating_notes = new_references.rating_notes,
1215 evaluation_sequence = new_references.evaluation_sequence,
1216 rating_scale_id = new_references.rating_scale_id,
1217 last_update_date = x_last_update_date,
1218 last_updated_by = x_last_updated_by,
1219 last_update_login = x_last_update_login,
1220 request_id = x_request_id,
1221 program_id = x_program_id,
1222 program_application_id = program_application_id,
1223 program_update_date = x_program_update_date,
1224 closed_ind = x_closed_ind
1225 WHERE rowid = x_rowid;
1226 IF (sql%NOTFOUND) THEN
1227 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1228 igs_ge_msg_stack.add;
1229 IF (x_mode = 'S') THEN
1230 igs_sc_gen_001.unset_ctx('R');
1231 END IF;
1232 app_exception.raise_exception;
1233 END IF;
1234 IF (x_mode = 'S') THEN
1235 igs_sc_gen_001.unset_ctx('R');
1236 END IF;
1237
1238
1239 After_DML (
1240 p_action => 'UPDATE' ,
1241 x_rowid => X_ROWID
1242 );
1243
1244 /* Added this if clause to take of closed indicator */
1245
1246 IF x_closed_ind = 'N' and old_references.evaluation_date IS NULL and
1247 old_references.rating_values_id IS NULL THEN
1248
1249 wf_evaluator_validation (
1250 x_person_id => x_person_id,
1251 x_admission_appl_number => x_admission_appl_number,
1252 x_NOMINATED_COURSE_CD => x_NOMINATED_COURSE_CD,
1253 x_SEQUENCE_NUMBER => x_SEQUENCE_NUMBER,
1254 x_eval_seq => x_EVALUATION_SEQUENCE
1255 );
1256 END IF;
1257
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 IF (x_mode = 'S') THEN
1261 igs_sc_gen_001.unset_ctx('R');
1262 END IF;
1263 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1264 -- Code to handle Security Policy error raised
1265 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1266 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1267 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1268 -- that the ownerof policy function does not have privilege to access.
1269 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1270 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1271 IGS_GE_MSG_STACK.ADD;
1272 app_exception.raise_exception;
1273 ELSE
1274 RAISE;
1275 END IF;
1276 END update_row;
1277
1278 PROCEDURE add_row (
1279 x_rowid IN OUT NOCOPY VARCHAR2,
1280 x_appl_eval_id IN OUT NOCOPY NUMBER,
1281 x_person_id IN NUMBER,
1282 x_admission_appl_number IN NUMBER,
1283 x_nominated_course_cd IN VARCHAR2,
1284 x_sequence_number IN NUMBER,
1285 x_evaluator_id IN NUMBER,
1286 x_assign_type IN VARCHAR2,
1287 x_assign_date IN DATE,
1288 x_evaluation_date IN DATE,
1289 x_rating_type_id IN NUMBER,
1290 x_rating_values_id IN NUMBER,
1291 x_rating_notes IN VARCHAR2,
1292 x_mode IN VARCHAR2,
1293 x_evaluation_sequence IN NUMBER DEFAULT NULL,
1294 x_rating_scale_id IN NUMBER DEFAULT NULL ,
1295 x_closed_ind IN VARCHAR2
1296 ) AS
1297 /*************************************************************
1298 Created By :
1299 Date Created By :
1300 Purpose :
1301 Know limitations, enhancements or remarks
1302 Change History
1303 Who When What
1304
1305 (reverse chronological order - newest change first)
1306 ***************************************************************/
1307
1308 CURSOR c1 IS
1309 SELECT rowid FROM igs_ad_appl_eval
1310 WHERE appl_eval_id= x_appl_eval_id;
1311
1312 BEGIN
1313 OPEN c1;
1314 FETCH c1 INTO x_rowid;
1315 IF (c1%NOTFOUND) THEN
1316 CLOSE c1;
1317 insert_row (
1318 x_rowid,
1319 x_appl_eval_id,
1320 x_person_id,
1321 x_admission_appl_number,
1322 x_nominated_course_cd,
1323 x_sequence_number,
1324 x_evaluator_id,
1325 x_assign_type,
1326 x_assign_date,
1327 x_evaluation_date,
1328 x_rating_type_id,
1329 x_rating_values_id,
1330 x_rating_notes,
1331 x_mode,
1332 x_evaluation_sequence,
1333 x_rating_scale_id,
1334 x_closed_ind );
1335 RETURN;
1336 END IF;
1337 CLOSE c1;
1338 update_row (
1339 x_rowid,
1340 x_appl_eval_id,
1341 x_person_id,
1342 x_admission_appl_number,
1343 x_nominated_course_cd,
1344 x_sequence_number,
1345 x_evaluator_id,
1346 x_assign_type,
1347 x_assign_date,
1348 x_evaluation_date,
1349 x_rating_type_id,
1350 x_rating_values_id,
1351 x_rating_notes,
1352 x_mode,
1353 x_evaluation_sequence,
1354 x_rating_scale_id,
1355 x_closed_ind );
1356 END add_row;
1357
1358 function find_prev_seq_number(
1359 x_person_id IN NUMBER,
1360 x_admission_appl_number IN NUMBER,
1361 x_NOMINATED_COURSE_CD IN VARCHAR2,
1362 x_SEQUENCE_NUMBER IN NUMBER,
1363 x_eval_seq IN NUMBER
1364 ) RETURN NUMBER AS
1365
1366 /* Given an evaluation sequence this function is used to find out the previous evaluation sequence number */
1367 cursor c_prev_eval IS
1368 select evaluation_sequence
1369 from igs_ad_appl_eval
1370 where person_id = x_person_id
1371 and admission_appl_number = x_admission_appl_number
1372 and nominated_course_cd = x_nominated_course_cd
1373 and sequence_number = x_sequence_number
1374 and evaluation_sequence < x_eval_seq
1375 order by evaluation_sequence desc;
1376 l_prev_eval_seq NUMBER;
1377
1378 BEGIN
1379 OPEN c_prev_eval;
1380 FETCH c_prev_eval INTO l_prev_eval_seq;
1381 IF c_prev_eval%NOTFOUND THEN
1382 CLOSE c_prev_eval;
1383 RETURN 0;
1384 ELSE
1385 CLOSE c_prev_eval;
1386 RETURN l_prev_eval_seq;
1387 END IF;
1388
1389 END;
1390
1391 procedure Notification_On_Delete(
1392 x_person_id IN NUMBER,
1393 x_admission_appl_number IN NUMBER,
1394 x_NOMINATED_COURSE_CD IN VARCHAR2,
1395 x_SEQUENCE_NUMBER IN NUMBER,
1396 x_eval_seq IN NUMBER
1397 ) AS
1398
1399 /* This procedure is used to sent notification to the next personid in the sequence provided all records in the current sequence is del
1400 eted and the evaluation has been completed for the prevous sequence */
1401
1402 /* Cursor to find out if there are still some records for the evaluation sequence that is getting deleted */
1403
1404 cursor c_seq_exists IS
1405 SELECT evaluation_sequence
1406 FROM IGS_AD_APPL_EVAL
1407 WHERE person_id = x_person_id
1408 AND admission_appl_number = x_admission_appl_number
1409 AND nominated_course_cd = x_nominated_course_cd
1410 AND sequence_number = x_sequence_number
1411 and evaluation_sequence = x_eval_seq;
1412
1413 /* Cursor to find out if the previous evaluation sequence has been completed or not */
1414
1415 cursor c_prev_seq_compl (cp_prev_seq_number IN NUMBER) IS
1416 SELECT evaluation_sequence
1417 FROM IGS_AD_APPL_EVAL
1418 WHERE person_id = x_person_id
1419 AND admission_appl_number = x_admission_appl_number
1420 AND nominated_course_cd = x_nominated_course_cd
1421 AND sequence_number = x_sequence_number
1422 and evaluation_sequence = cp_prev_seq_number
1423 and evaluation_date IS NULL
1424 AND rating_values_id IS NULL;
1425
1426 l_seq_exists NUMBER;
1427 l_prev_seq_number NUMBER;
1428 l_prev_seq_compl NUMBER;
1429 l_next_seq_person_id NUMBER;
1430
1431 BEGIN
1432
1433 OPEN c_seq_exists;
1434 FETCH c_seq_exists INTO l_seq_exists;
1435 IF c_seq_exists%FOUND THEN
1436 CLOSE c_seq_exists;
1437 ELSE
1438 CLOSE c_seq_exists;
1439 l_prev_seq_number := find_prev_seq_number (
1440 x_person_id,
1441 x_admission_appl_number,
1442 x_NOMINATED_COURSE_CD,
1443 x_SEQUENCE_NUMBER,
1444 x_eval_seq );
1445 IF l_prev_seq_number > 0 THEN
1446 OPEN c_prev_seq_compl(l_prev_seq_number);
1447 FETCH c_prev_seq_compl INTO l_prev_seq_compl;
1448 IF c_prev_seq_compl%NOTFOUND THEN
1449 close c_prev_seq_compl;
1450 l_next_seq_person_id := find_next_eval(
1451 x_person_id,
1452 x_admission_appl_number,
1453 x_NOMINATED_COURSE_CD,
1454 x_SEQUENCE_NUMBER,
1455 x_eval_seq );
1456 IF l_next_seq_person_id <> 0 THEN
1457
1458 /* send notification to this person */
1459
1460 wf_evaluator_validation (
1461 x_person_id => x_person_id,
1462 x_admission_appl_number => x_admission_appl_number,
1463 x_NOMINATED_COURSE_CD => x_NOMINATED_COURSE_CD,
1464 x_SEQUENCE_NUMBER => x_SEQUENCE_NUMBER,
1465 x_eval_seq => x_EVAL_SEQ
1466 );
1467
1468 END IF;
1469 ELSE
1470 CLOSE c_prev_seq_compl;
1471 END IF;
1472 END IF; /* l_prev_seq_number */
1473 END IF;
1474
1475 END;
1476
1477 PROCEDURE delete_row (
1478 x_rowid IN VARCHAR2,
1479 x_mode IN VARCHAR2
1480 ) AS
1481 /*************************************************************
1482 Created By :
1483 Date Created By :
1484 Purpose :
1485 Know limitations, enhancements or remarks
1486 Change History
1487 Who When What
1488 ravishar 5/30/2005 Security related changes
1489
1490 (reverse chronological order - newest change first)
1491 ***************************************************************/
1492
1493 cursor c_del_appl_eval IS
1494 SELECT person_id,
1495 admission_appl_number,
1496 nominated_course_cd,
1497 sequence_number,
1498 evaluator_id,
1499 evaluation_sequence
1500 FROM igs_ad_appl_eval
1501 WHERE rowid = x_rowid;
1502
1503 l_del_appl_eval c_del_appl_eval%ROWTYPE;
1504 --
1505
1506 BEGIN
1507 Before_DML (
1508 p_action => 'DELETE',
1509 x_rowid => X_ROWID
1510 );
1511
1512 OPEN c_del_appl_eval;
1513 FETCH c_del_appl_eval INTO l_del_appl_eval;
1514 CLOSE c_del_appl_eval;
1515
1516 IF (x_mode = 'S') THEN
1517 igs_sc_gen_001.set_ctx('R');
1518 END IF;
1519 DELETE FROM igs_ad_appl_eval
1520 WHERE rowid = x_rowid;
1521 IF (sql%NOTFOUND) THEN
1522 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1523 igs_ge_msg_stack.add;
1524 IF (x_mode = 'S') THEN
1525 igs_sc_gen_001.unset_ctx('R');
1526 END IF;
1527 app_exception.raise_exception;
1528 END IF;
1529 IF (x_mode = 'S') THEN
1530 igs_sc_gen_001.unset_ctx('R');
1531 END IF;
1532
1533 After_DML (
1534 p_action => 'DELETE',
1535 x_rowid => X_ROWID
1536 );
1537
1538 Notification_On_Delete(
1539 l_del_appl_eval.person_id,
1540 l_del_appl_eval.admission_appl_number,
1541 l_del_appl_eval.NOMINATED_COURSE_CD,
1542 l_del_appl_eval.SEQUENCE_NUMBER,
1543 l_del_appl_eval.evaluation_sequence
1544 );
1545
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 IF (x_mode = 'S') THEN
1549 igs_sc_gen_001.unset_ctx('R');
1550 END IF;
1551 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1552 -- Code to handle Security Policy error raised
1553 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1554 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1555 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1556 -- that the ownerof policy function does not have privilege to access.
1557 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1558 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1559 IGS_GE_MSG_STACK.ADD;
1560 app_exception.raise_exception;
1561 ELSE
1562 RAISE;
1563 END IF;
1564 END delete_row;
1565
1566 END igs_ad_appl_eval_pkg;
[Home] [Help]