1 PACKAGE BODY igs_ps_usec_lim_wlst_pkg AS
2 /* $Header: IGSPI1LB.pls 120.2 2005/07/04 05:46:01 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_lim_wlst%RowType;
5 new_references igs_ps_usec_lim_wlst%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_unit_section_limit_wlst_id IN NUMBER ,
11 x_uoo_id IN NUMBER ,
12 x_enrollment_expected IN NUMBER ,
13 x_enrollment_minimum IN NUMBER ,
14 x_enrollment_maximum IN NUMBER ,
15 x_advance_maximum IN NUMBER ,
16 x_waitlist_allowed IN VARCHAR2 ,
17 x_max_students_per_waitlist IN NUMBER ,
18 x_override_enrollment_max IN NUMBER ,
19 x_max_auditors_allowed IN NUMBER,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER
25 ) AS
26
27 /*************************************************************
28 Created By : venagara
29 Date Created By : 2000/05/12
30 Purpose :
31 Know limitations, enhancements or remarks
32 Change History
33 Who When What
34 (reverse chronological order - newest change first)
35 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
36 ***************************************************************/
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM IGS_PS_USEC_LIM_WLST
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 Open cur_old_ref_values;
50 Fetch cur_old_ref_values INTO old_references;
51 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
52 Close cur_old_ref_values;
53 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 Return;
57 END IF;
58 Close cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.unit_section_limit_waitlist_id := x_unit_section_limit_wlst_id;
62 new_references.uoo_id := x_uoo_id;
63 new_references.enrollment_expected := x_enrollment_expected;
64 new_references.enrollment_minimum := x_enrollment_minimum;
65 new_references.enrollment_maximum := x_enrollment_maximum;
66 new_references.advance_maximum := x_advance_maximum;
67 new_references.waitlist_allowed := x_waitlist_allowed;
68 new_references.max_students_per_waitlist := x_max_students_per_waitlist;
69 new_references.override_enrollment_max := x_override_enrollment_max ;
70 new_references.max_auditors_allowed := x_max_auditors_allowed;
71
72 IF (p_action = 'UPDATE') THEN
73 new_references.creation_date := old_references.creation_date;
74 new_references.created_by := old_references.created_by;
75 ELSE
76 new_references.creation_date := x_creation_date;
77 new_references.created_by := x_created_by;
78 END IF;
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 PROCEDURE Check_Constraints (
86 Column_Name IN VARCHAR2 ,
87 Column_Value IN VARCHAR2 ) AS
88 /*************************************************************
89 Created By : venagara
90 Date Created By : 2000/05/12
91 Purpose :
92 Know limitations, enhancements or remarks
93 Change History
94 Who When What
95
96 (reverse chronological order - newest change first)
97 ***************************************************************/
98
99 BEGIN
100
101 IF column_name IS NULL THEN
102 NULL;
103 NULL;
104 END IF;
105
106 END Check_Constraints;
107
108 PROCEDURE Check_Uniqueness AS
109 /*************************************************************
110 Created By : venagara
111 Date Created By : 2000/05/12
112 Purpose :
113 Know limitations, enhancements or remarks
114 Change History
115 Who When What
116
117 (reverse chronological order - newest change first)
118 ***************************************************************/
119
120 begin
121 IF Get_Uk_For_Validation (
122 new_references.uoo_id
123 ) THEN
124 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
125 IGS_GE_MSG_STACK.ADD;
126 app_exception.raise_exception;
127 END IF;
128 END Check_Uniqueness ;
129 PROCEDURE Check_Parent_Existance AS
130 /*************************************************************
131 Created By : venagara
132 Date Created By : 2000/05/12
133 Purpose :
134 Know limitations, enhancements or remarks
135 Change History
136 Who When What
137
138 (reverse chronological order - newest change first)
139 ***************************************************************/
140
141 BEGIN
142
143 IF (((old_references.uoo_id = new_references.uoo_id)) OR
144 ((new_references.uoo_id IS NULL))) THEN
145 NULL;
146 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
147 new_references.uoo_id
148 ) THEN
149 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END IF;
153
154 END Check_Parent_Existance;
155
156 FUNCTION Get_PK_For_Validation (
157 x_unit_section_limit_wlst_id IN NUMBER
158 ) RETURN BOOLEAN AS
159
160 /*************************************************************
161 Created By : venagara
162 Date Created By : 2000/05/12
163 Purpose :
164 Know limitations, enhancements or remarks
165 Change History
166 Who When What
167
168 (reverse chronological order - newest change first)
169 ***************************************************************/
170
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM igs_ps_usec_lim_wlst
174 WHERE unit_section_limit_waitlist_id = x_unit_section_limit_wlst_id
175 FOR UPDATE NOWAIT;
176
177 lv_rowid cur_rowid%RowType;
178
179 BEGIN
180
181 Open cur_rowid;
182 Fetch cur_rowid INTO lv_rowid;
183 IF (cur_rowid%FOUND) THEN
184 Close cur_rowid;
185 Return(TRUE);
186 ELSE
187 Close cur_rowid;
188 Return(FALSE);
189 END IF;
190 END Get_PK_For_Validation;
191
192 FUNCTION Get_UK_For_Validation (
193 x_uoo_id IN NUMBER
194 ) RETURN BOOLEAN AS
195
196 /*************************************************************
197 Created By : venagara
198 Date Created By : 2000/05/12
199 Purpose :
200 Know limitations, enhancements or remarks
201 Change History
202 Who When What
203
204 (reverse chronological order - newest change first)
205 ***************************************************************/
206
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM igs_ps_usec_lim_wlst
210 WHERE uoo_id = x_uoo_id and ((l_rowid is null) or (rowid <> l_rowid))
211
212 ;
213 lv_rowid cur_rowid%RowType;
214
215 BEGIN
216
217 Open cur_rowid;
218 Fetch cur_rowid INTO lv_rowid;
219 IF (cur_rowid%FOUND) THEN
220 Close cur_rowid;
221 return (true);
222 ELSE
223 close cur_rowid;
224 return(false);
225 END IF;
226 END Get_UK_For_Validation ;
227 PROCEDURE Get_UFK_Igs_Ps_Unit_Ofr_Opt (
228 x_uoo_id IN NUMBER
229 ) AS
230
231 /*************************************************************
232 Created By : venagara
233 Date Created By : 2000/05/12
234 Purpose :
235 Know limitations, enhancements or remarks
236 Change History
237 Who When What
238
239 (reverse chronological order - newest change first)
240 ***************************************************************/
241
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM igs_ps_usec_lim_wlst
245 WHERE uoo_id = x_uoo_id ;
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 Open cur_rowid;
252 Fetch cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 Close cur_rowid;
255 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USLW_UOO_UFK');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 Return;
259 END IF;
260 Close cur_rowid;
261
262 END Get_UFK_Igs_Ps_Unit_Ofr_Opt;
263
264 PROCEDURE Before_DML (
265 p_action IN VARCHAR2,
266 x_rowid IN VARCHAR2 ,
267 x_unit_section_limit_wlst_id IN NUMBER ,
268 x_uoo_id IN NUMBER ,
269 x_enrollment_expected IN NUMBER ,
270 x_enrollment_minimum IN NUMBER ,
271 x_enrollment_maximum IN NUMBER ,
272 x_advance_maximum IN NUMBER ,
273 x_waitlist_allowed IN VARCHAR2 ,
274 x_max_students_per_waitlist IN NUMBER ,
275 x_override_enrollment_max IN NUMBER ,
276 x_max_auditors_allowed IN NUMBER,
277 x_creation_date IN DATE ,
278 x_created_by IN NUMBER ,
279 x_last_update_date IN DATE ,
280 x_last_updated_by IN NUMBER ,
281 x_last_update_login IN NUMBER
282 ) AS
283 /*************************************************************
284 Created By : venagara
285 Date Created By : 2000/05/12
286 Purpose :
287 Know limitations, enhancements or remarks
288 Change History
289 Who When What
290 (reverse chronological order - newest change first)
291 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
292 ***************************************************************/
293
294 BEGIN
295
296 Set_Column_Values (
297 p_action,
298 x_rowid,
299 x_unit_section_limit_wlst_id,
300 x_uoo_id,
301 x_enrollment_expected,
302 x_enrollment_minimum,
303 x_enrollment_maximum,
304 x_advance_maximum,
305 x_waitlist_allowed,
306 x_max_students_per_waitlist,
307 x_override_enrollment_max,
308 x_max_auditors_allowed,
309 x_creation_date,
310 x_created_by,
311 x_last_update_date,
312 x_last_updated_by,
313 x_last_update_login
314 );
315
316 IF (p_action = 'INSERT') THEN
317 -- Call all the procedures related to Before Insert.
318 Null;
319 IF Get_Pk_For_Validation(
320 new_references.unit_section_limit_waitlist_id) THEN
321 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 END IF;
325 Check_Uniqueness;
326 Check_Constraints;
327 Check_Parent_Existance;
328 ELSIF (p_action = 'UPDATE') THEN
329 -- Call all the procedures related to Before Update.
330 Null;
331 Check_Uniqueness;
332 Check_Constraints;
333 Check_Parent_Existance;
334 ELSIF (p_action = 'DELETE') THEN
335 -- Call all the procedures related to Before Delete.
336 Null;
337 ELSIF (p_action = 'VALIDATE_INSERT') THEN
338 -- Call all the procedures related to Before Insert.
339 IF Get_PK_For_Validation (
340 new_references.unit_section_limit_waitlist_id) THEN
341 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END IF;
345 Check_Uniqueness;
346 Check_Constraints;
347 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
348 Check_Uniqueness;
349 Check_Constraints;
350 END IF;
351
352 END Before_DML;
353
354 PROCEDURE After_DML (
355 p_action IN VARCHAR2,
356 x_rowid IN VARCHAR2
357 ) IS
358 /*************************************************************
359 Created By : venagara
360 Date Created By : 2000/05/12
361 Purpose :
362 Know limitations, enhancements or remarks
363 Change History
364 Who When What
365 sarakshi 05-May-2005 Bug#4349740, added the after dml update/insert logic
366 (reverse chronological order - newest change first)
367 ***************************************************************/
368
369 CURSOR cur_unit_limit(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
370 SELECT enrollment_maximum,enrollment_expected,override_enrollment_max
371 FROM igs_ps_unit_ver_all uv,
372 igs_ps_unit_ofr_opt_all uoo
373 WHERE uv.unit_cd=uoo.unit_cd
374 AND uv.version_number=uoo.version_number
375 AND uoo.uoo_id=cp_uoo_id;
376 l_c_unit cur_unit_limit%ROWTYPE;
377
378 l_message_name VARCHAR2(30);
379 l_request_id NUMBER;
380 BEGIN
381
382 l_rowid := x_rowid;
383
384 IF (p_action = 'INSERT') THEN
385 -- Call all the procedures related to After Insert.
386 OPEN cur_unit_limit(new_references.uoo_id);
387 FETCH cur_unit_limit INTO l_c_unit;
388 CLOSE cur_unit_limit;
389
390 IF (
391 NVL(new_references.enrollment_maximum,-999) <> NVL(l_c_unit.enrollment_maximum,-999) OR
392 NVL(new_references.enrollment_expected,-999) <> NVL(l_c_unit.enrollment_expected,-999) OR
393 NVL(new_references.override_enrollment_max,-999) <> NVL(l_c_unit.override_enrollment_max,-999)
394 ) THEN
395
396 IF igs_ps_usec_schedule.prgp_upd_usec_dtls(
397 p_uoo_id=>new_references.uoo_id,
398 p_max_enrollments =>NVL(l_c_unit.enrollment_maximum,-999) ,
399 p_override_enrollment_max => NVL(l_c_unit.override_enrollment_max,-999),
400 p_enrollment_expected => NVL(l_c_unit.enrollment_expected,-999),
401 p_request_id =>l_request_id,
402 p_message_name=>l_message_name
403 ) = FALSE THEN
404
405
406 FND_MESSAGE.SET_NAME( 'IGS', 'l_message_name');
407 IGS_GE_MSG_STACK.ADD;
408 app_exception.raise_exception;
409 END IF;
410 END IF;
411 ELSIF (p_action = 'UPDATE') THEN
412
413 IF (
414 NVL(new_references.enrollment_maximum,-999) <> NVL(old_references.enrollment_maximum,-999) OR
415 NVL(new_references.enrollment_expected,-999) <> NVL(old_references.enrollment_expected,-999) OR
416 NVL(new_references.override_enrollment_max,-999) <> NVL(old_references.override_enrollment_max,-999)
417 ) THEN
418
419
420 IF igs_ps_usec_schedule.prgp_upd_usec_dtls(
421 p_uoo_id=>new_references.uoo_id,
422 p_max_enrollments =>NVL(old_references.enrollment_maximum,-999) ,
423 p_override_enrollment_max => NVL(old_references.override_enrollment_max,-999),
424 p_enrollment_expected => NVL(old_references.enrollment_expected,-999),
425 p_request_id =>l_request_id,
426 p_message_name=>l_message_name
427 ) = FALSE THEN
428
429
430 FND_MESSAGE.SET_NAME( 'IGS', 'l_message_name');
431 IGS_GE_MSG_STACK.ADD;
432 app_exception.raise_exception;
433 END IF;
434 END IF;
435
436
437 ELSIF (p_action = 'DELETE') THEN
438 -- Call all the procedures related to After Delete.
439 OPEN cur_unit_limit(old_references.uoo_id);
440 FETCH cur_unit_limit INTO l_c_unit;
441 CLOSE cur_unit_limit;
442
443 IF (
444 NVL(old_references.enrollment_maximum,-999) <> NVL(l_c_unit.enrollment_maximum,-999) OR
445 NVL(old_references.enrollment_expected,-999) <> NVL(l_c_unit.enrollment_expected,-999) OR
446 NVL(old_references.override_enrollment_max,-999) <> NVL(l_c_unit.override_enrollment_max,-999)
447 ) THEN
448
449
450 IF igs_ps_usec_schedule.prgp_upd_usec_dtls(
451 p_uoo_id=>old_references.uoo_id,
452 p_max_enrollments =>NVL(l_c_unit.enrollment_maximum,-999) ,
453 p_override_enrollment_max => NVL(l_c_unit.override_enrollment_max,-999),
454 p_enrollment_expected => NVL(l_c_unit.enrollment_expected,-999),
455 p_request_id =>l_request_id,
456 p_message_name=>l_message_name
457 ) = FALSE THEN
458
459
460 FND_MESSAGE.SET_NAME( 'IGS', 'l_message_name');
461 IGS_GE_MSG_STACK.ADD;
462 app_exception.raise_exception;
463 END IF;
464 END IF;
465
466 END IF;
467
468 l_rowid:=NULL;
469 END After_DML;
470
471 procedure INSERT_ROW (
472 X_ROWID in out NOCOPY VARCHAR2,
473 X_UNIT_SECTION_LIMIT_WLST_ID IN OUT NOCOPY NUMBER,
474 x_UOO_ID IN NUMBER,
475 x_ENROLLMENT_EXPECTED IN NUMBER,
476 x_ENROLLMENT_MINIMUM IN NUMBER,
477 x_ENROLLMENT_MAXIMUM IN NUMBER,
478 x_ADVANCE_MAXIMUM IN NUMBER,
479 x_WAITLIST_ALLOWED IN VARCHAR2,
480 x_MAX_STUDENTS_PER_WAITLIST IN NUMBER,
481 X_OVERRIDE_ENROLLMENT_MAX IN NUMBER,
482 X_MAX_AUDITORS_ALLOWED IN NUMBER,
483 X_MODE in VARCHAR2
484 ) AS
485 /*************************************************************
486 Created By : venagara
487 Date Created By : 2000/05/12
488 Purpose :
489 Know limitations, enhancements or remarks
490 Change History
491 Who When What
492 (reverse chronological order - newest change first)
493 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
494 ***************************************************************/
495
496 cursor C is select ROWID from IGS_PS_USEC_LIM_WLST
497 where UNIT_SECTION_LIMIT_WAITLIST_ID= x_unit_section_limit_wlst_id
498 ;
499 X_LAST_UPDATE_DATE DATE ;
500 X_LAST_UPDATED_BY NUMBER ;
501 X_LAST_UPDATE_LOGIN NUMBER ;
502 begin
503 X_LAST_UPDATE_DATE := SYSDATE;
504 if(X_MODE = 'I') then
505 X_LAST_UPDATED_BY := 1;
506 X_LAST_UPDATE_LOGIN := 0;
507 elsif (X_MODE = 'R') then
508 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
509 if X_LAST_UPDATED_BY is NULL then
510 X_LAST_UPDATED_BY := -1;
511 end if;
512 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
513 if X_LAST_UPDATE_LOGIN is NULL then
514 X_LAST_UPDATE_LOGIN := -1;
515 end if;
516 else
517 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
518 IGS_GE_MSG_STACK.ADD;
519 app_exception.raise_exception;
520 end if;
521 SELECT
522 igs_ps_usec_lim_wlst_s.nextval
523 INTO
524 x_unit_section_limit_wlst_id
525 FROM dual;
526 Before_DML(
527 p_action=>'INSERT',
528 x_rowid=>X_ROWID,
529 x_unit_section_limit_wlst_id=>x_unit_section_limit_wlst_id,
530 x_uoo_id=>X_UOO_ID,
531 x_enrollment_expected=>X_ENROLLMENT_EXPECTED,
532 x_enrollment_minimum=>X_ENROLLMENT_MINIMUM,
533 x_enrollment_maximum=>X_ENROLLMENT_MAXIMUM,
534 x_advance_maximum=>X_ADVANCE_MAXIMUM,
535 x_waitlist_allowed=>X_WAITLIST_ALLOWED,
536 x_max_students_per_waitlist=>X_MAX_STUDENTS_PER_WAITLIST,
537 x_override_enrollment_max => X_OVERRIDE_ENROLLMENT_MAX,
538 x_max_auditors_allowed => X_MAX_AUDITORS_ALLOWED,
539 x_creation_date=>X_LAST_UPDATE_DATE,
540 x_created_by=>X_LAST_UPDATED_BY,
541 x_last_update_date=>X_LAST_UPDATE_DATE,
542 x_last_updated_by=>X_LAST_UPDATED_BY,
543 x_last_update_login=>X_LAST_UPDATE_LOGIN);
544 insert into IGS_PS_USEC_LIM_WLST (
545 UNIT_SECTION_LIMIT_WAITLIST_ID
546 ,UOO_ID
547 ,ENROLLMENT_EXPECTED
548 ,ENROLLMENT_MINIMUM
549 ,ENROLLMENT_MAXIMUM
550 ,ADVANCE_MAXIMUM
551 ,WAITLIST_ALLOWED
552 ,MAX_STUDENTS_PER_WAITLIST
553 ,OVERRIDE_ENROLLMENT_MAX
554 ,MAX_AUDITORS_ALLOWED
555 ,CREATION_DATE
556 ,CREATED_BY
557 ,LAST_UPDATE_DATE
558 ,LAST_UPDATED_BY
559 ,LAST_UPDATE_LOGIN
560 ) values (
561 NEW_REFERENCES.UNIT_SECTION_LIMIT_WAITLIST_ID
562 ,NEW_REFERENCES.UOO_ID
563 ,NEW_REFERENCES.ENROLLMENT_EXPECTED
564 ,NEW_REFERENCES.ENROLLMENT_MINIMUM
565 ,NEW_REFERENCES.ENROLLMENT_MAXIMUM
566 ,NEW_REFERENCES.ADVANCE_MAXIMUM
567 ,NEW_REFERENCES.WAITLIST_ALLOWED
568 ,NEW_REFERENCES.MAX_STUDENTS_PER_WAITLIST
569 ,NEW_REFERENCES.OVERRIDE_ENROLLMENT_MAX
570 ,NEW_REFERENCES.MAX_AUDITORS_ALLOWED
571 ,X_LAST_UPDATE_DATE
572 ,X_LAST_UPDATED_BY
573 ,X_LAST_UPDATE_DATE
574 ,X_LAST_UPDATED_BY
575 ,X_LAST_UPDATE_LOGIN
576 );
577 open c;
578 fetch c into X_ROWID;
579 if (c%notfound) then
580 close c;
581 raise no_data_found;
582 end if;
583 close c;
584 After_DML (
585 p_action => 'INSERT' ,
586 x_rowid => X_ROWID );
587 end INSERT_ROW;
588 procedure LOCK_ROW (
589 x_rowid IN VARCHAR2,
590 x_unit_section_limit_wlst_id IN NUMBER,
591 x_uoo_id IN NUMBER,
592 x_enrollment_expected IN NUMBER,
593 x_enrollment_minimum IN NUMBER,
594 x_enrollment_maximum IN NUMBER,
595 x_advance_maximum IN NUMBER,
596 x_waitlist_allowed IN VARCHAR2,
597 x_max_students_per_waitlist IN NUMBER ,
598 x_override_enrollment_max IN NUMBER,
599 x_max_auditors_allowed IN NUMBER ) AS
600 /*************************************************************
601 Created By : venagara
602 Date Created By : 2000/05/12
603 Purpose :
604 Know limitations, enhancements or remarks
605 Change History
606 Who When What
607 (reverse chronological order - newest change first)
608 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
609 ***************************************************************/
610
611 CURSOR c1 IS
612 SELECT uoo_id,
613 enrollment_expected,
614 enrollment_minimum,
615 enrollment_maximum,
616 advance_maximum,
617 waitlist_allowed,
618 max_students_per_waitlist,
619 override_enrollment_max,
620 max_auditors_allowed
621 FROM igs_ps_usec_lim_wlst
622 WHERE rowid = x_rowid
623 FOR UPDATE NOWAIT;
624 tlinfo c1%ROWTYPE;
625 BEGIN
626 OPEN c1;
627 FETCH c1 INTO tlinfo;
628 IF (c1%NOTFOUND) THEN
629 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
630 IGS_GE_MSG_STACK.ADD;
631 CLOSE c1;
632 APP_EXCEPTION.RAISE_EXCEPTION;
633 RETURN;
634 END IF;
635 CLOSE c1;
636 IF ( ( tlinfo.uoo_id = x_uoo_id)
637 AND ((tlinfo.enrollment_expected = x_enrollment_expected)
638 OR ((tlinfo.enrollment_expected IS NULL)
639 AND (x_enrollment_expected IS NULL)))
640 AND ((tlinfo.enrollment_minimum = x_enrollment_minimum)
641 OR ((tlinfo.enrollment_minimum IS NULL)
642 AND (x_enrollment_minimum IS NULL)))
643 AND ((tlinfo.enrollment_maximum = x_enrollment_maximum)
644 OR ((tlinfo.enrollment_maximum IS NULL)
645 AND (x_enrollment_maximum IS NULL)))
646 AND ((tlinfo.advance_maximum = x_advance_maximum)
647 OR ((tlinfo.advance_maximum IS NULL)
648 AND (x_advance_maximum IS NULL)))
649 AND (tlinfo.waitlist_allowed = x_waitlist_allowed)
650 AND (tlinfo.max_students_per_waitlist = x_max_students_per_waitlist)
651 AND ((tlinfo.override_enrollment_max = x_override_enrollment_max)
652 OR ((tlinfo.override_enrollment_max IS NULL)
653 AND (X_override_enrollment_max IS NULL)))
654 AND ((tlinfo.max_auditors_allowed = x_max_auditors_allowed)
655 OR ((tlinfo.max_auditors_allowed IS NULL)
656 AND (x_max_auditors_allowed IS NULL)))
657 ) THEN
658 NULL;
659 ELSE
660 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
661 IGS_GE_MSG_STACK.ADD;
662 APP_EXCEPTION.RAISE_EXCEPTION;
663 END IF;
664 RETURN;
665 END LOCK_ROW;
666
667 PROCEDURE UPDATE_ROW (
668 X_ROWID in VARCHAR2,
669 x_unit_section_limit_wlst_id IN NUMBER,
670 x_UOO_ID IN NUMBER,
671 x_ENROLLMENT_EXPECTED IN NUMBER,
672 x_ENROLLMENT_MINIMUM IN NUMBER,
673 x_ENROLLMENT_MAXIMUM IN NUMBER,
674 x_ADVANCE_MAXIMUM IN NUMBER,
675 x_WAITLIST_ALLOWED IN VARCHAR2,
676 x_MAX_STUDENTS_PER_WAITLIST IN NUMBER,
677 X_OVERRIDE_ENROLLMENT_MAX IN NUMBER,
678 x_max_auditors_allowed IN NUMBER,
679 X_MODE in VARCHAR2
680 ) AS
681 /*************************************************************
682 Created By : venagara
683 Date Created By : 2000/05/12
684 Purpose :
685 Know limitations, enhancements or remarks
686 Change History
687 Who When What
688 (reverse chronological order - newest change first)
689 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
690 ***************************************************************/
691
692 X_LAST_UPDATE_DATE DATE ;
693 X_LAST_UPDATED_BY NUMBER ;
694 X_LAST_UPDATE_LOGIN NUMBER ;
695 begin
696 X_LAST_UPDATE_DATE := SYSDATE;
697 if(X_MODE = 'I') then
698 X_LAST_UPDATED_BY := 1;
699 X_LAST_UPDATE_LOGIN := 0;
700 elsif (X_MODE = 'R') then
701 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
702 if X_LAST_UPDATED_BY is NULL then
703 X_LAST_UPDATED_BY := -1;
704 end if;
705 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
706 if X_LAST_UPDATE_LOGIN is NULL then
707 X_LAST_UPDATE_LOGIN := -1;
708 end if;
709 else
710 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
711 IGS_GE_MSG_STACK.ADD;
712 app_exception.raise_exception;
713 end if;
714 Before_DML(
715 p_action =>'UPDATE',
716 x_rowid =>X_ROWID,
717 x_unit_section_limit_wlst_id =>x_unit_section_limit_wlst_id,
718 x_uoo_id =>X_UOO_ID,
719 x_enrollment_expected =>X_ENROLLMENT_EXPECTED,
720 x_enrollment_minimum =>X_ENROLLMENT_MINIMUM,
721 x_enrollment_maximum =>X_ENROLLMENT_MAXIMUM,
722 x_advance_maximum =>X_ADVANCE_MAXIMUM,
723 x_waitlist_allowed =>X_WAITLIST_ALLOWED,
724 x_max_students_per_waitlist =>X_MAX_STUDENTS_PER_WAITLIST,
725 x_override_enrollment_max =>x_override_enrollment_max,
726 x_max_auditors_allowed =>x_max_auditors_allowed,
727 x_creation_date =>X_LAST_UPDATE_DATE,
728 x_created_by =>X_LAST_UPDATED_BY,
729 x_last_update_date =>X_LAST_UPDATE_DATE,
730 x_last_updated_by =>X_LAST_UPDATED_BY,
731 x_last_update_login =>X_LAST_UPDATE_LOGIN);
732 update IGS_PS_USEC_LIM_WLST set
733 UOO_ID = NEW_REFERENCES.UOO_ID,
734 ENROLLMENT_EXPECTED = NEW_REFERENCES.ENROLLMENT_EXPECTED,
735 ENROLLMENT_MINIMUM = NEW_REFERENCES.ENROLLMENT_MINIMUM,
736 ENROLLMENT_MAXIMUM = NEW_REFERENCES.ENROLLMENT_MAXIMUM,
737 ADVANCE_MAXIMUM = NEW_REFERENCES.ADVANCE_MAXIMUM,
738 WAITLIST_ALLOWED = NEW_REFERENCES.WAITLIST_ALLOWED,
739 MAX_STUDENTS_PER_WAITLIST = NEW_REFERENCES.MAX_STUDENTS_PER_WAITLIST,
740 OVERRIDE_ENROLLMENT_MAX = NEW_REFERENCES.OVERRIDE_ENROLLMENT_MAX,
741 max_auditors_allowed = new_references.max_auditors_allowed,
742 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
743 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
744 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
745 where ROWID = X_ROWID;
746 if (sql%notfound) then
747 raise no_data_found;
748 end if;
749
750 After_DML (
751 p_action => 'UPDATE' ,
752 x_rowid => X_ROWID
753 );
754 END UPDATE_ROW;
755
756 PROCEDURE ADD_ROW (
757 x_rowid IN OUT NOCOPY VARCHAR2,
758 x_unit_section_limit_wlst_id IN OUT NOCOPY NUMBER,
759 x_uoo_id IN NUMBER,
760 x_enrollment_expected IN NUMBER,
761 x_enrollment_minimum IN NUMBER,
762 x_enrollment_maximum IN NUMBER,
763 x_advance_maximum IN NUMBER,
764 x_waitlist_allowed IN VARCHAR2,
765 x_max_students_per_waitlist IN NUMBER,
766 x_override_enrollment_max IN NUMBER,
767 x_max_auditors_allowed IN NUMBER,
768 x_mode IN VARCHAR2
769 ) AS
770 /*************************************************************
771 Created By : venagara
772 Date Created By : 2000/05/12
773 Purpose :
774 Know limitations, enhancements or remarks
775 Change History
776 Who When What
777 (reverse chronological order - newest change first)
778 vvutukur 31-Oct-2002 Enh#2636716.Added new column max_auditors_allowed.
779 ***************************************************************/
780
781 CURSOR c1 IS
782 SELECT rowid
783 FROM igs_ps_usec_lim_wlst
784 WHERE unit_section_limit_waitlist_id = x_unit_section_limit_wlst_id;
785
786 BEGIN
787 OPEN c1;
788 FETCH c1 INTO x_rowid;
789 IF (c1%NOTFOUND) THEN
790 CLOSE c1;
791 INSERT_ROW (
792 x_rowid,
793 x_unit_section_limit_wlst_id,
794 x_uoo_id,
795 x_enrollment_expected,
796 x_enrollment_minimum,
797 x_enrollment_maximum,
798 x_advance_maximum,
799 x_waitlist_allowed,
800 x_max_students_per_waitlist,
801 x_override_enrollment_max,
802 x_max_auditors_allowed,
803 x_mode );
804 RETURN;
805 END IF;
806 CLOSE c1;
807 UPDATE_ROW (
808 x_rowid,
809 x_unit_section_limit_wlst_id,
810 x_uoo_id,
811 x_enrollment_expected,
812 x_enrollment_minimum,
813 x_enrollment_maximum,
814 x_advance_maximum,
815 x_waitlist_allowed,
816 x_max_students_per_waitlist,
817 x_override_enrollment_max,
818 x_max_auditors_allowed,
819 x_mode );
820 END ADD_ROW;
821
822 PROCEDURE DELETE_ROW (
823 x_rowid IN VARCHAR2
824 ) AS
825 /*************************************************************
826 Created By : venagara
827 Date Created By : 2000/05/12
828 Purpose :
829 Know limitations, enhancements or remarks
830 Change History
831 Who When What
832 (reverse chronological order - newest change first)
833 ***************************************************************/
834
835 BEGIN
836 Before_DML (
837 p_action => 'DELETE',
838 x_rowid => X_ROWID
839 );
840 DELETE FROM igs_ps_usec_lim_wlst
841 WHERE rowid = x_rowid;
842
843 IF (sql%NOTFOUND) THEN
844 RAISE NO_DATA_FOUND;
845 END IF;
846
847 After_DML (
848 p_action => 'DELETE',
849 x_rowid => X_ROWID
850 );
851 END DELETE_ROW;
852 END igs_ps_usec_lim_wlst_pkg;
[Home] [Help]