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_FI_ELM_RNG_ORDS_PKG

Source


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