1 PACKAGE BODY igs_en_orun_wlst_pri_pkg AS
2 /* $Header: IGSEI34B.pls 115.7 2003/05/30 10:45:03 ptandon ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_en_orun_wlst_pri%RowType;
5 new_references igs_en_orun_wlst_pri%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_unit_wlst_pri_id IN NUMBER DEFAULT NULL,
11 x_org_unit_wlst_id IN NUMBER DEFAULT NULL,
12 x_priority_number IN NUMBER DEFAULT NULL,
13 x_priority_value IN VARCHAR2 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 :smanglm
23 Date Created on :26-MAY-2000
24 Purpose : Creation of TBH
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_EN_ORUN_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.org_unit_wlst_pri_id := x_org_unit_wlst_pri_id;
56 new_references.org_unit_wlst_id := x_org_unit_wlst_id;
57 new_references.priority_number := x_priority_number;
58 new_references.priority_value := x_priority_value;
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 :smanglm
77 Date Created on :26-MAY-2000
78 Purpose : Creation of TBH
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
93
94
95
96 END Check_Constraints;
97
98 PROCEDURE Check_Parent_Existance AS
99 /*************************************************************
100 Created By :smanglm
101 Date Created on :26-MAY-2000
102 Purpose : Creation of TBH
103 Know limitations, enhancements or remarks
104 Change History
105 Who When What
106
107 (reverse chronological order - newest change first)
108 ***************************************************************/
109
110 BEGIN
111
112 IF (((old_references.org_unit_wlst_id = new_references.org_unit_wlst_id)) OR
113 ((new_references.org_unit_wlst_id IS NULL))) THEN
114 NULL;
115 ELSIF NOT Igs_En_Or_Unit_Wlst_Pkg.Get_PK_For_Validation (
116 new_references.org_unit_wlst_id
117 ) THEN
118 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
119 IGS_GE_MSG_STACK.ADD;
120 App_Exception.Raise_Exception;
121 END IF;
122
123 IF (((old_references.priority_value = new_references.priority_value)) OR
124 ((new_references.priority_value IS NULL))) THEN
125 NULL;
126 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ('UNIT_WAITLIST',new_references.priority_value)
127 THEN
128 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 END IF;
132
133 END Check_Parent_Existance;
134
135 PROCEDURE Check_Child_Existance IS
136 /*************************************************************
137 Created By :smanglm
138 Date Created on :26-MAY-2000
139 Purpose : Creation of TBH
140 Know limitations, enhancements or remarks
141 Change History
142 Who When What
143
144 (reverse chronological order - newest change first)
145 ***************************************************************/
146
147 BEGIN
148
149 Igs_En_Orun_Wlst_Prf_Pkg.Get_FK_Igs_En_Or_Unit_Wlst_Pri (
150 old_references.org_unit_wlst_pri_id
151 );
152
153 END Check_Child_Existance;
154
155 FUNCTION Get_PK_For_Validation (
156 x_org_unit_wlst_pri_id IN NUMBER
157 ) RETURN BOOLEAN AS
158
159 /*************************************************************
160 Created By :smanglm
161 Date Created on :26-MAY-2000
162 Purpose : Creation of TBH
163 Know limitations, enhancements or remarks
164 Change History
165 Who When What
166
167 (reverse chronological order - newest change first)
168 ***************************************************************/
169
170 CURSOR cur_rowid IS
171 SELECT rowid
172 FROM igs_en_orun_wlst_pri
173 WHERE org_unit_wlst_pri_id = x_org_unit_wlst_pri_id
174 FOR UPDATE NOWAIT;
175
176 lv_rowid cur_rowid%RowType;
177
178 BEGIN
179
180 Open cur_rowid;
181 Fetch cur_rowid INTO lv_rowid;
182 IF (cur_rowid%FOUND) THEN
183 Close cur_rowid;
184 Return(TRUE);
185 ELSE
186 Close cur_rowid;
187 Return(FALSE);
188 END IF;
189 END Get_PK_For_Validation;
190
191 PROCEDURE Get_FK_Igs_En_Or_Unit_Wlst (
192 x_org_unit_wlst_id IN NUMBER
193 ) AS
194
195 /*************************************************************
196 Created By :smanglm
197 Date Created on :26-MAY-2000
198 Purpose : Creation of TBH
199 Know limitations, enhancements or remarks
200 Change History
201 Who When What
202
203 (reverse chronological order - newest change first)
204 ***************************************************************/
205
206 CURSOR cur_rowid IS
207 SELECT rowid
208 FROM igs_en_orun_wlst_pri
209 WHERE org_unit_wlst_id = x_org_unit_wlst_id ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_EN_OUWP_OUW_FK');
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 Return;
223 END IF;
224 Close cur_rowid;
225
226 END Get_FK_Igs_En_Or_Unit_Wlst;
227
228 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
229 x_priority_value IN VARCHAR2
230 ) AS
231 CURSOR cur_rowid IS
232 SELECT ROWID
233 FROM IGS_EN_ORUN_WLST_PRI
234 WHERE priority_value = x_priority_value ;
235 lv_rowid cur_rowid%ROWTYPE;
236 BEGIN
237 OPEN cur_rowid;
238 FETCH cur_rowid INTO lv_rowid;
239 IF (cur_rowid%FOUND) THEN
240 Fnd_Message.Set_Name ('IGS','IGS_EN_EWPI_LKUPV_FK');
241 IGS_GE_MSG_STACK.ADD;
242 CLOSE cur_rowid;
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 RETURN;
245 END IF;
246 CLOSE cur_rowid;
247 END GET_FK_IGS_LOOKUPS_VIEW;
248
249 PROCEDURE Before_DML (
250 p_action IN VARCHAR2,
251 x_rowid IN VARCHAR2 DEFAULT NULL,
252 x_org_unit_wlst_pri_id IN NUMBER DEFAULT NULL,
253 x_org_unit_wlst_id IN NUMBER DEFAULT NULL,
254 x_priority_number IN NUMBER DEFAULT NULL,
255 x_priority_value IN VARCHAR2 DEFAULT NULL,
256 x_creation_date IN DATE DEFAULT NULL,
257 x_created_by IN NUMBER DEFAULT NULL,
258 x_last_update_date IN DATE DEFAULT NULL,
259 x_last_updated_by IN NUMBER DEFAULT NULL,
260 x_last_update_login IN NUMBER DEFAULT NULL
261 ) AS
262 /*************************************************************
263 Created By :smanglm
264 Date Created on :26-MAY-2000
265 Purpose : Creation of TBH
266 Know limitations, enhancements or remarks
267 Change History
268 Who When What
269
270 (reverse chronological order - newest change first)
271 ***************************************************************/
272
273 BEGIN
274
275 Set_Column_Values (
276 p_action,
277 x_rowid,
278 x_org_unit_wlst_pri_id,
279 x_org_unit_wlst_id,
280 x_priority_number,
281 x_priority_value,
282 x_creation_date,
283 x_created_by,
284 x_last_update_date,
285 x_last_updated_by,
286 x_last_update_login
287 );
288
289 IF (p_action = 'INSERT') THEN
290 -- Call all the procedures related to Before Insert.
291 Null;
292 IF Get_Pk_For_Validation(
293 new_references.org_unit_wlst_pri_id) THEN
294 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END IF;
298 Check_Constraints;
299 Check_Parent_Existance;
300 ELSIF (p_action = 'UPDATE') THEN
301 -- Call all the procedures related to Before Update.
302 Null;
303 Check_Constraints;
304 Check_Parent_Existance;
305 ELSIF (p_action = 'DELETE') THEN
306 -- Call all the procedures related to Before Delete.
307 Null;
308 Check_Child_Existance;
309 ELSIF (p_action = 'VALIDATE_INSERT') THEN
310 -- Call all the procedures related to Before Insert.
311 IF Get_PK_For_Validation (
312 new_references.org_unit_wlst_pri_id) THEN
313 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
314 IGS_GE_MSG_STACK.ADD;
315 App_Exception.Raise_Exception;
316 END IF;
317 Check_Constraints;
318 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
319 Check_Constraints;
320 ELSIF (p_action = 'VALIDATE_DELETE') THEN
321 Check_Child_Existance;
322 END IF;
323
324 END Before_DML;
325
326 PROCEDURE After_DML (
327 p_action IN VARCHAR2,
328 x_rowid IN VARCHAR2
329 ) IS
330 /*************************************************************
331 Created By :smanglm
332 Date Created on :26-MAY-2000
333 Purpose : Creation of TBH
334 Know limitations, enhancements or remarks
335 Change History
336 Who When What
337
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340
341 BEGIN
342
343 l_rowid := x_rowid;
344
345 IF (p_action = 'INSERT') THEN
346 -- Call all the procedures related to After Insert.
347 Null;
348 ELSIF (p_action = 'UPDATE') THEN
349 -- Call all the procedures related to After Update.
350 Null;
351 ELSIF (p_action = 'DELETE') THEN
352 -- Call all the procedures related to After Delete.
353 Null;
354 END IF;
355
356 END After_DML;
357
358 procedure INSERT_ROW (
359 X_ROWID in out NOCOPY VARCHAR2,
360 x_ORG_UNIT_WLST_PRI_ID IN OUT NOCOPY NUMBER,
361 x_ORG_UNIT_WLST_ID IN NUMBER,
362 x_PRIORITY_NUMBER IN NUMBER,
363 x_PRIORITY_VALUE IN VARCHAR2,
364 X_MODE in VARCHAR2 default 'R'
365 ) AS
366 /*************************************************************
367 Created By :smanglm
368 Date Created on :26-MAY-2000
369 Purpose : Creation of TBH
370 Know limitations, enhancements or remarks
371 Change History
372 Who When What
373
374 (reverse chronological order - newest change first)
375 ***************************************************************/
376
377 cursor C is select ROWID from IGS_EN_ORUN_WLST_PRI
378 where ORG_UNIT_WLST_PRI_ID= X_ORG_UNIT_WLST_PRI_ID
379 ;
380 X_LAST_UPDATE_DATE DATE ;
381 X_LAST_UPDATED_BY NUMBER ;
382 X_LAST_UPDATE_LOGIN NUMBER ;
383 begin
384 X_LAST_UPDATE_DATE := SYSDATE;
385 if(X_MODE = 'I') then
386 X_LAST_UPDATED_BY := 1;
387 X_LAST_UPDATE_LOGIN := 0;
388 elsif (X_MODE = 'R') then
389 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
390 if X_LAST_UPDATED_BY is NULL then
391 X_LAST_UPDATED_BY := -1;
392 end if;
393 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
394 if X_LAST_UPDATE_LOGIN is NULL then
395 X_LAST_UPDATE_LOGIN := -1;
396 end if;
397 else
398 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
399 IGS_GE_MSG_STACK.ADD;
400 app_exception.raise_exception;
401 end if;
402 SELECT IGS_EN_OR_UNIT_WLST_PRI_S.NEXTVAL
403 INTO x_org_unit_wlst_pri_id
404 FROM dual;
405 Before_DML(
406 p_action=>'INSERT',
407 x_rowid=>X_ROWID,
408 x_org_unit_wlst_pri_id=>X_ORG_UNIT_WLST_PRI_ID,
409 x_org_unit_wlst_id=>X_ORG_UNIT_WLST_ID,
410 x_priority_number=>X_PRIORITY_NUMBER,
411 x_priority_value=>X_PRIORITY_VALUE,
412 x_creation_date=>X_LAST_UPDATE_DATE,
413 x_created_by=>X_LAST_UPDATED_BY,
414 x_last_update_date=>X_LAST_UPDATE_DATE,
415 x_last_updated_by=>X_LAST_UPDATED_BY,
416 x_last_update_login=>X_LAST_UPDATE_LOGIN);
417 insert into IGS_EN_ORUN_WLST_PRI (
418 ORG_UNIT_WLST_PRI_ID
419 ,ORG_UNIT_WLST_ID
420 ,PRIORITY_NUMBER
421 ,PRIORITY_VALUE
422 ,CREATION_DATE
423 ,CREATED_BY
424 ,LAST_UPDATE_DATE
425 ,LAST_UPDATED_BY
426 ,LAST_UPDATE_LOGIN
427 ) values (
428 NEW_REFERENCES.ORG_UNIT_WLST_PRI_ID
429 ,NEW_REFERENCES.ORG_UNIT_WLST_ID
430 ,NEW_REFERENCES.PRIORITY_NUMBER
431 ,NEW_REFERENCES.PRIORITY_VALUE
432 ,X_LAST_UPDATE_DATE
433 ,X_LAST_UPDATED_BY
434 ,X_LAST_UPDATE_DATE
435 ,X_LAST_UPDATED_BY
436 ,X_LAST_UPDATE_LOGIN
437 );
438 open c;
439 fetch c into X_ROWID;
440 if (c%notfound) then
441 close c;
442 raise no_data_found;
443 end if;
444 close c;
445 After_DML (
446 p_action => 'INSERT' ,
447 x_rowid => X_ROWID );
448 end INSERT_ROW;
449 procedure LOCK_ROW (
450 X_ROWID in VARCHAR2,
451 x_ORG_UNIT_WLST_PRI_ID IN NUMBER,
452 x_ORG_UNIT_WLST_ID IN NUMBER,
453 x_PRIORITY_NUMBER IN NUMBER,
454 x_PRIORITY_VALUE IN VARCHAR2 ) AS
455 /*************************************************************
456 Created By :smanglm
457 Date Created on :26-MAY-2000
458 Purpose : Creation of TBH
459 Know limitations, enhancements or remarks
460 Change History
461 Who When What
462
463 (reverse chronological order - newest change first)
464 ***************************************************************/
465
466 cursor c1 is select
467 ORG_UNIT_WLST_ID
468 , PRIORITY_NUMBER
469 , PRIORITY_VALUE
470 from IGS_EN_ORUN_WLST_PRI
471 where ROWID = X_ROWID
472 for update nowait;
473 tlinfo c1%rowtype;
474 begin
475 open c1;
476 fetch c1 into tlinfo;
477 if (c1%notfound) then
478 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
479 IGS_GE_MSG_STACK.ADD;
480 close c1;
481 app_exception.raise_exception;
482 return;
483 end if;
484 close c1;
485 if ( ( tlinfo.ORG_UNIT_WLST_ID = X_ORG_UNIT_WLST_ID)
486 AND (tlinfo.PRIORITY_NUMBER = X_PRIORITY_NUMBER)
487 AND (tlinfo.PRIORITY_VALUE = X_PRIORITY_VALUE)
488 ) then
489 null;
490 else
491 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 end if;
495 return;
496 end LOCK_ROW;
497 Procedure UPDATE_ROW (
498 X_ROWID in VARCHAR2,
499 x_ORG_UNIT_WLST_PRI_ID IN NUMBER,
500 x_ORG_UNIT_WLST_ID IN NUMBER,
501 x_PRIORITY_NUMBER IN NUMBER,
502 x_PRIORITY_VALUE IN VARCHAR2,
503 X_MODE in VARCHAR2 default 'R'
504 ) AS
505 /*************************************************************
506 Created By :smanglm
507 Date Created on :26-MAY-2000
508 Purpose : Creation of TBH
509 Know limitations, enhancements or remarks
510 Change History
511 Who When What
512
513 (reverse chronological order - newest change first)
514 ***************************************************************/
515
516 X_LAST_UPDATE_DATE DATE ;
517 X_LAST_UPDATED_BY NUMBER ;
518 X_LAST_UPDATE_LOGIN NUMBER ;
519 begin
520 X_LAST_UPDATE_DATE := SYSDATE;
521 if(X_MODE = 'I') then
522 X_LAST_UPDATED_BY := 1;
523 X_LAST_UPDATE_LOGIN := 0;
524 elsif (X_MODE = 'R') then
525 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
526 if X_LAST_UPDATED_BY is NULL then
527 X_LAST_UPDATED_BY := -1;
528 end if;
529 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
530 if X_LAST_UPDATE_LOGIN is NULL then
531 X_LAST_UPDATE_LOGIN := -1;
532 end if;
533 else
534 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
535 IGS_GE_MSG_STACK.ADD;
536 app_exception.raise_exception;
537 end if;
538 Before_DML(
539 p_action=>'UPDATE',
540 x_rowid=>X_ROWID,
541 x_org_unit_wlst_pri_id=>X_ORG_UNIT_WLST_PRI_ID,
542 x_org_unit_wlst_id=>X_ORG_UNIT_WLST_ID,
543 x_priority_number=>X_PRIORITY_NUMBER,
547 x_last_update_date=>X_LAST_UPDATE_DATE,
544 x_priority_value=>X_PRIORITY_VALUE,
545 x_creation_date=>X_LAST_UPDATE_DATE,
546 x_created_by=>X_LAST_UPDATED_BY,
548 x_last_updated_by=>X_LAST_UPDATED_BY,
549 x_last_update_login=>X_LAST_UPDATE_LOGIN);
550 update IGS_EN_ORUN_WLST_PRI set
551 ORG_UNIT_WLST_ID = NEW_REFERENCES.ORG_UNIT_WLST_ID,
552 PRIORITY_NUMBER = NEW_REFERENCES.PRIORITY_NUMBER,
553 PRIORITY_VALUE = NEW_REFERENCES.PRIORITY_VALUE,
554 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
555 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
556 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
557 where ROWID = X_ROWID;
558 if (sql%notfound) then
559 raise no_data_found;
560 end if;
561
562 After_DML (
563 p_action => 'UPDATE' ,
564 x_rowid => X_ROWID
565 );
566 end UPDATE_ROW;
567 procedure ADD_ROW (
568 X_ROWID in out NOCOPY VARCHAR2,
569 x_ORG_UNIT_WLST_PRI_ID IN OUT NOCOPY NUMBER,
570 x_ORG_UNIT_WLST_ID IN NUMBER,
571 x_PRIORITY_NUMBER IN NUMBER,
572 x_PRIORITY_VALUE IN VARCHAR2,
573 X_MODE in VARCHAR2 default 'R'
574 ) AS
575 /*************************************************************
576 Created By :smanglm
577 Date Created on :26-MAY-2000
578 Purpose : Creation of TBH
579 Know limitations, enhancements or remarks
580 Change History
581 Who When What
582
583 (reverse chronological order - newest change first)
584 ***************************************************************/
585
586 cursor c1 is select ROWID from IGS_EN_ORUN_WLST_PRI
587 where ORG_UNIT_WLST_PRI_ID= X_ORG_UNIT_WLST_PRI_ID
588 ;
589 begin
590 open c1;
591 fetch c1 into X_ROWID;
592 if (c1%notfound) then
593 close c1;
594 INSERT_ROW (
595 X_ROWID,
596 X_ORG_UNIT_WLST_PRI_ID,
597 X_ORG_UNIT_WLST_ID,
598 X_PRIORITY_NUMBER,
599 X_PRIORITY_VALUE,
600 X_MODE );
601 return;
602 end if;
603 close c1;
604 UPDATE_ROW (
605 X_ROWID,
606 X_ORG_UNIT_WLST_PRI_ID,
607 X_ORG_UNIT_WLST_ID,
608 X_PRIORITY_NUMBER,
609 X_PRIORITY_VALUE,
610 X_MODE );
611 end ADD_ROW;
612 procedure DELETE_ROW (
613 X_ROWID in VARCHAR2
614 ) AS
615 /*************************************************************
616 Created By :smanglm
617 Date Created on :26-MAY-2000
618 Purpose : Creation of TBH
619 Know limitations, enhancements or remarks
620 Change History
621 Who When What
622
623 (reverse chronological order - newest change first)
624 ***************************************************************/
625
626 begin
627 Before_DML (
628 p_action => 'DELETE',
629 x_rowid => X_ROWID
630 );
631 delete from IGS_EN_ORUN_WLST_PRI
632 where ROWID = X_ROWID;
633 if (sql%notfound) then
634 raise no_data_found;
635 end if;
636 After_DML (
637 p_action => 'DELETE',
638 x_rowid => X_ROWID
639 );
640 end DELETE_ROW;
641 END igs_en_orun_wlst_pri_pkg;
[Home] [Help]