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_EN_NOM_CMPL_PRD_PKG

Source


1 PACKAGE BODY igs_en_nom_cmpl_prd_pkg AS
2 /* $Header: IGSEI61B.pls 115.7 2003/02/20 08:43:09 prraj noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_en_nom_cmpl_prd%ROWTYPE;
6   new_references igs_en_nom_cmpl_prd%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_completion_perd                   IN     VARCHAR2    DEFAULT NULL,
12     x_description                       IN     VARCHAR2    DEFAULT NULL,
13     x_closed_ind                        IN     VARCHAR2    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 : [email protected]
22   ||  Created On : 19-DEC-2001
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_EN_NOM_CMPL_PRD
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.completion_perd                   := x_completion_perd;
54     new_references.description                       := x_description;
55     new_references.closed_ind                        := x_closed_ind;
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_completion_perd                   IN     VARCHAR2
74   ) RETURN BOOLEAN AS
75   /*
76   ||  Created By : [email protected]
77   ||  Created On : 19-DEC-2001
78   ||  Purpose : Validates the Primary Key of the table.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84     CURSOR cur_rowid IS
85       SELECT   rowid
86       FROM     igs_en_nom_cmpl_prd
87       WHERE    completion_perd = x_completion_perd;
88 
89     lv_rowid cur_rowid%RowType;
90 
91   BEGIN
92 
93     OPEN cur_rowid;
94     FETCH cur_rowid INTO lv_rowid;
95     IF (cur_rowid%FOUND) THEN
96       CLOSE cur_rowid;
97       RETURN(TRUE);
98     ELSE
99       CLOSE cur_rowid;
100       RETURN(FALSE);
101     END IF;
102 
103   END get_pk_for_validation;
104 
105 
106 
107   PROCEDURE before_dml (
108     p_action                            IN     VARCHAR2,
109     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
110     x_completion_perd                   IN     VARCHAR2    DEFAULT NULL,
111     x_description                       IN     VARCHAR2    DEFAULT NULL,
112     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
113     x_creation_date                     IN     DATE        DEFAULT NULL,
114     x_created_by                        IN     NUMBER      DEFAULT NULL,
115     x_last_update_date                  IN     DATE        DEFAULT NULL,
116     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
117     x_last_update_login                 IN     NUMBER      DEFAULT NULL
118   ) AS
119   /*
120   ||  Created By : [email protected]
121   ||  Created On : 19-DEC-2001
122   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
123   ||            Trigger Handlers for the table, before any DML operation.
124   ||  Known limitations, enhancements or remarks :
125   ||  Change History :
126   ||  Who             When            What
127   ||  (reverse chronological order - newest change first)
128   ||smaddali 22-feb-2002 added calls to check_child_existance procedure
129   || for delete and validate_delete actions bug#2237194 ARCR043 ccr build
130   */
131   BEGIN
132 
133     set_column_values (
134       p_action,
135       x_rowid,
136       x_completion_perd,
137       x_description,
138       x_closed_ind,
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.completion_perd
150            )
151          ) THEN
152         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
153         igs_ge_msg_stack.add;
154         app_exception.raise_exception;
155       END IF;
156 
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.completion_perd
161            )
162          ) THEN
163         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
164         igs_ge_msg_stack.add;
165         app_exception.raise_exception;
166       END IF;
167 
168     END IF;
169 
170   END before_dml;
171 
172 
173   PROCEDURE insert_row (
174     x_rowid                             IN OUT NOCOPY VARCHAR2,
175     x_completion_perd                   IN     VARCHAR2,
176     x_description                       IN     VARCHAR2,
177     x_closed_ind                        IN     VARCHAR2,
178     x_mode                              IN     VARCHAR2 DEFAULT 'R'
179   ) AS
180   /*
181   ||  Created By : [email protected]
182   ||  Created On : 19-DEC-2001
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_en_nom_cmpl_prd
192       WHERE    completion_perd                   = x_completion_perd;
193 
194     x_last_update_date           DATE;
195     x_last_updated_by            NUMBER;
196     x_last_update_login          NUMBER;
197 
198   BEGIN
199 
200     x_last_update_date := SYSDATE;
201     IF (x_mode = 'I') THEN
202       x_last_updated_by := 1;
203       x_last_update_login := 0;
204     ELSIF (x_mode = 'R') THEN
205       x_last_updated_by := fnd_global.user_id;
206       IF (x_last_updated_by IS NULL) THEN
207         x_last_updated_by := -1;
208       END IF;
209       x_last_update_login := fnd_global.login_id;
210       IF (x_last_update_login IS NULL) THEN
211         x_last_update_login := -1;
212       END IF;
213     ELSE
214       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
215       igs_ge_msg_stack.add;
216       app_exception.raise_exception;
217     END IF;
218 
219     before_dml(
220       p_action                            => 'INSERT',
221       x_rowid                             => x_rowid,
222       x_completion_perd                   => x_completion_perd,
223       x_description                       => x_description,
224       x_closed_ind                        => x_closed_ind,
225       x_creation_date                     => x_last_update_date,
226       x_created_by                        => x_last_updated_by,
227       x_last_update_date                  => x_last_update_date,
228       x_last_updated_by                   => x_last_updated_by,
229       x_last_update_login                 => x_last_update_login
230     );
231 
232     INSERT INTO igs_en_nom_cmpl_prd (
233       completion_perd,
234       description,
235       closed_ind,
236       creation_date,
237       created_by,
238       last_update_date,
239       last_updated_by,
240       last_update_login
241     ) VALUES (
242       new_references.completion_perd,
243       new_references.description,
244       new_references.closed_ind,
245       x_last_update_date,
246       x_last_updated_by,
247       x_last_update_date,
248       x_last_updated_by,
249       x_last_update_login
250     );
251 
252     OPEN c;
253     FETCH c INTO x_rowid;
254     IF (c%NOTFOUND) THEN
255       CLOSE c;
256       RAISE NO_DATA_FOUND;
257     END IF;
258     CLOSE c;
259 
260   END insert_row;
261 
262 
263   PROCEDURE lock_row (
264     x_rowid                             IN     VARCHAR2,
265     x_completion_perd                   IN     VARCHAR2,
266     x_description                       IN     VARCHAR2,
267     x_closed_ind                        IN     VARCHAR2
268   ) AS
269   /*
270   ||  Created By : [email protected]
271   ||  Created On : 19-DEC-2001
272   ||  Purpose : Handles the LOCK mechanism for the table.
273   ||  Known limitations, enhancements or remarks :
274   ||  Change History :
275   ||  Who             When            What
276   ||  (reverse chronological order - newest change first)
277   */
278     CURSOR c1 IS
279       SELECT
280         description,
281         closed_ind
282       FROM  igs_en_nom_cmpl_prd
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.description = x_description)
303         AND (tlinfo.closed_ind = x_closed_ind)
304        ) THEN
305       NULL;
306     ELSE
307       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
308       igs_ge_msg_stack.add;
309       app_exception.raise_exception;
310     END IF;
311 
312     RETURN;
313 
314   END lock_row;
315 
316 
317   PROCEDURE update_row (
318     x_rowid                             IN     VARCHAR2,
319     x_completion_perd                   IN     VARCHAR2,
320     x_description                       IN     VARCHAR2,
321     x_closed_ind                        IN     VARCHAR2,
322     x_mode                              IN     VARCHAR2 DEFAULT 'R'
323   ) AS
324   /*
325   ||  Created By : [email protected]
326   ||  Created On : 19-DEC-2001
327   ||  Purpose : Handles the UPDATE DML logic for the table.
328   ||  Known limitations, enhancements or remarks :
329   ||  Change History :
330   ||  Who             When            What
331   ||  (reverse chronological order - newest change first)
332   */
333     x_last_update_date           DATE ;
334     x_last_updated_by            NUMBER;
335     x_last_update_login          NUMBER;
336 
337   BEGIN
338 
339     x_last_update_date := SYSDATE;
340     IF (X_MODE = 'I') THEN
341       x_last_updated_by := 1;
342       x_last_update_login := 0;
343     ELSIF (x_mode = 'R') THEN
344       x_last_updated_by := fnd_global.user_id;
345       IF x_last_updated_by IS NULL THEN
346         x_last_updated_by := -1;
347       END IF;
348       x_last_update_login := fnd_global.login_id;
349       IF (x_last_update_login IS NULL) THEN
350         x_last_update_login := -1;
351       END IF;
352     ELSE
353       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
354       igs_ge_msg_stack.add;
355       app_exception.raise_exception;
356     END IF;
357 
358     before_dml(
359       p_action                            => 'UPDATE',
360       x_rowid                             => x_rowid,
361       x_completion_perd                   => x_completion_perd,
362       x_description                       => x_description,
363       x_closed_ind                        => x_closed_ind,
364       x_creation_date                     => x_last_update_date,
365       x_created_by                        => x_last_updated_by,
366       x_last_update_date                  => x_last_update_date,
367       x_last_updated_by                   => x_last_updated_by,
368       x_last_update_login                 => x_last_update_login
369     );
370 
371     UPDATE igs_en_nom_cmpl_prd
372       SET
373         description                       = new_references.description,
374         closed_ind                        = new_references.closed_ind,
375         last_update_date                  = x_last_update_date,
376         last_updated_by                   = x_last_updated_by,
377         last_update_login                 = x_last_update_login
378       WHERE rowid = x_rowid;
379 
380     IF (SQL%NOTFOUND) THEN
381       RAISE NO_DATA_FOUND;
382     END IF;
383 
384   END update_row;
385 
386 
387   PROCEDURE add_row (
388     x_rowid                             IN OUT NOCOPY VARCHAR2,
389     x_completion_perd                   IN     VARCHAR2,
390     x_description                       IN     VARCHAR2,
391     x_closed_ind                        IN     VARCHAR2,
392     x_mode                              IN     VARCHAR2 DEFAULT 'R'
393   ) AS
394   /*
395   ||  Created By : [email protected]
396   ||  Created On : 19-DEC-2001
397   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
398   ||  Known limitations, enhancements or remarks :
399   ||  Change History :
400   ||  Who             When            What
401   ||  (reverse chronological order - newest change first)
402   */
403     CURSOR c1 IS
404       SELECT   rowid
405       FROM     igs_en_nom_cmpl_prd
406       WHERE    completion_perd                   = x_completion_perd;
407 
408   BEGIN
409 
410     OPEN c1;
411     FETCH c1 INTO x_rowid;
412     IF (c1%NOTFOUND) THEN
413       CLOSE c1;
414 
415       insert_row (
416         x_rowid,
417         x_completion_perd,
418         x_description,
419         x_closed_ind,
420         x_mode
421       );
422       RETURN;
423     END IF;
424     CLOSE c1;
425 
426     update_row (
427       x_rowid,
428       x_completion_perd,
429       x_description,
430       x_closed_ind,
431       x_mode
432     );
433 
434   END add_row;
435 
436 
437 END igs_en_nom_cmpl_prd_pkg;