1 PACKAGE BODY igs_pr_stdnt_pr_awd_pkg AS
2 /* $Header: IGSQI38B.pls 120.0 2005/07/05 11:59:53 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_stdnt_pr_awd%ROWTYPE;
6 new_references igs_pr_stdnt_pr_awd%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_spo_sequence_number IN NUMBER DEFAULT NULL,
14 x_award_cd IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 12-DEC-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_pr_stdnt_pr_awd
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.person_id := x_person_id;
55 new_references.course_cd := x_course_cd;
56 new_references.spo_sequence_number := x_spo_sequence_number;
57 new_references.award_cd := x_award_cd;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_parent_existance AS
75 /*
76 || Created By : [email protected]
77 || Created On : 12-DEC-2001
78 || Purpose : Checks for the existance of Parent records.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF (((old_references.award_cd = new_references.award_cd)) OR
87 ((new_references.award_cd IS NULL))) THEN
88 NULL;
89 ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation (
90 new_references.award_cd
91 ) THEN
92 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 END IF;
96
97 IF (((old_references.person_id = new_references.person_id) AND
98 (old_references.course_cd = new_references.course_cd) AND
99 (old_references.spo_sequence_number = new_references.spo_sequence_number)) OR
100 ((new_references.person_id IS NULL) OR
101 (new_references.course_cd IS NULL) OR
102 (new_references.spo_sequence_number IS NULL))) THEN
103 NULL;
104 ELSIF NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
105 new_references.person_id,
106 new_references.course_cd,
107 new_references.spo_sequence_number
108 ) THEN
109 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 END IF;
113
114 END check_parent_existance;
115
116
117 FUNCTION get_pk_for_validation (
118 x_person_id IN NUMBER,
119 x_course_cd IN VARCHAR2,
120 x_spo_sequence_number IN NUMBER,
121 x_award_cd IN VARCHAR2
122 ) RETURN BOOLEAN AS
123 /*
124 || Created By : [email protected]
125 || Created On : 12-DEC-2001
126 || Purpose : Validates the Primary Key of the table.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 CURSOR cur_rowid IS
133 SELECT rowid
134 FROM igs_pr_stdnt_pr_awd
135 WHERE person_id = x_person_id
136 AND course_cd = x_course_cd
137 AND spo_sequence_number = x_spo_sequence_number
138 AND award_cd = x_award_cd
139 FOR UPDATE NOWAIT;
140
141 lv_rowid cur_rowid%RowType;
142
143 BEGIN
144
145 OPEN cur_rowid;
146 FETCH cur_rowid INTO lv_rowid;
147 IF (cur_rowid%FOUND) THEN
148 CLOSE cur_rowid;
149 RETURN(TRUE);
150 ELSE
151 CLOSE cur_rowid;
152 RETURN(FALSE);
153 END IF;
154
155 END get_pk_for_validation;
156
157
158 PROCEDURE get_fk_igs_pr_stdnt_pr_ou (
159 x_person_id IN NUMBER,
160 x_course_cd IN VARCHAR2,
161 x_sequence_number IN NUMBER
162 ) AS
163 /*
164 || Created By : [email protected]
165 || Created On : 12-DEC-2001
166 || Purpose : Validates the Foreign Keys for the table.
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || Who When What
170 || (reverse chronological order - newest change first)
171 */
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM igs_pr_stdnt_pr_awd
175 WHERE ((course_cd = x_course_cd) AND
176 (person_id = x_person_id) AND
177 (spo_sequence_number = x_sequence_number));
178
179 lv_rowid cur_rowid%RowType;
180
181 BEGIN
182
183 OPEN cur_rowid;
184 FETCH cur_rowid INTO lv_rowid;
185 IF (cur_rowid%FOUND) THEN
186 CLOSE cur_rowid;
187 fnd_message.set_name ('IGS', 'IGS_PR_SPOA_SPO_FK');
188 igs_ge_msg_stack.add;
189 app_exception.raise_exception;
190 RETURN;
191 END IF;
192 CLOSE cur_rowid;
193
194 END get_fk_igs_pr_stdnt_pr_ou;
195
196
197 PROCEDURE before_dml (
198 p_action IN VARCHAR2,
199 x_rowid IN VARCHAR2 DEFAULT NULL,
200 x_person_id IN NUMBER DEFAULT NULL,
201 x_course_cd IN VARCHAR2 DEFAULT NULL,
202 x_spo_sequence_number IN NUMBER DEFAULT NULL,
203 x_award_cd IN VARCHAR2 DEFAULT NULL,
204 x_creation_date IN DATE DEFAULT NULL,
205 x_created_by IN NUMBER DEFAULT NULL,
206 x_last_update_date IN DATE DEFAULT NULL,
207 x_last_updated_by IN NUMBER DEFAULT NULL,
208 x_last_update_login IN NUMBER DEFAULT NULL
209 ) AS
210 /*
211 || Created By : [email protected]
212 || Created On : 12-DEC-2001
213 || Purpose : Initialises the columns, Checks Constraints, Calls the
214 || Trigger Handlers for the table, before any DML operation.
215 || Known limitations, enhancements or remarks :
216 || Change History :
217 || Who When What
218 || (reverse chronological order - newest change first)
219 */
220 BEGIN
221
222 set_column_values (
223 p_action,
224 x_rowid,
225 x_person_id,
226 x_course_cd,
227 x_spo_sequence_number,
228 x_award_cd,
229 x_creation_date,
230 x_created_by,
231 x_last_update_date,
232 x_last_updated_by,
233 x_last_update_login
234 );
235
236 IF (p_action = 'INSERT') THEN
237 -- Call all the procedures related to Before Insert.
238 IF ( get_pk_for_validation(
239 new_references.person_id,
240 new_references.course_cd,
241 new_references.spo_sequence_number,
242 new_references.award_cd
243 )
244 ) THEN
245 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
246 igs_ge_msg_stack.add;
247 app_exception.raise_exception;
248 END IF;
249 check_parent_existance;
250 ELSIF (p_action = 'UPDATE') THEN
251 -- Call all the procedures related to Before Update.
252 check_parent_existance;
253 ELSIF (p_action = 'VALIDATE_INSERT') THEN
254 -- Call all the procedures related to Before Insert.
255 IF ( get_pk_for_validation (
256 new_references.person_id,
257 new_references.course_cd,
258 new_references.spo_sequence_number,
259 new_references.award_cd
260 )
261 ) THEN
262 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
263 igs_ge_msg_stack.add;
264 app_exception.raise_exception;
265 END IF;
266 END IF;
267
268 END before_dml;
269
270
271 PROCEDURE insert_row (
272 x_rowid IN OUT NOCOPY VARCHAR2,
273 x_person_id IN NUMBER,
274 x_course_cd IN VARCHAR2,
275 x_spo_sequence_number IN NUMBER,
276 x_award_cd IN VARCHAR2,
277 x_mode IN VARCHAR2 DEFAULT 'R'
278 ) AS
279 /*
280 || Created By : [email protected]
281 || Created On : 12-DEC-2001
282 || Purpose : Handles the INSERT DML logic for the table.
283 || Known limitations, enhancements or remarks :
284 || Change History :
285 || Who When What
286 || (reverse chronological order - newest change first)
287 */
288 CURSOR c IS
289 SELECT rowid
290 FROM igs_pr_stdnt_pr_awd
291 WHERE person_id = x_person_id
292 AND course_cd = x_course_cd
293 AND spo_sequence_number = x_spo_sequence_number
294 AND award_cd = x_award_cd;
295
296 x_last_update_date DATE;
297 x_last_updated_by NUMBER;
298 x_last_update_login NUMBER;
299
300 BEGIN
301
302 x_last_update_date := SYSDATE;
303 IF (x_mode = 'I') THEN
304 x_last_updated_by := 1;
305 x_last_update_login := 0;
306 ELSIF (X_MODE IN ('R', 'S')) THEN
307 x_last_updated_by := fnd_global.user_id;
308 IF (x_last_updated_by IS NULL) THEN
309 x_last_updated_by := -1;
310 END IF;
311 x_last_update_login := fnd_global.login_id;
312 IF (x_last_update_login IS NULL) THEN
313 x_last_update_login := -1;
314 END IF;
315 ELSE
316 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
317 igs_ge_msg_stack.add;
318 app_exception.raise_exception;
319 END IF;
320
321 before_dml(
322 p_action => 'INSERT',
323 x_rowid => x_rowid,
324 x_person_id => x_person_id,
325 x_course_cd => x_course_cd,
326 x_spo_sequence_number => x_spo_sequence_number,
327 x_award_cd => x_award_cd,
328 x_creation_date => x_last_update_date,
329 x_created_by => x_last_updated_by,
330 x_last_update_date => x_last_update_date,
331 x_last_updated_by => x_last_updated_by,
332 x_last_update_login => x_last_update_login
333 );
334
335 IF (x_mode = 'S') THEN
336 igs_sc_gen_001.set_ctx('R');
337 END IF;
338 INSERT INTO igs_pr_stdnt_pr_awd (
339 person_id,
340 course_cd,
341 spo_sequence_number,
342 award_cd,
343 creation_date,
344 created_by,
345 last_update_date,
346 last_updated_by,
347 last_update_login
348 ) VALUES (
349 new_references.person_id,
350 new_references.course_cd,
351 new_references.spo_sequence_number,
352 new_references.award_cd,
353 x_last_update_date,
354 x_last_updated_by,
355 x_last_update_date,
356 x_last_updated_by,
357 x_last_update_login
358 );
359 IF (x_mode = 'S') THEN
360 igs_sc_gen_001.unset_ctx('R');
361 END IF;
362
363
364 OPEN c;
365 FETCH c INTO x_rowid;
366 IF (c%NOTFOUND) THEN
367 CLOSE c;
368 RAISE NO_DATA_FOUND;
369 END IF;
370 CLOSE c;
371
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
376 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
377 fnd_message.set_token ('ERR_CD', SQLCODE);
378 igs_ge_msg_stack.add;
379 igs_sc_gen_001.unset_ctx('R');
380 app_exception.raise_exception;
381 ELSE
382 igs_sc_gen_001.unset_ctx('R');
383 RAISE;
384 END IF;
385 END insert_row;
386
387
388 PROCEDURE lock_row (
389 x_rowid IN VARCHAR2,
390 x_person_id IN NUMBER,
391 x_course_cd IN VARCHAR2,
392 x_spo_sequence_number IN NUMBER,
393 x_award_cd IN VARCHAR2
394 ) AS
395 /*
396 || Created By : [email protected]
397 || Created On : 12-DEC-2001
398 || Purpose : Handles the LOCK mechanism for the table.
399 || Known limitations, enhancements or remarks :
400 || Change History :
401 || Who When What
402 || (reverse chronological order - newest change first)
403 */
404 CURSOR c1 IS
405 SELECT
406 rowid
407 FROM igs_pr_stdnt_pr_awd
408 WHERE rowid = x_rowid
409 FOR UPDATE NOWAIT;
410
411 tlinfo c1%ROWTYPE;
412
413 BEGIN
414
415 OPEN c1;
416 FETCH c1 INTO tlinfo;
417 IF (c1%notfound) THEN
418 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
419 igs_ge_msg_stack.add;
420 CLOSE c1;
421 app_exception.raise_exception;
422 RETURN;
423 END IF;
424 CLOSE c1;
425
426
427 RETURN;
428
429 END lock_row;
430
431
432 PROCEDURE delete_row (
433 x_rowid IN VARCHAR2,
434 x_mode IN VARCHAR2
435 ) AS
436 /*
437 || Created By : [email protected]
438 || Created On : 12-DEC-2001
439 || Purpose : Handles the DELETE DML logic for the table.
440 || Known limitations, enhancements or remarks :
441 || Change History :
442 || Who When What
443 || (reverse chronological order - newest change first)
444 */
445 BEGIN
446
447 before_dml (
448 p_action => 'DELETE',
449 x_rowid => x_rowid
450 );
451
452 IF (x_mode = 'S') THEN
453 igs_sc_gen_001.set_ctx('R');
454 END IF;
455 DELETE FROM igs_pr_stdnt_pr_awd
456 WHERE rowid = x_rowid;
457
458 IF (SQL%NOTFOUND) THEN
459 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
460 igs_ge_msg_stack.add;
461 igs_sc_gen_001.unset_ctx('R');
462 app_exception.raise_exception;
463 END IF;
464 IF (x_mode = 'S') THEN
465 igs_sc_gen_001.unset_ctx('R');
466 END IF;
467
468
469 END delete_row;
470
471
472 END igs_pr_stdnt_pr_awd_pkg;
[Home] [Help]