[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_PAYROLL_ARCH
Source
1 PACKAGE BODY pay_ca_payroll_arch AS
2 /* $Header: pycapyar.pkb 120.5.12010000.1 2008/07/27 22:15:21 appldev ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_payroll_arch
21
22 Description : Generate
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ ------- --------------------------
30 15-Aug-2001 vpandya 115.0 Created.
31 Currently the balances are
32 first written to a local pl/sql
33 table and then the api is called to
34 write to pay_act_info.
35 Later procedures could be written
36 to make this part more readable.
37
38 One balance adjustment results in
39 nearly 10 payroll_actions and 10
40 assignment action currently.
41
42 Run Resullts maybe written for
43 only some of these assignment
44 actions. Currently the prog
45 loops for all 10 actions.
46 This can later be reduced.
47 02-Oct-2001 vpandya 115.1 Replace 'S' with 'STANDARD' in
48 c_asg_run_actions cursor.
49 05-Oct-2001 vpandya 115.2 Changed 'order by' clause in
50 c_asg_run_actions cursor.
51 25-Oct-2001 vpandya 115.3 Bug# 2077373, print 'Quebec Tax'
52 instead of 'PROV Withheld(QC)'.
53 changed populate_tax_balance,
54 printing lv_soe_short_name now
55 instead of lv_reporting_name.
56 30-Oct-2001 vpandya 115.4 Bug# 2085775, ln_prev_standard_aaid
57 was not getting initialize in
58 asg. act. creation. Now it has been
59 initialized in declare section and
60 after 'end loop' with zero'0'.
61 22-Jan-2002 vpandya 115.5 Changed package to take care of
62 Multi Assignment Processing.
63 action_creation and archive_data
64 procedures have been modified.
65 process_actions has been introduced.
66 Added dbdrv lines to meet new std.
67 24-Jan-2002 vpandya 115.6 Get rid of procedures
68 populate_summary, get_emp_residence
69 , insret_rows_thro_api_process,
70 get_multi_assignment_flag,
71 get_withholding_info and
72 update_ytd_withheld
73 Modified py_archive_date and
74 py_action_creation. Calling
75 get_multi_assignment_flag of
76 pay_ac_action_arch and
77 arch_pay_action_level_data of
78 pay_emp_action_arch.
79 19-FEB-2002 vpandya 115.7 Changed global variable name for
80 Multiple Assignment Payments.
81 12-Jun-2002 vpandya 115.8 Added
82 - procedure populate_fed_prov_bal
83 - get_context_value
84 Modified py_archinit, populating
85 PL/SQL table for defined balance id
86 for Tax Balances.
87 Modified py_archive_data,
88 added cursor cur_taxgrp to get
89 Tax Group Id and cursor cur_language
90 to get correspondance language of
91 person.
92 12-Jun-2002 vpandya 115.9 Modified populate_fed_prov_bal
93 archive jurisdiction_code as
94 '00-000-0000'
95 13-Jun-2002 vpandya 115.10 Modified get_context_value return
96 '-1' when 'No Tax Group' found.
97 24-Jun-2002 vpandya 115.11 Modified py_archinit to populate
98 PL/SQL table for all jurisdiction.
99 Also modified populate_fed_prov_bal
100 to archive taxes for all juris. Now
101 storing tax group id in the variable
102 gn_taxgrp_gre_id(static variable).
103 23-Jul-2002 vpandya 115.12 2476693 Setting context Tax Unit Id
104 for Non-Payroll Payment element.
105 20-NOV-2002 vpandya 115.14 Calling set_error_message function
106 of pay_emp_action_arch from all
107 exceptions to get error message
108 Remote Procedure Calls(RPC or Sub
109 program). Added exceptions in
110 all procedures and functions.
111 06-FEB-2003 vpandya 115.15 2657464 Changed for translation.
112 2705741 Getting base_language. If person's
113 2683634 correspondence language is not
114 US or FRC, or it is null then
115 setting base language as default.
116 10-FEB-2003 vpandya 115.16 Added two input paramters to
117 get_xfr_elements.
118 18-FEB-2003 vpandya 115.17 Added nocopy for gscc.
119 24-FEB-2003 vpandya 115.18 Added procedure
120 create_chld_act_for_multi_gre for
121 assignment action creation for
122 multi gre.
123 07-Mar-2003 vpandya 115.19 Changed procedure
124 create_chld_act_for_multi_gre, added
125 condition exit from the loop if
126 c_mst_prepay_act%notfound.
127 12-Mar-2003 vpandya 115.20 Changed proc create_child_actions
128 and create_chld_act_for_multi_gre:
129 added pay_org_payment_methods_f
130 to avoid to get pay_pre_payments of
131 'Third Party Payments'
132 02-Apr-2003 vpandya 115.21 2879620 Changed process_action:
133 Modified cursor c_time_period.
134 11-Apr-2003 vpandya 115.22 Changed archive_data:
135 create_child_actions_for_gre and
136 create_child_act_for_taxgrp.
137 Using view pay_payment_information_v
138 to archive assignments whether
139 it has zero and non zero payment.If
140 zero payment, then atleast earning
141 element has been processed.
142 28-Jul-2003 vpandya 115.23 3053917 Passing parameter
143 p_ytd_balcall_aaid to
144 get_personal_information.
145 10-Sep-2003 vpandya 115.24 Passing p_seperate_check_flag to
146 get_last_xfr_info as per teminated
147 asg changes done by ekim.
148 18-Sep-2003 vpandya 115.25 Changed range cursor to fix gscc
149 error on date conversion. Using
150 fnd_date.date_to_canonical instead
151 to_char and canonical_to_date
152 instead of to_date.
153 19-Jan-2004 vpandya 115.26 3356401 The SQL ID: 6194306 is for the
154 cursor c_prev_run_information, which
155 was in get_last_xfr_info procedure.
156 This procedure has been removed from
157 this package and same procedure of
158 pay_ac_action_arch is being called.
159 17-Apr-2004 rsethupa 115.27 3311866 SS Payslip Currency Format Enhancement
160 Current Amount and Ytd Amount for
161 category 'AC DEDUCTIONS' will be
162 archived in canonical format.
163 26-Apr-2004 rsethupa 115.28 3559626 In procedure process_actions,
164 assigned lv_person_lang to variable
165 pay_emp_action_arch.gv_correspondence_language
166 also.(For fetching Accrual Information
167 in the corresponding language)
168 02-Aug-2004 SSattini 115.29 3498653 Added functionality to archive
169 Balance adjustments and Reversals
170 for Canada legislation.
171 18-Oct-2004 SSattini 115.30 3940380 Added p_xfr_action_id parameter
172 to get_last_xfr_info procedure call
173 from process actions, part of fix
174 for bug#3940380.
175 26-Oct-2004 SSattini 115.31 3960157 Bugfix 3960157
176 02-Sep-2005 Saurgupt 115.33 4566656 Modified proc populate_fed_prov_bal.
177 Added 'PPIP EE Withheld' along with
178 QPP balances. Modified cur_def_bal,
179 added 'PPIP EE Withheld' in query.
180 26-APR-2006 ahanda 115.34 4675938 Changed priority for tax elements.
181 13-DEC-2006 meshah 115.36 5655448 changed action_creation, cursor
182 c_get_xfr_emp added a INDEX hint
183 and removed nvl for
184 consolidation_set.
185 18-JUL-2007 pganguly 115.37 6169715 Change the cursor cur_language,
186 added the missing date join with
187 per_people_f.
188
189 *******************************************************************/
190
191 /******************************************************************
192 ** Package Local Variables
193 ******************************************************************/
194 gv_package varchar2(100) := 'pay_ca_payroll_arch';
195 gn_taxgrp_gre_id NUMBER;
196 dbt def_bal_tbl;
197 tax tax_tbl;
198
199 /******************************************************************
200 Name : get_context_val
201 Purpose : This returns the conext value of given assignment action id
202 information for Canadian Payroll Archiver.
203 Arguments : p_context_name - Context Name
204 p_assignment_id - Assignment Id
205 p_asg_act_id - Assignment Action Id
206 ******************************************************************/
207 FUNCTION get_context_val( p_context_name in varchar2,
208 p_assignment_id in number,
209 p_asg_act_id in number)
210 RETURN varchar2 is
211 cursor cur_context is
212 select context_value
213 from pay_action_contexts pac,
214 ff_contexts fc
215 where pac.assignment_action_id = p_asg_act_id
216 and pac.assignment_id = p_assignment_id
217 and pac.context_id = fc.context_id
218 and fc.context_name = p_context_name;
219
220 lv_context_value varchar2(80);
221
222 lv_error_message VARCHAR2(500);
223 lv_procedure_name VARCHAR2(100);
224 ln_step NUMBER;
225
226 begin
227 lv_context_value := '-1';
228 ln_step := 1;
229 lv_procedure_name := '.get_context_value';
230 hr_utility.set_location(gv_package || lv_procedure_name, 10);
231
232 open cur_context;
233 fetch cur_context into lv_context_value;
234 close cur_context;
235
236 hr_utility.set_location(gv_package || lv_procedure_name, 20);
237 ln_step := 2;
238 if lv_context_value = 'No Tax Group' then
239 lv_context_value := '-1';
240 end if;
241 return(lv_context_value);
242
243 EXCEPTION
244 when others then
245 hr_utility.set_location(gv_package || lv_procedure_name, 500);
246 lv_error_message := 'Error at step ' || ln_step ||
247 ' in ' || gv_package || lv_procedure_name;
248 hr_utility.trace(lv_error_message || '-' || sqlerrm);
249
250 lv_error_message :=
251 pay_emp_action_arch.set_error_message(lv_error_message);
252
253 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
254 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
255 hr_utility.raise_error;
256 return(lv_context_value);
257
258 end get_context_val;
259
260
261 /******************************************************************
262 Name : get_taxgroup_val (Added new function to fix bug#3498653)
263 Purpose : This returns the tax group conext value for a given
264 assignment_id, assignment action id. If tax group
265 context value is not found for the assignment then
266 it gets the value from the GRE definition based on
267 the tax_unit_id passed.
268 Arguments : p_tax_unit_id - Tax Unit Id
269 p_assignment_id - Assignment Id
270 p_asg_act_id - Assignment Action Id
271 ******************************************************************/
272 FUNCTION get_taxgroup_val( p_tax_unit_id in number,
273 p_assignment_id in number,
274 p_asg_act_id in number)
275 RETURN number is
276 cursor cur_taxgrp is
277 select org_information4
278 from hr_organization_information hoi
279 where hoi.org_information_context = 'Canada Employer Identification'
280 and hoi.organization_id = p_tax_unit_id;
281
282 ln_taxgrp_gre_id number;
283
284 Begin
285
286 ln_taxgrp_gre_id := -1;
287 ln_taxgrp_gre_id := get_context_val(
288 p_context_name => 'TAX_GROUP'
289 , p_assignment_id => p_assignment_id
290 , p_asg_act_id => p_asg_act_id);
291
292 if ( ln_taxgrp_gre_id = -1 or ln_taxgrp_gre_id is null ) then
293 open cur_taxgrp;
294 fetch cur_taxgrp into ln_taxgrp_gre_id;
295 close cur_taxgrp;
296 end if;
297
298 return ln_taxgrp_gre_id;
299
300 End get_taxgroup_val;
301
302
303 /******************************************************************
304 Name : get_payroll_action_info
305 Purpose : This returns the Payroll Action level
306 information for Canadian Payroll Archiver.
307 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
308 p_start_date - Start date of Archiver
309 p_end_date - End date of Archiver
310 p_business_group_id - Business Group ID
311 p_cons_set_id - Consolidation Set when submitting Archiver
312 p_payroll_id - Payroll ID when submitting Archiver
313 ******************************************************************/
314 PROCEDURE get_payroll_action_info( p_payroll_action_id in number
315 ,p_end_date out nocopy date
316 ,p_start_date out nocopy date
317 ,p_business_group_id out nocopy number
318 ,p_cons_set_id out nocopy number
319 ,p_payroll_id out nocopy number
320 )
321
322 IS
323 cursor c_payroll_action_info
324 (cp_payroll_action_id in number) is
325 select effective_date,
326 start_date,
327 business_group_id,
328 to_number(substr(legislative_parameters,
329 instr(legislative_parameters,
330 'TRANSFER_CONSOLIDATION_SET_ID=')
331 + length('TRANSFER_CONSOLIDATION_SET_ID='))),
332 to_number(ltrim(rtrim(substr(legislative_parameters,
333 instr(legislative_parameters,
334 'TRANSFER_PAYROLL_ID=')
335 + length('TRANSFER_PAYROLL_ID='),
336 (instr(legislative_parameters,
337 'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
338 - (instr(legislative_parameters,
339 'TRANSFER_PAYROLL_ID=')
340 + length('TRANSFER_PAYROLL_ID='))))))
341 from pay_payroll_actions
342 where payroll_action_id = cp_payroll_action_id;
343
344 ld_end_date DATE;
345 ld_start_date DATE;
346 ln_business_group_id NUMBER;
347 ln_cons_set_id NUMBER;
348 ln_payroll_id NUMBER;
349
350 lv_error_message VARCHAR2(500);
351 lv_procedure_name VARCHAR2(100);
352 ln_step NUMBER;
353
354 BEGIN
355
356 ln_step := 1;
357 lv_procedure_name := '.get_payroll_action_info';
358 hr_utility.set_location(gv_package || lv_procedure_name, 10);
359 open c_payroll_action_info(p_payroll_action_id);
360 fetch c_payroll_action_info into ld_end_date,
361 ld_start_date,
362 ln_business_group_id,
363 ln_cons_set_id,
364 ln_payroll_id;
365 close c_payroll_action_info;
366
367 ln_step := 2;
368 hr_utility.set_location(gv_package || lv_procedure_name, 30);
369 p_end_date := ld_end_date;
370 p_start_date := ld_start_date;
371 p_business_group_id := ln_business_group_id;
372 p_cons_set_id := ln_cons_set_id;
373 p_payroll_id := ln_payroll_id;
374 hr_utility.set_location(gv_package || lv_procedure_name, 50);
375
376 EXCEPTION
377 when others then
378 hr_utility.set_location(gv_package || lv_procedure_name, 500);
379 lv_error_message := 'Error at step ' || ln_step ||
380 ' in ' || gv_package || lv_procedure_name;
381 hr_utility.trace(lv_error_message || '-' || sqlerrm);
382
383 lv_error_message :=
384 pay_emp_action_arch.set_error_message(lv_error_message);
385
386 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
387 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
388 hr_utility.raise_error;
389
390 END get_payroll_action_info;
391
392
393 PROCEDURE populate_fed_prov_bal( p_xfr_action_id in number
394 ,p_assignment_id in number
395 ,p_pymt_balcall_aaid in number
396 ,p_tax_unit_id in number
397 ,p_action_type in varchar2
398 ,p_pymt_eff_date in date
399 ,p_start_date in date
400 ,p_end_date in date
401 ,p_ytd_balcall_aaid in number
402 )
403 IS
404
405 ln_pymt_amount number;
406 ln_ytd_amount number;
407 lv_reporting_name varchar2(150);
408 lv_lookup_code varchar2(150);
409
410 i number;
411 j number;
412
413 ln_index number;
414 ln_element_index number;
415
416 lv_error_message VARCHAR2(500);
417 lv_procedure_name VARCHAR2(100);
418 ln_step NUMBER;
419
420 BEGIN
421 ln_step := 1;
422 lv_procedure_name := '.populate_fed_prov_bal';
423 hr_utility.set_location(gv_package || lv_procedure_name, 10);
424 i := 0;
425 j := 0;
426
427 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
428 pay_balance_pkg.set_context('TAX_GROUP', gn_taxgrp_gre_id);
429 else
430 pay_balance_pkg.set_context('TAX_UNIT_ID', gn_taxgrp_gre_id);
431 end if;
432
433 hr_utility.set_location(gv_package || lv_procedure_name, 20);
434 ln_step := 2;
435 for i in dbt.first..dbt.last loop
436
437 ln_pymt_amount := 0;
438 ln_ytd_amount := 0;
439
440 if dbt(i).bal_name in ('PROV Withheld', 'QPP EE Withheld' , 'PPIP EE Withheld') -- 4566656
441 then
442 ln_step := 3;
443 pay_balance_pkg.set_context('JURISDICTION_CODE'
444 ,dbt(i).jurisdiction_cd);
445 lv_lookup_code := upper(dbt(i).bal_name|| '(' ||
446 dbt(i).jurisdiction_cd || ')');
447 else
448 ln_step := 4;
449 lv_lookup_code := upper(dbt(i).bal_name);
450 end if;
451
452 /* Added this extra validation for reversals in Canada
453 if action_type is 'V' then the run_def_bal_id should
454 be used to get ln_pymt_amount, otherwise the pymt_def_bal_id
455 should be used to get ln_pymt_amount. Bug#3498653 */
456 if p_action_type in ('V','B') then
457 if dbt(i).run_def_bal_id is not null then
458 ln_step := 5;
459 ln_pymt_amount := nvl(pay_balance_pkg.get_value(
460 dbt(i).run_def_bal_id,
461 p_pymt_balcall_aaid),0);
462 end if;
463 else
464 /* old code before adding run_def_bal_id */
465 if dbt(i).pymt_def_bal_id is not null then
466 ln_step := 5;
467 ln_pymt_amount := nvl(pay_balance_pkg.get_value(
468 dbt(i).pymt_def_bal_id,
469 p_pymt_balcall_aaid),0);
470 end if;
471 end if; -- p_action_type = 'V'
472
473 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
474 ln_step := 6;
475 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
476 dbt(i).tg_ytd_def_bal_id,
477 p_ytd_balcall_aaid),0);
478 else
479 ln_step := 7;
480 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
481 dbt(i).gre_ytd_def_bal_id,
482 p_ytd_balcall_aaid),0);
483 end if;
484
485 hr_utility.set_location(gv_package || lv_procedure_name, 30);
486 ln_step := 8;
487 if ( ln_pymt_amount + ln_ytd_amount <> 0 ) then
488
489
490 hr_utility.trace('lv_lookup_code : '||lv_lookup_code);
491 hr_utility.set_location(gv_package || lv_procedure_name, 40);
492
493 ln_step := 9;
494 j := 0;
495 for j in tax.first..tax.last loop
496 if tax(j).language = pay_ac_action_arch.gv_person_lang and
497 tax(j).lookup_code = lv_lookup_code
498 then
499 lv_reporting_name := tax(j).meaning;
500 exit;
501 end if;
502 end loop;
503
504 /*Insert this into the plsql table */
505 hr_utility.trace('Tax Balance Name : '|| dbt(i).bal_name );
506 hr_utility.trace('lv_reporting_name : '||lv_reporting_name);
507 hr_utility.set_location(gv_package || lv_procedure_name, 50);
508
509 ln_step := 10;
510 ln_index := pay_ac_action_arch.lrr_act_tab.count;
511
512 hr_utility.trace('ln_index is '
513 || pay_ac_action_arch.lrr_act_tab.count);
514
515 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
516 := 'AC DEDUCTIONS';
517 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
518 := dbt(i).jurisdiction_cd;
519 -- pay_ac_action_arch.lrr_act_tab(ln_index).effective_date
520 -- := p_pymt_eff_date;
521 --pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
522 --:= p_element_type_id;
523 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
524 := p_xfr_action_id;
525 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
526 := 'Tax Deductions';
527 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
528 := dbt(i).bal_type_id;
529 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
530 := dbt(i).disp_sequence;
531 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
532 := fnd_number.number_to_canonical(ln_pymt_amount); /*Bug 3311866*/
533 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
534 := fnd_number.number_to_canonical(ln_ytd_amount);
535 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
536 := lv_reporting_name; --lv_reporting_name bug#2077373;
537
538 hr_utility.set_location(gv_package || lv_procedure_name, 60);
539
540 ln_step := 11;
541 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
542 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_classfn
543 := 'Tax Deductions';
544 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
545 := dbt(i).jurisdiction_cd;
546 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
547 := dbt(i).bal_name;
548 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
549 := dbt(i).bal_type_id;
550
551 hr_utility.set_location(gv_package || lv_procedure_name, 70);
552
553
554 end if;
555
556 end loop;
557
558 hr_utility.set_location(gv_package || lv_procedure_name, 80);
559
560 EXCEPTION
561 when others then
562 hr_utility.set_location(gv_package || lv_procedure_name, 500);
563 lv_error_message := 'Error at step ' || ln_step ||
564 ' in ' || gv_package || lv_procedure_name;
565 hr_utility.trace(lv_error_message || '-' || sqlerrm);
566
567 lv_error_message :=
568 pay_emp_action_arch.set_error_message(lv_error_message);
569
570 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
571 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
572 hr_utility.raise_error;
573
574 END populate_fed_prov_bal;
575
576 --payslip
577
578 PROCEDURE get_last_pymt_info(p_assignment_id in number
579 ,p_curr_pymt_eff_date in date
580 ,p_last_pymt_eff_date out nocopy date
581 ,p_last_pymt_ass_act_id out nocopy number
582 )
583
584
585 IS
586
587 cursor c_last_payment_info(cp_assignment_id in number
588 ,cp_curr_pymt_eff_date in date) is
589
590 select ppa.effective_date, paa.assignment_action_id
591 from pay_payroll_actions ppa,
592 pay_assignment_actions paa
593 where paa.assignment_id = p_assignment_id
594 and ppa.payroll_action_id = paa.payroll_action_id
595 and ppa.action_type in ('U','P')
596 and ppa.effective_date < p_curr_pymt_eff_date
597 and ppa.effective_date in
598 ( select max(ppa1.effective_date)
599 from pay_payroll_actions ppa1,
600 pay_assignment_actions paa1
601 where ppa1.payroll_action_id = paa1.payroll_action_id
602 and ppa1.action_type in ('U','P')
603 and paa1.assignment_id = p_assignment_id
604 and ppa1.effective_date < p_curr_pymt_eff_date);
605
606
607 ld_last_pymt_eff_date date;
608 ln_last_pymt_ass_act_id number;
609
610 lv_error_message VARCHAR2(500);
611 lv_procedure_name VARCHAR2(100);
612 ln_step NUMBER;
613
614 BEGIN
615
616 hr_utility.trace('Entering get_last_pymt_info');
617 lv_procedure_name := '.get_last_pymt_info';
618
619 ln_step := 1;
620 open c_last_payment_info(p_assignment_id,p_curr_pymt_eff_date);
621
622 fetch c_last_payment_info INTO ld_last_pymt_eff_date,
623 ln_last_pymt_ass_act_id ;
624
625 if c_last_payment_info%NOTFOUND then
626
627 hr_utility.trace('This process has not been run earlier');
628
629 end if;
630 close c_last_payment_info ;
631
632
633 p_last_pymt_eff_date := ld_last_pymt_eff_date;
634 p_last_pymt_ass_act_id := ln_last_pymt_ass_act_id;
635
636 hr_utility.trace('Leaving get_last_pymt_info');
637
638 EXCEPTION
639 when others then
640 hr_utility.set_location(gv_package || lv_procedure_name, 500);
641 lv_error_message := 'Error at step ' || ln_step ||
642 ' in ' || gv_package || lv_procedure_name;
643 hr_utility.trace(lv_error_message || '-' || sqlerrm);
644
645 lv_error_message :=
646 pay_emp_action_arch.set_error_message(lv_error_message);
647
648 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
649 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
650 hr_utility.raise_error;
651
652 END get_last_pymt_info;
653
654
655 /************************************************************
656 Name : process_actions
657 Purpose :
658 Arguments : p_rqp_action_id - For Child actions we pass the
659 Action ID of Run/Quick Pay
660 - For Master we pass the Action ID
661 of Pre Payment Process.
662 Notes :
663 ************************************************************/
664 PROCEDURE process_actions( p_xfr_payroll_action_id in number
665 ,p_xfr_action_id in number
666 ,p_pre_pay_action_id in number
667 ,p_payment_action_id in number
668 ,p_rqp_action_id in number
669 ,p_seperate_check_flag in varchar2 default 'N'
670 ,p_sepcheck_run_type_id in number
671 ,p_action_type in varchar2
672 ,p_legislation_code in varchar2
673 ,p_assignment_id in number
674 ,p_tax_unit_id in number
675 ,p_curr_pymt_eff_date in date
676 ,p_xfr_start_date in date
677 ,p_xfr_end_date in date
678 ,p_ppp_source_action_id in number default null
679 ,p_archive_balance_info in varchar2 default 'Y' -- Bug 3960157
680 )
681 IS
682
683 /* Modified c_ytd_aaid because when we run the balance_adjustments
684 with pre-payments to pickup balance adjustments 'B',
685 in this case source_action_id will be null. The balance adjustments
686 should be archived when the pre-payments is ran or when pre-payments
687 is not ran. Bug#3498653 */
688 cursor c_ytd_aaid(cp_prepayment_action_id in number
689 ,cp_assignment_id in number
690 ,cp_sepchk_run_type in number) is
691 select paa.assignment_action_id
692 from pay_assignment_actions paa,
693 pay_action_interlocks pai,
694 pay_payroll_actions ppa
695 where pai.locking_action_id = cp_prepayment_action_id
696 and paa.assignment_action_id = pai.locked_action_id
697 and paa.assignment_id = cp_assignment_id
698 and ppa.payroll_action_id = paa.payroll_action_id
699 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
700 and not exists ( select 1
701 from pay_run_types_f prt
702 where prt.legislation_code = 'CA'
703 and prt.run_type_id = nvl(paa.run_type_id,0)
704 and prt.run_method = 'C' )
705 and ((paa.source_action_id is not null) OR
706 (ppa.action_type = 'B' and paa.source_action_id is null))
707 /* and paa.source_action_id is not null -- old code */
708 order by paa.action_sequence desc;
709
710 cursor c_ytd_aaid_for_gre(cp_prepayment_action_id in number
711 ,cp_assignment_id in number
712 ,cp_tax_unit_id in number
713 ,cp_sepchk_run_type in number) is
714 select paa.assignment_action_id
715 from pay_assignment_actions paa,
716 pay_action_interlocks pai,
717 pay_payroll_actions ppa
718 where pai.locking_action_id = cp_prepayment_action_id
719 and paa.assignment_action_id = pai.locked_action_id
720 and paa.assignment_id = cp_assignment_id
721 and paa.tax_unit_id = cp_tax_unit_id
722 and ppa.payroll_action_id = paa.payroll_action_id
723 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
724 and not exists ( select 1
725 from pay_run_types_f prt
726 where prt.legislation_code = 'CA'
727 and prt.run_type_id = nvl(paa.run_type_id,0)
728 and prt.run_method = 'C' )
729 and paa.source_action_id is not null
730 order by paa.action_sequence desc;
731
732 cursor c_time_period(cp_run_assignment_action in number) is
733 select ptp.time_period_id,
734 ppa.date_earned,
735 ppa.effective_date
736 from pay_assignment_actions paa,
737 pay_payroll_actions ppa,
738 per_time_periods ptp
739 where paa.assignment_action_id = cp_run_assignment_action
740 and ppa.payroll_action_id = paa.payroll_action_id
741 and ptp.payroll_id = ppa.payroll_id
742 and ppa.date_earned between ptp.start_date and ptp.end_date;
743
744 cursor cur_language is
745 select ppf.correspondence_language person_language
746 from per_assignments_f paf
747 , per_people_f ppf
748 where paf.assignment_id = p_assignment_id
749 and p_curr_pymt_eff_date between paf.effective_start_date
750 and paf.effective_end_date
751 and ppf.person_id = paf.person_id
752 and p_curr_pymt_eff_date between ppf.effective_start_date
753 and ppf.effective_end_date;
754
755 cursor cur_taxgrp is
756 select org_information4
757 from hr_organization_information hoi
758 where hoi.org_information_context = 'Canada Employer Identification'
759 and hoi.organization_id = p_tax_unit_id;
760
761 cursor cur_get_base_lang is
762 select language_code
763 from fnd_languages
764 where installed_flag = 'B';
765
766 ln_ytd_balcall_aaid NUMBER;
767 ld_run_date_earned DATE;
768 ld_run_effective_date DATE;
769
770 ld_last_xfr_eff_date DATE;
771 ln_last_xfr_action_id NUMBER;
772 ld_last_pymt_eff_date DATE;
773 ln_last_pymt_action_id NUMBER;
774
775 ln_time_period_id NUMBER;
776
777 ln_taxgrp_gre_id number;
778 lv_person_lang varchar2(30);
779
780 lv_base_lang varchar2(30);
781
782 lv_error_message VARCHAR2(500);
783 lv_procedure_name VARCHAR2(100);
784 ln_step NUMBER;
785
786 BEGIN
787 lv_procedure_name := '.process_actions';
788 lv_person_lang := 'US';
789 ln_taxgrp_gre_id := -1;
790
791 hr_utility.set_location(gv_package || lv_procedure_name, 10);
792 ln_step := 1;
793
794 /****************************************************************
795 ** For Seperate Check we do the YTD balance calls with the Run
796 ** Action ID. So, we do not need to get the max. action which is
797 ** not seperate Check.
798 ** Also, p_ppp_source_action_id is set to null as we want to get
799 ** all records from pay_pre_payments where source_action_id is
800 ** is null.
801 ****************************************************************/
802 ln_ytd_balcall_aaid := p_payment_action_id;
803 if p_action_type in ('U', 'P') then
804
805 if p_seperate_check_flag = 'N' and p_archive_balance_info <> 'N' then
806
807 -- if pay_ac_action_arch.gv_reporting_level = 'GRE' then
808 --
809 -- hr_utility.set_location(gv_package || lv_procedure_name, 40);
810 -- ln_step := 2;
811 -- open c_ytd_aaid_for_gre(p_rqp_action_id,
812 -- p_assignment_id,
813 -- p_tax_unit_id,
814 -- p_sepcheck_run_type_id);
815 -- fetch c_ytd_aaid_for_gre into ln_ytd_balcall_aaid;
816 -- if c_ytd_aaid_for_gre%notfound then
817 -- hr_utility.set_location(gv_package || lv_procedure_name, 50);
818 -- hr_utility.raise_error;
819 -- end if;
820 -- close c_ytd_aaid_for_gre;
821 --
822 -- else
823
824 hr_utility.set_location(gv_package || lv_procedure_name, 40);
825 ln_step := 2;
826 open c_ytd_aaid(p_rqp_action_id,
827 p_assignment_id,
828 p_sepcheck_run_type_id);
829 fetch c_ytd_aaid into ln_ytd_balcall_aaid;
830 --if c_ytd_aaid%notfound then
831 -- hr_utility.set_location(gv_package || lv_procedure_name, 50);
832 -- hr_utility.raise_error;
833 --end if;
834 close c_ytd_aaid;
835
836 -- end if;
837
838 end if;
839
840 end if;
841
842 hr_utility.set_location(gv_package || lv_procedure_name, 60);
843
844 ln_step := 3;
845 -- if p_seperate_check_flag = 'N' and p_archive_balance_info <> 'N' then
846 open c_time_period(ln_ytd_balcall_aaid);
847 fetch c_time_period into ln_time_period_id,
848 ld_run_date_earned,
849 ld_run_effective_date;
850 close c_time_period;
851
852 hr_utility.set_location(gv_package || lv_procedure_name, 70);
853 -- end if;
854 open cur_get_base_lang;
855 fetch cur_get_base_lang into lv_base_lang;
856 close cur_get_base_lang;
857
858 ln_step := 4;
859 open cur_language;
860 fetch cur_language into lv_person_lang;
861 if cur_language%notfound then
862 lv_person_lang := lv_base_lang;
863 end if;
864 close cur_language;
865
866 if ( ( lv_person_lang not in ( 'US', 'FRC' ) ) or
867 ( lv_person_lang is null ) ) then
868
869 lv_person_lang := lv_base_lang;
870
871 end if;
872
873 pay_ac_action_arch.gv_person_lang := lv_person_lang;
874 /* Bug 3559626 */
875 pay_emp_action_arch.gv_correspondence_language := lv_person_lang;
876
877 hr_utility.trace('Correspondance Lang: '|| pay_ac_action_arch.gv_person_lang);
878 hr_utility.trace('ln_ytd_balcall_aaid: '|| ln_ytd_balcall_aaid);
879
880 -- if p_seperate_check_flag = 'N' and p_archive_balance_info <> 'N' then
881 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
882 ln_step := 5;
883 /* removed old code to use the get_taxgroup_val function bug#3498653*/
884
885 ln_taxgrp_gre_id := get_taxgroup_val(
886 p_tax_unit_id => p_tax_unit_id,
887 p_assignment_id => p_assignment_id,
888 p_asg_act_id => ln_ytd_balcall_aaid);
889 else
890 ln_taxgrp_gre_id := p_tax_unit_id;
891 end if;
892
893 gn_taxgrp_gre_id := ln_taxgrp_gre_id;
894
895 hr_utility.trace('Reporting Level : '|| pay_ac_action_arch.gv_reporting_level);
896 hr_utility.trace('gv_taxgrp_gre_id : '|| gn_taxgrp_gre_id);
897
898 ln_step := 7;
899 gv_jurisdiction_cd := get_context_val(
900 p_context_name => 'JURISDICTION_CODE'
901 , p_assignment_id => p_assignment_id
902 , p_asg_act_id => ln_ytd_balcall_aaid);
903
904 hr_utility.trace('gv_jurisdiction_cd : ' || gv_jurisdiction_cd);
905
906 ln_step := 8;
907
908 -- end if;
909 -- Added p_xfr_action_id parameter part of fix for bug#3940380
910 pay_ac_action_arch.get_last_xfr_info(
911 p_assignment_id => p_assignment_id
912 ,p_curr_effective_date => p_xfr_end_date
913 ,p_action_info_category=> 'EMPLOYEE DETAILS'
914 ,p_xfr_action_id => p_xfr_action_id
915 ,p_sepchk_flag => p_seperate_check_flag
916 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
917 ,p_last_xfr_action_id => ln_last_xfr_action_id
918 );
919
920 hr_utility.trace('p_xfr_payroll_action_id= '|| p_xfr_payroll_action_id);
921 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
922 hr_utility.trace('p_seperate_check_flag = ' || p_seperate_check_flag);
923 hr_utility.trace('p_action_type = ' || p_action_type);
924 hr_utility.trace('p_pre_pay_action_id = ' || p_pre_pay_action_id);
925 hr_utility.trace('p_payment_action_id = ' || p_payment_action_id);
926 hr_utility.trace('p_rqp_action_id = ' || p_rqp_action_id);
927 hr_utility.trace('p_sepcheck_run_type_id = '|| p_sepcheck_run_type_id);
928 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
929 hr_utility.trace('p_xfr_start_date = ' || p_xfr_start_date );
930 hr_utility.trace('p_xfr_end_date = ' || p_xfr_end_date );
931 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
932 hr_utility.trace('ld_run_effective_date = ' || ld_run_effective_date);
933 hr_utility.trace('ln_ytd_balcall_aaid = ' || ln_ytd_balcall_aaid);
934 hr_utility.trace('p_ppp_source_action_id = '|| p_ppp_source_action_id);
935 hr_utility.trace('ld_run_date_earned = ' || ld_run_date_earned);
936 hr_utility.trace('ld_last_xfr_eff_date = ' || ld_last_xfr_eff_date);
937 hr_utility.trace('ln_last_xfr_action_id = ' || ln_last_xfr_action_id);
938
939 ln_step := 9;
940 pay_ac_action_arch.initialization_process;
941
942 ln_step := 10;
943
944 if p_archive_balance_info = 'Y' then -- Bug 3960157
945 populate_fed_prov_bal( p_xfr_action_id => p_xfr_action_id
946 ,p_assignment_id => p_assignment_id
947 ,p_pymt_balcall_aaid => p_payment_action_id
948 ,p_tax_unit_id => p_tax_unit_id
949 ,p_action_type => p_action_type
950 ,p_pymt_eff_date => p_curr_pymt_eff_date
951 ,p_start_date => p_xfr_start_date
952 ,p_end_date => p_xfr_end_date
953 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
954 );
955
956 ln_step := 11;
957 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
958 /****************************************************
959 ** Need to set Tax Unit Id context for Non-Payroll
960 ** Payment element has been processed when Reporting
961 ** Level is Tax Group.
962 ****************************************************/
963 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
964 end if;
965
966 hr_utility.set_location(gv_package || lv_procedure_name, 90);
967
968 /******************************************************************
969 ** For seperate check cases, the ld_last_xfr_eff_date is never null
970 ** as the master is always processed before the child actions. The
971 ** master data is already in the archive table and as it is in the
972 ** same session the process will always go to the else statement
973 ******************************************************************/
974 ln_step := 12;
975 -- if p_seperate_check_flag = 'N' and p_archive_balance_info <> 'N' then
976 if ld_last_xfr_eff_date is null then
977 hr_utility.set_location(gv_package || lv_procedure_name, 100);
978 ln_step := 13;
979 pay_ac_action_arch.first_time_process(
980 p_xfr_action_id => p_xfr_action_id
981 ,p_assignment_id => p_assignment_id
982 ,p_curr_pymt_action_id => p_rqp_action_id --PP
983 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
984 ,p_curr_eff_date => p_xfr_end_date
985 ,p_tax_unit_id => p_tax_unit_id
986 ,p_pymt_balcall_aaid => p_payment_action_id --SM
987 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid --MM
988 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
989 ,p_sepchk_flag => p_seperate_check_flag
990 ,p_legislation_code => p_legislation_code
991 );
992
993 else
994 ln_step := 14;
995 get_last_pymt_info(p_assignment_id,
996 p_curr_pymt_eff_date,
997 ld_last_pymt_eff_date,
998 ln_last_pymt_action_id
999 );
1000
1001 ln_step := 15;
1002 pay_ac_action_arch.get_current_elements(
1003 p_xfr_action_id => p_xfr_action_id
1004 ,p_curr_pymt_action_id => p_rqp_action_id
1005 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1006 ,p_assignment_id => p_assignment_id
1007 ,p_tax_unit_id => p_tax_unit_id
1008 ,p_pymt_balcall_aaid => p_payment_action_id
1009 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
1010 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
1011 ,p_sepchk_flag => p_seperate_check_flag
1012 ,p_legislation_code => p_legislation_code);
1013
1014 ln_step := 16;
1015 pay_ac_action_arch.get_xfr_elements(
1016 p_xfr_action_id => p_xfr_action_id
1017 ,p_last_xfr_action_id => ln_last_xfr_action_id
1018 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
1019 ,p_pymt_eff_date => p_curr_pymt_eff_date
1020 ,p_legislation_code => p_legislation_code
1021 ,p_sepchk_flag => p_seperate_check_flag
1022 ,p_assignment_id => p_assignment_id);
1023
1024 if ld_last_pymt_eff_date <> p_curr_pymt_eff_date then
1025 ln_step := 17;
1026 pay_ac_action_arch.get_missing_xfr_info(
1027 p_xfr_action_id => p_xfr_action_id
1028 ,p_tax_unit_id => p_tax_unit_id
1029 ,p_assignment_id => p_assignment_id
1030 ,p_last_pymt_action_id => ln_last_pymt_action_id
1031 ,p_last_pymt_eff_date => ld_last_pymt_eff_date
1032 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
1033 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
1034 ,p_pymt_eff_date => p_curr_pymt_eff_date
1035 ,p_legislation_code => p_legislation_code);
1036 end if;
1037
1038 end if;
1039 -- end if;
1040 ln_step := 19;
1041 pay_ac_action_arch.populate_summary(
1042 p_xfr_action_id => p_xfr_action_id);
1043
1044 end if;
1045
1046
1047 pay_emp_action_arch.get_personal_information(
1048 p_payroll_action_id => p_xfr_payroll_action_id
1049 ,p_assactid => p_xfr_action_id
1050 ,p_assignment_id => p_assignment_id
1051 ,p_curr_pymt_ass_act_id => p_pre_pay_action_id
1052 ,p_curr_eff_date => p_xfr_end_date
1053 ,p_date_earned => ld_run_date_earned
1054 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1055 ,p_tax_unit_id => p_tax_unit_id
1056 ,p_time_period_id => ln_time_period_id
1057 ,p_ppp_source_action_id => p_ppp_source_action_id
1058 ,p_run_action_id => p_payment_action_id
1059 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
1060 );
1061
1062
1063 hr_utility.set_location(gv_package || lv_procedure_name, 160);
1064
1065 pay_emp_action_arch.insert_rows_thro_api_process(
1066 p_action_context_id => p_xfr_action_id
1067 ,p_action_context_type=> 'AAP'
1068 ,p_assignment_id => p_assignment_id
1069 ,p_tax_unit_id => p_tax_unit_id
1070 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1071 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
1072 );
1073
1074 EXCEPTION
1075 when others then
1076 hr_utility.set_location(gv_package || lv_procedure_name, 500);
1077 lv_error_message := 'Error at step ' || ln_step ||
1078 ' in ' || gv_package || lv_procedure_name;
1079 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1080
1081 lv_error_message :=
1082 pay_emp_action_arch.set_error_message(lv_error_message);
1083
1084 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1085 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1086 hr_utility.raise_error;
1087
1088 END process_actions;
1089
1090 /************************************************************
1091 Name : create_child_act_for_taxgrp
1092 Purpose : This function creates child assignment actions.
1093 These action would be created for normal cheques
1094 as well as separate cheques for Single GRE/
1095 Multi Assignment / Multi GRE when Payroll
1096 Aerchiver Level is set to 'TAXGRP' and this
1097 also creates locking records
1098 Arguments :
1099 Notes :
1100 ************************************************************/
1101 PROCEDURE create_child_act_for_taxgrp(
1102 p_xfr_payroll_action_id in number
1103 ,p_master_xfr_action_id in number
1104 ,p_master_prepay_action_id in number
1105 ,p_master_action_type in varchar2
1106 ,p_sepchk_run_type_id in number
1107 ,p_legislation_code in varchar2
1108 ,p_assignment_id in number
1109 ,p_tax_unit_id in number
1110 ,p_curr_pymt_eff_date in date
1111 ,p_xfr_start_date in date
1112 ,p_xfr_end_date in date
1113 ,p_chunk in number
1114 )
1115 IS
1116
1117 cursor c_payment_info(cp_prepay_action_id number) is
1118 select distinct assignment_id
1119 ,nvl(source_action_id,-999)
1120 from pay_payment_information_v
1121 where assignment_action_id = cp_prepay_action_id
1122 order by 2,1;
1123
1124 cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1125 ,cp_assignment_id number
1126 ,cp_tax_unit_id number) is
1127 select assignment_action_id
1128 from pay_assignment_actions
1129 where source_action_id = cp_prepay_action_id
1130 and assignment_id = cp_assignment_id
1131 and tax_unit_id = cp_tax_unit_id;
1132
1133 cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1134 select paa.assignment_action_id
1135 from pay_action_interlocks pai
1136 ,pay_assignment_actions paa
1137 ,pay_payroll_actions ppa
1138 where pai.locked_action_id = cp_source_action_id
1139 and paa.assignment_action_id = pai.locking_action_id
1140 and paa.source_action_id is not null
1141 and ppa.payroll_action_id = paa.payroll_action_id
1142 and ppa.action_type in ( 'P', 'U' );
1143
1144 /* Modified to avoid reversals to be picked up based on
1145 pre-payment assignment action id, 'V'. Bug#3498653 */
1146 cursor c_run_master_aa_id(cp_pp_asg_act_id number
1147 ,cp_assignment_id number) is
1148 select paa.assignment_action_id,ppa_run.action_type
1149 from pay_assignment_actions paa
1150 ,pay_action_interlocks pai
1151 ,pay_payroll_actions ppa_run
1152 where pai.locking_action_id = cp_pp_asg_act_id
1153 and paa.assignment_action_id = pai.locked_action_id
1154 and paa.assignment_id = cp_assignment_id
1155 and paa.source_action_id is null
1156 /* Added these two line to avoid reversals 'V' */
1157 and ppa_run.payroll_action_id = paa.payroll_action_id
1158 and ppa_run.action_type <> 'V';
1159
1160 cursor c_all_runs(cp_pp_asg_act_id in number
1161 ,cp_assignment_id in number
1162 ,cp_tax_unit_id in number
1163 ,cp_sepchk_run_type in number) is
1164 select paa.assignment_action_id
1165 from pay_assignment_actions paa,
1166 pay_action_interlocks pai
1167 where pai.locking_action_id = cp_pp_asg_act_id
1168 and paa.assignment_action_id = pai.locked_action_id
1169 and paa.assignment_id = cp_assignment_id
1170 and paa.tax_unit_id = cp_tax_unit_id
1171 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
1172 and not exists ( select 1
1173 from pay_run_types_f prt
1174 where prt.legislation_code = 'CA'
1175 and prt.run_type_id = nvl(paa.run_type_id,0)
1176 and prt.run_method = 'C' );
1177
1178 ln_assignment_id NUMBER;
1179 ln_tax_unit_id NUMBER;
1180 ln_source_action_id NUMBER;
1181
1182 prev_assignment_id NUMBER;
1183 prev_tax_unit_id NUMBER;
1184 prev_source_action_id NUMBER;
1185
1186 ln_prepay_asg_act_id NUMBER;
1187 lv_seperate_check_flag VARCHAR2(1);
1188 ln_child_xfr_action_id NUMBER;
1189 lv_serial_number VARCHAR2(500);
1190 ln_rqp_action_id NUMBER;
1191 ln_ppp_source_action_id NUMBER;
1192
1193 ln_run_aa_id NUMBER;
1194 ln_run_source_aa_id NUMBER;
1195 ln_master_run_aa_id NUMBER;
1196
1197 ln_gross_earn_bal NUMBER;
1198 ln_assignment_action_id NUMBER;
1199
1200 lv_error_message VARCHAR2(500);
1201 lv_procedure_name VARCHAR2(100);
1202 ln_step NUMBER;
1203 /* New variable added for Bal Adj's, bug#3498653 */
1204 lv_run_action_type VARCHAR2(30);
1205
1206
1207 BEGIN
1208
1209 lv_procedure_name := '.create_child_act_for_taxgrp';
1210 hr_utility.set_location('Entering create_child_act_for_taxgrp ', 10 );
1211 hr_utility.trace('GRE p_master_prepay_action_id : ' ||
1212 p_master_prepay_action_id);
1213
1214 /* Initialising local variables to avoid GSCC warnings */
1215 ln_step := 1;
1216 prev_assignment_id := 0;
1217 prev_tax_unit_id := 0;
1218 prev_source_action_id := 0;
1219 ln_prepay_asg_act_id := 0;
1220 lv_seperate_check_flag := 'N';
1221 ln_child_xfr_action_id := 0;
1222 ln_rqp_action_id := 0;
1223 ln_run_aa_id := 0;
1224 ln_run_source_aa_id := 0;
1225 ln_master_run_aa_id := 0;
1226
1227 /*************************************************************
1228 ** The c_payment_info cursor will give the count, how many
1229 ** no. of cheques will be printed.
1230 *************************************************************/
1231
1232 open c_payment_info(p_master_prepay_action_id);
1233 loop
1234
1235 fetch c_payment_info into ln_assignment_id
1236 ,ln_source_action_id;
1237 exit when c_payment_info%notfound;
1238
1239 if ln_source_action_id = -999 then
1240
1241 lv_seperate_check_flag := 'N';
1242 ln_prepay_asg_act_id := p_master_prepay_action_id;
1243
1244 ln_step := 6;
1245
1246 /********************************************************
1247 ** Getting Run Assignment Action Id for normal cheque.
1248 ********************************************************/
1249 open c_run_master_aa_id(ln_prepay_asg_act_id
1250 ,ln_assignment_id);
1251 fetch c_run_master_aa_id into ln_master_run_aa_id,lv_run_action_type;
1252 /* Added this to archive Bal Adj's 'B' current amounts with
1253 pre-payments for Tax Group reporting. Bug#3498653 */
1254
1255 if c_run_master_aa_id%found then
1256 hr_utility.trace('c_run_master_aa_id ran, ln_master_run_aa_id = '||to_char(ln_master_run_aa_id));
1257
1258 if lv_run_action_type = 'B' then
1259 ln_master_run_aa_id := ln_prepay_asg_act_id;
1260 end if;
1261 end if;
1262 /* End of addition bug#3498653 */
1263 close c_run_master_aa_id;
1264
1265 hr_utility.trace('TAXGRP ln_master_run_aa_id = ' ||
1266 ln_master_run_aa_id);
1267
1268 ln_rqp_action_id := ln_prepay_asg_act_id;
1269 ln_ppp_source_action_id := NULL;
1270
1271 else
1272
1273 lv_seperate_check_flag := 'Y';
1274
1275 /*****************************************************
1276 ** To get prepayment assignment action id for Separate
1277 ** Cheque for locking.
1278 ** Single Asg + Single GRE -> Master PP Asg Act ID
1279 ** Single Asg + Multi GRE -> Master or Child PP AAID
1280 ** Multi Asg + Single GRE -> Child PP AAID
1281 ** Following cursor returns corrent PP AAID for any
1282 ** above case. If not found then set master PP AAID
1283 ** which is nothing but zero net pay.
1284 ******************************************************/
1285
1286 open c_get_pp_actid_of_sepchk(ln_source_action_id);
1287 fetch c_get_pp_actid_of_sepchk into ln_prepay_asg_act_id;
1288
1289 if c_get_pp_actid_of_sepchk%notfound then
1290 ln_prepay_asg_act_id := p_master_prepay_action_id;
1291 end if;
1292 close c_get_pp_actid_of_sepchk;
1293
1294 ln_master_run_aa_id := ln_source_action_id; -- Sep Chk
1295 ln_rqp_action_id := ln_source_action_id; -- Sep Chk
1296 ln_ppp_source_action_id := ln_source_action_id; -- Sep Chk
1297
1298 end if;
1299
1300 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1301 hr_utility.trace('TAXGRP ln_prepay_asg_act_id : '||ln_prepay_asg_act_id);
1302 hr_utility.trace('TAXGRP ln_assignment_id : ' || ln_assignment_id);
1303 hr_utility.trace('TAXGRP ln_source_action_id : ' || ln_source_action_id);
1304 hr_utility.trace('TAXGRP lv_seperate_check_flag : ' ||
1305 lv_seperate_check_flag);
1306
1307 ln_step := 2;
1308
1309
1310 /****************************************************************
1311 ** Create Child Assignment Action
1312 ** When Source Action Id is -999 i.e. Normal Cheque
1313 ** When Source Action Id is not -999 i.e. Separate Cheque
1314 ** Below condition will create only one assignment action
1315 ** id when Multi Assignment payment is enabled for diff assignments
1316 *******************************************************************/
1317
1318 if ( ln_source_action_id <> prev_source_action_id ) then
1319
1320 select pay_assignment_actions_s.nextval
1321 into ln_child_xfr_action_id
1322 from dual;
1323
1324 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1325
1326 -- insert into pay_assignment_actions.
1327
1328 ln_step := 3;
1329
1330 hr_nonrun_asact.insact(ln_child_xfr_action_id,
1331 ln_assignment_id,
1332 p_xfr_payroll_action_id,
1333 p_chunk,
1334 p_tax_unit_id,
1335 null,
1336 'C',
1337 p_master_xfr_action_id);
1338
1339 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1340
1341 hr_utility.trace('GRE Locking Action = ' || ln_child_xfr_action_id);
1342 hr_utility.trace('GRE Locked Action = ' || ln_prepay_asg_act_id);
1343
1344 -- insert an interlock to this action
1345
1346 ln_step := 4;
1347
1348 hr_nonrun_asact.insint(ln_child_xfr_action_id,
1349 ln_prepay_asg_act_id);
1350
1351 if ln_source_action_id = -999 then
1352 lv_serial_number := p_master_action_type ||
1353 lv_seperate_check_flag || ln_prepay_asg_act_id;
1354 else
1355 lv_serial_number := p_master_action_type ||
1356 lv_seperate_check_flag || ln_source_action_id;
1357 end if;
1358
1359 ln_step := 5;
1360
1361 update pay_assignment_actions
1362 set serial_number = lv_serial_number
1363 where assignment_action_id = ln_child_xfr_action_id;
1364
1365 hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
1366 p_master_xfr_action_id);
1367
1368
1369 end if;
1370
1371 hr_utility.trace('GRE ln_master_run_aa_id = ' ||
1372 ln_master_run_aa_id);
1373 hr_utility.trace('GRE B4 Calling Process Actions ln_prepay_asg_act_id : '
1374 || ln_prepay_asg_act_id);
1375
1376 /****************************************************************
1377 ** Archive the data for the Child Action
1378 ****************************************************************/
1379
1380 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1381 ln_step := 7;
1382
1383 process_actions(p_xfr_payroll_action_id => p_xfr_payroll_action_id
1384 ,p_xfr_action_id => ln_child_xfr_action_id
1385 ,p_pre_pay_action_id => ln_prepay_asg_act_id
1386 ,p_payment_action_id => ln_master_run_aa_id
1387 ,p_rqp_action_id => ln_rqp_action_id
1388 ,p_seperate_check_flag => lv_seperate_check_flag
1389 ,p_sepcheck_run_type_id => p_sepchk_run_type_id
1390 ,p_action_type => p_master_action_type
1391 ,p_legislation_code => p_legislation_code
1392 ,p_assignment_id => ln_assignment_id
1393 ,p_tax_unit_id => p_tax_unit_id
1394 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1395 ,p_xfr_start_date => p_xfr_start_date
1396 ,p_xfr_end_date => p_xfr_end_date
1397 ,p_ppp_source_action_id => ln_ppp_source_action_id
1398 );
1399
1400 prev_source_action_id := ln_source_action_id;
1401
1402 end loop; -- c_payment_info
1403
1404 close c_payment_info;
1405
1406 hr_utility.set_location('Leaving create_child_act_for_taxgrp ',60 );
1407
1408 EXCEPTION
1409 when others then
1410 hr_utility.set_location(gv_package || lv_procedure_name, 500);
1411 lv_error_message := 'Error at step ' || ln_step ||
1412 ' in ' || gv_package || lv_procedure_name;
1413 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1414
1415 lv_error_message :=
1416 pay_emp_action_arch.set_error_message(lv_error_message);
1417
1418 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1419 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1420 hr_utility.raise_error;
1421
1422
1423 END create_child_act_for_taxgrp;
1424
1425
1426 /************************************************************
1427 Name : create_child_actions_for_gre
1428 Purpose : This function creates child assignment actions.
1429 These action would be created for normal cheques
1430 as well as separate cheques for Single GRE/
1431 Multi Assignment / Multi GRE when Payroll
1432 Aerchiver Level is set to 'GRE' and this
1433 also creates locking records
1434 Arguments :
1435 Notes :
1436 ************************************************************/
1437 PROCEDURE create_child_actions_for_gre(
1438 p_xfr_payroll_action_id in number
1439 ,p_master_xfr_action_id in number
1440 ,p_master_prepay_action_id in number
1441 ,p_master_action_type in varchar2
1442 ,p_sepchk_run_type_id in number
1443 ,p_legislation_code in varchar2
1444 ,p_assignment_id in number
1445 ,p_curr_pymt_eff_date in date
1446 ,p_xfr_start_date in date
1447 ,p_xfr_end_date in date
1448 ,p_chunk in number
1449 )
1450 IS
1451
1452 cursor c_payment_info(cp_prepay_action_id number) is
1453 select assignment_id
1454 ,tax_unit_id
1455 ,nvl(source_action_id,-999)
1456 from pay_payment_information_v
1457 where assignment_action_id = cp_prepay_action_id
1458 order by 3,1,2;
1459
1460 cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1461 ,cp_assignment_id number
1462 ,cp_tax_unit_id number) is
1463 select assignment_action_id
1464 from pay_assignment_actions
1465 where source_action_id = cp_prepay_action_id
1466 and assignment_id = cp_assignment_id
1467 and tax_unit_id = cp_tax_unit_id;
1468
1469 cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1470 select paa.assignment_action_id
1471 from pay_action_interlocks pai
1472 ,pay_assignment_actions paa
1473 ,pay_payroll_actions ppa
1474 where pai.locked_action_id = cp_source_action_id
1475 and paa.assignment_action_id = pai.locking_action_id
1476 and paa.source_action_id is not null
1477 and ppa.payroll_action_id = paa.payroll_action_id
1478 and ppa.action_type in ( 'P', 'U' );
1479
1480 cursor c_run_aa_id(cp_pp_asg_act_id number
1481 ,cp_assignment_id number
1482 ,cp_tax_unit_id number) is
1483 select paa.assignment_action_id, paa.source_action_id
1484 from pay_assignment_actions paa
1485 ,pay_action_interlocks pai
1486 where pai.locking_action_id = cp_pp_asg_act_id
1487 and paa.assignment_action_id = pai.locked_action_id
1488 and paa.assignment_id = cp_assignment_id
1489 and paa.tax_unit_id = cp_tax_unit_id
1490 and paa.source_action_id is not null
1491 order by paa.action_sequence desc;
1492
1493 cursor c_all_runs(cp_pp_asg_act_id in number
1494 ,cp_assignment_id in number
1495 ,cp_tax_unit_id in number
1496 ,cp_sepchk_run_type in number) is
1497 select paa.assignment_action_id
1498 from pay_assignment_actions paa,
1499 pay_action_interlocks pai
1500 where pai.locking_action_id = cp_pp_asg_act_id
1501 and paa.assignment_action_id = pai.locked_action_id
1502 and paa.assignment_id = cp_assignment_id
1503 and paa.tax_unit_id = cp_tax_unit_id
1504 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
1505 and not exists ( select 1
1506 from pay_run_types_f prt
1507 where prt.legislation_code = 'CA'
1508 and prt.run_type_id = nvl(paa.run_type_id,0)
1509 and prt.run_method = 'C' );
1510
1511 ln_assignment_id NUMBER;
1512 ln_tax_unit_id NUMBER;
1513 ln_source_action_id NUMBER;
1514
1515 prev_assignment_id NUMBER;
1516 prev_tax_unit_id NUMBER;
1517 prev_source_action_id NUMBER;
1518
1519 ln_prepay_asg_act_id NUMBER;
1520 lv_seperate_check_flag VARCHAR2(1);
1521 ln_child_xfr_action_id NUMBER;
1522 lv_serial_number VARCHAR2(500);
1523 ln_rqp_action_id NUMBER;
1524 ln_ppp_source_action_id NUMBER;
1525
1526 ln_run_aa_id NUMBER;
1527 ln_run_source_aa_id NUMBER;
1528 ln_master_run_aa_id NUMBER;
1529
1530 ln_gross_earn_bal NUMBER;
1531 ln_assignment_action_id NUMBER;
1532
1533 lv_error_message VARCHAR2(500);
1534 lv_procedure_name VARCHAR2(100);
1535 ln_step NUMBER;
1536 lv_archive_balance_info VARCHAR2(1) := 'Y'; -- Bug 3960157
1537
1538
1539 BEGIN
1540
1541 -- hr_utility.trace_on (null, 'PYARCH');
1542 lv_procedure_name := '.create_child_actions_for_gre';
1543 hr_utility.set_location('Entering create_child_actions_for_gre ', 10 );
1544 hr_utility.trace('GRE p_master_prepay_action_id : ' ||
1545 p_master_prepay_action_id);
1546
1547 ln_step := 1;
1548 /* Initialising local variables to avoid GSCC warnings */
1549 prev_assignment_id := 0;
1550 prev_tax_unit_id := 0;
1551 prev_source_action_id := 0;
1552 ln_prepay_asg_act_id := 0;
1553 lv_seperate_check_flag := 'N';
1554 ln_child_xfr_action_id := 0;
1555 ln_rqp_action_id := 0;
1556 ln_run_aa_id := 0;
1557 ln_run_source_aa_id := 0;
1558 ln_master_run_aa_id := 0;
1559
1560 /*************************************************************
1561 ** The c_payment_info cursor will give the count, how many
1562 ** no. of cheques will be printed.
1563 *************************************************************/
1564
1565 open c_payment_info(p_master_prepay_action_id);
1566 loop
1567
1568 fetch c_payment_info into ln_assignment_id
1569 ,ln_tax_unit_id
1570 ,ln_source_action_id;
1571 exit when c_payment_info%notfound;
1572
1573 ln_gross_earn_bal := 0;
1574
1575 if ln_source_action_id = -999 then
1576
1577 lv_seperate_check_flag := 'N';
1578
1579 /**************************************************
1580 ** gv_multi_gre_payment = 'N' means, Multi GRE is
1581 ** enabled. To get prepayment assignment action id
1582 ** of particular GRE. If no data found then use the
1583 ** master prepayment assignment action id.
1584 *****************************************************/
1585
1586 if pay_ac_action_arch.gv_multi_gre_payment = 'N' then
1587
1588 if (pay_emp_action_arch.gv_multi_payroll_pymt = 'N' or
1589 pay_emp_action_arch.gv_multi_payroll_pymt is null) then
1590
1591 open c_get_pp_actid_of_multigre(p_master_prepay_action_id
1592 ,ln_assignment_id
1593 ,ln_tax_unit_id);
1594
1595 fetch c_get_pp_actid_of_multigre into ln_prepay_asg_act_id;
1596
1597 if c_get_pp_actid_of_multigre%notfound then
1598 ln_prepay_asg_act_id := p_master_prepay_action_id;
1599 end if;
1600
1601 close c_get_pp_actid_of_multigre;
1602
1603 else
1604 /************************************************
1605 ** Multi GRE and Multi Asg of one GRE
1606 *************************************************/
1607 ln_prepay_asg_act_id := p_master_prepay_action_id;
1608
1609 end if;
1610
1611 else
1612
1613 /***************************************************
1614 ** For Multi Assignment or Single Assignment payroll.
1615 *****************************************************/
1616
1617 ln_prepay_asg_act_id := p_master_prepay_action_id;
1618
1619 end if;
1620
1621 ln_step := 6;
1622
1623 /********************************************************
1624 ** Getting Run Assignment Action Id for normal cheque.
1625 ********************************************************/
1626 open c_run_aa_id(ln_prepay_asg_act_id
1627 ,ln_assignment_id
1628 ,ln_tax_unit_id);
1629 fetch c_run_aa_id into ln_run_aa_id
1630 ,ln_run_source_aa_id;
1631 hr_utility.trace('GRE ln_run_aa_id = ' || ln_run_aa_id);
1632 hr_utility.trace('GRE ln_run_source_aa_id = ' || ln_run_source_aa_id); /* Balance Adjustments source_action_id is null, even if we
1633 correct the c_run_aa_id cursor, it will pass the balance
1634 adjustment run asg_act_id for balance calls to archive.
1635 But ASG_PAYMENTS will not return any value with balance
1636 adjustments run asg_act_id, we have to pass the balance
1637 adjustments pre-payment asg_act_id to get the correct
1638 balance values archived using ASG_PAYMENTS dimension.'B'.
1639 so added c_run_aa_id is not found then ln_master_run_aa_id
1640 will be assigned the ln_prepay_asg_act_id that is nothing
1641 but balance adjustment pre-payment asg_act_id. Bug#3498653 */
1642
1643 if c_run_aa_id%NOTFOUND then
1644 hr_utility.trace('Procedure name: '||lv_procedure_name);
1645 hr_utility.trace('c_run_aa_id%NOT FOUND satisfied with action type B');
1646 ln_master_run_aa_id := ln_prepay_asg_act_id;
1647 hr_utility.trace('ln_master_run_aa_id :'||to_char(ln_master_run_aa_id));
1648 else
1649 if pay_ac_action_arch.gv_multi_gre_payment = 'N' then
1650 hr_utility.trace('Procedure name: '||lv_procedure_name);
1651 hr_utility.trace('gv_multi_gre_payment = N satisfied');
1652
1653 ln_master_run_aa_id := ln_run_aa_id; -- Sub Master for Multi GRE
1654 hr_utility.trace('ln_master_run_aa_id :'||to_char(ln_master_run_aa_id));
1655 else
1656 hr_utility.trace('gv_multi_gre_payment = N did not satisfied');
1657 ln_master_run_aa_id := ln_run_source_aa_id; -- Main Master
1658 hr_utility.trace('ln_master_run_aa_id :'||to_char(ln_master_run_aa_id));
1659 end if;
1660 end if; -- c_run_aa_id%NOTFOUND
1661 close c_run_aa_id;
1662
1663 /* Old code before bug#3498653 fix
1664 hr_utility.trace('GRE ln_run_aa_id = ' || ln_run_aa_id);
1665 hr_utility.trace('GRE ln_run_source_aa_id = ' || ln_run_source_aa_id);
1666
1667 if pay_ac_action_arch.gv_multi_gre_payment = 'N' then
1668 ln_master_run_aa_id := ln_run_aa_id; -- Sub Master for Multi GRE
1669 else
1670 ln_master_run_aa_id := ln_run_source_aa_id; -- Main Master
1671 end if;
1672 */
1673
1674 ln_rqp_action_id := ln_prepay_asg_act_id;
1675 ln_ppp_source_action_id := NULL;
1676
1677 /***************************************************************
1678 ** The following process is checked whether any earning
1679 ** element has been processed for normal run (also check net pay
1680 ** zero) if yes then create assignment action and archive
1681 ** otherwise don't create assignment action and don't
1682 ** archive too.
1683 ****************************************************************/
1684
1685 if gn_gross_earn_def_bal_id + gn_payments_def_bal_id <> 0 then
1686 open c_all_runs(p_master_prepay_action_id,
1687 ln_assignment_id,
1688 ln_tax_unit_id,
1689 p_sepchk_run_type_id);
1690 loop
1691 fetch c_all_runs into ln_assignment_action_id;
1692 if c_all_runs%notfound then
1693 exit;
1694 end if;
1695
1696 ln_gross_earn_bal := nvl(pay_balance_pkg.get_value(
1697 gn_gross_earn_def_bal_id,
1698 ln_assignment_action_id),0);
1699
1700 /**************************************************
1701 ** For Non-payroll Payments element is processed
1702 ** alone, the gross earning balance returns zero.
1703 ** In this case check payment.
1704 **************************************************/
1705
1706 if ln_gross_earn_bal = 0 then
1707
1708 ln_gross_earn_bal := nvl(pay_balance_pkg.get_value(
1709 gn_payments_def_bal_id,
1710 ln_assignment_action_id),0);
1711
1712 end if;
1713
1714 if ln_gross_earn_bal <> 0 then
1715 exit;
1716 end if;
1717
1718 end loop;
1719 close c_all_runs;
1720 end if;
1721
1722 else
1723
1724 lv_seperate_check_flag := 'Y';
1725 ln_gross_earn_bal := 1;
1726
1727 /*****************************************************
1728 ** To get prepayment assignment action id for Separate
1729 ** Cheque for locking.
1730 ** Single Asg + Single GRE -> Master PP Asg Act ID
1731 ** Single Asg + Multi GRE -> Master or Child PP AAID
1732 ** Multi Asg + Single GRE -> Child PP AAID
1733 ** Following cursor returns corrent PP AAID for any
1734 ** above case. If not found then set master PP AAID
1735 ** which is nothing but zero net pay.
1736 ******************************************************/
1737
1738 open c_get_pp_actid_of_sepchk(ln_source_action_id);
1739 fetch c_get_pp_actid_of_sepchk into ln_prepay_asg_act_id;
1740
1741 if c_get_pp_actid_of_sepchk%notfound then
1742 ln_prepay_asg_act_id := p_master_prepay_action_id;
1743 end if;
1744 close c_get_pp_actid_of_sepchk;
1745
1746 ln_master_run_aa_id := ln_source_action_id; -- Sep Chk
1747 ln_rqp_action_id := ln_source_action_id; -- Sep Chk
1748 ln_ppp_source_action_id := ln_source_action_id; -- Sep Chk
1749
1750 end if;
1751
1752 /* Bug 3960157 */
1753 if ln_gross_earn_bal = 0 and
1754 p_assignment_id = ln_assignment_id and
1755 pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' then
1756 ln_gross_earn_bal := 1;
1757 lv_archive_balance_info := 'N';
1758 else
1759 lv_archive_balance_info := 'Y';
1760 end if;
1761 /* Bug 3960157 */
1762
1763 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1764 hr_utility.trace('GRE ln_prepay_asg_act_id : ' || ln_prepay_asg_act_id);
1765 hr_utility.trace('GRE ln_tax_unit_id : ' || ln_tax_unit_id);
1766 hr_utility.trace('GRE ln_assignment_id : ' || ln_assignment_id);
1767 hr_utility.trace('GRE ln_source_action_id : ' || ln_source_action_id);
1768 hr_utility.trace('GRE lv_seperate_check_flag : ' ||
1769 lv_seperate_check_flag);
1770
1771 ln_step := 2;
1772
1773 if ln_gross_earn_bal <> 0 then
1774
1775 /****************************************************************
1776 ** Create Child Assignment Action
1777 ** When Source Action Id is -999 i.e. Normal Cheque
1778 ** Multi GRE same assignment but diff tax unit id
1779 ** When Source Action Id is not -999 i.e. Separate Cheque
1780 ** Below condition will create only one assignment action
1781 ** id when Multi Assignment payment is enabled for diff assignments
1782 *******************************************************************/
1783
1784 if ( ( ln_source_action_id <> prev_source_action_id ) or
1785 ( ln_tax_unit_id <> prev_tax_unit_id ) ) then
1786
1787 select pay_assignment_actions_s.nextval
1788 into ln_child_xfr_action_id
1789 from dual;
1790
1791 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1792
1793 -- insert into pay_assignment_actions.
1794
1795 ln_step := 3;
1796
1797 hr_nonrun_asact.insact(ln_child_xfr_action_id,
1798 ln_assignment_id,
1799 p_xfr_payroll_action_id,
1800 p_chunk,
1801 ln_tax_unit_id,
1802 null,
1803 'C',
1804 p_master_xfr_action_id);
1805
1806 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1807
1808 hr_utility.trace('GRE Locking Action = ' || ln_child_xfr_action_id);
1809 hr_utility.trace('GRE Locked Action = ' || ln_prepay_asg_act_id);
1810
1811 -- insert an interlock to this action
1812
1813 ln_step := 4;
1814
1815 hr_nonrun_asact.insint(ln_child_xfr_action_id,
1816 ln_prepay_asg_act_id);
1817
1818 if ln_source_action_id = -999 then
1819 lv_serial_number := p_master_action_type ||
1820 lv_seperate_check_flag || ln_prepay_asg_act_id;
1821 else
1822 lv_serial_number := p_master_action_type ||
1823 lv_seperate_check_flag || ln_source_action_id;
1824 end if;
1825
1826 ln_step := 5;
1827
1828 update pay_assignment_actions
1829 set serial_number = lv_serial_number
1830 where assignment_action_id = ln_child_xfr_action_id;
1831
1832 hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
1833 p_master_xfr_action_id);
1834
1835
1836 end if;
1837
1838 hr_utility.trace('GRE ln_master_run_aa_id = ' ||
1839 ln_master_run_aa_id);
1840 hr_utility.trace('GRE B4 Calling Process Actions ln_prepay_asg_act_id : '
1841 || ln_prepay_asg_act_id);
1842
1843 /****************************************************************
1844 ** Archive the data for the Child Action
1845 ****************************************************************/
1846
1847 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1848 ln_step := 7;
1849 end if;
1850
1851
1852 if ln_gross_earn_bal <> 0 then
1853 if ln_child_xfr_action_id = 0 then
1854 ln_child_xfr_action_id := p_master_xfr_action_id;
1855 end if;
1856 process_actions(p_xfr_payroll_action_id => p_xfr_payroll_action_id
1857 ,p_xfr_action_id => ln_child_xfr_action_id
1858 ,p_pre_pay_action_id => ln_prepay_asg_act_id
1859 ,p_payment_action_id => ln_master_run_aa_id
1860 ,p_rqp_action_id => ln_rqp_action_id
1861 ,p_seperate_check_flag => lv_seperate_check_flag
1862 ,p_sepcheck_run_type_id => p_sepchk_run_type_id
1863 ,p_action_type => p_master_action_type
1864 ,p_legislation_code => p_legislation_code
1865 ,p_assignment_id => ln_assignment_id
1866 ,p_tax_unit_id => ln_tax_unit_id
1867 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
1868 ,p_xfr_start_date => p_xfr_start_date
1869 ,p_xfr_end_date => p_xfr_end_date
1870 ,p_ppp_source_action_id => ln_ppp_source_action_id
1871 ,p_archive_balance_info => lv_archive_balance_info -- Bug 3960157
1872 );
1873
1874 end if;
1875
1876 prev_source_action_id := ln_source_action_id;
1877 prev_tax_unit_id := ln_tax_unit_id;
1878
1879 end loop; -- c_payment_info
1880
1881 close c_payment_info;
1882
1883 hr_utility.set_location('Leaving create_child_actions_for_gre ',60 );
1884
1885 EXCEPTION
1886 when others then
1887 hr_utility.set_location(gv_package || lv_procedure_name, 500);
1888 lv_error_message := 'Error at step ' || ln_step ||
1889 ' in ' || gv_package || lv_procedure_name;
1890 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1891
1892 lv_error_message :=
1893 pay_emp_action_arch.set_error_message(lv_error_message);
1894
1895 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1896 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1897 hr_utility.raise_error;
1898
1899
1900 END create_child_actions_for_gre;
1901
1902 /************************************************************
1903 Name : create_child_actions
1904 Purpose : This function creates child assignment actions.
1905 These action would be created for the seperate check
1906 case(s) only and also create locking records
1907 Arguments :
1908 Notes :
1909 ************************************************************/
1910 PROCEDURE create_chld_act_for_multi_gre(
1911 p_xfr_payroll_action_id in number
1912 ,p_master_xfr_action_id in number
1913 ,p_master_prepay_action_id in number
1914 ,p_master_action_type in varchar2
1915 ,p_sepchk_run_type_id in number
1916 ,p_legislation_code in varchar2
1917 ,p_assignment_id in number
1918 ,p_curr_pymt_eff_date in date
1919 ,p_xfr_start_date in date
1920 ,p_xfr_end_date in date
1921 ,p_chunk in number
1922 )
1923 IS
1924
1925 cursor c_prepay_act(cp_master_prepay_act_id number,
1926 cp_curr_pymt_eff_date date) is
1927 select distinct
1928 paa.assignment_action_id
1929 ,paa.tax_unit_id
1930 from pay_assignment_actions paa
1931 ,pay_pre_payments ppp
1932 ,pay_org_payment_methods popm
1933 where paa.source_action_id = cp_master_prepay_act_id
1934 and ppp.assignment_action_id = paa.assignment_action_id
1935 and ppp.source_action_id is null
1936 and nvl(ppp.value,0) <> 0
1937 and ppp.org_payment_method_id = popm.org_payment_method_id
1938 and popm.defined_balance_id is not null
1939 and cp_curr_pymt_eff_date between popm.effective_start_date
1940 and popm.effective_end_date;
1941
1942
1943 cursor c_mst_prepay_act(cp_master_prepay_act_id number,
1944 cp_curr_pymt_eff_date date) is
1945 select distinct
1946 paa.assignment_action_id
1947 ,paa.tax_unit_id
1948 from pay_assignment_actions paa
1949 ,pay_pre_payments ppp
1950 ,pay_org_payment_methods popm
1951 where paa.assignment_action_id = cp_master_prepay_act_id
1952 and ppp.assignment_action_id = paa.assignment_action_id
1953 and ppp.source_action_id is null
1954 and nvl(ppp.value,0) <> 0
1955 and ppp.org_payment_method_id = popm.org_payment_method_id
1956 and popm.defined_balance_id is not null
1957 and p_curr_pymt_eff_date between popm.effective_start_date
1958 and popm.effective_end_date;
1959
1960 cursor c_tax_unit(cp_pp_asg_act_id number) is
1961 select distinct paa.tax_unit_id
1962 from pay_assignment_actions paa
1963 ,pay_action_interlocks pai
1964 where pai.locking_action_id = cp_pp_asg_act_id
1965 and paa.assignment_action_id = pai.locked_action_id
1966 and paa.tax_unit_id is not null;
1967
1968 cursor c_run_aa_id(cp_pp_asg_act_id number) is
1969 select paa.assignment_action_id, paa.source_action_id
1970 from pay_assignment_actions paa
1971 ,pay_action_interlocks pai
1972 where pai.locking_action_id = cp_pp_asg_act_id
1973 and paa.assignment_action_id = pai.locked_action_id
1974 and paa.source_action_id is not null
1975 order by paa.action_sequence desc;
1976
1977 ln_pp_asg_act_id number;
1978 ln_tax_unit_id number;
1979
1980 ln_child_xfr_action_id number;
1981
1982 ln_run_aa_id number;
1983 ln_source_aa_id number;
1984 ln_master_run_aa_id number;
1985
1986 lv_serial_number VARCHAR2(500);
1987
1988 lv_error_message VARCHAR2(500);
1989 lv_procedure_name VARCHAR2(100);
1990 ln_step NUMBER;
1991
1992 ln_child_aa_count NUMBER;
1993
1994 BEGIN
1995
1996 lv_procedure_name := '.create_chld_act_for_multi_gre';
1997 hr_utility.set_location('Entering create_chld_act_for_multi_gre ', 10 );
1998 hr_utility.trace('MG p_master_prepay_action_id : ' ||
1999 p_master_prepay_action_id);
2000
2001 ln_step := 1;
2002 ln_child_aa_count := 0;
2003 open c_prepay_act(p_master_prepay_action_id
2004 ,p_curr_pymt_eff_date);
2005
2006 loop
2007
2008 fetch c_prepay_act into ln_pp_asg_act_id
2009 ,ln_tax_unit_id;
2010 if c_prepay_act%notfound then
2011 if ln_child_aa_count <> 0 then
2012 exit;
2013 else
2014 open c_mst_prepay_act(p_master_prepay_action_id
2015 ,p_curr_pymt_eff_date);
2016 fetch c_mst_prepay_act into ln_pp_asg_act_id
2017 ,ln_tax_unit_id;
2018 if c_mst_prepay_act%notfound then
2019 close c_mst_prepay_act;
2020 exit;
2021 end if;
2022
2023 close c_mst_prepay_act;
2024 end if;
2025 end if;
2026
2027 ln_child_aa_count := ln_child_aa_count + 1;
2028
2029 ln_step := 2;
2030 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2031
2032 if ln_tax_unit_id is null then
2033 open c_tax_unit(ln_pp_asg_act_id);
2034 fetch c_tax_unit into ln_tax_unit_id;
2035 close c_tax_unit;
2036 end if;
2037
2038 hr_utility.trace('MG ln_pp_asg_act_id : ' || ln_pp_asg_act_id);
2039 hr_utility.trace('MG ln_tax_unit_id : ' || ln_tax_unit_id);
2040
2041 select pay_assignment_actions_s.nextval
2042 into ln_child_xfr_action_id
2043 from dual;
2044
2045 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2046
2047 -- insert into pay_assignment_actions.
2048
2049 ln_step := 3;
2050
2051 hr_nonrun_asact.insact(ln_child_xfr_action_id,
2052 p_assignment_id,
2053 p_xfr_payroll_action_id,
2054 p_chunk,
2055 ln_tax_unit_id,
2056 null,
2057 'C',
2058 p_master_xfr_action_id);
2059
2060 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2061
2062 hr_utility.trace('MG Locking Action = ' || ln_child_xfr_action_id);
2063 hr_utility.trace('MG Locked Action = ' || ln_pp_asg_act_id);
2064
2065 -- insert an interlock to this action
2066
2067 ln_step := 4;
2068
2069 hr_nonrun_asact.insint(ln_child_xfr_action_id,
2070 ln_pp_asg_act_id);
2071
2072 lv_serial_number := p_master_action_type || 'N' || ln_pp_asg_act_id;
2073
2074 ln_step := 5;
2075
2076 update pay_assignment_actions
2077 set serial_number = lv_serial_number
2078 where assignment_action_id = ln_child_xfr_action_id;
2079
2080 hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
2081 p_master_xfr_action_id);
2082
2083 ln_step := 6;
2084
2085 open c_run_aa_id(ln_pp_asg_act_id);
2086 fetch c_run_aa_id into ln_run_aa_id
2087 ,ln_source_aa_id;
2088 close c_run_aa_id;
2089
2090 hr_utility.trace('MG ln_run_aa_id = ' || ln_run_aa_id);
2091 hr_utility.trace('MG ln_source_aa_id = ' || ln_source_aa_id);
2092
2093 if pay_ac_action_arch.gv_multi_gre_payment = 'N' then
2094 ln_master_run_aa_id := ln_run_aa_id;
2095 else
2096 ln_master_run_aa_id := ln_source_aa_id;
2097 end if;
2098
2099 --ln_sub_master_run_aa_id := ln_run_aa_id;
2100
2101 --ln_master_run_aa_id := ln_pp_asg_act_id;
2102
2103 hr_utility.trace('MG ln_master_run_aa_id = ' ||
2104 ln_master_run_aa_id);
2105
2106 /****************************************************************
2107 ** Archive the data for the Child Action
2108 ****************************************************************/
2109
2110 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2111 ln_step := 7;
2112
2113 process_actions(p_xfr_payroll_action_id => p_xfr_payroll_action_id
2114 ,p_xfr_action_id => ln_child_xfr_action_id
2115 ,p_pre_pay_action_id => ln_pp_asg_act_id
2116 ,p_payment_action_id => ln_master_run_aa_id
2117 ,p_rqp_action_id => ln_pp_asg_act_id
2118 ,p_seperate_check_flag => 'N'
2119 ,p_sepcheck_run_type_id => p_sepchk_run_type_id
2120 ,p_action_type => p_master_action_type
2121 ,p_legislation_code => p_legislation_code
2122 ,p_assignment_id => p_assignment_id
2123 ,p_tax_unit_id => ln_tax_unit_id
2124 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
2125 ,p_xfr_start_date => p_xfr_start_date
2126 ,p_xfr_end_date => p_xfr_end_date
2127 );
2128
2129 end loop;
2130
2131 close c_prepay_act;
2132
2133 hr_utility.set_location('Leaving create_chld_act_for_multi_gre ',60 );
2134
2135 EXCEPTION
2136 when others then
2137 hr_utility.set_location(gv_package || lv_procedure_name, 500);
2138 lv_error_message := 'Error at step ' || ln_step ||
2139 ' in ' || gv_package || lv_procedure_name;
2140 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2141
2142 lv_error_message :=
2143 pay_emp_action_arch.set_error_message(lv_error_message);
2144
2145 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2146 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2147 hr_utility.raise_error;
2148
2149 END create_chld_act_for_multi_gre;
2150
2151 /************************************************************
2152 Name : create_child_actions
2153 Purpose : This function creates child assignment actions.
2154 These action would be created for the seperate check
2155 case(s) only and also create locking records
2156 Arguments :
2157 Notes :
2158 ************************************************************/
2159 PROCEDURE create_child_actions (
2160 p_xfr_payroll_action_id in number
2161 ,p_master_xfr_action_id in number
2162 ,p_master_prepay_action_id in number
2163 ,p_master_action_type in varchar2
2164 ,p_curr_pymt_eff_date in date
2165 ,p_sepchk_run_type_id in number
2166 ,p_legislation_code in varchar2
2167 ,p_chunk in number)
2168 IS
2169
2170 cursor c_multi_asg_child_action(
2171 cp_master_prepay_action_id in number
2172 ,cp_cons_set_id in number
2173 ,cp_payroll_id in number
2174 ,cp_business_group_id in number
2175 ,cp_start_date in date
2176 ,cp_end_date in date
2177 ,cp_curr_pymt_eff_date in date
2178 ) is
2179 select distinct
2180 paa.assignment_id,
2181 paa.tax_unit_id,
2182 paa.assignment_action_id,
2183 ppp.source_action_id
2184 from pay_payroll_actions ppa
2185 ,pay_assignment_actions paa
2186 ,pay_pre_payments ppp
2187 ,pay_org_payment_methods popm
2188 where ppa.consolidation_set_id
2189 = nvl(cp_cons_set_id,ppa.consolidation_set_id)
2190 and paa.action_status = 'C'
2191 and ppa.payroll_id = cp_payroll_id
2192 and ppa.payroll_action_id = paa.payroll_action_id
2193 and ppa.business_group_id = cp_business_group_id
2194 and ppa.action_status = 'C'
2195 and ppa.effective_date between cp_start_date
2196 and cp_end_date
2197 and ppa.action_type in ('U','P')
2198 and nvl(paa.source_action_id,paa.assignment_action_id)
2199 = cp_master_prepay_action_id
2200 and ppp.assignment_action_id = paa.assignment_action_id
2201 and ppp.source_action_id is not null
2202 and nvl(ppp.value,0) <> 0
2203 and ppp.org_payment_method_id = popm.org_payment_method_id
2204 and popm.defined_balance_id is not null
2205 and cp_curr_pymt_eff_date between popm.effective_start_date
2206 and popm.effective_end_date
2207 order by 1,2,3,4;
2208
2209 cursor c_asg_child_action (cp_prepayment_action_id number
2210 ,cp_curr_pymt_eff_date date) is
2211 select distinct
2212 paa.assignment_id,
2213 paa.tax_unit_id,
2214 paa.assignment_action_id,
2215 ppp.source_action_id
2216 from pay_pre_payments ppp
2217 ,pay_assignment_actions paa
2218 ,pay_org_payment_methods popm
2219 where paa.assignment_action_id = cp_prepayment_action_id
2220 and ppp.assignment_action_id = paa.assignment_action_id
2221 and nvl(ppp.value,0) <> 0
2222 and ppp.source_action_id is not null
2223 and ppp.org_payment_method_id = popm.org_payment_method_id
2224 and popm.defined_balance_id is not null
2225 and cp_curr_pymt_eff_date between popm.effective_start_date
2226 and popm.effective_end_date
2227 order by ppp.source_action_id;
2228
2229 cursor c_pre_pay_run_action
2230 (cp_master_prepay_action_id in number
2231 ,cp_sepchk_run_type_id in number) is
2232 select pai.locked_action_id
2233 from pay_action_interlocks pai,
2234 pay_assignment_actions paa
2235 where pai.locking_action_id = cp_master_prepay_action_id
2236 and paa.assignment_action_id = pai.locked_action_id
2237 and paa.source_action_id is not null
2238 and paa.run_type_id = cp_sepchk_run_type_id;
2239
2240 cursor c_tax_unit(cp_source_action_id number) is
2241 select paa.tax_unit_id
2242 from pay_assignment_actions paa
2243 where paa.assignment_action_id = cp_source_action_id;
2244
2245 ln_assignment_id NUMBER;
2246 ln_tax_unit_id NUMBER;
2247 ln_asg_action_id NUMBER;
2248
2249 ld_end_date DATE;
2250 ld_start_date DATE;
2251 ln_business_group_id NUMBER;
2252 ln_cons_set_id NUMBER;
2253 ln_payroll_id NUMBER;
2254
2255 ln_child_xfr_action_id NUMBER;
2256 ln_run_action_id NUMBER(10);
2257
2258 lv_serial_number VARCHAR2(20);
2259
2260 lv_error_message VARCHAR2(500);
2261 lv_procedure_name VARCHAR2(100);
2262 ln_step NUMBER;
2263
2264 BEGIN
2265 lv_procedure_name := '.create_child_actions';
2266 hr_utility.set_location('Entering create_child_actions ', 10 );
2267
2268 ln_step := 1;
2269 -- Initialising local variables to avoid GSCC warnings
2270 ln_assignment_id := 0;
2271 ln_tax_unit_id := 0;
2272 ln_asg_action_id := 0;
2273
2274 get_payroll_action_info(p_payroll_action_id => p_xfr_payroll_action_id
2275 ,p_start_date => ld_start_date
2276 ,p_end_date => ld_end_date
2277 ,p_business_group_id => ln_business_group_id
2278 ,p_cons_set_id => ln_cons_set_id
2279 ,p_payroll_id => ln_payroll_id);
2280 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2281
2282 ln_step := 2;
2283 pay_emp_action_arch.gv_multi_payroll_pymt
2284 := pay_emp_action_arch.get_multi_assignment_flag(
2285 p_payroll_id => ln_payroll_id
2286 ,p_effective_date => ld_end_date);
2287
2288 hr_utility.trace('pay_emp_action_arch.gv_multi_payroll_pymt = ' ||
2289 pay_emp_action_arch.gv_multi_payroll_pymt);
2290 ln_step := 3;
2291 if ((pay_emp_action_arch.gv_multi_payroll_pymt = 'N' or
2292 pay_emp_action_arch.gv_multi_payroll_pymt is null) and
2293 pay_ac_action_arch.gv_multi_gre_payment = 'Y' ) then
2294
2295 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2296 hr_utility.trace('ln_master_prepay_action_id ' ||
2297 p_master_prepay_action_id);
2298 ln_step := 4;
2299 open c_asg_child_action(p_master_prepay_action_id
2300 ,p_curr_pymt_eff_date);
2301 else
2302 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2303 ln_step := 5;
2304 open c_multi_asg_child_action(
2305 p_master_prepay_action_id
2306 ,ln_cons_set_id
2307 ,ln_payroll_id
2308 ,ln_business_group_id
2309 ,ld_start_date
2310 ,ld_end_date
2311 ,p_curr_pymt_eff_date);
2312 end if;
2313 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2314
2315 ln_step := 6;
2316 loop
2317 if ((pay_emp_action_arch.gv_multi_payroll_pymt = 'N' or
2318 pay_emp_action_arch.gv_multi_payroll_pymt is null) and
2319 pay_ac_action_arch.gv_multi_gre_payment = 'Y' ) then
2320
2321 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2322 ln_step := 7;
2323 fetch c_asg_child_action into ln_assignment_id,
2324 ln_tax_unit_id,
2325 ln_asg_action_id,
2326 ln_run_action_id;
2327 exit when c_asg_child_action%notfound;
2328 else
2329 ln_step := 8;
2330 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2331 fetch c_multi_asg_child_action into ln_assignment_id,
2332 ln_tax_unit_id,
2333 ln_asg_action_id,
2334 ln_run_action_id;
2335 exit when c_multi_asg_child_action%notfound;
2336 end if;
2337 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2338
2339 if ln_tax_unit_id is null then
2340 ln_step := 81;
2341 open c_tax_unit(ln_run_action_id);
2342 fetch c_tax_unit into ln_tax_unit_id;
2343 close c_tax_unit;
2344 end if;
2345
2346 -- create child assignment action
2347 ln_step := 9;
2348 select pay_assignment_actions_s.nextval
2349 into ln_child_xfr_action_id
2350 from dual;
2351
2352 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2353 -- insert into pay_assignment_actions.
2354 ln_step := 10;
2355 hr_nonrun_asact.insact(ln_child_xfr_action_id,
2356 ln_assignment_id,
2357 p_xfr_payroll_action_id,
2358 p_chunk,
2359 ln_tax_unit_id,
2360 null,
2361 'C',
2362 p_master_xfr_action_id);
2363 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2364 hr_utility.trace('Locking Action = ' || ln_child_xfr_action_id);
2365 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
2366 -- insert an interlock to this action
2367 ln_step := 11;
2368 hr_nonrun_asact.insint(ln_child_xfr_action_id,
2369 ln_asg_action_id);
2370
2371 hr_utility.set_location(gv_package || lv_procedure_name, 110);
2372 -- if pay_ac_action_arch.gv_multi_asg_enabled = 'Y' then
2373 -- open c_pre_pay_run_action (ln_asg_action_id, p_sepchk_run_type_id);
2374 -- fetch c_pre_pay_run_action into ln_run_action_id;
2375 -- close c_pre_pay_run_action;
2376 -- end if;
2377
2378 lv_serial_number := p_master_action_type || 'Y' || ln_run_action_id;
2379
2380 ln_step := 12;
2381 update pay_assignment_actions
2382 set serial_number = lv_serial_number
2383 where assignment_action_id = ln_child_xfr_action_id;
2384
2385 hr_utility.trace('Processing Child action for Master ' ||
2386 p_master_xfr_action_id);
2387
2388 /****************************************************************
2389 ** Archive the data for the Child Action
2390 ****************************************************************/
2391 ln_step := 13;
2392 process_actions(p_xfr_payroll_action_id => p_xfr_payroll_action_id
2393 ,p_xfr_action_id => ln_child_xfr_action_id
2394 ,p_pre_pay_action_id => ln_asg_action_id
2395 ,p_payment_action_id => ln_run_action_id
2396 ,p_rqp_action_id => ln_run_action_id
2397 ,p_seperate_check_flag => 'Y'
2398 ,p_sepcheck_run_type_id => p_sepchk_run_type_id
2399 ,p_action_type => p_master_action_type
2400 ,p_legislation_code => p_legislation_code
2401 ,p_assignment_id => ln_assignment_id
2402 ,p_tax_unit_id => ln_tax_unit_id
2403 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
2404 ,p_xfr_start_date => ld_start_date
2405 ,p_xfr_end_date => ld_end_date
2406 ,p_ppp_source_action_id => ln_run_action_id
2407 );
2408
2409 end loop;
2410 hr_utility.set_location(gv_package || lv_procedure_name, 120);
2411
2412 ln_step := 14;
2413 if ((pay_emp_action_arch.gv_multi_payroll_pymt = 'N' or
2414 pay_emp_action_arch.gv_multi_payroll_pymt is null) and
2415 pay_ac_action_arch.gv_multi_gre_payment = 'Y' ) then
2416 close c_asg_child_action;
2417 else
2418 close c_multi_asg_child_action;
2419 end if;
2420 hr_utility.set_location('Leaving create_child_actions ',130 );
2421
2422 EXCEPTION
2423 when others then
2424 hr_utility.set_location(gv_package || lv_procedure_name, 500);
2425 lv_error_message := 'Error at step ' || ln_step ||
2426 ' in ' || gv_package || lv_procedure_name;
2427 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2428
2429 lv_error_message :=
2430 pay_emp_action_arch.set_error_message(lv_error_message);
2431
2432 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2433 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2434 hr_utility.raise_error;
2435
2436 END create_child_actions;
2437
2438 /************************************************************
2439 Name : py_achive_data
2440 Purpose : This performs the CA specific employee context
2441 setting for the Tax Remittance Archiver and
2442 for the payslip,check writer and
2443 Deposit Advice modules.
2444 Arguments :
2445 Notes :
2446 ************************************************************/
2447 PROCEDURE py_archive_data( p_xfr_action_id in number
2448 ,p_effective_date in date)
2449 IS
2450
2451 cursor c_xfr_info (cp_assignment_action in number) is
2452 select paa.payroll_action_id,
2453 paa.assignment_action_id,
2454 paa.assignment_id,
2455 paa.tax_unit_id,
2456 paa.serial_number,
2457 paa.chunk_number
2458 from pay_assignment_actions paa
2459 where paa.assignment_action_id = cp_assignment_action;
2460
2461 cursor c_legislation (cp_business_group in number) is
2462 select org_information9
2463 from hr_organization_information
2464 where org_information_context = 'Business Group Information'
2465 and organization_id = cp_business_group;
2466
2467 cursor c_sepchk_run_type is
2468 select prt.run_type_id
2469 from pay_run_types_f prt
2470 where prt.shortname = 'SEP_PAY'
2471 and prt.legislation_code = 'CA';
2472
2473 cursor c_assignment_run (cp_prepayment_action_id in number) is
2474 select distinct paa.assignment_id
2475 from pay_action_interlocks pai,
2476 pay_assignment_actions paa,
2477 pay_payroll_actions ppa
2478 where pai.locking_action_id = cp_prepayment_action_id
2479 and paa.assignment_action_id = pai.locked_action_id
2480 and ppa.payroll_action_id = paa.payroll_action_id
2481 and ppa.action_type in ('R', 'Q', 'B')
2482 and ((ppa.run_type_id is null and
2483 paa.source_action_id is null) or
2484 (ppa.run_type_id is not null and
2485 paa.source_action_id is not null))
2486 and ppa.action_status = 'C';
2487
2488 cursor c_master_run_action(
2489 cp_prepayment_action_id in number,
2490 cp_assignment_id in number) is
2491 select paa.assignment_action_id, paa.payroll_action_id,
2492 ppa.action_type
2493 from pay_payroll_actions ppa,
2494 pay_assignment_actions paa,
2495 pay_action_interlocks pai
2496 where pai.locking_action_Id = cp_prepayment_action_id
2497 and pai.locked_action_id = paa.assignment_action_id
2498 and paa.assignment_id = cp_assignment_id
2499 and paa.source_action_id is null
2500 and ppa.payroll_action_id = paa.payroll_action_id
2501 order by paa.assignment_action_id desc;
2502
2503 cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
2504 select effective_date
2505 from pay_payroll_actions ppa,
2506 pay_assignment_actions paa
2507 where ppa.payroll_action_id = paa.payroll_action_id
2508 and paa.assignment_action_id = cp_prepayment_action_id;
2509
2510 cursor c_check_pay_action( cp_payroll_action_id in number) is
2511 select count(*)
2512 from pay_action_information
2513 where action_context_id = cp_payroll_action_id
2514 and action_context_type = 'PA';
2515
2516 /* Added new cursor to archive multiple balance adjustments done
2517 with same effective date, 'B'. Bug#3498653 */
2518
2519 cursor c_get_emp_adjbal(cp_xfr_action_id number) IS
2520 select locked_action_id
2521 from pay_action_interlocks
2522 where locking_action_id = cp_xfr_action_id;
2523
2524 /* Added this cursor to get the run action type, so that
2525 if it is reversals 'V' then we will not call the
2526 create_child_action procedures, to avoid creating
2527 unnecessary child actions. Bug#3498653 */
2528
2529 cursor c_master_run_action_type(
2530 cp_prepayment_action_id in number,
2531 cp_assignment_id in number) is
2532 select distinct ppa.action_type
2533 from pay_payroll_actions ppa,
2534 pay_assignment_actions paa,
2535 pay_action_interlocks pai
2536 where pai.locking_action_Id = cp_prepayment_action_id
2537 and pai.locked_action_id = paa.assignment_action_id
2538 and paa.assignment_id = cp_assignment_id
2539 and paa.source_action_id is null
2540 and ppa.payroll_action_id = paa.payroll_action_id
2541 and ppa.action_type <> 'V';
2542
2543 ld_curr_pymt_eff_date DATE;
2544 ln_sepchk_run_type_id NUMBER;
2545 lv_legislation_code VARCHAR2(2);
2546
2547 ln_xfr_master_action_id NUMBER;
2548
2549 ln_tax_unit_id NUMBER;
2550 ln_xfr_payroll_action_id NUMBER; /* of current xfr */
2551 ln_assignment_id NUMBER;
2552 ln_chunk_number NUMBER;
2553
2554 lv_xfr_master_serial_number VARCHAR2(30);
2555
2556 lv_master_action_type VARCHAR2(1);
2557 lv_master_sepcheck_flag VARCHAR2(1);
2558 ln_asg_action_id NUMBER;
2559
2560 ln_master_run_action_id NUMBER;
2561 ln_master_run_pact_id NUMBER;
2562 lv_master_run_action_type VARCHAR2(1);
2563
2564 ln_pymt_balcall_aaid NUMBER;
2565 ln_pay_action_count NUMBER;
2566
2567 ld_start_date DATE;
2568 ld_end_date DATE; /* End date of current xfr from ppa */
2569 ln_business_group_id NUMBER;
2570 ln_cons_set_id NUMBER;
2571 ln_payroll_id NUMBER;
2572
2573 lv_error_message VARCHAR2(500);
2574 lv_procedure_name VARCHAR2(100);
2575 ln_step NUMBER;
2576 ln_tax_group_id NUMBER;
2577 ln_tax_unit_id_context NUMBER;
2578 lv_run_action_type VARCHAR2(10);
2579
2580 BEGIN
2581
2582 lv_procedure_name := '.action_archive_data';
2583 pay_emp_action_arch.gv_error_message := NULL;
2584 hr_utility.trace('Entered py_archive_data');
2585 hr_utility.trace('p_xfr_action_id '||to_char(p_xfr_action_id));
2586 hr_utility.trace('Cursor c_xfr_info');
2587
2588 ln_step := 1;
2589 open c_xfr_info (p_xfr_action_id);
2590 fetch c_xfr_info into ln_xfr_payroll_action_id,
2591 ln_xfr_master_action_id,
2592 ln_assignment_id,
2593 ln_tax_unit_id,
2594 lv_xfr_master_serial_number,
2595 ln_chunk_number;
2596 close c_xfr_info;
2597
2598 ln_step := 2;
2599 get_payroll_action_info(p_payroll_action_id => ln_xfr_payroll_action_id
2600 ,p_start_date => ld_start_date
2601 ,p_end_date => ld_end_date
2602 ,p_business_group_id => ln_business_group_id
2603 ,p_cons_set_id => ln_cons_set_id
2604 ,p_payroll_id => ln_payroll_id);
2605
2606 ln_step := 200;
2607 pay_emp_action_arch.gv_multi_payroll_pymt
2608 := pay_emp_action_arch.get_multi_assignment_flag(
2609 p_payroll_id => ln_payroll_id
2610 ,p_effective_date => ld_end_date);
2611
2612 hr_utility.trace('pay_emp_action_arch.gv_multi_payroll_pymt = ' ||
2613 pay_emp_action_arch.gv_multi_payroll_pymt);
2614
2615 ln_step := 3;
2616 open c_legislation (ln_business_group_id);
2617 fetch c_legislation into lv_legislation_code ;
2618 if c_legislation%notfound then
2619 hr_utility.trace('Business Group for Interface Process Not Found');
2620 hr_utility.raise_error;
2621 end if;
2622 close c_legislation;
2623 hr_utility.trace('lv_legislation_code '||lv_legislation_code);
2624
2625 ln_step := 4;
2626 open c_sepchk_run_type;
2627 fetch c_sepchk_run_type into ln_sepchk_run_type_id;
2628 if c_sepchk_run_type%notfound then
2629 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2630 hr_utility.raise_error;
2631 end if;
2632 close c_sepchk_run_type;
2633
2634 ln_step := 5;
2635 -- process the master_action
2636 lv_master_action_type := substr(lv_xfr_master_serial_number,1,1);
2637 -- Always N for Master Assignment Action
2638 lv_master_sepcheck_flag := substr(lv_xfr_master_serial_number,2,1);
2639 -- Assignment Action of Quick Pay Pre Payment, Pre Payment, Reversal
2640 ln_asg_action_id := substr(lv_xfr_master_serial_number,3);
2641
2642 ln_step := 6;
2643 open c_pymt_eff_date(ln_asg_action_id);
2644 fetch c_pymt_eff_date into ld_curr_pymt_eff_date;
2645 if c_pymt_eff_date%notfound then
2646 hr_utility.trace('PayrollAction for InterfaceProcess NotFound');
2647 hr_utility.raise_error;
2648 end if;
2649 close c_pymt_eff_date;
2650
2651 ln_step := 7;
2652 hr_utility.trace('End Date=' || to_char(ld_end_date, 'dd-mon-yyyy'));
2653 hr_utility.trace('Start Date='||to_char(ld_start_date, 'dd-mon-yyyy'));
2654 hr_utility.trace('Business Group Id='||to_char(ln_business_group_id));
2655 hr_utility.trace('Serial Number='||lv_xfr_master_serial_number);
2656 hr_utility.trace('ln_xfr_payroll_action_id ='||
2657 to_char(ln_xfr_payroll_action_id));
2658
2659 if lv_master_action_type in ( 'P','U') then
2660 /************************************************************
2661 ** For Master Pre Payment Action of Multi GRE
2662 ** Archive the data seperately for all different GREs.
2663 *************************************************************/
2664 lv_run_action_type := Null;
2665 open c_master_run_action_type(ln_asg_action_id,ln_assignment_id);
2666 fetch c_master_run_action_type into lv_run_action_type;
2667 hr_utility.trace('lv_run_action_type ='||lv_run_action_type);
2668
2669 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
2670 /* Added this validation to avoid creating child assignment actions,
2671 when reversals to be picked up based on Pre-payments. Reversals
2672 are archived directly based on run actions. Bug#3498653 */
2673
2674 if c_master_run_action_type%found then
2675
2676 create_child_act_for_taxgrp(
2677 p_xfr_payroll_action_id => ln_xfr_payroll_action_id
2678 ,p_master_xfr_action_id => p_xfr_action_id
2679 ,p_master_prepay_action_id => ln_asg_action_id
2680 ,p_master_action_type => lv_master_action_type
2681 ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2682 ,p_legislation_code => lv_legislation_code
2683 ,p_assignment_id => ln_assignment_id
2684 ,p_tax_unit_id => ln_tax_unit_id -- TXUNTID
2685 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2686 ,p_xfr_start_date => ld_start_date
2687 ,p_xfr_end_date => ld_end_date
2688 ,p_chunk => ln_chunk_number
2689 );
2690 else
2691 hr_utility.trace('Dont create child actions for Reversals: '||
2692 'py_archive_date Tax Gruop level');
2693 null;
2694
2695 end if; -- c_master_run_action_type%found
2696 close c_master_run_action_type;
2697
2698 else
2699 /* Added this validation to avoid creating child assignment actions
2700 when reversals to be picked up based on Pre-payments. Reversals
2701 are archived directly based on run actions. Bug#3498653 */
2702 if c_master_run_action_type%found then
2703
2704 create_child_actions_for_gre(
2705 p_xfr_payroll_action_id => ln_xfr_payroll_action_id
2706 ,p_master_xfr_action_id => p_xfr_action_id
2707 ,p_master_prepay_action_id => ln_asg_action_id
2708 ,p_master_action_type => lv_master_action_type
2709 ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2710 ,p_legislation_code => lv_legislation_code
2711 ,p_assignment_id => ln_assignment_id
2712 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2713 ,p_xfr_start_date => ld_start_date
2714 ,p_xfr_end_date => ld_end_date
2715 ,p_chunk => ln_chunk_number
2716 );
2717 else
2718 hr_utility.trace('Dont create child actions for Reversals: '||
2719 'py_archive_date GRE level');
2720 null;
2721 end if; -- c_master_run_action_type%found
2722 close c_master_run_action_type;
2723
2724 end if; -- gv_reporting_level = 'TAXGRP'
2725
2726 -- if ( ( pay_ac_action_arch.gv_multi_gre_payment = 'N' ) and
2727 -- ( pay_emp_action_arch.gv_multi_payroll_pymt = 'N' or
2728 -- pay_emp_action_arch.gv_multi_payroll_pymt is null ) ) then
2729
2730 -- create_chld_act_for_multi_gre(
2731 -- p_xfr_payroll_action_id => ln_xfr_payroll_action_id
2732 -- ,p_master_xfr_action_id => p_xfr_action_id
2733 -- ,p_master_prepay_action_id => ln_asg_action_id
2734 -- ,p_master_action_type => lv_master_action_type
2735 -- ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2736 -- ,p_legislation_code => lv_legislation_code
2737 -- ,p_assignment_id => ln_assignment_id
2738 -- ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2739 -- ,p_xfr_start_date => ld_start_date
2740 -- ,p_xfr_end_date => ld_end_date
2741 -- ,p_chunk => ln_chunk_number
2742 -- );
2743
2744
2745 -- else
2746 -- /************************************************************
2747 -- ** For Master Pre Payment Action get the distinct
2748 -- ** Assignment_ID's and archive the data seperately for
2749 -- ** all the assigments.
2750 -- *************************************************************/
2751 -- ln_step := 8;
2752 -- open c_assignment_run(ln_asg_action_id);
2753 -- loop
2754 -- fetch c_assignment_run into ln_assignment_id;
2755 -- if c_assignment_run%notfound then
2756 -- exit;
2757 -- end if;
2758 --
2759 -- ln_step := 9;
2760 -- open c_master_run_action(ln_asg_action_id,
2761 -- ln_assignment_id);
2762 -- fetch c_master_run_action into ln_master_run_action_id,
2763 -- ln_master_run_pact_id,
2764 -- lv_master_run_action_type;
2765 -- if c_master_run_action%notfound then
2766 -- hr_utility.raise_error;
2767 -- end if;
2768 -- close c_master_run_action;
2769 --
2770 -- ln_step := 10;
2771 -- if lv_master_run_action_type in ('R', 'Q') then
2772 -- ln_pymt_balcall_aaid := ln_master_run_action_id;
2773 -- else
2774 -- ln_pymt_balcall_aaid := ln_asg_action_id;
2775 -- end if;
2776
2777 -- call fuction to process the actions
2778 -- ln_step := 11;
2779 -- process_actions(
2780 -- p_xfr_payroll_action_id => ln_xfr_payroll_action_id
2781 -- ,p_xfr_action_id => p_xfr_action_id
2782 -- ,p_pre_pay_action_id => ln_asg_action_id
2783 -- ,p_payment_action_id => ln_pymt_balcall_aaid
2784 -- ,p_rqp_action_id => ln_asg_action_id
2785 -- ,p_seperate_check_flag => lv_master_sepcheck_flag
2786 -- ,p_sepcheck_run_type_id => ln_sepchk_run_type_id
2787 -- ,p_action_type => lv_master_action_type
2788 -- ,p_legislation_code => lv_legislation_code
2789 -- ,p_assignment_id => ln_assignment_id
2790 -- ,p_tax_unit_id => ln_tax_unit_id
2791 -- ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2792 -- ,p_xfr_start_date => ld_start_date
2793 -- ,p_xfr_end_date => ld_end_date
2794 -- );
2795 -- end loop;
2796 -- close c_assignment_run;
2797
2798 -- end if;
2799
2800 -- /************************************************************
2801 -- ** If Action is Pre Payment, then create child records for
2802 -- ** Seperate Check Runs.
2803 -- *************************************************************/
2804 -- ln_step := 12;
2805 -- create_child_actions(
2806 -- p_xfr_payroll_action_id => ln_xfr_payroll_action_id
2807 -- ,p_master_xfr_action_id => ln_xfr_master_action_id
2808 -- ,p_master_prepay_action_id => ln_asg_action_id
2809 -- ,p_master_action_type => lv_master_action_type
2810 -- ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2811 -- ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2812 -- ,p_legislation_code => lv_legislation_code
2813 -- ,p_chunk => ln_chunk_number);
2814
2815 end if;
2816
2817 ln_step := 13;
2818 if lv_master_action_type = 'V' then
2819 /* ln_asg_action_id is nothing but reversal run action id */
2820 ln_pymt_balcall_aaid := ln_asg_action_id ;
2821 hr_utility.trace('Reversal ln_pymt_balcall_aaid'
2822 ||to_char(ln_pymt_balcall_aaid));
2823 /* Added this code to archive the tax balances and other elements
2824 for reversals in Canada. Bug#3498653 */
2825 ln_step := 14;
2826 pay_ac_action_arch.initialization_process;
2827
2828 hr_utility.trace('Populating Tax Balances for Reversals');
2829 hr_utility.trace('ln_tax_unit_id : '||to_char(ln_tax_unit_id));
2830 hr_utility.trace('ln_pymt_balcall_aaid :'||to_char(ln_pymt_balcall_aaid));
2831 hr_utility.trace('ld_curr_pymt_eff_date :'||to_char(ld_curr_pymt_eff_date,'DD-MON-YYYY'));
2832 hr_utility.trace('ln_assignment_id :'||to_char(ln_assignment_id));
2833
2834 /* Added this to support tax group level reporting for
2835 Reversals. Need to set both contexts for
2836 Tax Group reporting because for current amounts we
2837 use GRE context and for YTD amounts we use Tax_Group
2838 context */
2839 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
2840 ln_tax_group_id := get_taxgroup_val(
2841 p_tax_unit_id => ln_tax_unit_id,
2842 p_assignment_id => ln_assignment_id,
2843 p_asg_act_id =>ln_pymt_balcall_aaid);
2844 pay_balance_pkg.set_context('TAX_GROUP', ln_tax_group_id);
2845 gn_taxgrp_gre_id := ln_tax_group_id;
2846
2847 ln_tax_unit_id_context := -1;
2848 ln_tax_unit_id_context := get_context_val(
2849 p_context_name => 'TAX_UNIT_ID'
2850 , p_assignment_id => ln_assignment_id
2851 , p_asg_act_id => ln_pymt_balcall_aaid);
2852 if ((ln_tax_unit_id_context = -1) or
2853 (ln_tax_unit_id_context is null)) then
2854 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2855 end if;
2856
2857 elsif pay_ac_action_arch.gv_reporting_level = 'GRE' then
2858 ln_tax_unit_id_context := -1;
2859 ln_tax_unit_id_context := get_context_val(
2860 p_context_name => 'TAX_UNIT_ID'
2861 , p_assignment_id => ln_assignment_id
2862 , p_asg_act_id => ln_pymt_balcall_aaid);
2863 gn_taxgrp_gre_id := ln_tax_unit_id_context;
2864
2865 if ((ln_tax_unit_id_context = -1) or
2866 (ln_tax_unit_id_context is null)) then
2867 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2868 gn_taxgrp_gre_id := ln_tax_unit_id;
2869 end if;
2870 end if;
2871
2872 ln_step := 15;
2873 populate_fed_prov_bal( p_xfr_action_id => p_xfr_action_id
2874 ,p_assignment_id => ln_assignment_id
2875 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
2876 ,p_tax_unit_id => ln_tax_unit_id
2877 ,p_action_type => lv_master_action_type
2878 ,p_pymt_eff_date => ld_curr_pymt_eff_date
2879 ,p_start_date => ld_start_date
2880 ,p_end_date => ld_end_date
2881 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
2882 );
2883
2884 ln_step := 16;
2885 hr_utility.trace('Populating Current Elements for Reversals');
2886 pay_ac_action_arch.get_current_elements(
2887 p_xfr_action_id => p_xfr_action_id
2888 ,p_curr_pymt_action_id => ln_pymt_balcall_aaid
2889 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2890 ,p_assignment_id => ln_assignment_id
2891 ,p_tax_unit_id => ln_tax_unit_id
2892 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
2893 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
2894 ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2895 ,p_sepchk_flag => lv_master_sepcheck_flag
2896 ,p_legislation_code => lv_legislation_code
2897 ,p_action_type => lv_master_action_type);
2898
2899 hr_utility.trace('Done Populating Tax Balances for Reversals');
2900 ln_step := 17;
2901 pay_emp_action_arch.insert_rows_thro_api_process(
2902 p_action_context_id => p_xfr_action_id
2903 ,p_action_context_type=> 'AAP'
2904 ,p_assignment_id => ln_assignment_id
2905 ,p_tax_unit_id => ln_tax_unit_id
2906 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2907 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
2908 );
2909
2910 end if; -- lv_master_action_type = 'V'
2911
2912
2913 /* Added this to archive the Balance Adjustments for the
2914 Payslip Archiver, this will be useful for historical reporting
2915 purposes when run_results table is purged. Bug#3498653 */
2916
2917 if lv_master_action_type = 'B' then
2918 hr_utility.trace('Populating Current Elements for Balance Adjustments');
2919 /* ln_asg_action_id is nothing but Balance Adjustment run action id */
2920 ln_asg_action_id := -1;
2921 pay_ac_action_arch.initialization_process;
2922 open c_get_emp_adjbal(p_xfr_action_id);
2923 loop
2924 fetch c_get_emp_adjbal into ln_asg_action_id;
2925 exit when c_get_emp_adjbal%NOTFOUND;
2926
2927 ln_pymt_balcall_aaid := ln_asg_action_id ;
2928 hr_utility.trace('Bal Adjustment ln_pymt_balcall_aaid'
2929 ||to_char(ln_pymt_balcall_aaid));
2930
2931 ln_step := 18;
2932
2933 hr_utility.trace('ln_tax_unit_id : '||to_char(ln_tax_unit_id));
2934 hr_utility.trace('ln_pymt_balcall_aaid :'||to_char(ln_pymt_balcall_aaid));
2935 hr_utility.trace('ld_curr_pymt_eff_date :'||to_char(ld_curr_pymt_eff_date,'DD-MON-YYYY'));
2936 hr_utility.trace('ln_assignment_id :'||to_char(ln_assignment_id));
2937
2938 /* Added this to support tax group level reporting for
2939 Balance Adjustments. Need to set both contexts for
2940 Tax Group reporting because for current amounts we
2941 use GRE context and for YTD amounts we use Tax_Group
2942 context */
2943
2944 if pay_ac_action_arch.gv_reporting_level = 'TAXGRP' then
2945 ln_tax_group_id := get_taxgroup_val(
2946 p_tax_unit_id => ln_tax_unit_id,
2947 p_assignment_id => ln_assignment_id,
2948 p_asg_act_id =>ln_pymt_balcall_aaid);
2949 pay_balance_pkg.set_context('TAX_GROUP', ln_tax_group_id);
2950 gn_taxgrp_gre_id := ln_tax_group_id;
2951
2952 ln_tax_unit_id_context := -1;
2953 ln_tax_unit_id_context := get_context_val(
2954 p_context_name => 'TAX_UNIT_ID'
2955 , p_assignment_id => ln_assignment_id
2956 , p_asg_act_id => ln_pymt_balcall_aaid);
2957
2958 if ((ln_tax_unit_id_context = -1) or
2959 (ln_tax_unit_id_context is null)) then
2960 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2961 end if;
2962
2963 elsif pay_ac_action_arch.gv_reporting_level = 'GRE' then
2964 ln_tax_unit_id_context := -1;
2965 ln_tax_unit_id_context := get_context_val(
2966 p_context_name => 'TAX_UNIT_ID'
2967 , p_assignment_id => ln_assignment_id
2968 , p_asg_act_id => ln_pymt_balcall_aaid);
2969 gn_taxgrp_gre_id := ln_tax_unit_id_context;
2970
2971 if ((ln_tax_unit_id_context = -1) or
2972 (ln_tax_unit_id_context is null)) then
2973 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2974 gn_taxgrp_gre_id := ln_tax_unit_id;
2975 end if;
2976 end if; -- gv_reporting_level = 'TAXGRP'
2977
2978 /* Need to pass Payslip Archiver Assignment_Action_id to
2979 p_curr_pymt_action_id because we have to archive Bal Adjustments
2980 that are not marked for 'Pre-Payment' in canada, Otherwise nothing
2981 will be archived. */
2982
2983 if ln_asg_action_id <> -1 and ln_asg_action_id is not null then
2984 ln_step := 19;
2985 pay_ac_action_arch.get_current_elements(
2986 p_xfr_action_id => p_xfr_action_id
2987 ,p_curr_pymt_action_id => p_xfr_action_id
2988 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
2989 ,p_assignment_id => ln_assignment_id
2990 ,p_tax_unit_id => ln_tax_unit_id
2991 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
2992 ,p_ytd_balcall_aaid => ln_pymt_balcall_aaid
2993 ,p_sepchk_run_type_id => ln_sepchk_run_type_id
2994 ,p_sepchk_flag => lv_master_sepcheck_flag
2995 ,p_legislation_code => lv_legislation_code
2996 ,p_action_type => lv_master_action_type);
2997 end if;
2998 end loop;
2999 close c_get_emp_adjbal;
3000
3001 ln_step := 20;
3002 pay_emp_action_arch.insert_rows_thro_api_process(
3003 p_action_context_id => p_xfr_action_id
3004 ,p_action_context_type=> 'AAP'
3005 ,p_assignment_id => ln_assignment_id
3006 ,p_tax_unit_id => ln_tax_unit_id
3007 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
3008 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
3009 );
3010
3011 end if; -- master_action_type = 'B'
3012 /* End of Balance Adjustments archiving Bug#3498653 */
3013
3014 /****************************************************************
3015 ** Archive all the payroll action level data once only when
3016 ** chunk number is 1. Also check if this has not been archived
3017 ** earlier
3018 *****************************************************************/
3019 ln_step := 21;
3020 hr_utility.set_location(gv_package || lv_procedure_name,210);
3021 open c_check_pay_action( ln_xfr_payroll_action_id);
3022 fetch c_check_pay_action into ln_pay_action_count;
3023 close c_check_pay_action;
3024 if ln_pay_action_count = 0 then
3025 hr_utility.set_location(gv_package || lv_procedure_name,215);
3026 ln_step := 22;
3027 if ln_chunk_number = 1 then
3028 pay_emp_action_arch.arch_pay_action_level_data(
3029 p_payroll_action_id => ln_xfr_payroll_action_id
3030 ,p_payroll_id => ln_payroll_id
3031 ,p_effective_Date => ld_end_date
3032 );
3033 end if;
3034
3035 end if;
3036
3037 EXCEPTION
3038 when others then
3039 hr_utility.set_location(gv_package || lv_procedure_name, 500);
3040 lv_error_message := 'Error at step ' || ln_step ||
3041 ' in ' || gv_package || lv_procedure_name;
3042 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3043
3044 lv_error_message :=
3045 pay_emp_action_arch.set_error_message(lv_error_message);
3046
3047 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3048 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3049 hr_utility.raise_error;
3050
3051 END py_archive_data;
3052
3053 /******************************************************************
3054 Name : py_range_cursor
3055 Purpose : This returns the select statement that is used to created the
3056 range rows for the Canadian Payroll Archiver.
3057 Arguments :
3058 Notes : Calls procedure - get_payroll_action_info
3059 ******************************************************************/
3060 PROCEDURE py_range_cursor( p_payroll_action_id in number
3061 ,p_sqlstr out nocopy varchar2)
3062 IS
3063
3064 ld_end_date DATE;
3065 ld_start_date DATE;
3066 ln_business_group_id NUMBER;
3067 ln_cons_set_id NUMBER;
3068 ln_payroll_id NUMBER;
3069
3070 lv_sql_string VARCHAR2(32000);
3071
3072 lv_error_message VARCHAR2(500);
3073 lv_procedure_name VARCHAR2(100);
3074 ln_step NUMBER;
3075
3076 begin
3077
3078 lv_procedure_name := '.py_range_cursor';
3079 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3080 pay_emp_action_arch.gv_error_message := NULL;
3081
3082 ln_step := 1;
3083 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
3084 ,p_start_date => ld_start_date
3085 ,p_end_date => ld_end_date
3086 ,p_business_group_id => ln_business_group_id
3087 ,p_cons_set_id => ln_cons_set_id
3088 ,p_payroll_id => ln_payroll_id);
3089 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3090
3091 ln_step := 2;
3092 /* removed the reversal validation from range cursor SQL STMT
3093 ''V'', nvl(ppa.future_process_mode, ''Y''). Bug#3498653 */
3094 lv_sql_string :=
3095 'select distinct paa.assignment_id
3096 from pay_assignment_actions paa,
3097 pay_payroll_actions ppa
3098 where ppa.business_group_id = ''' || ln_business_group_id || '''
3099 and ppa.effective_date between fnd_date.canonical_to_date(''' ||
3100 fnd_date.date_to_canonical(ld_start_date) || ''')
3101 and fnd_date.canonical_to_date(''' ||
3102 fnd_date.date_to_canonical(ld_end_date) || ''')
3103 and ppa.action_type in (''U'',''P'',''B'',''V'')
3104 and decode(ppa.action_type,
3105 ''B'', nvl(ppa.future_process_mode, ''Y''),
3106 ''N'') = ''N''
3107 and ppa.action_status =''C''
3108 and ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
3109 and ppa.payroll_id = ''' || ln_payroll_id || '''
3110 and ppa.payroll_action_id = paa.payroll_action_id
3111 and paa.action_status = ''C''
3112 and paa.source_action_id is null
3113 and not exists
3114 (select ''x''
3115 from pay_action_interlocks pai,
3116 pay_assignment_actions paa1,
3117 pay_payroll_actions ppa1
3118 where pai.locked_action_id = paa.assignment_action_id
3119 and paa1.assignment_action_id = pai.locking_action_id
3120 and ppa1.payroll_action_id = paa1.payroll_action_id
3121 and ppa1.action_type =''X''
3122 and ppa1.report_type = ''PY_ARCHIVER'')
3123 and :payroll_action_id > 0
3124 order by paa.assignment_id';
3125
3126 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3127 ln_step := 3;
3128 p_sqlstr := lv_sql_string;
3129 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3130
3131 EXCEPTION
3132 when others then
3133 hr_utility.set_location(gv_package || lv_procedure_name, 500);
3134 lv_error_message := 'Error at step ' || ln_step ||
3135 ' in ' || gv_package || lv_procedure_name;
3136 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3137
3138 lv_error_message :=
3139 pay_emp_action_arch.set_error_message(lv_error_message);
3140
3141 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3142 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3143 hr_utility.raise_error;
3144
3145 END py_range_cursor;
3146
3147
3148 /************************************************************
3149 Name : py_action_creation
3150 Purpose : This creates the assignment actions for
3151 a specific chunk of people to be archived
3152 by the Archiver process.
3153 Arguments :
3154 Notes : Calls procedure - get_payroll_action_info
3155 ************************************************************/
3156
3157 PROCEDURE py_action_creation(
3158 p_payroll_action_id in number
3159 ,p_start_assignment_id in number
3160 ,p_end_assignment_id in number
3161 ,p_chunk in number)
3162 IS
3163 /* removed the reversal validation in the decode stmt in cursor
3164 c_get_xfr_emp 'V', nvl(ppa.future_process_mode, 'Y'). Bug#3498653 */
3165 cursor c_get_xfr_emp( cp_start_assignment_id in number
3166 ,cp_end_assignment_id in number
3167 ,cp_cons_set_id in number
3168 ,cp_payroll_id in number
3169 ,cp_business_group_id in number
3170 ,cp_start_date in date
3171 ,cp_end_date in date
3172 ) is
3173 select /*+ INDEX (PAA PAY_ASSIGNMENT_ACTIONS_N50) */
3174 paa.assignment_id,
3175 paa.tax_unit_id,
3176 ppa.effective_date,
3177 ppa.date_earned,
3178 ppa.action_type,
3179 paa.assignment_action_id,
3180 paa.payroll_action_id
3181 from pay_payroll_actions ppa,
3182 pay_assignment_actions paa
3183 where paa.assignment_id between cp_start_assignment_id
3184 and cp_end_assignment_id
3185 and ppa.consolidation_set_id = cp_cons_set_id
3186 and paa.action_status = 'C'
3187 and ppa.payroll_id = cp_payroll_id
3188 and ppa.payroll_action_id = paa.payroll_action_id
3189 and ppa.business_group_id = cp_business_group_id
3190 and ppa.action_status = 'C'
3191 and ppa.effective_date between cp_start_date
3192 and cp_end_date
3193 and ppa.action_type in ('U','P','B','V')
3194 and decode(ppa.action_type,
3195 'B', nvl(ppa.future_process_mode, 'Y'),
3196 'N') = 'N'
3197 and paa.source_action_id is null
3198 and not exists
3199 (select 'x'
3200 from pay_action_interlocks pai1,
3201 pay_assignment_actions paa1,
3202 pay_payroll_actions ppa1
3203 where pai1.locked_action_id = paa.assignment_action_id
3204 and paa1.assignment_action_id = pai1.locking_action_id
3205 and ppa1.payroll_action_id = paa1.payroll_action_id
3206 and ppa1.action_type ='X'
3207 and ppa1.report_type = 'PY_ARCHIVER')
3208 order by 1,2,3,4,5;
3209
3210 cursor c_master_action(cp_prepayment_action_id number) is
3211 select max(paa.assignment_action_id)
3212 from pay_payroll_actions ppa,
3213 pay_assignment_actions paa,
3214 pay_action_interlocks pai
3215 where pai.locking_action_Id = cp_prepayment_action_id
3216 and pai.locked_action_id = paa.assignment_action_id
3217 and paa.source_action_id is null
3218 and ppa.payroll_action_id = paa.payroll_action_id
3219 and ppa.action_type in ('R', 'Q');
3220
3221 cursor c_lock_chld_pp_aa(cp_prepay_master_aa_id number) is
3222 select paa.assignment_action_id
3223 from pay_assignment_actions paa
3224 where paa.source_action_id = cp_prepay_master_aa_id;
3225
3226 ln_assignment_id NUMBER;
3227 ln_tax_unit_id NUMBER;
3228 ld_effective_date DATE;
3229 ld_date_earned DATE;
3230 lv_action_type VARCHAR2(10);
3231 ln_asg_action_id NUMBER;
3232 ln_payroll_action_id NUMBER;
3233
3234 ln_master_action_id NUMBER;
3235
3236 ld_end_date DATE;
3237 ld_start_date DATE;
3238 ln_business_group_id NUMBER;
3239 ln_cons_set_id NUMBER;
3240 ln_payroll_id NUMBER;
3241
3242 ln_prev_asg_action_id NUMBER;
3243 ln_prev_assignment_id NUMBER;
3244 ln_prev_tax_unit_id NUMBER;
3245 ld_prev_effective_date DATE;
3246
3247 ln_xfr_action_id NUMBER;
3248
3249 lv_serial_number VARCHAR2(30);
3250
3251 lv_error_message VARCHAR2(500);
3252 lv_procedure_name VARCHAR2(100);
3253 ln_step NUMBER;
3254
3255 BEGIN
3256
3257 --- hr_utility.trace_on(null, 'PYARCH');
3258
3259 lv_procedure_name := '.py_action_creation';
3260 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3261 pay_emp_action_arch.gv_error_message := NULL;
3262
3263 ln_step := 1;
3264 /* Initialising local variables to avoid GSCC warnings */
3265 ln_assignment_id := 0;
3266 ln_tax_unit_id := 0;
3267 ld_effective_date := to_date('1900/12/31','YYYY/MM/DD');
3268 ln_asg_action_id := 0;
3269 ln_payroll_action_id := 0;
3270
3271 ln_master_action_id := 0;
3272 ln_prev_asg_action_id := 0;
3273 ln_prev_assignment_id := 0;
3274 ln_prev_tax_unit_id := 0;
3275 ld_prev_effective_date := to_date('1800/12/31','YYYY/MM/DD');
3276
3277 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
3278 ,p_start_date => ld_start_date
3279 ,p_end_date => ld_end_date
3280 ,p_business_group_id => ln_business_group_id
3281 ,p_cons_set_id => ln_cons_set_id
3282 ,p_payroll_id => ln_payroll_id);
3283 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3284
3285 hr_utility.trace('ld_start_date '||ld_start_date);
3286 hr_utility.trace('ld_end_date '||ld_end_date);
3287 hr_utility.trace('ln_business_group_id '||ln_business_group_id);
3288 hr_utility.trace('ln_cons_set_id '||ln_cons_set_id);
3289 hr_utility.trace('ln_payroll_id '||ln_payroll_id);
3290
3291
3292 ln_step := 2;
3293 open c_get_xfr_emp( p_start_assignment_id
3294 ,p_end_assignment_id
3295 ,ln_cons_set_id
3296 ,ln_payroll_id
3297 ,ln_business_group_id
3298 ,ld_start_date
3299 ,ld_end_date);
3300
3301 -- Loop for all rows returned for SQL statement.
3302 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3303 loop
3304 ln_step := 3;
3305 fetch c_get_xfr_emp into ln_assignment_id,
3306 ln_tax_unit_id,
3307 ld_effective_date,
3308 ld_date_earned,
3309 lv_action_type,
3310 ln_asg_action_id,
3311 ln_payroll_action_id;
3312
3313 exit when c_get_xfr_emp%notfound;
3314
3315 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3316 hr_utility.trace('ln_assignment_id = ' ||
3317 to_char(ln_assignment_id));
3318
3319 /********************************************************
3320 ** If Balance Adjustment, only create one assignment
3321 ** action record. As there could be multiple assignment
3322 ** actions for Balance Adjustment, we lock all the
3323 ** balance adj record.
3324 ** First time the else portion will be executed which
3325 ** creates the assignment action. If the Assignment ID,
3326 ** Tax Unit ID and Effective Date is same and Action
3327 ** Type is Balance Adj only lock the record
3328 ********************************************************/
3329 ln_step := 4;
3330 if ln_assignment_id = ln_prev_assignment_id and
3331 ln_tax_unit_id = ln_prev_tax_unit_id and
3332 ld_effective_date = ld_prev_effective_date and
3333 lv_action_type = 'B' and
3334 ln_asg_action_id <> ln_prev_asg_action_id then
3335
3336 ln_step := 5;
3337 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3338 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
3339 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
3340 hr_nonrun_asact.insint(ln_xfr_action_id
3341 ,ln_asg_action_id);
3342 else
3343 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3344 hr_utility.trace('Action_type = '||lv_action_type );
3345
3346 ln_step := 6;
3347 select pay_assignment_actions_s.nextval
3348 into ln_xfr_action_id
3349 from dual;
3350
3351 -- insert into pay_assignment_actions.
3352 ln_step := 7;
3353 hr_nonrun_asact.insact(ln_xfr_action_id,
3354 ln_assignment_id,
3355 p_payroll_action_id,
3356 p_chunk,
3357 ln_tax_unit_id,
3358 null,
3359 'U',
3360 null);
3361 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3362 hr_utility.trace('ln_asg_action_id = ' || ln_asg_action_id);
3363 hr_utility.trace('ln_xfr_action_id = ' || ln_xfr_action_id);
3364 hr_utility.trace('p_payroll_action_id = ' || p_payroll_action_id);
3365 hr_utility.trace('ln_tax_unit_id = ' || ln_tax_unit_id);
3366 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3367
3368 -- insert an interlock to this action
3369 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
3370 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
3371 ln_step := 8;
3372 hr_nonrun_asact.insint(ln_xfr_action_id,
3373 ln_asg_action_id);
3374
3375 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3376
3377 for lock_pp_aa in c_lock_chld_pp_aa(ln_asg_action_id)
3378 loop
3379
3380 hr_utility.trace('Locked Action by Master = ' ||
3381 lock_pp_aa.assignment_action_id);
3382 hr_nonrun_asact.insint(ln_xfr_action_id,
3383 lock_pp_aa.assignment_action_id);
3384
3385 end loop;
3386
3387 /********************************************************
3388 ** For Balance Adj we put only the first assignment action
3389 ********************************************************/
3390 lv_serial_number := lv_action_type || 'N' ||
3391 ln_asg_action_id;
3392
3393 ln_step := 9;
3394 update pay_assignment_actions
3395 set serial_number = lv_serial_number
3396 where assignment_action_id = ln_xfr_action_id;
3397
3398 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3399
3400 end if ; --ln_assignment_id ...
3401
3402 ln_step := 10;
3403 ln_prev_tax_unit_id := ln_tax_unit_id;
3404 ld_prev_effective_date := ld_effective_date;
3405 ln_prev_assignment_id := ln_assignment_id;
3406 ln_prev_asg_action_id := ln_asg_action_id;
3407
3408 end loop;
3409 close c_get_xfr_emp;
3410
3411 EXCEPTION
3412 when others then
3413 hr_utility.set_location(gv_package || lv_procedure_name, 500);
3414 lv_error_message := 'Error at step ' || ln_step ||
3415 ' in ' || gv_package || lv_procedure_name;
3416 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3417
3418 lv_error_message :=
3419 pay_emp_action_arch.set_error_message(lv_error_message);
3420
3421 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3422 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3423 hr_utility.raise_error;
3424
3425 END py_action_creation;
3426
3427 /************************************************************
3428 Name : py_archinit
3429 Purpose : This performs the context initialization.
3430 Arguments :
3431 Notes :
3432 ************************************************************/
3433
3434 PROCEDURE py_archinit(p_payroll_action_id in number) is
3435
3436 cursor cur_reporting_level(p_pactid in number) is
3437 select org_information1
3438 from hr_organization_information hoi,
3439 pay_payroll_actions ppa
3440 where ppa.payroll_action_id = p_pactid
3441 and hoi.organization_id = ppa.business_group_id
3442 and hoi.org_information_context = 'Payroll Archiver Level';
3443
3444 cursor cur_def_bal is
3445 select pbt.balance_name,
3446 decode(pbt.balance_name,
3447 'CPP EE Withheld', 1,
3448 'QPP EE Withheld', 2,
3449 'EI EE Withheld', 3,
3450 'PPIP EE Withheld',4,
3451 'FED Withheld', 5,
3452 'PROV Withheld', 6,
3453 7) display_sequence,
3454 pbt.balance_type_id
3455 from pay_balance_types pbt
3456 where pbt.legislation_code = 'CA'
3457 and pbt.balance_name in ( 'FED Withheld',
3458 'CPP EE Withheld',
3459 'EI EE Withheld',
3460 'PROV Withheld',
3461 'QPP EE Withheld',
3462 'PPIP EE Withheld')
3463 order by 2;
3464
3465 cursor cur_tax_name is
3466 select language, lookup_code, meaning
3467 from fnd_lookup_values
3468 where lookup_type = 'CA_SOE_SHORT_NAME';
3469
3470 cursor cur_mg_payment is
3471 select rule_mode
3472 from pay_legislation_rules
3473 where legislation_code = 'CA'
3474 and rule_type = 'MULTI_TAX_UNIT_PAYMENT';
3475
3476 cursor cur_bal_type is
3477 select balance_name,
3478 balance_type_id
3479 from pay_balance_types
3480 where legislation_code = 'CA'
3481 and balance_name in ( 'Gross Earnings', 'Payments' );
3482
3483
3484 ln_pymt_def_bal_id number;
3485 ln_gre_ytd_def_bal_id number;
3486 ln_tg_ytd_def_bal_id number;
3487 lv_reporting_level varchar2(30);
3488 lv_jd_pymt_dimension varchar2(100);
3489 lv_pymt_dimension varchar2(100);
3490 /* Added new variable for canada reversals. Bug#3498653 */
3491 ln_run_def_bal_id number;
3492
3493 lv_error_message VARCHAR2(500);
3494 lv_procedure_name VARCHAR2(100);
3495 ln_step NUMBER;
3496
3497 ln_run_bal_type_id number;
3498 lv_balance_name varchar2(100);
3499
3500 i number;
3501 j number;
3502
3503 BEGIN
3504 lv_procedure_name := '.py_archinit';
3505 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3506 pay_emp_action_arch.gv_error_message := NULL;
3507 lv_reporting_level := Null;
3508 i := 0;
3509 j := 0;
3510
3511 ln_step := 5;
3512 open cur_mg_payment;
3513 fetch cur_mg_payment into pay_ac_action_arch.gv_multi_gre_payment;
3514 if cur_mg_payment%notfound then
3515 pay_ac_action_arch.gv_multi_gre_payment := 'N';
3516 end if;
3517 close cur_mg_payment;
3518
3519 ln_step := 6;
3520 open cur_bal_type;
3521 loop
3522 fetch cur_bal_type into lv_balance_name, ln_run_bal_type_id;
3523 exit when cur_bal_type%notfound;
3524 if lv_balance_name = 'Payments' then
3525 gn_payments_def_bal_id :=
3526 nvl(pay_emp_action_arch.get_defined_balance_id(
3527 ln_run_bal_type_id,
3528 '_ASG_RUN',
3529 'CA'),-1);
3530 else
3531 gn_gross_earn_def_bal_id :=
3532 nvl(pay_emp_action_arch.get_defined_balance_id(
3533 ln_run_bal_type_id,
3534 '_ASG_RUN',
3535 'CA'),-1);
3536 end if;
3537 end loop;
3538 close cur_bal_type;
3539
3540
3541 ln_step := 10;
3542 open cur_reporting_level(p_payroll_action_id);
3543 fetch cur_reporting_level into lv_reporting_level;
3544 if cur_reporting_level%notfound then
3545 lv_reporting_level := 'GRE';
3546 end if;
3547 close cur_reporting_level;
3548
3549 pay_ac_action_arch.gv_reporting_level := lv_reporting_level;
3550
3551 ln_step := 20;
3552 if pay_emp_action_arch.gv_multi_leg_rule is null then
3553 pay_emp_action_arch.gv_multi_leg_rule
3554 := pay_emp_action_arch.get_multi_legislative_rule('CA');
3555 end if;
3556
3557 hr_utility.trace('lv_reporting_level : '|| lv_reporting_level);
3558 hr_utility.trace('gv_multi_leg_rule : ' || pay_emp_action_arch.gv_multi_leg_rule);
3559 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3560
3561 ln_step := 30;
3562 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
3563 lv_pymt_dimension := '_ASG_PAYMENTS';
3564 lv_jd_pymt_dimension := '_ASG_PAYMENTS_JD';
3565 else
3566 lv_pymt_dimension := '_PAYMENTS';
3567 lv_jd_pymt_dimension := '_PAYMENTS_JD';
3568 end if;
3569
3570 ln_step := 40;
3571 dbt.delete;
3572 tax.delete;
3573 i := 0;
3574
3575 ln_step := 50;
3576 for c_dbt in cur_def_bal loop
3577
3578 ln_pymt_def_bal_id := 0;
3579 ln_gre_ytd_def_bal_id := 0;
3580 ln_tg_ytd_def_bal_id := 0;
3581 /* Added to archive reversals 'V', Bug#3498653 */
3582 ln_run_def_bal_id := 0;
3583
3584 if c_dbt.balance_name in ('PROV Withheld', 'QPP EE Withheld' , 'PPIP EE Withheld') -- 4566656
3585 then
3586
3587 ln_step := 60;
3588 ln_pymt_def_bal_id :=
3589 pay_emp_action_arch.get_defined_balance_id(
3590 c_dbt.balance_type_id,
3591 lv_jd_pymt_dimension,
3592 'CA');
3593
3594 ln_step := 70;
3595 ln_gre_ytd_def_bal_id :=
3596 pay_emp_action_arch.get_defined_balance_id(
3597 c_dbt.balance_type_id,
3598 '_ASG_JD_GRE_YTD',
3599 'CA');
3600
3601 ln_step := 80;
3602 ln_tg_ytd_def_bal_id :=
3603 pay_emp_action_arch.get_defined_balance_id(
3604 c_dbt.balance_type_id,
3605 '_ASG_JD_TG_YTD',
3606 'CA');
3607
3608 /* Modifying to check for reversals for Canada. Bug#3498653 */
3609 ln_run_def_bal_id :=
3610 pay_emp_action_arch.get_defined_balance_id(
3611 c_dbt.balance_type_id,
3612 '_ASG_JD_GRE_RUN',
3613 'CA');
3614
3615 else
3616 ln_step := 90;
3617 ln_pymt_def_bal_id :=
3618 pay_emp_action_arch.get_defined_balance_id(
3619 c_dbt.balance_type_id,
3620 lv_pymt_dimension,
3621 'CA');
3622
3623 ln_step := 100;
3624 ln_gre_ytd_def_bal_id :=
3625 pay_emp_action_arch.get_defined_balance_id(
3626 c_dbt.balance_type_id,
3627 '_ASG_GRE_YTD',
3628 'CA');
3629
3630 ln_step := 110;
3631 ln_tg_ytd_def_bal_id :=
3632 pay_emp_action_arch.get_defined_balance_id(
3633 c_dbt.balance_type_id,
3634 '_ASG_TG_YTD',
3635 'CA');
3636
3637 /* Modifying to check for reversals for Canada. Bug#3498653 */
3638 ln_run_def_bal_id :=
3639 pay_emp_action_arch.get_defined_balance_id(
3640 c_dbt.balance_type_id,
3641 '_ASG_GRE_RUN',
3642 'CA');
3643
3644 end if;
3645
3646 if ( c_dbt.balance_name = 'PROV Withheld' ) then
3647 ln_step := 120;
3648 for j in 1..3 loop
3649 dbt(i).bal_name := c_dbt.balance_name;
3650 dbt(i).disp_sequence := c_dbt.display_sequence;
3651 dbt(i).bal_type_id := c_dbt.balance_type_id;
3652 dbt(i).pymt_def_bal_id := ln_pymt_def_bal_id;
3653 dbt(i).gre_ytd_def_bal_id := ln_gre_ytd_def_bal_id;
3654 dbt(i).tg_ytd_def_bal_id := ln_tg_ytd_def_bal_id;
3655 /* Added run_def_bal_id for reversals in Canada */
3656 dbt(i).run_def_bal_id := ln_run_def_bal_id;
3657
3658 if j = 1 then
3659 dbt(i).jurisdiction_cd := 'NT';
3660 elsif j = 2 then
3661 dbt(i).jurisdiction_cd := 'NU';
3662 else
3663 dbt(i).jurisdiction_cd := 'QC';
3664 end if;
3665 hr_utility.trace(dbt(i).jurisdiction_cd);
3666 i := i + 1;
3667 end loop;
3668 elsif ( c_dbt.balance_name = 'QPP EE Withheld' ) then
3669 ln_step := 130;
3670 dbt(i).bal_name := c_dbt.balance_name;
3671 dbt(i).disp_sequence := c_dbt.display_sequence;
3672 dbt(i).bal_type_id := c_dbt.balance_type_id;
3673 dbt(i).pymt_def_bal_id := ln_pymt_def_bal_id;
3674 dbt(i).gre_ytd_def_bal_id := ln_gre_ytd_def_bal_id;
3675 dbt(i).tg_ytd_def_bal_id := ln_tg_ytd_def_bal_id;
3676 /* Added run_def_bal_id for reversals in Canada. Bug#3498653 */
3677 dbt(i).run_def_bal_id := ln_run_def_bal_id;
3678
3679 dbt(i).jurisdiction_cd := 'QC';
3680 i := i + 1;
3681 elsif ( c_dbt.balance_name = 'PPIP EE Withheld' ) then -- 4566656: Added the code for PPIP EE Withheld
3682 ln_step := 140;
3683 dbt(i).bal_name := c_dbt.balance_name;
3684 dbt(i).disp_sequence := c_dbt.display_sequence;
3685 dbt(i).bal_type_id := c_dbt.balance_type_id;
3686 dbt(i).pymt_def_bal_id := ln_pymt_def_bal_id;
3687 dbt(i).gre_ytd_def_bal_id := ln_gre_ytd_def_bal_id;
3688 dbt(i).tg_ytd_def_bal_id := ln_tg_ytd_def_bal_id;
3689 dbt(i).run_def_bal_id := ln_run_def_bal_id;
3690
3691 dbt(i).jurisdiction_cd := 'QC';
3692 i := i + 1;
3693 else
3694 ln_step := 150;
3695 dbt(i).bal_name := c_dbt.balance_name;
3696 dbt(i).disp_sequence := c_dbt.display_sequence;
3697 dbt(i).bal_type_id := c_dbt.balance_type_id;
3698 dbt(i).pymt_def_bal_id := ln_pymt_def_bal_id;
3699 dbt(i).gre_ytd_def_bal_id := ln_gre_ytd_def_bal_id;
3700 dbt(i).tg_ytd_def_bal_id := ln_tg_ytd_def_bal_id;
3701 /* Added run_def_bal_id for reversals in Canada. Bug#3498653 */
3702 dbt(i).run_def_bal_id := ln_run_def_bal_id;
3703
3704 dbt(i).jurisdiction_cd := '-1';
3705 i := i + 1;
3706 end if;
3707
3708 end loop;
3709
3710 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3711 i := 0;
3712
3713 ln_step := 160;
3714 for tax_short_name in cur_tax_name loop
3715
3716 tax(i).language := tax_short_name.language;
3717 tax(i).lookup_code := tax_short_name.lookup_code;
3718 tax(i).meaning := tax_short_name.meaning;
3719
3720 hr_utility.trace(tax(i).language);
3721 hr_utility.trace(tax(i).lookup_code);
3722 hr_utility.trace(tax(i).meaning);
3723
3724 i := i + 1;
3725
3726 end loop;
3727
3728 ln_step := 170;
3729 for i in dbt.first..dbt.last loop
3730 hr_utility.trace(dbt(i).bal_name);
3731 hr_utility.trace(dbt(i).disp_sequence);
3732 hr_utility.trace(dbt(i).bal_type_id);
3733 hr_utility.trace(dbt(i).pymt_def_bal_id);
3734 hr_utility.trace(dbt(i).gre_ytd_def_bal_id);
3735 hr_utility.trace(dbt(i).tg_ytd_def_bal_id);
3736 -- Added run_def_bal_id for reversals in canada Bug#3498653
3737 hr_utility.trace(dbt(i).run_def_bal_id);
3738 hr_utility.trace(dbt(i).jurisdiction_cd);
3739 end loop;
3740
3741 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3742
3743 EXCEPTION
3744 when others then
3745 hr_utility.set_location(gv_package || lv_procedure_name, 500);
3746 lv_error_message := 'Error at step ' || ln_step ||
3747 ' in ' || gv_package || lv_procedure_name;
3748 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3749
3750 lv_error_message :=
3751 pay_emp_action_arch.set_error_message(lv_error_message);
3752
3753 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3754 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3755 hr_utility.raise_error;
3756
3757 END py_archinit;
3758
3759 --begin
3760 --hr_utility.trace_on (null, 'PYARCH');
3761
3762 end pay_ca_payroll_arch;