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