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