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_AS_DOC_DLVY_TYP_PKG

Source


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