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