1 PACKAGE BODY PAY_DK_GENERAL AS
2 /* $Header: pydkgenr.pkb 120.2.12000000.2 2007/05/07 11:53:06 saurai noship $ */
3 --
4 g_formula_name ff_formulas_f.formula_name%TYPE;
5 --
6 FUNCTION get_tax_card_details
7 (p_assignment_id IN NUMBER
8 ,p_effective_date IN DATE
9 ,p_tax_card_type OUT NOCOPY VARCHAR2
10 ,p_tax_percentage OUT NOCOPY NUMBER
11 ,p_tax_free_threshold OUT NOCOPY NUMBER
12 ,p_monthly_tax_deduction OUT NOCOPY NUMBER
13 ,p_bi_weekly_tax_deduction OUT NOCOPY NUMBER
14 ,p_weekly_tax_deduction OUT NOCOPY NUMBER
15 ,p_daily_tax_deduction OUT NOCOPY NUMBER) RETURN NUMBER IS
16 --
17 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
18 SELECT eev1.screen_entry_value screen_entry_value
19 FROM per_all_assignments_f asg1
20 ,per_all_assignments_f asg2
21 ,per_all_people_f per
22 ,pay_element_links_f el
23 ,pay_element_types_f et
24 ,pay_input_values_f iv1
25 ,pay_element_entries_f ee
26 ,pay_element_entry_values_f eev1
27 WHERE asg1.assignment_id = p_assignment_id
28 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
29 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
30 AND per.person_id = asg1.person_id
31 AND asg2.person_id = per.person_id
32 AND asg2.primary_flag = 'Y'
33 AND et.element_name = 'Tax Card'
34 AND et.legislation_code = 'DK'
35 AND iv1.element_type_id = et.element_type_id
36 AND iv1.name = p_input_value
37 AND el.business_group_id = per.business_group_id
38 AND el.element_type_id = et.element_type_id
39 AND ee.assignment_id = asg2.assignment_id
40 AND ee.element_link_id = el.element_link_id
41 AND eev1.element_entry_id = ee.element_entry_id
42 AND eev1.input_value_id = iv1.input_value_id
43 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
44 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
45 --
46 l_rec get_details%ROWTYPE;
47 --
48 BEGIN
49 --
50
51
52 OPEN get_details(p_assignment_id , p_effective_date ,'Tax Card Type' );
53 FETCH get_details INTO l_rec;
54 CLOSE get_details;
55
56 p_tax_card_type := l_rec.screen_entry_value ;
57
58 OPEN get_details(p_assignment_id , p_effective_date ,'Tax Percentage' );
59 FETCH get_details INTO l_rec;
60 CLOSE get_details;
61
62 p_tax_percentage := nvl(l_rec.screen_entry_value,0);
63
64 OPEN get_details(p_assignment_id , p_effective_date ,'Tax Free Threshold' );
65 FETCH get_details INTO l_rec;
66 CLOSE get_details;
67
68 p_tax_free_threshold := nvl(l_rec.screen_entry_value,0) ;
69
70 OPEN get_details(p_assignment_id , p_effective_date ,'Monthly Tax Deduction');
71 FETCH get_details INTO l_rec;
72 CLOSE get_details;
73
74 p_monthly_tax_deduction := nvl(l_rec.screen_entry_value,0) ;
75
76 OPEN get_details(p_assignment_id , p_effective_date ,'Bi Weekly Tax Deduction' );
77 FETCH get_details INTO l_rec;
78 CLOSE get_details;
79
80 --p_julian_effective_date := l_rec.julian_effective_date;
81 p_bi_weekly_tax_deduction := nvl(l_rec.screen_entry_value,0) ;
82
83 OPEN get_details(p_assignment_id , p_effective_date ,'Weekly Tax Deduction');
84 FETCH get_details INTO l_rec;
85 CLOSE get_details;
86
87 p_weekly_tax_deduction := nvl(l_rec.screen_entry_value,0) ;
88
89 OPEN get_details(p_assignment_id , p_effective_date ,'Daily Tax Deduction');
90 FETCH get_details INTO l_rec;
91 CLOSE get_details;
92
93 p_daily_tax_deduction := nvl(l_rec.screen_entry_value,0) ;
94
95 --
96 RETURN 1;
97 --
98 END get_tax_card_details;
99 --
100 FUNCTION get_tax_details
101 (p_assignment_id IN NUMBER
102 ,p_effective_date IN DATE
103 ,p_effective_start_date OUT NOCOPY DATE
104 ,p_effective_end_date OUT NOCOPY DATE
105 ) RETURN NUMBER IS
106 --
107 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
108 SELECT ee.effective_start_date effective_start_date, ee.effective_end_date effective_end_date
109 FROM per_all_assignments_f asg
110 ,per_all_people_f per
111 ,pay_element_links_f el
112 ,pay_element_types_f et
113 ,pay_element_entries_f ee
114 WHERE asg.assignment_id = p_assignment_id
115 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
116 AND et.element_name = 'Tax'
117 AND et.legislation_code = 'DK'
118 AND el.business_group_id = per.business_group_id
119 AND el.element_type_id = et.element_type_id
120 AND ee.assignment_id = asg.assignment_id
121 AND ee.element_link_id = el.element_link_id
122 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date;
123 --
124 l_rec get_details%ROWTYPE;
125 --
126 BEGIN
127 --
128
129 OPEN get_details(p_assignment_id , p_effective_date);
130 FETCH get_details INTO l_rec.effective_start_date , l_rec.effective_end_date;
131 CLOSE get_details;
132
133 p_effective_start_date := l_rec.effective_start_date;
134 p_effective_end_date := l_rec.effective_end_date;
135
136 --
137 RETURN 1;
138 --
139 END get_tax_details;
140 --
141
142 FUNCTION get_le_employment_details
143 (p_org_id IN VARCHAR2
144 ,p_le_work_hours OUT NOCOPY NUMBER
145 ,p_freq OUT NOCOPY VARCHAR2
146 )RETURN NUMBER IS
147 --
148 CURSOR get_details(p_org_id VARCHAR2) IS
149 SELECT hoi.org_information3 WORKING_HOURS
150 , hoi.org_information4 FREQ
151 FROM hr_organization_information hoi
152 WHERE hoi.org_information_context='DK_EMPLOYMENT_DEFAULTS'
153 AND hoi.organization_id = p_org_id ;
154
155 l_rec get_details%ROWTYPE;
156 --
157 BEGIN
158 --
159 OPEN get_details(p_org_id);
160 FETCH get_details INTO l_rec;
161 CLOSE get_details;
162
163 p_le_work_hours := l_rec.working_hours;
164 p_freq := l_rec.freq;
165
166 RETURN 1;
167 --
168 END get_le_employment_details;
169 --
170 --
171 FUNCTION get_atp_details
172 (p_assignment_id IN NUMBER
173 ,p_effective_date IN DATE
174 ,p_effective_start_date OUT NOCOPY DATE
175 ,p_effective_end_date OUT NOCOPY DATE
176 ) RETURN NUMBER IS
177 --
178 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
179 SELECT ee.effective_start_date effective_start_date, ee.effective_end_date effective_end_date
180 FROM per_all_assignments_f asg
181 ,per_all_people_f per
182 ,pay_element_links_f el
183 ,pay_element_types_f et
184 ,pay_element_entries_f ee
185 WHERE asg.assignment_id = p_assignment_id
186 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
187 AND et.element_name = 'Employee ATP'
188 AND et.legislation_code = 'DK'
189 AND el.business_group_id = per.business_group_id
190 AND el.element_type_id = et.element_type_id
191 AND ee.assignment_id = asg.assignment_id
192 AND ee.element_link_id = el.element_link_id
193 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date;
194 --
195 l_rec get_details%ROWTYPE;
196 --
197 BEGIN
198 --
199
200 OPEN get_details(p_assignment_id , p_effective_date);
201 FETCH get_details INTO l_rec.effective_start_date , l_rec.effective_end_date;
202 CLOSE get_details;
203
204 p_effective_start_date := l_rec.effective_start_date;
205 p_effective_end_date := l_rec.effective_end_date;
206
207 --
208 RETURN 1;
209 --
210 END get_atp_details;
211
212
213 --
214 FUNCTION get_sp_details
215 (p_payroll_action_id IN NUMBER
216 ,p_cvr_number OUT NOCOPY VARCHAR2
217 ) RETURN NUMBER IS
218
219 CURSOR get_sp_details( p_payroll_action_id NUMBER) IS
220 SELECT hoi2.org_information1 cvr_number
221 FROM HR_ORGANIZATION_INFORMATION hoi1
222 ,HR_ORGANIZATION_INFORMATION hoi2
223 ,HR_ORGANIZATION_UNITS hou
224 ,PAY_PAYROLL_ACTIONS ppa
225 WHERE ppa.payroll_action_id = p_payroll_action_id
226 and hoi1.org_information_context ='CLASS'
227 and hoi1.org_information1 ='DK_SERVICE_PROVIDER'
228 and hoi1.ORG_INFORMATION2 ='Y'
229 and hoi2.ORG_INFORMATION_CONTEXT= 'DK_SERVICE_PROVIDER_DETAILS'
230 and hoi2.organization_id = hoi1.organization_id
231 and hou.organization_id = hoi1.organization_id
232 and hou.business_group_id = ppa.BUSINESS_GROUP_ID
233 and ppa.EFFECTIVE_DATE BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, ppa.EFFECTIVE_DATE);
234
235 --
236 l_rec get_sp_details%ROWTYPE;
237 --
238 BEGIN
239 --
240
241 OPEN get_sp_details(p_payroll_action_id);
242 FETCH get_sp_details INTO l_rec.cvr_number;
243 CLOSE get_sp_details;
244
245 p_cvr_number := l_rec.cvr_number;
246
247 --
248 RETURN 1;
249 --
250 END get_sp_details;
251
252
253
254
255 FUNCTION get_atp_override_hours
256 (p_assignment_id NUMBER
257 , p_effective_date DATE
258 ) RETURN NUMBER IS
259 --
260 CURSOR get_details(p_assignment_id NUMBER,p_effective_date DATE ) IS
261 SELECT eev1.screen_entry_value atp_override_hours
262 FROM per_all_assignments_f asg1
263 ,per_all_assignments_f asg2
264 ,per_all_people_f per
265 ,pay_element_links_f el
266 ,pay_element_types_f et
267 ,pay_input_values_f iv1
268 ,pay_element_entries_f ee
269 ,pay_element_entry_values_f eev1
270 WHERE asg1.assignment_id = p_assignment_id
271 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
272 AND per.person_id = asg1.person_id
273 AND asg2.person_id = per.person_id
274 AND asg2.primary_flag = 'Y'
275 AND et.element_name = 'ATP Override Hours'
276 AND et.legislation_code = 'DK'
277 AND iv1.element_type_id = et.element_type_id
278 AND iv1.name = 'ATP Override Hours'
279 AND el.business_group_id = per.business_group_id
280 AND el.element_type_id = et.element_type_id
281 AND ee.assignment_id = asg2.assignment_id
282 AND ee.element_link_id = el.element_link_id
283 AND eev1.element_entry_id = ee.element_entry_id
284 AND eev1.input_value_id = iv1.input_value_id
285 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
286 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
287 --
288 l_rec get_details%ROWTYPE;
289 --
290 BEGIN
291 --
292 OPEN get_details(p_assignment_id ,p_effective_date);
293 FETCH get_details INTO l_rec;
294 CLOSE get_details;
295 --
296 RETURN NVL(l_rec.atp_override_hours, -1);
297 --
298 END get_atp_override_hours;
299
300 FUNCTION get_holiday_details
301 (p_assignment_id IN NUMBER
302 ,p_effective_date IN DATE
303 ,p_abs_start_date IN DATE
304 ,p_abs_end_date IN DATE
305 ,p_start_date OUT NOCOPY DATE
306 ,p_end_date OUT NOCOPY DATE
307 ,p_over_days OUT NOCOPY NUMBER
308 ,p_over_hours OUT NOCOPY NUMBER ) RETURN NUMBER IS
309 --
310 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
311 SELECT ee.element_entry_id element_entry_id
312 , eev1.screen_entry_value screen_entry_value
313 , iv1.name
314 FROM per_all_assignments_f asg1
315 ,per_all_assignments_f asg2
316 ,per_all_people_f per
317 ,pay_element_links_f el
318 ,pay_element_types_f et
319 ,pay_input_values_f iv1
320 ,pay_element_entries_f ee
321 ,pay_element_entry_values_f eev1
322 WHERE asg1.assignment_id = p_assignment_id
323 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
324 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
325 AND per.person_id = asg1.person_id
326 AND asg2.person_id = per.person_id
327 AND et.element_name = 'Override Holiday Duration'
328 AND et.legislation_code = 'DK'
329 AND iv1.element_type_id = et.element_type_id
330 AND iv1.name in ('Start Date', 'End Date', 'Override Hours', 'Override Days')
331 AND el.business_group_id = per.business_group_id
332 AND el.element_type_id = et.element_type_id
333 AND ee.assignment_id = asg2.assignment_id
334 AND ee.element_link_id = el.element_link_id
335 AND eev1.element_entry_id = ee.element_entry_id
336 AND eev1.input_value_id = iv1.input_value_id
337 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
338 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
339 ORDER BY ee.element_entry_id;
340 --
341 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
342 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
343 eename pay_input_values_f.name%TYPE );
344 l_rec l_record;
345 TYPE l_table is table of l_record index by BINARY_INTEGER;
346 l_tab l_table;
347
348 l_start_date date;
349 l_end_date date;
350 l_over_hours number;
351 l_over_days number;
352 l_counter number ;
353 l_bool_match boolean;
354 l_num_match number;
355 --
356 BEGIN
357 --
358 l_counter := 1;
359 l_bool_match := FALSE;
360
361 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
362 OPEN get_details(p_assignment_id , p_effective_date );
363 -- Assign the values to a table type
364 FETCH get_details BULK COLLECT INTO l_tab;
365 CLOSE get_details;
366
367 -- Loop through each values for processing.
368 FOR l_cur in 1..l_tab.count LOOP
369 -- Assign values to local variables.
370 IF l_tab(l_cur).eename = 'Start Date' THEN
371 l_start_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss') ;
372 elsif l_tab(l_cur).eename = 'End Date' THEN
373 l_end_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
374 elsif l_tab(l_cur).eename = 'Override Days' THEN
375 l_over_days := l_tab(l_cur).eevalue;
376 elsif l_tab(l_cur).eename = 'Override Hours' THEN
377 l_over_hours := l_tab(l_cur).eevalue;
378 end if;
379 -- Check no. of input values of override element is 4
380 IF l_counter < 4 then
381 l_counter := l_counter + 1;
382 else
383 -- Check override element's start and end date matches with Absent element.
384 if l_start_date = p_abs_start_date and l_end_date = p_abs_end_date then
385 -- Multiple entry exists with same start and end date
386 IF l_bool_match THEN
387 p_start_date := null;
388 p_end_date := null;
389 p_over_days := null;
390 p_over_hours := null;
391 return -1;
392 -- Exact match found
393 ELSE
394 l_bool_match := True;
395 END IF;
396 -- Assign input values to output variables.
397 p_start_date := l_start_date;
398 p_end_date := l_end_date;
399 p_over_days := l_over_days;
400 p_over_hours := l_over_hours;
401 end if;
402 l_counter := 1;
403 end if;
404 END LOOP;
405
406 -- Match found successfully
407 IF p_start_date is not null then
408 RETURN 1;
409 -- Override element exists but date doesnt match.
410 elsif p_start_date is null and l_tab.count > 0 then
411 RETURN 2;
412 -- No override element attached
413 else
414 RETURN 0;
415 end if;
416 --
417 END get_holiday_details;
418
419 --------------------------------------------------------------------------
420 -- --
421 -- Name : get_IANA_charset --
422 -- Type : Function --
428 -- OUT : N/A --
423 -- Access : Public --
424 -- Description : Function to IANA charset equivalent of --
425 -- NLS_CHARACTERSET --
426 -- Parameters : --
427 -- IN : N/A --
429 -- RETURN : VARCHAR2 --
430 -- --
431 --------------------------------------------------------------------------
432 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
433 CURSOR csr_get_iana_charset IS
434 SELECT tag
435 FROM fnd_lookup_values
436 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
437 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
438 INSTR(USERENV('LANGUAGE'), '.') + 1)
439 AND language = 'US';
440
441 lv_iana_charset fnd_lookup_values.tag%type;
442 BEGIN
443 OPEN csr_get_iana_charset;
444 FETCH csr_get_iana_charset INTO lv_iana_charset;
445 CLOSE csr_get_iana_charset;
446
447 hr_utility.trace('IANA Charset = '||lv_iana_charset);
448 RETURN (lv_iana_charset);
449 END get_IANA_charset;
450 --------------------------------------------------------------------------
451 FUNCTION get_hour_sal_flag
452 (p_assignment_id IN NUMBER
453 ,p_effective_date IN DATE
454 ) RETURN VARCHAR2 IS
455
456 CURSOR csr_get_asg_hs_flag( p_assignment_id IN NUMBER
457 ,p_effective_date IN DATE
458 ) IS
459 SELECT paaf.hourly_salaried_code
460 FROM per_all_assignments_f paaf
461 WHERE paaf.assignment_id = p_assignment_id
462 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
463
464 rec_get_asg_hs_flag csr_get_asg_hs_flag%ROWTYPE;
465
466 BEGIN
467 OPEN csr_get_asg_hs_flag( p_assignment_id,p_effective_date);
468 FETCH csr_get_asg_hs_flag INTO rec_get_asg_hs_flag;
469 CLOSE csr_get_asg_hs_flag;
470 RETURN rec_get_asg_hs_flag.hourly_salaried_code;
471
472 END get_hour_sal_flag;
473
474
475 --
476 END PAY_DK_GENERAL;
[Home] [Help]