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