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