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_TR_TSTP_GRP_LMT_PKG

Source


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