[Home] [Help]
PACKAGE BODY: APPS.HR_DE_TAX_SUPPORT
Source
1 PACKAGE BODY HR_DE_TAX_SUPPORT AS
2 /* $Header: perdetbu.pkb 115.4 2002/01/25 04:47:49 pkm ship $ */
3
4 --
5
6 procedure batch_update
7 ( P_BUSINESS_GROUP_ID IN NUMBER
8 , P_date_from IN DATE
9 , P_ORG_HIERARCHY IN NUMBER
10 , P_TOP_ORG IN NUMBER
11 , P_ASSIGNMENT_SET IN NUMBER
12 , P_ACTION IN VARCHAR2
13 , P_PROCESS_ID IN NUMBER
14 , P_TAX_CLASS IN VARCHAR2
15 , P_NO_OF_CHILDREN IN VARCHAR2
16 , P_TAX_FREE_INCOME IN VARCHAR2
17 , P_ADD_INCOME IN VARCHAR2
18 )
19
20 is
21
22
23 cursor get_org_structure_version
24 (P_ORG_HIERARCHY NUMBER
25 ,P_DATE_FROM DATE)
26 is
27 select max(posv.org_structure_version_id) org_structure_version_id
28 from per_org_structure_versions posv
29 where posv.organization_structure_id = P_ORG_HIERARCHY
30 and nvl(posv.date_from,P_DATE_FROM) <= P_DATE_FROM
31 and nvl(posv.date_to,TO_DATE('31/12/4712','DD/MM/YYYY')) >= P_DATE_FROM
32 order by posv.org_structure_version_id;
33
34
35 cursor get_org_structure_element
36 (P_ORG_STRUCT_VERSION_ID NUMBER
37 ,P_TOP_ORG NUMBER
38 ,P_ORG_HIERARCHY NUMBER)
39 is
40 select distinct ose.organization_id_child organization_id
41 from per_org_structure_elements ose, per_org_structure_versions_v posv
42 where ose.org_structure_version_id = posv.ORG_STRUCTURE_VERSION_ID
43 and ose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
44 and posv.ORG_STRUCTURE_VERSION_ID = P_ORG_HIERARCHY
45 and posv.ORGANIZATION_STRUCTURE_ID = P_ORG_HIERARCHY
46 and ose.organization_id_parent = P_TOP_ORG
47
48 UNION
49 select P_TOP_ORG organization_id
50 from dual
51 order by organization_id;
52
53 cursor get_organizations
54 ( P_ORG_HIERARCHY NUMBER
55 , P_ORGANIZATION_ID NUMBER
56 )
57 is
58 select distinct ose.organization_id_child organization_id
59 from per_org_structure_elements ose
60 where ose.org_structure_version_id +0 = P_ORG_HIERARCHY
61 connect by prior ose.organization_id_child = ose.organization_id_parent
62 and ose.org_structure_version_id = P_ORG_HIERARCHY
63 start with ose.organization_id_parent = P_ORGANIZATION_ID
64 and ose.org_structure_version_id = P_ORG_HIERARCHY
65 UNION
66 select P_ORGANIZATION_ID organization_id
67 from dual;
68
69
70 cursor get_assignment_sets
71 ( P_BUSINESS_GROUP_ID NUMBER
72 ,P_DATE_FROM DATE
73 ,P_ASSIGNMENT_SET NUMBER)
74
75 is
76 SELECT asg.assignment_id
77 ,hdt.effective_end_date
78 ,hdt.effective_start_date
79 ,hdt.element_entry_id
80 ,hdt.object_version_number
81 FROM PER_ALL_ASSIGNMENTS_F asg
82 ,HR_ASSIGNMENT_SET_AMENDMENTS amn
83 ,HR_DE_TAX_INFORMATION_V hdt
84 WHERE asg.business_group_id = P_BUSINESS_GROUP_ID
85 AND asg.assignment_id = amn.assignment_id
86 AND amn.include_or_exclude = 'I'
87 AND amn.assignment_set_id = P_ASSIGNMENT_SET
88 AND P_DATE_FROM between asg.effective_start_date and nvl(asg.effective_end_date,TO_DATE('31/12/4712','DD/MM/YYYY'))
89 AND hdt.assignment_id = asg.assignment_id
90 and get_tax_record(asg.assignment_id, P_DATE_FROM) = 'Y';
91
92
93 cursor get_assignment_bg
94 ( P_DATE_FROM DATE
95 , P_BUSINESS_GROUP_ID NUMBER
96 )
97 is
98
99 select paf.assignment_id
100 ,hdt.effective_end_date
101 ,hdt.effective_start_date
102 ,hdt.element_entry_id
103 ,hdt.object_version_number
104
105 from per_all_assignments_f paf
106 ,hr_de_tax_information_v hdt
107 where paf.business_group_id = P_BUSINESS_GROUP_ID
108 and paf.assignment_type = 'E'
109 and P_DATE_FROM between paf.effective_start_date and nvl(paf.effective_end_date,TO_DATE('31/12/4712','DD/MM/YYYY'))
110 AND paf.assignment_id = hdt.assignment_id
111 and get_tax_record(paf.assignment_id, P_DATE_FROM) = 'Y';
112
113
114 cursor get_assignment_org
115 ( P_DATE_FROM DATE
116 ,P_ORGANIZATION_ID NUMBER
117 , P_BUSINESS_GROUP_ID NUMBER
118 )
119 is
120
121 select paf.assignment_id
122 ,HDT.EFFECTIVE_END_DATE
123 ,HDT.EFFECTIVE_START_DATE
124 ,hdt.element_entry_id
125 ,hdt.object_version_number
126 from per_all_assignments_f paf
127 ,hr_de_tax_information_v hdt
128 where paf.business_group_id = P_BUSINESS_GROUP_ID
129 and paf.organization_id = P_ORGANIZATION_ID
130 and paf.assignment_type = 'E'
131 and P_DATE_FROM between paf.effective_start_date and nvl(paf.effective_end_date,TO_DATE('31/12/4712','DD/MM/YYYY'))
132 and paf.assignment_id = hdt.assignment_id
133 and get_tax_record(paf.assignment_id, P_DATE_FROM) = 'Y';
134
135
136
137 BEGIN
138
139
140 IF P_ASSIGNMENT_SET IS NULL AND P_ORG_HIERARCHY IS NULL THEN
141
142 for assgt_rec in get_assignment_bg
143 ( P_DATE_FROM
144 , P_BUSINESS_GROUP_ID)
145
146 loop
147
148
149 tax_record( P_BUSINESS_GROUP_ID
150 ,P_DATE_FROM
151 ,P_ACTION
152 ,assgt_rec.assignment_id
153 ,P_PROCESS_ID
154 ,assgt_rec.effective_end_date
155 ,assgt_rec.effective_start_date
156 ,assgt_rec.element_entry_id
157 ,P_TAX_CLASS
158 ,P_NO_OF_CHILDREN
159 ,P_TAX_FREE_INCOME
160 ,P_ADD_INCOME
161 ,assgt_rec.object_version_number);
162
163 end loop;
164
165 END IF;
166
167 IF P_ASSIGNMENT_SET IS NULL AND P_ORG_HIERARCHY IS not NULL THEN
168
169 for org_structure_version_rec in get_org_structure_version
170 (P_ORG_HIERARCHY
171 ,P_DATE_FROM)
172 loop
173
174 for org_structure_element_rec in get_org_structure_element
175 (org_structure_version_rec.org_structure_version_id
176 ,P_TOP_ORG
177 ,P_ORG_HIERARCHY)
178 loop
179
180
181 for org_rec in get_organizations
182 ( org_structure_version_rec.org_structure_version_id
183 , org_structure_element_rec.organization_id )
184
185 loop
186
187 for assgt_rec in get_assignment_org
188 ( P_DATE_FROM
189 , org_rec.organization_id
190 , P_BUSINESS_GROUP_ID)
191
192 loop
193 fnd_file.put_line(fnd_file.log,'ASG_SET');
194 tax_record( P_BUSINESS_GROUP_ID
195 ,P_DATE_FROM
196 ,P_ACTION
197 ,assgt_rec.assignment_id
198 ,P_PROCESS_ID
199 ,assgt_rec.effective_end_date
200 ,assgt_rec.effective_start_date
201 ,assgt_rec.element_entry_id
202 ,P_TAX_CLASS
203 ,P_NO_OF_CHILDREN
204 ,P_TAX_FREE_INCOME
205 ,P_ADD_INCOME
206 ,assgt_rec.object_version_number);
207
208 end loop; -- assignment
209
210
211 end loop; -- get_organizations
212
213
214 end loop; -- get_org_structure_element
215
216
217 end loop; -- get_org_structure_version
218
219 END IF;
220
221 IF P_ASSIGNMENT_SET IS NOT NULL THEN
222
223 for assgt_rec in get_assignment_sets
224 (P_BUSINESS_GROUP_ID,
225 P_DATE_FROM,
226 P_ASSIGNMENT_SET)
227 loop
228
229 tax_record( P_BUSINESS_GROUP_ID
230 ,P_DATE_FROM
231 ,P_ACTION
232 ,assgt_rec.assignment_id
233 ,P_PROCESS_ID
234 ,assgt_rec.effective_end_date
235 ,assgt_rec.effective_start_date
236 ,assgt_rec.element_entry_id
237 ,P_TAX_CLASS
238 ,P_NO_OF_CHILDREN
239 ,P_TAX_FREE_INCOME
240 ,P_ADD_INCOME
241 ,assgt_rec.object_version_number);
242
243
244
245
246
247
248 end loop;
249 END IF;
250
251 exception
252 when others then
253 fnd_file.put_line(fnd_file.log,SQLERRM);
254 fnd_file.put_line(fnd_file.log,'');
255
256 end batch_update;
257
258
259 procedure tax_record
260 ( P_BUSINESS_GROUP_ID IN NUMBER
261 , P_date_from IN DATE
262 , P_ACTION IN VARCHAR2
263 , P_ASSIGNMENT_ID IN NUMBER
264 , P_PROCESS_ID IN NUMBER
265 , P_END_DATE IN DATE
266 , P_START_DATE IN DATE
267 , P_ELEMENT_ENTRY_ID IN NUMBER
268 , P_TAX_CLASS IN VARCHAR2
269 , P_NO_OF_CHILDREN IN VARCHAR2
270 , P_TAX_FREE_INCOME IN VARCHAR2
271 , P_ADD_INCOME IN VARCHAR2
272 , P_OBJECT_VERSION_NUMBER IN NUMBER)
273
274 is
275
276 l_update_mode varchar2(20);
277 l_tax_class varchar2(30);
278 l_no_of_children varchar2(30);
279 l_tax_free_income varchar2(30);
280 l_add_income varchar2(30);
281 l_assignment_info_id number(15);
282 l_object_version_number number(15);
283 l_effective_start_date date;
284 l_effective_end_date date;
285 l_update_warning boolean;
286 l_bdate date;
287 l_cdate date;
288
289 BEGIN
290
291 IF P_ACTION = 'R' THEN
292
293
294 hr_assignment_extra_info_api.create_assignment_extra_info
295 (p_assignment_id => P_ASSIGNMENT_ID
296 ,p_information_type => 'DE_TAX_BATCH_UPDATE_INFO'
297 ,p_aei_information_category => 'DE_TAX_BATCH_UPDATE_INFO'
298 ,p_aei_information1 => to_char(P_PROCESS_ID)
299 ,P_ASSIGNMENT_EXTRA_INFO_ID => l_assignment_info_id
300 ,P_OBJECT_VERSION_NUMBER => l_object_version_number);
301
302
303
304
305
306
307
308 END IF;
309
310 IF P_ACTION = 'UR' THEN
311
312 select nvl(p_end_date,to_date('31/12/4712','dd/mm/yyyy'))
313 into l_cdate
314 from dual;
315
316 select to_date('31/12/4712','dd/mm/yyyy')
317 into l_bdate
318 from dual;
319
320 if l_cdate = l_bdate then
321
322 l_update_mode := 'UPDATE' ;
323 else
324
325 l_update_mode := 'UPDATE_CHANGE_INSERT' ;
326
327 end if;
328 if P_START_DATE = P_DATE_FROM then
329 l_update_mode := 'CORRECTION' ;
330 end if;
331
332
333 select DECODE(P_TAX_CLASS,'NC',hr_api.g_varchar2,'DE_TAX_CLASS6')
334 ,DECODE(P_NO_OF_CHILDREN,'NC',hr_api.g_varchar2,'0')
335 ,DECODE(P_TAX_FREE_INCOME,'NC',hr_api.g_varchar2,'0')
336 ,DECODE(P_ADD_INCOME,'NC',hr_api.g_varchar2,'0')
337
338 into l_tax_class
339 ,l_no_of_children
340 ,l_tax_free_income
341 ,l_add_income
342
343 from dual;
344
345
346 l_object_version_number := p_object_version_number;
347 per_de_ele_api.update_tax_information
348
349 (p_datetrack_update_mode => RTRIM(l_update_mode)
350 ,P_EFFECTIVE_DATE => p_date_from
351 ,p_business_group_id => P_BUSINESS_GROUP_ID
352 ,p_element_entry_id => P_ELEMENT_ENTRY_ID
353 ,p_updated => P_PROCESS_ID
354 ,p_tax_class => l_tax_class
355 ,p_no_of_children => l_no_of_children
356 ,p_yearly_tax_free_income => l_tax_free_income
357 ,p_monthly_tax_free_income => l_tax_free_income
358 ,p_additional_mth_tax_income => l_add_income
359 ,p_additional_year_tax_income => l_add_income
360 ,p_object_version_number => l_object_version_number
361 ,p_effective_start_date => l_effective_start_date
362 ,p_effective_end_date => l_effective_end_date
363 ,p_update_warning => l_update_warning);
364
365 END IF;
366
367
368 end tax_record;
369
370 procedure delete_assignment
371 ( p_process_id IN NUMBER) IS
372
373 CURSOR C_ASSIGNMENT ( p_process_id NUMBER) IS
374 SELECT ASSIGNMENT_ID,
375 ASSIGNMENT_EXTRA_INFO_ID,
376 OBJECT_VERSION_NUMBER
377 FROM HR_DE_ASG_TAX_BATCH_UPD_V
378 WHERE PROCESS_ID = P_PROCESS_ID;
379
380
381
382 BEGIN
383
384 for assgt_del_rec in c_assignment(P_PROCESS_ID)
385 loop
386
387 hr_assignment_extra_info_api.delete_assignment_extra_info
388 ( p_assignment_extra_info_id => assgt_del_rec.ASSIGNMENT_EXTRA_INFO_ID
389 ,p_object_version_number => assgt_del_rec.object_version_number );
390
391 end loop;
392
393 END delete_assignment;
394
395 function get_tax_record( p_assignment_id IN NUMBER,
396 p_date_from IN DATE) return char is
397 CURSOR c_get_tax_rec ( p_assignment_id number,
398 p_date_from date)
399 IS
400 select '1'
401 from hr_de_tax_information_v hdt
402 where hdt.assignment_id = p_assignment_id
403 and to_char(hdt.effective_start_date,'YYYY') < to_char(p_date_from,'YYYY')
404 and nvl(hdt.effective_end_date,TO_DATE('31/12/4712','DD/MM/YYYY')) > p_date_from
405 and hdt.tax_class IN ('I','II','III','IV','V','VI')
406 and hdt.effective_start_date = ( select max(effective_start_date)
407 from pay_element_entries_f pee
408 where pee.element_entry_id = hdt.element_entry_id
409 and pee.assignment_id = hdt.assignment_id);
410
411 l_var varchar2(1);
412 begin
413
414 OPEN c_get_tax_rec(p_assignment_id, p_date_from);
415
416 FETCH c_get_tax_rec into l_var;
417 IF c_get_tax_rec%notfound then
418 close c_get_tax_rec;
419 return 'N';
420 END IF;
421 close c_get_tax_rec;
422 return 'Y';
423 end;
424
425 END HR_DE_TAX_SUPPORT;