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