[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_GRADUAND_PKG
Source
1 package body IGS_GR_GRADUAND_PKG as
2 /* $Header: IGSGI12B.pls 115.16 2003/10/08 09:10:33 ijeddy ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_GR_GRADUAND_ALL%RowType;
6 new_references IGS_GR_GRADUAND_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_person_id IN NUMBER ,
12 x_create_dt IN DATE ,
13 x_grd_cal_type IN VARCHAR2 ,
14 x_grd_ci_sequence_number IN NUMBER ,
15 x_course_cd IN VARCHAR2 ,
16 x_award_course_cd IN VARCHAR2 ,
17 x_award_crs_version_number IN NUMBER ,
18 x_award_cd IN VARCHAR2 ,
19 x_graduand_status IN VARCHAR2 ,
20 x_graduand_appr_status IN VARCHAR2 ,
21 x_s_graduand_type IN VARCHAR2 ,
22 x_graduation_name IN VARCHAR2 ,
23 x_proxy_award_ind IN VARCHAR2 ,
24 x_proxy_award_person_id IN NUMBER ,
25 x_previous_qualifications IN VARCHAR2 ,
26 x_convocation_membership_ind IN VARCHAR2 ,
27 x_sur_for_course_cd IN VARCHAR2 ,
28 x_sur_for_crs_version_number IN NUMBER ,
29 x_sur_for_award_cd IN VARCHAR2 ,
30 x_comments IN VARCHAR2 ,
31 x_creation_date IN DATE ,
32 x_created_by IN NUMBER ,
33 x_last_update_date IN DATE ,
34 x_last_updated_by IN NUMBER ,
35 x_last_update_login IN NUMBER ,
36 x_org_id IN NUMBER ,
37 x_attribute_category IN VARCHAR2 ,
38 x_attribute1 IN VARCHAR2 ,
39 x_attribute2 IN VARCHAR2 ,
40 x_attribute3 IN VARCHAR2 ,
41 x_attribute4 IN VARCHAR2 ,
42 x_attribute5 IN VARCHAR2 ,
43 x_attribute6 IN VARCHAR2 ,
44 x_attribute7 IN VARCHAR2 ,
45 x_attribute8 IN VARCHAR2 ,
46 x_attribute9 IN VARCHAR2 ,
47 x_attribute10 IN VARCHAR2 ,
48 x_attribute11 IN VARCHAR2 ,
49 x_attribute12 IN VARCHAR2 ,
50 x_attribute13 IN VARCHAR2 ,
51 x_attribute14 IN VARCHAR2 ,
52 x_attribute15 IN VARCHAR2 ,
53 x_attribute16 IN VARCHAR2 ,
54 x_attribute17 IN VARCHAR2 ,
55 x_attribute18 IN VARCHAR2 ,
56 x_attribute19 IN VARCHAR2 ,
57 x_attribute20 IN VARCHAR2
58 ) AS
59
60 CURSOR cur_old_ref_values IS
61 SELECT *
62 FROM IGS_GR_GRADUAND_ALL
63 WHERE rowid = x_rowid;
64
65 BEGIN
66
67 l_rowid := x_rowid;
68
69 -- Code for setting the Old and New Reference Values.
70 -- Populate Old Values.
71 Open cur_old_ref_values;
72 Fetch cur_old_ref_values INTO old_references;
73 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
74 Close cur_old_ref_values;
75 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED1');
76 IGS_GE_MSG_STACK.ADD;
77 App_Exception.Raise_Exception;
78 Return;
79 END IF;
80 Close cur_old_ref_values;
81
82 -- Populate New Values.
83 new_references.person_id := x_person_id;
84 new_references.create_dt := x_create_dt;
85 new_references.grd_cal_type := x_grd_cal_type;
86 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
87 new_references.course_cd := x_course_cd;
88 new_references.award_course_cd := x_award_course_cd;
89 new_references.award_crs_version_number := x_award_crs_version_number;
90 new_references.award_cd := x_award_cd;
91 new_references.graduand_status := x_graduand_status;
92 new_references.graduand_appr_status := x_graduand_appr_status;
93 new_references.s_graduand_type := x_s_graduand_type;
94 new_references.graduation_name := x_graduation_name;
95 new_references.proxy_award_ind := x_proxy_award_ind;
96 new_references.proxy_award_person_id := x_proxy_award_person_id;
97 new_references.previous_qualifications := x_previous_qualifications;
98 new_references.convocation_membership_ind := x_convocation_membership_ind;
99 new_references.sur_for_course_cd := x_sur_for_course_cd;
100 new_references.sur_for_crs_version_number := x_sur_for_crs_version_number;
101 new_references.sur_for_award_cd := x_sur_for_award_cd;
102 new_references.comments := x_comments;
103 new_references.org_id := x_org_id;
104 new_references.attribute_category := x_attribute_category;
105 new_references.attribute1 := x_attribute1;
106 new_references.attribute2 := x_attribute2;
107 new_references.attribute3 := x_attribute3;
108 new_references.attribute4 := x_attribute4;
109 new_references.attribute5 := x_attribute5;
110 new_references.attribute6 := x_attribute6;
111 new_references.attribute7 := x_attribute7;
112 new_references.attribute8 := x_attribute8;
113 new_references.attribute9 := x_attribute9;
114 new_references.attribute10 := x_attribute10;
115 new_references.attribute11 := x_attribute11;
116 new_references.attribute12 := x_attribute12;
117 new_references.attribute13 := x_attribute13;
118 new_references.attribute14 := x_attribute14;
119 new_references.attribute15 := x_attribute15;
120 new_references.attribute16 := x_attribute16;
121 new_references.attribute17 := x_attribute17;
122 new_references.attribute18 := x_attribute18;
123 new_references.attribute19 := x_attribute19;
124 new_references.attribute20 := x_attribute20;
125 IF (p_action = 'UPDATE') THEN
126 new_references.creation_date := old_references.creation_date;
127 new_references.created_by := old_references.created_by;
128 ELSE
129 new_references.creation_date := x_creation_date;
130 new_references.created_by := x_created_by;
131 END IF;
132 new_references.last_update_date := x_last_update_date;
133 new_references.last_updated_by := x_last_updated_by;
134 new_references.last_update_login := x_last_update_login;
135
136 END Set_Column_Values;
137
138 -- Trigger description :-
139 -- "OSS_TST".trg_gr_br_iu
140 -- BEFORE INSERT OR UPDATE
141 -- ON IGS_GR_GRADUAND_ALL
142 -- FOR EACH ROW
143
144 PROCEDURE BeforeRowInsertUpdate1(
145 p_inserting IN BOOLEAN ,
146 p_updating IN BOOLEAN ,
147 p_deleting IN BOOLEAN
148 ) AS
149 v_message_name VARCHAR2(30);
150 BEGIN
151 IF p_inserting OR p_updating THEN
152 -- Validate the graduand record has the required details
153 IF IGS_GR_VAL_GR.grdp_val_gr_rqrd(
154 p_course_cd => new_references.course_cd,
155 p_graduand_status => new_references.graduand_status,
156 p_s_graduand_type => new_references.s_graduand_type,
157 p_award_course_cd => new_references.award_course_cd,
158 p_award_crs_version_number => new_references.award_crs_version_number,
159 p_award_cd => new_references.award_cd,
160 p_sur_for_course_cd => new_references.sur_for_course_cd,
161 p_sur_for_crs_version_number => new_references.sur_for_crs_version_number,
162 p_sur_for_award_cd => new_references.sur_for_award_cd,
163 p_message_name => v_message_name) = FALSE THEN
164 Fnd_Message.Set_Name('IGS', v_message_name);
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 -- Validate proxy award details
169 IF IGS_GR_VAL_GR.grdp_val_gr_proxy(
170 new_references.person_id,
171 new_references.s_graduand_type,
172 new_references.proxy_award_ind,
173 new_references.proxy_award_person_id,
174 v_message_name) = FALSE THEN
175 Fnd_Message.Set_Name('IGS', v_message_name);
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180 IF p_inserting OR (p_updating AND
181 NVL(new_references.grd_cal_type, 'NULL') <> NVL(old_references.grd_cal_type, 'NULL') OR
182 NVL(new_references.grd_ci_sequence_number, 0) <>
183 NVL(old_references.grd_ci_sequence_number, 0)) THEN
184 -- validate the ceremony round calendar instance
185 IF IGS_GR_VAL_GR.grdp_val_gr_crd_ci(
186 new_references.grd_cal_type,
187 new_references.grd_ci_sequence_number,
188 v_message_name) = FALSE THEN
189 Fnd_Message.Set_Name('IGS', v_message_name);
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 END IF;
193 END IF;
194 IF p_inserting OR (p_updating AND
195 new_references.graduand_status <> old_references.graduand_status) THEN
196 -- Validate graduand status is not closed
197 IF IGS_GR_VAL_GR.grdp_val_gst_closed(
198 new_references.graduand_status,
199 v_message_name) = FALSE THEN
200 Fnd_Message.Set_Name('IGS', v_message_name);
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END IF;
204 -- Validate the graduand status
205 IF IGS_GR_VAL_GR.grdp_val_gr_gst(
206 new_references.person_id,
207 new_references.create_dt,
208 new_references.course_cd,
209 new_references.graduand_appr_status,
210 new_references.s_graduand_type,
211 new_references.award_course_cd,
212 new_references.award_crs_version_number,
213 new_references.award_cd,
214 new_references.graduand_status,
215 old_references.graduand_status,
216 v_message_name) = FALSE THEN
217 Fnd_Message.Set_Name('IGS', v_message_name);
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception;
220 END IF;
221 END IF;
222 IF p_inserting OR (p_updating AND
223 new_references.graduand_appr_status <> old_references.graduand_appr_status) THEN
224 -- Validate graduand approval status is not closed
225 IF IGS_GR_VAL_GR.grdp_val_gas_closed(
226 new_references.graduand_appr_status,
227 v_message_name) = FALSE THEN
228 Fnd_Message.Set_Name('IGS', v_message_name);
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 -- Validate the graduand approval status
233 IF IGS_GR_VAL_GR.grdp_val_gr_gas(
234 new_references.person_id,
235 new_references.course_cd,
236 new_references.graduand_status,
237 new_references.graduand_appr_status,
238 old_references.graduand_appr_status,
239 v_message_name) = FALSE THEN
240 Fnd_Message.Set_Name('IGS', v_message_name);
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 END IF;
245 IF p_inserting OR (p_updating AND
246 NVL(new_references.course_cd,'NULL') <> NVL(old_references.course_cd,'NULL')) THEN
247 IF new_references.course_cd IS NOT NULL THEN
248 -- Validate the student course attempt course version graduates students
249 IF IGS_GR_VAL_GR.grdp_val_gr_sca(
250 new_references.person_id,
251 new_references.course_cd,
252 v_message_name) = FALSE THEN
253 Fnd_Message.Set_Name('IGS', v_message_name);
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257 END IF;
258 END IF;
259 IF p_inserting OR (p_updating AND
260 (new_references.award_cd <> old_references.award_cd OR
261 NVL(new_references.course_cd,'NULL') <> NVL(old_references.course_cd,'NULL'))) THEN
262 IF new_references.course_cd IS NOT NULL THEN
263 -- COURSE award
264 -- Validate the award type
265 IF IGS_GR_VAL_AWC.GRDP_VAL_AWARD_TYPE(
266 new_references.award_cd,
267 'COURSE',
268 v_message_name) = FALSE THEN
269 Fnd_Message.Set_Name('IGS', v_message_name);
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 -- Validate the graduand course award
274 IF IGS_GR_VAL_GR.grdp_val_gr_caw(
275 new_references.person_id,
276 new_references.course_cd,
277 new_references.award_course_cd,
278 new_references.award_crs_version_number,
279 new_references.award_cd,
280 v_message_name) = FALSE THEN
281 Fnd_Message.Set_Name('IGS', v_message_name);
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 ELSE
286 -- HONORARY award
287 -- Validate the award type
288 IF IGS_GR_VAL_AWC.GRDP_VAL_AWARD_TYPE(
289 new_references.award_cd,
290 'HONORARY',
291 v_message_name) = FALSE THEN
292 Fnd_Message.Set_Name('IGS', v_message_name);
293 IGS_GE_MSG_STACK.ADD;
294 App_Exception.Raise_Exception;
295 END IF;
296 -- Validate award is not closed
297 IF igs_gr_val_awc.crsp_val_aw_closed(
298 new_references.award_cd,
299 v_message_name) = FALSE THEN
300 Fnd_Message.Set_Name('IGS', v_message_name);
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 END IF;
305 END IF;
306 IF p_inserting OR (p_updating AND
307 new_references.s_graduand_type <> old_references.s_graduand_type) THEN
308 -- Validate the system graduand type
309 IF IGS_GR_VAL_GR.grdp_val_gr_type(
310 new_references.person_id,
311 new_references.create_dt,
312 new_references.course_cd,
313 new_references.graduand_status,
314 new_references.s_graduand_type,
315 old_references.s_graduand_type,
316 v_message_name) = FALSE THEN
317 Fnd_Message.Set_Name('IGS', v_message_name);
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 END IF;
321 END IF;
322 IF p_inserting OR (p_updating AND
323 (new_references.person_id <> old_references.person_id OR
324 NVL(new_references.course_cd,'NULL') <> NVL(old_references.course_cd,'NULL') OR
325 NVL(new_references.sur_for_course_cd,'NULL') <> NVL(old_references.sur_for_course_cd,'NULL') OR
326 NVL(new_references.sur_for_crs_version_number,0) <>
327 NVL(old_references.sur_for_crs_version_number,0) OR
328 NVL(new_references.sur_for_award_cd,'NULL') <> NVL(old_references.sur_for_award_cd,'NULL'))) THEN
329 -- Validate the surrender for details
330 IF IGS_GR_VAL_GR.grdp_val_gr_sur_caw(
331 new_references.person_id,
332 new_references.course_cd,
333 new_references.graduand_status,
334 new_references.sur_for_course_cd,
335 new_references.sur_for_crs_version_number,
336 new_references.sur_for_award_cd,
337 v_message_name) = FALSE THEN
338 Fnd_Message.Set_Name('IGS', v_message_name);
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END IF;
342 END IF;
343
344
345 END BeforeRowInsertUpdate1;
346
347 -- Trigger description :-
348 -- "OSS_TST".trg_gr_ar_iu
349 -- AFTER INSERT OR UPDATE
350 -- ON IGS_GR_GRADUAND_ALL
351 -- FOR EACH ROW
352
353 PROCEDURE AfterRowInsertUpdate2(
354 p_inserting IN BOOLEAN ,
355 p_updating IN BOOLEAN ,
356 p_deleting IN BOOLEAN
357 ) AS
358 v_message_name VARCHAR2(30);
359 v_rowid_saved BOOLEAN := FALSE;
360 BEGIN
361 IF p_inserting OR p_updating THEN
362 -- validate graduand award uniqueness
363 -- Save the rowid of the current row.
364 -- Cannot call grdp_val_gr_unique because trigger
365 -- will be mutating.
366 -- validate graduand award uniqueness
367 IF IGS_GR_VAL_GR.grdp_val_gr_unique(
368 new_references.person_id,
369 new_references.create_dt,
370 new_references.grd_cal_type,
371 new_references.grd_ci_sequence_number,
372 new_references.award_course_cd,
373 new_references.award_crs_version_number,
374 new_references.award_cd,
375 v_message_name) = FALSE THEN
376 Fnd_Message.Set_Name('IGS', v_message_name);
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 END IF;
380 v_rowid_saved := TRUE;
381 END IF;
382 END AfterRowInsertUpdate2;
383
384 -- Trigger description :-
385 -- "OSS_TST".trg_gr_ar_u_hist
386 -- AFTER UPDATE
387 -- ON IGS_GR_GRADUAND_ALL
388 -- FOR EACH ROW
389
390 PROCEDURE AfterRowUpdate3(
391 p_inserting IN BOOLEAN ,
392 p_updating IN BOOLEAN ,
393 p_deleting IN BOOLEAN
394 ) AS
395 BEGIN
396 -- create a history
397 IGS_GR_GEN_002.GRDP_INS_GR_HIST(
398 p_person_id => old_references.person_id,
399 p_create_dt => old_references.create_dt,
400 p_old_grd_cal_type => old_references.grd_cal_type,
401 p_new_grd_cal_type => new_references.grd_cal_type,
402 p_old_grd_ci_sequence_number => old_references.grd_ci_sequence_number,
403 p_new_grd_ci_sequence_number => new_references.grd_ci_sequence_number,
404 p_old_course_cd => old_references.course_cd,
405 p_new_course_cd => new_references.course_cd,
406 p_old_award_course_cd => old_references.award_course_cd,
407 p_new_award_course_cd => new_references.award_course_cd,
408 p_old_award_crs_version_number => old_references.award_crs_version_number,
409 p_new_award_crs_version_number => new_references.award_crs_version_number,
410 p_old_award_cd => old_references.award_cd,
411 p_new_award_cd => new_references.award_cd,
412 p_old_graduand_status => old_references.graduand_status,
413 p_new_graduand_status => new_references.graduand_status,
414 p_old_graduand_appr_status => old_references.graduand_appr_status,
415 p_new_graduand_appr_status => new_references.graduand_appr_status,
416 p_old_s_graduand_type => old_references.s_graduand_type,
417 p_new_s_graduand_type => new_references.s_graduand_type,
418 p_old_graduation_name => old_references.graduation_name,
419 p_new_graduation_name => new_references.graduation_name,
420 p_old_proxy_award_ind => old_references.proxy_award_ind,
421 p_new_proxy_award_ind => new_references.proxy_award_ind,
422 p_old_proxy_award_person_id => old_references.proxy_award_person_id,
423 p_new_proxy_award_person_id => new_references.proxy_award_person_id,
424 p_old_previous_qualifications => old_references.previous_qualifications,
425 p_new_previous_qualifications => new_references.previous_qualifications,
426 p_old_convocation_memb_ind => old_references.convocation_membership_ind,
427 p_new_convocation_memb_ind => new_references.convocation_membership_ind,
428 p_old_sur_for_course_cd => old_references.sur_for_course_cd,
429 p_new_sur_for_course_cd => new_references.sur_for_course_cd,
430 p_old_sur_for_crs_version_numb => old_references.sur_for_crs_version_number,
431 p_new_sur_for_crs_version_numb => new_references.sur_for_crs_version_number,
432 p_old_sur_for_award_cd => old_references.sur_for_award_cd,
433 p_new_sur_for_award_cd => new_references.sur_for_award_cd,
434 p_old_update_who => old_references.last_updated_by,
435 p_new_update_who => new_references.last_updated_by,
436 p_old_update_on => old_references.last_update_date,
437 p_new_update_on => new_references.last_update_date,
438 p_old_comments => old_references.comments,
439 p_new_comments => new_references.comments);
440
441
442 END AfterRowUpdate3;
443
444 PROCEDURE before_insert_update(p_inserting IN BOOLEAN DEFAULT FALSE,
445 p_updating IN BOOLEAN DEFAULT FALSE ) AS
446 CURSOR c_closed_ind (cp_c_award_cd IN IGS_PS_AWARD.AWARD_CD%TYPE,
447 cp_c_course_cd IN IGS_PS_AWARD.COURSE_CD%TYPE,
448 cp_n_version_num IN IGS_PS_AWARD.VERSION_NUMBER%TYPE) IS
449 SELECT CLOSED_IND
450 FROM IGS_PS_AWARD
451 WHERE AWARD_CD = cp_c_award_cd
452 AND COURSE_CD = cp_c_course_cd
453 AND VERSION_NUMBER = cp_n_version_num;
454
455 l_c_closed_ind VARCHAR2(1);
456
457 BEGIN
458 IF p_inserting OR ( p_updating AND new_references.award_cd <> old_references.award_cd ) THEN
459 OPEN c_closed_ind(new_references.award_cd,new_references.award_course_cd, new_references.award_crs_version_number);
460 FETCH c_closed_ind INTO l_c_closed_ind;
461 CLOSE c_closed_ind;
462 IF l_c_closed_ind = 'Y' THEN
463 fnd_message.set_name('IGS','IGS_PS_AWD_CD_CLOSED');
464 igs_ge_msg_stack.add;
465 app_exception.raise_exception;
466 END IF;
467 END IF;
468
469 IF p_inserting OR ( p_updating AND new_references.sur_for_award_cd <> old_references.sur_for_award_cd ) THEN
470 OPEN c_closed_ind(new_references.sur_for_award_cd, new_references.sur_for_course_cd, new_references.sur_for_crs_version_number);
471 FETCH c_closed_ind INTO l_c_closed_ind;
472 CLOSE c_closed_ind;
473 IF l_c_closed_ind = 'Y' THEN
474 fnd_message.set_name('IGS','IGS_PS_AWD_CD_CLOSED');
475 igs_ge_msg_stack.add;
476 app_exception.raise_exception;
477 END IF;
478 END IF;
479 END before_insert_update;
480
481 PROCEDURE Check_Parent_Existance AS
482 BEGIN
483
484 IF (((old_references.award_cd = new_references.award_cd)) OR
485 ((new_references.award_cd IS NULL))) THEN
486 NULL;
487 ELSE
488 IF NOT IGS_PS_AWD_PKG.Get_PK_For_Validation (
489 new_references.award_cd
490 ) THEN
491 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED2');
492 IGS_GE_MSG_STACK.ADD;
493 APP_EXCEPTION.RAISE_EXCEPTION;
494 END IF;
495 END IF;
496
497 IF (((old_references.award_course_cd = new_references.award_course_cd) AND
498 (old_references.award_crs_version_number = new_references.award_crs_version_number) AND
499 (old_references.award_cd = new_references.award_cd)) OR
500 ((new_references.award_course_cd IS NULL) OR
501 (new_references.award_crs_version_number IS NULL) OR
502 (new_references.award_cd IS NULL))) THEN
503 NULL;
504 ELSE
505 IF NOT IGS_PS_AWARD_PKG.Get_PK_For_Validation (
506 new_references.award_course_cd,
507 new_references.award_crs_version_number,
508 new_references.award_cd
509 ) THEN
510 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED3');
511 IGS_GE_MSG_STACK.ADD;
512 APP_EXCEPTION.RAISE_EXCEPTION;
513 END IF;
514 END IF;
515
516 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
517 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
518 ((new_references.grd_cal_type IS NULL) OR
519 (new_references.grd_ci_sequence_number IS NULL))) THEN
520 NULL;
521 ELSE
522 IF NOT IGS_GR_CRMN_ROUND_PKG.Get_PK_For_Validation (
523 new_references.grd_cal_type,
524 new_references.grd_ci_sequence_number
525 ) THEN
526 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED4');
527 APP_EXCEPTION.RAISE_EXCEPTION;
528 END IF;
529 END IF;
530
531 IF (((old_references.graduand_appr_status = new_references.graduand_appr_status)) OR
532 ((new_references.graduand_appr_status IS NULL))) THEN
533 NULL;
534 ELSE
535 IF NOT IGS_GR_APRV_STAT_PKG.Get_PK_For_Validation (
536 new_references.graduand_appr_status
537 ) THEN
538 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED5');
539 IGS_GE_MSG_STACK.ADD;
540 APP_EXCEPTION.RAISE_EXCEPTION;
541 END IF;
542 END IF;
543
544 IF (((old_references.graduand_status = new_references.graduand_status)) OR
545 ((new_references.graduand_status IS NULL))) THEN
546 NULL;
547 ELSE
548 IF NOT IGS_GR_STAT_PKG.Get_PK_For_Validation (
549 new_references.graduand_status
550 ) THEN
551 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED6');
552 IGS_GE_MSG_STACK.ADD;
553 APP_EXCEPTION.RAISE_EXCEPTION;
554 END IF;
555 END IF;
556
557
558 IF (((old_references.person_id = new_references.person_id)) OR
559 ((new_references.person_id IS NULL))) THEN
560 NULL;
561 ELSE
562 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
563 new_references.person_id
564 ) THEN
565 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED7');
566 IGS_GE_MSG_STACK.ADD;
567 APP_EXCEPTION.RAISE_EXCEPTION;
568 END IF;
569 END IF;
570
571 IF (((old_references.proxy_award_person_id = new_references.proxy_award_person_id)) OR
572 ((new_references.proxy_award_person_id IS NULL))) THEN
573 NULL;
574 ELSE
575 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
576 new_references.proxy_award_person_id
577 ) THEN
578 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED8');
579 IGS_GE_MSG_STACK.ADD;
580 APP_EXCEPTION.RAISE_EXCEPTION;
581 END IF;
582 END IF;
583
584 IF (((old_references.person_id = new_references.person_id) AND
585 (old_references.course_cd = new_references.course_cd)) OR
586 ((new_references.person_id IS NULL) OR
587 (new_references.course_cd IS NULL))) THEN
588 NULL;
589 ELSE
590 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
591 new_references.person_id,
592 new_references.course_cd
593 ) THEN
594 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED9');
595 IGS_GE_MSG_STACK.ADD;
596 APP_EXCEPTION.RAISE_EXCEPTION;
597 END IF;
598 END IF;
599
600 IF (((old_references.sur_for_course_cd = new_references.sur_for_course_cd) AND
601 (old_references.sur_for_crs_version_number = new_references.sur_for_crs_version_number) AND
602 (old_references.sur_for_award_cd = new_references.sur_for_award_cd)) OR
603 ((new_references.sur_for_course_cd IS NULL) OR
604 (new_references.sur_for_crs_version_number IS NULL) OR
605 (new_references.sur_for_award_cd IS NULL))) THEN
606 NULL;
607 ELSE
608 IF NOT IGS_PS_AWARD_PKG.Get_PK_For_Validation (
609 new_references.sur_for_course_cd,
610 new_references.sur_for_crs_version_number,
611 new_references.sur_for_award_cd
612 ) THEN
613 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED0');
614 IGS_GE_MSG_STACK.ADD;
615 APP_EXCEPTION.RAISE_EXCEPTION;
616 END IF;
617 END IF;
618
619 END Check_Parent_Existance;
620
621 PROCEDURE Check_Uniqueness AS
622 BEGIN
623 IF Get_UK_For_Validation (
624 NEW_REFERENCES.person_id,
625 NEW_REFERENCES.create_dt,
626 NEW_REFERENCES.award_course_cd,
627 NEW_REFERENCES.award_crs_version_number,
628 NEW_REFERENCES.award_cd) THEN
629 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
630 IGS_GE_MSG_STACK.ADD;
631 APP_EXCEPTION.RAISE_EXCEPTION;
632 END IF;
633 END Check_Uniqueness;
634
635 PROCEDURE CHECK_CONSTRAINTS(
636 Column_Name IN VARCHAR2 ,
637 Column_Value IN VARCHAR2
638 ) AS
639 BEGIN
640 IF Column_Name is null THEN
641 NULL;
642 ELSIF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
643 new_references.GRD_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
644
645 ELSIF upper(Column_name) = 'PROXY_AWARD_IND' THEN
646 new_references.PROXY_AWARD_IND:= COLUMN_VALUE ;
647
648 ELSIF upper(Column_name) = 'CONVOCATION_MEMBERSHIP_IND' THEN
649 new_references.CONVOCATION_MEMBERSHIP_IND:= COLUMN_VALUE ;
650
651 ELSIF upper(Column_name) = 'S_GRADUAND_TYPE' THEN
652 new_references.S_GRADUAND_TYPE:= COLUMN_VALUE ;
653
654 ELSIF upper(Column_name) = 'AWARD_CD' THEN
655 new_references.AWARD_CD:= COLUMN_VALUE ;
656
657 ELSIF upper(Column_name) = 'AWARD_COURSE_CD' THEN
658 new_references.AWARD_COURSE_CD:= COLUMN_VALUE ;
659
660 ELSIF upper(Column_name) = 'COURSE_CD' THEN
661 new_references.COURSE_CD:= COLUMN_VALUE ;
662
663 ELSIF upper(Column_name) = 'PROXY_AWARD_IND' THEN
664 new_references.PROXY_AWARD_IND:= COLUMN_VALUE ;
665
666 ELSIF upper(Column_name) = 'SUR_FOR_AWARD_CD' THEN
667 new_references.SUR_FOR_AWARD_CD:= COLUMN_VALUE ;
668
669 ELSIF upper(Column_name) = 'SUR_FOR_COURSE_CD' THEN
670 new_references.SUR_FOR_COURSE_CD:= COLUMN_VALUE ;
671
672 ELSIF upper(Column_name) = 'S_GRADUAND_TYPE' THEN
673 new_references.S_GRADUAND_TYPE:= COLUMN_VALUE ;
674
675 END IF ;
676
677 IF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
678 IF new_references.GRD_CI_SEQUENCE_NUMBER < 1 OR new_references.GRD_CI_SEQUENCE_NUMBER > 999999 then
679 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
680 IGS_GE_MSG_STACK.ADD;
681 App_Exception.Raise_Exception ;
682 END IF;
683
684 END IF ;
685
686 IF upper(Column_name) = 'PROXY_AWARD_IND' OR COLUMN_NAME IS NULL THEN
687 IF new_references.PROXY_AWARD_IND not in ('Y','N') then
688 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
689 IGS_GE_MSG_STACK.ADD;
690 App_Exception.Raise_Exception ;
691 END IF;
692
693 END IF ;
694
695 IF upper(Column_name) = 'CONVOCATION_MEMBERSHIP_IND' OR COLUMN_NAME IS NULL THEN
696 IF new_references.CONVOCATION_MEMBERSHIP_IND not in ('Y','N') then
697 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
698 IGS_GE_MSG_STACK.ADD;
699 App_Exception.Raise_Exception ;
700 END IF;
701 END IF ;
702
703 IF upper(Column_name) = 'S_GRADUAND_TYPE' OR COLUMN_NAME IS NULL THEN
704 IF new_references.S_GRADUAND_TYPE not in ( 'ATTENDING' , 'INABSENTIA' , 'ARTICULATE' , 'DEFERRED' , 'UNKNOWN' , 'DECLINED' ) then
705 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
706 IGS_GE_MSG_STACK.ADD;
707 App_Exception.Raise_Exception ;
708 END IF;
709 END IF ;
710
711 IF upper(Column_name) = 'AWARD_CD' OR COLUMN_NAME IS NULL THEN
712 IF new_references.AWARD_CD<> upper(new_references.AWARD_CD) then
713 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
714 IGS_GE_MSG_STACK.ADD;
715 App_Exception.Raise_Exception ;
716 END IF;
717
718 END IF ;
719
720 IF upper(Column_name) = 'AWARD_COURSE_CD' OR COLUMN_NAME IS NULL THEN
721 IF new_references.AWARD_COURSE_CD<> upper(new_references.AWARD_COURSE_CD) then
722 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
723 IGS_GE_MSG_STACK.ADD;
724 App_Exception.Raise_Exception ;
725 END IF;
726
727 END IF ;
728
729
730 IF upper(Column_name) = 'COURSE_CD' OR COLUMN_NAME IS NULL THEN
731 IF new_references.COURSE_CD<> upper(new_references.COURSE_CD) then
732 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
733 IGS_GE_MSG_STACK.ADD;
734 App_Exception.Raise_Exception ;
735 END IF;
736
737 END IF ;
738
739 IF upper(Column_name) = 'PROXY_AWARD_IND' OR COLUMN_NAME IS NULL THEN
740 IF new_references.PROXY_AWARD_IND<> upper(new_references.PROXY_AWARD_IND) then
741 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
742 IGS_GE_MSG_STACK.ADD;
743 App_Exception.Raise_Exception ;
744 END IF;
745
746 END IF ;
747
748 IF upper(Column_name) = 'SUR_FOR_AWARD_CD' OR COLUMN_NAME IS NULL THEN
749 IF new_references.SUR_FOR_AWARD_CD<> upper(new_references.SUR_FOR_AWARD_CD) then
750 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
751 IGS_GE_MSG_STACK.ADD;
752 App_Exception.Raise_Exception ;
753 END IF;
754
755 END IF ;
756
757 IF upper(Column_name) = 'SUR_FOR_COURSE_CD' OR COLUMN_NAME IS NULL THEN
758 IF new_references.SUR_FOR_COURSE_CD<> upper(new_references.SUR_FOR_COURSE_CD) then
759 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
760 IGS_GE_MSG_STACK.ADD;
761 App_Exception.Raise_Exception ;
762 END IF;
763
764 END IF ;
765
766 IF upper(Column_name) = 'S_GRADUAND_TYPE' OR COLUMN_NAME IS NULL THEN
767 IF new_references.S_GRADUAND_TYPE<> upper(new_references.S_GRADUAND_TYPE) then
768 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
769 IGS_GE_MSG_STACK.ADD;
770 App_Exception.Raise_Exception ;
771 END IF;
772
773 END IF ;
774 END CHECK_CONSTRAINTS;
775
776
777
778 PROCEDURE Check_Child_Existance AS
779 BEGIN
780
781 IGS_GR_AWD_CRMN_PKG.GET_UFK_IGS_GR_GRADUAND (
782 old_references.person_id,
783 old_references.create_dt,
784 old_references.award_course_cd,
785 old_references.award_crs_version_number,
786 old_references.award_cd
787 );
788
789 IGS_GR_AWD_CRMN_HIST_PKG.GET_FK_IGS_GR_GRADUAND (
790 old_references.person_id,
791 old_references.create_dt
792 );
793
794 END Check_Child_Existance;
795
796 PROCEDURE Check_UK_Child_Existance AS
797 BEGIN
798
799 IF (((old_references.person_id = new_references.person_id) AND
800 (old_references.create_dt = new_references.create_dt) AND
801 (old_references.award_course_cd = new_references.award_course_cd) AND
802 (old_references.award_crs_version_number = new_references.award_crs_version_number)) AND
803 (old_references.award_cd = new_references.award_cd) OR
804 ((old_references.person_id IS NULL) AND
805 (old_references.create_dt IS NULL) AND
806 (old_references.award_course_cd IS NULL) AND
807 (old_references.award_crs_version_number IS NULL) AND
808 (old_references.award_cd IS NULL))) THEN
809 NULL;
810 ELSE
811 IGS_GR_AWD_CRMN_PKG.GET_UFK_IGS_GR_GRADUAND(
812 old_references.person_id,
813 old_references.create_dt,
814 old_references.award_course_cd,
815 old_references.award_crs_version_number,
816 old_references.award_cd
817 );
818 END IF;
819 END Check_UK_Child_Existance;
820
821 FUNCTION Get_PK_For_Validation (
822 x_person_id IN NUMBER,
823 x_create_dt IN DATE
824 ) RETURN BOOLEAN AS
825
826 CURSOR cur_rowid IS
827 SELECT rowid
828 FROM IGS_GR_GRADUAND_ALL
829 WHERE person_id = x_person_id
830 AND create_dt = x_create_dt
831 FOR UPDATE NOWAIT;
832
833 lv_rowid cur_rowid%RowType;
834
835 BEGIN
836
837 Open cur_rowid;
838 Fetch cur_rowid INTO lv_rowid;
839 IF (cur_rowid%FOUND) THEN
840 Close cur_rowid;
841 Return (TRUE);
842 ELSE
843 Close cur_rowid;
844 Return (FALSE);
845 END IF;
846
847 END Get_PK_For_Validation;
848
849 FUNCTION Get_UK_For_Validation (
850 x_person_id IN NUMBER,
851 x_create_dt IN DATE,
852 x_award_course_cd IN VARCHAR2,
853 x_award_crs_version_number IN NUMBER,
854 x_award_cd IN VARCHAR2
855 ) RETURN BOOLEAN AS
856
857 CURSOR cur_rowid IS
858 SELECT rowid
859 FROM IGS_GR_GRADUAND_ALL
860 WHERE person_id = x_person_id
861 AND create_dt = x_create_dt
862 AND award_course_cd = x_award_course_cd
863 AND award_crs_version_number = x_award_crs_version_number
864 AND award_cd = x_award_cd
865 AND (l_rowid is null or rowid <> l_rowid )
866 FOR UPDATE NOWAIT;
867
868 lv_rowid cur_rowid%RowType;
869
870 BEGIN
871
872 Open cur_rowid;
873 Fetch cur_rowid INTO lv_rowid;
874 IF (cur_rowid%FOUND) THEN
875 Close cur_rowid;
876 Return (TRUE);
877 ELSE
878 Close cur_rowid;
879 Return (FALSE);
880 END IF;
881 Close cur_rowid;
882
883 END Get_UK_For_Validation;
884
885 PROCEDURE GET_FK_IGS_GR_CRMN_ROUND (
886 x_grd_cal_type IN VARCHAR2,
887 x_grd_ci_sequence_number IN NUMBER
888 ) AS
889
890 CURSOR cur_rowid IS
891 SELECT rowid
892 FROM IGS_GR_GRADUAND_ALL
893 WHERE grd_cal_type = x_grd_cal_type
894 AND grd_ci_sequence_number = x_grd_ci_sequence_number ;
895
896 lv_rowid cur_rowid%RowType;
897
898 BEGIN
899
900 Open cur_rowid;
901 Fetch cur_rowid INTO lv_rowid;
902 IF (cur_rowid%FOUND) THEN
903 Close cur_rowid;
904 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GR_CRD_FK');
905 IGS_GE_MSG_STACK.ADD;
906 App_Exception.Raise_Exception;
907 Return;
908 END IF;
909 Close cur_rowid;
910
911 END GET_FK_IGS_GR_CRMN_ROUND;
912
913 PROCEDURE GET_FK_IGS_GR_APRV_STAT (
914 x_graduand_appr_status IN VARCHAR2
915 ) AS
916
917 CURSOR cur_rowid IS
918 SELECT rowid
919 FROM IGS_GR_GRADUAND_ALL
920 WHERE graduand_appr_status = x_graduand_appr_status ;
921
922 lv_rowid cur_rowid%RowType;
923
924 BEGIN
925
926 Open cur_rowid;
927 Fetch cur_rowid INTO lv_rowid;
928 IF (cur_rowid%FOUND) THEN
929 Close cur_rowid;
930 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GR_GAS_FK');
931 IGS_GE_MSG_STACK.ADD;
932 App_Exception.Raise_Exception;
933 Return;
934 END IF;
935 Close cur_rowid;
936
937 END GET_FK_IGS_GR_APRV_STAT;
938
939 PROCEDURE GET_FK_IGS_GR_STAT (
940 x_graduand_status IN VARCHAR2
941 ) AS
942
943 CURSOR cur_rowid IS
944 SELECT rowid
945 FROM IGS_GR_GRADUAND_ALL
946 WHERE graduand_status = x_graduand_status ;
947
948 lv_rowid cur_rowid%RowType;
949
950 BEGIN
951
952 Open cur_rowid;
953 Fetch cur_rowid INTO lv_rowid;
954 IF (cur_rowid%FOUND) THEN
955 Close cur_rowid;
956 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GR_GST_FK');
957 IGS_GE_MSG_STACK.ADD;
958 App_Exception.Raise_Exception;
959 Return;
960 END IF;
961 Close cur_rowid;
962
963 END GET_FK_IGS_GR_STAT;
964
965 PROCEDURE GET_FK_IGS_GR_HONOURS_LEVEL (
966 x_honours_level IN VARCHAR2
967 ) AS
968 BEGIN
969 NULL;
970 END GET_FK_IGS_GR_HONOURS_LEVEL;
971
972 PROCEDURE GET_FK_IGS_PE_PERSON (
973 x_person_id IN NUMBER
974 ) AS
975
976 CURSOR cur_rowid IS
977 SELECT rowid
978 FROM IGS_GR_GRADUAND_ALL
979 WHERE (person_id = x_person_id)
980 OR (proxy_award_person_id = x_person_id);
981
982 lv_rowid cur_rowid%RowType;
983
984 BEGIN
985
986 Open cur_rowid;
987 Fetch cur_rowid INTO lv_rowid;
988 IF (cur_rowid%FOUND) THEN
989 Close cur_rowid;
990 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GR_PE_FK');
991 IGS_GE_MSG_STACK.ADD;
992 App_Exception.Raise_Exception;
993 Return;
994 END IF;
995 Close cur_rowid;
996
997 END GET_FK_IGS_PE_PERSON;
998
999 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
1000 x_person_id IN NUMBER,
1001 x_course_cd IN VARCHAR2
1002 ) AS
1003
1004 CURSOR cur_rowid IS
1005 SELECT rowid
1006 FROM IGS_GR_GRADUAND_ALL
1007 WHERE person_id = x_person_id
1008 AND course_cd = x_course_cd ;
1009
1010 lv_rowid cur_rowid%RowType;
1011
1012 BEGIN
1013
1014 Open cur_rowid;
1015 Fetch cur_rowid INTO lv_rowid;
1016 IF (cur_rowid%FOUND) THEN
1017 Close cur_rowid;
1018 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GR_SCA_FK');
1019 IGS_GE_MSG_STACK.ADD;
1020 App_Exception.Raise_Exception;
1021 Return;
1022 END IF;
1023 Close cur_rowid;
1024
1025 END GET_FK_IGS_EN_STDNT_PS_ATT;
1026
1027 PROCEDURE GET_FK_IGS_EN_SPA_AWD (
1028 x_person_id IN NUMBER,
1029 x_course_cd IN VARCHAR2,
1030 x_award_cd IN VARCHAR2
1031 ) AS
1032
1033 CURSOR cur_rowid IS
1034 SELECT rowid
1035 FROM IGS_GR_GRADUAND_ALL
1036 WHERE person_id = x_person_id
1037 AND course_cd = x_course_cd
1038 AND award_cd = x_award_cd;
1039
1040 lv_rowid cur_rowid%RowType;
1041
1042 BEGIN
1043
1044 Open cur_rowid;
1045 Fetch cur_rowid INTO lv_rowid;
1046 IF (cur_rowid%FOUND) THEN
1047 Close cur_rowid;
1048 Fnd_Message.Set_Name ('IGS', 'IGS_GR_SPAA_AW_CCD_FK');
1049 IGS_GE_MSG_STACK.ADD;
1050 App_Exception.Raise_Exception;
1051 Return;
1052 END IF;
1053 Close cur_rowid;
1054
1055 END GET_FK_IGS_EN_SPA_AWD;
1056
1057 PROCEDURE Before_DML (
1058 p_action IN VARCHAR2,
1059 x_rowid IN VARCHAR2 ,
1060 x_person_id IN NUMBER ,
1061 x_create_dt IN DATE ,
1062 x_grd_cal_type IN VARCHAR2 ,
1063 x_grd_ci_sequence_number IN NUMBER ,
1064 x_course_cd IN VARCHAR2 ,
1065 x_award_course_cd IN VARCHAR2 ,
1066 x_award_crs_version_number IN NUMBER ,
1067 x_award_cd IN VARCHAR2 ,
1068 x_honours_level IN VARCHAR2 DEFAULT NULL,
1069 x_conferral_dt IN DATE DEFAULT NULL,
1070 x_graduand_status IN VARCHAR2 ,
1071 x_graduand_appr_status IN VARCHAR2 ,
1072 x_s_graduand_type IN VARCHAR2 ,
1073 x_graduation_name IN VARCHAR2 ,
1074 x_proxy_award_ind IN VARCHAR2 ,
1075 x_proxy_award_person_id IN NUMBER ,
1076 x_previous_qualifications IN VARCHAR2 ,
1077 x_convocation_membership_ind IN VARCHAR2 ,
1078 x_sur_for_course_cd IN VARCHAR2 ,
1079 x_sur_for_crs_version_number IN NUMBER ,
1080 x_sur_for_award_cd IN VARCHAR2 ,
1081 x_comments IN VARCHAR2 ,
1082 x_creation_date IN DATE ,
1083 x_created_by IN NUMBER ,
1084 x_last_update_date IN DATE ,
1085 x_last_updated_by IN NUMBER ,
1086 x_last_update_login IN NUMBER ,
1087 x_org_id IN NUMBER ,
1088 x_attribute_category IN VARCHAR2 ,
1089 x_attribute1 IN VARCHAR2 ,
1090 x_attribute2 IN VARCHAR2 ,
1091 x_attribute3 IN VARCHAR2 ,
1092 x_attribute4 IN VARCHAR2 ,
1093 x_attribute5 IN VARCHAR2 ,
1094 x_attribute6 IN VARCHAR2 ,
1095 x_attribute7 IN VARCHAR2 ,
1096 x_attribute8 IN VARCHAR2 ,
1097 x_attribute9 IN VARCHAR2 ,
1098 x_attribute10 IN VARCHAR2 ,
1099 x_attribute11 IN VARCHAR2 ,
1100 x_attribute12 IN VARCHAR2 ,
1101 x_attribute13 IN VARCHAR2 ,
1102 x_attribute14 IN VARCHAR2 ,
1103 x_attribute15 IN VARCHAR2 ,
1104 x_attribute16 IN VARCHAR2 ,
1105 x_attribute17 IN VARCHAR2 ,
1106 x_attribute18 IN VARCHAR2 ,
1107 x_attribute19 IN VARCHAR2 ,
1108 x_attribute20 IN VARCHAR2
1109 ) AS
1110 BEGIN
1111
1112 Set_Column_Values (
1113 p_action,
1114 x_rowid,
1115 x_person_id,
1116 x_create_dt,
1117 x_grd_cal_type,
1118 x_grd_ci_sequence_number,
1119 x_course_cd,
1120 x_award_course_cd,
1121 x_award_crs_version_number,
1122 x_award_cd,
1123 x_graduand_status,
1124 x_graduand_appr_status,
1125 x_s_graduand_type,
1126 x_graduation_name,
1127 x_proxy_award_ind,
1128 x_proxy_award_person_id,
1129 x_previous_qualifications,
1130 x_convocation_membership_ind,
1131 x_sur_for_course_cd,
1132 x_sur_for_crs_version_number,
1133 x_sur_for_award_cd,
1134 x_comments,
1135 x_creation_date,
1136 x_created_by,
1137 x_last_update_date,
1138 x_last_updated_by,
1139 x_last_update_login ,
1140 x_org_id,
1141 x_attribute_category,
1142 x_attribute1,
1143 x_attribute2,
1144 x_attribute3,
1145 x_attribute4,
1146 x_attribute5,
1147 x_attribute6,
1148 x_attribute7,
1149 x_attribute8,
1150 x_attribute9,
1151 x_attribute10,
1152 x_attribute11,
1153 x_attribute12,
1154 x_attribute13,
1155 x_attribute14,
1156 x_attribute15,
1157 x_attribute16,
1158 x_attribute17,
1159 x_attribute18,
1160 x_attribute19,
1161 x_attribute20
1162 );
1163
1164 IF (p_action = 'INSERT') THEN
1165 -- Call all the procedures related to Before Insert.
1166 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
1167 p_updating => FALSE,
1168 p_deleting => FALSE );
1169 before_insert_update( p_inserting => TRUE, p_updating => FALSE);
1170 IF GET_PK_FOR_VALIDATION(
1171 NEW_REFERENCES.person_id,
1172 NEW_REFERENCES.create_dt
1173 ) THEN
1174 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
1175 IGS_GE_MSG_STACK.ADD;
1176 App_Exception.Raise_Exception;
1177 END IF;
1178 check_uniqueness;
1179 check_constraints;
1180 Check_Parent_Existance;
1181 ELSIF (p_action = 'UPDATE') THEN
1182 -- Call all the procedures related to Before Update.
1183 BeforeRowInsertUpdate1 ( p_updating => TRUE ,
1184 p_inserting => FALSE,
1185 p_deleting => FALSE);
1186 before_insert_update( p_inserting => FALSE, p_updating => TRUE);
1187 check_uniqueness;
1188 check_constraints;
1189 Check_Parent_Existance;
1190 Check_UK_Child_Existance;
1191 ELSIF (p_action = 'DELETE') THEN
1192 -- Call all the procedures related to Before Delete.
1193 Check_Child_Existance;
1194 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1195 IF GET_PK_FOR_VALIDATION(
1196 NEW_REFERENCES.person_id,
1197 NEW_REFERENCES.create_dt
1198 ) THEN
1199 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
1200 IGS_GE_MSG_STACK.ADD;
1201 App_Exception.Raise_Exception;
1202 END IF;
1203 check_uniqueness;
1204 check_constraints;
1205 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1206 check_uniqueness;
1207 check_constraints;
1208 Check_UK_Child_Existance;
1209 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1210 check_child_existance;
1211 END IF;
1212
1213 END Before_DML;
1214
1215 PROCEDURE After_DML (
1216 p_action IN VARCHAR2,
1217 x_rowid IN VARCHAR2
1218 ) AS
1219 BEGIN
1220
1221 l_rowid := x_rowid;
1222
1223 IF (p_action = 'INSERT') THEN
1224 -- Call all the procedures related to After Insert.
1225 AfterRowInsertUpdate2 ( p_inserting => TRUE ,
1226 p_updating => FALSE ,
1227 p_deleting=>FALSE);
1228 ELSIF (p_action = 'UPDATE') THEN
1229 -- Call all the procedures related to After Update.
1230 AfterRowInsertUpdate2 ( p_updating => TRUE ,
1231 p_inserting => FALSE ,
1232 p_deleting =>FALSE );
1233 AfterRowUpdate3 ( p_updating => TRUE,
1234 p_inserting =>FALSE,
1235 p_deleting =>FALSE );
1236 END IF;
1237
1238 l_rowid := NULL;
1239
1240 END After_DML;
1241
1242 procedure INSERT_ROW (
1243 X_ROWID in out NOCOPY VARCHAR2,
1244 X_PERSON_ID in NUMBER,
1245 X_CREATE_DT in out NOCOPY DATE,
1246 X_GRD_CAL_TYPE in VARCHAR2,
1247 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1248 X_COURSE_CD in VARCHAR2,
1249 X_AWARD_COURSE_CD in VARCHAR2,
1250 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1251 X_AWARD_CD in VARCHAR2,
1252 X_HONOURS_LEVEL in VARCHAR2 DEFAULT NULL,
1253 X_CONFERRAL_DT in DATE DEFAULT NULL,
1254 X_GRADUAND_STATUS in VARCHAR2,
1255 X_GRADUAND_APPR_STATUS in VARCHAR2,
1256 X_S_GRADUAND_TYPE in VARCHAR2,
1257 X_GRADUATION_NAME in VARCHAR2,
1258 X_PROXY_AWARD_IND in VARCHAR2,
1259 X_PROXY_AWARD_PERSON_ID in NUMBER,
1260 X_PREVIOUS_QUALIFICATIONS in VARCHAR2,
1261 X_CONVOCATION_MEMBERSHIP_IND in VARCHAR2,
1262 X_SUR_FOR_COURSE_CD in VARCHAR2,
1263 X_SUR_FOR_CRS_VERSION_NUMBER in NUMBER,
1264 X_SUR_FOR_AWARD_CD in VARCHAR2,
1265 X_COMMENTS in VARCHAR2,
1266 X_MODE in VARCHAR2 ,
1267 X_ORG_ID in NUMBER,
1268 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1269 X_ATTRIBUTE1 IN VARCHAR2 ,
1270 X_ATTRIBUTE2 IN VARCHAR2 ,
1271 X_ATTRIBUTE3 IN VARCHAR2 ,
1272 X_ATTRIBUTE4 IN VARCHAR2 ,
1273 X_ATTRIBUTE5 IN VARCHAR2 ,
1274 X_ATTRIBUTE6 IN VARCHAR2 ,
1275 X_ATTRIBUTE7 IN VARCHAR2 ,
1276 X_ATTRIBUTE8 IN VARCHAR2 ,
1277 X_ATTRIBUTE9 IN VARCHAR2 ,
1278 X_ATTRIBUTE10 IN VARCHAR2 ,
1279 X_ATTRIBUTE11 IN VARCHAR2 ,
1280 X_ATTRIBUTE12 IN VARCHAR2 ,
1281 X_ATTRIBUTE13 IN VARCHAR2 ,
1282 X_ATTRIBUTE14 IN VARCHAR2 ,
1283 X_ATTRIBUTE15 IN VARCHAR2 ,
1284 X_ATTRIBUTE16 IN VARCHAR2 ,
1285 X_ATTRIBUTE17 IN VARCHAR2 ,
1286 X_ATTRIBUTE18 IN VARCHAR2 ,
1287 X_ATTRIBUTE19 IN VARCHAR2 ,
1288 X_ATTRIBUTE20 IN VARCHAR2
1289 ) AS
1290 cursor C is select ROWID from IGS_GR_GRADUAND_ALL
1291 where PERSON_ID = X_PERSON_ID
1292 and CREATE_DT = NEW_REFERENCES.CREATE_DT;
1293 X_LAST_UPDATE_DATE DATE;
1294 X_LAST_UPDATED_BY NUMBER;
1295 X_LAST_UPDATE_LOGIN NUMBER;
1296 X_REQUEST_ID NUMBER;
1297 X_PROGRAM_ID NUMBER;
1298 X_PROGRAM_APPLICATION_ID NUMBER;
1299 X_PROGRAM_UPDATE_DATE DATE;
1300 begin
1301 X_LAST_UPDATE_DATE := SYSDATE;
1302 if(X_MODE = 'I') then
1303 X_LAST_UPDATED_BY := 1;
1304 X_LAST_UPDATE_LOGIN := 0;
1305 elsif (X_MODE = 'R') then
1306 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1307 if X_LAST_UPDATED_BY is NULL then
1308 X_LAST_UPDATED_BY := -1;
1309 end if;
1310 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1311 if X_LAST_UPDATE_LOGIN is NULL then
1312 X_LAST_UPDATE_LOGIN := -1;
1313 end if;
1314 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1315 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1316 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1317 if (X_REQUEST_ID = -1) then
1318 X_REQUEST_ID := NULL;
1319 X_PROGRAM_ID := NULL;
1320 X_PROGRAM_APPLICATION_ID := NULL;
1321 X_PROGRAM_UPDATE_DATE := NULL;
1322 else
1323 X_PROGRAM_UPDATE_DATE := SYSDATE;
1324 end if;
1325 else
1326 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1327 IGS_GE_MSG_STACK.ADD;
1328 app_exception.raise_exception;
1329 end if;
1330
1331 Before_DML (
1332 p_action => 'INSERT',
1333 x_rowid => X_ROWID,
1334 x_person_id => X_PERSON_ID,
1335 x_create_dt => NVL(X_CREATE_DT, SYSDATE),
1336 x_grd_cal_type => X_GRD_CAL_TYPE,
1337 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
1338 x_course_cd => X_COURSE_CD,
1339 x_award_course_cd => X_AWARD_COURSE_CD,
1340 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
1341 x_award_cd => X_AWARD_CD,
1342 x_graduand_status => X_GRADUAND_STATUS,
1343 x_graduand_appr_status => X_GRADUAND_APPR_STATUS,
1344 x_s_graduand_type => NVL(X_S_GRADUAND_TYPE, 'UNKNOWN'),
1345 x_graduation_name => X_GRADUATION_NAME,
1346 x_proxy_award_ind => NVL(X_PROXY_AWARD_IND, 'N'),
1347 x_proxy_award_person_id => X_PROXY_AWARD_PERSON_ID,
1348 x_previous_qualifications => X_PREVIOUS_QUALIFICATIONS,
1349 x_convocation_membership_ind => NVL(X_CONVOCATION_MEMBERSHIP_IND, 'N'),
1350 x_sur_for_course_cd => X_SUR_FOR_COURSE_CD,
1351 x_sur_for_crs_version_number => X_SUR_FOR_CRS_VERSION_NUMBER,
1352 x_sur_for_award_cd => X_SUR_FOR_AWARD_CD,
1353 x_comments => X_COMMENTS,
1354 x_creation_date => X_LAST_UPDATE_DATE,
1355 x_created_by => X_LAST_UPDATED_BY,
1356 x_last_update_date => X_LAST_UPDATE_DATE,
1357 x_last_updated_by => X_LAST_UPDATED_BY,
1358 x_last_update_login => X_LAST_UPDATE_LOGIN,
1359 x_org_id => igs_ge_gen_003.get_org_id,
1360 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1361 x_attribute1=>X_ATTRIBUTE1,
1362 x_attribute2=>X_ATTRIBUTE2,
1363 x_attribute3=>X_ATTRIBUTE3,
1364 x_attribute4=>X_ATTRIBUTE4,
1365 x_attribute5=>X_ATTRIBUTE5,
1366 x_attribute6=>X_ATTRIBUTE6,
1367 x_attribute7=>X_ATTRIBUTE7,
1368 x_attribute8=>X_ATTRIBUTE8,
1369 x_attribute9=>X_ATTRIBUTE9,
1370 x_attribute10=>X_ATTRIBUTE10,
1371 x_attribute11=>X_ATTRIBUTE11,
1372 x_attribute12=>X_ATTRIBUTE12,
1373 x_attribute13=>X_ATTRIBUTE13,
1374 x_attribute14=>X_ATTRIBUTE14,
1375 x_attribute15=>X_ATTRIBUTE15,
1376 x_attribute16=>X_ATTRIBUTE16,
1377 x_attribute17=>X_ATTRIBUTE17,
1378 x_attribute18=>X_ATTRIBUTE18,
1379 x_attribute19=>X_ATTRIBUTE19,
1380 x_attribute20=>X_ATTRIBUTE20
1381 );
1382
1383 insert into IGS_GR_GRADUAND_ALL (
1384 PERSON_ID,
1385 CREATE_DT,
1386 GRD_CAL_TYPE,
1387 GRD_CI_SEQUENCE_NUMBER,
1388 COURSE_CD,
1389 AWARD_COURSE_CD,
1390 AWARD_CRS_VERSION_NUMBER,
1391 AWARD_CD,
1392 GRADUAND_STATUS,
1393 GRADUAND_APPR_STATUS,
1394 S_GRADUAND_TYPE,
1395 GRADUATION_NAME,
1396 PROXY_AWARD_IND,
1397 PROXY_AWARD_PERSON_ID,
1398 PREVIOUS_QUALIFICATIONS,
1399 CONVOCATION_MEMBERSHIP_IND,
1400 SUR_FOR_COURSE_CD,
1401 SUR_FOR_CRS_VERSION_NUMBER,
1402 SUR_FOR_AWARD_CD,
1403 COMMENTS,
1404 CREATION_DATE,
1405 CREATED_BY,
1406 LAST_UPDATE_DATE,
1407 LAST_UPDATED_BY,
1408 LAST_UPDATE_LOGIN,
1409 REQUEST_ID,
1410 PROGRAM_ID,
1411 PROGRAM_APPLICATION_ID,
1412 PROGRAM_UPDATE_DATE,
1413 ORG_ID,
1414 ATTRIBUTE_CATEGORY,
1415 ATTRIBUTE1,
1416 ATTRIBUTE2,
1417 ATTRIBUTE3,
1418 ATTRIBUTE4,
1419 ATTRIBUTE5,
1420 ATTRIBUTE6,
1421 ATTRIBUTE7,
1422 ATTRIBUTE8,
1423 ATTRIBUTE9,
1424 ATTRIBUTE10,
1425 ATTRIBUTE11,
1426 ATTRIBUTE12,
1427 ATTRIBUTE13,
1428 ATTRIBUTE14,
1429 ATTRIBUTE15,
1430 ATTRIBUTE16,
1431 ATTRIBUTE17,
1432 ATTRIBUTE18,
1433 ATTRIBUTE19,
1434 ATTRIBUTE20
1435 ) values (
1436 NEW_REFERENCES.PERSON_ID,
1437 NEW_REFERENCES.CREATE_DT,
1438 NEW_REFERENCES.GRD_CAL_TYPE,
1439 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
1440 NEW_REFERENCES.COURSE_CD,
1441 NEW_REFERENCES.AWARD_COURSE_CD,
1442 NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
1443 NEW_REFERENCES.AWARD_CD,
1444 NEW_REFERENCES.GRADUAND_STATUS,
1445 NEW_REFERENCES.GRADUAND_APPR_STATUS,
1446 NEW_REFERENCES.S_GRADUAND_TYPE,
1447 NEW_REFERENCES.GRADUATION_NAME,
1448 NEW_REFERENCES.PROXY_AWARD_IND,
1449 NEW_REFERENCES.PROXY_AWARD_PERSON_ID,
1450 NEW_REFERENCES.PREVIOUS_QUALIFICATIONS,
1451 NEW_REFERENCES.CONVOCATION_MEMBERSHIP_IND,
1452 NEW_REFERENCES.SUR_FOR_COURSE_CD,
1453 NEW_REFERENCES.SUR_FOR_CRS_VERSION_NUMBER,
1454 NEW_REFERENCES.SUR_FOR_AWARD_CD,
1455 NEW_REFERENCES.COMMENTS,
1456 X_LAST_UPDATE_DATE,
1457 X_LAST_UPDATED_BY,
1458 X_LAST_UPDATE_DATE,
1459 X_LAST_UPDATED_BY,
1460 X_LAST_UPDATE_LOGIN,
1461 X_REQUEST_ID,
1462 X_PROGRAM_ID,
1463 X_PROGRAM_APPLICATION_ID,
1464 X_PROGRAM_UPDATE_DATE,
1465 NEW_REFERENCES.ORG_ID,
1466 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1467 NEW_REFERENCES.ATTRIBUTE1,
1468 NEW_REFERENCES.ATTRIBUTE2,
1469 NEW_REFERENCES.ATTRIBUTE3,
1470 NEW_REFERENCES.ATTRIBUTE4,
1471 NEW_REFERENCES.ATTRIBUTE5,
1472 NEW_REFERENCES.ATTRIBUTE6,
1473 NEW_REFERENCES.ATTRIBUTE7,
1474 NEW_REFERENCES.ATTRIBUTE8,
1475 NEW_REFERENCES.ATTRIBUTE9,
1476 NEW_REFERENCES.ATTRIBUTE10,
1477 NEW_REFERENCES.ATTRIBUTE11,
1478 NEW_REFERENCES.ATTRIBUTE12,
1479 NEW_REFERENCES.ATTRIBUTE13,
1480 NEW_REFERENCES.ATTRIBUTE14,
1481 NEW_REFERENCES.ATTRIBUTE15,
1482 NEW_REFERENCES.ATTRIBUTE16,
1483 NEW_REFERENCES.ATTRIBUTE17,
1484 NEW_REFERENCES.ATTRIBUTE18,
1485 NEW_REFERENCES.ATTRIBUTE19,
1486 NEW_REFERENCES.ATTRIBUTE20
1487 );
1488
1489 open c;
1490 fetch c into X_ROWID;
1491 if (c%notfound) then
1492 close c;
1493 raise no_data_found;
1494 end if;
1495 close c;
1496
1497 After_DML (
1498 p_action => 'INSERT',
1499 x_rowid => X_ROWID
1500 );
1501
1502 end INSERT_ROW;
1503
1504 procedure LOCK_ROW (
1505 X_ROWID in VARCHAR2,
1506 X_PERSON_ID in NUMBER,
1507 X_CREATE_DT in DATE,
1508 X_GRD_CAL_TYPE in VARCHAR2,
1509 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1510 X_COURSE_CD in VARCHAR2,
1511 X_AWARD_COURSE_CD in VARCHAR2,
1512 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1513 X_AWARD_CD in VARCHAR2,
1514 X_HONOURS_LEVEL in VARCHAR2 DEFAULT NULL,
1515 X_CONFERRAL_DT in DATE DEFAULT NULL,
1516 X_GRADUAND_STATUS in VARCHAR2,
1517 X_GRADUAND_APPR_STATUS in VARCHAR2,
1518 X_S_GRADUAND_TYPE in VARCHAR2,
1519 X_GRADUATION_NAME in VARCHAR2,
1520 X_PROXY_AWARD_IND in VARCHAR2,
1521 X_PROXY_AWARD_PERSON_ID in NUMBER,
1522 X_PREVIOUS_QUALIFICATIONS in VARCHAR2,
1523 X_CONVOCATION_MEMBERSHIP_IND in VARCHAR2,
1524 X_SUR_FOR_COURSE_CD in VARCHAR2,
1525 X_SUR_FOR_CRS_VERSION_NUMBER in NUMBER,
1526 X_SUR_FOR_AWARD_CD in VARCHAR2,
1527 X_COMMENTS in VARCHAR2,
1528 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1529 X_ATTRIBUTE1 IN VARCHAR2 ,
1530 X_ATTRIBUTE2 IN VARCHAR2 ,
1531 X_ATTRIBUTE3 IN VARCHAR2 ,
1532 X_ATTRIBUTE4 IN VARCHAR2 ,
1533 X_ATTRIBUTE5 IN VARCHAR2 ,
1534 X_ATTRIBUTE6 IN VARCHAR2 ,
1535 X_ATTRIBUTE7 IN VARCHAR2 ,
1536 X_ATTRIBUTE8 IN VARCHAR2 ,
1537 X_ATTRIBUTE9 IN VARCHAR2 ,
1538 X_ATTRIBUTE10 IN VARCHAR2 ,
1539 X_ATTRIBUTE11 IN VARCHAR2 ,
1540 X_ATTRIBUTE12 IN VARCHAR2 ,
1541 X_ATTRIBUTE13 IN VARCHAR2 ,
1542 X_ATTRIBUTE14 IN VARCHAR2 ,
1543 X_ATTRIBUTE15 IN VARCHAR2 ,
1544 X_ATTRIBUTE16 IN VARCHAR2 ,
1545 X_ATTRIBUTE17 IN VARCHAR2 ,
1546 X_ATTRIBUTE18 IN VARCHAR2 ,
1547 X_ATTRIBUTE19 IN VARCHAR2 ,
1548 X_ATTRIBUTE20 IN VARCHAR2
1549 ) AS
1550 cursor c1 is select
1551 GRD_CAL_TYPE,
1552 GRD_CI_SEQUENCE_NUMBER,
1553 COURSE_CD,
1554 AWARD_COURSE_CD,
1555 AWARD_CRS_VERSION_NUMBER,
1556 AWARD_CD,
1557 GRADUAND_STATUS,
1558 GRADUAND_APPR_STATUS,
1559 S_GRADUAND_TYPE,
1560 GRADUATION_NAME,
1561 PROXY_AWARD_IND,
1562 PROXY_AWARD_PERSON_ID,
1563 PREVIOUS_QUALIFICATIONS,
1564 CONVOCATION_MEMBERSHIP_IND,
1565 SUR_FOR_COURSE_CD,
1566 SUR_FOR_CRS_VERSION_NUMBER,
1567 SUR_FOR_AWARD_CD,
1568 COMMENTS,
1569 ATTRIBUTE_CATEGORY,
1570 ATTRIBUTE1,
1571 ATTRIBUTE2,
1572 ATTRIBUTE3,
1573 ATTRIBUTE4,
1574 ATTRIBUTE5,
1575 ATTRIBUTE6,
1576 ATTRIBUTE7,
1577 ATTRIBUTE8,
1578 ATTRIBUTE9,
1579 ATTRIBUTE10,
1580 ATTRIBUTE11,
1581 ATTRIBUTE12,
1582 ATTRIBUTE13,
1583 ATTRIBUTE14,
1584 ATTRIBUTE15,
1585 ATTRIBUTE16,
1586 ATTRIBUTE17,
1587 ATTRIBUTE18,
1588 ATTRIBUTE19,
1589 ATTRIBUTE20
1590 from IGS_GR_GRADUAND_ALL
1591 where ROWID = X_ROWID for update nowait;
1592 tlinfo c1%rowtype;
1593
1594 begin
1595 open c1;
1596 fetch c1 into tlinfo;
1597 if (c1%notfound) then
1598 close c1;
1599 fnd_message.set_name('FND', 'FORM_RECORD_DELETED11');
1600 app_exception.raise_exception;
1601 return;
1602 end if;
1603 close c1;
1604
1605 if ( (tlinfo.GRD_CAL_TYPE = X_GRD_CAL_TYPE)
1606 AND (tlinfo.GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER)
1607 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
1608 OR ((tlinfo.COURSE_CD is null)
1609 AND (X_COURSE_CD is null)))
1610 AND ((tlinfo.AWARD_COURSE_CD = X_AWARD_COURSE_CD)
1611 OR ((tlinfo.AWARD_COURSE_CD is null)
1612 AND (X_AWARD_COURSE_CD is null)))
1613 AND ((tlinfo.AWARD_CRS_VERSION_NUMBER = X_AWARD_CRS_VERSION_NUMBER)
1614 OR ((tlinfo.AWARD_CRS_VERSION_NUMBER is null)
1615 AND (X_AWARD_CRS_VERSION_NUMBER is null)))
1616 AND (tlinfo.AWARD_CD = X_AWARD_CD)
1617 AND (tlinfo.GRADUAND_STATUS = X_GRADUAND_STATUS)
1618 AND (tlinfo.GRADUAND_APPR_STATUS = X_GRADUAND_APPR_STATUS)
1619 AND (tlinfo.S_GRADUAND_TYPE = X_S_GRADUAND_TYPE)
1620 AND (tlinfo.GRADUATION_NAME = X_GRADUATION_NAME)
1621 AND (tlinfo.PROXY_AWARD_IND = X_PROXY_AWARD_IND)
1622 AND ((tlinfo.PROXY_AWARD_PERSON_ID = X_PROXY_AWARD_PERSON_ID)
1623 OR ((tlinfo.PROXY_AWARD_PERSON_ID is null)
1624 AND (X_PROXY_AWARD_PERSON_ID is null)))
1625 AND ((tlinfo.PREVIOUS_QUALIFICATIONS = X_PREVIOUS_QUALIFICATIONS)
1626 OR ((tlinfo.PREVIOUS_QUALIFICATIONS is null)
1627 AND (X_PREVIOUS_QUALIFICATIONS is null)))
1628 AND (tlinfo.CONVOCATION_MEMBERSHIP_IND = X_CONVOCATION_MEMBERSHIP_IND)
1629 AND ((tlinfo.SUR_FOR_COURSE_CD = X_SUR_FOR_COURSE_CD)
1630 OR ((tlinfo.SUR_FOR_COURSE_CD is null)
1631 AND (X_SUR_FOR_COURSE_CD is null)))
1632 AND ((tlinfo.SUR_FOR_CRS_VERSION_NUMBER = X_SUR_FOR_CRS_VERSION_NUMBER)
1633 OR ((tlinfo.SUR_FOR_CRS_VERSION_NUMBER is null)
1634 AND (X_SUR_FOR_CRS_VERSION_NUMBER is null)))
1635 AND ((tlinfo.SUR_FOR_AWARD_CD = X_SUR_FOR_AWARD_CD)
1636 OR ((tlinfo.SUR_FOR_AWARD_CD is null)
1637 AND (X_SUR_FOR_AWARD_CD is null)))
1638 AND ((tlinfo.COMMENTS = X_COMMENTS)
1639 OR ((tlinfo.COMMENTS is null)
1640 AND (X_COMMENTS is null)))
1641 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1642 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
1643 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
1644 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1645 OR ((tlinfo.ATTRIBUTE1 IS NULL)
1646 AND (X_ATTRIBUTE1 IS NULL)))
1647 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1648 OR ((tlinfo.ATTRIBUTE2 IS NULL)
1649 AND (X_ATTRIBUTE2 IS NULL)))
1650 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1651 OR ((tlinfo.ATTRIBUTE3 IS NULL)
1652 AND (X_ATTRIBUTE3 IS NULL)))
1653 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1654 OR ((tlinfo.ATTRIBUTE4 IS NULL)
1655 AND (X_ATTRIBUTE4 IS NULL)))
1656 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1657 OR ((tlinfo.ATTRIBUTE5 IS NULL)
1658 AND (X_ATTRIBUTE5 IS NULL)))
1659 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1660 OR ((tlinfo.ATTRIBUTE6 IS NULL)
1661 AND (X_ATTRIBUTE6 IS NULL)))
1662 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1663 OR ((tlinfo.ATTRIBUTE7 IS NULL)
1664 AND (X_ATTRIBUTE7 IS NULL)))
1665 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1666 OR ((tlinfo.ATTRIBUTE8 IS NULL)
1667 AND (X_ATTRIBUTE8 IS NULL)))
1668 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1669 OR ((tlinfo.ATTRIBUTE9 IS NULL)
1670 AND (X_ATTRIBUTE9 IS NULL)))
1671 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1672 OR ((tlinfo.ATTRIBUTE10 IS NULL)
1673 AND (X_ATTRIBUTE10 IS NULL)))
1674 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1675 OR ((tlinfo.ATTRIBUTE11 IS NULL)
1676 AND (X_ATTRIBUTE11 IS NULL)))
1677 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1678 OR ((tlinfo.ATTRIBUTE12 IS NULL)
1679 AND (X_ATTRIBUTE12 IS NULL)))
1680 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1681 OR ((tlinfo.ATTRIBUTE13 IS NULL)
1682 AND (X_ATTRIBUTE13 IS NULL)))
1683 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1684 OR ((tlinfo.ATTRIBUTE14 IS NULL)
1685 AND (X_ATTRIBUTE14 IS NULL)))
1686 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1687 OR ((tlinfo.ATTRIBUTE15 IS NULL)
1688 AND (X_ATTRIBUTE15 IS NULL)))
1689 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1690 OR ((tlinfo.ATTRIBUTE16 IS NULL)
1691 AND (X_ATTRIBUTE16 IS NULL)))
1692 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1693 OR ((tlinfo.ATTRIBUTE17 IS NULL)
1694 AND (X_ATTRIBUTE17 IS NULL)))
1695 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1696 OR ((tlinfo.ATTRIBUTE18 IS NULL)
1697 AND (X_ATTRIBUTE18 IS NULL)))
1698 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1699 OR ((tlinfo.ATTRIBUTE19 IS NULL)
1700 AND (X_ATTRIBUTE19 IS NULL)))
1701 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1702 OR ((tlinfo.ATTRIBUTE20 IS NULL)
1703 AND (X_ATTRIBUTE20 IS NULL)))
1704 ) then
1705 null;
1706 else
1707 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1708 app_exception.raise_exception;
1709 end if;
1710 return;
1711 end LOCK_ROW;
1712
1713 procedure UPDATE_ROW (
1714 X_ROWID in VARCHAR2,
1715 X_PERSON_ID in NUMBER,
1716 X_CREATE_DT in DATE,
1717 X_GRD_CAL_TYPE in VARCHAR2,
1718 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1719 X_COURSE_CD in VARCHAR2,
1720 X_AWARD_COURSE_CD in VARCHAR2,
1721 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1722 X_AWARD_CD in VARCHAR2,
1723 X_HONOURS_LEVEL in VARCHAR2 DEFAULT NULL,
1724 X_CONFERRAL_DT in DATE DEFAULT NULL,
1725 X_GRADUAND_STATUS in VARCHAR2,
1726 X_GRADUAND_APPR_STATUS in VARCHAR2,
1727 X_S_GRADUAND_TYPE in VARCHAR2,
1728 X_GRADUATION_NAME in VARCHAR2,
1729 X_PROXY_AWARD_IND in VARCHAR2,
1730 X_PROXY_AWARD_PERSON_ID in NUMBER,
1731 X_PREVIOUS_QUALIFICATIONS in VARCHAR2,
1732 X_CONVOCATION_MEMBERSHIP_IND in VARCHAR2,
1733 X_SUR_FOR_COURSE_CD in VARCHAR2,
1734 X_SUR_FOR_CRS_VERSION_NUMBER in NUMBER,
1735 X_SUR_FOR_AWARD_CD in VARCHAR2,
1736 X_COMMENTS in VARCHAR2,
1737 X_MODE in VARCHAR2 ,
1738 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1739 X_ATTRIBUTE1 IN VARCHAR2 ,
1740 X_ATTRIBUTE2 IN VARCHAR2 ,
1741 X_ATTRIBUTE3 IN VARCHAR2 ,
1742 X_ATTRIBUTE4 IN VARCHAR2 ,
1743 X_ATTRIBUTE5 IN VARCHAR2 ,
1744 X_ATTRIBUTE6 IN VARCHAR2 ,
1745 X_ATTRIBUTE7 IN VARCHAR2 ,
1746 X_ATTRIBUTE8 IN VARCHAR2 ,
1747 X_ATTRIBUTE9 IN VARCHAR2 ,
1748 X_ATTRIBUTE10 IN VARCHAR2 ,
1749 X_ATTRIBUTE11 IN VARCHAR2 ,
1750 X_ATTRIBUTE12 IN VARCHAR2 ,
1751 X_ATTRIBUTE13 IN VARCHAR2 ,
1752 X_ATTRIBUTE14 IN VARCHAR2 ,
1753 X_ATTRIBUTE15 IN VARCHAR2 ,
1754 X_ATTRIBUTE16 IN VARCHAR2 ,
1755 X_ATTRIBUTE17 IN VARCHAR2 ,
1756 X_ATTRIBUTE18 IN VARCHAR2 ,
1757 X_ATTRIBUTE19 IN VARCHAR2 ,
1758 X_ATTRIBUTE20 IN VARCHAR2
1759 ) AS
1760 X_LAST_UPDATE_DATE DATE;
1761 X_LAST_UPDATED_BY NUMBER;
1762 X_LAST_UPDATE_LOGIN NUMBER;
1763 X_REQUEST_ID NUMBER;
1764 X_PROGRAM_ID NUMBER;
1765 X_PROGRAM_APPLICATION_ID NUMBER;
1766 X_PROGRAM_UPDATE_DATE DATE;
1767 begin
1768 X_LAST_UPDATE_DATE := SYSDATE;
1769 if(X_MODE = 'I') then
1770 X_LAST_UPDATED_BY := 1;
1771 X_LAST_UPDATE_LOGIN := 0;
1772 elsif (X_MODE = 'R') then
1773 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1774 if X_LAST_UPDATED_BY is NULL then
1775 X_LAST_UPDATED_BY := -1;
1776 end if;
1777 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1778 if X_LAST_UPDATE_LOGIN is NULL then
1779 X_LAST_UPDATE_LOGIN := -1;
1780 end if;
1781 else
1782 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1783 IGS_GE_MSG_STACK.ADD;
1784 app_exception.raise_exception;
1785 end if;
1786
1787 Before_DML (
1788 p_action => 'UPDATE',
1789 x_rowid => X_ROWID,
1790 x_person_id => X_PERSON_ID,
1791 x_create_dt => X_CREATE_DT,
1792 x_grd_cal_type => X_GRD_CAL_TYPE,
1793 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
1794 x_course_cd => X_COURSE_CD,
1795 x_award_course_cd => X_AWARD_COURSE_CD,
1796 x_award_crs_version_number => X_AWARD_CRS_VERSION_NUMBER,
1797 x_award_cd => X_AWARD_CD,
1798 x_graduand_status => X_GRADUAND_STATUS,
1799 x_graduand_appr_status => X_GRADUAND_APPR_STATUS,
1800 x_s_graduand_type => X_S_GRADUAND_TYPE,
1801 x_graduation_name => X_GRADUATION_NAME,
1802 x_proxy_award_ind => X_PROXY_AWARD_IND,
1803 x_proxy_award_person_id => X_PROXY_AWARD_PERSON_ID,
1804 x_previous_qualifications => X_PREVIOUS_QUALIFICATIONS,
1805 x_convocation_membership_ind => X_CONVOCATION_MEMBERSHIP_IND,
1806 x_sur_for_course_cd => X_SUR_FOR_COURSE_CD,
1807 x_sur_for_crs_version_number => X_SUR_FOR_CRS_VERSION_NUMBER,
1808 x_sur_for_award_cd => X_SUR_FOR_AWARD_CD,
1809 x_comments => X_COMMENTS,
1810 x_creation_date => X_LAST_UPDATE_DATE,
1811 x_created_by => X_LAST_UPDATED_BY,
1812 x_last_update_date => X_LAST_UPDATE_DATE,
1813 x_last_updated_by => X_LAST_UPDATED_BY,
1814 x_last_update_login => X_LAST_UPDATE_LOGIN,
1815 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1816 x_attribute1=>X_ATTRIBUTE1,
1817 x_attribute2=>X_ATTRIBUTE2,
1818 x_attribute3=>X_ATTRIBUTE3,
1819 x_attribute4=>X_ATTRIBUTE4,
1820 x_attribute5=>X_ATTRIBUTE5,
1821 x_attribute6=>X_ATTRIBUTE6,
1822 x_attribute7=>X_ATTRIBUTE7,
1823 x_attribute8=>X_ATTRIBUTE8,
1824 x_attribute9=>X_ATTRIBUTE9,
1825 x_attribute10=>X_ATTRIBUTE10,
1826 x_attribute11=>X_ATTRIBUTE11,
1827 x_attribute12=>X_ATTRIBUTE12,
1828 x_attribute13=>X_ATTRIBUTE13,
1829 x_attribute14=>X_ATTRIBUTE14,
1830 x_attribute15=>X_ATTRIBUTE15,
1831 x_attribute16=>X_ATTRIBUTE16,
1832 x_attribute17=>X_ATTRIBUTE17,
1833 x_attribute18=>X_ATTRIBUTE18,
1834 x_attribute19=>X_ATTRIBUTE19,
1835 x_attribute20=>X_ATTRIBUTE20
1836 );
1837
1838 if (X_MODE = 'R') then
1839 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1840 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1841 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1842 if (X_REQUEST_ID = -1) then
1843 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1844 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1845 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1846 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1847 else
1848 X_PROGRAM_UPDATE_DATE := SYSDATE;
1849 end if;
1850 end if;
1851 update IGS_GR_GRADUAND_ALL set
1852 GRD_CAL_TYPE = NEW_REFERENCES.GRD_CAL_TYPE,
1853 GRD_CI_SEQUENCE_NUMBER = NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
1854 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1855 AWARD_COURSE_CD = NEW_REFERENCES.AWARD_COURSE_CD,
1856 AWARD_CRS_VERSION_NUMBER = NEW_REFERENCES.AWARD_CRS_VERSION_NUMBER,
1857 AWARD_CD = NEW_REFERENCES.AWARD_CD,
1858 GRADUAND_STATUS = NEW_REFERENCES.GRADUAND_STATUS,
1859 GRADUAND_APPR_STATUS = NEW_REFERENCES.GRADUAND_APPR_STATUS,
1860 S_GRADUAND_TYPE = NEW_REFERENCES.S_GRADUAND_TYPE,
1861 GRADUATION_NAME = NEW_REFERENCES.GRADUATION_NAME,
1862 PROXY_AWARD_IND = NEW_REFERENCES.PROXY_AWARD_IND,
1863 PROXY_AWARD_PERSON_ID = NEW_REFERENCES.PROXY_AWARD_PERSON_ID,
1864 PREVIOUS_QUALIFICATIONS = NEW_REFERENCES.PREVIOUS_QUALIFICATIONS,
1865 CONVOCATION_MEMBERSHIP_IND = NEW_REFERENCES.CONVOCATION_MEMBERSHIP_IND,
1866 SUR_FOR_COURSE_CD = NEW_REFERENCES.SUR_FOR_COURSE_CD,
1867 SUR_FOR_CRS_VERSION_NUMBER = NEW_REFERENCES.SUR_FOR_CRS_VERSION_NUMBER,
1868 SUR_FOR_AWARD_CD = NEW_REFERENCES.SUR_FOR_AWARD_CD,
1869 COMMENTS = NEW_REFERENCES.COMMENTS,
1870 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1871 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1872 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1873 REQUEST_ID = X_REQUEST_ID,
1874 PROGRAM_ID = X_PROGRAM_ID,
1875 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1876 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1877 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1878 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1879 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1880 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1881 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1882 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1883 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1884 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1885 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1886 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1887 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1888 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1889 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1890 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1891 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1892 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1893 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1894 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1895 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1896 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1897 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20
1898 where ROWID = X_ROWID;
1899 if (sql%notfound) then
1900 raise no_data_found;
1901 end if;
1902
1903 After_DML (
1904 p_action => 'UPDATE',
1905 x_rowid => X_ROWID
1906 );
1907
1908 end UPDATE_ROW;
1909
1910 procedure ADD_ROW (
1911 X_ROWID in out NOCOPY VARCHAR2,
1912 X_PERSON_ID in NUMBER,
1913 X_CREATE_DT in out NOCOPY DATE,
1914 X_GRD_CAL_TYPE in VARCHAR2,
1915 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
1916 X_COURSE_CD in VARCHAR2,
1917 X_AWARD_COURSE_CD in VARCHAR2,
1918 X_AWARD_CRS_VERSION_NUMBER in NUMBER,
1919 X_AWARD_CD in VARCHAR2,
1920 X_HONOURS_LEVEL in VARCHAR2 DEFAULT NULL,
1921 X_CONFERRAL_DT in DATE DEFAULT NULL,
1922 X_GRADUAND_STATUS in VARCHAR2,
1923 X_GRADUAND_APPR_STATUS in VARCHAR2,
1924 X_S_GRADUAND_TYPE in VARCHAR2,
1925 X_GRADUATION_NAME in VARCHAR2,
1926 X_PROXY_AWARD_IND in VARCHAR2,
1927 X_PROXY_AWARD_PERSON_ID in NUMBER,
1928 X_PREVIOUS_QUALIFICATIONS in VARCHAR2,
1929 X_CONVOCATION_MEMBERSHIP_IND in VARCHAR2,
1930 X_SUR_FOR_COURSE_CD in VARCHAR2,
1931 X_SUR_FOR_CRS_VERSION_NUMBER in NUMBER,
1932 X_SUR_FOR_AWARD_CD in VARCHAR2,
1933 X_COMMENTS in VARCHAR2,
1934 X_MODE in VARCHAR2 ,
1935 X_ORG_ID in NUMBER,
1936 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1937 X_ATTRIBUTE1 IN VARCHAR2 ,
1938 X_ATTRIBUTE2 IN VARCHAR2 ,
1939 X_ATTRIBUTE3 IN VARCHAR2 ,
1940 X_ATTRIBUTE4 IN VARCHAR2 ,
1941 X_ATTRIBUTE5 IN VARCHAR2 ,
1942 X_ATTRIBUTE6 IN VARCHAR2 ,
1943 X_ATTRIBUTE7 IN VARCHAR2 ,
1944 X_ATTRIBUTE8 IN VARCHAR2 ,
1945 X_ATTRIBUTE9 IN VARCHAR2 ,
1946 X_ATTRIBUTE10 IN VARCHAR2 ,
1947 X_ATTRIBUTE11 IN VARCHAR2 ,
1948 X_ATTRIBUTE12 IN VARCHAR2 ,
1949 X_ATTRIBUTE13 IN VARCHAR2 ,
1950 X_ATTRIBUTE14 IN VARCHAR2 ,
1951 X_ATTRIBUTE15 IN VARCHAR2 ,
1952 X_ATTRIBUTE16 IN VARCHAR2 ,
1953 X_ATTRIBUTE17 IN VARCHAR2 ,
1954 X_ATTRIBUTE18 IN VARCHAR2 ,
1955 X_ATTRIBUTE19 IN VARCHAR2 ,
1956 X_ATTRIBUTE20 IN VARCHAR2
1957 ) AS
1958 cursor c1 is select rowid from IGS_GR_GRADUAND_ALL
1959 where PERSON_ID = X_PERSON_ID
1960 and CREATE_DT = NVL(X_CREATE_DT, SYSDATE)
1961 ;
1962
1963 begin
1964 open c1;
1965 fetch c1 into X_ROWID;
1966 if (c1%notfound) then
1967 close c1;
1968 INSERT_ROW (
1969 X_ROWID,
1970 X_PERSON_ID,
1971 X_CREATE_DT,
1972 X_GRD_CAL_TYPE,
1973 X_GRD_CI_SEQUENCE_NUMBER,
1974 X_COURSE_CD,
1975 X_AWARD_COURSE_CD,
1976 X_AWARD_CRS_VERSION_NUMBER,
1977 X_AWARD_CD,
1978 null,
1979 null,
1980 X_GRADUAND_STATUS,
1981 X_GRADUAND_APPR_STATUS,
1982 X_S_GRADUAND_TYPE,
1983 X_GRADUATION_NAME,
1984 X_PROXY_AWARD_IND,
1985 X_PROXY_AWARD_PERSON_ID,
1986 X_PREVIOUS_QUALIFICATIONS,
1987 X_CONVOCATION_MEMBERSHIP_IND,
1988 X_SUR_FOR_COURSE_CD,
1989 X_SUR_FOR_CRS_VERSION_NUMBER,
1990 X_SUR_FOR_AWARD_CD,
1991 X_COMMENTS,
1992 X_MODE,
1993 x_org_id,
1994 X_ATTRIBUTE_CATEGORY,
1995 X_ATTRIBUTE1,
1996 X_ATTRIBUTE2,
1997 X_ATTRIBUTE3,
1998 X_ATTRIBUTE4,
1999 X_ATTRIBUTE5,
2000 X_ATTRIBUTE6,
2001 X_ATTRIBUTE7,
2002 X_ATTRIBUTE8,
2003 X_ATTRIBUTE9,
2004 X_ATTRIBUTE10,
2005 X_ATTRIBUTE11,
2006 X_ATTRIBUTE12,
2007 X_ATTRIBUTE13,
2008 X_ATTRIBUTE14,
2009 X_ATTRIBUTE15,
2010 X_ATTRIBUTE16,
2011 X_ATTRIBUTE17,
2012 X_ATTRIBUTE18,
2013 X_ATTRIBUTE19,
2014 X_ATTRIBUTE20
2015 );
2016 return;
2017 end if;
2018 close c1;
2019 UPDATE_ROW (
2020 X_ROWID,
2021 X_PERSON_ID,
2022 X_CREATE_DT,
2023 X_GRD_CAL_TYPE,
2024 X_GRD_CI_SEQUENCE_NUMBER,
2025 X_COURSE_CD,
2026 X_AWARD_COURSE_CD,
2027 X_AWARD_CRS_VERSION_NUMBER,
2028 X_AWARD_CD,
2029 null,
2030 null,
2031 X_GRADUAND_STATUS,
2032 X_GRADUAND_APPR_STATUS,
2033 X_S_GRADUAND_TYPE,
2034 X_GRADUATION_NAME,
2035 X_PROXY_AWARD_IND,
2036 X_PROXY_AWARD_PERSON_ID,
2037 X_PREVIOUS_QUALIFICATIONS,
2038 X_CONVOCATION_MEMBERSHIP_IND,
2039 X_SUR_FOR_COURSE_CD,
2040 X_SUR_FOR_CRS_VERSION_NUMBER,
2041 X_SUR_FOR_AWARD_CD,
2042 X_COMMENTS,
2043 X_MODE,
2044 X_ATTRIBUTE_CATEGORY,
2045 X_ATTRIBUTE1,
2046 X_ATTRIBUTE2,
2047 X_ATTRIBUTE3,
2048 X_ATTRIBUTE4,
2049 X_ATTRIBUTE5,
2050 X_ATTRIBUTE6,
2051 X_ATTRIBUTE7,
2052 X_ATTRIBUTE8,
2053 X_ATTRIBUTE9,
2054 X_ATTRIBUTE10,
2055 X_ATTRIBUTE11,
2056 X_ATTRIBUTE12,
2057 X_ATTRIBUTE13,
2058 X_ATTRIBUTE14,
2059 X_ATTRIBUTE15,
2060 X_ATTRIBUTE16,
2061 X_ATTRIBUTE17,
2062 X_ATTRIBUTE18,
2063 X_ATTRIBUTE19,
2064 X_ATTRIBUTE20
2065 );
2066 end ADD_ROW;
2067
2068 procedure DELETE_ROW (
2069 X_ROWID in VARCHAR2
2070 ) AS
2071 begin
2072
2073 Before_DML (
2074 p_action => 'DELETE',
2075 x_rowid => X_ROWID
2076 );
2077
2078 delete from IGS_GR_GRADUAND_ALL
2079 where ROWID = X_ROWID;
2080 if (sql%notfound) then
2081 raise no_data_found;
2082 end if;
2083
2084 end DELETE_ROW;
2085
2086 end IGS_GR_GRADUAND_PKG;