1 PACKAGE BODY igs_en_timeslot_stup_pkg AS
2 /* $Header: IGSEI38B.pls 115.6 2003/01/31 09:30:36 nbehera ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_en_timeslot_stup%ROWTYPE;
5 new_references igs_en_timeslot_stup%ROWTYPE;
6
7 PROCEDURE set_column_values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_sequence_number IN NUMBER DEFAULT NULL,
11 x_student_type IN VARCHAR2 DEFAULT NULL,
12 x_assign_randomly IN VARCHAR2 DEFAULT NULL,
13 x_surname_alphabet IN VARCHAR2 DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_igs_en_timeslot_stup_id IN NUMBER DEFAULT NULL,
16 x_program_type_group_cd IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL ,
22 x_org_id IN NUMBER DEFAULT NULL
23 ) AS
24
25 /*************************************************************
26 Created By :
27 Date Created By :
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32
33 (reverse chronological order - newest change first)
34 ***************************************************************/
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_EN_TIMESLOT_STUP
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.sequence_number := x_sequence_number;
60 new_references.student_type := x_student_type;
61 new_references.assign_randomly := x_assign_randomly;
62 new_references.surname_alphabet := x_surname_alphabet;
63 new_references.cal_type := x_cal_type;
64 new_references.igs_en_timeslot_stup_id := x_igs_en_timeslot_stup_id;
65 new_references.program_type_group_cd := x_program_type_group_cd;
66 IF (p_action = 'UPDATE') THEN
67 new_references.creation_date := old_references.creation_date;
68 new_references.created_by := old_references.created_by;
69 ELSE
70 new_references.creation_date := x_creation_date;
71 new_references.created_by := x_created_by;
72 END IF;
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76 new_references.org_id := x_org_id;
77
78 END set_column_values;
79
80 PROCEDURE check_constraints (
81 column_name IN VARCHAR2 DEFAULT NULL,
82 column_value IN VARCHAR2 DEFAULT NULL ) AS
83 /*************************************************************
84 Created By :
85 Date Created By :
86 Purpose :
87 Know limitations, enhancements or remarks
88 Change History
89 Who When What
90
91 (reverse chronological order - newest change first)
92 ***************************************************************/
93
94 BEGIN
95
96 IF column_name IS NULL THEN
97 NULL;
98 ELSIF UPPER(column_name) = 'ASSIGN_RANDOMLY' THEN
99 new_references.assign_randomly := column_value;
100 NULL;
101 END IF;
102
103
104
105 -- The following code checks for check constraints on the Columns.
106 IF UPPER(column_name) = 'ASSIGN_RANDOMLY' OR
107 column_name IS NULL THEN
108 IF NOT (new_references.assign_randomly IN ('Y','N')) THEN
109 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 END IF;
113 END IF;
114
115
116 END check_constraints;
117
118 PROCEDURE check_uniqueness AS
119 /*************************************************************
120 Created By :
121 Date Created By :
122 Purpose :
123 Know limitations, enhancements or remarks
124 Change History
125 Who When What
126
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 begin
131 IF get_uk_for_validation (
132 new_references.program_type_group_cd
133 ,new_references.cal_type
134 ,new_references.sequence_number
135 ,new_references.student_type
136 ) THEN
137 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
138 igs_ge_msg_stack.add;
139 app_exception.raise_exception;
140 END IF;
141 END check_uniqueness ;
142 PROCEDURE check_parent_existance AS
143 /*************************************************************
144 Created By :
145 Date Created By :
146 Purpose :
147 Know limitations, enhancements or remarks
148 Change History
149 Who When What
150
151 (reverse chronological order - newest change first)
152 ***************************************************************/
153
154 BEGIN
155
156 IF (((old_references.cal_type = new_references.cal_type) AND
157 (old_references.sequence_number = new_references.sequence_number)) OR
158 ((new_references.cal_type IS NULL) OR
159 (new_references.sequence_number IS NULL))) THEN
160 NULL;
161 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
162 new_references.cal_type,
163 new_references.sequence_number
164 ) THEN
165 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
166 igs_ge_msg_stack.add;
167 app_exception.raise_exception;
168 END IF;
169
170 IF (((old_references.program_type_group_cd = new_references.program_type_group_cd)) OR
171 ((new_references.program_type_group_cd IS NULL))) THEN
172 NULL;
173 ELSIF NOT igs_ps_type_grp_pkg.get_pk_for_validation (
174 new_references.program_type_group_cd
175 ) THEN
176 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
177 igs_ge_msg_stack.add;
178 app_exception.raise_exception;
179 END IF;
180
181 END check_parent_existance;
182
183 PROCEDURE check_child_existance IS
184 /*************************************************************
185 Created By :
186 Date Created By :
187 Purpose :
188 Know limitations, enhancements or remarks
189 Change History
190 Who When What
191
192 (reverse chronological order - newest change first)
193 ***************************************************************/
194
195 BEGIN
196
197 igs_en_timeslot_prty_pkg.get_fk_igs_en_timeslot_stup (
198 old_references.igs_en_timeslot_stup_id
199 );
200
201 END check_child_existance;
202
203 FUNCTION get_pk_for_validation (
204 x_igs_en_timeslot_stup_id IN NUMBER
205 ) RETURN BOOLEAN AS
206
207 /*************************************************************
208 Created By :
209 Date Created By :
210 Purpose :
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214
215 (reverse chronological order - newest change first)
216 ***************************************************************/
217
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_en_timeslot_stup
221 WHERE igs_en_timeslot_stup_id = x_igs_en_timeslot_stup_id
222 FOR UPDATE NOWAIT;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 OPEN cur_rowid;
229 FETCH cur_rowid INTO lv_rowid;
230 IF (cur_rowid%FOUND) THEN
231 CLOSE cur_rowid;
232 RETURN(TRUE);
233 ELSE
234 CLOSE cur_rowid;
235 RETURN(FALSE);
236 END IF;
237 END get_pk_for_validation;
238
239 FUNCTION get_uk_for_validation (
240 x_program_type_group_cd IN VARCHAR2,
241 x_cal_type IN VARCHAR2,
242 x_sequence_number IN NUMBER,
243 x_student_type IN VARCHAR2
244 ) RETURN BOOLEAN AS
245
246 /*************************************************************
247 Created By :
248 Date Created By :
249 Purpose :
250 Know limitations, enhancements or remarks
251 Change History
252 Who When What
253
254 (reverse chronological order - newest change first)
255 ***************************************************************/
256
257 CURSOR cur_rowid IS
258 SELECT rowid
259 FROM igs_en_timeslot_stup
260 WHERE program_type_group_cd = x_program_type_group_cd
261 AND cal_type = x_cal_type
262 AND sequence_number = x_sequence_number
266 lv_rowid cur_rowid%RowType;
263 AND student_type = x_student_type and ((l_rowid is null) or (rowid <> l_rowid))
264
265 ;
267
268 BEGIN
269
270 OPEN cur_rowid;
271 FETCH cur_rowid INTO lv_rowid;
272 IF (cur_rowid%FOUND) THEN
273 CLOSE cur_rowid;
274 RETURN (true);
275 ELSE
276 CLOSE cur_rowid;
277 RETURN(FALSE);
278 END IF;
279 END get_uk_for_validation ;
280 PROCEDURE get_fk_igs_ca_inst (
281 x_cal_type IN VARCHAR2,
282 x_sequence_number IN NUMBER
283 ) AS
284
285 /*************************************************************
286 Created By :
287 Date Created By :
288 Purpose :
289 Know limitations, enhancements or remarks
290 Change History
291 Who When What
292
293 (reverse chronological order - newest change first)
294 ***************************************************************/
295
296 CURSOR cur_rowid IS
297 SELECT rowid
298 FROM igs_en_timeslot_stup
299 WHERE cal_type = x_cal_type
300 AND sequence_number = x_sequence_number ;
301
302 lv_rowid cur_rowid%RowType;
303
304 BEGIN
305
306 OPEN cur_rowid;
307 FETCH cur_rowid INTO lv_rowid;
308 IF (cur_rowid%FOUND) THEN
309 CLOSE cur_rowid;
310 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313 RETURN;
314 END IF;
315 CLOSE cur_rowid;
316
317 END get_fk_igs_ca_inst;
318
319 PROCEDURE get_fk_igs_ps_type_grp (
320 x_course_type_group_cd IN VARCHAR2
321 ) AS
322
323 /*************************************************************
324 Created By :
325 Date Created By :
326 Purpose :
327 Know limitations, enhancements or remarks
328 Change History
329 Who When What
330
331 (reverse chronological order - newest change first)
332 ***************************************************************/
333
334 CURSOR cur_rowid IS
335 SELECT rowid
336 FROM igs_en_timeslot_stup
337 WHERE program_type_group_cd = x_course_type_group_cd ;
338
339 lv_rowid cur_rowid%RowType;
340
341 BEGIN
342
343 OPEN cur_rowid;
344 FETCH cur_rowid INTO lv_rowid;
345 IF (cur_rowid%FOUND) THEN
346 CLOSE cur_rowid;
347 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
348 igs_ge_msg_stack.add;
349 app_exception.raise_exception;
350 RETURN;
351 END IF;
352 CLOSE cur_rowid;
353
354 END get_fk_igs_ps_type_grp;
355
356 PROCEDURE before_dml (
357 p_action IN VARCHAR2,
358 x_rowid IN VARCHAR2 DEFAULT NULL,
359 x_sequence_number IN NUMBER DEFAULT NULL,
360 x_student_type IN VARCHAR2 DEFAULT NULL,
361 x_assign_randomly IN VARCHAR2 DEFAULT NULL,
362 x_surname_alphabet IN VARCHAR2 DEFAULT NULL,
363 x_cal_type IN VARCHAR2 DEFAULT NULL,
364 x_igs_en_timeslot_stup_id IN NUMBER DEFAULT NULL,
365 x_program_type_group_cd IN VARCHAR2 DEFAULT NULL,
366 x_creation_date IN DATE DEFAULT NULL,
367 x_created_by IN NUMBER DEFAULT NULL,
368 x_last_update_date IN DATE DEFAULT NULL,
369 x_last_updated_by IN NUMBER DEFAULT NULL,
370 x_last_update_login IN NUMBER DEFAULT NULL,
371 x_org_id IN NUMBER DEFAULT NULL
372
373 ) AS
374 /*************************************************************
375 Created By :
379 Change History
376 Date Created By :
377 Purpose :
378 Know limitations, enhancements or remarks
380 Who When What
381
382 (reverse chronological order - newest change first)
383 ***************************************************************/
384
385 BEGIN
386
387 set_column_values (
388 p_action,
389 x_rowid,
390 x_sequence_number,
391 x_student_type,
392 x_assign_randomly,
393 x_surname_alphabet,
394 x_cal_type,
395 x_igs_en_timeslot_stup_id,
396 x_program_type_group_cd,
397 x_creation_date,
398 x_created_by,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_login ,
402 x_org_id
403 );
404
405 IF (p_action = 'INSERT') THEN
406 -- Call all the procedures related to Before Insert.
407 Null;
408 IF get_pk_for_validation(
409 new_references.igs_en_timeslot_stup_id) THEN
410 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
411 igs_ge_msg_stack.add;
412 app_exception.raise_exception;
413 END IF;
414 check_uniqueness;
415 check_constraints;
416 check_parent_existance;
417 ELSIF (p_action = 'UPDATE') THEN
418 -- Call all the procedures related to Before Update.
419 Null;
420 check_uniqueness;
421 check_constraints;
422 check_parent_existance;
423 ELSIF (p_action = 'DELETE') THEN
424 -- Call all the procedures related to Before Delete.
425 Null;
426 check_child_existance;
427 ELSIF (p_action = 'VALIDATE_INSERT') THEN
428 -- Call all the procedures related to Before Insert.
429 IF get_pk_for_validation (
430 new_references.igs_en_timeslot_stup_id) THEN
431 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434 END IF;
435 check_uniqueness;
436 check_constraints;
437 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
438 check_uniqueness;
439 check_constraints;
440 ELSIF (p_action = 'VALIDATE_DELETE') THEN
441 check_child_existance;
442 END IF;
443
444 END before_dml;
445
446 PROCEDURE after_dml (
447 p_action IN VARCHAR2,
448 x_rowid IN VARCHAR2
449 ) IS
450 /*************************************************************
451 Created By :
452 Date Created By :
453 Purpose :
454 Know limitations, enhancements or remarks
455 Change History
456 Who When What
457
458 (reverse chronological order - newest change first)
459 ***************************************************************/
460
461 BEGIN
462
463 l_rowid := x_rowid;
464
465 IF (p_action = 'INSERT') THEN
466 -- Call all the procedures related to After Insert.
467 Null;
468 ELSIF (p_action = 'UPDATE') THEN
469 -- Call all the procedures related to After Update.
470 Null;
471 ELSIF (p_action = 'DELETE') THEN
472 -- Call all the procedures related to After Delete.
473 Null;
474 END IF;
475
476 END after_dml;
477
478 PROCEDURE insert_row (
479 x_rowid IN OUT NOCOPY VARCHAR2,
480 x_sequence_number IN NUMBER,
481 x_student_type IN VARCHAR2,
482 x_assign_randomly IN VARCHAR2,
483 x_surname_alphabet IN VARCHAR2,
484 x_cal_type IN VARCHAR2,
485 x_igs_en_timeslot_stup_id IN OUT NOCOPY NUMBER,
486 x_program_type_group_cd IN VARCHAR2,
487 x_mode IN VARCHAR2 DEFAULT 'R' ,
488 X_ORG_ID in NUMBER
489
490 ) AS
491 /*************************************************************
492 Created By :
493 Date Created By :
494 Purpose :
495 Know limitations, enhancements or remarks
496 Change History
500 pradhakr 23-Jul-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id in
497 Who When What
498
499 (reverse chronological order - newest change first)
501 before dml as part of bug# 2457599
502 ***************************************************************/
503
504 CURSOR c IS SELECT ROWID FROM igs_en_timeslot_stup
505 WHERE igs_en_timeslot_stup_id= x_igs_en_timeslot_stup_id
506 ;
507 x_last_update_date DATE ;
508 x_last_updated_by NUMBER ;
509 x_last_update_login NUMBER ;
510 BEGIN
511 x_last_update_date := SYSDATE;
512 IF(x_mode = 'I') THEN
513 x_last_updated_by := 1;
514 x_last_update_login := 0;
515 ELSIF (x_mode = 'R') THEN
516 x_last_updated_by := fnd_global.user_id;
517 IF x_last_updated_by IS NULL then
518 x_last_updated_by := -1;
519 END IF;
520 x_last_update_login := fnd_global.login_id;
521 IF x_last_update_login IS NULL THEN
522 x_last_update_login := -1;
523 END IF;
524 ELSE
525 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
528 end if;
529
530 select igs_en_timeslot_stup_s.nextval into x_igs_en_timeslot_stup_id from dual;
531
532 before_dml(
533 p_action=>'INSERT',
534 x_rowid=>X_ROWID,
535 x_sequence_number=>x_sequence_number,
536 x_student_type=>x_student_type,
537 x_assign_randomly=>x_assign_randomly,
538 x_surname_alphabet=>x_surname_alphabet,
539 x_cal_type=>x_cal_type,
540 x_igs_en_timeslot_stup_id=>x_igs_en_timeslot_stup_id,
541 x_program_type_group_cd=>x_program_type_group_cd,
542 x_creation_date=> x_last_update_date,
543 x_created_by=> x_last_updated_by,
544 x_last_update_date=> x_last_update_date,
545 x_last_updated_by=> x_last_updated_by,
546 x_last_update_login=> x_last_update_login,
547 x_org_id => igs_ge_gen_003.get_org_id );
548 INSERT INTO igs_en_timeslot_stup (
549 sequence_number
550 ,student_type
551 ,assign_randomly
552 ,surname_alphabet
553 ,cal_type
554 ,igs_en_timeslot_stup_id
555 ,program_type_group_cd
556 ,creation_date
557 ,created_by
558 ,last_update_date
559 ,last_updated_by
560 ,last_update_login
561 ,ORG_ID
562 ) VALUES (
563 new_references.sequence_number
564 ,new_references.student_type
565 ,new_references.assign_randomly
566 ,new_references.surname_alphabet
567 ,new_references.cal_type
568 ,new_references.igs_en_timeslot_stup_id
569 ,new_references.program_type_group_cd
570 ,x_last_update_date
571 ,x_last_updated_by
572 ,x_last_update_date
573 ,x_last_updated_by
574 ,x_last_update_login
575 ,NEW_REFERENCES.ORG_ID );
576 OPEN c;
577 FETCH c INTO x_rowid;
578 IF (c%NOTFOUND) THEN
579 CLOSE c;
580 RAISE NO_DATA_FOUND;
581 END IF;
582 CLOSE c;
583 after_dml (
584 p_action => 'INSERT' ,
585 x_rowid => X_ROWID );
586 END insert_row;
587 PROCEDURE lock_row (
588 x_rowid IN VARCHAR2,
589 x_sequence_number IN NUMBER,
590 x_student_type IN VARCHAR2,
591 x_assign_randomly IN VARCHAR2,
592 x_surname_alphabet IN VARCHAR2,
593 x_cal_type IN VARCHAR2,
594 x_igs_en_timeslot_stup_id IN NUMBER,
595 x_program_type_group_cd IN VARCHAR2 ) AS
596 /*************************************************************
597 Created By :
598 Date Created By :
599 Purpose :
600 Know limitations, enhancements or remarks
601 Change History
602 Who When What
603 Nishikant 31JAN03 SURNAME_ALPHABET changed to nullable - Bug#2455364
604 (reverse chronological order - newest change first)
605 ***************************************************************/
606
607 CURSOR c1 IS SELECT
608 sequence_number
609 , student_type
610 , assign_randomly
611 , surname_alphabet
612 , cal_type
613 , program_type_group_cd
614 FROM igs_en_timeslot_stup
615 WHERE ROWID = x_rowid
616 FOR UPDATE NOWAIT;
617 tlinfo c1%ROWTYPE;
618 BEGIN
619 OPEN c1;
620 FETCH c1 INTO tlinfo;
621 IF (c1%notfound) THEN
622 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
623 igs_ge_msg_stack.add;
624 CLOSE c1;
625 app_exception.raise_exception;
626 RETURN;
627 END IF;
628 CLOSE c1;
629 if ( ( tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
630 AND (tlinfo.STUDENT_TYPE = X_STUDENT_TYPE)
631 AND (tlinfo.ASSIGN_RANDOMLY = X_ASSIGN_RANDOMLY)
632 AND ((tlinfo.SURNAME_ALPHABET = X_SURNAME_ALPHABET)
633 OR ((tlinfo.SURNAME_ALPHABET IS NULL)
634 AND (X_SURNAME_ALPHABET IS NULL)))
635 AND (tlinfo.CAL_TYPE = X_CAL_TYPE)
636 AND (tlinfo.PROGRAM_TYPE_GROUP_CD = X_PROGRAM_TYPE_GROUP_CD)
637 ) THEN
641 igs_ge_msg_stack.add;
638 NULL;
639 ELSE
640 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
642 app_exception.raise_exception;
643 END IF;
644 RETURN;
645 END lock_row;
646 PROCEDURE update_row (
647 x_rowid IN VARCHAR2,
648 x_SEQUENCE_NUMBER IN NUMBER,
649 x_STUDENT_TYPE IN VARCHAR2,
650 x_ASSIGN_RANDOMLY IN VARCHAR2,
651 x_SURNAME_ALPHABET IN VARCHAR2,
652 x_CAL_TYPE IN VARCHAR2,
653 x_IGS_EN_TIMESLOT_STUP_ID IN NUMBER,
654 x_PROGRAM_TYPE_GROUP_CD IN VARCHAR2,
655 x_mode IN VARCHAR2 DEFAULT 'R'
656 ) AS
657 /*************************************************************
658 Created By :
659 Date Created By :
660 Purpose :
661 Know limitations, enhancements or remarks
662 Change History
663 Who When What
664
665 (reverse chronological order - newest change first)
666 ***************************************************************/
667
668 x_last_update_date DATE ;
669 x_last_updated_by NUMBER ;
670 x_last_update_login NUMBER ;
671 BEGIN
672 x_last_update_date := SYSDATE;
673 IF (X_MODE = 'I') THEN
674 x_last_updated_by := 1;
675 x_last_update_login := 0;
676 ELSIF (x_mode = 'R') THEN
677 x_last_updated_by := fnd_global.user_id;
678 IF x_last_updated_by IS NULL THEN
679 x_last_updated_by := -1;
680 END IF;
681 x_last_update_login := fnd_global.login_id;
682 IF x_last_update_login IS NULL THEN
683 x_last_update_login := -1;
684 END IF;
685 ELSE
686 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
687 igs_ge_msg_stack.add;
688 app_exception.raise_exception;
689 END IF;
690 before_dml(
691 p_action=>'UPDATE',
692 x_rowid=>X_ROWID,
693 x_sequence_number=>X_SEQUENCE_NUMBER,
694 x_student_type=>X_STUDENT_TYPE,
695 x_assign_randomly=>X_ASSIGN_RANDOMLY,
696 x_surname_alphabet=>X_SURNAME_ALPHABET,
697 x_cal_type=>X_CAL_TYPE,
698 x_igs_en_timeslot_stup_id=>X_IGS_EN_TIMESLOT_STUP_ID,
699 x_program_type_group_cd=>X_PROGRAM_TYPE_GROUP_CD,
700 x_creation_date=>x_last_update_date,
701 x_created_by=>x_last_updated_by,
702 x_last_update_date=>x_last_update_date,
703 x_last_updated_by=>x_last_updated_by,
704 x_last_update_login=>x_last_update_login);
705 UPDATE igs_en_timeslot_stup SET
706 sequence_number = NEW_REFERENCES.sequence_number,
707 student_type = NEW_REFERENCES.student_type,
708 assign_randomly = NEW_REFERENCES.assign_randomly,
709 surname_alphabet = NEW_REFERENCES.surname_alphabet,
710 cal_type = NEW_REFERENCES.cal_type,
711 program_type_group_cd = NEW_REFERENCES.program_type_group_cd,
712 last_update_date = x_last_update_date,
713 last_updated_by = x_last_updated_by,
714 last_update_login = x_last_update_login
715 WHERE ROWID = x_rowid;
716 IF (SQL%NOTFOUND) THEN
717 RAISE NO_DATA_FOUND;
718 END IF;
719
720 after_dml (
721 p_action => 'UPDATE' ,
722 x_rowid => X_ROWID
723 );
724 END update_row;
725 PROCEDURE add_row (
726 x_rowid IN OUT NOCOPY VARCHAR2,
727 x_sequence_number IN NUMBER,
728 x_student_type IN VARCHAR2,
729 x_assign_randomly IN VARCHAR2,
730 x_surname_alphabet IN VARCHAR2,
731 x_cal_type IN VARCHAR2,
732 x_igs_en_timeslot_stup_id IN OUT NOCOPY NUMBER,
733 x_program_type_group_cd IN VARCHAR2,
734 x_mode IN VARCHAR2 DEFAULT 'R' ,
735 X_ORG_ID in NUMBER
736 ) AS
737 /*************************************************************
738 Created By :
739 Date Created By :
740 Purpose :
741 Know limitations, enhancements or remarks
742 Change History
743 Who When What
744
745 (reverse chronological order - newest change first)
746 ***************************************************************/
747
748 CURSOR c1 IS SELECT ROWID FROM igs_en_timeslot_stup
749 WHERE igs_en_timeslot_stup_id= x_igs_en_timeslot_stup_id
750 ;
751 BEGIN
752 OPEN c1;
753 FETCH c1 INTO x_rowid;
754 IF (c1%NOTFOUND) THEN
755 CLOSE c1;
756 insert_row (
757 x_rowid,
758 X_sequence_number,
759 X_student_type,
760 X_assign_randomly,
761 X_surname_alphabet,
762 X_cal_type,
763 X_igs_en_timeslot_stup_id,
764 X_program_type_group_cd,
765 x_mode ,
766 X_ORG_ID);
767
768 RETURN;
769 END IF;
770 CLOSE c1;
771 update_row (
772 x_rowid,
773 x_sequence_number,
774 x_student_type,
775 x_assign_randomly,
776 x_surname_alphabet,
777 x_cal_type,
778 x_igs_en_timeslot_stup_id,
779 x_program_type_group_cd,
780 x_mode );
781 END add_row;
782 PROCEDURE delete_row (
783 x_rowid IN VARCHAR2
784 ) AS
785 /*************************************************************
786 Created By :
787 Date Created By :
788 Purpose :
789 Know limitations, enhancements or remarks
790 Change History
791 Who When What
792
793 (reverse chronological order - newest change first)
794 ***************************************************************/
795
796 BEGIN
797 before_dml (
798 p_action => 'DELETE',
799 x_rowid => X_ROWID
800 );
801 DELETE FROM igs_en_timeslot_stup
802 WHERE ROWID = x_rowid;
803 IF (SQL%NOTFOUND) THEN
804 RAISE NO_DATA_FOUND;
805 END IF;
806 after_dml (
807 p_action => 'DELETE',
808 x_rowid => X_ROWID
809 );
810 END delete_row;
811 END igs_en_timeslot_stup_pkg;
[Home] [Help]