[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_VAH_CONF_PKG
Source
1 PACKAGE BODY igs_as_vah_conf_pkg AS
2 /* $Header: IGSDI77B.pls 115.2 2003/12/08 10:07:19 ijeddy noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_vah_conf%ROWTYPE;
6 new_references igs_as_vah_conf%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_configuration_id IN NUMBER,
12 x_course_type IN VARCHAR2,
13 x_display_order_flag IN VARCHAR2,
14 x_start_note_flag IN VARCHAR2,
15 x_program_region_flag IN VARCHAR2,
16 x_test_score_flag IN VARCHAR2,
17 x_sum_adv_stnd_flag IN VARCHAR2,
18 x_admission_note_flag IN VARCHAR2,
19 x_term_region_flag IN VARCHAR2,
20 x_unit_details_flag IN VARCHAR2,
21 x_unit_note_flag IN VARCHAR2,
22 x_adv_stnd_unit_flag IN VARCHAR2,
23 x_adv_stnd_unit_level_flag IN VARCHAR2,
24 x_statistics_flag IN VARCHAR2,
25 x_class_rank_flag IN VARCHAR2,
26 x_intermission_flag IN VARCHAR2,
27 x_special_req_flag IN VARCHAR2,
28 x_period_note_flag IN VARCHAR2,
29 x_unit_set_flag IN VARCHAR2,
30 x_awards_flag IN VARCHAR2,
31 x_prog_completion_flag IN VARCHAR2,
32 x_degree_note_flag IN VARCHAR2,
33 x_end_note_flag IN VARCHAR2,
34 x_creation_date IN DATE,
35 x_created_by IN NUMBER,
36 x_last_update_date IN DATE,
37 x_last_updated_by IN NUMBER,
38 x_last_update_login IN NUMBER ,
39 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
40 ) AS
41 /*
42 || Created By : [email protected]
43 || Created On : 18-SEP-2003
44 || Purpose : Initialises the Old and New references for the columns of the table.
45 || Known limitations, enhancements or remarks :
46 || Change History :
47 || Who When What
48 || (reverse chronological order - newest change first)
49 */
50
51 CURSOR cur_old_ref_values IS
52 SELECT *
53 FROM igs_as_vah_conf
54 WHERE rowid = x_rowid;
55
56 BEGIN
57
58 l_rowid := x_rowid;
59
60 -- Code for setting the Old and New Reference Values.
61 -- Populate Old Values.
62 OPEN cur_old_ref_values;
63 FETCH cur_old_ref_values INTO old_references;
64 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
65 CLOSE cur_old_ref_values;
66 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
67 igs_ge_msg_stack.add;
68 app_exception.raise_exception;
69 RETURN;
70 END IF;
71 CLOSE cur_old_ref_values;
72
73 -- Populate New Values.
74 new_references.configuration_id := x_configuration_id;
75 new_references.course_type := x_course_type;
76 new_references.display_order_flag := x_display_order_flag;
77 new_references.start_note_flag := x_start_note_flag;
78 new_references.program_region_flag := x_program_region_flag;
79 new_references.test_score_flag := x_test_score_flag;
80 new_references.sum_adv_stnd_flag := x_sum_adv_stnd_flag;
81 new_references.admission_note_flag := x_admission_note_flag;
82 new_references.term_region_flag := x_term_region_flag;
83 new_references.unit_details_flag := x_unit_details_flag;
84 new_references.unit_note_flag := x_unit_note_flag;
85 new_references.adv_stnd_unit_flag := x_adv_stnd_unit_flag;
86 new_references.adv_stnd_unit_level_flag := x_adv_stnd_unit_level_flag;
87 new_references.statistics_flag := x_statistics_flag;
88 new_references.class_rank_flag := x_class_rank_flag;
89 new_references.intermission_flag := x_intermission_flag;
90 new_references.special_req_flag := x_special_req_flag;
91 new_references.period_note_flag := x_period_note_flag;
92 new_references.unit_set_flag := x_unit_set_flag;
93 new_references.awards_flag := x_awards_flag;
94 new_references.prog_completion_flag := x_prog_completion_flag;
95 new_references.degree_note_flag := x_degree_note_flag;
96 new_references.end_note_flag := x_end_note_flag;
97
98 new_references.unt_lvl_marks_flag := x_unt_lvl_marks_flag;
99
100 IF (p_action = 'UPDATE') THEN
101 new_references.creation_date := old_references.creation_date;
102 new_references.created_by := old_references.created_by;
103 ELSE
104 new_references.creation_date := x_creation_date;
105 new_references.created_by := x_created_by;
106 END IF;
107
108 new_references.last_update_date := x_last_update_date;
109 new_references.last_updated_by := x_last_updated_by;
110 new_references.last_update_login := x_last_update_login;
111
112 END set_column_values;
113
114
115 PROCEDURE check_uniqueness AS
116 /*
117 || Created By : [email protected]
118 || Created On : 18-SEP-2003
119 || Purpose : Initialises the Old and New references for the columns of the table.
120 || Known limitations, enhancements or remarks :
121 || Change History :
122 || Who When What
123 || (reverse chronological order - newest change first)
124 */
125 BEGIN
126 IF (get_uk_for_validation (
127 new_references.course_type
128 )
129 ) THEN
130 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
131 igs_ge_msg_stack.add;
132 app_exception.raise_exception;
133 END IF;
134 END check_uniqueness;
135
136
137 PROCEDURE check_parent_existance AS
138 /*
139 || Created By : [email protected]
140 || Created On : 18-SEP-2003
141 || Purpose : Checks for the existance of Parent records.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149 IF (((old_references.course_type = new_references.course_type)) OR
150 ((new_references.course_type IS NULL))) THEN
151 NULL;
152 ELSIF NOT igs_ps_type_pkg.get_pk_for_validation (
153 new_references.course_type
154 ) THEN
155 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
156 igs_ge_msg_stack.add;
157 app_exception.raise_exception;
158 END IF;
159
160 END check_parent_existance;
161
162
163 FUNCTION get_pk_for_validation (
164 x_configuration_id IN NUMBER
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By : [email protected]
168 || Created On : 18-SEP-2003
169 || Purpose : Validates the Primary Key of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT ROWID
177 FROM igs_as_vah_conf
178 WHERE configuration_id = x_configuration_id
179 FOR UPDATE NOWAIT;
180
181 lv_rowid cur_rowid%RowType;
182
183 BEGIN
184
185 OPEN cur_rowid;
186 FETCH cur_rowid INTO lv_rowid;
187 IF (cur_rowid%FOUND) THEN
188 CLOSE cur_rowid;
189 RETURN(TRUE);
190 ELSE
191 CLOSE cur_rowid;
192 RETURN(FALSE);
193 END IF;
194
195 END get_pk_for_validation;
196
197 FUNCTION get_uk_for_validation (
198 x_course_type IN VARCHAR2
199 ) RETURN BOOLEAN AS
200 /*
201 || Created By : [email protected]
202 || Created On : 18-SEP-2003
203 || Purpose : Validates the Primary 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_as_vah_conf
212 WHERE ((course_type = x_course_type) OR (course_type IS NULL AND x_course_type IS NULL))
213 AND ((l_rowid IS NULL) OR (ROWID <> l_rowid));
214 lv_rowid cur_rowid%RowType;
215 BEGIN
216 OPEN cur_rowid;
217 FETCH cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 CLOSE cur_rowid;
220 RETURN(TRUE);
221 ELSE
222 CLOSE cur_rowid;
223 RETURN(FALSE);
224 END IF;
225 END get_uk_for_validation;
226
227 PROCEDURE get_fk_igs_ps_type (
228 x_course_type IN VARCHAR2
229 ) AS
230 /*
231 || Created By : [email protected]
232 || Created On : 18-SEP-2003
233 || Purpose : Validates the Foreign Keys for the table.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || (reverse chronological order - newest change first)
238 */
239 CURSOR cur_rowid IS
240 SELECT rowid
241 FROM igs_as_vah_conf
242 WHERE ((course_type = x_course_type));
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 OPEN cur_rowid;
249 FETCH cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 CLOSE cur_rowid;
252 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
253 igs_ge_msg_stack.add;
254 app_exception.raise_exception;
255 RETURN;
256 END IF;
257 CLOSE cur_rowid;
258
259 END get_fk_igs_ps_type;
260
261
262 PROCEDURE before_dml (
263 p_action IN VARCHAR2,
264 x_rowid IN VARCHAR2,
265 x_configuration_id IN NUMBER,
266 x_course_type IN VARCHAR2,
267 x_display_order_flag IN VARCHAR2,
268 x_start_note_flag IN VARCHAR2,
269 x_program_region_flag IN VARCHAR2,
270 x_test_score_flag IN VARCHAR2,
271 x_sum_adv_stnd_flag IN VARCHAR2,
272 x_admission_note_flag IN VARCHAR2,
273 x_term_region_flag IN VARCHAR2,
274 x_unit_details_flag IN VARCHAR2,
275 x_unit_note_flag IN VARCHAR2,
276 x_adv_stnd_unit_flag IN VARCHAR2,
277 x_adv_stnd_unit_level_flag IN VARCHAR2,
278 x_statistics_flag IN VARCHAR2,
279 x_class_rank_flag IN VARCHAR2,
280 x_intermission_flag IN VARCHAR2,
281 x_special_req_flag IN VARCHAR2,
282 x_period_note_flag IN VARCHAR2,
283 x_unit_set_flag IN VARCHAR2,
284 x_awards_flag IN VARCHAR2,
285 x_prog_completion_flag IN VARCHAR2,
286 x_degree_note_flag IN VARCHAR2,
287 x_end_note_flag IN VARCHAR2,
288 x_creation_date IN DATE,
289 x_created_by IN NUMBER,
290 x_last_update_date IN DATE,
294 ) AS
291 x_last_updated_by IN NUMBER,
292 x_last_update_login IN NUMBER ,
293 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
295 /*
296 || Created By : [email protected]
297 || Created On : 18-SEP-2003
298 || Purpose : Initialises the columns, Checks Constraints, Calls the
299 || Trigger Handlers for the table, before any DML operation.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 BEGIN
306
307 set_column_values (
308 p_action,
309 x_rowid,
310 x_configuration_id,
311 x_course_type,
312 x_display_order_flag,
313 x_start_note_flag,
314 x_program_region_flag,
315 x_test_score_flag,
316 x_sum_adv_stnd_flag,
317 x_admission_note_flag,
318 x_term_region_flag,
319 x_unit_details_flag,
320 x_unit_note_flag,
321 x_adv_stnd_unit_flag,
322 x_adv_stnd_unit_level_flag,
323 x_statistics_flag,
324 x_class_rank_flag,
325 x_intermission_flag,
326 x_special_req_flag,
327 x_period_note_flag,
328 x_unit_set_flag,
329 x_awards_flag,
330 x_prog_completion_flag,
331 x_degree_note_flag,
332 x_end_note_flag,
333 x_creation_date,
334 x_created_by,
335 x_last_update_date,
336 x_last_updated_by,
337 x_last_update_login ,
338 x_unt_lvl_marks_flag
339 );
340
341 IF (p_action = 'INSERT') THEN
342 -- Call all the procedures related to Before Insert.
343 IF ( get_pk_for_validation(
344 new_references.configuration_id
345 )
346 ) THEN
347 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
348 igs_ge_msg_stack.add;
349 app_exception.raise_exception;
350 END IF;
351 check_uniqueness;
352 check_parent_existance;
353 ELSIF (p_action = 'UPDATE') THEN
354 -- Call all the procedures related to Before Update.
355 check_uniqueness;
356 check_parent_existance;
357 ELSIF (p_action = 'VALIDATE_INSERT') THEN
358 -- Call all the procedures related to Before Insert.
359 IF ( get_pk_for_validation (
360 new_references.configuration_id
361 )
362 ) THEN
363 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
364 igs_ge_msg_stack.add;
365 app_exception.raise_exception;
366 END IF;
367 check_uniqueness;
368 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
369 -- Call all the procedures related to Before Update.
370 check_uniqueness;
371 END IF;
372
373 IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
374 l_rowid := NULL;
375 END IF;
376
377 END before_dml;
378
379
380 PROCEDURE insert_row (
381 x_rowid IN OUT NOCOPY VARCHAR2,
382 x_configuration_id IN OUT NOCOPY NUMBER,
383 x_course_type IN VARCHAR2,
384 x_display_order_flag IN VARCHAR2,
385 x_start_note_flag IN VARCHAR2,
386 x_program_region_flag IN VARCHAR2,
387 x_test_score_flag IN VARCHAR2,
388 x_sum_adv_stnd_flag IN VARCHAR2,
389 x_admission_note_flag IN VARCHAR2,
390 x_term_region_flag IN VARCHAR2,
391 x_unit_details_flag IN VARCHAR2,
392 x_unit_note_flag IN VARCHAR2,
393 x_adv_stnd_unit_flag IN VARCHAR2,
394 x_adv_stnd_unit_level_flag IN VARCHAR2,
395 x_statistics_flag IN VARCHAR2,
396 x_class_rank_flag IN VARCHAR2,
397 x_intermission_flag IN VARCHAR2,
398 x_special_req_flag IN VARCHAR2,
399 x_period_note_flag IN VARCHAR2,
400 x_unit_set_flag IN VARCHAR2,
401 x_awards_flag IN VARCHAR2,
402 x_prog_completion_flag IN VARCHAR2,
403 x_degree_note_flag IN VARCHAR2,
404 x_end_note_flag IN VARCHAR2,
405 x_mode IN VARCHAR2 ,
406 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
407 ) AS
408 /*
409 || Created By : [email protected]
410 || Created On : 18-SEP-2003
411 || Purpose : Handles the INSERT DML logic for the table.
412 || Known limitations, enhancements or remarks :
413 || Change History :
414 || Who When What
415 || (reverse chronological order - newest change first)
416 */
417
418 x_last_update_date DATE;
419 x_last_updated_by NUMBER;
420 x_last_update_login NUMBER;
421
422 BEGIN
423
424 x_last_update_date := SYSDATE;
425 IF (x_mode = 'I') THEN
426 x_last_updated_by := 1;
427 x_last_update_login := 0;
428 ELSIF (x_mode = 'R') THEN
429 x_last_updated_by := fnd_global.user_id;
430 IF (x_last_updated_by IS NULL) THEN
431 x_last_updated_by := -1;
432 END IF;
433 x_last_update_login := fnd_global.login_id;
434 IF (x_last_update_login IS NULL) THEN
435 x_last_update_login := -1;
436 END IF;
437 ELSE
441 app_exception.raise_exception;
438 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
439 fnd_message.set_token ('ROUTINE', 'IGS_AS_VAH_CONF_PKG.INSERT_ROW');
440 igs_ge_msg_stack.add;
442 END IF;
443
444 x_configuration_id := NULL;
445
446 before_dml(
447 p_action => 'INSERT',
448 x_rowid => x_rowid,
449 x_configuration_id => x_configuration_id,
450 x_course_type => x_course_type,
451 x_display_order_flag => x_display_order_flag,
452 x_start_note_flag => x_start_note_flag,
453 x_program_region_flag => x_program_region_flag,
454 x_test_score_flag => x_test_score_flag,
455 x_sum_adv_stnd_flag => x_sum_adv_stnd_flag,
456 x_admission_note_flag => x_admission_note_flag,
457 x_term_region_flag => x_term_region_flag,
458 x_unit_details_flag => x_unit_details_flag,
459 x_unit_note_flag => x_unit_note_flag,
460 x_adv_stnd_unit_flag => x_adv_stnd_unit_flag,
461 x_adv_stnd_unit_level_flag => x_adv_stnd_unit_level_flag,
462 x_statistics_flag => x_statistics_flag,
463 x_class_rank_flag => x_class_rank_flag,
464 x_intermission_flag => x_intermission_flag,
465 x_special_req_flag => x_special_req_flag,
466 x_period_note_flag => x_period_note_flag,
467 x_unit_set_flag => x_unit_set_flag,
468 x_awards_flag => x_awards_flag,
469 x_prog_completion_flag => x_prog_completion_flag,
470 x_degree_note_flag => x_degree_note_flag,
471 x_end_note_flag => x_end_note_flag,
472 x_creation_date => x_last_update_date,
473 x_created_by => x_last_updated_by,
474 x_last_update_date => x_last_update_date,
475 x_last_updated_by => x_last_updated_by,
476 x_last_update_login => x_last_update_login ,
477 x_unt_lvl_marks_flag => x_unt_lvl_marks_flag
478 );
479
480 INSERT INTO igs_as_vah_conf (
481 configuration_id,
482 course_type,
483 display_order_flag,
484 start_note_flag,
485 program_region_flag,
486 test_score_flag,
487 sum_adv_stnd_flag,
488 admission_note_flag,
489 term_region_flag,
490 unit_details_flag,
491 unit_note_flag,
492 adv_stnd_unit_flag,
493 adv_stnd_unit_level_flag,
494 statistics_flag,
495 class_rank_flag,
496 intermission_flag,
497 special_req_flag,
498 period_note_flag,
499 unit_set_flag,
500 awards_flag,
501 prog_completion_flag,
502 degree_note_flag,
503 end_note_flag,
504 creation_date,
505 created_by,
506 last_update_date,
507 last_updated_by,
508 last_update_login,
509 unt_lvl_marks_flag
510 ) VALUES (
511 igs_as_vah_conf_s.NEXTVAL,
512 new_references.course_type,
513 new_references.display_order_flag,
514 new_references.start_note_flag,
515 new_references.program_region_flag,
516 new_references.test_score_flag,
517 new_references.sum_adv_stnd_flag,
518 new_references.admission_note_flag,
519 new_references.term_region_flag,
520 new_references.unit_details_flag,
521 new_references.unit_note_flag,
522 new_references.adv_stnd_unit_flag,
523 new_references.adv_stnd_unit_level_flag,
524 new_references.statistics_flag,
525 new_references.class_rank_flag,
526 new_references.intermission_flag,
527 new_references.special_req_flag,
528 new_references.period_note_flag,
529 new_references.unit_set_flag,
530 new_references.awards_flag,
531 new_references.prog_completion_flag,
532 new_references.degree_note_flag,
533 new_references.end_note_flag,
534 x_last_update_date,
535 x_last_updated_by,
536 x_last_update_date,
537 x_last_updated_by,
538 x_last_update_login,
539 x_unt_lvl_marks_flag
540 ) RETURNING ROWID, configuration_id INTO x_rowid, x_configuration_id;
541
542 l_rowid := NULL;
543
544 END insert_row;
545
546
547 PROCEDURE lock_row (
548 x_rowid IN VARCHAR2,
549 x_configuration_id IN NUMBER,
550 x_course_type IN VARCHAR2,
551 x_display_order_flag IN VARCHAR2,
552 x_start_note_flag IN VARCHAR2,
553 x_program_region_flag IN VARCHAR2,
554 x_test_score_flag IN VARCHAR2,
555 x_sum_adv_stnd_flag IN VARCHAR2,
556 x_admission_note_flag IN VARCHAR2,
557 x_term_region_flag IN VARCHAR2,
558 x_unit_details_flag IN VARCHAR2,
559 x_unit_note_flag IN VARCHAR2,
560 x_adv_stnd_unit_flag IN VARCHAR2,
561 x_adv_stnd_unit_level_flag IN VARCHAR2,
562 x_statistics_flag IN VARCHAR2,
563 x_class_rank_flag IN VARCHAR2,
564 x_intermission_flag IN VARCHAR2,
565 x_special_req_flag IN VARCHAR2,
566 x_period_note_flag IN VARCHAR2,
567 x_unit_set_flag IN VARCHAR2,
571 x_end_note_flag IN VARCHAR2,
568 x_awards_flag IN VARCHAR2,
569 x_prog_completion_flag IN VARCHAR2,
570 x_degree_note_flag IN VARCHAR2,
572 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
573 ) AS
574 /*
575 || Created By : [email protected]
576 || Created On : 18-SEP-2003
577 || Purpose : Handles the LOCK mechanism for the table.
578 || Known limitations, enhancements or remarks :
579 || Change History :
580 || Who When What
581 || (reverse chronological order - newest change first)
582 */
583 CURSOR c1 IS
584 SELECT
585 course_type,
586 display_order_flag,
587 start_note_flag,
588 program_region_flag,
589 test_score_flag,
590 sum_adv_stnd_flag,
591 admission_note_flag,
592 term_region_flag,
593 unit_details_flag,
594 unit_note_flag,
595 adv_stnd_unit_flag,
596 adv_stnd_unit_level_flag,
597 statistics_flag,
598 class_rank_flag,
599 intermission_flag,
600 special_req_flag,
601 period_note_flag,
602 unit_set_flag,
603 awards_flag,
604 prog_completion_flag,
605 degree_note_flag,
606 end_note_flag,
607 unt_lvl_marks_flag
608 FROM igs_as_vah_conf
609 WHERE rowid = x_rowid
610 FOR UPDATE NOWAIT;
611
612 tlinfo c1%ROWTYPE;
613
614 BEGIN
615
616 OPEN c1;
617 FETCH c1 INTO tlinfo;
618 IF (c1%notfound) THEN
619 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
620 igs_ge_msg_stack.add;
621 CLOSE c1;
622 app_exception.raise_exception;
623 RETURN;
624 END IF;
625 CLOSE c1;
626
627 IF (
628 ((tlinfo.course_type = x_course_type) OR ((tlinfo.course_type IS NULL) AND (X_course_type IS NULL)))
629 AND (tlinfo.display_order_flag = x_display_order_flag)
630 AND (tlinfo.start_note_flag = x_start_note_flag)
631 AND (tlinfo.program_region_flag = x_program_region_flag)
632 AND (tlinfo.test_score_flag = x_test_score_flag)
633 AND (tlinfo.sum_adv_stnd_flag = x_sum_adv_stnd_flag)
634 AND (tlinfo.admission_note_flag = x_admission_note_flag)
635 AND (tlinfo.term_region_flag = x_term_region_flag)
636 AND (tlinfo.unit_details_flag = x_unit_details_flag)
637 AND (tlinfo.unit_note_flag = x_unit_note_flag)
638 AND (tlinfo.adv_stnd_unit_flag = x_adv_stnd_unit_flag)
639 AND (tlinfo.adv_stnd_unit_level_flag = x_adv_stnd_unit_level_flag)
640 AND (tlinfo.statistics_flag = x_statistics_flag)
641 AND (tlinfo.class_rank_flag = x_class_rank_flag)
642 AND (tlinfo.intermission_flag = x_intermission_flag)
643 AND (tlinfo.special_req_flag = x_special_req_flag)
644 AND (tlinfo.period_note_flag = x_period_note_flag)
645 AND (tlinfo.unit_set_flag = x_unit_set_flag)
646 AND (tlinfo.awards_flag = x_awards_flag)
647 AND (tlinfo.prog_completion_flag = x_prog_completion_flag)
648 AND (tlinfo.degree_note_flag = x_degree_note_flag)
649 AND (tlinfo.end_note_flag = x_end_note_flag)
650 AND ((tlinfo.unt_lvl_marks_flag = x_unt_lvl_marks_flag) OR ((tlinfo.unt_lvl_marks_flag IS NULL) AND (X_unt_lvl_marks_flag IS NULL)))
651 )
652 THEN
653 NULL;
654 ELSE
655 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
656 igs_ge_msg_stack.add;
657 app_exception.raise_exception;
658 END IF;
659
660 RETURN;
661
662 END lock_row;
663
664
665 PROCEDURE update_row (
666 x_rowid IN VARCHAR2,
667 x_configuration_id IN NUMBER,
668 x_course_type IN VARCHAR2,
669 x_display_order_flag IN VARCHAR2,
670 x_start_note_flag IN VARCHAR2,
671 x_program_region_flag IN VARCHAR2,
672 x_test_score_flag IN VARCHAR2,
673 x_sum_adv_stnd_flag IN VARCHAR2,
674 x_admission_note_flag IN VARCHAR2,
675 x_term_region_flag IN VARCHAR2,
676 x_unit_details_flag IN VARCHAR2,
677 x_unit_note_flag IN VARCHAR2,
678 x_adv_stnd_unit_flag IN VARCHAR2,
679 x_adv_stnd_unit_level_flag IN VARCHAR2,
680 x_statistics_flag IN VARCHAR2,
681 x_class_rank_flag IN VARCHAR2,
682 x_intermission_flag IN VARCHAR2,
683 x_special_req_flag IN VARCHAR2,
684 x_period_note_flag IN VARCHAR2,
685 x_unit_set_flag IN VARCHAR2,
686 x_awards_flag IN VARCHAR2,
687 x_prog_completion_flag IN VARCHAR2,
688 x_degree_note_flag IN VARCHAR2,
689 x_end_note_flag IN VARCHAR2,
690 x_mode IN VARCHAR2,
691 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
692 ) AS
693 /*
694 || Created By : [email protected]
695 || Created On : 18-SEP-2003
696 || Purpose : Handles the UPDATE DML logic for the table.
697 || Known limitations, enhancements or remarks :
698 || Change History :
699 || Who When What
700 || (reverse chronological order - newest change first)
701 */
702 x_last_update_date DATE ;
706 BEGIN
703 x_last_updated_by NUMBER;
704 x_last_update_login NUMBER;
705
707
708 x_last_update_date := SYSDATE;
709 IF (X_MODE = 'I') THEN
710 x_last_updated_by := 1;
711 x_last_update_login := 0;
712 ELSIF (x_mode = 'R') THEN
713 x_last_updated_by := fnd_global.user_id;
714 IF x_last_updated_by IS NULL THEN
715 x_last_updated_by := -1;
716 END IF;
717 x_last_update_login := fnd_global.login_id;
718 IF (x_last_update_login IS NULL) THEN
719 x_last_update_login := -1;
720 END IF;
721 ELSE
722 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
723 fnd_message.set_token ('ROUTINE', 'IGS_AS_VAH_CONF_PKG.UPDATE_ROW');
724 igs_ge_msg_stack.add;
725 app_exception.raise_exception;
726 END IF;
727
728 before_dml(
729 p_action => 'UPDATE',
730 x_rowid => x_rowid,
731 x_configuration_id => x_configuration_id,
732 x_course_type => x_course_type,
733 x_display_order_flag => x_display_order_flag,
734 x_start_note_flag => x_start_note_flag,
735 x_program_region_flag => x_program_region_flag,
736 x_test_score_flag => x_test_score_flag,
737 x_sum_adv_stnd_flag => x_sum_adv_stnd_flag,
738 x_admission_note_flag => x_admission_note_flag,
739 x_term_region_flag => x_term_region_flag,
740 x_unit_details_flag => x_unit_details_flag,
741 x_unit_note_flag => x_unit_note_flag,
742 x_adv_stnd_unit_flag => x_adv_stnd_unit_flag,
743 x_adv_stnd_unit_level_flag => x_adv_stnd_unit_level_flag,
744 x_statistics_flag => x_statistics_flag,
745 x_class_rank_flag => x_class_rank_flag,
746 x_intermission_flag => x_intermission_flag,
747 x_special_req_flag => x_special_req_flag,
748 x_period_note_flag => x_period_note_flag,
749 x_unit_set_flag => x_unit_set_flag,
750 x_awards_flag => x_awards_flag,
751 x_prog_completion_flag => x_prog_completion_flag,
752 x_degree_note_flag => x_degree_note_flag,
753 x_end_note_flag => x_end_note_flag,
754 x_creation_date => x_last_update_date,
755 x_created_by => x_last_updated_by,
756 x_last_update_date => x_last_update_date,
757 x_last_updated_by => x_last_updated_by,
758 x_last_update_login => x_last_update_login,
759 x_unt_lvl_marks_flag => x_unt_lvl_marks_flag
760 );
761
762 UPDATE igs_as_vah_conf
763 SET
764 course_type = new_references.course_type,
765 display_order_flag = new_references.display_order_flag,
766 start_note_flag = new_references.start_note_flag,
767 program_region_flag = new_references.program_region_flag,
768 test_score_flag = new_references.test_score_flag,
769 sum_adv_stnd_flag = new_references.sum_adv_stnd_flag,
770 admission_note_flag = new_references.admission_note_flag,
771 term_region_flag = new_references.term_region_flag,
772 unit_details_flag = new_references.unit_details_flag,
773 unit_note_flag = new_references.unit_note_flag,
774 adv_stnd_unit_flag = new_references.adv_stnd_unit_flag,
775 adv_stnd_unit_level_flag = new_references.adv_stnd_unit_level_flag,
776 statistics_flag = new_references.statistics_flag,
777 class_rank_flag = new_references.class_rank_flag,
778 intermission_flag = new_references.intermission_flag,
779 special_req_flag = new_references.special_req_flag,
780 period_note_flag = new_references.period_note_flag,
781 unit_set_flag = new_references.unit_set_flag,
782 awards_flag = new_references.awards_flag,
783 prog_completion_flag = new_references.prog_completion_flag,
784 degree_note_flag = new_references.degree_note_flag,
785 end_note_flag = new_references.end_note_flag,
786 last_update_date = x_last_update_date,
787 last_updated_by = x_last_updated_by,
788 last_update_login = x_last_update_login ,
789 unt_lvl_marks_flag = new_references.unt_lvl_marks_flag
790 WHERE rowid = x_rowid;
791
792 IF (SQL%NOTFOUND) THEN
793 RAISE NO_DATA_FOUND;
794 END IF;
795
796 l_rowid := NULL;
797
798 END update_row;
799
800
801 PROCEDURE add_row (
802 x_rowid IN OUT NOCOPY VARCHAR2,
803 x_configuration_id IN OUT NOCOPY NUMBER,
804 x_course_type IN VARCHAR2,
805 x_display_order_flag IN VARCHAR2,
806 x_start_note_flag IN VARCHAR2,
807 x_program_region_flag IN VARCHAR2,
808 x_test_score_flag IN VARCHAR2,
809 x_sum_adv_stnd_flag IN VARCHAR2,
810 x_admission_note_flag IN VARCHAR2,
811 x_term_region_flag IN VARCHAR2,
812 x_unit_details_flag IN VARCHAR2,
813 x_unit_note_flag IN VARCHAR2,
814 x_adv_stnd_unit_flag IN VARCHAR2,
815 x_adv_stnd_unit_level_flag IN VARCHAR2,
816 x_statistics_flag IN VARCHAR2,
817 x_class_rank_flag IN VARCHAR2,
818 x_intermission_flag IN VARCHAR2,
819 x_special_req_flag IN VARCHAR2,
820 x_period_note_flag IN VARCHAR2,
821 x_unit_set_flag IN VARCHAR2,
822 x_awards_flag IN VARCHAR2,
823 x_prog_completion_flag IN VARCHAR2,
824 x_degree_note_flag IN VARCHAR2,
825 x_end_note_flag IN VARCHAR2,
826 x_mode IN VARCHAR2,
827 x_unt_lvl_marks_flag IN VARCHAR2 DEFAULT NULL
828 ) AS
829 /*
830 || Created By : [email protected]
831 || Created On : 18-SEP-2003
832 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
833 || Known limitations, enhancements or remarks :
834 || Change History :
835 || Who When What
836 || (reverse chronological order - newest change first)
837 */
838 CURSOR c1 IS
839 SELECT rowid
840 FROM igs_as_vah_conf
841 WHERE configuration_id = x_configuration_id;
842
843 BEGIN
844
845 OPEN c1;
846 FETCH c1 INTO x_rowid;
847 IF (c1%NOTFOUND) THEN
848 CLOSE c1;
849
850 insert_row (
851 x_rowid,
852 x_configuration_id,
853 x_course_type,
854 x_display_order_flag,
855 x_start_note_flag,
856 x_program_region_flag,
857 x_test_score_flag,
858 x_sum_adv_stnd_flag,
859 x_admission_note_flag,
860 x_term_region_flag,
861 x_unit_details_flag,
862 x_unit_note_flag,
863 x_adv_stnd_unit_flag,
864 x_adv_stnd_unit_level_flag,
865 x_statistics_flag,
866 x_class_rank_flag,
867 x_intermission_flag,
868 x_special_req_flag,
869 x_period_note_flag,
870 x_unit_set_flag,
871 x_awards_flag,
872 x_prog_completion_flag,
873 x_degree_note_flag,
874 x_end_note_flag,
875 x_mode ,
876 x_unt_lvl_marks_flag
877 );
878 RETURN;
879 END IF;
880 CLOSE c1;
881
882 update_row (
883 x_rowid,
884 x_configuration_id,
885 x_course_type,
886 x_display_order_flag,
887 x_start_note_flag,
888 x_program_region_flag,
889 x_test_score_flag,
890 x_sum_adv_stnd_flag,
891 x_admission_note_flag,
892 x_term_region_flag,
893 x_unit_details_flag,
894 x_unit_note_flag,
895 x_adv_stnd_unit_flag,
896 x_adv_stnd_unit_level_flag,
897 x_statistics_flag,
898 x_class_rank_flag,
899 x_intermission_flag,
900 x_special_req_flag,
901 x_period_note_flag,
902 x_unit_set_flag,
903 x_awards_flag,
904 x_prog_completion_flag,
905 x_degree_note_flag,
906 x_end_note_flag,
907 x_mode ,
908 x_unt_lvl_marks_flag
909 );
910
911 END add_row;
912
913
914 PROCEDURE delete_row (
915 x_rowid IN VARCHAR2
916 ) AS
917 /*
918 || Created By : [email protected]
919 || Created On : 18-SEP-2003
920 || Purpose : Handles the DELETE DML logic for the table.
921 || Known limitations, enhancements or remarks :
922 || Change History :
923 || Who When What
924 || (reverse chronological order - newest change first)
925 */
926 BEGIN
927
928 before_dml (
929 p_action => 'DELETE',
930 x_rowid => x_rowid
931 );
932
933 DELETE FROM igs_as_vah_conf
934 WHERE rowid = x_rowid;
935
936 IF (SQL%NOTFOUND) THEN
937 RAISE NO_DATA_FOUND;
938 END IF;
939
940 l_rowid := NULL;
941
942 END delete_row;
943
944
945 END igs_as_vah_conf_pkg;