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_CO_TYPE_PKG

Source


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