[Home] [Help]
PACKAGE BODY: APPS.HR_QUA_AWARDS_UTIL_SS
Source
1 PACKAGE BODY hr_qua_awards_util_ss AS
2 /* $Header: hrquawrs.pkb 120.1.12000000.2 2007/04/19 09:28:16 arumukhe ship $*/
3
4
5 -- ---------------------------------------------------------------------------
6 -- delete_entire_qua
7 -- delete all qualification details, the attendance details for
8 -- the school at which the qualification was attained and any
9 -- subjects
10 -- ---------------------------------------------------------------------------
11
12 PROCEDURE delete_entire_qua
13 (p_validate in boolean
14 ,p_qualification_id in varchar2
15 ,p_pq_object_version_number in varchar2
16 ,p_attendance_id in varchar2
17 ,p_pea_object_version_number in varchar2
18 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
19 ) IS
20 --Bug#3236273
21
22 cursor subjects_for_qua(p_qualification_id in varchar2)is
23 select subjects_taken_id,object_version_number
24 from per_subjects_taken per
25 where per.qualification_id = p_qualification_id;
26
27 l_cursor_record subjects_for_qua%ROWTYPE;
28 l_proc varchar2(72) := g_package||'delete_entire_qua';
29
30 BEGIN
31
32 hr_utility.set_location('Entering:'||l_proc, 5);
33 savepoint delete_entire_qua;
34
35 --
36 -- delete all subjects
37 --
38
39 --Bug#3236273
40 hr_utility.set_location('Opening and Fetching subjects_for_qua :'||l_proc,10);
41 open subjects_for_qua(p_qualification_id => p_qualification_id);
42 fetch subjects_for_qua into l_cursor_record;
43 hr_utility.set_location('Entering while loop:'||l_proc,15);
44 WHILE subjects_for_qua%FOUND LOOP
45
46 --FOR i IN 1..NVL(p_qua_subjects.count,0) LOOP
47 IF l_cursor_record.subjects_taken_id IS NOT null THEN
48
49 per_sbt_del.del_tl
50 (p_subjects_taken_id => l_cursor_record.subjects_taken_id);
51 per_sub_del.del
52 (p_validate => p_validate
53 ,p_subjects_taken_id => l_cursor_record.subjects_taken_id
54 ,p_object_version_number => l_cursor_record.object_version_number);
55 END IF;
56 fetch subjects_for_qua into l_cursor_record;
57 END LOOP;
58 hr_utility.set_location('Exiting For Loop:'||l_proc,20);
59 close subjects_for_qua;
60
61 --
62 -- delete qualification
63 --
64 --per_qua_del.del
65 PER_QUALIFICATIONS_API.DELETE_QUALIFICATION
66 (p_validate => p_validate
67 ,p_qualification_id => p_qualification_id
68 ,p_object_version_number => p_pq_object_version_number);
69
70 --
71 -- delete attendance
72 --
73 BEGIN
74 IF p_attendance_id IS NOT null THEN
75 hr_utility.set_location('p_attendance_id IS NOT null:'||l_proc,30);
76 per_esa_del.del
77 (p_validate => p_validate
78 ,p_attendance_id => p_attendance_id
79 ,p_object_version_number => p_pea_object_version_number);
80 END IF;
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 hr_utility.set_location('Exception:Others'||l_proc,555);
85 null;
86 END;
87
88 IF p_validate = TRUE THEN
89 hr_utility.set_location('p_validate = TRUE:'||l_proc,35);
90 rollback to delete_entire_qua;
91 END IF;
92
93 hr_utility.set_location('Exiting:'||l_proc,25);
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 hr_utility.set_location('Exception:Others'||l_proc,555);
98 rollback to delete_entire_qua;
99 raise;
100
101 END delete_entire_qua;
102
103 -- ---------------------------------------------------------------------------
104 -- process_api
105 -- ---------------------------------------------------------------------------
106
107 PROCEDURE process_api
108 (p_validate in boolean
109 ,p_transaction_step_id in number
110 ,p_effective_date in varchar2 default null
111 ) is
112
113 l_mode varchar2(2000);
114
115 l_qualifications SSHR_QUA_TAB_TYP;
116 l_qua_subjects SSHR_QUA_SUBJECT_TAB_TYP;
117 l_qua_attendance SSHR_QUA_ATTENDANCE_TAB_TYP;
118 l_selected_person_id number;
119 l_proc varchar2(72) := g_package||'process_api';
120
121 BEGIN
122 --
123 -- get user date format
124 --
125 --get entire qualification data from transaction table
126 --
127 hr_utility.set_location('Entering:'||l_proc, 5);
128 get_entire_qua
129 (p_transaction_step_id => p_transaction_step_id
130 ,p_mode => l_mode
131 ,p_qualifications => l_qualifications
132 ,p_qua_subjects => l_qua_subjects
133 ,p_qua_attendance => l_qua_attendance);
134
135 l_selected_person_id := to_number(
136 hr_transaction_api.get_varchar2_value
137 (p_transaction_step_id => p_transaction_step_id
138 ,p_name => 'P_SELECTED_PERSON_ID'));
139
140 IF l_qualifications(1).delete_flag = 'Y' THEN
141 hr_utility.set_location('l_qualifications(1).delete_flag = Y:'||l_proc,10);
142 delete_entire_qua
143 (p_validate => p_validate
144 ,p_qualification_id => l_qualifications(1).qualification_id
145 ,p_pq_object_version_number =>
146 l_qualifications(1).object_version_number
147 ,p_attendance_id => l_qualifications(1).attendance_id
148 ,p_pea_object_version_number =>
149 l_qua_attendance(1).object_version_number
150 ,p_qua_subjects => l_qua_subjects);
151 ELSE
152 hr_utility.set_location('l_qualifications(1).delete_flag != Y:'||l_proc,15);
153 validate_api
154 (p_validate => p_validate
155 ,p_mode => l_mode
156 ,p_selected_person_id => l_selected_person_id
157 ,p_qualifications => l_qualifications
158 ,p_qua_subjects => l_qua_subjects
159 ,p_qua_attendance => l_qua_attendance);
160 END IF;
161
162 hr_utility.set_location('Exiting:'||l_proc, 20);
163 end process_api;
164 -- ---------------------------------------------------------------------------
165 -- field_changed
166 -- ---------------------------------------------------------------------------
167
168 FUNCTION field_changed(p_field1 in varchar2
169 ,p_field2 in varchar2)
170 RETURN BOOLEAN IS
171 l_proc varchar2(72) := g_package||'field_changed';
172 BEGIN
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 IF p_field1 IS null AND p_field2 IS null
175 OR
176 p_field1 IS NOT null AND p_field2 IS NOT null AND
177 p_field1 = p_field2 THEN
178 hr_utility.set_location('Return FALSE:'||l_proc, 10);
179 RETURN FALSE;
180 ELSE
181 --fnd_message.set_name('PER',p_field1||'-'||p_field2);
182 --hr_utility.raise_error;
183 hr_utility.set_location('Return TRUE:'||l_proc, 10);
184 RETURN TRUE;
185 END IF;
186
187 END field_changed;
188
189 -- ---------------------------------------------------------------------------
190 -- is_entire_qua_changed
191 -- ---------------------------------------------------------------------------
192 FUNCTION is_entire_qua_changed
193 (p_qualifications in SSHR_QUA_TAB_TYP
194 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
195 ,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
196 )
197
198 RETURN BOOLEAN IS
199
200
201 cursor csr_qualification(p_qualification_id in number) is
202 select *
203 from per_qualifications
204 where qualification_id=p_qualification_id;
205
206 cursor csr_attendance(p_attendance_id in number) is
207 select *
208 from per_establishment_attendances
209 where attendance_id = p_attendance_id;
210
211 cursor csr_subject(p_subject_taken_id in number) is
212 select *
213 from per_subjects_taken
214 where subjects_taken_id = p_subject_taken_id;
215
216 cursor csr_establishment(p_establishment_id in number) is
217 select name
218 from per_establishments
219 where establishment_id = p_establishment_id;
220
221 l_subject_count number;
222
223 l_changed boolean;
224
225 v_qualification csr_qualification%ROWTYPE;
226 v_attendance csr_attendance%ROWTYPE;
227 v_subject csr_subject%ROWTYPE;
228 v_establishment csr_establishment%ROWTYPE;
229 l_proc varchar2(72) := g_package||'is_entire_qua_changed';
230
231 BEGIN
232 --
233 --new qualification
234 --
235 hr_utility.set_location('Entering:'||l_proc, 5);
236 IF p_qualifications(1).qualification_id IS null THEN
237 hr_utility.set_location('qualification_id =null, RETURN TRUE:'||l_proc,10);
238 RETURN TRUE;
239 END IF;
240
241 --
242 --updated qualification
243 --
244 l_changed := FALSE;
245
246 OPEN csr_qualification(p_qualifications(1).qualification_id);
247 FETCH csr_qualification INTO v_qualification;
248 CLOSE csr_qualification;
249
250 IF (v_qualification.attendance_id is null and p_qua_attendance(1).establishment_id is not null)
251 THEN
252 hr_utility.set_location(' establishment change '||l_proc,10);
253 RETURN TRUE;
254 END IF;
255
256 --IF field_changed(v_qualification.party_id
257 -- ,p_qualifications(1).party_id) THEN
258 -- RETURN TRUE;
259 --END IF;
260
261 IF field_changed(v_qualification.title
262 ,p_qualifications(1).title) THEN
263 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,10);
264 RETURN TRUE;
265 END IF;
266
267 IF field_changed(to_char(v_qualification.start_date,FORMAT_RRRR_MM_DD)
268 ,to_char(p_qualifications(1).start_date,FORMAT_RRRR_MM_DD)) THEN
269 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,15);
270 RETURN TRUE;
271 END IF;
272
273 IF field_changed(to_char(v_qualification.end_date,FORMAT_RRRR_MM_DD)
274 ,to_char(p_qualifications(1).end_date,FORMAT_RRRR_MM_DD)) THEN
275 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,20);
276 RETURN TRUE;
277 END IF;
278
279 IF field_changed(v_qualification.status
280 ,p_qualifications(1).status) THEN
281 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,25);
282 RETURN TRUE;
283 END IF;
284
285 IF field_changed(v_qualification.attendance_id
286 ,p_qualifications(1).attendance_id) THEN
287 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,30);
288 RETURN TRUE;
289 END IF;
290
291 IF p_qualifications(1).attendance_id is not null THEN
292 OPEN csr_attendance(p_qualifications(1).attendance_id);
293 FETCH csr_attendance INTO v_attendance;
294 IF csr_attendance%NOTFOUND THEN
295 CLOSE csr_attendance;
296 hr_utility.set_location('csr_attendance%NOTFOUND, RETURN TRUE:'||l_proc,35);
297 RETURN TRUE;
298 END IF;
299 CLOSE csr_attendance;
300 END IF;
301
302 --check attendance
303 IF p_qualifications(1).attendance_id is not null THEN
304
305 IF field_changed(v_attendance.establishment_id
306 ,p_qua_attendance(1).establishment_id) THEN
307 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,40);
308 RETURN TRUE;
309 END IF;
310 IF v_attendance.establishment_id IS NOT NULL THEN
311 OPEN csr_establishment(v_attendance.establishment_id);
312 FETCH csr_establishment INTO v_establishment;
313 CLOSE csr_establishment;
314 IF field_changed(v_establishment.name
315 ,p_qua_attendance(1).establishment) THEN
316 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,45);
317 RETURN TRUE;
318 END IF;
319 ELSE
320 IF field_changed(v_attendance.establishment
321 ,p_qua_attendance(1).establishment) THEN
322 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,50);
323 RETURN TRUE;
324 END IF;
325 END IF;
326 IF field_changed(to_char(v_attendance.attended_start_date,
327 FORMAT_RRRR_MM_DD)
328 ,to_char(p_qua_attendance(1).attended_start_date,
329 FORMAT_RRRR_MM_DD)) THEN
330 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,55);
331 RETURN TRUE;
332 END IF;
333 IF field_changed(to_char(v_attendance.attended_end_date,
334 FORMAT_RRRR_MM_DD)
335 ,to_char(p_qua_attendance(1).attended_end_date,
336 FORMAT_RRRR_MM_DD)) THEN
337 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,60);
338 RETURN TRUE;
339 END IF;
340 IF field_changed(NVL(v_attendance.full_time,'N')
341 ,NVL(p_qua_attendance(1).full_time,'N')) THEN
342 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,65);
343 RETURN TRUE;
344 END IF;
345 IF field_changed(v_attendance.attribute_category
346 ,p_qua_attendance(1).attribute_category) THEN
347 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,70);
348 RETURN TRUE;
349 END IF;
350 IF field_changed(v_attendance.attribute1
351 ,p_qua_attendance(1).attribute1) THEN
352 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,75);
353 RETURN TRUE;
354 END IF;
355 IF field_changed(v_attendance.attribute2
356 ,p_qua_attendance(1).attribute2) THEN
357 hr_utility.set_location('if field_changed(..,..), RETURN TRUE:'||l_proc,85);
358 RETURN TRUE;
359 END IF;
360 IF field_changed(v_attendance.attribute3
361 ,p_qua_attendance(1).attribute3) THEN
362 RETURN TRUE;
363 END IF;
364 IF field_changed(v_attendance.attribute4
365 ,p_qua_attendance(1).attribute4) THEN
366 RETURN TRUE;
367 END IF;
368 IF field_changed(v_attendance.attribute5
369 ,p_qua_attendance(1).attribute5) THEN
370 RETURN TRUE;
371 END IF;
372 IF field_changed(v_attendance.attribute6
373 ,p_qua_attendance(1).attribute6) THEN
374 RETURN TRUE;
375 END IF;
376 IF field_changed(v_attendance.attribute7
377 ,p_qua_attendance(1).attribute7) THEN
378 RETURN TRUE;
379 END IF;
380 IF field_changed(v_attendance.attribute8
381 ,p_qua_attendance(1).attribute8) THEN
382 RETURN TRUE;
383 END IF;
384 IF field_changed(v_attendance.attribute9
385 ,p_qua_attendance(1).attribute9) THEN
386 RETURN TRUE;
387 END IF;
388 IF field_changed(v_attendance.attribute10
389 ,p_qua_attendance(1).attribute10) THEN
390 RETURN TRUE;
391 END IF;
392 IF field_changed(v_attendance.attribute11
393 ,p_qua_attendance(1).attribute11) THEN
394 RETURN TRUE;
395 END IF;
396 IF field_changed(v_attendance.attribute12
397 ,p_qua_attendance(1).attribute12) THEN
398 RETURN TRUE;
399 END IF;
400 IF field_changed(v_attendance.attribute13
401 ,p_qua_attendance(1).attribute13) THEN
402 RETURN TRUE;
403 END IF;
404 IF field_changed(v_attendance.attribute14
405 ,p_qua_attendance(1).attribute14) THEN
406 RETURN TRUE;
407 END IF;
408 IF field_changed(v_attendance.attribute15
409 ,p_qua_attendance(1).attribute15) THEN
410 RETURN TRUE;
411 END IF;
412 IF field_changed(v_attendance.attribute16
413 ,p_qua_attendance(1).attribute16) THEN
414 RETURN TRUE;
415 END IF;
416 IF field_changed(v_attendance.attribute17
417 ,p_qua_attendance(1).attribute17) THEN
418 RETURN TRUE;
419 END IF;
420 IF field_changed(v_attendance.attribute18
421 ,p_qua_attendance(1).attribute18) THEN
422 RETURN TRUE;
423 END IF;
424 IF field_changed(v_attendance.attribute19
425 ,p_qua_attendance(1).attribute19) THEN
426 RETURN TRUE;
427 END IF;
428 IF field_changed(v_attendance.attribute20
429 ,p_qua_attendance(1).attribute20) THEN
430 RETURN TRUE;
431 END IF;
432 END IF; --end check attendance
433
434 IF field_changed(v_qualification.attribute_category
435 ,p_qualifications(1).attribute_category) THEN
436 RETURN TRUE;
437 END IF;
438
439 IF field_changed(v_qualification.attribute1
440 ,p_qualifications(1).attribute1) THEN
441 RETURN TRUE;
442 END IF;
443
444 IF field_changed(v_qualification.attribute2
445 ,p_qualifications(1).attribute2) THEN
446 RETURN TRUE;
447 END IF;
448
449 IF field_changed(v_qualification.attribute3
450 ,p_qualifications(1).attribute3) THEN
451 RETURN TRUE;
452 END IF;
453
454 IF field_changed(v_qualification.attribute4
455 ,p_qualifications(1).attribute4) THEN
456 RETURN TRUE;
457 END IF;
458
459 IF field_changed(v_qualification.attribute5
460 ,p_qualifications(1).attribute5) THEN
461 RETURN TRUE;
462 END IF;
463
464 IF field_changed(v_qualification.attribute6
465 ,p_qualifications(1).attribute6) THEN
466 RETURN TRUE;
467 END IF;
468
469 IF field_changed(v_qualification.attribute7
470 ,p_qualifications(1).attribute7) THEN
471 RETURN TRUE;
472 END IF;
473
474 IF field_changed(v_qualification.attribute8
475 ,p_qualifications(1).attribute8) THEN
476 RETURN TRUE;
477 END IF;
478
479 IF field_changed(v_qualification.attribute9
480 ,p_qualifications(1).attribute9) THEN
481 RETURN TRUE;
482 END IF;
483
484 IF field_changed(v_qualification.attribute10
485 ,p_qualifications(1).attribute10) THEN
486 RETURN TRUE;
487 END IF;
488
489 IF field_changed(v_qualification.attribute11
490 ,p_qualifications(1).attribute11) THEN
491 RETURN TRUE;
492 END IF;
493
494 IF field_changed(v_qualification.attribute12
495 ,p_qualifications(1).attribute12) THEN
496 RETURN TRUE;
497 END IF;
498
499 IF field_changed(v_qualification.attribute13
500 ,p_qualifications(1).attribute13) THEN
501 RETURN TRUE;
502 END IF;
503
504 IF field_changed(v_qualification.attribute14
505 ,p_qualifications(1).attribute14) THEN
506 RETURN TRUE;
507 END IF;
508
509 IF field_changed(v_qualification.attribute15
510 ,p_qualifications(1).attribute15) THEN
511 RETURN TRUE;
512 END IF;
513
514 IF field_changed(v_qualification.attribute16
515 ,p_qualifications(1).attribute16) THEN
516 RETURN TRUE;
517 END IF;
518
519 IF field_changed(v_qualification.attribute17
520 ,p_qualifications(1).attribute17) THEN
521 RETURN TRUE;
522 END IF;
523
524 IF field_changed(v_qualification.attribute18
525 ,p_qualifications(1).attribute18) THEN
526 RETURN TRUE;
527 END IF;
528
529 IF field_changed(v_qualification.attribute19
530 ,p_qualifications(1).attribute19) THEN
531 RETURN TRUE;
532 END IF;
533
534 IF field_changed(v_qualification.attribute20
535 ,p_qualifications(1).attribute20) THEN
536 RETURN TRUE;
537 END IF;
538
539 --IF field_changed(v_qualification.qua_information_category
540 -- ,p_qualifications(1).qua_information_category) THEN
541 -- RETURN TRUE;
542 --END IF;
543
544 IF field_changed(v_qualification.qua_information1
545 ,p_qualifications(1).qua_information1) THEN
546 RETURN TRUE;
547 END IF;
548
549 IF field_changed(v_qualification.qua_information2
550 ,p_qualifications(1).qua_information2) THEN
551 RETURN TRUE;
552 END IF;
553 IF field_changed(v_qualification.qua_information3
554 ,p_qualifications(1).qua_information3) THEN
555 RETURN TRUE;
556 END IF;
557 IF field_changed(v_qualification.qua_information4
558 ,p_qualifications(1).qua_information4) THEN
559 RETURN TRUE;
560 END IF;
561 IF field_changed(v_qualification.qua_information5
562 ,p_qualifications(1).qua_information5) THEN
563 RETURN TRUE;
564 END IF;
565 IF field_changed(v_qualification.qua_information6
566 ,p_qualifications(1).qua_information6) THEN
567 RETURN TRUE;
568 END IF;
569 IF field_changed(v_qualification.qua_information7
570 ,p_qualifications(1).qua_information7) THEN
571 RETURN TRUE;
572 END IF;
573 IF field_changed(v_qualification.qua_information8
574 ,p_qualifications(1).qua_information8) THEN
575 RETURN TRUE;
576 END IF;
577 IF field_changed(v_qualification.qua_information9
578 ,p_qualifications(1).qua_information9) THEN
579 RETURN TRUE;
580 END IF;
581 IF field_changed(v_qualification.qua_information10
582 ,p_qualifications(1).qua_information10) THEN
583 RETURN TRUE;
584 END IF;
585 IF field_changed(v_qualification.qua_information11
586 ,p_qualifications(1).qua_information11) THEN
587 RETURN TRUE;
588 END IF;
589 IF field_changed(v_qualification.qua_information12
590 ,p_qualifications(1).qua_information12) THEN
591 RETURN TRUE;
592 END IF;
593 IF field_changed(v_qualification.qua_information13
594 ,p_qualifications(1).qua_information13) THEN
595 RETURN TRUE;
596 END IF;
597 IF field_changed(v_qualification.qua_information14
598 ,p_qualifications(1).qua_information14) THEN
599 RETURN TRUE;
600 END IF;
601 IF field_changed(v_qualification.qua_information15
602 ,p_qualifications(1).qua_information15) THEN
603 RETURN TRUE;
604 END IF;
605 IF field_changed(v_qualification.qua_information16
606 ,p_qualifications(1).qua_information16) THEN
607 RETURN TRUE;
608 END IF;
609 IF field_changed(v_qualification.qua_information17
610 ,p_qualifications(1).qua_information17) THEN
611 RETURN TRUE;
612 END IF;
613 IF field_changed(v_qualification.qua_information18
614 ,p_qualifications(1).qua_information18) THEN
615 RETURN TRUE;
616 END IF;
617 IF field_changed(v_qualification.qua_information19
618 ,p_qualifications(1).qua_information19) THEN
619 RETURN TRUE;
620 END IF;
621 IF field_changed(v_qualification.qua_information20
622 ,p_qualifications(1).qua_information20) THEN
623 RETURN TRUE;
624 END IF;
625
626 IF field_changed(v_qualification.grade_attained
627 ,p_qualifications(1).grade_attained) THEN
628 RETURN TRUE;
629 END IF;
630
631 IF field_changed(v_qualification.tuition_method
632 ,p_qualifications(1).tuition_method) THEN
633 RETURN TRUE;
634 END IF;
635
636 IF field_changed(v_qualification.fee
637 ,p_qualifications(1).fee) THEN
638 RETURN TRUE;
639 END IF;
640
641 IF field_changed(v_qualification.fee_currency
642 ,p_qualifications(1).fee_currency) THEN
643 RETURN TRUE;
644 END IF;
645
646 IF field_changed(v_qualification.reimbursement_arrangements
647 ,p_qualifications(1).reimbursement_arrangements) THEN
648 RETURN TRUE;
649 END IF;
650
651 IF field_changed(v_qualification.training_completed_amount
652 ,p_qualifications(1).training_completed_amount) THEN
653 RETURN TRUE;
654 END IF;
655
656 IF field_changed(v_qualification.training_completed_units
657 ,p_qualifications(1).training_completed_units) THEN
658 RETURN TRUE;
659 END IF;
660
661 IF field_changed(v_qualification.total_training_amount
662 ,p_qualifications(1).total_training_amount) THEN
663 RETURN TRUE;
664 END IF;
665
666 IF field_changed(v_qualification.awarding_body
667 ,p_qualifications(1).awarding_body) THEN
668 RETURN TRUE;
669 END IF;
670
671 IF field_changed(v_qualification.group_ranking
672 ,p_qualifications(1).group_ranking) THEN
673 RETURN TRUE;
674 END IF;
675
676 IF field_changed(to_char(v_qualification.awarded_date,
677 FORMAT_RRRR_MM_DD)
678 ,to_char(p_qualifications(1).awarded_date,
679 FORMAT_RRRR_MM_DD)
680 ) THEN
681 RETURN TRUE;
682 END IF;
683
684 IF field_changed(v_qualification.license_number
685 ,p_qualifications(1).license_number) THEN
686 RETURN TRUE;
687 END IF;
688
689 IF field_changed(v_qualification.license_restrictions
690 ,p_qualifications(1).license_restrictions) THEN
691 RETURN TRUE;
692 END IF;
693
694 IF field_changed(to_char(v_qualification.expiry_date,
695 FORMAT_RRRR_MM_DD)
696 ,to_char(p_qualifications(1).expiry_date,
697 FORMAT_RRRR_MM_DD)
698 ) THEN
699 RETURN TRUE;
700 END IF;
701
702 IF field_changed(v_qualification.comments
703 ,p_qualifications(1).comments) THEN
704 RETURN TRUE;
705 END IF;
706
707
708 l_subject_count := p_qua_subjects.count;
709
710 hr_utility.set_location('Entering For Loop:'||l_proc,100);
711 FOR i IN 1..l_subject_count LOOP
712 IF p_qua_subjects(i).subjects_taken_id IS null AND
713 p_qua_subjects(i).delete_flag = 'N' THEN
714 --a new subject is added
715 RETURN TRUE;
716 END IF;
717 IF p_qua_subjects(i).subjects_taken_id IS NOT null AND
718 p_qua_subjects(i).delete_flag = 'Y' THEN
719 --a subject is deleted
720 RETURN TRUE;
721 END IF;
722 IF p_qua_subjects(i).subjects_taken_id IS NOT null AND
723 p_qua_subjects(i).delete_flag = 'N' THEN
724 OPEN csr_subject(p_qua_subjects(i).subjects_taken_id);
725 FETCH csr_subject INTO v_subject;
726 IF csr_subject%NOTFOUND THEN
727 CLOSE csr_subject;
728 RETURN TRUE;
729 ELSE
730 CLOSE csr_subject;
731 END IF;
732 IF field_changed(v_subject.major
733 ,p_qua_subjects(i).major) THEN
734 RETURN TRUE;
735 END IF;
736 IF field_changed(to_char(v_subject.start_date,
737 FORMAT_RRRR_MM_DD)
738 ,to_char(p_qua_subjects(i).start_date,
739 FORMAT_RRRR_MM_DD)) THEN
740 RETURN TRUE;
741 END IF;
742 IF field_changed(to_char(v_subject.end_date,
743 FORMAT_RRRR_MM_DD)
744 ,to_char(p_qua_subjects(i).end_date,
745 FORMAT_RRRR_MM_DD)) THEN
746 RETURN TRUE;
747 END IF;
748 IF field_changed(v_subject.subject_status
749 ,p_qua_subjects(i).subject_status) THEN
750 RETURN TRUE;
751 END IF;
752 IF field_changed(v_subject.grade_attained
753 ,p_qua_subjects(i).grade_attained) THEN
754 RETURN TRUE;
755 END IF;
756 IF field_changed(v_subject.attribute_category
757 ,p_qua_subjects(i).attribute_category) THEN
758 RETURN TRUE;
759 END IF;
760 IF field_changed(v_subject.attribute1
761 ,p_qua_subjects(i).attribute1) THEN
762 RETURN TRUE;
763 END IF;
764 IF field_changed(v_subject.attribute2
765 ,p_qua_subjects(i).attribute2) THEN
766 RETURN TRUE;
767 END IF;
768 IF field_changed(v_subject.attribute3
769 ,p_qua_subjects(i).attribute3) THEN
770 RETURN TRUE;
771 END IF;
772 IF field_changed(v_subject.attribute4
773 ,p_qua_subjects(i).attribute4) THEN
774 RETURN TRUE;
775 END IF;
776 IF field_changed(v_subject.attribute5
777 ,p_qua_subjects(i).attribute5) THEN
778 RETURN TRUE;
779 END IF;
780 IF field_changed(v_subject.attribute6
781 ,p_qua_subjects(i).attribute6) THEN
782 RETURN TRUE;
783 END IF;
784 IF field_changed(v_subject.attribute7
785 ,p_qua_subjects(i).attribute7) THEN
786 RETURN TRUE;
787 END IF;
788 IF field_changed(v_subject.attribute8
789 ,p_qua_subjects(i).attribute8) THEN
790 RETURN TRUE;
791 END IF;
792 IF field_changed(v_subject.attribute9
793 ,p_qua_subjects(i).attribute9) THEN
794 RETURN TRUE;
795 END IF;
796 IF field_changed(v_subject.attribute10
797 ,p_qua_subjects(i).attribute10) THEN
798 RETURN TRUE;
799 END IF;
800 IF field_changed(v_subject.attribute11
801 ,p_qua_subjects(i).attribute11) THEN
802 RETURN TRUE;
803 END IF;
804 IF field_changed(v_subject.attribute12
805 ,p_qua_subjects(i).attribute12) THEN
806 RETURN TRUE;
807 END IF;
808 IF field_changed(v_subject.attribute13
809 ,p_qua_subjects(i).attribute13) THEN
810 RETURN TRUE;
811 END IF;
812 IF field_changed(v_subject.attribute14
813 ,p_qua_subjects(i).attribute14) THEN
814 RETURN TRUE;
815 END IF;
816 IF field_changed(v_subject.attribute15
817 ,p_qua_subjects(i).attribute15) THEN
818 RETURN TRUE;
819 END IF;
820 IF field_changed(v_subject.attribute16
821 ,p_qua_subjects(i).attribute16) THEN
822 RETURN TRUE;
823 END IF;
824 IF field_changed(v_subject.attribute17
825 ,p_qua_subjects(i).attribute17) THEN
826 RETURN TRUE;
827 END IF;
828 IF field_changed(v_subject.attribute18
829 ,p_qua_subjects(i).attribute18) THEN
830 RETURN TRUE;
831 END IF;
832 IF field_changed(v_subject.attribute19
833 ,p_qua_subjects(i).attribute19) THEN
834 RETURN TRUE;
835 END IF;
836 IF field_changed(v_subject.attribute20
837 ,p_qua_subjects(i).attribute20) THEN
838 RETURN TRUE;
839 END IF;
840
841 --IF field_changed(v_subject.sub_information_category
842 -- ,p_qua_subjects(i).sub_information_category) THEN
843 -- RETURN TRUE;
844 --END IF;
845 IF field_changed(v_subject.sub_information1
846 ,p_qua_subjects(i).sub_information1) THEN
847 RETURN TRUE;
848 END IF;
849 IF field_changed(v_subject.sub_information2
850 ,p_qua_subjects(i).sub_information2) THEN
851 RETURN TRUE;
852 END IF;
853 IF field_changed(v_subject.sub_information3
854 ,p_qua_subjects(i).sub_information3) THEN
855 RETURN TRUE;
856 END IF;
857 IF field_changed(v_subject.sub_information4
858 ,p_qua_subjects(i).sub_information4) THEN
859 RETURN TRUE;
860 END IF;
861 IF field_changed(v_subject.sub_information5
862 ,p_qua_subjects(i).sub_information5) THEN
863 RETURN TRUE;
864 END IF;
865 IF field_changed(v_subject.sub_information6
866 ,p_qua_subjects(i).sub_information6) THEN
867 RETURN TRUE;
868 END IF;
869 IF field_changed(v_subject.sub_information7
870 ,p_qua_subjects(i).sub_information7) THEN
871 RETURN TRUE;
872 END IF;
873 IF field_changed(v_subject.sub_information8
874 ,p_qua_subjects(i).sub_information8) THEN
875 RETURN TRUE;
876 END IF;
877 IF field_changed(v_subject.sub_information9
878 ,p_qua_subjects(i).sub_information9) THEN
879 RETURN TRUE;
880 END IF;
881 IF field_changed(v_subject.sub_information10
882 ,p_qua_subjects(i).sub_information10) THEN
883 RETURN TRUE;
884 END IF;
885 IF field_changed(v_subject.sub_information11
886 ,p_qua_subjects(i).sub_information11) THEN
887 RETURN TRUE;
888 END IF;
889 IF field_changed(v_subject.sub_information12
890 ,p_qua_subjects(i).sub_information12) THEN
891 RETURN TRUE;
892 END IF;
893 IF field_changed(v_subject.sub_information13
894 ,p_qua_subjects(i).sub_information13) THEN
895 RETURN TRUE;
896 END IF;
897 IF field_changed(v_subject.sub_information14
898 ,p_qua_subjects(i).sub_information14) THEN
899 RETURN TRUE;
900 END IF;
901 IF field_changed(v_subject.sub_information15
902 ,p_qua_subjects(i).sub_information15) THEN
903 RETURN TRUE;
904 END IF;
905 IF field_changed(v_subject.sub_information16
906 ,p_qua_subjects(i).sub_information16) THEN
907 RETURN TRUE;
908 END IF;
909 IF field_changed(v_subject.sub_information17
910 ,p_qua_subjects(i).sub_information17) THEN
911 RETURN TRUE;
912 END IF;
913 IF field_changed(v_subject.sub_information18
914 ,p_qua_subjects(i).sub_information18) THEN
915 RETURN TRUE;
916 END IF;
917 IF field_changed(v_subject.sub_information19
918 ,p_qua_subjects(i).sub_information19) THEN
919 RETURN TRUE;
920 END IF;
921 IF field_changed(v_subject.sub_information20
922 ,p_qua_subjects(i).sub_information20) THEN
923 RETURN TRUE;
924 END IF;
925 END IF;
926 END LOOP;
927 hr_utility.set_location('Exiting For Loop:'||l_proc,105);
928 hr_utility.set_location('Exiting:'||l_proc, 110);
929
930 RETURN FALSE;
931
932 END is_entire_qua_changed;
933
934 procedure delete_transaction_step
935 (p_item_type in varchar2
936 ,p_item_key in varchar2
937 ,p_creator_person_id in number) is
938
939 cursor c_get_transaction_step_id(p_transaction_id number) is
940 select transaction_step_id
941 from hr_api_transaction_steps
942 where transaction_id = p_transaction_id;
943
944 l_transaction_id hr_api_transactions.transaction_id%type;
945 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
946 l_proc varchar2(72) := g_package||'delete_transaction_step';
947
948 begin
949
950 hr_utility.set_location('Entering:'||l_proc, 5);
951 l_transaction_id := hr_transaction_ss.get_transaction_id
952 (p_item_type => p_item_type
953 ,p_item_key => p_item_key);
954 if l_transaction_id is not null then
955 hr_utility.set_location('l_transaction_id is not null:'||l_proc,10);
956 open c_get_transaction_step_id(l_transaction_id);
957 fetch c_get_transaction_step_id into l_transaction_step_id;
958 close c_get_transaction_step_id;
959 if l_transaction_step_id is not null then
960 hr_utility.set_location('if l_transaction_step_id is not null:'||l_proc,15);
961 hr_transaction_ss.delete_transaction_step
962 (l_transaction_step_id,null,p_creator_person_id);
963 end if;
964 end if;
965
966 hr_utility.set_location('Exiting:'||l_proc, 20);
967 end delete_transaction_step;
968
969
970 -- start of function decode_value
971
972 function decode_value (p_expression in boolean,
973 p_true in varchar2,
974 p_false in varchar2)
975 return varchar2 is
976 l_proc varchar2(72) := g_package||'decode_value';
977 Begin
978 hr_utility.set_location('Entering:'||l_proc, 5);
979 if p_expression then
980 hr_utility.set_location('if p_expression= true:'||l_proc,10);
981 return p_true;
982 else
983 hr_utility.set_location('if p_expression=false:'||l_proc,15);
984 return p_false;
985 end if;
986 Exception
987 when others then
988 hr_utility.set_location('Exception:Others'||l_proc,555);
989 rollback;
990 raise;
991 end decode_value;
992
993 -- end of function decode_value
994
995 -- start of function get_qualification_type
996
997 /*
998 This method returns the qualification type give the qualification type id
999 */
1000
1001 function get_qualification_type(p_qualification_type_id in number)
1002 return varchar2 is
1003
1004 CURSOR csr_qua_type IS
1005 SELECT
1006 name
1007 FROM
1008 per_qualification_types
1009 WHERE qualification_type_id = p_qualification_type_id;
1010
1011 l_name per_qualification_types.name%TYPE;
1012 l_proc varchar2(72) := g_package||'get_qualification_type';
1013
1014 BEGIN
1015
1016 hr_utility.set_location('Entering:'||l_proc, 5);
1017 OPEN csr_qua_type;
1018 FETCH csr_qua_type INTO l_name;
1019 CLOSE csr_qua_type;
1020
1021 hr_utility.set_location('Exiting:'||l_proc, 10);
1022 RETURN l_name;
1023
1024 Exception
1025 when others then
1026 hr_utility.set_location('Exception:Others'||l_proc,555);
1027 rollback;
1028 raise;
1029 END get_qualification_type;
1030
1031 -- end of function get_qualification_type
1032
1033 -- start of procedure get_pending_transaction_ids
1034
1035 /*
1036 This method returns the list of Qualifications or Awards that are pending approval for a person. All
1037 the values are concatenated and passed as out nocopy params to the java code. The column separator is
1038 '^' and the row separator is '?'
1039 */
1040 procedure get_pending_transaction_ids
1041 (p_item_type in varchar2
1042 ,p_selected_person_id in varchar2
1043 ,p_mode in varchar2
1044 ,p_process_name in varchar2
1045 ,p_activity_name in varchar2
1046 ,p_qualifications out nocopy SSHR_QUA_TAB_TYP
1047 ,p_qua_attendance out nocopy SSHR_QUA_ATTENDANCE_TAB_TYP
1048 ,p_transaction_steps out nocopy SSHR_TRN_TAB_TYP
1049 )
1050 IS
1051
1052 l_transaction_step_id hr_util_misc_web.g_varchar2_tab_type;
1053 l_total_rec_count number := 0;
1054
1055 l_mode varchar2(2000);
1056
1057 l_qualifications SSHR_QUA_TAB_TYP;
1058 l_qua_subjects SSHR_QUA_SUBJECT_TAB_TYP;
1059 l_qua_attendance SSHR_QUA_ATTENDANCE_TAB_TYP;
1060 l_selected_person_id number;
1061 l_proc varchar2(72) := g_package||'get_pending_transaction_ids';
1062
1063 BEGIN
1064
1065 hr_utility.set_location('Entering:'||l_proc, 5);
1066 hr_qua_awards_util_ss.get_pending_transaction_steps
1067 (p_item_type => p_item_type
1068 ,p_selected_person_id => p_selected_person_id
1069 ,p_mode => p_mode
1070 ,p_process_name => p_process_name
1071 ,p_activity_name => p_activity_name
1072 ,p_transaction_step_id => l_transaction_step_id);
1073
1074 l_total_rec_count := nvl(l_transaction_step_id.COUNT,0);
1075
1076 p_qualifications := SSHR_QUA_TAB_TYP();
1077 p_qua_attendance := SSHR_QUA_ATTENDANCE_TAB_TYP();
1078 p_transaction_steps := SSHR_TRN_TAB_TYP();
1079
1080 hr_utility.set_location('Entering For Loop:'||l_proc,10);
1081 FOR i IN 1..l_total_rec_count LOOP
1082 if l_transaction_step_id(i) is not null then
1083 --
1084 --get entire qualification data from transaction table
1085 --
1086 get_entire_qua
1087 (p_transaction_step_id => l_transaction_step_id(i)
1088 ,p_mode => l_mode
1089 ,p_qualifications => l_qualifications
1090 ,p_qua_subjects => l_qua_subjects
1091 ,p_qua_attendance => l_qua_attendance);
1092
1093 p_qua_attendance.extend;
1094 p_qua_attendance(i) := l_qua_attendance(1);
1095 p_qualifications.extend;
1096 p_qualifications(i) := l_qualifications(1);
1097
1098 p_transaction_steps.extend;
1099 p_transaction_steps(i) := SSHR_TRN_OBJ_TYP(
1100 l_transaction_step_id(i));
1101
1102 end if;
1103
1104 END LOOP;
1105 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
1106 hr_utility.set_location('Exiting:'||l_proc, 20);
1107 Exception
1108
1109 when others then
1110 hr_utility.set_location('Exception:Others'||l_proc,555);
1111 rollback;
1112 raise;
1113 END get_pending_transaction_ids;
1114
1115 -- end of procedure get_pending_transaction_ids
1116
1117
1118 -- start of function is_qualification_in_pending
1119
1120 /*
1121 This method returns whether a given qualification id is pending approval or not. This function
1122 is called from java code.
1123 */
1124 function is_qualification_in_pending
1125
1126 (
1127 p_item_type in varchar2
1128 ,p_selected_person_id in varchar2
1129 ,p_mode in varchar2
1130 ,p_process_name in varchar2
1131 ,p_activity_name in varchar2
1132 ,p_qualification_id in number
1133 )
1134
1135 return varchar2 is
1136
1137 l_pending_found varchar2(1);
1138 l_transaction_step_id hr_util_misc_web.g_varchar2_tab_type;
1139 l_qualification_id number;
1140 l_proc varchar2(72) := g_package||'is_qualification_in_pending';
1141
1142 Begin
1143
1144 hr_utility.set_location('Entering:'||l_proc, 5);
1145 get_pending_transaction_steps
1146 (p_item_type => p_item_type
1147 ,p_selected_person_id => p_selected_person_id
1148 ,p_mode => p_mode
1149 ,p_process_name => p_process_name
1150 ,p_activity_name => p_activity_name
1151 ,p_transaction_step_id => l_transaction_step_id);
1152
1153 l_pending_found := 'N';
1154
1155 hr_utility.set_location('Entering For Loop:'||l_proc,10);
1156 FOR i IN 1..NVL(l_transaction_step_id.count,0) LOOP
1157 l_qualification_id :=
1158 hr_transaction_api.get_varchar2_value
1159 (p_transaction_step_id => l_transaction_step_id(i)
1160 ,p_name => 'P_QUALIFICATION_ID');
1161 IF l_qualification_id = p_qualification_id THEN
1162 l_pending_found := 'Y';
1163 EXIT;
1164 END IF;
1165 END LOOP;
1166 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
1167
1168 hr_utility.set_location('Exiting:'||l_proc, 15);
1169 RETURN l_pending_found;
1170
1171 Exception
1172 when others then
1173 hr_utility.set_location('Exception:Others'||l_proc,555);
1174 raise;
1175 end is_qualification_in_pending;
1176
1177 -- end of function is_qualification_in_pending
1178
1179 -- Start of Procedure validate_qualification
1180 /*
1181 This method is being called from the java code when creating/editing a qualification or an award.
1182 */
1183 PROCEDURE validate_qualification
1184 (p_validate in VARCHAR2
1185 ,p_save_mode in varchar2
1186 ,p_mode in varchar2
1187 ,p_creator_person_id in number
1188 ,p_selected_person_id in number
1189 ,p_item_type in varchar2
1190 ,p_item_key in varchar2
1191 ,p_act_id in varchar2
1192 ,p_proc_call in varchar2
1193 ,p_error_message in out nocopy varchar2
1194 ,p_subjects_error_message in out nocopy varchar2
1195 ,p_qualifications in SSHR_QUA_TAB_TYP
1196 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
1197 ,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP) is
1198
1199 l_app_exception exception;
1200
1201 l_transaction_step_id number;
1202 l_validate boolean := true;
1203 l_changed boolean := false;
1204 l_subject_count number := 0;
1205 l_error_message varchar2(3000);
1206 l_proc varchar2(72) := g_package||'validate_qualification';
1207
1208 -- start of begin for validate_qualification
1209
1210 Begin
1211
1212 hr_utility.set_location('Entering:'||l_proc, 5);
1213 if p_validate = 'Y' then
1214 hr_utility.set_location('p_validate = N:'||l_proc,10);
1215 l_validate := true;
1216 else
1217 l_validate := false;
1218 end if;
1219
1220 --check if there are any changes
1221 if p_qualifications(1).delete_flag is null or
1222 p_qualifications(1).delete_flag = 'N' then
1223 hr_utility.set_location('delete_flag = N:'||l_proc,15);
1224 l_changed := is_entire_qua_changed
1225 (p_qualifications => p_qualifications
1226 ,p_qua_subjects => p_qua_subjects
1227 ,p_qua_attendance => p_qua_attendance);
1228 IF l_changed = FALSE THEN
1229 hr_utility.set_location('l_changed = FALSE THEN:'||l_proc,25);
1230 delete_transaction_step
1231 (p_item_type => p_item_type
1232 ,p_item_key => p_item_key
1233 ,p_creator_person_id => p_creator_person_id);
1234 END IF;
1235 end if;
1236
1237 if l_changed = TRUE THEN
1238 hr_utility.set_location('l_changed = TRUE THEN:'||l_proc,30);
1239 if (p_qualifications(1).delete_flag is null or
1240 p_qualifications(1).delete_flag = 'N')
1241 and p_save_mode <> 'SAVE_FOR_LATER'
1242 then
1243 hr_utility.set_location('Delete flag = N and not SFL'||l_proc,35);
1244 hr_qua_awards_util_ss.check_errors
1245 (p_ignore_sub_date_boundaries => 'N'
1246 ,p_mode => p_mode
1247 ,p_qualifications => p_qualifications
1248 ,p_qua_subjects => p_qua_subjects
1249 ,p_qua_attendance => p_qua_attendance
1250 ,p_error_message => p_error_message
1251 ,p_subjects_error_message => p_subjects_error_message);
1252
1253 if nvl(length(p_error_message),0) > 0 or
1254 nvl(length(p_subjects_error_message),0) > 0 then
1255 hr_utility.set_location('Exception:l_app_exception'||l_proc,555);
1256 raise l_app_exception;
1257 end if;
1258 end if;
1259
1260 --should not do validation if user wants to delete the current qualification.
1261 if (p_qualifications(1).delete_flag is null or
1262 p_qualifications(1).delete_flag = 'N') and
1263 p_save_mode <> 'SAVE_FOR_LATER' then
1264 hr_utility.set_location('Delete flag = N and not SFL'||l_proc,40);
1265 hr_qua_awards_util_ss.validate_api
1266 (p_validate => l_validate
1267 ,p_mode => p_mode
1268 ,p_selected_person_id => p_selected_person_id
1269 ,p_qualifications => p_qualifications
1270 ,p_qua_subjects => p_qua_subjects
1271 ,p_qua_attendance => p_qua_attendance
1272 );
1273 end if;
1274 end if; --l_changed
1275
1276 if l_changed = TRUE or p_qualifications(1).delete_flag = 'Y' THEN
1277
1278 hr_utility.set_location('Delete flag = Y or changed=True'||l_proc,40);
1279 hr_qua_awards_util_ss.save_transaction_step
1280 (p_item_type => p_item_type
1281 ,p_item_key => p_item_key
1282 ,p_actid => to_number(p_act_id)
1283 ,p_transaction_step_id => l_transaction_step_id
1284 ,p_mode => p_mode
1285 ,p_creator_person_id => p_creator_person_id
1286 ,p_selected_person_id => p_selected_person_id
1287 ,p_qualifications => p_qualifications
1288 ,p_qua_subjects => p_qua_subjects
1289 ,p_qua_attendance => p_qua_attendance
1290 ,p_proc_call => p_proc_call);
1291 end if;
1292
1293 Exception
1294 when l_app_exception then
1295 hr_utility.set_location('Exception:l_app_exception'||l_proc,555);
1296 rollback;
1297 -- Fix bug 2899882.
1298 -- should directly raise error. Otherwise the tokens in the messages are
1299 -- lost.
1300 --when hr_utility.hr_error then
1301 -- hr_message.provide_error;
1302 -- p_error_message := nvl(p_error_message,'')||'!'||'|'||'PAGE'||'|'||
1303 -- hr_message.last_message_app||'|'||hr_message.last_message_name||
1304 -- '|'||'!';
1305 -- rollback;
1306 when others then
1307 hr_utility.set_location('Exception:Others'||l_proc,555);
1308 rollback;
1309 -- Bug Fix 3103716
1310 l_error_message := hr_utility.get_message;
1311 IF l_error_message is null THEN
1312 raise;
1313 ELSE
1314 p_error_message := nvl(p_error_message,'')||'!'||'|'||'PAGE'||'|'||'PER'||'|'|| l_error_message ||'|_ValidateAPIError|'||'!';
1315 END IF;
1316 hr_utility.set_location('Exiting:'||l_proc, 45);
1317 End; -- end of validate_qualification
1318
1319 -- end of procedure validate_qualification
1320
1321 -- start of procedure save_transaction_step
1322
1323 PROCEDURE save_transaction_step
1324 (p_item_type in varchar2
1325 ,p_item_key in varchar2
1326 ,p_actid in number
1327 ,p_transaction_step_id in out nocopy number
1328 ,p_mode in varchar2
1329 ,p_creator_person_id in number
1330 ,p_selected_person_id in number
1331 ,p_qualifications in SSHR_QUA_TAB_TYP
1332 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
1333 ,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
1334 ,p_proc_call in varchar2 ) IS
1335
1336 l_result VARCHAR2(100);
1337 l_transaction_id number;
1338 l_trn_object_version_number hr_api_transaction_steps.object_version_number%TYPE;
1339 l_subject_count number;
1340 l_proc varchar2(72) := g_package||'save_transaction_step';
1341
1342 BEGIN
1343
1344
1345 hr_utility.set_location('Entering:'||l_proc, 5);
1346 hr_qua_awards_util_ss.start_transaction
1347 (itemtype => p_item_type
1348 ,itemkey => p_item_key
1349 ,actid => p_actid
1350 ,funmode => 'RUN'
1351 ,p_selected_person_id => p_qualifications(1).person_id
1352 ,p_creator_person_id => p_creator_person_id
1353 ,result => l_result);
1354
1355 l_transaction_id := hr_transaction_ss.get_transaction_id
1356 (p_item_type => p_item_type
1357 ,p_item_key => p_item_key);
1358
1359 --create a transaction step for this transaction.
1360
1361 hr_transaction_api.create_transaction_step
1362 (p_validate => FALSE
1363 ,p_creator_person_id => p_creator_person_id
1364 ,p_transaction_id => l_transaction_id
1365 ,p_api_name => HR_QUA_AWARDS_UTIL_SS.API_NAME
1366 ,p_item_type => p_item_type
1367 ,p_item_key => p_item_key
1368 ,p_transaction_step_id => p_transaction_step_id
1369 ,p_object_version_number => l_trn_object_version_number);
1370
1371
1372 --save to transaction table
1373
1374 hr_transaction_api.set_varchar2_value
1375 (p_transaction_step_id => p_transaction_step_id
1376 ,p_person_id => p_creator_person_id
1377 ,p_name => 'p_mode'
1378 ,p_value => p_mode);
1379
1380 hr_transaction_api.set_varchar2_value
1381 (p_transaction_step_id => p_transaction_step_id
1382 ,p_person_id => p_creator_person_id
1383 ,p_name => 'P_SELECTED_PERSON_ID'
1384 ,p_value => p_selected_person_id);
1385
1386 hr_transaction_api.set_varchar2_value
1387 (p_transaction_step_id => p_transaction_step_id
1388 ,p_person_id => p_creator_person_id
1389 ,p_name => 'P_PQ_PERSON_ID'
1390 ,p_value => p_qualifications(1).person_id);
1391
1392 hr_transaction_api.set_varchar2_value
1393 (p_transaction_step_id => p_transaction_step_id
1394 ,p_person_id => p_creator_person_id
1395 ,p_name => 'P_BUSINESS_GROUP_ID'
1396 ,p_value => p_qualifications(1).business_group_id);
1397
1398
1399 hr_transaction_api.set_varchar2_value
1400 (p_transaction_step_id => p_transaction_step_id
1401 ,p_person_id => p_creator_person_id
1402 ,p_name => 'P_DELETE_FLAG'
1403 ,p_value => p_qualifications(1).delete_flag);
1404
1405 hr_transaction_api.set_varchar2_value
1406 (p_transaction_step_id => p_transaction_step_id
1407 ,p_person_id => p_creator_person_id
1408 ,p_name => 'P_QUALIFICATION_ID'
1409 ,p_value => p_qualifications(1).qualification_id);
1410
1411 hr_transaction_api.set_varchar2_value
1412 (p_transaction_step_id => p_transaction_step_id
1413 ,p_person_id => p_creator_person_id
1414 ,p_name => 'P_QUALIFICATION_TYPE_ID'
1415 ,p_value => p_qualifications(1).qualification_type_id);
1416
1417 hr_transaction_api.set_varchar2_value
1418 (p_transaction_step_id => p_transaction_step_id
1419 ,p_person_id => p_creator_person_id
1420 ,p_name => 'P_PQ_OBJECT_VERSION_NUMBER'
1421 ,p_value => p_qualifications(1).object_version_number);
1422
1423 hr_transaction_api.set_varchar2_value
1424 (p_transaction_step_id => p_transaction_step_id
1425 ,p_person_id => p_creator_person_id
1426 ,p_name => 'P_PQ_TITLE'
1427 ,p_value => p_qualifications(1).title);
1428
1429 hr_transaction_api.set_date_value
1430 (p_transaction_step_id => p_transaction_step_id
1431 ,p_person_id => p_creator_person_id
1432 ,p_name => 'P_PQ_START_DATE'
1433 ,p_value => p_qualifications(1).start_date);
1434
1435 hr_transaction_api.set_date_value
1436 (p_transaction_step_id => p_transaction_step_id
1437 ,p_person_id => p_creator_person_id
1438 ,p_name => 'P_PQ_END_DATE'
1439 ,p_value => p_qualifications(1).end_date);
1440
1441 hr_transaction_api.set_varchar2_value
1442 (p_transaction_step_id => p_transaction_step_id
1443 ,p_person_id => p_creator_person_id
1444 ,p_name => 'P_PQ_STATUS'
1445 ,p_value => p_qualifications(1).status);
1446
1447 hr_transaction_api.set_varchar2_value
1448 (p_transaction_step_id => p_transaction_step_id
1449 ,p_person_id => p_creator_person_id
1450 ,p_name => 'P_PQ_GRADE_ATTAINED'
1451 ,p_value => p_qualifications(1).grade_attained);
1452
1453 hr_transaction_api.set_varchar2_value
1454 (p_transaction_step_id => p_transaction_step_id
1455 ,p_person_id => p_creator_person_id
1456 ,p_name => 'P_PQ_TUITION_METHOD'
1457 ,p_value => p_qualifications(1).tuition_method);
1458
1459 hr_transaction_api.set_varchar2_value
1460 (p_transaction_step_id => p_transaction_step_id
1461 ,p_person_id => p_creator_person_id
1462 ,p_name => 'P_PQ_FEE'
1463 ,p_value => p_qualifications(1).fee);
1464
1465 hr_transaction_api.set_varchar2_value
1466 (p_transaction_step_id => p_transaction_step_id
1467 ,p_person_id => p_creator_person_id
1468 ,p_name => 'P_PQ_FEE_CURRENCY'
1469 ,p_value => p_qualifications(1).fee_currency);
1470
1471 hr_transaction_api.set_varchar2_value
1472 (p_transaction_step_id => p_transaction_step_id
1473 ,p_person_id => p_creator_person_id
1474 ,p_name => 'P_PQ_REIMBURSEMENT'
1475 ,p_value => p_qualifications(1).reimbursement_arrangements);
1476
1477 hr_transaction_api.set_varchar2_value
1478 (p_transaction_step_id => p_transaction_step_id
1479 ,p_person_id => p_creator_person_id
1480 ,p_name => 'P_PQ_COMPLETED_AMOUNT'
1481 ,p_value => p_qualifications(1).training_completed_amount);
1482
1483 hr_transaction_api.set_varchar2_value
1484 (p_transaction_step_id => p_transaction_step_id
1485 ,p_person_id => p_creator_person_id
1486 ,p_name => 'P_PQ_COMPLETED_UNITS'
1487 ,p_value => p_qualifications(1).training_completed_units);
1488
1489 hr_transaction_api.set_varchar2_value
1490 (p_transaction_step_id => p_transaction_step_id
1491 ,p_person_id => p_creator_person_id
1492 ,p_name => 'P_PQ_TOTAL_AMOUNT'
1493 ,p_value => p_qualifications(1).total_training_amount);
1494
1495 hr_transaction_api.set_varchar2_value
1496 (p_transaction_step_id => p_transaction_step_id
1497 ,p_person_id => p_creator_person_id
1498 ,p_name => 'P_PQ_COMMENTS'
1499 ,p_value => p_qualifications(1).comments);
1500
1501 hr_transaction_api.set_varchar2_value
1502 (p_transaction_step_id => p_transaction_step_id
1503 ,p_person_id => p_creator_person_id
1504 ,p_name => 'P_PQ_AWARDING_BODY'
1505 ,p_value => p_qualifications(1).awarding_body);
1506
1507 hr_transaction_api.set_varchar2_value
1508 (p_transaction_step_id => p_transaction_step_id
1509 ,p_person_id => p_creator_person_id
1510 ,p_name => 'P_PQ_GROUP_RANKING'
1511 ,p_value => p_qualifications(1).group_ranking);
1512
1513 hr_transaction_api.set_date_value
1514 (p_transaction_step_id => p_transaction_step_id
1515 ,p_person_id => p_creator_person_id
1516 ,p_name => 'P_PQ_AWARDED_DATE'
1517 ,p_value => p_qualifications(1).awarded_date);
1518
1519 hr_transaction_api.set_varchar2_value
1520 (p_transaction_step_id => p_transaction_step_id
1521 ,p_person_id => p_creator_person_id
1522 ,p_name => 'P_PQ_LICENSE_NUMBER'
1523 ,p_value => p_qualifications(1).license_number);
1524
1525 hr_transaction_api.set_date_value
1526 (p_transaction_step_id => p_transaction_step_id
1527 ,p_person_id => p_creator_person_id
1528 ,p_name => 'P_PQ_EXPIRY_DATE'
1529 ,p_value => p_qualifications(1).expiry_date);
1530
1531 hr_transaction_api.set_varchar2_value
1532 (p_transaction_step_id => p_transaction_step_id
1533 ,p_person_id => p_creator_person_id
1534 ,p_name => 'P_PQ_LICENSE_RESTRICTIONS'
1535 ,p_value => p_qualifications(1).license_restrictions);
1536
1537 hr_transaction_api.set_varchar2_value
1538 (p_transaction_step_id => p_transaction_step_id
1539 ,p_person_id => p_creator_person_id
1540 ,p_name => 'P_PEA_PERSON_ID'
1541 ,p_value => p_qua_attendance(1).person_id);
1542
1543 hr_transaction_api.set_varchar2_value
1544 (p_transaction_step_id => p_transaction_step_id
1545 ,p_person_id => p_creator_person_id
1546 ,p_name => 'P_ESTABLISHMENT_ID'
1547 ,p_value => p_qua_attendance(1).establishment_id);
1548
1549 hr_transaction_api.set_varchar2_value
1550 (p_transaction_step_id => p_transaction_step_id
1551 ,p_person_id => p_creator_person_id
1552 ,p_name => 'P_PE_NAME'
1553 ,p_value => p_qua_attendance(1).establishment);
1554
1555 hr_transaction_api.set_varchar2_value
1556 (p_transaction_step_id => p_transaction_step_id
1557 ,p_person_id => p_creator_person_id
1558 ,p_name => 'P_ATTENDANCE_ID'
1559 ,p_value => p_qualifications(1).attendance_id);
1560
1561 hr_transaction_api.set_varchar2_value
1562 (p_transaction_step_id => p_transaction_step_id
1563 ,p_person_id => p_creator_person_id
1564 ,p_name => 'P_PEA_OBJECT_VERSION_NUMBER'
1565 ,p_value => p_qua_attendance(1).object_version_number);
1566
1567 hr_transaction_api.set_date_value
1568 (p_transaction_step_id => p_transaction_step_id
1569 ,p_person_id => p_creator_person_id
1570 ,p_name => 'P_PEA_START_DATE'
1571 ,p_value => p_qua_attendance(1).attended_start_date);
1572
1573 hr_transaction_api.set_date_value
1574 (p_transaction_step_id => p_transaction_step_id
1575 ,p_person_id => p_creator_person_id
1576 ,p_name => 'P_PEA_END_DATE'
1577 ,p_value => p_qua_attendance(1).attended_end_date);
1578
1579 hr_transaction_api.set_varchar2_value
1580 (p_transaction_step_id => p_transaction_step_id
1581 ,p_person_id => p_creator_person_id
1582 ,p_name => 'P_PEA_FULL_TIME'
1583 ,p_value => p_qua_attendance(1).full_time);
1584
1585 hr_transaction_api.set_varchar2_value
1586 (p_transaction_step_id => p_transaction_step_id
1587 ,p_person_id => p_creator_person_id
1588 ,p_name => 'P_PQ_ATTRIBUTE_CATEGORY'
1589 ,p_value => p_qualifications(1).attribute_category);
1590
1591 hr_transaction_api.set_varchar2_value
1592 (p_transaction_step_id => p_transaction_step_id
1593 ,p_person_id => p_creator_person_id
1594 ,p_name => 'P_PQ_ATTRIBUTE1'
1595 ,p_value => p_qualifications(1).attribute1);
1596
1597 hr_transaction_api.set_varchar2_value
1598 (p_transaction_step_id => p_transaction_step_id
1599 ,p_person_id => p_creator_person_id
1600 ,p_name => 'P_PQ_ATTRIBUTE2'
1601 ,p_value => p_qualifications(1).attribute2);
1602
1603 hr_transaction_api.set_varchar2_value
1604 (p_transaction_step_id => p_transaction_step_id
1605 ,p_person_id => p_creator_person_id
1606 ,p_name => 'P_PQ_ATTRIBUTE3'
1607 ,p_value => p_qualifications(1).attribute3);
1608
1609 hr_transaction_api.set_varchar2_value
1610 (p_transaction_step_id => p_transaction_step_id
1611 ,p_person_id => p_creator_person_id
1612 ,p_name => 'P_PQ_ATTRIBUTE4'
1613 ,p_value => p_qualifications(1).attribute4);
1614
1615 hr_transaction_api.set_varchar2_value
1616 (p_transaction_step_id => p_transaction_step_id
1617 ,p_person_id => p_creator_person_id
1618 ,p_name => 'P_PQ_ATTRIBUTE5'
1619 ,p_value => p_qualifications(1).attribute5);
1620
1621 hr_transaction_api.set_varchar2_value
1622 (p_transaction_step_id => p_transaction_step_id
1623 ,p_person_id => p_creator_person_id
1624 ,p_name => 'P_PQ_ATTRIBUTE6'
1625 ,p_value => p_qualifications(1).attribute6);
1626
1627 hr_transaction_api.set_varchar2_value
1628 (p_transaction_step_id => p_transaction_step_id
1629 ,p_person_id => p_creator_person_id
1630 ,p_name => 'P_PQ_ATTRIBUTE7'
1631 ,p_value => p_qualifications(1).attribute7);
1632
1633 hr_transaction_api.set_varchar2_value
1634 (p_transaction_step_id => p_transaction_step_id
1635 ,p_person_id => p_creator_person_id
1636 ,p_name => 'P_PQ_ATTRIBUTE8'
1637 ,p_value => p_qualifications(1).attribute8);
1638
1639 hr_transaction_api.set_varchar2_value
1640 (p_transaction_step_id => p_transaction_step_id
1641 ,p_person_id => p_creator_person_id
1642 ,p_name => 'P_PQ_ATTRIBUTE9'
1643 ,p_value => p_qualifications(1).attribute9);
1644
1645 hr_transaction_api.set_varchar2_value
1646 (p_transaction_step_id => p_transaction_step_id
1647 ,p_person_id => p_creator_person_id
1648 ,p_name => 'P_PQ_ATTRIBUTE10'
1649 ,p_value => p_qualifications(1).attribute10);
1650
1651 hr_transaction_api.set_varchar2_value
1652 (p_transaction_step_id => p_transaction_step_id
1653 ,p_person_id => p_creator_person_id
1654 ,p_name => 'P_PQ_ATTRIBUTE11'
1655 ,p_value => p_qualifications(1).attribute11);
1656
1657 hr_transaction_api.set_varchar2_value
1658 (p_transaction_step_id => p_transaction_step_id
1659 ,p_person_id => p_creator_person_id
1660 ,p_name => 'P_PQ_ATTRIBUTE12'
1661 ,p_value => p_qualifications(1).attribute12);
1662
1663 hr_transaction_api.set_varchar2_value
1664 (p_transaction_step_id => p_transaction_step_id
1665 ,p_person_id => p_creator_person_id
1666 ,p_name => 'P_PQ_ATTRIBUTE13'
1667 ,p_value => p_qualifications(1).attribute13);
1668
1669 hr_transaction_api.set_varchar2_value
1670 (p_transaction_step_id => p_transaction_step_id
1671 ,p_person_id => p_creator_person_id
1672 ,p_name => 'P_PQ_ATTRIBUTE14'
1673 ,p_value => p_qualifications(1).attribute14);
1674
1675 hr_transaction_api.set_varchar2_value
1676 (p_transaction_step_id => p_transaction_step_id
1677 ,p_person_id => p_creator_person_id
1678 ,p_name => 'P_PQ_ATTRIBUTE15'
1679 ,p_value => p_qualifications(1).attribute15);
1680
1681 hr_transaction_api.set_varchar2_value
1682 (p_transaction_step_id => p_transaction_step_id
1683 ,p_person_id => p_creator_person_id
1684 ,p_name => 'P_PQ_ATTRIBUTE16'
1685 ,p_value => p_qualifications(1).attribute16);
1686
1687 hr_transaction_api.set_varchar2_value
1688 (p_transaction_step_id => p_transaction_step_id
1689 ,p_person_id => p_creator_person_id
1690 ,p_name => 'P_PQ_ATTRIBUTE17'
1691 ,p_value => p_qualifications(1).attribute17);
1692
1693 hr_transaction_api.set_varchar2_value
1694 (p_transaction_step_id => p_transaction_step_id
1695 ,p_person_id => p_creator_person_id
1696 ,p_name => 'P_PQ_ATTRIBUTE18'
1697 ,p_value => p_qualifications(1).attribute18);
1698
1699 hr_transaction_api.set_varchar2_value
1700 (p_transaction_step_id => p_transaction_step_id
1701 ,p_person_id => p_creator_person_id
1702 ,p_name => 'P_PQ_ATTRIBUTE19'
1703 ,p_value => p_qualifications(1).attribute19);
1704
1705 hr_transaction_api.set_varchar2_value
1706 (p_transaction_step_id => p_transaction_step_id
1707 ,p_person_id => p_creator_person_id
1708 ,p_name => 'P_PQ_ATTRIBUTE20'
1709 ,p_value => p_qualifications(1).attribute20);
1710
1711 hr_transaction_api.set_varchar2_value
1712 (p_transaction_step_id => p_transaction_step_id
1713 ,p_person_id => p_creator_person_id
1714 ,p_name => 'P_PQ_QUA_INFORMATION_CATEGORY'
1715 ,p_value => p_qualifications(1).qua_information_category);
1716
1717 hr_transaction_api.set_varchar2_value
1718 (p_transaction_step_id => p_transaction_step_id
1719 ,p_person_id => p_creator_person_id
1720 ,p_name => 'P_PQ_QUA_INFORMATION1'
1721 ,p_value => p_qualifications(1).qua_information1);
1722
1723 hr_transaction_api.set_varchar2_value
1724 (p_transaction_step_id => p_transaction_step_id
1725 ,p_person_id => p_creator_person_id
1726 ,p_name => 'P_PQ_QUA_INFORMATION2'
1727 ,p_value => p_qualifications(1).qua_information2);
1728 hr_transaction_api.set_varchar2_value
1729 (p_transaction_step_id => p_transaction_step_id
1730 ,p_person_id => p_creator_person_id
1731 ,p_name => 'P_PQ_QUA_INFORMATION3'
1732 ,p_value => p_qualifications(1).qua_information3);
1733 hr_transaction_api.set_varchar2_value
1734 (p_transaction_step_id => p_transaction_step_id
1735 ,p_person_id => p_creator_person_id
1736 ,p_name => 'P_PQ_QUA_INFORMATION4'
1737 ,p_value => p_qualifications(1).qua_information4);
1738 hr_transaction_api.set_varchar2_value
1739 (p_transaction_step_id => p_transaction_step_id
1740 ,p_person_id => p_creator_person_id
1741 ,p_name => 'P_PQ_QUA_INFORMATION5'
1742 ,p_value => p_qualifications(1).qua_information5);
1743 hr_transaction_api.set_varchar2_value
1744 (p_transaction_step_id => p_transaction_step_id
1745 ,p_person_id => p_creator_person_id
1746 ,p_name => 'P_PQ_QUA_INFORMATION6'
1747 ,p_value => p_qualifications(1).qua_information6);
1748 hr_transaction_api.set_varchar2_value
1749 (p_transaction_step_id => p_transaction_step_id
1750 ,p_person_id => p_creator_person_id
1751 ,p_name => 'P_PQ_QUA_INFORMATION7'
1752 ,p_value => p_qualifications(1).qua_information7);
1753 hr_transaction_api.set_varchar2_value
1754 (p_transaction_step_id => p_transaction_step_id
1755 ,p_person_id => p_creator_person_id
1756 ,p_name => 'P_PQ_QUA_INFORMATION8'
1757 ,p_value => p_qualifications(1).qua_information8);
1758 hr_transaction_api.set_varchar2_value
1759 (p_transaction_step_id => p_transaction_step_id
1760 ,p_person_id => p_creator_person_id
1761 ,p_name => 'P_PQ_QUA_INFORMATION9'
1762 ,p_value => p_qualifications(1).qua_information9);
1763 hr_transaction_api.set_varchar2_value
1764 (p_transaction_step_id => p_transaction_step_id
1765 ,p_person_id => p_creator_person_id
1766 ,p_name => 'P_PQ_QUA_INFORMATION10'
1767 ,p_value => p_qualifications(1).qua_information10);
1768
1769 hr_transaction_api.set_varchar2_value
1770 (p_transaction_step_id => p_transaction_step_id
1771 ,p_person_id => p_creator_person_id
1772 ,p_name => 'P_PQ_QUA_INFORMATION11'
1773 ,p_value => p_qualifications(1).qua_information11);
1774 hr_transaction_api.set_varchar2_value
1775 (p_transaction_step_id => p_transaction_step_id
1776 ,p_person_id => p_creator_person_id
1777 ,p_name => 'P_PQ_QUA_INFORMATION12'
1778 ,p_value => p_qualifications(1).qua_information12);
1779 hr_transaction_api.set_varchar2_value
1780 (p_transaction_step_id => p_transaction_step_id
1781 ,p_person_id => p_creator_person_id
1782 ,p_name => 'P_PQ_QUA_INFORMATION13'
1783 ,p_value => p_qualifications(1).qua_information13);
1784 hr_transaction_api.set_varchar2_value
1785 (p_transaction_step_id => p_transaction_step_id
1786 ,p_person_id => p_creator_person_id
1787 ,p_name => 'P_PQ_QUA_INFORMATION14'
1788 ,p_value => p_qualifications(1).qua_information14);
1789 hr_transaction_api.set_varchar2_value
1790 (p_transaction_step_id => p_transaction_step_id
1791 ,p_person_id => p_creator_person_id
1792 ,p_name => 'P_PQ_QUA_INFORMATION15'
1793 ,p_value => p_qualifications(1).qua_information15);
1794 hr_transaction_api.set_varchar2_value
1795 (p_transaction_step_id => p_transaction_step_id
1796 ,p_person_id => p_creator_person_id
1797 ,p_name => 'P_PQ_QUA_INFORMATION16'
1798 ,p_value => p_qualifications(1).qua_information16);
1799 hr_transaction_api.set_varchar2_value
1800 (p_transaction_step_id => p_transaction_step_id
1801 ,p_person_id => p_creator_person_id
1802 ,p_name => 'P_PQ_QUA_INFORMATION17'
1803 ,p_value => p_qualifications(1).qua_information17);
1804 hr_transaction_api.set_varchar2_value
1805 (p_transaction_step_id => p_transaction_step_id
1806 ,p_person_id => p_creator_person_id
1807 ,p_name => 'P_PQ_QUA_INFORMATION18'
1808 ,p_value => p_qualifications(1).qua_information18);
1809 hr_transaction_api.set_varchar2_value
1810 (p_transaction_step_id => p_transaction_step_id
1811 ,p_person_id => p_creator_person_id
1812 ,p_name => 'P_PQ_QUA_INFORMATION19'
1813 ,p_value => p_qualifications(1).qua_information19);
1814 hr_transaction_api.set_varchar2_value
1815 (p_transaction_step_id => p_transaction_step_id
1816 ,p_person_id => p_creator_person_id
1817 ,p_name => 'P_PQ_QUA_INFORMATION20'
1818 ,p_value => p_qualifications(1).qua_information20);
1819
1820 hr_transaction_api.set_varchar2_value
1821 (p_transaction_step_id => p_transaction_step_id
1822 ,p_person_id => p_creator_person_id
1823 ,p_name => 'P_PARTY_ID'
1824 ,p_value => p_qualifications(1).party_id);
1825
1826 hr_transaction_api.set_varchar2_value
1827 (p_transaction_step_id => p_transaction_step_id
1828 ,p_person_id => p_creator_person_id
1829 ,p_name => 'P_PEA_ATTRIBUTE_CATEGORY'
1830 ,p_value => p_qua_attendance(1).attribute_category);
1831
1832 hr_transaction_api.set_varchar2_value
1833 (p_transaction_step_id => p_transaction_step_id
1834 ,p_person_id => p_creator_person_id
1835 ,p_name => 'P_PEA_ATTRIBUTE1'
1836 ,p_value => p_qua_attendance(1).attribute1);
1837
1838 hr_transaction_api.set_varchar2_value
1839 (p_transaction_step_id => p_transaction_step_id
1840 ,p_person_id => p_creator_person_id
1841 ,p_name => 'P_PEA_ATTRIBUTE2'
1842 ,p_value => p_qua_attendance(1).attribute2);
1843
1844 hr_transaction_api.set_varchar2_value
1845 (p_transaction_step_id => p_transaction_step_id
1846 ,p_person_id => p_creator_person_id
1847 ,p_name => 'P_PEA_ATTRIBUTE3'
1848 ,p_value => p_qua_attendance(1).attribute3);
1849
1850 hr_transaction_api.set_varchar2_value
1851 (p_transaction_step_id => p_transaction_step_id
1852 ,p_person_id => p_creator_person_id
1853 ,p_name => 'P_PEA_ATTRIBUTE4'
1854 ,p_value => p_qua_attendance(1).attribute4);
1855
1856 hr_transaction_api.set_varchar2_value
1857 (p_transaction_step_id => p_transaction_step_id
1858 ,p_person_id => p_creator_person_id
1859 ,p_name => 'P_PEA_ATTRIBUTE5'
1860 ,p_value => p_qua_attendance(1).attribute5);
1861
1862 hr_transaction_api.set_varchar2_value
1863 (p_transaction_step_id => p_transaction_step_id
1864 ,p_person_id => p_creator_person_id
1865 ,p_name => 'P_PEA_ATTRIBUTE6'
1866 ,p_value => p_qua_attendance(1).attribute6);
1867
1868 hr_transaction_api.set_varchar2_value
1869 (p_transaction_step_id => p_transaction_step_id
1870 ,p_person_id => p_creator_person_id
1871 ,p_name => 'P_PEA_ATTRIBUTE7'
1872 ,p_value => p_qua_attendance(1).attribute7);
1873
1874 hr_transaction_api.set_varchar2_value
1875 (p_transaction_step_id => p_transaction_step_id
1876 ,p_person_id => p_creator_person_id
1877 ,p_name => 'P_PEA_ATTRIBUTE8'
1878 ,p_value => p_qua_attendance(1).attribute8);
1879
1880 hr_transaction_api.set_varchar2_value
1881 (p_transaction_step_id => p_transaction_step_id
1882 ,p_person_id => p_creator_person_id
1883 ,p_name => 'P_PEA_ATTRIBUTE9'
1884 ,p_value => p_qua_attendance(1).attribute9);
1885
1886 hr_transaction_api.set_varchar2_value
1887 (p_transaction_step_id => p_transaction_step_id
1888 ,p_person_id => p_creator_person_id
1889 ,p_name => 'P_PEA_ATTRIBUTE10'
1890 ,p_value => p_qua_attendance(1).attribute10);
1891
1892 hr_transaction_api.set_varchar2_value
1893 (p_transaction_step_id => p_transaction_step_id
1894 ,p_person_id => p_creator_person_id
1895 ,p_name => 'P_PEA_ATTRIBUTE11'
1896 ,p_value => p_qua_attendance(1).attribute11);
1897
1898 hr_transaction_api.set_varchar2_value
1899 (p_transaction_step_id => p_transaction_step_id
1900 ,p_person_id => p_creator_person_id
1901 ,p_name => 'P_PEA_ATTRIBUTE12'
1902 ,p_value => p_qua_attendance(1).attribute12);
1903
1904 hr_transaction_api.set_varchar2_value
1905 (p_transaction_step_id => p_transaction_step_id
1906 ,p_person_id => p_creator_person_id
1907 ,p_name => 'P_PEA_ATTRIBUTE13'
1908 ,p_value => p_qua_attendance(1).attribute13);
1909
1910 hr_transaction_api.set_varchar2_value
1911 (p_transaction_step_id => p_transaction_step_id
1912 ,p_person_id => p_creator_person_id
1913 ,p_name => 'P_PEA_ATTRIBUTE14'
1914 ,p_value => p_qua_attendance(1).attribute14);
1915
1916 hr_transaction_api.set_varchar2_value
1917 (p_transaction_step_id => p_transaction_step_id
1918 ,p_person_id => p_creator_person_id
1919 ,p_name => 'P_PEA_ATTRIBUTE15'
1920 ,p_value => p_qua_attendance(1).attribute15);
1921
1922 hr_transaction_api.set_varchar2_value
1923 (p_transaction_step_id => p_transaction_step_id
1924 ,p_person_id => p_creator_person_id
1925 ,p_name => 'P_PEA_ATTRIBUTE16'
1926 ,p_value => p_qua_attendance(1).attribute16);
1927
1928 hr_transaction_api.set_varchar2_value
1929 (p_transaction_step_id => p_transaction_step_id
1930 ,p_person_id => p_creator_person_id
1931 ,p_name => 'P_PEA_ATTRIBUTE17'
1932 ,p_value => p_qua_attendance(1).attribute17);
1933
1934 hr_transaction_api.set_varchar2_value
1935 (p_transaction_step_id => p_transaction_step_id
1936 ,p_person_id => p_creator_person_id
1937 ,p_name => 'P_PEA_ATTRIBUTE18'
1938 ,p_value => p_qua_attendance(1).attribute18);
1939
1940 hr_transaction_api.set_varchar2_value
1941 (p_transaction_step_id => p_transaction_step_id
1942 ,p_person_id => p_creator_person_id
1943 ,p_name => 'P_PEA_ATTRIBUTE19'
1944 ,p_value => p_qua_attendance(1).attribute19);
1945
1946 hr_transaction_api.set_varchar2_value
1947 (p_transaction_step_id => p_transaction_step_id
1948 ,p_person_id => p_creator_person_id
1949 ,p_name => 'P_PEA_ATTRIBUTE20'
1950 ,p_value => p_qua_attendance(1).attribute20);
1951
1952 l_subject_count := NVL(p_qua_subjects.count,0);
1953
1954 hr_transaction_api.set_varchar2_value
1955 (p_transaction_step_id => p_transaction_step_id
1956 ,p_person_id => p_creator_person_id
1957 ,p_name => 'P_SUBJECT_COUNT'
1958 ,p_value => l_subject_count);
1959
1960 hr_utility.set_location('Entering For Loop:'||l_proc,10);
1961 FOR i IN 1..l_subject_count LOOP
1962
1963 hr_transaction_api.set_varchar2_value
1964 (p_transaction_step_id => p_transaction_step_id
1965 ,p_person_id => p_creator_person_id
1966 ,p_name => 'P_SUBJECTS_TAKEN_ID'||i
1967 ,p_value => p_qua_subjects(i).subjects_taken_id);
1968
1969 hr_transaction_api.set_varchar2_value
1970 (p_transaction_step_id => p_transaction_step_id
1971 ,p_person_id => p_creator_person_id
1972 ,p_name => 'P_PST_OBJECT_VERSION_NUMBER'||i
1973 ,p_value => p_qua_subjects(i).object_version_number);
1974
1975 hr_transaction_api.set_varchar2_value
1976 (p_transaction_step_id => p_transaction_step_id
1977 ,p_person_id => p_creator_person_id
1978 ,p_name => 'P_PST_SUBJECT'||i
1979 ,p_value => p_qua_subjects(i).subject);
1980
1981 hr_transaction_api.set_varchar2_value
1982 (p_transaction_step_id => p_transaction_step_id
1983 ,p_person_id => p_creator_person_id
1984 ,p_name => 'P_PST_MAJOR'||i
1985 ,p_value => p_qua_subjects(i).major);
1986
1987 hr_transaction_api.set_date_value
1988 (p_transaction_step_id => p_transaction_step_id
1989 ,p_person_id => p_creator_person_id
1990 ,p_name => 'P_PST_START_DATE'||i
1991 ,p_value => p_qua_subjects(i).start_date);
1992
1993 hr_transaction_api.set_date_value
1994 (p_transaction_step_id => p_transaction_step_id
1995 ,p_person_id => p_creator_person_id
1996 ,p_name => 'P_PST_END_DATE'||i
1997 ,p_value => p_qua_subjects(i).end_date);
1998
1999 hr_transaction_api.set_varchar2_value
2000 (p_transaction_step_id => p_transaction_step_id
2001 ,p_person_id => p_creator_person_id
2002 ,p_name => 'P_PST_SUBJECT_STATUS'||i
2003 ,p_value => p_qua_subjects(i).subject_status);
2004
2005 hr_transaction_api.set_varchar2_value
2006 (p_transaction_step_id => p_transaction_step_id
2007 ,p_person_id => p_creator_person_id
2008 ,p_name => 'P_PST_GRADE_ATTAINED'||i
2009 ,p_value => p_qua_subjects(i).grade_attained);
2010
2011 hr_transaction_api.set_varchar2_value
2012 (p_transaction_step_id => p_transaction_step_id
2013 ,p_person_id => p_creator_person_id
2014 ,p_name => 'P_PST_DELETE_FLAG'||i
2015 ,p_value => p_qua_subjects(i).delete_flag);
2016
2017 hr_transaction_api.set_varchar2_value
2018 (p_transaction_step_id => p_transaction_step_id
2019 ,p_person_id => p_creator_person_id
2020 ,p_name => 'P_PST_ATTRIBUTE_CATEGORY'||i
2021 ,p_value => p_qua_subjects(i).attribute_category);
2022
2023 hr_transaction_api.set_varchar2_value
2024 (p_transaction_step_id => p_transaction_step_id
2025 ,p_person_id => p_creator_person_id
2026 ,p_name => 'P_ATTRIBUTE1_PST'||i
2027 ,p_value => p_qua_subjects(i).attribute1);
2028
2029 hr_transaction_api.set_varchar2_value
2030 (p_transaction_step_id => p_transaction_step_id
2031 ,p_person_id => p_creator_person_id
2032 ,p_name => 'P_ATTRIBUTE2_PST'||i
2033 ,p_value => p_qua_subjects(i).attribute2);
2034 hr_transaction_api.set_varchar2_value
2035 (p_transaction_step_id => p_transaction_step_id
2036 ,p_person_id => p_creator_person_id
2037 ,p_name => 'P_ATTRIBUTE3_PST'||i
2038 ,p_value => p_qua_subjects(i).attribute3);
2039 hr_transaction_api.set_varchar2_value
2040 (p_transaction_step_id => p_transaction_step_id
2041 ,p_person_id => p_creator_person_id
2042 ,p_name => 'P_ATTRIBUTE4_PST'||i
2043 ,p_value => p_qua_subjects(i).attribute4);
2044 hr_transaction_api.set_varchar2_value
2045 (p_transaction_step_id => p_transaction_step_id
2046 ,p_person_id => p_creator_person_id
2047 ,p_name => 'P_ATTRIBUTE5_PST'||i
2048 ,p_value => p_qua_subjects(i).attribute5);
2049 hr_transaction_api.set_varchar2_value
2050 (p_transaction_step_id => p_transaction_step_id
2051 ,p_person_id => p_creator_person_id
2052 ,p_name => 'P_ATTRIBUTE6_PST'||i
2053 ,p_value => p_qua_subjects(i).attribute6);
2054 hr_transaction_api.set_varchar2_value
2055 (p_transaction_step_id => p_transaction_step_id
2056 ,p_person_id => p_creator_person_id
2057 ,p_name => 'P_ATTRIBUTE7_PST'||i
2058 ,p_value => p_qua_subjects(i).attribute7);
2059 hr_transaction_api.set_varchar2_value
2060 (p_transaction_step_id => p_transaction_step_id
2061 ,p_person_id => p_creator_person_id
2062 ,p_name => 'P_ATTRIBUTE8_PST'||i
2063 ,p_value => p_qua_subjects(i).attribute8);
2064 hr_transaction_api.set_varchar2_value
2065 (p_transaction_step_id => p_transaction_step_id
2066 ,p_person_id => p_creator_person_id
2067 ,p_name => 'P_ATTRIBUTE9_PST'||i
2068 ,p_value => p_qua_subjects(i).attribute9);
2069 hr_transaction_api.set_varchar2_value
2070 (p_transaction_step_id => p_transaction_step_id
2071 ,p_person_id => p_creator_person_id
2072 ,p_name => 'P_ATTRIBUTE10_PST'||i
2073 ,p_value => p_qua_subjects(i).attribute10);
2074 hr_transaction_api.set_varchar2_value
2075 (p_transaction_step_id => p_transaction_step_id
2076 ,p_person_id => p_creator_person_id
2077 ,p_name => 'P_ATTRIBUTE11_PST'||i
2078 ,p_value => p_qua_subjects(i).attribute11);
2079 hr_transaction_api.set_varchar2_value
2080 (p_transaction_step_id => p_transaction_step_id
2081 ,p_person_id => p_creator_person_id
2082 ,p_name => 'P_ATTRIBUTE12_PST'||i
2083 ,p_value => p_qua_subjects(i).attribute12);
2084 hr_transaction_api.set_varchar2_value
2085 (p_transaction_step_id => p_transaction_step_id
2086 ,p_person_id => p_creator_person_id
2087 ,p_name => 'P_ATTRIBUTE13_PST'||i
2088 ,p_value => p_qua_subjects(i).attribute13);
2089 hr_transaction_api.set_varchar2_value
2090 (p_transaction_step_id => p_transaction_step_id
2091 ,p_person_id => p_creator_person_id
2092 ,p_name => 'P_ATTRIBUTE14_PST'||i
2093 ,p_value => p_qua_subjects(i).attribute14);
2094 hr_transaction_api.set_varchar2_value
2095 (p_transaction_step_id => p_transaction_step_id
2096 ,p_person_id => p_creator_person_id
2097 ,p_name => 'P_ATTRIBUTE15_PST'||i
2098 ,p_value => p_qua_subjects(i).attribute15);
2099 hr_transaction_api.set_varchar2_value
2100 (p_transaction_step_id => p_transaction_step_id
2101 ,p_person_id => p_creator_person_id
2102 ,p_name => 'P_ATTRIBUTE16_PST'||i
2103 ,p_value => p_qua_subjects(i).attribute16);
2104 hr_transaction_api.set_varchar2_value
2105 (p_transaction_step_id => p_transaction_step_id
2106 ,p_person_id => p_creator_person_id
2107 ,p_name => 'P_ATTRIBUTE17_PST'||i
2108 ,p_value => p_qua_subjects(i).attribute17);
2109 hr_transaction_api.set_varchar2_value
2110 (p_transaction_step_id => p_transaction_step_id
2111 ,p_person_id => p_creator_person_id
2112 ,p_name => 'P_ATTRIBUTE18_PST'||i
2113 ,p_value => p_qua_subjects(i).attribute18);
2114 hr_transaction_api.set_varchar2_value
2115 (p_transaction_step_id => p_transaction_step_id
2116 ,p_person_id => p_creator_person_id
2117 ,p_name => 'P_ATTRIBUTE19_PST'||i
2118 ,p_value => p_qua_subjects(i).attribute19);
2119 hr_transaction_api.set_varchar2_value
2120 (p_transaction_step_id => p_transaction_step_id
2121 ,p_person_id => p_creator_person_id
2122 ,p_name => 'P_ATTRIBUTE20_PST'||i
2123 ,p_value => p_qua_subjects(i).attribute20);
2124 hr_transaction_api.set_varchar2_value
2125 (p_transaction_step_id => p_transaction_step_id
2126 ,p_person_id => p_creator_person_id
2127 ,p_name => 'P_SUB_INFORMATION_CATEGORY'||i
2128 ,p_value => p_qua_subjects(i).sub_information_category);
2129 hr_transaction_api.set_varchar2_value
2130 (p_transaction_step_id => p_transaction_step_id
2131 ,p_person_id => p_creator_person_id
2132 ,p_name => 'P_SUB_INFORMATION1_PST'||i
2133 ,p_value => p_qua_subjects(i).sub_information1);
2134 hr_transaction_api.set_varchar2_value
2135 (p_transaction_step_id => p_transaction_step_id
2136 ,p_person_id => p_creator_person_id
2137 ,p_name => 'P_SUB_INFORMATION2_PST'||i
2138 ,p_value => p_qua_subjects(i).sub_information2);
2139 hr_transaction_api.set_varchar2_value
2140 (p_transaction_step_id => p_transaction_step_id
2141 ,p_person_id => p_creator_person_id
2142 ,p_name => 'P_SUB_INFORMATION3_PST'||i
2143 ,p_value => p_qua_subjects(i).sub_information3);
2144 hr_transaction_api.set_varchar2_value
2145 (p_transaction_step_id => p_transaction_step_id
2146 ,p_person_id => p_creator_person_id
2147 ,p_name => 'P_SUB_INFORMATION4_PST'||i
2148 ,p_value => p_qua_subjects(i).sub_information4);
2149 hr_transaction_api.set_varchar2_value
2150 (p_transaction_step_id => p_transaction_step_id
2151 ,p_person_id => p_creator_person_id
2152 ,p_name => 'P_SUB_INFORMATION5_PST'||i
2153 ,p_value => p_qua_subjects(i).sub_information5);
2154 hr_transaction_api.set_varchar2_value
2155 (p_transaction_step_id => p_transaction_step_id
2156 ,p_person_id => p_creator_person_id
2157 ,p_name => 'P_SUB_INFORMATION6_PST'||i
2158 ,p_value => p_qua_subjects(i).sub_information6);
2159 hr_transaction_api.set_varchar2_value
2160 (p_transaction_step_id => p_transaction_step_id
2161 ,p_person_id => p_creator_person_id
2162 ,p_name => 'P_SUB_INFORMATION7_PST'||i
2163 ,p_value => p_qua_subjects(i).sub_information7);
2164 hr_transaction_api.set_varchar2_value
2165 (p_transaction_step_id => p_transaction_step_id
2166 ,p_person_id => p_creator_person_id
2167 ,p_name => 'P_SUB_INFORMATION8_PST'||i
2168 ,p_value => p_qua_subjects(i).sub_information8);
2169 hr_transaction_api.set_varchar2_value
2170 (p_transaction_step_id => p_transaction_step_id
2171 ,p_person_id => p_creator_person_id
2172 ,p_name => 'P_SUB_INFORMATION9_PST'||i
2173 ,p_value => p_qua_subjects(i).sub_information9);
2174 hr_transaction_api.set_varchar2_value
2175 (p_transaction_step_id => p_transaction_step_id
2176 ,p_person_id => p_creator_person_id
2177 ,p_name => 'P_SUB_INFORMATION10_PST'||i
2178 ,p_value => p_qua_subjects(i).sub_information10);
2179 hr_transaction_api.set_varchar2_value
2180 (p_transaction_step_id => p_transaction_step_id
2181 ,p_person_id => p_creator_person_id
2182 ,p_name => 'P_SUB_INFORMATION11_PST'||i
2183 ,p_value => p_qua_subjects(i).sub_information11);
2184 hr_transaction_api.set_varchar2_value
2185 (p_transaction_step_id => p_transaction_step_id
2186 ,p_person_id => p_creator_person_id
2187 ,p_name => 'P_SUB_INFORMATION12_PST'||i
2188 ,p_value => p_qua_subjects(i).sub_information12);
2189 hr_transaction_api.set_varchar2_value
2190 (p_transaction_step_id => p_transaction_step_id
2191 ,p_person_id => p_creator_person_id
2192 ,p_name => 'P_SUB_INFORMATION13_PST'||i
2193 ,p_value => p_qua_subjects(i).sub_information13);
2194 hr_transaction_api.set_varchar2_value
2195 (p_transaction_step_id => p_transaction_step_id
2196 ,p_person_id => p_creator_person_id
2197 ,p_name => 'P_SUB_INFORMATION14_PST'||i
2198 ,p_value => p_qua_subjects(i).sub_information14);
2199 hr_transaction_api.set_varchar2_value
2200 (p_transaction_step_id => p_transaction_step_id
2201 ,p_person_id => p_creator_person_id
2202 ,p_name => 'P_SUB_INFORMATION15_PST'||i
2203 ,p_value => p_qua_subjects(i).sub_information15);
2204 hr_transaction_api.set_varchar2_value
2205 (p_transaction_step_id => p_transaction_step_id
2206 ,p_person_id => p_creator_person_id
2207 ,p_name => 'P_SUB_INFORMATION16_PST'||i
2208 ,p_value => p_qua_subjects(i).sub_information16);
2209 hr_transaction_api.set_varchar2_value
2210 (p_transaction_step_id => p_transaction_step_id
2211 ,p_person_id => p_creator_person_id
2212 ,p_name => 'P_SUB_INFORMATION17_PST'||i
2213 ,p_value => p_qua_subjects(i).sub_information17);
2214 hr_transaction_api.set_varchar2_value
2215 (p_transaction_step_id => p_transaction_step_id
2216 ,p_person_id => p_creator_person_id
2217 ,p_name => 'P_SUB_INFORMATION18_PST'||i
2218 ,p_value => p_qua_subjects(i).sub_information18);
2219 hr_transaction_api.set_varchar2_value
2220 (p_transaction_step_id => p_transaction_step_id
2221 ,p_person_id => p_creator_person_id
2222 ,p_name => 'P_SUB_INFORMATION19_PST'||i
2223 ,p_value => p_qua_subjects(i).sub_information19);
2224 hr_transaction_api.set_varchar2_value
2225 (p_transaction_step_id => p_transaction_step_id
2226 ,p_person_id => p_creator_person_id
2227 ,p_name => 'P_SUB_INFORMATION20_PST'||i
2228 ,p_value => p_qua_subjects(i).sub_information20);
2229
2230 END LOOP;
2231 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
2232
2233 hr_transaction_api.set_varchar2_value
2234 (p_transaction_step_id => p_transaction_step_id
2235 ,p_person_id => p_creator_person_id
2236 ,p_name => 'P_REVIEW_PROC_CALL'
2237 ,p_value => p_proc_call);
2238
2239 hr_transaction_api.set_varchar2_value
2240 (p_transaction_step_id => p_transaction_step_id
2241 ,p_person_id => p_creator_person_id
2242 ,p_name => 'P_REVIEW_ACTID'
2243 ,p_value => p_actid);
2244
2245 hr_utility.set_location('Exiting:'||l_proc, 20);
2246 Exception
2247 when others then
2248 hr_utility.set_location('Exception:Others'||l_proc,555);
2249 rollback;
2250 raise;
2251 END save_transaction_step;
2252
2253 -- Start of Procedure validate_api
2254 PROCEDURE validate_api
2255 (p_validate in boolean
2256 ,p_mode in varchar2
2257 ,p_selected_person_id in number
2258 ,p_qualifications in SSHR_QUA_TAB_TYP
2259 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
2260 ,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
2261 ) IS
2262 CURSOR csr_school_changed(p_establishment in varchar2
2263 ,p_attendance_id in number
2264 ,p_establishment_id in number)
2265 IS
2266 SELECT null
2267 FROM
2268 per_establishment_attendances
2269 WHERE
2270 attendance_id = p_attendance_id
2271 AND NVL(establishment_id,-1) = NVL(p_establishment_id,-1)
2272 AND NVL(UPPER(establishment),-1) = NVL(UPPER(p_establishment),-1);
2273
2274 CURSOR csr_attendance(p_business_group_id in number
2275 ,p_establishment_id in number
2276 ,p_start_date in date
2277 ,p_establishment in varchar2
2278 ,p_selected_person_id in number)
2279 IS
2280 SELECT attendance_id
2281 FROM
2282 per_establishment_attendances
2283 WHERE
2284 PERSON_ID = p_selected_person_id
2285 AND (attended_start_date is null
2286 and p_start_date is null
2287 or attended_start_date = p_start_date)
2288 AND business_group_id = p_business_group_id
2289 AND (p_establishment_id is not null
2290 AND establishment_id = p_establishment_id
2291 OR p_establishment is not null
2292 AND UPPER(establishment) = UPPER(p_establishment));
2293
2294 CURSOR csr_qualification_dates(p_qualification_id in number)
2295 IS
2296 SELECT
2297 start_date
2298 ,end_date
2299 FROM
2300 per_qualifications
2301 WHERE
2302 qualification_id = p_qualification_id;
2303
2304 cursor c1(p_establishment in varchar2
2305 ,p_attendance_id in number
2306 ,p_school_id in number
2307 ,p_attended_start_date in date
2308 ,p_attended_end_date in date
2309 ,p_qualification_type_id in number
2310 ,p_title in varchar2
2311 ,p_selected_person_id in number) is
2312 select
2313 null
2314 from
2315 per_establishment_attendances per,
2316 per_qualifications pq
2317 where
2318 per.person_id = p_selected_person_id
2319 and (p_establishment is not null
2320 and UPPER(per.establishment) = UPPER(p_establishment)
2321 or p_school_id is not null and per.establishment_id is not null
2322 and per.establishment_id = p_school_id)
2323 and per.attendance_id <> nvl(p_attendance_id,-1)
2324 and (p_attended_start_date
2325 between per.attended_start_date
2326 and nvl(per.attended_end_date,hr_api.g_eot)
2327 or nvl(p_attended_end_date,hr_api.g_eot)
2328 between per.attended_start_date
2329 and nvl(per.attended_end_date,hr_api.g_eot))
2330 and per.attendance_id = pq.attendance_id
2331 and pq.qualification_type_id = p_qualification_type_id
2332 --and nvl(pq.party_id, -1) = nvl(p_party_id,-1)
2333 and nvl(pq.title,-1) = nvl(p_title,-1);
2334
2335 cursor csr_get_establishment_id(p_establishment in varchar2) is
2336 select establishment_id
2337 from per_establishments
2338 where upper(name) = upper(p_establishment);
2339
2340 cursor csr_ok_to_del_attendance(p_attendance_id in number) is
2341 select count(*)
2342 from per_qualifications
2343 where attendance_id = p_attendance_id;
2344
2345 l_attendance_count number;
2346 l_school_name_id number;
2347 l_qualification_id number;
2348 l_pq_object_version_number number;
2349 l_attendance_id number;
2350 l_old_attendance_id number;
2351 l_establishment_id number;
2352 l_establishment varchar2(2000);
2353 l_pea_object_version_number number;
2354 l_subjects_taken_id number;
2355 l_pst_object_version_number number;
2356 l_school_changed varchar2(1);
2357 l_start_date date;
2358 l_end_date date;
2359 l_dummy varchar2(1);
2360 l_new_school_allowed varchar2(10);
2361 l_proc varchar2(72) := g_package||'validate_api';
2362
2363 BEGIN
2364
2365 hr_utility.set_location('Entering:'||l_proc, 5);
2366 savepoint validate_qua;
2367
2368 l_old_attendance_id := p_qualifications(1).attendance_id;
2369
2370 l_establishment := p_qua_attendance(1).establishment;
2371
2372 --check if attendance already exists
2373 if p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN
2374
2375 hr_utility.set_location('p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN:'||l_proc,10);
2376 l_establishment_id := p_qua_attendance(1).establishment_id;
2377
2378 --if the school is entered (not picked from the LOV),
2379 if l_establishment_id is null then
2380 hr_utility.set_location('l_establishment_id is null then:'||l_proc,20);
2381 OPEN csr_get_establishment_id(p_qua_attendance(1).establishment);
2382 FETCH csr_get_establishment_id into l_school_name_id;
2383 IF csr_get_establishment_id%FOUND THEN
2384 hr_utility.set_location('csr_get_establishment_id%FOUND:'||l_proc,25);
2385 l_establishment_id := l_school_name_id;
2386 FETCH csr_get_establishment_id into l_school_name_id;
2387 IF csr_get_establishment_id%FOUND THEN
2388 hr_utility.set_location('csr_get_establishment_id%FOUND:'||l_proc,30);
2389 CLOSE csr_get_establishment_id;
2390 fnd_message.set_name('PER','HR_SCHOOL_NAME_NOT_UNIQUE');
2391 hr_utility.raise_error;
2392 END IF;
2393 ELSE
2394 l_new_school_allowed := fnd_profile.value('HR_SSHR_NEW_SCHOOL');
2395 if l_new_school_allowed = 'N' THEN
2396 hr_utility.set_location('l_new_school_allowed=N:'||l_proc,35);
2397 CLOSE csr_get_establishment_id;
2398 fnd_message.set_name('PER','HR_SCHOOL_NAME_NOT_EXIST');
2399 hr_utility.raise_error;
2400 END IF;
2401 END IF;
2402 CLOSE csr_get_establishment_id;
2403 end if;
2404
2405 --check attendance overlap
2406 OPEN c1(p_establishment => p_qua_attendance(1).establishment
2407 ,p_school_id => p_qua_attendance(1).establishment_id
2408 ,p_attendance_id => p_qualifications(1).attendance_id
2409 ,p_attended_start_date => p_qua_attendance(1).attended_start_date
2410 ,p_attended_end_date => p_qua_attendance(1).attended_end_date
2411 ,p_qualification_type_id => p_qualifications(1).qualification_type_id
2412 ,p_title => p_qualifications(1).title
2413 ,p_selected_person_id => p_selected_person_id);
2414 FETCH c1 into l_dummy;
2415 if c1%found then
2416 close c1;
2417 fnd_message.set_name('PAY','HR_51847_QUA_REC_EXISTS');
2418 hr_utility.raise_error;
2419 end if;
2420 CLOSE c1;
2421
2422 OPEN csr_attendance
2423 (p_business_group_id => p_qualifications(1).business_group_id
2424 ,p_establishment_id => p_qua_attendance(1).establishment_id
2425 ,p_start_date => p_qua_attendance(1).attended_start_date
2426 ,p_establishment => p_qua_attendance(1).establishment
2427 ,p_selected_person_id => p_selected_person_id);
2428 FETCH csr_attendance INTO l_attendance_id;
2429 IF csr_attendance%NOTFOUND THEN
2430 l_attendance_id := null;
2431 END IF;
2432 CLOSE csr_attendance;
2433
2434 if l_establishment_id is null then
2435 l_establishment := p_qua_attendance(1).establishment;
2436 else
2437 l_establishment := null;
2438 end if;
2439 end if;
2440
2441 l_qualification_id := p_qualifications(1).qualification_id;
2442
2443 if l_qualification_id is null then --a new qualification
2444 --insert attendance only for qua
2445 if p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN
2446
2447 if l_attendance_id IS null THEN
2448
2449 hr_utility.set_location('qualification id is null,mode is EDUCATION,attendance id is null:'||l_proc,45);
2450 --attendance is not exist, then insert a new one
2451 --hr_qua_awards_util_ss.ins
2452 per_esa_ins.ins
2453 (p_validate => FALSE
2454 ,p_effective_date => sysdate
2455 ,p_attendance_id => l_attendance_id
2456 ,p_business_group_id => p_qualifications(1).business_group_id
2457 ,p_person_id => p_selected_person_id
2458 ,p_establishment_id => l_establishment_id
2459 ,p_establishment =>
2460 decode_value
2461 (p_expression => l_establishment_id IS NOT null
2462 ,p_true => null
2463 ,p_false => p_qua_attendance(1).establishment)
2464 ,p_attended_start_date => p_qua_attendance(1).attended_start_date
2465 ,p_attended_end_date => p_qua_attendance(1).attended_end_date
2466 ,p_full_time => NVL(p_qua_attendance(1).full_time,'N')
2467 ,p_attribute_category => p_qua_attendance(1).attribute_category
2468 ,p_attribute1 => p_qua_attendance(1).attribute1
2469 ,p_attribute2 => p_qua_attendance(1).attribute2
2470 ,p_attribute3 => p_qua_attendance(1).attribute3
2471 ,p_attribute4 => p_qua_attendance(1).attribute4
2472 ,p_attribute5 => p_qua_attendance(1).attribute5
2473 ,p_attribute6 => p_qua_attendance(1).attribute6
2474 ,p_attribute7 => p_qua_attendance(1).attribute7
2475 ,p_attribute8 => p_qua_attendance(1).attribute8
2476 ,p_attribute9 => p_qua_attendance(1).attribute9
2477 ,p_attribute10 => p_qua_attendance(1).attribute10
2478 ,p_attribute11 => p_qua_attendance(1).attribute11
2479 ,p_attribute12 => p_qua_attendance(1).attribute12
2480 ,p_attribute13 => p_qua_attendance(1).attribute13
2481 ,p_attribute14 => p_qua_attendance(1).attribute14
2482 ,p_attribute15 => p_qua_attendance(1).attribute15
2483 ,p_attribute16 => p_qua_attendance(1).attribute16
2484 ,p_attribute17 => p_qua_attendance(1).attribute17
2485 ,p_attribute18 => p_qua_attendance(1).attribute18
2486 ,p_attribute19 => p_qua_attendance(1).attribute19
2487 ,p_attribute20 => p_qua_attendance(1).attribute20
2488 ,p_object_version_number => l_pea_object_version_number);
2489 end if;
2490 end if; --end insert attendance
2491
2492 --insert qualification for both qua and award
2493 --per_qua_ins.ins
2494 PER_QUALIFICATIONS_API.CREATE_QUALIFICATION
2495 (p_validate => FALSE
2496 ,p_effective_date => sysdate
2497 ,p_qualification_type_id => p_qualifications(1).qualification_type_id
2498 ,p_qualification_id => l_qualification_id
2499 ,p_business_group_id => p_qualifications(1).business_group_id
2500 ,p_object_version_number => l_pq_object_version_number
2501 ,p_person_id => decode_value(
2502 p_expression => l_attendance_id IS NOT null
2503 ,p_true => null
2504 ,p_false => p_selected_person_id)
2505 ,p_title => p_qualifications(1).title
2506 ,p_status => p_qualifications(1).status
2507 ,p_start_date => p_qualifications(1).start_date
2508 ,p_end_date => p_qualifications(1).end_date
2509 --,p_party_id => p_qualifications(1).party_id
2510 ,p_attendance_id => l_attendance_id
2511 ,p_grade_attained => p_qualifications(1).grade_attained
2512 ,p_awarded_date => p_qualifications(1).awarded_date
2513 ,p_fee => p_qualifications(1).fee
2514 ,p_fee_currency => p_qualifications(1).fee_currency
2515 ,p_training_completed_amount =>
2516 p_qualifications(1).training_completed_amount
2517 ,p_reimbursement_arrangements =>
2518 p_qualifications(1).reimbursement_arrangements
2519 ,p_training_completed_units =>
2520 p_qualifications(1).training_completed_units
2521 ,p_total_training_amount => p_qualifications(1).total_training_amount
2522 ,p_license_number => p_qualifications(1).license_number
2523 ,p_expiry_date => p_qualifications(1).expiry_date
2524 ,p_license_restrictions => p_qualifications(1).license_restrictions
2525 ,p_awarding_body => p_qualifications(1).awarding_body
2526 ,p_tuition_method => p_qualifications(1).tuition_method
2527 ,p_group_ranking => p_qualifications(1).group_ranking
2528 ,p_comments => substr(p_qualifications(1).comments,1,2000)
2529 ,p_attribute_category => p_qualifications(1).attribute_category
2530 ,p_attribute1 => p_qualifications(1).attribute1
2531 ,p_attribute2 => p_qualifications(1).attribute2
2532 ,p_attribute3 => p_qualifications(1).attribute3
2533 ,p_attribute4 => p_qualifications(1).attribute4
2534 ,p_attribute5 => p_qualifications(1).attribute5
2535 ,p_attribute6 => p_qualifications(1).attribute6
2536 ,p_attribute7 => p_qualifications(1).attribute7
2537 ,p_attribute8 => p_qualifications(1).attribute8
2538 ,p_attribute9 => p_qualifications(1).attribute9
2539 ,p_attribute10 => p_qualifications(1).attribute10
2540 ,p_attribute11 => p_qualifications(1).attribute11
2541 ,p_attribute12 => p_qualifications(1).attribute12
2542 ,p_attribute13 => p_qualifications(1).attribute13
2543 ,p_attribute14 => p_qualifications(1).attribute14
2544 ,p_attribute15 => p_qualifications(1).attribute15
2545 ,p_attribute16 => p_qualifications(1).attribute16
2546 ,p_attribute17 => p_qualifications(1).attribute17
2547 ,p_attribute18 => p_qualifications(1).attribute18
2548 ,p_attribute19 => p_qualifications(1).attribute19
2549 ,p_attribute20 => p_qualifications(1).attribute20
2550 ,p_qua_information_category => p_qualifications(1).qua_information_category
2551 ,p_qua_information1 => p_qualifications(1).qua_information1
2552 ,p_qua_information2 => p_qualifications(1).qua_information2
2553 ,p_qua_information3 => p_qualifications(1).qua_information3
2554 ,p_qua_information4 => p_qualifications(1).qua_information4
2555 ,p_qua_information5 => p_qualifications(1).qua_information5
2556 ,p_qua_information6 => p_qualifications(1).qua_information6
2557 ,p_qua_information7 => p_qualifications(1).qua_information7
2558 ,p_qua_information8 => p_qualifications(1).qua_information8
2559 ,p_qua_information9 => p_qualifications(1).qua_information9
2560 ,p_qua_information10 => p_qualifications(1).qua_information10
2561 ,p_qua_information11 => p_qualifications(1).qua_information11
2562 ,p_qua_information12 => p_qualifications(1).qua_information12
2563 ,p_qua_information13 => p_qualifications(1).qua_information13
2564 ,p_qua_information14 => p_qualifications(1).qua_information14
2565 ,p_qua_information15 => p_qualifications(1).qua_information15
2566 ,p_qua_information16 => p_qualifications(1).qua_information16
2567 ,p_qua_information17 => p_qualifications(1).qua_information17
2568 ,p_qua_information18 => p_qualifications(1).qua_information18
2569 ,p_qua_information19 => p_qualifications(1).qua_information19
2570 ,p_qua_information20 => p_qualifications(1).qua_information20
2571 );
2572
2573 --insert subject for both qua and award
2574 hr_utility.set_location('Entering For Loop:'||l_proc,55);
2575 for i in 1..NVL(p_qua_subjects.count, 0) loop
2576 if p_qua_subjects(i).delete_flag = 'N' then
2577 per_sub_ins.ins
2578 (p_validate => FALSE
2579 ,p_effective_date => sysdate
2580 ,p_subjects_taken_id => l_subjects_taken_id
2581 ,p_start_date => p_qua_subjects(i).start_date
2582 ,p_end_date => p_qua_subjects(i).end_date
2583 ,p_major => p_qua_subjects(i).major
2584 ,p_subject_status => p_qua_subjects(i).subject_status
2585 ,p_subject => p_qua_subjects(i).subject
2586 ,p_grade_attained => p_qua_subjects(i).grade_attained
2587 ,p_qualification_id => l_qualification_id
2588 ,p_object_version_number => l_pst_object_version_number
2589 ,p_attribute_category => p_qua_subjects(i).attribute_category
2590 ,p_attribute1 => p_qua_subjects(i).attribute1
2591 ,p_attribute2 => p_qua_subjects(i).attribute2
2592 ,p_attribute3 => p_qua_subjects(i).attribute3
2593 ,p_attribute4 => p_qua_subjects(i).attribute4
2594 ,p_attribute5 => p_qua_subjects(i).attribute5
2595 ,p_attribute6 => p_qua_subjects(i).attribute6
2596 ,p_attribute7 => p_qua_subjects(i).attribute7
2597 ,p_attribute8 => p_qua_subjects(i).attribute8
2598 ,p_attribute9 => p_qua_subjects(i).attribute9
2599 ,p_attribute10 => p_qua_subjects(i).attribute10
2600 ,p_attribute11 => p_qua_subjects(i).attribute11
2601 ,p_attribute12 => p_qua_subjects(i).attribute12
2602 ,p_attribute13 => p_qua_subjects(i).attribute13
2603 ,p_attribute14 => p_qua_subjects(i).attribute14
2604 ,p_attribute15 => p_qua_subjects(i).attribute15
2605 ,p_attribute16 => p_qua_subjects(i).attribute16
2606 ,p_attribute17 => p_qua_subjects(i).attribute17
2607 ,p_attribute18 => p_qua_subjects(i).attribute18
2608 ,p_attribute19 => p_qua_subjects(i).attribute19
2609 ,p_attribute20 => p_qua_subjects(i).attribute20
2610 ,p_sub_information_category => p_qua_subjects(i).sub_information_category
2611 ,p_sub_information1 => p_qua_subjects(i).sub_information1
2612 ,p_sub_information2 => p_qua_subjects(i).sub_information2
2613 ,p_sub_information3 => p_qua_subjects(i).sub_information3
2614 ,p_sub_information4 => p_qua_subjects(i).sub_information4
2615 ,p_sub_information5 => p_qua_subjects(i).sub_information5
2616 ,p_sub_information6 => p_qua_subjects(i).sub_information6
2617 ,p_sub_information7 => p_qua_subjects(i).sub_information7
2618 ,p_sub_information8 => p_qua_subjects(i).sub_information8
2619 ,p_sub_information9 => p_qua_subjects(i).sub_information9
2620 ,p_sub_information10 => p_qua_subjects(i).sub_information10
2621 ,p_sub_information11 => p_qua_subjects(i).sub_information11
2622 ,p_sub_information12 => p_qua_subjects(i).sub_information12
2623 ,p_sub_information13 => p_qua_subjects(i).sub_information13
2624 ,p_sub_information14 => p_qua_subjects(i).sub_information14
2625 ,p_sub_information15 => p_qua_subjects(i).sub_information15
2626 ,p_sub_information16 => p_qua_subjects(i).sub_information16
2627 ,p_sub_information17 => p_qua_subjects(i).sub_information17
2628 ,p_sub_information18 => p_qua_subjects(i).sub_information18
2629 ,p_sub_information19 => p_qua_subjects(i).sub_information19
2630 ,p_sub_information20 => p_qua_subjects(i).sub_information20
2631 );
2632
2633 per_sbt_ins.ins_tl
2634 (p_language_code => hr_api.userenv_lang
2635 ,p_subjects_taken_id => l_subjects_taken_id
2636 ,p_grade_attained => p_qua_subjects(i).grade_attained
2637 );
2638 end if;
2639 end loop;
2640 hr_utility.set_location('Exiting For Loop:'||l_proc,60);
2641 else --update an existing qua or award
2642
2643 l_pq_object_version_number := p_qualifications(1).object_version_number;
2644 l_pea_object_version_number := p_qua_attendance(1).object_version_number;
2645
2646 --update attendance only for qua
2647 if p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION then
2648 OPEN csr_school_changed(l_establishment
2649 ,p_qualifications(1).attendance_id
2650 ,p_qua_attendance(1).establishment_id);
2651 FETCH csr_school_changed INTO l_dummy;
2652 IF csr_school_changed%found THEN
2653 CLOSE csr_school_changed;
2654 l_school_changed := 'N';
2655 ELSE
2656 CLOSE csr_school_changed;
2657 l_school_changed := 'Y';
2658 END IF;
2659
2660 if l_school_changed = 'Y' and l_establishment is null then --establishment_id changed.
2661 --if l_attendance_id is null then --create a new attendance
2662 per_esa_ins.ins
2663 (p_validate => FALSE
2664 ,p_effective_date => sysdate
2665 ,p_attendance_id => l_attendance_id
2666 ,p_business_group_id => p_qualifications(1).business_group_id
2667 ,p_person_id => p_selected_person_id
2668 ,p_establishment_id => l_establishment_id
2669 ,p_establishment => decode_value(p_expression =>
2670 l_establishment_id IS NOT null
2671 ,p_true => null
2672 ,p_false => p_qua_attendance(1).establishment)
2673 ,p_attended_start_date => p_qua_attendance(1).attended_start_date
2674 ,p_attended_end_date => p_qua_attendance(1).attended_end_date
2675 ,p_full_time => NVL(p_qua_attendance(1).full_time,'N')
2676 ,p_attribute_category => p_qua_attendance(1).attribute_category
2677 ,p_attribute1 => p_qua_attendance(1).attribute1
2678 ,p_attribute2 => p_qua_attendance(1).attribute2
2679 ,p_attribute3 => p_qua_attendance(1).attribute3
2680 ,p_attribute4 => p_qua_attendance(1).attribute4
2681 ,p_attribute5 => p_qua_attendance(1).attribute5
2682 ,p_attribute6 => p_qua_attendance(1).attribute6
2683 ,p_attribute7 => p_qua_attendance(1).attribute7
2684 ,p_attribute8 => p_qua_attendance(1).attribute8
2685 ,p_attribute9 => p_qua_attendance(1).attribute9
2686 ,p_attribute10 => p_qua_attendance(1).attribute10
2687 ,p_attribute11 => p_qua_attendance(1).attribute11
2688 ,p_attribute12 => p_qua_attendance(1).attribute12
2689 ,p_attribute13 => p_qua_attendance(1).attribute13
2690 ,p_attribute14 => p_qua_attendance(1).attribute14
2691 ,p_attribute15 => p_qua_attendance(1).attribute15
2692 ,p_attribute16 => p_qua_attendance(1).attribute16
2693 ,p_attribute17 => p_qua_attendance(1).attribute17
2694 ,p_attribute18 => p_qua_attendance(1).attribute18
2695 ,p_attribute19 => p_qua_attendance(1).attribute19
2696 ,p_attribute20 => p_qua_attendance(1).attribute20
2697 ,p_object_version_number => l_pea_object_version_number);
2698 else
2699 per_esa_upd.upd
2700 (p_validate => FALSE
2701 ,p_effective_date => sysdate
2702 ,p_attendance_id => l_old_attendance_id
2703 ,p_establishment_id => l_establishment_id
2704 ,p_establishment => decode_value(p_expression =>
2705 l_establishment_id IS NOT null
2706 ,p_true => null
2707 ,p_false => p_qua_attendance(1).establishment)
2708 ,p_attended_start_date => p_qua_attendance(1).attended_start_date
2709 ,p_attended_end_date => p_qua_attendance(1).attended_end_date
2710 ,p_full_time => NVL(p_qua_attendance(1).full_time,'N')
2711 ,p_attribute_category => p_qua_attendance(1).attribute_category
2712 ,p_attribute1 => p_qua_attendance(1).attribute1
2713 ,p_attribute2 => p_qua_attendance(1).attribute2
2714 ,p_attribute3 => p_qua_attendance(1).attribute3
2715 ,p_attribute4 => p_qua_attendance(1).attribute4
2716 ,p_attribute5 => p_qua_attendance(1).attribute5
2717 ,p_attribute6 => p_qua_attendance(1).attribute6
2718 ,p_attribute7 => p_qua_attendance(1).attribute7
2719 ,p_attribute8 => p_qua_attendance(1).attribute8
2720 ,p_attribute9 => p_qua_attendance(1).attribute9
2721 ,p_attribute10 => p_qua_attendance(1).attribute10
2722 ,p_attribute11 => p_qua_attendance(1).attribute11
2723 ,p_attribute12 => p_qua_attendance(1).attribute12
2724 ,p_attribute13 => p_qua_attendance(1).attribute13
2725 ,p_attribute14 => p_qua_attendance(1).attribute14
2726 ,p_attribute15 => p_qua_attendance(1).attribute15
2727 ,p_attribute16 => p_qua_attendance(1).attribute16
2728 ,p_attribute17 => p_qua_attendance(1).attribute17
2729 ,p_attribute18 => p_qua_attendance(1).attribute18
2730 ,p_attribute19 => p_qua_attendance(1).attribute19
2731 ,p_attribute20 => p_qua_attendance(1).attribute20
2732 ,p_object_version_number => l_pea_object_version_number);
2733 l_attendance_id := l_old_attendance_id;
2734 end if;
2735 end if; --end update attendance
2736
2737 -- update qua record for both qua and award
2738 --per_qua_upd.upd
2739 PER_QUALIFICATIONS_API.UPDATE_QUALIFICATION
2740 (p_validate => FALSE
2741 ,p_effective_date => sysdate
2742 ,p_qualification_id => p_qualifications(1).qualification_id
2743 --,p_business_group_id => p_qualifications(1).business_group_id
2744 ,p_object_version_number =>
2745 l_pq_object_version_number
2746 --,p_person_id => decode_value(p_expression =>
2747 -- l_attendance_id IS null
2748 --,p_true => p_qualifications(1).person_id
2749 --,p_false => null)
2750 ,p_title => p_qualifications(1).title
2751 ,p_status => p_qualifications(1).status
2752 ,p_start_date => p_qualifications(1).start_date
2753 ,p_end_date => p_qualifications(1).end_date
2754 ,p_attendance_id => l_attendance_id
2755 ,p_grade_attained => p_qualifications(1).grade_attained
2756 ,p_awarded_date => p_qualifications(1).awarded_date
2757 ,p_fee => p_qualifications(1).fee
2758 ,p_fee_currency => p_qualifications(1).fee_currency
2759 ,p_training_completed_amount =>
2760 p_qualifications(1).training_completed_amount
2761 ,p_reimbursement_arrangements =>
2762 p_qualifications(1).reimbursement_arrangements
2763 ,p_training_completed_units =>
2764 p_qualifications(1).training_completed_units
2765 ,p_total_training_amount =>
2766 p_qualifications(1).total_training_amount
2767 ,p_license_number => p_qualifications(1).license_number
2768 ,p_expiry_date => p_qualifications(1).expiry_date
2769 ,p_license_restrictions => p_qualifications(1).license_restrictions
2770 ,p_awarding_body => p_qualifications(1).awarding_body
2771 ,p_tuition_method => p_qualifications(1).tuition_method
2772 ,p_group_ranking => p_qualifications(1).group_ranking
2773 ,p_comments => substr(p_qualifications(1).comments,1,2000)
2774 ,p_attribute_category => p_qualifications(1).attribute_category
2775 ,p_attribute1 => p_qualifications(1).attribute1
2776 ,p_attribute2 => p_qualifications(1).attribute2
2777 ,p_attribute3 => p_qualifications(1).attribute3
2778 ,p_attribute4 => p_qualifications(1).attribute4
2779 ,p_attribute5 => p_qualifications(1).attribute5
2780 ,p_attribute6 => p_qualifications(1).attribute6
2781 ,p_attribute7 => p_qualifications(1).attribute7
2782 ,p_attribute8 => p_qualifications(1).attribute8
2783 ,p_attribute9 => p_qualifications(1).attribute9
2784 ,p_attribute10 => p_qualifications(1).attribute10
2785 ,p_attribute11 => p_qualifications(1).attribute11
2786 ,p_attribute12 => p_qualifications(1).attribute12
2787 ,p_attribute13 => p_qualifications(1).attribute13
2788 ,p_attribute14 => p_qualifications(1).attribute14
2789 ,p_attribute15 => p_qualifications(1).attribute15
2790 ,p_attribute16 => p_qualifications(1).attribute16
2791 ,p_attribute17 => p_qualifications(1).attribute17
2792 ,p_attribute18 => p_qualifications(1).attribute18
2793 ,p_attribute19 => p_qualifications(1).attribute19
2794 ,p_attribute20 => p_qualifications(1).attribute20
2795 ,p_qua_information_category => p_qualifications(1).qua_information_category
2796 ,p_qua_information1 => p_qualifications(1).qua_information1
2797 ,p_qua_information2 => p_qualifications(1).qua_information2
2798 ,p_qua_information3 => p_qualifications(1).qua_information3
2799 ,p_qua_information4 => p_qualifications(1).qua_information4
2800 ,p_qua_information5 => p_qualifications(1).qua_information5
2801 ,p_qua_information6 => p_qualifications(1).qua_information6
2802 ,p_qua_information7 => p_qualifications(1).qua_information7
2803 ,p_qua_information8 => p_qualifications(1).qua_information8
2804 ,p_qua_information9 => p_qualifications(1).qua_information9
2805 ,p_qua_information10 => p_qualifications(1).qua_information10
2806 ,p_qua_information11 => p_qualifications(1).qua_information11
2807 ,p_qua_information12 => p_qualifications(1).qua_information12
2808 ,p_qua_information13 => p_qualifications(1).qua_information13
2809 ,p_qua_information14 => p_qualifications(1).qua_information14
2810 ,p_qua_information15 => p_qualifications(1).qua_information15
2811 ,p_qua_information16 => p_qualifications(1).qua_information16
2812 ,p_qua_information17 => p_qualifications(1).qua_information17
2813 ,p_qua_information18 => p_qualifications(1).qua_information18
2814 ,p_qua_information19 => p_qualifications(1).qua_information19
2815 ,p_qua_information20 => p_qualifications(1).qua_information20
2816 );
2817
2818 -- update subject record for both qua and award
2819 hr_utility.set_location('Entering For Loop:'||l_proc,65);
2820 FOR i IN 1..NVL(p_qua_subjects.count, 0) LOOP
2821 l_subjects_taken_id := p_qua_subjects(i).subjects_taken_id;
2822 l_pst_object_version_number :=
2823 p_qua_subjects(i).object_version_number;
2824 IF p_qua_subjects(i).delete_flag = 'N' THEN
2825 IF l_subjects_taken_id IS null THEN
2826
2827 --insert subject
2828 per_sub_ins.ins
2829 (p_validate => FALSE
2830 ,p_effective_date => sysdate
2831 ,p_subjects_taken_id => l_subjects_taken_id
2832 ,p_start_date => p_qua_subjects(i).start_date
2833 ,p_end_date => p_qua_subjects(i).end_date
2834 ,p_major => p_qua_subjects(i).major
2835 ,p_subject_status => p_qua_subjects(i).subject_status
2836 ,p_subject => p_qua_subjects(i).subject
2837 ,p_grade_attained => p_qua_subjects(i).grade_attained
2838 ,p_qualification_id => p_qualifications(1).qualification_id
2839 ,p_object_version_number => l_pst_object_version_number
2840 ,p_attribute_category => p_qua_subjects(i).attribute_category
2841 ,p_attribute1 => p_qua_subjects(i).attribute1
2842 ,p_attribute2 => p_qua_subjects(i).attribute2
2843 ,p_attribute3 => p_qua_subjects(i).attribute3
2844 ,p_attribute4 => p_qua_subjects(i).attribute4
2845 ,p_attribute5 => p_qua_subjects(i).attribute5
2846 ,p_attribute6 => p_qua_subjects(i).attribute6
2847 ,p_attribute7 => p_qua_subjects(i).attribute7
2848 ,p_attribute8 => p_qua_subjects(i).attribute8
2849 ,p_attribute9 => p_qua_subjects(i).attribute9
2850 ,p_attribute10 => p_qua_subjects(i).attribute10
2851 ,p_attribute11 => p_qua_subjects(i).attribute11
2852 ,p_attribute12 => p_qua_subjects(i).attribute12
2853 ,p_attribute13 => p_qua_subjects(i).attribute13
2854 ,p_attribute14 => p_qua_subjects(i).attribute14
2855 ,p_attribute15 => p_qua_subjects(i).attribute15
2856 ,p_attribute16 => p_qua_subjects(i).attribute16
2857 ,p_attribute17 => p_qua_subjects(i).attribute17
2858 ,p_attribute18 => p_qua_subjects(i).attribute18
2859 ,p_attribute19 => p_qua_subjects(i).attribute19
2860 ,p_attribute20 => p_qua_subjects(i).attribute20
2861 ,p_sub_information_category => p_qua_subjects(i).sub_information_category
2862 ,p_sub_information1 => p_qua_subjects(i).sub_information1
2863 ,p_sub_information2 => p_qua_subjects(i).sub_information2
2864 ,p_sub_information3 => p_qua_subjects(i).sub_information3
2865 ,p_sub_information4 => p_qua_subjects(i).sub_information4
2866 ,p_sub_information5 => p_qua_subjects(i).sub_information5
2867 ,p_sub_information6 => p_qua_subjects(i).sub_information6
2868 ,p_sub_information7 => p_qua_subjects(i).sub_information7
2869 ,p_sub_information8 => p_qua_subjects(i).sub_information8
2870 ,p_sub_information9 => p_qua_subjects(i).sub_information9
2871 ,p_sub_information10 => p_qua_subjects(i).sub_information10
2872 ,p_sub_information11 => p_qua_subjects(i).sub_information11
2873 ,p_sub_information12 => p_qua_subjects(i).sub_information12
2874 ,p_sub_information13 => p_qua_subjects(i).sub_information13
2875 ,p_sub_information14 => p_qua_subjects(i).sub_information14
2876 ,p_sub_information15 => p_qua_subjects(i).sub_information15
2877 ,p_sub_information16 => p_qua_subjects(i).sub_information16
2878 ,p_sub_information17 => p_qua_subjects(i).sub_information17
2879 ,p_sub_information18 => p_qua_subjects(i).sub_information18
2880 ,p_sub_information19 => p_qua_subjects(i).sub_information19
2881 ,p_sub_information20 => p_qua_subjects(i).sub_information20
2882 );
2883 per_sbt_ins.ins_tl
2884 (p_language_code => hr_api.userenv_lang
2885 ,p_subjects_taken_id => l_subjects_taken_id
2886 ,p_grade_attained => p_qua_subjects(i).grade_attained
2887 );
2888 ELSE
2889
2890 --update subject
2891 per_sub_upd.upd(p_validate => FALSE
2892 ,p_effective_date => sysdate
2893 ,p_subjects_taken_id => l_subjects_taken_id
2894 ,p_start_date => p_qua_subjects(i).start_date
2895 ,p_end_date => p_qua_subjects(i).end_date
2896 ,p_major => p_qua_subjects(i).major
2897 ,p_subject_status => p_qua_subjects(i).subject_status
2898 ,p_subject => p_qua_subjects(i).subject
2899 ,p_grade_attained => p_qua_subjects(i).grade_attained
2900 ,p_qualification_id => p_qualifications(1).qualification_id
2901 ,p_object_version_number => l_pst_object_version_number
2902 ,p_attribute_category => p_qua_subjects(i).attribute_category
2903 ,p_attribute1 => p_qua_subjects(i).attribute1
2904 ,p_attribute2 => p_qua_subjects(i).attribute2
2905 ,p_attribute3 => p_qua_subjects(i).attribute3
2906 ,p_attribute4 => p_qua_subjects(i).attribute4
2907 ,p_attribute5 => p_qua_subjects(i).attribute5
2908 ,p_attribute6 => p_qua_subjects(i).attribute6
2909 ,p_attribute7 => p_qua_subjects(i).attribute7
2910 ,p_attribute8 => p_qua_subjects(i).attribute8
2911 ,p_attribute9 => p_qua_subjects(i).attribute9
2912 ,p_attribute10 => p_qua_subjects(i).attribute10
2913 ,p_attribute11 => p_qua_subjects(i).attribute11
2914 ,p_attribute12 => p_qua_subjects(i).attribute12
2915 ,p_attribute13 => p_qua_subjects(i).attribute13
2916 ,p_attribute14 => p_qua_subjects(i).attribute14
2917 ,p_attribute15 => p_qua_subjects(i).attribute15
2918 ,p_attribute16 => p_qua_subjects(i).attribute16
2919 ,p_attribute17 => p_qua_subjects(i).attribute17
2920 ,p_attribute18 => p_qua_subjects(i).attribute18
2921 ,p_attribute19 => p_qua_subjects(i).attribute19
2922 ,p_attribute20 => p_qua_subjects(i).attribute20
2923 ,p_sub_information_category => p_qua_subjects(i).sub_information_category
2924 ,p_sub_information1 => p_qua_subjects(i).sub_information1
2925 ,p_sub_information2 => p_qua_subjects(i).sub_information2
2926 ,p_sub_information3 => p_qua_subjects(i).sub_information3
2927 ,p_sub_information4 => p_qua_subjects(i).sub_information4
2928 ,p_sub_information5 => p_qua_subjects(i).sub_information5
2929 ,p_sub_information6 => p_qua_subjects(i).sub_information6
2930 ,p_sub_information7 => p_qua_subjects(i).sub_information7
2931 ,p_sub_information8 => p_qua_subjects(i).sub_information8
2932 ,p_sub_information9 => p_qua_subjects(i).sub_information9
2933 ,p_sub_information10 => p_qua_subjects(i).sub_information10
2934 ,p_sub_information11 => p_qua_subjects(i).sub_information11
2935 ,p_sub_information12 => p_qua_subjects(i).sub_information12
2936 ,p_sub_information13 => p_qua_subjects(i).sub_information13
2937 ,p_sub_information14 => p_qua_subjects(i).sub_information14
2938 ,p_sub_information15 => p_qua_subjects(i).sub_information15
2939 ,p_sub_information16 => p_qua_subjects(i).sub_information16
2940 ,p_sub_information17 => p_qua_subjects(i).sub_information17
2941 ,p_sub_information18 => p_qua_subjects(i).sub_information18
2942 ,p_sub_information19 => p_qua_subjects(i).sub_information19
2943 ,p_sub_information20 => p_qua_subjects(i).sub_information20
2944 );
2945 per_sbt_upd.upd_tl
2946 (p_language_code => hr_api.userenv_lang
2947 ,p_subjects_taken_id => l_subjects_taken_id
2948 ,p_grade_attained => p_qua_subjects(i).grade_attained
2949 );
2950
2951 END IF;
2952 ELSE
2953 IF l_subjects_taken_id IS NOT null THEN
2954 per_sbt_del.del_tl
2955 (p_subjects_taken_id => l_subjects_taken_id);
2956 per_sub_del.del(p_validate => FALSE
2957 ,p_subjects_taken_id => l_subjects_taken_id
2958 ,p_object_version_number => l_pst_object_version_number);
2959 END IF;
2960 END IF;
2961 END LOOP;
2962 hr_utility.set_location('Exiting For Loop:'||l_proc,70);
2963 END IF;
2964
2965 IF p_validate = TRUE THEN
2966 rollback to validate_qua;
2967 END IF;
2968 hr_utility.set_location('Exiting:'||l_proc, 15);
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 hr_utility.set_location('Exception:Others'||l_proc,555);
2972 rollback to validate_qua;
2973 raise;
2974 END validate_api;
2975
2976 -- End of Procedure validate_api
2977
2978 -- Start of Procedure start_transaction
2979 Procedure start_transaction(itemtype in varchar2
2980 ,itemkey in varchar2
2981 ,actid in number
2982 ,funmode in varchar2
2983 ,p_selected_person_id in number
2984 ,p_creator_person_id in number
2985 ,result out nocopy varchar2) is
2986 -- --------------------------------------------------------------------------
2987 -- declare local variables
2988 -- --------------------------------------------------------------------------
2989 l_transaction_privilege hr_api_transactions.transaction_privilege%type;
2990 l_transaction_id hr_api_transactions.transaction_id%type;
2991 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
2992 l_proc varchar2(72) := g_package||'start_transaction';
2993
2994 -- l_person_id hr_api_transactions.creator_person_id%type := p_selected_person_id;
2995
2996 Cursor c_get_transaction_step_id
2997 is
2998 select
2999 transaction_step_id
3000 from
3001 hr_api_transaction_steps
3002 where
3003 transaction_id = l_transaction_id;
3004
3005 Begin
3006
3007 hr_utility.set_location('Entering:'||l_proc, 5);
3008 if funmode = 'RUN' then
3009 hr_utility.set_location('funmode=RUN:'||l_proc,10);
3010 savepoint start_transaction;
3011
3012 -- check to see if the TRANSACTION_ID attribute has been created
3013 if hr_workflow_utility.item_attribute_exists
3014 (p_item_type => itemtype
3015 ,p_item_key => itemkey
3016 ,p_name => 'TRANSACTION_ID') then
3017
3018 -- the TRANSACTION_ID exists so ensure that it is null
3019
3020
3021 if hr_transaction_ss.get_transaction_id
3022 (p_item_type => itemtype
3023 ,p_item_key => itemkey) is not null then
3024
3025 -- a current transaction is in progress we cannot overwrite it
3026 -- get the Transaction Step Id
3027 hr_utility.set_location('If itemtype and itemKey is not null:'||l_proc,15);
3028 l_transaction_id := hr_transaction_ss.get_transaction_id
3029 (p_item_type => itemtype
3030 ,p_item_key => itemkey);
3031 open c_get_transaction_step_id;
3032 fetch c_get_transaction_step_id into l_transaction_step_id;
3033 close c_get_transaction_step_id;
3034 hr_transaction_ss.delete_transaction_step
3035 (l_transaction_step_id,null,p_creator_person_id);
3036 end if;
3037 end if;
3038 -- the TRANSACTION_ID attribute has not been created. create it.
3039 hr_transaction_ss.start_transaction
3040 (itemtype => itemtype
3041 ,itemkey => itemkey
3042 ,actid => actid
3043 ,funmode => funmode
3044 ,p_selected_person_id => p_selected_person_id
3045 ,p_login_person_id => p_creator_person_id
3046 ,result => result);
3047
3048 result := 'SUCCESS';
3049
3050 elsif funmode = 'CANCEL' then
3051 null;
3052 end if;
3053
3054 hr_utility.set_location('Exiting:'||l_proc, 20);
3055 Exception
3056 when others then
3057 hr_utility.set_location('Exception:Others'||l_proc,555);
3058 rollback to start_transaction;
3059 raise;
3060 End start_transaction;
3061
3062 -- End of Procedure start_transaction
3063
3064 -- Start of Function get_pending_items
3065 Function get_pending_items
3066 (p_item_type in wf_items.item_type%type
3067 ,p_api_name in varchar2
3068 ,p_current_person_id in number
3069 ,p_result_code in varchar2
3070 ) return hr_workflow_service.active_wf_trans_items_list is
3071
3072 -- Local cursor definitions
3073 cursor csr_pending_items is
3074 select transaction_step_id, activity_id, t.item_key
3075 from hr_api_transactions t, hr_api_transaction_steps ts
3076 ,wf_item_activity_statuses s
3077 where t.selected_person_id = p_current_person_id
3078 and t.status = 'Y'
3079 and t.item_type = p_item_type
3080 and t.transaction_id = ts.transaction_id
3081 and ts.api_name = p_api_name
3082 and t.item_type = s.item_type
3083 and t.item_key = s.item_key
3084 and s.activity_result_code = p_result_code;
3085
3086 -- Local variable definitions
3087 l_count integer;
3088 l_active_wf_items_list hr_workflow_service.active_wf_trans_items_list;
3089 l_proc varchar2(72) := g_package||'get_pending_items';
3090 Begin
3091 hr_utility.set_location('Entering:'||l_proc, 5);
3092 l_count := 0;
3093 hr_utility.set_location('Entering For Loop:'||l_proc,10);
3094 For I in csr_pending_items Loop
3095 l_count := l_count + 1;
3096 l_active_wf_items_list(l_count).active_item_key := I.item_key;
3097 l_active_wf_items_list(l_count).activity_id := I.activity_id;
3098 l_active_wf_items_list(l_count).trans_step_id := I.transaction_step_id;
3099 End Loop;
3100 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
3101 hr_utility.set_location('Exiting:'||l_proc, 20);
3102 return l_active_wf_items_list;
3103 End get_pending_items;
3104
3105 -- Start of Procedure get_pending_transaction_steps
3106
3107 Procedure get_pending_transaction_steps
3108 (p_item_type in varchar2
3109 ,p_selected_person_id in varchar2
3110 ,p_mode in varchar2
3111 ,p_process_name in varchar2
3112 ,p_activity_name in varchar2
3113 ,p_transaction_step_id out nocopy hr_util_misc_web.g_varchar2_tab_type) IS
3114
3115 l_active_item_keys hr_workflow_service.active_wf_trans_items_list;
3116 l_pending_count number;
3117 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
3118 l_trs_object_version_number hr_api_transaction_steps.object_version_number%type;
3119 l_is_workflow_complete varchar2(1) := 'N';
3120 j number := 1;
3121 l_result_code varchar2(30);
3122 l_proc varchar2(72) := g_package||'get_pending_transaction_steps';
3123
3124 BEGIN
3125 hr_utility.set_location('Entering:'||l_proc, 5);
3126
3127 IF p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN
3128 hr_utility.set_location('mode=EDUCATION :'||l_proc,10);
3129 l_result_code := HR_QUA_AWARDS_UTIL_SS.EDUCATION_CHANGED;
3130 ELSE
3131 hr_utility.set_location('mode!=Education:'||l_proc,15);
3132 l_result_code := HR_QUA_AWARDS_UTIL_SS.AWARD_CHANGED;
3133 END IF;
3134
3135 l_active_item_keys :=
3136 get_pending_items
3137 (p_item_type => p_item_type
3138 ,p_api_name => HR_QUA_AWARDS_UTIL_SS.API_NAME
3139 ,p_current_person_id => p_selected_person_id
3140 ,p_result_code => l_result_code
3141 );
3142
3143 l_pending_count := NVL(l_active_item_keys.count,0);
3144
3145 hr_utility.set_location('Entering For Loop:'||l_proc,20);
3146 FOR i IN 1..l_pending_count LOOP
3147 p_transaction_step_id(j) := l_active_item_keys(i).trans_step_id;
3148 j := j +1;
3149 END LOOP;
3150 hr_utility.set_location('Exiting For Loop:'||l_proc,25);
3151
3152 hr_utility.set_location('Exiting:'||l_proc, 30);
3153 END get_pending_transaction_steps;
3154
3155
3156 -- End of Procedure get_pending_transaction_steps
3157
3158 -- start of procedure check_errors
3159
3160 PROCEDURE check_errors
3161 (p_ignore_sub_date_boundaries in varchar2
3162 ,p_mode in varchar2
3163 ,p_qualifications in SSHR_QUA_TAB_TYP
3164 ,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
3165 ,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
3166 ,p_error_message out nocopy varchar2
3167 ,p_subjects_error_message out nocopy varchar2) IS
3168
3169 cursor c1(p_start_date in date,
3170 p_end_date in date,
3171 p_person_id in number,
3172 p_business_group_id in number,
3173 p_qualification_type_id in number,
3174 p_attendance_id in number,
3175 p_title in varchar2,
3176 p_qualification_id number) is
3177 select null
3178 from per_qualifications per
3179 where per.qualification_type_id = p_qualification_type_id
3180 --and nvl(per.party_id,-1) = nvl(p_party_id,-1)
3181 and nvl(per.person_id,-1) = nvl(p_person_id,-1)
3182 and nvl(per.attendance_id,-1) = nvl(p_attendance_id,-1)
3183 and per.business_group_id +0 = p_business_group_id
3184 and per.title = p_title
3185 and per.qualification_id <> nvl(p_qualification_id,-1)
3186 and (nvl(per.start_date,hr_api.g_sot)
3187 between nvl(p_start_date,hr_api.g_sot)
3188 and nvl(p_end_date,hr_api.g_eot)
3189 or nvl(per.end_date,nvl(per.start_date,p_start_date))
3190 between nvl(p_start_date,hr_api.g_sot)
3191 and nvl(p_end_date,hr_api.g_eot));
3192
3193 l_date_test date;
3194 l_pea_start_date date;
3195 l_pea_end_date date;
3196 l_number_test number;
3197 l_error boolean;
3198 l_pq_date_error boolean;
3199 l_pea_date_error boolean;
3200 l_length number;
3201 l_start_date date;
3202 l_end_date date;
3203 l_fee1 number(15,2);
3204 l_fee2 number;
3205 l_dummy varchar2(1);
3206 l_proc varchar2(72) := g_package||'check_errors';
3207
3208
3209 BEGIN
3210
3211 hr_utility.set_location('Entering:'||l_proc, 5);
3212 BEGIN
3213 l_length := length(p_qualifications(1).comments);
3214 IF l_length > 2000 THEN
3215 --fnd_message.set_name('PER','HR_COMMENT_EXCEED_MAX_LENGTH');
3216 --hr_utility.raise_error;
3217 hr_utility.set_location('length>2000:'||l_proc,10);
3218 p_error_message := nvl(p_error_message,'')||'!'||'|'||'Comments'||'|'||'PER'||'|'||'HR_COMMENT_EXCEED_MAX_LENGTH'||'|'||'!';
3219 END IF;
3220 EXCEPTION
3221 WHEN OTHERS THEN
3222 hr_utility.set_location('Exception:Others'||l_proc,555);
3223 raise;
3224 END;
3225
3226 IF p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN
3227 --
3228 -- Business Rule Mapping
3229 -- =====================
3230 -- CHK_ATT_OVERLAP
3231 -- CHK_ATTENDED_START_DATE
3232 -- CHK_ATTENDED_END_DATE
3233 -- CHK_ATT_TO_DATE
3234 hr_utility.set_location('mode is Education:'||l_proc,20);
3235 l_pea_date_error := FALSE;
3236
3237 BEGIN
3238 IF p_qua_attendance(1).attended_start_date is not null and
3239 p_qua_attendance(1).attended_end_date is not null and
3240 p_qua_attendance(1).attended_start_date >
3241 p_qua_attendance(1).attended_end_date THEN
3242 --fnd_message.set_name('PER','HR_51496_ESA_ATT_END_DATE');
3243 --hr_utility.raise_error;
3244 hr_utility.set_location('Startdate>EndDate:'||l_proc,25);
3245 p_error_message := nvl(p_error_message,'')||'!'||'|'||'AttendedStartDate'||'|'||'PER'||'|'||'HR_51496_ESA_ATT_END_DATE'||'|'||'!';
3246 END IF;
3247 EXCEPTION
3248 WHEN OTHERS THEN
3249 hr_utility.set_location('Exception:Others'||l_proc,555);
3250 l_pea_date_error := TRUE;
3251 raise;
3252 END;
3253
3254 --
3255 -- Business Rule Mapping
3256 -- =====================
3257 -- CHK_START_DATE
3258 -- CHK_END_DATE
3259 l_pq_date_error := FALSE;
3260
3261 IF l_pq_date_error = FALSE AND l_pea_date_error = FALSE THEN
3262 -- the start date and end date are valid values.
3263 -- qualification record that uses the establishment
3264 -- attendance id do not have a qualification start date that falls
3265 -- outside of the attendance start dates.
3266 IF p_qualifications(1).start_date is not null and
3267 p_qualifications(1).end_date is not null and
3268 p_qualifications(1).start_date > p_qualifications(1).end_date
3269 THEN
3270 --fnd_message.set_name('PAY','HR_51853_QUA_DATE_INV');
3271 --hr_utility.raise_error;
3272 hr_utility.set_location('StartDate>endDate:'||l_proc,30);
3273 p_error_message := nvl(p_error_message,'')||'!'||'|'||'StartDate'||'|'||'PAY'||'|'||'HR_51853_QUA_DATE_INV'||'|'||'!';
3274 END IF;
3275 IF p_mode = HR_QUA_AWARDS_UTIL_SS.EDUCATION THEN
3276 IF p_qualifications(1).start_date is not null and
3277 p_qua_attendance(1).attended_start_date is not null and
3278 p_qualifications(1).start_date < p_qua_attendance(1).attended_start_date
3279 THEN
3280 --fnd_message.set_name('PAY','HR_51841_QUA_DATES_OUT_ESA');
3281 --hr_utility.raise_error;
3282 hr_utility.set_location('startdate<attended_start_date:'||l_proc,35);
3283 p_error_message := nvl(p_error_message,'')||'!'||'|'||'StartDate'||'|'||'PAY'||'|'||'HR_51841_QUA_DATES_OUT_ESA'||'|'||'!';
3284 END IF;
3285 IF p_qualifications(1).end_date is not null
3286 and p_qua_attendance(1).attended_end_date is not null
3287 and p_qualifications(1).end_date > p_qua_attendance(1).attended_end_date
3288 THEN
3289 --fnd_message.set_name('PAY','HR_51841_QUA_DATES_OUT_ESA');
3290 --hr_utility.raise_error;
3291 hr_utility.set_location('end_date>attended_end_date:'||l_proc,40);
3292 p_error_message := nvl(p_error_message,'')||'!'||'|'||'EndDate'||'|'||'PAY'||'|'||'HR_51841_QUA_DATES_OUT_ESA'||'|'||'!';
3293 END IF;
3294 END IF;
3295 END IF;
3296
3297 IF l_pq_date_error = FALSE THEN
3298 BEGIN
3299 --
3300 -- Business Rule Mapping
3301 -- =====================
3302 -- CHK_QUAL_OVERLAP
3303 hr_utility.set_location('date_error=FALSE:'||l_proc,45);
3304 OPEN c1(p_qualifications(1).start_date,
3305 p_qualifications(1).end_date,
3306 p_qualifications(1).person_id,
3307 p_qualifications(1).business_group_id,
3308 p_qualifications(1).qualification_type_id,
3309 p_qualifications(1).attendance_id,
3310 p_qualifications(1).title,
3311 p_qualifications(1).qualification_id);
3312 FETCH c1 into l_dummy;
3313 if c1%found then
3314 hr_utility.set_location('c1%found:'||l_proc,50);
3315 close c1;
3316 fnd_message.set_name('PAY','HR_51847_QUA_REC_EXISTS');
3317 hr_utility.raise_error;
3318 end if;
3319 CLOSE c1;
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 hr_utility.set_location('Exception:Others'||l_proc,555);
3323 raise;
3324 END;
3325 END IF;
3326 --
3327 -- Business Rule Mapping
3328 -- =====================
3329 -- CHK_FEE
3330 -- CHK_FEE_CURRENCY
3331 l_error := FALSE;
3332 BEGIN
3333 per_qua_bus.chk_fee
3334 (p_qualification_id => p_qualifications(1).qualification_id,
3335 p_fee => p_qualifications(1).fee,
3336 p_fee_currency => p_qualifications(1).fee_currency,
3337 p_object_version_number => p_qualifications(1).object_version_number);
3338 EXCEPTION
3339 WHEN OTHERS THEN
3340 hr_utility.set_location('Exception:Others'||l_proc,555);
3341 raise;
3342 END;
3343 END IF;
3344
3345 --
3346 -- Business Rule Mapping
3347 -- =====================
3348 -- CHK_AWARDED_DATE
3349 l_error := FALSE;
3350 IF l_error = FALSE AND l_pq_date_error = FALSE THEN
3351 BEGIN
3352 per_qua_bus.chk_awarded_date
3353 (p_qualification_id => p_qualifications(1).qualification_id,
3354 p_awarded_date => p_qualifications(1).awarded_date,
3355 p_start_date => p_qualifications(1).start_date,
3356 p_object_version_number => p_qualifications(1).object_version_number);
3357 EXCEPTION
3358 WHEN OTHERS THEN
3359 hr_utility.set_location('Exception:Others'||l_proc,555);
3360 raise;
3361 END;
3362 END IF;
3363
3364 --
3365 -- Business Rule Mapping
3366 -- =====================
3367 -- CHK_START_DATE
3368 -- CHK_END_DATE
3369 hr_utility.set_location('Entering For Loop:'||l_proc,55);
3370 FOR i IN 1..NVL(p_qua_subjects.count,0) LOOP
3371 IF p_qua_subjects(i).delete_flag = 'N' THEN
3372 l_error := FALSE;
3373 IF l_error = FALSE AND l_pq_date_error = FALSE THEN
3374 BEGIN
3375 -- the start date of the subject is before the end
3376 -- date of the subject.
3377 -- the start and end dates of the subject are within
3378 -- the start and end dates of the qualification.
3379 IF p_qua_subjects(i).start_date is not null and
3380 p_qua_subjects(i).end_date is not null and
3381 p_qua_subjects(i).start_date > p_qua_subjects(i).end_date
3382 THEN
3383 --fnd_message.set_name('PAY','HR_51816_SUB_START_DATE_INV');
3384 --hr_utility.raise_error;
3385 p_subjects_error_message := nvl(p_subjects_error_message,'')||'!'||'|'||to_char(i-1)||'|'||'StartDate'||'|'||'PAY'||'|'||'HR_51816_SUB_START_DATE_INV'||'|'||'!';
3386 END IF;
3387 IF p_ignore_sub_date_boundaries = 'N' THEN
3388 IF p_qua_subjects(i).start_date
3389 < p_qualifications(1).start_date THEN
3390 --fnd_message.set_name('PAY','HR_51817_SUB_START_DATE_QUAL');
3391 --hr_utility.raise_error;
3392 p_subjects_error_message := nvl(p_subjects_error_message,'')||'!'||'|'||to_char(i-1)||'|'||'StartDate'||'|'||'PAY'||'|'||'HR_51817_SUB_START_DATE_QUAL'||'|'||'!';
3393 END IF;
3394 END IF;
3395 EXCEPTION
3396 WHEN OTHERS THEN
3397 hr_utility.set_location('Exception:Others'||l_proc,555);
3398 raise;
3399 END;
3400 END IF;
3401 END IF;
3402 END LOOP;
3403 hr_utility.set_location('Exiting For Loop:'||l_proc,60);
3404 hr_utility.set_location('Exiting:'||l_proc, 65);
3405 Exception
3406 when others then
3407 hr_utility.set_location('Exception:Others'||l_proc,555);
3408 raise;
3409 END check_errors;
3410
3411 -- end of procedures check_errors
3412
3413 -- start of procedure get_entire_qua
3414 /*
3415 This method returns all the details for a Qualification/Award given a qualification id. This method
3416 is being called from the java code to get the old values in the review page.
3417 */
3418
3419
3420 PROCEDURE get_entire_qua
3421 (p_transaction_step_id in varchar2
3422 ,p_mode out nocopy varchar2
3423 ,p_qualifications out nocopy SSHR_QUA_TAB_TYP
3424 ,p_qua_subjects out nocopy SSHR_QUA_SUBJECT_TAB_TYP
3425 ,p_qua_attendance out nocopy SSHR_QUA_ATTENDANCE_TAB_TYP) IS
3426
3427 l_subject_count number;
3428 l_qua_count number := 1;
3429 l_attendance_count number := 1;
3430 l_proc varchar2(72) := g_package||'get_entire_qua';
3431
3432 BEGIN
3433
3434 hr_utility.set_location('Entering:'||l_proc, 5);
3435 p_mode :=
3436 hr_transaction_api.get_varchar2_value
3437 (p_transaction_step_id => p_transaction_step_id
3438 ,p_name => 'P_MODE');
3439
3440 l_subject_count := to_number(
3441 hr_transaction_api.get_varchar2_value
3442 (p_transaction_step_id => p_transaction_step_id
3443 ,p_name => 'P_SUBJECT_COUNT'));
3444
3445 p_qua_attendance := SSHR_QUA_ATTENDANCE_TAB_TYP();
3446
3447 hr_utility.set_location('Entering For Loop:'||l_proc,10);
3448 FOR i IN 1..NVL(l_attendance_count,0) LOOP
3449 p_qua_attendance.extend;
3450 p_qua_attendance(i) := SSHR_QUA_ATTENDANCE_OBJ_TYP(
3451 to_number(hr_transaction_api.get_varchar2_value
3452 (p_transaction_step_id => p_transaction_step_id
3453 ,p_name => 'P_ATTENDANCE_ID')),
3454 hr_transaction_api.get_date_value
3455 (p_transaction_step_id => p_transaction_step_id
3456 ,p_name => 'P_PEA_START_DATE'),
3457 to_number(hr_transaction_api.get_varchar2_value
3458 (p_transaction_step_id => p_transaction_step_id
3459 ,p_name => 'P_PEA_PERSON_ID')),
3460 hr_transaction_api.get_varchar2_value
3461 (p_transaction_step_id => p_transaction_step_id
3462 ,p_name => 'P_PEA_FULL_TIME'),
3463 hr_transaction_api.get_date_value
3464 (p_transaction_step_id => p_transaction_step_id
3465 ,p_name => 'P_PEA_END_DATE'),
3466 to_number(hr_transaction_api.get_varchar2_value
3467 (p_transaction_step_id => p_transaction_step_id
3468 ,p_name => 'P_ESTABLISHMENT_ID')),
3469 to_number(hr_transaction_api.get_varchar2_value
3470 (p_transaction_step_id => p_transaction_step_id
3471 ,p_name => 'P_PEA_OBJECT_VERSION_NUMBER')),
3472 hr_transaction_api.get_varchar2_value
3473 (p_transaction_step_id => p_transaction_step_id
3474 ,p_name => 'P_BUSINESS_GROUP_ID'),
3475 hr_transaction_api.get_varchar2_value
3476 (p_transaction_step_id => p_transaction_step_id
3477 ,p_name => 'P_PEA_ATTRIBUTE_CATEGORY'),
3478 hr_transaction_api.get_varchar2_value
3479 (p_transaction_step_id => p_transaction_step_id
3480 ,p_name => 'P_PEA_ATTRIBUTE1'),
3481 hr_transaction_api.get_varchar2_value
3482 (p_transaction_step_id => p_transaction_step_id
3483 ,p_name => 'P_PEA_ATTRIBUTE2'),
3484 hr_transaction_api.get_varchar2_value
3485 (p_transaction_step_id => p_transaction_step_id
3486 ,p_name => 'P_PEA_ATTRIBUTE3'),
3487 hr_transaction_api.get_varchar2_value
3488 (p_transaction_step_id => p_transaction_step_id
3489 ,p_name => 'P_PEA_ATTRIBUTE4'),
3490 hr_transaction_api.get_varchar2_value
3491 (p_transaction_step_id => p_transaction_step_id
3492 ,p_name => 'P_PEA_ATTRIBUTE5'),
3493 hr_transaction_api.get_varchar2_value
3494 (p_transaction_step_id => p_transaction_step_id
3495 ,p_name => 'P_PEA_ATTRIBUTE6'),
3496 hr_transaction_api.get_varchar2_value
3497 (p_transaction_step_id => p_transaction_step_id
3498 ,p_name => 'P_PEA_ATTRIBUTE7'),
3499 hr_transaction_api.get_varchar2_value
3500 (p_transaction_step_id => p_transaction_step_id
3501 ,p_name => 'P_PEA_ATTRIBUTE8'),
3502 hr_transaction_api.get_varchar2_value
3503 (p_transaction_step_id => p_transaction_step_id
3504 ,p_name => 'P_PEA_ATTRIBUTE9'),
3505 hr_transaction_api.get_varchar2_value
3506 (p_transaction_step_id => p_transaction_step_id
3507 ,p_name => 'P_PEA_ATTRIBUTE10'),
3508 hr_transaction_api.get_varchar2_value
3509 (p_transaction_step_id => p_transaction_step_id
3510 ,p_name => 'P_PEA_ATTRIBUTE11'),
3511 hr_transaction_api.get_varchar2_value
3512 (p_transaction_step_id => p_transaction_step_id
3513 ,p_name => 'P_PEA_ATTRIBUTE12'),
3514 hr_transaction_api.get_varchar2_value
3515 (p_transaction_step_id => p_transaction_step_id
3516 ,p_name => 'P_PEA_ATTRIBUTE13'),
3517 hr_transaction_api.get_varchar2_value
3518 (p_transaction_step_id => p_transaction_step_id
3519 ,p_name => 'P_PEA_ATTRIBUTE14'),
3520 hr_transaction_api.get_varchar2_value
3521 (p_transaction_step_id => p_transaction_step_id
3522 ,p_name => 'P_PEA_ATTRIBUTE15'),
3523 hr_transaction_api.get_varchar2_value
3524 (p_transaction_step_id => p_transaction_step_id
3525 ,p_name => 'P_PEA_ATTRIBUTE16'),
3526 hr_transaction_api.get_varchar2_value
3527 (p_transaction_step_id => p_transaction_step_id
3528 ,p_name => 'P_PEA_ATTRIBUTE17'),
3529 hr_transaction_api.get_varchar2_value
3530 (p_transaction_step_id => p_transaction_step_id
3531 ,p_name => 'P_PEA_ATTRIBUTE18'),
3532 hr_transaction_api.get_varchar2_value
3533 (p_transaction_step_id => p_transaction_step_id
3534 ,p_name => 'P_PEA_ATTRIBUTE19'),
3535 hr_transaction_api.get_varchar2_value
3536 (p_transaction_step_id => p_transaction_step_id
3537 ,p_name => 'P_PEA_ATTRIBUTE20'),
3538 hr_transaction_api.get_varchar2_value
3539 (p_transaction_step_id => p_transaction_step_id
3540 ,p_name => 'P_PE_NAME'),
3541 to_number(hr_transaction_api.get_varchar2_value
3542 (p_transaction_step_id => p_transaction_step_id
3543 ,p_name => 'P_PARTY_ID'))
3544 );
3545 END LOOP;
3546 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
3547
3548 p_qualifications := SSHR_QUA_TAB_TYP();
3549
3550 hr_utility.set_location('Entering For Loop:'||l_proc,20);
3551 FOR i IN 1..NVL(l_qua_count,0) LOOP
3552 p_qualifications.extend;
3553 p_qualifications(i) := SSHR_QUA_OBJ_TYP(
3554 to_number(hr_transaction_api.get_varchar2_value
3555 (p_transaction_step_id => p_transaction_step_id
3556 ,p_name => 'P_QUALIFICATION_ID')),
3557 hr_transaction_api.get_varchar2_value
3558 (p_transaction_step_id => p_transaction_step_id
3559 ,p_name => 'P_DELETE_FLAG'),
3560 to_number(hr_transaction_api.get_varchar2_value
3561 (p_transaction_step_id => p_transaction_step_id
3562 ,p_name => 'P_BUSINESS_GROUP_ID')),
3563 to_number(hr_transaction_api.get_varchar2_value
3564 (p_transaction_step_id => p_transaction_step_id
3565 ,p_name => 'P_PQ_OBJECT_VERSION_NUMBER')),
3566 to_number(hr_transaction_api.get_varchar2_value
3567 (p_transaction_step_id => p_transaction_step_id
3568 ,p_name => 'P_PQ_PERSON_ID')),
3569 hr_transaction_api.get_varchar2_value
3570 (p_transaction_step_id => p_transaction_step_id
3571 ,p_name => 'P_PQ_TITLE'),
3572 hr_transaction_api.get_varchar2_value
3573 (p_transaction_step_id => p_transaction_step_id
3574 ,p_name => 'P_PQ_GRADE_ATTAINED'),
3575 hr_transaction_api.get_varchar2_value
3576 (p_transaction_step_id => p_transaction_step_id
3577 ,p_name => 'P_PQ_STATUS'),
3578 hr_transaction_api.get_date_value
3579 (p_transaction_step_id => p_transaction_step_id
3580 ,p_name => 'P_PQ_AWARDED_DATE'),
3581 to_number(hr_transaction_api.get_varchar2_value
3582 (p_transaction_step_id => p_transaction_step_id
3583 ,p_name => 'P_PQ_FEE')),
3584 hr_transaction_api.get_varchar2_value
3585 (p_transaction_step_id => p_transaction_step_id
3586 ,p_name => 'P_PQ_FEE_CURRENCY'),
3587 to_number(hr_transaction_api.get_varchar2_value
3588 (p_transaction_step_id => p_transaction_step_id
3589 ,p_name => 'P_PQ_COMPLETED_AMOUNT')),
3590 hr_transaction_api.get_varchar2_value
3591 (p_transaction_step_id => p_transaction_step_id
3592 ,p_name => 'P_PQ_REIMBURSEMENT'),
3593 hr_transaction_api.get_varchar2_value
3594 (p_transaction_step_id => p_transaction_step_id
3595 ,p_name => 'P_PQ_COMPLETED_UNITS'),
3596 to_number(hr_transaction_api.get_varchar2_value
3597 (p_transaction_step_id => p_transaction_step_id
3598 ,p_name => 'P_PQ_TOTAL_AMOUNT')),
3599 hr_transaction_api.get_date_value
3600 (p_transaction_step_id => p_transaction_step_id
3601 ,p_name => 'P_PQ_START_DATE'),
3602 hr_transaction_api.get_date_value
3603 (p_transaction_step_id => p_transaction_step_id
3604 ,p_name => 'P_PQ_END_DATE'),
3605 hr_transaction_api.get_varchar2_value
3606 (p_transaction_step_id => p_transaction_step_id
3607 ,p_name => 'P_PQ_LICENSE_NUMBER'),
3608 hr_transaction_api.get_date_value
3609 (p_transaction_step_id => p_transaction_step_id
3610 ,p_name => 'P_PQ_EXPIRY_DATE'),
3611 hr_transaction_api.get_varchar2_value
3612 (p_transaction_step_id => p_transaction_step_id
3613 ,p_name => 'P_PQ_LICENSE_RESTRICTIONS'),
3614 null, --PROJECTED_COMPLETION_DATE
3615 hr_transaction_api.get_varchar2_value
3616 (p_transaction_step_id => p_transaction_step_id
3617 ,p_name => 'P_PQ_AWARDING_BODY'),
3618 hr_transaction_api.get_varchar2_value
3619 (p_transaction_step_id => p_transaction_step_id
3620 ,p_name => 'P_PQ_TUITION_METHOD'),
3621 hr_transaction_api.get_varchar2_value
3622 (p_transaction_step_id => p_transaction_step_id
3623 ,p_name => 'P_PQ_GROUP_RANKING'),
3624 hr_transaction_api.get_varchar2_value
3625 (p_transaction_step_id => p_transaction_step_id
3626 ,p_name => 'P_PQ_COMMENTS'),
3627 to_number(hr_transaction_api.get_varchar2_value
3628 (p_transaction_step_id => p_transaction_step_id
3629 ,p_name => 'P_QUALIFICATION_TYPE_ID')),
3630 to_number(hr_transaction_api.get_varchar2_value
3631 (p_transaction_step_id => p_transaction_step_id
3632 ,p_name => 'P_ATTENDANCE_ID')),
3633 hr_transaction_api.get_varchar2_value
3634 (p_transaction_step_id => p_transaction_step_id
3635 ,p_name => 'P_PQ_ATTRIBUTE_CATEGORY'),
3636 hr_transaction_api.get_varchar2_value
3637 (p_transaction_step_id => p_transaction_step_id
3638 ,p_name => 'P_PQ_ATTRIBUTE1'),
3639 hr_transaction_api.get_varchar2_value
3640 (p_transaction_step_id => p_transaction_step_id
3641 ,p_name => 'P_PQ_ATTRIBUTE2'),
3642 hr_transaction_api.get_varchar2_value
3643 (p_transaction_step_id => p_transaction_step_id
3644 ,p_name => 'P_PQ_ATTRIBUTE3'),
3645 hr_transaction_api.get_varchar2_value
3646 (p_transaction_step_id => p_transaction_step_id
3647 ,p_name => 'P_PQ_ATTRIBUTE4'),
3648 hr_transaction_api.get_varchar2_value
3649 (p_transaction_step_id => p_transaction_step_id
3650 ,p_name => 'P_PQ_ATTRIBUTE5'),
3651 hr_transaction_api.get_varchar2_value
3652 (p_transaction_step_id => p_transaction_step_id
3653 ,p_name => 'P_PQ_ATTRIBUTE6'),
3654 hr_transaction_api.get_varchar2_value
3655 (p_transaction_step_id => p_transaction_step_id
3656 ,p_name => 'P_PQ_ATTRIBUTE7'),
3657 hr_transaction_api.get_varchar2_value
3658 (p_transaction_step_id => p_transaction_step_id
3659 ,p_name => 'P_PQ_ATTRIBUTE8'),
3660 hr_transaction_api.get_varchar2_value
3661 (p_transaction_step_id => p_transaction_step_id
3662 ,p_name => 'P_PQ_ATTRIBUTE9'),
3663 hr_transaction_api.get_varchar2_value
3664 (p_transaction_step_id => p_transaction_step_id
3665 ,p_name => 'P_PQ_ATTRIBUTE10'),
3666 hr_transaction_api.get_varchar2_value
3667 (p_transaction_step_id => p_transaction_step_id
3668 ,p_name => 'P_PQ_ATTRIBUTE11'),
3669 hr_transaction_api.get_varchar2_value
3670 (p_transaction_step_id => p_transaction_step_id
3671 ,p_name => 'P_PQ_ATTRIBUTE12'),
3672 hr_transaction_api.get_varchar2_value
3673 (p_transaction_step_id => p_transaction_step_id
3674 ,p_name => 'P_PQ_ATTRIBUTE13'),
3675 hr_transaction_api.get_varchar2_value
3676 (p_transaction_step_id => p_transaction_step_id
3677 ,p_name => 'P_PQ_ATTRIBUTE14'),
3678 hr_transaction_api.get_varchar2_value
3679 (p_transaction_step_id => p_transaction_step_id
3680 ,p_name => 'P_PQ_ATTRIBUTE15'),
3681 hr_transaction_api.get_varchar2_value
3682 (p_transaction_step_id => p_transaction_step_id
3683 ,p_name => 'P_PQ_ATTRIBUTE16'),
3684 hr_transaction_api.get_varchar2_value
3685 (p_transaction_step_id => p_transaction_step_id
3686 ,p_name => 'P_PQ_ATTRIBUTE17'),
3687 hr_transaction_api.get_varchar2_value
3688 (p_transaction_step_id => p_transaction_step_id
3689 ,p_name => 'P_PQ_ATTRIBUTE18'),
3690 hr_transaction_api.get_varchar2_value
3691 (p_transaction_step_id => p_transaction_step_id
3692 ,p_name => 'P_PQ_ATTRIBUTE19'),
3693 hr_transaction_api.get_varchar2_value
3694 (p_transaction_step_id => p_transaction_step_id
3695 ,p_name => 'P_PQ_ATTRIBUTE20'),
3696 hr_transaction_api.get_varchar2_value
3697 (p_transaction_step_id => p_transaction_step_id
3698 ,p_name => 'P_PQ_QUA_INFORMATION_CATEGORY'),
3699 hr_transaction_api.get_varchar2_value
3700 (p_transaction_step_id => p_transaction_step_id
3701 ,p_name => 'P_PQ_QUA_INFORMATION1'),
3702 hr_transaction_api.get_varchar2_value
3703 (p_transaction_step_id => p_transaction_step_id
3704 ,p_name => 'P_PQ_QUA_INFORMATION2'),
3705 hr_transaction_api.get_varchar2_value
3706 (p_transaction_step_id => p_transaction_step_id
3707 ,p_name => 'P_PQ_QUA_INFORMATION3'),
3708 hr_transaction_api.get_varchar2_value
3709 (p_transaction_step_id => p_transaction_step_id
3710 ,p_name => 'P_PQ_QUA_INFORMATION4'),
3711 hr_transaction_api.get_varchar2_value
3712 (p_transaction_step_id => p_transaction_step_id
3713 ,p_name => 'P_PQ_QUA_INFORMATION5'),
3714 hr_transaction_api.get_varchar2_value
3715 (p_transaction_step_id => p_transaction_step_id
3716 ,p_name => 'P_PQ_QUA_INFORMATION6'),
3717 hr_transaction_api.get_varchar2_value
3718 (p_transaction_step_id => p_transaction_step_id
3719 ,p_name => 'P_PQ_QUA_INFORMATION7'),
3720 hr_transaction_api.get_varchar2_value
3721 (p_transaction_step_id => p_transaction_step_id
3722 ,p_name => 'P_PQ_QUA_INFORMATION8'),
3723 hr_transaction_api.get_varchar2_value
3724 (p_transaction_step_id => p_transaction_step_id
3725 ,p_name => 'P_PQ_QUA_INFORMATION9'),
3726 hr_transaction_api.get_varchar2_value
3727 (p_transaction_step_id => p_transaction_step_id
3728 ,p_name => 'P_PQ_QUA_INFORMATION10'),
3729 hr_transaction_api.get_varchar2_value
3730 (p_transaction_step_id => p_transaction_step_id
3731 ,p_name => 'P_PQ_QUA_INFORMATION11'),
3732 hr_transaction_api.get_varchar2_value
3733 (p_transaction_step_id => p_transaction_step_id
3734 ,p_name => 'P_PQ_QUA_INFORMATION12'),
3735 hr_transaction_api.get_varchar2_value
3736 (p_transaction_step_id => p_transaction_step_id
3737 ,p_name => 'P_PQ_QUA_INFORMATION13'),
3738 hr_transaction_api.get_varchar2_value
3739 (p_transaction_step_id => p_transaction_step_id
3740 ,p_name => 'P_PQ_QUA_INFORMATION14'),
3741 hr_transaction_api.get_varchar2_value
3742 (p_transaction_step_id => p_transaction_step_id
3743 ,p_name => 'P_PQ_QUA_INFORMATION15'),
3744 hr_transaction_api.get_varchar2_value
3745 (p_transaction_step_id => p_transaction_step_id
3746 ,p_name => 'P_PQ_QUA_INFORMATION16'),
3747 hr_transaction_api.get_varchar2_value
3748 (p_transaction_step_id => p_transaction_step_id
3749 ,p_name => 'P_PQ_QUA_INFORMATION17'),
3750 hr_transaction_api.get_varchar2_value
3751 (p_transaction_step_id => p_transaction_step_id
3752 ,p_name => 'P_PQ_QUA_INFORMATION18'),
3753 hr_transaction_api.get_varchar2_value
3754 (p_transaction_step_id => p_transaction_step_id
3755 ,p_name => 'P_PQ_QUA_INFORMATION19'),
3756 hr_transaction_api.get_varchar2_value
3757 (p_transaction_step_id => p_transaction_step_id
3758 ,p_name => 'P_PQ_QUA_INFORMATION20'),
3759 null,null,null,null,
3760 /*hr_transaction_api.get_varchar2_value
3761 (p_transaction_step_id => p_transaction_step_id
3762 ,p_name => 'P_PQ_PROFESSIONAL_BODY_NAME'),
3763 hr_transaction_api.get_varchar2_value
3764 (p_transaction_step_id => p_transaction_step_id
3765 ,p_name => 'P_PQ_MEMBERSHIP_NUMBER'),
3766 hr_transaction_api.get_varchar2_value
3767 (p_transaction_step_id => p_transaction_step_id
3768 ,p_name => 'P_PQ_MEMBERSHIP_CATEGORY'),
3769 hr_transaction_api.get_varchar2_value
3770 (p_transaction_step_id => p_transaction_step_id
3771 ,p_name => 'P_PQ_SUBS_PAYMENT_METHOD'),*/
3772 hr_transaction_api.get_number_value
3773 (p_transaction_step_id => p_transaction_step_id
3774 ,p_name => 'P_PARTY_ID')
3775 );
3776 END LOOP;
3777 hr_utility.set_location('Exiting For Loop:'||l_proc,25);
3778
3779
3780 -- now get the subject details
3781 p_qua_subjects := SSHR_QUA_SUBJECT_TAB_TYP();
3782
3783 hr_utility.set_location('Entering For Loop:'||l_proc,30);
3784 FOR i IN 1..NVL(l_subject_count,0) LOOP
3785 p_qua_subjects.extend;
3786 p_qua_subjects(i) := SSHR_QUA_SUBJECT_OBJ_TYP(
3787
3788 to_number(hr_transaction_api.get_varchar2_value
3789 (p_transaction_step_id => p_transaction_step_id
3790 ,p_name => 'P_SUBJECTS_TAKEN_ID'||i)),
3791
3792 hr_transaction_api.get_varchar2_value
3793 (p_transaction_step_id => p_transaction_step_id
3794 ,p_name => 'P_PST_DELETE_FLAG'||i),
3795
3796 hr_transaction_api.get_date_value
3797 (p_transaction_step_id => p_transaction_step_id
3798 ,p_name => 'P_PST_START_DATE'||i),
3799
3800 hr_transaction_api.get_varchar2_value
3801 (p_transaction_step_id => p_transaction_step_id
3802 ,p_name => 'P_PST_MAJOR'||i),
3803 hr_transaction_api.get_varchar2_value
3804 (p_transaction_step_id => p_transaction_step_id
3805 ,p_name => 'P_PST_SUBJECT_STATUS'||i),
3806
3807 hr_transaction_api.get_varchar2_value
3808 (p_transaction_step_id => p_transaction_step_id
3809 ,p_name => 'P_PST_SUBJECT'||i),
3810
3811 hr_transaction_api.get_varchar2_value
3812 (p_transaction_step_id => p_transaction_step_id
3813 ,p_name => 'P_PST_GRADE_ATTAINED'||i),
3814
3815 hr_transaction_api.get_date_value
3816 (p_transaction_step_id => p_transaction_step_id
3817 ,p_name => 'P_PST_END_DATE'||i),
3818
3819 hr_transaction_api.get_number_value
3820 (p_transaction_step_id => p_transaction_step_id
3821 ,p_name => 'P_QUALIFICATION_ID'),
3822
3823 to_number(hr_transaction_api.get_varchar2_value
3824 (p_transaction_step_id => p_transaction_step_id
3825 ,p_name => 'P_PST_OBJECT_VERSION_NUMBER'||i)),
3826
3827 hr_transaction_api.get_varchar2_value
3828 (p_transaction_step_id => p_transaction_step_id
3829 ,p_name => 'P_PST_ATTRIBUTE_CATEGORY'||i),
3830
3831 hr_transaction_api.get_varchar2_value
3832 (p_transaction_step_id => p_transaction_step_id
3833 ,p_name => 'P_ATTRIBUTE1_PST'||i),
3834
3835 hr_transaction_api.get_varchar2_value
3836 (p_transaction_step_id => p_transaction_step_id
3837 ,p_name => 'P_ATTRIBUTE2_PST'||i),
3838
3839 hr_transaction_api.get_varchar2_value
3840 (p_transaction_step_id => p_transaction_step_id
3841 ,p_name => 'P_ATTRIBUTE3_PST'||i),
3842
3843 hr_transaction_api.get_varchar2_value
3844 (p_transaction_step_id => p_transaction_step_id
3845 ,p_name => 'P_ATTRIBUTE4_PST'||i),
3846
3847 hr_transaction_api.get_varchar2_value
3848 (p_transaction_step_id => p_transaction_step_id
3849 ,p_name => 'P_ATTRIBUTE5_PST'||i),
3850
3851 hr_transaction_api.get_varchar2_value
3852 (p_transaction_step_id => p_transaction_step_id
3853 ,p_name => 'P_ATTRIBUTE6_PST'||i),
3854
3855 hr_transaction_api.get_varchar2_value
3856 (p_transaction_step_id => p_transaction_step_id
3857 ,p_name => 'P_ATTRIBUTE7_PST'||i),
3858
3859 hr_transaction_api.get_varchar2_value
3860 (p_transaction_step_id => p_transaction_step_id
3861 ,p_name => 'P_ATTRIBUTE8_PST'||i),
3862
3863 hr_transaction_api.get_varchar2_value
3864 (p_transaction_step_id => p_transaction_step_id
3865 ,p_name => 'P_ATTRIBUTE9_PST'||i),
3866
3867 hr_transaction_api.get_varchar2_value
3868 (p_transaction_step_id => p_transaction_step_id
3869 ,p_name => 'P_ATTRIBUTE10_PST'||i),
3870
3871 hr_transaction_api.get_varchar2_value
3872 (p_transaction_step_id => p_transaction_step_id
3873 ,p_name => 'P_ATTRIBUTE11_PST'||i),
3874
3875 hr_transaction_api.get_varchar2_value
3876 (p_transaction_step_id => p_transaction_step_id
3877 ,p_name => 'P_ATTRIBUTE12_PST'||i),
3878
3879 hr_transaction_api.get_varchar2_value
3880 (p_transaction_step_id => p_transaction_step_id
3881 ,p_name => 'P_ATTRIBUTE13_PST'||i),
3882
3883 hr_transaction_api.get_varchar2_value
3884 (p_transaction_step_id => p_transaction_step_id
3885 ,p_name => 'P_ATTRIBUTE14_PST'||i),
3886
3887 hr_transaction_api.get_varchar2_value
3888 (p_transaction_step_id => p_transaction_step_id
3889 ,p_name => 'P_ATTRIBUTE15_PST'||i),
3890
3891 hr_transaction_api.get_varchar2_value
3892 (p_transaction_step_id => p_transaction_step_id
3893 ,p_name => 'P_ATTRIBUTE16_PST'||i),
3894
3895 hr_transaction_api.get_varchar2_value
3896 (p_transaction_step_id => p_transaction_step_id
3897 ,p_name => 'P_ATTRIBUTE17_PST'||i),
3898
3899 hr_transaction_api.get_varchar2_value
3900 (p_transaction_step_id => p_transaction_step_id
3901 ,p_name => 'P_ATTRIBUTE18_PST'||i),
3902
3903 hr_transaction_api.get_varchar2_value
3904 (p_transaction_step_id => p_transaction_step_id
3905 ,p_name => 'P_ATTRIBUTE19_PST'||i),
3906
3907 hr_transaction_api.get_varchar2_value
3908 (p_transaction_step_id => p_transaction_step_id
3909 ,p_name => 'P_ATTRIBUTE20_PST'||i),
3910
3911 hr_transaction_api.get_varchar2_value
3912 (p_transaction_step_id => p_transaction_step_id
3913 ,p_name => 'P_SUB_INFORMATION_CATEGORY'||i),
3914
3915 hr_transaction_api.get_varchar2_value
3916 (p_transaction_step_id => p_transaction_step_id
3917 ,p_name => 'P_SUB_INFORMATION1_PST'||i),
3918
3919 hr_transaction_api.get_varchar2_value
3920 (p_transaction_step_id => p_transaction_step_id
3921 ,p_name => 'P_SUB_INFORMATION2_PST'||i),
3922
3923 hr_transaction_api.get_varchar2_value
3924 (p_transaction_step_id => p_transaction_step_id
3925 ,p_name => 'P_SUB_INFORMATION3_PST'||i),
3926
3927 hr_transaction_api.get_varchar2_value
3928 (p_transaction_step_id => p_transaction_step_id
3929 ,p_name => 'P_SUB_INFORMATION4_PST'||i),
3930
3931 hr_transaction_api.get_varchar2_value
3932 (p_transaction_step_id => p_transaction_step_id
3933 ,p_name => 'P_SUB_INFORMATION5_PST'||i),
3934
3935 hr_transaction_api.get_varchar2_value
3936 (p_transaction_step_id => p_transaction_step_id
3937 ,p_name => 'P_SUB_INFORMATION6_PST'||i),
3938
3939 hr_transaction_api.get_varchar2_value
3940 (p_transaction_step_id => p_transaction_step_id
3941 ,p_name => 'P_SUB_INFORMATION7_PST'||i),
3942
3943 hr_transaction_api.get_varchar2_value
3944 (p_transaction_step_id => p_transaction_step_id
3945 ,p_name => 'P_SUB_INFORMATION8_PST'||i),
3946
3947 hr_transaction_api.get_varchar2_value
3948 (p_transaction_step_id => p_transaction_step_id
3949 ,p_name => 'P_SUB_INFORMATION9_PST'||i),
3950
3951 hr_transaction_api.get_varchar2_value
3952 (p_transaction_step_id => p_transaction_step_id
3953 ,p_name => 'P_SUB_INFORMATION10_PST'||i),
3954
3955 hr_transaction_api.get_varchar2_value
3956 (p_transaction_step_id => p_transaction_step_id
3957 ,p_name => 'P_SUB_INFORMATION11_PST'||i),
3958
3959 hr_transaction_api.get_varchar2_value
3960 (p_transaction_step_id => p_transaction_step_id
3961 ,p_name => 'P_SUB_INFORMATION12_PST'||i),
3962
3963 hr_transaction_api.get_varchar2_value
3964 (p_transaction_step_id => p_transaction_step_id
3965 ,p_name => 'P_SUB_INFORMATION13_PST'||i),
3966
3967 hr_transaction_api.get_varchar2_value
3968 (p_transaction_step_id => p_transaction_step_id
3969 ,p_name => 'P_SUB_INFORMATION14_PST'||i),
3970
3971 hr_transaction_api.get_varchar2_value
3972 (p_transaction_step_id => p_transaction_step_id
3973 ,p_name => 'P_SUB_INFORMATION15_PST'||i),
3974
3975 hr_transaction_api.get_varchar2_value
3976 (p_transaction_step_id => p_transaction_step_id
3977 ,p_name => 'P_SUB_INFORMATION16_PST'||i),
3978
3979 hr_transaction_api.get_varchar2_value
3980 (p_transaction_step_id => p_transaction_step_id
3981 ,p_name => 'P_SUB_INFORMATION17_PST'||i),
3982
3983 hr_transaction_api.get_varchar2_value
3984 (p_transaction_step_id => p_transaction_step_id
3985 ,p_name => 'P_SUB_INFORMATION18_PST'||i),
3986
3987 hr_transaction_api.get_varchar2_value
3988 (p_transaction_step_id => p_transaction_step_id
3989 ,p_name => 'P_SUB_INFORMATION19_PST'||i),
3990
3991 hr_transaction_api.get_varchar2_value
3992 (p_transaction_step_id => p_transaction_step_id
3993 ,p_name => 'P_SUB_INFORMATION20_PST'||i)
3994 );
3995
3996 END LOOP;
3997 hr_utility.set_location('Exiting For Loop:'||l_proc,35);
3998
3999 EXCEPTION
4000 WHEN OTHERS THEN
4001 hr_utility.set_location('Exception:Others'||l_proc,555);
4002 RAISE;
4003 END get_entire_qua;
4004
4005
4006 -- end of procedure get_entire_qua
4007
4008
4009 Procedure rollback_transaction_step
4010 ( p_transaction_step_id varchar2
4011 ) is
4012 l_proc varchar2(72) := g_package||'rollback_transaction_step';
4013
4014 Begin
4015 hr_utility.set_location('Entering:'||l_proc, 5);
4016 delete from hr_api_transaction_values where transaction_step_id = p_transaction_step_id;
4017 delete from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id;
4018 hr_utility.set_location('Exiting:'||l_proc, 15);
4019
4020 Exception
4021 when others then
4022 hr_utility.set_location('Exception:Others'||l_proc,555);
4023 raise;
4024 End rollback_transaction_step;
4025
4026 /*
4027 This method is returns the qualification id given a transaction step id.
4028 */
4029
4030 Function get_qualification_id ( p_transaction_step_id number )
4031 return Number is
4032
4033 l_qualification_id number := 0;
4034 l_proc varchar2(72) := g_package||'get_qualification_id';
4035 Begin
4036
4037 hr_utility.set_location('Entering:'||l_proc, 5);
4038 l_qualification_id :=
4039 hr_transaction_api.get_varchar2_value
4040 (p_transaction_step_id => p_transaction_step_id
4041 ,p_name => 'p_qualification_id');
4042
4043 hr_utility.set_location('Exiting:'||l_proc, 15);
4044 return nvl(l_qualification_id,0);
4045 Exception
4046 when others then
4047 hr_utility.set_location('Exception:Others'||l_proc,555);
4048 raise;
4049 End;
4050
4051 /*
4052 This method is used to delete the data from the transaction tables for a given transaction step id
4053 */
4054 Procedure delete_transaction_step ( p_transaction_step_id in number,
4055 p_creator_person_id in number ) IS
4056 l_proc varchar2(72) := g_package||'delete_transaction_step';
4057
4058 Begin
4059
4060 -- delete the old transaction values
4061 hr_utility.set_location('Entering:'||l_proc, 5);
4062 hr_transaction_ss.delete_transaction_step(p_transaction_step_id,null,p_creator_person_id);
4063 hr_utility.set_location('Exiting:'||l_proc, 15);
4064 Exception
4065 when others then
4066 hr_utility.set_location('Exception:Others'||l_proc,555);
4067 raise;
4068 End;
4069
4070
4071 END hr_qua_awards_util_ss;
4072
4073 --end of package hr_qua_awards_util_ss