1 PACKAGE BODY HR_SA_GET_PERSON_DETAILS AS
2 /* $Header: pesagpdt.pkb 120.0 2005/05/31 20:38:27 appldev noship $ */
3
4 Function ASG_NATIONALITY_GROUP (p_Assignment_Id Number,
5 p_Effective_Date Date) return Varchar2
6 IS
7 l_Nationality_Group HR_LOOKUPS.MEANING%TYPE;
8
9 CURSOR csr_Nationality_Group (l_Assignment_Id Number,
10 l_Effective_Date Date) IS
11 select a.meaning
12 from per_all_people_f PEOPLE
13 ,hr_lookups a
14 ,per_all_assignments_f ASSIGN
15 ,per_shared_types SHTYPE
16 where l_effective_date between ASSIGN.effective_start_date
17 and ASSIGN.effective_end_date
18 and l_effective_date BETWEEN PEOPLE.effective_start_date
19 and PEOPLE.effective_end_date
20 and ASSIGN.assignment_id = l_assignment_id
21 and PEOPLE.person_id = ASSIGN.person_id
22 and SHTYPE.LOOKUP_TYPE (+)= 'NATIONALITY'
23 and PEOPLE.NATIONALITY (+)= SHTYPE.SYSTEM_TYPE_CD
24 and a.lookup_code (+)= SHTYPE.INFORMATION5
25 and a.lookup_type (+)= 'NATIONALITY_GROUP'
26 and a.application_id (+)= 800;
27
28 BEGIN
29
30 OPEN csr_NAtionality_Group (p_Assignment_Id, p_Effective_Date);
31 FETCH csr_NAtionality_Group INTO l_Nationality_Group;
32 CLOSE csr_NAtionality_Group;
33
34 RETURN l_Nationality_Group;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 IF csr_NAtionality_Group%ISOPEN THEN
39 CLOSE csr_NAtionality_Group;
40 END IF;
41 RAISE;
42 END ASG_NATIONALITY_GROUP;
43
44 Function PER_NATIONALITY_GROUP (p_Person_Id Number,
45 p_Effective_Date Date) return Varchar2
46 IS
47 l_Nationality_Group HR_LOOKUPS.MEANING%TYPE;
48
49 CURSOR csr_Nationality_Group (l_Person_Id Number,
50 l_Effective_Date Date) IS
51 select a.meaning
52 from per_all_people_f PEOPLE
53 ,hr_lookups a
54 ,per_shared_types SHTYPE
55 where PEOPLE.person_id = l_person_id
56 and l_effective_date BETWEEN PEOPLE.effective_start_date
57 AND PEOPLE.effective_end_date
58 and SHTYPE.LOOKUP_TYPE (+)= 'NATIONALITY'
59 and PEOPLE.NATIONALITY (+)= SHTYPE.SYSTEM_TYPE_CD
60 and a.lookup_code (+)= SHTYPE.INFORMATION5
61 and a.lookup_type (+)= 'NATIONALITY_GROUP'
62 and a.application_id (+)= 800;
63
64 BEGIN
65
66 OPEN csr_NAtionality_Group (p_Person_Id, p_Effective_Date);
67 FETCH csr_NAtionality_Group INTO l_Nationality_Group;
68 CLOSE csr_NAtionality_Group;
69
70 RETURN l_Nationality_Group;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF csr_NAtionality_Group%ISOPEN THEN
75 CLOSE csr_NAtionality_Group;
76 END IF;
77 RAISE;
78 END PER_NATIONALITY_GROUP;
79
80
81 END HR_SA_GET_PERSON_DETAILS;
82
[Home] [Help]