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_AS_DOC_PURPOSES_PKG

Source


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