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