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_PRE2KPOC_PKG

Source


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