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_SL_PE_CITI_MAP_PKG

Source


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