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

Source


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