1 PACKAGE BODY igs_fi_refund_setup_pkg AS
2 /* $Header: IGSSIB3B.pls 115.13 2002/12/19 06:12:32 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_refund_setup%ROWTYPE;
6 new_references igs_fi_refund_setup%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_refund_setup_id IN NUMBER DEFAULT NULL,
12 x_start_date IN DATE DEFAULT NULL,
13 x_end_date IN DATE DEFAULT NULL,
14 x_amount_high IN NUMBER DEFAULT NULL,
15 x_amount_low IN NUMBER DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22 /*
23 || Created By : [email protected]
24 || Created On : 26-FEB-2002
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 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igs_fi_refund_setup
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.refund_setup_id := x_refund_setup_id;
56 new_references.start_date := x_start_date;
57 new_references.end_date := x_end_date;
58 new_references.amount_high := x_amount_high;
59 new_references.amount_low := x_amount_low;
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75
76 FUNCTION get_pk_for_validation (
77 x_refund_setup_id IN NUMBER
78 ) RETURN BOOLEAN AS
79 /*
80 || Created By : [email protected]
81 || Created On : 26-FEB-2002
82 || Purpose : Validates the Primary Key of the table.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 CURSOR cur_rowid IS
89 SELECT rowid
90 FROM igs_fi_refund_setup
91 WHERE refund_setup_id = x_refund_setup_id
92 FOR UPDATE NOWAIT;
93
94 lv_rowid cur_rowid%RowType;
95
96 BEGIN
97
98 OPEN cur_rowid;
99 FETCH cur_rowid INTO lv_rowid;
100 IF (cur_rowid%FOUND) THEN
101 CLOSE cur_rowid;
102 RETURN(TRUE);
103 ELSE
104 CLOSE cur_rowid;
105 RETURN(FALSE);
106 END IF;
107
108 END get_pk_for_validation;
109
110 FUNCTION check_range_overlap(
111 p_start_date IN DATE,
112 p_end_date IN DATE,
113 p_refund_setup_id IN NUMBER )
114 RETURN BOOLEAN AS
115 /*
116 || Created By : [email protected]
117 || Created On : 27-FEB-2002
118 || Purpose : To ensure that tolerance limit periods do not overlap
119 ||
120 || Known limitations, enhancements or remarks :
121 || Change History :
122 || Who When What
123 || (reverse chronological order - newest change first)
124 */
125
126 CURSOR cur_lmt is
127 SELECT start_date ,
128 end_date
129 FROM igs_fi_refund_setup
130 WHERE refund_setup_id<>p_refund_setup_id OR
131 p_refund_setup_id IS NULL;
132 l_flag BOOLEAN :=false;
133 BEGIN
134 FOR l_lmt IN cur_lmt LOOP
135 IF (l_lmt.end_date IS NOT NULL) THEN
136 -- a) period overlaps with an existing refunds period(start date is between an
137 -- existing start and end date)
138 IF (p_start_date >= l_lmt.start_date AND
139 p_start_date <= l_lmt.end_date) THEN
140 l_flag:=true;
141 EXIT;
142 END IF;
143 -- b) period overlaps with an existing refunds period(end date is between an
144 -- existing start and end date)
145 IF (p_end_date IS NOT NULL) THEN
146 IF (p_end_date >= l_lmt.start_date AND
147 p_end_date <= l_lmt.end_date) THEN
148 l_flag:=true;
149 EXIT;
150 END IF;
151 -- c) period encompasses an existing refunds period
152 IF (p_start_date <= l_lmt.start_date AND
153 p_end_date >= l_lmt.end_date) THEN
154 l_flag:=true;
155 EXIT;
156 END IF;
157 ELSE -- p_end_date is null
158 --d)period overlaps with existing refunds period.
159 IF (p_start_date <= l_lmt.start_date OR
160 p_start_date<= l_lmt.end_date) THEN
161 l_flag:=true;
162 EXIT;
163 END IF;
164 END IF;
165 ELSE -- l_lmt.end_date is null
166 IF (p_start_date >=l_lmt.start_date OR
167 NVL(p_end_date,l_lmt.start_date) >= l_lmt.start_date) THEN
168 l_flag:=true;
169 EXIT;
170 END IF;
171 END IF;
172 END LOOP;
173 RETURN l_flag;
174 END check_range_overlap;
175
176 PROCEDURE beforerowinsertupdate(
177 p_inserting IN BOOLEAN DEFAULT NULL,
178 p_updating IN BOOLEAN DEFAULT NULL
179 ) AS
180 /*
181 || Created By : [email protected]
182 || Created On : 27-FEB-2002
183 || Purpose : Checks Constraints, Calls the
184 || Trigger Handlers for the table, before insert or update.
185 ||
186 || Known limitations, enhancements or remarks :
187 || Change History :
188 || Who When What
189 || (reverse chronological order - newest change first)
190 */
191 l_sysdate DATE :=trunc(SYSDATE);
192
193 BEGIN
194 IF (p_inserting) THEN
195 -- Call all the procedures related to Before Insert.
196 IF ( get_pk_for_validation(
197 new_references.refund_setup_id
198 )
199 ) THEN
200 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203 END IF;
204
205 --Start date should be greater then sysdate date.
206 IF new_references.start_date < l_sysdate THEN
207 fnd_message.set_name('IGS','IGS_FI_EFF_DATE');
208 igs_ge_msg_stack.add;
209 app_exception.raise_exception;
210
211 END IF;
212
213 --End date should be greater then sysdate date.
214 IF new_references.end_date IS NOT NULL THEN
215 IF new_references.end_date < l_sysdate THEN
216 fnd_message.set_name('IGS','IGS_FI_END_DT_LESS_THAN_SD');
217 igs_ge_msg_stack.add;
218 app_exception.raise_exception;
219 END IF;
220 END IF;
221
222 --upper limit should be positive.
223
224 IF new_references.amount_high IS NOT NULL THEN
225 IF new_references.amount_high < 0 THEN
226 fnd_message.set_name('IGS','IGS_FI_REFUND_AMNT_NEGATIVE');
227 igs_ge_msg_stack.add;
228 app_exception.raise_exception;
229 END IF;
230 END IF;
231
232 --lower limit should be positive.
233 IF new_references.amount_low IS NOT NULL THEN
234 IF new_references.amount_low < 0 THEN
235 fnd_message.set_name('IGS','IGS_FI_REFUND_AMNT_NEGATIVE');
236 igs_ge_msg_stack.add;
237 app_exception.raise_exception;
238 END IF;
239 END IF;
240
241 --start date should be lesser than end date
242 IF new_references.end_date IS NOT NULL THEN
243 IF new_references.end_date < new_references.start_date THEN
244 fnd_message.set_name('IGS','IGS_FI_END_DT_LESS_THAN_ST_DT');
245 igs_ge_msg_stack.add;
246 app_exception.raise_exception;
247 END IF;
248 END IF;
249
250 --Upper and lower limit both should not be null.
251 IF new_references.amount_high IS NULL AND new_references.amount_low IS NULL THEN
252 fnd_message.set_name('IGS','IGS_FI_REFUND_LMT_NULL');
253 igs_ge_msg_stack.add;
254 app_exception.raise_exception;
255 ELSE
256 --lower limit should be less than upper limit.
257 IF new_references.amount_high IS NOT NULL AND new_references.amount_low IS NOT NULL THEN
258 IF new_references.amount_low > new_references.amount_high THEN
259
260 fnd_message.set_name('IGS','IGS_FI_RFND_AMNT_INVALID');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 END IF;
265 END IF;
266
267 --The tolerance limit ranges should not overlap
268 IF ( check_range_overlap(
269 new_references.start_date,
270 new_references.end_date,
271 new_references.refund_setup_id
272 )
273 ) THEN
274 -- Removed the space after the message name as per Bug# 2684818 by shtatiko
275 fnd_message.set_name('IGS','IGS_FI_RFND_TOL_OVERLAP');
276 igs_ge_msg_stack.add;
277 app_exception.raise_exception;
278 END IF;
279
280
281 ELSIF (p_updating) THEN
282 -- Call all the procedures related to Before Update.
283
284 --start date should be lesser than end date
285 IF new_references.end_date IS NOT NULL THEN
286 IF new_references.end_date < new_references.start_date THEN
287 fnd_message.set_name('IGS','IGS_FI_END_DT_LESS_THAN_ST_DT');
288 igs_ge_msg_stack.add;
289 app_exception.raise_exception;
290 END IF;
291 END IF;
292
293
294 --End date should be greater then sysdate date.
295 IF new_references.end_date IS NOT NULL THEN
296 IF new_references.end_date < l_sysdate THEN
297 fnd_message.set_name('IGS','IGS_FI_END_DT_LESS_THAN_SD');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 END IF;
301 END IF;
302
303
304 --The tolerance limit ranges should not overlap
305 IF ( check_range_overlap(
306 new_references.start_date,
307 new_references.end_date,
308 new_references.refund_setup_id
309 )
310 ) THEN
311 fnd_message.set_name('IGS','IGS_FI_RFND_TOL_OVERLAP');
312 igs_ge_msg_stack.add;
313 app_exception.raise_exception;
314 END IF;
315
316 END IF;
317 END beforerowinsertupdate;
318
319 PROCEDURE before_dml (
320 p_action IN VARCHAR2,
321 x_rowid IN VARCHAR2 DEFAULT NULL,
322 x_refund_setup_id IN NUMBER DEFAULT NULL,
323 x_start_date IN DATE DEFAULT NULL,
324 x_end_date IN DATE DEFAULT NULL,
325 x_amount_high IN NUMBER DEFAULT NULL,
326 x_amount_low IN NUMBER DEFAULT NULL,
327 x_creation_date IN DATE DEFAULT NULL,
328 x_created_by IN NUMBER DEFAULT NULL,
329 x_last_update_date IN DATE DEFAULT NULL,
330 x_last_updated_by IN NUMBER DEFAULT NULL,
331 x_last_update_login IN NUMBER DEFAULT NULL
332 ) AS
333 /*
334 || Created By : [email protected]
335 || Created On : 26-FEB-2002
336 || Purpose : Initialises the columns, Checks Constraints, Calls the
337 || Trigger Handlers for the table, before any DML operation.
338 || Known limitations, enhancements or remarks :
339 || Change History :
340 || Who When What
341 || sbaliga 27-feb-2002 Included code for checking for tolerance periods
342 || overlap as part of refunds build#2144600
343 || (reverse chronological order - newest change first)
344 */
345 BEGIN
346
347 set_column_values (
348 p_action,
349 x_rowid,
350 x_refund_setup_id,
351 x_start_date,
352 x_end_date,
353 x_amount_high,
354 x_amount_low,
355 x_creation_date,
356 x_created_by,
357 x_last_update_date,
358 x_last_updated_by,
359 x_last_update_login
360 );
361
362 IF (p_action = 'INSERT') OR (p_action= 'VALIDATE_INSERT') THEN
363 -- Call all the procedures related to Before Insert.
364 beforerowinsertupdate(p_inserting => TRUE);
365 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
366 -- Call all the procedures related to Before Update.
367 beforerowinsertupdate(p_updating => TRUE);
368 END IF;
369
370 END before_dml;
371
372
373 PROCEDURE insert_row (
374 x_rowid IN OUT NOCOPY VARCHAR2,
375 x_refund_setup_id IN OUT NOCOPY NUMBER,
376 x_start_date IN DATE,
377 x_end_date IN DATE,
378 x_amount_high IN NUMBER,
379 x_amount_low IN NUMBER,
380 x_mode IN VARCHAR2 DEFAULT 'R'
381 ) AS
382 /*
383 || Created By : [email protected]
384 || Created On : 26-FEB-2002
385 || Purpose : Handles the INSERT DML logic for the table.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR c IS
392 SELECT rowid
393 FROM igs_fi_refund_setup
394 WHERE refund_setup_id = x_refund_setup_id;
395
396 x_last_update_date DATE;
397 x_last_updated_by NUMBER;
398 x_last_update_login NUMBER;
399
400 BEGIN
401
402 x_last_update_date := SYSDATE;
403 IF (x_mode = 'I') THEN
404 x_last_updated_by := 1;
405 x_last_update_login := 0;
409 x_last_updated_by := -1;
406 ELSIF (x_mode = 'R') THEN
407 x_last_updated_by := fnd_global.user_id;
408 IF (x_last_updated_by IS NULL) THEN
410 END IF;
411 x_last_update_login := fnd_global.login_id;
412 IF (x_last_update_login IS NULL) THEN
413 x_last_update_login := -1;
414 END IF;
415 ELSE
416 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
417 igs_ge_msg_stack.add;
418 app_exception.raise_exception;
419 END IF;
420
421 SELECT igs_fi_refund_setup_s.NEXTVAL
422 INTO x_refund_setup_id
423 FROM dual;
424
425 before_dml(
426 p_action => 'INSERT',
427 x_rowid => x_rowid,
428 x_refund_setup_id => x_refund_setup_id,
429 x_start_date => x_start_date,
430 x_end_date => x_end_date,
431 x_amount_high => x_amount_high,
432 x_amount_low => x_amount_low,
433 x_creation_date => x_last_update_date,
434 x_created_by => x_last_updated_by,
435 x_last_update_date => x_last_update_date,
436 x_last_updated_by => x_last_updated_by,
437 x_last_update_login => x_last_update_login
438 );
439
440 INSERT INTO igs_fi_refund_setup (
441 refund_setup_id,
442 start_date,
443 end_date,
444 amount_high,
445 amount_low,
446 creation_date,
447 created_by,
448 last_update_date,
449 last_updated_by,
450 last_update_login
451 ) VALUES (
452 new_references.refund_setup_id,
453 new_references.start_date,
454 new_references.end_date,
455 new_references.amount_high,
456 new_references.amount_low,
457 x_last_update_date,
458 x_last_updated_by,
459 x_last_update_date,
460 x_last_updated_by,
461 x_last_update_login
462 );
463
464 OPEN c;
465 FETCH c INTO x_rowid;
466 IF (c%NOTFOUND) THEN
467 CLOSE c;
468 RAISE NO_DATA_FOUND;
469 END IF;
470 CLOSE c;
471
472 END insert_row;
473
474
475 PROCEDURE lock_row (
476 x_rowid IN VARCHAR2,
477 x_refund_setup_id IN NUMBER,
478 x_start_date IN DATE,
479 x_end_date IN DATE,
480 x_amount_high IN NUMBER,
481 x_amount_low IN NUMBER
482 ) AS
483 /*
484 || Created By : [email protected]
485 || Created On : 26-FEB-2002
486 || Purpose : Handles the LOCK mechanism for the table.
487 || Known limitations, enhancements or remarks :
488 || Change History :
489 || Who When What
490 || (reverse chronological order - newest change first)
491 */
492 CURSOR c1 IS
493 SELECT
494 start_date,
495 end_date,
496 amount_high,
497 amount_low
498 FROM igs_fi_refund_setup
499 WHERE rowid = x_rowid
500 FOR UPDATE NOWAIT;
501
502 tlinfo c1%ROWTYPE;
503
504 BEGIN
505
506 OPEN c1;
507 FETCH c1 INTO tlinfo;
508 IF (c1%notfound) THEN
509 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
510 igs_ge_msg_stack.add;
511 CLOSE c1;
512 app_exception.raise_exception;
513 RETURN;
514 END IF;
515 CLOSE c1;
516
517 IF (
518 (tlinfo.start_date = x_start_date)
519 AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
520 AND ((tlinfo.amount_high = x_amount_high) OR ((tlinfo.amount_high IS NULL) AND (X_amount_high IS NULL)))
521 AND ((tlinfo.amount_low = x_amount_low) OR ((tlinfo.amount_low IS NULL) AND (X_amount_low IS NULL)))
522 ) THEN
523 NULL;
524 ELSE
525 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
528 END IF;
529
530 RETURN;
531
532 END lock_row;
533
534
535 PROCEDURE update_row (
536 x_rowid IN VARCHAR2,
537 x_refund_setup_id IN NUMBER,
538 x_start_date IN DATE,
539 x_end_date IN DATE,
540 x_amount_high IN NUMBER,
541 x_amount_low IN NUMBER,
542 x_mode IN VARCHAR2 DEFAULT 'R'
543 ) AS
544 /*
545 || Created By : [email protected]
546 || Created On : 26-FEB-2002
547 || Purpose : Handles the UPDATE DML logic for the table.
548 || Known limitations, enhancements or remarks :
549 || Change History :
550 || Who When What
554 x_last_updated_by NUMBER;
551 || (reverse chronological order - newest change first)
552 */
553 x_last_update_date DATE ;
555 x_last_update_login NUMBER;
556
557 BEGIN
558
559 x_last_update_date := SYSDATE;
560 IF (X_MODE = 'I') THEN
561 x_last_updated_by := 1;
562 x_last_update_login := 0;
563 ELSIF (x_mode = 'R') THEN
564 x_last_updated_by := fnd_global.user_id;
565 IF x_last_updated_by IS NULL THEN
566 x_last_updated_by := -1;
567 END IF;
568 x_last_update_login := fnd_global.login_id;
569 IF (x_last_update_login IS NULL) THEN
570 x_last_update_login := -1;
571 END IF;
572 ELSE
573 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
574 igs_ge_msg_stack.add;
575 app_exception.raise_exception;
576 END IF;
577
578 before_dml(
579 p_action => 'UPDATE',
580 x_rowid => x_rowid,
581 x_refund_setup_id => x_refund_setup_id,
582 x_start_date => x_start_date,
583 x_end_date => x_end_date,
584 x_amount_high => x_amount_high,
585 x_amount_low => x_amount_low,
586 x_creation_date => x_last_update_date,
587 x_created_by => x_last_updated_by,
588 x_last_update_date => x_last_update_date,
589 x_last_updated_by => x_last_updated_by,
590 x_last_update_login => x_last_update_login
591 );
592
593 UPDATE igs_fi_refund_setup
594 SET
595 start_date = new_references.start_date,
596 end_date = new_references.end_date,
597 amount_high = new_references.amount_high,
598 amount_low = new_references.amount_low,
599 last_update_date = x_last_update_date,
600 last_updated_by = x_last_updated_by,
601 last_update_login = x_last_update_login
602 WHERE rowid = x_rowid;
603
604 IF (SQL%NOTFOUND) THEN
605 RAISE NO_DATA_FOUND;
606 END IF;
607
608 END update_row;
609
610
611 PROCEDURE add_row (
612 x_rowid IN OUT NOCOPY VARCHAR2,
613 x_refund_setup_id IN OUT NOCOPY NUMBER,
614 x_start_date IN DATE,
615 x_end_date IN DATE,
616 x_amount_high IN NUMBER,
617 x_amount_low IN NUMBER,
618 x_mode IN VARCHAR2 DEFAULT 'R'
619 ) AS
620 /*
621 || Created By : [email protected]
622 || Created On : 26-FEB-2002
623 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
624 || Known limitations, enhancements or remarks :
625 || Change History :
626 || Who When What
627 || (reverse chronological order - newest change first)
628 */
629 CURSOR c1 IS
630 SELECT rowid
631 FROM igs_fi_refund_setup
632 WHERE refund_setup_id = x_refund_setup_id;
633
634 BEGIN
635
636 OPEN c1;
637 FETCH c1 INTO x_rowid;
638 IF (c1%NOTFOUND) THEN
639 CLOSE c1;
640
641 insert_row (
642 x_rowid,
643 x_refund_setup_id,
644 x_start_date,
645 x_end_date,
646 x_amount_high,
647 x_amount_low,
648 x_mode
649 );
650 RETURN;
651 END IF;
652 CLOSE c1;
653
654 update_row (
655 x_rowid,
656 x_refund_setup_id,
657 x_start_date,
658 x_end_date,
659 x_amount_high,
660 x_amount_low,
661 x_mode
662 );
663
664 END add_row;
665
666
667 PROCEDURE delete_row (
668 x_rowid IN VARCHAR2
669 ) AS
670 /*
671 || Created By : [email protected]
672 || Created On : 26-FEB-2002
673 || Purpose : Handles the DELETE DML logic for the table.
674 || Known limitations, enhancements or remarks :
675 || Change History :
676 || Who When What
677 || (reverse chronological order - newest change first)
678 */
679 BEGIN
680
681 before_dml (
682 p_action => 'DELETE',
683 x_rowid => x_rowid
684 );
685
686 DELETE FROM igs_fi_refund_setup
687 WHERE rowid = x_rowid;
688
689 IF (SQL%NOTFOUND) THEN
690 RAISE NO_DATA_FOUND;
691 END IF;
692
693 END delete_row;
694
695
696 END igs_fi_refund_setup_pkg;
[Home] [Help]