1 PACKAGE BODY igs_ps_rsv_usec_pri_pkg AS
2 /* $Header: IGSPI1WB.pls 115.5 2003/03/24 15:00:44 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_rsv_usec_pri%ROWTYPE;
6 new_references igs_ps_rsv_usec_pri%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_rsv_usec_pri_id IN NUMBER DEFAULT NULL,
12 x_uoo_id IN NUMBER DEFAULT NULL,
13 x_priority_order IN NUMBER DEFAULT NULL,
14 x_priority_value 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 || Created By : apelleti
23 || Created On : 02-MAY-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_PS_RSV_USEC_PRI
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.rsv_usec_pri_id := x_rsv_usec_pri_id;
55 new_references.uoo_id := x_uoo_id;
56 new_references.priority_order := x_priority_order;
57 new_references.priority_value := x_priority_value;
58
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
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : apelleti
77 || Created On : 02-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.uoo_id,
88 new_references.priority_value
89 )
90 ) THEN
91 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95
96 END check_uniqueness;
97
98
99 PROCEDURE check_parent_existance AS
100 /*
101 || Created By : apelleti
102 || Created On : 02-MAY-2001
103 || Purpose : Checks for the existance of Parent records.
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 || smvk 31-Jan-2003 Bug # 2532094. Added the foreign key checking with igs_lookups_view.
109 */
110 BEGIN
111
112 IF (((old_references.uoo_id = new_references.uoo_id)) OR
113 ((new_references.uoo_id IS NULL))) THEN
114 NULL;
115 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
116 new_references.uoo_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('RESERVE_SEAT_PRIORITY',
127 new_references.priority_value) 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
136 PROCEDURE check_child_existance IS
137 /*
138 || Created By : apelleti
139 || Created On : 02-MAY-2001
140 || Purpose : Checks for the existance of Child records.
141 || Known limitations, enhancements or remarks :
142 || Change History :
143 || Who When What
144 || (reverse chronological order - newest change first)
145 */
146 BEGIN
147
148 igs_ps_rsv_usec_prf_pkg.get_fk_igs_ps_rsv_usec_pri (
149 old_references.rsv_usec_pri_id
150 );
151
152 END check_child_existance;
153
154
155 FUNCTION get_pk_for_validation (
156 x_rsv_usec_pri_id IN NUMBER
157 ) RETURN BOOLEAN AS
158 /*
159 || Created By : apelleti
160 || Created On : 02-MAY-2001
161 || Purpose : Validates the Primary Key of the table.
162 || Known limitations, enhancements or remarks :
163 || Change History :
164 || Who When What
165 || (reverse chronological order - newest change first)
166 */
167 CURSOR cur_rowid IS
168 SELECT rowid
169 FROM igs_ps_rsv_usec_pri
170 WHERE rsv_usec_pri_id = x_rsv_usec_pri_id
171 FOR UPDATE NOWAIT;
172
173 lv_rowid cur_rowid%RowType;
174
175 BEGIN
176
177 OPEN cur_rowid;
178 FETCH cur_rowid INTO lv_rowid;
179 IF (cur_rowid%FOUND) THEN
180 CLOSE cur_rowid;
181 RETURN(TRUE);
182 ELSE
183 CLOSE cur_rowid;
184 RETURN(FALSE);
185 END IF;
186
187 END get_pk_for_validation;
188
189
190 FUNCTION get_uk_for_validation (
191 x_uoo_id IN NUMBER,
192 x_priority_value IN VARCHAR2
193 ) RETURN BOOLEAN AS
194 /*
195 || Created By : apelleti
196 || Created On : 02-MAY-2001
197 || Purpose : Validates the Unique Keys of the table.
198 || Known limitations, enhancements or remarks :
199 || Change History :
200 || Who When What
201 || (reverse chronological order - newest change first)
202 */
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM igs_ps_rsv_usec_pri
206 WHERE uoo_id = x_uoo_id
207 AND priority_value = x_priority_value
208 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 OPEN cur_rowid;
215 FETCH cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 CLOSE cur_rowid;
218 RETURN (true);
219 ELSE
220 CLOSE cur_rowid;
221 RETURN(FALSE);
222 END IF;
223
224 END get_uk_for_validation ;
225
226
227 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
228 x_uoo_id IN NUMBER
229 ) AS
230 /*
231 || Created By : apelleti
232 || Created On : 02-MAY-2001
233 || Purpose : Validates the Foreign Keys for the table.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || (reverse chronological order - newest change first)
238 */
239 CURSOR cur_rowid IS
240 SELECT rowid
241 FROM igs_ps_rsv_usec_pri
242 WHERE ((uoo_id = x_uoo_id));
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 OPEN cur_rowid;
249 FETCH cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 CLOSE cur_rowid;
252 fnd_message.set_name ('IGS', 'IGS_PS_USPR_UOO_UFK');
253 igs_ge_msg_stack.add;
254 app_exception.raise_exception;
255 RETURN;
256 END IF;
257 CLOSE cur_rowid;
258
259 END get_ufk_igs_ps_unit_ofr_opt;
260
261
262 PROCEDURE before_dml (
263 p_action IN VARCHAR2,
264 x_rowid IN VARCHAR2 DEFAULT NULL,
265 x_rsv_usec_pri_id IN NUMBER DEFAULT NULL,
266 x_uoo_id IN NUMBER DEFAULT NULL,
267 x_priority_order IN NUMBER DEFAULT NULL,
268 x_priority_value IN VARCHAR2 DEFAULT NULL,
269 x_creation_date IN DATE DEFAULT NULL,
270 x_created_by IN NUMBER DEFAULT NULL,
271 x_last_update_date IN DATE DEFAULT NULL,
272 x_last_updated_by IN NUMBER DEFAULT NULL,
273 x_last_update_login IN NUMBER DEFAULT NULL
274 ) AS
275 /*
276 || Created By : apelleti
277 || Created On : 02-MAY-2001
278 || Purpose : Initialises the columns, Checks Constraints, Calls the
279 || Trigger Handlers for the table, before any DML operation.
280 || Known limitations, enhancements or remarks :
281 || Change History :
282 || Who When What
283 || (reverse chronological order - newest change first)
284 */
285 BEGIN
286
287 set_column_values (
288 p_action,
289 x_rowid,
290 x_rsv_usec_pri_id,
291 x_uoo_id,
292 x_priority_order,
293 x_priority_value,
294 x_creation_date,
295 x_created_by,
296 x_last_update_date,
297 x_last_updated_by,
298 x_last_update_login
299 );
300
301 IF (p_action = 'INSERT') THEN
302 -- Call all the procedures related to Before Insert.
303 IF ( get_pk_for_validation(
304 new_references.rsv_usec_pri_id
305 )
306 ) THEN
307 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
308 igs_ge_msg_stack.add;
309 app_exception.raise_exception;
310 END IF;
311 check_uniqueness;
312 check_parent_existance;
313 ELSIF (p_action = 'UPDATE') THEN
314 -- Call all the procedures related to Before Update.
315 check_uniqueness;
316 check_parent_existance;
317 ELSIF (p_action = 'DELETE') THEN
318 -- Call all the procedures related to Before Delete.
319 check_child_existance;
320 ELSIF (p_action = 'VALIDATE_INSERT') THEN
321 -- Call all the procedures related to Before Insert.
322 IF ( get_pk_for_validation (
323 new_references.rsv_usec_pri_id
324 )
325 ) THEN
326 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 END IF;
330 check_uniqueness;
331 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
332 check_uniqueness;
333 ELSIF (p_action = 'VALIDATE_DELETE') THEN
334 check_child_existance;
335 END IF;
336
337 l_rowid:=NULL;
338
339 END before_dml;
340
341
342 PROCEDURE insert_row (
343 x_rowid IN OUT NOCOPY VARCHAR2,
344 x_rsv_usec_pri_id IN OUT NOCOPY NUMBER,
345 x_uoo_id IN NUMBER,
346 x_priority_order IN NUMBER,
347 x_priority_value IN VARCHAR2,
348 x_mode IN VARCHAR2 DEFAULT 'R'
349 ) AS
350 /*
351 || Created By : apelleti
352 || Created On : 02-MAY-2001
353 || Purpose : Handles the INSERT DML logic for the table.
354 || Known limitations, enhancements or remarks :
355 || Change History :
356 || Who When What
357 || (reverse chronological order - newest change first)
358 */
359 CURSOR c IS
360 SELECT rowid
361 FROM igs_ps_rsv_usec_pri
362 WHERE rsv_usec_pri_id = x_rsv_usec_pri_id;
363
364 x_last_update_date DATE;
365 x_last_updated_by NUMBER;
366 x_last_update_login NUMBER;
367
368 BEGIN
369
370 x_last_update_date := SYSDATE;
371 IF (x_mode = 'I') THEN
372 x_last_updated_by := 1;
373 x_last_update_login := 0;
374 ELSIF (x_mode = 'R') THEN
375 x_last_updated_by := fnd_global.user_id;
376 IF (x_last_updated_by IS NULL) THEN
377 x_last_updated_by := -1;
378 END IF;
379 x_last_update_login := fnd_global.login_id;
380 IF (x_last_update_login IS NULL) THEN
381 x_last_update_login := -1;
382 END IF;
383 ELSE
384 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
385 igs_ge_msg_stack.add;
386 app_exception.raise_exception;
387 END IF;
388
389 SELECT igs_ps_rsv_usec_pri_s.NEXTVAL
390 INTO x_rsv_usec_pri_id
391 FROM dual;
392
393 before_dml(
394 p_action => 'INSERT',
395 x_rowid => x_rowid,
396 x_rsv_usec_pri_id => x_rsv_usec_pri_id,
397 x_uoo_id => x_uoo_id,
398 x_priority_order => x_priority_order,
399 x_priority_value => x_priority_value,
400 x_creation_date => x_last_update_date,
401 x_created_by => x_last_updated_by,
402 x_last_update_date => x_last_update_date,
403 x_last_updated_by => x_last_updated_by,
404 x_last_update_login => x_last_update_login
405 );
406
407 INSERT INTO igs_ps_rsv_usec_pri (
408 rsv_usec_pri_id,
409 uoo_id,
410 priority_order,
411 priority_value,
412 creation_date,
413 created_by,
414 last_update_date,
415 last_updated_by,
416 last_update_login
417 ) VALUES (
418 new_references.rsv_usec_pri_id,
419 new_references.uoo_id,
420 new_references.priority_order,
421 new_references.priority_value,
422 x_last_update_date,
423 x_last_updated_by,
424 x_last_update_date,
425 x_last_updated_by,
426 x_last_update_login
427 );
428
429 OPEN c;
430 FETCH c INTO x_rowid;
431 IF (c%NOTFOUND) THEN
432 CLOSE c;
433 RAISE NO_DATA_FOUND;
434 END IF;
435 CLOSE c;
436
437 END insert_row;
438
439
440 PROCEDURE lock_row (
441 x_rowid IN VARCHAR2,
442 x_rsv_usec_pri_id IN NUMBER,
443 x_uoo_id IN NUMBER,
444 x_priority_order IN NUMBER,
445 x_priority_value IN VARCHAR2
446 ) AS
447 /*
448 || Created By : apelleti
449 || Created On : 02-MAY-2001
450 || Purpose : Handles the LOCK mechanism for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 || (reverse chronological order - newest change first)
455 */
456 CURSOR c1 IS
457 SELECT
458 uoo_id,
459 priority_order,
460 priority_value
461 FROM igs_ps_rsv_usec_pri
462 WHERE rowid = x_rowid
463 FOR UPDATE NOWAIT;
464
465 tlinfo c1%ROWTYPE;
466
467 BEGIN
468
469 OPEN c1;
470 FETCH c1 INTO tlinfo;
471 IF (c1%notfound) THEN
472 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
473 igs_ge_msg_stack.add;
474 CLOSE c1;
475 app_exception.raise_exception;
476 RETURN;
477 END IF;
478 CLOSE c1;
479
480 IF (
481 (tlinfo.uoo_id = x_uoo_id)
482 AND (tlinfo.priority_order = x_priority_order)
483 AND (tlinfo.priority_value = x_priority_value)
484 ) THEN
485 NULL;
486 ELSE
487 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
488 igs_ge_msg_stack.add;
489 app_exception.raise_exception;
490 END IF;
491
492 RETURN;
493
494 END lock_row;
495
496
497 PROCEDURE update_row (
498 x_rowid IN VARCHAR2,
499 x_rsv_usec_pri_id IN NUMBER,
500 x_uoo_id IN NUMBER,
501 x_priority_order IN NUMBER,
502 x_priority_value IN VARCHAR2,
503 x_mode IN VARCHAR2 DEFAULT 'R'
504 ) AS
505 /*
506 || Created By : apelleti
507 || Created On : 02-MAY-2001
508 || Purpose : Handles the UPDATE DML logic for the table.
509 || Known limitations, enhancements or remarks :
510 || Change History :
511 || Who When What
512 || (reverse chronological order - newest change first)
513 */
514 x_last_update_date DATE ;
515 x_last_updated_by NUMBER;
516 x_last_update_login NUMBER;
517
518 BEGIN
519
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
539 before_dml(
540 p_action => 'UPDATE',
541 x_rowid => x_rowid,
542 x_rsv_usec_pri_id => x_rsv_usec_pri_id,
543 x_uoo_id => x_uoo_id,
544 x_priority_order => x_priority_order,
545 x_priority_value => x_priority_value,
546 x_creation_date => x_last_update_date,
547 x_created_by => x_last_updated_by,
548 x_last_update_date => x_last_update_date,
549 x_last_updated_by => x_last_updated_by,
550 x_last_update_login => x_last_update_login
551 );
552
553 UPDATE igs_ps_rsv_usec_pri
554 SET
555 uoo_id = new_references.uoo_id,
556 priority_order = new_references.priority_order,
557 priority_value = new_references.priority_value,
558 last_update_date = x_last_update_date,
559 last_updated_by = x_last_updated_by,
560 last_update_login = x_last_update_login
561 WHERE rowid = x_rowid;
562
563 IF (SQL%NOTFOUND) THEN
564 RAISE NO_DATA_FOUND;
565 END IF;
566
567 END update_row;
568
569
570 PROCEDURE add_row (
571 x_rowid IN OUT NOCOPY VARCHAR2,
572 x_rsv_usec_pri_id IN OUT NOCOPY NUMBER,
573 x_uoo_id IN NUMBER,
574 x_priority_order IN NUMBER,
575 x_priority_value IN VARCHAR2,
576 x_mode IN VARCHAR2 DEFAULT 'R'
577 ) AS
578 /*
579 || Created By : apelleti
580 || Created On : 02-MAY-2001
581 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
582 || Known limitations, enhancements or remarks :
583 || Change History :
584 || Who When What
585 || (reverse chronological order - newest change first)
586 */
587 CURSOR c1 IS
588 SELECT rowid
589 FROM igs_ps_rsv_usec_pri
590 WHERE rsv_usec_pri_id = x_rsv_usec_pri_id;
591
592 BEGIN
593
594 OPEN c1;
595 FETCH c1 INTO x_rowid;
596 IF (c1%NOTFOUND) THEN
597 CLOSE c1;
598
599 insert_row (
600 x_rowid,
601 x_rsv_usec_pri_id,
602 x_uoo_id,
603 x_priority_order,
604 x_priority_value,
605 x_mode
606 );
607 RETURN;
608 END IF;
609 CLOSE c1;
610
611 update_row (
612 x_rowid,
613 x_rsv_usec_pri_id,
614 x_uoo_id,
615 x_priority_order,
616 x_priority_value,
617 x_mode
618 );
619
620 END add_row;
621
622
623 PROCEDURE delete_row (
624 x_rowid IN VARCHAR2
625 ) AS
626 /*
627 || Created By : apelleti
628 || Created On : 02-MAY-2001
629 || Purpose : Handles the DELETE DML logic for the table.
630 || Known limitations, enhancements or remarks :
631 || Change History :
632 || Who When What
633 || (reverse chronological order - newest change first)
634 */
635 BEGIN
636
637 before_dml (
638 p_action => 'DELETE',
639 x_rowid => x_rowid
640 );
641
642 DELETE FROM igs_ps_rsv_usec_pri
643 WHERE rowid = x_rowid;
644
645 IF (SQL%NOTFOUND) THEN
646 RAISE NO_DATA_FOUND;
647 END IF;
648
649 END delete_row;
650
651
652 END igs_ps_rsv_usec_pri_pkg;
[Home] [Help]