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

Source


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