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