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_HOUSING_MAP_PKG

Source


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