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