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