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_IAC_PROJECTIONS_PKG

Source


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