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_POS_ACTIONS_PKG

Source


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