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