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