1 PACKAGE BODY igs_uc_exam_scores_pkg AS
2 /* $Header: IGSXI18B.pls 115.7 2003/02/28 07:47:34 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_exam_scores%ROWTYPE;
6 new_references igs_uc_exam_scores%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_exam_scores_id IN NUMBER ,
12 x_ref_code_type IN VARCHAR2,
13 x_exam_level IN VARCHAR2,
14 x_points IN VARCHAR2,
15 x_grades IN VARCHAR2,
16 x_creation_date IN DATE ,
17 x_created_by IN NUMBER ,
18 x_last_update_date IN DATE ,
19 x_last_updated_by IN NUMBER ,
20 x_last_update_login IN NUMBER
21 ) AS
22 /*
23 || Created By : rgopalan
24 || Created On : 13-NOV-2001
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_UC_EXAM_SCORES
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.exam_scores_id := x_exam_scores_id;
56 new_references.ref_code_type := x_ref_code_type;
57 new_references.exam_level := x_exam_level;
58 new_references.points := x_points;
59 new_references.grades := x_grades;
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75
76 PROCEDURE check_parent_existance AS
77 /*
78 || Created By : rgopalan
79 || Created On : 13-NOV-2001
80 || Purpose : Checks for the existance of Parent records.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 BEGIN
87
88 IF (((old_references.ref_code_type = new_references.ref_code_type) AND
89 (old_references.exam_level = new_references.exam_level)) OR
90 ((new_references.ref_code_type IS NULL) OR
91 (new_references.exam_level IS NULL))) THEN
92 NULL;
93 ELSIF NOT igs_uc_ref_codes_pkg.get_pk_for_validation (
94 new_references.ref_code_type,
95 new_references.exam_level
96 ) THEN
97 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
98 igs_ge_msg_stack.add;
99 app_exception.raise_exception;
100 END IF;
101
102 END check_parent_existance;
103
104
105 FUNCTION get_pk_for_validation (
106 x_exam_scores_id IN NUMBER
107 ) RETURN BOOLEAN AS
108 /*
109 || Created By : rgopalan
110 || Created On : 13-NOV-2001
111 || Purpose : Validates the Primary Key of the table.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || (reverse chronological order - newest change first)
116 */
117 CURSOR cur_rowid IS
118 SELECT rowid
119 FROM igs_uc_exam_scores
120 WHERE exam_scores_id = x_exam_scores_id ;
121
122 lv_rowid cur_rowid%RowType;
123
124 BEGIN
125
126 OPEN cur_rowid;
127 FETCH cur_rowid INTO lv_rowid;
128 IF (cur_rowid%FOUND) THEN
129 CLOSE cur_rowid;
130 RETURN(TRUE);
131 ELSE
132 CLOSE cur_rowid;
133 RETURN(FALSE);
134 END IF;
135
136 END get_pk_for_validation;
137
138
139 PROCEDURE get_fk_igs_uc_ref_codes (
140 x_code_type IN VARCHAR2,
141 x_code IN VARCHAR2
142 ) AS
143 /*
144 || Created By : rgopalan
145 || Created On : 13-NOV-2001
146 || Purpose : Validates the Foreign Keys for the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM igs_uc_exam_scores
155 WHERE ((exam_level = x_code) AND
156 (ref_code_type = x_code_type));
157
158 lv_rowid cur_rowid%RowType;
159
160 BEGIN
161
162 OPEN cur_rowid;
163 FETCH cur_rowid INTO lv_rowid;
164 IF (cur_rowid%FOUND) THEN
165 CLOSE cur_rowid;
166 fnd_message.set_name ('IGS', 'IGS_UC_UCES_UCRECD_FK');
167 igs_ge_msg_stack.add;
168 app_exception.raise_exception;
169 RETURN;
170 END IF;
171 CLOSE cur_rowid;
172
173 END get_fk_igs_uc_ref_codes;
174
175
176 PROCEDURE before_dml (
177 p_action IN VARCHAR2,
178 x_rowid IN VARCHAR2,
179 x_exam_scores_id IN NUMBER ,
180 x_ref_code_type IN VARCHAR2,
181 x_exam_level IN VARCHAR2,
182 x_points IN VARCHAR2,
183 x_grades IN VARCHAR2,
184 x_creation_date IN DATE ,
185 x_created_by IN NUMBER ,
186 x_last_update_date IN DATE ,
187 x_last_updated_by IN NUMBER ,
188 x_last_update_login IN NUMBER
189 ) AS
190 /*
191 || Created By : rgopalan
192 || Created On : 13-NOV-2001
193 || Purpose : Initialises the columns, Checks Constraints, Calls the
194 || Trigger Handlers for the table, before any DML operation.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 BEGIN
201
202 set_column_values (
203 p_action,
204 x_rowid,
205 x_exam_scores_id,
206 x_ref_code_type,
207 x_exam_level,
208 x_points,
209 x_grades,
210 x_creation_date,
211 x_created_by,
212 x_last_update_date,
213 x_last_updated_by,
214 x_last_update_login
215 );
216
217 IF (p_action = 'INSERT') THEN
218 -- Call all the procedures related to Before Insert.
219 IF ( get_pk_for_validation(
220 new_references.exam_scores_id
221 )
222 ) THEN
223 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END IF;
227 check_parent_existance;
228 ELSIF (p_action = 'UPDATE') THEN
229 -- Call all the procedures related to Before Update.
230 check_parent_existance;
231 ELSIF (p_action = 'VALIDATE_INSERT') THEN
232 -- Call all the procedures related to Before Insert.
233 IF ( get_pk_for_validation (
234 new_references.exam_scores_id
235 )
236 ) THEN
237 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
238 igs_ge_msg_stack.add;
239 app_exception.raise_exception;
240 END IF;
241 END IF;
242
243 END before_dml;
244
245
246 PROCEDURE insert_row (
247 x_rowid IN OUT NOCOPY VARCHAR2,
248 x_exam_scores_id IN OUT NOCOPY NUMBER,
249 x_ref_code_type IN VARCHAR2,
250 x_exam_level IN VARCHAR2,
251 x_points IN VARCHAR2,
252 x_grades IN VARCHAR2,
253 x_mode IN VARCHAR2
254 ) AS
255 /*
256 || Created By : rgopalan
257 || Created On : 13-NOV-2001
258 || Purpose : Handles the INSERT DML logic for the table.
259 || Known limitations, enhancements or remarks :
260 || Change History :
261 || Who When What
262 || (reverse chronological order - newest change first)
263 */
264 CURSOR c IS
265 SELECT rowid
266 FROM igs_uc_exam_scores
267 WHERE exam_scores_id = x_exam_scores_id;
268
269 x_last_update_date DATE;
270 x_last_updated_by NUMBER;
271 x_last_update_login NUMBER;
272
273 BEGIN
274
275 x_last_update_date := SYSDATE;
276 IF (x_mode = 'I') THEN
277 x_last_updated_by := 1;
278 x_last_update_login := 0;
279 ELSIF (x_mode = 'R') THEN
280 x_last_updated_by := fnd_global.user_id;
281 IF (x_last_updated_by IS NULL) THEN
282 x_last_updated_by := -1;
283 END IF;
284 x_last_update_login := fnd_global.login_id;
285 IF (x_last_update_login IS NULL) THEN
286 x_last_update_login := -1;
287 END IF;
288 ELSE
289 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 END IF;
293
294 SELECT igs_uc_exam_scores_s.NEXTVAL
295 INTO x_exam_scores_id
296 FROM dual;
297
298 before_dml(
299 p_action => 'INSERT',
300 x_rowid => x_rowid,
301 x_exam_scores_id => x_exam_scores_id,
302 x_ref_code_type => x_ref_code_type,
303 x_exam_level => x_exam_level,
304 x_points => x_points,
305 x_grades => x_grades,
306 x_creation_date => x_last_update_date,
307 x_created_by => x_last_updated_by,
308 x_last_update_date => x_last_update_date,
309 x_last_updated_by => x_last_updated_by,
310 x_last_update_login => x_last_update_login
311 );
312
313 INSERT INTO igs_uc_exam_scores (
314 exam_scores_id,
315 ref_code_type,
316 exam_level,
317 points,
318 grades,
319 creation_date,
320 created_by,
321 last_update_date,
322 last_updated_by,
323 last_update_login
324 ) VALUES (
325 new_references.exam_scores_id,
326 new_references.ref_code_type,
327 new_references.exam_level,
328 new_references.points,
329 new_references.grades,
330 x_last_update_date,
331 x_last_updated_by,
332 x_last_update_date,
333 x_last_updated_by,
334 x_last_update_login
335 );
336
337 OPEN c;
338 FETCH c INTO x_rowid;
339 IF (c%NOTFOUND) THEN
340 CLOSE c;
341 RAISE NO_DATA_FOUND;
342 END IF;
343 CLOSE c;
344
345 END insert_row;
346
347
348 PROCEDURE lock_row (
349 x_rowid IN VARCHAR2,
350 x_exam_scores_id IN NUMBER,
351 x_ref_code_type IN VARCHAR2,
352 x_exam_level IN VARCHAR2,
353 x_points IN VARCHAR2,
354 x_grades IN VARCHAR2
355 ) AS
356 /*
357 || Created By : rgopalan
358 || Created On : 13-NOV-2001
359 || Purpose : Handles the LOCK mechanism for the table.
360 || Known limitations, enhancements or remarks :
361 || Change History :
362 || Who When What
363 || (reverse chronological order - newest change first)
364 */
365 CURSOR c1 IS
366 SELECT
367 ref_code_type,
368 exam_level,
369 points,
370 grades
371 FROM igs_uc_exam_scores
372 WHERE rowid = x_rowid
373 FOR UPDATE NOWAIT;
374
375 tlinfo c1%ROWTYPE;
376
377 BEGIN
378
379 OPEN c1;
380 FETCH c1 INTO tlinfo;
381 IF (c1%notfound) THEN
382 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
383 igs_ge_msg_stack.add;
384 CLOSE c1;
385 app_exception.raise_exception;
386 RETURN;
387 END IF;
388 CLOSE c1;
389
390 IF (
391 (tlinfo.ref_code_type = x_ref_code_type)
392 AND (tlinfo.exam_level = x_exam_level)
393 AND ((tlinfo.points = x_points) OR ((tlinfo.points IS NULL) AND (X_points IS NULL)))
394 AND ((tlinfo.grades = x_grades) OR ((tlinfo.grades IS NULL) AND (X_grades IS NULL)))
395 ) THEN
396 NULL;
397 ELSE
398 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
399 igs_ge_msg_stack.add;
400 app_exception.raise_exception;
401 END IF;
402
403 RETURN;
404
405 END lock_row;
406
407
408 PROCEDURE update_row (
409 x_rowid IN VARCHAR2,
410 x_exam_scores_id IN NUMBER,
411 x_ref_code_type IN VARCHAR2,
412 x_exam_level IN VARCHAR2,
413 x_points IN VARCHAR2,
414 x_grades IN VARCHAR2,
415 x_mode IN VARCHAR2
416 ) AS
417 /*
418 || Created By : rgopalan
419 || Created On : 13-NOV-2001
420 || Purpose : Handles the UPDATE DML logic for the table.
421 || Known limitations, enhancements or remarks :
422 || Change History :
423 || Who When What
424 || (reverse chronological order - newest change first)
425 */
426 x_last_update_date DATE ;
427 x_last_updated_by NUMBER;
428 x_last_update_login NUMBER;
429
430 BEGIN
431
432 x_last_update_date := SYSDATE;
433 IF (X_MODE = 'I') THEN
434 x_last_updated_by := 1;
435 x_last_update_login := 0;
436 ELSIF (x_mode = 'R') THEN
437 x_last_updated_by := fnd_global.user_id;
438 IF x_last_updated_by IS NULL THEN
439 x_last_updated_by := -1;
440 END IF;
441 x_last_update_login := fnd_global.login_id;
442 IF (x_last_update_login IS NULL) THEN
443 x_last_update_login := -1;
444 END IF;
445 ELSE
446 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
447 igs_ge_msg_stack.add;
448 app_exception.raise_exception;
449 END IF;
450
451 before_dml(
452 p_action => 'UPDATE',
453 x_rowid => x_rowid,
454 x_exam_scores_id => x_exam_scores_id,
455 x_ref_code_type => x_ref_code_type,
456 x_exam_level => x_exam_level,
457 x_points => x_points,
458 x_grades => x_grades,
459 x_creation_date => x_last_update_date,
460 x_created_by => x_last_updated_by,
461 x_last_update_date => x_last_update_date,
462 x_last_updated_by => x_last_updated_by,
463 x_last_update_login => x_last_update_login
464 );
465
466 UPDATE igs_uc_exam_scores
467 SET
468 ref_code_type = new_references.ref_code_type,
469 exam_level = new_references.exam_level,
470 points = new_references.points,
471 grades = new_references.grades,
472 last_update_date = x_last_update_date,
473 last_updated_by = x_last_updated_by,
474 last_update_login = x_last_update_login
475 WHERE rowid = x_rowid;
476
477 IF (SQL%NOTFOUND) THEN
478 RAISE NO_DATA_FOUND;
479 END IF;
480
481 END update_row;
482
483
484 PROCEDURE add_row (
485 x_rowid IN OUT NOCOPY VARCHAR2,
486 x_exam_scores_id IN OUT NOCOPY NUMBER,
487 x_ref_code_type IN VARCHAR2,
488 x_exam_level IN VARCHAR2,
489 x_points IN VARCHAR2,
490 x_grades IN VARCHAR2,
491 x_mode IN VARCHAR2
492 ) AS
493 /*
494 || Created By : rgopalan
495 || Created On : 13-NOV-2001
496 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
497 || Known limitations, enhancements or remarks :
498 || Change History :
499 || Who When What
500 || (reverse chronological order - newest change first)
501 */
502 CURSOR c1 IS
503 SELECT rowid
504 FROM igs_uc_exam_scores
505 WHERE exam_scores_id = x_exam_scores_id;
506
507 BEGIN
508
509 OPEN c1;
510 FETCH c1 INTO x_rowid;
511 IF (c1%NOTFOUND) THEN
512 CLOSE c1;
513
514 insert_row (
515 x_rowid,
516 x_exam_scores_id,
517 x_ref_code_type,
518 x_exam_level,
519 x_points,
520 x_grades,
521 x_mode
522 );
523 RETURN;
524 END IF;
525 CLOSE c1;
526
527 update_row (
528 x_rowid,
529 x_exam_scores_id,
530 x_ref_code_type,
531 x_exam_level,
532 x_points,
533 x_grades,
534 x_mode
535 );
536
537 END add_row;
538
539
540 PROCEDURE delete_row (
541 x_rowid IN VARCHAR2
542 ) AS
543 /*
544 || Created By : rgopalan
545 || Created On : 13-NOV-2001
546 || Purpose : Handles the DELETE DML logic for the table.
547 || Known limitations, enhancements or remarks :
548 || Change History :
549 || Who When What
550 || (reverse chronological order - newest change first)
551 */
552 BEGIN
553
554 before_dml (
555 p_action => 'DELETE',
556 x_rowid => x_rowid
557 );
558
559 DELETE FROM igs_uc_exam_scores
560 WHERE rowid = x_rowid;
561
562 IF (SQL%NOTFOUND) THEN
563 RAISE NO_DATA_FOUND;
564 END IF;
565
566 END delete_row;
567
568
569 END igs_uc_exam_scores_pkg;
[Home] [Help]