[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_GEN_002
Source
1 PACKAGE BODY IGS_GR_GEN_002 AS
2 /* $Header: IGSGR14B.pls 120.3 2006/04/19 23:58:41 sepalani noship $ */
3 PROCEDURE grdp_ins_graduand(
4 errbuf out NOCOPY varchar2,
5 retcode out NOCOPY number,
6 p_ceremony_round IN VARCHAR2,
7 p_course_cd IGS_PS_COURSE.course_cd%TYPE ,
8 p_crs_location_cd IN IGS_AD_LOCATION_ALL.location_cd%TYPE,
9 p_award_cd IGS_PS_AWD.award_cd%TYPE ,
10 p_nominated_completion VARCHAR2 ,
11 p_derived_completion VARCHAR2 ,
12 p_restrict_rqrmnt_complete VARCHAR2 ,
13 p_potential_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
14 p_eligible_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
15 p_graduand_appr_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE,
16 p_org_id IN NUMBER,
17 p_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
18 p_approval_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE) AS
19 -------------------------------------------------------
20 -- Change History :
21 -- Who When What
22 -- (reverse chronological order - newest change first)
23 -------------------------------------------------------
24 -- Nalin Kumar 23-Nov-2001 Modified the grdpl_ins_new_graduand procedure
25 -- as per the UK Award Aims DLD. Bug ID: 1366899
26 -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
27 -- to table handler procedure calls as part of CCR - ENCR022.
28 -- Nalin Kumar 29-Oct-2002 Modified the grdp_upd_gac_order procedure as per the Conferral Date TD. Bug# 2640799
29 -- Nalin Kumar 05-Dec-2002 Modified the grdpl_ins_new_graduand procedure to fix Bug# 2683072.
30 -- Nalin Kumar 10-Dec-2002 Modified the grdp_upd_gac_order procedure to fix Bug# 2691809.
31 -- Nalin Kumar 18-DEC-2002 Modified this procedure to fix Bug# 2690151.
32 -- Added the code to log the parameters value in the log file.
33 -- Nalin Kumar 10-Mar-2003 Modified c_crd_sca cursor to considered IGS_CA_DA_INST_V.ALIAS_VAL instead of IGS_CA_DA_INST_V.ABSOLUTE_VAL
34 -- This is to fix Bug# 2760539.
35 -- Nalin Kumar 12-Dec-2003 Modified grdp_upd_gac_order procedure to fix Bug# 3294453.
36 -- iJeddy The Fourth of July, 2005 Bug 4473024, added a join on award_cd to the Cursor c_crd_sca
37 -- sepalani 19-Apr-2006 Modified Cursor c_gr_upd on procedure GRDP_PRC_GAC for Bug# 5074150
38 -------------------------------------------------------
39
40 p_grd_cal_type IGS_GR_CRMN_ROUND.grd_cal_type%TYPE ;
41 p_grd_ci_sequence_number IGS_GR_CRMN_ROUND.grd_ci_sequence_number%TYPE;
42 lv_param_values VARCHAR2(1080);
43 l_org_id NUMBER(15);
44 BEGIN
45 -- grdp_ins_graduand
46 -- This module is used to identify potential graduands and to create
47 -- IGS_GR_GRADUAND records if they haven't already been created.
48 igs_ge_gen_003.set_org_id(p_org_id);
49 --Block for Parameter Validation/Splitting of Parameters
50 retcode:=0;
51
52 DECLARE
53 v_message VARCHAR2(30);
54 invalid_parameter EXCEPTION;
55 BEGIN
56 p_grd_cal_type :=RTRIM(SUBSTR(p_ceremony_round, 101, 10));
57 p_grd_ci_sequence_number :=TO_NUMBER(RTRIM(SUBSTR(p_ceremony_round, 112, 6)));
58
59 --
60 --Log the Parameters value in the log file. This is to fix Bug# 2690151
61 --
62 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_ANC_LOG_PARM');
63 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET()||':');
64 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_cal_type = '||p_grd_cal_type);
65 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_ci_sequence_number = '||p_grd_ci_sequence_number);
66 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_course_cd = '||p_course_cd);
67 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_crs_location_cd = '||p_crs_location_cd);
68 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_award_cd = '||p_award_cd);
69 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_nominated_completion = '||p_nominated_completion);
70 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_derived_completion = '||p_derived_completion);
71 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_restrict_rqrmnt_complete = '||p_restrict_rqrmnt_complete);
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_potential_graduand_status = '||p_potential_graduand_status);
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_eligible_graduand_status = '||p_eligible_graduand_status);
74 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_graduand_appr_status = '||p_graduand_appr_status);
75 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_graduand_status = '||p_graduand_status);
76 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_approval_status = '||p_approval_status);
77 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
78
79
80 IF NOT IGS_GR_VAL_AWC.grdp_val_award_type(p_award_cd, 'COURSE', v_message) THEN
81 ERRBUF:=FND_MESSAGE.GET_STRING('IGS', v_message);
82 RAISE INVALID_PARAMETER;
83 END IF;
84
85 IF p_derived_completion = 'N' AND p_nominated_completion = 'N' THEN
86 ERRBUF:= FND_MESSAGE.GET_STRING('IGS', 'IGS_GR_NOMIN_DERV_COMPL_SET');
87 RAISE INVALID_PARAMETER;
88 END IF;
89
90 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE')='GB' and (p_graduand_status IS NULL OR p_approval_status IS NULL) THEN
91 ERRBUF:= FND_MESSAGE.GET_STRING('IGS', 'IGS_GR_GRAD_APPR_REQ');
92 RAISE INVALID_PARAMETER;
93 END IF;
94 EXCEPTION
95 WHEN INVALID_PARAMETER THEN
96 retcode:=2;
97 RETURN;
98 END;
99 --End of Block for Parameter Validation/Splitting of Parameters
100
101 DECLARE
102 cst_potential CONSTANT VARCHAR2(10) := 'POTENTIAL';
103 cst_eligible CONSTANT VARCHAR2(10) := 'ELIGIBLE';
104 cst_waiting CONSTANT VARCHAR2(10) := 'WAITING';
105 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
106 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
107 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
108 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
109 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
110 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
111 cst_graduated CONSTANT VARCHAR2(10) := 'GRADUATED';
112 cst_surrender CONSTANT VARCHAR2(10) := 'SURRENDER';
113 cst_articulate CONSTANT VARCHAR2(10) := 'ARTICULATE';
114 cst_declined CONSTANT VARCHAR2(10) := 'DECLINED';
115
116 CURSOR c_crd_sca IS
117 SELECT crd.grd_cal_type,
118 crd.grd_ci_sequence_number,
119 sca.person_id,
120 sca.course_cd,
121 sca.version_number,
122 sca.commencement_dt,
123 spaa.award_cd
124 FROM igs_en_stdnt_ps_att sca ,
125 igs_en_spa_awd_aim spaa,
126 igs_ps_ver crv ,
127 igs_gr_crmn_round crd ,
128 igs_gr_crm_round_prd crdp,
129 igs_ca_da_inst_v dai1,
130 igs_ca_da_inst_v dai2
131 WHERE (p_grd_cal_type IS NULL OR
132 (crd.grd_cal_type = p_grd_cal_type AND
133 crd.grd_ci_sequence_number = p_grd_ci_sequence_number)) AND
134 dai1.cal_type = crd.grd_cal_type AND
135 dai1.ci_sequence_number = crd.grd_ci_sequence_number AND
136 dai1.dt_alias = crd.start_dt_alias AND
137 dai1.sequence_number = crd.start_dai_sequence_number AND
138 dai1.alias_val IS NOT NULL AND
139 dai2.cal_type = crd.grd_cal_type AND
140 dai2.ci_sequence_number = crd.grd_ci_sequence_number AND
141 dai2.dt_alias = crd.end_dt_alias AND
142 dai2.sequence_number = crd.end_dai_sequence_number AND
143 dai2.alias_val IS NOT NULL AND
144 TRUNC(SYSDATE) BETWEEN NVL(dai1.alias_val, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
145 NVL(dai2.alias_val, IGS_GE_DATE.IGSDATE('1900/01/01'))AND
146 crdp.grd_cal_type(+) = crd.grd_cal_type AND
147 crdp.grd_ci_sequence_number(+) = crd.grd_ci_sequence_number AND
148 (p_course_cd IS NULL OR
149 sca.course_cd = p_course_cd) AND
150 (p_crs_location_cd IS NULL OR
151 sca.location_cd = p_crs_location_cd) AND
152 sca.person_id = spaa.person_id(+) AND
153 sca.course_cd = spaa.course_cd(+) AND
154 (p_restrict_rqrmnt_complete = 'N' OR
155 sca.course_rqrmnt_complete_ind = 'Y') AND
156 ((p_nominated_completion = 'Y' AND
157 sca.nominated_completion_yr = NVL(crdp.completion_year, 1900) AND
158 sca.nominated_completion_perd = NVL(crdp.completion_period, 'NULL')) OR
159 (p_derived_completion = 'Y' AND
160 sca.derived_completion_yr = NVL(crdp.completion_year, 1900) AND
161 sca.derived_completion_perd = NVL(crdp.completion_period, ' ')) OR
162 (NVL(sca.course_rqrmnts_complete_dt, IGS_GE_DATE.IGSDATE('9998/01/01'))
163 BETWEEN NVL(crd.completion_start_date, IGS_GE_DATE.IGSDATE('9999/01/01')) AND
164 NVL(crd.completion_end_date, IGS_GE_DATE.IGSDATE('9999/01/01'))) OR
165 (NVL(spaa.conferral_date, IGS_GE_DATE.IGSDATE('9998/01/01'))
166 BETWEEN NVL(crd.conferral_start_date, IGS_GE_DATE.IGSDATE('9999/01/01')) AND
167 NVL(crd.conferral_end_date, IGS_GE_DATE.IGSDATE('9999/01/01')))) AND
168 sca.course_attempt_status IN (
169 cst_completed,
170 cst_enrolled ,
171 cst_inactive ,
172 cst_intermit ,
173 cst_lapsed ) AND
174 crv.course_cd = sca.course_cd AND
175 crv.version_number = sca.version_number AND
176 crv.graduate_students_ind = 'Y' AND
177 ( p_award_cd IS NULL OR
178 spaa.AWARD_CD = p_award_cd )
179 ORDER BY sca.person_id;
180
181 CURSOR c_gr (
182 cp_person_id igs_gr_graduand.person_id%TYPE,
183 cp_course_cd igs_gr_graduand.course_cd%TYPE,
184 cp_award_cd igs_en_spa_awd_aim.award_cd%TYPE) IS
185 SELECT 'x'
186 FROM igs_gr_graduand gr,
187 IGS_GR_STAT gst
188 WHERE gr.person_id = cp_person_id AND
189 gr.course_cd = cp_course_cd AND
190 gr.award_cd = cp_award_cd AND
191 gr.graduand_status = gst.graduand_status AND
192 (gst.s_graduand_status IN (
193 cst_graduated,
194 cst_surrender) OR
195 gr.s_graduand_type IN (
196 cst_articulate,
197 cst_declined));
198 v_gr_exists VARCHAR2(1);
199 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE DEFAULT 0;
200 v_message_name VARCHAR2(30);
201
202 --Cursor to check the existence of the SPAA records.
203 CURSOR cur_spaaa (
204 cp_person_id igs_en_spa_awd_aim.person_id%TYPE,
205 cp_course_cd igs_en_spa_awd_aim.course_cd%TYPE) IS
206 SELECT 'x'
207 FROM igs_en_spa_awd_aim
208 WHERE person_id = cp_person_id AND
209 course_cd = cp_course_cd;
210 rec_spaaa cur_spaaa%ROWTYPE;
211
212 -- Added the following coursor to get the Person Number. Bug# 2690151
213 CURSOR get_person_num(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
214 SELECT person_number
215 FROM igs_pe_person_base_v
216 WHERE person_id = cp_person_id;
217 l_person_number VARCHAR2(500) := NULL;
218 l_log_person_id VARCHAR2(250);
219 l_chk_hold VARCHAR2(250);
220 -- -----------------------------------------------------------------
221 PROCEDURE grdpl_val_graduand_status
222 AS
223 BEGIN -- grdpl_val_graduand_status
224 -- Validate the status values provided
225 DECLARE
226 CURSOR c_gst (
227 cp_graduand_status IGS_GR_STAT.graduand_status%TYPE,
228 cp_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE) IS
229 SELECT gst.closed_ind
230 FROM IGS_GR_STAT gst
231 WHERE gst.graduand_status = cp_graduand_status AND
232 gst.s_graduand_status = cp_s_graduand_status;
233 v_gst_rec c_gst%ROWTYPE;
234 CURSOR c_gas IS
235 SELECT gas.closed_ind
236 FROM IGS_GR_APRV_STAT gas
237 WHERE gas.graduand_appr_status = p_graduand_appr_status AND
238 gas.s_graduand_appr_status IN (
239 cst_waiting,
240 cst_approved);
241 v_gas_rec c_gas%ROWTYPE;
242 BEGIN
243 -- Validate the potential IGS_GR_GRADUAND status value
244 OPEN c_gst(
245 p_potential_graduand_status,
246 cst_potential);
247 FETCH c_gst INTO v_gst_rec;
248 IF c_gst%NOTFOUND THEN
249 CLOSE c_gst;
250 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
251 --App_Exception.Raise_Exception;
252 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
253 RETURN;
254 END IF;
255 CLOSE c_gst;
256 IF v_gst_rec.closed_ind = 'Y' THEN
257 Fnd_Message.Set_Name('IGS', 'IGS_GR_GRAD_STATUS_CLOSED');
258 --App_Exception.Raise_Exception;
259 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
260 RETURN;
261 END IF;
262 -- Validate the eligible IGS_GR_GRADUAND status value
263 OPEN c_gst(
264 p_eligible_graduand_status,
265 cst_eligible);
266 FETCH c_gst INTO v_gst_rec;
267 IF c_gst%NOTFOUND THEN
268 CLOSE c_gst;
269 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
270 --App_Exception.Raise_Exception;
271 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
272 RETURN;
273 END IF;
274 CLOSE c_gst;
275 IF v_gst_rec.closed_ind = 'Y' THEN
276 Fnd_Message.Set_Name('IGS', 'IGS_GR_GRAD_STATUS_CLOSED');
277 --App_Exception.Raise_Exception;
278 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
279 RETURN;
280 END IF;
281 -- Validate the IGS_GR_GRADUAND approval status value
282 OPEN c_gas;
283 FETCH c_gas INTO v_gas_rec;
284 IF c_gas%NOTFOUND THEN
285 CLOSE c_gas;
286 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
287 --App_Exception.Raise_Exception;
288 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
289 RETURN;
290 END IF;
291 CLOSE c_gas;
292 IF v_gas_rec.closed_ind = 'Y' THEN
293 Fnd_Message.Set_Name('IGS', 'IGS_GR_GRAD_APPR_STATUS_CLOSE');
294 --App_Exception.Raise_Exception;
295 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
296 RETURN;
297 END IF;
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF c_gst%ISOPEN THEN
301 CLOSE c_gst;
302 END IF;
303 IF c_gas%ISOPEN THEN
304 CLOSE c_gas;
305 END IF;
306 RAISE;
307 END;
308 EXCEPTION
309 WHEN OTHERS THEN
310 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
311 App_Exception.Raise_Exception;
312 END grdpl_val_graduand_status;
313 -------------------------------------------------------------------
314 PROCEDURE grdpl_ins_new_graduand(
315 p_person_id igs_en_stdnt_ps_att.person_id%TYPE,
316 p_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
317 p_version_number igs_en_stdnt_ps_att.version_number%TYPE,
318 p_grd_cal_type igs_gr_crmn_round.grd_cal_type%TYPE,
319 p_grd_ci_sequence_number igs_gr_crmn_round.grd_ci_sequence_number%TYPE,
320 p_award_cd igs_en_spa_awd_aim.award_cd%TYPE,
321 p_message_name IN OUT NOCOPY VARCHAR2) AS
322 --
323 -- Change History :
324 -- Who When What
325 -- (reverse chronological order - newest change first)
326 -- Nalin Kumar 23-Nov-2001 Modified the grdpl_ins_new_graduand procedure
327 -- as per the UK Award Aims DLD. Bug ID: 1366899
328 --
329 BEGIN -- grdpl_ins_new_graduand
330 -- 2.1 Create New Graduands
331 DECLARE
332 --
333 -- New code added for the UK Award Aims DLD.
334 --
335 CURSOR c_spaaa IS
336 SELECT spaaa.award_cd,
337 spaaa.start_dt,
338 spaaa.complete_ind,
339 spaaa.conferral_date
340 FROM igs_en_spa_awd_aim spaaa
341 WHERE spaaa.person_id = p_person_id
342 AND spaaa.course_cd = p_course_cd
343 AND spaaa.award_cd = p_award_cd
344 AND ((spaaa.end_dt is NULL OR spaaa.complete_ind = 'Y')
345 OR(spaaa.conferral_date IS NOT NULL))
346 AND NOT EXISTS (
347 SELECT 'x'
348 FROM IGS_GR_GRADUAND gr
349 WHERE gr.person_id = p_person_id
350 AND gr.course_cd = p_course_cd
351 AND gr.grd_cal_type = p_grd_cal_type
352 AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
353 AND gr.award_course_cd = p_course_cd
354 AND gr.award_crs_version_number = p_version_number
355 AND gr.award_cd = spaaa.award_cd)
356 ORDER BY spaaa.start_dt DESC,
357 spaaa.complete_ind DESC;
358
359 --
360 -- Added to fix Bug# 2683072
361 --
362 CURSOR c_spaaa_chk IS
363 SELECT 'X'
364 FROM igs_en_spa_awd_aim spaaa
365 WHERE spaaa.person_id = p_person_id
366 AND spaaa.course_cd = p_course_cd
367 AND spaaa.award_cd = p_award_cd
368 AND NOT EXISTS (
369 SELECT 'x'
370 FROM igs_gr_graduand gr
371 WHERE gr.person_id = p_person_id
372 AND gr.course_cd = p_course_cd
373 AND gr.grd_cal_type = p_grd_cal_type
374 AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
375 AND gr.award_course_cd = p_course_cd
376 AND gr.award_crs_version_number = p_version_number
377 AND gr.award_cd = spaaa.award_cd);
378 l_rec_spaaa_chk c_spaaa_chk%ROWTYPE;
379 --
380 -- End of new code added to fix Bug# 2683072
381 --
382
383 v_graduand_status IGS_GR_STAT.graduand_status%TYPE;
384 v_approval_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE;
385 v_records_update NUMBER DEFAULT 0;
386 v_records_not_found NUMBER DEFAULT 0;
387 v_last_start_dt DATE;
388 v_last_complete_ind VARCHAR2(1);
389
390 l_msg_count NUMBER;
391 l_msg_data VARCHAR2(2000);
392 l_msg_index_out NUMBER;
393 l_app_name VARCHAR2(30) DEFAULT 'IGS';
394 l_message_name VARCHAR2(40);
395 l_hold VARCHAR2(1) := NULL;
396 BEGIN
397 -- Set the default message number
398 p_message_name := NULL;
399 --
400 -- Loop through the Student Program Attempt Award Aim records
401 --
402 FOR v_spaaa_rec IN c_spaaa LOOP
403 --
404 -- If it is the first record OR it has the same start date and completion status as the
405 -- previous record insert a graduand record.
406 --
407 IF (v_spaaa_rec.start_dt = v_last_start_dt AND
408 v_spaaa_rec.complete_ind = v_last_complete_ind) OR
409 v_last_start_dt IS NULL THEN
410 v_last_complete_ind := v_spaaa_rec.complete_ind;
411 v_last_start_dt := v_spaaa_rec.start_dt;
412
413 --
414 -- Determine what the initial graduand status should be
415 IF IGS_GR_VAL_GR.grdp_val_aw_eligible(p_person_id,
416 p_course_cd,
417 p_course_cd,
418 p_version_number,
419 v_spaaa_rec.award_cd,
420 p_message_name) THEN
421
422 v_graduand_status := p_eligible_graduand_status;
423 ELSE
424 v_graduand_status := p_potential_graduand_status;
425 END IF;
426 --
427 -- To change the CREATE_DT value by one second to pass the primary key
428 -- validation for the IGS_GR_GRADUAND table, which is the combination of the PERSON_ID and CREATE_DT.
429 IF p_person_id = v_person_id THEN
430 dbms_lock.sleep(1);
431 ELSE
432 v_person_id := p_person_id;
433 END IF;
434
435 --IF v_spaaa_rec.conferral_date IS NOT NULL AND
436 IF NVL(v_spaaa_rec.complete_ind, 'N') = 'Y' AND
437 /*
438 Added the next AND conditions to consider the case when the Completion Flag is 'Y' in the
439 IGSEN070 form but the user has not passed the 'Gradutated Status' or 'Approval Status for Graduate'-
440 then in this case this Job should not create a new record with 'Conferral Date' mentioned and
441 'Graduand Status' as 'ELIGIBLE' or 'POTANTIAL'.
442 */
443 p_graduand_status IS NOT NULL AND p_approval_status IS NOT NULL THEN
444 DECLARE
445 CURSOR cur_sca IS
446 SELECT sca.course_rqrmnt_complete_ind
447 FROM igs_en_stdnt_ps_att sca
448 WHERE sca.person_id = p_person_id AND
449 sca.course_cd = p_course_cd;
450 rec_sca cur_sca%ROWTYPE;
451 BEGIN
452 OPEN cur_sca;
453 FETCH cur_sca INTO rec_sca;
454 IF NVL(rec_sca.course_rqrmnt_complete_ind, 'N') = 'Y' THEN
455 v_graduand_status := p_graduand_status;
456 v_approval_status := p_approval_status;
457 ELSE
458 v_approval_status := p_graduand_appr_status;
459 END IF;
460 CLOSE cur_sca;
461 END;
462 --*******
463 ELSE
464 -- if the 'Gradutated Status' or 'Approval Status for Graduated' is null then dont consider the 'Completion Flag'.
465 -- Because Conferral Date should not be mentioned with the 'Graduand Status' of 'ELIGIBLE' or 'POTANTIAL'.
466 v_approval_status := p_graduand_appr_status;
467 END IF;
468 --
469 -- Insert IGS_GR_GRADUAND record
470 --
471 DECLARE
472 lv_rowid VARCHAR2(25);
473 lv_create_dt DATE DEFAULT NULL;
474 BEGIN
475 l_org_id := igs_ge_gen_003.get_org_id;
476 l_hold := 'N';
477 IGS_GR_GRADUAND_PKG.INSERT_ROW(
478 X_ROWID => lv_rowid,
479 X_PERSON_ID => p_person_id,
480 X_CREATE_DT => lv_create_dt,
481 X_GRD_CAL_TYPE => p_grd_cal_type,
482 X_GRD_CI_SEQUENCE_NUMBER => p_grd_ci_sequence_number,
483 X_COURSE_CD => p_course_cd,
484 X_AWARD_COURSE_CD => p_course_cd,
485 X_AWARD_CRS_VERSION_NUMBER => p_version_number,
486 X_AWARD_CD => v_spaaa_rec.award_cd,
487 X_GRADUAND_STATUS => v_graduand_status,
488 X_GRADUAND_APPR_STATUS => v_approval_status,
489 X_S_GRADUAND_TYPE => NULL,
490 X_GRADUATION_NAME => IGS_GR_GEN_001.grdp_get_grad_name(p_person_id),
491 X_PROXY_AWARD_IND => NULL,
492 X_PROXY_AWARD_PERSON_ID => NULL,
493 X_PREVIOUS_QUALIFICATIONS => NULL,
494 X_CONVOCATION_MEMBERSHIP_IND => NULL,
495 X_SUR_FOR_COURSE_CD => NULL,
496 X_SUR_FOR_CRS_VERSION_NUMBER => NULL,
497 X_SUR_FOR_AWARD_CD => NULL,
498 X_COMMENTS => NULL,
499 X_MODE => 'R',
500 X_ORG_ID => l_org_id,
501 X_ATTRIBUTE_CATEGORY => NULL,
502 X_ATTRIBUTE1 => NULL,
503 X_ATTRIBUTE2 => NULL,
504 X_ATTRIBUTE3 => NULL,
505 X_ATTRIBUTE4 => NULL,
506 X_ATTRIBUTE5 => NULL,
507 X_ATTRIBUTE6 => NULL,
508 X_ATTRIBUTE7 => NULL,
509 X_ATTRIBUTE8 => NULL,
510 X_ATTRIBUTE9 => NULL,
511 X_ATTRIBUTE10 => NULL,
512 X_ATTRIBUTE11 => NULL,
513 X_ATTRIBUTE12 => NULL,
514 X_ATTRIBUTE13 => NULL,
515 X_ATTRIBUTE14 => NULL,
516 X_ATTRIBUTE15 => NULL,
517 X_ATTRIBUTE16 => NULL,
518 X_ATTRIBUTE17 => NULL,
519 X_ATTRIBUTE18 => NULL,
520 X_ATTRIBUTE19 => NULL,
521 X_ATTRIBUTE20 => NULL
522 );
523 EXCEPTION
524 WHEN OTHERS THEN
525 --
526 -- Check if there was a hold related problem, if yes then display proper error message.
527 -- Added to fix bug# 2690151
528 --
529 l_message_name := NULL;
530 IGS_GE_MSG_STACK.GET(IGS_GE_MSG_STACK.COUNT_MSG,'T',l_msg_data,l_msg_index_out);
531 FND_MESSAGE.PARSE_ENCODED(l_msg_data, l_app_name, l_message_name);
532 IF NVL(l_message_name,'NULL') = 'IGS_GR_CANNOT_BE_APPROVED' THEN
533 l_hold := 'Y';
534 FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_GRD_HOLD');
535 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
536 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET);
537 EXIT; --If hold is there then don't process any other award...
538 ELSIF l_message_name IS NOT NULL THEN
539 FND_MESSAGE.SET_NAME('IGS',l_message_name);
540 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
541 EXIT; --Dont EXIT; Let the system process another SPAAA record for the student.
542 ELSE
543 --IGS_UC_ERROR_PROC_DATA - Unexpected error encountered while processing VIEW data
544 FND_MESSAGE.SET_NAME('IGS','IGS_UC_ERROR_PROC_DATA');
545 FND_MESSAGE.SET_TOKEN('VIEW',l_person_number);
546 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
547 EXIT;
548 --RAISE; --Dont raise any error... log it in log file and proceed with next student...
549 END IF;
550 END;
551 FND_MESSAGE.SET_NAME('IGS','IGS_GR_GRD_REC_CRTED');
552 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
553 FND_MESSAGE.SET_TOKEN('COURSE',p_course_cd);
554 FND_MESSAGE.SET_TOKEN('AWARD',v_spaaa_rec.award_cd);
555 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
556 -- End of new code added as per the bug# 2690151
557 v_records_update := v_records_update + 1;
558 END IF;
559 END LOOP;
560
561 --
562 -- If records were inserted from the Award Aims table return. Otherwise insert records from the
563 -- Program Award table
564 --
565 IF v_records_update > 0 THEN
566 RETURN;
567 END IF;
568
569 --
570 -- Added to fix Bug# 2683072
571 --
572 OPEN c_spaaa_chk;
573 FETCH c_spaaa_chk INTO l_rec_spaaa_chk;
574 IF c_spaaa_chk%FOUND THEN
575 CLOSE c_spaaa_chk;
576 IF NVL(l_hold,'N') <> 'Y' THEN --"l_hold = 'N'" Indicates no Hold related problems...
577 FND_MESSAGE.SET_NAME('IGS','IGS_PR_PRG');
578 l_person_number := l_person_number||' '||FND_MESSAGE.GET()||': '||p_course_cd||' - '||p_award_cd;
579 FND_MESSAGE.SET_NAME('IGS','IGS_GR_AWD_AIM_SETUP');
580 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
581 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
582 END IF;
583 RETURN;
584 END IF;
585 CLOSE c_spaaa_chk;
586 --
587 -- End of new code added as per the Bug# 2683072
588 --
589
590 DECLARE
591 CURSOR cur_spaa_gr_dif IS
592 SELECT 'X'
593 FROM igs_en_spa_awd_aim spaaa
594 WHERE spaaa.person_id = p_person_id
595 AND spaaa.course_cd = p_course_cd
596 AND spaaa.award_cd = p_award_cd
597 AND EXISTS (
598 SELECT 'x'
599 FROM igs_gr_graduand gr
600 WHERE gr.person_id = p_person_id
601 AND gr.course_cd = p_course_cd
602 AND gr.grd_cal_type = p_grd_cal_type
603 AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
604 AND gr.award_course_cd = p_course_cd
605 AND gr.award_crs_version_number = p_version_number
606 AND gr.award_cd = spaaa.award_cd);
607 rec_spaa_gr_dif cur_spaa_gr_dif%ROWTYPE;
608 BEGIN
609 IF v_records_update = 0 AND NVL(l_hold,'N') <> 'Y' THEN
610 OPEN cur_spaa_gr_dif;
611 FETCH cur_spaa_gr_dif INTO rec_spaa_gr_dif;
612 IF cur_spaa_gr_dif%NOTFOUND THEN
613 FND_MESSAGE.SET_NAME('IGS','IGS_GR_NO_REC_CRETD');
614 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
615 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
616 ELSIF cur_spaa_gr_dif%FOUND THEN
617 FND_MESSAGE.SET_NAME('IGS','IGS_GR_GRD_REC_EXT');
618 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
619 FND_MESSAGE.SET_TOKEN('COURSE',p_course_cd);
620 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
621 END IF;
622 CLOSE cur_spaa_gr_dif;
623 END IF;
624 END;
625 --
626 -- End of new code added to fix bug# 2690151
627 --
628 EXCEPTION
629 WHEN OTHERS THEN
630 IF c_spaaa%ISOPEN THEN
631 CLOSE c_spaaa;
632 END IF;
633 RAISE;
634 END;
635 EXCEPTION
636 WHEN OTHERS THEN
637 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
638 App_Exception.Raise_Exception;
639 END grdpl_ins_new_graduand;
640 -------------------------------------------------------------------
641 BEGIN
642 -- 1. Check parameters
643 IF (p_grd_cal_type IS NULL AND
644 p_grd_ci_sequence_number IS NOT NULL) OR
645 (p_grd_ci_sequence_number IS NULL AND
646 p_grd_cal_type IS NOT NULL) OR
647 p_nominated_completion IS NULL OR
648 p_derived_completion IS NULL OR
649 p_restrict_rqrmnt_complete IS NULL OR
650 p_potential_graduand_status IS NULL OR
651 p_eligible_graduand_status IS NULL OR
652 p_graduand_appr_status IS NULL THEN
653 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
654 --App_Exception.Raise_Exception;
655 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
656 RETURN;
657 END IF;
658 -- Validate completion criterion
659 IF p_nominated_completion = 'N' AND
660 p_derived_completion = 'N' THEN
661 Fnd_Message.Set_Name('IGS', 'IGS_GR_NOMIN_DERV_COMPL_SET');
662 --App_Exception.Raise_Exception;
663 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
664 RETURN;
665 END IF;
666 --
667 -- Validate the status values provided
668 --
669 grdpl_val_graduand_status;
670 --
671 --
672
673 -- Create the SPAA records for all defaulted Awards at the Program level.
674 -- This should happen only when there is not even a single SPAA record for the Student.
675 FOR v_crd_sca_rec IN c_crd_sca LOOP
676 --Check if the SPAA record exists or not. If no SPAA record exists
677 --for the given Person Number and Course Code combination then create
678 --the SPAA record for each defaulted Awards (defined at the Program Version Level).
679 --Added as part of the Program Completion Validation Build; Bug# 3129913; Nalin Kumar;
680 OPEN cur_spaaa(v_crd_sca_rec.person_id, v_crd_sca_rec.course_cd);
681 FETCH cur_spaaa INTO rec_spaaa;
682 IF cur_spaaa%NOTFOUND THEN
683 CLOSE cur_spaaa;
684 DECLARE
685 --Fetch all defaulted Awards for the Program Version.
686 CURSOR cur_def_awds IS
687 SELECT caw.award_cd,
688 grd.grading_schema_cd,
689 grd.gs_version_number
690 FROM igs_ps_award caw,
691 igs_ps_awd grd
692 WHERE caw.course_cd = v_crd_sca_rec.course_cd AND
693 caw.version_number = v_crd_sca_rec.version_number AND
694 caw.default_ind = 'Y' AND
695 caw.closed_ind = 'N' AND
696 caw.award_cd = grd.award_cd(+);
697 l_row_id VARCHAR2(30);
698 BEGIN
699 --Loop through all Defaulted Awards and create the SPAA records.
700 FOR rec_def_awds IN cur_def_awds LOOP
701 igs_en_spa_awd_aim_pkg.insert_row(
702 X_ROWID => l_row_id,
703 X_PERSON_ID => v_crd_sca_rec.person_id,
704 X_COURSE_CD => v_crd_sca_rec.course_cd,
705 X_AWARD_CD => rec_def_awds.award_cd,
706 X_START_DT => v_crd_sca_rec.commencement_dt,
707 X_END_DT => NULL,
708 X_COMPLETE_IND => 'N',
709 X_CONFERRAL_DATE => NULL,
710 X_MODE => 'R',
711 X_AWARD_MARK => NULL,
712 X_AWARD_GRADE => NULL,
713 X_GRADING_SCHEMA_CD => rec_def_awds.grading_schema_cd,
714 X_GS_VERSION_NUMBER => rec_def_awds.gs_version_number);
715 l_row_id := NULL;
716 END LOOP;
717 END;
718 ELSE
719 CLOSE cur_spaaa;
720 END IF;
721 END LOOP;
722
723 -- 2. Find Potential Graduands
724 -- establish the ceremony rounds to process within.
725 -- get the completion period to target potential graduands.
726 -- get the target potential IGS_GR_GRADUAND details.
727 l_log_person_id := 'NULL';
728 l_chk_hold := 'NULL';
729
730 FOR v_crd_sca_rec IN c_crd_sca LOOP
731 -- check the student hasn't already graduated, articulated
732 -- or declined in an IGS_PS_AWD for the IGS_PS_COURSE
733 --Get the Person Number
734 OPEN get_person_num(v_crd_sca_rec.person_id);
735 FETCH get_person_num INTO l_person_number;
736 CLOSE get_person_num;
737
738 --Log this message only once for a person.
739 IF l_log_person_id = 'NULL' OR
740 l_log_person_id <> v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd THEN
741 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
742 FND_MESSAGE.SET_NAME('IGS','IGS_GR_PROCESSING');
743 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
744 FND_MESSAGE.SET_TOKEN('COURSE',v_crd_sca_rec.course_cd);
745 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
746 l_log_person_id := v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd;
747 END IF;
748
749 OPEN c_gr(
750 v_crd_sca_rec.person_id,
751 v_crd_sca_rec.course_cd,
752 v_crd_sca_rec.award_cd);
753 FETCH c_gr INTO v_gr_exists;
754 IF c_gr%NOTFOUND THEN
755 CLOSE c_gr;
756 --Check the Graduation Block.
757 IF p_graduand_appr_status = 'APPROVED' THEN
758 IF l_chk_hold = 'NULL' OR
759 l_chk_hold <> v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd THEN
760 IF igs_gr_val_gr.enrp_val_encmb_efct(
761 p_person_id => v_crd_sca_rec.person_id,
762 p_course_cd => v_crd_sca_rec.course_cd,
763 p_effective_dt => SYSDATE,
764 p_encmb_effect_type => 'GRAD_BLK',
765 p_message_name => v_message_name) = TRUE THEN
766 FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_GRD_HOLD');
767 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
768 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET);
769 l_chk_hold := v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd;
770 END IF;
771 END IF;
772 END IF;
773 IF l_chk_hold = 'NULL' OR
774 l_chk_hold <> v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd THEN
775 -- Do 2.1 Create New IGS_GR_GRADUAND
776 grdpl_ins_new_graduand(
777 v_crd_sca_rec.person_id,
778 v_crd_sca_rec.course_cd,
779 v_crd_sca_rec.version_number,
780 v_crd_sca_rec.grd_cal_type,
781 v_crd_sca_rec.grd_ci_sequence_number,
782 v_crd_sca_rec.award_cd,
783 v_message_name);
784 END IF;
785 ELSE
786 FND_MESSAGE.SET_NAME('IGS','IGS_GR_GRD_REC_EXT');
787 FND_MESSAGE.SET_TOKEN('PERSON',l_person_number);
788 FND_MESSAGE.SET_TOKEN('COURSE',v_crd_sca_rec.course_cd);
789 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||FND_MESSAGE.GET());
790 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
791 CLOSE c_gr;
792 END IF;
793 END LOOP; -- crd_sca
794 EXCEPTION
795 WHEN OTHERS THEN
796 IF c_crd_sca%ISOPEN THEN
797 CLOSE c_crd_sca;
798 END IF;
799 IF c_gr%ISOPEN THEN
800 CLOSE c_gr;
801 END IF;
802 RAISE;
803 END;
804 EXCEPTION
805 WHEN OTHERS THEN
806 RETCODE := 2;
807 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
808 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
809 END grdp_ins_graduand;
810
811
812 PROCEDURE grdp_ins_gr_hist(
813 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814 p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815 p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816 p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817 p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818 p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819 p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820 p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821 p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822 p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823 p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824 p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825 p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826 p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
827 p_old_honours_level VARCHAR2 DEFAULT NULL,
828 p_new_honours_level VARCHAR2 DEFAULT NULL,
829 p_old_conferral_dt DATE DEFAULT NULL,
830 p_new_conferral_dt DATE DEFAULT NULL,
831 p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832 p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833 p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834 p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835 p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836 p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837 p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838 p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839 p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840 p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841 p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842 p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843 p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844 p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845 p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846 p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847 p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848 p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849 p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850 p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851 p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852 p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853 p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854 p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855 p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856 p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857 p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858 p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859 AS
860 l_org_id NUMBER(15);
861 BEGIN -- grdp_ins_gr_hist
862 -- Insert IGS_GR_GRADUAND history(IGS_GR_GRADUAND_HIST)
863 DECLARE
864 v_gr_rec IGS_GR_GRADUAND_HIST%ROWTYPE;
865 v_create_history BOOLEAN := FALSE;
866
867 BEGIN -- If any of the old values (p_old_<column_name>) are
868 -- different from the associated new values (p_new_<column_name>)
869 -- (with the exception of the last_update_date and last_updated_by columns)
870 -- then create a graduand_history record with the old values
871 -- (p_old_<column_name>). Do not set the last_updated_by and last_update_date
872 -- columns when creating the history recor
873 IF p_new_grd_cal_type <> p_old_grd_cal_type THEN
874 v_gr_rec.grd_cal_type := p_old_grd_cal_type;
875 v_create_history := TRUE;
876 END IF;
877 IF p_new_grd_ci_sequence_number <> p_old_grd_ci_sequence_number THEN
878 v_gr_rec.grd_ci_sequence_number := p_old_grd_ci_sequence_number;
879 v_create_history := TRUE;
880 END IF;
881 IF NVL(p_new_course_cd,'NULL') <> NVL(p_old_course_cd,'NULL') THEN
882 v_gr_rec.course_cd := p_old_course_cd;
883 v_create_history := TRUE;
884 END IF;
885 IF NVL(p_new_award_course_cd,'NULL') <> NVL(p_old_award_course_cd,'NULL') THEN
886 v_gr_rec.award_course_cd := p_old_award_course_cd;
887 v_create_history := TRUE;
888 END IF;
889 IF NVL(p_new_award_crs_version_number,0) <>
890 NVL(p_old_award_crs_version_number,0) THEN
891 v_gr_rec.award_crs_version_number := p_old_award_crs_version_number;
892 v_create_history := TRUE;
893 END IF;
894 IF p_new_award_cd <> p_old_award_cd THEN
895 v_gr_rec.award_cd := p_old_award_cd;
896 v_create_history := TRUE;
897 END IF;
898 IF p_new_graduand_status <> p_old_graduand_status THEN
899 v_gr_rec.graduand_status := p_old_graduand_status;
900 v_create_history := TRUE;
901 END IF;
902 IF p_new_graduand_appr_status <> p_old_graduand_appr_status THEN
903 v_gr_rec.graduand_appr_status := p_old_graduand_appr_status;
904 v_create_history := TRUE;
905 END IF;
906 IF p_new_s_graduand_type <> p_old_s_graduand_type THEN
907 v_gr_rec.s_graduand_type := p_old_s_graduand_type;
908 v_create_history := TRUE;
909 END IF;
910 IF p_new_graduation_name <> p_old_graduation_name THEN
911 v_gr_rec.graduation_name := p_old_graduation_name;
912 v_create_history := TRUE;
913 END IF;
914 IF p_new_proxy_award_ind <> p_old_proxy_award_ind THEN
915 v_gr_rec.proxy_award_ind := p_old_proxy_award_ind;
916 v_create_history := TRUE;
917 END IF;
918 IF NVL(p_new_proxy_award_person_id,0) <>
919 NVL(p_old_proxy_award_person_id,0) THEN
920 v_gr_rec.proxy_award_ind := p_old_proxy_award_ind;
921 END IF;
922 IF NVL(p_new_previous_qualifications,'NULL') <>
923 NVL(p_old_previous_qualifications,'NULL') THEN
924 v_gr_rec.previous_qualifications := p_old_previous_qualifications;
925 v_create_history := TRUE;
926 END IF;
927 IF p_new_convocation_memb_ind <> p_old_convocation_memb_ind THEN
928 v_gr_rec.convocation_membership_ind := p_old_convocation_memb_ind;
929 v_create_history := TRUE;
930 END IF;
931 IF NVL(p_new_sur_for_course_cd,'NULL') <>
932 NVL(p_old_sur_for_course_cd,'NULL') THEN
933 v_gr_rec.sur_for_course_cd := p_old_sur_for_course_cd;
934 v_create_history := TRUE;
935 END IF;
936 IF NVL(p_new_sur_for_crs_version_numb,0) <>
937 NVL(p_old_sur_for_crs_version_numb,0) THEN
938 v_gr_rec.sur_for_crs_version_number := p_old_sur_for_crs_version_numb;
939 v_create_history := TRUE;
940 END IF;
941 IF NVL(p_new_sur_for_award_cd,'NULL') <>
942 NVL(p_old_sur_for_award_cd,'NULL') THEN
943 v_gr_rec.sur_for_award_cd := p_old_sur_for_award_cd;
944 v_create_history := TRUE;
945 END IF;
946 IF NVL(p_new_comments,'NULL') <> NVL(p_old_comments,'NULL') THEN
947 v_gr_rec.comments := p_old_comments;
948 v_create_history := TRUE;
949 END IF;
950 -- Insert history rec.
951 IF v_create_history THEN
952 v_gr_rec.person_id := p_person_id;
953 v_gr_rec.create_dt := p_create_dt;
954 v_gr_rec.hist_start_dt := p_old_update_on;
955 v_gr_rec.hist_end_dt := p_new_update_on;
956 v_gr_rec.hist_who := p_old_update_who;
957 DECLARE
958 lv_rowid VARCHAR2(25);
959 BEGIN
960 l_org_id := igs_ge_gen_003.get_org_id;
961 IGS_GR_GRADUAND_HIST_PKG.INSERT_ROW(
962 X_ROWID => lv_rowid,
963 X_PERSON_ID => v_gr_rec.person_id,
964 X_CREATE_DT => v_gr_rec.create_dt,
965 X_HIST_START_DT => v_gr_rec.hist_start_dt,
966 X_HIST_END_DT => v_gr_rec.hist_end_dt,
967 X_HIST_WHO => v_gr_rec.hist_who,
968 X_GRD_CAL_TYPE => v_gr_rec.grd_cal_type,
969 X_GRD_CI_SEQUENCE_NUMBER => v_gr_rec.grd_ci_sequence_number,
970 X_COURSE_CD => v_gr_rec.course_cd,
971 X_AWARD_COURSE_CD => v_gr_rec.award_course_cd,
972 X_AWARD_CRS_VERSION_NUMBER => v_gr_rec.award_crs_version_number,
973 X_AWARD_CD => v_gr_rec.award_cd,
974 X_GRADUAND_STATUS => v_gr_rec.graduand_status,
975 X_GRADUAND_APPR_STATUS => v_gr_rec.graduand_appr_status,
976 X_S_GRADUAND_TYPE => v_gr_rec.s_graduand_type,
977 X_GRADUATION_NAME => v_gr_rec.graduation_name,
978 X_PROXY_AWARD_IND => v_gr_rec.proxy_award_ind,
979 X_PROXY_AWARD_PERSON_ID => v_gr_rec.proxy_award_person_id,
980 X_PREVIOUS_QUALIFICATIONS => v_gr_rec.previous_qualifications,
981 X_CONVOCATION_MEMBERSHIP_IND => v_gr_rec.convocation_membership_ind,
982 X_SUR_FOR_COURSE_CD => v_gr_rec.sur_for_course_cd,
983 X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_rec.sur_for_crs_version_number,
984 X_SUR_FOR_AWARD_CD => v_gr_rec.sur_for_award_cd,
985 X_COMMENTS => v_gr_rec.comments,
986 X_MODE => 'R',
987 X_ORG_ID => l_org_id
988 );
989 END;
990 END IF;
991 END;
992 EXCEPTION
993 WHEN OTHERS THEN
994 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
995 App_Exception.Raise_Exception;
996 END grdp_ins_gr_hist;
997
998
999 PROCEDURE grdp_prc_gac(
1000 errbuf out NOCOPY varchar2,
1001 retcode out NOCOPY number,
1002 p_ceremony_round IN VARCHAR2,
1003 p_lctn_cd IN VARCHAR2 ,
1004 p_grdnd_status IN VARCHAR2 ,
1005 p_resolve_stalemate_type IN VARCHAR2 ,
1006 p_ignore_unit_sets_ind IN VARCHAR2 ,
1007 p_org_id NUMBER)
1008 AS
1009 p_grd_cal_type igs_ca_inst.cal_type%type;
1010 p_grd_ci_sequence_number igs_ca_inst.sequence_number%type;
1011 p_location_cd IGS_AD_LOCATION.LOCATION_CD%TYPE;
1012 p_graduand_status IGS_GR_STAT.GRADUAND_STATUS%TYPE;
1013
1014 BEGIN -- grdp_prc_gac
1015 -- This process manages the initial allocation of IGS_GR_GRADUAND records to
1016 -- ceremonies by creating IGS_GR_AWD_CRMN records and the
1017 -- re-alloaction of graduands when IGS_GR_AWD_CEREMONY and
1018 -- IGS_GR_AWD_CRM_US_GP records are closed.
1019 -- The process finds closed IGS_GR_AWD_CEREMONY records for the specified
1020 -- IGS_GR_CRMN_ROUND, calls GENP_PRC_AWC_CLOSE to re-allocate any associated
1021 -- graduands.
1022 -- The process finds closed IGS_GR_AWD_CRM_US_GP records for the specified
1023 -- IGS_GR_CRMN_ROUND, calls GENP_PRC_ACUSG_CLOSE to re-allocate any associated
1024 -- graduands.
1025 -- The process finds any IGS_GR_GRADUAND records for the specified IGS_GR_CRMN_ROUND,
1026 -- location_cd, and IGS_GR_STAT which do not have an existing
1027 -- IGS_GR_AWD_CRMN record and calls GENP_INS_GAC to determine if a
1028 -- ceremony exists suitable for the IGS_GR_GRADUAND and if one is suitable create
1029 -- a IGS_GR_AWD_CRMN record linking the IGS_GR_GRADUAND to it.
1030
1031 --
1032 -- Change History :
1033 -- Who When What
1034 -- (reverse chronological order - newest change first)
1035 --
1036 -- Nalin Kumar 18-DEC-2002 Modified this procedure to fix Bug# 2690151.
1037 -- Added the code to log the parameters value in the log file.
1038 --
1039
1040 --Block for Parameter Validation/Splitting of Parameters
1041 retcode:=0;
1042 igs_ge_gen_003.set_org_id(p_org_id);
1043 p_location_cd := NVL(p_lctn_cd,'%');
1044 p_graduand_status := NVL(p_grdnd_status,'%');
1045
1046
1047 BEGIN
1048 p_grd_cal_type := RTRIM(SUBSTR(p_ceremony_round, 101, 10));
1049 p_grd_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_ceremony_round, 112, 6)));
1050 END;
1051 --End of Block for Parameter Validation/Splitting of Parameters
1052
1053 DECLARE
1054 cst_surrender VARCHAR2(10) := 'SURRENDER';
1055 cst_attending VARCHAR2(10) := 'ATTENDING';
1056 cst_unknown VARCHAR2(10) := 'UNKNOWN';
1057 cst_deferred VARCHAR2(10) := 'DEFERRED';
1058 e_resource_busy_exception EXCEPTION;
1059 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54 );
1060 v_message_name VARCHAR2(30);
1061 v_exit_loop BOOLEAN := FALSE;
1062
1063 CURSOR c_awc IS
1064 SELECT awc.ceremony_number,
1065 awc.award_course_cd,
1066 awc.award_crs_version_number,
1067 awc.award_cd
1068 FROM IGS_GR_AWD_CEREMONY awc
1069 WHERE awc.grd_cal_type = p_grd_cal_type AND
1070 awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1071 awc.closed_ind = 'Y';
1072 CURSOR c_acusg IS
1073 SELECT acusg.ceremony_number,
1074 acusg.award_course_cd,
1075 acusg.award_crs_version_number,
1076 acusg.award_cd,
1077 acusg.us_group_number
1078 FROM IGS_GR_AWD_CRM_US_GP acusg
1079 WHERE acusg.grd_cal_type = p_grd_cal_type AND
1080 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1081 acusg.closed_ind = 'Y';
1082 CURSOR c_gr IS
1083 SELECT gr.person_id,
1084 gr.create_dt,
1085 gr.s_graduand_type,
1086 gr.grd_cal_type,
1087 gr.grd_ci_sequence_number
1088 FROM IGS_GR_GRADUAND gr,
1089 IGS_GR_STAT gst,
1090 IGS_EN_STDNT_PS_ATT sca
1091 WHERE gr.grd_cal_type = p_grd_cal_type AND
1092 gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1093 gr.graduand_status = gst.graduand_status AND
1094 gst.s_graduand_status <> cst_surrender AND
1095 gst.graduand_status like p_graduand_status AND
1096 gr.s_graduand_type IN ( cst_attending,
1097 cst_unknown,
1098 cst_deferred) AND
1099 gr.person_id = sca.person_id AND
1100 gr.course_cd = sca.course_cd AND
1101 sca.location_cd like p_location_cd AND
1102 NOT EXISTS
1103 (SELECT 'X'
1104 FROM IGS_GR_AWD_CRMN gac
1105 WHERE gac.person_id = gr.person_id AND
1106 gac.create_dt = gr.create_dt);
1107 --
1108 -- sepalani Bug# 5074150
1109 --
1110 CURSOR c_gr_upd (
1111 cp_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE,
1112 cp_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE) IS
1113 SELECT rowid, gr.*
1114 FROM IGS_GR_GRADUAND_ALL gr
1115 WHERE gr.person_id = cp_person_id AND
1116 gr.create_dt = cp_create_dt
1117 FOR UPDATE OF s_graduand_type NOWAIT;
1118 v_gr_del c_gr_upd%ROWTYPE;
1119 BEGIN
1120 --
1121 --Log the Parameters value in the log file. This is to fix Bug# 2690151
1122 --
1123 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_ANC_LOG_PARM');
1124 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET()||':');
1125 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_cal_type = '||p_grd_cal_type);
1126 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_ci_sequence_number = '||p_grd_ci_sequence_number);
1127 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_location_cd = '||p_location_cd);
1128 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_graduand_status = '||p_graduand_status);
1129 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_resolve_stalemate_type = '||p_resolve_stalemate_type);
1130 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_ignore_unit_sets_ind = '||p_ignore_unit_sets_ind);
1131 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1132
1133 -- 1. Check parameters :
1134 IF p_grd_cal_type IS NULL OR
1135 p_grd_ci_sequence_number IS NULL OR
1136 p_location_cd IS NULL OR
1137 p_graduand_status IS NULL OR
1138 p_resolve_stalemate_type IS NULL OR
1139 p_ignore_unit_sets_ind IS NULL THEN
1140 Fnd_Message.Set_Name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
1141 App_Exception.Raise_Exception;
1142 END IF;
1143 -- 2.1 Find any closed IGS_GR_AWD_CEREMONY records for the specified
1144 -- IGS_GR_CRMN_ROUND and loop through them.
1145 FOR v_awc_rec IN c_awc LOOP
1146 -- 2.2 Call GENP_PRC_AWC_CLOSE to process the graduands for the
1147 -- IGS_GR_AWD_CEREMONY record and then delete it.
1148 IF IGS_GR_PRC_GAC.grdp_prc_awc_close(
1149 p_grd_cal_type,
1150 p_grd_ci_sequence_number,
1151 v_awc_rec.ceremony_number,
1152 v_awc_rec.award_course_cd,
1153 v_awc_rec.award_crs_version_number,
1154 v_awc_rec.award_cd,
1155 p_resolve_stalemate_type,
1156 p_ignore_unit_sets_ind,
1157 v_message_name) = FALSE THEN
1158 Fnd_Message.Set_Name('IGS', v_message_name);
1159 App_Exception.Raise_Exception;
1160 END IF;
1161 END LOOP; -- c_awc
1162 -- 3.1 Find closed IGS_GR_AWD_CRM_US_GP records for the specified
1163 -- IGS_GR_CRMN_ROUND and loop through them.
1164 FOR v_acusg_rec IN c_acusg LOOP
1165 -- 3.2 Call GENP_PRC_ACUSG_CLOSE to process the graduands for the
1166 -- IGS_GR_AWD_CRM_US_GP record and then delete it.
1167 IF IGS_GR_PRC_GAC.grdp_prc_acusg_close(
1168 p_grd_cal_type,
1169 p_grd_ci_sequence_number,
1170 v_acusg_rec.ceremony_number,
1171 v_acusg_rec.award_course_cd,
1172 v_acusg_rec.award_crs_version_number,
1173 v_acusg_rec.award_cd,
1174 v_acusg_rec.us_group_number,
1175 p_resolve_stalemate_type,
1176 p_ignore_unit_sets_ind,
1177 v_message_name) = FALSE THEN
1178 Fnd_Message.Set_Name('IGS', v_message_name);
1179 App_Exception.Raise_Exception;
1180 END IF;
1181 END LOOP; -- c_acusg
1182 -- 4.1 Find any IGS_GR_GRADUAND records matching the IGS_GR_CRMN_ROUND, location_cd
1183 -- and IGS_GR_STAT specified which do not have an existing
1184 -- IGS_GR_AWD_CRMN record and loop through them.
1185 FOR v_gr_rec IN c_gr LOOP
1186 SAVEPOINT sp_prc_gac;
1187 -- 4.2 If the IGS_GR_GRADUAND has been deferred into this IGS_GR_CRMN_ROUND update the
1188 -- s_graduand_type from DEFERRED to UNKOWN. DO NOT COMMIT THIS UPDATE.
1189 IF v_gr_rec.s_graduand_type = cst_deferred THEN
1190 BEGIN
1191 OPEN c_gr_upd(
1192 v_gr_rec.person_id,
1193 v_gr_rec.create_dt);
1194 FETCH c_gr_upd INTO v_gr_del;
1195 -- 3.Delete the existing IGS_GR_AWD_CRMN record
1196 IF (c_gr_upd%FOUND) THEN
1197 IGS_GR_GRADUAND_PKG.UPDATE_ROW(
1198 X_ROWID => v_gr_del.rowid,
1199 X_PERSON_ID => v_gr_del.person_id,
1200 X_CREATE_DT => v_gr_del.create_dt,
1201 X_GRD_CAL_TYPE => v_gr_del.grd_cal_type,
1202 X_GRD_CI_SEQUENCE_NUMBER => v_gr_del.grd_ci_sequence_number,
1203 X_COURSE_CD => v_gr_del.course_cd,
1204 X_AWARD_COURSE_CD => v_gr_del.award_course_cd,
1205 X_AWARD_CRS_VERSION_NUMBER => v_gr_del.award_crs_version_number,
1206 X_AWARD_CD => v_gr_del.award_cd,
1207 X_GRADUAND_STATUS => v_gr_del.graduand_status,
1208 X_GRADUAND_APPR_STATUS => v_gr_del.graduand_appr_status,
1209 X_S_GRADUAND_TYPE => cst_unknown,
1210 X_GRADUATION_NAME => v_gr_del.graduation_name,
1211 X_PROXY_AWARD_IND => v_gr_del.proxy_award_ind,
1212 X_PROXY_AWARD_PERSON_ID => v_gr_del.proxy_award_person_id,
1213 X_PREVIOUS_QUALIFICATIONS => v_gr_del.previous_qualifications,
1214 X_CONVOCATION_MEMBERSHIP_IND => v_gr_del.convocation_membership_ind,
1215 X_SUR_FOR_COURSE_CD => v_gr_del.sur_for_course_cd,
1216 X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_del.sur_for_crs_version_number,
1217 X_SUR_FOR_AWARD_CD => v_gr_del.sur_for_award_cd,
1218 X_COMMENTS => v_gr_del.comments,
1219 X_MODE => 'R',
1220 X_ATTRIBUTE_CATEGORY => v_gr_del.attribute_category,
1221 X_ATTRIBUTE1 => v_gr_del.attribute1,
1222 X_ATTRIBUTE2 => v_gr_del.attribute2,
1223 X_ATTRIBUTE3 => v_gr_del.attribute3,
1224 X_ATTRIBUTE4 => v_gr_del.attribute4,
1225 X_ATTRIBUTE5 => v_gr_del.attribute5,
1226 X_ATTRIBUTE6 => v_gr_del.attribute6,
1227 X_ATTRIBUTE7 => v_gr_del.attribute7,
1228 X_ATTRIBUTE8 => v_gr_del.attribute8,
1229 X_ATTRIBUTE9 => v_gr_del.attribute9,
1230 X_ATTRIBUTE10 => v_gr_del.attribute10,
1231 X_ATTRIBUTE11 => v_gr_del.attribute11,
1232 X_ATTRIBUTE12 => v_gr_del.attribute12,
1233 X_ATTRIBUTE13 => v_gr_del.attribute13,
1234 X_ATTRIBUTE14 => v_gr_del.attribute14,
1235 X_ATTRIBUTE15 => v_gr_del.attribute15,
1236 X_ATTRIBUTE16 => v_gr_del.attribute16,
1237 X_ATTRIBUTE17 => v_gr_del.attribute17,
1238 X_ATTRIBUTE18 => v_gr_del.attribute18,
1239 X_ATTRIBUTE19 => v_gr_del.attribute19,
1240 X_ATTRIBUTE20 => v_gr_del.attribute20
1241 );
1242 END IF;
1243 CLOSE c_gr_upd;
1244 EXCEPTION
1245 WHEN e_resource_busy_exception THEN
1246 IF c_gr_upd%ISOPEN THEN
1247 CLOSE c_gr_upd;
1248 END IF;
1249 ROLLBACK TO sp_prc_gac;
1250 Fnd_Message.Set_Name('IGS', 'IGS_GR_CANNOT_UPDATE_GRAD_REC');
1251 App_Exception.Raise_Exception;
1252 WHEN OTHERS THEN
1253 ROLLBACK TO sp_prc_gac;
1254 RAISE;
1255 END;
1256 END IF;
1257 -- 4.3 Call GENP_PRC_GAC_CRMNY for each IGS_GR_GRADUAND record found which will
1258 -- create a IGS_GR_AWD_CRMN record allocating it to an appropriate
1259 -- ceremony if one is available.
1260 IF IGS_GR_PRC_GAC.grdp_ins_gac(
1261 v_gr_rec.person_id,
1262 v_gr_rec.create_dt ,
1263 v_gr_rec.grd_cal_type,
1264 v_gr_rec.grd_ci_sequence_number,
1265 NULL,
1266 NULL,
1267 'Y',
1268 NULL,
1269 NULL,
1270 NULL,
1271 NULL,
1272 NULL,
1273 'N',
1274 NULL,
1275 p_resolve_stalemate_type,
1276 p_ignore_unit_sets_ind,
1277 v_message_name) = FALSE THEN
1278 Fnd_Message.Set_Name('IGS', v_message_name);
1279 App_Exception.Raise_Exception;
1280 END IF;
1281 -- 4.4 If the IGS_GR_GRADUAND has been deferred into this IGS_GR_CRMN_ROUND and they
1282 -- have not been placed in a ceremony, ROLLBACK the change to the
1283 -- s_graduand_type to DEFERRED. If they were placed in a ceremony round
1284 -- this change would have been commited on the insert of the
1285 -- IGS_GR_AWD_CRMN record.
1286 IF v_gr_rec.s_graduand_type = cst_deferred THEN
1287 ROLLBACK;
1288 END IF;
1289 END LOOP; -- c_gr
1290 -- 5. Return no error:
1291 RETURN;
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF (c_awc%ISOPEN) THEN
1295 CLOSE c_awc;
1296 END IF;
1297 IF (c_acusg%ISOPEN) THEN
1298 CLOSE c_acusg;
1299 END IF;
1300 IF (c_gr%ISOPEN) THEN
1301 CLOSE c_gr;
1302 END IF;
1303 IF (c_gr_upd%ISOPEN) THEN
1304 CLOSE c_gr_upd;
1305 END IF;
1306 RAISE;
1307 END;
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 /*RETCODE:=2;
1311 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1312 commented for the exception handler related changes*/
1313
1314 RETCODE := 2;
1315 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1316 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1317
1318
1319 END grdp_prc_gac;
1320
1321
1322 PROCEDURE grdp_set_gr_gst(
1323 errbuf out NOCOPY varchar2,
1324 retcode out NOCOPY NUMBER,
1325 p_eligible_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
1326 p_potential_graduand_status IGS_GR_STAT.graduand_status%TYPE,
1327 p_org_id NUMBER,
1328 p_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
1329 p_approval_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE)
1330 AS
1331
1332 BEGIN -- grdp_set_gr_gst
1333 -- This module checks if a IGS_GR_GRADUAND with a 'POTENTIAL' IGS_GR_GRADUAND status is
1334 -- now 'ELIGIBLE'. It also checks if an 'ELIGIBLE' IGS_GR_GRADUAND is no longer
1335 -- eligible. It sets the status accordingly.
1336 retcode:=0;
1337 igs_ge_gen_003.set_org_id(p_org_id);
1338 DECLARE
1339 cst_eligible CONSTANT VARCHAR2(10) := 'ELIGIBLE';
1340 cst_potential CONSTANT VARCHAR2(10) := 'POTENTIAL';
1341 e_resource_busy EXCEPTION;
1342 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
1343 v_graduand_status IGS_GR_STAT.graduand_status%TYPE;
1344 v_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
1345 v_message_name VARCHAR2(30);
1346 v_closed_ind IGS_GR_STAT.closed_ind%TYPE;
1347 CURSOR c_gst (
1348 cp_graduand_status IGS_GR_STAT.graduand_status%TYPE,
1349 cp_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE) IS
1350 SELECT gst.closed_ind
1351 FROM IGS_GR_STAT gst
1352 WHERE gst.graduand_status = cp_graduand_status AND
1353 gst.s_graduand_status = cp_s_graduand_status;
1354
1355 CURSOR c_sp_exists(
1356 cp_person_id IGS_GR_GRADUAND.person_id%TYPE,
1357 cp_course_cd IGS_GR_GRADUAND.course_cd%TYPE,
1358 cp_award_cd IGS_GR_GRADUAND.award_cd%TYPE) IS
1359 SELECT '1'
1360 FROM IGS_EN_SPA_AWD_AIM spaaa
1361 WHERE spaaa.person_id = cp_person_id
1362 AND spaaa.course_cd = cp_course_cd
1363 AND spaaa.award_cd = cp_award_cd
1364 AND spaaa.complete_ind='Y';
1365
1366
1367 CURSOR c_gr_gst IS
1368 SELECT gr.rowid,
1369 gr.person_id,
1370 gr.create_dt,
1371 gr.grd_cal_type,
1372 gr.grd_ci_sequence_number,
1373 gr.course_cd,
1374 gr.award_course_cd,
1375 gr.award_crs_version_number,
1376 gr.award_cd,
1377 gr.graduand_status,
1378 gr.graduand_appr_status,
1379 gr.s_graduand_type,
1380 gr.graduation_name,
1381 gr.proxy_award_ind,
1382 gr.proxy_award_person_id,
1383 gr.previous_qualifications,
1384 gr.convocation_membership_ind,
1385 gr.sur_for_course_cd,
1386 gr.sur_for_crs_version_number,
1387 gr.sur_for_award_cd,
1388 gr.comments,
1389 gst.s_graduand_status,
1390 gr.attribute_category,
1391 gr.attribute1,
1392 gr.attribute2,
1393 gr.attribute3,
1394 gr.attribute4,
1395 gr.attribute5,
1396 gr.attribute6,
1397 gr.attribute7,
1398 gr.attribute8,
1399 gr.attribute9,
1400 gr.attribute10,
1401 gr.attribute11,
1402 gr.attribute12,
1403 gr.attribute13,
1404 gr.attribute14,
1405 gr.attribute15,
1406 gr.attribute16,
1407 gr.attribute17,
1408 gr.attribute18,
1409 gr.attribute19,
1410 gr.attribute20
1411 FROM IGS_GR_GRADUAND gr,
1412 IGS_GR_STAT gst
1413 WHERE gr.graduand_status = gst.graduand_status AND
1414 gst.s_graduand_status IN
1415 ('POTENTIAL',
1416 'ELIGIBLE')
1417 FOR UPDATE OF gr.graduand_status NOWAIT;
1418
1419 v_approval_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE;
1420 lc_sp_exists VARCHAR2(10);
1421 BEGIN
1422
1423 -- Next check has been added as per the 'Progression Completion' TD Bug# 2636792
1424 IF p_graduand_status IS NULL OR p_approval_status IS NULL THEN
1425 FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_GRAD_APPR_REQ');
1426 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1427 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1428 RETURN;
1429 END IF;
1430 -- Check paramenters
1431 IF p_eligible_graduand_status IS NULL OR
1432 p_potential_graduand_status IS NULL THEN
1433 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
1434 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1435 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1436 RETURN;
1437 END IF;
1438 -- Validate the status values provided
1439 OPEN c_gst(
1440 p_eligible_graduand_status,
1441 cst_eligible);
1442 FETCH c_gst INTO v_closed_ind;
1443 IF c_gst%NOTFOUND THEN
1444 CLOSE c_gst;
1445 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
1446 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1447 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1448 RETURN;
1449 ELSE
1450 CLOSE c_gst;
1451 IF v_closed_ind = 'Y' THEN
1452 Fnd_Message.Set_Name('IGS', 'IGS_GR_GRAD_STATUS_CLOSED');
1453 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1454 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1455 RETURN;
1456 END IF;
1457 END IF;
1458 OPEN c_gst(
1459 p_potential_graduand_status,
1460 cst_potential);
1461 FETCH c_gst INTO v_closed_ind;
1462 IF c_gst%NOTFOUND THEN
1463 CLOSE c_gst;
1464 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
1465 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1466 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1467 RETURN;
1468 ELSE
1469 CLOSE c_gst;
1470 IF v_closed_ind = 'Y' THEN
1471 Fnd_Message.Set_Name('IGS', 'IGS_GR_GRAD_STATUS_CLOSED');
1472 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
1473 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1474 RETURN;
1475 END IF;
1476 END IF;
1477 -- Find potential and eligible graduands
1478 FOR v_gr_gst_rec IN c_gr_gst LOOP
1479 -- Check the eligibility status
1480 IF IGS_GR_VAL_GR.grdp_val_aw_eligible (
1481 v_gr_gst_rec.person_id,
1482 v_gr_gst_rec.course_cd,
1483 v_gr_gst_rec.award_course_cd,
1484 v_gr_gst_rec.award_crs_version_number,
1485 v_gr_gst_rec.award_cd,
1486 v_message_name) THEN
1487 v_graduand_status := p_eligible_graduand_status;
1488 v_s_graduand_status := cst_eligible;
1489 ELSE
1490 v_graduand_status := p_potential_graduand_status;
1491 v_s_graduand_status := cst_potential;
1492 END IF;
1493
1494 OPEN c_sp_exists(v_gr_gst_rec.person_id, v_gr_gst_rec.award_course_cd, v_gr_gst_rec.award_cd); --**
1495 FETCH c_sp_exists INTO lc_sp_exists;
1496 IF c_sp_exists%FOUND THEN
1497 v_graduand_status := p_graduand_status;
1498 v_approval_status := p_approval_status;
1499 ELSE
1500 v_s_graduand_status := cst_potential;
1501 v_approval_status := v_gr_gst_rec.graduand_appr_status;
1502 END IF;
1503
1504 IF NVL(v_gr_gst_rec.s_graduand_status,'NULL') <> NVL(v_s_graduand_status,'NULL') OR
1505 (c_sp_exists%FOUND AND p_graduand_status IS NOT NULL AND p_approval_status IS NOT NULL) THEN
1506 DECLARE
1507 l_message_text fnd_new_messages.message_text%TYPE;
1508 BEGIN
1509 IGS_GR_GRADUAND_PKG.UPDATE_ROW(
1510 X_ROWID => v_gr_gst_rec.rowid,
1511 X_PERSON_ID => v_gr_gst_rec.person_id,
1512 X_CREATE_DT => v_gr_gst_rec.create_dt,
1513 X_GRD_CAL_TYPE => v_gr_gst_rec.grd_cal_type,
1514 X_GRD_CI_SEQUENCE_NUMBER => v_gr_gst_rec.grd_ci_sequence_number,
1515 X_COURSE_CD => v_gr_gst_rec.course_cd,
1516 X_AWARD_COURSE_CD => v_gr_gst_rec.award_course_cd,
1517 X_AWARD_CRS_VERSION_NUMBER => v_gr_gst_rec.award_crs_version_number,
1518 X_AWARD_CD => v_gr_gst_rec.award_cd,
1519 X_GRADUAND_STATUS => v_graduand_status,
1520 X_GRADUAND_APPR_STATUS => v_approval_status,
1521 X_S_GRADUAND_TYPE => v_gr_gst_rec.s_graduand_type,
1522 X_GRADUATION_NAME => v_gr_gst_rec.graduation_name,
1523 X_PROXY_AWARD_IND => v_gr_gst_rec.proxy_award_ind,
1524 X_PROXY_AWARD_PERSON_ID => v_gr_gst_rec.proxy_award_person_id,
1525 X_PREVIOUS_QUALIFICATIONS => v_gr_gst_rec.previous_qualifications,
1526 X_CONVOCATION_MEMBERSHIP_IND => v_gr_gst_rec.convocation_membership_ind,
1527 X_SUR_FOR_COURSE_CD => v_gr_gst_rec.sur_for_course_cd,
1528 X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_gst_rec.sur_for_crs_version_number,
1529 X_SUR_FOR_AWARD_CD => v_gr_gst_rec.sur_for_award_cd,
1530 X_COMMENTS => v_gr_gst_rec.comments,
1531 X_MODE => 'R',
1532 X_ATTRIBUTE_CATEGORY => v_gr_gst_rec.attribute_category,
1533 X_ATTRIBUTE1 => v_gr_gst_rec.attribute1,
1534 X_ATTRIBUTE2 => v_gr_gst_rec.attribute2,
1535 X_ATTRIBUTE3 => v_gr_gst_rec.attribute3,
1536 X_ATTRIBUTE4 => v_gr_gst_rec.attribute4,
1537 X_ATTRIBUTE5 => v_gr_gst_rec.attribute5,
1538 X_ATTRIBUTE6 => v_gr_gst_rec.attribute6,
1539 X_ATTRIBUTE7 => v_gr_gst_rec.attribute7,
1540 X_ATTRIBUTE8 => v_gr_gst_rec.attribute8,
1541 X_ATTRIBUTE9 => v_gr_gst_rec.attribute9,
1542 X_ATTRIBUTE10 => v_gr_gst_rec.attribute10,
1543 X_ATTRIBUTE11 => v_gr_gst_rec.attribute11,
1544 X_ATTRIBUTE12 => v_gr_gst_rec.attribute12,
1545 X_ATTRIBUTE13 => v_gr_gst_rec.attribute13,
1546 X_ATTRIBUTE14 => v_gr_gst_rec.attribute14,
1547 X_ATTRIBUTE15 => v_gr_gst_rec.attribute15,
1548 X_ATTRIBUTE16 => v_gr_gst_rec.attribute16,
1549 X_ATTRIBUTE17 => v_gr_gst_rec.attribute17,
1550 X_ATTRIBUTE18 => v_gr_gst_rec.attribute18,
1551 X_ATTRIBUTE19 => v_gr_gst_rec.attribute19,
1552 X_ATTRIBUTE20 => v_gr_gst_rec.attribute20);
1553 EXCEPTION WHEN OTHERS THEN
1554 DECLARE
1555 CURSOR cur_get_person_num (cp_person_id NUMBER)IS
1556 SELECT person_number
1557 FROM igs_pe_person_base_v
1558 WHERE person_id = cp_person_id;
1559 rec_get_person_num cur_get_person_num%ROWTYPE;
1560 l_log VARCHAR2(4000);
1561 BEGIN
1562 OPEN cur_get_person_num(v_gr_gst_rec.person_id);
1563 FETCH cur_get_person_num INTO rec_get_person_num;
1564 CLOSE cur_get_person_num;
1565 l_message_text := fnd_message.get;
1566 FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_ERROR_OCC');
1567 l_log := FND_MESSAGE.GET()||' '''||rec_get_person_num.person_number||'''';
1568 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_PRG');
1569 l_log := l_log||' '||FND_MESSAGE.GET()||': '''||v_gr_gst_rec.course_cd||' '||v_gr_gst_rec.award_crs_version_number||'''';
1570 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_PROGRAM_AWARDS');
1571 l_log := l_log||' '||FND_MESSAGE.GET()||': '''||v_gr_gst_rec.award_cd||'''';
1572 FND_FILE.PUT_LINE(FND_FILE.LOG, l_log);
1573 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||l_message_text);
1574 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1575 END;
1576 END;
1577 END IF;
1578 CLOSE c_sp_exists;
1579 END LOOP;
1580 EXCEPTION
1581 WHEN e_resource_busy THEN
1582 IF c_gr_gst%ISOPEN THEN
1583 CLOSE c_gr_gst;
1584 END IF;
1585 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_RECORD_LOCKED');
1586 RETCODE:=2;
1587 WHEN OTHERS THEN
1588
1589 IF c_gst%ISOPEN THEN
1590 CLOSE c_gst;
1591 END IF;
1592 IF c_gr_gst%ISOPEN THEN
1593 CLOSE c_gr_gst;
1594 END IF;
1595 RAISE;
1596 END;
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 /*RETCODE:=2;
1600 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1601 commented for exception handler related changes */
1602
1603 RETCODE := 2;
1604 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1605 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1606 END grdp_set_gr_gst;
1607
1608
1609 PROCEDURE grdp_upd_gac_order(
1610 errbuf out NOCOPY varchar2,
1611 retcode out NOCOPY NUMBER,
1612 p_grd_perd VARCHAR2,
1613 p_order_by IN VARCHAR2 ,
1614 p_ignore_unit_sets_ind IN VARCHAR2 ,
1615 p_group_multi_award_ind IN VARCHAR2,
1616 p_mode IN VARCHAR2 , -- Added a new parameter as part of Order in Presentation DLD.
1617 p_org_id NUMBER
1618 )
1619 AS
1620 --
1621 -- Change History :
1622 -- Who When What
1623 -- (reverse chronological order - newest change first)
1624 -- pradhakr 14-Sep-2002 Changes as per Order in Presentation DLD
1625 --
1626 -- Nalin Kumar 29-Oct-2002 Modified the dynamic SQL which creates the main select stetament (v_gac_upd_select)
1627 -- to fetch records to update the 'Order in presantation' (removed the 'System Graduand Type'
1628 -- check from the where clause. This is as per the Conferral Date TD Bug# 2640799
1629 --
1630 -- Nalin Kumar 10-DEC-2002 Modified this procedure to fix Bug# 2691809. Modified the code to log error
1631 -- messages in the log file instead of raising unhandled exception.
1632 --
1633 --
1634 -- Nalin Kumar 18-DEC-2002 Modified this procedure to fix Bug# 2690151.
1635 -- Added the code to log the parameters value in the log file.
1636 --
1637 p_grd_cal_type IGS_GR_CRMN_ROUND.grd_cal_type%type;
1638 p_grd_ci_sequence_number IGS_GR_CRMN_ROUND.grd_ci_sequence_number%type;
1639 p_ceremony_number IGS_GR_CRMN.ceremony_number%type;
1640 l_index NUMBER;
1641 l_app_name VARCHAR2(30) := 'IGS';
1642 l_msg_text varchar2(200);
1643 l_message_name VARCHAR2(200);
1644
1645 BEGIN -- grdp_upd_gac_order
1646 -- Set the IGS_GR_AWD_CRMN.order_in_presentation based on the
1647 -- IGS_GR_AWD_CEREMONY.order_in_ceremony, IGS_GR_AWD_CRM_US_GP.order_in_award
1648 -- and the parameters passed.
1649
1650 --Block for Parameter Validation/Splitting of Parameters
1651 retcode:=0;
1652 igs_ge_gen_003.set_org_id(p_org_id);
1653 BEGIN
1654 p_grd_cal_type := RTRIM(SUBSTR(p_grd_perd, 1,10 ));
1655 p_grd_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_grd_perd, 11, 10)));
1656 --Modified the next line to fix Bug# 2691848;
1657 p_ceremony_number := RTRIM(SUBSTR(p_grd_perd,21));
1658 END;
1659 --End of Block for Parameter Validation/Splitting of Parameters
1660
1661 DECLARE
1662 cst_eligible CONSTANT VARCHAR2(10) := 'ELIGIBLE';
1663 cst_graduated CONSTANT VARCHAR2(10) := 'GRADUATED';
1664 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
1665 cst_attending CONSTANT VARCHAR2(10) := 'ATTENDING';
1666 cst_update_of_clause
1667 CONSTANT VARCHAR2(40) := 'FOR UPDATE OF gac.person_id NOWAIT';
1668 e_resource_busy EXCEPTION;
1669 PRAGMA EXCEPTION_INIT(e_resource_busy, -54 );
1670 v_order_count NUMBER;
1671 v_index NUMBER := 1;
1672 v_gac_upd_select VARCHAR2(2000);
1673 v_order_by_clause VARCHAR2(500);
1674 v_gac_upd_c_handle INTEGER;
1675 v_gac_upd_c_exe_result INTEGER;
1676 -- Record declaration for Dynamic SQL
1677 TYPE gac_upd_rectype IS RECORD (
1678 person_id IGS_GR_AWD_CRMN.person_id%TYPE,
1679 create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
1680 award_course_cd IGS_GR_AWD_CRMN.award_course_cd%TYPE,
1681 award_crs_version_number
1682 IGS_GR_AWD_CRMN.award_crs_version_number%TYPE,
1683 award_cd IGS_GR_AWD_CRMN.award_cd%TYPE,
1684 grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
1685 grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
1686 ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE);
1687 v_gac_upd_rec gac_upd_rectype;
1688 CURSOR c_gc IS
1689 SELECT gc.ceremony_number
1690 FROM IGS_GR_CRMN gc
1691 WHERE gc.grd_cal_type = p_grd_cal_type AND
1692 gc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1693 gc.ceremony_number LIKE p_ceremony_number;
1694 CURSOR c_awc (
1695 cp_ceremony_number IGS_GR_CRMN.ceremony_number%TYPE) IS
1696 SELECT /*+ INDEX(awc awc_uk*/
1697 'X'
1698 FROM IGS_GR_AWD_CEREMONY awc
1699 WHERE awc.grd_cal_type = p_grd_cal_type AND
1700 awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1701 awc.ceremony_number = cp_ceremony_number AND
1702 awc.closed_ind = 'Y' AND
1703 EXISTS (SELECT 'X'
1704 FROM IGS_GR_AWD_CRMN gac
1705 WHERE gac.grd_cal_type = awc.grd_cal_type AND
1706 gac.grd_ci_sequence_number = awc.grd_ci_sequence_number AND
1707 gac.ceremony_number = awc.ceremony_number AND
1708 ((gac.award_course_cd IS NULL AND
1709 awc.award_course_cd IS NULL) OR
1710 gac.award_course_cd = awc.award_course_cd) AND
1711 ((gac.award_crs_version_number IS NULL AND
1712 awc.award_crs_version_number IS NULL) OR
1713 awc.award_crs_version_number = gac.award_crs_version_number) AND
1714 awc.award_cd = gac.award_cd);
1715 v_awc_exists VARCHAR2(1);
1716
1717 CURSOR c_acusg (
1718 cp_ceremony_number IGS_GR_CRMN.ceremony_number%TYPE) IS
1719 SELECT /*+ INDEX(acusg acusg_pk)*/
1720 'X'
1721 FROM IGS_GR_AWD_CRM_US_GP acusg
1722 WHERE acusg.grd_cal_type = p_grd_cal_type AND
1723 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1724 acusg.ceremony_number = cp_ceremony_number AND
1725 acusg.closed_ind = 'Y' AND
1726 EXISTS
1727 (SELECT 'X'
1728 FROM IGS_GR_AWD_CRMN gac
1729 WHERE gac.grd_cal_type = acusg.grd_cal_type AND
1730 gac.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
1731 gac.ceremony_number = acusg.ceremony_number AND
1732 gac.award_course_cd = acusg.award_course_cd AND
1733 gac.award_crs_version_number = acusg.award_crs_version_number AND
1734 gac.award_cd = acusg.award_cd AND
1735 gac.us_group_number = acusg.us_group_number);
1736 v_acusg_exists VARCHAR2(1);
1737 CURSOR c_gac_del(
1738 cp_ceremony_number IGS_GR_CRMN.ceremony_number%TYPE) IS
1739 SELECT rowid, gac.*
1740 FROM IGS_GR_AWD_CRMN gac
1741 WHERE gac.grd_cal_type = p_grd_cal_type AND
1742 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1743 gac.ceremony_number = cp_ceremony_number
1744 FOR UPDATE OF gac.order_in_presentation NOWAIT;
1745 CURSOR c_existing_order(
1746 cp_ceremony_number IGS_GR_CRMN.ceremony_number%TYPE,
1747 cp_person_id IGS_GR_AWD_CRMN.person_id%TYPE,
1748 cp_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE,
1749 cp_award_course_cd IGS_GR_AWD_CRMN.award_course_cd%TYPE,
1750 cp_award_crs_version_number
1751 IGS_GR_AWD_CRMN.award_crs_version_number%TYPE,
1752 cp_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE) IS
1753 SELECT gac.order_in_presentation
1754 FROM IGS_GR_AWD_CRMN gac
1755 WHERE gac.person_id = cp_person_id AND
1756 gac.grd_cal_type = p_grd_cal_type AND
1757 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1758 gac.ceremony_number = cp_ceremony_number AND
1759 (((gac.award_course_cd IS NULL AND
1760 cp_award_course_cd IS NULL) OR
1761 gac.award_course_cd <> cp_award_course_cd) OR
1762 ((gac.award_crs_version_number IS NULL AND
1763 cp_award_crs_version_number IS NULL) OR
1764 cp_award_crs_version_number <> gac.award_crs_version_number) OR
1765 gac.award_cd <> cp_award_cd) AND
1766 gac.order_in_presentation IS NOT NULL;
1767
1768 -- Cursor to get the maximum number of the 'Order in Presentation' for a given ceremony
1769 -- Added as part of Order in Presentation DLD Bug# 2578638
1770 -- pradhakr; 14-Sep-2002;
1771
1772 CURSOR c_max_ord_num IS
1773 SELECT max(order_in_presentation)
1774 FROM igs_gr_awd_crmn
1775 WHERE grd_cal_type = p_grd_cal_type
1776 AND grd_ci_sequence_number = p_grd_ci_sequence_number
1777 AND ceremony_number = p_ceremony_number;
1778
1779 v_existing_order IGS_GR_AWD_CRMN.order_in_presentation%TYPE;
1780 BEGIN
1781 --
1782 --Log the Parameters value in the log file. This is to fix Bug# 2690151
1783 --
1784 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_ANC_LOG_PARM');
1785 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET()||':');
1786 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_cal_type = '||p_grd_cal_type);
1787 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_grd_ci_sequence_number = '||p_grd_ci_sequence_number);
1788 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_ceremony_number = '||p_ceremony_number);
1789 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_order_by = '||p_order_by);
1790 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_ignore_unit_sets_ind = '||p_ignore_unit_sets_ind);
1791 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_group_multi_award_ind = '||p_group_multi_award_ind);
1792 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_mode = '||p_mode);
1793
1794 --1. Check parameters :
1795 IF p_grd_cal_type IS NULL OR
1796 p_grd_ci_sequence_number IS NULL OR
1797 p_ceremony_number IS NULL OR
1798 p_order_by IS NULL OR
1799 p_ignore_unit_sets_ind IS NULL OR
1800 p_group_multi_award_ind IS NULL THEN
1801 Fnd_Message.Set_Name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
1802 --App_Exception.Raise_Exception;
1803 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
1804 RETURN;
1805 END IF;
1806 --2. Loop through all of the IGS_GR_CRMN records
1807 -- which match the specifed parameters.
1808 FOR v_gc_rec IN c_gc LOOP
1809 --3. Check if the IGS_GR_CRMN has any related IGS_GR_AWD_CRMN
1810 -- records linked to closed IGS_GR_AWD_CEREMONY records.
1811 OPEN c_awc(v_gc_rec.ceremony_number);
1812 FETCH c_awc INTO v_awc_exists;
1813 IF c_awc%FOUND THEN
1814 --4. If any records are found raise an error.
1815 CLOSE c_awc;
1816 Fnd_Message.Set_Name('IGS', 'IGS_GR_AWD_CERM_REC_CLOSED');
1817 --App_Exception.Raise_Exception;
1818 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
1819 RETURN;
1820 END IF;
1821 CLOSE c_awc;
1822 --5. Check if the IGS_GR_CRMN has any related graduand_award_cermony
1823 -- records linked to closed IGS_GR_AWD_CRM_US_GP records.
1824 OPEN c_acusg(
1825 v_gc_rec.ceremony_number);
1826 FETCH c_acusg INTO v_acusg_exists;
1827 IF c_acusg%FOUND THEN
1828 --6. If any records are found raise an error.
1829 CLOSE c_acusg;
1830 Fnd_Message.Set_Name('IGS', 'IGS_GR_AWD_CERM_UNIT_SET_CLOS');
1831 --App_Exception.Raise_Exception;
1832 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
1833 RETURN;
1834 END IF;
1835 CLOSE c_acusg;
1836 --7. Clear the order_in_presentation for all of the IGS_GR_AWD_CRMN
1837 -- records related to this IGS_GR_CRMN.
1838
1839 -- If the concurrent process is executed in 'Reallocate' mode then update
1840 -- all the order in presentation column to NULL. This will allows to
1841 -- assign a fresh order for the graduants who satify the given criteria.
1842 -- Added the IF condition as part of order in Presentation DLD.
1843 -- pradhakr; 14-Sep-2002; Bug# 2578638
1844
1845 IF p_mode = 'R' THEN
1846
1847 BEGIN -- local block to trap exception
1848 SAVEPOINT sp_gac_del;
1849
1850 FOR v_gac_del_rec IN c_gac_del(
1851 v_gc_rec.ceremony_number) LOOP
1852
1853 IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
1854 X_ROWID =>v_gac_del_rec.rowid,
1855 X_GAC_ID =>v_gac_del_rec.GAC_ID,
1856 X_GRADUAND_SEAT_NUMBER =>v_gac_del_rec.graduand_seat_number,
1857 X_NAME_PRONUNCIATION =>v_gac_del_rec.name_pronunciation,
1858 X_NAME_ANNOUNCED =>v_gac_del_rec.name_announced,
1859 X_ACADEMIC_DRESS_RQRD_IND =>v_gac_del_rec.academic_dress_rqrd_ind,
1860 X_ACADEMIC_GOWN_SIZE =>v_gac_del_rec.academic_gown_size,
1861 X_ACADEMIC_HAT_SIZE =>v_gac_del_rec.academic_hat_size,
1862 X_GUEST_TICKETS_REQUESTED =>v_gac_del_rec.guest_tickets_requested,
1863 X_GUEST_TICKETS_ALLOCATED =>v_gac_del_rec.guest_tickets_allocated,
1864 X_GUEST_SEATS =>v_gac_del_rec.guest_seats,
1865 X_FEES_PAID_IND =>v_gac_del_rec.fees_paid_ind,
1866 X_SPECIAL_REQUIREMENTS =>v_gac_del_rec.special_requirements,
1867 X_COMMENTS =>v_gac_del_rec.COMMENTS,
1868 X_PERSON_ID =>v_gac_del_rec.person_id,
1869 X_CREATE_DT =>v_gac_del_rec.create_dt,
1870 X_GRD_CAL_TYPE =>v_gac_del_rec.grd_cal_type,
1871 X_GRD_CI_SEQUENCE_NUMBER =>v_gac_del_rec.grd_ci_sequence_number,
1872 X_CEREMONY_NUMBER =>v_gac_del_rec.ceremony_number,
1873 X_AWARD_COURSE_CD =>v_gac_del_rec.award_course_cd,
1874 X_AWARD_CRS_VERSION_NUMBER =>v_gac_del_rec.award_crs_version_number,
1875 X_AWARD_CD =>v_gac_del_rec.award_cd,
1876 X_US_GROUP_NUMBER =>v_gac_del_rec.us_group_number,
1877 X_ORDER_IN_PRESENTATION => NULL,
1878 X_MODE => 'R');
1879
1880 END LOOP; -- c_gac_del
1881 COMMIT;
1882 EXCEPTION
1883 WHEN e_resource_busy THEN
1884 -- Error 4713 for locking conflict exception and ROLLBACK.
1885 IF c_gac_del%ISOPEN THEN
1886 CLOSE c_gac_del;
1887 END IF;
1888 ROLLBACK TO sp_gac_del;
1889 Fnd_Message.Set_Name('IGS', 'IGS_GR_AWD_CERM_CANNOT_UPDATE');
1890 --App_Exception.Raise_Exception;
1891 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
1892 WHEN OTHERS THEN
1893 ROLLBACK TO sp_gac_del;
1894 l_msg_text := fnd_message.get;
1895 IGS_GE_MSG_STACK.GET(1,'T',l_msg_text, l_index );
1896 FND_MESSAGE.PARSE_ENCODED (l_msg_text, l_app_name, l_message_name);
1897 IF l_message_name IN ('IGS_GR_INVALID_PROC_PERIOD','IGS_GR_INV_DT_GRAD_CERM', 'IGS_GR_CLOSING_DT_REACHED') THEN
1898 Fnd_Message.Set_Name('IGS', l_message_name);
1899 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
1900 RETURN;
1901 END IF;
1902 RAISE;
1903 END;
1904 END IF;
1905 -- Main Select contents for dynamic SQL construction.
1906
1907 v_gac_upd_select :=
1908 'SELECT ' ||
1909 'gac.person_id, ' ||
1910 'gac.create_dt, ' ||
1911 'gac.award_course_cd, ' ||
1912 'gac.award_crs_version_number, '||
1913 'gac.award_cd, ' ||
1914 'gac.grd_cal_type, ' ||
1915 'gac.grd_ci_sequence_number, '||
1916 'gac.ceremony_number ' ||
1917 'FROM IGS_GR_AWD_CRMN gac, ' ||
1918 'IGS_GR_GRADUAND gr, ' ||
1919 'IGS_GR_STAT gst, ' ||
1920 'IGS_PE_PERSON pe, ' ||
1921 'IGS_GR_AWD_CEREMONY awc, ' ||
1922 'IGS_GR_AWD_CRM_US_GP acusg, '||
1923 'IGS_GR_APRV_STAT gas ' ||
1924 'WHERE gac.grd_cal_type=''' || p_grd_cal_type || ''' AND ' ||
1925 'gac.grd_ci_sequence_number=' || TO_CHAR(p_grd_ci_sequence_number) || ' AND '||
1926 'gac.ceremony_number=' || TO_CHAR(v_gc_rec.ceremony_number) || ' AND '||
1927 'gac.person_id=gr.person_id AND ' ||
1928 'gac.create_dt=gr.create_dt AND ' ||
1929 'gac.person_id=pe.person_id AND ' ||
1930 'gr.graduand_status=gst.graduand_status AND ' ||
1931 '(gst.s_graduand_status=''' || cst_eligible || ''' OR ' ||
1932 'gst.s_graduand_status=''' || cst_graduated || ''') AND ' ||
1933 'gr.graduand_appr_status=gas.graduand_appr_status AND ' ||
1934 'gas.s_graduand_appr_status=''' || cst_approved || ''' AND ' ||
1935 'gr.s_graduand_type = ''' || cst_attending || ''' AND ' || /* Added to fix Bug# 3294453 */
1936 'gac.grd_cal_type=awc.grd_cal_type AND ' ||
1937 'gac.grd_ci_sequence_number=awc.grd_ci_sequence_number AND ' ||
1938 'gac.ceremony_number=awc.ceremony_number AND ' ||
1939 '((gac.award_course_cd IS NULL AND ' ||
1940 'awc.award_course_cd IS NULL) OR ' ||
1941 'gac.award_course_cd=awc.award_course_cd) AND '||
1942 '((gac.award_crs_version_number IS NULL AND ' ||
1943 'awc.award_crs_version_number IS NULL) OR ' ||
1944 'awc.award_crs_version_number=gac.award_crs_version_number) AND ' ||
1945 'gac.award_cd=awc.award_cd AND ' ||
1946 'gac.grd_cal_type=acusg.grd_cal_type(+) AND ' ||
1947 'gac.grd_ci_sequence_number=acusg.grd_ci_sequence_number(+) AND ' ||
1948 'gac.ceremony_number=acusg.ceremony_number(+) AND ' ||
1949 'gac.award_course_cd=acusg.award_course_cd(+) AND ' ||
1950 'gac.award_crs_version_number=acusg.award_crs_version_number(+) AND ' ||
1951 'gac.award_cd=acusg.award_cd(+) AND ' ||
1952 'gac.us_group_number=acusg.us_group_number(+) ';
1953
1954
1955 -- If job is running in 'Allocate' Mode then - select the Graduands
1956 -- who has 'Order in Presentation' as NULL.
1957 -- pradhakr; 15-Sep-2002; Bug# 2578638
1958
1959 IF p_mode = 'A' THEN
1960 v_gac_upd_select := v_gac_upd_select || 'AND gac.order_in_presentation IS NULL ' || 'ORDER BY ';
1961 ELSE
1962 v_gac_upd_select := v_gac_upd_select || 'ORDER BY ';
1963 END IF;
1964
1965 --7.1 Construct the order by clause for the select statement:
1966 v_order_by_clause := 'awc.order_in_ceremony ASC';
1967 IF p_ignore_unit_sets_ind = 'N' THEN
1968 v_order_by_clause := v_order_by_clause || ', acusg.order_in_award ASC';
1969 END IF;
1970
1971 --The Honours Level has been obsoleted so directly set the ORDER BY on the 'SURNAME' and 'GIVEN NAME'
1972 --Program Completion Validation Build.
1973 v_order_by_clause := v_order_by_clause || ', pe.surname ASC, pe.given_names ASC';
1974
1975 -- Open Dynamic Cursor, pass reference to v_cursor_handle.
1976 v_gac_upd_c_handle := DBMS_SQL.OPEN_CURSOR;
1977 -- Parse the complete SQL statement.
1978 DBMS_SQL.PARSE (
1979 v_gac_upd_c_handle,
1980 v_gac_upd_select || ' ' || v_order_by_clause || ' ' || cst_update_of_clause,
1981 DBMS_SQL.NATIVE);
1982 -- Define the columns in the dynamic SQL query.
1983 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 1, v_gac_upd_rec.person_id);
1984 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 2, v_gac_upd_rec.create_dt);
1985 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 3,
1986 v_gac_upd_rec.award_course_cd, 6);
1987 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 4,
1988 v_gac_upd_rec.award_crs_version_number);
1989 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 5, v_gac_upd_rec.award_cd, 10);
1990 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 6,
1991 v_gac_upd_rec.grd_cal_type, 10);
1992 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 7,
1993 v_gac_upd_rec.grd_ci_sequence_number);
1994 DBMS_SQL.DEFINE_COLUMN(v_gac_upd_c_handle, 8, v_gac_upd_rec.ceremony_number);
1995
1996 -- Now execute the dynamic cursor.
1997 v_gac_upd_c_exe_result := DBMS_SQL.EXECUTE(
1998 v_gac_upd_c_handle);
1999
2000
2001 -- Check if the 'Append' Mode then get the maximum number of the 'Order in Presentation'
2002 -- in a variable else just assign '1' to that variable (v_order_count).
2003 -- pradhakr; 15-Sep-2002
2004 IF p_mode = 'A' THEN
2005 OPEN c_max_ord_num;
2006 FETCH c_max_ord_num INTO v_order_count;
2007 v_order_count := NVL(v_order_count, 0) + 1;
2008 CLOSE c_max_ord_num;
2009 ELSE
2010 v_order_count := 1;
2011 END IF;
2012
2013 --8. Find all of the IGS_GR_AWD_CRMN records for
2014 -- this IGS_GR_CRMN.
2015 --9. Loop through the IGS_GR_AWD_CRMN records setting the
2016 -- order_in_presentation.
2017 LOOP -- Dynamic Cursor associated with handle 'v_gac_upd_c_handle'
2018 Exit WHEN DBMS_SQL.FETCH_ROWS(v_gac_upd_c_handle) = 0;
2019 -- Retrieve the data associated with the Dynamic SQL cursor
2020 -- v_gac_upd_c_handle
2021 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 1, v_gac_upd_rec.person_id);
2022 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 2, v_gac_upd_rec.create_dt);
2023 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 3, v_gac_upd_rec.award_course_cd);
2024 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 4,
2025 v_gac_upd_rec.award_crs_version_number);
2026 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 5, v_gac_upd_rec.award_cd);
2027 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 6, v_gac_upd_rec.grd_cal_type);
2028 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 7,
2029 v_gac_upd_rec.grd_ci_sequence_number);
2030 DBMS_SQL.COLUMN_VALUE(v_gac_upd_c_handle, 8, v_gac_upd_rec.ceremony_number);
2031
2032 BEGIN -- local block for update error trapping
2033 SAVEPOINT sp_gac_upd;
2034 IF p_group_multi_award_ind = 'Y' THEN
2035 OPEN c_existing_order(
2036 v_gc_rec.ceremony_number,
2037 v_gac_upd_rec.person_id,
2038 v_gac_upd_rec.create_dt,
2039 v_gac_upd_rec.award_course_cd,
2040 v_gac_upd_rec.award_crs_version_number,
2041 v_gac_upd_rec.award_cd);
2042 FETCH c_existing_order INTO v_existing_order;
2043 IF c_existing_order%FOUND THEN
2044 CLOSE c_existing_order;
2045 DECLARE
2046 CURSOR cur_gac IS
2047 SELECT rowid, gac.*
2048 FROM IGS_GR_AWD_CRMN gac
2049 WHERE person_id = v_gac_upd_rec.person_id AND
2050 create_dt = v_gac_upd_rec.create_dt AND
2051 NVL(award_course_cd, 'NULL' )
2052 = NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
2053 NVL(award_crs_version_number, 0 )
2054 = NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
2055 award_cd = v_gac_upd_rec.award_cd AND
2056 grd_cal_type = v_gac_upd_rec.grd_cal_type AND
2057 grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
2058 ceremony_number = v_gac_upd_rec.ceremony_number;
2059 BEGIN
2060 for gac_rec in cur_gac loop
2061 BEGIN
2062 gac_rec.order_in_presentation := v_existing_order;
2063 IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
2064 X_ROWID =>gac_rec.rowid,
2065 X_GAC_ID =>gac_rec.GAC_ID,
2066 X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
2067 X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
2068 X_NAME_ANNOUNCED =>gac_rec.name_announced,
2069 X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
2070 X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
2071 X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
2072 X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
2073 X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
2074 X_GUEST_SEATS =>gac_rec.guest_seats,
2075 X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
2076 X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
2077 X_COMMENTS =>gac_rec.COMMENTS,
2078 X_PERSON_ID =>gac_rec.person_id,
2079 X_CREATE_DT =>gac_rec.create_dt,
2080 X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
2081 X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
2082 X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
2083 X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
2084 X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
2085 X_AWARD_CD =>gac_rec.award_cd,
2086 X_US_GROUP_NUMBER =>gac_rec.us_group_number,
2087 X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
2088 X_MODE => 'R');
2089 EXCEPTION
2090 WHEN OTHERS THEN
2091 l_msg_text := fnd_message.get;
2092 igs_ge_msg_stack.get(1,'T',l_msg_text, l_index );
2093 fnd_message.parse_encoded (l_msg_text, l_app_name, l_message_name);
2094 IF l_message_name IN ('IGS_GR_INVALID_PROC_PERIOD','IGS_GR_INV_DT_GRAD_CERM', 'IGS_GR_CLOSING_DT_REACHED') THEN
2095 Fnd_Message.Set_Name('IGS', l_message_name);
2096 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
2097 RETURN;
2098 END IF;
2099 END;
2100 end loop;
2101 END;
2102 ELSE
2103 CLOSE c_existing_order;
2104 DECLARE
2105 CURSOR cur_gac IS
2106 SELECT rowid, gac.*
2107 FROM IGS_GR_AWD_CRMN gac
2108 WHERE person_id = v_gac_upd_rec.person_id AND
2109 create_dt = v_gac_upd_rec.create_dt AND
2110 NVL(award_course_cd, 'NULL' )
2111 = NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
2112 NVL(award_crs_version_number, 0 )
2113 = NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
2114 award_cd = v_gac_upd_rec.award_cd AND
2115 grd_cal_type = v_gac_upd_rec.grd_cal_type AND
2116 grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
2117 ceremony_number = v_gac_upd_rec.ceremony_number;
2118 BEGIN
2119 for gac_rec in cur_gac loop
2120 BEGIN
2121 gac_rec.order_in_presentation := v_order_count;
2122 IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
2123 X_ROWID =>gac_rec.rowid,
2124 X_GAC_ID =>gac_rec.GAC_ID,
2125 X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
2126 X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
2127 X_NAME_ANNOUNCED =>gac_rec.name_announced,
2128 X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
2129 X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
2130 X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
2131 X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
2132 X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
2133 X_GUEST_SEATS =>gac_rec.guest_seats,
2134 X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
2135 X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
2136 X_COMMENTS =>gac_rec.COMMENTS,
2137 X_PERSON_ID =>gac_rec.person_id,
2138 X_CREATE_DT =>gac_rec.create_dt,
2139 X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
2140 X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
2141 X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
2142 X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
2143 X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
2144 X_AWARD_CD =>gac_rec.award_cd,
2145 X_US_GROUP_NUMBER =>gac_rec.us_group_number,
2146 X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
2147 X_MODE => 'R');
2148
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151 l_msg_text := fnd_message.get;
2152 igs_ge_msg_stack.get(1,'T',l_msg_text, l_index );
2153 fnd_message.parse_encoded (l_msg_text, l_app_name, l_message_name);
2154 IF l_message_name IN ('IGS_GR_INVALID_PROC_PERIOD','IGS_GR_INV_DT_GRAD_CERM', 'IGS_GR_CLOSING_DT_REACHED') THEN
2155 Fnd_Message.Set_Name('IGS', l_message_name);
2156 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
2157 RETURN;
2158 END IF;
2159 END;
2160 END LOOP;
2161 END;
2162 v_order_count := v_order_count + 1;
2163 END IF;
2164 ELSE
2165 DECLARE
2166 CURSOR cur_gac IS
2167 SELECT rowid, gac.*
2168 FROM IGS_GR_AWD_CRMN gac
2169 WHERE person_id = v_gac_upd_rec.person_id AND
2170 create_dt = v_gac_upd_rec.create_dt AND
2171 NVL(award_course_cd, 'NULL' )
2172 = NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
2173 NVL(award_crs_version_number, 0 )
2174 = NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
2175 award_cd = v_gac_upd_rec.award_cd AND
2176 grd_cal_type = v_gac_upd_rec.grd_cal_type AND
2177 grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
2178 ceremony_number = v_gac_upd_rec.ceremony_number;
2179
2180 BEGIN
2181 for gac_rec in cur_gac loop
2182 gac_rec.order_in_presentation := v_order_count;
2183 IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
2184 X_ROWID =>gac_rec.rowid,
2185 X_GAC_ID =>gac_rec.GAC_ID,
2186 X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
2187 X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
2188 X_NAME_ANNOUNCED =>gac_rec.name_announced,
2189 X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
2190 X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
2191 X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
2192 X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
2193 X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
2194 X_GUEST_SEATS =>gac_rec.guest_seats,
2195 X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
2196 X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
2197 X_COMMENTS =>gac_rec.COMMENTS,
2198 X_PERSON_ID =>gac_rec.person_id,
2199 X_CREATE_DT =>gac_rec.create_dt,
2200 X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
2201 X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
2202 X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
2203 X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
2204 X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
2205 X_AWARD_CD =>gac_rec.award_cd,
2206 X_US_GROUP_NUMBER =>gac_rec.us_group_number,
2207 X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
2208 X_MODE => 'R');
2209 end loop;
2210 END;
2211 v_order_count := v_order_count + 1;
2212 END IF; -- p_group_multi_award_ind
2213 EXCEPTION
2214 WHEN e_resource_busy THEN
2215 -- Error 4713 for locking conflict exception and ROLLBACK.
2216 IF c_existing_order%ISOPEN THEN
2217 CLOSE c_existing_order;
2218 END IF;
2219 DBMS_SQL.CLOSE_CURSOR(v_gac_upd_c_handle);
2220 ROLLBACK TO sp_gac_upd;
2221 FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_AWD_CERM_CANNOT_UPDATE');
2222 --App_Exception.Raise_Exception;
2223 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2691809
2224 RETURN;
2225 WHEN OTHERS THEN
2226 ROLLBACK TO sp_gac_upd;
2227 RAISE;
2228 END;
2229 END LOOP; -- Dynamic Cursor associated with handle 'v_gac_upd_c_handle'
2230 DBMS_SQL.CLOSE_CURSOR(v_gac_upd_c_handle);
2231 COMMIT;
2232 END LOOP; -- c_gc
2233 --10. Return no error:
2234 RETURN;
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 IF c_gc%ISOPEN THEN
2238 CLOSE c_gc;
2239 END IF;
2240 IF c_awc%ISOPEN THEN
2241 CLOSE c_awc;
2242 END IF;
2243 IF c_acusg%ISOPEN THEN
2244 CLOSE c_acusg;
2245 END IF;
2246 IF c_gac_del%ISOPEN THEN
2247 CLOSE c_gac_del;
2248 END IF;
2249 IF DBMS_SQL.IS_OPEN(v_gac_upd_c_handle) THEN
2250 DBMS_SQL.CLOSE_CURSOR(v_gac_upd_c_handle);
2251 END IF;
2252 IF c_existing_order%ISOPEN THEN
2253 CLOSE c_existing_order;
2254 END IF;
2255 ROLLBACK;
2256 RAISE;
2257 END;
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 /*RETCODE:=2;
2261 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2262 commented for exception handler related changes */
2263
2264 RETCODE := 2;
2265 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2266 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2267
2268 END grdp_upd_gac_order;
2269
2270 END IGS_GR_GEN_002 ;