1 PACKAGE BODY IGS_GR_PRC_GAC AS
2 /* $Header: IGSGR01B.pls 120.2 2006/02/21 00:56:30 sepalani noship $ */
3 --
4 -- Create graduand award ceremony records for graduands
5 FUNCTION grdp_ins_gac(
6 p_person_id IN NUMBER ,
7 p_create_dt IN DATE ,
8 p_grd_cal_type IN VARCHAR2 ,
9 p_grd_ci_sequence_number IN NUMBER ,
10 p_name_pronunciation IN VARCHAR2 ,
11 p_name_announced IN VARCHAR2 ,
12 p_academic_dress_rqrd_ind IN VARCHAR2 DEFAULT 'N',
13 p_academic_gown_size IN VARCHAR2 ,
14 p_academic_hat_size IN VARCHAR2 ,
15 p_guest_tickets_requested IN NUMBER ,
16 p_guest_tickets_allocated IN NUMBER ,
17 p_guest_seats IN VARCHAR2 ,
18 p_fees_paid_ind IN VARCHAR2 DEFAULT 'N',
19 p_special_requirements IN VARCHAR2 ,
20 p_resolve_stalemate_type IN VARCHAR2 ,
21 p_ignore_unit_sets_ind IN VARCHAR2 DEFAULT 'N',
22 p_message_name OUT NOCOPY VARCHAR2 )
23 RETURN BOOLEAN AS
24
25 gv_t_awc_cntr NUMBER DEFAULT 0;
26 gv_t_acusg_cntr NUMBER DEFAULT 0;
27 BEGIN -- grdp_ins_gac
28 -- This process performs allocation of graduands to the appropriate ceremony
29 -- for their course award and unit set(s) depending on the campus location of
30 -- their student_course_attempt record. The process ultimately creates
31 -- graduand_award_ceremony records
32 --
33 -- Change History :
34 -- Who When What
35 -- (reverse chronological order - newest change first)
36 --
37 -- Nalin Kumar 18-DEC-2002 Modified grdpl_ins_gac_record procedure to fix Bug# 2690151.
38 -- Added the code to log message when the Graduand ceremony record is created.
39 --
40 DECLARE
41 -- table to hold acusg records which have matching acus and susa unit sets
42 TYPE r_acusg_match_typ IS RECORD(
43 grd_cal_type IGS_GR_AWD_CRM_US_GP.grd_cal_type%TYPE,
44 grd_ci_sequence_number
45 IGS_GR_AWD_CRM_US_GP.grd_ci_sequence_number%TYPE,
46 ceremony_number IGS_GR_AWD_CRM_US_GP.ceremony_number%TYPE,
47 award_course_cd IGS_GR_AWD_CRM_US_GP.award_course_cd%TYPE,
48 award_crs_version_number
49 IGS_GR_AWD_CRM_US_GP.award_crs_version_number%TYPE,
50 award_cd IGS_GR_AWD_CRM_US_GP.award_cd%TYPE,
51 us_group_number IGS_GR_AWD_CRM_US_GP.us_group_number%TYPE,
52 dflt_ind IGS_AD_LOCATION_REL.dflt_ind%TYPE);
53 r_acusg_match r_acusg_match_typ;
54 TYPE t_acusg_match_typ IS TABLE OF r_acusg_match%TYPE
55 INDEX BY BINARY_INTEGER;
56 t_acusg_match t_acusg_match_typ;
57 t_acusg_match_blank t_acusg_match_typ;
58 -- table to hold awc matching graduand award course_cd and version_num with
59 -- links through graduation ceremony - venue - location - sca location
60 TYPE r_awc_match_typ IS RECORD(
61 grd_cal_type IGS_GR_AWD_CEREMONY.grd_cal_type%TYPE,
62 grd_ci_sequence_number IGS_GR_AWD_CEREMONY.grd_ci_sequence_number%TYPE,
63 ceremony_number IGS_GR_AWD_CEREMONY.ceremony_number%TYPE,
64 award_course_cd IGS_GR_AWD_CEREMONY.award_course_cd%TYPE,
65 award_crs_version_number IGS_GR_AWD_CEREMONY.award_crs_version_number%TYPE,
66 award_cd IGS_GR_AWD_CEREMONY.award_cd%TYPE,
67 dflt_ind IGS_AD_LOCATION_REL.dflt_ind%TYPE);
68 r_awc_match r_awc_match_typ;
69 TYPE t_awc_match_typ IS TABLE OF r_awc_match%TYPE
70 INDEX BY BINARY_INTEGER;
71 t_awc_match t_awc_match_typ;
72 t_awc_match_blank t_awc_match_typ;
73 cst_alpha CONSTANT VARCHAR2(5) := 'ALPHA';
74 cst_proportion CONSTANT VARCHAR2(10) :='PROPORTION';
75 v_susa_count NUMBER;
76 v_cntr NUMBER;
77 v_row_num NUMBER;
78 CURSOR c_susa IS
79 SELECT COUNT(*)
80 FROM IGS_AS_SU_SETATMPT susa,
81 IGS_GR_GRADUAND gr
82 WHERE gr.person_id = p_person_id AND
83 gr.create_dt = p_create_dt AND
84 gr.grd_cal_type = p_grd_cal_type AND
85 gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
86 susa.person_id = gr.person_id AND
87 susa.course_cd = gr.course_cd AND
88 susa.primary_set_ind = 'Y' AND
89 susa.student_confirmed_ind = 'Y' AND
90 susa.end_dt IS NULL;
91 CURSOR c_awc IS
92 SELECT awc.grd_cal_type,
93 awc.grd_ci_sequence_number,
94 awc.ceremony_number,
95 awc.award_course_cd,
96 awc.award_crs_version_number,
97 awc.award_cd,
98 lr.dflt_ind
99 FROM IGS_GR_GRADUAND gr,
100 IGS_GR_AWD_CEREMONY awc,
101 IGS_EN_STDNT_PS_ATT sca,
102 IGS_GR_CRMN gc,
103 IGS_GR_VENUE ve,
104 IGS_AD_LOCATION_REL lr,
105 IGS_CA_DA_INST_V daiv
106 WHERE gr.person_id = p_person_id AND
107 gr.create_dt = p_create_dt AND
108 gr.grd_cal_type = p_grd_cal_type AND
109 gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
110 sca.person_id = gr.person_id AND
111 sca.course_cd = gr.course_cd AND
112 sca.location_cd = lr.location_cd AND
113 gc.grd_cal_type = gr.grd_cal_type AND
114 gc.grd_ci_sequence_number = gr.grd_ci_sequence_number AND
115 gc.venue_cd = ve.venue_cd AND
116 ve.exam_location_cd = lr.sub_location_cd AND
117 gc.closing_dt_alias = daiv.dt_alias AND
118 gc.closing_dai_sequence_number = daiv.sequence_number AND
119 gc.grd_cal_type = daiv.cal_type AND
120 gc.grd_ci_sequence_number = daiv.ci_sequence_number AND
121 TRUNC(SYSDATE) < TRUNC(daiv.alias_val) AND
122 awc.grd_cal_type = gc.grd_cal_type AND
123 awc.grd_ci_sequence_number = gc.grd_ci_sequence_number AND
124 awc.ceremony_number = gc.ceremony_number AND
125 awc.award_course_cd = gr.award_course_cd AND
126 awc.award_crs_version_number = gr.award_crs_version_number AND
127 awc.award_cd = gr.award_cd AND
128 awc.closed_ind = 'N'
129 ORDER BY awc.grd_cal_type,
130 awc.grd_ci_sequence_number,
131 awc.ceremony_number,
132 awc.award_course_cd,
133 awc.award_crs_version_number,
134 awc.award_cd;
135 CURSOR c_acusg (
136 cp_grd_cal_type IGS_GR_AWD_CEREMONY.grd_cal_type%TYPE,
137 cp_grd_ci_sequence_number IGS_GR_AWD_CEREMONY.grd_ci_sequence_number%TYPE,
138 cp_ceremony_number IGS_GR_AWD_CEREMONY.ceremony_number%TYPE,
139 cp_award_course_cd IGS_GR_AWD_CEREMONY.award_course_cd%TYPE,
140 cp_award_crs_version_number IGS_GR_AWD_CEREMONY.award_crs_version_number%TYPE,
141 cp_award_cd IGS_GR_AWD_CEREMONY.award_cd%TYPE) IS
142 SELECT acusg.grd_cal_type,
143 acusg.grd_ci_sequence_number,
144 acusg.ceremony_number,
145 acusg.award_course_cd,
146 acusg.award_crs_version_number,
147 acusg.award_cd,
148 acusg.us_group_number
149 FROM IGS_GR_AWD_CRM_US_GP acusg
150 WHERE acusg.grd_cal_type = cp_grd_cal_type AND
151 acusg.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
152 acusg.ceremony_number = cp_ceremony_number AND
153 acusg.award_course_cd = cp_award_course_cd AND
154 acusg.award_crs_version_number = cp_award_crs_version_number AND
155 acusg.award_cd = cp_award_cd AND
156 acusg.closed_ind = 'N' AND
157 NOT EXISTS
158 (SELECT susa.unit_set_cd,
159 susa.us_version_number
160 FROM IGS_AS_SU_SETATMPT susa,
161 IGS_GR_GRADUAND gr
162 WHERE gr.person_id = p_person_id AND
163 gr.create_dt = p_create_dt AND
164 gr.grd_cal_type = p_grd_cal_type AND
165 gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
166 susa.person_id = gr.person_id AND
167 susa.course_cd = gr.course_cd AND
168 susa.primary_set_ind = 'Y' AND
169 susa.student_confirmed_ind = 'Y' AND
170 susa.end_dt IS NULL
171 MINUS
172 SELECT acus.unit_set_cd,
173 acus.us_version_number
174 FROM IGS_GR_AWD_CRM_UT_ST acus
175 WHERE acus.grd_cal_type = acusg.grd_cal_type AND
176 acus.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
177 acus.ceremony_number = acusg.ceremony_number AND
178 acus.award_course_cd = acusg.award_course_cd AND
179 acus.award_crs_version_number = acusg.award_crs_version_number AND
180 acus.award_cd = acusg.award_cd AND
181 acus.us_group_number = acusg.us_group_number) AND
182 NOT EXISTS
183 (SELECT acus.unit_set_cd,
184 acus.us_version_number
185 FROM IGS_GR_AWD_CRM_UT_ST acus
186 WHERE acus.grd_cal_type = acusg.grd_cal_type AND
187 acus.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
188 acus.ceremony_number = acusg.ceremony_number AND
189 acus.award_course_cd = acusg.award_course_cd AND
190 acus.award_crs_version_number = acusg.award_crs_version_number AND
191 acus.award_cd = acusg.award_cd AND
192 acus.us_group_number = acusg.us_group_number
193 MINUS
194 SELECT susa.unit_set_cd,
195 susa.us_version_number
196 FROM IGS_AS_SU_SETATMPT susa,
197 IGS_GR_GRADUAND gr
198 WHERE gr.person_id = p_person_id AND
199 gr.create_dt = p_create_dt AND
200 gr.grd_cal_type = p_grd_cal_type AND
201 gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
202 susa.person_id = gr.person_id AND
203 susa.course_cd = gr.course_cd AND
204 susa.primary_set_ind = 'Y' AND
205 susa.student_confirmed_ind = 'Y' AND
206 susa.end_dt IS NULL)
207 ORDER BY acusg.grd_cal_type,
208 acusg.grd_ci_sequence_number,
209 acusg.ceremony_number,
210 acusg.award_course_cd,
211 acusg.award_crs_version_number,
212 acusg.award_cd,
213 acusg.us_group_number;
214 PROCEDURE grdpl_rslv_stlmt_default (
215 p_row_num OUT NOCOPY NUMBER)
216 AS
217 -- 8A. Internal procedure to resolve ceremony stalemates by using
218 -- the ceremony at the default graduation location for the students
219 -- campus location if one exists.
220 BEGIN
221 DECLARE
222 v_row_num NUMBER;
223 v_row_num_new NUMBER;
224 v_dflt_count NUMBER;
225 BEGIN
226 p_row_num := 0;
227 v_dflt_count := 0;
228 FOR v_row_num IN 1.. gv_t_acusg_cntr LOOP
229 IF t_acusg_match(v_row_num).dflt_ind = 'Y' THEN
230 p_row_num := v_row_num;
231 v_dflt_count := v_dflt_count + 1;
232 END IF;
233 END LOOP;
234 IF v_dflt_count > 1 THEN
235 p_row_num := 0;
236 v_row_num_new := 0;
237 -- Remove the non-default location ceremonies from the set
238 FOR v_row_num IN 1.. gv_t_acusg_cntr LOOP
239 IF t_acusg_match(v_row_num).dflt_ind = 'Y' THEN
240 v_row_num_new := v_row_num_new + 1;
241 t_acusg_match(v_row_num_new) := t_acusg_match(v_row_num);
242 END IF;
243 END LOOP;
244 gv_t_acusg_cntr := v_row_num_new;
245 END IF;
246 END;
247 EXCEPTION
248 WHEN OTHERS THEN
249 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END grdpl_rslv_stlmt_default;
253 PROCEDURE grdpl_rslv_stlmt_alpha (
254 p_row_number OUT NOCOPY NUMBER)
255 AS
256 BEGIN
257 --Internal procedure to resolve ceremony stalemates by splitting the
258 -- ceremonies alphabetically. It passes back the row number for the
259 -- appropriate record in the t_acusg_match PL/SQL table.
260 DECLARE
261 v_letter_number NUMBER;
262 CURSOR c_pe IS
263 SELECT (ASCII(UPPER(pe.last_name)) - 65)
264 FROM IGS_PE_PERSON_BASE_V pe
265 WHERE pe.person_id = p_person_id;
266 BEGIN
267 OPEN c_pe;
268 FETCH c_pe INTO v_letter_number;
269 CLOSE c_pe;
270 p_row_number := FLOOR(v_letter_number / (26 / gv_t_acusg_cntr)) + 1;
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF c_pe%ISOPEN THEN
274 CLOSE c_pe;
275 END IF;
276 RAISE;
277 END;
278 EXCEPTION
279 WHEN OTHERS THEN
280 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END grdpl_rslv_stlmt_alpha;
284 PROCEDURE grdpl_rslv_stlmt_prprtn (
285 p_row_num OUT NOCOPY NUMBER)
286 AS
287 -- 8. Internal procedure to resolve ceremony stalemates by splitting the
288 -- ceremonies proportionally. It passes back the row number for the
289 -- appropriate record in the t_acusg_match PL/SQL table.
290 BEGIN
291 DECLARE
292 v_lowest_count NUMBER;
293 v_row_num NUMBER;
294 v_gac_count NUMBER;
295 CURSOR c_gac (
296 cp_row_num NUMBER) IS
297 SELECT COUNT(*)
298 FROM IGS_GR_AWD_CRMN gac
299 WHERE gac.grd_cal_type = t_acusg_match(cp_row_num).grd_cal_type AND
300 gac.grd_ci_sequence_number =
301 t_acusg_match(cp_row_num).grd_ci_sequence_number AND
302 gac.ceremony_number = t_acusg_match(cp_row_num).ceremony_number AND
303 gac.award_course_cd = t_acusg_match(cp_row_num).award_course_cd AND
304 gac.award_crs_version_number =
305 t_acusg_match(cp_row_num).award_crs_version_number AND
306 gac.award_cd = t_acusg_match(cp_row_num).award_cd AND
307 NVL(gac.us_group_number, 0) =
308 NVL(t_acusg_match(cp_row_num).us_group_number, 0);
309 BEGIN
310 v_lowest_count := 999999;
311 FOR v_row_num IN 1.. gv_t_acusg_cntr LOOP
312 OPEN c_gac(v_row_num);
313 FETCH c_gac INTO v_gac_count;
314 CLOSE c_gac;
315 IF v_gac_count < v_lowest_count THEN
316 v_lowest_count := v_gac_count;
317 p_row_num := v_row_num;
318 END IF;
319 END LOOP;
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF c_gac%ISOPEN THEN
323 CLOSE c_gac;
324 END IF;
325 RAISE;
326 END;
327 EXCEPTION
328 WHEN OTHERS THEN
329 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 END grdpl_rslv_stlmt_prprtn;
333 PROCEDURE grdpl_ins_gac_record (
334 p_row_num NUMBER)
335 AS
336 BEGIN
337 -- 9. Internal procedure to insert graduand_award_ceremony records.
338 -- It is passed the row number for the appropriate record in the
339 -- t_acusg_match PL/SQL table.
340 DECLARE
341 lv_rowid VARCHAR2(25);
342 lv_id NUMBER;
343
344 -- Added the following coursor to get the Person Number. Bug# 2690151
345 CURSOR get_person_num IS
346 SELECT person_number
347 FROM igs_pe_person_base_v
348 WHERE person_id = p_person_id;
349 l_person_number igs_pe_person_base_v.person_number%TYPE := NULL;
350 BEGIN
351 IGS_GR_AWD_CRMN_PKG.INSERT_ROW(
352 X_ROWID => lv_rowid,
353 X_GAC_ID => lv_id,
354 X_GRADUAND_SEAT_NUMBER => NULL,
355 X_NAME_PRONUNCIATION => p_name_pronunciation,
356 X_NAME_ANNOUNCED => p_name_announced,
357 X_ACADEMIC_DRESS_RQRD_IND => p_academic_dress_rqrd_ind,
358 X_ACADEMIC_GOWN_SIZE => p_academic_gown_size,
359 X_ACADEMIC_HAT_SIZE => p_academic_hat_size,
360 X_GUEST_TICKETS_REQUESTED => p_guest_tickets_requested,
361 X_GUEST_TICKETS_ALLOCATED => p_guest_tickets_allocated,
362 X_GUEST_SEATS => p_guest_seats,
363 X_FEES_PAID_IND => p_fees_paid_ind,
364 X_SPECIAL_REQUIREMENTS => p_special_requirements,
365 X_COMMENTS => NULL,
366 X_PERSON_ID => p_person_id,
367 X_CREATE_DT => p_create_dt,
368 X_GRD_CAL_TYPE => t_acusg_match(p_row_num).grd_cal_type,
369 X_GRD_CI_SEQUENCE_NUMBER => t_acusg_match(p_row_num).grd_ci_sequence_number,
370 X_CEREMONY_NUMBER => t_acusg_match(p_row_num).ceremony_number,
371 X_AWARD_COURSE_CD => t_acusg_match(p_row_num).award_course_cd,
372 X_AWARD_CRS_VERSION_NUMBER => t_acusg_match(p_row_num).award_crs_version_number,
373 X_AWARD_CD => t_acusg_match(p_row_num).award_cd,
374 X_US_GROUP_NUMBER => t_acusg_match(p_row_num).us_group_number,
375 X_ORDER_IN_PRESENTATION => NULL,
376 X_MODE => 'R');
377 COMMIT;
378 --
379 -- Added the following 'put_line' to fix bug# 2690151
380 --
381 --Get the Person Number
382 OPEN get_person_num;
383 FETCH get_person_num INTO l_person_number;
384 CLOSE get_person_num;
385 FND_MESSAGE.SET_NAME('IGS','IGS_GR_CRMN_REC_CRTD');
386 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
387 FND_MESSAGE.SET_TOKEN('GRD_CAL',t_acusg_match(p_row_num).grd_cal_type);
388 FND_MESSAGE.SET_TOKEN('CEREMONY',t_acusg_match(p_row_num).ceremony_number);
389 FND_MESSAGE.SET_TOKEN('COURSE',t_acusg_match(p_row_num).award_course_cd);
390 FND_MESSAGE.SET_TOKEN('AWARD',t_acusg_match(p_row_num).award_cd);
391 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
392 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
393
394 END;
395 EXCEPTION
396 WHEN OTHERS THEN
397 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
398 IGS_GE_MSG_STACK.ADD;
399 App_Exception.Raise_Exception;
400 END grdpl_ins_gac_record;
401 BEGIN
402 gv_t_awc_cntr := 0;
403 gv_t_acusg_cntr := 0;
404 t_acusg_match := t_acusg_match_blank;
405 t_awc_match := t_awc_match_blank;
406 --1. Check parameters :
407 IF p_person_id IS NULL OR
408 p_create_dt IS NULL OR
409 p_grd_cal_type IS NULL OR
410 p_grd_ci_sequence_number IS NULL OR
411 p_academic_dress_rqrd_ind IS NULL OR
412 p_fees_paid_ind IS NULL OR
413 p_resolve_stalemate_type IS NULL OR
414 p_ignore_unit_sets_ind IS NULL THEN
415 p_message_name := NULL;
416 RETURN TRUE;
417 END IF;
418 -- 2. Get the count of primary student_unit_set_attempts for the current
419 -- graduand.
420 OPEN c_susa;
421 FETCH c_susa INTO v_susa_count;
422 CLOSE c_susa;
423 -- 3. Find award_ceremony records which match the graduand award_course_cd,
424 -- award_crs_version_number and award_cd and are linked to a
425 -- graduation_ceremony at a venue linked to the location in the graduands
426 -- student_course_attempt. Loop through the matching award_ceremony records.
427 FOR v_awc_rec IN c_awc LOOP
428 -- 4. If the graduand has any primary student_unit_set_attempt records
429 -- attempt to find an award_ceremony_us_group within the current
430 -- award_ceremony with a matching set of award_ceremony_unit_set records.
431 IF v_susa_count > 0 THEN
432 FOR v_acusg_rec IN c_acusg (
433 v_awc_rec.grd_cal_type,
434 v_awc_rec.grd_ci_sequence_number,
435 v_awc_rec.ceremony_number,
436 v_awc_rec.award_course_cd,
437 v_awc_rec.award_crs_version_number,
438 v_awc_rec.award_cd) LOOP
439 -- write record v_acusg_rec into PL/SQL table t_acusg_match
440 gv_t_acusg_cntr := gv_t_acusg_cntr + 1;
441 t_acusg_match(gv_t_acusg_cntr).grd_cal_type := v_acusg_rec.grd_cal_type;
442 t_acusg_match(gv_t_acusg_cntr).grd_ci_sequence_number :=
443 v_acusg_rec.grd_ci_sequence_number;
444 t_acusg_match(gv_t_acusg_cntr).ceremony_number :=
445 v_acusg_rec.ceremony_number;
446 t_acusg_match(gv_t_acusg_cntr).award_course_cd :=
447 v_acusg_rec.award_course_cd;
448 t_acusg_match(gv_t_acusg_cntr).award_crs_version_number :=
449 v_acusg_rec.award_crs_version_number;
450 t_acusg_match(gv_t_acusg_cntr).award_cd := v_acusg_rec.award_cd;
451 t_acusg_match(gv_t_acusg_cntr).us_group_number :=
452 v_acusg_rec.us_group_number;
453 t_acusg_match(gv_t_acusg_cntr).dflt_ind :=
454 v_awc_rec.dflt_ind;
455 END LOOP; -- c_acusg
456 END IF;
457 -- write record v_awc_rec into PL/SQL table t_awc_match
458 gv_t_awc_cntr := gv_t_awc_cntr + 1;
459 t_awc_match(gv_t_awc_cntr).grd_cal_type := v_awc_rec.grd_cal_type;
460 t_awc_match(gv_t_awc_cntr).grd_ci_sequence_number :=
461 v_awc_rec.grd_ci_sequence_number;
462 t_awc_match(gv_t_awc_cntr).ceremony_number := v_awc_rec.ceremony_number;
463 t_awc_match(gv_t_awc_cntr).award_course_cd := v_awc_rec.award_course_cd;
464 t_awc_match(gv_t_awc_cntr).award_crs_version_number :=
465 v_awc_rec.award_crs_version_number;
466 t_awc_match(gv_t_awc_cntr).award_cd := v_awc_rec.award_cd;
467 t_awc_match(gv_t_awc_cntr).dflt_ind := v_awc_rec.dflt_ind;
468 END LOOP;
469 -- 5. Place the award_ceremony records in the empty award_ceremony_us_group
470 -- PL/SQL table t_acusg_match.
471 IF(v_susa_count = 0 AND gv_t_awc_cntr > 0) OR
472 (v_susa_count > 0 AND
473 gv_t_acusg_cntr = 0 AND
474 p_ignore_unit_sets_ind = 'Y') THEN
475 -- Copy t_awc_match records into t_acusg_match
476 FOR v_cntr IN 1..gv_t_awc_cntr LOOP
477 gv_t_acusg_cntr := gv_t_acusg_cntr + 1;
478 t_acusg_match(gv_t_acusg_cntr).grd_cal_type :=
479 t_awc_match(v_cntr).grd_cal_type;
480 t_acusg_match(gv_t_acusg_cntr).grd_ci_sequence_number :=
481 t_awc_match(v_cntr).grd_ci_sequence_number;
482 t_acusg_match(gv_t_acusg_cntr).ceremony_number :=
483 t_awc_match(v_cntr).ceremony_number;
484 t_acusg_match(gv_t_acusg_cntr).award_course_cd :=
485 t_awc_match(v_cntr).award_course_cd;
486 t_acusg_match(gv_t_acusg_cntr).award_crs_version_number :=
487 t_awc_match(v_cntr).award_crs_version_number;
488 t_acusg_match(gv_t_acusg_cntr).award_cd := t_awc_match(v_cntr).award_cd;
489 t_acusg_match(gv_t_acusg_cntr).us_group_number := NULL;
490 t_acusg_match(gv_t_acusg_cntr).dflt_ind := t_awc_match(v_cntr).dflt_ind;
491 END LOOP;
492 END IF;
493 -- 6. If there is only one matching ceremony insert the record there.
494 -- If there is more than one matching ceremony call a function to resolve
495 -- the stalemate.
496 IF gv_t_acusg_cntr = 1 THEN
497 grdpl_ins_gac_record(gv_t_acusg_cntr);
498 ELSE
499 IF gv_t_acusg_cntr > 1 THEN
500 grdpl_rslv_stlmt_default(v_row_num);
501 IF v_row_num > 0 THEN
502 grdpl_ins_gac_record(v_row_num);
503 ELSE
504 IF p_resolve_stalemate_type = cst_alpha THEN
505 grdpl_rslv_stlmt_alpha(v_row_num);
506 grdpl_ins_gac_record(v_row_num);
507 ELSE
508 IF p_resolve_stalemate_type = cst_proportion THEN
509 grdpl_rslv_stlmt_prprtn(v_row_num);
510 grdpl_ins_gac_record(v_row_num);
511 END IF;
512 END IF;
513 END IF;
514 END IF;
515 END IF;
516 -- Return no error:
517 p_message_name := NULL;
518 RETURN TRUE;
519 EXCEPTION
520 WHEN OTHERS THEN
521 IF (c_susa%ISOPEN) THEN
522 CLOSE c_susa;
523 END IF;
524 IF (c_awc%ISOPEN) THEN
525 CLOSE c_awc;
526 END IF;
527 IF (c_acusg%ISOPEN) THEN
528 CLOSE c_acusg;
529 END IF;
530 RAISE;
531 END;
532 EXCEPTION
533 WHEN OTHERS THEN
534 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
535 IGS_GE_MSG_STACK.ADD;
536 App_Exception.Raise_Exception;
537 END grdp_ins_gac;
538 --
539 -- Process the close of a Award Ceremony Unit Set Group
540 FUNCTION grdp_prc_acusg_close(
541 p_grd_cal_type IN VARCHAR2 ,
542 p_grd_ci_sequence_number IN NUMBER ,
543 p_ceremony_number IN NUMBER ,
544 p_award_course_cd IN VARCHAR2 ,
545 p_award_crs_version_number IN NUMBER ,
546 p_award_cd IN VARCHAR2 ,
547 p_us_group_number IN NUMBER ,
548 p_resolve_stalemate_type IN VARCHAR2 ,
549 p_ignore_unit_sets_ind IN VARCHAR2 DEFAULT 'N',
550 p_message_name OUT NOCOPY VARCHAR2 )
551 RETURN BOOLEAN AS
552
553 BEGIN -- grdp_prc_acusg_close
554 -- Description: This process is passed the details of a award_ceremony_us_group
555 -- record which is closed. It finds any associated graduand_award_ceremony
556 -- records
557 DECLARE
558 v_loop_flag BOOLEAN := FALSE;
559 e_resource_busy_exception EXCEPTION;
560 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
561 CURSOR c_gac IS
562 SELECT gac.person_id,
563 gac.create_dt,
564 gac.name_pronunciation,
565 gac.name_announced,
566 gac.academic_dress_rqrd_ind,
567 gac.academic_gown_size,
568 gac.academic_hat_size,
569 gac.guest_tickets_requested,
570 gac.guest_tickets_allocated,
571 gac.guest_seats,
572 gac.fees_paid_ind,
573 gac.special_requirements,
574 gac.grd_cal_type,
575 gac.grd_ci_sequence_number,
576 gac.ceremony_number,
577 gac.award_course_cd,
578 gac.award_crs_version_number,
579 gac.award_cd
580 FROM IGS_GR_AWD_CRMN gac
581 WHERE gac.grd_cal_type = p_grd_cal_type AND
582 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
583 gac.ceremony_number = p_ceremony_number AND
584 NVL(gac.award_course_cd,'NULL')
585 = NVL(p_award_course_cd,'NULL') AND
586 NVL(gac.award_crs_version_number,0)
587 = NVL(p_award_crs_version_number,0) AND
588 gac.award_cd = p_award_cd AND
589 gac.us_group_number = p_us_group_number;
590 CURSOR c_gac_del (
591 cp_person_id IGS_GR_AWD_CRMN.person_id%TYPE,
592 cp_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
593 cp_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
594 cp_grd_ci_sequence_number
595 IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
596 cp_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE,
597 cp_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE) IS
598 SELECT rowid,gac.*
599 FROM IGS_GR_AWD_CRMN gac
600 WHERE gac.person_id = cp_person_id AND
601 gac.create_dt = cp_create_dt AND
602 gac.grd_cal_type = cp_grd_cal_type AND
603 gac.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
604 gac.award_cd = cp_award_cd
605 FOR UPDATE OF gac.person_id NOWAIT;
606 v_gac_del c_gac_del%ROWTYPE;
607 CURSOR c_gach(
608 cp_gac_person_id IGS_GR_AWD_CRMN.person_id%TYPE,
609 cp_gac_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
610 cp_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
611 cp_grd_ci_sequence_number
612 IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
613 cp_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE,
614 cp_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE) IS
615 SELECT rowid, gach.person_id
616 FROM IGS_GR_AWD_CRMN_HIST gach
617 WHERE gach.person_id = cp_gac_person_id AND
618 gach.create_dt = cp_gac_create_dt AND
619 gach.grd_cal_type = cp_grd_cal_type AND
620 gach.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
621 gach.ceremony_number = cp_ceremony_number AND
622 gach.award_cd = cp_award_cd
623 FOR UPDATE OF gach.person_id NOWAIT;
624 BEGIN
625 p_message_name := NULL;
626 -- 1.Check parameters
627 IF p_grd_cal_type IS NULL OR
628 p_grd_ci_sequence_number IS NULL OR
629 p_ceremony_number IS NULL OR
630 p_award_cd IS NULL OR
631 p_us_group_number IS NULL THEN
632 RETURN TRUE;
633 END IF;
634 -- 2.Find any associated graduand_award_ceremony records for
635 -- this award_ceremony_us_group and loop through them.
636 FOR v_gac_rec IN c_gac LOOP
637 SAVEPOINT sp_gac_del;
638 BEGIN
639 OPEN c_gac_del(
640 v_gac_rec.person_id,
641 v_gac_rec.create_dt,
642 v_gac_rec.grd_cal_type,
643 v_gac_rec.grd_ci_sequence_number,
644 v_gac_rec.ceremony_number,
645 v_gac_rec.award_cd);
646 FETCH c_gac_del INTO v_gac_del;
647 -- 3.Delete the existing graduand_award_ceremony record
648 IF (c_gac_del%FOUND) THEN
649 IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
650 X_ROWID => v_gac_del.rowid);
651 END IF;
652 CLOSE c_gac_del;
653 EXCEPTION
654 WHEN e_resource_busy_exception THEN
655 IF c_gach %ISOPEN THEN
656 CLOSE c_gach;
657 END IF;
658 ROLLBACK TO sp_gac_del;
659 p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
660 RETURN FALSE;
661 WHEN OTHERS THEN
662 ROLLBACK TO sp_gac_del;
663 RAISE;
664 END;
665 BEGIN
666 FOR v_gach_rec IN c_gach(
667 v_gac_rec.person_id,
668 v_gac_rec.create_dt,
669 v_gac_rec.grd_cal_type,
670 v_gac_rec.grd_ci_sequence_number,
671 v_gac_rec.ceremony_number,
672 v_gac_rec.award_cd) LOOP
673 IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW(
674 X_ROWID => v_gach_rec.rowid);
675 END LOOP;
676 EXCEPTION
677 WHEN e_resource_busy_exception THEN
678 IF c_gach %ISOPEN THEN
679 CLOSE c_gach;
680 END IF;
681 ROLLBACK TO sp_gac_del;
682 p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
683 RETURN FALSE;
684 WHEN OTHERS THEN
685 ROLLBACK TO sp_gac_del;
686 RAISE;
687 END;
688 COMMIT;
689 -- 4.Call GRDP_INS_GAC to allocate this graduand to another
690 -- ceremony if one is available
691 IF NOT grdp_ins_gac(
692 v_gac_rec.person_id,
693 v_gac_rec.create_dt ,
694 p_grd_cal_type,
695 p_grd_ci_sequence_number,
696 v_gac_rec.name_pronunciation,
697 v_gac_rec.name_announced,
698 v_gac_rec.academic_dress_rqrd_ind,
699 v_gac_rec.academic_gown_size,
700 v_gac_rec.academic_hat_size,
701 v_gac_rec.guest_tickets_requested,
702 v_gac_rec.guest_tickets_allocated,
703 v_gac_rec.guest_seats,
704 v_gac_rec.fees_paid_ind,
705 v_gac_rec.special_requirements,
706 p_resolve_stalemate_type,
707 p_ignore_unit_sets_ind,
708 p_message_name) THEN
709 v_loop_flag := TRUE;
710 Exit;
711 END IF;
712 END LOOP; --c_gac
713 IF (v_loop_flag = TRUE) THEN
714 RETURN FALSE;
715 END IF;
716 RETURN TRUE;
717 EXCEPTION
718 WHEN OTHERS THEN
719 IF c_gac %ISOPEN THEN
720 CLOSE c_gac;
721 END IF;
722 IF c_gac_del %ISOPEN THEN
723 CLOSE c_gac_del;
724 END IF;
725 IF c_gach %ISOPEN THEN
726 CLOSE c_gach;
727 END IF;
728 RAISE;
729 END;
730 EXCEPTION
731 WHEN OTHERS THEN
732 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
733 IGS_GE_MSG_STACK.ADD;
734 App_Exception.Raise_Exception;
735 END grdp_prc_acusg_close;
736 --
737 -- Process the close of a Award Ceremony
738 FUNCTION grdp_prc_awc_close(
739 p_grd_cal_type IN VARCHAR2 ,
740 p_grd_ci_sequence_number IN NUMBER ,
741 p_ceremony_number IN NUMBER ,
742 p_award_course_cd IN VARCHAR2 ,
743 p_award_crs_version_number IN NUMBER ,
744 p_award_cd IN VARCHAR2 ,
745 p_resolve_stalemate_type IN VARCHAR2 ,
746 p_ignore_unit_sets_ind IN VARCHAR2 DEFAULT 'N',
747 p_message_name OUT NOCOPY VARCHAR2 )
748 RETURN BOOLEAN AS
749
750 BEGIN -- grdp_prc_awc_close
751 -- Description: This process is passed the details of a award_ceremony
752 -- record which is closed. It finds any related award_ceremony_us_group
753 -- records and calls GENP_PRC_ACUSG_CLOSE to delete associated
754 -- graduand_award_ceremony records and attempt to re-allocate them to
755 -- another ceremony. After all the graduand_award_ceremony records are
756 -- removed the award_ceremony_us_group record is deleted. The process
757 -- then finds any graduand_award_ceremony records associated with the
758 -- award_ceremony, deletes them and calls GENP_PRC_GAC_CRMNY to attempt
759 -- to allocate them to another ceremony.
760 DECLARE
761 v_loop_flag BOOLEAN := FALSE;
762 e_resource_busy_exception EXCEPTION;
763 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
764 CURSOR c_acusg IS
765 SELECT acusg.us_group_number
766 FROM IGS_GR_AWD_CRM_US_GP acusg
767 WHERE acusg.grd_cal_type = p_grd_cal_type AND
768 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
769 acusg.ceremony_number = p_ceremony_number AND
770 NVL(acusg.award_course_cd, 'NULL') = NVL(p_award_course_cd, 'NULL') AND
771 NVL(acusg.award_crs_version_number, 0)
772 = NVL(p_award_crs_version_number, 0) AND
773 acusg.award_cd = p_award_cd;
774 CURSOR c_gac IS
775 SELECT gac.person_id,
776 gac.create_dt,
777 gac.name_pronunciation,
778 gac.name_announced,
779 gac.academic_dress_rqrd_ind,
780 gac.academic_gown_size,
781 gac.academic_hat_size,
782 gac.guest_tickets_requested,
783 gac.guest_tickets_allocated,
784 gac.guest_seats,
785 gac.fees_paid_ind,
786 gac.special_requirements,
787 gac.grd_cal_type,
788 gac.grd_ci_sequence_number,
789 gac.ceremony_number,
790 gac.award_course_cd,
791 gac.award_crs_version_number,
792 gac.award_cd
793 FROM IGS_GR_AWD_CRMN gac
794 WHERE gac.grd_cal_type = p_grd_cal_type AND
795 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
796 gac.ceremony_number = p_ceremony_number AND
797 gac.award_course_cd = p_award_course_cd AND
798 gac.award_crs_version_number = p_award_crs_version_number AND
799 gac.award_cd = p_award_cd AND
800 gac.us_group_number IS NULL;
801 CURSOR c_gac_del (
802 cp_person_id IGS_GR_AWD_CRMN.person_id%TYPE,
803 cp_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
804 cp_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
805 cp_grd_ci_sequence_number
806 IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
807 cp_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE,
808 cp_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE) IS
809 SELECT rowid, gac.*
810 FROM IGS_GR_AWD_CRMN gac
811 WHERE gac.person_id = cp_person_id AND
812 gac.create_dt = cp_create_dt AND
813 gac.grd_cal_type = cp_grd_cal_type AND
814 gac.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
815 gac.award_cd = cp_award_cd
816 FOR UPDATE OF gac.person_id NOWAIT;
817 v_gac_del c_gac_del%ROWTYPE;
818 CURSOR c_gach(
819 cp_gac_person_id IGS_GR_AWD_CRMN.person_id%TYPE,
820 cp_gac_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
821 cp_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
822 cp_grd_ci_sequence_number
823 IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
824 cp_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE,
825 cp_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE) IS
826 SELECT rowid, gach.person_id
827 FROM IGS_GR_AWD_CRMN_HIST gach
828 WHERE gach.person_id = cp_gac_person_id AND
829 gach.create_dt = cp_gac_create_dt AND
830 gach.grd_cal_type = cp_grd_cal_type AND
831 gach.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
832 gach.ceremony_number = cp_ceremony_number AND
833 gach.award_cd = cp_award_cd
834 FOR UPDATE OF gach.person_id NOWAIT;
835 BEGIN
836 p_message_name := NULL;
837 -- 1.Check parameters
838 IF p_grd_cal_type IS NULL OR
839 p_grd_ci_sequence_number IS NULL OR
840 p_ceremony_number IS NULL OR
841 p_award_cd IS NULL THEN
842 RETURN TRUE;
843 END IF;
844 -- 2.Find any associated award_ceremony_us_group records for
845 -- this award_ceremony and loop through them.
846 FOR v_acusg_rec IN c_acusg LOOP
847 -- 3.Call GRDP_PRC_ACUSG_CLOSE to process the graduands
848 -- for the award_ceremony_us_group record and then delete it.
849 IF NOT grdp_prc_acusg_close(
850 p_grd_cal_type,
851 p_grd_ci_sequence_number,
852 p_ceremony_number,
853 p_award_course_cd,
854 p_award_crs_version_number,
855 p_award_cd,
856 v_acusg_rec.us_group_number,
857 p_resolve_stalemate_type,
858 p_ignore_unit_sets_ind,
859 p_message_name) THEN
860 v_loop_flag := TRUE;
861 Exit;
862 END IF;
863 END LOOP;
864 IF (v_loop_flag = TRUE) THEN
865 RETURN FALSE;
866 END IF;
867 -- 4.Find any associated graduand_award_ceremony records for this
868 -- award_ceremony and loop through them.
869 FOR v_gac_rec IN c_gac LOOP
870 SAVEPOINT sp_gac_del;
871 BEGIN
872 OPEN c_gac_del(
873 v_gac_rec.person_id,
874 v_gac_rec.create_dt,
875 v_gac_rec.grd_cal_type,
876 v_gac_rec.grd_ci_sequence_number,
877 v_gac_rec.ceremony_number,
878 v_gac_rec.award_cd);
879 FETCH c_gac_del INTO v_gac_del;
880 -- 3.Delete the existing graduand_award_ceremony record
881 IF (c_gac_del%FOUND) THEN
882 IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
883 X_ROWID => v_gac_del.rowid);
884 END IF;
885 CLOSE c_gac_del;
886 EXCEPTION
887 WHEN e_resource_busy_exception THEN
888 IF c_gach %ISOPEN THEN
889 CLOSE c_gach;
890 END IF;
891 ROLLBACK TO sp_gac_del;
892 p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
893 RETURN FALSE;
894 WHEN OTHERS THEN
895 ROLLBACK TO sp_gac_del;
896 RAISE;
897 END;
898 BEGIN
899 FOR v_gach_rec IN c_gach(
900 v_gac_rec.person_id,
901 v_gac_rec.create_dt,
902 v_gac_rec.grd_cal_type,
903 v_gac_rec.grd_ci_sequence_number,
904 v_gac_rec.ceremony_number,
905 v_gac_rec.award_cd) LOOP
906 IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW(
907 X_ROWID => v_gach_rec.rowid);
908 END LOOP;
909 EXCEPTION
910 WHEN e_resource_busy_exception THEN
911 IF c_gach %ISOPEN THEN
912 CLOSE c_gach;
913 END IF;
914 ROLLBACK TO sp_gac_del;
915 p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
916 RETURN FALSE;
917 WHEN OTHERS THEN
918 ROLLBACK TO sp_gac_del;
919 RAISE;
920 END;
921 COMMIT;
922 -- 6.Call grdp_ins_gac to allocate this graduand to
923 -- another ceremony if one is available.
924 IF NOT grdp_ins_gac(
925 v_gac_rec.person_id,
926 v_gac_rec.create_dt ,
927 p_grd_cal_type,
928 p_grd_ci_sequence_number,
929 v_gac_rec.name_pronunciation,
930 v_gac_rec.name_announced,
931 v_gac_rec.academic_dress_rqrd_ind,
932 v_gac_rec.academic_gown_size,
933 v_gac_rec.academic_hat_size,
934 v_gac_rec.guest_tickets_requested,
935 v_gac_rec.guest_tickets_allocated,
936 v_gac_rec.guest_seats,
937 v_gac_rec.fees_paid_ind,
938 v_gac_rec.special_requirements,
939 p_resolve_stalemate_type,
940 p_ignore_unit_sets_ind,
941 p_message_name) THEN
942 v_loop_flag := TRUE;
943 Exit;
944 END IF;
945 END LOOP;
946 IF (v_loop_flag = TRUE) THEN
947 RETURN FALSE;
948 END IF;
949 RETURN TRUE;
950 EXCEPTION
951 WHEN OTHERS THEN
952 IF c_acusg %ISOPEN THEN
953 CLOSE c_acusg;
954 END IF;
955 IF c_gac %ISOPEN THEN
956 CLOSE c_gac;
957 END IF;
958 IF c_gac_del %ISOPEN THEN
959 CLOSE c_gac_del;
960 END IF;
961 IF c_gach %ISOPEN THEN
962 CLOSE c_gach;
963 END IF;
964 RAISE;
965 END;
966 EXCEPTION
967 WHEN OTHERS THEN
968 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
969 IGS_GE_MSG_STACK.ADD;
970 App_Exception.Raise_Exception;
971 END grdp_prc_awc_close;
972 END IGS_GR_PRC_GAC;
[Home] [Help]