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