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_APPRV_PROFILES_PKG

Source


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