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.IGS_AD_SS_APPL_COMPS_PKG

Source


1 PACKAGE BODY igs_ad_ss_appl_comps_pkg AS
2 /* $Header: IGSAIH7B.pls 120.1 2005/09/08 14:43:27 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_ad_ss_appl_comps%ROWTYPE;
6   new_references igs_ad_ss_appl_comps%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_admission_application_type        IN     VARCHAR2,
12     x_page_name                         IN     VARCHAR2,
13     x_component_code                         IN     VARCHAR2,
14     x_comp_disp_name                    IN     VARCHAR2,
15     x_include_flag                      IN     VARCHAR2,
16     x_required_flag                     IN     VARCHAR2,
17     x_creation_date                     IN     DATE,
18     x_created_by                        IN     NUMBER,
19     x_last_update_date                  IN     DATE,
20     x_last_updated_by                   IN     NUMBER,
21     x_last_update_login                 IN     NUMBER
22   ) AS
23   /*
24   ||  Created By :
25   ||  Created On : 07-JUL-2005
26   ||  Purpose : Initialises the Old and New references for the columns of the table.
27   ||  Known limitations, enhancements or remarks :
28   ||  Change History :
29   ||  Who             When            What
30   ||  (reverse chronological order - newest change first)
31   */
32 
33     CURSOR cur_old_ref_values IS
34       SELECT   *
35       FROM     igs_ad_ss_appl_comps
36       WHERE    rowid = x_rowid;
37 
38   BEGIN
39 
40     l_rowid := x_rowid;
41 
42     -- Code for setting the Old and New Reference Values.
43     -- Populate Old Values.
44     OPEN cur_old_ref_values;
45     FETCH cur_old_ref_values INTO old_references;
46     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47       CLOSE cur_old_ref_values;
48       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49       igs_ge_msg_stack.add;
50       app_exception.raise_exception;
51       RETURN;
52     END IF;
53     CLOSE cur_old_ref_values;
54 
55     -- Populate New Values.
56     new_references.admission_application_type        := x_admission_application_type;
57     new_references.page_name                         := x_page_name;
58     new_references.component_code                    := x_component_code;
59     new_references.comp_disp_name                    := NVL(x_comp_disp_name,old_references.comp_disp_name);
60     new_references.include_flag                      := x_include_flag;
61     new_references.required_flag                     := x_required_flag;
62 
63     IF (p_action = 'UPDATE') THEN
64       new_references.creation_date                   := old_references.creation_date;
65       new_references.created_by                      := old_references.created_by;
66     ELSE
67       new_references.creation_date                   := x_creation_date;
68       new_references.created_by                      := x_created_by;
69     END IF;
70 
71     new_references.last_update_date                  := x_last_update_date;
72     new_references.last_updated_by                   := x_last_updated_by;
73     new_references.last_update_login                 := x_last_update_login;
74 
75   END set_column_values;
76 
77 
78   PROCEDURE check_parent_existance AS
79   /*
80   ||  Created By :
81   ||  Created On : 07-JUL-2005
82   ||  Purpose : Checks for the existance of Parent records.
83   ||  Known limitations, enhancements or remarks :
84   ||  Change History :
85   ||  Who             When            What
86   ||  (reverse chronological order - newest change first)
87   */
88   BEGIN
89 
90     IF (((old_references.admission_application_type = new_references.admission_application_type) AND
91          (old_references.page_name = new_references.page_name)) OR
92         ((new_references.admission_application_type IS NULL) OR
93          (new_references.page_name IS NULL))) THEN
94       NULL;
95     ELSIF NOT igs_ad_ss_appl_pgs_pkg.get_pk_for_validation (
96                 new_references.page_name,
97                 new_references.admission_application_type
98               ) THEN
99       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
100       igs_ge_msg_stack.add;
101       app_exception.raise_exception;
102     END IF;
103 
104   END check_parent_existance;
105 
106 
107   FUNCTION get_pk_for_validation (
108     x_admission_application_type        IN     VARCHAR2,
109     x_page_name                         IN     VARCHAR2,
110     x_component_code                         IN     VARCHAR2
111   ) RETURN BOOLEAN AS
112   /*
113   ||  Created By :
114   ||  Created On : 07-JUL-2005
115   ||  Purpose : Validates the Primary Key of the table.
116   ||  Known limitations, enhancements or remarks :
117   ||  Change History :
118   ||  Who             When            What
119   ||  (reverse chronological order - newest change first)
120   */
121     CURSOR cur_rowid IS
122       SELECT   rowid
123       FROM     igs_ad_ss_appl_comps
124       WHERE    admission_application_type = x_admission_application_type
125       AND      page_name = x_page_name
126       AND      component_code = x_component_code
127       FOR UPDATE NOWAIT;
128 
129     lv_rowid cur_rowid%RowType;
130 
131   BEGIN
132 
133     OPEN cur_rowid;
134     FETCH cur_rowid INTO lv_rowid;
135     IF (cur_rowid%FOUND) THEN
136       CLOSE cur_rowid;
137       RETURN(TRUE);
138     ELSE
139       CLOSE cur_rowid;
140       RETURN(FALSE);
141     END IF;
142 
143   END get_pk_for_validation;
144 
145 
146   PROCEDURE get_fk_igs_ad_ss_appl_pgs (
147     x_admission_application_type        IN     VARCHAR2,
148     x_page_name                         IN     VARCHAR2
149   ) AS
150   /*
151   ||  Created By :
152   ||  Created On : 07-JUL-2005
153   ||  Purpose : Validates the Foreign Keys for the table.
154   ||  Known limitations, enhancements or remarks :
155   ||  Change History :
156   ||  Who             When            What
157   ||  (reverse chronological order - newest change first)
158   */
159     CURSOR cur_rowid IS
160       SELECT   rowid
161       FROM     igs_ad_ss_appl_comps
162       WHERE   ((admission_application_type = x_admission_application_type) AND
163                (page_name = x_page_name));
164 
165     lv_rowid cur_rowid%RowType;
166 
167   BEGIN
168 
169     OPEN cur_rowid;
170     FETCH cur_rowid INTO lv_rowid;
171     IF (cur_rowid%FOUND) THEN
172       CLOSE cur_rowid;
173       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
174       igs_ge_msg_stack.add;
175       app_exception.raise_exception;
176       RETURN;
177     END IF;
178     CLOSE cur_rowid;
179 
180   END get_fk_igs_ad_ss_appl_pgs;
181 
182 
183   PROCEDURE before_dml (
184     p_action                            IN     VARCHAR2,
185     x_rowid                             IN     VARCHAR2,
186     x_admission_application_type        IN     VARCHAR2,
187     x_page_name                         IN     VARCHAR2,
188     x_component_code                    IN     VARCHAR2,
189     x_comp_disp_name                    IN     VARCHAR2,
190     x_include_flag                      IN     VARCHAR2,
191     x_required_flag                     IN     VARCHAR2,
192     x_creation_date                     IN     DATE,
193     x_created_by                        IN     NUMBER,
194     x_last_update_date                  IN     DATE,
195     x_last_updated_by                   IN     NUMBER,
196     x_last_update_login                 IN     NUMBER
197   ) AS
198   /*
199   ||  Created By :
200   ||  Created On : 07-JUL-2005
201   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
202   ||            Trigger Handlers for the table, before any DML operation.
203   ||  Known limitations, enhancements or remarks :
204   ||  Change History :
205   ||  Who             When            What
206   ||  (reverse chronological order - newest change first)
207   */
208   BEGIN
209 
210     set_column_values (
211       p_action,
212       x_rowid,
213       x_admission_application_type,
214       x_page_name,
215       x_component_code,
216       x_comp_disp_name,
217       x_include_flag,
218       x_required_flag,
219       x_creation_date,
220       x_created_by,
221       x_last_update_date,
222       x_last_updated_by,
223       x_last_update_login
224     );
225 
226     IF (p_action = 'INSERT') THEN
227       -- Call all the procedures related to Before Insert.
228       IF ( get_pk_for_validation(
229              new_references.admission_application_type,
230              new_references.page_name,
231              new_references.component_code
232            )
233          ) THEN
234         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
235         igs_ge_msg_stack.add;
236         app_exception.raise_exception;
237       END IF;
238       check_parent_existance;
239     ELSIF (p_action = 'UPDATE') THEN
240       -- Call all the procedures related to Before Update.
241       check_parent_existance;
242     ELSIF (p_action = 'VALIDATE_INSERT') THEN
243       -- Call all the procedures related to Before Insert.
244       IF ( get_pk_for_validation (
245              new_references.admission_application_type,
246              new_references.page_name,
247              new_references.component_code
248            )
249          ) THEN
250         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
251         igs_ge_msg_stack.add;
252         app_exception.raise_exception;
253       END IF;
254     END IF;
255 
256   END before_dml;
257 
258 
259   PROCEDURE insert_row (
260     x_rowid                             IN OUT NOCOPY VARCHAR2,
261     x_admission_application_type        IN OUT NOCOPY VARCHAR2,
262     x_page_name                         IN     VARCHAR2,
263     x_component_code                    IN     VARCHAR2,
264     x_comp_disp_name                    IN     VARCHAR2,
265     x_include_flag                      IN     VARCHAR2,
266     x_required_flag                     IN     VARCHAR2,
267     x_mode                              IN     VARCHAR2
268   ) AS
269   /*
270   ||  Created By :
271   ||  Created On : 07-JUL-2005
272   ||  Purpose : Handles the INSERT DML logic for the table.
273   ||  Known limitations, enhancements or remarks :
274   ||  Change History :
275   ||  Who             When            What
276   ||  (reverse chronological order - newest change first)
277   */
278 
279     x_last_update_date           DATE;
280     x_last_updated_by            NUMBER;
281     x_last_update_login          NUMBER;
282 
283   BEGIN
284 
285     x_last_update_date := SYSDATE;
286     IF (x_mode = 'I') THEN
287       x_last_updated_by := 1;
288       x_last_update_login := 0;
289     ELSIF (x_mode = 'R') THEN
290       x_last_updated_by := fnd_global.user_id;
291       IF (x_last_updated_by IS NULL) THEN
292         x_last_updated_by := -1;
293       END IF;
294       x_last_update_login := fnd_global.login_id;
295       IF (x_last_update_login IS NULL) THEN
296         x_last_update_login := -1;
297       END IF;
298     ELSE
299       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
300       fnd_message.set_token ('ROUTINE', 'igs_ad_ss_appl_comps_PKG.INSERT_ROW');
301       igs_ge_msg_stack.add;
302       app_exception.raise_exception;
303     END IF;
304 
305     before_dml(
306       p_action                            => 'INSERT',
307       x_rowid                             => x_rowid,
308       x_admission_application_type        => x_admission_application_type,
309       x_page_name                         => x_page_name,
310       x_component_code                    => x_component_code,
311       x_comp_disp_name                    => x_comp_disp_name,
312       x_include_flag                      => x_include_flag,
313       x_required_flag                     => x_required_flag,
314       x_creation_date                     => x_last_update_date,
315       x_created_by                        => x_last_updated_by,
316       x_last_update_date                  => x_last_update_date,
317       x_last_updated_by                   => x_last_updated_by,
318       x_last_update_login                 => x_last_update_login
319     );
320 
321     INSERT INTO igs_ad_ss_appl_comps (
322       admission_application_type,
323       page_name,
324       component_code,
325       comp_disp_name,
326       include_flag,
327       required_flag,
328       creation_date,
329       created_by,
330       last_update_date,
331       last_updated_by,
332       last_update_login
333     ) VALUES (
334       new_references.admission_application_type,
335       new_references.page_name,
336       new_references.component_code,
337       new_references.comp_disp_name,
338       new_references.include_flag,
339       new_references.required_flag,
340       x_last_update_date,
341       x_last_updated_by,
342       x_last_update_date,
343       x_last_updated_by,
344       x_last_update_login
345     ) RETURNING ROWID, admission_application_type INTO x_rowid, x_admission_application_type;
346 
347     IF (x_rowid IS NULL) THEN
348        RAISE NO_DATA_FOUND;
349     END IF;
350 
351   END insert_row;
352 
353 
354   PROCEDURE lock_row (
355     x_rowid                             IN     VARCHAR2,
356     x_admission_application_type        IN     VARCHAR2,
357     x_page_name                         IN     VARCHAR2,
358     x_component_code                    IN     VARCHAR2,
359     x_comp_disp_name                    IN     VARCHAR2,
360     x_include_flag                      IN     VARCHAR2,
361     x_required_flag                     IN     VARCHAR2
362   ) AS
363   /*
364   ||  Created By :
365   ||  Created On : 07-JUL-2005
366   ||  Purpose : Handles the LOCK mechanism for the table.
367   ||  Known limitations, enhancements or remarks :
368   ||  Change History :
369   ||  Who             When            What
370   ||  (reverse chronological order - newest change first)
371   */
372     CURSOR c1 IS
373       SELECT
374         comp_disp_name,
375         include_flag,
376         required_flag
377       FROM  igs_ad_ss_appl_comps
378       WHERE rowid = x_rowid
379       FOR UPDATE NOWAIT;
380 
381     tlinfo c1%ROWTYPE;
382 
383   BEGIN
384 
385     OPEN c1;
386     FETCH c1 INTO tlinfo;
387     IF (c1%notfound) THEN
388       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
389       igs_ge_msg_stack.add;
390       CLOSE c1;
391       app_exception.raise_exception;
392       RETURN;
393     END IF;
394     CLOSE c1;
395 
396     IF (
397         (tlinfo.comp_disp_name = x_comp_disp_name)
398         AND (tlinfo.include_flag = x_include_flag)
399         AND (tlinfo.required_flag = x_required_flag)
400        ) THEN
401       NULL;
402     ELSE
403       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
404       igs_ge_msg_stack.add;
405       app_exception.raise_exception;
406     END IF;
407 
408     RETURN;
409 
410   END lock_row;
411 
412 
413   PROCEDURE update_row (
414     x_rowid                             IN     VARCHAR2,
415     x_admission_application_type        IN     VARCHAR2,
416     x_page_name                         IN     VARCHAR2,
417     x_component_code                         IN     VARCHAR2,
418     x_comp_disp_name                    IN     VARCHAR2,
419     x_include_flag                      IN     VARCHAR2,
420     x_required_flag                     IN     VARCHAR2,
421     x_mode                              IN     VARCHAR2
422   ) AS
423   /*
424   ||  Created By :
425   ||  Created On : 07-JUL-2005
426   ||  Purpose : Handles the UPDATE DML logic for the table.
427   ||  Known limitations, enhancements or remarks :
428   ||  Change History :
429   ||  Who             When            What
430   ||  (reverse chronological order - newest change first)
431   */
432     x_last_update_date           DATE ;
433     x_last_updated_by            NUMBER;
434     x_last_update_login          NUMBER;
435 
436   BEGIN
437 
438     x_last_update_date := SYSDATE;
439     IF (X_MODE = 'I') THEN
440       x_last_updated_by := 1;
441       x_last_update_login := 0;
442     ELSIF (x_mode = 'R') THEN
443       x_last_updated_by := fnd_global.user_id;
444       IF x_last_updated_by IS NULL THEN
445         x_last_updated_by := -1;
446       END IF;
447       x_last_update_login := fnd_global.login_id;
448       IF (x_last_update_login IS NULL) THEN
449         x_last_update_login := -1;
450       END IF;
451     ELSE
452       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
453       fnd_message.set_token ('ROUTINE', 'igs_ad_ss_appl_comps_PKG.UPDATE_ROW');
454       igs_ge_msg_stack.add;
455       app_exception.raise_exception;
456     END IF;
457 
458     before_dml(
459       p_action                            => 'UPDATE',
460       x_rowid                             => x_rowid,
461       x_admission_application_type        => x_admission_application_type,
462       x_page_name                         => x_page_name,
463       x_component_code                         => x_component_code,
464       x_comp_disp_name                    => x_comp_disp_name,
465       x_include_flag                      => x_include_flag,
466       x_required_flag                     => x_required_flag,
467       x_creation_date                     => x_last_update_date,
468       x_created_by                        => x_last_updated_by,
469       x_last_update_date                  => x_last_update_date,
470       x_last_updated_by                   => x_last_updated_by,
471       x_last_update_login                 => x_last_update_login
472     );
473 
474     UPDATE igs_ad_ss_appl_comps
475       SET
476         comp_disp_name                    = new_references.comp_disp_name,
477         include_flag                      = new_references.include_flag,
478         required_flag                     = new_references.required_flag,
479         last_update_date                  = x_last_update_date,
480         last_updated_by                   = x_last_updated_by,
481         last_update_login                 = x_last_update_login
482       WHERE rowid = x_rowid;
483 
484     IF (SQL%NOTFOUND) THEN
485       RAISE NO_DATA_FOUND;
486     END IF;
487 
488   END update_row;
489 
490 
491   PROCEDURE add_row (
492     x_rowid                             IN OUT NOCOPY VARCHAR2,
493     x_admission_application_type        IN OUT NOCOPY VARCHAR2,
494     x_page_name                         IN     VARCHAR2,
495     x_component_code                    IN     VARCHAR2,
496     x_comp_disp_name                    IN     VARCHAR2,
497     x_include_flag                      IN     VARCHAR2,
498     x_required_flag                     IN     VARCHAR2,
499     x_mode                              IN     VARCHAR2
500   ) AS
501   /*
502   ||  Created By :
503   ||  Created On : 07-JUL-2005
504   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
505   ||  Known limitations, enhancements or remarks :
506   ||  Change History :
507   ||  Who             When            What
508   ||  (reverse chronological order - newest change first)
509   */
510     CURSOR c1 IS
511       SELECT   rowid
512       FROM     igs_ad_ss_appl_comps
513       WHERE    admission_application_type        = x_admission_application_type
514       AND      page_name                         = x_page_name
515       AND      component_code                    = x_component_code;
516 
517   BEGIN
518 
519     OPEN c1;
520     FETCH c1 INTO x_rowid;
521     IF (c1%NOTFOUND) THEN
522       CLOSE c1;
523 
524       insert_row (
525         x_rowid,
526         x_admission_application_type,
527         x_page_name,
528         x_component_code,
529         x_comp_disp_name,
530         x_include_flag,
531         x_required_flag,
532         x_mode
533       );
534       RETURN;
535     END IF;
536     CLOSE c1;
537 
538     update_row (
539       x_rowid,
540       x_admission_application_type,
541       x_page_name,
542       x_component_code,
543       x_comp_disp_name,
544       x_include_flag,
545       x_required_flag,
546       x_mode
547     );
548 
549   END add_row;
550 
551 
552   PROCEDURE delete_row (
553     x_rowid IN VARCHAR2
554   ) AS
555   /*
556   ||  Created By :
557   ||  Created On : 07-JUL-2005
558   ||  Purpose : Handles the DELETE DML logic for the table.
559   ||  Known limitations, enhancements or remarks :
560   ||  Change History :
561   ||  Who             When            What
562   ||  (reverse chronological order - newest change first)
563   */
564   BEGIN
565 
566     before_dml (
567       p_action => 'DELETE',
568       x_rowid => x_rowid
569     );
570 
571     DELETE FROM igs_ad_ss_appl_comps
572     WHERE rowid = x_rowid;
573 
574     IF (SQL%NOTFOUND) THEN
575       RAISE NO_DATA_FOUND;
576     END IF;
577 
578   END delete_row;
579 
580 
581 END igs_ad_ss_appl_comps_pkg;