[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_PTU_CALC
Source
1 PACKAGE BODY pay_mx_PTU_calc AS
2 /* $Header: paymxprofitshare.pkb 120.13.12000000.5 2007/04/07 02:37:23 vpandya noship $ */
3
4 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 /******************************************************************************
7 ** Global Variables
8 ******************************************************************************/
9 gv_package VARCHAR2(100);
10
11 -------------------------------------------------------------------------------
12 -- Name : get_payroll_action_info
13 -- Purpose : This returns the Payroll Action level
14 -- information for Profit Sharing process.
15 -- Arguments : p_payroll_action_id - Payroll_Action_id of the process
16 -- p_start_date - Start of Profit Sharing Year
17 -- p_effective_date - Date Earned for Profit Sharing Year
18 -- p_business_group_id - Business Group ID
19 -- p_legal_employer_id - Legal Employer ID when submitting PTU
20 -- p_asg_set_id - Assignment Set ID when submitting PTU
21 -- p_batch_name - Batch Name for the BEE batch header.
22 ------------------------------------------------------------------------------
23 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
24 ,p_start_date OUT NOCOPY DATE
25 ,p_effective_date OUT NOCOPY DATE
26 ,p_business_group_id OUT NOCOPY NUMBER
27 ,p_legal_employer_id OUT NOCOPY NUMBER
28 ,p_asg_set_id OUT NOCOPY NUMBER
29 ,p_batch_name OUT NOCOPY VARCHAR2
30 )
31 IS
32 CURSOR c_payroll_Action_info
33 (cp_payroll_action_id IN NUMBER) IS
34 SELECT start_date,
35 effective_date,
36 business_group_id,
37 pay_mx_utility.get_parameter( 'BATCH_NAME',
38 legislative_parameters) BATCH_NAME,
39 pay_mx_utility.get_parameter('LEGAL_EMPLOYER',
40 legislative_parameters) LEGAL_EMPLOYER,
41 pay_mx_utility.get_parameter('ASG_SET_ID',
42 legislative_parameters) ASG_SET_ID
43 FROM pay_payroll_actions
44 WHERE payroll_action_id = cp_payroll_action_id;
45
46 ld_start_date DATE;
47 ld_effective_date DATE;
48 ln_business_group_id NUMBER;
49 ln_asg_set_id NUMBER;
50 ln_legal_er_id NUMBER;
51 lv_incl_temp_EEs VARCHAR2(1);
52 ln_min_days NUMBER;
53 lv_procedure_name VARCHAR2(100);
54
55 lv_error_message VARCHAR2(200);
56 ln_step NUMBER;
57 lv_batch_name pay_batch_headers.batch_name%TYPE;
58
59 BEGIN
60 lv_procedure_name := '.get_payroll_action_info';
61
62 hr_utility.trace('Entering ' ||gv_package || lv_procedure_name);
63 ln_step := 1;
64 OPEN c_payroll_action_info(p_payroll_action_id);
65 FETCH c_payroll_action_info INTO ld_start_date,
66 ld_effective_date,
67 ln_business_group_id,
68 lv_batch_name,
69 ln_legal_er_id,
70 ln_asg_set_id;
71 CLOSE c_payroll_action_info;
72
73 hr_utility.set_location(gv_package || lv_procedure_name, 10);
74
75 p_start_date := ld_start_date;
76 p_effective_date := ld_effective_date;
77 p_business_group_id := ln_business_group_id;
78 p_legal_employer_id := ln_legal_er_id;
79 -- p_incl_temp_EEs := lv_incl_temp_EEs;
80 -- p_min_days_worked := ln_min_days;
81 p_asg_set_id := ln_asg_set_id;
82 p_batch_name := lv_batch_name;
83
84 hr_utility.trace('Leaving ' ||gv_package || lv_procedure_name);
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
89 gv_package || lv_procedure_name;
90
91 hr_utility.trace(lv_error_message || '-' || SQLERRM);
92
93 lv_error_message :=
94 pay_emp_action_arch.set_error_message(lv_error_message);
95
96 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
97 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
98 hr_utility.raise_error;
99
100 END get_payroll_action_info;
101
102 -------------------------------------------------------------------------------
103 -- Name : get_capped_average_earnings
104 -- Purpose : This procedure returns the capped average earnings for an
105 -- an employee. The capped earnings are used for calculating
106 -- the wage component of the employees share in company's
107 -- profit. The capped earnings are calculated based on the
108 -- method set at the legal employer level. Users can choose to
109 -- use one of the following -
110 -- Daily Wages
111 -- Annual Earnings
112 -- Prorated Annual Earnings
113
114 -- Arguments : p_ptu_calc_method - Calculation method for PTU
115 -- p_days_in_year - Actual number of days in the year
116 -- p_business_group_id - Business Group ID
117 -- p_legal_employer_id - Legal Employer ID when submitting PTU
118 -- p_factor_B - Factor B (Number of Days Worked)
119 -- p_factor_C - Factor C (Annual Earnings)
120 -- p_factor_D - Factor D (Daily Wage of the highest paid EE
121 -- p_factor_D_used - Factor D Used in the calculation.
122 -- p_factor_E - Factor E (Capped Earnings)
123 ------------------------------------------------------------------------------
124 PROCEDURE get_capped_average_earnings (
125 p_ptu_calc_method IN VARCHAR2,
126 p_days_in_year IN NUMBER,
127 p_business_group_id IN NUMBER,
128 p_legal_employer_id IN NUMBER,
129 p_factor_B IN NUMBER,
130 p_factor_C IN NUMBER,
131 p_factor_D IN NUMBER,
132 p_factor_D_used OUT NOCOPY NUMBER,
133 p_factor_E OUT NOCOPY NUMBER) IS
134
135 lv_procedure_name VARCHAR2(30);
136 lv_ptu_calc_method FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
137 ln_days_in_year NUMBER;
138 ln_business_group_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
139 ln_legal_employer_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
140 ln_factor_B NUMBER;
141 ln_factor_C NUMBER;
142 ln_factor_D NUMBER;
143 ln_factor_E NUMBER;
144 ln_days_in_month_le NUMBER;
145 ln_days_in_year_le NUMBER;
146
147 BEGIN
148 lv_procedure_name := '.get_capped_average_earnings';
149 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
150
151 lv_ptu_calc_method := p_ptu_calc_method;
152 ln_days_in_year := p_days_in_year;
153 ln_business_group_id := p_business_group_id;
154 ln_legal_employer_id := p_legal_employer_id;
155 ln_factor_B := p_factor_B;
156 ln_factor_C := p_factor_C;
157 ln_factor_D := p_factor_D;
158 ln_factor_E := p_factor_E;
159
160 IF (lv_ptu_calc_method = 'DAILY_WAGE') THEN
161 /*
162 * Use employee's daily wage to calculate the
163 * portion of profit share based on wages.
164 */
165 IF (ln_factor_C / ln_factor_B) > ln_factor_D THEN
166
167 hr_utility.set_location(gv_package || lv_procedure_name,10);
168 ln_factor_E := ln_factor_D;
169
170 ELSE
171
172 hr_utility.set_location(gv_package || lv_procedure_name,20);
173 ln_factor_E := ROUND( ln_factor_C / ln_factor_B, 4 );
174
175 END IF;
176
177 ELSIF (lv_ptu_calc_method = 'ANNUAL_EARN') THEN
178
179 /* Use employee's annual earnings to calculate the
180 * distribution of profit. The highest union worker
181 * daily wage is converted into annual earnings for
182 * comparision. This is achieved by multiplying with
183 * number of days in the year specified at the
184 * Legal Employer level
185 */
186
187 hr_utility.set_location(gv_package || lv_procedure_name,30);
188 pay_mx_utility.get_no_of_days_for_org
189 (ln_business_group_id,
190 ln_legal_employer_id,
191 'LE',
192 ln_days_in_month_le,
193 ln_days_in_year_le) ;
194
195 IF (ln_days_in_year_le <> NULL) THEN
196 hr_utility.set_location(gv_package || lv_procedure_name,40);
197 ln_days_in_year := ln_days_in_year_le;
198 END IF;
199
200 ln_factor_D := ln_factor_D * ln_days_in_year;
201 hr_utility.trace('ln_factor_D = ' || to_char(ln_factor_D));
202
203 IF (ln_factor_C > ln_factor_D) THEN
204 hr_utility.set_location(gv_package || lv_procedure_name,50);
205 ln_factor_E := ln_factor_D;
206 ELSE
207 hr_utility.set_location(gv_package || lv_procedure_name,60);
208 ln_factor_E := ln_factor_C;
209 END IF;
210
211 ELSIF (lv_ptu_calc_method = 'PRORATE') THEN
212
213 /*
214 * The maximum salary cap is a function of the
215 * number of days the employee worked. The highest
216 * union worker salary is considered to be the
217 * limit for someone who has worked all year.
218 * For an employee, who has worked part of the year
219 * the limit should be adjusted to the amount that
220 * the highest paid employee would have made if he
221 * had worked the same number of days.
222 */
223 hr_utility.set_location(gv_package || lv_procedure_name,70);
224 ln_factor_D := ln_factor_D * ln_factor_B;
225 IF (ln_factor_C > ln_factor_D) THEN
226 hr_utility.set_location(gv_package || lv_procedure_name,80);
227 ln_factor_E := ln_factor_D;
228 ELSE
229 hr_utility.set_location(gv_package || lv_procedure_name,90);
230 ln_factor_E := ln_factor_C;
231 END IF;
232
233 END IF;
234
235 p_factor_D_used := ln_factor_D;
236 p_factor_E := ln_factor_E;
237 hr_utility.trace('p_factor_D = ' || TO_CHAR(p_factor_D));
238 hr_utility.trace('p_factor_E = ' || TO_CHAR(p_factor_E));
239 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
240 END get_capped_average_earnings;
241 /******************************************************************
242 Name : range_code
243 Purpose : This returns the select statement that is
244 used to create the range rows for the Profit Sharing
245 process.
246 Arguments :
247 Notes : Calls procedure - get_payroll_action_info
248 ******************************************************************/
249 PROCEDURE range_code(
250 p_payroll_action_id IN NUMBER
251 ,p_sqlstr OUT NOCOPY VARCHAR2)
252 IS
253
254 ld_end_date DATE;
255 ld_start_date DATE;
256 ld_date_earned DATE;
257 ln_business_group_id NUMBER;
258 ln_asg_set_id NUMBER;
259 ln_legal_employer_id NUMBER;
260 -- lv_incl_temp_EEs VARCHAR2(1);
261 -- ln_min_days_worked NUMBER;
262 lv_batch_name pay_batch_headers.batch_name%TYPE;
263
264 lv_sql_string VARCHAR2(32000);
265 lv_procedure_name VARCHAR2(100);
266
267 lv_error_message VARCHAR2(200);
268 ln_step NUMBER;
269
270
271 BEGIN
272 lv_procedure_name := '.range_code';
273
274 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
275 hr_utility.set_location(gv_package || lv_procedure_name, 10);
276
277 ln_step := 1;
278 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
279 ,p_start_date => ld_start_date
280 ,p_effective_date => ld_date_earned
281 ,p_business_group_id => ln_business_group_id
282 ,p_legal_employer_id => ln_legal_employer_id
283 ,p_asg_set_id => ln_asg_set_id
284 ,p_batch_name => lv_batch_name);
285
286 -- IF ln_min_days_worked > 0 THEN
287 -- NULL;
288 -- ELSE
289 -- ln_min_days_worked := 0;
290 -- END IF;
291
292 hr_utility.set_location(gv_package || lv_procedure_name, 20);
293
294 ld_end_date := add_months(ld_start_date, 12) - 1;
295
296 ln_step := 2;
297 pay_mx_yrend_arch.load_gre (ln_business_group_id,
298 ln_legal_employer_id,
299 ld_end_date);
300
301 ln_step := 3;
302
303 IF ln_asg_set_id IS NULL THEN
304
305 lv_sql_string :=
306 'SELECT DISTINCT paf.person_id
307 FROM pay_assignment_actions paa,
308 pay_payroll_actions ppa,
309 per_assignments_f paf
310 WHERE ppa.business_group_id = ' || ln_business_group_id || '
311 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
312 fnd_date.date_to_canonical(ld_start_date) || ''')
313 AND fnd_date.canonical_to_date(''' ||
314 fnd_date.date_to_canonical(ld_end_date) || ''')
315 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
316 AND ppa.payroll_action_id = paa.payroll_action_id
317 AND paa.source_action_id IS NULL
318 AND paf.assignment_id = paa.assignment_id
319 AND ppa.effective_date BETWEEN paf.effective_start_date
320 AND paf.effective_end_date
321 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
322 AND :payroll_action_id > 0
323 ORDER BY paf.person_id';
324 ELSE
325 lv_sql_string :=
326 'SELECT DISTINCT paf.person_id
327 FROM pay_assignment_actions paa,
328 pay_payroll_actions ppa,
329 per_assignments_f paf
330 WHERE ppa.business_group_id = ' || ln_business_group_id || '
331 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
332 fnd_date.date_to_canonical(ld_start_date) || ''')
333 AND fnd_date.canonical_to_date(''' ||
334 fnd_date.date_to_canonical(ld_end_date) || ''')
335 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
336 AND ppa.payroll_action_id = paa.payroll_action_id
337 AND paa.source_action_id IS NULL
338 AND paf.assignment_id = paa.assignment_id
339 AND ppa.effective_date BETWEEN paf.effective_start_date
340 AND paf.effective_end_date
341 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
342 AND (NOT EXISTS
343 (SELECT ''x''
344 FROM hr_assignment_set_amendments hasa
345 WHERE hasa.assignment_set_id = ' || ln_asg_set_id || '
346 AND hasa.include_or_exclude = ''I'')
347 OR EXISTS
348 (SELECT ''x''
349 FROM hr_assignment_sets has,
350 hr_assignment_set_amendments hasa,
351 per_assignments_f paf_all
352 WHERE has.assignment_set_id = ' || ln_asg_set_id || '
353 AND has.assignment_set_id = hasa.assignment_set_id
354 AND hasa.assignment_id = paf_all.assignment_id
355 AND paf_all.person_id = paf.person_id
356 AND hasa.include_or_exclude = ''I'')
357 )
358 AND NOT EXISTS
359 (SELECT ''x''
360 FROM hr_assignment_sets has,
361 hr_assignment_set_amendments hasa,
362 per_assignments_f paf_all
363 WHERE has.assignment_set_id = ' || ln_asg_set_id || '
364 AND has.assignment_set_id = hasa.assignment_set_id
365 AND hasa.assignment_id = paf_all.assignment_id
366 AND paf_all.person_id = paf.person_id
367 AND hasa.include_or_exclude = ''E'')
368 AND :payroll_action_id > 0
369 ORDER BY paf.person_id';
370
371 END IF; -- ln_asg_set_id is null
372
373 hr_utility.set_location(gv_package || lv_procedure_name, 30);
374 p_sqlstr := lv_sql_string;
375 hr_utility.trace ('SQL string :' ||p_sqlstr);
376 hr_utility.set_location(gv_package || lv_procedure_name, 50);
377 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
378
379 EXCEPTION
380
381 WHEN OTHERS THEN
382 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
383 gv_package || lv_procedure_name;
384
385 hr_utility.trace(lv_error_message || '-' || SQLERRM);
386
387 lv_error_message :=
388 pay_emp_action_arch.set_error_message(lv_error_message);
389
390 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
391 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
392 hr_utility.raise_error;
393
394
395 END range_code;
396
397 /************************************************************
398 Name : assignment_action_code
399 Purpose : This creates the assignment actions for
400 a specific chunk of people to be archived
401 by the Profit Sharing (PTU) process.
402 Arguments :
403 Notes : Calls procedure - get_payroll_action_info
404 ************************************************************/
405 PROCEDURE assignment_action_code(
406 p_payroll_action_id IN NUMBER
407 ,p_start_person_id IN NUMBER
408 ,p_end_person_id IN NUMBER
409 ,p_chunk IN NUMBER)
410 IS
411
412 CURSOR c_chk_asg (cp_asg_set_id NUMBER,
413 cp_person_id NUMBER) IS
414 SELECT 'X'
415 FROM dual
416 WHERE (EXISTS(SELECT 'x'
417 FROM hr_assignment_set_amendments hasa
418 WHERE hasa.assignment_set_id = cp_asg_set_id
419 AND hasa.include_or_exclude = 'I')
420 AND NOT EXISTS
421 (SELECT 'x'
422 FROM hr_assignment_sets has,
423 hr_assignment_set_amendments hasa,
424 per_assignments_f paf_all
425 WHERE has.assignment_set_id = cp_asg_set_id
426 AND has.assignment_set_id = hasa.assignment_set_id
427 AND hasa.assignment_id = paf_all.assignment_id
428 AND paf_all.person_id = cp_person_id
429 AND hasa.include_or_exclude = 'I')
430 )
431 OR EXISTS (SELECT 'x'
432 FROM hr_assignment_sets has,
433 hr_assignment_set_amendments hasa,
434 per_assignments_f paf_all
435 WHERE has.assignment_set_id = cp_asg_set_id
436 AND has.assignment_set_id = hasa.assignment_set_id
437 AND hasa.assignment_id = paf_all.assignment_id
438 AND paf_all.person_id = cp_person_id
439 AND hasa.include_or_exclude = 'E');
440
441 CURSOR c_get_emp_asg_range (cp_bg_id NUMBER,
442 cp_incl_temp_EEs VARCHAR2,
443 cp_start_date DATE,
444 cp_end_date DATE) IS
445 SELECT --DISTINCT
446 paf_pri.assignment_id,
447 paf_pri.person_id,
448 NVL(paf.employment_category, 'MX1_PERM_WRK'),
449 paa.tax_unit_id
450 FROM per_assignments_f paf,
451 per_assignments_f paf_pri,
452 pay_assignment_actions paa,
453 pay_payroll_actions ppa,
454 pay_population_ranges ppr
455 WHERE ppa.business_group_id = cp_bg_id
456 AND paf.assignment_id = paa.assignment_id
457 AND pay_mx_yrend_arch.gre_exists(paa.tax_unit_id) = 1
458 AND ppr.payroll_action_id = p_payroll_action_id
459 AND ppr.chunk_number = p_chunk
460 AND ppr.person_id = paf.person_id
461 AND paf.person_id = paf_pri.person_id
462 AND paf_pri.primary_flag = 'Y'
463 AND paa.payroll_action_id = ppa.payroll_action_id
464 AND ppa.action_type IN ('Q','R','B','V','I')
465 AND ppa.effective_date BETWEEN cp_start_date
466 AND cp_end_date
467 AND (paf.employment_category NOT IN ('MX2_TEMP_WRK',
468 'MX3_TEMP_CONSTRCT_WRK')
469 OR
470 cp_incl_temp_EEs = 'Y')
471 AND paf_pri.effective_start_date <= cp_end_date
472 AND paf_pri.effective_end_date >= cp_start_date
473 AND ppa.effective_date BETWEEN paf.effective_start_date
474 AND paf.effective_end_date
475 ORDER BY paf_pri.person_id,
476 NVL(paf.employment_category, 'MX1_PERM_WRK'),
477 paf_pri.effective_end_date DESC;
478
479 CURSOR c_get_emp_asg (cp_bg_id NUMBER,
480 cp_incl_temp_EEs VARCHAR2,
481 cp_start_date DATE,
482 cp_end_date DATE) IS
483 SELECT --DISTINCT
484 paf_pri.assignment_id,
485 paf_pri.person_id,
486 NVL(paf.employment_category, 'MX1_PERM_WRK'),
487 paa.tax_unit_id
488 FROM pay_assignment_actions paa,
489 pay_payroll_actions ppa,
490 per_assignments_f paf,
491 per_assignments_f paf_pri
492 WHERE ppa.business_group_id = cp_bg_id
493 AND ppa.effective_date BETWEEN cp_start_date
494 AND cp_end_date
495 AND ppa.action_type IN ('Q','R','B','V','I')
496 AND ppa.payroll_action_id = paa.payroll_action_id
497 AND paa.source_action_id IS NULL
498 AND paf.assignment_id = paa.assignment_id
499 AND paf_pri.person_id = paf.person_id
500 AND paf_pri.primary_flag = 'Y'
501 AND ppa.effective_date BETWEEN paf.effective_start_date
502 AND paf.effective_end_date
503 AND paf_pri.effective_start_date <= cp_end_date
504 AND paf_pri.effective_end_date >= cp_start_date
505 AND pay_mx_yrend_arch.gre_exists(paa.tax_unit_id) = 1
506 AND paf.person_id BETWEEN p_start_person_id
507 AND p_end_person_id
508 AND (paf.employment_category NOT IN ('MX2_TEMP_WRK',
509 'MX3_TEMP_CONSTRCT_WRK')
510 OR
511 cp_incl_temp_EEs = 'Y')
512 ORDER BY paf_pri.person_id,
513 NVL(paf.employment_category, 'MX1_PERM_WRK'),
514 paf_pri.effective_end_date DESC;
515
516 -- Check if Batch Name already exists in PAY_BATCH_HEADERS
517 CURSOR c_chk_batch_name_exists(cp_batch_name VARCHAR2,
518 cp_business_group_id NUMBER) IS
519 SELECT 'Y'
520 FROM pay_batch_headers
521 WHERE business_group_id = cp_business_group_id
522 AND UPPER(cp_batch_name) = UPPER(batch_name);
523
524 -- Check if assignment exists as on the Date Paid for Profit Sharing
525 CURSOR c_chk_asg_valid(cp_assignment_id NUMBER,
526 cp_effective_date DATE
527 ) IS
528 SELECT 'X'
529 FROM dual
530 WHERE NOT EXISTS(SELECT 'Y'
531 FROM per_assignments_f
532 WHERE assignment_id = cp_assignment_id
533 AND cp_effective_date BETWEEN effective_start_date
534 AND effective_end_date);
535
536 -- Get CURP for the person
537 CURSOR c_get_EE_no(cp_person_id NUMBER) IS
538 SELECT employee_number
539 FROM per_people_f
540 WHERE person_id = cp_person_id
541 ORDER BY effective_end_date DESC;
542
543 ln_assignment_id NUMBER;
544 ln_tax_unit_id NUMBER;
545
546 ld_end_date DATE;
547 ld_start_date DATE;
548 ld_date_earned DATE;
549 ln_business_group_id NUMBER;
550 ln_legal_employer_id NUMBER;
551 ln_asg_set_id NUMBER;
552 lv_incl_temp_EEs VARCHAR2(1);
553 ln_min_days_worked NUMBER;
554 lv_batch_name pay_batch_headers.batch_name%TYPE;
555
556 ln_PTU_action_id NUMBER;
557 ln_employment_category per_all_assignments_f.employment_category%TYPE;
558 ln_ytd_aaid NUMBER;
559 lv_batch_name_exists VARCHAR2(1);
560
561 lv_procedure_name VARCHAR2(100);
562 lv_error_message VARCHAR2(200);
563 ln_step NUMBER;
564 ln_ovn NUMBER;
565
566 lb_range_person BOOLEAN;
567 ln_person_id NUMBER;
568 ln_prev_person_id NUMBER;
569 lv_excl_flag VARCHAR2(2);
570 lv_run_exists VARCHAR2(2);
571
572 lb_action_created BOOLEAN;
573 lb_valid_pri_asg_found BOOLEAN;
574 ln_skipped_person_id NUMBER;
575 BATCH_EXISTS EXCEPTION;
576
577 /* Variables for Factors Calculation */
578
579 lv_legal_ER_name hr_all_organization_units_tl.name%TYPE;
580
581 ln_factor_A NUMBER;
582 ln_factor_B NUMBER;
583 ln_factor_C NUMBER;
584 ln_factor_D NUMBER;
585 ln_factor_D_used NUMBER;
586 ln_factor_E NUMBER;
587 ln_factor_F NUMBER;
588 ln_factor_G NUMBER;
589 ln_factor_H NUMBER;
590 ln_factor_I NUMBER;
591
592 ln_factor_F_found BOOLEAN;
593 ln_factor_G_found BOOLEAN;
594 ln_factor_F_total NUMBER;
595 ln_factor_G_total NUMBER;
596
597 ln_action_information_id NUMBER;
598 ln_object_version_number NUMBER;
599
600 lv_EE_no per_all_people_f.employee_number%TYPE;
601
602 BEGIN
603 lv_procedure_name := '.assignment_action_code';
604 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
605
606 ln_person_id := -1;
607 ln_prev_person_id := -1;
608 lv_excl_flag := '-1';
609 lb_action_created := FALSE;
610
611 hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
612 hr_utility.trace('p_start_person_id = '|| p_start_person_id);
613 hr_utility.trace('p_end_person_id = '|| p_end_person_id);
614 hr_utility.trace('p_chunk = '|| p_chunk);
615
616 ln_step := 1;
617 -- pay_emp_action_arch.gv_error_message := NULL;
618 hr_utility.set_location(gv_package || lv_procedure_name, 10);
619
620 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
621 ,p_start_date => ld_start_date
622 ,p_effective_date => ld_date_earned
623 ,p_business_group_id => ln_business_group_id
624 ,p_legal_employer_id => ln_legal_employer_id
625 ,p_asg_set_id => ln_asg_set_id
626 ,p_batch_name => lv_batch_name);
627
628 lv_legal_ER_name := hr_general.decode_organization(ln_legal_employer_id);
629
630 ld_end_date := ADD_MONTHS(ld_start_date, 12) - 1;
631
632 BEGIN
633 g_ptu_calc_method := NVL(hruserdt.get_table_value(ln_business_group_id,
634 'PTU Factors',
635 'Calculation Method',
636 lv_legal_ER_name,
637 ld_end_date),
638 'DAILY_WAGE');
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 g_ptu_calc_method := 'DAILY_WAGE';
642 END;
643
644 BEGIN
645 lv_incl_temp_EEs := NVL(hruserdt.get_table_value(ln_business_group_id,
646 'PTU Factors',
647 'Include Temporary Workers (Y/N)?',
648 lv_legal_ER_name,
649 ld_end_date), 'Y');
650 EXCEPTION
651 WHEN NO_DATA_FOUND THEN
652 lv_incl_temp_EEs := 'Y';
653 END;
654
655 BEGIN
656 ln_min_days_worked := NVL(TO_NUMBER(hruserdt.get_table_value(
657 ln_business_group_id,
658 'PTU Factors',
659 'Minimum Days Worked',
660 lv_legal_ER_name,
661 ld_end_date)), 0);
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 ln_min_days_worked := 0;
665 END;
666
667 ln_step := 2;
668 IF g_worked_days_def_bal_id IS NULL THEN
669
670 g_worked_days_def_bal_id := pay_ac_utility.get_defined_balance_id(
671 'Eligible Worked Days for Profit Sharing',
672 '_PER_YTD',
673 NULL,
674 'MX');
675 END IF;
676
677 g_elig_comp_def_bal_id := pay_ac_utility.get_defined_balance_id(
678 'Eligible Compensation for Profit Sharing',
679 '_PER_YTD',
680 NULL,
681 'MX');
682
683
684 IF ln_min_days_worked > 0 THEN
685 hr_utility.set_location(gv_package || lv_procedure_name, 20);
686
687 ELSE
688 hr_utility.set_location(gv_package || lv_procedure_name, 30);
689 ln_min_days_worked := 0;
690 END IF;
691
692 hr_utility.set_location(gv_package || lv_procedure_name, 40);
693
694 ln_step := 3;
695 IF pay_mx_yrend_arch.g_gre_tab.count() = 0 THEN
696
697 hr_utility.set_location(gv_package || lv_procedure_name, 50);
698
699 --------------------------------------------------------------
700 -- Load the cache with the GREs under the given Legal Employer
701 --------------------------------------------------------------
702 pay_mx_yrend_arch.load_gre (ln_business_group_id,
703 ln_legal_employer_id,
704 ld_end_date);
705 END IF;
706
707 BEGIN
708 ln_factor_F := hruserdt.get_table_value(ln_business_group_id,
709 'PTU Factors',
710 'Total Worked Days (Factor F)',
711 lv_legal_ER_name,
712 ld_end_date);
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 ln_factor_F := NULL;
716 END;
717
718 BEGIN
719 ln_factor_G := hruserdt.get_table_value(ln_business_group_id,
720 'PTU Factors',
721 'Total Capped Average Salary (Factor G)',
722 lv_legal_ER_name,
723 ld_end_date);
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 ln_factor_G := NULL;
727 END;
728
729 hr_utility.trace('Values from PTU Factors Table IN ACTION_CODE');
730 hr_utility.trace('Factor F: ' || ln_factor_F);
731 hr_utility.trace('Factor G: ' || ln_factor_G);
732
733 IF NVL(ln_factor_F, 0) = 0 THEN
734 ln_factor_F_found := FALSE;
735 ELSE
736 ln_factor_F_found := TRUE;
737 -- ln_factor_F_total := ROUND(ln_factor_F,4);
738 END IF;
739
740 IF NVL(ln_factor_G, 0) = 0 THEN
741 ln_factor_G_found := FALSE;
742 ELSE
743 ln_factor_G_found := TRUE;
744 -- ln_factor_G_total := ROUND(ln_factor_G,4);
745 END IF;
746
747 ln_step := 4;
748 lb_range_person := pay_ac_utility.range_person_on(
749 p_report_type => 'MX_PTU_CALC'
750 ,p_report_format => 'MX_PTU_CALC'
751 ,p_report_qualifier => 'MX'
752 ,p_report_category => 'ARCHIVE');
753
754 -- FOR cntr_gre IN
755 -- pay_mx_yrend_arch.g_gre_tab.first()..pay_mx_yrend_arch.g_gre_tab.last()
756 -- LOOP
757
758 IF lb_range_person THEN
759 hr_utility.set_location(gv_package || lv_procedure_name, 60);
760
761 OPEN c_get_emp_asg_range(ln_business_group_id,
762 lv_incl_temp_EEs,
763 ld_start_date,
764 ld_end_date);
765 ELSE
766 hr_utility.set_location(gv_package || lv_procedure_name, 70);
767
768 OPEN c_get_emp_asg (ln_business_group_id,
769 lv_incl_temp_EEs,
770 ld_start_date,
771 ld_end_date);
772 END IF;
773
774 LOOP
775 IF lb_range_person THEN
776 FETCH c_get_emp_asg_range INTO ln_assignment_id,
777 ln_person_id,
778 ln_employment_category,
779 ln_tax_unit_id;
780 EXIT WHEN c_get_emp_asg_range%NOTFOUND;
781 ELSE
782 FETCH c_get_emp_asg INTO ln_assignment_id,
783 ln_person_id,
784 ln_employment_category,
785 ln_tax_unit_id;
786 EXIT WHEN c_get_emp_asg%NOTFOUND;
787 END IF;
788
789 hr_utility.trace('Previous person ID = ' || ln_prev_person_id);
790 hr_utility.trace('Current person ID = ' || ln_person_id);
791 hr_utility.trace('Assignment ID= ' || ln_assignment_id);
792 hr_utility.trace('Employment Category= ' || ln_employment_category);
793
794 IF ln_person_id <> ln_prev_person_id THEN
795
796 hr_utility.set_location(gv_package || lv_procedure_name,80);
797 ln_step := 5;
798
799 OPEN c_get_ytd_aaid(ld_start_date,
800 ld_end_date,
801 ln_person_id);
802
803 FETCH c_get_ytd_aaid INTO ln_ytd_aaid;
804 CLOSE c_get_ytd_aaid;
805
806 IF ln_employment_category IN ('MX2_TEMP_WRK',
807 'MX3_TEMP_CONSTRCT_WRK') THEN
808
809 hr_utility.set_location(gv_package ||lv_procedure_name,90);
810
811 --------------------------------------------------------
812 -- Check if worked days exceed the minimum limit.
813 --------------------------------------------------------
814 ln_factor_B := NVL(pay_balance_pkg.get_value(
815 g_worked_days_def_bal_id,
816 ln_ytd_aaid), 0);
817
818 IF ln_min_days_worked > ln_factor_B THEN
819 ----------------------------------------------------
820 -- The temporary worker hasn't worked the stipulated
821 -- number of days during the Profit Sharing year
822 -- and is therefore ineligible for PTU.
823 ----------------------------------------------------
824 hr_utility.set_location(gv_package ||
825 lv_procedure_name, 95);
826 lv_excl_flag := 'X';
827
828 END IF;
829
830 END IF;
831
832 IF lv_excl_flag <> 'X' THEN
833
834 ------------------------------------------------------------
835 -- The person is eligible for PTU. Include the factors B and
836 -- C into the factor F and G running totals for this chunk.
837 ------------------------------------------------------------
838
839 IF ln_factor_G_found = FALSE OR ln_factor_F_found = FALSE THEN
840
841 hr_utility.set_location(gv_package ||
842 lv_procedure_name, 999);
843
844 ln_factor_B := NVL(pay_balance_pkg.get_value(
845 g_worked_days_def_bal_id,
846 ln_ytd_aaid), 0);
847
848 hr_utility.set_location(gv_package ||
849 lv_procedure_name, 998);
850
851 IF ( ln_factor_B <> 0 ) THEN
852
853 ln_factor_B := ln_factor_B;
854
855 ln_factor_C := NVL(pay_balance_pkg.get_value(
856 g_elig_comp_def_bal_id,
857 ln_ytd_aaid), 0);
858
859 ln_factor_C := ln_factor_C;
860
861 BEGIN
862
863 ln_factor_D := 1.2 *
864 hruserdt.get_table_value(
865 ln_business_group_id,
866 'PTU Factors',
867 'Highest Average Daily Salary',
868 lv_legal_ER_name,
869 ld_end_date) ;
870
871 EXCEPTION
872 WHEN NO_DATA_FOUND THEN
873 hr_utility.set_message(801,
874 'PAY_MX_PTU_INPUTS_MISSING');
875 hr_utility.raise_error;
876 END;
877
878 IF NVL(ln_factor_D, 0) = 0 THEN
879
880 hr_utility.set_message(801,
881 'PAY_MX_PTU_INPUTS_MISSING');
882 hr_utility.raise_error;
883
884 END IF;
885
886 get_capped_average_earnings(
887 p_ptu_calc_method =>g_ptu_calc_method,
888 p_days_in_year =>ld_end_date - ld_start_date,
889 p_business_group_id=>ln_business_group_id,
890 p_legal_employer_id=>ln_legal_employer_id,
891 p_factor_B =>ln_factor_B,
892 p_factor_C =>ln_factor_C,
893 p_factor_D =>ln_factor_D,
894 p_factor_D_used =>ln_factor_D_used,
895 p_factor_E =>ln_factor_E);
896
897 -- ln_factor_G_total := ROUND( nvl(ln_factor_G_total,0) +
898 -- ln_factor_E, 4);
899 ln_factor_G_total := NVL(ln_factor_G_total,0) +
900 ln_factor_E;
901
902 --IF ln_factor_F_found = FALSE THEN
903
904 hr_utility.set_location(gv_package ||
905 lv_procedure_name, 888);
906 -- ln_factor_F_total := ROUND( nvl(ln_factor_F_total,0) +
907 -- ln_factor_B, 4);
908
909 ln_factor_F_total := NVL(ln_factor_F_total,0) +
910 ln_factor_B;
911 --END IF;
912
913 END IF;
914
915 END IF;
916
917 IF ln_asg_set_id IS NOT NULL THEN
918
919 hr_utility.set_location(gv_package ||
920 lv_procedure_name, 100);
921 ------------------------------------------------------
922 -- Check if the assignment set excludes this person
923 ------------------------------------------------------
924 OPEN c_chk_asg (ln_asg_set_id, ln_person_id);
925 FETCH c_chk_asg INTO lv_excl_flag;
926 CLOSE c_chk_asg;
927
928 END IF;
929
930 ln_step := 6;
931
932 IF lv_excl_flag <> 'X' THEN
933
934 -----------------------------------------------------------
935 -- No assignment set exclusions apply.
936 -----------------------------------------------------------
937
938 hr_utility.set_location(gv_package ||
939 lv_procedure_name, 110);
940
941 OPEN c_chk_asg_valid(ln_assignment_id,
942 ld_date_earned);
943 FETCH c_chk_asg_valid INTO lv_excl_flag;
944 CLOSE c_chk_asg_valid;
945
946 IF lv_excl_flag <> 'X' THEN
947
948 hr_utility.set_location(gv_package ||lv_procedure_name,
949 120);
950 ln_prev_person_id := ln_person_id;
951
952 IF NOT lb_valid_pri_asg_found AND
953 ln_skipped_person_id <> ln_person_id THEN
954
955 ---------------------------------------------------
956 -- The Previous person doesn't have a single valid
957 -- primary assignment as on the Profit Sharing Date
958 -- Paid. Log a message for the same.
959 ---------------------------------------------------
960 hr_utility.set_location(gv_package ||
961 lv_procedure_name, 130);
962
963 OPEN c_get_EE_no(ln_skipped_person_id);
964 FETCH c_get_EE_no INTO lv_EE_no;
965 CLOSE c_get_EE_no;
966
967 pay_core_utils.push_message(801,
968 'PAY_MX_MISSING_ASG_FOR_PTU',
969 'P');
970 pay_core_utils.push_token('DETAILS',
971 'EE: ' || lv_EE_no);
972
973 END IF;
974
975 lb_valid_pri_asg_found := TRUE;
976
977 SELECT pay_assignment_actions_s.NEXTVAL
978 INTO ln_PTU_action_id
979 FROM dual;
980
981 hr_nonrun_asact.insact(ln_PTU_action_id,
982 ln_assignment_id,
983 p_payroll_action_id,
984 p_chunk,
985 ln_tax_unit_id,
986 NULL,
987 'U',
988 NULL,
989 ln_assignment_id,
990 'ASG');
991
992 lb_action_created := TRUE;
993 pay_mx_tax_functions.g_temp_object_actions := TRUE;
994
995 hr_utility.set_location(gv_package ||
996 lv_procedure_name, 140);
997
998 hr_utility.trace('PTU asg action ' ||
999 ln_PTU_action_id || ' created.');
1000
1001 ELSE
1002 -------------------------------------------------------
1003 -- The primary assignment is not valid on the Profit
1004 -- Sharing Date Paid. Set a flag to check if any other
1005 -- primary assignment exists on that date for that
1006 -- person.
1007 -------------------------------------------------------
1008 hr_utility.set_location(gv_package ||
1009 lv_procedure_name, 150);
1010 lb_valid_pri_asg_found := FALSE;
1011 ln_skipped_person_id := ln_person_id;
1012 lv_excl_flag := '-1';
1013
1014 END IF;
1015
1016 ELSE
1017 hr_utility.trace('Assignment is excluded in asg set.');
1018 ln_prev_person_id := ln_person_id;
1019 lv_excl_flag := '-1';
1020 END IF;
1021 ELSE
1022 hr_utility.trace('Temporary worker criterion not satisfied');
1023 lv_excl_flag := '-1';
1024 END IF;
1025 ELSE
1026 hr_utility.trace('The assignment action creation has been ' ||
1027 'either already done or skipped for this ' ||
1028 'person.');
1029 END IF;
1030 END LOOP;
1031
1032 IF lb_range_person THEN
1033 CLOSE c_get_emp_asg_range;
1034 ELSE
1035 CLOSE c_get_emp_asg;
1036 END IF;
1037
1038 IF NOT lb_valid_pri_asg_found THEN
1039
1040 ---------------------------------------------------------
1041 -- The Last person didn't have a single valid
1042 -- primary assignment as on the Profit Sharing Date
1043 -- Paid. Log a message for the same.
1044 ---------------------------------------------------------
1045 hr_utility.set_location(gv_package || lv_procedure_name, 160);
1046
1047 OPEN c_get_EE_no(ln_skipped_person_id);
1048 FETCH c_get_EE_no INTO lv_EE_no;
1049 CLOSE c_get_EE_no;
1050
1051 pay_core_utils.push_message(801, 'PAY_MX_MISSING_ASG_FOR_PTU', 'P');
1052 pay_core_utils.push_token('DETAILS', 'EE: ' || lv_EE_no);
1053
1054 END IF;
1055
1056 IF ln_factor_G_found = FALSE OR ln_factor_F_found = FALSE THEN
1057
1058
1059 -- ln_factor_F_total := ROUND( ln_factor_F_total, 4 );
1060 -- ln_factor_G_total := ROUND( ln_factor_G_total, 4 );
1061
1062 pay_action_information_api.create_action_information(
1063 p_action_information_id => ln_action_information_id
1064 ,p_object_version_number => ln_object_version_number
1065 ,p_action_information_category => 'MX PROFIT SHARING FACTORS'
1066 ,p_action_context_id => p_payroll_action_id
1067 ,p_action_context_type => 'PA'
1068 ,p_jurisdiction_code => NULL
1069 ,p_tax_unit_id => ln_legal_employer_id
1070 ,p_effective_date => ld_date_earned
1071 ,p_action_information1 => p_chunk
1072 ,p_action_information2 =>
1073 SUBSTR(TO_CHAR(ln_factor_F_total), 1, 240)
1074 ,p_action_information3 =>
1075 SUBSTR(TO_CHAR(ln_factor_G_total), 1, 240));
1076
1077 ln_factor_F_total := 0;
1078 ln_factor_G_total := 0;
1079
1080 END IF;
1081
1082 ln_step := 7;
1083 lv_batch_name_exists := 'N';
1084
1085 IF lb_action_created AND p_chunk = 1 THEN
1086
1087 OPEN c_chk_batch_name_exists(lv_batch_name,
1088 ln_business_group_id);
1089 FETCH c_chk_batch_name_exists INTO lv_batch_name_exists;
1090 CLOSE c_chk_batch_name_exists;
1091
1092 IF lv_batch_name_exists = 'Y' THEN
1093 raise BATCH_EXISTS;
1094 END IF;
1095
1096 pay_batch_element_entry_api.create_batch_header(
1097 p_session_date => ld_date_earned,
1098 p_batch_name => lv_batch_name,
1099 p_business_group_id => ln_business_group_id,
1100 p_batch_id => g_batch_id,
1101 p_object_version_number => ln_ovn);
1102
1103 END IF;
1104
1105 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1106
1107 EXCEPTION
1108 WHEN BATCH_EXISTS THEN
1109
1110 hr_utility.set_message(800, 'HR_BATCH_NAME_ALREADY_EXISTS');
1111 hr_utility.raise_error;
1112
1113 WHEN OTHERS THEN
1114
1115 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1116 gv_package || lv_procedure_name;
1117
1118 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1119
1120 lv_error_message :=
1121 pay_emp_action_arch.set_error_message(lv_error_message);
1122
1123 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1124 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1125 hr_utility.raise_error;
1126
1127 END assignment_action_code;
1128
1129 /************************************************************
1130 Name : initialization_code
1131 Purpose : This loads the values common to all assignments
1132 into a PL-SQL cache.
1133 Arguments :
1134 Notes :
1135 ************************************************************/
1136
1137 PROCEDURE initialization_code(p_payroll_action_id IN NUMBER) IS
1138 --
1139 lv_procedure_name VARCHAR2(100);
1140 lv_error_message VARCHAR2(200);
1141 ln_step NUMBER;
1142
1143 ld_end_date DATE;
1144 ld_start_date DATE;
1145 ld_date_earned DATE;
1146 ln_business_group_id NUMBER;
1147 ln_legal_employer_id NUMBER;
1148 ln_asg_set_id NUMBER;
1149 -- lv_incl_temp_EEs VARCHAR2(1);
1150 -- ln_min_days_worked NUMBER;
1151 lv_batch_name pay_batch_headers.batch_name%TYPE;
1152
1153 lv_legal_ER_name hr_all_organization_units_tl.name%TYPE;
1154
1155 ln_tot_share_amt NUMBER;
1156 ln_factor_A NUMBER;
1157 ln_factor_B NUMBER;
1158 ln_factor_C NUMBER;
1159 ln_factor_D NUMBER;
1160 ln_factor_E NUMBER;
1161 ln_factor_F NUMBER;
1162 ln_factor_G NUMBER;
1163 ln_factor_H NUMBER;
1164 ln_factor_I NUMBER;
1165
1166 -- Query to retrieve all the persons eligible for
1167 -- Profit Sharing under the given Legal Employer
1168 --
1169 CURSOR c_get_elig_persons
1170 IS
1171 SELECT DISTINCT paf.person_id
1172 FROM pay_temp_object_actions ptoa,
1173 per_assignments_f paf,
1174 pay_payroll_actions ppa
1175 WHERE ptoa.payroll_action_id = p_payroll_action_id
1176 AND paf.assignment_id = ptoa.object_id
1177 AND ptoa.object_type = 'ASG'
1178 AND ppa.payroll_action_id = ptoa.payroll_action_id
1179 AND ppa.effective_date BETWEEN paf.effective_start_date
1180 AND paf.effective_end_date
1181 ORDER BY paf.person_id;
1182
1183 -- Get element details for PTU element
1184 CURSOR c_get_PTU_ele_details
1185 IS
1186 SELECT element_type_id
1187 FROM pay_element_types_f
1188 WHERE element_name = 'Profit Sharing'
1189 AND legislation_code = 'MX';
1190
1191 -- Get Total of Factors F and G
1192
1193 CURSOR c_get_factors (cp_payroll_action_id NUMBER)
1194 IS
1195 SELECT NVL (SUM( NVL(pai.action_information2,0) ), 0) Factor_F,
1196 NVL (SUM( NVL(pai.action_information3,0) ), 0) Factor_G
1197 FROM pay_action_information pai
1198 WHERE pai.action_context_id = cp_payroll_action_id
1199 AND pai.action_context_type = 'PA'
1200 AND pai.action_information_category = 'MX PROFIT SHARING FACTORS';
1201
1202 -- Get Batch ID
1203
1204 CURSOR c_get_batch_id(cp_batch_name VARCHAR2,
1205 cp_business_group_id NUMBER) IS
1206
1207 SELECT batch_id
1208 FROM pay_batch_headers
1209 WHERE business_group_id = cp_business_group_id
1210 AND UPPER(cp_batch_name) = UPPER(batch_name);
1211
1212 BEGIN
1213 lv_procedure_name := '.initialization_code';
1214
1215 ln_step := 1;
1216
1217 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1218
1219 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1220 ,p_start_date => ld_start_date
1221 ,p_effective_date => ld_date_earned
1222 ,p_business_group_id => ln_business_group_id
1223 ,p_legal_employer_id => ln_legal_employer_id
1224 ,p_asg_set_id => ln_asg_set_id
1225 ,p_batch_name => lv_batch_name);
1226
1227 ld_end_date := ADD_MONTHS(ld_start_date, 12) - 1;
1228 gd_start_date := ld_start_date;
1229 gd_end_date := ld_end_date;
1230 gn_legal_employer_id:= ln_legal_employer_id;
1231
1232 OPEN c_get_batch_id( lv_batch_name
1233 ,ln_business_group_id);
1234 FETCH c_get_batch_id into g_batch_id;
1235 CLOSE c_get_batch_id;
1236
1237 IF pay_mx_yrend_arch.g_gre_tab.count() = 0 THEN
1238
1239 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1240
1241 --------------------------------------------------------------
1242 -- Load the cache with the GREs under the given Legal Employer
1243 --------------------------------------------------------------
1244 pay_mx_yrend_arch.load_gre (ln_business_group_id,
1245 ln_legal_employer_id,
1246 ld_end_date);
1247 END IF;
1248
1249 IF g_worked_days_def_bal_id IS NULL THEN
1250
1251 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1252
1253 g_worked_days_def_bal_id := pay_ac_utility.get_defined_balance_id(
1254 'Eligible Worked Days for Profit Sharing',
1255 '_PER_YTD',
1256 NULL,
1257 'MX');
1258 END IF;
1259
1260 g_elig_comp_def_bal_id := pay_ac_utility.get_defined_balance_id(
1261 'Eligible Compensation for Profit Sharing',
1262 '_PER_YTD',
1263 NULL,
1264 'MX');
1265
1266 lv_legal_ER_name := hr_general.decode_organization(ln_legal_employer_id);
1267
1268 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1269
1270 OPEN c_get_PTU_ele_details;
1271 FETCH c_get_PTU_ele_details INTO g_PTU_ele_type_id;
1272 CLOSE c_get_PTU_ele_details;
1273
1274 -- IF ln_min_days_worked > 0 THEN
1275 -- hr_utility.set_location(gv_package || lv_procedure_name, 50);
1276
1277 -- ELSE
1278 -- hr_utility.set_location(gv_package || lv_procedure_name, 60);
1279 -- ln_min_days_worked := 0;
1280 -- END IF;
1281
1282 ln_step := 2;
1283
1284 BEGIN
1285 ln_tot_share_amt := hruserdt.get_table_value(ln_business_group_id,
1286 'PTU Factors',
1287 'Total Amount to Share',
1288 lv_legal_ER_name,
1289 ld_end_date);
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 hr_utility.set_message(801,'PAY_MX_PTU_INPUTS_MISSING');
1293 hr_utility.raise_error;
1294 END;
1295
1296 ln_factor_A := ln_tot_share_amt / 2;
1297
1298 BEGIN
1299 ln_factor_D := 1.2 *
1300 hruserdt.get_table_value(ln_business_group_id,
1301 'PTU Factors',
1302 'Highest Average Daily Salary',
1303 lv_legal_ER_name,
1304 ld_end_date);
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 hr_utility.set_message(801,'PAY_MX_PTU_INPUTS_MISSING');
1308 hr_utility.raise_error;
1309 END;
1310
1311 BEGIN
1312 ln_factor_F := hruserdt.get_table_value(ln_business_group_id,
1313 'PTU Factors',
1314 'Total Worked Days (Factor F)',
1315 lv_legal_ER_name,
1316 ld_end_date);
1317 EXCEPTION
1318 WHEN NO_DATA_FOUND THEN
1319 ln_factor_F := NULL;
1320 END;
1321
1322 BEGIN
1323 ln_factor_G := hruserdt.get_table_value(ln_business_group_id,
1324 'PTU Factors',
1325 'Total Capped Average Salary (Factor G)',
1326 lv_legal_ER_name,
1327 ld_end_date);
1328 EXCEPTION
1329 WHEN NO_DATA_FOUND THEN
1330 ln_factor_G := NULL;
1331 END;
1332
1333 hr_utility.trace('Values from PTU Factors Table');
1334 hr_utility.trace('Factor A: ' || ln_factor_A);
1335 hr_utility.trace('Factor D: ' || ln_factor_D);
1336 hr_utility.trace('Factor F: ' || ln_factor_F);
1337 hr_utility.trace('Factor G: ' || ln_factor_G);
1338 ln_step := 3;
1339
1340 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1341
1342 IF NVL(ln_factor_A, 0) = 0 OR NVL(ln_factor_D, 0) = 0 THEN
1343 hr_utility.set_message(801,'PAY_MX_PTU_INPUTS_MISSING');
1344 hr_utility.raise_error;
1345 END IF;
1346
1347 --
1348 IF NVL(ln_factor_G, 0) = 0 OR NVL(ln_factor_F, 0) = 0 THEN
1349
1350 OPEN c_get_factors (p_payroll_action_id);
1351 FETCH c_get_factors INTO ln_factor_F, ln_factor_G;
1352 CLOSE c_get_factors;
1353
1354 ln_factor_F := ln_factor_F;
1355 ln_factor_G := ln_factor_G;
1356
1357 END IF;
1358
1359
1360 /** Below condition has been put to avoid divide by zero **/
1361
1362 IF ln_factor_F <> 0 THEN
1363
1364 ln_factor_H := ln_factor_A / ln_factor_F;
1365
1366 ELSE
1367
1368 ln_factor_H := 0;
1369
1370 END IF;
1371
1372 IF ln_factor_G <> 0 THEN
1373
1374 ln_factor_I := ln_factor_A / ln_factor_G;
1375
1376 ELSE
1377
1378 ln_factor_I := 0;
1379
1380 END IF;
1381
1382 BEGIN
1383 g_ptu_calc_method := NVL(hruserdt.get_table_value(ln_business_group_id,
1384 'PTU Factors',
1385 'Calculation Method',
1386 lv_legal_ER_name,
1387 ld_end_date),
1388 'DAILY_WAGE');
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391 g_ptu_calc_method := 'DAILY_WAGE';
1392 END;
1393
1394 g_factor_A := ln_tot_share_amt;
1395 g_factor_D := ln_factor_D;
1396 g_factor_F := ln_factor_F;
1397 g_factor_G := ln_factor_G;
1398 g_factor_H := ln_factor_H;
1399 g_factor_I := ln_factor_I;
1400
1401 hr_utility.trace('Global Values for PTU Factors');
1402 hr_utility.trace('g_factor_A: ' || g_factor_A);
1403 hr_utility.trace('g_factor_D: ' || g_factor_D);
1404 hr_utility.trace('g_factor_F: ' || g_factor_F);
1405 hr_utility.trace('g_factor_G: ' || g_factor_G);
1406 hr_utility.trace('g_factor_H: ' || g_factor_H);
1407 hr_utility.trace('g_factor_I: ' || g_factor_I);
1408
1409 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1414 gv_package || lv_procedure_name;
1415
1416 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1417
1418 lv_error_message :=
1419 pay_emp_action_arch.set_error_message(lv_error_message);
1420
1421 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1422 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1423 hr_utility.raise_error;
1424
1425 END initialization_code;
1426
1427 /************************************************************
1428 Name : archive_code
1429 Purpose : This procedure performs assignment specific
1430 profit share calculations for the Profit Sharing
1431 process in Mexico.
1432 Arguments : p_archive_action_id IN NUMBER
1433 p_effective_date IN DATE
1434 Notes :
1435 ************************************************************/
1436 PROCEDURE archive_code(p_archive_action_id IN NUMBER
1437 ,p_effective_date IN DATE)
1438 IS
1439 --
1440 lv_procedure_name VARCHAR2(100);
1441 lv_error_message VARCHAR2(200);
1442 ln_step NUMBER;
1443
1444 ln_batch_line_id NUMBER;
1445 ln_ovn NUMBER;
1446 ln_assignment_id NUMBER;
1447 lv_assignment_number per_assignments_f.assignment_number%TYPE;
1448 ln_person_id NUMBER;
1449
1450 ln_factor_B NUMBER;
1451 ln_factor_C NUMBER;
1452 ln_factor_D NUMBER;
1453 ln_factor_E NUMBER;
1454 ln_factor_J NUMBER;
1455 ln_factor_K NUMBER;
1456
1457 ln_isr_subject NUMBER;
1458 ln_isr_exempt NUMBER;
1459 ln_business_group_id NUMBER;
1460
1461 ln_payroll_action_id NUMBER;
1462 ln_ytd_asg_act_id NUMBER;
1463
1464 lv_lkup_meaning VARCHAR2(100);
1465
1466 ld_end_date DATE;
1467 ld_start_date DATE;
1468 ld_date_earned DATE;
1469 ln_legal_employer_id NUMBER;
1470 ln_asg_set_id NUMBER;
1471 lv_batch_name pay_batch_headers.batch_name%TYPE;
1472 lv_legal_ER_name hr_all_organization_units_tl.name%TYPE;
1473
1474 -- Get the person and assignment IDs
1475 CURSOR c_get_person
1476 IS
1477 SELECT paf.assignment_id,
1478 paf.assignment_number,
1479 paf.person_id,
1480 paf.business_group_id,
1481 ptoa.payroll_action_id
1482 FROM pay_temp_object_actions ptoa,
1483 per_assignments_f paf,
1484 pay_payroll_actions ppa
1485 WHERE ptoa.object_action_id = p_archive_action_id
1486 AND paf.assignment_id = ptoa.object_id
1487 AND ptoa.object_type = 'ASG'
1488 AND ppa.payroll_action_id = ptoa.payroll_action_id
1489 AND ppa.effective_date BETWEEN paf.effective_start_date
1490 AND paf.effective_end_date;
1491
1492 -- Get meaning for Yes as per language used
1493 -- This is used for input values Separate Payment
1494 -- and Process Separately
1495
1496 CURSOR c_get_lkup_meaning IS
1497 SELECT meaning
1498 FROM hr_lookups
1499 WHERE lookup_type = 'YES_NO'
1500 AND lookup_code = 'Y';
1501 BEGIN
1502 lv_procedure_name := '.archive_code';
1503 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1504
1505 OPEN c_get_person;
1506 FETCH c_get_person INTO ln_assignment_id,
1507 lv_assignment_number,
1508 ln_person_id,
1509 ln_business_group_id,
1510 ln_payroll_action_id;
1511 CLOSE c_get_person;
1512
1513 hr_utility.trace('Assignment ID: ' || ln_assignment_id);
1514 hr_utility.trace('Person ID: ' || ln_person_id);
1515
1516 hr_utility.trace('gd_start_date: '||gd_start_date);
1517 hr_utility.trace('gd_end_date: '||gd_end_date);
1518
1519 get_payroll_action_info(p_payroll_action_id => ln_payroll_action_id
1520 ,p_start_date => ld_start_date
1521 ,p_effective_date => ld_date_earned
1522 ,p_business_group_id => ln_business_group_id
1523 ,p_legal_employer_id => ln_legal_employer_id
1524 ,p_asg_set_id => ln_asg_set_id
1525 ,p_batch_name => lv_batch_name);
1526
1527 ld_end_date := ADD_MONTHS(ld_start_date, 12) - 1;
1528
1529 lv_legal_ER_name := hr_general.decode_organization(ln_legal_employer_id);
1530
1531 hr_utility.trace('ld_end_date: '||ld_end_date);
1532 hr_utility.trace('lv_legal_ER_name: '||lv_legal_ER_name);
1533
1534 BEGIN
1535 g_ptu_calc_method := NVL(hruserdt.get_table_value(ln_business_group_id,
1536 'PTU Factors',
1537 'Calculation Method',
1538 lv_legal_ER_name,
1539 ld_end_date),
1540 'DAILY_WAGE');
1541 EXCEPTION
1542 WHEN NO_DATA_FOUND THEN
1543 g_ptu_calc_method := 'DAILY_WAGE';
1544 END;
1545
1546 hr_utility.trace('g_ptu_calc_method: '||g_ptu_calc_method);
1547
1548 OPEN c_get_ytd_aaid(gd_start_date,
1549 gd_end_date,
1550 ln_person_id);
1551 FETCH c_get_ytd_aaid INTO ln_ytd_asg_act_id;
1552 CLOSE c_get_ytd_aaid;
1553
1554 hr_utility.trace('YTD AA ID: ' || ln_ytd_asg_act_id );
1555
1556 ln_step := 1;
1557 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1558
1559 ln_factor_B := NVL(pay_balance_pkg.get_value(
1560 g_worked_days_def_bal_id,
1561 ln_ytd_asg_act_id), 0);
1562
1563 IF ln_factor_B <> 0 THEN
1564
1565 ln_factor_J := ROUND( ln_factor_B * g_factor_H, 2 );
1566
1567 ln_step := 2;
1568 ln_factor_C := NVL(pay_balance_pkg.get_value(
1569 g_elig_comp_def_bal_id,
1570 ln_ytd_asg_act_id), 0);
1571
1572 get_capped_average_earnings(
1573 p_ptu_calc_method =>g_ptu_calc_method,
1574 p_days_in_year =>gd_end_date - gd_start_date,
1575 p_business_group_id=>ln_business_group_id,
1576 p_legal_employer_id=>gn_legal_employer_id,
1577 p_factor_B =>ln_factor_B,
1578 p_factor_C =>ln_factor_C,
1579 p_factor_D =>g_factor_D,
1580 p_factor_D_used =>ln_factor_D,
1581 p_factor_E =>ln_factor_E);
1582
1583 ln_factor_K := ROUND( ln_factor_E * g_factor_I, 2);
1584
1585 ELSE
1586 ln_factor_J := 0;
1587 ln_factor_K := 0;
1588 END IF;
1589
1590 IF (ln_factor_J + ln_factor_K) <> 0 THEN
1591
1592 ln_isr_subject := pay_mx_tax_functions.get_partial_subj_earnings(
1593 P_CTX_EFFECTIVE_DATE => p_effective_date,
1594 P_CTX_ASSIGNMENT_ACTION_ID => p_archive_action_id,
1595 P_CTX_BUSINESS_GROUP_ID => ln_business_group_id,
1596 P_CTX_JURISDICTION_CODE => 'XXX',
1597 P_CTX_ELEMENT_TYPE_ID => g_PTU_ele_type_id,
1598 P_TAX_TYPE => 'ISR',
1599 P_EARNINGS_AMT => ln_factor_J + ln_factor_K,
1600 P_YTD_EARNINGS_AMT => ln_factor_J + ln_factor_K,
1601 P_PTD_EARNINGS_AMT => ln_factor_J + ln_factor_K,
1602 P_GROSS_EARNINGS => -999,
1603 P_YTD_GROSS_EARNINGS => -999,
1604 P_DAILY_SALARY => ln_factor_E, -- Ignored
1605 P_CLASSIFICATION_NAME => 'Profit Sharing');
1606
1607 ln_isr_exempt := ln_factor_J + ln_factor_K - ln_isr_subject;
1608
1609
1610 OPEN c_get_lkup_meaning;
1611 FETCH c_get_lkup_meaning INTO lv_lkup_meaning;
1612 CLOSE c_get_lkup_meaning;
1613
1614 ln_step := 3;
1615 pay_batch_element_entry_api.create_batch_line (
1616 p_session_date => p_effective_date,
1617 p_batch_id => g_batch_id,
1618 p_assignment_id => ln_assignment_id,
1619 p_assignment_number => lv_assignment_number,
1620 -- p_batch_sequence => p_batch_sequence,
1621 p_effective_date => p_effective_date,
1622 -- p_element_name => p_element_name,
1623 p_element_type_id => g_PTU_ele_type_id,
1624 p_value_1 => ln_factor_J + ln_factor_K,
1625 p_value_2 => NULL,
1626 p_value_3 => lv_lkup_meaning,
1627 p_value_4 => lv_lkup_meaning,
1628 p_value_5 => g_factor_A,
1629 p_value_6 => ln_factor_B,
1630 p_value_7 => ln_factor_C,
1631 p_value_8 => g_factor_D,
1632 p_value_9 => ln_factor_E,
1633 p_value_10 => g_factor_F,
1634 p_value_11 => g_factor_G,
1635 p_value_12 => ln_factor_J,
1636 p_value_13 => ln_factor_K,
1637 p_value_14 => ln_isr_subject,
1638 p_value_15 => ln_isr_exempt,
1639 p_batch_line_id => ln_batch_line_id,
1640 p_object_version_number => ln_ovn);
1641
1642 END IF;
1643
1644 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1649 gv_package || lv_procedure_name;
1650
1651 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1652
1653 lv_error_message :=
1654 pay_emp_action_arch.set_error_message(lv_error_message);
1655
1656 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1657 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1658 hr_utility.raise_error;
1659
1660 END archive_code;
1661
1662 /************************************************************
1663 Name : deinit_code
1664 Purpose : This procedure deletes the temporary records
1665 created in PAY_ACTION_INFORMATION for the Profit
1666 Sharing process in Mexico.
1667 Arguments : p_payroll_action_id IN NUMBER
1668 Notes :
1669 ************************************************************/
1670 PROCEDURE deinit_code(p_payroll_action_id IN NUMBER)
1671 IS
1672 --
1673 lv_procedure_name VARCHAR2(100);
1674 lv_error_message VARCHAR2(200);
1675 ln_step NUMBER;
1676
1677 BEGIN
1678 lv_procedure_name := '.deinit_code';
1679 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1680
1681 ln_step := 1;
1682 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1683
1684 DELETE
1685 FROM pay_action_information
1686 WHERE action_information_category = 'MX PROFIT SHARING FACTORS'
1687 AND action_context_id = p_payroll_action_id
1688 AND action_context_type = 'PA';
1689
1690 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1695 gv_package || lv_procedure_name;
1696
1697 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1698
1699 lv_error_message :=
1700 pay_emp_action_arch.set_error_message(lv_error_message);
1701
1702 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1703 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1704 hr_utility.raise_error;
1705
1706 END deinit_code;
1707
1708 BEGIN
1709 --hr_utility.trace_on (NULL, 'MX_IDC');
1710 gv_package := 'pay_mx_PTU_calc';
1711 END pay_mx_PTU_calc;