[Home] [Help]
PACKAGE BODY: APPS.PER_NL_QH_TAX
Source
1 PACKAGE BODY per_nl_qh_tax as
2 /* $Header: penlqhtx.pkb 115.9 2003/02/25 17:01:37 pgdavies noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_nl_qh_tax.';
7 --
8 procedure update_nl_tax_data
9 (p_rec in out nocopy per_qh_tax_query.taxrec
10 ,p_person_id in per_all_people_f.person_id%type
11 ,p_assignment_id in per_all_assignments_f.assignment_id%type
12 ,p_legislation_code in varchar2
13 ,p_effective_date in date
14 ) is
15 l_proc varchar2(72) := g_package||'update_nl_tax_data';
16 --
17 begin
18 --
19 hr_utility.set_location('Entering:'|| l_proc, 10);
20 --
21 if p_legislation_code='NL' then
22 --
23 update per_all_people_f
24 set per_information1 = p_rec.tax_field1
25 ,per_information2 = p_rec.tax_field25
26 ,per_information3 = p_rec.tax_field26
27 ,per_information4 = p_rec.tax_field27
28 ,per_information5 = p_rec.tax_field23
29 ,per_information6 = p_rec.tax_field24
30 ,per_information7 = p_rec.tax_field28
31 ,per_information8 = p_rec.tax_field20
32 ,per_information10 = p_rec.tax_field29
33 ,per_information11 = p_rec.tax_field30
34 ,per_information12 = p_rec.tax_field31
35 ,first_name = p_rec.tax_field22
36 ,middle_names = p_rec.tax_field7
37 where person_id = p_person_id
38 and p_effective_date between effective_start_date and effective_end_date;
39 if p_rec.tax_field21 is null then
40 update per_all_people_f
41 set country_of_birth = null
42 where person_id = p_person_id
43 and p_effective_date between effective_start_date and effective_end_date;
44 end if;
45 End if;
46 --
47 hr_utility.set_location('Leaving:'|| l_proc, 1000);
48 --
49 end update_nl_tax_data;
50 --
51 --
52
53 procedure nl_tax_query
54 (p_rec in out nocopy per_qh_tax_query.taxrec
55 ,p_person_id in per_all_people_f.person_id%type
56 ,p_assignment_id in per_all_assignments_f.assignment_id%type
57 ,p_legislation_code in varchar2
58 ,p_effective_date in date
59 ) is
60 l_proc varchar2(72) := g_package||'nl_tax_query';
61
62 Function nl_mand_lookup_meanings(p_lookup_type in varchar2, p_lookup_code in varchar2) return varchar2 is
63 l_lookup_meaning hr_lookups.meaning%type;
64 begin
65 select meaning
66 into l_lookup_meaning
67 from hr_lookups
68 where lookup_type = p_lookup_type
69 and lookup_code = p_lookup_code;
70 return l_lookup_meaning;
71 exception
72 when no_data_found then
73 fnd_message.set_name('PER','HR_NL_EMP_INVALID_LOOKUP');
74 fnd_message.set_token('LOOKUP',p_lookup_type);
75 fnd_message.raise_error;
76 return l_lookup_meaning;
77 end;
78
79 Function nl_lookup_meanings(p_lookup_type in varchar2, p_lookup_code in varchar2) return varchar2 is
80 l_lookup_meaning hr_lookups.meaning%type;
81 begin
82 select meaning
83 into l_lookup_meaning
84 from hr_lookups
85 where lookup_type = p_lookup_type
86 and lookup_code = p_lookup_code;
87 return l_lookup_meaning;
88 exception
89 when no_data_found then
90 return null;
91 end;
92
93 Function nl_get_territory(p_territory_code in varchar2) return varchar2 is
94 l_territory_name fnd_territories_vl.territory_short_name%type;
95 begin
96 select territory_short_name
97 into l_territory_name
98 from fnd_territories_vl
99 where territory_code = p_territory_code;
100 return l_territory_name;
101 exception
102 when no_data_found then
103 return null;
104 end;
105
106 --
107 begin
108 --
109 if p_legislation_code='NL' then
110 hr_utility.set_location('Entering:'|| l_proc, 10);
111
112 Begin
113 select per_information1
114 ,per_information2
115 ,per_information3
116 ,per_information4
117 ,per_information5
118 ,per_information6
119 ,per_information7
120 ,per_information8
121 ,per_information10
122 ,per_information11
123 ,per_information12
124 ,middle_names
125 ,country_of_birth
126 ,first_name
127 ,full_name
128 into p_rec.tax_field1
129 ,p_rec.tax_field25
130 ,p_rec.tax_field26
131 ,p_rec.tax_field27
132 ,p_rec.tax_field23
133 ,p_rec.tax_field24
134 ,p_rec.tax_field28
135 ,p_rec.tax_field20
136 ,p_rec.tax_field29
137 ,p_rec.tax_field30
138 ,p_rec.tax_field31
139 ,p_rec.tax_field7
140 ,p_rec.tax_field32
141 ,p_rec.tax_field22
142 ,p_rec.tax_field5
143 from per_all_people_f
144 where person_id = p_person_id
145 and p_effective_date between effective_start_date and effective_end_date
146 and rownum = 1;
147 Exception
148 when no_data_found then
149 null;
150 End;
151 p_rec.tax_field10 := nl_lookup_meanings('HR_NL_SPECIAL_TITLE',p_rec.tax_field25);
152 p_rec.tax_field8 := nl_lookup_meanings('HR_NL_SUB_ACADEMIC_TITLE',p_rec.tax_field26);
153 p_rec.tax_field4 := nl_lookup_meanings('HR_NL_FULL_NAME_FORMAT',p_rec.tax_field27);
154 p_rec.tax_field15 := nl_lookup_meanings('HR_NL_Y_N',p_rec.tax_field28);
155 p_rec.tax_field6 := nl_lookup_meanings('HR_NL_ACADEMIC_TITLE',p_rec.tax_field29);
156 p_rec.tax_field16 := nl_get_territory(p_rec.tax_field30);
157 p_rec.tax_field12 := nl_get_territory(p_rec.tax_field31);
158
159 Begin
160 select territory_short_name into p_rec.tax_field21
161 from fnd_territories_vl
162 where territory_code = p_rec.tax_field32;
163 Exception
164 when no_data_found then
165 null;
166 End;
167
168 end if;
169 --
170 hr_utility.set_location('Leaving:'|| l_proc, 1000);
171 --
172 end nl_tax_query;
173
174 end per_nl_qh_tax;