[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_LABOR_COST_REPORT
Source
1 PACKAGE BODY pay_dk_labor_cost_report AS
2 /* $Header: pydkalcr.pkb 120.3 2011/02/04 09:50:28 knadhan noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4 g_package VARCHAR2(33) := ' PAY_DK_LABOR_COST_REPORT.';
5 EOL VARCHAR2(5) := fnd_global.local_chr(10);
6 -------------------------------------------------------------------------------
7
8 PROCEDURE generate( p_legal_employer NUMBER
9 ,p_payroll NUMBER
10 ,p_year NUMBER
11 ,p_template_name VARCHAR2
12 ,p_xml OUT NOCOPY CLOB) IS
13
14 l_legal_employer_name hr_organization_units.name%TYPE;
15 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
16 l_business_group_id hr_organization_units.business_group_id%TYPE;
17 l_string VARCHAR2(32767) := NULL;
18 l_xml CLOB;
19
20 l_value_0050 NUMBER := 0;
21 l_value_0051 NUMBER := 0;
22 l_value_0052 NUMBER := 0;
23 l_value_0120 NUMBER := 0;
24 l_start_date DATE;
25 l_end_date DATE;
26 l_first_end_date DATE; --9059957
27 l_last_end_date DATE; --9059957
28 l_end_date_before_age_change DATE; --9059957
29
30 l_employer_atp_balance_id NUMBER :=0;
31 l_employee_atp_balance_id NUMBER :=0;
32 l_worked_hours_id NUMBER :=0;
33 l_total_atp_hours_id NUMBER :=0; --9059957
34
35 l_full_atp_contribution NUMBER :=0;
36 l_employer_atp NUMBER :=0;
37 l_employee_atp NUMBER :=0;
38 l_total_atp NUMBER :=0;
39 l_trainee_total_atp NUMBER :=0;
40 l_atp_employee_count NUMBER :=0;
41 l_atp_trainee_count NUMBER :=0;
42 l_aer_rate NUMBER :=0;
43 l_total_employer_aer NUMBER :=0;
44 l_total_aer NUMBER :=0;
45 l_normal_hours_per_year NUMBER :=0;
46 l_non_atp_worked_hours NUMBER :=0;
47 l_worked_hours NUMBER :=0;
48 l_non_atp_employee_count NUMBER :=0;
49 l_total_atp_hours NUMBER :=0; --9059957
50 l_full_atp_hours NUMBER :=0; --9059957
51 l_partial_atp_hours NUMBER :=0; --9059957
52 l_atp_employee_age_low NUMBER :=0; --9059957
53 l_atp_employee_age_high NUMBER :=0; --9059957
54 l_age_on_start NUMBER :=0; --9059957
55 l_age_on_end NUMBER :=0; --9059957
56 l_assgid_before_change NUMBER :=0; --9059957
57 ---------------------
58 CURSOR csr_legal_employer_details(p_legal_employer_id NUMBER) IS
59 SELECT
60 hou.name, hou.business_group_id
61 FROM
62 hr_organization_units hou
63 WHERE hou.organization_id = p_legal_employer_id;
64 ---------------------
65 CURSOR csr_payroll_name(p_payroll_id NUMBER, p_effective_date DATE) IS
66 SELECT
67 pay.payroll_name
68 FROM
69 pay_payrolls_f pay
70 WHERE pay.payroll_id = p_payroll_id
71 AND p_effective_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
72 ---------------------
73 CURSOR csr_global_value(p_global_name VARCHAR2,
74 p_effective_date DATE) IS
75 SELECT
76 fnd_number.canonical_to_number(global_value) -- 10034051
77 FROM
78 ff_globals_f
79 WHERE global_name = p_global_name
80 AND legislation_code = 'DK'
81 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
82 ---------------------
83 CURSOR cur_defined_balance_id(BAL_NAME VARCHAR2, DIM_NAME VARCHAR2)
84 IS
85 SELECT
86 defined_balance_id
87 FROM
88 pay_balance_types pbt,
89 pay_defined_balances pdb,
90 pay_balance_dimensions pbd
91 WHERE pdb.balance_type_id = pbt.balance_type_id
92 AND pdb.balance_dimension_id = pbd.balance_dimension_id
93 AND pbt.balance_name = bal_name
94 AND pbd.database_item_suffix = dim_name
95 AND pbt.legislation_code = 'DK'
96 AND pdb.legislation_code = 'DK'
97 AND pbd.legislation_code = 'DK';
98 ---------------------
99 CURSOR csr_atp_assignments
100 ( p_date_from DATE
101 ,p_date_to DATE
102 ,p_business_group_id NUMBER
103 ,p_legal_employer_id NUMBER
104 ,p_payroll_id NUMBER
105 ) IS
106 SELECT
107 asg.assignment_id assignment_id,
108 MAX(pap.per_information3) trainee,
109 MAX(paa.assignment_action_id) assignment_action_id,
110 MAX(pap.date_of_birth) date_of_birth, --9059957
111 MAX(asg.payroll_id) payroll_id --9059957
112 FROM
113 per_all_people_f pap,
114 per_all_assignments_f asg,
115 pay_payroll_actions ppa,
116 pay_assignment_actions paa,
117 pay_run_results prr,
118 pay_element_types_f pet
119 WHERE pap.person_id = asg.person_id
120 AND asg.payroll_id = ppa.payroll_id
121 AND asg.assignment_id = paa.assignment_id
122 AND ppa.payroll_action_id = paa.payroll_action_id
123 AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
124 AND paa.assignment_action_id = prr.assignment_action_id
125 AND pet.element_type_id = prr.element_type_id
126 AND pet.legislation_code = 'DK'
127 AND asg.payroll_id=NVL(p_payroll_id,asg.payroll_id)
128 AND ppa.business_group_id = p_business_group_id
129 AND paa.tax_unit_id = p_legal_employer_id
130 AND pet.element_name IN ( 'Employee ATP' , 'Employer ATP' )
131 AND ppa.date_earned BETWEEN p_date_from AND p_date_to
132 AND asg.assignment_status_type_id = 1
133 AND pap.current_employee_flag = 'Y'
134 AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
135 AND ppa.date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
136 AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
137 GROUP BY
138 asg.assignment_id;
139 ---------------------
140 CURSOR csr_non_atp_assignments
141 ( p_date_from DATE
142 ,p_date_to DATE
143 ,p_business_group_id NUMBER
144 ,p_legal_employer_id NUMBER
145 ,p_payroll_id NUMBER
146 ) IS
147 SELECT
148 asg.assignment_id assignment_id,
149 max(paa.assignment_action_id) assignment_action_id
150 FROM
151 per_all_people_f pap,
152 per_all_assignments_f asg,
153 pay_payroll_actions ppa,
154 pay_assignment_actions paa,
155 pay_run_results prr
156 WHERE pap.person_id = asg.person_id
157 AND pap.current_employee_flag = 'Y'
158 AND asg.payroll_id = ppa.payroll_id
159 AND asg.assignment_id = paa.assignment_id
160 AND asg.payroll_id = nvl(p_payroll_id
161 ,asg.payroll_id)
162 AND asg.assignment_status_type_id = 1
163 AND ppa.payroll_action_id = paa.payroll_action_id
164 AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
165 AND ppa.business_group_id = p_business_group_id
166 AND ppa.date_earned BETWEEN p_date_from AND p_date_to
167 AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
168 AND ppa.date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
169 AND paa.tax_unit_id = p_legal_employer_id
170 AND paa.assignment_action_id = prr.assignment_action_id
171 AND NOT EXISTS (
172 SELECT
173 1
174 FROM
175 pay_payroll_actions ppa1,
176 pay_assignment_actions paa1,
177 pay_run_results prr1,
178 pay_element_types_f pet
179 WHERE pet.legislation_code = 'DK'
180 AND pet.element_name IN ( 'Employee ATP' , 'Employer ATP' )
181 AND pet.element_type_id = prr1.element_type_id
182 AND ppa1.payroll_action_id = paa1.payroll_action_id
183 AND paa1.assignment_id = asg.assignment_id
184 AND paa1.assignment_action_id = prr1.assignment_action_id
185 AND paa1.tax_unit_id = p_legal_employer_id
186 AND ppa1.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
187 AND ppa1.business_group_id = p_business_group_id
188 AND ppa1.date_earned BETWEEN p_date_from AND p_date_to
189 AND ppa1.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date)
190 GROUP BY
191 asg.assignment_id;
192 ---------------------
193 --9059957--begin
194 CURSOR csr_end_date_before_birthday
195 ( p_date_of_birth DATE
196 ,p_report_year NUMBER
197 ,p_payroll_id NUMBER
198 ) IS
199 SELECT
200 start_date-1 before_date
201 FROM
202 per_time_periods
203 WHERE payroll_id = p_payroll_id
204 AND to_date(to_char(p_date_of_birth,'ddmm')||p_report_year,'ddmmyyyy')
205 BETWEEN start_date AND end_date;
206 ---------------------
207 CURSOR csr_end_dates
208 ( p_report_year NUMBER
209 ,p_payroll_id NUMBER
210 ) IS
211 SELECT
212 min(end_date) first_end_date,
213 max(end_date) last_end_date
214 FROM
215 per_time_periods
216 WHERE payroll_id = p_payroll_id
217 AND to_char(end_date,'yyyy') = p_report_year;
218 ---------------------
219 CURSOR csr_assgid_before_change
220 ( p_assignment_id NUMBER
221 ,p_business_group_id NUMBER
222 ,p_legal_employer_id NUMBER
223 ,p_payroll_id NUMBER
224 ,p_date_from DATE
225 ,p_date_to DATE
226 ) IS
227 SELECT
228 max(paa.assignment_action_id) assignment_action_id
229 FROM
230 pay_payroll_actions ppa,
231 pay_assignment_actions paa
232 WHERE paa.assignment_id = p_assignment_id
233 AND paa.tax_unit_id = p_legal_employer_id
234 AND ppa.payroll_action_id = paa.payroll_action_id
235 AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
236 AND ppa.business_group_id = p_business_group_id
237 AND ppa.payroll_id = p_payroll_id
238 AND ppa.date_earned BETWEEN p_date_from AND p_date_to;
239 --9059957--end
240 -------------------------------------------------------------------------------
241 BEGIN
242 IF g_debug THEN
243 hr_utility.set_location(' Entering Procedure GENERATE',1);
244 END IF;
245
246 l_start_date := TO_DATE('01/01/'||TO_CHAR(P_YEAR), 'DD/MM/YYYY');
247 l_end_date := TO_DATE('31/12/'||TO_CHAR(P_YEAR), 'DD/MM/YYYY');
248
249 OPEN csr_legal_employer_details(p_legal_employer);
250 FETCH csr_legal_employer_details INTO l_legal_employer_name,l_business_group_id;
251 CLOSE csr_legal_employer_details;
252
253 OPEN csr_payroll_name(p_payroll,l_end_date);
254 FETCH csr_payroll_name INTO l_payroll_name;
255 CLOSE csr_payroll_name;
256
257 /* Balance Fetches */
258 OPEN cur_defined_balance_id('Employer ATP Deductions', '_ASG_LE_YTD');
259 FETCH cur_defined_balance_id INTO l_employer_atp_balance_id;
260 CLOSE cur_defined_balance_id;
261
262 OPEN cur_defined_balance_id('Employee ATP Deductions', '_ASG_LE_YTD');
263 FETCH cur_defined_balance_id INTO l_employee_atp_balance_id;
264 CLOSE cur_defined_balance_id;
265
266 OPEN cur_defined_balance_id('Worked Hours', '_ASG_LE_YTD');
267 FETCH cur_defined_balance_id INTO l_worked_hours_id;
268 CLOSE cur_defined_balance_id;
269
270 --9059957--begin
271 OPEN cur_defined_balance_id('Total ATP Hours', '_ASG_LE_YTD');
272 FETCH cur_defined_balance_id INTO l_total_atp_hours_id;
273 CLOSE cur_defined_balance_id;
274 --9059957--end
275
276
277 /* Global Fetches */
278 OPEN csr_global_value('DK_AER_ATPAMOUNT_QUARTER',l_end_date);
279 FETCH csr_global_value INTO l_full_atp_contribution;
280 CLOSE csr_global_value;
281 l_full_atp_contribution := l_full_atp_contribution*4; -- QTR x 4 = YEAR
282
283 OPEN csr_global_value('DK_AER_RATE',l_end_date);
284 FETCH csr_global_value INTO l_aer_rate;
285 CLOSE csr_global_value;
286 l_aer_rate := l_aer_rate*4; -- QTR x 4 = Year
287
288 OPEN csr_global_value('DK_HOURS_IN_WEEK',l_end_date);
289 FETCH csr_global_value INTO l_normal_hours_per_year;
290 CLOSE csr_global_value;
291 l_normal_hours_per_year := l_normal_hours_per_year*52; -- Week x 52 = Year
292
293 --9059957--begin
294 OPEN csr_global_value('DK_ATP_AGE_LOW',l_end_date);
295 FETCH csr_global_value INTO l_atp_employee_age_low;
296 CLOSE csr_global_value;
297
298 OPEN csr_global_value('DK_ATP_AGE_HIGH',l_end_date);
299 FETCH csr_global_value INTO l_atp_employee_age_high;
300 CLOSE csr_global_value;
301 --9059957--end
302
303 FOR c_atp_assignments IN csr_atp_assignments(l_start_date,l_end_date,
304 l_business_group_id,p_legal_employer,p_payroll)
305 LOOP
306 l_employer_atp := pay_balance_pkg.get_value(
307 p_defined_balance_id => l_employer_atp_balance_id,
308 p_assignment_action_id => c_atp_assignments.assignment_action_id,
309 p_tax_unit_id => p_legal_employer,
310 p_jurisdiction_code => NULL,
311 p_source_id => NULL,
312 p_tax_group => NULL,
313 p_date_earned => NULL);
314
315 l_employee_atp := pay_balance_pkg.get_value(
316 p_defined_balance_id => l_employee_atp_balance_id,
317 p_assignment_action_id => c_atp_assignments.assignment_action_id,
318 p_tax_unit_id => p_legal_employer,
319 p_jurisdiction_code => NULL,
320 p_source_id => NULL,
321 p_tax_group => NULL,
322 p_date_earned => NULL);
323
324 l_total_atp := l_total_atp + NVL(l_employer_atp,0)+ NVL(l_employee_atp,0);
325
326 IF NVL(c_atp_assignments.trainee,'N') = 'Y' THEN
327 l_trainee_total_atp := l_trainee_total_atp
328 + NVL(l_employer_atp,0)+ NVL(l_employee_atp,0);
329 END IF;
330
331 --9059957--begin
332
333 OPEN csr_end_dates(p_year,c_atp_assignments.payroll_id);
334 FETCH csr_end_dates INTO l_first_end_date,l_last_end_date;
335 CLOSE csr_end_dates;
336 --9127531-- TRUNC added
337 l_age_on_start := TRUNC(MONTHS_BETWEEN(l_first_end_date,c_atp_assignments.date_of_birth)/12);
338 l_age_on_end := TRUNC(MONTHS_BETWEEN(l_last_end_date,c_atp_assignments.date_of_birth)/12);
339
340 -- If age of the Assignment is below ATP LOW at year end
341 -- or above ATP HIGH at the beginning of the year
342 -- Completely no ATP condition
343 IF (l_age_on_end < l_atp_employee_age_low) OR
344 (l_age_on_start > l_atp_employee_age_high)
345 THEN
346 l_full_atp_hours := pay_balance_pkg.get_value(
347 p_defined_balance_id => l_total_atp_hours_id,
348 p_assignment_action_id => c_atp_assignments.assignment_action_id,
349 p_tax_unit_id => p_legal_employer,
350 p_jurisdiction_code => NULL,
351 p_source_id => NULL,
352 p_tax_group => NULL,
353 p_date_earned => NULL);
354
355 l_total_atp_hours := l_total_atp_hours + NVL(l_full_atp_hours,0); --9258608
356 l_full_atp_hours :=0;
357 ELSIF
358 -- If reached age ATP LOW or ATP HIGH during the year.
359 -- Partially No ATP condition
360 (l_age_on_start = (l_atp_employee_age_low -1) AND
361 l_age_on_end = l_atp_employee_age_low)
362 OR
363 (l_age_on_start = (l_atp_employee_age_high) AND
364 l_age_on_end = l_atp_employee_age_high + 1) --9127531
365
366 THEN
367
368 OPEN csr_end_date_before_birthday(c_atp_assignments.date_of_birth,
369 p_year,c_atp_assignments.payroll_id);
370 FETCH csr_end_date_before_birthday INTO l_end_date_before_age_change;
371 CLOSE csr_end_date_before_birthday;
372
373 OPEN csr_assgid_before_change(c_atp_assignments.assignment_action_id
374 ,l_business_group_id,p_legal_employer
375 ,c_atp_assignments.payroll_id
376 ,l_start_date,l_end_date_before_age_change);
377 FETCH csr_assgid_before_change INTO l_assgid_before_change;
378 CLOSE csr_assgid_before_change;
379 IF l_assgid_before_change IS NOT NULL THEN --10322489
380 l_partial_atp_hours := pay_balance_pkg.get_value(
381 p_defined_balance_id => l_total_atp_hours_id,
382 p_assignment_action_id => l_assgid_before_change,
383 p_tax_unit_id => p_legal_employer,
384 p_jurisdiction_code => NULL,
385 p_source_id => NULL,
386 p_tax_group => NULL,
387 p_date_earned => NULL);
388 END IF; --10322489
389 --If Age reached ATP LOW during the year.
390 IF (l_age_on_start = (l_atp_employee_age_low -1) AND
391 l_age_on_end = l_atp_employee_age_low) --9127531
392 THEN
393 l_total_atp_hours := l_total_atp_hours + NVL(l_partial_atp_hours,0); --9258608
394
395 --If Age crossed ATP HIGH during the year.
396 ELSIF (l_age_on_start = (l_atp_employee_age_high) AND
397 l_age_on_end = l_atp_employee_age_high + 1) --9127531
398 THEN
399 l_full_atp_hours := pay_balance_pkg.get_value(
400 p_defined_balance_id => l_total_atp_hours_id,
401 p_assignment_action_id => c_atp_assignments.assignment_action_id,
402 p_tax_unit_id => p_legal_employer,
403 p_jurisdiction_code => NULL,
404 p_source_id => NULL,
405 p_tax_group => NULL,
406 p_date_earned => NULL);
407
408 l_total_atp_hours := l_total_atp_hours + (NVL(l_full_atp_hours,0)
409 - NVL(l_partial_atp_hours,0)); --9258608
410 l_full_atp_hours := 0;
411 END IF;
412 l_partial_atp_hours :=0;
413 END IF;
414 --9059957--end
415 l_employer_atp := 0;
416 l_employee_atp := 0;
417
418 END LOOP;
419
420 FOR c_non_atp_assignments IN csr_non_atp_assignments(l_start_date,l_end_date,
421 l_business_group_id,p_legal_employer,p_payroll)
422 LOOP
423 l_worked_hours := pay_balance_pkg.get_value(
424 p_defined_balance_id => l_worked_hours_id,
425 p_assignment_action_id => c_non_atp_assignments.assignment_action_id,
426 p_tax_unit_id => p_legal_employer,
427 p_jurisdiction_code => NULL,
428 p_source_id => NULL,
429 p_tax_group => NULL,
430 p_date_earned => NULL);
431
432 l_non_atp_worked_hours := l_non_atp_worked_hours + NVL(l_worked_hours,0);
433 l_worked_hours := 0;
434 END LOOP;
435
436 /* Computations*/
437 l_atp_employee_count := 0 + round((l_total_atp/l_full_atp_contribution),0);
438 l_atp_trainee_count := 0 + round((l_trainee_total_atp/l_full_atp_contribution),0);
439 l_non_atp_employee_count := 0 + round(((l_non_atp_worked_hours
440 + l_total_atp_hours)/l_normal_hours_per_year),0); --9059957
441 l_total_employer_aer := l_atp_employee_count - l_atp_trainee_count;
442
443 IF l_total_employer_aer < 0 OR NVL(l_total_atp,0) = 0 THEN
444 l_total_aer := 0;
445 hr_utility.set_message (801, 'PAY_377056_DK_NEGATIVE_ERR');
446 ELSE
447 l_total_aer := round((l_total_employer_aer * l_aer_rate),2);
448 END IF;
449
450 /* Final Values */
451 l_value_0050 := l_atp_employee_count;
452 l_value_0051 := l_atp_trainee_count;
453 l_value_0052 := l_non_atp_employee_count;
454 l_value_0120 := l_total_aer;
455
456 /* Build XML Structure */
457 l_string := l_string || '<PYDKALCR>'||EOL;
458 l_string := l_string || '<LEGAL_EMPLOYER>'|| l_legal_employer_name ||'</LEGAL_EMPLOYER>'||EOL;
459 l_string := l_string || '<PAYROLL>'|| l_payroll_name ||'</PAYROLL>'||EOL;
460 l_string := l_string || '<YEAR>'|| p_year ||'</YEAR>'||EOL;
461 l_string := l_string || '<VALUE0050>'|| l_value_0050 ||'</VALUE0050>'||EOL;
462 l_string := l_string || '<VALUE0051>'|| l_value_0051 ||'</VALUE0051>'||EOL;
463 l_string := l_string || '<VALUE0052>'|| l_value_0052 ||'</VALUE0052>'||EOL;
464 l_string := l_string || '<VALUE0120>'|| l_value_0120 ||'</VALUE0120>'||EOL;
465 l_string := l_string || '</PYDKALCR>';
466
467 /* Writing XML File */
468 dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
469 dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
470 dbms_lob.writeAppend( l_xml, length(l_string), l_string);
471 p_xml := l_xml;
472 dbms_lob.freeTemporary(l_xml);
473
474 IF g_debug THEN
475 hr_utility.set_location(' Leaving Procedure GENERATE',2);
476 END IF;
477
478 EXCEPTION
479 WHEN others THEN
480 IF g_debug THEN
481 hr_utility.set_location('error raised in GENERATE ',9);
482 END IF;
483 RAISE;
484 END generate;
485
486 -------------------------------------------------------------------------------
487
488 END pay_dk_labor_cost_report;