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_PE_HZ_PARTY_REL_PKG

Source


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