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