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

Source


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