1 PACKAGE BODY igs_he_sys_rtn_clas_seed_pkg AS
2 /* $Header: IGSWI29B.pls 115.2 2002/11/29 04:42:43 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_sys_rtn_clas%ROWTYPE;
6 new_references igs_he_sys_rtn_clas%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_system_return_class_type IN VARCHAR2 ,
12 x_system_return_class_recid IN VARCHAR2 ,
13 x_description IN VARCHAR2 ,
14 x_creation_date IN DATE ,
15 x_created_by IN NUMBER ,
16 x_last_update_date IN DATE ,
17 x_last_updated_by IN NUMBER ,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By : rgopalan
22 || Created On : 15-JUN-2001
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_HE_SYS_RTN_CLAS
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.system_return_class_type := x_system_return_class_type;
54 new_references.system_return_class_recid := x_system_return_class_recid;
55 new_references.description := x_description;
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_child_existance IS
73 /*
74 || Created By : rgopalan
75 || Created On : 15-JUN-2001
76 || Purpose : Checks for the existance of Child records.
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 null;
85
86 END check_child_existance;
87
88
89 FUNCTION get_pk_for_validation (
90 x_system_return_class_type IN VARCHAR2
91 ) RETURN BOOLEAN AS
92 /*
93 || Created By : rgopalan
94 || Created On : 15-JUN-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_he_sys_rtn_clas
104 WHERE system_return_class_type = x_system_return_class_type
105 FOR UPDATE NOWAIT;
106
107 lv_rowid cur_rowid%RowType;
108
109 BEGIN
110
111 OPEN cur_rowid;
112 FETCH cur_rowid INTO lv_rowid;
113 IF (cur_rowid%FOUND) THEN
114 CLOSE cur_rowid;
115 RETURN(TRUE);
116 ELSE
117 CLOSE cur_rowid;
118 RETURN(FALSE);
119 END IF;
120
121 END get_pk_for_validation;
122
123
124 PROCEDURE before_dml (
125 p_action IN VARCHAR2,
126 x_rowid IN VARCHAR2 ,
127 x_system_return_class_type IN VARCHAR2 ,
128 x_system_return_class_recid IN VARCHAR2 ,
129 x_description IN VARCHAR2 ,
130 x_creation_date IN DATE ,
131 x_created_by IN NUMBER ,
132 x_last_update_date IN DATE ,
133 x_last_updated_by IN NUMBER ,
134 x_last_update_login IN NUMBER
135 ) AS
136 /*
137 || Created By : rgopalan
138 || Created On : 15-JUN-2001
139 || Purpose : Initialises the columns, Checks Constraints, Calls the
140 || Trigger Handlers for the table, before any DML operation.
141 || Known limitations, enhancements or remarks :
142 || Change History :
143 || Who When What
144 || (reverse chronological order - newest change first)
145 */
146 BEGIN
147
148 set_column_values (
149 p_action,
150 x_rowid,
151 x_system_return_class_type,
152 x_system_return_class_recid,
153 x_description,
154 x_creation_date,
155 x_created_by,
156 x_last_update_date,
157 x_last_updated_by,
158 x_last_update_login
159 );
160
161 IF (p_action = 'INSERT') THEN
162 -- Call all the procedures related to Before Insert.
163 IF ( get_pk_for_validation(
164 new_references.system_return_class_type
165 )
166 ) THEN
167 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171 ELSIF (p_action = 'DELETE') THEN
172 -- Call all the procedures related to Before Delete.
173 check_child_existance;
174 ELSIF (p_action = 'VALIDATE_INSERT') THEN
175 -- Call all the procedures related to Before Insert.
176 IF ( get_pk_for_validation (
177 new_references.system_return_class_type
178 )
179 ) THEN
180 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
181 igs_ge_msg_stack.add;
182 app_exception.raise_exception;
183 END IF;
184 ELSIF (p_action = 'VALIDATE_DELETE') THEN
185 check_child_existance;
186 END IF;
187
188 END before_dml;
189
190
194 x_system_return_class_recid IN VARCHAR2,
191 PROCEDURE insert_row (
192 x_rowid IN OUT NOCOPY VARCHAR2,
193 x_system_return_class_type IN OUT NOCOPY VARCHAR2,
195 x_description IN VARCHAR2,
196 x_mode IN VARCHAR2
197 ) AS
198 /*
199 || Created By : rgopalan
200 || Created On : 15-JUN-2001
201 || Purpose : Handles the INSERT DML logic for the table.
202 || Known limitations, enhancements or remarks :
203 || Change History :
204 || Who When What
205 || (reverse chronological order - newest change first)
206 */
207 CURSOR c IS
208 SELECT rowid
209 FROM igs_he_sys_rtn_clas
210 WHERE system_return_class_type = x_system_return_class_type;
211
212 x_last_update_date DATE;
213 x_last_updated_by NUMBER;
214 x_last_update_login NUMBER;
215
216 BEGIN
217
218 x_last_update_date := SYSDATE;
219 IF (x_mode = 'I') THEN
220 x_last_updated_by := 1;
221 x_last_update_login := 0;
222 ELSIF (x_mode = 'R') THEN
223 x_last_updated_by := fnd_global.user_id;
224 IF (x_last_updated_by IS NULL) THEN
225 x_last_updated_by := -1;
226 END IF;
227 x_last_update_login := fnd_global.login_id;
228 IF (x_last_update_login IS NULL) THEN
229 x_last_update_login := -1;
230 END IF;
231 ELSE
232 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
233 igs_ge_msg_stack.add;
234 app_exception.raise_exception;
235 END IF;
236
237 before_dml(
238 p_action => 'INSERT',
239 x_rowid => x_rowid,
240 x_system_return_class_type => x_system_return_class_type,
241 x_system_return_class_recid => x_system_return_class_recid,
242 x_description => x_description,
243 x_creation_date => x_last_update_date,
244 x_created_by => x_last_updated_by,
245 x_last_update_date => x_last_update_date,
246 x_last_updated_by => x_last_updated_by,
247 x_last_update_login => x_last_update_login
248 );
249
250 INSERT INTO igs_he_sys_rtn_clas (
251 system_return_class_type,
252 system_return_class_recid,
253 description,
254 creation_date,
255 created_by,
256 last_update_date,
257 last_updated_by,
258 last_update_login
259 ) VALUES (
263 x_last_update_date,
260 new_references.system_return_class_type,
261 new_references.system_return_class_recid,
262 new_references.description,
264 x_last_updated_by,
265 x_last_update_date,
266 x_last_updated_by,
267 x_last_update_login
268 );
269
270 OPEN c;
271 FETCH c INTO x_rowid;
272 IF (c%NOTFOUND) THEN
273 CLOSE c;
274 RAISE NO_DATA_FOUND;
275 END IF;
276 CLOSE c;
277
278 END insert_row;
279
280
281 PROCEDURE lock_row (
282 x_rowid IN VARCHAR2,
283 x_system_return_class_type IN VARCHAR2,
284 x_system_return_class_recid IN VARCHAR2,
285 x_description IN VARCHAR2
286 ) AS
287 /*
288 || Created By : rgopalan
289 || Created On : 15-JUN-2001
290 || Purpose : Handles the LOCK mechanism for the table.
291 || Known limitations, enhancements or remarks :
292 || Change History :
293 || Who When What
294 || (reverse chronological order - newest change first)
295 */
296 CURSOR c1 IS
297 SELECT
298 system_return_class_recid,
299 description
300 FROM igs_he_sys_rtn_clas
301 WHERE rowid = x_rowid
302 FOR UPDATE NOWAIT;
303
304 tlinfo c1%ROWTYPE;
305
306 BEGIN
307
308 OPEN c1;
309 FETCH c1 INTO tlinfo;
310 IF (c1%notfound) THEN
311 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
312 igs_ge_msg_stack.add;
313 CLOSE c1;
314 app_exception.raise_exception;
315 RETURN;
316 END IF;
317 CLOSE c1;
318
319 IF (
320 (tlinfo.system_return_class_recid = x_system_return_class_recid)
321 AND (tlinfo.description = x_description)
322 ) THEN
323 NULL;
324 ELSE
325 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
326 igs_ge_msg_stack.add;
327 app_exception.raise_exception;
328 END IF;
329
330 RETURN;
331
332 END lock_row;
333
334
335 PROCEDURE update_row (
336 x_rowid IN VARCHAR2,
337 x_system_return_class_type IN VARCHAR2,
338 x_system_return_class_recid IN VARCHAR2,
339 x_description IN VARCHAR2,
340 x_mode IN VARCHAR2
341 ) AS
342 /*
343 || Created By : rgopalan
344 || Created On : 15-JUN-2001
345 || Purpose : Handles the UPDATE DML logic for the table.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 */
351 x_last_update_date DATE ;
352 x_last_updated_by NUMBER;
353 x_last_update_login NUMBER;
354
355 BEGIN
356
357 x_last_update_date := SYSDATE;
358 IF (X_MODE = 'I') THEN
359 x_last_updated_by := 1;
360 x_last_update_login := 0;
361 ELSIF (x_mode = 'R') THEN
362 x_last_updated_by := fnd_global.user_id;
363 IF x_last_updated_by IS NULL THEN
364 x_last_updated_by := -1;
365 END IF;
366 x_last_update_login := fnd_global.login_id;
367 IF (x_last_update_login IS NULL) THEN
368 x_last_update_login := -1;
369 END IF;
370 ELSE
371 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 END IF;
375
376 before_dml(
377 p_action => 'UPDATE',
378 x_rowid => x_rowid,
379 x_system_return_class_type => x_system_return_class_type,
380 x_system_return_class_recid => x_system_return_class_recid,
381 x_description => x_description,
382 x_creation_date => x_last_update_date,
383 x_created_by => x_last_updated_by,
384 x_last_update_date => x_last_update_date,
385 x_last_updated_by => x_last_updated_by,
386 x_last_update_login => x_last_update_login
387 );
388
389 UPDATE igs_he_sys_rtn_clas
390 SET
391 system_return_class_recid = new_references.system_return_class_recid,
392 description = new_references.description,
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_system_return_class_type IN OUT NOCOPY VARCHAR2,
408 x_system_return_class_recid IN VARCHAR2,
409 x_description IN VARCHAR2,
410 x_mode IN VARCHAR2
411 ) AS
412 /*
413 || Created By : rgopalan
414 || Created On : 15-JUN-2001
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_he_sys_rtn_clas
424 WHERE system_return_class_type = x_system_return_class_type;
425
426 BEGIN
427
428 OPEN c1;
429 FETCH c1 INTO x_rowid;
430 IF (c1%NOTFOUND) THEN
431 CLOSE c1;
432
433 insert_row (
434 x_rowid,
435 x_system_return_class_type,
436 x_system_return_class_recid,
437 x_description,
438 x_mode
439 );
440 RETURN;
441 END IF;
442 CLOSE c1;
443
444 update_row (
445 x_rowid,
446 x_system_return_class_type,
447 x_system_return_class_recid,
448 x_description,
449 x_mode
450 );
451
452 END add_row;
453
454
455 PROCEDURE delete_row (
456 x_rowid IN VARCHAR2
457 ) AS
458 /*
459 || Created By : rgopalan
460 || Created On : 15-JUN-2001
461 || Purpose : Handles the DELETE DML logic for the table.
462 || Known limitations, enhancements or remarks :
463 || Change History :
464 || Who When What
465 || (reverse chronological order - newest change first)
466 */
467 BEGIN
468
469 before_dml (
470 p_action => 'DELETE',
471 x_rowid => x_rowid
472 );
473
474 DELETE FROM igs_he_sys_rtn_clas
475 WHERE rowid = x_rowid;
476
477 IF (SQL%NOTFOUND) THEN
478 RAISE NO_DATA_FOUND;
479 END IF;
480
481 END delete_row;
482
483
484 END igs_he_sys_rtn_clas_seed_pkg;
[Home] [Help]