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