1 PACKAGE BODY igs_gr_val_awc AS
2 /* $Header: IGSGR05B.pls 115.5 2004/02/04 04:53:11 kdande ship $ */
3 --
4 -- Validate the award has the correct system award type
5 --
6 FUNCTION grdp_val_award_type(
7 p_award_cd IN VARCHAR2 ,
8 p_s_award_type IN VARCHAR2 ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 BEGIN -- grdp_val_award_type
12 -- Description: Validate the award specified by the award_cd has a
13 -- s_award_type the same as that supplied.
14 DECLARE
15 v_award_rec IGS_PS_AWD.s_award_type%TYPE;
16 v_ret_val BOOLEAN DEFAULT TRUE;
17 cst_course CONSTANT VARCHAR2(6) := 'COURSE';
18 cst_honorary CONSTANT VARCHAR2(8) := 'HONORARY';
19 cst_special CONSTANT VARCHAR2(7) := 'SPECIAL';
20 CURSOR c_award IS
21 SELECT s_award_type
22 FROM IGS_PS_AWD
23 WHERE award_cd = p_award_cd;
24 BEGIN
25 p_message_name := NULL;
26 IF p_award_cd IS NULL OR
27 p_s_award_type IS NULL THEN
28 RETURN TRUE;
29 END IF;
30 OPEN c_award;
31 FETCH c_award INTO v_award_rec;
32 IF (c_award%FOUND) THEN
33 IF p_s_award_type = cst_course AND
34 p_s_award_type <> v_award_rec THEN
35 CLOSE c_award;
36 p_message_name := 'IGS_GR_TYPE_MUST_BE_COURSE';
37 RETURN FALSE;
38 END IF;
39 IF p_s_award_type = cst_honorary AND
40 p_s_award_type <> v_award_rec THEN
41 CLOSE c_award;
42 p_message_name := 'IGS_GR_TYPE_MUST_BE_HNRY';
43 RETURN FALSE;
44 END IF;
45 IF p_s_award_type = cst_special AND
46 (v_award_rec = cst_course OR
47 v_award_rec = cst_honorary) THEN
48 CLOSE c_award;
49 p_message_name := 'IGS_GR_NOT_A_VALID_AWD_TYPE';
50 RETURN FALSE;
51 END IF;
52 END IF;
53 CLOSE c_award;
54 RETURN TRUE;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF (c_award%ISOPEN) THEN
58 CLOSE c_award;
59 END IF;
60 RAISE;
61 END;
62 EXCEPTION
63 WHEN OTHERS THEN
64 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
65 App_Exception.Raise_Exception;
66 RAISE;
67 END grdp_val_award_type;
68 --
69 -- Validate the award ceremony has related student course attempts
70 --
71 FUNCTION grdp_val_awc_sca(
72 p_course_cd IN VARCHAR2 ,
73 p_version_number IN NUMBER ,
74 p_message_name OUT NOCOPY VARCHAR2 )
75 RETURN BOOLEAN AS
76 BEGIN -- grdp_val_awc_sca
77 -- Description: Warn the user if no student_course_attempt records
78 -- exist for the specified course_cd and version_number. WARNING ONLY
79 DECLARE
80 v_dummy VARCHAR2(1);
81 CURSOR c_sca IS
82 SELECT 'X'
83 FROM IGS_EN_STDNT_PS_ATT sca
84 WHERE sca.course_cd = p_course_cd AND
85 sca.version_number = p_version_number;
86 BEGIN
87 p_message_name := NULL;
88 IF p_course_cd IS NULL OR
89 p_version_number IS NULL THEN
90 RETURN TRUE;
91 END IF;
92 OPEN c_sca;
93 FETCH c_sca INTO v_dummy;
94 IF (c_sca%NOTFOUND) THEN
95 CLOSE c_sca;
96 p_message_name := 'IGS_GR_NO_STUD_COURSE_EXISTS';
97 RETURN TRUE;
98 END IF;
99 CLOSE c_sca;
100 RETURN TRUE;
101 END;
102 EXCEPTION
103 WHEN OTHERS THEN
104 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END grdp_val_awc_sca;
108 --
109 -- Validate the award is not closed.
110 --
111 FUNCTION crsp_val_aw_closed(
112 p_award_cd IN VARCHAR2 ,
113 p_message_name OUT NOCOPY VARCHAR2 )
114 RETURN BOOLEAN AS
115 v_other_detail VARCHAR(255);
116 v_closed_ind IGS_PS_AWD.closed_ind%TYPE;
117 CURSOR c_aw IS
118 SELECT closed_ind
119 FROM IGS_PS_AWD aw
120 WHERE aw.award_cd = p_award_cd;
121 BEGIN
122 -- check if the award is closed
123 OPEN c_aw;
124 FETCH c_aw INTO v_closed_ind;
125 IF c_aw%NOTFOUND THEN
126 p_message_name := NULL;
127 CLOSE c_aw;
128 RETURN TRUE;
129 ELSIF (v_closed_ind = 'N') THEN
130 p_message_name := NULL;
131 CLOSE c_aw;
132 RETURN TRUE;
133 ELSE
134 p_message_name := 'IGS_PS_AWARD_CD_CLOSED';
135 CLOSE c_aw;
136 RETURN FALSE;
137 END IF;
138 EXCEPTION
139 WHEN OTHERS THEN
140 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END crsp_val_aw_closed;
144 --
145 -- Validate the award ceremony order in ceremony
146 --
147 FUNCTION grdp_val_awc_order(
148 p_grd_cal_type IN VARCHAR2 ,
149 p_grd_ci_sequence_number IN NUMBER ,
150 p_ceremony_number IN NUMBER ,
151 p_award_course_cd IN VARCHAR2 ,
152 p_award_crs_version_number IN NUMBER ,
153 p_award_cd IN VARCHAR2 ,
154 p_order_in_ceremony IN NUMBER ,
155 p_message_name OUT NOCOPY VARCHAR2 )
156 RETURN BOOLEAN AS
157 BEGIN -- grdp_val_awc_order
158 DECLARE
159 v_awc_exists VARCHAR2(1);
160 v_acusg_grd_cal_type IGS_GR_AWD_CRM_US_GP.grd_cal_type%TYPE;
161 v_acusg_grd_ci_sequence_number
162 IGS_GR_AWD_CRM_US_GP.grd_ci_sequence_number%TYPE;
163 v_acusg_ceremony_number IGS_GR_AWD_CRM_US_GP.ceremony_number%TYPE;
164 v_acusg_award_course_cd IGS_GR_AWD_CRM_US_GP.award_course_cd%TYPE;
165 v_acusg_award_crs_version_num
166 IGS_GR_AWD_CRM_US_GP.award_crs_version_number%TYPE;
167 v_acusg_award_cd IGS_GR_AWD_CRM_US_GP.award_cd%TYPE;
168 v_acusg_us_group_number IGS_GR_AWD_CRM_US_GP.us_group_number%TYPE;
169 v_message_name VARCHAR2(30);
170 CURSOR c_awc IS
171 SELECT 'X'
172 FROM IGS_GR_AWD_CEREMONY awc
173 WHERE awc.grd_cal_type = p_grd_cal_type AND
174 awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
175 awc.ceremony_number = p_ceremony_number AND
176 awc.order_in_ceremony = p_order_in_ceremony AND
177 awc.award_cd <> p_award_cd;
178 CURSOR c_acusg IS
179 SELECT acusg.grd_cal_type,
180 acusg.grd_ci_sequence_number,
181 acusg.ceremony_number,
182 acusg.award_course_cd,
183 acusg.award_crs_version_number,
184 acusg.award_cd,
185 acusg.us_group_number
186 FROM IGS_GR_AWD_CRM_US_GP acusg
187 WHERE acusg.grd_cal_type = p_grd_cal_type AND
188 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
189 acusg.ceremony_number = p_ceremony_number AND
190 acusg.award_course_cd = p_award_course_cd AND
191 acusg.award_crs_version_number = p_award_crs_version_number AND
192 acusg.award_cd = p_award_cd;
193 BEGIN
194 -- Set the default message number
195 p_message_name := NULL;
196 --1. Check parameters :
197 IF p_grd_cal_type IS NULL OR
198 p_grd_ci_sequence_number IS NULL OR
199 p_ceremony_number IS NULL OR
200 p_award_cd IS NULL OR
201 p_order_in_ceremony IS NULL THEN
202 RETURN TRUE;
203 END IF;
204 --Check for any award_ceremony records with for the same graduation_ceremony
205 -- with the same order_in_ceremony but a different award_cd.
206 --4. If any records are found raise an error.
207 OPEN c_awc;
208 FETCH c_awc INTO v_awc_exists;
209 IF c_awc%FOUND THEN
210 CLOSE c_awc;
211 p_message_name := 'IGS_GR_MUST_BE_SAME_AWRD_CD';
212 RETURN FALSE;
213 END IF;
214 -- If course code and version number are NULL it mus be
215 -- an honorary award which cannot have unit set groups.
216 IF p_award_course_cd IS NULL OR
217 p_award_crs_version_number IS NULL THEN
218 RETURN TRUE;
219 END IF;
220 --5. Loop through all of the award_ceremony_us_group records for this
221 -- award_ceremony and call GRDP_VAL_ACUSG_ORDER to check for any
222 -- order_in_award conflicts.
223 FOR v_acusg_rec IN c_acusg LOOP
224 IF NOT IGS_GR_VAL_AWC.grdp_val_acusg_order(
225 v_acusg_rec.grd_cal_type,
226 v_acusg_rec.grd_ci_sequence_number,
227 v_acusg_rec.ceremony_number,
228 v_acusg_rec.award_course_cd,
229 v_acusg_rec.award_crs_version_number,
230 v_acusg_rec.award_cd,
231 v_acusg_rec.us_group_number,
232 v_message_name) THEN
233 p_message_name := v_message_name;
234 RETURN FALSE;
235 END IF;
236 END LOOP;
237 -- Return the default value
238 RETURN TRUE;
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF c_awc%ISOPEN THEN
242 CLOSE c_awc;
243 END IF;
244 IF c_acusg%ISOPEN THEN
245 CLOSE c_acusg;
246 END IF;
247 RAISE;
248 END;
249 EXCEPTION
250 WHEN OTHERS THEN
251 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
252 IGS_GE_MSG_STACK.ADD;
253 App_Exception.Raise_Exception;
254 END grdp_val_awc_order;
255 --
256 -- Validate if the award ceremony us group order in award.
257 --
258 FUNCTION grdp_val_acusg_order(
259 p_grd_cal_type IN VARCHAR2 ,
260 p_grd_ci_sequence_number IN NUMBER ,
261 p_ceremony_number IN NUMBER ,
262 p_award_course_cd IN VARCHAR2 ,
263 p_award_crs_version_number IN NUMBER ,
264 p_award_cd IN VARCHAR2 ,
265 p_us_group_number IN NUMBER ,
266 p_message_name OUT NOCOPY VARCHAR2 )
267 RETURN BOOLEAN AS
268 BEGIN -- grdp_val_acusg_order
269 -- This validates that award_ceremony_us_group records with the same
270 -- order_in_award which have parent award_ceremony records in the same
271 -- graduation_ceremony with the same order_in_ceremony have matching
272 -- award_ceremony_unit_set records.
273 DECLARE
274 v_awc_acusg_exists VARCHAR2(1);
275 CURSOR c_awc_acusg IS
276 SELECT 'x'
277 FROM IGS_GR_AWD_CEREMONY awc1,
278 IGS_GR_AWD_CEREMONY awc2,
279 IGS_GR_AWD_CRM_US_GP acusg1,
280 IGS_GR_AWD_CRM_US_GP acusg2
281 WHERE acusg1.grd_cal_type = p_grd_cal_type AND
282 acusg1.grd_ci_sequence_number = p_grd_ci_sequence_number AND
283 acusg1.ceremony_number = p_ceremony_number AND
284 acusg1.award_course_cd = p_award_course_cd AND
285 acusg1.award_crs_version_number = p_award_crs_version_number AND
286 acusg1.award_cd = p_award_cd AND
287 acusg1.us_group_number = p_us_group_number AND
288 acusg1.closed_ind = 'N' AND
289 acusg2.grd_cal_type = acusg1.grd_cal_type AND
290 acusg2.grd_ci_sequence_number = acusg1.grd_ci_sequence_number AND
291 acusg2.ceremony_number = acusg1.ceremony_number AND
292 (acusg2.award_course_cd <> acusg1.award_course_cd OR
293 acusg2.award_crs_version_number <> acusg1.award_crs_version_number) AND
294 acusg2.award_cd = acusg1.award_cd AND
295 acusg2.closed_ind = 'N' AND
296 acusg1.order_in_award = acusg2.order_in_award AND
297 awc1.grd_cal_type = acusg1.grd_cal_type AND
298 awc1.grd_ci_sequence_number = acusg1.grd_ci_sequence_number AND
299 awc1.ceremony_number = acusg1.ceremony_number AND
300 awc1.award_course_cd = acusg1.award_course_cd AND
301 awc1.award_crs_version_number = acusg1.award_crs_version_number AND
302 awc1.award_cd = acusg1.award_cd AND
303 awc1.closed_ind = 'N' AND
304 awc2.grd_cal_type = acusg2.grd_cal_type AND
305 awc2.grd_ci_sequence_number = acusg2.grd_ci_sequence_number AND
306 awc2.ceremony_number = acusg2.ceremony_number AND
307 awc2.award_course_cd = acusg2.award_course_cd AND
308 awc2.award_crs_version_number = acusg2.award_crs_version_number AND
309 awc2.award_cd = acusg2.award_cd AND
310 awc2.closed_ind = 'N' AND
311 awc1.order_in_ceremony = awc2.order_in_ceremony
312 AND
313 (EXISTS
314 (SELECT acus.unit_set_cd,
315 acus.us_version_number
316 FROM IGS_GR_AWD_CRM_UT_ST acus
317 WHERE acus.grd_cal_type = acusg1.grd_cal_type AND
318 acus.grd_ci_sequence_number = acusg1.grd_ci_sequence_number AND
319 acus.ceremony_number = acusg1.ceremony_number AND
320 acus.award_course_cd = acusg1.award_course_cd AND
321 acus.award_crs_version_number = acusg1.award_crs_version_number AND
322 acus.award_cd = acusg1.award_cd AND
323 acus.us_group_number = acusg1.us_group_number
324 MINUS
325 SELECT acus.unit_set_cd,
326 acus.us_version_number
327 FROM IGS_GR_AWD_CRM_UT_ST acus
328 WHERE acus.grd_cal_type = acusg2.grd_cal_type AND
329 acus.grd_ci_sequence_number = acusg2.grd_ci_sequence_number AND
330 acus.ceremony_number = acusg2.ceremony_number AND
331 acus.award_course_cd = acusg2.award_course_cd AND
332 acus.award_crs_version_number = acusg2.award_crs_version_number AND
333 acus.award_cd = acusg2.award_cd AND
334 acus.us_group_number = acusg2.us_group_number)
335 OR
336 EXISTS
337 (SELECT acus.unit_set_cd,
338 acus.us_version_number
339 FROM IGS_GR_AWD_CRM_UT_ST acus
340 WHERE acus.grd_cal_type = acusg2.grd_cal_type AND
341 acus.grd_ci_sequence_number = acusg2.grd_ci_sequence_number AND
342 acus.ceremony_number = acusg2.ceremony_number AND
343 acus.award_course_cd = acusg2.award_course_cd AND
344 acus.award_crs_version_number = acusg2.award_crs_version_number AND
345 acus.award_cd = acusg2.award_cd AND
346 acus.us_group_number = acusg2.us_group_number
347 MINUS
348 SELECT acus.unit_set_cd,
349 acus.us_version_number
350 FROM IGS_GR_AWD_CRM_UT_ST acus
351 WHERE acus.grd_cal_type = acusg1.grd_cal_type AND
352 acus.grd_ci_sequence_number = acusg1.grd_ci_sequence_number AND
353 acus.ceremony_number = acusg1.ceremony_number AND
354 acus.award_course_cd = acusg1.award_course_cd AND
355 acus.award_crs_version_number = acusg1.award_crs_version_number AND
356 acus.award_cd = acusg1.award_cd AND
357 acus.us_group_number = acusg1.us_group_number)
358 );
359 BEGIN
360 -- Set the default message number
361 p_message_name := NULL;
362 -- Check Parameters
363 IF p_grd_cal_type IS NULL OR
364 p_grd_ci_sequence_number IS NULL OR
365 p_ceremony_number IS NULL OR
366 p_award_course_cd IS NULL OR
367 p_award_crs_version_number IS NULL OR
368 p_award_cd IS NULL OR
369 p_us_group_number IS NULL THEN
370 RETURN TRUE;
371 END IF;
372 -- Check if there is any award_ceremony_us_group records with parent
373 -- award_ceremony records with the same order_in_ceremony (are for the
374 -- same award_cd but different course codes/versions) which have
375 -- award_ceremony_us_group records with the same order_in_award but are made
376 -- up of different award_ceremony_unit_set records.
377 OPEN c_awc_acusg;
378 FETCH c_awc_acusg INTO v_awc_acusg_exists;
379 IF c_awc_acusg%FOUND THEN
380 CLOSE c_awc_acusg;
381 p_message_name := 'IGS_GR_AWD_SET_GRP_EXISTS';
382 RETURN FALSE;
383 END IF;
384 CLOSE c_awc_acusg;
385 -- Return the default value
386 RETURN TRUE;
387 EXCEPTION
388 WHEN OTHERS THEN
389 IF c_awc_acusg%ISOPEN THEN
390 CLOSE c_awc_acusg;
391 END IF;
392 RAISE;
393 END;
394 EXCEPTION
395 WHEN OTHERS THEN
396 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
397 IGS_GE_MSG_STACK.ADD;
398 App_Exception.Raise_Exception;
399 END grdp_val_acusg_order;
400 END igs_gr_val_awc;
[Home] [Help]