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