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