1 PACKAGE BODY hr_misc_web AS
2 /* $Header: hrmscmnw.pkb 120.1 2005/09/23 15:05:51 svittal noship $*/
3
4
5 --------------------------------------------------------------------------------
6 -- Private Global Variables
7 --------------------------------------------------------------------------------
8 gv_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'hr_misc_web';
9
10
11
12
13 /*
14 ||===========================================================================
15 || PROCEDURE: remove_transaction
16 ||----------------------------------------------------------------------------
17 ||
18 || Description:
19 || This procedure removes transaction steps, transaction step values
20 || and transaction id on Cancel or Exit to Main Menu.
21 ||
22 || Pre-Conditions:
23 ||
24 || Input Parameters:
25 ||
26 || Output Parameters:
27 ||
28 || In out nocopy Parameters:
29 ||
30 || Post Success:
31 ||
32 ||
33 || Post Failure:
34 || Raise exception.
35 ||
36 || Access Status:
37 || Public
38 ||
39 ||=============================================================================
40 */
41
42 PROCEDURE remove_transaction(
43 p_item_type in varchar2
44 ,p_item_key in varchar2
45 ,p_actid in number
46 )
47 IS
48 --
49 l_index integer default 0;
50 ltt_trans_id_tbl g_number_tbl_type
51 default g_number_tbl_default;
52 --
53
54 CURSOR get_transaction_id (
55 p_item_type in varchar2
56 ,p_item_key in varchar2
57 ,p_actid in number
58 )
59 IS
60 SELECT distinct hats.transaction_id
61 FROM hr_api_transaction_steps hats
62 WHERE hats.item_type = p_item_type
63 AND hats.item_key = p_item_key
64 AND hats.activity_id = p_actid
65 ORDER by hats.transaction_id;
66 Begin
67 --
68 savepoint cleanup_transaction;
69 --
70 -- There may be multiple transaction_id associated to p_item_type and
71 -- p_item_key. We need to get all transaction_id's first.
72 --
73 l_index := 0;
74 FOR csr1 in get_transaction_id(p_item_type => p_item_type
75 ,p_item_key => p_item_key
76 ,p_actid => p_actid)
77 LOOP
78 l_index := l_index + 1;
79 ltt_trans_id_tbl(l_index) := csr1.transaction_id;
80 END LOOP;
81 --
82 IF l_index > 1 THEN
83 FOR i in 1..l_index LOOP
84 hr_transaction_api.rollback_transaction
85 (p_transaction_id => ltt_trans_id_tbl(i));
86 END LOOP;
87 ELSIF l_index = 1 THEN
88 hr_transaction_api.rollback_transaction
89 (p_transaction_id => ltt_trans_id_tbl(l_index));
90 ELSE
91 null;
92 END IF;
93 --
94 Exception
95 When others then
96 rollback to cleanup_transaction;
97 raise;
98 --
99 --
100 End remove_transaction;
101
102
103 /*
104 ||===========================================================================
105 || FUNCTION: get_language_code
106 ||---------------------------------------------------------------------------
107 ||
108 || Description:
109 || This function returns the current language code.
110 || Example:- US - United States
111 || JP - Japan
112 || UK - United Kingdom
113 ||
114 || Access Status:
115 || Public.
116 ||
117 ||===========================================================================
118 */
119 FUNCTION get_language_code
120 RETURN VARCHAR2
121 IS
122 BEGIN
123
124 RETURN (icx_sec.getID(icx_sec.PV_LANGUAGE_CODE));
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 hr_utility.trace(' Exception in hr_misc_web.get_language_code ' || SQLERRM);
129 END get_language_code;
130
131
132 /*
133 ||===========================================================================
134 || FUNCTION: get_image_directory
135 ||---------------------------------------------------------------------------
136 ||
137 || Description:
138 || This function returns the image directory.
139 || Example:- '/OA_MEDIA/US/' in r11
140 || '/OA_MEDIA/' in r115
141 ||
142 || Access Status:
143 || Public.
144 ||
145 ||===========================================================================
146 */
147 FUNCTION get_image_directory
148 RETURN VARCHAR2
149 IS
150 BEGIN
151
152 RETURN hr_util_misc_web.get_image_directory;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 hr_utility.trace(' Exception in hr_misc_web.get_image_directory ' || SQLERRM);
157 END get_image_directory;
158
159
160 /*
161 --===========================================================================
162 --|| PROCEDURE: get_sshr_segment_value
163 --||-------------------------------------------------------------------------
164 --||
165 --|| Description:
166 --|| This procedure retrieves the segment value of input parameter
167 --|| p_user_column_name from the context "SSHR Information"
168 --||
169 --||
170 --|| Pre-Conditions: See the context "SSHR Information" is added.
171 --||
172 --|| Input Parameters: Business Group Id, User Segment Name
173 --||
174 --|| Output Parameters: Segment Value of the input parameter.
175 --||
176 --|| In Out Parameters:
177 --||
178 --|| Post Success:
179 --|| Exit to Main Menu
180 --||
181 --|| Post Failure:
182 --|| Raise exception.
183 --||
184 --|| Access Status:
185 --|| Public
186 --||
187 --||===========================================================================
188 */
189
190 FUNCTION get_sshr_segment_value
191 ( p_bg_id IN per_all_people_f.business_group_id%TYPE DEFAULT NULL,
192 p_user_column_name IN varchar2 )
193 RETURN VARCHAR2
194 IS
195
196 CURSOR get_org_column_name (
197 user_column_name VARCHAR2
198 )
199 IS
200 select application_column_name
201 from FND_DESCR_FLEX_COLUMN_USAGES
202 where descriptive_flex_context_code = 'SSHR Information'
203 and end_user_column_name = user_column_name
204 -- ***** Start new code for bug 2731002 *****
205 and application_id=800
206 and descriptive_flexfield_name='Org Developer DF';
207 -- ***** End new code for bug 2731002 *****
208
209 col_name fnd_descr_flex_column_usages.application_column_name%TYPE DEFAULT NULL;
210 v_BlockStr VARCHAR2(500);
211 v_ColName VARCHAR2(100);
212 v_BusGrpID NUMBER;
213 v_segment_value VARCHAR2(200) DEFAULT NULL;
214
215 BEGIN
216
217
218 OPEN get_org_column_name ( p_user_column_name );
219 FETCH get_org_column_name INTO col_name;
220
221 IF get_org_column_name%NOTFOUND
222 THEN
223 v_segment_value := NULL;
224 CLOSE get_org_column_name;
225 RETURN v_segment_value;
226 ELSE
227 CLOSE get_org_column_name;
228
229 v_BlockStr := ' select ' || col_name ||
230 ' from hr_organization_information hoi ' ||
231 ' where hoi.organization_id = :1 '||
232 ' and hoi.org_information_context = ' ||'''SSHR Information''';
233
234 -- Fix 3666171.
235 BEGIN
236 EXECUTE IMMEDIATE v_BlockStr INTO v_segment_value USING p_bg_id;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 v_segment_value := null;
240 END;
241
242 IF v_segment_value = '' THEN
243 v_segment_value := NULL;
244 END IF;
245 END IF;
246 RETURN v_segment_value;
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF get_org_column_name%isopen then
250 close get_org_column_name;
251 end if;
252 hr_utility.trace(' Exception in hr_misc_web.get_target ' || SQLERRM);
253 END get_sshr_segment_value;
254
255
256 /*
257 --===========================================================================
258 --|| PROCEDURE: get_user_defined_job_segments
259 --||-------------------------------------------------------------------------
260 --||
261 --|| Description:
262 --|| This derives the Job Name according to the segments specified in
263 --|| the p_job_segments. If p_job_segments is NULL then it returns
264 --|| p_job_name as it is or if p_job_name is NULL or empty it returns
265 --|| p_job_name as it is.
266 --||
267 --||
268 --|| Pre-Conditions: See the context "SSHR Information" is added
269 --|| and the segment "Display MEE Job Segments" if want any
270 --|| configuration.
271 --||
272 --|| Input Parameters: segment string, job_name, job_id
273 --||
274 --|| Output Parameters: derived Job Name
275 --||
276 --|| In Out Parameters:
277 --||
278 --|| Post Success:
279 --|| Exit to Main Menu
280 --||
281 --|| Post Failure:
282 --|| Raise exception.
283 --||
284 --|| Access Status:
285 --|| Public
286 --||
287 --||==========================================================================
288 */
289
290 FUNCTION get_user_defined_job_segments
291 ( p_job_segments IN varchar2 DEFAULT NULL,
292 p_job_name IN per_jobs.name%TYPE DEFAULT NULL,
293 p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL)
294 RETURN VARCHAR2
295 IS
296
297 v_BlockStr VARCHAR2(500);
298 v_ColName VARCHAR2(100);
299 v_BusGrpID NUMBER;
300 job_select_segments VARCHAR2(500) DEFAULT NULL;
301 v_segment_value VARCHAR2(1000) DEFAULT NULL;
302 --p_job_segments VARCHAR2(500) DEFAULT NULL;
303 --p_job_id NUMBER DEFAULT NULL;
304
305 BEGIN
306
307 IF ( p_job_segments is NULL OR (length(p_job_segments) = 0)
308 OR p_job_name is NULL OR (length(p_job_name) = 0))
309 THEN
310 RETURN p_job_name;
311 END IF;
312
313 job_select_segments := replace(p_job_segments,'|','||''.''||');
314
315 v_BlockStr := ' SELECT ' || job_select_segments ||
316 ' FROM per_job_definitions pjd, per_jobs pj ' ||
317 ' WHERE pjd.job_definition_id = pj.job_definition_id ' ||
318 ' AND pj.job_id = :1';
319
320 -- Fix 3666171.
321 BEGIN
322 EXECUTE IMMEDIATE v_BlockStr INTO v_segment_value USING p_job_id;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 v_segment_value := null;
326 END;
327
328 IF v_segment_value = ''
329 THEN
330 v_segment_value := NULL;
331 ELSE
332 WHILE(INSTR(v_segment_value,'..',1,1) > 0)
333 LOOP
334 v_segment_value := replace(v_segment_value,'..','.');
335 END LOOP;
336
337 IF( INSTR(v_segment_value,'.',-1,1 ) = length(v_segment_value)) THEN
338 v_segment_value := substr(v_segment_value,1,
339 length(v_segment_value)-1);
340 END IF;
341 END IF;
342
343
344 RETURN v_segment_value;
345
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 hr_utility.trace(' Exception in hr_misc_web.get_target ' || SQLERRM );
350 raise;
351 END get_user_defined_job_segments;
352
353
354
355 /*
356 --===========================================================================
357 --|| PROCEDURE: get_job_segments
358 --||-------------------------------------------------------------------------
359 --||
360 --|| Description:
361 --|| Utility method used in V4 to get User Defined Job Segments.
362 --||
363 --||
364 --|| Pre-Conditions: See the context "SSHR Information" is added
365 --|| and the segment "Display MEE Job Segments" if want any
366 --|| configuration.
367 --||
368 --|| Input Parameters: p_bg_id, job_name, job_id
369 --||
370 --|| Output Parameters: derived Job Name
371 --||
372 --|| In Out Parameters:
373 --||
374 --|| Post Success:
375 --||
376 --|| Post Failure:
377 --|| Raise exception.
378 --||
379 --|| Access Status:
380 --|| Public
381 --||
382 --||==========================================================================
383 */
384
385
386 FUNCTION get_job_segments
387 ( p_bg_id IN per_all_people_f.business_group_id%TYPE,
388 p_job_id IN hr_organization_units.organization_id%TYPE,
389 p_job_name IN hr_organization_units.name%TYPE)
390 RETURN VARCHAR2
391 IS
392 sshr_segment VARCHAR2(500) DEFAULT NULL;
393 job_segments VARCHAR2(500) DEFAULT '';
394 BEGIN
395
396 sshr_segment := hr_misc_web.get_sshr_segment_value
397 ( p_bg_id,
398 p_user_column_name => 'Display MEE Job Segments');
399
400 IF ( (length(sshr_segment)>0) OR sshr_segment is not NULL) THEN
401 job_segments := hr_misc_web.get_user_defined_job_segments
402 ( p_job_segments => sshr_segment,
403 p_job_id => p_job_id,
404 p_job_name => p_job_name);
405
406 END IF;
407
408
409 RETURN job_segments;
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 raise;
414 END get_job_segments;
415
416
417
418
419
420
421
422
423 /*
424 ||===========================================================================
425 || FUNCTION: get_person_details
426 ||---------------------------------------------------------------------------
427 ||
428 || Description:
429 || This function will return details pertaining to the passed
430 || person ID. It will return a record structure.
431 ||
432 || Access Status:
433 || Public.
434 ||
435 ||===========================================================================
436 */
437 FUNCTION get_person_details (
438 p_person_id IN per_people_f.person_id%TYPE
439 )
440 RETURN hr_misc_web.grt_person_details
441 IS
442
443 -- Local Variables.
444 lrt_person_details hr_misc_web.grt_person_details;
445 --
446 --1839081
447 --
448 CURSOR lc_person_details (
449 p_person_id IN per_people_f.person_id%TYPE
450 ) IS
451 SELECT full_name,
452 business_group_id
453 FROM per_all_people_f
454 WHERE person_id = p_person_id
455 AND TRUNC(SYSDATE) BETWEEN effective_start_date
456 AND NVL(effective_end_date, TRUNC(SYSDATE));
457
458 BEGIN
459
460 OPEN lc_person_details (p_person_id => p_person_id);
461
462 FETCH lc_person_details INTO
463 lrt_person_details.full_name,
464 lrt_person_details.business_group_id;
465
466 CLOSE lc_person_details;
467
468 RETURN lrt_person_details;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 CLOSE lc_person_details;
473 hr_utility.trace(' hr_misc_web.get_person_details ' || SQLERRM );
474
475 END get_person_details;
476
477
478 /*
479 ||===========================================================================
480 || FUNCTION: get_assignment_details
481 ||---------------------------------------------------------------------------
482 ||
483 || Description:
484 || This function will return details pertaining to the passed
485 || assignment ID. It will return a record structure.
486 ||
487 || Access Status:
488 || Public.
489 ||
490 ||===========================================================================
491 */
492 FUNCTION get_assignment_details (
493 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
494 p_effective_date IN DATE
495 )
496 RETURN hr_misc_web.grt_assignment_details
497 IS
498
499 -- Local Variables.
500 lrt_assignment_details hr_misc_web.grt_assignment_details;
501 lrt_empty hr_misc_web.grt_assignment_details;
502
503 CURSOR lc_assignment_details (
504 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
505 p_effective_date IN DATE
506 ) IS
507 SELECT ppf.person_id,
508 ppf.full_name,
509 paf.assignment_number,
510 paf.business_group_id,
511 paf.effective_start_date,
512 paf.effective_end_date,
513 paf.payroll_id,
514 paf.location_id,
515 paf.primary_flag,
516 paf.object_version_number
517 FROM per_all_people_f ppf,
518 per_all_assignments_f paf
519 WHERE paf.assignment_id = p_assignment_id
520 AND paf.person_id = ppf.person_id
521 AND p_effective_date BETWEEN paf.effective_start_date
522 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
523 AND p_effective_date BETWEEN ppf.effective_start_date
524 AND NVL(ppf.effective_end_date, TRUNC(SYSDATE))
525 ORDER BY paf.effective_start_date DESC;
526
527 ---------------------------------------------------------------------------
528 -- Bug 1803576 Fix:
529 -- With global supervisor and cross business group, the comparison of
530 -- the subordinate's business_group_id with the supervisor's bg id will
531 -- result in no records found. The reason is that the global supervisor's
532 -- business_group_id will never be equal to the subordinate's bg id. We
533 -- don't need to compare the bg id here as long as the records are joined
534 -- by person_id. Thus, remove the AND clause:
535 -- "paf.business_group_id + 0 = ppf.business_group_id + 0".
536 ---------------------------------------------------------------------------
537
538 --
539 -- 1839081
540 --
541 CURSOR lc_supervisor_details (
542 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
543 p_effective_date IN DATE
544 ) IS
545 SELECT ppf.person_id,
546 ppf.full_name
547 FROM per_all_people_f ppf,
548 per_all_assignments_f paf
549 WHERE paf.assignment_id = p_assignment_id
550 AND paf.supervisor_id = ppf.person_id
551 -- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
552 AND p_effective_date BETWEEN paf.effective_start_date
553 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
554 AND p_effective_date BETWEEN ppf.effective_start_date
555 AND NVL(ppf.effective_end_date, TRUNC(SYSDATE))
556 ORDER BY paf.effective_start_date DESC;
557
558
559 --
560 -- 1839081
561 --
562 CURSOR lc_job_details (
563 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
564 p_effective_date IN DATE
565 ) IS
566 SELECT pj.job_id,
567 pj.name
568 FROM per_jobs_vl pj,
569 per_all_assignments_f paf
570 WHERE paf.assignment_id = p_assignment_id
571 AND paf.job_id = pj.job_id
572 -- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
573 AND paf.business_group_id+0 = pj.business_group_id+0
574 AND p_effective_date BETWEEN paf.effective_start_date
575 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
576 ORDER BY paf.effective_start_date DESC;
577
578
579 --
580 -- 1839081
581 --
582 CURSOR lc_loc_details (
583 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
584 p_effective_date IN DATE
585 ) IS
586 SELECT hl.location_id,
587 hl.location_code
588 FROM hr_locations hl,
589 per_all_assignments_f paf
590 WHERE paf.assignment_id = p_assignment_id
591 AND paf.location_id = hl.location_id
592 -- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
593 AND p_effective_date BETWEEN paf.effective_start_date
594 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
595 ORDER BY paf.effective_start_date DESC;
596
597
598 --
599 -- 1839081
600 --
601 CURSOR lc_organization_details (
602 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
603 p_effective_date IN DATE
604 ) IS
605 SELECT hou.organization_id,
606 hou.name
607 FROM hr_organization_units hou,
608 per_all_assignments_f paf
609 WHERE paf.assignment_id = p_assignment_id
610 AND paf.organization_id = hou.organization_id
611 -- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
612 AND paf.business_group_id+0 = hou.business_group_id+0
613 AND p_effective_date BETWEEN paf.effective_start_date
614 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
615 ORDER BY paf.effective_start_date DESC;
616
617 BEGIN
618
619 OPEN lc_assignment_details (
620 p_assignment_id => p_assignment_id,
621 p_effective_date => p_effective_date
622 );
623 FETCH lc_assignment_details INTO
624 lrt_assignment_details.person_id,
625 lrt_assignment_details.person_full_name,
626 lrt_assignment_details.assignment_number,
627 lrt_assignment_details.business_group_id,
628 lrt_assignment_details.effective_start_date,
629 lrt_assignment_details.effective_end_date,
630 lrt_assignment_details.payroll_id,
631 lrt_assignment_details.location_id,
632 lrt_assignment_details.primary_flag,
633 lrt_assignment_details.object_version_number;
634 CLOSE lc_assignment_details;
635
636 OPEN lc_supervisor_details (
637 p_assignment_id => p_assignment_id,
638 p_effective_date => p_effective_date
639 );
640 FETCH lc_supervisor_details INTO
641 lrt_assignment_details.supervisor_id,
642 lrt_assignment_details.supervisor_full_name;
643 CLOSE lc_supervisor_details;
644
645
646 OPEN lc_job_details (
647 p_assignment_id => p_assignment_id,
648 p_effective_date => p_effective_date
649 );
650 FETCH lc_job_details INTO
651 lrt_assignment_details.job_id,
652 lrt_assignment_details.job_name;
653 CLOSE lc_job_details;
654
655
656 OPEN lc_organization_details (
657 p_assignment_id => p_assignment_id,
658 p_effective_date => p_effective_date
659 );
660 FETCH lc_organization_details INTO
661 lrt_assignment_details.organization_id,
662 lrt_assignment_details.organization_name;
663 CLOSE lc_organization_details;
664
665 RETURN (lrt_assignment_details);
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 CLOSE lc_assignment_details;
670 CLOSE lc_supervisor_details;
671 CLOSE lc_job_details;
672 CLOSE lc_organization_details;
673 hr_utility.trace( ' hr_misc_web.get_assignment_details ' || SQLERRM );
674
675 END get_assignment_details;
676
677
678 /*
679 ||===========================================================================
680 || FUNCTION: get_assignment_id
681 ||---------------------------------------------------------------------------
682 ||
683 || Description:
684 || This function will return the current assignment id of the person
685 || based on the person id.
686 ||
687 || Access Status:
688 || Public.
689 ||
690 ||===========================================================================
691 */
692 FUNCTION get_assignment_id (
693 p_person_id IN per_people_f.person_id%TYPE
694 )
695 RETURN hr_misc_web.grt_assignment_details
696 IS
697
698 -- Local Variables.
699 lrt_assignment_details hr_misc_web.grt_assignment_details;
700
701 --
702 --1839081
703 --
704 CURSOR lc_get_assignment (p_person_id IN per_people_f.person_id%TYPE)
705 IS
706 SELECT paf.assignment_id, paf.effective_start_date
707 FROM per_all_assignments_f paf
708 WHERE paf.person_id = p_person_id
709 AND paf.primary_flag = 'Y'
710 -- Fix for bug 2268056
711 AND paf.assignment_type in ('E','C')
712 ORDER BY paf.effective_start_date DESC;
713
714 BEGIN
715
716 OPEN lc_get_assignment (
717 p_person_id => p_person_id
718 );
719
720 FETCH lc_get_assignment INTO
721 lrt_assignment_details.assignment_id,
722 lrt_assignment_details.effective_start_date;
723
724 IF lc_get_assignment%NOTFOUND OR lc_get_assignment%NOTFOUND IS NULL
725 THEN
726 CLOSE lc_get_assignment;
727 RETURN NULL;
728 ELSE
729 CLOSE lc_get_assignment;
730 END IF;
731
732 RETURN (lrt_assignment_details);
733
734 EXCEPTION
735 WHEN OTHERS THEN
736 CLOSE lc_get_assignment;
737 hr_utility.trace(' hr_misc_web.get_assignment_id ' || SQLERRM );
738
739 END get_assignment_id;
740
741
742 FUNCTION get_lookup_meaning (
743 p_code IN VARCHAR2,
744 p_lookup_type IN VARCHAR2,
745 p_date IN DATE
746 )
747 RETURN VARCHAR2 IS
748
749 CURSOR lc_meaning IS
750 SELECT meaning
751 FROM hr_lookups
752 WHERE LOOKUP_CODE = p_code
753 AND LOOKUP_TYPE = p_lookup_type AND
754 p_date between nvl(start_date_active , p_date) AND
755 nvl(end_date_active, p_date)
756 AND enabled_flag = 'Y' ;
757
758
759 lv_meaning hr_lookups.meaning%TYPE;
760
761 BEGIN
762
763 OPEN lc_meaning ;
764 FETCH lc_meaning into lv_meaning ;
765 IF lc_meaning%NOTFOUND
766 THEN
767 return NULL ;
768 END IF ;
769 CLOSE lc_meaning;
770
771 return lv_meaning ;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 CLOSE lc_meaning;
776 raise;
777 END get_lookup_meaning;
778
779
780 /*
781 ||===========================================================================
782 || FUNCTION: get_enter_process_checks
783 ||---------------------------------------------------------------------------
784 ||
785 || Description:
786 || This function will return a record which will contain the data to
787 || do the required error checking before launching a module from the
788 || effective date dialog box.
789 ||
790 || Access Status:
791 || Public.
792 ||
793 ||===========================================================================
794 */
795 FUNCTION get_enter_process_checks (
796 p_assignment_id IN NUMBER DEFAULT NULL,
797 p_effective_date IN DATE DEFAULT NULL,
798 p_enter_process_checks IN hr_misc_web.grt_enter_process_checks DEFAULT NULL
799 )
800 RETURN hr_misc_web.grt_enter_process_checks
801 IS
802
803 -- Local variables.
804 -- Hire date
805
806 --
807 -- 1839081
808 --
809 CURSOR lc_hire_date
810 IS
811 SELECT MIN(pps.date_start)
812 FROM per_periods_of_service pps,
813 per_all_assignments_f asg
814 WHERE asg.assignment_id = p_assignment_id
815 AND asg.person_id = pps.person_id;
816
817 -- Termination date
818
819 --
820 -- 1839081
821 -- 2173279
822 -- Redefine the cursor lc_termination_date
823 --
824 --CURSOR lc_termination_date
825 --IS
826 --SELECT MAX(asg.effective_start_date)
827 --FROM per_all_assignments_f asg,
828 -- per_assignment_status_types ast
829 --WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
830 --AND asg.assignment_id = p_assignment_id
831 --AND per_system_status = 'TERM_ASSIGN';
832
833 CURSOR lc_termination_date IS
834 select ser.actual_termination_date
835 from per_periods_of_service ser,
836 per_all_assignments_f ass
837 where ass.period_of_service_id = ser.period_of_service_id
838 AND TRUNC(SYSDATE) between ass.effective_start_date
839 AND ass.effective_end_date
840 AND ass.assignment_id = p_assignment_id;
841 -- 2173279
842
843 -- Future assignment date
844
845 --
846 -- 1839081
847 --
848 CURSOR lc_future_assg_date
849 IS
850 SELECT MAX(effective_start_date)
851 FROM per_all_assignments_f
852 WHERE assignment_id = p_assignment_id
853 AND effective_start_date > p_effective_date;
854
855 ld_hire_date DATE;
856 ld_termination_date DATE;
857 ld_future_assg_date DATE;
858 lrt_enter_process_checks hr_misc_web.grt_enter_process_checks;
859
860 BEGIN
861
862 -- Assign all the checks to be done to the returning structure.
863 lrt_enter_process_checks.hire_date :=
864 p_enter_process_checks.hire_date;
865 lrt_enter_process_checks.termination_date :=
866 p_enter_process_checks.termination_date;
867 lrt_enter_process_checks.future_assignment :=
868 p_enter_process_checks.future_assignment;
869 lrt_enter_process_checks.pending_workflow :=
870 p_enter_process_checks.pending_workflow;
871 lrt_enter_process_checks.correction_mode :=
872 p_enter_process_checks.correction_mode;
873 lrt_enter_process_checks.earlier_date :=
874 p_enter_process_checks.earlier_date;
875
876
877 -- Now get the required dates depending the checks to be made.
878
879 -- Get the hire date of the employee.
880 IF ( p_enter_process_checks.hire_date = 'Y' )
881 THEN
882 OPEN lc_hire_date;
883 FETCH lc_hire_date INTO ld_hire_date;
884 IF lc_hire_date%NOTFOUND OR ld_hire_date IS NULL
885 THEN
886 ld_hire_date := NULL;
887 END IF;
888 CLOSE lc_hire_date;
889 lrt_enter_process_checks.hire_date2 := ld_hire_date;
890 ELSE
891 lrt_enter_process_checks.hire_date2 := NULL;
892 END IF;
893
894
895 -- Get the termination date of the employee.
896 IF ( p_enter_process_checks.termination_date = 'Y' )
897 THEN
898 OPEN lc_termination_date;
899 FETCH lc_termination_date INTO ld_termination_date;
900 IF lc_termination_date%NOTFOUND OR ld_termination_date IS NULL
901 THEN
902 ld_termination_date := NULL;
903 END IF;
904 CLOSE lc_termination_date;
905 lrt_enter_process_checks.termination_date2 := ld_termination_date;
906 ELSE
907 lrt_enter_process_checks.termination_date2 := NULL;
908 END IF;
909
910
911
912 -- Check for the existance of future dated assignment change.
913 IF ( p_enter_process_checks.future_assignment = 'Y' )
914 THEN
915 OPEN lc_future_assg_date;
916 FETCH lc_future_assg_date INTO ld_future_assg_date;
917 IF lc_future_assg_date%NOTFOUND OR ld_future_assg_date IS NULL
918 THEN
919 ld_future_assg_date := NULL;
920 END IF;
921 CLOSE lc_future_assg_date;
922 lrt_enter_process_checks.future_assignment_date := ld_future_assg_date;
923 ELSE
924 lrt_enter_process_checks.future_assignment_date := NULL;
925 END IF;
926
927
928 RETURN (lrt_enter_process_checks);
929
930 EXCEPTION
931
932 WHEN OTHERS
933 THEN
934 raise;
935
936 END get_enter_process_checks;
937
938 /*
939 ||===========================================================================
940 || PROCEDURE: get_business_group_id
941 ||---------------------------------------------------------------------------
942 ||
943 || Description:
944 || This function is overloaded to get the business group id either from
945 || the logged in person or from the selected resposibility.
946 || Logged in Person - This function will return the business group of
947 || the logged in person.
948 || Responsibility - The Function call returns the Business Group ID
949 || for the current session's login responsibility.
950 || The defaulting levels are as defined in the
951 || package FND_PROFILE. It returns business group id
952 || value for a specific user/resp/appl combo.
953 || Default is user/resp/appl/site is current login.
954 ||
955 || Access Status:
956 || Public.
957 ||
958 ||===========================================================================
959 */
960 FUNCTION get_business_group_id (
961 p_person_id IN per_people_f.person_id%TYPE
962 )
963 RETURN per_business_groups.business_group_id%TYPE
964 IS
965
966 -- Local Variables.
967 l_person_rec per_people_f%ROWTYPE;
968
969 BEGIN
970
971 l_person_rec := hr_util_misc_web.get_person_rec (
972 p_effective_date => SYSDATE,
973 p_person_id => p_person_id);
974
975 RETURN (l_person_rec.business_group_id);
976
977 EXCEPTION
978 WHEN OTHERS THEN
979 hr_utility.trace(' Exception in hr_misc_web.get_business_group_id ' || SQLERRM );
980
981 END get_business_group_id;
982
983
984 FUNCTION get_business_group_id
985 RETURN per_business_groups.business_group_id%TYPE
986 IS
987
988 -- Local Variables.
989 ln_business_group_id per_business_groups.business_group_id%TYPE;
990
991 BEGIN
992
993 fnd_profile.get (
994 name => 'PER_BUSINESS_GROUP_ID',
995 val => ln_business_group_id
996 );
997
998 RETURN (ln_business_group_id);
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 hr_utility.trace(' Exception in hr_misc_web.get_business_group_id ' || SQLERRM );
1003
1004 END get_business_group_id;
1005
1006 /*
1007 ||===========================================================================
1008 || FUNCTION: get_legislation_code
1009 ||---------------------------------------------------------------------------
1010 ||
1011 || Description:
1012 || This function will return the legislation code of a person,
1013 || an assignment, or of a business_group_id which ever is provided.
1014 ||
1015 || Access Status:
1016 || Public.
1017 ||
1018 ||===========================================================================
1019 */
1020 FUNCTION get_legislation_code (
1021 p_person_id IN per_people_f.person_id%TYPE DEFAULT NULL,
1022 p_assignment_id IN per_assignments_f.assignment_id%TYPE DEFAULT NULL,
1023 p_business_group_id IN per_business_groups.business_group_id%TYPE
1024 DEFAULT NULL,
1025 p_effective_date IN DATE DEFAULT SYSDATE
1026 )
1027 RETURN per_business_groups.legislation_code%TYPE
1028 IS
1029
1030 -- Local Variables.
1031 l_legislation_code VARCHAR2(150);
1032
1033 /* --------------------------------------------------------------------------
1034 -- The following cursors are commented out because they use the
1035 -- per_business_groups complex views. For better performance, they are
1036 -- replaced by base table which is hr_organization_information.
1037 --
1038 -- Avoid using join for better performance
1039 CURSOR csr_per_leg_code is
1040 SELECT pbg.legislation_code
1041 FROM per_business_groups pbg
1042 WHERE pbg.business_group_id IN
1043 (SELECT per.business_group_id
1044 FROM per_all_people_f per
1045 WHERE per.person_id = p_person_id
1046 AND TRUNC(p_effective_date) BETWEEN per.effective_start_date
1047 AND NVL(per.effective_end_date, TRUNC(SYSDATE)));
1048
1049 CURSOR csr_asg_leg_code is
1050 SELECT pbg.legislation_code
1051 FROM per_business_groups pbg
1052 WHERE pbg.business_group_id IN
1053 (SELECT paf.business_group_id
1054 FROM per_all_assignments_f paf
1055 WHERE paf.assignment_id = p_assignment_id
1056 AND TRUNC(p_effective_date) BETWEEN paf.effective_start_date
1057 AND NVL(paf.effective_end_date, TRUNC(SYSDATE)));
1058
1059 CURSOR csr_bus_leg_code is
1060 SELECT legislation_code
1061 FROM per_business_groups
1062 WHERE business_group_id = p_business_group_id;
1063
1064 -------------------------------------------------------------------------
1065 */
1066
1067 -- Bug 1680269:
1068 -- The following cursors retrieve legislation_code information for better
1069 -- performance because base tables are used instead of views. These cursors
1070 -- are similar to SSHR V4 common module code.
1071 CURSOR csr_per_leg_code is
1072 SELECT org.org_information9 legislation_code
1073 FROM per_all_people_f ppf
1074 ,hr_organization_information org
1075 WHERE org.organization_id = ppf.business_group_id
1076 AND org.org_information_context = 'Business Group Information'
1077 AND ppf.person_id = p_person_id
1078 AND TRUNC(p_effective_date)
1079 BETWEEN ppf.effective_start_date
1080 AND ppf.effective_end_date;
1081
1082
1083 CURSOR csr_asg_leg_code is
1084 SELECT org.org_information9 legislation_code
1085 FROM per_all_assignments_f paf
1086 ,hr_organization_information org
1087 WHERE org.organization_id = paf.business_group_id
1088 AND org.org_information_context = 'Business Group Information'
1089 AND paf.assignment_id = p_assignment_id
1090 AND TRUNC(p_effective_date)
1091 BETWEEN paf.effective_start_date
1092 AND paf.effective_end_date;
1093
1094 CURSOR csr_bus_leg_code is
1095 SELECT org_information9 legislation_code
1096 FROM hr_organization_information
1097 WHERE org_information_context = 'Business Group Information'
1098 and organization_id = p_business_group_id;
1099
1100 BEGIN
1101
1102 IF p_person_id IS NOT NULL
1103 THEN
1104 OPEN csr_per_leg_code;
1105 FETCH csr_per_leg_code INTO l_legislation_code;
1106 CLOSE csr_per_leg_code;
1107 ELSIF p_assignment_id IS NOT NULL
1108 THEN
1109 OPEN csr_asg_leg_code;
1110 FETCH csr_asg_leg_code INTO l_legislation_code;
1111 CLOSE csr_asg_leg_code;
1112 ELSIF p_business_group_id IS NOT NULL
1113 THEN
1114 OPEN csr_bus_leg_code;
1115 FETCH csr_bus_leg_code INTO l_legislation_code;
1116 CLOSE csr_bus_leg_code;
1117 ELSE
1118 l_legislation_code := NULL;
1119 END IF;
1120
1121 RETURN l_legislation_code;
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 hr_utility.trace(' Exception in hr_misc_web.get_legislation_code ' || SQLERRM );
1126
1127 END get_legislation_code;
1128
1129
1130
1131 END hr_misc_web;
[Home] [Help]