[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SPA_AWD_AIM_PKG
Source
1 PACKAGE BODY igs_en_spa_awd_aim_pkg AS
2 /* $Header: IGSEI59B.pls 120.4 2006/06/29 10:41:27 shimitta ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_spa_awd_aim%ROWTYPE;
6 new_references igs_en_spa_awd_aim%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_course_cd IN VARCHAR2,
13 x_award_cd IN VARCHAR2,
14 x_start_dt IN DATE ,
15 x_end_dt IN DATE ,
16 x_complete_ind IN VARCHAR2,
17 x_conferral_date IN DATE ,
18 x_creation_date IN DATE ,
19 x_created_by IN NUMBER ,
20 x_last_update_date IN DATE ,
21 x_last_updated_by IN NUMBER ,
22 x_last_update_login IN NUMBER ,
23 x_award_mark IN NUMBER,
24 x_award_grade IN VARCHAR2,
25 x_grading_schema_cd IN VARCHAR2,
26 x_gs_version_number IN NUMBER
27 ) AS
28 /*
29 || Created By : [email protected]
30 || Created On : 22-NOV-2001
31 || Purpose : Initialises the Old and New references for the columns of the table.
32 || Known limitations, enhancements or remarks :
33 || Change History :
34 || Who When What
35 || (reverse chronological order - newest change first)
36 */
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM igs_en_spa_awd_aim
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 OPEN cur_old_ref_values;
50 FETCH cur_old_ref_values INTO old_references;
51 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
52 CLOSE cur_old_ref_values;
53 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
54 igs_ge_msg_stack.add;
55 app_exception.raise_exception;
56 RETURN;
57 END IF;
58 CLOSE cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.person_id := x_person_id;
62 new_references.course_cd := x_course_cd;
63 new_references.award_cd := x_award_cd;
64 new_references.start_dt := TRUNC(x_start_dt); -- TRUNC added in the code by Nishikant - bug#2386592 - 24MAY2002.
65 new_references.end_dt := TRUNC(x_end_dt); -- TRUNC added in the code by Nishikant - bug#2386592 - 24MAY2002.
66 new_references.complete_ind := x_complete_ind;
67 new_references.conferral_date := TRUNC(x_conferral_date);
68
69 --ijeddy, Build 31229913
70 new_references.award_mark := x_award_mark;
71 new_references.award_grade := x_award_grade;
72 new_references.grading_schema_cd := x_grading_schema_cd;
73 new_references.gs_version_number := x_gs_version_number;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END set_column_values;
88
89 -- anilk, 01-Oct-2003, Program Completion Validation build
90 -- This local procedure inserts record into history table for spaa.
91 PROCEDURE ins_spaa_hist AS
92 l_rowid VARCHAR2(25);
93 BEGIN
94 IF ( NVL(new_references.start_dt, igs_ge_date.igsdate('1900/01/01')) <> NVL(old_references.start_dt, igs_ge_date.igsdate('1900/01/01')) OR
95 NVL(new_references.end_dt, igs_ge_date.igsdate('1900/01/01')) <> NVL(old_references.end_dt, igs_ge_date.igsdate('1900/01/01')) OR
96 NVL(new_references.complete_ind,'NULL') <> NVL(old_references.complete_ind, 'NULL') OR
97 NVL(new_references.conferral_date,igs_ge_date.igsdate('1900/01/01'))<> NVL(old_references.conferral_date, igs_ge_date.igsdate('1900/01/01'))OR
98 NVL(new_references.award_mark, 99999) <> NVL(old_references.award_mark, 99999) OR
99 NVL(new_references.award_grade, 'NULL') <> NVL(old_references.award_grade,'NULL') OR
100 NVL(new_references.grading_schema_cd, 'NULL') <> NVL(old_references.grading_schema_cd, 'NULL') OR
101 NVL(new_references.gs_version_number, 999) <> NVL(old_references.gs_version_number, 999) ) THEN
102 igs_en_spaa_hist_pkg.insert_row (
103 x_rowid => l_rowid,
104 x_person_id => old_references.person_id,
105 x_course_cd => old_references.course_cd,
106 x_award_cd => old_references.award_cd,
107 x_start_date => old_references.start_dt,
108 x_end_date => old_references.end_dt,
109 x_complete_flag => old_references.complete_ind,
110 x_conferral_date => old_references.conferral_date,
111 x_award_mark => old_references.award_mark,
112 x_award_grade => old_references.award_grade,
113 x_grading_schema_cd => old_references.grading_schema_cd,
114 x_gs_version_number => old_references.gs_version_number,
115 x_mode => 'R');
116 END IF;
117 END ins_spaa_hist;
118
119 -- anilk, 01-Oct-2003, Program Completion Validation build
120 -- This local procedure deletes records from history table for spaa.
121 PROCEDURE del_spaa_hist(p_rowid IN VARCHAR2) AS
122 l_rowid VARCHAR2(25);
123 CURSOR cur_spaah IS
124 SELECT spaah.rowid
125 FROM igs_en_spa_awd_aim spaa,
126 igs_en_spaa_hist spaah
127 WHERE spaa.rowid = p_rowid AND
128 spaa.person_id = spaah.person_id AND
129 spaa.course_cd = spaah.course_cd AND
130 spaa.award_cd = spaah.award_cd;
131 BEGIN
132 FOR cur_spaah_rec IN cur_spaah LOOP
133 igs_en_spaa_hist_pkg.delete_row(x_rowid => cur_spaah_rec.rowid);
134 END LOOP;
135 END del_spaa_hist;
136
137 -- anilk, 01-Oct-2003, Program Completion Validation build
138 PROCEDURE AfterRowInsertUpdate1(
139 p_inserting IN BOOLEAN DEFAULT FALSE,
140 p_updating IN BOOLEAN DEFAULT FALSE,
141 p_deleting IN BOOLEAN DEFAULT FALSE
142 ) AS
143 v_message_name VARCHAR2(30);
144 BEGIN
145 IF p_updating THEN
146 ins_spaa_hist;
147 END IF;
148 END AfterRowInsertUpdate1;
149
150 PROCEDURE AfterRowInsertUpdate( p_inserting IN BOOLEAN,
151 p_updating IN BOOLEAN,
152 p_deleting IN BOOLEAN,
153 p_rowid IN VARCHAR2 DEFAULT NULL
154 ) AS
155 /*
156 || Created By : shimitta
157 || Created On : 27-JUN-2006
158 || Purpose : Changing the person type depending on the conferral date as per bug# 2691653.
159 || Change History :
160 || Who When What
161 */
162
163 -- Cursor to fetch active Person Type Instance Record
164 CURSOR cur_typ_id_inst(p_PERSON_ID NUMBER,p_COURSE_CD VARCHAR2,p_PERSON_TYPE_CODE VARCHAR2) IS
165 SELECT pti.*
166 FROM igs_pe_typ_instances_all pti
167 WHERE pti.PERSON_ID = p_PERSON_ID AND
168 pti.COURSE_CD = p_COURSE_CD AND
169 pti.PERSON_TYPE_CODE = p_PERSON_TYPE_CODE AND
170 pti.END_DATE IS NULL;
171
172 CURSOR cur_pers_type(p_system_type varchar2) IS
173 SELECT PERSON_TYPE_CODE
174 FROM igs_pe_person_types
175 WHERE SYSTEM_TYPE = p_system_type AND
176 CLOSED_IND = 'N';
177
178 -- Cursor used to fetch the Person Type Instance record which is being opened
179 -- irrespective of the system person type is closed or not.
180
181 CURSOR cur_pe_typ_inst( p_person_id igs_pe_typ_instances.PERSON_ID%TYPE,
182 p_course_cd igs_pe_typ_instances.course_cd%TYPE,
183 p_system_type igs_pe_person_types.SYSTEM_TYPE%TYPE
184 ) IS
185 SELECT pti.rowid row_id ,pti.*
186 FROM igs_pe_typ_instances_all pti,
187 igs_pe_person_types pty
188 WHERE pti.person_id = p_person_id AND
189 pti.course_cd = p_course_cd AND
190 pti.end_date IS NULL AND
191 pty.person_type_code = pti.person_type_code AND
192 pty.system_type = p_system_type;
193
194 --Cursor to fecth Person Type Instance record with end date not null
195 CURSOR cur_per_typ_dt( p_person_id igs_pe_typ_instances.PERSON_ID%TYPE,
196 p_course_cd igs_pe_typ_instances.course_cd%TYPE,
197 p_system_type igs_pe_person_types.SYSTEM_TYPE%TYPE,
198 p_date DATE ) IS
199 SELECT pti.rowid row_id ,pti.*
200 FROM igs_pe_typ_instances_all pti,
201 igs_pe_person_types pty
202 WHERE pti.person_id = p_person_id AND
203 pti.course_cd = p_course_cd AND
204 pti.end_date = p_date AND
205 pty.person_type_code = pti.person_type_code AND
206 pty.system_type = p_system_type;
207
208
209
210 CURSOR cur_conf_dt (p_rowid VARCHAR2) IS
211 SELECT *
212 FROM IGS_EN_SPA_AWD_AIM
213 WHERE ROWID = p_rowid ;
214
215 cur_conf_dt_rec cur_conf_dt%ROWTYPE;
216 cur_pe_typ_inst_rec cur_pe_typ_inst%ROWTYPE;
217 cur_typ_id_inst_rec cur_typ_id_inst%ROWTYPE;
218 cur_per_typ_dt_rec cur_per_typ_dt%ROWTYPE;
219 l_person_type igs_pe_person_types.PERSON_TYPE_CODE%TYPE;
220 l_method igs_pe_typ_instances.CREATE_METHOD%TYPE;
221 l_TYPE_INSTANCE_ID igs_pe_typ_instances.TYPE_INSTANCE_ID%TYPE;
222 l_rowid VARCHAR2(25);
223 l_date DATE;
224
225
226
227 BEGIN
228 IF (new_references.conferral_date IS NOT NULL AND p_updating) THEN
229 l_person_type := NULL;
230 l_date := SYSDATE;
231 l_method := 'PERSON_DEG_CONFER_PRG';
232
233 -- Select Person type Code for the System type GRADUATE
234 OPEN cur_pers_type('GRADUATE');
235 FETCH cur_pers_type INTO l_person_type;
236 CLOSE cur_pers_type;
237 IF l_person_type IS NULL THEN
238 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242
243 OPEN cur_typ_id_inst(new_references.PERSON_ID,new_references.COURSE_CD,l_person_type);
244 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
245 IF cur_typ_id_inst%NOTFOUND THEN
246 igs_pe_typ_instances_pkg.insert_row(
247 X_ROWID => l_ROWID,
248 X_PERSON_ID => new_references.PERSON_ID,
249 X_COURSE_CD => new_references.COURSE_CD,
250 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
251 X_PERSON_TYPE_CODE => l_person_type,
252 X_CC_VERSION_NUMBER => NULL,
253 X_FUNNEL_STATUS => NULL,
254 X_ADMISSION_APPL_NUMBER => NULL,
255 X_NOMINATED_COURSE_CD => NULL,
256 X_NCC_VERSION_NUMBER => NULL,
257 X_SEQUENCE_NUMBER => NULL,
258 X_START_DATE => new_references.conferral_date,
259 X_END_DATE => NULL,
260 X_CREATE_METHOD => l_method,
261 X_ENDED_BY => NULL,
262 X_END_METHOD => NULL,
263 X_MODE => 'R',
264 X_ORG_ID => NULL,
265 X_EMPLMNT_CATEGORY_CODE => NULL
266 );
267 END IF;
268 CLOSE cur_typ_id_inst;
269
270 OPEN cur_pe_typ_inst( new_references.person_id,
271 new_references.COURSE_CD,
272 'GRADUATE');
273 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
274 IF cur_pe_typ_inst%FOUND THEN
275 l_date := cur_pe_typ_inst_rec.START_DATE;
276 igs_pe_typ_instances_pkg.update_row(
277 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
278 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
279 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
280 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
281 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
282 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
283 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
284 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
285 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
286 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
287 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
288 X_START_DATE => new_references.conferral_date,
289 X_END_DATE => NULL,
290 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
291 X_ENDED_BY => NULL,
292 X_END_METHOD => NULL,
293 X_MODE => 'R' ,
294 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
295 END IF;
296 CLOSE cur_pe_typ_inst;
297
298 l_person_type := NULL;
299 -- Select Person type Code for the System type FORMER_STUDENT
300 OPEN cur_pers_type('FORMER_STUDENT');
301 FETCH cur_pers_type INTO l_person_type;
302 CLOSE cur_pers_type;
303 IF l_person_type IS NULL THEN
304 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 END IF;
308
309 -- Check any active record found for this student program, with System Person Type,FORMER_STUDENT
310 OPEN cur_per_typ_dt( new_references.person_id,
311 new_references.COURSE_CD,
312 'FORMER_STUDENT',
313 l_date);
314 FETCH cur_per_typ_dt INTO cur_per_typ_dt_rec;
315 IF cur_per_typ_dt%FOUND THEN
316 igs_pe_typ_instances_pkg.update_row(
317 X_ROWID => cur_per_typ_dt_rec.ROW_ID,
318 X_PERSON_ID => cur_per_typ_dt_rec.PERSON_ID,
319 X_COURSE_CD => cur_per_typ_dt_rec.COURSE_CD,
320 X_TYPE_INSTANCE_ID => cur_per_typ_dt_rec.TYPE_INSTANCE_ID,
321 X_PERSON_TYPE_CODE => cur_per_typ_dt_rec.PERSON_TYPE_CODE,
322 X_CC_VERSION_NUMBER => cur_per_typ_dt_rec.CC_VERSION_NUMBER,
323 X_FUNNEL_STATUS => cur_per_typ_dt_rec.FUNNEL_STATUS,
324 X_ADMISSION_APPL_NUMBER => cur_per_typ_dt_rec.ADMISSION_APPL_NUMBER,
325 X_NOMINATED_COURSE_CD => cur_per_typ_dt_rec.NOMINATED_COURSE_CD,
326 X_NCC_VERSION_NUMBER => cur_per_typ_dt_rec.NCC_VERSION_NUMBER,
327 X_SEQUENCE_NUMBER => cur_per_typ_dt_rec.SEQUENCE_NUMBER,
328 X_START_DATE => cur_per_typ_dt_rec.START_DATE,
329 X_END_DATE => new_references.conferral_date,
330 X_CREATE_METHOD => cur_per_typ_dt_rec.CREATE_METHOD,
331 X_ENDED_BY => cur_per_typ_dt_rec.ENDED_BY,
332 X_END_METHOD => l_method,
333 X_MODE => 'R' ,
334 X_EMPLMNT_CATEGORY_CODE => cur_per_typ_dt_rec.emplmnt_category_code);
335 END IF;
336 CLOSE cur_per_typ_dt;
337
338 OPEN cur_pe_typ_inst( new_references.person_id,
339 new_references.COURSE_CD,
340 'FORMER_STUDENT');
341 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
342 IF cur_pe_typ_inst%FOUND THEN
343 igs_pe_typ_instances_pkg.update_row(
344 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
345 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
346 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
347 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
348 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
349 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
350 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
351 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
352 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
353 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
354 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
355 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
356 X_END_DATE => new_references.conferral_date,
357 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
358 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
359 X_END_METHOD => l_method,
360 X_MODE => 'R' ,
361 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
362 END IF;
363 CLOSE cur_pe_typ_inst;
364
365 ELSIF (new_references.conferral_date IS NULL AND p_updating) THEN
366 l_date := NULL;
367 l_person_type := NULL;
368 l_method := 'PERSON_NO_ENROLL_PRG';
369
370 -- Select Person type Code for the System type GRADUATE
371 OPEN cur_pers_type('GRADUATE');
372 FETCH cur_pers_type INTO l_person_type;
373 CLOSE cur_pers_type;
374 IF l_person_type IS NULL THEN
375 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
376 IGS_GE_MSG_STACK.ADD;
377 App_Exception.Raise_Exception;
378 END IF;
379
380 -- Check any active record found for this student program, with System Person Type,GRADUATE
381 OPEN cur_pe_typ_inst( new_references.person_id,
382 new_references.COURSE_CD,
383 'GRADUATE');
384 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
385 IF cur_pe_typ_inst%FOUND THEN
386
387 IF SYSDATE < cur_pe_typ_inst_rec.START_DATE THEN
388 l_date := cur_pe_typ_inst_rec.START_DATE;
389 ELSE l_date := SYSDATE;
390 END IF;
391 igs_pe_typ_instances_pkg.update_row(
392 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
393 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
394 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
395 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
396 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
397 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
398 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
399 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
400 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
401 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
402 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
403 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
404 X_END_DATE => l_date, --- what should be the end date
405 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
406 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
407 X_END_METHOD => l_method,
408 X_MODE => 'R' ,
409 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
410 END IF;
411 CLOSE cur_pe_typ_inst;
412
413 l_person_type := NULL;
414 -- Select Person type Code for the System type FORMER_STUDENT
415 OPEN cur_pers_type('FORMER_STUDENT');
416 FETCH cur_pers_type INTO l_person_type;
417 CLOSE cur_pers_type;
418 IF l_person_type IS NULL THEN
419 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
420 IGS_GE_MSG_STACK.ADD;
421 App_Exception.Raise_Exception;
422 END IF;
423
424 IF l_date = SYSDATE THEN
425 OPEN cur_typ_id_inst(new_references.PERSON_ID,new_references.COURSE_CD,l_person_type);
426 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
427 IF cur_typ_id_inst%NOTFOUND THEN
428 igs_pe_typ_instances_pkg.insert_row(
429 X_ROWID => l_ROWID,
430 X_PERSON_ID => new_references.PERSON_ID,
431 X_COURSE_CD => new_references.COURSE_CD,
432 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
433 X_PERSON_TYPE_CODE => l_person_type,
434 X_CC_VERSION_NUMBER => NULL,
435 X_FUNNEL_STATUS => NULL,
436 X_ADMISSION_APPL_NUMBER => NULL,
437 X_NOMINATED_COURSE_CD => NULL,
438 X_NCC_VERSION_NUMBER => NULL,
439 X_SEQUENCE_NUMBER => NULL,
440 X_START_DATE => l_date, -- what should be the start date
441 X_END_DATE => NULL,
442 X_CREATE_METHOD => l_method,
443 X_ENDED_BY => NULL,
444 X_END_METHOD => NULL,
445 X_MODE => 'R',
446 X_ORG_ID => NULL,
447 X_EMPLMNT_CATEGORY_CODE => NULL
448 );
449 END IF;
450 CLOSE cur_typ_id_inst;
451 ELSE
452 OPEN cur_per_typ_dt( new_references.person_id,
453 new_references.COURSE_CD,
454 'FORMER_STUDENT',
455 l_date);
456 FETCH cur_per_typ_dt INTO cur_per_typ_dt_rec;
457 IF cur_per_typ_dt%FOUND THEN
458 igs_pe_typ_instances_pkg.update_row(
459 X_ROWID => cur_per_typ_dt_rec.ROW_ID,
460 X_PERSON_ID => cur_per_typ_dt_rec.PERSON_ID,
461 X_COURSE_CD => cur_per_typ_dt_rec.COURSE_CD,
462 X_TYPE_INSTANCE_ID => cur_per_typ_dt_rec.TYPE_INSTANCE_ID,
463 X_PERSON_TYPE_CODE => cur_per_typ_dt_rec.PERSON_TYPE_CODE,
464 X_CC_VERSION_NUMBER => cur_per_typ_dt_rec.CC_VERSION_NUMBER,
465 X_FUNNEL_STATUS => cur_per_typ_dt_rec.FUNNEL_STATUS,
466 X_ADMISSION_APPL_NUMBER => cur_per_typ_dt_rec.ADMISSION_APPL_NUMBER,
467 X_NOMINATED_COURSE_CD => cur_per_typ_dt_rec.NOMINATED_COURSE_CD,
468 X_NCC_VERSION_NUMBER => cur_per_typ_dt_rec.NCC_VERSION_NUMBER,
469 X_SEQUENCE_NUMBER => cur_per_typ_dt_rec.SEQUENCE_NUMBER,
470 X_START_DATE => cur_per_typ_dt_rec.START_DATE,
471 X_END_DATE => NULL, --- what should be the end date
472 X_CREATE_METHOD => cur_per_typ_dt_rec.CREATE_METHOD,
473 X_ENDED_BY => NULL,
474 X_END_METHOD => NULL,
475 X_MODE => 'R' ,
476 X_EMPLMNT_CATEGORY_CODE => cur_per_typ_dt_rec.emplmnt_category_code);
477 END IF;
478 CLOSE cur_per_typ_dt;
479 END IF;
480
481 ELSIF p_deleting THEN
482 l_date := SYSDATE;
483 l_person_type := NULL;
484 l_method := 'PERSON_NO_ENROLL_PRG';
485
486 -- Select Person type Code for the System type GRADUATE
487 OPEN cur_pers_type('GRADUATE');
488 FETCH cur_pers_type INTO l_person_type;
489 CLOSE cur_pers_type;
490 IF l_person_type IS NULL THEN
491 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
492 IGS_GE_MSG_STACK.ADD;
493 App_Exception.Raise_Exception;
494 END IF;
495
496 OPEN cur_conf_dt(p_rowid);
497 FETCH cur_conf_dt INTO cur_conf_dt_rec;
498 IF cur_conf_dt%FOUND THEN
499 -- Check any active record found for this student program, with System Person Type,GRADUATE
500 OPEN cur_pe_typ_inst( cur_conf_dt_rec.person_id,
501 cur_conf_dt_rec.COURSE_CD,
502 'GRADUATE');
503 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
504 IF cur_pe_typ_inst%FOUND THEN
505 IF SYSDATE < cur_pe_typ_inst_rec.START_DATE THEN
506 l_date := cur_pe_typ_inst_rec.START_DATE;
507 END IF;
508 igs_pe_typ_instances_pkg.update_row(
509 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
510 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
511 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
512 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
513 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
514 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
515 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
516 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
517 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
518 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
519 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
520 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
521 X_END_DATE => l_date,
522 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
523 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
524 X_END_METHOD => l_method,
525 X_MODE => 'R' ,
526 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
527 END IF;
528 CLOSE cur_pe_typ_inst;
529
530 l_person_type := NULL;
531 -- Select Person type Code for the System type FORMER_STUDENT
532 OPEN cur_pers_type('FORMER_STUDENT');
533 FETCH cur_pers_type INTO l_person_type;
534 CLOSE cur_pers_type;
535 IF l_person_type IS NULL THEN
536 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
537 IGS_GE_MSG_STACK.ADD;
538 App_Exception.Raise_Exception;
539 END IF;
540 IF l_date = SYSDATE THEN
541 OPEN cur_typ_id_inst(cur_conf_dt_rec.PERSON_ID,cur_conf_dt_rec.COURSE_CD,l_person_type);
542 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
543 IF cur_typ_id_inst%NOTFOUND THEN
544 igs_pe_typ_instances_pkg.insert_row(
545 X_ROWID => l_ROWID,
546 X_PERSON_ID => cur_conf_dt_rec.PERSON_ID,
547 X_COURSE_CD => cur_conf_dt_rec.COURSE_CD,
548 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
549 X_PERSON_TYPE_CODE => l_person_type,
550 X_CC_VERSION_NUMBER => NULL,
551 X_FUNNEL_STATUS => NULL,
552 X_ADMISSION_APPL_NUMBER => NULL,
553 X_NOMINATED_COURSE_CD => NULL,
554 X_NCC_VERSION_NUMBER => NULL,
555 X_SEQUENCE_NUMBER => NULL,
556 X_START_DATE => SYSDATE, -- what should be the start date
557 X_END_DATE => NULL,
558 X_CREATE_METHOD => l_method,
559 X_ENDED_BY => NULL,
560 X_END_METHOD => NULL,
561 X_MODE => 'R',
562 X_ORG_ID => NULL,--new_references.ORG_ID,
563 X_EMPLMNT_CATEGORY_CODE => NULL
564 );
565 END IF;
566 CLOSE cur_typ_id_inst;
567 ELSE
568 OPEN cur_per_typ_dt( cur_conf_dt_rec.PERSON_ID,
569 cur_conf_dt_rec.COURSE_CD,
570 'FORMER_STUDENT',
571 l_date);
572 FETCH cur_per_typ_dt INTO cur_per_typ_dt_rec;
573 IF cur_per_typ_dt%FOUND THEN
574 igs_pe_typ_instances_pkg.update_row(
575 X_ROWID => cur_per_typ_dt_rec.ROW_ID,
576 X_PERSON_ID => cur_per_typ_dt_rec.PERSON_ID,
577 X_COURSE_CD => cur_per_typ_dt_rec.COURSE_CD,
578 X_TYPE_INSTANCE_ID => cur_per_typ_dt_rec.TYPE_INSTANCE_ID,
579 X_PERSON_TYPE_CODE => cur_per_typ_dt_rec.PERSON_TYPE_CODE,
580 X_CC_VERSION_NUMBER => cur_per_typ_dt_rec.CC_VERSION_NUMBER,
581 X_FUNNEL_STATUS => cur_per_typ_dt_rec.FUNNEL_STATUS,
582 X_ADMISSION_APPL_NUMBER => cur_per_typ_dt_rec.ADMISSION_APPL_NUMBER,
583 X_NOMINATED_COURSE_CD => cur_per_typ_dt_rec.NOMINATED_COURSE_CD,
584 X_NCC_VERSION_NUMBER => cur_per_typ_dt_rec.NCC_VERSION_NUMBER,
585 X_SEQUENCE_NUMBER => cur_per_typ_dt_rec.SEQUENCE_NUMBER,
586 X_START_DATE => cur_per_typ_dt_rec.START_DATE,
587 X_END_DATE => NULL, --- what should be the end date
588 X_CREATE_METHOD => cur_per_typ_dt_rec.CREATE_METHOD,
589 X_ENDED_BY => NULL,
590 X_END_METHOD => NULL,
591 X_MODE => 'R' ,
592 X_EMPLMNT_CATEGORY_CODE => cur_per_typ_dt_rec.emplmnt_category_code);
593 END IF;
594 CLOSE cur_per_typ_dt;
595 END IF;
596 CLOSE cur_conf_dt;
597 END IF;
598 END IF;
599 END AfterRowInsertUpdate;
600
601 PROCEDURE check_parent_existance AS
602 /*
603 || Created By : [email protected]
604 || Created On : 22-NOV-2001
605 || Purpose : Checks for the existance of Parent records.
606 || Known limitations, enhancements or remarks :
607 || Change History :
608 || Who When What
609 || (reverse chronological order - newest change first)
610 || Nalin Kumar 22-Oct-2002 Added the call to igs_gr_honours_level_pkg.get_pk_for_validation to validate the foreign key.
611 */
612 BEGIN
613
614 IF (((old_references.person_id = new_references.person_id) AND
615 (old_references.course_cd = new_references.course_cd)) OR
616 ((new_references.person_id IS NULL) OR
617 (new_references.course_cd IS NULL))) THEN
618 NULL;
619 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
620 new_references.person_id,
621 new_references.course_cd
622 ) THEN
623 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
624 igs_ge_msg_stack.add;
625 app_exception.raise_exception;
626 END IF;
627
628 IF (((old_references.award_cd = new_references.award_cd)) OR
629 ((new_references.award_cd IS NULL))) THEN
630 NULL;
631 ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation (
632 new_references.award_cd
633 ) THEN
634 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
635 igs_ge_msg_stack.add;
636 app_exception.raise_exception;
637 END IF;
638
639 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
640 (old_references.gs_version_number = new_references.gs_version_number)) OR
641 ((new_references.grading_schema_cd IS NULL) OR
642 (new_references.gs_version_number IS NULL))) THEN
643 NULL;
644 ELSIF NOT igs_as_grd_schema_pkg.get_pk_for_validation (
645 new_references.grading_schema_cd,
646 new_references.gs_version_number
647 ) THEN
648 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
649 igs_ge_msg_stack.add;
650 app_exception.raise_exception;
651 END IF;
652
653 END check_parent_existance;
654
655 PROCEDURE Check_Child_Existance AS
656 /*
657 || Created By : [email protected]
658 || Created On : 24-Sept-2003
659 || Purpose : Checking for child existance
660 || Known limitations, enhancements or remarks :
661 || Change History :
662 || Who When What
663 || (reverse chronological order - newest change first)
664 */
665 BEGIN
666 IGS_GR_GRADUAND_PKG.GET_FK_IGS_EN_SPA_AWD(
667 old_references.person_id,
668 old_references.course_cd,
669 old_references.award_cd
670 );
671 END Check_Child_Existance;
672
673 FUNCTION get_pk_for_validation (
674 x_award_cd IN VARCHAR2,
675 x_course_cd IN VARCHAR2,
676 x_person_id IN NUMBER
677 ) RETURN BOOLEAN AS
678 /*
679 || Created By : [email protected]
680 || Created On : 22-NOV-2001
681 || Purpose : Validates the Primary Key of the table.
682 || Known limitations, enhancements or remarks :
683 || Change History :
684 || Who When What
685 || (reverse chronological order - newest change first)
686 */
687 CURSOR cur_rowid IS
688 SELECT rowid
689 FROM igs_en_spa_awd_aim
690 WHERE award_cd = x_award_cd
691 AND course_cd = x_course_cd
692 AND person_id = x_person_id
693 FOR UPDATE NOWAIT;
694
695 lv_rowid cur_rowid%RowType;
696
697 BEGIN
698
699 OPEN cur_rowid;
700 FETCH cur_rowid INTO lv_rowid;
701 IF (cur_rowid%FOUND) THEN
702 CLOSE cur_rowid;
703 RETURN(TRUE);
704 ELSE
705 CLOSE cur_rowid;
706 RETURN(FALSE);
707 END IF;
708
709 END get_pk_for_validation;
710
711
712 PROCEDURE get_fk_igs_en_stdnt_ps_att (
713 x_person_id IN NUMBER,
714 x_course_cd IN VARCHAR2
715 ) AS
716 /*
717 || Created By : [email protected]
718 || Created On : 22-NOV-2001
719 || Purpose : Validates the Foreign Keys for the table.
720 || Known limitations, enhancements or remarks :
721 || Change History :
722 || Who When What
723 || (reverse chronological order - newest change first)
724 */
725 CURSOR cur_rowid IS
726 SELECT rowid
727 FROM igs_en_spa_awd_aim
728 WHERE ((course_cd = x_course_cd) AND
729 (person_id = x_person_id));
730
731 lv_rowid cur_rowid%RowType;
732
733 BEGIN
734
735 OPEN cur_rowid;
736 FETCH cur_rowid INTO lv_rowid;
737 IF (cur_rowid%FOUND) THEN
738 CLOSE cur_rowid;
739 fnd_message.set_name ('IGS', 'IGS_EN_ESAA_SCA_FK');
740 igs_ge_msg_stack.add;
741 app_exception.raise_exception;
742 RETURN;
743 END IF;
744 CLOSE cur_rowid;
745
746 END get_fk_igs_en_stdnt_ps_att;
747
748
749 PROCEDURE get_fk_igs_as_grading_sch (
750 x_grading_schema_cd IN VARCHAR2,
751 x_gs_version_number IN NUMBER
752 ) AS
753 /*
754 || Created By : [email protected]
755 || Created On : 22-NOV-2001
756 || Purpose : Validates the Foreign Keys for the table.
757 || Known limitations, enhancements or remarks :
758 || Change History :
759 || rvangala 27-Aug-2004 Bug #3699796, changed incorrect column
760 || x_grading_schema_cd to grading_schema_cd
761 || in cursor cur_rowid
762 || (reverse chronological order - newest change first)
763 */
764 CURSOR cur_rowid IS
765 SELECT rowid
766 FROM igs_en_spa_awd_aim
767 WHERE (( grading_schema_cd = x_grading_schema_cd) AND
768 (gs_version_number = x_gs_version_number));
769
770 lv_rowid cur_rowid%RowType;
771
772 BEGIN
773
774 OPEN cur_rowid;
775 FETCH cur_rowid INTO lv_rowid;
776 IF (cur_rowid%FOUND) THEN
777 CLOSE cur_rowid;
778 fnd_message.set_name ('IGS', 'IGS_AS_GSG_GS_FK');
779 igs_ge_msg_stack.add;
780 app_exception.raise_exception;
781 RETURN;
782 END IF;
783 CLOSE cur_rowid;
784
785 END get_fk_igs_as_grading_sch;
786
787
788
789 PROCEDURE get_fk_igs_gr_honours_level (
790 x_honours_level IN VARCHAR2
791 ) AS
792 /*
793 || Created By : [email protected]
794 || Created On : 22-Oct-2002
795 || Purpose : Validates the Foreign Keys for the table.
796 || Known limitations, enhancements or remarks :
797 || Change History :
798 || Who When What
799 || ijeddy 23-Sept-03 Obsoleted the function as per build #3129913.
800 || (reverse chronological order - newest change first)
801 */
802 BEGIN
803 NULL;
804 END get_fk_igs_gr_honours_level;
805
806
807 PROCEDURE before_dml (
808 p_action IN VARCHAR2,
809 x_rowid IN VARCHAR2,
810 x_person_id IN NUMBER ,
811 x_course_cd IN VARCHAR2,
812 x_award_cd IN VARCHAR2,
813 x_start_dt IN DATE ,
814 x_end_dt IN DATE ,
815 x_complete_ind IN VARCHAR2,
816 x_honours_level IN VARCHAR2,
817 x_conferral_date IN DATE ,
818 x_creation_date IN DATE ,
819 x_created_by IN NUMBER ,
820 x_last_update_date IN DATE ,
821 x_last_updated_by IN NUMBER ,
822 x_last_update_login IN NUMBER ,
823 x_award_mark IN NUMBER,
824 x_award_grade IN VARCHAR2,
825 x_grading_schema_cd IN VARCHAR2,
826 x_gs_version_number IN NUMBER
827 ) AS
828 /*
829 || Created By : [email protected]
830 || Created On : 22-NOV-2001
831 || Purpose : Initialises the columns, Checks Constraints, Calls the
832 || Trigger Handlers for the table, before any DML operation.
833 || Known limitations, enhancements or remarks :
834 || Change History :
835 || Who When What
836 || (reverse chronological order - newest change first)
837 */
838 BEGIN
839 set_column_values (
840 p_action,
841 x_rowid,
842 x_person_id,
843 x_course_cd,
844 x_award_cd,
845 x_start_dt,
846 x_end_dt,
847 x_complete_ind,
848 x_conferral_date,
849 x_creation_date,
850 x_created_by,
851 x_last_update_date,
852 x_last_updated_by,
853 x_last_update_login ,
854 x_award_mark,
855 x_award_grade,
856 x_grading_schema_cd,
857 x_gs_version_number
858 );
859
860 IF (p_action = 'INSERT') THEN
861 -- Call all the procedures related to Before Insert.
862 IF ( get_pk_for_validation(
863 new_references.award_cd,
864 new_references.course_cd,
865 new_references.person_id
866 )
867 ) THEN
868 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
869 igs_ge_msg_stack.add;
870 app_exception.raise_exception;
871 END IF;
872 check_parent_existance;
873 ELSIF (p_action = 'UPDATE') THEN
874 -- Call all the procedures related to Before Update.
875 check_parent_existance;
876 ELSIF (p_action = 'DELETE') THEN
877 -- Call all the procedures related to Before Delete.
878 AfterRowInsertUpdate( p_rowid => x_rowid,
879 p_inserting => FALSE,
880 p_updating => FALSE,
881 p_deleting => TRUE );
882 Check_Child_Existance;
883 del_spaa_hist(x_rowid);
884 ELSIF (p_action = 'VALIDATE_DELETE') THEN
885 -- Call all the procedures related to Before Delete.
886 Check_Child_Existance;
887
888 ELSIF (p_action = 'VALIDATE_INSERT') THEN
889 -- Call all the procedures related to Before Insert.
890 IF ( get_pk_for_validation (
891 new_references.award_cd,
892 new_references.course_cd,
893 new_references.person_id
894 )
895 ) THEN
896 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
897 igs_ge_msg_stack.add;
898 app_exception.raise_exception;
899 END IF;
900 END IF;
901
902 END before_dml;
903
904 PROCEDURE After_DML (
905 p_action IN VARCHAR2,
906 x_rowid IN VARCHAR2
907 ) AS
908 BEGIN
909 l_rowid := x_rowid;
910 IF (p_action = 'UPDATE') THEN
911 -- Call all the procedures related to After Update.
912 AfterRowInsertUpdate1 ( p_updating => TRUE );
913 END IF;
914 IF(NVL(old_references.conferral_date,IGS_GE_DATE.IGSDATE('1900/01/01')) <> NVL(new_references.conferral_date,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
915 AfterRowInsertUpdate( p_inserting => FALSE,
916 p_updating => TRUE,
917 p_deleting => FALSE );
918
919 END IF;
920 END After_DML;
921 PROCEDURE insert_row (
922 x_rowid IN OUT NOCOPY VARCHAR2,
923 x_person_id IN NUMBER,
924 x_course_cd IN VARCHAR2,
925 x_award_cd IN VARCHAR2,
926 x_start_dt IN DATE,
927 x_end_dt IN DATE,
928 x_complete_ind IN VARCHAR2,
929 x_honours_level IN VARCHAR2,
930 x_conferral_date IN DATE ,
931 x_mode IN VARCHAR2 ,
932 x_award_mark IN NUMBER,
933 x_award_grade IN VARCHAR2,
934 x_grading_schema_cd IN VARCHAR2,
935 x_gs_version_number IN NUMBER
936 ) AS
937 /*
938 || Created By : [email protected]
939 || Created On : 22-NOV-2001
940 || Purpose : Handles the INSERT DML logic for the table.
941 || Known limitations, enhancements or remarks :
942 || Change History :
943 || Who When What
944 || (reverse chronological order - newest change first)
945 */
946 CURSOR c IS
947 SELECT rowid
948 FROM igs_en_spa_awd_aim
949 WHERE award_cd = x_award_cd
950 AND course_cd = x_course_cd
951 AND person_id = x_person_id;
952
953 x_last_update_date DATE;
954 x_last_updated_by NUMBER;
955 x_last_update_login NUMBER;
956
957 BEGIN
958
959 x_last_update_date := SYSDATE;
960 IF (x_mode = 'I') THEN
961 x_last_updated_by := 1;
962 x_last_update_login := 0;
963 ELSIF (X_MODE IN ('R', 'S')) THEN
964 x_last_updated_by := fnd_global.user_id;
965 IF (x_last_updated_by IS NULL) THEN
966 x_last_updated_by := -1;
967 END IF;
968 x_last_update_login := fnd_global.login_id;
969 IF (x_last_update_login IS NULL) THEN
970 x_last_update_login := -1;
971 END IF;
972 ELSE
973 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
974 igs_ge_msg_stack.add;
975 app_exception.raise_exception;
976 END IF;
977
978 before_dml(
979 p_action => 'INSERT',
980 x_rowid => x_rowid,
981 x_person_id => x_person_id,
982 x_course_cd => x_course_cd,
983 x_award_cd => x_award_cd,
984 x_start_dt => x_start_dt,
985 x_end_dt => x_end_dt,
986 x_complete_ind => x_complete_ind,
987 x_conferral_date => x_conferral_date,
988 x_creation_date => x_last_update_date,
989 x_created_by => x_last_updated_by,
990 x_last_update_date => x_last_update_date,
991 x_last_updated_by => x_last_updated_by,
992 x_last_update_login => x_last_update_login ,
993 x_award_mark => x_award_mark,
994 x_award_grade => x_award_grade,
995 x_grading_schema_cd => x_grading_schema_cd,
996 x_gs_version_number => x_gs_version_number
997 );
998
999 IF (x_mode = 'S') THEN
1000 igs_sc_gen_001.set_ctx('R');
1001 END IF;
1002 INSERT INTO igs_en_spa_awd_aim (
1003 person_id,
1004 course_cd,
1005 award_cd,
1006 start_dt,
1007 end_dt,
1008 complete_ind,
1009 conferral_date,
1010 creation_date,
1011 created_by,
1012 last_update_date,
1013 last_updated_by,
1014 last_update_login,
1015 award_mark,
1016 award_grade,
1017 grading_schema_cd,
1018 gs_version_number
1019
1020 ) VALUES (
1021 new_references.person_id,
1022 new_references.course_cd,
1023 new_references.award_cd,
1024 new_references.start_dt,
1025 new_references.end_dt,
1026 new_references.complete_ind,
1027 new_references.conferral_date,
1028 x_last_update_date,
1029 x_last_updated_by,
1030 x_last_update_date,
1031 x_last_updated_by,
1032 x_last_update_login ,
1033 new_references.award_mark,
1034 new_references.award_grade,
1035 new_references.grading_schema_cd,
1036 new_references.gs_version_number
1037 );
1038 IF (x_mode = 'S') THEN
1039 igs_sc_gen_001.unset_ctx('R');
1040 END IF;
1041
1042 OPEN c;
1043 FETCH c INTO x_rowid;
1044 IF (c%NOTFOUND) THEN
1045 CLOSE c;
1046 RAISE NO_DATA_FOUND;
1047 END IF;
1048 CLOSE c;
1049
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1054 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1055 fnd_message.set_token ('ERR_CD', SQLCODE);
1056 igs_ge_msg_stack.add;
1057 igs_sc_gen_001.unset_ctx('R');
1058 app_exception.raise_exception;
1059 ELSE
1060 igs_sc_gen_001.unset_ctx('R');
1061 RAISE;
1062 END IF;
1063 END insert_row;
1064
1065
1066 PROCEDURE lock_row (
1067 x_rowid IN VARCHAR2,
1068 x_person_id IN NUMBER,
1069 x_course_cd IN VARCHAR2,
1070 x_award_cd IN VARCHAR2,
1071 x_start_dt IN DATE,
1072 x_end_dt IN DATE,
1073 x_complete_ind IN VARCHAR2,
1074 x_honours_level IN VARCHAR2,
1075 x_conferral_date IN DATE ,
1076 x_award_mark IN NUMBER,
1077 x_award_grade IN VARCHAR2,
1078 x_grading_schema_cd IN VARCHAR2,
1079 x_gs_version_number IN NUMBER
1080 ) AS
1081 /*
1082 || Created By : [email protected]
1083 || Created On : 22-NOV-2001
1084 || Purpose : Handles the LOCK mechanism for the table.
1085 || Known limitations, enhancements or remarks :
1086 || Change History :
1087 || Who When What
1088 || (reverse chronological order - newest change first)
1089 || Nishikant 24MAY2002 Bug#2386592. Date fields was not being truncating before comparing.
1090 */
1091 CURSOR c1 IS
1092 SELECT
1093 start_dt,
1094 end_dt,
1095 complete_ind,
1096 conferral_date
1097 FROM igs_en_spa_awd_aim
1098 WHERE rowid = x_rowid
1099 FOR UPDATE NOWAIT;
1100
1101 tlinfo c1%ROWTYPE;
1102
1103 BEGIN
1104
1105 OPEN c1;
1106 FETCH c1 INTO tlinfo;
1107 IF (c1%notfound) THEN
1108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1109 igs_ge_msg_stack.add;
1110 CLOSE c1;
1111 app_exception.raise_exception;
1112 RETURN;
1113 END IF;
1114 CLOSE c1;
1115 -- TRUNCs added in the code by Nishikant - bug#2386592 - 24MAY2002.
1116 IF (
1117 (TRUNC(tlinfo.start_dt) = TRUNC(x_start_dt))
1118 AND ((TRUNC(tlinfo.end_dt) = TRUNC(x_end_dt)) OR ((tlinfo.end_dt IS NULL) AND (X_end_dt IS NULL)))
1119 AND (tlinfo.complete_ind = x_complete_ind)
1120 AND ((TRUNC(tlinfo.conferral_date) = TRUNC(x_conferral_date)) OR ((tlinfo.conferral_date IS NULL) AND (X_conferral_date IS NULL)))
1121 ) THEN
1122 NULL;
1123 ELSE
1124 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1125 igs_ge_msg_stack.add;
1126 app_exception.raise_exception;
1127 END IF;
1128
1129 RETURN;
1130
1131 END lock_row;
1132
1133
1134 PROCEDURE update_row (
1135 x_rowid IN VARCHAR2,
1136 x_person_id IN NUMBER,
1137 x_course_cd IN VARCHAR2,
1138 x_award_cd IN VARCHAR2,
1139 x_start_dt IN DATE,
1140 x_end_dt IN DATE,
1141 x_complete_ind IN VARCHAR2,
1142 x_honours_level IN VARCHAR2,
1143 x_conferral_date IN DATE,
1144 x_mode IN VARCHAR2 ,
1145 x_award_mark IN NUMBER,
1146 x_award_grade IN VARCHAR2,
1147 x_grading_schema_cd IN VARCHAR2,
1148 x_gs_version_number IN NUMBER
1149
1150 ) AS
1151 /*
1152 || Created By : [email protected]
1153 || Created On : 22-NOV-2001
1154 || Purpose : Handles the UPDATE DML logic for the table.
1155 || Known limitations, enhancements or remarks :
1156 || Change History :
1157 || Who When What
1158 || (reverse chronological order - newest change first)
1159 */
1160 x_last_update_date DATE ;
1161 x_last_updated_by NUMBER;
1162 x_last_update_login NUMBER;
1163
1164
1165 BEGIN
1166
1167 x_last_update_date := SYSDATE;
1168 IF (X_MODE = 'I') THEN
1169 x_last_updated_by := 1;
1170 x_last_update_login := 0;
1171 ELSIF (X_MODE IN ('R', 'S')) THEN
1172 x_last_updated_by := fnd_global.user_id;
1173 IF x_last_updated_by IS NULL THEN
1174 x_last_updated_by := -1;
1175 END IF;
1176 x_last_update_login := fnd_global.login_id;
1177 IF (x_last_update_login IS NULL) THEN
1178 x_last_update_login := -1;
1179 END IF;
1180 ELSE
1181 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1182 igs_ge_msg_stack.add;
1183 app_exception.raise_exception;
1184 END IF;
1185
1186 before_dml(
1187 p_action => 'UPDATE',
1188 x_rowid => x_rowid,
1189 x_person_id => x_person_id,
1190 x_course_cd => x_course_cd,
1191 x_award_cd => x_award_cd,
1192 x_start_dt => x_start_dt,
1193 x_end_dt => x_end_dt,
1194 x_complete_ind => x_complete_ind,
1195 x_conferral_date => x_conferral_date,
1196 x_creation_date => x_last_update_date,
1197 x_created_by => x_last_updated_by,
1198 x_last_update_date => x_last_update_date,
1199 x_last_updated_by => x_last_updated_by,
1200 x_last_update_login => x_last_update_login ,
1201 x_award_mark => x_award_mark,
1202 x_award_grade => x_award_grade,
1203 x_grading_schema_cd => x_grading_schema_cd,
1204 x_gs_version_number => x_gs_version_number
1205
1206 );
1207
1208 IF (x_mode = 'S') THEN
1209 igs_sc_gen_001.set_ctx('R');
1210 END IF;
1211 UPDATE igs_en_spa_awd_aim
1212 SET
1213 award_cd = new_references.award_cd,
1214 start_dt = new_references.start_dt,
1215 end_dt = new_references.end_dt,
1216 complete_ind = new_references.complete_ind,
1217 conferral_date = new_references.conferral_date,
1218 last_update_date = x_last_update_date,
1219 last_updated_by = x_last_updated_by,
1220 last_update_login = x_last_update_login ,
1221 award_mark = new_references.award_mark,
1222 award_grade = new_references.award_grade,
1223 grading_schema_cd = new_references.grading_schema_cd,
1224 gs_version_number = new_references.gs_version_number
1225 WHERE rowid = x_rowid;
1226
1227 IF (SQL%NOTFOUND) THEN
1228 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1229 igs_ge_msg_stack.add;
1230 igs_sc_gen_001.unset_ctx('R');
1231 app_exception.raise_exception;
1232 END IF;
1233 IF (x_mode = 'S') THEN
1234 igs_sc_gen_001.unset_ctx('R');
1235 END IF;
1236
1237 After_DML(
1238 p_action => 'UPDATE',
1239 x_rowid => x_rowid
1240 );
1241
1242
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 IF (SQLCODE = (-28115)) THEN
1247 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1248 fnd_message.set_token ('ERR_CD', SQLCODE);
1249 igs_ge_msg_stack.add;
1250 igs_sc_gen_001.unset_ctx('R');
1251 app_exception.raise_exception;
1252 ELSE
1253 igs_sc_gen_001.unset_ctx('R');
1254 RAISE;
1255 END IF;
1256 END update_row;
1257
1258
1259 PROCEDURE add_row (
1260 x_rowid IN OUT NOCOPY VARCHAR2,
1261 x_person_id IN NUMBER,
1262 x_course_cd IN VARCHAR2,
1263 x_award_cd IN VARCHAR2,
1264 x_start_dt IN DATE,
1265 x_end_dt IN DATE,
1266 x_complete_ind IN VARCHAR2,
1267 x_honours_level IN VARCHAR2,
1268 x_conferral_date IN DATE,
1269 x_mode IN VARCHAR2 ,
1270 x_award_mark IN NUMBER,
1271 x_award_grade IN VARCHAR2 DEFAULT NULL,
1272 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
1273 x_gs_version_number IN NUMBER
1274 ) AS
1275 /*
1276 || Created By : [email protected]
1277 || Created On : 22-NOV-2001
1278 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1279 || Known limitations, enhancements or remarks :
1280 || Change History :
1281 || Who When What
1282 || (reverse chronological order - newest change first)
1283 */
1284 CURSOR c1 IS
1285 SELECT rowid
1286 FROM igs_en_spa_awd_aim
1287 WHERE award_cd = x_award_cd
1288 AND course_cd = x_course_cd
1289 AND person_id = x_person_id;
1290
1291 BEGIN
1292
1293 OPEN c1;
1294 FETCH c1 INTO x_rowid;
1295 IF (c1%NOTFOUND) THEN
1296 CLOSE c1;
1297
1298 insert_row (
1299 x_rowid,
1300 x_person_id,
1301 x_course_cd,
1302 x_award_cd,
1303 x_start_dt,
1304 x_end_dt,
1305 x_complete_ind,
1306 x_conferral_date,
1307 x_mode,
1308 x_award_mark,
1309 x_award_grade,
1310 x_grading_schema_cd,
1311 x_gs_version_number
1312 );
1313 RETURN;
1314 END IF;
1315 CLOSE c1;
1316
1317 update_row (
1318 x_rowid,
1319 x_person_id,
1320 x_course_cd,
1321 x_award_cd,
1322 x_start_dt,
1323 x_end_dt,
1324 x_complete_ind,
1325 x_conferral_date,
1326 x_mode,
1327 x_award_mark,
1328 x_award_grade,
1329 x_grading_schema_cd ,
1330 x_gs_version_number
1331 );
1332
1333 END add_row;
1334
1335
1336 PROCEDURE delete_row (
1337 x_rowid IN VARCHAR2,
1338 x_mode IN VARCHAR2
1339 ) AS
1340 /*
1341 || Created By : [email protected]
1342 || Created On : 22-NOV-2001
1343 || Purpose : Handles the DELETE DML logic for the table.
1344 || Known limitations, enhancements or remarks :
1345 || Change History :
1346 || Who When What
1347 || (reverse chronological order - newest change first)
1348 */
1349 BEGIN
1350
1351 before_dml (
1352 p_action => 'DELETE',
1353 x_rowid => x_rowid
1354 );
1355
1356 IF (x_mode = 'S') THEN
1357 igs_sc_gen_001.set_ctx('R');
1358 END IF;
1359 DELETE FROM igs_en_spa_awd_aim
1360 WHERE rowid = x_rowid;
1361
1362 IF (SQL%NOTFOUND) THEN
1363 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1364 igs_ge_msg_stack.add;
1365 igs_sc_gen_001.unset_ctx('R');
1366 app_exception.raise_exception;
1367 END IF;
1368 IF (x_mode = 'S') THEN
1369 igs_sc_gen_001.unset_ctx('R');
1370 END IF;
1371
1372
1373 END delete_row;
1374
1375
1376 END igs_en_spa_awd_aim_pkg;