1 package body pay_ca_eoy_rl2_archive as
2 /* $Header: pycarl2a.pkb 120.19.12020000.5 2013/01/02 10:21:57 sbachu ship $ */
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 09-APR-2009 sapalani 115.33 6768167 Added Function gen_rl2_pdf_seq to
138 generate sequence number for RL2 PDFs.
139 08-MAY-2009 sapalani 115.34 8500723 Added Function getnext_seq_num to
140 calculate and add check digit for
141 PDF sequence number before archiving.
142 23-SEP-2009 aneghosh 115.35 8921055 Added the pre_printed_slip no to the
143 function compare_archive_data so that
144 changes in original slip number will
145 also set the AMENDMENT_FLAG to Y.
146 10-Feb-2011 sneelapa 115.36 11654691 Modified eoy_archive_data procedure to
147 archive slip_number as 9 character
148 if slip number is 12, lpad will be done
149 with 7 ZEROs to make it 9 character.
150
151 29-Aug-2011 sneelapa 115.37 10399514 Introduced new CURSOR c_eoy_qbin_range
152 it will be called in place of
153 c_eoy_qbin CURSOR, if RANGE_PERSON_ID
154 is enabled.
155 12-SEP-2011 rgottipa 115.38 11694701 Introduced new cursor c_get_rl2_pdf_slip
156 It will be used to get Starting and
157 Ending slip numbers.
158 14-SEP-2011 rgottipa 115.39 11694701 Romoved the new cursor c_get_rl2_pdf_slip.
159 Trying to fetch Starting and Ending slip
160 numbers from hr_organization_information.
161 19-SEP-2011 rgottipa 115.40 12996280 Handling no_data_found exception
162 while capturing starting and ending
163 slip numbers.
164 20-Jan-2012 rgottipa 115.41 13584299 Added new procedure
165 'eoy_archive_further_info' to archive
166 new further information.
167 20-NOV-2012 sgotlasw 115.42 14701748 Included new further information code
168 RL2_FURTHER_INFO_AMOUNT_210
169 Modified code to support multiple slip
170 numbers for employee.
171 14-DEC-2012 sgotlasw 115.43 15997380 Corrected the position of
172 'l_negative_balance_exists' flag set to
173 the archiver .
174 02-Jan-2013 sbachu 115.44 16045054 Modified code to archive records
175 even if any of the rl2 further info
176 balance is non zero.
177 */
178
179 eoy_all_qbin varchar2(4000);
180
181 /* Name : get_def_bal_id
182 Purpose : Given the name of a balance and balance dimension
183 the function returns the defined_balance_id .
184
185 Arguments : balance_name,balance_dimension_name and legislation_code
186 Notes : A defined balance_id is required call pay_balance_pkg.get_value.
187 */
188
189 Function get_def_bal_id ( p_balance_name varchar2,
190 p_balance_dimension varchar2,
191 p_legislation_code varchar2)
192 return number is
193
194 /* Get the defined_balance_id for the specified balance name and dimension */
195
196 cursor csr_bal_type_id(cp_bal_name varchar2) is
197 select balance_type_id
198 from pay_balance_types
199 where balance_name = cp_bal_name;
200
201 cursor csr_def_bal_id(cp_bal_type_id number,
202 cp_bal_dimension varchar2,
203 cp_legislation_code varchar2) is
204 select pdb.defined_balance_id
205 from pay_defined_balances pdb,
206 pay_balance_dimensions pbd
207 where pdb.balance_type_id = cp_bal_type_id
208 and pbd.dimension_name = cp_bal_dimension
209 and pbd.balance_dimension_id = pdb.balance_dimension_id
210 and ((pbd.legislation_code = cp_legislation_code and
211 pbd.business_group_id is null)
212 or (pbd.legislation_code is null and
213 pbd.business_group_id is not null));
214
215 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
216 l_balance_type_id pay_balance_types.balance_type_id%type;
217
218 begin
219
220 open csr_bal_type_id(p_balance_name);
221 fetch csr_bal_type_id into l_balance_type_id;
222
223 if csr_bal_type_id%notfound then
224 close csr_bal_type_id;
225 /* need a pop-message */
226 hr_utility.trace('Balance name :'||p_balance_name||'doesnot exist');
227 raise hr_utility.hr_error;
228 else
229 close csr_bal_type_id;
230 end if;
231
232 open csr_def_bal_id(l_balance_type_id,p_balance_dimension,
233 p_legislation_code);
234 fetch csr_def_bal_id into l_defined_balance_id;
235 if csr_def_bal_id%notfound then
236 close csr_def_bal_id;
237 /* need a pop-message */
238 hr_utility.trace('Balance Dimension :'||p_balance_dimension||'doesnot exist');
239 raise hr_utility.hr_error;
240 else
241 close csr_def_bal_id;
242 end if;
243
244 return (l_defined_balance_id);
245
246 end get_def_bal_id;
247
248
249 /* Name : get_dates
250 Purpose : The dates are dependent on the report being run
251 For RL2 it is year end dates.
252 Arguments :
253 Notes :
254 */
255
256 procedure get_dates
257 (
258 p_report_type in varchar2,
259 p_effective_date in date,
260 p_period_end in out nocopy date,
261 p_quarter_start in out nocopy date,
262 p_quarter_end in out nocopy date,
263 p_year_start in out nocopy date,
264 p_year_end in out nocopy date
265 ) is
266 begin
267
268 if p_report_type = 'RL2' then
269
270 /* Year End Pre-process is a yearly process where the identifier
271 indicates the year eg. 1998. The expected values for the example
272 should be
273 p_period_end 31-DEC-1998
274 p_quarter_start 01-OCT-1998
275 p_quarter_end 31-DEC-1998
276 p_year_start 01-JAN-1998
277 p_year_end 31-DEC-1998
278 */
279
280 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
281 p_quarter_start := trunc(p_period_end, 'Q');
282 p_quarter_end := p_period_end;
283
284 /* For EOY */
285
286 end if;
287
288 p_year_start := trunc(p_effective_date, 'Y');
289 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
290
291 end get_dates;
292
293
294 /* Name : get_selection_information
295 Purpose : Returns information used in the selection of people to
296 be reported on.
297 Arguments :
298
299 The following values are returned :
300
301 p_period_start - The start of the period over which to select
302 the people.
303 p_period_end - The end of the period over which to select
304 the people.
305 p_defined_balance_id - The balance which must be non zero for each
306 person to be included in the report.
307 p_group_by_gre - should the people be grouped by GRE.
308 p_group_by_medicare - Should the people ,be grouped by medicare
309 within GRE NB. this is not currently supported.
310 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
311 the testing of the balance.
312 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
313 for the testing of the balance.
314
315 Notes : This routine provides a way of coding explicit rules for
316 individual reports where they are different from the
317 standard selection criteria for the report type ie. in
318 NY state the selection of people in the 4th quarter is
319 different from the first 3.
320 */
321
322 procedure get_selection_information
323 (
324
325 /* Identifies the type of report, the authority for which it is being run,
326 and the period being reported. */
327 p_report_type varchar2,
328 p_quarter_start date,
329 p_quarter_end date,
330 p_year_start date,
331 p_year_end date,
332 /* Information returned is used to control the selection of people to
333 report on. */
334 p_period_start in out nocopy date,
335 p_period_end in out nocopy date,
336 p_defined_balance_id in out nocopy number,
337 p_group_by_gre in out nocopy boolean,
338 p_group_by_medicare in out nocopy boolean,
339 p_tax_unit_context in out nocopy boolean,
340 p_jurisdiction_context in out nocopy boolean
341 ) is
342
343 begin
344
345 /* Depending on the report being processed, derive all the information
346 required to be able to select the people to report on. */
347
348 if p_report_type = 'RL2' then
349
350 /* Default settings for Year End Preprocess. */
351
352 hr_utility.trace('in getting selection information ');
353 p_period_start := p_year_start;
354 p_period_end := p_year_end;
355 p_defined_balance_id := 0;
356 p_group_by_gre := FALSE;
357 p_group_by_medicare := FALSE;
358 p_tax_unit_context := FALSE;
359 p_jurisdiction_context := FALSE;
360
361 /* For EOY end */
362
363 /* An invalid report type has been passed so fail. */
364
365 else
366 hr_utility.trace('in error of getting selection information ');
367
368 raise hr_utility.hr_error;
369
370 end if;
371
372 end get_selection_information;
373
374 /* Name : chk_rl2_footnote
375 Purpose : Function to check whether the RL2 Footnote to be archived
376 is valid or not.
377 Arguments :footnote_code
378 Notes :
379 */
380
381 function chk_rl2_footnote(p_footnote_code varchar2) return boolean is
382
383 l_flag varchar2(1);
384
385 cursor c_chk_footnote is
386 select 'Y'
387 from dual
388 where exists (select 'X'
389 from fnd_lookup_values
390 where ((lookup_type = 'PAY_CA_RL2_FOOTNOTES'
391 and lookup_code = p_footnote_code)
392 OR (lookup_type = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
393 and lookup_code = p_footnote_code))
394 );
395 begin
396
397 hr_utility.trace('chk_rl2_footnote - checking footnote exists');
398 hr_utility.trace('c_chk_footnote - opening cursor');
399
400 open c_chk_footnote;
401 fetch c_chk_footnote into l_flag;
402 if c_chk_footnote%FOUND then
403 hr_utility.trace('c_chk_footnote - found in cursor');
404 l_flag := 'Y';
405 else
406 hr_utility.trace('c_chk_footnote - not found in cursor');
407 l_flag := 'N';
408 end if;
409
410 hr_utility.trace('c_chk_footnote - closing cursor');
411 close c_chk_footnote;
412
413 if l_flag = 'Y' then
414 hr_utility.trace('chk_rl2_footnote - returning true');
415 return (TRUE);
416 else
417 hr_utility.trace('chk_rl2_footnote - returning false');
418 return(FALSE);
419 end if;
420
421 end chk_rl2_footnote;
422
423
424 /*
425 Name : Initialization_process
426 Purpose : This procedure will delete the plsql tables used for
427 archiving the employee and employer data.
428 Arguments :
429 Notes :
430 */
431
432 procedure initialization_process(p_data varchar2)
433 is
434
435 BEGIN
436
437 If p_data = 'EMPLOYEE_DATA' then
438
439 hr_utility.trace('deleting plsql table'|| p_data);
440
441 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count > 0 then
442 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.delete;
443 end if;
444
445 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count > 0 then
446 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.delete;
447 end if;
448
449 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count > 0 then
450 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.delete;
451 end if;
452
453 End if;
454
455 If p_data = 'PRE_DATA' then
456
457 hr_utility.trace('deleting plsql table'|| p_data);
458
459 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.count > 0 then
460 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.delete;
461 end if;
462
463 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count > 0 then
464 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.delete;
465 end if;
466
467 End if;
468
469 END initialization_process;
470
471
472 /*
473 Name : archive_data_records
474 Purpose : This procedure will insert values in to pay_action_information
475 table using the plsql table.
476 Arguments :
477 Notes :
478 */
479
480 procedure archive_data_records(
481 p_action_context_id in number
482 ,p_action_context_type in varchar2
483 ,p_assignment_id in number
484 ,p_tax_unit_id in number
485 ,p_effective_date in date
486 ,p_tab_rec_data in pay_ca_eoy_rl2_archive.action_info_table
487 )
488
489 IS
490 l_action_information_id_1 NUMBER ;
491 l_object_version_number_1 NUMBER ;
492
493 BEGIN
494
495 if p_tab_rec_data.count > 0 then
496 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
497 hr_utility.trace('Defining category '||
498 p_tab_rec_data(i).action_info_category);
499 hr_utility.trace('action_context_id = '|| p_action_context_id);
500 hr_utility.trace('jurisdiction_code '||
501 p_tab_rec_data(i).jurisdiction_code);
502 hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503
504 hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
505
506 hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
507
508 hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
509
510 hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
511
512 hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
513
514 hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
515
516 pay_action_information_api.create_action_information(
517 p_action_information_id => l_action_information_id_1,
518 p_object_version_number => l_object_version_number_1,
519 p_action_information_category
520 => p_tab_rec_data(i).action_info_category,
521 p_action_context_id => p_action_context_id,
522 p_action_context_type => p_action_context_type,
523 p_jurisdiction_code => p_tab_rec_data(i).jurisdiction_code,
524 p_assignment_id => p_assignment_id,
525 p_tax_unit_id => p_tax_unit_id,
526 p_effective_date => p_effective_date,
527 p_action_information1 => p_tab_rec_data(i).act_info1,
528 p_action_information2 => p_tab_rec_data(i).act_info2,
529 p_action_information3 => p_tab_rec_data(i).act_info3,
530 p_action_information4 => p_tab_rec_data(i).act_info4,
531 p_action_information5 => p_tab_rec_data(i).act_info5,
532 p_action_information6 => p_tab_rec_data(i).act_info6,
533 p_action_information7 => p_tab_rec_data(i).act_info7,
534 p_action_information8 => p_tab_rec_data(i).act_info8,
535 p_action_information9 => p_tab_rec_data(i).act_info9,
536 p_action_information10 => p_tab_rec_data(i).act_info10,
537 p_action_information11 => p_tab_rec_data(i).act_info11,
538 p_action_information12 => p_tab_rec_data(i).act_info12,
539 p_action_information13 => p_tab_rec_data(i).act_info13,
540 p_action_information14 => p_tab_rec_data(i).act_info14,
541 p_action_information15 => p_tab_rec_data(i).act_info15,
542 p_action_information16 => p_tab_rec_data(i).act_info16,
543 p_action_information17 => p_tab_rec_data(i).act_info17,
544 p_action_information18 => p_tab_rec_data(i).act_info18,
545 p_action_information19 => p_tab_rec_data(i).act_info19,
546 p_action_information20 => p_tab_rec_data(i).act_info20,
547 p_action_information21 => p_tab_rec_data(i).act_info21,
548 p_action_information22 => p_tab_rec_data(i).act_info22,
549 p_action_information23 => p_tab_rec_data(i).act_info23,
550 p_action_information24 => p_tab_rec_data(i).act_info24,
551 p_action_information25 => p_tab_rec_data(i).act_info25,
552 p_action_information26 => p_tab_rec_data(i).act_info26,
553 p_action_information27 => p_tab_rec_data(i).act_info27,
554 p_action_information28 => p_tab_rec_data(i).act_info28,
555 p_action_information29 => p_tab_rec_data(i).act_info29,
556 p_action_information30 => p_tab_rec_data(i).act_info30
557 );
558
559 end loop;
560 end if;
561
562 END archive_data_records;
563
564
565 FUNCTION compare_archive_data(p_assignment_action_id in number,
566 p_locked_action_id in number,
567 l_pre_printed_slip_no in varchar2) -- For Bug 8921055
568 RETURN VARCHAR2 IS
569
570 TYPE act_info_rec IS RECORD
571 (act_info1 varchar2(240),
572 act_info2 varchar2(240),
573 act_info3 varchar2(240),
574 act_info4 varchar2(240),
575 act_info5 varchar2(240),
576 act_info6 varchar2(240),
577 act_info7 varchar2(240),
578 act_info8 varchar2(240),
579 act_info9 varchar2(240),
580 act_info10 varchar2(240),
581 act_info11 varchar2(240),
582 act_info12 varchar2(240),
583 act_info13 varchar2(240),
584 act_info14 varchar2(240),
585 act_info15 varchar2(240),
586 act_info16 varchar2(240),
587 act_info17 varchar2(240),
588 act_info18 varchar2(240),
589 act_info19 varchar2(240),
590 act_info20 varchar2(240),
591 act_info21 varchar2(240),
592 act_info22 varchar2(240),
593 act_info23 varchar2(240),
594 act_info24 varchar2(240),
595 act_info25 varchar2(240),
596 act_info26 varchar2(240),
597 act_info27 varchar2(240),
598 act_info28 varchar2(240),
599 act_info29 varchar2(240),
600 act_info30 varchar2(240));
601
602 TYPE act_info_ft_rec IS RECORD
603 (message varchar2(240),
604 value varchar2(240));
605
606 TYPE action_info_table IS TABLE OF act_info_rec
607 INDEX BY BINARY_INTEGER;
608
609 TYPE action_info_footnote_table IS TABLE OF act_info_ft_rec
610 INDEX BY BINARY_INTEGER;
611
612 ltr_amend_arch_data action_info_table;
613 ltr_yepp_arch_data action_info_table;
614 ltr_amend_footnote action_info_footnote_table;
615 ltr_yepp_footnote action_info_footnote_table;
616 ltr_amend_pre_printed_slipno varchar2(240); -- For Bug 8921055
617 ltr_yepp_pre_printed_slipno varchar2(240); -- For Bug 8921055
618 ln_yepp_footnote_count number;
619 ln_amend_footnote_count number;
620
621 cursor c_get_footnotes(cp_asg_act_id number) is
622 select action_information4,
623 action_information5
624 from pay_action_information
625 where action_context_id = cp_asg_act_id
626 and action_information_category = 'CA FOOTNOTES'
627 and action_context_type = 'AAP'
628 and action_information6 = 'RL2'
629 and jurisdiction_code = 'QC'
630 order by action_information4;
631
632 cursor c_get_employee_data(cp_asg_act_id number) is
633 select nvl(action_information1,'NULL'),
634 nvl(action_information2,'NULL'),
635 nvl(action_information3,'NULL'),
636 nvl(action_information4,'NULL'),
637 nvl(action_information5,'NULL'),
638 nvl(action_information6,'NULL'),
639 nvl(action_information7,'NULL'),
640 nvl(action_information8,'NULL'),
641 nvl(action_information9,'NULL'),
642 nvl(action_information10,'NULL'),
643 nvl(action_information11,'NULL'),
644 nvl(action_information12,'NULL'),
645 nvl(action_information13,'NULL'),
646 nvl(action_information14,'NULL'),
647 nvl(action_information15,'NULL'),
648 nvl(action_information16,'NULL'),
649 nvl(action_information17,'NULL'),
650 nvl(action_information18,'NULL'),
651 nvl(action_information19,'NULL'),
652 nvl(action_information20,'NULL'),
653 nvl(action_information21,'NULL'),
654 nvl(action_information22,'NULL'),
655 nvl(action_information23,'NULL'),
656 nvl(action_information24,'NULL'),
657 nvl(action_information25,'NULL'),
658 nvl(action_information26,'NULL'),
659 nvl(action_information27,'NULL'),
660 nvl(action_information28,'NULL'),
661 nvl(action_information29,'NULL'),
662 nvl(action_information30,'NULL')
663 from pay_action_information
664 where action_context_id = cp_asg_act_id
665 and action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
666 and action_context_type = 'AAP'
667 and jurisdiction_code = 'QC';
668
669 cursor c_get_employee_data2(cp_asg_act_id number) is -- For Bug 8921055
670 select nvl(action_information1,'NULL')
671 from pay_action_information
672 where action_context_id = cp_asg_act_id
673 and action_information_category = 'CAEOY RL2 EMPLOYEE INFO2'
674 and action_context_type = 'AAP'
675 and jurisdiction_code = 'QC';
676
677 i number;
678 lv_flag varchar2(2);
679
680 begin
681
682 /* Initialization Process */
683
684 lv_flag := 'N';
685
686 if ltr_amend_arch_data.count > 0 then
687 ltr_amend_arch_data.delete;
688 end if;
689
690 if ltr_yepp_arch_data.count > 0 then
691 ltr_yepp_arch_data.delete;
692 end if;
693
694 if ltr_amend_footnote.count > 0 then
695 ltr_amend_footnote.delete;
696 end if;
697
698 if ltr_yepp_footnote.count > 0 then
699 ltr_yepp_footnote.delete;
700 end if;
701
702
703 /* Populate RL2 Amendment Employee Data for an assignment_action */
704
705 open c_get_employee_data(p_assignment_action_id);
706
707 hr_utility.trace('Populating RL2 Amendment Employee Data ');
708 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
709
710 fetch c_get_employee_data into ltr_amend_arch_data(0);
711 close c_get_employee_data;
712
713 hr_utility.trace('ltr_amend_pre_printed_slipno:'||to_char(l_pre_printed_slip_no));
714
715 /* Populate RL2 YEPP Employee Data for an assignment_action */
716
717 open c_get_employee_data(p_locked_action_id);
718
719 hr_utility.trace('Populating RL2 YEPP Employee Data ');
720 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
721
722 fetch c_get_employee_data into ltr_yepp_arch_data(0);
723 close c_get_employee_data;
724
725 open c_get_employee_data2(p_locked_action_id); -- For Bug 8921055
726
727 hr_utility.trace('Populating RL2 YEPP Employee Data2 ');
728 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
729
730 fetch c_get_employee_data2 into ltr_yepp_pre_printed_slipno;
731 hr_utility.trace('ltr_yepp_pre_printed_slipno :'||to_char(ltr_yepp_pre_printed_slipno));
732 close c_get_employee_data2;
733
734 /* Populate RL2 Amendment Footnotes */
735 open c_get_footnotes(p_assignment_action_id);
736
737 hr_utility.trace('Populating RL2 Amendment Footnote ');
738
739 ln_amend_footnote_count := 0;
740 loop
741 fetch c_get_footnotes into ltr_amend_footnote(ln_amend_footnote_count);
742 exit when c_get_footnotes%NOTFOUND;
743
744 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
745 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
746
747 ln_amend_footnote_count := ln_amend_footnote_count + 1;
748 end loop;
749
750 close c_get_footnotes;
751
752 /* Populate RL2 YEPP Footnotes */
753 open c_get_footnotes(p_locked_action_id);
754
755 ln_yepp_footnote_count := 0;
756 loop
757 fetch c_get_footnotes into ltr_yepp_footnote(ln_yepp_footnote_count);
758 exit when c_get_footnotes%NOTFOUND;
759
760 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
761 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
762
763 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
764 end loop;
765
766 close c_get_footnotes;
767
768 hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Data ');
769
770 if (ltr_yepp_arch_data.count = ltr_amend_arch_data.count) then
771
772 if (ltr_yepp_arch_data.count <> 0) then
773
774 if ((ltr_yepp_arch_data(0).act_info2 <> ltr_amend_arch_data(0).act_info2) or
775 (ltr_yepp_arch_data(0).act_info3 <> ltr_amend_arch_data(0).act_info3) or
776 (ltr_yepp_arch_data(0).act_info4 <> ltr_amend_arch_data(0).act_info4) or
777 (ltr_yepp_arch_data(0).act_info5 <> ltr_amend_arch_data(0).act_info5) or
778 (ltr_yepp_arch_data(0).act_info6 <> ltr_amend_arch_data(0).act_info6) or
779 (ltr_yepp_arch_data(0).act_info7 <> ltr_amend_arch_data(0).act_info7) or
780 (ltr_yepp_arch_data(0).act_info8 <> ltr_amend_arch_data(0).act_info8) or
781 (ltr_yepp_arch_data(0).act_info9 <> ltr_amend_arch_data(0).act_info9) or
782 (ltr_yepp_arch_data(0).act_info10 <> ltr_amend_arch_data(0).act_info10) or
783 (ltr_yepp_arch_data(0).act_info11 <> ltr_amend_arch_data(0).act_info11) or
784 (ltr_yepp_arch_data(0).act_info12 <> ltr_amend_arch_data(0).act_info12) or
785 (ltr_yepp_arch_data(0).act_info13 <> ltr_amend_arch_data(0).act_info13) or
786 (ltr_yepp_arch_data(0).act_info14 <> ltr_amend_arch_data(0).act_info14) or
787 (ltr_yepp_arch_data(0).act_info15 <> ltr_amend_arch_data(0).act_info15) or
788 (ltr_yepp_arch_data(0).act_info16 <> ltr_amend_arch_data(0).act_info16) or
789 (ltr_yepp_arch_data(0).act_info17 <> ltr_amend_arch_data(0).act_info17) or
790 (ltr_yepp_arch_data(0).act_info18 <> ltr_amend_arch_data(0).act_info18) or
791 (ltr_yepp_arch_data(0).act_info19 <> ltr_amend_arch_data(0).act_info19) or
792 (ltr_yepp_arch_data(0).act_info20 <> ltr_amend_arch_data(0).act_info20) or
793 (ltr_yepp_arch_data(0).act_info21 <> ltr_amend_arch_data(0).act_info21) or
794 (ltr_yepp_arch_data(0).act_info22 <> ltr_amend_arch_data(0).act_info22) or
795 (ltr_yepp_arch_data(0).act_info23 <> ltr_amend_arch_data(0).act_info23) or
796 (ltr_yepp_arch_data(0).act_info24 <> ltr_amend_arch_data(0).act_info24) or
797 (ltr_yepp_arch_data(0).act_info25 <> ltr_amend_arch_data(0).act_info25) or
798 (ltr_yepp_arch_data(0).act_info26 <> ltr_amend_arch_data(0).act_info26) or
799 (ltr_yepp_arch_data(0).act_info27 <> ltr_amend_arch_data(0).act_info27) or
800 (ltr_yepp_arch_data(0).act_info28 <> ltr_amend_arch_data(0).act_info28) or
801 (ltr_yepp_arch_data(0).act_info29 <> ltr_amend_arch_data(0).act_info29))or
802 (ltr_yepp_pre_printed_slipno <> l_pre_printed_slip_no) then -- For Bug 8921055
803
804 lv_flag := 'Y';
805 hr_utility.trace('lv_flag has been set to Y for Employee Data');
806 end if;
807
808 end if;
809
810 else
811 lv_flag := 'Y';
812 hr_utility.trace('lv_flag has been set to Y for Employee Data');
813 end if;
814
815
816 /* Compare RL2 Amendment Footnotes and RL2 YEPP Footnotes for an
817 assignment_action */
818
819 hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Footnotes');
820
821 if lv_flag <> 'Y' then
822
823 if ln_yepp_footnote_count <> ln_amend_footnote_count then
824
825 lv_flag := 'Y';
826
827 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
828 (ln_yepp_footnote_count <> 0)) then
829
830 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
831 loop
832 if (ltr_yepp_footnote(i).message = ltr_amend_footnote(i).message) then
833
834 if ((ltr_yepp_footnote(i).value <>
835 ltr_amend_footnote(i).value) or
836 (ltr_yepp_footnote(i).value is null and
837 ltr_amend_footnote(i).value is not null) or
838 (ltr_yepp_footnote(i).value is not null and
839 ltr_amend_footnote(i).value is null)) then
840
841 lv_flag := 'Y';
842 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
843 exit;
844 end if;
845 end if;
846 end loop;
847 end if;
848
849 end if;
850
851 /* If there is no value difference for Entire Employee data then set
852 flag to 'N' */
853
854 if lv_flag <> 'Y' then
855
856 lv_flag := 'N';
857 hr_utility.trace('No value difference for Asg Action: '|| to_char(p_assignment_action_id));
858
859 end if;
860
861 hr_utility.trace('lv_flag :'||lv_flag);
862
863 return lv_flag;
864
865 end compare_archive_data;
866
867
868 /*
869 Name : eoy_action_creation
870 Purpose : This creates the assignment actions for a specific chunk
871 of people to be archived by the RL2 Archiver preprocess.
872 Arguments :
873 Notes :
874 */
875
876 procedure eoy_action_creation(pactid in number,
877 stperson in number,
878 endperson in number,
879 chunk in number) is
880
881
882
883 /* Variables used to hold the select columns from the SQL statement.*/
884
885 l_person_id number;
886 l_assignment_id number;
887 l_tax_unit_id number;
888 l_eoy_tax_unit_id number;
889 l_effective_end_date date;
890 l_object_version_number number;
891 l_some_warning boolean;
892 l_counter number;
893 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
894 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
895 l_user_entity_name varchar2(240);
896
897 /* Variables used to hold the values used as bind variables within the
898 SQL statement. */
899
900 l_bus_group_id number;
901 l_period_start date;
902 l_period_end date;
903
904 /* Variables used to hold the details of the payroll and assignment actions
905 that are created. */
906
907 l_payroll_action_created boolean := false;
908 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
909 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
910 l_archive_item_id number;
911
912 /* Variable holding the balance to be tested. */
913
914 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
915
916 /* Indicator variables used to control how the people are grouped. */
917
918 l_group_by_gre boolean := FALSE;
919 l_group_by_medicare boolean := FALSE;
920
921 /* Indicator variables used to control which contexts are set up for
922 balance. */
923
924 l_tax_unit_context boolean := FALSE;
925 l_jurisdiction_context boolean := FALSE;
926
927 /* Variables used to hold the current values returned within the loop for
928 checking against the new values returned from within the loop on the
929 next iteration. */
930
931 l_prev_person_id per_all_people_f.person_id%type;
932 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
933
934 /* Variable to hold the jurisdiction code used as a context for state
935 reporting. */
936
937 l_jurisdiction_code varchar2(30);
938
939 /* general process variables */
940
941 l_report_type pay_payroll_actions.report_type%type;
942
943 -- Variables declared for bug 10399514
944 l_person_on boolean ;
945 l_report_cat pay_payroll_actions.report_category%type;
946 l_state pay_payroll_actions.report_qualifier%type;
947 l_report_format pay_report_format_mappings_f.report_format%type;
948 -- Variables declared for bug 10399514
949
950 l_province pay_payroll_actions.report_qualifier%type;
951 l_value number;
952 l_effective_date date;
953 l_quarter_start date;
954 l_quarter_end date;
955 l_year_start date;
956 l_year_end date;
957 lockingactid number;
958 l_max_aaid number;
959 l_pre_org_id varchar2(17);
960 l_prev_pre_org_id varchar2(17);
961 l_primary_asg pay_assignment_actions.assignment_id%type;
962 ln_no_gross_earnings number;
963
964
965 /* For Year End Preprocess we have to archive the assignments
966 belonging to a GRE */
967
968 /* For Year End Preprocess we can also archive the assignments
969 belonging to all GREs */
970 /*
971 CURSOR c_eoy_qbin IS
972 SELECT ASG.person_id person_id,
973 ASG.assignment_id assignment_id,
974 ASG.effective_end_date effective_end_date
975 FROM per_all_assignments_f ASG,
976 pay_all_payrolls_f PPY,
977 hr_soft_coding_keyflex SCL
978 WHERE ASG.business_group_id + 0 = l_bus_group_id
979 AND ASG.person_id between stperson and endperson
980 AND ASG.assignment_type = 'E'
981 AND ASG.effective_start_date <= l_period_end
982 AND ASG.effective_end_date >= l_period_start
983 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
984 AND rtrim(ltrim(SCL.segment12)) in
985 (select to_char(hoi.organization_id)
986 from hr_organization_information hoi
987 where hoi.org_information_context = 'Canada Employer Identification'
988 and hoi.org_information2 = l_pre_org_id
989 and hoi.org_information5 = 'T4A/RL2')
990 AND PPY.payroll_id = ASG.payroll_id
991 and exists ( select 'X' from pay_action_contexts pac, ff_contexts fc
992 where pac.assignment_id = asg.assignment_id
993 and pac.context_id = fc.context_id
994 and fc.context_name = 'JURISDICTION_CODE'
995 and pac.context_value = 'QC' )
996 ORDER BY 1, 3 DESC, 2; */
997
998 /*
999 Bug 5202869. For performance issue modified the cursor c_eoy_qbin.
1000 Removed the table per_people_f and also disabled few indexes to make
1001 sure the query takes the correct path. With this change the cost of
1002 the query has increased but the path taken is better.
1003 */
1004
1005 CURSOR c_eoy_qbin IS
1006 SELECT asg.person_id person_id,
1007 asg.assignment_id assignment_id,
1008 asg.effective_end_date effective_end_date
1009 FROM per_all_assignments_f asg,
1010 pay_assignment_actions paa,
1011 pay_payroll_actions ppa
1012 WHERE ppa.effective_date between l_period_start
1013 and l_period_end
1014 AND ppa.action_type in ('R','Q','V','B','I')
1015 AND ppa.business_group_id +0 = l_bus_group_id
1016 AND ppa.payroll_action_id = paa.payroll_action_id
1017 AND paa.tax_unit_id in (select hoi.organization_id
1018 from hr_organization_information hoi
1019 where hoi.org_information_context ||''= 'Canada Employer Identification'
1020 and hoi.org_information2 = l_pre_org_id
1021 and hoi.org_information5 = 'T4A/RL2')
1022 AND paa.assignment_id = asg.assignment_id
1023 AND ppa.business_group_id = asg.business_group_id +0
1024 AND asg.person_id between stperson and endperson
1025 AND asg.assignment_type = 'E'
1026 AND ppa.effective_date between asg.effective_start_date
1027 and asg.effective_end_date
1028 AND EXISTS (select 1
1029 from pay_action_contexts pac,
1030 ff_contexts fc
1031 where pac.assignment_id = paa.assignment_id
1032 and pac.assignment_action_id = paa.assignment_action_id
1033 and pac.context_id = fc.context_id
1034 and fc.context_name || '' = 'JURISDICTION_CODE'
1035 and pac.context_value ||'' = 'QC')
1036 ORDER BY 1, 3 DESC, 2;
1037
1038 -- Added for Bug# 10399514
1039 -- Used when RANGE_PERSON_ID functionality is available
1040
1041 CURSOR c_eoy_qbin_range IS
1042 SELECT asg.person_id person_id,
1043 asg.assignment_id assignment_id,
1044 asg.effective_end_date effective_end_date
1045 FROM per_all_assignments_f asg,
1046 pay_assignment_actions paa,
1047 pay_payroll_actions ppa,
1048 pay_population_ranges ppr
1049 WHERE ppa.effective_date between l_period_start
1050 and l_period_end
1051 AND ppa.action_type in ('R','Q','V','B','I')
1052 AND ppa.business_group_id +0 = l_bus_group_id
1053 AND ppa.payroll_action_id = paa.payroll_action_id
1054 AND paa.tax_unit_id in (select hoi.organization_id
1055 from hr_organization_information hoi
1056 where hoi.org_information_context ||''= 'Canada Employer Identification'
1057 and hoi.org_information2 = l_pre_org_id
1058 and hoi.org_information5 = 'T4A/RL2')
1059 AND paa.assignment_id = asg.assignment_id
1060 AND ppa.business_group_id = asg.business_group_id +0
1061 -- AND asg.person_id between stperson and endperson
1062 AND ppr.payroll_action_id = pactid
1063 AND ppr.chunk_number = chunk
1064 AND ppr.person_id = ASG.person_id
1065 AND asg.assignment_type = 'E'
1066 AND ppa.effective_date between asg.effective_start_date
1067 and asg.effective_end_date
1068 AND EXISTS (select 1
1069 from pay_action_contexts pac,
1070 ff_contexts fc
1071 where pac.assignment_id = paa.assignment_id
1072 and pac.assignment_action_id = paa.assignment_action_id
1073 and pac.context_id = fc.context_id
1074 and fc.context_name ||'' = 'JURISDICTION_CODE'
1075 and pac.context_value ||'' = 'QC')
1076 ORDER BY 1, 3 DESC, 2;
1077
1078 cursor c_all_qbin_gres is
1079 select hoi.organization_id
1080 from hr_organization_information hoi
1081 where hoi.org_information_context = 'Canada Employer Identification'
1082 and hoi.org_information2 = l_pre_org_id
1083 and hoi.org_information5 = 'T4A/RL2';
1084
1085 /* Get the assignment for the given person_id */
1086
1087 CURSOR c_get_asg_id (p_person_id number) IS
1088 SELECT assignment_id
1089 from per_all_assignments_f paf
1090 where person_id = p_person_id
1091 and assignment_type = 'E'
1092 and primary_flag = 'Y'
1093 and paf.effective_start_date <= l_period_end
1094 and paf.effective_end_date >= l_period_start
1095 ORDER BY assignment_id desc;
1096
1097 /* Cursor to get the latest payroll run assignment_action_id
1098 for a person with a given tax_unit_id and for that year.
1099 11510 bug# fix. Changed the cursor to get asgact_id based on
1100 person_id to fix bug#3638928 */
1101
1102 CURSOR c_get_asg_act_id(cp_person_id number,
1103 cp_tax_unit_id number,
1104 cp_period_start date,
1105 cp_period_end date) IS
1106 select paa.assignment_action_id
1107 from pay_assignment_actions paa,
1108 per_all_assignments_f paf,
1109 per_all_people_f ppf,
1110 pay_payroll_actions ppa,
1111 pay_action_classifications pac
1112 where ppf.person_id = cp_person_id
1113 and paf.person_id = ppf.person_id
1114 and paa.assignment_id = paf.assignment_id
1115 and paa.tax_unit_id = cp_tax_unit_id
1116 and ppa.payroll_action_id = paa.payroll_action_id
1117 and ppa.effective_date between cp_period_start and cp_period_end
1118 and ppa.effective_date between ppf.effective_start_date
1119 and ppf.effective_end_date
1120 and ppa.effective_date between paf.effective_start_date
1121 and paf.effective_end_date
1122 and ppa.action_type = pac.action_type
1123 and pac.classification_name = 'SEQUENCED'
1124 order by paa.action_sequence desc;
1125
1126
1127 begin
1128
1129 /* Get the report type, report qualifier, business group id and the
1130 gre for which the archiving has to be done */
1131
1132 hr_utility.trace('getting report type ');
1133
1134 select effective_date,
1135 report_type,
1136 -- Added for bug 10399514
1137 report_qualifier,
1138 report_category,
1139 -- Added for bug 10399514
1140 business_group_id,
1141 pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
1142 legislative_parameters)
1143 into l_effective_date,
1144 l_report_type,
1145 -- Added for bug 10399514
1146 l_state,
1147 l_report_cat,
1148 -- Added for bug 10399514
1149 l_bus_group_id,
1150 l_pre_org_id
1151 from pay_payroll_actions
1152 where payroll_action_id = pactid;
1153
1154 hr_utility.trace('getting dates');
1155
1156 get_dates(l_report_type,
1157 l_effective_date,
1158 l_period_end,
1159 l_quarter_start,
1160 l_quarter_end,
1161 l_year_start,
1162 l_year_end);
1163
1164 hr_utility.trace('getting selection information');
1165 hr_utility.trace('report type '|| l_report_type);
1166 hr_utility.trace('report qualifier '|| l_state);
1167 hr_utility.trace('report category '|| l_report_cat);
1168 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1172
1173 get_selection_information
1174 (l_report_type,
1175 l_quarter_start,
1176 l_quarter_end,
1177 l_year_start,
1178 l_year_end,
1179 l_period_start,
1180 l_period_end,
1181 l_defined_balance_id,
1182 l_group_by_gre,
1183 l_group_by_medicare,
1184 l_tax_unit_context,
1185 l_jurisdiction_context);
1186
1187 hr_utility.trace('Out of get selection information');
1188
1189 -- Code modification for bug 10399514 starts here
1190 /* Initializing variable */
1191 l_person_on := FALSE ;
1192
1193 Begin
1194 select report_format
1195 into l_report_format
1196 from pay_report_format_mappings_f
1197 where report_type = l_report_type
1198 and report_qualifier = l_state
1199 and report_category = l_report_cat ;
1200 Exception
1201 When Others Then
1202 l_report_format := Null ;
1203 End ;
1204
1205 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
1206 p_report_format => l_report_format,
1207 p_report_qualifier => l_state,
1208 p_report_category => l_report_cat) ;
1209
1210 -- open c_eoy_qbin;
1211
1212 if l_person_on then
1213 hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1214 OPEN c_eoy_qbin_range ;
1215 else
1216 hr_utility.trace('opening c_eoy_qbin CURSOR');
1217 OPEN c_eoy_qbin;
1218 end if ;
1219
1220 -- Code modification for bug 10399514 ends here
1221
1222 /* Loop for all rows returned for SQL statement. */
1223
1224 hr_utility.trace('Entering loop');
1225
1226 loop
1227
1228 -- Code modification for bug 10399514 starts here
1229 if l_person_on then
1230 hr_utility.trace('fetching from c_eoy_qbin_range CURSOR');
1231 fetch c_eoy_qbin_range
1232 into l_person_id,
1233 l_assignment_id,
1234 l_effective_end_date;
1235 exit when c_eoy_qbin_range%NOTFOUND;
1236 else
1237 hr_utility.trace('fetching from c_eoy_qbin CURSOR');
1238 fetch c_eoy_qbin into l_person_id,
1239 l_assignment_id,
1240 l_effective_end_date;
1241
1242 exit when c_eoy_qbin%NOTFOUND;
1243 end if ;
1244 -- Code modification for bug 10399514 ends here
1245
1246
1247 /* If the new row is the same as the previous row according to the way
1248 the rows are grouped then discard the row ie. grouping by Prov Reporting
1249 Est requires a single row for each person / PRE combination. */
1250
1251 hr_utility.trace('Prov Reporting Est is '
1252 || l_pre_org_id);
1253 hr_utility.trace('previous Prov Reporting Est is '||
1254 l_prev_pre_org_id);
1255 hr_utility.trace('person_id is '||
1256 to_char(l_person_id));
1257 hr_utility.trace('previous person_id is '||
1258 to_char(l_prev_person_id));
1259
1260 if (l_person_id = l_prev_person_id and
1261 l_pre_org_id = l_prev_pre_org_id) then
1262
1263 hr_utility.trace('Not creating assignment action');
1264
1265 else
1266 /* Check whether the person has 0 payment or not */
1267
1268 l_value := 0;
1269 ln_no_gross_earnings := 0;
1270
1271 open c_all_qbin_gres;
1272 loop
1273 fetch c_all_qbin_gres into l_tax_unit_id;
1274 exit when c_all_qbin_gres%NOTFOUND;
1275
1276 /* select the maximum assignment action id. Fix for bug#3638928 */
1277
1278 begin
1279
1280 open c_get_asg_act_id(l_person_id,l_tax_unit_id,
1281 l_period_start,l_period_end);
1282 fetch c_get_asg_act_id into l_max_aaid;
1283
1284 if c_get_asg_act_id%NOTFOUND then
1285 pay_core_utils.push_message(801,
1286 'PAY_74038_EOY_EXCP_NO_PAYROLL','A');
1287 pay_core_utils.push_token('person','Person id: '
1288 ||to_char(l_person_id));
1289 pay_core_utils.push_token('reporting_year','Reporting Year: '
1290 ||to_char(l_effective_date,'YYYY'));
1291
1292 l_max_aaid := -9999;
1293 end if;
1294 close c_get_asg_act_id;
1295 end; /* end for select of max assignment action id */
1296
1297 if l_max_aaid <> -9999 then
1298 l_value := l_value +
1299 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1300 ('Gross Earnings',
1301 'YTD' ,
1302 l_max_aaid,
1303 l_assignment_id ,
1304 NULL,
1305 'PER' ,
1306 l_tax_unit_id,
1307 l_bus_group_id,
1308 'QC'
1309 ),0) ;
1310
1311 ln_no_gross_earnings := ln_no_gross_earnings +
1312 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1313 ('RL2 No Gross Earnings',
1314 'YTD' ,
1315 l_max_aaid,
1316 l_assignment_id ,
1317 NULL,
1318 'PER' ,
1319 l_tax_unit_id,
1320 l_bus_group_id,
1321 'QC'
1322 ),0) ;
1323 end if; /* end l_max_id <> -9999 */
1324 end loop;
1325 close c_all_qbin_gres;
1326 /* end of checking whether the person has 0 payment */
1327
1328 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1329 hr_utility.trace('person is '|| to_char(l_person_id));
1330 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1331
1332
1333 /* Have a new unique row according to the way the rows are grouped.
1334 The inclusion of the person is dependent on having a non zero
1335 balance.
1336 If the balance is non zero then an assignment action is created to
1337 indicate their inclusion in the magnetic tape report. */
1338
1339 /* Set up the context of tax unit id */
1340
1341 hr_utility.trace('Setting context');
1342
1343 if ((l_value <> 0) or (ln_no_gross_earnings <> 0)) then
1344 /* Get the primary assignment */
1345 open c_get_asg_id(l_person_id);
1346 fetch c_get_asg_id into l_primary_asg;
1347
1348 if c_get_asg_id%NOTFOUND then
1349 close c_get_asg_id;
1350 pay_core_utils.push_message(800,'HR_74004_ASSIGNMENT_ABSENT','A');
1351 raise hr_utility.hr_error;
1352 else
1353 close c_get_asg_id;
1354 end if;
1355
1356 /* Create the assignment action to represnt the person / tax unit
1357 combination. */
1358
1359 select pay_assignment_actions_s.nextval
1360 into lockingactid
1361 from dual;
1362
1363 /* Insert into pay_assignment_actions. */
1364
1365 hr_utility.trace('creating assignment_action');
1366
1367 /* Passing tax unit id as null */
1368
1369 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1370 pactid,chunk,null);
1371
1372 /* Update the serial number column with the person id
1373 so that the mag routine and the RL2 view will not have
1374 to do an additional checking against the assignment
1375 table
1376 */
1377
1378 hr_utility.trace('updating assignment_action' || to_char(lockingactid));
1379
1380 update pay_assignment_actions aa
1381 set aa.serial_number = to_char(l_person_id)
1382 where aa.assignment_action_id = lockingactid;
1383
1384 end if; /* end if l_value <> 0 or ln_no_gross_earnings <> 0 */
1385
1386 end if; /* end if l_person_id = l_prev_person_id */
1387
1388 /* Record the current values for the next time around the loop. */
1389
1390 l_prev_person_id := l_person_id;
1391 l_prev_pre_org_id := l_pre_org_id;
1392
1393 end loop;
1394
1395 hr_utility.trace('Action creation done');
1396
1397 -- Code modification for bug 10399514 starts here
1398 if l_person_on then
1399 hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1400 close c_eoy_qbin_range;
1401 else
1402 hr_utility.trace('closing c_eoy_qbin CURSOR');
1403 close c_eoy_qbin;
1404 end if ;
1405 -- Code modification for bug 10399514 ends here
1406
1407 end eoy_action_creation;
1408
1409
1410 /* Name : eoy_archive_gre_data
1411 Purpose : This performs the CA specific employer data archiving.
1412 Arguments :
1413 Notes :
1414 */
1415
1416 PROCEDURE eoy_archive_gre_data(p_payroll_action_id in number,
1417 p_pre_org_id in varchar2)
1418 IS
1419
1420 l_user_entity_id number;
1421 l_taxunit_context_id number;
1422 l_jursd_context_id number;
1423 l_value varchar2(240);
1424 l_sit_uid number;
1425 l_sui_uid number;
1426 l_fips_uid number;
1427 l_seq_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1428 l_context_id_tab pay_ca_eoy_rl2_archive.number_data_type_table;
1429 l_context_val_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1430 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1431 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
1432 l_arch_gre_step number := 0;
1433 l_archive_item_id number;
1434 l_town_or_city varchar2(240);
1435 l_province_code varchar2(240);
1436 l_postal_code varchar2(240);
1437 l_organization_id_of_qin number;
1438 l_transmitter_org_id number;
1439 l_country_code varchar2(240);
1440 l_transmitter_name varchar2(240);
1441 l_Transmitter_Type_Indicator varchar2(240);
1442 l_transmitter_gre_ind varchar2(240);
1443 l_Transmitter_number varchar2(240);
1444 l_transmitter_addr_line_1 varchar2(240);
1445 l_transmitter_addr_line_2 varchar2(240);
1446 l_transmitter_addr_line_3 varchar2(240);
1447 l_transmitter_city varchar2(240);
1448 l_transmitter_province varchar2(240);
1449 l_transmitter_postal_code varchar2(240);
1450 l_transmitter_country varchar2(240);
1451 l_rl_data_type varchar2(240);
1452 l_rl_package_type varchar2(240);
1453 l_rl_source_of_slips varchar2(240);
1454 l_technical_contact_name varchar2(240);
1455 l_technical_contact_phone varchar2(240);
1456 l_technical_contact_area_code varchar2(240);
1457 l_technical_contact_extension varchar2(240);
1458 l_technical_contact_language varchar2(240);
1459 l_accounting_contact_name varchar2(240);
1460 l_accounting_contact_phone varchar2(240);
1461 l_accounting_contact_area_code varchar2(240);
1462 l_accounting_contact_extension varchar2(240);
1463 l_accounting_contact_language varchar2(240);
1464 l_proprietor_sin varchar2(240);
1465 l_name varchar2(240);
1466 l_org_name varchar2(240);
1467 l_employer_ein varchar2(240);
1468 l_address_line_1 varchar2(240);
1469 l_address_line_2 varchar2(240);
1470 l_address_line_3 varchar2(240);
1471 l_counter number := 0;
1472 l_object_version_number number;
1473 l_business_group_id number;
1474 l_some_warning boolean;
1475 l_step number := 0;
1476 l_taxation_year varchar2(4);
1477 l_rl2_last_slip_number number ;
1478 l_employer_info_found varchar2(1);
1479 l_max_slip_number varchar2(80);
1480 l_effective_date date;
1481
1482 ln_index number;
1483 ln_index2 number;
1484
1485 l_action_information_id_1 NUMBER ;
1486 l_object_version_number_1 NUMBER ;
1487
1488 cursor employer_info is
1489 select target1.organization_id,
1490 target2.name,
1491 target2.business_group_id,
1492 target1.ORG_INFORMATION2 Prov_Identi_Number,
1493 target1.ORG_INFORMATION7 Type_of_Transmitter,
1494 target1.ORG_INFORMATION5 Transmitter_Number,
1495 target1.ORG_INFORMATION4 Type_of_Data,
1496 target1.ORG_INFORMATION6 Type_of_Package,
1497 target1.ORG_INFORMATION8 Source_of_RL_slips_used,
1498 target1.ORG_INFORMATION9 Tech_Res_Person_Name,
1499 target1.ORG_INFORMATION11 Tech_Res_Phone,
1500 target1.ORG_INFORMATION10 Tech_Res_Area_Code,
1501 target1.ORG_INFORMATION12 Tech_Res_Extension,
1502 decode(target1.ORG_INFORMATION13,'E','A',
1503 target1.ORG_INFORMATION13) Tech_Res_Language,
1504 target1.ORG_INFORMATION14 Acct_Res_Person_Name,
1505 target1.ORG_INFORMATION16 Acct_Res_Phone,
1506 target1.ORG_INFORMATION15 Acct_Res_Area_Code,
1507 target1.ORG_INFORMATION17 Acct_Res_Extension,
1508 decode(target1.ORG_INFORMATION19,'E','A',
1509 target1.ORG_INFORMATION19) Acct_Res_Language,
1510 substr(target1.ORG_INFORMATION18,1,8) RL2_Slip_Number,
1511 decode(target1.org_information3,'Y',target1.organization_id,
1512 to_number(target1.ORG_INFORMATION20)),
1513 target1.ORG_INFORMATION3
1514 from hr_organization_information target1,
1515 hr_all_organization_units target2
1516 where target1.organization_id = to_number(p_pre_org_id)
1517 and target2.business_group_id = l_business_group_id
1518 and target2.organization_id = target1.organization_id
1519 and target1.org_information_context = 'Prov Reporting Est'
1520 and target1.org_information4 = 'P02';
1521
1522 /* payroll action level database items */
1523
1524 BEGIN
1525
1526 /* hr_utility.trace_on('Y','RL2'); */
1527
1528 initialization_process('PRE_DATA');
1529
1530 select to_char(effective_date,'YYYY'),business_group_id,effective_date
1531 into l_taxation_year,l_business_group_id,l_effective_date
1532 from pay_payroll_actions
1533 where payroll_action_id = p_payroll_action_id;
1534
1535 open employer_info;
1536
1537 fetch employer_info
1538 into l_organization_id_of_qin,
1539 l_name, l_business_group_id,
1540 l_employer_ein,
1541 l_Transmitter_Type_Indicator, l_transmitter_number,
1542 l_rl_data_type, l_rl_package_type,
1543 l_rl_source_of_slips,
1544 l_technical_contact_name, l_technical_contact_phone,
1545 l_technical_contact_area_code, l_technical_contact_extension,
1546 l_technical_contact_language, l_accounting_contact_name,
1547 l_accounting_contact_phone ,
1548 l_accounting_contact_area_code ,
1549 l_accounting_contact_extension ,
1550 l_accounting_contact_language,
1551 l_rl2_last_slip_number,
1552 l_transmitter_org_id,
1553 l_transmitter_gre_ind;
1554
1555 l_arch_gre_step := 40;
1556 hr_utility.trace('eoy_archive_gre_data 1');
1557
1558 if employer_info%FOUND then
1559
1560 close employer_info;
1561 hr_utility.trace('got employer data ');
1562
1563 l_employer_info_found := 'Y';
1564
1565 begin
1566 select
1567 L.ADDRESS_LINE_1
1568 , L.ADDRESS_LINE_2
1569 , L.ADDRESS_LINE_3
1570 , L.TOWN_OR_CITY
1571 , DECODE(L.STYLE ,'US',L.REGION_2,'CA',L.REGION_1,'CA_GLB',L.REGION_1,' ')
1572 , replace(L.POSTAL_CODE,' ')
1573 , L.COUNTRY
1574 , O.name
1575 into
1576 l_address_line_1
1577 , l_address_line_2
1578 , l_address_line_3
1579 , l_town_or_city
1580 , l_province_code
1581 , l_postal_code
1582 , l_country_code
1583 , l_org_name
1584 from hr_all_organization_units O,
1585 hr_locations_all L
1586 where L.LOCATION_ID = O.LOCATION_ID
1587 AND O.ORGANIZATION_ID = l_organization_id_of_qin;
1588
1589 /* Find out the highest slip number for that transmitter */
1590
1591 if l_transmitter_gre_ind = 'Y' then
1592
1593 l_transmitter_org_id := l_organization_id_of_qin;
1594
1595 l_transmitter_name := l_org_name;
1596 l_transmitter_addr_line_1 := l_address_line_1;
1597 l_transmitter_addr_line_2 := l_address_line_2;
1598 l_transmitter_addr_line_3 := l_address_line_3;
1599 l_transmitter_city := l_town_or_city;
1600 l_transmitter_province := l_province_code;
1601 l_transmitter_postal_code := l_postal_code;
1602 l_transmitter_country := l_country_code;
1603
1604 end if;
1605
1606 exception when no_data_found then
1607 l_transmitter_name := NULL;
1608 l_address_line_1 := NULL;
1609 l_address_line_2 := NULL;
1610 l_address_line_3 := NULL;
1611 l_town_or_city := NULL;
1612 l_province_code := NULL;
1613 l_postal_code := NULL;
1614 l_country_code := NULL;
1615 end;
1616
1617 else
1618 l_employer_ein := 'TEST_DATA';
1619 l_address_line_1 := 'TEST_DATA';
1620 l_address_line_2 := 'TEST_DATA';
1621 l_address_line_3 := 'TEST_DATA';
1622 l_town_or_city := 'TEST_DATA';
1623 l_province_code := 'TEST_DATA';
1624 l_postal_code := 'TEST_DATA';
1625 l_country_code := 'TEST_DATA';
1626 l_name := 'TEST_DATA';
1627 l_transmitter_name := 'TEST_DATA';
1628 l_transmitter_addr_line_1 := 'TEST_DATA';
1629 l_transmitter_addr_line_2 := 'TEST_DATA';
1630 l_transmitter_addr_line_3 := 'TEST_DATA';
1631 l_transmitter_city := 'TEST_DATA';
1632 l_transmitter_province := 'TEST_DATA';
1633 l_transmitter_postal_code := 'TEST_DATA';
1634 l_transmitter_country := 'TEST_DATA';
1635 l_technical_contact_name := 'TEST_DATA';
1636 l_technical_contact_phone := 'TEST_DATA';
1637 l_technical_contact_language := 'TEST_DATA';
1638 l_accounting_contact_name := 'TEST_DATA';
1639 l_accounting_contact_phone := 'TEST_DATA';
1640 l_accounting_contact_language:= 'TEST_DATA';
1641 l_proprietor_sin := 'TEST_DATA';
1642 l_arch_gre_step := 424;
1643
1644 hr_utility.trace('eoy_archive_gre_data 2');
1645 close employer_info;
1646
1647 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1648 pay_core_utils.push_token('orgind','Prov Reporting Est: '
1649 ||p_pre_org_id);
1650 hr_utility.raise_error;
1651 end if; /* end if for employer_info%FOUND */
1652
1653
1654 /* archive Releve 2 data */
1655
1656 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.count;
1657 ln_index2 := pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count;
1658
1659 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).action_info_category
1660 := 'CAEOY TRANSMITTER INFO';
1661
1662 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).jurisdiction_code
1663 := null;
1664
1665 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info1
1666 := 'RL2';
1667
1668 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info2
1669 := l_employer_ein;
1670
1671 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info3
1672 := l_transmitter_number;
1673
1674 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info4
1675 := l_rl_data_type;
1676
1677 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info5
1678 := l_rl_package_type;
1679
1680 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info6
1681 := l_Transmitter_Type_Indicator;
1682
1683 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info7
1684 := l_rl_source_of_slips;
1685
1686 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info8
1687 := l_taxation_year;
1688
1689 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info9
1690 := l_transmitter_country;
1691
1692 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info10
1693 := l_transmitter_name;
1694
1695 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info11
1696 := l_transmitter_addr_line_1;
1697
1698 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info12
1699 := l_transmitter_addr_line_2;
1700
1701 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info13
1702 := l_transmitter_addr_line_3;
1703
1704 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info14
1705 := l_transmitter_city;
1706
1707 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info15
1708 := l_transmitter_province;
1709
1710 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info16
1711 := l_transmitter_postal_code;
1712
1713 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info17
1714 := l_technical_contact_name;
1715
1716 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info18
1717 := l_technical_contact_area_code;
1718
1719 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info19
1720 := l_technical_contact_phone;
1721
1722 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info20
1723 := l_technical_contact_extension;
1724
1725 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info21
1726 := l_technical_contact_language;
1727
1728 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info22
1729 := l_accounting_contact_name;
1730
1731 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info23
1732 := l_accounting_contact_area_code;
1733
1734 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info24
1735 := l_accounting_contact_phone ;
1736
1737 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info25
1738 := l_accounting_contact_extension;
1739
1740 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info26
1741 := l_accounting_contact_language;
1742
1743 pay_ca_eoy_rl2_archive.ltr_ppa_arch_data(ln_index).act_info27
1744 := p_pre_org_id;
1745
1746 /* Archive Employer Data */
1747 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).action_info_category
1748 := 'CAEOY EMPLOYER INFO';
1749
1750 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).jurisdiction_code
1751 := null;
1752
1753 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info1
1754 := 'RL2';
1755
1756 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info2
1757 := l_name;
1758
1759 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info3
1760 := l_address_line_1;
1761
1762 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info4
1763 := l_address_line_2;
1764
1765 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info5
1766 := l_address_line_3;
1767
1768 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info6
1769 := l_town_or_city;
1770
1771 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info7
1772 := l_province_code;
1773
1774 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info8
1775 := l_country_code;
1776
1777 pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data(ln_index2).act_info9
1778 := l_postal_code;
1779
1780 l_arch_gre_step := 50;
1781 l_arch_gre_step := 51;
1782
1783 /* Other employer level data for RL-2 total is to be discussed ,
1784 whether it is for Quebec only or not */
1785
1786 g_archive_flag := 'Y';
1787
1788 /* Inserting rows into pay_action_information table
1789 Transmitter PRE Information */
1790
1791 if ltr_ppa_arch_data.count >0 then
1792 hr_utility.trace('Archiving PRE Data');
1793 archive_data_records(
1794 p_action_context_id => p_payroll_action_id
1795 ,p_action_context_type=> 'PA'
1796 ,p_assignment_id => null
1797 ,p_tax_unit_id => null
1798 ,p_effective_date => l_effective_date
1799 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_data);
1800 end if;
1801
1802 /* Inserting rows into pay_action_information table
1803 Employer Information (Could be just a PRE or Transmitter PRE) */
1804
1805 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count >0 then
1806 hr_utility.trace('Archiving Employer Data');
1807 archive_data_records(
1808 p_action_context_id => p_payroll_action_id
1809 ,p_action_context_type=> 'PA'
1810 ,p_assignment_id => null
1811 ,p_tax_unit_id => null
1812 ,p_effective_date => l_effective_date
1813 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data);
1814 end if;
1815
1816 EXCEPTION
1817 when others then
1818 g_archive_flag := 'N';
1819 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1820 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1821 if l_arch_gre_step = 40 then
1822 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1823 pay_core_utils.push_token('orgind','Prov Reporting Est: '
1824 ||p_pre_org_id);
1825 end if;
1826 hr_utility.raise_error;
1827
1828 END eoy_archive_gre_data;
1829
1830 /* Name : chk_gre_archive
1831 Purpose : Function to check if the employer level data has been archived
1832 or not.
1833 Arguments :
1834 Notes :
1835 */
1836
1837 function chk_gre_archive (p_payroll_action_id number) return boolean is
1838
1839 l_flag varchar2(1);
1840
1841 cursor c_chk_payroll_action is
1842 select 'Y'
1843 from pay_action_information
1844 where action_information1 = 'RL2'
1845 and action_context_id = p_payroll_action_id;
1846
1847 begin
1848
1849 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1850
1851 if g_archive_flag = 'Y' then
1852 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1853 return (TRUE);
1854 else
1855
1856 hr_utility.trace('chk_gre_archive - opening cursor');
1857
1858 open c_chk_payroll_action;
1859 fetch c_chk_payroll_action into l_flag;
1860 if c_chk_payroll_action%FOUND then
1861 hr_utility.trace('chk_gre_archive - found in cursor');
1862 g_archive_flag := 'Y';
1863 else
1864 hr_utility.trace('chk_gre_archive - not found in cursor');
1865 g_archive_flag := 'N';
1866 end if;
1867
1868 hr_utility.trace('chk_gre_archive - closing cursor');
1869 close c_chk_payroll_action;
1870 if g_archive_flag = 'Y' then
1871 hr_utility.trace('chk_gre_archive - returning true');
1872 return (TRUE);
1873 else
1874 hr_utility.trace('chk_gre_archive - returning false');
1875 return(FALSE);
1876 end if;
1877 end if;
1878 end chk_gre_archive;
1879
1880 /* Name : eoy_archinit
1881 Purpose : This performs the context initialization for the year end
1882 pre-process.
1883 Arguments :
1884 Notes :
1885 */
1886
1887
1888 procedure eoy_archinit(p_payroll_action_id in number) is
1889 l_jurisdiction_code VARCHAR2(30);
1890 l_tax_unit_id NUMBER(15);
1891 l_archive boolean:= FALSE;
1892 l_step number := 0;
1893
1894 cursor c_get_min_chunk is
1895 select min(paa.chunk_number)
1896 from pay_assignment_actions paa
1897 where paa.payroll_action_id = p_payroll_action_id;
1898 begin
1899 open c_get_min_chunk;
1900 fetch c_get_min_chunk into g_min_chunk;
1901 l_step := 1;
1902 if c_get_min_chunk%NOTFOUND then
1903 g_min_chunk := -1;
1904 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1905 end if;
1906 close c_get_min_chunk;
1907
1908 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1909 l_step := 2;
1910 l_archive := chk_gre_archive(p_payroll_action_id);
1911
1912 l_step := 3;
1913 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1914 exception
1915 when others then
1916 raise_application_error(-20001,'eoy_archinit at '
1917 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1918 end eoy_archinit;
1919
1920
1921 /* Name : getnext_seq_num
1922 Purpose : Calculates and inserts check digit to PDF sequence number - 8500723
1923 */
1924
1925 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
1926 RETURN NUMBER IS
1927 l_seq_number number;
1928 l_check_number number;
1929 BEGIN
1930
1931 l_check_number := mod(p_curr_seq,7);
1932 hr_utility.trace('l_check_number ='|| l_check_number);
1933 l_seq_number := (p_curr_seq * 10) + l_check_number;
1934 hr_utility.trace('l_seq_number ='|| l_seq_number);
1935 return l_seq_number;
1936 END;
1937
1938 /* Function to generate sequence number for RL2 PDFs - Bug 6768167*/
1939
1940 function gen_rl2_pdf_seq(p_aaid number,
1941 p_reporting_year varchar2,
1942 called_from varchar2)
1943 return varchar2 is
1944
1945 cursor c_get_arch_seq_num(cp_aaid number) is
1946 select action_information3,
1947 ACTION_INFORMATION_ID,
1948 OBJECT_VERSION_NUMBER
1949 from pay_action_information
1950 where action_information_category = 'CAEOY RL2 EMPLOYEE INFO2'
1951 and action_context_id = cp_aaid;
1952
1953 cursor c_get_seq_num_range(cp_run_year varchar2) is
1954 select ROW_LOW_RANGE_OR_NAME range_start,
1955 ROW_HIGH_RANGE range_end
1956 from pay_user_tables put,
1957 pay_user_rows_f pur
1958 where pur.USER_TABLE_ID=put.USER_TABLE_ID
1959 and put.USER_TABLE_NAME = 'RL2 PDF Sequence Range'
1960 and fnd_date.string_to_date('31/12/'||cp_run_year,'DD/MM/YYYY')
1961 between pur.EFFECTIVE_START_DATE and pur.EFFECTIVE_END_DATE;
1962
1963 l_final_seq_num varchar2(240); -- Bug 14701748 - Increased size
1964 l_start_seq_num varchar2(25);
1965 l_end_seq_num varchar2(25);
1966 l_seq_offset number;
1967 l_obj_ver number;
1968 l_act_info_id number;
1969
1970 begin
1971
1972 hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 10');
1973
1974 if (called_from = 'XMLPROC') then
1975 hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 20');
1976 open c_get_arch_seq_num(p_aaid);
1977 fetch c_get_arch_seq_num into l_final_seq_num,l_act_info_id,l_obj_ver;
1978 close c_get_arch_seq_num;
1979
1980 if (l_final_seq_num is not null) then
1981 return l_final_seq_num;
1982 end if;
1983
1984 end if;
1985
1986 l_start_seq_num := null;
1987 open c_get_seq_num_range(p_reporting_year);
1988 fetch c_get_seq_num_range into l_start_seq_num,l_end_seq_num;
1989 close c_get_seq_num_range;
1990
1991 if (l_start_seq_num is not null) then
1992 hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 30');
1993
1994 select PAY_CA_RL2_PDF_SEQ_COUNT_S.nextval into l_seq_offset
1995 from dual;
1996 l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset); --Bug 8500723
1997 elsif (called_from ='ARCHIVER') then
1998 l_final_seq_num := null;
1999 end if;
2000
2001 if (called_from ='XMLPROC') then
2002 hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 40');
2003
2004 pay_action_information_api.update_action_information(p_action_information_id=>l_act_info_id,
2005 p_object_version_number=>l_obj_ver,
2006 p_action_information3=>l_final_seq_num);
2007
2008 end if;
2009
2010 return l_final_seq_num;
2011
2012 end gen_rl2_pdf_seq;
2013
2014 /* RL2 Slip number generation */
2015
2016
2017 FUNCTION gen_rl2_slip_no(p_transmitter_id number,p_asgid pay_assignment_actions.assignment_id%type)
2018 return varchar2 is
2019
2020 l_rl2_slip_number_last_digit number;
2021 l_rl2_curr_slip_number number;
2022 -- added for bug 11694701
2023 l_rl2_starting_slip_num number;
2024 l_rl2_ending_slip_num number;
2025 -- commented by sneelapa and redefined for bug 11654691
2026 -- l_rl2_slip_number number;
2027 l_rl2_slip_number varchar2(20);
2028 l_rl2_last_slip_number number;
2029
2030 begin
2031 hr_utility.trace('p_transmitter_id : ' || to_char(p_transmitter_id));
2032
2033 begin
2034 select to_number(target.ORG_INFORMATION1),to_number(target.ORG_INFORMATION2)
2035 into l_rl2_starting_slip_num,l_rl2_ending_slip_num
2036 from hr_organization_information target
2037 where target.organization_id = p_transmitter_id
2038 and target.org_information_context = 'Prov Reporting Est3'
2039 and exists (select 'X' from hr_organization_information target1
2040 where target1.organization_id = p_transmitter_id
2041 and target1.org_information_context = 'Prov Reporting Est'
2042 and target1.ORG_INFORMATION3 = 'Y');
2043 exception -- handling this exception for bug 12996280
2044 when no_data_found then
2045 null;
2046 end;
2047
2048 if l_rl2_starting_slip_num is NULL then
2049 hr_utility.trace('Warning: Starting Slip number is NULL');
2050 end if;
2051
2052 if l_rl2_ending_slip_num is NULL then
2053 hr_utility.trace('Warning: Ending Slip number is NULL');
2054 end if;
2055 hr_utility.trace('Starting Slip number: '||l_rl2_starting_slip_num);
2056 hr_utility.trace('Ending Slip number: '||l_rl2_ending_slip_num);
2057
2058 select l_rl2_starting_slip_num + pay_ca_eoy_rl2_s.nextval - 1
2059 into l_rl2_curr_slip_number from dual;
2060 hr_utility.trace('l_rl2_curr_slip_number: '||l_rl2_curr_slip_number);
2061
2062 if l_rl2_ending_slip_num is not NULL and l_rl2_curr_slip_number is not NULL
2063 and l_rl2_curr_slip_number > l_rl2_ending_slip_num then
2064 hr_utility.trace('Warning: Generated Slip number '||l_rl2_curr_slip_number
2065 ||' is greater than Ending slip number '||l_rl2_ending_slip_num
2066 ||' for assignment id '||to_char(p_asgid));
2067 end if;
2068 -- Ended changes for bug 11694701
2069
2070 l_rl2_slip_number_last_digit := mod(l_rl2_curr_slip_number,7);
2071
2072 hr_utility.trace('l_rl2_curr_slip_number : '||l_rl2_curr_slip_number);
2073 hr_utility.trace('l_rl2_slip_number_last_digit : '||
2074 l_rl2_slip_number_last_digit);
2075
2076 /* Commented and modified as below by sneelapa, bug 11654691
2077
2078 l_rl2_slip_number := (l_rl2_curr_slip_number)||
2079 l_rl2_slip_number_last_digit;
2080 */
2081
2082 l_rl2_slip_number :=
2083 lpad ((l_rl2_curr_slip_number)||l_rl2_slip_number_last_digit, 9, 0);
2084
2085 hr_utility.trace('l_rl2_slip_number : ' || l_rl2_slip_number);
2086 return l_rl2_slip_number;
2087
2088 exception
2089 when no_data_found then
2090 hr_utility.trace('Problem in generation of RL2 Slip Number');
2091 hr_utility.trace('l_transmitter_id :'||to_char(p_transmitter_id));
2092 /* need a pop-message if rl2 slip number not generated */
2093 l_rl2_slip_number := 0;
2094
2095 end gen_rl2_slip_no;
2096
2097 PROCEDURE eoy_archive_further_info(p_assactid in number,
2098 p_effective_date in date,
2099 p_source_of_income_descr in varchar2 default NULL,
2100 p_negative_balance_exists in out NOCOPY varchar2,
2101 p_temp_fi in out NOCOPY number ) IS
2102
2103 l_aaid pay_assignment_actions.assignment_action_id%type;
2104 l_asgid pay_assignment_actions.assignment_id%type;
2105 l_date_earned date;
2106 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2107 l_reporting_type varchar2(240);
2108 l_business_group_id number;
2109 l_year_start date;
2110 l_year_end date;
2111 l_count number := 0;
2112 l_jurisdiction varchar2(11);
2113
2114 l_step number := 0;
2115 l_chunk number;
2116 l_payroll_action_id number;
2117 l_pre_org_id varchar2(80);
2118
2119 l_negative_balance_exists varchar2(2);
2120 result number;
2121 l_rl2_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2122 lv_serial_number varchar2(30);
2123 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2124 l_balance_type_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2125 l_footnote_balance_type_tab varchar2(80);
2126 ln_further_index NUMBER;
2127
2128
2129 /* Cursor to get the all gre values that are under the archived
2130 transmitter PRE */
2131 cursor c_all_gres(asgactid number) is
2132 select hoi.organization_id
2133 from pay_action_information pac,
2134 pay_assignment_actions paa,
2135 hr_organization_information hoi
2136 where paa.assignment_action_id = asgactid
2137 and pac.action_context_id = paa.payroll_action_id
2138 and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2139 and pac.action_information1 = 'RL2'
2140 and pac.action_information27 = hoi.org_information2
2141 and hoi.org_information_context = 'Canada Employer Identification'
2142 order by 1;
2143
2144
2145 /* Query to get the max asg_act_id for a payroll run in a given year
2146 with tax_unit_id, asg_id and effective_date as parameters. Changed
2147 cursor to get max asgact_id based on person_id, fix for bug#3638928 */
2148 CURSOR c_get_max_asg_act_id(cp_person_id number,
2149 cp_tax_unit_id number,
2150 cp_period_start date,
2151 cp_period_end date) IS
2152 select paa.assignment_action_id
2153 from pay_assignment_actions paa,
2154 per_all_assignments_f paf,
2155 per_all_people_f ppf,
2156 pay_payroll_actions ppa,
2157 pay_action_classifications pac,
2158 pay_action_contexts pac1,
2159 ff_contexts fc
2160 where ppf.person_id = cp_person_id
2161 and paf.person_id = ppf.person_id
2162 and paa.assignment_id = paf.assignment_id
2163 and paa.tax_unit_id = cp_tax_unit_id
2164 and paa.payroll_action_id = ppa.payroll_action_id
2165 and ppa.action_type = pac.action_type
2166 and pac.classification_name = 'SEQUENCED'
2167 and ppa.effective_date between paf.effective_start_date
2168 and paf.effective_end_date
2169 and ppa.effective_date between ppf.effective_start_date
2170 and ppf.effective_end_date
2171 and ppa.effective_date between cp_period_start and cp_period_end
2172 AND pac1.assignment_action_id = paa.assignment_action_id
2173 AND pac1.assignment_id = paa.assignment_id
2174 AND fc.context_id = pac1.context_id
2175 AND fc.context_name = 'JURISDICTION_CODE'
2176 AND pac1.context_value = 'QC'
2177 order by paa.action_sequence desc;
2178
2179 BEGIN
2180
2181 --hr_utility.trace_on(null,'RL2');
2182 hr_utility.set_location ('archive_data',1);
2183 hr_utility.trace('getting assignment');
2184 l_negative_balance_exists := p_negative_balance_exists;
2185 p_temp_fi := 0; -- 14701748
2186
2187 l_step := 1;
2188 begin
2189
2190 SELECT aa.assignment_id,
2191 pay_magtape_generic.date_earned
2192 (p_effective_date,aa.assignment_id),
2193 aa.tax_unit_id,
2194 aa.chunk_number,
2195 aa.payroll_action_id,
2196 aa.serial_number
2197 into l_asgid,
2198 l_date_earned,
2199 l_tax_unit_id,
2200 l_chunk,
2201 l_payroll_action_id,
2202 lv_serial_number
2203 FROM pay_assignment_actions aa
2204 WHERE aa.assignment_action_id = p_assactid;
2205
2206 l_rl2_tax_unit_id := l_tax_unit_id;
2207
2208 select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
2209 legislative_parameters),
2210 business_group_id
2211 into l_pre_org_id,
2212 l_business_group_id
2213 from pay_payroll_actions
2214 where payroll_action_id = l_payroll_action_id;
2215
2216 exception when no_data_found then
2217 /* need a pop-message */
2218 hr_utility.trace('assignment_action_id doesnot exist to archive emp_info'
2219 ||to_char(p_assactid));
2220 end;
2221
2222 /* If the chunk of the assignment is same as the minimun chunk
2223 for the payroll_action_id and the gre data has not yet been
2224 archived then archive the gre data i.e. the employer data */
2225
2226 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2227
2228 hr_utility.trace('eoy_archive_data archiving employer data');
2229 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2230 hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2231
2232 eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2233 p_pre_org_id=>l_pre_org_id);
2234
2235 hr_utility.trace('eoy_archive_data archived employer data');
2236
2237 end if;
2238
2239 hr_utility.set_location ('eoy_archive_further_info',2);
2240
2241 hr_utility.trace('assignment '|| to_char(l_asgid));
2242 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2244 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245
2246 /* Derive the beginning and end of the effective year */
2247
2248 hr_utility.trace('getting begin and end dates');
2249
2250 l_step := 2;
2251
2252 l_year_start := trunc(p_effective_date, 'Y');
2253 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2254
2255 hr_utility.trace('year start '|| to_char(l_year_start));
2256 hr_utility.trace('year end '|| to_char(l_year_end));
2257
2258
2259 l_step := 3;
2260
2261 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2262
2263
2264 l_jurisdiction := 'QC';
2265
2266 l_step := 12;
2267
2268 /* We can archive the balance level dbis also because for employee level
2269 balances jurisdiction is always a context. */
2270
2271 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2272
2273 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2274 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2275
2276 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2277
2278 /* Initialise the PL/SQL table before populating it */
2279 hr_utility.trace('Initialising Pl/SQL table');
2280
2281 /* Any new further info codes introduced should be added here and also
2282 in the procedure eoy_archive_data to check whether line of records
2283 need to archived or not -- bug 16045054*/
2284
2285 l_count := 0;
2286
2287 l_count := l_count + 1;
2288 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_201';
2289
2290 l_count := l_count + 1;
2291 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_210'; /* 14701748 */
2292
2293 l_count := l_count + 1;
2294 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_235';
2295
2296 l_count := l_count + 1;
2297 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_B1';
2298
2299 l_count := l_count + 1;
2300 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_B2';
2301
2302 l_count := l_count + 1;
2303 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_B3';
2304
2305 l_count := l_count + 1;
2306 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_B4';
2307
2308 l_count := l_count + 1;
2309 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_C1';
2310
2311 l_count := l_count + 1;
2312 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_C2';
2313
2314 l_count := l_count + 1;
2315 l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_C3';
2316
2317 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2318
2319 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.count > 0 then
2320 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.delete;
2321 end if;
2322 ln_further_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.count;
2323 for i in 1 .. l_count
2324 loop
2325 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2326 if l_balance_type_tab(i) = 'RL2_FURTHER_INFO_AMOUNT_201' then
2327 ln_further_index := ln_further_index+1;
2328 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2329 (ln_further_index).action_info_category := 'CAEOY RL2 FURTHER INFO';
2330 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2331 (ln_further_index).jurisdiction_code := 'QC';
2332 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2333 (ln_further_index).act_info4 := l_balance_type_tab(i);
2334 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2335 (ln_further_index).act_info5 := substr(p_source_of_income_descr,1,15); -- 14701748
2336 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2337 (ln_further_index).act_info6 := 'RL2';
2338 /*Bug 14701748 */
2339 if p_source_of_income_descr is not null then
2340 p_temp_fi := p_temp_fi +1;
2341 end if;
2342 else
2343 result := 0;
2344 open c_all_gres(p_assactid);
2345 loop
2346 hr_utility.trace('Fetching all GREs');
2347 fetch c_all_gres into l_tax_unit_id;
2348 exit when c_all_gres%NOTFOUND;
2349
2350 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351 hr_utility.trace('Person_id is ' || lv_serial_number);
2352 hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353 hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354 hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2355
2356 begin
2357 /* Getting Payroll Run Level Max Assignment Action Id for
2358 the given tax_unit_id in the reporting year. Fix for bug#3638928 */
2359
2360 open c_get_max_asg_act_id(to_number(lv_serial_number),
2361 l_tax_unit_id,
2362 l_year_start,
2363 l_year_end);
2364 fetch c_get_max_asg_act_id into l_aaid;
2365 close c_get_max_asg_act_id;
2366
2367 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2368 hr_utility.trace('l_count is ' || to_char(l_count));
2369
2370 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2371 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2372 result := result + nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2373 ( l_balance_type_tab(i),
2374 'YTD' ,
2375 l_aaid,
2376 l_asgid ,
2377 NULL,
2378 'PER' ,
2379 l_tax_unit_id,
2380 l_business_group_id,
2381 'QC'
2382 ),0) ;
2383 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2384 hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2385 hr_utility.trace('Result is ' || to_char(result));
2386 exception when no_data_found then
2387 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2388 end;
2389 end loop;
2390 close c_all_gres;
2391 ln_further_index := ln_further_index+1;
2392 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2393 (ln_further_index).action_info_category := 'CAEOY RL2 FURTHER INFO';
2394 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2395 (ln_further_index).jurisdiction_code := 'QC';
2396 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2397 (ln_further_index).act_info4 := l_balance_type_tab(i);
2398 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2399 (ln_further_index).act_info5 := to_char(result);
2400 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data
2401 (ln_further_index).act_info6 := 'RL2';
2402 if result < 0 and l_negative_balance_exists <> 'Y' then
2403 l_negative_balance_exists := 'Y';
2404 end if;
2405 /*Bug 14701748 */
2406 if result is not null and result <> 0 then
2407 p_temp_fi := p_temp_fi +1;
2408 end if;
2409
2410 end if; --l_balance_type_tab(i) = 'RL2_FURTHER_INFO_AMOUNT_201'
2411 end loop;
2412
2413
2414 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.count >0 then
2415 archive_data_records(
2416 p_action_context_id => p_assactid
2417 ,p_action_context_type=> 'AAP'
2418 ,p_assignment_id => l_asgid
2419 ,p_tax_unit_id => l_rl2_tax_unit_id
2420 ,p_effective_date => p_effective_date
2421 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data);
2422 ln_further_index := null;
2423 end if;
2424 p_negative_balance_exists := l_negative_balance_exists;
2425 hr_utility.trace('End of Provincial YE Amendment PP Validation');
2426
2427 Exception when no_data_found then
2428 hr_utility.trace('Report type not found for given Payroll_action ');
2429 null;
2430 -- End of Provincial YE Amendment Pre-Process Validation
2431 end eoy_archive_further_info;
2432
2433
2434 /* Name : eoy_archive_data
2435 Purpose : This is the main procedure to archive the whole employee
2436 data along with balance values for RL2 Archiver PreProcess.
2437
2438 Arguments :
2439 Notes :
2440 */
2441
2442 PROCEDURE eoy_archive_data(p_assactid in number,
2443 p_effective_date in date) IS
2444
2445 l_aaid pay_assignment_actions.assignment_action_id%type;
2446 l_aaid1 pay_assignment_actions.assignment_action_id%type;
2447 l_aaseq pay_assignment_actions.action_sequence%type;
2448 l_asgid pay_assignment_actions.assignment_id%type;
2449 l_date_earned date;
2450 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2451 l_reporting_type varchar2(240);
2452 l_prev_tax_unit_id pay_assignment_actions.tax_unit_id%type := null;
2453 l_business_group_id number;
2454 l_year_start date;
2455 l_year_end date;
2456 l_context_no number := 60;
2457 l_count number := 0;
2458 l_jurisdiction varchar2(11);
2459 l_province_uei ff_user_entities.user_entity_id%type;
2460 l_county_uei ff_user_entities.user_entity_id%type;
2461 l_city_uei ff_user_entities.user_entity_id%type;
2462 l_county_sd_uei ff_user_entities.user_entity_id%type;
2463 l_city_sd_uei ff_user_entities.user_entity_id%type;
2464 l_province_abbrev pay_us_states.state_abbrev%type;
2465 l_county_name pay_us_counties.county_name%type;
2466 l_city_name pay_us_city_names.city_name%type;
2467 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
2468 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
2469 l_step number := 0;
2470 l_county_code varchar2(3);
2471 l_city_code varchar2(4);
2472 l_jursd_context_id ff_contexts.context_id%type;
2473 l_taxunit_context_id ff_contexts.context_id%type;
2474 l_seq_tab pay_ca_eoy_rl2_archive.number_data_type_table;
2475 l_context_id_tab pay_ca_eoy_rl2_archive.number_data_type_table;
2476 l_context_val_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2477 l_chunk number;
2478 l_payroll_action_id number;
2479 l_person_id number;
2480 l_defined_balance_id number;
2481 l_archive_item_id number;
2482 l_date_of_birth date;
2483 l_hire_date date;
2484 l_termination_date date;
2485 l_first_name varchar2(240);
2486 l_middle_name varchar2(240);
2487 l_last_name varchar2(240);
2488 l_employee_number varchar2(240);
2489 l_pre_name_adjunct varchar2(240);
2490 l_employee_phone_no varchar2(240);
2491 l_address_line1 varchar2(240);
2492 l_address_line2 varchar2(240);
2493 l_address_line3 varchar2(240);
2494 l_town_or_city varchar2(80);
2495 l_province_code varchar2(80);
2496 l_postal_code varchar2(80);
2497 l_telephone_number varchar2(80);
2498 l_country_code varchar2(80);
2499 l_counter number := 0;
2500
2501 l_count_start_for_boxo number := 0;
2502 l_count_end_for_boxo number := 0;
2503 l_count_for_boxo_code number := 0;
2504 l_pre_org_id varchar2(80);
2505 l_national_identifier varchar2(240);
2506 l_user_entity_value_tab_boxo number := 0;
2507 l_user_entity_code_tab_boxo VARCHAR2(4) := NULL;
2508 l_object_version_number number;
2509 -- l_rl2_slip_number_last_digit number; -- bug 14701748
2510 -- commented by sneelapa and redefined for bug 11654691
2511 -- l_rl2_slip_number number;
2512 l_rl2_slip_number varchar2(240); -- bug 14701748
2513 l_rl2_seq_number varchar2(240); -- bug 14701748
2514
2515 /* following 2 lines commented out for 14701748 */
2516 /*-- added for bug 11694701
2517 l_rl2_starting_slip_num number;
2518 l_rl2_ending_slip_num number;*/
2519 -- end changes for bug 11694701
2520
2521 l_negative_balance_exists varchar2(2);
2522
2523 l_max_assactid number;
2524 l_some_warning boolean;
2525 result number;
2526 l_no_of_payroll_run number := 0;
2527 l_has_been_paid varchar2(3);
2528 l_user_entity_name_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2529 l_user_entity_value_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2530 l_balance_type_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2531 l_footnote_balance_type_tab varchar2(80);
2532 l_footnote_code varchar2(30);
2533 l_footnote_balance varchar2(80);
2534 l_footnote_amount number;
2535 old_l_footnote_code varchar2(80) := null;
2536 old_balance_type_tab varchar2(80) := null;
2537 l_footnote_code_ue varchar2(80);
2538 l_footnote_amount_ue varchar2(80);
2539 l_no_of_fn_codes number := 0;
2540 l_value number := 0;
2541 l_transmitter_id number;
2542 -- l_rl2_last_slip_number number; -- bug 14701748
2543 l_rl2_curr_slip_number number;
2544 l_max_slip_number varchar2(80);
2545 fed_result number;
2546
2547 l_messages varchar2(240);
2548 l_mesg_amt number(12,2) := 0;
2549
2550 l_action_information_id_1 NUMBER ;
2551 l_object_version_number_1 NUMBER ;
2552 ln_tax_unit_id NUMBER ;
2553 ld_eff_date DATE ;
2554
2555 ln_status_indian NUMBER := 0;
2556 ln_index NUMBER;
2557 ln_footnote_index NUMBER;
2558 l_rl2_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2559 lv_footnote_bal varchar2(80);
2560
2561 l_rl2_source_of_income varchar2(150);
2562 l_per_eit_source_of_income varchar2(150);
2563 l_pre_source_of_income varchar2(150);
2564 l_per_eit_description varchar2(150);
2565 l_pre_description varchar2(150);
2566 l_per_eit_beneficiary_id varchar2(20);
2567 l_beneficiary_name varchar2(150);
2568 l_beneficiary_sin varchar2(20);
2569 ln_no_gross_earnings NUMBER := 0;
2570
2571 ln_defined_balance_id pay_defined_balances.defined_balance_id%type;
2572 lv_serial_number varchar2(30);
2573 lv_BoxL_excess_amt varchar2(30);
2574 lv_BoxO_excess_amt varchar2(30);
2575 lv_BoxL_Maxlimit varchar2(30);
2576 lv_BoxO_Maxlimit varchar2(30);
2577
2578 /* new variables added for Provincial YE Amendment PP */
2579 lv_fapp_effective_date varchar2(5);
2580 ln_fapp_pre_org_id number;
2581 lv_fapp_report_type varchar2(20);
2582 ln_fapp_locked_action_id number;
2583 lv_fapp_flag varchar2(2):= 'N';
2584
2585 /* new variables added for pre-printed form number */
2586 lv_eit_year varchar2(30);
2587 lv_eit_pre_org_id varchar2(40);
2588 lv_eit_form_no varchar2(20);
2589 l_pre_printed_slip_no varchar2(240); -- bug 14701748 - increased size to hold multiple slip nos-- For Bug 8921055
2590
2591 /*Bug 14701748 sgotlasw */
2592 ln_no_fi number := 0;
2593 ln_no_fi_per_slip number := 4;
2594 l_k number;
2595 lv_pre_pr_form_no varchar2(240);
2596
2597 /*Bug 16045054 sbachu*/
2598 l_count_fi number := 0;
2599 l_fi_balance_type_tab pay_ca_eoy_rl2_archive.char240_data_type_table;
2600 l_fi_result number := 0;
2601
2602 cursor c_get_fapp_locked_action_id(cp_locking_act_id number) is
2603 select locked_action_id
2604 from pay_action_interlocks
2605 where locking_action_id = cp_locking_act_id;
2606
2607 cursor c_get_preprinted_form_no (cp_person_id number,
2608 cp_pre_org_id number) is
2609 select pei_information5,
2610 pei_information6,
2611 pei_information7
2612 from per_people_extra_info
2613 where person_id = cp_person_id
2614 and pei_information6 = to_char(cp_pre_org_id)
2615 and pei_information_category = 'PAY_CA_RL2_FORM_NO'
2616 order by pei_information7; -- bug 14701748
2617
2618 /* Cursor to get the all gre values that are under the archived
2619 transmitter PRE */
2620 cursor c_all_gres(asgactid number) is
2621 select hoi.organization_id ,
2622 hoi.org_information5
2623 from pay_action_information pac,
2624 pay_assignment_actions paa,
2625 hr_organization_information hoi
2626 where paa.assignment_action_id = asgactid
2627 and pac.action_context_id = paa.payroll_action_id
2628 and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2629 and pac.action_information1 = 'RL2'
2630 and pac.action_information27 = hoi.org_information2
2631 and hoi.org_information_context = 'Canada Employer Identification'
2632 order by 1;
2633
2634 /* Cursor to get the all gre values that are under the archived
2635 transmitter PRE */
2636 cursor c_all_gres_for_footnote(asgactid number) is
2637 select hoi.organization_id ,
2638 hoi.org_information5
2639 from pay_action_information pac,
2640 pay_assignment_actions paa,
2641 hr_organization_information hoi
2642 where paa.assignment_action_id = asgactid
2643 and pac.action_context_id = paa.payroll_action_id
2644 and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2645 and pac.action_information1 = 'RL2'
2646 and pac.action_information27 = hoi.org_information2
2647 and hoi.org_information_context = 'Canada Employer Identification'
2648 order by 1;
2649
2650
2651 /* c_all_gres_for_person cursor because we not using anymore, 11510 bugfix */
2652
2653 /* Cursor to get the all the footnote elements that
2654 are fed to the given balance name */
2655 cursor c_footnote_info(p_balance_name varchar2) is
2656 select distinct pet.element_information19,
2657 pbt1.balance_name
2658 from pay_balance_feeds_f pbf,
2659 pay_balance_types pbt,
2660 pay_balance_types pbt1,
2661 pay_input_values_f piv,
2662 pay_element_types_f pet,
2663 fnd_lookup_values flv
2664 where pbt.balance_name = p_balance_name
2665 and pbf.balance_type_id = pbt.balance_type_id
2666 and pbf.input_value_id = piv.input_value_id
2667 and piv.element_type_id = pet.element_type_id
2668 and pbt1.balance_type_id = pet.element_information10
2669 and pet.business_group_id = l_business_group_id
2670 and pet.element_information19 = flv.lookup_code
2671 and flv.lookup_type = 'PAY_CA_RL2_FOOTNOTES'
2672 and flv.language = userenv('LANG')
2673 order by pet.element_information19;
2674
2675 /* Cursor to get the employee primary address */
2676 cursor c_get_pri_addr(cp_person_id in number
2677 ,cp_date_earned in date) is
2678 select address_line1,
2679 address_line2,
2680 address_line3,
2681 town_or_city,
2682 decode(country,'US',region_2,'CA',region_1,null),
2683 replace(postal_code,' '),
2684 country
2685 from per_addresses pa
2686 where pa.person_id = cp_person_id
2687 and pa.primary_flag = 'Y'
2688 and cp_date_earned between pa.date_from
2689 and nvl(pa.date_to, cp_date_earned);
2690
2691 /* Cursor to get the employee secondary address */
2692 cursor c_get_sec_addr(cp_person_id in number
2693 ,cp_date_earned in date) is
2694 select address_line1,
2695 address_line2,
2696 address_line3,
2697 town_or_city,
2698 decode(country,'US',region_2,'CA',region_1,null),
2699 replace(postal_code,' '),
2700 country
2701 from per_addresses pa
2702 where pa.person_id = cp_person_id
2703 and pa.primary_flag <> 'Y'
2704 and cp_date_earned between pa.date_from
2705 and nvl(pa.date_to, cp_date_earned)
2706 order by pa.date_from desc;
2707
2708 /* Cursor to get the employee details */
2709 cursor c_get_emp_detail(cp_asg_id number) is
2710 select PEOPLE.person_id,
2711 PEOPLE.first_name,
2712 PEOPLE.middle_names,
2713 PEOPLE.last_name,
2714 PEOPLE.employee_number,
2715 PEOPLE.date_of_birth,
2716 replace(PEOPLE.national_identifier,' '),
2717 PEOPLE.pre_name_adjunct
2718 from per_all_assignments_f ASSIGN
2719 ,per_all_people_f PEOPLE
2720 where ASSIGN.assignment_id = cp_asg_id
2721 and PEOPLE.person_id = ASSIGN.person_id
2722 and PEOPLE.effective_end_date =
2723 (select max(effective_end_date)
2724 from per_all_people_f PEOPLE1
2725 where PEOPLE1.person_id = PEOPLE.person_id);
2726
2727
2728 /* Query to get the max asg_act_id for a payroll run in a given year
2729 with tax_unit_id, asg_id and effective_date as parameters. Changed
2730 cursor to get max asgact_id based on person_id, fix for bug#3638928 */
2731 CURSOR c_get_max_asg_act_id(cp_person_id number,
2732 cp_tax_unit_id number,
2733 cp_period_start date,
2734 cp_period_end date) IS
2735 select paa.assignment_action_id
2736 from pay_assignment_actions paa,
2737 per_all_assignments_f paf,
2738 per_all_people_f ppf,
2739 pay_payroll_actions ppa,
2740 pay_action_classifications pac,
2741 pay_action_contexts pac1,
2742 ff_contexts fc
2743 where ppf.person_id = cp_person_id
2744 and paf.person_id = ppf.person_id
2745 and paa.assignment_id = paf.assignment_id
2746 and paa.tax_unit_id = cp_tax_unit_id
2747 and paa.payroll_action_id = ppa.payroll_action_id
2748 and ppa.action_type = pac.action_type
2749 and pac.classification_name = 'SEQUENCED'
2750 and ppa.effective_date between paf.effective_start_date
2751 and paf.effective_end_date
2752 and ppa.effective_date between ppf.effective_start_date
2753 and ppf.effective_end_date
2754 and ppa.effective_date between cp_period_start and cp_period_end
2755 AND pac1.assignment_action_id = paa.assignment_action_id
2756 AND pac1.assignment_id = paa.assignment_id
2757 AND fc.context_id = pac1.context_id
2758 AND fc.context_name = 'JURISDICTION_CODE'
2759 AND pac1.context_value = 'QC'
2760 order by paa.action_sequence desc;
2761
2762 CURSOR c_get_person_extra_info (cp_person_id number,
2763 cp_pre_org_id varchar2) IS
2764 select pei_information2,
2765 pei_information3,
2766 pei_information4
2767 from per_people_extra_info
2768 where person_id = cp_person_id
2769 and pei_information1 = cp_pre_org_id
2770 and pei_information_category = 'PAY_CA_RL2_INFORMATION';
2771
2772 /* Bug#3358604, Cursor to get RL2 Box L and O Max Limits for validation */
2773 CURSOR c_get_rl2box_limits(cp_lookup_code varchar2,
2774 cp_eff_date date) IS
2775 select information_value
2776 from pay_ca_legislation_info
2777 where lookup_type = 'RL2ARCHIVE'
2778 and lookup_code = cp_lookup_code
2779 and cp_eff_date between start_date and end_date;
2780
2781 BEGIN
2782
2783 -- hr_utility.trace_on(null,'RL2');
2784 hr_utility.set_location ('archive_data',1);
2785 hr_utility.trace('getting assignment');
2786 l_negative_balance_exists := 'N';
2787 l_has_been_paid := 'N';
2788
2789 lv_BoxL_Excess_amt := '0';
2790 lv_BoxO_Excess_amt := '0';
2791
2792 initialization_process('EMPLOYEE_DATA');
2793
2794 l_step := 1;
2795 begin
2796
2797 SELECT aa.assignment_id,
2798 pay_magtape_generic.date_earned
2799 (p_effective_date,aa.assignment_id),
2800 aa.tax_unit_id,
2801 aa.chunk_number,
2802 aa.payroll_action_id,
2803 aa.serial_number
2804 into l_asgid,
2805 l_date_earned,
2806 l_tax_unit_id,
2807 l_chunk,
2808 l_payroll_action_id,
2809 lv_serial_number
2810 FROM pay_assignment_actions aa
2811 WHERE aa.assignment_action_id = p_assactid;
2812
2813 l_rl2_tax_unit_id := l_tax_unit_id;
2814
2815 select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
2816 legislative_parameters),
2817 business_group_id
2818 into l_pre_org_id,
2819 l_business_group_id
2820 from pay_payroll_actions
2821 where payroll_action_id = l_payroll_action_id;
2822
2823 exception when no_data_found then
2824 /* need a pop-message */
2825 hr_utility.trace('assignment_action_id doesnot exist to archive emp_info'
2826 ||to_char(p_assactid));
2827 end;
2828
2829 /* If the chunk of the assignment is same as the minimun chunk
2830 for the payroll_action_id and the gre data has not yet been
2831 archived then archive the gre data i.e. the employer data */
2832
2833 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2834
2835 hr_utility.trace('eoy_archive_data archiving employer data');
2836 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2837 hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2838
2839 eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2840 p_pre_org_id=>l_pre_org_id);
2841
2842 hr_utility.trace('eoy_archive_data archived employer data');
2843
2844 end if;
2845
2846 hr_utility.set_location ('archive_data',2);
2847
2848 hr_utility.trace('assignment '|| to_char(l_asgid));
2849 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2851 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852
2853 /* Derive the beginning and end of the effective year */
2854
2855 hr_utility.trace('getting begin and end dates');
2856
2857 l_step := 2;
2858
2859 l_year_start := trunc(p_effective_date, 'Y');
2860 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2861
2862 hr_utility.trace('year start '|| to_char(l_year_start));
2863 hr_utility.trace('year end '|| to_char(l_year_end));
2864
2865
2866 l_step := 3;
2867
2868 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2869
2870 l_step := 5;
2871
2872 select context_id
2873 into l_jursd_context_id
2874 from ff_contexts
2875 where context_name = 'JURISDICTION_CODE';
2876
2877 select context_id
2878 into l_taxunit_context_id
2879 from ff_contexts
2880 where context_name = 'TAX_UNIT_ID';
2881
2882 l_step := 6;
2883
2884 l_jurisdiction := 'QC';
2885
2886 l_step := 12;
2887
2888 /* We can archive the balance level dbis also because for employee level
2889 balances jurisdiction is always a context. */
2890
2891 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2892
2893 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2894 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2895
2896 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2897
2898 /* 14701748 */
2899 begin
2900
2901 select decode(hoi.org_information3,'Y',hoi.organization_id,
2902 hoi.org_information20)
2903 into l_transmitter_id
2904 from hr_organization_information hoi,
2905 hr_all_organization_units hou
2906 WHERE hou.business_group_id = l_business_group_id
2907 and hoi.organization_id = hou.organization_id
2908 and hoi.org_information_context = 'Prov Reporting Est'
2909 and hoi.organization_id = to_number(l_pre_org_id)
2910 and hoi.org_information4 = 'P02';
2911 hr_utility.trace('l_transmitter_id : ' || to_char(l_transmitter_id));
2912
2913 begin
2914 select hoi.org_information1,hoi.org_information2
2915 into l_pre_source_of_income,l_pre_description
2916 from hr_organization_information hoi
2917 where hoi.organization_id = l_transmitter_id
2918 and hoi.org_information_context = 'Prov Reporting Est2';
2919
2920 exception
2921 when no_data_found then
2922 hr_utility.trace('No RL2 Source of Income at PRE level');
2923 hr_utility.trace('l_transmitter_id :'||to_char(l_transmitter_id));
2924 l_pre_source_of_income := NULL;
2925 l_pre_description := NULL;
2926 end;
2927 exception
2928 when no_data_found then
2929 hr_utility.trace('Could not get the transmitter ID for the PRE');
2930 end;
2931
2932
2933 /* Initialise the PL/SQL table before populating it */
2934 hr_utility.trace('Initialising Pl/SQL table');
2935
2936 l_count := 0;
2937
2938 l_count := l_count + 1;
2939 l_balance_type_tab(l_count) := 'Gross Earnings';
2940
2941 l_count := l_count + 1;
2942 l_balance_type_tab(l_count) := 'QPP EE Withheld';
2943
2944 l_count := l_count + 1;
2945 l_balance_type_tab(l_count) := 'EI EE Withheld';
2946
2947 -- Quebec Income tax withheld (used for RL2 Box J)
2948 l_count := l_count + 1;
2949 l_balance_type_tab(l_count) := 'PROV Withheld';
2950
2951 -- RL2 Box A Registered Plan
2952 l_count := l_count + 1;
2953 l_balance_type_tab(l_count) := 'Life Annuity Payments registered plan';
2954
2955 -- RL2 Box A Unregistered Plan
2956 if to_number(to_char(l_year_end,'YYYY')) < 2011 then
2957 l_count := l_count + 1;
2958 l_balance_type_tab(l_count) := 'Life Annuity Payments Unregistered plan';
2959 end if;
2960
2961 -- RL2 Box B
2962 l_count := l_count + 1;
2963 l_balance_type_tab(l_count) := 'Benefits from RRSP RRIF DPSP and Annuities';
2964
2965 -- RL2 Box C
2966 l_count := l_count + 1;
2967 l_balance_type_tab(l_count) := 'Other Payments';
2968
2969 -- RL2 Box D
2970 l_count := l_count + 1;
2971 l_balance_type_tab(l_count)
2972 := 'Refund of RRSP Premiums paid to surviving spouse';
2973
2974 -- RL2 Box E
2975 l_count := l_count + 1;
2976 l_balance_type_tab(l_count) := 'Benefits at the time of death';
2977
2978 -- RL2 Box F
2979 l_count := l_count + 1;
2980 l_balance_type_tab(l_count) := 'Refund of Undeducted RRSP contributions';
2981
2982 -- RL2 Box G
2983 l_count := l_count + 1;
2984 l_balance_type_tab(l_count)
2985 := 'Taxable Amount revoked registration RRSP or RRIF';
2986
2987 -- RL2 Box H
2988 l_count := l_count + 1;
2989 l_balance_type_tab(l_count) := 'Other Income RRSP or RRIF';
2990
2991 -- RL2 Box I
2992 l_count := l_count + 1;
2993 l_balance_type_tab(l_count) :=
2994 'Amount entitlement deduction for RRSP or RRIF';
2995
2996 -- RL2 Box K
2997 l_count := l_count + 1;
2998 l_balance_type_tab(l_count) :=
2999 'Income earned after death RRSP or RRIF';
3000
3001 -- RL2 Box L
3002 l_count := l_count + 1;
3003 l_balance_type_tab(l_count) :=
3004 'Withdrawal under the Lifelong Learning Plan';
3005
3006 -- RL2 Box M
3007 l_count := l_count + 1;
3008 l_balance_type_tab(l_count) := 'Tax Paid Amounts';
3009
3010 -- RL2 Box O
3011 l_count := l_count + 1;
3012 l_balance_type_tab(l_count) := 'Withdrawal under the Home Buyers Plan';
3013
3014 /*Bug 16045054 starts here sbachu*/
3015
3016 l_count_fi := 0;
3017
3018 /*l_count_fi := l_count_fi + 1;
3019 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_201';*/
3020
3021 /* Any new further info codes introduced should be added here also since
3022 archiver records need to be created even if one rl2 further info balance
3023 is non zero except 201*/
3024
3025 l_count_fi := l_count_fi + 1;
3026 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_210'; /* 14701748 */
3027
3028 l_count_fi := l_count_fi + 1;
3029 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_235';
3030
3031 l_count_fi := l_count_fi + 1;
3032 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_B1';
3033
3034 l_count_fi := l_count_fi + 1;
3035 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_B2';
3036
3037 l_count_fi := l_count_fi + 1;
3038 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_B3';
3039
3040 l_count_fi := l_count_fi + 1;
3041 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_B4';
3042
3043 l_count_fi := l_count_fi + 1;
3044 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_C1';
3045
3046 l_count_fi := l_count_fi + 1;
3047 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_C2';
3048
3049 l_count_fi := l_count_fi + 1;
3050 l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_C3';
3051
3052 /*Bug 16045054 ends here*/
3053
3054 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3055
3056 for i in 1 .. l_count
3057 loop
3058 hr_utility.trace('Initialising values');
3059 l_user_entity_value_tab(i) := 0;
3060 end loop;
3061
3062 open c_all_gres(p_assactid);
3063
3064 loop
3065
3066 hr_utility.trace('Fetching all GREs');
3067 fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3068 exit when c_all_gres%NOTFOUND;
3069
3070 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071 hr_utility.trace('Person_id is ' || lv_serial_number);
3072 hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073 hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074 hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3075
3076 begin
3077 /* Getting Payroll Run Level Max Assignment Action Id for
3078 the given tax_unit_id in the reporting year. Fix for bug#3638928 */
3079
3080 open c_get_max_asg_act_id(to_number(lv_serial_number),
3081 l_tax_unit_id,
3082 l_year_start,
3083 l_year_end);
3084 fetch c_get_max_asg_act_id into l_aaid;
3085 close c_get_max_asg_act_id;
3086
3087 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3088 hr_utility.trace('l_count is ' || to_char(l_count));
3089
3090 ln_no_gross_earnings := ln_no_gross_earnings +
3091 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3092 ('RL2 No Gross Earnings',
3093 'YTD' ,
3094 l_aaid,
3095 l_asgid,
3096 NULL,
3097 'PER' ,
3098 l_tax_unit_id,
3099 l_business_group_id,
3100 'QC'
3101 ),0);
3102
3103 l_no_of_payroll_run := l_no_of_payroll_run + 1;
3104
3105 if l_tax_unit_id <> l_prev_tax_unit_id or
3106 l_prev_tax_unit_id is null then
3107
3108 hr_utility.trace('l_business_group_id is '||to_char(l_business_group_id));
3109
3110 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3111 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3112 Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3113
3114 for i in 1 .. l_count
3115 loop
3116
3117 hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3118 hr_utility.trace('i is ' || i);
3119
3120 -- T4A earnings should not go to BOX A of RL2
3121
3122 if l_reporting_type = 'T4A/RL2' and
3123 l_balance_type_tab(i) = 'Gross Earnings'
3124 then
3125 null;
3126 else
3127 -- l_user_entity_value_tab(i) := 0;
3128
3129 if l_balance_type_tab(i) = 'Gross Earnings' then
3130
3131 fed_result :=
3132 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3133 ('Taxable Benefits for Federal',
3134 'YTD' ,
3135 l_aaid,
3136 l_asgid ,
3137 NULL,
3138 'PER' ,
3139 l_tax_unit_id,
3140 l_business_group_id,
3141 'QC'
3142 ),0);
3143
3144 hr_utility.trace('Fed Result = ' || fed_result);
3145 else
3146 fed_result := 0;
3147 hr_utility.trace('Fed Result = ' || fed_result);
3148 end if;/*end if for l_balance_type_tab(i)='Gross Earnings' */
3149
3150 /* Based on defined_balance_id get the balance value
3151 for each assignment action */
3152
3153 ln_defined_balance_id :=
3154 get_def_bal_id(l_balance_type_tab(i),
3155 'Person in JD within GRE Year to Date',
3156 'CA');
3157
3158 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
3159 nvl(pay_balance_pkg.get_value(ln_defined_balance_id,
3160 l_aaid),0);
3161
3162 if l_user_entity_value_tab(i) <> 0 then
3163 l_has_been_paid := 'Y';
3164 if l_balance_type_tab(i) = 'FED STATUS INDIAN Subject' then
3165 ln_status_indian := l_user_entity_value_tab(i);
3166 end if;
3167 end if;
3168
3169 end if; -- end if for 'T4A/RL2' validation
3170
3171 hr_utility.trace('Balance Type is '||l_balance_type_tab(i));
3172 hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
3173 l_prev_tax_unit_id := l_tax_unit_id ;
3174
3175 end loop; -- end loop for all balances plsql table
3176 end if; --end if for l_tax_unit_id <> l_prev_tax_unit_id validation
3177
3178 exception
3179 when no_data_found then
3180 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3181 /* need a pop-message asgid has no payroll run in tax-unit-id */
3182 end;
3183 end loop;
3184 close c_all_gres;
3185
3186 /*Bug 16045054 starts here sbachu*/
3187 /* Line of records need to archived even if any of the rl2 further info balance
3188 is non zero irrespective of other rl2 balances since from 2012 further info
3189 values are also getting reported in mag */
3190
3191 for i in 1 .. l_count_fi
3192 loop
3193 l_fi_result := 0;
3194 open c_all_gres(p_assactid);
3195 loop
3196 hr_utility.trace('Fetching all GREs');
3197 fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3198 exit when c_all_gres%NOTFOUND;
3199
3200 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3201 hr_utility.trace('Person_id is ' || lv_serial_number);
3202 hr_utility.trace('Asgid is ' || to_char(l_asgid));
3203
3204 begin
3205 /* Getting Payroll Run Level Max Assignment Action Id for
3206 the given tax_unit_id in the reporting year. Fix for bug#3638928 */
3207
3208 open c_get_max_asg_act_id(to_number(lv_serial_number),
3209 l_tax_unit_id,
3210 l_year_start,
3211 l_year_end);
3212 fetch c_get_max_asg_act_id into l_aaid;
3213 close c_get_max_asg_act_id;
3214
3215 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3216 hr_utility.trace('l_count_fi is ' || to_char(l_count_fi));
3217 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3218 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3219 Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3220 l_fi_result := l_fi_result + nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3221 ( l_fi_balance_type_tab(i),
3222 'YTD' ,
3223 l_aaid,
3224 l_asgid ,
3225 NULL,
3226 'PER' ,
3227 l_tax_unit_id,
3228 l_business_group_id,
3229 'QC'
3230 ),0) ;
3231 hr_utility.trace('Result is ' || to_char(l_fi_result));
3232 exception when no_data_found then
3233 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3234 end;
3235 end loop;
3236 close c_all_gres;
3237 if l_fi_result is not null and l_fi_result <> 0 then
3238 l_has_been_paid := 'Y';
3239 end if;
3240 end loop;
3241 /*Bug 16045054 ends here sbachu*/
3242 hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3243
3244 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count;
3245 ln_footnote_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count;
3246
3247 hr_utility.trace('ln_index :'||to_char(ln_index));
3248 hr_utility.trace('ln_footnote_index :'||to_char(ln_footnote_index));
3249
3250 if ((l_no_of_payroll_run > 0) and
3251 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3252
3253 /* Removed from here 14701748
3254 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info1
3255 := gen_rl2_slip_no(l_transmitter_id,l_asgid);*/
3256 for i in 1 .. l_count
3257 loop
3258
3259 hr_utility.trace('in the employee info archive loop');
3260 hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3261 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3262 /*
3263 lv_BoxL_excess_amt := '0';
3264 lv_BoxO_excess_amt := '0';
3265 */
3266
3267 if l_balance_type_tab(i) =
3268 'Life Annuity Payments registered plan' then
3269 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3
3270 := l_user_entity_value_tab(i);
3271 hr_utility.trace('ln_index :'||to_char(ln_index));
3272
3273 elsif l_balance_type_tab(i) =
3274 'Life Annuity Payments Unregistered plan' then
3275
3276 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3
3277 := to_number
3278 (pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3)
3279 + to_number(nvl(l_user_entity_value_tab(i),0));
3280
3281 elsif l_balance_type_tab(i) =
3282 'Benefits from RRSP RRIF DPSP and Annuities' then
3283 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info4
3284 := l_user_entity_value_tab(i);
3285
3286 elsif l_balance_type_tab(i) = 'Other Payments' then
3287 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3288 := l_user_entity_value_tab(i);
3289 hr_utility.trace('Box C :'||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
3290
3291 elsif l_balance_type_tab(i) =
3292 'Refund of RRSP Premiums paid to surviving spouse' then
3293 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info6
3294 := l_user_entity_value_tab(i);
3295
3296 elsif l_balance_type_tab(i) = 'Benefits at the time of death' then
3297 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info7
3298 := l_user_entity_value_tab(i);
3299
3300 elsif l_balance_type_tab(i) =
3301 'Refund of Undeducted RRSP contributions' then
3302 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info8
3303 := l_user_entity_value_tab(i);
3304
3305 elsif l_balance_type_tab(i) =
3306 'Taxable Amount revoked registration RRSP or RRIF' then
3307 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info9
3308 := l_user_entity_value_tab(i);
3309
3310 elsif l_balance_type_tab(i) = 'Other Income RRSP or RRIF' then
3311 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info10
3312 := l_user_entity_value_tab(i);
3313
3314 elsif l_balance_type_tab(i) =
3315 'Amount entitlement deduction for RRSP or RRIF' then
3316 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info11
3317 := l_user_entity_value_tab(i);
3318
3319 elsif l_balance_type_tab(i) = 'PROV Withheld' then
3320 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info12
3321 := l_user_entity_value_tab(i);
3322
3323 elsif l_balance_type_tab(i) =
3324 'Income earned after death RRSP or RRIF' then
3325 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info13
3326 := l_user_entity_value_tab(i);
3327
3328 elsif l_balance_type_tab(i) =
3329 'Withdrawal under the Lifelong Learning Plan' then
3330
3331 /* Bug#3358604, if Box L is more than $10,000.00 put excess
3332 amount in Box C */
3333 open c_get_rl2box_limits('BOXL_MAXLIMIT',p_effective_date);
3334 fetch c_get_rl2box_limits into lv_boxL_Maxlimit;
3335 close c_get_rl2box_limits;
3336
3337 if to_number(l_user_entity_value_tab(i)) > to_number(lv_boxL_Maxlimit) then
3338
3339 lv_BoxL_excess_amt := to_char(to_number(l_user_entity_value_tab(i))
3340 - to_number(lv_boxL_Maxlimit)) ;
3341 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info14
3342 := lv_boxL_Maxlimit;
3343 else
3344 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info14
3345 := l_user_entity_value_tab(i);
3346 end if;
3347
3348 elsif l_balance_type_tab(i) = 'Tax Paid Amounts' then
3349 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info15
3350 := l_user_entity_value_tab(i);
3351
3352 elsif l_balance_type_tab(i)
3353 = 'Withdrawal under the Home Buyers Plan' then
3354
3355 /* Bug#3358604, if Box O is more than $20,000.00 put excess
3356 amount in Box C */
3357 open c_get_rl2box_limits('BOXO_MAXLIMIT',p_effective_date);
3358 fetch c_get_rl2box_limits into lv_boxO_Maxlimit;
3359 close c_get_rl2box_limits;
3360
3361 if to_number(l_user_entity_value_tab(i)) > to_number(lv_boxO_Maxlimit) then
3362
3363 lv_BoxO_excess_amt := to_char(to_number(l_user_entity_value_tab(i))
3364 - to_number(lv_boxO_Maxlimit));
3365 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info17
3366 := lv_boxO_Maxlimit;
3367 else
3368 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info17
3369 := l_user_entity_value_tab(i);
3370 end if;
3371
3372 end if;
3373
3374
3375 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3376
3377 if to_number(nvl(l_user_entity_value_tab(i),'0')) < 0 then
3378
3379 hr_utility.trace('Negative balance exists');
3380 l_negative_balance_exists := 'Y';
3381 end if;
3382
3383 if l_user_entity_value_tab(i) <> 0 then
3384
3385 if l_balance_type_tab(i)
3386 = 'Life Annuity Payments Unregistered plan' then
3387 begin
3388
3389 /* RL2 Automatic Footnote Archive Start */
3390 l_footnote_code := 'BOXA';
3391 if chk_rl2_footnote(l_footnote_code) then
3392
3393 l_footnote_amount
3394 := to_number(nvl(l_user_entity_value_tab(i),0));
3395 ln_footnote_index := ln_footnote_index;
3396 hr_utility.trace(' Box A ln_footnote_index :'
3397 ||to_char(ln_footnote_index));
3398
3399 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3400 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3401 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3402 (ln_footnote_index).jurisdiction_code := 'QC';
3403 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3404 (ln_footnote_index).act_info4 := l_footnote_code;
3405 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3406 (ln_footnote_index).act_info5 :=
3407 to_char(l_footnote_amount);
3408 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3409 (ln_footnote_index).act_info6 := 'RL2';
3410
3411 if l_footnote_amount < 0 then
3412
3413 hr_utility.trace('Negative balance exists');
3414 l_negative_balance_exists := 'Y';
3415 end if;
3416
3417 end if; /* end if for chk_rl2_footnote */
3418 end ; /* end of RL2 Automatic footnote archive */
3419 else
3420 l_footnote_balance_type_tab := l_balance_type_tab(i);
3421 end if; /* end if for l_balance_type_tab(i)= 'Life Annuity...' */
3422
3423 if l_footnote_balance_type_tab in
3424 ('Benefits from RRSP RRIF DPSP and Annuities',
3425 'Other Payments') then
3426 begin
3427
3428 hr_utility.trace('RL2 Footnote archive start ');
3429 lv_footnote_bal := l_footnote_balance_type_tab;
3430 l_footnote_code := NULL;
3431 old_l_footnote_code := NULL;
3432 l_footnote_amount := 0;
3433
3434 open c_footnote_info(lv_footnote_bal);
3435 hr_utility.trace('lv_footnote_bal is '||lv_footnote_bal);
3436
3437 loop
3438 fetch c_footnote_info into l_footnote_code,
3439 l_footnote_balance;
3440 exit when c_footnote_info%NOTFOUND;
3441
3442 hr_utility.trace('l_footnote_amount_balance is '||
3443 l_footnote_balance);
3444 hr_utility.trace('l_footnote_code is '||
3445 l_footnote_code);
3446
3447 if ( l_footnote_code <> old_l_footnote_code or
3448 old_l_footnote_code is null )
3449 then
3450 if old_l_footnote_code is not null then
3451
3452 hr_utility.trace('old_l_footnote_code is '||
3453 old_l_footnote_code);
3454 hr_utility.trace('l_footnote_amount is '||
3455 to_char(l_footnote_amount));
3456
3457 if chk_rl2_footnote(old_l_footnote_code) and
3458 l_footnote_amount <> 0 then
3459
3460 hr_utility.trace('RL2 footnote archiving ');
3461 ln_footnote_index := ln_footnote_index + 1;
3462 hr_utility.trace('old_l_ftcode ln_footnote_index :'
3463 ||to_char(ln_footnote_index));
3464
3465 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3466 (ln_footnote_index).action_info_category
3467 := 'CA FOOTNOTES';
3468
3469 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3470 (ln_footnote_index).jurisdiction_code := 'QC';
3471
3472 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3473 (ln_footnote_index).act_info4
3474 := old_l_footnote_code;
3475
3476 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3477 (ln_footnote_index).act_info5
3478 := to_char(l_footnote_amount);
3479
3480 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3481 (ln_footnote_index).act_info6 := 'RL2';
3482
3483 if l_footnote_amount < 0 then
3484
3485 hr_utility.trace('Negative balance exists');
3486 l_negative_balance_exists := 'Y';
3487 end if;
3488
3489 end if;/* end if for chk_rl2_footnote */
3490
3491 end if; /* end if for old_l_footnote_code not null */
3492
3493 l_footnote_amount := 0;
3494 old_l_footnote_code := l_footnote_code ;
3495 old_balance_type_tab := l_footnote_balance_type_tab ;
3496
3497 end if; /* end if for l_footnote_code<>old_l_footnote_code*/
3498
3499 l_prev_tax_unit_id := NULL;
3500
3501 -- get the footnote_balance
3502
3503 open c_all_gres_for_footnote(p_assactid);
3504 loop
3505 hr_utility.trace('Fetching all GREs for footnotes');
3506 fetch c_all_gres_for_footnote into l_tax_unit_id,
3507 l_reporting_type;
3508 exit when c_all_gres_for_footnote%NOTFOUND;
3509
3510 hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
3511 hr_utility.trace('Asgid is ' || l_asgid);
3512 hr_utility.trace('Reporting_type is ' || l_reporting_type);
3513 hr_utility.trace('Effective date is '|| p_effective_date);
3514
3515 begin
3516 open c_get_max_asg_act_id(to_number(lv_serial_number),
3517 l_tax_unit_id,
3518 l_year_start,
3519 l_year_end);
3520 fetch c_get_max_asg_act_id into l_aaid;
3521 close c_get_max_asg_act_id;
3522
3523 hr_utility.trace('l_aaid is ' || l_aaid);
3524 hr_utility.trace('l_count is ' || l_count);
3525
3526 l_no_of_payroll_run := l_no_of_payroll_run + 1;
3527
3528 if ( l_tax_unit_id <> l_prev_tax_unit_id or
3529 l_prev_tax_unit_id is null )
3530 then
3531 pay_balance_pkg.set_context('TAX_UNIT_ID',
3532 l_tax_unit_id);
3533 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',
3534 l_aaid);
3535 pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3536
3537 l_footnote_amount := l_footnote_amount +
3538 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3539 ( l_footnote_balance,
3540 'YTD' ,
3541 l_aaid,
3542 l_asgid ,
3543 NULL,
3544 'PER' ,
3545 l_tax_unit_id,
3546 l_business_group_id,
3547 'QC'
3548 ),0) ;
3549
3550 hr_utility.trace('l_footnote_amount is '
3551 || to_char(l_footnote_amount));
3552 end if;
3553
3554 l_prev_tax_unit_id := l_tax_unit_id ;
3555 exception
3556 when no_data_found then
3557 /* need a pop-message asgid has not payrollrun in tx*/
3558 hr_utility.trace('This Tax unit id has no payroll run'
3559 ||' so skip it');
3560 end;
3561 end loop;
3562 close c_all_gres_for_footnote;
3563
3564 -- end of getting balance
3565
3566 if l_footnote_amount <> 0 then
3567 l_no_of_fn_codes := l_no_of_fn_codes + 1;
3568 hr_utility.trace('l_no_of_fn_codes is '
3569 || l_no_of_fn_codes);
3570 end if;
3571
3572 end loop; -- c_footnote_info loop
3573 close c_footnote_info;
3574
3575 -- Archiving the last footnote code and amount
3576 if chk_rl2_footnote(l_footnote_code) and
3577 l_footnote_amount <> 0 then
3578
3579 hr_utility.trace('p_assactid is ' ||to_char(p_assactid));
3580 hr_utility.trace('before ftnote archive l_footnote_code is '
3581 || l_footnote_code);
3582 hr_utility.trace('l_footnote_amount is '
3583 || to_char(l_footnote_amount));
3584
3585 hr_utility.trace('RL2 footnote archiving ');
3586 ln_footnote_index := ln_footnote_index + 1;
3587 hr_utility.trace('after close c_footnote_info ln_footnote_index :'
3588 ||to_char(ln_footnote_index));
3589
3590 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3591 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3592 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3593 (ln_footnote_index).jurisdiction_code := 'QC';
3594 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3595 (ln_footnote_index).act_info4 := l_footnote_code;
3596 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3597 (ln_footnote_index).act_info5 := to_char(l_footnote_amount);
3598 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3599 (ln_footnote_index).act_info6 := 'RL2';
3600
3601 if l_footnote_amount < 0 then
3602
3603 hr_utility.trace('Negative balance exists');
3604 l_negative_balance_exists := 'Y';
3605 end if;
3606
3607 end if;/* end if for chk_rl2_footnote */
3608
3609 end;
3610 hr_utility.trace('RL2 Footnote archive end ');
3611 end if; /* end if for l_footnote_balance_type_tab in validation */
3612 -- End of footnote archiving
3613
3614 end if; /* end if for l_user_entity_value_tab(i) <>0 */
3615
3616 end loop; /* end loop for plsql table balances */
3617
3618 /* Bug#3358604 Adding Box L,O excess Amount to Box C */
3619 hr_utility.trace('lv_BoxL_excess_amt : '||lv_BoxL_excess_amt);
3620 hr_utility.trace('lv_BoxO_excess_amt : '||lv_BoxO_excess_amt);
3621
3622 if ((lv_BoxL_Excess_amt > 0) or (lv_BoxO_Excess_amt > 0)) then
3623 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3624 := to_char(NVL(to_number(pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5),0)
3625 + to_number(lv_BoxL_excess_amt) + to_number(lv_BoxO_excess_amt));
3626 hr_utility.trace('Box C : '||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
3627 end if;
3628
3629 /* Commented out for Bug 15997380
3630 Set the Negative Balance Flag for Archiving
3631 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info30
3632 := l_negative_balance_exists; */
3633
3634 hr_utility.trace('after loop act_info4 is: '
3635 || pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info4);
3636
3637 end if; /* end if for ((l_no_of_payroll_run >0) and (l_has_been_paid='Y'))*/
3638
3639 -- Need to Archive Non-Box Footnotes, will be done next year
3640
3641 l_count := 0;
3642 -- Similarly create archive data for employee surname,employee first name,
3643 -- employee initial, employee address ,city,province,country,postal code,
3644 -- SIN, employee number , business number .
3645 -- Not all of them has jurisdiction context.
3646
3647 if ((l_no_of_payroll_run > 0) and
3648 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3649
3650 begin
3651
3652 open c_get_emp_detail(l_asgid);
3653 fetch c_get_emp_detail into
3654 l_person_id,
3655 l_first_name,
3656 l_middle_name,
3657 l_last_name,
3658 l_employee_number,
3659 l_date_of_birth,
3660 l_national_identifier,
3661 l_pre_name_adjunct;
3662
3663 if c_get_emp_detail%NOTFOUND then
3664
3665 /* need a pop-message employee basic data absent */
3666 l_first_name := null;
3667 l_middle_name := null;
3668 l_last_name := null;
3669 l_employee_number := null;
3670 l_national_identifier := null;
3671 l_pre_name_adjunct := null;
3672 l_employee_phone_no := null;
3673 l_date_of_birth := null;
3674 end if;
3675 close c_get_emp_detail;
3676 end;
3677
3678 begin
3679
3680 select max(date_start)
3681 ,max(actual_termination_date)
3682 into l_hire_date
3683 ,l_termination_date
3684 from per_periods_of_service
3685 where person_id = l_person_id;
3686
3687 exception
3688 when no_data_found then
3689 l_hire_date := null;
3690 l_termination_date := null;
3691
3692 end;
3693
3694 open c_get_person_extra_info(l_person_id, l_pre_org_id);
3695 fetch c_get_person_extra_info into
3696 l_per_eit_source_of_income,
3697 l_per_eit_description,
3698 l_per_eit_beneficiary_id;
3699
3700 if c_get_person_extra_info%NOTFOUND then
3701 close c_get_person_extra_info;
3702 l_per_eit_source_of_income := null;
3703 l_per_eit_description := null;
3704 l_per_eit_beneficiary_id := null;
3705 else
3706 close c_get_person_extra_info;
3707 end if;
3708
3709 if l_per_eit_beneficiary_id is not null then
3710 begin
3711 select ppf.full_name,
3712 replace(ppf.national_identifier,' ')
3713 into l_beneficiary_name,
3714 l_beneficiary_sin
3715 from per_all_people_f ppf
3716 where ppf.person_id = to_number(l_per_eit_beneficiary_id);
3717
3718 exception when no_data_found then
3719 l_beneficiary_name := null;
3720 l_beneficiary_sin := null;
3721 end;
3722 end if;
3723
3724
3725 if l_per_eit_source_of_income is not null then
3726 if l_per_eit_source_of_income = 'OTHER' then
3727 l_rl2_source_of_income := l_per_eit_source_of_income||':'||
3728 l_per_eit_description;
3729
3730 -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
3731 ln_footnote_index := ln_footnote_index + 1;
3732 hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
3733 ||to_char(ln_footnote_index));
3734
3735 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3736 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3737 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3738 (ln_footnote_index).jurisdiction_code := 'QC';
3739 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3740 (ln_footnote_index).act_info4 := l_rl2_source_of_income;
3741 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3742 (ln_footnote_index).act_info5 := '0';
3743 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3744 (ln_footnote_index).act_info6 := 'RL2';
3745 else
3746 l_rl2_source_of_income := l_per_eit_source_of_income;
3747 end if;
3748 else
3749 if l_pre_source_of_income = 'OTHER' then
3750 l_rl2_source_of_income := l_pre_source_of_income||':'||
3751 l_pre_description;
3752
3753 -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
3754 ln_footnote_index := ln_footnote_index + 1;
3755 hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
3756 ||to_char(ln_footnote_index));
3757
3758 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3759 (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3760 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3761 (ln_footnote_index).jurisdiction_code := 'QC';
3762 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3763 (ln_footnote_index).act_info4 := l_rl2_source_of_income;
3764 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3765 (ln_footnote_index).act_info5 := '0';
3766 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3767 (ln_footnote_index).act_info6 := 'RL2';
3768 else
3769 l_rl2_source_of_income := l_pre_source_of_income;
3770 end if;
3771 end if;
3772
3773 hr_utility.trace('Before counter of asgid '|| l_asgid);
3774
3775 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).action_info_category
3776 := 'CAEOY RL2 EMPLOYEE INFO';
3777
3778 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).jurisdiction_code
3779 := l_jurisdiction;
3780
3781 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info2
3782 := l_rl2_source_of_income;
3783
3784 -- RL2 Box N SIN of Spouse
3785 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info16
3786 := l_beneficiary_sin;
3787
3788 hr_utility.trace('Employee Info ln_index: '||to_char(ln_index));
3789 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info18
3790 := l_first_name;
3791
3792 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info20
3793 := l_last_name ;
3794
3795 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info19
3796 := l_middle_name ;
3797
3798 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info28
3799 := l_national_identifier;
3800
3801 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info29
3802 := l_employee_number;
3803
3804 end if;
3805
3806 --added for bug 13584299
3807 if ((l_no_of_payroll_run > 0) and
3808 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3809 if to_number(to_char(l_year_end,'YYYY')) >= 2011 then
3810 if l_rl2_source_of_income is not null and substr(l_rl2_source_of_income,1,5) = 'OTHER' then
3811 eoy_archive_further_info(p_assactid,p_effective_date,substr(l_rl2_source_of_income,7),l_negative_balance_exists,ln_no_fi);
3812 else
3813 eoy_archive_further_info(p_assactid,p_effective_date,NULL,l_negative_balance_exists,ln_no_fi);
3814 end if;
3815 end if;
3816
3817 /* bug 14701748
3818 This code snippet has been introduced here to generate the string
3819 of slip numbers for each slip */
3820 l_rl2_slip_number := gen_rl2_slip_no(l_transmitter_id,l_asgid);
3821 if ln_no_fi > ln_no_fi_per_slip then
3822 if mod(ln_no_fi,ln_no_fi_per_slip) = 0 then
3823 for l_i in 1..trunc(ln_no_fi/ln_no_fi_per_slip)-1
3824 loop
3825 l_rl2_slip_number := l_rl2_slip_number || '|' || gen_rl2_slip_no(l_transmitter_id,l_asgid);
3826 end loop;
3827 else
3828 for l_i in 1..trunc(ln_no_fi/ln_no_fi_per_slip)
3829 loop
3830 l_rl2_slip_number := l_rl2_slip_number || '|' || gen_rl2_slip_no(l_transmitter_id,l_asgid);
3831
3832 end loop;
3833 end if;
3834 end if;
3835
3836 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info1
3837 := l_rl2_slip_number;
3838
3839
3840
3841 end if;
3842
3843 if ((l_no_of_payroll_run > 0) and
3844 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3845
3846 begin
3847 open c_get_pri_addr(l_person_id,l_date_earned);
3848 fetch c_get_pri_addr into l_address_line1
3849 ,l_address_line2
3850 ,l_address_line3
3851 ,l_town_or_city
3852 ,l_province_code
3853 ,l_postal_code
3854 ,l_country_code;
3855 if c_get_pri_addr%NOTFOUND then
3856 open c_get_sec_addr(l_person_id,l_date_earned);
3857 fetch c_get_sec_addr into l_address_line1
3858 ,l_address_line2
3859 ,l_address_line3
3860 ,l_town_or_city
3861 ,l_province_code
3862 ,l_postal_code
3863 ,l_country_code;
3864 if c_get_sec_addr%NOTFOUND then
3865 pay_core_utils.push_message(800,'HR_74010_NO_RES_ADDRESS','A');
3866
3867 l_address_line1 := null;
3868 l_address_line2 := null;
3869 l_address_line3 := null;
3870 l_town_or_city := null;
3871 l_province_code := null;
3872 l_postal_code := null;
3873 l_telephone_number := null;
3874 l_country_code := null;
3875 end if;
3876 close c_get_sec_addr;
3877 end if; /* c_get_pri_addr%NOTFOUND */
3878 close c_get_pri_addr;
3879 end;
3880
3881 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info21
3882 := l_address_line1;
3883
3884 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info22
3885 := l_address_line2;
3886
3887 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info23
3888 := l_address_line3;
3889
3890 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info24
3891 := l_town_or_city;
3892
3893 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info25
3894 := l_province_code;
3895
3896 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info27
3897 := l_country_code;
3898
3899 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info26
3900 := l_postal_code;
3901
3902 /* Set the Negative Balance Flag for Archiving */
3903 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info30
3904 := l_negative_balance_exists;
3905
3906 end if;
3907
3908 /* Inserting rows into pay_action_information table
3909 RL2 Employee Data Archived */
3910
3911 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count >0 then
3912 archive_data_records(
3913 p_action_context_id => p_assactid
3914 ,p_action_context_type=> 'AAP'
3915 ,p_assignment_id => l_asgid
3916 ,p_tax_unit_id => l_rl2_tax_unit_id
3917 ,p_effective_date => p_effective_date
3918 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data);
3919 ln_index := null;
3920 end if;
3921
3922 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count >0 then
3923 archive_data_records(
3924 p_action_context_id => p_assactid
3925 ,p_action_context_type=> 'AAP'
3926 ,p_assignment_id => l_asgid
3927 ,p_tax_unit_id => l_rl2_tax_unit_id
3928 ,p_effective_date => p_effective_date
3929 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data);
3930 ln_footnote_index := null;
3931 end if;
3932
3933 --hr_utility.trace_on('Y','SAM');
3934 hr_utility.trace('Started Provincial YE Amendment');
3935
3936 select to_char(effective_date,'YYYY'),
3937 report_type,
3938 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
3939 legislative_parameters))
3940 into lv_fapp_effective_date,
3941 lv_fapp_report_type,
3942 ln_fapp_pre_org_id
3943 from pay_payroll_actions
3944 where payroll_action_id = l_payroll_action_id;
3945
3946 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3947
3948
3949 /* Archive the Pre-Printed form number for the RL2
3950 Amendment Pre-Process if one exists*/
3951
3952 ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count;
3953
3954 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).action_info_category
3955 := 'CAEOY RL2 EMPLOYEE INFO2';
3956
3957 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).jurisdiction_code
3958 := l_jurisdiction;
3959
3960 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info1
3961 := null;
3962 l_k := 0;
3963 open c_get_preprinted_form_no (l_person_id, ln_fapp_pre_org_id);
3964 loop
3965 fetch c_get_preprinted_form_no
3966 into lv_eit_year,
3967 lv_eit_pre_org_id,
3968 lv_eit_form_no;
3969
3970 exit when c_get_preprinted_form_no%NOTFOUND;
3971
3972 if ((lv_fapp_effective_date =
3973 to_char(fnd_date.canonical_to_date(lv_eit_year), 'YYYY')) and
3974 (ln_fapp_pre_org_id = to_number(lv_eit_pre_org_id))) then
3975
3976 if l_k = 0 then
3977 l_k := 1;
3978 lv_pre_pr_form_no := lv_eit_form_no;
3979 else
3980 lv_pre_pr_form_no := lv_pre_pr_form_no || '|' || lv_eit_form_no;
3981 end if;
3982 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info1
3983 := lv_pre_pr_form_no;
3984 end if;
3985
3986 end loop;
3987
3988 close c_get_preprinted_form_no;
3989 -- For Bug 8921055
3990 l_pre_printed_slip_no := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info1;
3991
3992 if lv_fapp_report_type = 'CAEOY_RL2_AMEND_PP' then
3993
3994 open c_get_fapp_locked_action_id(p_assactid);
3995 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
3996 close c_get_fapp_locked_action_id;
3997
3998 hr_utility.trace('RL2 Amend Action ID : '||to_char(p_assactid));
3999 hr_utility.trace('ln_fapp_locked_action_id :'|| to_char(ln_fapp_locked_action_id));
4000
4001 -- For Bug 8921055
4002 lv_fapp_flag := compare_archive_data(p_assactid,
4003 ln_fapp_locked_action_id,l_pre_printed_slip_no);
4004
4005 end if; -- report type validation for FAPP
4006
4007 hr_utility.trace('Archiving RL2 Amendment Flag : ' || lv_fapp_flag);
4008 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info2
4009 := lv_fapp_flag;
4010
4011 l_rl2_seq_number := gen_rl2_pdf_seq(p_assactid,
4012 to_char(p_effective_date,'YYYY'),
4013 'ARCHIVER');
4014 if ln_no_fi > ln_no_fi_per_slip then
4015 if mod(ln_no_fi,ln_no_fi_per_slip) = 0 then
4016 for l_i in 1..trunc(ln_no_fi/ln_no_fi_per_slip)-1
4017 loop
4018 l_rl2_seq_number := l_rl2_seq_number || '|' || gen_rl2_pdf_seq(p_assactid,
4019 to_char(p_effective_date,'YYYY'),
4020 'ARCHIVER');
4021 hr_utility.trace('l_rl2_seq_number -- '||l_rl2_seq_number);
4022 end loop;
4023 else
4024 for l_i in 1..trunc(ln_no_fi/ln_no_fi_per_slip)
4025 loop
4026 l_rl2_seq_number := l_rl2_seq_number || '|' || gen_rl2_pdf_seq(p_assactid,
4027 to_char(p_effective_date,'YYYY'),
4028 'ARCHIVER');
4029 hr_utility.trace('l_rl2_seq_number -- '||l_rl2_seq_number);
4030 end loop;
4031 end if;
4032 end if;
4033
4034 pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info3 := l_rl2_seq_number;
4035
4036 if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.count >0 then
4037 archive_data_records(
4038 p_action_context_id => p_assactid
4039 ,p_action_context_type=> 'AAP'
4040 ,p_assignment_id => l_asgid
4041 ,p_tax_unit_id => l_rl2_tax_unit_id
4042 ,p_effective_date => p_effective_date
4043 ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2);
4044 ln_index := null;
4045 end if;
4046
4047 hr_utility.trace('End of Provincial YE Amendment PP Validation');
4048
4049 end eoy_archive_data;
4050
4051
4052 -- Name : eoy_range_cursor
4053 -- Purpose : This returns the select statement that is used to created
4054 -- the range rows for the Year End Pre-Process.
4055 -- Arguments :
4056 -- Notes :
4057
4058
4059 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
4060
4061 l_pre_org_id varchar2(50);
4062 l_archive boolean:= FALSE;
4063 l_business_group number;
4064 l_year_start date;
4065 l_year_end date;
4066
4067 begin
4068
4069 select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
4070 legislative_parameters),
4071 trunc(effective_date,'Y'),
4072 effective_date,
4073 business_group_id
4074 into l_pre_org_id,
4075 l_year_start,
4076 l_year_end,
4077 l_business_group
4078 from pay_payroll_actions
4079 where payroll_action_id = pactid;
4080
4081 hr_utility.trace('in range cursor step 1');
4082
4083 sqlstr := 'select distinct asg.person_id
4084 from pay_all_payrolls_f ppy,
4085 pay_payroll_actions ppa,
4086 pay_assignment_actions paa,
4087 per_all_assignments_f asg,
4088 pay_payroll_actions ppa1
4089 where ppa1.payroll_action_id = :payroll_action_id
4090 and ppa.effective_date between
4091 fnd_date.canonical_to_date('''||
4092 fnd_date.date_to_canonical(l_year_start)||''') and
4093 fnd_date.canonical_to_date('''||
4094 fnd_date.date_to_canonical(l_year_end)||''')
4095 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
4096 and ppa.action_status = ''C''
4097 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
4098 and ppa.payroll_action_id = paa.payroll_action_id
4099 and paa.tax_unit_id in
4100 (select hoi.organization_id
4101 from hr_organization_information hoi
4102 where hoi.org_information_context = ''Canada Employer Identification''
4103 and hoi.org_information2 = '''|| l_pre_org_id ||''''||'
4104 and hoi.org_information5 = ''T4A/RL2'')
4105 and paa.action_status = ''C''
4106 and paa.assignment_id = asg.assignment_id
4107 and ppa.business_group_id = asg.business_group_id + 0
4108 and ppa.effective_date between asg.effective_start_date
4109 and asg.effective_end_date
4110 and asg.assignment_type = ''E''
4111 and ppa.payroll_id = ppy.payroll_id
4112 and ppy.business_group_id = '||to_char(l_business_group)||'
4113 and exists (select 1
4114 from pay_action_contexts pac,
4115 ff_contexts fc
4116 where pac.assignment_id = paa.assignment_id
4117 and pac.assignment_action_id = paa.assignment_action_id
4118 and pac.context_id = fc.context_id
4119 and fc.context_name = ''JURISDICTION_CODE''
4120 and pac.context_value = ''QC'' )
4121 order by asg.person_id';
4122
4123 l_archive := chk_gre_archive(pactid);
4124 if g_archive_flag = 'N' then
4125 hr_utility.trace('eoy_range_cursor archiving employer data');
4126 /* Now the archiver has provision for archiving
4127 payroll_action_level data . So make use of that */
4128 eoy_archive_gre_data(pactid,
4129 l_pre_org_id);
4130 hr_utility.trace('eoy_range_cursor archived employer data');
4131 end if;
4132
4133 end eoy_range_cursor;
4134 end pay_ca_eoy_rl2_archive;
[Home] [Help]