eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGF_AP_APPL_SETUP_PKG

Source


1 PACKAGE BODY igf_ap_appl_setup_pkg AS
2 /* $Header: IGFAI01B.pls 120.1 2005/08/09 07:42:50 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_ap_appl_setup_all%ROWTYPE;
6   new_references igf_ap_appl_setup_all%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_question_id                       IN     NUMBER      DEFAULT NULL,
12     x_question                          IN     VARCHAR2    DEFAULT NULL,
13     x_enabled                           IN     VARCHAR2    DEFAULT NULL,
14     x_org_id                            IN     NUMBER      DEFAULT NULL,
15     x_ci_cal_type                       IN     VARCHAR2    DEFAULT NULL,
16     x_ci_sequence_number                IN     NUMBER      DEFAULT NULL,
17     x_creation_date                     IN     DATE        DEFAULT NULL,
18     x_created_by                        IN     NUMBER      DEFAULT NULL,
19     x_last_update_date                  IN     DATE        DEFAULT NULL,
20     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
21     x_last_update_login                 IN     NUMBER      DEFAULT NULL,
22     x_application_code                  IN     VARCHAR2,
23     x_application_name                  IN     VARCHAR2,
24     x_active_flag                       IN     VARCHAR2	   DEFAULT NULL,
25     x_answer_type_code                  IN     VARCHAR2,
26     x_destination_txt                   IN     VARCHAR2    DEFAULT NULL,
27     x_ld_cal_type                       IN     VARCHAR2    DEFAULT NULL,
28     x_ld_sequence_number                IN     NUMBER      DEFAULT NULL,
29     x_all_terms_flag                    IN     VARCHAR2    DEFAULT NULL,
30     x_override_exist_ant_data_flag      IN     VARCHAR2    DEFAULT NULL,
31     x_required_flag                     IN     VARCHAR2    DEFAULT NULL,
32     x_minimum_value_num                 IN     NUMBER      DEFAULT NULL,
33     x_maximum_value_num                 IN     NUMBER      DEFAULT NULL,
34     x_minimum_date                      IN     DATE        DEFAULT NULL,
35     x_maximium_date                     IN     DATE        DEFAULT NULL,
36     x_lookup_code                       IN     VARCHAR2    DEFAULT NULL,
37     x_hint_txt                          IN     VARCHAR2    DEFAULT NULL
38   ) AS
39   /*
40   ||  Created By : brajendr
41   ||  Created On : 07-DEC-2000
42   ||  Purpose : Initialises the Old and New references for the columns of the table.
43   ||  Known limitations, enhancements or remarks :
44   ||  Change History :
45   ||  Who             When            What
46   ||  (reverse chronological order - newest change first)
47   */
48 
49     CURSOR cur_old_ref_values IS
50       SELECT   *
51       FROM     IGF_AP_APPL_SETUP_ALL
52       WHERE    rowid = x_rowid;
53 
54   BEGIN
55 
56     l_rowid := x_rowid;
57 
58     -- Code for setting the Old and New Reference Values.
59     -- Populate Old Values.
60     OPEN cur_old_ref_values;
61     FETCH cur_old_ref_values INTO old_references;
62     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
63       CLOSE cur_old_ref_values;
64       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
65       igs_ge_msg_stack.add;
66       app_exception.raise_exception;
67       RETURN;
68     END IF;
69     CLOSE cur_old_ref_values;
70 
71     -- Populate New Values.
72     new_references.question_id                       := x_question_id;
73     new_references.question                          := x_question;
74     new_references.enabled                           := x_enabled;
75     new_references.org_id                            := x_org_id;
76     new_references.ci_cal_type                       := x_ci_cal_type;
77     new_references.ci_sequence_number                := x_ci_sequence_number;
78     new_references.application_code                  := x_application_code;
79     new_references.application_name                  := x_application_name;
80     new_references.active_flag                       := x_active_flag;
81     new_references.answer_type_code                  := x_answer_type_code;
82     new_references.destination_txt                   := x_destination_txt;
83     new_references.ld_cal_type                       := x_ld_cal_type;
84     new_references.ld_sequence_number                := x_ld_sequence_number;
85     new_references.all_terms_flag                    := x_all_terms_flag;
86     new_references.override_exist_ant_data_flag      := x_override_exist_ant_data_flag;
87     new_references.required_flag                     := x_required_flag;
88     new_references.minimum_value_num                 := x_minimum_value_num;
89     new_references.maximum_value_num                 := x_maximum_value_num;
90     new_references.minimum_date                      := x_minimum_date;
91     new_references.maximium_date                     := x_maximium_date;
92     new_references.lookup_code                       := x_lookup_code;
93     new_references.hint_txt                          := x_hint_txt;
94 
95 
96     IF (p_action = 'UPDATE') THEN
97       new_references.creation_date                   := old_references.creation_date;
98       new_references.created_by                      := old_references.created_by;
99     ELSE
100       new_references.creation_date                   := x_creation_date;
101       new_references.created_by                      := x_created_by;
102     END IF;
103 
104     new_references.last_update_date                  := x_last_update_date;
105     new_references.last_updated_by                   := x_last_updated_by;
106     new_references.last_update_login                 := x_last_update_login;
107 
108   END set_column_values;
109 
110 
111   PROCEDURE check_uniqueness AS
112   /*
113   ||  Created By : rasingh
114   ||  Created On : 04-JAN-2001
115   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
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 ( get_uk_for_validation (
124            new_references.ci_cal_type,
125            new_references.ci_sequence_number,
126            new_references.application_code,
127            new_references.question
128          )
129        ) THEN
130       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
131       igs_ge_msg_stack.add;
132       app_exception.raise_exception;
133     END IF;
134 
135   END check_uniqueness;
136 
137 
138   PROCEDURE check_parent_existance AS
139   /*
140   ||  Created By : brajendr
141   ||  Created On : 07-DEC-2000
142   ||  Purpose : Checks for the existance of Parent records.
143   ||  Known limitations, enhancements or remarks :
144   ||  Change History :
145   ||  Who             When            What
146   ||  (reverse chronological order - newest change first)
147   */
148   BEGIN
149 
150     IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
151          (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
152         ((new_references.ci_cal_type IS NULL) OR
153          (new_references.ci_sequence_number IS NULL))) THEN
154       NULL;
155     ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
156                 new_references.ci_cal_type,
157                 new_references.ci_sequence_number
158               ) THEN
159       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
160       igs_ge_msg_stack.add;
161       app_exception.raise_exception;
162     END IF;
163 
164   END check_parent_existance;
165 
166 
167   PROCEDURE check_child_existance IS
168   /*
169   ||  Created By : brajendr
170   ||  Created On : 07-DEC-2000
171   ||  Purpose : Checks for the existance of Child records.
172   ||  Known limitations, enhancements or remarks :
173   ||  Change History :
174   ||  Who             When            What
175   ||  (reverse chronological order - newest change first)
176   */
177   BEGIN
178 
179     igf_ap_st_inst_appl_pkg.get_fk_igf_ap_appl_setup (
180       old_references.question_id
181     );
182 
183   END check_child_existance;
184 
185 
186   FUNCTION get_pk_for_validation (
187     x_question_id                       IN     NUMBER
188   ) RETURN BOOLEAN AS
189   /*
190   ||  Created By : brajendr
191   ||  Created On : 07-DEC-2000
192   ||  Purpose : Validates the Primary Key of the table.
193   ||  Known limitations, enhancements or remarks :
194   ||  Change History :
195   ||  Who             When            What
196   ||  (reverse chronological order - newest change first)
197   */
198 
199 
200     CURSOR cur_rowid IS
201       SELECT   rowid
202       FROM     igf_ap_appl_setup_all
203       WHERE    question_id = x_question_id
204       FOR UPDATE NOWAIT;
205 
206     lv_rowid cur_rowid%RowType;
207 
208   BEGIN
209 
210     OPEN cur_rowid;
211     FETCH cur_rowid INTO lv_rowid;
212     IF (cur_rowid%FOUND) THEN
213       CLOSE cur_rowid;
214       RETURN(TRUE);
215     ELSE
216       CLOSE cur_rowid;
217       RETURN(FALSE);
218     END IF;
219 
220   END get_pk_for_validation;
221 
222 
223   PROCEDURE get_fk_igs_ca_inst (
224     x_cal_type                          IN     VARCHAR2,
225     x_sequence_number                   IN     NUMBER
226   ) AS
227   /*
228   ||  Created By : brajendr
229   ||  Created On : 07-DEC-2000
230   ||  Purpose : Validates the Foreign Keys for the table.
231   ||  Known limitations, enhancements or remarks :
232   ||  Change History :
233   ||  Who             When            What
234   ||  (reverse chronological order - newest change first)
235   */
236     CURSOR cur_rowid IS
237       SELECT   rowid
238       FROM     igf_ap_appl_setup_all
239       WHERE   ((ci_cal_type = x_cal_type) AND
240                (ci_sequence_number = x_sequence_number));
241 
242     lv_rowid cur_rowid%RowType;
243 
244   BEGIN
245 
246     OPEN cur_rowid;
247     FETCH cur_rowid INTO lv_rowid;
248     IF (cur_rowid%FOUND) THEN
249       CLOSE cur_rowid;
250       fnd_message.set_name ('IGF', 'IGF_AP_IAS_CI_FK');
251       igs_ge_msg_stack.add;
252       app_exception.raise_exception;
253       RETURN;
254     END IF;
255     CLOSE cur_rowid;
256 
257   END get_fk_igs_ca_inst;
258 
259 
260   FUNCTION get_uk_for_validation (
261     x_ci_cal_type                       IN     VARCHAR2,
262     x_ci_sequence_number                IN     NUMBER,
263     x_application_code                  IN     VARCHAR2,
264     x_question                          IN     VARCHAR2
265   ) RETURN BOOLEAN AS
266   /*
267   ||  Created By : rasingh
268   ||  Created On : 04-JAN-2001
269   ||  Purpose : Validates the Unique Keys of the table.
270   ||  Known limitations, enhancements or remarks :
271   ||  Change History :
272   ||  Who             When            What
273   ||  (reverse chronological order - newest change first)
274   */
275 
276 
277     CURSOR cur_rowid IS
278       SELECT   rowid
279       FROM     igf_ap_appl_setup_all
280       WHERE    ci_cal_type = x_ci_cal_type
281       AND      ci_sequence_number = x_ci_sequence_number
282       AND      application_code = x_application_code
283       AND      question = x_question
284       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
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         RETURN (true);
295         ELSE
296        CLOSE cur_rowid;
297       RETURN(FALSE);
298     END IF;
299 
300   END get_uk_for_validation ;
301 
302 
303   PROCEDURE before_dml (
304     p_action                            IN     VARCHAR2,
305     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
306     x_question_id                       IN     NUMBER      DEFAULT NULL,
307     x_question                          IN     VARCHAR2    DEFAULT NULL,
308     x_enabled                           IN     VARCHAR2    DEFAULT NULL,
309     x_org_id                            IN     NUMBER      DEFAULT NULL,
310     x_ci_cal_type                       IN     VARCHAR2    DEFAULT NULL,
311     x_ci_sequence_number                IN     NUMBER      DEFAULT NULL,
312     x_creation_date                     IN     DATE        DEFAULT NULL,
313     x_created_by                        IN     NUMBER      DEFAULT NULL,
314     x_last_update_date                  IN     DATE        DEFAULT NULL,
315     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
316     x_last_update_login                 IN     NUMBER      DEFAULT NULL,
317     x_application_code                  IN     VARCHAR2,
318     x_application_name                  IN     VARCHAR2,
319     x_active_flag                       IN     VARCHAR2,
320     x_answer_type_code                  IN     VARCHAR2,
321     x_destination_txt                   IN     VARCHAR2,
322     x_ld_cal_type                       IN     VARCHAR2,
323     x_ld_sequence_number                IN     NUMBER,
324     x_all_terms_flag                    IN     VARCHAR2,
325     x_override_exist_ant_data_flag      IN     VARCHAR2,
326     x_required_flag                     IN     VARCHAR2,
327     x_minimum_value_num                 IN     NUMBER,
328     x_maximum_value_num                 IN     NUMBER,
329     x_minimum_date                      IN     DATE,
330     x_maximium_date                     IN     DATE,
331     x_lookup_code                       IN     VARCHAR2,
332     x_hint_txt                          IN     VARCHAR2
333   ) AS
334   /*
335   ||  Created By : brajendr
336   ||  Created On : 07-DEC-2000
337   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
338   ||            Trigger Handlers for the table, before any DML operation.
339   ||  Known limitations, enhancements or remarks :
340   ||  Change History :
341   ||  Who             When            What
342   ||  (reverse chronological order - newest change first)
343   */
344   BEGIN
345 
346     set_column_values (
347       p_action,
348       x_rowid,
349       x_question_id,
350       x_question,
351       x_enabled,
352       x_org_id,
353       x_ci_cal_type,
354       x_ci_sequence_number,
355       x_creation_date,
356       x_created_by,
357       x_last_update_date,
358       x_last_updated_by,
359       x_last_update_login,
360       x_application_code,
361       x_application_name,
362       x_active_flag,
363       x_answer_type_code,
364       x_destination_txt,
365       x_ld_cal_type,
366       x_ld_sequence_number,
367       x_all_terms_flag,
368       x_override_exist_ant_data_flag,
369       x_required_flag,
370       x_minimum_value_num,
371       x_maximum_value_num,
375       x_hint_txt
372       x_minimum_date,
373       x_maximium_date,
374       x_lookup_code,
376     );
377 
378     IF (p_action = 'INSERT') THEN
379       -- Call all the procedures related to Before Insert.
380       IF ( get_pk_for_validation(
381              new_references.question_id
382            )
383          ) THEN
384         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
385         igs_ge_msg_stack.add;
386         app_exception.raise_exception;
387       END IF;
388       check_parent_existance;
389       check_uniqueness;
390     ELSIF (p_action = 'UPDATE') THEN
391       -- Call all the procedures related to Before Update.
392       check_parent_existance;
393       check_uniqueness;
394     ELSIF (p_action = 'DELETE') THEN
395       -- Call all the procedures related to Before Delete.
396       check_child_existance;
397     ELSIF (p_action = 'VALIDATE_INSERT') THEN
398       -- Call all the procedures related to Before Insert.
399       IF ( get_pk_for_validation (
400              new_references.question_id
401            )
402          ) THEN
403         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
404         igs_ge_msg_stack.add;
405         app_exception.raise_exception;
406       END IF;
407       check_uniqueness;
408     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
409       check_uniqueness;
410     ELSIF (p_action = 'VALIDATE_DELETE') THEN
411       check_child_existance;
412     END IF;
413 
414   END before_dml;
415 
416 
417   PROCEDURE insert_row (
418     x_rowid                             IN OUT NOCOPY VARCHAR2,
419     x_question_id                       IN OUT NOCOPY NUMBER,
420     x_question                          IN     VARCHAR2,
421     x_enabled                           IN     VARCHAR2,
422     x_org_id                            IN     NUMBER,
423     x_ci_cal_type                       IN     VARCHAR2,
424     x_ci_sequence_number                IN     NUMBER,
425     x_mode                              IN     VARCHAR2 DEFAULT 'R',
426     x_application_code                  IN     VARCHAR2,
427     x_application_name                  IN     VARCHAR2,
428     x_active_flag                       IN     VARCHAR2,
429     x_answer_type_code                  IN     VARCHAR2,
430     x_destination_txt                   IN     VARCHAR2,
431     x_ld_cal_type                       IN     VARCHAR2,
432     x_ld_sequence_number                IN     NUMBER,
433     x_all_terms_flag                    IN     VARCHAR2,
434     x_override_exist_ant_data_flag      IN     VARCHAR2,
435     x_required_flag                     IN     VARCHAR2,
436     x_minimum_value_num                 IN     NUMBER,
437     x_maximum_value_num                 IN     NUMBER,
438     x_minimum_date                      IN     DATE,
439     x_maximium_date                     IN     DATE,
440     x_lookup_code                       IN     VARCHAR2,
441     x_hint_txt                          IN     VARCHAR2
442   ) AS
443   /*
444   ||  Created By : brajendr
445   ||  Created On : 07-DEC-2000
446   ||  Purpose : Handles the INSERT DML logic for the table.
447   ||  Known limitations, enhancements or remarks :
448   ||  Change History :
449   ||  Who             When            What
450   || vvutukur       16-feb-2002      removed l_org_id portion and passed igf_aw_gen.get_org_id to before_dml.
451   ||  Who             When            What
452   ||  (reverse chronological order - newest change first)
453   */
454     CURSOR c IS
455       SELECT   rowid
456       FROM     igf_ap_appl_setup_all
457       WHERE    question_id                       = x_question_id;
458 
459     x_last_update_date           DATE;
460     x_last_updated_by            NUMBER;
461     x_last_update_login          NUMBER;
462 
463   BEGIN
464 
465     SELECT igf_ap_appl_setup_s.nextval
466 	INTO x_question_id
467 	FROM dual;
468 
469     x_last_update_date := SYSDATE;
470     IF (x_mode = 'I') THEN
471       x_last_updated_by := 1;
472       x_last_update_login := 0;
473     ELSIF (x_mode = 'R') THEN
474       x_last_updated_by := fnd_global.user_id;
475       IF (x_last_updated_by IS NULL) THEN
476         x_last_updated_by := -1;
477       END IF;
478       x_last_update_login := fnd_global.login_id;
479       IF (x_last_update_login IS NULL) THEN
480         x_last_update_login := -1;
481       END IF;
482     ELSE
483       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
484       igs_ge_msg_stack.add;
485       app_exception.raise_exception;
486     END IF;
487 
488     before_dml(
489       p_action                            => 'INSERT',
490       x_rowid                             => x_rowid,
491       x_question_id                       => x_question_id,
492       x_question                          => x_question,
493       x_enabled                           => NVL (x_enabled,'Y' ),
494       x_org_id                            => igf_aw_gen.get_org_id,
495       x_ci_cal_type                       => x_ci_cal_type,
496       x_ci_sequence_number                => x_ci_sequence_number,
497       x_creation_date                     => x_last_update_date,
498       x_created_by                        => x_last_updated_by,
502       x_application_code                  => x_application_code,
499       x_last_update_date                  => x_last_update_date,
500       x_last_updated_by                   => x_last_updated_by,
501       x_last_update_login                 => x_last_update_login,
503       x_application_name                  => x_application_name,
504       x_active_flag                       => x_active_flag,
505       x_answer_type_code                  => x_answer_type_code,
506       x_destination_txt                   => x_destination_txt,
507       x_ld_cal_type                       => x_ld_cal_type,
508       x_ld_sequence_number                => x_ld_sequence_number,
509       x_all_terms_flag                    => x_all_terms_flag,
510       x_override_exist_ant_data_flag      => x_override_exist_ant_data_flag,
511       x_required_flag                     => x_required_flag,
512       x_minimum_value_num                 => x_minimum_value_num,
513       x_maximum_value_num                 => x_maximum_value_num,
514       x_minimum_date                      => x_minimum_date,
515       x_maximium_date                     => x_maximium_date,
516       x_lookup_code                       => x_lookup_code,
517       x_hint_txt                          => x_hint_txt
518     );
519 
520 
521     INSERT INTO igf_ap_appl_setup_all (
522       question_id,
523       question,
524       enabled,
525       org_id,
526       ci_cal_type,
527       ci_sequence_number,
528       creation_date,
529       created_by,
530       last_update_date,
531       last_updated_by,
532       last_update_login,
533       application_code,
534       application_name,
535       active_flag,
536       answer_type_code,
537       destination_txt,
538       ld_cal_type,
539       ld_sequence_number,
540       all_terms_flag,
541       override_exist_ant_data_flag,
542       required_flag,
543       minimum_value_num,
544       maximum_value_num,
545       minimum_date,
546       maximium_date,
547       lookup_code,
548       hint_txt
549     ) VALUES (
550       new_references.question_id,
551       new_references.question,
552       new_references.enabled,
553       new_references.org_id,
554       new_references.ci_cal_type,
555       new_references.ci_sequence_number,
556       x_last_update_date,
557       x_last_updated_by,
558       x_last_update_date,
559       x_last_updated_by,
560       x_last_update_login,
561       new_references.application_code,
562       new_references.application_name,
563       new_references.active_flag,
564       new_references.answer_type_code,
565       new_references.destination_txt,
566       new_references.ld_cal_type,
567       new_references.ld_sequence_number,
568       new_references.all_terms_flag,
569       new_references.override_exist_ant_data_flag,
570       new_references.required_flag,
571       new_references.minimum_value_num,
572       new_references.maximum_value_num,
573       new_references.minimum_date,
574       new_references.maximium_date,
575       new_references.lookup_code,
576       new_references.hint_txt
577     );
578 
579     OPEN c;
580     FETCH c INTO x_rowid;
581     IF (c%NOTFOUND) THEN
582       CLOSE c;
583       RAISE NO_DATA_FOUND;
584     END IF;
585     CLOSE c;
586 
587   END insert_row;
588 
589 
590   PROCEDURE lock_row (
591     x_rowid                             IN     VARCHAR2,
592     x_question_id                       IN     NUMBER,
593     x_question                          IN     VARCHAR2,
594     x_enabled                           IN     VARCHAR2,
595     x_org_id                            IN     NUMBER,
596     x_ci_cal_type                       IN     VARCHAR2,
597     x_ci_sequence_number                IN     NUMBER,
598     x_application_code                  IN     VARCHAR2,
599     x_application_name                  IN     VARCHAR2,
600     x_active_flag                       IN     VARCHAR2,
601     x_answer_type_code                  IN     VARCHAR2,
602     x_destination_txt                   IN     VARCHAR2,
603     x_ld_cal_type                       IN     VARCHAR2,
604     x_ld_sequence_number                IN     NUMBER,
605     x_all_terms_flag                    IN     VARCHAR2,
606     x_override_exist_ant_data_flag      IN     VARCHAR2,
607     x_required_flag                     IN     VARCHAR2,
608     x_minimum_value_num                 IN     NUMBER,
609     x_maximum_value_num                 IN     NUMBER,
610     x_minimum_date                      IN     DATE,
611     x_maximium_date                     IN     DATE,
612     x_lookup_code                       IN     VARCHAR2,
613     x_hint_txt                          IN     VARCHAR2
614   ) AS
615   /*
616   ||  Created By : brajendr
617   ||  Created On : 07-DEC-2000
618   ||  Purpose : Handles the LOCK mechanism for the table.
619   ||  Known limitations, enhancements or remarks :
620   ||  Change History :
621   ||  Who             When            What
622   || vvutukur       16-feb-2002      removed tlinfo check in IF condition for bug:2222272(SWSCR006-MO)
623   ||  (reverse chronological order - newest change first)
624   */
625     CURSOR c1 IS
629         org_id,
626       SELECT
627         question,
628         enabled,
630         ci_cal_type,
631         ci_sequence_number,
632         application_code,
633         application_name,
634         active_flag,
635         answer_type_code,
636         destination_txt,
637         ld_cal_type,
638         ld_sequence_number,
639         all_terms_flag,
640         override_exist_ant_data_flag,
641         required_flag,
642         minimum_value_num,
643         maximum_value_num,
644         minimum_date,
645         maximium_date,
646         lookup_code,
647         hint_txt
648       FROM  igf_ap_appl_setup_all
649       WHERE rowid = x_rowid
650       FOR UPDATE NOWAIT;
651 
652     tlinfo c1%ROWTYPE;
653 
654 
655   BEGIN
656 
657     OPEN c1;
658     FETCH c1 INTO tlinfo;
659     IF (c1%notfound) THEN
660       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
661       igs_ge_msg_stack.add;
662       CLOSE c1;
663       app_exception.raise_exception;
664       RETURN;
665     END IF;
666     CLOSE c1;
667 
668     IF (
669         (tlinfo.question = x_question)
670         AND (tlinfo.enabled = x_enabled)
671         AND (tlinfo.ci_cal_type = x_ci_cal_type)
672         AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
673         AND (tlinfo.application_code = x_application_code)
674         AND (tlinfo.application_name = x_application_name)
675         AND ((tlinfo.active_flag = x_active_flag) OR ((tlinfo.active_flag IS NULL) AND (X_active_flag IS NULL)))
676         AND ((tlinfo.answer_type_code = x_answer_type_code) OR ((tlinfo.answer_type_code IS NULL) AND (X_answer_type_code IS NULL)))
677         AND ((tlinfo.destination_txt = x_destination_txt) OR ((tlinfo.destination_txt IS NULL) AND (X_destination_txt IS NULL)))
678         AND ((tlinfo.ld_cal_type = x_ld_cal_type) OR ((tlinfo.ld_cal_type IS NULL) AND (X_ld_cal_type IS NULL)))
679         AND ((tlinfo.ld_sequence_number = x_ld_sequence_number) OR ((tlinfo.ld_sequence_number IS NULL) AND (X_ld_sequence_number IS NULL)))
680         AND ((tlinfo.all_terms_flag = x_all_terms_flag) OR ((tlinfo.all_terms_flag IS NULL) AND (X_all_terms_flag IS NULL)))
681         AND ((tlinfo.override_exist_ant_data_flag = x_override_exist_ant_data_flag) OR ((tlinfo.override_exist_ant_data_flag IS NULL) AND (X_override_exist_ant_data_flag IS NULL)))
682         AND ((tlinfo.required_flag = x_required_flag) OR ((tlinfo.required_flag IS NULL) AND (X_required_flag IS NULL)))
683         AND ((tlinfo.minimum_value_num = x_minimum_value_num) OR ((tlinfo.minimum_value_num IS NULL) AND (X_minimum_value_num IS NULL)))
684         AND ((tlinfo.maximum_value_num = x_maximum_value_num) OR ((tlinfo.maximum_value_num IS NULL) AND (X_maximum_value_num IS NULL)))
685         AND ((tlinfo.minimum_date = x_minimum_date) OR ((tlinfo.minimum_date IS NULL) AND (X_minimum_date IS NULL)))
686         AND ((tlinfo.maximium_date = x_maximium_date) OR ((tlinfo.maximium_date IS NULL) AND (X_maximium_date IS NULL)))
687         AND ((tlinfo.lookup_code = x_lookup_code) OR ((tlinfo.lookup_code IS NULL) AND (X_lookup_code IS NULL)))
688         AND ((tlinfo.hint_txt = x_hint_txt) OR ((tlinfo.hint_txt IS NULL) AND (X_hint_txt IS NULL)))
689        ) THEN
690       NULL;
691     ELSE
692       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
693       igs_ge_msg_stack.add;
694       app_exception.raise_exception;
695     END IF;
696 
697     RETURN;
698 
699   END lock_row;
700 
701 
702   PROCEDURE update_row (
703     x_rowid                             IN     VARCHAR2,
704     x_question_id                       IN     NUMBER,
705     x_question                          IN     VARCHAR2,
706     x_enabled                           IN     VARCHAR2,
707     x_org_id                            IN     NUMBER,
708     x_ci_cal_type                       IN     VARCHAR2,
709     x_ci_sequence_number                IN     NUMBER,
710     x_mode                              IN     VARCHAR2 DEFAULT 'R',
711     x_application_code                  IN     VARCHAR2,
712     x_application_name                  IN     VARCHAR2,
713     x_active_flag                       IN     VARCHAR2,
714     x_answer_type_code                  IN     VARCHAR2,
715     x_destination_txt                   IN     VARCHAR2,
716     x_ld_cal_type                       IN     VARCHAR2,
717     x_ld_sequence_number                IN     NUMBER,
718     x_all_terms_flag                    IN     VARCHAR2,
719     x_override_exist_ant_data_flag      IN     VARCHAR2,
720     x_required_flag                     IN     VARCHAR2,
721     x_minimum_value_num                 IN     NUMBER,
722     x_maximum_value_num                 IN     NUMBER,
723     x_minimum_date                      IN     DATE,
724     x_maximium_date                     IN     DATE,
725     x_lookup_code                       IN     VARCHAR2,
726     x_hint_txt                          IN     VARCHAR2
727   ) AS
728   /*
729   ||  Created By : brajendr
730   ||  Created On : 07-DEC-2000
731   ||  Purpose : Handles the UPDATE DML logic for the table.
732   ||  Known limitations, enhancements or remarks :
733   ||  Change History :
734   ||  Who             When            What
735   || vvutukur        16-feb-2002     passed igf_aw_gen.get_org_id in call to up before_dml call.bug:2222272.
736   ||  (reverse chronological order - newest change first)
737   */
738     x_last_update_date           DATE ;
739     x_last_updated_by            NUMBER;
740     x_last_update_login          NUMBER;
741 
742 
743   BEGIN
744 
745     x_last_update_date := SYSDATE;
746     IF (X_MODE = 'I') THEN
747       x_last_updated_by := 1;
748       x_last_update_login := 0;
749     ELSIF (x_mode = 'R') THEN
750       x_last_updated_by := fnd_global.user_id;
751       IF x_last_updated_by IS NULL THEN
752         x_last_updated_by := -1;
753       END IF;
754       x_last_update_login := fnd_global.login_id;
755       IF (x_last_update_login IS NULL) THEN
756         x_last_update_login := -1;
757       END IF;
758     ELSE
759       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
760       igs_ge_msg_stack.add;
761       app_exception.raise_exception;
762     END IF;
763 
764     before_dml(
765       p_action                            => 'UPDATE',
766       x_rowid                             => x_rowid,
767       x_question_id                       => x_question_id,
768       x_question                          => x_question,
769       x_enabled                           => NVL (x_enabled,'Y' ),
770       x_org_id                            => igf_aw_gen.get_org_id,
771       x_ci_cal_type                       => x_ci_cal_type,
772       x_ci_sequence_number                => x_ci_sequence_number,
773       x_creation_date                     => x_last_update_date,
774       x_created_by                        => x_last_updated_by,
775       x_last_update_date                  => x_last_update_date,
776       x_last_updated_by                   => x_last_updated_by,
777       x_last_update_login                 => x_last_update_login,
778       x_application_code                  => x_application_code,
779       x_application_name                  => x_application_name,
780       x_active_flag                       => x_active_flag,
781       x_answer_type_code                  => x_answer_type_code,
782       x_destination_txt                   => x_destination_txt,
783       x_ld_cal_type                       => x_ld_cal_type,
784       x_ld_sequence_number                => x_ld_sequence_number,
785       x_all_terms_flag                    => x_all_terms_flag,
786       x_override_exist_ant_data_flag      => x_override_exist_ant_data_flag,
787       x_required_flag                     => x_required_flag,
788       x_minimum_value_num                 => x_minimum_value_num,
789       x_maximum_value_num                 => x_maximum_value_num,
790       x_minimum_date                      => x_minimum_date,
791       x_maximium_date                     => x_maximium_date,
792       x_lookup_code                       => x_lookup_code,
793       x_hint_txt                          => x_hint_txt
794     );
795 
796     UPDATE igf_ap_appl_setup_all
797       SET
798         question                          = new_references.question,
799         enabled                           = new_references.enabled,
800         ci_cal_type                       = new_references.ci_cal_type,
801         ci_sequence_number                = new_references.ci_sequence_number,
802         last_update_date                  = x_last_update_date,
803         last_updated_by                   = x_last_updated_by,
804         last_update_login                 = x_last_update_login,
805         application_code                  = new_references.application_code,
806         application_name                  = new_references.application_name,
807         active_flag                       = new_references.active_flag,
808         answer_type_code                  = new_references.answer_type_code,
809         destination_txt                   = new_references.destination_txt,
810         ld_cal_type                       = new_references.ld_cal_type,
814         required_flag                     = new_references.required_flag,
811         ld_sequence_number                = new_references.ld_sequence_number,
812         all_terms_flag                    = new_references.all_terms_flag,
813         override_exist_ant_data_flag      = new_references.override_exist_ant_data_flag,
815         minimum_value_num                 = new_references.minimum_value_num,
816         maximum_value_num                 = new_references.maximum_value_num,
817         minimum_date                      = new_references.minimum_date,
818         maximium_date                     = new_references.maximium_date,
819         lookup_code                       = new_references.lookup_code,
820         hint_txt                          = new_references.hint_txt
821       WHERE rowid = x_rowid;
822 
823     IF (SQL%NOTFOUND) THEN
824       RAISE NO_DATA_FOUND;
825     END IF;
826 
827   END update_row;
828 
829 
830   PROCEDURE add_row (
831     x_rowid                             IN OUT NOCOPY VARCHAR2,
832     x_question_id                       IN OUT NOCOPY NUMBER,
833     x_question                          IN     VARCHAR2,
834     x_enabled                           IN     VARCHAR2,
835     x_org_id                            IN     NUMBER,
836     x_ci_cal_type                       IN     VARCHAR2,
837     x_ci_sequence_number                IN     NUMBER,
838     x_mode                              IN     VARCHAR2 DEFAULT 'R',
839     x_application_code                  IN     VARCHAR2,
840     x_application_name                  IN     VARCHAR2,
841     x_active_flag                       IN     VARCHAR2,
842     x_answer_type_code                  IN     VARCHAR2,
843     x_destination_txt                   IN     VARCHAR2,
844     x_ld_cal_type                       IN     VARCHAR2,
845     x_ld_sequence_number                IN     NUMBER,
846     x_all_terms_flag                    IN     VARCHAR2,
847     x_override_exist_ant_data_flag      IN     VARCHAR2,
848     x_required_flag                     IN     VARCHAR2,
849     x_minimum_value_num                 IN     NUMBER,
850     x_maximum_value_num                 IN     NUMBER,
851     x_minimum_date                      IN     DATE,
852     x_maximium_date                     IN     DATE,
853     x_lookup_code                       IN     VARCHAR2,
854     x_hint_txt                          IN     VARCHAR2
855   ) AS
856   /*
857   ||  Created By : brajendr
858   ||  Created On : 07-DEC-2000
859   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
860   ||  Known limitations, enhancements or remarks :
861   ||  Change History :
862   ||  Who             When            What
863   ||  (reverse chronological order - newest change first)
864   */
865     CURSOR c1 IS
866       SELECT   rowid
867       FROM     igf_ap_appl_setup_all
868       WHERE    question_id                       = x_question_id;
869 
870   BEGIN
871 
872     OPEN c1;
873     FETCH c1 INTO x_rowid;
874     IF (c1%NOTFOUND) THEN
875       CLOSE c1;
876 
877       insert_row (
878         x_rowid,
879         x_question_id,
880         x_question,
881         x_enabled,
882         x_org_id,
883         x_ci_cal_type,
884         x_ci_sequence_number,
885         x_mode,
886         x_application_code,
887         x_application_name,
888         x_active_flag,
889         x_answer_type_code,
890         x_destination_txt,
891         x_ld_cal_type,
892         x_ld_sequence_number,
893         x_all_terms_flag,
894         x_override_exist_ant_data_flag,
895         x_required_flag,
896         x_minimum_value_num,
897         x_maximum_value_num,
898         x_minimum_date,
899         x_maximium_date,
900         x_lookup_code,
901         x_hint_txt
902       );
903       RETURN;
904     END IF;
905     CLOSE c1;
906 
907     update_row (
908       x_rowid,
909       x_question_id,
910       x_question,
911       x_enabled,
912       x_org_id,
913       x_ci_cal_type,
914       x_ci_sequence_number,
915       x_mode,
916       x_application_code,
917       x_application_name,
918       x_active_flag,
919       x_answer_type_code,
920       x_destination_txt,
921       x_ld_cal_type,
922       x_ld_sequence_number,
923       x_all_terms_flag,
924       x_override_exist_ant_data_flag,
925       x_required_flag,
926       x_minimum_value_num,
927       x_maximum_value_num,
928       x_minimum_date,
929       x_maximium_date,
930       x_lookup_code,
931       x_hint_txt
932     );
933 
934   END add_row;
935 
936 
937   PROCEDURE delete_row (
938     x_rowid IN VARCHAR2
939   ) AS
940   /*
941   ||  Created By : brajendr
942   ||  Created On : 07-DEC-2000
943   ||  Purpose : Handles the DELETE DML logic for the table.
944   ||  Known limitations, enhancements or remarks :
945   ||  Change History :
946   ||  Who             When            What
947   ||  (reverse chronological order - newest change first)
948   */
949   BEGIN
950 
951     before_dml (
952       p_action => 'DELETE',
953       x_rowid => x_rowid
954     );
955 
956     DELETE FROM igf_ap_appl_setup_all
957     WHERE rowid = x_rowid;
958 
959     IF (SQL%NOTFOUND) THEN
960       RAISE NO_DATA_FOUND;
961     END IF;
962 
963   END delete_row;
964 
965 
966 END igf_ap_appl_setup_pkg;