1 PACKAGE BODY igs_en_or_unit_wlst_pkg AS
2 /* $Header: IGSEI33B.pls 115.10 2003/09/18 03:42:40 svanukur ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_en_or_unit_wlst_all%RowType;
5 new_references igs_en_or_unit_wlst_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_unit_wlst_id IN NUMBER DEFAULT NULL,
11 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_start_dt IN DATE DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_max_stud_per_wlst IN NUMBER DEFAULT NULL,
15 x_smtanus_wlst_unit_enr_alwd IN VARCHAR2 DEFAULT NULL,
16 x_asses_chrg_for_wlst_stud 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 x_closed_flag IN VARCHAR2 DEFAULT 'N'
24 ) AS
25
26 /*************************************************************
27 Created By : smanglm
28 Date Created on : 26-MAY-2000
29 Purpose : Creation of TBH
30 Know limitations, enhancements or remarks
31 Change History
32 Who When What
33
34 (reverse chronological order - newest change first)
35 ***************************************************************/
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_EN_OR_UNIT_WLST_ALL
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.org_unit_wlst_id := x_org_unit_wlst_id;
61 new_references.org_unit_cd := x_org_unit_cd;
62 new_references.start_dt := x_start_dt;
63 new_references.cal_type := x_cal_type;
64 new_references.max_stud_per_wlst := x_max_stud_per_wlst;
65 new_references.smtanus_wlst_unit_enr_alwd := x_smtanus_wlst_unit_enr_alwd;
66 new_references.asses_chrg_for_wlst_stud := x_asses_chrg_for_wlst_stud;
67 new_references.closed_flag := x_closed_flag;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78 new_references.org_id := x_org_id ;
79 END Set_Column_Values;
80
81 PROCEDURE Check_Constraints (
82 Column_Name IN VARCHAR2 DEFAULT NULL,
83 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
84 /*************************************************************
85 Created By : smanglm
86 Date Created on : 26-MAY-2000
87 Purpose : Creation of TBH
88 Know limitations, enhancements or remarks
89 Change History
90 Who When What
91
92 (reverse chronological order - newest change first)
93 ***************************************************************/
94
95 BEGIN
96
97 IF column_name IS NULL THEN
98 NULL;
99
100 ELSIF UPPER(column_name) = 'ASSES_CHRG_FOR_WLST_STUD' THEN
101 new_references.asses_chrg_for_wlst_stud := column_value;
102 ELSIF UPPER(column_name) = 'SMTANUS_WLST_UNIT_ENR_ALWD' THEN
103 new_references.smtanus_wlst_unit_enr_alwd := column_value;
104 NULL;
105 END IF;
106
107 -- The following code checks for check constraints on the Columns.
108 IF Upper(Column_Name) = 'ASSES_CHRG_FOR_WLST_STUD' OR
109 Column_Name IS NULL THEN
110 IF NOT (new_references.asses_chrg_for_wlst_stud IN ('Y', 'N')) THEN
111 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END IF;
115 END IF;
116
117 -- The following code checks for check constraints on the Columns.
118 IF Upper(Column_Name) = 'SMTANUS_WLST_UNIT_ENR_ALWD' OR
119 Column_Name IS NULL THEN
120 IF NOT (new_references.smtanus_wlst_unit_enr_alwd IN ('Y', 'N')) THEN
121 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127
128 END Check_Constraints;
129
130 PROCEDURE Check_Uniqueness AS
131 /*************************************************************
132 Created By : smanglm
133 Date Created on : 26-MAY-2000
134 Purpose : Creation of TBH
135 Know limitations, enhancements or remarks
136 Change History
137 Who When What
138
139 (reverse chronological order - newest change first)
140 ***************************************************************/
141
142 begin
143 IF Get_Uk_For_Validation (
144 new_references.cal_type
145 ,new_references.org_unit_cd
146 ,new_references.start_dt
147 ) THEN
148 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
149 IGS_GE_MSG_STACK.ADD;
150 app_exception.raise_exception;
151 END IF;
152 END Check_Uniqueness ;
153 PROCEDURE Check_Parent_Existance AS
154 /*************************************************************
155 Created By : smanglm
156 Date Created on : 26-MAY-2000
157 Purpose : Creation of TBH
158 Know limitations, enhancements or remarks
159 Change History
160 Who When What
161
162 (reverse chronological order - newest change first)
163 ***************************************************************/
164
165 BEGIN
166
167 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
168 (old_references.start_dt = new_references.start_dt)) OR
169 ((new_references.org_unit_cd IS NULL) OR
170 (new_references.start_dt IS NULL))) THEN
171 NULL;
172 ELSIF NOT Igs_Or_Unit_Pkg.Get_PK_For_Validation (
173 new_references.org_unit_cd,
174 new_references.start_dt
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
182 IF (old_references.cal_type = new_references.cal_type) OR
183 (new_references.cal_type IS NULL) THEN
184
185 NULL;
186 ELSIF NOT Igs_Ca_Type_Pkg.Get_PK_For_Validation (
187 new_references.cal_type
188 ) THEN
189 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 END IF;
193
194 END Check_Parent_Existance;
195
196 PROCEDURE Check_Child_Existance IS
197 /*************************************************************
198 Created By : smanglm
199 Date Created on : 26-MAY-2000
200 Purpose : Creation of TBH
201 Know limitations, enhancements or remarks
202 Change History
203 Who When What
204
205 (reverse chronological order - newest change first)
206 ***************************************************************/
207
208 BEGIN
209
210 Igs_En_Orun_Wlst_Pri_Pkg.Get_FK_Igs_En_Or_Unit_Wlst (
211 old_references.org_unit_wlst_id
212 );
213
214 END Check_Child_Existance;
215
216 FUNCTION Get_PK_For_Validation (
217 x_org_unit_wlst_id IN NUMBER
218 ) RETURN BOOLEAN AS
219
220 /*************************************************************
221 Created By : smanglm
222 Date Created on : 26-MAY-2000
223 Purpose : Creation of TBH
224 Know limitations, enhancements or remarks
225 Change History
226 Who When What
227
228 (reverse chronological order - newest change first)
229 ***************************************************************/
230
231 CURSOR cur_rowid IS
232 SELECT rowid
233 FROM igs_en_or_unit_wlst_all
234 WHERE org_unit_wlst_id = x_org_unit_wlst_id
235 FOR UPDATE NOWAIT;
236
237 lv_rowid cur_rowid%RowType;
238
239 BEGIN
240
241 Open cur_rowid;
242 Fetch cur_rowid INTO lv_rowid;
243 IF (cur_rowid%FOUND) THEN
244 Close cur_rowid;
245 Return(TRUE);
246 ELSE
247 Close cur_rowid;
248 Return(FALSE);
249 END IF;
250 END Get_PK_For_Validation;
251
252 FUNCTION Get_UK_For_Validation (
253 x_cal_type IN VARCHAR2,
254 x_org_unit_cd IN VARCHAR2,
255 x_start_dt IN DATE
256 ) RETURN BOOLEAN AS
257
258 /*************************************************************
259 Created By : smanglm
260 Date Created on : 26-MAY-2000
261 Purpose : Creation of TBH
262 Know limitations, enhancements or remarks
263 Change History
264 Who When What
265
266 (reverse chronological order - newest change first)
267 ***************************************************************/
268
269 CURSOR cur_rowid IS
270 SELECT rowid
271 FROM igs_en_or_unit_wlst_all
272 WHERE cal_type = x_cal_type
273 AND org_unit_cd = x_org_unit_cd
274 AND start_dt = x_start_dt and ((l_rowid is null) or (rowid <> l_rowid))
275
276 ;
277 lv_rowid cur_rowid%RowType;
278
279 BEGIN
280
281 Open cur_rowid;
282 Fetch cur_rowid INTO lv_rowid;
283 IF (cur_rowid%FOUND) THEN
284 Close cur_rowid;
285 return (true);
286 ELSE
287 close cur_rowid;
288 return(false);
289 END IF;
290 END Get_UK_For_Validation ;
291 PROCEDURE Get_FK_Igs_Or_Unit (
292 x_org_unit_cd IN VARCHAR2,
293 x_start_dt IN DATE
294 ) AS
295
296 /*************************************************************
297 Created By : smanglm
298 Date Created on : 26-MAY-2000
299 Purpose : Creation of TBH
300 Know limitations, enhancements or remarks
301 Change History
302 Who When What
303
304 (reverse chronological order - newest change first)
305 ***************************************************************/
306
307 CURSOR cur_rowid IS
308 SELECT rowid
309 FROM igs_en_or_unit_wlst_all
310 WHERE org_unit_cd = x_org_unit_cd
311 AND start_dt = x_start_dt ;
312
313 lv_rowid cur_rowid%RowType;
314
315 BEGIN
316
317 Open cur_rowid;
318 Fetch cur_rowid INTO lv_rowid;
319 IF (cur_rowid%FOUND) THEN
320 Close cur_rowid;
321 Fnd_Message.Set_Name ('IGS', 'IGS_EN_OUW_OU_FK');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 Return;
325 END IF;
326 Close cur_rowid;
327
328 END Get_FK_Igs_Or_Unit;
329
330 PROCEDURE Get_FK_Igs_Ca_Inst (
331 x_cal_type IN VARCHAR2,
332 x_sequence_number IN NUMBER
333 ) AS
334
335 /*************************************************************
336 Created By : smanglm
337 Date Created on : 26-MAY-2000
338 Purpose : Creation of TBH
339 Know limitations, enhancements or remarks
340 Change History
341 Who When What
342
343 (reverse chronological order - newest change first)
344 svanukur 02-sep-2003 commenting since sequence_number is obsoleted
345 as part of waitlist enhancement build 3052426
346 and to avoid dependency on PSP build #3045007
347 ***************************************************************/
348
349 /* CURSOR cur_rowid IS
350 SELECT rowid
351 FROM igs_en_or_unit_wlst_all
352 WHERE cal_type = x_cal_type
353 AND sequence_number = x_sequence_number ;
354
355 lv_rowid cur_rowid%RowType;
356
357 BEGIN
358
359 Open cur_rowid;
360 Fetch cur_rowid INTO lv_rowid;
361 IF (cur_rowid%FOUND) THEN
362 Close cur_rowid;
363 Fnd_Message.Set_Name ('IGS', 'IGS_EN_OUW_OU_FK');
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 Return;
367 END IF;
368 Close cur_rowid; */
369 BEGIN
370 NULL;
371
372 END Get_FK_Igs_Ca_Inst;
373
374 PROCEDURE Get_FK_Igs_Ca_Type (
375 x_cal_type IN VARCHAR2
376 ) AS
377
378 /*************************************************************
379 Created By : smanglm
380 Date Created on : 26-MAY-2000
381 Purpose : Creation of TBH
382 Know limitations, enhancements or remarks
383 Change History
384 Who When What
385
386 (reverse chronological order - newest change first)
387 svanukur 02-sep-2003 created as part of waitlist enhancement build 3052426
388 ***************************************************************/
389
390 CURSOR cur_rowid IS
391 SELECT rowid
392 FROM igs_en_or_unit_wlst_all
393 WHERE cal_type = x_cal_type ;
394
395
396 lv_rowid cur_rowid%RowType;
397
398 BEGIN
399
400 Open cur_rowid;
401 Fetch cur_rowid INTO lv_rowid;
402 IF (cur_rowid%FOUND) THEN
403 Close cur_rowid;
404 Fnd_Message.Set_Name ('IGS', 'IGS_EN_OUW_OU_FK');
405 IGS_GE_MSG_STACK.ADD;
406 App_Exception.Raise_Exception;
407 Return;
408 END IF;
409 Close cur_rowid;
410 END Get_FK_Igs_Ca_Type;
411
412 PROCEDURE Before_DML (
413 p_action IN VARCHAR2,
414 x_rowid IN VARCHAR2 ,
415 x_org_unit_wlst_id IN NUMBER ,
416 x_org_unit_cd IN VARCHAR2 ,
417 x_start_dt IN DATE ,
418 x_cal_type IN VARCHAR2 ,
419 x_max_stud_per_wlst IN NUMBER ,
420 x_smtanus_wlst_unit_enr_alwd IN VARCHAR2 ,
421 x_asses_chrg_for_wlst_stud IN VARCHAR2 ,
422 x_creation_date IN DATE ,
423 x_created_by IN NUMBER ,
424 x_last_update_date IN DATE ,
425 x_last_updated_by IN NUMBER ,
426 x_last_update_login IN NUMBER ,
427 x_org_id IN NUMBER ,
428 x_closed_flag IN VARCHAR2
429 ) AS
430 /*************************************************************
431 Created By : smanglm
432 Date Created on : 26-MAY-2000
433 Purpose : Creation of TBH
434 Know limitations, enhancements or remarks
435 Change History
436 Who When What
437
438 (reverse chronological order - newest change first)
439 ***************************************************************/
440
441 BEGIN
442 Set_Column_Values (
443 p_action,
444 x_rowid,
445 x_org_unit_wlst_id,
446 x_org_unit_cd,
447 x_start_dt,
448 x_cal_type,
449 x_max_stud_per_wlst,
450 x_smtanus_wlst_unit_enr_alwd,
451 x_asses_chrg_for_wlst_stud,
452 x_creation_date,
453 x_created_by,
454 x_last_update_date,
455 x_last_updated_by,
456 x_last_update_login,
457 x_org_id,
458 x_closed_flag
459 );
460
461 IF (p_action = 'INSERT') THEN
462 -- Call all the procedures related to Before Insert.
463 Null;
464 IF Get_Pk_For_Validation(
465 new_references.org_unit_wlst_id) THEN
466 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
467 IGS_GE_MSG_STACK.ADD;
468 App_Exception.Raise_Exception;
469 END IF;
470 Check_Uniqueness;
471 Check_Constraints;
472 Check_Parent_Existance;
473 ELSIF (p_action = 'UPDATE') THEN
474 -- Call all the procedures related to Before Update.
475 Null;
476 Check_Uniqueness;
477 Check_Constraints;
478 Check_Parent_Existance;
479 ELSIF (p_action = 'DELETE') THEN
480 -- Call all the procedures related to Before Delete.
481 Null;
482 Check_Child_Existance;
483 ELSIF (p_action = 'VALIDATE_INSERT') THEN
484 -- Call all the procedures related to Before Insert.
485
486 IF Get_PK_For_Validation (
487 new_references.org_unit_wlst_id) THEN
488 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
489 IGS_GE_MSG_STACK.ADD;
490 App_Exception.Raise_Exception;
491 END IF;
492 Check_Uniqueness;
493 Check_Constraints;
494 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
495 Check_Uniqueness;
496 Check_Constraints;
497 ELSIF (p_action = 'VALIDATE_DELETE') THEN
498 Check_Child_Existance;
499 END IF;
500
501 END Before_DML;
502
503 PROCEDURE After_DML (
504 p_action IN VARCHAR2,
505 x_rowid IN VARCHAR2
506 ) IS
507 /*************************************************************
508 Created By : smanglm
509 Date Created on : 26-MAY-2000
510 Purpose : Creation of TBH
511 Know limitations, enhancements or remarks
512 Change History
513 Who When What
514
515 (reverse chronological order - newest change first)
516 ***************************************************************/
517
518 BEGIN
519
520 l_rowid := x_rowid;
521
522 IF (p_action = 'INSERT') THEN
523 -- Call all the procedures related to After Insert.
524 Null;
525 ELSIF (p_action = 'UPDATE') THEN
526 -- Call all the procedures related to After Update.
527 Null;
528 ELSIF (p_action = 'DELETE') THEN
529 -- Call all the procedures related to After Delete.
530 Null;
531 END IF;
532
533 END After_DML;
534
535 procedure INSERT_ROW (
536 X_ROWID in out NOCOPY VARCHAR2,
537 x_ORG_UNIT_WLST_ID IN OUT NOCOPY NUMBER,
538 x_ORG_UNIT_CD IN VARCHAR2,
539 x_START_DT IN DATE,
540 x_CAL_TYPE IN VARCHAR2,
541 x_MAX_STUD_PER_WLST IN NUMBER,
542 x_SMTANUS_WLST_UNIT_ENR_ALWD IN VARCHAR2,
543 x_ASSES_CHRG_FOR_WLST_STUD IN VARCHAR2,
544 X_MODE in VARCHAR2 ,
545 x_org_id IN NUMBER ,
546 x_closed_flag in VARCHAR2
547 ) AS
548 /*************************************************************
549 Created By : smanglm
550 Date Created on : 26-MAY-2000
551 Purpose : Creation of TBH
552 Know limitations, enhancements or remarks
553 Change History
554 Who When What
555
556 (reverse chronological order - newest change first)
557 ***************************************************************/
558
559 cursor C is select ROWID from IGS_EN_OR_UNIT_WLST_ALL
560 where ORG_UNIT_WLST_ID= X_ORG_UNIT_WLST_ID
561 ;
562 X_LAST_UPDATE_DATE DATE ;
563 X_LAST_UPDATED_BY NUMBER ;
564 X_LAST_UPDATE_LOGIN NUMBER ;
565 begin
566 X_LAST_UPDATE_DATE := SYSDATE;
567 if(X_MODE = 'I') then
568 X_LAST_UPDATED_BY := 1;
569 X_LAST_UPDATE_LOGIN := 0;
570 elsif (X_MODE = 'R') then
571 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
572 if X_LAST_UPDATED_BY is NULL then
573 X_LAST_UPDATED_BY := -1;
574 end if;
575 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
576 if X_LAST_UPDATE_LOGIN is NULL then
577 X_LAST_UPDATE_LOGIN := -1;
578 end if;
579 else
580 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
581 IGS_GE_MSG_STACK.ADD;
582 app_exception.raise_exception;
583 end if;
584 SELECT IGS_EN_OR_UNIT_WLST_S.NEXTVAL
585 INTO x_org_unit_wlst_id
586 FROM dual;
587 Before_DML(
588 p_action=>'INSERT',
589 x_rowid=>X_ROWID,
590 x_org_unit_wlst_id=>X_ORG_UNIT_WLST_ID,
591 x_org_unit_cd=>X_ORG_UNIT_CD,
592 x_start_dt=>X_START_DT,
593 x_cal_type=>X_CAL_TYPE,
594 x_max_stud_per_wlst=>X_MAX_STUD_PER_WLST,
595 x_smtanus_wlst_unit_enr_alwd=>X_SMTANUS_WLST_UNIT_ENR_ALWD,
596 x_asses_chrg_for_wlst_stud=>X_ASSES_CHRG_FOR_WLST_STUD,
597 x_creation_date=>X_LAST_UPDATE_DATE,
598 x_created_by=>X_LAST_UPDATED_BY,
599 x_last_update_date=>X_LAST_UPDATE_DATE,
600 x_last_updated_by=>X_LAST_UPDATED_BY,
601 x_last_update_login=>X_LAST_UPDATE_LOGIN,
602 x_org_id => igs_ge_gen_003.get_org_id,
603 x_closed_flag => X_CLOSED_FLAG);
604 insert into IGS_EN_OR_UNIT_WLST_ALL (
605 ORG_UNIT_WLST_ID
606 ,ORG_UNIT_CD
607 ,START_DT
608 ,CAL_TYPE
609 ,MAX_STUD_PER_WLST
610 ,SMTANUS_WLST_UNIT_ENR_ALWD
611 ,ASSES_CHRG_FOR_WLST_STUD
612 ,CREATION_DATE
613 ,CREATED_BY
614 ,LAST_UPDATE_DATE
615 ,LAST_UPDATED_BY
616 ,LAST_UPDATE_LOGIN,
617 org_id,
618 closed_flag
619 ) values (
620 NEW_REFERENCES.ORG_UNIT_WLST_ID
621 ,NEW_REFERENCES.ORG_UNIT_CD
622 ,NEW_REFERENCES.START_DT
623 ,NEW_REFERENCES.CAL_TYPE
624 ,NEW_REFERENCES.MAX_STUD_PER_WLST
625 ,NEW_REFERENCES.SMTANUS_WLST_UNIT_ENR_ALWD
626 ,NEW_REFERENCES.ASSES_CHRG_FOR_WLST_STUD
627 ,X_LAST_UPDATE_DATE
628 ,X_LAST_UPDATED_BY
629 ,X_LAST_UPDATE_DATE
630 ,X_LAST_UPDATED_BY
631 ,X_LAST_UPDATE_LOGIN,
632 NEW_REFERENCES.org_id ,
633 NEW_REFERENCES.closed_flag
634 );
635 open c;
636 fetch c into X_ROWID;
637 if (c%notfound) then
638 close c;
639 raise no_data_found;
640 end if;
641 close c;
642 After_DML (
643 p_action => 'INSERT' ,
644 x_rowid => X_ROWID );
645 end INSERT_ROW;
646 procedure LOCK_ROW (
647 X_ROWID in VARCHAR2,
648 x_ORG_UNIT_WLST_ID IN NUMBER,
649 x_ORG_UNIT_CD IN VARCHAR2,
650 x_START_DT IN DATE,
651 x_CAL_TYPE IN VARCHAR2,
652 x_MAX_STUD_PER_WLST IN NUMBER,
653 x_SMTANUS_WLST_UNIT_ENR_ALWD IN VARCHAR2,
654 x_ASSES_CHRG_FOR_WLST_STUD IN VARCHAR2,
655 x_closed_flag IN VARCHAR2) AS
656 /*************************************************************
657 Created By : smanglm
658 Date Created on : 26-MAY-2000
659 Purpose : Creation of TBH
660 Know limitations, enhancements or remarks
661 Change History
662 Who When What
663
664 (reverse chronological order - newest change first)
665 ***************************************************************/
666
667 cursor c1 is select
668 ORG_UNIT_CD
669 , START_DT
670 , CAL_TYPE
671 , MAX_STUD_PER_WLST
672 , SMTANUS_WLST_UNIT_ENR_ALWD
673 , ASSES_CHRG_FOR_WLST_STUD
674 , CLOSED_FLAG
675 from IGS_EN_OR_UNIT_WLST_ALL
676 where ROWID = X_ROWID
677 for update nowait;
678 tlinfo c1%rowtype;
679 begin
680 open c1;
681 fetch c1 into tlinfo;
682 if (c1%notfound) then
683 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
684 IGS_GE_MSG_STACK.ADD;
685 close c1;
686 app_exception.raise_exception;
687 return;
688 end if;
689 close c1;
690 if ( ( tlinfo.ORG_UNIT_CD = X_ORG_UNIT_CD)
691 AND (tlinfo.START_DT = X_START_DT)
692 AND (tlinfo.CAL_TYPE = X_CAL_TYPE)
693 AND ((tlinfo.MAX_STUD_PER_WLST = X_MAX_STUD_PER_WLST)
694 OR ((tlinfo.MAX_STUD_PER_WLST is null)
695 AND (X_MAX_STUD_PER_WLST is null)))
696 AND (tlinfo.SMTANUS_WLST_UNIT_ENR_ALWD = X_SMTANUS_WLST_UNIT_ENR_ALWD)
697 AND (tlinfo.ASSES_CHRG_FOR_WLST_STUD = X_ASSES_CHRG_FOR_WLST_STUD)
698 AND (tlinfo.CLOSED_FLAG = X_CLOSED_FLAG)
699 ) then
700 null;
701 else
702 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
703 IGS_GE_MSG_STACK.ADD;
704 app_exception.raise_exception;
705 end if;
706 return;
707 end LOCK_ROW;
708 Procedure UPDATE_ROW (
709 X_ROWID in VARCHAR2,
710 x_ORG_UNIT_WLST_ID IN NUMBER,
711 x_ORG_UNIT_CD IN VARCHAR2,
712 x_START_DT IN DATE,
713 x_CAL_TYPE IN VARCHAR2,
714 x_MAX_STUD_PER_WLST IN NUMBER,
715 x_SMTANUS_WLST_UNIT_ENR_ALWD IN VARCHAR2,
716 x_ASSES_CHRG_FOR_WLST_STUD IN VARCHAR2,
717 X_MODE in VARCHAR2 default 'R',
718 x_closed_flag IN VARCHAR2
719 ) AS
720 /*************************************************************
721 Created By : smanglm
722 Date Created on : 26-MAY-2000
723 Purpose : Creation of TBH
724 Know limitations, enhancements or remarks
725 Change History
726 Who When What
727
728 (reverse chronological order - newest change first)
729 ***************************************************************/
730
731 X_LAST_UPDATE_DATE DATE ;
732 X_LAST_UPDATED_BY NUMBER ;
733 X_LAST_UPDATE_LOGIN NUMBER ;
734 begin
735 X_LAST_UPDATE_DATE := SYSDATE;
736 if(X_MODE = 'I') then
737 X_LAST_UPDATED_BY := 1;
738 X_LAST_UPDATE_LOGIN := 0;
739 elsif (X_MODE = 'R') then
740 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
741 if X_LAST_UPDATED_BY is NULL then
742 X_LAST_UPDATED_BY := -1;
743 end if;
744 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
745 if X_LAST_UPDATE_LOGIN is NULL then
746 X_LAST_UPDATE_LOGIN := -1;
747 end if;
748 else
749 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
750 IGS_GE_MSG_STACK.ADD;
751 app_exception.raise_exception;
752 end if;
753 Before_DML(
754 p_action=>'UPDATE',
755 x_rowid=>X_ROWID,
756 x_org_unit_wlst_id=>X_ORG_UNIT_WLST_ID,
757 x_org_unit_cd=>X_ORG_UNIT_CD,
758 x_start_dt=>X_START_DT,
759 x_cal_type=>X_CAL_TYPE,
760 x_max_stud_per_wlst=>X_MAX_STUD_PER_WLST,
761 x_smtanus_wlst_unit_enr_alwd=>X_SMTANUS_WLST_UNIT_ENR_ALWD,
762 x_asses_chrg_for_wlst_stud=>X_ASSES_CHRG_FOR_WLST_STUD,
763 x_creation_date=>X_LAST_UPDATE_DATE,
764 x_created_by=>X_LAST_UPDATED_BY,
765 x_last_update_date=>X_LAST_UPDATE_DATE,
766 x_last_updated_by=>X_LAST_UPDATED_BY,
767 x_last_update_login=>X_LAST_UPDATE_LOGIN,
768 x_closed_flag => X_CLOSED_FLAG);
769 update IGS_EN_OR_UNIT_WLST_ALL set
770 ORG_UNIT_CD = NEW_REFERENCES.ORG_UNIT_CD,
771 START_DT = NEW_REFERENCES.START_DT,
772 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
773 MAX_STUD_PER_WLST = NEW_REFERENCES.MAX_STUD_PER_WLST,
774 SMTANUS_WLST_UNIT_ENR_ALWD = NEW_REFERENCES.SMTANUS_WLST_UNIT_ENR_ALWD,
775 ASSES_CHRG_FOR_WLST_STUD = NEW_REFERENCES.ASSES_CHRG_FOR_WLST_STUD,
776 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
777 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
778 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
779 CLOSED_FLAG = NEW_REFERENCES.CLOSED_FLAG
780 where ROWID = X_ROWID;
781 if (sql%notfound) then
782 raise no_data_found;
783 end if;
784
785 After_DML (
786 p_action => 'UPDATE' ,
787 x_rowid => X_ROWID
788 );
789 end UPDATE_ROW;
790 procedure ADD_ROW (
791 X_ROWID in out NOCOPY VARCHAR2,
792 x_ORG_UNIT_WLST_ID IN OUT NOCOPY NUMBER,
793 x_ORG_UNIT_CD IN VARCHAR2,
794 x_START_DT IN DATE,
795 x_CAL_TYPE IN VARCHAR2,
796 x_MAX_STUD_PER_WLST IN NUMBER,
797 x_SMTANUS_WLST_UNIT_ENR_ALWD IN VARCHAR2,
798 x_ASSES_CHRG_FOR_WLST_STUD IN VARCHAR2,
799 X_MODE in VARCHAR2 ,
800 x_org_id IN NUMBER ,
801 x_closed_flag IN VARCHAR2
802 ) AS
803 /*************************************************************
804 Created By : smanglm
805 Date Created on : 26-MAY-2000
806 Purpose : Creation of TBH
807 Know limitations, enhancements or remarks
808 Change History
809 Who When What
810
811 (reverse chronological order - newest change first)
812 ***************************************************************/
813
814 cursor c1 is select ROWID from IGS_EN_OR_UNIT_WLST_ALL
815 where ORG_UNIT_WLST_ID= X_ORG_UNIT_WLST_ID
816 ;
817 begin
818 open c1;
819 fetch c1 into X_ROWID;
820 if (c1%notfound) then
821 close c1;
822 INSERT_ROW (
823 X_ROWID,
824 X_ORG_UNIT_WLST_ID,
825 X_ORG_UNIT_CD,
826 X_START_DT,
827 X_CAL_TYPE,
828 X_MAX_STUD_PER_WLST,
829 X_SMTANUS_WLST_UNIT_ENR_ALWD,
830 X_ASSES_CHRG_FOR_WLST_STUD,
831 X_MODE ,
832 x_org_id,
833 X_CLOSED_FLAG);
834 return;
835 end if;
836 close c1;
837 UPDATE_ROW (
838 X_ROWID,
839 X_ORG_UNIT_WLST_ID,
840 X_ORG_UNIT_CD,
841 X_START_DT,
842 X_CAL_TYPE,
843 X_MAX_STUD_PER_WLST,
844 X_SMTANUS_WLST_UNIT_ENR_ALWD,
845 X_ASSES_CHRG_FOR_WLST_STUD,
846 X_MODE,
847 X_CLOSED_FLAG);
848 end ADD_ROW;
849 procedure DELETE_ROW (
850 X_ROWID in VARCHAR2
851 ) AS
852 /*************************************************************
853 Created By : smanglm
854 Date Created on : 26-MAY-2000
855 Purpose : Creation of TBH
856 Know limitations, enhancements or remarks
857 Change History
858 Who When What
859
860 (reverse chronological order - newest change first)
861 ***************************************************************/
862
863 begin
864 Before_DML (
865 p_action => 'DELETE',
866 x_rowid => X_ROWID
867 );
868 delete from IGS_EN_OR_UNIT_WLST_ALL
869 where ROWID = X_ROWID;
870 if (sql%notfound) then
871 raise no_data_found;
872 end if;
873 After_DML (
874 p_action => 'DELETE',
875 x_rowid => X_ROWID
876 );
877 end DELETE_ROW;
878 END igs_en_or_unit_wlst_pkg;
[Home] [Help]