DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_AS_ALIAS_TYPES_PKG

Source


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