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