[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ALIAS_TYPES_PKG
Source
1 PACKAGE BODY igs_as_alias_types_pkg AS
2 /* $Header: IGSDI75B.pls 115.2 2002/11/28 23:30:29 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_alias_types%ROWTYPE;
6 new_references igs_as_alias_types%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_alias_type IN VARCHAR2,
12 x_order_of_display IN NUMBER,
13 x_creation_date IN DATE,
14 x_created_by IN NUMBER,
15 x_last_update_date IN DATE,
16 x_last_updated_by IN NUMBER,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*
20 || Created By : [email protected]
21 || Created On : 23-OCT-2002
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_as_alias_types
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.alias_type := x_alias_type;
53 new_references.order_of_display := x_order_of_display;
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_alias_type IN VARCHAR2
72 ) RETURN BOOLEAN AS
73 /*
74 || Created By : [email protected]
75 || Created On : 23-OCT-2002
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_as_alias_types
85 WHERE alias_type = x_alias_type
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,
108 x_alias_type IN VARCHAR2,
109 x_order_of_display IN NUMBER,
110 x_creation_date IN DATE,
111 x_created_by IN NUMBER,
112 x_last_update_date IN DATE,
113 x_last_updated_by IN NUMBER,
114 x_last_update_login IN NUMBER
115 ) AS
116 /*
117 || Created By : [email protected]
118 || Created On : 23-OCT-2002
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_alias_type,
132 x_order_of_display,
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.alias_type
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.alias_type
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_alias_type IN VARCHAR2,
168 x_order_of_display IN NUMBER,
169 x_mode IN VARCHAR2
170 ) AS
171 /*
172 || Created By : [email protected]
173 || Created On : 23-OCT-2002
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
181 x_last_update_date DATE;
182 x_last_updated_by NUMBER;
183 x_last_update_login NUMBER;
184
185 BEGIN
186
187 x_last_update_date := SYSDATE;
188 IF (x_mode = 'I') THEN
189 x_last_updated_by := 1;
190 x_last_update_login := 0;
191 ELSIF (x_mode = 'R') THEN
192 x_last_updated_by := fnd_global.user_id;
193 IF (x_last_updated_by IS NULL) THEN
194 x_last_updated_by := -1;
195 END IF;
196 x_last_update_login := fnd_global.login_id;
197 IF (x_last_update_login IS NULL) THEN
198 x_last_update_login := -1;
199 END IF;
200 ELSE
201 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
202 igs_ge_msg_stack.add;
203 app_exception.raise_exception;
204 END IF;
205
206 before_dml(
207 p_action => 'INSERT',
208 x_rowid => x_rowid,
209 x_alias_type => x_alias_type,
210 x_order_of_display => x_order_of_display,
211 x_creation_date => x_last_update_date,
212 x_created_by => x_last_updated_by,
213 x_last_update_date => x_last_update_date,
214 x_last_updated_by => x_last_updated_by,
215 x_last_update_login => x_last_update_login
216 );
217
218 INSERT INTO igs_as_alias_types (
219 alias_type,
220 order_of_display,
221 creation_date,
222 created_by,
223 last_update_date,
224 last_updated_by,
225 last_update_login
226 ) VALUES (
227 new_references.alias_type,
228 new_references.order_of_display,
229 x_last_update_date,
230 x_last_updated_by,
231 x_last_update_date,
232 x_last_updated_by,
233 x_last_update_login
234 ) RETURNING ROWID INTO x_rowid;
235
236 END insert_row;
237
238
239 PROCEDURE lock_row (
240 x_rowid IN VARCHAR2,
241 x_alias_type IN VARCHAR2,
242 x_order_of_display IN NUMBER
243 ) AS
244 /*
245 || Created By : [email protected]
246 || Created On : 23-OCT-2002
247 || Purpose : Handles the LOCK mechanism for the table.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253 CURSOR c1 IS
254 SELECT
255 order_of_display
256 FROM igs_as_alias_types
257 WHERE rowid = x_rowid
258 FOR UPDATE NOWAIT;
259
260 tlinfo c1%ROWTYPE;
261
262 BEGIN
263
264 OPEN c1;
265 FETCH c1 INTO tlinfo;
266 IF (c1%notfound) THEN
267 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
268 igs_ge_msg_stack.add;
269 CLOSE c1;
270 app_exception.raise_exception;
271 RETURN;
272 END IF;
273 CLOSE c1;
274
275 IF (
276 (tlinfo.order_of_display = x_order_of_display)
277 ) THEN
278 NULL;
279 ELSE
280 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
281 igs_ge_msg_stack.add;
282 app_exception.raise_exception;
283 END IF;
284
285 RETURN;
286
287 END lock_row;
288
289
290 PROCEDURE update_row (
291 x_rowid IN VARCHAR2,
292 x_alias_type IN VARCHAR2,
293 x_order_of_display IN NUMBER,
294 x_mode IN VARCHAR2
295 ) AS
296 /*
297 || Created By : [email protected]
298 || Created On : 23-OCT-2002
299 || Purpose : Handles the UPDATE DML logic for the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 x_last_update_date DATE ;
306 x_last_updated_by NUMBER;
307 x_last_update_login NUMBER;
308
309 BEGIN
310
311 x_last_update_date := SYSDATE;
312 IF (X_MODE = 'I') THEN
313 x_last_updated_by := 1;
314 x_last_update_login := 0;
315 ELSIF (x_mode = 'R') THEN
316 x_last_updated_by := fnd_global.user_id;
317 IF x_last_updated_by IS NULL THEN
318 x_last_updated_by := -1;
319 END IF;
320 x_last_update_login := fnd_global.login_id;
321 IF (x_last_update_login IS NULL) THEN
322 x_last_update_login := -1;
323 END IF;
324 ELSE
325 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
326 igs_ge_msg_stack.add;
327 app_exception.raise_exception;
328 END IF;
329
330 before_dml(
331 p_action => 'UPDATE',
332 x_rowid => x_rowid,
333 x_alias_type => x_alias_type,
334 x_order_of_display => x_order_of_display,
335 x_creation_date => x_last_update_date,
336 x_created_by => x_last_updated_by,
337 x_last_update_date => x_last_update_date,
338 x_last_updated_by => x_last_updated_by,
339 x_last_update_login => x_last_update_login
340 );
341
342 UPDATE igs_as_alias_types
343 SET
344 order_of_display = new_references.order_of_display,
345 last_update_date = x_last_update_date,
346 last_updated_by = x_last_updated_by,
347 last_update_login = x_last_update_login
348 WHERE rowid = x_rowid;
349
350 IF (SQL%NOTFOUND) THEN
351 RAISE NO_DATA_FOUND;
352 END IF;
353
354 END update_row;
355
356
357 PROCEDURE add_row (
358 x_rowid IN OUT NOCOPY VARCHAR2,
359 x_alias_type IN VARCHAR2,
360 x_order_of_display IN NUMBER,
361 x_mode IN VARCHAR2
362 ) AS
363 /*
364 || Created By : [email protected]
365 || Created On : 23-OCT-2002
366 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
367 || Known limitations, enhancements or remarks :
368 || Change History :
369 || Who When What
370 || (reverse chronological order - newest change first)
371 */
372 CURSOR c1 IS
373 SELECT rowid
374 FROM igs_as_alias_types
375 WHERE alias_type = x_alias_type;
376
377 BEGIN
378
379 OPEN c1;
380 FETCH c1 INTO x_rowid;
381 IF (c1%NOTFOUND) THEN
382 CLOSE c1;
383
384 insert_row (
385 x_rowid,
386 x_alias_type,
387 x_order_of_display,
388 x_mode
389 );
390 RETURN;
391 END IF;
392 CLOSE c1;
393
394 update_row (
395 x_rowid,
396 x_alias_type,
397 x_order_of_display,
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 : [email protected]
409 || Created On : 23-OCT-2002
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 igs_as_alias_types
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
433 END igs_as_alias_types_pkg;