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_DEPD_ACTIVE_PKG

Source


1 PACKAGE BODY igs_pe_depd_active_pkg AS
2 /* $Header: IGSNI42B.pls 120.1 2005/06/28 05:20:33 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_pe_depd_active%ROWTYPE;
6   new_references igs_pe_depd_active%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_relationship_id                   IN     NUMBER,
12     x_action_code                       IN     VARCHAR2,
13     x_effective_date                    IN     DATE,
14     x_reason_code                       IN     VARCHAR2,
15     x_remarks                           IN     VARCHAR2,
16     x_creation_date                     IN     DATE,
17     x_created_by                        IN     NUMBER,
18     x_last_update_date                  IN     DATE,
19     x_last_updated_by                   IN     NUMBER,
20     x_last_update_login                 IN     NUMBER ,
21     x_other_reason_remarks              IN     VARCHAR2
22   ) AS
23   /*
24   ||  Created By : [email protected]
25   ||  Created On : 28-NOV-2002
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     igs_pe_depd_active
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.relationship_id                   := x_relationship_id;
57     new_references.action_code                       := x_action_code;
58     new_references.effective_date                    := trunc(x_effective_date);
59     new_references.reason_code                       := x_reason_code;
60     new_references.remarks                           := x_remarks;
61     new_references.other_reason_remarks              := x_other_reason_remarks;
62 
63     IF (p_action = 'UPDATE') THEN
64       new_references.creation_date                   := old_references.creation_date;
65       new_references.created_by                      := old_references.created_by;
66     ELSE
67       new_references.creation_date                   := x_creation_date;
68       new_references.created_by                      := x_created_by;
69     END IF;
70 
71     new_references.last_update_date                  := x_last_update_date;
72     new_references.last_updated_by                   := x_last_updated_by;
73     new_references.last_update_login                 := x_last_update_login;
74 
75   END set_column_values;
76 
77 
78   FUNCTION get_pk_for_validation (
79     x_relationship_id                   IN     NUMBER,
80     x_action_code                       IN     VARCHAR2,
81     x_effective_date                    IN     DATE
82   ) RETURN BOOLEAN AS
83   /*
84   ||  Created By : [email protected]
85   ||  Created On : 28-NOV-2002
86   ||  Purpose : Validates the Primary Key of the table.
87   ||  Known limitations, enhancements or remarks :
88   ||  Change History :
89   ||  Who             When            What
90   ||  (reverse chronological order - newest change first)
91   */
92     CURSOR cur_rowid IS
93       SELECT   rowid
94       FROM     igs_pe_depd_active
95       WHERE    relationship_id = x_relationship_id
96       AND      action_code = x_action_code
97       AND      effective_date = x_effective_date
98       FOR UPDATE NOWAIT;
99 
100     lv_rowid cur_rowid%RowType;
101 
102   BEGIN
103 
104     OPEN cur_rowid;
105     FETCH cur_rowid INTO lv_rowid;
106     IF (cur_rowid%FOUND) THEN
107       CLOSE cur_rowid;
108       RETURN(TRUE);
109     ELSE
110       CLOSE cur_rowid;
111       RETURN(FALSE);
112     END IF;
113 
114   END get_pk_for_validation;
115 
116 
117   PROCEDURE before_dml (
118     p_action                            IN     VARCHAR2,
119     x_rowid                             IN     VARCHAR2,
120     x_relationship_id                   IN     NUMBER,
121     x_action_code                       IN     VARCHAR2,
122     x_effective_date                    IN     DATE,
123     x_reason_code                       IN     VARCHAR2,
124     x_remarks                           IN     VARCHAR2,
125     x_creation_date                     IN     DATE,
126     x_created_by                        IN     NUMBER,
127     x_last_update_date                  IN     DATE,
128     x_last_updated_by                   IN     NUMBER,
129     x_last_update_login                 IN     NUMBER ,
130     x_other_reason_remarks              IN     VARCHAR2
131   ) AS
132   /*
133   ||  Created By : [email protected]
134   ||  Created On : 28-NOV-2002
135   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
136   ||            Trigger Handlers for the table, before any DML operation.
137   ||  Known limitations, enhancements or remarks :
138   ||  Change History :
139   ||  Who             When            What
140   ||  (reverse chronological order - newest change first)
141   */
142   BEGIN
143 
144     set_column_values (
145       p_action,
146       x_rowid,
147       x_relationship_id,
148       x_action_code,
149       x_effective_date,
150       x_reason_code,
151       x_remarks,
152       x_creation_date,
153       x_created_by,
154       x_last_update_date,
155       x_last_updated_by,
156       x_last_update_login ,
157       x_other_reason_remarks
158     );
159 
160     IF (p_action = 'INSERT') THEN
161       -- Call all the procedures related to Before Insert.
162       IF ( get_pk_for_validation(
163              new_references.relationship_id,
164              new_references.action_code,
165              new_references.effective_date
166            )
167          ) THEN
168         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
169         igs_ge_msg_stack.add;
170         app_exception.raise_exception;
171       END IF;
172     ELSIF (p_action = 'VALIDATE_INSERT') THEN
173       -- Call all the procedures related to Before Insert.
174       IF ( get_pk_for_validation (
175              new_references.relationship_id,
176              new_references.action_code,
177              new_references.effective_date
178            )
179          ) THEN
180         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
181         igs_ge_msg_stack.add;
182         app_exception.raise_exception;
183       END IF;
184     END IF;
185 
186   END before_dml;
187 
188 
189   PROCEDURE insert_row (
190     x_rowid                             IN OUT NOCOPY VARCHAR2,
191     x_relationship_id                   IN     NUMBER,
192     x_action_code                       IN     VARCHAR2,
193     x_effective_date                    IN     DATE,
194     x_reason_code                       IN     VARCHAR2,
195     x_remarks                           IN     VARCHAR2,
196     x_mode                              IN     VARCHAR2 ,
197     x_other_reason_remarks              IN     VARCHAR2
198   ) AS
199   /*
200   ||  Created By : [email protected]
201   ||  Created On : 28-NOV-2002
202   ||  Purpose : Handles the INSERT DML logic for the table.
203   ||  Known limitations, enhancements or remarks :
204   ||  Change History :
205   ||  Who             When            What
206   ||  (reverse chronological order - newest change first)
207   */
208 
209     x_last_update_date           DATE;
210     x_last_updated_by            NUMBER;
211     x_last_update_login          NUMBER;
212 
213   BEGIN
214 
215     x_last_update_date := SYSDATE;
216     IF (x_mode = 'I') THEN
217       x_last_updated_by := 1;
218       x_last_update_login := 0;
219     ELSIF (X_MODE IN ('R', 'S')) THEN
220       x_last_updated_by := fnd_global.user_id;
221       IF (x_last_updated_by IS NULL) THEN
222         x_last_updated_by := -1;
223       END IF;
224       x_last_update_login := fnd_global.login_id;
225       IF (x_last_update_login IS NULL) THEN
226         x_last_update_login := -1;
227       END IF;
228     ELSE
229       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
230       igs_ge_msg_stack.add;
231       app_exception.raise_exception;
232     END IF;
233 
234     before_dml(
235       p_action                            => 'INSERT',
236       x_rowid                             => x_rowid,
237       x_relationship_id                   => x_relationship_id,
238       x_action_code                       => x_action_code,
239       x_effective_date                    => x_effective_date,
240       x_reason_code                       => x_reason_code,
241       x_remarks                           => x_remarks,
242       x_creation_date                     => x_last_update_date,
243       x_created_by                        => x_last_updated_by,
244       x_last_update_date                  => x_last_update_date,
245       x_last_updated_by                   => x_last_updated_by,
246       x_last_update_login                 => x_last_update_login ,
247       x_other_reason_remarks              => x_other_reason_remarks
248     );
249 
250      IF (x_mode = 'S') THEN
251     igs_sc_gen_001.set_ctx('R');
252   END IF;
253  INSERT INTO igs_pe_depd_active (
254       relationship_id,
255       action_code,
256       effective_date,
257       reason_code,
258       remarks,
259       creation_date,
260       created_by,
261       last_update_date,
262       last_updated_by,
263       last_update_login,
264       other_reason_remarks
265     ) VALUES (
266       new_references.relationship_id,
267       new_references.action_code,
268       new_references.effective_date,
269       new_references.reason_code,
270       new_references.remarks,
271       x_last_update_date,
272       x_last_updated_by,
273       x_last_update_date,
274       x_last_updated_by,
275       x_last_update_login ,
276       new_references.other_reason_remarks
277     ) RETURNING ROWID INTO x_rowid;
278  IF (x_mode = 'S') THEN
279     igs_sc_gen_001.unset_ctx('R');
280   END IF;
281 
282 
283 
284 EXCEPTION
285   WHEN OTHERS THEN
286     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
287       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
288       fnd_message.set_token ('ERR_CD', SQLCODE);
289       igs_ge_msg_stack.add;
290       igs_sc_gen_001.unset_ctx('R');
291       app_exception.raise_exception;
292     ELSE
293       igs_sc_gen_001.unset_ctx('R');
294       RAISE;
295     END IF;
296  END insert_row;
297 
298 
299   PROCEDURE lock_row (
300     x_rowid                             IN     VARCHAR2,
301     x_relationship_id                   IN     NUMBER,
302     x_action_code                       IN     VARCHAR2,
303     x_effective_date                    IN     DATE,
304     x_reason_code                       IN     VARCHAR2,
305     x_remarks                           IN     VARCHAR2,
306     x_other_reason_remarks              IN     VARCHAR2
307   ) AS
308   /*
309   ||  Created By : [email protected]
310   ||  Created On : 28-NOV-2002
311   ||  Purpose : Handles the LOCK mechanism for the table.
312   ||  Known limitations, enhancements or remarks :
313   ||  Change History :
314   ||  Who             When            What
315   ||  (reverse chronological order - newest change first)
316   */
317     CURSOR c1 IS
318       SELECT
319         reason_code,
320         remarks,
321         other_reason_remarks
322       FROM  igs_pe_depd_active
323       WHERE rowid = x_rowid
324       FOR UPDATE NOWAIT;
325 
326     tlinfo c1%ROWTYPE;
327 
328   BEGIN
329 
330     OPEN c1;
331     FETCH c1 INTO tlinfo;
332     IF (c1%notfound) THEN
333       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
334       igs_ge_msg_stack.add;
335       CLOSE c1;
336       app_exception.raise_exception;
337       RETURN;
338     END IF;
339     CLOSE c1;
340 
341     IF (
342         ((tlinfo.reason_code = x_reason_code) OR ((tlinfo.reason_code IS NULL) AND (X_reason_code IS NULL)))
343         AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
344         AND ((tlinfo.other_reason_remarks = x_other_reason_remarks) OR ((tlinfo.other_reason_remarks IS NULL) AND (x_other_reason_remarks IS NULL )))
345        ) THEN
346       NULL;
347     ELSE
348       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
349       igs_ge_msg_stack.add;
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_relationship_id                   IN     NUMBER,
361     x_action_code                       IN     VARCHAR2,
362     x_effective_date                    IN     DATE,
363     x_reason_code                       IN     VARCHAR2,
364     x_remarks                           IN     VARCHAR2,
365     x_mode                              IN     VARCHAR2 ,
366     x_other_reason_remarks              IN     VARCHAR2
367   ) AS
368   /*
369   ||  Created By : [email protected]
370   ||  Created On : 28-NOV-2002
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 IN ('R', 'S')) 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       igs_ge_msg_stack.add;
399       app_exception.raise_exception;
400     END IF;
401 
402     before_dml(
403       p_action                            => 'UPDATE',
404       x_rowid                             => x_rowid,
405       x_relationship_id                   => x_relationship_id,
406       x_action_code                       => x_action_code,
407       x_effective_date                    => x_effective_date,
408       x_reason_code                       => x_reason_code,
409       x_remarks                           => x_remarks,
410       x_creation_date                     => x_last_update_date,
411       x_created_by                        => x_last_updated_by,
412       x_last_update_date                  => x_last_update_date,
413       x_last_updated_by                   => x_last_updated_by,
414       x_last_update_login                 => x_last_update_login ,
415       x_other_reason_remarks              => x_other_reason_remarks
416     );
417 
418      IF (x_mode = 'S') THEN
419     igs_sc_gen_001.set_ctx('R');
420   END IF;
421  UPDATE igs_pe_depd_active
422       SET
423         reason_code                       = new_references.reason_code,
424         remarks                           = new_references.remarks,
425         last_update_date                  = x_last_update_date,
426         last_updated_by                   = x_last_updated_by,
427         last_update_login                 = x_last_update_login ,
428         other_reason_remarks              = x_other_reason_remarks
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_relationship_id                   IN     NUMBER,
461     x_action_code                       IN     VARCHAR2,
462     x_effective_date                    IN     DATE,
463     x_reason_code                       IN     VARCHAR2,
464     x_remarks                           IN     VARCHAR2,
465     x_mode                              IN     VARCHAR2 ,
466     x_other_reason_remarks              IN     VARCHAR2
467   ) AS
468   /*
469   ||  Created By : [email protected]
470   ||  Created On : 28-NOV-2002
471   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
472   ||  Known limitations, enhancements or remarks :
473   ||  Change History :
474   ||  Who             When            What
475   ||  (reverse chronological order - newest change first)
476   */
477     CURSOR c1 IS
478       SELECT   rowid
479       FROM     igs_pe_depd_active
480       WHERE    relationship_id                   = x_relationship_id
481       AND      action_code                       = x_action_code
482       AND      effective_date                    = x_effective_date;
483 
484   BEGIN
485 
486     OPEN c1;
487     FETCH c1 INTO x_rowid;
488     IF (c1%NOTFOUND) THEN
489       CLOSE c1;
490 
491       insert_row (
492         x_rowid,
493         x_relationship_id,
494         x_action_code,
495         x_effective_date,
496         x_reason_code,
497         x_remarks,
498         x_mode ,
499         x_other_reason_remarks
500       );
501       RETURN;
502     END IF;
503     CLOSE c1;
504 
505     update_row (
506       x_rowid,
507       x_relationship_id,
508       x_action_code,
509       x_effective_date,
510       x_reason_code,
511       x_remarks,
512       x_mode ,
513       x_other_reason_remarks
514     );
515 
516   END add_row;
517 
518 
519   PROCEDURE delete_row (
520     x_rowid IN VARCHAR2,
521   x_mode IN VARCHAR2
522   ) AS
523   /*
524   ||  Created By : [email protected]
525   ||  Created On : 28-NOV-2002
526   ||  Purpose : Handles the DELETE DML logic for the table.
527   ||  Known limitations, enhancements or remarks :
528   ||  Change History :
529   ||  Who             When            What
530   ||  (reverse chronological order - newest change first)
531   */
532   BEGIN
533 
534     before_dml (
535       p_action => 'DELETE',
536       x_rowid => x_rowid
537     );
538 
539      IF (x_mode = 'S') THEN
540     igs_sc_gen_001.set_ctx('R');
541   END IF;
542  DELETE FROM igs_pe_depd_active
543     WHERE rowid = x_rowid;
544 
545     IF (SQL%NOTFOUND) THEN
546      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
547      igs_ge_msg_stack.add;
548      igs_sc_gen_001.unset_ctx('R');
549      app_exception.raise_exception;
550  END IF;
551  IF (x_mode = 'S') THEN
552     igs_sc_gen_001.unset_ctx('R');
553   END IF;
554 
555 
556   END delete_row;
557 
558 
559 END igs_pe_depd_active_pkg;