[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_RL2_ARCHIVE
Source
1 package body pay_ca_eoy_rl2_archive as
2 /* $Header: pycarl2a.pkb 120.11 2006/08/28 22:18:03 meshah noship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description : Canadian EOY RL2 Archiver Process
26
27 Change List
28
29 Date Name Vers Bug No Description
30
31 30-SEP-2002 SSattini 115.0 Created
32 22-OCT-2002 SSattini 115.1 2618558 Earlier Box O used to archive
33 Beneficiary SIN but for YE-2002
34 it has changed and currently
35 Box O archives 'Withdrawal under
36 the Home Buyers Plan' value.
37 Also changed the queries
38 in eoy_archive_date,
39 eoy_action_creation to
40 improve the performance.
41 01-NOV-2002 SSattini 115.2 2647945 Renamed balance names for RL2
42 'Income Earned After death' to
43 'Income earned after death RRSP
44 or RRIF' and
45 'Withdrawal under the LPP' to
46 'Withdrawal under the Lifelong
47 Learning Plan'.
48 15-NOV-2002 SSattini 115.3 2671025 Range cursor sql stmt was
49 erroring out with invalid number,
50 corrected it for HRNOV02 bugfix.
51 19-NOV-2002 SSattini 115.4 2675144 RL2 Box N was archiving beneficiary
52 name earlier, changed to archive
53 beneficiary sin.
54 22-NOV-2002 SSattini 115.5 2681250 Fixed the archiving of employee
55 address.
56 02-DEC-2002 SSattini 115.6 Added 'nocopy' for out and in out
57 parameters, GSCC compliance.
58 27-AUG-2003 SSouresr 115.7 If the new balance 'RL2 No Gross Earnings'
59 is non zero then archiving will take place
60 even if gross earnings is zero
61 25-SEP-2003 mmukherj 115.8 Bugfix 3162038. The range cursor was
62 checking segment1 of softcoded
63 keyflex .But in the new flexfield
64 structure with multi-gre T4A/RL2
65 GRE will be in segment12.
66 06-NOV-2003 SSouresr 115.9 Changed Archiver to use Prov Reporting
67 Est instead of Quebec Business Number
68 08-JAN-2004 SSouresr 115.10 A new flag will be archived on the
69 employee level if any negative balances
70 exist.
71 20-FEB-2004 SSattini 115.11 3356533 Modified the cursor c_get_asg_act_id
72 and removed cursor c_all_gres_for_person
73 because we not using it. Part of fix
74 for 11510 bug#3356533.
75 22-MAR-2004 SSouresr 115.12 3513423 The extra person information data is now
76 retrieved through the cursor
77 c_get_person_extra_info. This cursor only
78 picks up the override data set corresponding
79 with the Archiver's PRE.
80 07-JUN-2004 SSattini 115.13 3638928 Modified the cursor
81 c_get_asg_act_id and
82 c_get_max_asg_act_id to get max
83 asgact_id based on person_id.
84 Fix for bug#3638928.
85 07-JUN-2004 SSattini 115.14 3638928 Fixed the GSCC Error
86 30-JUL-2004 SSouresr 115.15 3687849 Records are now archived against the primary
87 assignment id
88 05-NOV-2004 SSouresr 115.16 The RL2 No Gross Earnings balance should be
89 retrieved across all GREs
90 10-NOV-2004 SSouresr 115.17 Modified to use tables instead of views
91 to remove problems with security groups
92 28-NOV-2004 SSouresr 115.19 Added date range to c_get_max_asg_act_id
93 29-NOV-2004 SSouresr 115.20 Modified c_footnote_info to only return RL2
94 footnotes
95 04-MAR-2005 SSouresr 115.21 The province code for the employer address with
96 a Canadian International style is now archived
97 08-AUG-2005 mmukherj 115.22 The procedure eoy_archinit has been
98 modified to set the minimum chunk
99 no, which is required to re archive
100 the data while retrying the Archiver
101 in the payroll action level.
102 Bugfix: #4525642
103 17-AUG-2005 SSattini 115.23 3531136 Modified eoy_archive_data to archive
104 Source of income 'Other' as footnotes,
105 Also added Box L and O validation
106 Bug#3358604.
107 21-OCT-2005 SSouresr 115.24 The negative balance flag is archived as Y
108 if any of the RL2 footnotes is negative
109 29-NOV-2005 SSouresr 115.25 The first parameter passed to c_get_max_asg_act_id
110 for footnotes was changed from assignment_id to person_id
111 10-FEB-2006 SSouresr 115.26 Added RL2 Amendment functionality and removed
112 references to hr_soft_coding_key_flex
113 14-FEB-2006 SSouresr 115.27 CAEOY RL2 EMPLOYEE INFO2 is now archived for the RL2 process
114 as well as the RL2 Amendment Process
115 24-Apr-2006 ssmukher 115.28 Modified the sqlstr string variable in the procedure
116 eoy_range_cursor for bug #5120627 fix.
117 24-APR-2006 ssouresr 115.29 ln_index and ln_footnote_index were taken out of a
118 conditional statement to prevent the error message
119 NULL index table key value from occurring
120 The function compare_archive_data was also modified
121 for the situation where either the original RL2 or
122 the amended RL2 have not been archived
123 04-AUG-2006 YDEVI 115.30 RL2 archiver will used
124 PAY_CA_EOY_RL2_S instead of
125 PAY_CA_EOY_RL1_S. to generate
126 sequence number
127 18-AUG-2006 meshah 115.31 5202869 For performance issue modified the
128 cursor c_eoy_qbin.Removed the table
129 per_people_f and also disabled
130 few indexes to make sure the query
131 takes the correct path. With this
132 change the cost of the query has
133 increased but the path taken is
134 better.
135 28-AUG-2006 meshah 115.32 5495704 the way indexes were disabled has
136 been changed from using +0 to ||.
137
138 */
139
140 eoy_all_qbin varchar2(4000);
141
142 /* Name : get_def_bal_id
143 Purpose : Given the name of a balance and balance dimension
144 the function returns the defined_balance_id .
145
146 Arguments : balance_name,balance_dimension_name and legislation_code
147 Notes : A defined balance_id is required call pay_balance_pkg.get_value.
148 */
149
150 Function get_def_bal_id ( p_balance_name varchar2,
151 p_balance_dimension varchar2,
152 p_legislation_code varchar2)
153 return number is
154
155 /* Get the defined_balance_id for the specified balance name and dimension */
156
157 cursor csr_bal_type_id(cp_bal_name varchar2) is
158 select balance_type_id
159 from pay_balance_types
160 where balance_name = cp_bal_name;
161
162 cursor csr_def_bal_id(cp_bal_type_id number,
163 cp_bal_dimension varchar2,
164 cp_legislation_code varchar2) is
165 select pdb.defined_balance_id
166 from pay_defined_balances pdb,
167 pay_balance_dimensions pbd
168 where pdb.balance_type_id = cp_bal_type_id
169 and pbd.dimension_name = cp_bal_dimension
170 and pbd.balance_dimension_id = pdb.balance_dimension_id
171 and ((pbd.legislation_code = cp_legislation_code and
172 pbd.business_group_id is null)
173 or (pbd.legislation_code is null and
174 pbd.business_group_id is not null));
175
176 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
177 l_balance_type_id pay_balance_types.balance_type_id%type;
178
179 begin
180
181 open csr_bal_type_id(p_balance_name);
182 fetch csr_bal_type_id into l_balance_type_id;
183
184 if csr_bal_type_id%notfound then
185 close csr_bal_type_id;
186 /* need a pop-message */
187 hr_utility.trace('Balance name :'||p_balance_name||'doesnot exist');
188 raise hr_utility.hr_error;
189 else
190 close csr_bal_type_id;
191 end if;
192
193 open csr_def_bal_id(l_balance_type_id,p_balance_dimension,
194 p_legislation_code);
195 fetch csr_def_bal_id into l_defined_balance_id;
196 if csr_def_bal_id%notfound then
197 close csr_def_bal_id;
198 /* need a pop-message */
199 hr_utility.trace('Balance Dimension :'||p_balance_dimension||'doesnot exist');
200 raise hr_utility.hr_error;
201 else
202 close csr_def_bal_id;
203 end if;
204
205 return (l_defined_balance_id);
206
207 end get_def_bal_id;
208
209
210 /* Name : get_dates
211 Purpose : The dates are dependent on the report being run
212 For RL2 it is year end dates.
213 Arguments :
214 Notes :
215 */
216
217 procedure get_dates
218 (
219 p_report_type in varchar2,
220 p_effective_date in date,
221 p_period_end in out nocopy date,
222 p_quarter_start in out nocopy date,
223 p_quarter_end in out nocopy date,
224 p_year_start in out nocopy date,
225 p_year_end in out nocopy date
226 ) is
227 begin
228
229 if p_report_type = 'RL2' then
230
231 /* Year End Pre-process is a yearly process where the identifier
232 indicates the year eg. 1998. The expected values for the example
233 should be
234 p_period_end 31-DEC-1998
235 p_quarter_start 01-OCT-1998
236 p_quarter_end 31-DEC-1998
237 p_year_start 01-JAN-1998
238 p_year_end 31-DEC-1998
239 */
240
241 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
242 p_quarter_start := trunc(p_period_end, 'Q');
243 p_quarter_end := p_period_end;
244
245 /* For EOY */
246
247 end if;
248
249 p_year_start := trunc(p_effective_date, 'Y');
250 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
251
252 end get_dates;
253
254
255 /* Name : get_selection_information
256 Purpose : Returns information used in the selection of people to
257 be reported on.
258 Arguments :
259
260 The following values are returned :
261
262 p_period_start - The start of the period over which to select
263 the people.
264 p_period_end - The end of the period over which to select
265 the people.
266 p_defined_balance_id - The balance which must be non zero for each
267 person to be included in the report.
268 p_group_by_gre - should the people be grouped by GRE.
269 p_group_by_medicare - Should the people ,be grouped by medicare
270 within GRE NB. this is not currently supported.
271 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
272 the testing of the balance.
273 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
274 for the testing of the balance.
275
276 Notes : This routine provides a way of coding explicit rules for
277 individual reports where they are different from the
278 standard selection criteria for the report type ie. in
279 NY state the selection of people in the 4th quarter is
280 different from the first 3.
281 */
282
283 procedure get_selection_information
284 (
285
286 /* Identifies the type of report, the authority for which it is being run,
287 and the period being reported. */
288 p_report_type varchar2,
289 p_quarter_start date,
290 p_quarter_end date,
291 p_year_start date,
292 p_year_end date,
293 /* Information returned is used to control the selection of people to
294 report on. */
295 p_period_start in out nocopy date,
296 p_period_end in out nocopy date,
297 p_defined_balance_id in out nocopy number,
298 p_group_by_gre in out nocopy boolean,
299 p_group_by_medicare in out nocopy boolean,
300 p_tax_unit_context in out nocopy boolean,
301 p_jurisdiction_context in out nocopy boolean
302 ) is
303
304 begin
305
306 /* Depending on the report being processed, derive all the information
307 required to be able to select the people to report on. */
308
309 if p_report_type = 'RL2' then
310
311 /* Default settings for Year End Preprocess. */
312
313 hr_utility.trace('in getting selection information ');
314 p_period_start := p_year_start;
315 p_period_end := p_year_end;
316 p_defined_balance_id := 0;
317 p_group_by_gre := FALSE;
318 p_group_by_medicare := FALSE;
319 p_tax_unit_context := FALSE;
320 p_jurisdiction_context := FALSE;
321
322 /* For EOY end */
323
324 /* An invalid report type has been passed so fail. */
325
326 else
327 hr_utility.trace('in error of getting selection information ');
328
329 raise hr_utility.hr_error;
330
331 end if;
332
333 end get_selection_information;
334
335 /* Name : chk_rl2_footnote
336 Purpose : Function to check whether the RL2 Footnote to be archived
337 is valid or not.
338 Arguments :footnote_code
339 Notes :
340 */
341
342 function chk_rl2_footnote(p_footnote_code varchar2) return boolean is
343
344 l_flag varchar2(1);
345
346 cursor c_chk_footnote is
347 select 'Y'
348 from dual
349 where exists (select 'X'
350 from fnd_lookup_values
351 where ((lookup_type = 'PAY_CA_RL2_FOOTNOTES'
352 and lookup_code = p_footnote_code)
353 OR (lookup_type = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
354 and lookup_code = p_footnote_code))
355 );
356 begin
357
358 hr_utility.trace('chk_rl2_footnote - checking footnote exists');
359 hr_utility.trace('c_chk_footnote - opening cursor');
360
361 open c_chk_footnote;
362 fetch c_chk_footnote into l_flag;
363 if c_chk_footnote%FOUND then
364 hr_utility.trace('c_chk_footnote - found in cursor');
365 l_flag := 'Y';
366 else
367 hr_utility.trace('c_chk_footnote - not found in cursor');
368 l_flag := 'N';
369 end if;
370
371 hr_utility.trace('c_chk_footnote - closing cursor');
372 close c_chk_footnote;
373
374 if l_flag = 'Y' then
375 hr_utility.trace('chk_rl2_footnote - returning true');
376 return (TRUE);
377 else
378 hr_utility.trace('chk_rl2_footnote - returning false');
379 return(FALSE);
380 end if;
381
382 end chk_rl2_footnote;
383
384
385 /*
386 Name : Initialization_process
387 Purpose : This procedure will delete the plsql tables used for
388 archiving the employee and employer data.
389 Arguments :
390 Notes :
391 */
392
393 procedure initialization_process(p_data varchar2)
394 is
395
396 BEGIN
397
398 If p_data = 'EMPLOYEE_DATA' then
399
400 hr_utility.trace('deleting plsql table'|| p_data);
401
402 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count > 0 then
403 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.delete;
404 end if;
405
406 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count > 0 then
407 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.delete;
408 end if;
409
410 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count > 0 then
411 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.delete;
412 end if;
413
414 End if;
415
416 If p_data = 'PRE_DATA' then
417
418 hr_utility.trace('deleting plsql table'|| p_data);
419
420 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.count > 0 then
421 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.delete;
422 end if;
423
424 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count > 0 then
425 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.delete;
426 end if;
427
428 End if;
429
430 END initialization_process;
431
432
433 /*
434 Name : archive_data_records
435 Purpose : This procedure will insert values in to pay_action_information
436 table using the plsql table.
437 Arguments :
438 Notes :
439 */
440
441 procedure archive_data_records(
442 p_action_context_id in number
443 ,p_action_context_type in varchar2
444 ,p_assignment_id in number
445 ,p_tax_unit_id in number
446 ,p_effective_date in date
447 ,p_tab_rec_data in pay_ca_eoy_rl2_archive.action_info_table
448 )
449
450 IS
451 l_action_information_id_1 NUMBER ;
452 l_object_version_number_1 NUMBER ;
453
454 BEGIN
455
456 if p_tab_rec_data.count > 0 then
457 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
458 hr_utility.trace('Defining category '||
459 p_tab_rec_data(i).action_info_category);
460 hr_utility.trace('action_context_id = '|| p_action_context_id);
461 hr_utility.trace('jurisdiction_code '||
462 p_tab_rec_data(i).jurisdiction_code);
463 hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
464
465 hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
466
467 hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
468
469 hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
470
471 hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
472
473 hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
474
475 hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
476
477 pay_action_information_api.create_action_information(
478 p_action_information_id => l_action_information_id_1,
479 p_object_version_number => l_object_version_number_1,
480 p_action_information_category
481 => p_tab_rec_data(i).action_info_category,
482 p_action_context_id => p_action_context_id,
483 p_action_context_type => p_action_context_type,
484 p_jurisdiction_code => p_tab_rec_data(i).jurisdiction_code,
485 p_assignment_id => p_assignment_id,
486 p_tax_unit_id => p_tax_unit_id,
487 p_effective_date => p_effective_date,
488 p_action_information1 => p_tab_rec_data(i).act_info1,
489 p_action_information2 => p_tab_rec_data(i).act_info2,
490 p_action_information3 => p_tab_rec_data(i).act_info3,
491 p_action_information4 => p_tab_rec_data(i).act_info4,
492 p_action_information5 => p_tab_rec_data(i).act_info5,
493 p_action_information6 => p_tab_rec_data(i).act_info6,
494 p_action_information7 => p_tab_rec_data(i).act_info7,
495 p_action_information8 => p_tab_rec_data(i).act_info8,
496 p_action_information9 => p_tab_rec_data(i).act_info9,
497 p_action_information10 => p_tab_rec_data(i).act_info10,
498 p_action_information11 => p_tab_rec_data(i).act_info11,
499 p_action_information12 => p_tab_rec_data(i).act_info12,
500 p_action_information13 => p_tab_rec_data(i).act_info13,
501 p_action_information14 => p_tab_rec_data(i).act_info14,
502 p_action_information15 => p_tab_rec_data(i).act_info15,
503 p_action_information16 => p_tab_rec_data(i).act_info16,
504 p_action_information17 => p_tab_rec_data(i).act_info17,
505 p_action_information18 => p_tab_rec_data(i).act_info18,
506 p_action_information19 => p_tab_rec_data(i).act_info19,
507 p_action_information20 => p_tab_rec_data(i).act_info20,
508 p_action_information21 => p_tab_rec_data(i).act_info21,
509 p_action_information22 => p_tab_rec_data(i).act_info22,
510 p_action_information23 => p_tab_rec_data(i).act_info23,
511 p_action_information24 => p_tab_rec_data(i).act_info24,
512 p_action_information25 => p_tab_rec_data(i).act_info25,
513 p_action_information26 => p_tab_rec_data(i).act_info26,
514 p_action_information27 => p_tab_rec_data(i).act_info27,
515 p_action_information28 => p_tab_rec_data(i).act_info28,
516 p_action_information29 => p_tab_rec_data(i).act_info29,
517 p_action_information30 => p_tab_rec_data(i).act_info30
518 );
519
520 end loop;
521 end if;
522
523 END archive_data_records;
524
525
526 FUNCTION compare_archive_data(p_assignment_action_id in number,
527 p_locked_action_id in number)
528 RETURN VARCHAR2 IS
529
530 TYPE act_info_rec IS RECORD
531 (act_info1 varchar2(240),
532 act_info2 varchar2(240),
533 act_info3 varchar2(240),
534 act_info4 varchar2(240),
535 act_info5 varchar2(240),
536 act_info6 varchar2(240),
537 act_info7 varchar2(240),
538 act_info8 varchar2(240),
539 act_info9 varchar2(240),
540 act_info10 varchar2(240),
541 act_info11 varchar2(240),
542 act_info12 varchar2(240),
543 act_info13 varchar2(240),
544 act_info14 varchar2(240),
545 act_info15 varchar2(240),
546 act_info16 varchar2(240),
547 act_info17 varchar2(240),
548 act_info18 varchar2(240),
549 act_info19 varchar2(240),
550 act_info20 varchar2(240),
551 act_info21 varchar2(240),
552 act_info22 varchar2(240),
553 act_info23 varchar2(240),
554 act_info24 varchar2(240),
555 act_info25 varchar2(240),
556 act_info26 varchar2(240),
557 act_info27 varchar2(240),
558 act_info28 varchar2(240),
559 act_info29 varchar2(240),
560 act_info30 varchar2(240));
561
562 TYPE act_info_ft_rec IS RECORD
563 (message varchar2(240),
564 value varchar2(240));
565
566 TYPE action_info_table IS TABLE OF act_info_rec
567 INDEX BY BINARY_INTEGER;
568
569 TYPE action_info_footnote_table IS TABLE OF act_info_ft_rec
570 INDEX BY BINARY_INTEGER;
571
572 ltr_amend_arch_data action_info_table;
573 ltr_yepp_arch_data action_info_table;
574 ltr_amend_footnote action_info_footnote_table;
575 ltr_yepp_footnote action_info_footnote_table;
576
577 ln_yepp_footnote_count number;
578 ln_amend_footnote_count number;
579
580 cursor c_get_footnotes(cp_asg_act_id number) is
581 select action_information4,
582 action_information5
583 from pay_action_information
584 where action_context_id = cp_asg_act_id
585 and action_information_category = 'CA FOOTNOTES'
586 and action_context_type = 'AAP'
587 and action_information6 = 'RL2'
588 and jurisdiction_code = 'QC'
589 order by action_information4;
590
591 cursor c_get_employee_data(cp_asg_act_id number) is
592 select nvl(action_information1,'NULL'),
593 nvl(action_information2,'NULL'),
594 nvl(action_information3,'NULL'),
595 nvl(action_information4,'NULL'),
596 nvl(action_information5,'NULL'),
597 nvl(action_information6,'NULL'),
598 nvl(action_information7,'NULL'),
599 nvl(action_information8,'NULL'),
600 nvl(action_information9,'NULL'),
601 nvl(action_information10,'NULL'),
602 nvl(action_information11,'NULL'),
603 nvl(action_information12,'NULL'),
604 nvl(action_information13,'NULL'),
605 nvl(action_information14,'NULL'),
606 nvl(action_information15,'NULL'),
607 nvl(action_information16,'NULL'),
608 nvl(action_information17,'NULL'),
609 nvl(action_information18,'NULL'),
610 nvl(action_information19,'NULL'),
611 nvl(action_information20,'NULL'),
612 nvl(action_information21,'NULL'),
613 nvl(action_information22,'NULL'),
614 nvl(action_information23,'NULL'),
615 nvl(action_information24,'NULL'),
616 nvl(action_information25,'NULL'),
617 nvl(action_information26,'NULL'),
618 nvl(action_information27,'NULL'),
619 nvl(action_information28,'NULL'),
620 nvl(action_information29,'NULL'),
621 nvl(action_information30,'NULL')
622 from pay_action_information
623 where action_context_id = cp_asg_act_id
624 and action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
625 and action_context_type = 'AAP'
626 and jurisdiction_code = 'QC';
627
628 i number;
629 lv_flag varchar2(2);
630
631 begin
632
633 /* Initialization Process */
634
635 lv_flag := 'N';
636
637 if ltr_amend_arch_data.count > 0 then
638 ltr_amend_arch_data.delete;
639 end if;
640
641 if ltr_yepp_arch_data.count > 0 then
642 ltr_yepp_arch_data.delete;
643 end if;
644
645 if ltr_amend_footnote.count > 0 then
646 ltr_amend_footnote.delete;
647 end if;
648
649 if ltr_yepp_footnote.count > 0 then
650 ltr_yepp_footnote.delete;
651 end if;
652
653
654 /* Populate RL2 Amendment Employee Data for an assignment_action */
655
656 open c_get_employee_data(p_assignment_action_id);
657
658 hr_utility.trace('Populating RL2 Amendment Employee Data ');
659 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
660
661 fetch c_get_employee_data into ltr_amend_arch_data(0);
662 close c_get_employee_data;
663
664
665 /* Populate RL2 YEPP Employee Data for an assignment_action */
666
667 open c_get_employee_data(p_locked_action_id);
668
669 hr_utility.trace('Populating RL2 YEPP Employee Data ');
670 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
671
672 fetch c_get_employee_data into ltr_yepp_arch_data(0);
673 close c_get_employee_data;
674
675
676 /* Populate RL2 Amendment Footnotes */
677 open c_get_footnotes(p_assignment_action_id);
678
679 hr_utility.trace('Populating RL2 Amendment Footnote ');
680
681 ln_amend_footnote_count := 0;
682 loop
683 fetch c_get_footnotes into ltr_amend_footnote(ln_amend_footnote_count);
684 exit when c_get_footnotes%NOTFOUND;
685
686 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
687 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
688
689 ln_amend_footnote_count := ln_amend_footnote_count + 1;
690 end loop;
691
692 close c_get_footnotes;
693
694 /* Populate RL2 YEPP Footnotes */
695 open c_get_footnotes(p_locked_action_id);
696
697 ln_yepp_footnote_count := 0;
698 loop
699 fetch c_get_footnotes into ltr_yepp_footnote(ln_yepp_footnote_count);
700 exit when c_get_footnotes%NOTFOUND;
701
702 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
703 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
704
705 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
706 end loop;
707
708 close c_get_footnotes;
709
710 hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Data ');
711
712 if (ltr_yepp_arch_data.count = ltr_amend_arch_data.count) then
713
714 if (ltr_yepp_arch_data.count <> 0) then
715
716 if ((ltr_yepp_arch_data(0).act_info2 <> ltr_amend_arch_data(0).act_info2) or
717 (ltr_yepp_arch_data(0).act_info3 <> ltr_amend_arch_data(0).act_info3) or
718 (ltr_yepp_arch_data(0).act_info4 <> ltr_amend_arch_data(0).act_info4) or
719 (ltr_yepp_arch_data(0).act_info5 <> ltr_amend_arch_data(0).act_info5) or
720 (ltr_yepp_arch_data(0).act_info6 <> ltr_amend_arch_data(0).act_info6) or
721 (ltr_yepp_arch_data(0).act_info7 <> ltr_amend_arch_data(0).act_info7) or
722 (ltr_yepp_arch_data(0).act_info8 <> ltr_amend_arch_data(0).act_info8) or
723 (ltr_yepp_arch_data(0).act_info9 <> ltr_amend_arch_data(0).act_info9) or
724 (ltr_yepp_arch_data(0).act_info10 <> ltr_amend_arch_data(0).act_info10) or
725 (ltr_yepp_arch_data(0).act_info11 <> ltr_amend_arch_data(0).act_info11) or
726 (ltr_yepp_arch_data(0).act_info12 <> ltr_amend_arch_data(0).act_info12) or
727 (ltr_yepp_arch_data(0).act_info13 <> ltr_amend_arch_data(0).act_info13) or
728 (ltr_yepp_arch_data(0).act_info14 <> ltr_amend_arch_data(0).act_info14) or
729 (ltr_yepp_arch_data(0).act_info15 <> ltr_amend_arch_data(0).act_info15) or
730 (ltr_yepp_arch_data(0).act_info16 <> ltr_amend_arch_data(0).act_info16) or
731 (ltr_yepp_arch_data(0).act_info17 <> ltr_amend_arch_data(0).act_info17) or
732 (ltr_yepp_arch_data(0).act_info18 <> ltr_amend_arch_data(0).act_info18) or
733 (ltr_yepp_arch_data(0).act_info19 <> ltr_amend_arch_data(0).act_info19) or
734 (ltr_yepp_arch_data(0).act_info20 <> ltr_amend_arch_data(0).act_info20) or
735 (ltr_yepp_arch_data(0).act_info21 <> ltr_amend_arch_data(0).act_info21) or
736 (ltr_yepp_arch_data(0).act_info22 <> ltr_amend_arch_data(0).act_info22) or
737 (ltr_yepp_arch_data(0).act_info23 <> ltr_amend_arch_data(0).act_info23) or
738 (ltr_yepp_arch_data(0).act_info24 <> ltr_amend_arch_data(0).act_info24) or
739 (ltr_yepp_arch_data(0).act_info25 <> ltr_amend_arch_data(0).act_info25) or
740 (ltr_yepp_arch_data(0).act_info26 <> ltr_amend_arch_data(0).act_info26) or
741 (ltr_yepp_arch_data(0).act_info27 <> ltr_amend_arch_data(0).act_info27) or
742 (ltr_yepp_arch_data(0).act_info28 <> ltr_amend_arch_data(0).act_info28) or
743 (ltr_yepp_arch_data(0).act_info29 <> ltr_amend_arch_data(0).act_info29)) then
744
745 lv_flag := 'Y';
746 hr_utility.trace('lv_flag has been set to Y for Employee Data');
747 end if;
748
749 end if;
750
751 else
752 lv_flag := 'Y';
753 hr_utility.trace('lv_flag has been set to Y for Employee Data');
754 end if;
755
756
757 /* Compare RL2 Amendment Footnotes and RL2 YEPP Footnotes for an
758 assignment_action */
759
760 hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Footnotes');
761
762 if lv_flag <> 'Y' then
763
764 if ln_yepp_footnote_count <> ln_amend_footnote_count then
765
766 lv_flag := 'Y';
767
768 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
769 (ln_yepp_footnote_count <> 0)) then
770
771 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
772 loop
773 if (ltr_yepp_footnote(i).message = ltr_amend_footnote(i).message) then
774
775 if ((ltr_yepp_footnote(i).value <>
776 ltr_amend_footnote(i).value) or
777 (ltr_yepp_footnote(i).value is null and
778 ltr_amend_footnote(i).value is not null) or
779 (ltr_yepp_footnote(i).value is not null and
780 ltr_amend_footnote(i).value is null)) then
781
782 lv_flag := 'Y';
783 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
784 exit;
785 end if;
786 end if;
787 end loop;
788 end if;
789
790 end if;
791
792 /* If there is no value difference for Entire Employee data then set
793 flag to 'N' */
794
795 if lv_flag <> 'Y' then
796
797 lv_flag := 'N';
798 hr_utility.trace('No value difference for Asg Action: '|| to_char(p_assignment_action_id));
799
800 end if;
801
802 hr_utility.trace('lv_flag :'||lv_flag);
803
804 return lv_flag;
805
806 end compare_archive_data;
807
808
809 /*
810 Name : eoy_action_creation
811 Purpose : This creates the assignment actions for a specific chunk
812 of people to be archived by the RL2 Archiver preprocess.
813 Arguments :
814 Notes :
815 */
816
817 procedure eoy_action_creation(pactid in number,
818 stperson in number,
819 endperson in number,
820 chunk in number) is
821
822
823
824 /* Variables used to hold the select columns from the SQL statement.*/
825
826 l_person_id number;
827 l_assignment_id number;
828 l_tax_unit_id number;
829 l_eoy_tax_unit_id number;
830 l_effective_end_date date;
831 l_object_version_number number;
832 l_some_warning boolean;
833 l_counter number;
834 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
835 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
836 l_user_entity_name varchar2(240);
837
838 /* Variables used to hold the values used as bind variables within the
839 SQL statement. */
840
841 l_bus_group_id number;
842 l_period_start date;
843 l_period_end date;
844
845 /* Variables used to hold the details of the payroll and assignment actions
846 that are created. */
847
848 l_payroll_action_created boolean := false;
849 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
850 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
851 l_archive_item_id number;
852
853 /* Variable holding the balance to be tested. */
854
855 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
856
857 /* Indicator variables used to control how the people are grouped. */
858
859 l_group_by_gre boolean := FALSE;
860 l_group_by_medicare boolean := FALSE;
861
862 /* Indicator variables used to control which contexts are set up for
863 balance. */
864
865 l_tax_unit_context boolean := FALSE;
866 l_jurisdiction_context boolean := FALSE;
867
868 /* Variables used to hold the current values returned within the loop for
869 checking against the new values returned from within the loop on the
870 next iteration. */
871
872 l_prev_person_id per_all_people_f.person_id%type;
873 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
874
875 /* Variable to hold the jurisdiction code used as a context for state
876 reporting. */
877
878 l_jurisdiction_code varchar2(30);
879
880 /* general process variables */
881
882 l_report_type pay_payroll_actions.report_type%type;
883 l_province pay_payroll_actions.report_qualifier%type;
884 l_value number;
885 l_effective_date date;
886 l_quarter_start date;
887 l_quarter_end date;
888 l_year_start date;
889 l_year_end date;
890 lockingactid number;
891 l_max_aaid number;
892 l_pre_org_id varchar2(17);
893 l_prev_pre_org_id varchar2(17);
894 l_primary_asg pay_assignment_actions.assignment_id%type;
895 ln_no_gross_earnings number;
896
897
898 /* For Year End Preprocess we have to archive the assignments
899 belonging to a GRE */
900
901 /* For Year End Preprocess we can also archive the assignments
902 belonging to all GREs */
903 /*
904 CURSOR c_eoy_qbin IS
905 SELECT ASG.person_id person_id,
906 ASG.assignment_id assignment_id,
907 ASG.effective_end_date effective_end_date
908 FROM per_all_assignments_f ASG,
909 pay_all_payrolls_f PPY,
910 hr_soft_coding_keyflex SCL
911 WHERE ASG.business_group_id + 0 = l_bus_group_id
912 AND ASG.person_id between stperson and endperson
913 AND ASG.assignment_type = 'E'
914 AND ASG.effective_start_date <= l_period_end
915 AND ASG.effective_end_date >= l_period_start
916 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
917 AND rtrim(ltrim(SCL.segment12)) in
918 (select to_char(hoi.organization_id)
919 from hr_organization_information hoi
920 where hoi.org_information_context = 'Canada Employer Identification'
921 and hoi.org_information2 = l_pre_org_id
922 and hoi.org_information5 = 'T4A/RL2')
923 AND PPY.payroll_id = ASG.payroll_id
924 and exists ( select 'X' from pay_action_contexts pac, ff_contexts fc
925 where pac.assignment_id = asg.assignment_id
926 and pac.context_id = fc.context_id
927 and fc.context_name = 'JURISDICTION_CODE'
928 and pac.context_value = 'QC' )
929 ORDER BY 1, 3 DESC, 2; */
930
931 /*
932 Bug 5202869. For performance issue modified the cursor c_eoy_qbin.
933 Removed the table per_people_f and also disabled few indexes to make
934 sure the query takes the correct path. With this change the cost of
935 the query has increased but the path taken is better.
936 */
937
938 CURSOR c_eoy_qbin IS
939 SELECT asg.person_id person_id,
940 asg.assignment_id assignment_id,
941 asg.effective_end_date effective_end_date
942 FROM per_all_assignments_f asg,
943 pay_assignment_actions paa,
944 pay_payroll_actions ppa
945 WHERE ppa.effective_date between l_period_start
946 and l_period_end
947 AND ppa.action_type in ('R','Q','V','B','I')
948 AND ppa.business_group_id +0 = l_bus_group_id
949 AND ppa.payroll_action_id = paa.payroll_action_id
950 AND paa.tax_unit_id in (select hoi.organization_id
951 from hr_organization_information hoi
952 where hoi.org_information_context ||''= 'Canada Employer Identification'
953 and hoi.org_information2 = l_pre_org_id
954 and hoi.org_information5 = 'T4A/RL2')
955 AND paa.assignment_id = asg.assignment_id
956 AND ppa.business_group_id = asg.business_group_id +0
957 AND asg.person_id between stperson and endperson
958 AND asg.assignment_type = 'E'
959 AND ppa.effective_date between asg.effective_start_date
960 and asg.effective_end_date
961 AND EXISTS (select 1
962 from pay_action_contexts pac,
963 ff_contexts fc
964 where pac.assignment_id = paa.assignment_id
965 and pac.assignment_action_id = paa.assignment_action_id
966 and pac.context_id = fc.context_id
967 and fc.context_name || '' = 'JURISDICTION_CODE'
968 and pac.context_value ||'' = 'QC')
969 ORDER BY 1, 3 DESC, 2;
970
971 cursor c_all_qbin_gres is
972 select hoi.organization_id
973 from hr_organization_information hoi
974 where hoi.org_information_context = 'Canada Employer Identification'
975 and hoi.org_information2 = l_pre_org_id
976 and hoi.org_information5 = 'T4A/RL2';
977
978 /* Get the assignment for the given person_id */
979
980 CURSOR c_get_asg_id (p_person_id number) IS
981 SELECT assignment_id
982 from per_all_assignments_f paf
983 where person_id = p_person_id
984 and assignment_type = 'E'
985 and primary_flag = 'Y'
986 and paf.effective_start_date <= l_period_end
987 and paf.effective_end_date >= l_period_start
988 ORDER BY assignment_id desc;
989
990 /* Cursor to get the latest payroll run assignment_action_id
991 for a person with a given tax_unit_id and for that year.
992 11510 bug# fix. Changed the cursor to get asgact_id based on
993 person_id to fix bug#3638928 */
994
995 CURSOR c_get_asg_act_id(cp_person_id number,
996 cp_tax_unit_id number,
997 cp_period_start date,
998 cp_period_end date) IS
999 select paa.assignment_action_id
1000 from pay_assignment_actions paa,
1001 per_all_assignments_f paf,
1002 per_all_people_f ppf,
1003 pay_payroll_actions ppa,
1004 pay_action_classifications pac
1005 where ppf.person_id = cp_person_id
1006 and paf.person_id = ppf.person_id
1007 and paa.assignment_id = paf.assignment_id
1008 and paa.tax_unit_id = cp_tax_unit_id
1009 and ppa.payroll_action_id = paa.payroll_action_id
1010 and ppa.effective_date between cp_period_start and cp_period_end
1011 and ppa.effective_date between ppf.effective_start_date
1012 and ppf.effective_end_date
1013 and ppa.effective_date between paf.effective_start_date
1014 and paf.effective_end_date
1015 and ppa.action_type = pac.action_type
1016 and pac.classification_name = 'SEQUENCED'
1017 order by paa.action_sequence desc;
1018
1019
1020 begin
1021
1022 /* Get the report type, report qualifier, business group id and the
1023 gre for which the archiving has to be done */
1024
1025 hr_utility.trace('getting report type ');
1026
1027 select effective_date,
1028 report_type,
1029 business_group_id,
1030 pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
1031 legislative_parameters)
1032 into l_effective_date,
1033 l_report_type,
1034 l_bus_group_id,
1035 l_pre_org_id
1036 from pay_payroll_actions
1037 where payroll_action_id = pactid;
1038
1039 hr_utility.trace('getting dates');
1040
1041 get_dates(l_report_type,
1042 l_effective_date,
1043 l_period_end,
1044 l_quarter_start,
1045 l_quarter_end,
1046 l_year_start,
1047 l_year_end);
1048
1049 hr_utility.trace('getting selection information');
1050 hr_utility.trace('report type '|| l_report_type);
1051 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1052 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1053 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1054 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1055
1056 get_selection_information
1057 (l_report_type,
1058 l_quarter_start,
1059 l_quarter_end,
1060 l_year_start,
1061 l_year_end,
1062 l_period_start,
1063 l_period_end,
1064 l_defined_balance_id,
1065 l_group_by_gre,
1066 l_group_by_medicare,
1067 l_tax_unit_context,
1068 l_jurisdiction_context);
1069
1070 hr_utility.trace('Out of get selection information');
1071 open c_eoy_qbin;
1072
1073 /* Loop for all rows returned for SQL statement. */
1074
1075 hr_utility.trace('Entering loop');
1076
1077 loop
1078
1079 fetch c_eoy_qbin into l_person_id,
1080 l_assignment_id,
1081 l_effective_end_date;
1082
1083 exit when c_eoy_qbin%NOTFOUND;
1084
1085
1086 /* If the new row is the same as the previous row according to the way
1087 the rows are grouped then discard the row ie. grouping by Prov Reporting
1088 Est requires a single row for each person / PRE combination. */
1089
1090 hr_utility.trace('Prov Reporting Est is '
1091 || l_pre_org_id);
1092 hr_utility.trace('previous Prov Reporting Est is '||
1093 l_prev_pre_org_id);
1094 hr_utility.trace('person_id is '||
1095 to_char(l_person_id));
1096 hr_utility.trace('previous person_id is '||
1097 to_char(l_prev_person_id));
1098
1099 if (l_person_id = l_prev_person_id and
1100 l_pre_org_id = l_prev_pre_org_id) then
1101
1102 hr_utility.trace('Not creating assignment action');
1103
1104 else
1105 /* Check whether the person has 0 payment or not */
1106
1107 l_value := 0;
1108 ln_no_gross_earnings := 0;
1109
1110 open c_all_qbin_gres;
1111 loop
1112 fetch c_all_qbin_gres into l_tax_unit_id;
1113 exit when c_all_qbin_gres%NOTFOUND;
1114
1115 /* select the maximum assignment action id. Fix for bug#3638928 */
1116
1117 begin
1118
1119 open c_get_asg_act_id(l_person_id,l_tax_unit_id,
1120 l_period_start,l_period_end);
1121 fetch c_get_asg_act_id into l_max_aaid;
1122
1123 if c_get_asg_act_id%NOTFOUND then
1124 pay_core_utils.push_message(801,
1125 'PAY_74038_EOY_EXCP_NO_PAYROLL','A');
1126 pay_core_utils.push_token('person','Person id: '
1127 ||to_char(l_person_id));
1128 pay_core_utils.push_token('reporting_year','Reporting Year: '
1129 ||to_char(l_effective_date,'YYYY'));
1130
1131 l_max_aaid := -9999;
1132 end if;
1133 close c_get_asg_act_id;
1134 end; /* end for select of max assignment action id */
1135
1136 if l_max_aaid <> -9999 then
1137 l_value := l_value +
1138 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1139 ('Gross Earnings',
1140 'YTD' ,
1141 l_max_aaid,
1142 l_assignment_id ,
1143 NULL,
1144 'PER' ,
1145 l_tax_unit_id,
1146 l_bus_group_id,
1147 'QC'
1148 ),0) ;
1149
1150 ln_no_gross_earnings := ln_no_gross_earnings +
1151 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1152 ('RL2 No Gross Earnings',
1153 'YTD' ,
1154 l_max_aaid,
1155 l_assignment_id ,
1156 NULL,
1157 'PER' ,
1158 l_tax_unit_id,
1159 l_bus_group_id,
1160 'QC'
1161 ),0) ;
1162 end if; /* end l_max_id <> -9999 */
1163 end loop;
1164 close c_all_qbin_gres;
1165 /* end of checking whether the person has 0 payment */
1166
1167 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1168 hr_utility.trace('person is '|| to_char(l_person_id));
1169 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1170
1171
1172 /* Have a new unique row according to the way the rows are grouped.
1173 The inclusion of the person is dependent on having a non zero
1174 balance.
1175 If the balance is non zero then an assignment action is created to
1176 indicate their inclusion in the magnetic tape report. */
1177
1178 /* Set up the context of tax unit id */
1179
1180 hr_utility.trace('Setting context');
1181
1182 if ((l_value <> 0) or (ln_no_gross_earnings <> 0)) then
1183 /* Get the primary assignment */
1184 open c_get_asg_id(l_person_id);
1185 fetch c_get_asg_id into l_primary_asg;
1186
1187 if c_get_asg_id%NOTFOUND then
1188 close c_get_asg_id;
1189 pay_core_utils.push_message(800,'HR_74004_ASSIGNMENT_ABSENT','A');
1190 raise hr_utility.hr_error;
1191 else
1192 close c_get_asg_id;
1193 end if;
1194
1195 /* Create the assignment action to represnt the person / tax unit
1196 combination. */
1197
1198 select pay_assignment_actions_s.nextval
1199 into lockingactid
1200 from dual;
1201
1202 /* Insert into pay_assignment_actions. */
1203
1204 hr_utility.trace('creating assignment_action');
1205
1206 /* Passing tax unit id as null */
1207
1208 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1209 pactid,chunk,null);
1210
1211 /* Update the serial number column with the person id
1212 so that the mag routine and the RL2 view will not have
1213 to do an additional checking against the assignment
1214 table
1215 */
1216
1217 hr_utility.trace('updating assignment_action' || to_char(lockingactid));
1218
1219 update pay_assignment_actions aa
1220 set aa.serial_number = to_char(l_person_id)
1221 where aa.assignment_action_id = lockingactid;
1222
1223 end if; /* end if l_value <> 0 or ln_no_gross_earnings <> 0 */
1224
1225 end if; /* end if l_person_id = l_prev_person_id */
1226
1227 /* Record the current values for the next time around the loop. */
1228
1229 l_prev_person_id := l_person_id;
1230 l_prev_pre_org_id := l_pre_org_id;
1231
1232 end loop;
1233
1234 hr_utility.trace('Action creation done');
1235 close c_eoy_qbin;
1236
1237 end eoy_action_creation;
1238
1239
1240 /* Name : eoy_archive_gre_data
1241 Purpose : This performs the CA specific employer data archiving.
1242 Arguments :
1243 Notes :
1244 */
1245
1246 PROCEDURE eoy_archive_gre_data(p_payroll_action_id in number,
1247 p_pre_org_id in varchar2)
1248 IS
1249
1250 l_user_entity_id number;
1251 l_taxunit_context_id number;
1252 l_jursd_context_id number;
1253 l_value varchar2(240);
1254 l_sit_uid number;
1255 l_sui_uid number;
1256 l_fips_uid number;
1257 l_seq_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1258 l_context_id_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1259 l_context_val_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1260 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1261 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1262 l_arch_gre_step number := 0;
1263 l_archive_item_id number;
1264 l_town_or_city varchar2(240);
1265 l_province_code varchar2(240);
1266 l_postal_code varchar2(240);
1267 l_organization_id_of_qin number;
1268 l_transmitter_org_id number;
1269 l_country_code varchar2(240);
1270 l_transmitter_name varchar2(240);
1271 l_Transmitter_Type_Indicator varchar2(240);
1272 l_transmitter_gre_ind varchar2(240);
1273 l_Transmitter_number varchar2(240);
1274 l_transmitter_addr_line_1 varchar2(240);
1275 l_transmitter_addr_line_2 varchar2(240);
1276 l_transmitter_addr_line_3 varchar2(240);
1277 l_transmitter_city varchar2(240);
1278 l_transmitter_province varchar2(240);
1279 l_transmitter_postal_code varchar2(240);
1280 l_transmitter_country varchar2(240);
1281 l_rl_data_type varchar2(240);
1282 l_rl_package_type varchar2(240);
1283 l_rl_source_of_slips varchar2(240);
1284 l_technical_contact_name varchar2(240);
1285 l_technical_contact_phone varchar2(240);
1286 l_technical_contact_area_code varchar2(240);
1287 l_technical_contact_extension varchar2(240);
1288 l_technical_contact_language varchar2(240);
1289 l_accounting_contact_name varchar2(240);
1290 l_accounting_contact_phone varchar2(240);
1291 l_accounting_contact_area_code varchar2(240);
1292 l_accounting_contact_extension varchar2(240);
1293 l_accounting_contact_language varchar2(240);
1294 l_proprietor_sin varchar2(240);
1295 l_name varchar2(240);
1296 l_org_name varchar2(240);
1297 l_employer_ein varchar2(240);
1298 l_address_line_1 varchar2(240);
1299 l_address_line_2 varchar2(240);
1300 l_address_line_3 varchar2(240);
1301 l_counter number := 0;
1302 l_object_version_number number;
1303 l_business_group_id number;
1304 l_some_warning boolean;
1305 l_step number := 0;
1306 l_taxation_year varchar2(4);
1307 l_rl2_last_slip_number number ;
1308 l_employer_info_found varchar2(1);
1309 l_max_slip_number varchar2(80);
1310 l_effective_date date;
1311
1312 ln_index number;
1313 ln_index2 number;
1314
1315 l_action_information_id_1 NUMBER ;
1316 l_object_version_number_1 NUMBER ;
1317
1318 cursor employer_info is
1319 select target1.organization_id,
1320 target2.name,
1321 target2.business_group_id,
1322 target1.ORG_INFORMATION2 Prov_Identi_Number,
1323 target1.ORG_INFORMATION7 Type_of_Transmitter,
1324 target1.ORG_INFORMATION5 Transmitter_Number,
1325 target1.ORG_INFORMATION4 Type_of_Data,
1326 target1.ORG_INFORMATION6 Type_of_Package,
1327 target1.ORG_INFORMATION8 Source_of_RL_slips_used,
1328 target1.ORG_INFORMATION9 Tech_Res_Person_Name,
1329 target1.ORG_INFORMATION11 Tech_Res_Phone,
1330 target1.ORG_INFORMATION10 Tech_Res_Area_Code,
1331 target1.ORG_INFORMATION12 Tech_Res_Extension,
1332 decode(target1.ORG_INFORMATION13,'E','A',
1333 target1.ORG_INFORMATION13) Tech_Res_Language,
1334 target1.ORG_INFORMATION14 Acct_Res_Person_Name,
1335 target1.ORG_INFORMATION16 Acct_Res_Phone,
1336 target1.ORG_INFORMATION15 Acct_Res_Area_Code,
1337 target1.ORG_INFORMATION17 Acct_Res_Extension,
1338 decode(target1.ORG_INFORMATION19,'E','A',
1339 target1.ORG_INFORMATION19) Acct_Res_Language,
1340 substr(target1.ORG_INFORMATION18,1,8) RL2_Slip_Number,
1341 decode(target1.org_information3,'Y',target1.organization_id,
1342 to_number(target1.ORG_INFORMATION20)),
1343 target1.ORG_INFORMATION3
1344 from hr_organization_information target1,
1345 hr_all_organization_units target2
1346 where target1.organization_id = to_number(p_pre_org_id)
1347 and target2.business_group_id = l_business_group_id
1348 and target2.organization_id = target1.organization_id
1349 and target1.org_information_context = 'Prov Reporting Est'
1350 and target1.org_information4 = 'P02';
1351
1352 /* payroll action level database items */
1353
1354 BEGIN
1355
1356 /* hr_utility.trace_on('Y','RL2'); */
1357
1358 initialization_process('PRE_DATA');
1359
1360 select to_char(effective_date,'YYYY'),business_group_id,effective_date
1361 into l_taxation_year,l_business_group_id,l_effective_date
1362 from pay_payroll_actions
1363 where payroll_action_id = p_payroll_action_id;
1364
1365 open employer_info;
1366
1367 fetch employer_info
1368 into l_organization_id_of_qin,
1369 l_name, l_business_group_id,
1370 l_employer_ein,
1371 l_Transmitter_Type_Indicator, l_transmitter_number,
1372 l_rl_data_type, l_rl_package_type,
1373 l_rl_source_of_slips,
1374 l_technical_contact_name, l_technical_contact_phone,
1375 l_technical_contact_area_code, l_technical_contact_extension,
1376 l_technical_contact_language, l_accounting_contact_name,
1377 l_accounting_contact_phone ,
1378 l_accounting_contact_area_code ,
1379 l_accounting_contact_extension ,
1380 l_accounting_contact_language,
1381 l_rl2_last_slip_number,
1382 l_transmitter_org_id,
1383 l_transmitter_gre_ind;
1384
1385 l_arch_gre_step := 40;
1386 hr_utility.trace('eoy_archive_gre_data 1');
1387
1388 if employer_info%FOUND then
1389
1390 close employer_info;
1391 hr_utility.trace('got employer data ');
1392
1393 l_employer_info_found := 'Y';
1394
1395 begin
1396 select
1397 L.ADDRESS_LINE_1
1398 , L.ADDRESS_LINE_2
1399 , L.ADDRESS_LINE_3
1400 , L.TOWN_OR_CITY
1401 , DECODE(L.STYLE ,'US',L.REGION_2,'CA',L.REGION_1,'CA_GLB',L.REGION_1,' ')
1402 , replace(L.POSTAL_CODE,' ')
1403 , L.COUNTRY
1404 , O.name
1405 into
1406 l_address_line_1
1407 , l_address_line_2
1408 , l_address_line_3
1409 , l_town_or_city
1410 , l_province_code
1411 , l_postal_code
1412 , l_country_code
1413 , l_org_name
1414 from hr_all_organization_units O,
1415 hr_locations_all L
1416 where L.LOCATION_ID = O.LOCATION_ID
1417 AND O.ORGANIZATION_ID = l_organization_id_of_qin;
1418
1419 /* Find out the highest slip number for that transmitter */
1420
1421 if l_transmitter_gre_ind = 'Y' then
1422
1423 l_transmitter_org_id := l_organization_id_of_qin;
1424
1425 l_transmitter_name := l_org_name;
1426 l_transmitter_addr_line_1 := l_address_line_1;
1427 l_transmitter_addr_line_2 := l_address_line_2;
1428 l_transmitter_addr_line_3 := l_address_line_3;
1429 l_transmitter_city := l_town_or_city;
1430 l_transmitter_province := l_province_code;
1431 l_transmitter_postal_code := l_postal_code;
1432 l_transmitter_country := l_country_code;
1433
1434 end if;
1435
1436 exception when no_data_found then
1437 l_transmitter_name := NULL;
1438 l_address_line_1 := NULL;
1439 l_address_line_2 := NULL;
1440 l_address_line_3 := NULL;
1441 l_town_or_city := NULL;
1442 l_province_code := NULL;
1443 l_postal_code := NULL;
1444 l_country_code := NULL;
1445 end;
1446
1447 else
1448 l_employer_ein := 'TEST_DATA';
1449 l_address_line_1 := 'TEST_DATA';
1450 l_address_line_2 := 'TEST_DATA';
1451 l_address_line_3 := 'TEST_DATA';
1452 l_town_or_city := 'TEST_DATA';
1453 l_province_code := 'TEST_DATA';
1454 l_postal_code := 'TEST_DATA';
1455 l_country_code := 'TEST_DATA';
1456 l_name := 'TEST_DATA';
1457 l_transmitter_name := 'TEST_DATA';
1458 l_transmitter_addr_line_1 := 'TEST_DATA';
1459 l_transmitter_addr_line_2 := 'TEST_DATA';
1460 l_transmitter_addr_line_3 := 'TEST_DATA';
1461 l_transmitter_city := 'TEST_DATA';
1462 l_transmitter_province := 'TEST_DATA';
1463 l_transmitter_postal_code := 'TEST_DATA';
1464 l_transmitter_country := 'TEST_DATA';
1465 l_technical_contact_name := 'TEST_DATA';
1466 l_technical_contact_phone := 'TEST_DATA';
1467 l_technical_contact_language := 'TEST_DATA';
1468 l_accounting_contact_name := 'TEST_DATA';
1469 l_accounting_contact_phone := 'TEST_DATA';
1470 l_accounting_contact_language:= 'TEST_DATA';
1471 l_proprietor_sin := 'TEST_DATA';
1472 l_arch_gre_step := 424;
1473
1474 hr_utility.trace('eoy_archive_gre_data 2');
1475 close employer_info;
1476
1477 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1478 pay_core_utils.push_token('orgind','Prov Reporting Est: '
1479 ||p_pre_org_id);
1480 hr_utility.raise_error;
1481 end if; /* end if for employer_info%FOUND */
1482
1483
1484 /* archive Releve 2 data */
1485
1486 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.count;
1487 ln_index2 := pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count;
1488
1489 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).action_info_category
1490 := 'CAEOY TRANSMITTER INFO';
1491
1492 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).jurisdiction_code
1493 := null;
1494
1495 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info1
1496 := 'RL2';
1497
1498 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info2
1499 := l_employer_ein;
1500
1501 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info3
1502 := l_transmitter_number;
1503
1504 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info4
1505 := l_rl_data_type;
1506
1507 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info5
1508 := l_rl_package_type;
1509
1510 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info6
1511 := l_Transmitter_Type_Indicator;
1512
1513 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info7
1514 := l_rl_source_of_slips;
1515
1516 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info8
1517 := l_taxation_year;
1518
1519 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info9
1520 := l_transmitter_country;
1521
1522 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info10
1523 := l_transmitter_name;
1524
1525 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info11
1526 := l_transmitter_addr_line_1;
1527
1528 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info12
1529 := l_transmitter_addr_line_2;
1530
1531 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info13
1532 := l_transmitter_addr_line_3;
1533
1534 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info14
1535 := l_transmitter_city;
1536
1537 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info15
1538 := l_transmitter_province;
1539
1540 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info16
1541 := l_transmitter_postal_code;
1542
1543 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info17
1544 := l_technical_contact_name;
1545
1546 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info18
1547 := l_technical_contact_area_code;
1548
1549 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info19
1550 := l_technical_contact_phone;
1551
1552 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info20
1553 := l_technical_contact_extension;
1554
1555 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info21
1556 := l_technical_contact_language;
1557
1558 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info22
1559 := l_accounting_contact_name;
1560
1561 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info23
1562 := l_accounting_contact_area_code;
1563
1564 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info24
1565 := l_accounting_contact_phone ;
1566
1567 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info25
1568 := l_accounting_contact_extension;
1569
1570 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info26
1571 := l_accounting_contact_language;
1572
1573 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info27
1574 := p_pre_org_id;
1575
1576 /* Archive Employer Data */
1577 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).action_info_category
1578 := 'CAEOY EMPLOYER INFO';
1579
1580 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).jurisdiction_code
1581 := null;
1582
1583 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info1
1584 := 'RL2';
1585
1586 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info2
1587 := l_name;
1588
1589 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info3
1590 := l_address_line_1;
1591
1592 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info4
1593 := l_address_line_2;
1594
1595 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info5
1596 := l_address_line_3;
1597
1598 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info6
1599 := l_town_or_city;
1600
1601 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info7
1602 := l_province_code;
1603
1604 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info8
1605 := l_country_code;
1606
1607 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info9
1608 := l_postal_code;
1609
1610 l_arch_gre_step := 50;
1611 l_arch_gre_step := 51;
1612
1613 /* Other employer level data for RL-2 total is to be discussed ,
1614 whether it is for Quebec only or not */
1615
1616 g_archive_flag := 'Y';
1617
1618 /* Inserting rows into pay_action_information table
1619 Transmitter PRE Information */
1620
1621 if ltr_ppa_arch_data.count >0 then
1622 hr_utility.trace('Archiving PRE Data');
1623 archive_data_records(
1624 p_action_context_id => p_payroll_action_id
1625 ,p_action_context_type=> 'PA'
1626 ,p_assignment_id => null
1627 ,p_tax_unit_id => null
1628 ,p_effective_date => l_effective_date
1629 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_data);
1630 end if;
1631
1632 /* Inserting rows into pay_action_information table
1633 Employer Information (Could be just a PRE or Transmitter PRE) */
1634
1635 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count >0 then
1636 hr_utility.trace('Archiving Employer Data');
1637 archive_data_records(
1638 p_action_context_id => p_payroll_action_id
1639 ,p_action_context_type=> 'PA'
1640 ,p_assignment_id => null
1641 ,p_tax_unit_id => null
1642 ,p_effective_date => l_effective_date
1643 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data);
1644 end if;
1645
1646 EXCEPTION
1647 when others then
1648 g_archive_flag := 'N';
1649 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1650 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1651 if l_arch_gre_step = 40 then
1652 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1653 pay_core_utils.push_token('orgind','Prov Reporting Est: '
1654 ||p_pre_org_id);
1655 end if;
1656 hr_utility.raise_error;
1657
1658 END eoy_archive_gre_data;
1659
1660 /* Name : chk_gre_archive
1661 Purpose : Function to check if the employer level data has been archived
1662 or not.
1663 Arguments :
1664 Notes :
1665 */
1666
1667 function chk_gre_archive (p_payroll_action_id number) return boolean is
1668
1669 l_flag varchar2(1);
1670
1671 cursor c_chk_payroll_action is
1672 select 'Y'
1673 from pay_action_information
1674 where action_information1 = 'RL2'
1675 and action_context_id = p_payroll_action_id;
1676
1677 begin
1678
1679 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1680
1681 if g_archive_flag = 'Y' then
1682 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1683 return (TRUE);
1684 else
1685
1686 hr_utility.trace('chk_gre_archive - opening cursor');
1687
1688 open c_chk_payroll_action;
1689 fetch c_chk_payroll_action into l_flag;
1690 if c_chk_payroll_action%FOUND then
1691 hr_utility.trace('chk_gre_archive - found in cursor');
1692 g_archive_flag := 'Y';
1693 else
1694 hr_utility.trace('chk_gre_archive - not found in cursor');
1695 g_archive_flag := 'N';
1696 end if;
1697
1698 hr_utility.trace('chk_gre_archive - closing cursor');
1699 close c_chk_payroll_action;
1700 if g_archive_flag = 'Y' then
1701 hr_utility.trace('chk_gre_archive - returning true');
1702 return (TRUE);
1703 else
1704 hr_utility.trace('chk_gre_archive - returning false');
1705 return(FALSE);
1706 end if;
1707 end if;
1708 end chk_gre_archive;
1709
1710 /* Name : eoy_archinit
1711 Purpose : This performs the context initialization for the year end
1712 pre-process.
1713 Arguments :
1714 Notes :
1715 */
1716
1717
1718 procedure eoy_archinit(p_payroll_action_id in number) is
1719 l_jurisdiction_code VARCHAR2(30);
1720 l_tax_unit_id NUMBER(15);
1721 l_archive boolean:= FALSE;
1722 l_step number := 0;
1723
1724 cursor c_get_min_chunk is
1725 select min(paa.chunk_number)
1726 from pay_assignment_actions paa
1727 where paa.payroll_action_id = p_payroll_action_id;
1728 begin
1729 open c_get_min_chunk;
1730 fetch c_get_min_chunk into g_min_chunk;
1731 l_step := 1;
1732 if c_get_min_chunk%NOTFOUND then
1733 g_min_chunk := -1;
1734 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1735 end if;
1736 close c_get_min_chunk;
1737
1738 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1739 l_step := 2;
1740 l_archive := chk_gre_archive(p_payroll_action_id);
1741
1742 l_step := 3;
1743 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1744 exception
1745 when others then
1746 raise_application_error(-20001,'eoy_archinit at '
1747 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1748 end eoy_archinit;
1749
1750
1751 /* Name : eoy_archive_data
1752 Purpose : This is the main procedure to archive the whole employee
1753 data along with balance values for RL2 Archiver PreProcess.
1754
1755 Arguments :
1756 Notes :
1757 */
1758
1759 PROCEDURE eoy_archive_data(p_assactid in number,
1760 p_effective_date in date) IS
1761
1762 l_aaid pay_assignment_actions.assignment_action_id%type;
1763 l_aaid1 pay_assignment_actions.assignment_action_id%type;
1764 l_aaseq pay_assignment_actions.action_sequence%type;
1765 l_asgid pay_assignment_actions.assignment_id%type;
1766 l_date_earned date;
1767 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1768 l_reporting_type varchar2(240);
1769 l_prev_tax_unit_id pay_assignment_actions.tax_unit_id%type := null;
1770 l_business_group_id number;
1771 l_year_start date;
1772 l_year_end date;
1773 l_context_no number := 60;
1774 l_count number := 0;
1775 l_jurisdiction varchar2(11);
1776 l_province_uei ff_user_entities.user_entity_id%type;
1777 l_county_uei ff_user_entities.user_entity_id%type;
1778 l_city_uei ff_user_entities.user_entity_id%type;
1779 l_county_sd_uei ff_user_entities.user_entity_id%type;
1780 l_city_sd_uei ff_user_entities.user_entity_id%type;
1781 l_province_abbrev pay_us_states.state_abbrev%type;
1782 l_county_name pay_us_counties.county_name%type;
1783 l_city_name pay_us_city_names.city_name%type;
1784 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
1785 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
1786 l_step number := 0;
1787 l_county_code varchar2(3);
1788 l_city_code varchar2(4);
1789 l_jursd_context_id ff_contexts.context_id%type;
1790 l_taxunit_context_id ff_contexts.context_id%type;
1791 l_seq_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1792 l_context_id_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1793 l_context_val_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1794 l_chunk number;
1795 l_payroll_action_id number;
1796 l_person_id number;
1797 l_defined_balance_id number;
1798 l_archive_item_id number;
1799 l_date_of_birth date;
1800 l_hire_date date;
1801 l_termination_date date;
1802 l_first_name varchar2(240);
1803 l_middle_name varchar2(240);
1804 l_last_name varchar2(240);
1805 l_employee_number varchar2(240);
1806 l_pre_name_adjunct varchar2(240);
1807 l_employee_phone_no varchar2(240);
1808 l_address_line1 varchar2(240);
1809 l_address_line2 varchar2(240);
1810 l_address_line3 varchar2(240);
1811 l_town_or_city varchar2(80);
1812 l_province_code varchar2(80);
1813 l_postal_code varchar2(80);
1814 l_telephone_number varchar2(80);
1815 l_country_code varchar2(80);
1816 l_counter number := 0;
1817
1818 l_count_start_for_boxo number := 0;
1819 l_count_end_for_boxo number := 0;
1820 l_count_for_boxo_code number := 0;
1821 l_pre_org_id varchar2(80);
1822 l_national_identifier varchar2(240);
1823 l_user_entity_value_tab_boxo number := 0;
1824 l_user_entity_code_tab_boxo VARCHAR2(4) := NULL;
1825 l_object_version_number number;
1826 l_rl2_slip_number_last_digit number;
1827 l_rl2_slip_number number;
1828 l_negative_balance_exists varchar2(2);
1829
1830 l_max_assactid number;
1831 l_some_warning boolean;
1832 result number;
1833 l_no_of_payroll_run number := 0;
1834 l_has_been_paid varchar2(3);
1835 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1836 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1837 l_balance_type_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1838 l_footnote_balance_type_tab varchar2(80);
1839 l_footnote_code varchar2(30);
1840 l_footnote_balance varchar2(80);
1841 l_footnote_amount number;
1842 old_l_footnote_code varchar2(80) := null;
1843 old_balance_type_tab varchar2(80) := null;
1844 l_footnote_code_ue varchar2(80);
1845 l_footnote_amount_ue varchar2(80);
1846 l_no_of_fn_codes number := 0;
1847 l_value number := 0;
1848 l_transmitter_id number;
1849 l_rl2_last_slip_number number;
1850 l_rl2_curr_slip_number number;
1851 l_max_slip_number varchar2(80);
1852 fed_result number;
1853
1854 l_messages varchar2(240);
1855 l_mesg_amt number(12,2) := 0;
1856
1857 l_action_information_id_1 NUMBER ;
1858 l_object_version_number_1 NUMBER ;
1859 ln_tax_unit_id NUMBER ;
1860 ld_eff_date DATE ;
1861
1862 ln_status_indian NUMBER := 0;
1863 ln_index NUMBER;
1864 ln_footnote_index NUMBER;
1865 l_rl2_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1866 lv_footnote_bal varchar2(80);
1867
1868 l_rl2_source_of_income varchar2(150);
1869 l_per_eit_source_of_income varchar2(150);
1870 l_pre_source_of_income varchar2(150);
1871 l_per_eit_description varchar2(150);
1872 l_pre_description varchar2(150);
1873 l_per_eit_beneficiary_id varchar2(20);
1874 l_beneficiary_name varchar2(150);
1875 l_beneficiary_sin varchar2(20);
1876 ln_no_gross_earnings NUMBER := 0;
1877
1878 ln_defined_balance_id pay_defined_balances.defined_balance_id%type;
1879 lv_serial_number varchar2(30);
1880 lv_BoxL_excess_amt varchar2(30);
1881 lv_BoxO_excess_amt varchar2(30);
1882 lv_BoxL_Maxlimit varchar2(30);
1883 lv_BoxO_Maxlimit varchar2(30);
1884
1885 /* new variables added for Provincial YE Amendment PP */
1886 lv_fapp_effective_date varchar2(5);
1887 ln_fapp_pre_org_id number;
1888 lv_fapp_report_type varchar2(20);
1889 ln_fapp_locked_action_id number;
1890 lv_fapp_flag varchar2(2):= 'N';
1891
1892 /* new variables added for pre-printed form number */
1893 lv_eit_year varchar2(30);
1894 lv_eit_pre_org_id varchar2(40);
1895 lv_eit_form_no varchar2(20);
1896
1897 cursor c_get_fapp_locked_action_id(cp_locking_act_id number) is
1898 select locked_action_id
1899 from pay_action_interlocks
1900 where locking_action_id = cp_locking_act_id;
1901
1902 cursor c_get_preprinted_form_no (cp_person_id number,
1903 cp_pre_org_id number) is
1904 select pei_information5,
1905 pei_information6,
1906 pei_information7
1907 from per_people_extra_info
1908 where person_id = cp_person_id
1909 and pei_information6 = to_char(cp_pre_org_id)
1910 and pei_information_category = 'PAY_CA_RL2_FORM_NO';
1911
1912 /* Cursor to get the all gre values that are under the archived
1913 transmitter PRE */
1914 cursor c_all_gres(asgactid number) is
1915 select hoi.organization_id ,
1916 hoi.org_information5
1917 from pay_action_information pac,
1918 pay_assignment_actions paa,
1919 hr_organization_information hoi
1920 where paa.assignment_action_id = asgactid
1921 and pac.action_context_id = paa.payroll_action_id
1922 and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
1923 and pac.action_information1 = 'RL2'
1924 and pac.action_information27 = hoi.org_information2
1925 and hoi.org_information_context = 'Canada Employer Identification'
1926 order by 1;
1927
1928 /* Cursor to get the all gre values that are under the archived
1929 transmitter PRE */
1930 cursor c_all_gres_for_footnote(asgactid number) is
1931 select hoi.organization_id ,
1932 hoi.org_information5
1933 from pay_action_information pac,
1934 pay_assignment_actions paa,
1935 hr_organization_information hoi
1936 where paa.assignment_action_id = asgactid
1937 and pac.action_context_id = paa.payroll_action_id
1938 and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
1939 and pac.action_information1 = 'RL2'
1940 and pac.action_information27 = hoi.org_information2
1941 and hoi.org_information_context = 'Canada Employer Identification'
1942 order by 1;
1943
1944
1945 /* c_all_gres_for_person cursor because we not using anymore, 11510 bugfix */
1946
1947 /* Cursor to get the all the footnote elements that
1948 are fed to the given balance name */
1949 cursor c_footnote_info(p_balance_name varchar2) is
1950 select distinct pet.element_information19,
1951 pbt1.balance_name
1952 from pay_balance_feeds_f pbf,
1953 pay_balance_types pbt,
1954 pay_balance_types pbt1,
1955 pay_input_values_f piv,
1956 pay_element_types_f pet,
1957 fnd_lookup_values flv
1958 where pbt.balance_name = p_balance_name
1959 and pbf.balance_type_id = pbt.balance_type_id
1960 and pbf.input_value_id = piv.input_value_id
1961 and piv.element_type_id = pet.element_type_id
1962 and pbt1.balance_type_id = pet.element_information10
1963 and pet.business_group_id = l_business_group_id
1964 and pet.element_information19 = flv.lookup_code
1965 and flv.lookup_type = 'PAY_CA_RL2_FOOTNOTES'
1966 and flv.language = userenv('LANG')
1967 order by pet.element_information19;
1968
1969 /* Cursor to get the employee primary address */
1970 cursor c_get_pri_addr(cp_person_id in number
1971 ,cp_date_earned in date) is
1972 select address_line1,
1973 address_line2,
1974 address_line3,
1975 town_or_city,
1976 decode(country,'US',region_2,'CA',region_1,null),
1977 replace(postal_code,' '),
1978 country
1979 from per_addresses pa
1980 where pa.person_id = cp_person_id
1981 and pa.primary_flag = 'Y'
1982 and cp_date_earned between pa.date_from
1983 and nvl(pa.date_to, cp_date_earned);
1984
1985 /* Cursor to get the employee secondary address */
1986 cursor c_get_sec_addr(cp_person_id in number
1987 ,cp_date_earned in date) is
1988 select address_line1,
1989 address_line2,
1990 address_line3,
1991 town_or_city,
1992 decode(country,'US',region_2,'CA',region_1,null),
1993 replace(postal_code,' '),
1994 country
1995 from per_addresses pa
1996 where pa.person_id = cp_person_id
1997 and pa.primary_flag <> 'Y'
1998 and cp_date_earned between pa.date_from
1999 and nvl(pa.date_to, cp_date_earned)
2000 order by pa.date_from desc;
2001
2002 /* Cursor to get the employee details */
2003 cursor c_get_emp_detail(cp_asg_id number) is
2004 select PEOPLE.person_id,
2005 PEOPLE.first_name,
2006 PEOPLE.middle_names,
2007 PEOPLE.last_name,
2008 PEOPLE.employee_number,
2009 PEOPLE.date_of_birth,
2010 replace(PEOPLE.national_identifier,' '),
2011 PEOPLE.pre_name_adjunct
2012 from per_all_assignments_f ASSIGN
2013 ,per_all_people_f PEOPLE
2014 where ASSIGN.assignment_id = cp_asg_id
2015 and PEOPLE.person_id = ASSIGN.person_id
2016 and PEOPLE.effective_end_date =
2017 (select max(effective_end_date)
2018 from per_all_people_f PEOPLE1
2019 where PEOPLE1.person_id = PEOPLE.person_id);
2020
2021
2022 /* Query to get the max asg_act_id for a payroll run in a given year
2023 with tax_unit_id, asg_id and effective_date as parameters. Changed
2024 cursor to get max asgact_id based on person_id, fix for bug#3638928 */
2025 CURSOR c_get_max_asg_act_id(cp_person_id number,
2026 cp_tax_unit_id number,
2027 cp_period_start date,
2028 cp_period_end date) IS
2029 select paa.assignment_action_id
2030 from pay_assignment_actions paa,
2031 per_all_assignments_f paf,
2032 per_all_people_f ppf,
2033 pay_payroll_actions ppa,
2034 pay_action_classifications pac,
2035 pay_action_contexts pac1,
2036 ff_contexts fc
2037 where ppf.person_id = cp_person_id
2038 and paf.person_id = ppf.person_id
2039 and paa.assignment_id = paf.assignment_id
2040 and paa.tax_unit_id = cp_tax_unit_id
2041 and paa.payroll_action_id = ppa.payroll_action_id
2042 and ppa.action_type = pac.action_type
2043 and pac.classification_name = 'SEQUENCED'
2044 and ppa.effective_date between paf.effective_start_date
2045 and paf.effective_end_date
2046 and ppa.effective_date between ppf.effective_start_date
2047 and ppf.effective_end_date
2048 and ppa.effective_date between cp_period_start and cp_period_end
2049 AND pac1.assignment_action_id = paa.assignment_action_id
2050 AND pac1.assignment_id = paa.assignment_id
2051 AND fc.context_id = pac1.context_id
2052 AND fc.context_name = 'JURISDICTION_CODE'
2053 AND pac1.context_value = 'QC'
2054 order by paa.action_sequence desc;
2055
2056 CURSOR c_get_person_extra_info (cp_person_id number,
2057 cp_pre_org_id varchar2) IS
2058 select pei_information2,
2059 pei_information3,
2060 pei_information4
2061 from per_people_extra_info
2062 where person_id = cp_person_id
2063 and pei_information1 = cp_pre_org_id
2064 and pei_information_category = 'PAY_CA_RL2_INFORMATION';
2065
2066 /* Bug#3358604, Cursor to get RL2 Box L and O Max Limits for validation */
2067 CURSOR c_get_rl2box_limits(cp_lookup_code varchar2,
2068 cp_eff_date date) IS
2069 select information_value
2070 from pay_ca_legislation_info
2071 where lookup_type = 'RL2ARCHIVE'
2072 and lookup_code = cp_lookup_code
2073 and cp_eff_date between start_date and end_date;
2074
2075 BEGIN
2076
2077 --hr_utility.trace_on(null,'RL2');
2078 hr_utility.set_location ('archive_data',1);
2079 hr_utility.trace('getting assignment');
2080 l_negative_balance_exists := 'N';
2081 l_has_been_paid := 'N';
2082
2083 lv_BoxL_Excess_amt := '0';
2084 lv_BoxO_Excess_amt := '0';
2085
2086 initialization_process('EMPLOYEE_DATA');
2087
2088 l_step := 1;
2089 begin
2090
2091 SELECT aa.assignment_id,
2092 pay_magtape_generic.date_earned
2093 (p_effective_date,aa.assignment_id),
2094 aa.tax_unit_id,
2095 aa.chunk_number,
2096 aa.payroll_action_id,
2097 aa.serial_number
2098 into l_asgid,
2099 l_date_earned,
2100 l_tax_unit_id,
2101 l_chunk,
2102 l_payroll_action_id,
2103 lv_serial_number
2104 FROM pay_assignment_actions aa
2105 WHERE aa.assignment_action_id = p_assactid;
2106
2107 l_rl2_tax_unit_id := l_tax_unit_id;
2108
2109 select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
2110 legislative_parameters),
2111 business_group_id
2112 into l_pre_org_id,
2113 l_business_group_id
2114 from pay_payroll_actions
2115 where payroll_action_id = l_payroll_action_id;
2116
2117 exception when no_data_found then
2118 /* need a pop-message */
2119 hr_utility.trace('assignment_action_id doesnot exist to archive emp_info'
2120 ||to_char(p_assactid));
2121 end;
2122
2123 /* If the chunk of the assignment is same as the minimun chunk
2124 for the payroll_action_id and the gre data has not yet been
2125 archived then archive the gre data i.e. the employer data */
2126
2127 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2128
2129 hr_utility.trace('eoy_archive_data archiving employer data');
2130 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2131 hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2132
2133 eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2134 p_pre_org_id=>l_pre_org_id);
2135
2136 hr_utility.trace('eoy_archive_data archived employer data');
2137
2138 end if;
2139
2140 hr_utility.set_location ('archive_data',2);
2141
2142 hr_utility.trace('assignment '|| to_char(l_asgid));
2143 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2144 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2145 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2146
2147 /* Derive the beginning and end of the effective year */
2148
2149 hr_utility.trace('getting begin and end dates');
2150
2151 l_step := 2;
2152
2153 l_year_start := trunc(p_effective_date, 'Y');
2154 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2155
2156 hr_utility.trace('year start '|| to_char(l_year_start));
2157 hr_utility.trace('year end '|| to_char(l_year_end));
2158
2159
2160 l_step := 3;
2161
2162 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2163
2164 l_step := 5;
2165
2166 select context_id
2167 into l_jursd_context_id
2168 from ff_contexts
2169 where context_name = 'JURISDICTION_CODE';
2170
2171 select context_id
2172 into l_taxunit_context_id
2173 from ff_contexts
2174 where context_name = 'TAX_UNIT_ID';
2175
2176 l_step := 6;
2177
2178 l_jurisdiction := 'QC';
2179
2180 l_step := 12;
2181
2182 /* We can archive the balance level dbis also because for employee level
2183 balances jurisdiction is always a context. */
2184
2185 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2186
2187 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2188 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2189
2190 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2191
2192 /* RL2 Slip number generation */
2193
2194 begin
2195 select decode(hoi.org_information3,'Y',hoi.organization_id,
2196 hoi.org_information20)
2197 into l_transmitter_id
2198 from hr_organization_information hoi,
2199 hr_all_organization_units hou
2200 WHERE hou.business_group_id = l_business_group_id
2201 and hoi.organization_id = hou.organization_id
2202 and hoi.org_information_context = 'Prov Reporting Est'
2203 and hoi.organization_id = to_number(l_pre_org_id)
2204 and hoi.org_information4 = 'P02';
2205
2206
2207 hr_utility.trace('l_transmitter_id : ' || to_char(l_transmitter_id));
2208
2209 hr_utility.trace('3');
2210
2211 select to_number(target.ORG_INFORMATION18)
2212 into l_rl2_last_slip_number
2213 from hr_organization_information target
2214 where target.organization_id = l_transmitter_id
2215 and target.org_information_context = 'Prov Reporting Est'
2216 and target.ORG_INFORMATION3 = 'Y';
2217
2218 hr_utility.trace('l_rl2_last_slip_number b4 adding sequence= '|| l_rl2_last_slip_number);
2219
2220 select l_rl2_last_slip_number + pay_ca_eoy_rl2_s.nextval - 1
2221 into l_rl2_curr_slip_number from dual;
2222
2223 hr_utility.trace('1');
2224
2225 l_rl2_slip_number_last_digit := mod(l_rl2_curr_slip_number,7);
2226
2227 hr_utility.trace('l_rl2_curr_slip_number : '||l_rl2_curr_slip_number);
2228 hr_utility.trace('l_rl2_slip_number_last_digit : '||
2229 l_rl2_slip_number_last_digit);
2230
2231 l_rl2_slip_number := (l_rl2_curr_slip_number)||
2232 l_rl2_slip_number_last_digit;
2233
2234 hr_utility.trace('l_rl2_slip_number : ' || l_rl2_slip_number);
2235
2236 begin
2237 select hoi.org_information1,hoi.org_information2
2238 into l_pre_source_of_income,l_pre_description
2239 from hr_organization_information hoi
2240 where hoi.organization_id = l_transmitter_id
2241 and hoi.org_information_context = 'Prov Reporting Est2';
2242
2243 exception
2244 when no_data_found then
2245 hr_utility.trace('No RL2 Source of Income at PRE level');
2246 hr_utility.trace('l_transmitter_id :'||to_char(l_transmitter_id));
2247 l_pre_source_of_income := NULL;
2248 l_pre_description := NULL;
2249 end;
2250
2251 exception
2252 when no_data_found then
2253 hr_utility.trace('Problem in generation of RL2 Slip Number');
2254 hr_utility.trace('l_transmitter_id :'||to_char(l_transmitter_id));
2255 /* need a pop-message if rl2 slip number not generated */
2256 l_rl2_slip_number := 0;
2257
2258 end;
2259
2260 /* Initialise the PL/SQL table before populating it */
2261 hr_utility.trace('Initialising Pl/SQL table');
2262
2263 l_count := 0;
2264
2265 l_count := l_count + 1;
2266 l_balance_type_tab(l_count) := 'Gross Earnings';
2267
2268 l_count := l_count + 1;
2269 l_balance_type_tab(l_count) := 'QPP EE Withheld';
2270
2271 l_count := l_count + 1;
2272 l_balance_type_tab(l_count) := 'EI EE Withheld';
2273
2274 -- Quebec Income tax withheld (used for RL2 Box J)
2275 l_count := l_count + 1;
2276 l_balance_type_tab(l_count) := 'PROV Withheld';
2277
2278 -- RL2 Box A Registered Plan
2279 l_count := l_count + 1;
2280 l_balance_type_tab(l_count) := 'Life Annuity Payments registered plan';
2281
2282 -- RL2 Box A Unregistered Plan
2283 l_count := l_count + 1;
2284 l_balance_type_tab(l_count) := 'Life Annuity Payments Unregistered plan';
2285
2286 -- RL2 Box B
2287 l_count := l_count + 1;
2288 l_balance_type_tab(l_count) := 'Benefits from RRSP RRIF DPSP and Annuities';
2289
2290 -- RL2 Box C
2291 l_count := l_count + 1;
2292 l_balance_type_tab(l_count) := 'Other Payments';
2293
2294 -- RL2 Box D
2295 l_count := l_count + 1;
2296 l_balance_type_tab(l_count)
2297 := 'Refund of RRSP Premiums paid to surviving spouse';
2298
2299 -- RL2 Box E
2300 l_count := l_count + 1;
2301 l_balance_type_tab(l_count) := 'Benefits at the time of death';
2302
2303 -- RL2 Box F
2304 l_count := l_count + 1;
2305 l_balance_type_tab(l_count) := 'Refund of Undeducted RRSP contributions';
2306
2307 -- RL2 Box G
2308 l_count := l_count + 1;
2309 l_balance_type_tab(l_count)
2310 := 'Taxable Amount revoked registration RRSP or RRIF';
2311
2312 -- RL2 Box H
2313 l_count := l_count + 1;
2314 l_balance_type_tab(l_count) := 'Other Income RRSP or RRIF';
2315
2316 -- RL2 Box I
2317 l_count := l_count + 1;
2318 l_balance_type_tab(l_count) :=
2319 'Amount entitlement deduction for RRSP or RRIF';
2320
2321 -- RL2 Box K
2322 l_count := l_count + 1;
2323 l_balance_type_tab(l_count) :=
2324 'Income earned after death RRSP or RRIF';
2325
2326 -- RL2 Box L
2327 l_count := l_count + 1;
2328 l_balance_type_tab(l_count) :=
2329 'Withdrawal under the Lifelong Learning Plan';
2330
2331 -- RL2 Box M
2332 l_count := l_count + 1;
2333 l_balance_type_tab(l_count) := 'Tax Paid Amounts';
2334
2335 -- RL2 Box O
2336 l_count := l_count + 1;
2337 l_balance_type_tab(l_count) := 'Withdrawal under the Home Buyers Plan';
2338
2339 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2340
2341 for i in 1 .. l_count
2342 loop
2343 hr_utility.trace('Initialising values');
2344 l_user_entity_value_tab(i) := 0;
2345 end loop;
2346
2347 open c_all_gres(p_assactid);
2348
2349 loop
2350
2351 hr_utility.trace('Fetching all GREs');
2352 fetch c_all_gres into l_tax_unit_id,l_reporting_type;
2353 exit when c_all_gres%NOTFOUND;
2354
2355 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2356 hr_utility.trace('Person_id is ' || lv_serial_number);
2357 hr_utility.trace('Asgid is ' || to_char(l_asgid));
2358 hr_utility.trace('Reporting_type is ' || l_reporting_type);
2359 hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2360
2361 begin
2362 /* Getting Payroll Run Level Max Assignment Action Id for
2363 the given tax_unit_id in the reporting year. Fix for bug#3638928 */
2364
2365 open c_get_max_asg_act_id(to_number(lv_serial_number),
2366 l_tax_unit_id,
2367 l_year_start,
2368 l_year_end);
2369 fetch c_get_max_asg_act_id into l_aaid;
2370 close c_get_max_asg_act_id;
2371
2372 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2373 hr_utility.trace('l_count is ' || to_char(l_count));
2374
2375 ln_no_gross_earnings := ln_no_gross_earnings +
2376 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2377 ('RL2 No Gross Earnings',
2378 'YTD' ,
2379 l_aaid,
2380 l_asgid,
2381 NULL,
2382 'PER' ,
2383 l_tax_unit_id,
2384 l_business_group_id,
2385 'QC'
2386 ),0);
2387
2388 l_no_of_payroll_run := l_no_of_payroll_run + 1;
2389
2390 if l_tax_unit_id <> l_prev_tax_unit_id or
2391 l_prev_tax_unit_id is null then
2392
2393 hr_utility.trace('l_business_group_id is '||to_char(l_business_group_id));
2394
2395 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2396 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2397 Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
2398
2399 for i in 1 .. l_count
2400 loop
2401
2402 hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
2403 hr_utility.trace('i is ' || i);
2404
2405 -- T4A earnings should not go to BOX A of RL2
2406
2407 if l_reporting_type = 'T4A/RL2' and
2408 l_balance_type_tab(i) = 'Gross Earnings'
2409 then
2410 null;
2411 else
2412 -- l_user_entity_value_tab(i) := 0;
2413
2414 if l_balance_type_tab(i) = 'Gross Earnings' then
2415
2416 fed_result :=
2417 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2418 ('Taxable Benefits for Federal',
2419 'YTD' ,
2420 l_aaid,
2421 l_asgid ,
2422 NULL,
2423 'PER' ,
2424 l_tax_unit_id,
2425 l_business_group_id,
2426 'QC'
2427 ),0);
2428
2429 hr_utility.trace('Fed Result = ' || fed_result);
2430 else
2431 fed_result := 0;
2432 hr_utility.trace('Fed Result = ' || fed_result);
2433 end if;/*end if for l_balance_type_tab(i)='Gross Earnings' */
2434
2435 /* Based on defined_balance_id get the balance value
2436 for each assignment action */
2437
2438 ln_defined_balance_id :=
2439 get_def_bal_id(l_balance_type_tab(i),
2440 'Person in JD within GRE Year to Date',
2441 'CA');
2442
2443 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
2444 nvl(pay_balance_pkg.get_value(ln_defined_balance_id,
2445 l_aaid),0);
2446
2447 if l_user_entity_value_tab(i) <> 0 then
2448 l_has_been_paid := 'Y';
2449 if l_balance_type_tab(i) = 'FED STATUS INDIAN Subject' then
2450 ln_status_indian := l_user_entity_value_tab(i);
2451 end if;
2452 end if;
2453
2454 end if; -- end if for 'T4A/RL2' validation
2455
2456 hr_utility.trace('Balance Type is '||l_balance_type_tab(i));
2457 hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
2458 l_prev_tax_unit_id := l_tax_unit_id ;
2459
2460 end loop; -- end loop for all balances plsql table
2461 end if; --end if for l_tax_unit_id <> l_prev_tax_unit_id validation
2462
2463 exception
2464 when no_data_found then
2465 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2466 /* need a pop-message asgid has no payroll run in tax-unit-id */
2467 end;
2468 end loop;
2469 close c_all_gres;
2470
2471 hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
2472
2473 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count;
2474 ln_footnote_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count;
2475
2476 hr_utility.trace('ln_index :'||to_char(ln_index));
2477 hr_utility.trace('ln_footnote_index :'||to_char(ln_footnote_index));
2478
2479 if ((l_no_of_payroll_run > 0) and
2480 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
2481
2482 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info1
2483 := l_rl2_slip_number;
2484 for i in 1 .. l_count
2485 loop
2486
2487 hr_utility.trace('in the employee info archive loop');
2488 hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
2489 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
2490 /*
2491 lv_BoxL_excess_amt := '0';
2492 lv_BoxO_excess_amt := '0';
2493 */
2494
2495 if l_balance_type_tab(i) =
2496 'Life Annuity Payments registered plan' then
2497 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3
2498 := l_user_entity_value_tab(i);
2499 hr_utility.trace('ln_index :'||to_char(ln_index));
2500
2501 elsif l_balance_type_tab(i) =
2502 'Life Annuity Payments Unregistered plan' then
2503
2504 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3
2505 := to_number
2506 (pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3)
2507 + to_number(nvl(l_user_entity_value_tab(i),0));
2508
2509 elsif l_balance_type_tab(i) =
2510 'Benefits from RRSP RRIF DPSP and Annuities' then
2511 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info4
2512 := l_user_entity_value_tab(i);
2513
2514 elsif l_balance_type_tab(i) = 'Other Payments' then
2515 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
2516 := l_user_entity_value_tab(i);
2517 hr_utility.trace('Box C :'||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
2518
2519 elsif l_balance_type_tab(i) =
2520 'Refund of RRSP Premiums paid to surviving spouse' then
2521 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info6
2522 := l_user_entity_value_tab(i);
2523
2524 elsif l_balance_type_tab(i) = 'Benefits at the time of death' then
2525 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info7
2526 := l_user_entity_value_tab(i);
2527
2528 elsif l_balance_type_tab(i) =
2529 'Refund of Undeducted RRSP contributions' then
2530 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info8
2531 := l_user_entity_value_tab(i);
2532
2533 elsif l_balance_type_tab(i) =
2534 'Taxable Amount revoked registration RRSP or RRIF' then
2535 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info9
2536 := l_user_entity_value_tab(i);
2537
2538 elsif l_balance_type_tab(i) = 'Other Income RRSP or RRIF' then
2539 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info10
2540 := l_user_entity_value_tab(i);
2541
2542 elsif l_balance_type_tab(i) =
2543 'Amount entitlement deduction for RRSP or RRIF' then
2544 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info11
2545 := l_user_entity_value_tab(i);
2546
2547 elsif l_balance_type_tab(i) = 'PROV Withheld' then
2548 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info12
2549 := l_user_entity_value_tab(i);
2550
2551 elsif l_balance_type_tab(i) =
2552 'Income earned after death RRSP or RRIF' then
2553 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info13
2554 := l_user_entity_value_tab(i);
2555
2556 elsif l_balance_type_tab(i) =
2557 'Withdrawal under the Lifelong Learning Plan' then
2558
2559 /* Bug#3358604, if Box L is more than $10,000.00 put excess
2560 amount in Box C */
2561 open c_get_rl2box_limits('BOXL_MAXLIMIT',p_effective_date);
2562 fetch c_get_rl2box_limits into lv_boxL_Maxlimit;
2563 close c_get_rl2box_limits;
2564
2565 if to_number(l_user_entity_value_tab(i)) > to_number(lv_boxL_Maxlimit) then
2566
2567 lv_BoxL_excess_amt := to_char(to_number(l_user_entity_value_tab(i))
2568 - to_number(lv_boxL_Maxlimit)) ;
2569 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info14
2570 := lv_boxL_Maxlimit;
2571 else
2572 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info14
2573 := l_user_entity_value_tab(i);
2574 end if;
2575
2576 elsif l_balance_type_tab(i) = 'Tax Paid Amounts' then
2577 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info15
2578 := l_user_entity_value_tab(i);
2579
2580 elsif l_balance_type_tab(i)
2581 = 'Withdrawal under the Home Buyers Plan' then
2582
2583 /* Bug#3358604, if Box O is more than $20,000.00 put excess
2584 amount in Box C */
2585 open c_get_rl2box_limits('BOXO_MAXLIMIT',p_effective_date);
2586 fetch c_get_rl2box_limits into lv_boxO_Maxlimit;
2587 close c_get_rl2box_limits;
2588
2589 if to_number(l_user_entity_value_tab(i)) > to_number(lv_boxO_Maxlimit) then
2590
2591 lv_BoxO_excess_amt := to_char(to_number(l_user_entity_value_tab(i))
2592 - to_number(lv_boxO_Maxlimit));
2593 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info17
2594 := lv_boxO_Maxlimit;
2595 else
2596 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info17
2597 := l_user_entity_value_tab(i);
2598 end if;
2599
2600 end if;
2601
2602
2603 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
2604
2605 if to_number(nvl(l_user_entity_value_tab(i),'0')) < 0 then
2606
2607 hr_utility.trace('Negative balance exists');
2608 l_negative_balance_exists := 'Y';
2609 end if;
2610
2611 if l_user_entity_value_tab(i) <> 0 then
2612
2613 if l_balance_type_tab(i)
2614 = 'Life Annuity Payments Unregistered plan' then
2615 begin
2616
2617 /* RL2 Automatic Footnote Archive Start */
2618 l_footnote_code := 'BOXA';
2619 if chk_rl2_footnote(l_footnote_code) then
2620
2621 l_footnote_amount
2622 := to_number(nvl(l_user_entity_value_tab(i),0));
2623 ln_footnote_index := ln_footnote_index;
2624 hr_utility.trace(' Box A ln_footnote_index :'
2625 ||to_char(ln_footnote_index));
2626
2627 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2628 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
2629 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2630 (ln_footnote_index).jurisdiction_code := 'QC';
2631 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2632 (ln_footnote_index).act_info4 := l_footnote_code;
2633 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2634 (ln_footnote_index).act_info5 :=
2635 to_char(l_footnote_amount);
2636 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2637 (ln_footnote_index).act_info6 := 'RL2';
2638
2639 if l_footnote_amount < 0 then
2640
2641 hr_utility.trace('Negative balance exists');
2642 l_negative_balance_exists := 'Y';
2643 end if;
2644
2645 end if; /* end if for chk_rl2_footnote */
2646 end ; /* end of RL2 Automatic footnote archive */
2647 else
2648 l_footnote_balance_type_tab := l_balance_type_tab(i);
2649 end if; /* end if for l_balance_type_tab(i)= 'Life Annuity...' */
2650
2651 if l_footnote_balance_type_tab in
2652 ('Benefits from RRSP RRIF DPSP and Annuities',
2653 'Other Payments') then
2654 begin
2655
2656 hr_utility.trace('RL2 Footnote archive start ');
2657 lv_footnote_bal := l_footnote_balance_type_tab;
2658 l_footnote_code := NULL;
2659 old_l_footnote_code := NULL;
2660 l_footnote_amount := 0;
2661
2662 open c_footnote_info(lv_footnote_bal);
2663 hr_utility.trace('lv_footnote_bal is '||lv_footnote_bal);
2664
2665 loop
2666 fetch c_footnote_info into l_footnote_code,
2667 l_footnote_balance;
2668 exit when c_footnote_info%NOTFOUND;
2669
2670 hr_utility.trace('l_footnote_amount_balance is '||
2671 l_footnote_balance);
2672 hr_utility.trace('l_footnote_code is '||
2673 l_footnote_code);
2674
2675 if ( l_footnote_code <> old_l_footnote_code or
2676 old_l_footnote_code is null )
2677 then
2678 if old_l_footnote_code is not null then
2679
2680 hr_utility.trace('old_l_footnote_code is '||
2681 old_l_footnote_code);
2682 hr_utility.trace('l_footnote_amount is '||
2683 to_char(l_footnote_amount));
2684
2685 if chk_rl2_footnote(old_l_footnote_code) and
2686 l_footnote_amount <> 0 then
2687
2688 hr_utility.trace('RL2 footnote archiving ');
2689 ln_footnote_index := ln_footnote_index + 1;
2690 hr_utility.trace('old_l_ftcode ln_footnote_index :'
2691 ||to_char(ln_footnote_index));
2692
2693 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2694 (ln_footnote_index).action_info_category
2695 := 'CA FOOTNOTES';
2696
2697 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2698 (ln_footnote_index).jurisdiction_code := 'QC';
2699
2700 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2701 (ln_footnote_index).act_info4
2702 := old_l_footnote_code;
2703
2704 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2705 (ln_footnote_index).act_info5
2706 := to_char(l_footnote_amount);
2707
2708 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2709 (ln_footnote_index).act_info6 := 'RL2';
2710
2711 if l_footnote_amount < 0 then
2712
2713 hr_utility.trace('Negative balance exists');
2714 l_negative_balance_exists := 'Y';
2715 end if;
2716
2717 end if;/* end if for chk_rl2_footnote */
2718
2719 end if; /* end if for old_l_footnote_code not null */
2720
2721 l_footnote_amount := 0;
2722 old_l_footnote_code := l_footnote_code ;
2723 old_balance_type_tab := l_footnote_balance_type_tab ;
2724
2725 end if; /* end if for l_footnote_code<>old_l_footnote_code*/
2726
2727 l_prev_tax_unit_id := NULL;
2728
2729 -- get the footnote_balance
2730
2731 open c_all_gres_for_footnote(p_assactid);
2732 loop
2733 hr_utility.trace('Fetching all GREs for footnotes');
2734 fetch c_all_gres_for_footnote into l_tax_unit_id,
2735 l_reporting_type;
2736 exit when c_all_gres_for_footnote%NOTFOUND;
2737
2738 hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2739 hr_utility.trace('Asgid is ' || l_asgid);
2740 hr_utility.trace('Reporting_type is ' || l_reporting_type);
2741 hr_utility.trace('Effective date is '|| p_effective_date);
2742
2743 begin
2744 open c_get_max_asg_act_id(to_number(lv_serial_number),
2745 l_tax_unit_id,
2746 l_year_start,
2747 l_year_end);
2748 fetch c_get_max_asg_act_id into l_aaid;
2749 close c_get_max_asg_act_id;
2750
2751 hr_utility.trace('l_aaid is ' || l_aaid);
2752 hr_utility.trace('l_count is ' || l_count);
2753
2754 l_no_of_payroll_run := l_no_of_payroll_run + 1;
2755
2756 if ( l_tax_unit_id <> l_prev_tax_unit_id or
2757 l_prev_tax_unit_id is null )
2758 then
2759 pay_balance_pkg.set_context('TAX_UNIT_ID',
2760 l_tax_unit_id);
2761 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',
2762 l_aaid);
2763 pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
2764
2765 l_footnote_amount := l_footnote_amount +
2766 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2767 ( l_footnote_balance,
2768 'YTD' ,
2769 l_aaid,
2770 l_asgid ,
2771 NULL,
2772 'PER' ,
2773 l_tax_unit_id,
2774 l_business_group_id,
2775 'QC'
2776 ),0) ;
2777
2778 hr_utility.trace('l_footnote_amount is '
2779 || to_char(l_footnote_amount));
2780 end if;
2781
2782 l_prev_tax_unit_id := l_tax_unit_id ;
2783 exception
2784 when no_data_found then
2785 /* need a pop-message asgid has not payrollrun in tx*/
2786 hr_utility.trace('This Tax unit id has no payroll run'
2787 ||' so skip it');
2788 end;
2789 end loop;
2790 close c_all_gres_for_footnote;
2791
2792 -- end of getting balance
2793
2794 if l_footnote_amount <> 0 then
2795 l_no_of_fn_codes := l_no_of_fn_codes + 1;
2796 hr_utility.trace('l_no_of_fn_codes is '
2797 || l_no_of_fn_codes);
2798 end if;
2799
2800 end loop; -- c_footnote_info loop
2801 close c_footnote_info;
2802
2803 -- Archiving the last footnote code and amount
2804 if chk_rl2_footnote(l_footnote_code) and
2805 l_footnote_amount <> 0 then
2806
2807 hr_utility.trace('p_assactid is ' ||to_char(p_assactid));
2808 hr_utility.trace('before ftnote archive l_footnote_code is '
2809 || l_footnote_code);
2810 hr_utility.trace('l_footnote_amount is '
2811 || to_char(l_footnote_amount));
2812
2813 hr_utility.trace('RL2 footnote archiving ');
2814 ln_footnote_index := ln_footnote_index + 1;
2815 hr_utility.trace('after close c_footnote_info ln_footnote_index :'
2816 ||to_char(ln_footnote_index));
2817
2818 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2819 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
2820 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2821 (ln_footnote_index).jurisdiction_code := 'QC';
2822 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2823 (ln_footnote_index).act_info4 := l_footnote_code;
2824 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2825 (ln_footnote_index).act_info5 := to_char(l_footnote_amount);
2826 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2827 (ln_footnote_index).act_info6 := 'RL2';
2828
2829 if l_footnote_amount < 0 then
2830
2831 hr_utility.trace('Negative balance exists');
2832 l_negative_balance_exists := 'Y';
2833 end if;
2834
2835 end if;/* end if for chk_rl2_footnote */
2836
2837 end;
2838 hr_utility.trace('RL2 Footnote archive end ');
2839 end if; /* end if for l_footnote_balance_type_tab in validation */
2840 -- End of footnote archiving
2841
2842 end if; /* end if for l_user_entity_value_tab(i) <>0 */
2843
2844 end loop; /* end loop for plsql table balances */
2845
2846 /* Bug#3358604 Adding Box L,O excess Amount to Box C */
2847 hr_utility.trace('lv_BoxL_excess_amt : '||lv_BoxL_excess_amt);
2848 hr_utility.trace('lv_BoxO_excess_amt : '||lv_BoxO_excess_amt);
2849
2850 if ((lv_BoxL_Excess_amt > 0) or (lv_BoxO_Excess_amt > 0)) then
2851 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
2852 := to_char(NVL(to_number(pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5),0)
2853 + to_number(lv_BoxL_excess_amt) + to_number(lv_BoxO_excess_amt));
2854 hr_utility.trace('Box C : '||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
2855 end if;
2856
2857 /* Set the Negative Balance Flag for Archiving */
2858 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info30
2859 := l_negative_balance_exists;
2860
2861 hr_utility.trace('after loop act_info4 is: '
2862 || pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info4);
2863
2864 end if; /* end if for ((l_no_of_payroll_run >0) and (l_has_been_paid='Y'))*/
2865
2866 -- Need to Archive Non-Box Footnotes, will be done next year
2867
2868 l_count := 0;
2869 -- Similarly create archive data for employee surname,employee first name,
2870 -- employee initial, employee address ,city,province,country,postal code,
2871 -- SIN, employee number , business number .
2872 -- Not all of them has jurisdiction context.
2873
2874 if ((l_no_of_payroll_run > 0) and
2875 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
2876
2877 begin
2878
2879 open c_get_emp_detail(l_asgid);
2880 fetch c_get_emp_detail into
2881 l_person_id,
2882 l_first_name,
2883 l_middle_name,
2884 l_last_name,
2885 l_employee_number,
2886 l_date_of_birth,
2887 l_national_identifier,
2888 l_pre_name_adjunct;
2889
2890 if c_get_emp_detail%NOTFOUND then
2891
2892 /* need a pop-message employee basic data absent */
2893 l_first_name := null;
2894 l_middle_name := null;
2895 l_last_name := null;
2896 l_employee_number := null;
2897 l_national_identifier := null;
2898 l_pre_name_adjunct := null;
2899 l_employee_phone_no := null;
2900 l_date_of_birth := null;
2901 end if;
2902 close c_get_emp_detail;
2903 end;
2904
2905 begin
2906
2907 select max(date_start)
2908 ,max(actual_termination_date)
2909 into l_hire_date
2910 ,l_termination_date
2911 from per_periods_of_service
2912 where person_id = l_person_id;
2913
2914 exception
2915 when no_data_found then
2916 l_hire_date := null;
2917 l_termination_date := null;
2918
2919 end;
2920
2921 open c_get_person_extra_info(l_person_id, l_pre_org_id);
2922 fetch c_get_person_extra_info into
2923 l_per_eit_source_of_income,
2924 l_per_eit_description,
2925 l_per_eit_beneficiary_id;
2926
2927 if c_get_person_extra_info%NOTFOUND then
2928 close c_get_person_extra_info;
2929 l_per_eit_source_of_income := null;
2930 l_per_eit_description := null;
2931 l_per_eit_beneficiary_id := null;
2932 else
2933 close c_get_person_extra_info;
2934 end if;
2935
2936 if l_per_eit_beneficiary_id is not null then
2937 begin
2938 select ppf.full_name,
2939 replace(ppf.national_identifier,' ')
2940 into l_beneficiary_name,
2941 l_beneficiary_sin
2942 from per_all_people_f ppf
2943 where ppf.person_id = to_number(l_per_eit_beneficiary_id);
2944
2945 exception when no_data_found then
2946 l_beneficiary_name := null;
2947 l_beneficiary_sin := null;
2948 end;
2949 end if;
2950
2951
2952 if l_per_eit_source_of_income is not null then
2953 if l_per_eit_source_of_income = 'OTHER' then
2954 l_rl2_source_of_income := l_per_eit_source_of_income||':'||
2955 l_per_eit_description;
2956
2957 -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
2958 ln_footnote_index := ln_footnote_index + 1;
2959 hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
2960 ||to_char(ln_footnote_index));
2961
2962 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2963 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
2964 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2965 (ln_footnote_index).jurisdiction_code := 'QC';
2966 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2967 (ln_footnote_index).act_info4 := l_rl2_source_of_income;
2968 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2969 (ln_footnote_index).act_info5 := '0';
2970 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2971 (ln_footnote_index).act_info6 := 'RL2';
2972 else
2973 l_rl2_source_of_income := l_per_eit_source_of_income;
2974 end if;
2975 else
2976 if l_pre_source_of_income = 'OTHER' then
2977 l_rl2_source_of_income := l_pre_source_of_income||':'||
2978 l_pre_description;
2979
2980 -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
2981 ln_footnote_index := ln_footnote_index + 1;
2982 hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
2983 ||to_char(ln_footnote_index));
2984
2985 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2986 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
2987 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2988 (ln_footnote_index).jurisdiction_code := 'QC';
2989 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2990 (ln_footnote_index).act_info4 := l_rl2_source_of_income;
2991 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2992 (ln_footnote_index).act_info5 := '0';
2993 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
2994 (ln_footnote_index).act_info6 := 'RL2';
2995 else
2996 l_rl2_source_of_income := l_pre_source_of_income;
2997 end if;
2998 end if;
2999
3000
3001 hr_utility.trace('Before counter of asgid '|| l_asgid);
3002
3003 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).action_info_category
3004 := 'CAEOY RL2 EMPLOYEE INFO';
3005
3006 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).jurisdiction_code
3007 := l_jurisdiction;
3008
3009 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info2
3010 := l_rl2_source_of_income;
3011
3012 -- RL2 Box N SIN of Spouse
3013 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info16
3014 := l_beneficiary_sin;
3015
3016 hr_utility.trace('Employee Info ln_index: '||to_char(ln_index));
3017 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info18
3018 := l_first_name;
3019
3020 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info20
3021 := l_last_name ;
3022
3023 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info19
3024 := l_middle_name ;
3025
3026 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info28
3027 := l_national_identifier;
3028
3029 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info29
3030 := l_employee_number;
3031
3032 end if;
3033
3034 if ((l_no_of_payroll_run > 0) and
3035 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3036
3037 begin
3038 open c_get_pri_addr(l_person_id,l_date_earned);
3039 fetch c_get_pri_addr into l_address_line1
3040 ,l_address_line2
3041 ,l_address_line3
3042 ,l_town_or_city
3043 ,l_province_code
3044 ,l_postal_code
3045 ,l_country_code;
3046 if c_get_pri_addr%NOTFOUND then
3047 open c_get_sec_addr(l_person_id,l_date_earned);
3048 fetch c_get_sec_addr into l_address_line1
3049 ,l_address_line2
3050 ,l_address_line3
3051 ,l_town_or_city
3052 ,l_province_code
3053 ,l_postal_code
3054 ,l_country_code;
3055 if c_get_sec_addr%NOTFOUND then
3056 pay_core_utils.push_message(800,'HR_74010_NO_RES_ADDRESS','A');
3057
3058 l_address_line1 := null;
3059 l_address_line2 := null;
3060 l_address_line3 := null;
3061 l_town_or_city := null;
3062 l_province_code := null;
3063 l_postal_code := null;
3064 l_telephone_number := null;
3065 l_country_code := null;
3066 end if;
3067 close c_get_sec_addr;
3068 end if; /* c_get_pri_addr%NOTFOUND */
3069 close c_get_pri_addr;
3070 end;
3071
3072 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info21
3073 := l_address_line1;
3074
3075 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info22
3076 := l_address_line2;
3077
3078 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info23
3079 := l_address_line3;
3080
3081 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info24
3082 := l_town_or_city;
3083
3084 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info25
3085 := l_province_code;
3086
3087 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info27
3088 := l_country_code;
3089
3090 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info26
3091 := l_postal_code;
3092
3093 end if;
3094
3095 /* Inserting rows into pay_action_information table
3096 RL2 Employee Data Archived */
3097
3098 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count >0 then
3099 archive_data_records(
3100 p_action_context_id => p_assactid
3101 ,p_action_context_type=> 'AAP'
3102 ,p_assignment_id => l_asgid
3103 ,p_tax_unit_id => l_rl2_tax_unit_id
3104 ,p_effective_date => p_effective_date
3105 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data);
3106 ln_index := null;
3107 end if;
3108
3109 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count >0 then
3110 archive_data_records(
3111 p_action_context_id => p_assactid
3112 ,p_action_context_type=> 'AAP'
3113 ,p_assignment_id => l_asgid
3114 ,p_tax_unit_id => l_rl2_tax_unit_id
3115 ,p_effective_date => p_effective_date
3116 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data);
3117 ln_footnote_index := null;
3118 end if;
3119
3120 --hr_utility.trace_on('Y','SAM');
3121 hr_utility.trace('Started Provincial YE Amendment');
3122
3123 select to_char(effective_date,'YYYY'),
3124 report_type,
3125 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
3126 legislative_parameters))
3127 into lv_fapp_effective_date,
3128 lv_fapp_report_type,
3129 ln_fapp_pre_org_id
3130 from pay_payroll_actions
3131 where payroll_action_id = l_payroll_action_id;
3132
3133 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3134
3135
3136 /* Archive the Pre-Printed form number for the RL2
3137 Amendment Pre-Process if one exists*/
3138
3139 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count;
3140
3141 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).action_info_category
3142 := 'CAEOY RL2 EMPLOYEE INFO2';
3143
3144 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).jurisdiction_code
3145 := l_jurisdiction;
3146
3147 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info1
3148 := null;
3149
3150 open c_get_preprinted_form_no (l_person_id, ln_fapp_pre_org_id);
3151 loop
3152 fetch c_get_preprinted_form_no
3153 into lv_eit_year,
3154 lv_eit_pre_org_id,
3155 lv_eit_form_no;
3156
3157 exit when c_get_preprinted_form_no%NOTFOUND;
3158
3159 if ((lv_fapp_effective_date =
3160 to_char(fnd_date.canonical_to_date(lv_eit_year), 'YYYY')) and
3161 (ln_fapp_pre_org_id = to_number(lv_eit_pre_org_id))) then
3162
3163 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info1
3164 := lv_eit_form_no;
3165 end if;
3166
3167 end loop;
3168
3169 close c_get_preprinted_form_no;
3170
3171 if lv_fapp_report_type = 'CAEOY_RL2_AMEND_PP' then
3172
3173 open c_get_fapp_locked_action_id(p_assactid);
3174 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
3175 close c_get_fapp_locked_action_id;
3176
3177 hr_utility.trace('RL2 Amend Action ID : '||to_char(p_assactid));
3178 hr_utility.trace('ln_fapp_locked_action_id :'|| to_char(ln_fapp_locked_action_id));
3179
3180 lv_fapp_flag := compare_archive_data(p_assactid,
3181 ln_fapp_locked_action_id);
3182
3183 end if; -- report type validation for FAPP
3184
3185 hr_utility.trace('Archiving RL2 Amendment Flag : ' || lv_fapp_flag);
3186 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info2
3187 := lv_fapp_flag;
3188
3189 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count >0 then
3190 archive_data_records(
3191 p_action_context_id => p_assactid
3192 ,p_action_context_type=> 'AAP'
3193 ,p_assignment_id => l_asgid
3194 ,p_tax_unit_id => l_rl2_tax_unit_id
3195 ,p_effective_date => p_effective_date
3196 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2);
3197 ln_index := null;
3198 end if;
3199
3200 hr_utility.trace('End of Provincial YE Amendment PP Validation');
3201
3202 end eoy_archive_data;
3203
3204
3205 -- Name : eoy_range_cursor
3206 -- Purpose : This returns the select statement that is used to created
3207 -- the range rows for the Year End Pre-Process.
3208 -- Arguments :
3209 -- Notes :
3210
3211
3212 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
3213
3214 l_pre_org_id varchar2(50);
3215 l_archive boolean:= FALSE;
3216 l_business_group number;
3217 l_year_start date;
3218 l_year_end date;
3219
3220 begin
3221
3222 select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
3223 legislative_parameters),
3224 trunc(effective_date,'Y'),
3225 effective_date,
3226 business_group_id
3227 into l_pre_org_id,
3228 l_year_start,
3229 l_year_end,
3230 l_business_group
3231 from pay_payroll_actions
3232 where payroll_action_id = pactid;
3233
3234 hr_utility.trace('in range cursor step 1');
3235
3236 sqlstr := 'select distinct asg.person_id
3237 from pay_all_payrolls_f ppy,
3238 pay_payroll_actions ppa,
3239 pay_assignment_actions paa,
3240 per_all_assignments_f asg,
3241 pay_payroll_actions ppa1
3242 where ppa1.payroll_action_id = :payroll_action_id
3243 and ppa.effective_date between
3244 fnd_date.canonical_to_date('''||
3245 fnd_date.date_to_canonical(l_year_start)||''') and
3246 fnd_date.canonical_to_date('''||
3247 fnd_date.date_to_canonical(l_year_end)||''')
3248 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
3249 and ppa.action_status = ''C''
3250 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
3251 and ppa.payroll_action_id = paa.payroll_action_id
3252 and paa.tax_unit_id in
3253 (select hoi.organization_id
3254 from hr_organization_information hoi
3255 where hoi.org_information_context = ''Canada Employer Identification''
3256 and hoi.org_information2 = '''|| l_pre_org_id ||''''||'
3257 and hoi.org_information5 = ''T4A/RL2'')
3258 and paa.action_status = ''C''
3259 and paa.assignment_id = asg.assignment_id
3260 and ppa.business_group_id = asg.business_group_id + 0
3261 and ppa.effective_date between asg.effective_start_date
3262 and asg.effective_end_date
3263 and asg.assignment_type = ''E''
3264 and ppa.payroll_id = ppy.payroll_id
3265 and ppy.business_group_id = '||to_char(l_business_group)||'
3266 and exists (select 1
3267 from pay_action_contexts pac,
3268 ff_contexts fc
3269 where pac.assignment_id = paa.assignment_id
3270 and pac.assignment_action_id = paa.assignment_action_id
3271 and pac.context_id = fc.context_id
3272 and fc.context_name = ''JURISDICTION_CODE''
3273 and pac.context_value = ''QC'' )
3274 order by asg.person_id';
3275
3276 l_archive := chk_gre_archive(pactid);
3277 if g_archive_flag = 'N' then
3278 hr_utility.trace('eoy_range_cursor archiving employer data');
3279 /* Now the archiver has provision for archiving
3280 payroll_action_level data . So make use of that */
3281 eoy_archive_gre_data(pactid,
3282 l_pre_org_id);
3283 hr_utility.trace('eoy_range_cursor archived employer data');
3284 end if;
3285
3286 end eoy_range_cursor;
3287
3288 end pay_ca_eoy_rl2_archive;