[Home] [Help]
PACKAGE BODY: APPS.HR_NZ_TAX_API
Source
1 PACKAGE BODY hr_nz_tax_api AS
2 /* $Header: hrnzwrtx.pkb 120.2 2005/10/06 05:03:56 rpalli noship $ */
3 --
4 -- Package Variables
5 --
6 --
7 PROCEDURE maintain_tax_info
8 (p_validate IN BOOLEAN
9 ,p_assignment_id IN NUMBER
10 ,p_effective_date IN DATE
11 ,p_mode IN VARCHAR2
12 ,p_business_group_id IN NUMBER
13 ,p_cost_allocation_keyflex_id IN NUMBER DEFAULT hr_api.g_number
14 ,p_updating_action_id IN NUMBER DEFAULT hr_api.g_number
15 ,p_updating_action_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
16 ,p_original_entry_id IN NUMBER DEFAULT hr_api.g_number
17 ,p_creator_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
18 ,p_comment_id IN NUMBER DEFAULT hr_api.g_number
19 ,p_creator_id IN NUMBER DEFAULT hr_api.g_number
20 ,p_reason IN VARCHAR2 DEFAULT hr_api.g_varchar2
21 ,p_subpriority IN NUMBER DEFAULT hr_api.g_number
22 ,p_date_earned IN DATE DEFAULT hr_api.g_date
23 ,p_personal_payment_method_id IN NUMBER DEFAULT hr_api.g_number
24 ,p_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
25 ,p_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
26 ,p_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
27 ,p_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
28 ,p_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
29 ,p_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
30 ,p_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
31 ,p_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
32 ,p_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
33 ,p_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
34 ,p_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
35 ,p_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
36 ,p_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
37 ,p_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
38 ,p_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
39 ,p_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
40 ,p_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
41 ,p_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
42 ,p_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
43 ,p_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
44 ,p_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
45 ,p_tax_code IN VARCHAR2 DEFAULT 'ND'
46 ,p_special_tax_code IN VARCHAR2 DEFAULT 'N'
47 ,p_paye_special_rate IN NUMBER DEFAULT hr_api.g_number
48 ,p_acc_special_rate IN NUMBER DEFAULT hr_api.g_number
49 ,p_student_loan_rate IN NUMBER DEFAULT hr_api.g_number
50 ,p_all_extra_emol_at_high_rate IN VARCHAR2 DEFAULT 'N'
51 ,p_effective_start_date OUT NOCOPY DATE
52 ,p_effective_end_date OUT NOCOPY DATE
53 ,p_update_warning OUT NOCOPY BOOLEAN
54 ) IS
55
56 l_proc VARCHAR2(72);
57
58 /*Bug 3648883: removed upper on element_name to improve performance*/
59 CURSOR csr_paye_tax_element(c_effective_date DATE) IS
60 SELECT pet.element_type_id
61 FROM pay_element_types_f pet
62 WHERE pet.element_name = 'PAYE Information'
63 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
64 AND legislation_code = 'NZ';
65
66
67 CURSOR csr_paye_tax_input_values(c_element_type_id pay_input_values_f.element_type_id%TYPE
68 ,c_effective_date DATE) IS
69 SELECT piv.input_value_id
70 ,piv.name
71 FROM pay_input_values_f piv
72 WHERE piv.element_type_id = c_element_type_id
73 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
74
75
76 CURSOR csr_ele_entry (p_element_link NUMBER
77 ,p_inp_val NUMBER
78 ,c_effective_date DATE)IS
79 SELECT pee.element_entry_id,
80 object_version_number
81 FROM pay_element_entries_f pee,
82 pay_element_entry_values_f pev
83 WHERE pee.assignment_id = p_assignment_id
84 AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
85 AND pee.element_link_id = p_element_link
86 AND pev.element_entry_id = pee.element_entry_id
87 AND c_effective_date BETWEEN pev.effective_start_date AND pev.effective_end_date
88 AND pev.input_value_id = p_inp_val;
89
90
91 l_inp_value_id_table hr_entry.number_table;
92 l_scr_value_table hr_entry.varchar2_table;
93
94 l_dummy NUMBER;
95 l_element_type_id NUMBER;
96 l_element_link_id NUMBER;
97 l_element_entry_id NUMBER;
98 l_object_version_number NUMBER;
99 l_effective_date DATE;
100
101
102 BEGIN
103 l_proc := 'hr_nz_tax_api.maintain_tax_info';
104 hr_utility.set_location('Entering:'|| l_proc, 5);
105 --
106 -- Get the element type id for the Tax element
107 --
108
109 l_effective_date := TRUNC(p_effective_date);
110 l_element_type_id :=0;
111 l_element_link_id :=0;
112 l_element_entry_id :=0;
113
114 OPEN csr_paye_tax_element(l_effective_date);
115 FETCH csr_paye_tax_element INTO l_element_type_id;
116 IF (csr_paye_tax_element%NOTFOUND)
117 THEN
118 CLOSE csr_paye_tax_element;
119 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
120 hr_utility.raise_error;
121 END IF;
122 CLOSE csr_paye_tax_element;
123
124 hr_utility.set_location(l_proc, 10);
125
126 --
127 -- Get the Input Value Id for each Tax Input
128 --
129 FOR rec_paye_tax_element in csr_paye_tax_input_values(l_element_type_id, l_effective_date)
130 LOOP
131 IF UPPER(rec_paye_tax_element.name) = 'TAX CODE'
132 THEN
133 l_inp_value_id_table(1) := rec_paye_tax_element.input_value_id;
134 ELSIF UPPER(rec_paye_tax_element.name) = 'SPECIAL TAX CODE'
135 THEN
136 l_inp_value_id_table(2) := rec_paye_tax_element.input_value_id;
137 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX RATE'
138 THEN
139 l_inp_value_id_table(3) := rec_paye_tax_element.input_value_id;
140 ELSIF UPPER(rec_paye_tax_element.name) = 'ACC EP SPECIAL RATE'
141 THEN
142 l_inp_value_id_table(4) := rec_paye_tax_element.input_value_id;
143 ELSIF UPPER(rec_paye_tax_element.name) = 'STUDENT LOAN SPECIAL RATE'
144 THEN
145 l_inp_value_id_table(5) := rec_paye_tax_element.input_value_id;
146 ELSIF UPPER(rec_paye_tax_element.name) = 'ALL EXTRA EMOL AT HIGH RATE'
147 THEN
148 l_inp_value_id_table(6) := rec_paye_tax_element.input_value_id;
149 END IF;
150 END LOOP;
151
152 hr_utility.set_location(l_proc, 15);
153
154 --
155 -- Check that an input value is present for each input value
156 --
157 -- *** PM :- Removed to conserve error messages on direction of BB ***
158 --
159 -- FOR i IN 1..l_inp_value_id_table.COUNT
160 -- LOOP
161 -- IF (l_inp_value_id_table(i) = NULL OR l_inp_value_id_table(i) = 0)
162 -- THEN
163 -- hr_utility.set_message(801,'HR_NZ_INVALID_INPUT_VALUE_ID');
164 -- hr_utility.raise_error;
165 -- END IF;
166 -- END LOOP;
167
168 hr_utility.set_location(l_proc, 20);
169
170 --
171 -- Get the element link id for the PAYE information element
172 --
173 l_element_link_id := hr_entry_api.get_link
174 (p_assignment_id => p_assignment_id
175 ,p_element_type_id => l_element_type_id
176 ,p_session_date => l_effective_date);
177
178 IF (l_element_link_id IS NULL OR l_element_link_id = 0)
179 THEN
180 hr_utility.set_message(801,'HR_AU_NZ_ELE_LNK_NOT_FND');
181 hr_utility.raise_error;
182 END IF;
183
184 hr_utility.set_location(l_proc, 25);
185
186 IF (p_mode IN ('CORRECTION','UPDATE','UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE'))
187 THEN
188 -----------------------------------------------------------------------------
189 -- Get the element entry of the tax element entry that is to be updated
190 ------------------------------------------------------------------------------
191
192 hr_utility.set_location(l_proc, 30);
193
194 OPEN csr_ele_entry(l_element_link_id, l_inp_value_id_table(1),l_effective_date);
195 FETCH csr_ele_entry INTO l_element_entry_id,l_object_version_number;
196 IF (csr_ele_entry%NOTFOUND)
197 THEN
198 CLOSE csr_ele_entry;
199 hr_utility.set_message(801, 'HR_AU_NZ_ELE_ENT_NOT_FND');
200 hr_utility.raise_error;
201 END IF;
202 CLOSE csr_ele_entry;
203
204 hr_utility.set_location(l_proc, 35);
205
206 py_element_entry_api.update_element_entry
207 (p_validate => p_validate
208 ,p_datetrack_update_mode => p_mode
209 ,p_effective_date => l_effective_date
210 ,p_business_group_id => p_business_group_id
211 ,p_element_entry_id => l_element_entry_id
212 ,p_object_version_number => l_object_version_number
213 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
214 ,p_updating_action_id => p_updating_action_id
215 ,p_updating_action_type => p_updating_action_type
216 ,p_original_entry_id => p_original_entry_id
217 ,p_creator_type => p_creator_type
218 ,p_comment_id => p_comment_id
219 ,p_creator_id => p_creator_id
220 ,p_reason => p_reason
221 ,p_subpriority => p_subpriority
222 ,p_date_earned => p_date_earned
223 ,p_personal_payment_method_id => p_personal_payment_method_id
224 ,p_attribute_category => p_attribute_category
225 ,p_attribute1 => p_attribute1
226 ,p_attribute2 => p_attribute2
227 ,p_attribute3 => p_attribute3
228 ,p_attribute4 => p_attribute4
229 ,p_attribute5 => p_attribute5
230 ,p_attribute6 => p_attribute6
231 ,p_attribute7 => p_attribute7
232 ,p_attribute8 => p_attribute8
233 ,p_attribute9 => p_attribute9
234 ,p_attribute10 => p_attribute10
235 ,p_attribute11 => p_attribute11
236 ,p_attribute12 => p_attribute12
237 ,p_attribute13 => p_attribute13
238 ,p_attribute14 => p_attribute14
239 ,p_attribute15 => p_attribute15
240 ,p_attribute16 => p_attribute16
241 ,p_attribute17 => p_attribute17
242 ,p_attribute18 => p_attribute18
243 ,p_attribute19 => p_attribute19
244 ,p_attribute20 => p_attribute20
245 ,p_input_value_id1 => l_inp_value_id_table(1)
246 ,p_input_value_id2 => l_inp_value_id_table(2)
247 ,p_input_value_id3 => l_inp_value_id_table(3)
248 ,p_input_value_id4 => l_inp_value_id_table(4)
249 ,p_input_value_id5 => l_inp_value_id_table(5)
250 ,p_input_value_id6 => l_inp_value_id_table(6)
251 ,p_entry_value1 => p_tax_code
252 ,p_entry_value2 => p_special_tax_code
253 ,p_entry_value3 => p_paye_special_rate
254 ,p_entry_value4 => p_acc_special_rate
255 ,p_entry_value5 => p_student_loan_rate
256 ,p_entry_value6 => p_all_extra_emol_at_high_rate
257 ,p_effective_start_date => p_effective_start_date
258 ,p_effective_end_date => p_effective_end_date
259 ,p_override_user_ent_chk => 'Y'
260 ,p_update_warning => p_update_warning);
261
262 -- *** PM :- Removed to conserve error messages on direction of BB ***
263 -- ELSE
264 -- hr_utility.set_message(801, 'HR_NZ_UPDATE_ENTRY_FAILED');
265 -- hr_utility.raise_error;
266
267 END IF;
268
269 hr_utility.set_location(' Leaving:'||l_proc, 40);
270
271 END maintain_tax_info;
272
273 END hr_nz_tax_api;