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