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