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_IMP_IAC_CONTROLS_PKG

Source


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