[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_PAYROLL_ARCH
Source
1 PACKAGE BODY pay_mx_payroll_arch AS
2 /* $Header: paymxpaysliparch.pkb 120.2 2006/08/21 20:58:51 vpandya noship $ */
3 --
4
5 /******************************************************************************
6 ** Package Local Variables
7 ******************************************************************************/
8 gv_package VARCHAR2(100);
9 gn_gross_earn_def_bal_id NUMBER := 0;
10 -- gn_payments_def_bal_id NUMBER := 0;
11 gv_dim_asg_gre_ytd VARCHAR2(100);
12 gv_dim_asg_jd_gre_ytd VARCHAR2(100);
13 gv_ytd_balance_dimension VARCHAR2(80);
14
15 dbt DEF_BAL_TBL;
16 tax_calc_tbl DEF_BAL_TBL;
17
18 /******************************************************************************
19 Name : get_payroll_action_info
20 Purpose : This returns the Payroll Action level
21 information for Payslip Archiver.
22 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
23 p_start_date - Start date of Archiver
24 p_end_date - End date of Archiver
25 p_business_group_id - Business Group ID
26 p_cons_set_id - Consolidation Set when submitting Archiver
27 p_payroll_id - Payroll ID when submitting Archiver
28 ******************************************************************************/
29 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
30 ,p_end_date OUT NOCOPY DATE
31 ,p_start_date OUT NOCOPY DATE
32 ,p_business_group_id OUT NOCOPY NUMBER
33 ,P_CONS_SET_ID OUT NOCOPY NUMBER
34 ,p_payroll_id OUT NOCOPY NUMBER
35 )
36 IS
37 CURSOR c_payroll_Action_info
38 (cp_payroll_action_id IN NUMBER) IS
39 SELECT effective_date,
40 start_date,
41 business_group_id,
42 TO_NUMBER(SUBSTR(legislative_parameters,
43 INSTR(legislative_parameters,
44 'TRANSFER_CONSOLIDATION_SET_ID=')
45 + LENGTH('TRANSFER_CONSOLIDATION_SET_ID='))),
46 TO_NUMBER(LTRIM(RTRIM(SUBSTR(legislative_parameters,
47 INSTR(legislative_parameters,
48 'TRANSFER_PAYROLL_ID=')
49 + LENGTH('TRANSFER_PAYROLL_ID='),
50 (INSTR(legislative_parameters,
51 'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
52 - (INSTR(legislative_parameters,
53 'TRANSFER_PAYROLL_ID=')
54 + LENGTH('TRANSFER_PAYROLL_ID='))))))
55 FROM pay_payroll_actions
56 WHERE payroll_action_id = cp_payroll_action_id;
57
58 ld_end_date DATE;
59 ld_start_date DATE;
60 ln_business_group_id NUMBER;
61 ln_cons_set_id NUMBER;
62 ln_payroll_id NUMBER;
63 lv_procedure_name VARCHAR2(100);
64
65 lv_error_message VARCHAR2(200);
66 ln_step NUMBER;
67
68 BEGIN
69 lv_procedure_name := '.get_payroll_action_info';
70
71 hr_utility.set_location(gv_package || lv_procedure_name, 10);
72 ln_step := 1;
73 OPEN c_payroll_action_info(p_payroll_action_id);
74 FETCH c_payroll_action_info INTO ld_end_date,
75 ld_start_date,
76 ln_business_group_id,
77 ln_cons_set_id,
78 ln_payroll_id;
79 CLOSE c_payroll_action_info;
80
81 hr_utility.set_location(gv_package || lv_procedure_name, 30);
82 p_end_date := ld_end_date;
83 p_start_date := ld_start_date;
84 p_business_group_id := ln_business_group_id;
85 p_cons_set_id := ln_cons_set_id;
86 p_payroll_id := ln_payroll_id;
87 hr_utility.set_location(gv_package || lv_procedure_name, 50);
88 ln_step := 2;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
93 gv_package || lv_procedure_name;
94
95 hr_utility.trace(lv_error_message || '-' || SQLERRM);
96
97 lv_error_message :=
98 pay_emp_action_arch.set_error_message(lv_error_message);
99
100 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
101 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
102 hr_utility.raise_error;
103
104 END get_payroll_action_info;
105
106 /******************************************************************
107 Name : populate_elements
108 Purpose : This procedure archives details of a Primary Balance
109 Arguments : p_xfr_action_id - Assignment_Action_id of
110 archiver
111 p_pymt_assignment_
112 action_id - Assignment_Action_id used to
113 retrieve Current value
114 p_pymt_eff_date - Effective date of the Payment
115 p_primary_balance_id - Balance_type_ID of Pri Balance
116 p_hours_balance_id - Balance_type_ID of Hrs Balance
117 p_days_balance_id - Balance_type_ID of Days Balance
118 p_reporting_name - Reporting name of Pri Balance
119 p_attribute_name - Bal attribute of Pri Balance
120 p_tax_unit_id - Tax Unit ID context
121 p_ytd_balcall_aaid - Assignment_Action_id used to
122 fetch the YTD value
123 p_pymt_balcall_aaid - Assignment_Action_id used to
124 fetch the Current value
125 p_jurisdiction_code - Jurisdiction Code context
126 p_legislation_code - Legislation code
127 p_sepchk_flag - Separate Check flag
128 p_action_type - Action type of the action
129 being archived
130
131 Notes :
132 ******************************************************************/
133 PROCEDURE populate_elements(p_xfr_action_id IN NUMBER
134 ,p_pymt_assignment_action_id IN NUMBER
135 ,p_pymt_eff_date IN DATE
136 -- ,p_element_type_id IN NUMBER
137 ,p_primary_balance_id IN NUMBER
138 ,p_hours_balance_id IN NUMBER
139 ,p_days_balance_id IN NUMBER
140 -- ,p_processing_priority IN NUMBER
141 -- ,p_element_classification_name IN VARCHAR2
142 ,p_reporting_name IN VARCHAR2
143 ,p_attribute_name IN VARCHAR2
144 ,p_tax_unit_id IN NUMBER
145 ,p_ytd_balcall_aaid IN NUMBER
146 ,p_pymt_balcall_aaid IN NUMBER
147 ,p_jurisdiction_code IN VARCHAR2
148 DEFAULT NULL
149 ,p_legislation_code IN VARCHAR2
150 ,p_sepchk_flag IN VARCHAR2
151 ,p_action_type IN VARCHAR2
152 DEFAULT NULL
153 )
154 IS
155
156 CURSOR c_non_sep_check(cp_pymt_assignment_action_id IN NUMBER) IS
157 SELECT paa.assignment_action_id
158 FROM pay_action_interlocks pai,
159 pay_assignment_actions paa,
160 pay_payroll_actions ppa
161 WHERE pai.locking_action_id = cp_pymt_assignment_action_id
162 AND paa.assignment_action_id = pai.locked_action_id
163 AND paa.payroll_action_id = ppa.payroll_action_id
164 AND ppa.action_type IN ('Q','R')
165 AND ((NVL(paa.run_type_id, ppa.run_type_id) IS NULL AND
166 source_action_id IS NULL) OR
167 (NVL(paa.run_type_id, ppa.run_type_id) IS NOT NULL AND
168 source_action_id IS NOT NULL AND
169 paa.run_type_id NOT IN (gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)));
170
171
172 ln_current_hours NUMBER(15,2);
173 ln_current_days NUMBER(15,2);
174 ln_payments_amount NUMBER(15,2);
175 ln_ytd_hours NUMBER(15,2);
176 ln_ytd_days NUMBER(15,2);
177 ln_ytd_amount NUMBER(17,2);
178
179 ln_pymt_defined_balance_id NUMBER;
180 ln_pymt_hours_balance_id NUMBER;
181 ln_pymt_days_balance_id NUMBER;
182 ln_ytd_defined_balance_id NUMBER;
183 ln_ytd_hours_balance_id NUMBER;
184 ln_ytd_days_balance_id NUMBER;
185
186 lv_rate_exists VARCHAR2(1);
187 ln_nonpayroll_balcall_aaid NUMBER;
188
189 ln_index NUMBER ;
190 lv_action_category VARCHAR2(50);
191 lv_procedure_name VARCHAR2(100);
192 lv_error_message VARCHAR2(200);
193
194 ln_step NUMBER;
195
196 BEGIN
197 lv_rate_exists := 'N';
198 lv_action_category := 'AC DEDUCTIONS';
199 lv_procedure_name := '.populate_elements';
200
201 ln_step := 1;
202 hr_utility.set_location(gv_package || lv_procedure_name, 10);
203 hr_utility.trace('p_pymt_assignment_action_id '
204 ||to_char(p_pymt_assignment_action_id));
205 hr_utility.trace('p_pymt_eff_date '
206 ||to_char(p_pymt_eff_date));
207 hr_utility.trace('p_primary_balance_id '
208 ||to_char(p_primary_balance_id));
209 hr_utility.trace('p_reporting_name '
210 ||p_reporting_name);
211 hr_utility.trace('p_ytd_balcall_aaid '
212 ||to_char(p_ytd_balcall_aaid));
213 hr_utility.trace('p_pymt_balcall_aaid '
214 ||to_char(p_pymt_balcall_aaid));
215 hr_utility.trace('p_legislation_code '
216 ||p_legislation_code);
217 hr_utility.trace('p_hours_balance_id '
218 ||to_char(p_hours_balance_id));
219 hr_utility.trace('p_days_balance_id '
220 ||to_char(p_days_balance_id));
221
222 IF pay_emp_action_arch.gv_multi_leg_rule IS NULL THEN
223 pay_emp_action_arch.gv_multi_leg_rule
224 := pay_emp_action_arch.get_multi_legislative_rule(
225 p_legislation_code);
226 END IF;
227
228 ln_step := 2;
229 IF p_jurisdiction_code IS NOT NULL THEN
230 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
231 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
232 ELSE
233 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
234 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
235 END IF;
236
237
238 ln_step := 3;
239 /*********************************************************
240 ** Get the defined balance_id for YTD call as it will be
241 ** same for all classification types.
242 *********************************************************/
243 ln_ytd_defined_balance_id
244 := pay_emp_action_arch.get_defined_balance_id(
245 p_primary_balance_id,
246 gv_ytd_balance_dimension,
247 p_legislation_code);
248
249 hr_utility.trace('ln_ytd_defined_balance_id = ' ||
250 ln_ytd_defined_balance_id);
251
252 ln_step := 4;
253 IF p_hours_balance_id IS NOT NULL THEN
254 hr_utility.set_location(gv_package || lv_procedure_name, 20);
255 ln_ytd_hours_balance_id
256 := pay_emp_action_arch.get_defined_balance_id(
257 p_hours_balance_id,
258 gv_ytd_balance_dimension,
259 p_legislation_code);
260
261 hr_utility.trace('ln_ytd_hours_balance_id = ' ||
262 ln_ytd_hours_balance_id);
263
264 END IF;
265
266 IF p_days_balance_id IS NOT NULL THEN
267 hr_utility.set_location(gv_package || lv_procedure_name, 20);
268 ln_ytd_days_balance_id
269 := pay_emp_action_arch.get_defined_balance_id(
270 p_days_balance_id,
271 gv_ytd_balance_dimension,
272 p_legislation_code);
273
274 hr_utility.trace('ln_ytd_days_balance_id = ' ||
275 ln_ytd_days_balance_id);
276
277 END IF;
278
279 ln_step := 5;
280 hr_utility.set_location(gv_package || lv_procedure_name, 30);
281
282 ln_step := 6;
283 lv_rate_exists := 'N';
284
285 IF pay_ac_action_arch.lrr_act_tab.count <> 0 THEN
286 FOR i IN pay_ac_action_arch.lrr_act_tab.first..
287 pay_ac_action_arch.lrr_act_tab.last
288 LOOP
289 IF ( ( pay_ac_action_arch.lrr_act_tab(i).action_context_id =
290 p_xfr_action_id ) AND
291 ( pay_ac_action_arch.lrr_act_tab(i).act_info6 =
292 p_primary_balance_id ) )
293 THEN
294 lv_rate_exists := 'Y';
295 EXIT;
296 END IF;
297 END LOOP;
298 END IF;
299
300 hr_utility.trace('lv_rate_exists = ' || lv_rate_exists);
301
302 IF lv_rate_exists = 'N' THEN
303 ln_step := 7;
304 hr_utility.set_location(gv_package || lv_procedure_name, 40);
305 IF ln_ytd_defined_balance_id IS NOT NULL THEN
306 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
307 ln_ytd_defined_balance_id,
308 p_ytd_balcall_aaid),0);
309 END IF;
310
311 IF p_hours_balance_id IS NOT NULL THEN
312 hr_utility.set_location(gv_package || lv_procedure_name, 50);
313 IF ln_ytd_hours_balance_id IS NOT NULL THEN
314 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
315 ln_ytd_hours_balance_id,
316 p_ytd_balcall_aaid),0);
317 hr_utility.set_location(gv_package || lv_procedure_name, 60);
318 END IF;
319 END IF; --Hours
320
321 IF p_days_balance_id IS NOT NULL THEN
322 hr_utility.set_location(gv_package || lv_procedure_name, 50);
323 IF ln_ytd_days_balance_id IS NOT NULL THEN
324 ln_ytd_days := NVL(pay_balance_pkg.get_value(
325 ln_ytd_days_balance_id,
326 p_ytd_balcall_aaid),0);
327 hr_utility.set_location(gv_package || lv_procedure_name, 60);
328 END IF;
329 END IF; --Days
330
331 ln_step := 8;
332 IF p_pymt_balcall_aaid IS NOT NULL THEN
333 ln_step := 10;
334 IF p_action_type IN ('B','V') THEN
335 ln_pymt_defined_balance_id
336 := pay_emp_action_arch.get_defined_balance_id(
337 p_primary_balance_id,
338 '_ASG_GRE_RUN',
339 p_legislation_code);
340 ELSE
341 IF pay_emp_action_arch.gv_multi_leg_rule = 'Y' THEN
342 ln_pymt_defined_balance_id
343 := pay_emp_action_arch.get_defined_balance_id(
344 p_primary_balance_id,
345 '_ASG_PAYMENTS',
346 p_legislation_code);
347 ELSE
348 ln_pymt_defined_balance_id
349 := pay_emp_action_arch.get_defined_balance_id(
350 p_primary_balance_id,
351 '_PAYMENTS',
352 p_legislation_code);
353 END IF;
354 END IF; -- p_action_type IN ('B','V')
355 /* END of addition FOR Reversals AND bal adjustments */
356 hr_utility.trace('ln_pymt_defined_balance_id ' ||
357 ln_pymt_defined_balance_id);
358 IF ln_pymt_defined_balance_id IS NOT NULL THEN
359 ln_payments_amount := NVL(pay_balance_pkg.get_value(
360 ln_pymt_defined_balance_id,
361 p_pymt_balcall_aaid),0);
362 hr_utility.trace('ln_payments_amount = ' ||ln_payments_amount);
363 END IF;
364
365 IF p_hours_balance_id IS NOT NULL THEN
366 IF p_action_type IN ('B','V') THEN
367 ln_pymt_hours_balance_id
368 := pay_emp_action_arch.get_defined_balance_id(
369 p_hours_balance_id
370 ,'_ASG_GRE_RUN'
371 ,p_legislation_code);
372 ELSE
373 IF pay_emp_action_arch.gv_multi_leg_rule = 'Y' THEN
374 ln_pymt_hours_balance_id
375 := pay_emp_action_arch.get_defined_balance_id(
376 p_hours_balance_id
377 ,'_ASG_PAYMENTS'
378 ,p_legislation_code);
379 ELSE
380 ln_pymt_hours_balance_id
381 := pay_emp_action_arch.get_defined_balance_id(
382 p_hours_balance_id
383 ,'_PAYMENTS'
384 ,p_legislation_code);
385 END IF;
386 END IF; -- p_action_type IN ('B','V')
387
388 hr_utility.trace('ln_pymt_hours_balance_id ' ||
389 ln_pymt_hours_balance_id);
390 IF ln_pymt_hours_balance_id IS NOT NULL THEN
391 ln_current_hours := NVL(pay_balance_pkg.get_value(
392 ln_pymt_hours_balance_id,
393 p_pymt_balcall_aaid),0);
394 END IF;
395 hr_utility.set_location(gv_package || lv_procedure_name, 120);
396 END IF; --Hours
397
398 IF p_days_balance_id IS NOT NULL THEN
399 IF p_action_type IN ('B','V') THEN
400 ln_pymt_days_balance_id
401 := pay_emp_action_arch.get_defined_balance_id(
402 p_days_balance_id
403 ,'_ASG_GRE_RUN'
404 ,p_legislation_code);
405 ELSE
406 IF pay_emp_action_arch.gv_multi_leg_rule = 'Y' THEN
407 ln_pymt_days_balance_id
408 := pay_emp_action_arch.get_defined_balance_id(
409 p_days_balance_id
410 ,'_ASG_PAYMENTS'
411 ,p_legislation_code);
412 ELSE
413 ln_pymt_days_balance_id
414 := pay_emp_action_arch.get_defined_balance_id(
415 p_days_balance_id
416 ,'_PAYMENTS'
417 ,p_legislation_code);
418 END IF;
419 END IF; -- p_action_type in ('B','V')
420
421 hr_utility.trace('ln_pymt_days_balance_id ' ||
422 ln_pymt_days_balance_id);
423 IF ln_pymt_days_balance_id IS NOT NULL THEN
424 ln_current_days := NVL(pay_balance_pkg.get_value(
425 ln_pymt_days_balance_id,
426 p_pymt_balcall_aaid),0);
427 END IF;
428 hr_utility.set_location(gv_package || lv_procedure_name, 120);
429 END IF; --Days
430
431 END IF; -- p_pymt_balcall_aaid is not NULL
432
433 ln_step := 15;
434 IF NVL(ln_ytd_amount, 0) <> 0 OR NVL(ln_payments_amount, 0) <> 0 THEN
435 ln_index := pay_ac_action_arch.lrr_act_tab.count;
436
437 IF p_attribute_name IN ('Employee Earnings', 'Hourly Earnings',
438 'Taxable Benefits') THEN
439
440 hr_utility.set_location(gv_package || lv_procedure_name, 125);
441 lv_action_category := 'AC EARNINGS';
442 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
443 := fnd_number.number_to_canonical(ln_current_hours);
444 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
445 := fnd_number.number_to_canonical(ln_ytd_hours);
446
447 pay_ac_action_arch.lrr_act_tab(ln_index).act_info14
448 := fnd_number.number_to_canonical(ln_current_days);
449 pay_ac_action_arch.lrr_act_tab(ln_index).act_info15
450 := fnd_number.number_to_canonical(ln_ytd_days);
451
452 END IF;
453
454 hr_utility.set_location(gv_package || lv_procedure_name, 130);
455 /* Insert this into the plsql table if Current or YTD
456 amount is not Zero */
457 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
458 := lv_action_category;
459 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
460 := p_jurisdiction_code;
461 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
462 := p_xfr_action_id;
463 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
464 := p_primary_balance_id;
465 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
466 := fnd_number.number_to_canonical(ln_payments_amount);
467 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
468 := fnd_number.number_to_canonical(ln_ytd_amount);
469 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
470 := p_reporting_name;
471 pay_ac_action_arch.lrr_act_tab(ln_index).act_info16
472 := p_attribute_name;
473
474 END IF;
475 END IF; -- lv_rate_exists = 'N'
476
477 hr_utility.set_location(gv_package || lv_procedure_name, 150);
478 ln_step := 20;
479
480 EXCEPTION
481 WHEN OTHERS THEN
482 hr_utility.set_location(gv_package || lv_procedure_name, 200);
483 lv_error_message := 'Error at step ' || ln_step ||
484 ' IN ' || gv_package || lv_procedure_name;
485
486 hr_utility.trace(lv_error_message || '-' || SQLERRM);
487
488 lv_error_message :=
489 pay_emp_action_arch.set_error_message(lv_error_message);
490
491 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
492 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
493 hr_utility.raise_error;
494
495 END populate_elements;
496
497
498 /******************************************************************
499 Name : get_missing_xfr_info
500 Purpose : The procedure gets the elements which have been
501 processed for a given Payment Action. This procedure
502 is only called if the archiver has not been run for
503 all pre-payment actions.
504 Arguments :
505 Notes :
506 ******************************************************************/
507 PROCEDURE get_missing_xfr_info(p_xfr_action_id IN NUMBER
508 ,p_tax_unit_id IN NUMBER
509 ,p_assignment_id IN NUMBER
510 ,p_last_pymt_action_id IN NUMBER
511 ,p_last_pymt_eff_date IN DATE
512 ,p_last_xfr_eff_date IN DATE
513 ,p_ytd_balcall_aaid IN NUMBER
514 ,p_pymt_eff_date IN DATE
515 ,p_legislation_code IN VARCHAR2
516 )
517
518 IS
519
520 CURSOR c_prev_elements(cp_assignment_id IN NUMBER
521 ,cp_last_pymt_eff_date IN DATE
522 ,cp_last_xfr_eff_date IN DATE) IS
523 SELECT DISTINCT
524 NVL(pbtl.reporting_name, pbtl.balance_name),
525 pbad.attribute_name,
526 DECODE(pbad.attribute_name,
527 'Employee Taxes', prb.jurisdiction_code),
528 pbt_pri.balance_type_id, -- Primary Balance
529 DECODE(pbad.attribute_name,
530 'Hourly Earnings', pbt_sec.balance_type_id,
531 NULL), -- Hours Balance
532 DECODE(pbad.attribute_name,
533 'Employee Earnings', pbt_sec.balance_type_id,
534 NULL) -- Days Balance
535 FROM pay_bal_attribute_definitions pbad,
536 pay_balance_attributes pba,
537 pay_defined_balances pdb,
538 pay_run_balances prb,
539 pay_action_interlocks pai,
540 pay_assignment_actions paa_pre,
541 pay_payroll_actions ppa_pre,
542 pay_balance_types pbt_pri,
543 pay_balance_types pbt_sec,
544 pay_balance_types_tl pbtl
545 WHERE ppa_pre.action_type IN ('U', 'P')
546 AND ppa_pre.effective_date > cp_last_xfr_eff_date
547 AND ppa_pre.effective_date <= cp_last_pymt_eff_date
548 AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
549 AND paa_pre.assignment_id = cp_assignment_id
550 AND pai.locking_action_id = paa_pre.assignment_action_id
551 AND prb.assignment_action_id = pai.locked_action_id
552 AND pbad.attribute_name IN ('Employee Earnings',
553 'Hourly Earnings',
554 'Deductions',
555 'Taxable Benefits'
556 -- 'Employee Taxes',
557 -- 'Tax Calculation Details'
558 )
559 AND pbad.legislation_code = 'MX'
560 AND pba.attribute_id = pbad.attribute_id
561 AND pdb.defined_balance_id = pba.defined_balance_id
562 AND prb.defined_balance_id = pdb.defined_balance_id
563 AND pbt_pri.balance_type_id = pdb.balance_type_id
564 AND pbt_pri.input_value_id IS NOT NULL
565 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
566 AND pbtl.balance_type_id = pbt_pri.balance_type_id
567 AND pbtl.language = USERENV('LANG')
568 ORDER BY 1;
569
570 CURSOR c_prev_elements_RR(cp_assignment_id IN NUMBER
571 ,cp_last_pymt_eff_date IN DATE
572 ,cp_last_xfr_eff_date IN DATE) IS
573 SELECT DISTINCT
574 NVL(pbtl.reporting_name, pbtl.balance_name),
575 pbad.attribute_name,
576 DECODE(pbad.attribute_name,
577 'Employee Taxes', prr.jurisdiction_code),
578 pbt_pri.balance_type_id, -- Primary Balance
579 DECODE(pbad.attribute_name,
580 'Hourly Earnings', pbt_sec.balance_type_id,
581 NULL), -- Hours Balance
582 DECODE(pbad.attribute_name,
583 'Employee Earnings', pbt_sec.balance_type_id,
584 NULL) -- Days Balance
585 FROM pay_bal_attribute_definitions pbad,
586 pay_balance_attributes pba,
587 pay_defined_balances pdb,
588 pay_run_results prr,
589 pay_input_values_f piv,
590 pay_action_interlocks pai,
591 pay_assignment_actions paa_pre,
592 pay_payroll_actions ppa_pre,
593 pay_balance_types pbt_pri,
594 pay_balance_types pbt_sec,
595 pay_balance_types_tl pbtl
596 WHERE ppa_pre.action_type IN ('U', 'P')
597 AND ppa_pre.effective_date > cp_last_xfr_eff_date
598 AND ppa_pre.effective_date <= cp_last_pymt_eff_date
599 AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
600 AND paa_pre.assignment_id = cp_assignment_id
601 AND pai.locking_action_id = paa_pre.assignment_action_id
602 AND prr.assignment_action_id = pai.locked_action_id
603 AND pbad.attribute_name IN ('Employee Earnings',
604 'Hourly Earnings',
605 'Deductions',
606 'Taxable Benefits'
607 -- 'Employee Taxes',
608 -- 'Tax Calculation Details'
609 )
610 AND pbad.legislation_code = 'MX'
611 AND pba.attribute_id = pbad.attribute_id
612 AND pdb.defined_balance_id = pba.defined_balance_id
613 AND pbt_pri.balance_type_id = pdb.balance_type_id
614 AND pbt_pri.input_value_id = piv.input_value_id
615 AND piv.element_type_id = prr.element_type_id
616 AND ppa_pre.effective_date BETWEEN piv.effective_start_date
617 AND piv.effective_end_date
618 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
619 AND pbtl.balance_type_id = pbt_pri.balance_type_id
620 AND pbtl.language = USERENV('LANG')
621 ORDER BY 1;
622
623 CURSOR c_business_grp_id IS
624 SELECT DISTINCT business_group_id
625 FROM per_assignments_f
626 WHERE assignment_id = p_assignment_id;
627
628
629 ln_primary_balance_id NUMBER;
630 lv_reporting_name VARCHAR2(80);
631 lv_attribute_name VARCHAR2(80);
632 lv_jurisdiction_code VARCHAR2(80);
633 ln_hours_balance_id NUMBER;
634 ln_days_balance_id NUMBER;
635
636 ln_ytd_hours_balance_id NUMBER;
637 ln_ytd_days_balance_id NUMBER;
638 ln_ytd_defined_balance_id NUMBER;
639 ln_payments_amount NUMBER;
640 ln_ytd_hours NUMBER;
641 ln_ytd_days NUMBER;
642 ln_ytd_amount NUMBER(17,2);
643 lv_action_info_category VARCHAR2(30);
644
645 ln_index NUMBER ;
646 lv_element_archived VARCHAR2(1);
647 lv_procedure_name VARCHAR2(100);
648 lv_error_message VARCHAR2(200);
649 ln_step NUMBER;
650
651 st_cnt NUMBER;
652 end_cnt NUMBER;
653 lv_business_grp_id NUMBER;
654 lv_run_bal_status VARCHAR2(1);
655
656 BEGIN
657 lv_action_info_category := 'AC DEDUCTIONS';
658 lv_element_archived := 'N';
659 lv_procedure_name := '.get_missing_xfr_info';
660
661 ln_step := 1;
662 hr_utility.set_location(gv_package || lv_procedure_name, 10);
663 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
664 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
665 hr_utility.trace('p_last_pymt_action_id ='|| p_last_pymt_action_id );
666 hr_utility.trace('p_last_pymt_eff_date=' || p_last_pymt_eff_date);
667
668 lv_run_bal_status := NULL;
669
670 IF run_bal_stat.COUNT >0 THEN
671 st_cnt := run_bal_stat.FIRST;
672 end_cnt := run_bal_stat.LAST;
673 FOR i IN st_cnt..end_cnt LOOP
674 IF run_bal_stat(i).valid_status = 'N' THEN
675 lv_run_bal_status := 'N';
676 EXIT;
677 END IF;
678 END LOOP;
679 ELSE
680 OPEN c_business_grp_id;
681 FETCH c_business_grp_id INTO lv_business_grp_id;
682 CLOSE c_business_grp_id;
683
684 run_bal_stat(1).attribute_name := 'Employee Earnings';
685 run_bal_stat(2).attribute_name := 'Hourly Earnings';
686 run_bal_stat(3).attribute_name := 'Deductions';
687 run_bal_stat(4).attribute_name := 'Employee Taxes';
688 run_bal_stat(5).attribute_name := 'Tax Calculation Details';
689 run_bal_stat(6).attribute_name := 'Taxable Benefits';
690
691 st_cnt := run_bal_stat.FIRST;
692 end_cnt := run_bal_stat.LAST;
693
694 FOR i IN st_cnt..end_cnt LOOP
695 run_bal_stat(i).valid_status := pay_us_payroll_utils.check_balance_status(
696 p_pymt_eff_date,
697 lv_business_grp_id,
698 run_bal_stat(i).attribute_name,
699 p_legislation_code);
700 IF (lv_run_bal_status IS NULL AND run_bal_stat(i).valid_status = 'N') THEN
701 lv_run_bal_status := 'N';
702 END IF;
703 END LOOP;
704 END IF;
705
706 IF lv_run_bal_status IS NULL THEN
707 lv_run_bal_status := 'Y';
708 END IF;
709
710 IF lv_run_bal_status = 'N' THEN
711
712 OPEN c_prev_elements_RR(p_assignment_id,
713 p_last_pymt_eff_date,
714 p_last_xfr_eff_date);
715
716 ELSE
717 OPEN c_prev_elements(p_assignment_id,
718 p_last_pymt_eff_date,
719 p_last_xfr_eff_date);
720
721 END IF;
722
723 LOOP
724
725 IF lv_run_bal_status = 'N' THEN
726
727 FETCH c_prev_elements_RR INTO lv_reporting_name,
728 lv_attribute_name,
729 lv_jurisdiction_code,
730 ln_primary_balance_id,
731 ln_hours_balance_id,
732 ln_days_balance_id;
733 IF c_prev_elements_RR%NOTFOUND THEN
734 hr_utility.set_location(gv_package || lv_procedure_name, 15);
735 EXIT;
736 END IF;
737 hr_utility.set_location(gv_package || lv_procedure_name, 20);
738
739 ELSE
740
741 FETCH c_prev_elements INTO lv_reporting_name,
742 lv_attribute_name,
743 lv_jurisdiction_code,
744 ln_primary_balance_id,
745 ln_hours_balance_id,
746 ln_days_balance_id;
747 IF c_prev_elements%NOTFOUND THEN
748 hr_utility.set_location(gv_package || lv_procedure_name, 25);
749 EXIT;
750 END IF;
751 hr_utility.set_location(gv_package || lv_procedure_name, 30);
752
753 END IF;
754
755 IF lv_attribute_name IN ('Deductions', 'Employee Taxes',
756 'Tax Calculation Details') THEN
757 ln_hours_balance_id := NULL;
758 ln_days_balance_id := NULL;
759 END IF;
760
761 ln_step := 5;
762 IF pay_ac_action_arch.emp_elements_tab.count > 0 THEN
763 FOR i IN pay_ac_action_arch.emp_elements_tab.first..
764 pay_ac_action_arch.emp_elements_tab.last LOOP
765 IF pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
766 = ln_primary_balance_id AND
767 NVL(pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code,
768 -999) = NVL(lv_jurisdiction_code, -999) THEN
769 lv_element_archived := 'Y';
770 EXIT;
771 END IF;
772 END LOOP;
773 END IF;
774
775 IF lv_element_archived = 'N' THEN
776 /* populate the extra element table */
777 ln_step := 10;
778 ln_index := pay_ac_action_arch.emp_elements_tab.count;
779 pay_ac_action_arch.emp_elements_tab(ln_index).element_primary_balance_id
780 := ln_primary_balance_id;
781 pay_ac_action_arch.emp_elements_tab(ln_index).element_reporting_name
782 := lv_reporting_name;
783 pay_ac_action_arch.emp_elements_tab(ln_index).element_hours_balance_id
784 := ln_hours_balance_id;
785 pay_ac_action_arch.emp_elements_tab(ln_index).jurisdiction_code
786 := lv_jurisdiction_code;
787
788 IF lv_jurisdiction_code IS NOT NULL THEN
789 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
790 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
791 ELSE
792 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
793 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
794 END IF;
795
796 ln_step := 15;
797 ln_ytd_defined_balance_id :=
798 pay_emp_action_arch.get_defined_balance_id
799 (ln_primary_balance_id,
800 gv_ytd_balance_dimension,
801 p_legislation_code);
802 hr_utility.set_location(gv_package || lv_procedure_name, 60);
803 IF ln_ytd_defined_balance_id IS NOT NULL THEN
804 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
805 ln_ytd_defined_balance_id,
806 p_ytd_balcall_aaid),0);
807 hr_utility.set_location(gv_package || lv_procedure_name, 70);
808 END IF;
809 IF ln_hours_balance_id IS NOT NULL THEN
810 ln_ytd_hours_balance_id :=
811 pay_emp_action_arch.get_defined_balance_id
812 (ln_hours_balance_id,
813 gv_ytd_balance_dimension,
814 p_legislation_code);
815 hr_utility.set_location(gv_package || lv_procedure_name, 80);
816 IF ln_ytd_hours_balance_id IS NOT NULL THEN
817 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
818 ln_ytd_hours_balance_id,
819 p_ytd_balcall_aaid),0);
820 hr_utility.set_location(gv_package || lv_procedure_name, 90);
821 END IF;
822 END IF; -- Hours
823
824 IF ln_days_balance_id IS NOT NULL THEN
825 ln_ytd_days_balance_id :=
826 pay_emp_action_arch.get_defined_balance_id
827 (ln_days_balance_id,
828 gv_ytd_balance_dimension,
829 p_legislation_code);
830 hr_utility.set_location(gv_package || lv_procedure_name, 80);
831 IF ln_ytd_days_balance_id IS NOT NULL THEN
832 ln_ytd_days := NVL(pay_balance_pkg.get_value(
833 ln_ytd_days_balance_id,
834 p_ytd_balcall_aaid),0);
835 hr_utility.set_location(gv_package || lv_procedure_name, 90);
836 END IF;
837 END IF; -- Days
838
839 hr_utility.set_location(gv_package || lv_procedure_name, 100);
840
841 IF NVL(ln_ytd_amount, 0) <> 0 OR NVL(ln_payments_amount, 0) <> 0 THEN
842
843 ln_index := pay_ac_action_arch.lrr_act_tab.count;
844 hr_utility.trace('ln_index = ' || ln_index);
845
846 IF lv_attribute_name IN ('Employee Earnings',
847 'Hourly Earnings',
848 'Taxable Benefits') THEN
849
850 lv_action_info_category := 'AC EARNINGS';
851 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
852 := fnd_number.number_to_canonical(ln_ytd_hours);
853 pay_ac_action_arch.lrr_act_tab(ln_index).act_info15
854 := fnd_number.number_to_canonical(ln_ytd_days);
855
856 END IF;
857
858 ln_step := 20;
859 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
860 := lv_action_info_category;
861 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
862 := lv_jurisdiction_code;
863 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
864 := p_xfr_action_id ;
865 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
866 := ln_primary_balance_id;
867 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
868 := fnd_number.number_to_canonical(ln_payments_amount);
869 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
870 := fnd_number.number_to_canonical(ln_ytd_amount);
871 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
872 := lv_reporting_name;
873 pay_ac_action_arch.lrr_act_tab(ln_index).act_info16
874 := lv_attribute_name;
875
876 END IF;
877 END IF;
878 lv_element_archived := 'N';
879 lv_action_info_category := 'AC DEDUCTIONS';
880 lv_jurisdiction_code := NULL;
881 ln_primary_balance_id := NULL;
882 lv_reporting_name := NULL;
883 ln_hours_balance_id := NULL;
884 END LOOP;
885
886 IF lv_run_bal_status = 'N' THEN
887 CLOSE c_prev_elements_RR;
888 ELSE
889 CLOSE c_prev_elements;
890 END IF;
891
892 hr_utility.set_location(gv_package || lv_procedure_name, 150);
893
894 ln_step := 30;
895
896
897 EXCEPTION
898 WHEN OTHERS THEN
899
900 lv_error_message := 'Error at step ' || ln_step ||
901 ' IN ' || gv_package || lv_procedure_name;
902
903 hr_utility.trace(lv_error_message || '-' || SQLERRM);
904
905 lv_error_message :=
906 pay_emp_action_arch.set_error_message(lv_error_message);
907
908 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
909 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
910 hr_utility.raise_error;
911
912 END get_missing_xfr_info;
913
914
915
916 /******************************************************************
917 Name : get_xfr_elements
918 Purpose : Check the elements archived in the previous record with
919 the given assignment and if the element is not archived
920 in this current run, get YTD for the element found.
921 Arguments : p_xfr_action_id => Current xfr action id
922 p_last_xfr_action_id => Previous xfr action id retrieved
923 from get_last_xfr_info procedure
924 p_ytd_balcall_aaid => aaid for YTD balance call.
925 p_pymt_eff_date => Current pymt eff date.
926 p_legislation_code => Legislation code.
927 p_sepchk_flag => Separate Check flag.
928 p_assignment_id => Current assignment id that IS being
929 processed.
930 Notes : If multi assignment is enabled and is a sepchk, then check
931 the last xfr run for the given person not assignment.
932 ******************************************************************/
933 PROCEDURE get_xfr_elements(p_xfr_action_id IN NUMBER
934 ,p_last_xfr_action_id IN NUMBER
935 ,p_ytd_balcall_aaid IN NUMBER
936 ,p_pymt_eff_date IN DATE
937 ,p_legislation_code IN VARCHAR2
938 ,p_sepchk_flag IN VARCHAR2
939 ,p_assignment_id IN NUMBER
940 )
941
942 IS
943 CURSOR c_last_xfr_elements(cp_xfr_action_id IN NUMBER
944 ,cp_legislation_code IN VARCHAR2) IS
945 SELECT assignment_id, action_information_category,
946 jurisdiction_code,
947 action_information6 primary_balance_id,
948 action_information9 ytd_amount,
949 action_information10 reporting_name,
950 effective_date effective_date,
951 action_information12 ytd_hours,
952 action_information15 ytd_days,
953 action_information16 attribute_name
954 FROM pay_action_information
955 WHERE action_information_category IN ('AC EARNINGS', 'AC DEDUCTIONS')
956 AND action_context_id = cp_xfr_action_id;
957
958
959 CURSOR c_get_balance (cp_balance_name IN VARCHAR2
960 ,cp_legislation_code IN VARCHAR2) IS
961 SELECT balance_type_id
962 FROM pay_balance_types
963 WHERE legislation_code = cp_legislation_code
964 AND balance_name = cp_balance_name;
965
966 CURSOR c_last_per_xfr_run IS
967 SELECT pai.action_context_id
968 FROM per_assignments_f paf2,
969 per_assignments_f paf,
970 pay_action_information pai
971 WHERE paf.assignment_id = p_assignment_id
972 AND paf.effective_end_date >= trunc(p_pymt_eff_date, 'Y')
973 AND paf.effective_start_date <= p_pymt_eff_date
974 AND paf.person_id = paf2.person_id
975 AND paf2.effective_end_date >= trunc(p_pymt_eff_date, 'Y')
976 AND paf2.effective_start_date <= p_pymt_eff_date
977 AND paf2.assignment_id = pai.assignment_id
978 AND pai.effective_date >= trunc(p_pymt_eff_date, 'Y')
979 ORDER BY pai.action_context_id DESC;
980
981 CURSOR c_balance_info(cp_primary_balance_id IN NUMBER
982 ,cp_effective_date IN DATE) IS
983 SELECT DISTINCT
984 pbad.attribute_name,
985 pbt_pri.balance_type_id, -- Primary Balance
986 DECODE(pbad.attribute_name,
987 'Hourly Earnings', pbt_sec.balance_type_id,
988 NULL), -- Hours Balance
989 DECODE(pbad.attribute_name,
990 'Employee Earnings', pbt_sec.balance_type_id,
991 NULL) -- Days Balance
992 FROM pay_bal_attribute_definitions pbad,
993 pay_balance_attributes pba,
994 pay_defined_balances pdb,
995 pay_balance_types pbt_pri,
996 pay_balance_types pbt_sec,
997 pay_input_values_f piv,
998 pay_element_types_f pet
999 WHERE pbad.attribute_name IN ('Employee Earnings',
1000 'Hourly Earnings',
1001 'Deductions',
1002 'Taxable Benefits'
1003 -- ,'Employee Taxes',
1004 -- 'Tax Calculation Details'
1005 )
1006 AND pbad.legislation_code = 'MX'
1007 AND pba.attribute_id = pbad.attribute_id
1008 AND pdb.defined_balance_id = pba.defined_balance_id
1009 AND pbt_pri.balance_type_id = pdb.balance_type_id
1010 AND pbt_pri.balance_type_id = cp_primary_balance_id
1011 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1012 AND pbt_pri.input_value_id = piv.input_value_id
1013 AND piv.element_type_id = pet.element_type_id
1014 AND cp_effective_date BETWEEN pet.effective_start_date
1015 AND pet.effective_end_date
1016 AND cp_effective_date BETWEEN piv.effective_start_date
1017 AND piv.effective_end_date
1018 ORDER BY 1;
1019
1020 lv_jurisdiction_code VARCHAR2(80);
1021 ln_primary_balance_id NUMBER;
1022 lv_reporting_name VARCHAR2(150);
1023 ld_effective_date DATE;
1024 ln_hours_balance_id NUMBER;
1025 ln_days_balance_id NUMBER;
1026
1027 ln_t_primary_balance_id NUMBER;
1028 lv_t_reporting_name VARCHAR2(150);
1029 lv_attribute_name VARCHAR2(80);
1030
1031 ln_ele_primary_balance_id NUMBER;
1032 ln_ele_hours_balance_id NUMBER;
1033 ln_ele_days_balance_id NUMBER;
1034
1035 ln_ytd_defined_balance_id NUMBER;
1036 ln_ytd_hours_balance_id NUMBER;
1037 ln_ytd_days_balance_id NUMBER;
1038 ln_payments_amount NUMBER;
1039 ln_ytd_hours NUMBER;
1040 ln_ytd_days NUMBER;
1041 ln_ytd_amount NUMBER;
1042
1043 ln_index NUMBER := 0;
1044 lv_element_archived VARCHAR2(1);
1045 lv_action_info_category VARCHAR2(30);
1046 lv_procedure_name VARCHAR2(100);
1047 lv_error_message VARCHAR2(200);
1048 ln_step NUMBER;
1049 ln_assignment_id NUMBER;
1050 lv_act_info_category VARCHAR2(30);
1051 ln_last_per_xfr_action_id NUMBER;
1052 cn_last_xfr_action_id NUMBER;
1053
1054 BEGIN
1055 lv_element_archived := 'N';
1056 lv_action_info_category := 'AC DEDUCTIONS';
1057 lv_procedure_name := '.get_xfr_elements';
1058
1059 ln_step:= 1;
1060 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1061 hr_utility.trace('p_xfr_action_id = '||p_xfr_action_id);
1062 hr_utility.trace('p_last_xfr_action_id = '|| p_last_xfr_action_id );
1063 hr_utility.trace('p_assignment_id = '|| p_assignment_id );
1064 hr_utility.trace('gv_multi_payroll_pymt = '||
1065 pay_emp_action_arch.gv_multi_payroll_pymt);
1066 hr_utility.trace('p_sepchk_flag = '||p_sepchk_flag);
1067
1068 cn_last_xfr_action_id := p_last_xfr_action_id;
1069
1070 IF pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' AND
1071 p_sepchk_flag = 'Y' THEN
1072
1073 OPEN c_last_per_xfr_run;
1074 FETCH c_last_per_xfr_run INTO ln_last_per_xfr_action_id;
1075 IF c_last_per_xfr_run%FOUND THEN
1076 hr_utility.trace('found ln_last_per_xfr_action_id = '||
1077 ln_last_per_xfr_action_id);
1078 cn_last_xfr_action_id := ln_last_per_xfr_action_id;
1079 END IF;
1080 CLOSE c_last_per_xfr_run;
1081
1082 hr_utility.trace('New cn_last_xfr_action_id = '||cn_last_xfr_action_id);
1083 END IF;
1084
1085
1086 OPEN c_last_xfr_elements(cn_last_xfr_action_id, p_legislation_code);
1087 LOOP
1088 FETCH c_last_xfr_elements INTO ln_assignment_id,
1089 lv_act_info_category,
1090 lv_jurisdiction_code,
1091 ln_primary_balance_id,
1092 ln_ytd_amount,
1093 lv_reporting_name,
1094 ld_effective_date,
1095 ln_ytd_hours,
1096 ln_ytd_days,
1097 lv_attribute_name;
1098
1099 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1100 IF c_last_xfr_elements%NOTFOUND THEN
1101 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1102 EXIT;
1103 END IF;
1104
1105 ln_step := 5;
1106 IF ln_primary_balance_id IS NULL THEN
1107 OPEN c_get_balance(lv_t_reporting_name, p_legislation_code);
1108 FETCH c_get_balance INTO ln_t_primary_balance_id;
1109 CLOSE c_get_balance;
1110 ln_primary_balance_id := ln_t_primary_balance_id;
1111 END IF;
1112
1113 hr_utility.trace('Reporting Name =' || lv_reporting_name);
1114 hr_utility.trace('JD Code =' || lv_jurisdiction_code);
1115
1116 ln_step := 6;
1117
1118 hr_utility.trace('p_assignment_id (current) = '||p_assignment_id);
1119 hr_utility.trace('ln_assignment_id (prev) = '||ln_assignment_id);
1120
1121 IF ((pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' ) AND
1122 (p_sepchk_flag = 'Y') AND
1123 (ln_assignment_id <> p_assignment_id)) THEN
1124
1125 hr_utility.trace('action_info_category = ' ||lv_act_info_category);
1126 hr_utility.trace('ln_primary_balance_id = '||ln_primary_balance_id);
1127 hr_utility.trace('ln_ytd_amount = ' ||ln_ytd_amount);
1128
1129 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1130
1131 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1132 := lv_act_info_category;
1133 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1134 := lv_jurisdiction_code;
1135 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1136 := p_xfr_action_id;
1137 pay_ac_action_arch.lrr_act_tab(ln_index).assignment_id
1138 := ln_assignment_id;
1139 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1140 := ln_primary_balance_id;
1141 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1142 := fnd_number.number_to_canonical(ln_ytd_amount);
1143 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
1144 := lv_reporting_name;
1145 pay_ac_action_arch.lrr_act_tab(ln_index).act_info16
1146 := lv_attribute_name;
1147
1148 IF lv_act_info_category = 'AC EARNINGS' THEN
1149 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1150 := fnd_number.number_to_canonical(ln_ytd_hours);
1151 pay_ac_action_arch.lrr_act_tab(ln_index).act_info15
1152 := fnd_number.number_to_canonical(ln_ytd_days);
1153 END IF;
1154 END IF;
1155
1156 IF ln_assignment_id = p_assignment_id THEN
1157 IF pay_ac_action_arch.emp_elements_tab.count > 0 THEN
1158 FOR i IN pay_ac_action_arch.emp_elements_tab.first..
1159 pay_ac_action_arch.emp_elements_tab.last LOOP
1160 IF pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
1161 = ln_primary_balance_id AND
1162 NVL(pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code,
1163 -999) = NVL(lv_jurisdiction_code, -999) THEN
1164 lv_element_archived := 'Y';
1165 EXIT;
1166 END IF;
1167 END LOOP;
1168 END IF;
1169
1170 ln_step := 10;
1171 IF lv_element_archived = 'N' THEN
1172 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1173 /**************************************************************
1174 ** Check to see if the element is still effective
1175 ** the primary balance IS there before archiving
1176 ** the value when picking elements which have
1177 ** already been archived.
1178 ** Note: This will take care of the issue when clients migrate
1179 ** to a new element and only want one entry to be archived
1180 ** and show up in checks, payslip and depsoit advice
1181 **************************************************************/
1182 OPEN c_balance_info(ln_primary_balance_id, ld_effective_date);
1183 FETCH c_balance_info INTO lv_attribute_name,
1184 ln_ele_primary_balance_id,
1185 ln_ele_hours_balance_id,
1186 ln_ele_days_balance_id;
1187 IF c_balance_info%NOTFOUND OR
1188 ln_ele_primary_balance_id IS NULL THEN
1189 lv_element_archived := 'Y';
1190 END IF;
1191
1192 CLOSE c_balance_info;
1193
1194 IF lv_attribute_name <> 'Deductions' THEN
1195 ln_hours_balance_id := ln_ele_hours_balance_id;
1196 ln_days_balance_id := ln_ele_days_balance_id;
1197 END IF;
1198 END IF;
1199 END IF;
1200
1201
1202 IF lv_element_archived = 'N' THEN
1203 /* populate the extra element table */
1204 ln_index := pay_ac_action_arch.emp_elements_tab.count;
1205 pay_ac_action_arch.emp_elements_tab(ln_index).jurisdiction_code
1206 := lv_jurisdiction_code;
1207 pay_ac_action_arch.emp_elements_tab(ln_index).element_primary_balance_id
1208 := ln_primary_balance_id;
1209 pay_ac_action_arch.emp_elements_tab(ln_index).element_reporting_name
1210 := lv_reporting_name;
1211 pay_ac_action_arch.emp_elements_tab(ln_index).element_hours_balance_id
1212 := ln_hours_balance_id;
1213
1214 IF lv_jurisdiction_code IS NOT NULL THEN
1215 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
1216 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
1217 ELSE
1218 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
1219 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
1220 END IF;
1221
1222 ln_step := 15;
1223 ln_ytd_defined_balance_id
1224 := pay_emp_action_arch.get_defined_balance_id
1225 (ln_primary_balance_id,
1226 gv_ytd_balance_dimension,
1227 p_legislation_code);
1228 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1229 IF ln_ytd_defined_balance_id IS NOT NULL THEN
1230 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
1231 ln_ytd_defined_balance_id,
1232 p_ytd_balcall_aaid),0);
1233 END IF;
1234 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1235 IF ln_hours_balance_id IS NOT NULL THEN
1236 ln_ytd_hours_balance_id
1237 := pay_emp_action_arch.get_defined_balance_id
1238 (ln_hours_balance_id,
1239 gv_ytd_balance_dimension,
1240 p_legislation_code);
1241 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1242 IF ln_ytd_hours_balance_id IS NOT NULL THEN
1243 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
1244 ln_ytd_hours_balance_id,
1245 p_ytd_balcall_aaid),0);
1246 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1247 END IF;
1248 END IF;
1249
1250 IF ln_days_balance_id IS NOT NULL THEN
1251 ln_ytd_days_balance_id
1252 := pay_emp_action_arch.get_defined_balance_id
1253 (ln_days_balance_id,
1254 gv_ytd_balance_dimension,
1255 p_legislation_code);
1256 hr_utility.set_location(gv_package || lv_procedure_name, 93);
1257 IF ln_ytd_days_balance_id IS NOT NULL THEN
1258 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
1259 ln_ytd_days_balance_id,
1260 p_ytd_balcall_aaid),0);
1261 hr_utility.set_location(gv_package || lv_procedure_name, 96);
1262 END IF;
1263 END IF;
1264
1265 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
1266 hr_utility.trace('ln_ytd_hours = '||ln_ytd_hours);
1267 hr_utility.trace('ln_ytd_days = '||ln_ytd_days);
1268
1269 IF (( NVL(ln_ytd_amount, 0) + NVL(ln_payments_amount, 0) <> 0 ) OR
1270 ( pay_ac_action_arch.gv_multi_gre_payment = 'N' ) ) THEN
1271
1272 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1273 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1274 hr_utility.trace('ln_index = ' || ln_index);
1275 ln_step := 20;
1276 IF lv_attribute_name IN ('Employee Earnings',
1277 'Hourly Earnings',
1278 'Taxable Benefits') THEN
1279 lv_action_info_category := 'AC EARNINGS';
1280 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1281 := fnd_number.number_to_canonical(ln_ytd_hours);
1282 pay_ac_action_arch.lrr_act_tab(ln_index).act_info15
1283 := fnd_number.number_to_canonical(ln_ytd_days);
1284
1285 END IF;
1286
1287 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1288 := lv_action_info_category;
1289 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1290 := lv_jurisdiction_code;
1291 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1292 := p_xfr_action_id;
1293 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1294 := ln_primary_balance_id;
1295 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1296 := fnd_number.number_to_canonical(ln_payments_amount);
1297 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1298 := fnd_number.number_to_canonical(ln_ytd_amount);
1299 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
1300 := lv_reporting_name;
1301 pay_ac_action_arch.lrr_act_tab(ln_index).act_info16
1302 := lv_attribute_name;
1303 END IF;
1304 END IF;
1305
1306 lv_element_archived := 'N';
1307 lv_action_info_category := 'AC DEDUCTIONS';
1308 lv_jurisdiction_code := NULL;
1309 ln_primary_balance_id := NULL;
1310 lv_reporting_name := NULL;
1311 ln_hours_balance_id := NULL;
1312 ln_ytd_amount := NULL;
1313 ln_ytd_hours := NULL;
1314
1315 END LOOP;
1316
1317 CLOSE c_last_xfr_elements;
1318
1319 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1320 ln_step := 25;
1321
1322
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 lv_error_message := 'Error at step ' || ln_step ||
1327 ' IN ' || gv_package || lv_procedure_name;
1328
1329 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1330
1331 lv_error_message :=
1332 pay_emp_action_arch.set_error_message(lv_error_message);
1333
1334 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1335 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1336 hr_utility.raise_error;
1337
1338 END get_xfr_elements;
1339
1340
1341
1342 PROCEDURE get_current_elements(p_xfr_action_id IN NUMBER
1343 ,p_curr_pymt_action_id IN NUMBER
1344 ,p_curr_pymt_eff_date IN DATE
1345 ,p_assignment_id IN NUMBER
1346 ,p_tax_unit_id IN NUMBER
1347 ,p_sepchk_flag IN VARCHAR2
1348 ,p_pymt_balcall_aaid IN NUMBER
1349 ,p_ytd_balcall_aaid IN NUMBER
1350 ,p_legislation_code IN VARCHAR2
1351 ,p_action_type IN VARCHAR2 DEFAULT NULL
1352 )
1353 IS
1354
1355 CURSOR c_cur_sp_action_elements(cp_pymt_action_id IN NUMBER
1356 ,cp_assignment_id IN NUMBER
1357 ,cp_sepchk_flag IN VARCHAR2
1358 ) IS
1359 SELECT DISTINCT
1360 NVL(pbtl.reporting_name, pbtl.balance_name),
1361 pbad.attribute_name,
1362 pbt_pri.balance_type_id, -- Primary Balance
1363 DECODE(pbad.attribute_name,
1364 'Hourly Earnings', pbt_sec.balance_type_id,
1365 NULL), -- Hours Balance
1366 DECODE(pbad.attribute_name,
1367 'Employee Earnings', pbt_sec.balance_type_id,
1368 NULL) -- Days Balance
1369 FROM pay_bal_attribute_definitions pbad,
1370 pay_balance_attributes pba,
1371 pay_defined_balances pdb,
1372 pay_balance_types pbt_pri,
1373 pay_balance_types pbt_sec,
1374 pay_balance_types_tl pbtl,
1375 pay_run_balances prb,
1376 pay_assignment_actions paa,
1377 pay_payroll_actions ppa
1378 WHERE pbad.attribute_name IN ('Employee Earnings',
1379 'Hourly Earnings',
1380 'Deductions',
1381 'Taxable Benefits'
1382 -- ,'Employee Taxes',
1383 -- 'Tax Calculation Details'
1384 )
1385 AND pbad.legislation_code = 'MX'
1386 AND pba.attribute_id = pbad.attribute_id
1387 AND pdb.defined_balance_id = pba.defined_balance_id
1388 AND prb.defined_balance_id = pdb.defined_balance_id
1389 AND pbt_pri.balance_type_id = pdb.balance_type_id
1390 AND pbt_pri.input_value_id IS NOT NULL
1391 AND pbtl.balance_type_id = pbt_pri.balance_type_id
1392 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1393 AND pbtl.language = USERENV('LANG')
1394 AND prb.assignment_id = cp_assignment_id
1395 AND paa.assignment_id = prb.assignment_id
1396 AND cp_sepchk_flag = 'Y'
1397 AND prb.assignment_action_id = cp_pymt_action_id
1398 AND prb.assignment_action_id = paa.assignment_action_id
1399 AND NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
1400 (gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
1401 AND ppa.payroll_action_id = paa.payroll_action_id
1402 ORDER BY 1;
1403
1404
1405 CURSOR c_cur_sp_action_elements_RR(cp_pymt_action_id IN NUMBER
1406 ,cp_assignment_id IN NUMBER
1407 ,cp_sepchk_flag IN VARCHAR2
1408 ) IS
1409 SELECT DISTINCT
1410 NVL(pbtl.reporting_name, pbtl.balance_name),
1411 pbad.attribute_name,
1412 pbt_pri.balance_type_id, -- Primary Balance
1413 DECODE(pbad.attribute_name,
1414 'Hourly Earnings', pbt_sec.balance_type_id,
1415 NULL), -- Hours Balance
1416 DECODE(pbad.attribute_name,
1417 'Employee Earnings', pbt_sec.balance_type_id,
1418 NULL) -- Days Balance
1419 FROM pay_bal_attribute_definitions pbad,
1420 pay_balance_attributes pba,
1421 pay_defined_balances pdb,
1422 pay_balance_types pbt_pri,
1423 pay_balance_types pbt_sec,
1424 pay_balance_types_tl pbtl,
1425 pay_run_results prr,
1426 pay_input_values_f piv,
1427 pay_assignment_actions paa,
1428 pay_payroll_actions ppa
1429 WHERE pbad.attribute_name IN ('Employee Earnings',
1430 'Hourly Earnings',
1431 'Deductions',
1432 'Taxable Benefits'
1433 -- ,'Employee Taxes',
1434 -- 'Tax Calculation Details'
1435 )
1436 AND pbad.legislation_code = 'MX'
1437 AND pba.attribute_id = pbad.attribute_id
1438 AND pdb.defined_balance_id = pba.defined_balance_id
1439 AND pbt_pri.balance_type_id = pdb.balance_type_id
1440 AND pbt_pri.input_value_id = piv.input_value_id
1441 AND piv.element_type_id = prr.element_type_id
1442 AND ppa.effective_date BETWEEN piv.effective_start_date
1443 AND piv.effective_end_date
1444 AND pbtl.balance_type_id = pbt_pri.balance_type_id
1445 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1446 AND pbtl.language = USERENV('LANG')
1447 AND paa.assignment_id = cp_assignment_id
1448 AND cp_sepchk_flag = 'Y'
1449 AND prr.assignment_action_id = cp_pymt_action_id
1450 AND prr.assignment_action_id = paa.assignment_action_id
1451 AND NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
1452 (gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
1453 AND ppa.payroll_action_id = paa.payroll_action_id
1454 ORDER BY 1;
1455
1456 CURSOR c_cur_action_elements(cp_pymt_action_id IN NUMBER
1457 ,cp_assignment_id IN NUMBER
1458 ,cp_sepchk_flag IN VARCHAR2
1459 ,cp_ytd_act_sequence IN NUMBER
1460 ) IS
1461 SELECT DISTINCT
1462 NVL(pbtl.reporting_name, pbtl.balance_name),
1463 pbad.attribute_name,
1464 pbt_pri.balance_type_id, -- Primary Balance
1465 DECODE(pbad.attribute_name,
1466 'Hourly Earnings', pbt_sec.balance_type_id,
1467 NULL), -- Hours Balance
1468 DECODE(pbad.attribute_name,
1469 'Employee Earnings', pbt_sec.balance_type_id,
1470 NULL) -- Days Balance
1471 FROM pay_bal_attribute_definitions pbad,
1472 pay_balance_attributes pba,
1473 pay_defined_balances pdb,
1474 pay_balance_types pbt_pri,
1475 pay_balance_types pbt_sec,
1476 pay_balance_types_tl pbtl,
1477 pay_run_balances prb,
1478 pay_assignment_actions paa,
1479 pay_action_interlocks pai,
1480 pay_payroll_actions ppa
1481 WHERE pbad.attribute_name IN ('Employee Earnings',
1482 'Hourly Earnings',
1483 'Deductions',
1484 'Taxable Benefits'
1485 -- ,'Employee Taxes',
1486 -- 'Tax Calculation Details'
1487 )
1488 AND pbad.legislation_code = 'MX'
1489 AND pba.attribute_id = pbad.attribute_id
1490 AND pdb.defined_balance_id = pba.defined_balance_id
1491 AND prb.defined_balance_id = pdb.defined_balance_id
1492 AND pbt_pri.balance_type_id = pdb.balance_type_id
1493 AND pbt_pri.input_value_id IS NOT NULL
1494 AND pbt_pri.balance_type_id = pbtl.balance_type_id
1495 AND pbtl.language = USERENV('LANG')
1496 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1497 AND prb.assignment_id = cp_assignment_id
1498 AND paa.assignment_id = prb.assignment_id
1499 AND cp_sepchk_flag = 'N'
1500 AND pai.locking_action_id = cp_pymt_action_id
1501 AND prb.assignment_action_id = pai.locked_action_id
1502 AND prb.assignment_action_id = paa.assignment_action_id
1503 AND paa.action_sequence <= cp_ytd_act_sequence
1504 AND paa.action_sequence = prb.action_sequence
1505 AND ppa.payroll_action_id = paa.payroll_action_id
1506 ORDER BY 1;
1507
1508 CURSOR c_cur_action_elements_RR(cp_pymt_action_id IN NUMBER
1509 ,cp_assignment_id IN NUMBER
1510 ,cp_sepchk_flag IN VARCHAR2
1511 ,cp_ytd_act_sequence IN NUMBER
1512 ) IS
1513 SELECT DISTINCT
1514 NVL(pbtl.reporting_name, pbtl.balance_name),
1515 pbad.attribute_name,
1516 pbt_pri.balance_type_id, -- Primary Balance
1517 DECODE(pbad.attribute_name,
1518 'Hourly Earnings', pbt_sec.balance_type_id,
1519 NULL), -- Hours Balance
1520 DECODE(pbad.attribute_name,
1521 'Employee Earnings', pbt_sec.balance_type_id,
1522 NULL) -- Days Balance
1523 FROM pay_bal_attribute_definitions pbad,
1524 pay_balance_attributes pba,
1525 pay_defined_balances pdb,
1526 pay_balance_types pbt_pri,
1527 pay_balance_types pbt_sec,
1528 pay_balance_types_tl pbtl,
1529 pay_run_results prr,
1530 pay_input_values_f piv,
1531 pay_assignment_actions paa,
1532 pay_action_interlocks pai,
1533 pay_payroll_actions ppa
1534 WHERE pbad.attribute_name IN ('Employee Earnings',
1535 'Hourly Earnings',
1536 'Deductions',
1537 'Taxable Benefits'
1538 -- ,'Employee Taxes',
1539 -- 'Tax Calculation Details'
1540 )
1541 AND pbad.legislation_code = 'MX'
1542 AND pba.attribute_id = pbad.attribute_id
1543 AND pdb.defined_balance_id = pba.defined_balance_id
1544 AND pbt_pri.balance_type_id = pdb.balance_type_id
1545 -- AND pbt_pri.input_value_id IS NOT NULL
1546 AND pbt_pri.input_value_id = piv.input_value_id
1547 AND piv.element_type_id = prr.element_type_id
1548 AND ppa.effective_date BETWEEN piv.effective_start_date
1549 AND piv.effective_end_date
1550 AND pbt_pri.balance_type_id = pbtl.balance_type_id
1551 AND pbtl.language = USERENV('LANG')
1552 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1553 AND paa.assignment_id = cp_assignment_id
1554 AND cp_sepchk_flag = 'N'
1555 AND pai.locking_action_id = cp_pymt_action_id
1556 AND paa.assignment_action_id = pai.locked_action_id
1557 AND prr.assignment_action_id = paa.assignment_action_id
1558 AND paa.action_sequence <= cp_ytd_act_sequence
1559 AND ppa.payroll_action_id = paa.payroll_action_id
1560 ORDER BY 1;
1561
1562
1563 CURSOR c_ytd_action_seq(cp_asg_act_id IN NUMBER) IS
1564 SELECT paa.action_sequence
1565 FROM pay_assignment_actions paa
1566 WHERE paa.assignment_action_id = cp_asg_act_id;
1567
1568 CURSOR c_business_grp_id IS
1569 SELECT DISTINCT business_group_id
1570 FROM per_assignments_f
1571 WHERE assignment_id = p_assignment_id;
1572
1573 ln_element_type_id NUMBER;
1574 lv_element_classification_name VARCHAR2(80);
1575 lv_reporting_name VARCHAR2(80);
1576 lv_attribute_name VARCHAR2(80);
1577 ln_primary_balance_id NUMBER;
1578 ln_hours_balance_id NUMBER;
1579 ln_days_balance_id NUMBER;
1580 ln_processing_priority NUMBER;
1581 ln_ytd_action_sequence NUMBER;
1582
1583 ln_element_index NUMBER ;
1584 lv_procedure_name VARCHAR2(100);
1585 lv_error_message VARCHAR2(200);
1586 ln_step NUMBER;
1587
1588 st_cnt NUMBER;
1589 end_cnt NUMBER;
1590 lv_business_grp_id NUMBER;
1591 lv_run_bal_status VARCHAR2(1);
1592
1593 BEGIN
1594 lv_procedure_name := '.get_current_elements';
1595
1596 ln_step := 1;
1597 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1598 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
1599 hr_utility.trace('p_assignment_id ' || p_assignment_id);
1600 hr_utility.trace('p_tax_unit_id ' || p_tax_unit_id);
1601 hr_utility.trace('p_sepchk_flag ' || p_sepchk_flag);
1602 hr_utility.trace('p_legislation_code '|| p_legislation_code);
1603 hr_utility.trace('p_curr_pymt_action_id '
1604 ||to_char(p_curr_pymt_action_id ));
1605 hr_utility.trace('p_ytd_balcall_aaid ' || p_ytd_balcall_aaid);
1606 hr_utility.trace('p_pymt_balcall_aaid ' ||p_pymt_balcall_aaid);
1607 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1608
1609 lv_run_bal_status := NULL;
1610
1611 ln_step := 6;
1612 OPEN c_ytd_action_seq(p_ytd_balcall_aaid);
1613 FETCH c_ytd_action_seq INTO ln_ytd_action_sequence;
1614 CLOSE c_ytd_action_seq;
1615
1616 IF run_bal_stat.COUNT >0 THEN
1617 st_cnt := run_bal_stat.FIRST;
1618 end_cnt := run_bal_stat.LAST;
1619 FOR i IN st_cnt..end_cnt LOOP
1620 IF run_bal_stat(i).valid_status = 'N' THEN
1621 lv_run_bal_status := 'N';
1622 EXIT;
1623 END IF;
1624 END LOOP;
1625 ELSE
1626 ln_step := 7;
1627 OPEN c_business_grp_id;
1628 FETCH c_business_grp_id INTO lv_business_grp_id;
1629 CLOSE c_business_grp_id;
1630
1631 run_bal_stat(1).attribute_name := 'Employee Earnings';
1632 run_bal_stat(2).attribute_name := 'Hourly Earnings';
1633 run_bal_stat(3).attribute_name := 'Deductions';
1634 run_bal_stat(4).attribute_name := 'Employee Taxes';
1635 run_bal_stat(5).attribute_name := 'Tax Calculation Details';
1636 run_bal_stat(6).attribute_name := 'Taxable Benefits';
1637
1638 st_cnt := run_bal_stat.FIRST;
1639 end_cnt := run_bal_stat.LAST;
1640
1641 FOR i IN st_cnt..end_cnt LOOP
1642 ln_step := 8;
1643 run_bal_stat(i).valid_status := pay_us_payroll_utils.check_balance_status(
1644 p_curr_pymt_eff_date,
1645 lv_business_grp_id,
1646 run_bal_stat(i).attribute_name,
1647 p_legislation_code);
1648 IF (lv_run_bal_status IS NULL AND run_bal_stat(i).valid_status = 'N') THEN
1649 lv_run_bal_status := 'N';
1650 END IF;
1651 END LOOP;
1652 END IF;
1653
1654 IF lv_run_bal_status IS NULL THEN
1655 lv_run_bal_status := 'Y';
1656 END IF;
1657
1658 ln_step := 10;
1659 IF p_sepchk_flag = 'Y' THEN
1660
1661 IF lv_run_bal_status = 'N' THEN
1662 OPEN c_cur_sp_action_elements_RR(p_curr_pymt_action_id,
1663 p_assignment_id,
1664 p_sepchk_flag);
1665
1666 ELSE
1667 OPEN c_cur_sp_action_elements(p_curr_pymt_action_id,
1668 p_assignment_id,
1669 p_sepchk_flag);
1670 END IF;
1671
1672 ELSIF p_sepchk_flag = 'N' THEN
1673
1674 IF lv_run_bal_status = 'N' THEN
1675 OPEN c_cur_action_elements_RR(p_curr_pymt_action_id,
1676 p_assignment_id,
1677 p_sepchk_flag,
1678 ln_ytd_action_sequence);
1679
1680 ELSE
1681
1682 OPEN c_cur_action_elements(p_curr_pymt_action_id,
1683 p_assignment_id,
1684 p_sepchk_flag,
1685 ln_ytd_action_sequence);
1686 END IF;
1687
1688 END IF;
1689
1690 LOOP
1691 IF p_sepchk_flag = 'Y' THEN
1692 IF lv_run_bal_status = 'N' THEN
1693
1694 FETCH c_cur_sp_action_elements_RR INTO
1695 lv_reporting_name,
1696 lv_attribute_name,
1697 ln_primary_balance_id,
1698 ln_hours_balance_id,
1699 ln_days_balance_id;
1700
1701 IF c_cur_sp_action_elements_RR%NOTFOUND THEN
1702 hr_utility.set_location(gv_package || lv_procedure_name, 25);
1703 EXIT;
1704 END IF;
1705
1706 ELSE
1707
1708 FETCH c_cur_sp_action_elements INTO
1709 lv_reporting_name,
1710 lv_attribute_name,
1711 ln_primary_balance_id,
1712 ln_hours_balance_id,
1713 ln_days_balance_id;
1714
1715 IF c_cur_sp_action_elements%NOTFOUND THEN
1716 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1717 EXIT;
1718 END IF;
1719
1720 END IF;
1721
1722 ELSIF p_sepchk_flag = 'N' THEN
1723 IF lv_run_bal_status = 'N' THEN
1724
1725 FETCH c_cur_action_elements_RR INTO
1726 lv_reporting_name,
1727 lv_attribute_name,
1728 ln_primary_balance_id,
1729 ln_hours_balance_id,
1730 ln_days_balance_id;
1731
1732 IF c_cur_action_elements_RR%NOTFOUND THEN
1733 hr_utility.set_location(gv_package || lv_procedure_name, 33);
1734 EXIT;
1735 END IF;
1736
1737 ELSE
1738
1739 FETCH c_cur_action_elements INTO
1740 lv_reporting_name,
1741 lv_attribute_name,
1742 ln_primary_balance_id,
1743 ln_hours_balance_id,
1744 ln_days_balance_id;
1745
1746 IF c_cur_action_elements%NOTFOUND THEN
1747 hr_utility.set_location(gv_package || lv_procedure_name, 36);
1748 EXIT;
1749 END IF;
1750
1751 END IF;
1752
1753 END IF;
1754
1755 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1756 hr_utility.trace('Primary Bal id = '|| ln_primary_balance_id);
1757
1758 ln_step := 15;
1759 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
1760 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
1761 := ln_primary_balance_id;
1762 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
1763 := lv_reporting_name;
1764 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
1765 := ln_hours_balance_id;
1766
1767 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1768 ln_step := 20;
1769
1770 populate_elements(p_xfr_action_id => p_xfr_action_id
1771 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
1772 ,p_pymt_eff_date => p_curr_pymt_eff_date
1773 ,p_primary_balance_id => ln_primary_balance_id
1774 ,p_hours_balance_id => ln_hours_balance_id
1775 ,p_days_balance_id => ln_days_balance_id
1776 ,p_attribute_name => lv_attribute_name
1777 ,p_reporting_name => lv_reporting_name
1778 ,p_tax_unit_id => p_tax_unit_id
1779 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
1780 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
1781 ,p_legislation_code => p_legislation_code
1782 ,p_sepchk_flag => p_sepchk_flag
1783 ,p_action_type => p_action_type
1784 );
1785 END LOOP;
1786 IF p_sepchk_flag = 'Y' THEN
1787 IF lv_run_bal_status = 'N' THEN
1788 CLOSE c_cur_sp_action_elements_RR;
1789 ELSE
1790 CLOSE c_cur_sp_action_elements;
1791 END IF;
1792 ELSIF p_sepchk_flag = 'N' THEN
1793 IF lv_run_bal_status = 'N' THEN
1794 CLOSE c_cur_action_elements_RR;
1795 ELSE
1796 CLOSE c_cur_action_elements;
1797 END IF;
1798 END IF;
1799 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1800 ln_step := 25;
1801
1802 EXCEPTION
1803 WHEN OTHERS THEN
1804 lv_error_message := 'Error at step ' || ln_step ||
1805 ' IN ' || gv_package || lv_procedure_name;
1806
1807 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1808
1809 lv_error_message :=
1810 pay_emp_action_arch.set_error_message(lv_error_message);
1811
1812 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1813 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1814 hr_utility.raise_error;
1815
1816 END get_current_elements;
1817
1818
1819 PROCEDURE first_time_process(p_assignment_id IN NUMBER
1820 ,p_xfr_action_id IN NUMBER
1821 ,p_curr_pymt_action_id IN NUMBER
1822 ,p_curr_pymt_eff_date IN DATE
1823 ,p_curr_eff_date IN DATE
1824 ,p_tax_unit_id IN NUMBER
1825 ,p_ytd_balcall_aaid IN NUMBER
1826 ,p_pymt_balcall_aaid IN NUMBER
1827 ,p_sepchk_flag IN VARCHAR2
1828 ,p_legislation_code IN VARCHAR2
1829 )
1830
1831 IS
1832
1833 lv_element_classification_name VARCHAR2(80);
1834 ln_processing_priority NUMBER;
1835 lv_reporting_name VARCHAR2(80);
1836 lv_attribute_name VARCHAR2(80);
1837 ln_element_type_id NUMBER;
1838 lv_jurisdiction_code VARCHAR2(80);
1839 ln_primary_balance_id NUMBER;
1840 ln_hours_balance_id NUMBER;
1841 ln_days_balance_id NUMBER;
1842
1843 ln_element_index NUMBER ;
1844 lv_element_archived VARCHAR2(1);
1845 lv_procedure_name VARCHAR2(100);
1846 lv_error_message VARCHAR2(200);
1847 ln_step NUMBER;
1848
1849 i NUMBER;
1850 st_cnt NUMBER;
1851 end_cnt NUMBER;
1852 lv_business_grp_id NUMBER;
1853 lv_run_bal_status VARCHAR2(1);
1854
1855 CURSOR c_business_grp_id IS
1856 SELECT DISTINCT business_group_id
1857 FROM per_assignments_f
1858 WHERE assignment_id = p_assignment_id;
1859
1860 CURSOR c_prev_ytd_action_elem_rbr(cp_assignment_id IN NUMBER
1861 ,cp_curr_eff_date IN DATE
1862 ) IS
1863 SELECT DISTINCT
1864 pbad.attribute_name,
1865 NVL(pbtl.reporting_name, pbtl.balance_name),
1866 prb.jurisdiction_code,
1867 pbt_pri.balance_type_id, -- Primary Balance
1868 DECODE(pbad.attribute_name,
1869 'Hourly Earnings', pbt_sec.balance_type_id,
1870 NULL), -- Hours Balance
1871 DECODE(pbad.attribute_name,
1872 'Employee Earnings', pbt_sec.balance_type_id,
1873 NULL) -- Days Balance
1874 FROM pay_bal_attribute_definitions pbad,
1875 pay_balance_attributes pba,
1876 pay_defined_balances pdb,
1877 pay_balance_types pbt_pri,
1878 pay_balance_types pbt_sec,
1879 pay_balance_types_tl pbtl,
1880 pay_run_balances prb
1881 WHERE pbad.attribute_name IN ('Employee Earnings',
1882 'Hourly Earnings',
1883 'Deductions',
1884 'Taxable Benefits'
1885 -- ,'Employee Taxes',
1886 -- 'Tax Calculation Details'
1887 )
1888 AND pbad.legislation_code = 'MX'
1889 AND pba.attribute_id = pbad.attribute_id
1890 AND pdb.defined_balance_id = pba.defined_balance_id
1891 AND pbt_pri.balance_type_id = pdb.balance_type_id
1892 AND pbt_pri.input_value_id IS NOT NULL
1893 AND pbtl.balance_type_id = pbt_pri.balance_type_id
1894 AND pbtl.language = USERENV('LANG')
1895 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1896 AND prb.effective_date >= trunc(cp_curr_eff_date,'Y')
1897 AND prb.effective_date <= cp_curr_eff_date
1898 AND prb.assignment_id = cp_assignment_id
1899 AND pdb.defined_balance_id = prb.defined_balance_id
1900 ORDER BY 1;
1901
1902
1903 CURSOR c_prev_ytd_action_elements(cp_assignment_id IN NUMBER
1904 ,cp_curr_eff_date IN DATE
1905 ) IS
1906 SELECT DISTINCT
1907 pbad.attribute_name,
1908 NVL(pbtl.reporting_name, pbtl.balance_name),
1909 prr.jurisdiction_code,
1910 pbt_pri.balance_type_id, -- Primary Balance
1911 DECODE(pbad.attribute_name,
1912 'Hourly Earnings', pbt_sec.balance_type_id,
1913 NULL), -- Hours Balance
1914 DECODE(pbad.attribute_name,
1915 'Employee Earnings', pbt_sec.balance_type_id,
1916 NULL) -- Days Balance
1917 FROM pay_bal_attribute_definitions pbad,
1918 pay_balance_attributes pba,
1919 pay_defined_balances pdb,
1920 pay_balance_types pbt_pri,
1921 pay_balance_types pbt_sec,
1922 pay_balance_types_tl pbtl,
1923 pay_assignment_actions paa,
1924 pay_payroll_actions ppa,
1925 pay_run_results prr,
1926 pay_input_values_f piv
1927 WHERE pbad.attribute_name IN ('Employee Earnings',
1928 'Hourly Earnings',
1929 'Deductions',
1930 'Taxable Benefits'
1931 -- ,'Employee Taxes',
1932 -- 'Tax Calculation Details'
1933 )
1934 AND pbad.legislation_code = 'MX'
1935 AND pba.attribute_id = pbad.attribute_id
1936 AND pdb.defined_balance_id = pba.defined_balance_id
1937 AND pbt_pri.balance_type_id = pdb.balance_type_id
1938 AND pbt_pri.input_value_id = piv.input_value_id
1939 AND piv.element_type_id = prr.element_type_id
1940 AND ppa.effective_date BETWEEN piv.effective_start_date
1941 AND piv.effective_end_date
1942 AND pbtl.balance_type_id = pbt_pri.balance_type_id
1943 AND pbtl.language = USERENV('LANG')
1944 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
1945 AND prr.assignment_action_id = paa.assignment_action_id
1946 AND paa.assignment_id = cp_assignment_id
1947 AND ppa.payroll_action_id = paa.payroll_action_id
1948 AND ppa.action_type in ('Q','R','B')
1949 AND ppa.effective_date >= TRUNC(cp_curr_eff_date,'Y')
1950 AND ppa.effective_date <= cp_curr_eff_date
1951 ORDER BY 1;
1952
1953 BEGIN
1954 ln_step := 1;
1955 lv_run_bal_status := NULL;
1956 lv_element_archived := 'N';
1957 lv_procedure_name := '.first_time_process';
1958
1959 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1960 hr_utility.trace('p_xfr_action_id' || p_xfr_action_id);
1961 hr_utility.trace('p_assignment_id '|| p_assignment_id);
1962 hr_utility.trace('p_curr_eff_date '|| p_curr_eff_date);
1963 hr_utility.trace('p_tax_unit_id ' || p_tax_unit_id);
1964 hr_utility.trace('p_sepchk_flag ' || p_sepchk_flag);
1965 hr_utility.trace('p_legislation_code ' || p_legislation_code);
1966 hr_utility.trace('p_ytd_balcall_aaid ' || p_ytd_balcall_aaid);
1967 hr_utility.trace('p_pymt_balcall_aaid ' || p_pymt_balcall_aaid);
1968 hr_utility.trace('p_curr_pymt_action_id '
1969 ||to_char(p_curr_pymt_action_id ));
1970
1971 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1972 get_current_elements(p_xfr_action_id => p_xfr_action_id
1973 ,p_curr_pymt_action_id => p_curr_pymt_action_id
1974 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1975 ,p_assignment_id => p_assignment_id
1976 ,p_tax_unit_id => p_tax_unit_id
1977 ,p_sepchk_flag => p_sepchk_flag
1978 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
1979 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
1980 ,p_legislation_code => p_legislation_code);
1981 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1982
1983 -- Populating the PL/SQL table run_bal_stat_tab with the validity status
1984 -- of various attributes. If already populated, we use that to check the
1985 -- validity
1986
1987 IF run_bal_stat.COUNT >0 THEN
1988 st_cnt := run_bal_stat.FIRST;
1989 end_cnt := run_bal_stat.LAST;
1990 FOR i IN st_cnt..end_cnt LOOP
1991 IF run_bal_stat(i).valid_status = 'N' THEN
1992 lv_run_bal_status := 'N';
1993 EXIT;
1994 END IF;
1995 END LOOP;
1996 ELSE
1997 OPEN c_business_grp_id;
1998 FETCH c_business_grp_id INTO lv_business_grp_id;
1999 CLOSE c_business_grp_id;
2000
2001 run_bal_stat(1).attribute_name := 'Employee Earnings';
2002 run_bal_stat(2).attribute_name := 'Hourly Earnings';
2003 run_bal_stat(3).attribute_name := 'Deductions';
2004 run_bal_stat(4).attribute_name := 'Employee Taxes';
2005 run_bal_stat(5).attribute_name := 'Tax Calculation Details';
2006 run_bal_stat(6).attribute_name := 'Taxable Benefits';
2007
2008 st_cnt := run_bal_stat.FIRST;
2009 end_cnt := run_bal_stat.LAST;
2010
2011 FOR i IN st_cnt..end_cnt LOOP
2012 run_bal_stat(i).valid_status := pay_us_payroll_utils.check_balance_status(
2013 p_curr_pymt_eff_date,
2014 lv_business_grp_id,
2015 run_bal_stat(i).attribute_name,
2016 p_legislation_code);
2017 IF (lv_run_bal_status IS NULL AND run_bal_stat(i).valid_status = 'N') THEN
2018 lv_run_bal_status := 'N';
2019 END IF;
2020 END LOOP;
2021 END IF;
2022
2023 IF lv_run_bal_status IS NULL THEN
2024 lv_run_bal_status := 'Y';
2025 END IF;
2026
2027 ln_step := 5;
2028
2029
2030 IF lv_run_bal_status = 'Y' THEN
2031 OPEN c_prev_ytd_action_elem_rbr(p_assignment_id,
2032 p_curr_pymt_eff_date);
2033 ELSE
2034 OPEN c_prev_ytd_action_elements(p_assignment_id,
2035 p_curr_pymt_eff_date);
2036 END IF;
2037
2038 LOOP
2039 IF lv_run_bal_status = 'Y' THEN
2040 FETCH c_prev_ytd_action_elem_rbr INTO
2041 lv_attribute_name,
2042 lv_reporting_name,
2043 lv_jurisdiction_code,
2044 ln_primary_balance_id,
2045 ln_hours_balance_id,
2046 ln_days_balance_id;
2047
2048 IF c_prev_ytd_action_elem_rbr%NOTFOUND THEN
2049 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2050 EXIT;
2051 END IF;
2052 ELSE
2053 FETCH c_prev_ytd_action_elements INTO
2054 lv_attribute_name,
2055 lv_reporting_name,
2056 lv_jurisdiction_code,
2057 ln_primary_balance_id,
2058 ln_hours_balance_id,
2059 ln_days_balance_id;
2060
2061 IF c_prev_ytd_action_elements%NOTFOUND THEN
2062 hr_utility.set_location(gv_package || lv_procedure_name, 45);
2063 EXIT;
2064 END IF;
2065 END IF;
2066
2067 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2068 hr_utility.trace('Reporting Name = '|| lv_reporting_name);
2069 hr_utility.trace('Primary Bal id = '|| ln_primary_balance_id);
2070 hr_utility.trace('JD Code = ' || lv_jurisdiction_code);
2071
2072 IF lv_attribute_name IN ('Deductions',
2073 'Employee Taxes',
2074 'Tax Calculation Details') THEN
2075 ln_step := 10;
2076 ln_hours_balance_id := NULL;
2077 ln_days_balance_id := NULL;
2078
2079 END IF;
2080
2081 /**********************************************************
2082 ** check whether the element has already been archived
2083 ** when archiving the Current Action. If it has been archived
2084 ** skip the element
2085 **********************************************************/
2086 ln_step := 15;
2087 FOR i IN pay_ac_action_arch.emp_elements_tab.first ..
2088 pay_ac_action_arch.emp_elements_tab.last LOOP
2089
2090 IF pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
2091 = ln_primary_balance_id AND
2092 NVL(pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code,
2093 -999) = NVL(lv_jurisdiction_code, -999) THEN
2094
2095 hr_utility.set_location(gv_package || lv_procedure_name, 65);
2096 lv_element_archived := 'Y';
2097 EXIT;
2098 END IF;
2099 END LOOP;
2100
2101 IF lv_element_archived = 'N' THEN
2102 ln_step := 20;
2103 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2104 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
2105 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
2106 := lv_reporting_name;
2107 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
2108 := ln_primary_balance_id;
2109 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
2110 := ln_hours_balance_id;
2111 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
2112 := lv_jurisdiction_code;
2113
2114 /*****************************************************************
2115 ** The Payment Assignemnt Action is not passed to this procedure
2116 ** as we do not want to call the Payment Balance.
2117 *****************************************************************/
2118 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2119
2120 ln_step := 25;
2121 populate_elements(p_xfr_action_id => p_xfr_action_id
2122 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
2123 ,p_pymt_eff_date => p_curr_pymt_eff_date
2124 ,p_primary_balance_id => ln_primary_balance_id
2125 ,p_hours_balance_id => ln_hours_balance_id
2126 ,p_days_balance_id => ln_days_balance_id
2127 ,p_attribute_name => lv_attribute_name
2128 ,p_reporting_name => lv_reporting_name
2129 ,p_tax_unit_id => p_tax_unit_id
2130 ,p_pymt_balcall_aaid => NULL
2131 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2132 ,p_jurisdiction_code => lv_jurisdiction_code
2133 ,p_legislation_code => p_legislation_code
2134 ,p_sepchk_flag => p_sepchk_flag
2135 );
2136 END IF;
2137 lv_element_archived := 'N'; -- Initilializing the variable back
2138 -- to N FOR the next element
2139 lv_jurisdiction_code := NULL;
2140 ln_primary_balance_id := NULL;
2141 lv_reporting_name := NULL;
2142 ln_hours_balance_id := NULL;
2143 END LOOP;
2144
2145 IF lv_run_bal_status = 'Y' THEN
2146 CLOSE c_prev_ytd_action_elem_rbr;
2147 ELSE
2148 CLOSE c_prev_ytd_action_elements;
2149 END IF;
2150
2151 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2152
2153
2154 ln_step := 30;
2155 IF pay_ac_action_arch.lrr_act_tab.count > 0 THEN
2156 FOR i IN pay_ac_action_arch.lrr_act_tab.first ..
2157 pay_ac_action_arch.lrr_act_tab.last LOOP
2158
2159 hr_utility.trace('after populate_elements ftp' ||
2160 ' action_context_id IS ' ||
2161 to_char(pay_ac_action_arch.lrr_act_tab(i).action_context_id));
2162 hr_utility.trace('action_info_category ' ||
2163 pay_ac_action_arch.lrr_act_tab(i).action_info_category);
2164 hr_utility.trace('act_info1 IS ' ||
2165 pay_ac_action_arch.lrr_act_tab(i).act_info1);
2166 hr_utility.trace('act_info10 ' ||
2167 pay_ac_action_arch.lrr_act_tab(i).act_info10);
2168 hr_utility.trace('act_info3 ' ||
2169 pay_ac_action_arch.lrr_act_tab(i).act_info3);
2170 hr_utility.trace('act_info4 ' ||
2171 pay_ac_action_arch.lrr_act_tab(i).act_info4);
2172 hr_utility.trace('act_info5 ' ||
2173 pay_ac_action_arch.lrr_act_tab(i).act_info5);
2174 hr_utility.trace('act_info6 ' ||
2175 pay_ac_action_arch.lrr_act_tab(i).act_info6);
2176 hr_utility.trace('act_info7 ' ||
2177 pay_ac_action_arch.lrr_act_tab(i).act_info7);
2178 hr_utility.trace('act_info8 ' ||
2179 pay_ac_action_arch.lrr_act_tab(i).act_info8);
2180
2181 END LOOP;
2182 END IF;
2183
2184 hr_utility.set_location(gv_package || lv_procedure_name, 110);
2185
2186
2187 EXCEPTION
2188 WHEN OTHERS THEN
2189
2190 lv_error_message := 'Error at step ' || ln_step ||
2191 ' IN ' || gv_package || lv_procedure_name;
2192
2193 hr_utility.trace(lv_error_message || '-' || SQLERRM);
2194
2195 lv_error_message :=
2196 pay_emp_action_arch.set_error_message(lv_error_message);
2197
2198 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2199 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2200 hr_utility.raise_error;
2201
2202 END first_time_process;
2203
2204
2205 /******************************************************************
2206 Name : range_code
2207 Purpose : This returns the select statement that is
2208 used to created the range rows for the Payslip
2209 Archiver.
2210 Arguments :
2211 Notes : Calls procedure - get_payroll_action_info
2212 ******************************************************************/
2213 PROCEDURE range_code(
2214 p_payroll_action_id IN NUMBER
2215 ,p_sqlstr OUT NOCOPY VARCHAR2)
2216 IS
2217
2218 ld_end_date DATE;
2219 ld_start_date DATE;
2220 ln_business_group_id NUMBER;
2221 ln_cons_set_id NUMBER;
2222 ln_payroll_id NUMBER;
2223
2224 lv_sql_string VARCHAR2(32000);
2225 lv_procedure_name VARCHAR2(100);
2226
2227 BEGIN
2228 lv_procedure_name := '.range_code';
2229
2230 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2231 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
2232 ,p_start_date => ld_start_date
2233 ,p_end_date => ld_end_date
2234 ,p_business_group_id => ln_business_group_id
2235 ,p_cons_set_id => ln_cons_set_id
2236 ,p_payroll_id => ln_payroll_id);
2237 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2238
2239 lv_sql_string :=
2240 'SELECT DISTINCT paf.person_id
2241 FROM pay_assignment_actions paa,
2242 pay_payroll_actions ppa,
2243 per_assignments_f paf
2244 WHERE ppa.business_group_id = ''' || ln_business_group_id || '''
2245 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
2246 fnd_date.date_to_canonical(ld_start_date) || ''')
2247 AND fnd_date.canonical_to_date(''' ||
2248 fnd_date.date_to_canonical(ld_end_date) || ''')
2249 AND ppa.action_type IN (''U'',''P'',''B'',''V'')
2250 AND DECODE(ppa.action_type,
2251 ''B'', NVL(ppa.future_process_mode, ''Y''),
2252 ''N'') = ''N''
2253 AND ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
2254 AND ppa.payroll_id = ''' || ln_payroll_id || '''
2255 AND ppa.payroll_action_id = paa.payroll_action_id
2256 AND paa.action_status = ''C''
2257 AND paa.source_action_id IS NULL
2258 AND paf.assignment_id = paa.assignment_id
2259 AND ppa.effective_date BETWEEN paf.effective_start_date
2260 AND paf.effective_end_date
2261 AND NOT EXISTS
2262 (SELECT ''x''
2263 FROM pay_action_interlocks pai,
2264 pay_assignment_actions paa1,
2265 pay_payroll_actions ppa1
2266 WHERE pai.locked_action_id = paa.assignment_action_id
2267 AND paa1.assignment_action_id = pai.locking_action_id
2268 AND ppa1.payroll_action_id = paa1.payroll_action_id
2269 AND ppa1.action_type =''X''
2270 AND ppa1.report_type = ''MX_PAYSLIP_ARCHIVE'')
2271 AND :payroll_action_id > 0 -- Bug 4202702
2272 ORDER BY paf.person_id';
2273
2274 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2275 p_sqlstr := lv_sql_string;
2276 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2277
2278 END range_code;
2279
2280
2281 /************************************************************
2282 Name : assignment_action_code
2283 Purpose : This creates the assignment actions for
2284 a specific chunk of people to be archived
2285 by the Archiver process.
2286 Arguments :
2287 Notes : Calls procedure - get_payroll_action_info
2288 ************************************************************/
2289 PROCEDURE assignment_action_code(
2290 p_payroll_action_id IN NUMBER
2291 ,p_start_person_id IN NUMBER
2292 ,p_end_person_id IN NUMBER
2293 ,p_chunk IN NUMBER)
2294 IS
2295
2296 CURSOR c_get_arch_emp( cp_start_person_id IN NUMBER
2297 ,cp_end_person_id IN NUMBER
2298 ,cp_cons_set_id IN NUMBER
2299 ,cp_payroll_id IN NUMBER
2300 ,cp_business_group_id IN NUMBER
2301 ,cp_start_date IN DATE
2302 ,cp_end_date IN DATE
2303 ) IS
2304 SELECT paa.assignment_id,
2305 paa.tax_unit_id,
2306 ppa.effective_date,
2307 ppa.date_earned,
2308 ppa.action_type,
2309 paa.assignment_action_id,
2310 paa.payroll_action_id
2311 FROM pay_payroll_actions ppa,
2312 pay_assignment_actions paa,
2313 per_assignments_f paf
2314 WHERE paf.person_id BETWEEN cp_start_person_id
2315 AND cp_end_person_id
2316 AND paa.assignment_id = paf.assignment_id
2317 AND ppa.effective_date BETWEEN paf.effective_start_date
2318 AND paf.effective_end_date
2319 AND ppa.consolidation_set_id
2320 = NVL(cp_cons_set_id,ppa.consolidation_set_id)
2321 AND paa.action_status = 'C'
2322 AND ppa.payroll_id = cp_payroll_id
2323 AND ppa.payroll_action_id = paa.payroll_action_id
2324 AND ppa.business_group_id = cp_business_group_id
2325 AND ppa.effective_date BETWEEN cp_start_date
2326 AND cp_end_date
2327 AND ppa.action_type IN ('U','P','B','V')
2328 AND DECODE(ppa.action_type,
2329 'B', NVL(ppa.future_process_mode, 'Y'),
2330 'N') = 'N'
2331 AND paa.source_action_id IS NULL
2332 AND NOT EXISTS
2333 (SELECT 'x'
2334 FROM pay_action_interlocks pai1,
2335 pay_assignment_actions paa1,
2336 pay_payroll_actions ppa1
2337 WHERE pai1.locked_action_id = paa.assignment_action_id
2338 AND paa1.assignment_action_id = pai1.locking_action_id
2339 AND ppa1.payroll_action_id = paa1.payroll_action_id
2340 AND ppa1.action_type ='X'
2341 AND ppa1.report_type = 'MX_PAYSLIP_ARCHIVE')
2342 ORDER BY 1,2,3,5,6;
2343
2344 CURSOR c_get_arch_range_emp(
2345 cp_payroll_action_id IN NUMBER
2346 ,cp_chunk_number IN NUMBER
2347 ,cp_cons_set_id IN NUMBER
2348 ,cp_payroll_id IN NUMBER
2349 ,cp_business_group_id IN NUMBER
2350 ,cp_start_date IN DATE
2351 ,cp_end_date IN DATE
2352 ) IS
2353 SELECT paa.assignment_id,
2354 paa.tax_unit_id,
2355 ppa.effective_date,
2356 ppa.date_earned,
2357 ppa.action_type,
2358 paa.assignment_action_id,
2359 paa.payroll_action_id
2360 FROM pay_payroll_actions ppa,
2361 pay_assignment_actions paa,
2362 per_assignments_f paf,
2363 pay_population_ranges ppr
2364 WHERE ppr.payroll_action_id = cp_payroll_action_id
2365 AND ppr.chunk_number = cp_chunk_number
2366 AND paf.person_id = ppr.person_id
2367 AND ppa.effective_date BETWEEN paf.effective_start_date
2368 AND paf.effective_end_date
2369 AND paa.assignment_id = paf.assignment_id
2370 AND ppa.consolidation_set_id
2371 = NVL(cp_cons_set_id,ppa.consolidation_set_id)
2372 AND paa.action_status = 'C'
2373 AND ppa.payroll_id = cp_payroll_id
2374 AND ppa.payroll_action_id = paa.payroll_action_id
2375 AND ppa.business_group_id = cp_business_group_id
2376 AND ppa.effective_date BETWEEN cp_start_date
2377 AND cp_end_date
2378 AND ppa.action_type IN ('U','P','B','V')
2379 AND DECODE(ppa.action_type,
2380 'B', NVL(ppa.future_process_mode, 'Y'),
2381 'N') = 'N'
2382 AND paa.source_action_id IS NULL
2383 AND NOT EXISTS
2384 (SELECT 'x'
2385 FROM pay_action_interlocks pai1,
2386 pay_assignment_actions paa1,
2387 pay_payroll_actions ppa1
2388 WHERE pai1.locked_action_id = paa.assignment_action_id
2389 AND paa1.assignment_action_id = pai1.locking_action_id
2390 AND ppa1.payroll_action_id = paa1.payroll_action_id
2391 AND ppa1.action_type ='X'
2392 AND ppa1.report_type = 'MX_PAYSLIP_ARCHIVE')
2393 ORDER BY 1,2,3,5,6;
2394
2395 CURSOR c_master_action(cp_prepayment_action_id NUMBER) IS
2396 SELECT MAX(paa.assignment_action_id)
2397 FROM pay_payroll_actions ppa,
2398 pay_assignment_actions paa,
2399 pay_action_interlocks pai
2400 WHERE pai.locking_action_Id = cp_prepayment_action_id
2401 AND paa.assignment_action_id = pai.locked_action_id
2402 AND paa.source_action_id IS NULL
2403 AND ppa.payroll_action_id = paa.payroll_action_id
2404 AND ppa.action_type IN ('R', 'Q');
2405
2406 ln_assignment_id NUMBER := 0;
2407 ln_tax_unit_id NUMBER := 0;
2408 ld_effective_date DATE;
2409 ld_date_earned DATE;
2410 lv_action_type VARCHAR2(10);
2411 ln_asg_action_id NUMBER := 0;
2412 ln_payroll_action_id NUMBER := 0;
2413
2414 ln_master_action_id NUMBER := 0;
2415
2416 ld_end_date DATE;
2417 ld_start_date DATE;
2418 ln_business_group_id NUMBER;
2419 ln_cons_set_id NUMBER;
2420 ln_payroll_id NUMBER;
2421
2422 ln_prev_asg_action_id NUMBER := 0;
2423 ln_prev_assignment_id NUMBER := 0;
2424 ln_prev_tax_unit_id NUMBER := 0;
2425 ld_prev_effective_date DATE;
2426
2427 ln_xfr_action_id NUMBER;
2428
2429 lv_serial_number VARCHAR2(30);
2430 lv_procedure_name VARCHAR2(100);
2431 lv_error_message VARCHAR2(200);
2432 ln_step NUMBER;
2433
2434 lb_range_person BOOLEAN;
2435
2436 BEGIN
2437 ld_effective_date := fnd_date.canonical_to_date('1900/12/31');
2438 lv_procedure_name := '.assignment_action_code';
2439
2440 ln_step := 1;
2441 pay_emp_action_arch.gv_error_message := NULL;
2442 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2443
2444 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
2445 ,p_start_date => ld_start_date
2446 ,p_end_date => ld_end_date
2447 ,p_business_group_id => ln_business_group_id
2448 ,p_cons_set_id => ln_cons_set_id
2449 ,p_payroll_id => ln_payroll_id);
2450 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2451
2452 lb_range_person := pay_ac_utility.range_person_on(
2453 p_report_type => 'MX_PAYSLIP_ARCHIVE'
2454 ,p_report_format => 'MX_PAYSLIP_ARCHIVE'
2455 ,p_report_qualifier => 'MX'
2456 ,p_report_category => 'ARCHIVE');
2457
2458 ln_step := 2;
2459 IF lb_range_person THEN
2460 OPEN c_get_arch_range_emp(p_payroll_action_id
2461 ,p_chunk
2462 ,ln_cons_set_id
2463 ,ln_payroll_id
2464 ,ln_business_group_id
2465 ,ld_start_date
2466 ,ld_end_date);
2467 ELSE
2468 OPEN c_get_arch_emp( p_start_person_id
2469 ,p_end_person_id
2470 ,ln_cons_set_id
2471 ,ln_payroll_id
2472 ,ln_business_group_id
2473 ,ld_start_date
2474 ,ld_end_date);
2475 END IF;
2476
2477 -- Loop for all rows returned for SQL statement.
2478 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2479 LOOP
2480 IF lb_range_person THEN
2481 FETCH c_get_arch_range_emp INTO ln_assignment_id,
2482 ln_tax_unit_id,
2483 ld_effective_date,
2484 ld_date_earned,
2485 lv_action_type,
2486 ln_asg_action_id,
2487 ln_payroll_action_id;
2488 EXIT WHEN c_get_arch_range_emp%NOTFOUND;
2489 ELSE
2490
2491 FETCH c_get_arch_emp INTO ln_assignment_id,
2492 ln_tax_unit_id,
2493 ld_effective_date,
2494 ld_date_earned,
2495 lv_action_type,
2496 ln_asg_action_id,
2497 ln_payroll_action_id;
2498
2499 EXIT WHEN c_get_arch_emp%NOTFOUND;
2500 END IF;
2501
2502 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2503 hr_utility.trace('ln_assignment_id = ' ||
2504 TO_CHAR(ln_assignment_id));
2505
2506 /********************************************************
2507 ** If Balance Adjustment, only create one assignment
2508 ** action record. As there could be multiple assignment
2509 ** actions for Balance Adjustment, we lock all the
2510 ** balance adj record.
2511 ** First time the ELSE portion will be executed which
2512 ** creates the assignment action. If the Assignment ID,
2513 ** Tax Unit ID and Effective Date is same and Action
2514 ** Type is Balance Adjm, only then lock the record
2515 ********************************************************/
2516 IF ln_assignment_id = ln_prev_assignment_id AND
2517 ln_tax_unit_id = ln_prev_tax_unit_id AND
2518 ld_effective_date = ld_prev_effective_date AND
2519 lv_action_type = 'B' AND
2520 ln_asg_action_id <> ln_prev_asg_action_id THEN
2521
2522 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2523 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
2524 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
2525 hr_nonrun_asact.insint(ln_xfr_action_id
2526 ,ln_asg_action_id);
2527 ELSE
2528 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2529 hr_utility.trace('Action_type = '||lv_action_type );
2530
2531 SELECT pay_assignment_actions_s.NEXTVAL
2532 INTO ln_xfr_action_id
2533 FROM dual;
2534
2535 -- insert into pay_assignment_actions.
2536 hr_nonrun_asact.insact(ln_xfr_action_id,
2537 ln_assignment_id,
2538 p_payroll_action_id,
2539 p_chunk,
2540 ln_tax_unit_id,
2541 NULL,
2542 'U',
2543 NULL);
2544 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2545 hr_utility.trace('ln_asg_action_id = ' || ln_asg_action_id);
2546 hr_utility.trace('ln_xfr_action_id = ' || ln_xfr_action_id);
2547 hr_utility.trace('p_payroll_action_id = ' || p_payroll_action_id);
2548 hr_utility.trace('ln_tax_unit_id = ' || ln_tax_unit_id);
2549 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2550
2551 -- insert an interlock to this action
2552 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
2553 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
2554 hr_nonrun_asact.insint(ln_xfr_action_id,
2555 ln_asg_action_id);
2556
2557 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2558
2559 /********************************************************
2560 ** For Balance Adj we put only the first assignment action
2561 ********************************************************/
2562 lv_serial_number := lv_action_type || 'N' ||
2563 ln_asg_action_id;
2564
2565 UPdate pay_assignment_actions
2566 SET serial_number = lv_serial_number
2567 WHERE assignment_action_id = ln_xfr_action_id;
2568
2569 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2570
2571 END IF ; --ln_assignment_id ...
2572
2573 ln_prev_tax_unit_id := ln_tax_unit_id;
2574 ld_prev_effective_date := ld_effective_date;
2575 ln_prev_assignment_id := ln_assignment_id;
2576 ln_prev_asg_action_id := ln_asg_action_id;
2577
2578 END LOOP;
2579 IF lb_range_person THEN
2580 CLOSE c_get_arch_range_emp;
2581 ELSE
2582 CLOSE c_get_arch_emp;
2583 END IF;
2584
2585 ln_step := 5;
2586
2587 EXCEPTION
2588 WHEN OTHERS THEN
2589 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2590 gv_package || lv_procedure_name;
2591
2592 hr_utility.trace(lv_error_message || '-' || SQLERRM);
2593
2594 lv_error_message :=
2595 pay_emp_action_arch.set_error_message(lv_error_message);
2596
2597 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2598 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2599 hr_utility.raise_error;
2600
2601 END assignment_action_code;
2602
2603
2604 /************************************************************
2605 Name : initialization_code
2606 Purpose : This performs the context initialization.
2607 Arguments :
2608 Notes :
2609 ************************************************************/
2610
2611 PROCEDURE initialization_code(p_payroll_action_id IN NUMBER) IS
2612
2613 CURSOR cur_def_bal IS
2614 SELECT NVL(pbtl.reporting_name, pbtl.balance_name) reporting_name,
2615 pbtl.balance_type_id,
2616 pbad.attribute_name
2617 FROM pay_bal_attribute_definitions pbad,
2618 pay_balance_attributes pba,
2619 pay_defined_balances pdb,
2620 pay_balance_types_tl pbtl
2621 WHERE pbad.attribute_name IN ('Employee Taxes',
2622 'Tax Calculation Details'
2623 )
2624 AND pbad.legislation_code = 'MX'
2625 AND pba.attribute_id = pbad.attribute_id
2626 AND pdb.defined_balance_id = pba.defined_balance_id
2627 AND pbtl.balance_type_id = pdb.balance_type_id
2628 AND pbtl.language = USERENV('LANG')
2629 UNION
2630 SELECT balance_name reporting_name,
2631 balance_type_id,
2632 'SUMMARY'
2633 FROM pay_balance_types
2634 WHERE balance_name IN ('Gross Earnings',
2635 'Tax Deductions',
2636 'Deductions',
2637 'Net Pay')
2638 AND legislation_code = 'MX';
2639
2640
2641 CURSOR cur_sepchk_run_type IS
2642 SELECT prt.run_type_id,
2643 prt.shortname
2644 FROM pay_run_types_f prt
2645 WHERE prt.run_method = 'S'
2646 AND prt.legislation_code = 'MX';
2647
2648 CURSOR cur_bal_type IS
2649 SELECT balance_name,
2650 balance_type_id
2651 FROM pay_balance_types
2652 WHERE legislation_code = 'MX'
2653 AND balance_name = 'Gross Earnings';
2654 --IN ( 'Gross Earnings', 'Total Pay' );
2655
2656
2657 ln_pymt_def_bal_id NUMBER;
2658 ln_gre_ytd_def_bal_id NUMBER;
2659 lv_reporting_level VARCHAR2(30);
2660 lv_pymt_dimension VARCHAR2(100);
2661 ln_run_def_bal_id NUMBER;
2662
2663 lv_error_message VARCHAR2(500);
2664 lv_procedure_name VARCHAR2(100);
2665 ln_step NUMBER;
2666
2667 ln_run_bal_type_id NUMBER;
2668 lv_balance_name VARCHAR2(100);
2669 lv_bal_category pay_balance_categories_f.category_name%TYPE;
2670 ln_sep_chk_run_type_id NUMBER;
2671 lv_shortname pay_run_types_f.shortname%TYPE;
2672
2673 i NUMBER;
2674 j NUMBER;
2675
2676 BEGIN
2677 lv_procedure_name := '.initialization_code';
2678 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2679 pay_emp_action_arch.gv_error_message := NULL;
2680 lv_reporting_level := Null;
2681 i := 0;
2682 j := 0;
2683
2684 ln_step := 6;
2685 OPEN cur_bal_type;
2686 LOOP
2687 FETCH cur_bal_type INTO lv_balance_name, ln_run_bal_type_id;
2688 EXIT WHEN cur_bal_type%NOTFOUND;
2689 -- IF lv_balance_name = 'Total Pay' THEN
2690 -- gn_payments_def_bal_id :=
2691 -- NVL(pay_emp_action_arch.get_defined_balance_id(
2692 -- ln_run_bal_type_id,
2693 -- '_ASG_GRE_RUN',
2694 -- 'MX'),-1);
2695 -- ELSE
2696 gn_gross_earn_def_bal_id :=
2697 NVL(pay_emp_action_arch.get_defined_balance_id(
2698 ln_run_bal_type_id,
2699 '_ASG_GRE_RUN',
2700 'MX'),-1);
2701 -- END IF;
2702 END LOOP;
2703 CLOSE cur_bal_type;
2704
2705 ln_step := 10;
2706 OPEN cur_sepchk_run_type;
2707 LOOP
2708 FETCH cur_sepchk_run_type INTO ln_sep_chk_run_type_id, lv_shortname;
2709 EXIT WHEN cur_sepchk_run_type%NOTFOUND;
2710
2711 IF lv_shortname = 'REG_SEPPAY' THEN
2712 gn_sepchk_run_type_id := ln_sep_chk_run_type_id;
2713
2714 ELSIF lv_shortname = 'NP_SEPPAY' THEN
2715 gn_np_sepchk_run_type_id := ln_sep_chk_run_type_id;
2716
2717 END IF;
2718 END LOOP;
2719
2720 ln_step := 20;
2721 IF pay_emp_action_arch.gv_multi_leg_rule IS NULL THEN
2722 pay_emp_action_arch.gv_multi_leg_rule
2723 := pay_emp_action_arch.get_multi_legislative_rule('MX');
2724 END IF;
2725
2726 hr_utility.trace('lv_reporting_level : '|| lv_reporting_level);
2727 hr_utility.trace('gv_multi_leg_rule : ' || pay_emp_action_arch.gv_multi_leg_rule);
2728 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2729
2730 ln_step := 30;
2731 IF pay_emp_action_arch.gv_multi_leg_rule = 'Y' THEN
2732 lv_pymt_dimension := '_ASG_PAYMENTS';
2733 -- lv_jd_pymt_dimension := '_ASG_PAYMENTS_JD';
2734 ELSE
2735 lv_pymt_dimension := '_PAYMENTS';
2736 -- lv_jd_pymt_dimension := '_PAYMENTS_JD';
2737 END IF;
2738
2739 ln_step := 40;
2740 dbt.delete;
2741 i := 0;
2742
2743 ln_step := 50;
2744 FOR c_dbt IN cur_def_bal LOOP
2745
2746 ln_pymt_def_bal_id := 0;
2747 ln_gre_ytd_def_bal_id := 0;
2748 ln_run_def_bal_id := 0;
2749
2750
2751 ln_step := 60;
2752 ln_pymt_def_bal_id :=
2753 pay_emp_action_arch.get_defined_balance_id(
2754 c_dbt.balance_type_id,
2755 lv_pymt_dimension,
2756 'MX');
2757
2758 ln_step := 70;
2759 ln_gre_ytd_def_bal_id :=
2760 pay_emp_action_arch.get_defined_balance_id(
2761 c_dbt.balance_type_id,
2762 '_ASG_GRE_YTD',
2763 'MX');
2764
2765 ln_step := 80;
2766 ln_run_def_bal_id :=
2767 pay_emp_action_arch.get_defined_balance_id(
2768 c_dbt.balance_type_id,
2769 '_ASG_GRE_RUN',
2770 'MX');
2771
2772 ln_step := 140;
2773
2774 IF c_dbt.attribute_name = 'Employee Taxes' THEN
2775
2776 dbt(i).act_info_category := 'AC DEDUCTIONS';
2777
2778 ELSIF c_dbt.attribute_name = 'Tax Calculation Details' THEN
2779
2780 dbt(i).act_info_category := 'MX TAX CALCULATION DETAILS';
2781
2782 ELSIF c_dbt.attribute_name = 'SUMMARY' THEN
2783
2784 dbt(i).act_info_category := 'MX SUMMARY';
2785
2786 END IF;
2787
2788 dbt(i).bal_name := c_dbt.reporting_name;
2789 dbt(i).bal_type_id := c_dbt.balance_type_id;
2790 dbt(i).pymt_def_bal_id := ln_pymt_def_bal_id;
2791 dbt(i).gre_ytd_def_bal_id := ln_gre_ytd_def_bal_id;
2792 dbt(i).run_def_bal_id := ln_run_def_bal_id;
2793
2794 dbt(i).jurisdiction_cd := NULL;
2795 i := i + 1;
2796
2797 END LOOP;
2798
2799 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2800 i := 0;
2801
2802 ln_step := 160;
2803 FOR i IN dbt.first..dbt.last LOOP
2804 hr_utility.trace(dbt(i).act_info_category);
2805 hr_utility.trace(dbt(i).bal_name);
2806 hr_utility.trace(dbt(i).bal_type_id);
2807 hr_utility.trace(dbt(i).pymt_def_bal_id);
2808 hr_utility.trace(dbt(i).gre_ytd_def_bal_id);
2809 hr_utility.trace(dbt(i).run_def_bal_id);
2810 hr_utility.trace(dbt(i).jurisdiction_cd);
2811 END LOOP;
2812
2813 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2814
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817 hr_utility.set_location(gv_package || lv_procedure_name, 500);
2818 lv_error_message := 'Error at step ' || ln_step ||
2819 ' IN ' || gv_package || lv_procedure_name;
2820 hr_utility.trace(lv_error_message || '-' || SQLERRM);
2821
2822 lv_error_message :=
2823 pay_emp_action_arch.set_error_message(lv_error_message);
2824
2825 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2826 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2827 hr_utility.raise_error;
2828
2829 END initialization_code;
2830
2831
2832 PROCEDURE populate_tax_and_summary( p_xfr_action_id IN NUMBER
2833 ,p_assignment_id IN NUMBER
2834 ,p_pymt_balcall_aaid IN NUMBER
2835 ,p_tax_unit_id IN NUMBER
2836 ,p_action_type IN VARCHAR2
2837 ,p_pymt_eff_date IN DATE
2838 ,p_start_date IN DATE
2839 ,p_end_date IN DATE
2840 ,p_ytd_balcall_aaid IN NUMBER
2841 )
2842 IS
2843
2844 ln_pymt_amount NUMBER;
2845 ln_ytd_amount NUMBER;
2846 lv_reporting_name VARCHAR2(150);
2847 lv_lookup_code VARCHAR2(150);
2848
2849 ln_gross_earnings NUMBER := 0;
2850 ln_tax_deductions NUMBER := 0;
2851 ln_deductions NUMBER := 0;
2852 ln_net_pay NUMBER := 0;
2853
2854 ln_ytd_gross_earnings NUMBER := 0;
2855 ln_ytd_tax_deductions NUMBER := 0;
2856 ln_ytd_deductions NUMBER := 0;
2857 ln_ytd_net_pay NUMBER := 0;
2858
2859
2860 i NUMBER;
2861 j NUMBER;
2862
2863 ln_index NUMBER;
2864 ln_element_index NUMBER;
2865
2866 lv_error_message VARCHAR2(500);
2867 lv_procedure_name VARCHAR2(100);
2868 ln_step NUMBER;
2869
2870 BEGIN
2871 ln_step := 1;
2872 lv_procedure_name := '.populate_tax_and_summary';
2873 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2874 i := 0;
2875 j := 0;
2876
2877 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
2878
2879 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2880 ln_step := 2;
2881 FOR i IN dbt.first..dbt.last LOOP
2882
2883 ln_pymt_amount := 0;
2884 ln_ytd_amount := 0;
2885
2886 IF p_pymt_balcall_aaid <> -999 THEN
2887
2888 IF p_action_type IN ('V','B') THEN
2889 IF dbt(i).run_def_bal_id IS NOT NULL THEN
2890 ln_step := 5;
2891 ln_pymt_amount := NVL(pay_balance_pkg.get_value(
2892 dbt(i).run_def_bal_id,
2893 p_pymt_balcall_aaid),0);
2894 END IF;
2895 ELSE
2896 IF dbt(i).pymt_def_bal_id IS NOT NULL THEN
2897 ln_step := 5;
2898 ln_pymt_amount := NVL(pay_balance_pkg.get_value(
2899 dbt(i).pymt_def_bal_id,
2900 p_pymt_balcall_aaid),0);
2901 END IF;
2902 END IF; -- p_action_type = 'V'
2903
2904 ELSE
2905
2906 ln_pymt_amount := 0;
2907
2908 END IF; -- p_pymt_balcall_aaid <> -999
2909
2910 ln_step := 7;
2911 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
2912 dbt(i).gre_ytd_def_bal_id,
2913 p_ytd_balcall_aaid),0);
2914
2915 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2916 ln_step := 8;
2917
2918 IF dbt(i).act_info_category = 'MX SUMMARY' THEN
2919
2920 IF dbt(i).bal_name = 'Gross Earnings' THEN
2921
2922 ln_gross_earnings := ln_pymt_amount;
2923 ln_ytd_gross_earnings := ln_ytd_amount;
2924
2925 ELSIF dbt(i).bal_name = 'Tax Deductions' THEN
2926
2927 ln_tax_deductions := ln_pymt_amount;
2928 ln_ytd_tax_deductions := ln_ytd_amount;
2929
2930 ELSIF dbt(i).bal_name = 'Deductions' THEN
2931
2932 ln_deductions := ln_pymt_amount;
2933 ln_ytd_deductions := ln_ytd_amount;
2934
2935 ELSIF dbt(i).bal_name = 'Net Pay' THEN
2936
2937 ln_net_pay := ln_pymt_amount;
2938 ln_ytd_net_pay := ln_ytd_amount;
2939
2940 END IF;
2941
2942 ELSIF ( ln_pymt_amount + ln_ytd_amount <> 0 ) THEN
2943
2944 hr_utility.trace('lv_lookup_code : '||lv_lookup_code);
2945 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2946
2947
2948 lv_reporting_name := dbt(i).bal_name; -- MX specific
2949
2950 /*Insert this into the plsql table */
2951 -- hr_utility.trace('Tax Balance Name : '|| dbt(i).bal_name );
2952 hr_utility.trace('lv_reporting_name : '||lv_reporting_name);
2953 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2954
2955 ln_step := 10;
2956 ln_index := pay_ac_action_arch.lrr_act_tab.count;
2957
2958 hr_utility.trace('ln_index IS '
2959 || pay_ac_action_arch.lrr_act_tab.count);
2960
2961 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
2962 := dbt(i).act_info_category;
2963 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2964 := dbt(i).jurisdiction_cd;
2965 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
2966 := p_xfr_action_id;
2967 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
2968 := dbt(i).bal_type_id;
2969 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
2970 := fnd_number.number_to_canonical(ln_pymt_amount);
2971 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
2972 := fnd_number.number_to_canonical(ln_ytd_amount);
2973 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
2974 := lv_reporting_name;
2975
2976 IF dbt(i).act_info_category = 'AC DEDUCTIONS' THEN
2977 pay_ac_action_arch.lrr_act_tab(ln_index).act_info16
2978 := 'Employee Taxes';
2979
2980 END IF;
2981
2982
2983 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2984
2985 ln_step := 11;
2986 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
2987 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
2988 := dbt(i).jurisdiction_cd;
2989 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
2990 := dbt(i).bal_name;
2991 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
2992 := dbt(i).bal_type_id;
2993
2994 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2995
2996
2997 END IF;
2998
2999 END LOOP;
3000
3001 IF p_pymt_balcall_aaid <> -999 THEN
3002
3003 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3004
3005 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3006 := 'MX SUMMARY CURRENT';
3007 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
3008 := fnd_number.number_to_canonical(ln_gross_earnings);
3009 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
3010 := fnd_number.number_to_canonical(ln_tax_deductions);
3011 pay_ac_action_arch.lrr_act_tab(ln_index).act_info3
3012 := fnd_number.number_to_canonical(ln_deductions);
3013 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5 -- Bug 4155512
3014 := fnd_number.number_to_canonical(ln_net_pay);
3015
3016 END IF;
3017
3018 hr_utility.set_location(gv_package || lv_procedure_name, 75);
3019
3020 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3021
3022 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3023 := 'MX SUMMARY YTD';
3024 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
3025 := fnd_number.number_to_canonical(ln_ytd_gross_earnings);
3026 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
3027 := fnd_number.number_to_canonical(ln_ytd_tax_deductions);
3028 pay_ac_action_arch.lrr_act_tab(ln_index).act_info3
3029 := fnd_number.number_to_canonical(ln_ytd_deductions);
3030 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5 -- Bug 4155512
3031 := fnd_number.number_to_canonical(ln_ytd_net_pay);
3032
3033
3034 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3035
3036 EXCEPTION
3037 WHEN OTHERS THEN
3038 hr_utility.set_location(gv_package || lv_procedure_name, 500);
3039 lv_error_message := 'Error at step ' || ln_step ||
3040 ' IN ' || gv_package || lv_procedure_name;
3041 hr_utility.trace(lv_error_message || '-' || SQLERRM);
3042
3043 lv_error_message :=
3044 pay_emp_action_arch.set_error_message(lv_error_message);
3045
3046 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3047 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3048 hr_utility.raise_error;
3049
3050 END populate_tax_and_summary;
3051
3052
3053
3054 /*********************************************************************
3055 Name : update_employee_information
3056 Purpose : This function updates the Employee Information, which is
3057 archived by the global archive procedure.
3058 The employee name and Legal Employer ID are updated. The
3059 Organization ID segment will be updated to hold the
3060 organization_id of the Legal Employer. The Global package
3061 archives the full name for the employee. This procedure
3062 will update the name to
3063
3064 Paternal Last Name[space]
3065 Maternal Last Name[space]
3066 First Name[space]
3067 Second Name
3068 Arguments : IN
3069 p_assignment_action_id NUMBER;
3070 p_action_context_id NUMBER;
3071 Notes :
3072 *********************************************************************/
3073 PROCEDURE update_employee_information(
3074 p_action_context_id IN NUMBER
3075 ,p_assignment_id IN NUMBER)
3076 IS
3077 CURSOR c_get_archive_info(cp_action_context_id IN NUMBER
3078 ,cp_assignment_id IN NUMBER) IS
3079 SELECT action_information_id, effective_date,
3080 object_version_number,
3081 tax_unit_id
3082 FROM pay_action_information
3083 WHERE action_context_id = cp_action_context_id
3084 AND action_context_type = 'AAP'
3085 AND assignment_id = cp_assignment_id
3086 AND action_information_category = 'EMPLOYEE DETAILS';
3087
3088 CURSOR c_get_employee_info(cp_assignment_id IN NUMBER
3089 ,cp_effective_date IN DATE) IS
3090 SELECT LTRIM(RTRIM(
3091 DECODE(last_name, NULL, '', ' ' || last_name)
3092 || DECODE(per_information1, NULL,'',' ' || per_information1)
3093 || DECODE(first_name,NULL, '', ' ' || first_name)
3094 || DECODE(middle_names,NULL, '', ' ' || middle_names)
3095 ))
3096 FROM per_people_f ppf
3097 WHERE ppf.person_id =
3098 (SELECT person_id FROM per_assignments_f paf
3099 WHERE assignment_id = cp_assignment_id
3100 AND cp_effective_date BETWEEN paf.effective_start_date
3101 AND paf.effective_end_date)
3102 AND cp_effective_date BETWEEN ppf.effective_start_date
3103 AND ppf.effective_end_date;
3104
3105 ln_action_information_id NUMBER;
3106 ld_effective_date DATE;
3107
3108 lv_employee_name VARCHAR2(300);
3109
3110 ln_ovn NUMBER;
3111 lv_procedure_name VARCHAR2(200);
3112 lv_error_message VARCHAR2(200);
3113 ln_tax_unit_id NUMBER;
3114 ln_business_group_id NUMBER;
3115 ln_legal_employer_id NUMBER;
3116
3117 BEGIN
3118 lv_procedure_name := '.update_employee_information';
3119
3120 hr_utility.trace('Action_Context_ID = ' || p_action_context_id);
3121 hr_utility.trace('Asg ID = ' || p_assignment_id);
3122 OPEN c_get_archive_info(p_action_context_id, p_assignment_id);
3123 LOOP
3124 FETCH c_get_archive_info INTO ln_action_information_id,
3125 ld_effective_date,
3126 ln_ovn,
3127 ln_tax_unit_id;
3128 IF c_get_archive_info%NOTFOUND THEN
3129 EXIT;
3130 END IF;
3131
3132 ln_business_group_id :=
3133 hr_mx_utility.get_bg_from_assignment(p_assignment_id);
3134
3135 ln_legal_employer_id :=
3136 hr_mx_utility.get_legal_employer(ln_business_group_id,
3137 ln_tax_unit_id);
3138 hr_utility.trace('ln_legal_employer_id = ' || ln_legal_employer_id);
3139
3140
3141 hr_utility.trace('Action_info_id = ' || ln_action_information_id);
3142 hr_utility.trace('ld_eff_date = ' ||
3143 fnd_date.date_to_canonical(ld_effective_date));
3144
3145 OPEN c_get_employee_info(p_assignment_id, ld_effective_date);
3146 FETCH c_get_employee_info INTO lv_employee_name;
3147 CLOSE c_get_employee_info;
3148
3149 hr_utility.trace('lv_employee_name = *' || lv_employee_name ||'*');
3150
3151 pay_action_information_api.update_action_information
3152 (p_action_information_id => ln_action_information_id
3153 ,p_object_version_number => ln_ovn
3154 ,p_action_information1 => lv_employee_name
3155 ,p_action_information2 => ln_legal_employer_id
3156 );
3157
3158 END LOOP;
3159 CLOSE c_get_archive_info;
3160
3161 EXCEPTION
3162 WHEN OTHERS THEN
3163 lv_error_message := 'Error IN ' ||
3164 gv_package || lv_procedure_name;
3165
3166 hr_utility.trace(lv_error_message || '-' || SQLERRM);
3167
3168 lv_error_message :=
3169 pay_emp_action_arch.set_error_message(lv_error_message);
3170
3171 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3172 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3173 hr_utility.raise_error;
3174
3175 END update_employee_information;
3176
3177 /**********************************************************************
3178 ** Procedure get_mx_personal_information populates
3179 ** pay_emp_action_arch.lrr_act_tab with the following
3180 ** action_information_contexts :
3181 **
3182 ** MX EMPLOYEE DETAILS
3183 **
3184 ** It expects the following in parameters:
3185 **
3186 ** #1 p_payroll_action_id : payroll_action_id of the Archive process
3187 ** #2 p_assactid : assignment action id of the Archive process
3188 ** #3 p_assignment_id : assignment_id
3189 ** #4 p_curr_pymt_ass_act_id : 'P','U' action which is locked by the
3190 ** Archive process.
3191 ** #5 p_curr_eff_date : Effective Date of the Archive Process
3192 ** #6 p_date_earned : This is the date_earned for the Run
3193 ** Process.
3194 ** #7 p_curr_pymt_eff_date : The effective date of prepayments.
3195 ** #8 p_tax_unit_id : tax_unit_id from pay_assignment_actions
3196 ** #9 p_time_period_id : Time Period Id of the Run.
3197 ** #10 p_ppp_source_action_id : This is the source_action_id of
3198 ** pay_pre_payments for the 'P','U' action.
3199 ** #11 p_ytd_balcall_aaid : This is the assignment action id to call
3200 ** balances other than ASG_PAYMENTS for Employee
3201 ** other information.
3202 **********************************************************************/
3203
3204 PROCEDURE get_mx_personal_information(
3205 p_payroll_action_id IN NUMBER
3206 ,p_assactid IN NUMBER
3207 ,p_assignment_id IN NUMBER
3208 ,p_curr_pymt_ass_act_id IN NUMBER
3209 ,p_curr_eff_date IN DATE
3210 ,p_date_earned IN DATE
3211 ,p_curr_pymt_eff_date IN DATE
3212 ,p_tax_unit_id IN NUMBER
3213 ,p_time_period_id IN NUMBER
3214 ,p_ppp_source_action_id IN NUMBER
3215 ,p_run_action_id IN NUMBER
3216 ,p_ytd_balcall_aaid IN NUMBER DEFAULT NULL
3217 )
3218 IS
3219 CURSOR c_employee_details(cp_assignment_id IN NUMBER
3220 , cp_curr_eff_date IN DATE
3221 ) IS
3222 SELECT ppf.per_information2 rfc_id,
3223 ppf.per_information3 ss_id
3224 FROM per_assignments_f paf,
3225 per_people_f ppf
3226 WHERE paf.person_id = ppf.person_id
3227 AND paf.assignment_id = cp_assignment_id
3228 AND cp_curr_eff_date BETWEEN paf.effective_start_date
3229 AND paf.effective_end_date
3230 AND cp_curr_eff_date BETWEEN ppf.effective_start_date
3231 AND ppf.effective_end_date;
3232
3233
3234 CURSOR c_get_legal_er_details(cp_legal_er_id NUMBER)
3235 IS
3236 SELECT org_information1 "Employer Name",
3237 org_information2 "Employer RFC ID"
3238 FROM hr_organization_information
3239 WHERE organization_id = cp_legal_er_id
3240 AND org_information_context = 'MX_TAX_REGISTRATION';
3241
3242 CURSOR c_get_er_ss_id
3243 IS
3244 SELECT org_information1 "Employer Social Security ID"
3245 FROM hr_organization_information
3246 WHERE organization_id = p_tax_unit_id
3247 AND org_information_context = 'MX_SOC_SEC_DETAILS';
3248
3249 ln_index NUMBER;
3250 lv_ee_rfc_id per_all_people_f.per_information2%TYPE;
3251 lv_ee_ss_id per_all_people_f.per_information3%TYPE;
3252 ln_business_group_id NUMBER;
3253 ln_legal_employer_id NUMBER;
3254 lv_er_rfc_id hr_organization_information.org_information1%TYPE;
3255 lv_er_ss_id hr_organization_information.org_information1%TYPE;
3256 lv_legal_employer_name hr_all_organization_units.name%TYPE;
3257 lv_gre_name hr_all_organization_units.name%TYPE;
3258 ld_date_start DATE;
3259
3260 lv_procedure_name VARCHAR2(100);
3261 ln_step NUMBER;
3262 lv_error_message VARCHAR2(200);
3263 lv_exists VARCHAR2(1);
3264 ln_index1 NUMBER;
3265
3266 ln_total_idw NUMBER;
3267 ln_fixed_idw NUMBER;
3268 ln_variable_idw NUMBER;
3269
3270 BEGIN
3271 lv_procedure_name := 'get_mx_personal_information';
3272 lv_exists := 'N';
3273
3274 hr_utility.trace('Entered get_mx_personal_information');
3275 ln_step := 1;
3276 pay_emp_action_arch.initialization_process;
3277
3278 hr_utility.trace('p_assactid = ' || p_assactid);--
3279 hr_utility.trace('p_assignment_id = ' || p_assignment_id);--
3280 hr_utility.trace('p_curr_pymt_ass_act_id = ' || p_curr_pymt_ass_act_id);
3281 hr_utility.trace('p_curr_eff_date = ' || p_curr_eff_date);--
3282 hr_utility.trace('p_date_earned = ' || p_date_earned);
3283 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);--
3284 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);--
3285 hr_utility.trace('p_time_period_id = ' || p_time_period_id);
3286 hr_utility.trace('p_run_action_id = ' || p_run_action_id);
3287
3288 OPEN c_employee_details(p_assignment_id,p_curr_eff_date);
3289 ln_step := 2;
3290 FETCH c_employee_details INTO lv_ee_rfc_id,
3291 lv_ee_ss_id;
3292
3293 IF c_employee_details%NOTFOUND THEN
3294 hr_utility.raise_error;
3295 END IF;
3296
3297 hr_utility.trace('lv_ee_rfc_id = ' || lv_ee_rfc_id);
3298 hr_utility.trace('lv_ee_ss_id = ' || lv_ee_ss_id);
3299
3300 CLOSE c_employee_details;
3301
3302 ln_step := 3;
3303 ln_total_idw := pay_mx_ff_udfs.get_idw(p_assignment_id,
3304 p_tax_unit_id,
3305 p_curr_pymt_eff_date,
3306 'REPORT',
3307 ln_fixed_idw,
3308 ln_variable_idw);
3309
3310 --
3311 ln_index := pay_emp_action_arch.lrr_act_tab.count;
3312
3313 hr_utility.trace('ln_index IN get_mx_personal_information proc IS '
3314 || pay_emp_action_arch.lrr_act_tab.count);
3315
3316 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
3317 := 'MX EMPLOYEE DETAILS';
3318 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
3319 := lv_ee_ss_id;
3320 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
3321 := lv_ee_rfc_id;
3322 pay_emp_action_arch.lrr_act_tab(ln_index).act_info3
3323 := ln_total_idw;
3324
3325
3326 IF pay_emp_action_arch.lrr_act_tab.count > 0 THEN
3327 ln_step := 4;
3328 pay_emp_action_arch.insert_rows_thro_api_process(
3329 p_action_context_id => p_assactid
3330 ,p_action_context_type => 'AAP'
3331 ,p_assignment_id => p_assignment_id
3332 ,p_tax_unit_id => p_tax_unit_id
3333 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3334 ,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
3335 );
3336 END IF;
3337
3338 EXCEPTION
3339 WHEN OTHERS THEN
3340 lv_error_message := 'Error IN step ' ||ln_step|| ' of '||
3341 gv_package || lv_procedure_name;
3342
3343 hr_utility.trace(lv_error_message || '-' || SQLERRM);
3344
3345 lv_error_message :=
3346 pay_emp_action_arch.set_error_message(lv_error_message);
3347
3348 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3349 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3350 hr_utility.raise_error;
3351
3352 END get_mx_personal_information;
3353
3354 /******************************************************************
3355 Name : populate_summary
3356 Purpose : This procedure adds the values for Gross Earnings,
3357 Taxes and Deductions; and inserts two rows for
3358 CURRENT and YTD Summary.
3359 Arguments :
3360 Notes :
3361 ******************************************************************/
3362 /* PROCEDURE populate_summary(p_xfr_action_id IN NUMBER)
3363 IS
3364 lv_gross_earnings VARCHAR2(80):= 0;
3365 lv_imputed_earnings VARCHAR2(80):= 0;
3366 lv_deductions VARCHAR2(80):= 0;
3367 lv_tax_deductions VARCHAR2(80):= 0;
3368
3369 lv_ytd_gross_earnings VARCHAR2(80):= 0;
3370 lv_ytd_deductions VARCHAR2(80):= 0;
3371 lv_ytd_tax_deductions VARCHAR2(80):= 0;
3372 lv_ytd_imputed_earnings VARCHAR2(80):= 0;
3373
3374 ln_index NUMBER;
3375 lv_procedure_name VARCHAR2(100);
3376 lv_error_message VARCHAR2(200);
3377 ln_step NUMBER;
3378
3379 j NUMBER := 0;
3380
3381
3382 BEGIN
3383 lv_procedure_name := '.populate_summary';
3384 ln_step := 1;
3385 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3386 IF pay_ac_action_arch.lrr_act_tab.count > 0 THEN
3387 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3388
3389 ln_step := 2;
3390 FOR i IN pay_ac_action_arch.lrr_act_tab.first ..
3391 pay_ac_action_arch.lrr_act_tab.last LOOP
3392
3393 IF pay_ac_action_arch.lrr_act_tab(i).action_context_id
3394 = p_xfr_action_id THEN
3395 IF pay_ac_action_arch.lrr_act_tab(i).action_info_category
3396 IN ('AC EARNINGS', 'AC DEDUCTIONS') THEN
3397
3398 IF pay_ac_action_arch.lrr_act_tab(i).act_info16
3399 IN ('Employee Earnings',
3400 'Hourly Earnings',
3401 'Taxable Benefits') THEN
3402
3403 -- Bug 4168970 - Imputed Earnings summed up separately
3404 -- to be used in calculating Net Pay.
3405 --
3406 IF pay_ac_action_arch.lrr_act_tab(i).act_info16
3407 = 'Taxable Benefits' THEN
3408
3409 hr_utility.set_location(gv_package || lv_procedure_name, 25);
3410 lv_imputed_earnings :=
3411 lv_imputed_earnings +
3412 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info8,0);
3413
3414 lv_ytd_imputed_earnings :=
3415 lv_ytd_imputed_earnings +
3416 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info9,0);
3417
3418 END IF;
3419
3420 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3421 ln_step := 3;
3422 lv_gross_earnings
3423 := lv_gross_earnings +
3424 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info8,0);
3425 lv_ytd_gross_earnings
3426 := lv_ytd_gross_earnings +
3427 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info9,0);
3428 ELSIF pay_ac_action_arch.lrr_act_tab(i).act_info16
3429 = 'Employee Taxes' THEN
3430 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3431 ln_step := 4;
3432 lv_tax_deductions
3433 := lv_tax_deductions +
3434 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info8,0);
3435 lv_ytd_tax_deductions
3436 := lv_ytd_tax_deductions +
3437 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info9,0);
3438 ELSIF pay_ac_action_arch.lrr_act_tab(i).act_info16
3439 = 'Deductions' THEN
3440 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3441 ln_step := 5;
3442 lv_deductions
3443 := lv_deductions +
3444 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info8,0);
3445 lv_ytd_deductions
3446 := lv_ytd_deductions +
3447 NVL(pay_ac_action_arch.lrr_act_tab(i).act_info9,0);
3448 END IF;
3449
3450 END IF;
3451 END IF;
3452 END LOOP;
3453 END IF;
3454
3455 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3456 -- Insert one row for CURRENT and one for YTD
3457 IF pay_ac_action_arch.lrr_act_tab.count > 0 THEN
3458 ln_step := 6;
3459 -- CURRENT
3460 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3461 hr_utility.trace('ln_index = ' || ln_index);
3462 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3463 := 'MX SUMMARY CURRENT';
3464 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
3465 := fnd_number.number_to_canonical(lv_gross_earnings);
3466 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
3467 := fnd_number.number_to_canonical(lv_tax_deductions) ;
3468 pay_ac_action_arch.lrr_act_tab(ln_index).act_info3
3469 := fnd_number.number_to_canonical(lv_deductions);
3470 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5 -- Bug 4155512
3471 := fnd_number.number_to_canonical(lv_gross_earnings -
3472 -- Bug 4168970
3473 lv_imputed_earnings -
3474 lv_tax_deductions -
3475 lv_deductions);
3476
3477
3478 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3479 -- YTD
3480 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3481 hr_utility.trace('ln_index = ' || ln_index);
3482 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3483 := 'MX SUMMARY YTD';
3484 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
3485 := fnd_number.number_to_canonical(lv_ytd_gross_earnings);
3486 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
3487 := fnd_number.number_to_canonical(lv_ytd_tax_deductions) ;
3488 pay_ac_action_arch.lrr_act_tab(ln_index).act_info3
3489 := fnd_number.number_to_canonical(lv_ytd_deductions);
3490 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5 -- Bug 4155512
3491 := fnd_number.number_to_canonical(lv_ytd_gross_earnings -
3492 -- Bug 4168970
3493 lv_ytd_imputed_earnings -
3494 lv_ytd_tax_deductions -
3495 lv_ytd_deductions);
3496
3497 END IF;
3498
3499 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3500 ln_step := 10;
3501
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504
3505 lv_error_message := 'Error at step ' || ln_step ||
3506 ' in ' || gv_package || lv_procedure_name;
3507
3508 hr_utility.trace(lv_error_message || '-' || SQLERRM);
3509
3510 lv_error_message :=
3511 pay_emp_action_arch.set_error_message(lv_error_message);
3512
3513 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3514 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3515 hr_utility.raise_error;
3516
3517 END populate_summary;
3518 */
3519
3520 /************************************************************
3521 Name : process_actions
3522 Purpose :
3523 Arguments : p_rqp_action_id - For Child actions we pass the
3524 Action ID of Run/Quick Pay
3525 - For Master we pass the Action ID
3526 of Pre Payment Process.
3527 Notes :
3528 ************************************************************/
3529 PROCEDURE process_actions( p_xfr_payroll_action_id IN NUMBER
3530 ,p_xfr_action_id IN NUMBER
3531 ,p_pre_pay_action_id IN NUMBER
3532 ,p_payment_action_id IN NUMBER
3533 ,p_rqp_action_id IN NUMBER
3534 ,p_seperate_check_flag IN VARCHAR2
3535 ,p_action_type IN VARCHAR2
3536 ,p_legislation_code IN VARCHAR2
3537 ,p_assignment_id IN NUMBER
3538 ,p_tax_unit_id IN NUMBER
3539 ,p_curr_pymt_eff_date IN DATE
3540 ,p_xfr_start_date IN DATE
3541 ,p_xfr_end_date IN DATE
3542 ,p_ppp_source_action_id IN NUMBER DEFAULT NULL
3543 ,p_archive_balance_info IN VARCHAR2
3544 )
3545 IS
3546
3547 CURSOR c_ytd_aaid(cp_prepayment_action_id IN NUMBER
3548 ,cp_assignment_id IN NUMBER) IS
3549 SELECT paa.assignment_action_id
3550 FROM pay_assignment_actions paa,
3551 pay_action_interlocks pai,
3552 pay_payroll_actions ppa
3553 WHERE pai.locking_action_id = cp_prepayment_action_id
3554 AND paa.assignment_action_id = pai.locked_action_id
3555 AND paa.assignment_id = cp_assignment_id
3556 AND ppa.payroll_action_id = paa.payroll_action_id
3557 AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
3558 gn_np_sepchk_run_type_id)
3559 ORDER BY paa.assignment_action_id DESC;
3560
3561 CURSOR c_time_period(cp_run_assignment_action IN NUMBER) IS
3562 SELECT ptp.time_period_id,
3563 ppa.date_earned,
3564 ppa.effective_date
3565 FROM pay_assignment_actions paa,
3566 pay_payroll_actions ppa,
3567 per_time_periods ptp
3568 WHERE paa.assignment_action_id = cp_run_assignment_action
3569 AND ppa.payroll_action_id = paa.payroll_action_id
3570 AND ptp.payroll_id = ppa.payroll_id
3571 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3572
3573 CURSOR c_chk_act_type(cp_last_xfr_act_id NUMBER) IS
3574 SELECT SUBSTR(serial_number,1,1)
3575 FROM pay_assignment_actions paa
3576 WHERE paa.assignment_action_id = cp_last_xfr_act_id;
3577
3578 lv_pre_xfr_act_type VARCHAR2(80);
3579
3580 ln_run_action_id NUMBER;
3581 ln_ytd_balcall_aaid NUMBER;
3582 ld_run_date_earned DATE;
3583 ld_run_effective_date DATE;
3584
3585 ld_last_xfr_eff_date DATE;
3586 ln_last_xfr_action_id NUMBER;
3587 ld_last_pymt_eff_date DATE;
3588 ln_last_pymt_action_id NUMBER;
3589
3590 ln_time_period_id NUMBER;
3591 lv_resident_jurisdiction VARCHAR2(15);
3592
3593 lv_procedure_name VARCHAR2(100);
3594 lv_error_message VARCHAR2(200);
3595 ln_step NUMBER;
3596
3597 BEGIN
3598 lv_procedure_name := '.process_actions';
3599
3600 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3601 ln_step := 1;
3602 /****************************************************************
3603 ** For Seperate Check we do the YTD balance calls with the Run
3604 ** Action ID. So, we do not need to get the MAX. action which IS
3605 ** not seperate Check.
3606 ** Also, p_ppp_source_action_id is set to NULL as we want to get
3607 ** all records from pay_pre_payments where source_action_id is
3608 ** NULL.
3609 ****************************************************************/
3610 ln_ytd_balcall_aaid := p_payment_action_id;
3611 IF p_seperate_check_flag = 'N' AND
3612 p_action_type IN ('U', 'P') THEN
3613 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3614 ln_step := 2;
3615 OPEN c_ytd_aaid(p_rqp_action_id,
3616 p_assignment_id);
3617 FETCH c_ytd_aaid INTO ln_ytd_balcall_aaid;
3618 IF c_ytd_aaid%NOTFOUND THEN
3619 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3620 hr_utility.raise_error;
3621 END IF;
3622 CLOSE c_ytd_aaid;
3623 END IF;
3624
3625 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3626 ln_step := 3;
3627
3628 OPEN c_time_period(p_payment_action_id);
3629 FETCH c_time_period INTO ln_time_period_id,
3630 ld_run_date_earned,
3631 ld_run_effective_date;
3632 CLOSE c_time_period;
3633
3634 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3635 ln_step := 4;
3636 pay_ac_action_arch.get_last_xfr_info(
3637 p_assignment_id => p_assignment_id
3638 ,p_curr_effective_date => p_xfr_end_date
3639 ,p_action_info_category => 'EMPLOYEE DETAILS'
3640 ,p_xfr_action_id => p_xfr_action_id
3641 ,p_sepchk_flag => p_seperate_check_flag
3642 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
3643 ,p_last_xfr_action_id => ln_last_xfr_action_id
3644 );
3645
3646 IF ld_last_xfr_eff_date IS NOT NULL THEN
3647 IF gv_act_param_val IS NOT NULL THEN
3648 IF gv_act_param_val = 'Y'
3649 THEN
3650 ld_last_xfr_eff_date := NULL;
3651 ELSIF fnd_date.canonical_to_date(gv_act_param_val) = p_xfr_end_date
3652 THEN
3653 ld_last_xfr_eff_date := NULL;
3654 END IF;
3655 END IF;
3656 END IF;
3657
3658 IF ld_last_xfr_eff_date IS NOT NULL THEN
3659 ln_step := 5;
3660 OPEN c_chk_act_type(ln_last_xfr_action_id);
3661 FETCH c_chk_act_type INTO lv_pre_xfr_act_type;
3662 CLOSE c_chk_act_type;
3663
3664 IF lv_pre_xfr_act_type = 'B' THEN
3665 ld_last_xfr_eff_date := NULL;
3666 END IF;
3667 END IF;
3668
3669 hr_utility.trace('p_xfr_payroll_action_id= '|| p_xfr_payroll_action_id);
3670 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
3671 hr_utility.trace('p_seperate_check_flag = ' || p_seperate_check_flag);
3672 hr_utility.trace('p_action_type = ' || p_action_type);
3673 hr_utility.trace('p_pre_pay_action_id = ' || p_pre_pay_action_id);
3674 hr_utility.trace('p_payment_action_id = ' || p_payment_action_id);
3675 hr_utility.trace('p_rqp_action_id = ' || p_rqp_action_id);
3676 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
3677 hr_utility.trace('p_xfr_start_date = ' || p_xfr_start_date );
3678 hr_utility.trace('p_xfr_end_date = ' || p_xfr_end_date );
3679 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
3680 hr_utility.trace('ld_run_effective_date = ' || ld_run_effective_date);
3681 hr_utility.trace('ln_ytd_balcall_aaid = ' || ln_ytd_balcall_aaid);
3682 hr_utility.trace('p_ppp_source_action_id = '|| p_ppp_source_action_id);
3683 hr_utility.trace('ld_run_date_earned = ' || ld_run_date_earned);
3684 hr_utility.trace('ld_last_xfr_eff_date = ' || ld_last_xfr_eff_date);
3685 hr_utility.trace('ln_last_xfr_action_id = ' || ln_last_xfr_action_id);
3686
3687 ln_step := 6;
3688 pay_ac_action_arch.initialization_process;
3689
3690 IF p_archive_balance_info = 'Y' THEN
3691 ln_step := 7;
3692 populate_tax_and_summary( p_xfr_action_id => p_xfr_action_id
3693 ,p_assignment_id => p_assignment_id
3694 ,p_pymt_balcall_aaid => p_payment_action_id
3695 ,p_tax_unit_id => p_tax_unit_id
3696 ,p_action_type => p_action_type
3697 ,p_pymt_eff_date => p_curr_pymt_eff_date
3698 ,p_start_date => p_xfr_start_date
3699 ,p_end_date => p_xfr_end_date
3700 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3701 );
3702
3703
3704 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3705 ln_step := 8;
3706 /******************************************************************
3707 ** For seperate check cases, the ld_last_xfr_eff_date is never NULL
3708 ** as the master is always processed before the child actions. The
3709 ** master data is already in the archive table and as it is in the
3710 ** same session the process will always go to the ELSE statement
3711 ******************************************************************/
3712 IF ld_last_xfr_eff_date IS NULL THEN
3713 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3714 first_time_process(
3715 p_xfr_action_id => p_xfr_action_id
3716 ,p_assignment_id => p_assignment_id
3717 ,p_curr_pymt_action_id => p_rqp_action_id
3718 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3719 ,p_curr_eff_date => p_xfr_end_date
3720 ,p_tax_unit_id => p_tax_unit_id
3721 ,p_pymt_balcall_aaid => p_payment_action_id
3722 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3723 ,p_sepchk_flag => p_seperate_check_flag
3724 ,p_legislation_code => p_legislation_code
3725 );
3726
3727 ELSE
3728 ln_step := 9;
3729 pay_ac_action_arch.get_last_pymt_info(
3730 p_assignment_id => p_assignment_id
3731 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3732 ,p_last_pymt_eff_date => ld_last_pymt_eff_date
3733 ,p_last_pymt_action_id => ln_last_pymt_action_id);
3734
3735 ln_step := 10;
3736 get_current_elements(
3737 p_xfr_action_id => p_xfr_action_id
3738 ,p_curr_pymt_action_id => p_rqp_action_id
3739 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3740 ,p_assignment_id => p_assignment_id
3741 ,p_tax_unit_id => p_tax_unit_id
3742 ,p_pymt_balcall_aaid => p_payment_action_id
3743 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3744 ,p_sepchk_flag => p_seperate_check_flag
3745 ,p_legislation_code => p_legislation_code);
3746
3747 ln_step := 11;
3748 get_xfr_elements(
3749 p_xfr_action_id => p_xfr_action_id
3750 ,p_last_xfr_action_id => ln_last_xfr_action_id
3751 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3752 ,p_pymt_eff_date => p_curr_pymt_eff_date
3753 ,p_legislation_code => p_legislation_code
3754 ,p_sepchk_flag => p_seperate_check_flag
3755 ,p_assignment_id => p_assignment_id);
3756
3757 IF ld_last_pymt_eff_date <> p_curr_pymt_eff_date THEN
3758 ln_step := 12;
3759 get_missing_xfr_info(
3760 p_xfr_action_id => p_xfr_action_id
3761 ,p_tax_unit_id => p_tax_unit_id
3762 ,p_assignment_id => p_assignment_id
3763 ,p_last_pymt_action_id => ln_last_pymt_action_id
3764 ,p_last_pymt_eff_date => ld_last_pymt_eff_date
3765 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
3766 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3767 ,p_pymt_eff_date => p_curr_pymt_eff_date
3768 ,p_legislation_code => p_legislation_code);
3769 END IF;
3770
3771 END IF;
3772
3773 -- hr_utility.set_location(gv_package || lv_procedure_name, 145);
3774 -- ln_step := 13;
3775 -- populate_summary(p_xfr_action_id => p_xfr_action_id);
3776
3777 END IF; /* p_archive_balance_info = 'Y' */
3778
3779 hr_utility.set_location(gv_package || lv_procedure_name, 150);
3780 ln_step := 14;
3781 pay_emp_action_arch.get_personal_information(
3782 p_payroll_action_id => p_xfr_payroll_action_id
3783 ,p_assactid => p_xfr_action_id
3784 ,p_assignment_id => p_assignment_id
3785 ,p_curr_pymt_ass_act_id => p_pre_pay_action_id
3786 ,p_curr_eff_date => p_xfr_end_date
3787 ,p_date_earned => ld_run_date_earned
3788 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3789 ,p_tax_unit_id => p_tax_unit_id
3790 ,p_time_period_id => ln_time_period_id
3791 ,p_ppp_source_action_id => p_ppp_source_action_id
3792 ,p_run_action_id => p_payment_action_id
3793 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3794 );
3795
3796 ln_step := 15;
3797 get_mx_personal_information(
3798 p_payroll_action_id => p_xfr_payroll_action_id
3799 ,p_assactid => p_xfr_action_id
3800 ,p_assignment_id => p_assignment_id
3801 ,p_curr_pymt_ass_act_id => p_pre_pay_action_id
3802 ,p_curr_eff_date => p_xfr_end_date
3803 ,p_date_earned => ld_run_date_earned
3804 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3805 ,p_tax_unit_id => p_tax_unit_id
3806 ,p_time_period_id => ln_time_period_id
3807 ,p_ppp_source_action_id => p_ppp_source_action_id
3808 ,p_run_action_id => p_payment_action_id
3809 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
3810 );
3811
3812 hr_utility.set_location(gv_package || lv_procedure_name, 210);
3813 ln_step := 16;
3814 pay_emp_action_arch.insert_rows_thro_api_process(
3815 p_action_context_id => p_xfr_action_id
3816 ,p_action_context_type=> 'AAP'
3817 ,p_assignment_id => p_assignment_id
3818 ,p_tax_unit_id => p_tax_unit_id
3819 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3820 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
3821 );
3822
3823 hr_utility.set_location(gv_package || lv_procedure_name, 220);
3824 ln_step := 17;
3825 update_employee_information(
3826 p_action_context_id => p_xfr_action_id
3827 ,p_assignment_id => p_assignment_id);
3828
3829 hr_utility.set_location(gv_package || lv_procedure_name, 250);
3830
3831 EXCEPTION
3832 WHEN OTHERS THEN
3833 lv_error_message := 'Error IN step ' ||ln_step|| ' of '||
3834 gv_package || lv_procedure_name;
3835
3836 hr_utility.trace(lv_error_message || '-' || SQLERRM);
3837
3838 lv_error_message :=
3839 pay_emp_action_arch.set_error_message(lv_error_message);
3840
3841 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3842 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3843 hr_utility.raise_error;
3844
3845 END process_actions;
3846
3847 /******************************************************************
3848 Name : This procedure archives data at payroll action level.
3849 This would be called from the archive_data procedure
3850 (for the first chunk only). The
3851 action_infomration_categories archived by this are
3852 EMPLOYEE OTHER INFORMATION for MESG, MX EMPLOYER
3853 DETAILS and ADDRESS DETAILS for Legal Employer
3854 Address.
3855 Arguments : p_payroll_action_id Archiver Payroll Action ID
3856 p_payroll_id Payroll ID
3857 p_effective_date End Date of Archiver
3858 Notes :
3859 ******************************************************************/
3860 PROCEDURE arch_pay_action_level_data(p_payroll_action_id IN NUMBER
3861 ,p_payroll_id IN NUMBER
3862 ,p_effective_date IN DATE
3863 )
3864 IS
3865
3866 ln_organization_id NUMBER(15);
3867 ln_tax_unit_id NUMBER(15);
3868 ln_business_group_id NUMBER(15);
3869 lv_procedure_name VARCHAR2(100);
3870
3871
3872 CURSOR c_get_organization(cp_payroll_id IN NUMBER
3873 ,cp_effective_date IN DATE
3874 ) IS
3875 SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_N7)*/
3876 DISTINCT paf.organization_id,
3877 paf.business_group_id
3878 FROM per_all_assignments_f paf
3879 WHERE paf.payroll_id = cp_payroll_id
3880 AND cp_effective_date BETWEEN paf.effective_start_date
3881 AND paf.effective_end_date;
3882
3883 CURSOR c_get_legal_ER IS
3884 SELECT DISTINCT hr_mx_utility.get_legal_employer(paf.business_group_id,
3885 paa.tax_unit_id)
3886 FROM per_all_assignments_f paf,
3887 pay_assignment_actions paa
3888 WHERE paa.payroll_action_id = p_payroll_action_id
3889 AND paa.assignment_id = paf.assignment_id
3890 AND paf.payroll_id = p_payroll_id
3891 AND p_effective_date BETWEEN paf.effective_start_date
3892 AND paf.effective_end_date;
3893
3894 CURSOR c_get_gre IS
3895 SELECT DISTINCT paa.tax_unit_id
3896 FROM pay_assignment_actions paa
3897 WHERE paa.payroll_action_id = p_payroll_action_id;
3898
3899
3900 PROCEDURE get_legal_er_details(p_legal_er_id NUMBER
3901 ) AS
3902
3903 CURSOR c_get_legal_er_details
3904 IS
3905 SELECT nvl(hoi_LE.org_information1, -- Bug 4155512
3906 hr_general.decode_organization(p_legal_er_id)) "Employer Name",
3907 hoi_LE.org_information2 "Employer RFC ID",
3908 hoi_GRE.org_information1 "Employer Social Security ID"
3909 FROM hr_organization_information hoi_LE,
3910 hr_organization_information hoi_GRE
3911 WHERE hoi_LE.organization_id = p_legal_er_id
3912 AND hoi_LE.org_information_context = 'MX_TAX_REGISTRATION'
3913 AND hoi_GRE.organization_id(+) = hoi_LE.organization_id
3914 AND hoi_GRE.org_information_context(+) = 'MX_SOC_SEC_DETAILS';
3915
3916 lv_legal_employer_name hr_organization_information.org_information1%TYPE;
3917 lv_er_rfc_id hr_organization_information.org_information2%TYPE;
3918 lv_er_ss_id hr_organization_information.org_information1%TYPE;
3919 ln_index NUMBER;
3920
3921 BEGIN
3922 OPEN c_get_legal_er_details;
3923 FETCH c_get_legal_er_details INTO lv_legal_employer_name,
3924 lv_er_rfc_id,
3925 lv_er_ss_id;
3926 CLOSE c_get_legal_er_details;
3927
3928 hr_utility.trace('lv_legal_employer_name: ' || lv_legal_employer_name);
3929 hr_utility.trace('lv_er_rfc_id ' || lv_er_rfc_id);
3930 hr_utility.trace('lv_er_ss_id ' || lv_er_ss_id);
3931
3932 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
3933 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
3934 := 'MX EMPLOYER DETAILS';
3935 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
3936 := NULL;
3937 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
3938 := p_legal_er_id;
3939 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info2
3940 := lv_legal_employer_name;
3941 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info3
3942 := lv_er_ss_id;
3943 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info4
3944 := lv_er_rfc_id ;
3945
3946 END get_legal_er_details;
3947
3948
3949 PROCEDURE get_gre_details(p_gre_id NUMBER) AS
3950 --
3951 CURSOR c_get_gre_details
3952 IS
3953 SELECT org_information1 "Employer Social Security ID"
3954 FROM hr_organization_information hoi
3955 WHERE organization_id = p_gre_id
3956 AND org_information_context = 'MX_SOC_SEC_DETAILS'
3957 AND organization_id NOT IN
3958 (SELECT organization_id
3959 FROM hr_organization_information
3960 WHERE org_information_context
3961 = 'MX_TAX_REGISTRATION'
3962 );
3963
3964 lv_er_ss_id hr_organization_information.org_information1%TYPE;
3965 ln_index NUMBER;
3966
3967 BEGIN
3968 OPEN c_get_gre_details;
3969 FETCH c_get_gre_details INTO lv_er_ss_id;
3970 CLOSE c_get_gre_details;
3971
3972 hr_utility.trace('lv_er_ss_id ' || lv_er_ss_id);
3973
3974 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
3975 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
3976 := 'MX EMPLOYER DETAILS';
3977 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
3978 := NULL;
3979 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
3980 := p_gre_id;
3981 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info3
3982 := lv_er_ss_id;
3983
3984 END get_gre_details;
3985
3986 PROCEDURE get_org_other_info(p_organization_id IN NUMBER
3987 ,p_business_group_id IN NUMBER)
3988 IS
3989 CURSOR c_get_other_info(cp_organization_id IN NUMBER
3990 ,cp_org_information_context IN VARCHAR2) IS
3991 SELECT hri.org_information1,
3992 hri.org_information2, hri.org_information3,
3993 hri.org_information4, hri.org_information5,
3994 hri.org_information6, hri.org_information7
3995 FROM hr_organization_information hri
3996 WHERE hri.organization_id = cp_organization_id
3997 AND hri.org_information_context = cp_org_information_context
3998 AND hri.org_information1 = 'MESG';
3999
4000 lv_org_information1 hr_organization_information.org_information1%TYPE;
4001 lv_org_information2 hr_organization_information.org_information2%TYPE;
4002 lv_org_information3 hr_organization_information.org_information3%TYPE;
4003 lv_org_information4 hr_organization_information.org_information4%TYPE;
4004 lv_org_information5 hr_organization_information.org_information5%TYPE;
4005 lv_org_information6 hr_organization_information.org_information6%TYPE;
4006 lv_org_information7 hr_organization_information.org_information7%TYPE;
4007
4008 ln_index NUMBER;
4009 lv_procedure_name VARCHAR2(100);
4010
4011 BEGIN
4012 lv_procedure_name := '.arch_pay_action_level_data:get_org_other_info';
4013
4014 OPEN c_get_other_info(p_organization_id
4015 ,'Organization:Payslip Info') ;
4016 LOOP
4017 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4018 FETCH c_get_other_info INTO lv_org_information1
4019 ,lv_org_information2
4020 ,lv_org_information3
4021 ,lv_org_information4
4022 ,lv_org_information5
4023 ,lv_org_information6
4024 ,lv_org_information7;
4025 IF c_get_other_info%NOTFOUND THEN
4026 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4027 EXIT;
4028 END IF;
4029
4030
4031 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4032
4033 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
4034 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
4035 := 'EMPLOYEE OTHER INFORMATION';
4036 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
4037 := NULL;
4038 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
4039 := p_organization_id;
4040 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info2
4041 := 'MESG';
4042 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info4
4043 := NVL(lv_org_information7,lv_org_information4) ;
4044 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
4045 := lv_org_information5 ;
4046 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
4047 := lv_org_information6;
4048 END LOOP ;
4049 CLOSE c_get_other_info;
4050
4051 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4052 OPEN c_get_other_info(p_business_group_id
4053 ,'Business Group:Payslip Info') ;
4054 LOOP
4055 hr_utility.set_location(gv_package || lv_procedure_name, 110);
4056 FETCH c_get_other_info INTO lv_org_information1
4057 ,lv_org_information2
4058 ,lv_org_information3
4059 ,lv_org_information4
4060 ,lv_org_information5
4061 ,lv_org_information6
4062 ,lv_org_information7;
4063 IF c_get_other_info%NOTFOUND THEN
4064 hr_utility.set_location(gv_package || lv_procedure_name, 120);
4065 EXIT;
4066 END IF;
4067
4068 hr_utility.set_location(gv_package || lv_procedure_name, 130);
4069 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
4070 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
4071 := 'EMPLOYEE OTHER INFORMATION';
4072 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
4073 := NULL;
4074 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
4075 := p_business_group_id;
4076 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info2
4077 := 'MESG';
4078 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info4
4079 := NVL(lv_org_information7,lv_org_information4) ;
4080 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
4081 := lv_org_information5 ;
4082 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
4083 := lv_org_information6;
4084 END LOOP ;
4085 CLOSE c_get_other_info;
4086 hr_utility.set_location(gv_package || lv_procedure_name, 140);
4087
4088 END get_org_other_info;
4089
4090
4091 PROCEDURE get_legal_ER_address(p_organization_id IN NUMBER)
4092 IS
4093 CURSOR c_addr_line(cp_organization_id IN NUMBER) IS
4094 SELECT address_line_1, address_line_2,
4095 address_line_3, town_or_city,
4096 region_1, region_2,
4097 region_3, postal_code,
4098 country, telephone_number_1
4099 FROM hr_locations hl,
4100 hr_organization_units hou
4101 WHERE hou.organization_id = cp_organization_id
4102 AND hou.location_id = hl.location_id;
4103
4104 lv_ee_or_er VARCHAR2(150);
4105 lv_er_address_line_1 VARCHAR2(240);
4106 lv_er_address_line_2 VARCHAR2(240);
4107 lv_er_address_line_3 VARCHAR2(240);
4108 lv_er_town_or_city VARCHAR2(150);
4109 lv_er_region_1 VARCHAR2(240);
4110 lv_er_region_2 VARCHAR2(240);
4111 lv_er_region_3 VARCHAR2(240);
4112 lv_er_postal_code VARCHAR2(150);
4113 lv_er_country VARCHAR2(240);
4114 lv_er_telephone VARCHAR2(150);
4115
4116 lv_exists VARCHAR2(1);
4117 ln_index NUMBER;
4118 lv_procedure_name VARCHAR2(100);
4119
4120 BEGIN
4121 lv_ee_or_er := 'Employer Address';
4122 lv_exists := 'N';
4123 lv_procedure_name := '.arch_pay_action_level_data:get_legal_ER_address';
4124
4125 -- Get Employer address
4126 hr_utility.set_location(gv_package || lv_procedure_name, 210);
4127 OPEN c_addr_line(p_organization_id);
4128 FETCH c_addr_line INTO lv_er_address_line_1
4129 ,lv_er_address_line_2
4130 ,lv_er_address_line_3
4131 ,lv_er_town_or_city
4132 ,lv_er_region_1
4133 ,lv_er_region_2
4134 ,lv_er_region_3
4135 ,lv_er_postal_code
4136 ,lv_er_country
4137 ,lv_er_telephone;
4138 CLOSE c_addr_line;
4139 hr_utility.set_location(gv_package || lv_procedure_name, 250);
4140
4141 IF pay_emp_action_arch.ltr_ppa_arch_data.count > 0 THEN
4142 FOR i IN pay_emp_action_arch.ltr_ppa_arch_data.FIRST ..
4143 pay_emp_action_arch.ltr_ppa_arch_data.LAST LOOP
4144 IF pay_emp_action_arch.ltr_ppa_arch_data(i).act_info1
4145 = ln_organization_id AND
4146 pay_emp_action_arch.ltr_ppa_arch_data(i).act_info14
4147 = 'Employer Address' THEN
4148 lv_exists := 'Y';
4149 EXIT;
4150 END IF;
4151 END LOOP;
4152 END IF;
4153
4154 IF lv_exists = 'N' THEN
4155 hr_utility.set_location(gv_package || lv_procedure_name, 260);
4156 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
4157
4158 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
4159 := 'ADDRESS DETAILS';
4160 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
4161 := NULL;
4162 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
4163 := ln_organization_id;
4164 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
4165 := lv_er_address_line_1 ;
4166 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
4167 := lv_er_address_line_2;
4168 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info7
4169 := lv_er_address_line_3;
4170 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info8
4171 := lv_er_town_or_city;
4172 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info9
4173 := lv_er_region_1;
4174 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info10
4175 := lv_er_region_2;
4176 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info11
4177 := lv_er_region_3 ;
4178 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info12
4179 := lv_er_postal_code;
4180 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info13
4181 := lv_er_country;
4182 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info14
4183 := lv_ee_or_er;
4184 END IF;
4185
4186 END get_legal_ER_address;
4187
4188 BEGIN
4189 lv_procedure_name := '.arch_pay_action_level_data';
4190
4191 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4192 OPEN c_get_organization(p_payroll_id, p_effective_date);
4193 LOOP
4194 FETCH c_get_organization INTO ln_organization_id,
4195 ln_business_group_id;
4196 IF c_get_organization%NOTFOUND THEN
4197 EXIT;
4198 END IF;
4199
4200 get_org_other_info(ln_organization_id, ln_business_group_id);
4201 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4202
4203 END LOOP;
4204 CLOSE c_get_organization;
4205
4206 -- For each Legal Employer corresponding to the GREs processed in the
4207 -- Archiver, we archive the Employer Address, Employer Legal Name and
4208 -- Employer RFC ID. If it is also a GRE, then the Employer SS ID for it is
4209 -- also archived.
4210 --
4211 OPEN c_get_legal_ER;
4212 LOOP
4213 FETCH c_get_legal_ER INTO ln_organization_id;
4214 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4215
4216 IF c_get_legal_ER%NOTFOUND THEN
4217 EXIT;
4218 END IF;
4219
4220 get_legal_ER_address(ln_organization_id);
4221 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4222
4223 get_legal_ER_details(ln_organization_id);
4224 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4225
4226 END LOOP;
4227 CLOSE c_get_legal_ER;
4228
4229 -- For each GRE processed in the Archiver that is not a Legal Employer
4230 -- (because Legal Employer details are already archived), we archive the
4231 -- Employer SS ID for it.
4232 --
4233 OPEN c_get_gre;
4234 LOOP
4235 FETCH c_get_gre INTO ln_organization_id;
4236 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4237
4238 IF c_get_gre%NOTFOUND THEN
4239 EXIT;
4240 END IF;
4241
4242 get_gre_details(ln_organization_id);
4243 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4244
4245 END LOOP;
4246 CLOSE c_get_gre;
4247
4248
4249 hr_utility.set_location(gv_package || lv_procedure_name, 80);
4250
4251 -- insert rows in pay_action_information table
4252 IF pay_emp_action_arch.ltr_ppa_arch.count > 0 THEN
4253 pay_emp_action_arch.insert_rows_thro_api_process(
4254 p_action_context_id => p_payroll_action_id
4255 ,p_action_context_type => 'PA'
4256 ,p_assignment_id => NULL
4257 ,p_tax_unit_id => NULL
4258 ,p_curr_pymt_eff_date => p_effective_date
4259 ,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
4260 );
4261 END IF;
4262 EXCEPTION
4263 WHEN OTHERS THEN
4264 hr_utility.trace('Error in ' || gv_package || '.'
4265 || lv_procedure_name || '-'
4266 || TO_CHAR(SQLCODE) || '-' || SQLERRM);
4267 hr_utility.set_location(gv_package || lv_procedure_name, 90);
4268 RAISE hr_utility.hr_error;
4269
4270 END arch_pay_action_level_data;
4271
4272
4273 /******************************************************************
4274 Name : process_additional_elements
4275 Purpose : Retrieves the balances corresponding to the elements
4276 processed in the given assignment and inserts YTD
4277 balance to pl/sql table.
4278 Arguments : p_assignment_id => Terminated Assignment Id
4279 p_assignment_action_id => Max assignment action id
4280 of given assignment
4281 p_curr_eff_date => Current effective date
4282 p_xfr_action_id => Current XFR action id.
4283 p_legislation_code => 'MX'
4284 p_tax_unit_id => GRE of the assignment
4285 p_action_type => Action type of the payment
4286 action
4287 p_start_date => Start Date of the XFR action.
4288 p_end_date => End Date of the XFR action.
4289
4290 Notes : This process is used to retrieve elements processed
4291 in terminated assignments which are not picked up by
4292 the archiver.
4293 ******************************************************************/
4294 PROCEDURE process_additional_elements(p_assignment_id IN NUMBER
4295 ,p_assignment_action_id IN NUMBER
4296 ,p_curr_eff_date IN DATE
4297 ,p_xfr_action_id IN NUMBER
4298 ,p_legislation_code IN VARCHAR2
4299 ,p_tax_unit_id IN NUMBER
4300 ,p_action_type IN VARCHAR2
4301 ,p_start_date IN DATE
4302 ,p_end_date IN DATE )
4303 IS
4304
4305 lv_procedure_name VARCHAR2(50);
4306 lv_reporting_name VARCHAR2(80);
4307 lv_jurisdiction_code VARCHAR2(80);
4308 ln_primary_balance_id NUMBER;
4309 ln_hours_balance_id NUMBER;
4310 ln_days_balance_id NUMBER;
4311 ln_element_index NUMBER;
4312 lv_action_category VARCHAR2(50);
4313 ln_ytd_defined_balance_id NUMBER;
4314 ln_ytd_amount NUMBER(15,2) := 0;
4315 ln_ytd_hours_balance_id NUMBER;
4316 ln_ytd_hours NUMBER(15,2);
4317 ln_current_hours NUMBER(15,2) := 0;
4318 ln_ytd_days_balance_id NUMBER;
4319 ln_ytd_days NUMBER(15,2);
4320 ln_current_days NUMBER(15,2) := 0;
4321 ln_payments_amount NUMBER(15,2) := 0;
4322 ln_index NUMBER;
4323 ln_check_count NUMBER;
4324 ln_check_count2 NUMBER;
4325 ln_step NUMBER;
4326 lv_error_message VARCHAR2(200);
4327
4328 i NUMBER;
4329 st_cnt NUMBER;
4330 end_cnt NUMBER;
4331 lv_business_grp_id NUMBER;
4332 lv_run_bal_status VARCHAR2(1);
4333 lv_attribute_name VARCHAR2(80);
4334
4335 CURSOR c_business_grp_id IS
4336 SELECT DISTINCT business_group_id
4337 FROM per_assignments_f
4338 WHERE assignment_id = p_assignment_id;
4339
4340 CURSOR c_prev_ytd_action_elem_rbr(cp_assignment_id IN NUMBER
4341 ,cp_curr_eff_date IN DATE
4342 ) IS
4343 SELECT DISTINCT
4344 pbad.attribute_name,
4345 NVL(pbtl.reporting_name, pbtl.balance_name),
4346 prb.jurisdiction_code,
4347 pbt_pri.balance_type_id, -- Primary Balance
4348 DECODE(pbad.attribute_name,
4349 'Hourly Earnings', pbt_sec.balance_type_id,
4350 NULL), -- Hours Balance
4351 DECODE(pbad.attribute_name,
4352 'Employee Earnings', pbt_sec.balance_type_id,
4353 NULL) -- Days Balance
4354 FROM pay_bal_attribute_definitions pbad,
4355 pay_balance_attributes pba,
4356 pay_defined_balances pdb,
4357 pay_balance_types pbt_pri,
4358 pay_balance_types pbt_sec,
4359 pay_balance_types_tl pbtl,
4360 pay_run_balances prb
4361 WHERE pbad.attribute_name IN ('Employee Earnings',
4362 'Hourly Earnings',
4363 'Deductions',
4364 'Taxable Benefits'
4365 -- ,'Employee Taxes',
4366 -- 'Tax Calculation Details'
4367 )
4368 AND pbad.legislation_code = 'MX'
4369 AND pba.attribute_id = pbad.attribute_id
4370 AND pdb.defined_balance_id = pba.defined_balance_id
4371 AND pbt_pri.balance_type_id = pdb.balance_type_id
4372 AND pbt_pri.input_value_id IS NOT NULL
4373 AND pbtl.balance_type_id = pbt_pri.balance_type_id
4374 AND pbtl.language = USERENV('LANG')
4375 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
4376 AND prb.effective_date >= TRUNC(cp_curr_eff_date,'Y')
4377 AND prb.effective_date <= cp_curr_eff_date
4378 AND prb.assignment_id = cp_assignment_id
4379 AND pdb.defined_balance_id = prb.defined_balance_id
4380 ORDER BY 1;
4381
4382
4383 CURSOR c_prev_ytd_action_elements(cp_assignment_id IN NUMBER
4384 ,cp_curr_eff_date IN DATE
4385 ) IS
4386 SELECT DISTINCT
4387 pbad.attribute_name,
4388 NVL(pbtl.reporting_name, pbtl.balance_name),
4389 prr.jurisdiction_code,
4390 pbt_pri.balance_type_id, -- Primary Balance
4391 DECODE(pbad.attribute_name,
4392 'Hourly Earnings', pbt_sec.balance_type_id,
4393 NULL), -- Hours Balance
4394 DECODE(pbad.attribute_name,
4395 'Employee Earnings', pbt_sec.balance_type_id,
4396 NULL) -- Days Balance
4397 FROM pay_bal_attribute_definitions pbad,
4398 pay_balance_attributes pba,
4399 pay_defined_balances pdb,
4400 pay_balance_types pbt_pri,
4401 pay_balance_types pbt_sec,
4402 pay_balance_types_tl pbtl,
4403 pay_assignment_actions paa,
4404 pay_payroll_actions ppa,
4405 pay_run_results prr,
4406 pay_input_values_f piv
4407 WHERE pbad.attribute_name IN ('Employee Earnings',
4408 'Hourly Earnings',
4409 'Deductions',
4410 'Taxable Benefits'
4411 -- ,'Employee Taxes',
4412 -- 'Tax Calculation Details'
4413 )
4414 AND pbad.legislation_code = 'MX'
4415 AND pba.attribute_id = pbad.attribute_id
4416 AND pdb.defined_balance_id = pba.defined_balance_id
4417 AND pbt_pri.balance_type_id = pdb.balance_type_id
4418 AND pbt_pri.input_value_id = piv.input_value_id
4419 AND piv.element_type_id = prr.element_type_id
4420 AND ppa.effective_date BETWEEN piv.effective_start_date
4421 AND piv.effective_end_date
4422 AND pbtl.balance_type_id = pbt_pri.balance_type_id
4423 AND pbtl.language = USERENV('LANG')
4424 AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
4425 AND prr.assignment_action_id = paa.assignment_action_id
4426 AND paa.assignment_id = cp_assignment_id
4427 AND ppa.payroll_action_id = paa.payroll_action_id
4428 AND ppa.action_type in ('Q','R','B')
4429 AND ppa.effective_date >= TRUNC(cp_curr_eff_date,'Y')
4430 AND ppa.effective_date <= cp_curr_eff_date
4431 ORDER BY 1;
4432
4433 BEGIN
4434 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4435 lv_procedure_name := '.process_additional_elements';
4436 lv_action_category := 'AC DEDUCTIONS';
4437
4438
4439 ln_step := 10;
4440 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
4441
4442
4443 -- Populating the PL/SQL table run_bal_stat_tab with the validity status
4444 -- of various attributes. If already populated, we use that to check the
4445 -- validity
4446
4447 IF run_bal_stat.COUNT >0 THEN
4448 st_cnt := run_bal_stat.FIRST;
4449 end_cnt := run_bal_stat.LAST;
4450 FOR i IN st_cnt..end_cnt LOOP
4451 IF run_bal_stat(i).valid_status = 'N' THEN
4452 lv_run_bal_status := 'N';
4453 EXIT;
4454 END IF;
4455 END LOOP;
4456 ELSE
4457 OPEN c_business_grp_id;
4458 FETCH c_business_grp_id INTO lv_business_grp_id;
4459 CLOSE c_business_grp_id;
4460
4461 run_bal_stat(1).attribute_name := 'Employee Earnings';
4462 run_bal_stat(2).attribute_name := 'Hourly Earnings';
4463 run_bal_stat(3).attribute_name := 'Deductions';
4464 run_bal_stat(4).attribute_name := 'Employee Taxes';
4465 run_bal_stat(5).attribute_name := 'Tax Calculation Details';
4466
4467 st_cnt := run_bal_stat.FIRST;
4468 end_cnt := run_bal_stat.LAST;
4469
4470 FOR i IN st_cnt..end_cnt LOOP
4471 run_bal_stat(i).valid_status :=
4472 pay_us_payroll_utils.check_balance_status(
4473 p_curr_eff_date,
4474 lv_business_grp_id,
4475 run_bal_stat(i).attribute_name,
4476 p_legislation_code);
4477 IF (lv_run_bal_status IS NULL AND
4478 run_bal_stat(i).valid_status = 'N') THEN
4479 lv_run_bal_status := 'N';
4480 END IF;
4481 END LOOP;
4482 END IF;
4483
4484 IF lv_run_bal_status IS NULL THEN
4485 lv_run_bal_status := 'Y';
4486 END IF;
4487
4488 ln_step := 20;
4489
4490
4491 IF lv_run_bal_status = 'Y' THEN
4492 OPEN c_prev_ytd_action_elem_rbr(p_assignment_id,
4493 p_curr_eff_date);
4494 ELSE
4495 OPEN c_prev_ytd_action_elements(p_assignment_id,
4496 p_curr_eff_date);
4497 END IF;
4498
4499 LOOP
4500 IF lv_run_bal_status = 'Y' THEN
4501 FETCH c_prev_ytd_action_elem_rbr INTO
4502 lv_attribute_name,
4503 lv_reporting_name,
4504 lv_jurisdiction_code,
4505 ln_primary_balance_id,
4506 ln_hours_balance_id,
4507 ln_days_balance_id;
4508
4509 IF c_prev_ytd_action_elem_rbr%NOTFOUND THEN
4510 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4511 EXIT;
4512 END IF;
4513 ELSE
4514 FETCH c_prev_ytd_action_elements INTO
4515 lv_attribute_name,
4516 lv_reporting_name,
4517 lv_jurisdiction_code,
4518 ln_primary_balance_id,
4519 ln_hours_balance_id,
4520 ln_days_balance_id;
4521
4522 IF c_prev_ytd_action_elements%NOTFOUND THEN
4523 hr_utility.set_location(gv_package || lv_procedure_name, 45);
4524 EXIT;
4525 END IF;
4526 END IF;
4527
4528 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4529 hr_utility.trace('Reporting Name = '|| lv_reporting_name);
4530 hr_utility.trace('Primary Bal id = '|| ln_primary_balance_id);
4531 hr_utility.trace('JD Code = ' || lv_jurisdiction_code);
4532
4533 IF lv_attribute_name IN ('Deductions',
4534 'Employee Taxes',
4535 'Tax Calculation Details') THEN
4536 ln_step := 30;
4537 ln_hours_balance_id := NULL;
4538 ln_days_balance_id := NULL;
4539
4540 END IF;
4541
4542 IF lv_jurisdiction_code IS NOT NULL THEN
4543 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
4544 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
4545 ELSE
4546 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
4547 END IF;
4548
4549
4550 IF ln_hours_balance_id IS NOT NULL THEN
4551 ln_step := 40;
4552 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4553 ln_ytd_hours_balance_id
4554 := pay_emp_action_arch.get_defined_balance_id(
4555 ln_hours_balance_id,
4556 gv_ytd_balance_dimension,
4557 p_legislation_code);
4558 hr_utility.trace('ln_ytd_hours_balance_id = '||
4559 ln_ytd_hours_balance_id);
4560 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4561
4562 ln_step := 50;
4563 IF ln_ytd_hours_balance_id IS NOT NULL THEN
4564 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
4565 ln_ytd_hours_balance_id,
4566 p_assignment_action_id),0);
4567 hr_utility.trace('ln_ytd_hours = '||ln_ytd_hours);
4568 hr_utility.set_location(gv_package || lv_procedure_name, 80);
4569 END IF;
4570 END IF; --Hours
4571
4572
4573
4574 IF ln_days_balance_id IS NOT NULL THEN
4575 ln_step := 60;
4576 hr_utility.set_location(gv_package || lv_procedure_name, 90);
4577 ln_ytd_days_balance_id
4578 := pay_emp_action_arch.get_defined_balance_id(
4579 ln_days_balance_id,
4580 gv_ytd_balance_dimension,
4581 p_legislation_code);
4582 hr_utility.trace('ln_ytd_days_balance_id = '||
4583 ln_ytd_days_balance_id);
4584 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4585
4586 ln_step := 70;
4587 IF ln_ytd_days_balance_id IS NOT NULL THEN
4588 ln_ytd_days := NVL(pay_balance_pkg.get_value(
4589 ln_ytd_days_balance_id,
4590 p_assignment_action_id),0);
4591 hr_utility.trace('ln_ytd_days = '||ln_ytd_days);
4592 hr_utility.set_location(gv_package || lv_procedure_name, 110);
4593 END IF;
4594 END IF; --Days
4595
4596
4597
4598 ln_step := 80;
4599 ln_ytd_defined_balance_id
4600 := pay_emp_action_arch.get_defined_balance_id
4601 (ln_primary_balance_id,
4602 gv_ytd_balance_dimension,
4603 p_legislation_code);
4604 hr_utility.trace('ln_ytd_defined_balance_id = '||
4605 ln_ytd_defined_balance_id);
4606 hr_utility.set_location(gv_package || lv_procedure_name, 120);
4607 IF ln_ytd_defined_balance_id IS NOT NULL THEN
4608 ln_step := 90;
4609 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
4610 ln_ytd_defined_balance_id,
4611 p_assignment_action_id),0);
4612 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
4613 END IF;
4614 hr_utility.set_location(gv_package || lv_procedure_name, 130);
4615
4616
4617 IF NVL(ln_ytd_amount, 0) <> 0 THEN
4618 ln_step := 100;
4619 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
4620
4621 hr_utility.trace('ln_element_index = '||ln_element_index);
4622
4623 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
4624 := lv_reporting_name;
4625 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
4626 := ln_primary_balance_id;
4627 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
4628 := ln_hours_balance_id;
4629 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
4630 := lv_jurisdiction_code;
4631
4632
4633 ln_index := pay_ac_action_arch.lrr_act_tab.count;
4634 hr_utility.trace('ln_index = '||ln_index);
4635 IF lv_attribute_name IN ('Employee Earnings', 'Hourly Earnings',
4636 'Taxable Benefits') THEN
4637 hr_utility.set_location(gv_package || lv_procedure_name, 140);
4638 lv_action_category := 'AC EARNINGS';
4639 hr_utility.trace('ln_current_hours = '||ln_current_hours);
4640 hr_utility.trace('ln_ytd_hours = ' ||ln_ytd_hours);
4641 hr_utility.trace('ln_current_days = ' ||ln_current_days);
4642 hr_utility.trace('ln_ytd_days = ' ||ln_ytd_days);
4643 ln_step := 120;
4644 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
4645 := fnd_number.number_to_canonical(ln_current_hours);
4646 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
4647 := fnd_number.number_to_canonical(ln_ytd_hours);
4648 pay_ac_action_arch.lrr_act_tab(ln_index).act_info14
4649 := fnd_number.number_to_canonical(ln_current_days);
4650 pay_ac_action_arch.lrr_act_tab(ln_index).act_info15
4651 := fnd_number.number_to_canonical(ln_ytd_days);
4652 ELSE
4653 lv_action_category := 'AC DEDUCTIONS';
4654 END IF;
4655 hr_utility.set_location(gv_package || lv_procedure_name, 150);
4656 hr_utility.trace('lv_action_category = '||lv_action_category);
4657 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
4658 hr_utility.trace('lv_reporting_name = '||lv_reporting_name);
4659 hr_utility.trace('p_xfr_action_id = '||p_xfr_action_id);
4660 ln_step := 130;
4661
4662 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
4663 := lv_action_category;
4664 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
4665 := lv_jurisdiction_code;
4666 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
4667 := p_xfr_action_id;
4668 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
4669 := ln_primary_balance_id;
4670 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
4671 := fnd_number.number_to_canonical(ln_payments_amount);
4672 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
4673 := fnd_number.number_to_canonical(ln_ytd_amount);
4674 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
4675 := lv_reporting_name;
4676
4677 END IF;
4678 hr_utility.set_location(gv_package || lv_procedure_name, 160);
4679
4680 END LOOP;
4681 IF lv_run_bal_status = 'Y' THEN
4682 CLOSE c_prev_ytd_action_elem_rbr;
4683 ELSE
4684 CLOSE c_prev_ytd_action_elements;
4685 END IF;
4686
4687 ln_step := 140;
4688 -- populate_tax_and_summary(p_xfr_action_id);
4689 populate_tax_and_summary( p_xfr_action_id => p_xfr_action_id
4690 ,p_assignment_id => p_assignment_id
4691 ,p_pymt_balcall_aaid => -999
4692 ,p_tax_unit_id => p_tax_unit_id
4693 ,p_action_type => p_action_type
4694 ,p_pymt_eff_date => p_curr_eff_date
4695 ,p_start_date => p_start_date
4696 ,p_end_date => p_end_date
4697 ,p_ytd_balcall_aaid => p_assignment_action_id
4698 );
4699
4700 ln_step := 150;
4701 hr_utility.trace('------------Looping to see pl/sql table --------');
4702 ln_check_count := pay_ac_action_arch.emp_elements_tab.count;
4703 ln_check_count2 := pay_ac_action_arch.lrr_act_tab.count;
4704
4705 hr_utility.trace('ln_check_count = '||ln_check_count);
4706 hr_utility.trace('ln_check_count2 = '||ln_check_count2);
4707 hr_utility.trace('============= End of Processing '||p_assignment_id||
4708 '=============');
4709 hr_utility.set_location(gv_package || lv_procedure_name,170);
4710
4711 EXCEPTION
4712 WHEN OTHERS THEN
4713
4714 lv_error_message := 'Error at step ' || ln_step ||
4715 ' in ' || gv_package || lv_procedure_name;
4716
4717 hr_utility.trace(lv_error_message || '-' || SQLERRM);
4718
4719 lv_error_message :=
4720 pay_emp_action_arch.set_error_message(lv_error_message);
4721
4722 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4723 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4724 hr_utility.raise_error;
4725
4726 END process_additional_elements;
4727
4728
4729 /************************************************************
4730 Name : archive_code
4731 Purpose : This procedure Archives data which are used in
4732 Payslip, Check Writer, Deposit Advice modules.
4733 Arguments :
4734 Notes :
4735 ************************************************************/
4736 PROCEDURE archive_code(p_xfr_action_id IN NUMBER
4737 ,p_effective_date IN DATE)
4738 IS
4739
4740 CURSOR c_xfr_info (cp_assignment_action IN NUMBER) IS
4741 SELECT paa.payroll_action_id,
4742 paa.assignment_action_id,
4743 paa.assignment_id,
4744 paa.tax_unit_id,
4745 paa.serial_number,
4746 paa.chunk_number
4747 FROM pay_assignment_actions paa
4748 WHERE paa.assignment_action_id = cp_assignment_action;
4749
4750 CURSOR c_legislation (cp_business_group IN NUMBER) IS
4751 SELECT org_information9
4752 FROM hr_organization_information
4753 WHERE org_information_context = 'Business Group Information'
4754 AND organization_id = cp_business_group;
4755
4756 CURSOR c_assignment_run (cp_prepayment_action_id IN NUMBER) IS
4757 SELECT DISTINCT paa.assignment_id
4758 FROM pay_action_interlocks pai,
4759 pay_assignment_actions paa,
4760 pay_payroll_actions ppa
4761 WHERE pai.locking_action_id = cp_prepayment_action_id
4762 AND paa.assignment_action_id = pai.locked_action_id
4763 AND ppa.payroll_action_id = paa.payroll_action_id
4764 AND ppa.action_type IN ('R', 'Q', 'B')
4765 AND ((ppa.run_type_id IS NULL AND
4766 paa.source_action_id IS NULL) OR
4767 (ppa.run_type_id IS NOT NULL AND
4768 paa.source_action_id IS NOT NULL))
4769 AND paa.action_status = 'C';
4770
4771 CURSOR c_master_run_action(
4772 cp_prepayment_action_id IN NUMBER,
4773 cp_assignment_id IN NUMBER) IS
4774 SELECT paa.assignment_action_id, paa.payroll_action_id,
4775 ppa.action_type
4776 FROM pay_payroll_actions ppa,
4777 pay_assignment_actions paa,
4778 pay_action_interlocks pai
4779 WHERE pai.locking_action_id = cp_prepayment_action_id
4780 AND pai.locked_action_id = paa.assignment_action_id
4781 AND paa.assignment_id = cp_assignment_id
4782 AND paa.source_action_id IS NULL
4783 AND ppa.payroll_action_id = paa.payroll_action_id
4784 ORDER BY paa.assignment_action_id DESC;
4785
4786 CURSOR c_pymt_eff_date(cp_prepayment_action_id IN NUMBER) IS
4787 SELECT ppa.effective_date
4788 FROM pay_payroll_actions ppa,
4789 pay_assignment_actions paa
4790 WHERE ppa.payroll_action_id = paa.payroll_action_id
4791 AND paa.assignment_action_id = cp_prepayment_action_id;
4792
4793 CURSOR c_check_pay_action( cp_payroll_action_id IN NUMBER) IS
4794 SELECT count(*)
4795 FROM pay_action_information
4796 WHERE action_context_id = cp_payroll_action_id
4797 AND action_context_type = 'PA';
4798
4799 CURSOR c_payment_info(cp_prepay_action_id NUMBER) IS
4800 SELECT assignment_id
4801 ,tax_unit_id
4802 ,NVL(source_action_id,-999)
4803 ,assignment_action_id
4804 FROM pay_payment_information_v
4805 WHERE assignment_action_id = cp_prepay_action_id
4806 ORDER BY 3,1,2;
4807
4808 CURSOR c_run_aa_id(cp_pp_asg_act_id NUMBER
4809 ,cp_assignment_id NUMBER
4810 ,cp_tax_unit_id NUMBER) IS
4811 SELECT paa.assignment_action_id
4812 ,paa.source_action_id
4813 FROM pay_assignment_actions paa
4814 ,pay_action_interlocks pai
4815 where pai.locking_action_id = cp_pp_asg_act_id
4816 AND paa.assignment_action_id = pai.locked_action_id
4817 AND paa.assignment_id = cp_assignment_id
4818 AND paa.tax_unit_id = cp_tax_unit_id
4819 AND paa.source_action_id IS NOT NULL
4820 AND NOT EXISTS ( SELECT 1
4821 FROM pay_run_types_f prt
4822 WHERE prt.legislation_code = 'MX'
4823 AND prt.run_type_id = paa.run_type_id
4824 AND prt.run_method IN ( 'C', 'S' ) )
4825 ORDER BY paa.action_sequence DESC;
4826
4827 CURSOR c_get_prepay_aaid_for_sepchk( cp_asg_act_id NUMBER,
4828 cp_source_act_id NUMBER ) IS
4829 SELECT ppp.assignment_action_id
4830 FROM pay_assignment_actions paa
4831 ,pay_pre_payments ppp
4832 WHERE ( paa.assignment_action_id = cp_asg_act_id OR
4833 paa.source_action_id = cp_asg_act_id )
4834 AND ppp.assignment_action_id = paa.assignment_action_id
4835 AND ppp.source_action_id = cp_source_act_id;
4836
4837
4838 CURSOR c_run_aa_id_bal_adj(cp_pp_asg_act_id NUMBER
4839 ,cp_assignment_id NUMBER
4840 ,cp_tax_unit_id NUMBER) IS
4841 SELECT paa.assignment_action_id
4842 ,paa.source_action_id
4843 FROM pay_assignment_actions paa
4844 ,pay_action_interlocks pai
4845 WHERE pai.locking_action_id = cp_pp_asg_act_id
4846 AND paa.assignment_action_id = pai.locked_action_id
4847 AND paa.assignment_id = cp_assignment_id
4848 AND paa.tax_unit_id = cp_tax_unit_id
4849 ORDER BY paa.action_sequence DESC;
4850
4851 CURSOR c_all_runs(cp_pp_asg_act_id IN NUMBER
4852 ,cp_assignment_id IN NUMBER
4853 ,cp_tax_unit_id IN NUMBER) IS
4854 SELECT paa.assignment_action_id
4855 FROM pay_assignment_actions paa,
4856 pay_action_interlocks pai
4857 WHERE pai.locking_action_id = cp_pp_asg_act_id
4858 AND paa.assignment_action_id = pai.locked_action_id
4859 AND paa.assignment_id = cp_assignment_id
4860 AND paa.tax_unit_id = cp_tax_unit_id
4861 AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
4862 gn_np_sepchk_run_type_id)
4863 AND NOT EXISTS ( SELECT 1
4864 FROM pay_run_types_f prt
4865 WHERE prt.legislation_code = 'US'
4866 AND prt.run_type_id = NVL(paa.run_type_id,0)
4867 AND prt.run_method = 'C' );
4868
4869 CURSOR c_get_emp_adjbal(cp_xfr_action_id NUMBER) IS
4870 SELECT locked_action_id
4871 FROM pay_action_interlocks
4872 WHERE locking_action_id = cp_xfr_action_id;
4873
4874 ld_curr_pymt_eff_date DATE;
4875 ln_sepchk_run_type_id NUMBER;
4876 lv_legislation_code VARCHAR2(2);
4877
4878 ln_xfr_master_action_id NUMBER;
4879
4880 ln_tax_unit_id NUMBER;
4881 ln_xfr_payroll_action_id NUMBER; /* of current xfr */
4882 ln_xfr_assignment_id NUMBER;
4883 ln_assignment_id NUMBER;
4884 ln_chunk_number NUMBER;
4885
4886 lv_xfr_master_serial_number VARCHAR2(30);
4887 lv_master_action_type VARCHAR2(1);
4888 lv_master_sepcheck_flag VARCHAR2(1);
4889 ln_asg_action_id NUMBER;
4890
4891 ln_master_run_action_id NUMBER;
4892 ln_master_run_pact_id NUMBER;
4893 lv_master_run_action_type VARCHAR2(1);
4894
4895 ln_pymt_balcall_aaid NUMBER;
4896 ln_pay_action_count NUMBER;
4897
4898 ld_start_date DATE;
4899 ld_end_date DATE;
4900 ln_business_group_id NUMBER;
4901 ln_cons_set_id NUMBER;
4902 ln_payroll_id NUMBER;
4903
4904 lv_resident_jurisdiction VARCHAR2(30);
4905
4906 lv_procedure_name VARCHAR2(100);
4907 lv_error_message VARCHAR2(200);
4908 ln_step NUMBER;
4909 ln_term_asg_id NUMBER;
4910 ln_term_asg_act_id NUMBER;
4911 ln_old_term_asg_id NUMBER;
4912
4913
4914 ln_source_action_id NUMBER;
4915 ln_child_xfr_action_id NUMBER;
4916 ln_run_aa_id NUMBER;
4917 ln_run_source_action_id NUMBER;
4918 ln_rqp_action_id NUMBER;
4919 ln_ppp_source_action_id NUMBER;
4920 ln_master_run_aa_id NUMBER;
4921 ln_earnings NUMBER;
4922 lv_serial_number VARCHAR2(30);
4923
4924 ln_run_qp_found NUMBER;
4925 ln_all_run_asg_act_id NUMBER;
4926
4927 lv_archive_balance_info VARCHAR2(1);
4928
4929 BEGIN
4930 lv_procedure_name := '.archive_code';
4931 ln_old_term_asg_id := '-1';
4932 lv_archive_balance_info := 'Y';
4933
4934
4935 pay_emp_action_arch.gv_error_message := NULL;
4936 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4937 ln_step := 1;
4938 OPEN c_xfr_info (p_xfr_action_id);
4939 FETCH c_xfr_info INTO ln_xfr_payroll_action_id,
4940 ln_xfr_master_action_id,
4941 ln_xfr_assignment_id,
4942 ln_tax_unit_id,
4943 lv_xfr_master_serial_number,
4944 ln_chunk_number;
4945 CLOSE c_xfr_info;
4946
4947 ln_step := 2;
4948 get_payroll_action_info(p_payroll_action_id => ln_xfr_payroll_action_id
4949 ,p_start_date => ld_start_date
4950 ,p_end_date => ld_end_date
4951 ,p_business_group_id => ln_business_group_id
4952 ,p_cons_set_id => ln_cons_set_id
4953 ,p_payroll_id => ln_payroll_id);
4954
4955 hr_utility.set_location(gv_package || lv_procedure_name, 15);
4956
4957 ln_step := 205;
4958 pay_emp_action_arch.gv_multi_payroll_pymt
4959 := pay_emp_action_arch.get_multi_assignment_flag(
4960 p_payroll_id => ln_payroll_id
4961 ,p_effective_date => ld_end_date);
4962
4963 hr_utility.trace('pay_emp_action_arch.gv_multi_payroll_pymt = ' ||
4964 pay_emp_action_arch.gv_multi_payroll_pymt);
4965
4966 ln_step := 3;
4967 OPEN c_legislation (ln_business_group_id);
4968 FETCH c_legislation INTO lv_legislation_code ;
4969 IF c_legislation%NOTFOUND THEN
4970 hr_utility.trace('Business Group FOR Archiver Process Not Found');
4971 hr_utility.raise_error;
4972 END IF;
4973 CLOSE c_legislation;
4974 hr_utility.trace('lv_legislation_code '||lv_legislation_code);
4975
4976 ln_step := 4;
4977
4978 -- process the master_action
4979 lv_master_action_type := SUBSTR(lv_xfr_master_serial_number,1,1);
4980 -- Always N FOR Master Assignment Action
4981 lv_master_sepcheck_flag := SUBSTR(lv_xfr_master_serial_number,2,1);
4982 -- Assignment Action of Quick Pay Pre Payment, Pre Payment, Reversal
4983 ln_asg_action_id := SUBSTR(lv_xfr_master_serial_number,3);
4984
4985 ln_step := 5;
4986 OPEN c_pymt_eff_date(ln_asg_action_id);
4987 FETCH c_pymt_eff_date INTO ld_curr_pymt_eff_date;
4988 IF c_pymt_eff_date%NOTFOUND THEN
4989 hr_utility.trace('Payroll Action FOR Archiver Process Not Found');
4990 hr_utility.raise_error;
4991 END IF;
4992 CLOSE c_pymt_eff_date;
4993
4994 hr_utility.trace('End Date=' || TO_CHAR(ld_end_date, 'dd-mon-yyyy'));
4995 hr_utility.trace('Start Date=' || TO_CHAR(ld_start_date, 'dd-mon-yyyy'));
4996 hr_utility.trace('Business Group Id=' || TO_CHAR(ln_business_group_id));
4997 hr_utility.trace('Serial Number=' || lv_xfr_master_serial_number);
4998 hr_utility.trace('ln_xfr_payroll_action_id =' ||
4999 TO_CHAR(ln_xfr_payroll_action_id));
5000
5001 ln_step := 6;
5002 IF lv_master_action_type IN ( 'P','U') THEN
5003 /************************************************************
5004 ** For Master Pre Payment Action get the distinct
5005 ** Assignment_ID's and archive the data separately for
5006 ** all the assigments.
5007 *************************************************************/
5008 ln_step := 7;
5009 OPEN c_payment_info(ln_asg_action_id);
5010 LOOP
5011
5012 FETCH c_payment_info INTO ln_assignment_id
5013 ,ln_tax_unit_id
5014 ,ln_source_action_id
5015 ,ln_asg_action_id;
5016 EXIT WHEN c_payment_info%NOTFOUND;
5017
5018 hr_utility.trace('archive_code:payment_info: ln_asg_action_id' ||
5019 ln_asg_action_id );
5020 hr_utility.trace('archive_code:payment_info: ln_assignment_id' ||
5021 ln_assignment_id );
5022 hr_utility.trace('archive_code:payment_info: ln_tax_unit_id' ||
5023 ln_tax_unit_id );
5024 hr_utility.trace('archive_code:payment_info: ln_source_action_id' ||
5025 ln_source_action_id );
5026
5027 ln_step := 8;
5028
5029 IF ln_source_action_id = -999 THEN
5030
5031 ln_step := 9;
5032 lv_master_sepcheck_flag := 'N';
5033 ln_master_run_aa_id := NULL;
5034 ln_run_qp_found := 0;
5035
5036 /********************************************************
5037 ** Getting Run Assignment Action Id for normal cheque.
5038 ********************************************************/
5039 -- This cursor fetches all aaids locked by the prepayment
5040 -- that are non-cumulative and non-separate-check, but which have a source action.
5041 -- So either Regular or Tax Separate.
5042 --
5043 OPEN c_run_aa_id(ln_asg_action_id
5044 ,ln_assignment_id
5045 ,ln_tax_unit_id);
5046 FETCH c_run_aa_id INTO ln_run_aa_id, ln_run_source_action_id;
5047 IF c_run_aa_id%found THEN
5048 ln_run_qp_found := 1;
5049 END IF;
5050 CLOSE c_run_aa_id;
5051
5052 ln_step := 10;
5053 hr_utility.trace('GRE ln_run_aa_id = ' || ln_run_aa_id);
5054
5055 IF ln_run_source_action_id IS NOT NULL THEN
5056 ln_master_run_aa_id := ln_run_source_action_id; -- Normal Chk
5057 ELSE
5058 IF ln_run_qp_found = 0 THEN
5059 /* Balance Adjustment or Reversal */
5060 OPEN c_run_aa_id_bal_adj(ln_asg_action_id
5061 ,ln_assignment_id
5062 ,ln_tax_unit_id);
5063 FETCH c_run_aa_id_bal_adj INTO ln_run_aa_id,
5064 ln_run_source_action_id;
5065 CLOSE c_run_aa_id_bal_adj;
5066 ln_master_run_aa_id := ln_asg_action_id;
5067 ELSE
5068 --
5069 -- This will never be entered since the source_action_id is NULL
5070 -- IS already ruled out in the cursor c_run_aa_id
5071 --
5072 ln_master_run_aa_id := ln_run_aa_id; -- Normal Chk
5073 END IF;
5074 END IF;
5075
5076 ln_rqp_action_id := ln_asg_action_id;
5077 ln_ppp_source_action_id := NULL;
5078
5079
5080 ELSE
5081
5082 ln_step := 11;
5083 lv_master_sepcheck_flag := 'Y';
5084 ln_master_run_aa_id := ln_source_action_id; -- Sep Chk
5085 ln_rqp_action_id := ln_source_action_id; -- Sep Chk
5086 ln_ppp_source_action_id := ln_source_action_id; -- Sep Chk
5087 ln_run_aa_id := ln_source_action_id; -- Sep Chk
5088
5089 END IF;
5090
5091 IF ln_source_action_id <> -999 THEN
5092
5093 OPEN c_get_prepay_aaid_for_sepchk(ln_asg_action_id
5094 ,ln_source_action_id);
5095 FETCH c_get_prepay_aaid_for_sepchk INTO ln_asg_action_id;
5096 CLOSE c_get_prepay_aaid_for_sepchk;
5097
5098 ln_step := 12;
5099 SELECT pay_assignment_actions_s.nextval
5100 INTO ln_child_xfr_action_id
5101 FROM dual;
5102
5103 hr_utility.set_location(gv_package || lv_procedure_name, 30);
5104
5105 -- insert into pay_assignment_actions.
5106
5107 ln_step := 13;
5108
5109
5110 hr_nonrun_asact.insact(ln_child_xfr_action_id,
5111 ln_assignment_id,
5112 ln_xfr_payroll_action_id,
5113 ln_chunk_number,
5114 ln_tax_unit_id,
5115 NULL,
5116 'C',
5117 p_xfr_action_id);
5118
5119 hr_utility.set_location(gv_package || lv_procedure_name, 40);
5120
5121 hr_utility.trace('GRE Locking Action = ' ||ln_child_xfr_action_id);
5122 hr_utility.trace('GRE Locked Action = ' ||ln_asg_action_id);
5123
5124 -- insert an interlock to this action
5125
5126 ln_step := 14;
5127
5128 hr_nonrun_asact.insint(ln_child_xfr_action_id,
5129 ln_asg_action_id);
5130
5131 ln_step := 15;
5132
5133 lv_serial_number := lv_master_action_type ||
5134 lv_master_sepcheck_flag || ln_source_action_id;
5135
5136 ln_step := 16;
5137
5138 update pay_assignment_actions
5139 set serial_number = lv_serial_number
5140 WHERE assignment_action_id = ln_child_xfr_action_id;
5141
5142 hr_utility.trace('Processing Child action ' ||
5143 p_xfr_action_id);
5144
5145 ELSE
5146 ln_step := 17;
5147 ln_child_xfr_action_id := p_xfr_action_id;
5148 END IF;
5149
5150 ln_earnings := 0;
5151 ln_step := 18;
5152
5153 -- IF gn_gross_earn_def_bal_id + gn_payments_def_bal_id <> 0 THEN
5154 IF gn_gross_earn_def_bal_id <> 0 THEN
5155
5156 IF ln_source_action_id = -999 THEN
5157
5158 ln_step := 19;
5159
5160 OPEN c_all_runs(ln_asg_action_id,
5161 ln_assignment_id,
5162 ln_tax_unit_id);
5163 LOOP
5164 FETCH c_all_runs INTO ln_all_run_asg_act_id;
5165 IF c_all_runs%NOTFOUND THEN
5166 EXIT;
5167 END IF;
5168
5169 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
5170 ln_earnings := NVL(pay_balance_pkg.get_value(
5171 gn_gross_earn_def_bal_id,
5172 ln_all_run_asg_act_id),0);
5173
5174 /**************************************************
5175 ** For Non-payroll Payments element is processed
5176 ** alone, the gross earning balance returns zero.
5177 ** In this case check payment.
5178 **************************************************/
5179
5180 -- IF ln_earnings = 0 THEN
5181
5182 -- ln_step := 20;
5183 -- ln_earnings := NVL(pay_balance_pkg.get_value(
5184 -- gn_payments_def_bal_id,
5185 -- ln_all_run_asg_act_id),0);
5186
5187 -- END IF;
5188
5189 IF ln_earnings <> 0 THEN
5190 EXIT;
5191 END IF;
5192
5193 END LOOP;
5194 CLOSE c_all_runs;
5195 ELSE
5196 ln_earnings := 1; -- For Separate Check
5197 END IF;
5198
5199 END IF;
5200
5201
5202 ln_step := 21;
5203 IF ln_earnings = 0 AND
5204 ln_xfr_assignment_id = ln_assignment_id AND
5205 pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' THEN
5206 ln_earnings := 1;
5207 lv_archive_balance_info := 'N';
5208 ELSE
5209 lv_archive_balance_info := 'Y';
5210 END IF;
5211
5212 IF ln_earnings <> 0 THEN
5213
5214 process_actions(p_xfr_payroll_action_id => ln_xfr_payroll_action_id
5215 ,p_xfr_action_id => ln_child_xfr_action_id
5216 ,p_pre_pay_action_id => ln_asg_action_id
5217 ,p_payment_action_id => ln_master_run_aa_id
5218 ,p_rqp_action_id => ln_rqp_action_id
5219 ,p_seperate_check_flag => lv_master_sepcheck_flag
5220 ,p_action_type => lv_master_action_type
5221 ,p_legislation_code => lv_legislation_code
5222 ,p_assignment_id => ln_assignment_id
5223 ,p_tax_unit_id => ln_tax_unit_id
5224 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
5225 ,p_xfr_start_date => ld_start_date
5226 ,p_xfr_end_date => ld_end_date
5227 ,p_ppp_source_action_id => ln_ppp_source_action_id
5228 ,p_archive_balance_info => lv_archive_balance_info
5229 );
5230 END IF;
5231
5232 END LOOP; -- c_payment_info
5233
5234 CLOSE c_payment_info;
5235
5236 hr_utility.trace('pay_ac_action_arch.g_xfr_run_exists = '||
5237 pay_ac_action_arch.g_xfr_run_exists );
5238
5239 /***
5240 ** Removed cursor c_get_term_asg as it gets executed when
5241 ** Multiple Payment is enabled for the payroll and it is not
5242 ** enabled for Mexico for now. Please check version 115.8
5243 ***/
5244
5245 END IF; /* P,U */
5246
5247
5248 ln_step := 24;
5249
5250 IF lv_master_action_type = 'V' THEN
5251 /* ln_asg_action_id is nothing but reversal run action id */
5252 ln_pymt_balcall_aaid := ln_asg_action_id ;
5253 hr_utility.trace('Reversal ln_pymt_balcall_aaid'
5254 ||to_char(ln_pymt_balcall_aaid));
5255 ln_step := 25;
5256 pay_ac_action_arch.initialization_process;
5257
5258 hr_utility.trace('Populating Tax Balances FOR Reversals');
5259 hr_utility.trace('ln_tax_unit_id : '||to_char(ln_tax_unit_id));
5260 hr_utility.trace('ln_pymt_balcall_aaid :'||to_char(ln_pymt_balcall_aaid));
5261 hr_utility.trace('ld_curr_pymt_eff_date :'||to_char(ld_curr_pymt_eff_date,'DD-MON-YYYY'));
5262 hr_utility.trace('ln_assignment_id :'||to_char(ln_assignment_id));
5263
5264
5265 ln_step := 26;
5266 populate_tax_and_summary( p_xfr_action_id => p_xfr_action_id
5267 ,p_assignment_id => ln_assignment_id
5268 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
5269 ,p_tax_unit_id => ln_tax_unit_id
5270 ,p_action_type => lv_master_action_type
5271 ,p_pymt_eff_date => ld_curr_pymt_eff_date
5272 ,p_start_date => ld_start_date
5273 ,p_end_date => ld_end_date
5274 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
5275 );
5276
5277 ln_step := 27;
5278 hr_utility.trace('Populating Current Elements FOR Reversals');
5279 get_current_elements(
5280 p_xfr_action_id => p_xfr_action_id
5281 ,p_curr_pymt_action_id => ln_pymt_balcall_aaid
5282 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
5283 ,p_assignment_id => ln_assignment_id
5284 ,p_tax_unit_id => ln_tax_unit_id
5285 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
5286 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
5287 ,p_sepchk_flag => lv_master_sepcheck_flag
5288 ,p_legislation_code => lv_legislation_code
5289 ,p_action_type => lv_master_action_type);
5290
5291 hr_utility.trace('Done Populating Tax Balances FOR Reversals');
5292 ln_step := 28;
5293 pay_emp_action_arch.insert_rows_thro_api_process(
5294 p_action_context_id => p_xfr_action_id
5295 ,p_action_context_type=> 'AAP'
5296 ,p_assignment_id => ln_assignment_id
5297 ,p_tax_unit_id => ln_tax_unit_id
5298 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
5299 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
5300 );
5301
5302 END IF; -- lv_master_action_type = 'V'
5303
5304
5305 ln_step := 29;
5306
5307 IF lv_master_action_type = 'B' THEN
5308 hr_utility.trace('Populating Current Elements FOR Balance Adjustments');
5309 /* ln_asg_action_id is nothing but Balance Adjustment run action id */
5310 ln_asg_action_id := -1;
5311 pay_ac_action_arch.initialization_process;
5312
5313 OPEN c_get_emp_adjbal(p_xfr_action_id);
5314 LOOP
5315 FETCH c_get_emp_adjbal INTO ln_asg_action_id;
5316 EXIT WHEN c_get_emp_adjbal%NOTFOUND;
5317
5318 ln_pymt_balcall_aaid := ln_asg_action_id ;
5319 hr_utility.trace('Bal Adjustment ln_pymt_balcall_aaid'
5320 ||to_char(ln_pymt_balcall_aaid));
5321
5322 ln_step := 30;
5323
5324 hr_utility.trace('ln_tax_unit_id : '||to_char(ln_tax_unit_id));
5325 hr_utility.trace('ln_pymt_balcall_aaid :'||to_char(ln_pymt_balcall_aaid));
5326 hr_utility.trace('ld_curr_pymt_eff_date :'||to_char(ld_curr_pymt_eff_date,'DD-MON-YYYY'));
5327 hr_utility.trace('ln_assignment_id :'||to_char(ln_assignment_id));
5328
5329 /* Need to pass Payslip Archiver Assignment_Action_id to
5330 p_curr_pymt_action_id because we have to archive Bal Adjustments
5331 that are not marked for 'Pre-Payment', Otherwise nothing
5332 will be archived. */
5333
5334 IF ln_asg_action_id <> -1 AND ln_asg_action_id IS NOT NULL THEN
5335 ln_step := 31;
5336 get_current_elements(
5337 p_xfr_action_id => p_xfr_action_id
5338 ,p_curr_pymt_action_id => p_xfr_action_id
5339 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
5340 ,p_assignment_id => ln_assignment_id
5341 ,p_tax_unit_id => ln_tax_unit_id
5342 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
5343 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
5344 ,p_sepchk_flag => lv_master_sepcheck_flag
5345 ,p_legislation_code => lv_legislation_code
5346 ,p_action_type => lv_master_action_type);
5347 END IF;
5348 END LOOP;
5349 CLOSE c_get_emp_adjbal;
5350
5351 ln_step := 32;
5352 pay_emp_action_arch.insert_rows_thro_api_process(
5353 p_action_context_id => p_xfr_action_id
5354 ,p_action_context_type=> 'AAP'
5355 ,p_assignment_id => ln_assignment_id
5356 ,p_tax_unit_id => ln_tax_unit_id
5357 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
5358 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
5359 );
5360
5361 END IF; -- master_action_type = 'B'
5362
5363
5364 /****************************************************************
5365 ** Archive all the payroll action level data once only when
5366 ** chunk number is 1. Also check if this has not been archived
5367 ** earlier
5368 *****************************************************************/
5369 hr_utility.set_location(gv_package || lv_procedure_name,210);
5370 ln_step := 33;
5371 OPEN c_check_pay_action(ln_xfr_payroll_action_id);
5372 FETCH c_check_pay_action INTO ln_pay_action_count;
5373 CLOSE c_check_pay_action;
5374 IF ln_pay_action_count = 0 THEN
5375 hr_utility.set_location(gv_package || lv_procedure_name,210);
5376 IF ln_chunk_number = 1 THEN
5377 ln_step := 34;
5378 arch_pay_action_level_data(
5379 p_payroll_action_id => ln_xfr_payroll_action_id
5380 ,p_payroll_id => ln_payroll_id
5381 ,p_effective_Date => ld_end_date
5382 );
5383 END IF;
5384
5385 END IF;
5386
5387 EXCEPTION
5388 WHEN OTHERS THEN
5389 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
5390 gv_package || lv_procedure_name;
5391
5392 hr_utility.trace(lv_error_message || '-' || SQLERRM);
5393
5394 lv_error_message :=
5395 pay_emp_action_arch.set_error_message(lv_error_message);
5396
5397 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5398 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5399 hr_utility.raise_error;
5400
5401 END archive_code;
5402
5403
5404 BEGIN
5405 --hr_utility.trace_on (NULL, 'MX_PAYSLIP_ARCHIVE');
5406 gv_package := 'pay_mx_payroll_arch';
5407 gv_dim_asg_gre_ytd := '_ASG_GRE_YTD';
5408 gv_dim_asg_jd_gre_ytd := '_ASG_JD_GRE_YTD';
5409 gv_ytd_balance_dimension := '_ASG_GRE_YTD';
5410
5411
5412 END pay_mx_payroll_arch;