1 PACKAGE BODY igs_pr_ul_mark_cnfg_pkg AS
2 /* $Header: IGSQI46B.pls 115.0 2003/11/07 10:54:54 ijeddy noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_ul_mark_cnfg%ROWTYPE;
6 new_references igs_pr_ul_mark_cnfg%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_mark_config_id IN NUMBER,
12 x_unit_level IN VARCHAR2,
13 x_course_cd IN VARCHAR2,
14 x_version_number IN NUMBER,
15 x_total_unit_level_credits IN NUMBER,
16 x_selection_method_code IN VARCHAR2,
17 x_creation_date IN DATE,
18 x_created_by IN NUMBER,
19 x_last_update_date IN DATE,
20 x_last_updated_by IN NUMBER,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 15-OCT-2003
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_pr_ul_mark_cnfg
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.mark_config_id := x_mark_config_id;
57 new_references.unit_level := x_unit_level;
58 new_references.course_cd := x_course_cd;
59 new_references.version_number := x_version_number;
60 new_references.total_unit_level_credits := x_total_unit_level_credits;
61 new_references.selection_method_code := x_selection_method_code;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE check_uniqueness AS
79 /*
80 || Created By : [email protected]
81 || Created On : 15-OCT-2003
82 || Purpose : Handles the Unique Constraint logic defined for the columns.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 BEGIN
89
90 IF ( get_uk_for_validation (
91 new_references.unit_level,
92 new_references.course_cd,
93 new_references.version_number
94 )
95 ) THEN
96 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
97 igs_ge_msg_stack.add;
98 app_exception.raise_exception;
99 END IF;
100 END check_uniqueness;
101
102 PROCEDURE check_parent_existance AS
103 /*
104 || Created By : [email protected]
105 || Created On : 15-OCT-2003
106 || Purpose : Checks for the existance of Parent records.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 BEGIN
113
114 IF (((old_references.unit_level = new_references.unit_level)) OR
115 ((new_references.unit_level IS NULL))) THEN
116 NULL;
117 ELSIF NOT igs_ps_unit_level_pkg.get_pk_for_validation (
118 new_references.unit_level
119 ) THEN
120 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
121 igs_ge_msg_stack.add;
122 app_exception.raise_exception;
123 END IF;
124
125 IF (((old_references.course_cd = new_references.course_cd) AND
126 (old_references.version_number = new_references.version_number)) OR
127 ((new_references.course_cd IS NULL) OR
128 (new_references.version_number IS NULL))) THEN
129 NULL;
130 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
131 new_references.course_cd,
132 new_references.version_number
133 ) THEN
134 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
135 igs_ge_msg_stack.add;
136 app_exception.raise_exception;
137 END IF;
138
139 END check_parent_existance;
140
141
142 PROCEDURE check_child_existance AS
143 /*
144 || Created By : [email protected]
145 || Created On : 15-OCT-2003
146 || Purpose : Checks for the existance of Child records.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 BEGIN
153
154 igs_pr_ul_mark_dtl_pkg.get_fk_igs_pr_ul_mark_cnfg (
155 old_references.mark_config_id
156 );
157
158 END check_child_existance;
159
160
161 FUNCTION get_pk_for_validation (
162 x_mark_config_id IN NUMBER
163 ) RETURN BOOLEAN AS
164 /*
165 || Created By : [email protected]
166 || Created On : 15-OCT-2003
167 || Purpose : Validates the Primary Key of the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_pr_ul_mark_cnfg
176 WHERE mark_config_id = x_mark_config_id
177 FOR UPDATE NOWAIT;
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 RETURN(TRUE);
188 ELSE
189 CLOSE cur_rowid;
190 RETURN(FALSE);
191 END IF;
192
193 END get_pk_for_validation;
194
195 FUNCTION get_uk_for_validation (
196 x_unit_level IN VARCHAR2,
197 x_course_cd IN VARCHAR2,
198 x_version_number IN NUMBER
199 ) RETURN BOOLEAN AS
200 /*
201 || Created By : [email protected]
202 || Created On : 15-OCT-2003
203 || Purpose : Validates the Unique Key of the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 CURSOR cur_rowid IS
210 SELECT rowid
211 FROM igs_pr_ul_mark_cnfg
212 WHERE (unit_level = x_unit_level)
213 AND ((course_cd = x_course_cd) OR ((course_cd IS NULL) AND (x_course_cd IS NULL)))
214 AND ((version_number = x_version_number) OR ((version_number IS NULL) AND (x_version_number IS NULL)));
215
216
217 lv_rowid cur_rowid%RowType;
218
219 BEGIN
220
221 OPEN cur_rowid;
222 FETCH cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 CLOSE cur_rowid;
225 RETURN(TRUE);
226 ELSE
227 CLOSE cur_rowid;
228 RETURN(FALSE);
229 END IF;
230
231 END get_uk_for_validation;
232
233 PROCEDURE get_fk_igs_ps_unit_level (
234 x_unit_level IN VARCHAR2
235 ) AS
236 /*
237 || Created By : [email protected]
238 || Created On : 15-OCT-2003
239 || Purpose : Validates the Foreign Keys for the table.
240 || Known limitations, enhancements or remarks :
241 || Change History :
242 || Who When What
243 || (reverse chronological order - newest change first)
244 */
245 CURSOR cur_rowid IS
246 SELECT rowid
247 FROM igs_pr_ul_mark_cnfg
248 WHERE ((unit_level = x_unit_level));
249
250 lv_rowid cur_rowid%RowType;
251
252 BEGIN
253
254 OPEN cur_rowid;
255 FETCH cur_rowid INTO lv_rowid;
256 IF (cur_rowid%FOUND) THEN
257 CLOSE cur_rowid;
258 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
259 igs_ge_msg_stack.add;
260 app_exception.raise_exception;
261 RETURN;
262 END IF;
263 CLOSE cur_rowid;
264
265 END get_fk_igs_ps_unit_level;
266
267
268 PROCEDURE get_fk_igs_ps_ver (
269 x_course_cd IN VARCHAR2,
270 x_version_number IN NUMBER
271 ) AS
272 /*
273 || Created By : [email protected]
274 || Created On : 15-OCT-2003
275 || Purpose : Validates the Foreign Keys for the table.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 CURSOR cur_rowid IS
282 SELECT rowid
283 FROM igs_pr_ul_mark_cnfg
284 WHERE ((course_cd = x_course_cd) AND
285 (version_number = x_version_number));
286
287 lv_rowid cur_rowid%RowType;
288
289 BEGIN
290
291 OPEN cur_rowid;
292 FETCH cur_rowid INTO lv_rowid;
293 IF (cur_rowid%FOUND) THEN
294 CLOSE cur_rowid;
295 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
296 igs_ge_msg_stack.add;
297 app_exception.raise_exception;
298 RETURN;
299 END IF;
300 CLOSE cur_rowid;
301
302 END get_fk_igs_ps_ver;
303
304
305 PROCEDURE before_dml (
306 p_action IN VARCHAR2,
307 x_rowid IN VARCHAR2,
308 x_mark_config_id IN NUMBER,
309 x_unit_level IN VARCHAR2,
310 x_course_cd IN VARCHAR2,
311 x_version_number IN NUMBER,
312 x_total_unit_level_credits IN NUMBER,
313 x_selection_method_code IN VARCHAR2,
314 x_creation_date IN DATE,
315 x_created_by IN NUMBER,
316 x_last_update_date IN DATE,
317 x_last_updated_by IN NUMBER,
318 x_last_update_login IN NUMBER
319 ) AS
320 /*
321 || Created By : [email protected]
322 || Created On : 15-OCT-2003
323 || Purpose : Initialises the columns, Checks Constraints, Calls the
324 || Trigger Handlers for the table, before any DML operation.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 */
330 BEGIN
331
332 set_column_values (
333 p_action,
334 x_rowid,
335 x_mark_config_id,
336 x_unit_level,
337 x_course_cd,
338 x_version_number,
339 x_total_unit_level_credits,
340 x_selection_method_code,
341 x_creation_date,
342 x_created_by,
343 x_last_update_date,
344 x_last_updated_by,
345 x_last_update_login
346 );
347
348 IF (p_action = 'INSERT') THEN
349 -- Call all the procedures related to Before Insert.
350 IF ( get_pk_for_validation(
351 new_references.mark_config_id
352 )
353 ) THEN
354 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
355 igs_ge_msg_stack.add;
356 app_exception.raise_exception;
357 END IF;
358 check_uniqueness;
359 check_parent_existance;
360 ELSIF (p_action = 'UPDATE') THEN
361 -- Call all the procedures related to Before Update.
362
363 check_parent_existance;
364 ELSIF (p_action = 'DELETE') THEN
365 -- Call all the procedures related to Before Delete.
366 check_child_existance;
367 ELSIF (p_action = 'VALIDATE_INSERT') THEN
368 -- Call all the procedures related to Before Insert.
369 IF ( get_pk_for_validation (
370 new_references.mark_config_id
371 )
372 ) THEN
373 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377 check_uniqueness;
378 ELSIF (p_action = 'VALIDATE_DELETE') THEN
379 check_child_existance;
380 END IF;
381
382 END before_dml;
383
384
385 PROCEDURE insert_row (
386 x_rowid IN OUT NOCOPY VARCHAR2,
387 x_mark_config_id IN OUT NOCOPY NUMBER,
388 x_unit_level IN VARCHAR2,
389 x_course_cd IN VARCHAR2,
390 x_version_number IN NUMBER,
391 x_total_unit_level_credits IN NUMBER,
392 x_selection_method_code IN VARCHAR2,
393 x_mode IN VARCHAR2
394 ) AS
395 /*
396 || Created By : [email protected]
397 || Created On : 15-OCT-2003
398 || Purpose : Handles the INSERT DML logic 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
405 x_last_update_date DATE;
406 x_last_updated_by NUMBER;
407 x_last_update_login NUMBER;
408
409 BEGIN
410
411 x_last_update_date := SYSDATE;
412 IF (x_mode = 'I') THEN
413 x_last_updated_by := 1;
414 x_last_update_login := 0;
415 ELSIF (x_mode = 'R') THEN
416 x_last_updated_by := fnd_global.user_id;
417 IF (x_last_updated_by IS NULL) THEN
418 x_last_updated_by := -1;
419 END IF;
420 x_last_update_login := fnd_global.login_id;
421 IF (x_last_update_login IS NULL) THEN
422 x_last_update_login := -1;
423 END IF;
424 ELSE
425 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
426 fnd_message.set_token ('ROUTINE', 'IGS_PR_UL_MARK_CNFG_PKG.INSERT_ROW');
427 igs_ge_msg_stack.add;
428 app_exception.raise_exception;
429 END IF;
430
431 x_mark_config_id := NULL;
432
433 before_dml(
434 p_action => 'INSERT',
435 x_rowid => x_rowid,
436 x_mark_config_id => x_mark_config_id,
437 x_unit_level => x_unit_level,
438 x_course_cd => x_course_cd,
439 x_version_number => x_version_number,
440 x_total_unit_level_credits => x_total_unit_level_credits,
441 x_selection_method_code => x_selection_method_code,
442 x_creation_date => x_last_update_date,
443 x_created_by => x_last_updated_by,
444 x_last_update_date => x_last_update_date,
445 x_last_updated_by => x_last_updated_by,
446 x_last_update_login => x_last_update_login
447 );
448
449 INSERT INTO igs_pr_ul_mark_cnfg (
450 mark_config_id,
451 unit_level,
452 course_cd,
453 version_number,
454 total_unit_level_credits,
455 selection_method_code,
456 creation_date,
457 created_by,
458 last_update_date,
459 last_updated_by,
460 last_update_login
461 ) VALUES (
462 igs_pr_ul_mark_cnfg_s.NEXTVAL,
463 new_references.unit_level,
464 new_references.course_cd,
465 new_references.version_number,
466 new_references.total_unit_level_credits,
467 new_references.selection_method_code,
468 x_last_update_date,
469 x_last_updated_by,
470 x_last_update_date,
471 x_last_updated_by,
472 x_last_update_login
473 ) RETURNING ROWID, mark_config_id INTO x_rowid, x_mark_config_id;
474
475 END insert_row;
476
477
478 PROCEDURE lock_row (
479 x_rowid IN VARCHAR2,
480 x_mark_config_id IN NUMBER,
481 x_unit_level IN VARCHAR2,
482 x_course_cd IN VARCHAR2,
483 x_version_number IN NUMBER,
484 x_total_unit_level_credits IN NUMBER,
485 x_selection_method_code IN VARCHAR2
486 ) AS
487 /*
488 || Created By : [email protected]
489 || Created On : 15-OCT-2003
490 || Purpose : Handles the LOCK mechanism for the table.
491 || Known limitations, enhancements or remarks :
492 || Change History :
493 || Who When What
494 || (reverse chronological order - newest change first)
495 */
496 CURSOR c1 IS
497 SELECT
498 unit_level,
499 course_cd,
500 version_number,
501 total_unit_level_credits,
502 selection_method_code
503 FROM igs_pr_ul_mark_cnfg
504 WHERE rowid = x_rowid
505 FOR UPDATE NOWAIT;
506
507 tlinfo c1%ROWTYPE;
508
509 BEGIN
510
511 OPEN c1;
512 FETCH c1 INTO tlinfo;
513 IF (c1%notfound) THEN
514 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
515 igs_ge_msg_stack.add;
516 CLOSE c1;
517 app_exception.raise_exception;
518 RETURN;
519 END IF;
520 CLOSE c1;
521
522 IF (
523 (tlinfo.unit_level = x_unit_level)
524 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (x_course_cd IS NULL)))
525 AND ((tlinfo.version_number = x_version_number) OR ((tlinfo.version_number IS NULL) AND (x_version_number IS NULL)))
526 AND ((tlinfo.total_unit_level_credits = x_total_unit_level_credits) OR ((tlinfo.total_unit_level_credits IS NULL) AND (X_total_unit_level_credits IS NULL)))
527 AND (tlinfo.selection_method_code = x_selection_method_code)
528 ) THEN
529 NULL;
530 ELSE
531 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
532 igs_ge_msg_stack.add;
533 app_exception.raise_exception;
534 END IF;
535
536 RETURN;
537
538 END lock_row;
539
540
541 PROCEDURE update_row (
542 x_rowid IN VARCHAR2,
543 x_mark_config_id IN NUMBER,
544 x_unit_level IN VARCHAR2,
545 x_course_cd IN VARCHAR2,
546 x_version_number IN NUMBER,
547 x_total_unit_level_credits IN NUMBER,
548 x_selection_method_code IN VARCHAR2,
549 x_mode IN VARCHAR2
550 ) AS
551 /*
552 || Created By : [email protected]
553 || Created On : 15-OCT-2003
554 || Purpose : Handles the UPDATE DML logic for the table.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || (reverse chronological order - newest change first)
559 */
560 x_last_update_date DATE ;
561 x_last_updated_by NUMBER;
562 x_last_update_login NUMBER;
563
564 BEGIN
565
566 x_last_update_date := SYSDATE;
567 IF (X_MODE = 'I') THEN
568 x_last_updated_by := 1;
569 x_last_update_login := 0;
570 ELSIF (x_mode = 'R') THEN
571 x_last_updated_by := fnd_global.user_id;
572 IF x_last_updated_by IS NULL THEN
573 x_last_updated_by := -1;
574 END IF;
575 x_last_update_login := fnd_global.login_id;
576 IF (x_last_update_login IS NULL) THEN
577 x_last_update_login := -1;
578 END IF;
579 ELSE
580 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
581 fnd_message.set_token ('ROUTINE', 'IGS_PR_UL_MARK_CNFG_PKG.UPDATE_ROW');
582 igs_ge_msg_stack.add;
583 app_exception.raise_exception;
584 END IF;
585
586 before_dml(
587 p_action => 'UPDATE',
588 x_rowid => x_rowid,
589 x_mark_config_id => x_mark_config_id,
590 x_unit_level => x_unit_level,
591 x_course_cd => x_course_cd,
592 x_version_number => x_version_number,
593 x_total_unit_level_credits => x_total_unit_level_credits,
594 x_selection_method_code => x_selection_method_code,
595 x_creation_date => x_last_update_date,
596 x_created_by => x_last_updated_by,
597 x_last_update_date => x_last_update_date,
598 x_last_updated_by => x_last_updated_by,
599 x_last_update_login => x_last_update_login
600 );
601
602 UPDATE igs_pr_ul_mark_cnfg
603 SET
604 unit_level = new_references.unit_level,
605 course_cd = new_references.course_cd,
606 version_number = new_references.version_number,
607 total_unit_level_credits = new_references.total_unit_level_credits,
608 selection_method_code = new_references.selection_method_code,
609 last_update_date = x_last_update_date,
610 last_updated_by = x_last_updated_by,
611 last_update_login = x_last_update_login
612 WHERE rowid = x_rowid;
613
614 IF (SQL%NOTFOUND) THEN
615 RAISE NO_DATA_FOUND;
616 END IF;
617
618 END update_row;
619
620
621 PROCEDURE add_row (
622 x_rowid IN OUT NOCOPY VARCHAR2,
623 x_mark_config_id IN OUT NOCOPY NUMBER,
624 x_unit_level IN VARCHAR2,
625 x_course_cd IN VARCHAR2,
626 x_version_number IN NUMBER,
627 x_total_unit_level_credits IN NUMBER,
628 x_selection_method_code IN VARCHAR2,
629 x_mode IN VARCHAR2
630 ) AS
631 /*
632 || Created By : [email protected]
633 || Created On : 15-OCT-2003
634 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
635 || Known limitations, enhancements or remarks :
636 || Change History :
637 || Who When What
638 || (reverse chronological order - newest change first)
639 */
640 CURSOR c1 IS
641 SELECT rowid
642 FROM igs_pr_ul_mark_cnfg
643 WHERE mark_config_id = x_mark_config_id;
644
645 BEGIN
646
647 OPEN c1;
648 FETCH c1 INTO x_rowid;
649 IF (c1%NOTFOUND) THEN
650 CLOSE c1;
651
652 insert_row (
653 x_rowid,
654 x_mark_config_id,
655 x_unit_level,
656 x_course_cd,
657 x_version_number,
658 x_total_unit_level_credits,
659 x_selection_method_code,
660 x_mode
661 );
662 RETURN;
663 END IF;
664 CLOSE c1;
665
666 update_row (
667 x_rowid,
668 x_mark_config_id,
669 x_unit_level,
670 x_course_cd,
671 x_version_number,
672 x_total_unit_level_credits,
673 x_selection_method_code,
674 x_mode
675 );
676
677 END add_row;
678
679
680 PROCEDURE delete_row (
681 x_rowid IN VARCHAR2
682 ) AS
683 /*
684 || Created By : [email protected]
685 || Created On : 15-OCT-2003
686 || Purpose : Handles the DELETE DML logic for the table.
687 || Known limitations, enhancements or remarks :
688 || Change History :
689 || Who When What
690 || (reverse chronological order - newest change first)
691 */
692 BEGIN
693
694 before_dml (
695 p_action => 'DELETE',
696 x_rowid => x_rowid
697 );
698
699 DELETE FROM igs_pr_ul_mark_cnfg
700 WHERE rowid = x_rowid;
701
702 IF (SQL%NOTFOUND) THEN
703 RAISE NO_DATA_FOUND;
704 END IF;
705
706 END delete_row;
707
708
709 END igs_pr_ul_mark_cnfg_pkg;
[Home] [Help]