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