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