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