1 PACKAGE BODY igs_en_timeslot_prty_pkg AS
2 /* $Header: IGSEI39B.pls 115.5 2002/11/28 23:41:46 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_en_timeslot_prty%ROWTYPE;
5 new_references igs_en_timeslot_prty%ROWTYPE;
6
7 PROCEDURE set_column_values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_igs_en_timeslot_prty_id IN NUMBER DEFAULT NULL,
11 x_igs_en_timeslot_stup_id IN NUMBER DEFAULT NULL,
12 x_priority_order 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 :
23 Date Created By :
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_EN_TIMESLOT_PRTY
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.igs_en_timeslot_prty_id := x_igs_en_timeslot_prty_id;
56 new_references.igs_en_timeslot_stup_id := x_igs_en_timeslot_stup_id;
57 new_references.priority_order := x_priority_order;
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 :
77 Date Created By :
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
93
94
95
96 END check_constraints;
97
98 PROCEDURE check_uniqueness AS
99 /*************************************************************
100 Created By :
101 Date Created By :
102 Purpose :
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 IF get_uk_for_validation (
112 new_references.igs_en_timeslot_stup_id
113 ,new_references.priority_value
114 ) THEN
115 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119 END check_uniqueness ;
120 PROCEDURE check_parent_existance AS
121 /*************************************************************
122 Created By :
123 Date Created By :
124 Purpose :
125 Know limitations, enhancements or remarks
126 Change History
127 Who When What
128
129 (reverse chronological order - newest change first)
130 ***************************************************************/
131
132 BEGIN
133
134 IF (((old_references.igs_en_timeslot_stup_id = new_references.igs_en_timeslot_stup_id)) OR
135 ((new_references.igs_en_timeslot_stup_id IS NULL))) THEN
136 NULL;
137 ELSIF NOT igs_en_timeslot_stup_pkg.get_pk_for_validation (
138 new_references.igs_en_timeslot_stup_id
139 ) THEN
140 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
141 igs_ge_msg_stack.add;
142 app_exception.raise_exception;
143 END IF;
144
145 END check_parent_existance;
146
147 PROCEDURE check_child_existance IS
148 /*************************************************************
149 Created By :
150 Date Created By :
151 Purpose :
152 Know limitations, enhancements or remarks
153 Change History
154 Who When What
155
156 (reverse chronological order - newest change first)
157 ***************************************************************/
158
159 BEGIN
160
161 igs_en_timeslot_pref_pkg.get_fk_igs_en_timeslot_prty (
162 old_references.igs_en_timeslot_prty_id
163 );
164
165 END check_child_existance;
166
167 FUNCTION get_pk_for_validation (
168 x_igs_en_timeslot_prty_id IN NUMBER
169 ) RETURN BOOLEAN AS
170
171 /*************************************************************
172 Created By :
173 Date Created By :
174 Purpose :
175 Know limitations, enhancements or remarks
176 Change History
177 Who When What
178
179 (reverse chronological order - newest change first)
180 ***************************************************************/
181
182 CURSOR cur_rowid IS
183 SELECT rowid
184 FROM igs_en_timeslot_prty
185 WHERE igs_en_timeslot_prty_id = x_igs_en_timeslot_prty_id
186 FOR UPDATE NOWAIT;
187
188 lv_rowid cur_rowid%RowType;
189
190 BEGIN
191
192 OPEN cur_rowid;
193 FETCH cur_rowid INTO lv_rowid;
194 IF (cur_rowid%FOUND) THEN
195 CLOSE cur_rowid;
196 RETURN(TRUE);
197 ELSE
198 CLOSE cur_rowid;
199 RETURN(FALSE);
200 END IF;
201 END get_pk_for_validation;
202
203 FUNCTION get_uk_for_validation (
204 x_igs_en_timeslot_stup_id IN NUMBER,
205 x_priority_value IN VARCHAR2
206 ) RETURN BOOLEAN AS
207
208 /*************************************************************
209 Created By :
210 Date Created By :
211 Purpose :
212 Know limitations, enhancements or remarks
213 Change History
214 Who When What
215
216 (reverse chronological order - newest change first)
217 ***************************************************************/
218
219 CURSOR cur_rowid IS
220 SELECT rowid
221 FROM igs_en_timeslot_prty
222 WHERE igs_en_timeslot_stup_id = x_igs_en_timeslot_stup_id
223 AND priority_value = x_priority_value and ((l_rowid is null) or (rowid <> l_rowid))
224
225 ;
226 lv_rowid cur_rowid%RowType;
227
228 BEGIN
229
230 OPEN cur_rowid;
231 FETCH cur_rowid INTO lv_rowid;
232 IF (cur_rowid%FOUND) THEN
233 CLOSE cur_rowid;
234 RETURN (true);
235 ELSE
236 CLOSE cur_rowid;
237 RETURN(FALSE);
238 END IF;
239 END get_uk_for_validation ;
240 PROCEDURE get_fk_igs_en_timeslot_stup (
241 x_igs_en_timeslot_stup_id IN NUMBER
242 ) AS
243
244 /*************************************************************
245 Created By :
246 Date Created By :
247 Purpose :
248 Know limitations, enhancements or remarks
249 Change History
250 Who When What
251
252 (reverse chronological order - newest change first)
253 ***************************************************************/
254
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM igs_en_timeslot_prty
258 WHERE igs_en_timeslot_stup_id = x_igs_en_timeslot_stup_id ;
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 OPEN cur_rowid;
265 FETCH cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 CLOSE cur_rowid;
268 fnd_message.set_name ('IGS', 'IGS_EN_ETPY_ETST_FK');
269 igs_ge_msg_stack.add;
270 app_exception.raise_exception;
271 RETURN;
272 END IF;
273 CLOSE cur_rowid;
274
275 END get_fk_igs_en_timeslot_stup;
276
277 PROCEDURE before_dml (
278 p_action IN VARCHAR2,
279 x_rowid IN VARCHAR2 DEFAULT NULL,
280 x_igs_en_timeslot_prty_id IN NUMBER DEFAULT NULL,
281 x_igs_en_timeslot_stup_id IN NUMBER DEFAULT NULL,
282 x_priority_order IN NUMBER DEFAULT NULL,
283 x_priority_value IN VARCHAR2 DEFAULT NULL,
284 x_creation_date IN DATE DEFAULT NULL,
285 x_created_by IN NUMBER DEFAULT NULL,
286 x_last_update_date IN DATE DEFAULT NULL,
287 x_last_updated_by IN NUMBER DEFAULT NULL,
288 x_last_update_login IN NUMBER DEFAULT NULL
289 ) AS
290 /*************************************************************
291 Created By :
292 Date Created By :
293 Purpose :
294 Know limitations, enhancements or remarks
295 Change History
296 Who When What
297
298 (reverse chronological order - newest change first)
299 ***************************************************************/
300
301 BEGIN
302
303 set_column_values (
304 p_action,
305 x_rowid,
306 x_igs_en_timeslot_prty_id,
307 x_igs_en_timeslot_stup_id,
308 x_priority_order,
309 x_priority_value,
310 x_creation_date,
311 x_created_by,
312 x_last_update_date,
313 x_last_updated_by,
314 x_last_update_login
315 );
316
317 IF (p_action = 'INSERT') THEN
318 -- Call all the procedures related to Before Insert.
319 Null;
320 IF get_pk_for_validation(
321 new_references.igs_en_timeslot_prty_id) THEN
322 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326 check_uniqueness;
327 check_constraints;
328 check_parent_existance;
329 ELSIF (p_action = 'UPDATE') THEN
330 -- Call all the procedures related to Before Update.
331 Null;
332 check_uniqueness;
333 check_constraints;
334 check_parent_existance;
335 ELSIF (p_action = 'DELETE') THEN
336 -- Call all the procedures related to Before Delete.
337 Null;
338 check_child_existance;
339 ELSIF (p_action = 'VALIDATE_INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 IF get_pk_for_validation (
342 new_references.igs_en_timeslot_prty_id) THEN
343 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
344 igs_ge_msg_stack.add;
345 app_exception.raise_exception;
346 END IF;
347 check_uniqueness;
348 check_constraints;
349 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
350 check_uniqueness;
351 check_constraints;
352 ELSIF (p_action = 'VALIDATE_DELETE') THEN
353 check_child_existance;
354 END IF;
355
356 END before_dml;
357
358 PROCEDURE after_dml (
359 p_action IN VARCHAR2,
360 x_rowid IN VARCHAR2
361 ) IS
362 /*************************************************************
363 Created By :
364 Date Created By :
365 Purpose :
366 Know limitations, enhancements or remarks
367 Change History
368 Who When What
369
370 (reverse chronological order - newest change first)
371 ***************************************************************/
372
373 BEGIN
374
375 l_rowid := x_rowid;
376
377 IF (p_action = 'INSERT') THEN
378 -- Call all the procedures related to After Insert.
379 Null;
380 ELSIF (p_action = 'UPDATE') THEN
381 -- Call all the procedures related to After Update.
382 Null;
383 ELSIF (p_action = 'DELETE') THEN
384 -- Call all the procedures related to After Delete.
385 Null;
386 END IF;
387
388 END after_dml;
389
390 PROCEDURE insert_row (
391 x_rowid IN OUT NOCOPY VARCHAR2,
392 x_igs_en_timeslot_prty_id IN OUT NOCOPY NUMBER,
393 x_igs_en_timeslot_stup_id IN NUMBER,
394 x_priority_order IN NUMBER,
395 x_priority_value IN VARCHAR2,
396 x_mode IN VARCHAR2 DEFAULT 'R'
397 ) AS
398 /*************************************************************
399 Created By :
400 Date Created By :
401 Purpose :
402 Know limitations, enhancements or remarks
403 Change History
404 Who When What
405
406 (reverse chronological order - newest change first)
407 ***************************************************************/
408
409 CURSOR c IS SELECT ROWID FROM igs_en_timeslot_prty
410 WHERE igs_en_timeslot_prty_id= x_igs_en_timeslot_prty_id
411 ;
412 x_last_update_date DATE ;
413 x_last_updated_by NUMBER ;
414 x_last_update_login NUMBER ;
415 BEGIN
416 x_last_update_date := SYSDATE;
417 IF(x_mode = 'I') THEN
418 x_last_updated_by := 1;
419 x_last_update_login := 0;
420 ELSIF (x_mode = 'R') THEN
421 x_last_updated_by := fnd_global.user_id;
422 IF x_last_updated_by IS NULL then
423 x_last_updated_by := -1;
424 END IF;
425 x_last_update_login := fnd_global.login_id;
426 IF x_last_update_login IS NULL THEN
427 x_last_update_login := -1;
428 END IF;
429 ELSE
430 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
431 igs_ge_msg_stack.add;
432 app_exception.raise_exception;
433 end if;
434
435 select igs_en_timeslot_prty_s.nextval into x_igs_en_timeslot_prty_id from dual;
436
437 before_dml(
438 p_action=>'INSERT',
439 x_rowid=>X_ROWID,
440 x_igs_en_timeslot_prty_id=>x_igs_en_timeslot_prty_id,
441 x_igs_en_timeslot_stup_id=>x_igs_en_timeslot_stup_id,
442 x_priority_order=>x_priority_order,
443 x_priority_value=>x_priority_value,
444 x_creation_date=> x_last_update_date,
445 x_created_by=> x_last_updated_by,
446 x_last_update_date=> x_last_update_date,
447 x_last_updated_by=> x_last_updated_by,
448 x_last_update_login=> x_last_update_login);
449 INSERT INTO igs_en_timeslot_prty (
450 igs_en_timeslot_prty_id
451 ,igs_en_timeslot_stup_id
452 ,priority_order
453 ,priority_value
454 ,creation_date
455 ,created_by
456 ,last_update_date
457 ,last_updated_by
458 ,last_update_login
459 ) VALUES (
460 new_references.igs_en_timeslot_prty_id
461 ,new_references.igs_en_timeslot_stup_id
462 ,new_references.priority_order
463 ,new_references.priority_value
464 ,x_last_update_date
465 ,x_last_updated_by
466 ,x_last_update_date
467 ,x_last_updated_by
468 ,x_last_update_login
469 );
470 OPEN c;
471 FETCH c INTO x_rowid;
472 IF (c%NOTFOUND) THEN
473 CLOSE c;
474 RAISE NO_DATA_FOUND;
475 END IF;
476 CLOSE c;
477 after_dml (
478 p_action => 'INSERT' ,
479 x_rowid => X_ROWID );
480 END insert_row;
481 PROCEDURE lock_row (
482 x_rowid IN VARCHAR2,
483 x_igs_en_timeslot_prty_id IN NUMBER,
484 x_igs_en_timeslot_stup_id IN NUMBER,
485 x_priority_order IN NUMBER,
486 x_priority_value IN VARCHAR2 ) AS
487 /*************************************************************
488 Created By :
489 Date Created By :
490 Purpose :
491 Know limitations, enhancements or remarks
492 Change History
493 Who When What
494
495 (reverse chronological order - newest change first)
496 ***************************************************************/
497
498 CURSOR c1 IS SELECT
499 igs_en_timeslot_stup_id
500 , priority_order
501 , priority_value
502 FROM igs_en_timeslot_prty
503 WHERE ROWID = x_rowid
504 FOR UPDATE NOWAIT;
505 tlinfo c1%ROWTYPE;
506 BEGIN
507 OPEN c1;
508 FETCH c1 INTO tlinfo;
509 IF (c1%notfound) THEN
510 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
511 igs_ge_msg_stack.add;
512 CLOSE c1;
513 app_exception.raise_exception;
514 RETURN;
515 END IF;
516 CLOSE c1;
517 if ( ( tlinfo.IGS_EN_TIMESLOT_STUP_ID = X_IGS_EN_TIMESLOT_STUP_ID)
518 AND (tlinfo.PRIORITY_ORDER = X_PRIORITY_ORDER)
519 AND (tlinfo.PRIORITY_VALUE = X_PRIORITY_VALUE)
520 ) THEN
521 NULL;
522 ELSE
523 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
524 igs_ge_msg_stack.add;
525 app_exception.raise_exception;
526 END IF;
527 RETURN;
528 END lock_row;
529 PROCEDURE update_row (
530 x_rowid IN VARCHAR2,
531 x_IGS_EN_TIMESLOT_PRTY_ID IN NUMBER,
532 x_IGS_EN_TIMESLOT_STUP_ID IN NUMBER,
533 x_PRIORITY_ORDER IN NUMBER,
534 x_PRIORITY_VALUE IN VARCHAR2,
535 x_mode IN VARCHAR2 DEFAULT 'R'
536 ) AS
537 /*************************************************************
538 Created By :
539 Date Created By :
540 Purpose :
541 Know limitations, enhancements or remarks
542 Change History
543 Who When What
544
545 (reverse chronological order - newest change first)
546 ***************************************************************/
547
548 x_last_update_date DATE ;
549 x_last_updated_by NUMBER ;
550 x_last_update_login NUMBER ;
551 BEGIN
552 x_last_update_date := SYSDATE;
553 IF (X_MODE = 'I') THEN
554 x_last_updated_by := 1;
555 x_last_update_login := 0;
556 ELSIF (x_mode = 'R') THEN
557 x_last_updated_by := fnd_global.user_id;
558 IF x_last_updated_by IS NULL THEN
559 x_last_updated_by := -1;
560 END IF;
561 x_last_update_login := fnd_global.login_id;
562 IF x_last_update_login IS NULL THEN
563 x_last_update_login := -1;
564 END IF;
565 ELSE
566 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
567 igs_ge_msg_stack.add;
568 app_exception.raise_exception;
569 END IF;
570 before_dml(
571 p_action=>'UPDATE',
572 x_rowid=>X_ROWID,
573 x_igs_en_timeslot_prty_id=>X_IGS_EN_TIMESLOT_PRTY_ID,
574 x_igs_en_timeslot_stup_id=>X_IGS_EN_TIMESLOT_STUP_ID,
575 x_priority_order=>X_PRIORITY_ORDER,
576 x_priority_value=>X_PRIORITY_VALUE,
577 x_creation_date=>x_last_update_date,
578 x_created_by=>x_last_updated_by,
579 x_last_update_date=>x_last_update_date,
580 x_last_updated_by=>x_last_updated_by,
581 x_last_update_login=>x_last_update_login);
582 UPDATE igs_en_timeslot_prty SET
583 igs_en_timeslot_stup_id = NEW_REFERENCES.igs_en_timeslot_stup_id,
584 priority_order = NEW_REFERENCES.priority_order,
585 priority_value = NEW_REFERENCES.priority_value,
586 last_update_date = x_last_update_date,
587 last_updated_by = x_last_updated_by,
588 last_update_login = x_last_update_login
589 WHERE ROWID = x_rowid;
590 IF (SQL%NOTFOUND) THEN
591 RAISE NO_DATA_FOUND;
592 END IF;
593
594 after_dml (
595 p_action => 'UPDATE' ,
596 x_rowid => X_ROWID
597 );
598 END update_row;
599 PROCEDURE add_row (
600 x_rowid IN OUT NOCOPY VARCHAR2,
601 x_igs_en_timeslot_prty_id IN OUT NOCOPY NUMBER,
602 x_igs_en_timeslot_stup_id IN NUMBER,
603 x_priority_order IN NUMBER,
604 x_priority_value IN VARCHAR2,
605 x_mode IN VARCHAR2 DEFAULT 'R'
606 ) AS
607 /*************************************************************
608 Created By :
609 Date Created By :
610 Purpose :
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_en_timeslot_prty
619 WHERE igs_en_timeslot_prty_id= x_igs_en_timeslot_prty_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_igs_en_timeslot_prty_id,
629 X_igs_en_timeslot_stup_id,
630 X_priority_order,
631 X_priority_value,
632 x_mode );
633 RETURN;
634 END IF;
635 CLOSE c1;
636 update_row (
637 x_rowid,
638 x_igs_en_timeslot_prty_id,
639 x_igs_en_timeslot_stup_id,
640 x_priority_order,
641 x_priority_value,
642 x_mode );
643 END add_row;
644 PROCEDURE delete_row (
645 x_rowid IN VARCHAR2
646 ) AS
647 /*************************************************************
648 Created By :
649 Date Created By :
650 Purpose :
651 Know limitations, enhancements or remarks
652 Change History
653 Who When What
654
655 (reverse chronological order - newest change first)
656 ***************************************************************/
657
658 BEGIN
659 before_dml (
660 p_action => 'DELETE',
661 x_rowid => X_ROWID
662 );
663 DELETE FROM igs_en_timeslot_prty
664 WHERE ROWID = x_rowid;
665 IF (SQL%NOTFOUND) THEN
666 RAISE NO_DATA_FOUND;
667 END IF;
668 after_dml (
669 p_action => 'DELETE',
670 x_rowid => X_ROWID
671 );
672 END delete_row;
673 END igs_en_timeslot_prty_pkg;
[Home] [Help]