1 PACKAGE BODY IGS_AS_VAL_SEI AS
2 /* $Header: IGSAS29B.pls 115.6 2003/05/27 18:45:20 anilk ship $ */
3
4 --
5 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
6 --
7 -- Validate IGS_AS_STD_EXM_INSTN teaching calendar instance
8 FUNCTION ASSP_VAL_SEI_CI(
9 p_cal_type IN VARCHAR2 ,
10 p_ci_sequence_number IN NUMBER ,
11 p_exam_cal_type IN VARCHAR2 ,
12 p_exam_ci_sequence_number IN NUMBER ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN boolean IS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- assp_val_sei_ci
17
18 -- Validate the teaching calendar instance of the IGS_AS_STD_EXM_INSTN record
19 -- being created. Check for, EXIT WHEN condition_is_true ; -- To exit out NOCOPY of
20 -- loop The teaching calendar instance must be a subordinate calendar to
21 -- the specified examination calendar instance.
22 DECLARE
23 BEGIN
24 p_message_name := null;
25
26 -- Check that the teaching calendar is within the examination calendar instance
27 IF IGS_EN_GEN_008.ENRP_GET_WITHIN_CI(
28 p_exam_cal_type,
29 p_exam_ci_sequence_number,
30 p_cal_type,
31 p_ci_sequence_number,
32 FALSE) = FALSE THEN
33 p_message_name := 'IGS_AS_TEACHCAL_SAI_RELATED';
34 RETURN FALSE;
35 END IF;
36
37 RETURN TRUE;
38 END;
39 EXCEPTION
40 WHEN OTHERS THEN
41 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
42 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SEI.assp_val_sei_ci');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 END assp_val_sei_ci;
46 --
47 -- Validate for IGS_AS_STD_EXM_INSTN duplicate within exam period
48 FUNCTION ASSP_VAL_SEI_DPLCT(
49 p_person_id IN NUMBER ,
50 p_course_cd IN VARCHAR2 ,
51 p_unit_cd IN VARCHAR2 ,
52 p_cal_type IN VARCHAR2 ,
53 p_ci_sequence_number IN NUMBER ,
54 p_exam_cal_type IN VARCHAR2 ,
55 p_exam_ci_sequence_number IN NUMBER ,
56 p_dt_alias IN VARCHAR2 ,
57 p_dai_sequence_number IN NUMBER ,
58 p_start_time IN DATE ,
59 p_end_time IN DATE ,
60 p_ass_id IN NUMBER ,
61 p_venue_cd IN VARCHAR2 ,
62 p_message_name OUT NOCOPY VARCHAR2 ,
63 -- anilk, 22-Apr-2003, Bug# 2829262
64 p_uoo_id IN NUMBER )
65 RETURN boolean IS
66 gv_other_detail VARCHAR2(255);
67
68 BEGIN -- assp_val_sei_dplct
69
70 -- Validate that the student examination instance isn?t a duplicate for
71 -- the related IGS_AS_SU_ATMPT_ITM record. A student cannot be
72 -- timetabled more than once within an examination calendar. IGS_GE_NOTE:
73 -- A student can possibly be timetabled in more than one calendar ;
74 -- once for the normal examination and once for a supplementary/special
75 -- examination.
76 DECLARE
77 v_x VARCHAR2(1) DEFAULT NULL;
78 v_ret_val BOOLEAN DEFAULT TRUE;
79
80 CURSOR c_sei IS
81 SELECT 'x'
82 FROM IGS_AS_STD_EXM_INSTN
83 WHERE person_id = p_person_id AND
84 course_cd = p_course_cd AND
85 -- anilk, 22-Apr-2003, Bug# 2829262
86 uoo_id = p_uoo_id AND
87 exam_cal_type = p_exam_cal_type AND
88 exam_ci_sequence_number = p_exam_ci_sequence_number AND
89 ass_id = p_ass_id AND
90 (venue_cd <> p_venue_cd OR
91 dt_alias <> p_dt_alias OR
92 dai_sequence_number <> p_dai_sequence_number OR
93 start_time <> p_start_time OR
94 end_time <> p_end_time);
95 BEGIN
96 p_message_name := null;
97
98 OPEN c_sei;
99 FETCH c_sei INTO v_x;
100
101 -- 1. Search for another timetabled student exam instance in the same
102 -- examination calendar.
103
104 IF (c_sei%FOUND) THEN
105 p_message_name := 'IGS_AS_SAI_ALREADY_SCHEDULED';
106 v_ret_val := FALSE;
107 END IF;
108
109 CLOSE c_sei;
110
111 RETURN v_ret_val;
112 END;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
117 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SEI.assp_val_sei_dplct');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120
121 END assp_val_sei_dplct;
122 --
123 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
124
125 --
126 -- Validate seat not allocated twice within an examination and IGS_GR_VENUE.
127 FUNCTION ASSP_VAL_SEI_SEAT(
128 p_ese_id IN NUMBER ,
129 p_venue_cd IN VARCHAR2 ,
130 p_person_id OUT NOCOPY NUMBER ,
131 p_seat_number OUT NOCOPY NUMBER ,
132 p_message_name OUT NOCOPY VARCHAR2 )
133 RETURN BOOLEAN IS
134 gv_other_detail VARCHAR2(255);
135
136 BEGIN -- assp_val_sei_seat
137 -- Description: This module checks that there does not exist two students
138 -- with the same seat allocation within an exam instance and IGS_GR_VENUE.
139 DECLARE
140
141 v_sei_person_id IGS_AS_STD_EXM_INSTN.person_id%TYPE;
142 v_sei_seat_number IGS_AS_STD_EXM_INSTN.seat_number%TYPE;
143
144 CURSOR c_sei IS
145 SELECT sei.person_id,
146 sei.seat_number
147 FROM IGS_AS_STD_EXM_INSTN sei
148 WHERE sei.ese_id = p_ese_id AND
149 sei.venue_cd = p_venue_cd AND
150 EXISTS(
151 SELECT 'x'
152 FROM IGS_AS_STD_EXM_INSTN sei2
153 WHERE sei2.ese_id = sei.ese_id AND
154 sei2.venue_cd = sei.venue_cd AND
155 sei2.person_id <> sei.person_id AND
156 sei2.seat_number = sei.seat_number);
157
158 BEGIN
159 p_message_name := null;
160
161 OPEN c_sei;
162 FETCH c_sei INTO v_sei_person_id,
163 v_sei_seat_number;
164
165 IF (c_sei%FOUND) THEN
166 CLOSE c_sei;
167 p_person_id := v_sei_person_id;
168 p_seat_number := v_sei_seat_number;
169 p_message_name := 'IGS_AS_SEAT_ALREADY_ALLOCATED';
170 RETURN FALSE;
171 ELSE
172 p_person_id := NULL;
173 p_seat_number := NULL;
174 END IF;
175
176 CLOSE c_sei;
177
178 RETURN TRUE;
179 EXCEPTION
180 WHEN OTHERS THEN
181 IF (c_sei%ISOPEN) THEN
182 CLOSE c_sei;
183 END IF;
184 RAISE;
185 END;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
190 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SEI.assp_val_sei_seat');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193
194 END assp_val_sei_seat;
195 END IGS_AS_VAL_SEI;
[Home] [Help]