1 PACKAGE BODY igs_en_nsc_options_pkg AS
2 /* $Header: IGSEI62B.pls 115.2 2002/11/28 23:47:32 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_nsc_options%ROWTYPE;
6 new_references igs_en_nsc_options%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_opt_type IN VARCHAR2 DEFAULT NULL,
12 x_opt_val IN VARCHAR2 DEFAULT NULL,
13 x_priority IN NUMBER DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 18-JAN-2002
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM igs_en_nsc_options
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
44 CLOSE cur_old_ref_values;
45 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
46 igs_ge_msg_stack.add;
47 app_exception.raise_exception;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.opt_type := x_opt_type;
54 new_references.opt_val := x_opt_val;
55 new_references.priority := x_priority;
56
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END set_column_values;
70
71
72 PROCEDURE check_uniqueness AS
73 /*
74 || Created By : [email protected]
75 || Created On : 18-JAN-2002
76 || Purpose : Handles the Unique Constraint logic defined for the columns.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 */
82 BEGIN
83
84 IF ( get_uk_for_validation (
85 new_references.opt_type,
86 new_references.opt_val
87 )
88 ) THEN
89 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
90 igs_ge_msg_stack.add;
91 app_exception.raise_exception;
92 END IF;
93
94 END check_uniqueness;
95
96
97 FUNCTION get_uk_for_validation (
98 x_opt_type IN VARCHAR2,
99 x_opt_val IN VARCHAR2
100 ) RETURN BOOLEAN AS
101 /*
102 || Created By : [email protected]
103 || Created On : 18-JAN-2002
104 || Purpose : Validates the Unique Keys of the table.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 CURSOR cur_rowid IS
111 SELECT rowid
112 FROM igs_en_nsc_options
113 WHERE opt_type = x_opt_type
114 AND opt_val = x_opt_val
115 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
116
117 lv_rowid cur_rowid%RowType;
118
119 BEGIN
120
121 OPEN cur_rowid;
122 FETCH cur_rowid INTO lv_rowid;
123 IF (cur_rowid%FOUND) THEN
124 CLOSE cur_rowid;
125 RETURN (true);
126 ELSE
127 CLOSE cur_rowid;
128 RETURN(FALSE);
129 END IF;
130
131 END get_uk_for_validation ;
132
133
134 PROCEDURE before_dml (
135 p_action IN VARCHAR2,
136 x_rowid IN VARCHAR2 DEFAULT NULL,
137 x_opt_type IN VARCHAR2 DEFAULT NULL,
138 x_opt_val IN VARCHAR2 DEFAULT NULL,
139 x_priority IN NUMBER DEFAULT NULL,
140 x_creation_date IN DATE DEFAULT NULL,
141 x_created_by IN NUMBER DEFAULT NULL,
142 x_last_update_date IN DATE DEFAULT NULL,
143 x_last_updated_by IN NUMBER DEFAULT NULL,
144 x_last_update_login IN NUMBER DEFAULT NULL
145 ) AS
146 /*
147 || Created By : [email protected]
148 || Created On : 18-JAN-2002
149 || Purpose : Initialises the columns, Checks Constraints, Calls the
150 || Trigger Handlers for the table, before any DML operation.
151 || Known limitations, enhancements or remarks :
152 || Change History :
153 || Who When What
154 || (reverse chronological order - newest change first)
155 */
156 BEGIN
157
158 set_column_values (
159 p_action,
160 x_rowid,
161 x_opt_type,
162 x_opt_val,
163 x_priority,
164 x_creation_date,
165 x_created_by,
166 x_last_update_date,
167 x_last_updated_by,
168 x_last_update_login
169 );
170
171 IF (p_action = 'INSERT') THEN
172 -- Call all the procedures related to Before Insert.
173 check_uniqueness;
174 ELSIF (p_action = 'UPDATE') THEN
175 -- Call all the procedures related to Before Update.
176 check_uniqueness;
177 ELSIF (p_action = 'VALIDATE_INSERT') THEN
178 -- Call all the procedures related to Before Insert.
179 check_uniqueness;
180 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
181 check_uniqueness;
182 END IF;
183
184 END before_dml;
185
186
187 PROCEDURE insert_row (
188 x_rowid IN OUT NOCOPY VARCHAR2,
189 x_opt_type IN VARCHAR2,
190 x_opt_val IN VARCHAR2,
191 x_priority IN NUMBER,
192 x_mode IN VARCHAR2 DEFAULT 'R'
193 ) AS
194 /*
195 || Created By : [email protected]
196 || Created On : 18-JAN-2002
197 || Purpose : Handles the INSERT DML logic for the table.
198 || Known limitations, enhancements or remarks :
199 || Change History :
200 || Who When What
201 || (reverse chronological order - newest change first)
202 */
203 CURSOR c IS
204 SELECT rowid
205 FROM igs_en_nsc_options
206 WHERE opt_type = x_opt_type
207 AND opt_val = x_opt_val ;
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 = 'R') 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_opt_type => x_opt_type,
238 x_opt_val => x_opt_val,
239 x_priority => x_priority,
240 x_creation_date => x_last_update_date,
241 x_created_by => x_last_updated_by,
242 x_last_update_date => x_last_update_date,
243 x_last_updated_by => x_last_updated_by,
244 x_last_update_login => x_last_update_login
245 );
246
247 INSERT INTO igs_en_nsc_options (
248 opt_type,
249 opt_val,
250 priority,
251 creation_date,
252 created_by,
253 last_update_date,
254 last_updated_by,
255 last_update_login
256 ) VALUES (
257 new_references.opt_type,
258 new_references.opt_val,
259 new_references.priority,
260 x_last_update_date,
261 x_last_updated_by,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_login
265 );
266
267 OPEN c;
268 FETCH c INTO x_rowid;
269 IF (c%NOTFOUND) THEN
270 CLOSE c;
271 RAISE NO_DATA_FOUND;
272 END IF;
273 CLOSE c;
274
275 END insert_row;
276
277
278 PROCEDURE lock_row (
279 x_rowid IN VARCHAR2,
280 x_opt_type IN VARCHAR2,
281 x_opt_val IN VARCHAR2,
282 x_priority IN NUMBER
283 ) AS
284 /*
285 || Created By : [email protected]
286 || Created On : 18-JAN-2002
287 || Purpose : Handles the LOCK mechanism for the table.
288 || Known limitations, enhancements or remarks :
289 || Change History :
290 || Who When What
291 || (reverse chronological order - newest change first)
292 */
293 CURSOR c1 IS
294 SELECT
295 opt_type,
296 opt_val,
297 priority
298 FROM igs_en_nsc_options
299 WHERE rowid = x_rowid
300 FOR UPDATE NOWAIT;
301
302 tlinfo c1%ROWTYPE;
303
304 BEGIN
305
306 OPEN c1;
307 FETCH c1 INTO tlinfo;
308 IF (c1%notfound) THEN
309 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
310 igs_ge_msg_stack.add;
311 CLOSE c1;
312 app_exception.raise_exception;
313 RETURN;
314 END IF;
315 CLOSE c1;
316
317 IF (
318 (tlinfo.opt_type = x_opt_type)
319 AND (tlinfo.opt_val = x_opt_val)
320 AND ((tlinfo.priority = x_priority) OR ((tlinfo.priority IS NULL) AND (X_priority IS NULL)))
321 ) THEN
322 NULL;
323 ELSE
324 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
325 igs_ge_msg_stack.add;
326 app_exception.raise_exception;
327 END IF;
328
329 RETURN;
330
331 END lock_row;
332
333
334 PROCEDURE update_row (
335 x_rowid IN VARCHAR2,
336 x_opt_type IN VARCHAR2,
337 x_opt_val IN VARCHAR2,
338 x_priority IN NUMBER,
339 x_mode IN VARCHAR2 DEFAULT 'R'
340 ) AS
341 /*
342 || Created By : [email protected]
343 || Created On : 18-JAN-2002
344 || Purpose : Handles the UPDATE DML logic 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 x_last_update_date DATE ;
351 x_last_updated_by NUMBER;
352 x_last_update_login NUMBER;
353
354 BEGIN
355
356 x_last_update_date := SYSDATE;
357 IF (X_MODE = 'I') THEN
358 x_last_updated_by := 1;
359 x_last_update_login := 0;
360 ELSIF (x_mode = 'R') THEN
361 x_last_updated_by := fnd_global.user_id;
362 IF x_last_updated_by IS NULL THEN
363 x_last_updated_by := -1;
364 END IF;
365 x_last_update_login := fnd_global.login_id;
366 IF (x_last_update_login IS NULL) THEN
367 x_last_update_login := -1;
368 END IF;
369 ELSE
370 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374
375 before_dml(
376 p_action => 'UPDATE',
377 x_rowid => x_rowid,
378 x_opt_type => x_opt_type,
379 x_opt_val => x_opt_val,
380 x_priority => x_priority,
381 x_creation_date => x_last_update_date,
382 x_created_by => x_last_updated_by,
383 x_last_update_date => x_last_update_date,
384 x_last_updated_by => x_last_updated_by,
385 x_last_update_login => x_last_update_login
386 );
387
388 UPDATE igs_en_nsc_options
389 SET
390 opt_type = new_references.opt_type,
391 opt_val = new_references.opt_val,
392 priority = new_references.priority,
393 last_update_date = x_last_update_date,
394 last_updated_by = x_last_updated_by,
395 last_update_login = x_last_update_login
396 WHERE rowid = x_rowid;
397
398 IF (SQL%NOTFOUND) THEN
399 RAISE NO_DATA_FOUND;
400 END IF;
401
402 END update_row;
403
404
405 PROCEDURE add_row (
406 x_rowid IN OUT NOCOPY VARCHAR2,
407 x_opt_type IN VARCHAR2,
408 x_opt_val IN VARCHAR2,
409 x_priority IN NUMBER,
410 x_mode IN VARCHAR2 DEFAULT 'R'
411 ) AS
412 /*
413 || Created By : [email protected]
414 || Created On : 18-JAN-2002
415 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
416 || Known limitations, enhancements or remarks :
417 || Change History :
418 || Who When What
419 || (reverse chronological order - newest change first)
420 */
421 CURSOR c1 IS
422 SELECT rowid
423 FROM igs_en_nsc_options
424 WHERE opt_type = x_opt_type
425 AND opt_val = x_opt_val ;
426
427 BEGIN
428
429 OPEN c1;
430 FETCH c1 INTO x_rowid;
431 IF (c1%NOTFOUND) THEN
432 CLOSE c1;
433
434 insert_row (
435 x_rowid,
436 x_opt_type,
437 x_opt_val,
438 x_priority,
439 x_mode
440 );
441 RETURN;
442 END IF;
443 CLOSE c1;
444
445 update_row (
446 x_rowid,
447 x_opt_type,
448 x_opt_val,
449 x_priority,
450 x_mode
451 );
452
453 END add_row;
454
455
456 PROCEDURE delete_row (
457 x_rowid IN VARCHAR2
458 ) AS
459 /*
460 || Created By : [email protected]
461 || Created On : 18-JAN-2002
462 || Purpose : Handles the DELETE DML logic for the table.
463 || Known limitations, enhancements or remarks :
464 || Change History :
465 || Who When What
466 || (reverse chronological order - newest change first)
467 */
468 BEGIN
469
470 before_dml (
471 p_action => 'DELETE',
472 x_rowid => x_rowid
473 );
474
475 DELETE FROM igs_en_nsc_options
476 WHERE rowid = x_rowid;
477
478 IF (SQL%NOTFOUND) THEN
479 RAISE NO_DATA_FOUND;
480 END IF;
481
482 END delete_row;
483
484
485 END igs_en_nsc_options_pkg;
[Home] [Help]