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