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