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