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.GMD_PARAMETERS_PKG

Source


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