1 PACKAGE BODY igs_fi_app_rules_pkg AS
2 /* $Header: IGSSI90B.pls 120.4 2005/10/10 23:19:28 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_app_rules%ROWTYPE;
6 new_references igs_fi_app_rules%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_appl_rule_id IN NUMBER DEFAULT NULL,
12 x_appl_hierarchy_id IN NUMBER DEFAULT NULL,
13 x_rule_sequence IN NUMBER DEFAULT NULL,
14 x_fee_type IN VARCHAR2 DEFAULT NULL,
15 x_enabled_flag IN VARCHAR2 DEFAULT NULL,
16 x_rule_type IN VARCHAR2 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 : BDEVARAK
25 || Created On : 26-APR-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 IGS_FI_APP_RULES
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.appl_rule_id := x_appl_rule_id;
57 new_references.appl_hierarchy_id := x_appl_hierarchy_id;
58 new_references.rule_sequence := x_rule_sequence;
59 new_references.fee_type := x_fee_type;
60 new_references.enabled_flag := x_enabled_flag;
61 new_references.rule_type := x_rule_type;
62
63
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END set_column_values;
77
78 PROCEDURE chk_unique_fee(x_appl_hierarchy_id IN NUMBER,
79 x_rule_type IN VARCHAR2,
80 x_fee_type IN VARCHAR2,
81 x_enabled_flag IN VARCHAR2) AS
82 /*
83 || Created By : AGAIROLA
84 || Created On : 10-Oct-2005
85 || Purpose : Checks for Uniqueness of Fee Type and Rule Type.
86 || Known limitations, enhancements or remarks :
87 || Change History :
88 || Who When What
89 || (reverse chronological order - newest change first)
90 */
91 CURSOR cur_app(cp_appl_hier_id IN NUMBER,
92 cp_rule_type IN VARCHAR2,
93 cp_fee_type IN VARCHAR2) IS
94 SELECT 'x'
95 FROM igs_fi_app_rules
96 WHERE appl_hierarchy_id = cp_appl_hier_id
97 AND rule_type = cp_rule_type
98 AND fee_type = cp_fee_type
99 AND enabled_flag = 'Y';
100
101 l_v_var VARCHAR2(1);
102 l_b_bool BOOLEAN;
103 BEGIN
104 l_b_bool := FALSE;
105 IF x_enabled_flag = 'Y' THEN
106 IF x_rule_type = 'ADDITION' THEN
107 OPEN cur_app(x_appl_hierarchy_id,
108 'ALLOW',
109 x_fee_type);
110 FETCH cur_app INTO l_v_var;
111 IF cur_app%FOUND THEN
112 l_b_bool := TRUE;
113 END IF;
114 CLOSE cur_app;
115 ELSE
116 OPEN cur_app(x_appl_hierarchy_id,
117 'ADDITION',
118 x_fee_type);
119 FETCH cur_app INTO l_v_var;
120 IF cur_app%FOUND THEN
121 l_b_bool := TRUE;
122 END IF;
123 CLOSE cur_app;
124 END IF;
125 END IF;
126
127 IF l_b_bool THEN
128 fnd_message.set_name ( 'IGS', 'IGS_FI_AHR_FTYP_CANT_ENABLE' );
129 fnd_message.set_token('FEE_TYPE',x_fee_type);
130 igs_ge_msg_stack.add;
131 app_exception.raise_exception;
132 END IF;
133
134 END chk_unique_fee;
135
136 FUNCTION unique_rule_seq (
137 x_appl_hierarchy_id IN NUMBER,
138 x_rule_type IN VARCHAR2,
139 x_rule_sequence IN NUMBER
140 ) RETURN BOOLEAN AS
141 /*
142 || Created By : JBEGUM
143 || Created On : 8-FEB-2002
144 || Purpose : Added as part of Enh bug#2191470 .This function ensures unique rule sequence number across
145 || the two tab pages of the application Hierarchy form
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 || vvutukur 30-dec-2002 Bug#2725955.Modified cursor cur_rule to add check for rowid also.
151 */
152 CURSOR cur_rule IS
153 SELECT rowid
154 FROM igs_fi_app_rules
155 WHERE appl_hierarchy_id = x_appl_hierarchy_id
156 AND rule_type = x_rule_type
157 AND rule_sequence = x_rule_sequence
158 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
159
160 l_rule cur_rule%ROWTYPE;
161
162 BEGIN
163
164 OPEN cur_rule;
165 FETCH cur_rule INTO l_rule;
166 IF (cur_rule%FOUND) THEN
167 CLOSE cur_rule;
168 RETURN (TRUE);
169 ELSE
170 CLOSE cur_rule;
171 RETURN(FALSE);
172 END IF;
173
174 END unique_rule_seq;
175
176 PROCEDURE check_uniqueness AS
177 /*
178 || Created By : BDEVARAK
179 || Created On : 26-APR-2001
180 || Purpose : Handles the Unique Constraint logic defined for the columns.
181 || Known limitations, enhancements or remarks :
182 || Change History :
183 || Who When What
184 || jbegum 8-Feb-02 Added call to local function unique_rule_seq
185 || As part of Enh Bug # 2191470
186 || (reverse chronological order - newest change first)
187 */
188 BEGIN
189
190 IF ( get_uk_for_validation (
191 new_references.appl_hierarchy_id,
192 new_references.fee_type,
193 new_references.rule_type
194 )
195 ) THEN
196 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
197 igs_ge_msg_stack.add;
198 app_exception.raise_exception;
199
200 END IF;
201
202 IF ( unique_rule_seq (new_references.appl_hierarchy_id,
203 new_references.rule_type,
204 new_references.rule_sequence
205 )
206 ) THEN
207 fnd_message.set_name ('IGS', 'IGS_FI_UNQ_RULE_SEQ');
208 igs_ge_msg_stack.add;
209 app_exception.raise_exception;
210
211 END IF;
212
213 END check_uniqueness;
214
215
216 PROCEDURE check_parent_existance AS
217 /*
218 || Created By : BDEVARAK
219 || Created On : 26-APR-2001
220 || Purpose : Checks for the existance of Parent records.
221 || Known limitations, enhancements or remarks :
222 || Change History :
223 || Who When What
224 || (reverse chronological order - newest change first)
225 */
226 BEGIN
227
228 IF (((old_references.appl_hierarchy_id = new_references.appl_hierarchy_id)) OR
229 ((new_references.appl_hierarchy_id IS NULL))) THEN
230 NULL;
231 ELSIF NOT igs_fi_a_hierarchies_pkg.get_pk_for_validation (
232 new_references.appl_hierarchy_id
233 ) THEN
234 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
235 igs_ge_msg_stack.add;
236 app_exception.raise_exception;
237 END IF;
238
239 IF (((old_references.rule_type = new_references.rule_type)) OR
240 ((new_references.rule_type IS NULL))) THEN
241 NULL;
242 ELSE
243 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
244 'IGS_FI_RULE_TYPE',
245 new_references.rule_type
246 ) THEN
247 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
248 IGS_GE_MSG_STACK.ADD;
249 App_Exception.Raise_Exception;
250 END IF;
251 END IF;
252
253 END check_parent_existance;
254
255
256 FUNCTION get_pk_for_validation (
257 x_appl_rule_id IN NUMBER
258 ) RETURN BOOLEAN AS
259 /*
260 || Created By : BDEVARAK
261 || Created On : 26-APR-2001
262 || Purpose : Validates the Primary Key of the table.
263 || Known limitations, enhancements or remarks :
264 || Change History :
265 || Who When What
266 || (reverse chronological order - newest change first)
267 */
268 CURSOR cur_rowid IS
269 SELECT rowid
270 FROM igs_fi_app_rules
271 WHERE appl_rule_id = x_appl_rule_id
272 FOR UPDATE NOWAIT;
273
274 lv_rowid cur_rowid%RowType;
275
276 BEGIN
277
278 OPEN cur_rowid;
279 FETCH cur_rowid INTO lv_rowid;
280 IF (cur_rowid%FOUND) THEN
281 CLOSE cur_rowid;
282 RETURN(TRUE);
283 ELSE
284 CLOSE cur_rowid;
285 RETURN(FALSE);
286 END IF;
287
288 END get_pk_for_validation;
289
290
291 FUNCTION get_uk_for_validation (
292 x_appl_hierarchy_id IN NUMBER,
293 x_fee_type IN VARCHAR2,
294 x_rule_type IN VARCHAR2
295 ) RETURN BOOLEAN AS
296 /*
297 || Created By : BDEVARAK
298 || Created On : 26-APR-2001
299 || Purpose : Validates the Unique Keys of the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 || agairola 06-Oct-2005 For bug 4212082
305 */
306 CURSOR cur_rowid IS
307 SELECT rowid
308 FROM igs_fi_app_rules
309 WHERE appl_hierarchy_id = x_appl_hierarchy_id
310 AND fee_type = x_fee_type
311 AND rule_type = x_rule_type
312 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
313
314 lv_rowid cur_rowid%RowType;
315
316 BEGIN
317
318 OPEN cur_rowid;
319 FETCH cur_rowid INTO lv_rowid;
320 IF (cur_rowid%FOUND) THEN
321 CLOSE cur_rowid;
322 RETURN (true);
323 ELSE
324 CLOSE cur_rowid;
325 RETURN(FALSE);
326 END IF;
327
328 END get_uk_for_validation ;
329
330 PROCEDURE get_fk_igs_fi_a_hierarchies (
331 x_appl_hierarchy_id IN NUMBER
332 ) AS
333 /*
334 || Created By : BDEVARAK
335 || Created On : 26-APR-2001
336 || Purpose : Validates the Foreign Keys for the table.
337 || Known limitations, enhancements or remarks :
338 || Change History :
339 || Who When What
340 || (reverse chronological order - newest change first)
341 */
342 CURSOR cur_rowid IS
343 SELECT rowid
344 FROM igs_fi_app_rules
345 WHERE ((appl_hierarchy_id = x_appl_hierarchy_id));
346
347 lv_rowid cur_rowid%RowType;
348
349 BEGIN
350
351 OPEN cur_rowid;
352 FETCH cur_rowid INTO lv_rowid;
353 IF (cur_rowid%FOUND) THEN
354 CLOSE cur_rowid;
355 fnd_message.set_name ('IGS', 'IGS_FI_HRRL_APHR_FK');
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 RETURN;
359 END IF;
360 CLOSE cur_rowid;
361
362 END get_fk_igs_fi_a_hierarchies;
363
364
365 PROCEDURE before_dml (
366 p_action IN VARCHAR2,
367 x_rowid IN VARCHAR2 DEFAULT NULL,
368 x_appl_rule_id IN NUMBER DEFAULT NULL,
369 x_appl_hierarchy_id IN NUMBER DEFAULT NULL,
373 x_rule_type IN VARCHAR2 DEFAULT NULL,
370 x_rule_sequence IN NUMBER DEFAULT NULL,
371 x_fee_type IN VARCHAR2 DEFAULT NULL,
372 x_enabled_flag IN VARCHAR2 DEFAULT NULL,
374 x_creation_date IN DATE DEFAULT NULL,
375 x_created_by IN NUMBER DEFAULT NULL,
376 x_last_update_date IN DATE DEFAULT NULL,
377 x_last_updated_by IN NUMBER DEFAULT NULL,
378 x_last_update_login IN NUMBER DEFAULT NULL
379 ) AS
380 /*
381 || Created By : BDEVARAK
382 || Created On : 26-APR-2001
383 || Purpose : Initialises the columns, Checks Constraints, Calls the
384 || Trigger Handlers for the table, before any DML operation.
385 || Known limitations, enhancements or remarks :
386 || Change History :
387 || Who When What
388 || (reverse chronological order - newest change first)
389 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null.
390 */
391 BEGIN
392
393 set_column_values (
394 p_action,
395 x_rowid,
396 x_appl_rule_id,
397 x_appl_hierarchy_id,
398 x_rule_sequence,
399 x_fee_type,
400 x_enabled_flag,
401 x_rule_type,
402 x_creation_date,
403 x_created_by,
404 x_last_update_date,
405 x_last_updated_by,
406 x_last_update_login
407 );
408
409 IF (p_action = 'INSERT') THEN
410 -- Call all the procedures related to Before Insert.
411 IF ( get_pk_for_validation(
412 new_references.appl_rule_id
413 )
414 ) THEN
415 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419 check_uniqueness;
420 check_parent_existance;
421 chk_unique_fee(x_appl_hierarchy_id,
422 x_rule_type,
423 x_fee_type,
424 x_enabled_flag);
425 ELSIF (p_action = 'UPDATE') THEN
426 -- Call all the procedures related to Before Update.
427 check_uniqueness;
428 check_parent_existance;
429 chk_unique_fee(x_appl_hierarchy_id,
430 x_rule_type,
431 x_fee_type,
432 x_enabled_flag);
433 ELSIF (p_action = 'VALIDATE_INSERT') THEN
434 -- Call all the procedures related to Before Insert.
435 IF ( get_pk_for_validation (
436 new_references.appl_rule_id
437 )
438 ) THEN
439 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
440 igs_ge_msg_stack.add;
441 app_exception.raise_exception;
442 END IF;
443 check_uniqueness;
444 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
445 check_uniqueness;
446 END IF;
447 l_rowid := NULL;
448 END before_dml;
449
450
451 PROCEDURE insert_row (
452 x_rowid IN OUT NOCOPY VARCHAR2,
453 x_appl_rule_id IN OUT NOCOPY NUMBER,
454 x_appl_hierarchy_id IN NUMBER,
455 x_rule_sequence IN NUMBER,
456 x_fee_type IN VARCHAR2,
457 x_enabled_flag IN VARCHAR2,
458 x_rule_type IN VARCHAR2,
459 x_mode IN VARCHAR2 DEFAULT 'R'
460 ) AS
461 /*
462 || Created By : BDEVARAK
463 || Created On : 26-APR-2001
464 || Purpose : Handles the INSERT DML logic for the table.
465 || Known limitations, enhancements or remarks :
466 || Change History :
467 || Who When What
468 || (reverse chronological order - newest change first)
469 */
470 CURSOR c IS
471 SELECT rowid
472 FROM igs_fi_app_rules
473 WHERE appl_rule_id = x_appl_rule_id;
474
475 x_last_update_date DATE;
476 x_last_updated_by NUMBER;
477 x_last_update_login NUMBER;
478
479 BEGIN
480
481 x_last_update_date := SYSDATE;
482 IF (x_mode = 'I') THEN
483 x_last_updated_by := 1;
484 x_last_update_login := 0;
485 ELSIF (x_mode = 'R') THEN
486 x_last_updated_by := fnd_global.user_id;
487 IF (x_last_updated_by IS NULL) THEN
488 x_last_updated_by := -1;
489 END IF;
490 x_last_update_login := fnd_global.login_id;
491 IF (x_last_update_login IS NULL) THEN
492 x_last_update_login := -1;
493 END IF;
494 ELSE
495 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 END IF;
499
500 SELECT igs_fi_app_rules_s.NEXTVAL
501 INTO x_appl_rule_id
502 FROM dual;
503
504 before_dml(
505 p_action => 'INSERT',
509 x_rule_sequence => x_rule_sequence,
506 x_rowid => x_rowid,
507 x_appl_rule_id => x_appl_rule_id,
508 x_appl_hierarchy_id => x_appl_hierarchy_id,
510 x_fee_type => x_fee_type,
511 x_enabled_flag => x_enabled_flag,
512 x_rule_type => x_rule_type,
513 x_creation_date => x_last_update_date,
514 x_created_by => x_last_updated_by,
515 x_last_update_date => x_last_update_date,
516 x_last_updated_by => x_last_updated_by,
517 x_last_update_login => x_last_update_login
518 );
519
520 INSERT INTO igs_fi_app_rules (
521 appl_rule_id,
522 appl_hierarchy_id,
523 rule_sequence,
524 fee_type,
525 enabled_flag,
526 rule_type,
527 creation_date,
528 created_by,
529 last_update_date,
530 last_updated_by,
531 last_update_login
532 ) VALUES (
533 new_references.appl_rule_id,
534 new_references.appl_hierarchy_id,
535 new_references.rule_sequence,
536 new_references.fee_type,
537 new_references.enabled_flag,
538 new_references.rule_type,
539 x_last_update_date,
540 x_last_updated_by,
541 x_last_update_date,
542 x_last_updated_by,
543 x_last_update_login
544 );
545
546 OPEN c;
547 FETCH c INTO x_rowid;
548 IF (c%NOTFOUND) THEN
549 CLOSE c;
550 RAISE NO_DATA_FOUND;
551 END IF;
552 CLOSE c;
553
554 END insert_row;
555
556
557 PROCEDURE lock_row (
558 x_rowid IN VARCHAR2,
559 x_appl_rule_id IN NUMBER,
560 x_appl_hierarchy_id IN NUMBER,
561 x_rule_sequence IN NUMBER,
562 x_fee_type IN VARCHAR2,
563 x_enabled_flag IN VARCHAR2,
564 x_rule_type IN VARCHAR2
565 ) AS
566 /*
567 || Created By : BDEVARAK
568 || Created On : 26-APR-2001
569 || Purpose : Handles the LOCK mechanism for the table.
570 || Known limitations, enhancements or remarks :
571 || Change History :
572 || Who When What
573 || (reverse chronological order - newest change first)
574 */
575 CURSOR c1 IS
576 SELECT
577 appl_hierarchy_id,
578 rule_sequence,
579 fee_type,
580 enabled_flag,
581 rule_type
582 FROM igs_fi_app_rules
583 WHERE rowid = x_rowid
584 FOR UPDATE NOWAIT;
585
586 tlinfo c1%ROWTYPE;
587
588 BEGIN
589
590 OPEN c1;
591 FETCH c1 INTO tlinfo;
592 IF (c1%notfound) THEN
593 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
594 igs_ge_msg_stack.add;
595 CLOSE c1;
596 app_exception.raise_exception;
597 RETURN;
598 END IF;
599 CLOSE c1;
600
601 IF (
602 (tlinfo.appl_hierarchy_id = x_appl_hierarchy_id)
603 AND (tlinfo.rule_sequence = x_rule_sequence)
604 AND (tlinfo.fee_type = x_fee_type)
605 AND (tlinfo.enabled_flag = x_enabled_flag)
606 AND (tlinfo.rule_type = x_rule_type)
607 ) THEN
608 NULL;
609 ELSE
610 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
611 igs_ge_msg_stack.add;
612 app_exception.raise_exception;
613 END IF;
614
615 RETURN;
616
617 END lock_row;
618
619
620 PROCEDURE update_row (
621 x_rowid IN VARCHAR2,
622 x_appl_rule_id IN NUMBER,
626 x_enabled_flag IN VARCHAR2,
623 x_appl_hierarchy_id IN NUMBER,
624 x_rule_sequence IN NUMBER,
625 x_fee_type IN VARCHAR2,
627 x_rule_type IN VARCHAR2,
628 x_mode IN VARCHAR2 DEFAULT 'R'
629 ) AS
630 /*
631 || Created By : BDEVARAK
632 || Created On : 26-APR-2001
633 || Purpose : Handles the UPDATE DML logic for the table.
634 || Known limitations, enhancements or remarks :
635 || Change History :
636 || Who When What
637 || (reverse chronological order - newest change first)
638 */
639 x_last_update_date DATE ;
640 x_last_updated_by NUMBER;
641 x_last_update_login NUMBER;
642
643 BEGIN
644
645 x_last_update_date := SYSDATE;
646 IF (X_MODE = 'I') THEN
647 x_last_updated_by := 1;
648 x_last_update_login := 0;
649 ELSIF (x_mode = 'R') THEN
650 x_last_updated_by := fnd_global.user_id;
651 IF x_last_updated_by IS NULL THEN
652 x_last_updated_by := -1;
653 END IF;
654 x_last_update_login := fnd_global.login_id;
655 IF (x_last_update_login IS NULL) THEN
656 x_last_update_login := -1;
657 END IF;
658 ELSE
659 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
660 igs_ge_msg_stack.add;
661 app_exception.raise_exception;
662 END IF;
663
664 before_dml(
665 p_action => 'UPDATE',
666 x_rowid => x_rowid,
667 x_appl_rule_id => x_appl_rule_id,
668 x_appl_hierarchy_id => x_appl_hierarchy_id,
669 x_rule_sequence => x_rule_sequence,
670 x_fee_type => x_fee_type,
671 x_enabled_flag => x_enabled_flag,
672 x_rule_type => x_rule_type,
673 x_creation_date => x_last_update_date,
674 x_created_by => x_last_updated_by,
675 x_last_update_date => x_last_update_date,
676 x_last_updated_by => x_last_updated_by,
677 x_last_update_login => x_last_update_login
678 );
679
680 UPDATE igs_fi_app_rules
681 SET
682 appl_hierarchy_id = new_references.appl_hierarchy_id,
683 rule_sequence = new_references.rule_sequence,
684 fee_type = new_references.fee_type,
685 enabled_flag = new_references.enabled_flag,
686 rule_type = new_references.rule_type,
687 last_update_date = x_last_update_date,
688 last_updated_by = x_last_updated_by,
689 last_update_login = x_last_update_login
690 WHERE rowid = x_rowid;
691
692 IF (SQL%NOTFOUND) THEN
693 RAISE NO_DATA_FOUND;
694 END IF;
695
696 END update_row;
697
698
699 PROCEDURE add_row (
700 x_rowid IN OUT NOCOPY VARCHAR2,
701 x_appl_rule_id IN OUT NOCOPY NUMBER,
702 x_appl_hierarchy_id IN NUMBER,
703 x_rule_sequence IN NUMBER,
704 x_fee_type IN VARCHAR2,
705 x_enabled_flag IN VARCHAR2,
706 x_rule_type IN VARCHAR2,
707 x_mode IN VARCHAR2 DEFAULT 'R'
708 ) AS
709 /*
710 || Created By : BDEVARAK
711 || Created On : 26-APR-2001
712 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
713 || Known limitations, enhancements or remarks :
714 || Change History :
715 || Who When What
716 || (reverse chronological order - newest change first)
717 */
718 CURSOR c1 IS
719 SELECT rowid
720 FROM igs_fi_app_rules
721 WHERE appl_rule_id = x_appl_rule_id;
722
723 BEGIN
724
725 OPEN c1;
726 FETCH c1 INTO x_rowid;
727 IF (c1%NOTFOUND) THEN
728 CLOSE c1;
729
730 insert_row (
731 x_rowid,
732 x_appl_rule_id,
733 x_appl_hierarchy_id,
734 x_rule_sequence,
735 x_fee_type,
736 x_enabled_flag,
737 x_rule_type,
738 x_mode
739 );
740 RETURN;
741 END IF;
742 CLOSE c1;
743
744 update_row (
745 x_rowid,
746 x_appl_rule_id,
747 x_appl_hierarchy_id,
748 x_rule_sequence,
749 x_fee_type,
750 x_enabled_flag,
751 x_rule_type,
752 x_mode
753 );
754
755 END add_row;
756
757
758 PROCEDURE delete_row (
759 x_rowid IN VARCHAR2
760 ) AS
761 /*
762 || Created By : BDEVARAK
763 || Created On : 26-APR-2001
764 || Purpose : Handles the DELETE DML logic for the table.
765 || Known limitations, enhancements or remarks :
766 || Change History :
767 || Who When What
768 || (reverse chronological order - newest change first)
769 */
770 BEGIN
771
772 before_dml (
773 p_action => 'DELETE',
774 x_rowid => x_rowid
775 );
776
777 DELETE FROM igs_fi_app_rules
778 WHERE rowid = x_rowid;
779
780 IF (SQL%NOTFOUND) THEN
781 RAISE NO_DATA_FOUND;
782 END IF;
783
784 END delete_row;
785
786
787 END igs_fi_app_rules_pkg;
[Home] [Help]