[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_RL1_ARCHIVE
Source
1 package body pay_ca_eoy_rl1_archive as
2 /* $Header: pycarlar.pkb 120.17 2008/01/10 16:41:30 sapalani noship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description : Package and procedure to build sql for payroll processes.
26
27 Change List
28
29 Date Name Vers Bug No Description
30
31 14-JAN-2000 M.Mukherjee 110.0 Created
32 18-JAN-2000 M.Mukherjee 110.1 Taken out trace_on and archiving
33 QBN at PA level
34 18-JAN-2000 M.Mukherjee 110.2 Archiving taxation year
35 21-JAN-2000 M.Mukherjee 110.3 Archiving BOXO balances
36 27-JAN-2000 M.Mukherjee 110.4 Put the check for not to take
37 put T4A earnings in Gross Earnings
38 which is Box A
39 31-JAN-2000 M.Mukherjee 110.4 If condition for T4A/Gross earning
40 combinations are written differently
41 31-JAN-2000 M.Mukherjee 110.5 Corrected condition so that it does
42 not archive employees who does not
43 have payroll runs for that QC_ID
44 02-FEB-2000 M.Mukherjee 110.6 Taking Quebec ID no 16 chars
45 every where and stopped archiving
46 non quebec employees
47 16-MAY-2000 M.Mukherjee 115.3 Changed the report_type to RL1
48 10-JUL-2000 V.PANDYA 115.4 Added Existance of record in
49 PAY_ACTION_CONTEXT for Juridiction Code
50 for QC in Range Cursor and Action
51 Creation query so it takes only those
52 employees who have been paid in Quebec.
53 18-AUG-2000 M.Mukherjee 115.5 Added RL1_SLIP_NUMBER archiving
54 31-AUG-2000 M.Mukherjee 115.6,115.7 Added footnote,CPP, BOXO archiving
55 18-SEP-2000 V.PANDYA 115.8 Remove assignment_id condition to get
56 asg.actid for more asgid.
57 20-SEP-2000 mmukherj 115.11 Corrected footnote archiving logic
58 20-SEP-2000 V.PANDYA 115.12 Corrected footnote archiving logic
59 18-OCT-2000 M.Mukherjee 115.13-15 Corrected footnote archiving logic and
60 slip number archiving logic.
61 20-OCT-2000 M.Mukherjee 115.16-17 Corrected slip number archiving logic.
62 30-OCT-2000 M.Mukherjee 115.18 Corrected slip number archiving logic
63 instead of global variable, a sequence
64 will be used , because global variable
65 is not effective across diff sessions
66 so it will not work in a multi threaded
67 environment.
68 30-OCT-2000 M.Mukherjee 115.19 balance_feeds are being checked for
69 that business group id.Bug1482190
70 06-DEC-2000 M.Mukherjee 115.20 added to_char(hoi.organization_id) in
71 c_eoy_qbin cursor, it was returning
72 invalid_number.
73 08-DEC-2000 M.Mukherjee 115.21 added business group in the select
74 queries, otherwise it will fetch
75 duplicate data.
76 08-DEC-2000 M.Mukherjee 115.22 changed comments double dash to
77 slash/star,
78 otherwise adchkdrv will fail
79 08-DEC-2000 VPandya 115.23,26 Trying to solve PI on driver
80 says to replace dashes
81 12-DEC-2000 MMukherjee 115.27 Added parameter l_has_been_paid to
82 avoid archiving 0 salary records.
83 13-DEC-2000 MMukherjee 115.28 Stopped assignment action creation
84 if the employee has not been paid
85 anything in that year, even though
86 there is payroll run.
87 29-DEC-2000 P.Ganguly 115.29 Added a check if Taxable Benefits
88 for Federal is present then subtract
89 it from the gross earnings.
90 08-NOV-2001 VPandya 115.30 Added QPP Basic Exemption, QPP Exempt
91 Earnings. Archiving RL1 NonBox Footnotes
92 in pay_action_information table.
93 10-NOV-2001 vpandya 115.31 Added set veify off at top as per GSCC.
94 12-NOV-2001 vpandya 115.32 Added dbdrv line.
95 27-DEC-2001 vpandya 115.33 Archiving new DBI
96 CAEOY_EMPLOYEE_DATE_OF_BIRTH
97 08-Jan-2002 vpandya 115.34 Archiving new DBI
98 CAEOY_EMPLOYEE_HIRE_DATE
99 02-Jul-2002 vpandya 115.36 Commented out below clause while getting
100 max-assignment action id query
101 AND pac1.assignment_id = l_asgid
102 01-Aug-2002 vpandya 115.37 Indention of archive_data and not include
103 status indian(BOXR) in to Gross Earnings
104 (BOXA).
105 10-Aug-2002 mmukherj 115.38 Bugfix for #2458533. The cursor
106 employer_info has been changed so that
107 it checks the business_group_id.
108 16-Aug-2002 vpandya 115.39 Bugfix for 2192914:archiving termination
109 date.
110 Archiving transmitter name instead of
111 transmitter org id in DBI
112 CAEOY_RL1_TRANSMITTER_NAME(ref.2192914)
113 21-Aug-2002 vpandya 115.40 Bugfix for 2449408:archiving DBI
114 CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE
115 Changed cursor employer_info, added
116 column org_information19 for Archiving
117 Accounting Resource Language, also given
118 alias to all information columns.
119 06-Oct-2002 vpandya 115.41 Changed archiver to archive Box-O
120 footnote.
121 08-Oct-2002 vpandya 115.43 Initializing variables l_footnote_amount
122 and l_footnote_amount_ue to avoid
123 duplicate archiving of footnotes.
124 22-Oct-2002 vpandya 115.44 Bug 2681250: changed cursor c_get_addr
125 of eoy_archive_data. If country is CA
126 take data from region_1 to get province
127 code and if it is US take data from
128 region_2 to get state code.
129 02-DEC-2002 vpandya 115.45 Added nocopy with out parameter
130 as per GSCC.
131 06-DEC-2002 vpandya 115.46 Bug 2698320,RL1 BOX-O codes RA to RZ
132 should be excluded from BOX A on the RL1.
133 Done using ln_boxo_exclude_from_boxa.
134 11-DEC-2002 vpandya 115.47 Bug 2698320,not excluding Box-O amount of
135 T4A/RL1 GRE from Box-A. Put this cond.
136 getting balance in ln_balance_value first
137 and summing up after for the same balance
138 for different GREs.
139 27-AUG-2003 ssouresr 115.49 If the balance 'RL1 No Gross Earnings' is
140 non zero then archiving takes place even
141 if Gross Earnings is zero.
142 Also the balance 'RL1 Non Taxable Earnings'
143 is deducted from Gross Earnings.
144 18-Sep-2003 vpandya 115.50 Archiving dates in canonical format
145 (YYYY/MM/DD HH:MI:SS) using
146 fnd_date.date_to_canonical_to_date
147 instead of using to_char with default
148 format to fix gscc date conversion error.
149 21-OCT-2003 ssouresr 115.51 Added RL1 Amendment Archiving logic
150 in eoy_archive_data procedure. Also
151 added new local function
152 compare_archive_data used for RL1
153 Amendment Archiver.
154 The organization_id of the Prov Reporting
155 Est will now be used instead of the QIN
156 04-NOV-2003 ssouresr 115.52 Converted the pre printed form number
157 select to a cursor as more than one
158 record can be returned
159 10-NOV-2003 ssouresr 115.53 Archiving pre printed form number both
160 for RL1 and RL1 Amendment. This will
161 ensure that the function
162 compare_archive_data compares the
163 correct data.
164 12-NOV-2003 ssouresr 115.54 Modified the function
165 compare_archive_data so that if the
166 number of archived items to be compared
167 is different then the amendment flag is
168 set to Y without checking all the
169 individual data records.
170 21-FEB-2004 pganguly 115.55 Fixed bug# 3459723. Changed the cursor
171 c_get_asg_id so that it picks
172 assignment of type 'E' only.
173 02-APR-2004 ssattini 115.56 11510 Changes to fix bug#3356533.
174 Added new cursor c_get_max_asg_act_id
175 in action_creation procedure. Modified
176 cursor c_all_gres_for_person and added
177 two new cursors c_get_max_asgactid_jd,
178 c_get_max_asgactid in eoy_archive_data
179 procedure.
180 23-APR-2004 ssouresr 115.57 Modified the cursor cur_non_box_mesg to
181 stop returning duplicate nonbox footnotes
182 06-JUN-2004 ssattini 115.60 Modified the cursors
183 c_get_max_asg_act_id,
184 c_get_max_asgactid_jd and
185 c_get_max_asgactid to get max asgact_id
186 based on person_id. Bug fix bug#3638928.
187 05-AUG-2004 ssouresr 115.61 Footnote codes for BoxQ can now be
188 archived Also added check to make sure
189 an appropriate error message is written
190 to the log if no transmitter has been
191 specified for the PRE. Bug#3353450.
192 05-AUG-2004 ssattini 115.62 Modified the cursor cur_non_box_mesg
193 to archive the balance adjustments
194 for Non-Box footnotes. Fix bug#3641353.
195 10-AUG-2004 ssouresr 115.63 Added the negative balance flag bug#3311402
196 Also modified the non box footnote logic
197 so that the amounts for identical footnote
198 codes are summed up bug#3641308
199 01-SEP-2004 ssouresr 115.64 BoxO can now have a negative balance
200 Bug 3863016, previously negative values
201 for this box were being ignored
202 02-SEP-2004 ssouresr 115.65 Changed to use the function get_parameter
203 to retrieve PRE_ORGANIZATION_ID
204 04-OCT-2004 ssouresr 115.66 The negative balance flag is archived as Y
205 when either a box or nonbox footnote is negative
206 05-NOV-2004 ssouresr 115.67 RL1 No Gross Earnings needs to be retrieved
207 across GREs
208 08-NOV-2004 ssouresr 115.68 All footnotes were reviewed.
209 BOXL and BOXO RW do not require any footnotes.
210 BOXO RX and BOX RY are not valid anymore. Also
211 BOXR has been changed to only have footnote
212 code 14 (Income from an office or employment)
213 17-NOV-2004 ssouresr 115.70 BoxO Code is now archived correctly
214 18-NOV-2004 ssouresr 115.71 Added BOXO_RR to list of balances to archive
215 19-NOV-2004 ssouresr 115.72 Footnotes for Gross Earnings(BOXA) are now archived
216 and archiving of BOXO_RZ has been removed
217 28-NOV-2004 ssouresr 115.73 Modified the cursor c_footnote_info to only return
218 RL1 footnotes, was previously returning RL2 footnotes
219 as well.
220 28-NOV-2004 ssouresr 115.74 Added date range to the latest assignment action cursor
221 29-NOV-2004 ssouresr 115.75 RL1 footnotes should be archived with Jurisdiction QC
222 30-NOV-2004 ssouresr 115.76 Archiving CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD for BoxG
223 02-FEB-2005 ssouresr 115.77 NonBox Footnotes with a value of 0 are not archived
224 04-MAR-2005 ssouresr 115.78 The archiver uses a new NonBox Footnote Element which
225 has a Jurisdiction input value from the beginning of 2006
226 26-APR-2005 ssouresr 115.79 The archiver will now recognize amendments made
227 only to non box footnotes
228 05-AUG-2005 saurgupt 115.80 Bug 4517693: Added Address_line3 for RL1 archiver.
229 08-AUG-2005 mmukherj 115.81 The procedure eoy_archinit has been
230 modified to set the minimum chunk
231 no, which is required to re archive
232 the data while retrying the Archiver
233 in the payroll action level.
234 Bugfix: #4525642
235 31-AUG-2005 ssouresr 115.82 New RL1 Nonbox footnote for Taxable Benefits without pay
236 27-SEP-2005 ssouresr 115.83 Corrected footnote condition in the function
237 compare_archive_data
238 10-NOV-2005 ssouresr 115.84 Added Footnote for BOXO RN
239 07-FEB-2006 ssouresr 115.85 Modified range cursor and main action creation
240 query to remove the table hr_soft_coding_keyflex
241 13-Apr-2006 ssmukher 115.86 Modified the sqlstr statement in eoy_range_cursor
242 procedure for Bug #5120627 fix
243 07-Aug-2006 ydevi 115.87 5096509..Archiver archives two PPIP EE Withheld
244 and PPIP EE Taxable into database itens
245 CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD
246 and CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD
247 respectively
248 18-AUG-2006 meshah 115.88 5202869 For performance reason changed the
249 query to remove per_people_f and
250 also disabled some indexes. With this
251 change the cost of the query
252 increases however now the path taken
253 is now more correct. Cursor
254 c_eoy_qbin has been changed.
255 28-AUG-2006 meshah 115.89 5495704 the way indexes were disabled has
256 been changed from using +0 to ||
257 16-Nov-2006 ydevi 115.90 5159150 archiving RL1_BOXV and RL1_BOXW
258 into db item CAEOY_RL1_BOXV_PER_JD_YTD
259 and CAEOY_RL1_BOXW_PER_JD_YTD
260 21-Dec-2006 ssmukher 115.91 5706335 Archiving BoxI value into DBI
261 CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD
262 05-Feb-2007 meshah 115.92 5768390 Removed the if condition that would
263 not populate boxA when the GRE type
264 is T4A/RL1
265 21-Aug-2007 amigarg 115.93 5558604 Added date track and enabled flag
266 condtion in c_footnote_info
267 21-Sep-2007 amigarg 115.95 6440125 added date track condition in employee
268 archiving
269 10-Jan-2008 sapalani 115.96 6525899 Added check to not to archive the
270 RL1_BOXO_AMOUNT_RW balance from 2007
271 */
272
273
274 sqwl_range varchar2(4000);
275 eoy_gre_range varchar2(4000);
276 eoy_all_qbin varchar2(4000);
277
278
279 /* Name : bal_db_item
280 Purpose : Given the name of a balance DB item as would be seen in a fast formula
281 it returns the defined_balance_id of the balance it represents.
282 Arguments :
283 Notes : A defined balance_id is required by the PLSQL balance function.
284 */
285
286 function bal_db_item
287 (
288 p_db_item_name varchar2
289 ) return number is
290
291 /* Get the defined_balance_id for the specified balance DB item. */
292
293 cursor csr_defined_balance is
294 select to_number(UE.creator_id)
295 from ff_user_entities UE,
296 ff_database_items DI
297 where DI.user_name = p_db_item_name
298 and UE.user_entity_id = DI.user_entity_id
299 and Ue.creator_type = 'B';
300
301 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
302
303 begin
304
305 open csr_defined_balance;
306 fetch csr_defined_balance into l_defined_balance_id;
307 if csr_defined_balance%notfound then
308 close csr_defined_balance;
309 raise hr_utility.hr_error;
310 else
311 close csr_defined_balance;
312 end if;
313
314 return (l_defined_balance_id);
315
316 end bal_db_item;
317
318
319 /* Name : get_dates
320 Purpose : The dates are dependent on the report being run
321 For T4 it is year end dates.
322
323 Arguments :
324 Notes :
325 */
326
327 procedure get_dates
328 (
329 p_report_type in varchar2,
330 p_effective_date in date,
331 p_period_end in out nocopy date,
332 p_quarter_start in out nocopy date,
333 p_quarter_end in out nocopy date,
334 p_year_start in out nocopy date,
335 p_year_end in out nocopy date
336 ) is
337 begin
338
339 if p_report_type = 'RL1' then
340
341 /* Year End Pre-process is a yearly process where the identifier
342 indicates the year eg. 1998. The expected values for the example
343 should be
344 p_period_end 31-DEC-1998
345 p_quarter_start 01-OCT-1998
346 p_quarter_end 31-DEC-1998
347 p_year_start 01-JAN-1998
348 p_year_end 31-DEC-1998
349 */
350
351 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
352 p_quarter_start := trunc(p_period_end, 'Q');
353 p_quarter_end := p_period_end;
354
355 /* For EOY */
356
357 end if;
358
359 p_year_start := trunc(p_effective_date, 'Y');
360 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
361
362 end get_dates;
363
364
365 /* Name : get_selection_information
366 Purpose : Returns information used in the selection of people to be reported on.
367 Arguments :
368
369 The following values are returned :
370
371 p_period_start - The start of the period over which to select
372 the people.
373 p_period_end - The end of the period over which to select
374 the people.
375 p_defined_balance_id - The balance which must be non zero for each
376 person to be included in the report.
377 p_group_by_gre - should the people be grouped by GRE.
378 p_group_by_medicare - Should the people ,be grouped by medicare
379 within GRE NB. this is not currently supported.
380 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
381 the testing of the balance.
382 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
383 for the testing of the balance.
384
385 Notes : This routine provides a way of coding explicit rules for
386 individual reports where they are different from the
387 standard selection criteria for the report type ie. in
388 NY state the selection of people in the 4th quarter is
389 different from the first 3.
390 */
391
392 procedure get_selection_information
393 (
394
395 /* Identifies the type of report, the authority for which it is being run,
396 and the period being reported. */
397 p_report_type varchar2,
398 p_quarter_start date,
399 p_quarter_end date,
400 p_year_start date,
401 p_year_end date,
402 /* Information returned is used to control the selection of people to
403 report on. */
404 p_period_start in out nocopy date,
405 p_period_end in out nocopy date,
406 p_defined_balance_id in out nocopy number,
407 p_group_by_gre in out nocopy boolean,
408 p_group_by_medicare in out nocopy boolean,
409 p_tax_unit_context in out nocopy boolean,
410 p_jurisdiction_context in out nocopy boolean
411 ) is
412
413 begin
414
415 /* Depending on the report being processed, derive all the information
416 required to be able to select the people to report on. */
417
418 if p_report_type = 'RL1' then
419
420 /* Default settings for Year End Preprocess. */
421
422 hr_utility.trace('in getting selection information ');
423 p_period_start := p_year_start;
424 p_period_end := p_year_end;
425 /* p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'); */
426 p_defined_balance_id := 0;
427 p_group_by_gre := FALSE;
428 p_group_by_medicare := FALSE;
429 p_tax_unit_context := FALSE;
430 p_jurisdiction_context := FALSE;
431
432 /* For EOY end */
433
434 /* An invalid report type has been passed so fail. */
435
436 else
437 hr_utility.trace('in error of getting selection information ');
438
439 raise hr_utility.hr_error;
440
441 end if;
442
443 end get_selection_information;
444
445
446 /* Name : lookup_jurisdiction_code
447 Purpose : Given a state code ie. AL it returns the jurisdiction code that
448 represents that state.
449 Arguments :
450 Notes :
451 */
452
453 function lookup_jurisdiction_code
454 (
455 p_state varchar2
456 ) return varchar2 is
457
458 /* Get the jurisdiction_code for the specified state code. */
459
460 cursor csr_jurisdiction_code is
461 select SR.jurisdiction_code
462 from pay_state_rules SR
463 where SR.state_code = p_state;
464
465 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
466
467 begin
468
469 open csr_jurisdiction_code;
470 fetch csr_jurisdiction_code into l_jurisdiction_code;
471 if csr_jurisdiction_code%notfound then
472 close csr_jurisdiction_code;
473 raise hr_utility.hr_error;
474 else
475 close csr_jurisdiction_code;
476 end if;
477
478 return (l_jurisdiction_code);
479
480 end lookup_jurisdiction_code;
481
482
483 /*
484 Name : get_user_entity_id
485 Purpose : This gets the user_entity_id for a specific database item name.
486 Arguments : p_dbi_name > database item name.
487 Notes :
488 */
489
490 function get_user_entity_id (p_dbi_name in varchar2)
491 return number is
492 l_user_entity_id number;
493
494 begin
495
496 select user_entity_id
497 into l_user_entity_id
498 from ff_database_items
499 where user_name = p_dbi_name;
500
501 return l_user_entity_id;
502
503 exception
504 when others then
505 hr_utility.trace('Error while getting the user_entity_id'
506 || p_dbi_name);
507 raise hr_utility.hr_error;
508
509 end get_user_entity_id;
510
511
512 /*
513 Name : get_footnote_user_entity_id
514 Purpose : This gets the user_entity_id for a specific database item name.
515 and it does not raise error if the the user entity is not found
516 Arguments : p_dbi_name > database item name.
517 Notes :
518 */
519
520 function get_footnote_user_entity_id (p_dbi_name in varchar2)
521 return number is
522 l_user_entity_id number;
523
524 begin
525
526 if p_dbi_name is not null then
527 begin
528 select user_entity_id
529 into l_user_entity_id
530 from ff_database_items
531 where user_name = p_dbi_name;
532
533 return l_user_entity_id;
534
535 exception
536 when others then
537 hr_utility.trace('skipping the record because no dbi of name:'
538 || p_dbi_name);
539 return 0;
540 end;
541 end if;
542
543 return 0;
544
545 end get_footnote_user_entity_id;
546
547 /*
548 Name : compare_archive_data
549 Purpose : compares Provincial YEPP data and Provincial YE Amendment Data
550 Arguments : p_assignment_action_id -> Assignment_action_id
551 p_locked_action_id -> YEPP Assignment_action_id
552 p_jurisdiction -> Jurisdiction_code
553
554 Notes : Used for Provincial YE Amendment Pre-Process (YE-2003)
555 */
556
557 FUNCTION compare_archive_data(p_assignment_action_id in number,
558 p_locked_action_id in number,
559 p_jurisdiction in varchar2)
560 RETURN VARCHAR2 IS
561 TYPE act_info_rec IS RECORD
562 (archive_context1 number(25),
563 archive_ue_id number(25),
564 archive_value varchar2(240));
565
566 TYPE footnote_rec IS RECORD
567 (message varchar2(240)
568 ,value varchar2(240));
569
570 TYPE number_data_type_table IS TABLE OF NUMBER
571 INDEX BY BINARY_INTEGER;
572
573 TYPE action_info_table IS TABLE OF act_info_rec
574 INDEX BY BINARY_INTEGER;
575
576 TYPE footnote_table IS TABLE OF footnote_rec
577 INDEX BY BINARY_INTEGER;
578
579 ltr_amend_arch_data action_info_table;
580 ltr_yepp_arch_data action_info_table;
581 ltr_amend_emp_data action_info_table;
582 ltr_yepp_emp_data action_info_table;
583 ltr_emp_ue_id number_data_type_table;
584
585 ltr_amend_footnote footnote_table;
586 ltr_yepp_footnote footnote_table;
587 ln_yepp_footnote_count number;
588 ln_amend_footnote_count number;
589
590 cursor c_get_nonbox_footnote(cp_asg_act_id number) is
591 select action_information4,
592 action_information5
593 from pay_action_information
594 where action_context_id = cp_asg_act_id
595 and action_information_category = 'CA FOOTNOTES'
596 and action_context_type = 'AAP'
597 and action_information6 = 'RL1'
598 order by action_information4;
599
600 -- Cursor to get archived values based on Asg_act_id,jurisdiction
601 CURSOR c_get_emp_rl1box_data(cp_asg_act_id number) IS
602 SELECT fai1.context1,
603 fdi1.user_entity_id,
604 fai1.value
605 FROM ff_archive_items fai1,
606 ff_database_items fdi1,
607 ff_archive_item_contexts faic,
608 ff_contexts fc
609 WHERE fai1.user_entity_id = fdi1.user_entity_id
610 AND fai1.archive_item_id = faic.archive_item_id
611 AND fc.context_id = faic.context_id
612 AND fc.context_name = 'JURISDICTION_CODE'
613 AND faic.context = 'QC'
614 AND fai1.CONTEXT1 = cp_asg_act_id
615 AND fdi1.user_name <> 'CAEOY_RL1_AMENDMENT_FLAG'
616 ORDER BY fdi1.user_name;
617
618 -- Cursor to get archived values based on Asg_act_id
619 CURSOR c_get_employee_data(cp_asg_act_id number,
620 cp_dbi_ue_id number) IS
621 SELECT fai.context1,fai.user_entity_id,fai.value
622 FROM ff_archive_items fai
623 WHERE fai.user_entity_id = cp_dbi_ue_id
624 AND fai.context1 = cp_asg_act_id;
625
626 i number;
627 j number;
628 ln_box number;
629 ln_amend_box number;
630
631 lv_flag varchar2(2):= 'N';
632
633 begin
634
635 /* Initialization Process */
636 if ltr_amend_arch_data.count > 0 then
637 ltr_amend_arch_data.delete;
638 end if;
639
640 if ltr_yepp_arch_data.count > 0 then
641 ltr_yepp_arch_data.delete;
642 end if;
643
644 if ltr_amend_emp_data.count > 0 then
645 ltr_amend_emp_data.delete;
646 end if;
647
648 if ltr_yepp_emp_data.count > 0 then
649 ltr_yepp_emp_data.delete;
650 end if;
651
652 if ltr_emp_ue_id.count > 0 then
653 ltr_emp_ue_id.delete;
654 end if;
655
656 if ltr_amend_footnote.count > 0 then
657 ltr_amend_footnote.delete;
658 end if;
659
660 if ltr_yepp_footnote.count > 0 then
661 ltr_yepp_footnote.delete;
662 end if;
663
664 j := 0;
665
666 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_INITIAL');
667
668 j := j+1;
669 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_FIRST_NAME');
670
671 j := j+1;
672 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_LAST_NAME');
673
674 j := j+1;
675 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_SIN');
676
677 j := j+1;
678 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_NUMBER');
679
680 j := j+1;
681 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE1');
682
683 j := j+1;
684 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE2');
685
686 j := j+1;
687 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE3');
688
689 j := j+1;
690 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_CITY');
691
692 j := j+1;
693 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_PROVINCE');
694
695 j := j+1;
696 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_COUNTRY');
697
698 j := j+1;
699 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_POSTAL_CODE');
700
701 /* Populate RL1 Amendment Box Data for an assignment_action */
702 open c_get_emp_rl1box_data(p_assignment_action_id);
703 hr_utility.trace('Populating RL1 Amendment Box Data ');
704 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
705 ln_amend_box := 0;
706 loop
707 fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
708 exit when c_get_emp_rl1box_data%NOTFOUND;
709
710 hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
711 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
712 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
713 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
714 ln_amend_box := ln_amend_box + 1;
715 end loop;
716
717 close c_get_emp_rl1box_data;
718
719 /* Populate RL1 Amendment Employee Data for an assignment_action */
720 hr_utility.trace('Populating Amendment Employee Data ');
721 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
722 for i in 0 .. j
723 loop
724 open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
725 fetch c_get_employee_data into ltr_amend_emp_data(i);
726
727 hr_utility.trace('I :'||to_char(i));
728 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
729 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
730 hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
731
732 close c_get_employee_data;
733 end loop;
734
735
736 /* Populate RL1 YEPP Box Data for an assignment_action */
737 open c_get_emp_rl1box_data(p_locked_action_id);
738 hr_utility.trace('Populating RL1 YEPP Box Data ');
739 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
740 ln_box := 0;
741 loop
742 fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
743 exit when c_get_emp_rl1box_data%NOTFOUND;
744
745 hr_utility.trace('ln_box :'||to_char(ln_box));
746 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
747 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
748 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
749 ln_box := ln_box + 1;
750 end loop;
751
752 close c_get_emp_rl1box_data;
753
754 /* Populate RL1 YEPP Employee Data for an assignment_action */
755 hr_utility.trace('Populating YEPP Employee Data ');
756 hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
757
758 for i in 0 .. j
759 loop
760 open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
761 fetch c_get_employee_data into ltr_yepp_emp_data(i);
762 exit when c_get_employee_data%NOTFOUND;
763
764 hr_utility.trace('I :'||to_char(i));
765 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
766 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
767 hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
768
769 close c_get_employee_data;
770 end loop;
771
772 /* Populate RL1 Amendment Footnotes */
773 open c_get_nonbox_footnote(p_assignment_action_id);
774
775 hr_utility.trace('Populating RL1 Amendment Footnote ');
776
777 ln_amend_footnote_count := 0;
778 loop
779 fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
780 exit when c_get_nonbox_footnote%NOTFOUND;
781
782 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
783 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
784
785 ln_amend_footnote_count := ln_amend_footnote_count + 1;
786 end loop;
787
788 close c_get_nonbox_footnote;
789
790 /* Populate RL1 YEPP Footnotes */
791 open c_get_nonbox_footnote(p_locked_action_id);
792
793 ln_yepp_footnote_count := 0;
794 loop
795 fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
796 exit when c_get_nonbox_footnote%NOTFOUND;
797
798 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
799 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
800
801 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
802 end loop;
803
804 close c_get_nonbox_footnote;
805
806
807 /* Compare RL1 Amendment Box Data and RL1 YEPP Box Data for an
808 assignment_action */
809
810 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Box Data ');
811
812 if ln_box <> ln_amend_box then
813 lv_flag := 'Y';
814 elsif ln_box = ln_amend_box then
815 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
816 loop
817 if (ltr_yepp_arch_data(i).archive_ue_id =
818 ltr_amend_arch_data(i).archive_ue_id) then
819
820 if ((ltr_yepp_arch_data(i).archive_value <>
821 ltr_amend_arch_data(i).archive_value) or
822 (ltr_yepp_arch_data(i).archive_value is null and
823 ltr_amend_arch_data(i).archive_value is not null) or
824 (ltr_yepp_arch_data(i).archive_value is not null and
825 ltr_amend_arch_data(i).archive_value is null)) then
826
827 lv_flag := 'Y';
828 hr_utility.trace('Archive_UE_id with differnt value :'||
829 to_char(ltr_yepp_arch_data(i).archive_ue_id));
830 exit;
831 end if;
832
833 end if;
834
835 end loop;
836
837 end if;
838
839 /* Compare RL1 Employee Data and RL1 YEPP Employee Data for an
840 assignment_action */
841 If lv_flag <> 'Y' then
842
843 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Employee Data ');
844 for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
845 loop
846 if (ltr_yepp_emp_data(i).archive_ue_id =
847 ltr_amend_emp_data(i).archive_ue_id) then
848
849 hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
850 ltr_yepp_emp_data(i).archive_value);
851 hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
852 ltr_amend_emp_data(i).archive_value);
853
854 if ((ltr_yepp_emp_data(i).archive_value <>
855 ltr_amend_emp_data(i).archive_value) or
856 (ltr_yepp_emp_data(i).archive_value is null and
857 ltr_amend_emp_data(i).archive_value is not null) or
858 (ltr_yepp_emp_data(i).archive_value is not null and
859 ltr_amend_emp_data(i).archive_value is null)) then
860
861 lv_flag := 'Y';
862 hr_utility.trace('Archive_UE_id with different value :'||
863 to_char(ltr_yepp_arch_data(i).archive_ue_id));
864 exit;
865 end if;
866
867 end if;
868 end loop;
869
870 End if; -- p_flag <> 'Y'
871
872 /* Compare RL1 Amendment Footnotes and RL1 YEPP Footnotes for an
873 assignment_action */
874
875 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Footnotes');
876
877 if lv_flag <> 'Y' then
878
879 if ln_yepp_footnote_count <> ln_amend_footnote_count then
880 lv_flag := 'Y';
881 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
882 (ln_yepp_footnote_count <> 0)) then
883 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
884 loop
885 if (ltr_yepp_footnote(i).message =
886 ltr_amend_footnote(i).message) then
887
888 if ((ltr_yepp_footnote(i).value <>
889 ltr_amend_footnote(i).value) or
890 (ltr_yepp_footnote(i).value is null and
891 ltr_amend_footnote(i).value is not null) or
892 (ltr_yepp_footnote(i).value is not null and
893 ltr_amend_footnote(i).value is null)) then
894
895 lv_flag := 'Y';
896 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
897 exit;
898 end if;
899 end if;
900 end loop;
901 end if;
902
903 end if;
904
905 /* If there is no value difference for Entire Employee data then set
906 flag to 'N' */
907
908 if lv_flag <> 'Y' then
909 lv_flag := 'N';
910 hr_utility.trace('No value difference for an Employee Asg Action: '||
911 to_char(p_assignment_action_id));
912 end if;
913
914 hr_utility.trace('lv_flag :'||lv_flag);
915 return lv_flag;
916
917 end compare_archive_data;
918
919
920 /*
921 Name : eoy_action_creation
922 Purpose : This creates the assignment actions for a specific chunk
923 of people to be archived by the year end preprocess.
924 Arguments :
925 Notes :
926 */
927
928 procedure eoy_action_creation(pactid in number,
929 stperson in number,
930 endperson in number,
931 chunk in number) is
932
933
934
935 /* Variables used to hold the select columns from the SQL statement.*/
936
937 l_person_id number;
938 l_assignment_id number;
939 l_tax_unit_id number;
940 l_eoy_tax_unit_id number;
941 l_effective_end_date date;
942 l_object_version_number number;
943 l_some_warning boolean;
944 l_counter number;
945 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
946 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
947 l_user_entity_name varchar2(240);
948
949 /* Variables used to hold the values used as bind variables within the
950 SQL statement. */
951
952 l_bus_group_id number;
953 l_period_start date;
954 l_period_end date;
955
956 /* Variables used to hold the details of the payroll and assignment actions
957 that are created. */
958
959 l_payroll_action_created boolean := false;
960 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
961 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
962 l_archive_item_id number;
963
964 /* Variable holding the balance to be tested. */
965
966 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
967
968 /* Indicator variables used to control how the people are grouped. */
969
970 l_group_by_gre boolean := FALSE;
971 l_group_by_medicare boolean := FALSE;
972
973 /* Indicator variables used to control which contexts are set up for
974 balance. */
975
976 l_tax_unit_context boolean := FALSE;
977 l_jurisdiction_context boolean := FALSE;
978
979 /* Variables used to hold the current values returned within the loop for
980 checking against the new values returned from within the loop on the
981 next iteration. */
982
983 l_prev_person_id per_all_people_f.person_id%type;
984 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
985
986 /* Variable to hold the jurisdiction code used as a context for state
987 reporting. */
988
989 l_jurisdiction_code varchar2(30);
990
991 /* general process variables */
992
993 l_report_type pay_payroll_actions.report_type%type;
994 l_province pay_payroll_actions.report_qualifier%type;
995 l_value number;
996 l_effective_date date;
997 l_quarter_start date;
998 l_quarter_end date;
999 l_year_start date;
1000 l_year_end date;
1001 lockingactid number;
1002 l_max_aaid number;
1003 l_pre_organization_id varchar2(17);
1004 l_prev_pre_organization_id varchar2(17);
1005 l_primary_asg pay_assignment_actions.assignment_id%type;
1006 ln_no_gross_earnings number;
1007 ln_nontaxable_earnings number;
1008
1009
1010 /* For Year End Preprocess we have to archive the assignments
1011 belonging to a GRE */
1012
1013 /* For Year End Preprocess we can also archive the assignments
1014 belonging to all GREs */
1015 /*
1016 CURSOR c_eoy_qbin IS
1017 SELECT ASG.person_id person_id,
1018 ASG.assignment_id assignment_id,
1019 ASG.effective_end_date effective_end_date
1020 FROM per_all_assignments_f ASG,
1021 pay_all_payrolls_f PPY,
1022 hr_soft_coding_keyflex SCL
1023 WHERE ASG.business_group_id + 0 = l_bus_group_id
1024 AND ASG.person_id between stperson and endperson
1025 AND ASG.assignment_type = 'E'
1026 AND ASG.effective_start_date <= l_period_end
1027 AND ASG.effective_end_date >= l_period_start
1028 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1029 AND (
1030 (rtrim(ltrim(SCL.segment1)) in
1031 (select to_char(hoi.organization_id)
1032 from hr_organization_information hoi
1033 where hoi.org_information_context = 'Canada Employer Identification'
1034 and hoi.org_information2 = l_pre_organization_id))
1035 or
1036 (rtrim(ltrim(SCL.segment11)) in
1037 (select to_char(hoi.organization_id)
1038 from hr_organization_information hoi
1039 where hoi.org_information_context = 'Canada Employer Identification'
1040 and hoi.org_information2 = l_pre_organization_id))
1041 )
1042 AND PPY.payroll_id = ASG.payroll_id
1043 and exists ( select 'X' from pay_action_contexts pac, ff_contexts fc
1044 where pac.assignment_id = asg.assignment_id
1045 and pac.context_id = fc.context_id
1046 and fc.context_name = 'JURISDICTION_CODE'
1047 and pac.context_value = 'QC' )
1048 ORDER BY 1, 3 DESC, 2; */
1049
1050 /* bug 5202869. For performance reason changed the query to remove per_people_f
1051 and also disabled some indexes. With this change the cost of the query
1052 increases however now the path taken is now more correct.
1053 */
1054 CURSOR c_eoy_qbin IS
1055 SELECT asg.person_id person_id,
1056 asg.assignment_id assignment_id,
1057 asg.effective_end_date effective_end_date
1058 FROM per_all_assignments_f asg,
1059 pay_assignment_actions paa,
1060 pay_payroll_actions ppa
1061 WHERE ppa.effective_date between l_period_start
1062 and l_period_end
1063 AND ppa.action_type in ('R','Q','V','B','I')
1064 AND ppa.business_group_id +0 = l_bus_group_id
1065 AND ppa.payroll_action_id = paa.payroll_action_id
1066 AND paa.tax_unit_id in (select hoi.organization_id
1067 from hr_organization_information hoi
1068 where hoi.org_information_context ||''= 'Canada Employer Identification'
1069 and hoi.org_information2 = l_pre_organization_id
1070 and hoi.org_information5 in ('T4/RL1','T4A/RL1'))
1071 AND paa.assignment_id = asg.assignment_id
1072 AND ppa.business_group_id = asg.business_group_id +0
1073 AND asg.person_id between stperson and endperson
1074 AND asg.assignment_type = 'E'
1075 AND ppa.effective_date between asg.effective_start_date
1076 and asg.effective_end_date
1077 AND EXISTS (select 1
1078 from pay_action_contexts pac,
1079 ff_contexts fc
1080 where pac.assignment_id = paa.assignment_id
1081 and pac.assignment_action_id = paa.assignment_action_id
1082 and pac.context_id = fc.context_id
1083 and fc.context_name ||'' = 'JURISDICTION_CODE'
1084 and pac.context_value ||'' = 'QC')
1085 ORDER BY 1, 3 DESC, 2;
1086
1087 cursor c_all_qbin_gres is
1088 select hoi.organization_id
1089 from hr_organization_information hoi
1090 where hoi.org_information_context = 'Canada Employer Identification'
1091 and hoi.org_information2 = l_pre_organization_id;
1092
1093 /* Get the primary assignment for the given person_id */
1094
1095 CURSOR c_get_asg_id (p_person_id number) IS
1096 SELECT assignment_id
1097 from per_all_assignments_f paf
1098 where person_id = p_person_id
1099 and primary_flag = 'Y'
1100 and assignment_type = 'E'
1101 and paf.effective_start_date <= l_period_end
1102 and paf.effective_end_date >= l_period_start
1103 ORDER BY assignment_id desc;
1104
1105 /* Added cursor for 11510 Changes Bug#3356533. Changed cursor to get
1106 max asgact_id based on person_id, fix for bug#3638928. */
1107 CURSOR c_get_max_asg_act_id(cp_person_id number,
1108 cp_tax_unit_id number,
1109 cp_period_start date,
1110 cp_period_end date) IS
1111 select paa.assignment_action_id
1112 from pay_assignment_actions paa,
1113 per_all_assignments_f paf,
1114 per_all_people_f ppf,
1115 pay_payroll_actions ppa,
1116 pay_action_classifications pac
1117 where ppf.person_id = cp_person_id
1118 and paf.person_id = ppf.person_id
1119 and paa.assignment_id = paf.assignment_id
1120 and paa.tax_unit_id = cp_tax_unit_id
1121 and ppa.payroll_action_id = paa.payroll_action_id
1122 and ppa.effective_date between cp_period_start and cp_period_end
1123 and ppa.effective_date between ppf.effective_start_date
1124 and ppf.effective_end_date
1125 and ppa.effective_date between paf.effective_start_date
1126 and paf.effective_end_date
1127 and ppa.action_type = pac.action_type
1128 and pac.classification_name = 'SEQUENCED'
1129 order by paa.action_sequence desc;
1130
1131 begin
1132
1133 /* Get the report type, report qualifier, business group id and the
1134 gre for which the archiving has to be done */
1135
1136 hr_utility.trace('getting report type ');
1137
1138 select effective_date,
1139 report_type,
1140 business_group_id,
1141 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
1142 legislative_parameters)
1143 into l_effective_date,
1144 l_report_type,
1145 l_bus_group_id,
1146 l_pre_organization_id
1147 from pay_payroll_actions
1148 where payroll_action_id = pactid;
1149
1150 hr_utility.trace('getting dates');
1151
1152 get_dates(l_report_type,
1153 l_effective_date,
1154 l_period_end,
1155 l_quarter_start,
1156 l_quarter_end,
1157 l_year_start,
1158 l_year_end);
1159
1160 hr_utility.trace('getting selection information');
1161 hr_utility.trace('report type '|| l_report_type);
1162 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1166
1167 get_selection_information
1168 (l_report_type,
1169 l_quarter_start,
1170 l_quarter_end,
1171 l_year_start,
1172 l_year_end,
1173 l_period_start,
1174 l_period_end,
1175 l_defined_balance_id,
1176 l_group_by_gre,
1177 l_group_by_medicare,
1178 l_tax_unit_context,
1179 l_jurisdiction_context);
1180
1181 hr_utility.trace('Out of get selection information');
1182 open c_eoy_qbin;
1183
1184 /* Loop for all rows returned for SQL statement. */
1185
1186 hr_utility.trace('Entering loop');
1187
1188 loop
1189
1190 fetch c_eoy_qbin
1191 into l_person_id,
1192 l_assignment_id,
1193 l_effective_end_date;
1194
1195 exit when c_eoy_qbin%NOTFOUND;
1196
1197
1198 /* If the new row is the same as the previous row according to the way
1199 the rows are grouped then discard the row ie. grouping by PRE
1200 organization id requires a single row for each person / PRE
1201 combination. */
1202
1203 hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1204 hr_utility.trace('previous pre_organization_id is '||
1205 l_prev_pre_organization_id);
1206 hr_utility.trace('person_id is '||
1207 to_char(l_person_id));
1208 hr_utility.trace('previous person_id is '||
1209 to_char(l_prev_person_id));
1210
1211 if (l_person_id = l_prev_person_id and
1212 l_pre_organization_id = l_prev_pre_organization_id) then
1213
1214 hr_utility.trace('Not creating assignment action');
1215
1216 else
1217 /* Check whether the person has 0 payment or not */
1218
1219 l_value := 0;
1220 ln_no_gross_earnings := 0;
1221 ln_nontaxable_earnings := 0;
1222
1223 open c_all_qbin_gres;
1224 loop
1225 fetch c_all_qbin_gres into l_tax_unit_id;
1226 exit when c_all_qbin_gres%NOTFOUND;
1227
1228 /* select the maximum assignment action id, removed the select stmt
1229 and replaced it with cursor c_get_max_asg_act_id 11510 Changes
1230 Bug#3356533. Passing person_id to fix bug#3638928 */
1231 begin
1232 open c_get_max_asg_act_id(l_person_id,
1233 l_tax_unit_id,
1234 l_period_start,
1235 l_period_end);
1236 fetch c_get_max_asg_act_id into l_max_aaid;
1237 if c_get_max_asg_act_id%NOTFOUND then
1238 l_max_aaid := -9999;
1239 end if;
1240 close c_get_max_asg_act_id;
1241
1242 end;
1243
1244 if l_max_aaid <> -9999 then
1245 l_value := l_value +
1246 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1247 ('Gross Earnings',
1248 'YTD' ,
1249 l_max_aaid,
1250 l_assignment_id ,
1251 NULL,
1252 'PER' ,
1253 l_tax_unit_id,
1254 l_bus_group_id,
1255 'QC'
1256 ),0) ;
1257
1258 ln_no_gross_earnings := ln_no_gross_earnings +
1259 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1260 ('RL1 No Gross Earnings',
1261 'YTD' ,
1262 l_max_aaid,
1263 l_assignment_id ,
1264 NULL,
1265 'PER' ,
1266 l_tax_unit_id,
1267 l_bus_group_id,
1268 'QC'
1269 ),0);
1270
1271 ln_nontaxable_earnings := ln_nontaxable_earnings +
1272 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1273 ('RL1 Non Taxable Earnings',
1274 'YTD' ,
1275 l_max_aaid,
1276 l_assignment_id ,
1277 NULL,
1278 'PER' ,
1279 l_tax_unit_id,
1280 l_bus_group_id,
1281 'QC'
1282 ),0);
1283 end if; /* end l_max_id <> -9999 */
1284 end loop;
1285 close c_all_qbin_gres;
1286 /* end of checking whether the person has 0 payment */
1287
1288 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1289 hr_utility.trace('person is '|| to_char(l_person_id));
1290 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1291
1292
1293 /* Have a new unique row according to the way the rows are grouped.
1294 The inclusion of the person is dependent on having a non zero
1295 balance.
1296 If the balance is non zero then an assignment action is created to
1297 indicate their inclusion in the magnetic tape report. */
1298
1299 /* Set up the context of tax unit id */
1300
1301 hr_utility.trace('Setting context');
1302
1303 /* Only create assignment actions if Gross Earnings are not 0 and are not
1304 made up of only nontaxable earnings or the No Gross Earnings balance is
1305 non zero */
1306
1307 if (((l_value <> 0) and
1308 (ln_nontaxable_earnings <> l_value)) or
1309 (ln_no_gross_earnings <> 0)) then
1310
1311 /* Get the primary assignment */
1312 open c_get_asg_id(l_person_id);
1313 fetch c_get_asg_id into l_primary_asg;
1314
1315 if c_get_asg_id%NOTFOUND then
1316 close c_get_asg_id;
1317 raise hr_utility.hr_error;
1318 else
1319 close c_get_asg_id;
1320 end if;
1321
1322 /* Create the assignment action to represnt the person / tax unit
1323 combination. */
1324
1325 select pay_assignment_actions_s.nextval
1326 into lockingactid
1327 from dual;
1328
1329 /* Insert into pay_assignment_actions. */
1330
1331 hr_utility.trace('creating assignment action');
1332
1333 /* Passing tax unit id as null */
1334
1335 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1336 pactid,chunk,null);
1337
1338 /* Update the serial number column with the person id
1339 so that the mag routine and the RL1 view will not have
1340 to do an additional checking against the assignment
1341 table */
1342
1343 hr_utility.trace('updating assignment action' || to_char(lockingactid));
1344
1345 update pay_assignment_actions aa
1346 set aa.serial_number = to_char(l_person_id)
1347 where aa.assignment_action_id = lockingactid;
1348
1349
1350 /* Since the API checks the presence of a row in pay_report_format_items for
1351 action type AAC and PA , check it here also to avoid API error */
1352 /*
1353 l_counter := 1;
1354 l_user_entity_name := 'CAEOY_RL1_QUEBEC_BN';
1355
1356 hr_utility.trace('Archiving AAC level data for ' || to_char(lockingactid));
1357 ff_archive_api.create_archive_item(
1358 p_archive_item_id => l_archive_item_id
1359 ,p_user_entity_id => get_user_entity_id(l_user_entity_name)
1360 ,p_archive_value => l_pre_organization_id
1361 ,p_archive_type => 'AAC'
1362 ,p_action_id => lockingactid
1363 ,p_legislation_code => 'CA'
1364 ,p_object_version_number => l_object_version_number
1365 ,p_some_warning => l_some_warning
1366 );
1367 hr_utility.trace('Archived AAC level data');
1368 */
1369
1370 /* I have to enter data in new archive table also with archive type as AAC, ie
1371 assignment_action_creation */
1372 end if; /* end of l_value <> 0 OR ln_no_gross_earnings <> 0 */
1373 end if; /* end of l_person_id <> l_prev_person_id */
1374
1375 /* Record the current values for the next time around the loop. */
1376
1377 l_prev_person_id := l_person_id;
1378 l_prev_pre_organization_id := l_pre_organization_id;
1379
1380 end loop;
1381
1382 hr_utility.trace('Action creation done');
1383 close c_eoy_qbin;
1384
1385 end eoy_action_creation;
1386
1387
1388
1389 /* Name : eoy_get_jursd_level
1390 Purpose : This returns the jurisdiction level of the non balance
1391 database items.
1392 Arguments :
1393 Notes :
1394 */
1395
1396 function eoy_get_jursd_level(p_route_id number,
1397 p_user_entity_id number) return number is
1398 l_jursd_value number:= 0;
1399
1400 begin
1401
1402 select frpv.value
1403 into l_jursd_value
1404 from ff_route_parameter_values frpv,
1405 ff_route_parameters frp
1406 where frpv.route_parameter_id = frp.route_parameter_id
1407 and frpv.user_entity_id = p_user_entity_id
1408 and frp.route_id = p_route_id
1409 and frp.parameter_name = 'Jursd. Level';
1410
1411 return(l_jursd_value);
1412
1413 exception
1414 when no_data_found then
1415 return(0);
1416 when others then
1417 hr_utility.trace('Error while getting the jursd. value ' ||
1418 to_char(sqlcode));
1419
1420 end eoy_get_jursd_level;
1421
1422
1423
1424 /* Name : eoy_archive_gre_data
1425 Purpose : This performs the CA specific employer data archiving.
1426 Arguments :
1427 Notes :
1428 */
1429
1430 PROCEDURE eoy_archive_gre_data(p_payroll_action_id in number,
1431 p_pre_organization_id in varchar2)
1432 IS
1433
1434 l_user_entity_id number;
1435 l_taxunit_context_id number;
1436 l_jursd_context_id number;
1437 l_value varchar2(240);
1438 l_sit_uid number;
1439 l_sui_uid number;
1440 l_fips_uid number;
1441 l_seq_tab pay_ca_eoy_rl1_archive.number_data_type_table;
1442 l_context_id_tab pay_ca_eoy_rl1_archive.number_data_type_table;
1443 l_context_val_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1444 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1445 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1446 l_arch_gre_step number := 0;
1447 l_archive_item_id number;
1448 l_town_or_city varchar2(240);
1449 l_province_code varchar2(240);
1450 l_postal_code varchar2(240);
1451 l_organization_id_of_qin number;
1452 l_transmitter_org_id number;
1453 l_country_code varchar2(240);
1454 l_transmitter_name varchar2(240);
1455 l_Transmitter_Type_Indicator varchar2(240);
1456 l_transmitter_gre_ind varchar2(240);
1457 l_Transmitter_number varchar2(240);
1458 l_transmitter_addr_line_1 varchar2(240);
1459 l_transmitter_addr_line_2 varchar2(240);
1460 l_transmitter_addr_line_3 varchar2(240);
1461 l_transmitter_city varchar2(240);
1462 l_transmitter_province varchar2(240);
1463 l_transmitter_postal_code varchar2(240);
1464 l_transmitter_country varchar2(240);
1465 l_rl_data_type varchar2(240);
1466 l_rl_package_type varchar2(240);
1467 l_rl_source_of_slips varchar2(240);
1468 l_technical_contact_name varchar2(240);
1469 l_technical_contact_phone varchar2(240);
1470 l_technical_contact_area_code varchar2(240);
1471 l_technical_contact_extension varchar2(240);
1472 l_technical_contact_language varchar2(240);
1473 l_accounting_contact_name varchar2(240);
1474 l_accounting_contact_phone varchar2(240);
1475 l_accounting_contact_area_code varchar2(240);
1476 l_accounting_contact_extension varchar2(240);
1477 l_accounting_contact_language varchar2(240);
1478 l_proprietor_sin varchar2(240);
1479 l_name varchar2(240);
1480 l_employer_ein varchar2(240);
1481 l_address_line_1 varchar2(240);
1482 l_address_line_2 varchar2(240);
1483 l_address_line_3 varchar2(240);
1484 l_counter number := 0;
1485 l_object_version_number number;
1486 l_business_group_id varchar2(240);
1487 l_some_warning boolean;
1488 l_step number := 0;
1489 l_taxation_year varchar2(4);
1490 l_rl1_last_slip_number number ;
1491 l_employer_info_found varchar2(1);
1492 l_max_slip_number varchar2(80);
1493
1494 cursor employer_info is
1495 select target1.organization_id,
1496 target2.name,
1497 target2.business_group_id,
1498 target1.ORG_INFORMATION2 Prov_Identi_Number,
1499 target1.ORG_INFORMATION7 Type_of_Transmitter,
1500 target1.ORG_INFORMATION5 Transmitter_Number,
1501 target1.ORG_INFORMATION4 Type_of_Data,
1502 target1.ORG_INFORMATION6 Type_of_Package,
1503 target1.ORG_INFORMATION8 Source_of_RL_slips_used,
1504 target1.ORG_INFORMATION9 Tech_Res_Person_Name,
1505 target1.ORG_INFORMATION11 Tech_Res_Phone,
1506 target1.ORG_INFORMATION10 Tech_Res_Area_Code,
1507 target1.ORG_INFORMATION12 Tech_Res_Extension,
1508 decode(target1.ORG_INFORMATION13,'E','A',
1509 target1.ORG_INFORMATION13) Tech_Res_Language,
1510 target1.ORG_INFORMATION14 Acct_Res_Person_Name,
1511 target1.ORG_INFORMATION16 Acct_Res_Phone,
1512 target1.ORG_INFORMATION15 Acct_Res_Area_Code,
1513 target1.ORG_INFORMATION17 Acct_Res_Extension,
1514 decode(target1.ORG_INFORMATION19,'E','A',
1515 target1.ORG_INFORMATION19) Acct_Res_Language,
1516 substr(target1.ORG_INFORMATION18,1,8) RL1_Slip_Number,
1517 decode(target1.org_information3,'Y',target1.organization_id,
1518 target1.ORG_INFORMATION20),
1519 target1.ORG_INFORMATION3
1520 from hr_organization_information target1 ,
1521 hr_all_organization_units target2
1522 where target1.organization_id = to_number(p_pre_organization_id)
1523 and target2.business_group_id = l_business_group_id
1524 and target2.organization_id = target1.organization_id
1525 and target1.org_information_context = 'Prov Reporting Est';
1526
1527 /* payroll action level database items */
1528
1529 BEGIN
1530
1531 /*hr_utility.trace_on('Y','RL1'); */
1532
1533 select to_char(effective_date,'YYYY'),business_group_id
1534 into l_taxation_year,l_business_group_id
1535 from pay_payroll_actions
1536 where payroll_action_id = p_payroll_action_id;
1537
1538 open employer_info;
1539
1540 fetch employer_info
1541 into l_organization_id_of_qin,
1542 l_name, l_business_group_id,
1543 l_employer_ein,
1544 l_Transmitter_Type_Indicator, l_transmitter_number,
1545 l_rl_data_type, l_rl_package_type,
1546 l_rl_source_of_slips,
1547 l_technical_contact_name, l_technical_contact_phone,
1548 l_technical_contact_area_code, l_technical_contact_extension,
1549 l_technical_contact_language, l_accounting_contact_name,
1550 l_accounting_contact_phone ,
1551 l_accounting_contact_area_code ,
1552 l_accounting_contact_extension ,
1553 l_accounting_contact_language,
1554 l_rl1_last_slip_number,
1555 l_transmitter_org_id,
1556 l_transmitter_gre_ind;
1557
1558 l_arch_gre_step := 40;
1559 hr_utility.trace('eoy_archive_gre_data 1');
1560
1561 if employer_info%FOUND then
1562
1563 close employer_info;
1564 hr_utility.trace('got employer data ');
1565
1566 l_employer_info_found := 'Y';
1567
1568 begin
1569 select
1570 L.ADDRESS_LINE_1
1571 , L.ADDRESS_LINE_2
1572 , L.ADDRESS_LINE_3
1573 , L.TOWN_OR_CITY
1574 , DECODE(L.STYLE,'US',L.REGION_2,'CA',L.REGION_1,'CA_GLB',L.REGION_1,' ')
1575 , replace(L.POSTAL_CODE,' ')
1576 , L.COUNTRY
1577 into
1578 l_address_line_1
1579 , l_address_line_2
1580 , l_address_line_3
1581 , l_town_or_city
1582 , l_province_code
1583 , l_postal_code
1584 , l_country_code
1585 from hr_all_organization_units O,
1586 hr_locations_all L
1587 where L.LOCATION_ID = O.LOCATION_ID
1588 AND O.ORGANIZATION_ID = l_organization_id_of_qin;
1589
1590 /* Find out the highest slip number for that transmitter */
1591
1592 if l_transmitter_gre_ind = 'Y' then
1593
1594 l_transmitter_org_id := l_organization_id_of_qin;
1595
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_address_line_1 := NULL;
1608 l_address_line_2 := NULL;
1609 l_address_line_3 := NULL;
1610 l_town_or_city := NULL;
1611 l_province_code := NULL;
1612 l_postal_code := NULL;
1613 l_country_code := NULL;
1614 end;
1615
1616 begin
1617 select name
1618 into l_transmitter_name
1619 from hr_all_organization_units
1620 where organization_id = l_transmitter_org_id;
1621
1622 EXCEPTION
1623 when no_data_found then
1624 l_transmitter_name := null;
1625 end;
1626
1627 else
1628 l_employer_ein := 'TEST_DATA';
1629 l_address_line_1 := 'TEST_DATA';
1630 l_address_line_2 := 'TEST_DATA';
1631 l_address_line_3 := 'TEST_DATA';
1632 l_town_or_city := 'TEST_DATA';
1633 l_province_code := 'TEST_DATA';
1634 l_postal_code := 'TEST_DATA';
1635 l_country_code := 'TEST_DATA';
1636 l_name := 'TEST_DATA';
1637 l_transmitter_name := 'TEST_DATA';
1638 l_transmitter_addr_line_1 := 'TEST_DATA';
1639 l_transmitter_addr_line_2 := 'TEST_DATA';
1640 l_transmitter_addr_line_3 := 'TEST_DATA';
1641 l_transmitter_city := 'TEST_DATA';
1642 l_transmitter_province := 'TEST_DATA';
1643 l_transmitter_postal_code := 'TEST_DATA';
1644 l_transmitter_country := 'TEST_DATA';
1645 l_technical_contact_name := 'TEST_DATA';
1646 l_technical_contact_phone := 'TEST_DATA';
1647 l_technical_contact_language := 'TEST_DATA';
1648 l_accounting_contact_name := 'TEST_DATA';
1649 l_accounting_contact_phone := 'TEST_DATA';
1650 l_accounting_contact_language:= 'TEST_DATA';
1651 l_proprietor_sin := 'TEST_DATA';
1652 l_arch_gre_step := 424;
1653
1654 hr_utility.trace('eoy_archive_gre_data 2');
1655 close employer_info;
1656
1657 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1658 hr_utility.set_message_token('ORGIND','GRE');
1659 hr_utility.raise_error;
1660 end if;
1661
1662 /* archive Releve 1 data */
1663
1664 l_counter := l_counter + 1;
1665 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_QUEBEC_BN';
1666 l_user_entity_value_tab(l_counter) := l_employer_ein;
1667
1668 l_counter := l_counter + 1;
1669 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_PRE_ORG_ID';
1670 l_user_entity_value_tab(l_counter) := p_pre_organization_id;
1671
1672 l_counter := l_counter + 1;
1673 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_NUMBER';
1674 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1675
1676 l_counter := l_counter + 1;
1677 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_DATA_TYPE';
1678 l_user_entity_value_tab(l_counter) := l_rl_data_type;
1679
1680 l_counter := l_counter + 1;
1681 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE';
1682 l_user_entity_value_tab(l_counter) := l_rl_package_type;
1683
1684 l_counter := l_counter + 1;
1685 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_TYPE';
1686 l_user_entity_value_tab(l_counter) := l_Transmitter_Type_Indicator;
1687
1688 l_counter := l_counter + 1;
1689 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_SOURCE_OF_SLIPS';
1690 l_user_entity_value_tab(l_counter) := l_rl_source_of_slips;
1691
1692 l_counter := l_counter + 1;
1693 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1694 l_user_entity_value_tab(l_counter) := l_taxation_year;
1695
1696 l_arch_gre_step := 428;
1697 l_counter := l_counter + 1;
1698 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_COUNTRY';
1699 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1700
1701 l_arch_gre_step := 429;
1702 l_counter := l_counter + 1;
1703 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_NAME';
1704 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1705
1706 l_arch_gre_step := 4210;
1707 l_counter := l_counter + 1;
1708 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1';
1709 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1710
1711 l_arch_gre_step := 4211;
1712 l_counter := l_counter + 1;
1713 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2';
1714 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1715
1716 -- Bug 4517693
1717 l_arch_gre_step := 4212;
1718 l_counter := l_counter + 1;
1719 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE3';
1720 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1721
1722 l_arch_gre_step := 4213;
1723 l_counter := l_counter + 1;
1724 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_CITY';
1725 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1726
1727 l_arch_gre_step := 4214;
1728 l_counter := l_counter + 1;
1729 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PROVINCE';
1730 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1731
1732 /*-- This is original
1733 l_arch_gre_step := 4212;
1734 l_counter := l_counter + 1;
1735 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_CITY';
1736 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1737
1738 l_arch_gre_step := 4213;
1739 l_counter := l_counter + 1;
1740 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PROVINCE';
1741 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1742 */
1743
1744 l_arch_gre_step := 4215;
1745 l_counter := l_counter + 1;
1746 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE';
1747 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1748
1749 l_arch_gre_step := 4216;
1750 l_counter := l_counter + 1;
1751 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TECHNICAL_CONTACT_NAME';
1752 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1753
1754 l_arch_gre_step := 4217;
1755 l_counter := l_counter + 1;
1756 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE';
1757 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1758
1759 l_counter := l_counter + 1;
1760 l_user_entity_name_tab(l_counter) :=
1761 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE';
1762 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1763
1764 l_counter := l_counter + 1;
1765 l_user_entity_name_tab(l_counter) :=
1766 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION';
1767 l_user_entity_value_tab(l_counter) := l_technical_contact_extension;
1768
1769 l_arch_gre_step := 4218;
1770 l_counter := l_counter + 1;
1771 l_user_entity_name_tab(l_counter) :=
1772 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE';
1773 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1774
1775 l_arch_gre_step := 4219;
1776 l_counter := l_counter + 1;
1777 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME';
1778 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1779
1780 l_counter := l_counter + 1;
1781 l_user_entity_name_tab(l_counter) :=
1782 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE';
1783 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code;
1784
1785 l_arch_gre_step := 42110;
1786 l_counter := l_counter + 1;
1787 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE';
1788 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1789
1790 l_counter := l_counter + 1;
1791 l_user_entity_name_tab(l_counter) :=
1792 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION';
1793 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension;
1794
1795 l_arch_gre_step := 4218;
1796 l_counter := l_counter + 1;
1797 l_user_entity_name_tab(l_counter) :=
1798 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE';
1799 l_user_entity_value_tab(l_counter) := l_accounting_contact_language;
1800
1801 l_arch_gre_step := 42111;
1802 l_counter := l_counter + 1;
1803 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_NAME';
1804
1805 l_user_entity_value_tab(l_counter) := 'TEST_DATA';
1806 l_user_entity_value_tab(l_counter) := l_name;
1807
1808 l_counter := l_counter + 1;
1809 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1';
1810 l_user_entity_value_tab(l_counter) := l_address_line_1;
1811
1812 l_counter := l_counter + 1;
1813 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2';
1814 l_user_entity_value_tab(l_counter) := l_address_line_2;
1815
1816 -- Bug 4517693
1817 l_counter := l_counter + 1;
1818 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3';
1819 l_user_entity_value_tab(l_counter) := l_address_line_3;
1820 --
1821 l_counter := l_counter + 1;
1822 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_CITY';
1823 l_user_entity_value_tab(l_counter) := l_town_or_city;
1824
1825 l_counter := l_counter + 1;
1826 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_PROVINCE';
1827 l_user_entity_value_tab(l_counter) := l_province_code;
1828
1829 l_counter := l_counter + 1;
1830 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_COUNTRY';
1831 l_user_entity_value_tab(l_counter) := l_country_code;
1832
1833 l_counter := l_counter + 1;
1834 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_POSTAL_CODE';
1835 l_user_entity_value_tab(l_counter) := l_postal_code;
1836
1837 l_arch_gre_step := 50;
1838 l_arch_gre_step := 51;
1839
1840 /* Other employer level data for RL-1 total is to be discussed ,
1841 whether it is for Quebec only or not */
1842
1843 g_archive_flag := 'Y';
1844
1845 for i in 1..l_counter loop
1846
1847 /*
1848 Since the API checks the presence of a row in pay_report_format_items for
1849 action type AAC and PA , check it here also to avoid API error To be done
1850 */
1851
1852 l_arch_gre_step := 52;
1853
1854 /*hr_utility.trace_on('Y','RL1'); */
1855
1856 hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
1857
1858 ff_archive_api.create_archive_item(
1859 p_archive_item_id => l_archive_item_id
1860 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1861 ,p_archive_value => l_user_entity_value_tab(i)
1862 ,p_archive_type => 'PA'
1863 ,p_action_id => p_payroll_action_id
1864 ,p_legislation_code => 'CA'
1865 ,p_object_version_number=> l_object_version_number
1866 ,p_some_warning => l_some_warning
1867 );
1868 l_arch_gre_step := 53;
1869 end loop;
1870
1871 EXCEPTION
1872 when others then
1873 g_archive_flag := 'N';
1874 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1875 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1876 if l_arch_gre_step = 40 then
1877 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1878 hr_utility.set_message_token('ORGIND','ORG');
1879 end if;
1880 hr_utility.raise_error;
1881
1882 END eoy_archive_gre_data;
1883
1884 /* Name : chk_gre_archive
1885 Purpose : Function to check if the employer level data has been archived
1886 or not.
1887 Arguments :
1888 Notes :
1889 */
1890
1891 function chk_gre_archive (p_payroll_action_id number) return boolean is
1892
1893 l_flag varchar2(1);
1894
1895 cursor c_chk_payroll_action is
1896 select 'Y'
1897 from dual
1898 where exists (select null
1899 from ff_archive_items fai
1900 where fai.context1 = p_payroll_action_id);
1901 begin
1902
1903 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1904
1905 if g_archive_flag = 'Y' then
1906 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1907 return (TRUE);
1908 else
1909
1910 hr_utility.trace('chk_gre_archive - opening cursor');
1911
1912 open c_chk_payroll_action;
1913 fetch c_chk_payroll_action into l_flag;
1914 if c_chk_payroll_action%FOUND then
1915 hr_utility.trace('chk_gre_archive - found in cursor');
1916 g_archive_flag := 'Y';
1917 else
1918 hr_utility.trace('chk_gre_archive - not found in cursor');
1919 g_archive_flag := 'N';
1920 end if;
1921
1922 hr_utility.trace('chk_gre_archive - closing cursor');
1923 close c_chk_payroll_action;
1924 if g_archive_flag = 'Y' then
1925 hr_utility.trace('chk_gre_archive - returning true');
1926 return (TRUE);
1927 else
1928 hr_utility.trace('chk_gre_archive - returning false');
1929 return(FALSE);
1930 end if;
1931 end if;
1932 end chk_gre_archive;
1933
1934 /* Name : eoy_archinit
1935 Purpose : This performs the context initialization for the year end
1936 pre-process.
1937 Arguments :
1938 Notes :
1939 */
1940
1941
1942 procedure eoy_archinit(p_payroll_action_id in number) is
1943 l_jurisdiction_code VARCHAR2(30);
1944 l_tax_unit_id NUMBER(15);
1945 l_archive boolean:= FALSE;
1946 l_step number := 0;
1947
1948 cursor c_get_min_chunk is
1949 select min(paa.chunk_number)
1950 from pay_assignment_actions paa
1951 where paa.payroll_action_id = p_payroll_action_id;
1952 begin
1953 open c_get_min_chunk;
1954 fetch c_get_min_chunk into g_min_chunk;
1955 l_step := 1;
1956 if c_get_min_chunk%NOTFOUND then
1957 g_min_chunk := -1;
1958 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1959 end if;
1960 close c_get_min_chunk;
1961
1962 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1963 l_step := 2;
1964 l_archive := chk_gre_archive(p_payroll_action_id);
1965
1966 l_step := 3;
1967 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1968 exception
1969 when others then
1970 raise_application_error(-20001,'eoy_archinit at '
1971 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1972 end eoy_archinit;
1973
1974
1975 /* Name : eoy_archive_data
1976 Purpose : This performs the CA specific employee context setting for the
1977 Year End PreProcess.
1978 Arguments :
1979 Notes :
1980 */
1981
1982 PROCEDURE eoy_archive_data(p_assactid in number,
1983 p_effective_date in date) IS
1984
1985 l_aaid pay_assignment_actions.assignment_action_id%type;
1986 l_aaid1 pay_assignment_actions.assignment_action_id%type;
1987 l_aaseq pay_assignment_actions.action_sequence%type;
1988 l_asgid pay_assignment_actions.assignment_id%type;
1989 l_date_earned date;
1990 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1991 l_reporting_type varchar2(240);
1992 l_prev_tax_unit_id pay_assignment_actions.tax_unit_id%type := null;
1993 l_business_group_id number;
1994 l_year_start date;
1995 l_year_end date;
1996 l_context_no number := 60;
1997 l_count number := 0;
1998 l_jurisdiction varchar2(11);
1999 l_province_uei ff_user_entities.user_entity_id%type;
2000 l_county_uei ff_user_entities.user_entity_id%type;
2001 l_city_uei ff_user_entities.user_entity_id%type;
2002 l_county_sd_uei ff_user_entities.user_entity_id%type;
2003 l_city_sd_uei ff_user_entities.user_entity_id%type;
2004 l_province_abbrev pay_us_states.state_abbrev%type;
2005 l_county_name pay_us_counties.county_name%type;
2006 l_city_name pay_us_city_names.city_name%type;
2007 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
2008 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
2009 l_step number := 0;
2010 l_county_code varchar2(3);
2011 l_city_code varchar2(4);
2012 l_jursd_context_id ff_contexts.context_id%type;
2013 l_taxunit_context_id ff_contexts.context_id%type;
2014 l_seq_tab pay_ca_eoy_rl1_archive.number_data_type_table;
2015 l_context_id_tab pay_ca_eoy_rl1_archive.number_data_type_table;
2016 l_context_val_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2017 l_chunk number;
2018 l_payroll_action_id number;
2019 l_person_id number;
2020 l_defined_balance_id number;
2021 l_archive_item_id number;
2022 l_date_of_birth date;
2023 l_hire_date date;
2024 l_termination_date date;
2025 l_first_name varchar2(240);
2026 l_middle_name varchar2(240);
2027 l_last_name varchar2(240);
2028 l_employee_number varchar2(240);
2029 l_pre_name_adjunct varchar2(240);
2030 l_employee_phone_no varchar2(240);
2031 l_address_line1 varchar2(240);
2032 l_address_line2 varchar2(240);
2033 l_address_line3 varchar2(240);
2034 l_town_or_city varchar2(80);
2035 l_province_code varchar2(80);
2036 l_postal_code varchar2(80);
2037 l_telephone_number varchar2(80);
2038 l_country_code varchar2(80);
2039 l_counter number := 0;
2040
2041 l_count_start_for_boxo number := 0;
2042 l_count_end_for_boxo number := 0;
2043 l_count_for_boxo_code number := 0;
2044 l_pre_organization_id varchar2(80);
2045 l_national_identifier varchar2(240);
2046 l_user_entity_value_tab_boxo number := 0;
2047 l_user_entity_code_tab_boxo VARCHAR2(4) := NULL;
2048 l_object_version_number number;
2049 l_rl1_slip_number_last_digit number;
2050 l_rl1_slip_number number;
2051
2052 l_some_warning boolean;
2053 result number;
2054 l_no_of_payroll_run number := 0;
2055 l_has_been_paid varchar2(3) := 'N';
2056 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2057 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2058 l_balance_type_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2059 l_footnote_balance_type_tab varchar2(80);
2060 l_footnote_code varchar2(30);
2061 l_footnote_balance varchar2(80);
2062 l_footnote_amount number := 0;
2063 old_l_footnote_code varchar2(80) := null;
2064 old_balance_type_tab varchar2(80) := null;
2065 l_footnote_code_ue varchar2(80);
2066 l_footnote_amount_ue varchar2(80);
2067 l_no_of_fn_codes number := 0;
2068 l_value number := 0;
2069 l_transmitter_name1 varchar2(80);
2070 l_rl1_last_slip_number number;
2071 l_rl1_curr_slip_number number;
2072 l_max_slip_number varchar2(80);
2073 fed_result number;
2074 non_taxable_earnings number;
2075 l_negative_balance_exists varchar2(5);
2076 l_boxr_flag varchar2(5);
2077
2078 ln_balance_value NUMBER := 0;
2079 ln_no_gross_earnings NUMBER := 0;
2080
2081 l_messages VARCHAR2(240);
2082 l_prev_messages VARCHAR2(240);
2083 l_mesg_amt NUMBER(12,2);
2084 l_total_mesg_amt NUMBER(12,2);
2085
2086 l_action_information_id_1 NUMBER ;
2087 l_object_version_number_1 NUMBER ;
2088 ln_tax_unit_id NUMBER ;
2089 ln_prev_tax_unit_id NUMBER ;
2090 ld_eff_date DATE ;
2091 ld_prev_eff_date DATE ;
2092 ln_assignment_action_id NUMBER;
2093
2094 ln_status_indian NUMBER := 0;
2095 ln_boxo_exclude_from_boxa NUMBER := 0;
2096 lv_footnote_bal varchar2(80);
2097
2098 /* added these 3 new variables for 11510 changes bug#3356533 */
2099 l_ft_aaid pay_assignment_actions.assignment_action_id%type;
2100 l_ft_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2101 l_ft_reporting_type varchar2(240);
2102 lv_serial_number varchar2(30);
2103
2104 /* new variables added for Provincial YE Amendment PP */
2105 lv_fapp_effective_date varchar2(5);
2106 ln_fapp_pre_org_id number;
2107 lv_fapp_report_type varchar2(20);
2108 ln_fapp_locked_action_id number;
2109 lv_fapp_prov varchar2(5);
2110 lv_fapp_flag varchar2(2):= 'N';
2111 lv_fapp_locked_actid_reptype varchar2(20);
2112 ln_fapp_prev_amend_actid number;
2113
2114 /* new variables added for pre-printed form number */
2115 lv_eit_year varchar2(30);
2116 lv_eit_pre_org_id varchar2(40);
2117 lv_eit_form_no varchar2(20);
2118 ln_form_no_archived varchar2(2);
2119
2120 lv_footnote_element varchar2(50);
2121
2122 lv_max_pensionable_earnings number;
2123 lv_qpp_pensionable_earnings number;
2124 lv_cpp_pensionable_earnings number;
2125 lv_total_pensionable_earnings number;
2126 lv_taxable_benefit_with_no_rem number;
2127
2128 /* !!Report type 'RL1' or 'RL2' in the GRE might have
2129 to be checked too-Check */
2130
2131 cursor c_all_gres(asgactid number) is
2132 select hoi.organization_id ,
2133 hoi.org_information5
2134 from pay_payroll_actions ppa,
2135 pay_assignment_actions paa,
2136 hr_organization_information hoi
2137 where paa.assignment_action_id = asgactid
2138 and ppa.payroll_action_id = paa.payroll_action_id
2139 and hoi.org_information2 =
2140 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2141 ppa.legislative_parameters)
2142 and hoi.org_information_context = 'Canada Employer Identification'
2143 and hoi.org_information5 in ('T4/RL1','T4A/RL1')
2144 order by organization_id;
2145
2146 cursor c_all_gres_for_footnote(asgactid number) is
2147 select hoi.organization_id ,
2148 hoi.org_information5
2149 from pay_payroll_actions ppa,
2150 pay_assignment_actions paa,
2151 hr_organization_information hoi
2152 where paa.assignment_action_id = asgactid
2153 and ppa.payroll_action_id = paa.payroll_action_id
2154 and hoi.org_information2 =
2155 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2156 ppa.legislative_parameters)
2157 and hoi.org_information_context = 'Canada Employer Identification'
2158 and hoi.org_information5 in ('T4/RL1','T4A/RL1')
2159 order by organization_id;
2160
2161 /* !!To calculate CPP withheld select all the GREs
2162 the person has worked in */
2163
2164 /* 11510 changes for bug#3356533, replaced the old query for
2165 cursor c_all_gres_for_person with this to improve performance.
2166 Using assignment_id instead of assignment_action_id
2167 */
2168 cursor c_all_gres_for_person(cp_asg_id number,cp_eff_date date) is
2169 select distinct paa.tax_unit_id
2170 from pay_assignment_actions paa,
2171 pay_payroll_actions ppa,
2172 per_all_assignments_f paf
2173 where paa.assignment_id = cp_asg_id
2174 and paf.assignment_id = cp_asg_id
2175 and paf.assignment_id = paa.assignment_id
2176 and paa.action_status = 'C'
2177 and ppa.payroll_action_id = paa.payroll_action_id
2178 and ppa.effective_date <= cp_eff_date
2179 and ppa.action_type in ('R', 'Q')
2180 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2181 and exists ( select 1 from pay_run_types_f prt
2182 where prt.legislation_code = 'CA'
2183 and prt.run_type_id = paa.run_type_id
2184 and prt.run_method <> 'C' );
2185
2186 /* Get the jurisdiction code of all the cities
2187 for the person_id corresponding to the
2188 assignment_id . Take it from pay_action_context table. */
2189
2190 cursor c_get_province is
2191 select distinct context_value
2192 from pay_action_contexts pac
2193 where pac.assignment_id = l_asgid;
2194
2195 cursor c_footnote_info(p_balance_name varchar2) is
2196 select distinct pet.element_information19,
2197 pbt1.balance_name
2198 from pay_balance_feeds_f pbf,
2199 pay_balance_types pbt,
2200 pay_balance_types pbt1,
2201 pay_input_values_f piv,
2202 pay_element_types_f pet,
2203 fnd_lookup_values flv
2204 where pbt.balance_name = p_balance_name
2205 and pbf.balance_type_id = pbt.balance_type_id
2206 and pbf.input_value_id = piv.input_value_id
2207 and piv.element_type_id = pet.element_type_id
2208 and pbt1.balance_type_id = pet.element_information10
2209 and pet.business_group_id = l_business_group_id
2210 and pet.element_information19 = flv.lookup_code
2211 and flv.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
2212 --bug 5558604 starts
2213 and flv.enabled_flag = 'Y'
2214 and l_date_earned between nvl(flv.start_Date_active,l_date_earned)
2215 and nvl(flv.end_date_Active,l_date_earned)
2216 --bug 5558604 starts
2217 and flv.language = userenv('LANG')
2218 order by pet.element_information19;
2219
2220 cursor c_get_addr is
2221 select addr.address_line1,
2222 addr.address_line2,
2223 addr.address_line3,
2224 addr.town_or_city,
2225 decode(addr.country,'CA', addr.region_1 , 'US' , addr.region_2 , ' '),
2226 replace(addr.postal_code,' '),
2227 addr.telephone_number_1,
2228 country.territory_code
2229 from per_addresses addr,
2230 fnd_territories_vl country
2231 where addr.person_id = l_person_id
2232 and addr.primary_flag = 'Y'
2233 and l_date_earned between nvl(addr.date_from, l_date_earned)
2234 and nvl(addr.date_to, l_date_earned)
2235 and country.territory_code = addr.country
2236 order by date_from desc;
2237
2238 /* Modified the cursor to fix bug#3641353 and added
2239 action_type 'B' to consider Balance Adjustments */
2240 cursor cur_non_box_mesg( cp_asgactid in number,
2241 cp_eff_date in date ) is
2242 select distinct prrv1.result_value,
2243 prrv2.result_value,
2244 hoi.organization_id,
2245 run_ppa.effective_date,
2246 run_paa.assignment_action_id
2247 from pay_run_result_values prrv1
2248 , pay_run_result_values prrv2
2249 , pay_run_results prr
2250 , pay_element_types_f pet
2251 , pay_input_values_f piv1
2252 , pay_input_values_f piv2
2253 , pay_assignment_actions run_paa
2254 , pay_payroll_actions run_ppa
2255 , pay_assignment_actions arch_paa
2256 , pay_payroll_actions arch_ppa
2257 , per_all_assignments_f arch_paf
2258 , per_all_assignments_f all_paf
2259 , hr_all_organization_units hou
2260 , hr_organization_information hoi
2261 where arch_paa.assignment_action_id = cp_asgactid
2262 and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
2263 and hou.business_group_id + 0 = arch_ppa.business_group_id
2264 and hou.organization_id = hoi.organization_id
2265 and hoi.org_information2 = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2266 arch_ppa.legislative_parameters)
2267 and hoi.org_information_context = 'Canada Employer Identification'
2268 and run_paa.tax_unit_id = hou.organization_id
2269 and run_ppa.payroll_action_id = run_paa.payroll_action_id
2270 and run_ppa.action_type in ( 'R', 'Q','B' )
2271 and to_char(run_ppa.effective_date,'YYYY' ) = to_char(cp_eff_date,'YYYY')
2272 and run_paa.action_status = 'C'
2273 and pet.element_name = lv_footnote_element --'RL1 NonBox Footnotes'
2274 and prr.assignment_action_id = run_paa.assignment_action_id
2275 and prr.element_type_id = pet.element_type_id
2276 and piv1.element_type_id = pet.element_type_id
2277 and piv1.name = 'Message'
2278 and prrv1.run_result_id = prr.run_result_id
2279 and prrv1.input_value_id = piv1.input_value_id
2280 and piv2.element_type_id = pet.element_type_id
2281 and piv2.name = 'Amount'
2282 and prrv2.run_result_id = prrv1.run_result_id
2283 and prrv2.input_value_id = piv2.input_value_id
2284 and arch_paf.assignment_id = arch_paa.assignment_id
2285 and to_char(cp_eff_date,'YYYY')
2286 between to_char(arch_paf.effective_start_date,'YYYY')
2287 and to_char(arch_paf.effective_end_date,'YYYY')
2288 and all_paf.person_id = arch_paf.person_id
2289 and to_char(cp_eff_date,'YYYY')
2290 between to_char(all_paf.effective_start_date,'YYYY')
2291 and to_char(all_paf.effective_end_date,'YYYY')
2292 and run_paa.assignment_id = all_paf.assignment_id
2293 and exists (select 1
2294 from pay_action_contexts pac,ff_contexts ffc
2295 where ffc.context_name = 'JURISDICTION_CODE'
2296 and pac.context_id = ffc.context_id
2297 and pac.assignment_id = run_paa.assignment_id
2298 and pac.context_value = 'QC');
2299
2300
2301 /* New cursors added for Provincial YE Amendment Pre-Process Validation */
2302 CURSOR c_get_fapp_prov_emp(cp_assignment_action_id number) IS
2303 select fai.value
2304 from ff_archive_items fai,
2305 ff_database_items fdi
2306 where fdi.user_entity_id = fai.user_entity_id
2307 and fai.context1 = cp_assignment_action_id
2308 and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT';
2309
2310 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2311 select ppa.report_type
2312 from pay_payroll_actions ppa,pay_assignment_actions paa
2313 where paa.assignment_action_id = cp_locked_actid
2314 and ppa.payroll_action_id = paa.payroll_action_id;
2315
2316 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
2317 select locked_action_id
2318 from pay_action_interlocks
2319 where locking_action_id = cp_locking_act_id;
2320
2321 CURSOR c_get_preprinted_form_no (cp_person_id number,
2322 cp_pre_org_id number) IS
2323 select pei_information5,
2324 pei_information6,
2325 pei_information7
2326 from per_people_extra_info
2327 where person_id = cp_person_id
2328 and pei_information6 = to_char(cp_pre_org_id)
2329 and pei_information_category = 'PAY_CA_RL1_FORM_NO';
2330
2331 /* 11510 Changes Bug#3356533. Changed the cursor to get max asgact_id
2332 based on person_id, to fix bug#3638928. */
2333 CURSOR c_get_max_asgactid_jd(cp_person_id number,
2334 cp_tax_unit_id number,
2335 cp_period_start date,
2336 cp_period_end date
2337 ) IS
2338 select paa.assignment_action_id
2339 from pay_assignment_actions paa,
2340 per_all_assignments_f paf,
2341 per_all_people_f ppf,
2342 pay_payroll_actions ppa,
2343 pay_action_classifications pac,
2344 pay_action_contexts pac1,
2345 ff_contexts fc
2346 where ppf.person_id = cp_person_id
2347 and paf.person_id = ppf.person_id
2348 and paf.assignment_id = paa.assignment_id
2349 and paa.tax_unit_id = cp_tax_unit_id
2350 and ppa.payroll_action_id = paa.payroll_action_id
2351 and ppa.effective_date between cp_period_start and cp_period_end
2352 and ppa.effective_date between ppf.effective_start_date
2353 and ppf.effective_end_date
2354 and ppa.effective_date between paf.effective_start_date
2355 and paf.effective_end_date
2356 and ppa.action_type = pac.action_type
2357 and pac.classification_name = 'SEQUENCED'
2358 AND pac1.assignment_action_id = paa.assignment_action_id
2359 AND pac1.context_id = fc.context_id
2360 AND fc.context_name = 'JURISDICTION_CODE'
2361 AND pac1.context_value = 'QC'
2362 order by paa.action_sequence desc;
2363
2364 /* 11510 changes for bug#3356533. Changed the cursor to get max asgact_id
2365 based on person_id, to fix bug#3638928. */
2366 CURSOR c_get_max_asgactid(cp_person_id number,
2367 cp_tax_unit_id number,
2368 cp_period_start date,
2369 cp_period_end date) IS
2370 select paa.assignment_action_id
2371 from pay_assignment_actions paa,
2372 per_all_assignments_f paf,
2373 per_all_people_f ppf,
2374 pay_payroll_actions ppa,
2375 pay_action_classifications pac
2376 where ppf.person_id = cp_person_id
2377 and paf.person_id = ppf.person_id
2378 and paf.assignment_id = paa.assignment_id
2379 and paa.tax_unit_id = cp_tax_unit_id
2380 and ppa.payroll_action_id = paa.payroll_action_id
2381 and ppa.effective_date between cp_period_start and cp_period_end
2382 and ppa.effective_date between ppf.effective_start_date
2383 and ppf.effective_end_date
2384 and ppa.effective_date between paf.effective_start_date
2385 and paf.effective_end_date
2386 and ppa.action_type = pac.action_type
2387 and pac.classification_name = 'SEQUENCED'
2388 order by paa.action_sequence desc;
2389
2390
2391 BEGIN
2392
2393 --hr_utility.trace_on(null,'RL1');
2394 hr_utility.set_location ('archive_data',1);
2395 hr_utility.trace('getting assignment');
2396
2397 l_negative_balance_exists := 'N';
2398 lv_qpp_pensionable_earnings := 0;
2399 l_step := 1;
2400
2401 SELECT aa.assignment_id,
2402 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2403 aa.tax_unit_id,
2404 aa.chunk_number,
2405 aa.payroll_action_id,
2406 aa.serial_number
2407 into l_asgid,
2408 l_date_earned,
2409 l_tax_unit_id,
2410 l_chunk,
2411 l_payroll_action_id,
2412 lv_serial_number
2413 FROM pay_assignment_actions aa
2414 WHERE aa.assignment_action_id = p_assactid;
2415
2416 /* If the chunk of the assignment is same as the minimun chunk
2417 for the payroll_action_id and the gre data has not yet been
2418 archived then archive the gre data i.e. the employer data */
2419
2420 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2421
2422 hr_utility.trace('eoy_archive_data archiving employer data');
2423 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2424
2425 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2426 legislative_parameters),
2427 business_group_id
2428 into l_pre_organization_id,l_business_group_id
2429 from pay_payroll_actions
2430 where payroll_action_id = l_payroll_action_id;
2431
2432 eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2433 p_pre_organization_id=>l_pre_organization_id);
2434
2435 hr_utility.trace('eoy_archive_data archived employer data');
2436
2437 end if;
2438
2439 hr_utility.set_location ('archive_data',2);
2440
2441 hr_utility.trace('assignment '|| to_char(l_asgid));
2442 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2444 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445
2446 /* Derive the beginning and end of the effective year */
2447
2448 hr_utility.trace('getting begin and end dates');
2449
2450 l_step := 2;
2451
2452 l_year_start := trunc(p_effective_date, 'Y');
2453 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2454
2455 hr_utility.trace('year start '|| to_char(l_year_start));
2456 hr_utility.trace('year end '|| to_char(l_year_end));
2457
2458 if to_number(to_char(l_year_end,'YYYY')) > 2005 then
2459 lv_footnote_element := 'RL1 Non Box Footnotes';
2460 else
2461 lv_footnote_element := 'RL1 NonBox Footnotes';
2462 end if;
2463
2464 /* Initialise the PL/SQL table before populating it */
2465
2466 hr_utility.trace('Initialising Pl/SQL table');
2467
2468 l_step := 3;
2469
2470 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2471
2472 l_step := 5;
2473
2474 select context_id
2475 into l_jursd_context_id
2476 from ff_contexts
2477 where context_name = 'JURISDICTION_CODE';
2478
2479 select context_id
2480 into l_taxunit_context_id
2481 from ff_contexts
2482 where context_name = 'TAX_UNIT_ID';
2483
2484 l_step := 6;
2485
2486 l_jurisdiction := 'QC';
2487
2488 l_step := 12;
2489
2490 l_count := l_count + 1;
2491
2492 hr_utility.trace('archiving CAEOY_RL1_PROVINCE_OF_EMPLOYMENT');
2493
2494 ff_archive_api.create_archive_item(
2495 /*p_validate => 'TRUE' */
2496 p_archive_item_id => l_archive_item_id
2497 ,p_user_entity_id =>
2498 get_user_entity_id('CAEOY_RL1_PROVINCE_OF_EMPLOYMENT')
2499 ,p_archive_value => l_jurisdiction
2500 ,p_archive_type => 'AAP'
2501 ,p_action_id => p_assactid
2502 ,p_legislation_code => 'CA'
2503 ,p_object_version_number => l_object_version_number
2504 ,p_some_warning => l_some_warning
2505 );
2506
2507 hr_utility.trace('archived caeoy_rl1_employment_province');
2508
2509 /* We can archive the balance level dbis also because for employee level
2510 balances jurisdiction is always a context. */
2511
2512 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2513
2514 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2515 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2516
2517 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2518
2519 /* RL1 Slip number generation */
2520
2521 begin
2522
2523 select decode(hoi.org_information3,'Y',hoi.organization_id,
2524 hoi.org_information20)
2525 into l_transmitter_name1
2526 from pay_payroll_actions ppa,
2527 hr_organization_information hoi,
2528 hr_all_organization_units hou
2529 WHERE hou.business_group_id = ppa.business_group_id
2530 and hoi.organization_id = hou.organization_id
2531 and hoi.org_information_context='Prov Reporting Est'
2532 and hoi.organization_id =
2533 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2534 ppa.legislative_parameters )
2535 and ppa.payroll_action_id = l_payroll_action_id
2536 and hoi.org_information4 = 'P01';
2537
2538 hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2539
2540 hr_utility.trace('3');
2541
2542 if l_transmitter_name1 is not null then
2543
2544 select to_number(target.ORG_INFORMATION18)
2545 into l_rl1_last_slip_number
2546 from hr_organization_information target
2547 where target.organization_id = l_transmitter_name1
2548 and target.org_information_context = 'Prov Reporting Est'
2549 and target.ORG_INFORMATION3 = 'Y';
2550
2551 else
2552 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2553 hr_utility.set_message_token('ORGIND','PRE');
2554 hr_utility.raise_error;
2555 end if;
2556
2557 hr_utility.trace('2');
2558
2559 select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2560 into l_rl1_curr_slip_number from dual;
2561
2562 hr_utility.trace('1');
2563
2564 select mod(l_rl1_curr_slip_number,7)
2565 into l_rl1_slip_number_last_digit
2566 from dual;
2567
2568 hr_utility.trace('l_rl1_slip_number_last_digit : '||
2569 l_rl1_slip_number_last_digit);
2570
2571 l_rl1_slip_number := (l_rl1_curr_slip_number)||
2572 l_rl1_slip_number_last_digit;
2573
2574 hr_utility.trace('l_rl1_slip_number : ' || l_rl1_slip_number);
2575
2576 hr_utility.trace('l_rl1_curr_slip_number : '||l_rl1_curr_slip_number);
2577 end;
2578
2579 l_count := 0;
2580
2581 l_count := l_count + 1;
2582 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_JD_YTD';
2583 l_balance_type_tab(l_count) := 'Gross Earnings';
2584
2585 l_count := l_count + 1;
2586 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD';
2587 l_balance_type_tab(l_count) := 'QPP EE Withheld';
2588 /**********************************************************/
2589 l_count := l_count + 1;
2590 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD';
2591 l_balance_type_tab(l_count) := 'PPIP EE Withheld';
2592 /****************************tombi******************/
2593 l_count := l_count + 1;
2594 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_WITHHELD_PER_JD_YTD';
2595 l_balance_type_tab(l_count) := 'EI EE Withheld';
2596
2597 /* Quebec Income tax withheld */
2598 l_count := l_count + 1;
2599 l_user_entity_name_tab(l_count) := 'CAEOY_PROV_WITHHELD_PER_JD_YTD';
2600 l_balance_type_tab(l_count) := 'PROV Withheld';
2601
2602 /* Registered pension plan */
2603 l_count := l_count + 1;
2604 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXD_PER_JD_YTD';
2605 l_balance_type_tab(l_count) := 'RL1_BOXD';
2606
2607 /* Union Dues */
2608 l_count := l_count + 1;
2609 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXF_PER_JD_YTD';
2610 l_balance_type_tab(l_count) := 'RL1_BOXF';
2611
2612 /* Pensionable Earnings under Quebec pension plan */
2613 l_count := l_count + 1;
2614 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD';
2615 l_balance_type_tab(l_count) := 'QPP EE Taxable';
2616
2617 /**********************************************/
2618 l_count := l_count + 1;
2619 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD';
2620 l_balance_type_tab(l_count) := 'PPIP EE Taxable';
2621 /***************tombi************************/
2622
2623 /* QPP EE Basic Exemption ( EOY 2001 for YE Exemption Report ) */
2624 l_count := l_count + 1;
2625 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_BASIC_EXEMPTION_PER_JD_YTD';
2626 l_balance_type_tab(l_count) := 'QPP EE Basic Exemption';
2627
2628 /* QPP Exempt ( EOY 2001 for YE Exemption Report ) */
2629 l_count := l_count + 1;
2630 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EXEMPT_PER_JD_YTD';
2631 l_balance_type_tab(l_count) := 'QPP Exempt';
2632
2633 /* QPP Reduced Subject for Box G (EOY 2004) */
2634 l_count := l_count + 1;
2635 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD';
2636 l_balance_type_tab(l_count) := 'QPP Reduced Subject';
2637
2638 /* PPIP Reduced Subject for Box I (EOY 2006) */
2639 l_count := l_count + 1;
2640 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD';
2641 l_balance_type_tab(l_count) := 'PPIP Reduced Subject';
2642
2643 /* Meals and accommodations */
2644 l_count := l_count + 1;
2645 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXV_PER_JD_YTD';
2646 l_balance_type_tab(l_count) := 'RL1_BOXV';
2647 --l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXH_PER_JD_YTD';
2648 --l_balance_type_tab(l_count) := 'RL1_BOXH';
2649
2650 /* Use of a motor vehicle for personal purpose */
2651 l_count := l_count + 1;
2652 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXW_PER_JD_YTD';
2653 l_balance_type_tab(l_count) := 'RL1_BOXW';
2654 --l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXI_PER_JD_YTD';
2655 --l_balance_type_tab(l_count) := 'RL1_BOXI';
2656
2657 /* Contribution paid by the employer by the employer under
2658 a private health */
2659 l_count := l_count + 1;
2660 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXJ_PER_JD_YTD';
2661 l_balance_type_tab(l_count) := 'RL1_BOXJ';
2662
2663 /* Trips made by residents of designated remote areas */
2664 l_count := l_count + 1;
2665 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXK_PER_JD_YTD';
2666 l_balance_type_tab(l_count) := 'RL1_BOXK';
2667
2668 /* Other Benefits */
2669 l_count := l_count + 1;
2670 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXL_PER_JD_YTD';
2671 l_balance_type_tab(l_count) := 'RL1_BOXL';
2672
2673 /* Commissions included in amount in box A */
2674 l_count := l_count + 1;
2675 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXM_PER_JD_YTD';
2676 l_balance_type_tab(l_count) := 'RL1_BOXM';
2677
2678 /* Charitable Donations */
2679 l_count := l_count + 1;
2680 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXN_PER_JD_YTD';
2681 l_balance_type_tab(l_count) := 'RL1_BOXN';
2682
2683 l_count := l_count + 1;
2684 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RA_PER_JD_YTD';
2685 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RA';
2686 l_count_start_for_boxo := l_count;
2687
2688 l_count := l_count + 1;
2689 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RB_PER_JD_YTD';
2690 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RB';
2691
2692 l_count := l_count + 1;
2693 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RC_PER_JD_YTD';
2694 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RC';
2695
2696 l_count := l_count + 1;
2697 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RD_PER_JD_YTD';
2698 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RD';
2699
2700 l_count := l_count + 1;
2701 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RF_PER_JD_YTD';
2702 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RF';
2703
2704 l_count := l_count + 1;
2705 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RG_PER_JD_YTD';
2706 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RG';
2707
2708 l_count := l_count + 1;
2709 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RH_PER_JD_YTD';
2710 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RH';
2711
2712 l_count := l_count + 1;
2713 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RI_PER_JD_YTD';
2714 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RI';
2715
2716 l_count := l_count + 1;
2717 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RJ_PER_JD_YTD';
2718 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RJ';
2719
2720 l_count := l_count + 1;
2721 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RK_PER_JD_YTD';
2722 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RK';
2723
2724 l_count := l_count + 1;
2725 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RL_PER_JD_YTD';
2726 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RL';
2727
2728 l_count := l_count + 1;
2729 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RM_PER_JD_YTD';
2730 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RM';
2731
2732 l_count := l_count + 1;
2733 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RN_PER_JD_YTD';
2734 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RN';
2735
2736 l_count := l_count + 1;
2737 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RO_PER_JD_YTD';
2738 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RO';
2739
2740 l_count := l_count + 1;
2741 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RP_PER_JD_YTD';
2742 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RP';
2743
2744 l_count := l_count + 1;
2745 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RQ_PER_JD_YTD';
2746 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RQ';
2747
2748 l_count := l_count + 1;
2749 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RR_PER_JD_YTD';
2750 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RR';
2751
2752 l_count := l_count + 1;
2753 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RS_PER_JD_YTD';
2754 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RS';
2755
2756 l_count := l_count + 1;
2757 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RT_PER_JD_YTD';
2758 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RT';
2759
2760 l_count := l_count + 1;
2761 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RU_PER_JD_YTD';
2762 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RU';
2763
2764 l_count := l_count + 1;
2765 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RV_PER_JD_YTD';
2766 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RV';
2767
2768 --Bug 6525899. Added check to not to archive this balance from 2007
2769 IF ( to_number(to_char(l_year_end,'YYYY')) < 2007) then
2770
2771 l_count := l_count + 1;
2772 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RW_PER_JD_YTD';
2773 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RW';
2774
2775 END IF;
2776 --End 6525899
2777
2778 l_count_end_for_boxo := l_count;
2779
2780 /* Contributions to a multi-employer insurance plan */
2781 l_count := l_count + 1;
2782 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXP_PER_JD_YTD';
2783 l_balance_type_tab(l_count) := 'RL1_BOXP';
2784
2785 /* Deferred salary or wages */
2786 l_count := l_count + 1;
2787 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXQ_PER_JD_YTD';
2788 l_balance_type_tab(l_count) := 'RL1_BOXQ';
2789
2790 /* Tax exempt income paid to an Indian */
2791 l_count := l_count + 1;
2792 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXR_PER_JD_YTD';
2793 l_balance_type_tab(l_count) := 'PROV STATUS INDIAN Subject';
2794
2795 /* Tips received */
2796 l_count := l_count + 1;
2797 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXS_PER_JD_YTD';
2798 l_balance_type_tab(l_count) := 'RL1_BOXS';
2799
2800 /* Tips allocated */
2801 l_count := l_count + 1;
2802 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXT_PER_JD_YTD';
2803 l_balance_type_tab(l_count) := 'RL1_BOXT';
2804
2805 /* Phased retirement */
2806 l_count := l_count + 1;
2807 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXU_PER_JD_YTD';
2808 l_balance_type_tab(l_count) := 'RL1_BOXU';
2809
2810 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2811
2812 for i in 1 .. l_count
2813 loop
2814 hr_utility.trace('Initialising values');
2815 l_user_entity_value_tab(i) := 0;
2816 end loop;
2817
2818 open c_all_gres(p_assactid);
2819
2820 loop
2821
2822 hr_utility.trace('Fetching all GREs');
2823 fetch c_all_gres into l_tax_unit_id,l_reporting_type;
2824 exit when c_all_gres%NOTFOUND;
2825
2826 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827 hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828 hr_utility.trace('Person id is ' || lv_serial_number);
2829 hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830 hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2831
2832 begin
2833 /* Removed select stmt to get max asgact_id and replaced it with
2834 cursor c_get_max_asgactid_jd. 11510 Changes Bug#3356533.
2835 Changed the cursor to get max asgact_id based on person_id to
2836 fix bug#3638928 */
2837 open c_get_max_asgactid_jd(to_number(lv_serial_number),
2838 l_tax_unit_id,
2839 l_year_start,
2840 l_year_end);
2841 fetch c_get_max_asgactid_jd into l_aaid;
2842 close c_get_max_asgactid_jd;
2843
2844 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2845 hr_utility.trace('l_count is ' || to_char(l_count));
2846
2847 ln_no_gross_earnings := ln_no_gross_earnings +
2848 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2849 ('RL1 No Gross Earnings',
2850 'YTD' ,
2851 l_aaid,
2852 l_asgid,
2853 NULL,
2854 'PER' ,
2855 l_tax_unit_id,
2856 l_business_group_id,
2857 'QC'
2858 ),0);
2859
2860 l_no_of_payroll_run := l_no_of_payroll_run + 1;
2861
2862 select target1.business_group_id
2863 into l_business_group_id
2864 from hr_all_organization_units target1
2865 where target1.organization_id = l_tax_unit_id;
2866
2867 if l_tax_unit_id <> l_prev_tax_unit_id or
2868 l_prev_tax_unit_id is null then
2869
2870 hr_utility.trace('l_business_group_id is '||l_business_group_id);
2871
2872 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2873 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2874 Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
2875
2876 for i in 1 .. l_count
2877 loop
2878
2879 hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
2880 hr_utility.trace('i is ' || i);
2881
2882 /* T4A earnings should not go to BOX A of RL1 */
2883
2884 /* bug 5768390
2885 if l_reporting_type = 'T4A/RL1' and
2886 l_balance_type_tab(i) = 'Gross Earnings'
2887 then
2888 null;
2889 else
2890 bug 5768390 */
2891
2892 /* l_user_entity_value_tab(i) := 0; */
2893
2894 if l_balance_type_tab(i) = 'Gross Earnings' then
2895
2896 fed_result :=
2897 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2898 ('Taxable Benefits for Federal',
2899 'YTD' ,
2900 l_aaid,
2901 l_asgid ,
2902 NULL,
2903 'PER' ,
2904 l_tax_unit_id,
2905 l_business_group_id,
2906 'QC'
2907 ),0);
2908
2909 non_taxable_earnings :=
2910 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2911 ('RL1 Non Taxable Earnings',
2912 'YTD' ,
2913 l_aaid,
2914 l_asgid ,
2915 NULL,
2916 'PER' ,
2917 l_tax_unit_id,
2918 l_business_group_id,
2919 'QC'
2920 ),0);
2921
2922 hr_utility.trace('Fed Result = ' || fed_result);
2923 hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2924 else
2925 fed_result := 0;
2926 non_taxable_earnings := 0;
2927 hr_utility.trace('Fed Result = ' || fed_result);
2928 hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2929 end if;
2930
2931 ln_balance_value :=
2932 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2933 ( l_balance_type_tab(i),
2934 'YTD' ,
2935 l_aaid,
2936 l_asgid ,
2937 NULL,
2938 'PER' ,
2939 l_tax_unit_id,
2940 l_business_group_id,
2941 'QC'
2942 ),0);
2943
2944 /* Get QPP Pensionable Earnings for use when processing nonbox footnotes */
2945 if l_balance_type_tab(i) = 'QPP EE Taxable' then
2946 lv_qpp_pensionable_earnings := lv_qpp_pensionable_earnings + ln_balance_value;
2947 end if;
2948
2949 hr_utility.trace('Balance value is '|| ln_balance_value);
2950
2951 if ln_balance_value <> 0 then
2952 l_has_been_paid := 'Y';
2953 if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
2954 ln_status_indian := ln_status_indian +
2955 ln_balance_value;
2956 end if;
2957 end if;
2958
2959 if instr(l_balance_type_tab(i), 'RL1_BOXO') > 0 and
2960 ln_balance_value <> 0 then
2961
2962 /* bug 5768390
2963 if l_reporting_type <> 'T4A/RL1' then
2964 bug 5768390 */
2965 ln_boxo_exclude_from_boxa := ln_boxo_exclude_from_boxa +
2966 ln_balance_value;
2967 /* bug 5768390
2968 end if;
2969 bug 5768390 */
2970
2971
2972 hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973 hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974 hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975 hr_utility.trace('Assignemnt ID '|| l_asgid);
2976 hr_utility.trace('ln_boxo_exclude_from_boxa '||
2977 ln_boxo_exclude_from_boxa);
2978
2979 end if;
2980 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
2981 ln_balance_value -
2982 fed_result -
2983 non_taxable_earnings;
2984
2985 /* bug 5768390
2986 end if;
2987 bug 5768390 */
2988
2989 hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
2990 l_prev_tax_unit_id := l_tax_unit_id ;
2991
2992 end loop;
2993 end if;
2994
2995 exception
2996 when no_data_found then
2997 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2998 end;
2999 end loop;
3000 close c_all_gres;
3001
3002 hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3003
3004 if ((l_no_of_payroll_run > 0) and
3005 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3006
3007 -- hr_utility.trace_on('Y','RL1');
3008 for i in 1 .. l_count
3009 loop
3010
3011 hr_utility.trace('in the create_archive_item loop');
3012 hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3013 hr_utility.trace('archive value is ');
3014
3015 /* Archiving footnotes */
3016
3017 old_l_footnote_code := NULL;
3018 old_balance_type_tab := NULL;
3019
3020 hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3021 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3022
3023 if l_user_entity_value_tab(i) <> 0 then
3024
3025 if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3026 l_footnote_balance_type_tab := 'RL1_BOXR';
3027 elsif l_balance_type_tab(i) = 'Gross Earnings' then
3028 l_footnote_balance_type_tab := 'RL1_BOXA';
3029 if ln_status_indian <> 0 then
3030 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) -
3031 ln_status_indian;
3032 ln_status_indian := 0;
3033 end if;
3034 if ln_boxo_exclude_from_boxa <> 0 then
3035 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) -
3036 ln_boxo_exclude_from_boxa;
3037 ln_boxo_exclude_from_boxa := 0;
3038 end if;
3039 else
3040 l_footnote_balance_type_tab := l_balance_type_tab(i);
3041 end if;
3042
3043 if l_footnote_balance_type_tab in ('RL1_BOXA',
3044 'RL1_BOXD',
3045 'RL1_BOXK',
3046 'RL1_BOXR',
3047 'RL1_BOXQ',
3048 'RL1_BOXO_AMOUNT_RL',
3049 'RL1_BOXO_AMOUNT_RN') then
3050 begin
3051
3052 if l_footnote_balance_type_tab = 'RL1_BOXR' then
3053 lv_footnote_bal := 'PROV STATUS INDIAN Subject';
3054 elsif l_footnote_balance_type_tab = 'RL1_BOXA' then
3055 lv_footnote_bal := 'Gross Earnings';
3056 else
3057 lv_footnote_bal := l_footnote_balance_type_tab;
3058 end if;
3059
3060 open c_footnote_info(lv_footnote_bal);
3061 loop
3062 fetch c_footnote_info into l_footnote_code,
3063 l_footnote_balance;
3064 exit when c_footnote_info%NOTFOUND;
3065
3066 hr_utility.trace('l_footnote_amount_balance is '||
3067 l_footnote_balance);
3068 hr_utility.trace('l_footnote_code is '||
3069 l_footnote_code);
3070 hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3071 l_footnote_amount_ue);
3072
3073 /* Must ensure that BOXR is only used with footnote code 14 */
3074 l_boxr_flag := 'Y';
3075 if ((l_footnote_balance_type_tab = 'RL1_BOXR') and
3076 (l_footnote_code <> '14')) then
3077 l_boxr_flag := 'N';
3078 end if;
3079
3080 if l_boxr_flag = 'Y' then
3081
3082 if ( l_footnote_code <> old_l_footnote_code or
3083 old_l_footnote_code is null )
3084 then
3085 hr_utility.trace('old_l_footnote_code is '||
3086 nvl(old_l_footnote_code,'NULL'));
3087 if old_l_footnote_code is not null then
3088
3089 l_footnote_amount_ue := 'CAEOY_' ||old_balance_type_tab
3090 ||'_'||old_l_footnote_code||'_AMT_PER_JD_YTD';
3091
3092 if get_footnote_user_entity_id(l_footnote_amount_ue)<>0
3093 and l_footnote_amount <> 0
3094 then
3095 ff_archive_api.create_archive_item(
3096 p_archive_item_id => l_archive_item_id
3097 ,p_user_entity_id =>
3098 get_footnote_user_entity_id(l_footnote_amount_ue)
3099 ,p_archive_value => l_footnote_amount
3100 ,p_archive_type => 'AAP'
3101 ,p_action_id => p_assactid
3102 ,p_legislation_code => 'CA'
3103 ,p_object_version_number => l_object_version_number
3104 ,p_context_name1 => 'JURISDICTION_CODE'
3105 ,p_context1 => 'QC'
3106 ,p_some_warning => l_some_warning
3107 );
3108
3109 if l_footnote_amount < 0 then
3110 l_negative_balance_exists := 'Y';
3111 end if;
3112
3113 end if;
3114
3115 end if;
3116
3117 l_footnote_amount := 0;
3118 old_l_footnote_code := l_footnote_code ;
3119 old_balance_type_tab := l_footnote_balance_type_tab ;
3120 l_footnote_amount_ue := 'CAEOY_' ||
3121 l_footnote_balance_type_tab||
3122 '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3123 hr_utility.trace('l_footnote_amount_ue is '||
3124 l_footnote_amount_ue);
3125 end if;
3126
3127 l_footnote_amount_ue := 'CAEOY_' ||
3128 l_footnote_balance_type_tab||
3129 '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3130 l_prev_tax_unit_id := NULL;
3131
3132 /* get the footnote_balance */
3133 open c_all_gres_for_footnote(p_assactid);
3134 loop
3135 hr_utility.trace('Fetching all GREs');
3136 fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3137 l_ft_reporting_type;
3138 exit when c_all_gres_for_footnote%NOTFOUND;
3139
3140 hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3141 hr_utility.trace('Asgid is ' || l_asgid);
3142 hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3143 hr_utility.trace('Effective date is '|| p_effective_date);
3144 begin
3145 /* Removed select stmt to get max asgact_id and replaced
3146 it with cursor c_get_max_asgactid_jd, reusing the same
3147 cursor used above. 11510 Changes Bug#3356533. Changed
3148 cursor to get max asg_act_id based on person_id to
3149 fix bug#3638928. */
3150 open c_get_max_asgactid_jd(to_number(lv_serial_number),
3151 l_ft_tax_unit_id,
3152 l_year_start,
3153 l_year_end);
3154 fetch c_get_max_asgactid_jd into l_ft_aaid;
3155 close c_get_max_asgactid_jd;
3156
3157 hr_utility.trace('l_aaid is ' || l_ft_aaid);
3158 hr_utility.trace('l_count is ' || l_count);
3159
3160 l_no_of_payroll_run := l_no_of_payroll_run + 1;
3161
3162 select target1.business_group_id
3163 into l_business_group_id
3164 from hr_all_organization_units target1
3165 where target1.organization_id = l_ft_tax_unit_id;
3166
3167 if ( l_ft_tax_unit_id <> l_prev_tax_unit_id or
3168 l_prev_tax_unit_id is null )
3169 then
3170 hr_utility.trace('l_business_group_id is ' ||
3171 l_business_group_id);
3172
3173 pay_balance_pkg.set_context('TAX_UNIT_ID',
3174 l_ft_tax_unit_id);
3175 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',
3176 l_ft_aaid);
3177 pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3178
3179 l_footnote_amount := l_footnote_amount +
3180 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3181 ( l_footnote_balance,
3182 'YTD' ,
3183 l_ft_aaid,
3184 l_asgid ,
3185 NULL,
3186 'PER' ,
3187 l_ft_tax_unit_id,
3188 l_business_group_id,
3189 'QC'
3190 ),0) ;
3191 end if;
3192
3193 l_prev_tax_unit_id := l_ft_tax_unit_id ;
3194 exception
3195 when no_data_found then
3196 hr_utility.trace('This Tax unit id has no payroll run,'||
3197 ' so skip it');
3198 end;
3199 end loop;
3200 close c_all_gres_for_footnote;
3201
3202 /* end of getting balnce */
3203
3204 l_footnote_amount := l_footnote_amount + l_value ;
3205
3206 if l_value <> 0 then
3207 l_no_of_fn_codes := l_no_of_fn_codes + 1;
3208 end if;
3209
3210 end if; /* l_boxr_flag */
3211
3212 end loop; /* c_footnote_info loop */
3213 close c_footnote_info;
3214
3215 hr_utility.trace('before archiving l_footnote_amount_ue is '||
3216 l_footnote_amount_ue);
3217
3218 if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3219 and l_footnote_amount <> 0 then
3220
3221 ff_archive_api.create_archive_item(
3222 p_archive_item_id => l_archive_item_id
3223 ,p_user_entity_id =>
3224 get_footnote_user_entity_id(l_footnote_amount_ue)
3225 ,p_archive_value => l_footnote_amount
3226 ,p_archive_type => 'AAP'
3227 ,p_action_id => p_assactid
3228 ,p_legislation_code => 'CA'
3229 ,p_object_version_number => l_object_version_number
3230 ,p_context_name1 => 'JURISDICTION_CODE'
3231 ,p_context1 => 'QC'
3232 ,p_some_warning => l_some_warning
3233 );
3234
3235 if l_footnote_amount < 0 then
3236 l_negative_balance_exists := 'Y';
3237 end if;
3238
3239 l_footnote_amount := 0;
3240 l_footnote_amount_ue := null;
3241 end if;
3242 end;
3243 end if;
3244 end if;
3245
3246 /* End of footnote archiving */
3247
3248 /* archive the box balances */
3249 hr_utility.trace('here1');
3250 hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251 hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252 hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253 hr_utility.trace('p_assactid ='|| p_assactid);
3254 hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255 -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3256 ff_archive_api.create_archive_item(
3257 /* p_validate => 'TRUE' */
3258 p_archive_item_id => l_archive_item_id
3259 ,p_user_entity_id =>
3260 get_user_entity_id(l_user_entity_name_tab(i))
3261 ,p_archive_value => l_user_entity_value_tab(i)
3262 ,p_archive_type => 'AAP'
3263 ,p_action_id => p_assactid
3264 ,p_legislation_code => 'CA'
3265 ,p_object_version_number => l_object_version_number
3266 ,p_context_name1 => 'JURISDICTION_CODE'
3267 ,p_context1 => 'QC'
3268 ,p_some_warning => l_some_warning
3269 );
3270 hr_utility.trace('after the call');
3271 if l_user_entity_value_tab(i) < 0 then
3272 l_negative_balance_exists := 'Y';
3273 end if;
3274
3275 end loop;
3276
3277 /* Archive BOXO, which is sum of all the individual
3278 balances under BOXO, also determine the correct
3279 BOXO code that needs to be archived */
3280
3281 l_user_entity_value_tab_boxo := 0;
3282 l_count_for_boxo_code := 0;
3283 l_user_entity_code_tab_boxo := NULL;
3284
3285 for i in l_count_start_for_boxo..l_count_end_for_boxo
3286 loop
3287
3288 if to_number(l_user_entity_value_tab(i)) <> 0 then
3289
3290 l_count_for_boxo_code := l_count_for_boxo_code + 1;
3291
3292 l_user_entity_code_tab_boxo :=
3293 substr(l_user_entity_name_tab(i),23,2);
3294
3295 l_user_entity_value_tab_boxo :=
3296 l_user_entity_value_tab_boxo + l_user_entity_value_tab(i);
3297 end if;
3298
3299 end loop;
3300
3301 if l_count_for_boxo_code > 1 then
3302 l_user_entity_code_tab_boxo := 'RZ' ;
3303 end if;
3304
3305 if l_user_entity_value_tab_boxo < 0 then
3306 l_negative_balance_exists := 'Y';
3307 end if;
3308
3309 ff_archive_api.create_archive_item(
3310 p_archive_item_id => l_archive_item_id
3311 ,p_user_entity_id =>
3312 get_user_entity_id('CAEOY_RL1_BOXO_PER_JD_YTD')
3313 ,p_archive_value => l_user_entity_value_tab_boxo
3314 ,p_archive_type => 'AAP'
3315 ,p_action_id => p_assactid
3316 ,p_legislation_code => 'CA'
3317 ,p_object_version_number => l_object_version_number
3318 ,p_context_name1 => 'JURISDICTION_CODE'
3319 ,p_context1 => 'QC'
3320 ,p_some_warning => l_some_warning
3321 );
3322
3323 ff_archive_api.create_archive_item(
3324 /* p_validate => 'TRUE' */
3325 p_archive_item_id => l_archive_item_id
3326 ,p_user_entity_id =>
3327 get_user_entity_id('CAEOY_RL1_BOXO_CODE_PER_JD_YTD')
3328 ,p_archive_value => l_user_entity_code_tab_boxo
3329 ,p_archive_type => 'AAP'
3330 ,p_action_id => p_assactid
3331 ,p_legislation_code => 'CA'
3332 ,p_object_version_number => l_object_version_number
3333 ,p_context_name1 => 'JURISDICTION_CODE'
3334 ,p_context1 => 'QC'
3335 ,p_some_warning => l_some_warning
3336 );
3337
3338 /* for box o archiving */
3339
3340 /* archive RL1 slip number */
3341
3342 ff_archive_api.create_archive_item(
3343 /* p_validate => 'TRUE' */
3344 p_archive_item_id => l_archive_item_id
3345 ,p_user_entity_id =>
3346 get_user_entity_id('CAEOY_RL1_SLIP_NUMBER')
3347 ,p_archive_value => l_rl1_slip_number
3348 ,p_archive_type => 'AAP'
3349 ,p_action_id => p_assactid
3350 ,p_legislation_code => 'CA'
3351 ,p_object_version_number => l_object_version_number
3352 ,p_some_warning => l_some_warning );
3353
3354 /* archive CPP amount */
3355
3356 /* 11510 changes done to c_all_gres_for_person cursor
3357 passing asgid instead of p_assactid */
3358
3359 open c_all_gres_for_person(l_asgid,p_effective_date);
3360
3361 result := 0;
3362 lv_cpp_pensionable_earnings := 0;
3363 lv_taxable_benefit_with_no_rem := 0;
3364
3365 loop
3366 hr_utility.trace('Fetching all GREs for the person');
3367 fetch c_all_gres_for_person into l_tax_unit_id;
3368 exit when c_all_gres_for_person%NOTFOUND;
3369
3370 begin
3371 /* Removed the select stmt to get max asgact_id and replaced it
3372 with cursor c_get_max_asgactid. 11510 changes for bug#3356533.
3373 Changed the cursor to get max asg_act_id based on person_id
3374 to fix bug#3638928. */
3375 open c_get_max_asgactid(to_number(lv_serial_number),
3376 l_tax_unit_id,
3377 l_year_start,
3378 l_year_end);
3379 fetch c_get_max_asgactid into l_aaid1;
3380 close c_get_max_asgactid;
3381
3382 result := result +
3383 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3384 ('CPP EE Withheld',
3385 'YTD' ,
3386 l_aaid1,
3387 l_asgid,
3388 NULL,
3389 'PER' ,
3390 l_tax_unit_id,
3391 l_business_group_id,
3392 NULL),0);
3393
3394 lv_cpp_pensionable_earnings := lv_cpp_pensionable_earnings +
3395 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3396 ('CPP EE Taxable',
3397 'YTD' ,
3398 l_aaid1,
3399 l_asgid,
3400 NULL,
3401 'PER' ,
3402 l_tax_unit_id,
3403 l_business_group_id,
3404 NULL),0);
3405
3406 lv_taxable_benefit_with_no_rem := lv_taxable_benefit_with_no_rem +
3407 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3408 ('Taxable Benefit without Remuneration',
3409 'YTD' ,
3410 l_aaid1,
3411 l_asgid ,
3412 NULL,
3413 'PER' ,
3414 l_tax_unit_id,
3415 l_business_group_id,
3416 'QC'),0);
3417
3418 end;
3419 end loop;
3420 close c_all_gres_for_person;
3421 hr_utility.trace('closed all GREs for the person');
3422
3423 ff_archive_api.create_archive_item(
3424 /* p_validate => 'TRUE' */
3425 p_archive_item_id => l_archive_item_id
3426 ,p_user_entity_id =>
3427 get_user_entity_id('CAEOY_CPP_EE_WITHHELD_PER_YTD')
3428 ,p_archive_value => result
3429 ,p_archive_type => 'AAP'
3430 ,p_action_id => p_assactid
3431 ,p_legislation_code => 'CA'
3432 ,p_object_version_number => l_object_version_number
3433 ,p_some_warning => l_some_warning);
3434
3435 /* End of CPP archiving */
3436 --hr_utility.trace_off;
3437 end if;
3438
3439 hr_utility.trace('Out of province loop ');
3440
3441 /* Archiving of Non-Box Footnotes */
3442 begin
3443 --hr_utility.trace_on('Y','NONBOX');
3444
3445 /* Archive Nonbox footnote for Taxable Benefits that are processed on their
3446 own if the total pensionable earnings is less than the maximum bug# 3369317 */
3447
3448 lv_max_pensionable_earnings := 0;
3449 lv_total_pensionable_earnings := 0;
3450
3451 select fnd_number.canonical_to_number(information_value)
3452 into lv_max_pensionable_earnings
3453 from pay_ca_legislation_info
3454 where information_type = 'MAX_CPP_EARNINGS'
3455 and l_year_end between start_date
3456 and end_date;
3457
3458 lv_total_pensionable_earnings := lv_cpp_pensionable_earnings +
3459 lv_qpp_pensionable_earnings;
3460
3461 if ((lv_max_pensionable_earnings > lv_total_pensionable_earnings) and
3462 (lv_taxable_benefit_with_no_rem <> 0)) then
3463
3464 pay_action_information_api.create_action_information(
3465 p_action_information_id => l_action_information_id_1,
3466 p_object_version_number => l_object_version_number_1,
3467 p_action_information_category => 'CA FOOTNOTES',
3468 p_action_context_id => p_assactid,
3469 p_action_context_type => 'AAP',
3470 p_jurisdiction_code => 'QC',
3471 p_tax_unit_id => NULL,
3472 p_effective_date => l_year_end,
3473 p_assignment_id => l_asgid,
3474 p_action_information1 => NULL,
3475 p_action_information2 => NULL,
3476 p_action_information3 => NULL,
3477 p_action_information4 => '10', /* QPP - Taxable benefit in kind */
3478 p_action_information5 => lv_taxable_benefit_with_no_rem,
3479 p_action_information6 => 'RL1',
3480 p_action_information7 => NULL,
3481 p_action_information8 => NULL,
3482 p_action_information9 => NULL,
3483 p_action_information10 => NULL,
3484 p_action_information11 => NULL,
3485 p_action_information12 => NULL,
3486 p_action_information13 => NULL,
3487 p_action_information14 => NULL,
3488 p_action_information15 => NULL,
3489 p_action_information16 => NULL,
3490 p_action_information17 => NULL,
3491 p_action_information18 => NULL,
3492 p_action_information19 => NULL,
3493 p_action_information20 => NULL,
3494 p_action_information21 => NULL,
3495 p_action_information22 => NULL,
3496 p_action_information23 => NULL,
3497 p_action_information24 => NULL,
3498 p_action_information25 => NULL,
3499 p_action_information26 => NULL,
3500 p_action_information27 => NULL,
3501 p_action_information28 => NULL,
3502 p_action_information29 => NULL,
3503 p_action_information30 => NULL);
3504
3505 if lv_taxable_benefit_with_no_rem < 0 then
3506 l_negative_balance_exists := 'Y';
3507 end if;
3508 end if;
3509
3510 l_total_mesg_amt := 0;
3511 l_mesg_amt := 0;
3512
3513 open cur_non_box_mesg(p_assactid, p_effective_date);
3514 loop
3515 fetch cur_non_box_mesg into l_messages,
3516 l_mesg_amt,
3517 ln_tax_unit_id,
3518 ld_eff_date,
3519 ln_assignment_action_id;
3520 if cur_non_box_mesg%notfound then
3521 exit;
3522 end if;
3523
3524 hr_utility.trace('l_messages - '||l_messages);
3525 hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3526
3527 /* If the same Non Box footnote is processed more than
3528 once during the year, then the sum of the associated
3529 amounts is archived */
3530
3531 if ((l_messages <> l_prev_messages) and
3532 (l_prev_messages is not null)) then
3533
3534 hr_utility.trace('l_prev_messages - '||l_prev_messages);
3535
3536 if l_total_mesg_amt <> 0 then
3537
3538 pay_action_information_api.create_action_information(
3539 p_action_information_id => l_action_information_id_1,
3540 p_object_version_number => l_object_version_number_1,
3541 p_action_information_category => 'CA FOOTNOTES',
3542 p_action_context_id => p_assactid,
3543 p_action_context_type => 'AAP',
3544 p_jurisdiction_code => 'QC',
3545 p_tax_unit_id => ln_prev_tax_unit_id,
3546 p_effective_date => ld_prev_eff_date,
3547 p_assignment_id => l_asgid,
3548 p_action_information1 => NULL,
3549 p_action_information2 => NULL,
3550 p_action_information3 => NULL,
3551 p_action_information4 => l_prev_messages,
3552 p_action_information5 => l_total_mesg_amt,
3553 p_action_information6 => 'RL1',
3554 p_action_information7 => NULL,
3555 p_action_information8 => NULL,
3556 p_action_information9 => NULL,
3557 p_action_information10 => NULL,
3558 p_action_information11 => NULL,
3559 p_action_information12 => NULL,
3560 p_action_information13 => NULL,
3561 p_action_information14 => NULL,
3562 p_action_information15 => NULL,
3563 p_action_information16 => NULL,
3564 p_action_information17 => NULL,
3565 p_action_information18 => NULL,
3566 p_action_information19 => NULL,
3567 p_action_information20 => NULL,
3568 p_action_information21 => NULL,
3569 p_action_information22 => NULL,
3570 p_action_information23 => NULL,
3571 p_action_information24 => NULL,
3572 p_action_information25 => NULL,
3573 p_action_information26 => NULL,
3574 p_action_information27 => NULL,
3575 p_action_information28 => NULL,
3576 p_action_information29 => NULL,
3577 p_action_information30 => NULL
3578 );
3579
3580 if l_total_mesg_amt < 0 then
3581 l_negative_balance_exists := 'Y';
3582 end if;
3583
3584 end if;
3585
3586 l_total_mesg_amt := l_mesg_amt;
3587 else
3588 l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
3589 end if;
3590
3591 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3592
3593 l_prev_messages := l_messages;
3594 ln_prev_tax_unit_id := ln_tax_unit_id;
3595 ld_prev_eff_date := ld_eff_date;
3596
3597 end loop;
3598
3599 close cur_non_box_mesg;
3600
3601 if (l_prev_messages is not null) then
3602
3603 hr_utility.trace('l_prev_messages - '||l_prev_messages);
3604 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3605
3606 if l_total_mesg_amt <> 0 then
3607
3608 pay_action_information_api.create_action_information(
3609 p_action_information_id => l_action_information_id_1,
3610 p_object_version_number => l_object_version_number_1,
3611 p_action_information_category => 'CA FOOTNOTES',
3612 p_action_context_id => p_assactid,
3613 p_action_context_type => 'AAP',
3614 p_jurisdiction_code => 'QC',
3615 p_tax_unit_id => ln_prev_tax_unit_id,
3616 p_effective_date => ld_prev_eff_date,
3617 p_assignment_id => l_asgid,
3618 p_action_information1 => NULL,
3619 p_action_information2 => NULL,
3620 p_action_information3 => NULL,
3621 p_action_information4 => l_prev_messages,
3622 p_action_information5 => l_total_mesg_amt,
3623 p_action_information6 => 'RL1',
3624 p_action_information7 => NULL,
3625 p_action_information8 => NULL,
3626 p_action_information9 => NULL,
3627 p_action_information10 => NULL,
3628 p_action_information11 => NULL,
3629 p_action_information12 => NULL,
3630 p_action_information13 => NULL,
3631 p_action_information14 => NULL,
3632 p_action_information15 => NULL,
3633 p_action_information16 => NULL,
3634 p_action_information17 => NULL,
3635 p_action_information18 => NULL,
3636 p_action_information19 => NULL,
3637 p_action_information20 => NULL,
3638 p_action_information21 => NULL,
3639 p_action_information22 => NULL,
3640 p_action_information23 => NULL,
3641 p_action_information24 => NULL,
3642 p_action_information25 => NULL,
3643 p_action_information26 => NULL,
3644 p_action_information27 => NULL,
3645 p_action_information28 => NULL,
3646 p_action_information29 => NULL,
3647 p_action_information30 => NULL
3648 );
3649
3650 if l_total_mesg_amt < 0 then
3651 l_negative_balance_exists := 'Y';
3652 end if;
3653
3654 end if;
3655
3656 end if;
3657
3658 --hr_utility.trace_off;
3659 end;
3660
3661 ff_archive_api.create_archive_item(
3662 p_archive_item_id => l_archive_item_id
3663 ,p_user_entity_id =>
3664 get_user_entity_id('CAEOY_RL1_NEGATIVE_BALANCE_EXISTS')
3665 ,p_archive_value => l_negative_balance_exists
3666 ,p_archive_type => 'AAP'
3667 ,p_action_id => p_assactid
3668 ,p_legislation_code => 'CA'
3669 ,p_object_version_number => l_object_version_number
3670 ,p_context_name1 => 'JURISDICTION_CODE'
3671 ,p_context1 => 'QC'
3672 ,p_some_warning => l_some_warning
3673 );
3674
3675 l_count := 0;
3676 /* Similarly create archive data for employee surname,employee first name,
3677 employee initial, employee address ,city,province,country,postal code,
3678 SIN, employee number , business number .
3679 Not all of them has jurisdiction context.*/
3680
3681 if ((l_no_of_payroll_run > 0) and
3682 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3683 begin
3684
3685 --code fix started for bug 5893569
3686 /*
3687 select PEOPLE.person_id,
3688 PEOPLE.first_name,
3689 PEOPLE.middle_names,
3690 PEOPLE.last_name,
3691 PEOPLE.employee_number,
3692 PEOPLE.date_of_birth,
3693 replace(PEOPLE.national_identifier,' '),
3694 PEOPLE.pre_name_adjunct,
3695 NVL(PHONE.phone_number,PEOPLE.work_telephone)
3696 into l_person_id,
3697 l_first_name,
3698 l_middle_name,
3699 l_last_name,
3700 l_employee_number,
3701 l_date_of_birth,
3702 l_national_identifier,
3703 l_pre_name_adjunct,
3704 l_employee_phone_no
3705 from per_all_assignments_f ASSIGN
3706 ,per_all_people_f PEOPLE
3707 ,per_person_types PTYPE
3708 ,per_phones PHONE
3709 ,fnd_sessions SES
3710 where l_date_earned BETWEEN ASSIGN.effective_start_date
3711 AND ASSIGN.effective_end_date
3712 and ASSIGN.assignment_id = l_asgid
3713 and PEOPLE.person_id = ASSIGN.person_id
3714 and l_date_earned BETWEEN PEOPLE.effective_start_date
3715 AND PEOPLE.effective_end_date
3716 and PTYPE.person_type_id = PEOPLE.person_type_id
3717 and PHONE.parent_id (+) = PEOPLE.person_id
3718 and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
3719 and PHONE.phone_type (+)= 'W1'
3720 and l_date_earned
3721 BETWEEN NVL(PHONE.date_from,l_date_earned)
3722 AND NVL(PHONE.date_to,l_date_earned)
3723 and SES.session_id = USERENV('SESSIONID');
3724 */
3725
3726 select PEOPLE.person_id,
3727 PEOPLE.first_name,
3728 PEOPLE.middle_names,
3729 PEOPLE.last_name,
3730 PEOPLE.employee_number,
3731 PEOPLE.date_of_birth,
3732 replace(PEOPLE.national_identifier,' '),
3733 PEOPLE.pre_name_adjunct
3734 into l_person_id,
3735 l_first_name,
3736 l_middle_name,
3737 l_last_name,
3738 l_employee_number,
3739 l_date_of_birth,
3740 l_national_identifier,
3741 l_pre_name_adjunct
3742 from per_all_assignments_f ASSIGN
3743 ,per_all_people_f PEOPLE
3744 where ASSIGN.assignment_id =l_asgid
3745 and PEOPLE.person_id = ASSIGN.person_id
3746 -- code fix started for 6440125
3747 and l_date_earned BETWEEN ASSIGN.effective_start_date
3748 AND ASSIGN.effective_end_date
3749 and l_date_earned BETWEEN PEOPLE.effective_start_date
3750 AND PEOPLE.effective_end_date;
3751
3752 --code fix ended for 6440125
3753 --code fix ended for bug 5893569
3754
3755 exception
3756 when no_data_found then
3757 l_first_name := null;
3758 l_middle_name := null;
3759 l_last_name := null;
3760 l_employee_number := null;
3761 l_national_identifier := null;
3762 l_pre_name_adjunct := null;
3763 l_employee_phone_no := null;
3764 l_date_of_birth := null;
3765 end;
3766
3767 begin
3768
3769 select max(date_start)
3770 ,max(actual_termination_date)
3771 into l_hire_date
3772 ,l_termination_date
3773 from per_periods_of_service
3774 where person_id = l_person_id;
3775
3776 exception
3777 when no_data_found then
3778 l_hire_date := null;
3779 l_termination_date := null;
3780
3781 end;
3782
3783 hr_utility.trace('Before counter of asgid '|| l_asgid);
3784
3785 l_counter := l_counter + 1;
3786 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
3787 l_user_entity_value_tab(l_counter):= l_person_id;
3788
3789 l_counter := l_counter + 1;
3790 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
3791 l_user_entity_value_tab(l_counter):= l_first_name;
3792
3793 hr_utility.trace('Before counter 2');
3794 l_counter := l_counter + 1;
3795 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
3796 l_user_entity_value_tab(l_counter):= l_last_name ;
3797
3798 hr_utility.trace('Before counter 3');
3799 l_counter := l_counter + 1;
3800 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
3801 l_user_entity_value_tab(l_counter):= l_middle_name ;
3802
3803 hr_utility.trace('Before counter 3');
3804 l_counter := l_counter + 1;
3805 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
3806 l_user_entity_value_tab(l_counter):= l_national_identifier;
3807
3808 hr_utility.trace('Before counter 3');
3809 l_counter := l_counter + 1;
3810 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
3811 l_user_entity_value_tab(l_counter):=
3812 fnd_date.date_to_canonical(l_date_of_birth);
3813
3814 hr_utility.trace('Before counter 3');
3815 l_counter := l_counter + 1;
3816 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
3817 l_user_entity_value_tab(l_counter):=
3818 fnd_date.date_to_canonical(l_hire_date);
3819
3820 hr_utility.trace('Before counter 3');
3821 l_counter := l_counter + 1;
3822 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
3823 l_user_entity_value_tab(l_counter):=
3824 fnd_date.date_to_canonical(l_termination_date);
3825
3826 hr_utility.trace('Before counter 3');
3827 l_counter := l_counter + 1;
3828 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
3829 l_user_entity_value_tab(l_counter):= l_employee_number;
3830
3831 for i in 1 .. l_counter
3832 loop
3833
3834 hr_utility.trace('inside create loop '||l_user_entity_value_tab(i));
3835
3836 ff_archive_api.create_archive_item(
3837 /* p_validate => 'TRUE' */
3838 p_archive_item_id => l_archive_item_id
3839 ,p_user_entity_id =>
3840 get_user_entity_id(l_user_entity_name_tab(i))
3841 ,p_archive_value => l_user_entity_value_tab(i)
3842 ,p_archive_type => 'AAP'
3843 ,p_action_id => p_assactid
3844 ,p_legislation_code => 'CA'
3845 ,p_object_version_number => l_object_version_number
3846 ,p_some_warning => l_some_warning
3847 );
3848 end loop;
3849 end if;
3850
3851 l_counter := 0;
3852
3853 if ((l_no_of_payroll_run > 0) and
3854 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3855
3856 begin
3857 open c_get_addr;
3858 fetch c_get_addr into l_address_line1
3859 ,l_address_line2
3860 ,l_address_line3
3861 ,l_town_or_city
3862 ,l_province_code
3863 ,l_postal_code
3864 ,l_telephone_number
3865 ,l_country_code;
3866
3867 if c_get_addr%NOTFOUND then
3868 l_address_line1 := null;
3869 l_address_line2 := null;
3870 l_address_line3 := null;
3871 l_town_or_city := null;
3872 l_province_code := null;
3873 l_postal_code := null;
3874 l_telephone_number := null;
3875 l_country_code := null;
3876 end if;
3877 close c_get_addr;
3878 end;
3879
3880 l_counter := l_counter + 1;
3881 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
3882 l_user_entity_value_tab(l_counter) := l_address_line1;
3883
3884
3885 l_counter := l_counter + 1;
3886 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
3887 l_user_entity_value_tab(l_counter) := l_address_line2;
3888
3889 l_counter := l_counter + 1;
3890 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
3891 l_user_entity_value_tab(l_counter) := l_address_line3;
3892
3893 l_counter := l_counter + 1;
3894 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
3895 l_user_entity_value_tab(l_counter) := l_town_or_city;
3896
3897
3898 l_counter := l_counter + 1;
3899 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
3900 l_user_entity_value_tab(l_counter) := l_province_code;
3901
3902 l_counter := l_counter + 1;
3903 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
3904 l_user_entity_value_tab(l_counter) := l_country_code;
3905
3906 l_counter := l_counter + 1;
3907 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
3908 l_user_entity_value_tab(l_counter) := l_postal_code;
3909
3910
3911 for i in 1 .. l_counter
3912 loop
3913 ff_archive_api.create_archive_item(
3914 p_archive_item_id => l_archive_item_id
3915 ,p_user_entity_id =>
3916 get_user_entity_id(l_user_entity_name_tab(i))
3917 ,p_archive_value => l_user_entity_value_tab(i)
3918 ,p_archive_type => 'AAP'
3919 ,p_action_id => p_assactid
3920 ,p_legislation_code => 'CA'
3921 ,p_object_version_number => l_object_version_number
3922 ,p_some_warning => l_some_warning
3923 );
3924 end loop;
3925 end if;
3926
3927 Begin
3928
3929 hr_utility.trace('Started Provincial YE Amendment PP Validation ');
3930 select to_char(effective_date,'YYYY'),
3931 report_type,
3932 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID'
3933 ,legislative_parameters))
3934 into lv_fapp_effective_date,
3935 lv_fapp_report_type,
3936 ln_fapp_pre_org_id
3937 from pay_payroll_actions
3938 where payroll_action_id = l_payroll_action_id;
3939
3940 hr_utility.trace('Prov Amend Pre-Process Pactid :'||
3941 to_char(l_payroll_action_id));
3942 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3943
3944 /* Archive the Pre-Printed form number for the RL1 YEPP
3945 and Amendment Pre-Process if one exists*/
3946
3947 ln_form_no_archived := 'N';
3948 open c_get_preprinted_form_no (l_person_id, ln_fapp_pre_org_id);
3949 loop
3950 fetch c_get_preprinted_form_no
3951 into lv_eit_year,
3952 lv_eit_pre_org_id,
3953 lv_eit_form_no;
3954
3955 exit when c_get_preprinted_form_no%NOTFOUND;
3956
3957 if ((lv_fapp_effective_date =
3958 to_char(fnd_date.canonical_to_date(lv_eit_year), 'YYYY')) and
3959 (ln_fapp_pre_org_id = to_number(lv_eit_pre_org_id)) and
3960 (ln_form_no_archived = 'N')) then
3961
3962 ff_archive_api.create_archive_item(
3963 p_archive_item_id => l_archive_item_id
3964 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PRE_PRINTED_FORM_NO')
3965 ,p_archive_value => lv_eit_form_no
3966 ,p_archive_type => 'AAP'
3967 ,p_action_id => p_assactid
3968 ,p_legislation_code => 'CA'
3969 ,p_object_version_number => l_object_version_number
3970 ,p_context_name1 => 'JURISDICTION_CODE'
3971 ,p_context1 => 'QC'
3972 ,p_some_warning => l_some_warning
3973 );
3974
3975 ln_form_no_archived := 'Y';
3976 end if;
3977
3978 end loop;
3979
3980 close c_get_preprinted_form_no;
3981
3982 if ln_form_no_archived = 'N' then
3983
3984 ff_archive_api.create_archive_item(
3985 p_archive_item_id => l_archive_item_id
3986 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PRE_PRINTED_FORM_NO')
3987 ,p_archive_value => NULL
3988 ,p_archive_type => 'AAP'
3989 ,p_action_id => p_assactid
3990 ,p_legislation_code => 'CA'
3991 ,p_object_version_number => l_object_version_number
3992 ,p_context_name1 => 'JURISDICTION_CODE'
3993 ,p_context1 => 'QC'
3994 ,p_some_warning => l_some_warning
3995 );
3996
3997 end if;
3998
3999 IF lv_fapp_report_type = 'CAEOY_RL1_AMEND_PP' then
4000 begin
4001
4002 open c_get_fapp_locked_action_id(p_assactid);
4003 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4004 close c_get_fapp_locked_action_id;
4005
4006 hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
4007 hr_utility.trace('ln_fapp_locked_action_id :'||
4008 to_char(ln_fapp_locked_action_id));
4009 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4010 fetch c_get_fapp_lkd_actid_rtype
4011 into lv_fapp_locked_actid_reptype;
4012 close c_get_fapp_lkd_actid_rtype;
4013 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
4014 lv_fapp_locked_actid_reptype);
4015
4016 open c_get_fapp_prov_emp(p_assactid);
4017 loop
4018 fetch c_get_fapp_prov_emp into lv_fapp_prov;
4019 exit when c_get_fapp_prov_emp%NOTFOUND;
4020 hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
4021
4022 lv_fapp_flag := compare_archive_data(p_assactid,
4023 ln_fapp_locked_action_id,
4024 lv_fapp_prov);
4025
4026 if lv_fapp_flag = 'Y' then
4027
4028 hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4029 hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
4030
4031 ff_archive_api.create_archive_item(
4032 p_archive_item_id => l_archive_item_id
4033 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_AMENDMENT_FLAG')
4034 ,p_archive_value => lv_fapp_flag
4035 ,p_archive_type => 'AAP'
4036 ,p_action_id => p_assactid
4037 ,p_legislation_code => 'CA'
4038 ,p_object_version_number => l_object_version_number
4039 ,p_context_name1 => 'JURISDICTION_CODE'
4040 ,p_context1 => lv_fapp_prov
4041 ,p_context_name2 => 'TAX_UNIT_ID'
4042 ,p_context2 => l_tax_unit_id
4043 ,p_some_warning => l_some_warning
4044 );
4045
4046 end if;
4047
4048 end loop;
4049 close c_get_fapp_prov_emp;
4050
4051 end; -- report_type validation
4052
4053 END IF; -- report type validation for FAPP
4054 hr_utility.trace('End of Provincial YE Amendment PP Validation');
4055
4056 Exception when no_data_found then
4057 hr_utility.trace('Report type not found for given Payroll_action ');
4058 null;
4059 End;
4060 -- End of Provincial YE Amendment Pre-Process Validation
4061
4062 end eoy_archive_data;
4063
4064
4065 /* Name : eoy_range_cursor
4066 Purpose : This returns the select statement that is used to created the
4067 range rows for the Year End Pre-Process.
4068 Arguments :
4069 Notes :
4070 */
4071
4072 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
4073
4074 l_pre_organization_id varchar2(50);
4075 l_archive boolean:= FALSE;
4076 l_business_group number;
4077 l_year_start date;
4078 l_year_end date;
4079
4080 begin
4081
4082 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
4083 legislative_parameters),
4084 trunc(effective_date,'Y'),
4085 effective_date,
4086 business_group_id
4087 into l_pre_organization_id,
4088 l_year_start,
4089 l_year_end,
4090 l_business_group
4091 from pay_payroll_actions
4092 where payroll_action_id = pactid;
4093
4094 sqlstr := 'select distinct asg.person_id
4095 from pay_all_payrolls_f ppy,
4096 pay_payroll_actions ppa,
4097 pay_assignment_actions paa,
4098 per_all_assignments_f asg,
4099 pay_payroll_actions ppa1
4100 where ppa1.payroll_action_id = :payroll_action_id
4101 and ppa.effective_date between
4102 fnd_date.canonical_to_date('''||
4103 fnd_date.date_to_canonical(l_year_start)||''') and
4104 fnd_date.canonical_to_date('''||
4105 fnd_date.date_to_canonical(l_year_end)||''')
4106 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
4107 and ppa.action_status = ''C''
4108 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
4109 and ppa.payroll_action_id = paa.payroll_action_id
4110 and paa.tax_unit_id in
4111 (select hoi.organization_id
4112 from hr_organization_information hoi
4113 where hoi.org_information_context = ''Canada Employer Identification''
4114 and hoi.org_information2 = '''|| l_pre_organization_id ||''''||'
4115 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1''))
4116 and paa.action_status = ''C''
4117 and paa.assignment_id = asg.assignment_id
4118 and ppa.business_group_id = asg.business_group_id + 0
4119 and ppa.effective_date between asg.effective_start_date
4120 and asg.effective_end_date
4121 and asg.assignment_type = ''E''
4122 and ppa.payroll_id = ppy.payroll_id
4123 and ppy.business_group_id = '||to_char(l_business_group)||'
4124 and exists (select 1
4125 from pay_action_contexts pac,
4126 ff_contexts fc
4127 where pac.assignment_id = paa.assignment_id
4128 and pac.assignment_action_id = paa.assignment_action_id
4129 and pac.context_id = fc.context_id
4130 and fc.context_name = ''JURISDICTION_CODE''
4131 and pac.context_value = ''QC'' )
4132 order by asg.person_id';
4133
4134 l_archive := chk_gre_archive(pactid);
4135 if g_archive_flag = 'N' then
4136 hr_utility.trace('eoy_range_cursor archiving employer data');
4137 /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
4138 eoy_archive_gre_data(pactid,
4139 l_pre_organization_id);
4140 hr_utility.trace('eoy_range_cursor archived employer data');
4141 end if;
4142
4143 end eoy_range_cursor;
4144
4145 end pay_ca_eoy_rl1_archive;