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_UC_REF_SUBJ_PKG

Source


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