1 PACKAGE BODY igs_ps_uofr_wlst_pri_pkg AS
2 /* $Header: IGSPI96B.pls 115.9 2003/12/05 13:22:37 sarakshi ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_uofr_wlst_pri%RowType;
5 new_references igs_ps_uofr_wlst_pri%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_ofr_wl_priority_id IN NUMBER DEFAULT NULL,
11 x_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_calender_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_priority_number IN NUMBER DEFAULT NULL,
16 x_priority_value 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 ) AS
23
24 /*************************************************************
25 Created By : smanglm
26 Date Created On : 4-MAY-2000
27 Purpose : Creation of TBH
28 Know limitations, enhancements or remarks
29 Change History
30 Who When What
31
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_PS_UOFR_WLST_PRI
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.unit_ofr_waitlist_priority_id := x_unit_ofr_wl_priority_id;
59 new_references.unit_cd := x_unit_cd;
60 new_references.version_number := x_version_number;
61 new_references.calender_type := x_calender_type;
62 new_references.ci_sequence_number := x_ci_sequence_number;
63 new_references.priority_number := x_priority_number;
64 new_references.priority_value := x_priority_value;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END Set_Column_Values;
77
78 PROCEDURE Check_Constraints (
79 Column_Name IN VARCHAR2 DEFAULT NULL,
80 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
81 /*************************************************************
82 Created By : smanglm
83 Date Created On : 4-MAY-2000
84 Purpose : Creation of TBH
85 Know limitations, enhancements or remarks
86 Change History
87 Who When What
88
89 (reverse chronological order - newest change first)
90 ***************************************************************/
91
92 BEGIN
93
94 IF column_name IS NULL THEN
95 NULL;
96 NULL;
97 END IF;
98
99
100
101
102 END Check_Constraints;
103
104 PROCEDURE Check_Uniqueness AS
105 /*************************************************************
106 Created By : smanglm
107 Date Created On : 4-MAY-2000
108 Purpose : Creation of TBH
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115
116 begin
117 IF Get_Uk_For_Validation (
118 new_references.calender_type
119 ,new_references.ci_sequence_number
120 ,new_references.priority_value
121 ,new_references.unit_cd
122 ,new_references.version_number
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 : smanglm
132 Date Created On : 4-MAY-2000
133 Purpose : Creation of TBH
134 Know limitations, enhancements or remarks
135 Change History
136 Who When What
137 smvk 31-Jan-2003 Bug # 2532094. Added the foreign key checking with igs_lookups_view.
138 (reverse chronological order - newest change first)
139 ***************************************************************/
140
141 BEGIN
142
143 IF (((old_references.unit_cd = new_references.unit_cd) AND
144 (old_references.version_number = new_references.version_number) AND
145 (old_references.calender_type = new_references.calender_type) AND
146 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
147 ((new_references.unit_cd IS NULL) OR
148 (new_references.version_number IS NULL) OR
149 (new_references.calender_type IS NULL) OR
150 (new_references.ci_sequence_number IS NULL))) THEN
151 NULL;
152 ELSIF NOT Igs_Ps_Unit_Ofr_Pat_Pkg.Get_PK_For_Validation (
153 new_references.unit_cd,
154 new_references.version_number,
155 new_references.calender_type,
156 new_references.ci_sequence_number
157 ) THEN
158 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162
163 IF (((old_references.priority_value = new_references.priority_value)) OR
164 ((new_references.priority_value IS NULL))) THEN
165 NULL;
166 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation('UNIT_WAITLIST',
167 new_references.priority_value) THEN
168 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172
173 END Check_Parent_Existance;
174
175 PROCEDURE Check_Child_Existance IS
176 /*************************************************************
177 Created By : smanglm
178 Date Created On : 4-MAY-2000
179 Purpose : Creation of TBH
180 Know limitations, enhancements or remarks
181 Change History
182 Who When What
183
184 (reverse chronological order - newest change first)
185 ***************************************************************/
186
187 BEGIN
188
189 Igs_Ps_Uofr_Wlst_Prf_Pkg.Get_FK_Igs_Ps_Uofr_Wlst_Pri (
190 old_references.unit_ofr_waitlist_priority_id
191 );
192
193 END Check_Child_Existance;
194
195 FUNCTION Get_PK_For_Validation (
196 x_unit_ofr_wl_priority_id IN NUMBER
197 ) RETURN BOOLEAN AS
198
199 /*************************************************************
200 Created By : smanglm
201 Date Created On : 4-MAY-2000
202 Purpose : Creation of TBH
203 Know limitations, enhancements or remarks
204 Change History
205 Who When What
206
207 (reverse chronological order - newest change first)
208 ***************************************************************/
209
210 CURSOR cur_rowid IS
211 SELECT rowid
212 FROM igs_ps_uofr_wlst_pri
213 WHERE unit_ofr_waitlist_priority_id = x_unit_ofr_wl_priority_id
214 FOR UPDATE NOWAIT;
215
216 lv_rowid cur_rowid%RowType;
217
218 BEGIN
219
220 Open cur_rowid;
221 Fetch cur_rowid INTO lv_rowid;
222 IF (cur_rowid%FOUND) THEN
223 Close cur_rowid;
224 Return(TRUE);
225 ELSE
226 Close cur_rowid;
227 Return(FALSE);
228 END IF;
229 END Get_PK_For_Validation;
230
231 FUNCTION Get_UK_For_Validation (
232 x_calender_type IN VARCHAR2,
233 x_ci_sequence_number IN NUMBER,
234 x_priority_value IN VARCHAR2,
235 x_unit_cd IN VARCHAR2,
236 x_version_number IN NUMBER
237 ) RETURN BOOLEAN AS
238
239 /*************************************************************
240 Created By : smanglm
241 Date Created On : 4-MAY-2000
242 Purpose : Creation of TBH
243 Know limitations, enhancements or remarks
244 Change History
245 Who When What
246 sarakshi 03-Dec-2003 Bug#3168726,taken off priority_number from teh where condition.
247 (reverse chronological order - newest change first)
248 ***************************************************************/
249
250 CURSOR cur_rowid IS
251 SELECT rowid
252 FROM igs_ps_uofr_wlst_pri
253 WHERE calender_type = x_calender_type
254 AND ci_sequence_number = x_ci_sequence_number
255 AND priority_value = x_priority_value
256 AND unit_cd = x_unit_cd
257 AND version_number = x_version_number and ((l_rowid is null) or (rowid <> l_rowid));
258 lv_rowid cur_rowid%RowType;
259
260 BEGIN
261
262 Open cur_rowid;
263 Fetch cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 Close cur_rowid;
266 return (true);
267 ELSE
268 close cur_rowid;
269 return(false);
270 END IF;
271 END Get_UK_For_Validation ;
272 PROCEDURE Get_FK_Igs_Ps_Unit_Ofr_Pat (
273 x_unit_cd IN VARCHAR2,
274 x_version_number IN NUMBER,
275 x_cal_type IN VARCHAR2,
276 x_ci_sequence_number IN NUMBER
277 ) AS
278
279 /*************************************************************
280 Created By : smanglm
281 Date Created On : 4-MAY-2000
282 Purpose : Creation of TBH
283 Know limitations, enhancements or remarks
284 Change History
285 Who When What
286
287 (reverse chronological order - newest change first)
288 ***************************************************************/
289
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM igs_ps_uofr_wlst_pri
293 WHERE unit_cd = x_unit_cd
294 AND version_number = x_version_number
295 AND calender_type = x_cal_type
296 AND ci_sequence_number = x_ci_sequence_number ;
297
298 lv_rowid cur_rowid%RowType;
299
300 BEGIN
301
302 Open cur_rowid;
303 Fetch cur_rowid INTO lv_rowid;
304 IF (cur_rowid%FOUND) THEN
305 Close cur_rowid;
306 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 Return;
310 END IF;
311 Close cur_rowid;
312
313 END Get_FK_Igs_Ps_Unit_Ofr_Pat;
314
315 PROCEDURE Before_DML (
316 p_action IN VARCHAR2,
317 x_rowid IN VARCHAR2 DEFAULT NULL,
318 x_unit_ofr_wl_priority_id IN NUMBER DEFAULT NULL,
319 x_unit_cd IN VARCHAR2 DEFAULT NULL,
320 x_version_number IN NUMBER DEFAULT NULL,
321 x_calender_type IN VARCHAR2 DEFAULT NULL,
322 x_ci_sequence_number IN NUMBER DEFAULT NULL,
323 x_priority_number IN NUMBER DEFAULT NULL,
324 x_priority_value IN VARCHAR2 DEFAULT NULL,
325 x_creation_date IN DATE DEFAULT NULL,
326 x_created_by IN NUMBER DEFAULT NULL,
327 x_last_update_date IN DATE DEFAULT NULL,
328 x_last_updated_by IN NUMBER DEFAULT NULL,
329 x_last_update_login IN NUMBER DEFAULT NULL
330 ) AS
331 /*************************************************************
332 Created By : smanglm
333 Date Created On : 4-MAY-2000
334 Purpose : Creation of TBH
335 Know limitations, enhancements or remarks
336 Change History
337 Who When What
338
339 (reverse chronological order - newest change first)
340 ***************************************************************/
341
342 BEGIN
343
344 Set_Column_Values (
345 p_action,
346 x_rowid,
347 x_unit_ofr_wl_priority_id,
348 x_unit_cd,
349 x_version_number,
350 x_calender_type,
351 x_ci_sequence_number,
352 x_priority_number,
353 x_priority_value,
354 x_creation_date,
355 x_created_by,
356 x_last_update_date,
357 x_last_updated_by,
358 x_last_update_login
359 );
360
361 IF (p_action = 'INSERT') THEN
362 -- Call all the procedures related to Before Insert.
363 Null;
364 IF Get_Pk_For_Validation(
365 new_references.unit_ofr_waitlist_priority_id) THEN
366 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
367 IGS_GE_MSG_STACK.ADD;
368 App_Exception.Raise_Exception;
369 END IF;
370 Check_Uniqueness;
371 Check_Constraints;
372 Check_Parent_Existance;
373 ELSIF (p_action = 'UPDATE') THEN
374 -- Call all the procedures related to Before Update.
375 Null;
376 Check_Uniqueness;
377 Check_Constraints;
378 Check_Parent_Existance;
379 ELSIF (p_action = 'DELETE') THEN
380 -- Call all the procedures related to Before Delete.
381 Null;
382 Check_Child_Existance;
383 ELSIF (p_action = 'VALIDATE_INSERT') THEN
384 -- Call all the procedures related to Before Insert.
385 IF Get_PK_For_Validation (
386 new_references.unit_ofr_waitlist_priority_id) THEN
387 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
388 IGS_GE_MSG_STACK.ADD;
389 App_Exception.Raise_Exception;
390 END IF;
391 Check_Uniqueness;
392 Check_Constraints;
393 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
394 Check_Uniqueness;
395 Check_Constraints;
396 ELSIF (p_action = 'VALIDATE_DELETE') THEN
397 Check_Child_Existance;
398 END IF;
399
400 END Before_DML;
401
402 PROCEDURE After_DML (
403 p_action IN VARCHAR2,
404 x_rowid IN VARCHAR2
405 ) IS
406 /*************************************************************
407 Created By : smanglm
408 Date Created On : 4-MAY-2000
409 Purpose : Creation of TBH
410 Know limitations, enhancements or remarks
411 Change History
412 Who When What
413
414 (reverse chronological order - newest change first)
415 ***************************************************************/
416
417 BEGIN
418
419 l_rowid := x_rowid;
420
421 IF (p_action = 'INSERT') THEN
422 -- Call all the procedures related to After Insert.
423 Null;
424 ELSIF (p_action = 'UPDATE') THEN
425 -- Call all the procedures related to After Update.
426 Null;
427 ELSIF (p_action = 'DELETE') THEN
428 -- Call all the procedures related to After Delete.
429 Null;
430 END IF;
431 l_rowid:=NULL;
432 END After_DML;
433
434 procedure INSERT_ROW (
435 X_ROWID in out NOCOPY VARCHAR2,
436 x_UNIT_OFR_WL_PRIORITY_ID IN OUT NOCOPY NUMBER,
437 x_UNIT_CD IN VARCHAR2,
438 x_VERSION_NUMBER IN NUMBER,
439 x_CALENDER_TYPE IN VARCHAR2,
440 x_CI_SEQUENCE_NUMBER IN NUMBER,
441 x_PRIORITY_NUMBER IN NUMBER,
442 x_PRIORITY_VALUE IN VARCHAR2,
443 X_MODE in VARCHAR2 default 'R'
444 ) AS
445 /*************************************************************
446 Created By : smanglm
447 Date Created On : 4-MAY-2000
448 Purpose : Creation of TBH
449 Know limitations, enhancements or remarks
450 Change History
451 Who When What
452
453 (reverse chronological order - newest change first)
454 ***************************************************************/
455
456 cursor C is select ROWID from IGS_PS_UOFR_WLST_PRI
457 where UNIT_OFR_WAITLIST_PRIORITY_ID= X_UNIT_OFR_WL_PRIORITY_ID;
458 X_LAST_UPDATE_DATE DATE ;
459 X_LAST_UPDATED_BY NUMBER ;
460 X_LAST_UPDATE_LOGIN NUMBER ;
461 begin
462 X_LAST_UPDATE_DATE := SYSDATE;
463 if(X_MODE = 'I') then
464 X_LAST_UPDATED_BY := 1;
465 X_LAST_UPDATE_LOGIN := 0;
466 elsif (X_MODE = 'R') then
467 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
468 if X_LAST_UPDATED_BY is NULL then
469 X_LAST_UPDATED_BY := -1;
470 end if;
471 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
472 if X_LAST_UPDATE_LOGIN is NULL then
473 X_LAST_UPDATE_LOGIN := -1;
474 end if;
475 else
476 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
477 IGS_GE_MSG_STACK.ADD;
478 app_exception.raise_exception;
479 end if;
480 SELECT IGS_PS_UOFR_WLST_PRI_S.NEXTVAL
481 INTO X_UNIT_OFR_WL_PRIORITY_ID
482 FROM dual;
483 Before_DML(
484 p_action=>'INSERT',
485 x_rowid=>X_ROWID,
486 x_unit_ofr_wl_priority_id=>X_UNIT_OFR_WL_PRIORITY_ID,
487 x_unit_cd=>X_UNIT_CD,
488 x_version_number=>X_VERSION_NUMBER,
489 x_calender_type=>X_CALENDER_TYPE,
490 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
491 x_priority_number=>X_PRIORITY_NUMBER,
492 x_priority_value=>X_PRIORITY_VALUE,
493 x_creation_date=>X_LAST_UPDATE_DATE,
494 x_created_by=>X_LAST_UPDATED_BY,
495 x_last_update_date=>X_LAST_UPDATE_DATE,
496 x_last_updated_by=>X_LAST_UPDATED_BY,
497 x_last_update_login=>X_LAST_UPDATE_LOGIN);
498 insert into IGS_PS_UOFR_WLST_PRI (
499 UNIT_OFR_WAITLIST_PRIORITY_ID
500 ,UNIT_CD
501 ,VERSION_NUMBER
502 ,CALENDER_TYPE
503 ,CI_SEQUENCE_NUMBER
504 ,PRIORITY_NUMBER
505 ,PRIORITY_VALUE
506 ,CREATION_DATE
507 ,CREATED_BY
508 ,LAST_UPDATE_DATE
509 ,LAST_UPDATED_BY
510 ,LAST_UPDATE_LOGIN
511 ) values (
512 NEW_REFERENCES.UNIT_OFR_WAITLIST_PRIORITY_ID
513 ,NEW_REFERENCES.UNIT_CD
514 ,NEW_REFERENCES.VERSION_NUMBER
515 ,NEW_REFERENCES.CALENDER_TYPE
516 ,NEW_REFERENCES.CI_SEQUENCE_NUMBER
517 ,NEW_REFERENCES.PRIORITY_NUMBER
518 ,NEW_REFERENCES.PRIORITY_VALUE
519 ,X_LAST_UPDATE_DATE
520 ,X_LAST_UPDATED_BY
521 ,X_LAST_UPDATE_DATE
522 ,X_LAST_UPDATED_BY
523 ,X_LAST_UPDATE_LOGIN);
524 open c;
525 fetch c into X_ROWID;
526 if (c%notfound) then
527 close c;
528 raise no_data_found;
529 end if;
530 close c;
531 After_DML (
532 p_action => 'INSERT' ,
533 x_rowid => X_ROWID );
534 end INSERT_ROW;
535 procedure LOCK_ROW (
536 X_ROWID in VARCHAR2,
537 x_UNIT_OFR_WL_PRIORITY_ID IN NUMBER,
538 x_UNIT_CD IN VARCHAR2,
539 x_VERSION_NUMBER IN NUMBER,
540 x_CALENDER_TYPE IN VARCHAR2,
541 x_CI_SEQUENCE_NUMBER IN NUMBER,
542 x_PRIORITY_NUMBER IN NUMBER,
543 x_PRIORITY_VALUE IN VARCHAR2 ) AS
544 /*************************************************************
545 Created By : smanglm
546 Date Created On : 4-MAY-2000
547 Purpose : Creation of TBH
548 Know limitations, enhancements or remarks
549 Change History
550 Who When What
551
552 (reverse chronological order - newest change first)
553 ***************************************************************/
554
555 cursor c1 is select
556 UNIT_CD
557 , VERSION_NUMBER
558 , CALENDER_TYPE
559 , CI_SEQUENCE_NUMBER
560 , PRIORITY_NUMBER
561 , PRIORITY_VALUE
562 from IGS_PS_UOFR_WLST_PRI
563 where ROWID = X_ROWID
564 for update nowait;
565 tlinfo c1%rowtype;
566 begin
567 open c1;
568 fetch c1 into tlinfo;
569 if (c1%notfound) then
570 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
571 IGS_GE_MSG_STACK.ADD;
572 close c1;
573 app_exception.raise_exception;
574 return;
575 end if;
576 close c1;
577 if ( ( tlinfo.UNIT_CD = X_UNIT_CD)
578 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
579 AND (tlinfo.CALENDER_TYPE = X_CALENDER_TYPE)
580 AND (tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
581 AND (tlinfo.PRIORITY_NUMBER = X_PRIORITY_NUMBER)
582 AND (tlinfo.PRIORITY_VALUE = X_PRIORITY_VALUE)
583 ) then
584 null;
585 else
586 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
587 IGS_GE_MSG_STACK.ADD;
588 app_exception.raise_exception;
589 end if;
590 return;
591 end LOCK_ROW;
592 Procedure UPDATE_ROW (
593 X_ROWID in VARCHAR2,
594 x_UNIT_OFR_WL_PRIORITY_ID IN NUMBER,
595 x_UNIT_CD IN VARCHAR2,
596 x_VERSION_NUMBER IN NUMBER,
597 x_CALENDER_TYPE IN VARCHAR2,
598 x_CI_SEQUENCE_NUMBER IN NUMBER,
599 x_PRIORITY_NUMBER IN NUMBER,
600 x_PRIORITY_VALUE IN VARCHAR2,
601 X_MODE in VARCHAR2 default 'R'
602 ) AS
603 /*************************************************************
604 Created By : smanglm
605 Date Created On : 4-MAY-2000
606 Purpose : Creation of TBH
607 Know limitations, enhancements or remarks
608 Change History
609 Who When What
610
611 (reverse chronological order - newest change first)
612 ***************************************************************/
613
614 X_LAST_UPDATE_DATE DATE ;
615 X_LAST_UPDATED_BY NUMBER ;
616 X_LAST_UPDATE_LOGIN NUMBER ;
617 begin
618 X_LAST_UPDATE_DATE := SYSDATE;
619 if(X_MODE = 'I') then
620 X_LAST_UPDATED_BY := 1;
621 X_LAST_UPDATE_LOGIN := 0;
622 elsif (X_MODE = 'R') then
623 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
624 if X_LAST_UPDATED_BY is NULL then
625 X_LAST_UPDATED_BY := -1;
626 end if;
627 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
628 if X_LAST_UPDATE_LOGIN is NULL then
629 X_LAST_UPDATE_LOGIN := -1;
630 end if;
631 else
632 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
633 IGS_GE_MSG_STACK.ADD;
634 app_exception.raise_exception;
635 end if;
636 Before_DML(
637 p_action=>'UPDATE',
638 x_rowid=>X_ROWID,
639 x_unit_ofr_wl_priority_id=>X_UNIT_OFR_WL_PRIORITY_ID,
640 x_unit_cd=>X_UNIT_CD,
641 x_version_number=>X_VERSION_NUMBER,
642 x_calender_type=>X_CALENDER_TYPE,
643 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
644 x_priority_number=>X_PRIORITY_NUMBER,
645 x_priority_value=>X_PRIORITY_VALUE,
646 x_creation_date=>X_LAST_UPDATE_DATE,
647 x_created_by=>X_LAST_UPDATED_BY,
648 x_last_update_date=>X_LAST_UPDATE_DATE,
649 x_last_updated_by=>X_LAST_UPDATED_BY,
650 x_last_update_login=>X_LAST_UPDATE_LOGIN);
651 update IGS_PS_UOFR_WLST_PRI set
652 UNIT_CD = NEW_REFERENCES.UNIT_CD,
653 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
654 CALENDER_TYPE = NEW_REFERENCES.CALENDER_TYPE,
655 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
656 PRIORITY_NUMBER = NEW_REFERENCES.PRIORITY_NUMBER,
657 PRIORITY_VALUE = NEW_REFERENCES.PRIORITY_VALUE,
658 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
659 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
660 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
661 where ROWID = X_ROWID;
662 if (sql%notfound) then
663 raise no_data_found;
664 end if;
665
666 After_DML (
667 p_action => 'UPDATE' ,
668 x_rowid => X_ROWID
669 );
670 end UPDATE_ROW;
671 procedure ADD_ROW (
672 X_ROWID in out NOCOPY VARCHAR2,
673 x_UNIT_OFR_WL_PRIORITY_ID IN OUT NOCOPY NUMBER,
674 x_UNIT_CD IN VARCHAR2,
675 x_VERSION_NUMBER IN NUMBER,
676 x_CALENDER_TYPE IN VARCHAR2,
677 x_CI_SEQUENCE_NUMBER IN NUMBER,
678 x_PRIORITY_NUMBER IN NUMBER,
679 x_PRIORITY_VALUE IN VARCHAR2,
680 X_MODE in VARCHAR2 default 'R'
681 ) AS
682 /*************************************************************
683 Created By : smanglm
684 Date Created On : 4-MAY-2000
685 Purpose : Creation of TBH
686 Know limitations, enhancements or remarks
687 Change History
688 Who When What
689
690 (reverse chronological order - newest change first)
691 ***************************************************************/
692
693 cursor c1 is select ROWID from IGS_PS_UOFR_WLST_PRI
694 where UNIT_OFR_WAITLIST_PRIORITY_ID= X_UNIT_OFR_WL_PRIORITY_ID;
695 begin
696 open c1;
697 fetch c1 into X_ROWID;
698 if (c1%notfound) then
699 close c1;
700 INSERT_ROW (
701 X_ROWID,
702 X_UNIT_OFR_WL_PRIORITY_ID,
703 X_UNIT_CD,
704 X_VERSION_NUMBER,
705 X_CALENDER_TYPE,
706 X_CI_SEQUENCE_NUMBER,
707 X_PRIORITY_NUMBER,
708 X_PRIORITY_VALUE,
709 X_MODE );
710 return;
711 end if;
712 close c1;
713 UPDATE_ROW (
714 X_ROWID,
715 X_UNIT_OFR_WL_PRIORITY_ID,
716 X_UNIT_CD,
717 X_VERSION_NUMBER,
718 X_CALENDER_TYPE,
719 X_CI_SEQUENCE_NUMBER,
720 X_PRIORITY_NUMBER,
721 X_PRIORITY_VALUE,
722 X_MODE );
723 end ADD_ROW;
724 procedure DELETE_ROW (
725 X_ROWID in VARCHAR2
726 ) AS
727 /*************************************************************
728 Created By : smanglm
729 Date Created On : 4-MAY-2000
730 Purpose : Creation of TBH
731 Know limitations, enhancements or remarks
732 Change History
733 Who When What
734
735 (reverse chronological order - newest change first)
736 ***************************************************************/
737
738 begin
739 Before_DML (
740 p_action => 'DELETE',
741 x_rowid => X_ROWID
742 );
743 delete from IGS_PS_UOFR_WLST_PRI
744 where ROWID = X_ROWID;
745 if (sql%notfound) then
746 raise no_data_found;
747 end if;
748 After_DML (
749 p_action => 'DELETE',
750 x_rowid => X_ROWID
751 );
752 end DELETE_ROW;
753 END igs_ps_uofr_wlst_pri_pkg;
[Home] [Help]