1 PACKAGE BODY igs_he_code_values_pkg AS
2 /* $Header: IGSWI05B.pls 115.8 2003/01/07 13:11:30 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_code_values%ROWTYPE;
6 new_references igs_he_code_values%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_value IN VARCHAR2 ,
13 x_value_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 x_closed_ind IN VARCHAR2
20 ) AS
21 /*
22 || Created By : rgopalan
23 || Created On : 15-JUN-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_HE_CODE_VALUES
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.code_type := x_code_type;
55 new_references.value := x_value;
56 new_references.value_description := x_value_description;
57 new_references.closed_ind := x_closed_ind ;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75
76 /*
77 || Created By : rgopalan
78 || Created On : 15-JUN-2001
79 || Purpose : Initialises the Old and New references for the columns of the table.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 */
85 BEGIN
86 IF get_uk_for_validation(new_references.code_type, new_references.value) Then
87 fnd_message.set_name('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
88 igs_ge_msg_stack.add;
89 app_exception.raise_exception;
90 END IF;
91 END check_uniqueness;
92
93 PROCEDURE check_parent_existance AS
94 /*
95 || Created By : rgopalan
96 || Created On : 15-JUN-2001
97 || Purpose : Checks for the existance of Parent records.
98 || Known limitations, enhancements or remarks :
99 || Change History :
100 || Who When What
101 || (reverse chronological order - newest change first)
102 */
103 BEGIN
104
105 IF (((old_references.code_type = new_references.code_type)) OR
106 ((new_references.code_type IS NULL))) THEN
107 NULL;
108 ELSIF NOT igs_he_code_types_pkg.get_pk_for_validation (
109 new_references.code_type
110 ) THEN
111 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
112 igs_ge_msg_stack.add;
113 app_exception.raise_exception;
114 END IF;
115
116 END check_parent_existance;
117
118
119 FUNCTION get_pk_for_validation (
120 x_code_type IN VARCHAR2,
121 x_value IN VARCHAR2
122 ) RETURN BOOLEAN AS
123 /*
124 || Created By : rgopalan
125 || Created On : 15-JUN-2001
126 || Purpose : Validates the Primary Key of the table.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 CURSOR cur_rowid IS
133 SELECT rowid
134 FROM igs_he_code_values
135 WHERE code_type = x_code_type
136 AND value = x_value
137 FOR UPDATE NOWAIT;
138
139 lv_rowid cur_rowid%RowType;
140
141 BEGIN
142
143 OPEN cur_rowid;
144 FETCH cur_rowid INTO lv_rowid;
145 IF (cur_rowid%FOUND) THEN
146 CLOSE cur_rowid;
147 RETURN(TRUE);
148 ELSE
149 CLOSE cur_rowid;
150 RETURN(FALSE);
151 END IF;
152
153 END get_pk_for_validation;
154
155 FUNCTION get_uk_for_validation (
156 x_code_type IN VARCHAR2,
157 x_value IN VARCHAR2
158 ) RETURN BOOLEAN AS
159 /*
160 || Created By : rgopalan
161 || Created On : 15-JUN-2001
162 || Purpose : Validates the Unique Keys of the table.
163 || Known limitations, enhancements or remarks :
164 || Change History :
165 || Who When What
166 || (reverse chronological order - newest change first)
167 */
168 CURSOR cur_rowid IS
169 SELECT rowid
170 FROM igs_he_code_values
171 WHERE code_type = x_code_type
172 AND value = x_value
173 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
174
175 lv_rowid cur_rowid%RowType;
176
177 BEGIN
178
179 OPEN cur_rowid;
180 FETCH cur_rowid INTO lv_rowid;
181 IF (cur_rowid%FOUND) THEN
182 CLOSE cur_rowid;
183 RETURN(TRUE);
184 ELSE
185 CLOSE cur_rowid;
186 RETURN(FALSE);
187 END IF;
188
189 END get_uk_for_validation;
190
191 PROCEDURE get_fk_igs_he_code_types (
192 x_code_type IN VARCHAR2
193 ) AS
194 /*
195 || Created By : rgopalan
196 || Created On : 15-JUN-2001
197 || Purpose : Validates the Foreign Keys 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 cur_rowid IS
204 SELECT rowid
205 FROM igs_he_code_values
206 WHERE ((code_type = x_code_type));
207
208 lv_rowid cur_rowid%RowType;
209
210 BEGIN
211
212 OPEN cur_rowid;
213 FETCH cur_rowid INTO lv_rowid;
214 IF (cur_rowid%FOUND) THEN
215 CLOSE cur_rowid;
216 fnd_message.set_name ('IGS', 'IGS_HE_HECDVAL_HECDTY_FK');
217 igs_ge_msg_stack.add;
218 app_exception.raise_exception;
219 RETURN;
220 END IF;
221 CLOSE cur_rowid;
222
223 END get_fk_igs_he_code_types;
224
225
226 PROCEDURE before_dml (
227 p_action IN VARCHAR2,
228 x_rowid IN VARCHAR2 ,
229 x_code_type IN VARCHAR2 ,
230 x_value IN VARCHAR2 ,
231 x_value_description IN VARCHAR2 ,
232 x_creation_date IN DATE ,
233 x_created_by IN NUMBER ,
234 x_last_update_date IN DATE ,
235 x_last_updated_by IN NUMBER ,
236 x_last_update_login IN NUMBER ,
237 x_closed_ind IN VARCHAR2
238 ) AS
239 /*
240 || Created By : rgopalan
241 || Created On : 15-JUN-2001
242 || Purpose : Initialises the columns, Checks Constraints, Calls the
243 || Trigger Handlers for the table, before any DML operation.
244 || Known limitations, enhancements or remarks :
245 || Change History :
246 || Who When What
247 || (reverse chronological order - newest change first)
248 */
249 BEGIN
250
251 set_column_values (
252 p_action,
253 x_rowid,
254 x_code_type,
255 x_value,
256 x_value_description,
257 x_creation_date,
258 x_created_by,
259 x_last_update_date,
260 x_last_updated_by,
261 x_last_update_login ,
262 x_closed_ind
263 );
264
265 IF (p_action = 'INSERT') THEN
266 -- Call all the procedures related to Before Insert.
267 IF ( get_pk_for_validation(
268 new_references.code_type,
269 new_references.value
270 )
271 ) THEN
272 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
273 igs_ge_msg_stack.add;
274 app_exception.raise_exception;
275 END IF;
276 check_uniqueness;
277 check_parent_existance;
278 ELSIF (p_action = 'UPDATE') THEN
279 -- Call all the procedures related to Before Update.
280 check_uniqueness;
281 check_parent_existance;
282 ELSIF (p_action = 'VALIDATE_INSERT') THEN
283 -- Call all the procedures related to Before Insert.
284 IF ( get_pk_for_validation (
285 new_references.code_type,
286 new_references.value
287 )
288 ) THEN
289 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 END IF;
293 check_uniqueness;
294 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
295 check_uniqueness;
296 END IF;
297
298 END before_dml;
299
300
301 PROCEDURE insert_row (
302 x_rowid IN OUT NOCOPY VARCHAR2,
303 x_code_type IN VARCHAR2,
304 x_value IN VARCHAR2,
305 x_value_description IN VARCHAR2,
306 x_mode IN VARCHAR2 ,
307 x_closed_ind IN VARCHAR2
308 ) AS
309 /*
310 || Created By : rgopalan
311 || Created On : 15-JUN-2001
312 || Purpose : Handles the INSERT DML logic for the table.
313 || Known limitations, enhancements or remarks :
314 || Change History :
315 || Who When What
316 || (reverse chronological order - newest change first)
317 */
318 CURSOR c IS
319 SELECT rowid
320 FROM igs_he_code_values
321 WHERE code_type = x_code_type
322 AND value = x_value;
323
324 x_last_update_date DATE;
325 x_last_updated_by NUMBER;
326 x_last_update_login NUMBER;
327
328 BEGIN
329
330 x_last_update_date := SYSDATE;
331 IF (x_mode = 'I') THEN
332 x_last_updated_by := 1;
333 x_last_update_login := 0;
334 ELSIF (x_mode = 'R') THEN
335 x_last_updated_by := fnd_global.user_id;
336 IF (x_last_updated_by IS NULL) THEN
337 x_last_updated_by := -1;
338 END IF;
339 x_last_update_login := fnd_global.login_id;
340 IF (x_last_update_login IS NULL) THEN
341 x_last_update_login := -1;
342 END IF;
343 ELSE
344 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 END IF;
348
349 before_dml(
350 p_action => 'INSERT',
351 x_rowid => x_rowid,
352 x_code_type => x_code_type,
353 x_value => x_value,
354 x_value_description => x_value_description,
355 x_creation_date => x_last_update_date,
356 x_created_by => x_last_updated_by,
357 x_last_update_date => x_last_update_date,
358 x_last_updated_by => x_last_updated_by,
359 x_last_update_login => x_last_update_login ,
360 x_closed_ind => x_closed_ind
361 );
362
363 INSERT INTO igs_he_code_values (
364 code_type,
365 value,
366 value_description,
367 creation_date,
368 created_by,
369 last_update_date,
370 last_updated_by,
371 last_update_login,
372 closed_ind
373 ) VALUES (
374 new_references.code_type,
375 new_references.value,
376 new_references.value_description,
377 x_last_update_date,
378 x_last_updated_by,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_login ,
382 new_references.closed_ind
383 );
384
385 OPEN c;
386 FETCH c INTO x_rowid;
387 IF (c%NOTFOUND) THEN
388 CLOSE c;
389 RAISE NO_DATA_FOUND;
390 END IF;
391 CLOSE c;
392
393 END insert_row;
394
395
396 PROCEDURE lock_row (
397 x_rowid IN VARCHAR2,
398 x_code_type IN VARCHAR2,
399 x_value IN VARCHAR2,
400 x_value_description IN VARCHAR2,
401 x_closed_ind IN VARCHAR2
402 ) AS
403 /*
404 || Created By : rgopalan
405 || Created On : 15-JUN-2001
406 || Purpose : Handles the LOCK mechanism for the table.
407 || Known limitations, enhancements or remarks :
408 || Change History :
409 || Who When What
410 || (reverse chronological order - newest change first)
411 */
412 CURSOR c1 IS
413 SELECT
414 value_description, closed_ind
415 FROM igs_he_code_values
416 WHERE rowid = x_rowid
417 FOR UPDATE NOWAIT;
418
419 tlinfo c1%ROWTYPE;
420
421 BEGIN
422
423 OPEN c1;
424 FETCH c1 INTO tlinfo;
425 IF (c1%notfound) THEN
426 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
427 igs_ge_msg_stack.add;
428 CLOSE c1;
429 app_exception.raise_exception;
430 RETURN;
431 END IF;
432 CLOSE c1;
433
434 IF (
435 (tlinfo.value_description = x_value_description) AND
436 ( (tlinfo.closed_ind IS NULL AND x_closed_ind IS NULL ) OR (tlinfo.closed_ind = x_closed_ind) )
437 ) THEN
438 NULL;
439 ELSE
440 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
441 igs_ge_msg_stack.add;
442 app_exception.raise_exception;
443 END IF;
444
445 RETURN;
446
447 END lock_row;
448
449
450 PROCEDURE update_row (
451 x_rowid IN VARCHAR2,
452 x_code_type IN VARCHAR2,
453 x_value IN VARCHAR2,
454 x_value_description IN VARCHAR2,
455 x_mode IN VARCHAR2 ,
456 x_closed_ind IN VARCHAR2
457 ) AS
458 /*
459 || Created By : rgopalan
460 || Created On : 15-JUN-2001
461 || Purpose : Handles the UPDATE 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 x_last_update_date DATE ;
468 x_last_updated_by NUMBER;
469 x_last_update_login NUMBER;
470
471 BEGIN
472
473 x_last_update_date := SYSDATE;
474 IF (X_MODE = 'I') THEN
475 x_last_updated_by := 1;
476 x_last_update_login := 0;
477 ELSIF (x_mode = 'R') THEN
478 x_last_updated_by := fnd_global.user_id;
479 IF x_last_updated_by IS NULL THEN
480 x_last_updated_by := -1;
481 END IF;
482 x_last_update_login := fnd_global.login_id;
483 IF (x_last_update_login IS NULL) THEN
484 x_last_update_login := -1;
485 END IF;
486 ELSE
487 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
488 igs_ge_msg_stack.add;
489 app_exception.raise_exception;
490 END IF;
491
492 before_dml(
493 p_action => 'UPDATE',
494 x_rowid => x_rowid,
495 x_code_type => x_code_type,
496 x_value => x_value,
497 x_value_description => x_value_description,
498 x_creation_date => x_last_update_date,
499 x_created_by => x_last_updated_by,
500 x_last_update_date => x_last_update_date,
501 x_last_updated_by => x_last_updated_by,
502 x_last_update_login => x_last_update_login ,
503 x_closed_ind => x_closed_ind
504 );
505
506 UPDATE igs_he_code_values
507 SET
508 value_description = new_references.value_description,
509 last_update_date = x_last_update_date,
510 last_updated_by = x_last_updated_by,
511 last_update_login = x_last_update_login ,
512 closed_ind = new_references.closed_ind
513 WHERE rowid = x_rowid;
514
515 IF (SQL%NOTFOUND) THEN
516 RAISE NO_DATA_FOUND;
517 END IF;
518
519 END update_row;
520
521
522 PROCEDURE add_row (
523 x_rowid IN OUT NOCOPY VARCHAR2,
524 x_code_type IN VARCHAR2,
525 x_value IN VARCHAR2,
526 x_value_description IN VARCHAR2,
527 x_mode IN VARCHAR2 ,
528 x_closed_ind IN VARCHAR2
529 ) AS
530 /*
531 || Created By : rgopalan
532 || Created On : 15-JUN-2001
533 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
534 || Known limitations, enhancements or remarks :
535 || Change History :
536 || Who When What
537 || (reverse chronological order - newest change first)
538 */
539 CURSOR c1 IS
540 SELECT rowid
541 FROM igs_he_code_values
542 WHERE code_type = x_code_type
543 AND value = x_value;
544
545 BEGIN
546
547 OPEN c1;
548 FETCH c1 INTO x_rowid;
549 IF (c1%NOTFOUND) THEN
550 CLOSE c1;
551
552 insert_row (
553 x_rowid,
554 x_code_type,
555 x_value,
556 x_value_description,
557 x_mode ,
558 x_closed_ind
559 );
560 RETURN;
561 END IF;
562 CLOSE c1;
563
564 update_row (
565 x_rowid,
566 x_code_type,
567 x_value,
568 x_value_description,
569 x_mode ,
570 x_closed_ind
571 );
572
573 END add_row;
574
575
576 PROCEDURE delete_row (
577 x_rowid IN VARCHAR2
578 ) AS
579 /*
580 || Created By : rgopalan
581 || Created On : 15-JUN-2001
582 || Purpose : Handles the DELETE DML logic for the table.
583 || Known limitations, enhancements or remarks :
584 || Change History :
585 || Who When What
586 || (reverse chronological order - newest change first)
587 */
588 BEGIN
589
590 before_dml (
591 p_action => 'DELETE',
592 x_rowid => x_rowid
593 );
594
595 DELETE FROM igs_he_code_values
596 WHERE rowid = x_rowid;
597
598 IF (SQL%NOTFOUND) THEN
599 RAISE NO_DATA_FOUND;
600 END IF;
601
602 END delete_row;
603
604
605 END igs_he_code_values_pkg;
[Home] [Help]