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